數(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),--標(biāo)識(shí)列[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可以省略,如果列名為關(guān)鍵字那么用[列名]，如果該列為標(biāo)識(shí)列那么不能插入，直接跳過如果該列類型不是int型,那么要單引號(hào)'賦值'如果某列具有默認(rèn)值時(shí),插入該列那么用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í)行查詢語句時(shí)新建的，不能預(yù)先存在selectA.col1,A.col2,……intoNew_TablefromA注:如果要插入標(biāo)識(shí)列的話，就這樣寫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的語句搭配--下面的一個(gè)插入語句錯(cuò)誤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,……--把性別為男的全部改為默認(rèn)值updatestudentsetsex=defaultwheresex='男'updatestudentset[name]='多啦A夢(mèng)',sex='我是可愛多'where[name]='陳琳'and[no]=1--把A表的樣式類型復(fù)制到B表中--不要內(nèi)容select*intoBfromstudentwhere1<>1--刪除數(shù)據(jù)delete只會(huì)刪除整行，不會(huì)刪除某個(gè)字段所以delete后面不能有“列名”如果有有主外鍵關(guān)系的話，先刪外鍵再刪主鍵deletefrom表名where列名=‘刪除條件’truncatetable表名--注：truncate刪除的是所有的行truncate不能用于有外鍵關(guān)系的表--truncate比delete速度快--truncate刪除后再插入時(shí)標(biāo)識(shí)列從0開始--delete刪除后再插入時(shí)標(biāo)識(shí)列繼續(xù)累加--使用select查詢語句select列名from表名where條件orderby排序的列名ascordesc--asc升序desc降序默認(rèn)為asc1.查詢?nèi)縮elect*from表名2.查詢局部行列select列名1,列名2,列名3...from表名where條件3.在查詢中使用列名查詢不是“男”的學(xué)員selectcol1as編號(hào),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.查詢分?jǐn)?shù)降低10%后加5分還及格的人的信息按由高到低排序selectcol1as編號(hào),(score*0.9+5)as綜合成績from表名where(score*0.9+5)>60orderbyscoredesc10.查詢A表和B表，并把每張表的列合并，然后連接兩張表再按降序排序selectA.col1+'.'+A.col2asnew_NamefromAunionselectB.col1+'.'+B.col2asnew_NamefromBorderbynew_Namedesc11。按多個(gè)條件排序進(jìn)行查詢selectcol1as編號(hào),col2as姓名...from表名where查詢條件orderby列名1,列名2字符串函數(shù)12.查詢一個(gè)指定的字符串在另一個(gè)字符串中的起始位置charindex(a,b,c)a為要查詢的字符串b為a所在的字符串c〔int〕為從第幾個(gè)字符開始查找返回一個(gè)指定的字符串在另一個(gè)字符串中的起始位置下標(biāo)起始位置為1，不是0selectcharindex('@',email,2)as下標(biāo),upper(pwd)as密碼fromstudentwherelen(pwd)=3andstudentidlike'%2'13.查詢字符串的長度len(a)a任意字符串select*fromstudentwherelen(pwd)>314.去除字符串left或者right的空格并連接selectRtrim('愛新覺羅')+'.'+Ltrim('守衛(wèi)劍閣')as魔獸地圖15.替換一個(gè)字符串中指定的所有字符replaceselectreplace(charA,'c','b')把字符串charA中的字符‘c’替換為‘b’例子:selectreplace(Rtrim('愛新覺羅')+'.'+Ltrim('守衛(wèi)劍閣'),'新','心')as魔獸地圖selectreplace(replace(pwd,'o','0'),'i','1')16.在一個(gè)字符串中，在指定位置刪除指定長度的字符串，并在該位置插入一個(gè)新的字符串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.獲取當(dāng)天日期:Getdate()例子:selectGetdate()as日期時(shí)間21.日期時(shí)間的更改: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.兩個(gè)日期的指定局部的日期區(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日期號(hào)selectDatePart(MM,GetDate())as日期號(hào)selectDatePart(YY,GetDate())as日期號(hào)數(shù)學(xué)函數(shù)25.取絕對(duì)值：Abs()selectAbs(-45)26.取>=指定數(shù)值、表達(dá)式的最小整數(shù)：Ceiling()selectCeiling(45.56)27.取<=指定數(shù)值、表達(dá)式的最da整數(shù)：Floor()selectFloor(45.56)28.取X的Y次方:Power(x,y)selectPower(2,3)29.把一個(gè)數(shù)值表達(dá)式四舍五入為指定的精度:Round()selectRound(3.1415,3)30.判斷數(shù)字為正數(shù)負(fù)數(shù)還是為0正數(shù)返回+1負(fù)數(shù)返回-10返回0Sign()selectSign(-8)31.取浮點(diǎn)表達(dá)式的平方根:Sqrt()selectSqrt(9)系統(tǒng)函數(shù)32.轉(zhuǎn)換數(shù)據(jù)類型Convert(轉(zhuǎn)換類型,要轉(zhuǎn)換的數(shù)據(jù))selectConvert(int,DateName(YY,GetDate()))+Convert(int,DateName(MM,GetDate()))as當(dāng)年加當(dāng)天33.返回指定表達(dá)式的字節(jié)數(shù)Datalength()selectDatalength('sdfalksd')selectCurrent_Useras你登陸的用戶名selectHost_Name()as電腦名selectSystem_Useras當(dāng)前所登錄的用戶名稱selectUser_Name(1)as從給定的用戶id返回用戶名特殊排序先按“-”前半局部倒序再按"-"的后半局部倒序selectnumas級(jí)別,姓名=[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)計(jì)函數(shù):count()selectcount(表達(dá)式or列名or*)from表名where條件selectcount(age)fromstudentswhereage>225.分組查詢:groupby(此語句在select中所指定的列有限)select列名,與分組相對(duì)應(yīng)的列from表名groupby列名例子:selectcourseid,avg(score)as課程平均分fromscoregroupbycourseidselectstudentidas學(xué)員編號(hào),courseidas內(nèi)部測(cè)試,avg(score)as內(nèi)部測(cè)試平均分from表名groupbystudentid,courseid6.Having的妙用要求：查詢補(bǔ)考過的學(xué)員的平均分selectstudentidas學(xué)員編號(hào),courseidas內(nèi)部測(cè)試,avg(score)as內(nèi)部測(cè)試平均分from表名groupbystudentid,courseidHavingcount(score)>17.wheregroupbyhaving在查詢語句中的次序例子:selectstudentid,courseid，avg(score)wherescore>60groupbystudentid,courseidHavingcount(score)>1select部門編號(hào),count(*)form員工信息表where工資>=2000groupby部門編號(hào)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的話，那么查詢結(jié)果會(huì)出現(xiàn)重復(fù)的部門名查詢一張表中的基數(shù)行和偶數(shù)行表名:tbl字段名：A主鍵字段:IDKEY(標(biāo)識(shí)列，標(biāo)識(shí)種子:1,標(biāo)識(shí)增長量:1)selectA,identity(int,1,1)asIDintotemtablefromtblselectsum(A)as奇數(shù)列匯總fromtemtablewhereID%2<>0selectsum(A)as奇數(shù)列匯總fromtemtablewhereID%2=0銀行卡恢復(fù)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.查看結(jié)果select*fromstu數(shù)據(jù)庫設(shè)計(jì)和高級(jí)查詢表的標(biāo)準(zhǔn)設(shè)計(jì)：必須滿足三大范式1.原子性〔不可再分〕2.非主鍵字段必須全部依賴于主鍵3.非主鍵不能傳遞依賴--如何檢測(cè)是否存在數(shù)據(jù)庫stuDB，如果存在，先刪除，再創(chuàng)立--SQLServer將數(shù)據(jù)庫的清單存放在master系統(tǒng)數(shù)據(jù)庫的sysdatabases表中usemaster--設(shè)置當(dāng)前數(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)立學(xué)員信息表stuInfocreatetablestuINfo(stunamevarchar(20)notnull,stuNochar(6)notnull,stuAgeintnotnull,stuIDnumeric(18,0),stuSeatsmallintidentity(1,1),stuAddresstext)go--創(chuàng)立學(xué)員成績表stuMarksusestuDBgoifexists(select*fromsysobjectswherename='stuInfo')droptablestuInfocreatetablestuMarks(ExamNochar(7)notnull,stuNoschar(6)notnull,writenExamintnotnull,LabExamintnotnull)go--刪除學(xué)員信息表stuInfodroptablestuInfo--添加主鍵約束〔將stuNo作為主鍵〕altertablestuInfoaddconstraintpk_stuNoprimarykey(stuNo)--添加唯一約束altertablestuInfoaddconstraintUQ_stuIDunique(stuID)--添加默認(rèn)約束altertablestuInfoaddconstraintDF_stuAddressdefault('地址不詳')forstuAddress--添加檢查約束altertablestuInfoaddconstraintCK_stuAgecheck(stuAgebetween15and40)--添加外鍵約束〔主表stuInfo和從表stuMarks建立關(guān)系，關(guān)聯(lián)字段為stuNo〕altertablestuMarksaddconstraintFK_stuNoforeignkey(stuNo)referencesstuInfo(stuNos)go--查看表的約束是否存在execsp_helpconstraint表名execsp_helpconstraintstuMarks--刪除約束altertable表名dropconstraint約束名--例如:altertablestuInfodropconstraintDF_stuAddress--創(chuàng)立登陸賬戶--添加windows登陸賬戶execsp_grantlogin'計(jì)算機(jī)名\計(jì)算機(jī)登錄名'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ù)庫用戶授權(quán)grant權(quán)限[on表名]to數(shù)據(jù)庫名--給用戶chenlinDBUser授予建表的權(quán)限usestuDBgrantcreatetabletochenlinDBUser--給用戶chenlinDBUser授予增刪查改的權(quán)限grantselect,insert,update,deleteonstuMarkstochenlinDBUser--刪除用戶對(duì)表的權(quán)限r(nóng)evoke權(quán)限名on表名to用戶名例如:revokeselectonstuMarkstochenlinDBUser--刪除數(shù)據(jù)庫用戶execsp_revokedbaccess用戶名局部變量的聲明declare@變量名數(shù)據(jù)類型例如:declare@namevarchar(8)局部變量的賦值set@name=值或者select@name=值(注意,select語句賦值要求該語句返回信息不能多于一條,否那么將把最后一條信息賦值給變量)實(shí)例1:查找李文才的左右同桌declare@namevarchar(8)set@name='李文才'select*fromstuInfowherestuName=@namedeclare@seatintselect@seat=stuSeatfromstuInfowherestuName=@nameselect*fromstuInfowhere(stuSeat=@seat+1)or(stuSeat=@seat-1)go全局變量@@error--最后一個(gè)T-SQL錯(cuò)誤的錯(cuò)誤號(hào)@@identity--最后一次插入的標(biāo)識(shí)列@@language--當(dāng)前使用的語言@@max_connections--可以新建的同時(shí)連接的最大數(shù)目@@rowcount--受上一個(gè)SQL語句影響的行數(shù)@@servername--本地效勞器的名稱@@servicename--該計(jì)算機(jī)上的SQL效勞器的名稱@@timeticks--當(dāng)前計(jì)算機(jī)上沒每刻度的微秒數(shù)@@trancount--當(dāng)前連接翻開的事物數(shù)@@version--SQLServer的版本信息輸出語句print局部變量or字符串select局部變量as自定義列名例如：print'效勞器的名稱:'+@@servernameprint'當(dāng)前錯(cuò)誤號(hào)'+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)計(jì)并顯示本班筆試平均分，70以上，顯示”成績優(yōu)秀“，并顯示前3名學(xué)員的考試信息如果70以下，顯示“成績較差”，并顯示后三名學(xué)員的考試信息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)計(jì)不及格的人數(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結(jié)果1when條件2then結(jié)果2else其他結(jié)果end例子:采用ABCDE五級(jí)打分制來顯示筆試成績1.A級(jí)>=902.B級(jí)80~893.C級(jí)70~794.D級(jí)60~695.E級(jí)<60select*fromstuMarksprint'ABCDE五級(jí)顯示成績?nèi)缦?'selectstuNo,成績=casewhenwritenExam<60then'E'whenwritenExambetween60and69then'D'whenwritenExambetween70and79then'C'whenwritenExambetween80and89then'B'else'A'endfromstuMarks批處理:go注意:SQLServer規(guī)定：建庫建表存儲(chǔ)過程視圖都必須在末尾添加"GO"簡單子查詢例子：１.查詢年齡比”李斯文“大的學(xué)員，要求顯示這些學(xué)員的信息方法1：declare@ageintselect@age=stuAgefromstuINfowherestuName='李思文'select*fromstuInfowherestuAge>@ageGO方法2：select*fromstuINfowherestuAge>(selectstuAgefromstuINfowherestuName='李思文')注意:將子查詢和比擬運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能多于一個(gè)例子：２.查詢筆試剛好通過60分的學(xué)員名單方法1：selectstuNamefromstuInfoinnerjoinstuMarksonstuInfo.stuNo=stuMarks.stuNowherewritenExam=92GO方法２：selectstuNamefromstuInfowherestuNo=(selectstuNofromstuMarkswherewritenExam=92)GOin和notin子查詢要查詢筆試成績剛好為98分的學(xué)員〔而98分的學(xué)員又不只一個(gè)〕selectstuNamefromstuInfowherestuNoin(selectstuNofromstuMarkswherewritenExam=98)GO查詢參加考試的學(xué)員名單selectstuNamefromstuInfowherestuNoin(selectstuNofromstuMarks)GO查詢沒參加考試的學(xué)員名單selectstuNamefromstuInfowherestuNonotin(selectstuNofromstuMarks)GOdeletefromstuMarkswhereLabExam=48andwritenExam<>88exists和notexists字查詢ifexists(子查詢)語句例子：如果有人筆試到達(dá)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綜合練習(xí)1.統(tǒng)計(jì)本次考試的缺考情況select應(yīng)到人數(shù)=(selectcount(*)fromstuInfo),實(shí)到人數(shù)=(selectcount(*)fromstuMarks),缺考人數(shù)=((selectcount(*)fromstuInfo)-(selectcount(*)fromstuMarks))2.提取學(xué)員的成績信息并保存結(jié)果，包括姓名學(xué)號(hào)筆試機(jī)試是否通過ifexists(select*fromsysobjectswherename='newTable')droptablenewTableselectstuName,stuInfo.stuNo,writenExam,LabExam,isPass=casewhenwritenExam>=60andlabExam>=60then1else0endintonewTablefromstuInfoleftjoinstuMarksonstuInfo.stuNo=stuMarks.stuNo3.比擬筆試平均分和機(jī)試平均分，較低分的進(jìn)行循環(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)計(jì)學(xué)員的成績和通過情況select姓名=stuName,學(xué)號(hào)=stuNo,筆試成績=casewhenwritenExamisnullthen'缺考'elseconvert(varchar(5),writenExam)end,機(jī)試成績=casewhenlabExamisnullthen'缺考'elseconvert(varchar(5),labExam)end,是否通過=casewhenisPass=1then'是'else'否'endfromnewTable5.提分后統(tǒng)計(jì)學(xué)員的通過率情況select總?cè)藬?shù)=count(*),通過人數(shù)=sum(isPass),通過率=(convert(varchar(5),avg(isPass*100))+'%')fromnewTable事務(wù)事務(wù)四大特性:1.原子性:事務(wù)中有一處失敗，整個(gè)事務(wù)失敗。2.一致性:事務(wù)處理前后都必須一致。3.隔離性:事務(wù)之間必須相互獨(dú)立，不能有依賴。4.持久性:事務(wù)處理后，他對(duì)系統(tǒng)的影響是永久的。T-SQL事務(wù)語句開始事務(wù):begintransaction提交事務(wù):committransaction回滾〔撤銷〕事務(wù):rollbacktransaction實(shí)例1/*----------------創(chuàng)立表--------------------*/usestuDBGOifexists(select*fromsysobjectswherename='bank')droptablebankGOcreatetablebank(customerNamechar(10),--顧客姓名currentMoneyMoney--當(dāng)前余額)Goaltertablebank--添加約束，賬戶余額必須>=1AddconstraintCK_currentMoneycheck(currentMoney>=1)GOinsertintobank(customerName,currentMoney)values('張三',1000)insertintobank(customerName,currentMoney)values('李四',1)select*frombankGo實(shí)例2將張三的錢1000塊轉(zhuǎn)到李四賬戶updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='張三'updatebanksetcurrentMoney=currentMoney+1000wherecustomerName='李四'GOselect*frombankGO實(shí)例3usestuDBGO--恢復(fù)原來的數(shù)據(jù)updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='李四'setnocounton--不顯示受影響的行數(shù)信息print'查看轉(zhuǎn)賬事務(wù)錢的余額'select*frombankGO/*--開始事務(wù)〔指定事務(wù)從此處開始，后續(xù)的T-SQL語句都是一個(gè)整體〕--*/begintransaction/*--定義變量，用于累計(jì)事務(wù)執(zhí)行過程中的錯(cuò)誤--*/declare@erroeSumintset@erroeSum=0--初始化為0，即無錯(cuò)誤/*--轉(zhuǎn)賬:張三的賬戶少1000塊，李四的賬戶多1000塊--*/updatebanksetcurrentMoney=currentMoney-800wherecustomerName='張三'set@erroeSum=@erroeSum+@@error--累計(jì)是否有錯(cuò)誤updatebanksetcurrentMoney=currentMoney+800wherecustomerName='李四'set@erroeSum=@erroeSum+@@error--累計(jì)是否有錯(cuò)誤print'查看轉(zhuǎn)賬事務(wù)過程中的余額'select*frombank/*--根據(jù)是否有錯(cuò)誤，確定事務(wù)提交還是撤銷--*/if@erroeSum<>0--如果有錯(cuò)誤beginprint'交易失敗,回滾事務(wù)'rollbacktransactionendelsebeginprint'交易成功,提交事務(wù)，寫入硬盤，永久保存'committransactionendGOprint'查看轉(zhuǎn)賬事務(wù)后的余額'select*frombankGO索引索引分類：1.唯一索引:唯一索引要求兩行不能有相同的索引值。2.逐漸索引:給表定義一個(gè)主鍵將自動(dòng)創(chuàng)立主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引中的每個(gè)值都必須唯一。2.聚集索引:聚集索引中，表中各行的物理順序與鍵值的邏輯〔索引〕順序相同，每張表里面只能有一個(gè)聚集索引?！踩绻欠蔷奂饕?，表中各行的物理順序與鍵值順序不匹配〕注意:一個(gè)表中只能創(chuàng)立一個(gè)聚集索引，但可以有多個(gè)非聚集索引，設(shè)置某列為主鍵，該列就默認(rèn)為聚集索引。創(chuàng)立索引語法:create[unique][clustered|nonclustered]indexindex_nameontable_name(column_name[,column_name2]...)[withfillfactor=x]其中:unique指定唯一索引，可選。clustered、nonclustered指定是聚集索引還是非聚集索引，可選。fillfactor表示填充因子，指定一個(gè)0~~100的值，該值指示索引頁填滿的空間所占的百分比。實(shí)例:usestuDBGO/*--檢測(cè)是否存在索引(索引存放在系統(tǒng)表sysindexes中)--*/ifexists(selectnamefromsysindexeswherename='ix_stuMarks_writtenExam')dropindexstuMarks.ix_stuMarks_writtenExam--刪除索引/*--筆試列創(chuàng)立非聚集索引，填充因子為30%--*/createnonclusteredindexix_stuMarks_writtenExamonstuMarks(writtenExam)withfillfactor=30GO實(shí)例:/*--指定按索引:ix_stuMarks_writtenExam查詢--*/select*fromstuMarks(index=ix_stuMarks_writtenExam)or(index(ix_stuMarks_writtenExam))wherewrittenExambetween60and90視圖創(chuàng)立視圖語句:createviewview_nameas<select語句>實(shí)例：usestuDBGO/*--檢測(cè)是否存在:試圖存放在系統(tǒng)表sysobjects中--*/ifexists(select*fromsysobjectswherename='view_stuInfo_stuMarks')dropviewview_stuInfo_stuMarksGO/*--創(chuàng)立視圖:查看學(xué)員的成績情況--*/createviewview_stuInfo_stuMarksasselect姓名=stuName,學(xué)號(hào)=stuInfo.stuNo,筆試成績=writtenExam,機(jī)試成績=labExam,平均分=(weittenExam+labExam)/2fromstuInfoleftjoinstuMarksonstuInfo.stuNo=stuMarks.stuNoGO/*--使用視圖:視圖是一個(gè)虛擬表，可以像物理表一樣翻開--*/select*fromview_stuInfo_stuMarks存儲(chǔ)過程〔存放在sysobjects中〕存儲(chǔ)過程的幾大優(yōu)點(diǎn):1.允許模塊化程序設(shè)計(jì)〔只需創(chuàng)見一次存儲(chǔ)過程并將其存儲(chǔ)在數(shù)據(jù)庫中，屢次調(diào)用〕2.執(zhí)行速度比T-SQL批處理更快3.減少網(wǎng)絡(luò)流量4.可作為平安機(jī)制使用存儲(chǔ)過程分類：1.系統(tǒng)存儲(chǔ)過程2.自定義存儲(chǔ)過程常見的系統(tǒng)存儲(chǔ)過程sp_databases列出效勞器上的所有數(shù)據(jù)庫sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns返回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過程sp_password添加或修改登錄賬戶的密碼sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程、觸發(fā)器或視圖的實(shí)際文本實(shí)例--Purpose:常用系統(tǒng)存儲(chǔ)過程的使用execsp_databases--列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫execsp_renamedb'old_DB','new_DB'--更改數(shù)據(jù)庫的名稱usestuDBGOexecsp_tables--當(dāng)前數(shù)據(jù)庫可查詢的對(duì)象的列表execsp_columns表名--查看某個(gè)表列的信息execsp_help表名--查看某個(gè)表所有信息execsp_helpconstraint表名--查看某個(gè)表的約束execsp_helpindex表名--查看某個(gè)表的索引execsp_helptext'視圖名'--查看視圖的語句文本execsp_stored_procedures--返回當(dāng)前數(shù)據(jù)庫中的所有存儲(chǔ)過程列表新建文件夾、列出文件列表〔必須啟動(dòng)SQL的外圍配置器〕execxp_cmdshellDOS命令[NO_output]實(shí)例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\'--查看文件自定義存儲(chǔ)過程創(chuàng)立不帶參數(shù)存儲(chǔ)過程語句:createproc[edure]存儲(chǔ)過程名[{@參數(shù)1數(shù)據(jù)類型}[=默認(rèn)值][output],......{@參數(shù)1數(shù)據(jù)類型}[=默認(rèn)值][output]]asSQL語句實(shí)例usestuDBGO/*--檢測(cè)是否存在，存儲(chǔ)過程存放在sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dropprocedureproc_stuGO/*--創(chuàng)立存儲(chǔ)過程--*/createprocedureproc_stuasdeclare@writtenAvgfloat,@labAvgfloat--筆試平均分和機(jī)試平均分變量select@writtenAvg=avg(writtenExam),@labAvg=avg(labExam)fromstuMarksprint'筆試平均分:'+convert(varchar(5),@writtenAvg)print'機(jī)試平均分:'+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í)行存儲(chǔ)過程--*/execproc_stu--調(diào)用存儲(chǔ)過程的語法:exec過程名[參數(shù)]創(chuàng)立帶參數(shù)存儲(chǔ)過程語句:createproc[edure]存儲(chǔ)過程名{@參數(shù)1數(shù)據(jù)類型}[=默認(rèn)值][output],......{@參數(shù)1數(shù)據(jù)類型}[=默認(rèn)值][output]asSQL語句實(shí)例usestuDBGO/*--檢測(cè)是否存在，存儲(chǔ)過程存放在sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dropprocedureproc_stuGO/*--創(chuàng)立存儲(chǔ)過程--*/createprocedureproc_stu@writtenPassint,--輸入?yún)?shù):筆試及格線@labPassint--輸入?yún)?shù):機(jī)試及格線asprint'--------------------------------------------------------------------------'print'參加本次考試沒有通過的學(xué)員'selectstuName,stuInfo.stuNo,writtenExam,labExamfromstuInfoinnerjoinstuMarksonstuInfo.stuNo=stuMarks.stuNowherewrittenExam<@writtenPassorlabExam<@labPassGO/*--調(diào)用本次考試機(jī)試偏難，機(jī)試的及格線定為55分，筆試及格線定為60分--*/execproc_stu60,55--或這樣調(diào)用:execproc_stu@labPass=55,@writtenPass=60/*---調(diào)用存儲(chǔ)過程---execproc_stu---都采用默認(rèn)值:筆試和機(jī)試及格線都為60分execproc_stu64---機(jī)試采用默認(rèn)值，筆試及格線64，機(jī)試60分execproc_stu60,65---都不采用默認(rèn)值，筆試及格線60，機(jī)試及格線65--錯(cuò)誤的調(diào)用方法:execproc_stu,55---希望筆試采用默認(rèn)值，機(jī)試及格線為55--正確的調(diào)用方式:execproc_stu@labPass=55---筆試采用默認(rèn)值，機(jī)試及格線為55---*/實(shí)例usestuDBGO/*--檢查是否存在:存儲(chǔ)過程存放在系統(tǒng)表sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dro