通信工程專業(yè) SQL數據庫綜合實驗報告

1、實驗一用sql server實現數據庫設計實驗目的：1 掌握使用企業(yè)管理器創(chuàng)建和管理sql server數據庫及相關數據庫對象。2 掌握使用sql語言中的select命令實現查詢功能。上機準備：1 復習有關關系數據庫的基本知識和概念；2 復習有關sql語言中select命令的使用；3 了解有關sql server系統(tǒng)的組成；4 復習有關sql server服務器的使用和管理；5 復習有關企業(yè)管理器的基本操作；6 復習有關查詢分析器的基本操作；7 了解有關sql server服務器的登陸賬號，密碼；實驗內容：本實驗將建立一個學校教學管理數據庫，在這個數據庫中，存儲以下信息：1 有關學生的信息，包

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

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

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

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

6、相同的值； 例如：希望學生的姓名是唯一的； （3） 針對每一個表分析外部碼，并利用“關系圖”定義外部碼（foreign key），建立表之間的參照關系； 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) 關系圖：（4） 定義缺省值 方法1：直接在表設計時，定義列的default屬性； 例如：練

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

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

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

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

11、從哪些方面考慮是否應為此列創(chuàng)建索引？） 不需要每一列都創(chuàng)建索引，應該從我們對于數據庫的管理和排序的方便的和簡單性出發(fā)對于某些列創(chuàng)建索引4 練習索引的修改和刪除操作四、 使用查詢分析器實現以下查詢 1練習課堂上舉例介紹的幾類查詢； 2實現以下查詢(1) 檢索選修了課程號為c1或c2課程，且成績高于或等于70分的學生的姓名，課程名和成績。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) 檢索姓“王”的所有學生的姓名和年齡。select sname, year(getdate() - year(sbirth) from student where sname like 王% (3) 檢索沒有考試成績的學生姓名和課程名。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) 檢索年齡大于女同學平均年齡的男學生姓名和年齡。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 =女)注：上述查詢中所用的課程號和學號的值，可以根據自己表中的數據作修改；為了驗證查詢的正確，可能還需要輸入或修改表中的

14、示例數據； 五、 創(chuàng)建和管理視圖 1 創(chuàng)建視圖： 使用企業(yè)管理器或使用create view命令例1：創(chuàng)建視圖，包含所有通信工程專業(yè)的學生的信息； 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)建視圖，包含所有學生的學號，姓名，選課的課程名和成績； 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)建視圖，包含所有課程的課程號，名，班級名稱及每班選課的人數；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 練習修改視圖的定義 第一個視圖，包括with check option 選項 第二個視圖，包括with encryption選項；（查看視圖的屬性，觀察和以前有和不同） 3利用

17、已經創(chuàng)建的視圖進行查詢； 4利用已經創(chuàng)建的視圖修改數據（觀察是否所有通過視圖的修改都能實現？） 完全可以5練習刪除視圖可以直接刪除 實驗二 sql server數據庫設計高級內容一、使用查詢分析器實現以下查詢 (1) 統(tǒng)計有學生選修的課程門數。select count(distinct cno) from sc (2) 求選課在四門以上的學生所選課程的平均成績(不統(tǒng)計不及格的課程)。最后按降序列出平均成績名次名單來。 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)計每門指選課程的學生選修人數(超過4人的課程才統(tǒng)計),要求輸出課程號,課程名和選修人數,查詢結果按人數降序排列,若人數相同,按課程號升序排列。 select courseno,count(sno) snum from sc group by courseno having count(sno)=4order by count(sno) desc, courseno asc (4) 檢索所學課程包含了s3所選所有課程的學生姓名。 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 ) ) 注：上述查詢中所用的課程號和學號的值，可以根據自己表中的數據作修改；為了驗證查詢的正確，可能還需要輸入或修改表中的示例數據； 二、 實現數據完整性 （1） 定義check約束check約束用來限制用戶輸入的某一列數據； 例如：成績輸入的值應該限制為0-100之間的數值 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也可以用來限制用戶輸入的數據，但它只定義一次，可以綁定到一列或多列； 例如：創(chuàng)建一個規(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)建以上約束后，練習修改約束的操作（包括增加，修改和刪除以上約束）；三、 在企業(yè)管理器中利用數據導入，導出向導練習數據的導入導出；把每個表中的數據導出到指定的文本文件中； 四、 在企業(yè)管理器中，練習數據庫的完整性備份和恢復； 五、 存儲過程和觸發(fā)器的實現 1 存儲過程 （1）使用create procedure命令創(chuàng)建存儲過程例1：定義存儲過程，實現學生學號，姓名，課程名和成績的查詢； 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：定義存儲過程，實現按某人指定課程的成績； 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 張三,數據庫技術 例3：定義存儲過程，在查詢某人所選修的課程和成績，指定姓名時，可以只給出姓； create proc student_query2 sname varchar(20) = % as 待添加的隱藏文字內容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：定義存儲過程，計算并查看指定學生的總學分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í)行上述存儲過程2觸發(fā)器（1）使用create trigger命令對學生選課信息表創(chuàng)建插入觸發(fā)器，實現的功能是：當向學生選課信息表中插入一記錄時，檢查該記錄的學號在學生表中是否存在，檢查該記錄的課程編號是否在課程表中