580 likes | 727 Views
上海第二工业大å¦ã€€è®¡ç®—机与信æ¯å¦é™¢. æ•°æ®åº“系统概论 An Introduction to Database System ç¬¬ä¸‰ç« å…³ç³»æ•°æ®åº“æ ‡å‡†è¯è¨€ SQL ( ç» 1 ). 3.3 查 询. 3.3.1 概述 3.3.2 å•表查询 3.3.3 连接查询 3.3.4 嵌套查询 3.3.5 é›†åˆæŸ¥è¯¢ 3.3.6 å°ç»“. 3.3.3 连接查询. Select 查询的 From åŽé¢å¯ä»¥åŒ…å«å¤šä¸ªè¡¨ï¼Œè¡¨ç¤ºæŸ¥è¯¢æ•°æ®æ¥æºäºŽå¤šä¸ªè¡¨ï¼Œé€šå¸¸éœ€è¦ä½¿ç”¨è¿žæŽ¥æ¡ä»¶æŠŠå¤šä¸ªè¡¨è¿žæŽ¥èµ·æ¥ 连接æ¡ä»¶çš„æœ€å¸¸è§çš„形弿˜¯â€œè¡¨å 1. 列å 1= 表å 2. 列åâ€. 例:.
E N D
上海第二工业大学 计算机与信息学院 数据库系统概论 An Introduction to Database System 第三章 关系数据库标准语言SQL (续1) An Introduction to Database System
3.3 查 询 • 3.3.1 概述 • 3.3.2 单表查询 • 3.3.3 连接查询 • 3.3.4 嵌套查询 • 3.3.5 集合查询 • 3.3.6 小结 An Introduction to Database System
3.3.3 连接查询 • Select查询的From后面可以包含多个表,表示查询数据来源于多个表,通常需要使用连接条件把多个表连接起来 • 连接条件的最常见的形式是“表名1.列名1=表名2.列名” An Introduction to Database System
例: 学生表student 级表class 学号 姓名 班号 班号 班名 sno sname classno classno clname S01 王海燕 C01 C01 07计科S1 S02 李冰 C01 C02 06软工A1 S03 黄涛 C02 要求查询: 学号 姓名 班名 S01 王海燕 07计科S1 S02 李冰 07计科S1 S03 黄涛 06软工A1 要求查询的内容的数据来源于两个表,连接条件是:students.classno=classes.classno An Introduction to Database System
连接操作的执行过程 • 嵌套循环法(NESTED-LOOP) • 首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。 • 表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组。 • 重复上述操作,直到表1中的全部元组都处理完毕 An Introduction to Database System
排序合并法(SORT-MERGE) 常用于=连接 • 首先按连接属性对表1和表2排序 • 对表1的第一个元组,从头开始扫描表2,顺序查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时,对表2的查询不再继续 An Introduction to Database System
排序合并法 • 找到表1的第二条元组,然后从刚才的中断点处继续顺序扫描表2,查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时,对表2的查询不再继续 • 重复上述操作,直到表1或表2中的全部元组都处理完毕为止 An Introduction to Database System
索引连接(INDEX-JOIN) • 对表2按连接字段建立索引 • 对表1中的每个元组,依次根据其连接字段值查询表2的索引,从中找到满足条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组 An Introduction to Database System
连接查询 (续) SQL中连接查询的主要类型 • 广义笛卡尔积 • 等值连接(含自然连接) • 非等值连接查询 • 自身连接查询 • 外连接查询 • 复合条件连接查询 An Introduction to Database System
一、广义笛卡尔积 • 无连接条件 例如:SELECT * FROM Student,Classe 或:select * from student cross join class • 较少使用,无连接条件意味着其查询结果是全两个表行的全组合,没有包含两个表之间的任何关系信息 An Introduction to Database System
二、等值与非等值连接查询 等值连接、自然连接、非等值连接 [例] 查询每个学生及其所在班级 SELECT * FROM Student join Class on Student.classno = Class.Classno 或把连接条件放在where中: SELECT * FROM Student,Class WHERE Student.classno = Class.Classno; An Introduction to Database System
等值连接 • 连接运算符为 = 的连接操作 • [<表名1>.]<列名1> = [<表名2>.]<列名2> • 任何子句中引用表1和表2中同名属性时,都必须加表名前缀。引用唯一的属性名时可以省略表名前缀。 • 可以为每个表起一个别名,引用时可简单些: select * from student a,classe b where a.classno=b.classno An Introduction to Database System
自然连接 • 等值连接的一种特殊情况,把目标列中重复的属性列去掉。 • Select中没有自然连接功能,只能通过选列做到这一点 select a.cno,a.cname,b.classno,b.clname from student a,class b where a.classno=b.classno An Introduction to Database System
非等值连接查询 连接运算符 不是 = 的连接操作 [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2> 比较运算符:>、<、>=、<=、!= [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> An Introduction to Database System
三、自身连接 • 一个表与其自己进行连接,称为表的自连接 • 必须给表起别名以示区别 • 由于所有属性名都是同名属性,因此必须使用别名前缀 An Introduction to Database System
自身连接(续) [例] 查询每一门课的名称以及其先修课的名称 SELECT a.Cname,b.Cname FROM Course a,Course b WHERE a.Cpno =b.Cno; An Introduction to Database System
四、外连接(Outer Join) • 外连接与普通连接的区别 • 普通连接操作只输出满足连接条件的元组 • 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出 An Introduction to Database System
左外连接 • Join中处于左边表中的行全部作为查询结果输出 [例 ] 查询每个学生学号、姓名、选修课程号及成绩的情况,查询结果要包括没有选修课程的学生--用外连接操作 SELECT a.Sno,a.Sname,b.Cno,b.Grade FROM Student a LEFT JOIN SC b on a.Sno = b.Sno SQL Server使用where条件的连接也支持左外连接,上述等价的语句为: SELECT a.Sno,a.Sname,b.Cno,b.Grade FROM Student a, SC b WHERE a.Sno *= b.Sno An Introduction to Database System
右外连接 • Join中处于右边表中的行全部作为查询结果输出 • 上述查询语句中把student和sc交换位置,要实现相同的查询,就可以使用右外连接 SELECT a.Sno,a.Sname,b.Cno,b.Grade FROM SC b RIGHT JOIN SC a on a.Sno = b.Sno • SQL Server中使用WHERE不支持右外连接 An Introduction to Database System
五、复合条件连接 WHERE子句中含多个连接条件时,称为复合条件连接 [例]查询选修2号课程且成绩在90分以上的所有学生的 学号、姓名 SELECT Student.Sno, student.Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 连接谓词*/ SC.Cno= ‘ 2 ’ AND /* 其他限定条件 */ SC.Grade > 90 /* 其他限定条件 */ An Introduction to Database System
多表连接 [例] 查询每个学生的学号、姓名、选修的课程名及成绩。 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 An Introduction to Database System
3.3 查 询 • 3.3.1 概述 • 3.3.2 单表查询 • 3.3.3 连接查询 • 3.3.4 嵌套查询 • 3.3.5 集合查询 • 3.3.6 小结 An Introduction to Database System
3.3.4 嵌套查询(子查询) [例] 查询与“刘晨”在同一个系学习的学生的学号、姓名和所在系。 此查询要求可以分步来完成: ① 确定“刘晨”所在系名: SELECT Sdept FROM Student WHERE Sname= ' 刘晨 '; 结果为: IS ② 查找所有在IS系学习的学生。 SELECT Sno,Sname,Sdept FROM Student WHERE Sdept= ' IS '; An Introduction to Database System
构造嵌套查询 将第一步查询嵌入到第二步查询的条件中 SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= ‘ 刘晨 ’) 或: SELECT Sno,Sname,Sdept FROM Student WHERE Sdept=(SELECT Sdept FROM Student WHERE Sname= ‘ 刘晨 ’) //使用“=”必须确保子查询结果为单个值 An Introduction to Database System
嵌套查询(子查询) • 嵌套查询概述 • 一个SELECT-FROM-WHERE语句称为一个查询块 • 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 An Introduction to Database System
嵌套查询(续) • 子查询的限制 • 不能使用ORDER BY子句 • 层层嵌套方式反映了 SQL语言的结构化 • 有些嵌套查询可以用连接运算替代 An Introduction to Database System
与上述查询语句结果等价的查询语句: SELECT a.Sno,a.Sname,a.Sdept FROM Student a,Student b WHERE a.Sdept = b.Sdept AND b.Sname = '刘晨' An Introduction to Database System
引出子查询的谓词 • 带有IN谓词的子查询 • 带有比较运算符的子查询 • 带有ANY或ALL谓词的子查询 • 带有EXISTS谓词的子查询 An Introduction to Database System
一、带有IN谓词的子查询 [例]查询选修了课程学生学号和姓名 SELECT Sno,Sname FROM Student WHERE SNO IN (SELECT SNO FROM SC) [例]查询未选修任何课程学生学号和姓名 SELECT Sno,Sname FROM Student WHERE SNO NOT IN (SELECT SNO FROM SC) An Introduction to Database System
多层嵌套: [例]查询选修了课程名为“信息系统”的学生学号和姓名 SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname= ‘信息系统’)); An Introduction to Database System
二、带有比较运算符的子查询 • 当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!=或< >)。 • 与ANY或ALL谓词配合使用 An Introduction to Database System
带有比较运算符的子查询(续) 例:假设一个学生只可能在一个系学习,并且必须属于一个系,则与刘晨同一个系的学生信息查询可用“=”代替“IN” : SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept = (SELECT SdeptFROM StudentWHERE Sname= ‘ 刘晨 ’); • 子查询一定要跟在比较符之后,且子查询必须只返回至多一个值(或没有值)。 An Introduction to Database System
三、带有ANY或ALL谓词的子查询 谓词语义 • ANY/SOME:任意一个值,建议用SOME,其含义更容易和ALL区分 • ALL:所有值 An Introduction to Database System
带有ANY或ALL谓词的子查询(续) 需要配合使用比较运算符 > ANY (SOME) 大于子查询结果中的某个值 >ALL 大于子查询结果中的所有值 < ANY (SOME) 小于子查询结果中的某个值 < ALL 小于子查询结果中的所有值 >= ANY(SOME) 大于等于子查询结果中的某个值 >= ALL 大于等于子查询结果中的所有值 <= ANY (SOME) 小于等于子查询结果中的某个值 <= ALL 小于等于子查询结果中的所有值 = ANY (SOME) 等于子查询结果中的某个值 =ALL 等于子查询结果中的所有值 !=(或<>)ANY 不等于子查询结果中的某个值 !=(或<>)ALL 不等于子查询结果中的任何一个值 An Introduction to Database System
带有ANY或ALL谓词的子查询(续) [例] 查询其他系中比信息系某一个学生年龄小的学生姓名和年龄 SELECT Sname,Sage FROM Student WHERE Sage < SOME (SELECT Sage FROM Student WHERE Sdept= ' IS ') AND Sdept <> ' IS ' ; An Introduction to Database System
= <>或!= < <= > >= ANY IN -- <MAX <=MAX >MIN >= MIN ALL -- NOT IN <MIN <= MIN >MAX >= MAX 带有ANY或ALL谓词的子查询(续) • ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系 用集函数实现子查询通常比直接用ANY或ALL查询效率要高,因为前者通常能够减少比较次数 An Introduction to Database System
带有ANY或ALL谓词的子查询(续) [例]:查询其他系中比信息系某一个学生年龄小的学生姓名和年龄 SELECT Sname,Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept= ' IS ') AND Sdept <> ' IS ’; An Introduction to Database System
带有ANY或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 ’ An Introduction to Database System
例:查询课程号为2的课程成绩最高的学生学号例:查询课程号为2的课程成绩最高的学生学号 • 方法一: Select sno from sc where grade >= all (select grade from sc where cno=2) and cno=2(两个”cno=2”不可少) • 方法二: Select sno from sc where grade >= (select max(grade) from sc where cno=2) and cno=2 如要求查询该学生姓名,查询语句为: 把上列查询作为子查询: select sname from student where sno=(上列查询语句) 或以上查询语句中连接student: Select sname from student a join sc b on a.sno=b.sno where grade >= (select max(grade) from sc where cno=2) and cno=2 An Introduction to Database System
例:查询课程号为2的课程成绩不是最高的学生学号(去除最高成绩后的学生学号)例:查询课程号为2的课程成绩不是最高的学生学号(去除最高成绩后的学生学号) • 方法一: Select sno from sc where grade <some (select grade from sc where cno=2) and cno=2 • 方法二: Select sno from sc where grade < (select max(grade) from sc where cno=2) and cno=2 如要求查询该学生姓名,查询语句为: 把上列查询作为子查询: select sname from student where sno=(上列查询语句) 或以上查询语句中连接student: Select sname from student a join sc b on a.sno=b.sno where grade < (select max(grade) from sc where cno=2) and cno=2 • 对上一例仅作红色字体的修改 An Introduction to Database System
例:查询课程平均成绩最高的学生学号 • 方法一: Select sno from sc group by sno having avg(grade) >= all (select avg(grade) from sc group by sno) • 方法二不再有效,即执行下列语句将报错 Select sno from sc group by sno having avg(grade) >= (select max(avg(grade)) from sc group by sno) 解决方法(用子查询作为from后数据源): Select sno from sc group by sno having avg(grade) >= (select max(avggrade) from (select avg(grade) as avggrade from sc group by sno)a) 如要求查询该学生姓名,查询语句为: select sname from student where sno=(上列查询语句) An Introduction to Database System
四、带有EXISTS谓词的子查询 • 带有EXISTS的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。 • 若内层查询结果非空,则返回真值 • 若内层查询结果为空,则返回假值 • 由EXISTS引出的子查询,其目标列表达式通常都用* ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义 An Introduction to Database System
带有EXISTS谓词的子查询(续) [例] 查询所有选修了1号课程的学生姓名。 SELECT Sname FROM Student a WHERE EXISTS (SELECT * FROM SC WHERE Sno=a.Sno AND Cno=‘1’) An Introduction to Database System
带有EXISTS谓词的子查询(续) [例] 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1'); An Introduction to Database System
带有EXISTS谓词的子查询(续) • 带EXISTS或NOT EXISTS谓词的子查询不一定能被其他形式的子查询等价替换 • 反之,所有带IN谓词、比较运算符、ANY和ALL谓词的子查询通常都能用带EXISTS谓词的子查询等价替换。 An Introduction to Database System
用EXISTS实现关系的除运算 [例] 查询选修了全部课程的学生姓名。 • 其相反的条件是至少有一门课没选,即存在一门课,查询结果中的学生没有选。 • 所以要解决的基础查询是:查询没有被查询结果中学生选择的课程: • 使用IN select * from course where cno not in (select cno from sc where sno=student.sno) An Introduction to Database System
使用EXISTS:没有被某学生选修的课程 SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno) An Introduction to Database System
带有EXISTS谓词的子查询(续) • 最终查询语句为: SELECT Sname FROM student WHERE NOT EXISTS (SELECT * FROM course WHERE cno NOT IN (SELECT cno FROM sc WHERE sno=student.sno)) 或: SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno)); An Introduction to Database System
带有EXISTS谓词的子查询(续) [例] 查询至少选修了学生95002选修的全部课程的学生的学号和姓名,包括95002号学生。 An Introduction to Database System
带有EXISTS谓词的子查询(续) • 同样考虑相反条件是至少有一门课,‘95002’选了但查询结果中的学生没有选。所以关键要写出的子查询是:查询‘95002’选了但查询结果中的学生没有选的课程,该语句是: (SELECT * FROM course WHERE cno IN (SELECT cno FROM sc WHERE sno='95002') and cno NOT IN (SELECT cno FROM sc WHERE sno=student.sno)) • 所以最终的查询语句为: SELECT Sno,Sname FROM student WHERE NOT EXISTS (SELECT * FROM course WHERE cno IN (SELECT cno FROM sc WHERE sno='95002') and cno NOT IN (SELECT cno FROM sc WHERE sno=student.sno)) • 以上两个例题在关系代数中对应的为集合之间除法运算 An Introduction to Database System