连接查询 带有in谓词的子查询 带有exists谓词的子查询 只返回真假 集合查询

9连接查询

–1.简单连接
select Student.,SC.
from Student,SC
where Student.Sno=SC.Sno;

–2.自然连接(取消重复列 sno)
select Student.Sno,sname,sex,sdept,sage,cno,grade
from Student,SC
where Student.Sno = SC.Sno;

select Student.*,cno,grade
from Student,SC
where Student.Sno = SC.Sno;

–3.连接相关的复合操作
–2号课程成绩大于90的学生学号,姓名
select Student.Sno,Student.Sname,SC.Grade,SC.Cno
from Student,SC
where student.sno = SC.Sno
and cno=2
and Grade>90;

–4.自身连接(起别名)
–查询课程先修课的先修课
select f.Cno,s.Cpno
from Course f, Course s
where f.Cpno = s.Cno;

–5.外连接
–自然连接有条件显示不全
select Course.*,sno,grade
from Course,SC
where Course.Cno = SC.Cno;

select Course.*,sno,grade
from Course full outer join sc
on course.cno = sc.cno;

–左外 左边是主体
select Course.*,sno,grade
from SC left outer join Course
on course.cno = sc.cno;

–右外 右边的是主体
select Course.*,sno,grade
from SC right outer join Course
on course.cno = sc.cno;

–6.多表连接
select Student.Sno,sname,cname,grade
from Student,SC,Course
where Student.Sno = SC.sno and SC.Cno = Course.Cno;

10.带有in谓词的子查询

–查询与欧阳娜在同一个系学习的学生
select *
from Student
where Sdept in (select sdept
from Student
where Sname = ‘欧阳娜’);

select s2.Sno,s2.Sname,s2.Sdept
from Student s1,Student s2
where s1.Sdept = s2.Sdept and s1.Sname = ‘欧阳娜’;

select *
from Student s1
where exists (select *
from Student s2
where s1.Sdept = s2.Sdept and s2.Sname=‘欧阳娜’)
and s1.Sname != ‘欧阳娜’;

–查询选修课信息系统学生学号姓名

select Sno,Sname
from Student
where Sno in (select Sno
from SC
where Cno in (select Cno
from Course
where Cname = ‘信息系统’)) ;

–选出成绩比平均成绩大的科目(相关子查询)
select *
from SC s1
where s1.Grade >= (select AVG(grade)
from SC s2
where s1.Sno = s2.Sno);

–查询其他系比信息系任意一个学生年龄小的姓名年龄
select *
from Student
where Sdept not in (‘IS’ ) and Sage < any (select Sage
from Student
where Sdept = ‘IS’);

11带有exists谓词的子查询 只返回真假

–查询所有选修了一号课程学生
select *
from Student
where exists (select *
from SC
where sno = student.sno and Cno = 1 );
–查询没有选修了一号课程学生
select *
from Student
where not exists (select *
from SC
where sno = student.sno and Cno = 1);

–查询选修了全部课程的学生姓名 全部 改为 否定的否定
–(没有任何课程不被选的学生) student course sc
/*
　　最里层的子查询用于查找某个学生选修的所有课程；
　　中间层的子查询(结合not exists)用于查找该学生没有选修的课程；
　　最外层的查询(结合not exists)用于查找不存在“有几门课程没有选修”情况的学生，即选修了全部课程
*/
select *
from Student
where not exists (select *
from Course
where not exists(
select *
from SC
where Sno = Student.Sno
and Cno = Course.Cno));

–查询至少选修了201215121选修的全部课程学生
–(不存在这样的课程y,201215121选了y,而学生x没选)
select *
from Student
where not exists (select *
from SC s1
where Sno = ‘201215121’
and not exists(
select *
from SC s2
where Sno = Student.Sno
and s1.Cno = s2.Cno));

12.集合查询

–1 集合并
–查询计算机系学生及年龄小于20岁学生
select *
from Student
where Sdept = ‘CS’
union–并起来
select *
from Student
where Sage<=20;

–2集合交
select *
from Student
where Sdept = ‘CS’
intersect–交起来
select *
from Student
where Sage<=20;
–3集合差
select *
from Student
where Sdept = ‘CS’
except–差起来
select *
from Student
where Sage<=20;