第三章：关系数据库标准语言SQL课件.pptVIP

第三章：关系数据库标准语言SQL;3.4 数据查询;;Distinct子句;查询满足条件的元组（选择）;查询满足条件的元组;范围子句;集合子句in;字符匹配like;涉及空值的查询;多重查询条件;排序子句order by;聚集函数;计数count;均值、最大最小值;分组 group by;分组筛选having;3.4.2 连接查询;Where连接;自身连接 查询每一门课的间接先修课程（先修课的先修课） Select first.cno, second.cpno from course first,course second where first.cpno=secnod.cno;外连接、左外连接、右外连接;多表连接;;3.4.3嵌套查询;;方法二;查询选修了课程名为“信息系统”的学生学号、姓名 ;用连接查询实现 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=‘信息系统’；;带有比较运算符的子查询;;带有any或者all谓词的子查询;; SELECT Sname，Sage FROM Student WHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept= CS ) AND Sdept CS ’;;带有exists量词的子查询;SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 )；;利用存在量词建立跨表查询的方法; SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = ‘刘晨’);查询李勇各门考试的课程号和成绩;查询选修了信息系统的学生姓名;Select sname from student where exists (select * from sc where student.sno=sc.sno and not exists (select * from course where cname=‘信息系统’ and sc.cno=course.cno)) 其语义是什么？;;Select sno from student where exists( Select * from course where exists( Select * from sc where student.sno=sc.sno and Course.cno=sc.cno));Select sno from student where exists( Select * from course where not exists( Select * from sc where student.sno=sc.sno and Course.cno=sc.cno));SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno）） ;等价变换： (?y)p ? q ≡ ? (?y (?(p ? q ))