文章目录
- 子查询
- 子查询的特点
- 题型综合
子查询
称其顺藤摸瓜
实验相关数据表头:
gradem库表
course:课程信息;
sc:记录成绩相关的信息;
student:学生相关信息;
teaching:老师授课情况;
teacher:老师的基本信息;
例1:查询出生日期比闫旭光大的人的姓名和出生日期;
查询闫旭光的出生日期:
(1)SELECT sbirthday FROM student WHERE sname="小米";
(2) SELECT sname,sbirthday FROM student WHERE sbirthday>"1987-08-12";
分析:我们有没有更为简洁的查询,可以通过一条SQL语句实现结果?
利用子查询,可将上面的两条SQL语句合并为一句SQL
SELECT sname,sbirthday FROM student
WHERE sbirthday>(SELECT sbirthday FROM student
WHERE sname="小米");
其中第一个查询”SELECT sbirthday FROM student WHERE sname=“小米””就是子查询,它嵌套在外层查询”SELECT sname,sbirthday FROM student”中,作为where条件中的一部分;
子查询的特点
1、像这样一个查询语句嵌套在另一个查询语句内部的查询,我们称之为子查询;
2、在执行子查询语句时,首先执行子查询中的语句,然后将返回结果作为外层查询的过滤条件;
3、子查询必须放在一对圆括号内,外层查询称为父查询;
分类讲解子查询
(1)带IN关键字的子查询
什么时候使用带in关键字的子查询?
当内层查询结果仅返回一个数据列(这里再强调一下in和”=”的区别)
例1:查询a01成绩为80的学生的姓名
子查询:顺藤摸瓜,想要的结果-----中间变量----条件
select sname from student where sno in
(select sno from sc where cno="a01" and degree=80);
字段 in(子查询):字段的取值在子查询的结果集里面;
想要的结果-------中间变量(sno)---------条件
例2:查询选修JAVA程序设计基础的学生学号、成绩;
course:cname
sc:degree
select sno,degree from sc where cno
in(select cno from course where came="JAVA程序设计基础");
cname-cno-sno,degree
例3:查询电子工程系已经选修课程的学生的学号,选修的课程号以及对应的成绩;
sdept:student
select sno,cno,degree from sno in
(select sno from stduent where sdept="电子工程系");
例4:查询选修了a03课程的学生的名字以及所在的系别;
sc: select sno from sc where cno=“a03”;
student:
select sname,sdept from student where sno in(select sno from sc where cno="a03");
中间变量:两个表里面共有的字段(相同意义的字段)通常作为中间变量;
(2)子查询结合group by一块使用
例5:查询选修了a03课程且总成绩高于300分的学生的学号,以及总成绩;
常出现的错误写法:
select sno,sum(degree) from sc where cno=”a03” group by sno having sum(degree)>300;
正确
SELECT sno,SUM(degree) FROM sc
WHERE sno IN(SELECT sno FROM sc WHERE cno="a03") GROUP BY sno
HAVING SUM(degree)>300;
拆分
1、select * from sc where cno=”a03”;
2、select * from sc where sno in(select sno from sc where sno=”a03”);
(3)带exists关键字的子查询
Exists关键字后面的参数可以是任意一个子查询,这个子查询的作用相当于测试,它不产生任何数据,只返回true或false,当返回值为true时,外层查询才会执行;
例如:
select * from department where exists(select did from employee where age>20);
格式:外层查询 where exists(内层查询);
例1:查询student表里是否存在年龄小于20岁的学生,如果有,则查询student表的sno,sbirthday字段;
例2:查询student表里是否有年龄在30-40之间的学生,如果有,则查询student 表的sno,sbirthday字段;
题型综合
与题型有关表的标签如下:
学生(学号、姓名、性别、课程、系别、课程号等)
teacher(工号、姓名、性别、系别等)
teaching(课程号 工号)
course(课程号 课程名字)
sc(学号、课程号、成绩)
若答案有误或者存在更好的写法,欢迎留言。
1、查询c01课程成绩高于80分的学生名字(所涉及的表student,sc);
select sname from student where sno in
(select sno from sc where cno="c01" and degree>80);
2、查询选修大学英语且成绩高于80分的学生的学号,课程号以及对应的成绩(所涉及的表sc学号、成绩,course对应课程号);
select sno,cno,degree from sc where sno in
(select sno from sc where cname="大学英语" and degree>80);
3、查询”大学英语”和”高等数学”授课教师的工号及两门课程所对应的课程号;(所涉及的表course,teaching)
select tno,cno from course where tno in
(select tno from teaching where cname="大学英语" or cname="高等数学");
4、查询”大学英语”和”软件工程”授课教师的名字;(所涉及的表course课程名字和教师名字,teaching工号和课程名字,teacher教师名字 工号)
select tname from teacher where tno
in(select tno from teaching where cno
in(select cno form course where cname="大学英语" or cname="软件工程"));
5、查询和刘尚在一个系的学生的学号,所选修的课程号以及对应的成绩;(sc,student)
select sno,cno,degree from sc where sno
in(select sno from student where sdept
in(select sdept from student where sname="刘尚") and sname!="刘尚");
6、查询a01课程成绩不高于2007010104这位同学的a01成绩的学生的学号以及对应的成绩;(sc表)
“in” 和 “=”
in 是万能的,=只能匹配一条记录;
select sno,degree from sc where degree>(select degree from sc where sno="2007010104 and sno="a01");