关系数据库与SQL语言课后作业

第2课 构造学生信息管理系统数据库 1. 给出下列术语的定义，并加以理解：

函数依赖、完全函数依赖、传递函数依赖、1NF、2NF、3NF。 答：

函数依赖：设R(U)是属性集U上的关系模式，X，Y是U的子集。如果对于R的所有关系r 都存在：对于X的每一个具体值，Y都只有一个具体值与之对应，则称属性Y函数依赖于属性X。或者说，属性X函数决定属性Y，记作X→Y。 完全函数依赖：在关系模式R(U)中，如果X→Y，并且对于X的任何一个真子集X’，都有 X’?? Y，则称Y完全函数依赖于X，记作XY。

传递函数依赖：在关系模式R(U)中，如果X→Y，Y→Z，且YX，Y?? X，则称Z传递函数依赖于X，记作XY。 1NF：每一个分量必须是不可分的数据项。满足了这个条件的关系模式就属于第一范式(1NF)，记作关系R1NF。

2NF：若关系模式R满足第一范式，即R1NF，并且每个非主属性都完全函数依赖于R的码（即不存在部分函数依赖），则R满足第二范式，记为R2NF。

3NF：若关系模式R∈2NF，且它的每一个非主属性都不传递函数依赖于码，则R满足第三范式，记作R∈3NF。 2. 试述数据库设计方法和基本过程。 答：

数据库常用的设计方法有： 1）直观设计法（手工试凑法） 2）规范设计法，常用的有如下： ①新奥尔良法

②基于E-R模型的数据库设计方法。 ③基于3NF的数据库设计方法。 ④基于视图的数据库设计方法 3）计算机辅助设计法 4）自动化设计法

数据库设计的一般过程：

需求分析→概念结构设计→逻辑结构设计→物理结构设计→数据库实施→数据库运行和维护。 3. 什么是E-R图？构成E-R图的基本要素是什么？ 答：

E-R图为实体-联系图，提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型。 构成E-R图的基本要素是：

矩形。用来表示实体，矩形框内写上实体名。

椭圆。用来表示实体的属性，椭圆框内写上属性名，并用无向线把实体与属性连接起来。

菱形。用来表示实体与实体的联系，菱形框内写上联系名，用无向线把菱形与有关实体连接起来，并在无向线旁标注联系的类型。

无向线。用于实体与属性、实体与联系之间的连接。 4. 试述E-R图转换为关系模式的转换规则。 答：

将E-R图转换为关系模型实际上就是将实体、属性和联系转换成关系模式。在转换中要遵循以下原则： 1）实体的转换规则

将E-R图中的每一个常规实体转换为一个关系，实体的属性就是关系的属性，实体的码就是关系的码。 2）实体间联系的转换规则

①一个1：1可以转换为一个独立的关系模式，也可以与任意一端所对应的关系模式合并。如果转换为一个独立的关系模式，则与联系相连的各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均是该关系的候选码。 如果将联系与任意一端实体所对应的关系模式合并，则需要在被合并的关系中增加属性，其新增的属性为联系本身的属性和与联系相关的另一个实体的码。

②一个1：n联系可以转换为一个独立的关系模式，也可以与n端所对应的关系模式合并。

如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。

如果与n端所对应的关系合并，则在n端实体中增加新属性，新属性由联系对应的1端实体的码和联系自身的属性构成，新增属性后原关系的码不变。

③一个m：n联系转换为一个关系模式。转换的方法为与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，新关系的码为两个相连实体码的组合。 5. 现有一个局部应用，包括两个实体：“出版社”和“作者”，这两个实体是多对多的联系，请设计适当的属性，画出E-R图，再将其转换为关系模式。

答：

E-R图如下： 关系模式：

出版社（名称，地址，电话，网址） 作者（证件号，名称，性别，年龄） 出版（出版社名称，作者证件号）

6. 请设计一个图书馆数据库，此数据库中对每个借阅者保存的记录包括：读者号，姓名，地址，性别，年龄，单位。对每本书保存有：书号，书名，作者，出版社。对每本被借出的书保存有读者号、借出日期和应还日期。要求：给出该图书馆数据库的E-R图，再将其转换为关系模式。 答：

关系模式：

读者（读者号，姓名，地址，性别，年龄，单位）

图书（书号，书名，作者，出版社，读者号，借出日期，应还日期） 借阅（读者号，书号，借出日期，应还日期） E-R图如下：

7. 图1是一个销售业务管理的E-R图，请把它转换成关系模式。

答：

工厂（厂名，厂长，地址） 产品（编号，型号，单价） 用户（姓名，地址，电话） 工厂－产品（厂名，编号） 工厂－用户（厂名，姓名） 产品－用户（编号，姓名）

8. 现在要建立关于系、学生、班级、学会诸信息的一个关系数据库。规定：一个系有若干专业，每个专业每年只招一个班，每个班有若干学生，一个系的学生住在同一个宿舍区，每个学生可参加若干学会，每个学会有若干学生。 描述学生的属性有：学号、姓名、出生日期、系号、班号、宿舍区； 描述班级的属性有：班号、专业名、系号、人数、入校年份； 描述系的属性有：系号、系名、系办地点、人数；

描述学会的属性有：学会名、成立年份、地点、人数、学生参加某会有一个入会年份。 ① 请写出关系模式。

② 写出每个关系模式的函数依赖集，指出是否存在传递依赖。在函数依赖的决定因素是多属性的情况下，讨论函数依赖是完全依赖，还是部分函数依赖。 ③ 指出各个关系模式的关键字。 答：

①关系模式

学生（学号，姓名，出生日期，系号，班号，宿舍区） 班级（班号，专业名，系号，人数，入校年份） 系（系号，系名，系办地点，人数）

学会（学会名，成立年份，地点，人数） 会员（学号，学会名，入会年份）。

②写出每个关系模式的函数依赖集，指出是否存在传递依赖。在函数依赖的决定因素是多属性的情况下，讨论函数依赖是完全依赖，还是部分函数依赖。 学生：

主键：学号；外键：班号，系号。

最小函数依赖集：学号→姓名，学号→出生日期，学号→班号，班号→系号，系号→宿舍区。 存在传递函数依赖：学号→系号（学号→班号，班号→系号），学号→宿舍区（班号→系号，系号→宿舍区）。 班级：

最小函数依赖集：班号→专业名，班号→系号，班号→人数，班号→入校年份 主键：班号；外键：系号。

存在传递函数依赖：班号→系号（班号→专业名，专业名→系号） 系：

最小函数依赖集：系号→系名，系号→系办地点，系号→人数 主键：系号；外键：无 不存在传递函数依赖。 学会：

最小函数依赖集：学会名→成立年份，学会名→地点，学会名→人数 主键：学会名；外键：无 不存在传递函数依赖。 会员：

最小函数依赖集：（学号，学会名）→入会年份 主键：（学号，学会名）；外键：学号，学会名。 不存在传递函数依赖和部分函数依赖。 ③ 指出各个关系模式的关键字

学生 主键：学号；外键：班号，系号。 班级 主键：班号；外键：系号。 系 主键：系号；外键：无。 学会 主键：学会名；外键：无 会员 主键：（学号，学会名）；外键：学号，学会名。 9. 设一个仓库管理系统的局部应用有如下三个实体： 仓库：仓库号、仓库名称、地点、面积 职工：职工号、职工姓名、性别、年龄 货物：货物号、货物名、价格

其中，仓库和职工是一对多的关系，仓库和货物是多对多的关系。画出该局部应用的E-R模型，并将其转换为关系模式。 答：

仓库和职工局部E-R图：

仓库和货物局部E-R图： 关系模式：

职工（职工号，仓库号，职工姓名，性别，年龄） 仓库（仓库号，仓库名称，地点，面积） 货物（货物号，货物名，价格） 存储（仓库号，货物号）

10. 关系规范化的作用是什么？第一范式至第三范式，每种范式的特点是什么 答：

一个低一级范式的关系模式，通过模式分解转化为若干个高一级范式的关系模式的集合，这种分解过程叫作关系模式的规范化（Normalization）。规范化的目的就是使结构合理，消除存储异常，使数据冗余尽量小，便于插入、删除和更

新。

第一范式要求关系中属性必须是原子项，即不可再分的基本类型，集合、数组和结构不能作为某一属性出现，严禁出现“表中有表”的情况。

2NF就是不允许关系模式的属性之间有这样的函数依赖X→Y，其中X是码的真子集，Y是非主属性。 一个关系模式R满足不存在部分函数依赖和传递函数依赖，则R满足3NF。

第3课 初识SQL Server

1. 安装SQL Server 2000对硬件有什么需求？ 答：

计算机的芯片、内存、硬盘空间等配备需要满足最低的硬件配置要求： 处理器 Pentium 2 或更高

内存 至少64MB，建议128MB 或更多

硬盘空间 需要约250MB的程序空间，以及预留200M的数据空间 显示器 需要设置成至少800*600模式，才能使用其图形分析工具 2. SQL Server 2000提供了哪些主要组件，其功能是什么？ 答：

查询分析器：提供图形化的查询分析工具。

导入和导出数据：提供SQL Server 与其他数据源之间的数据库服务。 客户端网络实用工具：用于管理客户端网络连接配置。

联机丛书：为用户提供Microsoft SQL Server 联机文档资料，它具有索引和全文搜索能力，可根据关键字来快速查找用户所需资料。

企业管理器：SQL Server 企业管理器，它是一个具有界面的综合管理工具，可完成如下工作：

管理SQL Server 服务器和表、视图、存储过程、触发器、索引、规则、默认等数据库对象，以及用户定义数据库类型； 管理SQL Server 登陆标识和用户； 设置数据库对象的访问权限； 管理备份设备和数据库；

备份数据库和事务日志、恢复数据库； 实现数据转换服务；

创建全文索引、数据库图表和数据库维护计划； 执行数据库Web 出版和复制操作。

事件探查器：SQL Server 事件探查器，它能实时地捕获服务器活动记录，监视SQL Server 所产生的事件，并可将监视结果输出到文件、表或屏幕上。

服务管理器：SQL Server 服务管理器，用来启动、暂停、继续和停止MS SQL Server、SQLServerAgent、Microsoft Search等服务。

3. 下面的硬件设备，哪一个不是SQL Server 2000系统必备的？ A. CPU B. RAM C.显示器 D.打印机 答：D

4. SQL Server 2000数据库系统不能运行在哪个平台上？ A. Windows 2000 Server B. Windows NT Server

C. Windows 98 D. Windows 2000 Professional 答：C

5. 企业管理器的用途是什么？使用它可以完成哪些操作？ 答：

用途 ：SQL Server企业管理器是一个集成化的操作环境，几乎所有的操作都可以在该工具中完成。 操作：创建数据库、执行数据库备份、执行各种向导工具、服务配置、数据复制等。 6. 查询分析器可以进行的操作有哪些？ 答：

在命令语句编辑窗口中，创建查询和其他SQL脚本，并针对SQL Server 数据库来分析和执行它们，执行结果在结果窗格中以文本或表格形式显示，还允许用户将执行的结果保存到报表文件中或导出到指定文件中； 利用模板功能，可以借助预定义脚本来快速创建数据库和数据库对象； 利用对象浏览器脚本功能，快速复制现有数据库对象； 在参数未知的情况下执行存储过程；

调式查询性能问题，包括显示执行计划、显示服务器跟踪、显示客户统计、索引优化向导等； 在树型对象浏览窗格中，可以定位数据库对象或查看和使用对象； 在“打开表”窗口中，可快速插入、更新或删除表中的行。 7. 可以使用SQL Server提供的哪种工具来执行T-SQL语句？ 答：

可以使用SQL Server 查询分析器。

8. 使用什么工具可启动SQL Server的服务？ 答：

1）利用服务管理器； 2）利用DOS命令启动。

9. 如何以net命令来启动SQL Server 的服务？

答： 在DOS命令运行方式下输入net start mssqlserver 即可启动。 第4课 创建和管理学生信息管理系统数据库

1. SQL Server 系统数据库有哪些，它们各自的功能是什么？ 答：

系统数据库有四个，分别是：master ,tempdb ,model ,msdb

master数据库记录了所有系统信息，所有的注册帐户和密码，所有的系统设置信息。Master数据库还记录了所有用户定义数据库的存储位置和初始化信息。

Tempdb数据库记录了所有的临时表格，临时数据和临时创建的存储过程。 Model数据库是创建所有用户数据库和tempdb数据库的模板。

Msdb数据库供SQL Server代理程序调度警报和作业以及记录操作员时使用。 2．SQL Server 系统表有何作用？ 答：

SQL Server2000中的每个数据库都包含系统表，是在创建数据库时自动产生的。这些表用来记录SQL Server组件所需的数据。每个数据库中的系统表，为每个数据库存储数据库级系统信息。SQL Server的操作能否成功，取决于系统表信息的完整性。

3．什么是SQL Server 系统存储过程？有何作用？ 答：

系统存储过程是预先经过编译的SQL语句的集合，所有系统存储过程的名字都以sp为前缀，下划线后是这个系统存储过程的功能简介。使用系统存储过程可以方便地查看有关数据库和数据库对象的相关信息。 4．SQL Server2000的数据库对象有哪些？ 答：

对象有系统数据库，系统表，系统存储过程，实例数据库。

5．创建，修改和删除数据库的T-SQL语句是什么？ 答：

用CREATE DATABASE 语句来创建数据库，用ALLTER DATABASE 语句来修改数据库，用DROP语句删除数据库。 6．创建一个名为“RSGL”的数据库，数据文件的逻辑文件名为“rsgl_data”，物理文件名为“D:\\rsgl_data.mdf”，初始容量为1MB，最大容量为20MB，按2MB增长；日志文件的逻辑文件名为“rsgl_log”，物理文件名为“D:\\rsgl_log.ldf”初始容量为1MB，最大容量为10MB，按2%比例增长。写出T-SQL语句。 答：

CREATE DATABASE rsgl ON

( NAME=’rsgl_data’,

FILENAME=’d:\\rsgl_data.mdf’, SIZE=1MB, MAXSIZE=20MB, FILEGROWTH=2MB) LOG ON

(NAME=’rsgl_log’,

FILENAME=’d:\\rsgl_log.ldf’, SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=2%) GO

7．写出T-SQL语句，完成对RSGL数据库进行如下的操作。 答：

1.将RSGL数据库的初始分配空间1MB，扩充到10MB； ALTER DATABASE RSGL MODIFY FILE

(NAME=’rsgl_data’, SIZE=10MB ) GO

2.修改RSGL数据库文件的属性参数。将最大大小改为不限制，增长方式改为每次按10%比例增长。 ALTER DATABASE RSGL MODIFY FILE

(NAME=’rsgl_data’, MAXSIZE=UNLIMITED FILEGROWTH=10%) GO

3.修改RSGL日志文件的属性参数。将最大大小改为20MB，增长方式改为每次按5MB增长。

ALTER DATABASE RSGL MODIFY FILE (NAME=’rsgl_log’, MAXSIZE=20MB, FILEGROWTH=5MB) GO

4.将RSGL数据库的空间压缩至最小容量。 USE RSGL GO

DBCC SHRINKDATABASE(‘RSGL’) 5.将RSGL数据库更名为“NEW_RSGL”。 EXEC sp_renamedb ‘RSGL’, ‘NEW_RSGL’ 6.删除NEW_RSGL数据库。 DROP DATABASE NEW_RSGL

第5课 创建和管理学生信息管理系统中的表

1．什么是表？SQL Server 为列提供了哪些数据类型？ 答：

表是用来存储数据和操作数据的逻辑结构，关系数据库中的所有数据都表现为表的形式。在创建表之前的重要工作是设计表结构，即确定表的名字、所包含的各个列的列名、数据类型和长度、是否为空值等。

SQL Server 为列提供的数据类型有：字节型、数据型、精确数值型、浮点型、货币型、位型、字符型、文本型、日期时间型等。

2．简要说明空值的概念及其作用？ 答：

创建表时需要确定该列的取值能否为空值。空值意味着没有值，并不是“空格”或数值为0。允许空值表示该列取值目前是不确定的。

3．如果创建表时没有指定NULL或NOT NULL，默认用什么？ 答：

允许列的取值为空或不为空，默认情况为NULL。

4．INSERT 语句的作用是什么？如果在INSERT 语句中列出了5个列名，需提供几个列值？ 答：

T—SQL 中向表中插入记录的语句是 INSERT。如果在INSERT 语句中列出了5个列名，需提供5个列值。 5．UPDATE 语句的作用是什么？在使用UPDATE 语句时，带上 WHERE 子句意味着什么？ 答：

T—SQL 中用于修改表记录的语句是UPDATE。WHERE子句意味着指定被修改的记录应满足的条件。当省略该子句时，表明所有的记录都执行 SET 指定的修改。

6．DELETE 语句的作用是什么？用DELETE 语句能删除表吗？ 答：

T—SQL 中用于删除记录的语句是DELETE。用DELETE 语句不能删除表。

7．ALTER TABLE 的作用是什么？在表中增加列的子句是什么？删除列的子句是什么？用ALTER TABLE 可更新列名吗? 答：

T-SQL 中对表进行修改的语句是ALTER TABLE 。在表中增加列的子句是ADD 子句。删除列的子句是DROP 子句。用ALTER TABLE 不可更新列名。

8．使用企业管理器创建STUMS数据库中的各表。 答：略

9．按照题目要求写出下列SQL命令，并在机器上进行测试。 答：

1.创建STUMS数据库的专业表，并将专业代码设为主键。 CREATE TABLE 专业表

（专业代码 CHAR(4) NOT NULL PRIMARY KEY, 专业名称 CHAR(16), 系部代码 CHAR(2) )

2.在专业表中增加一列“培养方向”，CHAR（20）。 ALTER TABLE 专业表 ADD 培养方向 char(20) GO

3.在专业表中插入一条记录，其数据为：0210、轮机工程、02、船舶制造。 INSERT 专业表（专业代码, 专业名称, 系部代码, 培养方向） VALUES（‘0210’, ‘轮机工程’, ‘02’, ‘船舶制造’） GO

4.修改专业表中的记录，将轮机工程的专业代码改为“0201”、培养方向改为“船舶修理”。 UPDATE 专业表

SET 专业代码=’0201’, 培养方向=’船舶修理’ WHERE 专业名称=’轮机工程’ 5.删除专业表中的全部记录。 DELETE 专业表 6.查看专业表的属性。 EXEC sp_help 专业表 7.删除专业表。 DROP TABLE 专业表

第6课 创建简单的SELECT查询

1．简述SELECT 语句的基本语法格式。 答：

SELECT 语句的基本语法格式： SELECT select_list

[INTO new_table_name] FROM table_source

[WHERE search_conditions]

[GROUP BY? group_by_expression] [HAVING search_conditions]

[ORDER BY order_expression [ASC/DESC]

其中，

SELECT 子句——用于指定查询结果集中的列。

Select_list：为结果集选择的列。用*表示当前表或视图的所有列。 INOT子句——创建新表并将查询结果插入新表中。 new_table_name：为保存查询结果的新表名。 FROM子句——用于指定查询的数据源。

table_source：指定用于查询的表或视图、派生表和联接表等。 WHERE子句——用于指定查询条件。

search_conditions：为条件表达式，可以是关系表达式或逻辑表达式。 GROUP BY 子句——将查询结果按指定的表达式分组。

group_by_expression：是对其执行分组的表达式，group_by_expression也称为分组列。group_by_expression可以是列或引用列的非聚合表达式。

HAVING子句——指定满足条件的组才予以输出。HAVING通常与GROUP BY 子句一起使用。 search_condition：为输出组应满足的条件。 ORDER BY——指定结果集的排列顺序。

order_expression：指定要排序的列。可以将排序列指定为列名或列的别名，也可以指定一个表示该名称或别名在选择列表中所处位置的非负整数。列名和别名可由表名或视图名加以限定。也可指定多个排序列。 ASC：指定递增顺序。从最低值到最高值对指定列中的值进行排序。 DESC：指定递减顺序。从最高值到最低值对指定列中的值进行排序。 2．SQL Server 中提供了哪些常用的进行数据统计的聚合函数？ 答：

函数 COUNT MIN MAX AVG SUM STDEV STDEVP VAR VARP 功能 统计 求最小值 求最大值 求平均值 求总和 求标准偏差 求标准偏差 求标准方差 求标准方差 含义（返回值） 统计满足条件的行数 求某字段值的最小值 求某字段值的最大值 求某数字段值的平均值 求某数字字段值的总和 求所有数值的标准偏差 求所涉及及数值的标准偏差 求所有数值的标准方差 求所涉及数值的标准方差 注：字段值为NULL的数据记录不包括在聚合函数的运算中。 3．在STUMS数据库中，用SQL语句完成下列操作。 答：

1.列出家住南通的学生的姓名和年龄。

SELECT 姓名,年龄 FROM 学生基本信息 WHERE 籍贯=’南通’ 2.查询不在1990年出生的男生的姓名。

SELECT 姓名 FROM学生基本信息 WHERE 性别=’男’ and YEAR(出生日期) NOT IN (‘1990’) 3.列出所有“共产党员”的基本信息。

SELECT * FROM 学生基本信息 WHERE 政治面貌=’共产党员’ 4.统计“西方经济学”课程的平均分。 SELECT AVG(成绩) AS 平均分 FROM 选课

WHERE 课程号 IN (SELECT 课程号 FROM 课程 WHERE 课程名='西方经济学') 5.计算选修了课程号为“0310”的学生人数。

SELECT COUNT(学号) AS 总人数 FROM 选课 WHERE 课程号=’0310’ 6.列出所有女生信息，并按年龄从小到大排序。 SELECT * FROM 学生基本信息 WHERE 性别=’女’ ORDER BY 出生日期 DESC

第7课 查询综合设计

1．什么是连接查询？简述交叉连接查询的连接过程及其语法格式。 答：

一个查询同时涉及到两个或两个以上的表，则称之为连接查询。

交叉连接又称非限制连接，也叫广义迪卡尔积。二个表的广义笛卡尔积是两表中记录的记录乘积，结果集的列为二个表属性列的和，其连接的结果会产生一些没有意义的记录，并且进行该操作非常耗时。其语法格式为： SELECT column_name FROM table_namel CROSS JOIN table_name2 其中，CROSS JOIN 为交叉表连接关键字。

2．简述外连接查询中有哪几种连接及相应的语法格式。 1）左外连接 其语法格式为： SELECT column_name

FROM table_name1 LEFT [OUTER] JOIN table_name2 ON table_namel.column_name=table_name2.column_name 其中OUTER 关键字可以省略。 2）右外连接

其语法格式为： SELECT column_name

FROM table_name1 RIGHT [OUTER] JION table_name2 ON table_name1.column_name=table_name2.column_name 其中OUTER 关键字可以省略。 3）完全外连接 其语法格式为： SELECT? column_name

FROM table_name1 FULL[OUTER] JOIN table_name2 ON table_name1.column_name=table_name2.column_name 其中OUTER 关键字可以省略。

3．什么是子查询？在T-SQL 语言中存在哪几种基本的子查询方式？ 答：

括号内的查询语言作为条件嵌入在外WHERE 子句中，我们将括号内的查询语句称为子查询，与之相对的就是父查询或外层查询，即包含子查询语句。 基本的子查询方式：

1. 带有IN 或NOT IN 的子查询 2. 带有比较运算符的子查询 3. 带有EXISTS 运算符的子查询

4．在“stums”数据库中，用T-SQL语句完成下列操作。

1. 采用等值连接的方法，查出每个教师及其系部的详细情况。 SELECT 教师表.*, 系部表.* FROM 教师表, 系部表 WHERE 教师表.系部代码=系部表.系部代码 2.列出没有选修“西方经济学”课程的学生信息。 SELECT *

FROM 学生基本信息 WHERE 学号 NOT IN

( SELECT 学号 FROM 选课 WHERE 课程号=

( SELECT 课程号 FROM 课程 WHERE 课程名=’西方经济学’) )

3.列出比所有“071071”班的学生年龄都大的学生。 SELECT *

FROM 学生基本信息 WHERE 出生日期>ANY

( SELECT 出生日期 FROM 学生基本信息 WHERE 班号=’071071’) 4.将所有是“共青团员”的学生记录找出并插入新创建表中。

SELECT * INTO 共青团员 FROM 学生基本信息

WHERE 政治面貌='共青团员'

第8课 创建学生信息管理系统中的视图 1.简述视图与基本表的区别与联系。 答：

视图是从一个或多个表(或视图)导出的表，在数据库中是作为一个对象来存储的。

视图是数据库系统提供给用户以多种角度观察数据库中数据的重要机制，有时为与视图区别，将视图称为虚拟表，用于创建视图的表称为基本表。

视图的结果集通常不保存在数据库中，数据库中只存储视图的定义，而不存放构成视图的数据内容，这些数据仍然存放在原来的基本表中，只有在引用视图时根据视图的定义去操作与视图相关联的基本表，动态生成视图所需的数据结构。

2.如何创建和使用视图? 答：

在SQL Server中，可以使用企业管理器或视图向导创建视图，也可以使用T-SQL的CREATE VIEW语句创建视图。 可以通过SELECT语句使用视图查询信息，可以通过INSERT、UPDATE、DELETE语句使用视图更新基本表中的数据。 3.创建视图哪一个选项将机密语句文本? 答：

加密选项为WITH ENCRYPTION，选择此项则在系统表syscommentes中存储CREATE VIEW语句时进行加密。 4.在CREATE VIEW命令中哪个选项将强制所有通过视图更新的数据必须满足SELECT子句中指定的条件? 答：

在CREATE VIEW命令中WITH CHECK OPTION选项将强制所有通过视图更新的数据必须满足SELECT子句中指定的条件。 5.查看视图的定义信息,应使用哪一个系统存储过程? 答：

使用系统存储过程sp_helptext可查看视图的定义信息。 6.重命名视图应使用哪一个系统存储过程? 答：

重命名视图应使用sp_rename系统存储过程。

7.可用什么语句删除视图？创建某视图的基本表被删除了，该视图也一起被删除了？ 答：

可以使用DROP语句删除视图。其语法为：DROP VIEW 视图名称

执行此语句后，指定视图的定义将在数据字典中删除。由该视图基本表创建的视图的定义虽然在数据字典中，但该视图已无法使用。

创建某视图的基本表被删除了，该视图将失效，但一般不会被自动删除。 8.写出T-SQL语句，对STUMS数据库进行如下操作：

1.创建一个名为CJ_BK_VIEW的视图，该视图中包含不及格学生的学号、姓名、课程名，成绩和所在的班级名信息。 答：CREATE VIEW CJ_BK_VIEW AS

SELECT学生基本信息.学号，姓名，课程名，成绩, 班级名称 FROM 学生基本信息，选课，课程，班级

WHERE学生基本信息.学号=选课.学号 AND选课.课程号=课程.课程号AND 学生基本信息.班号=班级.班号AND 成绩<60

2.创建一个名为JS_RK_VIEW的视图，该视图中包含教师编号、姓名、课程名、学时、授课班级和学生数。 答：CREATE VIEW JS_RK_VIEW AS

SELECT教师. 教师编号，姓名，课程名，学时, 班号，学生数 FROM 教师，教师任课，课程，教学计划

WHERE教师.教师编号=教师任课.教师编AND教师任课.课程号=课程.课程号AND. 教师任课.课程号=教学计划.课程号 3.通过CJ_BK_VIEW视图查询补考的学生信息。 答：SELECT * FROM CJ_BK_VIEW

4.通过07_NJ_VIEW视图进行插入、修改和删除，数据由自己拟定。 答：INSERT 07_NJ_VIEW(学号，姓名，籍贯) VALUES(‘071172008’,’薛云’,’南京’) UPDATE 07_NJ_VIEW SET 姓名=’薛玫’ WHERE姓名=’薛云’ DELETE ?07_NJ_VIEW WHERE姓名=’薛玫’

5.使用系统存储过程sp_depends查看07_NJ_VIEW视图的相关性。 答：EXEC sp_depends 07_NJ_VIEW

6.使用ALTER VIEW 语句修改视图，使CJ_BK_VIEW为加密视图。 答：ALTER VIEW CJ_BK_VIEW AS

SELECT学生基本信息.学号，姓名，课程名，成绩, 班级名称 FROM 学生基本信息，选课，课程，班级

WHERE学生基本信息.学号=选课.学号 AND选课.课程号=课程.课程号AND 学生基本信息.班号=班级.班号干部AND 成绩<60

WITH ENCRYPTION

7.将07_NJ_VIEW视图重命名为NEW_VIEW。 答：sp_rename ‘07_NJ_VIEW’,’ NEW_VIEW’ 8.删除CJ_BK_VIEW视图。 答：DROP VIEW CJ_BK_VIEW

第8课 创建学生信息管理系统中的视图 1.简述视图与基本表的区别与联系。 答：

视图是从一个或多个表(或视图)导出的表，在数据库中是作为一个对象来存储的。

视图是数据库系统提供给用户以多种角度观察数据库中数据的重要机制，有时为与视图区别，将视图称为虚拟表，用于创建视图的表称为基本表。

视图的结果集通常不保存在数据库中，数据库中只存储视图的定义，而不存放构成视图的数据内容，这些数据仍然存放在原来的基本表中，只有在引用视图时根据视图的定义去操作与视图相关联的基本表，动态生成视图所需的数据结构。

2.如何创建和使用视图? 答：

在SQL Server中，可以使用企业管理器或视图向导创建视图，也可以使用T-SQL的CREATE VIEW语句创建视图。 可以通过SELECT语句使用视图查询信息，可以通过INSERT、UPDATE、DELETE语句使用视图更新基本表中的数据。 3.创建视图哪一个选项将机密语句文本? 答：

加密选项为WITH ENCRYPTION，选择此项则在系统表syscommentes中存储CREATE VIEW语句时进行加密。 4.在CREATE VIEW命令中哪个选项将强制所有通过视图更新的数据必须满足SELECT子句中指定的条件? 答：

在CREATE VIEW命令中WITH CHECK OPTION选项将强制所有通过视图更新的数据必须满足SELECT子句中指定的条件。 5.查看视图的定义信息,应使用哪一个系统存储过程? 答：

使用系统存储过程sp_helptext可查看视图的定义信息。 6.重命名视图应使用哪一个系统存储过程? 答：

重命名视图应使用sp_rename系统存储过程。

7.可用什么语句删除视图？创建某视图的基本表被删除了，该视图也一起被删除了？ 答：

可以使用DROP语句删除视图。其语法为：DROP VIEW 视图名称

执行此语句后，指定视图的定义将在数据字典中删除。由该视图基本表创建的视图的定义虽然在数据字典中，但该视图已无法使用。

创建某视图的基本表被删除了，该视图将失效，但一般不会被自动删除。 8.写出T-SQL语句，对STUMS数据库进行如下操作：

1.创建一个名为CJ_BK_VIEW的视图，该视图中包含不及格学生的学号、姓名、课程名，成绩和所在的班级名信息。 答：CREATE VIEW CJ_BK_VIEW AS

SELECT学生基本信息.学号，姓名，课程名，成绩, 班级名称 FROM 学生基本信息，选课，课程，班级

WHERE学生基本信息.学号=选课.学号 AND选课.课程号=课程.课程号AND 学生基本信息.班号=班级.班号AND 成绩<60 2.创建一个名为JS_RK_VIEW的视图，该视图中包含教师编号、姓名、课程名、学时、授课班级和学生数。 答：CREATE VIEW JS_RK_VIEW AS

SELECT教师. 教师编号，姓名，课程名，学时, 班号，学生数 FROM 教师，教师任课，课程，教学计划

WHERE教师.教师编号=教师任课.教师编AND教师任课.课程号=课程.课程号AND. 教师任课.课程号=教学计划.课程号 3.通过CJ_BK_VIEW视图查询补考的学生信息。

答：SELECT * FROM CJ_BK_VIEW

4.通过07_NJ_VIEW视图进行插入、修改和删除，数据由自己拟定。 答：INSERT 07_NJ_VIEW(学号，姓名，籍贯) VALUES(‘071172008’,’薛云’,’南京’) UPDATE 07_NJ_VIEW SET 姓名=’薛玫’ WHERE姓名=’薛云’ DELETE ?07_NJ_VIEW WHERE姓名=’薛玫’

5.使用系统存储过程sp_depends查看07_NJ_VIEW视图的相关性。 答：EXEC sp_depends 07_NJ_VIEW

6.使用ALTER VIEW 语句修改视图，使CJ_BK_VIEW为加密视图。 答：ALTER VIEW CJ_BK_VIEW AS

SELECT学生基本信息.学号，姓名，课程名，成绩, 班级名称 FROM 学生基本信息，选课，课程，班级

WHERE学生基本信息.学号=选课.学号 AND选课.课程号=课程.课程号AND 学生基本信息.班号=班级.班号干部AND 成绩<60

WITH ENCRYPTION

7.将07_NJ_VIEW视图重命名为NEW_VIEW。 答：sp_rename ‘07_NJ_VIEW’,’ NEW_VIEW’ 8.删除CJ_BK_VIEW视图。 答：DROP VIEW CJ_BK_VIEW

第9课 为学生信息管理系统创建存储过程 1.什么是存储过程？使用存储过程有哪些特点？ 答：

存储过程是一种数据库对象，通常是把实现某个特定任务的一组预编译的SQL语句创建一个存储过程，以一个存储单元的形式存储在服务器上，供用户反复调用，提高程序的使用效率。

使用存储过程的优点：允许模块化程序设计；允许更快执行；减少网络流量；可作为安全机制使用。 2.试说明存储过程分类的特点。 答：

1）系统存储过程。存储在master数据库中，并以sp_为前缀，许多管理和信息活动可以通过系统存储过程执行。 2）本地存储过程。是用户自行创建的并存储在用户数据库中的存储过程。这类存储过程能根据用户的实际需要完成某以特定的功能。

3）临时存储过程。临时存储过程分为本地临时存储过程和全局临时存储过程。在创建存储过程时。如果过程名的第一

个字符取“﹟”，那么创建的就是本地临时存储过程；如果过程名的第一.第二字符都取“﹟”，那么创建的就是全局临时存储过程。临时存储过程存储在tempbd内，它们在连接到SQL Server 以前的版本时很有用。 4）远程存储过程。指非本地服务器上的存储过程，只有在分布式查询中使用此存储过程。

5）扩展存储过程。扩展存储过程是用户使用外部程序语言编写的存储过程。使用时需要先加载到SQL Server系统中，且只能存储在master数据库中，其执行与一般的存储过程完全相同。引入扩展存储过程主要是弥补SQL Server的不足之处，可以按需要大幅扩展其功能。

3.请分别写出用企业管理器和T_SQL语句命令创建存储过程的主要步骤。 答：使用企业管理器

1. 运行企业管理器，展开数据库STUMS，在“存储过程”图标上右击，在弹出的快捷菜单中选择“新建存储过程”

命令。

2. 在该窗口中首先输入所有者和存储过程名。

3. 输入实现存储过程功能的语句，单击“检查语法”按钮，进行语法检查。 4. 如果没有任何错误，单击“确定”按钮，将存储过程保存到STUMS数据库中。

5. 双击STUMS数据库中的“存储过程”，用户在右窗格显示的存储过程列表中，就可看到刚创建的存储过程。 使用T_SQL语句： 1）在查询分析器中输入： CREATE PROC procedure_nam AS sql_statement[?n]

其中，procedure_nam是要创建的存储过程，过程名称必须符合标识符规则，且对于数据库及其所有者必须惟一。sql_statement：是定义存储过程所要完全操作的任意数目和类型的T-SQL语句。 2）进行语法检查，正确无误后，单击执行按钮即可。

4.创建存储过程哪一个选项将加密语句文本？哪一个选项可设置输入参数？ 答：创建存储过程的WITH ENCRYPTION选项将加密语句文本。 @ parameter_name date_type[=default] 选项可设置输入参数。 5.执行含有参数的存储过程应注意什么？ 答：

当存储过程含有多个输入参数时，传递值的顺序必须与存储过程中定义的输入参数的顺序相一致。 使用带有通配符参数的存储过程，可以实现模糊查询。

6.查看存储过程的定义信息，应使用哪一个系统存储过程？查看存储过程的相关性信息，应使用哪一个系统存储过程应注意什么？ 答：

可使用sp_helptext可查看存储过程的定义信息；使用sp_depends可查看存储过程的相关性。 7.可用什么语句修改存储过程？可用什么语句删除存储过程？ 答：

修改存储过程可以使用ALTER PROCEDURE语句；使用DROP PROCEDURE 语句删除存储过程。 8.在SQL 查询分析器的对象浏览器窗口中可以修改存储过程吗？举例说明操作过程。 答：

在SQL 查询分析器的对象浏览器窗口中可以修改存储过程。

1）打开SQL查询分析器，展开数据库STUMS，展开“存储过程”项目；

2）右击需修改的存储过程如xs_cj-proc，在弹出的快捷菜单中选择“编辑”子菜单，打开编辑器窗口； 3）窗口中呈现ALTER PROCEDURE 命令和待修改的粗出过程源代码，用户可对其进行修改；

1.修改完毕后，单击菜单“查询→执行”完成修改。 9.写出T-SQL语句，对STUMS数据库进行如下操作：

2.创建一个名为xs_bk_proc的存储过程，完成不及格学生的学号、姓名、课程名，成绩和班号信息的查询。 答：CREATE PROC xs_bk_proc AS

SELECT学生基本信息.学号，姓名，课程名，成绩, 班号 FROM 学生基本信息，选课，课程

WHERE学生基本信息.学号=选课.学号 AND选课.课程号=课程.课程号AND 成绩<60

3.在STUMS数据库中，基于班级表创建一个名为BJ_INFO_PROC的存储过程，根据班号查询班主任、班长和教室位置信息。

答：CREATE PROC BJ_INFO_PROC @ BH CHAR(6) AS

SELECT班主任，班长，教室 FROM 学生基本信息，选课，课程 WHERE班号=@BH

4.创建一个名为xs_tj_proc的存储过程，实现按性别统计学生数。 答：CREATE PROC xs_tj_proc @ XB CHAR(2) AS

SELECT COUNT(*) 学生数 FROM 学生基本信息 WHERE 性别=@XB GROUP BY 性别

5.调用上述xs_tj_proc存储过程，统计女生人数。 答：EXEC xs_tj_proc ‘女’

6.创建一个名为xk_ins_proc的存储过程，用于向选课表插入记录。 答：CREATE PROC xk_ins_proc @ XH CHAR(9)， @ KCH CHAR(4)， @ CJ smallint， @ BKCJ smallint， @ XF tinyint AS

INSERT 选课 VALUES（@ XH CHAR(9)，@ KCH CHAR(4)，@ CJ smallint，@ BKCJ smallint，@ XF tinyint）

7.创建一个名为xk_cj_proc的存储过程，根据课程号更新选课表中的对应成绩，令成绩等于0。 答：CREATE PROC xk_cj_proc @ KCH CHAR(4) AS

UPDATE 选课 SET 成绩=0 WHERE 课程号=@ KCH

8.使用系统存储过程查看xk_cj_proc的定义信息、一般信息和相关性信息。 答：EXEC sp_helptext xk_cj_proc EXEC sp_help xk_cj_proc EXEC sp_depends xk_cj_proc

9.使用ALTER PROCEDURE 命令修改xs_tj_proc存储过程，实现按系部统计学生数。 答：ALTER PROCEDURE xs_tj_proc @ XBDM CHAR(2) AS

SELECT COUNT(*) 学生数 FROM 学生基本信息 WHERE 系部代码=@XBDM GROUP BY 系部代码

10.将存储过程xs_tj_proc重命名为xs_xibu_proc。 答：sp_rename ‘xs_tj_proc’,’xs_xibu_proc’ 11.删除xk_ins_proc、xk_cj_proc存储过程。 答：DROP PROC xk_ins_proc,xk_cj_proc

第10课 为学生信息管理系统创建触发器 1.什么是触发器？使用触发器有哪些优点？ 答：

触发器由T-SQL语句组成，与表紧密相连，用于维护表中数据的正确性和多表之间数据的一致性。

优点：触发器是自动执行的；触发器能够对数据库中的相关表实现级联更改；触发器可以强制限制；触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。 2.试说明触发器的类型和特点。 答：

按触发器的触发操作分，可将触发器分为三种类型：INSERT、UPLATE和DELETE触发器。 按触发器被激发的时机分，可将触发器分为两种类型：AFTER触发器和INSTEAD OF 触发器。

1）AFTER触发器。该类触发器是在触发操作（INSERT，UPDATE或DELETE）后和处理完任何约束后激发。 2）INSTEAD OF触发器。该类触发器代替触发动作进行激发，并在处理约束之前激发。

3.请写出用企业管理器创建触发器的主要步骤。 答：

1）运行企业管理器，展开数据库，单击“表”文件夹，此时在右窗格中显示该数据库的所有表。

2）在右窗格中右击要创建触发器的数据表，在弹出的快捷菜单中执行“所有任务→管理触发器”菜单，打开“触发器属性”对话框。

3）在该对话框的“名称”下拉框中选择“新建”，在“文本”框中输入触发器的文本。 4）单击“检查语法”按钮，检查语句是否正确。

5）如果没有任何错误，单击“应用”按钮，在“名称”下拉框中，就会有新创建的触发器名称。 6）单击“确定”按钮，关闭窗口创建成功。 4. inserted 和deleted 表有何作用？ 答：

Inserted 和deleted表主要用于触发器，扩展表间引用完整性。

Inserted表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted 表和触发器表中。

deleted 表用于存储 DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。

5．存储过程和触发器的主要区别是什么？ 答：

存储过程和触发器的主要区别：触发器在数据库上执行并附在对应的基本表上，当表中的数据发生变化时自动生效，用户不能像执行存储过程那样，通过使用触发器的名称来调用或执行它，触发器也不能传递或接收参数。

6．查看触发器的定义信息，应使用哪一个系统存储过程？查看数据表上拥有的触发器类型，应使用哪一个系统存储过程？ 答：

查看触发器的定义信息，应使用sp_helptext系统存储过程；查看数据表上的触发器类型，应使用sp_helptrigger系统存储过程。

7．可使用什么语句修改触发器？可用什么语句禁止或启用触发器？ 答：

可使用ALTER TRIGGER 语句来修改触发器，可以使用ALTER TABLE 的DISABLE TRIGGER选项可禁用触发器，再使用ENABLE TRIGGER重新启用触发器.

8．如果触发器运行“ROLLBACK TRANSACTION”命令后，引起触发器触发的操作命令是否还有效？ 答：无效。

9.写出T-SQL语句，对STUMS数据库进行如下操作：

1.在专业表上创建一个名为zy_all_trigger触发器，使得用户执行删除、插入、修改操作时，该触发器被触发，自动给出报警信息“不能更改此表数据！”，并撤销此次操作。 答：CREATE TRIGGER zy_all_trigger ON 专业 FOR INSERT，UPDATE，DELETE AS BEGIN

PRINT（’ 不能更改此表数据！’） ROLLBACK TRANSACTION END

2.在系部表上创建一个名称为xbjs_delete_trigger触发器，当删除系部表中的记录时，如果教师表中引用了此记录的系部代码，则提示“用户不能删除！”，否则提示“记录已删除！”。 答： CREATE TRIGGER xbjs-delete_trigger ON 系部 FOR DELETE AS

IF（SELECT COUNT (*) FROM 教师 INNER JION DELETED ON教师.系部代码=DELETED.系部代码）>0 BEGIN

PRINT (‘用户不能删除’) ROLLBACK TRANSACTION END ELSE

PRINT (‘记录已删除!’) GO

3.在选课表上创建一个名称为xkkc_insert_trigger触发器，当向选课表中插入记录时，检查该记录的课程号在课程表中是否存在，如果不存在，则不允许插入。 答：CREATE TRIGGER xkkc_insert_trigger ON 选课 FOR INSERT AS

DECLARE @KCH CHAR(4) SELECT @KCH=课程.课程号 FORM 课程, inserted

WHERE 课程.课程号=insert.课程号 IF @KCH <>’’

PRINT (‘记录插入成功’) ELSE BEGIN

PRINT (‘课程号不存在，不能插入记录，插入将终止!’) ROLLBACK TRANSACTION END

4.在学生基本信息表上创建一个名称为xsxk_updare_trigger触发器，当修改学生基本信息表中的学号时，如果选课表中引用了该学号，则作同样的修改，并提示“记录已修改！”。 答：CREATE TRIGGER xsxk_update_trigger ON 学生基本信息 FOR UPDATE AS

IF UPDATE(学号) BEGIN

DECLARE @XH1 CHAR(9),@XH2 CHAR(9)

SELECT @XH1=DELETED.学号,@XH2 =INSERTED.学号 FROM DELETED,INSERTED UPDATE 选课 SET 学号=@XH2 WHERE 学号=@XH1

PRINT('记录已修改!') END

第11课 为学生信息管理系统创建索引 1.什么是索引？索引的作用是什么？ 答：

索引文件就是按照一定顺序对表中一列若干列建立的列值与记录行之间的对应关系表。它是一种特殊类型的数据库对象，保存着表中排序的索引列，并且记录了索引在数据表中的物理存储位置，实现了表中数据的逻辑排序。 索引的作用：提高查询信息的速度；保证数据记录的唯一性；实现表与表之间的参照完整性；在使用 ORDER BY 、GROUP BY子句进行数据检索时，利用索引可减少排序和分组的时间。 3.索引可以分为哪几类，每一类的特征是什么？ 答：

索引可以分为三类，每一类特征如下：

1）聚集索引。每个表只能创建一个聚集索引，一般创建在表中经常搜索的列或者按顺利访问的列上，当为一个表的某列创建聚集索引时，表中的数据会按该列进行重新排序，然后再存贮到磁盘上，创建一个聚集索引所需的磁盘空间至少是实际数量的120%。

2）非聚集索引。数据存贮在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一顺序存储。一个表最多可以创建249个非聚集索引。）

3）唯一索引。只要列中的数据是唯一的，就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引，只有当唯一性是数据本身的特征时，指定唯一索引才有意义。 4．简述用企业管理器创建索引的步骤。 答：

运行企业管理器，展开数据库，单击“表”文件夹，在右窗格中右击需要创建索引的表，在弹出的快捷菜单中，选择“所有任务→管理索引”，打开“管理索引”对话框。单击“新建”按钮，打开“新建索引”对话框，输入“索引名称”，从字段列表中选择用以创建索引的字段，并按要求设定索引选项（勾选相应选项前的复选框），单击“确定”按钮，关闭“新建索引”对话框。

5.什么情况下需要重建索引？用什么方法重建索引？ 答：

当对数据库表中的数据进行插入、修改或删除操作时，就会影响到已创建的索引，此时必须重建该表拥有的全部索引。 重建索引可以使用DBCC DBREINDEX语句，在查询分析器中输入并运行DBCC DBREINDEX命令即可。 6.用sp_rename系统存储过程重命名索引时，在语法中给出原索引名应是什么形式? 答：

在语法中的原索引名应是table.index形式的。

7.系统存储过程sp_helpindex或sp_help都可以用来查看数据表的索引信息，它们有何区别? 答：

sp_helpindex显示表的信息，而sp_help除了显示索引信息外，还有表的定义、约束等其他信息。 8.写出T-SQL语句，对STUMS数据库进行如下操作：

1.在STUMS数据库的班级表上按班号创建一个名为bj_bh_index惟一聚集索引。 答：USE STUMS GO

CREATE UNIQUE CLUSTERED INDEX bj_bh_index ON 班级(班号)

2.在STUMS数据库的班级表上按班级名称创建一个名为bj_bjmc_index的惟一非聚集索引。 答：USE STUMS GO

CREATE UNIQUE NONCLUSTERED INDEX bj_bjmc_index ON 班级(班级名称) GO

3.在STUMS数据库的班级表中，按bj_bjmc_index索引指定的顺序，查询班级信息。 答：USE STUMS GO

SELECT * FROM 班级 WITH (INDEX_(bj_bjmc_index)) GO

4.在STUMS数据库的选课表上按学号+课程号建立惟一非聚集索引xk_xhkch，其填充因子和PAD_INDEX的值均为60。 答：USE STUMS GO

CREATE? NONCLUSTERED INDEX xk_ xhkch ON 选课(学号,课程号) WITH PAD_INDEX,FILLFACTOR=60, DROP_EXISTING GO

5.重新命名索引xk_xhkch为xk_xhkch_index。

答：EXEC sp_rename '选课.xk_xhkch','xk_xhkch_index' 6.使用填充因子值70，重建班级表上所有索引。 答：DBCC DBREINDEX (班级,'',70) 7.查看班级表上的索引信息。 答：EXEC sp_helpindex '班级'

8.删除班级表上的所有索引。

答：DROP INDEX 班级. bj_bh_index，班级. bj_bjmc_index

第12课 学生信息管理系统数据完整性实现 1.什么是数据的完整性？数据的完整性分为哪几类？ 答：

数据完整性是指存储在数据库中的数据的一致性和准确性。

数据的完整性可分为四种类型：实体完整性、域完整性、参照完整性和用户定义的完整性。 2.什么是约束？请分别说明各种不同类型约束的含义。

答：

约束是一种强制数据完整性的标准机制，使用约束可以确保在字段中输入有效数据并维护各表之间的关系。SQL Server支持下列五类约束：

主键约束（PRIMARY KEY）。确保在特定的列中不会输入重复的值，并且在这些列中也不允许输入NULL值。可以使用主键约束强制完整性。

唯一性约束（UNIQUE）。不允许数据库表在指定列上具有相同的值，但允许有空值，确保在非主键列中不输入重复值。 检查约束（CHECK）。通过条件表达式判断限制插入到列中的值，以强制执行域的完整性。

默认值约束（DEFAULT）。当数据库表中插入数据时，如果没有明确的提供输入值时，SQL Server自动为该列输入默认值。

外键约束（FOREIGN KEY）。定义数据库表中指定列上插入或更新的数值，必须在另一张被参照表中的特定列上存在，约束表与表之间的关系，强制参照完整性。

3.如何创建和删除各种类型的约束，请写出其SQL语句的格式。 答： 创建主键约束

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY(column) 创建唯一性约束 ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (column) 创建检查约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK logical_expression? 创建默认值约束

ALTER TABLE table_name

ADD CONSTRAINT constraint_name?

DEFAULT constraint_expression FOR column_name 创建外键约束

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

FOREIGNKEY column_name REFERENCES ref_table(ref_column_name) 删除约束

ALTER TABLE table_name

DROP CONSTRAINT constraint_name[,?n] 4.简述默认和规则的概念与应用。 答：

默认与在约束中介绍的DEFAULT默认约束的作用一样，也可以实现当用户在向数据库表中插入一行数据时，如果没有明确的给出某列的输入值时，则由SQL Server自动为该列输入默认值。但与DEFAULT 默认约束不同的是，默认是一种数据库对象，在数据库中只需定义一次后，就可以被一次或多次应用于任意表中的一列或多列，还可以用于用户定义的数据类型。

规则用来定义表中某列可以输入的有效值范围，当用户输入的数据不在规定的范围内，就会提醒用户输入有误，从而确保输入数据的正确性。规则与CHECK约束的作用是相同的。但与CHECK约束不同的是，规则是一种数据库对象，在数据库中只需定义一次后，就可以被一次或多次绑定到任意表中某列，限制列值。规则的使用方法类似默认，同样包

括创创建、绑定、解绑和删除。

5.写出T-SQL语句，对STUMS数据库进行如下操作：

1.在STUMS数据库中，为学生基本信息表的“系部代码”创建外键约束fk_xs_xb_xbdm；为“入学时间”创建检查约束ck_xs_rxsj,确保入学时间大于出生日期，小于计算机系统的日期。 答：

ALTER TABLE 学生基本信息 ADD CONSTRAINT fk_xs_xb_xbdm

FOREIGN KEY(系部代码)REFERENCES 系部(系部代码) GO

ALTER TABLE 学生基本信息 ADD CONSTRAINT ck_xs_rxsj

CHECK(入学时间>出生日期 AND入学时间

2.在STUMS数据库中创建zzmm_default 默认，将其分别绑定到学生基本信息表和教师表的“政治面貌”字段上，政治面貌的默认值为共产党员。 答：

CREATE DEFAULT zzmm_default AS '共产党员' GO

EXEC sp_bindefault 'zzmm_default','教师.政治面貌' EXEC sp_bindefault 'zzmm_default','学生基本信息.政治面貌' GO

3.在STUMS数据库中创建xbdm_rule规则，将其绑定到系部表的系部代码字段上，用来保证输入的系部代码只能是数字字符。 答：

CREATE RULE xbdm_rule AS @xbdm LIKE? '[0-9][0-9]' GO

EXEC sp_bindrule 'xbdm_rule', '系部.系部代码' GO

4.查看zzmm_default 默认和xbdm_rule规则的定义信息。 答： EXEC sp_helptext zzmm_defaul EXEC sp_helptext xbdm_rule

第13课 学生信息管理系统的安全管理

1. 登录SQL Server服务器的两种验证方法有何区别? 如何实现两种登录方式的切换？

答：

两种验证方法的区别如下：

（1）Windows 身份验证：当SQL Server 2000配置成与Windows NT安全性集成时，它就可以利用Windows NT的安全性功能。Windows NT可以授权登录SQL Server，Windows NT用户账户可以映射到SQL Server的登录账户。这使用户不用直接提供单独的登录账户和口令就可以连接和登录SQL Server。用户只要通过了Windows NT的认证，就可以访问SQL Server。

（2）SQL Server身份验证：SQL Server本身也提供了用户登录的安全机制，在这种认证模式下，用户在连接SQL Server时必须提供登录名和登录密码，由SQL Server本身来执行认证处理，与Windows NT的登录账户无关。 两种登录方式的切换步骤如下： （1）打开企业管理器；

（2）在窗口中展开一个服务器组，然后选择该服务组中希望设置身份验证模式的服务器；

（3）在该服务器上单击鼠标右键，在弹出的菜单中选择命令“属性”，打开“SQL Server属性”对话框； （4）该对话框中选择“安全性”选项卡；

（5）在“身份验证”区域中选择要设置的身份验证模式。

① SQL Server和Windows[S]：指定用户可以使用SQL Server身份验证和Windows身份验证，这种验证为混合模式身份验证。

② 仅Windows[W]：指定用户只能通过Windows身份验证连接到SQL Server。 2. 什么是角色？服务器角色和数据库角色的区别是什么？ 答：

在SQL Server中，将一些用户集中到一个单元中并对该单元统一设定权限，这样的单元称为角色（role）。 服务器角色主要用于用户登录时授予的在服务器范围内的安全特权。在创建登录账户时，可以对服务器角色进行设置。 数据库角色是用来为某一用户或某一组用户，授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以使一个用户具有属于同一数据库的多个角色。 3. 结合学生信息管理系统数据库STUMS，在企业管理器窗口中，完成下列各题： ① 创建登录用户user1、user2和super，并创建对应的数据库用户。 答：操作步骤参见13.2和13.3节。

② 给用户user1和user2以及super 授予创建表的权限。 答：GRANT CREATE TABLE TO USER1,USER2,SUPER

③ 给public角色授予DELETE权限（SELECT、DELETE、UPDATE）并将特定的权限授予用户user1、user2和user3，使这些用户对学生基本信息表具有对应权限。 答：GRANT DELETE ON 学生基本信息 TO PUBLIC GRANT DELETE ON 学生基本信息 TO USER1,USER2,SUPER ④ 禁止用户user1、user2不能使用CREATE TABLE语句。 答：DENY CREATE TABLE TO USER1,USER2

⑤ 禁止SUPER用户对教师表的SELECT，INSERT，UPDATE，DELETE的权限。 答：DENY SELECT,INSERT,UPDATE,DELETE ON 教师 TO SUPER ⑥ 撤销用户user1的CREATE TABLE语句权限。 答：REVOKE CREATE TABLE TO USER1 ⑦ 列出用户user1的权限。 答：

操作步骤：在企业管理器中，展开STUMS数据库，在其下属对象中单击“用户”后，在右边的窗格中会出现当前已经存在的用户。用鼠标右键单击要user1数据库用户，在弹出的菜单中选择“所有任务→管理权限”命令，打开“数据库用户属性-STUMS”对话框，在对话框中选择“仅列出此用户具有权限的对象”单选钮，即可查看用户user1的权限。

第14课 事务与锁机制 1.什么是事务？事务有何特性？ 答：

SQL Server中的一个事务（Transaction）是由一系列的数据库查询操作和更新操作构成的，把这一系列操作作为单个逻辑工作单元执行。

事务具有4个特性，即原子性、一致性、隔离性和持久性。

（1）原子性：一个事务中的所有操作是一个逻辑上不可分割的单位。 （2）一致性：事务在完成时，必须使所有的数据都保持一致状态。 （3）隔离性：一个事务的执行不能被另一个事务干扰。

（4）持久性：指一个事务一旦提交，则它对数据库中数据的改变就应该是永久的。 2.SQL Server采用哪些机制保证事务物理的完整性？ 答：

SQL Server提供以下机制，保证每个事务物理的完整性。 1）锁机制。锁定设备，使事务相互隔离。

2）事务日志。即使服务器硬件、操作系统或 SQL Server 自身出现故障，SQL Server 也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的位置。

3）事务管理。强制保持事务的原子性和一致性。事务启动之后，就必须成功完成，否则 SQL Server 将撤消该事务启动之后对数据所作的所有修改。

3.SQL Server的事务模式有几种？每一种模式有何特点？ 答：

SQL Server的事务模式可以分为显式事务、隐性事务与自动提交事务3种模式。

显式事务的特点：显式事务可以显式地在其中定义事务的启动和结束。显式事务也称为用户定义或用户指定的事务。定义显式事务的语句有BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION 或 ROLLBACK WORK。

隐性事务模式的特点：当连接以隐性事务模式进行操作时， SQL Server将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始，只需提交或回滚每个事务。隐性事务模式生成连续的事务链。

自动提交事务模式的特点：这是SQL Server 默认的事务管理模式，每条单独的语句都是一个事务。也就是说，每个Transact-SQL语句结束时，事务被自动提交，若遇到错误就会回滚。只要自动提交模式没有被显式或隐性事务替代，SQL Server 连接就以该默认模式进行操作。 4.事务的提交和撤销有何意义？ 答：

使用COMMIT语句可以提交事务。如果没有遇到错误，可使用该语句成功地结束事务。事务一旦提交，该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。

使用ROLLBACK语句可以撤销事务。用来清除遇到错误的事务。一旦撤销事务，该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。

5.不当的并发控制将会引起何种数据不一致性？请举例说明。

答：

不当的并发控制将会引起以下四种数据不一致性： 1．丢失更新

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将重写由其他事务所做的更新，这将导致数据丢失。

例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。 2．脏读

未确认的相关性也称脏读，当第二个事务选择其他事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。

例如，一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该副本包含目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。 3．不可重复读

当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其他事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其他事务更改，因而该行被非重复读取。

例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。 4．幻象读

当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻象读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或生续读中，因为该行已被其他事务删除。同样，由于其他事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。

例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主副本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。 6.什么是共享锁？什么是排它锁？ 答：

共享（S）锁允许并发事务读取（SELECT）一个资源。资源上存在共享（S）锁时，任何其他事务都不能修改数据。 排他（X）锁又称互斥锁，可以防止并发事务对资源进行访问。其他事务不能读取或修改排他（X）锁锁定的数据。 7.什么是死锁？如何解除死锁？ 答：

死锁是一种可能发生在任何多线程系统中的状态。当某组资源的两个或多个线程之间有循环相关性时，将发生死锁。 在 SQL Server 2000 中，由一个称为锁监视器线程的单独的线程执行死锁检测。它识别线程正在等待的资源。然后，锁监视器查找特定资源的拥有者，并递归地继续执行对那些线程的死锁搜索，直到找到一个循环。用这种方式识别的循环形成一个死锁。

在识别死锁后，SQL Server 通过自动选择可以打破死锁的线程（死锁牺牲品）来结束死锁。 8.锁的状态有几种？分别起什么作用？ 答：

SQL Server使用六种不同的锁模式来锁定资源。

1. 共享锁

共享（S）锁允许并发事务读取（SELECT）一个资源。 2．更新锁

更新（U）锁可以防止通常形式的死锁。 3．排他锁

排他（X）锁又称互斥锁，可以防止并发事务对资源进行访问。 4．意向锁

意向锁表示SQL Server需要在层次结构中的某些底层资源上获取共享（S）锁或排他（X）锁。 5．架构锁

用于保护数据库的模式，又称模式锁。 6．大容量更新锁

当将数据大容量复制到表，且指定了TABLOCK提示或者使用sp_tableoption设置了table lock on bulk表选项时，将使用大容量更新（BU）锁。

9.按照题目要求在查询分析器中输入SQL命令，并进行调试：

1)定义一个事务向选课表输入新的数据记录，如果所输入的学号在学生基本信息表中没有，则回滚撤销，否则提交完成。 答： 代码如下： BEGIN TRANSACTION INSERT 选课

VALUES(‘071071001’,’0307’,’80’,’’,’3’)

IF EXISTS(SELECT * FROM 学生基本信息 WHERE 学号=‘071071001’) COMMIT TRANSACTION ELSE

ROLLBACK TRANSACTION

2)修改选课表中的数据，将课程号为“0310”的成绩乘以1.3，为避免脏读，请为选课表加排它锁，直到事务结束。 提示：在更新语句中，加锁的短语为：WITH (锁的类型)。 答：

UPDATE 选课 WITH(TABLOCK HOLDLOCK) SET 成绩=成绩*1.3 WHERE 课程号='0310'

3)使用sp_lock显示SQL Server中当前所有锁的信息。 答：

USE master; GO

EXEC sp_lock; GO

第15课 学生信息管理系统数据的备份与还原 1．在什么样的情况下需要进行数据库的备份和还原？ 答：

在使用过程中，难免会出现各种形式的故障，如硬件故障、软件错误、病毒、误操作或恶意的破坏等，而这些故障会造成系统运行的异常中断，甚至会破坏数据库，使数据库中的数据部分或全部丢失。为了保证在各种故障发生后，数据库中的数据可以从错误状态还原到某一正确的状态，数据库系统应具有数据库备份和还原功能。应根据实际需要对进行数据库的备份和还原。

2．需要对SQL Sserver的系统数据库作备份吗？ 答：

系统数据库记录了重要的系统信息，它们是确保SQLServer系统正常运行的重要依据。如master数据库记录 SQL Server 系统的所有系统级别信息，记录所有的登录账户和系统配置设置。model 数据库则提供了创建用户数据库的模板信息。msdb 数据库记录了有关SQL Server的Agent服务的全部信息。因此，这些系统数据库要做备份。 3．SQL Server提供了哪些数据备份的类型？这些备份类型适合于什么样的数据库？ 答：

SQL Server提供以下四种类型的数据库备份方式。 1）完全备份

完全备份是将数据库中的所有数据文件全部复制。将所有的用户数据、数据库对象和事务日志复制在一个文件里。当系统出现故障时，可以恢复到最近一次数据库完全备份时的状态。 2) 差异备份

差异备份仅复制自上一次完全数据库备份之后发生更改的数据。差异备份比完全备份工作量小而且备份速度快。因此，对于经常修改的数据库，采用差异备份策略，可以减少备份和还原的时间。 3) 事务日志备份

事务日志备份是指对数据库发生的事务进行备份。包括从上次进行事务日志备份、差异备份和数据库完全备份之后，所有已经完成的事务。使用事务日志备份可将数据库恢复到特定的即时点（如输入多余数据前的那一点）或恢复到故障点。

4) 文件或文件组备份

文件或文件组备份是指对数据库文件或数据库文件组进行备份。这是一种相对较完善的备份。当可用的备份时间不足以支持完全数据库备份时，则可以使用文件或文件组备份模式，在不同的时间备份数据库的子集。 4．什么是备份设备？如何创建这些备份设备？ 答：

备份设备是指用来存储备份内容的存储介质，可以是磁盘、磁带或命名管道。 创建备份设备有以下两种方法： 1）使用企业管理器创建备份设备

下面以为STUMS数据库在D盘的根目录下创建STU_BF备份设备为例，说明使用企业管理器创建备份设备的操作过程。 （1）展开服务器组，然后展开服务器；

（2）展开“管理”文件夹，右击“备份”图标，在弹出的快捷菜单中选择“新建备份设备”命令，打开备份设备属性对话框；

（3）在“名称”栏中输入备份设备的名称（STU_BF）；

（4）选中“文件名”单选钮，单击浏览（“...”）按钮，选择备份设备的存储位置（D:\\），并输入文件名(STUMS.BAK)，定义完毕，如图15-3-1所示；

（5）单击“确定”按钮，创建备份设备。 2）使用系统存储过程创建备份设备

可以在查询分析器中使用sp_addumpdevice系统存储过程创建备份设备。其基本语法如下：

sp_addumpdevice [ @devtype = ] 'device_type' , [ @logicalname = ] 'logical_name' , @physicalname = ] 'physical_name' 其中，

[@devtype =] 'device_type'：备份设备的类型，device_type 的数据类型为 varchar(20)，没有默认设置，可以是disk（硬盘）、pipe（命名管道）、tape（磁带）三者之一。

[@logicalname =] 'logical_name'：备份设备的逻辑名称，该逻辑名称用于 BACKUP 和 RESTORE 语句中。 [@physicalname =] 'physical_name'：备份设备的物理名称。物理名称必须遵照操作系统文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。 5．还原数据库的意思是什么？ 答：

数据库还原是指将数据库的备份加载到服务器中的过程，把数据库从错误状态还原到某一正确状态。 6．当还原数据库时，用户可以使用这些正在还原的数据库吗？ 答：

当还原数据库时，用户不可以使用这些正在还原的数据库。 7．SQL Sserver中数据库三种还原方法有什么区别？ 答： 1）简单还原

简单还原就是指在进行数据库还原时，仅使用了数据库备份或差异备份，而不涉及事务日志备份，只能将数据库恢复到上次备份的即时点。 2）完全还原

完全还原是指通过使用数据库完全备份、差异备份、文件组备份和事务日志备份，将数据库还原到发生失败的时刻，因此几乎不造成任何数据丢失。?????? 3）大容量日志记录还原

大容量日志记录还原在性能上要优于简单还原和完全还原模式。大容量日志记录还原模式可以使用数据库完全备份、差异备份、文件组备份和事务日志备份，提供对数据库的完全防范，并对某些大规模或大容量复制操作提供最佳性能和最少的日志空间。 第16课 程序设计基础 1.何为批处理？简述其作用。 答：

批处理是包含一个或多个 Transact-SQL 语句的组，从应用程序一次性地发送到SQL Server 执行。SQL Server 将批处理语句编译成一个可执行单元。建立批处理时，用 GO 命令来标识批处理的结束。 2.试述变量的分类。 答：

SQL Server 2000中的变量分为局部变量和全局变量两种，其中全局变量的名称以两个@@字符开始，由系统定义和维护；局部变量名称以一个@字符开始，由用户自己定义和赋值。 3.局部变量是如何定义和赋值的。 答：

在使用一个局部变量之前，必须先用DECLARE语句声明这个变量。DECLARE语句的语法格式为： DLCLARE @变量名 变量类型[，@变量名 变量类型?] 变量名必须以 @ 开头，局部变量名必须符合标识符规则。

第一次声明变量时，将此变量的值设为 NULL。若要为变量赋值，可使用 SET 语句。也可以通过 SELECT 语句的选择

列表中当前所引用值为变量赋值。语法如下： SET @变量名=变量值 SELECT @变量名=变量值

4.试述SQL Server中提供的主要流程控制语句及其功能。 答：

1.BEGIN?END

BEGIN?END用来定义一个语句块，位于BEGIN?END之间的SQL语句都属于这个语句块，可视作一个单元执行。 2.IF?ELSE

在SQL Server中，为了控制程序的执行方向，引进了IF?ELSE条件判断结构。 3．CASE结构

CASE结构提供了较一般IF?ELSE结构更多的条件选择，且判断功能更方便、更清晰明了。CASE结构用于多条件分支选择，可完成计算多个条件并为每个条件返回单个值。 4．WHILE语句

WHILE语句通过逻辑表达式设置重复执行 SQL 语句或语句块的循环条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。 5.简述WAITFOR语句的作用。 答：

WAITFOR语句指定触发语句块、存储过程或事务执行的时间、时间间隔或事件。 6.编写程序求2~500之间的所有素数。 答：

declare @i int,@flag int ,@s int set @s=2 while @s<500 begin set @i=2 set @flag=0 while @i<@s/2 begin

if @s%@i=0 set @flag=1 set @i=@i+1 end

if @flag=0 print @s set @s=@s+1 end