一、定義變量 --簡單賦值? declare?@a?int set?@a=5? print?@a? ?? --使用select語句賦值? declare?@user1?nvarchar(50)? select?@user1='張三' print?@user1? declare?@user2?nvarchar(50)? select?@user2?=?Name?from?ST_User?where?ID=1? print?@user2? ?? --使用update語句賦值? declare?@user3?nvarchar(50)? update?ST_User?set?@user3?=?Name?where?ID=1? print?@user3 ? 二、表、臨時表、表變量 --創(chuàng)建臨時表1? create?table?#DU_User1? (? ?????[ID]?[int]??NOT?NULL,? ?????[Oid]?[int]?NOT?NULL,? ?????[Login]?[nvarchar](50)?NOT?NULL,? ?????[Rtx]?[nvarchar](4)?NOT?NULL,? ?????[Name]?[nvarchar](5)?NOT?NULL,? ?????[Password]?[nvarchar](max)?NULL,? ?????[State]?[nvarchar](8)?NOT?NULL );? --向臨時表1插入一條記錄? insert?into?#DU_User1?(ID,Oid,[Login],Rtx,Name,[Password],State)?values?(100,2,'LS','0000','臨時','321','特殊');? ?? --從ST_User查詢數(shù)據(jù),填充至新生成的臨時表? select?*?into?#DU_User2?from?ST_User?where?ID<8? ?? --查詢并聯(lián)合兩臨時表? select?*?from?#DU_User2?where?ID<3?union?select?*?from?#DU_User1? ?? --刪除兩臨時表? drop?table?#DU_User1? drop?table?#DU_User2 ? --創(chuàng)建臨時表? CREATE?TABLE?#t? (? ????[ID]?[int]?NOT?NULL,? ????[Oid]?[int]?NOT?NULL,? ????[Login]?[nvarchar](50)?NOT?NULL,? ????[Rtx]?[nvarchar](4)?NOT?NULL,? ????[Name]?[nvarchar](5)?NOT?NULL,? ????[Password]?[nvarchar](max)?NULL,? ????[State]?[nvarchar](8)?NOT?NULL,? )? ?? --將查詢結(jié)果集(多條數(shù)據(jù))插入臨時表? insert?into?#t?select?*?from?ST_User? --不能這樣插入? --select?*?into?#t?from?dbo.ST_User? ?? --添加一列,為int型自增長子段? alter?table?#t?add?[myid]?int?NOT?NULL?IDENTITY(1,1)? --添加一列,默認填充全球唯一標識? alter?table?#t?add?[myid1]?uniqueidentifier?NOT?NULL?default(newid())? ?? select?*?from?#t? drop?table?#t --給查詢結(jié)果集增加自增長列? ?? --無主鍵時:? select?IDENTITY(int,1,1)as?ID,?Name,[Login],[Password]?into?#t?from?ST_User? select?*?from?#t? ?? --有主鍵時:? select?(select?SUM(1)?from?ST_User?where?ID<=?a.ID)?as?myID,*?from?ST_User?a?order?by?myID --定義表變量? declare?@t?table (? ????id?int?not?null,? ????msg?nvarchar(50)?null )? insert?into?@t?values(1,'1')? insert?into?@t?values(2,'2')? select?*?from?@t ?三、循環(huán) --while循環(huán)計算1到100的和? declare?@a?int declare?@sum?int set?@a=1? set?@sum=0? while?@a<=100? begin ????set?@sum+=@a? ????set?@a+=1? end print?@sum 四、條件語句 --if,else條件分支? if(1+1=2)? begin ????print?'對' end else begin ????print?'錯' end ?? --when?then條件分支? declare?@today?int declare?@week?nvarchar(3)? set?@today=3? set?@week=case ????when?@today=1?then?'星期一' ????when?@today=2?then?'星期二' ????when?@today=3?then?'星期三' ????when?@today=4?then?'星期四' ????when?@today=5?then?'星期五' ????when?@today=6?then?'星期六' ????when?@today=7?then?'星期日' ????else?'值錯誤' end print?@week ? 五、游標 declare?@ID?int declare?@Oid?int declare?@Login?varchar(50)? ?? --定義一個游標? declare?user_cur?cursor?for?select?ID,Oid,[Login]?from?ST_User? --打開游標? open?user_cur? while?@@fetch_status=0? begin --讀取游標? ????fetch?next?from?user_cur?into?@ID,@Oid,@Login? ????print?@ID? ????--print?@Login? end close?user_cur? --摧毀游標? deallocate?user_cur 六、觸發(fā)器 觸發(fā)器中的臨時表: Inserted? 存放進行insert和update?操作后的數(shù)據(jù)? Deleted? 存放進行delete?和update操作前的數(shù)據(jù) --創(chuàng)建觸發(fā)器? Create?trigger?User_OnUpdate?? ????On?ST_User?? ????for?Update? As? ????declare?@msg?nvarchar(50)? ????--@msg記錄修改情況? ????select?@msg?=?N'姓名從“'?+?Deleted.Name?+?N'”修改為“'?+?Inserted.Name?+?'”'?from?Inserted,Deleted? ????--插入日志表? ????insert?into?[LOG](MSG)values(@msg)? ?????? --刪除觸發(fā)器? drop?trigger?User_OnUpdate 七、存儲過程 --創(chuàng)建帶output參數(shù)的存儲過程? CREATE?PROCEDURE?PR_Sum? ????@a?int,? ????@b?int,? ????@sum?int?output AS BEGIN ????set?@sum=@a+@b? END ?? --創(chuàng)建Return返回值存儲過程? CREATE?PROCEDURE?PR_Sum2? ????@a?int,? ????@b?int AS BEGIN ????Return?@a+@b? END ?????? --執(zhí)行存儲過程獲取output型返回值? declare?@mysum?int execute?PR_Sum?1,2,@mysum?output print?@mysum? ?? --執(zhí)行存儲過程獲取Return型返回值? declare?@mysum2?int execute?@mysum2=?PR_Sum2?1,2? print?@mysum2 ? ?? 八、自定義函數(shù) 函數(shù)的分類: 1)標量值函數(shù) 2)表值函數(shù) a:內(nèi)聯(lián)表值函數(shù) b:多語句表值函數(shù) 3)系統(tǒng)函數(shù) --新建標量值函數(shù)? create?function?FUNC_Sum1? (? ????@a?int,? ????@b?int )? returns?int as begin ????return?@a+@b? end ?? --新建內(nèi)聯(lián)表值函數(shù)? create?function?FUNC_UserTab_1? (? ????@myId?int )? returns?table as return?(select?*?from?ST_User?where?ID<@myId)? ?? --新建多語句表值函數(shù)? create?function?FUNC_UserTab_2? (? ????@myId?int )? returns?@t?table (? ????[ID]?[int]?NOT?NULL,? ????[Oid]?[int]?NOT?NULL,? ????[Login]?[nvarchar](50)?NOT?NULL,? ????[Rtx]?[nvarchar](4)?NOT?NULL,? ????[Name]?[nvarchar](5)?NOT?NULL,? ????[Password]?[nvarchar](max)?NULL,? ????[State]?[nvarchar](8)?NOT?NULL )? as begin ????insert?into?@t?select?*?from?ST_User?where?ID<@myId? ????return end ?? --調(diào)用表值函數(shù)? select?*?from?dbo.FUNC_UserTab_1(15)? --調(diào)用標量值函數(shù)? declare?@s?int set?@s=dbo.FUNC_Sum1(100,50)? print?@s? ?? --刪除標量值函數(shù)? drop?function?FUNC_Sum1 談?wù)勛远x函數(shù)與存儲過程的區(qū)別: 一、自定義函數(shù): 1.?可以返回表變量 2.?限制頗多,包括 不能使用output參數(shù); 不能用臨時表; 函數(shù)內(nèi)部的操作不能影響到外部環(huán)境; 不能通過select返回結(jié)果集; 不能update,delete,數(shù)據(jù)庫表; 3.?必須return?一個標量值或表變量 自定義函數(shù)一般用在復(fù)用度高,功能簡單單一,爭對性強的地方。 二、存儲過程 1.?不能返回表變量 2.?限制少,可以執(zhí)行對數(shù)據(jù)庫表的操作,可以返回數(shù)據(jù)集 3.?可以return一個標量值,也可以省略return 存儲過程一般用在實現(xiàn)復(fù)雜的功能,數(shù)據(jù)操縱方面。