SQL是结构化查询语言（Structured Query Language）的缩写，尽管它被称为查询语言，但其功能包括数据查询、数据定义、数据操纵和数据控制四部分。SQL简洁方便、功能齐全，是目前应用最广的关系数据库语言。本章主要介绍SQL的使用和SQL Server 2008 R2数据库管理系统的主要功能。通过本章的学习，读者应了解SQL的特点，掌握SQL的四大功能及使用方法，重点掌握数据查询功能。结合SQL Server 2008 R2，加深对数据库管理系统在数据查询、数据定义、数据操纵和数据控制功能实现原理的理解，掌握利用SQL Server 2008 R2进行数据库应用程序设计的方法。

3.1 SQL的基本概念与特点

3.1.1 SQL的发展及标准化

1．SQL的发展

SQL是当前最成功、应用最广的关系数据库语言，其发展主要经历了以下几个阶段：

（1）1974年，由Chamberlin和Boyce提出，当时称为SEQUEL（Structured English Query Language）；
（2）1976年，IBM公司对SEQUEL进行了修改，将其用于System R关系数据库系统中；
（3）1981年，IBM推出了商用关系数据库SQL/DS。由于SQL功能强大，简洁易用，得到了广泛使用；
（4）今天，SQL广泛应用于各种大、中型数据库，如Sybase、Informix、SQL Server、Oracle、DB2、MySQL等；也用于各种小型数据库，如FoxPro、Access等。

2．SQL标准化

随着关系数据库系统和SQL应用的日益广泛，SQL的标准化工作也在紧张地进行着，三十多年来已制订了多个SQL标准。

（1）1982年，美国国家标准化协会（American National Standard Institute，ANSI）开始制订SQL标准；
（2）1986年，ANSI公布了SQL的第一个标准SQL-86；
（3）1987年，国际标准化组织（International Organization for Standardization，ISO）正式采纳了SQL-86标准为国际标准；
（4）1989年，ISO对SQL-86标准进行了补充，推出了SQL-89标准；
（5）1992年，ISO推出了SQL-92标准（也称SQL2）；
（6）1999年，ISO推出了SQL-99标准（也称SQL3），它增加了对象数据、递归和触发器等的支持功能；
（7）2003年，ISO推出了ISO/IEC 9075: 2003标准（也称SQL4）。

3.1.2 SQL的基本概念

1．基本表（Base Table）

一个关系对应一个基本表。基本表是独立存在的表，不是由其他表导出的。一个或多个基本表对应一个存储文件。

2．视图（View）

视图是从一个或几个基本表导出的表，是一个虚表。数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。当基本表中的数据发生变化时，从视图查询出来的数据也随之改变。

例如，设教学数据库中有一个学生基本情况表S（SNo，SN，Sex，Age，Dept），此表为基本表，对应一个存储文件。可以在其基础上定义一个男生基本情况表S_Male（SNo，SN，Age，Dept），它是从S中选择Sex='男'的各个行，然后在SNo、SN、Age、Dept上投影得到的。在数据库中只存储S_Male的定义，而S_Male的记录不重复存储。

在用户看来，视图是通过不同路径去看一个实际表，就像一个窗口一样，通过窗口去看外面的高楼，可以看到高楼的不同部分，而透过视图可以看到数据库中用户所感兴趣的内容。

SQL支持数据库的三级模式结构，如图3-1所示。其中，外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。

3.1.3 SQL的主要特点

SQL之所以能够成为标准并被业界和用户接受，是因为它具有简单、易学、综合、一体等鲜明的特点，主要有以下几个方面：

（1）SQL是类似于英语的自然语言，语法简单，且只有为数不多的几条命令。简洁易用。
（2）SQL是一种一体化的语言，它包括数据定义、数据查询、数据操纵和数据控制等方面的功能，可以完成数据库活动中的全部工作。
（3）SQL是一种非过程化的语言，用户不需要关心具体的操作过程，也不必了解数据的存取路径，即用户不需要一步步地告诉计算机“如何”去做，而只需要描述清楚“做什么”，SQL语言就可将要求交给系统，系统自动完成全部工作。
（4）SQL是一种面向集合的语言，每个命令的操作对象是一个或多个关系，结果也是一个关系。
（5）SQL既是自含式语言，又是嵌入式语言。自含式语言可以独立使用交互命令，适用于终端用户、应用程序员和DBA；嵌入式语言使其嵌入在高级语言中使用，供应用程序员开发应用程序。
（6）SQL具有数据查询（Query）、数据定义（Definition）、数据操纵（Manipulation）和数据控制（Control）四种功能。

本章各例题均采用第1章图1-22所示的基本表，后文不再赘述。

3.2 SQL Server 2008 R2简介

SQL Server是一个支持关系模型的关系数据库管理系统，是Microsoft公司的产品。最初是由Microsoft、Sybase和Ashton-Tate三家公司联合开发，于1988年推出了第一个OS/2版本。后来，Ashton-Tate公司退出了SQL Server的开发。在Windows NT操作系统推出后，Sybase与Microsoft在SQL Server的开发上就分道扬镳了。其中，Sybase专注于SQL Server在UNIX操作系统上的应用；Microsoft则将SQL Server移植到Windows NT操作系统上，专注于开发Windows NT版本的SQL Server。若无特殊说明，本书所指的SQL Server专指Microsoft公司的SQL Server。

3.2.1 SQL Server 的发展与版本

Microsoft SQL Server目前已历经多个版本的发展演化。Microsoft公司于1995年发布SQL Server 6.0版本；1996年发布SQL Server 6.5版本；1998年发布SQL Server 7.0版本，在数据存储和数据引擎方面做了根本性的变化，确立了SQL Server在数据库管理工具中的主导地位；2000年发布的SQL Server 2000，在数据库性能、可靠性、易用性方面做了重大改进；2005年发布的SQL Server 2005，可为各类用户提供完善的数据库解决方案。最新的SQL Server 2008 R2安全性更强、延展性更好、管理能力更高，是一个全方位的数据管理平台。

SQL Server 2008 R2不仅继承了早期版本的优点，同时增加了许多新的功能，具有高安全性、高可靠性、高效智能等优点。SQL Server 2008 R2包括以下几个常见版本。

（1）企业版（Enterprise Edition）：一个全面的数据管理与商业智能平台，为关键业务应用提供企业级的可扩展性、数据仓库、安全、高效分析和报表支持。可作为大型Web站点、企业OLTP（联机事务处理）以及数据仓库系统等的数据库服务器。
（2）标准版（Standard Edition）：提供完整的数据管理和商业智能平台，提供了最佳的易用性和可管理性，适用于部门级等中小规模的应用。
（3）Web版（Web Edition）：面向Internet Web服务环境设计。这一版本为实现低成本、大规模、高可用性的Web应用或客户托管解决方案提供了必要的支撑工具。
（4）开发者版（Developer Edition）：允许开发人员构建和测试基于SQL Server的任意类型应用。这一版本拥有企业版的特性，但只限于在开发、测试和演示中使用。基于这一版本开发的应用和数据库可以很容易地升级到企业版。 除上述主流版本外，SQL Server 2008 R2还有工作组版（Workgroup Edition）、速成版（Express Edition）和移动版（Compact Edition），用户可根据实际情况选择相应的SQL Server版本。本书以Microsoft SQL Server 2008 R2标准版（为叙述简洁，后文简称SQL Server 2008 R2）为例，进行有关内容的讲解。

3.2.2 SQL Server 2008 R2的主要组件

SQL Server 2008 R2提供了完善的管理工具套件，主要包括以下几部分。

1．SQL Server数据库引擎

SQL Server数据库引擎包括用于存储、处理和保护数据的核心引擎，复制，全文搜索以及用于管理关系数据和XML数据的工具。

2．SQL Server Management Studio

SQL Server Management Studio（后文简称Management Studio）是一个集成环境，用于配置和管理SQL Server的主要组件。Management Studio提供了直观易用的图形工具和强大的脚本环境，使各种技术水平的开发人员和管理人员都能访问SQL Server。

3．分析服务

分析服务（Analysis Services）包括用于创建和管理联机分析处理（OLAP）以及数据挖掘应用的工具。

4．报表服务

报表服务（Reporting Services）是一个开发报表应用程序的可扩展平台，用于创建、管理和部署表格报表、矩阵报表、图形报表以及自由格式报表等应用。

5．集成服务

集成服务（Integration Services）是一组图形工具和可编程对象，用于移动、复制和转换数据。

6．配置管理器

SQL Server配置管理器（Configuration Manager）为SQL Server服务、服务器协议、客户端协议和客户端别名提供配置管理。

7．数据库引擎优化顾问

数据库引擎优化顾问可协助创建索引、索引视图和分区的最佳组合，提升数据库的访问性能。

8．商业智能开发向导

商业智能开发向导（Business Intelligence Development Studio）是一个集成开发环境（IDE），集成了上述分析服务、报表服务和集成服务的功能。

9．连接组件

安装客户端和服务器通信的组件，以及用于DB-Library、ODBC和OLE DB的网络库。

10．联机丛书

SQL Server 2008 R2提供了大量的联机文档，用户可以查询到许多有价值的信息。一个优秀的SQL Server管理员和应用程序员，应能熟练使用联机文档。

3.2.3 Management Studio

Management Studio将早期SQL Server版本中包含的企业管理器、查询分析器和分析管理器等工具的功能整合到单一环境中，可以和报表服务、集成服务等组件协同工作。开发人员可以获得熟悉的体验，而数据库管理员可获得功能齐全的单一实用工具，其中包含易于使用的图形工具和丰富的脚本撰写功能。

用户可以通过执行“开始→程序→Microsoft SQL Server R2→SQL Server Management Studio”命令，启动Management Studio，如图3-2所示。

3.3 创建与使用数据库

3.3.1 数据库的结构

对于数据库，从逻辑上看，描述信息的数据存在数据库中并由DBMS统一管理；从物理上看，描述信息的数据以是以文件的方式存储在物理磁盘上，由操作系统进行统一管理。

数据库的存储结构是指数据库文件在磁盘上如何存储。在SQL Server 2008 R2中，创建一个数据库时，SQL Server会对应地在物理磁盘上创建相应的操作系统文件，数据库中的所有数据、对象和数据库操作日志都存储在这些文件中，其中，将至少产生两个文件：数据文件和事务日志文件。一个数据库至少应包含一个数据文件和一个事务日志文件。

一个数据库的所有物理文件，在逻辑上通过数据库名联系在一起。也就是说一个数据库在逻辑上对应一个数据库名，在物理存储上会对应若干个存储文件。

1．数据文件

数据文件（Database File）是存放数据库数据和数据库对象的文件。一个数据库可以有一个或多个数据文件，一个数据文件只属于一个数据库。当有多个数据文件时，有一个文件被定义为主数据文件（Primary Database File），扩展名为.mdf，用来存储数据库的启动信息和部分或全部数据。一个数据库只能有一个主数据文件，其他数据文件被称为次数据文件（Secondary Database File），扩展名为.ndf，用来存储主要数据文件未存储的其他数据。采用多个数据文件来存储数据的优点体现如下：

（1）数据文件可以不断扩充，不受操作系统文件大小的限制；
（2）可以将数据文件存储在不同的硬盘中，这样可以同时对几个硬盘并行存取，提高了数据的处理性能。

2．事务日志文件

事务日志文件（Transaction Log File）用来记录数据库的更新情况，扩展名为.ldf。使用INSERT、UPDATE和DELETE修改数据库时，这些操作都会记录在日志文件中。一个数据库可以有一个或多个事务日志文件。

SQL Server 2008 R2中采用“Write-Ahead（提前写）”方式的事务。其具体操作是：先将更改操作写入事务日志中，再更改存储在计算机缓存中的数据，为了提高执行效率，此更改不会立即写到硬盘中的数据库，而是由系统以固定的时间间隔执行CHECKPOINT命令，将更改过的数据批量写入硬盘。SQL Server 2008 R2在执行数据更改时会设置一个开始点和一个结束点，如果尚未到达结束点就因某种原因导致操作中断，在SQL Server 2008 R2重新启动时会自动还原已修改的数据，使其回退到未被修改的状态。由此可见，当数据库破坏时，可以用事务日志还原数据库内容。

3．文件组

文件组（File Group）是将多个数据文件集合起来形成的一个整体，每个文件组有一个组名。与数据文件一样，文件组也分为主要文件组和次要文件组。一个数据文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用。当建立数据库时，主要文件组包括了主要数据文件和未指定组的其他文件。在次要文件组中可以指定一个缺省文件组，在创建数据库对象时，如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中；如果没有指定缺省文件组，则主要文件组为缺省文件组。日志文件不分组，它不属于任何文件组。

3.3.2 SQL Server的系统数据库

SQL Server 2008 R2安装完毕后，有5个系统数据库：Master、Model、Msdb、Tempdb和Resource。这些系统数据库存储在SQL Server 2008 R2默认安装目录下的MSSQL子目录中的Data文件夹里。

1．Master数据库

Master数据库是SQL Server中最重要的数据库，存储了SQL Server的所有系统级信息。包括实例范围的元数据（例如登录帐户）、端点、链接服务器和系统配置设置。此外，Master 数据库还记录了所有其他数据库的信息、数据库文件的位置以及SQL Server的初始化信息。如果Master数据库不可用，SQL Server将无法启动。在SQL Server 2008 R2中，系统对象不再存储在Master 数据库中，而是存储在Resource数据库中。

考虑到Master数据库的重要性，应确保总是具有Master数据库的最新备份。在创建、修改、删除数据库，或者修改了数据库服务器的配置参数，或者添加、修改、删除了登录帐号时，都需要重新备份Master数据库。

2．Model数据库

Model数据库是一个模板数据库，它存储了所有用户数据库和Tempdb数据库的创建模板。当创建用户数据库时，系统会将Model数据库中的内容复制到新建的数据库中。利用Model数据库的模板特性，通过更改Model数据库的设置，并将经常使用的数据库对象复制到Model数据库中，可以大大简化数据库及其对象的创建及设置工作。通常可将以下内容添加到Model数据库中：①数据库的最小容量；②数据库选项设置；③经常使用的数据库对象，如用户自定义的数据类型、函数、规则和缺省值等。

3．Msdb数据库

Msdb数据库是代理服务数据库，由SQL Server Agent用于计划警报和作业，也可以由Service Broker和数据库邮件等其他功能使用。

4．Tempdb数据库

Tempdb数据库用作系统的临时存储，主要用于保存以下内容。

（1）显式创建的临时用户对象，如临时表、临时存储过程、表变量或游标。
（2）数据库引擎创建的内部对象，如用于存储假脱机或排序中间结果的工作表。

每次重新启动SQL Server时，SQL Server都会重新创建Tempdb，从而获得一个干净的数据库副本。Tempdb数据库采用最小日志策略，在该数据库中的表上进行数据操作，比在其他数据库中要快得多。

5．Resource数据库

Resource数据库是一个只读数据库，包含了SQL Server中的全部系统对象，但不包含任何用户数据。SQL Server系统对象（如sys.objects），物理存储于Resource数据库中，但逻辑上出现于每个数据库实例中。

Resource数据库为迁移到更高版本的SQL Server版本提供了便利，这是SQL Server 2008 R2的重要特性。SQL Server 2000等早期版本不包含Resource数据库，进行SQL Server的版本迁移时，要先删除所有的系统对象，然后再重建这些对象，这带来了较高的迁移开销。借助于最新的SQL Server 2008 R2中的Resource数据库，由于其包含了所有的系统对象，升级到新的SQL Server版本时，只需要将Resource数据库文件拷贝部署到新计算机即可。

3.3.3 SQL Server的示例数据库

SQL Server 2008 R2提供了AdventureWorks示例数据库。与SQL Server 2000等早期版本不同，SQL Server 2008 R2默认并不安装示例数据库，需要手工下载安装，下载地址为：http://go.microsoft.com/fwlink/?LinkId=87843。SQL Server 2008 R2联机丛书基本都以该数据库为例讲解，建议读者手工下载安装该示例数据库。 3.3.4 创建用户数据库 创建用户数据库有两种典型方法：一是通过Management Studio创建；二是通过SQL语句创建。

1．用Management Studio 创建数据库

在SQL Server 2008 R2的Management Studio中，按下列步骤创建用户数据库。
（1）单击Management Studio主菜单栏的“查看→对象资源管理器”，打开“对象资源管理器”窗口。右键单击其中的“数据库”节点，在弹出的菜单中选择“新建数据库(N)…”命令（见图3-3），即可打开新建数据库窗口（见图3-4）。

（2）图3-4 中，在“常规”选项卡的“数据库名称”文本框中输入数据库的名称。在“数据库文件”列表中，指定数据库文件的名称、存储位置、初始容量大小和所属文件组等信息，并进行数据库文件大小、扩充方式和容量限制的设置。
（3）单击“确定”按钮，则创建一个新数据库。

2．用SQL命令创建数据库

用CREATE DATABASE也可以创建数据库。CREATE DATABASE命令的语法格式如下：

CREATE DATABASE database_name

[ ON

{ [ PRIMARY ] [ [ ,...n ]

[ , [ ,...n ] ]

[ LOG ON { [ ,...n ] } ] } ]

[ COLLATE collation_name ]

[ WITH ]] [;]

CREATE DATABASE database_name

ON [ ,...n ]

FOR { ATTACH [ WITH ] | ATTACH_REBUILD_LOG }[;]

::= {(

NAME =logical_file_name,

FILENAME = { 'os_file_name' | 'filestream_path' }

[ , SIZE =size [ KB | MB | GB | TB ] ]

[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]

[ , FILEGROWTH =growth_increment [ KB | MB | GB | TB | % ] ]) [ ,...n ]}

::= {

FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ]

[ ,...n ] }

::= {

[ DB_CHAINING { ON | OFF } ]

[ , TRUSTWORTHY { ON | OFF } ] }

::= {

ENABLE_BROKER | NEW_BROKER | ERROR_BROKER_CONVERSATIONS }

各主要参数的说明如下。

（1）database_name：新建数据库的名称，其在服务器中必须唯一，并且符合标识符的命名规则。数据库名称最长为128个字符，不区分大小写。一个服务器理论上可以管理32767个数据库。
（2）ON：指定数据文件的存放位置。
（3）PRIMARY：指定主数据库文件或主文件组。如果没有指定PRIMARY，CREATE DATABASE语句中列出的第一个文件将成为主文件。
（4）LOG ON：指定日志文件的存放位置。如果没有指定LOG ON，将自动创建一个日志文件，其大小为该数据库所有数据文件大小总和的25%或512 KB，取两者之中的较大者。
（5）COLLATE collation_name：指定排序规则。collation_name可以是Windows排序规则名称，也可以是SQL排序规则名称。如果没有指定排序规则，则使用当前SQL Server的默认排序规则。
（6）FOR ATTACH：将已存在的数据库文件附加到新数据库中。
（7）NAME logical_file_name：指定逻辑名称。如果指定了FOR ATTACH，则不需要指定NAME参数。
（8）FILENAME：指定在操作系统中存储的路径名和文件名。
（9）SIZE：指定数据库的初始容量大小，单位可以使用千字节（KB）、兆字节（MB）、千兆字节（GB）或兆兆字节（TB），省略时默认为兆字节（MB）。
（10）MAXSIZE：指定数据库文件可以增长到的最大大小。
（11）UNLIMITED：指定文件将增长到磁盘充满。SQL Server中，设定为UNLIMITED的日志文件的最大大小为2 TB，设定为UNLIMITED的数据文件的最大大小为16 TB。
（12）FILEGROWTH：指定每次增容时增加的容量大小，该设置不能超过MAXSIZE设定的值。
（13）FILEGROUP：指定文件组名称，在数据库中必须唯一。

[例3-1] 用SQL命令创建一个教学数据库Teach，数据文件的逻辑名称为Teach_Data，数据文件存放在D盘根目录下，文件名为TeachData.mdf，数据文件的初始存储空间大小为10MB，最大存储空间为50MB，存储空间自动增长量为5MB；日志文件的逻辑名称为Teach_Log，日志文件物理地存放在D盘根目录下，文件名为TeachData.ldf，初始存储空间大小为5MB，最大存储空间为25MB，存储空间自动增长量为5MB。

CREATE DATABASE Teach

ON

( NAME=Teach_Data,

FILENAME='D:\TeachData.mdf',

SIZE=10,

MAXSIZE=50,

FILEGROWTH=5)

LOG ON

( NAME=Teach_Log,

FILENAME='D:\TeachData.ldf',

SIZE=5,

MAXSIZE=25,

FILEGROWTH=5)

3.3.5 修改用户数据库

创建数据库后，还可以对数据库的名称、大小和属性等进行修改。

1．用Management Studio修改数据库

打开“对象资源管理器”，右键单击要修改的数据库，从弹出菜单中选择“属性”命令，即可数据库属性对话框，如图3-5所示。

（1）“常规”选项卡中包含数据库的状态、所有者、创建日期、大小、可用空间、用户数、备份和维护等信息。
（2）“文件”选项卡中包含数据文件和日志文件的名称、存储位置、初始容量大小、文件增长和文件最大限制等信息。
（3）“文件组”选项卡中可以添加或删除文件组。但是，如果文件组中有文件则不能删除，必须先将文件移出文件组，才能删除文件组。
（4）“选项”选项卡中可以设置数据库的许多属性，如排序规则、恢复模式、兼容级别等，如图3-6所示。
（5）“更改跟踪”选项卡可以设定是否对数据库的修改进行跟踪。
（6）“权限”选项卡可以设定用户或角色对此数据库的操作权限。
（7）“扩展属性”选项卡可以设定表或列的扩展属性。在设计表或列时，通常通过表名或列名来表达含义，当表名或列名无法表达含义时，就需要使用扩展属性。
（8）“镜像”选项卡可以设定是否对数据库启用镜像备份。镜像备份是一种高性能的备份方案，但需要投入一定的设备成本，一般用于高可靠性环境。
（9）“事务日志传送”选项卡设定是否启用事务日志传送。事务日志传送备份是仅次于镜像的高可靠性备份方案，可以达到分钟级的灾难恢复能力，实施成本远小于镜像备份，是一种经济实用的备份方案。

2．用SQL命令修改数据库

ALTER DATABASE命令可以在数据库中添加或删除文件和文件组、更改数据库属性或其文件和文件组、更改数据库排序规则和设置数据库选项。应注意的是，只有数据库管理员（DBA）或具有CREATE DATABASE权限的数据库所有者才有权执行此命令。

ALTER DATABASE命令的语法规则较为复杂，为便于讲解，按文件和文件组选项、SET选项、数据库镜像和兼容级别四部分分别讨论。

（1）ALTER DATABASE文件和文件组选项
用于添加或删除文件和文件组、更改数据库或其文件和文件组的属性。语法规则如下：

ALTER DATABASE database_name
{
< add_or_modify_files>
| < add_or_modify_filegroups>
}
[;]
< add_or_modify_files>::=
{
ADD FILE < filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name } ]
| ADD LOG FILE < filespec> [ ,...n ]
| REMOVE FILE logical_file_name
| MODIFY FILE < filespec>
}
< filespec>::=
(
NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = {'os_file_name' | 'filestream_path' } ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
< add_or_modify_filegroups>::=
{
| ADD FILEGROUP filegroup_name
[ CONTAINS FILESTREAM ]
| REMOVE FILEGROUP filegroup_name
| MODIFY FILEGROUP filegroup_name
{ < filegroup_updatability_option>
| DEFAULT
| NAME = new_filegroup_name
}
}
< filegroup_updatability_option>::=
{
{ READONLY | READWRITE }
| { READ_ONLY | READ_WRITE }
}

各主要参数说明如下。

● database_name：要修改的数据库名称。
● ADD FILE：向数据库中添加数据文件。
● ADD LOG FILE：向数据库中添加日志文件。
● REMOVE FILE：从数据库中删除逻辑文件，并删除物理文件。如果文件不为空，则无法删除。
● MODIFY FILE：指定要修改的文件。
● NAME：指定数据库的原逻辑文件名称。
● NEWNAME：指定数据库的新逻辑文件名称。
● FILENAME、SIZE、MAXSIZE、FILEGROWTH：与CREATE DATABASE中的同名参数含义相同，不再赘述。
● OFFLINE：将文件设置为脱机，并使文件组中的所有对象都不可访问。仅当文件已损坏但可以还原时，才能使用该选项。设置为OFFLINE的文件，只能通过从备份中还原该文件，才能将其设置为联机。
● ADD FILEGROUP：向数据库中添加文件组。
● CONTAINS FILESTREAM：指定文件组在文件系统中存储FILESTREAM二进制大型对象（BLOB）。
● REMOVE FILEGROUP：从数据库中删除文件组。若文件组非空，无法将其删除，需要先从文件组中删除所有文件。
● MODIFY FILEGROUP：修改文件组名称以及文件组的只读、读写等属性。

[例3-2] 修改Northwind数据库中的Northwind文件增容方式为一次增加2MB。
ALTER DATABASE Northwind
MODIFY FILE
( NAME = Northwind,
FILEGROWTH = 2mb )

（2）ALTER DATABASE SET选项
用于修改数据库选项。通过该命令，可以对是否自动释放资源、是否定期收缩、是否加密数据库等选项进行设置。语法规则如下：

ALTER DATABASE database_name
SET
{
{ [ ,...n ] [ WITH ] }
}

（3）ALTER DATABASE数据库镜像
修改数据库的镜像选项。命令语法如下：

ALTER DATABASE database_name

SET { < partner_option> | < witness_option> }

（4）ALTER DATABASE兼容级别
设置数据库的兼容级别。命令语法如下：

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

各参数说明如下。

● database_name：要修改的数据库名称。
● COMPATIBILITY_LEVEL { 80 | 90 | 100 }。设定与数据库兼容的SQL Server 版本，必须为下列值之一：

80，SQL Server 2000；
90，SQL Server 2005；
100，SQL Server 2008。

除非Model数据库中定义了更低的兼容级别，否则SQL Server 2008 R2会将新建数据库的默认兼容级别设为100。将数据库从早期版本的SQL Server升级到SQL Server 2008 R2时，如果数据库的兼容级别等于或高于80，将保留其现有的兼容级别。若兼容级别低于80，会将兼容级别设置为80。

3.3.6 删除用户数据库

1．用Management Studio删除数据库

打开“对象资源管理器”，右键单击要删除的数据库，从弹出菜单中选择“删除”。删除数据库后，与此数据库关联的数据文件和日志文件都会被删除，系统数据库中存储的该数据库的所有信息也会被删除，因此务必要慎重！

2．用SQL命令删除数据库

DROP DATABASE命令可以从SQL Server中删除数据库，可以一次删除一个或多个数据库。数据库所有者（DBO）和数据库管理员（DBA）才有权执行此命令。其语法如下：

DROP DATABASE database_name [,...n]

[例3-3] 删除数据库Teach。

DROP DATABASE Teach

3.3.7 查看数据库信息

1．用Management Studio查看数据库信息

SQL Server 2008 R2提供了目录树式的浏览方式，浏览数据库信息方便、快捷。在Management Studio的“对象资源管理器”窗口中，选中“数据库”节点下的某个数据库，即可查看该数据库的详细信息。

2．用系统存储过程查看数据库信息

SQL Server 2008 R2提供了不少有用的系统存储过程，可以用它们获得许多从Management Studio界面中不易或不能看到的信息。有关存储过程的详细介绍请参见第7章，读者如果目前不了解存储过程也不要紧，可以把它当作函数或命令来用。

（1）用系统存储过程显示数据库结构
可以使用系统存储过程Sp_helpdb来显示数据库结构，其语法如下：
Sp_helpdb [[@dbname=] 'name']

使用Sp_helpdb系统存储过程可以显示指定数据库的信息。如果不指定[@dbname=]'name'子句，则会显示在master.dbo.sysdatabases表中存储的所有数据库信息，命令执行成功会返回0，否则返回1。如显示AdventureWorks2008R2数据库的信息：

EXEC Sp_helpdb AdventureWorks2008R2

（2）用系统存储过程显示文件信息
可以使用存储过程Sp_helpfile来显示当前数据库中的文件信息，其语法如下：

Sp_helpfile [[@filename =] 'name']

如果不指定文件名称，则会显示当前数据库中所有的文件信息。命令执行成功会返回0，否则返回1。如显示AdventureWorks2008R2数据库中的Address表的信息：

EXEC Sp_helpfile Address

（3）用系统存储过程显示文件组信息
可以系统存储过程Sp_helpfilegroup来显示当前数据库中的文件组信息，其语法如下：

Sp_helpfilegroup [[@filegroupname =] 'name']

如果不指定文件组名称，则会显示当前数据库中所有的文件组信息。命令执行成功会返回0，否则返回1。如显示AdventureWorks2008R2数据库中的所有文件组信息：

use AdventureWorks2008R2
EXEC Sp_helpfilegroup

3.4 创建与使用数据表

SQL使用数据定义语言（Data Definition Language，DDL）实现数据定义功能。

3.4.1 数据类型

关系表中的每一列（即每个字段）都来自同一个域，属于同一种数据类型。定义数据表时，需要为表中的每一列设置一种数据类型。SQL Server 2008 R2的数据类型包括以下七类。

（1）精确数值类型（Exact Numerics）。用来定义精确的数值，包括bigint、int、smallint、tinyint、bit、numeric、decimal、money、smallmoney九种。
（2）近似数值类型（Approximate Numerics）：当数值的位数太多时，可用此数据类型来取数值的近似值，有float和real两种。
（3）日期时间类型（Date and Time）：用来表示日期与时间，按照时间范围与精确程度可分为date，datetime、datetime2、time、smalldatetime、datetimeoffset六种。
（4）字符串类型（Character Strings）：包括char、varchar和text三种。
（5）Unicode字符串类型（Unicode Character Strings）：采用双字节文字编码标准，包括nchar、nvarchar和ntext三种。与字符串数据类型类似，但Unicode的一个字符用2字节存储。
（6）二进制字符串类型（Binary Strings）：用来定义二进制码的数据，有binary、varbinary和image三种，通常用十六进制表示，如0x5F3C。
（7）其他数据类型（Other Data Types）：包括cursor、timestamp、hierarchyid、uniqueidentifier、sql_variant、xml、table七种。

常见数据类型的有关规定如表3-1所示。

其中，

（1）timestamp数据类型提供数据库范围内的唯一值。此类型相当于binary(8)或varbinary(8)，但当它所定义的列在更新或添加数据行时，此列的值会被自动更新，一个计数值将自动地添加到此timestamp数据列中。每个数据库表中只能有一个timestamp类型数据列。如果建立一个名为“timestamp”的列，则该列的类型将被自动设为timestamp数据类型。
（2）uniqueidentifier数据类型存储一个16位的十六进制数。此数字称为全球唯一标识符（Globally Unique Identifier，GUID），此数字由SQL Server的NEWID() 函数产生。全球唯一标识符在全球各地的计算机用NEWID()函数产生的数字不会相同。
（3）sql_variant数据类型可以存储除文本、图形数据和timestamp类型数据外的其他任何合法的SQL Server数据。此数据类型大大方便了SQL Server的开发工作。
（4）table数据类型用于存储对表或视图处理后的结果集。这一类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。

3.4.2 创建数据表

数据表是由行和列组成的，创建数据表的过程就是定义数据表的列的过程，也就是定义数据表结构的过程。

1．用Management Studio创建数据表

（1）右键单击“对象资源管理器”中“数据库”下的“表”节点，从快捷菜单中选择“新建表”命令，会弹出定义数据表结构对话框如图3-7所示。其中，每一行用于定义数据表的一个字段，包括字段名、数据类型、长度、字段是否为NULL以及默认值等。其中，

① 列名”（即表中某个字段名）由用户命名，最长128字符，可包含中文、英文、下划线、#号、货币符号（￥）及@符号。同一表中不允许有重名的列。
② 数据类型”，定义字段可存放数据的类型。
③ 字段的“长度”、“精度”和“小数位数”。字段的长度指字段所能容纳的最大数据量，不同的数据类型，其长度的意义不同。

● 对字符型与Unicode字符类型而言，长度代表字段所能容纳的字符的数目，因此它会限制用户所能输入的文本长度。
● 对数值型类型而言，长度则代表字段使用多少个字节来存放数字，由精度决定，精度越高，字段的长度就越大。精度是指数据中数字的位数，包括小数点左侧的整数部分和小数点右侧的小数部分。例如，数字12345.678，其精度为8，小数位数为###3 . 只有数值类型才有必要指定精度和小数位数。
● 各种整数型的字段长度是固定的，用户不需要输入长度，系统根据相应整数类型的不同自动给出字段长度。
● 对binary、varbinary和image数据类型而言，长度代表字段所能容纳的字节数。

④ “允许空”，当对某个字段的“允许空”列上打勾“ ”时，表示该字段的值允许为NULL值。这样，在向数据表中输入数据时，如果没有给该字段输入数据，系统将自动取NULL值，否则，必须给该字段提供数据。
⑤ “默认值”，表示该字段的默认值（即DEFAULT值）。如果规定了默认值，在向数据表中输入数据时，如果没有给该字段输入数据，系统自动将默认值写入该字段。

（2）将数据表中各列定义完毕后，单击工具栏中的保存按钮，完成创建表过程。

2．用SQL命令创建数据表

可以使用CREATE TABLE语句创建数据表，其基本语法格式为：

CREATE TABLE <表名> (<列定义>[{，<列定义>|<表约束>}])

其中：
① <表名>最多可有128个字符，如S，SC，C等，不允许重名。
② <列定义>的书写格式为，<列名> <数据类型> [DEFAULT] [{<列约束>}]。
③ DEFAULT，若某字段设置有默认值，则当该字段未被输入数据时，以该默认值自动填入该字段。
④ 在SQL中用如下所示的格式来表示数据类型以及它所采用的长度、精度和小数位数，其中的N代表长度，P代表精度，S表示小数位数。

binary(N) — binary (10)
char(N) — char(20)
numeric(P,[S]) — numeric(8,3)

但有的数据类型的精度与小数位数是固定的，对采用此类数据类型的字段而言，不需设置精度与小数位数。例如，如果某字段采用INT数据类型，其长度固定是4，精度固定是10，小数位数则固定是0，这表示该字段能存放10位没有小数点的整数，存储大小则是4个字节。

[例3-4] 用SQL命令建立一个学生表S。

CREATE TABLE S
( SNo CHAR(6),
SN VARCHAR(8),
Sex CHAR(2) DEFAULT '男',
Age INT,
Dept VARCHAR(20))

执行该语句后，便创建了学生表S。该数据表中含有SNo、SN、Sex、Age及Dept共5个字段，它们的数据类型和字段长度分别为CHAR(6)、VARCHAR(8)、CHAR(2)，INT及VARCHAR(20)。其中，Sex字段的缺省值为‘男’。

3.4.3 定义数据表的约束

例3-4为创建基本表的最简单形式，还可以对表进一步定义，如主键、空值等约束的设定，使数据库用户能够根据应用的需要对基本表的定义作出更为精确和详尽的规定。

数据的完整性是指保护数据库中数据的正确性、有效性和相容性，防止错误的数据进入数据库造成无效操作。SQL Server提供的数据完整性机制主要包括：约束（Constraint）、默认（Default）、规则（Rule）、触发器（Trigger）、存储过程（Stored Procedure）等。本节只介绍约束，第5章介绍默认和规则，第7章介绍存储过程和触发器。

约束是SQL Server自动强制数据库完整性的方式，约束定义了列中允许的取值。

在SQL Server中，对于数据表的约束分为列约束和表约束。其中，列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用“，”分隔，定义表约束时必须指出要约束的列的名称。完整性约束的基本语法格式为：

[CONSTRAINT <约束名> ] <约束类型>

约束名：约束不指定名称时，系统会给定一个名称。
约束类型：在定义完整性约束时必须指定完整性约束的类型。在SQL Server中可以定义五种类型的完整性约束，下面分别加以介绍。

1．NULL/NOT NULL约束

NULL值不是0也不是空白，更不是填入字符串“NULL”，而是表示“不知道”、“不确定”或“没有数据”的意思。当某一字段的值一定要输入值才有意义的时候，则可以设置为NOT NULL。例如，主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用。该约束只能用于定义列约束，其语法格式如下：

[CONSTRAINT <约束名> ][NULL|NOT NULL]

[例3-5] 建立一个S表，对SNo字段进行NOT NULL约束。

CREATE TABLE S
( SNo CHAR(6) CONSTRAINT S_CONS NOT NULL,
SN VARCHAR(8),
Sex CHAR(2),
Age INT,
Dept VARCHAR(20))

当SNo为空时，系统给出错误信息，无NOT NULL约束时，系统缺省为NULL。其中S_CONS为指定的约束名称，当约束名省略时，系统自动产生一个名字。如下列语句的功能与例3-5相同，只是省略约束名称。

CREATE TABLE S
( SNo CHAR(6) NOT NULL,
SN VARCHAR(8),
Sex CHAR(2),
Age INT,
Dept VARCHAR(20))

2．UNIQUE约束（唯一约束）

UNIQUE约束用于指明基本表在某一列或多个列的组合上的取值必须唯一。定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。唯一键允许为空，但系统为保证其唯一性，最多只可以出现一个NULL值。

在建立UNIQUE约束时，需要考虑以下几个因素。

• 使用UNIQUE约束的字段允许为NULL值；
• 一个表中可以允许有多个UNIQUE约束；
• 可以把UNIQUE约束定义在多个字段上；
• UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引，缺省为非聚集索引。

UNIQUE既可用于列约束，也可用于表约束。UNIQUE用于定义列约束时，其语法格式如下：

[CONSTRAINT <约束名>] UNIQUE

[例3-6] 建立一个S表，定义SN为唯一键。

CREATE TABLE S
( SNo CHAR(6),
SN CHAR(8) CONSTRAINT SN_UNIQ UNIQUE,
Sex CHAR(2),
Age INT,
Dept VARCHAR(20))

其中，SN_UNIQ为指定的约束名称，约束名称可以省略，如下例：

CREATE TABLE S
( SNo CHAR(6),
SN CHAR(8) UNIQUE,
SexCHAR(2),
Age INT,
Dept VARCHAR(20))

UNIQUE用于定义表约束时，其语法格式如下：

[CONSTRAINT <约束名>] UNIQUE（<列名>[{,<列名>}]）

[例3-7] 建立一个S表，定义SN+Sex为唯一键，此约束为表约束。

CREATE TABLE S
( SNo CHAR(6),
SN CHAR(8) UNIQUE,
Sex CHAR(2),
Age INT,
Dept VARCHAR(20),
CONSTRAINT S_UNIQ UNIQUE(SN, Sex))

系统为SN+Sex建立唯一索引，确保同一性别的学生没有重名。

3．PRIMARY KEY约束（主键约束）

PRIMARY KEY约束用于定义基本表的主键，起唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。

PRIMARY KEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别。

• 在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束。
• 对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的唯一键，则允许为NULL。
• 不能为同一个列或一组列，既定义UNIQUE约束，又定义PRIMARY KEY约束。

PRIMARY KEY既可用于列约束，也可用于表约束。PRIMARY KEY用于定义列约束时，其语法格式如下：

CONSTRAINT <约束名> PRIMARY KEY

[例3-8] 建立一个S表，定义SNo为S的主键，建立另外一个数据表C，定义CNo为C的主键。

定义数据表S：

CREATE TABLE S
( SNo CHAR(6) CONSTRAINT S_Prim PRIMARY KEY,
SN CHAR(8),
Sex CHAR(2),
Age INT,
Dept VARCHAR(20))

定义数据表C：

CREATE TABLE C
( CNo CHAR(5) CONSTRAINT C_Prim PRIMARY KEY,
CN CHAR(20),
CT INT)

PRIMARY KEY用于定义表约束时，即将某些列的组合定义为主键时，其语法格式如下：

[CONSTRAINT <约束名>] PRIMARY KEY (<列名>[{,<列名>}])

[例3-9] 建立一个SC表，定义SNo+CNo为SC的主键。

CREATE TABLE SC
( SNo CHAR(5) NOT NULL,
CNo CHAR(5) NOT NULL,
Score NUMERIC(4,1),
CONSTRAINT SC_Prim PRIMARY KEY(SNo,CNo))

4．FOREIGN KEY约束（外键约束）

FOREIGN KEY约束指定某一个列或几列作为外部键。其中，包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表称为主表。系统保证从表在外部键上的取值是主表中某一个主键值或唯一键值，或者取空值，以此保证两表间的参照完整性。

FOREIGN KEY既可用于列约束，也可用于表约束，其语法格式为：

[CONSTRAINT<约束名>] FOREIGN KEY REFERENCES <主表名> (<列名>[{,<列名>}])

[例3-10] 建立一个SC表，定义SNo，CNo为SC的外部键。

CREATE TABLE SC
( SNo CHAR(5) NOT NULL CONSTRAINT S_Fore FOREIGN KEY REFERENCES S(SNo),
CNo CHAR(5) NOT NULL CONSTRAINT C_Fore FOREIGN KEY REFERENCES C(CNo),
Score NUMERIC(4,1),
CONSTRAINT S_C_Prim PRIMARY KEY (SNo,CNo))

5．CHECK约束

CHECK约束用来检查字段值所允许的范围，如一个字段只能输入整数，而且限定在0～100的整数，以此来保证域的完整性。

在建立CHECK约束时，需要考虑以下几个因素。

• 一个表中可以定义多个CHECK约束；
• 每个字段只能定义一个CHECK约束；
• 在多个字段上定义的CHECK约束必须为表约束；
• 当执行INSERT、UPDATE语句时，CHECK约束将验证数据。

CHECK既可用于列约束，也可用于表约束，其语法格式为：

[CONSTRAINT <约束名>] CHECK (<条件>)

[例3-11] 建立一个SC表，定义Score的取值范围为0～100之间。

CREATE TABLE SC
( SNo CHAR(5),
CNo CHAR(5),
Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score>=0 AND Score <=100))

[例3-12] 建立包含完整性定义的学生表S。

CREATE TABLE S
( SNo CHAR(6) CONSTRAINT S_Prim PRIMARY KEY,
SN CHAR(8) CONSTRAINT SN_Cons NOT NULL,
Sex CHAR(2) CONSTRAINT Sex_Cons NOT NULL DEFAULT '男',
Age INT CONSTRAINT Age_Cons NOT NULL
CONSTRAINT Age_Chk CHECK (Age BETWEEN 15 AND 50),
Dept CHAR(10) CONSTRAINT Dept_Cons NOT NULL)

与例3-4相比，例3-12所创建的学生表中的每一列都增加了完整性约束定义。其中，指定SNo为主键，指定SN、Sex、Age、Dept各个列均不能为空，同时Sex的默认值为“男”，Age的取值范围为15～50。

读者可以模仿上例建立包含完整性定义的表T、表C、表SC和表TC。

3.4.4 修改数据表

由于应用环境和应用需求的变化，可能要修改基本表的结构，比如增加新列和完整性约束、修改原有的列定义和完整性约束等。

1．用Management Studio修改数据表的结构

用Management Studio修改数据表的结构，可按下列步骤进行操作。

（1）在Management Studio中的“对象资源管理器”窗口中，展开“数据库”节点。
（2）右键单击要修改的数据表，从快捷菜单中选择“设计表”命令，则会弹出图3-8所示的修改数据表结构对话框。可以在此对话框中修改列的数据类型、名称等属性，添加或删除列，也可以指定表的主关键字约束。

（3）修改完毕后，单击工具栏中的保存按钮，存盘退出。

2．用SQL命令修改数据表

SQL使用ALTER TABLE命令来完成这一功能，有如下三种修改方式。

（1）ADD方式
ADD方式用于增加新列和完整性约束，定义方式与CREATE TABLE语句中的定义方式相同，其语法格式为：

ALTER TABLE <表名> ADD <列定义> | <完整性约束定义>

[例3-13] 在S表中增加一个班号列和住址列。

ALTER TABLE S
ADD
Class_No CHAR(6),
Address CHAR(40)

[例3-14] 在SC表中增加完整性约束定义，使Score在0～100之间。

ALTER TABLE SC
ADD
CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)

（2）ALTER 方式

ALTER方式用于修改某些列，其语法格式为：

ALTER TABLE <表名>
ALTER COLUMN <列名> <数据类型> [NULL|NOT NULL]

[例3-15] 把S表中的SN列加宽到10个字符。

ALTER TABLE S
ALTER COLUMN
SN CHAR(10)

注意：使用此方式有如下一些限制。

• 不能改变列名；
• 不能将含有空值的列的定义修改为NOT NULL约束；
• 若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型；
• 只能修改NULL/NOT NULL约束，其他类型的约束在修改之前必须先将约束删除，然后再重新添加修改过的约束定义。

（3）DROP方式

DROP方式只用于删除完整性约束定义，其语法格式为：

ALTER TABLE<表名>
DROP CONSTRAINT <约束名>

[例3-16] 删除S表中的主键。

ALTER TABLE S
DROP CONSTRAINT S_Prim

3.4.5 删除基本表

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

1．用Management Studio删除数据表

在Management Studio中，右键单击要删除的表，从快捷菜单中选择“删除”命令，会弹出“删除对象”对话框，如图3-9所示。单击“显示依赖关系”按钮，即会弹出“依赖关系”对话框，其中列出了表所依靠的对象和依赖于表的对象，当有对象依赖于表时不能删除表。

2．用SQL命令删除数据表

删除表的SQL命令语法格式为：

DROP TABLE <表名>

[例3-17] 删除表S。

DROP TABLE S

3.4.6 查看数据表

1．查看数据表的属性

在Management Studio“对象资源管理器”中展开“数据库”节点，选中相应的数据库，从中找到要查看的数据表，右键单击该表，从快捷菜单中选择“属性”菜单项，则会弹出“表属性”对话框，如图3-10所示，从图中可以看到表的详细属性信息，如表名、所有者、创建日期、文件组、记录行数、数据表中的字段名称、结构和类型等。

2．查看数据表中的数据

在Management Studio中，用右键单击要查看数据的表，从快捷菜单中选择“选择前1000行(W)”命令，则会显示表中的前1000条数据，如图3-11所示。

3.5 创建与使用索引

3.5.1 索引概述

索引是一种可以加快检索的数据库结构，它包含从表或视图的一列或多列生成的键，以及映射到指定数据存储位置的指针。通过创建设计良好的索引，可以显著提高数据库查询和应用程序的性能。从某种程度上说，可以把数据库看作一本书，把索引看作书的目录。借助目录查找信息，显然比没有目录的书方便快捷。除提高检索速度外，索引还可以强制表中的行具有唯一性，从而确保数据的完整性。

索引一旦创建，将由DBMS自动管理和维护。当插入、修改或删除记录时，DBMS会自动更新表中的索引。编写SQL查询语句时，有索引的表与没有索引的表在使用方法上是一致的。虽然索引具有诸多优点，但要避免在一个表中创建大量的索引，否则会影响插入、删除、更新数据的性能，增加索引调整的成本，降低系统的响应速度。

3.5.2 索引的类型

在SQL Server 2008 R2中，有两种基本类型的索引：聚集索引和非聚集索引。除此之外，还有唯一索引、视图索引、全文索引和XML索引等。

1．聚集索引

在聚集索引中，表中行的物理存储顺序与索引键的逻辑（索引）顺序相同。由于真正的物理存储只有一个，因此，一个表只能包含一个聚集索引。创建或修改聚集索引可能会非常耗时，因为要根据索引键的逻辑值重新调整物理存储顺序。

在SQL Server 2008 R2中创建PRIMARY KEY约束时，如果不存在该表的聚集索引且未指定唯一非聚集索引，则自动对PRIMARY KEY涉及的列创建唯一聚集索引。在添加UNIQUE约束时，默认将创建唯一非聚集索引。如果不存在该表的聚集索引，可以指定唯一聚集索引。

在以下情况下，可以考虑使用聚集索引。

（1）包含有限数量的唯一值的列，如仅包含100个唯一状态码的列；
（2）使用BETWEEN、>、>=、<和<=这样的运算符返回某个范围值的查询；
（3）返回大型结果集的查询。

2．非聚集索引

非聚集索引与聚集索引具有相似的索引结构。不同的是，非聚集索引不影响数据行的物理存储顺序，数据行的物理存储顺序与索引键的逻辑（索引）顺序并不一致。每个表可以有多个非聚集索引，而不像聚集索引那样只能有一个。在SQL Server 2008 R2中每个表可以创建最多249个非聚集索引，其中包括PRIMARY KEY或者UNIQUE约束创建的索引，但不包括XML索引。

与聚集索引类似，非聚集索引也可以提升数据的查询速度，但也会降低插入和更新数据的速度。当更改包含非聚集索引的表数据时，DBMS必须同步更新索引。如果一个表需要频繁地更新数据，不应对它建立太多的非聚集索引。另外，如果硬盘和内存空间有限，也应该限制非聚集索引的数量。

3．唯一索引

唯一索引能够保证索引键中不包含重复的值，从而使表中的每一行在某种方式上具有唯一性。只有当唯一性是数据本身的特征时，指定唯一索引才有意义。例如，如果希望确保学生表的“身份证号”列的值唯一，当主键为“学号”时，可以为“身份证号”列创建一个UNIQUE约束。当尝试在该列中为多个学生输入相同的身份证号，将显示错误消息，禁止输入重复值。使用多列唯一索引，能保证索引键值中多列的组合是唯一的。例如，如果为“姓”和“名”列的组合创建了唯一索引，则表中任意两行记录不会具有完全相同的“姓”和“名”值。

聚集索引和非聚集索引都可以是唯一的，可以为同一个表创建一个唯一聚集索引和多个唯一非聚集索引。

创建PRIMARY KEY或UNIQUE约束时会为指定列自动创建唯一索引。由UNIQUE约束自动生成的唯一索引和独立于约束手工创建的唯一索引没有本质区别，二者数据验证的方式是相同的，查询优化器也不会区分唯一索引是由约束自动创建的还是手动创建的。但是，如果目的是要实现数据完整性，则应为列创建UNIQUE或PRIMARY KEY约束，这样做才能使索引的目标明确。

4．视图索引

视图也称为虚表，由视图返回的结果集格式与基本表相同，都由行和列组成，在SQL语句中使用视图与使用基本表的方式相同。标准视图的结果集不是永久地存储在数据库中。每次查询引用标准视图时，SQL Server会在内部将视图的定义替换为该查询，直到修改后的查询仅引用基本表。

对标准视图而言，查询动态生成的结果集开销很大，特别是涉及对大量行进行复杂处理的视图（如聚合大量数据或联接许多行）。如果在查询中频繁地引用这类视图，可通过对视图创建唯一聚集索引来提升性能。这类索引称为视图索引，对应的视图称为索引视图。索引视图是从SQL Server 2005后引入的一个新特征，可以有效改善标准视图的查询性能。对视图创建唯一聚集索引后，结果集将直接存储在数据库中，就像带有聚集索引的基本表一样。

如果很少更新基础表数据，则索引视图的使用效果最佳。如果经常更新基础表数据，维护索引视图的开销可能超过使用索引视图所带来的性能收益。如果基础表数据以批处理的形式定期更新，但在两次更新之间主要作为只读数据处理，可考虑在更新前删除所有索引视图，更新完毕后再重新生成，这样可提升批处理的更新性能。

5．全文索引

全文索引是目前搜索引擎的关键技术之一。试想在1兆大小的文件中搜索一个词，可能需要几秒，在100兆的文件中可能需要几十秒，在更大的文件中搜索开销会更大。为加快此类检索速度，出现了全文索引技术，也称倒排文档技术。其原理是先定义一个词库，然后在文章中查找并存储每个词条出现的频率和位置，相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置。

在SQL Server 2008 R2中，每个表只允许有一个全文索引。若要对某个表创建全文索引，该表必须具有一个唯一且非空（NULL）的列。可以对以下类型的列创建全文索引：char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary和varbinary(max)，从而可对这些列进行全文搜索。对数据类型为varbinary、varbinary(max)、image或xml的列创建全文索引需要指定文档类型列，类型列用来存储文件的扩展名（.doc、.pdf和xls等）。

6．XML索引

可以对xml数据类型列创建XML索引。XML索引对列中xml实例的所有标记、值和路径进行索引，从而提高查询性能。在下列情况下，可考虑创建XML索引。

（1）对xml列进行查询在工作中很常见。但需要注意的是，xml列如果频繁修改，可能会造成很高的索引维护开销。
（2）xml列的值相对较大，而检索的部分相对较小。生成索引避免了在运行时分析所有数据，能实现高效的查询处理。

3.5.3 索引的设计

既然索引有如此多的优点，能否为表中的每一列创建一个索引呢？这种想法有其合理性，但非常不明智，因为索引也有许多不利之处。

（1）创建和维护索引要耗费时间，随数据量的增加时间也会增加。
（2）索引需要占用物理存储空间。如果建立聚集索引，需要的存储空间会更大。
（3）当修改表中数据时，索引要同步更新，降低了数据的更改速度。

因此，应仔细考虑哪些列上需要创建索引，哪些列上不能创建索引。索引设计不佳和缺少索引是影响数据库和应用程序性能的重要瓶颈。设计高效的索引对于获得良好的数据库和应用程序性能极为重要，可考虑以下索引设计准则。

（1）一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能，要减少或删除不必要的索引。
（2）避免对频繁更新的表创建过多的索引，并且索引的列要尽可能少。
（3）使用多个索引可以提高更新少而数据量大的查询性能，因为查询优化器有更多的索引可供选择，从而可以确定最快的访问方法。
（4）对数据量小的表进行索引可能不会产生优化效果，查询优化器遍历索引数据时花费的时间可能比直接查询基本表的时间还长。
（5）视图包含聚合、表联接，或聚合和表联接的组合时，视图索引可以显著提升性能。
（6）使用数据库引擎优化顾问来分析性能瓶颈，并据此调整索引。

3.5.4 索引的使用

1．创建索引

在SQL Server 2008 R2中可通过Management Studio创建索引，也可以通过SQL语句创建索引。

（1）使用Management Studio创建索引
打开“对象资源管理器”，右键单击“数据库→【数据库名称】→表→索引”节点，在弹出菜单中选择“新建索引”命令，即可弹出新建索引窗口，如图3-12所示。设置好索引的名称、类型、是否唯一等，然后添加要索引的列（可以为一列或多列），单击“确定”按钮即可。

（2）使用SQL语句创建索引
在SQL SERVER 2008 R2中，可以使用CREATE INDEX语句创建索引，既可以创建聚集索引，也可以创建非聚集索引，既可以在一列上创建索引，也可以在多列上创建索引。其基本的语法形式如下：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON table_or_view_name ( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option> [ ,...n ] ]
[ ON { filegroup_name | "default" } ]

其中，UNIQUE表示创建唯一索引，CLUSTERED表示创建聚集索引，NONCLUSTERED表示创建非聚集索引。

[例3-18] 为表SC在SNo和CNo上建立唯一索引。

CREATE UNIQUE INDEX SCI ON SC(SNo,CNo)

执行此命令后，为SC表建立一个名为SCI的唯一索引，此索引为SNo和CNo两列的复合索引，即对SC表中的行先按SNo的递增顺序索引，对于相同的SNo，又按CNo的递增顺序索引。由于有UNIQUE的限制，所以该索引在（SNo，CNo）组合列的排序上具有唯一性，不存在重复值。

[例3-19] 为教师表T在TN上建立聚集索引。

CREATE CLUSTERED INDEX TI ON T(TN)

执行此命令后，为T表建立一个名为TI的聚集索引，T表中的记录将按照TN的值升序存放。

2．删除索引

（1）通过Management Studio删除索引
打开“对象资源管理器”，展开“数据库→【数据库名称】→表→索引”节点，右键单击要删除的索引，在弹出菜单中选择“删除”命令，即可删除索引，如图3-12所示。

（2）通过SQL语句删除索引
在SQL Server 2008 R2中，可以使用DROP INDEX删除索引。语法格式如下：

DROP INDEX

.

也可以使用如下的语法格式：

DROP INDEX ON

上述语句中，index_name表示要删除的索引名，table_or_view_name表示当前索引基于的表名或者视图名。

3．修改索引

通常情况下，索引建立后由DBMS自动维护更新，无需手工干预，但有的情况下可能需要对索引进行修改。例如，向一个带有索引的表中插入大量数据时，为了提高插入性能，可考虑先删除索引，然后再重新建立索引。修改索引的SQL命令语法如下：

ALTER INDEX { index_name | ALL }
ON table_or_view_name
{ REBUILD
[ [PARTITION = ALL]
[ WITH ( < rebuild_index_option> [ ,...n ] ) ]
| [ PARTITION = partition_number
[ WITH ( < single_partition_rebuild_index_option>
[ ,...n ] )
]
]
]
| DISABLE
| REORGANIZE
[ PARTITION = partition_number ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
| SET ( < set_index_option> [ ,...n ] )
}
[ ; ]

主要参数的含义如下。

（1）REBUILD：删除索引并且重新生成索引，这样可以根据指定的填充度压缩页来删除磁盘碎片，回收磁盘空间，重新排序索引。
（2）PARTITION：指定只重新生成或重新组织索引的一个分区。如果index_name不是已分区索引，则不能指定PARTITION。
（3）DISABLE：将索引标记为禁用，从而不能由数据库引擎使用。任何索引均可被禁用，已禁用索引的索引定义保留在没有基础索引数据的系统目录中。禁用聚集索引将阻止用户访问基础表数据。若要启用已禁用的索引，使用ALTER INDEX REBUILD或CREATE INDEX WITH DROP_EXISTING命令。
（4）REORGANIZE：重新组织索引，此子句等同于DBCC INDEXDEFRAG。ALTER INDEX REORGANIZE语句始终联机执行，这意味着不保留长期阻塞的表锁，对基础表的查询或更新可以在ALTER INDEX REORGANIZE事务处理期间继续执行。不能为已禁用的索引指定REORGANIZE。

4．查看索引

（1）使用Management Studio查看索引
打开“对象资源管理器”，展开“数据库→【数据库名称】→表→索引”节点，即可看到该表下的所有索引。双击其中一个索引，即可查看该索引的详细信息。

（2）用Sp_helpindex存储过程查看索引
Sp_helpindex存储过程可以返回表中的所有索引信息，其语法如下：

Sp_helpindex [@objname =] 'name'

其中，[@objname =] 'name'子句指定当前数据库中的表名。

[例3-20] 查看表SC的索引。

EXEC Sp_helpindex SC

如果要更改索引名称，可利用Sp_rename存储过程更改，其语法如下：

Sp_rename '数据表名.原索引名', '新索引名'

[例3-21] 更改T表中的索引TI名称为T_Index。

EXEC Sp_rename 'T.TI', 'T_Index'

3.5.5 使用数据库引擎优化顾问

数据库引擎优化顾问检查指定查询的处理方式，然后建议如何通过修改物理设计结构（如索引、索引视图和分区），以改善查询性能。它取代了SQL Server 2000中的索引优化向导，并提供了许多新的功能。数据库引擎优化顾问提供两个用户界面：图形用户界面（GUI）和dta命令行实用工具。使用GUI可以方便快捷地查看优化会话结果，而使用dta实用工具则可以轻松地将数据库引擎优化顾问的功能并入脚本中，从而实现自动优化。数据库引擎优化顾问也可以接受XML输入，借助XML输入可对优化过程进行更多控制。

本节以SQL Server 2008 R2的示例数据库“Adventure Works2008R2”为例，讲解数据库引擎优化顾问的使用方法。

1．优化工作负荷

（1）将图3-14所示的SQL查询语句保存为MyScript.sql，存储在可轻松找到的目录中。
（2）单击开始菜单，找到“Microsoft SQL Server 2008 R2”，选择其中的“性能工具→数据库引擎优化顾问”命令，即可启动数据库引擎优化顾问，如图3-15所示。

（3）在图3-15的“会话名称”中，键入“MySession”。针对“工作负荷”选择“文件”，再单击 （查找工作负荷文件）按钮，找到步骤（1）保存的MyScript.sql文件。在“选择要优化的数据库和表”中选择AdventureWorks2008R2，选中“保存优化日志”选项。
（4）单击“优化选项”选项卡，可以设置详细的优化选项。单击其中的“高级选项”可查看其他优化选项。本步骤中，保留所有系统默认选项。
（5）在工具栏中，单击"开始分析"按钮。在数据库引擎优化顾问分析工作负荷时，可以监视“进度”选项卡上的状态。优化完成后，“建议”选项卡随即显示，如图3-16。如果收到有关优化结束日期和时间的错误，请检查“优化选项”选项卡上的“结束时间”。请确保“结束时间”的日期和时间晚于当前的日期和时间，必要时可进行更改。
（6）在“操作”菜单中，单击“保存建议”，将建议保存为SQL脚本，文件命名为MyRecommendations.sql。 上述步骤已完成对AdventureWorks2008R2数据库中简单SELECT语句的工作负荷优化。“工作负荷”中除了选择“文件”作为分析对象外，还可以将SQL Server Profiler跟踪文件和表作为分析对象。

2．查看优化建议和优化报告 优化分析完毕后，数据库引擎优化顾问将在“建议”选项卡中显示优化建议。以下步骤讲述了如何分析利用该优化建议
（1）图3-16的“建议”选项卡中，每一行代表数据库引擎优化顾问建议删除或创建的一个数据库对象（索引或索引视图）。滚动到最右边的列，并单击“定义”，将显示“SQL脚本预览”窗口，如图3-17所示，从中可以查看建议的SQL脚本。单击“关闭”按钮关闭预览窗口。

如果找不到包含链接的“定义”列，单击清除页面底部的“显示现有对象”复选框，从而减少显示的行数。清除此复选框后，数据库引擎优化顾问将仅显示已为其生成建议的对象。
（2）在“索引建议”窗格中单击右键，在弹出菜单中可以选择应用或取消建议。
（3）在Management Studio的查询编辑器中打开MyRecommendations.sql脚本。通过在查询编辑器中执行脚本，可将建议应用于AdventureWorks2008R2数据库。但现在不要执行该操作，不运行该脚本，直接在查询编辑器中将其关闭。另外，也可以通过单击数据库引擎优化顾问“操作”菜单上的“应用建议”选项来应用建议。
（4）如果“建议”选项卡上存在多行记录，检查是否需要清除某些行，因为有些优化建议并不合理，需要逐一评阅决定是否采用。
（5）在“操作”菜单上，单击“评估建议”。数据库引擎优化顾问将创建一个新的优化会话，从中可以评估MySession原有建议的子集。键入新的会话名称“EvaluateMySession”，然后单击“开始分析”按钮，可以对新的优化会话重复上述步骤，以查看优化建议。
（6）单击“报告”选项卡，可以查看详细的优化报告信息，如图3-18所示。
（7）根据优化建议和优化报告的内容，对数据库表结构进行调整。例如，针对本例优化建议和优化报告显示，需要在Name字段上添加一个索引，即本例实际上是索引缺失导致的性能瓶颈，这也是最容易遇到的一类性能问题。

3.6 数据查询

3.6.1 SELECT命令的格式与基本使用

数据查询是数据库中最常用的操作。SQL提供SELECT语句，通过查询操作可得到所需的信息。SELECT语句的一般格式为：

SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]
〈列名〉[AS 别名1] [{，〈列名〉[ AS 别名2 ] } ]
[ INTO 新表名]
FROM〈表名1或视图名1〉[[AS] 表1别名] [{，〈表名2或视图名2〉[[AS] 表2别名] } ]
[WHERE〈 检索条件 〉 ]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]]

SELECT语句的格式还可写为：

SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]
列名1 [AS 别名1]
[, 列名2 [ AS 别名2]…]
[INTO 新表名]
FROM 表名 1[[AS] 表1别名]
[INNER|RIGHT|FULL|OUTER][OUTER]JOIN
表名2 [[AS] 表2别名]
ON 条件

查询的结果仍是一个表。SELECT语句的执行过程是，根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。如果有GROUP子句，则将查询结果按照与<列名1>相同的值进行分组。如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。如果有ORDER子句，查询结果还要按照ORDER子句中<列名2>的值进行排序。

可以看出，WHERE子句相当于关系代数中的选取操作，SELECT子句则相当于投影操作，当涉及多表查询时，WHERE子句要同时给出连接条件。但SQL查询不必规定投影、选取连接的执行顺序，它比关系代数更简单、功能更强大。

[例3-22] 查询全体学生的学号、姓名和年龄。

SELECT SNo, SN, Age
FROM S

查询结果如下：

[例3-23] 查询学生的全部信息。

SELECT *
FROM S

用“*”表示S表的全部列名，而不必逐一列出。

[例3-24] 查询选修了课程的学生的学号。

SELECT DISTINCT SNo
FROM SC

查询结果中的重复行被去掉，查询结果如下：

上述查询均为不使用WHERE子句的无条件查询，也称作投影查询，例3-24的查询结果与关系代数中的投影操作SNO（SC）的结果相同。在关系代数中，投影后自动消去重复行，而SQL中必须使用关键字DISTINCT才会消去重复行。

另外，利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果列标题的名字。

[例3-25] 查询全体学生的姓名、学号和年龄。

SELECT SN Name, SNo, Age
FROM S

或

SELECT SN AS Name, SNo, Age
FROM S

查询结果如下：

其中，Name为SN的别名。在SELECT语句中可以为查询结果的列名重新命名，并且可以重新指定列的次序。

3.6.2 条件查询

当要在表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件。WHERE子句中，条件通常通过三部分来描述。

（1）列名；
（2）比较运算符；
（3）列名、常数。

常用的比较运算符如表3-2所示。

1．比较大小

[例3-26] 查询选修课程号为'C1'的学生的学号和成绩。

SELECT SNo,Score
FROM SC
WHERE CNo= 'C1'

查询结果如下：

此查询结果与关系代数中的选取操作σCNo='C1' (TC)的结果相同。

[例3-27] 查询成绩高于85分的学生的学号、课程号和成绩。

SELECT SNo,CNo,Score
FROM SC
WHERE Score>85

2．多重条件查询

当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连接成复合的逻辑表达式。其优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级。

[例3-28] 查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩。

SELECT SNo, CNo, Score
FROM SC
WHERE (CNo = 'C1' OR CNo = 'C2') AND (Score >= 85)

3．确定范围

[例3-29] 查询工资在1000元～1500元之间的教师的教师号、姓名及职称。

SELECT TNo,TN,Prof
FROM T
WHERE Sal BETWEEN 1000 AND 1500

等价于

SELECT TNo,TN,Prof
FROM T
WHERE Sal>=1000 AND Sal<=1500

[例3-30] 查询工资不在1000元～1500元间的教师的教师号、姓名及职称。

SELECT TNo,TN,Prof
FROM T
WHERE Sal NOT BETWEEN 1000 AND 1500

4．确定集合

利用“IN”操作可以查询属性值属于指定集合的元组。

[例3-31] 查询选修C1或C2的学生的学号、课程号和成绩。

SELECT SNo, CNo, Score
FROM SC
WHERE CNo IN('C1'，'C2')

此语句也可以使用逻辑运算符“OR”实现。

SELECT SNo, CNo, Score
FROM SC
WHERE CNo='C1'OR CNo= 'C2'

利用“NOT IN”可以查询指定集合外的元组。

[例3-32] 查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。

SELECT SNo, CNo, Score FROM SC
WHERE CNo NOT IN('C1'，'C2')

等价于：

SELECT SNo, CNo, Score FROM SC
WHERE (CNo <> 'C1') AND (CNo <> 'C2')

5．部分匹配查询

以上各例均属于完全匹配查询，当不知道完全精确的值时，用户还可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）。LIKE定义的一般格式为：

<属性名> LIKE <字符串常量>

其中，属性名必须为字符型，字符串常量中的字符可以包含通配符，利用这些通配符，可以进行模糊查询，字符串中的通配符及其功能如表3-3所示。

[例3-33] 查询所有姓张的教师的教师号和姓名。

SELECT TNo, TN
FROM T
WHERE TN LIKE '张%'

[例3-34] 查询姓名中第二个汉字是“力”的教师号和姓名。

SELECT TNo, TN
FROM T
WHERE TN LIKE'_力%'

6．空值查询

某个字段没有值称为具有空值（NULL）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。例如，某些学生选修了课程但没有参加考试，就会造成数据表中有选课记录，但没有考试成绩。考试成绩为空值，这与参加考试，成绩为0分是不同的。

[例3-35] 查询没有考试成绩的学生的学号和相应的课程号。

SELECT SNo, CNo
FROM SC
WHERE Score IS NULL

3.6.3 常用库函数及统计汇总查询

SQL提供了许多库函数，增强了基本检索能力。常用的库函数如表3-4所示。

[例3-36] 求学号为S1的学生的总分和平均分。

SELECT SUM(Score) AS TotalScore, AVG(Score) AS AvgScore
FROM SC
WHERE (SNo = 'S1')

查询结果如下：

[例3-37] 求选修C1号课程的最高分、最低分及之间相差的分数。

SELECT MAX(Score) AS MaxScore, MIN(Score) AS MinScore, MAX(Score)－MIN(Score) AS Diff
FROM SC
WHERE (CNo = 'C1')

[例3-38] 求计算机系学生的总数。

SELECT COUNT（SNo）FROM S
WHERE Dept= '计算机'

[例3-39] 求学校中共有多少个系。

SELECT COUNT(DISTINCT Dept) AS DeptNum
FROM S

[例3-40] 统计有成绩同学的人数。

SELECT COUNT (Score)
FROM SC

上例中成绩为0的同学也计算在内，没有成绩（即为空值）的不计算。

[例3-41] 利用特殊函数COUNT(*)求计算机系学生的总数。

SELECT COUNT() FROM S
WHERE Dept='计算机'
COUNT（）用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字。

3.6.4 分组查询

GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。

[例3-42] 查询每个教师的教师号及其任课的门数。

SELECT TNo,COUNT(*) AS C_Num
FROM TC
GROUP BY TNo

GROUP BY子句按TNo的值分组，所有具有相同TNo的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。

查询结果如下：

若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。

[例3-43] 查询选修两门以上（含两门）课程的学生的学号和选课门数。

SELECT SNo, COUNT() AS SC_Num
FROM SC
GROUP BY SNo
HAVING (COUNT() >= 2)

查询结果如下：

GROUP BY子句按SNo的值分组，所有具有相同SNo的元组为一组，对每一组使用函数COUNT进行计算，统计出每个学生选课的门数。HAVING子句去掉不满足COUNT（*）>=2的组。

当在一个SQL查询中同时使用WHERE子句，GROUP BY子句和HAVING子句时，其顺序是WHERE、GROUP BY、HAVING。WHERE与HAVING子句的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组；HAVING子句作用于组，选择满足条件的组，必须用在GROUP BY子句之后，但GROUP BY子句可没有HAVING子句。

3.6.5 查询的排序

当需要对查询结果排序时，应该使用ORDER BY子句，ORDER BY子句必须出现在其他子句之后。排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。

[例3-44] 查询选修C1的学生学号和成绩，并按成绩降序排列。

SELECT SNo, Score
FROM SC
WHERE (CNo = 'C1')
ORDER BY Score DESC

[例3-45] 查询选修C2，C3，C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。

SELECT SNo, CNo, Score
FROM SC
WHERE CNo IN ('C2', 'C3', 'C4', 'C5')
ORDER BY SNo, Score DESC

[例3-46] 求选课在三门以上（含三门）且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序排列。

SELECT SNo, SUM(Score) AS TotalScore
FROM SC
WHERE (Score >= 60)
GROUP BY SNo
HAVING (COUNT(*) >= 3)
ORDER BY SUM(Score) DESC

此语句为分组排序，执行过程如下。

（1）（FROM）取出整个SC；
（2）（WHERE）筛选Score>=60的元组；
（3）（GROUP BY）将选出的元组按SNo分组；
（4）（HAVING）筛选选课三门以上的分组；
（5）（SELECT）在剩下的组中提取学号和总成绩；
（6）（ORDER BY）将选取结果排序。

ORDER BY SUM(Score) DESC可以改写成ORDER BY 2 DESC，“2”代表查询结果的第二列。

3.6.6 数据表连接及连接查询

数据库中的各个表中存放着不同的数据，用户往往需要用多个表中的数据来组合、提炼出所需要的信息。如果一个查询需要对多个表进行操作，就称为连接查询。连接查询的结果集或结果表，称为表之间的连接。连接查询实际上是通过各个表之间共同列的关联性来查询数据的，数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。

前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。

表的连接方法有以下两种。

（1）表之间满足一定条件的行进行连接时，FROM子句指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。
（2）利用关键字JOIN进行连接。

具体的连接方法分为以下几种。

INNER JOIN（内连接）显示符合条件的记录，此为默认值。

LEFT（OUTER）JOIN称为左（外）连接，用于显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示。

RIGHT（OUTER）JOIN称为右（外）连接，用于显示符合条件的数据行以及右边表中不符合条件的数据行。此时左边数据行会以NULL来显示。

FULL（OUTER）JOIN显示符合条件的数据行以及左边表和右边表中不符合条件的数据行。此时缺乏数据的数据行会以NULL来显示。

CROSS JOIN将一个表的每一个记录和另一表的每个记录匹配成新的数据行。

当将JOIN关键词放于FROM子句中时，应有关键词ON与之对应，以表明连接的条件。

下面介绍几种情况的表的连接操作。

1．等值连接与非等值连接

[例3-47] 查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号。

（1）方法1

SELECT T.TNo,TN,CNo
FROM T,TC
WHERE (T.TNo = TC. TNo) AND (TN='刘伟')

这里TN='刘伟'为查询条件，而T.TNo = TC.TNo为连接条件，TNo为连接字段。连接条件的一般格式为：

[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>

其中比较运算符主要有：=、＞、＜、＞=、＜=、! =。

当比较运算符为“=”时，称为等值连接，其他情况为非等值连接。

引用列名TNo时要加上表名前缀，这是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。如果列名是唯一的（比如TN）就不必加前缀。

上面的操作是将T表中的TNo 和TC表中的TNo相等的行连接，同时选取TN为“刘伟”的行，然后再在TNo、TN、CNo列上投影，这是连接、选取和投影操作的组合。

（2）方法2

SELECT T.TNo, TN, CNo
FROM T INNER JOIN TC
ON T.TNo = TC.TNo
WHERE (TN = '刘伟')

（3）方法3

SELECT R1.TNo R2.TN, R1.CNo
FROM
(SELECT TNo,CNo FROM TC ) AS R1
INNER JOIN
(SELECT TNo ,TN FROM T
WHERE TN='刘伟') AS R2
ON R1.TNo=R2.TNo

[例3-48] 查询所有选课学生的学号、姓名、选课名称及成绩。

SELECT S.SNo,SN,CN,Score
FROM S,C,SC
WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo

本例涉及三个表，WHERE子句中有两个连接条件。当有两个以上的表进行连接时，称为多表连接。

[例3-49] 查询每门课程的课程名、任课教师姓名及其职务、选课人数。

SELECT CN,TN,Prof,COUNT(SC.SNo)
FROM C,T,TC,SC
WHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNo
GROUP BY SC.CNo

2．自身连接

当一个表与其自身进行连接操作时，称为表的自身连接。

[例3-50] 查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。

要查询的内容均在同一表T中，可以将表T分别取两个别名，一个是X，一个是Y。将X，Y中满足比刘伟工资高的行连接起来。这实际上是同一表T的大于连接。

方法1：

SELECT X.TN,X.Sal AS Sal_a,Y.Sal AS Sal_b
FROM T AS X ,T AS Y
WHERE X.Sal>Y.Sal AND Y.TN='刘伟'

查询结果如下：

方法2：

SELECT X.TN, X.Sal,Y.Sal
FROM T AS X INNER JOIN T AS Y
ON X.Sal>Y.Sal AND Y.TN='刘伟'

方法3：

SELECT R1.TN,R1.Sal, R2.Sal
FROM
(SELECT TN,Sal FROM T ) AS R1
INNER JOIN
(SELECT Sal FROM T
WHERE TN='刘伟') AS R2
ON R1.Sal>R2.Sal

[例3-51] 检索所有学生姓名、年龄和选课名称。

方法1：

SELECT SN,Age,CN
FROM S,C,SC
WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo

方法2：

SELECT R3.SNo,R3.Age,R4.CN
FROM
(SELECT SNo,SN,Age FROM S) AS R3
INNER JOIN
(SELECT R2.SNo,R1.CN
FROM
(SELECT CNo,CN FROM C) AS R1
INNER JOIN
(SELECT SNo,CNo FROM SC) AS R2
ON R1.CNo=R2.CNo) AS R4
ON R3.SNo=R4.SNo

3．外连接

在上面的连接操作中，不满足连接条件的元组不能作为查询结果输出。如例3-51的查询结果只包括有选课记录的学生，而不会有吴丽同学的信息。而在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中（对BIT类型的列，由于BIT 数据类型不允许有NULL值，因此将会被填上0值再返回到结果中）。

外部连接分为左外部连接和右外部连接两种。以主表所在的方向区分外部连接，主表在左边，则称为左外部连接；主表在右边，则称为右外部连接。

[例3-52] 查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。

SELECT S.SNo,SN,CN,Score
FROM S
LEFT OUTER JOIN SC
ON S.SNo=SC.SNo
LEFT OUTER JOIN C
ON C.CNo=SC.CNo

查询结果只包括所有的学生，没有选课的吴丽同学的选课信息显示为空。

3.6.7 子查询

在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。嵌套查询可以将一系列简单查询构成复杂查询，增强查询能力。子查询的嵌套层次最多可达到255层，以层层嵌套的方式构造查询充分体现了SQL“结构化”的特点。

嵌套查询在执行时由里向外处理，每个子查询是在上一级外部查询处理之前完成的，父查询要用到子查询的结果。

1．返回一个值的子查询

当子查询的返回值只有一个时，可以使用比较运算符（=、 >、 <、 >=、 <=、 !=）将父查询和子查询连接起来。

[例3-53] 查询与“刘伟”老师职称相同的教师号、姓名。

SELECT TNo,TN
FROM T
WHERE Prof= (SELECT Prof
FROM T
WHERE TN= '刘伟')

此查询相当于将查询分成两个查询块来执行。先执行子查询：

SELECT Prof
FROM T
WHERE TN= '刘伟'

子查询向主查询只返回一个值，即刘伟老师的职称“讲师”，然后以此作为父查询的条件，相当于再执行父查询，查询所有职称为“讲师”的教师号、姓名。

SELECT TNo,TN
FROM T
WHERE Prof= '讲师'

查询结果如下：

2．返回一组值的子查询

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。其具体含义详见以下各例。

（1）使用ANY

[例3-54] 查询讲授课程号为C5的教师姓名。

SELECT TN FROM T WHERE (TNo = ANY ( SELECT TNo
FROM TC
WHERE CNo = 'C5'))

先执行子查询，找到讲授课程号为C5的教师号，教师号为一组值构成的集合(T2,T3,T5)；再执行父查询。其中ANY的含义为任意一个，查询教师号为T2，T3，T5的教师的姓名。查询结果如下：

该例也可以使用前面所讲的连接操作来实现：

SELECT TN
FROM T,TC
WHERE T.TNo=TC.TNo AND TC.CNo= 'C5 '

可见，对于同一查询可使用子查询和连接查询两种方法来解决，读者可根据习惯任意选用。

[例3-55] 查询其他系中比计算机系某一教师工资高的教师的姓名和工资。

SELECT TN, Sal
FROM T
WHERE (Sal > ANY ( SELECT Sal
FROM T
WHERE Dept = '计算机'))
AND (Dept <> '计算机')

先执行子查询，找到计算机系中所有教师的工资集合（1500，900）；再执行父查询，查询所有不是计算机系且工资高于900元的教师姓名和工资。

查询结果如下：

此查询也可以写成：

SELECT TN, Sal
FROM T
WHERE Sal > ( SELECT MIN(Sal)
FROM T
WHERE Dept = '计算机')
AND Dept <> '计算机'

先执行子查询，利用库函数MIN找到计算机系中所有教师的最低工资900元；再执行父查询，查询所有不是计算机系且工资高于900元的教师。

（2）使用IN

可以使用IN代替“=ANY”。

[例3-56] 查询讲授课程号为C5的教师姓名（使用IN）。

SELECT TN
FROM T
WHERE (TNo IN ( SELECT TNo
FROM TC
WHERE CNo = 'C5'))

（3）使用ALL

ALL的含义为全部。

[例3-57] 查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。

SELECT TN, Sal
FROM T
WHERE (Sal > ALL ( SELECT Sal
FROM T
WHERE Dept = '计算机'))
AND (Dept <> '计算机')

子查询找到计算机系中所有教师的工资集合(1500，900)，父查询找到所有不是计算机系且工资高于1500的教师姓名和工资。

查询结果如下：

此查询也可以写成：

SELECT TN, Sal
FROM T
WHERE (Sal > ( SELECT MAX(Sal)
FROM T
WHERE Dept = '计算机'))
AND (Dept <> '计算机')

库函数MAX的作用是找到计算机系中所有教师的最高工资。

[例3-58] 查询不讲授课程号为C5的教师姓名。

SELECT DISTINCT TN
FROM T
WHERE ('C5' <> ALL ( SELECT CNo
FROM TC
WHERE TNo = T.TNo))

<>ALL的含义为不等于子查询结果中的任何一个值，也可使用NOT IN代替<>ALL。

子查询包含普通子查询和相关子查询。前面所讲的子查询均为普通子查询，而本例中子查询的查询条件引用了父查询表中的属性值（T表的TNo值），我们把这类查询称为相关子查询。

二者的执行方式不同。

普通子查询的执行顺序是：首先执行子查询，然后把子查询的结果作为父查询的查询条件的值。普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较以确定查询结果集合。

相关子查询的执行顺序是：首先选取父查询表中的第一行记录，内部的子查询利用此行中相关的属性值进行查询，然后父查询根据子查询返回的结果判断此行是否满足查询条件。如果满足条件，则把该行放入父查询的查询结果集合中。重复执行这一过程，直到处理完父查询表中的每一行数据。

由此可以看出，相关子查询的执行次数是由父查询表的行数决定的。

如例3-58表T中的每一行即每个教师记录都要执行一次子查询，以确定该教师是否讲授C5这门课，当C5不是教师教授的一门课时，则该教师被选取。

以下几例均为相关子查询的例子。

（4）使用EXISTS
EXISTS是表示存在的量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOT EXISTS与此相反。
含有IN的查询通常可用EXISTS表示，但反过来不一定。

[例3-59] 用含有EXISTS的语句完成例3-54的查询，即查询讲授课程号为C5的教师姓名。

SELECT TN
FROM T
WHERE EXISTS ( SELECT *
FROM TC
WHERE TNo = T.TNo AND CNo = 'C5')

当子查询TC表存在一行记录满足其WHERE子句中的条件时，父查询便得到一个TN值，重复执行以上过程，直到得出最后结果。

[例3-60] 查询没有讲授课程号为C5的教师姓名。

SELECT TN
FROM T
WHERE (NOT EXISTS ( SELECT *
FROM TC
WHERE TNo = T.TNo AND CNo = 'C5'))

当子查询TC表存在一行记录不满足其WHERE子句中的条件时，父查询便得到一个TN值，重复执行以上过程，最后便选出了这样一些教师名单，在TC表中不存在他们讲授C5课程的记录。

[例3-61] 查询选修所有课程的学生姓名。

SELECT SN
FROM S
WHERE (NOT EXISTS ( SELECT
FROM C
WHERE NOT EXISTS ( SELECT
FROM SC
WHERE SNo = S.SNo
AND CNo = C.CNo)))

本例题也可理解为：选出这样一些学生名单，在SC表中不存在他们没有选修课程的记录。

3.6.8 合并查询

合并查询是使用UNION操作符将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。UNION操作会自动将重复的数据行剔除。必须注意的是，参加合并查询的各子查询的使用的表结构应该相同，即各子查询中的数据数目和对应的数据类型都必须相同。

[例3-62] 从SC数据表中查询出学号为“S1”同学的学号和总分，再从SC数据表中查询出学号为“S5”的同学的学号和总分，然后将两个查询结果合并成一个结果集。

SELECT SNo AS 学号, SUM(Score) AS 总分
FROM SC
WHERE (SNo = 'S1')
GROUP BY SNo
UNION
SELECT SNo AS 学号, SUM(Score) AS 总分
FROM SC
WHERE (SNo = 'S5')
GROUP BY SNo

3.6.9 存储查询结果到表中

使用SELECT…INTO 语句可以将查询结果存储到一个新建的数据库表或临时表中。

[例3-63] 从SC数据表中查询出所有同学的学号和总分，并将查询结果存放到一个新的数据表Cal_Table中。

SELECT SNo AS 学号, SUM(Score) AS 总分
INTO Cal_Table
FROM SC
GROUP BY SNo

如果在本例中，将INTO Cal_Table改为INTO #Cal_Table，则查询的结果被存放到一个临时表中，临时表只存储在内存中，并不存储在数据库中，所以其存在的时间非常短。

3.7 数据操纵

SQL提供的数据操纵语言DML（Data Manipulation Langnage）主要包括修改数据、添加数据和删除数据三类语句。

3.7.1 修改数据

1．用Management Studio修改数据

可以在Management Studio中，在查看数据库表的数据时修改数据，但这种方式不能应付数据的大量修改。

用惯了SQL Server 2000，再用SQL Server 2008 R2来直接修改表数据会很不习惯，因为在Management Studio中默认只能修改前200条数据。如何能显示和修改更多的数据呢？方法是：在Management Studio中，打开“工具→选项”，将“编辑前行”命令的值修改为0，如图3-19所示。单击“确认”按钮后，再看表名的右键弹出菜单，会有一个“编辑所有行”菜单项，如图3-20所示。这样，就可以修改表中的所有数据了。

用Management Studio修改数据的方法是，在“对象资源管理器”中右键单击要修改数据的表，在上图所示的弹出菜单中选择“编辑所有行(E)”命令，即可弹出修改表数据对话框，如图3-21所示。单击要修改的记录，分别向各字段中输入新数据即可，原数据被新数据覆盖。

与早期的SQL Server 2000等版本不同，SQL Server 2008 R2默认禁止修改表结构。当修改表结构并保存时，系统将提示：不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行了更改或者启用了“阻止保存要求重新创建表的更改”选项。

解决上述问题的方法是，单击Management Studio的“工具→选项”菜单项，找到如下的设置项：“表设计器和数据库设计器”，不选中“阻止保存要求重新创建表的更改”选项。单击“确定”按钮即可。

2．用SQL命令修改数据

可以使用SQL的UPDATE语句对表中的一行或多行记录的某些列值进行修改，其语法格式为：

UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>]…
[WHERE <条件>]

其中，<表名>是指要修改的表，SET子句给出要修改的列及其修改后的值。WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。

（1）修改一行

[例3-64] 把刘伟老师转到信息系。

UPDATE T
SET Dept= '信息'
WHERE TN= '刘伟'

（2）修改多行

[例3-65] 将所有学生的年龄增加1岁。

UPDATE S
SET Age=Age+1

[例3-66] 把教师表中工资小于或等于1000元的讲师的工资提高20%。

UPDATE T
SET Sal = 1.2 * Sal
WHERE (Prof = '讲师 ') AND (Sal <= 1000)

（3）用子查询选择要修改的行

[例3-67] 把讲授C5课程的教师的岗位津贴增加100元。

UPDATE T
SET Comm = Comm + 100
WHERE (TNo IN ( SELECT TNo
FROM T, TC
WHERE T.TNo = TC.TNo AND TC.CNo = 'C5'))

子查询的作用是得到讲授C5课程的教师号。

（4）用子查询提供要修改的值

[例3-68] 把所有教师的工资提高到平均工资的1.2倍。

UPDATE T
SET Sal = ( SELECT 1.2 * AVG(Sal)
FROM T)

子查询的作用是得到所有教师的平均工资的1.2倍。

3.7.2 添加数据

添加数据是把新的记录添加到一个已存在的表中。

1．用Management Studio添加数据

可以在Management Studio中，查看数据库表的数据时添加数据，但这种方式不能应付数据的大量添加。

添加数据的方法是：打开待添加数据记录的数据表，在图3-21所示的窗口中，单击最后的空白行，分别向各字段中输入新数据即可。当输入一个新记录的数据后，会自动在最后出现一新的空白行，用户可以继续输入多个数据记录。

2．用SQL命令添加数据

添加数据使用的SQL命令是INSERT INTO，可分为以下几种情况。

（1）添加一行新记录
添加一行新记录的语法格式为：

INSERT INTO <表名>[(<列名1>[,<列名2>…])] VALUES(<值>)

其中，<表名>是指要添加新记录的表，<列名>是可选项，指定待添加数据的列，VALUES子句指定待添加数据的具体值。列名的排列顺序不一定要和表定义时的顺序一致，但当指定列名时，VALUES子句中值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。

[例3-69] 在S表中添加一条学生记录（学号：S7、姓名：郑冬、性别：女、年龄：21、系别：计算机）。

INSERT INTO S (SNo, SN, Age, Sex, Dept)
VALUES ('S7', '郑冬', 21, '女', '计算机')

（2）添加一行记录的部分数据值

[例3-70] 在SC表中添加一条选课记录('S7', 'C1')。

INSERT INTO SC (SNo, CNo)
VALUES ('S7', 'C1')

将VALUES子句中的值按照INTO子句中指定列名的顺序添加到表中，对于INTO子句中没有出现的列，则新添加的记录在这些列上将赋NULL值，如上例的Score即赋NULL值。但在表定义时有NOT NULL约束的属性列不能取NULL值，插入时必须给其赋值。

3．添加多行记录

添加多行记录用于表间的复制，即将一个表中的数据抽取数行添加到另一个表中，可以通过子查询来实现。

添加数据的命令语法格式为：

INSERT INTO <表名> [(<列名1>[,<列名2>…])]

子查询

[例3-71] 求出各系教师的平均工资，把结果存放在新表AvgSal中。

首先建立新表AvgSal，用来存放系名和各系的平均工资。

CREATE TABLE AvgSal
( Department VARCHAR(20),
Average SMALLINT)

然后利用子查询求出T表中各系的平均工资，把结果存放在新表AvgSal中。

INSERT INTO AvgSal
SELECT Dept,AVG(Sal)
FROM T
GROUP BY Dept

3.7.3 删除数据

1．用Management Studio删除数据

可以在Management Studio中，在查看数据库表的数据时删除数据，这种方式适合于删除少量记录等简单情况。

删除数据的方法是：打开待删除记录的数据表，在如图3-21所示的窗口中，单击记录左侧的小方块，此时该记录呈蓝色，表示已选择了该条记录。也可以在记录左侧的区域内上、下拖动鼠标指针来选择多个记录，如图3-23所示。此时，单击右键从弹出菜单中选择“删除”，或者按下Del键或Delete键，系统会弹出删除提示对话框，如图3-24所示，如果用户单击“是”按钮，则记录被删除，如果用户单击“否”按钮，则记录将不被删除。

2．用SQL命令删除数据

使用SQL的DELETE语句可以删除表中的一行或多行记录，其语法格式为：

DELETE
FROM<表名>
[WHERE <条件>]

其中，<表名>是指要删除数据的表。WHERE子句指定待删除的记录应当满足的条件，WHERE子句省略时，则删除表中的所有记录。

（1）删除一行记录

[例3-72] 删除刘伟老师的记录。

DELETE
FROM T
WHERE TN= '刘伟'

（2）删除多行记录

[例3-73] 删除所有教师的授课记录。

DELETE
FROM TC

执行此语句后，TC表即为一个空表，但其定义仍存在数据字典中。

（3）利用子查询选择要删除的行

[例3-74] 删除刘伟老师授课的记录。

DELETE
FROM TC
WHERE (TNo = ( SELECT TNo
FROM T
WHERE TN = '刘伟'))

3.8 视 图

视图是一个虚拟表，其内容由查询定义。同基本表一样，视图包含一系列带有名称的列和行数据。视图在数据库中并不是以数据值存储集形式存在，除非是索引视图。行和列数据来自定义视图的查询所引用的基本表，并且在引用视图时动态生成。

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。分布式查询也可用于定义使用多个异类源数据的视图。例如，如果有多台不同的服务器分别存储单位在不同地区的数据，而需要将这些服务器上结构相似的数据组合起来，这种方式就很有用。

视图通常用来集中、简化和自定义每个用户对数据库的不同认识。视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图关联的基础表权限。视图可用于提供向后兼容接口来模拟曾经存在但其架构已更改的基础表。还可以在向SQL Server复制数据和从其中复制数据时使用视图，以便提高性能并对数据进行分区。

3.8.1 创建视图

可以使用Management Studio和SQL命令两种方法来创建视图。

1．用Management Studio创建视图

本节以在AdventureWorks2008R2数据库下创建一个名为“View_PostalCode”的视图为例，讲述如何使用Management Studio来创建视图。

（1）启动“对象资源管理器”，右键单击AdventureWorks2008R2数据库下的“视图”节点，在弹出菜单中选择“新建视图(N)…”如图3-25所示，会弹出“添加表”对话框，如图3-26所示。
（2）在“添加表”对话框中，从“表”、“视图”按钮、“函数”和“同义词”选项卡中选择在新视图中包含的元素，单击“添加”和“关闭”按钮。本例中选择“Address(Person)表。
（3）在“关系图窗格”中，选择要在新视图中包含的列和其他元素；在“条件窗格”中，选择列的排序和筛选条件。本节中，我们选择AddressID、AddressLine1、AddressLine2、City、StateProvinceID和PostcalCode字段。在PostcalCode行的筛选器中，增加：=’98011’。

（4）单击工具栏中的保存按钮，在“选择名称”对话框中，输入“View_PostalCode”，单击“确定”，这样就创建了一个新的视图。

2．用SQL命令创建视图

可以使用SQL语句CREATE VIEW创建视图，其语法格式为：

CREATE VIEW view_name [ (column [ ,...n ] ) ]
[ WITH < view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]
< view_attribute> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }

各主要参数说明如下。

（1）view_name：视图的名称，必须符合SQL Server的标识符命名规则。
（2）column：视图的列名称。仅在下列情况下需要列名：列是从算术表达式、函数或常量派生的；两个或更多的列可能会具有相同的名称（通常是由于联接的原因）；视图中的某个列的指定名称不同于其派生来源列的名称。
（3）select_statement：定义视图的SELECT语句。该语句可以使用多个表和其他视图。
（4）CHECK OPTION：设置针对视图的所有数据修改语句都必须符合select_statement中规定的条件。
（5）ENCRYPTION：视图是加密的，如果加上这个选项，则无法修改视图。因此，创建视图时需要将脚本保存，否则再也不能修改了。
（6）SCHEMABINDING：和底层引用的表进行定义绑定。加上这个选项的话，则视图引用的表不能随便更改构架（例如列的数据类型），如果需要更改底层表构架，则先DROP或者ALTER在底层表之上绑定的视图。SCHEMABINDIN常用于定义索引视图。
（7）VIEW_METADATA：不设置该选项，返回给客户端的metadata是视图所引用表的metadata。设置了该选项，则返回视图自身的metadata。通俗点说，VIEW_METADATA可以让视图看起来貌似表一样，视图的每一列的定义直接告诉客户端，而不是所引用的底层表列的定义。

[例3-75] 创建一个计算机系教师情况的视图Sub_T。

CREATE VIEW Sub_T
AS SELECT TNo, TN, Prof
FROM T
WHERE Dept = '计算机'

视图名字为Sub_T，省略了视图字段列表。视图由子查询中的三列Tno、TN和Prof组成。视图创建后，对视图Sub_T的数据的访问只限制在“计算机系”内，且只能访问TNo、TN和Prof三列的内容，从而达到了数据保密的目的。

视图创建后，只在数据字典中存放视图的定义，而其中的子查询SELECT语句并不执行。只有当用户对视图进行操作时，才按照视图的定义将数据从基本表中取出。

[例3-76] 创建一学生情况视图S_SC_C（包括学号、姓名、课程名及成绩）。

CREATE VIEW S_SC_C(SNo, SN, CN, Score)
AS SELECT S.SNo, SN, CN, Score
FROM S, C, SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo

此视图由三个表连接得到，在S表和SC表中均存在SNo列，故需指定视图列名。

[例3-77] 创建一学生平均成绩视图S_Avg。

CREATE VIEW S_Avg(SNo, Avg)
AS SELECT SNo, Avg(Score)
FROM SC
GROUP BY SNo

此视图的列名之一Avg为库函数的计算结果，在定义时需指明列名。

3.8.2 修改视图

1．用Management Studio修改视图

（1）打开“对象资源管理器”，展开“数据库→【数据库名称】→视图”节点，右键单击要修改的视图，在弹出菜单中选择“设计”，即可弹出修改视图对话框。
（2）对视图内容进行修改后，单击工具栏中的保存按钮，存盘退出。

2．用SQL命令修改视图

可以使用SQL的ALTER VIEW语句修改视图，其语法格式为：

ALTER VIEW <视图名>[(<视图列表>)]
AS <子查询>

[例3-78] 修改学生情况视图S_SC_C（包括姓名、课程名及成绩）。

ALTER VIEW S_SC_C(SN, CN, Score)
AS SELECT SN, CN, Score
FROM S, C, SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo

3.8.3 删除视图

1．用Management Studio删除视图

（1）打开“对象资源管理器”，展开“数据库→【数据库名称】→视图”节点，右键单击要删除的视图，在弹出菜单中选择“删除”，会弹出“删除对象”对话框，如图3-28所示。
（2）在图3-28的“删除对象”对话框中，单击“显示依赖关系(H)…”，可以看到依赖于该视图的对象和该视图依赖的对象。单击对话框中底部的“确定”按钮，可以删除视图。

2．用SQL命令删除视图

删除视图的语法格式为：

DROP VIEW <视图名>

[例3-79] 删除计算机系教师情况的视图Sub_T。

DROP VIEW Sub_T

视图删除后，只会删除该视图在数据字典中的定义，而与该视图有关的基本表中的数据不会受任何影响，由此视图导出的其他视图的定义不会删除，但已无任何意义。用户应该把这些视图删除。

3.8.4 查询视图

视图定义后，对视图的查询操作如同对基本表的查询操作一样。

[例3-80] 查询视图Sub_T中职称为教授的教师号和姓名。

SELECT TNo, TN
FROM Sub_T
WHERE Prof = '教授'

此查询的执行过程是系统首先从数据字典中找到Sub_T的定义，然后把此定义和用户的查询结合起来，转换成等价的对基本表T的查询，这一转换过程称为视图消解（View Resolution），相当于执行以下查询：

SELECT TNo,TN
FROM T
WHERE Dept = '计算机' AND Prof= '教授'

由上例可以看出，当对一个基本表进行复杂的查询时，可以先对基本表建立一个视图，然后只需对此视图进行查询，这样就不必再键入复杂的查询语句，而将一个复杂的查询转换成一个简单的查询，从而简化了查询操作。

3.8.5 更新视图

由于视图是一张虚表，所以对视图的更新，最终转换成对基本表的更新。其更新操作包括添加、修改和删除数据，其语法格式与对基本表的更新操作一样。

有些更新在理论上是不可能的，有些实现起来比较困难，以下仅考虑可以更新的视图。

1．添加（INSERT）

[例3-81] 向计算机系教师视图Sub_T中添加一条记录（教师号：T6、姓名：李丹、职称：副教授）。

INSERT INTO Sub_T (TNo, TN, Prof)
VALUES ('T6', '李丹', '副教授')

系统在执行此语句时，首先从数据字典中找到Sub_T的定义，然后把此定义和添加操作结合起来，转换成等价的对基本表T的添加。相当于执行以下操作：

INSERT INTO T (TNo, TN, Prof)
VALUES ('T6', '李丹', '副教授')

2．修改（UPDATE）

[例3-82] 将计算机系教师视图Sub_T中刘伟的职称改为“副教授”。

UPDATE Sub_T
SET Prof = '副教授'
WHERE (TN = '刘伟')

转换成对基本表的修改操作：

UPDATE T
SET Prof = '副教授'
WHERE TN = '刘伟' AND Dept = '计算机'

3．删除（DELETE）

[例3-83] 删除计算机系教师视图Sub_T中刘伟老师的记录。

DELETE FROM Sub_T
WHERE TN = '刘伟'

转换成对基本表的删除操作：

DELETE FROM T
WHERE Dept = '计算机' AND TN = '刘伟'

3.9 数 据 控 制

数据库中的数据由多个用户共享，为保证数据库的安全，SQL提供数据控制语言（Data Control Language，DCL）对数据库进行统一的控制管理。

3.9.1 权限与角色

1．权限

在SQL系统中，有两种安全机制。一种是视图机制，当用户通过视图访问数据库时，不能访问此视图外的数据，视图机制提供了一定的安全性。另外一种是权限机制，是实际中主要使用的安全机制。权限机制的基本思想是给用户授予不同类型的权限，在必要时，可以收回授权，使用户能够进行的数据库操作以及所操作的数据限定在指定的范围内，禁止用户超越权限对数据库进行非法的操作，从而保证数据库的安全性。

在数据库中，权限可分为系统权限和对象权限。

系统权限是指数据库用户能够对数据库系统进行某种特定操作的权力，它可由数据库管理员授予其他用户，如创建一个基本表（CREATE TABLE）。

对象权限是指数据库用户在指定的数据库对象上进行某种特定操作的权力，对象权限由创建基本表、视图等数据库对象的用户授予其他用户，如查询（SELECT）、添加（INSERT）、修改（UPDATE）和删除（DELETE）等操作。

2．角色

角色是多种权限的集合，可以把角色授予用户或其他角色。当要为某一用户同时授予或收回多项权限时，则可以把这些权限定义为一个角色，对此角色进行操作。这样就避免了许多重复性的工作，简化了数据库用户的权限管理工作。

3.9.2 系统权限与角色的授予与收回

1．系统权限与角色的授予

使用SQL的GRANT语句为用户授予系统权限，其语法格式为：

GRANT <系统权限>|<角色> [,<系统权限>|<角色>]…

TO <用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

[WITH ADMIN OPTION]

其语义为：将指定的系统权限授予指定的用户或角色。其中，PUBLIC代表数据库中的全部用户。WITH ADMIN OPTION为可选项，指定后则允许被授权的用户将指定的系统特权或角色再授予其他用户或角色。

[例3-84] 为用户ZhangSan授予CREATE TABLE的系统权限。

GRANT CREATE TABLE

TO ZhangSan

2．系统权限与角色的收回

数据库管理员可以使用SQL的REVOKE语句收回系统权限，其语法格式为：

REVOKE <系统权限>|<角色> [,<系统权限>|<角色>]…

FROM <用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

[例3-85] 收回用户ZhangSan所拥有的CREATE TABLE的系统权限。

REVOKE CREATE TABLE

FROM ZhangSan

3.9.3 对象权限与角色的授予与收回

1．对象权限与角色的授予

数据库管理员拥有系统权限，而作为数据库的普通用户，只对自己创建的基本表、视图等数据库对象拥有对象权限。如果要共享其他的数据库对象，则必须授予普通用户一定的对象权限。同系统权限的授予方法类似，SQL使用GRANT语句为用户授予对象权限，其语法格式为：

GRANT ALL|<对象权限>[(列名[,列名]…)][,<对象权限>]…
ON <对象名>
TO <用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…
[WITH GRANT OPTION]

其语义为：将指定的操作对象的对象权限授予指定的用户或角色。其中，ALL代表所有的对象权限。列名用于指定要授权的数据库对象的一列或多列。如果不指定列名，被授权的用户将在数据库对象的所有列上均拥有指定的特权。实际上，只有当授予INSERT和UPDATE权限时才需指定列名。ON子句用于指定要授予对象权限的数据库对象名，可以是基本表名、视图名等。WITH GRANT OPTION为可选项，指定后则允许被授权的用户将权限再授予其他用户或角色。

[例3-86] 将对S表和T表的所有对象权限授予User1和User2。

GRANT ALL
ON S,T
TO User1,User2

[例3-87] 将对C表的查询权限授予所有用户。

GRANT SELECT
ON C
TO PUBLIC

[例3-88] 将查询T表和修改教师职称的权限授予User3，并允许将此权限授予其他用户。

GRANT SELECT,UPDATE(Prof)
ON T
TO User3
WITH GRANT OPTION

User3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，User3将此权限授予User4：

GRANT SELECT,UPDATE(Prof)
ON T
TO User4

2．对象权限与角色的收回

所有授予出去的权限在必要时都可以由数据库管理员和授权者收回，收回对象权限仍然使用REVOKE语句，其语法格式为：

REVOKE <对象权限>|<角色> [,<对象权限>|<角色>]…
FROM <用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

[例3-89] 收回用户User1对C表的查询权限。

REVOKE SELECT
ON C
FROM User1

[例3-90] 收回用户User3查询T表和修改教师职称的权限。

REVOKE SELECT,UPDATE(Prof)
ON T
FROM User3

在例3-88中，User3将对T表的权限授予了User4，在收回User3对T表的权限的同时，系统会自动收回User4对T表的权限。

3.10 小 结

本章详细介绍了SQL的使用方法。在讲解SQL的同时，进一步介绍了关系数据库的有关概念，如索引和视图的概念及其作用。SQL具有数据定义、数据查询、数据操纵和数据控制四大功能。其全部功能可以用表3-5所示的9个动词概括出来。

其中，数据查询功能最为丰富和复杂，也非常重要，初学者掌握起来有一定的困难，应反复上机加强练习。