SQL 三大語言類別與核心語句速查表，涵蓋建庫/建表/索引/視圖、增刪改查、彙總函數、LIKE 模糊查詢、排序分頁基礎。

語言分類

類別	全名	核心指令
DDL	Data Definition Language	CREATE, ALTER, DROP, DECLARE
DML	Data Manipulation Language	SELECT, INSERT, UPDATE, DELETE
DCL	Data Control Language	GRANT, REVOKE, COMMIT, ROLLBACK

資料庫/資料表 基礎操作

-- 建立/刪除資料庫
CREATE DATABASE dbname;
DROP DATABASE dbname;

-- 建立新表
CREATE TABLE tabname (
    col1 type1 [NOT NULL] [PRIMARY KEY],
    col2 type2 [NOT NULL],
    ...
);

-- 依現有表建表 (僅結構)
CREATE TABLE tab_new LIKE tab_old;           -- MySQL
CREATE TABLE tab_new AS SELECT * FROM tab_old WHERE 1=0; -- 標準 SQL

-- 刪除表
DROP TABLE tabname;

-- 新增欄位
ALTER TABLE tabname ADD COLUMN col type;

-- 新增/刪除主鍵
ALTER TABLE tabname ADD PRIMARY KEY (col);
ALTER TABLE tabname DROP PRIMARY KEY;

-- 索引
CREATE [UNIQUE] INDEX idxname ON tabname(col...);
DROP INDEX idxname ON tabname;  -- MySQL
DROP INDEX idxname;             -- SQL Server

-- 視圖
CREATE VIEW viewname AS SELECT ...;
DROP VIEW viewname;

核心 DML 查詢與修改

-- 查詢
SELECT * FROM table1 WHERE 條件;

-- 新增
INSERT INTO table1 (field1, field2) VALUES (value1, value2);

-- 刪除
DELETE FROM table1 WHERE 條件;

-- 更新
UPDATE table1 SET field1 = value1 WHERE 條件;

-- 模糊查詢
SELECT * FROM table1 WHERE field1 LIKE '%value1%';
-- % 任意長度字串, _ 單一字元

-- 排序
SELECT * FROM table1 ORDER BY field1, field2 [DESC];

-- 彙總函數
SELECT COUNT(*) AS total FROM table1;
SELECT SUM(field1) AS sumv FROM table1;
SELECT AVG(field1) AS avgv FROM table1;
SELECT MAX(field1) AS maxv FROM table1;
SELECT MIN(field1) AS minv FROM table1;

進階查詢運算子

運算子	說明	去重
UNION	合併結果集	預設去重
UNION ALL	合併結果集	保留重複
EXCEPT / MINUS	差集 (在 A 不在 B)	去重
INTERSECT	交集	去重

前提：參與運算的查詢欄位數、類型、順序必須一致。

外部連接 (OUTER JOIN)

-- 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 TOP 10 * FROM (SELECT TOP 20 ... ORDER BY desc) ORDER BY asc (SQL Server 2000 風格)
• 前 N 筆：SELECT TOP 10 * FROM table (SQL Server) / LIMIT 10 (MySQL/PostgreSQL)
• 每組最大值：SELECT a,b,c FROM t ta WHERE a = (SELECT MAX(a) FROM t tb WHERE tb.b = ta.b)
• 隨機抽樣：SELECT TOP 10 * FROM t ORDER BY NEWID() (SQL Server) / ORDER BY RAND() (MySQL)
• 刪除重複 (保留最大 ID)：DELETE FROM t WHERE id NOT IN (SELECT MAX(id) FROM t GROUP BY col1,col2)
• 列出所有使用者表：SELECT name FROM sysobjects WHERE type='U' (SQL Server)
• 列出表欄位：SELECT name FROM syscolumns WHERE id=OBJECT_ID('TableName')
• 條件彙總 (CASE)：

  SELECT type,
      SUM(CASE vender WHEN 'A' THEN pcs ELSE 0 END) AS A_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 t ORDER BY id) t2 ORDER BY id DESC (取 11-15 筆)

掌握這些基礎語句與模式，覆蓋 90% 日常開發需求。