数据库原理实验指导书.docx
《数据库原理实验指导书.docx》由会员分享，可在线阅读，更多相关《数据库原理实验指导书.docx（20页珍藏版）》请在冰豆网上搜索。
数据库原理实验指导书
数据库原理实验指导书
实验一数据库模式设计及建立
实验目的：
掌握数据库模式设计，依据实际要求设计表结构，建立表的关系;
比较SQL命令方式和可视化环境管理器方式设计的异同点。
实验类型：
设计验证
实验内容：
a）用sql查询分析器设计商场[货物存货表A];[货物进货表A];[货物消售表A]
b）用企业管理器设计商场[货物存货表B];[货物进货表B];[货物消售表B]
操作步骤：
1）熟悉SQLSERVER的企业管理器和事件探查器
2）配置ODBC
3）创建个人数据库
4）编写[货物存货表A];[货物进货表A];[货物销售表A]
5）建立新表的SQL语句；注意约束条件及表间关系
6）启动“查询分析器”。
7）连接SQLSERVER;选择数据库
8）在“查询分析器”中输入已编写好的SQL命令
9）执行SQL命令
10）启动企业管理器
11）在控制台中选择先前建立的数据库
12）建立[货物存货表A];[货物进货表A];[货物消售表A]
13）设定表关系
实验2数据库的简单查询和连接查询实验
一．实验目的
本实验的目的是使学生掌握SQLServer查询分析器的使用方法，加深对SQL和SQL语言的查询语句的理解。
熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。
二．实验内容
1．简单查询操作。
该实验包括投影、选择条件表达，数据排序，使用临时表等。
具体完成以下题目，将它们转换为SQL语句表示，在学生选课数据库中实现其数据查询操作。
1．求数学系学生的学号和姓名
2．求选修了课程的学生学号
3．求选修课程号为‘C1’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。
4．求选修课程号为‘C1’且成绩在80~90之间的学生学号和成绩，并成绩乘以0.8输出。
5．求数学系或计算机系姓张的学生的信息。
6．求缺少了成绩的学生的学号和课程号
2．连接查询操作。
该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等。
7．查询每个学生的情况以及他所选修的课程
8．求学生的学号、姓名、选修的课程及成绩
9．求选修课程号为‘C1’且成绩在90以上的学生学号、姓名和成绩
10．查询每一门课程的间接先行课（即先行课的先行课）
三．实验方法
将查询需求用SQL语言表示；在SQLServer查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验3数据库的嵌套查询实验
一．实验目的
本实验的目的是使学生进一步掌握SQLServer查询分析器的使用方法，加深SQL语言的嵌套查询语句的理解
二．实验内容
本实验的主要内容是：
在SQLServer查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。
具体完成以下例题。
将它们用SQL语句表示，在学生选课中实现其数据嵌套查询操作。
例1求选修了高等数学的学号和姓名
例2求C1课程的成绩高于刘晨的学生学号和成绩
例3求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）
例4求其他系中比计算机系学生年龄都小的学生
例5求选修了C2课程的学生姓名
例6求没有选修C2课程的学生姓名
例7查询选修了全部课程的学生姓名
例8求至少选修了学号为“95002”的学生所选修全部课程的学生学号和姓名
三．实验方法
将查询需求用SQL语言表示：
在SQLServer查询分析器的输入区中输入SQL查询语句：
设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验4数据库的集合查询和统计查询实验
四．实验目的
本实验的目的是使学生熟练掌握SQLServer查询分析器的使用方法，加深SQL语言查询语句的理解。
熟练掌握数据查询中的分组、统计、计算和集合的操作方法。
五．相关知识
（1）分组和SQL函数查询
SQL函数是综合信息的统计函数，包括计数、求最大值、最小值、平均值、和值等，较详细的SQL函数及功能见第三章3.3节（SQL查询用的函数）。
SQL函数可作为列标识符出现在SELECT子句的目标列或HAVING子句的条件中。
在SQL查询语句中，如果有GROUPBY分组子句，则语句中的函数为分组统计函数；如果没有GROUPBY分组子句，则语句中的函数为全部结果集的统计函数。
表1基本的SQL函数
函数
功能
AVG（<数值表达式>）
求与字段相关的数值表达式的平均值
SUM（<数值表达式>）
求与字段相关的数值表达式的和值
MIN（<字段表达式>）
求字段表达式的最小值
MAX（<字段表达式>）
求字段表达式的最大值
COUNT（*|<字段>）
求记录行数（*），或求不是NULL的字段的行数
（2）集合查询
每一个SELECT语句都能获得一个或一组元组。
若要把多个SELECT语句的结果合并为一个结果，可用集合来完成。
集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。
标准SQL语言中提供了并操作UNION，没有直接提供交操作INTERSECT和差操作MINUS，但可以用其他方法来实现，具体实现方法依查询不同而不同。
六．实验内容
本实验的主要内容是：
a）分组查询实验。
该实验包括分组条件表达、选择组条件表达的方法。
b）使用函数查询的实验。
该实验包括统计函数和分组统计函数的使用方法。
c）集合查询实验。
该实验并操作UNION、交操作INTERSECT和差操作MINUS的实现方法。
具体完成以下例题，将它们用SQL语句表示，在学生选课中实现其数据查询操作。
例1求学生的总人数
例2求选修了课程的学生人数
例3求课程和选修了该课程的学生人数
例4求选修超过3门课的学生学号
例5查询计算机科学系的学生及年龄不大于19岁的学生
例6查询计算机科学系的学生与年龄不大于19岁的学生的交集
例7查询计算机科学系的学生与年龄不大于19岁的学生的差集
例8查询选修课程‘C1’的学生集合与选修课程‘C2’的学生集合的交集
例9查询选修课程‘C1’的学生集合与选修课程‘C2’的学生集合的差集
七．实验步骤
将查询需求用SQL语言表示：
在SQLServer查询分析器的输入区中输入SQL查询语句：
设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验5数据库的更新实验
八．实验目的
本实验的目的是要求学生熟练掌握使用SQL和通过SQLServer企业管理器向数据库输入数据、修改数据和删除数据的操作。
九．相关知识
SQLServer提供了很多方法更新表中数据。
以插入记录为例，可以使用INSERT语句、VALUES子句将特定值集合插入一行；可以使用数据库接口API（ADO、OLEDB、ODBC和DB-Library）中的函数进行数据插入等。
本实验主要掌握INSERT、UPDATE和DELETE语句的基本用法。
1.插入操作，其语句的一般格式为
INSERT
INTOtable_or_view[（colum_list）]
VALUES（date_values）;
此语句是使date_values作为一行数据记录插入已命名的表或视图table_or_view中。
Column_list是由逗号分隔的table_or_view的列名列表，用来指定为其提供数据的列。
如果没有指定column_list，表或视图中的所有列都将接收数据。
如果column_list没有为表或视图中的所有列命名，将在列表中没有命名的任何列中插入一个NULL值（或者在默认情况下为这些列定义的默认值）。
在列的列表中没有指定的所有列都必须允许NULL值或者指定的默认值。
否则SQLServer将返回一个错误。
2.修改操作语句的一般格式为
UPDATE表名
SET<列名>=<表达式>[,<列名>=<表达式>]……
[where<条件>];
其功能是修改指定表中满足WHERE子句条件的元组。
其中SET子句用于指定修改方法，即用<表达式>的值取代相应的属性列值。
如果省略WHERE子句，则表示要修改表中的所有元组。
3.删除语句的一般格式为
DELETE
FROM<表名>
[WHERE<条件>];
其功能是从指定表中删除满足WHERE子句条件的所有元组。
如果省略WHERE子句，则表示要删除表中的所有元组，但表的定义仍在字典中。
一十．实验内容
1.通过企业管理器，在学生选课数据库的学生、课程和选课3个表中各输入10条记录。
要求记录不仅满足数据约束要求，还要有表间关联的记录。
2.具体完成以下例题。
将它们转换为SQL语句表示，在学生选课库中实现其数据更新操作。
例1将一新学生记录（学号：
95050；姓名：
陈冬；性别：
男；年龄：
18；所在系：
计算机科学系）插入学生表中。
例2插入一条选课记录（‘95020’,’C1’）。
例3对每一个系，求学生的平均年龄，并把结果存入数据库。
对于这道题，首先要在数据库中建立一个有两个属性列的新表，其中一列存放系名，另一列存放相应系的学生平均年龄。
CREATETABLEDeptage（所在系CHAR（20），平均年龄SMALLINT）;
然后对数据库的学生表按所在系分组求平均年龄，再把所在系和平均年龄存入新表中。
例4将学生95001的年龄改为22岁。
例5将所有学生的年龄增加一岁。
例6将计算机科学系全体学生的成绩置零。
例7删除学号为95006的学生记录
例8删除所有的学生选课记录
例9删除计算机科学系所有学生的选课记录
一十一．实验步骤
将查询需求用SQL语言表示；在SQLServer查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验6数据库的视图定义及维护实验
一．实验目的
本实验的目的是使学生掌握视图的定义与维护操作，加深对视图在关系数据库中的作用的理解。
二．相关知识
视图是根据子模式建立的虚拟表。
一个视图可以由一个表构造，也可以由多个表构造。
利用企业管理器和视图创建向导进行创建，查看和修改视图就如同对表的操作一样，非常容易。
三．实验内容
本实验的主要内容是：
1）创建和删除视图
2）查询和更新视图
具体完成以下例题。
例1建立信息系学生的视图
例2建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。
例3建立信息系选修了’C1’号课程的学生视图
例4建立信息系选修了’C1’号课程且成绩在90分以上的学生视图
例5定义一个反映学生出生年份的视图
四．实验步骤
1．创建视图
下面利用了SQLServer中提供的视图创建向导，来实现例3（建立信息系选修了1号课程的学生视图）。
1）打开企业管理器窗口，确认服务器，打开数据库文件夹，选中新视图所在的数据库（此例为学生选课数据库）。
2）选择菜单：
[工具]|[向导]
3）在如图1所示的向导选择对话框中，单击数据库左边的“+”号，使之展开。
选择“创建数据库向导”项，单击“确定”按键。
图1向导选择对话框
4）进入创建视图向导后，首先出现的是欢迎使用创建视图向导对话框，其中简单介绍了该向导的功能。
单击“下一步”后，就会出现选择数据库对话框。
5）在选择数据库对话框中，选择视图所属的数据库。
本例的数据库为“学生选课”。
单击“下一步”按键，则进入选择表对话框。
6）在选择表对话框中，列出了指定数据库中所有用户定义的表。
用户可以从中选择构造视图所需的一个表或者多个表，被选中的表成为构造视图的参考表。
选择构造视图参考表的方法是：
用鼠标单击表后的“包含在视图中”列，使复选框为选中状态。
本例的数据库中只有学生表和选课表，它们都应当被选中。
单击“下一步”按键，则进入选择对话框。
7）列选择对话框中以表格形式列出了创建视图参考表的全部属性，每个属性占表的一行，创建视图参考属性可以在表格中选出。
选择视图参考属性的方法是用鼠标单击属性名后边的“选择列”列，使复选框为选中状态。
本例中学生.学号，学生.姓名和选课.成绩属性要被选中。
单击“下一步”按键，进入创建视图的定义限制对话框，如图2所示。
图2定义限制对话框
8）在定义限制对话框中，输入表的连接和元组选择条件。
本例输入“所在系=’信息系’AND学生.学号=选课.学号AND选课.课程号=‘C1’”。
单击“下一步”按键，出现视图名对话框。
9）在输入视图名对话框中输入所建视图的标识名。
本例的视图名为“信息系—选课1”。
单击“下一步”按键，则出现视图创建完成对话框。
在完成对话框中给出了根据前面对话框输入的内容译成的SQL语句。
您可以认真阅读该SQL语句，如果发现与要求有不符合之处可以直接进行修改。
确认无误后单击“完成”按钮。
随后出现一个通知用户已成功创建视图的信息框，单击“确认”按钮后，整个创建视图工作就完成了。
2．查看和修改视图
视图创建好后，就可以利用它进行查询信息了。
如果发现视图的结构不能很好地满足要求，还可以在企业管理器对它进行修改。
在企业管理器中修改视图结构需要首先选择服务器，数据库，并使数据库展开，然后用鼠标右击要修改的视图，在弹出的菜单上选择“设计视图”项，则弹出一个视图设计对话框，如图3所示。
当对其修改完毕后关闭窗口，新的视图结构就会取代原先的结构。
3．将查询需求用SQL语言表示；在SQL查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验7数据安全性实验
一．实验目的
本实验的目的是通过实验使学生加深对数据安全性的理解，并掌握SQLServer中有关用户，角色及操作权限的管理方法。
二．相关知识
SQLServer提供以下四种防线：
1．WindowsNT操作系统的安全防线
WindowsNT的网络管理员负责建立用户组，设置帐号并注册，同时决定不同的用户对不同的系统资源的访问级别。
用户只有拥有了一个有效的WindowsNT登录帐号，才能对网络资源进行访问。
2．SQLServer的运行安全防线
SQLServer通过另外一种帐号设置来创建附加安全层。
SQLServer具有标准登录和集成登录两种用户登录方式，用户只有登录成功，才能与SQLServer建立一次连接。
3．SQLServer数据库的安全防线
SQLServer的特定数据库都有自己的用户和角色（用户组），该数据库只能由它的用户或角色访问，其他用户无权访问其数据，数据库系统可以通过创建和管理特定的数据库的用户和角色来保证数据库不被非法用户访问。
4．SQLServer数据库对象的安全防线
SQLServer可以对权限进行管理，SQL的DCL功能保证合法用户即使进了数据库也不能有超越权限的数据操作，即合法用户必须在自己的权限范围内进行数据操作。
三．实验内容
本实验的实验内容为：
1．在SQLServer企业管理器中，设置SQLServer的安全认证模式。
2．通过SQLServer企业管理器，实现对SQLServer的用户和角色管理。
3．分别通过SQLServer企业管理器和SQL的数据控制功能，设置和管理数据操作权限。
四．实验步骤
1．在SQLServer企业管理器中为所属的SQL服务器设置为SQLServer和WindowsNT混合安全认证模式。
其步骤如下：
1）在企业管理器窗口中展开服务器组，用鼠标右击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，则出现SQLServer属性对话框，如图1所示。
图1SQLServer属性对话框
2）在SQLServer属性对话框中选择“安全性”选项卡。
3）身份验证选择“SQLServer和Windows”单选项。
2．在SQLServer企业管理器中为自己建立一个服务器用户、数据库用户和数据库角色。
1）在企业管理器窗口中展开服务器组，展开服务器，用鼠标单击“安全性”文件夹右侧的‘+’，用鼠标右击“登录”，在弹出的菜单中选择“新建登录”项，则出现新建登录对话框，如图2所示。
图2新建登录对话框
2）在新建登录对话框中有常规，服务器角色和数据库访问三个选项卡。
在常规选项卡中，输入用户名（本例为王平），选择SQLServer安全验证，输入用户口令。
在服务器角色选项卡中，需要确定用户所属的服务器角色，在本例中采用缺省值即可。
在数据库访问选项卡中，需要指定此登录可以访问的数据库（本例选中学生选课数据库）和“学生选课”的数据库角色（在本例中采用缺省值即可）。
单击“确定”按钮，既完成了创建登录用户的工作。
3．关闭SQLServer企业管理器，打开SQL查询分析器。
选择SQLServer安全验证，用户名为王平，输入用户口令，连接到SQLServer。
在“查询”窗口中可以输入SQL语句。
如“SELECT*FROM学生”。
运行后，得到消息“拒绝了对对象‘学生’（数据库‘学生选课’，所有者‘dbo’）的SELECT权限。
”。
可见用户王平没有对学生表的SELECT权限。
4．要想将学生选课数据库的操作权限赋予数据库用户王平，有两种方法。
方法一：
通过企业管理器
1）在企业管理器窗口中展开服务器组，展开服务器，用鼠标单击“数据库”文件夹右侧的‘+’，用鼠标单击学生选课数据库文件夹右侧的‘+’，用鼠标右击“用户”。
在屏幕右侧的“用户”窗口中选择“王平”项，用鼠标右击，在弹出的菜单中选择“属性”项，则出现数据库用户属性对话框，如图3所示。
图3数据库用户属性对话框
2）单击图3中的权限按钮，则出现数据库用户权限对话框，如图4所示。
对话框的下面是有关数据库用户和角色所对应的权限表。
这些权限均以复选框的形式表示。
复选框有三种状态：
“√”为授权；“×”为废除权；空为撤权。
在表中可以对用户或角色的各种对象操作权（SELECT，INSERT，UPDATE，DELETE，EXEC和DRI）进行授予或撤消。
图4数据库用户权限对话框
3）在图4中找到学生表，授予SELECT权限，即让学生表与SELECT列交叉的复选框为“√”即可。
方法二：
通过SQL的数据控制功能
对用户王平授权，必须是数据库对象拥有者以上用户授予。
我们可以以系统管理员或sa用户登录进入查询分析器。
在查询分析器中输入授权语句“GRANTSELECTON学生TO王平；”，然后执行即可。
实验8数据完整性实验
一．实验目的
本实验的目的是通过实验使学生加深对数据完整性的理解，学会创建和使用触发器。
二．相关知识
SQLServer实现数据完整性的具体方法有4种：
约束、缺省、规则和触发器。
其中约束和缺省在实验一中已接触过。
本实验重点学会创建和使用触发器。
触发器是实施复杂完整性的特殊类型的存储类型。
触发器不需要专门语句调用，当对它所保护数据进行修改时自动激活，以防止对数据进行不正确，未授权或不一致的修改。
创建触发器的语法为：
CREATETRIGGER<触发器>ON<表名>
[WITHENCRYPTION]
FOR{[DELETE][，][INSERT][，][UPDATE]}
[WITHAPPEND]
[NOTFORREPLICATION]
AS
其中：
1）WITHENCRYPTION为加密选项。
2）DELETE选项为创建DELETE触发器。
DELETE触发器的作用是当对表执行DELETE操作时触发器被激活，并从指定表中删除元组，同时将删除的元组放入一个特殊的逻辑表（delete表）中。
触发器的动作可以检查delete表中的数据，以确定下一步该如何处理。
3）INSERT选项为创建INSERT触发器。
INSERT触发器在对指定表中执行插入数据操作时激活，激活后将插入表中的数据拷贝并送入一个特殊的逻辑表（inserted表）中，触发器会根据INSERT表中的值决定如何处理。
4）UPDATE选项为创建UPDATE触发器。
UPDATE触发器仅在对指定表中进行更新数据操作时激活。
UPDATE触发器激活后把将要被更新的原数据移入delete表中再将要被更新后的新数据的备份送入insert表中，UPDATE触发器对delete和inserted表进行检查，并决定如何处理。
5）NOTFORREPLICATION选项说明当一个复制过程在修改一个触发器表时，与该表相关联的触发器不能被执行。
一个触发器只适用于一个表，每个表最多只能有三个触发器，它们分别是INSERT、UPDATE和DELETE触发器。
触发器仅在实施数据完整性和处理业务规则时使用。
三．实验内容
本实验的内容为：
使用SQL设计触发器，通过SQLServer企业管理器定义它们。
具体完成下面例题：
利用触发器来保证学生选课库中选课表的参照完整性，以维护其外码与参照表中的主码一致。
CREATETRIGGERSC_insertedON选课
FORINSERT
ASIF（SELECTCOUNT（*）
FROM学生，inserted，课程
WHERE学生.学号=inserted.学号ANDinserted.课程号=课程.课程号）=0
ROLLBACKTRANSACTION
四．实验步骤
1、在企业管理器中创建触发器
1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库），打开表文件夹，在表窗口中用鼠标右击触发器所属的表（本例为选课表）。
2）在弹出菜单上选择“所有任务”“管理触发器”项，则弹出如图所示的触发器属性对话框。
图触发器属性对话框
如果要新建触发器，在文本[T]的文本框中输入创建触发器的SQL语句（见实验内容），单击“检查语法”按钮进行语法的检查，检查无误后，单击确定按钮。
3）如果要修改触发器，则在名字弹出项中选择要修改的触发器名（本例为SC_inserted）,然后在文本[T]的文本框中对已有的内容进行修改，检查无误后单击“确定”按钮。
4）如果要删除触发器，则在名字弹出项中选择要删除的触发器名（本例为SC_inserted），并检查文本[T]的文本框中的内容，
升级会员 