2022年数据库应用实验教程.docx

精选学习资料 - - - - - - - - - 试验 1 通过 E-R 图设计关系数据模型【试验目的】1）熟识 E-R 模型的基本概念和图形的表示方法；2）把握将现实世界的事物转化成 E-R 图的基本技巧；3）熟识关系数据模型的基本概念；4）把握将 E-R 图转化成关系表的基本技巧；【试验环境】Sql server 2005 【试验重点及难点】1）依据需求确定实体、属性和联系；2）将实体、属性和联系转化为 E-R 图；3）将 E-R 图转化为表；【试验内容】（1） 为体育部门建立的数据库中要储备运动队、运动员、运动项目以及运动员参与运动队和运动项目竞赛情形,其中运动队、 运动员、 运动项目应包含 如下信息：运动队：队名、主教练；队名唯独标识运动队 运动员：运动员编号、姓名、性别、年龄,运动员编号唯独标识运动 员 运动项目：项目编号、项目名、所属类别、项目编号唯独标识项目 规定每个运动队有多名运动员, 每名运动员只属于一个运动队； 每名运动 员可以参与多个项目, 每个项目可以有多个运动员参与；系统记录每名运 动员参与每个项目所得名次和成果以及竞赛日期；请依据以上表达,为体育部门建立 E-R 模型；1）确定运动队实体、运动员实体、运动项目实体的属性和码；2）确定实体之间的联系,给联系命名并指出联系的类型；3）确定联系本身的属性；4）画出运动队、运动员、运动项目关系的 E-R 图；5）将 E-R 图转化为表,写出表的关系模式并标明各自的主键或外键；名师归纳总结 - - - - - - -第 1 页,共 19 页精选学习资料 - - - - - - - - - 试验 2 确定表中的关键字【试验目的】1）正确懂得候选键、主键、组合键、外键的基本概念；2）能够正确判定给定表中各种类型的键；3）在设计数据库时能正确指定各种类型的键,知道如何实施数据的完整性；【试验环境】Sql server 2005 【试验重点及难点】1）复习候选键、主键、外键、组合键以及数据完整性的基本概念；2）在给出的部门表和员工表中能正确标识出各种类型的键；【试验内容】1）写出候选键、主键、组合键、外键、实体完整性、域完整性、参照完整性的 定义；2）已知部门表和员工表如下所示：部门表部门代码部门名负责人地点0001 生产部李华江重庆荣昌县0002 销售部张丽重庆渝中区0003 市场部王欣重庆江北区员工表员工代码姓名家庭住址联系电话邮政编码部门代码王华重庆0001 李想成都0003 张丽上海0002 李江华重庆0001 3）确定部门表和员工表中的候选键,并陈述理由；4）在候选键中确定部门表和员工表的主键；5）确定部门表和员工表中的共有属性；6）指出哪个表中的哪个属性时外键；7）确定哪个表是主表,哪个表是从表；8）回答疑题：部门表和员工表是如何通过关键字实施数据完整性的？名师归纳总结 - - - - - - -第 2 页,共 19 页精选学习资料 - - - - - - - - - 试验 3 服务器的启动、暂停和停止【试验目的】1） 熟识 SQL Server 2005 配置治理器；2） 把握服务器的启动方法；3） 把握服务器的暂停方法；4） 把握服务器的停止方法；【试验环境】Sql server 2005 【试验重点及难点】利用 SQL Server配置治理器实现服务器的启动、暂停和停止；【试验内容】实训 1 服务器治理启动,暂停和停止服务的方法许多,这里主要介绍 器完成这些操作,其操作步骤如下：SQL Server 配置治理1）单击“ 开头” -“ Microsoft SQL Server 2005” -“ 配置工具” ,挑选“ SQL Server Configuration Manager” ,打开 SQL Setver 配置治理器,如图 2-52 所示；单击“SQL Server 2005服务” 选项,在右边的对话框里可以看到 本地全部的 SQL Server服务,包括不同实例的服务,如图 2-53 所示；2）假如要启动、停止、暂停SQL Server 服务的话,鼠标指向服务名称,单击右键,在弹出的快捷键菜单里挑选“ 启动”、“ 停止” 、“ 暂停” 即可；实训 2 服务器注册服务器注册主要为注册本地或者远程 SQL Server 服务器；打开 SQL Server 2005下 Management Studio,进行服务器注册；注册步骤如下：图 2-52 SQL Server 配置服务治理器（一）名师归纳总结 - - - - - - -第 3 页,共 19 页精选学习资料 - - - - - - - - - 图 2-53 SQL Server 配置治理器（二）1）在视图菜单中单击 “ 已注册的服务器”如图 2-54 所示；菜单项项显示出已注册的服务器,图 2-54 已注册服务器 2）在右上角已注册的服务器中,挑选注册类型进行相应服务类型注册；名师归纳总结 3）在选定的服务类型的树型架构的根部单击鼠标右键,挑选“ 新建” 菜单第 4 页,共 19 页下面的“ 服务器组” 进行组的建立,如图2-55 所示；- - - - - - -精选学习资料 - - - - - - - - - 图 2-55 挑选服务器组菜单4）输入服务器组名称,单击储存即可,如图 2-56 所示；图 2-56 新建服务器组5）在新建的服务器组下面注册服务器,在新建服务器节点处单击鼠标右 键,弹出菜单,挑选新建选项下面的服务器注册选项,进行服务器注册,如图 2-57 所示；填写服务器名称, 挑选相应的认证方式, 输入用户名及密码,完成注册；名师归纳总结 - - - - - - -第 5 页,共 19 页精选学习资料 - - - - - - - - - 图 2-57 新建服务器注册名师归纳总结 - - - - - - -第 6 页,共 19 页精选学习资料 - - - - - - - - - 试验 4 创建并治理数据库【试验目的】1）熟识 SQL Server Management Studio 窗口；2）把握创建数据库、数据表的方法；3）把握数据输入和修改的操作【试验环境】Sql server 2005 【试验内容】1）打开“SQL Server Management Studio” 窗口,在“ 对象资源治理器” 中展 开服务器,鼠标右键单击“ 数据库” 节点,单击“ 新建数据库” 命令,会显现“ 新建数据库” 对话框；2）在对话框的“ 数据库名称” 框内输入数据库名“按钮即可创建默认大小的数据库；XSCJ” 后,单击“ 确定”3）在 xscj 数据库中分别创建同学情形表 并表结构分别如下所示：xsqk,课程表 kc,同学与课程表 xs_kc,同学情形表 xsqk：列名数据类型长度属性默认值标识符约束学号Char 6 是否答应主键为空置姓名Char 8 否无唯独否无性别Bit 1 否1 0 或 1 诞生日期Smalldateti4 否无6 为数字me 所在系10 否无Char 专业名Char 10 否无联系电话Char 11 是无总学分Tinyint 1 是无0200 备注Text 30 是无课程表 kc 列名数据类型长度属性默认值标识列约束是否答应为空值序号Int 4 初 始 值增量为 1 名师归纳总结 课程号Char 3 否无主键第 7 页,共 19 页课程名Char 20 否无只能为 16 授课老师Char 8 无否开课学期Tinyint 1 1 学时Tinyint 1 无- - - - - - -精选学习资料 - - - - - - - - - 学分Tinyint 1 无同学与课程表 xs_kc 列名数据类型长度属性默认值约束是否答应为空学号Char 6 否无外键,参组合为主照 XSQK键表课程号Char 3 否无外键,参照 cK 表成果Tinyint 1 无0100 学分Tinyint 1 无4）分别向 XSQK, KC 以下试验内容选作：XS_KC 表中输入数据,其内容由用户自定义1）打开“SQL server Management Studio”窗口,单击在 标准” 工具栏中“ 新建查询” 按钮,会显现 SQL 编辑器界面2）在“SQL 编辑器” 工具栏中,单击“ 可用数据库” 右边的下拉按钮,将当前数据库切换成 ”XSCJ”库3）在查询窗口中,输入如下的命令CREATE TABLE XSQK 学号 CHAR6 NOT NULL, 姓名 CHAR8 NOT NULL, 性别 BIT NOT NULL DEFAULT 1, 生日 SMALLdATETIME NOT NULL, 专业 CHAR10 NOT NULL, 所在系 CHAR10 NOT NULL, 联系电话 CHAR11, 总学分 TINYINT, 备注 TEXT, CONSTRAINT PK_XSQK_XH PRIMARy KEY 学号, CONSTRAINT UQ_XSQK_DH UNIQUE 姓名 , CONSTRAINT CK_XSQK_XB CHECK 姓名=1 OR 姓名 =0, CONSTRAINT CK_XSQK_DH CHECK 联 系 电 话 LIKE '1-91-91-91-91-91-9', CONSTRAINT CK_XSQK_ZXF CHECK 总学分 <=0 AND 总学分 <=200 GO CREATETABLE KC PRIMARYKEY 课程号 , 序号INT, 课程号 CHAR 3NOT NULL课程名 CHAR 20 NOT NULL, 授课老师 CHAR 8 , 名师归纳总结 - - - - - - -第 8 页,共 19 页精选学习资料 - - - - - - - - - 开课学期 TINYINTNOT NULLDEFAULT 1 , 学时 TINYINT, NOT NULL, 学分 TINYINTCONSTRAINT CK_KC_XQ GO CREATE TABLE XS_KE CHECK 开课学期 >= 1 AND 开课学期 <= 6 学号 CHAR 6 NOT NULL REFERENCES XSQK 学号 , 课程号 CHAR 3 NOT NULL, 成果 TINYINT CHECK 成果 >= 0 AND 成果 <= 100 , 学分 TINYINT , PRIMARY KEY 学号 , 课程号 , FOREIGN KEY 课程号 REFERENCES Kc 课程号 4）在“SQL 编辑器 工具栏中,单击” 执行上述结果窗口会显现胜利提示；SQL 命令；创建胜利后,在5）在“ 对象资源治理器“ 中绽开数据库“XSCJ” 鼠标右键单击“ 表” 节点,在弹出的快捷菜单中单击” 刷新“ 命令,可看到创建好的 3 张表；6）将创建好的三张表的表结构截图到试验报告中试验 5 规范化数据【试验目的】1）明白函数（数据）依靠的基本概念；2）能正确判定某一关系属于第几范式；3）把握规范化数据的方法；【试验环境】Sql server 2005 【试验重点及难点】1）复习函数（数据）依靠、数据规范化、范式的基本概念以及各级范式的判别 标准；2）判定给定的表满意哪级别范式的条件；3）将给定的表转换成特定等级的范式条件的表；【试验内容】1）写出函数（数据）依靠、数据规范化、范式的定义以及各级范式的判别标准；2）项目表 1,判定是否满意第一范式的条件并说明理由；项目表 1 项目代码职员代码部门累计工作时间P27 E101 系统集成部90 P51 101 P20 E305 销售部60 P27 109 P22 98 P51 E508 行政办公室NULL P27 72 3）项目表 2,判定是否满意其次范式的条件并说明理由；名师归纳总结 - - - - - - -第 9 页,共 19 页精选学习资料 - - - - - - - - - 项目表 2 项目代码职员代码部门累计工作时间P27 E101 系统集成部90 P51 E101 系统集成部101 P20 E101 系统集成部60 P27 E305 销售部109 P22 E305 销售部98 P51 E508 行政办公室NULL P27 E508 行政办公室72 4）职员表,判定其是否满意第三范式的条件并说明理由；职员表职员代码部门部门负责人代码E101 系统集成部E901 E305 财务部E309 E402 销售部E909 E508 行政办公室E908 E607 财务部E909 E608 财务部E909 5）项目表 3,判定其是否满意 项目表 3 BCNF 的条件并说明理由；项目代码职员代码职员姓名累计工作时间P2 E1 李华玉48 P5 E2 陈家伟100 P6 E3 张勤15 P3 E4 谢成全250 P5 E4 谢成全75 P5 E1 李华玉40 6）将项目表 1 转换成满意第一范式条件的表；7）将项目表 2 转换成满意其次范式条件的表；8）将职员表转换成满意第三范式条件的表；9）将项目表 3 转换成满意 BCNF 条件的表；名师归纳总结 10）回答疑题：规范化数据带来的不利影响是什么？第 10 页,共 19 页- - - - - - -精选学习资料 - - - - - - - - - 试验 6 用 T-SQL创建数据库表【试验目的】4）熟识 SQL Server Management Studio 窗口；5）把握创建数据库、数据表的方法；6）把握数据输入和修改的操作【试验环境】Sql server 2005 【试验内容】1） 打开“ SQL Server Management Studio” 窗口,在“ 对象资源治理器” 中绽开服务器,鼠标右键单击“ 数据库” 节点,单击“ 新建数据库” 命令,会显现“ 新建数据库” 对话框；2） 在对话框的“ 数据库名称” 框内输入数据库名“钮即可创建默认大小的数据库；XSCJ” 后,单击“ 确定” 按3） 打开“SQL server Management Studio”窗口,单击在 标准” 工具栏中“ 新建 查询” 按钮,会显现 SQL 编辑器界面 4） 在“ SQL 编辑器” 工具栏中,单击“ 可用数据库” 右边的下拉按钮,将当前 数据库切换成 ”XSCJ”库或者使用命令 USE XSQK 5） 在查询窗口中,输入如下的命令 CREATE TABLE XSQK 学号 CHAR6 NOT NULL, 姓名 CHAR8 NOT NULL, 性别 BIT NOT NULL DEFAULT 1, 生日 SMALLdATETIME NOT NULL, 专业 CHAR10 NOT NULL, 所在系 CHAR10 NOT NULL, 联系电话 CHAR11, 总学分 TINYINT, 备注 TEXT, CONSTRAINT PK_XSQK_XH PRIMARy KEY 学号, 话LIKE CONSTRAINT UQ_XSQK_DH UNIQUE 姓名 , CONSTRAINT CK_XSQK_XB CHECK 姓名=1 OR 姓名 =0, CONSTRAINT CK_XSQK_DH CHECK联系电'1-91-91-91-91-91-9', CONSTRAINT CK_XSQK_ZXF CHECK 总学分 <=0 AND 总学分 <=200 GO CREATETABLE KC PRIMARYKEY 课程号 , 序号INT, 课程号 CHAR 3NOT NULL课程名 CHAR 20 NOT NULL, 授课老师 CHAR 8 , 名师归纳总结 开课学期 TINYINTNOT NULLDEFAULT 1 , 第 11 页,共 19 页学时 TINYINTNOT NULL, 学分 TINYINT, CONSTRAINT CK_KC_XQ CHECK 开课学期 >= 1 AND 开课学期 <= 6 - - - - - - -精选学习资料 - - - - - - - - - GO CREATE TABLE XS_KE 学号 CHAR 6 NOT NULL REFERENCES XSQK 学号 , 课程号 CHAR 3 NOT NULL, 成果 TINYINT CHECK 成果 >= 0 AND 成果 <= 100 , 学分 TINYINT , PRIMARY KEY 学号 , 课程号 , FOREIGN KEY 课程号 REFERENCES Kc 课程号 6）在“SQL 编辑器 工具栏中,单击” 执行上述 SQL 命令；创建胜利后,在结果窗口会显现胜利提示；名师归纳总结 7）在“ 对象资源治理器“ 中绽开数据库“XSCJ” 鼠标右键单击“ 表” 节点,第 12 页,共 19 页在弹出的快捷菜单中单击” 刷新“ 命令,可看到创建好的3 张表；- - - - - - -精选学习资料 - - - - - - - - - 试验 7 使用 Select 语句查询数据（一）简洁查询【试验目的】把握 SELECT 语句的使用和简洁查询方法；【试验环境】Sql server 2005 【试验重点及难点】1）启动 SQL Server 2005查询环境；2）涉及单表的简洁查询；【试验内容】1 打开“SQL Server Management Studio” 窗口；2 单击“ 标准” 工具栏的“ 新建查询” 按钮,打开“ 查询编辑器” 窗口 3 在窗口中输入以下 SQL 查询命令并执行：a.在 KC 表中,查询第 2 学期开课的课程、授课老师 select 课程名 , 授课老师 , 开课学期 kc from where 开课学期 =2 b.在 XSQK 表中,查询女同学的姓名和电话号码 select 姓名, 性别 , 联系电话 from xsqk where 性别=0 c.在 XS_KC 表中,查询成果在 select 学号, 课程号 , 成果 from xs_kc where 成果>=80 80 分以上的学号、课程号和成果d.在 XS_KC 表中,查询在 80 以上和不及格同学的信息 select 学号, 课程号 , 成果 from xs_kc where 成果>=80 or 成果 <60 e.在 XSQK 表中,查询不在 1980 年 7、8、9 月诞生的同学信息select 学号, 姓名 , 诞生日期1980-07-07and 1980-09-30from xsqk where 诞生日期not between f. 在 XSQK 表中,擦汗寻陈姓且单名的信息 select * from xsqk where 姓名 like 陈 _g.在 XSQK 表中,查询学号中含有 1 的记录信息 select * from xsqk where 学号 like %1%h.在 XSQK 表中,查询电话号码第 select * from xsqk 7 位为 4 和 6 的记录信息名师归纳总结 where 联系电话like _ _ _ _ _ _ 46 _第 13 页,共 19 页- - - - - - -精选学习资料 - - - - - - - - - i.在 KC 表中,查询第一、三、五学期开设的课程信息 select * from kc where 开课学期 in 1, 3, 5 j.查询 XSQK 表,输出学号、姓名、诞生日期、并使查询结构按诞生日期升序排 列select 学号 , 姓名, 诞生日期 from xsqk order by 诞生日期名师归纳总结 - - - - - - -第 14 页,共 19 页精选学习资料 - - - - - - - - - 试验 8 使用 Select 语句查询数据（二）汇总查询【试验目的】把握数据汇总查询及其相关子句的使用；【试验环境】Sql server 2005 【试验重点及难点】1）启动 SQL Server 2005查询环境；2）涉及单表的汇总查询；【试验内容】1 打开“SQL Server Management Studio” 窗口；2 单击“ 标准” 工具栏的“ 新建查询” 按钮,打开“ 查询编辑器” 窗口 3在窗口中输入以下 SQL 查询命令并执行：a.在 KC 表中,统计每学期的总分数 select 开课学期 , sum学分 as 各学期的学分合计 kc from group by 开课学期 b.在 XS_KC 表中统计每个同学的选修课程的门数 select 学号, count* as 每个同学选修的课程门数 from xs_kc group by 学号 c.统计 KC 表中的总学分,并显示明细信息 select 课程名称 , 开课学期 , 学分 from kc compute sum学分 d.按开课学期统计 KC 表中各期的学分,并显示明细信息 select 课程名称 , 开课学期 , 学分 kc from order by 2 compute sum学分 by 开课学期 e.将 XS_KC 表中的数据记录按学号分类汇总,输出学号和平均分 select 学号 , avg成果 as 同学的平均分 from xs_kc group by 学号 f.查询平均分大于 70 且小于 80 的同学学号和平均分 select 学号, avg成果 as 同学的平均分 from xs_kc group by 学号 having avg成果 between 70 and 80 g.查询 XS_KC 表,输出学号、课程号、成果,并使查询结果第一依据课程号的 升序排列,当课程号相同时再依据成果降序排列,并将查询结果储存到新表TEMP_KC 中 select 学号, 课程号 , 成果名师归纳总结 into temp_kc 第 15 页,共 19 页from xs_kc - - - - - - -精选学习资料 - - - - - - - - - order by 课程号 , 成果 desc h.查询选修了“101” 课程的同学的最高分和最低分 select max成果 as 101课程的最高分 , min成果 as 101课程的最低分 from xs_kc where 课程号 =101i.统计每个学期所开设的课程门数 select 开课学期 , count* as 每学期开设的课程门数 from kc group by 开课学期 j.查询各专业的同学人数 select 专业名 , count专业名 as 各专业人数 from xsqk group by 专业名名师归纳总结 - - - - - - -第 16 页,共 19 页精选学习资料 - - - - - - - - - 试验 9 使用 Select 语句查询数据（三）连接查询和子查询【试验目的】）把握内连接的查询方法；）明白子查询的查询方法；【试验环境】Sql server 2005 【试验重点及难点】1）启动 SQL Server 2005查询环境；）涉及多表的复杂查询；【试验内容】1 打开“SQL Server Management Studio” 窗口；2 单击“ 标准” 工具栏的“ 新建查询” 按钮,打开“ 查询编辑器” 窗口 3在窗口中输入以下 SQL 查询命令并执行 : a.查询不及格同学的学号、课程名、授课老师、开课学期的信息 select 学号, xs_kc.课程号 , 授课老师 , 开课学期 , 成果 from kc, xs_kc where kc.学号 =xs_kc.学号 and 成果 <60 b.按学号分组汇总总分高于100 的同学记录,并按总分的降序排列select 学号, sum成果 as 各同学的总分 from xs_kc group by 学号 having sum成果>=100 order by sum成果 desc c.使用子查询求恰好有两门课程不及格的同学信息 select 学号, 姓名 from xsqk where select count课程号 from xs_kc where xsqk.学号 =xs_kc.学号 and 成果>=60 =2 d.使用子查询查询每门课程的最高分的同学记录 select * from xs_kc a where 成果 =select max成果 from xs_kc b where a.课程号 =b.课程号 e.使用子查询查询每个同学的最低分的课程记录 select * from xs_kc a where 成果 =select min 成果 from xs_kc b where a.学号=b.学号 名师归纳总结 - - - - - - -第 17 页,共 19 页精选学习资料 - - - - - - - - - 试验 10 创建视图并通过视图操作表数据【试验目的】1）把握视图的创建；2）把握使用视图来插入、更新、删除表数据；【试验环境】Sql server 2005 【试验重点及难点】1）启动 SQL2005 查询编辑器；2）创建一个简洁的视图,查询第3 学期及其以后开课的课程信息；3）在视图中使用 INSERT 语句插入数据；4）在视图中使用 UPDATE 语句更新数据；5）在视图中使用 DELETE 语句删除数据；【试验内容】（1）创建视图 1）打开“SQL Server Management Studio”窗口；2）单击：“ 标准” 工具栏上的“ 新建查询” 按钮,打开“ 查询编辑器” 窗口；#在窗口内直接输入以下语句,按要求创建视图；在 XSCJ 数据库中,基于 KC 表创建一个名为 ”v_开课信息 ”的视图,要求该视图中包含列“ 课程号” 、“ 课程名” 、“ 开课学期” 和“ 学时”的行中只包括第 3 学期及以后开课的课程信息；USE XSCJ CREATE VIEW v_开课信息 AS SELECT 课程号,课程名,开课学期,学时 FROM KC WHERE 开课学期 >=3 、并且限定视图中返回4）单击“SQL 编辑器” 工具栏上的“ 分析” 按钮,检查输入的T-SQL 语句是否 v_开课信息” 的有语法错误；假如有语法错误,就进行修改；5）确保无语法错误后,在XSCJ 数据库中就会添加一个名为“视图,通过 SELECT 语句查看视图中的数据,如下列图（2）在视图中使用 INSERT 语句插入数据 1）在“ 查询编辑器” 串口内输入以下语句,在视图中插入一行数据；INSERT INTO v_开课信息 VALUES 022 , ASP ,4,80 2单击 ”SQL 编辑器 ”工具栏上的“ 执行” 按钮；SELECT 语句查看视图中的数据,如下列图；3）执行上述语句后,利用（3）在视图中使用 UPDATE 语句更新数据 1）在“ 查询编辑器” 串口内输入以下语句,修改途中的数据；UPDATE v_开课信息 SET 开课学期 =5,学时 =80 名师归纳总结 WHERE 课程号 =012第 18 页,共 19 页2）单击“SQL 编辑器” 工具栏上的“ 执行” 按钮；3）执行上述语句后,视图中课程号为“012” 的数据记录被修改了,基表中对应数据记录也被修改了；通过SELECT 语句查看视图和基表中的数据,结果如图- - - - - - -精选学习资料 - - - - - - - - - 6-24 所示；（4）在视图中使用 DELETE 语句删除数据1）在“ 查询编辑器” 窗口内输入以下语句,删除视图中的数据；DELETE FROM v_课程信息WHERE 课程号 =0222单击“SQL 编辑器” 工具栏上的“ 执行” 按钮；3）执行上述语句后,从前在视图中添加的数据行被删除；同时,在基表中该数据行也被删除；名师归纳总结 - - - - - - -第 19 页,共 19 页