第三章：关系数据语言 SQL

1. 第三章：关系数据语言SQL

2. 关系数据语言SQL • SQL概述 • SQL语言组成 • SQL的数据定义 • SQL的数据查询 • SQL的数据操纵 • 复杂查询 • 嵌入式SQL

3. SQL概述 • SQL(Structured Query Language)结构化查询语言，1974年Boyce和Chamberlin提出，首先在IBM公司的关系数据库系统System R上实现。 • 特点：功能丰富、使用方便、灵活、语言简洁易学,应用系统范围广，统一标准。 • 1986年，ANSI数据库委员会X3H2批准了SQL作为数据库语言的美国标准，ISO随后也提出了同样的决定。 • 现状 • 大部分DBMS产品都支持SQL，成为操作数据库的标准语言，有方言，支持程度不同。

4. SQL概述 • 结构化查询语言SQL（Structured Query Language）主要是基于关系代数的，但有部分是基于谓词演算的，还有部分则是其他的。 • SQL86、SQL89、 SQL2（又称SQL92）、SQL3（又称SQL99）。 • 从字面看SQL 只是一个查询语言，而实际上SQL作为一种标准数据库语言，从对数据库的随机查询到数据库的管理和程序设计，SQL几乎无所不能，功能十分丰富；而且SQL是一种陈述性的语言，语言简洁易学，书写非常简单，使用方便。SQL既可以作为交互式语言独立使用，用作联机终端用户与数据库系统的接口；也可以作为子语言嵌入宿主语言中使用，增强应用程序的处理能力。

5. SQL概述 • 主要特点 • （1）语言简洁、易学易用：核心功能只有8个动词，语法简单，接近英语

6. SQL概述 • （2）高度非过程化的语言：用户只需提出“干什么”，至于“怎么干”由DBMS解决；用户只需要早查询语句中提出需要什么，DBMS即可按路径存取，并把结果返回给用户。 • （3）面向集合的语言：每一个SQL的操作对象是一个或多个关系，操作的结果也是一个关系。 • （4）即可独立使用，又可嵌入到宿主语言中使用，具有自主型和宿主型两种特点。 • （5）具有查询、操作、定义和控制四种语言一体化的特点。它只向用户提供一种语言，但该语言具有上述多种功能，且每中操作只需一种操作符。

7. SQL语言组成 • 关系与表 • SQL的数据类型 • SQL的语句类型

8. 关系与表 • 表（或称关系）具有下列特征： • 1.一个表中有一个或多个命名列（Column）。 • 2.各列无从左到右的顺序。 • 3.一个表中可有零个或多个行（Row）。 • 4.行的顺序是无关紧要的。 • 5.一个表中的每一行都是唯一的。 • 6.任何行和列的交点都有一个唯一的值。 • 7.同一列中的所有值都必须取自同一值域。 • 8每一个表都有一个称为主关键字（Primary Key）的 列的子集，它的值可以唯一确定每一行。 • 表——关系，行——元组、记录，列——属性、字段。

9. 关系与表 • 具有无序元组的account关系

10. 关系与表 • Depositor关系

11. 关系与表 • Branch关系

12. 关系与表 • Loan关系

13. 关系与表 • Borrower关系

14. 关系与表 • Customer关系

15. SQL的数据类型 • 早期SQL标准定义的基本数据类型有8种：CHARACTER（字符）、NUMERIC（数值）、DECIMAL（十进制数）、INTEGER（整数）、SMALLINT（短整数）、FLOAT（浮点数）、REAL（实数）、DOUBLE PRECISION（双精度数）。 • 在SQL3中规定了3类数据类型：预定义数据类型、构造数据类型和用户定义数据类型（UDT，User Defined Type）。

16. SQL的数据类型 • 数值型 •  INTEGER 简写为INT。 • SMALLINT •  NUMERIC (p,s) 例如，语句： • x2NUMERIC(3，2) •  DECIMAL (p,s)　例如，语句： • x3 DEC(4,3) •  FLOAT(p) 例如，语句： • x5 FLOAT(10) •  REAL •  DOUBLE PRECISION

17. SQL的数据类型 • 字符串型 • CHARACTER (n) 定义数据类型为字符串。例如，语句 • x1 CHAR (30) •  VARCHAR(n) 定义可变长字符串。 • 位串型 • BIT(n) •  BIT VARYING(n ) 例如，语句 • y1 BIT (8) • y1可以存放一个二进制位串，如十六进制表示的位 串X' 44'，或用二进制表示的同一位串B,'01000100'。

18. SQL的数据类型 • 时间型 • DATE 定义日期。例如，语句 • start_date DATE •  TIME •  TIMESTAMP 定义时间戳。 • 布尔型 • BOOLEAN 定义布尔数，其值可以是：TRUE（真）、FALSE（假）、UNKNOWN（未知）。

19. SQL的数据类型 • 对于数值型数据，可以执行算术运算和比较运算，但对其他类型数据，只可以执行比较运算，不能执行算术运算。 • 构造数据类型是由特定的保留字和预定义数据类型构造而成的，如用“REF”定义的引用类型，用“ROW”定义的行类型，用“ARRAY”定义的聚合类型等。 • 用户定义数据类型（UDT）是一个对象类型，是由用户按照一定的规则用预定义数据类型组合定义的自己专用的数据类型。 • 许多SQL产品还扩充了其他一些数据类型，如TEXT（文本）、MONEY（货币）、GRAPHIC（图形）、IMAGE（图像）、GENERAL（通用）、MEMO（备注）等。

20. SQL的语句类型 • 1．查询语言（QL） • 查询语言用来对已存在的数据库中的数据按照指定的组合、条件表达式或排序进行检索。它的基本结构是由SELECT子句、FROM子句、WHERE子句组成的查询块。 • 2．数据操纵语言（DML） • DML的命令用来改变数据库中的数据，它有3个基本语句：INSERT （插入） 、UPDATE（修改）、DELETE（删除）。

21. SQL的语句类型 • 3．数据定义语言（DDL） • DDL用来创建数据库中的各种对象，包括数据库模式、表、视图、索引、同义词、聚簇等，它的基本语句有：CREATE SCHEMA、CREATE TABLE、CREATE VIEW、CREATE INDEX等。 • 4．数据控制语言（DCL） • DCL用来授予或回收访问数据库的某种特权，控制数据操纵事务的发生时间及效果、对数据库进行监视等。数据控制基本语句有：GRANT、REVOKE、COMMIT、ROLLBACK、LOCK、UNLOCK等。

22. SQL的语句类型 • SQL3按照语句的功能把SQL语句分为7类：SQL模式语句、SQL数据语句、SQL事务语句、SQL控制语句、SQL连接语句、SQL会话语句、SQL诊断语句。 • SQL语句由动词、SQL对象、限定词等组成。SQL3规定的SQL对象（Object）有：簇集（Cluster）、授权ID、特权（Privilege）、目录（Catalog）、模式（Schema）、表（Table）、列（Column）、SQL域（Domain）和UDT、约束和断言（Constraint and Assertion）、字符集（Character Set）、聚合（Collation）、翻译（Translation）、触发器（Trigger）、模块（Module）、调用例程（SQL-invoked Routine）等。

23. 3.2 SQL的数据定义 • 表的建立和删除 • 表的扩充与修改 • 视图（View） • 索引 • 数据库模式的创建和删除

24. 表的建立和删除 • 1．表的建立 • CREATE TABLE ＜表名＞ • (＜列定义＞ … [＜完整性约束＞ …]) • ＜列定义＞给出列的名称、数据类型、缺省值。每一个列的数据类型可以是预定义数据类型，也可以是用户定义数据类型。 • 缺省值用默认子句表达：DEFAULT ＜缺省值＞。 • ＜完整性约束＞可以是对表的约束或对列的约束，以保证数据的完整性。

25. 表的建立和删除 • 完整性约束可用以下3种语句表达。 • （1）主关键字子句： PRIMARY KEY(＜列名＞) • （2）外来关键字子句：FOREIGN KEY(＜列名＞) REFERENCES ＜表名＞(＜列名＞) • （3）检验子句： CHECK(＜约束搜索条件＞) • CHECK子句规定了一个规则，即一个表的一个列或多列的组合只能包含在这个规则定义的集合之内的值，其中的＜约束搜索条件＞可以是一个条件表达式或一个包含SELECT语句的SQL语句。

26. 表的建立和删除 • CREATE TABLE BRANCH • (BRANCH-NAME CHAR(15), • BRANCH-CITY CHAR(30), • ASSETS INTEGER, • PRIMARY KEY(BRANCH-NAME), • CHECK(ASSETS>=) • ) • 凡带有NOT NULL的列，不允许出现空值；未带NOT NULL的列，可以出现空值。 • 空值NULL表示“不知道”。 • 当首次用CREATE TABLE定义一个新表后，只是建立了一个空表。

27. 表的建立和删除 • 2．表的删除 • DROP TABLE ＜表名＞ [CASCADE | RESTRICT] • 当采用任选项CASCADE时，在删除表时，该表中的数据、表本身以及在该表上所建的索引和视图将全部随之消失；当采用任选项RESTRICT时，只有在先清除了表中的全部记录行数据，以及在该表上所建的索引和视图后，才能删除一个空表，否则拒绝删除表。 • 例：删除客户表CUSTOMER。 • DROP TABLE CUSTOMER

28. 表的扩充和修改 • 1．在现存表中增加新列 • ALTER TABLE ＜表名＞ • ADD [COLUMN] (＜列名＞ ＜数据类型 ＞ [NULL｜NOT NULL]，…) • 例：在顾客表中增加省份列 • ALTER TABLE CUSTOMER • ADD(PROVINCE CHAR(30))

29. 表的扩充和修改 • 2．删除已存在的某个列 • ALTER TABLE ＜表名＞ • DROP [COLUMN] ＜列名＞ [CASCADE | RESTRICT] • 例：把CUSTOMER表中的PROVINCE列删除，并且把引用BPLACE列的所有视图和约束也一起删除。 • ALTER TABLE CUSTOMER • DROP PROVINCE CASCADE

30. 表的扩充和修改 • 3．补充定义主关键字 • ALTER TABLE ＜表名＞ • ADD PRIMARY KEY (＜列名表＞) • 4．删除主关键字 • ALTER TABLE ＜表名＞ • DROP PRIMARY KEY

31. 表的扩充和修改 • 5．补充定义外来关键字 • ALTER TABLE ＜表名-1＞ • ADD FOREIGN KEY [(＜外来关键字名＞)] (＜列名表＞) • REFERENCES ＜表名-2＞ • [ON DELETE { CASCADE | RESTRICT | SET NULL}] • 6．删除外来关键字 • ALTER TABLE ＜表名＞ • DROP ＜外来关键字名＞

32. 视图（View） • 视图是以现存表的全部或部分内容建立起来的一个表。视图不是物理存在的，它不包含真正存储的数据，不占存储空间。但视图可以像一般的表那样操作（少有限制），因此视图也称作虚表，而真正物理存在的表称作实表或基本表。 • 优点是提高了数据库对应用程序的独立性，有利于保持数据的一致性。其次是简化了复杂查询，提供了保持数据库安全性的手段。

33. 视图（View） • 1．建立视图 • CREATE VIEW＜视图名＞［（＜列名1＞，＜列名2＞，…）］ • AS ＜查询子句＞ • 视图创建总是包括一个查询语句SELECT。可以利用SELECT语句从一个表中选取所需要的行或列（使用查询条件WHERE 子句）构成视图，也可以从几个表中选取所需要的行或列（使用子查询和链接技术）构成视图。但要注意，在视图定义中不能包含ORDER BY子句。

34. 视图（View） • 例：创建一个包含各支行以及支行中客户名字 • CREATE VIEW ALL-CUSTOMER AS • (SELECT BRANCH-NAME,CUSTOMER-NAME • FROM DEPOSITOR,ACCOUNT • WHERE DEPOSITOR.ACCOUNT-NUMBER = ACCOUNT.ACCOUNT-NUMBER) • UNION • (SELECT BRANCH-NAME,CUSTOMER-NAME • FROM BORROWER,LOAN • WHERE BORROWER.LOAN-NUUMBER = LOAN.LOAN- NUMBER • )

35. 视图（View） • 2．删除视图 • DROP VIEW ＜视图名＞{ CASCADE | RESTRICT } • 例：删除视图ALL-CUSTOMER • DROP VIEW ALL-CUSTOMER RESTRICT

36. 索引 • 早期的SQL标准中采用了索引技术，但是SQL2和SQL3不主张使用索引，而代之以在创建表时直接定义关键字。 • 1．创建索引 • CREATE [UNIQUE] INDEX ＜索引名＞ • ON ＜表名＞(＜列名1＞[ASC｜DESC]，＜列名2＞[ASC｜DESC]，…) • 2．删除索引 • DROP INDEX [ON＜表名＞]

37. 数据库模式的创建与删除 • 1．数据库模式的创建 • CREATE SCHEMA ＜模式名＞ AUTHRIZATION ＜用户名＞ • [＜CREATE DOMAIN子句＞| • ＜CREATE TABLE子句＞| • ＜CREATE VIEW ＞|……]

38. 数据库模式的创建与删除 • 2．数据库模式的删除 • DROP SCHEMA ＜模式名＞ {CASCADE | RESTRICT} • 当用DROP SCHEMA语句删除数据库模式时，可以选用两种方式。 • （1）选用CASCADE，即当删除数据库模式时，则本数据库模式和其下属的基本表、视图、索引等全部被删除。

39. 数据库模式的创建与删除 • （2）选用RESTRICT，即本数据库模式下属的基本表、视图、索引等事先已清除，才能删除本数据库模式，否则拒绝删除。

40. SQL的数据查询 • SQL的SELECT语句用于检索数据。 • “SELECT-FROM-WHERE”的基本结构块： • SELECT A１，A２，…，An • FROM R１，R２，…，Rm • WHERE P • SELECT-FROM-WHERE查询语句块的作用等价于下列的关系代数表达式：

41. SQL的数据查询 • 单表查询 • 多表查询 • 表达式与函数的使用 • 相关子查询 • 关系代数运算

42. 单表查询 • 1．从表中选出指定的列 • SELECT(DISTINCT)BRANCH-NAME • FROM LOAN • SQL对查询的结果不会自动去除重复行，如果要求删除重复行，可以使用限定词DISTINCT。 • 2．从表中选出若干记录 • WHERE子句允许用户确定一个谓词，该谓词的值随着记录（元组）数据的不同可以为真（True）或假（False）。带有WHERE子句的SELECT语句，执行结果只给出使谓词为真的那些记录（元组）值。WHERE之后的谓词就是查询条件。

43. 单表查询 • 例：找出所有在PERRYRIDGE支行贷款并且贷款额超过1200美元的贷款号 • SELECT LOAN-NUMBER • FROM LOAN • WHERE BRANCH-NAME = ‘PERRYRIDGE’ • AND AMOUNT>1200

44. 单表查询 • 谓词ORDER BY后可以跟多个排序的变量名，第一个变量为主序，下面依次类推。每一个排序列名后可用限定词ASC（升序）或DESC（降序）声明排序的方式，缺省为升序。 • 例：按字母顺序列出在Perryridge支行中有贷款的客户 • SELECT DISTINCT CUSTOMER-NAME • FROM BORROWER,LOAN • WHERE BORROW.LOAN-NUMBER=LOAN.LOAN-NUMBER • AND BRANCH-NAME=‘PERRYRIDGE’ • ORDER BY CUSTOMER-NAME

45. 单表查询 • 条件比较运算符＝、＜ 和逻辑运算符AND（与），此外还可以使用的运算符有：＞（大于）、＞＝（大于等于）、＜＝（小于等于）、＜＞（不等于）、NOT（非）、OR（或）等。 • 谓词LIKE 只能与字符串联用，常常是“＜列名＞ LIKE pattern”的格式。特殊字符“_”和“%”作为通配符。 • 谓词IN表示指定的属性应与后面的集合（括号中的值集或某个查询子句的结果）中的某个值相匹配，实际上是一系列的OR（或）的缩写。谓词NOT IN表示指定的属性不与后面的集合中的某个值相匹配。 • 谓词BETWEEN是“包含于…之中”的意思。

46. 单表查询 • 例：找出接到地址中包含子串‘Main’的所有客户的姓名 • SELECE CUSTOMER-NAME • FROM CUSTOMER • WHERE CUSTOMER-STREET LIKE ‘%MAIN%’ • 例：找出贷款额在90000美元和100000美元之间的贷款号 • SELECT LOAN-NUMBER • FROM LOAN • WHERE AMOUNT BETWEEN 90000 AND 100000

47. 单表查询 • 3．NULL • NULL的唯一含义就是“未知值”。NULL不能用来表示无形值、 缺省值、不可用值，以及取最低值或取最高值。 • SQL规定，在含有运算符+、–、*、/ 的算术表达式中，若有一个值是空值，则该算术表达式的值也是空值；任何一个含有NULL 的比较操作的结果的取值都为“假”。

48. 单表查询 • 在SQL中不能使用条件：＜列名＞＝NULL。在SQL中只有一个特殊的查询条件允许查询NULL值： • WHERE SNO IS [NOT] NULL • 找出LOAN关系中AMOUNT为空值的贷款号 • SELECT LOAN-NUMBER • FROM LOAN • WHERE AMOUNT IS NULL

49. 多表查询 • 1．子查询 • WHERE子句可以包含另一个称为子查询（Subquery）的查询，即在SELECT语句中先用子查询查出一个表的值，主句（主查询）根据这些值再去查另一个表的内容（与单表SELECT操作一样）。子查询总是括在圆括号中，作为表达式的可选部分出现在条件比较运算符的右边，并且可有选择地跟在IN、ANY（SOME）、ALL和EXISTS等谓词后面。采用子查询的查询又称嵌套查询。

50. 多表查询 • 例：找出在银行中同时又账户和贷款的客户 • SELECT DISTINCT CUSTOMER-NAME • FROM BORROWER • WHERE CUSTOMER-NAME IN ( • SELECT CUSTOMER-NAME • FROM DEPOSITOR)