MySQL必备的常见知识点汇总整理

程序员文章站 2024-01-30 10:47:10

本文实例总结了mysql必备的常见知识点。分享给大家供大家参考，具体如下：最近在整理 sql 的时候发现一份优秀的笔记，是原作者学习 sql 所做的笔记，分享这份总结给大家，对大家对 sql 的可以来...

本文实例总结了mysql必备的常见知识点。分享给大家供大家参考，具体如下：

最近在整理 sql 的时候发现一份优秀的笔记，是原作者学习 sql 所做的笔记，分享这份总结给大家，对大家对 sql 的可以来一次全方位的检漏和排查，感谢原作者 hjzcy 的付出，原文链接放在文章最下方，如果出现错误，希望大家共同指出！

登录和退出 mysql 服务器

# 登录mysql
$ mysql -u root -p12345612

# 退出mysql数据库服务器
exit;

基本语法

-- 显示所有数据库
show databases;

-- 创建数据库
create database test;

-- 切换数据库
use test;

-- 显示数据库中的所有表
show tables;

-- 创建数据表
create table pet (
  name varchar(20),
  owner varchar(20),
  species varchar(20),
  sex char(1),
  birth date,
  death date
);

-- 查看数据表结构
-- describe pet;
desc pet;

-- 查询表
select * from pet;

-- 插入数据
insert into pet values ('puffball', 'diane', 'hamster', 'f', '1990-03-30', null);

-- 修改数据
update pet set name = 'squirrel' where owner = 'diane';

-- 删除数据
delete from pet where name = 'squirrel';

-- 删除表
drop table myorder;

建表约束

主键约束

-- 主键约束
-- 使某个字段不重复且不得为空，确保表内所有数据的唯一性。
create table user (
  id int primary key,
  name varchar(20)
);

-- 联合主键
-- 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。
create table user (
  id int,
  name varchar(20),
  password varchar(20),
  primary key(id, name)
);

-- 自增约束
-- 自增约束的主键由系统自动递增分配。
create table user (
  id int primary key auto_increment,
  name varchar(20)
);

-- 添加主键约束
-- 如果忘记设置主键，还可以通过sql语句设置（两种方式）：
alter table user add primary key(id);
alter table user modify id int primary key;

-- 删除主键
alter table user drop primary key;

唯一主键

-- 建表时创建唯一主键
create table user (
  id int,
  name varchar(20),
  unique(name)
);

-- 添加唯一主键
-- 如果建表时没有设置唯一建，还可以通过sql语句设置（两种方式）：
alter table user add unique(name);
alter table user modify name varchar(20) unique;

-- 删除唯一主键
alter table user drop index name;

非空约束

-- 建表时添加非空约束
-- 约束某个字段不能为空
create table user (
  id int,
  name varchar(20) not null
);

-- 移除非空约束
alter table user modify name varchar(20);

默认约束

-- 建表时添加默认约束
-- 约束某个字段的默认值
create table user2 (
  id int,
  name varchar(20),
  age int default 10
);

-- 移除非空约束
alter table user modify age int;

外键约束

-- 班级
create table classes (
  id int primary key,
  name varchar(20)
);

-- 学生表
create table students (
  id int primary key,
  name varchar(20),
  -- 这里的 class_id 要和 classes 中的 id 字段相关联
  class_id int,
  -- 表示 class_id 的值必须来自于 classes 中的 id 字段值
  foreign key(class_id) references classes(id)
);

-- 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
-- 2. 主表中的记录被副表引用时，主表不可以被删除。

数据库的三大设计范式

1nf

只要字段值还可以继续拆分，就不满足第一范式。

范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。

2nf

在满足第一范式的前提下，其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下：

-- 订单表
create table myorder (
  product_id int,
  customer_id int,
  product_name varchar(20),
  customer_name varchar(20),
  primary key (product_id, customer_id)
);

实际上，在这张订单表中，product_name 只依赖于 product_id ，customer_name 只依赖于 customer_id 。也就是说，product_name 和 customer_id 是没用关系的，customer_name 和 product_id 也是没有关系的。

这就不满足第二范式：其他列都必须完全依赖于主键列！

create table myorder (
  order_id int primary key,
  product_id int,
  customer_id int
);

create table product (
  id int primary key,
  name varchar(20)
);

create table customer (
  id int primary key,
  name varchar(20)
);

拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计！

3nf

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。

create table myorder (
  order_id int primary key,
  product_id int,
  customer_id int,
  customer_phone varchar(15)
);

表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。

create table myorder (
  order_id int primary key,
  product_id int,
  customer_id int
);

create table customer (
  id int primary key,
  name varchar(20),
  phone varchar(15)
);

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

查询练习

准备数据

-- 创建数据库
create database select_test;
-- 切换数据库
use select_test;

-- 创建学生表
create table student (
  no varchar(20) primary key,
  name varchar(20) not null,
  sex varchar(10) not null,
  birthday date, -- 生日
  class varchar(20) -- 所在班级
);

-- 创建教师表
create table teacher (
  no varchar(20) primary key,
  name varchar(20) not null,
  sex varchar(10) not null,
  birthday date,
  profession varchar(20) not null, -- 职称
  department varchar(20) not null -- 部门
);

-- 创建课程表
create table course (
  no varchar(20) primary key,
  name varchar(20) not null,
  t_no varchar(20) not null, -- 教师编号
  -- 表示该 tno 来自于 teacher 表中的 no 字段值
  foreign key(t_no) references teacher(no)
);

-- 成绩表
create table score (
  s_no varchar(20) not null, -- 学生编号
  c_no varchar(20) not null, -- 课程号
  degree decimal,  -- 成绩
  -- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
  foreign key(s_no) references student(no),
  foreign key(c_no) references course(no),
  -- 设置 s_no, c_no 为联合主键
  primary key(s_no, c_no)
);

-- 查看所有表
show tables;

-- 添加学生表数据
insert into student values('101', '曾华', '男', '1977-09-01', '95033');
insert into student values('102', '匡明', '男', '1975-10-02', '95031');
insert into student values('103', '王丽', '女', '1976-01-23', '95033');
insert into student values('104', '李军', '男', '1976-02-20', '95033');
insert into student values('105', '王芳', '女', '1975-02-10', '95031');
insert into student values('106', '陆军', '男', '1974-06-03', '95031');
insert into student values('107', '王尼玛', '男', '1976-02-20', '95033');
insert into student values('108', '张全蛋', '男', '1975-02-10', '95031');
insert into student values('109', '赵铁柱', '男', '1974-06-03', '95031');

-- 添加教师表数据
insert into teacher values('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
insert into teacher values('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
insert into teacher values('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
insert into teacher values('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
insert into course values('3-105', '计算机导论', '825');
insert into course values('3-245', '操作系统', '804');
insert into course values('6-166', '数字电路', '856');
insert into course values('9-888', '高等数学', '831');

-- 添加添加成绩表数据
insert into score values('103', '3-105', '92');
insert into score values('103', '3-245', '86');
insert into score values('103', '6-166', '85');
insert into score values('105', '3-105', '88');
insert into score values('105', '3-245', '75');
insert into score values('105', '6-166', '79');
insert into score values('109', '3-105', '76');
insert into score values('109', '3-245', '68');
insert into score values('109', '6-166', '81');

-- 查看表结构
select * from course;
select * from score;
select * from student;
select * from teacher;

1 到 10

-- 查询 student 表的所有行
select * from student;

-- 查询 student 表中的 name、sex 和 class 字段的所有行
select name, sex, class from student;

-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
select distinct department from teacher;

-- 查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询）
-- between xx and xx: 查询区间, and 表示 "并且"
select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree < 80;

-- 查询 score 表中成绩为 85, 86 或 88 的行
-- in: 查询规定中的多个值
select * from score where degree in (85, 86, 88);

-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
select * from student where class = '95031' or sex = '女';

-- 以 class 降序的方式查询 student 表的所有行
-- desc: 降序，从高到低
-- asc（默认）: 升序，从低到高
select * from student order by class desc;
select * from student order by class asc;

-- 以 c_no 升序、degree 降序查询 score 表的所有行
select * from score order by c_no asc, degree desc;

-- 查询 "95031" 班的学生人数
-- count: 统计
select count(*) from student where class = '95031';

-- 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。
-- (select max(degree) from score): 子查询，算出最高分
select s_no, c_no from score where degree = (select max(degree) from score);

-- 排序查询
-- limit r, n: 表示从第r行开始，查询n条数据
select s_no, c_no, degree from score order by degree desc limit 0, 1;

分组计算平均成绩

查询每门课的平均成绩。

-- avg: 平均值
select avg(degree) from score where c_no = '3-105';
select avg(degree) from score where c_no = '3-245';
select avg(degree) from score where c_no = '6-166';

-- group by: 分组查询
select c_no, avg(degree) from score group by c_no;

分组条件与模糊查询

查询 score 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。

select * from score;
-- c_no 课程编号
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 |   92 |
| 103 | 3-245 |   86 |
| 103 | 6-166 |   85 |
| 105 | 3-105 |   88 |
| 105 | 3-245 |   75 |
| 105 | 6-166 |   79 |
| 109 | 3-105 |   76 |
| 109 | 3-245 |   68 |
| 109 | 6-166 |   81 |
+------+-------+--------+

分析表发现，至少有 2 名学生选修的课程是 3-105 、3-245 、6-166 ，以 3 开头的课程是 3-105 、3-245 。也就是说，我们要查询所有 3-105 和 3-245 的 degree 平均分。

-- 首先把 c_no, avg(degree) 通过分组查询出来
select c_no, avg(degree) from score group by c_no
+-------+-------------+
| c_no | avg(degree) |
+-------+-------------+
| 3-105 |   85.3333 |
| 3-245 |   76.3333 |
| 6-166 |   81.6667 |
+-------+-------------+

-- 再查询出至少有 2 名学生选修的课程
-- having: 表示持有
having count(c_no) >= 2

-- 并且是以 3 开头的课程
-- like 表示模糊查询，"%" 是一个通配符，匹配 "3" 后面的任意字符。
and c_no like '3%';

-- 把前面的sql语句拼接起来，
-- 后面加上一个 count(*)，表示将每个分组的个数也查询出来。
select c_no, avg(degree), count(*) from score group by c_no
having count(c_no) >= 2 and c_no like '3%';
+-------+-------------+----------+
| c_no | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 |   85.3333 |    3 |
| 3-245 |   76.3333 |    3 |
+-------+-------------+----------+

多表查询 - 1

查询所有学生的 name，以及该学生在 score 表中对应的 c_no 和 degree 。

select no, name from student;
+-----+-----------+
| no | name   |
+-----+-----------+
| 101 | 曾华   |
| 102 | 匡明   |
| 103 | 王丽   |
| 104 | 李军   |
| 105 | 王芳   |
| 106 | 陆军   |
| 107 | 王尼玛  |
| 108 | 张全蛋  |
| 109 | 赵铁柱  |
+-----+-----------+

select s_no, c_no, degree from score;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 |   92 |
| 103 | 3-245 |   86 |
| 103 | 6-166 |   85 |
| 105 | 3-105 |   88 |
| 105 | 3-245 |   75 |
| 105 | 6-166 |   79 |
| 109 | 3-105 |   76 |
| 109 | 3-245 |   68 |
| 109 | 6-166 |   81 |
+------+-------+--------+

通过分析可以发现，只要把 score 表中的 s_no 字段值替换成 student 表中对应的 name 字段值就可以了，如何做呢？

-- from...: 表示从 student, score 表中查询
-- where 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。
select name, c_no, degree from student, score
where student.no = score.s_no;
+-----------+-------+--------+
| name   | c_no | degree |
+-----------+-------+--------+
| 王丽   | 3-105 |   92 |
| 王丽   | 3-245 |   86 |
| 王丽   | 6-166 |   85 |
| 王芳   | 3-105 |   88 |
| 王芳   | 3-245 |   75 |
| 王芳   | 6-166 |   79 |
| 赵铁柱  | 3-105 |   76 |
| 赵铁柱  | 3-245 |   68 |
| 赵铁柱  | 6-166 |   81 |
+-----------+-------+--------+

多表查询 - 2

查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列。

只有 score 关联学生的 no ，因此只要查询 score 表，就能找出所有和学生相关的 no 和 degree ：

select s_no, c_no, degree from score;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 |   92 |
| 103 | 3-245 |   86 |
| 103 | 6-166 |   85 |
| 105 | 3-105 |   88 |
| 105 | 3-245 |   75 |
| 105 | 6-166 |   79 |
| 109 | 3-105 |   76 |
| 109 | 3-245 |   68 |
| 109 | 6-166 |   81 |
+------+-------+--------+

然后查询 course 表：

+-------+-----------------+
| no  | name      |
+-------+-----------------+
| 3-105 | 计算机导论   |
| 3-245 | 操作系统    |
| 6-166 | 数字电路    |
| 9-888 | 高等数学    |
+-------+-----------------+

只要把 score 表中的 c_no 替换成 course 表中对应的 name 字段值就可以了。

-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
select s_no, name as c_name, degree from score, course
where score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name     | degree |
+------+-----------------+--------+
| 103 | 计算机导论   |   92 |
| 105 | 计算机导论   |   88 |
| 109 | 计算机导论   |   76 |
| 103 | 操作系统    |   86 |
| 105 | 操作系统    |   75 |
| 109 | 操作系统    |   68 |
| 103 | 数字电路    |   85 |
| 105 | 数字电路    |   79 |
| 109 | 数字电路    |   81 |
+------+-----------------+--------+

三表关联查询

查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree 。

只有 score 表中关联学生的学号和课堂号，我们只要围绕着 score 这张表查询就好了。

select * from score;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 |   92 |
| 103 | 3-245 |   86 |
| 103 | 6-166 |   85 |
| 105 | 3-105 |   88 |
| 105 | 3-245 |   75 |
| 105 | 6-166 |   79 |
| 109 | 3-105 |   76 |
| 109 | 3-245 |   68 |
| 109 | 6-166 |   81 |
+------+-------+--------+

只要把 s_no 和 c_no 替换成 student 和 srouse 表中对应的 name 字段值就好了。

首先把 s_no 替换成 student 表中的 name 字段：

select name, c_no, degree from student, score where student.no = score.s_no;
+-----------+-------+--------+
| name   | c_no | degree |
+-----------+-------+--------+
| 王丽   | 3-105 |   92 |
| 王丽   | 3-245 |   86 |
| 王丽   | 6-166 |   85 |
| 王芳   | 3-105 |   88 |
| 王芳   | 3-245 |   75 |
| 王芳   | 6-166 |   79 |
| 赵铁柱  | 3-105 |   76 |
| 赵铁柱  | 3-245 |   68 |
| 赵铁柱  | 6-166 |   81 |
+-----------+-------+--------+

再把 c_no 替换成 course 表中的 name 字段：

-- 课程表
select no, name from course;
+-------+-----------------+
| no  | name      |
+-------+-----------------+
| 3-105 | 计算机导论   |
| 3-245 | 操作系统    |
| 6-166 | 数字电路    |
| 9-888 | 高等数学    |
+-------+-----------------+

-- 由于字段名存在重复，使用 "表名.字段名 as 别名" 代替。
select student.name as s_name, course.name as c_name, degree
from student, score, course
where student.no = score.s_no
and score.c_no = course.no;

子查询加分组求平均分

查询 95031 班学生每门课程的平均成绩。

在 score 表中根据 student 表的学生编号筛选出学生的课堂号和成绩：

-- in (..): 将筛选出的学生号当做 s_no 的条件查询
select s_no, c_no, degree from score
where s_no in (select no from student where class = '95031');
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 105 | 3-105 |   88 |
| 105 | 3-245 |   75 |
| 105 | 6-166 |   79 |
| 109 | 3-105 |   76 |
| 109 | 3-245 |   68 |
| 109 | 6-166 |   81 |
+------+-------+--------+

这时只要将 c_no 分组一下就能得出 95031 班学生每门课的平均成绩：

select c_no, avg(degree) from score
where s_no in (select no from student where class = '95031')
group by c_no;
+-------+-------------+
| c_no | avg(degree) |
+-------+-------------+
| 3-105 |   82.0000 |
| 3-245 |   71.5000 |
| 6-166 |   80.0000 |
+-------+-------------+

子查询 - 1

查询在 3-105 课程中，所有成绩高于 109 号同学的记录。

首先筛选出课堂号为 3-105 ，在找出所有成绩高于 109 号同学的的行。

select * from score
where c_no = '3-105'
and degree > (select degree from score where s_no = '109' and c_no = '3-105');

子查询 - 2

查询所有成绩高于 109 号同学的 3-105 课程成绩记录。

-- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。
select * from score
where degree > (select degree from score where s_no = '109' and c_no = '3-105');

year 函数与带 in 关键字查询

查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。

-- year(..): 取出日期中的年份
select no, name, birthday from student
where year(birthday) in (select year(birthday) from student where no in (101, 108));

多层嵌套子查询

查询 '张旭' 教师任课的学生成绩表。

首先找到教师编号：

select no from teacher where name = '张旭'

通过 sourse 表找到该教师课程号：

select no from course where t_no = ( select no from teacher where name = '张旭' );

通过筛选出的课程号查询成绩表：

select * from score where c_no = (
  select no from course where t_no = (
    select no from teacher where name = '张旭'
  )
);

多表查询

查询某选修课程多于 5 个同学的教师姓名。

首先在 teacher 表中，根据 no 字段来判断该教师的同一门课程是否有至少 5 名学员选修：

-- 查询 teacher 表
select no, name from teacher;
+-----+--------+
| no | name  |
+-----+--------+
| 804 | 李诚  |
| 825 | 王萍  |
| 831 | 刘冰  |
| 856 | 张旭  |
+-----+--------+

select name from teacher where no in (
  -- 在这里找到对应的条件
);

查看和教师编号有有关的表的信息：

select * from course;
-- t_no: 教师编号
+-------+-----------------+------+
| no  | name      | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论   | 825 |
| 3-245 | 操作系统    | 804 |
| 6-166 | 数字电路    | 856 |
| 9-888 | 高等数学    | 831 |
+-------+-----------------+------+

我们已经找到和教师编号有关的字段就在 course 表中，但是还无法知道哪门课程至少有 5 名学生选修，所以还需要根据 score 表来查询：

-- 在此之前向 score 插入一些数据，以便丰富查询条件。
insert into score values ('101', '3-105', '90');
insert into score values ('102', '3-105', '91');
insert into score values ('104', '3-105', '89');

-- 查询 score 表
select * from score;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 101 | 3-105 |   90 |
| 102 | 3-105 |   91 |
| 103 | 3-105 |   92 |
| 103 | 3-245 |   86 |
| 103 | 6-166 |   85 |
| 104 | 3-105 |   89 |
| 105 | 3-105 |   88 |
| 105 | 3-245 |   75 |
| 105 | 6-166 |   79 |
| 109 | 3-105 |   76 |
| 109 | 3-245 |   68 |
| 109 | 6-166 |   81 |
+------+-------+--------+

-- 在 score 表中将 c_no 作为分组，并且限制 c_no 持有至少 5 条数据。
select c_no from score group by c_no having count(*) > 5;
+-------+
| c_no |
+-------+
| 3-105 |
+-------+

根据筛选出来的课程号，找出在某课程中，拥有至少 5 名学员的教师编号：

select t_no from course where no in (
  select c_no from score group by c_no having count(*) > 5
);
+------+
| t_no |
+------+
| 825 |
+------+

在 teacher 表中，根据筛选出来的教师编号找到教师姓名：

select name from teacher where no in (
  -- 最终条件
  select t_no from course where no in (
    select c_no from score group by c_no having count(*) > 5
  )
);

子查询 - 3

查询 “计算机系” 课程的成绩表。

思路是，先找出 course 表中所有 计算机系 课程的编号，然后根据这个编号查询 score 表。

-- 通过 teacher 表查询所有 `计算机系` 的教师编号
select no, name, department from teacher where department = '计算机系'
+-----+--------+--------------+
| no | name  | department  |
+-----+--------+--------------+
| 804 | 李诚  | 计算机系   |
| 825 | 王萍  | 计算机系   |
+-----+--------+--------------+

-- 通过 course 表查询该教师的课程编号
select no from course where t_no in (
  select no from teacher where department = '计算机系'
);
+-------+
| no  |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- 根据筛选出来的课程号查询成绩表
select * from score where c_no in (
  select no from course where t_no in (
    select no from teacher where department = '计算机系'
  )
);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-245 |   86 |
| 105 | 3-245 |   75 |
| 109 | 3-245 |   68 |
| 101 | 3-105 |   90 |
| 102 | 3-105 |   91 |
| 103 | 3-105 |   92 |
| 104 | 3-105 |   89 |
| 105 | 3-105 |   88 |
| 109 | 3-105 |   76 |
+------+-------+--------+

union 和 notin 的使用

查询 计算机系 与 电子工程系 中的不同职称的教师。

-- not: 代表逻辑非
select * from teacher where department = '计算机系' and profession not in (
  select profession from teacher where department = '电子工程系'
)
-- 合并两个集
union
select * from teacher where department = '电子工程系' and profession not in (
  select profession from teacher where department = '计算机系'
);

any 表示至少一个 - desc ( 降序 )

查询课程 3-105 且成绩 <u>至少</u> 高于 3-245 的 score 表。

select * from score where c_no = '3-105';
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 101 | 3-105 |   90 |
| 102 | 3-105 |   91 |
| 103 | 3-105 |   92 |
| 104 | 3-105 |   89 |
| 105 | 3-105 |   88 |
| 109 | 3-105 |   76 |
+------+-------+--------+

select * from score where c_no = '3-245';
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-245 |   86 |
| 105 | 3-245 |   75 |
| 109 | 3-245 |   68 |
+------+-------+--------+

-- any: 符合sql语句中的任意条件。
-- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，
-- 最后根据降序查询结果。
select * from score where c_no = '3-105' and degree > any(
  select degree from score where c_no = '3-245'
) order by degree desc;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 |   92 |
| 102 | 3-105 |   91 |
| 101 | 3-105 |   90 |
| 104 | 3-105 |   89 |
| 105 | 3-105 |   88 |
| 109 | 3-105 |   76 |
+------+-------+--------+

表示所有的 all

查询课程 3-105 且成绩高于 3-245 的 score 表。

-- 只需对上一道题稍作修改。
-- all: 符合sql语句中的所有条件。
-- 也就是说，在 3-105 每一行成绩中，都要大于从 3-245 筛选出来全部行才算符合条件。
select * from score where c_no = '3-105' and degree > all(
  select degree from score where c_no = '3-245'
);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 101 | 3-105 |   90 |
| 102 | 3-105 |   91 |
| 103 | 3-105 |   92 |
| 104 | 3-105 |   89 |
| 105 | 3-105 |   88 |
+------+-------+--------+

复制表的数据作为条件查询

查询某课程成绩比该课程平均成绩低的 score 表。

-- 查询平均分
select c_no, avg(degree) from score group by c_no;
+-------+-------------+
| c_no | avg(degree) |
+-------+-------------+
| 3-105 |   87.6667 |
| 3-245 |   76.3333 |
| 6-166 |   81.6667 |
+-------+-------------+

-- 查询 score 表
select degree from score;
+--------+
| degree |
+--------+
|   90 |
|   91 |
|   92 |
|   86 |
|   85 |
|   89 |
|   88 |
|   75 |
|   79 |
|   76 |
|   68 |
|   81 |
+--------+

-- 将表 b 作用于表 a 中查询数据
-- score a (b): 将表声明为 a (b)，
-- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
select * from score a where degree < (
  (select avg(degree) from score b where a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 105 | 3-245 |   75 |
| 105 | 6-166 |   79 |
| 109 | 3-105 |   76 |
| 109 | 3-245 |   68 |
| 109 | 6-166 |   81 |
+------+-------+--------+

子查询 - 4

查询所有任课 ( 在 course 表里有课程 ) 教师的 name 和 department 。

select name, department from teacher where no in (select t_no from course);
+--------+-----------------+
| name  | department   |
+--------+-----------------+
| 李诚  | 计算机系    |
| 王萍  | 计算机系    |
| 刘冰  | 电子工程系   |
| 张旭  | 电子工程系   |
+--------+-----------------+

条件加组筛选

查询 student 表中至少有 2 名男生的 class 。

-- 查看学生表信息
select * from student;
+-----+-----------+-----+------------+-------+
| no | name   | sex | birthday  | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华   | 男 | 1977-09-01 | 95033 |
| 102 | 匡明   | 男 | 1975-10-02 | 95031 |
| 103 | 王丽   | 女 | 1976-01-23 | 95033 |
| 104 | 李军   | 男 | 1976-02-20 | 95033 |
| 105 | 王芳   | 女 | 1975-02-10 | 95031 |
| 106 | 陆军   | 男 | 1974-06-03 | 95031 |
| 107 | 王尼玛  | 男 | 1976-02-20 | 95033 |
| 108 | 张全蛋  | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱  | 男 | 1974-06-03 | 95031 |
| 110 | 张飞   | 男 | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

-- 只查询性别为男，然后按 class 分组，并限制 class 行大于 1。
select class from student where sex = '男' group by class having count(*) > 1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+

notlike 模糊查询取反

查询 student 表中不姓 "王" 的同学记录。

-- not: 取反
-- like: 模糊查询
mysql> select * from student where name not like '王%';
+-----+-----------+-----+------------+-------+
| no | name   | sex | birthday  | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华   | 男 | 1977-09-01 | 95033 |
| 102 | 匡明   | 男 | 1975-10-02 | 95031 |
| 104 | 李军   | 男 | 1976-02-20 | 95033 |
| 106 | 陆军   | 男 | 1974-06-03 | 95031 |
| 108 | 张全蛋  | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱  | 男 | 1974-06-03 | 95031 |
| 110 | 张飞   | 男 | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

year 与 now 函数

查询 student 表中每个学生的姓名和年龄。

-- 使用函数 year(now()) 计算出当前年份，减去出生年份后得出年龄。
select name, year(now()) - year(birthday) as age from student;
+-----------+------+
| name   | age |
+-----------+------+
| 曾华   |  42 |
| 匡明   |  44 |
| 王丽   |  43 |
| 李军   |  43 |
| 王芳   |  44 |
| 陆军   |  45 |
| 王尼玛  |  43 |
| 张全蛋  |  44 |
| 赵铁柱  |  45 |
| 张飞   |  45 |
+-----------+------+

max 与 min 函数

查询 student 表中最大和最小的 birthday 值。

select max(birthday), min(birthday) from student;
+---------------+---------------+
| max(birthday) | min(birthday) |
+---------------+---------------+
| 1977-09-01  | 1974-06-03  |
+---------------+---------------+

多段排序

以 class 和 birthday 从大到小的顺序查询 student 表。

select * from student order by class desc, birthday;
+-----+-----------+-----+------------+-------+
| no | name   | sex | birthday  | class |
+-----+-----------+-----+------------+-------+
| 110 | 张飞   | 男 | 1974-06-03 | 95038 |
| 103 | 王丽   | 女 | 1976-01-23 | 95033 |
| 104 | 李军   | 男 | 1976-02-20 | 95033 |
| 107 | 王尼玛  | 男 | 1976-02-20 | 95033 |
| 101 | 曾华   | 男 | 1977-09-01 | 95033 |
| 106 | 陆军   | 男 | 1974-06-03 | 95031 |
| 109 | 赵铁柱  | 男 | 1974-06-03 | 95031 |
| 105 | 王芳   | 女 | 1975-02-10 | 95031 |
| 108 | 张全蛋  | 男 | 1975-02-10 | 95031 |
| 102 | 匡明   | 男 | 1975-10-02 | 95031 |
+-----+-----------+-----+------------+-------+

子查询 - 5

查询 "男" 教师及其所上的课程。

select * from course where t_no in (select no from teacher where sex = '男');
+-------+--------------+------+
| no  | name     | t_no |
+-------+--------------+------+
| 3-245 | 操作系统   | 804 |
| 6-166 | 数字电路   | 856 |
+-------+--------------+------+

max 函数与子查询

查询最高分同学的 score 表。

-- 找出最高成绩（该查询只能有一个结果）
select max(degree) from score;

-- 根据上面的条件筛选出所有最高成绩表，
-- 该查询可能有多个结果，假设 degree 值多次符合条件。
select * from score where degree = (select max(degree) from score);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 |   92 |
+------+-------+--------+

子查询 - 6

查询和 "李军" 同性别的所有同学 name 。

-- 首先将李军的性别作为条件取出来
select sex from student where name = '李军';
+-----+
| sex |
+-----+
| 男 |
+-----+

-- 根据性别查询 name 和 sex
select name, sex from student where sex = (
  select sex from student where name = '李军'
);
+-----------+-----+
| name   | sex |
+-----------+-----+
| 曾华   | 男 |
| 匡明   | 男 |
| 李军   | 男 |
| 陆军   | 男 |
| 王尼玛  | 男 |
| 张全蛋  | 男 |
| 赵铁柱  | 男 |
| 张飞   | 男 |
+-----------+-----+

子查询 - 7

查询和 "李军" 同性别且同班的同学 name 。

select name, sex, class from student where sex = (
  select sex from student where name = '李军'
) and class = (
  select class from student where name = '李军'
);
+-----------+-----+-------+
| name   | sex | class |
+-----------+-----+-------+
| 曾华   | 男 | 95033 |
| 李军   | 男 | 95033 |
| 王尼玛  | 男 | 95033 |
+-----------+-----+-------+

子查询 - 8

查询所有选修 "计算机导论" 课程的 "男" 同学成绩表。

需要的 "计算机导论" 和性别为 "男" 的编号可以在 course 和 student 表中找到。

select * from score where c_no = (
  select no from course where name = '计算机导论'
) and s_no in (
  select no from student where sex = '男'
);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 101 | 3-105 |   90 |
| 102 | 3-105 |   91 |
| 104 | 3-105 |   89 |
| 109 | 3-105 |   76 |
+------+-------+--------+

按等级查询

建立一个 grade 表代表学生的成绩等级，并插入数据：

create table grade (
  low int(3),
  upp int(3),
  grade char(1)
);

insert into grade values (90, 100, 'a');
insert into grade values (80, 89, 'b');
insert into grade values (70, 79, 'c');
insert into grade values (60, 69, 'd');
insert into grade values (0, 59, 'e');

select * from grade;
+------+------+-------+
| low | upp | grade |
+------+------+-------+
|  90 | 100 | a   |
|  80 |  89 | b   |
|  70 |  79 | c   |
|  60 |  69 | d   |
|  0 |  59 | e   |
+------+------+-------+

查询所有学生的 s_no 、c_no 和 grade 列。

思路是，使用区间 ( between ) 查询，判断学生的成绩 ( degree ) 在 grade 表的 low 和 upp 之间。

select s_no, c_no, grade from score, grade
where degree between low and upp;
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 101 | 3-105 | a   |
| 102 | 3-105 | a   |
| 103 | 3-105 | a   |
| 103 | 3-245 | b   |
| 103 | 6-166 | b   |
| 104 | 3-105 | b   |
| 105 | 3-105 | b   |
| 105 | 3-245 | c   |
| 105 | 6-166 | c   |
| 109 | 3-105 | c   |
| 109 | 3-245 | d   |
| 109 | 6-166 | b   |
+------+-------+-------+

连接查询

准备用于测试连接查询的数据：

create database testjoin;

create table person (
  id int,
  name varchar(20),
  cardid int
);

create table card (
  id int,
  name varchar(20)
);

insert into card values (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
select * from card;
+------+-----------+
| id  | name   |
+------+-----------+
|  1 | 饭卡   |
|  2 | 建行卡  |
|  3 | 农行卡  |
|  4 | 工商卡  |
|  5 | 邮政卡  |
+------+-----------+

insert into person values (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
select * from person;
+------+--------+--------+
| id  | name  | cardid |
+------+--------+--------+
|  1 | 张三  |   1 |
|  2 | 李四  |   3 |
|  3 | 王五  |   6 |
+------+--------+--------+

分析两张表发现，person 表并没有为 cardid 字段设置一个在 card 表中对应的 id 外键。如果设置了的话，person 中 cardid 字段值为 6 的行就插不进去，因为该 cardid 值在 card 表中并没有。

内连接

要查询这两张表中有关系的数据，可以使用 inner join ( 内连接 ) 将它们连接在一起。

-- inner join: 表示为内连接，将两张表拼接在一起。
-- on: 表示要执行某个条件。
select * from person inner join card on person.cardid = card.id;
+------+--------+--------+------+-----------+
| id  | name  | cardid | id  | name   |
+------+--------+--------+------+-----------+
|  1 | 张三  |   1 |  1 | 饭卡   |
|  2 | 李四  |   3 |  3 | 农行卡  |
+------+--------+--------+------+-----------+

-- 将 inner 关键字省略掉，结果也是一样的。
-- select * from person join card on person.cardid = card.id;

注意：card 的整张表被连接到了右边。

左外连接

完整显示左边的表 ( person ) ，右边的表如果符合条件就显示，不符合则补 null 。

-- left join 也叫做 left outer join，用这两种方式的查询结果是一样的。
select * from person left join card on person.cardid = card.id;
+------+--------+--------+------+-----------+
| id  | name  | cardid | id  | name   |
+------+--------+--------+------+-----------+
|  1 | 张三  |   1 |  1 | 饭卡   |
|  2 | 李四  |   3 |  3 | 农行卡  |
|  3 | 王五  |   6 | null | null   |
+------+--------+--------+------+-----------+

右外链接

完整显示右边的表 ( card ) ，左边的表如果符合条件就显示，不符合则补 null 。

select * from person right join card on person.cardid = card.id;
+------+--------+--------+------+-----------+
| id  | name  | cardid | id  | name   |
+------+--------+--------+------+-----------+
|  1 | 张三  |   1 |  1 | 饭卡   |
|  2 | 李四  |   3 |  3 | 农行卡  |
| null | null  |  null |  2 | 建行卡  |
| null | null  |  null |  4 | 工商卡  |
| null | null  |  null |  5 | 邮政卡  |
+------+--------+--------+------+-----------+

全外链接

完整显示两张表的全部数据。

-- mysql 不支持这种语法的全外连接
-- select * from person full join card on person.cardid = card.id;
-- 出现错误：
-- error 1054 (42s22): unknown column 'person.cardid' in 'on clause'

-- mysql全连接语法，使用 union 将两张表合并在一起。
select * from person left join card on person.cardid = card.id
union
select * from person right join card on person.cardid = card.id;
+------+--------+--------+------+-----------+
| id  | name  | cardid | id  | name   |
+------+--------+--------+------+-----------+
|  1 | 张三  |   1 |  1 | 饭卡   |
|  2 | 李四  |   3 |  3 | 农行卡  |
|  3 | 王五  |   6 | null | null   |
| null | null  |  null |  2 | 建行卡  |
| null | null  |  null |  4 | 工商卡  |
| null | null  |  null |  5 | 邮政卡  |
+------+--------+--------+------+-----------+

事务

在 mysql 中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

比如我们的银行转账：

-- a -> -100
update user set money = money - 100 where name = 'a';

-- b -> +100
update user set money = money + 100 where name = 'b';

在实际项目中，假设只有一条 sql 语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。

因此，在执行多条有关联 sql 语句时，事务可能会要求这些 sql 语句要么同时执行成功，要么就都执行失败。

如何控制事务 - commit / rollback

在 mysql 中，事务的自动提交状态默认是开启的。

-- 查询事务的自动提交状态
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|      1 |
+--------------+

自动提交的作用：当我们执行一条 sql 语句的时候，其产生的效果就会立即体现出来，且不能回滚。

什么是回滚？举个例子：

create database bank;

use bank;

create table user (
  id int primary key,
  name varchar(20),
  money int
);

insert into user values (1, 'a', 1000);

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
+----+------+-------+

可以看到，在执行插入语句后数据立刻生效，原因是 mysql 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是，撤销执行过的所有 sql 语句，使其回滚到最后一次提交数据时的状态。

在 mysql 中使用 rollback 执行回滚：

-- 回滚到最后一次提交
rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
+----+------+-------+

由于所有执行过的 sql 语句都已经被提交过了，所以数据并没有发生回滚。那如何让数据可以发生回滚？

-- 关闭自动提交
set autocommit = 0;

-- 查询自动提交状态
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|      0 |
+--------------+

将自动提交关闭后，测试数据回滚：

insert into user values (2, 'b', 1000);

-- 关闭 autocommit 后，数据的变化是在一张虚拟的临时数据表中展示，
-- 发生变化的数据并没有真正插入到数据表中。
select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
| 2 | b  | 1000 |
+----+------+-------+

-- 数据表中的真实数据其实还是：
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
+----+------+-------+

-- 由于数据还没有真正提交，可以使用回滚
rollback;

-- 再次查询
select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
+----+------+-------+

那如何将虚拟的数据真正提交到数据库中？使用 commit :

insert into user values (2, 'b', 1000);
-- 手动提交数据（持久性），
-- 将数据真正提交到数据库中，执行后不能再回滚提交过的数据。
commit;

-- 提交后测试回滚
rollback;

-- 再次查询（回滚无效了）
select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
| 2 | b  | 1000 |
+----+------+-------+

总结

自动提交

查看自动提交状态：select @@autocommit ；

设置自动提交状态：set autocommit = 0 。

手动提交
@@autocommit = 0 时，使用 commit 命令提交事务。

事务回滚
@@autocommit = 0 时，使用 rollback 命令回滚事务。

事务的实际应用，让我们再回到银行转账项目：

-- 转账
update user set money = money - 100 where name = 'a';

-- 到账
update user set money = money + 100 where name = 'b';

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  900 |
| 2 | b  | 1100 |
+----+------+-------+

这时假设在转账时发生了意外，就可以使用 rollback 回滚到最后一次提交的状态：

-- 假设转账发生了意外，需要回滚。
rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
| 2 | b  | 1000 |
+----+------+-------+

这时我们又回到了发生意外之前的状态，也就是说，事务给我们提供了一个可以反悔的机会。假设数据没有发生意外，这时可以手动将数据真正提交到数据表中：commit 。

手动开启事务 - begin / start transaction

事务的默认提交被开启 ( @@autocommit = 1 ) 后，此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件，使其可以发生回滚：

-- 使用 begin 或者 start transaction 手动开启一个事务
-- start transaction;
begin;
update user set money = money - 100 where name = 'a';
update user set money = money + 100 where name = 'b';

-- 由于手动开启的事务没有开启自动提交，
-- 此时发生变化的数据仍然是被保存在一张临时表中。
select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  900 |
| 2 | b  | 1100 |
+----+------+-------+

-- 测试回滚
rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
| 2 | b  | 1000 |
+----+------+-------+

仍然使用 commit 提交数据，提交后无法再发生本次事务的回滚。

begin;
update user set money = money - 100 where name = 'a';
update user set money = money + 100 where name = 'b';

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  900 |
| 2 | b  | 1100 |
+----+------+-------+

-- 提交数据
commit;

-- 测试回滚（无效，因为表的数据已经被提交）
rollback;

事务的 acid 特征与使用

事务的四大特征：

a 原子性：事务是最小的单位，不可以再分割；
c 一致性：要求同一事务中的 sql 语句，必须保证同时成功或者失败；
i 隔离性：事务 1 和事务 2 之间是具有隔离性的；
d 持久性：事务一旦结束 ( commit ) ，就不可以再返回了 ( rollback ) 。

事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 ) ：

read uncommitted ( 读取未提交 )
如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。
read committed ( 读取已提交 )
只能读取到其他事务已经提交的数据。
repeatable read ( 可被重复读 )
如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。
serializable ( 串行化 )
所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。

查看当前数据库的默认隔离级别：

-- mysql 8.x, global 表示系统级别，不加表示会话级别。
select @@global.transaction_isolation;
select @@transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| repeatable-read        | -- mysql的默认隔离级别，可以重复读。
+--------------------------------+

-- mysql 5.x
select @@global.tx_isolation;
select @@tx_isolation;

修改隔离级别：

-- 设置系统隔离级别，level 后面表示要设置的隔离级别 (read uncommitted)。
set global transaction isolation level read uncommitted;

-- 查询系统隔离级别，发现已经被修改。
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| read-uncommitted        |
+--------------------------------+

脏读

测试 read uncommitted ( 读取未提交 ) 的隔离性：

insert into user values (3, '小明', 1000);
insert into user values (4, '淘宝店', 1000);

select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
+----+-----------+-------+

-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子：
start transaction;
update user set money = money - 800 where name = '小明';
update user set money = money + 800 where name = '淘宝店';

-- 然后淘宝店在另一方查询结果，发现钱已到账。
select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   |  200 |
| 4 | 淘宝店  | 1800 |
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的，而该操作的结果是可以被其他事务（另一方的淘宝店）看见的，因此淘宝店的查询结果是正确的，淘宝店确认到账。但就在这时，如果小明在它所处的事务上又执行了 rollback 命令，会发生什么？

-- 小明所处的事务
rollback;

-- 此时无论对方是谁，如果再去查询结果就会发现：
select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
+----+-----------+-------+

这就是所谓的脏读，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

读取已提交

把隔离级别设置为 read committed ：

set global transaction isolation level read committed;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| read-committed         |
+--------------------------------+

这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说，它们看到的还是未提交的数据，例如：

-- 正在操作数据事务（当前事务）
start transaction;
update user set money = money - 800 where name = '小明';
update user set money = money + 800 where name = '淘宝店';

-- 虽然隔离级别被设置为了 read committed，但在当前事务中，
-- 它看到的仍然是数据表中临时改变数据，而不是真正提交过的数据。
select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   |  200 |
| 4 | 淘宝店  | 1800 |
+----+-----------+-------+


-- 假设此时在远程开启了一个新事务，连接到数据库。
$ mysql -u root -p12345612

-- 此时远程连接查询到的数据只能是已经提交过的
select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
+----+-----------+-------+

但是这样还有问题，那就是假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：

-- 小张在查询数据的时候发现：
select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   |  200 |
| 4 | 淘宝店  | 1800 |
+----+-----------+-------+

-- 在小张求表的 money 平均值之前，小王做了一个操作：
start transaction;
insert into user values (5, 'c', 100);
commit;

-- 此时表的真实数据是：
select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
+----+-----------+-------+

-- 这时小张再求平均值的时候，就会出现计算不相符合的情况：
select avg(money) from user;
+------------+
| avg(money) |
+------------+
| 820.0000 |
+------------+

虽然 read committed 让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是在读取同一个表的数据时，可能会发生前后不一致的情况。这被称为不可重复读现象 ( read committed ) 。

幻读

将隔离级别设置为 repeatable read ( 可被重复读取 ) :

set global transaction isolation level repeatable read;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| repeatable-read        |
+--------------------------------+

测试 repeatable read ，假设在两个不同的连接上分别执行 start transaction :

-- 小张 - 成都
start transaction;
insert into user values (6, 'd', 1000);

-- 小王 - 北京
start transaction;

-- 小张 - 成都
commit;

当前事务开启后，没提交之前，查询不到，提交后可以被查询到。但是，在提交之前其他事务被开启了，那么在这条事务线上，就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 commit ，在小王这边，都不会查询到小张的事务记录，而是只会查询到自己所处事务的记录：

select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
+----+-----------+-------+

这是因为小王在此之前开启了一个新的事务 ( start transaction ) ，那么在他的这条新事务的线上，跟其他事务是没有联系的，也就是说，此时如果其他事务正在操作数据，它是不知道的。

然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？

insert into user values (6, 'd', 1000);
-- error 1062 (23000): duplicate entry '6' for key 'primary'

报错了，操作被告知已存在主键为 6 的字段。这种现象也被称为幻读，一个事务提交的数据，不能被其他事务读取到。

串行化

顾名思义，就是所有事务的写入操作全都是串行化的。什么意思？把隔离级别修改成 serializable :

set global transaction isolation level serializable;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| serializable          |
+--------------------------------+

还是拿小张和小王来举例：

-- 小张 - 成都
start transaction;

-- 小王 - 北京
start transaction;

-- 开启事务之前先查询表，准备操作数据。
select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  900 |
| 2 | b     | 1100 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
| 6 | d     | 1000 |
+----+-----------+-------+

-- 发现没有 7 号王小花，于是插入一条数据：
insert into user values (7, '王小花', 1000);

此时会发生什么呢？由于现在的隔离级别是 serializable ( 串行化 ) ，串行化的意思就是：假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行 commit 结束它所处的事务，或者出现等待超时。

MySQL必备的常见知识点汇总整理

登录和退出 mysql 服务器

基本语法

建表约束

主键约束

唯一主键

非空约束

默认约束

外键约束

数据库的三大设计范式

1nf

2nf

3nf

查询练习

准备数据

1 到 10

分组计算平均成绩

分组条件与模糊查询

多表查询 - 1

多表查询 - 2

三表关联查询

子查询加分组求平均分

子查询 - 1

子查询 - 2

year 函数与带 in 关键字查询

多层嵌套子查询

多表查询

子查询 - 3

union 和 notin 的使用

any 表示至少一个 - desc ( 降序 )

表示所有的 all

复制表的数据作为条件查询

子查询 - 4

条件加组筛选

notlike 模糊查询取反

year 与 now 函数

max 与 min 函数

多段排序

子查询 - 5

max 函数与子查询

子查询 - 6

子查询 - 7

子查询 - 8

按等级查询

连接查询

内连接

左外连接

右外链接

全外链接

事务

如何控制事务 - commit / rollback

手动开启事务 - begin / start transaction

事务的 acid 特征与使用

事务的隔离性

脏读

读取已提交

幻读

串行化

MySQL必备的常见知识点汇总整理

MYSQL的主从复制知识点整理

在Python中处理日期和时间的基本知识点整理汇总

MySQL中常见的几种日志汇总

MySQL 常见的数据表设计误区汇总

MySQL使用的常见问题解决与应用技巧汇总

Lua中的函数相关知识点整理汇总

51学工坊整理|MySQL数据库知识点汇总

漂亮~阿里P8整理总结，金九银十入职大厂必备的Java核心知识点，附加面试题+答案解析

初学者必读 MySQL的数据库常见问题汇总_MySQL