第5.3SQL数据查询.pptx

《第5.3SQL数据查询.pptx》由会员分享，可在线阅读，更多相关《第5.3SQL数据查询.pptx（101页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、课程主讲人：第5.3SQL数据查询 5.3 SQL数据查询 查询是数据库操作中最常用的操作。对于已经定义的表和视图，用户可以通过查询操作得到所需要的信息。SQL语言的核心就是查询，它提供了功能强大的SELECT语句来完成各种数据的查询。 3.3 查 询 5.3.1 概述5.3.2 单表查询5.3.3 聚合和分组5.3.4 连接查询5.3.5 集合运算5.3.6 嵌套查询 5.3.1 SQL概述 概述 语句格式SELECT ALL|DISTINCT ， FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC ； 示例数据库 学生-课程数据库 学生表：Stud

2、ent(Sno，Sname，Ssex，Sage，Sdept) 课程表：Course(Cno，Cname，Cpno，Ccredit) 学生选课表：SC(Sno，Cno，Grade) 3.3 查询 5.3.1 概述5.3.2 单表查询5.3.3 聚合和分组5.3.4 连接查询5.3.5 集合运算5.3.6 嵌套查询 表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列（投影）二、选择表中的若干元组（选择）三、对查询结果排序 查询指定列 查询全部列例3 查询全体学生的详细记录。 SELECT Sno, Sname, Ssex, Sage, Sdept FROM Student； 或

3、SELECT * FROM Student； 3. 查询经过计算的值 SELECT子句的为表达式 算术表达式 字符串常量 函数 列别名等 3. 查询经过计算的值例4 查询全体学生的姓名及其出生年份。SELECT Sname，year(now()-SageFROM Student； 输出结果： Sname Expr1001 - - 李勇 1998 刘晨 1999 王名 2000 张立 2000 year year是日期截取函数，月month,日daynow（）当前日期时间函数 2020-08-08 12:50:12:12year(now() 取当前年 3. 查询经过计算的值使用列别名改变查询结果

4、的列标题：SELECT Sname, year(now()-Sage as BirthYearFROM Student； 输出结果： Sname BirthYear - - 李勇 1998 刘晨 1999 王名 2000 张立 2000 二、选择表中的若干元组 消除取值重复的行 查询满足条件的元组 1. 消除取值重复的行 在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 Sno Cno Grade - - - 2019000001 1 92 2019000001 2 85 2019000001 3 88 2019000002 2 90 2019000002 3 80 ALL

5、与 DISTINCT 例5 查询选修了课程的学生学号。(1) SELECT Sno FROM SC;或 SELECT ALL Sno FROM SC; (默认 ALL)结果： Sno - 2019000001 2019000001 2019000001 2019000002 2019000002 (2) SELECT DISTINCT Sno FROM SC; 结果： Sno - 2019000001 2019000002 注意 DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECT DISTINCT Cno，DISTINCT GradeFROM SC;正确

6、的写法 SELECT DISTINCT Cno，Grade FROM SC; 2. 查询满足条件的元组 带有where子句的查询常用的查询条件查询条件查询条件 谓谓 词词比较=,=,=算术运算+ - * /确定范围Between And ,Not Between And确定集合IN , NOT IN字符匹配Like , Not Like空值IS NULL ,IS NOT NULL多重条件AND , OR (1) 比较大小在WHERE子句的中使用比较运算符 =，=，=，!= 或 ，!，!， 逻辑运算符NOT + 比较运算符例6 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Snam

7、e，Sage FROM Student WHERE Sage = 20； (2) 确定范围 使用谓词：BETWEEN AND NOT BETWEEN AND 例7 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23； 例8 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23； (3) 确定集合使用谓词：IN ,

8、 NOT IN ：用逗号分隔的一组取值例9 查询信息系（IS）、数学系（MA）和计 算机科学系（CS）学生的姓名和性别。SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS ); (3) 确定集合例10 查询既不是信息系、数学系，也不是计算 机科学系的学生的姓名和性别。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，MA，CS ); (4) 字符串匹配 NOT LIKE ESCAPE ：固定字符串或含通配符的字符串 ESCAPE 匹配串中本身含有通配符的用转义字符方式 通配符u%

9、 (百分号) 代表任意长度（长度可以为0）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串。u_ (下横线) 代表任意单个字符 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。 ESCAPE 短语当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE 短语对通配符进行转义。如：like _ _% escape 在access中查找通配符用 1) 匹配串为固定字符串 例11 查询学号为2019000001 的学生的详细情况。 SELECT * FROM Student WHERE Sn

10、o LIKE 2019000001 ；等价于： SELECT * FROM Student WHERE Sno = 2019000001 ； 2) 匹配串为含通配符的字符串例12 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%； 匹配模板为含通配符的字符串（续）例13 查询姓欧阳且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_； 匹配串为含通配符的字符串（续）例14 查询名字中第2个字为阳字的学生的姓名和学号。 SELE

11、CT Sname，Sno FROM Student WHERE Sname LIKE _阳%； 匹配串为含通配符的字符串（续）例15 查询所有不姓刘的学生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 刘%； 3) 使用转义字符将通配符转义为普通字符 例16 查询DB_Design课程的课程号和学分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE ; 使用换码字符将通配符转义为普通字符(续)例17 查询以DB_开头，且倒数第3个字符为 i的

12、课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ； (5) 涉及空值的查询 使用谓词 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替例18 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL； 例19 查所有有成绩的学生学号和课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL； (

13、6) 多重条件查询用逻辑运算符AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级可用来实现多种其他谓词 NOT IN NOT BETWEEN AND 例20 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=20 AND Sage3； 例31 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数 SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3; 使用HAVIN

14、G短语筛选最终输出结果 只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。 3.3 查 询 5.3.1 概述5.3.2 单表查询5.3.3 聚合和分组5.3.4 连接查询5.3.5 集合运算5.3.6 嵌套查询 3.3接查询 同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词 一般格式： . . 比较运算符：=、=、=、!= 连接查询 （续）SQL中连接查询的主要类型 广义笛卡尔积 等值连接(含自然连接) 非等值连接查

15、询 复合条件连接查询 一、广义笛卡尔积 不带连接谓词的连接 很少使用例： SELECT Student.* , SC.* FROM Student, SC； 二、等值与非等值连接查询 等值连接、自然连接、非等值连接例32 查询学生及其选修课程的情况 等价于： SELECT * FROM Student，SC WHERE Student.Sno = SC.Sno；SELECT Student.*，SC.*FROM Student INNER JOIN SC ON S.Sno=SC.Sno; 等值连接 连接运算符为 = 的连接操作 . = . 任何子句中引用表1和表2中同名属性时，都必须加表名前缀

16、。引用唯一属性名时可以加也可以省略表名前缀。 自然连接 等值连接的一种特殊情况，把目标列中重复的属性列去掉。例33 查询学生及其选修课程的情况 （用自然连接完成）。 SELECT Student.Sno，Sname，Ssex，Sage， Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno； 非等值连接查询连接运算符 不是 = 的连接操作 . .比较运算符：、=、 90； /* 其他限定条件 */TOP 谓词例例35：查询成绩前五名的学生姓名，课程名及成绩。：查询成绩前五名的学生姓名，课程名及成绩。SELECT TOP 5 SNA

17、ME, CNAME, GRADEFROM Student, SC, CourseWHERE Student.SNO=SC.SNO AND SC.CNO=Course.CNOORDER BY GRADE DESC; 3.3 查 询 5.3.1 概述5.3.2 单表查询5.3.3 聚合和分组5.3.4 连接查询5.3.5 集合运算5.3.6 嵌套查询 3.3.4 集合查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT) MYSQL不支持差操作(EXCEPT) MYSQL不支持 1并操作 形式 UNION 例36 查

18、询计算机科学系的学生及年龄不大于19岁的学生。方法一： (SELECT * FROM Student WHERE Sdept= CS) UNION (SELECT * FROM Student WHERE Sage=19)； 方法二： SELECT * FROM Student WHERE Sdept= CS OR Sage=19； 例37 查询选修了课程1或者选修了课程2的学生。方法一： (SELECT Sno FROM SC WHERE Cno= 1 ) UNION (SELECT Sno FROM SC WHERE Cno= 2 )； 方法二： SELECT DISTINCT Sno F

19、ROM SC WHERE Cno= 1 OR Cno= 2 ； 例38 设数据库中有一教师表Teacher(Tno, Tname,.)。查询学校中所有师生的姓名。 (SELECT Sname FROM Student ) UNION (SELECT Tname FROM Teacher); 2交操作 标准SQL中没有提供集合交操作，但可用其他方法间接实现。 有些DBMS支持交运算： 形式 INTERSECT 例39 查询既选修了课程1又选修了课程2的学生学号。 (SELECT SC.SNO FROM SC WHERE SC.CNO=1) INTERSECT (SELECT SC.SNO FRO

20、M SC WHERE SC.CNO=2); 3差操作 标准SQL中没有提供集合差操作，但可用其他方法间接实现。 有些DBMS支持差运算： 形式 EXCEPT 3差操作例40 查询只选修课程1而没有选修课程2的学生学号。 (SELECT SC.SNO FROM SC WHERE SC.CNO= 1) EXCEPT (SELECT SC.SNO FROM SC WHERE SC.CNO= 2); 3.3 查 询 5.3.1 概述5.3.2 单表查询5.3.3 聚合和分组5.3.4 连接查询5.3.5 集合运算5.3.6 嵌套查询 3.3.查询 嵌套查询概述 一个SELECT-FROM-WHERE语

21、句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 引出子查询的谓词1.等值=子查询2.带有IN谓词的子查询3.带有比较运算符的子查询4.带有ANY或ALL谓词的子查询5.带有EXISTS谓词的子查询 比较运算符的子查询（续）例41 查询与“刘晨”在同一个系学习的学生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = ( SELECT Sdept FROM Student WHERE Sname= 刘晨 )； 构造嵌套查询 例42 查询既选修了课程1又选修了课程2的学生学号。 SELE

22、CT Sno FROM SC WHERE Cno= 1 AND Sno IN (SELECT Sno FROM SC WHERE Cno= 2 )； 带有IN谓词的子查询（续）例43查询选修了课程名为“信息系统”的学生学号和姓名 (SELECT Cno FROM Course WHERE Cname= 信息系统); (SELECT Sno FROM SC WHERE Cno INSELECT Sno，Sname FROM Student WHERE Sno IN 带有IN谓词的子查询（续） 用连接查询 SELECT Sno，Sname FROM Student，SC，Course WHERE

23、Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Cname=信息系统； 二 3.带有比较运算符的子查询带有比较运算符的子查询 当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=或）。 与ANY或ALL谓词配合使用 有ANY或ALL谓词的子查询谓词语义nANY：任意一个值nALL：所有值 带有ANY或ALL谓词的子查询（续）需要配合使用比较运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值 ANY小于子查询结果中的某个值 = ANY大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值= ANY小于等

24、于子查询结果中的某个值 = ALL小于等于子查询结果中的所有值= ANY等于子查询结果中的某个值 =ALL等于子查询结果中的所有值（通常无实际意义）!=（或）ANY 不等于子查询结果中的某个值!=（或）ALL 不等于子查询结果中的任何一个值 带有ANY或ALL谓词的子查询（续）例44 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; 结果Sname Sage 王敏 1

25、8执行过程1. DBMS执行此查询时，首先处理子查询，找出 IS系中所有学生的年龄，构成一个集合(19，18)2. 处理父查询，找所有不是IS系且年龄小于 19 或 18的学生 带有ANY或ALL谓词的子查询（续）u用聚合函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数 带有ANY或ALL谓词的子查询（续）例45：用聚合函数实现例44 SELECT Sname，Sage FROM Student WHERE Sage (SELECT MAX(Sage) FROM Student WHERE Sdept= IS ) AND Sdept IS ; 带有ANY或AL

26、L谓词的子查询（续）例46 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词 SELECT Sname，Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; 带有ANY或ALL谓词的子查询（续） 方法二：用聚合函数 SELECT Sname，Sage FROM Student WHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept= IS ) AND Sdept IS ; 带有EXISTS

27、谓词的子查询 EXISTS谓词存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值 NOT EXISTS谓词若内层查询结果非空，则返回假值若内层查询结果为空，则返回真值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 例47 查询所有选修了1号课程的学生姓名。n用嵌套查询 SELECT Sname FROM Student WHERE EXISTS /*相关子查询*/ (SELECT * FROM SC WHE

28、RE SC.Sno=Student.Sno AND Cno=1)； 思路分析：u 本查询涉及Student和SC关系。u 在Student中依次取每个元组的Sno值，用此值去检查SC关系。u 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=1，则取此Student.Sname送入结果关系。 也可以用连接运算实现SELECT SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno= 1; 例48 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXI

29、STS (SELECT * FROM SC WHERE SC.Sno=Student.Sno AND Cno=1)；此例用连接运算难于实现 带有EXISTS谓词的子查询(续）不同形式的查询间的替换一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 思路： 构建某个学生所选修的课程集合，记为A； 构建全部的课程集合，记为B； 某个学生选修了全部课程，可以表示为： AB 100SELECT SNAMEFROM StudentWHERE NOT EXISTS ( SELEC

30、T CNO FROM Course WHERE NOT EXISTS ( SELECT DISTINCT CNO FROM SC WHERE SC.CNO=Course.CNO and SC.SNO=Student.SNO)除法表示方法除法表示方法2用NOT EXISTS 表示EXCEPT 例例5050查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno);