数据库实验 上机.doc

实验二 利用查询分析器查询（一） 从SC数据库表中求出学号为“110803101”同学的平均成绩，如果此平均成绩大于或等于60分，输出“pass”信息。if(select avg(grade) from SC where sno=110800201)=60print passelseprint not pass 查询所有姓刘的学生的基本信息。select sno,sname,age,sexfrom studentwhere sname like 刘% 查询班级号为“1108031”（即11级计算机专业软件一班）的所有学生。select sno,snamefrom studentwhere sno like 1108002% 查询选修了课程名为“数据库系统”的学生学号、姓名。select student.sno,snamefrom student,C,SCwhere student.sno=SC.sno and SC.cno=C.cno and C.cname=数据库系统原理elect sno,sname from studentwhere sno IN( select sno from SC where cno IN( elect cno from Cwhere cname=数据库系统原理 ) ) 求出各班级每门课平均成绩，并按平均成绩降序排列。select Left(sno,7) 班级,cno,avg(grade)平均成绩from SCgroup by Left(sno,7),cnoorder by avg(grade) desc 从学生表中检索所有的信息，要求只显示前6行数据。select top 6 sno,sname,age,sexfrom student 用sp_helpindex查看SC表中的索引信息，如果不存在索引则在(sno,cno)复合列上创建一个唯一索引，否则在grade创建索引。sp_helpindex SCcreate index gname on SC(grade)二2 实行如下要求的查询和操作： 求出有一门课成绩在85分上的学生的学号、姓名select distinct student.sno,snamefrom student,SCwhere grade85 and student.sno=SC.sno 求出各门课程成绩均在85分以上学生的学号、姓名（理解为该生的最小成绩大于等于85） select distinct student.sno,snamefrom studentwhere sno IN( select sno from SCgroup by snohaving min(grade)=85 ) 查询没有学生选修的课程号及课程名称（该生在成绩表里头没有记录）select cno,cnamefrom Cwhere cno not in( select cnofrom SCwhere SC.cno=C.cno ) 查询平均成绩大于85分的学号、姓名、平均成绩select student.sno,sname,avg(grade) 平均成绩from student,SCwhere student.sno=SC.snogroup by student.sno,snamehaving avg(grade)85 查询各门课程取得最高成绩的学生姓名和成绩select sname,gradefrom student,SC awhere student.sno=a.sno and grade=(select max(grade)from SC where SC.cno=o) 检索学习了课程“操作系统”或“数据库系统原理”或“编译原理”其中一门课的学生学号。（用连接和子查询来实现）select distinct snofrom SCwhere cno in(select cnofrom Cwhere SC.cno=C.cno and (cname=操作系统 or cname=数据库系统原理 orcname=编译原理) 检索已经学习课程“操作系统”和 “数据库系统原理”二门课的学生学号。select distinct snofrom SC a,C xwhere o=o and cname=数据库系统原理 and sno in(select snofrom SC b,C ywhere o=o and cname=操作系统 ) 查询至少选修了学生110803101选修的全部课程的学生学号select distinct snofrom SC awhere not exists( select * from sc bwhere b.sno=110800201 andnot exists(select * from SC cwhere c.sno=a.sno and c.sno=b.sno)实验三 利用查询分析器查询（二）利用查询分析器和样本（实验一）中的三个表：STUDENTS、SC及C表建立视图V_grobal，视图V_grobal包含的字段名为：sno、name、o、ame 、 gradecreate view V_grobalasselect student.sno,sname,C.cno,C.cname,gradefrom student,SC,C创建一个名为V_student的视图。该视图仅查看STUDENTS表中” 1108031”班的学生信息。并强制通过视图修改和添加的数据满足where条件。（with check option）create view V_studentasselect sno,sname,age,sexfrom studentwhere sno like 1108002%with check option1)通过视图修改该班的某一学生的姓名。能否成功？ 能2) 通过视图修改该班的某一学生的学号改为” 110803225”，成功与否？得到什么结论？不能，结论：with check option表示对视图进行UPDATE，INSERT和DELETE操作时要保证更新，插入或删除的行满足视图定义中的谓词条件 创建一个名为V_Sgrade的视图，该视图能显示各学生的学号，姓名，平均成绩create view V_Sgrade(sno,sname,Gavg)as select SC.sno,sname,avg(grade)from student,SCwhere student.sno=SC.snogroup by SC.sno,sname问：能不能将某一个学生的平均成绩改成90，为什么？不可以，对视图的更新是无法转换成对基本表SC的更新，因为系统无法修改各科成绩，以使平均成绩成为90.实验五 数据库安全性3 设置SQL Server的安全认证模式4 用系统管理员来连接，使用sp_addlogin或create login创建SQL Server帐号U1，U2，并分配他们访问数据库的权限use student go exec sp_grantdbaccess U1 或 use student go create user U1 create login U1with password=123use student gocreate user U1create login U2with password=321use student gocreate user U25 先用系统管理员来连接，使用TSQL命令分配查询 STUDENTS表的权限给U1，U2，分别用U1，U2来连接，并执行相应的查询语句和插入语句,验证用户是否有该权限。 grant select on studentto U1,U2select snofrom student对于查询操作，用户有此权限。insert into student (sno,sname,age,sex)values (110803111,王明,21,男)对于插入操作，无此权限。6 先用系统管理员来连接，使用TSQL命令分配对STUDENTS表的插入和删除的权限给U2，用U1来连接，并执行相应插入和删除语句。验证用户是否有该权限。 grant insert,delete on studentto U2insert into student (sno,sname,age,sex)values (110803111,王明,21,男)deletefrom studentwhere sno=110803101对于插入和删除操作，U1用户都无此权限。7 先用系统管理员来连接，从用户名为U1的用户回收查询 STUDNETS表的权限，用U1来连接并执行相应的查询语句，验证是否丧失了该权限。revoke select on studentfrom U1select snofrom studentU1丧失了该权限。8 用系统管理员来连接，使用create user或sp_addrole创建SQL Server角色R1 create role R19 用系统管理员来连接，使用TSQL命令分配查询C表的权限给R1。将U1添加到R1中；用U1来连接并执行相应的查询语句。验证用户是否有该权限。 grant select on Cto R1sp_addrolemember R1,U1select cno,cnamefrom CU1用户有此权限。10 用系统管理员来连接，用Deny语句拒绝R1查询 STUDENTS表的权限，用U1来连接并执行相应的查询语句。验证用户是否有该权限。 denyselect on studentto R1select snofrom student用户无此权限。实验六 数据更新及完整性控制在student表SNO属性上创建主键;在C表的Cno属性上定义主键；在SC表的Sno，cno属性上定义主键;定义SC表的外键其中SC表的Sno参照S表的Sno，SC表的Cno参照C表的Sno。在SC表的成绩列上创建检查约束要求成绩的取值范围为0100（用SQL语句添加约束的方法）alter table student add primary key(sno)alter table Cadd primary key(cno)alter table SC add primary key(sno,cno)alter table SC add foreign key(sno) references student(sno)alter table SC add foreign key(cno) references student(cno)alter table SC add check (grade=0 and grade60不能修改，与在SC表成绩列上的检查约束要求成绩的取值范围为0100冲突6)插入课程号为030307的软件工程，结论。不能插入，违反了实体完整性约束，C中cno为主键，030307已存在不能重复Insert into C(cno,cname)values(030307,软件工程)插入（090803103, ,86），结论不能插入，违反了参照完整性约束，SC中sno和cno为主键，都不能为空 insert into SC(sno,cno,grade)values(090803103,86)插入（090803104，030307，120），结论不能插入，违反了用户自定义的完整性，SC中grade有check约束，成绩要在0到100分insert into SC(sno,cno,grade)values(090803104,030307,120)7) 将上述增加的课程和选修的记录删除。（注意级联删除的用法）delete from Cwhere cno=030307delete from Cwhere cno=030309实验七触发器11 在course表中编写insert，update的触发器,当向表中插入或修改数据时，如果课程名称重复则不允许插入或修改。验证该触发器的有效性和正确性。use studentgocreate trigger yzpon Cfor insert,update asdeclare count bitdeclare cname char(16)select cname=cname from insertedselect count=count(cname) from C where cname=cnameif count=2 begin raiserror(不能插入，更新!,16,1) rollback transactionend else raiserror(成功插入，更新!,16,1)12 在Student表中编写insert，update的触发器，如果每个班的学生不能超过4个，如果低于此数，添加可以完成；如果超过此数，则插入、修改将不能实现。验证该触发器的有效性和正确性。use studentgocreate trigger xxon studentfor insert,updateasdeclare count tinyintdeclare sno char(10)select sno=sno from insertedselect count=count(sno) from studentif count4 begin raiserror(不能插入，修改,16,1) rollback transaction end else raiserror(成功插入，修改,16,1)13 在CS表上编写update触发器，当修改CS表中的grade字段时将其修改前后的信息保存在cs_log表中。查看修改前后的数据是否有记录cs_log表中。create table SC_log(sno char(10),cno char(6),grade tinyint)use studentgocreate trigger xiugon SCfor updateasif update(grade) begin insert into SC_log (sno,cno,grade) select sno,cno,gradefrom deletedinsert into SC_log(sno,cno,grade) select sno,cno,gradefrom insertedend实验八 创建和使用存储过程 使用T-SQL创建存储过程，能够根据给定课程号进行课程信息的查询。并执行该存储过程(分别用按名传递和按位传递的方式执