前言:T-SQL 是 SQL Server 的超集
Transact-SQL (T-SQL) 擴充 ANSI SQL-92 標準,增加程式化控制、錯誤處理、系統函數等企業級功能。本文將核心語法分類整理為速查手冊,涵蓋資料操作 (DML)、定義 (DDL)、控制 (DCL)、交易 (TCL)、程式化流程、變數、流程控制、統計/日期/系統函數,適合開發隨時查閱。
一、資料操作語言 (DML)
| 指令 | 用途 | 核心語法 |
SELECT | 查詢資料列/欄 | SELECT col1, col2 FROM table WHERE ... ORDER BY ... |
INSERT | 新增資料列 | INSERT INTO table (col1, col2) VALUES (val1, val2) |
DELETE | 刪除資料列 | DELETE FROM table WHERE ... |
UPDATE | 更新資料 | UPDATE table SET col1=val1 WHERE ... |
MERGE | 合併 (UPSERT) | MERGE target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... |
TRUNCATE TABLE | 快速清空表 (保留結構、重設 IDENTITY) | TRUNCATE TABLE table_name |
二、資料定義語言 (DDL)
| 指令 | 用途 | 核心語法 |
CREATE TABLE | 建立資料表 | CREATE TABLE t (id INT PRIMARY KEY, name NVARCHAR(50) NOT NULL, created DATETIME2 DEFAULT SYSUTCDATETIME()) |
DROP TABLE | 刪除資料表 | DROP TABLE [IF EXISTS] table_name |
ALTER TABLE | 修改表結構 | ADD col ..., DROP COLUMN col, ALTER COLUMN col TYPE, ADD CONSTRAINT ..., DROP CONSTRAINT ... |
CREATE VIEW | 建立檢視表 | CREATE VIEW v AS SELECT ... FROM ... WHERE ... |
DROP VIEW | 刪除檢視表 | DROP VIEW [IF EXISTS] view_name |
CREATE INDEX | 建立索引 | CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX ix_name ON table (col) INCLUDE (col2) WHERE ... |
DROP INDEX | 刪除索引 | DROP INDEX ix_name ON table_name |
CREATE PROCEDURE | 建立預存程序 | CREATE PROC usp_Name @p1 INT, @p2 NVARCHAR(50) AS BEGIN ... END |
DROP PROCEDURE | 刪除預存程序 | DROP PROC [IF EXISTS] proc_name |
CREATE TRIGGER | 建立觸發器 | CREATE TRIGGER tr_Name ON table AFTER INSERT, UPDATE AS BEGIN ... END |
DROP TRIGGER | 刪除觸發器 | DROP TRIGGER [IF EXISTS] trigger_name |
CREATE SCHEMA | 建立結構描述 | CREATE SCHEMA schema_name AUTHORIZATION owner |
DROP SCHEMA | 刪除結構描述 | DROP SCHEMA [IF EXISTS] schema_name |
CREATE TYPE | 建立使用者定義類型/表值類型 | CREATE TYPE dbo.UDT FROM NVARCHAR(50) NOT NULL |
三、資料控制語言 (DCL)
| 指令 | 用途 | 範例 |
GRANT | 授予權限 | GRANT SELECT, INSERT ON schema.table TO user_or_role |
DENY | 明確拒絕權限 (優先於 GRANT) | DENY DELETE ON schema.table TO user_or_role |
REVOKE | 移除 GRANT/DENY | REVOKE SELECT ON schema.table FROM user_or_role |
EXECUTE AS | 切換執行內容 | EXECUTE AS USER = 'app_user'; ... REVERT; |
四、交易控制語言 (TCL)
| 指令 | 用途 | 關鍵點 |
BEGIN TRANSACTION | 啟動交易 | 可命名、標記點 |
COMMIT | 認可交易 | 持久化所有變更 |
ROLLBACK | 回滾交易 | 撤銷所有變更,釋放鎖 |
SAVE TRANSACTION | 設定儲存點 | ROLLBACK TO savepoint 部分回滾 |
SET XACT_ABORT ON | 執行期錯誤自動回滾 | 強烈建議預存程序開啟 |
SET IMPLICIT_TRANSACTIONS | 隱含交易模式 | ANSI 模式,通常關閉 |
五、程式化流程控制
變數宣告與賦值
-- 區域變數
DECLARE @id CHAR(10), @cnt INT = 0;
SET @id = '10010001'; -- 標準賦值
SELECT @id = '10010001'; -- 亦可用 SELECT 賦值 (單行結果)
-- 全域變數 (系統定義,唯讀多數)
SELECT @@VERSION, @@SERVERNAME, @@SPID, @@ROWCOUNT, @@ERROR, @@IDENTITY, @@TRANCOUNT;
條件控制:IF...ELSE / CASE
-- IF ELSE
DECLARE @x INT = 1, @y INT = 2, @z INT = 3;
IF @x > @y
PRINT 'x > y';
ELSE IF @y > @z
PRINT 'y > z';
ELSE
PRINT 'z >= y';
-- CASE 運算式 (回傳值)
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;
-- CASE 陳述式 (控制流程) - 僅在程式區塊可用
DECLARE @grade CHAR(1) = 'B';
CASE @grade
WHEN 'A' THEN PRINT '優'
WHEN 'B' THEN PRINT '良'
ELSE PRINT '其他'
END;
迴圈控制:WHILE / CONTINUE / BREAK
DECLARE @i INT = 1, @sum INT = 0;
WHILE @i <= 100
BEGIN
IF @i % 2 = 0
BEGIN
SET @i += 1;
CONTINUE; -- 跳過偶數
END
SET @sum += @i;
SET @i += 1;
IF @sum > 1000 BREAK; -- 提早結束
END
PRINT @sum; -- 2500 (前 31 個奇數和超過 1000)
等待控制:WAITFOR
-- 延遲指定時間
WAITFOR DELAY '00:02:03'; -- 等待 2 分 3 秒
-- 等待至指定時間
WAITFOR TIME '23:08:00'; -- 等待至今晚 23:08
-- 等待訊息/通知 (Service Broker)
WAITFOR (RECEIVE TOP(1) * FROM queue);
游標操作 (效能敏感,慎用)
DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT id, name FROM customers WHERE region = 'TW';
OPEN cur;
FETCH NEXT FROM cur INTO @id, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 處理邏輯
FETCH NEXT FROM cur INTO @id, @name;
END
CLOSE cur;
DEALLOCATE cur;
動態 SQL
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@table) + N' WHERE id = @id';
EXEC sp_executesql @sql, N'@id INT', @id = 123; -- 參數化,防注入
六、核心查詢進階語法
JOIN 類型
-- 內連接
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
-- 左外連接 (左表全保留)
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
-- 右外連接
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;
-- 全外連接
SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id;
-- 交叉連接 (笛卡兒積)
SELECT * FROM t1 CROSS JOIN t2;
-- 舊版外連接語法 (已棄用,勿用)
-- t1.id *= t2.id (左外連接)
-- t1.id =* t2.id (右外連接)
集合運算
SELECT col FROM t1 UNION SELECT col FROM t2; -- 去重
SELECT col FROM t1 UNION ALL SELECT col FROM t2; -- 保留重複
SELECT col FROM t1 INTERSECT SELECT col FROM t2; -- 交集
SELECT col FROM t1 EXCEPT SELECT col FROM t2; -- 差集
子查詢與 APPLY
-- 相關子查詢
SELECT * FROM orders o
WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE cust_id = o.cust_id);
-- CROSS APPLY (類似 INNER JOIN 但可呼叫表值函數)
SELECT c.*, ca.top_order
FROM customers c
CROSS APPLY (
SELECT TOP 1 * FROM orders o WHERE o.cust_id = c.id ORDER BY order_date DESC
) ca;
-- OUTER APPLY (類似 LEFT JOIN)
SELECT c.*, ca.top_order
FROM customers c
OUTER APPLY (
SELECT TOP 1 * FROM orders o WHERE o.cust_id = c.id ORDER BY order_date DESC
) ca;
窗格函數 (OLAP)
SELECT
dept_id,
e_name,
e_wage,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY e_wage DESC) AS rn,
RANK() OVER (PARTITION BY dept_id ORDER BY e_wage DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY e_wage DESC) AS drk,
NTILE(4) OVER (PARTITION BY dept_id ORDER BY e_wage DESC) AS quartile,
LAG(e_wage) OVER (PARTITION BY dept_id ORDER BY e_wage) AS prev_wage,
LEAD(e_wage) OVER (PARTITION BY dept_id ORDER BY e_wage) AS next_wage,
AVG(e_wage) OVER (PARTITION BY dept_id) AS dept_avg
FROM employee;
七、常用函數速查
統計彙總函數
| 函數 | 說明 | 範例 |
AVG(expr) | 平均值 | AVG(e_wage) |
COUNT(*) / COUNT(col) | 計數 / 非NULL計數 | COUNT(DISTINCT dept_id) |
MAX/MIN(expr) | 最大/最小值 | MAX(e_wage) |
SUM(expr) | 加總 | SUM(sales_amt) |
STDEV / VAR | 標準差 / 變異數 | 樣本估計 |
STRING_AGG(expr, sep) | 字串串聯 (2017+) | STRING_AGG(name, ',') |
日期時間函數 (建議使用 DATETIME2 / DATE / TIME)
| 函數 | 說明 | 範例 |
GETDATE() / SYSDATETIME() | 目前日期時間 (低/高精度) | SYSDATETIME() |
SYSUTCDATETIME() | UTC 高精度時間 | 跨時區儲存推薦 |
DATEADD(interval, number, date) | 日期加減 | DATEADD(DAY, -30, GETDATE()) |
DATEDIFF(interval, start, end) | 日期差值 | DATEDIFF(DAY, start_dt, end_dt) |
DATENAME(interval, date) | 日期部分名稱 (字串) | DATENAME(MONTH, GETDATE()) → 'March' |
DATEPART(interval, date) | 日期部分數值 (整數) | DATEPART(WEEKDAY, GETDATE()) |
DAY/MONTH/YEAR(date) | 日/月/年數值 | YEAR(GETDATE()) |
EOMONTH(date, offset) | 月份最後一天 (2012+) | EOMONTH(GETDATE(), 1) -- 下月月底 |
FORMAT(date, format, culture) | 格式化 (CLR 依賴,較慢) | FORMAT(GETDATE(), 'yyyy-MM-dd', 'zh-TW') |
DATETIME2FROMPARTS | 從組件建構 datetime2 | 避免字串轉換 |
字串函數
| 函數 | 說明 | 範例 |
LEN / DATALENGTH | 字元數 / 位元組數 | DATALENGTH(N'abc') = 6 |
SUBSTRING(str, start, len) | 子字串 (1-based) | SUBSTRING('abcdef', 2, 3) → 'bcd' |
CHARINDEX / PATINDEX | 尋找字串/模式位置 | CHARINDEX('@', email) |
REPLACE(str, old, new) | 取代 | REPLACE(path, '\', '/') |
LEFT/RIGHT(str, len) | 左/右擷取 | RIGHT('abc123', 3) → '123' |
LTRIM/RTRIM/TRIM | 去除空白 (2017+ TRIM) | TRIM(' abc ') |
UPPER/LOWER | 大小寫轉換 | UPPER(name) |
CONCAT / CONCAT_WS | 串聯 (自動處理 NULL) | CONCAT_WS(' ', first, last) |
QUOTENAME | 加括號並逸出 | QUOTENAME('table]name') → '[table]]name]' |
系統/中繼資料函數
| 函數 | 說明 |
DB_ID() / DB_NAME() | 資料庫 ID/名稱 |
OBJECT_ID() / OBJECT_NAME() | 物件 ID/名稱 |
COL_NAME() / COL_LENGTH() | 欄位名稱/長度 |
INDEXPROPERTY / INDEX_COL | 索引屬性/欄位 |
FILE_ID / FILE_NAME / FILEGROUPPROPERTY | 檔案/檔案群組屬性 |
HOST_ID() / HOST_NAME() / APP_NAME() | 用戶端資訊 |
ISNULL(expr, replacement) | NULL 替換 (非標準,建議用 COALESCE) |
COALESCE(expr1, expr2, ...) | 回傳第一個非 NULL (標準) |
NULLIF(expr1, expr2) | 相等回傳 NULL |
IIF(condition, true_val, false_val) | 三元運算子 (2012+) |
CHOOSE(index, val1, val2, ...) | 索引選值 (2012+) |
NEWID() / NEWSEQUENTIALID() | 產生 GUID (後者有序,利於索引) |
TRY_CAST / TRY_CONVERT / TRY_PARSE | 安全轉換,失敗回傳 NULL |
八、實用查詢模式
分頁查詢 (2012+ OFFSET/FETCH)
SELECT * FROM products
ORDER BY product_id
OFFSET @pageSize * (@pageNum - 1) ROWS
FETCH NEXT @pageSize ROWS ONLY;
刪除重複資料 (保留最早一筆)
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY key_cols ORDER BY id) AS rn
FROM dup_table
)
DELETE FROM cte WHERE rn > 1;
遞迴 CTE (階層/樹狀查詢)
WITH cte AS (
SELECT id, parent_id, name, 0 AS lvl FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name, p.lvl + 1
FROM categories c JOIN cte p ON c.parent_id = p.id
)
SELECT * FROM cte OPTION (MAXRECURSION 100);
動態 PIVOT / UNPIVOT
-- 靜態 PIVOT
SELECT * FROM (
SELECT year, quarter, sales FROM yearly_sales
) src
PIVOT (SUM(sales) FOR quarter IN ([Q1],[Q2],[Q3],[Q4])) pvt;
結語
T-SQL 功能極其豐富,掌握核心語法分類、常用函數、進階查詢模式,能讓你高效解決 95% 的資料庫開發需求。建議將本手冊加入書籤,搭配 官方文件 深入細節,並養成「先想集合式解法,再考慮程式化流程」的思維習慣。