北郵數(shù)據(jù)庫(kù)_sql實(shí)驗(yàn)二報(bào)告.docx

數(shù)據(jù)庫(kù)實(shí)驗(yàn)二SQL server實(shí)驗(yàn)二SQL Server數(shù)據(jù)庫(kù)設(shè)計(jì)高級(jí)內(nèi)容一、使用查詢(xún)分析器實(shí)現(xiàn)以下查詢(xún)(1) 統(tǒng)計(jì)有學(xué)生選修的課程門(mén)數(shù)。 SELECT COUNT(DISTINCT CourseNo) CourseTotal FROM sc(2) 求選課在四門(mén)以上的學(xué)生所選課程的平均成績(jī)(不統(tǒng)計(jì)不及格的課程)。最后按降序列出平均成績(jī)名次名單來(lái)。 SELECT sno,AVG(score) avg_grade FROM scWHERE score = 60GROUP BY sno HAVING count(courseno)=4ORDER BY avg_grade DESC(3) 統(tǒng)計(jì)每門(mén)指選課程的學(xué)生選修人數(shù)(超過(guò)10人的課程才統(tǒng)計(jì)),要求輸出課程號(hào),課程名和選修人數(shù),查詢(xún)結(jié)果按人數(shù)降序排列,若人數(shù)相同,按課程號(hào)升序排列。SELECT courseno,COUNT(sno) snumFROM sc GROUP BY courseno HAVING COUNT(sno)=10 ORDER BY COUNT(sno) DESC, courseno ASC(4) 檢索所學(xué)課程包含了s3所選所有課程的學(xué)生姓名。SELECT DISTINCT sno FROM sc sc_1 WHERENOT EXISTS( SELECT * FROM sc sc_2 WHERE sno=200815128 ANDNOT EXISTS(SELECT * FROM sc sc_3 WHERE sc_3.sno = sc_1.sno AND sc_3.courseno = sc_2.courseno)注：上述查詢(xún)中所用的課程號(hào)和學(xué)號(hào)的值，可以根據(jù)自己表中的數(shù)據(jù)作修改；為了驗(yàn)證查詢(xún)的正確，可能還需要輸入或修改表中的示例數(shù)據(jù)；二、實(shí)現(xiàn)數(shù)據(jù)完整性（1）定義check約束check約束用來(lái)限制用戶(hù)輸入的某一列數(shù)據(jù)；例如：成績(jī)輸入的值應(yīng)該限制為0-100之間的數(shù)值 alter table sc add constraint ck_sc CHECK( score = 0 AND score = 100)刪除約束alter table sc drop constraint ck_sc（2）定義規(guī)則（rule）rule也可以用來(lái)限制用戶(hù)輸入的數(shù)據(jù)，但它只定義一次，可以綁定到一列或多列；例如：創(chuàng)建一個(gè)規(guī)則，保證只允許輸入指定的課程類(lèi)別：“必修”，“任選”，“指選”，然后把此規(guī)則綁定到“課程類(lèi)別”；創(chuàng)建規(guī)則create rule coursetypeas coursetype in (必修,任選,指選)綁定規(guī)則sp_bindrule coursetype,Course.Category解除綁定sp_unbindrule Course.Category刪除規(guī)則drop rule coursetype（3）創(chuàng)建以上約束后，練習(xí)修改約束的操作（包括增加，修改和刪除以上約束）；三、在企業(yè)管理器中利用數(shù)據(jù)導(dǎo)入，導(dǎo)出向?qū)Ь毩?xí)數(shù)據(jù)的導(dǎo)入導(dǎo)出；把每個(gè)表中的數(shù)據(jù)導(dǎo)出到指定的文本文件中；導(dǎo)出的文件四、在企業(yè)管理器中，練習(xí)數(shù)據(jù)庫(kù)的完整性備份和恢復(fù)；備份文件；五、存儲(chǔ)過(guò)程和觸發(fā)器的實(shí)現(xiàn)1存儲(chǔ)過(guò)程（1）使用CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過(guò)程例1：定義存儲(chǔ)過(guò)程，實(shí)現(xiàn)學(xué)生學(xué)號(hào)，姓名，課程名和成績(jī)的查詢(xún)；CREATE PROC student_queryASSELECT student.sno,sname,coursename,score FROM student,course,scWHERE student.sno = sc.sno and course.courseno = sc.coursenoEXEC student_query例2：定義存儲(chǔ)過(guò)程，實(shí)現(xiàn)按某人指定課程的成績(jī)；CREATE PROC student_query1sname varchar(20),cname varchar(30)ASSELECT student.sno,sname,coursename,score FROM student,course,scWHERE student.sno = sc.sno AND course.courseno = sc.coursenoAND sname = sname AND coursename = cnameexec student_query1 李剛,數(shù)據(jù)庫(kù)技術(shù)例3：定義存儲(chǔ)過(guò)程，在查詢(xún)某人所選修的課程和成績(jī)，指定姓名時(shí)，可以只給出姓；CREATE PROC student_query2sname varchar(20) = %ASSELECT student.sno,sname,coursename,score FROM student,course,scWHERE student.sno = sc.sno AND course.courseno = sc.coursenoand sname LIKE snameEXEC student_query2 張%例4：定義存儲(chǔ)過(guò)程，計(jì)算并查看指定學(xué)生的總學(xué)分alter table course alter column Credits floatCREATE PROC student_query3sname varchar(20), total integer OUTPUTASSELECT total = SUM(Credits) FROM student,course,scWHERE student.sno = sc.sno AND course.courseno = sc.coursenoand sname = snamedeclare total floatexec student_query3 李剛,total OUTPUTselect total TotalCredits（2）使用EXEC命令執(zhí)行上述存儲(chǔ)過(guò)程2觸發(fā)器（1）使用CREATE TRIGGER命令對(duì)學(xué)生選課信息表創(chuàng)建插入觸發(fā)器，實(shí)現(xiàn)的功能是：當(dāng)向?qū)W生選課信息表中插入一記錄時(shí)，檢查該記錄的學(xué)號(hào)在學(xué)生表中是否存在，檢查該記錄的課程編號(hào)是否在課程表中存在，若有一項(xiàng)為否，則提示“違背數(shù)據(jù)的一致性”錯(cuò)誤信息，并且不允許插入。CREATE TRIGGER check_ins ON dbo.SC FOR INSERT ASif not exists(select * from student,course,insertedwhere student.sno=inserted.sno and course.courseno=inserted.courseno) RAISERROR (違背數(shù)據(jù)的一致性,16,1)elsebegin PRINT 數(shù)據(jù)已插入endinsert into sc values(200815128,C4,80)（2）使用CREATE TRIGGER 命令對(duì)學(xué)生信息表創(chuàng)建刪除觸發(fā)器，實(shí)現(xiàn)的功能是：當(dāng)在學(xué)生信息表中刪除一條記錄時(shí)，同時(shí)刪除學(xué)生選課信息表中相應(yīng)的記錄。CREATE TRIGGER del_trigger ON dbo.Student FOR DELETE ASdelete from sc where sc.sno in (select sno from deleted