2022年数据库上机实验指导定义 .pdf

数据库原理实验指导书实验 1 数据库模式设计及建立一、实验目的：(1) 掌握数据库模式设计，依据实际要求设计表结构，建立表的关系; 比较SQL命令方式和可视化环境管理器方式设计的异同点。(2) 掌握SQL Server查询分析器的使用方法，加深对SQL和SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。二、实验内容：1、分别用 sql查询分析器和企业管理器创建表，并设定相应的约束。要求：在学生管理数据库XSGL 中创建如下三个表。创建名为 student(学生信息 )的表，表中的各列要求如下：字段名称字段类型大小说明SnoChar10主键SnameChar8SexChar2默认值为男，只能输入男和女SageIntSdeptChar20创建名为 course（课程信息）的表，表中的各列要求如下：字段名称字段类型大小说明CnoChar10主键CnameChar30唯一性CcreditReal名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 16 页 - - - - - - - - - 创建名为 score（学生成绩）的表，表中的各列要求如下：字段名称字段类型大小取值范围说明SnoChar10数据来自学生信息表主键CnoChar10数据来自课程信息表主键GradeReal0-1002、创建 score student表，以及 score course表的外部键。3、增加、修改和删除字段，要求：1) 给student表增加一个 memo(备注)字段，类型为varchar(200)。2) 将memo字段的类型修改为varchar(300)。3) 删除 memo 字段。4、 简单查询操作。该实验包括投影、选择条件表达，数据排序，使用临时表等。具体完成以下题目，将它们转换为SQL语句表示，在学生选课数据库中实现其数据查询操作。1) 求数学系学生的学号和姓名2) 求选修了课程的学生学号3) 求选修课程号为 C1的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。use student;select Student.Sno,Gradefrom SC,Studentwhere Cno=3 and SC.Sno=Student.Snoorder by Grade asc ,student.Sno desc4) 求选修课程号为 3且成绩在 8090之间的学生学号和成绩，并成绩乘以 0.8输出。use student;select distinct Student.Sno,Grade*0.8名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 16 页 - - - - - - - - - from SC,Studentwhere Cno=3 and student.Sno=SC.Sno andgrade80 and grade804） 查询每一门课程的间接先行课（即先行课的先行课）use student;select a.Cno as 课程号 ,a.Cname as 课程,b.Cname as 先行课 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 16 页 - - - - - - - - - from Course a,Course bwhere a.Cpno=b.Cno注意：如果要是修改为NULL 可按 Ctrl+0三、实验方法将查询需求用 SQL语言表示；在 SQL Server查询分析器的输入区中输入 SQL查询语句；设置查询分析器的结果区为StandardExecute(标准执行 )或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验 2 数据库的复杂查询实验1 实验目的本实验的目的是使学生进一步掌握SQL Server查询分析器的使用方法，加深 SQL语言的嵌套查询语句的理解，熟练掌握数据查询中的分组、统计、计算和集合的操作方法。2 实验内容1、在SQL Server查询分析器中使用 IN、比较符、 ANY 或ALL 和EXISTS操作符进行嵌套查询操作。具体完成以下例题。将它们用SQL语句表示，在学生选课中实现其数据嵌套查询操作。1） 求选修了数据库的学号和姓名use student;select Sname,Cnamefrom SC,Student,Coursewhere SC.Cno=Course.Cno and Student.Sno=SC.Snoand Cname=信息系统 2） 求3课程的成绩高于刘晨的学生学号和成绩use student;select Sname,Student.Sno,Gradefrom SC,Studentwhere Student.Sno=SC.Sno名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 16 页 - - - - - - - - - and Cno=3and Grade(select Gradefrom SC,Studentwhere Student.Sno=SC.snoand Sname like刘晨 and Cno=3)3） 求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）use student;select distinct Sname,Sno,Sdept,Sagefrom Studentwhere SdeptCS and Sageany(select Sagefrom Studentwhere Sdept=CS) and SdeptCS5） 求选修了 C2课程的学生姓名use student;select distinct Student.Snofrom Student,SCwhereexists(select *from SCwhere Student.Sno=SC.Sno and Cno=3)6） 求没有选修 3课程的学生姓名use student;select distinct Student.Snofrom Student,SC名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 16 页 - - - - - - - - - where notexists(select *from SCwhere Student.Sno=SC.Sno and Cno=3)7） 查询选修了全部课程的学生姓名use student;select Snofrom SCgroup by Sno having count(*)=38） 求至少选修了学号为“95002”的学生所选修全部课程的学生学号和姓名use student;select Sname,Snofrom Studentwhere not exists( select * from SC SCY where SCY.Sno=95002 and not exists ( select * from SC SCZ where SCZ.Sno=Student.Sno and SCZ.Cno=SCY.Cno ) )2、进行分组查询，包括分组条件表达、选择组条件表达的方法；进行函数查询，包括统计函数和分组统计函数的使用方法；进行集合查询，包括并操作 UNION 、交操作 INTERSECT 和差操作 MINUS 的使用方法。具体完成以下例题，将它们用SQL语句表示，在学生选课中实现其数据查询操作。1） 求学生的总人数use student;select count(distinct Sno)from Student名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 16 页 - - - - - - - - - 2） 求选修了课程的学生人数use student;select count(distinct Sno)from SC3） 求课程和选修了该课程的学生人数use student;select Cno,count(*)from SCgroup by Cno4） 求选修超过 2门课的学生学号use student;select Cno,count(*)from SCgroup by Cnohaving count(*)25） 查询计算机科学系的学生及年龄不大于20岁的学生use student;select Sno,Snamefrom Studentwhere Sdept=CS and Sage206） 查询计算机科学系的学生与年龄不大于20岁的学生的交集use student;select Sno,Snamefrom Studentwhere Sdept=CS and Sage208） 查询选修课程 C1的学生集合与选修课程C2的学生集合的交集use student;select Snofrom SCwhere Cno=1 and Sno in (select Sno名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 16 页 - - - - - - - - - from SC where Cno=2)9） 查询选修课程 C1的学生集合与选修课程C2的学生集合的差集use student;select student.Sno,Snamefrom SC,Studentwhere student.sno=sc.sno and Cno=1 and sc.Sno not in (select Sno from SC where Cno=2)3 实验步骤将查询需求用 SQL语言表示：在 SQL Server查询分析器的输入区中输入SQL查询语句：设置查询分析器的结果区为Standard Execute（标准执行）或 Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验 3 数据库的更新和视图定义及维护实验一、实验目的本实验的目的是要求学生熟练掌握使用SQL 和通过 SQL Server企业管理器向数据库输入数据、修改数据和删除数据的操作，同时使学生掌握视图的定义与维护操作，加深对视图在关系数据库中的作用的理解。二、实验内容1、具体完成以下例题。将它们转换为SQL语句表示，在学生选课库中实现其数据更新操作。1） 将一新学生记录 (学号： 95050；姓名：陈冬；性别：男；年龄：18；所在系：计算机科学系)插入学生表中。insertinto student (sno,sname,ssex,sdept,sage)values(95007,陈东 ,男,is,18)2） 插入一条选课记录 ( 95020, C1)。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 16 页 - - - - - - - - - insertinto SCvalues(95007,2,NULL)3）对每一个系，求学生的平均年龄，并把结果存入数据库。对于这道题，首先要在数据库中建立一个有两个属性列的新表，其中一列存放系名，另一列存放相应系的学生平均年龄。 CREATE TABLE Deptage （所在系CHAR （20），平均年龄SMALLINT ）;然后对数据库的学生表按所在系分组求平均年龄，再把所在系和平均年龄存入新表中。use student;create table av_age(sdept char(15),age int);insert into av_age(sdept,age)select Sdept,avg(Sage)from studentgroup by Sdept3） 将学生 95001的年龄改为 22岁。use student;update studentset Sage=22where Sno=950014） 将所有学生的年龄增加一岁。use student;update studentset Sage=Sage+15） 将计算机科学系全体学生的成绩置零。use student;update SCset Grade=0where CS=(select Sdeptfrom studentwhere student.Sno=SC.Sno)6） 删除学号为 95006的学生记录名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 16 页 - - - - - - - - - use student;delete from studentwhere Sno=950067） 删除所有的学生选课记录use student;delete from SC8） 删除计算机科学系所有学生的选课记录use studentdelete from SCwhere CS=(select Sdeptfrom studentwhere student.Sno=SC.Sno )2、具体完成以下例题。1） 建立信息系学生的视图create view CS_stuasselect Sname,Sdeptfrom studentwhere Sdept=CS2） 建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。create view IS_stuasselect Sname,Sdeptfrom studentwhere Sdept=ISwith check option3） 建立信息系选修了 C1号课程的学生视图create view IS_studentasselect Student.Sno,Sname,Sdeptfrom student,SCwhere Sdept=IS and student.Sno=SC.Snowith check option4） 建立信息系选修了 C1号课程且成绩在 90分以上的学生视图create view IS_studentas名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 16 页 - - - - - - - - - select Student.Sno,Sname,Sdept,Gradefrom student,SCwhere Sdept=IS and student.Sno=SC.Sno and Grade90with check option5） 定义一个反映学生出生年份的视图create view BIR_stu(Sno,Sname,Sbir)asselect Sno,Sname,2011-Sagefrom student三、实验步骤将查询需求用 SQL语言表示；在 SQL查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为Standard Execute（标准执行）或 Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验四存储过程和触发器的使用一、实验目的：本实验要求学生应了解存储过程和事务的作用，基本掌握存储过程和事务的创建和使用方法，要求学生了解触发器的知识与作用，基本掌握触发器的创建方法。要求学生在已建好的学生管理数据库中创建 3个分别用于插入、删除和更新的触发器。二、实验内容：1、存储过程例题（1）创建一个名为 “proc_1”的存储过程，用于查看学生表的所有信息。然后调用该存储过程。create procedure proc_1asbeginselect * from studentend名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 16 页 - - - - - - - - - exec proc_1（2）创建一个名为 “proc_2”的存储过程，用于向学生表的所有字段添加一条记录，记录内容由调用时决定。然后调用该存储过程。create procedure proc_2(num int,name varchar(20),sexvarchar(20),age int,dept varchar(20)asbegininsert into studentvalues(num,name,sex,age,dept)endexec proc_2 95008,李冰 ,男,19,CS（3）创建一个名为 “proc_3”的存储过程，用于删除学生表中指定学号的记录，具体学号由调用时决定。然后调用该存储过程。create procedure proc_3(num varchar)asbegindeletefrom studentwhere Sno=numendexec proc_3 95008（4）修改存储过程 “proc_4”，用于查询不小于指定成绩的学生的基本信息，具体成绩由调用时决定。create procedure proc_5(grade varchar)as名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 16 页 - - - - - - - - - beginselect snamefrom student,SCwhere SC.Sno=student.Sno and Gradegradeendexec proc_6 502、触发器例题（1） 创建一个名为 “trig_1 ”的触发器，当向学生表添加记录时，该触发器自动显示学生表的所有信息。create trigger trig_1 on studentfor insertasbeginselect * from studentendinsert into studentvalues(95008,李冰 ,男,18,CS)（2） 创建一个名为 “trig_2 ”的触发器，当试图向学生表添加、修改或删除记录时，该触发器自动显示如下信息： “ 对不起，你无权进行更新操作！” 。create trigger trig_2 on studentfor insert,update,deleteasbeginprint 对不起，你无权进行更新操作！roll back名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 16 页 - - - - - - - - - end（3） 创建一个名为 “trig_3 ”的触发器，当向学生表删除记录时，该触发器自动删除成绩表中与之相关的所有记录。create trigger trig_3 on studentfor deleteasbegindeletefrom SCwhere Sno in(select Snofrom deleted)end（4）创建一个名为 “trig_4 ”的触发器，当向成绩表添加记录时，该触发器自动显示与该记录相关的学生的学号、姓名和班级。实验五安全管理一、实验目的本实验要求学生掌握SQL Server2000 三级安全体系及验证模式的设置，掌握SQL Server2000 服务器登录帐号及数据库用户帐号的管理，掌握创建用户自定义数据库角色并为其授权，掌握为数据库用户授权的方法二、实验内容1、 创建 SQL Server 服务器登录账号操作步骤：展开 SQL Server 服务器，右击安全性节点下名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 16 页 - - - - - - - - - 的“ 登录 ” ，选择 “ 新建登录 “ ，在弹出的 “ 登录属性 ” 对话框中，选择一个 windows 用户，单击 “ 确定 ” 按钮，就可以创建一个登录账号。2、 创建数据库用户：为“ 学生选课 ” 数据库添加用户:zhang 。操作步骤：选择 “ 学生选课 ” 数据库，右击 “ 用户” 节点，选择“ 新建数据库用户” ，在弹出的 “ 数据库用户属性 新建用户” 对话框中，在 “ 登录名 ” 右边的下拉列表框中选择一个登录名，可以在 “ 用户名 ” 后的文本框输入数据库用户名。数据库用户名可以和选中的登录名相同，也可以不同。3、 创建数据库角色：为“ 学生选课 ” 数据库创建一用户自定义数据库角色s_c_reader, 该角色的权限是可以查询学生表及选课成绩表的信息。操作步骤：展开 “ 学生选课 ” 数据库，右击 “ 角色” 节点，选择“ 新建数据库角色” ，弹出的 “ 数据库用户角色 新建角色” 对话框，在 “ 名称 ” 下面的文本框输入数据库角色的名称，单击 “ 确定 ” 按钮即可。可以将数据库用户添加到数据库角色中。为数据库角色授权：选中一个具体的数据库角色，单击鼠标右键，选择 “ 属性 ” ，在弹出的 “ 数据库角色属性 ” 对话框中，单击 “ 权限 ” 按钮，在弹出的对话框中设置数据库角色的权限。4、 数据库对象操作权限的授予和回收：为数据库用户zhang 授予学生表的增、删、改、查权限。操作步骤：选择 “ 学生选课 ” 数据库中的 “ 表” 节点，选择 “ 学名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 16 页 - - - - - - - - - 生” 表，单击鼠标右键，选择“ 所有任务 ” 中的 “ 管理权限 ” 命令，在弹出的对话框中设置各个数据库用户的操作权限。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 16 页 - - - - - - - - -