表的创建与约束机制

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,数 据 库 原 理 及 应 用,（,SQL Server 2005,）,授课教师：*,2024年9月16日,第,7,章 表的创建与约束机制,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,第,7,章 表的创建与约束机制,任务目标,：,掌握,T-SQL,语句创建表的方法；,掌握使用,SSMS,创建表的方法；,掌握,T-SQL,语句对表结构修改的方法；,掌握使用,SSMS,修改表结构的方法；,掌握,T-SQL,语句对表中数据进行操作；,会利用,SSMS,对表中数据进行操作；,理解并掌握,5,种约束机制基本概念；,会使用,T-SQL,语句和利用,SSMS,创建约束的方法。,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,第,7,章 表的创建与约束机制,7.1,数据表的基本概念,7.2,表的设计与创建,7.3,管理表,7.4,数据的插入、更新和删除,7.5 SQL Server,约束机制,本章小结,习题与实验,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.1 数据表的基本概念,表是用来存储数据和操作数据的逻辑结构。关系数据库中的所有数据都存储在表中。因此表是,SQL Server,数据库中最为重要的组成部分。表是由行和列组成，最多可以有,1024,列，每行对应实体集的一个实体，也称为记录，每列代表一个属性，也称为字段。,SQL Server,中，表分为系统表和用户表两类。,1,系统表,默认情况下，每个数据库都有一组系统表，系统表主要记录所有服务器活动的信息，大多数系统表的表名以,sys,开头。系统表中的信息组成了系统使用的数据字典。任何用户都不能直接修改系统表，也不允许直接访问系统表中的信息，如要访问其中的内容，最好通过系统存储过程或系统函数来访问。,2,用户表,用户表是由用户自定义建立的表，用来存储用户特定的数据，又可分为永久表和临时表两种。,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.1 数据表的基本概念,（,1,）永久表,永久表存储在用户数据库中，用户数据通常存储在永久表中，如果用户没有删除永久表，永久表及其存储过程将永久存在。,（,2,）临时表,临时表存储在,tempdb,数据库中，当不再使用时，系统会自动删除。临时表又可分为本地临时表和全局临时表两种。,本地临时表：表名以,#,开头，仅对当前连接数据库的用户有效，当用户断开连接时，本地临时表自动删除。,全局临时表：表名以,#,开头，对所有连接数据库的用户有效，当所有用户断开连接，全局临时表才自动删除。,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.2 表的设计与创建,设计表时，要事先确定需要什么样的表，表中有哪些数据，表中各字段的数据类型及其属性，建表一般经过定义表结构、设置约束、输入记录等步骤，其中设置约束既可以在定义表结构时进行，也可以在定义表结构完成之后进行。,1,定义表结构,确定表的各列的列名及其数据类型、数据长度、是否允许为空等。,定义表结构时要注意：,7.2.1表的设计,2024/9/16,7.2 表的设计与创建,（,1,）允许空：决定某列在表中是否允许为空值。空值是不等于零、空白或零长度的字符串。,（,2,）默认值：当在表中插入该列为空值的行时，用此默认值。设置该值能够起到默认输入的作用，减少输入数据的工作量。如设置性别的默认值为“男”。,（,3,）标识列：设置为标识的列，可以有系统自动操作计数（自动编号），不用用户输入，对于一些具有递增或递减自动编号性质的列，如订单号、发票号等可以设置此附加属性。将一个字段设置为标识列，其数据类型必须是以下类型之一：,int,、,bigint,、,smallint,、,tinyint,、或小数位数为,0,的,decimal,、,numeric,字段。该字段不允许为空，且不能有默认值。,（,4,）计算列：计算字段是一个虚拟的字段，它并未将计算结果实际存储在表中，而只是在运行时才计算出结果。在设置计算字段时，不需要指定该字段的数据类型，当保存表结构，,SQL Server,会自动决定计算字段的数据类型。,7.2.1表的设计,2024/9/16,7.2 表的设计与创建,2,设置约束,约束定义了关于允许什么数据进入数据库的规则，是分配给表或表中某列的一个属性。使用约束，主要目的在于防止列中出现非法数据，可以自动维护数据库中的数据完整性。数据完整性是指数据的正确性、完备性和一致性，是衡量数据库质量好坏的重要标准。使用,INSERT,、,DELETE,、,UPDATE,语句修改数据库内容时，数据的完整性可能会遭到破坏。可能会将无效的数据添加到表中，如将学生考试成绩输入成负数。为了解决类似的问题，,SQL Server,提供了对数据库中表、列实施数据完整性的方法。,完整性的类型主要包括：,7.2.1表的设计,2024/9/16,7.2 表的设计与创建,（,1,）域完整性,域完整性是指一个列的输入有效性，是否允许空值。通常使用有效性检查强制域完整性，也可以通过限定列中允许的数据类型、格式或可能值的范围来强制域完整性。,（,2,）实体完整性,实体完整性是指保证表中所有的行唯一。实体完整性要求表中的所有行都有一个唯一标识符。这个唯一标识符可能是一列，也可能是几列的组合，称之为主键。也就是说，表中主键在所有行上必须取值唯一且不能为空值。,7.2.1表的设计,2024/9/16,7.2 表的设计与创建,（,3,）参照完整性,参照完整性也叫引用完整性。参照完整性总是保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。,在,SQL Server,中约束是强制数据完整性的首选方法，它是强制数据完整性的,ANSI,标准方法。每种数据完整性类型，使用单独的约束类型进行强制。约束确保在列中录入有效的数据值，并且能维护表之间的关联关系。具体的约束类型及实现方法将在,7.5,节做详细介绍。,3,输入记录,表结构设计好之后，就可以向表中输入数据了。输入记录时，必须遵循所设置的约束条件，否则，服务器将拒绝接受所输入的不非法数据。,按照学生成绩管理系统数据库（,SGMS,）实际问题的需要，设计了,9,张表，分别为：,student,、,course,、,coursetype,、,teacher,、,users,、,department,、,class,、,speciality,、,grade,，表结构见附录。,7.2.1表的设计,2024/9/16,7.2 表的设计与创建,用,T-SQL,语句创建表比使用,SSMS,创建表更加直接、有效。实际的应用系统中，通常用,CREATE TABEL,语句创建表，其基本语法格式如下：,CREATE TABEL,表名,(,列名 数据类型,NOT NULL|NULL),上面格式中，包含参数的含义如下：,表名：是所创建的表的名称，在一个数据库内表名必须唯一。,列名：列名在一个表内列名必须唯一。,数据类型：可以使用系统数据类型，也可以使用用户定义的数据类型。对于需要给定数据最大长度的类型，在定义时要给出长度。,7.2.2 使用T-SQL语句创建表,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.2 表的设计与创建,任务,7-1,在,SGMS,数据库中创建,student,表。,任务分析：创建表时，要给出表的名称、字段名、字段数据类型等有关信息。在,T-SQL,中，可以用,CREATE TABEL,命令来创建表，表中列的定义必须用括号括起来。一个表中最多可以包含,1024,列。本例中可以按照,student,表结构，依次定义字段的名称、数据类型及是否允许为空。实现代码如图,7-1,所示。,7.2.2 使用T-SQL语句创建表,图,7-1,创建,student,表,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,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:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.2 表的设计与创建,（,3,）表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的第一行对应设置一列，对每一列都需要进行如下设置：,列名：为每一列设定一个列名。,数据类型：数据类型是一个下拉列表框，其中包含了所有的系统数据类型和用户自定义的数据类型。用户可根据需要来选择数据类型和长度。,允许空：单击该行的复选框，可以切换是否允许该列为空值的状态。打勾表示允许为空值，空白表示不允许为空值，默认状态下是允许为空值的。,表设计器下半部分是特定列的详细属性，包括是否是,INDENTITY,列、是否使用默认值等。表设计器右半部分是表的详细属性，这里可以设置表的名称、表所在的文件组等信息。,7.2.3使用SSMS创建表,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.2 表的设计与创建,（,4,）逐个定义好表中的列，单击工具栏中的“保存”按钮。若没有在表设计器中给出表的名称，会出现保存对话框，提示用户输入表的名称，如图,7-3,所示。单击“确定”按钮，,course,表就建立完成。,7.2.3使用SSMS创建表,图,7-3,保存表结构,2024/9/16,7.3管理表,在表创建完成以后，根据实际的需要，有可能会对表原有的设计作相应的修改，比如增加一列，删除一列，或者对相应的某一列的数据类型做些调整等等。用户可以使用,T-SQL,脚本管理表，也可以使用,SSMS,管理表，下面分别介绍。,2024/9/16,7.3管理表,1,使用,T-SQL,语句显示表信息,任务,7-3,查看表,student,的基本信息。,任务分析：可以使用系统存储过程,sp_help,来查看表结构，包括表的所有者、类型（系统表还是用户表）、创建时间、表上每一列的名称、数据类型、表上定义的索引及约束等。本例实现代码如图,7-4,所示。,7.3.1使用T-SQL 脚本管理表,图,7-4,查看,student,表的结构,2024/9/16,7.3管理表,2,使用,T-SQL,语句修改表结构,使用,ATLER TABEL,语句可以对表结构进行修改，具体包括增加列、删除列以及修改列定义。,（,1,）增加列,向表中增加列，通过,ADD,子句实现，其基本语法格式为：,ATLER TABLE,表名,ADD,列名 列的描述,参数：列的描述中要给出列的数据类型，是否为空。,7.3.1使用T-SQL 脚本管理表,2024/9/16,7.3管理表,任务,7-4,向,student,表中增加列,Email,列。,任务分析：向表中增加一列时，应使新增加的列有默认值或允许为空值，,SQL Server,将向表中已经存在的行填充新增加列的默认值或空值。如果既没有提供默认值也不允许为空值，那么新增加列的操作将会出错，因为不知道该怎样处理哪些已经存在的行。实现代码如图,7-5,所示。,7.3.1使用T-SQL 脚本管理表,图,7-5,增加,Email,列,【,注意,】,可以一次向表中增加多列，多列之间用逗号分开。,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.3管理表,（,2,）删除列,对于一张表，要删除表中某列可以用,DROP COLUMN,子句实现，其基本语法格式如下：,ATLER TABLE,表名,DROP COLUMN,列名,任务,7-5,删除,student,表中,Email,列,任务分析：删除表中的列，使用,T-SQL,中提供的,DROP COLUMN,子句实现，实现代码如图,7-6,所示。,7.3.1使用T-SQL 脚本管理表,图,7-6,删除,student,表中,Email,列,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.3管理表,（,3,）修改列定义,修改列定义可以用,ALTER COLUMN,子句实现，其基本语句格式如下：,ALTER TABLE,表名,ALTER COLUMN,列名 列的描述,任务,7-6,将,student,表中,studentName,修改为最大长度为,60,的,nvarchar,型数据，且不允许为空值。,任务分析：表中的每一列都有其定义，包括列名、数据类型、数据长度及是否允许为空值等，这些值都可以在表创建好以后，如果不合适，可以对其进行修改。实现代码如图,7-7,所示。,7.3.1使用T-SQL 脚本管理表,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.3管理表,【,注意,】,默认状态下，列是被设置为允许为空值的，将一个原来允许为空值的列修改为不允许为空值的列时，必须有两个前提条件：,列中没有存放有空值的记录；,在列上没有创建索引。,7.3.1使用T-SQL 脚本管理表,图,7-7,修改,student,表,studentname,列,http:/www.ort- http:/www.light- http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- ,2024/9/16,7.3管理表,3,使用,T-SQL,删除表结构,在,T-SQL,语句中，,DROP TABLE,语句可以用来删除表。其基本语法格式如下：,DROP TABLE,表名,任务,7-7,删除,SGMS,数据库中的表,course,表。,任务分析：删除表之前一定要确定该表是不再需要的表。一旦一个表被删除，那么它的数据、结构定义、约束、索引等都将被永久删除，以前用来存储数据和索引的磁盘空间可以用来存储其他数据库对象了。实现代码如图,7-8,所示。,7.3.1使用T-SQL 脚本管理表,图,7-8,删除,course,表结构,2024/9/16,7.3管理表,1,使用,SSMS,查看表属性,任务,7-8,查看,student,表属性。,任务分析：在,SSMS,的“对象资源管理器”中找到要查看表所在的数据库，选中树型结构中的“表”结点，右边的窗口中就会显示这一数据库中所有的表，其中系统表单独在“系统表”文件夹内。对于每一个表，都会显示它的架构和创建时间。在列表中选择一个要查看的表，右击打开快捷菜单，选择“属性”命令，打开“表属性”对话框，如图,7-9,所示，可以在此查看、设置表的属性、权限和扩展属性等。,7.3.2使用SSMS管理表,图,7-9,查看,student,表的属性,2024/9/16,7.3管理表,2,使用,SSMS,修改表结构,任务,7-9,修改,student,表结构。,任务分析：修改表结构，也是在“表设计器”窗口中进行。其方法如下：,在,SSMS,的“对象资源管理器”中要修改的表，右击要修改的表，在弹出的快捷菜单中选择“修改（,Y,）”命令，将弹出如图,7-10,所示的表设计器。,7.3.2使用SSMS管理表,图,7-10,修改,student,表结构,2024/9/16,7.3管理表,此时可以象新建表一样，向表中增加列、从表中删除已有列或修改列的属性，修改完毕后单击“保存”按钮即可。,选中某一列，右击鼠标，在弹出的快捷菜单中选择“删除列（,N,）”命令，则可删除某一列。,7.3.2使用SSMS管理表,2024/9/16,7.3管理表,3,使用,SSMS,删除表结构,任务,7-10,删除,SGMS,数据库中,student1,表结构。,任务分析：这里对,student,表复制了一份，在副本,student1,上进行删除操作。,（,1,）使用,SSMS,删除表非常简单，只需在“对象资源管理器”中找到要删除的表，右击，在弹出快捷菜单中选择“删除（,D,）”命令即可，如图,7-11,所示。,（,2,）在弹出“删除对象”对话框中，单击“确定” 按钮即可。,7.3.2使用SSMS管理表,图,7-11,删除,SGMS,数据库中的,student1,表,2024/9/16,7,.4 数据的插入、更新和删除,1,向表中插入数据,