數(shù)據(jù)庫(kù)教務(wù)管理系統(tǒng)綜合實(shí)驗(yàn)報(bào)告-資料下載頁(yè)
2025-08-07 11:14本頁(yè)面
【正文】 nd = 教師基本信息查詢的視圖 create view V_teacher as select Tname,Dname,Tel from teacher,sdept where = 三、創(chuàng)建索引在教師表中系代號(hào)列上建立索引create unique index teadnoon teacher(Dno)。在學(xué)生表姓名列上建立索引create unique index stusnameon student(sname)。四、創(chuàng)建用戶并對(duì)用戶的權(quán)限做相應(yīng)設(shè)置創(chuàng)建學(xué)生用戶create login u1with password=39。11139。use 教務(wù)管理系統(tǒng)create user 學(xué)生for login u1對(duì)學(xué)生用戶進(jìn)行授權(quán)grant selecton studentto 學(xué)生grant selecton V_studentto 學(xué)生grant selecton DTCto 學(xué)生grant selecton V_teacherto 學(xué)生創(chuàng)建教師用戶create login u2with password=39。11139。use 教務(wù)管理系統(tǒng)create user 教師for login u2對(duì)教師用戶進(jìn)行授權(quán)grant selecton studentto 教師grant selecton DTCto 教師grant select,updateon teacherto 教師grant select,insert,update(grade,daigrade,midbrade,lasgrade,fingrade)on SCto 教師創(chuàng)建教務(wù)管理員用戶create login u3with password=39。11139。use 教務(wù)管理系統(tǒng)create user 教務(wù)處for login u3對(duì)教務(wù)管理員用戶進(jìn)行授權(quán)grant select,update,insert,deleteon teacherto 教務(wù)處grant select,update,insert,deleteon studentto 教務(wù)處grant select,update,insert,deleteon sdeptto 教務(wù)處grant select,update,insert,deleteon classto 教務(wù)處grant select,update,insert,deleteon courseto 教務(wù)處五、創(chuàng)建觸發(fā)器刪除教師信息觸發(fā)器create trigger 刪除教師信息on teacherfor deleteas if((select tno from deleted) in (select tno from course))beginprint39。course表里有該教師的課程，請(qǐng)先刪除course表里的記錄！39。rollback end演示刪除教師信息觸發(fā)器的操作delete from teacherwhere tno=39。0400439。刪除學(xué)生信息觸發(fā)器create trigger 刪除學(xué)生信息on studentfor deleteas if((select sno from deleted)in (select sno from sc))beginprint39。SC表里有該學(xué)生的選課課程，請(qǐng)先刪除SC表里的記錄！39。rollbackend演示刪除學(xué)生信息觸發(fā)器的操作delete from studentwhere sno=39。02031239。選課觸發(fā)器create trigger 選課on SCfor insertas if((select o from inserted) in (select Cno from course) )beginprint39。選課成功39。end 演示執(zhí)行“選課”觸發(fā)器insert into SC(sno,o)values(39。02030139。,39。0239。)刪除選課觸發(fā)器create trigger 刪除選課on SCfor DELETEASif((select o from deleted) in (select o from sc))beginprint39。刪除選課成功39。end 演示執(zhí)行“刪除選課”觸發(fā)器delete from scwhere sno=39。01011739。and o=39。0339。六、存儲(chǔ)過(guò)程查詢成績(jī)的存儲(chǔ)過(guò)程CREATE PROCEDURE 查詢成績(jī) @sn char(10) asselect ,sname,ame,Credit,gradefrom student,course,SCwhere = and = and =@sn 查詢學(xué)號(hào)為“010104”的學(xué)生成績(jī)exec 查詢成績(jī) 39。01010439。 20
教學(xué)課件相關(guān)推薦
鄂ICP備17016276號(hào)-1