参考资料来源于Bilibili《一天学会MYSQL数据库》
1、多表查询
(1)查询所有学生的s_name,c_no和grade列
由数据可知，s_name在student表，c_no和grade在score表，那么我们要考虑如何将两个表联立起来。
student表中的s_no和score表中的s_no通过外键联系在一起。因此我们采用以下的方法：
select s_name,c_no,grade from student,score where student.s_no = score.s_no;
--result
+--------+-------+-------+
| s_name | c_no | grade |
+--------+-------+-------+
| 王丽 | 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)查询所有学生的 s_name, c_name, grade列
三表联查
select s_name, c_name, grade from student,course,score where student.s_no=score.s_no and course.c_no=score.c_no；
--result
+--------+------------+-------+
| s_name | c_name | grade |
+--------+------------+-------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
+--------+------------+-------+
由下面的查询结果可知上述语句的含义是查询了三个表中共同字段相等的记录。
select s_name, c_name, grade,student.s_no as stu_sno, score.s_no as sco_sno
from student,course,score
where student.s_no=score.s_no and course.c_no=score.c_no;
--result
+--------+------------+-------+---------+---------+
| s_name | c_name | grade | stu_sno | sco_sno |
+--------+------------+-------+---------+---------+
| 王丽 | 计算机导论 | 92 | 103 | 103 |
| 王丽 | 操作系统 | 86 | 103 | 103 |
| 王丽 | 数字电路 | 85 | 103 | 103 |
| 王芳 | 计算机导论 | 88 | 105 | 105 |
| 王芳 | 操作系统 | 75 | 105 | 105 |
| 王芳 | 数字电路 | 79 | 105 | 105 |
| 赵铁柱 | 计算机导论 | 76 | 109 | 109 |
| 赵铁柱 | 操作系统 | 68 | 109 | 109 |
| 赵铁柱 | 数字电路 | 81 | 109 | 109 |
+--------+------------+-------+---------+---------+
(3)查询"男"教师 及其所上的课
select t.t_name, c.c_no, c.c_name from teacher as t, course as c where t_sex = '男' and t.t_no = c.t_no;
-- result
+--------+-------+----------+
| t_name | c_no | c_name |
+--------+-------+----------+
| 李诚 | 3-245 | 操作系统 |
| 张旭 | 6-166 | 数字电路 |
+--------+-------+----------+
(4)按照等级进行查询
首先新建grade_table表，将分数的等级放进去；
CREATE TABLE grade_table(
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');
查询所有同学的s_no , c_no 和等级列
select s_no, c_no, grade_table.grade from score, grade_table
where score.grade between low and upp;
-- result
+------+-------+-------+
| 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 |
+------+-------+-------+
总结：
- select 在同时查询两个表时，如果两个表中有相同的字段，要在字段前加入表名；
2.select同时查询两个表时，返回的结果是两个表结果的笛卡尔积。上述的where在这个笛卡尔积中进行条件过滤，返回符合条件的记录。
2、子查询
(1)查询班级是'95031'班学生每门课的平均分
涉及到的表：student表,score表
首先将班级为“95031”的学生信息提出，再按照课程进行分组求平均数
select * from student where s_class='95031';
-- 我们将其中的s_no数据提出作为查询score表时的条件
select * from score where s_no in (select s_no from student where s_class='95031');
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 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(grade) from score where s_no in (select s_no from student where s_class='95031') group by c_no;
--result
+-------+------------+
| c_no | avg(grade) |
+-------+------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+------------+
(2)查询选修"3-105"课程的成绩高于'109'号同学'3-105'成绩 的所有同学的记录
select * from score
where c_no='3-105' and
grade > ( select grade from score where s_no='109' and c_no='3-105');
(3) 查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday
用到的函数：year()函数提取日期类型数据中的年份
select s_no,s_name from student
where year(s_birth) in (select year(s_birth)
from student where s_no in('108', '101'));
--result
+------+--------+
| s_no | s_name |
+------+--------+
| 101 | 曾华 |
| 102 | 匡明 |
| 105 | 王芳 |
| 108 | 张全蛋 |
+------+--------+
(4)查询教师张旭所任课程的学生成绩
select * from score where s_no in
(select s_no from score where c_no =
(select c_no from course
where t_no = (select t_no from teacher where t_name='张旭')));
(5)查询选修某课程的同学人数多于5人的教师姓名
首先向数据表中添加数据
INSERT INTO score VALUES('101','3-105','90');
INSERT INTO score VALUES('102','3-105','91');
INSERT INTO score VALUES('104','3-105','89');
查询：
select t_name from teacher where t_no in (select t_no from course
where c_no in (select c_no from score group by c_no having count(*) > 5));
进行分组后才可以统计数量；
(6) 查询95033班和95031班全体学生的记录
select * from student where s_class in ('95033','95031');
--result
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birth | s_class |
+------+--------+-------+---------------------+---------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+------+--------+-------+---------------------+---------+
(7) 查出所有'计算机系' 教师所教课程的成绩表
select * from score
where c_no in (select c_no from course
where t_no in (select t_no from teacher where t_depart= '计算机系'));
--result
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 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 |
+------+-------+-------+
(8)查询所有任课教师的t_name 和 t_depart(注意是任课的)
select t_name, t_depart from teacher
where t_no in (select t_no from course where c_no in (select c_no from score group by c_no));
-- result
+--------+------------+
| t_name | t_depart |
+--------+------------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 张旭 | 电子工程系 |
+--------+------------+
3.UNION和NOT IN的使用
语法：UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。如果要使得数据不重复，需要使用UNION ALL。
(1) 查询'计算机系'与'电子工程系' 不同职称的教师的name和rof
含义：除了在计算机系和电子工程系中同时出现的职称的所有记录
select t_name,t_prof from teacher where t_depart = '计算机系'
and t_prof not in (select t_prof from teacher where t_depart ='电子工程系')
union
select t_name,t_prof from teacher where t_depart = '电子工程系'
and t_prof not in (select t_prof from teacher where t_depart = '计算机系');
--result
+--------+--------+
| t_name | t_prof |
+--------+--------+
| 李诚 | 副教授 |
| 张旭 | 讲师 |
+--------+--------+
4.ANY和ALL的使用
(1) 查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的c_no,s_no和grade,并且按照grade从高到低次序排序
select * from score where c_no = '3-105'
and grade > (select min(grade) from score where c_no = '3-245') order by grade desc;
题中提到“至少高于”，因此也可以用any表示，any(数据集）表示其中至少一个。
select * from score where c_no = '3-105'
and grade > any(select grade from score where c_no = '3-245') order by grade desc;
(2)查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和grade
select * from score where c_no = '3-105'
and grade > (select max(grade) from score where c_no = '3-245') order by grade desc;
也可以用all表示：
select * from score where c_no = '3-105'
and grade > all (select grade from score where c_no = '3-245') order by grade desc;
总结: ANY 和 ALL
ANY:表示比任何一个就行了。
ALL:表示所有都要比较。
5.别名的使用
规则：
a. select 字段名 as 别名。
b. 当两个表连接时，union后的表列名与第一个表保持一致。
select t_name as name, t_sex as sex, t_birth as birth from teacher
union
select s_name, s_sex, s_birth from student;
-- result
+--------+-----+---------------------+
| name | sex | birth |
+--------+-----+---------------------+
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
| 曾华 | 男 | 1977-09-01 00:00:00 |
| 匡明 | 男 | 1975-10-02 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 李军 | 男 | 1976-02-20 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 陆军 | 男 | 1974-06-03 00:00:00 |
| 王尼玛 | 男 | 1976-02-20 00:00:00 |
| 张全蛋 | 男 | 1975-02-10 00:00:00 |
| 赵铁柱 | 男 | 1974-06-03 00:00:00 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
+--------+-----+---------------------+
注意：连接两个表时，两个表的字段顺序要保持一致。举例来说，如果将上述student表的查询顺序换为s_sex,s_birth,s_name，则结果变为：
-- 字段顺序不匹配结果
+------+---------------------+---------------------+
| name | sex | birth |
+------+---------------------+---------------------+
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
| 男 | 1977-09-01 00:00:00 | 曾华 |
| 男 | 1975-10-02 00:00:00 | 匡明 |
| 女 | 1976-01-23 00:00:00 | 王丽 |
| 男 | 1976-02-20 00:00:00 | 李军 |
| 女 | 1975-02-10 00:00:00 | 王芳 |
| 男 | 1974-06-03 00:00:00 | 陆军 |
| 男 | 1976-02-20 00:00:00 | 王尼玛 |
| 男 | 1975-02-10 00:00:00 | 张全蛋 |
| 男 | 1974-06-03 00:00:00 | 赵铁柱 |
| 男 | 1974-06-03 00:00:00 | 张飞 |
+------+---------------------+---------------------+
除了字段顺序要匹配，数量也要匹配，如果两个union查询的字段数量不一致，sql会进行报错。
(3)查询所有'女'教师和'女'学生的name,sex,birthday
select t_name as name, t_sex as sex, t_birth as birth from teacher where t_sex = '女'
union
select s_name, s_sex, s_birth from student where s_sex = '女';
总结：使用了别名之后的查询字段仍然是原字段名
6.复制表数据做条件查询
(1)查询成绩比该课程平均成绩低的同学的成绩表
select * from score as a
where grade < (select avg(grade) from score as b where a.c_no = b.c_no);
-- result
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+-------+
总结：
查询平均值的两个方法：
a. 先按照某字段进行分组，再求每一组的平均值
select avg(grade) from score group by c_no ；
b.指定求某一条件的平均值
select avg(grade) from score where c_no = '3-105';
7.条件加分组筛选
(1) 查出至少有2名男生的班号
select s_class from student group by s_class having count(s_sex = '男') > 2;
也可以先查出男生的记录，再按照班级分组，再写计数的条件，如下：
select s_class from student where s_sex = '男' group by s_class having count(*) > 1;
-- result
+---------+
| s_class |
+---------+
| 95033 |
| 95031 |
+---------+
总结：
a. group by 对相同的字段值进行聚合，聚合后形成的各个分组可以使用sum,count,avg等聚合函数。如果要对分组加入条件，则使用having。详情见：where和having的区别
b.count函数可以内置条件，但是最好加上or null 如：count(s_sex = '男' or null)，具体原因见：count 中加条件为什么要加or null
8.模糊查询
(1)year，now函数：查询student 表中 不姓"王"的同学的记录
select * from student where s_name not like '王%';
总结：
like和not like进行模糊匹配
9.高级函数
(1)查询student 中每个学生的姓名和年龄
select s_name, year(now())- year(s_birth) as age from student;
-- result
+--------+------+
| s_name | age |
+--------+------+
| 曾华 | 44 |
| 匡明 | 46 |
| 王丽 | 45 |
| 李军 | 45 |
| 王芳 | 46 |
| 陆军 | 47 |
| 王尼玛 | 45 |
| 张全蛋 | 46 |
| 赵铁柱 | 47 |
| 张飞 | 47 |
+--------+------+
总结：
a.now()函数返回当前系统的日期；
b.year()函数返回日期类型的年份。
(2)多字段排序：以班级号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by s_class desc, s_birth;
-- result
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birth | s_class |
+------+--------+-------+---------------------+---------+
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
+------+--------+-------+---------------------+---------+
--
总结：
第一排序规则放在前面，第二排序规则依次在后。
网友评论