數(shù)據(jù)庫1數(shù)據(jù)查詢

1、實(shí)驗(yàn)一數(shù)據(jù)庫查詢課程名稱：數(shù)據(jù)庫原理實(shí)驗(yàn)實(shí)驗(yàn)類型：驗(yàn)證型實(shí)驗(yàn)名稱數(shù)據(jù)庫查詢學(xué)時4學(xué)時實(shí)驗(yàn)?zāi)康模菏箤W(xué)生掌握SQL Server Query Analyzer的使用方法，加深對SQL和T-SQL語 言的査詢語句的理解。熟練掌握表的基本查詢，連接查詢和嵌套查詢，以及掌握 數(shù)據(jù)排序和數(shù)據(jù)分組的操作方法。實(shí)驗(yàn)原理：SELECT ALLIDISTINCT 目標(biāo)列表達(dá)式,v目標(biāo)列表達(dá)式aFROM 表名或視圖名,v表劃或視圖坍刃（WHERE v條件表達(dá)式GROUP BY v列名 1 HAVING 條件表達(dá)式（order by 列名 2 ASCIDESC；實(shí)驗(yàn)方法：將查詢需求用T-SQL語言表示：在SQL Se

2、rver Query Analyzer的輸入?yún)^(qū)中輸 入T-SQL査詢語句：設(shè)置Query Analyzer的結(jié)果區(qū)為Standard Execute（標(biāo)準(zhǔn)執(zhí)行） 或Execute to Grid （M格執(zhí)行）方式：發(fā)布執(zhí)行命令，并在結(jié)果區(qū)中査看查詢結(jié)果; 如果結(jié)果不正確，要進(jìn)行修改，直到正確為止。實(shí)驗(yàn)內(nèi)容：1.分別用帶DISTINCT和不帶DISTINCT關(guān)鍵字的SELELCT在student中進(jìn)行 査詢.帶 distinct:Select class_idfrom studentclassd1I g994022g994023g994024g994025g994026g994027g99402

3、8g994029g9940210g9940311g9940312g9940313g99403不帶 distinct:select distinct class_id from student95%發(fā)放后的列名改為'預(yù)發(fā)工資'select teacher_idz teacher_namez salary*0 95 as預(yù)發(fā)工資 from teacherleacherjdteacher_name預(yù)發(fā)工資1depOIOO!王敬遠(yuǎn)1995.01902d 即 02_0Q康輝2S45.Q7603dep03_001董一平2185.34204depQ3_OO2潘惠171Q.34205d 即 G

4、4.OQ1紀(jì)云1995.34206depQ4_OQ2章紅3325.40357depQ4_OO3李偉3040.Q1908depQ4_0Q4嚴(yán)為1140.34209depQ4_OG5喬紅1710.67453.查詢course表中所有學(xué)分大于2并且成績不及格的學(xué)生的信息. select distinot studentfrom stucourse r scwhere student student_id=sc student_idand sc course_id=course course_idand course credit>2and sc.grade<60studentdstude

5、nt_namesexa.classddepartmentjdaddress1j gS940203曹雨120g95402Dep_03南京玄曲區(qū)林蔭犬道4導(dǎo)2gSS40204芮華119g99402Dep_01江蘇儀征市吉年路6號3g9S4&205李麗01Sg99402De：p.O4江蘇儀征市者年東路1號4.查詢學(xué)分在48之間的學(xué)生信息.（用between.and和復(fù)合條件分別實(shí)現(xiàn)） select distinct student*from student,course,scwhere student student_id=sc student_idand sc coursm_id二cour

6、se course_idand course creditbetween 4 and 8studentjdstudent_namesex弓classjddepartmentjdaddress1| g9W0201 |i張虹113g99402Dep_01南京市範(fàn)樓區(qū)古平崗3號2gS3402Q2林紅019g99402Dep_02揚(yáng)州市五亭街41號3©9940203曹雨12Qg99402Dep_03南京玄屁區(qū)林蔭大道4號4g9940204芮華115g99402DepJH江蘇儀征市書年路6號599940205李麗012g99402Dep_04江蘇儀征市書年東路1號6g33402Q6林海國113

7、g99402Dep_04北京市中關(guān)村太平北路739940207李紅021g99402Depj04南京虎訴北路4猖5.從studenLcourse表中査詢出學(xué)生為“g9940201”，=9940202”的課程號、 學(xué)生號以及學(xué)分，并按學(xué)分降序排列(用in實(shí)現(xiàn))select *from scz coursewhere studenin(*g9940201 ,g9940202,)and course course_id=sc coursm_2dorder by course credit deso結(jié)果消息studerrdcourdgradecourdCDurse_n3mebookdtotal_per

8、iorweek_periDrcredit1；g9940201cfep(M_s00189DepO4_s(X)1數(shù)據(jù)庫開發(fā)技術(shù)DepD4_s001_0130552g9940202cfep(M_sOO173DepO4_s(X)1數(shù)據(jù)庫開發(fā)技朮DepD4_s£X)1_0180553g9940201cfep(M_bX)183CepO4_b(X)1計(jì)算機(jī)基帀出DepD4_b001_02S8444g9940202dsp04_b0178Dcp04_bD01計(jì)算機(jī)基礎(chǔ)Dcpo4_b£X1_02se446. 從teacher表中分別檢索出姓王的教師的資料，或者姓名的第2個字是遠(yuǎn)或輝 的教師的資

9、料select *from teacherwhere teacherenamelike王電'or teacherenameor teacher_name like 二輝結(jié)果厶消息teacherjd teacher_ramecfepartmentjdprofessionsex phoneaddresssalaryPostalcode1 i dep01_001 ：王敬遠(yuǎn)Dep_01副教授16211544南京先賢賂312100.022100022dep02_£)01 康輝Dep_02教授18569321南京長虹蹺5號3100.082100087. 查詢每個學(xué)生及英選修課情況sele

10、ct student_name,course_namefrom student, scf coursewhere sc studendstudent student_id and sc cours3_id二course course_id二結(jié)果空消息student_namecoursename1計(jì)算機(jī)基礎(chǔ)2張虹數(shù)據(jù)庫開發(fā)技術(shù)3林紅計(jì)算機(jī)基礎(chǔ)4林紅數(shù)據(jù)庫開發(fā)技術(shù)5曹雨計(jì)算機(jī)基礎(chǔ)6曹雨數(shù)據(jù)庫開發(fā)技術(shù)7芮華計(jì)算機(jī)基礎(chǔ)8芮華數(shù)推庫開發(fā)技術(shù)9李麗計(jì)算機(jī)棊砒10李麗數(shù)據(jù)庫開笈技術(shù)11林海國計(jì)篡機(jī)基礎(chǔ)12林縛國數(shù)據(jù)庫開發(fā)技術(shù)13李紅計(jì)算機(jī)基礎(chǔ)8. 以siudcm表為主體列出每個學(xué)生的基本情況及其選課情況，如

11、果學(xué)生沒有選 課，只輸出其基本情況select student *,course *from student "oin scon student student_id=sc student_idlef-c join courseon course course_id=sc cours3_id卻 J ifi Bstudsrtjds：i>3Brt_namem a.dswdcecciren!dadttescoirsejdcsrsejenebookjd!o!al_pworwek_ps<iorcrvd：1: g9M02fl1:紐119g99402Z少1西巨帀砂宓古年囪歲D<p

12、(U_b00l計(jì)和哇弋Dw4_bO&1_C262442阿 0201張虹119DSS402Oep.Ol府克市最腕古干竊2號DeCOOOlDs 心 OCLOI80553§9340202019gS9402MR筠州幣五季衡弓D<pC4_M01計(jì) JWQiD«»4.問丄2住444列必2n&L019g59402go?彷州市五亭彷"號DepfX.cOOl宓莊毎蘇力捉術(shù)DmUOOl80555§9340203臥120g99402we麗N眞區(qū)甘1H戈號D«pC4_M01計(jì)胭更1DZ 申 1.C262446&3S4Q203Wf

13、fi1202SS402C.OG甬気玄耳E就呂主iSV08 心 0012?努庫開倉拄未Dw4.3001.0l8055793M02W113郵02C*9_01tL蘇僅征市P年藥6號Dep(X_W01計(jì)渤菱礎(chǔ)Dw4JZ>31_C2684429&M02C4丙華11999S402Z-01圧環(huán)儀征幣禱年珂6號Dop04_d001點(diǎn)痔庫幵總技術(shù)Cor»4ji001_0l20669©5540205018B55402Oep.M江蘇儀征市音年古爲(wèi)】號Dtp(X_W01計(jì)咖婁礎(chǔ)T5?LC2684413O9MC-205季麗018g99402X少遼茅儀征市去年芬烷1號DepC4_40D1

14、戲煙庫開扶技術(shù)D«>4_4001_01eo5511沖 oeoe林 X3SJ1ISgS54023匕宜市中關(guān)材太干北卷DeMJXDIT5"J：2684412g95d£-2O119gS9402Dep少1匕左幣中關(guān)初文平北廉DepC4_aOD1念瘙莊開左技術(shù)D«»4_4001_01eo5513罟 0207斑021285402DepOJZOl計(jì)直祖丑筍Cl如坤1.C2449査詢選修dep04_s001號課程且成績在80分以上的學(xué)生信息。（分別用連接, in和exists實(shí)現(xiàn)）連接：select student*from scz studentwhe

15、re sc student_:Ld=student studentidand sc courseid13 ' dep04_s001 1and sc grade>80商結(jié)果1曲消息studentdstudent_namesexa.classjddepartmentjdaddress1g9940201張虹119gS9402Dep_01南京市鼓樓區(qū)古平崗3號2g9W0206林海國119g99402Dep_04北京市中關(guān)村太平北路In：select *from studentwhere student_idin (select student_idfrom scwhere course_

16、id='dep04_s001and grade>80)1studentdstudent .namesexa.classddepartmentjdaddressgSM0201$張虹113gS94Q2Dep_01南京市前樓區(qū)古平崗3號2g9&40206林海國119g99402Dep_Q4北京市中關(guān)村太平北路exists：select from studentwhere exists(select student_idfrom scwhere student student_id=sc student_id and course_id=1depO4_sOCl' and

17、grade>80)student id student name sex a. class id department id addressMBMBIIM1g9S40201張虹119g39402D 即 _01南京市哉樓區(qū)古平崗3號2g9940206林海國119g93402Dep_04北京市中關(guān)村太平北路10.查詢所有上計(jì)算機(jī)基礎(chǔ)課程的學(xué)生的學(xué)號、選修課程號以及分?jǐn)?shù)（分別用連接. in和exists實(shí)現(xiàn)）連接：select sc from sc,coursewhere course cour二 1 計(jì)算機(jī)叢礎(chǔ)and course course_id=sc coursm_id結(jié)果匕消息stu

18、dentdcoursejdgrade1g9940201dep 04_b001882g&940202depO4_bW)1753g&940203depD4_bOO1884g&940204depO4_b(X)1565g&940205dep M_bOD1456g»94020Sdep04_b001987g&540207dep04_b00187In：select student_id,course_idz grade from scwhere course_idin (select course_idfrom coursewhere course_name

19、= 1 計(jì)算機(jī)叢礎(chǔ) 1 )結(jié)果區(qū)消息studentdcoureejdgrade1g994Q201deP04_b001882 _g9940202depD4_b001782_g9940203dep04_b001884g934G2O4depD4_b00156g994Q205depO4_bOO1456；g934Q2O6depQ4_b001587g9340207dep04_b00187exists：select student_id,courseid,grade from scwhere exists(select from course where course_id=sc and course_na

20、me= 1 計(jì)算機(jī)人E礎(chǔ) 1 )a結(jié)果|由消息studenrtjd coursejdgrade1g9940201 j dep04_b001882g99402O2dep04_b001783gSS40203 dep04_b001884g9940204dep04_b001565 _g99402D5 depO4_bOO145£_g994020S dep04_b001987_g99402D7 dep04_bD018711查詢選修了課程劣為“數(shù)據(jù)庫基礎(chǔ)”的學(xué)生學(xué)號和姓需（分別用連接，in和 exists 實(shí)現(xiàn)）連接:select student student_id,student studen

21、t_name from scr student,coursewhere course course_name= * 數(shù)據(jù)冷發(fā)技術(shù) 1 and sc studen student_id and course . course_id=sc course_idwhere student_idin (select student_idfrom scwhere course_id=(select course_idfrom coursewhere cours3_nam3二，數(shù)Wi冷丿I發(fā)技術(shù)，)g»940201張虹2g9640202林紅3g5940203

22、曹雨4g&940204芮華5g&940205李麗6g&940206林海國exists：select student_id,student_namefrom studentwhere exists(select *from scwhere sc student_id=student student_id and sc coursm_id=(select course_idfrom course where course_name= r 數(shù)據(jù)冷丿I發(fā)技術(shù))曲結(jié)果匕消£student id student name1g9940201張虹2g9940202林紅3g9S

23、40203曹雨4g9940204芮華5g9S40205李麗Gg9940206林海國12.查詢所有計(jì)算機(jī)系學(xué)生的學(xué)號、選修課程號以及分?jǐn)?shù)(分別用連接，in和exists 實(shí)現(xiàn))連接：select sc *from scr department,studentwhere department dmpartmentname二'計(jì)算機(jī) 科學(xué) 1and department departmentid31 student department_id and sc studen student_idstudentjd coursejdgrade1©9940205 j depW_b00145

24、2g9940205 dep&4_s(>01523©994020& depO*4_bO01984g994020G depCMsDOI925g3340207 depM_b&0187In：selecfrom scwhere student_idin (select student_idfrom studentwhere departmentald=(select department_idfrom departmentwhere department_name = 1 計(jì)算機(jī)科學(xué) 9 )結(jié)果書消息studentdcourse jdgrade1g3940205d

25、ep04_b001452g9340205ckp04.s001523g9940206dep04_b001984g9S40206dep04.s001925g9940207dep04_b00187exists：select*from scwhere exists (select * from studentwhere student student_id =sc student_idand student department_id=(select department_id from department xvhere department_name = ' il' 算機(jī)科J)結(jié)果

26、s消息studentdcoursejdgrade1g9«40205depO4_bOO1452g9940205dep04_§001523g9940206dep04_b001584g»940206dep04_s001925g&940207depO4_bOO18713.査詢每個dep_04系學(xué)生的總成績、平均成績，僅顯示平均成績及格的學(xué)生 的記錄。select student s七, sum(grade) as ，總、月戈績,avg(gradm) as 平均成績from sc, student,departmentwhere sc studentidstuden

27、t student_idand student department_id=department Department_idand department department_id='dep_04'group by student student_£dz stuhaving avg(grade；60為結(jié)果1由消息student .name總成平均成績1林海國190952李紅87&714査詢“數(shù)據(jù)庫開發(fā)技術(shù)''的平均成績 select avg (grade) as數(shù)據(jù)庫開發(fā)平均成績from scwhere course_id in (select course_idfrom coursewhere course_name= 1數(shù)據(jù)庫開發(fā)技術(shù)r )ca結(jié)果的消菖煞聲牙野攙鏡1 no! 15.按職稱査詢教師的平均工資，并按總工資降序排列select profession, avg (salary) as, sum (salary) as 總工資from teachergroup by professionorder by sum(salary) descJ結(jié)果消息profession平均工資總工資13266.8433339800.532副教授2166.9133336500.743