實(shí)驗(yàn)四 表的查詢操作(4學(xué)時(shí))

PAGEPAGE1實(shí)驗(yàn)四表的查詢操作(4學(xué)時(shí))【實(shí)驗(yàn)?zāi)康摹苛私釹QL語(yǔ)言的使用，進(jìn)一步理解關(guān)系運(yùn)算，鞏固數(shù)據(jù)庫(kù)的基礎(chǔ)知識(shí)?！緦?shí)驗(yàn)要求】掌握利用Select語(yǔ)句進(jìn)行各種查詢操作：?jiǎn)伪聿樵?、多表連接及查詢、嵌套查詢、集合查詢等?！緦?shí)驗(yàn)內(nèi)容】在實(shí)驗(yàn)三創(chuàng)建并插入數(shù)據(jù)的表（Student，Course，SC，Teacher，TC）的基礎(chǔ)上，完成以下操作。1.對(duì)實(shí)驗(yàn)步驟中所給示例進(jìn)行驗(yàn)證。2.參考所給示例，完成下列各種查詢操作。（1）將教師‘羅莉’的名字改為‘羅莉莉’。（2）將兩個(gè)同學(xué)（數(shù)據(jù)自己臨時(shí)設(shè)置，用后即刪除）的兩門課程的成績(jī)以運(yùn)行sql程序文件的形式插入score表中。該題用以驗(yàn)證、理解和掌握關(guān)系模型的完整性規(guī)則；（3）求每門課的平均成績(jī)，并把結(jié)果存入average表（自行設(shè)計(jì)并創(chuàng)建）；（4）將學(xué)生“馬麗”的出生日期改為“1982.8.20”（5）將所有學(xué)生的zipcode屬性列值填補(bǔ)上；（6）將average表中的所有課程的平均成績(jī)置零；（7）刪除average表中的課程號(hào)為‘c007’的平均成績(jī)記錄；（8）刪除所有average表中平均成績(jī)記錄；（9）建立一個(gè)臨時(shí)學(xué)生信息表（tstudent），刪除該表中的學(xué)號(hào)含‘101’的所有學(xué)生記錄。（10）查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名；（11）查詢?nèi)w學(xué)生的學(xué)號(hào)、姓名、所屬班級(jí)；（12）查詢?nèi)w學(xué)生的詳細(xì)記錄；（13）查詢?nèi)w學(xué)生的姓名及其年齡；（14）查詢?nèi)w學(xué)生的姓名、出生年份；（15）查詢所有修過(guò)課的學(xué)生的學(xué)號(hào)；（16）查詢“計(jì)61”班全體學(xué)生名單；（17）查詢查詢所有年齡在23歲以下的學(xué)生姓名及其年齡；（18）查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)；（19）查詢年齡在20至22歲之間的學(xué)生姓名、班級(jí)和年齡；（20）查詢年齡不在20至22歲之間的學(xué)生姓名、班級(jí)和年齡；（21）查詢“計(jì)11”和“計(jì)61”班的學(xué)生的姓名和班級(jí)信息；（22）查詢既不是“計(jì)11”也不是“計(jì)61”班的學(xué)生的姓名和班級(jí)信息；（23）查詢學(xué)號(hào)為“04262002”的學(xué)生的詳細(xì)情況；（24）查詢學(xué)號(hào)以“04262”打頭的學(xué)生信息；（25）查詢所有姓“張”學(xué)生的學(xué)號(hào)、姓名、性別、年齡；（26）查詢名字中第二個(gè)字有“?！弊值膶W(xué)生的學(xué)號(hào)、姓名、性別、年齡；（27）查詢所有不姓“劉”學(xué)生的姓名；（28）查詢課程號(hào)以“C”開頭的最后兩個(gè)字母為“05”（29）某些學(xué)生選修某門課程后沒(méi)有參加考試，所以有選修課記錄，但沒(méi)有考試成績(jī)，試查找缺少考試成績(jī)的學(xué)生和相應(yīng)的課程號(hào)；（30）查找全部有成績(jī)記錄的學(xué)生學(xué)號(hào)、課程號(hào)；（31）查找“計(jì)91”班年齡在22歲以下的學(xué)生學(xué)號(hào)、姓名；（32）查找選修了“C001”號(hào)課程的學(xué)生學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)降序排序；（33）查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在班級(jí)升序排列，對(duì)同一班級(jí)中的學(xué)生按年齡降序排列；（34）查詢學(xué)生總?cè)藬?shù)；（35）查詢選修了課程的學(xué)生人數(shù)；（36）在所有課程中查詢最高分的學(xué)生學(xué)號(hào)和成績(jī)；（37）查詢學(xué)習(xí)“C001”課程的學(xué)生最高分?jǐn)?shù)；（38）計(jì)算各個(gè)課程號(hào)與相應(yīng)的選課人數(shù)；（39）查詢“計(jì)91”班選修了兩門課程以上的學(xué)生學(xué)號(hào)、姓名；（40）自然連接student和score表；（41）使用自身連接查詢每一門課程的間接先行課（即先行課的先行課）（42）使用復(fù)合條件連接查詢選修“c001”號(hào)課程且成績(jī)?cè)?0分以上的所有同學(xué)；（43）使用復(fù)合條件連接查詢每個(gè)學(xué)生選修的課程名及其成績(jī)；（44）查詢選修了全部課程的學(xué)生；（45）查詢至少選修課程C001的學(xué)生選修的全部課程的學(xué)號(hào)、姓名；（46）查詢所有選修了C001號(hào)課程的學(xué)生學(xué)號(hào)、姓名；（47）查詢選修了課程C001或c007的學(xué)生學(xué)號(hào)、姓名；（48）查詢“計(jì)91”班的學(xué)生及年齡不大于23歲（現(xiàn)有年齡）的學(xué)生；（49）查詢既選修了課程C001又選修了課程c007的所有學(xué)生學(xué)號(hào)、姓名；（50）查詢選修了課程名為“數(shù)據(jù)庫(kù)原理”的學(xué)生的學(xué)號(hào)、姓名、性別、年齡；（51）查詢其他班中比“計(jì)91”班所有學(xué)生年齡都小的學(xué)生名單；（52）查詢與“夏天”在同一個(gè)班學(xué)習(xí)的學(xué)生學(xué)號(hào)、姓名、性別、年齡；（53）建立“計(jì)91”班學(xué)生的視圖；（54）建立“計(jì)911”班學(xué)生的視圖，并要求進(jìn)行修改與插入時(shí)，仍須保證該視圖只有“計(jì)91”班學(xué)生；（55）建立“計(jì)91”班選修了“C001”課程的學(xué)生的視圖，定義視圖名為“v_info_C001_student1”（56）建立“計(jì)91”班選修了“C001”課程且成績(jī)?cè)?0分以上的學(xué)生的視圖，定義視圖名為“info_c001_student2”（57）定義一個(gè)反映學(xué)生年齡的視圖，定義視圖名為“v_birth_student”；（58）將學(xué)生表中所有女生記錄定義為一個(gè)視圖，視圖名為“v_female_student”；（59）將學(xué)生的學(xué)號(hào)及其平均成績(jī)定義為一個(gè)視圖，視圖名為“v_average_student”；（60）刪除表student后，重新建student表，再建視圖“V_CS_student1”（61）在“計(jì)91”班學(xué)生視圖中找出年齡小于22歲（現(xiàn)在的年齡）的學(xué)生；（62）利用視圖查詢“計(jì)91”班選修了“C001”課程的學(xué)生；（63）通過(guò)（53）中的“計(jì)91”班視圖修改某個(gè)學(xué)生的名字；（64）通過(guò)（54）中的“計(jì)91”班視圖，插入一個(gè)新學(xué)生記錄。（65）通過(guò)（54）中的“計(jì)91”班視圖，刪除一個(gè)學(xué)生記錄。3.自行設(shè)計(jì)不同情況的DML語(yǔ)句（不少于100條語(yǔ)句），并進(jìn)行實(shí)驗(yàn)驗(yàn)證?！緦?shí)驗(yàn)步驟】一、單表查詢1．簡(jiǎn)單查詢打開查詢分析器，根建立teacher表，并加入數(shù)據(jù)。從teacher表中分別檢索出教師的所有信息，以及僅查詢教工號(hào)、姓名和職稱。語(yǔ)句如下：select*fromteacherselecttno,tnamefromteacher如要查詢時(shí)改變列標(biāo)題的顯示，則從teacher表中分別檢索出教師教工號(hào)、姓名、電子郵箱信息并分別加上‘教師姓名’、‘教工號(hào)’、’電子郵箱’等標(biāo)題信息。selecttno工號(hào),tname姓名,temail電子郵箱fromteacher使用TOP關(guān)鍵字：分別從teacher中檢索出前2條及前面67%的教師的信息。selecttop2*fromteacherselecttop67percent*fromteacher使用DISTINCT關(guān)鍵字：從teacher表中檢索出教師的職稱并且要求顯示的職稱不重復(fù)。selectdistincttpositionfromteacher2．用計(jì)算列：將teacher表中各教師的姓名、教工號(hào)及工資按95%發(fā)放的信息，第2條語(yǔ)句將工資按95%發(fā)放后列名該為‘預(yù)發(fā)工資’。語(yǔ)句如下：selecttnotname,tsalary*0.95fromteacherselecttno工號(hào),tname姓名,tsalary*0.95AS預(yù)發(fā)工資fromteacher3．使用ORDERBY子句對(duì)查詢的結(jié)果進(jìn)行排序使用ORDERBY語(yǔ)句可以對(duì)查詢的結(jié)果進(jìn)行排序，ASC、DESC分別是升序和降序排列的關(guān)鍵字，系統(tǒng)默認(rèn)的是升序排列。從teacher表中查詢工資大于2800的教師的教工號(hào)、姓名，并按升序排列，語(yǔ)句如下：selecttno,tnamefromteacherWHEREtsalary>2800orderbytsalaryASC4．條件查詢⑴使用關(guān)系運(yùn)算符：從teacher表中查詢出工作量大于288的教師資料，語(yǔ)句如下：select*fromteacherWHEREtamount>288orderbytamountDESC⑵使用BETWEENAND謂詞：從teacher表中查詢出工作量界于144和288之間的教師資料，語(yǔ)句如下：select*fromteacherWHEREtamountbetween144and288⑶使用IN謂詞：從teacher表中查詢出職稱為“教授”或“副教授”的教師的教工號(hào)、教師姓名、職稱及家庭住址，語(yǔ)句如下：selecttno,tname,tposition,taddressfromteacherWHEREtpositionin('教授','副教授')⑷使用LIKE謂詞：從teacher表中分別檢索出姓’王’的教師的資料，或者姓名的第2個(gè)字是’莉’或’軒’的教師的資料，語(yǔ)句如下：select*fromteacherWHEREtnamelike'王%'select*fromteacherWHEREtnamelike'_[莉，軒]%'

二、多表查詢數(shù)據(jù)庫(kù)各表中存放著不同的數(shù)據(jù)，用戶經(jīng)常需要用多個(gè)表中的數(shù)據(jù)來(lái)組合提煉出所需要的信息，如果一個(gè)查詢需要對(duì)多個(gè)表進(jìn)行操作，就稱為關(guān)聯(lián)查詢，關(guān)聯(lián)查詢的結(jié)果集或結(jié)果表稱為表之間的連接。關(guān)聯(lián)查詢實(shí)際上是通過(guò)各各表之間共同列的關(guān)聯(lián)來(lái)查詢數(shù)據(jù)的，它是關(guān)系數(shù)據(jù)庫(kù)查詢最基本的特征。1．SQL2000兼容2種連接形式：用于FROM子句的ANSI連接語(yǔ)法和用于WHERE子句的SQLSERVER連接語(yǔ)法形式。從student、course和SC三張表中檢索學(xué)生的學(xué)號(hào)、姓名、學(xué)習(xí)課程號(hào)、學(xué)習(xí)課程名及課程成績(jī)，語(yǔ)句如下：selectstudent.sno,sname,cno,gradefromstudentinnerjoinSConstudent.sno=SC.snoselectstudent.sname,sc.gradefromstudent,scWHEREstudent.sno=sc.snoselectstudent.sno,student.sname,o,ame,sc.gradefromstudent,sc,courseWHEREstudent.sno=sc.snoando=o２．使用UNION子句進(jìn)行查詢使用UNION子句可以將一個(gè)或者多個(gè)表的某些數(shù)據(jù)類型相同的列顯示在同一列上。如從teacher表中列出教工號(hào)、姓名并從student表中列出學(xué)號(hào)及學(xué)生姓名，語(yǔ)句及查詢結(jié)果如下：selectsnoAS學(xué)號(hào)或工號(hào),snameAS姓名fromstudentunionselecttno,tnamefromteacher3．用GROUP子句進(jìn)行查詢 如果要在數(shù)據(jù)檢索時(shí)對(duì)表中數(shù)據(jù)按照一定條件進(jìn)行分組匯總或求平均值，就要在SELECT語(yǔ)句中與GROUPBY子句一起使用集合函數(shù)。使用GROUPBY子句進(jìn)行數(shù)據(jù)檢索可得到數(shù)據(jù)分類的匯總統(tǒng)計(jì)、平均值或其他統(tǒng)計(jì)信息。⑴使用不帶HAVING的GROUPBY子句。使用不帶HAVING的GROUPBY子句匯總出SC表中的學(xué)生的學(xué)號(hào)及總成績(jī)，語(yǔ)句如下：select'學(xué)號(hào)'=sno,'總成績(jī)'=SUM(Grade)fromSCgroupbySno⑵使用帶HAVING的GROUPBY子句。使用帶HAVING的GROUPBY子句匯總出SC表中總分大于450分的學(xué)生的學(xué)號(hào)及總成績(jī)，語(yǔ)句如下：select'學(xué)號(hào)'=sno,'總成績(jī)'=SUM(Grade)fromSCgroupbySnoHavingSUM(Grade)>1604．用COMPUTE和COMPUTEBY子句進(jìn)行查詢使用COMPUTE和COMPUTEBY既能瀏覽數(shù)據(jù)又能看到統(tǒng)計(jì)的結(jié)果。⑴用COMPUTE子句匯總出SC表中每個(gè)學(xué)生的學(xué)號(hào)及總成績(jī)，語(yǔ)句如下：select'學(xué)號(hào)'=sno,'成績(jī)'=GradefromSCorderbysnoCOMPUTESUM(Grade)⑵用COMPUTEBY子句按學(xué)號(hào)匯總出SC表中每個(gè)學(xué)生的學(xué)號(hào)及總成績(jī)，語(yǔ)句如下：select'學(xué)號(hào)'=sno,'成績(jī)'=GradefromSCORDERBYSnoCOMPUTESUM(Grade)bysno觀察執(zhí)行COMPUTE和COMPUTEBY子句的結(jié)果有什么不同？5．嵌套查詢(1)使用IN或NOTIN關(guān)鍵字使用IN關(guān)鍵字查詢出j10011班所有男生的學(xué)號(hào)、課程號(hào)及相應(yīng)的成績(jī)，語(yǔ)句如下：selectSC.sno,SC.cno,SC.gradeFROMSCWHEREsnoIN(SELECTsnoFROMstudentWHERESclass=’j10011’ANDSsex='男')使用IN關(guān)鍵字查詢出與教師不同名的學(xué)生(學(xué)號(hào)，姓名)，語(yǔ)句如下：selectsno學(xué)號(hào),sname姓名fromstudentwheresnamenotin(selecttnamefromteacher)(2)使用EXISTS或NOTEXISTS關(guān)鍵字。使用EXISTS關(guān)鍵字查詢出‘j10011’班的學(xué)生的學(xué)號(hào)、課程號(hào)及相應(yīng)的成績(jī)，語(yǔ)句如下：SELECTSC.sno