您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

經典 SQL 語句大全：DDL/DML/DCL、進階查詢、分頁、去重、隨機抽樣實戰匯總

編輯：關於SqlServer

前言

SQL 語句分為 DDL (定義)、DML (操縱)、DCL (控制) 三大類。本文匯總從建表、索引、視圖到複雜聯結、集合運算、分頁、去重、隨機抽樣、跨庫複製等高頻實戰語句，標註 MSSQL/Access 差異，適合開發人員速查與學習。

一、基礎 DDL/DML/DCL

-- 建庫/刪庫
CREATE DATABASE dbname;  DROP DATABASE dbname;

-- 備份 (SQL Server)
USE master; EXEC sp_addumpdevice 'disk', 'testBack', 'c:\backup\db.bak';
BACKUP DATABASE pubs TO testBack;

-- 建表/刪表/加欄位/加主鍵/建索引/建視圖
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
DROP TABLE t;
ALTER TABLE t ADD age INT;
ALTER TABLE t ADD PRIMARY KEY (id);
CREATE [UNIQUE] INDEX idx_name ON t(col...);
DROP INDEX idx_name ON t;
CREATE VIEW v AS SELECT ...; DROP VIEW v;

二、基礎 DML 單表操作

SELECT * FROM t WHERE 條件;
INSERT INTO t (c1,c2) VALUES (v1,v2);
DELETE FROM t WHERE 條件;
UPDATE t SET c1=v1 WHERE 條件;
SELECT * FROM t WHERE c1 LIKE '%keyword%';
SELECT * FROM t ORDER BY c1, c2 [DESC];
SELECT COUNT(*) AS total FROM t;
SELECT SUM(c1), AVG(c1), MAX(c1), MIN(c1) FROM t;

三、進階查詢運算子 (集合運算)

UNION / UNION ALL： 合併結果集，前者去重、後者保留重複。欄位數/類型需一致。
EXCEPT (MINUS)： 差集，第一個結果集減去第二個。`EXCEPT ALL` 保留重複。
INTERSECT： 交集。`INTERSECT ALL` 保留重複。

四、外部聯結 (Outer Join)

-- LEFT JOIN: 左表全保留 + 右表匹配
SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.a_id;

-- RIGHT JOIN: 右表全保留
SELECT a.*, b.* FROM a RIGHT JOIN b ON a.id = b.a_id;

-- FULL JOIN: 雙邊全保留
SELECT a.*, b.* FROM a FULL JOIN b ON a.id = b.a_id;

五、實戰高頻語句庫

場景	SQL (MSSQL 語法為主)
複製表結構 (不含資料)	SELECT * INTO b FROM a WHERE 1<>1; -- 或 SELECT TOP 0 * INTO b FROM a;
複製表資料 (結構相同)	INSERT INTO b SELECT * FROM a;
跨資料庫複製 (Access)	INSERT INTO b SELECT * FROM a IN 'path\db.mdb';
子查詢 IN	SELECT * FROM a WHERE id IN (SELECT id FROM b);
顯示文章/作者/最新回覆	SELECT a.title, a.user, b.maxdate FROM a, (SELECT MAX(date) maxdate FROM a GROUP BY title) b;
行內檢視表	SELECT * FROM (SELECT a,b,c FROM t) tmp WHERE tmp.a > 1;
BETWEEN 範圍 (含邊界)	SELECT * FROM t WHERE dt BETWEEN '2023-01-01' AND '2023-12-31';
IN 列表查詢	SELECT * FROM t WHERE status IN (1,2,3);
刪除主表中副表無對應資料	DELETE FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE a.id = b.a_id);
四表聯結	SELECT * FROM a JOIN b ON ... JOIN c ON ... JOIN d ON ... WHERE ...;
排程提前 5 分鐘提醒	SELECT * FROM schedule WHERE DATEDIFF(minute, start_time, GETDATE()) > 5;
分頁 (SQL Server 2000 風格)	SELECT TOP 10 * FROM (SELECT TOP 20 pk, ord FROM t ORDER BY ord DESC) tmp JOIN t ON t.pk=tmp.pk ORDER BY tmp.ord;
前 10 筆	SELECT TOP 10 * FROM t WHERE ...;
分組取最大值完整列 (如月排行)	SELECT * FROM t ta WHERE score = (SELECT MAX(score) FROM t tb WHERE tb.group = ta.group);
多重 EXCEPT 排除	(SELECT a FROM A) EXCEPT (SELECT a FROM B) EXCEPT (SELECT a FROM C);
隨機取 10 筆	SELECT TOP 10 * FROM t ORDER BY NEWID(); -- MSSQL
刪除重複資料 (保留最大 ID)	DELETE FROM t WHERE id NOT IN (SELECT MAX(id) FROM t GROUP BY col1, col2...);
列出所有使用者表	SELECT name FROM sysobjects WHERE type='U';
列出表所有欄位	SELECT name FROM syscolumns WHERE id=OBJECT_ID('TableName');
CASE 多條件聚合 (樞紐)	SELECT type, SUM(CASE vender WHEN 'A' THEN pcs ELSE 0 END) AS A, SUM(CASE vender WHEN 'B' THEN pcs ELSE 0 END) AS B FROM t GROUP BY type;
清空表 (保留結構、重置識別欄)	TRUNCATE TABLE t;
取第 10-15 筆	SELECT TOP 5 * FROM (SELECT TOP 15 * FROM t ORDER BY id ASC) tmp ORDER BY id DESC;

六、隨機抽樣進階 (ASP + SQL 結合)

避免應用層遍歷全表。SQL 端直接 `WHERE ID = RAND()` 或 `ORDER BY NEWID()/RAND() LIMIT n`。大表建議先抽樣主鍵再 JOIN 明細。

七、 Access 特有語法差異

左聯結：`SELECT ... FROM a LEFT JOIN b ON a.f1 = b.f1` (無 OUTER 關鍵字)。
字串截斷：`IIF(LEN(f)>2, LEFT(f,2)+'...', f)`。
隨機：`SELECT TOP 10 * FROM t ORDER BY RND(id)`。

八、 ADO Execute / 事務處理

`Set rs = conn.Execute("SELECT ...")` 返回 Recordset。
`conn.Execute "INSERT ...", RecordsAffected, adCmdText` 執行動作查詢。
事務：`conn.BeginTrans` → 操作 → `conn.CommitTrans` / `conn.RollbackTrans`。錯誤檢查 `conn.Errors.Count`。