MySQL必备的常见知识点汇总整理
16lz
2021-12-18
最近在整理 sql 的时候发现一份优秀的笔记，是原作者学习 sql 所做的笔记，分享这份总结给大家，对大家对 sql 的可以来一次全方位的检漏和排查，感谢原作者 hjzCy 的付出，原文链接放在文章最下方，如果出现错误，希望大家共同指出！
登录和退出 MySQL 服务器
# 登录MySQL$ mysql -u root -p12345612# 退出MySQL数据库服务器exit;
范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。
2NF
在满足第一范式的前提下，其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下：
-- 订单表CREATE TABLE myorder ( product_id INT, customer_id INT, product_name VARCHAR(20), customer_name VARCHAR(20), PRIMARY KEY (product_id, customer_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));
3NF
在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。
CREATE TABLE myorder ( order_id INT PRIMARY KEY, product_id INT, customer_id INT, customer_phone VARCHAR(15));
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;-- 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;
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 |+------+-------+--------+
-- 首先把 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_noHAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';+-------+-------------+----------+| c_no | AVG(degree) | COUNT(*) |+-------+-------------+----------+| 3-105 | 85.3333 | 3 || 3-245 | 76.3333 | 3 |+-------+-------------+----------+
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 |+------+-------+--------+
-- FROM...: 表示从 student, score 表中查询-- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。SELECT name, c_no, degree FROM student, scoreWHERE 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 |+-----------+-------+--------+
只有 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 |+------+-------+--------+
+-------+-----------------+| no | name |+-------+-----------------+| 3-105 | 计算机导论 || 3-245 | 操作系统 || 6-166 | 数字电路 || 9-888 | 高等数学 |+-------+-----------------+
-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。-- as 表示取一个该字段的别名。SELECT s_no, name as c_name, degree FROM score, courseWHERE 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 |+------+-----------------+--------+
只有 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 替换成 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 |+-----------+-------+--------+
-- 课程表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, degreeFROM student, score, courseWHERE student.NO = score.s_noAND score.c_no = course.no;
在 score 表中根据 student 表的学生编号筛选出学生的课堂号和成绩：
-- IN (..): 将筛选出的学生号当做 s_no 的条件查询SELECT s_no, c_no, degree FROM scoreWHERE 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 |+------+-------+--------+
SELECT c_no, AVG(degree) FROM scoreWHERE 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 |+-------+-------------+
首先筛选出课堂号为 3-105 ，在找出所有成绩高于 109 号同学的的行。
SELECT * FROM scoreWHERE c_no = '3-105'AND degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
-- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。SELECT * FROM scoreWHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
-- YEAR(..): 取出日期中的年份SELECT no, name, birthday FROM studentWHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));
首先找到教师编号：
SELECT NO FROM teacher WHERE NAME = '张旭'
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 = '张旭' ));
首先在 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 |+-------+-----------------+------+
-- 在此之前向 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 |+-------+SELECT t_no FROM course WHERE no IN ( SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5);+------+| t_no |+------+| 825 |+------+
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 ));
思路是，先找出 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 |+------+-------+--------+
-- NOT: 代表逻辑非SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN ( SELECT profession FROM teacher WHERE department = '电子工程系')-- 合并两个集UNIONSELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN ( SELECT profession FROM teacher WHERE department = '计算机系');
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: 符合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 |+------+-------+--------+
-- 查询平均分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 |+------+-------+--------+
SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);+--------+-----------------+| name | department |+--------+-----------------+| 李诚 | 计算机系 || 王萍 | 计算机系 || 刘冰 | 电子工程系 || 张旭 | 电子工程系 |+--------+-----------------+
-- 查看学生表信息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 |+-------+
-- 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()) 计算出当前年份，减去出生年份后得出年龄。SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;+-----------+------+| name | age |+-----------+------+| 曾华 | 42 || 匡明 | 44 || 王丽 | 43 || 李军 | 43 || 王芳 | 44 || 陆军 | 45 || 王尼玛 | 43 || 张全蛋 | 44 || 赵铁柱 | 45 || 张飞 | 45 |+-----------+------+
SELECT MAX(birthday), MIN(birthday) FROM student;+---------------+---------------+| MAX(birthday) | MIN(birthday) |+---------------+---------------+| 1977-09-01 | 1974-06-03 |+---------------+---------------+
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 |+-----+-----------+-----+------------+-------+
SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');+-------+--------------+------+| no | name | t_no |+-------+--------------+------+| 3-245 | 操作系统 | 804 || 6-166 | 数字电路 | 856 |+-------+--------------+------+
-- 找出最高成绩（该查询只能有一个结果）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 |+------+-------+--------+
-- 首先将李军的性别作为条件取出来SELECT sex FROM student WHERE name = '李军';+-----+| sex |+-----+| 男 |+-----+-- 根据性别查询 name 和 sexSELECT name, sex FROM student WHERE sex = ( SELECT sex FROM student WHERE name = '李军');+-----------+-----+| name | sex |+-----------+-----+| 曾华 | 男 || 匡明 | 男 || 李军 | 男 || 陆军 | 男 || 王尼玛 | 男 || 张全蛋 | 男 || 赵铁柱 | 男 || 张飞 | 男 |+-----------+-----+
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 |+-----------+-----+-------+
需要的 "计算机导论" 和性别为 "男" 的编号可以在 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 |+------+-------+--------+
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 |+------+------+-------+
思路是，使用区间 ( BETWEEN ) 查询，判断学生的成绩 ( degree ) 在 grade 表的 low 和 upp 之间。
SELECT s_no, c_no, grade FROM score, gradeWHERE 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 |+------+--------+--------+
内连接
要查询这两张表中有关系的数据，可以使用 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;
-- 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 |+------+--------+--------+------+-----------+
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.idUNIONSELECT * 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 | 邮政卡 |+------+--------+--------+------+-----------+
比如我们的银行转账：
-- a -> -100UPDATE user set money = money - 100 WHERE name = 'a';-- b -> +100UPDATE user set money = money + 100 WHERE name = 'b';
因此，在执行多条有关联 SQL 语句时，事务可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。
如何控制事务 - COMMIT / ROLLBACK
在 MySQL 中，事务的自动提交状态默认是开启的。
-- 查询事务的自动提交状态SELECT @@AUTOCOMMIT;+--------------+| @@AUTOCOMMIT |+--------------+| 1 |+--------------+
什么是回滚？举个例子：
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 中使用 ROLLBACK 执行回滚：
-- 回滚到最后一次提交ROLLBACK;SELECT * FROM user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 1000 |+----+------+-------+
-- 关闭自动提交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 |+----+------+-------+
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;SELECT * FROM user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 1000 || 2 | b | 1000 |+----+------+-------+
手动开启事务 - 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 |+----+------+-------+
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;
- 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.xSELECT @@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 |+--------------------------------+
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;-- 此时无论对方是谁，如果再去查询结果就会发现：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 |+------------+
幻读
将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;SELECT @@GLOBAL.TRANSACTION_ISOLATION;+--------------------------------+| @@GLOBAL.TRANSACTION_ISOLATION |+--------------------------------+| REPEATABLE-READ |+--------------------------------+
-- 小张 - 成都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 |+----+-----------+-------+
然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？
INSERT INTO user VALUES (6, 'd', 1000);-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
串行化
顾名思义，就是所有事务的写入操作全都是串行化的。什么意思？把隔离级别修改成 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);
根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行 COMMIT 结束它所处的事务，或者出现等待超时。
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
更多相关文章
- Android开发小知识点集锦
- android引入so文件常见问题
- Android简单计算器（已消除常见bug）
- Android(安卓)studio 常见错误以及问题
- flutter常见问题
- android中操作SQLite常见错误
- 其他小知识点
- 其他小知识点
- android UI 小知识点