数据库(三)关系数据库标准语言SQL(2)

连接查询

查询同时涉及两个以上的表，对应代数运算中的连接。

等值/非等值连接

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 ) );

子类型 all/some

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)...