第5章数据库表的创建与管理

1、车 蕾 北京信息科技大学信息管理学院第第5 5章章 表的创建与管理表的创建与管理主要内容主要内容25.1 表的概述表的概述5.2 创建表创建表5.3 管理表管理表5.4 数据完整性约束的创建和管理数据完整性约束的创建和管理5.7 索引索引5.1 表的概述表的概述SQL Server中的表分为三类：中的表分为三类： 系统表系统表 用户自定义表用户自定义表 临时表临时表 3系统表系统表系统表是系统表是SQL Server数据库引擎使用的表数据库引擎使用的表。系统表中存储了定义服务器配置及其所有系统表中存储了定义服务器配置及其所有表的数据。表的数据。系统表的格式取决于系统表的格式取决于SQL Ser

2、ver内部体系内部体系结构的要求。结构的要求。系统表不允许用户进行修改的，但会随着系统表不允许用户进行修改的，但会随着SQL Server版本的不同而不同。版本的不同而不同。4用户自定义表用户自定义表用户自定义表是指用户创建的表，用户自用户自定义表是指用户创建的表，用户自定义表是定义表是SQL Server中最常见的表。中最常见的表。通常，该类表中的字段包含了用户所需的通常，该类表中的字段包含了用户所需的数据格式，而表中记录的是用户的数据。数据格式，而表中记录的是用户的数据。用户可以根据所拥有的权限创建、修改和用户可以根据所拥有的权限创建、修改和删除用户自定义表。删除用户自定义表。5临时表临时

3、表临时表存储在临时表存储在tempdb中，而不是存储在中，而不是存储在用户的数据库中。用户的数据库中。与用户自定义表不同的是，临时表会在用与用户自定义表不同的是，临时表会在用户不再使用时，自动被户不再使用时，自动被SQL Server删除删除。在在SQL Server 2008中，临时表有两种类中，临时表有两种类型，即本地临时表和全局临时表。型，即本地临时表和全局临时表。6本地临时表本地临时表 在在SQL Server中，创建本地临时表和普中，创建本地临时表和普通表相同。通表相同。本地临时表的名称以本地临时表的名称以“#”开头。开头。 对于创建本地临时表的用户来说，本地临时对于创建本地临时表的

4、用户来说，本地临时表是可见的，而其他用户却不能访问和使用表是可见的，而其他用户却不能访问和使用该本地临时表；该本地临时表； 当用户从当用户从SQL Server实例断开连接时，所实例断开连接时，所创建的本地临时表也将被创建的本地临时表也将被SQL Server删除删除。 7全局临时表全局临时表在在SQL Server中，创建全局临时表和普通表相中，创建全局临时表和普通表相同。同。全局临时表的名称以全局临时表的名称以“#”开头。开头。全局临时表对所有连接的用户都是可见的：全局临时表对所有连接的用户都是可见的： 本地临时表仅对表的创建者可见；本地临时表仅对表的创建者可见； 只有使用该全局临时表的所

5、有用户都断开连接时，只有使用该全局临时表的所有用户都断开连接时，SQL Server才能自动删除该全局临时表。才能自动删除该全局临时表。全局临时表和本地临时表的区别在于本地临时表全局临时表和本地临时表的区别在于本地临时表只和创建该表的用户相关，而全局临时表却与使只和创建该表的用户相关，而全局临时表却与使用该表的所有用户有关。用该表的所有用户有关。85.2 创建表创建表创建表就是定义表中各个列的结构：列名、数创建表就是定义表中各个列的结构：列名、数据类型、约束等据类型、约束等创建表的三种方式：创建表的三种方式： 使用使用SQL Server Management Studio创建表创建表 使用模

6、板创建表使用模板创建表 使用使用T-SQLT-SQL语句创建表语句创建表9表的结构表的结构列名：列的名称是人们为列取的名字列名：列的名称是人们为列取的名字 SQL Server中支持中文和英文名中支持中文和英文名 数据类型：数据类型： 系统数据类型系统数据类型 用户自定义数据类型用户自定义数据类型约束：约束： 主关键字约束主关键字约束 外部关键字约束外部关键字约束 取值范围约束取值范围约束 列取值是否允许为空；列取值是否允许为空； 列取值是否允许重复；列取值是否允许重复； 列取值是否有默认值。列取值是否有默认值。 列取值是否唯一。列取值是否唯一。101.使用使用SQL Server Manag

7、ement Studio创创建表建表122.使用模板创建表使用模板创建表13 3.使用使用T-SQL语句创建表语句创建表CREATE TABLE ( , ,.n ,.n)14使用使用T-SQL语句创建语句创建BankT (银行表银行表)、LegalEntityT（法人表）和（法人表）和LoanT（贷款（贷款表）的代码如下：表）的代码如下：USE LoanDB -指定数据库指定数据库go-创建银行表创建银行表CREATE TABLE BankT( Bno char(5) PRIMARY KEY, -使用列级约束定义主关键字，也可以使用表级约束定义使用列级约束定义主关键字，也可以使用表级约束定义B

8、name nvarchar(10) NOT NULL, -非空非空Btel char(8) ) go15-创建法人表创建法人表CREATE TABLE LegalEntityT(Eno char(3) ,Ename nvarchar(15),Enature nchar(2), Ecapital int,Erep nchar(4),PRIMARY KEY(Eno) -使用表级约束定义主关键字，也可以使用列级约束定义使用表级约束定义主关键字，也可以使用列级约束定义)go16-创建贷款表创建贷款表CREATE TABLE LoanT(Eno char(3),Bno char(5),Ldate sma

9、lldatetime,Lamount int,Lterm smallint,PRIMARY KEY(Eno,Bno,Ldate) -对多列做主关键字的情况只能用表级约束定义对多列做主关键字的情况只能用表级约束定义)go175.3 管理表管理表修改表结构修改表结构删除表删除表重命名表重命名表19 使用SQL Server Management Studio修改表结构 使用T-SQL语句修改表结构ALTER TABLE ADD | ADD | DROP COLUMN | DROP | ALTER COLUMN 5.3.1 5.3.1 修改表结构修改表结构5.3.2 删除表删除表删除表的顺序删除表的

10、顺序使用使用SQL Server Management Studio删删除表除表使用使用T-SQL语言删除表语言删除表20格式：格式：DROP TABLE 例：例：DROP TABLE LoanDB5.4 数据完整性约束的创建和管理数据完整性约束的创建和管理数据完整性是指保证数据正确的特性数据完整性是指保证数据正确的特性 。22列级约束列级约束表级约束表级约束5.4.1 实现数据完整性约束实现数据完整性约束实现方法实现方法 T-SQL SQL Server Management Studio245.4.1.1 实体完整性约束实体完整性约束实体完整性的目的实体完整性的目的是要保证关系中的每个元组

11、都是要保证关系中的每个元组都是可识别和唯一的。是可识别和唯一的。PRIMARY KEY定义主关键字约束要注意：定义主关键字约束要注意： 每个表只能有一个每个表只能有一个PRIMARY KEY约束；约束； 用用PRIMARY KEY约束的列的取值必须是不重复的约束的列的取值必须是不重复的（对于由多列构成的主关键字，是这些主关键字列（对于由多列构成的主关键字，是这些主关键字列组合起来的取值不能重复），并且不允许为空值。组合起来的取值不能重复），并且不允许为空值。251、创建表时定义主关键字、创建表时定义主关键字 列级约束的定义语法格式为：列级约束的定义语法格式为： CREATE TABLE ( C

12、ONSTRAINT约束名约束名 PRIMARY KEY .)表级约束的定义语法格式为：表级约束的定义语法格式为： CREATE TABLE (, . CONSTRAINT约束名约束名 PRIMARY KEY (,.n)262、修改表时添加主关键字、修改表时添加主关键字 语法格式为：语法格式为： ALTER TABLE 表名表名 ADD CONSTRAINT约束名约束名 PRIMARY KEY ( , n)假如假如LoanT表在创建时没有定义主关键字（表在创建时没有定义主关键字（Eno,Bno,Ldate），请为其添加。），请为其添加。 ALTER TABLE LoanT ADD PRIMARY

13、 KEY(Eno,Bno,Ldate)273、在、在SQL Server Management Studio中设置主关键字中设置主关键字 284、系统对实体完整性约束的检查、系统对实体完整性约束的检查插入操作插入操作 ？删除操作删除操作 ？更新操作更新操作 ？2.参照完整性约束参照完整性约束参照完整性的参照完整性的目的目的是要保证外部关键字的取值是要保证外部关键字的取值不超出所参照的主关键字的取值范围。不超出所参照的主关键字的取值范围。FOREIGN KEY定义定义FOREIGN KEY约束时要注意：外部关键约束时要注意：外部关键字列参照的列必须是有字列参照的列必须是有PRIMARY KEY约

14、束或约束或者者UNIQUE约束的列。约束的列。 30创建表时定义外部关键字创建表时定义外部关键字 列级约束的定义语法格式为：列级约束的定义语法格式为：CREATE TABLE (. CONSTRAINT约束名约束名 FOREIGN KEY REFERENCES 被参照表表名被参照表表名() ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT . )31创建表时定义外部关键字创建表时定义外部关键字 表级约束的定义语法格式为：表级约

15、束的定义语法格式为： CREATE TABLE (, . CONSTRAINT约束名约束名 FOREIGN KEY () REFERENCES被参照表表名被参照表表名() ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT)32例：创建例：创建LoanT表时定义外部关键字。其中表时定义外部关键字。其中Bno为参照为参照BankT表的外部关键字，表的外部关键字，Eno为参照为参照LegalEntityT表表的外部关键字。的外部关键

16、字。CREATE TABLE LoanT(Eno char(3),Bno char(5),Ldate smalldatetime ,Lamount int,Lterm smallint,PRIMARY KEY(Eno,Bno,Ldate), -只能用表级约束只能用表级约束FOREIGN KEY(Bno) REFERENCES BankT(Bno), -表级约束，也可以放在列级表级约束，也可以放在列级FOREIGN KEY(Eno) REFERENCES LegalEntityT(Eno) -表级约束，也可以放在列级表级约束，也可以放在列级)33修改表时添加外部关键字修改表时添加外部关键字语法格

17、式为：语法格式为： ALTER TABLE ADD CONSTRAINT约束名约束名 FOREIGN KEY () REFERENCES被参照表表名被参照表表名() ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT )34假设创建假设创建LoanT表时未定义外部关键字（如表时未定义外部关键字（如5.2.3 节），请为其添加。其中节），请为其添加。其中Bno为参照为参照BankT表表的外部关键字，的外部关键字，Eno为参照为参照

18、LegalEntityT表的表的外部关键字。外部关键字。 ALTER TABLE LoanT ADD FOREIGN KEY(Bno) REFERENCES BankT(Bno) ALTER TABLE LoanT ADD FOREIGN KEY(Eno) REFERENCES LegalEntityT(Eno)在在SQL Server Management Studio中设中设置外部关键字置外部关键字 3637测试（测试（SQL Server2008）部门号部门号部门名部门名B1XB2YB3Z职工号职工号 职工名职工名 部门号部门号Z1AB1Z2CB238强制外强制外键约束键约束删除删除/更

19、新更新规则规则被参照表（删除、更新被外部关键字引用的值被参照表（删除、更新被外部关键字引用的值的记录操作）的记录操作）否否无操作无操作层叠层叠设置空设置空设置默认设置默认值值系统不检查参照完整性约束；系统不检查参照完整性约束；执行操作执行操作是是无操作无操作系统检查参照完整性约束；系统检查参照完整性约束；不执行操作不执行操作层叠层叠系统检查参照完整性约束；系统检查参照完整性约束；被参照表和参照表级联更新或删除被参照表和参照表级联更新或删除设置空设置空系统检查参照完整性约束；系统检查参照完整性约束；被参照表更新或删除；参照表相应字段设置为被参照表更新或删除；参照表相应字段设置为空。空。设置默认设

20、置默认值值系统检查参照完整性约束；系统检查参照完整性约束；被参照表更新或删除；参照表相应字段设置为被参照表更新或删除；参照表相应字段设置为默认值。默认值。系统对参照完整性约束的检查系统对参照完整性约束的检查对参照表的操作对参照表的操作 插入操作插入操作 ？ 更新操作更新操作 ？ 删除操作删除操作 ？对被参照表的操作对被参照表的操作 插入操作插入操作 ？ 更新操作更新操作 ？ 删除操作删除操作 ？3.唯一值约束唯一值约束唯一值约束的唯一值约束的目的目的是保证在非主关键字的是保证在非主关键字的一列或多列组合中不输入重复的值。一列或多列组合中不输入重复的值。UNIQUE定义定义UNIQUE约束时注意

21、如下事项：约束时注意如下事项： 允许有一个空值；允许有一个空值； 在一个表中可以定义多个在一个表中可以定义多个UNIQUE约束；约束； 可以在一个列或者多个列上定义可以在一个列或者多个列上定义UNIQUE约约束。束。40创建表时定义创建表时定义UNIQUE约束约束 列级约束的定义语法格式为：列级约束的定义语法格式为： CREATE TABLE (. CONSTRAINT约束名约束名 UNIQUE . )表级约束的定义语法格式为：表级约束的定义语法格式为： CREATE TABLE (, . CONSTRAINT约束名约束名 UNIQUE ( , n) )41例：要求例：要求LegalEntit

22、yT表中的表中的Ename（法人名称）不能（法人名称）不能取重复值，请在创建取重复值，请在创建LegalEntityT表时实现此约束。表时实现此约束。CREATE TABLE LegalEntityT(Eno char(3) ,Ename nvarchar(15) UNIQUE, -取值唯一取值唯一Enature nchar(2), Ecapital int,Erep nchar(4),PRIMARY KEY(Eno) -使用表级约束定义主关键字使用表级约束定义主关键字)42修改表时定义修改表时定义UNIQUE约束约束语法格式为：语法格式为：ALTER TABLE 表名表名 ADD CONST

23、RAINT 约束名约束名 UNIQUE (, n)例：要求例：要求LegalEntityT表中的表中的Ename（法人名称）不（法人名称）不能取重复值（假设创建能取重复值（假设创建LegalEntityT表时未定义此约表时未定义此约束）。束）。 ALTER TABLE LoanT ADD UNIQUE(Ename)43在在SQL Server Management Studio中设中设置置UNIQUE约束约束 44系统对唯一值约束的检查系统对唯一值约束的检查插入操作插入操作 ？更新操作更新操作 ？删除操作删除操作 ？4.默认值约束默认值约束默认值约束的默认值约束的目的目的是为列提供默认值。即当

24、在是为列提供默认值。即当在表中插入数据时，如果没有为默认值约束的列表中插入数据时，如果没有为默认值约束的列提供值，则系统自动使用默认值约束定义的默提供值，则系统自动使用默认值约束定义的默认值。认值。 DEFAULT使用使用DEFAULT约束时要注意：约束时要注意： 只在向表中插入数据时才检查只在向表中插入数据时才检查DEFAULT约束；约束； 每个列只能有一个每个列只能有一个DEFAULT约束；约束； 允许使用系统提供的值作为默认值；允许使用系统提供的值作为默认值； 不能与不能与IDENTITY属性一起使用。属性一起使用。46创建表时定义创建表时定义DEFAULT约束约束列级约束定义语法格式为

25、：列级约束定义语法格式为： CREATE TABLE (. CONSTRAINT约束名约束名 DEFAULT 默认值默认值 . )47例：要求例：要求LoanT表中的表中的Ldate（贷款日期）的默认值为当前日期，（贷款日期）的默认值为当前日期，请在创建请在创建LoanT表时实现此约束。表时实现此约束。CREATE TABLE LoanT(Eno char(3),Bno char(5),Ldate smalldatetime DEFAULT GETDATE(), -只能用列级只能用列级约束约束Lamount int,Lterm smallint,PRIMARY KEY(Eno,Bno,Ldat

26、e), -只能用表级约只能用表级约束束FOREIGN KEY(Bno) REFERENCES BankT(Bno), -表级约束，也可以放在列级表级约束，也可以放在列级FOREIGN KEY(Eno) REFERENCES LegalEntityT(Eno) -表级约束，也可以放在列级表级约束，也可以放在列级)48修改表时定义修改表时定义DEFAULT约束约束语法格式为：语法格式为： ALTER TABLE 表名表名 ADD CONSTRAINT 约束名约束名 DEFAULT默认值默认值 FOR 列名列名例：要求例：要求LoanT表中的表中的Ldate（贷款日期）的默认值（贷款日期）的默认值为

27、当前日期，假设创建为当前日期，假设创建LoanT表时未定义此约束，请表时未定义此约束，请为其添加。为其添加。 ALTER TABLE LoanT ADD DEFAULT GETDATE() FOR Ldate 49在在SQL Server Management Studio中设中设置置DEFAULT约束约束 50系统对默认值约束的检查系统对默认值约束的检查插入操作？插入操作？更新操作？更新操作？删除操作？删除操作？5.检查约束检查约束检查约束目的是限制列的取值范围。检查约束目的是限制列的取值范围。CHECK使用使用CHECK约束时须注意：约束时须注意： 在执行在执行INSERT语句和语句和UP

28、DATE语句时系统语句时系统自动检查自动检查CHECK约束；约束； CHECK约束可以限制一个列的取值范围，约束可以限制一个列的取值范围，也可以限制同一个表中多个列之间的取值约也可以限制同一个表中多个列之间的取值约束关系。束关系。52创建表时定义创建表时定义CHECK约束约束列级约束的定义语法格式为：列级约束的定义语法格式为：CREATE TABLE (. CONSTRAINT约束名约束名 CHECK(逻辑表达式逻辑表达式) . )表级约束的定义语法格式为：表级约束的定义语法格式为： CREATE TABLE (, . CONSTRAINT约束名约束名 CHECK(逻辑表达式逻辑表达式) )5

29、3例：要求例：要求LegalEntityT表中的表中的Enature（经济性质）列的取值（经济性质）列的取值范围为范围为国营，私营，集体，三资国营，私营，集体，三资，请在创建，请在创建LegalEntityT表表时实现此约束。时实现此约束。CREATE TABLE LegalEntityT(Eno char(3) ,Ename nvarchar(15) UNIQUE, -取值唯一取值唯一Enature nchar(2) CHECK(Enature IN(国营国营,私营私营,集体集体,三资三资) ), -列级约束列级约束Ecapital int,Erep nchar(4),PRIMARY KEY

30、(Eno) ) -使用表级约束定义主关键字使用表级约束定义主关键字或者或者CREATE TABLE LegalEntityT(Eno char(3) ,Ename nvarchar(15) UNIQUE, -取值唯一取值唯一Enature nchar(2) , Ecapital int,Erep nchar(4),PRIMARY KEY(Eno), -使用表级约束定义主关键字使用表级约束定义主关键字CHECK(Enature IN(国营国营,私营私营,集体集体,三资三资) ) -表级约束表级约束)54例：已知关系模式：工作（工作证号，最高工资，最低工资例：已知关系模式：工作（工作证号，最高工资

31、，最低工资），限制最低工资必须小于等于最高工资，请按要求创建对），限制最低工资必须小于等于最高工资，请按要求创建对应的工作表。应的工作表。CREATE TABLE 工作表工作表 ( 工作证号工作证号 char(4) PRIMARY KEY, 最低工资最低工资 int， 最高工资最高工资 int， CHECK (最低工资最低工资= 最高工资最高工资) -多列之间的约束只能在表约束处定义多列之间的约束只能在表约束处定义 )55修改表时定义修改表时定义CHECK约束约束语法格式为：语法格式为： ALTER TABLE 表名表名 ADD CONSTRAINT 约束名约束名 CHECK(逻辑表达式逻辑表

32、达式)例：要求例：要求LegalEntityT表中的表中的Enature（经济性质）（经济性质）列的取值范围为列的取值范围为国营，私营，集体，三资国营，私营，集体，三资（假设创（假设创建建LegalEntityT表时未定义此约束）。表时未定义此约束）。 ALTER TABLE LegalEntity ADD CHECK(Enature IN(国营国营,私营私营,集体集体,三资三资) )56在在SQL Server Management Studio中设中设置置CHECK约束约束 57系统对检查约束的检查系统对检查约束的检查插入操作？插入操作？删除操作？删除操作？更新操作？更新操作？58管理数据

33、完整性约束管理数据完整性约束查看已定义的约束查看已定义的约束删除约束删除约束实现数据完整性汇总实现数据完整性汇总创建表时创建表时_列级列级创建表时创建表时_表级表级在已创建表上添加在已创建表上添加系统对完整性约束的检查系统对完整性约束的检查PRIMARY KEYPRIMARY KEY(只适合于单属性主关键字只适合于单属性主关键字)PRIMARY KEY(主关主关键字属性集键字属性集)ALTER TABLE ADD PRIMARY KEY(主主关键字属性集关键字属性集)INSERTUPDATEUNIQUEUNIQUEUNIQUE(列名集列名集)ALTER TABLE ADD UNIQUE(列名集

34、列名集)INSERTUPDATEFOREIGN KEYFOREIGN KEY(外部关键外部关键字字) REFERENCES 被参照被参照表表(主关键字主关键字)FOREIGN KEY(外部外部关键字关键字) REFERENCES 被参被参照表照表(主关键字主关键字)ALTER TABLE ADD FOREIGN KEY(外外部关键字部关键字) REFERENCES 被参照表被参照表(主关键字主关键字)参照表参照表:INSERTUPDATE被参照表被参照表:DELETE（4种选择）种选择）UPDATE （4种选择）种选择）DEFAULT DEFAULT 默认值默认值ALTER TABLE ADD

35、 DEFAULT 默认值默认值 FOR 列名列名INSERTCHECKCHECK(逻辑表达式逻辑表达式)(只适合于单列只适合于单列)CHECK(逻辑表达式逻辑表达式) ALTER TABLE ADD CHECK(逻辑表达逻辑表达式式)INSERTUPDATE索引索引在一个数据库表中检索数据时，如果采取逐行在一个数据库表中检索数据时，如果采取逐行扫描的方式对这个表中的所有记录进行检查，扫描的方式对这个表中的所有记录进行检查，就如同在一本厚厚的书中查找某个特定的词语就如同在一本厚厚的书中查找某个特定的词语一样，搜索效率十分低下。一样，搜索效率十分低下。如果在一个数据库中建立一个有效的索引，用如果在

36、一个数据库中建立一个有效的索引，用select语句检索数据时不用扫描整个表就能够语句检索数据时不用扫描整个表就能够找到所需要的记录，还能提高找到所需要的记录，还能提高update和和delete语句的执行速度，使数据库的性能得到改善。语句的执行速度，使数据库的性能得到改善。61索引索引索引的作用索引的作用625970164216索引表数据表由于索引页比较小，所以处理速度快，可以提高查询效率。索引键可以是表中的单个字段，也可以是索引键可以是表中的单个字段，也可以是由多个字段组合而成。由多个字段组合而成。63索引为数据库管理带来的优势索引为数据库管理带来的优势提高查询速度提高查询速度提高连接、提高连接、order by和和 group by执行的执行的速度速度查询优化器依靠索引起作用查询优化器依靠索引起作用强制实施行的唯一性强制实施行的唯一性64索引的分类索引的分类Sql server的索引主要分为两类：聚集索的索引主要分为两类：聚集索引和非聚集索引。引和非聚集索引。按照其他一些特性，还可以分为唯一索引按照其他一些特性，还可以分为唯一索引、视图索引等类别。、视图索引等类别。65聚集索引聚集索引