MySQL讲义第 45 讲——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);

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

22、查询有两门及以上课程不及格的学生的信息

SQL 语句如下：

SELECT sc.s_id,s.s_name,s.phone,s.addr
FROMscore sc JOIN student sON sc.s_id = s.s_id
WHEREscore < 60
GROUP BYsc.s_id
HAVINGcount(*) >= 2
;
+-------+-----------+-------------+-----------+
| s_id  | s_name    | phone       | addr      |
+-------+-----------+-------------+-----------+
| S2012 | 刘小青    | 13603732255 | 新乡市    |
| S2026 | 张学有    | 13743735566 | 郑州市    |
+-------+-----------+-------------+-----------+
2 rows in set (0.01 sec)

23、查询选修了编号为【C105】的课程的学生信息，按分数降序排列

SQL 语句如下：

SELECTs.s_id,s.s_name,s.phone,s.addr,sc.score 
FROMstudent s JOIN score scON s.s_id = sc.s_id
WHEREsc.c_id = 'C105'
ORDER BYsc.score DESC
;
+-------+-----------+-------------+-----------+-------+
| s_id  | s_name    | phone       | addr      | score |
+-------+-----------+-------------+-----------+-------+
| S2014 | 刘艳      | 13623735335 | 郑州市    |    96 |
| S2011 | 张晓刚    | 13163735775 | 信阳市    |    90 |
| S2012 | 刘小青    | 13603732255 | 新乡市    |    87 |
| S2013 | 曹梦德    | 13853735522 | 郑州市    |    68 |
| S2015 | 刘岩      | 13813735225 | 信阳市    |    66 |
| S2025 | 周健华    | 13788736655 | 开封市    |    62 |
| S2021 | 董雯花    | 13533735564 | 开封市    |    57 |
| S2026 | 张学有    | 13743735566 | 郑州市    |    48 |
+-------+-----------+-------------+-----------+-------+
8 rows in set (0.00 sec)

24、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SQL 语句如下：

SELECTs.s_id,s.s_name,c.c_id,c.c_name,avg_sc.avg_score
FROMscore sc JOIN (SELECT s_id, AVG(score) AS avg_scoreFROM scoreGROUP BY s_id) avg_scON sc.s_id = avg_sc.s_idJOIN student sON s.s_id = sc.s_id JOIN course cON c.c_id = sc.c_id
ORDER BY avg_sc.avg_score DESC
;
+-------+-----------+------+-----------------+-----------+
| s_id  | s_name    | c_id | c_name          | avg_score |
+-------+-----------+------+-----------------+-----------+
| S2014 | 刘艳      | C104 | 临床医学        |   87.0000 |
| S2014 | 刘艳      | C103 | 线性代数        |   87.0000 |
| S2014 | 刘艳      | C105 | 传染病学        |   87.0000 |
| S2014 | 刘艳      | C102 | 高等数学        |   87.0000 |
| S2024 | 奥巴马    | C103 | 线性代数        |   86.2500 |
| S2024 | 奥巴马    | C110 | 经济法          |   86.2500 |
| S2024 | 奥巴马    | C102 | 高等数学        |   86.2500 |
| S2024 | 奥巴马    | C109 | 刑事诉讼法      |   86.2500 |
| S2011 | 张晓刚    | C105 | 传染病学        |   79.5000 |
| S2011 | 张晓刚    | C109 | 刑事诉讼法      |   79.5000 |
| S2011 | 张晓刚    | C102 | 高等数学        |   79.5000 |
| S2011 | 张晓刚    | C106 | 大学物理        |   79.5000 |
| S2021 | 董雯花    | C105 | 传染病学        |   77.2500 |
| S2021 | 董雯花    | C102 | 高等数学        |   77.2500 |
| S2021 | 董雯花    | C104 | 临床医学        |   77.2500 |
| S2021 | 董雯花    | C103 | 线性代数        |   77.2500 |
| S2023 | 特朗普    | C110 | 经济法          |   75.2500 |
| S2023 | 特朗普    | C102 | 高等数学        |   75.2500 |
| S2023 | 特朗普    | C109 | 刑事诉讼法      |   75.2500 |
| S2023 | 特朗普    | C103 | 线性代数        |   75.2500 |
| S2013 | 曹梦德    | C103 | 线性代数        |   74.7500 |
| S2013 | 曹梦德    | C105 | 传染病学        |   74.7500 |
| S2013 | 曹梦德    | C102 | 高等数学        |   74.7500 |
| S2013 | 曹梦德    | C104 | 临床医学        |   74.7500 |
| S2015 | 刘岩      | C102 | 高等数学        |   73.8000 |
| S2015 | 刘岩      | C110 | 经济法          |   73.8000 |
| S2015 | 刘岩      | C107 | 诗歌欣赏        |   73.8000 |
| S2015 | 刘岩      | C104 | 临床医学        |   73.8000 |
| S2015 | 刘岩      | C101 | 古代文学        |   73.8000 |
| S2015 | 刘岩      | C109 | 刑事诉讼法      |   73.8000 |
| S2015 | 刘岩      | C106 | 大学物理        |   73.8000 |
| S2015 | 刘岩      | C103 | 线性代数        |   73.8000 |
| S2015 | 刘岩      | C108 | 教育学          |   73.8000 |
| S2015 | 刘岩      | C105 | 传染病学        |   73.8000 |
| S2025 | 周健华    | C109 | 刑事诉讼法      |   73.0000 |
| S2025 | 周健华    | C102 | 高等数学        |   73.0000 |
| S2025 | 周健华    | C106 | 大学物理        |   73.0000 |
| S2025 | 周健华    | C105 | 传染病学        |   73.0000 |
| S2022 | 周华建    | C103 | 线性代数        |   72.5000 |
| S2022 | 周华建    | C110 | 经济法          |   72.5000 |
| S2022 | 周华建    | C102 | 高等数学        |   72.5000 |
| S2022 | 周华建    | C109 | 刑事诉讼法      |   72.5000 |
| S2012 | 刘小青    | C104 | 临床医学        |   71.3000 |
| S2012 | 刘小青    | C101 | 古代文学        |   71.3000 |
| S2012 | 刘小青    | C109 | 刑事诉讼法      |   71.3000 |
| S2012 | 刘小青    | C106 | 大学物理        |   71.3000 |
| S2012 | 刘小青    | C103 | 线性代数        |   71.3000 |
| S2012 | 刘小青    | C108 | 教育学          |   71.3000 |
| S2012 | 刘小青    | C105 | 传染病学        |   71.3000 |
| S2012 | 刘小青    | C102 | 高等数学        |   71.3000 |
| S2012 | 刘小青    | C110 | 经济法          |   71.3000 |
| S2012 | 刘小青    | C107 | 诗歌欣赏        |   71.3000 |
| S2016 | 刘若非    | C102 | 高等数学        |   68.0000 |
| S2016 | 刘若非    | C108 | 教育学          |   68.0000 |
| S2016 | 刘若非    | C101 | 古代文学        |   68.0000 |
| S2016 | 刘若非    | C107 | 诗歌欣赏        |   68.0000 |
| S2026 | 张学有    | C105 | 传染病学        |   67.5000 |
| S2026 | 张学有    | C109 | 刑事诉讼法      |   67.5000 |
| S2026 | 张学有    | C102 | 高等数学        |   67.5000 |
| S2026 | 张学有    | C106 | 大学物理        |   67.5000 |
+-------+-----------+------+-----------------+-----------+
60 rows in set (0.01 sec)

25、查询各门课程的课程编号、课程名称、最高分、最低分、平均分以及优秀率（>=90 分的学生所占的百分比）、良好率（75 - 90 分的学生所占的百分比）、及格率（60 - 85 分的学生所占的百分比）与不及格率（<60 分的学生所占的百分比）。

SQL 语句如下：

SELECTc.c_id,c.c_name,MAX(score) AS max_score,MIN(score) AS min_score,AVG(score) AS avg_score,CONCAT(FORMAT(SUM(IF(score >= 90,1,0))/COUNT(*)*100,2),'%') AS yxl,CONCAT(FORMAT(SUM(IF(score >= 75 AND score < 90,1,0))/COUNT(*)*100,2),'%') AS lhl,CONCAT(FORMAT(SUM(IF(score >= 60 AND score <=75,1,0))/COUNT(*)*100,2),'%') AS jgl,CONCAT(FORMAT(SUM(IF(score < 60,1,0))/COUNT(*)*100,2),'%') AS bjgl
FROMscore sc JOIN course cON sc.c_id = c.c_id
GROUP BY c.c_id
;
+------+-----------------+-----------+-----------+-----------+--------+--------+---------+--------+
| c_id | c_name          | max_score | min_score | avg_score | yxl    | lhl    | jgl     | bjgl   |
+------+-----------------+-----------+-----------+-----------+--------+--------+---------+--------+
| C101 | 古代文学        |        69 |        65 |   67.0000 | 0.00%  | 0.00%  | 100.00% | 0.00%  |
| C102 | 高等数学        |        97 |        52 |   74.4167 | 16.67% | 25.00% | 41.67%  | 16.67% |
| C103 | 线性代数        |        97 |        55 |   82.7500 | 37.50% | 37.50% | 12.50%  | 12.50% |
| C104 | 临床医学        |        90 |        66 |   77.6000 | 20.00% | 40.00% | 40.00%  | 0.00%  |
| C105 | 传染病学        |        96 |        48 |   71.7500 | 25.00% | 12.50% | 37.50%  | 25.00% |
| C106 | 大学物理        |        90 |        64 |   81.6000 | 20.00% | 60.00% | 20.00%  | 0.00%  |
| C107 | 诗歌欣赏        |        82 |        62 |   71.0000 | 0.00%  | 33.33% | 66.67%  | 0.00%  |
| C108 | 教育学          |        73 |        56 |   65.0000 | 0.00%  | 0.00%  | 66.67%  | 33.33% |
| C109 | 刑事诉讼法      |        88 |        47 |   71.1250 | 0.00%  | 50.00% | 25.00%  | 25.00% |
| C110 | 经济法          |        91 |        62 |   78.4000 | 20.00% | 40.00% | 40.00%  | 0.00%  |
+------+-----------------+-----------+-----------+-----------+--------+--------+---------+--------+
10 rows in set (0.00 sec)

26、查询选修【高等数学】课程的学生信息，并根据成绩由高到低进行排序，显示排名

SQL 语句如下：

SET @i = 0;
SELECTs.s_id,s.s_name,sc.score,@i:=@i+1 AS rank
FROMstudent s JOIN score scON s.s_id = sc.s_idJOIN course cON c.c_id = sc.c_id
WHEREc.c_name = '高等数学'
;
+-------+-----------+-------+------+
| s_id  | s_name    | score | rank |
+-------+-----------+-------+------+
| S2011 | 张晓刚    |    84 |    1 |
| S2012 | 刘小青    |    52 |    2 |
| S2013 | 曹梦德    |    97 |    3 |
| S2014 | 刘艳      |    90 |    4 |
| S2015 | 刘岩      |    66 |    5 |
| S2016 | 刘若非    |    69 |    6 |
| S2021 | 董雯花    |    72 |    7 |
| S2022 | 周华建    |    88 |    8 |
| S2023 | 特朗普    |    68 |    9 |
| S2024 | 奥巴马    |    87 |   10 |
| S2025 | 周健华    |    61 |   11 |
| S2026 | 张学有    |    59 |   12 |
+-------+-----------+-------+------+
12 rows in set (0.01 sec)

27、查询每个学生的总成绩并进行排名

SQL 语句如下：

SET @rank = 0;
SELECTs.s_id,s.s_name,sc.sum_score,@rank := @rank + 1 AS rank
FROM(SELECT s_id, SUM(score) AS sum_scoreFROM scoreGROUP BY s_idORDER BY sum_score DESC) sc JOIN student sON sc.s_id = s.s_id
;
+-------+-----------+-----------+------+
| s_id  | s_name    | sum_score | rank |
+-------+-----------+-----------+------+
| S2011 | 张晓刚    |       318 |    1 |
| S2012 | 刘小青    |       713 |    2 |
| S2013 | 曹梦德    |       299 |    3 |
| S2014 | 刘艳      |       348 |    4 |
| S2015 | 刘岩      |       738 |    5 |
| S2016 | 刘若非    |       272 |    6 |
| S2021 | 董雯花    |       309 |    7 |
| S2022 | 周华建    |       290 |    8 |
| S2023 | 特朗普    |       301 |    9 |
| S2024 | 奥巴马    |       345 |   10 |
| S2025 | 周健华    |       292 |   11 |
| S2026 | 张学有    |       270 |   12 |
+-------+-----------+-----------+------+
12 rows in set (0.03 sec)