MySQL讲义第 46 讲——select 查询之查询练习（四）

以下查询使用到四张表，分别是：student、teacher、course 和 electives，表结构如下：

CREATE TABLE student(s_id char(5) primary key,s_name char(20),birth datetime,phone char(20),addr varchar(100)
);INSERT INTO student
VALUES('S2011','张晓刚','1999-12-3','13163735775','信阳市'),
('S2012','刘小青','1999-10-11','13603732255','新乡市'),
('S2013','曹梦德','1998-2-13','13853735522','郑州市'),
('S2014','刘艳','1998-6-24','13623735335','郑州市'),
('S2015','刘岩','1999-7-6','13813735225','信阳市'),
('S2016','刘若非','2000-8-31','13683735533','开封市'),
('S2021','董雯花','2000-7-30','13533735564','开封市'),
('S2022','周华建','1999-5-25','13243735578','郑州市'),
('S2023','特朗普','1999-6-21','13343735588','新乡市'),
('S2024','奥巴马','2000-10-17','13843735885','信阳市'),
('S2025','周健华','2000-8-22','13788736655','开封市'),
('S2026','张学有','1998-7-6','13743735566','郑州市'),
('S2031','李明博','1999-10-26','13643732222','郑州市'),
('S2032','达芬奇','1999-12-31','13043731234','郑州市');CREATE TABLE teacher(t_id char(5) primary key,t_name char(20),job_title char(20),phone char(20)
);INSERT INTO teacher
VALUES('T8001','欧阳修','教授','13703735666'),
('T8002','华罗庚','教授','13703735888'),
('T8003','钟南山','教授','13703735675'),
('T8004','钱学森','教授','13703735638'),
('T8005','李白','副教授','13703735828'),
('T8006','孔子','教授','13703735457'),
('T8007','王安石','副教授','13703735369');CREATE TABLE course(c_id char(4) primary key,c_name char(20),t_id char(5)
);INSERT INTO course
VALUES('C101','古代文学','T8001'),
('C102','高等数学','T8002'),
('C103','线性代数','T8002'),
('C104','临床医学','T8003'),
('C105','传染病学','T8003'),
('C106','大学物理','T8004'),
('C107','诗歌欣赏','T8005'),
('C108','教育学','T8006'),
('C109','刑事诉讼法','T8007'),
('C110','经济法','T8007');CREATE TABLE score(s_id char(5),c_id char(4),score int,primary key(s_id, c_id)
);INSERT INTO score
VALUES('S2011','C102',84),('S2011','C105',90),('S2011','C106',79),('S2011','C109',65),
('S2012','C101',67),('S2012','C102',52),('S2012','C103',55),('S2012','C104',86),
('S2012','C105',87),('S2012','C106',64),('S2012','C107',62),
('S2012','C108',73),('S2012','C109',78),('S2012','C110',89),
('S2013','C102',97),('S2013','C103',68),('S2013','C104',66),('S2013','C105',68),
('S2014','C102',90),('S2014','C103',85),('S2014','C104',77),('S2014','C105',96),
('S2015','C101',69),('S2015','C102',66),('S2015','C103',88),('S2015','C104',69),
('S2015','C105',66),('S2015','C106',88),('S2015','C107',69),
('S2015','C108',66),('S2015','C109',88),('S2015','C110',69),
('S2016','C101',65),('S2016','C102',69),('S2016','C107',82),('S2016','C108',56),
('S2021','C102',72),('S2021','C103',90),('S2021','C104',90),('S2021','C105',57),
('S2022','C102',88),('S2022','C103',93),('S2022','C109',47),('S2022','C110',62),
('S2023','C102',68),('S2023','C103',86),('S2023','C109',56),('S2023','C110',91),
('S2024','C102',87),('S2024','C103',97),('S2024','C109',80),('S2024','C110',81),
('S2025','C102',61),('S2025','C105',62),('S2025','C106',87),('S2025','C109',82),
('S2026','C102',59),('S2026','C105',48),('S2026','C106',90),('S2026','C109',73);

基于以上数据，完成如下查询：

28、查询每门课程的平均分并按平均分降序排列，显示课程编号、课程名和平均分

SQL 语句如下：

SELECT c.c_id,c.c_name,AVG(score) AS avg_score
FROMscore sc JOIN course cON sc.c_id = c.c_id
GROUP BY c.c_id
ORDER BYavg_score DESC
;
+------+-----------------+-----------+
| c_id | c_name          | avg_score |
+------+-----------------+-----------+
| C103 | 线性代数        |   82.7500 |
| C106 | 大学物理        |   81.6000 |
| C110 | 经济法          |   78.4000 |
| C104 | 临床医学        |   77.6000 |
| C102 | 高等数学        |   74.4167 |
| C105 | 传染病学        |   71.7500 |
| C109 | 刑事诉讼法      |   71.1250 |
| C107 | 诗歌欣赏        |   71.0000 |
| C101 | 古代文学        |   67.0000 |
| C108 | 教育学          |   65.0000 |
+------+-----------------+-----------+
10 rows in set (0.01 sec)

29、查询每门课程的最高分、课程信息及选修该门课程的学生信息

SQL 语句如下：

SELECTc.c_id,c.c_name,s.s_id,s.s_name,sc.score
FROM(SELECT c_id, MAX(score) AS max_scoreFROM scoreGROUP BY c_id) sc1 JOIN score scON sc1.c_id = sc.c_id AND sc1.max_score = sc.scoreJOIN student sON s.s_id = sc.s_idJOIN course cON c.c_id = sc.c_id
;
+------+-----------------+-------+-----------+-------+
| c_id | c_name          | s_id  | s_name    | score |
+------+-----------------+-------+-----------+-------+
| C108 | 教育学          | S2012 | 刘小青    |    73 |
| C102 | 高等数学        | S2013 | 曹梦德    |    97 |
| C105 | 传染病学        | S2014 | 刘艳      |    96 |
| C101 | 古代文学        | S2015 | 刘岩      |    69 |
| C109 | 刑事诉讼法      | S2015 | 刘岩      |    88 |
| C107 | 诗歌欣赏        | S2016 | 刘若非    |    82 |
| C104 | 临床医学        | S2021 | 董雯花    |    90 |
| C110 | 经济法          | S2023 | 特朗普    |    91 |
| C103 | 线性代数        | S2024 | 奥巴马    |    97 |
| C106 | 大学物理        | S2026 | 张学有    |    90 |
+------+-----------------+-------+-----------+-------+
10 rows in set (0.01 sec)

30、查询【高等数学】课程的成绩排名第 2 名和第 3 名的学生信息及分数

SQL 语句如下：

SELECTs.s_id,s.s_name,sc.score
FROMstudent s JOIN score scON s.s_id = sc.s_id
WHEREsc.c_id = (SELECT c_idFROM courseWHERE c_name = '高等数学')
ORDER BY sc.score DESC
LIMIT 1, 2
;
+-------+-----------+-------+
| s_id  | s_name    | score |
+-------+-----------+-------+
| S2014 | 刘艳      |    90 |
| S2022 | 周华建    |    88 |
+-------+-----------+-------+
2 rows in set (0.00 sec)

31、统计各门课程各个分数段的人数及所占总人数的百分比：课程编号、课程名称、100-85 分之间的人数及百分比、85-70 分之间的人数及百分比、70-60 分之间的人数及百分比、60 分以下的人数及百分比

SQL 语句如下：

SELECTc.c_id,c.c_name,SUM(IF(score >= 85 AND score <= 100, 1, 0)) AS cnt_100_85,CONCAT(FORMAT(SUM(IF(score >= 85 AND score <= 100, 1, 0))*100/count(*),2),'%') AS per1,SUM(IF(score >= 70 AND score < 85, 1, 0)) AS cnt_85_70,CONCAT(FORMAT(SUM(IF(score >= 70 AND score < 85, 1, 0))*100/count(*),2),'%') AS per2,SUM(IF(score >= 60 AND score < 70, 1, 0)) AS cnt_60_70,CONCAT(FORMAT(SUM(IF(score >= 60 AND score <= 70, 1, 0))*100/count(*),2),'%') AS per3,SUM(IF(score >= 0 AND score < 60, 1, 0)) AS cnt_60_0,CONCAT(FORMAT(SUM(IF(score >= 0 AND score <= 60, 1, 0))*100/count(*),2),'%') AS per4
FROMscore sc JOIN course cON sc.c_id = c.c_id
GROUP BY c.c_id
;
+------+--------+-----------+--------+----------+--------+----------+---------+---------+------+
| c_id | c_name |cnt_100_85 | per1   |cnt_85_70 | per2   |cnt_60_70 | per3    |cnt_60_0 | per4 |
+------+--------+-----------+--------+----------+--------+----------+---------+---------+------+
| C101 | 古代文学  |        0 | 0.00%  |       0 | 0.00%  |       3 | 100.00% |      0 | 0.00%  |
| C102 | 高等数学  |        4 | 33.33% |       2 | 16.67% |       4 | 33.33%  |      2 | 16.67% |
| C103 | 线性代数  |        6 | 75.00% |       0 | 0.00%  |       1 | 12.50%  |      1 | 12.50% |
| C104 | 临床医学  |        2 | 40.00% |       1 | 20.00% |       2 | 40.00%  |      0 | 0.00%  |
| C105 | 传染病学  |        3 | 37.50% |       0 | 0.00%  |       3 | 37.50%  |      2 | 25.00% |
| C106 | 大学物理  |        3 | 60.00% |       1 | 20.00% |       1 | 20.00%  |      0 | 0.00%  |
| C107 | 诗歌欣赏  |        0 | 0.00%  |       1 | 33.33% |       2 | 66.67%  |      0 | 0.00%  |
| C108 | 教育学    |        0 | 0.00%  |       1 | 33.33% |       1 | 33.33%  |      1 | 33.33% |
| C109 | 刑事诉讼法|        1 | 12.50% |       4 | 50.00% |       1 | 12.50%  |      2 | 25.00% |
| C110 | 经济法    |        2 | 40.00% |       1 | 20.00% |       2 | 40.00%  |      0 | 0.00%  |
+------+-----------+----------+--------+---------+--------+---------+---------+-------+--------+
10 rows in set (0.00 sec)

32、查询每个学生的平均成绩及其排名

SQL 语句如下：

SET @rank = 0;
SELECTs.s_id,s.s_name,sc.avg_score,@rank := @rank + 1 AS rank
FROM   (SELECT s_id, AVG(score) AS avg_scoreFROM scoreGROUP BY s_idORDER BY avg_score DESC) sc JOIN student sON s.s_id = sc.s_id
;
+-------+-----------+-----------+------+
| s_id  | s_name    | avg_score | rank |
+-------+-----------+-----------+------+
| S2011 | 张晓刚    |   79.5000 |    1 |
| S2012 | 刘小青    |   71.3000 |    2 |
| S2013 | 曹梦德    |   74.7500 |    3 |
| S2014 | 刘艳      |   87.0000 |    4 |
| S2015 | 刘岩      |   73.8000 |    5 |
| S2016 | 刘若非    |   68.0000 |    6 |
| S2021 | 董雯花    |   77.2500 |    7 |
| S2022 | 周华建    |   72.5000 |    8 |
| S2023 | 特朗普    |   75.2500 |    9 |
| S2024 | 奥巴马    |   86.2500 |   10 |
| S2025 | 周健华    |   73.0000 |   11 |
| S2026 | 张学有    |   67.5000 |   12 |
+-------+-----------+-----------+------+
12 rows in set (0.00 sec)

33、查询各科成绩前三名的记录

SQL 语句如下：

--###########   解题思路如下     #############################################
--###########   第一步：自连接，让每一个分数都和同一门课的其他分数进行  ##########
--###########          连接，连接的条件为：大于等于当前分数高的记录     #########
SELECT s1.s_id,s1.c_id,s1.score,s2.score AS high_score
FROMscore s1 JOIN score s2ON s1.c_id = s2.c_id AND s1.score <= s2.score
ORDER BYs1.c_id, s1.s_id
;
+-------+------+-------+------------+
| s_id  | c_id | score | high_score |
+-------+------+-------+------------+
| S2012 | C101 |    67 |         67 |
| S2012 | C101 |    67 |         69 |
| S2015 | C101 |    69 |         69 |
| S2016 | C101 |    65 |         65 |
| S2016 | C101 |    65 |         67 |
| S2016 | C101 |    65 |         69 |
| S2011 | C102 |    84 |         90 |
| S2011 | C102 |    84 |         88 |
| S2011 | C102 |    84 |         87 |
| S2011 | C102 |    84 |         84 |
| S2011 | C102 |    84 |         97 |
| S2012 | C102 |    52 |         87 |
| S2012 | C102 |    52 |         90 |
| S2012 | C102 |    52 |         61 |
| S2012 | C102 |    52 |         66 |
| S2012 | C102 |    52 |         59 |
| S2012 | C102 |    52 |         69 |
| S2012 | C102 |    52 |         72 |
| S2012 | C102 |    52 |         84 |
| S2012 | C102 |    52 |         88 |
...........
+-------+------+-------+------------+
249 rows in set (0.00 sec)
--###########   第二步：按照 s_id, c_id, sc1.score 分组，统计记录行数    #############
--###########          实际就是统计同一门课有多少个比当前分数大的分数      #############
SELECT s1.s_id,s1.c_id,s1.score,count(*) AS cnt
FROMscore s1 JOIN score s2ON s1.c_id = s2.c_id AND s1.score <= s2.score
GROUP BYs1.s_id, s1.c_id, s1.score
ORDER BYs1.c_id, cnt 
;
+-------+------+-------+-----+
| s_id  | c_id | score | cnt |
+-------+------+-------+-----+
| S2015 | C101 |    69 |   1 |
| S2012 | C101 |    67 |   2 |
| S2016 | C101 |    65 |   3 |
| S2013 | C102 |    97 |   1 |
| S2014 | C102 |    90 |   2 |
| S2022 | C102 |    88 |   3 |
| S2024 | C102 |    87 |   4 |
| S2011 | C102 |    84 |   5 |
| S2021 | C102 |    72 |   6 |
| S2016 | C102 |    69 |   7 |
| S2023 | C102 |    68 |   8 |
| S2015 | C102 |    66 |   9 |
| S2025 | C102 |    61 |  10 |
| S2026 | C102 |    59 |  11 |
| S2012 | C102 |    52 |  12 |
| S2024 | C103 |    97 |   1 |
| S2022 | C103 |    93 |   2 |
| S2021 | C103 |    90 |   3 |
| S2015 | C103 |    88 |   4 |
| S2023 | C103 |    86 |   5 |
| S2014 | C103 |    85 |   6 |
| S2013 | C103 |    68 |   7 |
| S2012 | C103 |    55 |   8 |
| S2021 | C104 |    90 |   1 |
| S2012 | C104 |    86 |   2 |
| S2014 | C104 |    77 |   3 |
| S2015 | C104 |    69 |   4 |
| S2013 | C104 |    66 |   5 |
| S2014 | C105 |    96 |   1 |
| S2011 | C105 |    90 |   2 |
| S2012 | C105 |    87 |   3 |
| S2013 | C105 |    68 |   4 |
| S2015 | C105 |    66 |   5 |
| S2025 | C105 |    62 |   6 |
| S2021 | C105 |    57 |   7 |
| S2026 | C105 |    48 |   8 |
| S2026 | C106 |    90 |   1 |
| S2015 | C106 |    88 |   2 |
| S2025 | C106 |    87 |   3 |
| S2011 | C106 |    79 |   4 |
| S2012 | C106 |    64 |   5 |
| S2016 | C107 |    82 |   1 |
| S2015 | C107 |    69 |   2 |
| S2012 | C107 |    62 |   3 |
| S2012 | C108 |    73 |   1 |
| S2015 | C108 |    66 |   2 |
| S2016 | C108 |    56 |   3 |
| S2015 | C109 |    88 |   1 |
| S2025 | C109 |    82 |   2 |
| S2024 | C109 |    80 |   3 |
| S2012 | C109 |    78 |   4 |
| S2026 | C109 |    73 |   5 |
| S2011 | C109 |    65 |   6 |
| S2023 | C109 |    56 |   7 |
| S2022 | C109 |    47 |   8 |
| S2023 | C110 |    91 |   1 |
| S2012 | C110 |    89 |   2 |
| S2024 | C110 |    81 |   3 |
| S2015 | C110 |    69 |   4 |
| S2022 | C110 |    62 |   5 |
+-------+------+-------+-----+
60 rows in set (0.01 sec)
--###########   第三步：筛选出 cnt 小于等于 3 的记录即可    #############
SELECT s1.s_id,s1.c_id,s1.score,count(*) AS cnt
FROMscore s1 JOIN score s2ON s1.c_id = s2.c_id AND s1.score <= s2.score
GROUP BYs1.s_id, s1.c_id, s1.score
HAVING cnt <=3
ORDER BYs1.c_id, s1.score DESC
;
+-------+------+-------+-----+
| s_id  | c_id | score | cnt |
+-------+------+-------+-----+
| S2015 | C101 |    69 |   1 |
| S2012 | C101 |    67 |   2 |
| S2016 | C101 |    65 |   3 |
| S2013 | C102 |    97 |   1 |
| S2014 | C102 |    90 |   2 |
| S2022 | C102 |    88 |   3 |
| S2024 | C103 |    97 |   1 |
| S2022 | C103 |    93 |   2 |
| S2021 | C103 |    90 |   3 |
| S2021 | C104 |    90 |   1 |
| S2012 | C104 |    86 |   2 |
| S2014 | C104 |    77 |   3 |
| S2014 | C105 |    96 |   1 |
| S2011 | C105 |    90 |   2 |
| S2012 | C105 |    87 |   3 |
| S2026 | C106 |    90 |   1 |
| S2015 | C106 |    88 |   2 |
| S2025 | C106 |    87 |   3 |
| S2016 | C107 |    82 |   1 |
| S2015 | C107 |    69 |   2 |
| S2012 | C107 |    62 |   3 |
| S2012 | C108 |    73 |   1 |
| S2015 | C108 |    66 |   2 |
| S2016 | C108 |    56 |   3 |
| S2015 | C109 |    88 |   1 |
| S2025 | C109 |    82 |   2 |
| S2024 | C109 |    80 |   3 |
| S2023 | C110 |    91 |   1 |
| S2012 | C110 |    89 |   2 |
| S2024 | C110 |    81 |   3 |
+-------+------+-------+-----+
30 rows in set (0.00 sec)