MySQL实操基础全过程学习笔记（二）

四、子查询

（一）简单子查询

18.查询选修3-105课程的成绩高于‘109’号同学3-105成绩的所有同学的记录 步骤：

第一步：将109同学3-105的成绩查出来 select degree from score where sno='109'and cno='3-105'; +--------+ | degree | +--------+ | 76 | +--------+ 第二步：利用嵌套将所有高于这个成绩的同学找出来 select *from score where degree>(select degree from score where sno='109'and cno='3-105'); +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 103 | 3-245 | 86 | | 103 | 3-105 | 92 | | 105 | 3-105 | 91 | | 103 | 6-166 | 85 | | 105 | 6-166 | 79 | | 109 | 6-166 | 81 | +-----+-------+--------+ 第三步：但我们要的是3-105的，因此加一个and条件 select *from score where degree>(select degree from score where sno='109'and cno='3-105') and cno='3-105'; +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 103 | 3-105 | 92 | | 105 | 3-105 | 91 | +-----+-------+--------+

遇到问题： （1）之前数据插入重复了，导致一个人选修的课程有俩成绩，因此此处做下修订

delete from score where cno='3-105' and degree='78'; delete from score where cno='3-105' and degree='64'; delete from score where cno='3-105' and degree='88';

19.查询成绩高于学号109，课程号为‘3-105’的成绩的所有记录 这个题就很简单了，，，只要减去上一题的那个条件即可

select *from score where degree>(select degree from score where sno='109'and cno='3-105'); +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 103 | 3-245 | 86 | | 103 | 3-105 | 92 | | 105 | 3-105 | 91 | | 103 | 6-166 | 85 | | 105 | 6-166 | 79 | | 109 | 6-166 | 81 | +-----+-------+--------+

（二）year函数与带in的子查询

20.查询学号为108、101同学同年出生的所有学生的sno、sname、sbirthday

思路：

第一步：查询出101和108同学的信息 select * from student where sno='108' or sno='101'; 或是 select * from student where sno in(101,108); +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+ 第二步：查询出他们的年份，用year select year(sbirthday) from student where sno in(101,108); +-----------------+ | year(sbirthday) | +-----------------+ | 1977 | | 1975 | +-----------------+ 第三步：在学生表中找出他们同一年的人，此处同年的条件有俩，用in select * from student where year(sbirthday) in(select year(sbirthday) from student where sno in(101,108)); +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+

（三）多层嵌套子查询

21.查询张旭教师任课的学生成绩

思路：

第一步：看看张旭的相关信息 select *from teacher where tname='张旭'; +-----+-------+------+---------------------+------+------------+ | tno | tname | tsex | tbirthday | prof | depart | +-----+-------+------+---------------------+------+------------+ | 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 | +-----+-------+------+---------------------+------+------------+ 第二步：通过教师编号获取授课信息 select * from course where tno=(select tno from teacher where tname='张旭'); +-------+----------+-----+ | cno | cname | tno | +-------+----------+-----+ | 6-166 | 数字电路 | 856 | +-------+----------+-----+ 第三步：取出课程号 select cno from course where tno=(select tno from teacher where tname='张旭'); +-------+ | cno | +-------+ | 6-166 | +-------+ 第四步:根据课程号，在score表中找到成绩 select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭')); +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 103 | 6-166 | 85 | | 105 | 6-166 | 79 | | 109 | 6-166 | 81 | +-----+-------+--------+

（四）多表查询

22.查询选修某门课程的学生多于5人的教师名 思路：

第一步：鉴于没有选修超过5人，之前把多于数据删了，这回重新加回来 INSERT INTO score VALUES('105', '3-105', '88'); INSERT INTO score VALUES('103', '3-105', '64'); INSERT INTO score VALUES('109', '3-105', '78'); 第二步：找出cno出现次数大于5的课程号 select cno from score group by cno having count(*)>5; 第三步：看看教师和课程表中有什么信息 mysql>select *from teacher; +-----+-------+------+---------------------+--------+------------+ | tno | tname | tsex | tbirthday | prof | depart | +-----+-------+------+---------------------+--------+------------+ | 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 | | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | | 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 | | 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 | +-----+-------+------+---------------------+--------+------------+ mysql> select * from course; +-------+------------+-----+ | cno | cname | tno | +-------+------------+-----+ | 3-105 | 计算机导论 | 825 | | 3-245 | 操作系统 | 804 | | 6-166 | 数字电路 | 856 | | 9-888 | 高等数学 | 831 | +-------+------------+-----+ 第四步：因为教师表中没有课程信息，只能通过tno和course表进行关联,利用第二步作为条件查询老师的tno select tno from course where cno=(select cno from score group by cno having count(*)>5); 第五步：根据tno查找教师姓名 select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5)); +-------+ | tname | +-------+ | 王萍 | +-------+

注意： 最好使用IN进行范围限定，因为选修大于5门的课程可能不止1门，用IN比较合适。

select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5)); +-------+ | tname | +-------+ | 王萍 | +-------+

（五）in表示或者关系

23.查询95033班和95031班全体学生记录

select *from student where class in('95031','95033'); 或是用or select *from student where class='95031' or class='95033'; +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+

（六）where条件查询

24.查询存在85分以上的成绩课程的cno

因为score表中存在cno，因此可以直接进行查询 select cno from score where degree>85; +-------+ | cno | +-------+ | 3-245 | | 3-105 | | 3-105 | | 3-105 | +-------+ 但这个查询结果具有重复性，因此需要利用distinct进行去重 select distinct cno from score where degree>85; +-------+ | cno | +-------+ | 3-105 | | 3-245 | +-------+

（七）其他子查询

25.查询·计算机系教师所交课程的成绩的成绩表 思路：

（1）先找出计算机系的老师 select *from teacher where depart='计算机系'; +-----+-------+------+---------------------+--------+----------+ | tno | tname | tsex | tbirthday | prof | depart | +-----+-------+------+---------------------+--------+----------+ | 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 | | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | +-----+-------+------+---------------------+--------+----------+ （2）根据课程表中的教师号查找对应的课程号 select cno from course where tno in(select tno from teacher where depart='计算机系'); +-------+ | cno | +-------+ | 3-245 | | 3-105 | +-------+ （3）根据课程号在score表中查询成绩 select *from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系')); +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 103 | 3-245 | 86 | | 105 | 3-245 | 75 | | 109 | 3-245 | 68 | | 103 | 3-105 | 92 | | 109 | 3-105 | 76 | | 105 | 3-105 | 91 | | 105 | 3-105 | 88 | | 103 | 3-105 | 64 | | 109 | 3-105 | 78 | +-----+-------+--------+

32.查询所有有任课的教师的tname和depart 思路：

（1）查询任课表中教师编号 select tno from course; +-----+ | tno | +-----+ | 804 | | 825 | | 831 | | 856 | +-----+ （2）根据任课表中的教师编号在教师表中找到需要的信息 select tname,depart from teacher where tno in(select tno from course); +-------+------------+ | tname | depart | +-------+------------+ | 李诚 | 计算机系 | | 王萍 | 计算机系 | | 刘冰 | 电子工程系 | | 张旭 | 电子工程系 | +-------+------------+

38.查询男教师及其所上的课

（1）找到男教师 select tno from teacher where tsex='男'; +-----+ | tno | +-----+ | 804 | | 856 | +-----+ （2）根据男教师tno查找对应的课程信息 select *from course where tno in(select tno from teacher where tsex='男'); +-------+----------+-----+ | cno | cname | tno | +-------+----------+-----+ | 3-245 | 操作系统 | 804 | | 6-166 | 数字电路 | 856 | +-------+----------+-----+

40.查询和李军同性别的其他同学的sname

（1）查看李军性别 select ssex from student where sname='李军'; +------+ | ssex | +------+ | 男 | +------+ （2）根据性别查找cname select sname from student where ssex=(select ssex from student where sname='李军'); +--------+ | sname | +--------+ | 曾华 | | 匡明 | | 李军 | | 陆军 | | 王尼玛 | | 张全蛋 | | 赵铁柱 | +--------+

41.查询和李军同性别且同班级的cname

（1）与李军同班级的查询 select class from student where sname='李军'; （2）只需在第40题的基础上加上班级的条件即可 select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军'); +--------+ | sname | +--------+ | 曾华 | | 李军 | | 王尼玛 | +--------+

42.查询选修“计算机导论”的男同学的成绩表

（1）查找男同学 select *from student where ssex='男'; +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+ （2）查询计算机导论的信息 select *from course where cname='计算机导论'; +-------+------------+-----+ | cno | cname | tno | +-------+------------+-----+ | 3-105 | 计算机导论 | 825 | +-------+------------+-----+ （3）根据课程和学生信息即可确定score的查询条件了 select *from score where sno in(select sno from student where ssex='男') and cno=(select cno from course where cname='计算机导论'); +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 109 | 3-105 | 76 | | 109 | 3-105 | 78 | +-----+-------+--------+