SQL 常用語法大全：DML、DDL、查詢、權限與 Oracle 物件完整參考

資料操作語言 (DML)

INSERT

-- 明確欄位
INSERT INTO table (col1, col2) VALUES (val1, val2);

-- 從另一張表
INSERT INTO table (col1, col2) SELECT col1, col2 FROM other_table;

-- 字串含單引號：雙單引號跳脫 'O''Brien'
-- 日期：SYSDATE / TO_DATE('2001-08-01','YYYY-MM-DD')
-- 長字串 >4000：使用 CLOB + DBMS_LOB
-- 自動序列：CREATE SEQUENCE seq_name INCREMENT BY 1 START WITH 1; INSERT ... seq_name.NEXTVAL

DELETE vs TRUNCATE

DELETE FROM table WHERE condition; -- 產生 UNDO、觸發器、不釋放空間
TRUNCATE TABLE table;              -- 極速、釋放 High Water Mark、不可回滾、不觸發觸發器

UPDATE

UPDATE table SET col1=val1, col2=val2 WHERE condition;
-- 注意：未賦值欄位變 NULL，超長度報錯
-- 交易控制：COMMIT / ROLLBACK
-- 大量異動建議分批 (如 1 萬筆) 並中間 COMMIT，避免 UNDO 爆滿

資料定義語言 (DDL)

常用型別 (Oracle)

• CHAR(n) 固定長度
• VARCHAR2(n) 可變長度 (建議慣用)
• NUMBER(p,s) p=總位數, s=小數位
• DATE 含年月日時分秒

CREATE TABLE 最佳實務

• NOT NULL、小欄位前置；可 NULL、大欄位後置。
• 欄位命名慣用英文；可加 DEFAULT SYSDATE 自動填入建立時間。
• 約束：PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY。

ALTER TABLE

ALTER TABLE t1 RENAME TO t2;
ALTER TABLE t ADD col datatype;
ALTER TABLE t MODIFY col new_datatype;
ALTER TABLE t ADD CONSTRAINT pk_name PRIMARY KEY (col);
ALTER TABLE t ADD CONSTRAINT uk_name UNIQUE (col);
ALTER TABLE t CACHE / NOCACHE;

DROP / TRUNCATE

DROP TABLE t CASCADE CONSTRAINTS; -- 連同約束刪除
TRUNCATE TABLE t;                 -- 保留結構、釋放空間

查詢語句 (SELECT)

基礎與函數

SELECT col1, COUNT(*), MIN(col), MAX(col), AVG(col), DISTINCT col
     , TO_CHAR(dt, 'YYYY-MM-DD HH24:MI:SS')
     , NVL(expr1, expr2)          -- expr1 為 NULL 回傳 expr2
     , DECODE(col, v1, r1, v2, r2, default)
     , LPAD(str, n, pad_str)
FROM table;

子查詢

-- 單行
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='ALLEN');

-- 多行：IN / ANY / SOME / ALL
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NEW YORK');
SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno=30);

集合運算

UNION        -- 合併去重
UNION ALL    -- 合併不去重 (效能較好)
MINUS        -- 差集
INTERSECT    -- 交集

分組與 HAVING

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 5;

連接 (JOIN)

-- 內連接
SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
-- 左外連 (保留左表所有)
SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;
-- 右外連
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;
-- 舊式 Oracle 外連 (+) 號置於右表欄位
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno(+);

排序

ORDER BY col1 ASC, col2 DESC;

模糊比對

WHERE col LIKE 'abc%'      -- 開頭
WHERE col LIKE '%abc%'     -- 含有
WHERE INSTR(col, 'abc') > 0

ROWID

每列隱含 ROWID 唯一標識，可用於快速定位/刪除重複列。

常用 Schema 物件

索引

CREATE INDEX idx_name ON table (col1, col2);
ALTER INDEX idx_name REBUILD; -- 重建消除碎片
-- 單表索引建議 ≤3 個；優先單欄，視計畫建立複合/函數索引
-- 字串索引長度限制：約 block_size * 40%

檢視

CREATE VIEW v_name AS SELECT ...;
ALTER VIEW v_name COMPILE; -- 重新編譯

同義詞

CREATE SYNONYM syn_name FOR table;
CREATE SYNONYM syn_name FOR table@dblink;

資料庫連結

CREATE DATABASE LINK link_name CONNECT TO user IDENTIFIED BY pwd USING 'tns_str';
SELECT * FROM table@link_name;

權限管理 (DCL)

系統權限角色

• CONNECT 基本連線
• RESOURCE 開發建立物件
• DBA 完整管理

物件權限

GRANT SELECT, INSERT, UPDATE, DELETE ON table TO user1, user2;
GRANT ALL ON table TO user;
REVOKE SELECT ON table FROM user;

結語

本文彙整 Oracle 偏重的 SQL 核心語法，涵蓋 DML/DDL/查詢/物件/權限。實務上請參考各資料庫官方文件確認語法細節 (如 SQL Server 用 IDENTITY、TOP、ISNULL 等差異)。