第3章 数据库与表的操作

在关系数据库中，数据都是存储在表中的。在上一章已经讨论了在SQL Server 2005中使用Management Studio创建数据库和表。在本章将主要介绍使用SQL对数据库和表进行创建、编辑、更新和删除操作。

3.1 创建、修改和删除数据库

要创建能够满足业务需要的数据库，要求对如何设计、创建和维护各个组件有深刻的理解，这样才能确保数据库最佳地运行。本节首先简单介绍一下SQL Server 2005数据库的基本组成，而后详细讲解如何使用Transact-SQL语言创建、修改和删除数据库。

3.1.1 SQL Server数据库的组成

在Microsoft SQL Server 2005中，用于数据存储的实用工具是数据库。数据库的物理表现是操作系统文件，即在物理上，一个数据库由一个或多个磁盘上的文件组成。这种物理表现只对数据库管理员是可见的，而对用户是透明的。逻辑上，一个数据库由若干个用户可视的组件构成，如表、视图、角色等，这些组件称为数据库对象。用户利用这些逻辑数据库的数据库对象存储或读取数据库中的数据，也直接或间接地利用这些对象在不同应用程序中完成存储、操作和检索等工作。

★ 说明 ★

SQL Server 2005将数据库映射为一组操作系统文件。每个SQL Server 2005数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。数据文件包含数据和对象，例如表、索引、存储过程和视图。日志文件包含恢复数据库中的所有事务所需的信息。可以将数据文件集合起来，放到文件组中，用于帮助数据布局和管理任务，例如备份和还原操作。

1.数据库文件

SQL Server 2005的数据文件又分为主要数据文件和次要数据文件两种形式。SQL Server 2005数据库具有的文件，如表3.1所示。

表3.1 SQL Server 2005数据库具有的文件及其说明

★ 说明 ★

2.数据库文件组

SQL Server有两种类型的文件组：

· 主文件组：包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。

·用户定义文件组：用户定义文件组是通过在CREATE DATABASE或ALTER DATABASE语句中使用FILEGROUP关键字指定的任何文件组。

★ 说明 ★

一些系统可以通过在特定磁盘驱动器上放置的数据和索引，来提高自身的性能。文件组可以对此进程提供帮助。系统管理员可以为每个磁盘驱动器创建文件组，然后将特定的表、索引，或者表中的text、ntext或image数据指派给特定的文件组。SQL Server 2005在没有文件组时也能有效地工作，因此许多系统不需要指定用户定义文件组。在这种情况下，所有文件都包含在主文件组中，而且SQL Server 2005可以在数据库内的任何位置分配数据。

每个数据库中都有一个文件组作为默认文件组运行。当SQL Server给创建时没有为其指定文件组的表或索引分配页时，将从默认文件组中进行分配。一次只能有一个文件组作为默认文件组。如果没有指定默认的文件组，主文件组则成为默认的文件组。

3.文件的大小

SQL Server 2005文件可以从它们最初指定的大小开始自动增长。在定义文件时，可以指定一个特定的增量。每次填充文件时，其大小均按此增量来增长。如果文件组中有多个文件，则它们在所有文件被填满之前不会自动增长。填满后，这些文件会循环增长。

每个文件还可以指定一个最大大小。如果没有指定最大大小，文件可以一直增长到用完磁盘上的所有可用空间。如果SQL Server作为数据库嵌入某应用程序，而该应用程序的用户无法迅速与系统管理员联系，则此功能就特别有用。用户可以使文件根据需要自动增长，以减轻监视数据库中的可用空间和手动分配额外空间的管理负担。

3.1.2 创建数据库——CREATE DATABASE

创建数据库时，必须为数据库提供名称、大小和操作系统文件名（物理文件名称）。在Transact-SQL中，可以使用CREATE DATABASE语句创建数据库。其语法可表示如下：

        CREATE DATABASE database_name
        [ ON
          [ ＜ filespec ＞ [1,...n ] ]
          [ , ＜ filegroup ＞ [1,...n ] ]
        ]
        [ LOG ON { ＜ filespec ＞ [1,...n ] } ]
        [ COLLATE collation_name ]
        [ FOR LOAD | FOR ATTACH ]

其中，＜ filespec ＞定义如下所示。

        [ PRIMARY ]
        ( [ NAME = logical_file_name , ]
            FILENAME = 'os_file_name'
            [ , SIZE = size ]
            [ , MAXSIZE = { max_size | UNLIMITED } ]
        [ , FILEGROWTH = growth_increment ] ) [1,...n ]

＜ filegroup ＞的定义为：

        FILEGROUP filegroup_name ＜ filespec ＞ [1,...n ]

所有用“[]”括起来的表示是可以省略的选项，[1,...n]表示同样的选项可以重复1～n遍；＜＞括起来表示是对一组若干选项的代替，实际编写语句时，应该用相应的选项来代替。

各关键字和选项的含义如表3.2所示。

表3.2 CREATE DATABASE语句中关键字和选项的含义

实例1 使用CREATE DATABASE创建数据库

本实例将在SQL Server服务器上创建一个数据库。该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。主数据文件在主文件组中，而用户定义文件组包含两个次要数据文件。实例代码如下：

        CREATE DATABASE Library
        ON PRIMARY                                 --主文件和主文件组
          ( NAME='Library_Primary',
            FILENAME='E:\DataBase\Library_Prm.mdf',
          SIZE=4MB,
          MAXSIZE=10MB,
          FILEGROWTH=1MB),
        FILEGROUP Library_FG1                      --其他文件组
          ( NAME = 'Library_FG1_Dat1',
          FILENAME ='E:\DataBase\Library_FG1_1.ndf',
          SIZE = 1MB,
          MAXSIZE=10MB,
          FILEGROWTH=1MB),
          ( NAME = 'Library_FG1_Dat2',
          FILENAME ='E:\DataBase\Library_FG1_2.ndf',
          SIZE = 1MB,
          MAXSIZE=10MB,
          FILEGROWTH=1MB)
        LOG ON                                     --日志文件
          ( NAME='Library_log',
          FILENAME = 'E:\DataBase\Library.ldf',
          SIZE=1MB,
          MAXSIZE=10MB,
          FILEGROWTH=1MB);

在Management Studio的查询分析器中运行该代码，即实现了数据库Library的创建。此时在Management Studio的对象资源管理器中就可以发现新创建的Library数据库，如图3.1所示。

图3.1 对象资源管理器中的Library数据库

右键单击Library选项，在弹出菜单中选择“属性”命令，弹出“数据库属性”对话框，在“文件”选项页中，可以发现创建的Library数据库的文件及文件组信息，如图3.2所示。

图3.2 “数据库属性”窗口中显示的Library数据库的文件

由于在指定数据文件时，使用PRIMARY关键字，所以文件“E:\DataBase\Library_Prm.mdf”为主数据文件，它属于主文件组。而用户定义文件组“Library_FG1”包含两个次要数据文件，即“E:\DataBase\Library_FG1_1.ndf”和“E:\DataBase\Library_FG1_2.ndf”。在资源管理器的“E:\DataBase”路径下可以发现这些数据文件和日志文件。

数据库建立后需要设计数据库对应的表、视图、存储过程等元素。本书将陆续讨论使用Transact-SQL语句创建这些元素。

3.1.3 修改数据库——ALTER DATABASE

SQL Server的数据文件可以自动扩充长度，所以数据库的大小也会自动增加。但是如果设置了最大文件长度，则数据库的扩充依然是有必要进行的操作。修改数据库的大小实质上是修改数据文件和日志文件的长度，或者增加/删除操作系统文件。这种操作可以通过下面的语法来实现：

        ALTER DATABASE database
        {    ADD FILE ＜filespec＞ [,...n] [TO FILEGROUP { filegroup_name | DEFAULT }]
          | ADD LOG FILE ＜filespec＞ [,...n]
          | REMOVE FILE logical_file_name
          | ADD FILEGROUP filegroup_name
          | REMOVE FILEGROUP filegroup_name
          | MODIFY FILE ＜filespec＞
          | MODIFY FILEGROUP filegroup_name[DEFAULT|NAME = new_filegroup_name]
        }

其中，＜ filespec ＞的定义代码如下所示。

          NAME = logical_file_name
          [, NEWNAME = new_logical_name ]
          [, FILENAME = 'os_file_name' ]
          [, SIZE = size [ KB | MB | GB | TB ] ]
          [, MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
          [, FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
          [ , OFFLINE ]

其主要关键字/选项及其含义如表3.3所示。

表3.3 ALTER DATABASE语句中主要关键字和选项的含义

下面给出两个修改数据的实例。

实例2 向数据库中添加由两个文件组成的文件组

本实例首先向Library数据库中添加一个文件组Library_FG2，而后向该文件组中添加两个文件，即Library_FG2_Dat1和Library_FG2_Dat2。代码如下：

        --添加文件组
        ALTER DATABASE Library
        ADD FILEGROUP Library_FG2
        --向文件组中添加数据文件
        ALTER DATABASE Library
        ADD FILE
          ( NAME = 'Library_FG2_Dat1',
          FILENAME ='E:\DataBase\Library_FG2_1.ndf',
          SIZE = 1MB,
          MAXSIZE=10MB,
          FILEGROWTH=1MB),
          ( NAME = 'Library_FG2_Dat2',
          FILENAME ='E:\DataBase\Library_FG2_2.ndf',
          SIZE = 1MB,
          MAXSIZE=10MB,
          FILEGROWTH=1MB)
        TO FILEGROUP Library_FG2

运行该代码，此时在Management Studio中的Library数据库属性窗口中，显示的文件及文件组信息如图3.3所示。

图3.3 “数据库属性”窗口中显示的Library数据库的文件

实例3 修改数据库文件

本实例将Library数据库中的Library_FG1_Dat1数据文件的初始大小修改为5MB，而将数据文件Library_FG2_Dat2删除。代码如下：

        --修改文件初始大小
        ALTER DATABASE Library
        MODIFY FILE
          (NAME = Library_FG1_Dat1,
          SIZE = 5MB)
        --删除一个数据文件
        ALTER DATABASE Library
        REMOVE FILE Library_FG2_Dat2

运行该代码，此时在Management Studio中的Library数据库属性窗口中，显示的文件及文件组信息如图3.4所示。

图3.4 “数据库属性”窗口中显示的Library数据库的文件

可见，文件已成功修改。

3.1.4 查看数据库信息——sp_helpdb、sp_spaceused

1.sp_helpdb

在SQL Server中，可以使用系统存储过程sp_helpdb查看数据库，或所有数据库的信息。

实例4 查看Library数据库的信息

使用存储过程sp_helpdb查看Library数据库的信息代码如下：

        sp_helpdb 'Library'

运行结果如图3.5所示。

图3.5 Library数据库的信息

其显示结果包含的列及含义如表3.4所示。

表3.4 数据库信息结果包含的列及其含义

实例5 查看服务器中的所有数据库的信息

直接运行sp_helpdb命令，即可查看服务器中存在的所有数据库的信息。运行结果如图3.6所示。

图3.6 服务器中的所有数据库的信息

2.sp_spaceused

显示行数、保留的磁盘空间以及当前数据库中的表、索引视图或SQL Server 2005 Service Broker队列所使用的磁盘空间，或者显示由整个数据库保留和使用的磁盘空间。其语法格式如下所示。

        sp_spaceused  数据库对象

实例6 查看数据库表的磁盘空间信息

本实例将通过sp_spaceused系统存储过程，显示本书第2章创建的Example数据库中客户资料表的磁盘空间信息。代码如下：

        USE Example
        GO
        sp_spaceused 'Example.dbo.客户资料'
        GO

运行结果如图3.7所示。

图3.7 客户资料表的磁盘空间信息

其显示结果包含的列及含义如表3.5所示。

表3.5 数据库表的磁盘空间信息结果包含的列及其含义

如果sp_spaceused系统存储过程后不指明数据库对象，则可以查询当前正在使用的数据库的磁盘空间信息。

实例7 查看数据库的磁盘空间信息

本实例将通过sp_spaceused系统存储过程，查看新创建的Library数据库的磁盘空间信息。代码如下：

        USE Library
        GO
        sp_spaceused
        GO

运行结果如图3.8所示。

图3.8 Library数据库的磁盘空间信息

其中，显示结果包含的列及含义如表3.6所示。

表3.6 数据库的磁盘空间信息结果包含的列及其含义

★ 注意 ★

3.1.5 删除数据库——DROP DATABASE

从SQL Server实例中删除一个或多个数据库可使用DROP DATABASE语句。其语法如下所示。

        DROP DATABASE database_name [ ,...n ]

另外，在删除数据库时，要注意以下几点：

· 若要使用DROP DATABASE删除数据库，则连接的数据库上下文不能与要删除的数据库相同。

· 无法删除系统数据库。

· 不能删除当前正在使用（表示正打开供任意用户读写）的数据库。

· 无论数据库处于下列哪种状态，都可将其删除：脱机状态、只读状态或可疑状态等。

· 执行删除数据库操作会从SQL Server实例中删除数据库，并删除该数据库使用的物理磁盘文件。如果在执行删除操作时，数据库或它的任意一个文件处于脱机状态，则不会删除磁盘文件。可使用Windows资源管理器手动删除这些文件。

3.2 SQL Server中的表

关系数据库通常包含多个表。数据库实际上可以看作是表的集合，数据库的数据或信息都是存储在表中的。表是对数据进行存储和操作的一种逻辑结构，对用户而言，一个表表示一个数据库对象。例如，一个公司数据库中，会有雇员表、部门表、库存表、销售表、工资表等。

在介绍使用Transact-SQL创建和操作表前，有必要对SQL Server中表的有关知识作一下简单介绍。

3.2.1 表的结构及设计

在本书的第1章已经简单介绍了表的基本结构。数据库中的表与人们日常生活中使用的表格类似，它也是由行（Row）和列（Column）组成的。列由相同类的信息组成，每列又称为一个字段，每列的标题称为字段名。行包括了若干列的信息项，一行数据称为一条记录，它表达有一定意义的信息组合。一个表由一条或多条记录组成，没有记录的表称为空表。

例如，在本书第2章创建的客户资料表中，每一行代表一个客户，各列分别代表该客户的信息，如客户编号、客户名称、联络人、电话、地址等。

设计数据库时，应先确定需要什么样的表，各表中都有哪些数据，以及各个表的存取权限等。在创建和操作表的过程中，将对表进行更为细致的设计。

创建一个表最有效的方法是将表中所需的信息一次定义完成，包括数据约束和附加成分。也可以先创建一个基础表，向其中添加一些数据并使用一段时间。这种方法使用户可以在添加各种约束、索引、默认设置、规则和其他对象形成最终设计之前，发现哪些事务最常用，哪些数据经常输入。

设计表时应注意以下的问题：

· 表所包含的数据的类型。

· 表的各列及每一列的数据类型（如果必要，还应注意列宽）。

· 哪些列允许空值。

· 是否要使用以及何时使用约束、默认设置或规则。

· 所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。

在SQL Server 2005中，每个数据库最多可包含20亿个表，每个表可包含1024列。表的行数及总大小仅受可用存储空间的限制。每行最多包括8060个字节。对于带varchar、nvarchar、varbinary或sql_variant列（导致已定义表的总宽超过8060字节）的表，此限制将放宽。其中每列的长度仍必须在8000字节的限制内，但是它们的总宽可能超过表的8060字节的限制。

3.2.2 Transact-SQL对表的要求

在创建表时要遵循严格的语法定义。在Transact-SQL语言中，必须满足以下规定：

· 每个表有一个名称，称为表名或关系名。表名必须以字母开头，最大长度为30个字符。

· 一张表包含若干列，列名唯一，列名也成为属性名。

· 同一列的数据必须要有相同的数据类型。

· 表中的每一列数值必须为一个不可分割的数据项。

· 表中的一行称为一条记录。

3.2.3 表的类型

在SQL Server中，并不是所有的表都是相同的。有些表是永久的，有些表则是临时的。有些表是模式对象，而有些表则包含在模块中，所有的模块表也是临时的。

在SQL Server 2005中包含的表的类型有：永久表、已分区表、临时表和系统表。

1.永久表

永久表保存存储在数据库中的SQL数据。它是一种最常见的表，如果没有特别说明，通常所说的表就是指永久表。只要表的定义存在，永久表就始终存在。它的创建语句为CREATE TABLE。

2.已分区表

已分区表是将数据水平划分为多个单元的表，这些单元可以分布到数据库中的多个文件组中。在维护整个集合的完整性时，使用分区可以快速而有效地访问或管理数据子集，从而使大型表或索引更易于管理。

如果表非常大或者有可能变得非常大，并且属于下列任一情况，那么分区表将很有意义：

· 表中包含或可能包含以不同方式使用的许多数据。

· 对表的查询或更新没有按照预期的方式执行，或者维护开销超出了预定义的维护期。

已分区表支持所有与设计和查询标准表关联的属性和功能，包括约束、默认值、标识和时间戳值、触发器和索引。

使用CREATE TABLE创建已分区表前，必须首先创建分区函数以指定表分区的方式。可使用CREATE PARTITION FUNCTION创建分区函数。其次，必须创建分区架构，以指定将保存由分区函数指示的分区的文件组。可使用CREATE PARTITION SCHEME创建分区架构。

3.临时表

临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除。临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。

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

全局临时表的名称以两个数字符号“##”打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQL Server断开连接时被删除。

例如，如果创建了employees表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表#employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了全局临时表##employees，则数据库中的任何用户均可使用该表。如果该表在创建后没有其他用户使用，则当断开连接时该表删除。如果创建该表后另一个用户在使用该表，则SQL Server将在断开连接并且所有其他会话不再使用该表时将其删除。

4.系统表

SQL Server将定义服务器配置及其所有表的数据存储在一组特殊的表中，这组表称为系统表。除非通过专用的管理员连接（DAC，只能在Microsoft客户服务的指导下使用），否则用户无法直接查询或更新系统表。

任何用户都不应直接更改系统表。例如，不要尝试使用DELETE、UPDATE、INSERT语句或用户定义的触发器修改系统表。

★ 说明 ★

3.3 SQL Server 2005中的数据类型

在创建表时，需要指明表中各列的数据类型。在SQL Server 2005中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型。

在SQL Server 2005中，可用的数据类型包括系统数据类型和用户自定义数据类型。其中系统数据类型包括数字、日期和时间、字符数据、二进制数据和其他数据类型。本章将对这些数据类型作详细介绍。

3.3.1 数字类型

数字类型数据主要用来存储数据信息，包括整数和浮点数两类。

1.整数

整数数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。

SQL Server 2005中包含的整数数据类型及其范围如表3.7所示。

表3.7 SQL Server 2005中的整数类型及其范围

其中，money和smallmoney数据类型代表货币或货币值，精确到它们所代表的货币单位的万分之一。也就是说，如果一个对象被定义为money，则它最多可以包含19位数字，其中小数点后可以有4位数字。而之所以把money和smallmoney划归为整数数据类型，是因为它在SQL Server中的存储方式与bigint和int完全相同。

使用货币数据时，不需要用单引号“’”引起来。但需要记住，虽然可以指定前面带有货币符号的货币值，但SQL Server不存储任何与符号关联的货币信息，它只存储数值。

实例8 使用货币数据类型

本实例将定义一个money类型的变量，为其赋值125美元15美分，而后查看在SQL Server中存储的变量值。代码如下：

        --声明money类型的变量
        DECLARE @dollars AS money
        --为变量赋值
        SET @dollars = $125.15
        --显示SQL Server中存储的货币值
        SELECT @dollars

运行结果如图3.9所示。

图3.9 SQL Server中存储的money值

可见，虽然赋值时指定了货币符号，然而在SQL Serve中并没有存储与货币符号有关的任何信息。

★ 说明 ★

2.浮点数

浮点数据类型用于存储十进制小数。SQL Server 2005中包含的浮点数据类型及其精度如表3.8所示。

表3.8 SQL Server 2005中的浮点数据类型

其中，float[(n)]中的n为用于存储float数值尾数的位数，以科学记数法表示，因此可以确定精度和存储大小。如果指定了n，则它必须是介于1和53之间的某个值。n的默认值为53。

此外，SQL Server 2005还提供了带有固定精度和小数位数的数值数据类型：decimal和numeric。其定义如下：

        decimal[(p[, s])]

或者

        numeric[(p[, s])]

使用最大精度时，有效值从-1038 +1～1038-1。其中：

· p为精度，最多可以存储的十进制数字的总位数，包括小数点左边和右边的位数。该精度必须是从1到最大精度38之间的值。默认精度为18。

· s为小数位数，小数点右边可以存储的十进制数字的最大位数。小数位数必须是从0到p之间的值。仅在指定精度后才可以指定小数位数。默认的小数位数为0，因此，0＜= s ＜= p。最大存储大小基于精度而变化。

decimal或numeric数据类型的精度和存储字节数的关系如表3.9所示。

表3.9 decimal或numeric数据类型的精度和存储字节数的关系

3.3.2 时间和日期类型

在SQL Server 2005中，日期和时间数据类型包括datetime和smalldatetime两种类型，它们用于表示某天的日期和时间。datetime和smalldatetime所表示时间、日期的范围和精度如表3.10所示。

表3.10 SQL Server 2005中的时间和日期类型

其中：

· datetime数据类型所占用的存储空间为8个字节。其中第一个4字节存储“基础日期”（即1900年1月1日）之前或之后的天数；另外一个4字节存储天的时间（以午夜后经过的毫秒数表示）。如果在输入数据时省略了时间部分，则系统将12:00:00:000AM作为时间默认值；如果省略了日期部分，则系统将1900年1月1日作为日期默认值。

· smalldatetime值存储为4字节的整数。第一个2字节存储1900年1月1日后的天数；另外一个2字节存储午夜后经过的分钟数。

下面介绍一下在SQL Server中可用的日期、时间输入格式。

1.日期输入格式

日期的输入格式大致可分为三类：

（1）英文+数字格式：此类格式中，月份可用英文全名或缩写，且不区分大小写；年和月日之间可不用逗号；年份可为4位或2位，当其为2位时，若值小于50，则视为20xx年，若大于或等于50，则视为19xx年；若日部分省略，则视为当月的1号。以下格式均为正确的日期格式：

        June 182004      Oct 11999        January 2007          2004 February
        1999 May 12       20035 Sep        99 June                July 00

（2）数字+分隔符格式：允许把斜杠（/）、连接符（-）和小数点（.）作为用数字表示的年、月、日之间的分隔符。格式如下所示。

        YMD ：   2000/6/22    2000-6-22    2000.6.22
        MDY ：   3/5/2000     3-5-2000     3.5.2000
        DMY ：   31/12/1999   31-12-1999   31.12.2000

（3）纯数字格式：纯数字格式是以连续的4位、6位或8位数字来表示日期。如果输入的是6位或8位数字，系统将按年、月、日来识别，即YMD格式，并且月和日都是用两位数字来表示；如果输入的数字是4位数，系统认为这4位数代表年份，其月份和日默认为此年度的1月1日。格式如下所示。

        20070612——2007年6月12日
        991218——1999年12月18日
        1999——1999年

2.时间输入格式

在输入时间时，必须按“小时、分钟、秒、毫秒”的顺序来输入。在其间用冒号“:”隔开，毫秒部分可以用小数点“.”分隔，其后第一位数字代表十分之一秒，第二位数字代表百分之一秒，第三位数字代表千分之一秒。

当使用12小时制时，用AM（am）和PM（pm）分别指定时间是午前或午后，若不指定，系统默认为AM。AM与PM均不区分大小写，格式如下所示。

        6:7:9.2pm——下午6时7分9秒200毫秒
        11:22:5.123Am——上午11时22分5秒123毫秒

3.SET DATEFORMAT命令

在SQL Server中，可以使用SET DATEFORMAT命令来设置用于输入datetime或smalldatetime数据的日期部分（月/日/年）的顺序。其语法如下：

        SET DATEFORMAT {format | @format_var}

其中，format | @format_var是日期的顺序。有效的参数包括MDY、DMY、YMD、YDM、MYD和DYM。在默认情况下，日期格式为MDY。

例如，当执行Set DateFormat YMD之后，日期的格式为年/月/日的形式；当执行SetDateFormat DMY之后，日期的格式为日/月/年的形式。

实例9 使用SET DATEFORMAT更改日期的输入格式

本实例将使用SET DATEFORMAT命令将时间格式更改为习惯的年/月/日的形式，而后声明时间变量并为其赋值，最后显示变量的值。代码如下：

        -- 更改日期格式为年/月/日的形式
        SET DATEFORMAT ymd;
        -- 声明日期时间变量
        DECLARE @datevar DATETIME;
        -- 按照年/月/日的形式为变量赋值
        SET @datevar = '2007/10/0911:22:5.123pm';
        --显示变量的值
        SELECT @datevar AS '时间显示';

运行结果如图3.10所示。

图3.10 变量所存储的时间结果

★ 说明 ★

3.3.3 字符数据类型

字符数据类型可以用来存储各种字母、数字符号和特殊符号。在SQL Server 2005中，字符数据类型包括字符串数据类型和Unicode数据类型。

1.字符串数据类型

字符串数据的类型包括char、varchar和text，它们都是非Unicode数据类型。字符数据是由任何字母、符号和数字任意组合而成的数据。字符串数据类型及其说明如表3.11所示。

表3.11 字符串数据类型及其说明

关于char和varchar的使用，需要注意以下几点：

· 如果未在数据定义或变量声明语句中指定n，则默认长度为1。如果在使用CAST和CONVERT函数时未指定n，则默认长度为30。

· DBMS在进行排序或处理字符时，对固定长度字符变量的处理效率要远高于可变长度字符变量。

· 如果列数据项的大小一致，则使用char。

· 如果列数据项的大小差异相当大，则使用varchar。

· 如果列数据项大小相差很大，而且大小可能超过8000字节，则使用varchar(max)。

2.Unicode数据类型

在Microsoft SQL Server中，传统的非Unicode数据类型允许使用由特定字符集定义的字符。在SQL Server安装过程中，允许选择一种字符集。而使用Unicode数据类型，列中可以存储任何由Unicode标准定义的字符。在Unicode标准中，包括了以各种字符集定义的全部字符。使用Unicode数据类型，所占用的空间是使用非Unicode数据类型所占用的空间大小的两倍。

在SQL Server中，Unicode数据以nchar、nvarchar或ntext数据类型存储。Unicode数据类型及其说明如表3.12所示。

表3.12 Unicode数据类型及其说明

在数据库中，字符型数据的应用是十分灵活和广泛的。如前面提到的电话号码信息，它虽然是一些数字信息，但是一般均采用字符型数据进行存储。如某人的电话号码为“010674600”，采用数字数据存储，则最左边的“0”将被忽略，记录的信息实际为“10674600”；而采用字符型数据进行存储则不会出现这个问题。

3.3.4 二进制数据类型

与前面介绍的数据类型不同，二进制数据类型不专门用于存储特定类型的数据，几乎可以存储任何类型的数据，包括图形图像、多媒体和字处理文档等。

SQL Server 2005中的二进制数据类型如表3.13所示。

表3.13 二进制数据类型

★ 说明 ★

3.3.5 其他数据类型

在SQL Server 2005中，还包含有其他的一些数据类型，如timestamp、sql_variant、uniqueidentifier、table、xml等。

1.timestamp

timestamp也称为时间戳数据类型，它提供数据库范围内的唯一值，反映数据库中数据修改的相对顺序，相当于一个单调上升的计数器。存储大小为8个字节。

每次修改或插入包含timestamp列的行时，就会在timestamp列中插入增量数据库时间戳值。使用某一行中的timestamp列可以很容易地确定该行中的任何值自上次读取以后是否发生了更改。如果对行进行了更改，就会更新该时间戳值；如果没有对行进行更改，则该时间戳值将与以前读取该行时的时间戳值一致。

★ 说明 ★

2.uniqueidentifier

uniqueidentifier用于存储一个16字节长的二进制数据类型，它是SQL Server根据计算机网络适配器地址和CPU时钟，产生的唯一号码而生成的全局唯一标识符代码（Globally Unique Identifier，简称GUID）。

当表的记录行要求唯一时，GUID是非常有用的。例如，在客户标识号列使用这种数据类型可以区别不同的客户。

uniqueidentifier数据类型的列或局部变量，可通过以下方式初始化为一个值：

· 使用NewID函数。

· 从xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx形式的字符串常量转换，其中，每个x是一个在0～9或a～f范围内的十六进制数字。例如，6F9619FF-8B86-D011-B42D-00C04FC964FF为有效uniqueidentifier值。

★ 说明 ★

3.sql_variant

sql_variant用于存储除文本、图形数据、用户自定义数据和timestamp类型数据外的其他任何合法的SQL Server数据。使用sql_variant数据类型需要指明以下几点：

· sql_variant的最大长度可以是8016个字节，这包括基类型信息和基类型值，实际基类型值的最大长度是8000个字节。

· 对于sql_variant数据类型，必须先将它转换为其基本数据类型值，然后才能参与诸如加、减这类运算。

· 可以为sql_variant分配默认值。该数据类型还可以将NULL作为其基础值，但是NULL值没有关联的基类型。而且sql_variant不能以另一个sql_variant作为它的基类型。

4.table

table数据类型用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或存储过程返回查询结果更加方便、快捷。

5.xml

xml是SQL Server 2005新增的数据类型，可以使用户在SQL Server中储存XML格式字段，其存储内容是符合XML格式的文件，最大可储存2GB。

3.3.6 用户自定义数据类型

除了使用系统提供的数据类型外，用户还可以用自定义的数据类型来定义表的列或声明变量。用户定义的数据类型基于在Microsoft SQL Server中提供的数据类型。当几个表中必须存储同一种数据类型，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。

1.创建用户自定义数据类型

创建用户定义的数据类型可以使用CREATE TYPE语句，或者系统存储过程sp_addtype。当创建用户定义的数据类型时，必须提供三个数：数据类型的名称、所基于的系统数据类型和数据类型是否可以为空。

CREATE TYPE语句的语法如下：

        CREATE TYPE type_name
        FROM base_type [ ( precision [ , scale ] ) ] [ NULL | NOT NULL ]

sp_addtype存储过程的语法如下：

        sp_addtype {type},[,system_data_bype][,'null_type']

其中，type是用户定义的数据类型的名称。system_data_type是系统提供的数据类型。null_type表示该数据类型是如何处理空值的，必须使用单引号引起来，例如'NULL'、'NOT NULL'。

实例10 使用sp_addtype自定义数据类型

本实例将创建一个用户定义的数据类型address，其基于的系统数据类型是变长为60的字符，不允许为空。代码如下：

        USE Library
        GO
        sp_addtype address,'varchar(60)','NOT NULL'
        GO

运行代码后，即创建了用户定义的数据类型address。

在SQL Server 2005的Management Studio中的对象资源管理器中，也可以通过图形界面的形式创建、查看、修改和删除用户定义的数据类型。本实例创建的数据类型在对象资源管理器中的位置如图3.11所示。

图3.11 Management Studio的对象资源管理器中的用户自定义数据类型

2.删除用户自定义数据类型

当用户定义的数据类型不需要时，可以将其删除。删除用户定义的数据类型的命令如下：

        sp_droptype {'type'}

比如删除创建的用户定义数据类型address，可采用如下的代码：

        USE Library
        GO
        sp_droptype 'address'
        GO

★ 注意 ★

3.3.7 数据类型同义词

为了实现与SQL-92标准的兼容性，在SQL Server 2005中定义了数据类型同义词。在诸如CREATE TABLE、CREATE PROCEDURE或DECLARE @variable这类数据定义语言（DDL）语句中，数据类型同义词可用来代替相应的基本数据类型名。但是，创建对象后，将为对象分配与同义词相关联的基本数据类型，即看不到同义词。

SQL Server 2005中定义的数据类型同义词，以及它们映射到的SQL Server系统数据类型如表3.14所示。

表3.14 数据类型同义词与对应的系统数据类型

比如，可以通过指定national character varying类型创建表：

        CREATE TABLE MyTable(ID int PRIMARY KEY, Name national character varying(10))

实际上，在MyTable表中为Name列指定了nvarchar(10)数据类型。

3.4 创建表（CREATE TABLE）

SQL Server 2005提供了两种方法创建数据库表，一种方法是利用Management Studio创建表；另一种方法是利用Transact-SQL语句中的CREATE命令创建表。前者在第2章中已有介绍，本节将主要介绍使用CREATE命令创建表的操作。

3.4.1 实例表设计

创建一个数据表时，主要包括以下几个组成部分。

（1）字段名（列名）：字段名可长达128个字符，可包含中文、英文字母、下划线、#号、货币符号（￥）及AT符号（@），且同一表中不许有重名列。

（2）字段数据类型：可采用的数据类型参见3.3节的介绍。

（3）字段的长度、精度和小数位数：

字段的长度是指字段所能容纳的最大数据量，但对不同的数据类型来说，长度对字段的意义可能有些不同。

· 对字符串与Unicode数据类型而言，长度代表字段所能容纳的字符的数目，因此它会限制用户所能输入的文本长度。

· 对数值类的数据类型而言，长度则代表字段使用多少个字节来存放数字。

· 对binary、varbinary、image数据类型而言，长度代表字段所能容纳的字节数。

精度是指数中数字的位数，包括小数点左侧的整数部分和小数点右侧的小数部分；小数位数则是指数字小数点右侧的位数。例如，数字12345.678，其精度为8，小数位数为3。所以只有数值类的数据类型，才有必要指定精度和小数位数。

★ 说明 ★

（4）NULL值与DEFAULT值：

NULL值表示设置该字段值是否运行为空，即不赋任何值。而DEFAULT值表示某一字段的默认值，当没有输入数据时，则使用此默认的值。

本节设计的实例表为图书馆图书信息表（BookInfo），其所在的数据库为Library，表的设计如表3.15所示。

表3.15 实例表BookInfo的结构设计

3.4.2 创建基本表——CREATE TABLE

与SQL标准语法相同，在Transact-SQL中，创建一张新表可以使用CREATE TABLE命令。在SQL Server 2005中，CREATE TABLE语句的完整语法非常复杂，可选选项很多，这里只简单介绍其最基本的创建语法，如下所示。

        CREATE TABLE table_name
        (
        column_name1 data_type,
        column_name2 data_type,
        )

在CREATE TABLE命令后，指明创建的数据库表的名称，接着要分别定义表中各列的名称、数据类型等。表中各列的定义在括号中完成，且各列之间以逗号隔开。

实例11 创建简单的BookInfo表

本实例将采用最基本的创建语法创建BookInfo表，代码如下：

        USE Library                                    --指定表所在的数据库
        CREATE TABLE BookInfo
        (
        bookid  int,
        bookname  char,
        publish  varchar,
        pubdate  datetime,
        price  decimal,
        author char,
        store bit,
        reader int,
        remarks text
        )

在Management Studio的查询分析器中执行该代码，即实现创建基本的BookInfo表。此时在Management Studio的对象资源管理器中的Library数据库的表节点下，可以发现新创建的BookInfo表，如图3.12所示。

而如果要查看BookInfo表的结构，则需要在图3.12中，右键单击BookInfo表节点，在弹出菜单中执行“修改”命令，即可查看BookInfo表的结构，如图3.13所示。

可见，系统自动为数据类型指定了默认的长度和精度，另外，默认均允许为空。下面修改创建代码，指定数据的长度和精度。

图3.12 在Management Studio的对象资源管理器中查看BookInfo表

图3.13 在Management Studio中查看BookInfo表的结构

实例12 创建BookInfo表，指定数据的长度和精度

相比实例11，本实例创建BookInfo表，并指定数据的长度和精度。代码如下：

        USE Library
        CREATE TABLE BookInfo
        (
        bookid  int,
        bookname  char(40),               --定义数据的长度
        publish  varchar(60),
        pubdate  datetime,
        price  decimal(7,2),              --指定数据的长度和精度
        author char(40),
        store bit,
        reader int,
        remarks text
        )

此时，创建的BookInfo表结构如图3.14所示。

图3.14 在Management Studio中查看BookInfo表的结构

★ 说明 ★

3.4.3 指定值非空——NOT NULL

SQL允许表中列的值为NULL，即空值。如果表中某列的值为NULL，则表明该列不存在值。使用CREATE TABLE命令创建数据库表时，可以通过在列的数据类型后，使用NOT NULL关键字为列指定非空约束，这样如果在添加数据时，如果该列的值为NULL，则数据库就会报错，添加失败。

非空约束在实际应用中是十分有用的，它保证了信息的完整性，避免了由于用户的误操作而导致数据的不完整。例如，对表BookInfo而言，其图书书号、图书名称和借出标志三个字段是不能为空的。

实例13 创建BookInfo表，指定数据的长度和精度

相比实例12，本实例创建BookInfo表，并为bookid、bookname和store三个字段添加非空约束。代码如下：

        USE Library
        CREATE TABLE BookInfo
        (
        bookid  int NOT NULL,             --添加非空约束
        bookname  char(40) NOT NULL,
        publish  varchar(60),
        pubdate  datetime,
        price  decimal(7,2),
        author char(40),
        store bit NOT NULL,
        reader int,
        remarks text
        )

添加了非空（NOT NULL）约束的BookInfo表结构，如图3.15所示。

图3.15 在Management Studio中查看BookInfo表的结构

此时，表中的bookid、bookname和store列的值是不允许为空的。如果插入的值为NULL，则数据库会报错，插入失败。在创建表时，各列默认设置是允许为NULL的，除非为其指明了NOT NULL属性。

★ 注意 ★

3.4.4 指定列的默认值——DEFAULT

使用CREATE TABLE命令创建表时，可以为每列指定默认值。即当向表中插入数据，且不指定该列的值时，系统会自动地采用设定的默认值。

指定默认值是通过DEFAULT关键字来实现的，其语法如下：

        ＜column name＞  ＜data type＞  DEFAULT  ＜default value＞

在DEFAULT关键字后面指定该列的默认值，无论＜default value＞使用什么类型的值，都必须符合在列定义中指定的数据要求。比如，如果使用char数据类型，那么指定的默认值也必须是char数据类型的。

实例14 创建BookInfo表，指定默认值

相比实例13，本实例创建BookInfo表，并为pubdate、price和store三个字段指定默认值。代码如下：

        USE Library
        CREATE TABLE BookInfo
        (
        bookid  int NOT NULL,
        bookname  char(40) NOT NULL,
        publish  varchar(60),
        pubdate  datetime DEFAULT GETDATE(),       --为表列定义默认值
        price  decimal(7,2) DEFAULT 0.0,
        author char(40),
        store bit DEFAULT 0 NOT NULL,
        reader int,
        remarks text
        )

当向表BookInfo中添加记录时，如果不指明pubdate、price和store值，则数据库会为其添加默认设置的值。如下面的添加数据代码：

        USE Library
        INSERT INTO BookInfo (bookid, bookname, publish)
        VALUES (40001,'计算机文化基础','清华大学出版社')

上面代码并没有给store字段插入数据，而store字段设置有非空约束，但是由于在创建表时，为store字段指定了默认值，所以数据库系统并没有报错，插入操作成功完成。

此时查看BookInfo表中的数据：

        SELECT ＊ FROM BookInfo

结果如图3.16所示。

图3.16 查询BookInfo表中的数据

可见，系统自动为pubdate、price和store字段填充了创建表时指定的默认值。

★ 说明 ★

至此，实例表BookInfo的创建基本完成。所缺少的就是主键和约束的定义，有关其概念和性质将在本书的第10章表的约束、索引与视图中介绍。

3.5 编辑表结构（ALTER TABLE）

由于应用环境和应用需求的变化，经常需要修改基本表的结构，比如，增加新列和完整性约束，修改原有的列定义和完整性约束等。在Transact-SQL语言中，可以采用ALTER TABLE命令来修改已经创建的表结构。通过ALTER TABLE命令可以向表中增加新列（ADD）、删除已有的列（DROP），也可以修改已经创建的列（ALTER COLUMN）。

3.5.1 添加列或约束——ADD

在ALTER TABLE语句中，使用ADD关键字可以增加新列和完整性约束，定义方式同CREATE TABLE语句中的定义方式相同，其语法格式如下所示。

        ALTER TABLE ＜表名＞ ADD ＜列定义＞ | ＜完整性约束定义＞

ADD关键字后面可以是要创建列的列名、数据类型等，当然也可以对列设置非空约束和默认值。

实例15 修改BookInfo表，添加新列

本实例向前面所创建的BookInfo表中，添加一列lenddate（借阅时间），其数据类型为datetime。代码如下：

        USE Library
        ALTER TABLE BookInfo
        ADD lenddate datetime                  --添加新列

运行代码后，BookInfo表中包含的列如图3.17所示。

图3.17 BookInfo表中包含的列

当用ALTER TABLE语句向表中添加新列时，DBMS将向表的列定义的尾部添加列，即在查询中将位于表的最右边。

除非指定默认值，否则DBMS将已有行上的新列设置为NULL值。因此，当使用ALTER TABLE语句向表中添加新列时，不能简单地添加NOT NULL约束，还必须提供默认值。因为如果没有提供默认值，DBMS假设已有行上的新列为NULL值，这就和NOT NULL约束相抵触。当然，如果表中不存在数据，则不存在这个问题。

★ 说明 ★

3.5.2 修改列的定义——ALTER COLUMN

与许多DBMS产品不同，SQL Server不仅允许改变列的数据长度，而且还允许改变其数据类型。在SQL Server中，可以在ALTER TABLE语句中，使用ALTER COLUMN关键字修改表中列的定义。其语法格式如下：

            ALTER TABLE＜表名＞
                ALTER COLUMN ＜列名＞＜数据类型＞[NULL|NOT NULL]

实例16 修改BookInfo表中列的定义

本实例将BookInfo表中的publish字段的最大长度更改为80，并为其添加非空约束，代码如下：

        USE Library
        ALTER TABLE BookInfo
        ALTER COLUMN publish  varchar(80) NOT NULL      --修改列

运行代码后，BookInfo表中的publish列就已经修改了，如图3.18所示。

图3.18 修改后的BookInfo表中的publish列

注意，在SQL Server 2005中，使用ALTER COLUMN关键字修改表中列的结构有如下一些限制：

· 不能改变列的列名。

· 不能将含有空值的列的定义修改为NOT NULL约束。

· 若列中已有数据，则减少该列的宽度，不能比已有的数据还短。

· 只能修改NULL|NOT NULL约束，其他类型的约束在修改之前必须先删除，然后再重新添加修改过的约束定义。

当改变列的数据类型时，列中已有的数据必须与新的数据类型兼容。比较常用的转换是从integer转换为字符型，因为char列可保存数字、字母和特殊符号。但是，如果要将char类型转换为integer类型，必须保证表的每行上所转换的char字段具有数字或NULL值。

3.5.3 删除列——DROP COLUMN

如果表中某列的信息已经无效或不再需要时，为了节省数据库空间，提高查询性能，可以采用DROP COLUMN关键字将其删除。语法如下：

        ALTER TABLE table_name
        DROP COLUMN  column_name

table_name指的是要修改的表的名字，DROP COLUMN关键字后面为要删除列的名字。一次可以删除多个列，只需要在DROP COLUMN关键字后面依次列出要删除的列的名字，中间用逗号分开即可。

实例17 删除BookInfo表中的某列

本实例将BookInfo表中新添加的lenddate字段删除，代码如下：

        USE Library
        ALTER TABLE BookInfo
        DROP COLUMN lenddate               --删除列

运行代码后，BookInfo表中的lenddate列就被删除了，如图3.19所示。

图3.19 删除了lenddate列后的BookInfo表

当列被删除后，列中的数据也就同时被删除了。在SQL Server中，下面的几种列是不能被删除的：

· 用于索引的列。

· 用于CHECK、FOREIGN KEY、UNIQUE或PRIMARY KEY约束的列。

· 与默认值（由DEFAULT关键字定义）相关联的列，或绑定到默认对象的列。

· 绑定到规则的列。

而具有非空约束的列是可以被删除的。

如果要删除具有约束的列，可以先删除约束，而后再删除该列。而删除约束，可以采用如下的语句：

        ALTER TABLE table_name
        DROP CONSTRAINT  constraint_name

其具体使用参见第10章表的约束、索引与视图。

★ 注意 ★

3.6 重命名、删除表

在Transact-SQL中，可以使用sp_rename系统存储过程改变表的名称，而使用DROP TABLE语句删除整个表。

3.6.1 改变表的名字——sp_rename

在创建表的时候，表的名字就被确定了，但在实际应用中，有时候需要修改表的名字而不改变其他信息。这时使用sp_rename系统存储过程，可以改变基本表的名字。此外，使用sp_rename还可以改变表的索引、列的名字。其语法格式为：

        sp_rename 'object_name', 'new_name'

对以上代码中的内容作如下解释：

· object_name为要重命名的对象。如果要重命名的对象是表中的列，则object_name的格式必须是table.column；如果要重命名的对象是索引，则object_name的格式必须是table.index。

· new_name指定对象的新名称，并且必须遵循标识符的规则。

实例18 重命名数据库表和其中的列

本实例将BookInfo表重命名为Books，并将其中的author列的名称更改为writer，代码如下：

        USE Library
        GO
        sp_rename 'BookInfo','Books'           --重命名表
        GO
        sp_rename 'Books.author','writer'      --重命名列
        GO

运行代码后，BookInfo表以及其中的author列都被重命名了，如图3.20所示。

图3.20 重命名BookInfo表及author列

3.6.2 删除基本表——DROP TABLE

当某个基本表无用时，可使用DROP TABLE命令将其删除。删除后，该表中的数据和在此表上所建的索引都被删除，而建立在该表上的视图不会随之删除，系统将继续保留其定义，但已无法使用。如果恢复该表，这些视图可重新使用。

删除表的语法如下：

        DROP TABLE ＜表名＞

比如要删除BookInfo表，可采用如下的代码：

        DROP TABLE BookInfo

不能使用DROP TABLE删除被FOREIGN KEY约束引用的表。必须先删除引用FOREIGN KEY约束或引用表。在系统表上不能使用DROP TABLE语句。

★ 说明 ★

3.7 小结

在本章，主要介绍了使用Transact-SQL语句对数据库和表进行创建、修改和删除操作，这是本章的重点。另外，本章还详细介绍了SQL Server 2005中的数据类型，这些数据类型在Transact-SQL中均可以使用。