TRANSACT SQL

Transact-SQL 語言中標準的SQL 語句暢通無阻.Transact-SQL 也有類似於SQL 語言的分類,不過做了許多擴充.Transact-SQL 語言的分類如下:

·變數說明

用來說明變數的命令.

·數據定義語言(DDL,Data Definition Language)

用來建立資料庫、資料庫對象和定義其列，大部分是以CREATE 開頭的命令，如：CREATE TABLE 、CREATE VIEW 、DROP TABLE 等。

·數據操縱語言（DML，Data Manipulation Language）

用來操縱資料庫中的數據的命令，如：SELECT、 INSERT、 UPDATE 、DELETE 、CURSOR等。

·數據控制語言（DCL， Data Control Language）

用來控制資料庫組件的存取許可、存取許可權等的命令。如GRANT、 REVOKE 等。

·流程控制語言（Flow Control Language）

用於設計應用程式的語句如IF WHILE CASE 等

·內嵌函式

說明變數的命令。

·其它命令

嵌於命令中使用的標準函式

上述分類語言中，數據定義語言DDL、 數據操縱語言DML 、數據控制語言DCL 分布在以後各章講述，本章重點討論變數說明、流程控制、內嵌函式和其它不好歸類的命令。

Transact-SQL是ANSI標準SQL資料庫查詢語言的一個強大的實現。

根據其完成的具體功能,可以將T-SQL語句分為四大類,分別為數據定義語句,數據操作語句,數據控制語句和一些附加的語言元素.

數據操作語句:

SELECT,INSERT,DELETE,UPDATE;

數據定義語句:

CREATE TABLE,DROP TABLE ,ALTER TABLE ,

CREATE VIEW,DROP VIEW

CREATE INDEX,DROP INDEX,

CREATE PROCEDURE,ALTER PROCEDURE,DROP PROCEDURE

CREATE TRIGGER,ALTER TRIGGER,DROP TRIGGER

數據控制語句:

CRANT,DENY,REVOKE

附加的語言元素:

BEGIN TRANSACTION/COMMIT,ROLLBACK, SET TRANSACTION,

DECLARE OPEN ,FETCH,CLOSE,EXECUTE

--語 句　功 能

--數據操作

SELECT--從資料庫表中檢索數據行和列

INSERT--向資料庫表添加新數據行

DELETE--從資料庫表中刪除數據行

UPDATE--更新資料庫表中的數據

--數據定義

CREATE TABLE　　--創建一個資料庫表

DROP TABLE　　 --從資料庫中刪除表

ALTER TABLE　　 --修改資料庫表結構

CREATE VIEW　　 --創建一個視圖

DROP VIEW　　 --從資料庫中刪除視圖

CREATE INDEX　　--為資料庫表創建一個索引

DROP INDEX　　 --從資料庫中刪除索引

CREATE PROCEDURE　 --創建一個存儲過程

DROP PROCEDURE　　--從資料庫中刪除存儲過程

CREATE TRIGGER　　--創建一個觸發器

DROP TRIGGER　　--從資料庫中刪除觸發器

CREATE SCHEMA　　--向資料庫添加一個新模式

DROP SCHEMA　　 --從資料庫中刪除一個模式

CREATE DOMAIN　　--創建一個數據值域

ALTER DOMAIN　　--改變域定義

DROP DOMAIN　　 --從資料庫中刪除一個域

--數據控制

GRANT--授予用戶訪問許可權

DENY--拒絕用戶訪問

REVOKE--解除用戶訪問許可權

--事務控制

COMMIT--結束當前事務

ROLLBACK　　 --中止當前事務

SET TRANSACTION　　--定義當前事務數據訪問特徵

--程式化SQL

DECLARE--為查詢設定游標

EXPLAN--為查詢描述數據訪問計畫

OPEN--檢索查詢結果打開一個游標

FETCH--檢索一行查詢結果

CLOSE--關閉游標

PREPARE--為動態執行準備SQL 語句

EXECUTE--動態地執行SQL 語句

DESCRIBE　　 --描述準備好的查詢

---局部變數

declare @id char(10)

--set @id = '10010001'

select @id = '10010001'

---全局變數

---必須以@@開頭

--IF ELSE

declare @x int @y int @z int

select @x = 1 @y = 2 @z=3

if @x > @y

print 'x > y' --列印字元串'x > y'

else if @y > @z

print 'y > z'

else print 'z > y'

--CASE

use pangu

update employee

set e_wage =

case

when job_level = ’1’ then e_wage*1.08

when job_level = ’2’ then e_wage*1.07

when job_level = ’3’ then e_wage*1.06

else e_wage*1.05

end

--WHILE CONTINUE BREAK

declare @x int @y int @c int

select @x = 1 @y=1

while @x < 3

begin

print @x --列印變數x 的值

while @y < 3

begin

select @c = 100*@x + @y

print @c --列印變數c 的值

select @y = @y + 1

end

select @x = @x + 1

select @y = 1

end

--WAITFOR

--例 等待1 小時2 分零3 秒後才執行SELECT 語句

waitfor delay ’01:02:03’

select * from employee

--例 等到晚上11 點零8 分後才執行SELECT 語句

waitfor time ’23:08:00’

select * from employee

***SELECT***

select *(列名) from table_name(表名) where column_name operator value

ex:(宿主)

select * from stock_information where stockid　 = str(nid)

stockname = 'str_name'

stockname like '% find this %'

stockname like '[a-zA-Z]%' --------- ([]指定值的範圍)

stockname like '[^F-M]%'　 --------- (^排除指定範圍)

--------- 只能在使用like關鍵字的where子句中使用通配符)

or stockpath = 'stock_path'

or stocknumber < 1000

and stockindex = 24

not stocksex = 'man'

stocknumber between 20 and 100

stocknumber in(10,20,30)

order by stockid desc(asc) --------- 排序，desc-降序，asc-升序

order by 1,2 --------- by列號

stockname = (select stockname from stock_information　where stockid　= 4)

--------- 子查詢

--------- 除非能確保內層select只返回一個行的值，

--------- 否則應在外層where子句中用一個in限定符

select distinct column_name form table_name --------- distinct指定檢索獨有的列值，不重複

select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name

select stockname , "stocknumber" = count(*) from table_name group by stockname

--------- group by 將表按行分組,指定列中有相同的值

having count(*) = 2　---------　having選定指定的組

select *

from table1, table2

where table1.id *= table2.id -------- 左外部連線，table1中有的而table2中沒有得以null表示

table1.id =* table2.id -------- 右外部連線

select stockname from table1

union [all]　-----　union合併查詢結果集，all-保留重複行

select stockname from table2

***insert***

insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")

value (select Stockname , Stocknumber from Stock_table2)---value為select語句

***update***

update table_name set Stockname = "xxx" [where Stockid = 3]

Stockname = default

Stockname = null

Stocknumber = Stockname + 4

***delete***

delete from table_name where Stockid = 3

truncate table_name ----------- 刪除表中所有行，仍保持表的完整性

drop table table_name --------------- 完全刪除表

***alter table*** --- 修改資料庫表結構

alter table database.owner.table_name add column_name char(2) null .....

sp_help table_name ---- 顯示錶已有特徵

create table table_name (name char(20), age smallint, lname varchar(30))

insert into table_name select ......... ----- 實現刪除列的方法（創建新表）

alter table table_name drop constraint Stockname_default ---- 刪除Stockname的default約束

***function(/*常用函式*/)***

----統計函式----

AVG　　--求平均值

COUNT　 --統計數目

MAX　　--求最大值

MIN　　--求最小值

SUM　　--求和

--AVG

use pangu

select avg(e_wage) as dept_avgWage

from employee

group by dept_id

--MAX

--求工資最高的員工姓名

use pangu

select e_name

from employee

where e_wage =

(select max(e_wage)

from employee)

--STDEV()

--STDEV()函式返回表達式中所有數據的標準差

--STDEVP()

--STDEVP()函式返回總體標準差

--VAR()

--VAR()函式返回表達式中所有值的統計變異數

--VARP()

--VARP()函式返回總體變異數

----算術函式----

/***三角函式***/

SIN(float_expression) --返回以弧度表示的角的正弦

COS(float_expression) --返回以弧度表示的角的餘弦

TAN(float_expression) --返回以弧度表示的角的正切

COT(float_expression) --返回以弧度表示的角的餘切

/***反三角函式***/

ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角

ACOS(float_expression) --返回餘弦是FLOAT 值的以弧度表示的角

ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角

ATAN2(float_expression1,float_expression2)

--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角

DEGREES(numeric_expression)

--把弧度轉換為角度返回與表達式相同的數據類型可為

RADIANS(numeric_expression) --把角度轉換為弧度返回與表達式相同的數據類型可為

--INTEGER/MONEY/REAL/FLOAT 類型

EXP(float_expression)　--返回表達式的指數值

LOG(float_expression)　--返回表達式的自然對數值

LOG10(float_expression)--返回表達式的以10 為底的對數值

SQRT(float_expression) --返回表達式的平方根

/***取近似值函式***/

CEILING(numeric_expression)　--返回>=表達式的最小整數返回的數據類型與表達式相同可為

--INTEGER/MONEY/REAL/FLOAT 類型

FLOOR(numeric_expression)　　--返回<=表達式的最小整數返回的數據類型與表達式相同可為

--INTEGER/MONEY/REAL/FLOAT 類型

ROUND(numeric_expression)　　--返回以integer_expression 為精度的四捨五入值返回的數據

--類型與表達式相同可為INTEGER/MONEY/REAL/FLOAT 類型

ABS(numeric_expression)--返回表達式的絕對值返回的數據類型與表達式相同可為

--INTEGER/MONEY/REAL/FLOAT 類型

SIGN(numeric_expression)　　 --測試參數的正負號返回0 零值1 正數或-1 負數返回的數據類型

--與表達式相同可為INTEGER/MONEY/REAL/FLOAT 類型

PI() --返回值為π 即3.1415926535897936

RAND()　 --用任選的做種子值得出0-1 間的隨機浮點數

----字元串函式----

ASCII()　 --函式返回字元表達式最左端字元的ASCII 碼值

CHAR()　 --函式用於將ASCII 碼轉換為字元

--如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函式會返回一個NULL 值

LOWER()　 --函式把字元串全部轉換為小寫

UPPER()　 --函式把字元串全部轉換為大寫

STR()　 --函式把數值型數據轉換為字元型數據

LTRIM()　 --函式把字元串頭部的空格去掉

RTRIM()　 --函式把字元串尾部的空格去掉

LEFT(),RIGHT(),SUBSTRING()　--函式返回部分字元串

CHARINDEX(),PATINDEX()　--函式返回字元串中某個指定的子串出現的開始位置

SOUNDEX()　--函式返回一個四位字元碼

--SOUNDEX函式可用來查找聲音相似的字元串但SOUNDEX函式對數字和漢字均只返回0 值　　

DIFFERENCE()　　--函式返回由SOUNDEX 函式返回的兩個字元表達式的值的差異

--0 兩個SOUNDEX 函式返回值的第一個字元不同

--1 兩個SOUNDEX 函式返回值的第一個字元相同

--2 兩個SOUNDEX 函式返回值的第一二個字元相同

--3 兩個SOUNDEX 函式返回值的第一二三個字元相同

--4 兩個SOUNDEX 函式返回值完全相同

QUOTENAME()　--函式返回被特定字元括起來的字元串

/*select quotename('abc', '{') quotename('abc')

運行結果如下

----------------------------------{

[abc]*/

REPLICATE()　　 --函式返回一個重複character_expression 指定次數的字元串

/*select replicate('abc', 3) replicate( 'abc', -2)

運行結果如下

----------- -----------

abcabcabc NULL*/

REVERSE() --函式將指定的字元串的字元排列順序顛倒

REPLACE() --函式返回被替換了指定子串的字元串

/*select replace('abc123g', '123', 'def')

運行結果如下

----------- -----------

abcdefg*/

SPACE()　 --函式返回一個有指定長度的空白字元串

STUFF()　 --函式用另一子串替換字元串指定位置長度的子串

----數據類型轉換函式----

CAST() 函式語法如下

CAST() (<expression> AS <data_ type>[ length ])

CONVERT() 函式語法如下

CONVERT() (<data_ type>[ length ], <expression> [, style])

select cast(100+99 as char) convert(varchar(12), getdate())

運行結果如下

------------------------------ ------------

199　 Jan 15 2000

----日期函式----

DAY()　 --函式返回date_expression 中的日期值

MONTH()　 --函式返回date_expression 中的月份值

YEAR()　 --函式返回date_expression 中的年份值

DATEADD(<datepart> ,<number> ,<date>)

--函式返回指定日期date 加上指定的額外日期間隔number 產生的新日期

DATEDIFF(<datepart> ,<number> ,<date>)

--函式返回兩個指定日期在datepart 方面的不同之處

DATENAME(<datepart> , <date>)　--函式以字元串的形式返回日期的指定部分

DATEPART(<datepart> , <date>)　--函式以整數值的形式返回日期的指定部分

GETDATE()　--函式以DATETIME 的預設格式返回系統當前的日期和時間

----系統函式----

APP_NAME()--函式返回當前執行的應用程式的名稱

COALESCE()　--函式返回眾多表達式中第一個非NULL 表達式的值

COL_LENGTH(<'table_name'>, <'column_name'>) --函式返回表中指定欄位的長度值

COL_NAME(<table_id>, <column_id>)　 --函式返回表中指定欄位的名稱即列名

DATALENGTH() --函式返回數據表達式的數據的實際長度

DB_ID(['database_name']) --函式返回資料庫的編號

DB_NAME(database_id)　--函式返回資料庫的名稱

HOST_ID()　　 --函式返回伺服器端計算機的名稱

HOST_NAME()　　 --函式返回伺服器端計算機的名稱

IDENTITY(<data_type>[, seed increment]) [AS column_name])

--IDENTITY() 函式只在SELECT INTO 語句中使用用於插入一個identity column列到新表中

/*select identity(int, 1, 1) as column_name

into newtable

from oldtable*/

ISDATE()　--函式判斷所給定的表達式是否為合理日期

ISNULL(<check_expression>, <replacement_value>) --函式將表達式中的NULL 值用指定值替換

ISNUMERIC()　--函式判斷所給定的表達式是否為合理的數值

NEWID()　 --函式返回一個UNIQUEIDENTIFIER 類型的數值

NULLIF(<expression1>, <expression2>)

--NULLIF 函式在expression1 與expression2 相等時返回NULL 值若不相等時則返回expression1 的值