数据库上机实验指导.docx

1、数据库上机实验指导数据库原理实验指导书实验1 数据库模式设计及建立一、实验目的： (1) 掌握数据库模式设计，依据实际要求设计表结构，建立表的关系; 比较SQL命令方式和可视化环境管理器方式设计的异同点。(2) 掌握SQL Server查询分析器的使用方法，加深对SQL和SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。二、实验内容： 1、分别用sql查询分析器和企业管理器创建表，并设定相应的约束。要求：在学生管理数据库XSGL中创建如下三个表。创建名为student(学生信息)的表，表中的各列要求如下：字段名称字段类型大小说明SnoChar10主键Sna

2、meChar8SexChar2默认值为男，只能输入男和女SageIntSdeptChar20创建名为course（课程信息）的表，表中的各列要求如下：字段名称字段类型大小说明CnoChar10主键CnameChar30唯一性CcreditReal创建名为score（学生成绩）的表，表中的各列要求如下：字段名称字段类型大小取值范围说明SnoChar10数据来自学生信息表主键CnoChar10数据来自课程信息表主键GradeReal0-1002、创建score student表，以及score course表的外部键。3、增加、修改和删除字段，要求：1) 给student表增加一个memo(备注)

3、字段，类型为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=Stude

4、nt.Snoorder by Grade asc ,student.Sno desc4) 求选修课程号为3且成绩在8090之间的学生学号和成绩，并成绩乘以0.8输出。use student;select distinct Student.Sno,Grade*0.8from SC,Studentwhere Cno=3 and student.Sno=SC.Sno and grade80 and grade804） 查询每一门课程的间接先行课（即先行课的先行课）use student;select a.Cno as 课程号,a.Cname as 课程,b.Cname as 先行课from Cour

5、se a,Course bwhere a.Cpno=b.Cno注意：如果要是修改为NULL 可按Ctrl+0三、实验方法将查询需求用SQL语言表示；在SQL Server查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为Standard Execute(标准执行)或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验2 数据库的复杂查询实验一 实验目的本实验的目的是使学生进一步掌握SQL Server查询分析器的使用方法，加深SQL语言的嵌套查询语句的理解，熟练掌握数据查询中的分组、统计、计算和集合

6、的操作方法。二 实验内容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,Gradefro

7、m SC,Studentwhere Student.Sno=SC.Snoand Cno=3and Grade(select Grade from 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)

8、 and SdeptCS5） 求选修了C2课程的学生姓名use student;select distinct Student.Snofrom Student,SCwhere exists(select *from SCwhere Student.Sno=SC.Sno and Cno=3)6） 求没有选修3课程的学生姓名use student;select distinct Student.Snofrom Student,SCwhere notexists(select *from SCwhere Student.Sno=SC.Sno and Cno=3)7） 查询选修了全部课程的学生姓名us

9、e 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、进行分组查询，包括分组条件表达、选择

10、组条件表达的方法；进行函数查询，包括统计函数和分组统计函数的使用方法；进行集合查询，包括并操作UNION、交操作INTERSECT和差操作MINUS的使用方法。具体完成以下例题，将它们用SQL语句表示，在学生选课中实现其数据查询操作。1） 求学生的总人数use student;select count(distinct Sno) from Student2） 求选修了课程的学生人数use student;select count(distinct Sno) from SC3） 求课程和选修了该课程的学生人数use student;select Cno,count(*) from SCgroup

11、 by Cno4） 求选修超过2门课的学生学号use student;select Cno,count(*) from SCgroup by Cno having 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的

12、学生集合的交集use student;select Snofrom SCwhere Cno=1 and Sno in (select Sno 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)三 实验步骤 将查询需求用SQL语言表示：在SQL Server查询分析器的输入区中

13、输入SQL查询语句：设置查询分析器的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。 实验3 数据库的更新和视图定义及维护实验一、实验目的本实验的目的是要求学生熟练掌握使用SQL 和通过SQL Server企业管理器向数据库输入数据、修改数据和删除数据的操作，同时使学生掌握视图的定义与维护操作，加深对视图在关系数据库中的作用的理解。 二、实验内容1、具体完成以下例题。将它们转换为SQL语句表示，在学生选课库中实现其数据更新操作。1） 将一新学生记录(学号：95

14、050；姓名：陈冬；性别：男；年龄：18；所在系：计算机科学系)插入学生表中。insert into student (sno,sname,ssex,sdept,sage)values(95007,陈东,男,is,18)2） 插入一条选课记录(95020,C1)。insert into SC values(95007,2,NULL)3） 对每一个系，求学生的平均年龄，并把结果存入数据库。对于这道题，首先要在数据库中建立一个有两个属性列的新表，其中一列存放系名，另一列存放相应系的学生平均年龄。 CREATE TABLE Deptage（所在系 CHAR（20），平均年龄SMALLINT）; 然后

15、对数据库的学生表按所在系分组求平均年龄，再把所在系和平均年龄存入新表中。 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+1

16、5） 将计算机科学系全体学生的成绩置零。use student;update SCset Grade=0where CS=(select Sdeptfrom studentwhere student.Sno=SC.Sno)6） 删除学号为95006的学生记录use student;delete from studentwhere Sno=950067） 删除所有的学生选课记录use student;delete from SC8） 删除计算机科学系所有学生的选课记录use studentdelete from SCwhere CS=(select Sdeptfrom studentwhere

17、student.Sno=SC.Sno )2、具体完成以下例题。1） 建立信息系学生的视图create view CS_stuas select Sname,Sdeptfrom studentwhere Sdept=CS2） 建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。create view IS_stuasselect Sname,Sdeptfrom studentwhere Sdept=ISwith check option3） 建立信息系选修了C1号课程的学生视图create view IS_studentasselect Student.Sno,Sna

18、me,Sdeptfrom student,SCwhere Sdept=IS and student.Sno=SC.Snowith check option4） 建立信息系选修了C1号课程且成绩在90分以上的学生视图create view IS_studentasselect 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

19、)asselect Sno,Sname,2011-Sagefrom student三、实验步骤将查询需求用SQL语言表示；在SQL查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验四 存储过程和触发器的使用一、实验目的：本实验要求学生应了解存储过程和事务的作用，基本掌握存储过程和事务的创建和使用方法，要求学生了解触发器的知识与作用，基本掌握触发器的创建方法。要求学生在已建好的学生管理数据库中创建3个分别

20、用于插入、删除和更新的触发器。二、实验内容：1、存储过程例题（1）创建一个名为“proc_1”的存储过程，用于查看学生表的所有信息。然后调用该存储过程。create procedure proc_1as beginselect * from studentendexec proc_1（2）创建一个名为“proc_2”的存储过程，用于向学生表的所有字段添加一条记录，记录内容由调用时决定。然后调用该存储过程。create procedure proc_2(num int,name varchar(20),sex varchar(20),age int,dept varchar(20)asbegin

21、insert 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 p

22、roc_5(grade varchar)asbeginselect snamefrom student,SCwhere SC.Sno=student.Sno and Gradegradeendexec proc_6 502、触发器例题（1） 创建一个名为“trig_1”的触发器，当向学生表添加记录时，该触发器自动显示学生表的所有信息。create trigger trig_1 on studentfor insert asbegin select * from studentendinsert into studentvalues(95008,李冰,男,18,CS)（2） 创建一个名为“tri

23、g_2”的触发器，当试图向学生表添加、修改或删除记录时，该触发器自动显示如下信息：“对不起，你无权进行更新操作！”。create trigger trig_2 on studentfor insert,update,delete asbegin print 对不起，你无权进行更新操作！roll backend（3） 创建一个名为“trig_3”的触发器，当向学生表删除记录时，该触发器自动删除成绩表中与之相关的所有记录。create trigger trig_3 on studentfor delete asbegin delete from SCwhere Sno in(select Snof

24、rom deleted)end（4）创建一个名为“trig_4”的触发器，当向成绩表添加记录时，该触发器自动显示与该记录相关的学生的学号、姓名和班级。实验五 安全管理一、实验目的本实验要求学生掌握SQL Server2000三级安全体系及验证模式的设置，掌握SQL Server2000服务器登录帐号及数据库用户帐号的管理，掌握创建用户自定义数据库角色并为其授权，掌握为数据库用户授权的方法二、实验内容1、 创建SQL Server 服务器登录账号操作步骤：展开SQL Server 服务器，右击安全性节点下的“登录”，选择“新建登录“，在弹出的“登录属性”对话框中，选择一个windows用户，单击

25、“确定”按钮，就可以创建一个登录账号。2、 创建数据库用户：为“学生选课”数据库添加用户:zhang。操作步骤：选择“学生选课”数据库，右击“用户”节点，选择“新建数据库用户”，在弹出的“数据库用户属性新建用户”对话框中，在“登录名”右边的下拉列表框中选择一个登录名，可以在“用户名”后的文本框输入数据库用户名。数据库用户名可以和选中的登录名相同，也可以不同。3、 创建数据库角色：为“学生选课”数据库创建一用户自定义数据库角色s_c_reader,该角色的权限是可以查询学生表及选课成绩表的信息。操作步骤： 展开“学生选课”数据库，右击“角色”节点，选择“新建数据库角色”，弹出的“数据库用户角色新建角色”对话框，在“名称”下面的文本框输入数据库角色的名称，单击“确定”按钮即可。 可以将数据库用户添加到数据库角色中。 为数据库角色授权：选中一个具体的数据库角色，单击鼠标右键，选择“属性”，在弹出的“数据库角色属性”对话框中，单击“权限”按钮，在弹出的对话框中设置数据库角色的权限。4、 数据库对象操作权限的授予和回收：为数据库用户zhang 授予学生表的增、删、改、查权限。操作步骤： 选择“学生选课”数据库中的“表”节点，选择“学生”表，单击鼠标右键，选择“所有任务”中的“管理权限”命令，在弹出的对话框中设置各个数据库用户的操作权限。