连接查询和子查询

实验目的：掌握多表查询、连接查询和子查询的方法。

1.查询所有学生的学号、姓名、选修课程号和成绩。

use XSCJ
go 
select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno

2.查询选修了课程名称为“数据库原理与应用”的学生的学号和姓名。

select distinct student.sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
and cname='数据库原理与应用'

3.使用别名实现查询所有学生的学号、姓名、选修课程号和成绩。

select s.sno,sname,cno,grade from student as s,sc as sc1 where s.sno=sc1.sno

4.查询所有年龄比刘磊大的学生的姓名、性别和年龄。

select X.sname,X.ssex,X.sage from student as X,student as Y
where Y.sname='刘磊' and X.sage>Y.sage

5.实现查询所有学生的学号、姓名、选修课程号和成绩。

select student.sno,sname,cno,grade from student join sc on student.sno=sc.sno

6.查询所有学生的学号、姓名及对应选课信息，如果该学生没有选课，也需要显示该生的学号和姓名。

select student.sno,sname,cno,grade from student left outer join sc on student.sno=sc.sno

7.查询选课学生的基本信息（若实际上有外键约束，这种情况是不存在的）。

select student.sno,cno,grade,sname from student right outer join sc on student.sno=sc.sno

8.采用右外连接，查询学生的学号、选课的课程号、课程名及学分，同时也列出无学生选修的课程信息。

select sc.sno,course.cno,cname,credit from sc right outer join course on sc.cno=course.cno

9.student和sc表实现全外连接。

select * from student  full outer join sc on student.sno=sc.sno

10.从student表中查询年龄为“19”和“20”的学生的系部，不包括重复行。

select distinct sdept from student where sage='19' or sage ='20'
或者
select distinct sdept from student where sage='19'
union
select distinct sdept from student where sage='20'

11.从student表中查询年龄为“19”和“20”的学生的系部，包括重复行。

select sdept from student where sage ='19' 
union all
select sdept from student where sage ='20'
--或者
select sdept from student where sage in(19,20)

12.查询所有选修课程的学生的学号和姓名。

select sno,sname from student where sno in(select sno from sc)

13.查询所有选修课程的学生的学号和姓名，改为连接查询实现。

select distinct student.sno,sname from student join sc on student.sno = sc.sno

14.查询年龄高于平均年龄的学生的学号、姓名和年龄。

select sno,sname,sage from student where sage>(select avg(sage) from student)

15.查询比CS系的任一学生年龄都大的学生姓名和年龄。

select sname,sage from student where sage> any(select sage from student where sdept='CS')
and sdept !='CS'
select * from student

16.查询已有学生选修的课程信息。

select * from course where exists(select * from sc where course.cno=sc.cno)

17.查询尚没有学生选修的课程信息。

select * from course where not exists(select * from sc where course.cno=sc.cno)

18.查询CS系学生的信息，生成一个新表temp.

select * into temp from student where sdept='CS'
select * from temp

19.将所有的学号和课程号信息生成一个新表SC1。

insert into SC1(sno,cno)
select sno,cno from student,course

20.将选修了“前台页面设计”课程的学生成绩增加5分。

update sc
set grade=grade+5
where cno=(select cno from course where sc.cno=course.cno and cname='前台页面设计')

21.删除选修了“前台页面设计”课程的选课信息。

delete from sc where cno=(select cno from course where sc.cno=course.cno and cname='前台页面设计')

小结：

内连接查询的两种格式：一种是使用‘，’进行连接的格式，一种是（JOIN ON）格式。三种外连接查询的语法格式是：左外连接（LEFT OUTER JOIN ON）、右外连接（RIGHT OUTER JOIN ON）、全外连接（FULL OUTER JOIN ON）。