第6章SQL Server 2000关系数据库管理系统

1、机械工业出版社 6.1 SQL Server 2000系统结构系统结构 6.1.1 N-Tier客户机客户机服务器结构服务器结构 6.1.2. SQL Server 2000与与Internet高度集成功能高度集成功能 6.1.4 SQL Server 2000的数据库及数据库对象的数据库及数据库对象 6.1.5 SQL Server 2000的系统数据库的系统数据库 6.2 SQL Server 2000的管理工具的管理工具 6.3 Transact-SQL功能及实例功能及实例 6.3.1 Transact-SQL的数据定义功能及实例的数据定义功能及实例 6.3.2 Transact-SQL

2、的数据操纵功能及实例的数据操纵功能及实例 6.4 SQL Server 2000数据库操作工具及其使用数据库操作工具及其使用 6.4.1 用企业管理器创建数据库用企业管理器创建数据库 6.4.2 在企业管理器中定义和管理表在企业管理器中定义和管理表 SQL Server 2000是Microsoft公司推出的关系型网络数据库管理系统，它的工作环境可以是Windows NT、Windows XP、 Windows 2000等。SQL Server 2000可以作为桌面型数据库系统，也可以作为客户机/服务器型（Client/Server系统）或浏览器/服务器型（Browser/Server系统）的

3、数据库系统。1.桌面型数据库系统 SQL Server 2000安装在客户端计算机中，数据库存储在客户端计算机中而让其中的应用程序独自使用。2. 两层结构的数据库系统 客户机/服务器型数据库系统（C/S系统） 浏览器/服务器型数据库系统（B/S系统） SQL Server 2000系统安装在网络服务器中，数据库为网络中的客户机应用程序共享。两层结构的数据库系统可以通过网络连接产品将多台计算机连接为企业内部网，能够与Internet相连并发布网页。网络中的一台计算机称为服务器（Server，后端），其他的计算机称为客户机（Client，前端）应用程序及浏览器安装在客户机端，负责建立用户界面，通过

4、用户界面让用户向数据库添加、修改、删除、查询数据；数据库服务程序及数据库安装在服务器端，数据库服务器执行数据库的存储、检索、管理、安全性及数据备份等工作；服务器和客户机间通过数据接口实现数据通信。B/S结构中用户界面是浏览器中的网页，应用程序以网页及链接的方式运行，客户机端只需要有浏览器，不需要安装其他应用程序。C/S结构的客户端要有专门的应用软件系统，还要安装和设置支持应用程序运行的软件环境。两层数据库系统适合于中小型规模的数据库系统。3. 多层数据库系统结构N-Tier客户机服务器结构 后端服务器、中间端服务器（应用服务器）和客户机：中间端服务器可以是多层的。中间端服务器可以管理一个或多个

5、独立的数据库，为客户机或前级服务器提供事务服务和数据预处理，承担处理与数据库服务器的交互工作。后端服务器是中间服务器的后台，管理多个中间服务器，提供整个数据库系统的事务管理功能。6.1.2. SQL Server 2000与与Internet高度集成功能高度集成功能 SQL Server 2000可以作为Internet或内部网服务器的后端数据库，它与Web服务器协作，为Web服务器和浏览器提供信息。SQL Server 2000通过Web站点共享数据，它可以发布Web网页和接收电子邮件，使用Web浏览器可以直接访问SQL Server 2000数据库的数据，且易于学习和使用。6.1.4 SQ

6、L Server 2000的数据库对象的数据库对象1. 数据库的类别(1) 物理数据库和逻辑数据库（按模式级别分） 物理数据库由构成数据库的物理文件构成。SQL Server 2000的一个物理数据库中至少有一个数据库信息文件和一个数据库事务日志文件。物理数据库由DBA（数据库管理员）负责创建和管理。 逻辑数据库是数据库中用户可视的表或视图，用户利用逻辑数据库的数据库对象，存储或读取数据库中的数据。(2) 系统数据库和用户数据库（） 系统数据库是由系统创建和维护的数据库。系统数据库中记录着SQL Server 2000的配置情况、任务情况和用户数据库的情况等系统管理的信息，它类似于我们常说的数

7、据字典。 用户数据库是根据管理对象要求创建的数据库，用户数据库中保存着用户直接需要的数据信息。2. SQL Server 2000的数据库对象的数据库对象(1) 表和视图：表是在数据库中存放的实际关系。视图是为了用户查询方便或根据数据安全的需要而建立的虚表。(2) 角色：由一个或多个用户组成的单元，也称职能组。一个用户可以成为多个角色中的成员。(3) 索引：来加速数据访问和保证表的实体完整性的数据库对象。的索引有群聚和非群聚索引两种。群聚索引会使表的物理顺序与索引顺序一致，一个表只能有一个群聚索引；非群聚索引与表的物理顺序无关，一个表可以建立多个非群聚索引。(4) 存储过程：通过Transac

8、t-SQL编写的程序。包括系统存储过程和用户存储过程：系统存储过程是由SQL Server 2000提供的，其过程名均以SP开头；用户过程是由用户编写的，它可以自动执行过程中安排的任务。(5) 触发器：一种特殊类型的存储过程，当表中发生特殊事件时执行。触发器主要用于保证数据的完整性。(6) 约束：约束规则用于加强数据完整性。3. 数据库结构(1) SQL Server 2000的三种物理文件1) 基本数据文件：基本数据文件用于容纳数据库对象，它使用.mdf作为文件扩展名。2) 辅助数据文件：当数据库中的数据较多时需要建立辅助数据文件。一个数据库中可以没有、也可以有一个或多个辅助数据文件。辅助数

9、据文件的扩展名为.ndf。3) 日志文件：用于存放数据库日志信息的文件。一个数据库可以有一个或多个日志文件。日志文件的扩展名为.ldf。(2) 数据库文件的两种组件1) 页：使用的最小数据单元，一页可以容纳8k的数据。共有8种页：数据页、索引页、文本/图像页、全局分配映射表页、页空闲空间、索引分配映射表页、大容量更改映射表页和差异更改映射表页。2) 扩展盘区：扩建表和索引的基本单位，一个扩展盘区由8个相邻页的构成。(3) 文件组：多个文件可以归纳成为一个文件组。6.1.5 SQL Server 2000的系统数据库的系统数据库1. Master数据库数据库Master数据库的主文件名为Mast

10、er.mdf，日志文件为Masterlog.ldf。Master中内含许多系统表，用来跟踪和记录SQL Server相关信息。2. Msdb数据库数据库Msdb的主文件名为Msdb.dbf，日志文件名为Msdb.ldf。Msdb由SQL Server企业管理器和代理服务器使用。Msdb中记录着任务计划信息、事件处理信息、数据备份及恢复信息和警告及异常信息。3. Model数据库数据库Model数据库的主文件是model.mdf，日志文件为model.ldf。Model数据库是SQL Server 2000为用户数据库提供的样板，新的用户数据库都以model数据库为基础。4. tempdb数据库

11、数据库tempdb的主文件名和日志文件名分别为tempdb.dbf和tempdb.ldf。tempdb是一个共享的工作空间，SQL Server 2000中的所有数据库都可以使用它，它为临时表和其他临时工作提供了一个存储区。6.2 SQL Server 2000的管理工具1. 查询分析器编辑Transact-SQL，将其发送到服务器，并将执行结果及分析显示出来。通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能。 2. 导入和导出数据导入和导出数据 导入和导出数据通过一个向导程序“数据转换服务（简称DTS）”实现，其作用是使SQL Server 与任何OL

12、E DB、ODBC、JDBC或文本文件等多种不同类型的数据库之间实现数据传递。3. 服务管理器服务管理器 服务管理器（Service Manger）用于开启、暂停和停止SQL服务工作，同时，也可以通过它设置服务是否自动启动。 4. 客户机网络连接工具和服务器网络连接工具客户机网络连接工具和服务器网络连接工具 客户机网络连接工具用于设置本机作为服务器或客户机所使用的缺省网络链接库，并可为不支持缺省网络库的SQL服务器设置连接方式。服务器网络连接工具用于设置本机作为服务器时允许的连接协议，以支持不同配置的客户端。5. 联机丛书联机丛书 联机丛书是SQL Server 2000为用户提供的在线帮助功

13、能。在联机丛书中，全面而详细地介绍了有关SQL Server2000的功能、管理及开发方法、TSQL语法等。6. 企业管理器企业管理器 企业管理器窗口的左边是以树形结构组织的管理对象，右边称为明细窗口，其内容是是左边选中对象的详细内容。企业管理器可以实现对管理对象的定义、改动、删除等各种操作。 7.事件探查器事件探查器 用于监视与分析SQL Server活动、服务器的网络进出流量或发出在SQL Server上的事件。事件探查器把一个操作序列保存为一个.trc文件，然后在本机或其他机器上按原来的次序重新执行一遍。 8. 在在IIS中配置中配置SQL XML支持支持 SQLServer 2000含

14、有SQL Server的IIS虚拟目录管理器，通过它在IIS定义一个与SQL Server 2000实例相关的虚拟目录。9. 分析管理器分析管理器 提供用户界面以访问分析服务器及其元数据知识库的控制台应用程序。实现：管理分析服务器，锁定被编辑的对象和它们的从属对象；创建数据库和指定数据源；生成并处理多维数据集；创建并处理数据挖掘模型；指定存储选项并优化查询性能；管理安全性；浏览数据源、共享维度、安全角色和其它对象。 6.3 SQL Server 2000数据库操作工具及其使用 6.3.1 用企业管理器创建数据库用企业管理器创建数据库 1) 选择企业管理器。2) 选中需要在其上创建数据库的服务器

15、 3) 选中“数据库”文件夹，单击右键，在弹出上选择“新建数据库”。在常规页面中，输入数据库名，选择SQL 服务器。 4) 选择数据文件页面，输入数据文件属性；再选择事物日志页面，输入数据库的日志文件属性。5) 单击“确定”按钮，关闭对话框。6.3.2 在企业管理器中定义和管理表 1. 新建表选中数据库中的表文件夹，击鼠标右健。在弹出的菜单中选择新建表。随后输入表结构对话框。 在对话框中单击右键，出现一个弹出框。字段输入完后，关闭建表对话框。在对话框中输入表名，单击“确定”按钮。2. 定义表的完整性约束和索引(1) 定义索引和键(2) 定义表间关联选择“索引/键”页面，选择表页面 6.4 Tr

16、ansact-SQL功能及实例 6.4.1 Transact-SQL的数据定义功能及实例定义功能及实例T-SQLT-SQL是是MicrosoftMicrosoft公司在公司在关系型数据库管理系关系型数据库管理系统统SQL ServerSQL Server中的中的SQL-3SQL-3标准的实现标准的实现, ,是是微软微软对对SQLSQL的扩展的扩展, ,具有具有SQLSQL的主要特点的主要特点, ,同时增加了变同时增加了变量量, ,运算符运算符, ,函数函数, ,流程控制流程控制和注释等语言元素和注释等语言元素, ,使得其功能更加强大使得其功能更加强大.T-SQL.T-SQL对对SQL Serv

17、er SQL Server 十分十分重要重要,SQL Server,SQL Server中使用图形界面能够完成的所中使用图形界面能够完成的所有功能有功能, ,都可以利用都可以利用T-SQLT-SQL来实现来实现. .使用使用T-SQLT-SQL操操作时作时, ,与与SQL ServerSQL Server通信的所有通信的所有应用程序应用程序都通过都通过向服务器发送向服务器发送T-SQLT-SQL语句来进行语句来进行, ,而与应用程序而与应用程序的界面无关的界面无关. . 根据其完成的具体功能根据其完成的具体功能, ,可以将可以将T-SQLT-SQL语句语句分为四大类分为四大类, ,分别为数据定

18、义语句分别为数据定义语句, ,数据操作语数据操作语句句, ,数据控制语句和一些附加的语言元素数据控制语句和一些附加的语言元素. . 6.4 Transact-SQL功能及实例 6.4.1 Transact-SQL的数据定义功能及实例定义功能及实例1. 创建和管理数据库 CREATE DATABASE数据库名 ON PRIMARY(NAME =逻辑数据文件名， FILENAME= 操作数据文件路径和文件名 ，SIZE=文件长度 ，MAXSIZE=最大长度 ，FILEROWTH=文件增长率)，n LOG ON (NAME=逻辑日志文件名， FILENAME= 操作日志文件路径和文件名 ，SIZE=

19、文件长度 )，n FOR RESTORE 子句中：PRIMARY指明主文件名；SIZE说明文件的大小，数据库文件最小为1MB，默认值为3MB；FILEROWTH说明文件的增长率，默认值为10%。FOR RESTORE子句说明重建一个数据库，该重建的数据库用于数据恢复操作。 6.4 Transact-SQL功能及实例 6.4.1 Transact-SQL的数据定义功能及实例定义功能及实例1. 创建和管理数据库 CREATE DATABASE数据库名 ON PRIMARY(NAME =逻辑数据文件名， FILENAME= 操作数据文件路径和文件名 ，SIZE=文件长度 ，MAXSIZE=最大长度

20、，FILEROWTH=文件增长率)，n LOG ON (NAME=逻辑日志文件名， FILENAME= 操作日志文件路径和文件名 ，SIZE=文件长度 )，n FOR RESTORE 子句中：PRIMARY指明主文件名；SIZE说明文件的大小，数据库文件最小为1MB，默认值为3MB；FILEROWTH说明文件的增长率，默认值为10%。FOR RESTORE子句说明重建一个数据库，该重建的数据库用于数据恢复操作。 2. 定义表：定义表：CREATE TABLE表名表名(列名类型列名类型|AS表达式表达式 字段约束字段约束 ， 记录约束记录约束)(1) 字段约束1) NOT NULL|NULL：不

21、允许或允许字段值为空。2) PRIMARY KEY CLUSTERED|NON CLUSTERED：字段为主码并建立聚集或非聚集索引。3) REFERENCE参照表(对应字段)：定义被参照表及字段。4) DEFAULT缺省值：定义字段的缺省值。5) CHECK(条件)：定义字段应满足的条件表达式。6) IDENTITY(初始值，步长)：定义字段为数值型数据，并指出它的初始值和逐步增加的步长值。 (2) 记录约束 CONSTRAINT约束名约束式1) PRIMARY KEY CLUSTERED|NON CLUSTERED(列名组) 2) FOREIGN KEY(外码) REFERENCES参照表

22、(对应列)： 3) CHECK(条件表达式)：定义记录应满足的条件。4) UNIQUE(列组)：定义不允许重复值的字段组。 3. 基本表的维护(1)修改基本表1) 修改字段的定义。 ALTER TABLE表名 ALTER COLUMN列名新类型 NULL|NOT NULL约束定义2) 增加字段和表约束规则。 ALTER TABLE表名ADD列定义|表约束定义 3) 删除字段或约束规则。 ALTER TABLE表名DROPCONSTRAINT约束名|COLUMN列名4) 使约束有效或无效。 ALTER TABLE表名CHECK|NOCHECK CONSTRAINT ALL|约束名组(2) 删除基

23、本表: DROP TABLE表名 4. 创建和管理索引 (1) 创建索引 CTEATE UNIQUE CLUSTERED|NON CLUSTERED INDEX索引名ON表名(索引列组)(2) 删除索引 DROP INDEX表名.索引名 5. 创建和管理视图 (1) 创建视图创建视图的语法为： CREATE VIEW视图名(列名组) WITH ENCRYPTIOPN AS子查询 WITH CHECK OPTION (2) 删除视图 DROP VIEW 视图名组 6. 创建和管理缺省(1)创建缺省 CREATE DEFAULT缺省名 (2) 绑定缺省 EXEC sp_bindfault 缺省名，

24、表名.列名系统存储过程执行时的格式为： EXEC存储过程名参数组 如果参数是常量，则要加定界符 。 (3) 解除缺省EXEC sp_unbinddefault 缺省名，表名.列名 (4) 删除缺省 DROP DEFAULT 缺省名组7. 创建和管理规则(1)创建规则 CREATE RULE规则名AS规则表达式规则表达式是WHERE子句中的有效表达式。 (2) 绑定规则 EXEC sp_bindrule 规则名，对象名(3) 解除规则EXEC sp_unbindrule 规则名，对象名(4) 删除规则 DROP RULE规则名组 8. 创建和管理存储过程 (1) 创建存储过程和调用存储过程 CR

25、EATE PROCEDURE过程名;版本号 参数名参数类型=缺省值OUTPUTWITH RECOMPILE|ENCRYPTION| RECOMPILE，ENCRYPTION ASSQL语句组1) 版本号是可选的整数，它用于将有相同名字的存储过程编为不同的组。2) OUTPUT选项用于给调用者返回值。3) RECOMPILE为重编译选项。它要求每次执行都要对过程重编译和优化，并创建新的查询计划。4) ENCYPTION为加密选项。5) 参数前加“” 为局部变量，参数前加“”则说明为全局变量。过程的调用语句为： EXEUTE过程名参数名=参数版本号 (2) 删除存储过程 DROP PROCEDUR

26、E存储过程名组 9. 创建和管理触发器CREATE TRIGGER触发器名ON表名 WITH ENCRYPTION FORDELETE，INSERT，UPDATE WITH APPEND NOT FOR REPLICATION ASSQL语句组1) WITH ENCRYPTION为加密选项。2) DELETE触发器是当对表执行DELETE操作时删除元组，将删除的元组放入deleted表中。检查delete表中的数据，确定该如何处理。3) INSERT触发器在对执行插入数据操作时,将插入表中的数据拷贝并送入inserted表中，根据inserted表中的值决定如何处理。4) UPDATE触发器仅

27、在更新数据操作时将要被更新的原数据移入deleted表中，将更新后的数据备份送入inserted表中，对deleted和inserted表进行检查，并决定如何处理。5) NOT FOR REPLICATION项说明当一个复制过程在修改一个触发器表时，与该表相关联的触发器不能被执行。【例【例6-21】设有】设有member表（成员表）、表（成员表）、loan表（借书表）和表（借书表）和reservation表（预定书表）。通过触发器定义未还图书的成员不能从成员表中删除，当删表（预定书表）。通过触发器定义未还图书的成员不能从成员表中删除，当删除成员时，该成员在的预定书表（除成员时，该成员在的预定书

28、表（reservation表）中的记录也将全部被删除。表）中的记录也将全部被删除。 CREATE TRIGGER member_delete ON member FOR DELETE AS IF (SELECT COUNT(*) FROM loan，deleted WHERE loan.member_no=deleted.member_no)0 ROLLBACK TRANSACTION ELSE DELETE reservation FROM reservation，deleted WHERE reservation.member_no = deleted.member_no6.3.2 Tra

29、nsact-SQL的数据操纵功能及实例的数据操纵功能及实例1. 数据检索语句的语句格式 SELECT查询列 INTO新表名 FROM数据源 WHERE元组条件表达式 GROUP BY分组条件HAVING组选择条件 ORDER BY排序条件 COMPUTER统计列组BY表达式（1） SELECT子句SELECT ALL|DISTINCT TOP数值PERCENT查询列组查询列为：查询列:=*|表或视图.*|列名或表达式AS列别名 |列别名=表达式1) ALL|DISTINCT：ALL为返回所有行,DISTINCT为仅显示结果集中的惟一行。该项不选时，ALL是缺省值。2) TOP数值：仅返回结果集

30、中的前数值行。如果有PENCENT，则返回结果集中的百分之数值行记录。3) “*”：指明返回表和视图的全部列。4)表或视图.*：指明返回指定表或视图的全部列。5)列别名：用来代替出现在结果集中的列名或表达式，别名可以在ORDER BY子句中出现，但不能在WHERE、GROUP BY或HAVING子句中出现。(2) INTO子句和FROM子句 INTO子句将查询结果添加到创建表中。INTO不能与COMPUTE 子句同时使用。如果创建表是临时表，在表名前加“#”字符。 FROM语法格式为： FROM数据源组数据源的语法为： 数据源:=表名AS表别名WITH(表线索组) |视图名AS视图别名 |行集合函数AS别名 |嵌套的SELECT语句AS别名 |连接表连接表的语法为：连接表:=数据源连接类型数据源ON连接条件 |数据源CROSSJOIN数据源|连接表连接类型:=