表的创建与约束机制.ppt

1、数 据 库 原 理 及 应 用 （SQL Server 2005）,授课教师：* 2019年7月27日,第7章 表的创建与约束机制,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/ htt

2、p:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/,2019/7/27,第7章 表的创建与约束机制,任务目标： 掌握T-SQL语句创建表的方法； 掌握使用SSMS创建表的方法； 掌握T-SQL语句对表结构修改的方法； 掌握使用SSMS修改表结构的方法； 掌握T-SQL语句对表中数据进行操作； 会利用SSMS对表中数据进行操作； 理解并掌握

3、5种约束机制基本概念； 会使用T-SQL语句和利用SSMS创建约束的方法。,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/ http:/www.ntn- http:/ http:/ ht

4、tp:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/,2019/7/27,第7章 表的创建与约束机制,7.1 数据表的基本概念 7.2 表的设计与创建 7.3 管理表 7.4 数据的插入、更新和删除 7.5 SQL Server约束机制 本章小结 习题与实验,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ ht

5、tp:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/ http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/

6、http:/ http:/ http:/,2019/7/27,7.1 数据表的基本概念,表是用来存储数据和操作数据的逻辑结构。关系数据库中的所有数据都存储在表中。因此表是SQL Server 数据库中最为重要的组成部分。表是由行和列组成，最多可以有1024列，每行对应实体集的一个实体，也称为记录，每列代表一个属性，也称为字段。 SQL Server中，表分为系统表和用户表两类。 1系统表 默认情况下，每个数据库都有一组系统表，系统表主要记录所有服务器活动的信息，大多数系统表的表名以sys开头。系统表中的信息组成了系统使用的数据字典。任何用户都不能直接修改系统表，也不允许直接访问系统表中的信息，

7、如要访问其中的内容，最好通过系统存储过程或系统函数来访问。 2用户表 用户表是由用户自定义建立的表，用来存储用户特定的数据，又可分为永久表和临时表两种。,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag-

8、 http:/ http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/,2019/7/27,7.1 数据表的基本概念,（1）永久表 永久表存储在用户数据库中，用户数据通常存储在永久表中，如果用户没有删除永久表，永久表及其存储过程将永久存在。 （2）临时表 临时表存储在tempdb数据库中，当不再使用时，系统会自动删除。临时表又可分为本地

9、临时表和全局临时表两种。 本地临时表：表名以#开头，仅对当前连接数据库的用户有效，当用户断开连接时，本地临时表自动删除。 全局临时表：表名以#开头，对所有连接数据库的用户有效，当所有用户断开连接，全局临时表才自动删除。,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.s

10、kf- http:/www.nsk- http:/www.fag- http:/ http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/,2019/7/27,7.2 表的设计与创建,设计表时，要事先确定需要什么样的表，表中有哪些数据，表中各字段的数据类型及其属性，建表一般经过定义表结构、设置约束、输入记录等步骤，其中设置约束既可以在定义

11、表结构时进行，也可以在定义表结构完成之后进行。 1定义表结构 确定表的各列的列名及其数据类型、数据长度、是否允许为空等。 定义表结构时要注意：,7.2.1表的设计,2019/7/27,7.2 表的设计与创建,（1）允许空：决定某列在表中是否允许为空值。空值是不等于零、空白或零长度的字符串。 （2）默认值：当在表中插入该列为空值的行时，用此默认值。设置该值能够起到默认输入的作用，减少输入数据的工作量。如设置性别的默认值为“男”。 （3）标识列：设置为标识的列，可以有系统自动操作计数（自动编号），不用用户输入，对于一些具有递增或递减自动编号性质的列，如订单号、发票号等可以设置此附加属性。将一个字段

12、设置为标识列，其数据类型必须是以下类型之一：int、bigint、smallint、tinyint、或小数位数为0的decimal、numeric字段。该字段不允许为空，且不能有默认值。 （4）计算列：计算字段是一个虚拟的字段，它并未将计算结果实际存储在表中，而只是在运行时才计算出结果。在设置计算字段时，不需要指定该字段的数据类型，当保存表结构，SQL Server会自动决定计算字段的数据类型。,7.2.1表的设计,2019/7/27,7.2 表的设计与创建,2设置约束 约束定义了关于允许什么数据进入数据库的规则，是分配给表或表中某列的一个属性。使用约束，主要目的在于防止列中出现非法数据，可以

13、自动维护数据库中的数据完整性。数据完整性是指数据的正确性、完备性和一致性，是衡量数据库质量好坏的重要标准。使用INSERT、DELETE、UPDATE语句修改数据库内容时，数据的完整性可能会遭到破坏。可能会将无效的数据添加到表中，如将学生考试成绩输入成负数。为了解决类似的问题，SQL Server提供了对数据库中表、列实施数据完整性的方法。 完整性的类型主要包括：,7.2.1表的设计,2019/7/27,7.2 表的设计与创建,（1）域完整性 域完整性是指一个列的输入有效性，是否允许空值。通常使用有效性检查强制域完整性，也可以通过限定列中允许的数据类型、格式或可能值的范围来强制域完整性。（2）

14、实体完整性 实体完整性是指保证表中所有的行唯一。实体完整性要求表中的所有行都有一个唯一标识符。这个唯一标识符可能是一列，也可能是几列的组合，称之为主键。也就是说，表中主键在所有行上必须取值唯一且不能为空值。,7.2.1表的设计,2019/7/27,7.2 表的设计与创建,（3）参照完整性 参照完整性也叫引用完整性。参照完整性总是保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。 在SQL Server中约束是强制数据完整性的首选方法，它是强制数据完整性的ANSI标准方法。每种数据完整性类型，使用单独的约束类型进行强制。约束确保在列中录入有效的数据值，并且能维护表之间的关联关系。具体

15、的约束类型及实现方法将在7.5节做详细介绍。 3输入记录 表结构设计好之后，就可以向表中输入数据了。输入记录时，必须遵循所设置的约束条件，否则，服务器将拒绝接受所输入的不非法数据。 按照学生成绩管理系统数据库（SGMS）实际问题的需要，设计了9张表，分别为：student、course、coursetype、teacher、users、department、class、speciality、grade，表结构见附录。,7.2.1表的设计,2019/7/27,7.2 表的设计与创建,用T-SQL语句创建表比使用SSMS创建表更加直接、有效。实际的应用系统中，通常用CREATE TABEL语句创建

16、表，其基本语法格式如下：CREATE TABEL 表名(列名 数据类型 NOT NULL|NULL)上面格式中，包含参数的含义如下： 表名：是所创建的表的名称，在一个数据库内表名必须唯一。 列名：列名在一个表内列名必须唯一。 数据类型：可以使用系统数据类型，也可以使用用户定义的数据类型。对于需要给定数据最大长度的类型，在定义时要给出长度。,7.2.2 使用T-SQL语句创建表,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ h

17、ttp:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/ http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/,2019/7/27,7.2 表的设计与创

18、建,任务7-1 在SGMS数据库中创建student表。 任务分析：创建表时，要给出表的名称、字段名、字段数据类型等有关信息。在T-SQL中，可以用CREATE TABEL命令来创建表，表中列的定义必须用括号括起来。一个表中最多可以包含1024列。本例中可以按照student表结构，依次定义字段的名称、数据类型及是否允许为空。实现代码如图7-1所示。,7.2.2 使用T-SQL语句创建表,图7-1 创建student表,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/

19、http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/ http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:

20、/,2019/7/27,7.2 表的设计与创建,任务7-2 使用SSMS创建course表。 任务分析：使用SSMS创建表，首先必须打开SGMS数据库，然后再打开“表设计器”，在“表设计器”窗口中依次定义表的字段名、数据类型、是否允许为空就可以了。 利用SSMS创建表步骤如下： （1）在“对象资源管理器”的树型目录中找到要建表的数据库SGMS，展开该数据库。 （2）选择“表”，右击鼠标，在弹出的快捷菜单中选择“新建表（N）.”命令，打开表设计器，如图7-2所示。,7.2.3使用SSMS创建表,图7-2 表设计器,http:/www.ort- http:/ http:/ http:/ http:

21、/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/ http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/

22、http:/ http:/ http:/ http:/ http:/ http:/ http:/,2019/7/27,7.2 表的设计与创建,（3）表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的第一行对应设置一列，对每一列都需要进行如下设置： 列名：为每一列设定一个列名。 数据类型：数据类型是一个下拉列表框，其中包含了所有的系统数据类型和用户自定义的数据类型。用户可根据需要来选择数据类型和长度。 允许空：单击该行的复选框，可以切换是否允许该列为空值的状态。打勾表示允许为空值，空白表示不允许为空值，默认状态下是允许为空值的。 表设计器下半部分是特定列的详细属性，包括是否是INDE

23、NTITY列、是否使用默认值等。表设计器右半部分是表的详细属性，这里可以设置表的名称、表所在的文件组等信息。,7.2.3使用SSMS创建表,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/

24、 http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/,2019/7/27,7.2 表的设计与创建,（4）逐个定义好表中的列，单击工具栏中的“保存”按钮。若没有在表设计器中给出表的名称，会出现保存对话框，提示用户输入表的名称，如图7-3所示。单击“确定”按钮，course表就建立完成。,7.2.3使用SSMS创建表,图7-3 保存表结

25、构,2019/7/27,7.3管理表,在表创建完成以后，根据实际的需要，有可能会对表原有的设计作相应的修改，比如增加一列，删除一列，或者对相应的某一列的数据类型做些调整等等。用户可以使用T-SQL脚本管理表，也可以使用SSMS管理表，下面分别介绍。,2019/7/27,7.3管理表,1使用T-SQL语句显示表信息 任务7-3 查看表student的基本信息。 任务分析：可以使用系统存储过程sp_help来查看表结构，包括表的所有者、类型（系统表还是用户表）、创建时间、表上每一列的名称、数据类型、表上定义的索引及约束等。本例实现代码如图7-4所示。,7.3.1使用T-SQL 脚本管理表,图7-4

26、 查看student表的结构,2019/7/27,7.3管理表,2使用T-SQL语句修改表结构 使用ATLER TABEL语句可以对表结构进行修改，具体包括增加列、删除列以及修改列定义。 （1）增加列 向表中增加列，通过ADD子句实现，其基本语法格式为：ATLER TABLE 表名ADD 列名 列的描述参数：列的描述中要给出列的数据类型，是否为空。,7.3.1使用T-SQL 脚本管理表,2019/7/27,7.3管理表,任务7-4 向student表中增加列Email列。 任务分析：向表中增加一列时，应使新增加的列有默认值或允许为空值，SQL Server将向表中已经存在的行填充新增加列的默认

27、值或空值。如果既没有提供默认值也不允许为空值，那么新增加列的操作将会出错，因为不知道该怎样处理哪些已经存在的行。实现代码如图7-5所示。,7.3.1使用T-SQL 脚本管理表,图7-5 增加Email列,【注意】可以一次向表中增加多列，多列之间用逗号分开。,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/

28、www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/ http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/,2019/7/27,7.3管理表,（2）删除列 对于一张表，要删除表中某列可以用DROP COLUMN子句实现，其基本语法格式如下：ATLER TABLE 表名D

29、ROP COLUMN 列名 任务7-5删除student表中Email列 任务分析：删除表中的列，使用T-SQL中提供的DROP COLUMN子句实现，实现代码如图7-6所示。,7.3.1使用T-SQL 脚本管理表,图7-6 删除student表中Email列,http:/www.ort- http:/ http:/ http:/ http:/ http:/www.light- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/ http:/www.ntn- http:/ http:/ http:/ http:/ http:/ http:/www.xunchi- http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/ http:/,