数据库原理实验

数据库原理实验指导书数据库原理实验指导书 实验一实验一 数据库模式设计及建立数据库模式设计及建立 实验目的 掌握数据库模式设计 依据实际要求设计表结构 建立表的关系 比较 SQL 命令方式和可视化环境管理器方式设计的异同点 实验类型 设计 验证 实验内容 a 用 sql 查询分析器设计商场 货物存货表 A 货物进 货表 A 货物消售 表 A b 用企业管理器设计商场 货物存货表 B 货物进货表 B 货物消售表 B 操作步骤 1 熟悉 SQL SERVER 的企业管理器和事件探查器 2 配置 ODBC 3 创建个人数据库 4 编写 货物存货表 A 货物进货表 A 货物销售表 A 5 建立新表的 SQL 语句 注意约束条件及表间关系 6 启动 查询分析器 7 连接 SQL SERVER 选择数据库 8 在 查询分析器 中输入已编写好的 SQL 命令 9 执行 SQL 命令 10 启动企业管理器 11 在控制台中选择先前建立的数据库 12 建立 货物存货表 A 货物进货表 A 货物消售表 A 13 设定表关系 实验实验 2 数据库的简单查询和连接查询实验数据库的简单查询和连接查询实验 一 实验目的 本实验的目的是使学生掌握 SQL Server 查询分析器的使用方法 加深对 SQL 和 SQL 语言的查询语句的理解 熟练掌握简单表的数据查询 数据排序和数据连接查询 的操作方法 二 实验内容 1 简单查询操作 该实验包括投影 选择条件表达 数据排序 使用临时表等 具体完成以下题目 将它们转换为 SQL 语句表示 在学生选课数据库中实现其数 据查询操作 1 求数学系学生的学号和姓名 2 求选修了课程的学生学号 3 求选修课程号为 C1 的学生号和成绩 并要求对查询结果按成绩的降序排列 如果成绩相同按学号的升序排列 4 求选修课程号为 C1 且成绩在 80 90 之间的学生学号和成绩 并成绩乘以 0 8 输出 5 求数学系或计算机系姓张的学生的信息 6 求缺少了成绩的学生的学号和课程号 2 连接查询操作 该实验包括等值连接 自然连接 求笛卡儿积 一般连接 外连 接 内连接 左连接 右连接和自连接等 7 查询每个学生的情况以及他所选修的课程 8 求学生的学号 姓名 选修的课程及成绩 9 求选修课程号为 C1 且成绩在 90 以上的学生学号 姓名和成绩 10 查询每一门课程的间接先行课 即先行课的先行课 三 实验方法 将查询需求用 SQL 语言表示 在 SQL Server 查询分析器的输入区中输入 SQL 查 询语句 设置查询分析器的结果区为 Standard Execute 标准执行 或 Execute to Grid 网 格执行 方式 发布执行命令 并在结果区中查看查询结果 如果结果不正确 要进 行修改 直到正确为止 实验实验 3 数据库的嵌套查询实验数据库的嵌套查询实验 一 实验目的 本实验的目的是使学生进一步掌握 SQL Server 查询分析器的使用方法 加深 SQL 语言的嵌套查询语句的理解 二 实验内容 本实验的主要内容是 在 SQL Server 查询分析器中使用 IN 比较符 ANY 或 ALL 和 EXISTS 操作符进行嵌套查询操作 具体完成以下例题 将它们用 SQL 语句表示 在学生选课中实现其数据嵌套查询 操作 例 1 求选修了高等数学的学号和姓名 例 2 求 C1 课程的成绩高于刘晨的学生学号和成绩 例 3 求其他系中比计算机系某一学生年龄小的学生 即年龄小于计算机系年龄最大者 的学生 例 4 求其他系中比计算机系学生年龄都小的学生 例 5 求选修了 C2 课程的学生姓名 例 6 求没有选修 C2 课程的学生姓名 例 7 查询选修了全部课程的学生姓名 例 8 求至少选修了学号为 95002 的学生所选修全部课程的学生学号和姓名 三 实验方法 将查询需求用 SQL 语言表示 在 SQL Server 查询分析器的输入区中输入 SQL 查询语 句 设置查询分析器的结果区为 Standard Execute 标准执行 或 Execute to Grid 网格执 行 方式 发布执行命令 并在结果区中查看查询结果 如果结果不正确 要进行修改 直到正确为止 实验实验 4 数据库的集合查询和统计查询实验数据库的集合查询和统计查询实验 四 实验目的 本实验的目的是使学生熟练掌握 SQL Server 查询分析器的使用方法 加深 SQL 语言查询语句的理解 熟练掌握数据查询中的分组 统计 计算和集合的操作方法 五 相关知识 1 分组和 SQL 函数查询 SQL 函数是综合信息的统计函数 包括计数 求最大值 最小值 平均值 和值等 较详细的 SQL 函数及功能见第三章 3 3 节 SQL 查询用的函数 SQL 函数可作为列标识 符出现在 SELECT 子句的目标列或 HAVING 子句的条件中 在 SQL 查询语句中 如果有 GROUP BY 分组子句 则语句中的函数为分组统计函数 如果没有 GROUP BY 分组子句 则语句中的函数为全部结果集的统计函数 表 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 语言表示 在 SQL Server 查询分析器的输入区中输入 SQL 查询语 句 设置查询分析器的结果区为 Standard Execute 标准执行 或 Execute to Grid 网格执 行 方式 发布执行命令 并在结果区中查看查询结果 如果结果不正确 要进行修改 直到正确为止 实验实验 5 数据库的更新实验数据库的更新实验 八 实验目的 本实验的目的是要求学生熟练掌握使用 SQL 和通过 SQL Server 企业管理器向数 据库输入数据 修改数据和删除数据的操作 九 相关知识 SQL Server 提供了很多方法更新表中数据 以插入记录为例 可以使用 INSERT 语句 VALUES 子句将特定值集合插入一行 可以使用数据库接口 API ADO OLE DB ODBC 和 DB Library 中的函数进行数据插入等 本实验主要掌握 INSERT UPDATE 和 DELETE 语句的基本用法 1 插入操作 其语句的一般格式为 INSERT INTO table or view colum list VALUES date values 此语句是使 date values 作为一行数据记录插入已命名的表或视图 table or view 中 Column list 是由逗号分隔的 table or view 的列名列表 用来指定为其提供数据的列 如果没有指定 column list 表或视图中的所有列都将接收数据 如果 column list 没有为表或视图中的所有列命名 将在列表中没有命名的任何列中插 入一个 NULL 值 或者在默认情况下为这些列定义的默认值 在列的列表中没有指定 的所有列都必须允许 NULL 值或者指定的默认值 否则 SQL Server 将返回一个错误 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 对每一个系 求学生的平均年龄 并把结果存入数据库 对于这道题 首先要在 数据库中建立一个有两个属性列的新表 其中一列存放系名 另一列存放相应系 的学生平均年龄 CREATE TABLE Deptage 所在系 CHAR 20 平均年龄 SMALLINT 然后对数据库的学生表按所在系分组求平均年龄 再把所在系和平均年龄存入新 表中 例 4 将学生 95001 的年龄改为 22 岁 例 5 将所有学生的年龄增加一岁 例 6 将计算机科学系全体学生的成绩置零 例 7 删除学号为 95006 的学生记录 例 8 删除所有的学生选课记录 例 9 删除计算机科学系所有学生的选课记录 十一 实验步骤 将查询需求用 SQL 语言表示 在 SQL Server 查询分析器的输入区中输入 SQL 查询语 句 设置查询分析器的结果区为 Standard Execute 标准执行 或 Execute to Grid 网格执 行 方式 发布执行命令 并在结果区中查看查询结果 如果结果不正确 要进行修改 直到正确为止 实验实验 6 数据库的视图定义及维护实验数据库的视图定义及维护实验 一 实验目的 本实验的目的是使学生掌握视图的定义与维护操作 加深对视图在关系数据库中 的作用的理解 二 相关知识 视图是根据子模式建立的虚拟表 一个视图可以由一个表构造 也可以由多个表 构造 利用企业管理器和视图创建向导进行创建 查看和修改视图就如同对表的操作 一样 非常容易 三 实验内容 本实验的主要内容是 1 创建和删除视图 2 查询和更新视图 具体完成以下例题 例 1 建立信息系学生的视图 例 2 建立信息系学生的视图 并要求进行修改和插入操作时仍须保证该视图只有信息 系的学生 例 3 建立信息系选修了 C1 号课程的学生视图 例 4 建立信息系选修了 C1 号课程且成绩在 90 分以上的学生视图 例 5 定义一个反映学生出生年份的视图 四 实验步骤 1 创建视图 下面利用了 SQL Server 中提供的视图创建向导 来实现例 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 查询语句 设置查询分析器的结果区为 Standard Execute 标准执行 或 Execute to Grid 网格 执行 方式 发布执行命令 并在结果区中查看查询结果 如果结果不正确 要进 行修改 直到正确为止 实验实验 7 7 数据安全性实验数据安全性实验 一 实验目的 本实验的目的是通过实验使学生加深对数据安全性的理解 并掌握 SQL Server 中有关 用户 角色及操作权限的管理方法 二 相关知识 SQL Server 提供以下四种防线 1 Windows NT 操作系统的安全防线 Windows NT 的网络管理员负责建立用户组 设置帐号并注册 同时决定不同的用 户对不同的系统资源的访问级别 用户只有拥有了一个有效的 Windows NT 登录帐号 才能对网络资源进行访问 2 SQL Server 的运行安全防线 SQL Server 通过另外一种帐号设置来创建附加安全层 SQL Server 具有标准登录 和集成登录两种用户登录方式 用户只有登录成功 才能与 SQL Server 建立一次连接 3 SQL Server 数据库的安全防线 SQL Server 的特定数据库都有自己的用户和角色 用户组 该数据库只能由它的 用户或角色访问 其他用户无权访问其数据 数据库系统可以通过创建和管理特定的 数据库的用户和角色来保证数据库不被非法用户访问 4 SQL Server 数据库对象的安全防线 SQL Server 可以对权限进行管理 SQL 的 DCL 功能保证合法用户即使进了数据库 也不能有超越权限的数据操作 即合法用户必须在自己的权限范围内进行数据操作 三 实验内容 本实验的实验内容为 1 在 SQL Server 企业管理器中 设置 SQL Server 的安全认证模式 2 通过 SQL Server 企业管理器 实现对 SQL Server 的用户和角色管理 3 分别通过 SQL Server 企业管理器和 SQL 的数据控制功能 设置和管理数据操作权 限 四 实验步骤 1 在 SQL Server 企业管理器中为所属的 SQL 服务器设置为 SQL Server 和 Windows NT 混合安全认证模式 其步骤如下 1 在企业管理器窗口中展开服务器组 用鼠标右击需要设置的 SQL 服务器 在 弹出的菜单中选择 属性 项 则出现 SQL Server 属性对话框 如图 1 所示 图 1 SQL Server 属性对话框 2 在 SQL Server 属性对话框中选择 安全性 选项卡 3 身份验证选择 SQL Server 和 Windows 单选项 2 在 SQL Server 企业管理器中为自己建立一个服务器用户 数据库用户和数据库角 色 1 在企业管理器窗口中展开服务器组 展开服务器 用鼠标单击 安全性 文 件夹右侧的 用鼠标右击 登录 在弹出的菜单中选择 新建登录 项 则出现新建登录对话框 如图 2 所示 图 2 新建登录对话框 2 在新建登录对话框中有常规 服务器角色和数据库访问三个选项卡 在常规选项卡中 输入用户名 本例为王平 选择 SQL Server 安全验证 输 入用户口令 在服务器角色选项卡中 需要确定用户所属的服务器角色 在本例中采用缺 省值即可 在数据库访问选项卡中 需要指定此登录可以访问的数据库 本例选中学生选 课数据库 和 学生选课 的数据库角色 在本例中采用缺省值即可 单击 确定 按钮 既完成了创建登录用户的工作 3 关闭 SQL Server 企业管理器 打开 SQL 查询分析器 选择 SQL Server 安全验证 用户名为王平 输入用户口令 连接到 SQL Server 在 查询 窗口中可以输入 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 用户登录进入查询分析器 在查询分析器中输入授权语句 GRANT SELECT ON 学生 TO 王平 然后执行即可 实验实验 8 数据完整性实验数据完整性实验 一 实验目的 本实验的目的是通过实验使学生加深对数据完整性的理解 学会创建和使用触发器 二 相关知识 SQL Server 实现数据完整性的具体方法有 4 种 约束 缺省 规则和触发器 其中约 束和缺省在实验一中已接触过 本实验重点学会创建和使用触发器 触发器是实施复杂完整性的特殊类型的存储类型 触发器不需要专门语句调用 当对 它所保护数据进行修改时自动激活 以防止对数据进行不正确 未授权或不一致的修改 创建触发器的语法为 CREATE TRIGGER ON WITH ENCRYPTION FOR DELETE INSERT UPDATE WITH APPEND NOT FOR REPLICATION AS 其中 1 WITH ENCRYPTION 为加密选项 2 DELETE 选项为创建 DELETE 触发器 DELETE 触发器的作用是当对表执行 DELETE 操作时触发器被激活 并从指定表中删除元组 同时将删除的元组放入 一个特殊的逻辑表 delete 表 中 触发器的动作可以检查 delete 表中的数据 以 确定下一步该如何处理 3 INSERT 选项为创建 INSERT 触发器 INSERT 触发器在对指定表中执行插入数据 操作时激活 激活后将插入表中的数据拷贝并送入一个特殊的逻辑表 inserted 表 中 触发器会根据 INSERT 表中的值决定如何处理 4 UPDATE 选项为创建 UPDATE 触发器 UPDATE 触发器仅在对指定表中进行更新 数据操作时激活 UPDATE 触发器激活后把将要被更新的原数据移入 delete 表中 再将要被更新后的新数据的备份送入 insert 表中 UPDATE 触发器对 delete 和 inserted 表进行检查 并决定如何处理 5 NOT FOR REPLICATION 选项说明当一个复制过程在修改一个触发器表时 与该 表相关联的触发器不能被执行 一个触发器只适用于一个表 每个表最多只能有三个触发器 它们分别是 INSERT UPDATE 和 DELETE 触发器 触发器仅在实施数据完整性和处理业务规则时 使用 三 实验内容 本实验的内容为 使用 SQL 设计触发器 通过 SQL Server 企业管理器定义它们 具体完成下面例题 利用触发器来保证学生选课库中选课表的参照完整性 以维护其 外码与参照表中的主码一致 CREATE TRIGGER SC inserted ON 选课 FOR INSERT AS IF SELECT COUNT FROM 学生 inserted 课程 WHERE 学生 学号 inserted 学号 AND inserted 课程号 课程 课程号 0 ROLLBACK TRANSACTION 四 实验步骤 1 在企业管理器中创建触发器 1 在企业管理器中 由服务器开始逐步扩展到触发器所属表的数据库 本例为学生选课 数据库 打开表文件夹 在表窗口中用鼠标右击触发器所属的表 本例为选课表 2 在弹出菜单上选择 所有任务 管理触发器 项 则弹出如图所示的触发器属性对话 框 图 触发器属性对话框 如果要新建触发器 在文本 T 的文本框中输入创建触发器的 SQL 语句 见实验内容 单击 检查语法 按钮进行语法的检查 检查无误后 单击确定按钮 3 如果要修改触发器 则在名字弹出项中选择要修改的触发器名 本例为 SC inserted 然后在文本 T 的文本框中对已有的内容进行修改 检查无误后单击 确定 按钮 4 如果要删除触发器 则在名字弹出项中选择要删除的触发器名 本例为 SC inserted 并检查文本 T 的文本框中的内容 看是否选得正确 最后单击 删除 按钮 2 验证触发器 1 在企业管理器中 由服务器开始逐步扩展到触发器所属表的数据库 本例为学生选课 数据库 此时 可在选课表中输入一条记录 学号 96001 课程号 C8 成绩 88 显然 这条记录是不符合参照完整性的 通过企业管理器输入时 会出现错误提 示 违反了数据库的约束 如果约束 C5 和 C6 同时存在的话 2 如果约束 C5 和 C6 存在 则删除之 参见实验一 3 打开 SQL 查询分析器 输入语句 INSERT INTO 选课 VALUES 96001 C8 88 查看执行结果 4 删除触发器 本例为 SC inserted 重复步骤 3 实验实验 9 数据库备份和恢复实验数据库备份和恢复实验 五 实验目的 本实验的目的是通过实验使学生了解 SQL Server 的数据备份和恢复机制 掌握 SQL Server 中数据库备份和恢复的方法 六 相关知识 1 SQL Server 的三种备份形式 SQL Server 具有三种备份形式 完全备份 事务日志和差异备份 1 完全备份 将数据库完全复制到备份文件中 2 事务日志备份 备份发生在数据库上的事务 3 差异备份