《通信工程專業(yè) SQL數(shù)據(jù)庫綜合實驗報告》由會員分享，可在線閱讀，更多相關《通信工程專業(yè) SQL數(shù)據(jù)庫綜合實驗報告（47頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、 實驗一用SQL Server實現(xiàn)數(shù)據(jù)庫設計 實驗目的： 1． 掌握使用企業(yè)管理器創(chuàng)建和管理SQL Server數(shù)據(jù)庫及相關數(shù)據(jù)庫對象。 2． 掌握使用SQL語言中的SELECT命令實現(xiàn)查詢功能。 上機準備： 1． 復習有關關系數(shù)據(jù)庫的基本知識和概念； 2． 復習有關SQL語言中SELECT命令的使用； 3． 了解有關SQL Server系統(tǒng)的組成； 4． 復習有關SQL Server服務器的使用和管理； 5． 復習有關企業(yè)管理器的基本操作； 6． 復習有關查詢分析器的基本操作； 7． 了解有關SQL Server服務器的登陸賬號，密碼； 實驗內(nèi)容： 本實驗將建立一

2、個學校教學管理數(shù)據(jù)庫，在這個數(shù)據(jù)庫中，存儲以下信息： 1． 有關學生的信息，包括學生的學號，姓名，班級，性別，出生時間，政治面貌，籍貫，入學時間，聯(lián)系電話，簡歷； Student(Sno,Sname, Ssex,Classno,Sbirth,Status,Hometown,Enrollment,Telephone,Resume) 2． 有關班級的信息，包括班級的班名，專業(yè)，年級，人數(shù)，班主任； SClass(ClassNo,ClassName,Major,Grade,Number,Advisor) 3． 有關課程的信息，包括課程編號，課程名稱，課程類別（是指該課程

3、為必修，指選還是任選）， 適用專業(yè)，適用年級，開課時間（是指在每個學年的第一學期，還是第二學期），學分，周學時； Course(CourseNo,CourseName,Category,Major,Grade,StartTM,Credits, WeekHours) 4． 有關學年課程安排的信息，包括課程編號，開設課程的學年（例如：2002學年），開設課程的班級，任課教師； Schedule(CourseNo,StartYear,ClassNo,Teacher) 5． 有關學生選課的信息，包括課程編號，學生的學號，成績； SC(CourseNo,Sno,Score

4、) 二、 使用企業(yè)管理器完成數(shù)據(jù)庫及數(shù)據(jù)庫對象的創(chuàng)建和管理 實驗步驟： 1． 創(chuàng)建數(shù)據(jù)庫 （1）要求參數(shù)： 數(shù)據(jù)庫名稱：teaching 數(shù)據(jù)庫邏輯文件名：teaching_data 日志邏輯文件名：teaching_log 操作系統(tǒng)數(shù)據(jù)文件名：e:\xxx\teaching.mdf 操作系統(tǒng)日志文件名：e:\xxx\teaching.ldf 數(shù)據(jù)文件初始大?。?MB 日志文件初始大?。?MB 數(shù)據(jù)文件最大大?。?0MB 日志文件最大大?。?MB 數(shù)據(jù)文件增長增量：10% 日志文件增長增量：10% （2）創(chuàng)建后在database節(jié)點查

5、看是否已包含了剛創(chuàng)建的數(shù)據(jù)庫teaching； （3）展開teaching 節(jié)點，以下的所有對象均創(chuàng)建在teaching 數(shù)據(jù)庫中； 2． 創(chuàng)建用戶定義的數(shù)據(jù)類型 （1）練習按要求創(chuàng)建以下數(shù)據(jù)類型 數(shù)據(jù)類型名稱 數(shù)據(jù)描述 student_no 長度為10且不允許空值的字符型； 3． 創(chuàng)建表 （1） 根據(jù)上述數(shù)據(jù)庫存儲的信息，決定創(chuàng)建幾個表，并給每一個表指定一個有意義的表名； （2） 根據(jù)每個表中每列存儲的數(shù)據(jù)的情況，為每個列指定列名，數(shù)據(jù)類型，數(shù)據(jù)的長度，是否允許為空等列的屬性； （3） 使用企業(yè)管理器建立所有的表； （4） 創(chuàng)建之后，還可

6、以根據(jù)具體情況，再對表的結構進行修改；（包括添加列，刪除列，修改已存在的列） 4． 實現(xiàn)數(shù)據(jù)完整性 （1） 針對每一個表分析并定義主碼（Primary Key） SC： Schedule： Course： Sclass： Student： （2） 定義UIQUE約束 用來規(guī)定一個列中的兩行不能有相同的值； 例如：希望學生的姓名是唯一的； （3） 針對每一個表分析外部碼，并利用“關系圖”定義外部碼（Foreign Key），建立表之間的參照關系； Create table SC(Sno char(9),CourseNo char(4),S

7、core smallint, Primary key(Sno,CourseNo), Foreign key(Sno) refrences Student(Sno), Foreign key(CourseNo) refrences Course(CourseNo)) 關系圖： （4） 定義缺省值 方法1：直接在表設計時，定義列的default屬性； 例如：練習在定義“性別”列時，定義它的缺省值為“男”； 方法2：創(chuàng)建一個缺省值對象，然后綁定到任何一個需要的列； 例如：練習創(chuàng)建并綁定一個缺省值到學生的聯(lián)系電話，缺省值為“unknown”； Create d

8、efult telephone_defult as unknown （5） 創(chuàng)建以上約束后，練習修改約束的操作（包括增加，修改和刪除以上約束）； 5． 根據(jù)自己所在班級的情況，對已經(jīng)創(chuàng)建的表自己輸入一些相關示例數(shù)據(jù)，在輸入的過程中，特地輸入一些違反上述約束條件的數(shù)據(jù)，觀察處理的效果； （1）使用企業(yè)管理器，完成一些記錄的插入、修改和刪除； （2）練習使用INSERT、UPDATE、DELETE命令完成記錄的插入、修改和刪除；如果對該命令的使用不是很清楚，請練習查找“幫助系統(tǒng)”完成上述命令的學習； insert into student values(20081512

9、6,張一,男,2008123,1990-04-05,團員,北京,2008-09-01,13812345678,無) insert into course values(1,數(shù)據(jù)庫技術,指選,通信工程,第2學期,2,2) insert into SC(Sno,CourseNo) values(10210444,1) update course set StartTM= 每年第2學期 where CourseNo = 1 update SC set Score = 90 where Sno =10210444 and CourseNo =1

10、執(zhí)行指令： SC表中出現(xiàn)10210444的學科1分數(shù)： 執(zhí)行刪除指令： 可看出10210444的學科1分數(shù)已被刪除： （3）練習使用TRUNCATE TABLE命令刪除表數(shù)據(jù)； truncate table delete 此為新建的表： 執(zhí)行刪除指令： 該表已消失： （4）在練習使用命令刪除數(shù)據(jù)之前，可以使用SELECT INTO命令把數(shù)據(jù)保存到一個新建的表中； Select sno,sname,ssex into stu from student 三、 使用企業(yè)管理器創(chuàng)建和管理索引 1． 利用表的屬性對話框，觀察每個表

11、已經(jīng)自動創(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） 再針對學生選課信息表輸入一些數(shù)據(jù)，觀察表中記錄位置的變化；通過觀察比較非聚簇索引和聚簇索引的不同之處； 3． 針對其它表，練習創(chuàng)建

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

13、no = course.courseno and (course.courseno=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.s

14、no and sc.courseno = course.courseno and (course.courseno=C1 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 stude

15、nt WHERE ssex =女) 注：上述查詢中所用的課程號和學號的值，可以根據(jù)自己表中的數(shù)據(jù)作修改；為了驗證查詢的正確，可能還需要輸入或修改表中的示例數(shù)據(jù)； 五、 創(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

16、and Major = 通信工程 WITH CHECK OPTION 例2：創(chuàng)建視圖，包含所有學生的學號，姓名，選課的課程名和成績； CREATE VIEW SC_Grade WITH 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)建視圖，包含所有課程的課程號，名，班級名稱及每班選課的人數(shù)； CREATE VIEW

17、 SC_CLASS AS SELECT course.courseno,coursename,classname,total FROM course,sclass, (SELECT course.courseno,classno,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=t

18、emp.courseno and sclass.classno=temp.classno 2． 練習修改視圖的定義 第一個視圖，包括with check option 選項 第二個視圖，包括with encryption選項；（查看視圖的屬性，觀察和以前有和不同） 3．利用已經(jīng)創(chuàng)建的視圖進行查詢； 4．利用已經(jīng)創(chuàng)建的視圖修改數(shù)據(jù)（觀察是否所有通過視圖的修改都能實現(xiàn)？） 完全可以 5．練習刪除視圖 可以直接刪除 實驗二 SQL Server數(shù)據(jù)庫設計高級內(nèi)容 一、使用查詢分析器實現(xiàn)以下查詢 (1

19、) 統(tǒng)計有學生選修的課程門數(shù)。 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(courseno)>=4 ORDER BY avg_grade DESC (3) 統(tǒng)計每門指選課程的學生選修人數(shù)(超過4人的課程才統(tǒng)計),要求輸出課程號,課程名和選修人數(shù),查詢結果按人

20、數(shù)降序排列,若人數(shù)相同,按課程號升序排列。 SELECT courseno,COUNT(sno) snum FROM sc GROUP BY courseno HAVING COUNT(sno)>=4 ORDER BY COUNT(sno) DESC, courseno ASC (4) 檢索所學課程包含了s3所選所有課程的學生姓名。 SELECT DISTINCT sno FROM sc sc_1 WHERE NOT EXISTS( SELECT * FROM sc sc_2 WHERE sno=10210477 AND NOT EXISTS( S

21、ELECT * FROM sc sc_3 WHERE sc_3.sno = sc_1.sno AND sc_3.courseno = sc_2.courseno ) ) 注：上述查詢中所用的課程號和學號的值，可以根據(jù)自己表中的數(shù)據(jù)作修改；為了驗證查詢的正確，可能還需要輸入或修改表中的示例數(shù)據(jù)； 二、 實現(xiàn)數(shù)據(jù)完整性 （1） 定義check約束 check約束用來限制用戶輸入的某一列數(shù)據(jù)； 例如：成績輸入的值應該限制為0-100之間的數(shù)值 alter table sc add constraint ck_sc CHECK( score >= 0 AND

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

23、gory 解除綁定 sp_unbindrule Course.Category刪除規(guī)則 刪除規(guī)則： drop rule coursetype （3） 創(chuàng)建以上約束后，練習修改約束的操作（包括增加，修改和刪除以上約束）； 三、 在企業(yè)管理器中利用數(shù)據(jù)導入，導出向?qū)Ь毩晹?shù)據(jù)的導入導出；把每個表中的數(shù)據(jù)導 出到指定的文本文件中； 四、 在企業(yè)管理器中，練習數(shù)據(jù)庫的完整性備份和恢復； 五、 存儲過程和觸發(fā)器的實現(xiàn) 1． 存儲過程 （1）使用CREATE PROCEDURE命令創(chuàng)建存儲過程 例1：定義存儲過程，實現(xiàn)學生學號，姓名

24、，課程名和成績的查詢； CREATE PROC student_query AS SELECT student.sno,sname,coursename,score FROM student,course,sc WHERE student.sno = sc.sno and course.courseno = sc.courseno EXEC student_query 例2：定義存儲過程，實現(xiàn)按某人指定課程的成績； CREATE PROC student_query1 @sname varchar(20), @cname varchar(30)

25、 AS SELECT student.sno,sname,coursename,score FROM student,course,sc WHERE student.sno = sc.sno AND course.courseno = sc.courseno AND sname = @sname AND coursename = @cname exec student_query1 張三,數(shù)據(jù)庫技術 例3：定義存儲過程，在查詢某人所選修的課程和成績，指定姓名時，可以只給出姓； CREATE PROC student_query2 @sname

26、varchar(20) = % AS SELECT student.sno,sname,coursename,score FROM student,course,sc WHERE 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 studen

27、t_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í)行上述存

28、儲過程 2．觸發(fā)器 （1）使用CREATE TRIGGER命令對學生選課信息表創(chuàng)建插入觸發(fā)器，實現(xiàn)的功能是：當向?qū)W生選課信息表中插入一記錄時，檢查該記錄的學號在學生表中是否存在，檢查該記錄的課程編號是否在課程表中存在，若有一項為否，則提示“違背數(shù)據(jù)的一致性”錯誤信息，并且不允許插入。 CREATE TRIGGER check_ins ON [dbo].[SC] FOR INSERT AS if not exists( select * from student,course,inserted where student.sno=inserted.sno and cou

29、rse.courseno=inserted.courseno ) RAISERROR (違背數(shù)據(jù)的一致性,16,1) else begin PRINT 數(shù)據(jù)已插入 end insert into sc values(200815128,C4,80) （2）使用CREATE TRIGGER 命令對學生信息表創(chuàng)建刪除觸發(fā)器，實現(xiàn)的功能是：當在學生信息表中刪除一條記錄時，同時刪除學生選課信息表中相應的記錄。 CREATE TRIGGER [del_trigger] ON [dbo].[Student] FOR DELETE AS del

30、ete from sc where sc.sno in (select sno from deleted) （3）向課程信息表插入數(shù)據(jù)，在學生信息表刪除記錄，驗證觸發(fā)器的執(zhí)行； 六、 在實驗老師驗收所有的項目后，刪除所創(chuàng)建的數(shù)據(jù)庫, 把實驗中生成的腳本文件，數(shù)據(jù)導出的文本文件以及數(shù)據(jù)庫的備份文件復制到U盤中，并通過驗收。 實驗三四，基于PB的繼續(xù)實驗 1、 連接數(shù)據(jù)源 1、 登陸界面 2、 做好的查詢界面 3、 運行后的界面 4、 升序： 5、 降序； 6、 信息輸入（自由顯示風格） 7、 表格顯示風格 8、 實現(xiàn)插入功能 9、 將插入數(shù)據(jù)刪除 10、 課程表 11、 實現(xiàn)順序查找 12、 學生查詢功能 13、 查詢學生信息 14、 插入新學生信息 15、 查詢剛插入的新學生信息 16、 更新數(shù)據(jù)庫 以上就是我的數(shù)據(jù)庫實驗報告，從中我學得到了很多，既學到了ＳＱＬ的使用，也學會了ＰＢ的基本操作，獲益匪淺，謝謝老師的指導