萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 語法大全:DML/DDL/查詢/物件/權限分類速查手冊(Oracle/通用 SQL 標準)

彙整關聯式資料庫核心 SQL 敘述,依功能分為 DML、DDL、查詢、結構描述物件、權限控制(DCL),語法以 Oracle/ANSI SQL 為基準,主流資料庫(SQL Server、PostgreSQL、MySQL)大多相容或微調即用。

一、資料操作語言 (DML)

INSERT

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')。
  • 長字串(>4000 字元)需用 CLOB + DBMS_LOB。
  • 自增序列:CREATE SEQUENCE Seq_Name INCREMENT BY 1 START WITH 1 MAXVALUE 999999 NOCACHE; → Seq_Name.NEXTVAL。

DELETE

DELETE FROM Table WHERE Condition;
TRUNCATE TABLE Table; -- 釋放空間、不可回滾、不觸發觸發器
  • DELETE 產生 UNDO、可回滾、觸發 Trigger;TRUNCATE 為 DDL,高速釋放 Extent。
  • 大量刪除建議分批 COMMIT,避免 UNDO 表空間暴漲。

UPDATE

UPDATE Table SET Col1 = Val1, Col2 = Val2 WHERE Condition;
  • 未賦值欄位變為 NULL(若無 DEFAULT),建議先驗證非空。
  • 長度超限報錯,應用層預檢。

交易控制

COMMIT;   -- 確認
ROLLBACK; -- 復原
SAVEPOINT SP1; ... ROLLBACK TO SP1;

二、資料定義語言 (DDL)

CREATE TABLE

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

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 / TRUNCATE

DROP TABLE T CASCADE CONSTRAINTS; -- 連同約束一併刪除
TRUNCATE TABLE T; -- 保留結構,清空資料、釋放空間、不可回滾

三、查詢語言 (SELECT)

基本結構

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;

四、結構描述物件 (SCHEMA OBJECTS)

物件建立語法用途
IndexCREATE INDEX Idx_Name ON Table (Col1, Col2);加速查詢,單表 ≤ 3 個為宜
ViewCREATE VIEW V AS SELECT ...;簡化複雜查詢、權限隔離
SynonymCREATE SYNONYM S FOR Table[@DBlink];跨 Schema/DB 透明存取
DB LinkCREATE DATABASE LINK L CONNECT TO U IDENTIFIED BY P USING 'TNS';跨資料庫查詢

五、權限控制 (DCL)

系統權限角色

  • 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;

六、開發最佳實務摘要

  1. 所有 DML 明確 COMMIT,異常捕獲 ROLLBACK。
  2. 大量操作分批 COMMIT,控制 UNDO/REDO 大小。
  3. 索引建立依查詢計畫分析,避免過度索引拖慢寫入。
  4. 視圖/同義詞封裝複雜邏輯,應用層只見簡單物件。
  5. 最小權限原則:程式帳號僅授必要物件權限,禁用 DBA 權限連線。

掌握此速查手冊,涵蓋 90% 日常 SQL 開發與維護場景。

copyright © 萬盛學電腦網 all rights reserved