MySQL查询实战6例及讲解
内容梗概
本文将通过一组数据表及其数据查询案例，来讲解关于
（1）对表和字段使用别名
（2）联表查询
（3）聚合函数
（4）JOIN与,
（5）HAVING
（6）IN，EXISTS，NOT IN
（7）DISTINCT
（8）LIKE
...
数据准备
为了更好的实战和理解本文内容，笔者准备了如下五张表，分别是学生表，班级表，课程表，教师表以及成绩表：
t_x_student 学生表
t_x_class 班级表
t_x_course 课程表
t_x_teacher 教师表
t_x_score 成绩表
数据库的模型设计
模型设计遵循了三范式及设计原则，如下：
设计模型
案例需求
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报王佩佩老师课的学生姓名
4、查询选修自然课程和社会课程其中一门的学生姓名
5、查询挂科超过两门(包括两门)的学生姓名和班级
6、查询同时选了王佩佩老师所有课的学生班级和姓名
案例解答
（1）查询所有的课程的名称以及对应的任课老师姓名
分析：我们需要用到t_x_course和t_x_teacher表：既需要得到课程名称又要拿到老师姓名，然后看表结构模型，我们可以知道t_x_course有外键字段teacher_id指向t_x_teacher表t_id，那么我们就可以用内连接inner join将两张表拼接起来然后取其字段t_x_course.c_name和t_x_teacher.t_name即可得到我们想要的数据，SQL语句如下：
SELECT
cor.c_name AS course,
tea.t_name AS teacher
FROM
t_x_course cor
LEFT JOIN
t_x_teacher tea
ON cor.teacher_id = tea.t_id;
也可以直接进行多表查询
SELECT
cor.c_name AS course,
tea.t_name AS teacher
FROM
t_x_course cor,
t_x_teacher tea
WHERE
cor.teacher_id = tea.t_id;
知识点：
【使用别名】
使用别名是为了简化SQL或者语义化表名和字段名，本例中对查询结果使用了
AS对cor.c_name创建了别名course，同样对标t_x_course使用空格创建了别名cor，两种方式在使用上没有区别。
【联表查询 】
联表查询 有三种类别（1）
INNER JOIN：表示两个表同时存在数据时返回该记录；（2）LEFT JOIN：左表存在数据即返回该记录，即便在ON条件下，右表无数据；（3）RIGHT JOIN：右表存在即返回该记录
（2）查询平均成绩大于八十分的同学的姓名和平均成绩
分析：需要用到t_x_score表和t_x_shtudent表，既要拿到学生姓名又要拿到成绩，我们理所当然需要将这两个表联表或者做子连接，然后需求中需要用到平均数，那么我们应想到用聚合函数avg()，但使用聚合函数的前提是分组即需要使用到GROUP BY
首先在联表或子连接前可以通过t_x_score表分组得到student_id和平均成绩
SELECT
student_id,
avg(score) as avg_score
FROM
t_x_score
GROUP BY
student_id
HAVING AVG(score) > 80;
然后在以上虚拟表的基础上通过student_id拼接student表，取student.sname和avg_score即可
SELECT
stu.s_name AS student,
t_avg_score.avg_score
FROM
t_x_student stu
INNER JOIN
(
SELECT
student_id,
avg(score) AS avg_score
FROM
t_x_score
GROUP BY student_id
HAVING AVG(score) > 80
) AS t_avg_score
ON
t_avg_score.student_id = stu.s_id
ORDER BY
avg_score DESC;
或直接使用多表查询如下：
SELECT
stu.s_name AS student,
t_avg_score.avg_score
FROM
t_x_student stu,
(
SELECT
student_id,
AVG(score) AS avg_score
FROM
t_x_score
GROUP BY student_id
HAVING AVG(score) > 80
) t_avg_score
WHERE
t_avg_score.student_id = stu.s_id
ORDER BY
avg_score DESC;
知识点：
【聚合函数】
聚合函数（aggregate function）会对一组值执行计算并返回计算结果单个值。 所有聚合函数都是确定性的。
在select列表或SELECT语句的HAVING子句中允许使用它们。 可以将聚合与GROUP BY子句结合使用，来计算行类别的聚合。 使用 OVER 子句来计算特定范围内的值的聚合。
常见的聚合函数：
（1）求个数/记录数/项目数等：count( )包括空值/count(*)不包括空值；
（2）求某一列平均数 ：avg()；注意null行会被忽略，也可以使用isNull(score, 0)将null行转换成0；
（3）求总和，总分等：sum()；必须为数字列
（4）求最大值，最高分，最高工资等：max()
（5）求最小值，最低分，最低工资等：min()
（6）求指定表达式中所有值的方差：var()
【HAVING 与 WHERE】
这两者都是对表中的数据进行过滤筛选，不同的是：
HAVING子句可以让我们在聚合后对组记录进行筛选。我们知道聚合函数可以将一组值进行计算后返回计算结果的单个值，而这个值并不是原表中的某个字段，只是一个虚拟的计算结果。当然HAVING也是允许用来过滤真实字段。
WHERE子句只能过滤表中存在的字段名称，当用来过滤虚拟字段时就会被提示错误。
（3）查询没有报王佩佩老师课的学生姓名
分析：根据表结构我们发现并没有存在一个直接关联老师和学生的表，但是t_x_score表中标注了哪个同学某个科目的考试成绩，而每个课程有对应的授课教师，这样假设每个学生都参加了考试的情况下，我们就可以得到学生和老师的对应关系。也就是说，我们需要用到t_x_student，t_x_score，t_x_course，t_x_teacher这4张表，直接得到没有报王佩佩老师课程的学生比较困难，那么我们就反过来想，哪些是报了王佩佩老师课程的，然后在学生表里剔除掉即可
先找到王佩佩老师教了哪些课程
SELECT
cor.c_id AS course_id,
cor.c_name AS course_name,
t_teacher.t_name AS teacher
FROM
t_x_course cor
INNER JOIN
(
SELECT
t_name,
t_id
FROM
t_x_teacher
WHERE
t_name = '王佩佩'
) AS t_teacher
ON
cor.teacher_id = t_teacher.t_id;
本例中发现王佩佩老师只教授了一门“社会”课，如果是多门课，需要使用IN关键字查找，分析过程简化，找到选修21005课程的学生列表：
SELECT
student_id,
course_id
FROM
t_x_score
WHERE
course_id = '21005';
这样，我们就得到了选修了王佩佩老师课程的学生
id列表。然后在学生表中使用NOT IN做反向剔除即可。结合以上查找逻辑，有了完整的语句如下：
SELECT
stu.s_name AS student
FROM
t_x_student stu
WHERE
s_id NOT IN
(
SELECT
student_id
FROM
t_x_score
WHERE
course_id IN
(
SELECT
cor.c_id
FROM
t_x_course cor
INNER JOIN
(
SELECT
t_id
FROM
t_x_teacher
WHERE
t_name = '王佩佩'
) AS t_teacher
ON
cor.teacher_id = t_teacher.t_id
)
);
知识点
【IN 和 NOT IN】
IN操作符允许我们在WHERE子句中规定多个值，相当于多个 or 条件的叠加，比较好理解。in查询的子条件返回结果必须只有一个字段，因为它要从这个结果中进行合并查询。
SELECT col FROM table1 WHERE col_name IN (value1, value2, ...)
NOT IN取的是IN的对立面
【EXISTS和NOT EXISTS】
EXISTS运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回True，否则返回False。
它对外表用 loop 逐条查询，每次查询都会查看exists的条件语句。当EXISTS查询子语句的条件语句能够返回记录行时（无论返回多少记录行，只要能返回），条件就为真，就返回当前 loop 到的这条记录，反之如果条件语句不能返回记录行，则当前 loop 到的这条记录被丢弃。就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false。
当子查询为select NULL时， mysql 仍然认为它是True
NOT EXISTS是EXISTS的对立面
【IN与EXISTS区别】
in语句：确定给定的值是否与子查询或列表中的值相匹配
exists语句：执行n次（外表行数），指定一个子查询，检测行的存在，遍历循环外表，检查外表中的记录有没有和内表的的数据一致的，匹配得上就放入结果集
使用上：外层查询表小于子查询表，则用exists，外层查询表大于子查询表，则用in。但是无论哪个表大，not exists比not in效率高
（4）查询选修自然课程和社会课程其中一门的学生姓名
分析：没有选修课程与学生表之间的直接关系，但是成绩表中有对应关系，因此，需要使用到学生表，课程表，和成绩表
（1）因为成绩表中只有课程id，因此找到课程表中的'自然', '社会'的课程id
SELECT
c_id
FROM
t_x_course
WHERE
c_name IN ('自然', '社会')
（2）在成绩表中找到有该课程id的学生id
SELECT
student_id
FROM
t_x_score
WHERE
course_id IN ('21004', '21005')
（3）选出只报了一门的课程的学生姓名，即 count(student_id)值为 1 的学生
（4）在学生表中找到这些学生id的学生姓名
SELECT
s_name AS student
FROM
t_x_student
WHERE
s_id IN
(
SELECT
student_id
FROM
t_x_score
WHERE
course_id IN
(
SELECT
c_id
FROM
t_x_course
WHERE
c_name IN ('自然', '社会')
)
GROUP BY
student_id
HAVING
COUNT(student_id) = 1
)
（5） 查询挂科超过两门(包括两门)的学生姓名和班级
分析：本需求和第4个类似。我们需要在成绩表中拿到挂科超过两门的学生id，然后根据学生id在学生表中查出学生姓名，再根据学生的班级id查出班级名称，即，需要使用到学生表，班级表，成绩表
SELECT
cls.caption,
k.s_name AS student
FROM
t_x_class cls
INNER JOIN
(
SELECT
s_name,
class_id
FROM
t_x_student stu
INNER JOIN
(
SELECT student_id FROM t_x_score WHERE score < 60 GROUP BY student_id HAVING COUNT(student_id) > 1
) sco
ON
sco.student_id = stu.s_id
) k
ON
k.class_id = cls.c_id;
（6） 查询同时选了王佩佩老师所有课的学生班级和姓名
分析：根据需求，需要先在教师表中找到王佩佩老师的教师id，再去课程表找到王佩佩老师教授的所有课程，然后根据这些课程id在成绩表中查询出学生的id，再通过这些学生的id在学生表中找到这些学生的姓名和学生所在的班级id，最后找到班级id找到班级名称。也就是说，本需求5个表都需要使用到。
- 找到王佩佩老师的教师id
SELECT
t_id
FROM
t_x_teacher
WHERE
t_name = '王佩佩';
- 根据教师id查到王佩佩老师教授了哪些课程
SELECT
c_id
FROM
t_x_course cos
INNER JOIN
( SELECT t_id FROM t_x_teacher WHERE t_name = '王佩佩' ) tea
ON tea.t_id = cos.teacher_id;
- 在成绩表中找到选修了这些课程id的学生的学生id
SELECT
sco.student_id
FROM
t_x_score sco
WHERE
sco.course_id IN
(
SELECT
c_id
FROM
t_x_course cos
INNER JOIN
( SELECT t_id FROM t_x_teacher WHERE t_name = '王佩佩' ) tea
ON tea.t_id = cos.teacher_id
);
- 在学生表中找到这些学生id的学生姓名以及班级id
SELECT
stu.s_name AS student,
stu.class_id
FROM
t_x_student stu
INNER JOIN
(
SELECT
sco.student_id
FROM
t_x_score sco
WHERE
sco.course_id IN
(
SELECT
c_id
FROM
t_x_course cos
INNER JOIN
( SELECT t_id FROM t_x_teacher WHERE t_name = '王佩佩' ) tea
ON tea.t_id = cos.teacher_id
)
) k
ON
k.student_id = stu.s_id;
- 将表格中的班级id替换成班级名称
SELECT
s.s_name AS student_id,
clas.caption
FROM
t_x_class clas
INNER JOIN
(
SELECT
stu.s_name,
stu.class_id
FROM
t_x_student stu
INNER JOIN
(
SELECT
sco.student_id
FROM
t_x_score sco
WHERE
sco.course_id IN
(
SELECT
c_id
FROM
t_x_course cos
INNER JOIN
( SELECT t_id FROM t_x_teacher WHERE t_name = '王佩佩' ) tea
ON tea.t_id = cos.teacher_id
)
) k
ON
k.student_id = stu.s_id
) s
ON
s.class_id = clas.c_id;
