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