Transact_SQL語句大全

版權聲明：本文為部落客原創文章，未經部落客允許不得轉載。 https://blog.csdn.net/chinahuyong/article/details/3552234

Transact_SQL

--語 句 功 能<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

--資料操作

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 = ’<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />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

select @c = 100*@x + @y

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

select @y = @y + 1

select @x = @x + 1

select @y = 1

--WAITFOR

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

waitfor delay ’01:02:03’

select * from employee

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

waitfor time ’23:08:00’

***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

select avg(e_wage) as dept_avgWage

from employee

group by dept_id

--MAX

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

select e_name

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)

--把弧度轉換為角度傳回與表達式相同的資料類型可為

--INTEGER/MONEY/REAL/FLOAT 類型

RADIANS(numeric_expression) --把角度轉換為弧度傳回與表達式相同的資料類型可為

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

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

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

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

/***取近似值函數***/

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

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

ROUND(numeric_expression) --傳回以integer_expression 為精度的四舍五入值傳回的資料

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

ABS(numeric_expression) --傳回表達式的絕對值傳回的資料類型與表達式相同可為

SIGN(numeric_expression) --測試參數的正負号傳回0 零值1 正數或-1 負數傳回的資料類型

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

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

RAND([integer_expression]) --用任選的[integer_expression]做種子值得出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 的值