MySQL

一：SQL概述

SQL语言分为五个部分

1.数据查询语言（DQL）：
DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
2.数据操作语言（DML）：
DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：

1. INSERT：增加数据
2. UPDATE：修改数据
3. DELETE：删除数据

3.数据定义语言（DDL）：
DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：

1. CREATE：创建数据库对象
2. ALTER：修改数据库对象
3. DROP：删除数据库对象

4.数据控制语言（DCL）：
DCL用来授予或回收访问 数据库的权限，其主要包括：

1. GRANT：授予用户某种权限
2. REVOKE：回收授予的某种权限

5.事务控制语言（TCL）：
TCL用于数据库的事务管理。其主要包括：

1. START TRANSACTION：开启事务
2. COMMIT：提交事务
3. ROLLBACK：回滚事务
4. SET TRANSACTION：设置事务的属性

二：数据库表

认识数据库表

表是数据库中数据存储最常见和最简单的一种形式，数据库可以将复杂的数据结构用较为简单的二维表来表示

每个表都是由若干行和列组成的，在数据库中表中的行被称为记录，表中的列被称为是这些记录的字段

数据库表列的类型

1.整数类型

真实数据大小即使超过了INT（）内的显示宽度，也会自动扩充，所以我们一般不带后面的括号

2.浮点数类型

double(4,1)表示小数部分为1位，总宽度4位，宽度不会自动扩充

3.字符串类型

CHAR类型长度固定，VARCHAR类型的长度可变
VARCHAR可以根据字符串的实际长度来改变占用内存的大小

4.日期和时间类型

DATETIME表示的时间比DATE更具体

三：数据的增删改(DML)

我们可以用（SELECT * FROM t_student;）来查看表

DML_添加数据

注意事项

1. int 宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节
2. 时间的方式多样 ‘1256-12-23’ “1256/12/23” “1256.12.23”
3. 字符串不区分单引号和双引号
4. 如何写入当前的时间 now() , sysdate() , CURRENT_DATE()
5. char varchar 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。
6. 如果不是全字段插入数据的话，需要加入字段的名字

##往表中添加数据
INSERT INTO t_student VALUES(111,"小王","2班","男",18,"123@.com","2025.2.4");
INSERT INTO t_student VALUES(222,"小李","2班","男",14,"125@.com",NOW());
INSERT INTO t_student VALUES(222,"小放","3班","女",14,"125@.com",NOW());
INSERT INTO t_student VALUES(222,"小刚","3班","男",14,"135@.com",NOW());
INSERT INTO t_student (name,age,sex) VALUES("小孙",22,"女");## 上面第6点的例子

DML_修改，删除数据

注意事项

1. 关键字，表名，字段名不区分大小写
2. 默认情况下，内容不区分大小写 （大小写是一样的）
3. 删除操作from关键字不可缺少
4. 修改，删除数据别忘记加限制条件

## 修改表中的数据
UPDATE t_student SET sno=333 WHERE sex="女";
UPDATE t_student SET sno=444 WHERE name="小刚";
##删除表中数据
DELETE FROM t_student WHERE sno=111;
DELETE FROM t_student WHERE sno=222;

三：数据库表的增删改(DDL)

DDL_增加,修改，删除数据库表

增加列
在表尾增加

ALTER TABLE t_student ADD score double(5,2);

在表头增加

ALTER TABLE t_student ADD score double(5,2) FIRST;

在指定元素后增加(sex为指定元素)

ALTER TABLE t_student ADD score double(5,2) AFTER sex;

删除列

ALTER TABLE t_student DROP score;

修改列(两种)
modify修改是列的类型的定义，但是不会改变列的名字
change修改列名和列的类型的定义

ALTER TABLE t_student modify score int;
ALTER TABLE t_student change score scoressssss double(5,1);

快速添加表

结构和数据跟t_student 都是一致的

create table t_student2
as
select * from t_student;

结构跟t_student一致，数据没有

create table t_student3
as
select * from t_student where 1=2;

只要部分列，部分数据

create table t_student4
as
select sno,sname,age from t_student where sno = 2;

四:约束

1.非外键约束

1.主键约束 (PRIMARY KEY)

作用： 唯一标识表中的每一行记录
特点：
不允许NULL值
不允许重复值
一个表只能有一个主键

注意： 多字段主键的添加只能使用表级约束

2.非空约束 (NOT NULL)

作用： 规定了一张表中指定的某个字段的值不能为空

注意： 只能使用列级约束添加
为已存在表中的字段添加非空约束

alter table t_student modify stu_sex varchar(1) not null;

使用ALTER TABLE语句删除非空约束

alter table t_student modify stu_sex varchar(1) null;

3.唯一约束 (UNIQUE)

作用： 约束字段的值是唯一的
注意： 无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束

4.检查约束 (CHECK)

作用： 限制某个字段的取值范围
注意： 可以定义为列级约束，也可以定义为表级约束

5.默认值约束 (DEFAULT)

作用： 约束字段的默认值
注意： 默认值约束的设置与非空约束一样，也只能使用列级约束

6.自增约束 (AUTO_INCREMENT )

作用： 约束字段的值自动递增
注意： 自增约束通常会配合主键使用，并且只适用于整数类型

2.列级约束和表级约束

用列级约束创建数据库表

create table t_student(
        sno int PRIMARY KEY AUTO_INCREMENT, ##主键约束加自增约束
        name varchar(5) NOT NULL, ## 非空约束
		classname varchar(10),
        sex char DEFAULT "男" CHECK(sex="男"||sex="女"),##检查约束
        age int CHECK(age>=18 and age<=50),##检查约束
		email varchar(15) UNIQUE,##唯一约束
        enterdate date
);

用表级约束创建数据库表

create table t_student(
        sno int AUTO_INCREMENT,
        name varchar(5) NOT NULL, 
		classname varchar(10),
        sex char,
        age int,
		email varchar(15) UNIQUE,
        enterdate date,
		CONSTRAINT pk_key PRIMARY KEY (sno),
		CONSTRAINT ch_sex CHECK(sex="男"||sex="女"),
		CONSTRAINT ch_age CHECK(age>=18 and age<=50)
);

更推荐用表级约束来创建数据库表

3.创建表以后添加约束

create table t_student(
        sno int,
        name varchar(5) NOT NULL, 
		classname varchar(10),
        sex char DEFAULT "男",
        age int,
		email varchar(15) ,
        enterdate date
);
ALTER TABLE t_student ADD CONSTRAINT pk_key PRIMARY KEY (sno);
ALTER TABLE t_student MODIFY sno INT AUTO_INCREMENT
ALTER TABLE t_student ADD CONSTRAINT ch_sex CHECK(sex="男"||sex="女");
ALTER TABLE t_student ADD CONSTRAINT ch_age CHECK(age>=18 and age<=50);

用于表已经创建好了之后后续添加新的约束,若重新创建表的话原来表中的数据都要重新添加
可以使用DESC t_student; 语句查看表的结构来验证约束是否添加好了

4.外键约束 (FOREIGN KEY)

作用： 使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性
案例：
上面的没有用外键约束
下面用了外键约束:
主表（父表）：班级表 - 班级编号 - 主键
从表（子表）：学生表 - 班级编号 - 外键

##班级表
CREATE TABLE t_class(
		cno INT AUTO_INCREMENT, ## 班级编号
		cname VARCHAR(10),
		room VARCHAR(10),
		CONSTRAINT pk_key PRIMARY KEY (cno)
);
##学生表
create table t_student(
        sno INT AUTO_INCREMENT, 
        name VARCHAR(5) , 
		classno INT,## 班级编号
		CONSTRAINT pk_key PRIMARY KEY (sno),
		## 添加外键约束
		CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno)
);

添加外键约束后:
1.若添加了班级编号为2的学生那么再删除班级表中班级编号为2的班级会报错(若没有添加外键约束则不会,所以外键约束保持了数据的完整性)

## 添加学生数据
INSERT INTO t_student VALUES (null,"张三",1),(null,"李四",1),(null,"王五",2),(null,"六六",3);
## 删除班级
DELETE FROM t_class WHERE cno=2;

2.若添加学生时班级编号添加的数据在班级表中没有,则添加不成功

## 添加现有班级编号外的班级编号在学生上
INSERT INTO t_student VALUES (null,"小王",4);

上述两个代码都会报错
注意：

1. 外键列和被引用列的数据类型必须完全相同
2. 被引用的列必须是 PRIMARY KEY 或 UNIQUE 约束
3. 添加约束前，外键列的值必须已存在于被引用列中

5.外键策略

(1) NO ACTION 不允许操作主表(外键约束默认)

(2) CASCADE 级联操作:操作主表影响从表的外键信息

添加代码(在创建表时用表级约束创建)

CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno) ON UPDATE CASCADE ON DELETE CASCADE;

添加CASCADE 级联操作后可以删除班级表中的班级编号,并且会将使用该班级编号的学生表中的学生一起删除
删除班级表中班级编号为2的班级

DELETE FROM t_class WHERE cno=2;

删除前的班级表

删除前的学生表

删除后的班级表

删除后的学生表

(3) SET NULL 置空操作:操作主表不影响从表的外键信息

添加代码(在创建表时用表级约束创建)

CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno) ON UPDATE SET NULL ON DELETE SET NULL;

添加SET NULL 置空操作后可以删除班级表中的班级编号,并且不会将使用该班级编号的学生表中的学生一起删除

五:查询(DQL)

后续案例用的表

## 部门表
create table DEPT(  
  DEPTNO int(2) not null,  ## 部门编号
  DNAME  VARCHAR(14),  ## 部门名字
  LOC    VARCHAR(13)  ## 位置
);  

## 添加主键
alter table DEPT  
  add constraint PK_DEPT primary key (DEPTNO); 
        
## 员工表
create table EMP  (  
  EMPNO    int(4) primary key,  ## 员工编号
  ENAME    VARCHAR(10),  ## 名字
  JOB      VARCHAR(9),  ## 职位
  MGR      int(4),  ## 上级领导的编号
  HIREDATE DATE,   ## 入职日期
  SAL      double(7,2),  ## 工资
  COMM     double(7,2),  ## 
  DEPTNO   int(2)    
);  

## 添加外键
alter table EMP  
  add constraint FK_DEPTNO foreign key (DEPTNO)  
  references DEPT (DEPTNO);  
        
## 薪资等级表
create table SALGRADE  (  
  GRADE int primary key,   ## 工资等级编号
  LOSAL double(7,2),  ## 区间下限
  HISAL double(7,2)   ## 区间上限
);  

## 奖金表
create table BONUS  (  
  ENAME VARCHAR(10),  ## 名字
  JOB   VARCHAR(9),   ## 职位
  SAL   double(7,2),  ## 薪资
  COMM  double(7,2)  ## 奖金
);  

1.单表查询

简单的查询语句

去重操作 (DISTINCT)
对后面的所有列组合(job,deptno)去重 ，而不是单独的某一列去重

SELECT DISTINCT job,deptno FROM emp;

排序 (ORDER BY)
asc 升序，可以默认不写,desc 降序

SELECT * FROM emp ORDER BY sal,deptno DESC;

where语句的使用

where 子句 + 关系运算符
默认情况下不区分大小写, 但是binary区分大小写

select * from emp where job = 'clerk'; 
select * from emp where binary job = 'clerk';

where 子句 + 逻辑运算符：and,or

 ## 开区间并且升序排序
select * from emp where sal > 1500 and sal < 3000 order by sal;
##  闭区间[1500,3000]
select * from emp where sal between 1500 and 3000; 

select * from emp where deptno = 10 or deptno = 20;
## 在10和20中选一个,等效于or,并且当数据两多了之后比or更好用
select * from emp where deptno in (10,20); 

对比or推荐使用in语句
where子句 + 模糊查询
查询名字中带A的员工 – %代表任意多个字符 0,1,2，…

select * from emp where ename like '%A%'

_代表任意一个字符

## 查询A在第二个字符的数据
select * from emp where ename like '_A%' ;

关于null的判断

select * from emp where comm is null;
select * from emp where comm is not null;

除了多行函数（max,min,count,sum,avg），都是单行函数

单行函数 (每行输入 → 每行输出)

字符串函数
length: 求字符串的长度
substring: 字符串截取,从第二个字符开始截取长度为3的数据

select ename,length(ename),substring(ename,2,3) from emp;

数值函数

## dual实际就是一个伪表 
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; 

如果没有where条件的话，from dual可以省略不写
日期与时间函数
curdate() 年月日, curtime() 时分秒
now() 返回当前日期和时间 , sysdate 返回该函数执行时的日期和时间

select curdate(),curtime() ;

select now(),sysdate(),sleep(3),now(),sysdate() from dual;

流程函数
if(A,B,C) 如果A成立那么取值为B,否则就是C--------if-else 双分支结构
ifnull(comm,0) ,如果comm是null，那么取值为0 ------------ 单分支
case 如果数据等于’CLERK’ 则返回’店员’ , end结束

select empno,ename,sal,if(sal>=2500,'高薪','底薪') as '薪资等级' from emp;
select empno,ename,sal,comm,sal+ifnull(comm,0) from emp;

## case等值判断
select empno,ename,job,
case job 
 when 'CLERK' then '店员'
 when 'SALESMAN'  then '销售'
 when 'MANAGER' then '经理'
 else '其他'
end '岗位'
from emp;

## case区间判断
select empno,ename,sal,
case 
 when sal<=1000 then 'A'
 when sal<=2000 then 'B'
 when sal<=3000 then 'C'
 else 'D'
end '工资等级',
deptno from emp;

其他函数
database() 返回当前数据库名
user() 返回当前登录的用户名
version() 返回当前MySQL的版本号

select database(),user(),version() from dual;

多行函数 (也叫聚合或分组函数) (多行输入 → 单行输出)

注意:
1.max(),min(),count()针对所有类型 sum(),avg() 只针对数值型类型有效
2.多行函数自动忽略null值
COUNT() 统计表中记录的数目
SUM() 计算指定字段值的总和
AVG() 计算指定字段值的平均值
MAX() 统计指定字段值的最大值
MIN() 统计指定字段值的最小值

select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;

group by分组

group by : 用来进行分组

## 将deptno分组后求每一类deptno中sal的平均值
select deptno,avg(sal) from emp group by deptno;

若没有分组(错误案例)

select deptno,avg(sal) from emp

系统不明白代码要做什么(代码有歧义)所以会报错

having分组后筛选

目标1: 统计各个部门的平均工资 ,只显示平均工资2000以上的

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;

统计完各部门工资后再筛选
目标2: 统计各个岗位的平均工资,除了MANAGER

-- 方法1：
select job,avg(sal) from emp where job != 'MANAGER' group by job;
-- 方法2：
select job,avg(sal) from emp group by job having job != 'MANAGER' ;

我们发现where在分组前进行过滤的，having在分组后进行后滤

总结:

select column, group_function(column)（可以用函数）
from table 　　（要操作的表）
[where condition] 　（分组前过滤）
[group by group_by_expression] （分组）
[having group_condition] （分组后过滤）
[order by column]; （排序）
注意： 顺序固定，不可以改变顺序

select语句的执行顺序
from–where – group by– select - having- order by

2.多表查询

交叉连接 (CROSS JOIN)

SELECT *
FROM emp
CROSS JOIN dept;

运行结果

我们发现有56条记录(56=14*4)
原因:交叉连接是对两个或者多个表进行笛卡儿积操作,就是把所有能匹配的方式都匹配一遍
缺点 : 同名列展示的次数太多了没有实际意义。于是引入自然连接

自然连接 (NATURAL JOIN)

优点 : 自动匹配所有的同名列 ,同名列只展示一次

SELECT empno,ename,sal,dname,loc
FROM emp
NATURAL JOIN dept;

缺点1： 查询字段的时候，没有指定字段所属的数据库表，效率低。于是我们可以指定表名并且可以给表名起别名
改正后：

SELECT e.empno,e.ename,e.sal,d.dname,d.loc
FROM emp e
NATURAL JOIN dept d;

缺点2： 自动匹配表中所有的同名列，但是有时候我们希望只匹配指定的同名列。
解决： 内连接 - using子句

内连接 (INNER JOIN)

1.USING子句

SELECT *
FROM emp e
INNER JOIN dept d;
USING(deptno)

优点： 可以只匹配直接指定的同名列
缺点： 关联的字段，必须是同名的，若不同名则要用ON子句
2.ON子句

SELECT *
FROM emp e
INNER JOIN dept d
ON(e.deptno=d.deptno)

上述代码代表：指定e表中的deptno和d表中的deptno匹配

外连接

1. 我们先手动把emp表中的BLAKE员工的deptno调为NULL（用于后续对比左外连接与右外连接）

2. 我们发现dept表中有deptno为40的值，但是emp表中没有

综上： 如果用内连接的话我们查询出来的表中会没有BLAKE（他的deptno为NULL，与dept表不匹配）和deptno为40（与emp表不匹配，因为emp表中没有deptnt为40的人）的那两个数据（查询出的记录为13条）。所有当我们不想忽略这两个记录的话就要使用外连接

左外连接（LEFT OUTER JOIN）

概念： 左面的那个表的信息，即使不匹配也可以查看出效果

SELECT *
FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

运行上述代码后我们发现BLAKE也被展示出来了（查询出的记录为14条）

右外连接（RIGHT OUTER JOIN）

概念： 右面的那个表的信息，即使不匹配也可以查看出效果

SELECT *
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

运行上述代码我们发现deptno为40的记录也被展示出来了（查询出的记录为14条）

全外连接（UNION / UNION ALL）

概念： 左右两表取并集
UNION：去重 效率低
UNION ALL：不去重 效率高

SELECT *
FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno
UNION  ## 去重 效率低
SELECT *
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

SELECT *
FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno
UNION ALL  ## 不去重 效率高
SELECT *
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

用UNION运行后有15条记录，用UNION ALL运行后有75条记录（因为没去重，笛卡尔乘积 15*5=75）

三表连接

本质上是将前面连接的两个表当作一个表后再连接一个新表
查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,s.*
FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno
INNER JOIN salgrade s
ON e.sal BETWEEN s.losal and s.hisal;

自连接

可以将一张表看作两张表

SELECT e1.empno as 员工编号,e1.ename as 员工姓名,e2.empno as 领导编号,e2.ename as 领导姓名
FROM emp e1
LEFT OUTER JOIN emp e2
ON e1.mgr=e2.empno;

运行结果

3.子查询

不相关子查询

子查询：一条SQL语句含有多个select
执行顺序： 先执行子查询，再执行外查询；
不相关子查询： 子查询可以独立运行，称为不相关子查询。

单行子查询

单行子查询只返回单个值
查询和CLARK同一部门且比他工资低的雇员名字和工资

select ename,sal
from emp
where deptno = (select deptno from emp where ename = 'CLARK') 
      and 
      sal < (select sal from emp where ename = 'CLARK')

多行子查询

多行子查询返回多行一列的结果（一组值）

1. IN：检查值是否存在于子查询结果集中，等价于 = ANY

2. NOT IN：检查值是否不存在于子查询结果集中，等价于 <> ALL

3. ANY：与子查询结果集中的任意一个值比较

4. ALL与子查询结果集中的所有值比较

查询工资低于任意一个“CLERK”的工资的雇员信息

select * 
from emp
where sal < any(select sal from emp where job = 'CLERK')
and job != 'CLERK'

相关子查询

• 子查询不能独立执行，依赖于外部查询的当前行
• 子查询需要为外部查询的每一行执行一次

好处： 简单 功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）
缺点： 稍难理解

查询本部门最高工资的员工
通过不相关子查询

select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
union
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
union
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30)

缺点：语句比较多，具体到底有多少个部分未知，若有100个需要全部写出来
通过相关子查询

select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) 

六:事务

事务及其特征

事务（Transaction） 是用来维护数据库完整性的，它能够保证一系列的MySQL操作要么全部执行，要么全不执行
事务的概念：
事务是数据库管理系统中的一个核心概念，它代表一组不可分割的数据库操作序列，这些操作要么全部执行成功，要么全部不执行
事务的特性：（ACID）
1.原子性(Atomicity)：
事务是不可分割的工作单位，事务中的操作要么全部完成，要么全部不执行。
如银行转账：A转给B 100元，必须保证A账户扣款和B账户入账同时成功或同时失败

2.一致性(Consistency)：
事务执行前后，数据库从一个一致状态变到另一个一致状态
不会破坏数据库的完整性约束(如外键约束、唯一约束等)

3.隔离性(Isolation)：
多个事务并发执行时，一个事务的执行不应影响其他事务
通过隔离级别控制事务间的可见性

4.持久性(Durability)：
事务一旦提交，其结果就是永久性的
即使系统故障，已提交事务的结果也不会丢失

事务并发问题

脏读（Dirty read）：
当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”

不可重复读（Unrepeatableread）：
在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读

幻读（Phantom read）：
幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读

不可重复度和幻读区别：
1.不可重复读的重点是修改，幻读的重点在于新增或者删除
2.解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离级别

如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题

MySQL默认的事务隔离级别是repeatable read

设置事务的隔离级别 （设置当前会话的隔离级别）

set session transaction isolation level read uncommitted;  
set session transaction isolation level read committed;  
set session transaction isolation level repeatable read;  
set session transaction isolation level serializable;

开启事务

start transaction;

回滚（刚才的操作全部取消）

rollback;

手动提交（结束事务）

commit;