1、河南工業大學實驗報告課程數據庫原理實驗名稱實驗二交互式院系：_信息科學與工程學院專業班級：計科姓名：_學號：指導老師：孫宜貴日期：實驗目的熟悉SQL的數據定義、數據操縱功能；掌握利用SQL語句進行多表內外連接查詢和嵌套查詢的方法；掌握GROUPBY子句、HAVING子句、ORDERBY子句的用法；掌握（NOT）IN、（NOT）EXISTS等謂詞的用法；掌握SUM、AVG、COUNT、MAX、MIN等集合函數的用法；掌握SQL語句中數據更新相關操作；熟悉視圖基本操作：定義、查詢、更新視圖。實驗內容及要求本實驗的表結構如下所示：Student（sno,sname,sex，sage，dept,old

2、grade,advisor）Teacher（tno,tname,dept,salary,title）Course（cno,cname,descry,dept,credit）SC（sno,cno,grade）以上數據庫表的含義為：Student學生：sno（學號），sname（學生姓名），sex（性別），sage（年齡），dept（所在院系），oldgrade（高考成績），advisor（導師）Teacher教師：tno（教師編號），tname（教師姓名），dept（所在院系）,salary（工資），title（職稱）Course一課程：eno（課程號），cname（課程名），descry（課程

3、說明），dept（開課院系），credits（學分）SC一成績：sno（學號），eno（課程號），grade（成績）根據上述情況完成：使用SQL語句定義上述各表。查詢選修了105323號課程的學生姓名。查詢所有的學生及其選課信息。分別利用內連接、左連接和右連接實現上題，觀察異同。5.查詢工資不在2000到3000的教師姓名，按工資排序。查詢高考平均成績大于700分的學生所在院系。查詢所有院系的平均高考成績，并按平均成績排序。查詢所有學生的學號及成績，并計算總成績。查詢每個院系教師的最高工資。10.把Teacher表中dept字段值為“計算機科學系”的記錄修改為“信息科學與工程學院”。11新.建

4、一個視圖，并依此查詢課程編號為205323，成績大于80分的學生名。為Teacher表增加一個字段，電話(Phone),數據類型char(12),允許空。實驗過程及結果1.定義上述各表的SQL語句是：CREATETABLEteacher(tnochar(3)primarykey,tnamechar(8),deptvarchar(20),salaryint,titlechar(6);CREATETABLEstudent(snochar(7)primarykey,snamechar(8),sexchar(2),deptvarchar(20),oldgradeint,tnochar(3);CREAT

5、ETABLECourse(cnochar(6)primarykey,cnamevarchar(20),descryvarchar(50),deptvarchar(20),creditfloat);CREATETABLESC(snochar(7),cnochar(6),gradeint,primarykey(sno,cno);各表創建完成后執行結果如下圖所示：圖1引入腳本，將各表數據信息錄入，以便完成以下的要求。查詢選修了105323號課程的學生姓名，SQL語句是：selectsnamefromstudent，coursewherecno=105323；或者：selectsnamefromstu

6、dent，coursewherecnolike105323；運行結果如圖2所示：3.查詢所有的學生及其選課信息，SQL語句是:selectstudent.*,sc.*fromstudent,scwherestudent.sno=sc.sno;運行結果如圖3所示:selectstude:nt*fsc*-3C5110；f2?omstude:nt,sc軒h皂工圮stud皂口匸ano1結果|L插消息|snc3snamesexdeptIdgradetnosnocnogradeJ_20D70D1李明胃管理學卩完6835512m70D11D5321NULL12-20D70D1李期管理學卩完5512M70D1

7、1D5322771320D70D1李明胃管理學卩完5512M70D11D532367|420D7D02張麗女管理學卩完5625522M70021D53217Q1520D7OT2張麗女管理學卩完M255224KJ7W21D5322671620D7OT2張麗女管理學卩完5625522M7WJ21D532396720D7OT3胡美美女管理學卩完712353207W)31D53217320D7D03胡美美女管理學卩完7125532M70031D532283圖34.分別利用內連接、左連接和右連接實現上題，觀察異同。內連接:相比等值連接，內鏈接把目標列中重復的屬性列去掉啦。用內連接查詢所有的學生及其選課信

8、息，SQL語句是：selectstudent.sno,sname,sex,dept,oldgrade,tno,cno,gradefromstudent,scwherestudent.sno=sc.sno;運行結果如圖4所示：Aselect：studentsrn蘆呂口aire,sex,dept-oldgradertnorcnorgradefromsmden匸扌已uwlierestudent巴口口=sc.sno;二結果|Lji消息|snosnarnesex|deptoldgradetnoenograde1李明管理學除68SB511D5321NULL7._2M7C-D1李明管理學除E8S-S5110

9、532277j32D07M1李明管理學除85-B5110532367|42D07M2張麗女管理學卩完562B521053217DL2D07M2張麗女管理學険562B5210532267h2D07M2張麗管理學除M2S5210532396|720070&3胡美美管理學除712B53-1D532179S2M7C-&3胡美美管理學除712S53-10532283圖4左連接：列出左邊關系中所有的元祖，運行結果如下圖5所示：selects匸口dentbhq,Bnaur.e,sex,dept：,aldgrdd_e,匸n口,eno,qrddefrom呂匸口dentleft口terjoinsasc.sno-o

10、n(etudent：.sno二結果|L3消息|anosnamesexJdeptoldgradetnoCTIOgrade12D07W1李明男管理學院6BSS511D-5321NULL|22fl07(H)1李明男管理學疏6&SS511D-532277|32fl07(H)1李明男管理學院6BSS511D-5323-67|42007M2弓總麗女管理學琮M2S5210-532170|52flD7(H2弓總麗女管理學琮5fi2S5210-53226762flO7M2兆麗女管理學除5fi2S5210-5323-9E17-2D07M3胡美美女管理學院712S531D-532179|8P2D07M3胡美美女管理

11、學疏712S531D-532285圖5右連接：列出右邊關系中所有的元祖，運行結果如下圖6所示：selectsc.sno,cno,grade,sname,sex,dept,oldgrade,tnofromstudentrightouterjoinsc*1on(student.sno=sc.sno);selectsc.belo,eno,grade,snaiTxe-Bexroldgradertznofranis匸口dent;rightouterjoinscSC.5ELQcm(student-sno二結果I也消息IsnoonegradesnamesexdeptIdgradetna12007H11053

12、21NULL李明菅理學院6BS851210532277李明菅理學壇6S8851324KJ7W110-5323-R7李明里管理學壇6SBS5142M7M21053217D強麗管理學曉5S285252007M21053227兆麗管理學曉56285262007M2105323-9強麗女菅理學院56285272007MB10532175胡美美菅理學院712853-3P2007M3105322SS胡美美菅理學壇712853圖6查詢工資不在2000到3000的教師姓名，按工資排序，SQL語句是:Selecttnamefromteacherwheresalary3000orderbysalary;或者:Se

13、lecttnamefromteacherwheresalarynotbetween2000and3000orderbysalary;運行結果如下圖7所示:二結果|由消息|*1*1圖7運行結果如下圖 所示：查詢高考平均成績大于700分的學生所在院系，SQL語句及運行結果如下圖8所示：圖8查詢所有院系的平均高考成績，并按平均成績排序，語句是:selectdept,avg(oldgrade)fromstudentgroupbydeptorderbyavg(oldgrade);運行結果如下圖9所示：出亡工亡匚匸dept,avg(old.gradeifromstudentgroupkydeptoTdez

14、?kyavc(o1dgrade;l結果I勺消息Idept1無列掃11村料工程學院620|2信息科學弓工程學喘62fi|3管理學院64查詢所有學生的學號及成績，并計算總成績，SQL語句是：Selectstudent.sno,count(grade)fromstudent,scwherestudent.sno=sc.snogroupbystudent.sno;圖10查詢每個院系教師的最高工資，SQL語句是：selectdept,MAX(salary)fromteachergroupbydept;運行結果如下圖11所示：selectdept,IalAX(salary)z?Qniteachergroi

15、ipbwdept;方結果啟消息1dept1無列名123村料工程學院管理學院信息科學弓工程學院36聊圖11把表中dept字段值為“計算機科學系”的記錄修改為“信息科學與工程學院”，SQL語句是：updateteachersetdept=信息科學與工程學院wheredept=計算機科學;運行結果如下圖 所示：查詢修改后的運行結果如下圖12所示：曰updateteactier癢t馬觸=信息赴學與孑程學院-whele翡觸=吾算機禪學縈；一selec匸*fzconciteacher;鉗-3結果I乜消息Itnotnamedeptsalarirtitle1S51胡玫信息科學與工程學院1&DD助敎|2應未紅信

16、息科學與工程學院2&DD副埶授|3趙剛信息科學與工程學院3?DD|4爐信息科學與工程學院2200副鞋授|5&61黎東信息科學與工程學曉1910講師6SG2周成信息科學與工程學院2830副埶授|7SS2郝思（圭信息科學與工程學院3DD圖12新.建一個視圖，并依此查詢課程編號為205323，成績大于80分的學生名。新建視圖的語句是：createviewISstudentasselectsname,cno,gradefromstudent,sc;運行結果如下圖13所示：aieateview15student.as一select：snarrie聲eno,gradefronoistudent,sc;1|

17、li消息I命令已成功完成。圖13查詢語句是：selectsnamefromISstudentwherecnolike205323andgrade80;5皂lect：511己m亡fromISsfadeniL2053231andgradeSO;一wHi皂工已enolikem2、卜為eacher表增加一個字段，電話(Phone),數據類型char(12),允許空。增加字段的語句及增加后的查詢結果如下圖、圖所示：altez?taLbleteacheiLaddphonechar(20;|屆消息I命令已成功完成。select*fronnteacher;d-J結果|也消息|tnotnamedeptsalaryrtitle-phene1851胡玫信息科學與工程學院1助教NULL2S52朱紅信息科學與工程學院2500畐懺槓NULLI3-853信息科學與工程學院32D0NULL|4S54信息科學與工程學院22D0畐燉陵NULL|5旳黎東信息科學與工程學院1310講師NULL|6&62周成信息科學與工程學院2O畐燉懂NULL|7&63郝思佳信息科學與工程學院300-NULLS&64信息科學與工程學院1500助教NULLfe-871馬00信