Microsoft Word - SQL03.doc

Transcription

1 数据库操作创建 sample 数据库数据库操作使用向导创建数据库数据库操作查看和修改 sample 数据库数据库操作收缩数据库数据库操作创建数据库维护计划数据库操作删除数据库表操作管理用户定义的数据类型表操作创建产品表 products 表操作添加删除和修改列表操作创建主键表操作创建外键一表操作创建外键二表操作创建 UNIQUE 约束表操作创建 CHECK 约束表操作查看表的定义及其相关性表操作设置用户对表的权限表操作查看表中的数据表操作删除表视图操作创建视图视图操作修改和重命名视图视图操作查看视图的信息视图操作删除视图实例索引操作创建索引索引操作在试图上创建索引索引操作重建索引索引操作重命名索引索引操作删除索引存储过程操作创建存储过程一存储过程操作创建存储过程二存储过程操作修改和重命名存储过程存储过程操作查看存储过程存储过程操作删除存储过程触发器操作创建触发器触发器操作删除触发器 目 录

2 导读 SQL Server 数据库用户的主要工作之一就是进行数据库的开发因为数据库的功能主要就是通过数据库及数据库中的各个对象实现的本章将详细介绍如何创建和管理数据库以及各种数据库对象包括数据库操作表操作视图操作索引操作存储过程操作和触发器操作 2 第三章数据库开发

3 SQL Server 中的数据库是由一组存储了数据的表和其他一些对象组成的包括视图索引存储过程和触发器等等数据库中的数据通常是与一个特定的主题或过程相关联的比如一个工厂仓库的存货信息 Microsoft SQL Server 2000 使用一组操作系统文件映射数据库数据库中的所有数据和对象如表存储过程触发器和视图都存储在下列操作系统文件中 1 主要该文件包含数据库的启动信息并用于存储数据每个数据库都有一个主要数据文件 2 次要这些文件含有不能置于主要数据文件中的所有数据 数据库操作创建 sample 数据库 点击程序组中 Microsoft SQL Server 启动 SQL Server 2000 企业管理器 选择企业管理器图标 展开 SQL Server 组和 SQL Server 服务器在数据库节点上右击鼠标在弹出的菜单上点击新建数据库项目 此时将弹出一个设置数据库属性的对话框在名称右侧的文本框内输入数据库的名称为 sample 在更改的同时对话框的标题改成了数据库属性 sample 第三章数据库开发 3

4 在数据文件标签页可以设置数据库的数据文件的属性如数据文件的名称存放位置初始大小所属文件组以及文件的增长方式这里接受系统默认的设置 在事务日志标签页可以设置数据库的事务日志文件的属性如文件的名称存放位置初始大小以及文件的增长方式这里接受系统默认的设置 还可以在 SQL Server 2000 的 SQL 查询分析器工具中执行 SQL 语句来创建数据库如下图在 SQL 查询分析器中输入创建数据库的语句后点击执行查询图标下面的窗口显示了执行的结果 如果主文件可以包含数据库中的所有数据那么数据库就不需要次要数据文件有些数据库可能足够大故需要多个次要数据文件或使用位于不同磁盘驱动器上的辅助文件将数据扩展到多个磁盘 3 事务日志这些文件包含用于恢复数据库的日志信息每个数据库都必须至少有一个日志文件使用文件和文件组时应注意以下的设计规则 1 文件或文件组不能由一个以上的数据库使用例如文件 sales.mdf 和 sales.ndf 包含 sale 数据库中的数据和对象任何其它数据库都不能使用这两个文件 2 文件只能是一个文件组的成员 通过本例我们学习了如何在企业管理其中创建数据库以及如何使用 SQL 语句创建数据库 4 第三章数据库开发

5 3 数据和事务日志信息不能属于同一文件或文件组 4 事务日志文件不能属于任何文件组文件组对组内的所有文件都使用按比例填充策略当将数据写入文件组时 Microsoft SQL Server 2000 根据文件中的可用空间量将一定比例的数据写入文件 数据库操作使用向导创建数据库 在 SQL Server 企业管理器中还可以使用创建数据库向导来创建数据库点击工具栏上的运行向导图标在出现的选择向导对话框中选择创建数据库向导 组的每个文件而不是将所有的数首先出现的界面概述了向导的功能阅读完该页面之后点击按据先写满第一个钮文件接着再写入下一个文件例如如果文件 f1 有 100 兆字节 (MB) 可用空间文件 f2 有 200MB 可用空间则从文件 f1 中分配一个扩展盘区从文件 f2 中分配两个扩展盘区依在下一个页面中的数据库名称文本框中输入 sample 点击下此类推这样两一步按钮个文件几乎同时填满并且可获得简单的条带化 第三章数据库开发 5

6 此页面中文件名列显示了系统设置的数据文件名称也可以在这里输入自己喜欢的名称初始大小列显示了系统默认的大小为 1MB 可以根据自己的需要设置该数值点击下一步按钮 设置事务日志文件的名称及其初始大小如果需要多个事务日志文件则在下面的空白行依次列出点击下一步按钮 一旦文件组内的所有文件已满 SQL Server 就自动地采用循环方式一次扩展一个文件以容纳更多的数据假定数据库设置为自动增长例如某个文件组由三个文件组成它们都设置为自动增长当文件组中的所有文件的空间用 此处设置数据库文件的增长方式 可以选择是否让数据库文件自 动增长 默认的方式是自动增长 在这种方式下 可以设置文件按一 定的大小还是百分比增长 以及数据库文件是否有最大值限制 不改 完时 只扩展第一 变默认的设置 点击 下一步 按钮 个文件 当第一个 文件已满 不能将 更多的数据写入 该文件组时 扩展 第二个文件 当第 二个文件已满 不 能将更多的数据 写入该文件组时 扩展第三个文件 如果第三个文件 已满不能将更多的数据写入该文件组那么再次扩展第一个文件依此类推 6 第三章数据库开发

7 使用文件和 文件组时 通过允 许跨多个磁盘 多 个磁盘控制器或 RAID 独立磁盘冗 余阵列 系统创建 数据库 可提高数 据库性能 例如 如果计算机中有 四个磁盘 那么可 以创建一个由三 个数据文件和一 个日志文件组成 的数据库 每个磁 盘上放置一个文 件 在对数据进行 访问时 四个读 / 写磁头可以同时 并行地访问数据 从而加速数据库 操作 另外 文件和 文件组允许数据布 局 因为可以在特 定的文件组中创建 表 因为特定表的 所有输入 / 输出都 可以定向到特定的 磁盘 所以性能得 以改善 例如 可 标 以将最常用的表放 在一个文件组中的 一个文件中 该文 件组位于一个磁 设置事务日志文件的增长方式接受默认的配置点击下一步按钮 此页面显示了创建数据库的摘要信息 如数据库名称 数据库文 件和事务日志文件的名称以及他们的增长方式 点击 完成 按钮 完成数据库的创建 可以看到企业管理器中数据库节点下多了一个 sample 数据库图 通过本例我们学习了如何使用创建数据库向导创建数据库 第三章数据库开发 7

8 数据库操作查看和修改 sample 数据库 在 SQL Server 企业管理器中选择数据库节点右击 sample 数据库图标在弹出的菜单中选择属性 常规页面显示了数据库的概要信息 文件组 页面显示了数据库中现有的文件组 此时数据库中只 有一个主文件组 PRIMARY 点击 PRIMARY 下面一行的 名称 列 输入 SECOND 创建第二个文件组 SECOND 点击 确定 按钮后该 文件组即可生效 盘上而将数据库中其它的不常访问的表放在另一个文件组中的其它文件中该文件组位于第二个磁盘上在 Microsoft SQL Server 2000 中数据库必须至少包含一个数据文件和一个事务日志文件数据和事务日志信息从不混合在同一文件中并且每个文件只能由一个数据库使用 SQL Server 使用各数据库的事务日志来恢复事务事务日志是数据库中已发生的所有修改和执行每次修改的事务的一连串记录事务日志记录每个事务的开始它记录了在每个事务期间对数据的更改及撤消所做更改以后如有必要所需的足够信息对于一些大的操作 8 第三章数据库开发

9 如 CREATE INDEX 事务日 志则记录该操作 发生的事实 随着 数据库中发生被 记录的操作 日志 会不断地增长 事务日志记 录页的分配和释 放 以及每个事务 的提交或回滚 这 允许 SQL Server 采用下列方式应 用 前滚 或收回 回滚 每个事 务 1 在应用 事务日志时 事务 将前滚 SQL Server 将每次修改 后的映像复制到 数据库中 或者重 新运行语句 如 CREATE INDEX 这些操作将 按照其原始发生 顺序进行应用 此 过程结束后 数据 库将处于与事务 日志备份时相同 的状态 2 当收回 未完成的事务时 事务将回滚 再次打开 sample 数据库的属性对话框数据文件页面显示了数据库中的现有的数据库文件为了创建第二个数据库文件点击第二行中的文件名列输入 sample_data_2 改变文件的分配空间为 10 MB 改变其隶属的文件组为刚刚创建的 SECOND 事务日志页面显示了数据库中现有的事务日志文件为了创建第二个事务日志文件点击第二行的文件名列输入 sample_log_2 改变其分配空间为 2 MB 选项页面显示了数据库选项的设置可以在此页面中方便地改变这些数据库选项 通过本例文件和文件组 我们学习了如何查看数据库信息以及如何更改数据库 第三章数据库开发 9

10 数据库操作收缩数据库 在 SQL Server 企业管理器中右击要收缩的数据库在弹出的菜单中选择所有命令子菜单下的收缩数据库项 在收缩后文件中的最大可用空间中输入收缩后数据库中剩余的可用空间量选择在收缩前将页移到文件起始位置使释放的文件空间保留在数据库文件中并使包含数据的页移到数据库文件的起始位置 点击调度命令创建或更改自动收缩数据库的频率和时间点击更改按钮对调度进行具体的设置在弹出的窗口中选择调度发生的时机 SQL Server 将所有修改前的映像复制到 BEGIN TRANSACTION 后的数据库如果遇到表示执行了 CREATE INDEX 的事务日志记录则会执行与该语句逻辑相反的操作这些前映像和 CREATE INDEX 逆转将按照与原始顺序相反的顺序进行应用在检查点处 SQL Server 确保所有已修改的事务日志记录和数据库页都写入磁盘在重新启动 SQL Server 时所发生的各数据库的恢复 过程中仅在不知道事务中所有的数据修改是否已经从高速缓冲中实际写入磁盘时才必须前滚事务因为检查点强迫所有修改的页写入磁盘所以检查点表示启动恢复必须开始前滚事务的位置 10 第三章数据库开发

11 SQL Server 2000 允许收缩数 据库的每个文件 以除掉未使用的 页面 数据文件和 事务日志文件都 能被收缩 收缩可 以成组进行 也可 以个别进行 可以 指定时间间隔使 得数据库在给定 的时间自动收缩 这时收缩操作是 在后台发生的 不 会影响任何数据 库用户的活动 当使用了 ALTER DATABASE 的 AUTO_SHRINK 选项设置数据库 的自动收缩 或者 是系统存储过程 sp_dboption 时 每当数据库中有 大量的空闲空间 时 数据库就会收 缩 然而 如果要 移除的空闲空间 百分比不能被设 置的话 则空闲空 间将会尽可能地 被释放 在上一个窗口中选择反复出现点击右侧的更改按钮设定调度发生的频率和每日发生的频率以及持续时间 在步骤 2 中还可以选择对单个的文件进行收缩方法是点击文件在数据库文件列表中选择一个要收缩的文件在收缩操作中选择一种收缩文件的方式点击确定按钮 除了手工设置数据库的收缩外还可以设置让数据库自动收缩双击要收缩的数据库图标在数据对话框的选项页面中选中自动收缩选项点击确定按钮 通过本例行调度 我们学习了如何收缩数据库以及如何对数据库收缩进 第三章数据库开发 11

12 数据库操作创建数据库维护计划 在 SQL Server 企业管理器中右击 sample 数据库图标在弹出的菜单中选择所有任务下的维护计划 首先出现的页面显示了数据库维护计划的功能阅读后点击下一步按钮 选择为其创建维护计划的数据库 sample 数据库点击下一步按钮 此时系统已默认地选择了 不能收缩比其最初的规模小的完全的数据库所以假定一个数据库在创建时的初始大小是 10M 然后增长到了 100M 如果此时数据库中所有的数据都被删除了则它能收缩到的最小规模也是 10M 不过使用 DBCC SHRINKFILE 语句可以将单个数据库文件收缩到小于初始规模的大小如果使用这种方法就必须对每个文件进行操作而不能对整个数据库收缩事务日志文件的收缩有固定的界线虚拟日志的规模决定了可能的减少因此日志文件决不能收缩到比虚拟日志文件更小的规模收缩事务日志文件将删除未使用的虚拟日志文 12 第三章数据库开发

13 选择 重新组织数据和索引页 确保了数据库页包含均匀分发的 数据量和可用空间从而允许以后更快地增长选择从数据库文件件但会留下至中删除未使用的空间可以压缩数据文件还可以通过调度选择少一个虚拟日调度优化作业的发生点击下一步按钮志文件 SQL Server 2000 中 DBCC SHRINK DATABASE 或 DBCC SHRINKFILE 操作 会试图立即将事 务日志文件收缩 到要求的尺寸 在 取整的条件下应该在收缩文件之前就截取好日志文件已减少逻 选择 检查数据库完整性 选项 系统将对数据库内的数据和数 辑日志文件的大 据页执行内部一致性检查 以确保系统或软件问题没有损坏数据 点 小 并且标记出不 击下一步按钮 含有任何逻辑日 志的虚拟日志 数据库维护 计划向导可以用 来帮助您设置核 心维护任务 这些 任务对于确保数 据库的良好执行 定期备份以防系 统失败 以及检查 一致性方面很有选择作为维护计划的一部分来备份数据库选项备份数据库必要数据库维护可以避免由于系统错误或用户的误操作等原因引起的数据损失选择计划向导将创建文件的存放位置为磁盘点击下一步按钮 SQL Server 2000 作业 该作业自动 按所计划的间隔 执行这些维护任 务 第三章数据库开发 13

14 选择保存备份文件的目录和其他的一些选项数据库和日志备份可以保留一段指定时间这使得用户可以创建备份的历史记录如果需要将数据库还原到最近一次备份之前的时间就可以使用这些备份点击下一步按钮 选择 作为维护计划的一部分来备份事务日志 选项 点击下一 步按钮 选择备份文件的目录为默认的目录件点击下一步按钮 选择删除较早版本的备份文 可以进行调度使之自动运行的维护任务包括 1 通过用新填充因子重建索引以重新组织数据和索引页上的数据这就确保了数据库页包含均匀分发的数据量和可用空间从而允许以后更快地增长有关更多信息请参见填充因子 2 通过删除空数据库页压缩数据文件 3 更新索引统计以确保查询优化器有关于表中数据值分发的最新信息这使得查询优化器能够更好地判断访问数据的最佳方法因为它有关于存储在数据库中的数据的更多信息虽然 SQL Server 自动定期更新索引统计但是此选项可以强迫统计立即更新 14 第三章数据库开发

15 4 对数据库内的数据和数据页执行内部一致性检查以确保系统或软件问题没有损坏数据 5 备份数据库和事务日志文件数据库和日志备份可以保留一段指定时间这使您可以创建备份的历史记录如果需要将数据库还原到最近一次备份之前的时间就可以使用这些备份 6 设置日志传送日志传送允许将事务日志从一个数据库源不断地传送到另一个数据库目的通过使目的数据库与源数据库保持同步使您得以具有备用服务器并提供将查询处理工作从主计算机源服务器卸载到只读目的服务器的方法 维护任务产生的结果可以作为报告写到文本文件 HTML 文件或 msdb 数据库的 sysdbmaintplan_history 表中报告也可以通过电子邮件发送给操作员如果要通过电子邮件发送给操作员则必须首先创建一个操作员点击下一步按钮 选择如何存储维护计划记录点击下一步按钮 在 计划名 文本框中输入此数据库维护计划的名称 下面的文 本框中显示了此维护计划的摘要信息 确认无误后点击 完成 按钮 即可完成维护计划的创建 通过本例我们学习了如何创建数据库维护计划这些计划可以确保数据库得以很好的执行 第三章数据库开发 15

16 数据库操作删除数据库 在要删除的数据库图标上右击鼠标在弹出的菜单中选择删除或者点击工具栏上的删除图标 在弹出的确认删除对话框中选择按钮 还可以使用 Transact-SQL 中的 DROP DATABASE 语句来删除数据库 DROP DATABASE 的语法如下 维护任务产 生的结果可以作 为报告写到文本文 件 HTML 文件或 msdb 数据库的 sysdbmaintplan _history 表中 报 告也可以通过电 子邮件发送给操 作员 当不再需要 数据库 或如果它 被移到另一数据 库或服务器时 即 可删除该数据库 数据库删除之后 文件及其数据都 从服务器上的磁 盘中删除 一旦删 除数据库 它即被 永久删除 并且不 能进行检索 除非 使用以前的备份 不能分离系统数 据 库 msdb master model 和 tempdb 16 第三章数据库开发

17 建议在数据库删除之后备份 master 数据库因为删除数据库将更新 master 中的系统表如果 master 需要还原则从上次备份 master 之后删除的所有数据库都将仍然在系统表中有引用因而可能导致出现错误信息 在删除数据库之前可以在 SQL Server 查询分析器运行 sp_helpdb 存储过程查看数据库信息如下图 在查询分析器中执行 DROP DATABASE 语句命令删除 sample 数据库如下图 此时在企业管理器中被删除的 sample 数据库在数据库节点中不再显示了如下图 通过本例我们学习了如何删除一个数据库 第三章数据库开发 17

18 表操作管理用户定义的数据类型 可以使用系统过程 sp_addtype 来增加一个用户定义的数据类型下图表明了 sp_addtype 过程的用法 可以在查询分析器中使用下面的语句创建一个名为 birthday 的用户定义数据类型该数据类型是基于 datetime 的并允许为空值 在 SQL Server 企业管理器中创建用户定义数据类型的方法为选择数据库中的用户定义的数据类型节点在右侧的空白处右击鼠标选择新建用户定义数据类型项 表是包含数据库中所有数据的数据库对象表定义为列的集合与电子表格相似数据在表中是按行和列的格式组织排列的每行代表唯一的一条记录而每列代表记录中的一个域例如在包含公司雇员数据的表中每一行代表一名雇员各列分别表示雇员的详细资料如雇员编号姓名地址职位以及家庭电话号码等设计数据库时应先确定需要什么样的表各表中都有哪些数据以及各个表的存取权限等等在创建和操作表的过程中将对表进行更为细致的设计 18 第三章数据库开发

19 创建一个表 最有效的方法是 将表中所需的信 息一次定义完成 包括数据约束和 附加成分 也可以 先创建一个基础 表 向其中添加一 些数据并使用一 段时间 这种方法 使您可以在添加 各种约束 索引 默认设置 规则和 其它对象形成最 终设计之前 发现 哪些事务最常用 哪些数据经常输 入 最好在创建 表及其对象时预 先将设计写在纸 上 设计时应注 意 1 表所包含 的数据的类型 2 表的各列及 每一列的数据类 型 如果必要 还 应注意列宽 3 哪些列允许空值 4 是否要使用 以及何时使用约 束 默认设置或规 则 在出现的设定用户定义数据类型的属性对话框中在名称文本框中输入 birthday 在数据类型列表框中选择 datetime 这样就创建了一个基于 datetime 类型的用户定义数据类型 如果需要删除用户定义的数据类型可以使用 Transact-SQL 中的 sp_droptype 系统过程或使用企业管理器来删除在企业管理器中删除时右击要删除的用户定义数据类型在弹出的对话框中选择删除或点击工具栏上的删除图标 在除去对象的对话框中显示了要删除的数据库对象点击显示相关性按钮可以查看依赖于此数据类型的对象点击全部除去按钮即可将所选择的对象删除 通过本例我们学习了如何创建用户定义的数据类型用户定义的数据类型使得用户在定义表列的数据类型时可以一致地定义多个列 第三章数据库开发 19

20 表操作创建产品表 products 在 SQL Server 企业管理器中的数据库下的表节点上右击鼠标在弹出的菜单上点击新建表 在出现的表设计器中为表中加入五个列 ProductID ProductName QuantityPerUnit UnitPrice SupplierID 其数据类型和长度如下图由于每一件产品必须有唯一标识的 ID 和产品名称所以前两列是不允许为空的 点击工具栏上的属性图标可以查看表的属性在描述文本框中输入对该表的描述产品表 5 所需索引的类型哪里需要索引哪些列是主键哪些是外键设计表时首先要为每列指派数据类型数据类型定义了各列所允许的数据值若要为列指派数据类型请使用 SQL Server 2000 的基本数据类型或基于这些系统数据类型创建用户定义的数据类型例如如果列中只含有姓名可以将该列定义为字符型以此类推如果列中只含有数值就可以将该列定义为数字数据类型用户定义数据类型基于 SQL Server 2000 中的系统数据类型当多个表的列中要存储同样类型的 20 第三章数据库开发

21 数据且想确保这些列具有完全相同的数据类型长度和为空性时可使用用户定义数据类型例如可以基于 char 数据类型创建名为 postal_code 的用户定义数据类型创建用户定义的数据类型时必须提供以下三个参数 1 名称 2 新数据类型所依据的系统数据类型 3 为空性数据类型是否允许空值如果为空性未明确定义系统将依据数据库或连接的 ANSI Null 默认设置进行指派设计表时需要识别列的有效值并决定如何强制实现列中数据的完整性 SQL Server 2000 提供多种强制列中数据完整性的机制 点击工具栏上的保存更改脚本图标可以将创建表的脚本保存下来如下图文本框中显示了创建表的脚本语句用户可以通过此窗口查看创建表的语句点击是按钮 在弹出的另存为对话框中为保存的脚本文件取一个名称该文件将被存为一个后缀名为 sql 的脚本文件 完成表的设计之后可以点击工具栏上的保存图标在弹出的选择名称对话框中输入表的名称 Products 点击确定按钮 通过本例我们学习了如何创建数据库中的表 第三章数据库开发 21

22 表操作添加删除和修改列 在已有的表中添加删除和修改列可以使用 Transact-SQL 中的 ALTER TABLE 语句使用 ALTER TABLE 语句的语法为在 SQL Server 查询分析器中输入如下语句往 Products 表中插入一个列 column_test 该列的数据类型为 varchar 允许为空 在查询分析器中输入如下语句修改表 Products 中 column_test 的数据类型为 int 如下图 PRIMARY KEY 约束 FOREIGN KEY 约 束 UNIQUE 约束 CHECK 约束 DEFAULT 定义和为空性一 PRIMARY KEY 约束一个表通常都有一个列或一个列的组合唯一标识了表中的一 行这样的列就称为是表的主键 Primary Key 主键可以强制地维护表的实体完 整性一个表只能有一个主键并且主键中的列也不能接受 null 值当在表中定义了一个主键时 SQL Server 2000 就会为主键列创建一个唯一索引以维护数据的唯一性这个索引使得再用主键查询时可以达到较快的访问速度 22 第三章数据库开发

23 往一个已存 在的表中添加主 键约束时 SQL Server 2000 将检 查表中该列的数 据 以保证这些数 据满足主键的规 则 1 没有 null 值 2 没有重复 的数值 如果这两 个条件中有一个 不满足的话 SQL Server 将返回一个 错误并且不会创 建主键约束 SQL Server 会自动在逐 渐约束上添加一 个唯一索引 以确 保主键的唯一性 要求 如果表中不 存在簇索引 或者 非簇索引并不明 确 则系统将创建 一个唯一的簇索 引以加强主键约 束 在查询分析器中输入如下语句如下图 删除表 Products 中 column_test 在企业管理器中修改表列定义的方法为展开 SQL Server 服务器 和相应的数据库 点击 表 节点 在相应的表上右击鼠标 在弹出 的菜单中选择 设计表 在设计表的窗口中可以在最后一行中输入新列的名称数据类型长度及是否允许为空 第三章数据库开发 23

24 如果新增加的列放在某一个特定的位置则可以在该位置上右击鼠标在弹出的菜单中选择插入列项如下图 义 此时在刚才的位置将出现一个空行 可以在该行输入新增列的定 在删除列时可以点击左侧图标所在的列选择要删除的列这时被选中的列将反色显示如需选择多个列则可以按住 Shift 键选取连续的列而按住 Ctrl 键可以选取多个不连续的列在选取的列上右击鼠标在弹出的菜单上点击删除列项如下图 二 FOREIGN KEY 约束外键是用于建立和加强两个表数据之间的链接的一列或多列通过将保存表中主键值的一列或多列添加到另一个表中可创建两个表之间的链接这个列就成为第二个表的外键当创建或更改表时可通过定义 FOREIGN KEY 约束来创建外键 FOREIGN KEY 约束并不仅仅只可以与另一表的 PRIMARY KEY 约束相链接它还可以定义为引用另一表的 UNIQUE 约束 FOREIGN KEY 约束不允许空值但是如果任何组合 FOREIGN KEY 约束的列包含空值则将跳过 FOREIGN KEY 约束的校验 通过本例我们学习了如何在表定义之后重新修改表中各列的定义 24 第三章数据库开发

25 FOREIGN KEY 约束可引用同一数据库中的表或同一表自引用表内的列尽管 FOREIGN KEY 约束的主要目的是控制存储在外键表中的数据但它还可以控制对主键表中数据的修改例如如果在 publishers 表中删除一个出版商而这个出版商的 ID 在 titles 表中记录书的信息时使用了则这两个表之间关联的完整性将被破坏 titles 表中该出版商的书籍因为与 publishers 表中的数据没有链接而变得孤立了 FOREIGN KEY 约束防止这种情况的发生如果主键表中数据的更改使之与外键表中数据的链接失效则这种更改是不能实现的 表操作创建主键 使用 Transact-SQL 语句在表中创建主键时中定义列时使用如下语法 可以在创建表的语句 在查询分析器中输入如下的语句 可以创建一个表 Suppliers 该表中的 SupplierID 列被定义为了主键 在企业管理器中定义主键的方法为展开数据库服务器以及相应的数据库节点点击表节点在相应的表上右击鼠标在弹出的对话框中选择设计表 第三章数据库开发 25

26 选择要设置为主键的列 可以选择一个列 也可以选择多个列 在所选的列上右击鼠标 在弹出的菜单中选择 设置主键 或点击工 具栏上的图标 在设计面板的空白处右击鼠标在弹出的对话框中选择属性查看表的属性如下图 在出现的表属性对话框中选择索引 / 键标签页可以看到在选定的索引列表中的项目为 PK_Products 这表明已经成功创建了一个主键在下面的列表框中显示了该主键所包含的列 从而确保了引用完整性如果试图删除主键表中的行或更改主键值而该主键值与另一个表的 FOREIGN KEY 约束值相关则该操作不可实现若要成功更改或删除 FOREIGN KEY 约束的行可以先在外键表中删除外键数据或更改外键数据然后将外键链接到不同的主键数据上去 FOREIGN KEY 约束是索引的候选约束其原因有以下两点 1 对 PRIMARY KEY 约束的更改可由相关表中的 FOREIGN KEY 约束校验 2 当在查询中组合来自相关表中的数据时经常在联接条件中使用外键列方法是 通过本例我们学习了如何为一个表定义主键 26 第三章数据库开发

27 将一个表的 FOREIGN KEY 约束中的列与另一个表中的主键列或唯一键列匹配索引使 SQL Server 2000 得以快速查找外键表中的相关数据但是创建索引不是必需的即使没有在表间定义 PRIMARY KEY 或 FOREIGN KEY 约束也可以对来自两个相关表中的数据进行组合但两个表间的外键关系说明已用其键作为条件对其进行了优化以便组合到查询中 表操作创建外键一 上一个例子中我们创建了表 Suppliers 下面我们将以表 Suppliers 中的 SupplierID 列为主键方以表 Products 中的 SupplierID 列为外键创建一个外键约束启动企业管理器展开 sample 数据库在 Products 表上右击鼠标在弹出的菜单上点击设计表进入表的设计窗口 在设计面板的空白处右击鼠标在弹出的菜单中选择关系 可以看到如下的对话框由于现在数据库中没有创建任何的关系所以窗口中的大部分项目都是以灰色显示的 第三章数据库开发 27

28 点击按钮这时系统为新建的关系取了一个以 FK_ 开始的名称显示在关系名文本框中 在主键表列表框中选择一个现有的表此处选择表 Suppliers 在下面的列列表框中选择 SupplierID 列作为主键列 在外键表列表框中选择表 Products 在下面的列列表框中选择 ProductID 列作为外键列 级联引用完整性约束当用户试图删除或更新外键所指向的键时级联引用完整性约束使您得以定义 SQL Server 2000 所采取的操作 CREATE TABLE 和 ALTER TABLE 语句的 REFERENCES 子句支持 ON DELETE 和 ON UPDATE 子句 [ON DELETE {CASCADE NO ACTION}] 和 [ON UPDATE {CASCADE NO ACTION}] 如果没有指定 ON DELETE 或 ON UPDATE 则默认为 NO ACTION NO ACTION 指定的行为与 SQL Server 的早期版本中发生的行为相同 通过本例我们学习了如何在表的属性对话框中定义表的外键 28 第三章数据库开发

29 ON DELETE NO ACTION 指定如果试图删除某行而该行含有由其它表的现有行中的外键所引用的键则产生错误并回滚 DELETE ON UPDATE NO ACTION 指定如果试图更新某行中的键值而该行含有由其它表的现有行中的外键所引用的键则产生错误并回滚 UPDATE CASCADE 允许在表间级联键值的删除或更新操作这些表的外键关系可追溯到执行修改的表不能为任何具有 timestamp 列的外键和主键指定 CASCADE ON DELETE CASCADE 指定如果试图删除某行而该行含有由其它表的现有行中的外键所引用的键则也将删除所有包含那些外键的行 表操作创建外键二 外键还可以通过关系图来创建展开 sample 数据库点击关系图节点在右侧的面板中右击鼠标在弹出的菜单点击新建数据库关系图 这时可以使用创建数据库关系图向导来创建关系图首先出现的欢迎创建显示了向导的功能阅读后点击下一步按钮 在选择要添加的表窗口中加入表 Products 和 Suppliers 如果选择了自动添加相关的表则在选取表的时候会把与这个表相关的表也添加进来 第三章数据库开发 29

30 在完成向导的窗口中显示了已经添加的表确认无误后点击完成按钮 在设计关系图的窗口中显示了加入的两个表 Suppliers Products 和 由于表 Products 中的 SupplierID 列与表 Suppliers 中的 SupplierID 列存在依赖关系需要在这两个列之间建立外键关系用鼠标点击表 Products 中的 SupplierID 列左侧的以选择该列然后拖动鼠标在表 Suppliers 中的 SupplierID 列上释放鼠标如下图 如果在目标表上也定义了级联引用操作则对从那些表中删除的行同样采取指定的级联操作 ON UPDATE CASCADE 指定如果试图更新某行中的键值而该行的键值由其它表的现有行中的外键所引用则所有外键值也将更新成为该键指定的新值如果在目标表上也定义了级联引用操作则对在那些表中更新的键值同样采取指定的级联操作级联引用操作示例有基于 Northwind 中 Products 表上的 FK_Products _Suppliers 约束该约束建立了从 Products 表中的 SupplierID 列到 Suppliers 表中的 SupplierID 主键列的外键关系 30 第三章数据库开发

31 如果为约束指定了 ON DELETE CASCADE 则删除 SupplierID=1 的 Suppliers 表中的行时也将删除 SupplierID=1 的 Products 表中的三行如果为该约束指定了 ON UPDATE CASCADE 则更新 Suppliers 表中从 1 到 55 的 SupplierID 值也将更新 Products 表中 SupplierID 值当前等于 1 的三行的 SupplierID 值不能为具有 INSTEAD OF 触发器的表指定级联操作为表定义级联操作后就不能向该表添加 INSTEAD OF 触发器 在出现的对话框中可以设定关系的名称在主键表和外键表下面的列列表框中应该显示的是 SupplierID 还可以选择是否级联更新和删除相关的记录 在上面的窗口中点击 确定 按钮之后 关系图设计窗口中两个 表之间将出现一根连线 其中 的一端表示外键方 的一端表示 主键方 点击保存图标可以为关系图取一个名称将其保存下来 通过本例创建外键 我们学习了如何在数据库关系图中使用图形化的方式 第三章数据库开发 31

32 表操作创建 UNIQUE 约束 在数据库关系图中右击表 Products 然后从弹出的菜单中选择属性命令或者点击工具栏上的属性图标 或者展开 sample 数据库在表 Products 上右击鼠标在弹出的菜单中选择设计表命令进入表设计器 在表设计器中右击鼠标然后从弹出的菜单中选择属性命令 单 独 的 DELETE 或 UPDATE 语句可 启动一系列级联 引用操作 例如 假设有一个数据 库包含三个表 TableA TableB 和 TableC 针对 TableA 中的主键 用 ON DELETE CASCADE 定义 TableB 中的外键 针对 TableB 中的 主键 用 ON DELETE CASCADE 定义 TableC 中的外键 如果 DELETE 语 句删除 TableA 中 的行 则该操作也 将删除 TableB 中 具有与 TableA 中 所删除的主键匹 配的任何外键中 的所有行 然后删 除 TableC 中具有 与 TableB 中所删 除的主键匹配的 任何外键中的所 有行 32 第三章数据库开发

33 由单个 DELETE 或 UPDATE 触发的一系列级联引用操作必须构成不包含循环引用的树在 DELETE 或 UPDATE 所产生的所有级联引用操作的列表中每个表只能出现一次级联引用操作树到任何给定表的路径必须只有一个树的任何分支在遇到指定了 NO ACTION 或默认为 NO ACTION 的表时终止三 UNIQUE 约束可使用 UNIQUE 约束确保在非主键列中不输入重复值尽管 UNIQUE 约束和 PRIMARY KEY 约束都强制唯一性但在强制下面的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束 在表 Products 属性对话框中选择索引 / 键标签页 点击 新建 按钮 系统分配的名称将出现在 索引名 文本框中 在列名下展开列的列表选择要将约束附加到的列 ProductName 若要将约束附加到多个列在后续行中选择其它的列选择创建 UNIQUE 复选框选择约束选项 通过本例我们学习了如何为表创建唯一性约束 第三章数据库开发 33

34 启动企业管理器展开数据库 sample 选取表节点在表 Products 上右击鼠标在弹出的菜单中点击设计表选项 图 表操作创建 CHECK 约束 在出现的表设计面板的空白处右击鼠标 CHECK 约束选项 在弹出的菜单中点击 这时将出现表的属性对话框中的 CHECK 约束标签页如下 1 非主键的一列或列组合一个表可以定义多个 UNIQUE 约束而只能定义一个 PRIMARY KEY 约束 2 允许空值的列允许空值的列上可以定义 UNIQUE 约束而不能定义 PRIMARY KEY 约束 FOREIGN KEY 约束也可引用 UNIQUE 约束在向表中的现有列添加 UNIQUE 约束时默认情况下 SQL Server 2000 检查列中的现有数据确保除 NULL 外的所有值均唯一如果对有重复值的列添加 UNIQUE 约束 SQL Server 将返回错误信息并不添加约束 34 第三章数据库开发

35 SQL Server 自动创建 UNIQUE 索引来强制 UNIQUE 约束的唯一性要求因此如果试图插入重复行 SQL Server 将返回错误信息说明该操作违反了 UNIQUE 约束并不将该行添加到表中除非明确指定了聚集索引否则默认情况下创建唯一的非聚集索引以强制 UNIQUE 约束删除 UNIQUE 约束以删除对约束中所包含列或列组合输入值的唯一性要求如果相关列是表的全文键则不能删除 UNIQUE 约束 点击新建按钮选定的约束列表框显示了由系统分配的新约束名系统分配的名称以 CK_ 开始后跟表名 在约束表达式框中为 CHECK 约束键入 SQL 表达式如将 QuantityPerUnit 列的数据限制在 0 到 1000 之间 关闭设计表 Products 的设计器系统将提示是否保存对表 Products 的更改点击是按钮保存所创建的 CHECK 约束 通过本例我们学习了如何在表中创建 CHECK 约束这使得表中的数据必须满足 CHECK 约束的条件才能存入数据库 第三章数据库开发 35

36 下 表操作查看表的定义及其相关性 可以使用 sp_help 系统存储过程查看表的定义 sp_help 的用法如 启动 SQL Server 查询分析器在查询分析器中输入如下的语句以便查看表 sample 的定义信息点击工具栏上的执行查询图标或按 F5 键窗口的下部显示了表 Products 的定义 还可以使用企业管理器查看表的定义 在企业管理器中 选取 sample 数据库中的 表 节点 在右侧窗口中的 Products 表上右击鼠 标在弹出的菜单中点击属性 或者点击工具栏上的属性图标 打 开表的属性对话框 四 CHECK 约束 CHECK 约束通过限制输入到列中的值来强制域的完整性这与 FOREIGN KEY 约束控制列中数值相似区别在于它们如何判断哪些值有效 FOREIGN KEY 约束从另一个表中获得有效数值列表 CHECK 约束从逻辑表达式判断而非基于其它列的数据例如通过创建 CHECK 约束可将 salary 列的取值范围限制在 $15,000 至 $100,000 之间从而防止输入的薪金值超出正常的薪金范围可以通过任何基于逻辑运算符返回结果 TRUE 或 FALSE 的逻辑布尔表达式来创建 CHECK 约束 36 第三章数据库开发

37 对单独一列可使用多个 CHECK 约束按约束创建的顺序对其取值通过在表一级上创建 CHECK 约束可以将该约束应用到多列上例如多列 CHECK 约束可以用来判明 country 列值为 USA 且 state 列值为两个字母值所对应的行这样就允许在一处同时检查多个条件在现有表中添加 CHECK 约束时该约束可以仅作用于新数据也可以同时作用于现有的数据默认设置为 CHECK 约束同时作用于现有数据和新数据当现有数据已符合新的 CHECK 约束或业务规则要求从此开始强制约束时使用约束仅作用于新数据选项比较有用 在表属性对话框中可以看到各个列的定义在列名前面有标的表明该列是表的主键 要查看表的相关性可以在 Products 表上右击鼠标在弹出的菜单中选择所有任务下的显示相关性 下面的窗口显示了与 Products 表相关的表由于 Products 表与 Suppliers 表具有外键关系所以 Products 表是依附于 Suppliers 表的 图 通过本例的相关关系 我们学习了如何查看表的定义及其与其他数据库对象 第三章数据库开发 37

38 表操作设置用户对表的权限 在企业管理器中双击 Products 表打开表的属性窗口如下图 点击权限按钮可以设置用户或数据库角色对该表所拥有的各种操作的权限如下图 点击列按钮可以设置选定用户对每一列的权限如下图 不过添加约束而不检查现有数据时一定要小心因为这样做将放弃 SQL Server 2000 对表强制完整性规则的控制在下列情况可以禁用现有 CHECK 约束 1 INSERT 和 UPDATE 语句以允许不经约束确认修改表中的数据在执行 INSERT 和 UPDATE 语句时如果新数据违反约束或约束应只适用于数据库中已有的数据那么可禁用 CHECK 约束 2 复制处理如果该约束为源数据库所特有则在复制时请禁用 CHECK 约束复制表时表定义和数据从源数据库复制到目标数据库这两个数据库通常但不一定在不同的服务器上 通过本例我们学习了如何设置用户在表中的权限可以通过设置权限限制用户执行某些操作的能力 38 第三章数据库开发

39 如果源数据库 特有的 CHECK 约 束在复制时未禁 用 则可能无谓地 妨碍向目标数据库 输入新数据 ( 五 )DEFAULT 定义 记录中的每一 列均必须有值 即 使它是 NULL 可 能会有这种情况 当向表中装载新行 时可能不知道某一 列的值 或该值尚 不存在 如果该列 允许空值 就可以 将该行赋予空值 由于有时不希望有 可为空的列 因此 如果合适 更好的 解决办法可能是为 该列定义 DEFAULT 定义 例如 通常 将数字型列的默认 值指定为零 将字 符串列的默认值指 定为暂缺 表操作查看表中的数据 可以使用 SELECT 语句查询表中的数据如在查询分析器中执行 SELECT * FROM Products 语句就可以查询 Products 表中的数据如下图 如在企业管理器中查询表中数据则要展开 sample 数据库在 Products 表上右击鼠标在弹出的菜单中点击打开表命令下的返回所有行可以查看表中的所有数据行 下图显示了表 Products 中的所有数据行 第三章数据库开发 39

40 如果不需要查看所有的数据 只是想查看表中位于前面的数行 则可以右击表 Products 在弹出的菜单中点击 打开表 命令下的 返 回首行 首先弹出的对话框中要求输入获取的行数在要获取的最大行数文本框中输入 10 取出 10 行数据 在显示数据行的窗口中显示了表 Products 的 10 行数据 当将某行装载到为行定义了 DEFAULT 的表中时若没有指定列 值则是隐性要求 SQL Server 2000 将默认值装载到该列中也可以使用 INSERT STATEMENT 语句 DEFAULT VALUES 子句显式要求 SQL Server 将默认值插入列中如果列不允许空值且没有 DEFAULT 定义就必须明确地指定列值否则 SQL Server 会返回错误信息指出该列不允许空值如下定义的列不能创建 DEFAULT 定义 1 timestamp 数据类型 IDENTITY 或 OWGUIDCOL 属性 2 已有 DEFAULT 定义或 DEFAULT 对象 40 第三章数据库开发

41 默认值必须与 DEFAULT 定义适用的列的数据类型相一致例如 int 列的默认值必须是整数而不是字符串在对表中现有列添加 DEFAULT 定义 时默认情况下 SQL Server 2000 只对添加到表中的新数据行应用新默认值而使用以前的 DEFAULT 定义插入的现有数据不受影响不过当在已有表中添加新列时可指定 SQL Server 对表中已有行的新列插入默认值由 DEFAULT 定义指定而不是插入空值删除 DEFAULT 定义后如果新行中的该列未输入数值则 SQL Server 自动插入空值而非默认值不过表中的已有数据保持不变 还可以通过定义复杂的查询方式来查看表中的数据 在企业管理 器中 右击表 Products 在弹出的菜单中点击 打开表 命令下面的 查询 在显示数据的窗口中从上到下的几个部分分别为关系图窗格 用 于选取要获取数据的列 网格窗格 显示列名 定义列的排序规则 SQL 窗格显示获取数据的 SELECT 语句 结果窗格 显示获取的结果 集 在定义了要获取数据的列及其排序规则后 SQL 窗格显示了获取数据的 SELECT 语句结果窗格则显示了所获取的数据 通过本例我们学习了如何查看表中的数据可以根据自己的需要灵活地选择数据显示的方式 第三章数据库开发 41

42 表操作删除表 删除表时在企业管理器中选择要删除的表在表上右击鼠标点击删除按钮或者点击工具栏上的删除图标 在 除去对象 的窗口中显示了将要除去的对象 对象 列为要 删除的对象名称 所有者 列为对象的所有者 类型 列中的图标 表明了对象的名称 在上一步中点击 显示相关性 按钮可以查看与该表有相关关系 的对象 点击上一步中的 全部除去 按钮可以把所选的对象全部删 除 有时需要删除表如当要实现新的设计或释放数据库的空间时删除表时表的结构定义数据全文索引约束和索引都永久地从数据库中删除原来存放表及其索引的存储空间可用来存放其它表如果不想等待临时表自动除去可明确删除临时表如果要删除通过 FOREIGN KEY 和 UNIQUE 或 PRIMARY KEY 约束相关的表必须首先删除具有 FOREIGN KEY 约束的表如果要删除 FOREIGN KEY 约束中引用的表而不是删除整个外键表则必须删除 FOREIGN KEY 约束 通过本例我们学习了如何删除一个不再需要的表 42 第三章数据库开发

43 视图是一个虚拟表其内容由一个查询所定义如同真正的表一样视图也含有一系列的命名列和一行行的数据不过视图并不是作为存储数据的对象而存在的它的行和列引用了表中的数据并且当视图被引用时其数据也是动态生成的在一个视图中最多可定义一个或多个表的 1024 个列定义的行数只受所引用表的行数限制视图在定义以后就可以像表一样的被引用了当视图被引用时视图执行包含在它定义中的 SELECT 语句被创建和返回到监视器的临时表在它的数据行显示完成后就失效了当像表一样引用视图时视图可执行 SELECT 语句 视图操作创建视图 在 SQL Server 企业管理器中展开 sample 数据库点击视图节点在右侧的视图列表面板中右击鼠标在弹出的菜单中选择新建视图命令 在出现的设计视图的窗口中在关系图窗格中右击鼠标在出现的菜单中选择添加表命令或者点击工具栏上的添加表图标 选择视图的基表如有多个基表则按住 Ctrl 键多次选取然后点击添加按钮将基表加入关系图窗格中 第三章数据库开发 43

44 这时所选的表出现在视图设计器的关系图窗格中在关系图窗格中选择要加入视图的列所选的列会出现在网格窗格中并且在 SQL 窗格中显示与之对应的 SELECT 语句如下图 在关系图窗格中的空白处右击鼠标在弹出的菜单中选择属性 在弹出的属性对话框中 DISTINCT 值选项可以使视图不输出值相同的行加密浏览选项可以对视图的定义进行加密顶端可以限制视图最多输出的记录数目 视图可以用来访问一个整表部分表或组合表由于在视图中定义了访问表的部分可以不用重复地选择语句可用视图来简化数据库访问如果创建了使用多个子句的复杂视图在视图中完成 SELECT 语句就和简单视图一样容易使用视图有以下几个优点 1 为用户集中数据视图创建了一个受控制的环境它允许访问某些数据并隐藏另外的数据不必要的敏感的或不合适的数据都可以不在视图中显示用户可以操作视图中数据的显示与表类似另外使用合适的权限和一些限制用户可以修改视图产生的数据 44 第三章数据库开发

45 2 掩盖数据库的复杂性视图对用户掩盖数据库设计的复杂性使开发者具有更改设计而不影响用户与数据库打交道的能力此外通过使用比复杂数据库中使用的名字更容易理解的名字用户可以看到更友好的数据显示视图还可以隐藏比较复杂的查询包括对异构数据的分布查询用户可以简单地查询视图而不用编写复杂的查询或执行脚本 3 简化用户权限的管理数据库拥有者 owner 可以给其他的用户授权使他们可以通过视图来查询数据而不是限制用户查询基表中特定列的权限这还可以使得基表设计中的更改不至于影响用户的查询 在网格窗格中的别名列可以为该列取一个别名该别名对应于 SELECT 语句中的 AS 子句 网格窗格中的 输出 列设置所选的列是否在视图结果中显示出 来 如果取消了一列的输出 则相应的在 SELECT 语句中也不出现该 列 网格窗格中的排序类型和排序顺序则定义了结果中列的排序方式其中排序类型定义每一列的升序或降序排序顺序则定义多个列的排序先后 第三章数据库开发 45

46 为了查看视图中的数据 可以在关系图窗格中的空白处右击 鼠标 在弹出的菜单中点击 运行 命令 或者点击工具栏上的运行 图标 可以看到在结果窗格中显示了视图中的数据 点击工具栏上的保存图标在弹出的窗口中显示了系统为新视图所取的名称点击确定按钮就可以保存该视图 4 组织数据导出到其它应用程序可以创建基于连接两个或多个表的复杂查询的视图然后把数据到导出另一个应用程序作进一步的分析在创建视图之前应该对以下几个方面有所了解 1 只能在当前数据库中创建视图不过如果使用了分布式查询则新视图所引用的表和视图可以是其他数据库甚至是其它服务器中的 2 视图的名称必须符合标识符定义的规则对于每个用户来说也必须是唯一的另外视图的名称不能与该用户的表重名 通过本例 我们学习了如何在企业管理器中 创建一个视图以及 设置视图的属性 46 第三章数据库开发

47 3 可以创建在引用了视图的存储过程和视图的基础上创建视图 SQL Server 2000 允许视图的嵌套定义 32 层 4 在视图上不能定义规则也不能有 DEFAULT 属性 5 在视图上不能有 AFTER 触发器但可以有 INSTEAD OF 触发器 6 定义视图的查询语句中不能含有以下关键字 ORDER BY COMPUTE COMPUTE BY 子句和 INTO 7 不能在视图上定义全文索引 8 不能创建临时视图也不能在临时表上创建视图 9 如果视图创建时使用了 SCHEMABINDING 子句则构成它的视图和表是不能被删除的 在要修改的视图上右击鼠标在弹出的菜单中选择设计视图命令 在视图的设计器中详细方法可参考上例 视图操作修改和重命名视图 可以按照定义视图的方法更改视图的定义 如要更改视图的名称则可以在要改名的视图上右击鼠标在弹出的菜单中选择重命名命令 第三章数据库开发 47

48 视图的名称变为可输入的状态可以直接输入视图的新名称 更改视图的名称后将弹出一个对话框提示用户更改视图名称可能引起的后果确认要更名后点击是按钮 点击查看相关性按钮可以查看与视图有关的数据库对象更改视图名称可能对这些数据库对象产生影响 除非视图被删除或者是改变而使计划绑定不再有效在有计划绑定的视图创建之后构成该视图的表就不能再用 ALTER TABLE 语句了 10 虽然视图的定义中可以含有全文查询如果那个查询引用了一个配置了全文索引但是不能发行视图的全文查询 11 在以下情况下必须指定视图中每个列的名字视图中的任意一列是来自于一个算术表达式函数或常数的视图中的两列或更多的列的名称相同通常这是因为视图的定义中包含了一个连接而两个或更多的表中有相同的列名你想给视图中的每个列取一个不同于原始表的名称 通过本例我们学习了如何使用企业管理器更改视图的定义以及更改视图的名称 48 第三章数据库开发

49 默认情况下由于行通过视图进行添加或更新当其不再符合定义视图的查询的条件时它们即从视图范围中消失例如可创建一个查询从而定义一个视图以在表中检索所有员工薪水低于 $30,000 的行如果该员工的薪水涨到了 $32,000 则查询视图时该特定员工将不再出现因其薪水不符合视图所设的标准但是 WITH CHECK OPTION 子句强制所有数据修改语句均根据视图执行以符合定义视图的 SELECT 语句中所设的条件如果使用该子句修改行时需考虑到不让它在修改完后从视图中消失任何可能导致行消失的修改都会被取消并显示错误信息 视图操作查看视图的信息 要查看视图的定义信息可以在企业管理器中右击相应的视图在弹出的菜单中选择属性命令 在弹出的属性对话框中显示了视图定义的文本 点击权限按钮可以设置数据库用户操作该视图的权限 第三章数据库开发 49

50 点击列按钮可以设置该用户对视图中每一列所进行操作的权限 要查看视图中的数据可以在相应的视图上右击鼠标在弹出的菜单中选择打开视图命令下的返回所有行子命令 在出现的窗口中显示了视图中的所有数据 如果视图定义没有加密即可获取该视图定义的有关信息您可能需要查看视图定义以了解数据从源表中的提取方式或查看视图所定义的数据视图的查询方式与普通表的查询方式相同但是查询视图时所使用的任何表提示均被忽略有关表提示的更多信息请参见 SELECT 如果更改视图所引用对象的名称则必须更改视图使其文本反映新的名称因此在重命名对象之前首先显示该对象的相关情况以确定即将发生的更改是否会影响任何视图 通过本例我们学习了如何在企业管理器中查看视图的定义信息如何查看用户在视图中的权限以及如何获取视图中的数据 50 第三章数据库开发

51 如果不再需要该视图或想清除视图定义及与之相关联的权限可以删除该视图删除视图后表和视图所基于的数据并不受到影响任何使用基于已删除视图的对象的查询将会失败除非创建了同样名称的一个视图但是如果新视图没有引用与之相关的对象所期待的对象使用相关对象的查询在执行时将会失败例如假设删除了检索 pubs 数据库内 authors 表中所有列的一个视图 my_view 并用检索 titles 表中所有列的新视图 my_view 代替它现在任何引用 my_view 的基础表 authors 中的列的存储过程都将失败因为这些列已由来自 titles 表中的列所取代 视图操作删除视图 在企业管理器中右击要删除的视图在弹出的菜单中选择删除命令或者点击工具栏上的删除图标 在除去对象窗口中显示了要删除的对象点击全部除去按钮可将这些数据库对象删除 点击显示相关性按钮可以查看与该视图相关的所有数据库对象通过查看与视图相关的对象可以知道删除视图将对数据库中的其他对象产生什么样的后果 通过本例我们学习了如何删除一个视图 第三章数据库开发 51

52 索引操作创建索引 在企业管理器中右击要创建索引的表在弹出的菜单中选择所有任务下的管理索引命令 在管理索引的窗口中显示了表中已有的索引点击新建按钮 在列下选择要创建索引的列可以选择多达 16 列为获得最佳性能最好只选择一列或两列对所选的每一列可指出索引是按升序还是降序组织列值为索引指定任何其它需要的设置然后单击确定按钮 数据库中的索引与书本的索引很相似在书本中索引可以使不用阅读整本书而快速找到要找的信息而在数据库中索引允许数据库程序可以不能扫描整个表而找到表中的数据书本中的索引是一系列包含了每个单词的带有页码的词数据库的索引是一系列包含了每个数据的带有存储位置信息的词索引是以 B- 树的形式实现的它可以在单个的列创建也可以在多个列的组合上创建这个 B- 树是按搜索键进行排序的这使得任何对搜索键子集的查询都可以得到有效的执行 52 第三章数据库开发

53 索引可以提 供对一个表中的 数据的有效访问 它可以用于加速 数据的检索和强 制唯一性限制 但 是 不应该在每一 个列上都创建索 引 因为构造一个 索引需要占用一 定的系统资源 降 低更新的速度 而 且插入 删除或更 新一个被索引列 中的数据比非索 引列花更长的时 间 在 创 建 PRIMARY KEY 和 UNIQUE 约束时 SQL Server 2000 自动创建唯一索 引 以强制实施约 束的唯一性要求 除非表中已存在 聚集索引 或者指 定了非聚集索引 否则将会创建一 个唯一的聚集索 引 以实施 PRIMARY KEY 约 束 对于 UNIQUE 约束 默认情况下 将创建唯一的非 聚集索引 还可以使用另外一种方法创建索引进入表设计器在上面的空白处右击鼠标在弹出的菜单中选择索引 / 键命令 在视图的属性对话框的索引 / 键标签页中显示了表中已有的索引以及这些索引的设置点击新建按钮创建一个新的索引 在选定的索引文本框显示了系统分配给新索引的名称在列名下可以选择为一个或多个列创建索引顺序下可以选择列的排序顺序设置其他的选项之后点击关闭按钮 通过本例 学习了如何在企业管理器创建索引 可以在管理索引 的窗口中创建 也可以在表设计器中创建 第三章数据库开发 53

54 索引操作在视图上创建索引 在企业管理器中右击要创建索引的视图在弹出的菜单中选择设计视图命令进入视图设计器 在视图设计器中显示了视图所包含的列定义视图的 SQL 语句以及视图中的数据 在设计器中任意一处右击鼠标在弹出的菜单中选择管理索引命令 如果需要创建不依赖于约束的索引可以使用 CREATE INDEX 语句默认情况下如果未指定聚集选项将创建非聚集索引创建索引时须考虑的其它事项包括 1 只有表的所有者可以在同一个表中创建索引 2 每个表中只能创建一个聚集索引 3 每个表可以创建的非聚集索引最多为 249 个包括 PRIMARY KEY 或 UNIQUE 约束创建的任何索引 4 包含索引的所有长度固定列的最大大小为 900 字节 5 包含同一索引的列的最大数目为 第三章数据库开发

55 在 使 用 CREATE INDEX 语句创建索引时 必须指定索引 表 以及索引所应用 的列的名称 作为 PRIMARY KEY 或 UNIQUE 约束的一 部分或使用 SQL Server 企业管理器 创建的新索引 会 根据数据库表的 名称 自动获得系 统定义的名称 如 果在一个表上创 建多个索引 这些 索引的名称被追 加 _1 _2 等 必要 时可对索引重新 命名 在大型表上 生成索引的首选 方法是以聚集索 引开始 然后生成 非聚集索引 除去 所有索引时 首先 除去非聚集索引 最后除去聚集索 引 这样 就无需 重建索引 在索引窗口中显示了视图中已有的索引定义任何的索引点击新建按钮 下图显示了视图中没有 在创建其他索引之前应该首先创建一个聚集索引在选定的索引列表框和索引名文本框中显示了系统分配给新索引的名称在列名下面选择要创建索引的列但选择一个没有重复值的列 然后再次点击新建按钮创建其它的索引可以选择多个列设置其他的选项之后点击确定按钮 通过本例我们学习了如何在视图中创建索引为视图创建索引是 SQL Server 2000 对增强数据库而增加的新的特性它提高了视图查询的效率 第三章数据库开发 55

56 索引操作重建索引 可以使用下面的语句创建索引重建索引应使用 DROP_EXISTING 选项在 SQL Server 查询分析器中输入如下的 SQL 语句点击工具栏上的执行查询图标查询语句执行完毕后结果窗格显示命令成功完成 在创建聚集索引时将会对表进行复制对表中的数据进行排序然后删除原始的表因此数据库上必须有足够的 空闲空间以容纳数据复本默认情况下表中的数据在创建索引时排序但是如果因聚集索引已经存在且正在使用同一名称和列重新创建而数据已经排序则会重建索引而不是从头创建该索引以自动跳过排序操作重建操作会检查行是否在生成索引时进行了排序如果有任何行排序不正确即会取消操作不创建索引 56 第三章数据库开发

57 创建唯一索引可以确保任何生成重复键值的尝试都会失败如果创建的单个查询导致添加了重复的和非重复的键值 SQL Server 会拒绝所有的行包括非重复的键值例如如果一个单个的插入语句从表 A 检索了 20 行然后将它们插入到表 B 中而这些行中有 10 行包含重复键值则默认情况下所有 20 行都将被拒绝不过在创建该索引时可以指定 IGNORE_DUP_KEY 子句使得只有重复的键值才被拒绝而非重复的键值将被添加在上面的例子中将只会拒绝 10 个重复的键值其它 10 个非重复的键值将被添加到表 B 中 可以使用 Transact-SQL 中的 DBCC DBREINDEX 重建指定数据库中表的一个或多个索引用法如下 在查询分析器中输入如下语句可以重建 Products 表中的所有索引点击工具栏上的执行查询图标 查询语句执行完毕后结果窗格显示命令执行完成 通过本例我们学习了如何重新创建索引可以重建一个索引也可以同时重建多个索引 第三章数据库开发 57

58 索引操作重命名索引 可以使用 Transact-SQL 中的 sp_rename 应设为 INDEX 在查询分析器中输入如下语句将 Products 表中的索引 IX_Products 更名为 IX_ProductName 点击工具栏上的执行查询图标或者按 F5 键 在企业管理器中更改索引名称的方法为右击索引所在的表或视图在弹出的菜单中选择设计表命令 如果有任何重复的键值便不能创建唯一索引如果一个单个的列中有不止一行包含 NULL 则无法在该列上创建唯一索引同样如果列的组合中有多行包含 NULL 值则不能在多个列上创建唯一索引在创建索引时这些被视为重复的值可以在视图上定义索引索引视图是一种在数据库中存储视图结果集的方法可减少动态生成结果集的开销索引视图还能自动反映出创建索引后对基表数据所做的修改说明只有安装了 SQL Server 2000 企业版或 SQL Server 2000 开发版才可以创建索引视图 58 第三章数据库开发

59 索引视图具有以下优点 1 通过对 CREATE INDEX 和 CREATE VIEW 语句进行简单语法扩展即可实现索引视图 2 索引视图中的数据随基表数据的更新自动更新其方式与基表索引键的自动更新方式大体相同无须使索引视图的内容与基础基表中的数据同步 3 无须在查询中指定特殊的提示 SQL Server 优化器就会考虑索引视图即使查询未在 FROM 子句中直接引用索引视图优化器也将通过尝试匹配为视图生成的查询计划和为查询生成的计划的某些部分来考虑索引视图 在表或视图设计器中右击鼠标在弹出的菜单中选择索引 / 键命令 在索引 / 键窗口中显示了表或视图中已有的索引在选定的索引列表框中选择要更改名称的索引 在索引名文本框中输入索引的新名称点击关闭按钮 通过本例我们学习了如何更改表或视图中已有索引的名称更改索引的名称不会引起索引的重建 第三章数据库开发 59

60 索引操作删除索引 可以使用 Transact-SQL 中的 DROP INDEX 语句删除索引该语句的使用语法为在查询分析器中执行如下语句删除视图 ViewSupply 中的索引 IX_ViewSupply 如在企业管理器中删除视图的索引方法为鼠标右击索引所在的视图在弹出的菜单中选择设计视图进入视图的设计窗口 4 若要将索引视图引入现有的数据库必须只发出相关的 CREATE VIEW 和 CREATE INDEX 语句必须对应用程序代码做少许改动才能使 SQL Server 利用视图上的任何索引索引优化向导除建议在基表 上使用索引外还建议使用索引视图使用该向导能极大地提高管理员确定组合使用索引与索引视图 的能力以便优化在数据库上执行的典型查询混合的性能与基于基表的索引相比维护索引视图更为复杂只有当视图的结果检索速度的收益超过了修改所需的开销时才应在视图上创建索引 60 第三章数据库开发

61 当一个索引 不再需要时 可以 将其从数据库中 删除 以回收它当 前使用的存储空 间 这些回收的空 间可以由数据库 中的任何对象使 用 必须先删除 约束后 才能删除 PRIMARY KEY 或 UNIQUE 约束使用 的索引 若要在不 删除和重新创建 PRIMARY KEY 或 UNIQUE 约束的情 况下 删除并重新 创建该约束使用 的索引 例如 重 新实施该索引使 用的原填充因 子 应通过一个 步骤重建该索引 删除视图或 表时 自动删除在 视图或表 永久性 和临时性 上创建 的索引 令 下面的窗口显示了视图的设计窗口 在视图设计器中右击鼠标在弹出的菜单中选择管理索引命 在管理索引的窗口中选择要删除的索引然后点击删除按钮 通过本例我们学习了如何删除表或视图中的索引 第三章数据库开发 61

62 存储过程操作创建存储过程一 可以使用 Transact-SQL 中的 CREATE PROCEDURE 语句创建存储过程使用该语句的语法为 在查询分析器中执行如下语句创建一个存储过程 在企业管理器中创建存储过程的方法为展开相应的数据库 sample 在存储过程节点上右击鼠标在弹出的菜单中选择新建存储过程命令 存储过程是集中存储在 SQL Server 中的预先定义且已经编译好的事务存储过程由 SQL 语句和流程控制语句组成它的功能包括接受参数调用另一过程返回一个状态值给调用过程或批处理指示调用成功或失败返回若干个参数值给调用过程或批处理为调用者提供动态结果在远程 SQL Server 中运行等存储过程是更高级别的应用程序它可创建在服务器上运行的例行程序及过程这些程序可由应用程序的调用启动也可由数据完整性规则或触发器调用 62 第三章数据库开发

63 SQL Server 中 的存储过程与其 它编程语言中的 过程类似 原因是 存储过程可以 1 接受输 入参数并以输出 参数的形式将多 个值返回至调用 过程或批处理 2 包含执 行数据库操作 包 括调用其它过程 的编程语句 3 向调用 过程或批处理返 回状态值 以表明 成功或失败 以及 失败原因可 使 用 Transact-SQL EXECUTE 语句运 行存储过程 存储 过程与函数不同 因为存储过程不 返回取代其名称 的值 也不能直接 用在表达式中 在创建存储过程的窗口中 文本 文本框中显示了新建存储过 程的模板文本 在文本文本框中输入创建存储过程的 SQL 语句如下图 要检查定义存储过程的语句是否有误可以点击检查语法按钮下面的窗口显示了该 SQL 语句没有错误 通过本例我们学习了如何创建存储过程 第三章数据库开发 63

64 存储过程操作创建存储过程二 还可以通过创建向导来创建存储过程点击企业管理器工具栏上的运行向导图标在出现的选择向导对话框中选择数据库中的创建存储过程向导 向导的首页显示了该向导所具有的功能阅读后点击下一步按钮 选择存储过程所在的数据库此数据库不能是系统数据库点击下一步按钮 使用 SQL Server 中的存储过程而不使用存储在客户计算机本地的 Transact-SQL 程序的优势有 1 允许模块化程序设计只需创建过程一次并将其存储在数据库中以后即可在程序中调用该过程任意次存储过程可由在数据库编程方面有专长的人员创建并可独立于程序源代码而单独修改 2 允许更快执行如果某操作需要大量 Transact-SQL 代码或需重复执行存储过程将比 Transact-SQL 批代码的执行要快将在创建存储过程时对其进行分析和优化并可在首次执行该过程后使用该过程的内存中版本 64 第三章数据库开发

65 每次运行 Transact-SQL 语句时都要从客户端重复发送并且在 SQL Server 每次执行这些语句时都要对其进行编译和优化 3 减少网络流量一个需要数百行 Transact-SQL 代码的操作由一条执行过程代码的单独语句就可实现而不需要在网络中发送数百行代码 4 可作为安全机制使用即使对于没有直接执行存储过程中语句的权限的用户也可授予他们执行该存储过程的权限 SQL Server 存储过程是用 Transact-SQL 语句 CREATE PROCEDURE 创建 的并可用 ALTER PROCEDURE 语句进行修改 为存储过程选择一个或多个操作对于每个表都可以创建用于插入删除和更新行的存储过程选择后点击下一步按钮 正在完成创建存储过程向导显示了刚刚创建的两个存储过程点击编辑按钮可以对存储过程进行编辑点击完成按钮可以完成存储过程的创建 在编辑存储过程的窗口中 可以设置存储过程的名称 设定该过 程所操作的列 通过本例我们学习了如何使用向导创建存储过程该向导为创建操作表的存储过程提供了一个方便的途径 第三章数据库开发 65

66 存储过程操作修改和重命名存储过程 在企业管理器中展开相应的数据库在要修改的存储过程上右击鼠标在弹出的菜单中选择属性命令 在存储过程属性对话框中文本文本框中显示了存储过程的定义可以在此直接修改其定义点击检查语法按钮可以检查该文本的语法点击权限可以设置数据库用户或数据库角色执行存储过程的权限 点击权限按钮后可以在下面的窗口中设置用户的权限 存储过程定义包含两个主要组成部分过程名称及其参数的说明以及过程的主体其中包含执行过程操作的 Transact-SQL 语句在用诸如添加索引或更改索引列中的数据等操作更改数据库时应通过重新编译访问数据库表的原始查询计划使其得到重新优化在重新启动 SQL Server 2000 后第一次运行存储过程时自动发生该优化当存储过程使用的基础表更改时也会发生优化但是如果添加了存储过程可能从中受益的新索引将不发生自动优化直到下一次 SQL Server 重新启动后再运行该存储过程时为止 66 第三章数据库开发

67 SQL Server 提供三种重新编译存储过程的方法 (1)sp_recompile 系统存储过程强制在下次运行存储过程时进行重新编译 2 创建存储过程时在其定义中指定 WITH RECOMPILE 选项表明 SQL Server 将不对该存储过程计划进行高速缓存该存储过程将在每次执行时都重新编译当存储过程的参数值在各次执行间都有较大差异导致每次均需创建不同的执行计划时可使用 WITH RECOMPILE 选项此选项并不常用因为每次执行存储过程时都必须对其进行重新编译这样会使存储过程的执行变慢 重命名存储过程可以在企业管理器中右击要更名的存储过程在弹出的菜单中选择重命名命令 称 此时存储过程的名称变为可输入的状态 输入存储过程的新名 更改存储过程的名称后将出现一个对话框询问用户是否确实要更改其名称点击是按钮之后将弹出一个消息窗口表明成功地执行了重命名操作 通过本例的学习储过程 我们学会了如何修改存储过程以及重命名存 第三章数据库开发 67

68 存储过程操作查看存储过程 查看存储过程的定义信息 可以执行 sp_helptext 系统过程 使用 sp_helptext 的语法如下 在查询分析器中执行下面的语句可以查看存储过程 delete_suppliers 的定义文本 3 在执行存储过程时指定 WITH RECOMPILE 选项可强制对存储过程进行重新编译仅当所提供的参数不典型或者自创建该存储过程后数据发生显著更改时才应使用此选项如果删除或重命名了存储过程所引用的对象那么在执行该存储过程时会返回错误但如果用同名对象替换了存储过程中引用的对象则可以不进行重新编译而继续执行该存储过程 sp_help 系统过程则可以用来显示存储过程的名称及其参数使用 sp_help 的语法如下 68 第三章数据库开发

69 几个系统存储过程用系统表提供有关存储过程的信息使用这些存储过程可以 1 查看用于创建存储过程的 Transact-SQL 语句这对于没有用于创建存储过程的 Transact-SQL 脚本文件的用户是很有用的 2 获得有关存储过程的信息如存储过程的所有者创建时间及其参数 3 列出指定存储过程所使用的对象及使用指定存储过程的过程此信息可用来识别那些受数据库中某个对象的更改或删除影响的过程 在查询分析器中执行下面的语句可以查看存储过程 delete_suppliers 的参数 在企业管理器中查看存储过程的信息可以在要查看的存储过程上右击鼠标在弹出的菜单中选择属性命令 存储过程属性对话框中显示了该存储过程的定义信息 通过本例的学习我们学会了如何查看存储过程的信息 第三章数据库开发 69

70 存储过程操作删除存储过程 在要删除的存储过程上右击鼠标在弹出的菜单中选择删除命令 在除去对象对话框中显示了即将删除的对象点击全部除去按钮即可删除该对象点击显示相关性按钮可以查看于该对象相关的其他对象 点击 显示相关性 按钮后 在下面的窗口中显示了与存储过程 相关的对象 不再需要存储过程时可将其删除如果另一个存储过程调用某个已删除的存储过程则 SQL Server 2000 会在执行该调用过程时显示一条错误信息但如果定义了同名和参数相同的新存储过程来替换已删除存储过程那么引用该过程的其它过程仍能顺利执行例如如果存储过程 proc1 引用存储过程 proc2 而 proc2 被删除但由创建了另一个名为 proc2 的存储过程现在 proc1 将引用这一新存储过程 proc1 也不必重新编译存储过程分组后将无法删除组内的单个存储过程删除一个存储过程会将同一组内的所有存储过程都删除 通过本例我们学习了如何在企业管理器中删除存储过程 70 第三章数据库开发

71 触发器是一 种特殊的存储过 程 它是在修改指 定表中的数据时 生效 触发器常用 来强制不同表中 逻辑相关的数据 的引用一致性或 统一性 引用一致 性要求主关键字 值和对应的外部 关键字值完全匹 配且没有外部关 键字是 孤单的 触发器的主 要优点是它们是 自动执行的 不管 什么引起数据修 改 它们都能工 作 每个触发器都 是对一个或多个 数据修改操所特 定的 触发器中的 每条 SQL 语句执 行一次 在数据修 改与完成后它立 即 启动 触发 器和启动它的语 句作为单个事务 对待 可以从触发 器中回滚 如果检 测到一个严重错 误 整个事务都会 自动回滚 触发器操作创建触发器 可以使用 Transact-SQL 中的 CREATE TRIGGER 语句创建触发器该语句的用法如下 在查询分析器中执行如下语句将创建一个触发器 在企业管理器中创建触发器的方法为在要创建触发器的表或视图上右击鼠标在弹出的菜单中选择所有任务命令下的管理触发器子命令 第三章数据库开发 71

72 在触发器属性对话框中 文本 文本框中显示了创建触发器模 板的内容 在 文本 文本框中输入创建触发器的语句 点击 确定 按钮 可以完成触发器的创建 点击 检查语法 可以检查创建触发器的语 法 点击检查语法按钮后显示如下对话框表明该创建触发器的语句没有错误 在创建触发器的时候 SQL Server 2000 提供了两种选择 (1)INSTEAD OF 触发器它的执行代替了通常的触发动作 INSTEAD OF 触发器还可在带有一个或多个基表的视图上定义而在这些视图上这些触发器可扩展视图可支持的更新类型 2 AFTER 触发器它是在 INSERT UPDATE 或 DELETE 等语句执行之后执行的指定 AFTER 就如同指定 FOR 一样 FOR 选项是在早期的 SQL Server 中唯一可用的选项该触发器只能在表上指定 通过本例我们学习了如何创建 SQL Server 触发器 72 第三章数据库开发

73 当不再需要某个触发器时可将其删除当触发器被删除时它所基于的表和数据并不受影响删除表将自动删除其上的所有触发器删除触发器的权限默认授予在该触发器所在表的所有者可以通过除去触发器或除去触发器表删除触发器除去表时也将除去所有与表关联的触发器除去触发器时将从 sysobjects 和 syscomments 系统表中删除有关触发器的信息 触发器操作删除触发器 在企业管理器中右击触发器所在表或视图上在弹出的菜单中选择所有任务命令下的管理触发器子命令 在 触发器属性 对话框中 选择要删除的触发器 在 文本 文本框中显示了该触发器的定义文本 点击 删除 按钮可以删除该 触发器 点击删除按钮后在弹出的对话框中点击是按钮即可删除该触发器 通过本例我们学习了如何在企业管理器中删除一个触发器 第三章数据库开发 73