数据库实验指导书

实验 1 SQL Server 2008 DBMS 基本框架 1 1 认识数据库服务器 实例 数据库 1 开始菜单中进入 Sql server2008 management studio 启动资源管理控制台 management studio 选择 jsj 或者 local 作为数据库服务器 直接使用 windows 身份认证 连接数据库 服务器 如图 1 图 1 2 一个控制台可以注册并管理多个数据库服务器 包括网络服务器 如图 2 在 jsj 上面 点右键 选择注册 在服务器名称中选择其他 出现可选择网络服务 器界面 点击网络服 务器 出现如下界面 注意图中有 选择需要连接的 SQL Server 实例 图 2 3 3 需要注意上述出现的几个概念 服务器 实例 数据库 服务器通常是从物理的角度出发 来看的 而实例是从应用的角度来看的 即 每一次安装 SQL Server 2008 时 都会在服务 器上创建一个新的实例 可以认为 一个具有并行计算能力的数据库服务器可以安装多个 实例 因此一般情况下 我 们则认为 一个数据库服务器就可以看做是一个实例 所以 实例名默认为服务器名 当 需要再次安装 SQL Server 2008 时 系统会提示输入实例的名 称 命名实例 因 为这时不能再使用默认实例 如图 3 图 3 受到学校机房条件限制 不能重新安装 SQL Server 2008 有条件的同学 可以回去 自己做一下 4 在图 2 的 jsj 上点击右键 选择属性 可以得到服务器的配置 如图 4 图 4 4 图 4 中 再点击查看连接属性 可以发现当前的实例名 为空表示没有特别 命名实例 取默认值 如图 5 图 5 5 查看图 2 左边的树形控件 不难发现 一个服务器上可以有多个数据库 因此 可以 认为一个服务器可以有多个实例 而在一个实例下面可以有多个 数据库 一个数据库中可 以有多个数据库对象 如表 视图 等 由此可见 完整地 在 SQL Server 2000 中 当 用户需要访问一个数据表时 需要通过指定 实例名 数据库名 用户名 表名才能唯一标 识一个数据表 而在 SQL Server2005 以上版本时 当用户需要访问一个数据表时 则需要 通过指定 实例名 数据库名 构架名 表名才能唯一标识一个数据表 2005 以上版本提 出 了使用构架代替用户名的思想 注意 Oracle 中的实例与 SQL Server 中的定义不 同 Oracle 中并行服务器 上 一个实例可以看做是一个数据库 请仔细实践上述 1 5 体会 SQL Server2008 中服务器 实例 数据库的区 别与联系 5 1 2 认识登录用户与数据库用户 1 在 management studio 的文件菜单中点连接 或者重新启动 management studio 会出 现一个身份认证对话框 如图 6 图 6 SQL Server 提供两种不同的身份认证方式登录到数据库服务器 即 SA 方式 和 Windows 方式 注意登录名与用户名的区别 登录名只是具有连接到 SQLSERVER 的权 限 而没有访问 SQLSERVER 上 数据库的权限 所以要为该登录名在指定的数据库中创建 用户名 使其可以访问 那个数据库中的数据 一个登录名可以在多个数据库中创建用户 名 以使这个登录名能够访问多 个数据库 但是一个登录名在一个数据库中只能创建一个 对应的用户名 我们以 SQL Server 认证方式登录服务器时 实际上是以 SA System Administrator 为登录名登录 而以 Windows 认证方式登录时 实际上是以当前 的 Windows 用户名 administrator 登录 dbo 即 DbOwner 是具有在数据库中执行所有活动的暗示性权限的用户 当 我们以 SA 为登录名 或者以 Windows 认证方式 登录数据库服务器 系统所定 义的用户名就 是 dbo 2 分别以 SA 方式和 Windows administrator 方式登录的数据库服务器 然后查 看数据 库 以及数据库中表的所有者是否均为 dbo SA 用户密码 机房有限 制 因此 以 SA 登录只能自己课后做 3 显然 我们也可以不使用 SA 和 administrator 登录 下面请注册一个登录用户 首 先以 Windows 认证方式登录数据库服务器 然 后在 安全性 的树形控件中 右键点击 登录名 选择新建一个登录名 test1 6 并选取 SQL Server 身份认证 如果选取 Windows 身份认证 则需要指定一个除 了 Administrator 之外的另一个用户 且符合 域 用户 的格式 如图 7 所示 图 7 图 7 中 点击左上角服务器角色和用户映射可以进行相关选取 如图 8 所示 图 8 7 图 8 中 服务器各角色含义如下 Sysadmin 在 SQL Server 中进行任何活动 该角色的 权限跨越所有其它固定服 务器角色 serveradmin 配置服务器范围的设置 setupadmin 添加和删除链接服务器 并执行某些系统存储过程 如 sp serveroption securityadmin 管理服务器登录 processadmin 管理在 SQL Server 实例中运行的进程 dbcreator 创建和改变数据库 diskadmin 管理磁盘文件 bulkadmin 执行 BULK INSERT 语句 图 8 中 用户映射的含义是 为该登录用户在所选的数据库中分别建立一个 数据库用户 且用户名 登录名 而图 7 中 选择默认数据库的含义是 当该登录用户登录到服务器时 默认 连接到哪个数据库中去 4 实践下述操作 a 请建立一个名为 test1 的登录用户 默认为 master 数据库 将 test1 映射到 master 数据库上 用户名为 test1 并映射到 tempdb 数据库上 用户名为 t 查看 master 数据库的用户清单中是否有 test1 tempdb 的用户中 是否有 t 点 击树 形控件中的 数据库 安全性 用户 b 使用 test1 登录到数据库服务器 在 数据库 上点右键 创建一个名为 test1 db 所有者为 test1 的数据库 查看此时 test1 db 数 据库的用户有哪些 c 修改登录用户 test1 的默认数据库为 test1 db 再查看此时 test1 db 数据库的 用户有哪些 是否有 test1 为什么 d 建立一个名为 test2 的登录 用户 默认为 test1 db 数据库 并建立到 test1 db 的映射 用户名为 test2 e 以 test2 登录到数据库服务器 查看 test1 db 数据库的用户中是否有 test2 请实践上述过程 体会登录用户 数据库用户 登录名和数据库用户名的 区别与联系 8 1 2 认识架构 Schema 角色与用户 在 sqlserver2000 中架构就已经存在 当时的架构名 用户名 当我们使用查 询分析器去 查询一个表的时候 一个完整的表的名称应该包括服务器名 数据库 名 用户名 对象名 而 在 sqlserver2005 中一个表的完全限定名称应该为服务器名 数据库名 架构名 对象名 实 现了架构和用户的分离 架构 Schema 是形成单个命名空间的数据库实体的集合 可以将 架构看成一 个存放数据库对象的一个容器 一个用户或者角色 role 可以拥有多个架构 而一个架构只能被一个用户或者一个角色 role 拥有 角色 role 是具有相 同权限的 同一类用户的集合的名称 使用架构的好处 1 多个用户可以通过角色 role 或组 Windows groups 成员关系拥有同一个 架构 在 2005 中 每个数据库中的固定数据库 角色都有一个属于自己的架构 如果我们创建一个表 给它指定的架构名称为 db ddladmin 那么任何一个属于 db ddladmin 中的用户都是可以去查询 修改和删除属 于这个架构中的表 但是 其它不属于这个组的用户是不行的 2 删除数据库用户变得极为简单 在 SQL Server 2000 中 用户 User 和架 构是隐含 关联的 即每个用户拥有与其同名的架构 因此要删除一个用户 必须 先删除或修改这个 用户所拥有的所有数据库对象 在 SQL Server 2005 将架构和 对象者分离后就不在存在这 样的问题 删除用户的时候 只需要将用户的架构进 行转移 这样数据库中的对象会跟着 转移 数据库的对象不需要删除 3 在架构和架构所包含的对象上设置权限 permissions 比以前的版本拥有更 高的可管 理性 例如 大量修改数据库对象的权限变得容易 在 SQL Server2000 中 修改表 视图 等数据库对象的权限 需要一个一个的操作 而是用架构的方 式可以一次将架构的所有权 进行转换 4 共享缺省架构使得开发人员可以为特定的应用程序创建特定的架构来存放 对 象 这比仅使用管理员架构 DBO schema 要好 5 区分不同业务处理需要的对象 例如 我们可以把公共的表设置成 pub 的架 构 把销售相关的设置为 sales 这样管理 和访问起来更容易 实验 1 在 test1 db 数据库中 请直接使用 management studio 创建一个名为 test1 和 test2 的架构 所有者均为 test2 用户 2 在 test1 db 数据库中 请为 db ddladmin 角色创建两个不同的架构 db1 和 db2 3 在 test1 db 数据库中 创建一个表 table2 将其所属架构分别改为 db1 和 test2 图 9 4 将 test2 用户加入到 db ddladmin 角色中 图 10 5 请删除 test2 用户 结 论 当 test2 用户在数据库中单独拥有架构 test2 时 test2 用户不能被删除 必须先删 除或者转移 test2 的架构 但是 当 test2 用户作为 db ddladmin 的角 色成员拥有架构 db1 时 删除 test2 用户 不需要删除或者变更 db ddladmin 角 色的架构 9 图 9 图 10 请仔细实践上述过程 体会架构 用户与角色的区别与联系 10 实验 2 3 SQL 语言的使用 在实验 1 中 我们认识了 SQL Server 2008 的基本框架 由于机房电脑具有 硬盘保护卡 每次重新启动之后就会将硬盘还原到初始状态 因此新建 的登录名 将无法保存使用 1 我们直接以 windows 认证方式登录到数据库服务器 并创建一个名为 teach 的 数据库 所有者取默认值 选择路径到移动 USB 盘上 图 11 图 10 2 在电脑重启后 或者下一次来上机时 右键点击 数据库 选择附加 在弹 出 的窗口中将 U 盘上的数据文件选中 即可导入上次存盘的数据库数据 进行 进一步的实 验操作 3 在 management studio 中 直接用鼠标操作 在 teach 数据库中 建立 student 表 course 表和 sc 表 并符合相关的完整性约束条件 三个表的定义 参见教 材例 5 例 6 例 7 4 直接用鼠标操作 删除 student 表 course 表和 sc 表 5 在 teach 上点右键 选择新建查询 在弹出的 SQL 编辑窗口中 分别输入 教材的 例 5 例 7 按下 F5 或者点击 运行 建立 student 表 course 表和 sc 表 查看表 的完整性约束条件 与 3 中的对比 11 6 根据教材第 3 2 节图 3 2 所示的内容 在 student 表 course 表和 sc 表中输入数 据 7 在 teach 上点右键 选择新建查询 在弹出的 SQL 编辑窗口中 输入 SQL 语句 再按下 F5 或者点击 运行 请实验教材第 3 4 节的例子 例 4 例 45 8 按照 7 的方法 请实验教材第 3 4 节的例子例 46 例 47 如果例 46 的结果为空 请在相应的表中添加数据 使之非空 如果例 47 的结果为空 请在相应的表中添加数据 使之非空 9 请使用例 46 的方法 对例 47 进行改造 老师课堂上讲的 2 种方法 10 请实验例 48 例 52 11 请实验教材 3 5 节的例 1 例 10 12 请实验教材 3 6 节的例 1 例 14 13 请使用附加的方式 将 spj 数据库导入到数据库服务器中 并完成教材第二 章习题 5 的如下查询 求至少用了供应商 S1 所供应的全部零件的工程号 JNO spj 数据库文件 请从公共邮箱 csucomputer2008 下载 邮箱密码为 123456 实验 3 完成后 请提交实验报告 内容为 请写出 13 的 SQL 语句 以及查询结果 放到一个 word 文档中 以班级 学号 姓名为文件名 如张三同学 0902 班 011110101 张三 在 1 周以前 发送到 老师的邮箱 yhxiong 12 实验 4 存储过程和触发器 1 请在 teach 数据库中 使用 Transact SQL 代码编写创建如下的存