SQL多表查询
- 连接查询
(1)等值查询
/* 查询每个学生的信息和选修课程的信息 stu sc */
select stu.*,sc.* from stu,sc where stu.sno=sc.sno
/* 查询选修 "20201" 学生的姓名sname */
select sname from stu,sc where stu.sno=sc.sno and sc.cno='20201'
-- 多表连接
/* 查询每个学生的信息和选修课程的信息和学时 */
select stu.*,sc.*,ctime from stu,sc,cou where stu.sno=sc.sno and sc.cno=cou.cno
(2)左外连接
/* 查询所有的学生信息和选课信息,但是没有选修的学生也要显示出来 */
select stu.*,sc.* from stu left outer join sc on stu.sno= sc.sno
/* 查询每个专业的学生人数,假设每个专业都有人 */
select mno,COUNT(sno) from stu group by mno
select mno,COUNT(sno) from stu group by mno having mno between 1 and 4
/* 查询每个专业的学生人数,但是有的专业可能没有人 */
select * from major
insert into major values(5,'人工智能')
select major.mno,COUNT(sno) from major left outer join stu on major.mno=stu.mno group by major.mno
- 嵌套查询
(1)不相关嵌套查询(子查询不依赖父查询)
建议:统一用 in 因为我们查询的时候并不知道结果有多少,所以用 in 最好
/* 查询选修 "20201" 学生的姓名sname */
select sname from stu where sno in (select sno from sc where cno='20201')
/* 查询选修 "20202" 学生的姓名sname */
select sname from stu where sno in (select sno from sc where cno='20202') -- 返回只有 小五 一个结果
-- in(返回多个结果) =(返回一个结果)
select sname from stu where sno = (select sno from sc where cno='20202') -- 因为只有一个结果,所以可以用 "="
-- 建议:统一用 in 因为我们查询的时候并不知道结果有多少,所以用 in 最好
(2)相关嵌套连接(将连接放在子查询里面)
派生表不是在数据库里面新建一个表,而是根据我们的需要创建的一个表
/* 查询选修 "20201" 学生的姓名sname */
select sname from stu,sc where stu.sno=sc.sno and sc.cno='20201'
select sname from stu where '20201' in(select cno from sc where stu.sno=sc.sno)
/* 查询选择 "C语言" 课程的学生学号 */
select sno from sc where 'C语言' in (select cname from cou where sc.cno=cou.cno)
/* 查询每个学生超过他平均分的课程号 第二种方法用派生表实现 */
select sno,AVG(grade) from sc group by sno
-- 第一种方法 给表取别名 因为sc.sno = sc.sno 数据库识别不了
select sno,cno from sc x where grade>(select AVG(grade) from sc y group by sno having x.sno=y.sno)
-- 第二种方法 派生表 (select sno,AVG(grade) from sc group by sno) as avg_sc(avg_sno,avg_grade)创建一个名为avg_sc的新表
-- 派生表不是在数据库里面新建一个表,而是根据我们的需要创建的一个表
select sno,cno
from sc,(select sno,AVG(grade) from sc group by sno) as avg_sc(avg_sno,avg_grade)
where sc.sno=avg_sc.avg_sno and grade>avg_grade
- 带有exists(涉及2个表也需要连接)
exists后面的括号里面不会返回具体的数据,只会返回true or false, 每次取一个sno连接 如果连接成功返回的条数大于等于1的话返回true 如果小于0就返回false
/* 查询选修 "20201" 学生的姓名sname */
select sname from stu,sc where stu.sno=sc.sno and cno='20201'
select sname from stu where exists (select * from sc where cno='20201' and stu.sno=sc.sno)
- 集合查询 union(并) intersect(交) except(差)
/* 查询年龄是18且mno=1的学生学号 intersect */
select sno from stu where age='18' and mno='1'
select sno from stu where age='18' intersect select sno from stu where mno='1'
/* 查询年龄是18且mno=1的学生学号 except */
select sno from stu where age=18 except select sno from stu where mno!=1
/* 查询选修 "20201" 号课程或 "20203"的学生学号 */
select distinct sno from sc where cno in('20201','20203')
select sno from sc where cno='20201' union select sno from sc where cno='20203'
/* -- 查询选修 "20201" 号课程且 "20203"的学生学号 */
select distinct sno from sc where cno='20201' intersect select sno from sc where cno='20203'