数据库实验报告 数据库实验上机实验报告

课课 程程 设设 计计 报报 告告 题目题目 数据库实验上机实验报告数据库实验上机实验报告 专业班级 专业班级 计算机科学与技术计算机科学与技术 1210 班班 学学 号 号 U201215079 姓姓 名 名 候宝峰候宝峰 指导教师 指导教师 报告日期 报告日期 2015 06 04 计算机科学与技术学院计算机科学与技术学院 目录目录 一 基本 SQL 操作 部分选做 1 1 数据定义 1 2 数据更新 3 3 用 SQL 语句完成下述查询需求 4 二 DBMS 综合运用 部分选做 8 1 学习 sqlserver 的两种完全备份方式 数据和日志文件的脱机 备份 系统的备份功能 选做 8 2 学习系统的身份 权限配置操作 9 3 了解 SQLSERVER 的存储过程 触发器 函数实现过程 14 三 实验总结 17 1 实验问题及解决 17 2 实验心得 18 一 基本一 基本 SQL 操作 部分选做 操作 部分选做 1 1 数据定义 数据定义 参照下面的内容建立自己实验所需的关系数据 创建三个关系 商品表 商品名称 商品类型 GOODS GNAME char 20 GTYPE char 10 主关键字为 商品名称 商品类型为 电器 文具 服装 商场 商场名称 所在地区 PLAZA PNAMEchar 20 PAREAchar 20 主关键字为商场名称 所在地区为 洪山 汉口 汉阳 武昌 销售价格表 商品名称 商场名称 当前销售价格 目前举办活动类型 SALE GNAMEchar 20 PNAMEchar 20 PRICE FLOAT ATYPEchar 10 主关键字为 商品名称 商场名称 举办活动类型为 送券 打折 也可为空值 表示 当前未举办任何活动 表中记录如 哈森皮靴 亚贸广场 200 打折 同一商场 针对不同的商品可能采取不同的促销活动 create table goods gname char 20 primary key gtype char 10 create table plaza pname char 20 primary key parea char 20 create table sale gname char 20 pname char 20 price FLOAT atype char 10 check atype in 送券送券 打折打折 primary key gname pname foreign key gname references goods gname foreign key pname references plaza pname 图 1 goods 表 图 2 plaza 表 图 3 sale 表 2 2 数据更新 数据更新 1 向上述表格中用 sql 语句完成增 删 个 改的操作 增加记录 insert into goods gname gtype values anta 服装 删除记录 delete from goods where gname 南孚 and gtype 电池 更新记录 update goods set gtype 电器 where gname 飞科 2 编写一个触发器 并测试该触发器 当插入一个 anta 货物时不能把它售价低于 350 如果低于 350 就修改成 350 create trigger dbo Chang Update sale on dbo sale after insert as if select count from sale inserted where sale gname anta and sale price30 图 10 查询 5 6 查询以 送券 方式销售的商品总数超过 30 种的商场所在地区 select pname PAREA from plaza where pname in select distinct pNAME from sale where atype 送券 and gname in select gNAME from sale group by gname having count 30 图 11 查询 6 7 查询价格为下列取值之一的商品名称 所在商场名称 目前举办活动的类型 88 188 288 388 488 588 888 select gname pname atype from sale where price in 88 188 288 388 488 588 888 图 12 查询 7 8 查询以 老 字开头的所有商品的名称 select from goods where gname like 老 图 13 查询 8 9 查询同时销售 剃须刀 和 电池 的商场名称 select pname from sale where gname 南孚 and pname in select pname from sale where gname 飞科 图 14 查询 9 10 查询不举办任何活动的商场 select distinct plaza pname PAREA from sale plaza where atype is null and sale pname plaza pname 图 15 查询 10 11 查询所销售的商品包含了 校园超市 所销售的所有商品的商场名称 select distinct pname from sale where gname in select gname from sale where pname 家乐福 图 16 查询 11 二 二 DBMS 综合运用 部分选做 综合运用 部分选做 1 1 学习 学习 sqlserversqlserver 的两种完全备份方式 数据和日志文件的的两种完全备份方式 数据和日志文件的 脱机备份 系统的备份功能 选做 脱机备份 系统的备份功能 选做 利用企业管理器 选择工具下的备份数据库 还以利用企业管理器设置自动备份计划 等 直接拷贝数据文件 把数据库的数据文件 mdf 和日志文件 ldf 都拷贝到目的服务器 在 SQL Server Query Analyzer 中用语句进行恢复 RESTORE DATABASE 数据库名 数据库名变量 FROM n WITH RESTRICTED USER NORECOVERY RECOVERY STANDBY undo file name RESTORE LOG 数据库名 数据库名变量 FROM n WITH RESTRICTED USER NORECOVERY RECOVERY STANDBY undo file name 使得数据库恢复到备份状态 2 2 学习系统的身份 权限配置操作 学习系统的身份 权限配置操作 图 17 sql server 安全性决策 当 SQL Server 2014 在 Windows 上运行时 sysadmin 固定服务器角色成员可以指定下面 两种身份验证模式之一 Windows 身份验证模式 只进行 Windows 身份验证 用户不能指定 SQL Server 2014 登录 ID 这是 SQL Server 2014 的默认身份验证模式 不能为在 Windows 98 上运行的 SQL Server 实例指定 Windows 身份验证模式 因为此操作系统不支持 Windows 身份验证 当用户通过 Windows NT 4 0 或 Windows 用户帐户进行连接时 SQL Server 通过回叫 Windows NT 4 0 或 Windows 以获得信息 重新验证帐户名和密码 SQL Server 通过使用网络用户的安全特性控制登录访问 以实现与 Windows NT 4 0 或 Windows 的登录安全集成 用户的网络安全特性在网络登录时建立 并通过 Windows 域 控制器进行验证 当网络用户尝试连接时 SQL Server 使用基于 Windows 的功能确定经 过验证的网络用户名 SQL Server 于是验证此人是否是如其所说的那个人 然后只基于网 络用户名允许或拒绝登录访问 而不要求单独的登录名和密码 说明 如果用户试图通过提供空白登录名称连接到 SQL Server 的实例 SQL Server 将使 用 Windows 身份验证 此外 如果用户试图使用特定的登录连接到配置为 Windows 身 份验证模式的 SQL Server 实例 则将忽略该登录并使用 Windows 身份验证 与 SQL Server 身份验证相比 Windows 身份验证有某些优点 主要是由于它与 Windows NT 4 0 和 Windows 安全系统的集成 Windows NT 4 0 和 Windows 安全系统提供 更多的功能 如安全验证和密码加密 审核 密码过期 最短密码长度 以及在多次登录 请求无效后锁定帐户 由于 Windows NT 4 0 和 Windows 用户和组只由 Windows NT 4 0 或 Windows 维护 因此当用户进行连接时 SQL Server 将读取有关该用户在组中的成员资格信息 如果对已 连接用户的可访问权限进行更改 则当用户下次连接到 SQL Server 实例或登录到 Windows NT 4 0 或 Windows 时 取决于更改的类型 这些更改会生效 混合模式 如果用户在登录时提供了 SQL Server 2014 登录 ID 则系统将使用 SQL Server 身份验 证对其进行验证 如果没有提供 SQL Server 2014 登录 ID 或请求 Windows 身份验证 则 使用 Windows 身份验证对其进行身份验证 当用户用指定的登录名称和密码从非信任连接进行连接时 SQL Server 通过检查是否 已设置 SQL Server 登录帐户 以及指定的密码是否与以前记录的密码匹配 自己进行身份 验证 如果 SQL Server 未设置登录帐户 则身份验证将失败 而且用户收到错误信息 提供 SQL Server 身份验证是为了向后兼容性 因为为 SQL Server 7 0 版或更早的版本 编写的应用程序可能要求使用 SQL Server 登录和密码 另外 当 SQL Server 实例在 Windows 98 上运行时 必须使用 SQL Server 身份验证 因为在 Windows 98 上不支持 Windows 身份验证模式 因此 SQL Server 在 Windows 98 上运行时使用混合模式 但只 支持 SQL Server 身份验证 尽管建议使用 Windows 身份验证 但对于 Windows NT 4 0 和 Windows 客户端以外 的其它客户端连接 可能需要使用 SQL Server 身份验证 说明 当使用命名管道连接到在 Windows NT 4 0 或 Windows 上运行的 SQL Server 实 例时 用户必须有连接到 Windows NT 命名管道 IPC IPC 的权限 如果用户没有连接 权限 则不能使用命名管道连接到 SQL Server 实例 除非计算机上的 Windows NT 4 0 或 Windowsguest 帐户已启用 默认情况下禁用 或者给用户帐户授予 从网络访问该计算机 的权限 设置 SQL Server 2014 身份验证模式 SQL Server 2014 身份验证模式可以在安装过程中指定或使用 SQL Server 企业管理器指 定 如下图 图 18 安全设置 也可以通过修改注册表的方式来改变 如下图 它保存在 HKEY LOCAL MACHINE SOFTWARE Microsoft MSSQLServer MSSQLServer 节的 LoginMode 中 LoginMode 的键值 1 表示 Windows 身份验证模式 2 表示混合模式 图 19 注册表更改 注 改变模式后 必须重新启动 SQL Server 2014 才会生效 SQL Server 2014 联机丛书 若要连接到 Microsoft SQL Server 2000 实例 只需给应用程序提供下面中的两条信 息 运行 SQL Server 实例的计算机所在的网络名称 实例名 可选 只有在连接到命名实例时才需要 登录标识符 ID 登录 ID 是帐户标识符 用来控制对任何 SQL Server 2014 系统的访问权限 SQL Server 2014 只有在首先验证了指定的登录 ID 有效后 才完成连接 这种登录验证称为身 份验证 在登录属性中 有一个是默认数据库 当一个登录连接到 SQL Server 时 这个默认数 据库就变成该连接的当前数据库 除非该连接请求指定另一个数据库作为当前数据库 登录 ID 仅能使您连接到 SQL Server 实例 特定数据库内的权限由用户帐户控制 数 据库管理员将您的登录帐户映射到您有权访问的任何数据库中的用户帐户 Windows 身份验证 SQL Server 2014 sysadmin 固定服务器角色成员必须首先向 SQL Server 2014 指定所有 允许连接到 SQL Server 2014 的 Microsoft Windows NT 或 Microsoft Windows 2000 帐户 或组 当使用 Windows 身份验证时 在连接到 SQL Server 2014 时不必指定登录 ID 或密 码 用户对 SQL Server 2014 的访问权限由 Windows NT 或 Windows 帐户或组控制 当登 录到客户端上的 Windows 操作系统时需接受身份验证 当连接到 SQL Server 2014 时 SQL Server 2014 客户端软件向 SQL Server 2014 请求 Windows 信任连接 直到客户端使用有效的 Windows 帐户成功登录后 Windows 才打开 信任连接 信任连接的属性包括打开连接的客户端的 Windows NT 和 Windows 组及用户 帐户 SQL Server 2014 从信任连接属性中得到用户的帐户信息 并将它们与定义为有效 SQL Server 2014 登录的 Windows 帐户相匹配 如果 SQL Server 2014 找到匹配的项 则 接受这个连接 当使用 Windows 身份验证连接到 SQL Server 2014 时 用户标识即是 Windows NT 或 Windows 组或用户帐户 Microsoft Windows Me 和 Windows 98 操作系统不支持服务器端的信任连接 API SQL Server 在 Windows Me 或 Windows 98 上运行时不支持 Windows 身份验证 用户在连接 时必须提供 SQL Server 登录帐户 当 SQL Server 在 Windows NT 或 Windows 上运行时 Windows Me Windows 98 和 Windows 95 客户端可以使用 Windows 身份验证与其连接 sysadmin 固定服务器角色成员首先向 SQL Server 2014 指定所有有效的 SQL Server 2014 登录帐户和密码 这些登录帐户和密码与用户的 Microsoft Windows 帐户或网络帐户 无关 当连接到 SQL Server 2014 时 用户必须提供 SQL Server 2014 登录帐户和密码 系 统将通过用户的 SQL Server 2014 登录帐户在 SQL Server 2014 中标识用户 由此可以看出 SQL Server 2014 安全控制是由登录 用户 权限来得到控制的 因此 要连接 SQL Server 2014 首先要经过身份验证 要通过身份验证 就必须拥有登录 ID 新建用户 ceshi 图 20 建立新用户 分配选择 goods 表权限给用户 ceshi grant select on goods to ceshi 图 21 分配 select 权限 分配插入删除权限给 ceshi grant insert and delete on plaza to ceshi 图 22 分配 insert delete 权限 使用 sql server 登陆验证方式登陆 ceshi 展开数据库 只有 goods 表 和 plaza 表 图 23 ceshi 用户所有表 收回 ceshi 用户所有的权限 Revoke all on goods from ceshi Revoke all on plaza from ceshi 图 24 回收所有的权限 3 3 了解 了解 SQLSERVERSQLSERVER 的存储过程 触发器 函数实现过程的存储过程 触发器 函数实现过程 通过查看 SQLSERVER 的联机帮助文档和示例 体会存储过程 函数和触发器的原理 尝试编制具备基本功能的存储过程 触发器和函数的实际例子 查看其执行效果 创建一个存储过程 其中包含插入和删除操作 经验证存储结果 过程生效 Create Procedure MyProcedure AS Begin Set NOCOUNT ON Set XACT ABORT ON Begin Tran Delete from goods where gname anta Insert into plaza values 校园超市 武广 Commit Tran End 创建一个级联删除触发器 使得在 sale 中删除一个货物时删除 goods 表中同种货物 create trigger Chang Update sale on dbo sale for delete as delete goods from goods sale where sale gname goods gname 图 25 级联删除结果 1 图 26 级联删除结果 2 创建一个函数选择 goods 表所有内容 SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO Author Create date Description CREATE FUNCTION typoints RETURNS TABLE AS RETURN SELECT from goods Select 选择该函数 由于是表值函数所以用 select from dbo typoints 调用 选择 goods 表内容 有结果可以看出 该函数执行成功 与直接执行函数体SELECT from goods 的结果是一样的 图 27 函数执行结果 三 实验总结三 实验总结 1 1 实验问题及解决 实验问题及解决 1 由于安装的是较新版的数据库 sql server 2014 使得有些语句与书上不符 比如建触 发器语句 create trigger dbo Chang Update sale on dbo sale after insert as if select count from sale inserted where sale gname anta and sale price 350 1 begin update sale set sale price 350 where sale gname anta and sale pname 家乐福 end 书上有before 语句 2014 版中不支持before语句但有instead of 语句 同时也不支持 or连接两个操作的说明 还是要查找一些资料才能理解语句的内涵 才能知道虽然变了部 分语法 但是还是换汤不换药的方式 2 建立视图是 总是建立失败 第一次未能插入到新视图里 第二次是想直接建立一 个平均后的视图 经过多次尝试失败 还使用了group语句去完善自己的视图 虽然实验简 单 但是同样体会到数据库的重要性 同样操作不容马虎 不能有一点一点的失误 3 所学标准语句与实际所用的数据库产品标准的差异问题 虽然很多时候核心的语句是一致的 但是有一些地方不一样 比如grant时 标准语句 需要在表明前加上table 但是在使用SQL Server 2014时 发现语句有问题 和同学讨论了 很久 改了很多关键字都不起效 后来在网上找到实际的grant语句 进行仔细地比较才发 现在SQL Server 2014里面是不需要 table 这个关键字的 4 创建外码的问题 一开始 随手就先创建sale关系 然后创建goods关系 但是后来发现 由于sale关系用到了goods关系的外码而无法实现 所以提示创建失败 最后 只好先创建goods关系 这应该是一个