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

吾爱主题阅读：192 2024-04-05 16:21:14 评论：0

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

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

登录和退出 MySQL 服务器

1 2 3 4 5 # 登录MySQL $ mysql -u root -p12345612 # 退出MySQL数据库服务器 exit;

基本语法

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 -- 显示所有数据库 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;

建表约束

主键约束

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 -- 主键约束 -- 使某个字段不重复且不得为空，确保表内所有数据的唯一性。 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 ;

唯一主键

1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- 建表时创建唯一主键 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 ;

非空约束

1 2 3 4 5 6 7 8 9 -- 建表时添加非空约束 -- 约束某个字段不能为空 CREATE TABLE user ( id INT , name VARCHAR (20) NOT NULL ); -- 移除非空约束 ALTER TABLE user MODIFY name VARCHAR (20);

默认约束

1 2 3 4 5 6 7 8 9 10 -- 建表时添加默认约束 -- 约束某个字段的默认值 CREATE TABLE user2 ( id INT , name VARCHAR (20), age INT DEFAULT 10 ); -- 移除非空约束 ALTER TABLE user MODIFY age INT ;

外键约束

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 -- 班级 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

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

1 2 3 4 5 6 7 8 -- 订单表 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 也是没有关系的。

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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

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

1 2 3 4 5 6 CREATE TABLE myorder ( order_id INT PRIMARY KEY , product_id INT , customer_id INT , customer_phone VARCHAR (15) );

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

1 2 3 4 5 6 7 8 9 10 11 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) );

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

查询练习

准备数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 -- 创建数据库 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 -- 查询 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;

分组计算平均成绩

查询每门课的平均成绩。

1 2 3 4 5 6 7 -- 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 开头的课程的平均分数。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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 平均分。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 -- 首先把 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 。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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 字段值就可以了，如何做呢？

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -- 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 ：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 表：

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -- 增加一个查询字段 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 这张表查询就好了。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 字段：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 字段：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 -- 课程表 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 表的学生编号筛选出学生的课堂号和成绩：

1 2 3 4 5 6 7 8 9 10 11 12 13 -- 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 班学生每门课的平均成绩：

1 2 3 4 5 6 7 8 9 10 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 号同学的的行。

1 2 3 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 课程成绩记录。

1 2 3 -- 不限制课程号，只要成绩大于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 列。

1 2 3 -- YEAR(..): 取出日期中的年份 SELECT no , name , birthday FROM student WHERE YEAR (birthday) IN ( SELECT YEAR (birthday) FROM student WHERE no IN (101, 108));

多层嵌套子查询

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

首先找到教师编号：

1	`SELECT` `NO` `FROM` `teacher` `WHERE` `NAME` `=` `'张旭'`

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

1	`SELECT` `NO` `FROM` `course` `WHERE` `t_no = (` `SELECT` `NO` `FROM` `teacher` `WHERE` `NAME` `=` `'张旭'` `);`

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

1 2 3 4 5 SELECT * FROM score WHERE c_no = ( SELECT no FROM course WHERE t_no = ( SELECT no FROM teacher WHERE NAME = '张旭' ) );

多表查询

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- 查询 teacher 表 SELECT no , name FROM teacher; + -----+--------+ | no | name | + -----+--------+ | 804 | 李诚 | | 825 | 王萍 | | 831 | 刘冰 | | 856 | 张旭 | + -----+--------+ SELECT name FROM teacher WHERE no IN ( -- 在这里找到对应的条件 );

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

1 2 3 4 5 6 7 8 9 10 SELECT * FROM course; -- t_no: 教师编号 + -------+-----------------+------+ | no | name | t_no | + -------+-----------------+------+ | 3-105 | 计算机导论 | 825 | | 3-245 | 操作系统 | 804 | | 6-166 | 数字电路 | 856 | | 9-888 | 高等数学 | 831 | + -------+-----------------+------+

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 -- 在此之前向 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 名学员的教师编号：

1 2 3 4 5 6 7 8 SELECT t_no FROM course WHERE no IN ( SELECT c_no FROM score GROUP BY c_no HAVING COUNT (*) > 5 ); + ------+ | t_no | + ------+ | 825 | + ------+

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

1 2 3 4 5 6 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 表。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 -- 通过 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 的使用

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

1 2 3 4 5 6 7 8 9 -- 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 表。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 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 表。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 -- 只需对上一道题稍作修改。 -- 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 表。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 -- 查询平均分 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 。

1 2 3 4 5 6 7 8 9 SELECT name , department FROM teacher WHERE no IN ( SELECT t_no FROM course); + --------+-----------------+ | name | department | + --------+-----------------+ | 李诚 | 计算机系 | | 王萍 | 计算机系 | | 刘冰 | 电子工程系 | | 张旭 | 电子工程系 | + --------+-----------------+

条件加组筛选

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -- 查看学生表信息 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 表中不姓 "王" 的同学记录。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- 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 表中每个学生的姓名和年龄。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 -- 使用函数 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 值。

1 2 3 4 5 6 SELECT MAX (birthday), MIN (birthday) FROM student; + ---------------+---------------+ | MAX (birthday) | MIN (birthday) | + ---------------+---------------+ | 1977-09-01 | 1974-06-03 | + ---------------+---------------+

多段排序

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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

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

1 2 3 4 5 6 7 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 表。

1 2 3 4 5 6 7 8 9 10 11 -- 找出最高成绩（该查询只能有一个结果） 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 。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 -- 首先将李军的性别作为条件取出来 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 。

1 2 3 4 5 6 7 8 9 10 11 12 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 表中找到。

1 2 3 4 5 6 7 8 9 10 11 12 13 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 表代表学生的成绩等级，并插入数据：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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 之间。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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 | + ------+-------+-------+

连接查询

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 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 ( 内连接 ) 将它们连接在一起。

1 2 3 4 5 6 7 8 9 10 11 12 -- 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 。

1 2 3 4 5 6 7 8 9 -- 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 。

1 2 3 4 5 6 7 8 9 10 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 | 邮政卡 | + ------+--------+--------+------+-----------+

全外链接

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 -- 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 中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

比如我们的银行转账：

1 2 3 4 5 -- 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 中，事务的自动提交状态默认是开启的。

1 2 3 4 5 6 7 -- 查询事务的自动提交状态 SELECT @@AUTOCOMMIT; + --------------+ | @@AUTOCOMMIT | + --------------+ | 1 | + --------------+

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

什么是回滚？举个例子：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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 执行回滚：

1 2 3 4 5 6 7 8 9 -- 回滚到最后一次提交 ROLLBACK ; SELECT * FROM user ; + ----+------+-------+ | id | name | money | + ----+------+-------+ | 1 | a | 1000 | + ----+------+-------+

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

1 2 3 4 5 6 7 8 9 10 -- 关闭自动提交 SET AUTOCOMMIT = 0; -- 查询自动提交状态 SELECT @@AUTOCOMMIT; + --------------+ | @@AUTOCOMMIT | + --------------+ | 0 | + --------------+

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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 :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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 命令回滚事务。

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

1 2 3 4 5 6 7 8 9 10 11 12 13 -- 转账 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 回滚到最后一次提交的状态：

1 2 3 4 5 6 7 8 9 10 -- 假设转账发生了意外，需要回滚。 ROLLBACK ; SELECT * FROM user ; + ----+------+-------+ | id | name | money | + ----+------+-------+ | 1 | a | 1000 | | 2 | b | 1000 | + ----+------+-------+

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

手动开启事务 - BEGIN / START TRANSACTION

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 -- 使用 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 提交数据，提交后无法再发生本次事务的回滚。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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 ( 串行化 )
所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。

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

1 2 3 4 5 6 7 8 9 10 11 12 -- 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;

修改隔离级别：

1 2 3 4 5 6 7 8 9 10 -- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; -- 查询系统隔离级别，发现已经被修改。 SELECT @@ GLOBAL .TRANSACTION_ISOLATION; + --------------------------------+ | @@ GLOBAL .TRANSACTION_ISOLATION | + --------------------------------+ | READ - UNCOMMITTED | + --------------------------------+

脏读

测试 READ UNCOMMITTED ( 读取未提交 ) 的隔离性：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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 命令，会发生什么？

1 2 3 4 5 6 7 8 9 10 11 12 13 -- 小明所处的事务 ROLLBACK ; -- 此时无论对方是谁，如果再去查询结果就会发现： SELECT * FROM user ; + ----+-----------+-------+ | id | name | money | + ----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | 小明 | 1000 | | 4 | 淘宝店 | 1000 | + ----+-----------+-------+

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

读取已提交

把隔离级别设置为 READ COMMITTED ：

1 2 3 4 5 6 7 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED ; SELECT @@ GLOBAL .TRANSACTION_ISOLATION; + --------------------------------+ | @@ GLOBAL .TRANSACTION_ISOLATION | + --------------------------------+ | READ - COMMITTED | + --------------------------------+

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 -- 正在操作数据事务（当前事务） 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 | + ----+-----------+-------+

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 -- 小张在查询数据的时候发现： 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 ( 可被重复读取 ) :

1 2 3 4 5 6 7 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ ; SELECT @@ GLOBAL .TRANSACTION_ISOLATION; + --------------------------------+ | @@ GLOBAL .TRANSACTION_ISOLATION | + --------------------------------+ | REPEATABLE - READ | + --------------------------------+

测试 REPEATABLE READ ，假设在两个不同的连接上分别执行 START TRANSACTION :

1 2 3 4 5 6 7 8 9 -- 小张 - 成都 START TRANSACTION ; INSERT INTO user VALUES (6, 'd' , 1000); -- 小王 - 北京 START TRANSACTION ; -- 小张 - 成都 COMMIT ;

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

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

1 2 3 4 5 6 7 8 9 10 SELECT * FROM user ; + ----+-----------+-------+ | id | name | money | + ----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | 小明 | 1000 | | 4 | 淘宝店 | 1000 | | 5 | c | 100 | + ----+-----------+-------+

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

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

1 2	`INSERT` `INTO` `user` `VALUES` `(6,` `'d'` `, 1000);` `-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'`

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

串行化

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

1 2 3 4 5 6 7 SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE ; SELECT @@ GLOBAL .TRANSACTION_ISOLATION; + --------------------------------+ | @@ GLOBAL .TRANSACTION_ISOLATION | + --------------------------------+ | SERIALIZABLE | + --------------------------------+

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 -- 小张 - 成都 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数据库计有所帮助。

原文链接：https://segmentfault.com/a/1190000022468956

可以去百度分享获取分享代码输入这里。

声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。