實驗三、SQL-數(shù)據(jù)查詢語言.doc

寧波工程學(xué)院數(shù)據(jù)庫理論與技術(shù)課程實驗報告學(xué)院： 電子與信息工程學(xué)院 專業(yè)： 計算機科學(xué)與技術(shù) 年級： 實驗時間： 組名： 組長： 組員姓名： 指導(dǎo)教師簽字： 成績： 實驗三、SQL-數(shù)據(jù)查詢語言一、實驗?zāi)康暮鸵?、實驗?zāi)康模荷钊肜斫膺B接查詢和嵌套查詢的語句格式、執(zhí)行過程，掌握它們的使用方法；理解數(shù)據(jù)更新語言INSERT、UPDATE、DELETE語句的格式和作用，掌握它們的使用方法和應(yīng)用范圍；理解視圖的概念，掌握其定義方法和應(yīng)用方法。2、實驗要求：在每次實驗前，學(xué)生必須根據(jù)實驗指導(dǎo)書設(shè)計出實驗方案（程序和實驗步驟）；在實驗過程中要求獨立進行程序調(diào)試和排錯，學(xué)會使用在線幫助來解決實驗中遇到的問題，同時運用理論的知識來分析問題和解決問題，記錄下實驗過程和結(jié)果。上機結(jié)束后寫出實驗報告，并對實驗過程進行分析和總結(jié)。二、實驗內(nèi)容與步驟1、在SQL SERVER 2005上附加數(shù)據(jù)庫jxgl和ReadBook。2、在jxgl中完成下列查詢jxgl數(shù)據(jù)庫中表及表中字段說明:Students(sno, sname,class,ssex,bday,bplace,mgrade,photo,sfzh,zxf)該表是學(xué)生基本信息表 其中：sno學(xué)號，sname姓名，class班級，ssex性別，bday出生日期，bplace籍貫，mgrade入學(xué)成績，photo照片，sfzh身份證號碼，zxf總學(xué)分Course(cno,cname,cpno,credit) 該表是課程情況表 其中：cno課程號，cname課程名稱，cpno先修課程號，credit學(xué)分SC(term,sno,cno,grade,point) 該表是學(xué)生選課成績表 其中：term學(xué)期，sno學(xué)號，cno課程號，grade成績，point績點TC(term,class,cno,tno,period)該表是教師上課情況表 其中：list序號，term學(xué)期；class班級；cno課程號；tno教師編號； period學(xué)時Teachers (tno,tname,ps,wday,dept,pay,marry,resume)該表是教師基本情況表：其中tno教師編號；tname教師姓名；ps職稱； wday入校日期；dept系別；pay工資；marry婚否；resume簡歷（1）查詢選修課程學(xué)生的學(xué)號、姓名、所選課程號、課程名稱以及所取得的成績（2）查詢每一門課程及其先修課程，查詢結(jié)果中顯示課程號、課程名和先修課程名（3）查詢和“劉濤”在一個班級的學(xué)生的信息思考：該查詢可用幾種方法實現(xiàn)？（4）查詢選修了計算機基礎(chǔ)課的學(xué)生的學(xué)號、姓名（分別用連接查詢和嵌套查詢的方法實現(xiàn)）（5）查詢沒有選修課程的學(xué)生的信息（6）查詢其他班中比“03計算應(yīng)用1”所有學(xué)生入學(xué)成績高的學(xué)生的姓名和入學(xué)成績（用兩種方法實現(xiàn)）（7）查詢每個學(xué)生超過該門課程平均成績的學(xué)號（8）查詢至少選修了“0311102”學(xué)生所選全部課程的學(xué)生的學(xué)號（9）查詢既選修了“計算機基礎(chǔ)”又選修了“C語言基礎(chǔ)”的學(xué)生的學(xué)號3、在jxgl中完成下列數(shù)據(jù)更新（1）向course表添加一個新的元組。（2）將“0301102”學(xué)生所選的“0000002”課程的成績改為100分，績點改為2（3）刪除（1）中在course表中添加的記錄（4）統(tǒng)計每門課程的平均成績并將統(tǒng)計結(jié)果存入到j(luò)xgl數(shù)據(jù)庫中思考：如何將students表中班級名 “03計算應(yīng)用1”改為“03計算機應(yīng)用1”(可調(diào)用函數(shù)完成)4、查詢教師的編號、姓名、上課的課程號、課程名稱及班級。將該查詢定義為一個視圖。5、在第4題定義的視圖中完成下列查詢（1）查詢“姚明”所上課程和班級。（2）統(tǒng)計每一位教師上課的數(shù)量6、附加題，做了有加分利用數(shù)據(jù)庫ReadBook完成下列查詢及視圖設(shè)計。（在SQL SERVER2005上附加數(shù)據(jù)庫ReadBook）（1）設(shè)計一個查詢，要求用連接和嵌套兩種方式完成。（2）設(shè)計一個查詢，要求用外連接的方式完成。（3）設(shè)計一個查詢，要求用帶exists或not exists相關(guān)子查詢完成（4）設(shè)計一個集合查詢（5）設(shè)計一個帶復(fù)合條件連接的查詢（6）設(shè)計一個視圖，并在該視圖中完成添加、修改、刪除數(shù)據(jù)的操作。（7）設(shè)計一個視圖，并在該視圖完成兩個統(tǒng)計：a.每位讀者以及他的借書數(shù)量，按借書數(shù)量作降序排列 b.每本書以及被借出的次數(shù)，按借出次數(shù)作降序排列。注意在實驗報告中說明查詢的目的和對應(yīng)的語句。三、實驗過程及數(shù)據(jù)記錄步驟2（1）查詢選修課程學(xué)生的學(xué)號、姓名、所選課程號、課程名稱以及所取得的成績 查詢結(jié)果如下： （1） 查詢每一門課程及其先修課程，查詢結(jié)果中顯示課程號、課程名和先修課程名 查詢結(jié)果如下： （3）查詢和“劉濤”在一個班級的學(xué)生的信息 思考：該查詢可用幾種方法實現(xiàn)？1.嵌套連接2.多表連接 查詢結(jié)果如下： 1.嵌套連接 2.多表連接 （4） 查詢選修了計算機基礎(chǔ)課的學(xué)生的學(xué)號、姓名（分別用連接查詢和嵌套查詢的方法 實現(xiàn)） 查詢結(jié)果如下： 1.連接查詢 2.嵌套查詢 （5） 查詢沒有選修課程的學(xué)生的信息 查詢結(jié)果如下：select * from Students where sno not in (select sno from sc) （6）查詢其他班中比“03計算應(yīng)用1”所有學(xué)生入學(xué)成績高的學(xué)生的姓名和入學(xué)成績（用兩種方法實現(xiàn)） 查詢結(jié)果如下： 1.方法一：使用ALL關(guān)鍵字 2.方法二：調(diào)用內(nèi)置函數(shù)MAX（） （7）查詢每個學(xué)生超過該門課程平均成績的學(xué)號 查詢結(jié)果如下： （8）查詢至少選修了“0311102”學(xué)生所選全部課程的學(xué)生的學(xué)號 查詢結(jié)果如下： （9）查詢既選修了“計算機基礎(chǔ)”又選修了“C語言基礎(chǔ)”的學(xué)生的學(xué)號 查詢結(jié)果如下： 步驟3（1） 向course表添加一個新的元組。 查詢結(jié)果如下： （2） 將“0301102”學(xué)生所選的“0000002”課程的成績改為100分，績點改為2 查詢結(jié)果如下： （3） 刪除（1）中在course表中添加的記錄 查詢結(jié)果如下： （4） 統(tǒng)計每門課程的平均成績并將統(tǒng)計結(jié)果存入到j(luò)xgl數(shù)據(jù)庫中 查詢結(jié)果如下： 思考：如何將students表中班級名 “03計算應(yīng)用1”改為“03計算機應(yīng)用1”(可調(diào)用函數(shù)完成) 查詢結(jié)果如下： 步驟4查詢教師的編號、姓名、上課的課程號、課程名稱及班級。將該查詢定義為一個視圖。 查詢結(jié)果如下： create view teacher_c(tno,tname,cno,cname,class) as select TC.tno ,tname,TC.cno,cname,class from Teachers,TC,Course WHERE Teachers.tno=TC.tno and TC.cno=Co go 步驟5在第4題定義的視圖中完成下列查詢（1） 查詢“姚明”所上課程和班級。 查詢結(jié)果如下： select cname,class from teacher_c where tname=姚明 Go （2） 統(tǒng)計每一位教師上課的數(shù)量 查詢結(jié)果如下： select tno,tname,count(cno) as 上課數(shù)量 from teacher_c group by tno,tname 附加題（1） 設(shè)計一個查詢，要求用連接和嵌套兩種方式完成。 查詢讀了”三重門”的讀者的姓名1. 連接查詢： select rname from readers,L_R,BOOKS where readers.rno=L_R.rno and l_r.bookid=books.bookid and bookname=三重門 2. 嵌套查詢： select rname from readers where rno in(select rno from l_r where bookid in(select bookid from books where bookname=三重門) 兩種查詢相同，結(jié)果如下： （2） 設(shè)計一個查詢，要求用外連接的方式完成。 查詢借過書的人的信息 select * from readers right outer join l_r on readers.rno=l_r.rno（3）設(shè)計一個查詢，要求用帶exists或not exists相關(guān)子查詢完成 查詢王小丫借過的書名 查詢結(jié)果如下： （4）設(shè)計一個集合查詢：查詢性別為男性或者spet=信管的學(xué)生信息 查詢結(jié)果如下： （5） 設(shè)計一個帶復(fù)合條件連接的查詢 查詢每個學(xué)生借的書名字和他的姓名 查詢結(jié)果如下： （6） 設(shè)計一個視圖，并在該視圖中完成添加、修改、刪除數(shù)據(jù)的操作。 將查詢學(xué)生的學(xué)號、姓名、性別、出生日期定義為一個視圖。 查詢結(jié)果如下： CREATE VIEW readers_students as select rno,rname,rsex,bday from readers 添加一個學(xué)號為042011、姓名為李偉、性別為男、出生日期1986/7/7 0:00:00的學(xué)生的信息。查詢結(jié)果如下： insert into readers_students(rno,rname,rsex,bday)values(04211,李偉,男,1986/7/7 0:00:00)將學(xué)號為04201的學(xué)生姓名改為林麗查詢結(jié)果如下：update readers_studentsset rname=林麗where rno=04201 （7） 設(shè)計一個視圖，并在該視圖完成兩個統(tǒng)計：a.每位讀者以及他的借書數(shù)量，按借書數(shù)量作降序排列 b.每本書以及被借出的次數(shù)，按借出次數(shù)作降序排列。4、 實驗結(jié)果分析 1. 使用選擇語