MySQL查询实战6例及讲解

内容梗概

  本文将通过一组数据表及其数据查询案例,来讲解关于
(1)对表和字段使用别名
(2)联表查询
(3)聚合函数
(4)JOIN,
(5)HAVING
(6)INEXISTSNOT 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_courset_x_teacher表:既需要得到课程名称又要拿到老师姓名,然后看表结构模型,我们可以知道t_x_course有外键字段teacher_id指向t_x_teachert_id,那么我们就可以用内连接inner join将两张表拼接起来然后取其字段t_x_course.c_namet_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或者语义化表名和字段名,本例中对查询结果使用了AScor.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.snameavg_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()

HAVINGWHERE

这两者都是对表中的数据进行过滤筛选,不同的是:
HAVING子句可以让我们在聚合后对组记录进行筛选。我们知道聚合函数可以将一组值进行计算后返回计算结果的单个值,而这个值并不是原表中的某个字段,只是一个虚拟的计算结果。当然HAVING也是允许用来过滤真实字段。
WHERE子句只能过滤表中存在的字段名称,当用来过滤虚拟字段时就会被提示错误。

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

  分析:根据表结构我们发现并没有存在一个直接关联老师和学生的表,但是t_x_score表中标注了哪个同学某个科目的考试成绩,而每个课程有对应的授课教师,这样假设每个学生都参加了考试的情况下,我们就可以得到学生和老师的对应关系。也就是说,我们需要用到t_x_studentt_x_scoret_x_courset_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
            )
    );

知识点
INNOT IN

IN 操作符允许我们在 WHERE 子句中规定多个值,相当于多个 or 条件的叠加,比较好理解。in查询的子条件返回结果必须只有一个字段,因为它要从这个结果中进行合并查询。
SELECT col FROM table1 WHERE col_name IN (value1, value2, ...)
NOT IN 取的是 IN的对立面

EXISTSNOT EXISTS

EXISTS 运算符用于判断查询子句是否有记录,如果有一条或多条记录存在返回 True,否则返回 False
 它对外表用 loop 逐条查询,每次查询都会查看 exists 的条件语句。当EXISTS查询子语句的条件语句能够返回记录行时(无论返回多少记录行,只要能返回),条件就为真,就返回当前 loop 到的这条记录,反之如果条件语句不能返回记录行,则当前 loop 到的这条记录被丢弃。就像一个 bool 条件,当能返回结果集则为 true,不能返回结果集则为 false
当子查询为 select NULL 时, mysql 仍然认为它是 True
NOT EXISTSEXISTS的对立面

INEXISTS区别

in 语句:确定给定的值是否与子查询或列表中的值相匹配
exists 语句:执行n次(外表行数),指定一个子查询,检测行的存在,遍历循环外表,检查外表中的记录有没有和内表的的数据一致的,匹配得上就放入结果集
使用上:外层查询表小于子查询表,则用 exists,外层查询表大于子查询表,则用 in。但是无论哪个表大,not existsnot 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个表都需要使用到。

  1. 找到王佩佩老师的教师id
SELECT
    t_id
FROM
    t_x_teacher
WHERE
    t_name = '王佩佩';
  1. 根据教师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;
  1. 在成绩表中找到选修了这些课程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
    );
  1. 在学生表中找到这些学生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;
  1. 将表格中的班级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;