彙整關聯式資料庫核心 SQL 敘述,依功能分為 DML、DDL、查詢、結構描述物件、權限控制(DCL),語法以 Oracle/ANSI SQL 為基準,主流資料庫(SQL Server、PostgreSQL、MySQL)大多相容或微調即用。
INSERT INTO Table (Col1, Col2, ...) VALUES (Val1, Val2, ...);
INSERT INTO Table (Col1, Col2) SELECT Col1, Col2 FROM OtherTable;
'Text',單引號逸出寫 ''(兩個單引號)。SYSDATE / CURRENT_TIMESTAMP 或 TO_DATE('2023-12-15','YYYY-MM-DD')。CLOB + DBMS_LOB。CREATE SEQUENCE Seq_Name INCREMENT BY 1 START WITH 1 MAXVALUE 999999 NOCACHE; → Seq_Name.NEXTVAL。DELETE FROM Table WHERE Condition;
TRUNCATE TABLE Table; -- 釋放空間、不可回滾、不觸發觸發器
DELETE 產生 UNDO、可回滾、觸發 Trigger;TRUNCATE 為 DDL,高速釋放 Extent。COMMIT,避免 UNDO 表空間暴漲。UPDATE Table SET Col1 = Val1, Col2 = Val2 WHERE Condition;
NULL(若無 DEFAULT),建議先驗證非空。COMMIT; -- 確認
ROLLBACK; -- 復原
SAVEPOINT SP1; ... ROLLBACK TO SP1;
CREATE TABLE Employees (
EmpId NUMBER(6) PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
DeptId NUMBER(4) REFERENCES Departments(DeptId),
HireDate DATE DEFAULT SYSDATE,
Salary NUMBER(8,2) CHECK (Salary > 0)
);
ALTER TABLE T RENAME TO T2;
ALTER TABLE T ADD (ColX VARCHAR2(20) DEFAULT 'X');
ALTER TABLE T MODIFY (ColY NUMBER(10,2));
ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (Id);
ALTER TABLE T ADD CONSTRAINT UK_T_Code UNIQUE (Code);
ALTER TABLE T CACHE | NOCACHE;
DROP TABLE T CASCADE CONSTRAINTS; -- 連同約束一併刪除
TRUNCATE TABLE T; -- 保留結構,清空資料、釋放空間、不可回滾
SELECT Col1, Col2, COUNT(*), MAX(Col3), TO_CHAR(Dt,'YYYY-MM-DD HH24:MI:SS')
FROM T1, T2
WHERE T1.Id = T2.Id
AND T1.Status = 'A'
GROUP BY Col1, Col2
HAVING COUNT(*) > 10
ORDER BY Col1 ASC, Col2 DESC;
| 函數 | 說明 |
|---|---|
| NVL(e1,e2) | e1 為 NULL 回 e2,否則回 e1 |
| DECODE(v, c1,r1, c2,r2, ..., def) | 類似 CASE WHEN,舊版常用 |
| LPAD(s,n,pad) | 左填充至長度 n |
| TO_CHAR(dt, fmt) | 日期/數字格式化 |
UNION(去重)、UNION ALL(保留重複)、MINUS(差集)、INTERSECT(交集)。-- 內連接
SELECT * FROM A JOIN B ON A.Id = B.Id;
-- 左外連接(Oracle 寫法)
SELECT * FROM A, B WHERE A.Id = B.Id(+);
-- ANSI 標準(推薦)
SELECT * FROM A LEFT JOIN B ON A.Id = B.Id;
| 物件 | 建立語法 | 用途 |
|---|---|---|
| Index | CREATE INDEX Idx_Name ON Table (Col1, Col2); | 加速查詢,單表 ≤ 3 個為宜 |
| View | CREATE VIEW V AS SELECT ...; | 簡化複雜查詢、權限隔離 |
| Synonym | CREATE SYNONYM S FOR Table[@DBlink]; | 跨 Schema/DB 透明存取 |
| DB Link | CREATE DATABASE LINK L CONNECT TO U IDENTIFIED BY P USING 'TNS'; | 跨資料庫查詢 |
CONNECT:建立 Session。RESOURCE:建立物件(表、序列、程序)。DBA:完整管理權限。GRANT SELECT, INSERT, UPDATE, DELETE ON Table TO User|Role;
GRANT ALL ON Table TO User;
REVOKE DELETE ON Table FROM User;
COMMIT,異常捕獲 ROLLBACK。COMMIT,控制 UNDO/REDO 大小。掌握此速查手冊,涵蓋 90% 日常 SQL 開發與維護場景。