SQL多表查询

SQL多表查询

1. 连接查询
   （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

2. 嵌套查询
   （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

3. 带有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)

4. 集合查询 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'