1、An Introduction to Database System,厦门大学计算机科学系,关系数据库标准语言SQL,计算机软件技术,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.1 概述,语句格式 SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；,An Intro
2、duction to Database System,语句格式,SELECT子句：指定要显示的属性列 FROM子句：指定查询对象(基本表或视图) WHERE子句：指定查询条件GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。 HAVING短语：筛选出只有满足指定条件的组 ORDER BY子句：对查询结果表按指定列值的升序或降序排序,An Introduction to Database System,示例数据库,学生-课程数据库 学生表：Student(Sno，Sname，Ssex，Sage，Sdept) 课程表：Course(Cno，C
3、name，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade),An Introduction to Database System,选择（续）,(a),Student,An Introduction to Database System,选择（续）,(b),Course,An Introduction to Database System,选择（续）,(c),SC,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 小结,
4、An Introduction to Database System,3.3.2 单表查询,查询仅涉及一个表，是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组,An Introduction to Database System,查询指定列,例1 查询全体学生的学号与姓名。 SELECT Sno, Sname FROM Student;例2 查询全体学生的姓名、学号、所在系。 SELECT Sname，Sno，Sdept FROM Student；,An Introduction to Database System,
5、查询全部列,例3 查询全体学生的详细记录。,SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或 SELECT * FROM Student；,An Introduction to Database System,3. 查询经过计算的值,SELECT子句的为表达式 算术表达式 字符串常量 函数 列别名 等,An Introduction to Database System,3. 查询经过计算的值,例4 查全体学生的姓名及其出生年份。,SELECT Sname,2007-Sage FROM Student;,An Introduction to Da
6、tabase System,3. 查询经过计算的值,例5 查询全体学生的姓名、出生年份和所有系。在出生年份前面增加一个说明，在系名称后面增加一个“系”作为表示,SELECT Sname, 出生年份: , 2007-Sage, Sdept + 系 FROM Student;,An Introduction to Database System,例5.1 使用列别名改变查询结果的列标题,SELECT Sname 姓名, Year of Birth: 生日标识, 2007-Sage 生日, Sdept+系 系名 FROM Student;,An Introduction to Database Sy
7、stem,二、选择表中的若干元组,消除取值重复的行 查询满足条件的元组,An Introduction to Database System,1. 消除取值重复的行,在SELECT子句中使用DISTINCT短语,假设SC表中有下列数据 Sno Cno Grade- - -95001 1 9295001 2 8595001 3 8895002 2 9095002 3 80,An Introduction to Database System,ALL 与 DISTINCT,例6 查询选修了课程的学生学号。,(1) SELECT SnoFROM SC;或(默认 ALL)SELECT ALL SnoF
8、ROM SC;,(2) SELECT DISTINCT SnoFROM SC;,An Introduction to Database System,例题（续）,注意 DISTINCT短语的作用范围是所有目标列 例：查询选修课程的各种成绩 错误的写法 SELECT DISTINCT Cno，DISTINCT Grade FROM SC; 正确的写法SELECT DISTINCT Cno，GradeFROM SC;,An Introduction to Database System,2.查询满足条件的元组,WHERE子句常用的查询条件,An Introduction to Database S
9、ystem,(1) 比较大小,在WHERE子句的中使用比较运算符 =，=，!，!， 逻辑运算符NOT + 比较运算符 例8 查询所有年龄在20岁以下的学生姓名及其年龄。,SELECT Sname, Sage FROM Student WHERE Sage = 20；,An Introduction to Database System,(2) 确定范围,使用谓词 BETWEEN AND NOT BETWEEN AND 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。,SELECT Sname, Sdept, Sage FROM Student WHERE Sag
10、e BETWEEN 20 AND 23;,An Introduction to Database System,例题（续）,例11 查询年龄不在2023岁之间的学生姓名、系别和年龄。,SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23；,An Introduction to Database System,(3) 确定集合,使用谓词 IN , NOT IN ：用逗号分隔的一组取值 例12查询信息系（IS）和计算机科学系（CS）学生的姓名和性别。,SELECT Sname, Ssex FROM Student
11、 WHERE Sdept IN ( IS, CS );,An Introduction to Database System,(3) 确定集合,例13查询既不是信息系又不是计算 机科学系的学生的姓名和性别。,SELECT Sname，Ssex FROM StudentWHERE Sdept NOT IN ( IS，CS );,An Introduction to Database System,(4) 字符串匹配,NOT LIKE ESCAPE ：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用 = 运算符取代 LIKE 谓词用 != 或 运算符取代 NOT
12、LIKE 谓词,An Introduction to Database System,通配符,% (百分号) 代表任意长度（长度可以为0）的字符串 例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串 _ (下横线) 代表任意单个字符 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串,An Introduction to Database System,ESCAPE 短语：,当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE 短语对通配符进行转义。,An Introduction to Data
13、base System,例题,1) 匹配模板为固定字符串 例14 查询学号为95001的学生的详细情况。SELECT * FROM Student WHERE Sno LIKE 95001; 等价于： SELECT * FROM Student WHERE Sno = 95001；,An Introduction to Database System,例题（续）,2) 匹配模板为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。,SELECT Sname，Sno，SsexFROM StudentWHERE Sname LIKE 刘%；,An Introduction to Datab
14、ase System,例题（续）,匹配模板为含通配符的字符串（续）例16 查询姓“刘“且全名为三个汉字的学生的姓名。,SELECT SnameFROM StudentWHERE Sname LIKE 刘_；,An Introduction to Database System,例题（续）,匹配模板为含通配符的字符串（续）例17 查询名字中第2个字为“敏”字的学生的姓名和学号。,SELECT Sname，SnoFROM StudentWHERE Sname LIKE _敏%；,An Introduction to Database System,例题（续）,例18 查询所有不姓刘的学生姓名。SE
15、LECT Sname，Sno，SsexFROM StudentWHERE Sname NOT LIKE 刘%；,An Introduction to Database System,例题（续）,3) 使用换码字符将通配符转义为普通字符例19 查询课程名称中包含“面向对象_C+课程”的课程号和学分。,SELECT Cno, CcreditFROM CourseWHERE Cname LIKE %面向对象_C+% ESCAPE ,An Introduction to Database System,例题（续）,使用换码字符将通配符转义为普通字符(续) 例20 查询以“DB_“开头，且倒数第3个字符
16、为 i的课程的详细情况。,SELECT *FROM CourseWHERE Cname LIKE DB_%i_ _ ESCAPE ；,An Introduction to Database System,(5) 涉及空值的查询,使用谓词 IS NULL 或 IS NOT NULL“IS NULL” 不能用 “= NULL” 代替 例21 有些课没有先修课程。查询没有先修课程的课程名称。,An Introduction to Database System,(6) 多重条件查询,用逻辑运算符AND和 OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级 可用来实现多种其他谓词NOT
17、INNOT BETWEEN AND ,An Introduction to Database System,例题,例23 查询计算机系年龄在20岁以下的学生姓名。,SELECT SnameFROM StudentWHERE Sdept= CS AND Sage20;,An Introduction to Database System,改写例12,例12 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。 SELECT Sname,Ssex FROM Student WHERE Sdept IN ( IS, MA, CS ) 可改写为： SELECT Sname, Ss
18、ex FROM Student WHERE Sdept= IS OR Sdept= MA OR Sdept= CS ;,An Introduction to Database System,改写例10,例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23；可改写为：SELECT Sname，Sdept，Sage FROM StudentWHERE Sage=20 AND Sage=23；,An Introduction to Datab
19、ase System,三、对查询结果排序,使用ORDER BY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序 当排序列含空值时 NULL 作为最小值,An Introduction to Database System,对查询结果排序（续）,例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。,SELECT Sno,GradeFROM SCWHERE Cno= 3ORDER BY Grade DESC;,An Introduction to Database System,对查询结果排序（续）,例25 查询全体学生情况，查询结果按所在系的系号升序排
20、列，同一系中的学生按年龄降序排列。,SELECT *FROM StudentORDER BY Sdept, Sage DESC;,An Introduction to Database System,四、使用集函数,5类主要集函数 计数 COUNT（DISTINCT|ALL *） COUNT（DISTINCT|ALL ） 计算总和 SUM（DISTINCT|ALL ） 计算平均值 AVG（DISTINCT|ALL ）,An Introduction to Database System,使用集函数（续）,求最大值 MAX（DISTINCT|ALL ） 求最小值 MIN（DISTINCT|ALL
21、 ） DISTINCT短语：在计算时要取消指定列中的重复值 ALL短语：不取消重复值 ALL为缺省值,An Introduction to Database System,使用集函数 （续）,例26 查询学生总人数。例27 查询选修了课程的学生人数。,SELECT COUNT(DISTINCT Sno)FROM SC； 注：用DISTINCT以避免重复计算学生人数,SELECT COUNT(*)FROM Student；,An Introduction to Database System,使用集函数 （续）,例28 计算2号课程的学生平均成绩。例29 查询选修3号课程的学生最高分数。,SEL
22、ECT AVG(Grade)FROM SCWHERE Cno= 2 ；,SELECT MAX(Grade)FROM SCWHER Cno= 3；,An Introduction to Database System,五、对查询结果分组,使用GROUP BY子句分组 细化集函数的作用对象未对查询结果分组，集函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组,An Introduction to Database System,使用GROUP BY子句分组,例30 求各个课程号及相应的选课人数。,SELECT Cno，COUNT(Sno)FROM SCGROUP BY Cno；,A
23、n Introduction to Database System,对查询结果分组 （续）,GROUP BY子句的作用对象是查询的中间结果表 分组方法：按指定的一列或多列值分组，值相等的为一组 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数,An Introduction to Database System,使用HAVING短语筛选最终输出结果,例31 查询选修了2门及以上课程的学生学号。,SELECT SnoFROM SCGROUP BY SnoHAVING COUNT(*) 2；,An Introduction to Database System,例题
24、,例32 查询有2门及以上课程是85分以上的学生的学号及（85分以上的）课程数,SELECT Sno, COUNT(*)FROM SCWHERE Grade=85GROUP BY SnoHAVING COUNT(*)=2;,An Introduction to Database System,使用HAVING短语筛选最终输出结果,只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。,An Introduction to Database Sys
25、tem,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 连接查询,同时涉及多个表的查询称为连接查询 用来连接两个表的条件称为连接条件或连接谓词 一般格式： . .比较运算符：=、=、. BETWEEN . AND .,An Introduction to Database System,连接查询 （续）,连接字段 连接谓词中的列名称为连接字段 连接条件中的各连接字段类型必须是可比的，但不必是相同的,An Introduct
26、ion to Database System,连接操作的执行过程,嵌套循环法(NESTED-LOOP) 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。 重复上述操作，直到表1中的全部元组都处理完毕,An Introduction to Database System,排序合并法(SORT-MERGE),常用于=连接 首先按连接属性对表1和
27、表2排序 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续,An Introduction to Database System,排序合并法,找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续 重复上述操作，直到表1或表2中的全部元组都处理完毕为止,An Introduction t
28、o Database System,索引连接(INDEX-JOIN),对表2按连接字段建立索引 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组,An Introduction to Database System,连接查询 （续）,SQL中连接查询的主要类型广义笛卡尔积等值连接(含自然连接)非等值连接查询自身连接查询外连接查询复合条件连接查询,An Introduction to Database System,一、广义笛卡尔积,不带连接谓词的连接很少使用 例：SELECT Student.*
29、, SC.*FROM Student, SC,An Introduction to Database System,二、等值与非等值连接查询,等值连接、自然连接、非等值连接 例32 查询每个学生及其选修课程的情况。,SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；,An Introduction to Database System,等值连接,连接运算符为 = 的连接操作. = . 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。,An Introduction
30、to Database System,自然连接,等值连接的一种特殊情况，把目标列中重复的属性列去掉。 例33 对例32用自然连接完成。SELECT Student.Sno，Sname，Ssex，Sage， Sdept，Cno，GradeFROM Student，SCWHERE Student.Sno = SC.Sno；,An Introduction to Database System,非等值连接查询,连接运算符 不是 = 的连接操作. 比较运算符：、=、. BETWEEN . AND .,An Introduction to Database System,三、自身连接,一个表与其自己进行
31、连接，称为表的自身连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀,An Introduction to Database System,自身连接（续）,例34 查询每一门课的间接先修课（即先修课的先修课）,SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno = SECOND.Cno；,An Introduction to Database System,四、外连接（Outer Join）,外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组 外连接操作以指定表
32、为连接主体，将主体表中不满足连接条件的元组一并输出,An Introduction to Database System,外连接（续）,例 33 查询每个学生及其选修课程的情况 包括没有选修课程的学生-用外连接操作,SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROM Student，SCWHERE Student.Sno *= SC.Sno；,An Introduction to Database System,外连接（续）,在属性列后面加外连接操作符*指定主体表 非主体表有一“万能”的虚行，该行全部由空值组成 虚行可以和主体表中所有不
33、满足连接条件的元组进行连接 由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值,An Introduction to Database System,外连接（续）,左外连接外连接符出现在连接条件的左边右外连接外连接符出现在连接条件的右边,An Introduction to Database System,五、复合条件连接,WHERE子句中含多个连接条件时，称为复合条件连接例35查询选修2号课程且成绩在86分以上的所有学生的学号、姓名,SELECT Student.Sno, student.Sname FROM Student, SC WHERE Student.Sno = SC.Sno /* 连接谓词*/ AND SC.Cno= 2 /* 其他限定条件 */ AND SC.Grade 86； /* 其他限定条件 */,An Introduction to Database System,多表连接,例36 查询每个学生的姓名、选修课程名及成绩。,SELECT Sname，Cname，GradeFROM Student，SC，CourseWHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno；,
