1 / 58

上海第二工业大学 计算机与信息学院

上海第二工业大学 计算机与信息学院. 数据库系统概论 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. 列名”. 例:.

palila
Download Presentation

上海第二工业大学 计算机与信息学院

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 上海第二工业大学 计算机与信息学院 数据库系统概论 An Introduction to Database System 第三章 关系数据库标准语言SQL (续1) An Introduction to Database System

  2. 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.3 连接查询 • Select查询的From后面可以包含多个表,表示查询数据来源于多个表,通常需要使用连接条件把多个表连接起来 • 连接条件的最常见的形式是“表名1.列名1=表名2.列名” An Introduction to Database System

  4. 例: 学生表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

  5. 连接操作的执行过程 • 嵌套循环法(NESTED-LOOP) • 首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。 • 表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组。 • 重复上述操作,直到表1中的全部元组都处理完毕 An Introduction to Database System

  6. 排序合并法(SORT-MERGE) 常用于=连接 • 首先按连接属性对表1和表2排序 • 对表1的第一个元组,从头开始扫描表2,顺序查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时,对表2的查询不再继续 An Introduction to Database System

  7. 排序合并法 • 找到表1的第二条元组,然后从刚才的中断点处继续顺序扫描表2,查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时,对表2的查询不再继续 • 重复上述操作,直到表1或表2中的全部元组都处理完毕为止 An Introduction to Database System

  8. 索引连接(INDEX-JOIN) • 对表2按连接字段建立索引 • 对表1中的每个元组,依次根据其连接字段值查询表2的索引,从中找到满足条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组 An Introduction to Database System

  9. 连接查询 (续) SQL中连接查询的主要类型 • 广义笛卡尔积 • 等值连接(含自然连接) • 非等值连接查询 • 自身连接查询 • 外连接查询 • 复合条件连接查询 An Introduction to Database System

  10. 一、广义笛卡尔积 • 无连接条件 例如:SELECT * FROM Student,Classe 或:select * from student cross join class • 较少使用,无连接条件意味着其查询结果是全两个表行的全组合,没有包含两个表之间的任何关系信息 An Introduction to Database System

  11. 二、等值与非等值连接查询 等值连接、自然连接、非等值连接 [例] 查询每个学生及其所在班级 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

  12. 等值连接 • 连接运算符为 = 的连接操作 • [<表名1>.]<列名1> = [<表名2>.]<列名2> • 任何子句中引用表1和表2中同名属性时,都必须加表名前缀。引用唯一的属性名时可以省略表名前缀。 • 可以为每个表起一个别名,引用时可简单些: select * from student a,classe b where a.classno=b.classno An Introduction to Database System

  13. 自然连接 • 等值连接的一种特殊情况,把目标列中重复的属性列去掉。 • 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

  14. 非等值连接查询 连接运算符 不是 = 的连接操作 [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2> 比较运算符:>、<、>=、<=、!= [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> An Introduction to Database System

  15. 三、自身连接 • 一个表与其自己进行连接,称为表的自连接 • 必须给表起别名以示区别 • 由于所有属性名都是同名属性,因此必须使用别名前缀 An Introduction to Database System

  16. 自身连接(续) [例] 查询每一门课的名称以及其先修课的名称 SELECT a.Cname,b.Cname FROM Course a,Course b WHERE a.Cpno =b.Cno; An Introduction to Database System

  17. 四、外连接(Outer Join) • 外连接与普通连接的区别 • 普通连接操作只输出满足连接条件的元组 • 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出 An Introduction to Database System

  18. 左外连接 • 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

  19. 右外连接 • 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

  20. 五、复合条件连接 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

  21. 多表连接 [例] 查询每个学生的学号、姓名、选修的课程名及成绩。 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

  22. 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

  23. 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

  24. 构造嵌套查询 将第一步查询嵌入到第二步查询的条件中 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

  25. 嵌套查询(子查询) • 嵌套查询概述 • 一个SELECT-FROM-WHERE语句称为一个查询块 • 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 An Introduction to Database System

  26. 嵌套查询(续) • 子查询的限制 • 不能使用ORDER BY子句 • 层层嵌套方式反映了 SQL语言的结构化 • 有些嵌套查询可以用连接运算替代 An Introduction to Database System

  27. 与上述查询语句结果等价的查询语句: 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

  28. 引出子查询的谓词 • 带有IN谓词的子查询 • 带有比较运算符的子查询 • 带有ANY或ALL谓词的子查询 • 带有EXISTS谓词的子查询 An Introduction to Database System

  29. 一、带有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

  30. 多层嵌套: [例]查询选修了课程名为“信息系统”的学生学号和姓名 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

  31. 二、带有比较运算符的子查询 • 当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!=或< >)。 • 与ANY或ALL谓词配合使用 An Introduction to Database System

  32. 带有比较运算符的子查询(续) 例:假设一个学生只可能在一个系学习,并且必须属于一个系,则与刘晨同一个系的学生信息查询可用“=”代替“IN” : SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept = (SELECT SdeptFROM StudentWHERE Sname= ‘ 刘晨 ’); • 子查询一定要跟在比较符之后,且子查询必须只返回至多一个值(或没有值)。 An Introduction to Database System

  33. 三、带有ANY或ALL谓词的子查询 谓词语义 • ANY/SOME:任意一个值,建议用SOME,其含义更容易和ALL区分 • ALL:所有值 An Introduction to Database System

  34. 带有ANY或ALL谓词的子查询(续) 需要配合使用比较运算符 > ANY (SOME) 大于子查询结果中的某个值 >ALL 大于子查询结果中的所有值 < ANY (SOME) 小于子查询结果中的某个值 < ALL 小于子查询结果中的所有值 >= ANY(SOME) 大于等于子查询结果中的某个值 >= ALL 大于等于子查询结果中的所有值 <= ANY (SOME) 小于等于子查询结果中的某个值 <= ALL 小于等于子查询结果中的所有值 = ANY (SOME) 等于子查询结果中的某个值 =ALL 等于子查询结果中的所有值 !=(或<>)ANY 不等于子查询结果中的某个值 !=(或<>)ALL 不等于子查询结果中的任何一个值 An Introduction to Database System

  35. 带有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

  36. = <>或!= < <= > >= 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

  37. 带有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

  38. 带有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

  39. 例:查询课程号为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

  40. 例:查询课程号为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

  41. 例:查询课程平均成绩最高的学生学号 • 方法一: 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

  42. 四、带有EXISTS谓词的子查询 • 带有EXISTS的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。 • 若内层查询结果非空,则返回真值 • 若内层查询结果为空,则返回假值 • 由EXISTS引出的子查询,其目标列表达式通常都用* ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义 An Introduction to Database System

  43. 带有EXISTS谓词的子查询(续) [例] 查询所有选修了1号课程的学生姓名。 SELECT Sname FROM Student a WHERE EXISTS (SELECT * FROM SC WHERE Sno=a.Sno AND Cno=‘1’) An Introduction to Database System

  44. 带有EXISTS谓词的子查询(续) [例] 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1'); An Introduction to Database System

  45. 带有EXISTS谓词的子查询(续) • 带EXISTS或NOT EXISTS谓词的子查询不一定能被其他形式的子查询等价替换 • 反之,所有带IN谓词、比较运算符、ANY和ALL谓词的子查询通常都能用带EXISTS谓词的子查询等价替换。 An Introduction to Database System

  46. 用EXISTS实现关系的除运算 [例] 查询选修了全部课程的学生姓名。 • 其相反的条件是至少有一门课没选,即存在一门课,查询结果中的学生没有选。 • 所以要解决的基础查询是:查询没有被查询结果中学生选择的课程: • 使用IN select * from course where cno not in (select cno from sc where sno=student.sno) An Introduction to Database System

  47. 使用EXISTS:没有被某学生选修的课程 SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno) An Introduction to Database System

  48. 带有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

  49. 带有EXISTS谓词的子查询(续) [例] 查询至少选修了学生95002选修的全部课程的学生的学号和姓名,包括95002号学生。 An Introduction to Database System

  50. 带有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

More Related