数据库原理 Database Principles 第三章 关系数据库标准语言SQL（续1）.

Presentation on theme: "数据库原理 Database Principles 第三章 关系数据库标准语言SQL（续1）."— Presentation transcript:

1 数据库原理 Database Principles 第三章 关系数据库标准语言SQL（续1）

2 3.3 查 询 3.3.1 概述 3.3.2 单表查询 3.3.3 连接查询 3.3.4 嵌套查询 3.3.5 集合查询 3.3.6 小结

3 3.3.1 概述 语句格式 SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] … [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ]；

4 语句格式 SELECT子句：指定要显示的属性列 FROM子句：指定查询对象(基本表或视图) WHERE子句：指定查询条件
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。 HAVING短语：筛选出只有满足指定条件的组 ORDER BY子句：对查询结果表按指定列值的升序或降序排序

5 示例数据库 学生-课程数据库 学生表：Student(Sno，Sname，Ssex，Sage，Sdept)
课程表：Course(Cno，Cname，Cpno，Ccredit) 学生选课表：SC(Sno，Cno，Grade)

6 3.3.2 单表查询 查询仅涉及一个表，是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序
单表查询 查询仅涉及一个表，是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组

7 查询指定列 [例3] 查询全体学生的详细记录。 [例1] 查询全体学生的学号与姓名。 SELECT Sno，Sname
[例1] 查询全体学生的学号与姓名。 SELECT Sno，Sname FROM Student；  [例2] 查询全体学生的姓名、学号、所在系。 SELECT Sname，Sno，Sdept FROM Student； [例3] 查询全体学生的详细记录。 SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或 SELECT *

8 3. 查询经过计算的值 SELECT子句的<目标列表达式>为表达式 算术表达式 字符串常量 函数 列别名等
[例4] 查全体学生的姓名及其出生年份。 SELECT Sname，2005-Sage FROM Student；  Sname Sage 输出结果： 李勇 刘晨 王名 张立

9 3. 查询经过计算的值 [例5] 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
[例5] 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。 SELECT Sname，'YearofBirth:'，2005-Sage，ISLOWER(Sdept) FROM Student； 输出结果： Sname 'YearofBirth:'2005-Sage ISLOWER(Sdept) 李勇 Year of Birth: cs 刘晨 Year of Birth: is 王名 Year of Birth: ma 张立 Year of Birth: is

10 SELECT Sname NAME，'Year of Birth: ’ BIRTH，
[例5.1] 使用列别名改变查询结果的列标题 SELECT Sname NAME，'Year of Birth: ’ BIRTH， 2005-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENT FROM Student； 输出结果： NAME BIRTH BIRTHDAY DEPARTMENT 李勇 Year of Birth: cs 刘晨 Year of Birth: is 王名 Year of Birth: ma 张立 Year of Birth: is

11 二、选择表中的若干元组 消除取值重复的行 查询满足条件的元组 1 消除取值重复的行
1 消除取值重复的行 在SELECT子句中使用DISTINCT短语,假设SC表中有下列数据 Sno Cno Grade

12 (2)SELECT DISTINCT Sno 结果： Sno ALL 与 DISTINCT [例6] 查询选修了课程的学生学号。
[例6] 查询选修了课程的学生学号。 (1) SELECT Sno FROM SC; 或(默认 ALL) SELECT ALL Sno  结果： Sno 95001 95002 (2)SELECT DISTINCT Sno FROM SC; 结果： Sno 95001 95002

13 例题（续） 例：查询选修课程的各种成绩 错误的写法 正确的写法 SELECT DISTINCT Cno，Grade
SELECT DISTINCT Cno，DISTINCT Grade FROM SC; 正确的写法 SELECT DISTINCT Cno，Grade FROM SC; 

14 2.查询满足条件的元组 WHERE子句常用的查询条件

15 [例8] 查询所有年龄在20岁以下的学生姓名及其年龄。
(1) 比较大小 在WHERE子句的<比较条件>中使用比较运算符 =，>，<，>=，<=，!= 或 <>，!>，!<， 逻辑运算符NOT + 比较运算符 [例8] 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname，Sage FROM Student WHERE Sage < 20； 或 WHERE NOT Sage >= 20；

16 (2) 确定范围 使用谓词 BETWEEN … AND … NOT BETWEEN … AND …
[例10] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23； [例11] 查询年龄不在20~23岁之间的学生姓名、系别和年龄。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23；

17 (3) 确定集合 使用谓词 IN <值表>, NOT IN <值表>
<值表>：用逗号分隔的一组取值 [例12]查询信息系（IS）、数学系（MA）和计 算机科学系（CS）学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( 'IS'，'MA'，'CS' ); [例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sdept NOT IN ( 'IS'，'MA'，'CS' );

18 (4) 字符串匹配 [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
<匹配串>：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时， 可以用 = 运算符取代 LIKE 谓词 用 != 或 < >运算符取代 NOT LIKE 谓词

19 通配符 例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串
% (百分号) 代表任意长度（长度可以为0）的字符串 例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串 _ (下横线) 代表任意单个字符 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串

20 ESCAPE 短语： 当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE '<换码字符>' 短语对通配符进行转义。 1) 匹配模板为固定字符串 [例14] 查询学号为95001的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE '95001'； 等价于： SELECT * WHERE Sno = '95001'；

21 [例15] 查询所有姓刘学生的姓名、学号和性别。
例题（续） 2) 匹配模板为含通配符的字符串 [例15] 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE ‘刘%’； 匹配模板为含通配符的字符串（续） [例16] 查询姓"欧阳"且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE '欧阳__'；

22 例题（续） 匹配模板为含通配符的字符串（续） 匹配模板为含通配符的字符串（续） [例17] 查询名字中第2个字为"阳"字的学生的姓名和学号。
[例17] 查询名字中第2个字为"阳"字的学生的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE '__阳%'； 匹配模板为含通配符的字符串（续） [例18] 查询所有不姓刘的学生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE '刘%'；

23 例题（续） 3) 使用换码字符将通配符转义为普通字符 [例19] 查询DB_Design课程的课程号和学分。
SELECT Cno，Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\' 使用换码字符将通配符转义为普通字符(续) [例20] 查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\'；

24 (5) 涉及空值的查询 使用谓词 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替
[例21] 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL； [例22] 查所有有成绩的学生学号和课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；

25 (6) 多重条件查询 用逻辑运算符AND和 OR来联结多个查询条件 可用来实现多种其他谓词 AND的优先级高于OR 可以用括号改变优先级
[NOT] IN [NOT] BETWEEN … AND … [例23] 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20；

26 改写[例12] [例12] 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( 'IS'，'MA'，'CS' ) 可改写为： FROM Student WHERE Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS '；

27 改写[例10] [例10] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23； 可改写为： SELECT Sname，Sdept，Sage WHERE Sage>=20 AND Sage<=23；

28 三、对查询结果排序 当排序列含空值时 使用ORDER BY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序
[例24] 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= ' 3 ' ORDER BY Grade DESC；

29 查询结果 Sno Grade ------- ------- 95010 95024 95007 92 95003 82 95010 82
95010 95024 [例25] 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC；

30 四、使用集函数 5类主要集函数 求最小值 计数 计算总和 计算平均值 求最大值 DISTINCT短语：在计算时要取消指定列中的重复值
COUNT（[DISTINCT|ALL] *） COUNT（[DISTINCT|ALL] <列名>） 计算总和 SUM（[DISTINCT|ALL] <列名>） 计算平均值 AVG（[DISTINCT|ALL] <列名>） 求最大值 MAX（[DISTINCT|ALL] <列名>） 求最小值 MIN（[DISTINCT|ALL] <列名>） DISTINCT短语：在计算时要取消指定列中的重复值 ALL短语：不取消重复值 ALL为缺省值

31 使用集函数 （续） [例26] 查询学生总人数。 SELECT COUNT(*) FROM Student；
[例26] 查询学生总人数。 SELECT COUNT(*) FROM Student；  [例27] 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC； 注：用DISTINCT以避免重复计算学生人数 [例28] 计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 '；  [例29] 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) WHER Cno= ' 1 '；

32 五、对查询结果分组 使用GROUP BY子句分组 细化集函数的作用对象 未对查询结果分组，集函数将作用于整个查询结果
对查询结果分组后，集函数将分别作用于每个组 [例30] 求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；  结果 Cno COUNT(Sno)

33 对查询结果分组 （续） GROUP BY子句的作用对象是查询的中间结果表 分组方法：按指定的一列或多列值分组，值相等的为一组
使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数 [例31] 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >3；  

34 例题 [例32] 查询有3门以上课程是90分以上的 学生的学号及（90分以上的）课程数 FROM SC WHERE Grade>=90
[例32] 查询有3门以上课程是90分以上的 学生的学号及（90分以上的）课程数 SELECT Sno, COUNT(*) FROM SC WHERE Grade>=90 GROUP BY Sno HAVING COUNT(*)>=3; 只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。

35 下课了。。。 追 求 休息一会儿。。。