萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server T-SQL 語法完整速查手冊:DML、DDL、DCL、TCL 與程式化控制

前言: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/DENYREVOKE 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% 的資料庫開發需求。建議將本手冊加入書籤,搭配 官方文件 深入細節,並養成「先想集合式解法,再考慮程式化流程」的思維習慣。

copyright © 萬盛學電腦網 all rights reserved