| 類別 | 關鍵字 | 功能 |
|---|---|---|
| 資料操作 (DML) | SELECT | 從資料表擷取列與行 |
| INSERT | 新增資料列 | |
| DELETE | 刪除資料列 | |
| UPDATE | 修改現有資料 | |
| 資料定義 (DDL) | CREATE TABLE | 建立資料表 |
| DROP TABLE | 刪除資料表 | |
| ALTER TABLE | 修改資料表結構 | |
| CREATE VIEW | 建立檢視 | |
| DROP VIEW | 刪除檢視 | |
| CREATE INDEX | 建立索引 | |
| DROP INDEX | 刪除索引 | |
| CREATE PROCEDURE | 建立預存程序 | |
| DROP PROCEDURE | 刪除預存程序 | |
| CREATE TRIGGER | 建立觸發器 | |
| DROP TRIGGER | 刪除觸發器 | |
| 資料控制 (DCL) | GRANT | 授與權限 |
| DENY | 拒絕權限 | |
| REVOKE | 收回權限 | |
| 交易控制 | COMMIT | 認可交易 |
| ROLLBACK | 回滾交易 | |
| SET TRANSACTION | 設定交易隔離等級等特性 | |
| 程序化 SQL | DECLARE CURSOR | 宣告游標 |
| OPEN | 開啟游標 | |
| FETCH | 擷取游標列 | |
| CLOSE | 關閉游標 | |
| PREPARE | 準備動態 SQL | |
| EXECUTE | 執行動態 SQL | |
| DESCRIBE | 描述準備好的查詢 |
-- 區域變數:以 @ 開頭
DECLARE @id CHAR(10)
SET @id = '10010001'
-- 或
SELECT @id = '10010001'
-- 全域變數:以 @@ 開頭 (系統預定義,如 @@ROWCOUNT, @@ERROR, @@IDENTITY)
SELECT @@VERSION
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'
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
DECLARE @x INT = 1, @y INT = 1, @c INT
WHILE @x < 3
BEGIN
PRINT @x
SET @y = 1
WHILE @y < 3
BEGIN
SET @c = 100 * @x + @y
PRINT @c
SET @y = @y + 1
END
SET @x = @x + 1
END
-- 等待 1 小時 2 分 3 秒
WAITFOR DELAY '01:02:03'
SELECT * FROM employee
-- 等待至指定時刻 (23:08)
WAITFOR TIME '23:08:00'
SELECT * FROM employee
WHERE col = 'value'
WHERE col LIKE '%pattern%' -- 萬用字元 % _
WHERE col LIKE '[a-z]%' -- 字元範圍
WHERE col LIKE '[^A-F]%' -- 排除範圍
WHERE col BETWEEN 10 AND 100
WHERE col IN (10, 20, 30)
WHERE col IS NULL / IS NOT NULL
ORDER BY col1 DESC, col2 ASC
ORDER BY 1, 2 -- 依選取清單第 1、2 欄位排序
WHERE col = (SELECT col FROM tbl WHERE ...) -- 單值
WHERE col IN (SELECT col FROM tbl WHERE ...) -- 多值
SELECT col1, COUNT(*) AS cnt
FROM tbl
GROUP BY col1
HAVING COUNT(*) > 1
-- 內部連接
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 col FROM t1
UNION -- 合併並去重
SELECT col FROM t2
SELECT col FROM t1
UNION ALL -- 合併保留重複
SELECT col FROM t2
-- INSERT
INSERT INTO tbl (col1, col2) VALUES ('a', 1)
INSERT INTO tbl (col1, col2) SELECT col1, col2 FROM tbl2
-- UPDATE
UPDATE tbl SET col1 = 'new' WHERE id = 3
UPDATE tbl SET col1 = DEFAULT, col2 = NULL
-- DELETE
DELETE FROM tbl WHERE id = 3
本指南涵蓋 T-SQL 核心語法,適合開發人員快速查閱。完整規格請參考 Microsoft 官方文件。