實(shí)驗(yàn)三、SQL-數(shù)據(jù)查詢(xún)語(yǔ)言.doc

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