1、實驗二 利用查詢分析器查詢（一） 從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 sn

2、o 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)

3、 班級,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分上的學生的學號、姓名s

4、elect 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

5、 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

6、)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

7、 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，

8、視圖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

9、%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 stu

10、dent.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 U1

11、with 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)valu

12、es (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的用戶回收查詢 STU

13、DNETS表的權限，用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,cnamef

14、rom 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 t

15、able 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表成績列上的檢查約束要求成績的取值范圍為010

16、0沖突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(

17、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

18、 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 studentgocreat

19、e 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表中。查看修改

20、前后的數據是否有記錄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創建存儲過程，能夠根據給定課程號進行課程信息的查詢。并執行該存儲過程(分別用按名傳遞和按位傳遞的方式執行)。