2022年关系数据库标准语言SQL(1).ppt

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,淮海工学院计算机系,数据库原理及应用,学习目标,掌握连接查询,掌握嵌套查询,掌握集合查询,理解,SELECT,语句的一般格式,3.3 查询,概述,单表查询,连接查询,嵌套查询,集合查询,连接查询：,同时涉及多个表的查询。用来连接两个表的条件称为连接条件或连接谓词。,连接条件的一般格式：,.,或,.,BETWEEN.,AND,.,其中：,连接谓词中的列名称为连接字段，连接条件中的各连接字段类型必须是可比的，但不必是相同的。,连接操作的执行过程,嵌套循环法,首先在表,1,中找到第一个元组，然后从头开始扫描表,2,，逐一查找满足连接件的元组，找到后就将表,1,中的第一个元组与该元组拼接起来，形成结果表中一个元组。,表,2,全部查找完后，再找表,1,中第二个元组，然后再从头开始扫描表,2,，逐一查找满足连接条件的元组，找到后就将表,1,中的第二个元组与该元组拼接起来，形成结果表中一个元组。,重复上述操作，直到表,1,中的全部元组都处理完毕。,排序合并法,首先按连接属性对表1和表2排序。,对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续。,找到表,1,的第二条元组，然后从刚才的中断点处继续顺序扫描表,2,，查找满足连接条件的元组，找到后就将表,1,中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表,2,中大于表,1,连接字段值的元组时，对表,2,的查询不再继续。,重复上述操作，直到表,1,或表,2,中的全部元组都处理完毕为止。,索引连接,对表2按连接字段建立索引。,对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。,SQL,中连接查询的主要类型：,广义笛卡尔积查询、等值连接(含自然连接)查询,、,非等值连接查询、自身连接查询、外连接查询、复合条件连接查询。,广义笛卡尔积(,很少使用,)：,不带连接谓词的连接。,例：,SELECT Student.*,SC.*FROM Student,SC；,等值连接：,连接运算符为=的连接操作。,格式：,.=.,说明：,任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。,Student,Cno,Cname,Cpno,Ccredit,1,数据库,5,4,2,数学,2,3,信息系统,1,4,4,操作系统,6,3,5,数据结构,7,4,6,数据处理,2,7,C语言,6,4,Course,Sc,Sno,Cno,Grade,95001,1,92,95001,2,85,95001,3,88,95002,2,90,95002,3,80,95001,95004,95003,95002,Sno,李勇,张立,王敏,刘晨,Sname,男,男,女,女,Ssex,20,19,18,19,Sage,CS,IS,MA,IS,Sdept,示例数据库：,学生-课程数据库,例32查询每个学生及其选修课程的情况。,SELECT Student.*，SC.*FROM Student，SC,WHERE Student.Sno=SC.Sno；,结果：,Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade,95001 李勇 男 20 CS 95001 1 92,95001 李勇 男 20 CS 95001 2 85,95001 李勇 男 20 CS 95001 3 88,95002 刘晨 女 19 IS 95002 2 90,95002 刘晨 女 19 IS 95002 3 80,自然连接：,等值连接的一种特殊情况，把目标列中重复的属性列去掉。,例33对例32,用自然连接完成。,SELECT Student.,Sno,，,Sname,，,Ssex,，Sage，,Sdept,，,Cno,，Grade,FROM Student，SC WHERE Student.,Sno,=SC.,Sno,；,非等值连接查询：,连接运算符不是=的连接操作。,自身连接：,一个表与其自己进行连接。连接需给表起别名以示区别，由于所有属性名都是同名属性，因此须使用别名前缀。,例,34查询每一门课的间接先修课(即先修课的先修课)。,SELECT FIRST.,Cno,，SECOND.,Cpno,FROM Course FIRST，Course SECOND,WHERE FIRST.,Cpno,=SECOND.,Cno,；,Cno,Cname,Cpno,Ccredit,1,数据库,5,4,2,数学,2,3,信息系统,1,4,4,操作系统,6,3,5,数据结构,7,4,6,数据处理,2,7,C语言,6,4,Cno,Cname,Cpno,Ccredit,1,数据库,5,4,2,数学,2,3,信息系统,1,4,4,操作系统,6,3,5,数据结构,7,4,6,数据处理,2,7,C语言,6,4,Cno,Cpno,1,7,3,5,5,6,FIRST表,SECOND表,查询结果,外连接：,在普通连接操作中只有满足连接条件的元组才能作为结果输出，如例32 的结果表中没有95003和95004的学生信息，原因是他们没有选课，在,SC,表中没有相应的元组，但要以,Student,表为连接主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，只输出其基本情况，其选课信息为空，这时就需要使用外连接。,在表名后面加外连接操作符(*)或(+)指定非主体表，非主体表有一万能的虚行，该行全部由空值组成，虚行可与主体表中所有不满足连接条件的元组进行连接，由于虚行各列全部是空值，因此与虚行连接的结果中来自非主体表的属性值全部是空值。,外连接符出现在连接条件的左边的称为左外连接，外连接符出现在连接条件的右边的称为右外连接。,将例32改为：,SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade,FROM Student，SC WHERE Student.Sno=SC.Sno(*)；,结果：,Student.Sno Sname Ssex Sage Sdept Cno Grade,95001 李勇 男 20 CS 1 92,95001 李勇 男 20 CS 2 85,95001 李勇 男 20 CS 3 88,95002 刘晨 女 19 IS 2 90,95002 刘晨 女 19 IS 3 80,95003 王敏 女 18 MA,95004 张立 男 19 IS,复合条件连接：,WHERE,子句中含多个连接条件。,例35,查询选修,2,号课程且成绩在,90,分以上所有学生的学号、姓名。,SELECT Student.,Sno,student.,Sname,FROM Student,SC,WHERE Student.,Sno,=SC.,Sno,AND SC.,Cno,=2 AND SC.Grade 90；,多表连接,例36查询每个学生的学号、姓名、选修的课程名及成绩。,SELECT Student.,Sno,，,Sname,，,Cname,，Grade,FROM Student，SC，Course,WHERE Student.,Sno,=SC.,Sno,and SC.,Cno,=Course.,Cno,；,结果：,Student.,Sno Sname Cname,Grade,95001,李勇 数据库 92,95001 李勇 数学 85,95001 李勇 信息系统 88,95002 刘晨 数学 90,95002 刘晨 信息系统 80,嵌套查询,概述：,一个,SELECT-FROM-WHERE,语句称为一个查询块，将一个查询块嵌套在另一个查询块的,WHERE,子句或,HAVING,短语的条件中的查询称为嵌套查询。其中外层查询块称为父查询，内层查询块称为子查询，子查询不能使用,ORDER BY,子句。层层嵌套方式反映了,SQL,语言的结构化，有些嵌套查询可以用连接运算来替代。,SELECT,Sname,FROM Student,外层查询/父查询,WHERE,Sno,IN,（SELECT,Sno,FROM SC,内层查询/子查询,WHERE,Cno,=2）；,嵌套查询分类,不相关子查询：,子查询的查询条件不依赖于父查询，由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。,相关子查询：,子查询的查询条件依赖于父查询，首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若,WHERE,子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组。重复这一过程，直至外层表全部检查完为止。,引出子查询的谓词：,带有,IN,谓词的子查询、带有比较运算符的子查询、带有,ANY,或,ALL,谓词的子查询、带有,EXISTS,谓词的子查询。,上例可构造嵌套查询：,将第一步查询嵌入到第二步查询条件中。,SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN,(SELECT Sdept FROM Student WHERE Sname=刘晨)；,此查询为不相关子查询，DBMS求解该查询时也是分步去做的。,也可用自身连接完成本查询要求：,SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2,WHERE S1.Sdept=S2.Sdept AND S2.Sname=刘晨；,父查询和子查询中的表均可以定义别名：,SELECT Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN,(SELECT Sdept FROM Student S2 WHERE S2.Sname=刘晨)；,例38查询选修了课程名为信息系统的学生学号和姓名。,SELECT Sno，Sname FROM Student,最后在Student关系中取出Sno和Sname,WHERE Sno IN(SELECT Sno FROM SC,然后在SC关系中找出选修了3号课程的学生学号,WHERE Cno IN,(SELECT Cno FROM Course WHERE Cname=信息系统);,首先在Course关系中找出信息系统的课程号，结果为3号,结果：,Sno Sname,95001 李勇,95002 刘晨,本例也可用连接查询：,AND Course.Cname=信息系统；,带有比较运算符的子查询：当能确切知道内层查询返回单值时，可用比较运算符(,，=，=，!=或)。,假设一个学生只可能在一个系学习，并且必须属于一个系，则在例37可以用=代替,IN：,SELECT,Sno,，,Sname,，,Sdept,FROM Student WHERE,Sdept,=,SELECT,Sdept,FROM Student WHERE,Sname,=,刘晨；,子查询一定要跟在比较符之后，一个错误的例子如下：,SELECT,Sno,，,Sname,，,Sdept,FROM Student,WHERE(SELECT,Sdept,FROM Student,WHERE,Sname,=,刘晨 )=,Sdept,；,例39查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄。,SELECT Sname，Sage FROM Student,WHERE Sage ANY(SELECT Sage FROM Student,WHERE Sdept=IS),AND Sdept IS ;/*注意这是父查询块中的条件*/,结果：,Sname,Sage,王敏 18,执行过程：,1.DBMS执行此查询时，首先处理子查询，找出 IS系中所有学生的年龄，构成一个集合(19,18)。,2.处理父查询，找所有不是IS系且年龄小于19 或 18的学生。,ANY和ALL谓词有时可以用集函数实现，对应关系如下：,用集函数实现子查询通常比直接用ANY或ALL查询效率要高，,因为前者通常能够减少比较次数。,用集函数实现例39如下：,SELECT Sname，Sage FROM Student,WHERE Sage (SELECT MAX(Sage)FROM Student,WHERE Sdept=IS)AND Sdept IS;,=,或!=,=,ANY,IN,-,MAX,MIN,=MIN,ALL,-,NOT IN,MIN,MAX,=MAX,例40查询其他系中比IS系所有学生年龄都小的学生姓名及年龄。,方法一：用ALL谓词,SELECT Sname,Sage FROM Student WHERE Sage ALL,(SELECT Sage FROM Student WHERE Sdept=IS),AND Sdept IS；,方法二：用集函数,SELECT Sname，Sage FROM Student WHERE Sage,(SELECT MIN(Sage)FROM Student WHERE Sdept=IS),AND Sdept IS；,带有,EXISTS,谓词的子查询,NOTEXISTS,谓词：,相当于存在量词,。带有,EXISTS,谓词的子查询不返回任何数据，只产生逻辑,true,或,false，,若内层查询结果非空则返回真值，否则返回假值。由,EXISTS,引出的子查询的目标列表达式通常都用*，因为带,EXISTS,的子查询只返回真值或假值，给出列名无实际意义。,例41查询所有选修了1号课程的学生姓名。,思路分析：,本查询涉及,Student,和,SC,关系，首先在,Student,中依次取每个元组的,Sno,值，用此值去检查,SC,关系，若,SC,中存在这样的元组，其,Sno,值等于此,Student.,Sno,值，并且其,Cno,=1，,则取此,Student.,Sname,送入结果关系。,用嵌套查询,SELECT,Sname,FROM Student WHERE EXISTS/*,相关子查询*/,(,SELECT*FROM SC WHERE,Sno,=Student.,Sno,AND,Cno,=1)；,求解过程,用连接查询,SELECT,Sname,FROM Student,SC,WHERE Student.,Sno,=SC.,Sno,AND SC.,Cno,=1；,不同形式的查询间的替换：,一些带,NOTEXISTS,谓词的子查询不能被其他形式的子查询等价替换，所有带,IN,谓词、比较运算符、,ANY,和,ALL,谓词的子查询都能用带,EXISTS,谓词的子查询等价替换。,例,42,查询没有选修,1,号课程的学生姓名。(用连接运算难于实现),SELECT,Sname,FROM Student,WHERE NOT EXISTS(SELECT*FROM SC,WHERE,Sno,=Student.,Sno,AND,Cno,=1),；,例37,查询与刘晨在同一个系学习的学生，,可以用带,EXISTS,谓词的子查询替换：,SELECT,Sno,，,Sname,，,Sdept,FROM Student S1,WHERE EXISTS SELECT*FROM Student S2,WHERE S2.,Sdept,=S1.,Sdept,AND S2.,Sname,=,刘晨；,用,NOTEXISTS,实现全称量词,(,难点)：,SQL,语言中没有全称量词,，,可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(,x)P,(,x(,P)。,例,43,查询选修了全部课程的学生姓名。,SELECT,Sname,FROM Student WHERE NOT EXISTS,（,SELECT*FROM Course WHERE NOT EXISTS,(SELECT*FROM SC WHERE,Sno,=Student.,Sno,AND,Cno,=Course.,Cno,）；,用,NOTEXISTS,实现逻辑蕴函(难点)：,SQL,语言中没有蕴函逻辑运算，可以利用谓词演算将逻辑蕴函谓词等价转换为：,p,q,pq。,例44查询至少选修了学生95002选修的全部课程的学生号码。,解题思路：,逻辑蕴函表达：,查询学号为,x,的学生，对所有的课程,y，,只要95002学生选修了课程,y，,则,x,也选修了,y。,形式化表示：,用,P,表示谓词“学生95002选修了课程,y”，q,表示谓词“学生,x,选修了课程,y”，,则上述查询为:(,y)p,q。,变换:,(,y)p,q,(,y(,(p,q),(,y(,(,p q),y(p,q),变换后语义：,不存在这样的课程,y，,学生,95002,选修了,y，,而学生,x,没有选。,用,NOT EXISTS,谓词表示：,SELECT DISTINCT,Sno,FROM SC SCX WHERE NOT EXISTS,(SELECT*FROM SC SCY WHERE SCY.,Sno,=95002 AND,NOT EXISTS (SELECT*FROM SC SCZ,WHERE SCZ.,Sno,=SCX.,Sno,AND SCZ.,Cno,=SCY.,Cno,)；,集合查询：,标准,SQL,直接支持并操作，而一般商用数据库支持并操作、交操作和差操作。,并操作,(,UNION):,参加,UNION,操作的各结果表的列数必须相同；对应项的数据类型也必须相同。一般格式为：,UNION,例45查询计算机系的学生及年龄不大于19岁的学生。,方法一：,SELECT*FROM Student WHERE,Sdept,=CS,UNION,SELECT*FROM Student WHERE Sage=19；,方法二：,SELECT DISTINCT *FROM Student,WHERE,Sdept,=CS OR Sage=19；,95001 李勇 信息系统 88,WHERE Student.,例35查询选修2号课程且成绩在90分以上所有学生的学号、姓名。,95004 张立 男 19 IS,Sno,student.,SELECT FIRST.,Sno，Sname，Ssex，Sage，Sdept，Cno，Grade,IS,外连接：一般SQL语法,Cname=信息系统；,95002 刘晨 女 19 IS 95002 2 90,95001 李勇 男 20 CS 95001 2 85,WHERE Cno=1 OR Cno=2；,WHERE(SELECT Sdept FROM Student,外连接：一般SQL语法,例46查询选修了课程1或者选修了课程2的学生。,方法一：,SELECT Sno FROM SC WHERE Cno=1,UNION,SELECT Sno FROM SC WHERE Cno=2；,方法二：,SELECT DISTINCT Sno FROM SC,WHERE Cno=1 OR Cno=2；,设数据库中有一教师表Teacher(Tno,Tname,.)。查询学校中所有师生的姓名。,SELECT Sname FROM Student,UNION,SELECT Tname FROM Teacher;,交操作,(,INTERSECT)：,标准,SQL,中没有提供集合交操作，但可用其他方法间接实现。,例,47查询计算机系的学生与年龄不大于,19,岁的学生的交集(实际上就是查询计算机系中年龄不大于,19,岁的学生)。,SELECT*FROM Student WHERE,Sdept,=CS AND Sage19；,对集合操作结果的排序：,ORDER BY,子句用数字指定排序属性，只用于对最终查询结果排序，不能对中间结果排序。任何情况下,，,ORDER BY,子句只能出现在最后。,如：错误写法,SELECT *FROM Student WHERE,Sdept,=CS ORDER BY,Sno,UNION,SELECT*FROM Student WHERE Sage=19 ORDER BY,Sno,；,正确写法,SELECT*FROM Student WHERE,Sdept,=CS,UNION,SELECT*FROM Student WHERE Sage=19 ORDER BY 1；,new,SELECT,语句的一般格式,SELECT,ALL|DISTINCT ,别名,别名,FROM,别名,别名,WHERE,GROUP BY,.,HAVING,ORDER BY,ASC|DESC,ASC|DESC,小结,等值与非等值连接查询,自身连接查询,外连接：一般,SQL,语法,SELECT Student.,Sno,，,Sname,，,Ssex,，Sage，,Sdept,，,Cno,，Grade,FROM Student,SC where Student.,Sno,=SC.,Sno,(*),；,SQL Server,的语法,SELECT Student.,Sno,，,Sname,，,Ssex,，Sage，,Sdept,，,Cno,，Grade,FROM Student left outer join SC on Student.,Sno,=SC.,Sno,；,这里,join,前还可以是,right outer、full outer、inner、cross(,笛卡尔积)。,复合连接查询,嵌套查询,集合查询,作业,P148,第4、5题。,预习3.4 3.6。,下课了。,休息。,追,