Transact_SQL
小手冊
1
語 句 功 能
1.1
數據操作
SELECT
--
從數據庫表中檢索數據行和列
INSERT
--
向數據庫表添加新數據行
DELETE
--
從數據庫表中刪除數據行
UPDATE
--
更新數據庫表中的數據
--------------------------------------------------------------------------
1.2
數據定義
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
-------
從數據庫中刪除一個域
--------------------------------------------------------------------------
1.3
數據控制
GRANT
-------------
授予用戶訪問權限
DENY
--------------
拒絕用戶訪問
REVOKE
------------
解除用戶訪問權限
--------------------------------------------------------------------------
1.4
事務控制
COMMIT
------------
結束當前事務
ROLLBACK
----------
中止當前事務
SET TRANSACTION
---
定義當前事務數據訪問特征
--------------------------------------------------------------------------
1.5
程序化SQL
DECLARE
-----------
為查詢設定游標
EXPLAN
------------
為查詢描述數據訪問計劃
OPEN
--------------
檢索查詢結果打開一個游標
FETCH
-------------
檢索一行查詢結果
CLOSE
-------------
關閉游標
PREPARE
-----------
為動態執行準備SQL 語句
EXECUTE
-----------
動態地執行SQL 語句
DESCRIBE
----------
描述準備好的查詢
--------------------------------------------------------------------------
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 秒后才執行SELECT 語句
waitfor delay ’01:02:03’
select * from employee
--
例 等到晚上11 點零8 分后才執行SELECT 語句
waitfor time ’23:08:00’
select * from employee
--------------------------------------------------------------------------
2
語 句 定 義 規 則
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關鍵字的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
--------------------------------------------------------------------------
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 ......... -----
實現刪除列的方法（創建新表）
alter table table_name drop constraint Stockname_default ----
刪除Stockname的default約束
--------------------------------------------------------------------------
2.6 function(/*
常用函數*/)
2.6.1
統計函數
AVG
--
求平均值
COUNT
--
統計數目
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()
函數返回表達式中所有數據的標準差
2.4.1
.4 STDEVP()
--STDEVP()
函數返回總體標準差
2.4.1
.5 VAR()
--VAR()
函數返回表達式中所有值的統計變異數
2.4.1
.6 VARP()
--VARP()
函數返回總體變異數
2.6.2
算術函數
2.6.2
.1
三角函數
SIN(float_expression)
--
返回以弧度表示的角的正弦
COS(float_expression)
--
返回以弧度表示的角的余弦
TAN(float_expression)
--
返回以弧度表示的角的正切
COT(float_expression)
--
返回以弧度表示的角的余切
2.6.2
.2
反三角函數
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)
--
把角度轉換為弧度返回與表達式相同的數據類型可為（INTEGER/MONEY/REAL/FLOAT 類型）
EXP(float_expression)
--
返回表達式的指數值
LOG(float_expression)
--
返回表達式的自然對數值
LOG10(float_expression)
--
返回表達式的以10 為底的對數值
SQRT(float_expression)
--
返回表達式的平方根
2.6.2
.3
取近似值函數
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([integer_expression])
--
用任選的[integer_expression]做種子值得出0-1 間的隨機浮點數
2.6.3
字符串函數
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} [abc]*/
REPLICATE() --
函數返回一個重復character_expression 指定次數的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
運行結果如下
----------- -----------
abcabcabc NULL*/
REVERSE()
--
函數將指定的字符串的字符排列順序顛倒
REPLACE()
--
函數返回被替換了指定子串的字符串
/*select replace('abc123g', '123', 'def')
運行結果如下
----------- -----------
abcdefg*/
SPACE() --
函數返回一個有指定長度的空白字符串
STUFF() --
函數用另一子串替換字符串指定位置長度的子串
2.6.4
數據類型轉換函數
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*/
2.6.5
日期函數
DAY()
--
函數返回date_expression 中的日期值
MONTH()
--
函數返回date_expression 中的月份值
YEAR()
--
函數返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
--
函數返回指定日期date 加上指定的額外日期間隔number 產生的新日期
DATE
DIFF(<datepart> ,<number> ,<date>)
--
函數返回兩個指定日期在datepart 方面的不同之處
DATENAME(<datepart> , <date>)
--
函數以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>)
--
函數以整數值的形式返回日期的指定部分
GETDATE()
--
函數以DATETIME 的缺省格式返回系統當前的日期和時間
2.6.6
系統函數
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
的值