SQL 語法大全：DML、DDL、查詢、Schema 物件、權限管理一網打盡

一、資料操作語言 (DML)

1. INSERT 插入

INSERT INTO 表名(欄位1, 欄位2, ...) VALUES (值1, 值2, ...);
INSERT INTO 表名(欄位1, 欄位2, ...) SELECT 欄位1, 欄位2, ... FROM 其他表;

• 字串值必須用單引號，單引號轉義用兩個單引號 ''。
• 日期可用 SYSDATE 或 TO_DATE('2001-08-01','YYYY-MM-DD')。
• 超過 4000 字元請用 CLOB + DBMS_LOB 套件。
• 自增序列：CREATE SEQUENCE seq_name INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;，插入用 seq_name.NEXTVAL。

2. DELETE 刪除

DELETE FROM 表名 WHERE 條件;

注意：DELETE 不釋放空間，僅標記區塊為 unused。大量清表請用：

TRUNCATE TABLE 表名;  -- 釋放空間、不可回滾

3. UPDATE 修改

UPDATE 表名 SET 欄位1=值1, 欄位2=值2, ... WHERE 條件;

• 未賦值欄位會變 NULL，建議先非空檢核。
• 長度超限會報錯，插入前驗證。

4. 事務控制

• DML 執行後須 COMMIT 生效，ROLLBACK 可回滾。
• 大量操作（>1 萬筆）建議分批 COMMIT，避免回滾段爆滿導致效能下降。

二、資料定義語言 (DDL)

常用資料型別 (Oracle)

建表最佳實踐

• 小且非空欄位前置，可空欄位後置。
• 欄位名建議英文，可加中文註解。
• 預設值：DEFAULT SYSDATE 自動記錄操作時間。
• 約束：UNIQUE、PRIMARY KEY。

ALTER 修改

ALTER TABLE 表名1 RENAME TO 表名2;           -- 改表名
ALTER TABLE 表名 ADD 欄位名 型別;             -- 加欄位
ALTER TABLE 表名 MODIFY 欄位名 新型別;        -- 改型別
ALTER TABLE 表名 ADD CONSTRAINT 名稱 PRIMARY KEY (欄位); -- 加主鍵
ALTER TABLE 表名 ADD CONSTRAINT 名稱 UNIQUE (欄位);      -- 加唯一
ALTER TABLE 表名 CACHE | NOCACHE;             -- 置入/移出記憶體

DROP / TRUNCATE

DROP TABLE 表名 CASCADE CONSTRAINTS;  -- 連同約束刪除
TRUNCATE TABLE 表名;                  -- 清空資料、保留結構

三、查詢語句 (SELECT)

SELECT 欄位1, 欄位2, ... FROM 表1, 表2 ... WHERE 條件;

常用函數

• 聚合：COUNT(*), MIN, MAX, AVG, DISTINCT
• 日期格式：TO_CHAR(date, 'YYYY-MM-DD HH24:MI:SS')
• NVL(expr1, expr2)：expr1 為 NULL 回傳 expr2
• DECODE(a, v1, r1, v2, r2, ...)：類似 switch-case
• LPAD(str, n, pad)：左補齊

進階查詢

• 子查詢：SELECT ... FROM (SELECT ... FROM ... WHERE ...) WHERE ...
• 集合運算：UNION(去重), UNION ALL, MINUS(差集), INTERSECT(交集)
• 分組：GROUP BY 欄位 [HAVING 條件]
• 連接：內連接 t1.col = t2.col，外連接 t1.col = t2.col(+)（Oracle 舊語法）
• 排序：ORDER BY 欄位1, 欄位2 DESC
• 模糊：INSTR(col, 'str')>0 或 col LIKE 'str%'
• 每列隱含 ROWID 標識唯一性。

四、常用 Schema 物件

索引

CREATE INDEX 索引名 ON 表名 (欄位1, 欄位2...);
ALTER INDEX 索引名 REBUILD;

• 單表索引 ≤ 3 個（大表除外），優先單欄索引，結合執行計畫建立組合/函數索引。
• 字串索引長度上限約 db_block_size * 40%。

視圖

CREATE VIEW 視圖名 AS SELECT ... FROM ...;
ALTER VIEW 視圖名 COMPILE;

視圖本質是儲存的查詢，簡化複雜關聯。

同義詞

CREATE SYNONYM 同義詞名 FOR 表名;
CREATE SYNONYM 同義詞名 FOR 表名@DB_LINK;

資料庫連結

CREATE DATABASE LINK 連結名
CONNECT TO 使用者 IDENTIFIED BY 密碼
USING '連線字串';
-- 查詢遠端表
SELECT ... FROM 表名@連結名;

GLOBAL_NAMES=TRUE 時連結名必須等於遠端 DB 名。查詢全域名：SELECT * FROM GLOBAL_NAME;

五、權限管理 (DCL)

系統權限角色

• CONNECT：基本連線
• RESOURCE：開發物件
• DBA：管理員

物件權限

• ALL, SELECT, UPDATE, DELETE, INSERT, ALTER ON 物件名

授權/回收範例

GRANT CONNECT, RESOURCE TO 用戶;
GRANT SELECT ON 表名 TO 用戶;
GRANT SELECT, INSERT, DELETE ON 表名 TO 用戶1, 用戶2;

REVOKE CONNECT, RESOURCE FROM 用戶;
REVOKE SELECT ON 表名 FROM 用戶;
REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用戶1, 用戶2;