1. 创建数据库
-- 语法 注意分号结束
create database [IF NOT EXISTS] 数据库名;
-- 实例
create database db1 charset=utf8mb4;2. 查询数据库
-- 语法
show databases;3. 删除数据库
-- 语法
drop database [IF EXISTS] 数据库名;
-- 实例
drop database db1;4. 使用数据库
4.1. 查看正在使用的数据库
-- 语法
select database();4.2. 切换正在使用的数据库
-- 语法
use 数据库名;
-- 实例
use db1; -- 一定要保证这个schema是存在的注意：
上面的语句的database也可以用schema代替
5. 数据表操作
5.1. 创建数据表
5.1.1. 创建数据表
-- 语法
create table 表名(
字段名1 字段类型1(字段长度), -- 不是;号
字段名2 字段类型2(字段长度),
....
字段名n 字段类型n(字段长度) -- 最后一列后面是没有,的
);
-- 实例
create table student(
id int(11),
name varchar(20),
birthday date,
address varchar(100),
score double(5,2),
email varchar(64),
tel char(11)
);5.1.2. 常见数据类型
| 大分类 | 类型 | 描述 |
| 数字类型 | int | 整型 |
| float | 单精度浮点数类型 | |
| double | 双精度浮点数类型 | |
| 字符类型 | varchar | 可变长度字符串类型，它的长度可以由我们自己指定，默认长度365, 最大值是65535，使用效率低,节省磁盘存储空间 |
| char | 固定长度字符串类型，如果没有指定长度，默认长度是255，查询效率高, 浪费磁盘存储空间 | |
| 日期类型 | date | 日期，格式：yyyy-MM-dd |
| datetime | 日期时间，格式：yyyy-MM-dd HH:mm:ss，占用8字节的存储空间 |
6. 查询数据表
6.1. 查询所有表
-- 语法
show tables;6.2. 查看表结构
-- 语法
desc 数据表名;
-- 实例
desc student;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
| score | double(5,2) | YES | | NULL | |
| email | varchar(64) | YES | | NULL | |
| tel | varchar(15) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+7. 修改数据表
7.1. 添加一列
-- 语法
alter table 表名 add 字段名 列类型
-- 实例
alter table student add description varchar(200);7.2. 修改类型
-- 语法
alter table 表名 modify 列名 新列类型
-- 实例
alter table student modify description char(230);7.3. 修改列名和类型
-- 语法
alter table 表名 change 旧列名 新列名 列类型
-- 实例
alter table student change description intro varchar(100);7.4. 删除指定列
-- 语法
alter table 表名 drop 列名;
-- 实例
alter table student drop intro;7.5. 修改表名
-- 语法
rename table 旧表名 to 新表名;
-- 实例
rename table student to stu;7.6. 删除数据表
-- 语法
drop table 表名;
-- 实例
drop table stu;8. 增删改数据
8.1. 插入数据
8.1.1. 基本方式
-- 语法
insert into 表名(字段1,字段2,.....字段n) values(值1,值2,.....值n)
-- 实例
insert into student(id,name,birthday,address,score,email,tel) values(1,'工藤','1990-01-01','西虹市',1.25,'123@qq.com','15512344321');
insert into student(id,name,birthday) values(5,'小兰','1990-01-01');
insert into student(id,name) values(6,'小五郎');
insert into student(id,name,birthday) values(6,'小五郎',null);注意：
- 插入数据的时候，列名和值要一一对应(包括数量 顺序 类型)
- 插入数据的时候，数值之外的类型需要使用引号引起来，推荐使用单引号
- 所有列的值都可以传入字符串类型的值，MySQL底层有隐式转换机制
- 当一列中不需要传入值的时候，可以使用null代替值
8.1.2. 批量插入
-- 语法
insert into 表名(字段1,字段2,.....字段n) values(值1,值2,.....值n),(值1,值2,.....值n),(值1,值2,.....值n);
-- 实例
insert into student(id,name,birthday) values(1,'工藤','1990-01-01'),(5,'小兰','1990-01-01'),(6,'小五郎',null);8.1.3. 省略列名
注意:
值的数量一定要保证跟列的数量一致，如果值中没有对应的列，使用null补齐
-- 语法
insert into 表名 values(值1,值2,.....值n)
-- 实例
insert into student values(8,'灰原哀','1991-01-01','西虹市',1.55,'223@qq.com','15512344322');
insert into student values(6,'小五郎'); -- Column count doesn't match value count at row 1 在保存数据的时候, 值和列数量上不匹配8.2. 修改数据
-- 语法
update 表名 set 字段1=值1,字段2=值2 [where 条件字段=值]
-- 实例
-- 先根据where后面的条件找到对应的数据（可能是多条数据），然后修改对应的字段的数据
-- 修改工藤的name为柯南,birthday为2000-01-01
update student set name='柯南',birthday='2000-01-01' where name='工藤';
-- 修改id为6的 name为小六郎
update student set name='小六郎' where id=6;注意 ：
- 修改语句中如果不加条件，则将所有数据都会被修改！
- set中没有处理的字段是不会被修改的
8.3. 删除数据
-- 语法
delete from 表名 [where 条件字段=值]
-- 实例
delete from student where name = '柯南';注意：
删除语句中如果不加条件，则将所有数据都会被删除！
9. 查询数据
9.1. 单表查询
9.1.1. 简单查询
9.1.1.1. 查询所有列
-- 语法
select * from 表名
-- 实例
select * from student1;9.1.1.2. 查询部分列
-- 语法
select 列1名,列1名,...列2名 from 表名
-- 实例
select name,chinese from student1;9.1.1.3. 去重
-- 语法
select distinct 列名 from student1;
-- 实例 name相同的只显示一个
select distinct name from student1;
-- 实例 id和name都相同的只显示一个
select distinct id,name from student1; 9.1.1.4. 四则运算
-- 语法
select 列(四则) from student1;
-- 实例
select name,math,math+10 from student1;9.1.1.5. 空值处理
-- 语法
1. null跟其它值进行四则运算,得到结果都是null
2. ifnull(列,默认值) 当指定的列的值为null的时候,使用后面的默认值进行替换
-- 实例
select name,chinese+math+english from student1;
select name,chinese+math+ifnull(english,0) from student1;9.1.1.6. 别名
-- 语法
select 列 [as] 别名 from 表名9.1.2. 条件查询
9.1.2.1. 关系运算符
-- 语法：
select * from 表名 where 条件( = >= <= != <> )
-- 实例：
-- 查询age等于20岁的学生
select * from student2 where age=20;
-- 查询age不等于20岁的学生
select * from student2 where age!=20;
-- 查询math分数大于80分的学生
select * from student2 where math>80;
-- 查询english分数小于或等于80分的学生
select * from student2 where english<=80;9.1.2.2. 逻辑运算符
-- 语法：
and 并且
or 或者
-- 实例：
-- 查询age等于20岁的学生并且math分数大于80分的学生
select * from student2 where age=20 and math>80;
-- 查询age等于20岁的学生或者math分数大于80分的学生
select * from student2 where age=20 or math>80;
-- 查询id是1或3或5的学生
select * from student2 where id=1 or id=3 or id=5;
-- 查询english成绩大于等于77，且小于等于87的学生
select * from student2 where english>=77 and english<=87 ;9.1.2.3. 范围关键字
-- 语法：
字段 in (值1,值2,....值n) 表示字段=集合中的任何一个都可以
字段 not in (值1,值2,....值n) 表示字段不在集合中才可以
字段 between 开始值 and 结束值 查询的字段的值要在 [开始 , 结束]
字段 not between 开始值 and 结束值 查询的字段的不值要在 [开始 , 结束]
-- 实例：
-- 查询id是1或3或5的学生
select * from student2 where id in (1,3,5);
-- 查询id不是1或3或5的学生
select * from student2 where id not in (1,3,5);
-- 查询english成绩大于等于77，且小于等于87的学生
select * from student2 where english between 77 and 87;9.1.2.4. null关键字
-- 语法：
is null 为空
is not null 不为null
注意: null是不能跟字段进行关系运算符匹配( 不能 =null !=null)
-- 实例：
-- 查询英语成绩为null的学生
select * from student2 where english is null;
-- 查询英语成绩不为null的学生
select * from student2 where english is not null;9.1.2.5. 模糊匹配
- like
- rlike
语法：
- 字段 like ''
- % 匹配0个或者多个字符
- _ 匹配1个字符
-- 实例：
-- 查询姓马的学生
select * from student2 where name like '马%';
-- 查询姓名中包含'德'字的学生
select * from student2 where name like '%德%';
-- 查询姓马，且姓名有三个字的学生
select * from student2 where name like '马__';
9.1.3. 高级查询
9.1.3.1. 排序
- asc ascending 升序
- desc descending 降序
语法：
- order by 字段1 [asc|desc] , 字段2 [asc|desc]
- 注意: null字段在排序时,是按照最小值处理
-- 实例：
-- 查询所有数据,使用年龄降序排序
SELECT * from student2 ORDER BY age desc;
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * from student2 ORDER BY age desc,math desc;
-- 排序有null值的列
SELECT * from student2 ORDER BY english desc;9.1.3.2. 结果截取、分页
语法：
limit 跳过几条,截取多少条
展示第几页的内容：（页码-1）* 每页的数量
-- 实例：
-- 查询学生表中数据，从第三条开始显示，显示4条
select * from student2 limit 2,4;
-- 查询学生表中数据，显示前4条
select * from student2 limit 0,4;
select * from student2 limit 4; -- 如果从0开始截取,开始索引可以省略
-- 每页显示3条 （页码-1）* 每页的数量
select * from student2 limit 0 3; 0*3 1-3
select * from student2 limit 3 3; 1*3 4- 6
select * from student2 limit 6 3; 2*3 7- 99.1.3.3. 聚合函数
功能：
- 对一列数据进行计算，返回一个结果，忽略null值
- 对一列数据,Mysql可以统计出: 个数count 总和sum 平均数avg 最大值max 最小值min
-- 语法：
select 聚合函数(字段) from 表名
-- 实例：
-- 查询学生总数
select count(*) from student2;
-- 查询数学成绩总分
select sum(math) from student2;
-- 查询数学成绩平均分
select avg(math) from student2;
-- 查询数学成绩最高分
select max (math) from student2;
-- 查询数学成绩最低分
select min (math) from student2;9.1.3.4. 分组
注意：
- 一旦分组,只能查询被分组的那个字段, 和聚合函数. 想看其他字段,需要使用GROUP_CONCAT来拼接
-
- 如果不拼接 有2种情况
-- 语法：
select 分组列名,聚合函数() from 表名 group by 列名; ---按照GROUP BY 后面的列名分组
分组,一般都是和聚合函数联合使用
-- 实例：
-- 查询所有学生, 按性别分组, 统计每组的人数
select sex,count(*) from student2 GROUP BY sex;
-- 查询所有学生, 按性别分组, 统计每组的人数 这里查询了name 会只显示一个名字或者报错
select sex,count(*),name from student2 GROUP BY sex;
-- 查询所有学生, 按性别分组, 统计每组的人数 GROUP_CONCAT可以把分组后的name的数据拼接显示
select sex,count(*),GROUP_CONCAT(name) from student2 GROUP BY sex;
-- 查询年龄大于25岁的人, 按性别分组, 统计每组的人数
SELECT sex,count(*) from student2 where age>25 GROUP BY sex;
-- 查询男女的数学平均分 只统计年龄大于40的
SELECT sex,AVG(math) from student2 where age>40 GROUP BY sex;
-- GROUP_CONCAT可以把分组后 其它字段的数据拼接后显示
SELECT sex,AVG(math),GROUP_CONCAT(NAME),GROUP_CONCAT(age) FROM student2 WHERE age>40 GROUP BY sex ;
+------+-----------+----------------------------+-------------------+
| sex | AVG(math) | GROUP_CONCAT(NAME) | GROUP_CONCAT(age) |
+------+-----------+----------------------------+-------------------+
| 女 | 98.0000 | 马化腾 | 45 |
| 男 | 73.6667 | 马云,马景涛,刘德华 | 55,55,57 |
+------+-----------+----------------------------+-------------------+9.1.3.5. 过滤
分组之后的条件过滤使用having
-- 语法：
group by 分组 having 过滤条件
1. 过滤,一般都是和分组联合使用
2. 分组之前的条件过滤使用where 分组之后的条件过滤使用having
-- 实例：
-- 查询年龄大于25岁的人, 按性别分组,统计每组的人数, 并只显示性别人数大于2的数据
select sex,count(*) from student2 where age > 25 group by sex having count(*) > 2;
-- 查询男女的数学平均分 保留平均分大于90的
select sex,avg(math) from student2 group by sex having avg(math)>90;9.1.3.6. 高级查询的顺序
- 排序: order by 列 [asc|desc]
- 截取: limit 开始,几个
- 聚合函数: count(列)
- 分组: group by 列
- 过滤: having 过滤条件
- 普通条件: where
select 聚合函数() from 表 [where 条件] [group by 分组] [having 过滤] [order by 排序] [limit 截取]
9.2. 多表查询
9.2.1. 连接查询
9.2.1.1. 交叉连接(笛卡尔积)
解释：
使用左表中的每一条数据分别去连接右表中的每一条数据, 将所有的连接结果都展示出来
-- 语法
select * from 左表,右表
-- 案例
-- 查询两张表的数据
select * from emp,dept
-- 查询两张表的数据 添加过滤条件 员工和部门要有对应关系的数据 才显示
select * from dept,emp where emp.dept_id = dept.id;9.2.1.2. 内连接
解释 ：
使用左表中的每一条数据分别去连接右表中的每一条数据, 仅仅显示出匹配成功的那部分
-- 语法
内连接: select * from 左表 [inner] join 右表 on 连接条件
-- 案例
-- 显示内连接
select * from emp e inner join dept d on e.dept_id = d.id; -- 业界说显示内连接效率高于隐式内连接注意：
- 一般两张表的时候用隐式和显示都可以
- 当表的数据多的时候 用显示的 , 隐式的会先计算笛卡尔积,浪费空间和效率
9.2.1.3. 左外连接
解释：
首先要显示出左表的全部, 然后使用连接条件匹配右表,能匹配中的就显示,匹配不中的显示为null
-- 语法
select * from 左表 left outer join 右表 on 连接条件
-- 案例 outer可以省略
select * from emp e left outer join dept d on e.dept_id = d.id; //左边的表emp全部显示 右边 的表dept只显示有关联的数据
select * from dept d left outer join emp e on e.dept_id = d.id; //左边的表dept全部显示 右边 的表emp只显示有关联的数据9.2.1.4. 右外连接
解释：
首先要显示出右表的全部, 然后使用连接条件匹配左表,能匹配中的就显示,匹配不中的显示为null
-- 语法
select * from 左表 right outer join 右表 on 连接条件
select * from dept d right join emp e on e.dept_id = d.id;9.2.1.5. UNION的使用
合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并
时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION
ALL关键字分隔。
语法格式：
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2UNION 和 UNION ALL区别：
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：
执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效
率。
9.2.1.6. 7种SQL JOINS的实现
代码实现：
#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULLSELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL语法格式小结：
左中图：
#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;右中图：
#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;左下图：
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句9.2.2. 子查询
一个查询使用了另一个查询的结果
-- 案例:
-- 1. 先查询最高工资是多少? 7200
select max(salary) from emp;
-- 2. 谁的工资是上面的结果?
select * from emp where salary = 7200;
-- 3. 合并
select * from emp where salary = (select max(salary) from emp);10. 数据库约束
约束用于对表中的数据进行进一步的限制，一般作用在表中的字段上，用于保证数据的正确性。
约束种类有：主键约束、唯一约束、非空约束、默认值、外键约束。
10.1. 主键约束
作用：唯一的代表一条数据记录
特点：唯一、非空
要求: 尽量使用不具有实际意义的字段作为主键
-- 语法:
-- 1) 创建注解约束
create table 表名(
列名 列类型 primary key,
)
-- 2) 配合自增器一起设置
-- 自增器用于主键列的自增长，每次都会在数据库原有值的基础上进行+1操作
create table 表名(
列名 列类型 primary key auto_increment, -- 主键 + 自增器
)10.2. 唯一约束
作用：限制某一列的数据是唯一的,不能重复
特点：被唯一约束管理的列 值不能重复, 但是允许为NULL
-- 语法:
create table 表名(
列名 列类型 unique
)10.3. 非空约束
作用：限制某一列数据的值不能为空
-- 语法:
create table 表名(
列名 列类型 not null
)10.4. 默认值
作用：当不向某一列传入值的时候,使用默认值
-- 语法:
create table 表名(
列名 列类型 default '默认值'
)注意：
- 只要我们传值了,就用我们的,哪怕传的是null
- 只有不传值的情况下,才使用默认值
10.5. 外键约束
作用：限定两张表有关系的数据，保证数据的正确性、有效性和完整性。
-- 添加外键约束
1 创建表的时候添加
create table 表名(
列名 列类型,
列名 列类型,
列名 列类型,
列名 列类型,
列名 列类型,
[constraint 约束名: xxx_fk] foreign key (列名) references 主表(主键)
)
-- 创建班级表
create table class(
id int primary key,
name varchar(30)
);
-- 创建学生表
create table student(
id int primary key,
name varchar(30),
class_id int,
CONSTRAINT fk_student_class_id FOREIGN key (class_id) REFERENCES class(id)
);
-- constraint表示添加一个外键约束
-- fk_从表名_外键字段 给外键约束起的名字 注意约束的名字 在同一个数据库内 不能重名
-- foreign key(class_id) 把class_id作为外键
-- references class(id) 关联 class表中的主键id
-- 插入数据
insert into class values(1,'JavaEE166'),(2,'JavaEE167');
insert into student values(1,'张三',1),(2,'李四',1),(3,'王五',2);
insert into student values(null,'xiaoming',3);
-- 2 创建表之后单独添加的方式
alter table 表名 add [constraint 约束名] foreign key (列名) references 主表(主键)
alter table student add constraint class_id_fk foreign key (class_id) references class(id);
-- 删除外键约束
alter table 表名 drop foreign key 约束名
alter table student drop foreign key class_id_fk;外键约束的特点
- 对于主表来说，不能删除被从表引用的数据；删除数据的时候，要先删除从表数据
- 对于从表来说，不能添加主表中不存在的数据,添加数据的时候，要先添加主表数据；
如果创建表的时候没有添加外键,那么可以用修改的形式后期添加 ,如下:
alter table 表名 add [constraint 约束名] foreign key (列名) references 主表(主键)
alter table student add constraint student_class_id_fk foreign key (class_id) references class(id);注意：
必须表中没有错误数据 才能添加约束成功
11. 事务
一个业务操作包含多个子操作，如果这个业务操作被事务管理了，那么这些子操作要么同时成功，要么同时失败。
在MySQL数据库中，默认情况下，一条DML语句就是一个独立的事务。
事务锁 : 一旦开启事务，在提交之前，其它的账户无法操作这个表，会一直等，直到超时
MySQL中提供了三个方法,来操作事务：
- begin; 开启事务
- commit; 提交事务
- rollback; 回滚事务
-- 需求: 实现这样一个业务, 从AA向BB账户转10元钱
-- 1. 从AA账户中减去10元
update account set money = money - 10 where NAME = 'AA';
-- 出现了一些问题,导致第二条语句执行不了了
-- 2. 向BB账户中添加10元
update account set money = money + 10 where NAME = 'BB';
-- 想法: 想让上面这两条语句变成一个整体, 要么都成功,要么都失败
-- 在数据库中,使用事务来解决这个问题
-- 一个业务操作包含多个子操作，如果这个业务操作被事务管理了，那么这些子操作要么同时成功，要么同时失败。
-- MySQL中提供了三个方法,来操作事务
-- begin; 开启事务
-- commit; 提交事务
-- rollback; 回滚事务
begin; -- 开启事务 或者用 start transaction
update account set money = money - 10 where NAME = 'AA';
update account set money = money + 10 where NAME = 'BB';
commit; -- 提交事务11.1. rollback
- 事务内回滚 默认回滚到 事务开启的时候
- 可以设置回滚点 savepoint 回滚点的名字
-
- rollback to savepoint 回滚点的名字
begin; -- 开启事务
update account set money = money - 100 where NAME = 'AA';
update account set money = money + 100 where NAME = 'BB';
savepoint aa;
update account set money = money - 200 where NAME = 'AA';
update account set money = money + 200 where NAME = 'CC';
commit; -- 如果正常执行完 就可以commit了
rollback to savepoint aa -- 如果在a给c转账时出了异常 那么就执行回滚11.2. 实际开发中
try{
begin; -- 开启事务
业务1;
设置回滚点
业务2;
int a = 1/0;
业务3;
commit; -- 提交事务
}catch(e){
rollback to savepoint aa; --回滚事务
commit
}11.3. autocommit
- 0 表示手动提交,1 表示自动提交
- MySQL 在自动提交模式下，每个 SQL 语句都是一个独立的事务
- mysql的autocommit（自动提交）默认是开启，其对mysql的性能有一定影响
- 举个例子来说，如果你插入了1000条数据，mysql会commit1000次的，如果我们把autocommit关闭掉，通过程序来控制，只要一次commit就可以了。
查看 MySQL 客户端的事务提交方式命令：select @@autocommit;
修改 MySQL 客户端的事务提交方式为手动提交命令：set @@autocommit = 0;
- 当我们把autocommit 设置为0 那么就关闭了自动的事务提交,注意之前是默认每条语句都会提交一次事务
-
- 关闭以后, 执行sql语句的时候,事务依然是自动开启的,只不过不会再自动提交事务了,需要自己去手动commit
- 如果没有关闭autocommit , 那么我们自己开启的事务 比如执行begin, 只能我们自己提交,不会自动提交
11.4. 事务特性
- 原子性：atomicity 一个事务操作是不可分割的整体，一个事务中的操作一定是要么全部成功，要么全部失败的。
- 一致性：consistency 一个事务执行前后，数据库的状态是一致的。
- 隔离性：isolation 当多个事务同时执行的时候，互相是不会产生影响的。
- 持久性：durability 当事务执行完毕之后(提交了)，其对数据库产生的影响一定会持久的保存在磁盘上.
11.5. 事务隔离性带来的问题
- 脏读：一个事务读取到了另外一个事务没有提交的数据
- 不可重复读：一个事务读取到了另外一个事务修改的数据（修改并提交）
- 幻读（虚读）：一个事务读取到了另外一个事务新增的数据（新增）
11.6. 事务隔离级别
MySQL提供了事务隔离级别来解决这些问题。在MySQL中共支持四种隔离级别，分别是：
| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
| 1 | 读未提交 | read uncommitted | 是 | |||
| 2 | 读已提交 | read committed | 否 | 是 | Oracle | |
| 3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL |
| 4 | 串行化 | serializable | 否 | 否 | 否 |
select @@tx_ISOLATION; 查看隔离级别 -- mysql5.7
select @@transaction_isolation; -- mysql8.0
set global transaction isolation level repeatable read;
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }- 设置隔离级别为 读已提交 :read UNCOMMITTED
-
- 演示不可重复读现象
- 开启1和2两个事务
- 1事务 修改数据 然后不提交
- 2事务可以查询到1事务没有提交的数据 这就是脏读
- 设置隔离级别为 读已提交 :read committed
-
- 演示不可重复读现象
- 开启1和2两个事务
- 1事务 修改数据 然后提交
- 2事务可以查询到1事务提交后的数据
update account set money = money - 10 where NAME = 'AA';- 设置隔离级别为 可重复度: repeatable read
-
- 演示幻读现象
- 开启1和2两个事务
- 1事务 添加数据 然后提交
- 2事务 查询不到1添加的数据,但是在添加同样的数据的时候会提示已经存在
- 设置隔离级别为 串行化: serializable
-
- 开启1和2两个事务
- 1事务 查询一下数据
- 2事务 添加数据发现卡住了无法执行 这时候把1事务提交 2事务才会继续执行
12. MySQL索引
12.1. 索引的分类
- 功能分类
-
- 普通索引： 最基本的索引，它没有任何限制。
- 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
- 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
- 组合索引：顾名思义，就是将单列索引进行组合。
- 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
- 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
- 结构分类
-
- B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
- Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。
12.2. 索引的操作
12.2.1. 创建索引
注意：
如果一个表中有一列是主键，那么就会默认为其创建主键索引！(主键列不需要单独创建索引)
-- 标准语法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型] -- 默认是B+TREE
ON 表名(列名...);
-- 为student表中姓名列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);
-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);12.2.2. 查看索引
-- 标准语法
SHOW INDEX FROM 表名;
-- 查看student表中的索引
SHOW INDEX FROM student;12.2.3. 添加索引
-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);
-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
-- 为student表中name列添加全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);
-- 查看student表中的索引
SHOW INDEX FROM student;12.2.4. 删除索引
-- 标准语法
DROP INDEX 索引名称 ON 表名;
-- 删除student表中的idx_score索引
DROP INDEX idx_score ON student;
-- 查看student表中的索引
SHOW INDEX FROM student;12.3. 联合索引的特点
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，
对列name列、address和列phone列建一个联合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone);联合索引index_three实际建立了(name)、(name,address)、(name,address,phone)三个索引。所以下面的三个SQL语句都可以命中索引。
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
SELECT * FROM user WHERE name = '张三' AND address = '北京';
SELECT * FROM user WHERE name = '张三';上面三个查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引
(name,address,phone)
(name,address)
(name)进行数据匹配。 索引的字段可以是任意顺序的，如：
-- 优化器会帮助我们调整顺序，下面的SQL语句都可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。
联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。
-- 联合索引中最左边的列不包含在条件查询中，下面的SQL语句就不会命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345';13. 流程控制
解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。流程控制语句的作用就是控
制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分。只要是执行的程序，流程
就分为三大类：
- 顺序结构 ：程序从上往下依次执行
- 分支结构 ：程序按条件进行选择执行，从两条或多条路径中选择一条执行
- 循环结构 ：程序满足一定条件下，重复执行一组语句
针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。
- 条件判断语句 ：IF 语句和 CASE 语句
- 循环语句 ：LOOP、WHILE 和 REPEAT 语句
- 跳转语句 ：ITERATE 和 LEAVE 语句
13.1. 分支结构之 IF
- IF 语句的语法结构是：
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF根据表达式的结果为TRUE或FALSE执行相应的语句。这里“[]”中的内容是可选的。
特点：① 不同的表达式对应不同的操作 ② 使用在begin end中
举例1：
IF val IS NULL
THEN SELECT 'val is null';
ELSE SELECT 'val is not null';
END IF;举例2：声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
FROM employees WHERE employee_id = emp_id;
IF emp_salary < 8000 AND hire_year > 5
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
END IF;
END //
DELIMITER ;13.2. 分支结构之 CASE
CASE 语句的语法结构1：
#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）CASE 语句的语法结构2：
#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）举例1：
使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。
CASE val
WHEN 1 THEN SELECT 'val is 1';
WHEN 2 THEN SELECT 'val is 2';
ELSE SELECT 'val is not 1 or 2';
END CASE; 举例2：
使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。
CASE
WHEN val IS NULL THEN SELECT 'val is null';
WHEN val < 0 THEN SELECT 'val is less than 0';
WHEN val > 0 THEN SELECT 'val is greater than 0';
ELSE SELECT 'val is 0';
END CASE;举例3：声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。
DELIMITER //
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE bonus DECIMAL(3,2);
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
CASE
WHEN emp_sal<9000
THEN UPDATE employees SET salary=9000 WHERE employee_id = emp_id;
WHEN emp_sal<10000 AND bonus IS NULL
THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
END CASE;
END //
DELIMITER ;13.3. 循环结构之LOOP
LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子
句），跳出循环过程。
LOOP语句的基本格式如下：
END LOOP [loop_label]其中，loop_label表示LOOP语句的标注名称，该参数可以省略。
举例1：
使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id +1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
“update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
DECLARE avg_salary DOUBLE;
DECLARE loop_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_salary FROM employees;
label_loop:LOOP
IF avg_salary >= 12000 THEN LEAVE label_loop;
END IF;
UPDATE employees SET salary = salary * 1.1;
SET loop_count = loop_count + 1;
SELECT AVG(salary) INTO avg_salary FROM employees;
END LOOP label_loop;
SET num = loop_count;
END //
DELIMITER ;13.4. 循环结构之WHILE
WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如 果为真，就执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直
至循环条件为假，退出循环。
WHILE语句示例，i值小于10时，将重复执行循环过程，代码如下：
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;
SELECT i;
END //
DELIMITER ;
#调用
CALL test_while();举例2：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程 “update_salary_while()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数
DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE while_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
WHILE avg_sal > 5000 DO
UPDATE employees SET salary = salary * 0.9;
SET while_count = while_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
SET num = while_count;
END //
DELIMITER ;13.5. 循环结构之REPEAT
REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循
环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会
就继续执行循环，直到满足退出条件为止。
REPEAT语句的基本格式如下:
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至 expr_condition为真。
举例1：
DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
UNTIL i >= 10
END REPEAT;
SELECT i;
END //
DELIMITER ;举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
“update_salary_repeat()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE repeat_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
REPEAT
UPDATE employees SET salary = salary * 1.15;
SET repeat_count = repeat_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
UNTIL avg_sal >= 13000
END REPEAT;
SET num = repeat_count;
END //
DELIMITER ;对比三种循环结构：
至少执行一次
13.6. 跳转语句之LEAVE语句
LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。基本格式如下：
LEAVE 标记名其中，label参数表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用。
举例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在
BEGIN...END中使用IF语句判断num参数的值。
如果num<=0，则使用LEAVE语句退出BEGIN...END；
如果num=1，则查询“employees”表的平均薪资；
如果num=2，则查询“employees”表的最低薪资；
如果num>2，则查询“employees”表的最高薪资。
IF语句结束后查询“employees”表的总人数。
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
IF num<=0
THEN LEAVE begin_label;
ELSEIF num=1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num=2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END //
DELIMITER ;举例2：
当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程“leave_while()”，声明OUT参数num，输出循环次数，存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%，直到全公司的平均薪资小于等于10000，并统计循环次数。
DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
#
DECLARE avg_sal DOUBLE;#记录平均工资
SELECT AVG(salary) INTO avg_sal FROM employees; #① 初始化条件
while_label:WHILE TRUE DO #② 循环条件
#③ 循环体
IF avg_sal <= 10000 THEN
LEAVE while_label;
END IF;
UPDATE employees SET salary = salary * 0.9;
SET while_count = while_count + 1;
#④ 迭代条件
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
#赋值
SET num = while_count;
END //
DELIMITER ;13.7. 跳转语句之ITERATE语句
ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序 转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意 思为“再次循环”。
语句基本格式如下：
ITERATE labellabel参数表示循环的标志。ITERATE语句必须跟在循环标志前面。
举例： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。
如果num < 10，则继续执行循环；
如果num > 15，则退出循环结构；
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE my_loop;
ELSEIF num > 15
SELECT 'Hello World';
END LOOP my_loop;
END //
DELIMITER ;
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
