數(shù)據(jù)庫常見語句

數(shù)據(jù)庫常見語句--新建數(shù)據(jù)庫語句createDATABASE數(shù)據(jù)庫名--新建具體的數(shù)據(jù)庫createdatabase數(shù)據(jù)庫名on(name=數(shù)據(jù)文件名_dat,filename='存放路徑',size=10)--size指的是數(shù)據(jù)文件初始大小logon(name=日志文件名_log,filename='存放路徑',size=1MB--size指的是日志文件初始大小maxsize=20MB)日志文件最大為20MB--選擇數(shù)據(jù)庫use數(shù)據(jù)庫名--新建表createtable表名(列名類型,studentidintnotnull,[no]intidentity(1,1),--標識列[name]varchar(20),pwdvarchar(20),Emailnvarchar(50))--刪除列altertable表名dropcolumn列名--插入列altertable表名add列名類型插入多列ALTERTABLEstudentADDsdfaVARCHAR(20),workvarchar(10)在指定列后插入一列(該語句目前有問題)ALTERTABLEstudentADDsdfaVARCHAR(20)aftersex刪除多列altertablestudentdropcolumnsdfa,work--插入一行insertinto表名(列名1,列名2,列名3,列名4)values(賦值1,賦值2,賦值3,賦值4)/*注：into可以省略,如果列名為關鍵字那么用[列名]，如果該列為標識列那么不能插入，直接跳過如果該列類型不是int型,那么要單引號'賦值'如果某列具有默認值時,插入該列那么用default如：insertinto表名(sex,studentid,[name],pwd,Email)values(default,168,'陳林','axjl','csdn@168')*/--例如:insertinto表名(studentid,[name],pwd,Email)values(168,'陳林','axjl','csdn@168')--插入多行1.把A表中的局部列數(shù)據(jù)插入到B表中insertintoB(B.col1,B.col2,B.col3,……)selectA.col1,A.col2,A.col3……fromA2.把A表中的數(shù)據(jù)插入到New_Table中--此New_Table表是執(zhí)行查詢語句時新建的，不能預先存在selectA.col1,A.col2,……intoNew_TablefromA注:如果要插入標識列的話，就這樣寫selectidentity(1,1)as列名,A.col……into新表formA3.通過Union合并數(shù)據(jù)插入到A表中insertA(列名1,列名2,列名3)select'','',''Unionselect'','',''Unionselect'','',''--插入多列insertstudent(studentid,[name],sex,pwd,email)select1001,'陳林','男','axjl','axjl@168'unionselect1002,'馬力','女','dsa','ml@139'unionselect1003,'滬指','男','csdn','hz@168'unionselect1001,'陳浩南','男','kdn','chn@168'unionselect1001,'山脊','男','dsfg','sj@168'unionselect1001,'胡話','女','sdgas','hh@168'unionselect1001,'姜維','男','tdhe','jw@168'unionselect1001,'卡哇伊','女','fgh','kwy@168'千萬要注意:default只能和有values的語句搭配--下面的一個插入語句錯誤insertstudent(studentid,[name],sex,pwd,email)select1001,'asdgdf',default,'axjl','axjl@168'--此語句沒問題insertintostudent(studentid,[name],sex,pwd,email)values(1002,'馬超',default,'dfklgjs','dfg@WSDJ')SELECT*fromstudentwhere[name]='sdf'select*fromstudentwherestudentid=168--更新數(shù)據(jù)庫update表名set列名1=更新值,列名2=更新值……where條件1and條件2,……--把性別為男的全部改為默認值updatestudentsetsex=defaultwheresex='男'updatestudentset[name]='多啦A夢',sex='我是可愛多'where[name]='陳琳'and[no]=1--把A表的樣式類型復制到B表中--不要內(nèi)容select*intoBfromstudentwhere1<>1--刪除數(shù)據(jù)delete只會刪除整行，不會刪除某個字段所以delete后面不能有“列名”如果有有主外鍵關系的話，先刪外鍵再刪主鍵deletefrom表名where列名=‘刪除條件’truncatetable表名--注：truncate刪除的是所有的行truncate不能用于有外鍵關系的表--truncate比delete速度快--truncate刪除后再插入時標識列從0開始--delete刪除后再插入時標識列繼續(xù)累加--使用select查詢語句select列名from表名where條件orderby排序的列名ascordesc--asc升序desc降序默認為asc1.查詢?nèi)縮elect*from表名2.查詢局部行列select列名1,列名2,列名3...from表名where條件3.在查詢中使用列名查詢不是“男”的學員selectcol1as編號,col2as姓名...from表名wheresex<>'男'4.查詢的列合并為一列select列名1+'.'+列名2as姓名or'姓名'=列名1+'.'+列名2from表名where條件5.查詢null或者notnull或者''select列名1,列名2,列名3...from表名whereemailisnulloremailisnotnulloremail=''--注意:null不等于''select*fromBwhereemailisnotnull6.在查詢中使用常量列select姓名=[name],性別=sex,'湖北黃岡'as地址fromBwheresex='女'7.查詢成績前三名selecttop3列名from表名orderbyscoredesc8.按百分比%查詢20%的女生的愛好selecttop20percent[name],likedfrom表名wheresex=09.查詢分數(shù)降低10%后加5分還及格的人的信息按由高到低排序selectcol1as編號,(score*0.9+5)as綜合成績from表名where(score*0.9+5)>60orderbyscoredesc10.查詢A表和B表，并把每張表的列合并，然后連接兩張表再按降序排序selectA.col1+'.'+A.col2asnew_NamefromAunionselectB.col1+'.'+B.col2asnew_NamefromBorderbynew_Namedesc11。按多個條件排序進行查詢selectcol1as編號,col2as姓名...from表名where查詢條件orderby列名1,列名2字符串函數(shù)12.查詢一個指定的字符串在另一個字符串中的起始位置charindex(a,b,c)a為要查詢的字符串b為a所在的字符串c〔int〕為從第幾個字符開始查找返回一個指定的字符串在另一個字符串中的起始位置下標起始位置為1，不是0selectcharindex('@',email,2)as下標,upper(pwd)as密碼fromstudentwherelen(pwd)=3andstudentidlike'%2'13.查詢字符串的長度len(a)a任意字符串select*fromstudentwherelen(pwd)>314.去除字符串left或者right的空格并連接selectRtrim('愛新覺羅')+'.'+Ltrim('守衛(wèi)劍閣')as魔獸地圖15.替換一個字符串中指定的所有字符replaceselectreplace(charA,'c','b')把字符串charA中的字符‘c’替換為‘b’例子:selectreplace(Rtrim('愛新覺羅')+'.'+Ltrim('守衛(wèi)劍閣'),'新','心')as魔獸地圖selectreplace(replace(pwd,'o','0'),'i','1')16.在一個字符串中，在指定位置刪除指定長度的字符串，并在該位置插入一個新的字符串selectstuff(charA,(int)a,(int)b,charC)在字符串charA中,從b位置開始，刪除長度為b的字符串，并在b位置處插入字符串charC例子:selectstuff(replace(Rtrim('愛新覺羅')+'.'+Ltrim('守衛(wèi)劍閣'),'新','心'),5,1,'版')as魔獸地圖17.刪除字符串左邊的空格Ltrim()selectLtrim('守衛(wèi)劍閣')18.刪除字符串右邊的空格Rtrim()selectRtrim('愛新覺羅')+Ltrim('守衛(wèi)劍閣')19.從字符串右邊返回指定數(shù)目的字符Right()selectRight('愛新覺羅守衛(wèi)劍閣',4)日期函數(shù)20.獲取當天日期:Getdate()例子:selectGetdate()as日期時間21.日期時間的更改:DateAdd(YYorMMorDD,(int)a,'月/日/年')例子:selectDateAdd(YY,-10,Getdate())as十年前的日期selectDateAdd(MM,-2,Getdate())as二月前的日期selectDateAdd(DD,-2,Getdate())as二天前的日期selectDateAdd(DD,2,'02/01/1989')as二天前的日期22.兩個日期的指定局部的日期區(qū)別:DateDiff(YYorMMorDD,'月a/日a/年a','月b/日b/年b')后面的減去前面的例子:selectDateDiff(YY,'12/11/1989','12/11/2008')as相隔年份selectDateDiff(MM,'12/11/2008','8/11/2008')as相隔月份selectDateDiff(DD,'12/11/2008','12/2/2008')as相隔天數(shù)23.返回星期幾:DateName(dw,'01/01/2001')dw指定的日期星期幾例子:selectDateName(dw,'01/01/2001')24.返回日期中指定日期局部的整數(shù)型式：DatePart()selectDatePart(day,GetDate())as日期號selectDatePart(MM,GetDate())as日期號selectDatePart(YY,GetDate())as日期號數(shù)學函數(shù)25.取絕對值：Abs()selectAbs(-45)26.取>=指定數(shù)值、表達式的最小整數(shù)：Ceiling()selectCeiling(45.56)27.取<=指定數(shù)值、表達式的最da整數(shù)：Floor()selectFloor(45.56)28.取X的Y次方:Power(x,y)selectPower(2,3)29.把一個數(shù)值表達式四舍五入為指定的精度:Round()selectRound(3.1415,3)30.判斷數(shù)字為正數(shù)負數(shù)還是為0正數(shù)返回+1負數(shù)返回-10返回0Sign()selectSign(-8)31.取浮點表達式的平方根:Sqrt()selectSqrt(9)系統(tǒng)函數(shù)32.轉換數(shù)據(jù)類型Convert(轉換類型,要轉換的數(shù)據(jù))selectConvert(int,DateName(YY,GetDate()))+Convert(int,DateName(MM,GetDate()))as當年加當天33.返回指定表達式的字節(jié)數(shù)Datalength()selectDatalength('sdfalksd')selectCurrent_Useras你登陸的用戶名selectHost_Name()as電腦名selectSystem_Useras當前所登錄的用戶名稱selectUser_Name(1)as從給定的用戶id返回用戶名特殊排序先按“-”前半局部倒序再按"-"的后半局部倒序selectnumas級別,姓名=[name]fromstudentorderbyconvert(int,left(num,charindex('-',num)-1))desc,convert(int,ltrim(stuff(num,1,charindex('-',num),'')))desc34.like的妙用例子：select*fromstudentswherenameslike'胡%'andlikednotlike'%S'35.between的妙用select*from表名wherescorenotbetween60and8036.in的妙用select*from表名where列名in('A','B','C')orderby列名(ascordesc)聚合函數(shù)1.求和函數(shù):sum()selectsum(列名)from表名where條件2.求平均數(shù)函數(shù):avg()selectavg(列名)from表名where條件3.求最大值Max最小值MinselectMax(列名)as最高分,Min(列名)as最低分from表名where條件4.統(tǒng)計函數(shù):count()selectcount(表達式or列名or*)from表名where條件selectcount(age)fromstudentswhereage>225.分組查詢:groupby(此語句在select中所指定的列有限)select列名,與分組相對應的列from表名groupby列名例子:selectcourseid,avg(score)as課程平均分fromscoregroupbycourseidselectstudentidas學員編號,courseidas內(nèi)部測試,avg(score)as內(nèi)部測試平均分from表名groupbystudentid,courseid6.Having的妙用要求：查詢補考過的學員的平均分selectstudentidas學員編號,courseidas內(nèi)部測試,avg(score)as內(nèi)部測試平均分from表名groupbystudentid,courseidHavingcount(score)>17.wheregroupbyhaving在查詢語句中的次序例子:selectstudentid,courseid，avg(score)wherescore>60groupbystudentid,courseidHavingcount(score)>1select部門編號,count(*)form員工信息表where工資>=2000groupby部門編號havingcount(*)>18.多表連接1.內(nèi)連接：innerJoina.在where子句中指定連接條件(A表B表)selectA.列名,B.列名fromA,BwhereA.col=B.colb.在from子句中使用Join...inselectA.列名,B.列名from表1asAinnerJoin表2asBon(A.col=B.col)selectA.列名,B.列名from表1asAinnerJoin表2asBon(A.col=B.col)where條件c.多表外連接selectA.列名,B.列名,C.列名from表1asAinnerJoin表2asBon(A.col=B.col)innerJoin表3asCon(C.col=B.col)2.外連接a.左連接：leftJoinselectA.列名,B.列名from表1asAleftJoin表2asBon(A.col=B.col)b.右連接：rightJoinselectA.列名,B.列名from表1asArightJoin表2asBon(A.col=B.col)c.全連接：fullJoinselectA.列名,B.列名from表1asAfullJoin表2asBon(A.col=B.col)3.交叉連接：crossJoin〔行數(shù)=A列*B列〕selectA.列名,B.列名from表1asAcrossJoin表2asBon(A.col=B.col)9.查詢部門名selectdistinctbumenIdas部門名注意:distinct是不同的意思，如果沒distinct的話，那么查詢結果會出現(xiàn)重復的部門名查詢一張表中的基數(shù)行和偶數(shù)行表名:tbl字段名：A主鍵字段:IDKEY(標識列，標識種子:1,標識增長量:1)selectA,identity(int,1,1)asIDintotemtablefromtblselectsum(A)as奇數(shù)列匯總fromtemtablewhereID%2<>0selectsum(A)as奇數(shù)列匯總fromtemtablewhereID%2=0銀行卡恢復Users表〔主表〕CardIDUserName16張三23陳林25馬力34胡志36老杜Account表〔子表〕CountIDCardIDScore1163522316833614525馬力子表中沒有要求插入這兩條記錄Score為234胡志insertintoAccount(Account.CardID,Account.Score)selectUsers.CardID,2fromUsersleftjoinAccounton(Account.CardID=Users.CardID)whereAccount.CardIDisnull在指定列的后面插入一列方法步驟：(原表stu已存在)1.新建一張列序即類型為用戶所要求的內(nèi)容為空的表sut3createtablestu3(numberintidentity(1,1),namesvarchar(20)notnull,ageint,sexbitdefault1,likednvarchar(20))2.將原表的內(nèi)容插入到新建表stu3中insertintostu3(names,sex,liked)selects,stu.sex,stu.likedfromstu3.刪除原有表studroptablestu4.把stu3表中的信息插入到新建的表stu中selectidentity(int,1,1)asnumber,s,stu3.age,stu3.sex,stu3.likedintostufromstu35.查看結果select*fromstu數(shù)據(jù)庫設計和高級查詢表的標準設計：必須滿足三大范式1.原子性〔不可再分〕2.非主鍵字段必須全部依賴于主鍵3.非主鍵不能傳遞依賴--如何檢測是否存在數(shù)據(jù)庫stuDB，如果存在，先刪除，再創(chuàng)立--SQLServer將數(shù)據(jù)庫的清單存放在master系統(tǒng)數(shù)據(jù)庫的sysdatabases表中usemaster--設置當前數(shù)據(jù)庫為master，以便訪問sysdatabases表goifexists(select*fromsysdatabaseswherename='stuDB')dropdatabasestuDBcreatedatabasestuDBonprimary(name='stuDB_data',filename='D:\project\stuDB_data.mdf',size=5mb,maxsize=100mb,filegrowth=15%)logon(name='stuDB_log',filename='D:\project\stuDB_log.ldf',size=2MB,filegrowth=1MB)go--創(chuàng)立學員信息表stuInfocreatetablestuINfo(stunamevarchar(20)notnull,stuNochar(6)notnull,stuAgeintnotnull,stuIDnumeric(18,0),stuSeatsmallintidentity(1,1),stuAddresstext)go--創(chuàng)立學員成績表stuMarksusestuDBgoifexists(select*fromsysobjectswherename='stuInfo')droptablestuInfocreatetablestuMarks(ExamNochar(7)notnull,stuNoschar(6)notnull,writenExamintnotnull,LabExamintnotnull)go--刪除學員信息表stuInfodroptablestuInfo--添加主鍵約束〔將stuNo作為主鍵〕altertablestuInfoaddconstraintpk_stuNoprimarykey(stuNo)--添加唯一約束altertablestuInfoaddconstraintUQ_stuIDunique(stuID)--添加默認約束altertablestuInfoaddconstraintDF_stuAddressdefault('地址不詳')forstuAddress--添加檢查約束altertablestuInfoaddconstraintCK_stuAgecheck(stuAgebetween15and40)--添加外鍵約束〔主表stuInfo和從表stuMarks建立關系，關聯(lián)字段為stuNo〕altertablestuMarksaddconstraintFK_stuNoforeignkey(stuNo)referencesstuInfo(stuNos)go--查看表的約束是否存在execsp_helpconstraint表名execsp_helpconstraintstuMarks--刪除約束altertable表名dropconstraint約束名--例如:altertablestuInfodropconstraintDF_stuAddress--創(chuàng)立登陸賬戶--添加windows登陸賬戶execsp_grantlogin'計算機名\計算機登錄名'execsp_grantlogin'HGACCP-1XL60HQ0\Administrator'--添加SQL登陸賬戶execsp_addlogin'賬戶名','密碼'execsp_addlogin'chenlin','axjl@168'go--創(chuàng)立數(shù)據(jù)庫用戶usestuDBgoexecsp_grantdbaccess'HGACCP-1XL60HQ0\Administrator','AdministratorDBUser'execsp_grantdbaccess'chenlin','chenlinDBUser'--給數(shù)據(jù)庫用戶授權grant權限[on表名]to數(shù)據(jù)庫名--給用戶chenlinDBUser授予建表的權限usestuDBgrantcreatetabletochenlinDBUser--給用戶chenlinDBUser授予增刪查改的權限grantselect,insert,update,deleteonstuMarkstochenlinDBUser--刪除用戶對表的權限r(nóng)evoke權限名on表名to用戶名例如:revokeselectonstuMarkstochenlinDBUser--刪除數(shù)據(jù)庫用戶execsp_revokedbaccess用戶名局部變量的聲明declare@變量名數(shù)據(jù)類型例如:declare@namevarchar(8)局部變量的賦值set@name=值或者select@name=值(注意,select語句賦值要求該語句返回信息不能多于一條,否那么將把最后一條信息賦值給變量)實例1:查找李文才的左右同桌declare@namevarchar(8)set@name='李文才'select*fromstuInfowherestuName=@namedeclare@seatintselect@seat=stuSeatfromstuInfowherestuName=@nameselect*fromstuInfowhere(stuSeat=@seat+1)or(stuSeat=@seat-1)go全局變量@@error--最后一個T-SQL錯誤的錯誤號@@identity--最后一次插入的標識列@@language--當前使用的語言@@max_connections--可以新建的同時連接的最大數(shù)目@@rowcount--受上一個SQL語句影響的行數(shù)@@servername--本地效勞器的名稱@@servicename--該計算機上的SQL效勞器的名稱@@timeticks--當前計算機上沒每刻度的微秒數(shù)@@trancount--當前連接翻開的事物數(shù)@@version--SQLServer的版本信息輸出語句print局部變量or字符串select局部變量as自定義列名例如：print'效勞器的名稱:'+@@servernameprint'當前錯誤號'+convert(varchar(5),@@error)select@@servernameas'效勞器的名稱'1if-else條件語句if(條件)begin語句1語句2endelse...insertintostuMarks(ExamNo,stuNo,writenExam,LabExam)select's271811','s25303',80,58unionselect's271813','s25302',50,90unionselect's271815','s25302',65,0unionselect's271816','s25301',77,82insertintostuInfo(stuname,stuNo,stuSex,stuAge,stuAddress)select'張秋麗','s25301','男',18,'北京海淀'unionselect'李文才','s25302','男',31,'地址不詳'unionselect'李思文','s25303','女',22,'河南洛陽'unionselect'歐陽俊雄','s25304','男',28,'新疆'altertablestuInfodropconstraintUQ_stuIDaltertablestuInfodropcolumnstuIDselect*fromstuInfoselect*fromstuMarks例子：統(tǒng)計并顯示本班筆試平均分，70以上，顯示”成績優(yōu)秀“，并顯示前3名學員的考試信息如果70以下，顯示“成績較差”，并顯示后三名學員的考試信息declare@avgfloatselect@avg=avg(writenExam)fromstuMarksprint'本班平均分:'+convert(varchar(5),@avg)if(@avg>70)beginprint'本班筆試成績優(yōu)秀，前三名的成績?yōu)?selecttop3*fromstuMarksorderbywritenExamdescendelsebeginprint'本班筆試成績較差，后三名的成績?yōu)?selecttop3*fromstuMarksorderbywritenExamend2while循環(huán)語句while(條件)語句或語句塊[break]例子:統(tǒng)計不及格的人數(shù)，如果有人不及格，那么加2分循環(huán)判斷insertintostuMarks(examNo,stuNo,writenExam,LabExam)values('S271819','S25318',56,48)select*fromstuMarksdeclare@nintwhile(1=1)beginselect@n=count(*)fromstuMarkswherewritenExam<60if(@n>0)updatestuMarkssetwritenExam=writenExam+2elsebreakendprint'加分后的成績?nèi)缦?'select*fromstuMarks3case多分支語句(case語句中沒有begin,只有end)casewhen條件1then結果1when條件2then結果2else其他結果end例子:采用ABCDE五級打分制來顯示筆試成績1.A級>=902.B級80~893.C級70~794.D級60~695.E級<60select*fromstuMarksprint'ABCDE五級顯示成績?nèi)缦?'selectstuNo,成績=casewhenwritenExam<60then'E'whenwritenExambetween60and69then'D'whenwritenExambetween70and79then'C'whenwritenExambetween80and89then'B'else'A'endfromstuMarks批處理:go注意:SQLServer規(guī)定：建庫建表存儲過程視圖都必須在末尾添加"GO"簡單子查詢例子：１.查詢年齡比”李斯文“大的學員，要求顯示這些學員的信息方法1：declare@ageintselect@age=stuAgefromstuINfowherestuName='李思文'select*fromstuInfowherestuAge>@ageGO方法2：select*fromstuINfowherestuAge>(selectstuAgefromstuINfowherestuName='李思文')注意:將子查詢和比擬運算符聯(lián)合使用，必須保證子查詢返回的值不能多于一個例子：２.查詢筆試剛好通過60分的學員名單方法1：selectstuNamefromstuInfoinnerjoinstuMarksonstuInfo.stuNo=stuMarks.stuNowherewritenExam=92GO方法２：selectstuNamefromstuInfowherestuNo=(selectstuNofromstuMarkswherewritenExam=92)GOin和notin子查詢要查詢筆試成績剛好為98分的學員〔而98分的學員又不只一個〕selectstuNamefromstuInfowherestuNoin(selectstuNofromstuMarkswherewritenExam=98)GO查詢參加考試的學員名單selectstuNamefromstuInfowherestuNoin(selectstuNofromstuMarks)GO查詢沒參加考試的學員名單selectstuNamefromstuInfowherestuNonotin(selectstuNofromstuMarks)GOdeletefromstuMarkswhereLabExam=48andwritenExam<>88exists和notexists字查詢ifexists(子查詢)語句例子：如果有人筆試到達80分以上，那么每人加2分，否那么每人加5分ifexists(select*fromstuMarkswherewritenExam>80)beginprint'本班有人筆試成績高于80分，每人加2分，加分后的成績?yōu)?updatestuMarkssetwritenExam=writenExam+2select*fromstuMarksendelsebeginprint'本班沒人筆試成績高于80分，每人加5分，加分后的成績?yōu)?updatestuMarkssetwritenExam=writenExam+5select*fromstuMarksendGOnotexists子查詢ifnotexists(select*fromstuMarkswherewritenExam>60andlabExam>60)beginprint'本班無一人通過考試，試題偏難，每人加3分，加分后的成績?yōu)?'updatestuMarkssetwritenExam=writenExam+3,labExam=labExam+3select*fromstuMarksendelsebeginprint'本班考試成績一般，每人只加1分，加分后的成績?yōu)?'updatestuMarkssetwritenExam=writenExam+1,labExam=labExam+1select*fromstuMarksendGO綜合練習1.統(tǒng)計本次考試的缺考情況select應到人數(shù)=(selectcount(*)fromstuInfo),實到人數(shù)=(selectcount(*)fromstuMarks),缺考人數(shù)=((selectcount(*)fromstuInfo)-(selectcount(*)fromstuMarks))2.提取學員的成績信息并保存結果，包括姓名學號筆試機試是否通過ifexists(select*fromsysobjectswherename='newTable')droptablenewTableselectstuName,stuInfo.stuNo,writenExam,LabExam,isPass=casewhenwritenExam>=60andlabExam>=60then1else0endintonewTablefromstuInfoleftjoinstuMarksonstuInfo.stuNo=stuMarks.stuNo3.比擬筆試平均分和機試平均分，較低分的進行循環(huán)加分，但提分后最高分不能高于97分declare@avgWritennumeric(4,1),@avgLabnumeric(4,1)select@avgWriten=avg(writenExam)fromnewTablewherewritenExamisnotnullselect@avgLab=avg(labExam)fromnewTablewherelabExamisnotnullif@avgWriten<@avgLabwhile(1=1)beginupdatenewTablesetwritenExam=writenExam+1if(selectmax(writenExam)fromnewTable)>=97breakendelsewhile(1=1)beginupdatenewTablesetlabExam=labExam+1if(selectmax(labExam)fromnewTable)>=97breakendupdatenewTablesetisPass=casewhenwritenExam>=60andlabExam>=60then1else0end--select*fromnewTable4.提分后，統(tǒng)計學員的成績和通過情況select姓名=stuName,學號=stuNo,筆試成績=casewhenwritenExamisnullthen'缺考'elseconvert(varchar(5),writenExam)end,機試成績=casewhenlabExamisnullthen'缺考'elseconvert(varchar(5),labExam)end,是否通過=casewhenisPass=1then'是'else'否'endfromnewTable5.提分后統(tǒng)計學員的通過率情況select總人數(shù)=count(*),通過人數(shù)=sum(isPass),通過率=(convert(varchar(5),avg(isPass*100))+'%')fromnewTable事務事務四大特性:1.原子性:事務中有一處失敗，整個事務失敗。2.一致性:事務處理前后都必須一致。3.隔離性:事務之間必須相互獨立，不能有依賴。4.持久性:事務處理后，他對系統(tǒng)的影響是永久的。T-SQL事務語句開始事務:begintransaction提交事務:committransaction回滾〔撤銷〕事務:rollbacktransaction實例1/*----------------創(chuàng)立表--------------------*/usestuDBGOifexists(select*fromsysobjectswherename='bank')droptablebankGOcreatetablebank(customerNamechar(10),--顧客姓名currentMoneyMoney--當前余額)Goaltertablebank--添加約束，賬戶余額必須>=1AddconstraintCK_currentMoneycheck(currentMoney>=1)GOinsertintobank(customerName,currentMoney)values('張三',1000)insertintobank(customerName,currentMoney)values('李四',1)select*frombankGo實例2將張三的錢1000塊轉到李四賬戶updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='張三'updatebanksetcurrentMoney=currentMoney+1000wherecustomerName='李四'GOselect*frombankGO實例3usestuDBGO--恢復原來的數(shù)據(jù)updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='李四'setnocounton--不顯示受影響的行數(shù)信息print'查看轉賬事務錢的余額'select*frombankGO/*--開始事務〔指定事務從此處開始，后續(xù)的T-SQL語句都是一個整體〕--*/begintransaction/*--定義變量，用于累計事務執(zhí)行過程中的錯誤--*/declare@erroeSumintset@erroeSum=0--初始化為0，即無錯誤/*--轉賬:張三的賬戶少1000塊，李四的賬戶多1000塊--*/updatebanksetcurrentMoney=currentMoney-800wherecustomerName='張三'set@erroeSum=@erroeSum+@@error--累計是否有錯誤updatebanksetcurrentMoney=currentMoney+800wherecustomerName='李四'set@erroeSum=@erroeSum+@@error--累計是否有錯誤print'查看轉賬事務過程中的余額'select*frombank/*--根據(jù)是否有錯誤，確定事務提交還是撤銷--*/if@erroeSum<>0--如果有錯誤beginprint'交易失敗,回滾事務'rollbacktransactionendelsebeginprint'交易成功,提交事務，寫入硬盤，永久保存'committransactionendGOprint'查看轉賬事務后的余額'select*frombankGO索引索引分類：1.唯一索引:唯一索引要求兩行不能有相同的索引值。2.逐漸索引:給表定義一個主鍵將自動創(chuàng)立主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引中的每個值都必須唯一。2.聚集索引:聚集索引中，表中各行的物理順序與鍵值的邏輯〔索引〕順序相同，每張表里面只能有一個聚集索引。〔如果是非聚集索引，表中各行的物理順序與鍵值順序不匹配〕注意:一個表中只能創(chuàng)立一個聚集索引，但可以有多個非聚集索引，設置某列為主鍵，該列就默認為聚集索引。創(chuàng)立索引語法:create[unique][clustered|nonclustered]indexindex_nameontable_name(column_name[,column_name2]...)[withfillfactor=x]其中:unique指定唯一索引，可選。clustered、nonclustered指定是聚集索引還是非聚集索引，可選。fillfactor表示填充因子，指定一個0~~100的值，該值指示索引頁填滿的空間所占的百分比。實例:usestuDBGO/*--檢測是否存在索引(索引存放在系統(tǒng)表sysindexes中)--*/ifexists(selectnamefromsysindexeswherename='ix_stuMarks_writtenExam')dropindexstuMarks.ix_stuMarks_writtenExam--刪除索引/*--筆試列創(chuàng)立非聚集索引，填充因子為30%--*/createnonclusteredindexix_stuMarks_writtenExamonstuMarks(writtenExam)withfillfactor=30GO實例:/*--指定按索引:ix_stuMarks_writtenExam查詢--*/select*fromstuMarks(index=ix_stuMarks_writtenExam)or(index(ix_stuMarks_writtenExam))wherewrittenExambetween60and90視圖創(chuàng)立視圖語句:createviewview_nameas<select語句>實例：usestuDBGO/*--檢測是否存在:試圖存放在系統(tǒng)表sysobjects中--*/ifexists(select*fromsysobjectswherename='view_stuInfo_stuMarks')dropviewview_stuInfo_stuMarksGO/*--創(chuàng)立視圖:查看學員的成績情況--*/createviewview_stuInfo_stuMarksasselect姓名=stuName,學號=stuInfo.stuNo,筆試成績=writtenExam,機試成績=labExam,平均分=(weittenExam+labExam)/2fromstuInfoleftjoinstuMarksonstuInfo.stuNo=stuMarks.stuNoGO/*--使用視圖:視圖是一個虛擬表，可以像物理表一樣翻開--*/select*fromview_stuInfo_stuMarks存儲過程〔存放在sysobjects中〕存儲過程的幾大優(yōu)點:1.允許模塊化程序設計〔只需創(chuàng)見一次存儲過程并將其存儲在數(shù)據(jù)庫中，屢次調(diào)用〕2.執(zhí)行速度比T-SQL批處理更快3.減少網(wǎng)絡流量4.可作為平安機制使用存儲過程分類：1.系統(tǒng)存儲過程2.自定義存儲過程常見的系統(tǒng)存儲過程sp_databases列出效勞器上的所有數(shù)據(jù)庫sp_helpdb報告有關指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當前環(huán)境下可查詢的對象的列表sp_columns返回某個表列的信息sp_help查看某個表的所有信息sp_helpconstraint查看某個表的約束sp_helpindex查看某個表的索引sp_stored_procedures列出當前環(huán)境中的所有存儲過程sp_password添加或修改登錄賬戶的密碼sp_helptext顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實際文本實例--Purpose:常用系統(tǒng)存儲過程的使用execsp_databases--列出當前系統(tǒng)中的數(shù)據(jù)庫execsp_renamedb'old_DB','new_DB'--更改數(shù)據(jù)庫的名稱usestuDBGOexecsp_tables--當前數(shù)據(jù)庫可查詢的對象的列表execsp_columns表名--查看某個表列的信息execsp_help表名--查看某個表所有信息execsp_helpconstraint表名--查看某個表的約束execsp_helpindex表名--查看某個表的索引execsp_helptext'視圖名'--查看視圖的語句文本execsp_stored_procedures--返回當前數(shù)據(jù)庫中的所有存儲過程列表新建文件夾、列出文件列表〔必須啟動SQL的外圍配置器〕execxp_cmdshellDOS命令[NO_output]實例usemasterGO/*--創(chuàng)立數(shù)據(jù)庫bankDB,要求保存在D:\bank--*/execxp_cmdshell'mkdirD:\bank',NO_output--創(chuàng)立文件夾D:\bank--創(chuàng)立庫bankDBifexists(select*fromsysdatabaseswherename='bankDB')dropdatabasebankDBGOcreatedatabasebankDBon(name='bankDB_data',filename='D:\bank\bankDB_data.mdf',size=3MB,filegrowth=15%)logon(name='bankDB_log',filename='D:\bank\bankDB_log.ldf',size=3MB,filegrowth=15%)GOexecxp_cmdshell'dirD:\bank\'--查看文件自定義存儲過程創(chuàng)立不帶參數(shù)存儲過程語句:createproc[edure]存儲過程名[{@參數(shù)1數(shù)據(jù)類型}[=默認值][output],......{@參數(shù)1數(shù)據(jù)類型}[=默認值][output]]asSQL語句實例usestuDBGO/*--檢測是否存在，存儲過程存放在sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dropprocedureproc_stuGO/*--創(chuàng)立存儲過程--*/createprocedureproc_stuasdeclare@writtenAvgfloat,@labAvgfloat--筆試平均分和機試平均分變量select@writtenAvg=avg(writtenExam),@labAvg=avg(labExam)fromstuMarksprint'筆試平均分:'+convert(varchar(5),@writtenAvg)print'機試平均分:'+convert(varchar(5),@labAvg)if(@writtenAvg>70and@labAvg>70)print'本班考試成績:優(yōu)秀'elseprint'本班考試成績:較差'print'--------------------------------------------------------------------------'selectstuName,stuInfo.stuNo,writtenExam,labExamfromstuInfoinnerjoinstuMarksonstuInfo.stuNo=stuMarks.stuNowherewrittenExam<60orlabExam<60GO/*--調(diào)用執(zhí)行存儲過程--*/execproc_stu--調(diào)用存儲過程的語法:exec過程名[參數(shù)]創(chuàng)立帶參數(shù)存儲過程語句:createproc[edure]存儲過程名{@參數(shù)1數(shù)據(jù)類型}[=默認值][output],......{@參數(shù)1數(shù)據(jù)類型}[=默認值][output]asSQL語句實例usestuDBGO/*--檢測是否存在，存儲過程存放在sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dropprocedureproc_stuGO/*--創(chuàng)立存儲過程--*/createprocedureproc_stu@writtenPassint,--輸入?yún)?shù):筆試及格線@labPassint--輸入?yún)?shù):機試及格線asprint'--------------------------------------------------------------------------'print'參加本次考試沒有通過的學員'selectstuName,stuInfo.stuNo,writtenExam,labExamfromstuInfoinnerjoinstuMarksonstuInfo.stuNo=stuMarks.stuNowherewrittenExam<@writtenPassorlabExam<@labPassGO/*--調(diào)用本次考試機試偏難，機試的及格線定為55分，筆試及格線定為60分--*/execproc_stu60,55--或這樣調(diào)用:execproc_stu@labPass=55,@writtenPass=60/*---調(diào)用存儲過程---execproc_stu---都采用默認值:筆試和機試及格線都為60分execproc_stu64---機試采用默認值，筆試及格線64，機試60分execproc_stu60,65---都不采用默認值，筆試及格線60，機試及格線65--錯誤的調(diào)用方法:execproc_stu,55---希望筆試采用默認值，機試及格線為55--正確的調(diào)用方式:execproc_stu@labPass=55---筆試采用默認值，機試及格線為55---*/實例usestuDBGO/*--檢查是否存在:存儲過程存放在系統(tǒng)表sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dro