连接查询
查询同时涉及两个以上的表,对应代数运算中的连接。
等值/非等值连接
select Student.*,SC.* from Student,SC where Student.Sno = SC.Sno and SC.Grade>90;
自身连接
select first.Cno, second.Cpno from Course first, Course second where first.Cpno = second.Cno;
外连接
select Student.Sno from Student left outer join SC on (Student.Sno = SC.Sno);
嵌套查询
带有in谓词
select Sno from Student where Sdept in (select Sdept from Student where Sname = '张三');
带有比较关系运算符
select Sno from SC x where Grade >= (select AVG(Grade) from SC y where y.Sno = x.Sno);
带有any(任意值)/all(所有值)
select Sname from Student where Sage < any(select Sage from Student where Sdept = 'CS') and Stept <>'CS';
带有exist
只返回查询的逻辑值true/false
select Sname from Student where not exist (select * from SC where Sno = Student.sno and Cno = '1'); 选出没有选1号课程的学生姓名
全称量词的查询
选出至少选择了95001号同学所选全部课程的学生的学号
select distinct Sno from SC x where not exists (
select * from SC y where y.Sno = 95001 and not exists(
select * from SC z where z.Sno = x.Sno and z.Cno = y.Cno ) );
select Sname from Student where 60 > all(select Score from SC where...);
集合操作
并union 交intersect 差except
select * from Student where Sdept='CS' istersect select * from student where Sage<=19;
选择新数据源
select sno,sname avg_grade from student ,(select sno,avg(grade) as avg_grade from sc group by sno)...