《SQL Server 2005案例教程》课件

SQLServer2005案例教程数计系目录第1章SQLServer2005使用基础第2章数据库创建与管理第3章表的设计与实现第4章操作数据库数据第5章检索数据库数据第6章索引与视图第7章Transact-SQL编程第8章管理事务和锁第9章存储过程和触发器第10章安全性管理2／159第1章SQLServer2005使用基础1.1数据库技术概述1.2SQLServer2005概述1.3SQLServer2005主要组件1.4SQLServer服务器管理3／1591.1数据库技术概述1.1.1数据库数据库是为特定目的而组织和表示的信息、表以及其他对象的集合。数据库中的数据是结构化的，没有不必要的冗余，并为多种应用提供服务；数据的存储独立于使用数据的应用程序；对数据库插入新数据，修改和检索原有数据都能够按一种可控制的方式进行；数据库可以用于搜索、排序以及重新组合数据等目的。数据库中的数据分为系统数据和用户数据两类。数据库可以包含各种类型的对象。第1章目录4／1591.1数据库技术概述1.1.2关系型数据库数据库支持的数据模型主要分为3种类型，即层次模型、网状模型和关系模型。目前以关系模型应用最为广泛。支持关系模型的数据库通常也称为关系型数据库。关系型数据库在表中以数据行和数据列的形式存储信息，并通过使用一个表的指定列中的数据在另一个表中查找其他数据来执行搜索。在关系型数据库中，数据是以行和列的形式存储的，这些行和列组成了表，一组表和其他对象一起组成了数据库。在关系型数据库中，数据分别存储在不同的表中。每个表包含某个特定主题的数据。表中的一列通常也称为字段，每个字段用于存储某种特性的数据。表中的一行通常也称为一条记录，每条记录包含表中一项的相关信息。第1章目录5／1591.1数据库技术概述1.1.3数据库管理系统数据库管理系统是对数据库进行管理的系统软件，它提供了用户与数据库之间的软件界面，可以用于创建、管理和维护数据库。数据库管理系统通常具有以下功能。数据库定义功能。数据库操作功能。数据控制功能。数据安全控制数据完整性控制数据库恢复数据库并发控制第1章目录6／1591.2SQLServer2005概述1.2.1SQLServer2005的版本SQLServer2005企业版（32位和64位）SQLServer2005标准版（32位和64位）SQLServer2005工作组版（仅适用于32位）SQLServer2005开发人员版（32位和64位）SQLServer2005免费版（仅适用于32位）1.2.2SQLServer2005的新增功能NotificationServices增强功能ReportingServices增强功能新增的ServiceBroker数据库引擎增强功能数据访问接口方面的增强功能AnalysisServices的增强功能IntegrationServices的增强功能复制增强工具和实用工具增强功能第1章目录7／1591.2SQLServer2005概述1.2.3SQLServer2005的安装使用SQLServer2005安装向导可以来安装所有SQLServer组件：包括数据库引擎、分析服务、报表服务、集成服务、管理工具以及文档和教程。选择要安装的组件设置实例名称和服务帐户设置身份验证模式和设置排序规则设置报表服务器安装选项1.2.4验证SQLServer安装“开始”菜单中的MicrosoftSQLServer2005程序组SQLServer系统数据库和示例数据库SQLServer服务的相关信息第1章目录8／1591.3SQLServer2005主要组件1.3.1SQLServer服务器组件数据库引擎分析服务报表服务集成服务1.3.2SQLServerManagementStudio集成环境，用于访问、配置、控制、管理和开发SQLServer的所有组件。常用窗口已注册的服务器，对象资源管理器，查询编辑器，查询结果，模板资源管理器，属性窗口，解决方案资源管理器 SQLServerManagementStudio常用功能。第1章目录9／1591.3SQLServer2005主要组件1.3.3SQLServer配置管理器SQLServer配置管理器用于管理与SQLServer相关联的服务、配置SQLServer使用的网络协议以及从SQLServer客户端计算机管理网络连接配置。SQLServer配置管理器是一个Microsoft管理控制台管理单元，可以从“开始”菜单进行访问，也可以将其添加到其他任何Microsoft管理控制台显示中。1.3.4SQLServerProfilerSQLServerProfiler是用于从服务器捕获SQLServer2005事件的工具。事件保存在一个跟踪文件中，可在以后对该文件进行分析，也可以在试图诊断某个问题时，用它来重播某一系列的步骤。1.3.5数据库引擎优化顾问数据库引擎优化顾问用于分析在一个或多个数据库中运行的工作负荷的性能效果。工作负荷是对要优化的数据库执行的一组Transact-SQL语句。分析数据库的工作负荷效果后，数据库引擎优化顾问会提供在SQLServer数据库中添加、删除或修改物理设计结构的建议。第1章目录10／1591.3SQLServer2005主要组件1.3.6BusinessIntelligenceDevelopmentStudioBusinessIntelligenceDevelopmentStudio是包含了专用于SQLServer商业智能的其他项目类型的MicrosoftVisualStudio2005也是用于开发商业解决方案的主要环境，其中包括AnalysisServices、IntegrationServices和ReportingServices项目。1.3.7命令提示实用工具SQLServer2005还提供了一组命令提示实用工具。例如bcp实用工具、dta实用工具。第1章目录11／1591.4SQLServer服务器管理1.4.1管理服务器注册服务器基于服务器创建查询将对象资源管理器连接到服务器编辑服务器的注册信息导出已注册的服务器信息导出更改服务器的状态删除服务器的注册信息1.4.2管理服务器组创建服务器组编辑服务器组的属性服务器组中注册新服务器将注册服务器移动到服务器组中向服务器组中导入注册服务器删除服务器组第1章目录12／159第2章数据库创建与管理2.1数据库概述2.2创建数据库 2.3修改数据库2.4备份和还原数据库13／1592.1数据库概述第2章目录2.1.1数据库基本概念SQLServer数据库由表的集合组成。表上有几种类型的控制，例如约束、触发器、默认值和自定义用户数据类型，用于保证数据的有效性。一个SQLServer实例可以支持多个数据库。每个数据库可以存储来自其他数据库的相关数据或不相关数据。在SQLServer中，数据库分为两种类型：系统数据库和用户数据库。2.1.2文件与文件组1.数据库文件主要数据库文件、次要数据库文件和事务日志文件。2.文件组每个数据库有一个主要文件组。如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。使用文件和文件组时需要考虑的因素。14／1592.1数据库概述第2章目录2.1.3事务日志事务日志用于记录所有事务以及每个事务对数据库所做的修改。事务日志是数据库的重要组件，如果系统出现故障，则可能需要使用事务日志将数据库恢复到一致状态。事务日志支持的操作。SQLServer数据库引擎事务日志的特征。2.1.4系统数据库mastermsdbmodelResourcetempdbdistribution15／1592.1数据库概述第2章目录2.1.5数据库状态和文件状态1.数据库状态ONLINEOFFLINERESTORINGRECOVERINGRECOVERYPENDINGSUSPECTEMERGENCY2.文件状态ONLINEOFFLINERESTORINGRECOVERYPENDING16／1592.2创建数据库第2章目录2.2.1使用SSMS创建数据库创建数据库，就是确定数据库的名称、所有者、大小、增长方式以及存储该数据库的文件和文件组等信息的过程。在一个SQLServer实例中，最多可以创建32767个数据库。创建数据库时，model数据库中的所有用户定义对象都将复制到所有新创建的数据库中。使用SQLServerManagementStudio创建数据库。2.2.2使用SQL语句创建数据库CREATEDATABASE语法格式Transact-SQL语法约定CREATEDATABASE中的组成部分【案例2.1】使用CREATEDATABASE语句创建数据库17／1592.3修改数据库第2章目录2.3.1设置数据库选项自动选项AUTO_CLOSEAUTO_CREATE_STATISTICSAUTO_UPDATE_STATISTICSAUTO_SHRINK游标选项CURSOR_CLOSE_ON_COMMITCURSOR_DEFAULT数据库可用性选项OFFLINE|ONLINE|EMERGENCYREAD_ONLY|READ_WRITESINGLE_USER|RESTRICTED_USER|MULTI_USER日期相关性优化选项DATE_CORRELATION_OPTIMIZATION外部访问选项参数化选项恢复选项ServiceBroker选项快照隔离选项SQL选项设置数据库选项ALTERDATABASEsp_configure【案例2.3】将MyDB数据库的恢复模式由完全模式更改为简单模式。18／1592.3修改数据库第2章目录2.3.2扩展数据库默认情况下，SQLServer根据创建数据库时指定的增长参数自动扩展数据库。通过为现有数据库文件分配更多的空间或在创建新文件，还可以通过手动方式来扩展数据库。如果现有的文件已满，则可能需要扩展数据或事务日志的空间。如果数据库已经用完分配给它的空间且不能自动增长，则会出现错误。增加数据库的大小使用SQLServerManagementStudioALTERDATABASE语句【案例2.4】19／1592.3修改数据库第2章目录2.3.3收缩数据库1.自动收缩数据库2.使用DBCC

SHRINKDATABASE收缩数据库3.使用DBCC

SHRINKFILE收缩数据库4.使用SQLServerManagementStudio收缩文件2.3.4扩展事务日志当一个数据库增长或数据库修改活动增加时，可能需要对事务日志进行扩展。如果事务日志使用完了空间，SQLServer便不能记录事务，在这种情况下不允许对数据进行修改。事务日志进行扩展使用SQLServerManagementStudioALTERDATABASE语句【案例2.5】20／1592.3修改数据库第2章目录2.3.5创建文件组文件组是在数据库中对文件进行分组的一种管理机制。文件组不能独立于数据库文件创建。在首次创建数据库或者以后将更多文件添加到数据库时，可以创建文件组。但是，一旦将文件添加到数据库，就不可能再将这些文件移到其他文件组。一个文件不能是多个文件组的成员。文件组只能包含数据文件。事务日志文件不能是文件组的一部分。创建文件组使用SQLServerManagementStudio对象资源管理器ALTERDATABASE语句【案例2.6】21／1592.3修改数据库第2章目录2.3.6分离和附加数据库1.分离数据库使用对象资源管理器使用sp_detach_db2.附加数据库使用对象资源管理器使用CREATEDATABASE语句【案例2.7】2.3.7重命名数据库使用对象资源管理器ALTERDATABASE【案例2.8】使用ALTERDATABASE语句重命名数据库22／1592.3修改数据库第2章目录2.3.8更改数据库所有者sp_changedbowner'login',remap_alias_flag2.3.9删除数据库使用对象资源管理器使用DROPDATABASE23／1592.4备份和还原数据库第2章目录2.4.1备份数据库使用对象资源管理器使用BACKUPDATABASE【案例2.9】2.4.2还原数据库使用对象资源管理器RESTOREDATABASE24／159第3章表的设计与实现3.1表的设计

3.2数据类型3.3创建和修改表25／1593.1表的设计第3章目录3.1.1制订表规划确定表的下列特征表要存储什么对象表中每一列的数据类型和长度表中哪些列允许空值是否要使用以及在何处使用约束、默认值和规则使用何种索引以及在何处使用索引哪些列是主键或外键绘制数据库模型图26／1593.1表的设计第3章目录3.1.2规范化逻辑设计规范化的好处和缺点数据库设计规则表应该有一个标识符表应只存储单一类型实体的数据表应避免可为空的列表不应有重复的值或列3.1.3联机事务处理与决策支持数据库应用程序分为两种主要类型联机事务处理（OLTP）注意事项联机分析处理（OLAP）注意事项27／1593.1表的设计第3章目录3.1.4表的类型标准表已分区表临时表本地临时表全局得时表系统表28／1593.1表的设计第3章目录3.1.4表的类型标准表已分区表临时表本地临时表全局得时表系统表29／1593.2数据类型第3章目录3.2.1数据类型概述SQLServer2005提供了28种系统数据类型，分为以下类别。数字数据类型日期和时间数据类型字符串数据类型其他数据类型3.2.2数字数据类型整数类型bigint、int、smallint、tinyint和bit小数类型float(n)和real、decimal(p,s)和numeric(p,s)以及money和smallmoney30／1593.2数据类型第3章目录3.2.3字符串数据类型普通字符串char(n)、varchar(n)和textUnicode字符串nchar(n)、nvarchar(n)和ntext二进制数据类型binary(n)、varbinary(n)和image3.2.4日期和时间数据类型datetimesmalldatetime日期数据的输入格式时间数据的输入格式31／1593.2数据类型第3章目录3.2.5其他数据类型cursorsql_varianttabletimestampuniqueidetifierxml3.2.6别名数据类型什么是别名数据类型创建别名数据类型CREATETYPE在对象资源管理器中创建删除别名数据类型DROPTYPE在对象资源管理器中删除32／1593.3创建和修改表第3章目录3.3.1创建表使用表设计器创建表使用CREATETABLE语句创建表【案例3.1】创建StudentInfo数据库，创建别名数据类型class_number，创建表Class。3.3.2添加和删除列使用表设计器使用ALTERTABLE语句【案例3.2】创建表Course，添加列。33／1593.3创建和修改表第3章目录3.3.3修改列的属性表中的每一列都有一组属性，例如名称、数据类型、为空性以及数据长度等。列的所有属性构成表中列的定义。创建表之后，还可以根据需要对列的属性进行修改。设置列属性使用表设计器使用ALTERTABLE语句【案例3.3】修改Course表。3.3.4创建和修改标识符列使用IDENTITY属性实现标识符列。使用ROWGUIDCOL属性定义GUID列注意事项【案例3.4】将Course表中CourseID列设置为该表的标识符列。34／1593.3创建和修改表第3章目录3.3.5创建和修改主键什么是主键创建主键在创建表时可以创建单个PRIMARYKEY约束作为表定义的一部分。如果表已存在，并且没有PRIMARYKEY约束，则可以在该表中添加PRIMARYKEY约束。当使用表设计器创建或修改表时创建PRIMARYKEY约束使用CREATETABLE语句在创建新表时设置主键使用带有ADD子句的ALTERTABLE语句为现有表设置主键【案例3.5】创建表CourseAssignation，主键是由3个列组成。【案例3.6】将Class表中的ClassID列设置为该表的主键，将Course表中CourseID列设置为该表的标识符列兼用作主键。35／1593.3创建和修改表第3章目录3.3.6创建和修改惟一约束惟一约束即UNIQUE约束。通过创建惟一约束可以确保在非主键列中不输入重复的值。UNIQUE约束和PRIMARYKEY约束的比较创建惟一约束创建表时，可以创建UNIQUE约束作为表定义的一部分。如果表已经存在，可以添加UNIQUE约束（假设组成UNIQUE约束的列或列组合仅包含惟一的值）。使用CREATETABLE语句创建表时创建UNIQUE约束使用带有ADD子句的ALTERTABLE语句在现有表中创建UNIQUE约束使用ALTERTABLE还可以从表中删除UNIQUE约束【案例3.7】在Course表的CourseName列上创建UNIQUE约束。36／1593.3创建和修改表第3章目录3.3.7创建和修改检查约束检查约束即CHECK约束。检查约束通过限制列可接受的值以强制域的完整性。CHECK约束通过不基于其他列中的数据的逻辑表达式确定有效值。创建表时可以创建CHECK约束作为表定义的一部分。如果表已经存在，则可以添加CHECK约束。使用表设计器创建或修改表时，可以在表中创建CHECK约束或从表中删除CHECK约束。使用CREATETABLE语句创建表时创建CHECK约束使用带有ADD子句的ALTERTABLE语句在现有的表中创建CHECK约束【案例3.8】创建Student表，创建创建CHECK约束。37／1593.3创建和修改表第3章目录3.3.8创建和修改列的默认值在数据库中，记录中的每列均必须有值，即使该值是NULL。在实际应用中可能会有这种情况：必须向表中加载一行数据但不知道某一列的值，或该值尚不存在。如果列允许空值，就可以为行加载空值。由于可能不希望有可为空的列，因此最好是为列定义DEFAULT定义（如果合适）。创建表时，可以创建DEFAULT定义作为表定义的一部分。如果某个表已经存在，则可以为其添加DEFAULT定义。表中的每一列都可以包含一个DEFAULT定义。如果某个DEFAULT定义已经存在，则可以修改或删除该定义。【案例3.9】创建Teacher表，设置DEFAULT定义。38／1593.3创建和修改表第3章目录3.3.9创建和修改外键外键（FK）是用于建立和加强两个表数据之间的链接的一列或多列。在外键引用中，当A表的列引用作为B表的主键值的列时，便在两表之间创建了链接，该列就成为A表的外键。创建外键当创建或修改表时可以通过定义FOREIGNKEY约束来创建外键。创建表时，可以创建FOREIGNKEY约束作为表定义的一部分。如果表已经存在，则可以添加FOREIGNKEY约束。【案例3.10】创建名称为Score表，创建外键约束。【案例3.11】在CourseAssignation表中创建外键约束。39／1593.3创建和修改表第3章目录3.3.10创建数据库关系图数据库关系图是数据库中对象的图形表示形式。数据库关系图既可以是整个数据库结构的图片，也可以是部分数据库结构的图片，包括表对象、表中所包含的列以及各个表之间的关系。使用数据库关系图可以创建和修改表、列、关系和键，也可以修改索引和约束。使用对象资源管理器创建数据库关系图【案例3.12】创建数据库关系图。40／1593.3创建和修改表第3章目录3.3.11查看表查看表的定义：使用sp_help系统存储过程。查看表中的数据：使用SELECT语句。获取有关表的信息：使用sys.tables目录视图。获取有关表列的信息：使用sys.columns目录视图。查看表的依赖关系：使用sys.sql_dependencies目录视图。3.3.12重命名表使用对象资源管理器使用sp_rename系统存储过程3.1.13删除表使用对象资源管理器使用DROPTABLE语句使用TRUNCATETABLE语句来截断该表41／159第4章操作数据库数据4.3从表中删除数据4.4导入和导出数据4.1向表中插入数据4.2更新表中的数据42／1594.1向表中插入数据第4章目录4.1.1在结果窗格中插入数据“结果”窗格是查询设计器的一个组成部分，用于显示最近执行的SELECT查询的结果。其他查询类型的结果将在消息框中显示。若要打开“结果”窗格，可打开或创建一个查询或视图，或者返回某个表的数据。如果默认情况下不显示“结果”窗格，可选择“查询设计器”→“窗格”→“结果”命令。打开“结果”窗格后，可以在该窗格中执行以下操作。查看执行的SELECT查询的结果集。对于显示单个表或视图中的数据的查询或视图，可以编辑结果集中各个列的值、添加新行以及删除现有的行。【案例4.1】在SQLServer2005对象资源管理器中，使用“结果”窗格向StudentInfo数据库的Class表中添加一些班级数据。43／1594.1向表中插入数据4.1.2使用INSERT插入数据INSERT[INTO][server_name.database_name.schema_name.|database_name.[schema_name].|schema_name.]

table_name{[(column_list)]

VALUES({DEFAULT|NULL|expression}[,...n])}|DEFAULTVALUES[;]【案例4.2】编写一个脚本文件，使用INSERT语句分别向StudentInfo数据库的Coruse表、Teacher表和CourseAssignation表插入一些数据。第4章目录44／1594.1向表中插入数据4.1.3使用BULKINSERT复制数据BULKINSERT[database_name.[schema_name].|schema_name.][table_name]FROM'data_file'WITH(

FIELDTERMINATOR='field_terminator',

ROWTERMINATOR='row_terminator');【案例4.3】用记事本编写一个用于存储学生信息的数据文件，然后使用BULKINSERT语句将数据文件加载到Student表中。第4章目录45／1594.1向表中插入数据4.1.4使用INSERT...SELECT插入数据在INSERT语句中，可以使用SELECT子查询将一个或多个表或视图中的值添加到另一个表中。使用SELECT子查询可以同时在表中插入多行。子查询的选择列表必须与INSERT语句的列列表匹配。如果没有指定列列表，则选择列表必须与正在其中执行插入操作的表或视图的列匹配。【案例4.4】在StudentInfo数据库中，从Student表中选择学号、从CourseAssignation表中选择课程编号，并将学号和课程编号填写到Score表中。第4章目录46／1594.2更新表中的数据4.2.1在结果窗格中编辑数据在许多情况下，都可以在“结果”窗格中编辑数据，这就像在电子表格中编辑数据一样，操作起来非常方便。若要在“结果”窗格中编辑数据，可执行以下操作。（1）在对象资源管理器中右键单击数据表，然后选择“打开表”命令，这将运行一个SELECT*FROMtable_name查询，在网格中显示该表中的数据。（2）定位到要更改的数据所在的单元格，然后键入新数据。（3）若要保存更改，将鼠标移出该行即可。【案例4.5】使用“结果”窗格在Score表中填写部分记录的Grade列的值。第4章目录47／1594.2更新表中的数据4.2.2使用UPDATE更新数据UPDATE{[server_name.database_name.schema_name.|database_name.[schema_name].|schema_name.]

table_or_view_name}SET{column_name={expression|DEFAULT|NULL}[,...n][FROM{<table_source>}[,...n]][WHERE{<search_condition>}]【案例4.6】使用UPDATE语句对Score表部分记录中的Grade列进行更新。第4章目录48／1594.2更新表中的数据4.2.3使用FROM子句更改数据UPDATEtable_nameSET{column_name={expression|DEFAULT|NULL}[,...n]FROM{<table_source>}[,...n][WHERE{<search_condition>}]【案例4.7】根据学生姓名和课程名称，用UPDATE语句对Score表部分记录中的Grade列进行更新。第4章目录49／1594.2更新表中的数据4.2.4使用TOP限制更新的数据在UPDATE语句中，可以使用TOP子句来限制修改的行数。当在UPDATE语句中使用TOP(n)子句时，将基于随机选择n行来执行更新操作。语法格式如下：UPDATEtable_name[TOP(expression)[PERCENT]]SET{column_name={expression|DEFAULT|NULL}[,...n]FROM{<table_source>}[,...n][WHERE{<search_condition>}]第4章目录50／1594.3从表中删除数据4.3.1在结果窗格中删除数据如果希望删除数据库中的记录，可在“结果”窗格中删除相应的行。如果希望删除所有行，则可以使用“删除”查询。若要在“结果”窗格中删除行，可执行以下操作。（1）在对象资源管理器中，右键单击数据表并选择“打开表”。（2）在“结果”窗格中选中要删除的行左侧的选择框。（3）按Delete键。（4）在要求确认的消息框中单击“是”。第4章目录51／1594.3从表中删除数据4.3.2使用DELETE删除数据DELETE[FROM]{[server_name.database_name.schema_name.|database_name.[schema_name].|schema_name.]

table_or_view_name}[FROM<table_source>[,...n]][WHERE<search_condition>][;]例如:USEStudentInfo;DELETEFROMScoreFROMStudentWHEREStudent.StudentName='左文举'ANDStudent.StudentID=Score.StudentID;第4章目录52／1594.3从表中删除数据4.3.3使用TOP限制删除的行在DELETE语句中，可以使用TOP子句来限制删除的行数。当在DELETE语句中使用TOP(n)子句时，删除操作将基于随机选择n行而执行。例如，下面的语句从PurchaseOrderDetail表中删除了其到期日期早于2002年7月1日的20个随机行。USEAdventureWorks;GODELETETOP(20)FROMPurchasing.PurchaseOrderDetailWHEREDueDate<'20020701';GO第4章目录53／1594.3从表中删除数据4.3.4使用TRUNCATETABLE删除所有行使用TRUNCATETABLE语句可以从表中删除所有行，而不记录单个行删除操作。该语句在功能上与没有WHERE子句的DELETE语句相同；但是，TRUNCATETABLE速度更快，使用的系统资源和事务日志资源更少。TRUNCATETABLE的语法格式如下：TRUNCATETABLE[{database_name.[schema_name].|schema_name.}]

table_name[;]例如，下面的语句用于删除JobCandidate表中的所有数据。USEAdventureWorks;GOTRUNCATETABLEHumanResources.JobCandidate;GO第4章目录54／1594.4

导入和导出数据4.4.1导入数据导入数据是指将外部数据源中的数据复制到SQLServer数据库中。导入数据的整个过程可以在向导的提示下完成，包括选择提供数据的数据源和接受数据的SQLServer目标数据库、指定表复制或查询选项、选择源表和源视图以及设置是否保存SSIS包等。【案例4.8】在SQLServer2005中创建一个名为NW的数据库，然后将Access示例数据库文件Northwind.mdb中的数据导入到NW数据库中。第4章目录55／1594.4

导入和导出数据4.4.2导出数据导出数据是指将存储在SQLServer数据库中的数据复制到其他数据库、电子表格或文本文件中。导出数据的整个操作过程可以在向导提示下完成，主要步骤包括选择提供数据的数据源和接受数据的目标数据库或文件、指定表复制或查询选项、选择源表和源视图以及设置是否保存SSIS包等。下面结合案例说明如何将SQLServer数据库中的数据导出到Access数据库中。【案例4.9】在F:\MSSQL\chapter04目录中创建一个Access数据库并命名为StudentInfo.mdb，然后将SQLServer数据库StudentInfo中的数据导入到这个Access数据库文件中。第4章目录56／159第5章检索数据库数据5.1SELECT语句概述5.2使用SELECT定义选择列表5.3使用FROM指定数据源5.4使用WHERE筛选数据5.5使用ORDERBY对数据排序5.6使用GROUPBY对数据分组5.7操作结果集5.8使用子查询57／1595.1SELECT语句概述5.1.1SELECT语句的组成SELECTselect_listINTOnew_table_nameFROMtable_list[WHEREsearch_conditions[GROUPBYgroup_by_list][HAVINGsearch_conditions][ORDERBYorder_list[ASC|DESC]]第5章目录58／1595.1SELECT语句概述5.1.2查询工具介绍查询编辑器用户界面介绍sqlcmd实用工具命令语法介绍bcp实用工具【案例5.1】使用sqlcmd实用工具执行SELECT语句，以显示StudentInfo数据库的Class表中的数据。第5章目录59／1595.2使用SELECT定义选择列表5.2.1从表中选择所有列在SELECT语句中，使用星号（*）可选择表或视图中的所有列。如果没有使用限定符指定，则星号将被解析为对FROM子句中指定的所有表或视图中的所有列的引用。如果使用表或视图名称进行限定，则星号将被解析为对指定表或视图中的所有列的引用。当在SELECT语句中使用星号时，结果集中的列的顺序与创建表或视图时所指定的列顺序相同。由于SELECT*将查找表中当前存在的所有列，因此每次执行SELECT*语句时，表结构的更改（通过添加、删除或重命名列）都会自动反映出来。【案例5.2】在StudentInfo数据库中，使用SELECT语句检索Course表中的全部数据。第5章目录60／1595.2使用SELECT定义选择列表5.2.2从表中选择特定列若要选择表中的特定列作为SELECT查询的输出列，则应当在选择列表中明确地列出每一列，各列之间用逗号分隔。如果创建表时在表名或列名中使用了空格（不符合标识符命名规则），则编写SELECT语句时需要使用方括号将表名或列名括起来，否则会出现错误信息。如果在FROM子句中指定了多个表，而这些表中又有同名的列，则在使用这些列时需要在列名前面冠以表名，以指明该列属于哪个表。【案例5.3】在StudentInfo数据库中，使用SELECT语句从Student表中检索学生数据，要求结果集中包括学号、姓名、性别和班级编号信息。第5章目录61／1595.2使用SELECT定义选择列表5.2.3从表中选择特殊列对于表中的标识符列，可使用$IDENTITY关键字来引用。对于具有ROWGUIDCOL属性的列，可使用$ROWGUID关键字来引用。【案例5.4】在StudentInfo数据库中，使用SELECT语句从Teacher表中检索教师数据，要求在结果集包含教师编号、姓名、性别和参加工作时间信息。5.2.4设置结果集列的名称column_nameAScolumn_aliasresult_column_expressionASderived_column_namecolumn_alias=column_namederived_column_name=result_column_expression【案例5.5】在StudentInfo数据库中，使用SELECT语句从Class表中检索班级数据，要求使用中文表示结果集的列名。第5章目录62／1595.2使用SELECT定义选择列表5.2.5在选择列表中进行计算在选择列表中，可以包含通过对一个或多个简单表达式应用运算符而生成的表达式。这使结果集中得以包含基表中不存在，但是根据基表中存储的值计算得到的值，这些结果集列被称为派生列。在派生列中，可以对数值列或常量使用算术运算符或函数进行的计算和运算，也可以进行数据类型转换，还可以使用子查询。通过在带有算术运算符、函数、转换或嵌套查询的选择列表中使用数值列或数值常量，可以对数据进行计算和运算。【案例5.6】计算年龄，使用中文表示结果集的列名。第5章目录63／1595.2使用SELECT定义选择列表5.2.6使用DISTINCT消除重复项使用DISTINCT关键字可以从SELECT语句的结果集中消除重复的行。如果没有指定DISTINCT，将返回包括重复行在内的所有行。对于DISTINCT关键字来说，空值将被认为是相互重复的内容。当SELECT语句中包括DISTINCT时，不论遇到多少个空值，结果中只返回一个NULL。【案例5.7】从Score表中检索所有不重复的学号。5.2.7使用TOP限制结果集

TOP(expression)[PERCENT][WITHTIES]【案例5.8】检索前6名学生的信息。第5章目录64／1595.3使用FROM指定数据源5.3.1没有FROM子句的SELECT语句在SELECT语句中，FROM子句是一个可选项。如果要使用SELECT语句从数据库内的表或视图中选择数据，就必须使用FROM子句。但是，如果要从局部变量或者从不对列进行操作的Transact-SQL函数中选择数据，而不是从数据库内的任何表或视图中选择数据，则可以使用没有FROM子句的SELECT语句。【案例5.9】使用SELECT语句显示以下信息：一条欢迎信息、系统当前日期和时间、SQLServer服务器名称以及SQLServer版本号。5.3.2使用内部联接FROMtable1[[AS]table_alias1][INNER]JOINtable2[[AS]table_alias2]ONsearch_condition【案例5.10】从Student表、Course表和Score表中检索学生的课程成绩，要求在结果集中包含学号、姓名、课程名称以及成绩信息。第5章目录65／1595.3使用FROM指定数据源5.3.3使用外部联接FROMtable1[[AS]table_alias1]{LEFT|RIGHT|FULL}[OUTER]JOINtable2[[AS]table_alias2]ONsearch_condition【案例5.11】从Score表和Course表中检索数据，要求列出Course表的所有行。5.3.4使用交叉联接

FROMtable1CROSSJOINtable2【案例5.12】在StudentInfo数据库中，通过交叉联接从Student表和Class表中检索数据。第5章目录66／1595.4使用WHERE筛选数据5.4.1WHERE子句的语法格式

[WHERE<search_condition>]其中<search_condition>定义要返回的行应满足的条件，该条件是用运算符连接列名、常量、变量、函数等而得到的表达式，其取值为TRUE、FALSE或UNKNOWN。通过WHERE子句可以指定一系列搜索条件，只有那些满足搜索条件的行才用于生成结果集，此时称满足搜索条件的行包含在结果集内。5.4.2使用比较搜索条件在SQLServer2005中，可以使用下列比较运算符：=（等于）、>（大于）、<（小于）、>=（大于或等于）、<=（小于或等于）、<>（不等于，SQL-92兼容）、!>（不大于）、!<（不小于）、!=（不等于）。这些运算符是在两个表达式之间进行比较的。【案例5.13】从Teacher表中检索学历为研究生的教师信息，要求在结果集包含教师编号、姓名、性别和学历。第5章目录67／1595.4使用WHERE筛选数据5.4.3使用范围搜索条件test_expression[NOT]BETWEENbegin_expressionANDend_expression【案例5.14】检索成绩在86～93之间的学生课程成绩，要求在结果集包含学号、姓名、课程名称和成绩信息。5.4.4使用列表搜索条件

test_expression[NOT]IN(subquery|expression[,...n])【案例5.15】从Student表中检索张王李赵4个姓氏的学生记录，要求在结果集包含学号、姓名、性别第5章目录68／1595.4使用WHERE筛选数据5.4.5搜索条件中的模式匹配

match_expression[NOT]LIKEpattern[ESCAPEescape_character]【案例5.16】从Student表中检索张王李赵4个姓氏的学生记录，要求使用LIKE运算符实现查询。【案例5.17】从Student表中检索姓名中包含一个“伟”字的学生记录。5.4.6使用逻辑运算符在SQLServer2005中，逻辑运算符包括AND、OR和NOT。AND和OR用于连接WHERE子句中的搜索条件。NOT用于反转搜索条件的结果。AND运算符连接两个条件，只有当两个条件都符合时才返回TRUE。只要有任何一个条件为FALSE，则结果也为FALSE。OR运算符也用于连接两个条件，但只要有一个条件符合便返回TRUE。只有当两个条件均为FALSE时，结果才是FALSE。NOT是单目运算符，用于对条件进行反转。若条件为TRUE，则变成FALSE；若条件为FALSE，则变成TRUE。【案例5.18】从Teacher表中检索政治面貌为党员或团员的女教师记录。第5章目录69／1595.5使用ORDER

BY对数据排序5.5.1ORDERBY子句的语法格式[ORDERBY{order_by_expression[COLLATEcollation_name][ASC|DESC]},...n]]【案例5.19】检索学生的会计电算化课程成绩，要求对结果集中的行按成绩降序排序，若成绩相同，则按姓名升序排序。5.5.2将TOP...WITHTIES与ORDERBY子句联用当TOP与ORDERBY一起使用时，如果要使排序列值相等的那些行一并显示出来，可以在SELECT子句中添加WITHTIES选项。WITHTIES选项指定从基本结果集中返回附加的行，这些行包含与出现在TOPn(PERCENT)行最后的ORDERBY列中的值相同的值。【案例5.20】使并列第3名的记录也包含在结果集内。第5章目录70／1595.6使用GROUP

BY对数据分组5.6.1GROUPBY子句的组件[GROUPBY[ALL]group_by_expression[,...n][WITH{CUBE|ROLLUP}]]【案例5.21】从Student表中检索各班的人数。5.6.2在分组操作中应用搜索条件可以使用HAVING子句对GROUPBY子句设置搜索条件。HAVING语法与WHERE语法类似，两者的区别在于：WHERE搜索条件在进行分组之前应用，而HAVING搜索条件在进行分组之后应用，而且HAVING可以包含聚合函数，也可以引用选择列表中显示的任意项。【案例5.22】查询平均分高于85的男生记录，要求按平均分对结果集中的行降序排序。第5章目录71／1595.6使用GROUP

BY对数据分组5.6.3使用聚合函数汇总数据AVG函数：返回组中各值的平均值COUNT函数：返回组中的项数。GROUPING函数：当行由CUBE或ROLLUP运算符添加时，该函数将导致附加列的输出值为1；当行不由CUBE或ROLLUP运算符添加时，该函数将导致附加列的输出值为0。MAX函数：返回表达式的最大值。MIN函数：返回表达式中的最小值。SUM函数：返回表达式中所有值的和或仅非重复值的和。STDEV函数：返回指定表达式中所有值的标准偏差。STDEVP函数：返回指定表达式中所有值的总体标准偏差。VAR函数：返回指定表达式中所有值的方差。VARP函数：返回指定表达式中所有值的总体方差。【案例5.23】统计每个班学生的总人数、平均分、最高分以及最低分并按照平均分降序排序。第5章目录72／1595.6使用GROUP

BY对数据分组5.6.4使用CUBE和ROLLUP汇总数据使用WITHCUBE汇总数据【案例5.24】统计每个学生、每个班级的平均分以及所有班级的总平均分。使用WITHROLLUP汇总数据【案例5.25】统计每个学生、每个班级的平均分以及所有班级的总平均分。5.6.5使用COMPUTE和COMPUTEBY汇总数据使用COMPUTE汇总数据【案例5.26】使用COMPUTEBY汇总数据COMPUTE{AVG|COUNT|MAX|MIN|STDEV|STDEVP|VAR|VARP|SUM}(expression)[,...n]BYexpression[,...n]【案例5.27】第5章目录73／1595.7

操作结果集5.7.1使用UNION组合结果集

select_statementUNION[ALL]select_statement【案例5.28】从StudentInfo数据库中检索电子工程系的教师和学生信息。5.7.2使用公用表表达式WITHexpression_name[(column_name[,...n])]AS(CTE_query_definition)【案例5.29】从StudentInfo数据库中查询学生课程成绩，要求在结果集中列出任课教师姓名、班级、学生姓名、课程名称和成绩信息。第5章目录74／1595.7

操作结果集5.7.3使用PIVOT运算符[WITH<common_table_expression>]SELECT*FROMtable_sourcePIVOT(

aggregate_function(value_column)FORpivot_columnIN(<column_list>))table_alias【案例5.30】查询计算机科学系学生的课程成绩，要求以学生姓名和3门课程名称作为结果集中的列（交叉表查询）。第5章目录75／1595.7

操作结果集5.7.4将结果集保存到表中

[INTOnew_table]【案例5.31】从StudentInfo数据库中查询计算机科学系的学生，并将结果集保存到一个名为computer_science_department的新表中，然后列出这个新表中的数据。第5章目录76／1595.8使用子查询5.8.1使用子查询进行集成员测试子查询可以通过IN或NOTIN引入，其结果集是包含零个值或多个值的列表。通过IN运算符可以使用子查询进行集成员测试，也就是将一个表达式的值与子查询返回的一列值进行比较，如果该表达式的值与此列中的任何一个值相等，则集成员测试返回TRUE；如果该表达式的值与此列中的所有值都不相等，则集成员测试返回FALSE。使用NOTIN时对集成员测试的结果取反。在集成员测试中，由子查询返回的结果集是单个列值的一个列表，该列必须与测试表达式的数据类型相同。当子查询返回结果之后，外层查询将使用这些结果。【案例5.32】第5章目录77／1595.8使用子查询5.8.3使用子查询进行比较测试通过使用ANY、SOME和ALL对比较运算符进行修改，可以引入子查询并进行批量比较测试。SOME是与ANY等效的SQL-92标准。使用ANY修改比较运算符时，将通过比较运算符对一个表达式的值与子查询返回的一列值中的每一个进行比较。如果在某次比较中运算结果为TURE，则返回TRUE。ANY运算符的语法格式如下：<比较运算符>ANY(subquery)例如，>ANY(子查询)表示至少大于子查询所返回的一个值，也就是大于最小值。例如，>ANY(1,2,3)表示大于1。=ANY则与IN等效。使用ALL修改比较运算符时，将通过比较运算符对一个表达式的值与子查询返回的一列值中的每一个进行比较。如果在每次比较中运算结果均为TRUE，则返回TRUE。只要有一次比较的结果为FALSE，则ALL测试返回FALSE。ALL运算符的语法格式如下：<比较运算符>ALL(subquery)例如，>ALL(子查询)表示大于子查询所返回的每一个值，也就是大于最大值。例如，>ALL(1,2,3)表示大于3。<>ALL与NOTIN作用相同。【案例5.34】第5章目录78／1595.8使用子查询5.8.4使用子查询进行存在性测试当使用EXISTS运算符引入一个子查询时，就相当于进行一次存在性测试。外部查询的WHERE子句测试子查询返回的行是否存在。子查询实际上不产生任何数据，它只返回TRUE或FALSE值。使用EXISTS引入的子查询的语法如下：

WHERE[NOT]EXISTS(subquery)如果在EXISTS前面加上NOT时，将对存在性测试结果取反。【案例5.34】从StudentInfo数据库中查询包含在课程表但尚未包含在课程安排表中的课程的编号和名称。5.8.5使用子查询替代表达式在Transact-SQL中，除了在ORDERBY列表中以外，在SELECT、UPDATE、INSERT和DELETE语句中任何能够使用表达式的地方都可以用子查询替代。【案例5.35】从StudentInfo数据库中查询每个学生的平均分并按平均分降序排序，若平均分相同则按姓名升序排序。第5章目录79／159第6章索引与视图6.1索引概述6.2设计索引6.3实现索引6.4视图概述6.5实现视图6.6管理和应用视图80／1596.1索引概述6.1.1索引的基本概念索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键，这些键存储在一个B树结构中，使SQLServer可以快速有效地查找与键值关联的行。SQLServer数据访问方式扫描表使用索引当在一个列上创建索引时，该列称为索引列或索引键；索引列中的值称为键值。索引键可以是表中的单个列，也可以由多个列的组合而成。一个索引就是一组键值的列表，这些值来自于表中的各个行。键值可以是惟一的，例如选择表中的主键作为索引键时就属于这种情况，但索引键也可以具有重复的值。第6章目录81／1596.1索引概述6.1.2索引的类型聚集索引非聚集索引其他索引类型惟一索引包含性列索引索引视图全文索引XML索引第6章目录82／1596.2设计索引6.2.1索引设计准则设计索引时的数据库准则设计索引时的查询准则设计索引时的列准则。6.2.2设计聚集索引聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。每个表几乎都对列定义聚集索引来实现下列功能：可用于经常使用的查询；提供高度惟一性；可用于范围查询。创建PRIMARYKEY约束时，将在列上自动创建惟一索引。第6章目录83／1596.2设计索引6.2.3设计非聚集索引非聚集索引包含索引键值和指向表数据存储位置的行定位器。通常情况下，设计非聚集索引是为了改善经常使用的、没有建立聚集索引的查询的性能。可以对表或索引视图创建多个非聚集索引。与使用书中索引的方式相似，查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。这使非聚集索引成为完全匹配查询的最佳选择，因为索引包含说明查询所搜索的数据值在表中的精确位置的项。每个索引项都指向表或聚集索引中准确的页和行，其中可以找到相应的数据。在查询优化器在索引中找到所有项之后，它可以直接转到准确的页和行进行数据检索。设计非聚集索引时应注意数据库的特征。第6章目录84／1596.2设计索引6.2.4设计惟一索引惟一索引能够保证索引键中不包含重复的值，从而使表中的每一行从某种方式上具有惟一性。只有当惟一性是数据本身的特征时，指定惟一索引才有意义。使用多列惟一索引，索引能够保证索引键中值的每个组合都是惟一的。例如，若为Score表中的StudentID和CourseID列的组合创建了惟一索引，则表中的任意两行都不会有这些列值的相同组合。惟一索引具有以下优点：能够确保定义的列的数据完整性；提供了对查询优化器有用的附加信息。聚集索引和非聚集索引都可以是惟一的。只要列中的数据是惟一的，就可以为同一个表创建一个惟一聚集索引和多个惟一非聚集索引。创建PRIMARYKEY或UNIQUE约束会自动为指定的列创建惟一索引。第6章目录85／1596.3实现索引6.3.1使用对象资源管理器创建索引（1）在对象资源管理器连接到SQLServer数据库引擎，然后展开该实例。（2）在树目录中展开要在其中创建索引的表，右键单击“索引”并选择“新建索引”。（3）在“新建索引”对话框的“常规”页中指定索引名称，选择索引类型，指定索引是否具有惟一性，添加包含在索引键列中的表列并设置其排序顺序。（4）若要设置索引的选项，可以选择“选项”页并对相关选项进行设置。（5）若要在索引中包含其他表列，可以选择“包含性列”页，然后添加要包含在索引中的一列或多列。（6）单击“确定”按钮。第6章目录86／1596.3实现索引6.3.2使用CREATEINDEX语句创建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON[database_name.[schema_name].|schema_name.]table_or_view_name(column[ASC|DESC][,...n])[INCLUDE(column_name[,...n])][WITH(

PAD_INDEX={ON|OFF}|FILLFACTOR=fillfactor|SORT_IN_TEMPDB={ON|OFF}|IGNORE_DUP_KEY={ON|OFF}|STATISTICS_NORECOMPUTE={ON|OFF}|DROP_EXISTING={ON|OFF}|ONLINE={ON|OFF}|ALLOW_ROW_LOCKS={ON|OFF}|ALLOW_PAGE_LOCKS={ON|OFF}|MAXDOP=max_degree_of_parallelism[,...n])][ON{partition_scheme_name(column_name)|filegroup_name|default}][;]第6章目录87／1596.3实现索引6.3.3查看索引信息使用sp_helpindex查看索引信息使用sp_spaceused查看索引使用的空间使用INDEXPROPERTY函数查看索引属性6.3.4删除索引使用对象资源管理器来删除索引使用DROPINDEX语句从当前数据库中删除一个或多个索引第6章目录88／1596.4视图概述6.4.1视图的基本概念视图是一个虚拟表，其内容由选择查询定义。与真实的表一样，视图也包含一系列带有名称的列和行数据，但这些列和行数据来自由定义视图的查询所引用的表，并且是在引用视图时动态生成的，而不是以数据值存储集形式存在于数据库中（索引视图除外）。视图中引用的表称为基础表。对基础表而言，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，也可以来自其他视图。分布式查询也可以用于定义使用多个异类源数据的视图。视图的类型标准视图索引视图分区视图第6章目录89／1596.4视图概述6.4.2视图的用途和限制视图的用途简化数据操作自定义数据提高数据库的安全性创建视图的注意事项第6章目录90／1596.5

实现视图6.5.1使用对象资源管理器创建视图（1）在对象资源管理器中连接到SQLServer数据库引擎，然后展开该实例。（2）展开要在其中创建视图的数据库，右键单击“视图”节点并选择“新建视图”。（3）当出现如图6.6所示的“添加表”对话框时，选择要在视图中引用的一个或多个基础表，然后单击“添加”。若要在视图中引用已有的视图，可选择“视图”选项卡。选择并添加表或视图后，单击“关闭”。（4）在视图设计器中，通过定义选择列表、设置筛选条件以及指定排序顺序等，生成用于定义视图的SELECT语句。【案例6.4】创建一个视图，用于从Student表、Score表和Course表中获取学生成绩，然后创建一个查询并通过引用该视图来检索080201班所有男生的电工基础成绩。第6章目录91／1596.5

实现视图6.5.2使用CREATEVIEW语句创建视图CREATEVIEW[schema_name.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[;][WITHCHECKOPTION]<view_attribute>::={[ENCRYPTION][SCHEMABINDING][VIEW_METADATA]}【案例6.5】在StudentInfo数据库中创建一个视图，用于检索学生的学号、姓名、性别、入学时间、班级以及系别信息，要求使用中文列名来代替基础表中的英文列名；然后在SELECT语句中引用该视图，以查询电子工程系所有男同学的记录。第6章目录92／1596.6管理和应用视图6.6.1修改视图使用对象资源管理器修改视图使用ALTERVIEW语句修改视图ALTERVIEW[schema_name.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[;][WITHCHECKOPTION]<view_attribute>::={[ENCRYPTION][SCH