數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用復(fù)習(xí)資料

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用復(fù)習(xí)資料數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用復(fù)習(xí)資料數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用復(fù)習(xí)資料x(chóng)xx公司數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用復(fù)習(xí)資料文件編號(hào)：文件日期：修訂次數(shù)：第1.0次更改批準(zhǔn)審核制定方案設(shè)計(jì)，管理制度使用SQL語(yǔ)句創(chuàng)建數(shù)據(jù)表&數(shù)據(jù)操作Insert、Update、Delete利用Transact-SQL語(yǔ)句創(chuàng)建表booksales的代碼。USEtest01GOCREATETABLEbooksales( book_idnchar(6)NOTNULL, sellnumintNOTNULL, selldatedatetimeNOTNULL)ONPRIMARY利用insert語(yǔ)句為表booksales添加數(shù)據(jù)：INSERTINTObooksalesVALUES('m00011',7,20/12/2008)利用update語(yǔ)句為表booksales更新數(shù)據(jù)：UPDATEbooksalesSETsellnum=11WHEREbook_id='m00011'利用delete語(yǔ)句刪除表booksales的數(shù)據(jù)：DELETEFROMbooksalesWHEREbook_id='m00011'Transact-SQL語(yǔ)句基礎(chǔ)1–數(shù)據(jù)完整性將teaching數(shù)據(jù)庫(kù)中score表的studentno列設(shè)置為引用表student的外鍵。ALTERTABLEScoreADDCONSTRAINTFK_score_studentFOREIGNKEY(studentno)REFERENCESstudent(studentno)將teaching數(shù)據(jù)庫(kù)中class表的classname創(chuàng)建UNIQUE約束。ALTERTABLEclassADDCONSTRAINTUQ_classUNIQUE(classname) 執(zhí)行如下插入語(yǔ)句，查看提示信息 INSERTINTOclassVALUES(‘090602’,’計(jì)算機(jī)0902’,’計(jì)算機(jī)學(xué)院’,’馬文斐’)3.為teaching數(shù)據(jù)庫(kù)中student表的birthday列創(chuàng)建CHECK約束，規(guī)定學(xué)生的年齡在17-25歲之間。 ALTERTABLEstudent ADDCONSTRAINTCK_birthdayCHECK(YEAR(GETDATE())-YEAR(birthday))BETWEEN17AND25 執(zhí)行如下插入語(yǔ)句，查看提示信息 INSERTINTOstudent(studentno,sname,sex,birthday,classno)VALUES(‘08’,’張?jiān)础?’男’,’1983-04-05’,’090501’) 提示：表達(dá)式Y(jié)EAR(GETDATE())-YEAR(birthday)數(shù)據(jù)檢索11）SELECT…FROM…2）條件WHERE3）生成新表INTO4）比較運(yùn)算符（數(shù)值類(lèi)型、字符串、日期時(shí)間，YEAR()）5）模糊查詢(xún)LIKE6）邏輯運(yùn)算符ANDORNOT7）檢索一定范圍的值BETWEEN…AND…8）利用列表值檢索IN9）改變列名AS10）排序ORDERBY11）消除重復(fù)行DISTINCT12）輸出前n行TOPnTOPnPERCENT13）分組GROUPBY篩選HAVING14）聚合函數(shù)15）附加行匯總值COMPUTE16）多表連接INNERJOIN17）使用子查詢(xún)1.查詢(xún)course表中所有的記錄。SELECT*FROMcourse2.查詢(xún)student表中女生的人數(shù)。SELECT*FROMstudentWHEREsex='女'查詢(xún)teacher表中每一位教授的教師號(hào)、姓名和專(zhuān)業(yè)名稱(chēng)。SELECTteacherno,tname,majorFROMteacherWHEREprof='教授'利用現(xiàn)有的表生成新表，新表中包括學(xué)號(hào)、課程號(hào)和總評(píng)成績(jī)。其中：總評(píng)成績(jī)=final*+usually*SELECT,sname,courseno,final*+usually*AS總評(píng)成績(jī)INTOzongpingFROMstudent,scoreWHERE=GOSELECT*FROMzongping查詢(xún)student表中所有年齡大于20歲的男生的姓名和年齡。SELECTsname,Year(GetDate())-Year(birthday)ASageFROMstudentWHEREYear(GetDate())-Year(birthday)>20GO查詢(xún)計(jì)算機(jī)學(xué)院教師的專(zhuān)業(yè)名稱(chēng)。SELECTmajorAS專(zhuān)業(yè)名稱(chēng)FROMteacherWHEREdepartment='計(jì)算機(jī)學(xué)院'GO查詢(xún)Email使用126郵箱的所有學(xué)生的學(xué)號(hào)、姓名和電子郵箱地址。SELECTstudentno,sname,EmailFROMstudentWHEREEmailLike'%%'GO查詢(xún)score表中選修’c05109’或’c05103’課程，并且課程期末成績(jī)?cè)?0~100分之間的學(xué)號(hào)和期末成績(jī)。SELECT*FROMscoreWHERE((coursenoin('c05109','c05103'))and(finalbetween90and100))查詢(xún)student表中所有學(xué)生的基本信息，查詢(xún)結(jié)果按班級(jí)號(hào)classno升序排列，同一班級(jí)中的學(xué)生按入學(xué)成績(jī)point降序排列。SELECT*FROMstudentORDERBYclassnoASC,pointDESC查詢(xún)選修’c05109’課程，并且期末成績(jī)?cè)谇?名的學(xué)生學(xué)號(hào)、課程號(hào)和期末成績(jī)。（提示：TOP5）SELECTTOP5studentno,courseno,finalFROMscoreWHEREcourseno='c05109'ORDERBYfinalDESC數(shù)據(jù)檢索2–Groupby、Having、Computeby1.按性別分組，求出student表中每組學(xué)生的平均年齡。SELECTsex,AVG(YEAR(GETDATE())-YEAR(birthday))ASageFROMstudentGROUPBYsex2.查詢(xún)各班學(xué)生的人數(shù)。SELECTclassno,COUNT(*)AS人數(shù)FROMstudentGROUPBYclassnoORDERBYclassno3.查詢(xún)各課程期末成績(jī)的最高分和最低分。SELECTcourseno,MAX(final)AS最高成績(jī),MIN(final)AS最低成績(jī)FROMscoreGROUPBYcourseno4.查詢(xún)教兩門(mén)及以上課程的教師編號(hào)、任課班級(jí)數(shù)。SELECTteacherno,COUNT(courseno)AS班級(jí)數(shù)FROMteach_classGROUPBYteachernoHAVINGCOUNT(courseno)>=25.查詢(xún)課程編號(hào)以’c05’開(kāi)頭、被3名及以上學(xué)生選修且期末成績(jī)的平均分高于75分的課程號(hào)、選修人數(shù)和期末成績(jī)平均分，并按平均分降序排序。SELECTcourseno,COUNT(studentno)AS選修人數(shù),AVG(final)AS平均分FROMscoreWHEREcoursenoLIKE'c05%'ANDfinalISNOTNULLGROUPBYcoursenoHAVINGCOUNT(studentno)>=3ANDAVG(final)>=75ORDERBYAVG(final)DESCSQL語(yǔ)句的高級(jí)應(yīng)用1–內(nèi)連接、外連接1.查詢(xún)所有班級(jí)的期末成績(jī)平均分，并按照平均分降序排列。表：score,student2joinSELECTclassno,AVG(final)AS班級(jí)平均分FROMstudentINNERJOINscoreON=WHEREfinalISNOTNULLGROUPBYclassnoORDERBYAVG(final)DESC2.查詢(xún)兩門(mén)及以上課程的期末成績(jī)超過(guò)80分的學(xué)生的姓名及其平均成績(jī)。表：student,scoreSELECT,sname,AVG(final)AS平均分FROMstudentJOINscoreON=WHEREfinalISNOTNULLandfinal>=80GROUPBY,snameHAVINGCOUNT(*)>=2SQL語(yǔ)句的高級(jí)應(yīng)用2–使用子查詢(xún)3.查詢(xún)?nèi)雽W(xué)考試成績(jī)最高的學(xué)生的學(xué)號(hào)、姓名和入學(xué)成績(jī)。=SELECTstudentno,sname,pointFROMstudentWHEREpoint=(SELECTMAX(point)FROMstudent)4.查詢(xún)所有教授’c05127’號(hào)課程的教師信息。InSELECT*FROMteacherWHEREteachernoIN(SELECTteachernoFROMteach_classWHEREcourseno='c05127')視圖與索引使用SQL語(yǔ)言1.創(chuàng)建一個(gè)視圖v_teacher，查詢(xún)所有“計(jì)算機(jī)學(xué)院”的教師信息,添加WITHCHECKOPTION選項(xiàng)。CREATEVIEWv_teacherASSELECT*FROMteacherWHEREdepartment='計(jì)算機(jī)學(xué)院'WITHCHECKOPTIONGOSELECT*FROMv_teacher2.創(chuàng)建一個(gè)視圖v_avgstu，查詢(xún)每個(gè)學(xué)生的學(xué)號(hào)、姓名及平均分，并且按照平均分降序排列。CREATEVIEWv_avgstuASSELECTTOP100,sname,avg(final)AS'平均分'FROMstudentJOINscoreON=GROUPBY,snameORDERBYavg(final)GOSELECT*FROMv_avgstu3.通過(guò)視圖v_teacher向基表teacher中分別插入數(shù)據(jù)(‘05039’,‘張馨月’,’計(jì)算機(jī)應(yīng)用’,’講師’,’計(jì)算機(jī)學(xué)院’)和(‘06018’,‘李誠(chéng)’,’機(jī)械制造’,’副教授’,’機(jī)械學(xué)院’)，并查看插入數(shù)據(jù)情況。INSERTINTOv_teacherVALUES('05039','張馨月','計(jì)算機(jī)應(yīng)用','講師','計(jì)算機(jī)學(xué)院')GOSELECT