分组、排序、筛选、连接查询1.ppt

[例6-4-3]查询所有选课学生的学号、姓名、 课程  名称及成绩。 select s.sno,sn,cn,score from s,c,sc where s.sno=sc.sno and sc.cno=c.cno [例6-4-4]检索所有学生姓名，年龄和选课名称。 select sn,age,cn from s,c,sc where s.sno=sc.sno and sc.cno=c.cno 例6-4-5：查找与出版商不住在同一城市中的作者 (au_id,au_lname,au_fname) (使用Exist / Not Exists) select * from authors where exists (select * from publishers where publishers.city=authors.city ) [例6-5-1]查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。 select s.sno, sn, cn, score from s left outer join sc on s.sno=sc.sno left outer join c on c.cno=sc.cno 分组、排序、筛选 连接查询、子查询 学生表:Student由学号(sno)、姓名(sn)、性别(sex)、年龄(age)、所在系(dept)五个属性组成， 记为：s(sno,sn,sex,age,dept)，sno 为关键字。 课程表:course由课程号(cno)、课程名(cn)、学分(ct)四个  属性组成， 记为：c(cno,cn,ct) cno为关键字。 选课表SC 由学号(sno)、课程号(cno)、成绩(score)四个 属性组成 记为：sc (sno,cno,score) (sno,cno)为主键。 [例] 建立一个S表，定义SNo为S的主键，一个数据表C，定义CNo为C的主键，一个sc表，其中(sno,cno)为主 create table s ( sno char(6) constraint s_prim primary key, sn char(8), sex char(2), age int, dept varchar(20) ) create table c ( cno char(5) constraint c_prim primary key, cn char(20), ct int ) create table sc ( sno char(5) not null, cno char(5) not null, score numeric(4,1), constraint sc_prim primary key(sno,cno) ) s(sno,sn,sex,age,dept) c(cno,cn,ct) sc (sno,cno,score) 三个关系模式如下： 6.1 分组查询 GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。 若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句 [例6-1-1] 查询选修两门以上课程的学生的学号和选课门数。 SELECT SNo, COUNT(*) AS SC_Num FROM SC GROUP BY SNo HAVING (COUNT(*) = 2) GROUP BY子句按SNo的值分组，所有具有相同SNo的元组为一 组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门 数。HAVING子句去掉不满足COUNT（*）=2的组 6.2 查询的排序 当需要对查询结果排序时，应该使用ORDER BY子句，ORDER BY子句必须出现在其他子句之后。排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。 [例6-2-1] 查询选修C1 的学生学号和成绩，并按成绩降序排列。 SELECT SNo, Score FROM SC WHERE (CNo = C1) ORDER BY Score DESC [例6-2-1]