通信工程專業(yè) SQL數(shù)據(jù)庫(kù)綜合實(shí)驗(yàn)報(bào)告

1、實(shí)驗(yàn)一用sql server實(shí)現(xiàn)數(shù)據(jù)庫(kù)設(shè)計(jì)實(shí)驗(yàn)?zāi)康模? 掌握使用企業(yè)管理器創(chuàng)建和管理sql server數(shù)據(jù)庫(kù)及相關(guān)數(shù)據(jù)庫(kù)對(duì)象。2 掌握使用sql語(yǔ)言中的select命令實(shí)現(xiàn)查詢功能。上機(jī)準(zhǔn)備：1 復(fù)習(xí)有關(guān)關(guān)系數(shù)據(jù)庫(kù)的基本知識(shí)和概念；2 復(fù)習(xí)有關(guān)sql語(yǔ)言中select命令的使用；3 了解有關(guān)sql server系統(tǒng)的組成；4 復(fù)習(xí)有關(guān)sql server服務(wù)器的使用和管理；5 復(fù)習(xí)有關(guān)企業(yè)管理器的基本操作；6 復(fù)習(xí)有關(guān)查詢分析器的基本操作；7 了解有關(guān)sql server服務(wù)器的登陸賬號(hào)，密碼；實(shí)驗(yàn)內(nèi)容：本實(shí)驗(yàn)將建立一個(gè)學(xué)校教學(xué)管理數(shù)據(jù)庫(kù)，在這個(gè)數(shù)據(jù)庫(kù)中，存儲(chǔ)以下信息：1 有關(guān)學(xué)生的信息，包

2、括學(xué)生的學(xué)號(hào)，姓名，班級(jí)，性別，出生時(shí)間，政治面貌，籍貫，入學(xué)時(shí)間，聯(lián)系電話，簡(jiǎn)歷；student(sno,sname, ssex,classno,sbirth,status,hometown,enrollment,telephone,resume) 2 有關(guān)班級(jí)的信息，包括班級(jí)的班名，專業(yè)，年級(jí)，人數(shù)，班主任； sclass(classno,classname,major,grade,number,advisor) 3 有關(guān)課程的信息，包括課程編號(hào)，課程名稱，課程類別（是指該課程為必修，指選還是任選）， 適用專業(yè)，適用年級(jí)，開課時(shí)間（是指在每個(gè)學(xué)年的第一學(xué)期，還是第二學(xué)期），學(xué)分，周學(xué)時(shí)；

3、course(courseno,coursename,category,major,grade,starttm,credits, weekhours) 4 有關(guān)學(xué)年課程安排的信息，包括課程編號(hào)，開設(shè)課程的學(xué)年（例如：2002學(xué)年），開設(shè)課程的班級(jí)，任課教師； schedule(courseno,startyear,classno,teacher) 5 有關(guān)學(xué)生選課的信息，包括課程編號(hào)，學(xué)生的學(xué)號(hào)，成績(jī)； sc(courseno,sno,score) 二、 使用企業(yè)管理器完成數(shù)據(jù)庫(kù)及數(shù)據(jù)庫(kù)對(duì)象的創(chuàng)建和管理 實(shí)驗(yàn)步驟： 1 創(chuàng)建數(shù)據(jù)庫(kù) （1）要求參數(shù)： 數(shù)據(jù)庫(kù)名稱：teaching 數(shù)據(jù)庫(kù)邏輯文件

4、名：teaching_data 日志邏輯文件名：teaching_log 操作系統(tǒng)數(shù)據(jù)文件名：e:xxxteaching.mdf 操作系統(tǒng)日志文件名：e:xxxteaching.ldf 數(shù)據(jù)文件初始大?。?mb 日志文件初始大?。?mb 數(shù)據(jù)文件最大大小：10mb 日志文件最大大?。?mb 數(shù)據(jù)文件增長(zhǎng)增量：10% 日志文件增長(zhǎng)增量：10% （2）創(chuàng)建后在database節(jié)點(diǎn)查看是否已包含了剛創(chuàng)建的數(shù)據(jù)庫(kù)teaching； （3）展開teaching 節(jié)點(diǎn)，以下的所有對(duì)象均創(chuàng)建在teaching 數(shù)據(jù)庫(kù)中； 2 創(chuàng)建用戶定義的數(shù)據(jù)類型 （1）練習(xí)按要求創(chuàng)建以下數(shù)據(jù)類型數(shù)據(jù)類型名稱 數(shù)據(jù)描述 s

5、tudent_no 長(zhǎng)度為10且不允許空值的字符型； 3 創(chuàng)建表 （1） 根據(jù)上述數(shù)據(jù)庫(kù)存儲(chǔ)的信息，決定創(chuàng)建幾個(gè)表，并給每一個(gè)表指定一個(gè)有意義的表名； （2） 根據(jù)每個(gè)表中每列存儲(chǔ)的數(shù)據(jù)的情況，為每個(gè)列指定列名，數(shù)據(jù)類型，數(shù)據(jù)的長(zhǎng)度，是否允許為空等列的屬性； （3） 使用企業(yè)管理器建立所有的表； （4） 創(chuàng)建之后，還可以根據(jù)具體情況，再對(duì)表的結(jié)構(gòu)進(jìn)行修改；（包括添加列，刪除列，修改已存在的列） 4 實(shí)現(xiàn)數(shù)據(jù)完整性 （1） 針對(duì)每一個(gè)表分析并定義主碼（primary key）sc：schedule：course：sclass：student：（2） 定義uique約束用來(lái)規(guī)定一個(gè)列中的兩行不能有

6、相同的值； 例如：希望學(xué)生的姓名是唯一的； （3） 針對(duì)每一個(gè)表分析外部碼，并利用“關(guān)系圖”定義外部碼（foreign key），建立表之間的參照關(guān)系； create table sc(sno char(9),courseno char(4),score smallint, primary key(sno,courseno), foreign key(sno) refrences student(sno), foreign key(courseno) refrences course(courseno) 關(guān)系圖：（4） 定義缺省值 方法1：直接在表設(shè)計(jì)時(shí)，定義列的default屬性； 例如：練

7、習(xí)在定義“性別”列時(shí)，定義它的缺省值為“男”； 方法2：創(chuàng)建一個(gè)缺省值對(duì)象，然后綁定到任何一個(gè)需要的列； 例如：練習(xí)創(chuàng)建并綁定一個(gè)缺省值到學(xué)生的聯(lián)系電話，缺省值為“unknown”； create defult telephone_defult as unknown （5） 創(chuàng)建以上約束后，練習(xí)修改約束的操作（包括增加，修改和刪除以上約束）； 5 根據(jù)自己所在班級(jí)的情況，對(duì)已經(jīng)創(chuàng)建的表自己輸入一些相關(guān)示例數(shù)據(jù)，在輸入的過程中，特地輸入一些違反上述約束條件的數(shù)據(jù)，觀察處理的效果； （1）使用企業(yè)管理器，完成一些記錄的插入、修改和刪除； （2）練習(xí)使用insert、update、delete命令完

8、成記錄的插入、修改和刪除；如果對(duì)該命令的使用不是很清楚，請(qǐng)練習(xí)查找“幫助系統(tǒng)”完成上述命令的學(xué)習(xí)； insert into student values(200815126,張一,男,2008123,1990-04-05,團(tuán)員,北京,2008-09-01無(wú)) insert into course values(1,數(shù)據(jù)庫(kù)技術(shù),指選,通信工程,第2學(xué)期,2,2) insert into sc(sno,courseno) values(10210444,1) update course set starttm= 每年第2學(xué)期 where courseno = 1 upda

9、te sc set score = 90 where sno =10210444 and courseno =1 執(zhí)行指令：sc表中出現(xiàn)10210444的學(xué)科1分?jǐn)?shù)：執(zhí)行刪除指令：可看出10210444的學(xué)科1分?jǐn)?shù)已被刪除：（3）練習(xí)使用truncate table命令刪除表數(shù)據(jù)； truncate table delete此為新建的表：執(zhí)行刪除指令：該表已消失：（4）在練習(xí)使用命令刪除數(shù)據(jù)之前，可以使用select into命令把數(shù)據(jù)保存到一個(gè)新建的表中； select sno,sname,ssex into stu from student三、 使用企業(yè)管理器創(chuàng)建和管理索引 1 利用表的屬

10、性對(duì)話框，觀察每個(gè)表已經(jīng)自動(dòng)創(chuàng)建的索引； 2 針對(duì)學(xué)生選課信息表創(chuàng)建如下索引 （1） 按學(xué)號(hào)+課程編號(hào)建立主鍵索引，索引組織方式為聚簇索引； create clustered index pk_sc on sc(sno,courseno) 執(zhí)行指令：創(chuàng)建成功：（2） 按學(xué)號(hào)建立索引，考慮是否需要?jiǎng)?chuàng)建唯一索引，索引組織方式為非聚簇索引； （3） 按課程編號(hào)建立索引，考慮是否需要?jiǎng)?chuàng)建唯一索引，索引組織方式為非聚簇索引； （4） 再針對(duì)學(xué)生選課信息表輸入一些數(shù)據(jù)，觀察表中記錄位置的變化；通過觀察比較非聚簇索引和聚簇索引的不同之處； 3 針對(duì)其它表，練習(xí)創(chuàng)建索引；（考慮：是否對(duì)每個(gè)列都應(yīng)創(chuàng)建索引？應(yīng)該

11、從哪些方面考慮是否應(yīng)為此列創(chuàng)建索引？） 不需要每一列都創(chuàng)建索引，應(yīng)該從我們對(duì)于數(shù)據(jù)庫(kù)的管理和排序的方便的和簡(jiǎn)單性出發(fā)對(duì)于某些列創(chuàng)建索引4 練習(xí)索引的修改和刪除操作四、 使用查詢分析器實(shí)現(xiàn)以下查詢 1練習(xí)課堂上舉例介紹的幾類查詢； 2實(shí)現(xiàn)以下查詢(1) 檢索選修了課程號(hào)為c1或c2課程，且成績(jī)高于或等于70分的學(xué)生的姓名，課程名和成績(jī)。select sname,coursename,score from student,sc,course where student.sno = sc.sno and sc.courseno = course.courseno and (course.course

12、no=c1 or course.courseno=c2) and score = 70(2) 檢索姓“王”的所有學(xué)生的姓名和年齡。select sname, year(getdate() - year(sbirth) from student where sname like 王% (3) 檢索沒有考試成績(jī)的學(xué)生姓名和課程名。select sname,coursename,score from student,sc,course where student.sno = sc.sno and sc.courseno = course.courseno and (course.courseno=c

13、1 or course.courseno=c2) and score = 70(4) 檢索年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡。select sname,year(getdate() - year(sbirth) age from student where ssex =男 and year(getdate() - year(sbirth) (select avg(year(getdate() - year(sbirth) from student where ssex =女)注：上述查詢中所用的課程號(hào)和學(xué)號(hào)的值，可以根據(jù)自己表中的數(shù)據(jù)作修改；為了驗(yàn)證查詢的正確，可能還需要輸入或修改表中的

14、示例數(shù)據(jù)； 五、 創(chuàng)建和管理視圖 1 創(chuàng)建視圖： 使用企業(yè)管理器或使用create view命令例1：創(chuàng)建視圖，包含所有通信工程專業(yè)的學(xué)生的信息； create view tx_student as select sno,sname,ssex,student.classno,sbirth,status from student,sclass where student.classno = sclass.classno and major = 通信工程 with check option 例2：創(chuàng)建視圖，包含所有學(xué)生的學(xué)號(hào)，姓名，選課的課程名和成績(jī)； create view sc_grade w

15、ith encryption as select student.sno,sname,coursename,score from student,course,sc where student.sno=sc.sno and course.courseno=sc.courseno 例3：創(chuàng)建視圖，包含所有課程的課程號(hào)，名，班級(jí)名稱及每班選課的人數(shù)；create view sc_class as select course.courseno,coursename,classname,total from course,sclass, (select course.courseno,classno,

16、count(*) total from course,student,sc where course.courseno=sc.courseno and student.sno=sc.sno group by course.courseno,student.classno) temp where course.courseno=temp.courseno and sclass.classno=temp.classno 2 練習(xí)修改視圖的定義 第一個(gè)視圖，包括with check option 選項(xiàng) 第二個(gè)視圖，包括with encryption選項(xiàng)；（查看視圖的屬性，觀察和以前有和不同） 3利用

17、已經(jīng)創(chuàng)建的視圖進(jìn)行查詢； 4利用已經(jīng)創(chuàng)建的視圖修改數(shù)據(jù)（觀察是否所有通過視圖的修改都能實(shí)現(xiàn)？） 完全可以5練習(xí)刪除視圖可以直接刪除 實(shí)驗(yàn)二 sql server數(shù)據(jù)庫(kù)設(shè)計(jì)高級(jí)內(nèi)容一、使用查詢分析器實(shí)現(xiàn)以下查詢 (1) 統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)。select count(distinct cno) from sc (2) 求選課在四門以上的學(xué)生所選課程的平均成績(jī)(不統(tǒng)計(jì)不及格的課程)。最后按降序列出平均成績(jī)名次名單來(lái)。 select sno,avg(score) avg_grade from sc where score = 60 group by sno having count(course

18、no)=4 order by avg_grade desc (3) 統(tǒng)計(jì)每門指選課程的學(xué)生選修人數(shù)(超過4人的課程才統(tǒng)計(jì)),要求輸出課程號(hào),課程名和選修人數(shù),查詢結(jié)果按人數(shù)降序排列,若人數(shù)相同,按課程號(hào)升序排列。 select courseno,count(sno) snum from sc group by courseno having count(sno)=4order by count(sno) desc, courseno asc (4) 檢索所學(xué)課程包含了s3所選所有課程的學(xué)生姓名。 select distinct sno from sc sc_1 where not exists

19、( select * from sc sc_2 where sno=10210477 and not exists( select * from sc sc_3 where sc_3.sno = sc_1.sno and sc_3.courseno = sc_2.courseno ) ) 注：上述查詢中所用的課程號(hào)和學(xué)號(hào)的值，可以根據(jù)自己表中的數(shù)據(jù)作修改；為了驗(yàn)證查詢的正確，可能還需要輸入或修改表中的示例數(shù)據(jù)； 二、 實(shí)現(xiàn)數(shù)據(jù)完整性 （1） 定義check約束check約束用來(lái)限制用戶輸入的某一列數(shù)據(jù)； 例如：成績(jī)輸入的值應(yīng)該限制為0-100之間的數(shù)值 alter table sc add c

20、onstraint ck_sc check( score = 0 and score = 100) 刪除約束 alter table sc drop constraint ck_sc （2） 定義規(guī)則（rule）rule也可以用來(lái)限制用戶輸入的數(shù)據(jù)，但它只定義一次，可以綁定到一列或多列； 例如：創(chuàng)建一個(gè)規(guī)則，保證只允許輸入指定的課程類別：“必修”，“任選”，“指選”，然后把此規(guī)則綁定到“課程類別”； 創(chuàng)建規(guī)則 create rule coursetype as coursetype in (必修,任選,指選) 綁定規(guī)則 sp_bindrule coursetype,course.categor

21、y 解除綁定 sp_unbindrule course.category刪除規(guī)則 刪除規(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)出到指定的文本文件中； 四、 在企業(yè)管理器中，練習(xí)數(shù)據(jù)庫(kù)的完整性備份和恢復(fù)； 五、 存儲(chǔ)過程和觸發(fā)器的實(shí)現(xiàn) 1 存儲(chǔ)過程 （1）使用create procedure命令創(chuàng)建存儲(chǔ)過程例1：定義存儲(chǔ)過程，實(shí)現(xiàn)學(xué)生學(xué)號(hào)，姓名，課程名和成績(jī)的查詢； create proc student_query as selec

22、t student.sno,sname,coursename,score from student,course,sc where student.sno = sc.sno and course.courseno = sc.courseno exec student_query 例2：定義存儲(chǔ)過程，實(shí)現(xiàn)按某人指定課程的成績(jī)； create proc student_query1 sname varchar(20), cname varchar(30) as select student.sno,sname,coursename,score from student,course,sc wher

23、e student.sno = sc.sno and course.courseno = sc.courseno and sname = sname and coursename = cname exec student_query1 張三,數(shù)據(jù)庫(kù)技術(shù) 例3：定義存儲(chǔ)過程，在查詢某人所選修的課程和成績(jī)，指定姓名時(shí)，可以只給出姓； create proc student_query2 sname varchar(20) = % as 待添加的隱藏文字內(nèi)容1select student.sno,sname,coursename,score from student,course,sc where

24、student.sno = sc.sno and course.courseno = sc.courseno and sname like sname exec student_query2 張%例4：定義存儲(chǔ)過程，計(jì)算并查看指定學(xué)生的總學(xué)分alter table course alter column credits float create proc student_query3 sname varchar(20), total integer output as select total = sum(credits) from student,course,sc where student.sno = sc.sno and course.courseno = sc.courseno and sname = sname declare total float exec student_query3 王五,total output select total （2）使用exec命令執(zhí)行上述存儲(chǔ)過程2觸發(fā)器（1）使用create trigger命令對(duì)學(xué)生選課信息表創(chuàng)建插入觸發(fā)器，實(shí)現(xiàn)的功能是：當(dāng)向?qū)W生選課信息表中插入一記錄時(shí)，檢查該記錄的學(xué)號(hào)在學(xué)生表中是否存在，檢查該記錄的課程編號(hào)是否在課程表中