SQL Server (T-SQL) 核心語法速查手冊,涵蓋三大語言類別、物件管理、進階查詢運算子、外連接、實用技巧 (分頁、去重、條件彙總、隨機抽樣)。
| 類別 | 指令 |
|---|---|
| DDL | CREATE, ALTER, DROP, DECLARE |
| DML | SELECT, INSERT, UPDATE, DELETE |
| DCL | GRANT, REVOKE, COMMIT, ROLLBACK |
-- 資料庫
CREATE DATABASE dbname;
DROP DATABASE dbname;
-- 資料表
CREATE TABLE tabname (
col1 type1 [NOT NULL] [PRIMARY KEY],
col2 type2 [NOT NULL], ...
);
-- 依現有表建表 (結構)
SELECT * INTO tab_new FROM tab_old WHERE 1=0; -- 空表
SELECT TOP 0 * INTO tab_new FROM tab_old; -- 同效
-- 刪表
DROP TABLE tabname;
-- 欄位增刪改
ALTER TABLE tabname ADD col type;
-- 注: SQL Server 不支援 DROP COLUMN 直接刪除 (需重建表)
-- 主鍵
ALTER TABLE tabname ADD PRIMARY KEY (col);
ALTER TABLE tabname DROP CONSTRAINT pk_name;
-- 索引
CREATE [UNIQUE] INDEX idxname ON tabname(col...);
DROP INDEX tabname.idxname; -- SQL Server 語法
-- 視圖
CREATE VIEW viewname AS SELECT ...;
DROP VIEW viewname;
SELECT * FROM table1 WHERE range;
INSERT INTO table1(f1,f2) VALUES(v1,v2);
DELETE FROM table1 WHERE range;
UPDATE table1 SET f1=v1 WHERE range;
SELECT * FROM table1 WHERE f1 LIKE '%value%'; -- 模糊查詢
SELECT * FROM table1 ORDER BY f1, f2 [DESC];
SELECT COUNT(*) AS total FROM table1;
SELECT SUM(f1), AVG(f1), MAX(f1), MIN(f1) FROM table1;
| 運算子 | 說明 | 去重 |
|---|---|---|
| UNION | 合併結果 | 是 |
| UNION ALL | 合併結果 | 否 |
| EXCEPT | 差集 (左有右無) | 是 |
| INTERSECT | 交集 | 是 |
參與查詢欄位數、類型、順序必須一致。
-- LEFT JOIN: 左表全保留
SELECT a.a, a.b, a.c, b.c, b.d, b.f
FROM a LEFT OUTER JOIN b ON a.a = b.c;
-- RIGHT JOIN: 右表全保留
SELECT ... FROM a RIGHT OUTER JOIN b ON a.a = b.c;
-- FULL JOIN: 雙表全保留
SELECT ... FROM a FULL OUTER JOIN b ON a.a = b.c;
SELECT a,b,c FROM a WHERE a IN (SELECT d FROM b);
SELECT a,b,c FROM a WHERE a IN (1,2,3);
SELECT a.title, a.username, b.adddate
FROM table a,
(SELECT MAX(adddate) adddate FROM table WHERE table.title = a.title) b;
SELECT * FROM (SELECT a,b,c FROM a) T WHERE t.a > 1;
SELECT * FROM t WHERE time BETWEEN t1 AND t2; -- 含邊界
SELECT * FROM t WHERE a NOT BETWEEN 1 AND 10;
SELECT * FROM t WHERE a IN ('v1','v2','v4');
DELETE FROM table1
WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.f1 = table2.f1);
SELECT * FROM a
LEFT JOIN b ON a.a = b.b
RIGHT JOIN c ON a.a = c.c
INNER JOIN d ON a.a = d.d WHERE ...;
SELECT * FROM schedule
WHERE DATEDIFF(minute, start_time, GETDATE()) > 5;
SELECT TOP 10 b.*
FROM (SELECT TOP 20 pk, sort_col FROM tbl ORDER BY sort_col DESC) a
JOIN tbl b ON b.pk = a.pk
ORDER BY a.sort_col;
SELECT TOP 10 * FROM table1 WHERE range;
SELECT a,b,c FROM tablename ta
WHERE a = (SELECT MAX(a) FROM tablename tb WHERE tb.b = ta.b);
(SELECT a FROM A) EXCEPT (SELECT a FROM B) EXCEPT (SELECT a FROM C);
SELECT TOP 10 * FROM tablename ORDER BY NEWID();
DELETE FROM tablename
WHERE id NOT IN (SELECT MAX(id) FROM tablename GROUP BY col1, col2, ...);
SELECT name FROM sysobjects WHERE type = 'U';
SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName');
SELECT type,
SUM(CASE vender WHEN 'A' THEN pcs ELSE 0 END) AS A_pcs,
SUM(CASE vender WHEN 'C' THEN pcs ELSE 0 END) AS C_pcs,
SUM(CASE vender WHEN 'B' THEN pcs ELSE 0 END) AS B_pcs
FROM tablename GROUP BY type;
TRUNCATE TABLE table1;
SELECT TOP 5 * FROM (SELECT TOP 15 * FROM table ORDER BY id ASC) t2 ORDER BY id DESC;
掌握此語法集,應對 T-SQL 開發、維護、遷移無虞。