1、實驗4數(shù)據(jù)查詢一、實驗?zāi)康?, 掌握使用Transact-SQL的SELEC砒句進行基本查詢的方法。2, 掌握使用SELEC砒句進行條件查詢的方法。3, 掌握嵌套查詢的方法。4, 掌握多表查詢的方法。5, 掌握SELEC砒句的GROUPBY口ORDERB仔句的作業(yè)和使用方法。6, 掌握聯(lián)合查詢的操作方法。7, 掌握數(shù)據(jù)更新語句INSERTINTOUPDATEDELETE勺使用方法。二、實驗準(zhǔn)備1, 了解SELEC砒句的基本語法格式和執(zhí)行方法。2, 了解嵌套查詢的表示方法。3, 了解UNION運算符的用法。4, 了解SELEC砒句的GROUPBY口ORDERBYF句的作用。5, 了解IN、JOI

2、N等子查詢的格式。6, 了解INSERTINTO、UPDATEDELETE的格式與作用。三、實驗內(nèi)容及步驟0,創(chuàng)建studentsdb數(shù)據(jù)庫及其相應(yīng)表，并錄入數(shù)據(jù)。啟動查詢分析器，運行下面鏈接的代碼即可。創(chuàng)建數(shù)據(jù)庫代碼1. 在studentsdb數(shù)據(jù)庫中，使用下列SQL語句將輸出什么？(1) SELECTCOUNT(*)FROMgrade:無列國1 '15I1 2(2) SELECTSUBSTRIN玳名,1,2)FROMstudent_info嫵列旬hiuirai”an，maui與1 j劉衛(wèi)2 森直3 馬東4 段這5 東方6 郭文7 肖海8 張明(3) SELECTUPPER('

3、;kelly')慶列匐1:KELLYihuiHIIHlP-lUli時III牖(4) SELECTReplicate('kelly',3)優(yōu)列匐1:kellyfceilykellyIU.IIIlllIIIBJIIIUMia：.Billl-UIIIIA.(5) SELECTSQRTO數(shù))FROMgradeWHERE分數(shù)>=85,一列名)Ifa.t-a>!Ill-.ilBt-SII：-A-SIS_Id,18網(wǎng)23&網(wǎng)514232727SO&3DSStf3i27361&4S549574勺儂9sli篁陶t(6) SELECT2,3,POWER(

4、2,3)疣列的：無列匐比列名je'S-?!"-!JSIS""esiss1'2：38SELECTYEAR(GETDATE(),MONTH(GETDATE(),DAY(GETDATE()團瞿、所列匐撫列省112013一！32G2.在studentsdb數(shù)據(jù)庫中使用SELEC9句進行基本查詢。(1)在student_info表中，查詢每個學(xué)生的學(xué)號、姓名、出生日期信息。SELECT*FROMstudent_info字號姓名性別出生日期家庭住址備注IIUIIIII1UIIIIMlll|L9?9Li劉衛(wèi)平男1涮DOiOaOO.DOO衡山市東風(fēng)路78號NULL

5、2張衛(wèi)民里1眄1202OfbO&ODOOO東陽由兒一北胳25名NULL30003馬東黑1死04>7500:00:00,000長崎市五TS咫洱NULL40004餞達度里0D.)C:K),(X»濱海市洞庭大道2門拈NULL5東方牧曼東方南中山路2婚NULL60006郭文斌里mcm-WM)do：dow長島市解旅路2猖NULL70M7肯海燕女19MH&1500:0&00(»0山南南紅旗路再怎MULLBQD03張明華女溟江市韶山路3涓NULL(2)查詢學(xué)號為0002的學(xué)生的姓名和家庭住址。SELECT姓名，家庭住址FROMstudent_infoWHER

6、E學(xué)號=0002蛙卷索底住加11.1111IHBHH11騙.民J東陽市A-北路H邑(3) 找出所有男同學(xué)的學(xué)號和姓名。SELECT學(xué)號，姓名FROMstudent_infoWHERE性別='男學(xué)號姓名1maiiie|0001j劉衛(wèi)平2，slim0002張衛(wèi)民30003馬東dDO(M錢達理5D0D5東方牧GOOOG郭文斌3,使用SELEC9句進行條件查詢1 1)在grade表中查找分數(shù)在8090分為內(nèi)的學(xué)生的學(xué)號和分數(shù)。SELECT學(xué)號,分數(shù)FROMgradeWHERE分數(shù)BETWEEN80AND90字號分班1*«1iraviJ0M1j8029030001&71D0D1

7、胭50002的£(XM3羽7_(MH3842 2)在grade表中查詢課程編號為0003的學(xué)生的平均分。SELECTAVG(分數(shù))AS平均分FROMgradeWHERE課程編號=0003平均分11而通通：3 3)在grade表中查詢學(xué)習(xí)各門課程的人數(shù)。SELECT課程編號，COUNT(*)AS人數(shù)FROMgradeGROUPBY課程編號裸程編號人數(shù)4 i!3ninra5 000236 DW337 00W38 W053(4) 將學(xué)生按出生日期由大到小排序。SELECT學(xué)號，姓名，出生日期FROMstudent_infoORDERBY出生日期ASC學(xué)工姓名出生日期1piiA&ai

8、.1g(W7i由海泰193043315ODJO&O.20DM張明華1300:30:00.00030004錢達理193045-10WODW.WO4OOM郭翔19SM&B400,00:00.5。兇3馬東1980417400:00:00,00060005東方犢19SM7-1300.30M.000一0001劉衛(wèi)平W1W1吶M皿QOD80002張衛(wèi)民19SM2-200.30:00.000(5) 查詢所有姓“張”的學(xué)生的學(xué)號和姓名。SELECT學(xué)號，姓名FROMstudent_infoWHERE姓名LIKE'張'(6) 嵌套查詢(1) 在student_info表中查找與“

9、劉衛(wèi)平”性別相同的所有學(xué)生的姓名、出生日期。SELECT姓名，出生日期FROMstudent_infoWHERE生另1J=(SELECT性另1JFROMstudent_infoWHERE生名='劉衛(wèi)平')二姓名出生日翱ZMlII1*111 I劃衛(wèi)平!19801WJ:00：X).0002 OR19SM2-02MrMMDOn3 馬東198007-0400:00:00.4 錢達理192M星加囿網(wǎng):岫網(wǎng)5 東方牧1期(HHT3MHXkK.ME郭文斌19S0-(K4400:00：M.HJC(2) 使用IN子查詢查找所修課程編號為0002、0005的學(xué)生學(xué)號、姓名、性別。SELECT學(xué)號，

10、姓名，性別FROMstudent_infoWHEREstudent_info.學(xué)號IN(SELECT學(xué)號FROMgradeWHEREM程編號IN('0002','0005')學(xué)希姓名性別I1B-S!B!r-SSIS5-esir的1刖衛(wèi)平男an(jbiiiiiainr2 0002張衛(wèi)民男3 0m馬東男(3) 列出學(xué)號為0001的學(xué)生的分數(shù)比0002號的學(xué)生的最低分數(shù)高的課程編號和分數(shù)。SELECT課程編號,分數(shù)FROMgradeWHERE學(xué)號='0001'AND分數(shù)>ANY(SELECT分數(shù)FROMgradeWHERE學(xué)號='0002

11、')課程編等分數(shù)1ttibiir-s111r?iairmii"i,'oooi!ImdiiaBiiiaaiiuni*1ao2冊3(K»3374於578(4) 列出學(xué)號為0001的學(xué)生的分數(shù)比0002的學(xué)生的最高成績還要高的課程編號和分數(shù)。SELECT課程編號，分數(shù)FROMgradeWHERE學(xué)號='0001'AND分數(shù)>ALL(SELECT分數(shù)FROMgradeWHERE學(xué)號='0002')1j0002I如nirHSiiarnniirMiinraaiiamE5. 多表查詢(1) 查詢分數(shù)在8090范圍內(nèi)的學(xué)生的學(xué)號、姓名、

12、分數(shù)。SELECTstudent_info.學(xué)號,姓名,分數(shù)FROMstudent_info,gradeWHEREstudent_info.學(xué)號=grade.學(xué)號AND分數(shù)BETWEEN80AND90學(xué)邑姓名分數(shù)1ai|iuil|!eu劉衛(wèi)平加2'boo'f劉衛(wèi)平扎30001劉衛(wèi)平B7A«K)1劉衛(wèi)平B-6E0002張衛(wèi)民的£D0D3馬東837DM3馬東(2) 查詢學(xué)習(xí)“C語言程序設(shè)計”課程的學(xué)生的學(xué)號、姓名、分數(shù)。SELECTstudent_info.學(xué)號,姓名,分數(shù)FROMstudent_infoINNERJOINgradeONstudent_info.

13、學(xué)號=grade.學(xué)號INNERJOINcurriculumON課程名稱='C語言程序設(shè)計姓名分額1Mmun»ari螞j劉衛(wèi)平即2劉衛(wèi)平3030001劉衛(wèi)平3740001劉衛(wèi)平B650001劉衛(wèi)平7B£0002張衛(wèi)民7E70002張衛(wèi)民73S0002張衛(wèi)民67g0002張衛(wèi)民70100002張衛(wèi)民89ii0001馬東S312馬東73130005馬東S4140003馬東75150003馬東65(3) 查詢所有男同學(xué)的選課情況，要求列出學(xué)號、姓名、課程名稱、分數(shù)。selectstudent_info,學(xué)號，姓名，課程編號，分數(shù)fromstudent_infoleftou

14、terjoingradeonstudent_info.學(xué)號=grade.學(xué)號where性別="男學(xué)號課程編號分數(shù)1iraHBBii01j劉衛(wèi)平00013020001文3平00029030001劉衛(wèi)平000JS7n0001劉衛(wèi)平BE50D01劉衛(wèi)平ODOS7S60002張衛(wèi)民甌7670002張衛(wèi)民0OT2730KJ2張衛(wèi)民甌6790D02張衛(wèi)民W047010蜘張衛(wèi)民彩11OHB馬東9001fi3120003馬東027313DOTS馬東0003B4140003馬榮0475150003馬東0005651GOOM錢達理NUL1NULL17TO5東方牧NULLNULL1£0D(K郭文

15、武MULLNULL(4) 查詢每個學(xué)生的所選課程的最高成績，要求列出學(xué)號、姓名、課程名稱、分數(shù)。selectgrade.學(xué)號,student_info.姓名,grade.課程編號,grade.分數(shù)fromstudent_infoleftouterjoingradeonstudent_info.學(xué)號=grade.學(xué)號wheregrade.分數(shù)>=(selectMAX(分數(shù))fromgradebwhereb.學(xué)號=grade.學(xué)號)學(xué)號'姓名課程編號分數(shù)1OTG1：劉衛(wèi)平9020002”張衛(wèi)民OCHfe3M03馬東ooasB4(5) 查詢所有學(xué)生的總成績，要求列出學(xué)號、姓名、總成績，

16、沒有選修課程的學(xué)生的總成績?yōu)榭铡Ｌ崾荆菏褂米笸膺B接。課程的選修情況，要求列出課程編號、課程名稱、選修人數(shù)，curriculum表中沒有的課程列值為空。提示：使用右外連接。insertgradevalues('0004','0006','76')selectgrade.課程編號,課程名稱,count(*)fromgraderightouterjoincurriculumongrade.課程編號=curriculum.課程編號groupbygrade.課程編號，課程名稱課程編招課程名稱伍列構(gòu)1|iMannlaaiiniiiMiiiMai|00020音

17、言程序設(shè)訐32SQLSEWER里據(jù)座號應(yīng)用330001大學(xué)計算機基砒340OT5高等勤學(xué)350004英詔26,使用UNION運算符將student_info表中姓“張”的學(xué)生的學(xué)號、姓名與curriculum表的課程編號、課程名稱返回在一個表中，且列名為u_編號、u_名稱，如圖1-8所示。select學(xué)號u編號，姓名u_名稱fromstudent_infowhere姓名like'張'unionselect課程編號，課程名稱fromcurriculum口一編號名稱10001大學(xué)計算機基礎(chǔ)20002戊吾言程序設(shè)計30002張衛(wèi)民400039QL31Vx數(shù)據(jù)庫及應(yīng)用50004英語60

18、005高等數(shù)學(xué)70008秣明華圖1-8聯(lián)合查詢結(jié)果集7.數(shù)據(jù)更新(1) 創(chuàng)建totalgrade表，具有數(shù)據(jù)列：學(xué)號、姓名、總成績。CREATETABLEtotalgrade(學(xué)號char(4)NOTNULL,姓名varchar(8)NULL,總成績decimal(5,2)NULL)(2) 使用INSERTINTO語句通過student_info表更新totalgrade表的學(xué)號、姓名列數(shù)據(jù)。INSERTINTOtotalgradeSELECT學(xué)號，姓名，總成績=0FROMstudent_info(3) 使用UPDATE語句通過grade表更新totalgrade表的中成績列數(shù)據(jù)，使totalgrade表中每個學(xué)生的總分成績?yōu)間rade表中該學(xué)生各成績之和。UP