MySQL实战二：多种查询方案

公众号guangcity

发布于 2019-09-20 17:29:42

7990

发布于 2019-09-20 17:29:42

文章被收录于专栏：光城(guangcity)光城(guangcity)

MySQL学习仓库Up-Up-MySQL，这是一个学习MySQL从入门实战到理论完善，再到精通的一个仓库，后面会把MySQL的学习资料上传上去！欢迎大家star与fork起来！

仓库地址：

https://github.com/Light-City/Up-Up-MySQL

也可以点击阅读原文！

今天上手第二弹，多种查询方案查询数据。

查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

mysql> select s.SId,s.Sname,count(s.SId) '选课总和',sum(sc.score) '成绩总和' from Student s left join SC sc on s.SId=sc.SId group by s.SId;
+-----+--------+--------------+--------------+
| SId | Sname  | 选课总和     | 成绩总和     |
+-----+--------+--------------+--------------+
| 01  | 赵雷   |            3 |        269.0 |
| 02  | 钱电   |            3 |        210.0 |
| 03  | 孙风   |            3 |        240.0 |
| 04  | 李云   |            3 |        100.0 |
| 05  | 周梅   |            2 |        163.0 |
| 06  | 吴兰   |            2 |         65.0 |
| 07  | 郑竹   |            2 |        187.0 |
| 09  | 张三   |            1 |         NULL |
| 10  | 李四   |            1 |         NULL |
| 11  | 李四   |            1 |         NULL |
| 12  | 赵六   |            1 |         NULL |
| 13  | 孙七   |            1 |         NULL |
+-----+--------+--------------+--------------+
12 rows in set (0.00 sec)

注意：题目中说的是查询所有同学的相关信息，然而有些同学的信息在SC成绩表中是没有的，因为他们有可能没有成绩，这些人的数据也需要被查询出来，所有需要左连接查询！

查询成绩的学生信息

分别使用in与exists子查询：

mysql> select * from Student s where s.SId in(select sc.SId from SC sc);
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
7 rows in set (0.00 sec)

mysql> select * from Student s where exists(select sc.SId from SC sc where s.SId=sc.SId);
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
7 rows in set (0.00 sec)

查询「李」姓老师的数量

mysql> select count(t.TId) '数量' from Teacher t where t.Tname like '李%';
+--------+
| 数量   |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

查询学过「张三」老师授课的同学的信息

考察：多表联合查询

涉及四张表！

mysql> select s.* from Student s,Course c,Teacher t,SC sc where t.TId=c.TId and c.CId=sc.CId and sc.SId=s.SId and t.Tname='张三';
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
6 rows in set (0.00 sec)

查询没有学全所有课程的同学的信息

因为有学生什么课都没有选，反向思考，先查询选了所有课的学生，再选择这些人之外的学生。

mysql> select s.* from Student s where s.SId not in(select sc.SId from SC sc group by sc.SId having count(sc.SId)=(select count(c.CId) from Course c));
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
| 09  | 张三   | 2017-12-20 00:00:00 | 女   |
| 10  | 李四   | 2017-12-25 00:00:00 | 女   |
| 11  | 李四   | 2012-06-06 00:00:00 | 女   |
| 12  | 赵六   | 2013-06-13 00:00:00 | 女   |
| 13  | 孙七   | 2014-06-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
8 rows in set (0.00 sec)

这道题进一步思考，如果要查询没有学全所有课程的同学的信息当中每个学生的选课个数。

注意：你或许第一反应，这还不简单，直接与SC成绩表联合查询不就得了，可是那如果当学生没有选择任何课程的时候，这些信息是查不到的，也就是只能输出查询到的学生信息，如下所示：

mysql> select s1.*,count(s1.SId) from (select s.* from Student s where s.SId not in(select sc.SId from SC sc group by sc.SId having count(sc.SId)=(select count(c.CId) from Course c))) s1 join SC sc on s1.SId=sc.SId group by s1.SId;
+-----+--------+---------------------+------+---------------+
| SId | Sname  | Sage                | Ssex | count(s1.SId) |
+-----+--------+---------------------+------+---------------+
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |             2 |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |             2 |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |             2 |
+-----+--------+---------------------+------+---------------+
3 rows in set (0.00 sec)

注意：那换成left join不就行了嘛，真的可以？如下所示：上述查询的count(sc.SId)不能为count(s.SId)，如果写成这个，也会查出与理想不符合，因为不管SC成绩表中有没有数据，count(s.SId)至少为1，而实际情况可以为0。

mysql> mysql> select s1.*,count(s1.SId) from (select s.* from Student s where s.SId not in(select sc.SId from SC sc group by sc.SId having count(sc.SId)=(selec(c.CId) from Course c))) s1 left join SC sc on s1.SId=sc.SId group by s1.SId;
+-----+--------+---------------------+------+---------------+
| SId | Sname  | Sage                | Ssex | count(s1.SId) |
+-----+--------+---------------------+------+---------------+
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |             2 |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |             2 |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |             2 |
| 09  | 张三   | 2017-12-20 00:00:00 | 女   |             1 |
| 10  | 李四   | 2017-12-25 00:00:00 | 女   |             1 |
| 11  | 李四   | 2012-06-06 00:00:00 | 女   |             1 |
| 12  | 赵六   | 2013-06-13 00:00:00 | 女   |             1 |
| 13  | 孙七   | 2014-06-01 00:00:00 | 女   |             1 |
+-----+--------+---------------------+------+---------------+
8 rows in set (0.00 sec)

上述查询的count(sc.SId)不能为count(s.SId)，如果写成这个，也会查出与理想不符合，因为不管SC成绩表中有没有数据，count(s.SId)至少为1，而实际情况可以为0。最后修改如下：

mysql> select s1.*,count(sc.SId) from (select s.* from Student s where s.SId not in(select sc.SId from SC sc group by sc.SId having count(sc.SId)=(select count(c.CId) from Course c))) s1 left join SC sc on s1.SId=sc.SId group by s1.SId;
+-----+--------+---------------------+------+---------------+
| SId | Sname  | Sage                | Ssex | count(sc.SId) |
+-----+--------+---------------------+------+---------------+
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |             2 |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |             2 |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |             2 |
| 09  | 张三   | 2017-12-20 00:00:00 | 女   |             0 |
| 10  | 李四   | 2017-12-25 00:00:00 | 女   |             0 |
| 11  | 李四   | 2012-06-06 00:00:00 | 女   |             0 |
| 12  | 赵六   | 2013-06-13 00:00:00 | 女   |             0 |
| 13  | 孙七   | 2014-06-01 00:00:00 | 女   |             0 |
+-----+--------+---------------------+------+---------------+
8 rows in set (0.00 sec)

综上：我们可以得到这个结论：当直接连接查询的时候，对于select的结果不需要考虑来源于哪个表，但是当有左或右连接时，需要慎重考虑来自于哪个表！

查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

这个看上去有点绕，实际上很简单，这样子思考：

理解“所学相同”，就是课程Id相同。因为只让查询出至少一门课与学号"01"的同学所学相同，而不是查询出与学号"01"的同学所学完全相同，所以可以将查询出"01"的所有课程编号作为子查询，然后又由于是至少一门课程，这表示，就是普通的连接，而不是左或右连接，因为当学生表与课程表进行连接后，有可能一门课都没。

（1）首先查询出学号为"01"的同学所学课程编号

（2）连接查询

（3）连接查询+子查询(1)

方法一：

将两边连接使用in来查询：

mysql> select * from Student s where s.SId in (select sc.SId from SC sc where sc.CId in (select sc.CId from SC sc where sc.SId='01'));
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
7 rows in set (0.00 sec)

方法二：

将量表查询使用join查询，一定要加distinct，如果不加，就会多出很多条重复数据。

mysql> select distinct s.* from Student s join SC sc on s.SId=sc.SId where sc.CId in (select sc.CId from SC sc where sc.SId='01');
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
7 rows in set (0.00 sec)

查询与学号为" 01 "的同学所学完全相同的其他同学的信息

该查询有两个关键点：

第一：完全相同

第二：其他同学

基于这两个关键点，我们得出查询语句需要考虑下面两种条件：

第一：查询所学课程是否全部存在于学号01的学生的所学课程中

上述简化为：查询不等于学号01且课程id均包含在01所学课程中的学生id。

SELECT DISTINCT sc.SId
FROM SC sc
WHERE sc.SId <> '01'
    AND sc.CId IN (
        SELECT sc.CId
        FROM SC sc
        WHERE sc.SId = '01'
    );

第二：查询所学课程数目与学号01的学生所学课程相等。

上述简化为：查询

SELECT DISTINCT sc.SId
FROM SC sc
WHERE sc.SId <> '01'
    AND sc.CId IN (
        SELECT sc.CId
        FROM SC sc
        WHERE sc.SId = '01'
    )
GROUP BY sc.SId
HAVING COUNT(1) = (
    SELECT COUNT(1)
    FROM SC sc
    WHERE sc.SId = '01'
)

最后，得出：查询与学号为" 01 "的同学所学完全相同的其他同学的信息

SELECT s.*
FROM Student s
WHERE s.SId IN (
    SELECT sc.SId
    FROM SC sc
    WHERE sc.SId <> '01'
        AND sc.CId IN (
            SELECT sc.CId
            FROM SC sc
            WHERE sc.SId = '01'
        )
    GROUP BY sc.SId
    HAVING COUNT(1) = (
        SELECT COUNT(1)
        FROM SC sc
        WHERE sc.SId = '01'
    )
);

查询结果：

+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
+-----+--------+---------------------+------+
3 rows in set (0.00 sec)

同理，也可以用not in 来表示：

SELECT s.*
FROM Student s
WHERE s.SId IN (
    SELECT sc.SId
    FROM SC sc
    WHERE sc.SId NOT IN (
        SELECT sc.SId
        FROM SC sc
        WHERE sc.CId NOT IN (
            SELECT sc.CId
            FROM SC sc
            WHERE sc.SId = '01'
        )
    )
    GROUP BY sc.SId
    HAVING COUNT(1) = (
        SELECT COUNT(1)
        FROM SC sc
        WHERE sc.SId = '01'
    )
    AND sc.SId <> '01'
);

查询没学过"张三"老师讲授的任一门课程的学生姓名

考察多层嵌套

SELECT s.Sname
FROM Student s
WHERE s.SId NOT IN (
    SELECT sc.SId
    FROM SC sc
    WHERE sc.CId IN (
        SELECT c.CId
        FROM Teacher t, Course c
        WHERE t.Tname = '张三'
            AND t.TId = c.TId
    )
);

查询结果：

+--------+
| Sname  |
+--------+
| 吴兰   |
| 张三   |
| 李四   |
| 李四   |
| 赵六   |
| 孙七   |
+--------+
6 rows in set (0.00 sec)

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

mysql> select s.SId,s.Sname,avg(sc.score) from Student s join SC sc on sc.SId=s.SId and sc.score<60 group by sc.SId having count(1)>1;
+-----+--------+---------------+
| SId | Sname  | avg(sc.score) |
+-----+--------+---------------+
| 04  | 李云   |      33.33333 |
| 06  | 吴兰   |      32.50000 |
+-----+--------+---------------+
2 rows in set (0.00 sec)

可以使用join中的on添加多个条件，也可以使用一个where。（注意如果是left或right join的话，就要注意了，这两种是不等价的！）

mysql> select s.SId,s.Sname,avg(sc.score) from Student s join SC sc on sc.SId=s.SId where sc.score<60 group by sc.SId having count(*)>1;
+-----+--------+---------------+
| SId | Sname  | avg(sc.score) |
+-----+--------+---------------+
| 04  | 李云   |      33.33333 |
| 06  | 吴兰   |      32.50000 |
+-----+--------+---------------+
2 rows in set (0.00 sec)

检索" 01 "课程分数小于 60，按分数降序排列的学生信息

mysql> select s.* from Student s,SC sc where s.SId=sc.SId and sc.score<60 and sc.CId='01' order by sc.score desc;
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
2 rows in set (0.01 sec)

当加入distinct关键字就会报错：

mysql> select distinct s.* from Student s,SC sc where s.SId=sc.SId and sc.score<60 and sc.CId='01' order by sc.score desc;
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'pratice.sc.score' which is not in SELECT list; this is incompatible with DISTINCT

此时解决方案：把order by的字段添加到select语句中即可！

mysql> select distinct s.*,sc.score from Student s,SC sc where s.SId=sc.SId and sc.score<60 and sc.CId='01' order by sc.score desc;
+-----+--------+---------------------+------+-------+
| SId | Sname  | Sage                | Ssex | score |
+-----+--------+---------------------+------+-------+
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |  50.0 |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |  31.0 |
+-----+--------+---------------------+------+-------+
2 rows in set (0.00 sec)

distinct和order by一起用时，order by的字段必须在select中,不论在ORACLE或者MYSQL都是这样。首先，distinct的执行顺序高于order by。第二，distinct执行时会对查询的记录进行去重，产生一张虚拟的临时表；第三，order by执行时对查询的虚拟临时表进行排序，产生新的虚拟临时表。综合来看，如果order by的字段不在select中，执行sql语句时首先执行distinct，之后产生的虚拟临时表中没有order by的字段，所以再执行order by时会报错。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-06-23，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server