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;