Transact_SQL
小手冊
1
語 句 功 能
1.1
數(shù)據(jù)操作
SELECT
--
從數(shù)據(jù)庫表中檢索數(shù)據(jù)行和列
INSERT
--
向數(shù)據(jù)庫表添加新數(shù)據(jù)行
DELETE
--
從數(shù)據(jù)庫表中刪除數(shù)據(jù)行
UPDATE
--
更新數(shù)據(jù)庫表中的數(shù)據(jù)
--------------------------------------------------------------------------
1.2
數(shù)據(jù)定義
CREATE TABLE
------
創(chuàng)建一個數(shù)據(jù)庫表
DROP TABLE
--------
從數(shù)據(jù)庫中刪除表
ALTER TABLE
-------
修改數(shù)據(jù)庫表結(jié)構(gòu)
CREATE VIEW
-------
創(chuàng)建一個視圖
DROP VIEW
---------
從數(shù)據(jù)庫中刪除視圖
CREATE INDEX
------
為數(shù)據(jù)庫表創(chuàng)建一個索引
DROP INDEX
--------
從數(shù)據(jù)庫中刪除索引
CREATE PROCEDURE
--
創(chuàng)建一個存儲過程
DROP PROCEDURE
----
從數(shù)據(jù)庫中刪除存儲過程
CREATE TRIGGER
----
創(chuàng)建一個觸發(fā)器
DROP TRIGGER
------
從數(shù)據(jù)庫中刪除觸發(fā)器
CREATE SCHEMA
-----
向數(shù)據(jù)庫添加一個新模式
DROP SCHEMA
-------
從數(shù)據(jù)庫中刪除一個模式
CREATE DOMAIN
-----
創(chuàng)建一個數(shù)據(jù)值域
ALTER DOMAIN
------
改變域定義
DROP DOMAIN
-------
從數(shù)據(jù)庫中刪除一個域
--------------------------------------------------------------------------
1.3
數(shù)據(jù)控制
GRANT
-------------
授予用戶訪問權(quán)限
DENY
--------------
拒絕用戶訪問
REVOKE
------------
解除用戶訪問權(quán)限
--------------------------------------------------------------------------
1.4
事務(wù)控制
COMMIT
------------
結(jié)束當(dāng)前事務(wù)
ROLLBACK
----------
中止當(dāng)前事務(wù)
SET TRANSACTION
---
定義當(dāng)前事務(wù)數(shù)據(jù)訪問特征
--------------------------------------------------------------------------
1.5
程序化SQL
DECLARE
-----------
為查詢設(shè)定游標(biāo)
EXPLAN
------------
為查詢描述數(shù)據(jù)訪問計劃
OPEN
--------------
檢索查詢結(jié)果打開一個游標(biāo)
FETCH
-------------
檢索一行查詢結(jié)果
CLOSE
-------------
關(guān)閉游標(biāo)
PREPARE
-----------
為動態(tài)執(zhí)行準(zhǔn)備SQL 語句
EXECUTE
-----------
動態(tài)地執(zhí)行SQL 語句
DESCRIBE
----------
描述準(zhǔn)備好的查詢
--------------------------------------------------------------------------
1.6
局部變量
declare @id char(10)
--set @id = '10010001'
select @id = '10010001'
--------------------------------------------------------------------------
1.7
全局變量
---
必須以@開頭
1.7.1
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'
1.7.2
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
1.7.3
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
1.7.4
WAITFOR
--
例 等待1 小時2 分零3 秒后才執(zhí)行SELECT 語句
waitfor delay ’01:02:03’
select * from employee
--
例 等到晚上11 點零8 分后才執(zhí)行SELECT 語句
waitfor time ’23:08:00’
select * from employee
--------------------------------------------------------------------------
2
語 句 定 義 規(guī) 則
2.1 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關(guān)鍵字的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)
---------
子查詢
---------
除非能確保內(nèi)層select只返回一個行的值，
---------
否則應(yīng)在外層where子句中用一個in限定符
select distinct column_name form table_name --------- distinct
指定檢索獨有的列值，不重復(fù)
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
合并查詢結(jié)果集，all-保留重復(fù)行
select stockname from table2
--------------------------------------------------------------------------
2.2 insert
insert into table_name (Stock_name,Stock_number) value
--- ("xxx","xxxx")
--- (select Stockname , Stocknumber from Stock_table2)---value
為select語句
--------------------------------------------------------------------------
2.3 update
update table_name set
--- Stockname = "xxx" [where Stockid = 3]
--- Stockname = default
--- Stockname = null
--- Stocknumber = Stockname + 4
--------------------------------------------------------------------------
2.4 delete
delete from table_name where Stockid = 3
truncate table_name
----------
刪除表中所有行，仍保持表的完整性
drop table table_name
--------
完全刪除表
--------------------------------------------------------------------------
2.5 alter
、sp_help、create和insert
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 ......... -----
實現(xiàn)刪除列的方法（創(chuàng)建新表）
alter table table_name drop constraint Stockname_default ----
刪除Stockname的default約束
--------------------------------------------------------------------------
2.6 function(/*
常用函數(shù)*/)
2.6.1
統(tǒng)計函數(shù)
AVG
--
求平均值
COUNT
--
統(tǒng)計數(shù)目
MAX
--
求最大值
MIN
--
求最小值
SUM
--
求和
2.6.1
.1 AVG
use pangu
select avg(e_wage) as dept_avgWage from employee group by dept_id
2.6.1
.2 MAX
--
求工資最高的員工姓名
use pangu
select e_name from employee where e_wage = (select max(e_wage) from employee)
2.4.1
.3 STDEV()
--STDEV()
函數(shù)返回表達式中所有數(shù)據(jù)的標(biāo)準(zhǔn)差
2.4.1
.4 STDEVP()
--STDEVP()
函數(shù)返回總體標(biāo)準(zhǔn)差
2.4.1
.5 VAR()
--VAR()
函數(shù)返回表達式中所有值的統(tǒng)計變異數(shù)
2.4.1
.6 VARP()
--VARP()
函數(shù)返回總體變異數(shù)
2.6.2
算術(shù)函數(shù)
2.6.2
.1
三角函數(shù)
SIN(float_expression)
--
返回以弧度表示的角的正弦
COS(float_expression)
--
返回以弧度表示的角的余弦
TAN(float_expression)
--
返回以弧度表示的角的正切
COT(float_expression)
--
返回以弧度表示的角的余切
2.6.2
.2
反三角函數(shù)
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)
--
把弧度轉(zhuǎn)換為角度返回與表達式相同的數(shù)據(jù)類型可為（INTEGER/MONEY/REAL/FLOAT 類型）
RADIANS(numeric_expression)
--
把角度轉(zhuǎn)換為弧度返回與表達式相同的數(shù)據(jù)類型可為（INTEGER/MONEY/REAL/FLOAT 類型）
EXP(float_expression)
--
返回表達式的指數(shù)值
LOG(float_expression)
--
返回表達式的自然對數(shù)值
LOG10(float_expression)
--
返回表達式的以10 為底的對數(shù)值
SQRT(float_expression)
--
返回表達式的平方根
2.6.2
.3
取近似值函數(shù)
CEILING(numeric_expression)
--
返回>=表達式的最小整數(shù)返回的數(shù)據(jù)類型與表達式相同可為（INTEGER/MONEY/REAL/FLOAT 類型）
FLOOR(numeric_expression)
--
返回<=表達式的最小整數(shù)返回的數(shù)據(jù)類型與表達式相同可為（INTEGER/MONEY/REAL/FLOAT 類型）
ROUND(numeric_expression)
--
返回以integer_expression 為精度的四舍五入值返回的數(shù)據(jù)（類型與表達式相同可為INTEGER/MONEY/REAL/FLOAT 類型）
ABS(numeric_expression)
--
返回表達式的絕對值返回的數(shù)據(jù)類型與表達式相同可為（INTEGER/MONEY/REAL/FLOAT 類型）
SIGN(numeric_expression)
--
測試參數(shù)的正負(fù)號返回0 零值1 正數(shù)或-1 負(fù)數(shù)返回的數(shù)據(jù)類型（與表達式相同可為INTEGER/MONEY/REAL/FLOAT 類型）
PI()
--
返回值為π 即3.1415926535897936
RAND([integer_expression])
--
用任選的[integer_expression]做種子值得出0-1 間的隨機浮點數(shù)
2.6.3
字符串函數(shù)
ASCII()
--
函數(shù)返回字符表達式最左端字符的ASCII 碼值
CHAR()
---
函數(shù)用于將ASCII 碼轉(zhuǎn)換為字符（如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函數(shù)會返回一個NULL 值）
LOWER()
--
函數(shù)把字符串全部轉(zhuǎn)換為小寫
UPPER()
--
函數(shù)把字符串全部轉(zhuǎn)換為大寫
STR()
----
函數(shù)把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)
LTRIM()
--
函數(shù)把字符串頭部的空格去掉
RTRIM()
--
函數(shù)把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING()
--
函數(shù)返回部分字符串
CHARINDEX(),PATINDEX()
--
函數(shù)返回字符串中某個指定的子串出現(xiàn)的開始位置
SOUNDEX()
--
函數(shù)返回一個四位字符碼（SOUNDEX函數(shù)可用來查找聲音相似的字符串但SOUNDEX函數(shù)對數(shù)字和漢字均只返回0 值）
DIFFERENCE()
--
函數(shù)返回由SOUNDEX 函數(shù)返回的兩個字符表達式的值的差異
--0
兩個SOUNDEX 函數(shù)返回值的第一個字符不同
--1
兩個SOUNDEX 函數(shù)返回值的第一個字符相同
--2
兩個SOUNDEX 函數(shù)返回值的第一二個字符相同
--3
兩個SOUNDEX 函數(shù)返回值的第一二三個字符相同
--4
兩個SOUNDEX 函數(shù)返回值完全相同
QUOTENAME()
--
函數(shù)返回被特定字符括起來的字符串
/*select quotename('abc', '{') quotename('abc')
運行結(jié)果如下
----------- -----------
{abc} [abc]*/
REPLICATE() --
函數(shù)返回一個重復(fù)character_expression 指定次數(shù)的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
運行結(jié)果如下
----------- -----------
abcabcabc NULL*/
REVERSE()
--
函數(shù)將指定的字符串的字符排列順序顛倒
REPLACE()
--
函數(shù)返回被替換了指定子串的字符串
/*select replace('abc123g', '123', 'def')
運行結(jié)果如下
----------- -----------
abcdefg*/
SPACE() --
函數(shù)返回一個有指定長度的空白字符串
STUFF() --
函數(shù)用另一子串替換字符串指定位置長度的子串
2.6.4
數(shù)據(jù)類型轉(zhuǎn)換函數(shù)
CAST()
函數(shù)語法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT()
函數(shù)語法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])
/*select cast(100+99 as char) convert(varchar(12), getdate())
運行結(jié)果如下
----------- -----------
199 Jan 15 2000*/
2.6.5
日期函數(shù)
DAY()
--
函數(shù)返回date_expression 中的日期值
MONTH()
--
函數(shù)返回date_expression 中的月份值
YEAR()
--
函數(shù)返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
--
函數(shù)返回指定日期date 加上指定的額外日期間隔number 產(chǎn)生的新日期
DATE
DIFF(<datepart> ,<number> ,<date>)
--
函數(shù)返回兩個指定日期在datepart 方面的不同之處
DATENAME(<datepart> , <date>)
--
函數(shù)以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>)
--
函數(shù)以整數(shù)值的形式返回日期的指定部分
GETDATE()
--
函數(shù)以DATETIME 的缺省格式返回系統(tǒng)當(dāng)前的日期和時間
2.6.6
系統(tǒng)函數(shù)
APP_NAME()
--
函數(shù)返回當(dāng)前執(zhí)行的應(yīng)用程序的名稱
COALESCE()
--
函數(shù)返回眾多表達式中第一個非NULL 表達式的值
COL_LENGTH(<'table_name'>, <'column_name'>)
--
函數(shù)返回表中指定字段的長度值
COL_NAME(<table_id>, <column_id>)
--
函數(shù)返回表中指定字段的名稱即列名
DATALENGTH()
--
函數(shù)返回數(shù)據(jù)表達式的數(shù)據(jù)的實際長度
DB_ID(['database_name'])
--
函數(shù)返回數(shù)據(jù)庫的編號
DB_NAME(database_id)
--
函數(shù)返回數(shù)據(jù)庫的名稱
HOST_ID()
--
函數(shù)返回服務(wù)器端計算機的名稱
HOST_NAME()
--
函數(shù)返回服務(wù)器端計算機的名稱
IDENTITY(<data_type>[, seed increment]) [AS column_name])
--IDENTITY()
函數(shù)只在SELECT INTO 語句中使用用于插入一個identity column列到新表中
/*select identity(int, 1, 1) as column_name into newtable from oldtable*/
ISDATE()
--
函數(shù)判斷所給定的表達式是否為合理日期
ISNULL(<check_expression>, <replacement_value>)
--
函數(shù)將表達式中的NULL 值用指定值替換
ISNUMERIC()
--
函數(shù)判斷所給定的表達式是否為合理的數(shù)值
NEWID()
--
函數(shù)返回一個UNIQUEIDENTIFIER 類型的數(shù)值
NULLIF(<expression1>, <expression2>)
--
NULLIF
函數(shù)在
expression1
與
expression2
相等時返回
NULL
值若不相等時則返回
expression1
的值