資料庫原理（十）--t-sql

一、變量


定義變量

declare @變量名稱 類型[=預設值];


變量指派

1、set @變量名稱=value; 

一次隻能給1個變量賦予标量值


2、select @變量名稱=value;

1次可以給多個變量指派,可以通過查詢指派。



二、輸出資訊

1、print:1次隻能輸出1項資訊

2、select:1次可以輸出多項資訊



三、控制結構

1、begin...end:表示一個語句塊，類似于{}

2、if 、if...else、 if...else if...else

3、while(條件)
   begin
	...
	[break|continue]
   end

4、case [表達式]
	when ...
	else ...
   end


五、局部變量與全局變量

1、局部變量：使用者自己所定義，使用一個@作為字首。

2、全局變量:系統所提供的，使用@@作為字首。

3、常用全局變量

@@identity:擷取辨別列的值
@@rowcount:擷取受影響的行數
@@error:擷取錯誤号，0表示上一行語句無錯誤
@@fetch_status:擷取遊标的狀态


六、常用函數

1、日期函數

getDate():擷取目前日期
year(時間):擷取年數
month(時間):擷取月份
day(時間）：擷取今天是幾号

DateAdd(datePart,number,date):在date上加上一個日期

例如：
DATEADD(day,7,regTime)>=GETDATE():表示1周以内

DATEADD(day,7,regTime)<GETDATE()：表示1周以外


DateDiff(datepart,startTime,endTime):擷取2個時間的內插補點

DatePart(detepart,time):擷取日期的某一部分


2、數學函數

abs(number):傳回絕對值

floor(number):傳回小于等于目前數的整數

ceiling(number):傳回大于等于目前數的整數

rand():傳回0-1之間的随機數



3、字元函數

charIndex(searchStr,sourceStr,[startLength]):起始為1，找不到傳回0

str(number):把數字轉換成字元串

convert(targetType,參數):把參數轉換成目标類型

cast(expression1 as targetType):把表達式轉換成目标類型

replace(sourceStr,searchStr,replaceStr):替換字元串

substring(sourceStr,startPosition,length):從statrPosition位置開始截取length個字元

left(sourceStr,length):從左邊截取指定長度字元

right(sourceStr,length):從右邊截取指定長度字元

datalength(sourceStr):擷取字元串的長度

ltrim(sourceStr):去除左邊空格

rtrim(sourceStr):去除右邊空格

reverse(sourceStr):反轉


4、系統函數

isDate(param):判斷參數是否是日期

isNumeric(param):判斷參數是否是數字

isNull(param1,newValue):判斷參數是為為Null,如果為Null就把newValue指派給參數


5、自定義函數

1)建立函數

1、标量函數（傳回基本類型)

create function function_name(參數清單)
returns 傳回類型
[with encryption|schemabinding]
as
begin
   statements
end


2、表值函數（傳回類型為table)

2.1文法：内聯表值函數

CREATE FUNCTION <函數名>(<參數>)
Returns TABLE
[WITH ENCRYPTION | SCHEMABINDING]
[AS]
RETURN   <select語句>


2.2文法：多語句表值函數

CREATE FUNCTION <函數名>(<參數>)
Returns  <表格變量名> TABLE (<表格變量定義>) 
[WITH ENCRYPTION | SCHEMABINDING]
[AS]
BEGIN
      <SQL語句>
      return
END


2)修改函數

文法與建立函數相同，隻需把create換成alter


3)删除删除

drop function 函數名稱



七、存儲過程

由1組預編譯的sql語句組成，存放在資料庫中。

1）優點

1、節省網絡流量
2、提高了執行速度
3、提高了安全性
4、可以進行子產品化設計


2）建立存儲過程

CREATE PROC <存儲過程名稱>
     @parameter1 <資料類型> = <預設值>,
     @parameter2 <資料類型> = <預設值> OUTPUT 
AS
BEGIN
     <代碼塊>
END


執行存儲過程
exec 存儲過程名 參數...

傳回值的方式：

1、使用輸出參數傳回

2、使用return傳回（隻能傳回int類型）


3）修改存儲過程

把create換成alter


4)删除存儲過程

drop proc 存儲過程名稱


八、觸發器

文法：
create trigger trigger_name on table_name
[WITH ENCRYPTION]
for|instead of  [DELETE, INSERT, UPDATE]
AS 
SQL語句


九、遊标:用于存儲查詢結果，通過指針移動讀取資料。

示例：
--1、定義遊标
Declare  acc_cursor  cursor  forward_only 
for select * from record 

--2、打開遊标
open acc_cursor

--3、定義變量接收資料
Declare @aid varchar(6),@ty varchar(6),
@jine float ,@transt datet

--4、先讀取一行
FETCH NEXT FROM acc_cursor INTO @aid,@ty,@jine,@transt  

--5、循環讀取
WHILE(@@FETCH_STATUS = 0)
begin
  insert into record_back2 values(@aid,@ty,@jine,@transt)

  FETCH NEXT FROM acc_cursor INTO @aid,@ty,@jine,@transt
end

--6、關閉遊标
close acc_cursor

--7、銷毀遊标
deallocate acc_cursor