南昌网站seo费用,重庆建筑工程信息管理平台,jetpack wordpress,尚德建设集团网站MySQL讲义第 43 讲——select 查询之查询练习#xff08;一#xff09; 文章目录 MySQL讲义第 43 讲——select 查询之查询练习#xff08;一#xff09;1、查询【C102】课程比【C103】课程分数低的学生的信息、课程及分数2、查询平均成绩高于80分的学生的学号、姓名和平均…MySQL讲义第 43 讲——select 查询之查询练习一 文章目录 MySQL讲义第 43 讲——select 查询之查询练习一1、查询【C102】课程比【C103】课程分数低的学生的信息、课程及分数2、查询平均成绩高于80分的学生的学号、姓名和平均成绩3、查询每个学生高于平均成绩的课程显示学号姓名课程名及分数4、查询平均成绩低于 70 分的学生的学号、姓名和平均成绩包含没有选课的学生5、查询所有学生的学号、姓名、选课门数以及所选课程的总成绩6、查询【姓刘】的学生人数7、查询选修了【钟南山】老师课程的学生信息8、查询没有选修【钟南山】老师课程的学生信息9、查询选修了编号为【C102】和【C103】课程的学生信息10、查询选修了编号为【C102】而没有选修编号为【C103】课程的学生信息 以下查询使用到四张表分别是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_scoreFROM scoreWHERE c_id C102) t_c102 JOIN(SELECT s_id, score AS c103_scoreFROM scoreWHERE c_id C103) t_c103ON t_c102.s_id t_c103.s_idJOIN student sON s.s_id t_c102.s_id
WHEREt_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)
---############### 方法二使用自连接 #######################################
SELECTs.s_id,s.s_name,s_t102.score AS t102_score,s_t102.score AS t103_score
FROMstudent s JOIN score s_t102 ON s.s_id s_t102.s_id AND s_t102.c_id C102JOIN score s_t103ON s_t102.s_id s_t103.s_id AND s_t103.c_id C103
WHEREs_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
FROMstudent s JOIN score scON s.s_id sc.s_id
GROUP BYs.s_id
HAVINGavg_score 80
;
-----------------------------
| s_id | s_name | avg_score |
-----------------------------
| S2014 | 刘艳 | 87.0000 |
| S2024 | 奥巴马 | 86.2500 |
-----------------------------
2 rows in set (0.04 sec)3、查询每个学生高于平均成绩的课程显示学号姓名课程名及分数
SQL 语句如下
SELECTs.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_scoreFROM student s JOIN score sc ON s.s_id sc.s_idGROUP BY s.s_id) t_avg JOIN score scON t_avg.s_id sc.s_idJOIN student sON s.s_id sc.s_idJOIN course cON c.c_id sc.c_id
WHEREsc.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 语句如下
SELECTs_id,s_name,该学生没有选课 AS avg_score
FROMstudent
WHEREs_id NOT IN(SELECT DISTINCT s_id FROM score)
UNION
SELECT s.s_id,s.s_name,AVG(sc.score) AS avg_score
FROMstudent s JOIN score scON s.s_id sc.s_id
GROUP BYs.s_id
HAVINGavg_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 语句如下
SELECTs_id,s_name,0 AS cnt_course,0 AS sum_score
FROMstudent
WHEREs_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
FROMstudent s JOIN score scON s.s_id sc.s_id
GROUP BYs.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 语句如下
SELECTcount(*) AS cnt_student
FROMstudent
WHEREs_name LIKE 刘%;
-------------
| cnt_student |
-------------
| 4 |
-------------
1 row in set (0.01 sec)7、查询选修了【钟南山】老师课程的学生信息
SQL 语句如下
SELECTs_id,s_name,birth,phone,addr
FROMstudent
WHEREs_id IN(SELECT s_idFROM scoreWHERE c_id IN (SELECT c_idFROM courseWHERE t_id (SELECT t_id FROM teacherWHERE 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 语句如下
SELECTs_id,s_name,birth,phone,addr
FROMstudent
WHEREs_id NOT IN --和上一题相比把这个地方修改为 NOT IN其他不变(SELECT s_idFROM scoreWHERE c_id IN (SELECT c_idFROM courseWHERE t_id (SELECT t_id FROM teacherWHERE 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 语句如下
SELECTs_id,s_name,birth,phone,addr
FROMstudent
WHEREs_id in(SELECT s_idFROM scoreWHERE c_id C102) ANDs_id in(SELECT s_idFROM scoreWHERE 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 语句如下
SELECTs_id,s_name,birth,phone,addr
FROMstudent
WHEREs_id IN(SELECT s_idFROM scoreWHERE c_id C102) ANDs_id NOT IN(SELECT s_idFROM scoreWHERE 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)
