第三章 关系数据库标准语言 SQL

1. 第三章 关系数据库标准语言SQL 3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 视图 3.5 数据更新 3.6 小结 (本章内容有所调整）

2. 3.1 SQL概述 1. 什么是SQL？ • 结构化查询语言(Structured Query Language)。 • 介于关系代数与关系演算之间的语言。 • 是关系数据库管理系统中最流行的语言。 • 主要功能是在应用程序和数据库之间进行通信。 2. SQL的发展 1974年由Boyce和Chamberlin提出。经过不断的修改、扩充和完善，最终成为关系数据库的标准语言。 1986: ISO和ANSI共同发布SQL-86标准(SQL-1) 1989：公布了SQL/89标准 1992: 发布SQL/92标准(SQL-2) 1999: 发布SQL99标准(SQL-3) 2003：发布SQL2003 标准

3. 3.1 SQL概述(续) • 自从SQL成为国际标准语言后，各个数据库厂家纷纷推出各自的SQL软件和SQL接口软件。 • 因此，大多数的数据库均使用SQL作为共同的数据存取语言和标准接口，使不同的数据库系统之间的互操作有了共同的基础。 • SQL成为标准后，许多软件产品将SQL语言的数据查询功能与图形功能、软件功能工具、软件开发工具、人工智能程序结合起来。 • SQL已经成为数据库领域中的一个主流语言

4. 3.1 SQL概述(续) 3. SQL的特点 • 综合统一： • SQL语言集数据定义语言DDL、数据操纵语言DML和数据控制语言DCL于一体，语言风格统一，可以独立完成数据库生命周期的全部活动。 • 在关系模型中，实体和实体之间的联系均用关系表示，这种数据结构的单一性带来了数据操作符的统一。

5. 3.1 SQL概述(续) • 高度非过程化： • 只提出“做什么”，无须了解“怎么做”，因此无需了解存取路径。存取路径的选择以及SQL语句的操作过程由系统自动完成。 • 减轻了用户的负担。 • 提高了数据独立性。 • 面向集合的操作方式： • 操作对象、查找结果都可以是元组（记录）的集合。 • 插入、删除、更新操作的对象也可以是元组的集合。

6. 3.1 SQL概述(续) • 以统一的语法结构提供两种使用方式 • 自含式语言：用户可以直接使用SQL命令对数据库进行操作。 • 嵌入式语言：SQL语句能够嵌入到高级语言程序中，结合程序的其他功能一起使用。 • 语言简洁，易学易用: 完成核心功能只需9个动词：

7. 3.2 数据定义 SQL的数据定义包括定义表、视图和索引。

8. 3.2.1 定义、修改与删除基本表 一、定义基本表 1.一般格式： CREATE TABLE <表名> (<列名> <数据类型>[<列级完整性约束条件> ] [,<列名> <数据类型>[ <列级完整性约束条件>] ] … [，<表级完整性约束条件> ] )； • <表名>：所要定义的基本表的名字 • <列名>：组成该表的各个属性（列） • <列级完整性约束条件>：涉及相应属性列的完整性约束条件 • <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

9. 2. 常用完整性约束 • 主码（主键）约束： PRIMARY KEY • 唯一性约束：UNIQUE • 空值约束： NULL • 非空值约束：NOT NULL • ……

10. *3. SQL Server的常用数据类型 1) 整型数据类型 • bigint: -263~ 263-1 • int(integer): -231~231-1 • smallint: -215~ 215-1 • tinyint: 0~255 2) 浮点数据类型 • real: -3.40×10 38 ~ 3.40×1038 • float [ ( n ) ] : -1.79×10308 ~ 1.79×10308 • decimal[(p[, s])]: -1038-1 ~ 1038-1 • numeric[(p[, s])]: -1038-1 ~ 1038-1 n：存储科学记数法尾数的位数 p：小数点左边和右边十进制数字的最大个数 s：十进制小数位数

11. *3. SQL Server的常用数据类型(续) 3)字符数据类型 • char(n): 1 ~ 8000 个字符，ANSI字符 • varchar(n) : 可变长，ANSI字符 • nchar(n): 1 ~ 4000个字符，Unicode字符 • nvarchar(n) :可变长，Unicode字符 4) 日期和时间数据类型 • datetime:1753年1月1日零时~9999年12月31日23时59分59秒 • smalldatetime: 1900年1月1日~2079年6月6日内的日期。 对于不同国家、不同语言，日期时间具体表示格式有所不同。

12. *3. SQL Server的常用数据类型(续) 5) 文本和图形数据类型 • text: 可变长度的非 Unicode 数据; 最大长度为 231-1个字符。 • ntext: 可变长度 Unicode 数据; 最大长度为 230-1 个字符。 • image: 可变长度二进制数据，用于存储图片； 最多231-1个字节。 6)货币数据类型 • money: -922337213685477.5808~ 2337213685477.5808 • smallmoney:-214748.3468~214748.3467 7) 位数据类型： bit: 1字节，取值: 0和1

13. *4.定义基本表示例 例：用CREATE TABLE创建数据库表。 CREATE TABLE 课程 ( 课程编号 nchar(3) PRIMARY KEY, 课程名称 nvarchar(40) NOT NULL, 讲授学时 tinyint, 实验学时 tinyint, 课程简介 nvarchar(200) NULL)

14. *4.定义基本表示例(续) 例：建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号为主码，并且姓名取值唯一。 CREATE TABLE Student (Sno CHAR(5) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(1), Sage INT, Sdept CHAR(15))

15. *4.定义基本表示例(续) 例：建立一个“学生选课”表SC，由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。 CREATE TABLE SC ( Sno CHAR(5) , Cno CHAR(3) , Grade int, Primary key (Sno, Cno)) 表级完整性约束条件

16. *4.定义基本表示例(续) 例：创建一个“学生成绩”表，由学号、姓名，数学成绩、英语成绩和平均成绩组成，其中学号为主码，平均成绩是计算列，为英语与数学成绩的平均值。 CREATE TABLE 学生成绩 (学号 SMALLINT PRIMARY KEY, 姓名 CHAR(10), 数学 INT, 英语 INT, 平均成绩 AS 数学+英语) 计算列：指其值由其他列通过计算生成的列。

17. *4.定义基本表示例(续) 例：创建一个“通讯录”表，由序号、姓名，电话和地址组成，其中序号为标识列、主码。 CREATE TABLE 通讯录 (序号 INT IDENTITY(1,1) PRIMARY KEY, 姓名 CHAR(10), 电话 CHAR(20), 地址 CHAR(50)) 标识列: 当列类型为整数类型时，可以进一步将其定义为标识列。 标识列自动以指定的种子为初始值，按指定的递增量增长，每个表只能有一个标识列。

18. *5. 利用企业管理器创建表

19. *6. 说明 • 每个数据库中最多可以创建20亿个表； • 每个表最多可以定义1024列(字段)。 • 表分为永久表和临时表。 • 永久表创建后一直存储在数据库文件中； • 临时表在用户退出或系统修复时自动被删除。 • 临时表又分为局部临时表和全局临时表。 • 局部临时表只能由创建它的用户使用，在该用户断开连接时自动被删除；表名前加＃号。 • 全局临时表对系统当前的所有用户都可用，在使用它的最后一个用户断开时自动被删除。表名前加＃＃号。

20. 二、修改基本表 1. 一般格式： ALTER TABLE <表名> [ ADD <新列名> <数据类型> [ 完整性约束 ] ] [ DROP <完整性约束名> ] [ MODIFY <列名> <数据类型> ]； 其中： • <表名>：要修改的基本表 • ADD子句：增加新列和新的完整性约束条件 • DROP子句：删除指定的完整性约束条件 • MODIFY子句：用于修改列名和数据类型

21. 2. 修改基本表示例 例：向Student表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD Scome DATE； 注：不论基本表中原来是否已有数据，新增加的列一律为空值。 例：将年龄的数据类型改为半字长整数。 ALTER TABLE Student MODIFY Sage SMALLINT； 注：修改原有的列定义有可能会破坏已有数据。

22. 2.修改基本表示例(续) 例： 删除学生姓名必须取唯一值的约束。 ALTER TABLE Student DROP UNIQUE(Sname)； 删除属性列 SQL没有提供删除属性列的语句；用户只能间接实现该功能 间接删除： • 把表中要保留的列及其内容复制到一个新表中 • 删除原表 • 再将新表重命名为原表名

23. *3. SQL Server的Alter tabel语句简单格式 ALTER TABLE table { ALTER COLUMNcolumn_name { new_data_type [NULL|NOT NULL]} --修改列定义 | ADD --添加列 { [ < column_definition > ] | column_name AS computed_column_expression} [ ,...n ] | DROP { [ CONSTRAINT ] constraint_name --删除约束 | COLUMNcolumn --删除列 } [ ,...n ]

24. *4. SQL Server的Alter tabel语句格式示例 例: 创建一个雇员信息表 , 然后在表中增加一个salary列，删除age列，修改memo列的类型。 CREATE TABLE employees ( id char(8) PRIMARY KEY, name char(20) NOT NULL, department char(20) NULL, memo char(30) NULL, age int NULL ) ALTER TABLE employees ADD salary int NULL ALTER TABLE employees DROP COLUMN AGE ALTER TABLE employees ALTER COLUMN memo varchar(100) NULL

25. *三、 SQL Server的约束 SQL Server 通过限制字段中的数据、记录中的数据和表之间的数据来保证数据的完整性，这种限制称为约束。 SQL Server中有5种约束: 主键约束(primary key constraint) 唯一性约束(unique constraint) 检查约束(check constraint) 默认约束(default constraint) 外部键约束(foreign key constraint)

26. 1. 主键约束 • 主键的特点：没有重复值；不允许NULL值 • 创建主键后，SQL Server将为主键创建一个唯一索引，以提高用主键查询的速度。 • 一个主键约束可以： • 作为表定义的一部分在创建表时创建。 • 添加到尚没有主键约束的表中。 • 如果已有主键约束，则可进行修改或删除。

27. 使用Transact-SQL语句添加主键约束 语法： [CONSTRAINT constraint_name] PRIMARY KEY [CLUSTERED|NONCLUSTERED] (column_name[,…n]) constraint_name: 指定约束的名称，约束的名称在数据库中应该是唯一的。如果不指定，则系统会自动生成一个约束名。 CLUSTERED|NONCLUSTERED: 用于指定索引的类型，即聚集索引或者非聚集索引，默认值为CLUSTERED。 column_name: 指定主键的列名。主键最多由16个列组成。

28. 约束名称 添加主键约束示例 例：在创建产品信息表时，指定产品编号为主键，并且创建一个聚集索引。 CREATE TABLE products ( id char(10) NOT NULL, name char(20) NOT NULL, price money DEFAULT 0.01, quantity smallint NULL --表级约束 CONSTRAINT pk_id --可以省略 PRIMARY KEY CLUSTERED (id) )

29. 添加主键约束示例 上例的另一种写法（列级约束）。 CREATE TABLE products ( id char(10) CONSTRAINT pk_id PRIMARY KEY CLUSTERED, name char(20) NOT NULL, price money DEFAULT 0.01, quantity smallint NULL ) 或省略成: id char(10) PRIMARY KEY 则: 系统自动生成约束名称 默认为CLUSTERED。

30. 删除或修改主键约束 语法: ALTER TABLE table DROP CONSTRAINT constraint_name 例: 删除上例创建的主键约束 ALTER TABLE products DROP CONSTRAINT pk_id 若要使用 Transact-SQL 修改主键，必须先删除现有的主键约束，然后再用新定义重新创建主键。 例: 重新创建上例的主键约束 ALTER TABLE products ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (id)

31. 使用企业管理器创建、修改或删除主键约束 右击数据表→选择“设计表” →打开对话框：

32. 2. 唯一性约束 唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。当使用唯一性约束时，需要考虑以下几个因素: • 使用唯一性约束的字段允许为空值。 • 一个表中允许有多个唯一性约束。 • 可以给多个字段定义唯一性约束。 • 唯一性约束用于强制在指定字段上创建一个唯一性索引。 • 缺省情况下，创建的索引类型为非聚集索引。

33. 使用Transact-SQL语句创建唯一性约束 语法： [CONSTRAINT constraint_name] UNIQUE [CLUSTERED|NONCLUSTERED] (column_name[,…n])

34. 创建唯一性约束示例 例:在创建tnumber表时，指定nam字段具有唯一性。 CREATE TABLE tnumber ( nam char(10), tno char(15), email char(20), CONSTRAINT uk_name UNIQUE(nam) ) --表级约束

35. 创建唯一性约束示例 上例的另一种写法。 CREATE TABLE tnumber ( nam char(10) CONSTRAINT uk_name UNIQUE, tno char(15), email char(20) ) --列级约束 或： CREATE TABLE tnumber ( nam char(10) UNIQUE, tno char(15), email char(20 ) --系统自动给约束命名

36. 删除或修改唯一性约束 例:去除上例建立的nam字段的唯一性约束。 ALTER TABLE tnumber DROP CONSTRAINT uk_name 例: 再次添加nam字段的唯一性约束。 ALTER TABLE tnumber ADD CONSTRAINT uk_name UNIQUE(nam)

37. 使用企业管理器创建、修改或删除唯一性约束

38. 3. 检查（CHECK）约束 • 检查约束用于定义列中可接受的数据值。 • 可以将 CHECK 约束应用于多个列。 • 在现有表中添加 CHECK 约束时，该约束可以仅作用于新数据也可以同时作用于现有的数据。默认设置为同时作用于现有数据和新数据。

39. 使用Transact-SQL语句创建检查约束 语法： CONSTRAINT constraint_name CHECK (logical_expression) logical_expression: 约束表达式

40. 定义约束表达式 约束表达式可以包括关系表达式或布尔表达式，用于对指定列的数据进行约束。 例:设学生成绩表中有一个名为math的列，该列要求大于等于0 且小于等于100,约束表达式为： math >= 0 and math <=100 例:设 authors 表中有一个 zip 列，该列要求 5 位数字的字符串。约束表达式为： zip LIKE '[0-9][0-9][0-9][0-9][0-9]' 例:设student表限制所有sex列只接受male和female，约束表达式为： sex IN ('male','female')

41. 创建检查约束示例 例: 创建一个student表，其性别字段只能接受“F”或者“M”，并且为phonenum字段创建检查约束，限制只能输入类似(010)80798654格式的数据。 CREATE TABLE student ( id char(8), name char(8), sex char(2), phonenum char(13), CONSTRAINT chk_sex CHECK(sex in ('F','M')), CONSTRAINT chk_phonenum CHECK(phonenum like '(010)[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') )

42. 删除或修改CHECK约束 例: 禁用上例建立的sex字段的CHECK约束。 ALTER TABLE student NOCHECK CONSTRAINT chk_sex 例: 恢复sex字段的CHECK约束 ALTER TABLE student CHECK CONSTRAINT chk_sex 例: 删除sex字段的CHECK约束 ALTER TABLE student DROP CONSTRAINT chk_sex 注: 若要使用 Transact-SQL 修改 CHECK 约束，必须首先删除已有的 CHECK 约束，然后再通过新定义重新创建。

43. 删除或修改CHECK约束示例 例: 给student表的id字段添加CHECK约束，使该字段只能接受1到100之间的数。 ALTER TABLE student ADD CONSTRAINT chk_id CHECK(id >=1 AND id <=100) 例: 重新添加sex字段的CHECK约束，添加时对表中现有的数据进行检查。 ALTER TABLE student WITH CHECK ADD CONSTRAINT chk_sex CHECK (sex in ('F','M')) 如果表中的sex字段存在不满足新约束的内容，执行以上语句将出错。 WITH CHECK WITH NOCHECK

44. 使用企业管理器创建、修改或删除检查约束

45. 4. 默认约束 默认约束用于为某一列定义一个默认值，即当用户没有在某一列中输入值时，则将所定义的值提供给这一列。使用默认约束时，应注意以下几点: • 每个字段只能定义一个默认约束。 • 如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断。 • 不能给带有IDENTITY属性的字段设默认约束。

46. 使用Transact-SQL语句创建默认约束 语法： [CONSTRAINT constraint_name] DEFAULT constraint_expression [FOR column_name] --用于ALTER 例: 在学生信息库中创建一个student表，sex字段默认为F，phonenum字段默认为(010)00000000. CREATE TABLE student ( id char(8), name char(8), sex char(2) CONSTRAINT aa DEFAULT 'F', phonenum char(13) CONSTRAINT bb DEFAULT '(010)00000000' )

47. 删除或修改默认约束 例: 删除上例建立的默认约束 ALTER TABLE student DROP CONSTRAINT aa,bb 例: 修改student表的默认约束 ALTER TABLE student ADD CONSTRAINT aa DEFAULT 'f' FOR sex ALTER TABLE student ADD CONSTRAINT bb DEFAULT '(010)11111111' FOR phonenum 注: 如果列已有默认值，必须除去旧默认值后才能添加新默认值。

48. 使用企业管理器创建、修改或删除默认约束 方法一：直接在定义列时定义

49. 使用企业管理器创建、修改或删除默认约束 方法二：创建默认约束，再绑定到指定的列

50. 例: 给同一个字段定义多种约束。 CREATE TABLE student ( id char(8) PRIMARY KEY, name char(8) UNIQUE, sex char(2)CONSTRAINT aa DEFAULT 'M', phonenum char(13) CONSTRAINT bb DEFAULT '(010)00000000', --以下是表级约束 CONSTRAINT chk_sex CHECK(sex in ('F','M')), CONSTRAINT chk_phonenum CHECK(phonenum like '(010)[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') )