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

1、客户端客户端1. 查询分析器查询分析器2. 导入和导出数据导入和导出数据3. 服务管理器服务管理器4. 客户机网络连接工具和服务器网络连接工具客户机网络连接工具和服务器网络连接工具6. 企业管理器企业管理器5. 联机丛书联机丛书8. 在在IIS中配置中配置SQL XML支持支持 9. 分析管理器分析管理器10. 英语查询工具英语查询工具1. 创建和管理数据库创建和管理数据库(1) 创建数据库创建数据库其语法为：其语法为： CREATE DATABASE数据库名数据库名 ON PRIMARY(NAME =逻辑数据文件名，逻辑数据文件名， FILENAME= 操作数据文件路径和文件名操作数据文件路

2、径和文件名 ，SIZE=文件长度文件长度 ，MAXSIZE=最大长度最大长度 ，FILEROWTH=文件增长率文件增长率)，n LOG ON (NAME=逻辑日志文件名，逻辑日志文件名， FILENAME= 操作日志文件路径和文件名操作日志文件路径和文件名 ，SIZE=文件长度文件长度 )，n FOR RESTORE【例【例6-1】建立学生选课库。】建立学生选课库。 CREATE DATABASE 学生选课库学生选课库 ON PRIMARY(NAME=学生选课库，学生选课库， FILENAME=C:mssqldata学生选课学生选课.mdf SIZE=4MB MAXSIZE=6MB FILEG

3、ROWHT=2MB)(2) 选择数据库选择数据库格式为：格式为： USE数据库名数据库名注意：在注意：在Transact SQL中没有语法结束符号。语句结束后按中没有语法结束符号。语句结束后按Enter键，并另起一行输入键，并另起一行输入GO语句，再按语句，再按Enter键。否则语句不会执行。键。否则语句不会执行。2. 定义表定义表定义基本表的格式为：定义基本表的格式为：CREATE TABLE表名表名(列名类型列名类型|AS表达式表达式字段约束字段约束 ， (4) 删除数据库删除数据库 DROP DATABASE 数据库名组数据库名组【例【例6-2】将学生选课库删除可使用下述语句。】将学生选

4、课库删除可使用下述语句。 DROP DATABASE 学生选课库学生选课库 GO记录约束记录约束)(1) 字段约束字段约束1) NOT NULL|NULL：定义不允许或允许字段值为空。：定义不允许或允许字段值为空。2) PRIMARY KEY CLUSTERED|NON CLUSTERED：定义该字段为主：定义该字段为主码并建立聚集或非聚集索引。码并建立聚集或非聚集索引。3) REFERENCE参照表参照表(对应字段对应字段)：定义该字段为外码，并指：定义该字段为外码，并指出被参照表及对应字段。出被参照表及对应字段。4) DEFAULT缺省值缺省值：定义字段的缺省值。：定义字段的缺省值。5)

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

6、照表参照表(对应列对应列)：指出表的外码和被参照表。：指出表的外码和被参照表。3) CHECK(条件表达式条件表达式)：定义记录应满足的条件。：定义记录应满足的条件。4) UNIQUE(列组列组)：定义不允许重复值的字段组。：定义不允许重复值的字段组。(3) 数据类型数据类型【例【例6-3】创建一个部门表。】创建一个部门表。CREATE TABLE 部门部门(代码代码 smallint INDENTITY(1，1) PRIMARY KEY CLUSTERED， 名称名称 VARCHAR(50) NOT NULL, 负责人负责人 CHAR(8)【例【例6-4】建立一个职工表。】建立一个职工表。

7、CREATE TABLE 职工职工( 编号编号smallint PRIMARY KEY CLUSTERED， 姓名姓名 CHAR(8)， 基本工资基本工资 smallmoney， 实发工资实发工资 AS 基本工资基本工资+附加工资，附加工资， 部门代码部门代码 smallint REFERENCES 部门部门(代码代码)， 性别性别 CHAR(2) DEFAULT 男男 CHECK( 性别性别IN (男男，女女)3. 基本表的维护基本表的维护(1) 修改基本表修改基本表1) 修改字段的定义。修改字段的定义。修改字段的语法为：修改字段的语法为： ALTER TABLE表名表名 ALTER COL

8、UMN列名新类型列名新类型NULL|NOT NULL约束定义约束定义2) 增加字段和表约束规则。增加字段和表约束规则。格式为：格式为： ALTER TABLE表名表名ADD列定义列定义|表约束定义表约束定义 ALTER TABLE表名表名DROPCONSTRAINT约束名约束名|COLUMN列名列名4) 使约束有效或无效。使约束有效或无效。格式为：格式为： ALTER TABLE表名表名CHECK|NOCHECK CONSTRAINT ALL|约束名组约束名组【例【例6-5】为表】为表example1增加增加Column_b列。该列的数据类型为列。该列的数据类型为Varchar(20)，并允许

9、有，并允许有NULL值存在。值存在。 CREATE TABLE example1(Column_a int) GO ALTER TABLE example1 ADD Column_b Varchar(20) NULL GO【例【例6-6】删除表】删除表example1的列的列Column_b。 ALTER TABLE example1 DROP COLUMN Column_b GO【例【例6-7】使表中的约束失效后，再重新有效。】使表中的约束失效后，再重新有效。 CREATE TABLE example2 (编号编号 int NOT NULL， 姓名姓名 varchar(10) NOT NUL

10、L， 工资工资 money NOT NULL， CONSTRAINT 工资约束工资约束 CHECK(工资工资10000) GO ALTER TABLE example2 NOCHECK CONSTRAINT 工资约束工资约束 GO ALTER TABLE example2 CHECK CONSTRAINT 工资约束工资约束 GO(2) 删除基本表删除基本表 DROP TABLE表名表名4. 创建和管理索引创建和管理索引 (1) 创建索引创建索引 CTEATE UNIQUECLUSTERED|NONCLUSTERED INDEX索引名索引名ON表名表名(索引列组索引列组)【例【例6-8】创建一个

11、惟一聚簇索引的示例。】创建一个惟一聚簇索引的示例。 CREATE TABLE emp_pay (employeeID int NOT NULL， base_Pay money NOT NULL， commission decimal(2，2) NOT NULL) GO INSERT emp_Pay VALUES (1，500，10) GO CREATE UNIQUE CLUSTERED INDEX employeeID_ind ON emp_pay (employeeID)【例【例6-9】创建一个复合索引的示例。】创建一个复合索引的示例。 CREATE TABLE order_emp(orde

12、rID int INDENTIEY(1000，1)， employeeID int NOT NULL， orderdate datetime NOT NULL DEFAULT GETDATE( )， orderamount money NOT NULL) GO INSERT order_emp(employeeID，orderdate，orderamount) VALUES (7，3/21/98，2178.98) GO CREATE INDEX emp_order_ind ON order_emp(orderID，employeeID) GO (2) 删除索引删除索引删除索引的语法为：删除索引

13、的语法为： DROP INDEX表名表名.索引名索引名【例【例6-12】删除】删除authors表中的名为表中的名为zip_ind的索引。的索引。 DROP INDEX authors.zip_ind5. 创建和管理视图创建和管理视图(1) 创建视图创建视图创建视图的语法为：创建视图的语法为： CREATE VIEW视图名视图名(列名组列名组) WITH ENCRYPTIOPN AS子查询子查询 WITH CHECK OPTION (2) 删除视图删除视图DROP VIEW 视图名组视图名组6. 创建和管理缺省创建和管理缺省(1) 创建缺省创建缺省 CREATE DEFAULT缺省名缺省名 (

14、2) 绑定缺省绑定缺省绑定缺省就是将缺省名与表的列联系在一起。绑定缺省就是将缺省名与表的列联系在一起。 EXEC sp_bindfault 缺省名缺省名，表名表名.列名列名系统存储过程执行时的格式为：系统存储过程执行时的格式为： EXEC存储过程名参数组存储过程名参数组 【例【例6-13】定义学生表中的所在系属性为】定义学生表中的所在系属性为“计算机系计算机系”的缺省的缺省。 CREATE DEFAULT 系缺省系缺省 AS 计算机系计算机系 GO EXEC sp_binddefault 系缺省，系缺省，学生学生.所在系所在系(3) 解除缺省解除缺省(4) 删除缺省删除缺省7. 创建和管理规则

15、创建和管理规则(1) 创建规则创建规则 CREATE RULE规则名规则名AS规则表达式规则表达式【例【例6-14】创建一个名为】创建一个名为range_rule的规则，该规则要求插入到列中的数的规则，该规则要求插入到列中的数据范围在据范围在($1000$2000)之间。之间。 CREATE RULE range_rule AS range = $1000 AND range 0 ROLLBACK TRANSACTION ELSE DELETE reservation FROM reservation，deleted WHERE reservation.member_no = deleted.

16、member_no (2) 删除触发器删除触发器语法为：语法为： DROP TRIGGER触发器名组触发器名组1. 数据检索语句的语句格式数据检索语句的语句格式 SELECT查询列查询列 INTO新表名新表名 FROM数据源数据源 WHERE元组条件表达式元组条件表达式 GROUP BY分组条件分组条件HAVING组选择条件组选择条件 ORDER BY排序条件排序条件 COMPUTER统计列组统计列组BY表达式表达式（1) SELECT子句子句SELECT子句的语法为：子句的语法为： SELECT ALL|DISTINCT TOP数值数值PERCENT查询列组查询列组查询列可以表示为：查询列可

17、以表示为： 查询列查询列:=*|表或视图表或视图.*|列名或表达式列名或表达式AS列别名列别名 |列别名列别名=表达式表达式(2) INTO子句子句INTO子句用于创建一个表，并将查询结果添加到该表中。子句用于创建一个表，并将查询结果添加到该表中。INTO不能与不能与COMPUTE子句同时使用。如果创建的表子句同时使用。如果创建的表是临时表，则在表名前加是临时表，则在表名前加“#”字符。字符。(3) FROM子句子句语法格式为：语法格式为：FROM数据源组数据源组数据源的语法为：数据源的语法为： 数据源数据源:=表名表名AS表别名表别名WITH(表线索组表线索组) |视图名视图名AS视图别名视

18、图别名 |行集合函数行集合函数AS别别名名 |嵌套的嵌套的SELECT语句语句AS别名别名 |连接表连接表连接表的语法为：连接表的语法为： 连接表连接表:=数据源连接类型数据源数据源连接类型数据源ON连接条件连接条件 |数据源数据源CROSSJOIN数据源数据源|连接表连接表 连接类型连接类型:= INNER| LEFT|RIGHT|FULLOUTER JOIN(4) WHERE子句子句的语法为：的语法为： WHERE查询条件查询条件|旧格式外连接理条件旧格式外连接理条件 旧格式外连接条件旧格式外连接条件:=列名列名*= | =*列名列名(5) GROUP BY子句子句语法为：语法为： GRO

19、UP BY ALL分组表达式组分组表达式组(6) HAVING子句。子句。指定分组或汇总筛选条件，指定分组或汇总筛选条件，HAVING子句的格式为：子句的格式为：HAVING分组或汇总筛选条件分组或汇总筛选条件(7) ORDER BY指明排序项和排序要求，其语法为：指明排序项和排序要求，其语法为：ORDER BY排序项排序项ASC|DESC，n(8) COMPUTE子句。子句。COPMPUTE子句的作用是产生汇总值，并在结果集中后将汇总值放入子句的作用是产生汇总值，并在结果集中后将汇总值放入摘要列，摘要列，COMPUTE与与BY配合，将起到换行控制和分段小计的作用。配合，将起到换行控制和分段小

20、计的作用。COMPUTE子句的语法如下：子句的语法如下：COMPUTE统计函数组统计函数组BY分组项分组项其中：其中：BY分组项表示在结果集中产生换行控制及分段小计。分组项表示在结果集中产生换行控制及分段小计。COMPUTE BY必须和必须和ORDER BY配合使用，分组项应完全等于排序配合使用，分组项应完全等于排序项项2. 查询语句中使用的运算符号查询语句中使用的运算符号3. SQL Server提供的函数提供的函数4.Transact-SQL查询实例查询实例设已建好了图书借阅数据库，图书借阅数据库中包括设已建好了图书借阅数据库，图书借阅数据库中包括3个基本表：图书、个基本表：图书、读者和借

21、阅，其结构为：读者和借阅，其结构为： 图书图书(书号，类别，出版社，作者，书名，定价书号，类别，出版社，作者，书名，定价)； 读者读者(书证号，姓名，单位，性别，电话书证号，姓名，单位，性别，电话)； 借阅借阅(书号，读者书证号，借阅日期书号，读者书证号，借阅日期). (1) 使用使用SELECT INTO创建新表创建新表【例【例6-22】将计算机类的书存入永久的计算机图书表，将借书日期在】将计算机类的书存入永久的计算机图书表，将借书日期在99年以前的借阅记录存入临时的超期借阅表。年以前的借阅记录存入临时的超期借阅表。 SELECT * INTO 计算机图书计算机图书 FROM 图书图书 WH

22、ERE 类别类别=计算机计算机 GO SELECT * INTO #超期借阅超期借阅 FROM 借阅借阅 WHERE 借阅日期借阅日期1999-01-01 GO (2) 使用关联子查询使用关联子查询【例【例6-23】查询借了书的读者。】查询借了书的读者。 SELECT * FROM 读者读者 WHERE EXISTS (SELECT * FROM 借阅借阅 WHERE 读者读者.编号编号=借阅借阅.读者编号读者编号)或：或： SELECT * FROM 读者读者 WHERE 编号编号 IN (SELECT 读者编号读者编号 FROM 借阅借阅)【例【例6-24】查找这样的图书类别：要求类别中最

23、高的图书定价不低】查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的于全部按类别分组的图书平均定价的2倍。倍。 SELECT A.* FROM 图书图书 A GROUP BY A.类别类别 HAVING MAX(A.定价定价)=ALL (SELECT 2*AVG(B.定价定价) FROM 图书图书B GROUP BY B.类别类别) (3) 使用使用GROUP BY ALL查询查询【例【例6-25】求机械工业出版社出版的各类图书的平均】求机械工业出版社出版的各类图书的平均定价，用定价，用GROUP BY表示。表示。 SELECT 类别，类别，AVG(定价定价) 平

24、均价平均价 FROM 图书图书 WHERE 出版社出版社=机械工业出版社机械工业出版社 GROUP BY 类别类别 ORDER BY 类别类别 ASC用用GROUP BY ALL 表示为：表示为： SELECT 类别，类别，AVG(定价定价) 平均价平均价 FROM 图书图书 WHERE 出版社出版社=机械工业出版社机械工业出版社 GROUP BY ALL 类别类别 ORDER BY 类别类别ASC (4) 使用使用COMPUTE和和COMPUTE BY查询查询【例【例6-26】列出计算机类图书的书号、名称及价格，最后求出册数和总】列出计算机类图书的书号、名称及价格，最后求出册数和总价格价格

25、SELECT 书号，名称，定价书号，名称，定价 FROM 图书图书 WHERE类别类别=计算机类计算机类 ORDER BY 书号书号 ASC COMPUTE COUNT(*)，SUM(定价定价)【例【例6-27】列出计算机类图书的书号、名称及价格，并求出各出版社这】列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。类书的总价格，最后求出全部册数和总价格。 SELECT书号，名称，定价书号，名称，定价 FROM 图书图书 WHERE 类别类别=计算机类计算机类 ORDER BY 书号书号 ASC CONPUTE COUNT(*)，SUM (定价定价) B

26、Y 出版社出版社 COMPUTE COUNT(*)，SUM(定价定价) (5) 使用使用UNION查询查询【例【例6-28】查询计算机类和机械工业出版社出版的图书。】查询计算机类和机械工业出版社出版的图书。 SELECT * FROM 图书图书 WHERE 类别类别=计算机类计算机类 UNION ALL SELECT * FROM 图书图书 WHERE 出版社出版社=机械工业出版社机械工业出版社(6) 连接操作连接操作【例【例6-29】查询借书者的编号，姓名，单位，所借书号，书名，】查询借书者的编号，姓名，单位，所借书号，书名，和借阅日期。和借阅日期。SELECT 读者读者.编号，读者编号，读

27、者.姓名，读者姓名，读者.单位，借阅单位，借阅.书号，书号， 图书图书.书名，借阅书名，借阅.借阅日期借阅日期 FROM 读者读者 INNER JOIN 借阅借阅 ON读者读者.编号编号=借阅借阅.读者读者编号编号 INNER JOIN 图书图书 ON借阅借阅.书号书号= 图书图书.书号书号 ORDER BY 读者读者.编号编号 ASC5. 数据更新操作数据更新操作(1) 数据插入语句数据插入语句语法为：语法为： INSERT INTO表或视图名表或视图名(列组列组) VALUES值列值列|SELECT语句语句其中：其中：1) INSERT VALUES语句一次只能插入一条记录，而语句一次只能

28、插入一条记录，而INSERT SELECT语句则可一次插入多条记录。语句则可一次插入多条记录。2) 未在列组中出现的列名的值，则按未在列组中出现的列名的值，则按IDEMTITY（有产（有产生递增值定义）、生递增值定义）、DEFAULT（有缺省值定义）或（有缺省值定义）或NULL（前（前两项都无）值确定，如果按两项都无）值确定，如果按NULL处理而定义中不允许处理而定义中不允许NULL值，则显示错误信息。值，则显示错误信息。(2) 修改数据修改数据语法为：语法为： UPDATE表或视图名表或视图名 SET列名列名=表达式表达式|DEFAULT，n WHERE条件条件【例【例6-30】设在读者表中

29、增加了】设在读者表中增加了“借书册数借书册数”字段，现要求统字段，现要求统计借书者在计借书者在98年年99年间所借书的册数，并将结果送入读者表年间所借书的册数，并将结果送入读者表中的借书册数字段。中的借书册数字段。 UPDATE 读者读者 SET 借书册数借书册数= (SELECT COUNT(*) FROM 读者，借阅读者，借阅 WHERE借者借者.编号编号=借阅借阅.读者编号读者编号AND 借阅日期借阅日期 BETWEEN 1998-01-01 AND 1999-12-31 )(3) 删除数据删除数据语法为：语法为： DELETE FROM表名表名 WHERE条件条件扩展语句格式为：扩展语

30、句格式为： DELETE表名表名 FROM表名连接类型表名连接类型JOIN表名表名ON连接条件连接条件 WHERE条件条件其中：其中：FROM子句为子句为SELECT查询语句中的查询语句中的FROM子句形式，它可子句形式，它可以表达连接条件。以表达连接条件。【例【例6-31】删除单位是计算机系的读者借阅记录。】删除单位是计算机系的读者借阅记录。 DELETE 借阅借阅 WHERE 读者编号读者编号 IN (SELECT 编号编号 FROM 读者读者 WHERE 单位单位=计算机系计算机系)或：或： DELETE 借阅借阅 FROM 借阅借阅 INNER JOIN 读者读者 ON 借阅借阅.读者

31、编号读者编号=读者读者.编号编号 WHERE 读者读者.单位单位=计算机系计算机系1. 创建数据库创建数据库1) 选择企业管理器。选择企业管理器。2) 选中需要在其上创建数据库的服务器选中需要在其上创建数据库的服务器4) 选择数据文件页面，输入图书选择数据文件页面，输入图书-读者数据库的数据文件属性，包读者数据库的数据文件属性，包括文件名、存放位置、和文件属性；再选择事物日志页面，输入数括文件名、存放位置、和文件属性；再选择事物日志页面，输入数据库的日志文件属性，包括文件名、存放位置、大小和文件属性。据库的日志文件属性，包括文件名、存放位置、大小和文件属性。5) 单击单击“确定确定”按钮，关闭

32、对话框。按钮，关闭对话框。2. 查看和修改数据库属性参数查看和修改数据库属性参数1) 启动企业管理器。启动企业管理器。2) 选中数据库文件夹；用鼠标右击指定的数据库标识，在弹出的菜选中数据库文件夹；用鼠标右击指定的数据库标识，在弹出的菜单中选择单中选择“属性属性”项。出现数据库属性对话框示。项。出现数据库属性对话框示。(3) 定义定义CHECK约束约束3. 修改表结构修改表结构4. 在表中插入、修改或删除数据在表中插入、修改或删除数据3) 在选项卡中查看或修改相应的内容，击在选项卡中查看或修改相应的内容，击“确定确定”按键关闭对话框按键关闭对话框。3. 删除数据库删除数据库1) 用鼠标右击要删

33、除的数据库，在出现的弹出菜单中选择删除项。用鼠标右击要删除的数据库，在出现的弹出菜单中选择删除项。2) 在弹出的确认删除对话框中，单击在弹出的确认删除对话框中，单击“确认确认”按钮。按钮。1. 新建表新建表2. 定义表的完整性约束和索引定义表的完整性约束和索引(1) 定义索引和键定义索引和键(2) 定义表间关联定义表间关联1. 创建视图创建视图在图书在图书_读者数据库已经建立了图书、读者和借阅读者数据库已经建立了图书、读者和借阅3个表，结构为：个表，结构为： 图书（书号，类别，出版社，作者，书名，定价）；图书（书号，类别，出版社，作者，书名，定价）； 借阅（书号，读者书证号，借阅日期）；借阅（

34、书号，读者书证号，借阅日期）； 读者（书证号，姓名，单位，性别，电话）读者（书证号，姓名，单位，性别，电话）.建立一个视图，名为读者建立一个视图，名为读者_VIEW, 用用SQL语句表示为：语句表示为： CREATE VIEW 读者读者_VIEW AS SELECT 图书图书.*，借阅，借阅.* FROM 图书图书,借阅借阅,读者读者 WHERE 图书图书.书号书号=借阅借阅.书号书号AND借阅借阅.读者书证号读者书证号=读者读者.书证书证号号;利用利用SQL Server 2000的视图创建向导，来创建读者的视图创建向导，来创建读者_VIEW视图：视图：1) 打开企业管理器窗口，确认服务器，

35、打开数据库文件夹，选中新打开企业管理器窗口，确认服务器，打开数据库文件夹，选中新视图所在的数据库。视图所在的数据库。2) 选择菜单：工具选择菜单：工具向导，如所示。向导，如所示。3) 在如图向导选择对话框中，单击数据库左边的在如图向导选择对话框中，单击数据库左边的“+”号，使之展号，使之展开。选择开。选择“创建视图向导创建视图向导”项，单击项，单击“确定确定”按键。按键。 4) 欢迎进入创建视图向导对话框中简单介绍了该向导的功能。单击欢迎进入创建视图向导对话框中简单介绍了该向导的功能。单击“下一步下一步”按钮后，就会出现选择按钮后，就会出现选择“数据库名称数据库名称”对话框。对话框。5) 选择视图所属的数据库。本例的数据库为选择视图所属的数据库。本例的数据库为“图书图书_读者读者”。单击。单击“下一步下一步”按键。按键。6) 在选择对象对话框中，列出了指定数据库中所有用户定义的表和在选择对象对话框中，列出了指定数据库中所有用户定义的表和视图。用鼠标击表名后的视图。用鼠标击表名后的“包含在视图中包含在视图中”列，使复选框为选中状列，使复选框为选中状态。单击态。单击“下一步下一步”按键，进入选择列对话框。按键，进入选择列对话框。7) 列选择对话框中以表格形式列出了创建视图参考表的全部属性，列选择对话框中以表格形式列出了创