目錄1 基本sql語句 11.1 操作表(數(shù)據(jù)庫定義) 11.2 操作數(shù)據(jù)（數(shù)據(jù)庫操作） 21.3 數(shù)據(jù)庫控制語言 22 Sqlserver2008數(shù)據(jù)庫命名規(guī)則 23 Sql分支結(jié)構(gòu)語法 33.1 If語句 33.2 多條件選擇 33.3 循環(huán)語句 34 條件查詢 44.1 常用關(guān)鍵字 44.2 聯(lián)合查詢 44.3 子查詢 54.4 集合查詢 55 Sql與oracle常用函數(shù)對比 55.1 數(shù)學(xué)函數(shù) 55.2 集合函數(shù) 65.3 字符函數(shù) 75.4 日期函數(shù) 75.5 常用函數(shù) 76 SQLSERVER的操作 87 存儲(chǔ)過程 87.1 分類 87.2 格式 87.3 常用實(shí)例 97.4 擴(kuò)展存儲(chǔ)過程 127.5 臨時(shí)表 127.6 編譯解析 137.7 其他 137.8 觸發(fā)器 14基本sql語句操作表(數(shù)據(jù)庫定義)使用數(shù)據(jù)庫Usedb_name;建表createtablestudent(snointnotnullconstraintPK_snoprimarykeyclusteredcheck(snolike‘s1300[0-9][0-9]’),snamevarchar(8)null,genderchar(2)null)主鍵自動(dòng)增長：idintidentity(1,1)primarykeynotnull修改表altertablestudentaddclass_nochar(6)altertablestudentdropcolumngender刪除表中的數(shù)據(jù)Truncatetable刪除表droptablestudent操作數(shù)據(jù)（數(shù)據(jù)庫操作）插入insertintostudent(sno,sname,gender)values(001,’chenjie’,’女’);刪除deletefromstudentwheresno=’001’deletefromstudent;%成為空表修改updatestudentsetsname=’chenjie1’wheresno=001;查詢select*fromstudentselectintosqlserver選擇一張表中的數(shù)據(jù)插入到另一張表中相關(guān)列方法一：要求newTable事先存在InsertintonewTableselect6,col1fromoldTable方法二：要求newTable事先不存在Selectcol1intonewTablefromoldTableselectintofrom數(shù)據(jù)庫控制語言grantcreatetabletochenjiegrantall/updateonstudenttopublicwithadminoptionrevokecreatetalefromchenjieDENY語句用于拒絕給當(dāng)前數(shù)據(jù)庫內(nèi)的用戶或者角色授予權(quán)限:revokeall/updateonstudenttochenjie建立索引CreateuniqueindexSnoonstudent(sno)數(shù)據(jù)庫設(shè)置外鍵--為表格mybbs中的列authorid添加外鍵約束author中的id字段。Altertabledbo.mybbsaddconstraintfk_mybbs_authorForeignkey(authorid)Referencesdbo.author([id])onupdatecascadeondeletecascade--刪除外鍵約束fk_mybbs_authorAltertabledbo.mybbsdropconstraintfk_mybbs_authorSql語句的應(yīng)用例3-7求出各位學(xué)生的平均成績，把結(jié)果存放在新表AVGSCORE中。程序清單如下：/*首先建立新表AVGSCORE，用來存放學(xué)號和學(xué)生的平均成績。*/CREATETABLEAVGSCORE(SNOCHAR(10),AVGSCORESMALLINT)Go/*利用子查詢求出SC表中各位學(xué)生的平均成績，把結(jié)果存放在新表AVGSCORE中。*/INSERTINTOAVGSCORESELECTSNO,AVG(SCORE)FROMSCGROUPBYSNO例3-12創(chuàng)建把講授C5課程的教師的工資增加100元。程序清單如下：/*T表（教師基本情況表）的結(jié)構(gòu)為T(TNO,TN,SEX,AGE,PROF,SAL,DEPT)分別表示教師的編號，姓名，性別，年齡，職稱，工資，系別。TC表（教師授課表）的結(jié)構(gòu)為TC(TNO,CNO)分別表示教師的編號，課程編號。*/UPDATETSETSAL=SAL+100WHERETNOIN(SELECTT.TNOFROMT,TCWHERET.TNO=TC.TNOANDTC.CNO='C5')/*通過連接查詢找到講授C5課程的教師編號。*/兩層lefjoinSqlserver的數(shù)組實(shí)現(xiàn)Sqlserver2008數(shù)據(jù)庫命名規(guī)則數(shù)據(jù)庫字符規(guī)范26個(gè)英文字母，0-9個(gè)自然數(shù)，加下劃線，共36個(gè)字符?！靶憽薄耙娒狻睌?shù)據(jù)庫對象命名規(guī)范使用單數(shù)。常用對象前綴表tb_<表的內(nèi)容分類>_<表的內(nèi)容>視圖vi存儲(chǔ)過程sp函數(shù)fn索引idx_<表名>_<索引標(biāo)識>主鍵pk_<表名>_<主鍵標(biāo)識>外鍵fk_<表名>_<主表名>_<外鍵標(biāo)識>序列seq表名：tb_XX字段名：user_idhas_message存儲(chǔ)過程：sp_user_操作名操作名：insert|delete|update|calculate|confirm語句規(guī)范：所有關(guān)鍵字全部大寫Sql分支結(jié)構(gòu)語法If語句DECLARE@dINTset@d=1IF@d=1BEGINPRINT'正確'ENDELSEBEGINPRINT'錯(cuò)誤'END多條件選擇declare@todayintdeclare@weeknvarchar(3)set@today=3set@week=casewhen@today=1then'星期一'when@today=2then'星期二'when@today=3then'星期三'when@today=4then'星期四'when@today=5then'星期五'when@today=6then'星期六'when@today=7then'星期日'else'值錯(cuò)誤'endprint@weekcase@inputwhenathen‘’whenbthen‘’else@inputend循環(huán)語句DECLARE@iINTSET@i=1WHILE@i<1000000BEGINset@i=@i+1END批量插入*2.BulkInsert根據(jù)格式文件導(dǎo)入數(shù)據(jù)文件，語法格式如下：Sql代碼Bulkinsert數(shù)據(jù)庫名.用戶名.表名from'數(shù)據(jù)文件路徑'with(formatfile='格式文件路徑',FirstRow=2--指定數(shù)據(jù)文件中開始的行數(shù)，默認(rèn)是1)條件查詢常用關(guān)鍵字Distinctdistict在列名前Selectdistinctcol1fromtableAnd/ORWherecondition1and/orcondition2In/notinWherecol1in/notin(‘xx1’,’xx2’)BetweenandWherecol1bettween1and5LikeWherenamelike‘a(chǎn)a%’Exists/notexists指定子查詢，檢測行的存在，子查詢返回boolean值Whereexists(select1fromtablewherename=’xx’)Groupbyhaving:having和where可以同時(shí)出現(xiàn)Goupbynamehavingsum(sales)>1500null/notnullset@param=nullIsnull/IsnullIf@paramisnull/isnotnullIsnull(@value,0)where子句的作用是在對查詢結(jié)果進(jìn)行分組前，將不符合where條件的行去掉，即在分組之前過濾數(shù)據(jù)，條件中不能包含聚組函數(shù)，使用where條件顯示特定的行。having子句的作用是篩選滿足條件的組，即在分組之后過濾數(shù)據(jù)，條件中經(jīng)常包含聚組函數(shù)，使用having條件顯示特定的組，也可以使用多個(gè)分組標(biāo)準(zhǔn)進(jìn)行分組。Orderasc/desc聯(lián)合查詢等值連接/*將books表和表bookstype中的信息聯(lián)合查詢，條件是聯(lián)系鍵相等*/select*frombooks,bookstypewherebookstype.typeid=books.typeid內(nèi)連接/*將books表和表bookstype中的信息聯(lián)合查詢，條件是聯(lián)系鍵相等，和等值連接等價(jià)*/select*frombooksinnerjoinbookstypeonbooks.typeid=bookstype.typeid左外連接（左邊可以為空）/*將books表和表bookstype中的信息聯(lián)合查詢，包括在books表中沒有和bookstype表關(guān)聯(lián)的信息*/select*frombooksleftouterjoinbookstypeonbookstype.typeid=books.typeid右外連接/*將bookstype表和books表中的信息聯(lián)合查詢，包括在bookstype表中沒有和books表關(guān)聯(lián)的信息*/select*frombooksrightouterjoinbookstypeonbookstype.typeid=books.typeid全連接/*將bookstype表和books表中的信息聯(lián)合查詢，包括在books表中沒有和bookstype表關(guān)聯(lián)的信息以及在bookstype表中沒有和books表關(guān)聯(lián)的信息*/select*frombooksfullouterjoinbookstypeonbookstype.typeid=books.typeid不等值連接查詢/*兩表關(guān)聯(lián)查詢，查詢表A的cid在表B中沒有對應(yīng)cid的表A的信息SQL*/SQLServer:select*frommvc_catalogcwherec.cidnotin(selectm.cidfrommvc_bookm);子查詢子查詢是一個(gè)在select查詢中含有其他的select語句，子查詢通常用在where子句中，即將一個(gè)查詢結(jié)果做為查詢的條件.集合查詢集合運(yùn)算是將2個(gè)或多個(gè)子查詢結(jié)果進(jìn)行(并union),交(intersect)和減(minus)(并union):selectemployee_id,empnamefromemp1unionselectemployee_id,namefromemp2圖:employee_idempname100king11000king21000king310000king4100000king51000000king6注:使用union,重復(fù)行只包括一個(gè),當(dāng)使用unionall時(shí)將包括重復(fù)行使用union和unionall必須保證各個(gè)select集合的結(jié)果有相同個(gè)數(shù)的列，并且每個(gè)列的類型是一樣的。交(intersect):2個(gè)或多個(gè)子查詢的公共行selectemployee_id,empnamefromemp1intersectselectemployee_id,namefromemp2減(minus):從第一個(gè)查詢結(jié)果中去掉出現(xiàn)在第二個(gè)查詢結(jié)果中的行:selectemployee_id,empnamefromemp1minusselectemployee_id,namefromemp2Sql與oracle常用函數(shù)對比數(shù)學(xué)函數(shù)sql語法：selectabs（-1）valueoracle語法：selectabs（-1）valuefromdualsqlserveroracle絕對值absabs取符號signsign向上取整ceilingceil**向下取整floorfloor取整casttrunc**四舍五入roundrounde為底的冪expexp平方squarepower(4,2)**任意底數(shù)的冪power(a,b)power開根號sqrtsqrte為底的對數(shù)logln10為底的對數(shù)log10log(10,10)**隨機(jī)數(shù)rand()sys.dbms_random.value(0,1)**圓周率PI()三角函數(shù)sin(pi/2)costan（單位為弧度）反三角函數(shù)asinacosatan(返回值為弧度)弧度轉(zhuǎn)角度degrees角度轉(zhuǎn)弧度radians集合函數(shù)指定列的記錄數(shù)count指定列數(shù)據(jù)求和sum求最大值max求最小值min平均值avg中間值median奇數(shù)個(gè)返回正中間的，偶數(shù)個(gè)返回中間兩個(gè)的平均值排序rank1）用法1:RANKOVERRANK()OVER([PARTITIONBYcolumn1]ORDERBYcolumn2[ASC|DESC])分類排序2）用法2：RANKWITHINGROUPRANK(expr1)WITHINGROUP(ORDERBYexpr2)值為expr1的行，在列expr2中的排序?yàn)槎嗌?。FIRST、LAST 語法： agg_function（e1）KEEP(DENSE_RANKFIRSTORDERBYe2[NULLS{FIRST|LAST}])[OVERPARTITIONBYe3] agg_function（e1）KEEP(DENSE_RANKLASTORDERBYe2[NULLS{FIRST|LAST}])[OVERPARTITIONBYe3]按e2排序之后取第一個(gè)（最后一個(gè)）然后在其中找e1函數(shù)規(guī)定的條目。字符函數(shù)sql中字符位置從1開始計(jì)算sqlserveroracle由字符求序號ascii('a')ascii('a')由序號求字符char(9)chr(9)**字符連接'11+'22'+'33'concat('11','22')**長度lendatalengthlengthlengthb大小寫lowerupper重復(fù)字符串replicate('abcd',2)（轉(zhuǎn)換連接長度大小重復(fù)）求子串substring('abcd',start_index,len)substr('abcd',2,2)**子串位置charindex('s','sdsq',searchpos)instr('sdsq','s',2)**模糊子串位置patindex('%d%q%','sdsfasdqe')null子串填充stuff('abcdef',2,3,'ijkmln')replace('abcdef','bcd','ijklmn')**Replace(s_before,s_tobe,s_new)子串全部替換translate('fasdbfa','fa','我')單詞首字母大寫initcap（'abcde'）左補(bǔ)空格space(10)+'abcd'LPAD('abcd',4)右補(bǔ)空格‘a(chǎn)bcd’+space(10)RPAD('abcd',4)**刪除空格ltrim+rtrimltrim,rtrim,trim**發(fā)音值soundex('smith')soundex('smith')發(fā)音相似性比較difference('smithers','smythers')日期函數(shù)系統(tǒng)時(shí)間getdate()sysdate**求日期convert(char(10),getdate(),20)trunc(sysdate)to_char(sysdate,'yyyy-mm-dd')求時(shí)間convert(char(8),getdate,108)to_char(sysdate,'hh24:mm:ss')取日期時(shí)間的其他部分datepartdatenameto_char當(dāng)月最mi,后一天LAST_DAY(sysdate)本星期某一天Next_day(sysdate,7)字符串轉(zhuǎn)時(shí)間cast('2004-09-08'asdatetime)to_date('2004-01-0522:09:38','yyyy-mm-ddhh24-mi-ss')兩日期某一部分的差datediff(ss,getdate(),getdate()+12.3)(d1-d2)*24*60*60根據(jù)差值求新的日期dateadd(mi,5,getdate())sysdate+8/60/24求不同時(shí)區(qū)時(shí)間New_time(sysdate,'ydt','gmt')常用函數(shù)select

left('abcdef',3)abc超長字符串賦值時(shí)使用select，使用setsubstring('abcd',start_index,length)charindex('char_to_be_find','string_to_search',searchpos)日期相關(guān)轉(zhuǎn)換：convert(date,getdate(),112)日期轉(zhuǎn)化為年月日格式（20051112）convert(char(10),getdate(),20)日期轉(zhuǎn)化為年月日（2005-11-12）convert(char(8),getdate(),108)日期轉(zhuǎn)時(shí)間（15:21:22）字符轉(zhuǎn)換convert(decimal(19,0),’123445456’)cast(‘12334’asdecimal(19,0))cast(111111asvarchar(10))ISNULL()ISNULL(check_expression,replacement_value)isnull（p1，p2）其用法是如果p1為null，則用p2代替。Otherdb..table:對另一個(gè)數(shù)據(jù)庫中的表進(jìn)行操作object_id返回int類型的數(shù)據(jù)庫對象標(biāo)識號。

語法

OBJECT_ID

(

'object'

)

分頁查詢Select*from(selectROW_NUMBER()over(orderbyid1)orderid,*fromtable1))table2wheretable2.orderidbetween1and5SQLSERVER的操作快捷鍵執(zhí)行語句:Alt+XorF5存儲(chǔ)過程速度快，性能好，提前解析、編譯、優(yōu)化，緩存。存儲(chǔ)在數(shù)據(jù)庫服務(wù)器，減少網(wǎng)絡(luò)通信。業(yè)務(wù)邏輯封裝。安全減少SQL注入攻擊。分類系統(tǒng)存儲(chǔ)過程：以sp_開頭,用來進(jìn)行系統(tǒng)的各項(xiàng)設(shè)定.本地存儲(chǔ)過程：用戶創(chuàng)建的存儲(chǔ)過程是由用戶創(chuàng)建并完成某一特定功能的存儲(chǔ)過程，事實(shí)上一般所說的存儲(chǔ)過程就是指本地存儲(chǔ)過程。臨時(shí)存儲(chǔ)過程：分為兩種存儲(chǔ)過程：一是本地臨時(shí)存儲(chǔ)過程，以井字號(#)作為其名稱的第一個(gè)字符，則該存儲(chǔ)過程將成為一個(gè)存放在tempdb數(shù)據(jù)庫中的本地臨時(shí)存儲(chǔ)過程，且只有創(chuàng)建它的用戶才能執(zhí)行它;二是全局臨時(shí)存儲(chǔ)過程，以兩個(gè)井字號(##)號開始，則該存儲(chǔ)過程將成為一個(gè)存儲(chǔ)在tempdb數(shù)據(jù)庫中的全局臨時(shí)存儲(chǔ)過程，全局臨時(shí)存儲(chǔ)過程一旦創(chuàng)建，以后連接到服務(wù)器的任意用戶都可以執(zhí)行它，而且不需要特定的權(quán)限。遠(yuǎn)程存儲(chǔ)過程：在SQLServer2005中，遠(yuǎn)程存儲(chǔ)過程(RemoteStoredProcedures)是位于遠(yuǎn)程服務(wù)器上的存儲(chǔ)過程，通?？梢允褂梅植际讲樵兒虴XECUTE命令執(zhí)行一個(gè)遠(yuǎn)程存儲(chǔ)過程。擴(kuò)展存儲(chǔ)過程：擴(kuò)展存儲(chǔ)過程(ExtendedStoredProcedures)是用戶可以使用外部程序語言編寫的存儲(chǔ)過程，而且擴(kuò)展存儲(chǔ)過程的名稱通常以xp_開頭。格式Createprocedureprocedue_name[@parameterdata_type][output][with]{recompile|encryption}assql_statement解釋：output：表示此參數(shù)是可傳回的with{recompile|encryption}recompile:表示每次執(zhí)行此存儲(chǔ)過程時(shí)都重新編譯一次encryption:所創(chuàng)建的存儲(chǔ)過程的內(nèi)容會(huì)被加密存儲(chǔ)過程的3種傳回值：1)、以Return傳回整數(shù)2)、以output格式傳回參數(shù)3)、RecordsetOutput定義時(shí)：@variabledata_typeoutput運(yùn)行時(shí)：Declare@variable_outdata_typeExecsp@para,@variable_outoutputReturn定義時(shí)：return@returndivname執(zhí)行時(shí)：execute@id=getdivname'1','divid'常用實(shí)例、語句(字符拼接，執(zhí)行)帶參存儲(chǔ)過程的新建與執(zhí)行加入一筆記錄到表book,并查詢此表中所有書籍的總金額Createprocinsert_book@param1char(10),@param2varchar(20),@param3money,@param4moneyoutputwithencryption加密asinsertintobook(編號,書名,價(jià)格)Values(@param1,@param2,@param3)select@param4=sum(價(jià)格)frombookgo執(zhí)行例子：declare@total_pricemoneyexecinsert_book'003','Delphi控件開發(fā)指南',$100,@total_priceprint'總金額為'+convert(varchar,@total_price)go分頁存儲(chǔ)過程存儲(chǔ)過程、row_number完成分頁

if(object_id('pro_page','P')isnotnull)

dropprocproc_cursor

go

createprocpro_page

@startIndexint,

@endIndexint

as

selectcount(*)fromproduct

;

select*from(

selectrow_number()over(orderbypid)asrowId,*fromproduct

)temp

wheretemp.rowIdbetween@startIndexand@endIndex

go

--dropprocpro_page

execpro_page1,4

--

--分頁存儲(chǔ)過程

if(object_id('pro_page','P')isnotnull)

dropprocpro_stu

go

createprocedurepro_stu(

@pageIndexint,

@pageSizeint

)

as

declare@startRowint,@endRowint

set@startRow=(@pageIndex-1)*@pageSize+1

set@endRow=@startRow+@pageSize-1

select*from(

select*,row_number()over(orderbyidasc)asnumberfromstudent

)t

wheret.numberbetween@startRowand@endRow;

execpro_stu2,2;帶事務(wù)的存儲(chǔ)過程刪除一條留言信息會(huì)級聯(lián)刪除回復(fù)信息，這時(shí)我們需要用到事務(wù)。ALTERPROCEDURE[dbo].[proc_tb_leaveword_delete](@leavewordIDINT,@recordTINYINTOUTPUT)ASBEGINBEGINTRYBEGINTRANSACTIONDELETEFROMtb_leavewordIDWHEREleavewordID=@leavewordIDDELETEFROMtb_replyWHEREleavewordID=@leavewordIDSET@record=0--成功COMMITTRANSACTIONENDTRYBEGINCATCHROLLBACKTRANSACTIONSET@record=-1--失敗ENDCATCHRETURN@recordENDSQL字符串中有變量的拼接方法方法一：不拆分整個(gè)字符串（從字符串中間插入變量）Select@s='Select*Fromdbo.CategorieswhereCategoryID='''+cast(@TestIDasvarchar)+''''方法二：拆分字符串（從字符串尾部追加）Set@s='Select*Fromdbo.CategorieswhereCategoryID='+''''+cast(@TestIDasvarchar)+''''SQL字符串拼接與不拼接的實(shí)現(xiàn)多條件查詢declare

@str

varchar(8000)

set

@str

=

''

select

@str

=

@str

+

','

+

cast(value

as

varchar)

from

tb

where

id

=

@id

set

@str

=

right(@str

,

len(@str)

-

1)以前拼接的寫法set@sql='select*fromtablewhere1=1'if(@addDateisnotnull)set@sql=@sql+'andaddDate='+@addDate+''if(@name<>''andisnotnull)set@sql=@sql+'andname='+@name+''exec(@sql)下面是不采用拼接SQL字符串實(shí)現(xiàn)多條件查詢的解決方案第一種寫法是感覺代碼有些冗余if(@addDateisnotnull)and(@name<>'')select*fromtablewhereaddDate=@addDateandname=@nameelseif(@addDateisnotnull)and(@name='')select*fromtablewhereaddDate=@addDateelseif(@addDateisnull)and(@name<>'')select*fromtablewhereandname=@nameelseif(@addDateisnull)and(@name='')select*fromtable第二種寫法是select*fromtablewhere(addDate=@addDateor@addDateisnull)and(name=@nameor@name='')第三種寫法是SELECT*FROMtablewhereaddDate=CASE@addDateISNULLTHENaddDateELSE@addDateEND,name=CASE@nameWHEN''THENnameELSE@nameENDsql語句中的字符串拼接字符串拼接問題單引號必須是成對的。單引號必須是成對的在成對單引號之間的單引號，每兩個(gè)相當(dāng)于一個(gè)轉(zhuǎn)換之后的單引號字符?！?23’’’123’加號運(yùn)算符的效果相當(dāng)于去掉前面和后面的串兩端的兩個(gè)單引號‘123’’’+Name+’’字符拼接:@sql=@sql+‘namelike‘’%’+@para+‘%’‘’@sql=@sql+’andid=’+@in_id@sql=@sql+’andname=’’’+@in_name+’’’’@ids=replace(@ids,’[’,’’’’)[‘@ids=replace(@ids,’,’,(‘’’’+’,’+’’’’))@update_sql=’updatetabsetsfqi=’’01’’whereID=’+@id[@id若為bigint類型，則報(bào)錯(cuò)]Id非自增的處理：找到數(shù)據(jù)庫中ID的最大值然后加1Set@new_id=(SelectMAX(ID)fromtable)+1執(zhí)行sqlExecutesp_executesql@sql@sql一定要為nvarchar或@sql=N’select**’SP拼接查詢條件原則：都要先轉(zhuǎn)成字符。字符拼接：一定要給字符條件添加引號數(shù)字拼接數(shù)字條件不添加引號時(shí)間拼接一定要給時(shí)間條件添加引號SP中的nullisnull‘’的區(qū)別首先說明SETANSI_NULLS[ON/OFF]。ON值采用ANSISQL標(biāo)準(zhǔn)，OFF值采用非標(biāo)準(zhǔn)模式。按照ANSISQL標(biāo)準(zhǔn)，下面的兩個(gè)查詢都不返回任何行：Query1:select*fromTwhereData=nullQuery2:select*fromTwhereData<>null非ANSISQL標(biāo)準(zhǔn)中Data=Null等同于DataIsNull，Data<>Null等同于DataIsNotNull。所以，一般情況下使用isnullisnotnull不容易引起問題。‘’表示字符串長度為0；字符型參數(shù)值為空則取值為’’，int類型參數(shù)值為空則取值為0；查詢變量的數(shù)據(jù)類型DECLARE@anvarchar(10)SET@a=N'HelloWorld'SELECT@a,CAST(SQL_VARIANT_PROPERTY(@a,'BaseType')ASVARCHAR(50))擴(kuò)展存儲(chǔ)過程sp_executesql建議您在執(zhí)行字符串時(shí)，使用

sp_executesql

存儲(chǔ)過程而不要使用EXECUTE語句。由于此存儲(chǔ)過程支持參數(shù)替換，因此

sp_executesql

比EXECUTE的功能更多；由于SQLServer更可能重用

sp_executesql

生成的執(zhí)行計(jì)劃，因此

sp_executesql

比EXECUTE更有效。DECLARE@IntVariableINT;DECLARE@SQLStringNVARCHAR(500);DECLARE@ParmDefinitionNVARCHAR(500);/*BuildtheSQLstringonetime.*/SET@SQLString=N'SELECT*FROMAdventureWorks2008R2.Sales.StoreWHERESalesPersonID=@SalesID';/*Specifytheparameterformatonetime.*/SET@ParmDefinition=N'@SalesIDint';/*Executethestringwiththefirstparametervalue.*/SET@IntVariable=275;EXECUTEsp_executesql@SQLString,@ParmDefinition,@SalesID=@IntVariable;/*Executethesamestringwiththesecondparametervalue.*/SET@IntVariable=276;EXECUTEsp_executesql@SQLString,@ParmDefinition,@SalesID=@IntVariable;臨時(shí)表vs表變量臨時(shí)表存在tempdb中，不再使用時(shí)自動(dòng)刪除。分為本地和全局。本地臨時(shí)表：格式為#XXX，僅當(dāng)前的用戶連接可見，實(shí)例斷開時(shí)刪除；創(chuàng)建臨時(shí)表時(shí)表名后，表出現(xiàn)在數(shù)據(jù)庫testdb中，表名后加了一串標(biāo)識。其他操作：全局臨時(shí)表：格式為##XXX，任何用戶均可見，所有引用該表的用戶斷開連接時(shí)刪除。表變量存儲(chǔ)在內(nèi)存中；表連接時(shí)要為表變量指定別名；表變量在批處理結(jié)束時(shí)自動(dòng)被系統(tǒng)刪除；DECLARE@tb1Table(Idint,Namevarchar(20),Ageint)INSERTINTO@tb1VALUES(1,'劉備',22)表變量VS臨時(shí)表：數(shù)據(jù)量小，使用表變量。數(shù)據(jù)量大，使用臨時(shí)表。編譯解析解析與編譯創(chuàng)建過程：語法檢查。執(zhí)行過程[解析+創(chuàng)建執(zhí)行計(jì)劃]如果存儲(chǔ)過程中有不存在的表對象，創(chuàng)建過程不會(huì)出錯(cuò)，執(zhí)行時(shí)會(huì)報(bào)錯(cuò)。稱為延遲名稱解析。因?yàn)椋捍鎯?chǔ)過程創(chuàng)建時(shí)不需要對象存在，執(zhí)行時(shí)才需要。如果存儲(chǔ)過程中不存在列對象，創(chuàng)建過程就會(huì)報(bào)錯(cuò)。分析存儲(chǔ)過程和創(chuàng)建執(zhí)行計(jì)劃的過程稱為編譯。執(zhí)行計(jì)劃將駐留在內(nèi)存中，直到重新啟動(dòng)SQLServer或其他對象需要存儲(chǔ)空間時(shí)為止。執(zhí)行存儲(chǔ)過程時(shí)，如果內(nèi)存中存在執(zhí)行計(jì)劃則使用該執(zhí)行計(jì)劃。存儲(chǔ)過程安全性調(diào)用存儲(chǔ)過程可以保證更全面的安全性。查看存儲(chǔ)過程SP_HELP'dbo.USP_GetEmployeById'--查看存儲(chǔ)過程的基本信息：例如參數(shù)等SP_HELPTEXT'dbo.USP_GetEmployeById'--查看具體的存儲(chǔ)過程selent*fromsys.sql_moduleswhereobject_id=object_id(N'dbo.USP_GetEmployeById')--查看具體的存儲(chǔ)過程sp_renameoldname,newname.加密解密CREATEPROCEDUREdbo.USP_GetEmployeById@EmployeeIDINT=-1,@EmployeeNameNVARCHAR(30)=NULLWITHENCRYPTIONAS觸發(fā)器是一種特殊的存儲(chǔ)過程函數(shù)定義表值函數(shù)返回值為一張表，標(biāo)量函數(shù)返回值為標(biāo)量。用戶自定義函數(shù)不能用于執(zhí)行一系列改變數(shù)據(jù)庫狀態(tài)的操作，但它可以像系統(tǒng)函數(shù)一樣在查詢或存儲(chǔ)過程等的程序段中使用，也可以像存儲(chǔ)過程一樣通過EXECUTE命令來執(zhí)行標(biāo)量函數(shù)定義：Createfunctionf_test(@in_stringvarchar(200))Returnsnvarchar(1024)AsBeginDeclare--Return@str_resultEnd調(diào)用：selectdbo.g_test(name)表值函數(shù)定義：Createfunction[dbo].[GetAllGoods]()ReturnstableAsReturn(select*from[Master_goods])調(diào)用：select*fromGetAllGoods游標(biāo)游標(biāo)使用--declareproduct_cusorcursor（global|local|forward_only|scroll）forselectPdt_id,Pdt_namefromProductopenproduct_cusordeclare@idintdeclare@namenvarchar(20)while@@FETCH_STATUS=0begin--fetch(first|last|prior|next|absolutn|relativen(-n))from..fetchnextfromproduct_cusorinto@id,@nameprint@idprint@nameendcloseproduct_cusordeallocateproduct_cusor數(shù)據(jù)庫高級同義詞同義詞是用來實(shí)現(xiàn)下列用途的數(shù)據(jù)庫對象：為本地或遠(yuǎn)程服務(wù)器上的另一個(gè)數(shù)據(jù)庫對象（稱為“基對象”）提供備選名稱。提供一個(gè)提取層，該層防止客戶端應(yīng)用程序的基對象的名稱或位置被更改。例如，名為Server1的服務(wù)器上有AdventureWorks的Employee表。若要從另一臺服務(wù)器Server2引用此表，客戶端應(yīng)用程序必須使用由四個(gè)部分構(gòu)成的名稱Server1.AdventureWorks.Person.Employee。另外，如果更改了表的位置（例如，更改到另一臺服務(wù)器上），那么需要修改客戶端應(yīng)用程序以反映所做的更改。若要解決這兩個(gè)問題，您可以在Server2上為Server1上的Employee表創(chuàng)建一個(gè)同義詞EmpTable。這樣，客戶端應(yīng)用程序只需使用由一個(gè)部分構(gòu)成的名稱EmpTable來引用Employee表。另外，如果Employee表的位置發(fā)生變化，則必須修改同義詞EmpTable以指向Employee表的新位置。由于沒有ALTERSYNONYM語句，因此，必須先刪除同義詞EmpTable，然后使用同樣的名稱重新創(chuàng)建該詞，但必須指向Employee的新位置。同義詞的創(chuàng)建：示例以下示例首次創(chuàng)建將在此后的示例中使用的同義詞。USEtempdb;

GO

CREATESYNONYMMyAddressType

FORAdventureWorks.Person.AddressType;

GO

以下示例將行插入到由MyAddressType同義詞引用的基表。USEtempdb;

GO

INSERTINTOMyAddressType(Name)

VALUES('Test');

GO

以下示例闡述了如何在動(dòng)態(tài)SQL中引用同義詞。USEtempdb;

GO

EXECUTE('SELECTNameFROMMyAddressType');

區(qū)分大小寫問題如果你是先建好數(shù)據(jù)庫再建的表，那么所有字符型變量默認(rèn)繼承數(shù)據(jù)庫collation那么，你右擊那個(gè)數(shù)據(jù)庫，選屬性，在最下面就能看到ｃｏｌｌａｔｉｏｎ，找倒數(shù)第二個(gè)字段，CI就是不區(qū)分，CS就是區(qū)分。Chinese_PRC_CI_AS是不區(qū)分Chinese_PRC_CS_AS是區(qū)分Sql+xml使用xml進(jìn)行字符拆分：DECLARE

@str

VARCHAR(1000)

DECLARE

@x

xml

SET

@str='aaa,bbb,ccc'

SELECT

id

FROM

(SELECT

[value]

=

CONVERT(XML

,

'<v>'

+

REPLACE(@str

,

','

,

'</v><v>')+

'</v>')

)

A

OUTER

APPLY

(

SELECT

id

=

N.v.value('.'

,

'varchar(100)')

FROM

A.[value].nodes('/v')

N

(v)

)

BSql將一個(gè)整型數(shù)字轉(zhuǎn)換成帶前導(dǎo)零的固定長度數(shù)字字符串Sp:/*功能：將一個(gè)整型數(shù)字轉(zhuǎn)換成帶前導(dǎo)零的固定長度數(shù)字字符串*/ALTERFUNCTION[dbo].[fnGenerateFixLengthCode](@codeasint,@codeLengthasint)RETURNSvarchar(100)ASBEGINdeclare@returnCodeasvarchar(100)set@returnCode=convert(varchar(100),@code)set@returnCode=isnull(replicate('0',@codeLength-len(@returnCode)),'')+@returnCodereturn@returnCodeEND連接數(shù)問題Sqlserver默認(rèn)連接數(shù)設(shè)置為0，表示不限制連接數(shù)。查看方式：企業(yè)管理器-->右鍵你的實(shí)例(就是那個(gè)有綠色圖標(biāo)的)-->屬性-->連接-->改最大并發(fā)連接數(shù)為0(無限制)為數(shù)據(jù)庫添加鏈接服務(wù)器非腳本的創(chuàng)建方式：/album/86f4a73e4e730737d752697e.html?picindex=1查看數(shù)據(jù)庫版本Select@@versionsqlserver2008密碼忘記：administrator登錄系統(tǒng)，windows身份驗(yàn)證登錄數(shù)據(jù)庫，安全性-登錄名–sa修改密碼即可。Bat文件Bat文件輸出日志：>>xx.log追加>xx.log覆蓋。/iw1210/article/details/39313677在Windows批處理文件中引用日期和時(shí)間的方法如取出格式為YYYYMMDD的日期：%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%-->20081218誤操作delete的恢復(fù)/lyhabc/p/3683147.html遇到的問題：數(shù)據(jù)庫右鍵，修改兼容性級別。Mergeinto詳情參考：/highriver/archive/2011/08/02/2125043.htmlMERGE語句用來合并UPDATE和INSERT語句。通過MERGE語句，根據(jù)一張表或子查詢的連接條件對另外一張表進(jìn)行查詢，連接條件匹配上的進(jìn)行UPDATE，無法匹配的執(zhí)行INSERT。MERGE

INTO

products

p

USING

newproducts

np

ON

(duct_id

=

duct_id)

WHEN

MATCHED

THEN

UPDATE

SET

duct_name

=

duct_name,

p.category

=

np.category

WHERE

p.category

=

'DVD'

WHEN

NOT

MATCHED

THEN

INSERT

VALUES

(duct_id,

duct_name,

np.category)

WHERE

np.category

!=

'BOOKS'

經(jīng)測試where子句只能寫在using后面。Insert說明的values字段需與p表中字段一致，并且不能對p表進(jìn)行select篩選字段。Withas(公用表達(dá)式)一．WITHAS的含義

WITHAS短語，也叫做子查詢部分（subqueryfactoring），可以讓你做很多事情，定義一個(gè)SQL片斷，該SQL片斷會(huì)

被整個(gè)SQL語句所用到。有的時(shí)候，是為了讓SQL語句的可讀性更高些，也有可能是在UNIONALL的不同部分，作為提供數(shù)

據(jù)的部分。

特別對于UNIONALL比較有用。因?yàn)閁NIONALL的每個(gè)部分可能相同，但是如果每個(gè)部分都去執(zhí)行一遍的話，則成本太高，

所以可以使用WITHAS短語，則只要執(zhí)行一遍即可。如果WITHAS短語所定義的表名被調(diào)用兩次以上，則優(yōu)化器會(huì)自動(dòng)將

WITHAS短語所獲取的數(shù)據(jù)放入一個(gè)TEMP表里，如果只是被調(diào)用一次，則不會(huì)。而提示materialize則是強(qiáng)制將WITHAS

短語里的數(shù)據(jù)放入一個(gè)全局臨時(shí)表里。很多查詢通過這種方法都可以提高速度。二．使用方法先看下面一個(gè)嵌套的查詢語句：select*fromperson.StateProvincewhereCountryRegionCodein

(selectCountryRegionCodefromperson.CountryRegionwhereNamelike'C%')上面的查詢語句使用了一個(gè)子查詢。雖然這條SQL語句并不復(fù)雜，但如果嵌套的層次過多，會(huì)使SQL語句非常難以閱

讀和維護(hù)。因此，也可以使用表變量的方式來解決這個(gè)問題。SQL語句如下：declare@ttable(CountryRegionCodenvarchar(3))

insertinto@t(CountryRegionCode)(selectCountryRegionCodefromperson.CountryRegionwhereNamelike'C%')

select*fromperson.StateProvincewhereCountryRegionCode

in(select*from@t)

雖然上面的SQL語句要比第一種方式更復(fù)雜，但卻將子查詢放在了表變量@t中，這樣做將使SQL語句更容易維護(hù)，但又

會(huì)帶來另一個(gè)問題，就是性能的損失。由于表變量實(shí)際上使用了臨時(shí)表，從而增加了額外的I/O開銷，因此，表變量的方式

并不太適合數(shù)據(jù)量大且頻繁查詢的情況。為此，在SQLServer2005中提供了另外一種解決方案，這就是公用表表達(dá)式（CTE），使用CTE，可以增加SQL語句的可維護(hù)性，同時(shí)，CTE要比表變量的效率高得多。下面是CTE的語法：[WITH<common_table_expression>[,n]]

<common_table_expression>::=

expression_name[(column_name[,n])]

AS

(CTE_query_definition)

現(xiàn)在使用CTE來解決上面的問題，SQL語句如下：with

cras

(

selectCountryRegionCodefromperson.CountryRegionwhereNamelike'C%'

)

select*fromperson.StateProvincewhereCountryRegionCodein(select*fromcr)

其中cr是一個(gè)公用表表達(dá)式，該表達(dá)式在使用上與表變量類似，只是SQLServer2005在處理公用表表達(dá)式的方式上有

所不同。在使用CTE時(shí)應(yīng)注意如下幾點(diǎn)：1.CTE后面必須直接跟使用CTE的SQL語句（如select、insert、update等），否則，CTE將失效。如下面的SQL語句將無法正

常使用CTE：with

cras

(

selectCountryRegionCodefromperson.CountryRegionwhereNamelike'C%'

)

select*fromperson.CountryRegion--應(yīng)將這條SQL語句去掉

--使用CTE的SQL語句應(yīng)緊跟在相關(guān)的CTE后面--

select*fromperson.StateProvincewhereCountryRegionCodein(select*fromcr)

2.CTE后面也可以跟其他的CTE，但只能使用一個(gè)with，多個(gè)CTE中間用逗號（,）分隔，如下面的SQL語句所示：with

cte1as

(

select*fromtable1wherenamelike'abc%'

),

cte2as

(

select*fromtable2whereid>20

),

cte3as

(

select*fromtable3whereprice<100

)

selecta.*fromcte1a,cte2b,cte3cwherea.id=b.idanda.id=c.id

3.如果CTE的表達(dá)式名稱與某個(gè)數(shù)據(jù)表或視圖重名，則緊跟在該CTE后面的SQL語句使用的仍然是CTE，當(dāng)然，后面的SQL語句

使用的就是數(shù)據(jù)表或視圖了，如下面的SQL語句所示：--table1是一個(gè)實(shí)際存在的表

with

table1as

(

select*frompersonswhereage<30

)

select*from