数据库原理与技术（第二版） - 万水书苑-出版资源网

全文

(1)第 6 章 SQL Server 数据库系统. 本章学习目标本章主要介绍 SQL Server 的使用和开发。通过本章学习，读者应该掌握以下内容：  了解 SQL Server  SQL Server 管理工具使用方法  SQL Server 中数据库的创建与维护  了解 Transact-SQL 语言  熟悉 SQL Server 中存储过程的使用  熟悉 SQL Server 中触发器的使用  SQL Server 数据导入、导出  SQL Server 的应用系统开发环境. 6.1. SQL Server 管理工具. SQL Server 是 Microsoft 公司在原来和 Sybase 公司合作的基础上打包出的一款面向高端的数据库系统，定位于 Internet 背景下的基于 Windows 的数据库的应用。经过不断的更新换代，已发展到了 SQL Server 2008，具有性能高、功能强、安全性好、易操作、易维护等特点，为用户的 Web 应用提供了一款完善的数据管理和数据分析解决方案。为方便应用，各种数据库都提供管理工具，采用可视化方式提供服务，一般建立数据库、建立表、建立视图、基本查询、存储过程、触发器、简单报表等操作都可以利用其管理工具完成。 SQL Server 2000 的管理工具包括查询分析器、导入和导出数据、服务管理器、服务器网络实用工具、客户端网络实用工具、企业管理器、事件探查器等。SQL Server 2005 的管理工具将企业管理器、查询分析器、数据转换、报表服务等合并为管理控制台 Management Studio，又加入了分析服务、配置工具、性能工具。考虑到易学易入门，本节仍借助 SQL Server 2000 的管理工具进行介绍，如图 6.1 所示。 6.1.1 服务管理器在安装 SQL Server 后，一般在计算机开机后会自动打开 SQL Server 数据库，用户自己建立的应用系统可以直接对 SQL Server 数据库进行操作。可以应用服务管理器（如图 6.2 所示）实现暂停、停止、再启动数据库等操作，其服务包括：distributed transaction coordinator（分布式的事务处理）、Microsoft Search（搜索）、SQL Server（SQL 服务器）、SQL Server agent（SQL 服务代理）等。如果不要求开机时自动启动 SQL Server，可将“当启动 OS 时自动启动服务” 复选框取消勾选。服务器上的箭头为绿色时表示服务器启动，红色表示停止。.

(2) 92. 数据库原理与技术（第二版）. 图 6.1 SQL Server 2000 的管理工具组成. 图 6.2 服务管理器. 6.1.2 建立数据库、表、索引的操作企业管理器以树形结构显示并管理所有 SQL Server 对象，包括数据库、表、视图、存储过程、规则、创建与管理用户账号与角色、用户定义的数据类型与函数、数据转换、服务器启停与配置、备份与恢复、数据库日志、检查数据库一致性、数据统计、安全性管理、分布式事务处理、全文检索等内容。可以利用它进行建库、建表、建立视图、建立存储过程、建立触发器等常规操作。其中有关服务器的操作见图 6.3。在 SQL Server 组下选择服务器并右击，从弹出菜单中选择有关菜单项可以进行服务器注册、属性（内存配置、处理器配置、安全性身份验.

(3) 第6章. SQL Server 数据库系统. 93. 证方法、启动账号、连接特性、服务器设置（语言、行为、SQL 邮件、年份范围）、数据库设置（重建索引、备份与还原期限、故障还原间隔、数据库默认位置））、新建（作业、操作员、数据库）、所有任务（管理 SQL Server 消息、数据导入与导出等）操作，也可以管理具体服务器的断开与停止。. 图 6.3 企业管理器中有关服务器的操作. 1．建立数据库 SQL Server 规定只有系统管理员、数据库建立者和他们所授权的用户才能建立数据库。数据库建立者称为数据库的所有者（DBO），他具有对该数据库及其对象管理的权限。如果要建数据库，在选服务器后，右点击“数据库” ，出现数据库管理界面，如图 6.4 所示。. 图 6.4 数据库管理界面. 在该界面中单击“新建数据库” ，进入数据库属性界面，如图 6.5 所示。.

(4) 94. 数据库原理与技术（第二版）. 图 6.5 数据库属性界面——新建数据库. 在“常规”选项卡中输入数据库名称，名字必须满足 SQL Server 标识符的命名规则：标识符只能由不超过 128 个字符构成，这些字符只能是字母、数字及符号：_、#、@、$。再在 “数据文件” 选项卡中设置有关数据库文件的属性，包括数据库文件在操作系统中的目录位置、初始大小、所属文件组、文件增长属性等。如果建立数据库名为 waremanage，会产生：主数据文件 waremanage_Data.MDF 与日志文件 waremanage_Data.LDF 两个文件（见第 1 章相关内容）。除了这两个文件外，如果主数据文件不能存储全部数据信息，还可以有多个次数据文件，扩展名为 NDF。加“_Data”的文件名为物理文件名，将来在程序中（如 Transact_SQL）这些文件名还有一个逻辑文件名，例如对 waremanage 数据库，主数据文件名为 waremanage.mdf，日志文件名为 waremanagelog.ldf。为了方便管理，SQL Server 将多个数据文件（不包括日志文件）组合在一起，称为数据文件组，它控制每个数据文件的存放位置，由于它的作用，可以将属于一个文件组的文件分布在不同硬盘上，从而减轻单个硬盘驱动器承受的负载，提高工作效率，也便于系统的扩展。在建立数据库之后，系统自动将该数据库的名称、顺序号、建立日期等信息存储在 SQL Server 的系统数据库 master 的 sysdatabases 表中。数据库中可以包含表、视图、存储过程，可以规定用户、角色、规则（见图 6.6）。表即关系，在 SQL Server 中表分为永久表与临时表，包含在数据库中的表为永久表，临时表只存在于内存中，表名以#号打头，当用户退出系统时会自动删除。 2．建立数据表 SQL Server 一个数据库中可以创建约 20 亿个表，每个表可含有 1024 个列（字段），每一.

(5) 第6章. SQL Server 数据库系统. 95. 列中数据均为相同数据类型。利用企业管理器建表的操作方法是打开相应数据库（例如 waremanage），右击“表”，在弹出菜单中选择“新建表”（见图 6.6），进入新表定义对话框，如图 6.7 所示。. 图 6.6 利用企业管理器建立数据表. 在新表定义对话框中可以依次输入列名（字段名）、数据类型、长度、是否允许空值。长度指字段宽度，一般除第 1 章表 1.1 中所列宽度前加*的那些类型外的字段宽度均为定值，无须用户设置。空值（NULL）指未定义的不确定的值，是否允许空值中打勾的列在未来输入数据时，可以不填入具体值。一般关键字字段不允许空值。另外注意，如果设置为允许空值，在查询与修改操作时要作特殊处理，因此尽量不要允许空值。对每一个字段可以进行描述，其内容包括：默认值，指输入时未具体指定数据值，但又不允许空值时自动填入的值；精度与小数位数，对某些允许进行算术运算的二进制数据需要考虑设置。标识，用于设定自动编号的标识列，当用户向表中每增加一新记录时，系统会为该记录生成一个唯一的数值并填入该列，如果选中该复选框，其下“标识种子” 、“标识递增量”将自动设置为 1，用户可以修改这两个数据； “是 RowGuid”：如果在该复选框中打勾，将来每录入一条新记录时会在该记录的 uniqueidentifier 类型字段中自动生成一个 16 位的全局唯一标识符（GUID）。.

(6) 96. 数据库原理与技术（第二版）. 可以为表设定关键字，操作方法是当输入列名后，右击该列名，在弹出菜单中包括设置主键、插入列、删除列、索引/键、CHECK 约束等菜单项。选择“设置主键”，将来表中该字段将不允许出现空值与重复值。. 图 6.7 新表定义对话框. 3．修改表结构与数据维护在建立数据表后，若需要修改表结构，例如增加字段、修改字段名、修改字段属性、修改关于列的描述及建立索引等，可以右击表名，在弹出菜单（见图 6.8）中选择“设计表”，进入表结构修改界面。如果作数据录入、修改、删除操作，可在如图 6.8 所示弹出菜单中选择“打开表→返回所有行”。. 图 6.8 进入表结构修改界面与数据录入、修改、删除界面.

(7) 第6章. SQL Server 数据库系统. 97. 4．建立索引为了加快查询速度，可以给表建立索引，一个表可以建多个索引。在右击列名后弹出的菜单中选择“索引”，出现如图 6.9 所示对话框。. 图 6.9 建立索引. SQL Server 索引分为主索引、唯一索引、普通索引、聚集索引四类。唯一索引要求为之创建索引的列的值在表中具有唯一性，不能有重复值，将来在输入与修改数据时，如果新数据与表中该列已经有的数据相同，操作将不能成功。普通索引则无唯一性要求。如果设置了关键字，将自动建立主索引（又称为主键索引），主索引是唯一标识记录的特殊的唯一索引。建立主索引也即建立了 PRIMARY 约束，它是数据库进行实体完整性保护的依据。聚集索引指对数据表中数据按该索引排序后重新存盘，将来表中数据在维护过程中保持该索引所涉及列的数据排序特性不变。对于一个表只可能有一个聚集索引，如果设计聚集索引，按该索引操作时效率较高，但数据维护效率较低。如果在表上尚未创建聚集索引，且在创建 PRIMARY 约束时未指定非聚集索引，PRIMARY 约束会自动创建聚集索引。在图 6.9 界面中先输入索引名，索引名是今后在程序中调用时使用的名字，当设定后，如果需要修改，下一次进入时，可以先在“选定的索引”下拉列表框中按名字选择欲修改的索引。再选择列名，并选择“升序”或“降序” 。每一索引可以选择多列，如果选择多列，称为复合索引，将来数据处理时会先按第一列顺序处理，在第一列的值相同的情况下再按第二列顺序处理，依此类推，最多可指定 16 列。.

(8) 数据库原理与技术（第二版）. 98. 如果选中“创建 UNIQUE”，则建立唯一索引；如果不选中“创建 UNIQUE”，则建立普通索引。如果选中“创建为 CLUSTERED”，则建立聚集索引。如果建立 PRIMARY 约束，或选中“创建 UNIQUE”的同时选择“约束”，将同时建立同名索引。创建索引时，可以指定一个填充因子，以便在索引项间留出额外的间隙和保留一定百分比的空间，使将来表的数据存储容量进行扩充时对索引文件的维护有较高效率。填充因子的值是从 0～100 的百分比数值，指定为创建索引后的填充比例。值为 100 时表示填满，所留出的存储空间量最小。只有当不会对数据进行更改时才会使用 100%。值越小则对索引文件的维护效率越高，但索引需要更多的存储空间。在图 6.9 界面中，在“选定的索引”下拉列表框中按名字选择索引，单击“删除”按钮可以删除索引。 6.1.3 建立视图的操作 1．建立视图视图是为不同用户提供的不同窗口，可以如同表一样对视图进行操作：查询表中的数据，在一定条件下对表进行数据录入、修改、删除等维护操作。可以在企业管理器支持下建立视图，右击某数据库下“视图” ，在弹出菜单中选择“新建视图”（如图 6.10 所示），将出现如图 6.11 所示“新视图”对话框。. 图 6.10 利用企业管理器建立视图. 在该对话框中，单击工具条中“添加表”按钮，选择视图所基于的基表，再选择将来该视图中包括的数据列，允许使用别名，使视图中的列名与原基表中的列名不相同，在输出列如果不打勾，则不作为 SELECT 后面输出的内容（实现关系投影操作），可以规定按哪些列排序.

(9) 第6章. SQL Server 数据库系统. 99. 及各是升序还是降序。. 图 6.11 “新视图”对话框. 可以规定输出的选择条件（实现关系选择操作）：如果选择条件是多条件的逻辑组合，且条件间是 OR 关系，要求将各条件分别写到“准则”与各个“或”列下；如果是 AND 关系，则要再起一行，将两条件分别写到两行的“准则”下面。列下允许使用表达式，如采用聚集函数（SUM、MAX、MIN、AVG、COUNT），允许规定按什么分组。可以选择多个基表，基表间如果有同名字段，会自动建立联系（见图 6.12）。双击两个基表之间的连线，会弹出属性对话框，如图 6.13 所示，可以改变列间的关系符。另外，在列间关系下面允许增加两个选择：第 1 个表的所有行与第 2 个表的所有行。如果加选第 1 个表的所有行，表示进行左外连接，输出内容除符合连接条件的那些记录之外，还要加上第 1 个表未列进输出记录的那些记录，这些记录在第 2 个表的各列位置中填 NULL 或按默认值填入；如果加选第 2 个表的所有行，表示进行右外连接，输出内容除符合连接条件的那些记录之外，还要加上第 2 个表未列进输出记录的那些记录，这些记录在第 1 个表的各列位置中填 NULL 或按默认值填入；如果两个复选框都选中，表示进行全连接，输出内容除符合连接条件的那些记录之外，还要加上两个表各自未列进输出记录的那些记录，这些记录在不匹配的另一个表的各列位置中填 NULL 或按默认值填入。关闭时，给定视图名称，例如保持默认的名字 VIEW1，视图设计完毕。 2．使用视图如果视图中涉及仅 1 个基表，且输出全是字段内容，没有表达式，没有聚集函数，则对视图的操作等同于对表操作，向视图录入数据将直接录入到表中，对视图中数据的修改与删除，将直接修改与删除表中的数据。如果是不满足上述条件的视图，只有查询操作与表的操作一致。.

(10) 数据库原理与技术（第二版）. 100. 图 6.12 选择一到多个基表建立相应连接. 图 6.13 选择联接关系符与实现外联接. 例如上述视图 VIEW1 只能用于查询，不能用于数据维护： SELECT * FROM VIEW1. 将实际完成 commodity 与 distribution 的左连接，在两个表的连接集里按条件选择记录并显示这些记录中原 commodity 表的所有数据及 distribution 表的 workoff、shopping 两个字段的数据。又例如，如果从 commodity 表中选择 WareName、Specification、unitprice 三个字段，分别更名为 spmc、gg、danw，建立视图 VIEW2。这样的视图中涉及的只有 1 个基表的内容，且输出的全是字段内容，对其中数据的维护操作实际上是对数据表中数据的维护操作： INSERT INTO VIEW2 (spmc,gg) VALUES ('pencil', 'BBB').

(11) 第6章. SQL Server 数据库系统. 101. 意义是向 commodity 中录入一条新记录，其商品名称为 pencil，规格为'BBB'。由此可见，视图可以简化程序编写的操作，使程序结构简单、清晰。尤其是给不同操作人员以不同视图，这是实现安全性控制的重要方法之一。 6.1.4 数据完整性保护 1．实体完整性保护的实现如果在表中定义了关键字（主键），就实际定义了 PRIMARY 约束（又称主键约束），自动建立主索引。当录入数据或修改数据值时，将自动检查主键的值，如果为空值，或与已经录入的主键值重复，将拒绝存盘。 2．参照完整性保护的实现在图 6.9 界面中选择“关系”选项卡，可以设置表与表之间的关系，如图 6.14 所示。. 图 6.14 建立表之间关系或 FOREIGN 约束. 单击“新建”按钮，再选择主表（主键表）的名字与子表（外键表）的名字，分别选择两表中相关联的字段（主键与外键），就建立两表的 FOREIGN 约束（又称外键约束）。要求子表中外键的值在主表中必须存在，称为参照完整性。操作时可以选择“级联更新相关的字段”，在数据录入或更新操作时，如果数据不满足上述参照完整性约束要求，将不能录入或自动修改子表外键的值，使之符合参考完整性要求。还可以选择“级联删除相关的记录”，在删除主表数据时，将同时删除子表中外键值与其主键中相关联字段值相同的所有记录。.

(12) 数据库原理与技术（第二版）. 102. 对于上述操作有三个复选框可选：①创建中检查现存数据，要求在建立该关系同时检查已经存放在表中的数据，如果有违反该关系的，必须先修改表中的数据之后才能再重新建立该约束；②对复制强制关系，指在复制数据时检查该约束关系，只有满足该约束的数据才允许复制；③对 INSERT 和 UPDATE 强制关系，指在录入新数据或修改表中数据时，只承认满足该约束关系的数据。 3．用户定义完整性保护的实现在图 6.9 界面中选择“CHECK 约束”选项卡，可以定义关于数据范围的约束（又称检查约束），如图 6.15 所示。. 图 6.15 对列设置数据正确性约束. 为尽量保证数据在录入、修改、导入等操作中的正确性，可以设置保证数据正确性的约束条件，使数据只能在一定范围内才能存进数据库。该约束起作用的范围同样可作三方面考虑：①创建中检查现存数据；②对复制强制约束； ③对 INSERT 和 UPDATE 强制约束。 6.1.5 备份与恢复 1．备份与恢复的概念当操作不当或出现意外事故（停电、设备故障）时，数据可能丢失或被破坏。为了保证数据的正确性，有必要经常将数据库中的数据保存到其它其他地方（其它其他文件夹、其它其他盘、其它其他计算机中），称为备份。一旦出现意外，可以利用备份文件恢复原来数据库中的备份前的内容，称为恢复。SQL Server 支持 4 种基本备份：数据库备份、事务日志备份、差.

(13) 第6章. SQL Server 数据库系统. 103. 异备份、文件和文件组备份。数据库备份是指对数据库的完全备份，包括用户表、系统表、索引、视图、存储过程、事务日志等所有数据与数据库对象。事务日志备份则是从上次备份事务日志之后对数据库执行的所有事务的记录，事务记录只记录数据库改变情况，所需要的时间与存储空间都比较少。差异备份只备份从上次差异备份之后更改了的数据，需要的存储空间更少。文件和文件组备份将数据库所涉及文件与文件组全部备份，需要时间很短，但是可能需要较多的存储空间。要根据需要选择备份内容，一般日常多做差异备份，定期进行全备份与文件备份。 2．创建备份设备数据库备份前，先要选择存放备份数据的备份设备，操作为：进入企业管理器、展开服务器组、展开服务器，如图 6.3 所示。展开“管理”文件夹，选择备份，在弹出菜单中选择“新建备份设备” ，弹出新设备对话框，如图 6.16 所示。在“名称”文本框中输入操作者定义的设备名称；在“文件名”文本框中输入备份设备的文件名称，可以单击“…”按钮，从“备份设备位置”对话框中选择备份设备的物理文件名称。最后单击“确定”按钮完成备份设备的创建。. 图 6.16 新设备对话框. 3．备份右击要备份的数据库的名字，在“所有任务”下面选择“备份数据库” （也可以展开“管理”文件夹，选择备份，在弹出菜单中选择“备份数据库”），将弹出 SQL Server 备份对话框（如图 6.17 所示），可以重选数据库，定义备份文件的名称（例如 master 备份），如果需要，可以在“描述”对话框中进一步对备份文件进行说明。在“目的”选项组中通过“添加”、“删除”等按钮选择备份文件或备份设备。如果选择的是用户自己的数据库，在“备份”选项组中可以选择备份内容：数据库－完全备份、数据库－差异备份、事务日志备份、文件和文件组备份。在“重写”选项组中可以选择备份方式，如果选择“追加到媒体” ，表示将备份内容写到当前备份设备原有内容的后面。如果选择“重写现有媒体”，表示将备份内容写到当前备份设备内，复盖当前备份设备中的内容。如果希望按照一定周期对数据库进行备份，可以选中“调度”复选框，再单击其后“…” 按钮，打开编辑调度对话框，安排每次备份数据库的执行时间。如果不选中“调度” ，单击“确.

(14) 数据库原理与技术（第二版）. 104. 定”按钮，将执行备份操作。. 图 6.17 SQL Server 备份数据库的操作界面. 4．恢复恢复是加载备份并应用事务日志重建数据库的过程，当执行恢复操作时，会将备份文件中的数据复制到数据库中，并回滚所有未完成的事务，以保证数据库中数据的一致性。操作方法：右击“数据库”或右击具体数据库的名字，在弹出菜单中选择“所有任务”，再选择“还原数据库” ，如图 6.18 所示。. 图 6.18 恢复数据库的操作过程.

(15) 第6章. SQL Server 数据库系统. 105. 在“还原数据库”对话框中选定欲还原的数据库名，选择是从数据库备份、文件组或文件备份还是从备份设备中还原，在“参数”选项组中，选择备份文件的名字，如果在一个备份文件中保存了多个备份，再选择从其中的哪一个备份恢复（默认的是最新的备份），如图 6.19 所示。. 图 6.19 “还原数据库”对话框. 可以转到“选项”选项卡（如图 6.20 所示）。在“将数据库文件还原为”列表框中给出了要恢复的数据库原文件名和将要恢复的文件名。在“恢复完成状态”选项组中可以选择数据库恢复完毕后数据库的状态。在设置完毕后单击“确定”按钮，将自动完成数据库恢复工作。 6.1.6 数据库安全性管理数据库的目标是让数据最大限度地为各用户共享，尽可能为更多的应用服务。这就要求数据按权限使用，即不同用户可能对数据结构操作的权限不同，如数据的录入权限不同、修改权限不同、删除权限不同、查询权限不同等，数据库要确保只有具有相应权限的用户才能进行相关的操作，防止越权使用。要具有这样的性能，数据库一是要能记录不同用户对有关目标所具有的权限，二是要能在不同情况下识别操作者、检查他所具有的权限、控制他只能进行他有权进行的操作。为此，数据库都应具有安全性管理功能。数据库的安全性管理功能一般包括两方面内容：①用户能否登录及如何登录的管理；② 用户能够操作哪些对象与执行哪些操作的管理。.

(16) 106. 数据库原理与技术（第二版）. 图 6.20 “选项”选项卡. SQL Server 的安全性管理是建立在身份验证和访问许可的机制上的。身份验证要求首先建立用户表，用户通过注册将自己的信息（包括自己选择的密码）存入表内，系统管理员要将其所具有的权限信息（访问许可）也存入表内，在进入系统前先要执行登录操作，报告自己的账号与密码，系统检查其输入内容是否与用户表中保存的数据一致，确定用户的合法性、确定他所具有的权限，控制其可以进行的操作。 1．身份验证 SQL Server 身份验证有两种模式：Windows 身份验证模式与混合身份验证模式。前者只要用户能登录 Windows NT/2000/2003 操作系统，即具有 Windows 用户账号，就视同 SQL Server 身份验证通过。这种方法集成了 Windows NT/2000/2003 的安全系统的功能，例如密码加密、审核、密码过期、最短密码长度、身份验证（包括多次登录申请无效后锁定账户等）功能。而混合身份验证使用户得以使用 Windows 身份验证或使用 SQL Server 身份验证实现与 SQL Server 连接。在 SQL Server 身份验证下，用户在连接 SQL Server 时必须提供登录名和密码， SQL Server 在系统表 syslogin 中检测输入的账户名和密码，只有找到相匹配的，才能进入系统。 SQL Server 身份验证模式通过 SQL Server 属性对话框设置。方法是：展开服务器组→右击需要修改验证模式的“服务器”名→选择属性（R）→系统弹出 SQL Server 属性对话框→进入“安全性”选项卡，如图 6.21 所示。 2．登录账号的管理在 SQL Server 中登录账号与用户账号是两个不同的概念，一个登录账号总是与一个或多个数据库用户账号对应。用户登录后将具有进入 SQL Server 的权限，在进一步有了用户账号.

(17) 第6章. SQL Server 数据库系统. 107. 后，才具有访问数据库的权限。. 图 6.21 SQL Server 属性对话框. 创建登录账号的操作如图 6.22 所示：展开服务器组→展开服务器→选择“安全性” →登录→新建登录→弹出 SQL Server 登录属性－新建登录对话框，如图 6.23 所示。. 图 6.22 创建登录账号的操作.

(18) 108. 数据库原理与技术（第二版）. 图 6.23 “新建登录”对话框. 在“常规”选项卡中先输入用户登录账号，再确定身份验证模式，如果选择 SQL Server 身份验证，将要求输入密码。之后选择该登录账号有权操作的数据库与语言。 3．创建用户账号创建用户账号的操作方法是：展开服务器组→展开服务器→展开数据库→右击某具体数据库→新建→数据库用户（如图 6.24 所示）→进入“新建用户”对话框。. 图 6.24 创建用户账号的操作.

(19) 第6章. SQL Server 数据库系统. 109. 在“登录名”列表框中选择于“新建登录”对话框中输入的某登录账户，再输入用户名，选择数据库角色中所允许的操作权限，单击“确定”按钮，如图 6.25 所示。. 图 6.25 “新建用户”对话框. 4．服务器角色当几个用户工作相类似时，为简化管理与方便操作，可以将他们集中到一个称为角色的单元中，按角色分配权限，对一个角色的权限设置可以用到多个用户的管理中。 SQL Server 规定了两种角色类型：服务器角色与数据库角色。系统创建了 8 个服务器角色，如表 6.1 所示。表 6.1 服务器角色及其权限服务器角色. 操作权限. sysadmin. 在 SQL Server 中各种活动. securityadmin. 管理服务器登录. serveradmin. 配置服务器范围. setupadmin. 添加和删除链接数据库并执行某些系统存储过程. processadmin. 管理在 SQL Server 中运行的进程. diskadmin. 管理磁盘文件. dbcreator. 创建和改变数据库. bulkadmin. 执行 BULK INSERT 语句.

(20) 110. 数据库原理与技术（第二版）. 指定用户角色的方法：在“新建登录”对话框中选择“服务器角色”选项卡→双击某角色，弹出“服务器角色属性”对话框，如图 6.26 所示。单击“属性”按钮，在弹出的“添加成员”对话框中选择用户名，并单击“确定”按钮。如果选择“权限”选项卡，可以查看该服务器角色所具有的权限情况，如图 6.27 所示。. 图 6.26 为用户授予服务器角色. 图 6.27 查看该服务器角色所具有的权限.

(21) 第6章. SQL Server 数据库系统. 111. 5．数据库角色系统管理员可以将用户加入到用户内部数据库角色中，从而能在数据库级别上进行操作。 SQL Server 提供了两种数据库角色类型：预定义的数据库角色和用户自定义的数据库角色。预定义的数据库角色有规定的权限，只要给某角色赋予某种预定义的数据库角色，该角色就具有预先规定的那些权限。如果要将登录用户添加到固定数据库角色成员中，可以展开数据库文件夹，展开用户准备授权的数据库，右击“角色” ，选择“新建数据库角色”，在弹出的数据库角色属性对话框中进行操作。 6．权限管理用户或角色还需要进一步被授予某些创建或操作权限才能对数据表、视图、存储过程进行具体的操作。创建权限包括：①创建数据库权限：CREATE DATABASE；②创建表：CREATE TABLE； ③创建视图：CREATE VIEW；④创建规则：CREATE RULE；⑤创建缺省：CREATE DEFAULT； ⑥创建存储过程：CREATE PROCEDURE；⑦备份数据库：BACKUP DATABASE；⑧备份事务日志：BACKUP LOG。操作权限包括：①对表或视图录入操作权限：INSERT；②对表或视图或列修改操作权限： UPDATE；③从表或视图删除操作权限：DELETE；④对表或视图或列查询操作权限：SELECT； ⑤对表转授权操作权限：REFERENCE；⑥对存储过程的执行权限：EXECUTE。利用企业管理器授权的方法：展开服务器组→展开服务器→展开数据库→展开某具体数据库→用户→双击某具体用户→进入“数据库用户属性”对话框，如图 6.28 所示。. 图 6.28 “数据库用户属性”对话框.

(22) 数据库原理与技术（第二版）. 112. 在“数据库用户属性”对话框中单击“权限”按钮，弹出“数据库用户属性”对话框的 “权限”选项卡，如图 6.29 所示。先选择视图、表或列等对象，再授予有关权限。. 图 6.29 权限选项卡. 6.1.7 查询分析器除了利用企业管理器对数据库进行操作外，还可以利用 SQL 语言的命令完成以上各项操作。SQL Server 中的 SQL 语言是 Transact-SQL 语言，利用 Transact-SQL 语言操作的界面之一是查询分析器。当选用查询分析器后首先进入“连接到 SQL Server”对话框，如图 6.30 所示。. 图 6.30 “连接到 SQL Server”对话框.

(23) 第6章. SQL Server 数据库系统. 113. 选择“…”按钮可以选择服务器，再选择身份验证方式之后进入查询分析器操作界面。如果涉及建表、数据维护、查询等操作时首先要选择数据库，在主菜单中选择“查询”，再选择“更改数据库” （见图 6.31），进入更改数据库的界面，如图 6.32 所示。. 图 6.31 更改数据库的操作. 图 6.32 更改数据库的界面. 选定数据库后，就可以在查询分析器的编辑框中输入 Transact-SQL 语句，如图 6.33 所示。在查询分析器的工具栏中用标志“执行 ”按钮，单击该按钮即可执行所输入的 Transact-SQL 语句，如图 6.34 所示。.

(24) 数据库原理与技术（第二版）. 114. 图 6.33 查询分析器的编辑框. 图 6.34 执行 Transact-SQL 语句. 6.2. Transact-SQL 语言简介. SQL Server 中的 SQL 语言是 Transact-SQL 语言。它包括以下主要组成部分：  数据定义语言（Data Definition Language，DDL）  数据操纵语言（Data Manipulation Language，DML）  数据控制语言（Data Control Language，DCL）  系统存储过程（System Stored Procedure）  一些附加的语言元素本节主要介绍 Transact-SQL 语言比标准 SQL 语言扩展或加强的部分内容。 6.2.1 数据定义语言（DDL）数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句，包括 CREATE、ALTER 和 DROP 等语句。在 SQL Server 中，数据库对象包括表、视图、触发器、存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通.

(25) 第6章. SQL Server 数据库系统. 115. 过使用 CREATE、ALTER、DROP 等语句来完成。 1．创建数据库的命令命令格式：CREATE DATABASE <数据库名> [ON [PRIMARY] [<文件 1>[，<文件 2>……]][，<文件组和文件>]] [LOG ON <文件>] [COLLATE <排序规则名称>] [FOR LOAD | FOR ATTACH] 说明：（1）PRIMARY 表示定义数据主文件，其后是关于数据文件的描述。如果没有 PRIMARY 字样，其中第一个文件为主文件。（2）关于“文件”的格式：NAME= <文件逻辑名称>，FILENAME=<操作系统中的文件名称>，SIZE=<文件大小>，MAXSIZE=<文件最大尺寸>，FILEGROWTH=<每次文件添加空间的大小>。其中“文件逻辑名称”指将来在指令中使用的名称， “操作系统中的文件名称”指存盘路径与文件名称，路径应当是 SQL Server 实例中的目录，不应是压缩文件系统中的目录。大小与尺寸的默认单位为 MB。每次需要增加空间时添加空间的大小可以用默认单位 MB，也可以用百分比：%，默认为 10%。文件可以是多个文件，对每一个文件的描述用括号括起，彼此间用逗号分隔。（3）关于“文件组和文件”的意义：定义中用 PRIMARY 定义主文件组，可以再增加用户定义文件组及文件组中的文件。格式为：FILEGROUP <文件组名> <文件 1>[， <文件 2>……]。（4）LOG 子句中定义的文件指日志文件名。（5）COLLATE 指定默认的排序规则，排序规则可以是 Windows 排序规则，也可以是 SQL Server 排序规则。如果没有指定排序规则，则以 SQL Server 实例的排序规则为排序规则。（6）FOR LOAD 指可以从备份数据库中加载。FOR ATTACH 是将已脱机的数据库重新联机。【例 6.1】创建数据库：waremanage，数据文件初始大小为 1M，最大为 10M，如果需要增加空间，每次增加 1M。逻辑文件同样设置。 CREATE DATABASE waremanage ON(NAME= 'waremanage_data',FILENAME='c:\program files\Microsoft sql server\data\ waremanage_data.mdf',SIZE=1,MAXSIZE=10,FILEGROWTH=1) LOG ON (NAME='waremanage_log',FILENAME='waremanage_data.ldf',SIZE=1,MAXSIZE= 10,FILEGROWTH=1) 2．创建数据库表创建表的语句格式类似于标准 SQL 语言中创建表的语句格式，但涉及数据库、约束条件、索引等描述后，表达更深入。命令格式：CREATE TABLE <表名说明>({列定义或列计算式} [CHECK 子句] [ ON { <文件组名> | DEFAULT }] [ TEXTIMAGE_ON { <文件组名> | DEFAULT } ]) 说明：（1）关于“表名说明”有三种格式：①直接用表名，表示在当前数据库下建表。②<数据库名>.<表名>，只有数据库属主有权操作；③<数据库名>.<属主>.<表名>。（2）列定义的一般格式是：<列名> <数据类型> [<宽度>] [NOT [NULL]] [CONSTRAINT 子句] [UNIQUE 子句] [PRIMARY 子句] [FOREIGN 子句] [DEFAULT 子句] 1）CONSTRAINT 子句是可选关键字，表示 PRIMARY KEY、NOT NULL、UNIQUE、.

(26) 116. 数据库原理与技术（第二版）. FOREIGN KEY 或 CHECK 约束定义的开始，同时定义索引名称。格式为：CONSTRAINT < 索引名>。 2）UNIQUE 表示唯一性，表示该列不允许有重复值。它和主键不同的是，主键除不允许有重复值外，还不允许有空值。 3）PRIMARY 子句说明该列为主键。格式：PRIMARY KEY CLUSTERED。 4）DEFAULT 子句定义默认值，如果该列不允许空值，而在录入时又未说明该列的值，则自动用默认值填充；否则填入 NULL。 5）FOREIGN 子句定义外键，其格式为：FOREIGN KEY <外键名> REFERENCES <主表名称>（主键名称）。其中外键名可以是多个列的列名，用逗号分隔，但其数量与类型必须与其后主表中说明的相关列的列名与类型一一对应。（3）{}表示其中内容为多个类似结构的集合，例如{列定义}表示多个列定义的集合，列定义之间用逗号分隔。（4）列计算式指有的列的数据可以由另外一些列的数据根据具体的公式计算得到，称为派生数据。在列定义中，这样的列可以用如下格式定义： <列名> AS <计算列值的表达式> 计算列不能作为 INSERT 或 UPDATE 语句的目标，也不能作为 DEFAULT 和 FOREIGN KEY 的约束定义。（5）CHECK 子句为表级约束，说明列自定义约束条件。其格式为：CHECK (<约束条件表达式>)。其中约束条件表达式如下所示： 1）<字段名> IN (<值表>)。 2）<字段名> <关系符><数据值>。 3）<字段名> LIKE <匹配表达式>。约束条件表达式可以由多个表达式组成，彼此间用 AND、OR 连接。（6）ON { <文件组名> | DEFAULT }定义存储表的文件组，该文件组必须在数据库中存在。如果使用“DEFAULT”或没有该子句，表示存储在默认文件组中。（7）TEXTIMAGE_ON { <文件组名> | DEFAULT } 定义 text、ntext、image 等类数据所存储的文件组名称。【例 6.2】在数据库 waremanage 中创建关于出版物的表 publishers(pub_id, pub_name, author ,unitprice, unit)。在选择数据库 waremanage 之后在查询分析器中可以输入如下语句建表： CREATE TABLE publishers (pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN ('0389', '0736', '0877', '1622', '1756') OR pub_id LIKE '20[0-9][0-9]'),pub_name varchar(40) NULL, author varchar(20) NULL, unitprice int NULL, unit varchar(30) NULL DEFAULT('USA') ) 说明：（1）NOT NULL 表示在输入数据时不允许空值，NULL 表示在输入数据时允许空值。（2）CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED 建立主键约束，同时建立主键索引，索引名为 UPKCL_pubind。CLUSTERED 表示索引类型为聚集索引。.

(27) 第6章. SQL Server 数据库系统. 117. （3）CHECK (pub_id IN ('0389', '0736', '0877', '1622', '1756') OR pub_id LIKE '20[0-9][0-9]') 子句建立用户定义约束，将建立如下表达式：([pub_id] = '1756' or ([pub_id] = '1622' or ([pub_id] = '0877' or ([pub_id] = '0736' or [pub_id] = '0389'))) or [pub_id] like '20[0-9][0-9]') like '20[0-9][0-9]')表示出版物编号头两位为“20”，后两位的每一位由 0 到 9 之间的数字构成。 3．修改数据表与删除数据表修改数据表与删除数据表的语句和标准 SQL 语言中修改数据表与删除数据表的语句基本相同，但修改数据表的添加新列、修改列属性等语句中可包括添加约束、修改约束的内容。【例 6.3】为 publishers 的 unitprice 创建一个名为 CK_publishers 的约束，要求控制 unitprice 的值在 10～1000 之间。 ALTER TABLE publishers ADD CONSTRAINT CK_publishers CHECK (unitprice>=10 AND unitprice<=1000). 4．建立视图语句格式：CREATE VIEW [<数据库名>.][<属主名>.] <视图名>[({<列名>})] [WITH <视图属性>] AS <子查询> [WITH CHECK OPTION] 说明：（1）{<列名>}定义视图中的列名，可以与表中列名不相同，列名间用逗号分隔。（2）视图属性包括①ENCRYPTION：表示加密包含 CREATE VIEW 语句文本的系统表列； ②SCHEMABINDING：将视图绑定在架构上，要求<SELECT 子句>包含表、视图等两部分的名称；③VIEW_METADATA：表示在某些查询中可以返回元数据信息。（3）子查询与 WITH CHECK OPTION 见 4.7 节相关内容。 5．修改视图语句格式：ALTER VIEW [<数据库名>.][<属主名>.] <视图名>[({<列名>})] [WITH <视图属性>] AS <子查询> [WITH CHECK OPTION] 不难发现，修改视图语句的参数和建立视图的语句参数是相同的。【例 6.4】修改原有表 publication 的视图 VIEW_pub 的内容，要求输出 pub_id、pub_name 与 unitprice，显示条件是 unitprice 大于 100。 ALTER VIEW waremanage. VIEW_pub AS SELECT pub_id, pub_name, unitprice FROM publishers WHERE unitprice>100 6．建立索引语句格式：CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX <索引名称> ON {<表名> | <视图名>} ({<列名>[ASC | DESC]}) [{WITH <索引选项>} ] [ON <文件组>] 说明：（1）UNIQUE 选项表示创建唯一索引。（2）CLUSTERED | NONCLUSTERED 选项表示创建聚集或非聚集索引。如果是聚集索引，则数据表中数据物理存储时按该索引规定的顺序排列。（3）索引选项如 IGNORE_DUP_KEY 表示向属于聚集索引的列插入重复值时将发出警告并拒绝执行。又如 DROP_EXISTING 表示除去并重建先前存在的聚集或非聚集索引。【例 6.5】为表 publishers 建立关于 pub_id 的聚集索引。.

(28) 118. 数据库原理与技术（第二版）. CREATE UNIQUE CLUSTERED INDEX pub_pub_id (pub_id ASC) WITH IGNORE_DUP_KEY 6.2.2 数据操纵语言（DML）数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括 SELECT、INSERT、UPDATE、DELETE 等。在默认情况下，只有 sysadmin、dbcreator、db_owner 或 db_datawriter 等角色的成员才有权利执行数据操纵语言。 1．插入数据语句语句基本结构：INSERT INTO ({<表名> | <视图名>} {[<列名>]}) VALUES ({DEFAULT | NULL | <表达式>}) 说明：（1）{[<列名>]}指多个列的名字，彼此用逗号分隔。如果该项省略，表示对表的所有字段。（2）{DEFAULT | NULL | <表达式>}表示多个“DEFAULT | NULL | <表达式>”的集合，彼此用逗号分隔，其数量与顺序要和{[<列名>]}一致，如果语句中省略{[<列名>]}，那么要和表定义的结构中列的数量与顺序一致。 “DEFAULT | NULL | <表达式>”表示可以是“DEFAULT”，或者是 NULL，或者是<表达式>。“DEFAULT”表示按表定义中关于列默认值的定义填入。NULL 表示填入“空” ，只有表定义中允许为空值的列允许填入。<表达式>指当前可以计算得到具体数据的计算式或函数式。 2．修改数据语句语句基本结构：UPDATE {<表名> | <视图名>} SET {列名=<表达式> | DEFAULT | NULL} [FROM {<表名>}] [WHERE <条件表达式>] 【例 6.6】如果有成绩表 1（学号，姓名，总分）和成绩表 2（学号，姓名，C 语言分数），要求更新成绩 1 表中的总分，等于相同学号记录的原总分加 C 语言分数。 UPDATE 成绩表 1 SET 成绩表 1.总分=成绩表 1.总分+ 成绩表 2.C 语言分数 WHERE 成绩表 1.学号=成绩表 2.学号 3．查询语句语句基本结构： SELECT {<表达式>} [INTO <新表名>] FROM {<表名>} [WHERE <选择条件表达式>] [GROUP BY {<分组字段名称>}] [HAVING <分组条件表达式>] [ORDER BY {<排序字段> [ASC | DESC]}] [COMPUTE <聚集函数>] [FOR < BROWSE 或 XML 选项>] 说明：（1）INTO <新表名>子句将查询结果传入一个新数据表中，将产生一个新数据表。（2）COMPUTE <聚集函数>的结构为： COMPUTE { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } ( <表达式>) } [ BY <表达式>] 其功能是生成统计数据作为附加的汇总列出现在结果集的最后。当与 BY 一起使用时，可以在结果集内生成分类汇总。在同一查询内可同时指定 COMPUTE BY 和 COMPUTE。其中各聚集函数的意义：AVG 表示数字表达式中所有值的平均值；COUNT 表示记录条数；MAX 表示表达式中的最大值；MIN 表示表达式中的最小值；STDEV 表示表达式中所有值的统计标准偏差；STDEVP 表示表达式中所有值的填充统计标准偏差；SUM 表示数字表达.

(29) 第6章. SQL Server 数据库系统. 119. 式中所有值的和；VAR 表示表达式中所有值的统计方差；VARP 表示表达式中所有值的填充统计方差。 BY <表达式>中的<表达式>通常是列名，表示分类汇总的分组字段。（3）FOR < BROWSE 或 XML 选项>子句格式为： FOR { BROWSE | XML { RAW | AUTO | EXPLICIT } [ , XMLDATA ] [ , ELEMENTS ] [ , BINARY BASE64 ]} 说明： 1）FOR BROWSE 指定当查看 DB-Library 浏览模式游标中的数据时允许更新。如果表包含时间戳列（用 timestamp 数据类型定义的列），表有唯一索引且 FOR BROWSE 选项在 SELECT 语句的最后发送到 SQL Server，则可以在应用程序中浏览该表。 2）FOR XML 指定查询结果将作为 XML 文档返回。必须指定下列 XML 模式之一：RAW、 AUTO、EXPLICIT。其中，RAW：获得查询结果并将结果集内的各行转换为 XML 元素，用一般标识符 <row /> 作为元素标记。AUTO：以简单的嵌套 XML 树返回查询结果。在 FROM 子句内，每个在 SELECT 子句中至少有一列被列出的表都表示为一个 XML 元素。SELECT 子句中列出的列映射到适当的元素特性。EXPLICIT：指定显式定义所得到的 XML 树的形状。使用此种模式，要求以一种特定的方式编写查询，以便显式指定有关期望的嵌套的附加信息。 3）XMLDATA 表示返回架构，但不将根元素添加到结果中。如果指定了 XMLDATA，它将被追加到文档上。 4）ELEMENTS 表示指定列作为子元素返回。否则，列将映射到 XML 特性。 5）BINARY BASE64 表示指定查询返回二进制 base64 编码格式的二进制数据。使用 RAW 和 EXPLICIT 模式检索二进制数据时，必须指定该选项。这是 AUTO 模式中的默认值。 6）使用 UNION 运算符可以将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中的所有查询的全部行，实现求关系并集的操作。使用 UNION 组合两个查询的结果集的两个基本规则是：①所有查询中的列数和列的顺序必须相同；②数据类型必须兼容。语句结构：<查询语句 1> UNION [ALL] <查询语句 2> [UNION [ALL] ……] 其中 ALL 表示在组合过程中不删除重复行。【例 6.7】如果有表 titles，包括 title、type、price、advance、ytd_sales 等字段，显示含有本年度截止到现在的当前销售额的行，然后按 type 分类以递减顺序计算书籍的平均价格和预付款总额，最后计算全部书籍的平均价格和预付款总额。 SELECT CAST(title AS char(20)) AS title, type, price, advance FROM titles WHERE ytd_sales IS NOT NULL ORDER BY type DESC COMPUTE AVG(price), SUM(advance) BY type COMPUTE SUM(price), SUM(advance) 6.2.3 数据控制语言（DCL）数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，包括 GRANT、 DENY 、 REVOKE 等语句，在默认状态下，只有 sysadmin 、 dbcreator 、 db_owner 或 db_securityadmin 等角色的成员才有权利执行数据控制语言。 1．GRANT 语句 GRANT 语句是授权语句，它可以把语句权限或者对象权限授予给其他用户和角色。.

(30) 数据库原理与技术（第二版）. 120. （1）授予语句权限的语句。语句格式：GRANT {ALL | <语句>} TO <用户名> 说明：“语句”包括：CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、 CREATE PROCEDURE、CREATE RULE、CREATE TABLE、CREATE VIEW 、BACKUP DATABASE、BACKUP LOG。（2）授予对象权限的语句。语句格式：GRANT { ALL [ PRIVILEGES ] | <对象权限>}{[ (<列名>) ON {<表名> | <视图名>} | ON {<表名> | <视图名>} [ (<列名>) ] | ON { <存储过程> | <扩展程序>} | ON { <用户定义函数>} ] TO <用户名> [ WITH GRANT OPTION ] [ AS {<组> | <角色>} ] 说明： 1）“对象权限”是当前授予的对象权限。当在表、表值函数或视图上授予对象权限时，权限列表可以包括这些权限中的一个或多个：SELECT、INSERT、DELETE、REFERENCES 或 UPDATE。列列表可以与 SELECT 和 UPDATE 权限一起提供。如果列列表未与 SELECT 和 UPDATE 权限一起提供，那么该权限应用于表、视图或表值函数中的所有列。在存储过程上授予的对象权限只可以包括 EXECUTE。 2）WITH GRANT OPTION 表示给予用户将指定的对象权限授予其它其他安全账户的能力。 3）AS {<组> | <角色>}指当前数据库中有执行 GRANT 语句权力的安全账户的可选名。当对象上的权限被授予一个组或角色时使用 AS，对象权限需要进一步授予不是组或角色的成员的用户。因为只有用户（而不是组或角色）可执行 GRANT 语句，组或角色的特定成员授予组或角色权力之下的对象的权限。 2．DENY 语句 DENY 语句用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。（1）否定语句权限的语句。语句格式：DENY { ALL | <语句>} TO <用户名> （2）否定对象权限的语句。语句格式：DENY { ALL [ PRIVILEGES ] | <对象权限>} { [ (<列名> ) ] ON {<表名> | <视图名>} | ON {<表名> | <视图名>} [ (<列名>) ] | ON { <存储过程> | <扩展程序>} | ON {<用户定义函数>} } TO <用户名> [ CASCADE ] 3．REVOKE 语句 REVOKE 语句是与 GRANT 语句相反的语句，它能够将以前对当前数据库内的用户或者角色上授予或拒绝的权限删除，但是该语句并不影响用户或者角色从其他角色中作为成员继承过来的权限。（1）收回语句权限的语法的语句。语句格式：REVOKE { ALL | <语句> } FROM <用户名> （2）收回对象权限的语法的语句。.

(31) 第6章. SQL Server 数据库系统. 121. 语句格式：REVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | <对象权限>} { [ (< 列名>) ] } ON {<表名> | <视图名>} | ON {<表名> | <视图名>} [ (<列名> ) ] | ON {<存储过程> | <扩展程序>} | ON { <用户定义函数>} { TO | FROM }<用户名> [ CASCADE ] [ AS {<组> | <角色>} ] 6.2.4 其它其他语言元素在 Transact-SQL 语言中嵌入了其他程序设计语言的元素，用其到存储过程与触发器之中。 1．注释注释是程序代码中不执行的文本字符串（也称为注解）。在 SQL Server 中，可以使用两种类型的注释字符：一种是 ANSI 标准的注释符“--” ，它用于单行注释；另一种是与 C 语言相同的程序注释符号，即“/* */”。 2．变量变量是一种语言中必不可少的组成部分。Transact-SQL 语言中有两种形式的变量，一种是用户自己定义的局部变量，另一种是系统提供的全局变量。（1）局部变量。局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。局部变量可以作为计数器来计算循环执行的次数，或是控制循环执行的次数。另外，利用局部变量还可以保存数据值，以供控制流语句测试以及保存由存储过程返回的数据值。局部变量被引用时要在其名称前加上标志“＠” ，而且必须先用 DECLARE 命令定义后才可以使用。（2）全局变量。全局变量是 SQL Server 系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些 SQL Server 的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是 Transact-SQL 命令执行后的状态值。使用全局变量时应该注意以下几点： 1）全局变量不是由用户的程序定义的，它们是在服务器级定义的。 2）用户只能使用预先定义的全局变量。 3）引用全局变量时，必须以标记符“@@”开头。 4）局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。 3．运算符运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。在 SQL Server 中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串串联运算符。（1）算术运算符。算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。算术运算符包括：加（+）、减（-）、乘（*）、除（/）和取模（%）。（2）赋值运算符。Transact-SQL 中只有一个赋值运算符，即等号（=）。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。.

(32) 122. 数据库原理与技术（第二版）. （3）位运算符。位运算符使我们能够在整型数据或者二进制数据（image 数据类型除外）之间执行位操作。此外，在位运算符左右两侧的操作数不能同时是二进制数据。位运算符包括： ^、&、|。（4）比较运算符。比较运算符用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即 TRUE（表示表达式的结果为真）、FALSE（表示表达式的结果为假）以及 UNKNOWN。除了 text、ntext 或 image 数据类型的表达式外，比较运算符可以用于所有的表达式。比较运算符包括：=、>、<、>=、<=、<>、!=、!>、!<。（5）逻辑运算符。逻辑运算符可以把多个逻辑表达式连接起来。逻辑运算符包括 AND、 OR 和 NOT 等运算符。逻辑运算符和比较运算符一样，返回带有 TRUE 或 FALSE 值的布尔数据类型。逻辑运算符包括：AND、OR、NOT。（6）字符串串联运算符。字符串串联运算符允许通过加号（+）进行字符串串联，这个加号即被称为字符串串联运算符。例如对于语句 SELECT 'abc'+'def'，其结果为 abcdef。运算符的优先等级从高到低如下所示。括号：（）乘、除、求模运算符：*、/、% 加减运算符：+、比较运算符：=、>、<、>=、<=、<>、!=、!>、!< 位运算符：^、&、|；逻辑运算符：NOT、AND、OR 4．函数在 Transact-SQL 语言中，函数被用来执行一些特殊的运算以支持 SQL Server 的标准命令。 Transact-SQL 编程语言提供了三种函数：（1）行集函数：行集函数可以在 Transact-SQL 语句中当作表引用。（2）聚集函数：聚集函数用于对一组值执行计算并返回一个单一的值。（3）标量函数：标量函数用于对传递给它的一个或者多个参数值进行处理和计算，并返回一个单一的值。 SQL Server 中最常用的几种函数：（1）字符串函数。字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于 char 和 varchar 数据类型以及明确转换成 char 和 varchar 的数据类型，少数几个字符串函数也可以用于 binary 和 varbinary 数据类型。此外，某些字符串函数还能够处理 text、ntext、image 数据类型的数据。字符串函数的分类： 1）基本字符串函数： UPPER(<字符串>)：将串中小写字符变大写字符。 LOWER(<字符串>)：将串中大写字符变小写字符。 SPACE(<整数>)：产生“整数”个空格。 REPLICATE(<字符串>,<整数>)：将字符串重复“整数”次。 STUFF(<字符串 1>,<数字>,<整数>,<字符串 2>)：将字符串 1 中从“数字”开始的整数个字符用字符串 2 代替。.

(33) 第6章. SQL Server 数据库系统. 123. REVERSE(<字符串表达式>)：反转字符串表达式。 LTRIM(<字符串>)：删除字符串前面空格。 RTRIM(<字符串>)：删除字符串后面空格。 2）字符串查找函数： CHARINDEX(<字符串 1>,<字符串 2>)：在字符串 2 中搜索字符串 1 的起始位置。 PATINDEX(’%<字串>%’,<字符串>)：在字符串中搜索字串出现的起始位置。 3）长度和分析函数： SUBSTRING(<字符串 1>,<数字>,<整数>)：从字符串 1 中取从“数字”开始的长度为“整数”的字符串。 RIGHT(<字符串>,<整数>)：从字符串右边取长度为“整数”的字符串。 LEFT(<字符串>,<整数>)：从字符串左边取长度为“整数”的字符串。 4）转换函数： ASCII(<字符>)：求“字符”的 ASCII 码。 CHAR(<整数>)：求 ASCII 码等于“整数”的字符。 STR(<数值表达式>[,<整数>[,<小数位>]])：将数值表达式的值变成长度等于“整数”、小数位位数等于“小数位”的字符串。（2）日期和时间函数。日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。 DATEADD(<参数>,<数字>,<日期>)：按参数指定部分计算日期与数字之和并返回。参数可以为：Year、quarter、Month、dayofyear、Day、Week、Hour、minute、second、millisecond。 DATEDIFF(<参数>,<日期 1>,<日期 2>)：按参数指定部分计算日期与日期之差。 DATENAME(<参数>,<日期>)：按参数指定部分返回日期相应部分的字符串。 DATEPART(<参数>,<日期>)：按参数指定部分返回日期相应部分的整数值。 GETDATE(<日期>)：返回系统日期。 DAY(<日期>)：返回日期表达式的日期数据。 MONTH(<日期>)：返回日期表达式的月份数据。 YEAR(<日期>)：返回日期表达式的年份数据。（3）数学函数。数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对 SQL Server 提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行处理。数学函数有： ABS(n)：求 n 的绝对值。 CEILING(n)：求大于等于 n 的最小整数。 DEGREES(n)：求弧度 n 的度数值。 FLOOR(n)：求小于等于 n 的最大整数。 POWER(n,m)：求 n 的 m 次方。 RADIANS(n)：求度数 n 的弧度值。 SIGN(n)：求 n 的符号，分别用 1、-1、0 表示正数、负数、0。 EXP(n)：求 n 的指数值。.

(34) 124. 数据库原理与技术（第二版）. LOG(n)：求 n 的对数值。 LOG10(n)：求 n 的以 10 为底的对数值。 SQUARE(n)：求 n 的平方。 SQRT(n)：求 n 的平方根。 SIN(n)：求 n 的正弦值。 COS(n)：求 n 的余弦值。 TAN(n)：求 n 的正切值。 PI()：返回π的值。 RAND()：产生随机数。 MOD(m,n)：求 m 除以 n 的余数。 ROUND(n,m)：对 n 作四舍五入处理，保留 m 位。（4）转换函数。一般情况下，SQL Server 会自动处理某些数据类型的转换。例如，如果比较 char 和 datetime 表达式、smallint 和 int 表达式、或不同长度的 char 表达式，SQL Server 可以将它们自动转换，这种转换被称为隐性转换。但是，无法由 SQL Server 自动转换的或者是 SQL Server 自动转换的结果不符合预期结果的，就需要使用转换函数做显式转换。 CONVERT(<转换后数据类型>[(<长度>)],<表达式>[,<转换样式>])：将表达式的值转换为 “转换后数据类型”指定的数据类型，“长度”表示转换后的数据长度，“转换样式”只在表达式为日期时间类型且欲转换为字符类型时使用，给出转换成字符类型的样式。转换后数据类型用 SQL Server 数据类型描述符表示。 CAST(<表达式>,<转换后数据类型>)：将表达式的值转换为“转换后数据类型”指定的数据类型。（5）系统函数。系统函数用于返回有关 SQL Server 系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它其他函数一样，可以在 SELECT 语句的 SELECT 和 WHERE 子句以及表达式中使用系统函数。 DB_ID(<名称>)：返回数据库 ID 号。 DB_NAME(<ID 号>)：返回数据库名称。 HOST_ID(<名称>)：返回主机 ID 号。 HOST_NAME(<ID 号>)：返回主机名称。 OBJECT_ID(<名称>)：返回指定对象 ID 号。 OBJECT_NAME(<ID 号>)：返回指定对象名称。 SUSER_ID(<名称>)：返回指定登录 ID 号。 SUSER_NAME(<ID 号>)：返回指定登录的名称。 USER_ID(<名称>)：返回指定用户 ID 号。 USER_NAME(<ID 号>)：返回指定用户名称。 COL_NAME(<表号>,<列号>)：返回列名。 COL_LENGTH(<表名>,<列名>)：返回列定义长度。 DATALENGTH(<表达式>)：返回表达式占用的字节长度。（6）聚集函数。聚集函数可以返回整个或者几个列或者一个列的汇总数据，它常用来计算.

(35) 第6章. SQL Server 数据库系统. 125. SELECT 语句查询的统计值。聚集函数经常与 SELECT 语句的 GROUP BY 子句一同使用。聚集函数包括：AVG、COUNT、MAX、MIN、SUM。 5．流程控制语句流程控制语句是指那些用来控制程序执行和流程分支的命令，在 SQL Server 中，流程控制语句主要用来控制 SQL 语句、语句块或者存储过程的执行流程。（1）BEGIN…END 语句。BEGIN…END 语句能够将多个 Transact-SQL 语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环语句等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用 BEGIN…END 语句语句格式： BEGIN { <执行语句> | <语句块> } END （2）IF…ELSE 语句。IF…ELSE 语句是条件判断语句，其中，ELSE 子句是可选的，最简单的 IF 语句没有 ELSE 子句部分。IF…ELSE 语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。SQL Server 允许嵌套使用 IF…ELSE 语句，而且嵌套层数没有限制。语句格式： IF <条件表达式> {<执行语句> | <语句块>} [ ELSE {<执行语句> | <语句块> } ] （3）CASE 语句。CASE 语句可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE 语句按照使用形式的不同，可以分为简单 CASE 语句和搜索 CASE 语句。语句格式： CASE <表达式> WHEN <值 1> THEN <表达式 1> …… WHEN <值 n> THEN <表达式 n> [ELSE <表达式 n+1 >] END 意义：如果“表达式”的值等于值 1，则返回表达式 1 的值且跳出 CASE 语句，否则继续检查“表达式”的值是否等于值 2，……。如果与值 n 也不相等且有 ELSE 子句，则返回表达式 n+1 的值。 CASE 函数语句还有一种格式： CASE WHEN <条件表达式 1> THEN <表达式 1> …… WHEN <条件表达式 n> THEN <表达式 n> [ELSE <表达式 n+1> ] END 意义：如果“条件表达式 1”的值为真，则返回表达式 1 的值且跳出 CASE 语句，否则继续检查“条件表达式 2”的值是否为真，……。如果所有条件表达式的值都不为真且有 ELSE 子句，则返回表达式 n+1 的值。（4）WHILE…CONTINUE…BREAK 语句。WHILE…CONTINUE…BREAK 语句用于设.

(36) 126. 数据库原理与技术（第二版）. 置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。其中， CONTINUE 语句可以使程序跳过 CONTINUE 语句后面的语句，回到 WHILE 循环的第一行命令。BREAK 语句则使程序完全跳出循环，结束 WHILE 语句的执行。语句格式： WHILE 条件表达式{ <执行语句 1> | <语句块 1> }[ BREAK ] [ CONTINUE ] { <执行语句 2> | <语句块 2> } 意义：只要“条件表达式”值为真，就重复执行<执行语句 1>或<语句块 1>。在<执行语句 1> | <语句块 1>中还应当有条件语句，控制是否执行 BREAK 或 CONTINUE，如果执行 BREAK，则停止循环。如果执行 CONTINUE，则停止本次循环，转至<执行语句 1> | <语句块 1>的首条语句继续执行。（5）GOTO 语句。GOTO 语句可以使程序直接跳到指定的标有标识符的位置处继续执行，而位于 GOTO 语句和标识符之间的程序将不会被执行。GOTO 语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“：”结尾。语句结构： GOTO <标识符> …… <标识符>：【例 6.8】利用 GOTO 语句求出从 1 加到 5 的总和。 declare @sum int, @count int select @sum=0, @count=1 label_1: select @sum=@sum+@count select @count=@count+1 if @count<=5 goto label_1 select @count @sum （6）WAITFOR 语句。WAITFOR 语句用于暂时停止执行 SQL 语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。语句格式： WAITFOR { DELAY '<时间间隔>' | TIME '<时间>' } 其中，DELAY 用于指定时间间隔，TIME 用于指定某一时刻，其数据类型为 datetime，格式为'hh:mm:ss'。（7）RETURN 语句。RETURN 语句用于无条件地终止一个查询、存储过程或者批处理，此时位于 RETURN 语句之后的程序将不会被执行。语句格式： RETURN [<整数>] 其中，参数“整数”为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。.

(37) 第6章. 6.3. SQL Server 数据库系统. 127. SQL Server 中的存储过程. 在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是 SQL 语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程。存储过程在运算时生成执行方式，所以以后对其再运行时执行速度很快。SQL Server 不仅提供了用户自定义存储过程的功能，而且提供了许多可作为工具使用的系统存储过程。 6.3.1 存储过程的概念存储过程（Stored Procedure）是一组为了完成特定功能的 Transact-SQL 语句集，经编译后存储在数据库中。用户通过存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在 SQL Server 系列版本中存储过程分为两类：系统提供的存储过程和用户自定义的存储过程。系统存储过程主要存储在 master 数据库中，并以 sp_为前缀，它从系统表中获取信息，为系统管理员管理 SQL Server 提供支持。通过系统存储过程，SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。系统存储过程可以在其它其他数据库中被调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。本节主要介绍用户自定义存储过程。 6.3.2 存储过程的优点当利用 SQL Server 创建一个应用程序时，Transact-SQL 是一种主要的编程语言。若运用 Transact-SQL 来进行编程，有两种方法。（1）在本地存储包含 Transact-SQL 语句的应用程序，通过 Transact-SQL 语句向 SQL Server 发送命令来进行数据处理。（2）把部分用 Transact-SQL 编写的程序作为存储过程存储在 SQL Server 中，另外创建应用程序调用这些存储过程对数据进行处理，包括对数据库的操作，不同参数驱使存储过程向调用者返回不同格式或内容的结果集。同时，返回状态值给调用者，指明调用是成功或是失败。存储过程可以嵌套，允许在一个存储过程中调用另一存储过程。我们通常更偏爱于使用第二种方法，即在 SQL Server 中使用存储过程而不是在客户计算机上调用 Transact-SQL 编写的一段程序，原因在于存储过程具有以下优点： 1．存储过程实现模块化程序设计存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。 2．存储过程能够实现较快的执行速度如果某一操作包含大量的 Transact-SQL 代码或被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的 Transact-SQL 语句在每.

(38) 128. 数据库原理与技术（第二版）. 次运行时都要进行编译和优化，速度相对要慢一些。 3．存储过程能够减少网络流量对于同一个针对数据库对象的操作（如查询、修改），如果这一操作所涉及到的 Transact-SQL 语句被组织成一个存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，而不是多条 SQL 语句，从而减少网络流量，降低网络负载。 4．存储过程可被作为一种安全机制使用系统管理员通过对存储过程的执行权限进行限制，从而实现对相应数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。注意：存储过程虽然既有参数又有返回值，但是它与函数不同。存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，在调用存储过程时，在存储过程名字前一定要有 EXEC 保留字。在 SQL Server 中，创建一个存储过程有两种方法：（1）使用 Transact-SQL 命令 Create Procedure。（2）使用图形化管理工具 Enterprise Manager。用 Transact-SQL 创建存储过程是一种较为快速的方法，但对于初学者，使用 Enterprise Manager 更易理解，更为简单。当创建存储过程时，需要确定存储过程的三个组成部分：（1）所有的输入参数以及传给调用者的输出参数。（2）被执行的针对数据库的操作语句，包括调用其它其他存储过程的语句。（3）返回给调用者的状态值，以指明调用是成功还是失败。 6.3.3 使用 Transact-SQL 命令创建存储过程在创建存储过程之前，应该考虑到以下几个方面：（1）在一个批处理中，Create Procedure 语句不能与其它其他 SQL 语句合并在一起。（2）数据库所有者具有默认的创建存储过程的权限，它可把该权限传递给其它其他的用户。（3）存储过程作为数据库对象，其命名必须符合命名规则。（4）只能在当前数据库中创建属于当前数据库的存储过程。用 Create Procedure 创建存储过程的语句格式为： CREATE PROC[EDURE] <存储过程名> [;<分组数字>] [{ @<参数> <参数数据类型>} [VARYING] [= <参数默认值>] [OUTPUT ] ] [,...n ] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS <SQL 语句> [ ...n ] 说明：（1）存储过程的名称必须符合标识符规则，且对于数据库及其所有者必须唯一。要创建局部临时过程，可以在<存储过程名>前面加一个编号符（#<存储过程名>），要创建全局临时过程，可以在<存储过程名>前面加两个编号符（##<存储过程名>）。完整的名称（包括#或##）不能超过 128 个字符。指定过程所有者的名称是可选的。.

(39) 第6章. SQL Server 数据库系统. 129. （2）过程中的参数可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2100 个参数。要求使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它其他过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它其他数据库对象的名称。（3）参数的数据类型可以是所有数据类型（包括 text、ntext 和 image）。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。VARYING 指定由 OUTPUT 参数支持的结果集，仅应用于游标型参数。（4）如果定义了参数的缺省值，那么即使不给出参数值，该存储过程仍能被调用。缺省值必须是常数，或者是空值。（5）OUTPUT。表明该参数是一个返回参数。用 OUTPUT 参数可以向调用者返回信息。 Text 类型参数不能用作 OUTPUT 参数。（6）RECOMPILE 指明 SQL Server 不保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。（7）ENCRYPTION 表明 SQL Server 加密了 syscomments 表，该表的 text 字段是包含有 Create procedure 语句的存储过程文本，使用该关键字无法通过查看 syscomments 表来查看存储过程内容。（8）FOR REPLICATION 选项指明了为复制创建的存储过程不能在订购服务器上执行，只有在创建过滤存储过程时（仅当进行数据复制时过滤存储过程才被执行），才使用该选项。 FOR REPLICATION 与 WITH RECOMPILE 选项是互不兼容的。（9）AS 指明该存储过程将要执行的动作。（10）<SQL 语句>是任何数量和类型包含在存储过程中的 SQL 语句。[ ...n ]表示 1 到多个。一个存储过程的最大尺寸为 128M，用户定义的存储过程必须创建在当前数据库中。下面通过示例来详细介绍如何创建包含有各种保留字的存储过程。【例 6.9】创建数据库 waremanege 的存储过程：返回关于出版物的表 publishers 中所有作者以及他们的文章和说明。在查询分析器中输入以下代码： USE waremanage GO CREATE PROCEDURE pub_infor AS SELECT pub_name,author,unit FROM publishers GO. 单击工具条中“执行查询”按钮，将生成存储过程：pub_infor。【例 6.10】在存储过程中使用参数 int_unitprice，将来在调用时只要给出 int_unitprice 的值，就能显示所有单价大于等于该值的出版物名称及其作者和说明。 USE waremanage GO CREATE PROCEDURE pub_infor @int_unitprice INT AS.