數(shù)據(jù)庫(kù)原理t-sql教程字符串函

插入更新刪除數(shù)據(jù)查詢(xún)模糊分組數(shù)日期數(shù)學(xué)系統(tǒng)聚合創(chuàng)建刪除添加約刪除約用戶局部變?nèi)肿冚敵稣Z(yǔ)IF-條件語(yǔ)WHILE環(huán)語(yǔ)ININ查T(mén)-SQL過(guò)發(fā)發(fā)發(fā)INSERT[INTO表名VALUES值insertintostudents(sname,saddress,sgrade,semall,s insertintostudents(sname,saddress,sgrade,s,s insertintotongxuelu('','地址','電子郵件') selectsname,saddress,sfromstudentsSELECTINTOselectintotongxuelu fromstudentsSELECTIDENTITY(數(shù)據(jù)類(lèi)型,標(biāo)識(shí),標(biāo)識(shí)增長(zhǎng)量)AS列INTOFROMstudents.sname,students.saddress,students.s,indetity(int,1,1)asfromstudents通過(guò)UNION關(guān)鍵字合并數(shù)據(jù)行插入insertstudents(sname,sgrade,s select'測(cè)試1',7,0,nuionselect'測(cè)試2',7,0,nuionselect'測(cè)試3',7,0,nuionselect'測(cè)試4',7,0,nuionselect'測(cè)試1',7,0,nuionselect'測(cè)試2',7,1,nuionselect'測(cè)試3',7,1,nuionselect'測(cè)試4',7,1,nuionselect'測(cè)試5',7,1語(yǔ)法格式:UPDATE<表名>SET<列名=更新值>[WHERE<更新條件>]updatestudentssets updatesetsaddress='女子學(xué)校家政班'wheresaddress=' 學(xué)校班'updatewherescores<=95語(yǔ)法格式:DELETEFROM[WHEREdeletefromstudentswheresname='張青'truncatetable語(yǔ)法格式:SELECT<列名>FROM<表名>[WHERE<查詢(xún)條件表達(dá)式>][ORDER<排序的列名>[ASC或 --默認(rèn)ASC升select*fromstudents查詢(xún)部分行 條件查fromstudentswheresaddress='新鄉(xiāng)formstudentswheresaddress<>'新鄉(xiāng)selectscodeas學(xué)員編號(hào),snameas學(xué)員,saddressas學(xué)員地fromwheresaddress<>'新鄉(xiāng)selectfirstname+'.'+lastnameas''fromemployeesselect from查詢(xún)空行ISNULLISNOTNULLselectsnamefromstudentswheresis在查詢(xún)中使用常量列將常量的科缺省信息添加到查詢(xún)輸出中select=sname,地址=saddress,'河北新龍'as學(xué)校名稱(chēng)fromstudents查詢(xún)返回限制的行數(shù)TOPselecttop5sname,saddressfromstudentswheres selecttop20percentsname,saddress fromstudentswheres selectstudentidas,(score*0.9+5asfromorderbyscoreselectau_lname+'.'+au_fnameasfromauthorsunionselectfname+'.'+lnameasempformemployeeorderbyempdescselectstudentidas,scoreasfromscorewherescore>60orderbycharindexselect ','my course',1)返回:4找不到返回lenselectlen('sqlserverlowerselectlower('SQLServer課程')返回:sqlserverupperselectupper('sqlserver課程')返回:SQLSERVERltrimselectltrim('')返回:(后面的空格保留rtrimselectltrim('')返回:(前面的空格保留right/leftselectright/left('提.吐?tīng)査?,3)返回:吐?tīng)査?rece替換一個(gè)字符串中的字selectrece('可切.楊可','可','蘭')返回:蘭切.stuff在一個(gè)字符串中,刪除指定長(zhǎng)度的字符,并在該位置插入一個(gè)新的字符串selectstuff('ABCDERG',2,3,'我的音樂(lè)我的世界')返回:A我的音樂(lè)我的世界EFGgetdateselectgetdate()selectdateadd(mm,4,'01/01/99')返回:以當(dāng)前的日期格式返回datediffselectdatediff(mm,'01/01/99','05/01/99')datenameselectdatename(dw,'01/01/2000')datepartselectdatepart(day,'01/15/2000')selectabs(-43)返回selectceiling(43.5)返回selectfloor(43.5)返回powerselectpower(5,2)返回selectround(43.543,1)返回selectsign(-43)返回:-1selectsqrt(9)返回convertselectconvert(varchar(5),121345)current_userselectcurrent_userdatalengthselectdatalength('中國(guó) host_namesystem_userselectsystem_userselectuser_name(1)返回:從任意數(shù)據(jù)庫(kù)中返回select*fromstudentswheresnamelike'張%'select*fromcardwhereidlike'00[^8]%[A,C]%'select*fromscorewherescorebetween60andselect*fromsaleswhereord_datenotbetween'1992-8-1'and'1993-8-selectsnameas學(xué)員fromstudentswheresaddressin/notin('','廣州','')orderbysaddressselectsum(ytd_sales)fromtitleswhereAVGselectavg(score)as平均成績(jī)fromscorewhereselectavg(score)as平均成績(jī),max(score)as,min(score)as分fromscorewherescore>=60selectcount(*)asfromscorewhereselectcourseid,avg(score)asfromgroupbyselectstudentidas,courseidas,avg(score)asfromgroupbyselectstudentidas,courseidas,avg(score)asfromgroupbystudentid,courseidhavingcount(score)>1from員工信息表where工資>=2000havingfromstudents,scoreb.在FROM子句中使用JOIN...ONselectfromstudentsassinnerjoinscoreascselectproductid,suppli rsinnerjoinproduces whereunitprice>$10and namelike'F%'selects.snameas學(xué)員,cs.coursenameas課程名稱(chēng),c.scoreas考試成fromstudentsasinnerjoinscoresacon(s.scode=c.studentid)innerjoincourseascson(cs.courseid=c.coureid)formstudentsassleftouterjoinscoreascons.scode=c.studentidselectfromrightouterjoinpublishersonCREATEDATABASE據(jù)庫(kù)ON[PRIMARY]))

{<日志文件數(shù)[,FILEGROUTH=增長(zhǎng)量])[,...n]FILEGROUP[,...n]數(shù)據(jù)庫(kù)名:最長(zhǎng)128 字符LOGON:指明日志文件的明確定義 NAME的邏輯名稱(chēng)一一對(duì)應(yīng)createdatabasestuDBonprimary--默認(rèn)就屬于primary(name='stuDB_data',--主數(shù)據(jù)文件的邏輯名))

size=5mb,--主數(shù)據(jù)文件的初始大小maxsize=100mb,--主數(shù)據(jù)文件增長(zhǎng)的最大值filegrowth=15%--主數(shù)據(jù)文件的增長(zhǎng)率go--和后續(xù)的SQLcreatedatabaseemployees))(

/*---日志文件1的具體描述---*/)DROPDATABASE據(jù)usemaster--設(shè)置當(dāng)前數(shù)據(jù)庫(kù)為master,以便systatabasesifexists(select*fromsysdatabaseswherename='stuDB')dropdatabasestuDBcreatedatabasestuDB))

SQLServer- )到 231-120 每個(gè)字符,能4000種字符變長(zhǎng)統(tǒng)一編碼字符型數(shù)據(jù),4000符節(jié) 年月日到 到1分鐘內(nèi)的唯一數(shù)碼.一個(gè)表中只能有一個(gè)timestamp第次插入或修改一行時(shí),timestamp列的值都會(huì)改列不是人CREATETABLE(1的特征2據(jù)類(lèi))createtablestuinfostunamevarchar(20)notnull,stunochar(6)notnull,stuageintnotnull,stuidstuseatsmallintidentity(1,1),stuaddresstext)createtablestumarksexamnochar(7)notnull,stunochar(6)notnull,writtenexamintnotnull,labexamintnotnull)DROPTABLEifexists(select*fromsysobjectswherename='stuinfo')droptablestuinfocreatetablestuinfo)主鍵約束:(primarykey唯一約束:(unique檢查約束:(check默認(rèn)約束:(default外鍵約束:(foreignkeyALTERTABLEADDCONSTRAINT束束類(lèi)型具體的約束說(shuō)altertablestuinfoaddconstraintpk_stunoprimarykeyaltertablestuinfoaddconstraintuo_stuidaltertablestuinfoaddconstraintdf_stuaddressdefault('地址不詳')foraltertableaddconstraintck_stuagecheck(stuagebetween15andaltertablestumarksaddressconstraintforeignkey(stuno)referencesALTERTABLEDROPCONSTRAINT束altertabledropconstraintEXECsp_grantlogin'windows\域帳戶EXECsp_ADDlogin'帳戶名','EXECsp_droplogin戶名execsp_grantlogin ----windowsjbtraining\s26301,jbtraining1234'EXECsp_revokedbaccess數(shù)據(jù)庫(kù)用戶usestudbexecsp_grantdbaccessexecsp_grantdbaccess向數(shù)據(jù)庫(kù)用戶創(chuàng)建(createtable)GRANTON名TO庫(kù)用示例8：grantselect,insert,updateonstuinfotozhangsandbusergrantcreatetabletos26301dbuserDECLARE@variable_name儲(chǔ)8個(gè)字符declare@seatint--一個(gè)存放學(xué)員座位號(hào)的變量SET@variable_name=valueSELECT1男12才男33女24男4declare@namevarchar(8)set@name='才'--使用SET賦select*fromstuinfowheredeclate@seatint--座位號(hào)select@seat=stuseatfromstuinfowherestuname=@nameselectselect*fromstuinfowhere(stuseat=@seta+1)or(stuseat=@seat-1)1才男31女22男4常用的全局變量如表3.1所示3.1print局部變量或字符SELECT局部變量AS示例2:print@@servernameselect@@servernameas'服務(wù)器名稱(chēng)'用print方法輸出結(jié)果將在消息窗口以文本方式顯示,用select方法結(jié)果將在網(wǎng)格窗口以表格方式顯示.由于使用print語(yǔ)句要求單個(gè)局部變量或字符串表達(dá)式作為參數(shù),所以如果這樣編寫(xiě)SQL語(yǔ)print因?yàn)槿肿兞緻@error返回的是整形數(shù)值,應(yīng)把數(shù)值轉(zhuǎn)換為字符串,如下:print'當(dāng)前錯(cuò)誤號(hào)'+convert(varchar(5),@@error)insertintostuinfo(stuname,stuno,stu ,stuage)values('梅超風(fēng)printconvert(varchar(5),@@error)0,表示上一printIF語(yǔ)句或語(yǔ)句語(yǔ)句或語(yǔ)句

declare@myavgselect@myavg=avg(writtenexam)fromstumarks

printselecttop3*fromstumarksorderbywrittenexamprintselecttop3*fromstumarksorderbyWHILE(條件insertintosutmark(examno,stuno,writtenexam,labexam)select*fromstumarksdeclare@nintwhile(1=1)--條件成select@n=count(*)fromstumarkswherewrittenexam<updatestumarksset

breakselect*fromstumarks

WHEN1THENWHEN2THEN[ELSEselect*fromstumarksprint'ABCDE五級(jí)顯示成績(jī)?nèi)缦?'selectstuno,成績(jī)=case69then79then89then

whenwrittenexam<60then'E'whenwrittenexambetween60whenwrittenexambetween70andwhenwrittenexambetween80andelse'A'from

SQLServer規(guī)定:如果是建庫(kù)、建表語(yǔ)句、以及過(guò)程和視圖等，必須在語(yǔ)句末尾添加示例1：declare@ageint--定義變量，用于存放李斯文的select@age=stuagefromstuinfowherestuname=select*fromstuinfowherestuage>@age--篩選比李斯文大的學(xué)select*fromwherestuage(selectstuagefromstuinfowherestuname='示例3：selectstunamefromstuinfoinnerjoinstumarks--innerjoinonstuinfo.stuno=stumarks.stunowhereselectstunamefromwherestuno=(selectstunofromstumarkswhere一般來(lái)說(shuō)，表連接都可以用子查詢(xún)替換，但反過(guò)來(lái)說(shuō)

INNOTIN5：INselectstunamefromwherestunoin(selectstunofromstumarkswhereselectstunamefromstuinfowherestunoin(selectstunofromselectstunamefromstuinfowherestunonotin(selectstunofromEXISTSNOTRXISTSIFEXISTS語(yǔ)ifexists(select*fromstumarkswherewrittenexam>80)

updatestumarkssetwrittenexam=writtenexam+2select*fromstumarks

updatestumarkssetwrittenexam=writtenexam+5select*fromstumarks

updatestumarksselect*from

updatestumarkssetselect*from1男12才男33女24男45女5123415411是2才是3是4否5否153/*--本次考試的原始數(shù)據(jù)----select*fromstuinfo---select*fromstumarks-/*--統(tǒng)計(jì)考試缺考情況--select=(selectcount(*)fromstuinfo),--應(yīng)到人數(shù)為子查詢(xún)表達(dá)實(shí)到人數(shù)=(selectcount(*)from缺考人數(shù)=((selectcount(*)fromstuinfo)-(selectcount(*)fromstumarks))ifexists(select*fromsysobjectswherename='newtable')droptablenewtablewhenwrittenexam>=60andlabexam>=60then1intonewtablefromstuinfoleftjoinstumarkson--select*fromnewtableselect@avgwritten=avg(writtenexam)fromnewtablewherewrittenexamisnotnullselect@avglab=avg(labexam)fromnewtablewherelabexamisnotnullif@avgwritten<@avglab--比較筆試和機(jī)試平均,看哪科偏低updatenewtablesetwrittenexam=writtenexam+1if(selectmax(writtenexam)fromnewtable)

updatenewtablesetlabexam=labexam+1if(selectmax(labexam)fromnewtable)>=97updatenewtablesetwhenwrittenexam>=60andlabexam>=60then1else0/*--顯示考試最終通過(guò)情況--select=stuname,學(xué)號(hào)=stuno,筆試成績(jī)whenwrittenexamisnullthenelseconvert(varchar(5),writtenexam),機(jī)試成績(jī)whenlabexamisnullthenelseconvert(varchar(5),labexam),是否通過(guò)whenispass=1thenelse'否'/*--顯示通過(guò)率及通過(guò)人數(shù)--select=count(*),通過(guò)人數(shù)=sum(ispass),=(convert(varchar(5),avg(ispass*100))+'%')fromnewtable開(kāi)始事務(wù)：BEGIN提交事務(wù)：COMMIT回滾（撤銷(xiāo)）事務(wù)：ROLLBACK顯式事務(wù)：用BEGINTRANSACTION 隱性事務(wù)：通過(guò)設(shè)置SETIMPLICIT_TRANSACTIONSON語(yǔ)句，將隱性事務(wù)模式設(shè)置為打開(kāi)。當(dāng)以隱性事務(wù)操作時(shí)，SQLServer將在提交或回滾事務(wù)后自動(dòng)啟動(dòng)新事務(wù)。無(wú)法描述事務(wù)的開(kāi)始，只需提交或回滾每個(gè)事務(wù)。 自動(dòng)提交事務(wù)：這是SQLServer的默認(rèn)模式，它將每條單獨(dú)的T-SQL語(yǔ)句視為一如果成功執(zhí)行，則自動(dòng)提交。如果錯(cuò)誤，則自動(dòng)回滾示例3：--updatebanksetcurrentmoney=currentmoney-1000wheresetnocounton--不顯示受影響的行數(shù)信息print'查看轉(zhuǎn)帳事務(wù)前的余額'select*frombankbegintransactiondeclare@errorsumintset@errorsum=00，updatebanksetcurrentmoney=currentmoney-1000wherecustomername=set@errorsum=@errorsum+@@errorupdatebanksetcurrentmoney=currentmoneywherecustomername='李四'set@errorsum=@errorsum+@@errorselect*frombankif@errorsum<>0--如果有錯(cuò)誤

rollbacktransactioncommittransactionselect*frombank

CREATE[UNIOUE][CLUSTERED|NONCLUSTERED]INDEXONtable_name(column_name[,column_name]...)

UNIQUE指定唯一索引,可選 指定是索引還是非索引,可選 FILLFACTOR表示填充因子,指定一個(gè)0～100的值,該值指示索引頁(yè)填滿的空間usestudbifexists(selectnamefromsysindexexwheredropindex /*--筆試列創(chuàng)建非索引:填充因子為30%--*/createnonclusteredindexix_stumarks_writtenexamonstumarks(writtenexam)with定SQLServer數(shù)據(jù)查詢(xún)的select*fromstumarkswherewrittenexambetween60and定.SQLServer將會(huì)根據(jù)所創(chuàng)建 CREATEVIEWview_nameselectifexists(select*fromsysobjectswherename='view_stuinfo_stumarks')dropviewview_stuinfo_stumarkscreateviewview_stuinfo_stumarksselect=stuname,學(xué)號(hào)=stuinfo.stuno,筆試成fromstuinfoleftjoinstumarksonselect*fromview_stuinfo_stumarks論上它可以像變通的物理表一樣使用,例如增,刪,改,查等,修改視圖實(shí)際上是修改原始數(shù)據(jù)表.因?yàn)樾薷囊晥D有許多限制,所以實(shí)際開(kāi)發(fā)中一般視圖僅做查詢(xún)使用. --purpose:常用系統(tǒng)過(guò)程的使EXECsp_databasesEXECsp_renamedb'northwind','northwind1'--改變數(shù)據(jù)庫(kù)名稱(chēng)(單用戶訪EXECsp_tables--當(dāng)前數(shù)據(jù)庫(kù)中可查詢(xún)對(duì)象的列表EXECsp_columnsstuinfo--查看stuinfoEXECsp_helpstuinfo--查看stuinfo的信息EXECsp_helpconstraintstuinfostuinfoEXECsp_helpindexstumarksstumarksEXECsp_helptext'view_stuinfo_stumarks'--查看視圖的語(yǔ)句文本EXECsp_stored_procedures--返回當(dāng)前數(shù)據(jù)庫(kù)中的過(guò)程列表EXECxp_cmdsDOS命令usemasterexecxp_cmds'mkdird:bank',no_output--創(chuàng)建文件夾--創(chuàng)建庫(kù)bankdbifexists(...)createdatabasebankdb())execxp_cmds'dird:\bank\'--查看文創(chuàng)建不帶參數(shù)的過(guò)程CREATE 過(guò)程 {@參數(shù)n示例3:

SQL語(yǔ)usestudb/*--檢測(cè)是否存在:過(guò)程存放在系統(tǒng)表sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dropprocedurecreateprocedureproc_studeclare@writtenavgfloat,@labavgfloat@writtenavg=avg(writtenexam),@labavg=avg(labexam)fromstumarksprint'機(jī)試平均分:'+convert(varchar(5),@labavg)if(@writtenavg>70and@labavg>70)print'

printprint'print selectstuname,stuinfo.stuno,writtenexam,labexamfromstuinfoinnerjoinstumarksonstuinfo.stuno=stumarks.stunowherewrittenexam<60orlabexam<60execproc_stu創(chuàng)建帶輸入?yún)?shù)的過(guò)程CREATEPROC[EDURE]過(guò)程@1 @2ifcreateprocedure@labpassintprint print' selectstuname,stuinfo.stuno,writtenexam,labexamfromstuinfoinnerjoinstumarksexecproc_stu

wherewrittenexam<@writtenpassorlabexam

@labpassint=60execproc_stu--都采用默認(rèn)值:筆試和機(jī)試及格線都為60分execproc_stu646460execproc_stu60,55--都不采用默認(rèn)值execproc_stu,5555execproc_stu@labpass=55--希望筆試采用默認(rèn),機(jī)試為55創(chuàng)建帶輸出參數(shù)的過(guò)程ifexists(select*fromsysobjectswherename='proc_stu')dropprocedureproc_stucreateprocedure@notpasssumintoutput,--output@writtenpassint=60,--默認(rèn)參數(shù)放后@labpassint=60--默認(rèn)參數(shù)放后print

print selectstuname,stuinfo.stuno,writtenexam,labexamstuinfoinnerjoinsumarkswherewrittenexam<@writtenpasslabexam<declare@sumexecproc_stu@sumoutput,64OUTPUTprint if@sum>=RAISERROR語(yǔ)句的語(yǔ)法如下:系 sysadmin定角色的成員預(yù)留的,并且需要指WITHLOG選項(xiàng).ifexists(select*fromsysobjectswherename='proc_stu')dropprocedureproc_stucreateprocedure@notpasssumintoutput,--output@writtenpassint=60,@labpassint=60--默認(rèn)參數(shù)放后 if(not@writtenpassbetween0and100)or(not@labpassbetween0and100)

過(guò)print

print selectstuname,stuinfo.stuno,writtenexam,labexamstuinfoinnerjoinsumarkswherewrittenexam<@writtenpasslabexam<@labpass/*----調(diào)用過(guò) declare@sumexecproc_stu@sumoutput,604--調(diào)用時(shí)也帶OUTPUT入604分set@t=@@errorraiserror@error將不等于0,表示有錯(cuò)if@t<>0returnprint if@sum>=SQLServer就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語(yǔ)句所定義的規(guī)則。由此觸發(fā)器可分為以下幾種。每個(gè)觸發(fā)器有兩特殊的表：插入表（inserted表）和刪除表（deleted表）。這兩入內(nèi)容，但可以表中的數(shù)據(jù)。例如可用語(yǔ)句查看deleted表的中的信息：select*fromdeleted 了被刪除或被更新前的記錄行。在執(zhí)行DELETE或UPDATE語(yǔ)句時(shí)，行從觸發(fā)器表中刪除， 存了入或被更新后的記錄行。在執(zhí)行INSERT或UPDATE語(yǔ)句時(shí)，新加行被同時(shí)添加到表中，然后新行被到觸發(fā)器表和inserted表中它們?cè)诰唧w的增加、刪除、更新操作時(shí)的情況如表7.1所示。inserteddeleted刪除（DELETE）修改（UPDATE）PRIMARYKEY過(guò)FOREIGNKEY約束進(jìn)行強(qiáng)制。 如某個(gè)表上的觸發(fā)器中包含對(duì)另外一個(gè)表的數(shù)據(jù)庫(kù)操作，如刪除、更新、插入 值與主鍵不匹配時(shí)，此類(lèi)觸發(fā)器就可能發(fā)生作用。例如，可以在stumarks表上創(chuàng)建FOREIGNKEY達(dá)到這個(gè)目 如果不滿足約束，則不執(zhí)行INSERT、UPDATE及DELETE觸發(fā)器。CREATETRIGGERTrigger_nameONtable_name[WITHASSQL語(yǔ)句 加密 ments表中包含CREATETRIGGER語(yǔ)句文本的條目.使用WITHENCRYPTION可防止將觸發(fā)器作為SQLServer use/*--------檢測(cè)是否存在,觸發(fā)器存放在系統(tǒng)表sysobjects ifexists(selectnamefromsysobjectswherename='trig_transinfo')droptriggertrig_transinfocreatetriggertrig_transinfoontransinfofor金額等---

/*--