第 4 章 关系数据库查询语言 SQL

1. 第4 章 关系数据库查询语言SQL • 要点： • SQL基本概念、特点 • 数据定义语句、数据查询语句、数据操纵语句 • 视图 • 嵌入式SQL • 存储过程与函数

2. SQL的基本概念 • SQL的发展 • 1974年由Boyce和Chamberlin首先提出 • 其后经历了ANSI（美国国家标准机构）SQL、SQL-92、SQL-99、SQL-2003、SQL-2006和SQL-2008等标准

3. SQL的特点 • SQL功能强大集数据定义语言（DDL）、数据操纵语言（DML）和数据控制语言（DCL）于一体，使用统一的语法形式，完成数据定义、数据查询、数据更新和数据控制功能，易学易用。 • 对SQL语句的解释由DBMS完成，语句对在何处断行没有特别的要求，对大小写不敏感。 • SQL的操作对象和操作结果都是元组的集合。 • SQL是高度非过程化的语言。。

4. SQL数据类型 • ① 字符串： CHAR(n), NCHAR(n), VARCHAR(n), NVARCHAR(n) • ② 整数：SHORTINT、INT（或INTEGER） • ③ 浮点数：FLOAT, REAL, DOUBLE • ④定点数：DECIMAL(n, d)或NUMERIC(n, d)表示由n位有效数字（不包括符号和小数点）组成的十进制定点数，小数点后有d位数字。 • 日期和时间：分别用DATE和TIME表示，实际上是某个特定格式的字符串，日期形如YYYY-MM-DD（年-月-日），时间形如HH:MM:SS（时:分:秒）。 • 其它类型：大文本TEXT，BLOB大二进制，Geometry等

5. 数据定义语句 • DDL（Data Definition Language，数据定义语言） • 定义和修改关系数据库的逻辑结构，包括基本表（关系模式）、视图、索引和域。 • 定义信息保存在数据字典中。 • SQL Server 中 以 sys 开始的一系列表 • 数据字典是数据库系统中各类数据描述的一个集合。

6. 关系模式的定义与删除 • CREATE TABLE＜表名＞( • ＜属性列名＞＜数据类型＞[列级完整性约束] • [, ＜属性列名＞＜数据类型＞[列级完整性约束] ] • [, ···] • [, 表级完整性约束] • );

7. 数据定义语言DDL • 完整性约束的定义形式主要有以下5种： • ① NOT NULL：列级完整性约束，表示某个属性不能取空值。 • ② UNIQUE：列级完整性约束，表示某个属性的取值必须唯一。 • ③ PRIMARY KEY：可以作为列级完整性约束，表示某个属性为主码；也可以作为表级完整性约束，用PRIMARY KEY(<属性名列表>)子句来定义，表示属性列表共同构成这个表的主码；

8. 数据定义语言DDL • ④ CHECK(<条件表达式>)子句：作为表级完整性约束，说明每个进入表中的元组必须满足的条件。 • ⑤ FOREIGN KEY(<属性名1>) REFERENCES <表名>(属性名2)子句：作为表级完整性约束，说明表的外码，表示所定义表中的属性（即“属性名1”）与另一个表中的属性（即“属性名2”）相对应。

9. 服装销售 数据库 数据

10. 数据定义语言 —Create Table • 【例4-1】 建立第3章服装销售系统数据库中的各个表。 • CREATE TABLE 服装 ( • 服装编号 char(4) NOT NULL UNIQUE, • 品牌 char(20), • 型号 char(5), • 颜色char(2), • 价格 unsigned int, • PRIMARY KEY(服装编号) • ); //与NOT NULL UNIQUE等价，可省略

11. 数据定义语言 —Create Table • CREATE TABLE 顾客 ( • 顾客编号 char(4), • 姓名 char(20), • 性别 char(2), • 年龄 unsigned int , • 电话 char(11), • PRIMARY KEY(顾客编号) ); CREATE TABLE 顾客 ( 顾客编号 char(4) PRIMARY KEY, 姓名 char(20), 性别 char(2), 年龄 unsigned int , 电话 char(11));

12. 数据定义语言 —Create Table • CREATE TABLE 购买记录( • 服装编号 char(4), • 顾客编号 char(4), • 购买日期 date, • 数量 unsigned int, • PRIMARY KEY(服装编号, 顾客编号), • FOREIGN KEY(服装编号) REFERENCES 服装(服装编号), • FOREIGN KEY(顾客编号) REFERENCES 顾客(顾客编号) • );

13. 数据定义语言 —Drop Table • DROP TABLE <表名>; • 用DROP TABLE语句将某个基本表删除后．表中的数据连同表的结构都从数据库中消失了。 • 【例4-2】 删除顾客关系。 • DROP TABLE 顾客;

14. 数据定义语言 —Alter Table • 基本表建立以后，可根据实际需要对其结构进行修改，如增加列或删除约束等。 • 增加列默认为空，不能使用NOT NULL约束。 • ALTER TABLE <表名> • [ADD <属性名> <数据类型>[完整性约束1, …, 完整性约束n] ] • [DROP <完整性约束名>] • [MODIFY (<属性名><数据类型>) ];

15. 数据定义语言 —Alter Table • 【例4-3】 向顾客关系中增加“地址”属性。 • ALTER TABLE 顾客 ADD 地址 char(50); • 【例4-4】 修改顾客关系属性姓名为30位定长字符串。 • ALTER TABLE 顾客 MODIFY 姓名 char(30);

16. 域定义 • 域约束是最基本的完整性约束形式。当向数据库中插入数据时，数据库管理系统会检测插入的数据是否符合域的约束。域定义语句的语法如下： • CREATE DOMAIN <域名> <数据类型> • [[NOT ] NULL] • [DEFAULT <默认值>] • [CHECK <条件表达式>]; • 【例4-5】 定义地址域，允许为空值。 • CREATE DOMAIN 地址 CHAR(50) NULL;

17. 索引Index • 索引的定义有利于提高查询速度，SQL可以创建和删除索引文件。 • 基本表建立以后，数据库管理员或表的建立者可以根据需要在基本表上建立一个或多个索引文件，以提供多种存取路经，加快存取速度。

18. 索引Index • 索引的定义： • CREATE [UNIQUE] [CLUSTER] INDEX • ＜索引名＞ ON＜表名＞( • ＜属性列名＞[ASC|DESC] • [, ＜属性列名＞[ASC[DESC] ]…); • UNIQUE表示索引的每一个索引值只对应唯一的数据记录 • CLUSTER：建聚簇索引，即索引项顺序与表中记录的物理顺序一致，一个基表只能建一个聚簇索引 • ASC(升序，缺省)、DESC(降序) • 索引建立后由系统使用和维护，不需用户干预

19. 索引Index • 【例4-6】 为顾客关系按姓名的升序建立聚簇索引。 • CREATE CLUSTERED INDEX IdxCname ON 顾客(姓名); • 【例4-7】 为购买记录关系按顾客编号的升序、服装编号的降序建立索引文件。 • CREATE INDEX IdxCCID ON • 购买记录(顾客编号, 服装编号 DESC); • 【例4-8】 为服装关系按价格的降序建立唯一性索引。 • CREATE UNIQUE INDEX IdxCprice • ON 服装(价格 DESC);

20. 索引Index • 索引的删除： • 当一个索引不再需要时，就可以用DROP INDEX语句将其删除，格式如下： • DROP INDEX <索引名>; • 【例4-9】 删除会员信息关系的索引IdxCname。 • DROP INDEX IdxCname;

21. 数据查询语句 • 所谓数据查询，就是从数据库所保存的众多数据中挑出符合某种条件的一部分数据，或者将这些数据挑出来之后对它们进行适当的运算，然后得到某种汇总结果（即统计信息）。 • 数据查询的对象可以是基本表，也可以是视图

22. 查询语句的基本结构 • SELECT [ALL|DISTINCT]<目标列表达式>[, <目标列表达式>]… • FROM <表名或视图名>[, <表名或视图名>]… [WHERE<条件表达式>] [GROUP BY <列名1> [HAVING<条件表达式>]] [ORDER BY <列名2> [ASC|DESC]];

23. 数据查询 • 整个SELECT语句的含义是， • 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组， • 再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。 • 如果有GROUP BY子句，则将结果按“列名1”的值进行分组，该属性列值相等的元组为一个组。通常会对每组中的记录用集函数。 • 如果有GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。 • 如果有ORDER BY子句，则结果表还要按“列名2”的值的升序（ASC，系统默认值）或降序（DESC）排序。

24. 简单SQL查询 • 简单查询又叫单表查询，是指查询条件和内容都只涉及一个基本表的查询。 • 【例4-10】 查询所有顾客的信息。 • SELECT * FROM 顾客; • SELECT子句指出被选择的目标列表的名称，“*”表示表的所有属性，FROM子句指出表的名称，查询结果就是会员信息关系中所有顾客的信息。

25. 简单SQL查询 • 【例4-11】 查询所有顾客的编号、姓名及电话。 • SELECT 顾客编号, 姓名, 电话 FROM 顾客;

26. 简单SQL查询—DISTINCT • 【例4-12】 查询购买了服装的顾客编号。 • SELECT DISTINCT 顾客编号 FROM 购买记录; • 用DISTINCT去掉重复行。如果没有指定DISTINCT短语，则默认为ALL，即保留结果表中取值重复的行。

27. 简单SQL查询—WHERE子句 • WHERE用来设定关系中的元组选择条件，只有满足这些条件的元组才能出现在结果中，相当于关系代数中的选择操作。 • WHERE子句常用的查询条件可以是关系表达式或逻辑表达式，可以使用(NOT) IN、IS (NOT) NULL、(NOT) BETWEEN AND、(NOT) LIKE等谓词。

28. 简单SQL查询 —复合条件 • 【例4-13】 查询20岁以下的女顾客的编号和姓名。 • SELECT 顾客编号, 姓名 • FROM 顾客 • WHERE 年龄<20 AND 性别=‘女’; • 查询条件可以是使用=、<>（或!=）、>、<、>=、<=等比较运算符的关系表达式，也可以是由多个条件通过AND、OR、NOT等逻辑运算符组成的逻辑表达式。如果指定的条件是字符串类型的，需要把字符串用单引号括起来。

29. 简单SQL查询—Between • 【例4-14】 查询年龄在18～30岁的顾客的姓名和性别。 • SELECT 姓名, 性别 FROM 顾客 WHERE 年龄 BETWEEN 18 AND 30; • 表达式“A BETWEEN B AND C”能够确定范围，等价于“A>=B AND A<=C”。与之相对的表达式是“A NOT BETWEEN B AND C”，用于查找属性值不在指定范围内的元组。

30. 简单SQL查询— in • 【例4-15】查询来自“佐丹奴”和“李宁”两个品牌的服装的信息。 • SELECT * FROM 服装 WHERE 品牌 IN (‘佐丹奴’, ‘李宁’); • 谓词IN用来确定集合，可以用来查找属性值属于指定集合的元组。与之相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。

31. 简单SQL查询— Like • 【例4-16】 查询所有姓王的顾客的信息。 • SELECT * FROM 顾客 WHERE 姓名 LIKE ‘王%’; • 使用LIKE谓词，可以查询指定的属性列值与<匹配串>相匹配的元组。其格式如下： • [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符>’] • <匹配串>可以是一个完整的字符串，也可以含有通配符“%”或“_”。 • “%”表示可以用任意长度的字符串替代。 • “_”表示可以用任意单个字符替代。 • 如acb、aaccb、ab等都与匹配串“a%b”匹配，acb、adb等都与匹配串“a_b”匹配。

32. 简单SQL查询—字符 _ % • 【例4-17】 查询所有姓李但全名为两个字的顾客的信息。 • SELECT * FROM 顾客WHERE 姓名 LIKE‘李_ _’; • 注意：一个汉字要占两个字符的位置，所以匹配串李后面需要跟两个空格。 • 【例4-18】 查询所有不姓李的顾客的信息。 • SELECT * FROM 顾客WHERE 姓名 NOT LIKE ‘李%’;

33. 简单SQL查询—字符_%转意 • 但当“%”或“_”不是作为匹配串而是数据库中某字符串属性值的组成部分时，就需要告诉系统“%”或“_”需要进行转义。转义的方法是使用“ESCAPE‘<换码字符>’”短语，用户可以自己设定换码字符，如“\”、“y”等。 • 例如，“LIKE '\%%\%' ESCAPE '\'”中使用了“ESCAPE '\'”短语，就表明“\”是换码字符，匹配串“\%%\%”中的“\%”指的是普通字符“%”，因此该匹配串将匹配所有以“%”开始并以“%”结束的字符串。

34. 简单SQL查询—字符_%转意 • 【例4-19】 查询姓名包含Kim_Jae的顾客的信息。 • SELECT * FROM 顾客 WHERE 姓名 LIKE '%Kim\_Jae%'ESCAPE '\'; • 通过转义，“\”后面的“_”作为一个普通字符使用。

35. 简单SQL查询—NULL空值 • 判断属性值或输入值是否为空值，可以用谓词IS NULL和IS NOT NULL，这里的“IS”不能用“＝”代替。 • 【例4-20】 查询电话是空值的顾客的姓名和性别。 • SELECT 姓名, 性别 FROM 顾客 • WHERE 电话 IS NULL; • 【例4-21】 查询电话不是空值的顾客的姓名和性别。 • SELECT 姓名, 性别 FROM 顾客WHERE 电话 IS NOT NULL;

36. 简单SQL查询—ORDER BY • 返回查询结果时，元组的排列顺序与数据的存储顺序相同。如果用户需要按照某种指定的顺序来显示查询结果，就需要使用ORDER BY子句。该子句只作用于查询结果，并不会改变数据库中的实际存储顺序。 • 排序的依据可以是基于一个或多个属性数据。当依据多个属性排序时，如果第一个属性值相等，就按照第二个属性值排序，以此类推。

37. 简单SQL查询—ORDER BY • 【例4-22】 查询所有顾客的姓名和年龄，并按照年龄降序排列。 • SELECT 姓名, 年龄 FROM 顾客 ORDER BY 年龄 DESC; • 【例4-23】 查询顾客C001的购买记录，按购买日期降序排列，相同购买日期的按数量升序排列。 • SELECT * FROM 购买记录 WHERE 顾客编号='C001' ORDER BY 购买日期 DESC, 数量;

38. 简单SQL查询—聚集函数 • 聚集函数（Aggregation Function）是一组对查询结果中的某属性列进行统计的函数，包括： • COUNT([DISTINCT] <属性名>) ：统计该属性列中值的个数，如果加DISTINCT，表示统计时不考虑重复值； • COUNT(*) ：统计关系中元组的个数。 • SUM([DISTINCT] <属性名>)：统计该属性列中值的总和。该属性必须是数值型的。 • AVG([DISTINCT] <属性名>)：统计该属性列中值的平均。该属性必须是数值型的。 • MAX(<属性名>)：统计该属性列中的最大值。 • MIN(<属性名>)：统计该属性列中的最小值。 • 特别地，除COUNT(*)之外，聚集函数在做统计之前，都先把属性列中的空值去掉。如果该属性列中都是空值，则COUNT函数返回0，其他函数返回NULL。

39. 简单SQL查询—聚集函数 • 【例4-24】 查询顾客的最低年龄。 • SELECT MIN(年龄) FROM 顾客; • 【例4-25】 查询女顾客的平均年龄。 • SELECT AVG(年龄) FROM 顾客 WHERE 性别='女'; • 【例4-26】 查询顾客的总人数。 • SELECT COUNT(*) FROM 顾客; • 【例4-27】 查询购买了服装的顾客总人数。 • SELECT COUNT(DISTINCT 顾客编号) FROM 购买记录;

40. 简单SQL查询—GROUP BY • 使用GROUP BY子句的查询称为分组查询。GROUP BY子句将一个表按照指定属性组值相等的记录进行分组，再对每个组的数据进行相应的操作。当查询语句中使用聚集函数时，GROUP BY子句将控制聚集函数运算的范围。 • 通常，与GROUP BY子句一起使用的还有一个HAVING子句。与WHERE子句相同的是，HAVING子句也描述条件，不同的是，HAVING子句描述的是分组条件，只有满足分组条件的组才选出来处理。

41. 简单SQL查询—GROUP BY • 【例4-28】 查询每天的购买记录数。 • SELECT 购买日期, COUNT(*) 购买记录数 • FROM 购买记录 GROUP BY 购买日期;

42. 简单SQL查询—GROUP BY • 【例4-29】 查询购买记录数在20笔以上的购买日期。 • SELECT 购买日期 FROM购买记录 GROUP BY 购买日期 HAVING COUNT(*)>20; • 【例4-30】 查询平均价格低于300元的品牌及其平均价格。 • SELECT 品牌, AVG(价格) FROM 服装GROUP BY 品牌 HAVING AVG(价格)<300;

43. 连接查询 • 又称为多关系查询。包括等值连接、自然连接、非等值连接、自身连接、外连接查询等类型。 • 1．等值与非等值连接查询 • 将两个表中对应属性列值相等的行连接起来，即当连接条件运算符为“=”时，称为等值连接。使用其他运算符称为非等值连接。若在等值连接中把目标列中重复的属性去掉则为自然连接。

44. 连接查询—内连接 • 【例4-31】 查询购买了任意服装的顾客的编号和姓名。 • SELECT DISTINCT 顾客.顾客编号, 姓名 • FROM 顾客, 购买记录 • WHERE 顾客.顾客编号=购买记录.顾客编号; • SELECT子句和WHERE子句中都用到了“表名.列名”这种格式来表示某一列属于哪个表，以消除属性列的二义性。但是如果某一列名在参加连接的各表中是唯一的，那么该列名前的表名是可以省略的。 • 利用SELECT语句进行表的连接时，必须在WHERE子句中指明连接条件，否则就是做两个表的笛卡儿积，其连接结果一般是无意义的。 SELECT DISTINCT 顾客.顾客编号, 姓名 FROM 顾客, 购买记录

45. 连接查询—自身连接 • 2．自身连接 • 连接操作可以在不同的表之间进行，也可以在同一个表中进行。对同一个表进行的连接查询称为自身连接查询。 • 【例4-32】 查询跟张珊年龄相同的顾客ID和姓名。 • SELECT C1.顾客编号, C1.姓名 • FROM 顾客 C1,顾客 C2 • WHERE C1.年龄= C2.年龄 AND C2.姓名='张珊'; • FROM子句中会员信息被打开两次，为区分两者所以分别赋以不同的别名C1、C2。

46. 连接查询—外连接 • 3．外连接(Outer Join) • 内连接的查询结果都是满足连接条件的元组。但是，在内连接的查询结果中，一些重要的信息可能会因为连接条件不满足而被丢失。如果允许结果关系中出现的不满足连接条件的某些元组，这种连接称为外连接。外连接的表示方法为 • SELECT <属性列1>, <属性列2>, ··· • FROM <表名1> LEFT/RIGHT/FULL OUT JOIN <表名2> ON [约束条件]; • 左外连接列出左边关系中所有的元组，右外连接列出右边关系中所有的元组。

47. 连接查询—外连接 • 【例4-33】 查询每个顾客的基本信息及其购物信息。若顾客没有购物，则显示基本信息，其购物信息用空值表示。 • SELECT 顾客.顾客编号, 姓名, 性别, 年龄, 电话, 服装编号, 购买日期, 数量 • FROM 顾客 LEFT JOIN 购买记录 ON(顾客.顾客编号=购买记录.顾客编号);

48. 连接查询—外连接 SELECT 顾客.顾客编号, 姓名, 性别, 年龄, 电话, 服装编号, 购买日期, 数量 FROM 顾客 LEFT OUT JOIN 购买记录 ON(顾客.顾客编号=购买记录.顾客编号);

49. 连接查询—多表 • 4．多表连接查询 • 如果查询涉两个以上的表，则称为多表连接查询。要注意定义表之间的连接条件。 • 【例4-34】 查询每个顾客的基本信息、购物信息及其所购服装的详细信息，结果按顾客编号升序排列。 • SELECT * FROM 顾客, 购买记录, 服装 • WHERE 顾客.顾客编号=购买记录.顾客编号 AND 购买记录.服装编号=服装.服装编号 • ORDER BY 顾客.顾客编号;

50. 连接查询—多表 • 【例4-35 查询购买过“李宁”服装的顾客信息。 • SELECT DISTINCT 顾客.* • FROM 顾客, 购买记录, 服装 • WHERE 顾客.顾客编号=购买记录.顾客编号 • AND 购买.服装编号=服装.服装编号 • AND 品牌='李宁';