多表查询
1、结构：select from WHERE
2、笛卡尔积查询
select * from student,course
select * from student,sdept
3、查询每个学院的学生情况
select * from student,sdept where student.deptno=sdept.deptno
4、查询计算机学院所有的学生记录
select * from student,sdept where student.deptno=sdept.deptno and sdept.dname='计算机与信息工程学院'
select student.*,dname,addree,manger,telephone from student,sdept where student.deptno=sdept.deptno
and sdept.dname='计算机与信息工程学院'
5、使用join连接字段的查询
（1）内连接查询select from 表1 inner join 表2 on 连接条件
查询每个学院学生的基本情况
select * from student inner join sdept on student.deptno=sdept.deptno
查询选修了课程的所有学生的姓名课程名称以及成绩
select sname,cname,grade from student inner join score on student.sno=score.sno inner join course on o=o
（2）使用自然连接的查询 select from 表一 natural join sdept
例：查询所有学院学生的情况
select * from student natural join sdept
例：查询每个学生的基本信息和成绩信息，去掉重复列
select * from student natural join score
（3）左外连接 select ...from 表1 left outer join 表2 on 连接条件
select * from student left outer join score on student.sno=course.sno
（4）左外连接 select ...from 表1 RIGHT outer join sdept on 连接条件
select * from sdept right outer join course on sdept.deptno=course.institute
6、自身链接 select ...from 表 表名1，表 表名2
例；查询与高天在同一院系的所有学生
select s2.* from student s1,student s2 where s1.deptno=s2.deptno and s1.sname='高天'
例；查询与高天在同一院系的所有学生,不包括高天
select s2.* from student s1,student s2 where s1.deptno=s2.deptno and s1.sname='高天'
查询学生表中年龄低于计算机学院最小年龄的所有学生姓名
select sname from student where sage<all(select sage from student,sdept where student.deptno=sdept.deptno and dname='计算机与信息工程学院')
select sname from student where sage<(select MIN(sage)from student,sdept where student.deptno=sdept.deptno and dname='计算机与信息工程学院')
一使用嵌套查询
1.查询通信学院所有学生的基本情况
select * from student where deptno=(select deptno from sdept where dname like '%通信%')
2.查询选修了A001这门课程的所有学生信息
select * from student where sno in(select sno from score where cno='A001')
3.使用any 查询选修了A001这门课程的所有学生信息
select * from student where sno=any(select sno from score where cno='A001')
4.查询编号为“04”院系中年龄小于“01”院系的最小年龄的学生信息
select * from student where deptno='04' sage<(select min(sage) from student where deptno='01')
select * from student where deptno='04' sage<all(select sage from student where deptno='01')
5.查询编号为“04”院系中年龄小于“01”院系的任何一位学生年龄的学生信息
select * from student where deptno='04' sage<any(select sage from student where deptno='01')
select * from student where deptno='04' sage<(select max(sage) from student where deptno='01')
6.查询选修了C语言学生的学号和姓名
select sno,sname from student where sno in (select sno from score where cno=(select cno from course where cname='C语言'))
select student.sno,sname from student,course,score where student.sno=score.sno and o=oand cname='C语言'
7.查询学号为“070306”这名学生的总学分
select sum(ccredit) from course where cno in
(select cno from score where sno='070306')
8.查询计算机学院的学生人数
select count(*) from student where deptno=
(select deptno from sdept where dname='计算机学院')
