1、變量

?要動態的寫sql語句，就不能沒有變量。

聲明變量并賦值：

1 declare @i as int;--定義一個 int 類型的 變量 (as可以省略) 2 print @i;--這注意：沒有賦值之前，程序不會報錯，而且輸出一個 空 3 set @i=3; 4 print @i;

在sql server 2008之后就可以對變量 在聲明的同時進行賦值

1 declare @a int=3; 2 print @a;

在變量的使用過程中，一定要注意nvarcahr 和nchar的區別。

1 declare @s nvarchar(20); 2 set @s='Hello'; 3 set @s=@s+' World!'; 4 print @s;--輸出的是 Hello World！ 5 6 declare @s2 nchar(20); 7 set @s2='Hello'; 8 set @s2=@s2+' World!'; 9 print @s2;--輸出的是 Hello

為什么使用nchar卻是輸出的 Hello，因為：nchar是固定長度，即使長度沒有達到最大，但是其余長度用 空來代替了，所以 相當于是滿的，所以在進行字符串的相加 是不會起作用的。

在查詢中賦值:

1 declare @now datetime; 2 select @now=GETDATE(); 3 print @now; 4 declare @orderNum int; 5 select @orderNum = COUNT(1) from [Sales.Orders];--這條查詢語句只是用來 對 變量進行賦值的,不會返回查詢結果的. 6 print @orderNum;?

主要作用：是將查詢結果保存在 變量里，為了下面的使用。

其實也可以使用 set 賦值的方式 實現 上面的作用

1 set @orderNum =(select COUNT(1) from [Sales.Orders]) 2 print @orderNum

2、批處理

?使用“go”，go前面的 所有的 語句處于在一個 批里面。不同批 的變量是不能互相調用。

3、流程控制

1）條件控制

1 declare @minute int ; 2 set @minute =DATEPART(minute,getdate()); 3 if @minute>20 4 begin--一條語句可以將 begin end 省略 5 print '馬上睡覺'; 6 end 7 else 8 print '繼續快樂玩耍';

2）循環控制

1 --高斯問題 2 declare @sum int,@i int; 3 set @sum=0; 4 set @i=0; 5 while @i<100 6 begin 7 set @i=@i+1; 8 set @sum=@sum+@i; 9 end 10 print @sum;

?在sql server 里面的 continue 和 break 和c#里面的 使用是相同的。

4、游標

什么是游標:

?不是基于集合的操作，而是將集合中的數據逐條取出來，逐條進行操作。

什么時候使用游標：

?默認的情況下是使用集合的方式 查詢，如果要使用 游標 ?必須是在 能有讓人信服的 理由下才考慮使用。

一般不使用游標的原因：

?1、使用游標，嚴重違背了關系模型，關系模型是基于集合考慮的。

2、逐條對記錄進行操作會帶來性能上的開銷。給定一個集合，對集合進行一系列的游標代碼的操作，一定會帶來性能上的開銷，并且這種使用游標的 方式 性能比 集合 慢了好幾倍。
3、使用游標 要寫很多代碼。但是使用集合的，就只 把需要的數據查詢出來，不描述怎么獲取他們。游標寫很多代碼，可讀性差，維護差。

存在的意義：

?說了這么多的 弊端，那是游標還有存在的意義嗎？當然：當需要對查詢出來的數據 ?逐條進行 處理的 時候就要使用游標.

使用游標的步驟：

1、在查詢中聲明游標

2、打開游標

3、從第一個值開始將值賦值到對應的變量里面。

4、循環遍歷游標，將賦值變量拿過來進行操作。

5、關閉游標

6、釋放游標

1 --1、首先在查詢基礎上聲明游標 2 declare c cursor 3 for 4 select shipperid,companyname 5 from [Sales.Shippers]; 6 7 --2、打開游標 8 open c; 9 10 --3、從第一個游標開始把 值 賦值到 對應的變量里面 11 declare @id int,@name nvarchar(20); 12 13 --每次取出來一條數據， 添加到指定的 變量。 14 fetch next from c into @id,@name; 15 16 --4、循環遍歷 游標，將賦值的變量 拿過來進行相應的操作 17 while @@FETCH_STATUS=0--等于0代表 游標沒有超過最后一行 18 begin 19 --相應的操作處理 20 print @name; 21 22 --嘗試 讀取下一條 數據 23 fetch next from c into @id,@name; 24 end 25 26 --5、關閉游標 27 close c; 28 29 --6、釋放游標 30 deallocate c;

總結：

1、使用游標，要非常謹慎，因為性能消耗很大，不確定的時候絕對不能使用

2、游標的唯一 好處：就是可以對查詢數據，進行 逐條操作。這也正是它適應的場合。

5、臨時表

使用場景：

當要將一些 數據 放到表里面 保存，但是又不想 創建一張數據表（因為一般 公司只有DBA 才有權限創建表）， ?或者我指向讓當前 數據 只有當前 會話可見 ，甚至 只要當前 批 可見。

臨時表的種類：

sql server三種臨時表：局部臨時表、全局臨時表、表變量

下面對三種臨時表進行分別講解：

局部臨時表:

1、創建過程和使用方式 都普通表是 一樣，加上“#” 就代表是臨時表.

2、只對 創建 他的 會話 是可見的，并且存儲 在 系統數據庫的 tempdb 數據的 臨時表 里面。當前會話（進程）結束后，臨時表會自動被刪除

3、在 sql server 里 系統創建的臨時表 都會加 后綴名，就是為了防止 不同進程之間 創建相同的表名的 表名，保證唯一性。

普通創建臨時表的方法：

1 create table #partTable 2 ( 3 num int 4 ); 5 6 insert into #partTable (num) values (1),(2),(3); 7 go;--不再 一個 批 里面 也能使用 ,只要是在同一個 進程里面 8 select * from #partTable;

在查詢過程中創建臨時表，并將查詢出的數據插入到臨時表里面

1 select * into #table from [Sales.Shippers] 2 select * from #table; ?

全局臨時表：

1、在 表名 前面 添加兩個 “#” 代表是 全局臨時表

?2、注意：對所有的 會話(進程)?都是可見的, 但是 當前進程 如果關閉或者 ?全局臨時表 長時間沒有被使用,那么就會被刪除

1 create table ##allTable 2 ( 3 num int 4 ); 5 insert into ##allTable (num) values(1),(2),(3),(4); 6 select * from ##allTable

表變量：

注意：它也會在 ?tempdb數據庫 里面創建一個 對應物理 臨時表,只不過,他只對當前操作他的"批"可見,而且 當前 批 執行完成 之后就會 刪除 臨時表.(所以一定要注意:表 變量 并不是 存在內存中,他也會 創建一張物理數據表)

?

性能考慮:當 只有幾行數據的時候,當然是 表變量 性能好;;但是如果是大量數據,應該使用臨時表

1 declare @tableVariable table 2 ( 3 num int 4 ) 5 6 insert into @tableVariable (num) values (1),(2),(3); 7 select * from @tableVariable; 8 go; 9 --不再同一 批 里面是不能訪問到 表 變量 10 select * from @tableVariable;

?

最重要的一點：表變量同變量一樣，當事務回滾之后，變量的值不會回滾。同理表變量也不會回滾。

1 --回滾中 變量的不會回滾的 特殊情況 2 declare @num int; 3 set @num =1; 4 begin transaction; 5 set @num=12; 6 print @num; 7 rollback; 8 --注意:事務回滾,如果變量在 事務里面 改變,回滾的時候 變量是不會回滾的. 9 print @num; 10 11 --同理:表變量也是如此的 12 declare @tableVariable2 table 13 ( 14 num int 15 ); 16 insert into @tableVariable2 (num) values(1),(2),(3); 17 begin transaction; 18 delete from @tableVariable2 where num =1; 19 rollback; 20 --表變量是不會 回滾的 21 select * from @tableVariable2;

6、動態sql

什么是動態sql

首先靜態sql就是普通的靜態查詢語句。

動態sql：就是使用 exec來執行字符串sql語句。

1 declare @sql nvarchar(100); 2 set @sql ='select * from [Sales.Shippers]'; 3 exec(@sql)

?

缺點：不能防止sql注入漏洞攻擊。（什么是sql注入漏洞大家應該都懂的吧。就不做介紹了）

?

為解決 上面的 sql注入漏洞攻擊 所以又出現了 第二種動態sql ：sp_executesql存儲過程:

1、安全,因為他 支持 輸入和輸出參數的設置

2、性能比 exec 要好:因為它的 參數化 有助于?重用 緩存過?的執行計劃. 執行計劃:就是sql server 處理 sql 語句時 生成的指令. ?如果 要想要 復用 ?緩存 中執行計劃.必須保證 sql 字符串語句 相同.所以要 因為 使用 ?參數化 的sql語句 只要替換 參數就可以,所以 sql語句 不變化 可以復用.

1 declare @sql nvarchar(100); 2 set @sql='select * from [Sales.Shippers] where companyname=@name'; 3 declare @name nvarchar(20); 4 --set @name='順豐'; 5 set @name='順豐;select * from [Sales.Shippers]';--即使這樣,想要進行sql 注入漏洞攻擊,不可能,因為 在sql 語句 把整個 @name里面的 值 作為一個 字符串 來使用的,就是執行 companyname 和 整個字符串的對比 6 7 exec sp_executesql 8 --下面兩個是非常重要的 9 @stmt=@sql,--動態執行的 sql語句 10 @params=N'@name as nvarchar(20)',--參數的類型 11 @name=@name;--參數賦值

7、例程

例程是什么：

為了 計算結果 或者 執行任務 而 封裝的代碼 的一種編程現象.提到例程，大家可能不知道，但是提到下面的他的三個種類，就全都知道了。

例程的種類：

用戶自定義函數、存儲過程、觸發器

最常用的是存儲過程，下面先對存儲過程進行介紹。

存儲過程：

創建存儲過程：

1 --存儲過程：最常用的方法 2 create procedure MyDemoPro 3 ( 4 --存儲過程中 要使用到 的參數 5 @orderid int 6 ) 7 as 8 --下面是執行的 sql 語句 9 select * from [Sales.Orders] where orderid=@orderid;

執行存儲過程：

exec MyDemoPro @orderid=10; --可以簡寫成:exec MyDemoPro 10;

要搞懂存儲過程，就必須搞懂他的三個參數類型：

傳入參數、傳出參數、return參數。

傳入參數：

就是普通的參數;上面使用的那中就是 參入參數

傳出參數：

output 定義的參數:可以 ?傳出 供用戶使用的

1 create procedure OrderCount 2 ( 3 @count int output 4 ) 5 as 6 select @count=COUNT(*) from [Sales.Orders]; 7 go; 8 9 --執行 ,一定以聲明一個變量 ,賦值給 傳出參數 10 declare @outCount int ; 11 exec OrderCount @count=@outCount output; 12 print @outCount;

return參數：

特殊的參數：和 c#里面的不一樣，這里只用來，表示 操作結果的正確或錯誤，只能返回數字

1 alter procedure ReturnProc 2 ( 3 @username nvarchar(100) 4 ) 5 as 6 declare @usernameLen int; 7 set @usernameLen=LEN(@username); 8 9 if @usernameLen>=5 10 return '1'; 11 else 12 return '0'; 13 14 15 declare @result int; 16 --如何為 return 參數 賦值 17 exec @result = ReturnProc @username='wanglihong'; 18 print @result;

?

如果將一個返回參數設置成'asd',就會報錯如下：

?

自定義函數：

1、可以直接返回一個值 ?

2、分兩種：

標量函數（返回值為一個 值）

表函數（返回值是一張表）(存在與 可編程性 里面的函數里面)

3、實際開發中很少使用。

1 create function GetMinute 2 ( 3 @date datetime 4 ) 5 --設置返回值： 6 returns int 7 as 8 begin 9 declare @minute int; 10 set @minute =datepart(minute,@date); 11 return @minute; 12 end 13 14 --使用自定義函數 15 select dbo.GetMinute(GETDATE());

?

觸發器：

特殊的存儲過程。主要作用：檢索。注意：觸發器必須依附于事件，只有當事件發生時候，出發觸發器，運行觸發器代碼。（sql ?server 中和 觸發器相對應兩個事件：數據操作事件和數據定義事件，從而對應下面的連個觸發器）

種類分為兩種：

? DML觸發器(修改觸發器:對表的數據修改:如:update等)

? DLL觸發器(架構觸發器:對數據庫的架構進行修改:如創建表)

DML觸發器:

分為兩種：

? after觸發器(對表操作)

? instead of 觸發器(對視圖進行操作)

注意:在觸發器的代碼里,只能訪問到 inserted 和deleted 兩張表.對數據進行更新的是 先刪除然后在插入執行的。 inserted表包含 insert 和update操作 之后新數據的行。deleted表含有 delete和update 操作 之后舊數據的 行。

對于after觸發器是經常使用，所以這里只對 after做介紹：

與之關聯的事件執行完成之后才出發after觸發器。

為shipper（貨運公司）表創建一張日志表：

1 create table Ship_Log 2 ( 3 id int identity(1,1) primary key, 4 op_date datetime default getdate(), 5 op_uaer nvarchar(50), 6 op nvarchar(50), 7 shipname nvarchar(50), 8 shipphone nvarchar(50) 9 )

?

為表 dbo.Sales.Shipper創建觸發器

1 create trigger ship_log_trigger 2 on [Sales.Shippers] after insert 3 as 4 --當對上面的表進行 增刪改的時候執行 觸發器的下面的代碼 5 insert into Ship_Log (op_uaer,op,shipname,shipphone) 6 select user_name()--返回當前操作的用戶名 7 ,'insert',companyname,phone from inserted;

?

向表?dbo.Sales.Shipper 中插入數據觸發觸發器：

1 insert into [Sales.Shippers] (companyname,phone) values('shits','12345678')

?

查詢日志表：

select * from Ship_Log;

?

查詢結果：

已經將日志插入進去了。

DLL觸發器

一般用不到。

分為兩種:

對數據庫的觸發(例如:創建表)

對服務器的觸發(例如:創建數據庫)

8、標識

標識：就是有時我們會將主鍵設置為標識列（自動增加列），然后查詢標識的時候就是查到最新增加的標識列的值。

分為兩種：

1、全局范圍的：@@identity

2、當前 表 范圍的 ：SCOPE_IDENTITY()；最常用

注意：如果想一張表含有觸發器的表中插入數據的話，查詢到的結果就是不同（因為向表中插入數據之后，觸發器還會再向日志表中插入數據，所以全局標識查到的是日志表中的標識，而 SCOPE_IDENTITY()查到的 插入數據表里的 標識）

1 insert into [Sales.Shippers] (companyname,phone) values('asd','12345'); 2 select @@identity;--整個數據庫中所有的 最新最新增加的 標識列 3 4 select SCOPE_IDENTITY();--獲得 當前操作的表的最新增加的 標識列的值

如果想沒有觸發器的 表 插入數據,兩個就查詢的標識列的值相同

總結

以上是生活随笔為你收集整理的SQLのT_SQL的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。