SQL操作復(fù)習(xí)題及復(fù)習(xí)資料

1數(shù)據(jù)庫操作1．創(chuàng)建數(shù)據(jù)庫：操作1.1：創(chuàng)建一個test數(shù)據(jù)庫，其主數(shù)據(jù)文件邏輯名test_data，物理文件名，初始大小10MB，最大尺寸為無限大，增長速度1MB；數(shù)據(jù)庫日志文件邏輯名稱為test_log，物理文件名為，初始大小為1MB，最大尺寸為5MB，增長速度為10%。參考答案：createdatabaseTestONprimary( name=test_data, ='d:\test\test_data.mdf', size=5MB, maxsize=unlimited, =1MB)LOGON( name=test_log, ='d:\test\test_log.ldf', size=1MB, maxsize=5MB, =10%)GO2．查看數(shù)據(jù)庫屬性：操作1.2：使用T-SQL語句查看數(shù)據(jù)庫test屬性參考答案：EXECsp_helpdbtest3．刪除數(shù)據(jù)庫：操作1.3：使用T-SQL語句刪除數(shù)據(jù)庫test參考答案：dropdatabaseTest2表操作1．創(chuàng)建表：操作2.1：創(chuàng)建學(xué)生表：表名：student說明：學(xué)生基本信息表屬性列數(shù)據(jù)類型長度空值列約束說明st_idnVarChar9NotNullPK學(xué)生學(xué)號st_nmnVarChar8NotNull學(xué)生姓名st_sexnVarChar2Null學(xué)生性別st_birthdatetimeNull出生日期st_scoreintNull入學(xué)成績st_datedatetimeNull入學(xué)日期st_fromnChar20Null學(xué)生來源st_dpidnVarChar2Null所在系編號st_mnttinyintNull學(xué)生職務(wù)參考答案：USEtestGOCREATETABLEstudent( st_id nVarChar(9) primarykeyNOTNULL, st_nm nVarChar(8) NOTNULL, st_sex nVarChar(2) NULL, st_birth datetime NULL, st_score int NULL, st_date datetime NULL, st_from nVarChar(20) NULL, st_dpid nVarChar(2) NULL, st_mnt tinyint NULL)GO操作2.2：創(chuàng)建課程信息表：表名：couse說明：課程信息表屬性列數(shù)據(jù)類型長度空值列約束說明cs_idnVarChar4NotNullPK課程編號cs_nmnVarChar20NotNull課程名稱cs_tmintNull課程學(xué)時cs_scintNull課程學(xué)分參考答案：USEtestGOCREATETABLEcouse( cs_id nVarChar(4) primarykeyNOTNULL, cs_nm nVarChar(20) NOTNULL, cs_tm int NULL, cs_sc int NULL)GO操作2.3：創(chuàng)建選課表：表名：slt_couse說明：選課表屬性列數(shù)據(jù)類型長度空值列約束說明cs_idnVarChar4NotNullFK課程編號st_idnVarChar9NotNullFK學(xué)生編號scoreintNull課程成績sltdatedatetimeNull選課日期參考答案：USEtestGOCREATETABLEcouse( cs_id nVarChar(4) NOTNULL, st_id nVarChar(9) NOTNULL, score int NULL, sltdate datetime NULL)GO操作2.4：創(chuàng)建院系信息表：表名：dept說明：院系信息表屬性列數(shù)據(jù)類型長度空值列約束說明dp_idnVarChar2NotNull系編號dp_nmnVarChar20NotNull院系名稱dp_drtnVarChar8Null院系主任dt_telnVarChar12Null聯(lián)系電話參考答案：USEtestGOCREATETABLEdept( dp_id nVarChar(2) NOTNULL, dp_nm nVarChar(20) NOTNULL, dp_drt nVarChar(8) NULL, dp_tel nVarChar(12) NULL)GO2．修改表結(jié)構(gòu)：（1）向表中添加列：操作2.5：為“dept”表添加“dp_count”列（數(shù)據(jù)類型為nvarchar，長度為3，允許為空）參考答案：ALTERTABLEdeptADDdp_countnvarchar(3)NULL（2）修改列數(shù)據(jù)類型：操作2.6：修改“dept”表的“dp_count”列數(shù)據(jù)類型為int參考答案：ALTERTABLEdeptALTERCOLUMNdp_countintNULL（3）刪除表中指定列：操作2.7：刪除“dept”表的“dp_count”列參考答案：ALTERTABLEdeptDROPCOLUMNdp_count3．刪除表操作2.8：刪除“dept”表參考答案：DROPTABLEstudent4．向表中輸入數(shù)據(jù)記錄操作2.9：分別向“student”表、“couse”表、“slt_couse”表、“dept”表中輸入數(shù)據(jù)記錄3數(shù)據(jù)完整性1．空值約束(NULL)操作3.1：將student表中的st_sex列屬性更改為NOTNULL參考答案：ALTERTABLEstudentALTERCOLUMEst_nmnVarChar(8)NOTNULL2．默認(rèn)值約束(DEFAULT)操作3.2：將student表中的st_from列默認(rèn)值設(shè)置為“陜西省”參考答案：ALTERTABLEstudentADDDEFAULT'陜西省'FORst_from3．默認(rèn)值對象操作3.3：創(chuàng)建默認(rèn)值對象df_today為當(dāng)前日期，并將其綁定到slt_couse表中的sltdate列，然后取消綁定，最后刪除默認(rèn)值對象df_today。參考答案：CREATEDEFAULTdf_todayASGetdate()GOEXECsp_bindefaultdf_today,'slt_couse.sltdate'GOEXECsp_unbindefault'slt_couse.sltdate'GODROPDEFAULTdf_todayGO4．檢查約束(CHECK)操作3.4：將slt_couse表中的score列的檢查約束設(shè)置為>=0且<=100參考答案：ALTERTABLEslt_couseADDCHECK(score>=0ANDscore<=100)5．規(guī)則約束對象操作3.5：創(chuàng)建規(guī)則約束對象rl_sex，用于檢查性別的取值僅限于“男”與“女”，并將其綁定到student表中的st_sex列，然后取消綁定，最后刪除規(guī)則約束對象rl_sex。參考答案：CREATERULErl_sexAS@chksex’男’OR@chksex=’女’或CREATERULErl_sexAS@chksexIN(’男’,’女’)GOEXECsp_bindrulerl_sexex'GOex'GODROPRULErl_sexGO6．主鍵操作3.6：將dept表中的dp_id列設(shè)置為主鍵參考答案：ALTERTABLEdeptADDPRIMARYKEY(dp_id)7．唯一性約束(UNIQUE)操作3.7：將dept表中的dp_nm列設(shè)置為唯一性約束參考答案：ALTERTABLEdeptADDUNIQUE(dp_nm)8．標(biāo)識列操作3.8：向slt_couse表中添加標(biāo)識列id，第1行默認(rèn)值為1，相鄰兩個標(biāo)識列間的增量為1參考答案：ALTERTABLEslt_couseADDidINTIDENTITY(1,1)NOTNULL9．外鍵（FOREIGNKEY）操作3.9：被參照表為dept，參照表為student參考答案：ALTERTABLEstudentADDFOREIGNKEY(st_dpid)REFERENCESdept(dp_id)4數(shù)據(jù)更新1．表中插入數(shù)據(jù)操作4.1：向dept表插入一條記錄，系號11，系名自動控制系，系主任為李其余，電話81234567INSERTINTOdeptVALUES('11','自動控制系','李其余','')操作4.2：向student表插入一條記錄，學(xué)號070201001，姓名為王小五，性別為男，出生日期為1990年9月9日，系號為11，其余字段為NULL或默認(rèn)值INSERTINTOstudent(st_id,st_nm,st_sex,st_birth,st_dpid)VALUES('070201001','王小五','男','1990.9.9','11')操作4.3：向couse表插入一條記錄，課程號1234，課程名為操作系統(tǒng)，其余字段為NULL或默認(rèn)值INSERTINTOcouse(cs_id,cs_nm)VALUES('1234','操作系統(tǒng)')操作4.4：向slt_couse表插入一條記錄，課程號1234，學(xué)名070201001，其余字段為NULL或默認(rèn)值INSERTINTOslt_couse(cs_id,st_id)VALUES('1234','070201001')2．修改表中數(shù)據(jù)操作4.5：修改student表記錄，將王小五的入學(xué)成績改為88UPDATEstudentSETst_score=88WHEREst_nm='王小五'操作4.6：修改couse表記錄，將所有記錄的學(xué)分改為4，學(xué)時改為64UPDATEcouseSETcs_tm=64,cs_sc=4操作4.7：修改slt_couse表記錄，將課程號為1234，學(xué)名為070201001的記錄的成績改為77UPDATEslt_couseSETscore=77WHEREcs_id='1234'ANDst_id='070201001'3．刪除表中數(shù)據(jù)操作4.8：刪除slt_couse表記錄，將課程號為1234，學(xué)名為070201001的記錄刪除DELETEFROMslt_couseWHEREcs_id='1234'ANDst_id='070201001'操作4.9：刪除couse表記錄，將課程號為1234的記錄刪除DELETEFROMcouseWHEREcs_id='1234'5數(shù)據(jù)查詢（1）——簡單查詢（1）查詢表中所有的列：查詢所有系的信息SELECT*FROMdept（2）查詢表中指定列的信息：查詢所有的課程號及課程名稱SELECTcs_id,cs_nmFROMcouse（3）在查詢列表中使用列表達(dá)式：在查詢student表時使用列表達(dá)式：入學(xué)成績+400SELECTst_id,st_nm,st_score,st_score+400ASnew_scoreFROMstudent（4）重新命名查詢結(jié)果：使用AS關(guān)鍵字為dept表中屬性指定列名：系號、系名、系主任、聯(lián)系電話SELECTdp_idAS系號,dp_nmAS系名,dp_drtAS系主任,dp_telAS聯(lián)系電話FROMdept：使用"="號為couse表中屬性指定列名：課程號、課程名、學(xué)時(=cs_sc*16)、學(xué)分SELECT課程號=cs_id,課程名=cs_nm,學(xué)分=cs_sc,學(xué)時=cs_sc*16FROMcouse（5）增加說明列：查詢dept表的系號、系名與系主任，向查詢結(jié)果中插入說明列：系號、系名與系主任SELECT'系號:',st_id,'系名:',st_nm,'系主任:',st_drtFROMdept（6）查詢列表中使用系統(tǒng)函數(shù)：顯示所有學(xué)生的學(xué)號、姓名、性別與入學(xué)年份SELECTst_id,st_nm,st_sex,DATEPART(yy,st_birth)AS入學(xué)年份FROMstudent：顯示所有學(xué)生學(xué)號、姓名、性別與班級(學(xué)號前6位)SELECTst_id,st_nm,st_sex,LEFT(st_id,6)AS班級FROMstudent（7）消除查詢結(jié)果中的重復(fù)項：顯示所有學(xué)生班級SELECTDISTINCTLEFT(st_id,6)AS班級FROMstudent（8）取得查詢結(jié)果的部分行集：顯示前5條學(xué)生記錄信息SELECTTOP5*FROMstudent：顯示前25%條學(xué)生記錄信息SELECTTOP25PERCENT*FROMstudent：顯示前n條學(xué)生記錄信息，n為局部變量DECLARE@nINTSET@n=4SELECTTOP@n*FROMstudent6數(shù)據(jù)查詢（2）——條件查詢1．使用關(guān)系表達(dá)式表示查詢條件操作6.1：查詢dept表中系號為11的院系信息SELECT*FROMdeptWHEREdp_id='11'：查詢student表中11系的學(xué)生學(xué)號、姓名、性別與所在系編號SELECTst_id,st_nm,st_sex,st_dpidFROMstudentWHEREst_dpid='11'：查詢student表中2008年及以后入學(xué)的學(xué)生信息SELECT*FROMstudentWHEREDATEPART(yy,st_date)>=2008：在查詢student表080808班學(xué)生的學(xué)號、姓名、性別與入學(xué)成績SELECTst_id,st_nm,st_sex,st_scoreFROMstudentWHERELeft(st_id,6)='080808'2．使用邏輯表達(dá)式表示查詢條件：查詢student表中非11系的學(xué)生信息SELECT*FROMstudentWHERENOT(st_dpid='11')：查詢選修了1002號課程且成績在60以下的學(xué)生學(xué)號SELECTst_idFROMslt_couseWHERE(cs_id='1002')AND(score<60)：查詢2007年入學(xué)的11系所有男生信息SELECT*FROMstudentWHEREDATEPART(yy,st_date)=2007ANDst_dpid='11'ANDst_sex='男'：查詢11系與12系的學(xué)生信息SELECT*FROMstudentWHEREst_dpid='11'ORst_dpid='12'：查詢11系與12系所有2007年入學(xué)的學(xué)生信息SELECT*FROMstudentWHERE(st_dpid='11'ORst_dpid='12')ANDDATEPART(yy,st_date)=20073．使用LIKE關(guān)鍵字進(jìn)行模糊查詢操作6.10：查詢所有“計算機(jī)”開頭的課程信息SELECT*FROMcouseWHEREcs_nmLIKE'計算機(jī)%'操作6.11：查詢所有由三個字組成的“王”姓學(xué)生信息SELECT*FROMstudentWHEREst_nmLIKE'王__'操作6.12：查詢所有課程名中包含“信息”的課程信息SELECT*FROMcouseWHEREcs_nmLIKE'%信息%'操作6.13：查詢學(xué)生姓名介于王姓到張姓的信息SELECT*FROMstudentWHEREst_nmLIKE'[王-張]%'4．使用Between…And關(guān)鍵字進(jìn)行查詢操作6.14：查詢在1989.7.1到1990之間出生的學(xué)生信息SELECTst_id,st_nm,st_sex,st_birthFROMstudentWHEREst_birthBETWEEN'1981.7.1'AND'1999.6.30'操作6.15：查詢選修了1001號課程且成績在60到80之間的學(xué)生選課信息SELECT*FROMslt_couseWHEREcs_id='1001'AND(scoreBETWEEN60AND80)5．使用IN關(guān)鍵字進(jìn)行查詢操作6.16：查詢11系、12系、13系的學(xué)生信息SELECT*FROMstudentWHEREst_dpidIN('11','12','13')操作6.17：查詢所有張，王，李，趙姓的學(xué)生的學(xué)號、姓名、性別SELECTst_id,st_nm,st_sexFROMstudentWHERELeft(st_nm,1)IN('張','王','李','趙')6．使用[NOT]NULL關(guān)鍵字進(jìn)行查詢操作6.18：查詢所有生源為非空的學(xué)生信息SELECT*FROMstudentWHEREst_fromISNOTNULL操作6.19：查詢選修了1001號課程且成績?yōu)榭盏膶W(xué)生選課信息SELECT*FROMslt_couseWHEREcs_id='1001'ANDscoreISNULL7數(shù)據(jù)查詢（3）——查詢排序及查詢結(jié)果存儲操作7.1：查詢課程信息，按課程名稱降序排序SELECT*FROMcouseORDERBYcs_nmDESC操作7.2：查詢選修了1001號課程成績非空的學(xué)生學(xué)號與成績，并按成績降序排序SELECTst_id,scoreFROMslt_corseWHEREcs_id='1001'ANDscoreISNOTNULLORDERBYscoreDESC操作7.3：查詢11系學(xué)生學(xué)號、姓名與年齡，按年齡升序排序SELECTst_id,st_nm,DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)ASageFROMstudentORDERBYageASC：查詢學(xué)生信息，按姓名升序排序，再按系號降序排序SELECT*FROMstudentORDERBYst_nm,st_dpidDESC：創(chuàng)建學(xué)生表副本student01，僅保留學(xué)生學(xué)號、姓名與性別SELECTst_id,st_nm,st_sexINTOstudent01FROMstudent：查詢陜西籍學(xué)生，將結(jié)果保存在新表st_shanxiSELECT*INTOst_shanxiFROMstudentWHEREst_from='陜西省'：查詢選修了1001號課程學(xué)生的選課信息，按學(xué)號升序排序，將結(jié)果保存在新表slt1001SELECT*INTOslt1001FROMslt_corseWHEREcs_id='1001'ORDERBYst_id：用局部變量@stage保存學(xué)生張三的年齡DECLARE@stageintSELECT@stage=DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)FROMstudentWHEREst_nm='張三'：用局部變量@name與@stscore保存070101班按學(xué)號排序后最后一個學(xué)生的姓名與入學(xué)成績DECLARE@namenVarChar(8),@stscoreintSELECT@name=st_nm,@stscore=st_scoreFROMstudentWHERELEFT(st_id,6)='070101'ORDERBYst_id8數(shù)據(jù)查詢（4）——查詢統(tǒng)計及匯總：查詢課程總數(shù)SELECTCOUNT(*)FROMcouse：查詢選修1001號課程的學(xué)生人數(shù)SELECTCOUNT(st_id)FROMslt_couseWherecs_id='1001'操作8.3：查詢被選修課程的數(shù)量SELECTCOUNT(DISTINCTcs_id)FROMslt_couse：查詢選修070101班學(xué)生的平均入學(xué)成績SELECTAVG(st_score)FROMstudentWHERELEFT(st_id,6)='070101'操作8.5：查詢070101001號學(xué)生選修課程的數(shù)量、總分以及平均分SELECTCOUNT(cs_id)AS課程數(shù)量,SUM(score)AS總分,AVG(score)AS平均分FROMslt_couseWHEREst_id='070101001'：查詢選修1001號課程的學(xué)生人數(shù)、最高分、最低分與平均分SELECT COUNT(*)AS學(xué)生人數(shù), MAX(score)AS最高分, MIN(score)AS最低分, AVG(score)AS平均分FROMslt_couseWHEREcs_id='1001'：求各個課程號與相應(yīng)的選課人數(shù)SELECTcs_id,COUNT(st_id)FROMslt_couseGROUPBYcs_id操作：統(tǒng)計各班人數(shù)SELECTLEFT(st_id,6)AS班級,COUNT(st_id)AS人數(shù)FROMstudentGROUPBYLEFT(st_id,6)：依次按班級、系號對學(xué)生進(jìn)行分類統(tǒng)計人數(shù)、入學(xué)平均分SELECTst_dpidAS系號,LEFT(st_id,6)AS班級,COUNT(st_nm)AS人數(shù),AVG(st_score)AS均分FROMstudentGROUPBYLEFT(st_id,6),st_dpid：查詢選修了均分在75以上的課程號及均分SELECTcs_idAS課程編號,AVG(score)AS均分FROMslt_couseGROUPBYcs_idHAVINGAVG(score)>75：查詢選修了2門以上課程的學(xué)生學(xué)號SELECTst_idFROMslt_couseGROUPBYst_idHAVINGCOUNT(*)>2：明細(xì)匯總年齡<20的學(xué)生，并匯總學(xué)生數(shù)量、平均年齡SELECTst_nm,DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)AS年齡FROMstudentWHEREDATEPART(yy,GETDATE())-DATEPART(yy,st_birth)<20COMPUTECOUNT(st_nm),AVG(DATEPART(yy,GETDATE())-DATEPART(yy,st_birth))：按班級明細(xì)匯總成績<85分的學(xué)生，匯總學(xué)生數(shù)、均分SELECTst_nm,LEFT(st_id,6)AS班級,st_scoreFROMstudentWHEREst_score<85ORDERBY班級COMPUTECOUNT(st_nm),AVG(st_score)BY班級9數(shù)據(jù)查詢（5）——連接查詢操作9.1：用SQLServer形式連接查詢學(xué)生學(xué)號、姓名、性別及其所選課程編號SELECTa.st_id,st_nm,st_sex,cs_idFROMstudenta,slt_cousebWHEREa.st_id=idORDERBYa.st_id操作9.2：用ANSI形式連接查詢學(xué)生學(xué)號、姓名、性別及其所選課程編號SELECTa.st_id,st_nm,st_sex,cs_idFROMstudentaINNERJOINslt_cousebONa.st_id=_idORDERBYa.st_id操作9.3：用SQLServer形式連接查詢學(xué)生學(xué)號、姓名及其所選課程名稱及成績SELECTa.st_id,st_nm,cs_nm,scoreFROMstudenta,slt_couseb,cousecWHEREa.st_id=idORDERBYa.st_id操作9.4：用ANSI形式連接查詢學(xué)生學(xué)號、姓名及其所選課程名稱及成績SELECTa.st_id,st_nm,cs_nm,scoreFROMslt_couseaINNERJOINstudentbONa.st_id=_idINNERJOINcousecON_id=_idORDERBYb.st_id操作9.5：查詢選修了1002課程的學(xué)生學(xué)號、姓名及1001課程成績SELECTa.st_id,st_nm,scoreFROMstudenta，slt_cousebWHEREa.st_id=idANDb.cs_id='1002'ORDERBYb.st_id操作9.6：查詢選修了“數(shù)據(jù)結(jié)構(gòu)”課程的學(xué)生學(xué)號、姓名及課程成績SELECTa.st_id,st_nm,scoreFROMstudenta,slt_couseb,cousecWHEREa.st_id=idANDb.cs_id=c.cs_idANDc.cs_nm='數(shù)據(jù)結(jié)構(gòu)'ORDERBYa.st_id操作9.7：用左外連接查詢沒有選修任何課程的學(xué)生學(xué)號、姓名SELECTa.st_id,st_nm,scoreFROMstudentaLEFTOUTERJOINslt_cousebONa.st_id=idWHEREb.cs_idISNULLORDERBYb.st_id操作9.8：用右外連接查詢選修各個課程的學(xué)生學(xué)號10數(shù)據(jù)查詢（6）——子查詢：用子查詢對各班人數(shù)進(jìn)行查詢（新增列）SELECTDISTINCTLEFT(a.st_id,6)AS班級,人數(shù)=(SELECTCOUNT(st_id)FROMstudentb WHERELEFT(a.st_id,6)=LEFT(b.st_id,6))FROMstudentaORDERBYLEFT(a.st_id,6)ASC：用子查詢對各課程的選課人數(shù)進(jìn)行查詢（新增列）SELECTDISTINCTa.cs_id,人數(shù)=(SELECTCOUNT(st_id)FROMslt_couseb WHEREa.cs_id=b.cs_id)FROMslt_couseaORDERBYa.cs_idASC：查詢選修了1002課程成績不及格的學(xué)生的學(xué)號、姓名與性別，并按姓名升序排序通過子查詢實現(xiàn)：——使用IN關(guān)鍵字SELECTst_id,st_nm,st_sexFROMstudentWHEREst_idIN( SELECTst_idFROMslt_couse WHEREcs_id='1002'ANDscore<60 )ORDERBYst_nm通過子查詢實現(xiàn)：——使用比較運算符SELECTst_id,st_nm,st_sexFROMstudentaWHERE( SELECTscoreFROMslt_couseb WHEREa.st_id=b.st_idANDcs_id='1002')<60ORDERBYst_nm：查詢“東方紅”同學(xué)所在班的學(xué)生信息，并按姓名降序排序通過子查詢實現(xiàn)：——IN運算符SELECTst_id,st_nm,st_sexFROMstudentWHERELEFT(st_dpid,6)IN( SELECTLeft(st_dpid,6)FROMstudentWHEREst_nm='東方紅' )ORDERBYst_nmDESC通過自連接查詢實現(xiàn)：——JOINSELECTs1.st_id,s1.st_nm,s1.st_sexFROMstudents1JOINstudents2ONLEFT(s1.st_dpid,6)=LEFT(s2.st_dpid,6)WHEREs2.st_nm='東方紅'ORDERBYs2.st_nm：查詢其它班比070511班某一學(xué)生的1002號課程成績高的學(xué)生信息（ANY/ALL）SELECT*FROMslt_couseWHEREscore>ANY( SELECTscoreFROMslt_couse WHEREcs_id='1002'ANDLEFT(st_id,6)='070511' )ANDLEFT(st_id,6)<>'070511'ANDcs_id='1002'操作10.