MySQL讲义第 43 讲——select 查询之查询练习（一）
以下查询使用到四张表，分别是：student、teacher、course 和 score，表结构如下：
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','C107',55),('S2012','C108',86),
('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','C107',88),('S2015','C108',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);
基于以上数据，完成如下查询：
1、查询【C102】课程比【C103】课程分数低的学生的信息、课程及分数
SQL 语句如下：
---############### 方法一：使用子查询 #######################################
SELECT
t_c102.s_id,
s.s_name,
t_c102.c102_score,
t_c103.c103_score
FROM
(SELECT s_id, score AS c102_score
FROM score
WHERE c_id = 'C102'
) t_c102 JOIN
(SELECT s_id, score AS c103_score
FROM score
WHERE c_id = 'C103'
) t_c103
ON t_c102.s_id = t_c103.s_id
JOIN student s
ON s.s_id = t_c102.s_id
WHERE
t_c102.c102_score < t_c103.c103_score
;
+-------+-----------+------------+------------+
| s_id | s_name | c102_score | c103_score |
+-------+-----------+------------+------------+
| S2021 | 董雯花 | 72 | 90 |
| S2022 | 周华建 | 88 | 93 |
| S2023 | 特朗普 | 68 | 86 |
| S2024 | 奥巴马 | 87 | 97 |
+-------+-----------+------------+------------+
4 rows in set (0.00 sec)
---############### 方法二：使用自连接 #######################################
SELECT
s.s_id,
s.s_name,
s_t102.score AS t102_score,
s_t102.score AS t103_score
FROM
student s JOIN score s_t102
ON s.s_id = s_t102.s_id AND s_t102.c_id = 'C102'
JOIN score s_t103
ON s_t102.s_id = s_t103.s_id AND s_t103.c_id = 'C103'
WHERE
s_t102.score < s_t103.score
;
+-------+-----------+------------+------------+
| s_id | s_name | t102_score | t103_score |
+-------+-----------+------------+------------+
| S2021 | 董雯花 | 72 | 72 |
| S2022 | 周华建 | 88 | 88 |
| S2023 | 特朗普 | 68 | 68 |
| S2024 | 奥巴马 | 87 | 87 |
+-------+-----------+------------+------------+
4 rows in set (0.00 sec)
2、查询平均成绩高于80分的学生的学号、姓名和平均成绩
SQL 语句如下：
SELECT
s.s_id,
s.s_name,
AVG(sc.score) AS avg_score
FROM
student s JOIN score sc
ON s.s_id = sc.s_id
GROUP BY
s.s_id
HAVING
avg_score > 80
;
+-------+-----------+-----------+
| s_id | s_name | avg_score |
+-------+-----------+-----------+
| S2014 | 刘艳 | 87.0000 |
| S2024 | 奥巴马 | 86.2500 |
+-------+-----------+-----------+
2 rows in set (0.04 sec)
3、查询每个学生高于平均成绩的课程，显示学号，姓名，课程名及分数
SQL 语句如下：
SELECT
s.s_id,
s.s_name,
c.c_id,
c.c_name,
sc.score,
t_avg.avg_score
FROM
(SELECT s.s_id, AVG(sc.score) AS avg_score
FROM student s JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
) t_avg JOIN score sc
ON t_avg.s_id = sc.s_id
JOIN student s
ON s.s_id = sc.s_id
JOIN course c
ON c.c_id = sc.c_id
WHERE
sc.score > t_avg.avg_score
;
+-------+-----------+------+-----------------+-------+-----------+
| s_id | s_name | c_id | c_name | score | avg_score |
+-------+-----------+------+-----------------+-------+-----------+
| S2011 | 张晓刚 | C102 | 高等数学 | 84 | 79.5000 |
| S2011 | 张晓刚 | C105 | 传染病学 | 90 | 79.5000 |
| S2012 | 刘小青 | C101 | 古代文学 | 67 | 65.0000 |
| S2012 | 刘小青 | C108 | 教育学 | 86 | 65.0000 |
| S2013 | 曹梦德 | C102 | 高等数学 | 97 | 74.7500 |
| S2014 | 刘艳 | C102 | 高等数学 | 90 | 87.0000 |
| S2014 | 刘艳 | C105 | 传染病学 | 96 | 87.0000 |
| S2015 | 刘岩 | C107 | 诗歌欣赏 | 88 | 73.0000 |
| S2016 | 刘若非 | C102 | 高等数学 | 69 | 68.0000 |
| S2016 | 刘若非 | C107 | 诗歌欣赏 | 82 | 68.0000 |
| S2021 | 董雯花 | C103 | 线性代数 | 90 | 77.2500 |
| S2021 | 董雯花 | C104 | 临床医学 | 90 | 77.2500 |
| S2022 | 周华建 | C102 | 高等数学 | 88 | 72.5000 |
| S2022 | 周华建 | C103 | 线性代数 | 93 | 72.5000 |
| S2023 | 特朗普 | C103 | 线性代数 | 86 | 75.2500 |
| S2023 | 特朗普 | C110 | 经济法 | 91 | 75.2500 |
| S2024 | 奥巴马 | C102 | 高等数学 | 87 | 86.2500 |
| S2024 | 奥巴马 | C103 | 线性代数 | 97 | 86.2500 |
| S2025 | 周健华 | C106 | 大学物理 | 87 | 73.0000 |
| S2025 | 周健华 | C109 | 刑事诉讼法 | 82 | 73.0000 |
| S2026 | 张学有 | C106 | 大学物理 | 90 | 67.5000 |
| S2026 | 张学有 | C109 | 刑事诉讼法 | 73 | 67.5000 |
+-------+-----------+------+-----------------+-------+-----------+
22 rows in set (0.00 sec)
4、查询平均成绩低于 70 分的学生的学号、姓名和平均成绩（包含没有选课的学生）
SQL 语句如下：
SELECT
s_id,
s_name,
'该学生没有选课' AS avg_score
FROM
student
WHERE
s_id NOT IN
(SELECT DISTINCT s_id FROM score)
UNION
SELECT
s.s_id,
s.s_name,
AVG(sc.score) AS avg_score
FROM
student s JOIN score sc
ON s.s_id = sc.s_id
GROUP BY
s.s_id
HAVING
avg_score > 80
;
+-------+-----------+-----------------------+
| s_id | s_name | avg_score |
+-------+-----------+-----------------------+
| S2031 | 李明博 | 该学生没有选课 |
| S2032 | 达芬奇 | 该学生没有选课 |
| S2014 | 刘艳 | 87.000000000 |
| S2024 | 奥巴马 | 86.250000000 |
+-------+-----------+-----------------------+
4 rows in set (0.00 sec)
5、查询所有学生的学号、姓名、选课门数以及所选课程的总成绩
SQL 语句如下：
SELECT
s_id,
s_name,
0 AS cnt_course,
0 AS sum_score
FROM
student
WHERE
s_id NOT IN
(SELECT DISTINCT s_id FROM score)
UNION
SELECT
s.s_id,
s.s_name,
COUNT(*) AS cnt_course,
SUM(sc.score) AS sum_score
FROM
student s JOIN score sc
ON s.s_id = sc.s_id
GROUP BY
s.s_id
;
+-------+-----------+------------+-----------+
| s_id | s_name | cnt_course | sum_score |
+-------+-----------+------------+-----------+
| S2031 | 李明博 | 0 | 0 |
| S2032 | 达芬奇 | 0 | 0 |
| S2011 | 张晓刚 | 4 | 318 |
| S2012 | 刘小青 | 4 | 260 |
| S2013 | 曹梦德 | 4 | 299 |
| S2014 | 刘艳 | 4 | 348 |
| S2015 | 刘岩 | 4 | 292 |
| S2016 | 刘若非 | 4 | 272 |
| S2021 | 董雯花 | 4 | 309 |
| S2022 | 周华建 | 4 | 290 |
| S2023 | 特朗普 | 4 | 301 |
| S2024 | 奥巴马 | 4 | 345 |
| S2025 | 周健华 | 4 | 292 |
| S2026 | 张学有 | 4 | 270 |
+-------+-----------+------------+-----------+
14 rows in set (0.01 sec)
6、查询【姓刘】的学生人数
SQL 语句如下：
SELECT
count(*) AS cnt_student
FROM
student
WHERE
s_name LIKE '刘%';
+-------------+
| cnt_student |
+-------------+
| 4 |
+-------------+
1 row in set (0.01 sec)
7、查询选修了【钟南山】老师课程的学生信息
SQL 语句如下：
SELECT
s_id,
s_name,
birth,
phone,addr
FROM
student
WHERE
s_id IN
(SELECT s_id
FROM score
WHERE c_id IN
(SELECT c_id
FROM course
WHERE t_id =
(SELECT t_id
FROM teacher
WHERE t_name = '钟南山')
)
)
;
+-------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+-----------+---------------------+-------------+-----------+
| S2011 | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 |
| S2013 | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 |
| S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 |
| S2021 | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 |
| S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 |
| S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 |
+-------+-----------+---------------------+-------------+-----------+
6 rows in set (0.01 sec)
8、查询没有选修【钟南山】老师课程的学生信息
SQL 语句如下：
SELECT
s_id,
s_name,
birth,
phone,addr
FROM
student
WHERE
s_id NOT IN --和上一题相比，把这个地方修改为 NOT IN，其他不变
(SELECT s_id
FROM score
WHERE c_id IN
(SELECT c_id
FROM course
WHERE t_id =
(SELECT t_id
FROM teacher
WHERE t_name = '钟南山')
)
)
;
+-------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+-----------+---------------------+-------------+-----------+
| S2012 | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 |
| S2015 | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 |
| S2016 | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 |
| S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 |
| S2023 | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 |
| S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 |
| S2031 | 李明博 | 1999-10-26 00:00:00 | 13643732222 | 郑州市 |
| S2032 | 达芬奇 | 1999-12-31 00:00:00 | 13043731234 | 郑州市 |
+-------+-----------+---------------------+-------------+-----------+
8 rows in set (0.00 sec)
9、查询选修了编号为【C102】和【C103】课程的学生信息
SQL 语句如下：
SELECT
s_id,
s_name,
birth,
phone,addr
FROM
student
WHERE
s_id in
(SELECT s_id
FROM score
WHERE c_id = 'C102'
) AND
s_id in
(SELECT s_id
FROM score
WHERE c_id = 'C103')
;
+-------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+-----------+---------------------+-------------+-----------+
| S2013 | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 |
| S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 |
| S2021 | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 |
| S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 |
| S2023 | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 |
| S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 |
+-------+-----------+---------------------+-------------+-----------+
6 rows in set (0.01 sec)
10、查询选修了编号为【C102】而没有选修编号为【C103】课程的学生信息
SQL 语句如下：
SELECT
s_id,
s_name,
birth,
phone,addr
FROM
student
WHERE
s_id IN
(SELECT s_id
FROM score
WHERE c_id = 'C102'
) AND
s_id NOT IN
(SELECT s_id
FROM score
WHERE c_id = 'C103')
;
+-------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+-----------+---------------------+-------------+-----------+
| S2011 | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 |
| S2012 | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 |
| S2015 | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 |
| S2016 | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 |
| S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 |
| S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 |
+-------+-----------+---------------------+-------------+-----------+
6 rows in set (0.00 sec)
扫一扫
1536
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
