数据库技术及应用-第4章第5章课件

第4章关系数据库标准语言——SQL4.1SQL概述及特点4.2SQL的数据定义功能

4.3SQL的数据查询功能

4.4SQL的数据更新功能4.5嵌入式SQL4.6SQL的数据控制功能习题4

4.1SQL概述及特点

SQL是一种介于关系代数与关系演算之间的结构化查询语言，其功能并不仅仅是查询。SQL语言是一个通用的、功能极强的关系数据库语言。1.SQL的主要功能SQL的功能可以分为三类：(1)数据定义功能。(2)数据操纵功能。

(3)数据控制功能。

2.SQL的特点

SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，它除了具有一般关系数据库语言的特点外，还具有以下三个特点：

(1)SQL具有自主式和嵌入式两种形式。

(2)SQL具有语言简洁、易学易用的特点。

(3)SQL支持三级数据模式结构。

表4-1SQL的命令动词图4-1SQL对关系数据库模式的支持4.2SQL的数据定义功能

SQL的数据定义包括定义基本表、索引、视图和数据库，其基本语句如表4-2所示。表4-2SQL的数据定义语句

1.语句格式的约定符号在语句格式中：尖括号“〈〉”——实际语义。中括号“[]”中的内容——任选项。大括号“{}”或用分隔符“|”中的内容——必选项，即必选其中之一项。

[，…n]——表示前面的项可重复多次。

2.一般语法规定

SQL中的数据项(包括列项、表和视图)分隔符为“，”，其字符串常数的定界符用单引号“'”表示。

3.SQL的特殊语法规定

SQL的关键词一般使用大写字母表示。SQL语句的结束符为“；”。SQL一般应采用格式化书写方式。

4.2.1基本表的定义和维护功能

SQL的基本表定义和维护功能使用基本表的定义、修改和删除三种语句实现。

1.定义基本表

SQL语言使用CREATETABLE语句定义基本表，定义基本表语句的一般格式为

CREATETABLE[〈库名〉]〈表名〉(〈列名〉〈数据类型〉[〈列级完整性约束条件〉][，〈列名〉〈数据类型〉[〈列级完整性约束条件〉]][，…n][，〈表级完整性约束条件〉][，…n])；

(1)SQL支持的数据类型。不同的数据库系统支持的数据类型不完全相同。IBMDB2SQL支持的数据类型由表4-3列出，其中，使用最多的是字符型数据和数值型数据。表4-3IBMDB2SQL支持的主要数据类型

(2)列级完整性的约束条件。列级完整性约束是针对属性值设置的限制条件。SQL的列级完整性条件有以下几种：①NOTNULL或NULL约束。②UNIQUE约束。③DEFAULT约束。④CHECK约束。

(3)表级完整性约束条件。表级完整性约束条件是指涉及到关系中多个列的限制条件。在上述的CHECK约束中，如果约束条件表达式中涉及到多列数据，它便为表级约束。

【例4-1】用SQL建立学生_课程库中的基本表，其表结构为学生(学号，姓名，年龄，性别，所在系)

课程(课程号，课程名，先行课)

选课(学号，课程号，成绩)

2.修改基本表当已建立好的基本表随着应用环境和应用需求的变化而需要修改时，需要利用SQL的修改基本表语句修改表结构。SQL语言用ALTERTABLE语句来修改基本表，其一般格式为

ALTERTABLE〈表名〉[ADD(〈新列名〉〈数据类型〉[完整性约束][，…n])][DROP〈完整性约束名〉][MODIFY(〈列名〉〈数据类型〉[，…n])]；

(1)使用ADD子句增加新列。当需要向表中增加新列和新的完整性约束时，使用ADD字句对表结构进行修改操作。

【例4-2】向课程表中增加“学时”字段。

ALTERTABLE课程ADD学时SMALLINT；

(2)使用MODIFY子句修改列的原定义。

(3)使用DROP子句删除指定的完整性约束条件。

【例4-3】删除学生表中对“年龄”的默认值的定义。

3.删除基本表当不再需要某个基本表时，使用DROPTABLE语句删除它，其一般格式为

DROPTABLE〈表名〉；基本表一旦被删除，表中的数据及在此表基础上建立的索引、视图将自动地全部被删除掉。因此，执行删除基本表的操作时一定要格外小心。4.2.2索引的定义和维护功能

1.索引的作用使用索引有以下三方面的作用：

(1)可以明显地加快数据查询的速度。

(2)可保证数据的惟一性。

(3)可以加快连接速度。

2.建立索引的原则建立索引是加快数据查询的有效手段，在建立索引时，用户应当依照以下原则：

(1)索引的建立和维护由DBA和DBMS完成。

(2)大表应当建索引，小表则不必建索引。

(3)对于一个基本表，不要建立过多的索引。

(4)根据查询要求建索引。

3.建立索引的格式在SQL语言中，建立索引使用CREATEINDEX语句，其一般格式为

CREATE[UNIQUE][CLUSTER]INDEX〈索引名〉

ON〈表名〉(〈列名〉[〈次序〉][，〈列名〉[〈次序〉]]…)；

【例4-4】为学生课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按“学号”升序建立索引；课程表按“课程号”升序建立惟一索引；选课表按“学号”升序和“课程号”降序建立惟一索引。

CREATEUNIQUEINDEX学号ON学生(学号)；

CREATEUNIQUEINDEX课程号ON课程(课程号)；

CREATEUNIQUEINDEX选课号ON选课(学号ASC，课程号DESC)；

4.删除索引索引一经建立，就由系统来选择和维护，无须用户干预。建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统就会花费大量的时间来维护索引，这样就得不偿失了，因此，有时需要删除一些不必要的索引。

SQL语言使用DROPINDEX语句删除索引，其一般格式为

DROPINDEX〈索引名〉；

【例4-5】删除学生表的学生姓名索引。

DROPINDEX学生姓名；删除索引时，系统会同时从数据字典中删去有关对该索引的描述。

4.2.3视图的定义和维护功能视图是根据子模式设计的关系，它是由一个或几个基本表(或已定义的视图)导出的虚表。

1.视图的优点合理地使用视图能够对系统的设计和用户的使用带来很多方便。

(1)视图能够简化用户的操作。

(2)视图机制可以使用户以不同的方式看待同一数据。

(3)视图对数据库的重构提供了一定程度的逻辑独立性。

(4)视图可以对机密的数据提供安全保护。2.视图定义的格式SQL语言用CREATEVIEW来定义视图，其一般格式为

CREATEVIEW〈视图名〉[(列名组)]

AS〈子查询〉[WITHCHECKOPTION]；【例4-6】建立计算机系学生的视图。

CREATEVIEW计算机系学生

ASSELECT学号，姓名，年龄

FROM学生

WHERE所在系='计算机系'；例4-6中的视图是由一个基本表构造出的。

【例4-7】由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩。

【例4-8】将学生的学号、总成绩、平均成绩定义成一个视图。

3.视图的删除视图删除语句的一般格式为

DROPVIEW〈视图名〉；视图删除后，视图的定义将从数据字典中删除，而由该视图导出的其他视图的定义却仍存在于数据字典中，但这些视图已失效。

4.视图的查询和维护视图可以和基本表一样被查询，其使用方法与基本表相同，但利用视图进行数据增、删、改操作，会受到一定的限制。4.3SQL的数据查询功能

4.3.1SELECT语句介绍

SQL的数据查询语句中包括SELECT、FROM、WHERE、GROUPBY和ORDERBY子句。SELECT语句具有数据查询、统计、分组和排序的功能，其语句表达能力非常强大。

1.SELECT语句的语法

SELECT语句的语法格式为

SELECT〈目标列组〉

FROM〈数据源〉[WHERE〈元组选择条件〉][GROUPBY〈分列组〉[HAVING〈组选择条件〉]][ORDERBY〈排序列1〉〈排序要求1〉[，…n]]；(1)SELECT子句。(2)FROM子句。(3)WHERE子句。(4)GROUPBY子句。(5)ORDERBY子句。

2.SELECT语句的操作符

(1)算术操作符。算术操作符在SQL语句中表达数学运算操作。SQL的数学运算操作符只有四种，它们是：+(加号)、－(减号)、*(乘号)和/(除号)。

(2)比较操作符。比较操作符用于测试两个数据是否相等、不等、小于或大于某个值。SQL中的比较操作符包括：=(等于)、>(大于)、<(小于)、<=(小于等于)、>=(大于等于)、!=或<>(不等于)、!>(不大于)和!<(不小于)，共九种操作符。

(3)逻辑操作符。

SQL的逻辑操作符如表4-4所示。表4-4SQL的逻辑操作符

(4)组合查询操作符。组合查询操作符的使用格式为

〈查询1〉〈组合操作符〉〈查询2〉SQL的组合查询操作符是针对传统关系运算的操作符，它包括UNION、MINUS和INTERSECT三种。

(5)其他SQL操作符。其他SQL操作符是针对SELECT子句中的字段表设计的，它用于简写结果集的字段表和对字段值的限制说明。其他SQL操作符包括*、ALL和DISTINCT三种。

4.3.2SQL的查询实例

SQL的查询语句可以分为简单查询、连接查询、嵌套查询和组合查询四种类型。下面仍以学生课程数据库为例，介绍各种查询的描述格式。学生课程数据库包括三个基本表，其结构为学生(学号，姓名，年龄，所在系)

课程(课程号，课程名，先行课)

选课(学号，课程号，成绩)

1.简单查询简单查询是指在查询过程中只涉及到一个表的查询语句。简单查询是最基本的查询语句。

【例4-9】求数学系学生的学号和姓名。

【例4-10】求选修了课程的学生学号。

【例4-11】求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

【例4-12】求选修课程C1且成绩在80～90之间的学生学号和成绩，并将成绩乘以系数0.8输出。

【例4-13】求数学系或计算机系姓张的学生的信息。

【例4-14】求缺少了成绩的学生的学号和课程号。

2.连接查询包含连接操作的查询语句称为连接查询。连接查询包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等多种。由于连接查询涉及被连接和连接两个表，所以它的源表一般为多表。

(1)等值连接和非等值连接操作。连接查询中，用来连接两个表的条件称为连接条件或连接谓词。连接条件的一般格式为

[〈表名1〉.]〈列名1〉〈比较运算符〉[〈表名2〉.]〈列名2〉

其中，比较运算符主要有：=、>、<、>=、<=和!=。当连接运算符为“=”时，该连接操作称为等值连接；否则，使用其他运算符的连接运算称为非等值连接。当等值连接中的连接字段相同，并且在SELECT子句中去除了重复字段时，则该连接操作为自然连接。

【例4-15】查询每个学生的情况以及他(她)所选修的课程。

【例4-16】求学生的学号、姓名、选修的课程名及成绩。

【例4-17】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。

(2)自连接操作。连接操作不只是在两个表之间进行，一个表内还可以进行自身连接操作。表自身的连接操作称为自连接。

【例4-18】查询每一门课的间接先行课(即先行课的先行课)。表4-5课程表中的数据表4-6课程表自连接操作

(3)外部连接操作。在前面连接示例的结果集中只保留了符合连接条件的元组，而排除了两个表中没有对应的或匹配的元组情况，这种连接称为内连接。如果要求查询结果集中保留非匹配的元组，就要执行外部连接操作。表4-7职工和部门表数据

【例4-19】用SQL表达职工和部门的内连接、左外部连接和右外部连接的语句分别如下：内连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门=部门.部门号；左外部连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门*=部门.部门号；右外部连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门=*部门.部门号；表4-8职工和部门表各种连接的结果集对照表

3.嵌套查询在SQL语言中，一个SELECT…FROM…WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

(1)使用IN操作符的嵌套查询。当IN操作符后的数据集需要通过查询得到时，就需要使用IN嵌套查询。

【例4-20】求选修了高等数学的学生学号和姓名。

(2)使用比较符的嵌套查询。

IN操作符用于一个值与多值比较，而比较符则用于一个值与另一个值之间的比较。当比较符后面的值需要通过查询才能得到时，就需要使用比较符嵌套查询。

【例4-21】求C1课程的成绩高于张三的学生学号和成绩。

(3)使用ANY或ALL操作符的嵌套查询。表4-9ANY和ALL与比较符结合的操作符及其语意表

【例4-22】求其他系中比计算机系某一学生年龄小的学生(即求年龄小于计算机系年龄最大者的学生)。

【例4-23】求其他系中比计算机系学生年龄都小的学生。

(4)使用EXISTS操作符的嵌套查询。

【例4-24】求选修了C2课程的学生姓名。

【例4-25】求没有选修C2课程的学生姓名。

【例4-26】查询选修了全部课程的学生的姓名。

【例4-27】求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。

4.组合查询将SELECT语句的查询结果集再进行集合运算就构成了SQL的组合查询。SQL的组合查询操作符有UNION(并操作)、INTERSECT(交操作)和MINUS(差操作)三种。

【例4-28】求选修了C1课程或选修了C2课程的学生学号。

【例4-29】求选修C1课程，并且也选修C2课程的学生学号。

【例4-30】求选修了C1课程但没有选修C2课程的学生学号。表4-10基本的SQL函数

5.使用分组和SQL函数查询

SQL函数是综合信息的统计函数，包括计数、求最大值、求最小值、求平均值、求和值等。SQL函数可作为列标识符出现在SELECT子句的目标列或HAVING子句的条件中。

【例4-31】求学生的总人数。

【例4-32】求选修了课程的学生人数。

【例4-33】求课程和选修该课程的人数。

【例4-34】求选修课超过三门课的学生学号。4.4SQL的数据更新功能

4.4.1SQL的数据插入功能

SQL的数据插入语句有两种使用形式：一种是使用常量，一次插入一个元组；另一种是插入子查询的结果，一次插入多个元组。

1.使用常量插入单个元组使用常量插入单个元组的INSERT语句的格式为

INSERTINTO〈表名〉[(〈属性列1〉[，〈属性列2〉…)]

VALUES(〈常量1〉[，〈常量2〉]…)；

【例4-35】将一个新学生记录(学号：'03010'，姓名：'张三'，年龄：20，所在系：'计算机系')插入到学生表中。

【例4-36】插入一条选课记录(学号：'03011'，课程号：'C10'，成绩不详)。

2.在表中插入子查询的结果集如果插入的数据需要查询才能得到，就需要使用插入子查询结果集的INSERT语句。SQL允许将查询语句嵌到数据插入语句中，以便将查询得到的结果集作为批量数据输入到表中。含有子查询的INSERT语句的格式为

INSERT

INTO〈表名〉[(〈属性列1〉[，〈属性列2〉]…)]〈子查询〉；

【例4-37】求每个系学生的平均年龄，并把结果存入数据库中。4.4.2SQL的数据修改功能

SQL修改数据操作语句的一般格式为

UPDATE〈表名〉

SET〈列名〉=〈表达式〉[，〈列名〉=〈表达式〉][，…n][WHERE〈条件〉]；【例4-38】将学生表中全部学生的年龄加上2岁。【例4-39】将选课表中的数据库课程的成绩乘以1.2。

4.4.3SQL的数据删除功能数据删除语句的一般格式为

DELETE

FROM〈表名〉[WHERE〈条件〉]；

DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。

【例4-40】删除管理系的学生记录及选课记录。4.5嵌入式SQL

4.5.1嵌入式SQL的特点

SQL的功能只包括数据定义功能DDL、数据操纵功能DML和数据控制功能DCL，而缺少程序设计必要的程序流程控制和交互式功能，也缺少一些专业应用的功能，例如SQL没有分支、循环、赋值等语句等。

1.区别SQL和主语言在嵌入式SQL中，为了能够区分SQL语句与主语言语句，必须在所有的SQL语句前面加上前缀EXECSQL。SQL语句的结束标志则随主语言的不同而不同。

2.使数据库的工作单元与程序工作单元之间能够通信在含有嵌入式SQL的应用程序中，SQL语句负责管理数据库，主语言语句负责控制程序流程和其他功能。数据库的工作单元和程序工作单元之间通信的主要方式有以下两种：

(1)主语言通过主变量向SQL语句提供参数。

(2)SQL语句的当前工作状态和运行环境数据要反馈给应用程序。

3.使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾

SQL语言与主语言具有不同的数据处理方式。4.5.2不用游标的SQL语句

1.几种不需要使用游标的SQL语句下面四种SQL语句不需要使用游标：

(1)用于说明主变量的说明性语句。

(2)数据定义和数据控制语句。

(3)查询结果为单记录的查询语句。

(4)数据的插入语句和某些数据删除、修改语句。

2.不用游标的查询语句不用游标的查询语句的一般格式为

EXECSQLSELECT[ALL|DISTINCT]〈目标列表达式〉[，…n]INTO〈主变量〉[〈指示变量〉][，…n]FROM〈表名或视图名〉[，…n][WHERE〈条件表达式〉]；

【例4-41】查询学号为主变量givesno的值、课号为主变量givecno的值的学生选课记录。

3.不用游标的数据维护语句

(1)不用游标的数据删除语句。在删除语句中，WHERE子句的条件中可以使用主变量。

【例4-42】删除学号由主变量Sno决定的学生记录。

【例4-43】将计算机系所有学生的年龄都加上主变量Raise。

【例4-44】将计算机系学生的年龄置空。

(3)不用游标的数据插入语句。

INSERT语句的VALUES子句可以使用主变量和指示变量，当需要插入空值时，可以把指示变量置为负值。

【例4-45】将学号由主变量Sno、课程号由Cno决定的选课记录插入到数据库中。

4.5.3使用游标的SQL

游标机制用于解决SQL查询结果为集合而主语言处理方式为记录方式的矛盾。在处理中，必须使用游标的SQL语句有两种：一种是查询结果为多条记录的SELECT语句；另一种是使用游标的DELETE语句和UPDATE语句。

1.定义游标命令游标通过DECLARE语句定义，其语句格式为

EXECSQLDECLARE〈游标名〉CURSORFOR〈子查询〉[FORUPDATEOF〈字段名1〉[，…n]]；【例4-46】定义按主变量DEPT查询系里学生的游标。

2.打开游标命令游标通过OPEN命令打开，打开游标语句的格式为

EXECSQLOPEN〈游标名〉；

OPEN语句的作用是执行游标对应的查询语句，并将游标指向结果集的第一条记录前。

【例4-47】打开SX游标。

3.推进游标命令游标通过FETCH命令向前(或称向下)推进一条记录。推进游标的语句格式为

EXECSQLFETCH〈游标名〉INTO〈主变量组〉；推进游标的作用是将游标下移一行，读出当前的记录，将当前记录的各数据项值放到INTO后的主变量组中。

【例4-48】将打开的指向系的游标向前推进。

4.关闭游标命令由于许多系统允许打开的游标数有一定的限制，所以当数据处理完后应及时把不使用的游标关闭，以释放结果集占用的缓冲区及其他资源。关闭游标使用CLOSE命令，CLOSE命令的具体格式为

EXECSQLCLOSE〈游标名〉；4.6SQL的数据控制功能

4.6.1数据控制的方法数据库系统通过以下三步来实现数据控制。

1.授权定义具有授权资格的用户，如数据库管理员DBA或建表户DBO，通过数据控制语言DCL，将授权决定告知数据库管理系统。

2.存权处理数据库管理系统DBMS把授权的结果编译后存入数据字典中。

3.查权操作当用户提出操作请求时，系统首先要在数据字典中查找该用户的数据操作权限，当用户拥有该操作权时才能执行其操作，否则系统将拒绝其操作。

4.6.2SQLServer的数据库操作权限

1.隐含特权隐含特权是系统内置权限，是用户不需要进行授权就可拥有的数据操作权。

2.系统特权系统特权又称为语句特权，它相当于数据定义语句DLL的语句权限。不同的数据库系统规定的系统权限不同，SQLServer中的系统特权如表4-11所示。表4-11SQLServer中的系统特权

3.对象特权对象特权类似于数据库操作语言DML的语句权限，它指用户对数据库中的表、视图、存储过程等对象的操作权限。SQLServer中的对象特权如表4-12所示。表4-12SQLServer中的对象特权

4.6.3数据操作权限的设置数据操作权限的设置语句包括授权语句、收权语句和拒绝访问语句三种。

1.授权语句系统授权的授权语句格式为

GRANT〈系统特权组〉To〈用户组〉|PUBLIC[WITHGRANTOPTION]；

【例4-49】把修改学生学号和查询学生表的权力授予用户王平。

【例4-50】把建立数据库和备份数据库的权力赋给用户王平。

2.收权语句数据库管理员DBA、数据库拥有者(建库户)DBO或数据库对象拥有者DBOO(数据库对象主要是基本表)可以通过REVOKE语句将其他用户的数据操作权收回。

REVOKE语句的一般格式为

REVOKE〈权限组〉|ALLPRIVILIGES[ON〈对象名〉]

FROM〈用户名组〉|PUBLIC；

【例4-51】将用户王平的可以在学生表中修改学生学号的权利收回。

3.拒绝访问语句拒绝访问语句的一般格式为

DENYALL[PRIVILIGES]|〈权限组〉[ON〈对象名〉]TO〈用户组〉|PUBLIC；其中，ON子句用于说明对象特权的对象名；对象名指的是表名、视图名、视图和表的字段名或者过程名。习题4

4.1试述SQL语言的特点。

4.2在嵌入式SQL中，如何区分SQL语句和主语言语句？

4.3SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，语句INSERT、DELETE、UPDATE实现______功能。

A.数据查询B.数据操纵C.数据定义D.数据控制

4.4下面列出的关于视图(VIEW)的条目中，不正确的是______。

A.视图是外模式

B.视图是虚表

C.使用视图可以加快查询语句的执行速度

D.使用视图可以简化查询语句的编写

4.5在SQL语言的SELECT语句中，实现投影操作的是______子句。

A.SELECTB.FROMC.WHERED.GROUPBY

4.6SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，语句ALTERTABLE实现______功能。

A.数据查询 B.数据操纵

C.数据定义 D.数据控制

4.7在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的方法是创建______。

A.另一个表 B.游标 C.视图 D.索引4.8设职工_社团数据库有三个基本表：职工(职工号，姓名，年龄，性别)社会团体(编号，名称，负责人，活动地点)参加(职工号，编号，参加日期)其中：①职工表的主码为“职工号”。②社会团体表的主码为“编号”；外码为“负责人”，被参照表为职工表，对应属性为“职工号”。③参加表的“职工号”和“编号”为主码；“职工号”为外码，其被参照表为职工表，对应属性为“职工号”；“编号”为外码，其被参照表为社会团体表，对应属性为“编号”。试用SQL语句表达下列操作：

(1)定义职工表、社会团体表和参加表，并说明其主码和参照关系。

(2)建立下列两个视图：社团负责人(编号，名称，负责人职工号，负责人姓名，负责人性别)

参加人情况(职工号，姓名，社团编号，社团名称，参加日期)

(3)查找参加唱歌队或篮球队的职工号和姓名。(4)查找没有参加任何社会团体的职工情况。(5)查找参加了全部社会团体的职工情况。(6)查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。(7)求每个社会团体的参加人数。(8)求参加人数最多的社会团体的名称和参加人数。

(9)求参加人数超过100人的社会团体的名称和负责人。

(10)把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。

4.9设工程_零件数据库中有四个基本表：供应商(供应商代码，姓名，所在城市，联系电话)

工程(工程代码，工程名，负责人，预算)

零件(零件代码，零件名，规格，产地，颜色)

供应零件(供应商代码，工程代码，零件代码，数量)

试用SQL语句完成下列操作：(1)找出天津市供应商的姓名和电话。(2)查找预算在50000～100000元之间的工程的信息，并将结果按预算降序排列。(3)找出使用供应商S1所供零件的工程号码。(4)找出工程项目J2使用的各种零件名称及其数量。(5)找出上海厂商供应的所有零件号码。(6)找出使用上海产的零件的工程名称。(7)找出没有使用天津产零件的工程号码。(8)把全部红色零件的颜色改成蓝色。(9)将由供应商S5供给工程代码为J4的零件P6改为由S3供应，并作其他必要的修改。(10)从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录。

4.10关系数据模型如下：学生S(SNO，SN，SEX，AGE)

课程C(CNO，CN，PCNO)PCNO为直接先行课号选课SC(SNO，CNO，GR)GR为课程考试成绩试用SQL写出查询程序：

(1)将选修课程“DB”的学生学号SNO、姓名SN建立视图SDB。

(2)查询选修课程“DB”的学生姓名SN。第5章SQLServer2000关系数据库管理系统5.1SQLServer2000的管理功能

5.2SQLServer2000数据库操作工具及其使用习题55.1SQLServer2000的管理功能

5.1.1SQLServer2000的管理任务

1.SQL服务器的安装、配置和管理为了使用SQL服务器，必须先对它们进行安装和配置。当系统中有多台SQL服务器时，还需要将它们链接，使它们协同工作。

2.服务管理服务管理是指在SQL客户机安装相应的软件并正确配置它们。为了使SQL客户机能够访问SQL服务器提供的资源和功能，还需要启动相应的服务并对这些服务进行管理。

SQL服务器为客户机提供了三种服务：

(1)数据库访问服务，它使得客户能够访问数据库。

(2)服务代理，它可以使SQL客户机具有作业调度功能。

(3)分布式事务处理服务，它可以用于SQLServer之间的分布式事务处理。

SQLServer还提供了ServiceManager(服务器管理)来管理这三种服务。

3.数据库管理数据库管理包括数据库的创建、修改、删除以及备份和恢复。

4.数据库对象的管理数据库对象是指保存在数据库中的表、视图、存储过程、触发器、规则、缺省值等。

5.数据管理数据管理包括记录的添加、删除和修改以及数据的传递与复制等。数据管理工作一般在SQLServer查询分析器中直接运行Transact-SQL语句来实现。

6.安全性管理安全性管理主要包括用户管理和权限管理两方面的内容。安全性管理也可通过使用SQLServer企业管理器来完成。

7.作业管理作业管理是SQLServerAgent的职能，作业管理的主要工作仍是在SQLServer企业管理器中实现。它包括以下三个方面的内容：

(1)警报管理：定义一些情况(非正常情况，例如系统错误)，当这些情况出现时，系统会通知操作员或者启动作业。

(2)操作员管理：定义接受警报或者执行作业的人员，一旦出现警报管理中的事件，系统就会通知相应的操作员。

(3)任务管理：当系统出现非正常情况时，一方面要向操作员报警，同时也要进行必要的事件处理。任务管理的主要功能是定义处理警报情况所必需的维护性工作，出现问题实施其处理工作。

8.集成操作集成操作指SQLServer2000与电子邮件系统和Web服务器的交互操作，这些管理工作在SQLServer企业管理器中完成。

5.1.2SQLServer2000的管理工具

SQLServer2000的管理工具和向导是系统为用户提供的工作环境和对话工具，用户通过它们可以更有效、更方便地进行数据库管理工作。在SQLServer2000的开始程序组中，共有11个选项，如图5-1所示。图5-1SQLServer2000的管理工具

1.查询分析器查询分析器是在开发数据库应用系统时使用最多的工具。查询分析器的主要作用是编辑Transact-SQL，将其发送到服务器，并将执行结果及分析显示出来(或进行存储)。查询分析功能主要是通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能。查询分析器的界面如图5-2所示。图5-2SQLServer2000查询分析器

2.导入和导出数据导入和导出数据是通过一个向导程序“数据转换服务(DTS)”实现的，其作用是使SQLServer与任何OLEDB、ODBC、JDBC或文本文件等多种不同类型的数据库之间实现数据传递。

DTS数据转换服务向导界面如图5-3所示。图5-3DTS数据转换向导界面

3.服务管理器

SQLServer服务管理器界面如图5-4所示。服务管理器用于开启、暂停和停止SQL服务工作，同时也可以通过它设置服务是否自动启动。图5-4SQLServer服务管理器界面

4.客户机网络连接工具和服务器网络连接工具客户机网络连接工具用于设置本机作为服务器或客户机所使用的缺省网络链接库，并可为不支持缺省网络库的SQL服务器设置连接方式。服务器网络连接工具用于设置本机作为服务器时允许的连接协议，以支持不同配置的客户端。尽管两者针对的对象不同，但设置方法却非常接近。下面我们以客户机网络连接工具为例进行解释。客户机网络连接工具的界面如图5-5所示。图5-5客户机网络连接工具图5-6客户机网络连接工具中新增网络库

5.联机丛书联机丛书是SQLServer2000为用户提供的在线帮助功能。在联机丛书中，全面而详细地介绍了有关SQLServer2000的功能、管理及开发方法、TSQL语法等内容。联机丛书的界面如图5-7所示，用户可以选择左边的索引项，右边将是与索引相关的信息。图5-7联机丛书的界面

6.企业管理器企业管理器是SQLServer2000功能最强大的管理工具之一。启动企业管理器后，可以看到如图5-8所示的窗口。图5-8企业管理器的界面

7.事件探查器事件探查器即服务器活动跟踪程序，用于监视与分析SQLServer活动、SQLServer服务器的网络进出流量或发生在SQLServer上的事件。事件探查器的界面如图5-9左图所示，其内容为事件的操作序列。图5-9SQLServer的事件探查器

8.在IIS中配置SQLXML支持

SQLServer2000含有SQLServer的IIS虚拟目录管理器，通过它可以在IIS(MicrosoftInternetInformationServices，因特网信息服务器)中定义一个与SQLServer2000实例相关的虚拟目录。然后，Internet应用程序便可以编写出能够引用SQLServer虚拟目录且内含T-SQL命令语句的URL字符串。T-SQL命令语句会被送到虚拟目录相关的SQLServer2000实例，其执行结果会以一个XML(可扩展标记语言)文件形式返回。

SQLServer的IIS虚拟目录管理器界面如图5-10所示。图5-10在IIS中配置SQLXML界面

9.分析管理器分析管理器是一个提供用户界面以访问分析服务器及其元数据知识库的控制台应用程序，其界面如图5-11所示。

10.英语查询工具英语查询工具为用户提供了用英语询问，而不是用SQL语句进行数据查询的方式。图5-11分析管理器界面5.2SQLServer2000数据库操作工具及其使用

5.2.1用企业管理器创建数据库下面我们通过企业管理器建立一个图书_读者数据库。完成后图书_读者数据库中将包括一个数据文件和一个日志文件，各文件的属性如表5-1所示。表中的文件组是指相似特性的文件集合。表5-1图书_读者数据库中各列的属性

1.创建数据库使用企业管理器创建数据库的步骤为：①从“开始”菜单中依次选择：程序(MicrosoftSQLServer2000(企业管理器。②选中需要在其上创建数据库的服务器，单击前面的“+”号，使其展示为树形目录。③选中“数据库”文件夹，单击鼠标右键，在弹出的菜单上选择“新建数据库”命令，如图5-12所示。图5-12在数据库的弹出菜单中选择“新建数据库”命令④此时，将弹出“数据库属性”对话框。在“常规”选项卡中，输入数据库名，选择SQL服务器，如图5-13所示。⑤选择“数据文件”选项卡，输入图书_读者数据库的数据文件属性，包括文件名、存放位置和文件属性，如图5-14所示。图5-13“数据库属性”对话框中的“常规”选项卡图5-14“数据库属性”对话框中的“数据文件”选项卡⑥选择“事务日志”选项卡，输入数据库的日志文件属性，包括文件名、存放位置、大小和文件属性，如图5-15所示。⑦单击“确定”按钮，关闭对话框。图5-15“数据库属性”对话框中的“事务日志”选项卡

2.查看和修改数据库属性参数已经建好的数据库，有时还需要对它的属性参数进行查看和修改，因此有必要知道如何查看和修改数据库的属性参数。查看和修改数据库属性的步骤为：①启动企业管理器，使数据库所在的服务器展开为树形目录。②选中数据库文件夹，使之展开；用鼠标右击指定的数据库标识，在弹出的菜单中选择“属性”项，弹出数据库属性对话框，如图5-16所示。③在选项卡中查看或修改相应的内容，单击“确定”按钮关闭对话框。图5-16数据库属性

3.删除数据库对于不需要的数据库，可以通过下面的方法删除：①用鼠标右击要删除的数据库，在弹出菜单中选择“删除”项。②在确认删除对话框中，单击“确认”按钮。

5.2.2在企业管理器中定义和管理表

1.新建表在SQLServer2000的数据库中，文件夹是按数据库对象的类型建立的，文件夹名是该数据库对象名。要建立“图书”表，先选中图书_读者数据库中的表文件夹，单击鼠标右键，在弹出的菜单中选择“新建表”，弹出输入表结构对话框，如图5-17所示。

图5-17数据库中的表文件夹的弹出菜单输入表结构时应注意以下几点：

(1)“列名”列用于输入字段名，例如“编号”、“类别”等，列名类似于变量名，其命名规格与变量一致。列名中不允许出现空格，一张表也不允许有重复的列名。

(2)“数据类型”列中的数据类型是通过选择方法，而不是直接键入数据类型字符输入的。

(3)“长度”列、精度和小数位数项不是所有字段都必选的。

(4)“允许空”列用于设置是否允许字段为空值，默认项用于设置字段的缺省值。

(5)“列名”前的一列按钮为字段标注按钮列。钥匙图标说明这个字段为主码，黑三角图标说明所指示行为当前字段。

(6)在对话框中单击鼠标右键，则出现弹出菜单，如图5-18所示。图5-18建表对话框中的弹出框

(7)字段输入完后，就可以关闭建表对话框了。最后，会弹出输入表名对话框，如图5-20所示。图5-19表属性对话框图5-20输入表名对话框

2.定义表的完整性约束和索引表的约束包括码(主键)约束、外码约束(关联或关系约束)、惟一性约束、Check(检查)约束等四种，这些约束可以在表属性对话框中定义。

(1)定义索引和键。选择“索引/键”选项卡，其界面如图5-19所示。

(2)定义表间关联。选择“关系”选项卡，其界面如图5-21所示。图5-21表属性对话框中的“关系”选项卡

(3)定义CHECK约束。选择“CHECK约束”选项卡，其界面如图5-22所示，可进行相应设置。图5-22表属性对话框中的“CHECK约束”选项卡

4.在表中插入、修改或删除数据在企业管理器中，对表进行数据增、删、改操作非常简便，其操作方法是：①选中服务器，展开数据库文件夹，进一步展开指定的数据库。单击表文件夹，找到需要更新数据的基本表。②用鼠标右键单击要更新数据的表，出现弹出菜单，如图5-23所示。图5-23基本表的弹出菜单在弹出的菜单中选择：打开表(返回全部行，就会出现表数据更新对话框，如图5-24所示。③需要数据插入时，就在最后一条记录后输入一条记录。当鼠标单击其他行时，输入的记录会自动保存在表中。④需要修改记录时，直接对表中已有记录的数据进行改动，用新值替换原值。图5-24在表中插入、修改或删除数据界面

5.2.3视图的创建和维护

1.创建视图假设在图书_读者数据库已经建立了图书、读者和借阅三个表，它们的结构为图书(书号，类别，出版社，作者，书名，定价)

借阅(书号，读者书证号，借阅日期)

读者(书证号，姓名，单位，性别，电话)①打开企业管理器窗口，确认服务器，打开数据库文件夹，选中新视图所在的数据库。②选择“工具”菜单中的“向导”命令，如图5-25所示。③在如图5-26所示的“选择向导”对话框中单击数据库左边的“+”号，使之展开。选择“创建视图向导”项，单击“确定”按钮。④

进入创建视图向导后，首先出现的是欢迎进入创建视图向导对话框，其中简单介绍了该向导的功能，如图5-27所示。

图5-25选择“向导”命令图5-26在“选择向导”对话框中选择“创建视图向导”图5-27进入创建视图向导图5-28“选择数据库”对话框⑤在“选择数据库”对话框中，选择视图所属的数据库。本例的数据库为“图书_读者”。单击“下一步”按钮，则进入如图5-29所示的“选择对象”对话框。⑥在“选择对象”对话框中，列出了指定数据库中所有用户定义的表和视图。单击“下一步”按钮，则进入“选择列”对话框，如图5-30所示。⑦“选择列”对话框中以表格形式列出了创建视图参考表的全部属性，每个属性占表的一行。图5-29创建视图向导的“选择对象”对话框图5-30创建视图向导的“选择列”对话框⑧在“定义限制”对话框中，输入表的连接和元组选择条件。本例应输入“WHERE图书.书号=借阅.书号AND借阅.读者书证号=读者.书证号”。单击“下一步”按钮后，出现“命名视图”对话框，如图5-32所示。⑨在“命名视图”对话框中输入所建视图的标识名。本例的视图名为“读者_VIEW”。单击“下一步”按钮，则出现如图5-33所示的视图创建完成对话框。图5-31创建视图向导的“定义限制”对话框图5-32创建视图向导的“命名视图”对话框图5-33创建视图向导的完成对话框

2.查看和修改视图视图创建好后，就可以利用它进行查询信息了。如果发现视图的结构不能很好地满足要求，还可以在企业管理器对它进行修改。其操作步骤为：①在企业管理器中，选择服务器和数据库，并使数据库展开，然后用鼠标右键单击要修改结构的视图，会弹出视图功能菜单，如图5-34所示。图5-34视图的功能弹出菜单②在弹出菜单上选择“设计视图”项，则弹出一个视图设计对话框，如图5-35所示。图中列出了视图结构的详细内容，当对其修改完毕后关闭窗口，新的视图结构就会取代原先的结构。图5-35视图设计对话框

(1)在图表区中为视图添加表。用鼠标右键单击图表区中的空白处，会出现如图5-36所示的弹出菜单。

(2)在图表区中从视图中移去表。

(3)在图表区中修改视图中所包含的列。

(4)在表格区中修改视图的结构。图5-36图表区空白处的弹出菜单

3.删除视图删除视图的操作步骤如下：①在企业管理器中，将鼠标指向数据库中的视图文件夹，单击鼠标右键。②在弹出的菜单中，选择“删除”项，出现如图5-37所示的“除去对象”对话框。③选中欲删除的视图，单击“全部除去”按钮，被选中的视图就会从视图中被移出。图5-37数据库的“除去对象”对话框

5.2.4关联表的创建和维护

1.创建关联表假如要在图书_读者数据库中建立一个读者_借