MySQL查询实战6例及讲解

作者: 果汁凉茶丶 | 来源:发表于2020-10-19 19:19 被阅读0次

内容梗概

本文将通过一组数据表及其数据查询案例，来讲解关于
（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;

MySQL查询实战6例及讲解

内容梗概

数据准备

数据库的模型设计

案例需求

案例解答

（1）查询所有的课程的名称以及对应的任课老师姓名

（2）查询平均成绩大于八十分的同学的姓名和平均成绩

（3）查询没有报王佩佩老师课的学生姓名

（4）查询选修自然课程和社会课程其中一门的学生姓名

（5）查询挂科超过两门(包括两门)的学生姓名和班级

（6）查询同时选了王佩佩老师所有课的学生班级和姓名

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库

MySQL查询实战6例及讲解

内容梗概

数据准备

数据库的模型设计

案例需求

案例解答

（1）查询所有的课程的名称以及对应的任课老师姓名

（2）查询平均成绩大于八十分的同学的姓名和平均成绩

（3）查询没有报王佩佩老师课的学生姓名

（4）查询选修自然课程和社会课程其中一门的学生姓名

（5） 查询挂科超过两门(包括两门)的学生姓名和班级

（6） 查询同时选了王佩佩老师所有课的学生班级和姓名

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库

（5）查询挂科超过两门(包括两门)的学生姓名和班级

（6）查询同时选了王佩佩老师所有课的学生班级和姓名