书名：零基础学SQL Server 2008
作者名：王浩等编著
本章字数：3856字
更新时间：2021-03-31 03:36:13

第二篇 SQL Server 2008数据管理

第5章数据表

数据表是数据库中最重要的数据对象，数据库中的数据全部保存在数据表中，用户对数据的操作也主要集中在数据表上。在SQL Server 2008数据库中，数据表分为用户定义数据表、临时表、已分区表和系统表。用户最常使用的是用户定义数据表，在一些操作环境中，也会使用临时表，而已分区表涉及高级的数据管理，将在第17章进行详细的说明。SQL Server 2008数据库提供了多种方式对数据表进行创建、修改和删除。在前面的几章已经就使用SQL Server Management Studio工具的表设计器，创建、修改数据表做了详细的介绍，本章重点讲述通过T-SQL语句创建和管理数据表。

本章重点：

❑ 使用SQL语句创建和管理表。

❑ 为表添加CHECK约束。

❑ 掌握临时表的概念及应用。

5.1 用CREATE TABLE语句创建表

创建数据库后，就可以在数据库中创建数据表了。数据存储于表中，表存储于数据库文件中，任何拥有创建数据库权限的用户都可以进行该操作。在SQL Server 2008数据库系统中，可以使用CREATE TABLE语句创建表。

5.1.1 一个创建表的小实例

首先来看一个创建数据表的小实例。

【实例5.1】以下实例用于设计数据表，以保存人员的基本信息，这在人力资源数据库、客户关系数据库中十分常见。人员信息一般包括姓名、出生年月、地址、电话等基本信息。本例根据表5.1所示的需求，建立员工数据表。

表5.1 建立数据表的需求信息

创建数据表的具体过程请参考如下步骤。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。如果执行成功，则完成建立数据表。

        01 USE AdventureWorks2008
        02 GO
        03 CREATE TABLE Employees(
        04      EmployeeID   int   NOT   NULL,
        05      Name   nvarchar(50)    NOT NULL,
        06      Title   nvarchar(30),
        07      BirthDate   datetime,
        08      HireDate   datetime,
        09      Address   nvarchar(60),
        10      City   nvarchar(15),
        11      Region   nvarchar(15),
        12      PostalCode   nvarchar(10),
        13      Country   nvarchar(15),
        14      HomePhone   nvarchar(24),
        15      Photo   image,
        16      Memo   nvarchar(50),
        17 CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED
        18 (
        19      EmployeeID ASC
        20 )
        21 )

【代码说明】

❑ 第3行使用CRETAE TABLE关键字建立数据表。

❑ 建立数据表要求指定数据表的名称，本例名为“Employees”。

❑ 建立数据表，要求包含若干数据字段，即第4～16行的数据列。

❑ 数据字段要求指定数据类型。

❑ 在不指定字段是否允许为空的时候，默认设置为允许为空，第4～5行指定了不能为空。

【运行效果】在SQL Server Management Studio工具的“对象资源管理器”中，可以看到表“Employees”，如图5.1所示。

图5.1 建立数据库的实例

5.1.2 基本语法

使用CREATE TABLE语句创建表是数据库管理员的常见工作，该语句的功能十分强大，其参数较多、语法结构也比较复杂，但提供了更高的灵活性。一般情况下，经常使用的语法类似于5.1.1小节中的例子。

1. 建立数据表的限制

在SQL Server 2008数据库中，建立数据表存在一些限制，如下所示。

❑ 每个数据库最多可包含20亿个表，每张表最多可包含1024列。

❑ 表的行数及总大小仅受可用存储空间的限制。

❑ 每个数据行的数据最多只能包括8060个字节。对于带varchar、nvarchar、varbinary、sql_variant等数据类型的列，总宽可超过8060字节。

❑ 每个表最多可以有249个非聚集索引和1个聚集索引。

2. 语法结构

在SQL Server 2008数据库中，使用DDL语言创建数据表的语法结构比较复杂，本书在多个章节分别进行讲解。

（1）使用CREATE TABLE创建数据表的语法结构如下所示。

        CREATE TABLE
              [ database_name . [ schema_name ] . | schema_name . ] table_name
                    ( { <column_definition>} [ <table_constraint> ] [ ,...n ] )
            [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ]
        [ ; ]

【语法说明】

❑ database_name项：指创建表创建在数据库的名称，为可选项。

❑ schema_name项：新建表所属架构的名称，为可选项。

❑ table_name项：新建表的名称，为必填项。

❑ <column_definition>项：表示数据列的语法结构，详细说明见（2）项。

❑ <table_constraint>项：表示对数据表的约束进行设置，将在第11章进行详细说明。

❑ partition_scheme_name(partition_column_name)项：用于为表分区，将在第17章进行说明。

（2）数据列的语法结构如下所示。

        <column_definition> ::=
        column_name
              [ type_schema_name . ] type_name [ ( precision [ , scale ] ) ]
              [ COLLATE collation_name ]
              [ NULL | NOT NULL ]
              [ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
                | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]
            ]
            [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]

【语法说明】

❑ [ type_schema_name. ] type_name项：指定列的数据类型以及该列所属的架构。数据类型可以是SQL Server 2008系统数据类型、别名类型、CLR用户定义类型等。

❑ precision项：指定数据类型的精度。

❑ scale项：指定数据类型的小数位数。

❑ COLLATE collation_name项：指定列的排序规则。排序规则名称可以是Windows排序规则名称或SQL排序规则名称。

❑ NULL|NOT NULL项：确定列中是否允许使用空值。

❑ CONSTRAINT项：表示PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY或CHECK约束定义的开始。

❑ constraint_name项：约束的名称。

❑ DEFAULT项：如果在插入过程中未显式提供值，则指定为列提供的值。

❑ constant_expression项：表示用作列的默认值的常量、NULL或系统函数。

❑ IDENTITY项：表示该列是标识列。

❑ seed项：指装入表的第一行所使用的值。

❑ increment项：向装载的前一行的标识值中添加的增量值。

❑ NOT FOR REPLICATION项：在CREATE TABLE语句中，可为IDENTITY项的属性、FOREIGN KEY约束和CHECK约束指定NOT FOR REPLICATION子句。

❑ ROWGUIDCOL项：指示新列是行GUID列。

注意表名必须遵循标识符规则。除了本地临时表名（以单个数字符号(#)为前缀的名称）不能超过116个字符外，table_name最多可包含128个字符。列名必须遵循标识符规则，并在表中唯一。column_name可包含1～128个字符。对于使用timestamp数据类型创建的列，可以省略column_name。如果未指定column_name，则timestamp列的名称将默认为timestamp。

5.1.3 创建自动编号列

自动编号列是SQL Server 2008数据库提供的自动功能，用于建立自动增加数值的数据列，在3.5.3节中讲述了使用SQL Server Management Studio工具建立它的过程，用户可以使用IDENTITY关键字在T-SQL语句中建立。建立标识列的语法说明如下。

1. 使用标识列的语法

在表中创建一个标识列，可以使用以下语法结构。

        IDENTITY [ (seed , increment) ]

【语法说明】

❑ 参考CREATE TABLE 5.1.2中（2）的语法说明。

❑ seed，在表中的第一个行使用的值，即初始值。

❑ increment，与前一个加载的行的标识值相加的增量值。必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值（1，1）。

2. 使用IDENTITY属性的实例

【实例5.2】以下实例创建了一张数据表，其中id_num数据列设定为IDENTITY属性，从1开始，每新增加一条记录，数值增加1，并通过插入数据测试该列的取值。具体操作可以参考如下步骤。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008
        02 GO
        03 IF OBJECT_ID ('dbo.table_test_6_1', 'U') IS NOT NULL      --1
        04      DROP TABLE table_test_6_1
        05 GO
        06 CREATE TABLE table_test_6_1
        07 (
        08   id_num int IDENTITY(1,1),                                         --2
        09   fname varchar (20),
        10   minit char(1),
        11   lname varchar(30)
        12 )
        13 INSERT table_test_6_1  (fname, minit, lname) VALUES  ('Karin', 'F', 'Josephs')           --3
        14 INSERT table_test_6_1   (fname, minit, lname) VALUES   ('Pirkko', 'O', 'Koskitalo')
        15 GO

【代码说明】

❑ 代码分为3个部分，第一部分在IF语句中第3～4行，第二部分为使用CREATE TABLE数据表（第6～12行），第三部分为使用INSERT向表中加入记录部分（13～14行）。

❑ IF语句处理，用于判断是否已经存在名称为table_test_6_1的数据表，如果存在则删除该表。

❑ CREATE TABLE语句中，IDENTITY（1，1）表示id_num列为标识字段。

❑ 第三部分，向数据表中增加数据。可以看到在Insert语句中，没有id_num字段的设置，系统会根据IDENTITY属性从1排列增加。

【运行效果】执行SELECT * FROM dbo.table_test_6_1可以查询到增加的数据，结果如图5.2所示。

图5.2 运行结果

3. 使用SET IDENTITY_INSERT ON的实例

如果在日常工作中经常删除数据，那么数据表中存在的标识列中的值之间可能会产生间隔。为了确保未产生间隔，或者填补现有的间隔，在用SET IDENTITY_INSERT ON显式输入标识值之前，请先对现有的标识值进行计算。查找标识值之间的间隔计算，通常用于维护良好的数据规范。

【实例5.3】以下实例建立数据表，其中将id_num列设置为IDENTITY属性，向该表插入数据，可以查看到id_num列自动生成的编号。再通过删除中间的行，使该表的id_num产生中断，随后通过一段常用的例程取出间断值重新使用。具体操作可以参考如下的步骤。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 --1.查找并删除已经存在的数据表
        02 IF OBJECT_ID ('dbo.table_test_6_2', 'U') IS NOT NULL
        03      DROP TABLE table_test_6_2
        04 GO
        05 --2.创建数据表，并插入数据
        06 CREATE TABLE table_test_6_2 (id_num int IDENTITY(1,1), company_name sysname)
        07 INSERT table_test_6_2(company_name) VALUES ('Test 1')
        08 INSERT table_test_6_2(company_name) VALUES ('Test 2')
        09 INSERT table_test_6_2(company_name) VALUES ('Test 3')
        10 INSERT table_test_6_2(company_name) VALUES ('Test 4')
        11 GO
        12 --3.查看table_test_6_2数据表，将看到4条数据
        13 SELECT * FROM table_test_6_2
        14 GO
        15 --4.删除中断的号
        16 DELETE FROM table_test_6_2 WHERE id_num = 3
        17 --5.查看table_test_6_2数据表，将看到3条数据，标识列记录分别为1，2，4
        18 SELECT * FROM table_test_6_2
        19 GO
        20 --5.查找最小标识号，结果为3
        21 -- SET IDENTITY_INSERT ON and use in table_test_6_2 table.
        22 SET IDENTITY_INSERT table_test_6_2 ON
        23 DECLARE @minidentval smallint
        24 DECLARE @nextidentval smallint
        25 SELECT @minidentval = MIN($IDENTITY) FROM table_test_6_2
        26   IF @minidentval = IDENT_SEED('table_test_6_2')
        27       SELECT @nextidentval =      --6.赋值语句
        28       MIN($IDENTITY) + IDENT_INCR('table_test_6_2') FROM table_test_6_2 t1
        29           WHERE   ($IDENTITY   BETWEEN   IDENT_SEED('table_test_6_2') AND 32766)
        30           AND    ( NOT EXISTS (SELECT * FROM table_test_6_2 t2 WHERE t2.$IDENTITY = t1.$IDENTITY
        31 + IDENT_INCR('table_test_6_2')
        32                                   ))
        33   ELSE
        34          SELECT @nextidentval = IDENT_SEED('table_test_6_2')
        35
        36 PRINT   @nextidentval --输入查找到的值
        37 SET IDENTITY_INSERT table_test_6_2 OFF

【代码说明】

❑ 本实例显示了删除数据时，用于在标识值中查找间隔的常规语法。

❑ 代码分为6个部分，第一部分用于查找并删除已经存在的数据表，即代码中标为1的部分（2～4行）。第二部分用于创建包括标识字段的数据表（6～11行），并向表中增加4条记录，根据表的定义，生成标识字段为1、2、3、4的数据行，可通过第三部代码查看到（13～14行）。第四部分，删除间隔的、标识记录为3的数据行（第16行），并通过第五部分代码查看到（第21～26行）。第六部分代码用于计算出间隔标识数（第27行）。

❑ IDENT_SEED为函数用于返回建立数据表时，第一行所使用的值。

❑ 对于代码最后一部分的具体算法，可以先不去了解。读者记住这样的处理方式即可。如果要重新使用已删除的标识值，则可使用代码6来查找下一个可用的标识值。

【运行效果】结果如图5.3所示。

图5.3 删除记录后的结果

5.1.4 创建非空列

列是否为空决定表中的行是否可为该列包含空值。空值不同于零，也不同于空白或长度为零的字符串。NULL（空）的意思是没有输入，出现NULL通常表示值未知或未定义。

【实例5.4】以下实例建立一张数据表，其中设置fname列为非空列，然后再向数据表中插入数值以验证非空的约束。具体操作可以参考如下步骤：

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008
        02 IF OBJECT_ID ('dbo.table_test_6_3', 'U') IS NOT NULL
        03      DROP TABLE table_test_6_3
        04 GO
        05 CREATE TABLE table_test_6_3
        06 (
        07   id_num int IDENTITY(1,1),
        08   fname varchar (20)   NOT NULL,   --1.设置为非空列
        09   minit char(1),
        10   lname varchar(30)
        11 )
        12 INSERT table_test_6_3   (fname, minit, lname) VALUES   ('K', 'F', 'Jose')
        13 --2.以下不能执行成功
        14 INSERT table_test_6_3   (minit, lname) VALUES   ('F', 'Jose')

【代码说明】

❑ 本段代码第8行的fname字段，fname varchar (20) NOT NULL，设置为非空。

❑ 执行第2部分的第14行代码时，将提示错误。

【运行效果】提示错误如图5.4所示。

图5.4 错误提示信息

注意定义了PRIMARY KEY约束或IDENTITY属性的列不允许空值。插入一行数据，但没有为允许空值的列包括值，SQL Server 2008数据库引擎将提供NULL值，除非存在DEFAULT定义或DEFAULT对象。

5.1.5 字段的默认值

如果插入行时没有为列指定值，默认值指定列中使用的值，用户可以通过在CREATE TABLE语句中使用DEFAULT关键字来创建默认值定义。

【实例5.5】以下实例创建数据表，并为数据表的不同列指定不同类型的默认值，包括建立数据表时使用的默认值，创建默认对象再为表增加默认值；最后向数据表插入一条记录以确定默认值的使用情况。具体操作可以参考如下步骤。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 --1.创建table_test_6_4表
        02 USE AdventureWorks2008;
        03 GO
        04 CREATE TABLE table_test_6_4
        05    (keycol        smallint,
        06    process_id    smallint DEFAULT @@SPID,       --定义默认值
        07    date_ins    datetime DEFAULT getdate(),      --定义默认值
        08    mathcol        smallint DEFAULT 10 * 2,       --定义默认值
        09    char1        char(3),
        10    char2        char(3) DEFAULT 'xyz')            --定义默认值
        11 GO
        12 --2.定义
        13 /* For illustration only, use DEFAULT definitions instead.*/
        14 CREATE DEFAULT abc_const AS 'abc';
        15 GO
        16 sp_bindefault abc_const, 'table_test_6_4.char1';
        17 GO
        18 --3.插入数据
        19 INSERT INTO table_test_6_4(keycol) VALUES (1);
        20 GO
        21 --4.查看数据
        22 SELECT * FROM table_test_6_4;
        23 GO

【代码说明】

❑ 第4～11行创建了一个默认对象，用于为列分配默认值，并将默认对象绑定到该列上，然后在没有为该列指定值的情况下做插入测试，并检索测试行以验证所应用的默认值。

❑ 第6行的@@SPID函数用于产生当前用户进程的会话ID，为process_id列设定的默认值是函数@@SPID产生的值。

❑ 第7行的getdate()函数，用于返回当前系统日期和时间，它用作date_ins列的默认值。

❑ 代码第2部分的第16行，创建称为默认值的对象。这个对象和'table_test_6_4.char1'字段进行绑定。

❑ 当绑定到列或别名数据类型时，如果插入时没有显式提供值，则默认值将指定一个值，以便将其插入该对象所绑定的列中。

【运行效果】下面是代码第4部分的运行结果，如图5.5所示。

图5.5 运行结果

5.1.6 选择表存放文件组

不同的数据表可以保存在不同文件组中，这为用户提供了非常有用的策略。将不同重要程度的数据表保存在不同磁盘的不同文件组中，这不仅有利于提高数据表的读写性能，更加有利于根据文件的重要程度实施保存和备份策略，对文件组的详细说明参见第17章。

【实例5.6】以下实例一个创建文件组，在创建数据表时，指定其存放的文件组，从而实现定制数据表存储的物理位置的目的。后面通过SQL Server Management Studio工具查看该数据表保存的文件组。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE master;
        02 GO
        03 --1.创建数据库
        04 CREATE DATABASE Table_test_6_5
        05 ON PRIMARY
        06    ( NAME='Table_test_6_5_Primary',
        07       FILENAME=
        08          'c:\Program  Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5_Prm.mdf',
        09       SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB),
        10 FILEGROUP Table_test_6_5_FG1   --次要数据文件
        11    ( NAME = 'Table_test_6_5_FG1_Dat1',
        12       FILENAME =
        13          'c:\Program  Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5_FG1_1.ndf',
        14       SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB),
        15    ( NAME = 'Table_test_6_5_FG1_Dat2',
        16       FILENAME =
        17          'c:\Program  Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5_FG1_2.ndf',
        18       SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB)
        19 LOG ON
        20    ( NAME='Table_test_6_5_log',
        21       FILENAME =
        22          'c:\Program  Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5.ldf',
        23       SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB);
        24 GO
        25 --2.修改文件组为默认
        26 ALTER DATABASE Table_test_6_5
        27    MODIFY FILEGROUP Table_test_6_5_FG1 DEFAULT;
        28 GO

【代码说明】

❑ 关于文件组的使用将在第17章详细说明，本节仅需要了解数据表可以存放在指定的文件组上即可。

❑ 第4～24行创建了一个数据库，它包括一个主数据文件、一个用户定义文件组和一个日志文件。

❑ 本段实例代码分为两部分，第一部分创建包含用户定义文件组的数据库（4～24行），第二部分修改用户定义文件组为默认文件组（26～28行）。

❑ 主数据文件在主文件组中，而用户定义文件组包含两个次要数据文件。

❑ 实例使用了ALTER DATABASE语句，将用户定义文件组指定为默认文件组。

（3）在查询窗口中，输入如下代码，并单击“执行”按钮。

        29 --3.在用户定义的文件组中创建数据表
        30 USE Table_test_6_5;
        31 CREATE TABLE MyTable
        32    ( cola int PRIMARY KEY,
        33       colb char(8) )
        34 ON Table_test_6_5_FG1;--4指定文件组
        35 GO

【代码说明】

❑ 以上代码第31～35行建立数据表，同时指定数据表所在的文件组。如代码中第4部分的34行所示ON Table_test_6_5_FG1将数据表保存的位置指定于Table_test_6_5_FG1文件组。

❑ 用户可以通过指定用户定义文件组来创建表，根据CREATE TABLE基本语法5.1.2的（1）项中， ON{<partition_scheme>|filegroup|"default"}项中。如果指定了filegroup，则该表将存储在命名的文件组中，数据库中必须存在该文件组。

【运行效果】在SQL Server Management Studio工具中右击该表，在弹出的快捷菜单中选择“设计”命令，打开表设计器，按F4键，打开表的属性窗口，用户可以在属性窗口中查看到表所在的文件组，如图5.6所示。

图5.6 表的属性

说明将数据表保存入指定的文件组，一般可以通过两种方式实现。一种方式是在CREATE TABLE语句中直接指定数据表位于的文件组，另一种方式是使用分区表实现将数据表存入文件组中。对于分区表的方式将在第17章的数据分区管理中详细说明。

5.1.7 使用CHECK约束

创建表时，用户可以创建CHECK约束作为表定义的一部分。如果表已经存在，则可以添加CHECK约束，表和列可以包含多个CHECK约束。

【实例5.7】以下实例通过在创建数据表过程中使用CHECK关键字，为列定义CHECK约束，然后插入数据验证约束。具体操作可以参考如下步骤。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 --1.创建数据表
        02 USE AdventureWorks2008;
        03 GO
        04 CREATE TABLE Table_test_6_6
        05 (
        06    id          int        PRIMARY KEY,
        07    cname      char(50),
        08    address   char(50),
        09    memo       char(50),
        10    CONSTRAINT id CHECK (id BETWEEN 0 and 10000 )
        11 )
        12 --2.执行插入数据验证约束
        13 INSERT INTO Table_test_6_6 values(100000,'NAME','ADDRESS','MEMO');
        14 GO

【代码说明】

❑ 第4～11行在建立数据库表时使用CHECK为id建立约束，使用值在0和10000之间。

❑ 第13行用来插入一条数据，仅用来测试。

❑ 执行代码2，由于数据100000超出id的CHECK约束，出现以下提示。

        消息547，级别16，状态0，第1 行
        INSERT 语句与CHECK 约束"id"冲突。该冲突发生于数据库"AdventureWorks2008"，表"dbo.Table_test_6_6",
        column 'id'。
        语句已终止。

【运行效果】执行代码1～11行后，可在表设计器中查看到该约束，如图5.7所示。

图5.7 查看约束

5.1.8 设置字段的排序规则

排序规则是根据特定语言和区域设置的标准，指定对字符串数据进行排序和比较的规则。用户可以在CREATE TABLE语句中设置字段的排序规则。

【实例5.8】以下实例在创建数据表中为列指定排序规则，用户使用COLLATE关键字在CREATE TABLE语句中实现该功能。具体操作可以参考以下步骤。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008
        02 GO
        03 --在CREATE TABLE过程中设计字段的排序规则
        04 CREATE TABLE Table_test_6_7
        05    (col1    int PRIMARY KEY,
        06    col2      varchar(10)   COLLATE   French_CI_AS NOT NULL
        07    )
        08 GO

【代码说明】

❑ 代码第5行是创建本表的主键，PRIMARY KEY表示主键。

❑ 代码在第6行的CREATE TABLE中指定“COLLATE French_CI_AS”的排列顺序。

【运行效果】执行结果可在表设计器的列属性信息中查看到，如图5.8所示。

图5.8 表设计器中的排序规则

5.2 用ALTER TABLE语句修改表

对表的结构进行修改，是SQL Server 2008数据库管理员常用的操作，用户可通过ALTER TABLE语句重新定义数据表的结构。

5.2.1 基本语法

使用ALTER TABLE通过更改、添加或删除列和约束，或者启用或禁用约束和触发器，从而修改表的定义，SQL Server 2008数据库的ALTER TABLE语句提供了丰富的参数项，本节中仅列出常用的语法结构。SQL Server 2008数据库中ALTER TABLE的基本语法如下：

        ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
        {
            ALTER COLUMN column_name
              {
                    [ type_schema_name. ] type_name [ ( { precision [ , scale ] | xml_schema_collection } ) ]
                    [ NULL | NOT NULL ]
                    [ COLLATE collation_name ]
              }
            | [ WITH { CHECK | NOCHECK } ] ADD
              {
                    <column_definition>| <table_constraint>
              } [ ,...n ]
            | DROP
              {
                      [ CONSTRAINT ] constraint_name
                      [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
                      | COLUMN column_name
              } [ ,...n ]
            | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] }
            | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] }
        }

【语法说明】

❑ database_name项：更改表时所在的数据库的名称。

❑ schema_name项：更改表所属架构的名称。

❑ table_name项：要更改的表的名称。

❑ ALTER COLUMN项：指定要更改命名列。

❑ column_name项：要更改、添加或删除的列的名称。

❑ [type_schema_name.]type_name项：更改后的列的新数据类型或添加的列的数据类型。

❑ precision项：指定的数据类型的精度。

❑ scale项：指定的数据类型的小数位数。

❑ xml_schema_collection项：仅应用于xml数据类型，以便将XML架构与类型相关联。

❑ NULL | NOT NULL项：指定列是否可接受空值。

❑ COLLATE < collation_name >项：指定更改后的列的新排序规则。

❑ WITH CHECK | WITH NOCHECK项：指定表中的数据是否用新添加的或重新启用的FOREIGN KEY或CHECK约束进行验证。

❑ ADD项：指定添加一个或多个列定义、计算列定义或者表约束。

❑ DROP{[CONSTRAINT] constraint_name|COLUMN column_name}项：从表中删除约束或列。

❑ { CHECK | NOCHECK} CONSTRAINT项：指定启用或禁用constraint_name。

❑ ALL项：指定使用NOCHECK选项禁用所有约束，或者使用CHECK选项启用所有约束。

❑ {ENABLE | DISABLE} TRIGGER项：指定启用或禁用trigger_name。

❑ trigger_name项：指定要启用或禁用的触发器的名称。

5.2.2 修改字段属性

【实例5.9】本节列举一个实例，使用ALTER TABLE ALTER COLUMN修改数据表中的字段的数据类型，由int型更改为decimal型，具体操作步骤如下。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008
        02 GO
        03 CREATE TABLE Table_test_6_8 ( column_a INT ) ; --创建表
        04 GO
        05 INSERT INTO Table_test_6_8 (column_a)
        06 VALUES (10) ;                                     --插入数据
        07 GO
        08 SELECT * FROM Table_test_6_8;
        09 GO
        10 ALTER TABLE   Table_test_6_8   ALTER   COLUMN   column_a   DECIMAL (5, 2) ;
        11 GO
        12 SELECT * FROM Table_test_6_8;
        13 GO

【代码说明】

❑ 使用ALTER TABLE Table_test_6_8 ALTER COLUMN column_a DECIMAL（5, 2）修改表的列属性。

❑ 进行数据列的类型转换时，要求能够进行数据类型的隐示转换。

【运行效果】运行结果如图5.9所示。

图5.9 执行结果

5.2.3 添加字段

【实例5.10】本节列举一个实例，使用ALTER TABLE ADD修改数据表，添加一个允许空值的列，具体操作步骤如下。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008
        02 GO
        03 CREATE TABLE Table_test_6_9 ( column_a INT) ;
        04 GO
        05 ALTER TABLE Table_test_6_9 ADD column_b VARCHAR(20) NULL ;       --1
        06 GO
        07 EXEC sp_help Table_test_6_9 ;                                              --2
        08 GO

【代码说明】

❑ 第5行添加一个允许空值的列，而且没有通过DEFAULT定义提供的值。在该新列中，每一行都将有NULL值。

❑ 代码第5行完成增加字段的功能，代码第7行查看增加的字段。

【运行效果】运行结果如图5.10所示。

图5.10 执行结果

5.2.4 删除字段

【实例5.11】本节列举一个实例，使用ALTER TABLE DROP COLUMN修改数据表，从表中删除一个数据列，具体操作可参考如下步骤。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008
        02 GO
        03 CREATE TABLE Table_test_6_10 ( column_a INT, column_b VARCHAR(20) NULL) ;
        04 GO
        05 ALTER TABLE Table_test_6_10 DROP COLUMN column_b ; --1
        06 GO
        07 EXEC sp_help Table_test_6_10 ;                                  --2
        08 GO
        09 --DROP TABLE Table_test_6_10
        10 --GO

【代码说明】

❑ 代码第5行为删除字段。

❑ 代码第7行查看删除的字段。

【运行效果】运行结果如图5.11所示。

图5.11 删除字段的查看

5.2.5 添加约束

本节列举多个实例分别使用ALTER TABLE为数据库加入CHECK约束、DEFAULT约束等特性。

1. 添加CHECK约束的实例

【实例5.12】本例使用ALTER TABLE ADD CONSTRAINT修改数据表，在现有列中添加一个未经验证的CHECK约束，具体操作步骤如下。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008
        02 GO
        03 CREATE TABLE Table_test_6_11 ( column_a INT) ;
        04 GO
        05 INSERT INTO Table_test_6_11 VALUES (-1) ;      --1
        06 GO
        07 ALTER TABLE Table_test_6_11 WITH NOCHECK    --2
        08 ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
        09 GO
        10 EXEC sp_help Table_test_6_11 ;
        11 GO
        12 --DROP TABLE Table_test_6_11 ;
        13 --GO
        14 SELECT * FROM Table_test_6_11;
        15 GO

【代码说明】

❑ 代码第7～9行将在表中的现有列中添加一个约束。该列包含一个违反约束的值。因此，将使用WITH NOCHECK以避免根据现有行验证该约束，从而允许添加该约束。

❑ 第5行增加数据行用于后面的约束的验证，第7行使用WITH NOCHECK避免对现有行验证该约束。

【运行效果】使用WITH NOCHECK，执行“SELECT”查询后，可以看到如图5.12所示的结果。

图5.12 执行结果

2. 添加一个DEFAULT约束的实例

【实例5.13】本例使用ALTER TABLE ADD CONSTRAINT DEFAULT修改数据表，在现有列中添加一个DEFAULT约束。示例将创建一个包含两列的表，在第一列插入一个值，另一列保持为NULL。然后在第二列中添加一个DEFAULT约束。验证是否已应用了默认值，另一个值是否已插入第一列以及是否已查询表。具体操作步骤如下。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks2008
        02 GO
        03 --Table_test_6_12
        04 CREATE TABLE Table_test_6_12 ( column_a INT, column_b INT) ; --1
        05 GO
        06 INSERT INTO Table_test_6_12 (column_a) VALUES ( 7 ) ; --2
        07 GO
        08 ALTER TABLE Table_test_6_12 ADD CONSTRAINT col_b_def DEFAULT 50 FOR column_b ;   --3
        09 GO
        10 INSERT INTO Table_test_6_12 (column_a) VALUES ( 10 ) ; --4
        11 GO
        12 SELECT * FROM Table_test_6_12 ;
        13 GO
        14 --DROP   TABLE   Table_test_6_12
        15 --GO

【代码说明】

❑ 代码第4行创建一个包含两列的表，代码2在第一列插入一个值，另一列保持为NULL。

❑ 代码第8行向第二列中添加一个DEFAULT约束。

❑ 代码第10行验证是否已应用了默认值，另一个值是否已插入第一列以及是否已查询表。

【运行效果】执行结果如图5.13所示。

图5.13 执行结果

3. 添加多个包含约束列的实例

【实例5.14】本例使用ALTER TABLE ADD INT IDENTITY CONSTRAINT修改数据表，在现有的数据表中，添加多个包含约束的列；第一个新列具有IDENTITY属性。表中的每一行在标识列中都有新的增量值，具体操作可参考以下步骤。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks2008
        02 GO
        03 CREATE TABLE Table_test_6_13 ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
        04 GO
        05 --1.增加主键，设为标识列
        06 ALTER TABLE Table_test_6_13 ADD column_b INT IDENTITY CONSTRAINT column_b_pk
        07   PRIMARY KEY,
        08 -- 2.增加字段，引用关系于另一字段
        09 column_c INT NULL   CONSTRAINT column_c_fk   REFERENCES Table_test_6_13(column_a),
        10 -- 3.增加字段，强制为非空，并为电话号码的格式
        11 column_d VARCHAR(16) NULL   CONSTRAINT column_d_chk CHECK
        12 (column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR column_d LIKE
        13 '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
        14 --4.增加有默认值，为非空的字段
        15 column_e DECIMAL(3,3) CONSTRAINT column_e_default DEFAULT .222 ;
        16 GO
        17 --5.查看结构
        18 EXEC sp_help Table_test_6_13 ;
        19 GO
        20 --DROP   TABLE Table_test_6_13
        21 --GO

【代码说明】

❑ 本段代码添加了多个包含随新列定义的约束的列。

❑ 第6行的新列具有IDENTITY属性，表中的每一行在标识列中都有新的增量值。

❑ 代码第11～13行表示增加号码，并遵循电话号码的格式。

❑ 代码第15～16行表示增加默认值为222的字段。

【运行效果】结果比较复杂，如图5.14所示。

图5.14 添加多个约束列

4. 添加FOREIGN KEY约束的实例

【实例5.15】本例使用ALTER TABLE ADD CONSTRAINT修改数据表，添加和删除FOREIGN KEY约束。以下实例将创建Table_test_6_14表，然后更改此表。首先添加引用Contact表的FOREIGN KEY约束，然后再删除FOREIGN KEY约束。

执行过程同1中的步骤，输入如下代码。这里不再给出运行效果。

        01 USE AdventureWorks2008 ;
        02 GO
        03 CREATE TABLE Table_test_6_14 (ContactID int) ;
        04 GO
        05 ALTER TABLE Table_test_6_14   ADD CONSTRAINT FK_ContactBacup_Contact
        06   FOREIGN KEY (ContactID)  REFERENCES Person.Contact (ContactID) ;  --1.增加FOREIGN KEY约束
        07 ALTER TABLE Table_test_6_14 DROP CONSTRAINT FK_ContactBacup_Contact ;   --2.删除该约束
        08 GO
        09 --DROP TABLE Table_test_6_14 ;
        10 --GO

【代码说明】

❑ 代码第5～6行添加引用Contact表的FOREIGN KEY约束。

❑ 代码第7行用于删除FOREIGN KEY约束。

5.2.6 禁用/启用CHECK约束

在执行一些特定操作（例如INSERT操作、UPDATE操作和复制处理）时，实现禁用现有的CHECK约束，将极大提高导入数据等操作的执行效率。

【实例5.16】以下实例使用ALTER TABLE NOCHECK CONSTRAINT语句，修改数据表设置，禁用对数据表中CHECK约束值的限制。NOCHECK CONSTRAINT将与ALTER TABLE配合使用来禁用该约束，从而允许执行通常会违反该约束的插入操作；使用CHECK CONSTRAINT将重新启用该约束。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008 ;
        02 GO
        03 CREATE TABLE Table_test_6_15
        04 (   id INT NOT NULL,
        05      name VARCHAR(10) NOT NULL,
        06      salary MONEY NOT NULL
        07      CONSTRAINT salary_cap CHECK (salary < 100000)
        08 )
        09 -- 1.验证插入数据
        10 INSERT INTO Table_test_6_15 VALUES (1,'Joe Brown',65000)
        11 INSERT INTO Table_test_6_15 VALUES (2,'Mary Smith',75000)
        12 -- 2.违反约束.
        13 INSERT INTO Table_test_6_15 VALUES (3,'Pat Jones',105000)
        14 -- 3.禁用约束，并重试插入数据
        15 ALTER TABLE Table_test_6_15 NOCHECK CONSTRAINT salary_cap
        16 INSERT INTO Table_test_6_15 VALUES (3,'Pat Jones',105000)
        17 -- 4.启用约束，并重试插入数据.
        18 ALTER TABLE Table_test_6_15 CHECK CONSTRAINT salary_cap
        19 INSERT INTO Table_test_6_15 VALUES (4,'Eric James',110000) ;
        20 GO
        21 select * from Table_test_6_15
        22 GO

【代码说明】

❑ 代码第10～11行用于验证符合约束的数据处理，第13行用于验证不符合约束的数据处理，salary的值为105000，大于CHECK（salary < 100000）约束。

❑ NOCHECK CONSTRAINT将与ALTER TABLE配合使用来禁用该约束，从而允许执行通常会违反该约束的插入操作。

❑ 第18行使用CHECK CONSTRAINT关键字重新启用该约束。

【运行效果】执行查询的结果如图5.15所示。

图5.15 执行结果

5.2.7 删除约束

【实例5.17】以下实例使用ALTER TABLE DROP CONSTRAINT修改数据表，删除UNIQUE约束，具体操作步骤如下。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。这里不再给出运行效果。

        01 USE AdventureWorks2008 ;
        02 GO
        03 CREATE TABLE Table_test_6_16 ( column_a INT
        04 CONSTRAINT my_constraint UNIQUE) ;
        05 GO
        06 --删除约束的例子
        07 ALTER TABLE Table_test_6_16 DROP CONSTRAINT my_constraint ;
        08 GO
        09 --DROP TABLE Table_test_6_16 ;
        10 --GO

【代码说明】

❑ 代码第7行用于删除约束。

❑ 第9～10行添加了SQL Server中的注释符号“--”。

5.2.8 设置主键

【实例5.18】本节列举一个实例，对5.1.1节中建立的数据库，使用ALTER TABLE WITH DROP CONSTRAINT删除数据表的主键约束，再使用ADD CONSTRAINT关键字为数据表添加主键约束，具体操作步骤如下。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。这里不再给出运行效果。

        01 USE AdventureWorks2008;
        02 GO
        03 ALTER TABLE dbo.Employees
        04    DROP CONSTRAINT PK_Employees ;       --1.删除主键
        05 GO
        06 ALTER TABLE Employees   WITH NOCHECK   --2.增加主键
        07   ADD CONSTRAINT PK_EmployeeNew
        08   PRIMARY KEY CLUSTERED (EmployeeID);
        09 GO

【代码说明】

❑ 代码第3~4行删除dbo.Employees数据表中的PK_Employees主键。

❑ 使用第6~8行的ALTER TABLE生成聚集主键。

5.3 用DROP TABLE语句删除数据表

当用户不再需要数据表的数据时，可以使用DROP TABLE语句将其删除，删除可以释放表的所有数据、索引、触发器、约束所使用的空间。

5.3.1 DROP TABLE的语法结构

删除DROP TABLE语句的语法结构如下所示。

        DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
              table_name [ ,...n ] [ ; ]

【语法说明】

❑ database_name项：要在其中创建表的数据库的名称。

❑ schema_name项：表所属架构的名称。

❑ table_name项：要删除的表的名称。

说明不能使用DROP TABLE删除被FOREIGN KEY约束引用的表。必须先删除引用FOREIGN KEY约束或引用表。如果要在同一个DROP TABLE语句中删除引用表以及包含主键的表，则必须先列出引用表。删除表时，要求用户对表具有CONTROL权限或者具备db_ddladmin固定数据库角色的成员身份。

5.3.2 使用DROP TABLE语句删除数据表的实例

【实例5.19】以下实例将从当前数据库中删除5.1.1节中建立的表及其数据和索引。

        01 USE AdventureWorks2008
        02 GO
        03 DROP TABLE   dbo.Employees
        04 GO

【代码说明】

❑ DROP TABLE后跟表名称，提示命令成功能完成即可。

5.4 临时表

临时表与永久表相似，但临时表存储在tempdb数据库中，当不再使用时会自动删除。临时表有两种类型：本地临时表和全局临时表。

❑ 本地临时表的名称以单个数字符号（#）为前缀，它们仅对当前的用户连接是可见的，当用户从SQL Server实例断开连接时被删除。

❑ 全局临时表的名称以两个数字符号（##）为前缀，创建后对任何用户都是可见的，当所有引用该表的用户从SQL Server断开连接时被删除，即本地临时表仅在当前会话中可见，而全局临时表在所有会话中都可见。

说明临时表的生存周期和普通的数据表不同，可以举一个例子来说明临时表的使用。例如在5.1.1节中创建的employees表，任何在数据库中，有使用该表的安全权限的用户，都可以使用该表。如果数据库会话创建了本地临时表#employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了##employees全局临时表，则数据库中的任何用户均可使用该表。如果该表在创建后没有其他用户使用，则当创建者断开连接时该表删除。如果创建该表后另一个用户在使用该表，则SQL Server将在用户断开连接并且所有其他会话不再使用该表时将其删除。

技巧在开发过程中，建议使用表变量而不使用临时表。当必须对临时表显式地创建索引时，或多个存储过程或函数必须使用表值时，临时表很有用。通常，表变量可提供更有效的查询处理。

5.4.1 创建临时表的实例

用户可以使用CREATE TABLE语句创建临时表。

【实例5.20】以下实例使用CREATE TABLE语句创建临时表，并向数据表中增加记录。

        01 CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
        02 GO
        03 INSERT INTO #MyTempTable VALUES (1);
        04 GO

【代码说明】

❑ 第1行创建临时表MyTempTable。

❑ 第3行为临时表添加记录。

5.4.2 删除临时表的实例

用户也可以显式地删除临时表。

【实例5.21】用以下实例创建一个临时表，测试该表是否存在，然后使用DROP TABLE语句删除该表，最后再次测试该表是否存在，具体操作可以参考以下步骤。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 CREATE TABLE #temptable (col1 int);   --1.创建临时表
        02 GO
        03 INSERT INTO #temptable VALUES (10);   --2.插入数据
        04 GO
        05 SELECT * FROM #temptable;                --3.测试数据的存在
        06 GO
        07 IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL
        08 DROP TABLE #temptable;          --4.删除表
        09 GO
        10 --测试删除
        11 SELECT * FROM #temptable;      --5.测试数据表的存在

【代码说明】

❑ 代码第1行创建一个临时表，名为#temptable，并插入数据。

❑ 代码第5行测试该表是否存在。

❑ 代码第8行删除该表，然后测试数据是否存在。

【运行效果】运行结果如下所示。

        (1 行受影响)

        (1 行受影响)
        消息208，级别16，状态0，第2 行
        对象名 '#temptable' 无效。

5.5 小结

本章并没有介绍直接使用可视化界面来创建数据表，都是使用的T-SQL语句。这些语句并不是直接写在数据表中，而是需要打开专门书写SQL语句的“查询”窗口。在这个窗口中，可以任意输入T-SQL承认的语法格式，还可以保存这些代码，扩展名为.sql。如果书上提供了很多.sql扩展名的文件，那可以直接拿来在这个查询窗口中运行。

5.6 本章练习

1. 创建表的语句是__________。

2. 在“Pubs”表中查询出所有女生的总人数的语句正确的是（）。

A. select count(ID) from Pubs where Sex='女'

B. select sex='女' from Pubs

C. select ID from Pubs where Sex='女'

3. 以下哪个语句是正确的？（）

A. select Name="张三" from Pubs

B. select Name, DepartMent from Pubs group by DepartMent

C. select count(StudentID) from Pubs group DepartMent

4. 表和列的别名有什么作用？

第二篇 SQL Server 2008数据管理

第5章 数据表

5.1 用CREATE TABLE语句创建表

5.1.1 一个创建表的小实例

5.1.2 基本语法

5.1.3 创建自动编号列

5.1.4 创建非空列

5.1.5 字段的默认值

5.1.6 选择表存放文件组

5.1.7 使用CHECK约束

5.1.8 设置字段的排序规则

5.2 用ALTER TABLE语句修改表

5.2.1 基本语法

5.2.2 修改字段属性

5.2.3 添加字段

5.2.4 删除字段

5.2.5 添加约束

5.2.6 禁用/启用CHECK约束

5.2.7 删除约束

5.2.8 设置主键

5.3 用DROP TABLE语句删除数据表

5.3.1 DROP TABLE的语法结构

5.3.2 使用DROP TABLE语句删除数据表的实例

5.4 临时表

5.4.1 创建临时表的实例

5.4.2 删除临时表的实例

5.5 小结

5.6 本章练习

第5章数据表