SQL Server数据库及应用（第二版）课件第4章 数据表的创建和管理

第4章数据表的创建和管理本章学习目标掌握SQLServer2008中常用的数据类型熟练掌握数据表的创建熟练掌握约束的创建、删除和修改熟练掌握字段的增加、删除和修改掌握查看数据表定义、表中数据、数据库对象之间的依赖关系的方法熟练掌握数据表的删除4.1数据类型数据类型决定了数据的存储格式，代表了各种不同的信息类型。数据类型是一种属性，用于指定对象可保存的数据的类型。数据类型决定了数据的存储格式，代表各种不同的信息类型。为对象分配数据类型时同时确定对象四个属性：（1）对象包含的数据种类（2）所存储值的长度或大小（3）数值的精度（4）数值的小数位数4.1数据类型SQLServer提供系统数据类型集，该类型集定义了可与SQLServer一起使用的所有数据类型。系统数据类型是SQLServer预先定义好的，可以直接使用。SQLServer2008中的数据类型分为七大类，共28种。4.1.1精确数字类型精确数字类型分为整数类型、位类型、数值类型和货币类型等四类：（1）整数类型(bigint、int、smallint、tinyint)（2）位类型（0、1、NULL）（3）数值类型（decimal、numeric）（4）货币类型（money、smallmoney）4.1.2近似数字类型近似数字类型包括float和real这2类。它们用于表示浮点数值数据。4.1.3日期和时间类型日期和时间类型包括time、date、datetime、datetime2、smalldatetime和datetimeoffset。它们是用于表示某天的日期和时间的数据类型。具体表示格式、范围、精确度见表4-5。在输入日期数据时，允许使用指定的数字格式表示日期数据。建议使用SQLServer2008新增的date、time和datetime2数据类型。4.1.4字符串类型包括char、varchar和text数据类型。char[(n)]：固定长度，非Unicode字符数据，n的取值范围为1至8,000，存储大小是n个字节。varchar[(n|max)]：可变长度，非Unicode字符数据。n的取值范围为1至8,000,存储大小是输入数据的实际长度加2个字节。text：用于存储大容量文本数据，根据数据的长度自动分配空间。4.1.5Unicode字符串类型包括nchar、nvarchar和ntext数据类型。nchar[(n)]：n个字符的固定长度的Unicode字符数据。n值必须在1到4,000之间,存储大小为两倍n字节。nvarchar[(n|max)]：可变长度Unicode字符数据。n值在1到4,000之间,存储大小是所输入字符个数的两倍加2个字节。Ntext：是长度可变的Unicode数据，最大长度为230-1(1,073,741,823)个字符,存储大小是所输入字符个数的两倍（以字节为单位）。4.1.6二进制字符串类型包括binary、varbinary和image数据类型。binary[n]：长度为n字节的固定长度二进制数据，其中n从1到8000之间,存储大小为n字节。binary[(n|max)]：可变长度的二进制数据，其中n从1到8000之间，存储大小为所输入数据的实际长度+2个字节。image：可变长度的二进制数据。4.1.7其他数据类型包括cursor、sql_variant、table、timestamp、uniqueidentifier、xml、hierarchyid数据类型。4.1.8

用户自定义数据类型用户自定义数据类型是基于SQLServer系统提供的数据类型。创建用户自定义数据类型时，必须提供类型名称、所依据的系统数据类型、是否允许为空值。用户可以使用SSMS或T-SQL语句来创建用户自定义数据类型，它的使用与系统数据类型相同。4.1.8

用户自定义数据类型1．创建用户自定义数据类型（1）使用SSMS创建用户自定义数据类型1）打开SSMS，在“对象资源管理器”中，依次展开“数据库”|“用户数据库”|“可编程性”节点。2）鼠标右击“类型”，从弹出的快捷菜单中选择“用户定义数据类型”|“新建用户定义数据类型”。3）系统弹出“新建用户定义数据类型”对话框。在“名称”文本框中输入新建数据类型名称。在“数据类型”下拉框中选择数据类型。在“长度”数值框中输入值。选择“允许NULL值”复选框。设置完成后，单击“确定”按钮，完成创建用户自定义数据类型。4.1.8

用户自定义数据类型1．创建用户自定义数据类型（2）使用T-SQL语句创建用户自定义数据类型可以使用T-SQL的CREATETYPE语句创建用户自定义数据类型，其语法格式如下：CREATETYPEtype_name{FROMsystem_type[NULL|NOTNULL]}例4-1：使用T-SQL为AWLT数据库创建一个用户自定义数据类型AccountNumber，可变字符型，长度最大为15，允许为空。4.1.8

用户自定义数据类型2．删除用户自定义数据类型（1）使用SSMS删除用户自定义数据类型选中要删除的用户自定义数据类型，右键单击，在弹出的对话框中选择“删除”命令，在“删除对象”窗口中，选择“确定”按钮，即可删除。（2）使用T-SQL语句删除用户自定义数据类型DROPTYPEtype_name例4-2：删除AWLT数据库中用户自定义数据类型AccountNumber。4.2创建数据表数据表是数据库的主要对象，用来存储各种各样的信息。一个数据库表由一条或多条记录组成，没有记录的表称为空表。每个表通常都有一个主关键字（又称为主键），用于唯一地确定一条记录。在SQLServer2008中，每个数据库最多可包含20亿个表，每个表可包含1,024个字段。在同一个表中不允许有相同名称的字段。4.2.1使用SSMS创建数据表在SSMS的“对象资源管理器”中，展开指定的服务器和数据库，打开想要创建新表的数据库，右击“表”对象，并从弹出的快捷菜单中选择“新建表”选项，在窗口右部会出现新建数据表的对话框。说明：在SQLServer的早期版本中，数据库用户和架构在概念上是同一对象。从SQLServer2005开始，用户和架构区分开来了，架构用作对象的容器。每个对象都属于一个数据库架构。4.2.1使用SSMS创建数据表在数据库中数据表名必须是唯一的，但是如果为数据表指定了不同的架构，就可以创建多个相同名称的数据表，即在不同的架构下可以存在相同名称的数据表。在使用这些数据表时，需要在数据表的名称前面加上架构的名称。4.2.1使用SSMS创建数据表4.2.2使用T-SQL语句创建数据表CREATETABLE[database_name.[schema_name].|schema_name.]table_name({<column_definition>|<computed_column_definition>}[<table_constraint>][,...n])<column_definition>::=column_name<data_type>[NULL|NOTNULL|DEFAULTconstant_expression|IDENTITY[(seed,increment)]][<column_constraint>[...n]]4.2.2使用T-SQL语句创建数据表<column_constraint>::=[CONSTRAINTconstraint_name]{{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED]|[FOREIGNKEY]REFERENCES[schema_name.]referenced_table_name[(ref_column)][ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]|CHECK(logical_expression)}4.2.2使用T-SQL语句创建数据表<table_constraint>::=[CONSTRAINTconstraint_name]{{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED](column[ASC|DESC][,...n])|FOREIGNKEY(column[,...n])REFERENCESreferenced_table_name[(ref_column[,...n])][ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][NOTFORREPLICATION]|CHECK(logical_expression)}4.2.2使用T-SQL语句创建数据表其中各参数的说明如下：database_name：在其中创建表的数据库的名称。schema_name：新表所属架构的名称。table_name：新表的名称。表名必须遵循标识符规则。column_name：表中列的名称。4.2.2使用T-SQL语句创建数据表computed_column_expression：定义计算列的值的表达式。DEFAULT：如果在插入过程中没有显式地提供值，则指定为列提供的值。[ASC|DESC]：指定加入到表约束中的一列或多列的排序顺序。默认值为ASC。4.2.2使用T-SQL语句创建数据表例4-3：使用T-SQL语句创建AWLT数据库中的Customer表，表中各列的要求如表4-6所示。4.2.2使用T-SQL语句创建数据表4.3创建、修改和删除约束约束通过限制字段的取值范围和数据表之间的数据依赖关系来保证数据的完整性。在SQLServer2005中主要可以使用以下几种约束：非空约束主键约束唯一性约束默认约束检查约束外键约束4.3创建、修改和删除约束约束可以分为表级约束和列级约束两种：列级约束：列级约束是行定义的一部分，只能够应用在一列上，是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名

。4.3创建、修改和删除约束约束可以分为表级约束和列级约束两种：表级约束：表级约束的定义独立于列的定义，可以应用在一个表中的多列上，与列定义用’,’分隔，定义表约束时必须指出要约束的那些列的名称。表级约束有四种：主键、外键、唯一、检查

4.3.1非空约束空值（或NULL）不同于零(0)、空白或长度为零的字符串（如“”）。出现NULL通常表示值未知或未定义。列的为空性决定表中的行是否可为该列包含空值。插入数据时，如果没有为允许空值的列包括值，将提供NULL值。（DEFAULT除外）用关键字NULL定义的列也接收用户的NULL显式输入。NULL值不应放在引号内。指定某一列不允许空值有助于维护数据的完整性，确保行中的列永远包含数据。4.3.1非空约束创建非空约束常用的操作方法有如下两种：1．使用SSMS创建非空约束2．使用T-SQL语句创建非空约束

CONSTRAINT<约束名>NOTNULL4.3.2主键约束主键能够唯一地确定表中的每一条记录，主键不能取空值。主键约束可以保证实体的完整性，是最重要的一种约束。如果表中有一列被指定为主键，该列不允许指定NULL属性。主键约束定义在不只一列上，一列中的值可以重复，所有列的组合值必须唯一。Image和text类型的列不能被指定为主键。4.3.2主键约束主键的添加、修改和删除操作方法有两种：SSMS操作法和TSQL语句操作法。1．通过SSMS完成添加、修改和删除主键的操作2．使用T-SQL语句设置主键约束其语法形式如下：CONSTRAINTconstraint_namePRIMARYKEY[CLUSTERED|NONCLUSTERED]（column_name[,…n]）4.3.2主键约束例4-4：在数据库AWLT中创建一个CustomerAddress表，并定义CustomerID，AddressID组合为主键。4.3.3唯一性约束唯一性约束用于指定一个或多个列的组合值具有唯一性，以防止在列中输入重复的值。当表中已经有一个主键值时，如果还要保证其他的字段值唯一时，可以使用唯一性约束。1．通过SSMS创建唯一性约束4.3.3唯一性约束2．使用T-SQL语句创建唯一性约束CONSTRAINTconstraint_nameUNIQUE[CLUSTERED|NONCLUSTERED]（column_name[,…n]）例4-5：创建一个Customer1表，其中Title字段具有唯一性。4.3.4检查约束检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。1．使用SSMS创建检查约束4.3.4检查约束2．使用T-SQL语句创建检查约束其语法形式如下：CONSTRAINTconstraint_nameCHECK[NOTFORREPLICATION]（logical_expression）4.3.4检查约束其中，参数NOTFORREPLICATION用于指定在把从其他表中复制的数据插入到表中时检查约束对其不发生作用。logical_expression用于指定逻辑条件表达式，返回值为TRUE或者FALSE。例4-6：创建一个Customer2表，其中EmailAddress字段必须包含“@”。4.3.5默认约束默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。使用默认约束时，应该注意以下几点：每个字段只能定义一个默认约束；如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上。4.3.5默认约束1．使用SSMS创建默认约束进入设计表对话框后，选定要设置的字段后，在窗口下部的“列属性”中找到“（常规）”下的“默认值或绑定”框，在默认值栏中输入该字段的默认值，即可创建默认约束。4.3.5默认约束2．使用T-SQL语句创建默认约束可以在createtable命令中定义列时，使用DEFAULT关键字指定默认值。例4-7：在AWLT中创建一个地址表Address，为CountryRegion字段创建默认约束“China”。4.3.6外键约束外键约束主要用来维护两个表之间数据的一致性，实现数据表之间的参照完整性。当一个数据表（表A）中的某些字段的取值参照另外一个数据表（表B）的主键所在列对应的数据值时，表A中的这些字段就叫做表A的外键。创建外键约束常用的操作方法有如下两种：1．在SSMS中添加外键约束4.3.6外键约束2．使用T-SQL语句设置外键约束

CONSTRAINTconstraint_nameFOREIGNKEY（column_name[,…n]）REFERENCESref_table[（ref_column[,…n]）]4.3.6外键约束其中，各参数的说明如下：REFERENCES：用于指定要建立关联的表的信息。ref_table：用于指定要建立关联的表的名称。ref_column：用于指定要建立关联的表中相关列的名称。说明：外键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配。4.3.6外键约束例4-8：创建CustomerAddress2表，并为CustomerID创建外键约束，该约束把CustomerAddress2表中的顾客编号（CustomerID）字段和表Customer中的顾客编号（CustomerID）字段关联起来，实现CustomerAddress2中的顾客编号（CustomerID）字段的取值要参照Customer表中的顾客编号（CustomerID）字段的数据值，类似的为AddressID字段创建了外键约束。4.4表结构的修改当数据表的结构创建完成后，用户还可以根据实际需要随时更改表结构。用户可以增加、删除和修改字段，更改数据表名称以及对约束的修改。在SQLServer中可以使用SSMS和T-SQL中的ALTERTABALE语句两种方法来增加、删除和修改字段。4.4.1使用SSMS增加、删除和修改字段在SSMS中，打开指定的服务器中要修改表的数据库，右键单击要修改的表，从弹出的快捷菜单中选择“设计”选项，则会出现显示已有的表结构的窗口。在该对话框中，可以通过鼠标操作完成增加、删除和修改字段的操作。4.4.2使用T-SQL语句增加、删除和修改字段ALTERTABLE[database_name.[schema_name].|schema_name.]table_name{ALTERCOLUMNcolumn_name{[type_schema_name.]type_name[({precision[,scale]|max})][NULL|NOTNULL]}|[WITH{CHECK|NOCHECK}]ADD{<column_definition>|<computed_column_definition>|<table_constraint>}[,...n]|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name}[,...n]}例4-9：修改数据库AWLT中Customer2表，修改Title字段的长度为10，在表中增加一个Sex字段，删除表中的ModifiedDate字段，并且删除对EmailAddress的约束chk_Email。4.4.2使用T-SQL语句增加、删除和修改字段4.5查看数据表数据表保存在数据库中，可以随时查看数据表的有关信息。比如数据表属性、数据表结构、表中的记录以及数据表与其他数据库对象之间的依赖关系等。4.5.1查看数据表属性在SSMS中，打开指定的服务器和数据库，右击要查看的数据表，然后从弹出的快捷菜单中选择“属性”选项。4.5.2查看数据表中的数据在SSMS中，打开指定的数据库并展开“表”对象，右击要查看数据的数据表，并从弹出的快捷菜单中选择“选择前1000行”选项。会出现显示表中数据的对话框。4.5.3查看数据表与其他数据库

对象的依赖关系在SSMS中，右击要查看的数据表，从弹出的快捷菜单中选择“查看依赖关系”选项，则会出现“对象依赖关系”对话框，在该对话框中，可以通过选择不同的选项来查看依赖于此数据表的对象，也可以查看此数据表依赖的对象。4.5.4利用系统存储过程查看表的信息系统存储过程sp_help可以提供指定数据库对象的信息，也可以提供系统或者用户定义的数据类型的信息，其语法格式如下：sp_help[[@objname=]name]只用于当前的数据库，其中[objname=]name子句用于指定对象的名称。如果不指定对象名称，就会列出当前数据库中的所有对象名称、对象的所有者和对象的类型。4.5.4利用系统存储过程查看表的信息例4-10：（1）显示AWLT数据库中所有对象的信息。（2）显示AWLT数据表的信息。4.6删除数据表4.6.1

使用SSMS删除数据表在SSMS中，展开指定的数据库，并展开其中的“表”对象，右击要删除的数据表，从弹出的快捷菜单中选择“删除”选项，会出现“删除对象”对话框。删除某个数据表之前，应该首先查看它与其他数据库对象之间是否存在依赖关系。单击“显示依赖关系”按钮，会出现“依赖关系”对话框。4.6.1

使用SSMS删除数据表在出现的依赖关系对话框中可以查看该数据表所依赖的对象和依赖于该数据表的对象，当有对象依赖于该数据表时，该表就不能删除。如果没有依赖于该表的其他数据库对象，则在“删除对象”对话框中单击“确定”按钮，即可删除此数据表。4.6.2使用T-SQL语句删除数据表可以利用TSQL中的DROPTABLE语句删除一个或多个数据表。DROPTABLE语句的语法形式如下：DROPTABLE[database_name.[schema_name].|schema_name.]table_name[,...n]4.6.2使用T-SQL语句删除数据表database_name：要删除的表所在的数据库的名称。schema_name：表所属架构的名称。table_name：要删除的表的名称。要删除的表如果不在当前数据库中，则应在table_name中指明其所属的数据库和用户名。在删除一个表之前要先删除与此表相关联的表中的外部关键字约束。当删除表后，绑定的规则或者默认值会自动松绑。4.6.2使用T-SQL语句删除数据表说明：（1）不能使用

DROPTABLE删除被

FOREIGNKEY约束引用的表。必须先删除引用

FOREIGNKEY约束或引用表。（2）可以在任何数据库中删除多个表。（3）删除表时，表的规则或默认值将被解除绑定，与该表关联的任何约束或触发器将被自动删除。4.6.2使用T-SQL语句删除数据表例4-11：删除AWLT数据库中的表Customer1、Customer2和CustomerAddress2。本章小结数据类型描述并约束了数据的种类、所存储数据的长度或大小、数值精度和小数位数。SQLServer2008中常用的数据类型：精确数字类型、近似数字类型、日期时间类型、字符串类型、Unicoide字符串类型、二进制字符串类型和其他数据类型。用户可以使用SSMS或T-SQL语句来创建用户自定义数据类型，它的使用与系统数据类型相同。4.7本章小结表由行和列组成，每列代表一个属性，每行代表一个记录。定义表结构时，要给出表的每一列的:字段名称数据类型数据长度是否为空4.7本章小结设置约束时，给表中的列添加约束，限制列输入值的取值范围，以保证输入数据的正确性和一致性。在SQLServer2008中创建数据表：使用SSMS使用T-SQL语句的CREATETABLE4.7本章小结SQLServer2008支持6中约束：NOTNULLDFFAULTCHECKPRIMAERYKEYFOREIGNKEYUNIQUE创建、删除和修改约束常用两种方法：使用SSMS使用T-SQL语句4.7本章小结在SQLServer中使用SSMS和T-SQL语句两种方法来增加、修改和删除字段。使用SSMS和系统存储过程查看数据库、数据表属性、数据表结构、表中的记录以及数据表与其他数据库对象之间的依赖关系等。使用SSMS和T-SQL语句删除一个或多个数据表。习题四一、填空题1．数据库中的表由

和

组成。2．约束是通过限制字段的取值范围和数据表之间的数据依赖关系来保证数据的

。3．如果主键约束定义在不止一列上，则一列中的值可以重复，但所有列的组合值

。习题四一、填空题4．若表中的约束由单一的字段构成，则可以定义为表级的和列级的约束；若表中的约束由多个字段构成，则只能定义为

。5．在一个表中可以定义

个主键约束，

个唯一性约束。6．外键约束主要用来维护两个表之间数据的一致性，实现数据表之间的

完整性。7．不能使用DROPTABLE删除被

约束引用的表。习题四二、选择题1．创建一个数据表时，可以指定的约束类型