| 類別 | 全稱 | 核心關鍵字 |
|---|---|---|
| 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 DatabaseName;
-- 刪除資料庫
DROP DATABASE DatabaseName;
-- 備份裝置與備份
USE master;
EXEC sp_addumpdevice 'disk', 'TestBack', 'C:\Backup\MyDB.dat';
BACKUP DATABASE Pub TO TestBack;-- 建表
CREATE TABLE TabName (
Col1 Type1 [NOT NULL] [PRIMARY KEY],
Col2 Type2 [NOT NULL],
...
);
-- 依現有表結構建表 (語法因版本異)
-- SQL Server: SELECT * INTO NewTable FROM OldTable WHERE 1=0;
-- 或: CREATE TABLE NewTable LIKE OldTable; (MySQL/PostgreSQL)
-- 刪表
DROP TABLE TabName;
-- 增加欄位 (不可刪欄，型別不可變，僅可擴充 VARCHAR 長度)
ALTER TABLE TabName ADD ColumnName DataType;
-- 主鍵管理
ALTER TABLE TabName ADD PRIMARY KEY (ColName);
ALTER TABLE TabName DROP PRIMARY KEY (ColName);-- 建立索引 (唯一/非唯一)
CREATE [UNIQUE] INDEX IdxName ON TabName (Col1, Col2...);
-- 刪除索引
DROP INDEX IdxName ON TabName; -- 或 DROP INDEX TabName.IdxName
-- 視圖
CREATE VIEW ViewName AS SELECT ...;
DROP VIEW ViewName;-- 查詢
SELECT * FROM Table1 WHERE Condition;
-- 新增
INSERT INTO Table1 (Field1, Field2) VALUES (Value1, Value2);
-- 刪除
DELETE FROM Table1 WHERE Condition;
-- 更新
UPDATE Table1 SET Field1 = Value1 WHERE Condition;
-- 模糊查詢
SELECT * FROM Table1 WHERE Field1 LIKE '%Value1%';
-- 排序
SELECT * FROM Table1 ORDER BY Field1, Field2 [DESC];
-- 聚合
SELECT COUNT(*) AS TotalCount FROM Table1;
SELECT SUM(Field1) AS SumValue FROM Table1;
SELECT AVG(Field1) AS AvgValue FROM Table1;
SELECT MAX(Field1) AS MaxValue FROM Table1;
SELECT MIN(Field1) AS MinValue FROM Table1;| 運算子 | 說明 | 去重行為 |
|---|---|---|
| UNION | 合併兩結果集 | 預設去重 |
| UNION ALL | 合併保留重複 | 不去重，效能較好 |
| EXCEPT | 差集 (在 A 不在 B) | 預設去重 |
| EXCEPT ALL | 差集保留重複 | 不去重 |
| INTERSECT | 交集 | 預設去重 |
| INTERSECT ALL | 交集保留重複 | 不去重 |
註：參與運算的查詢欄位數、順序、資料型別必須一致。
-- LEFT OUTER JOIN (左聯接)
SELECT A.A, A.B, A.C, B.C, B.D, B.F
FROM TableA A LEFT OUTER JOIN TableB B ON A.A = B.C;
-- RIGHT OUTER JOIN (右聯接)
SELECT A.A, A.B, A.C, B.C, B.D, B.F
FROM TableA A RIGHT OUTER JOIN TableB B ON A.A = B.C;
-- FULL OUTER JOIN (全聯接)
SELECT A.A, A.B, A.C, B.C, B.D, B.F
FROM TableA A FULL OUTER JOIN TableB B ON A.A = B.C;SELECT * INTO NewTable FROM OldTable WHERE 1 <> 1;
-- 或
SELECT TOP 0 * INTO NewTable FROM OldTable;INSERT INTO TargetTable (ColA, ColB, ColC)
SELECT ColD, ColE, ColF FROM SourceTable;INSERT INTO TargetTable (ColA, ColB, ColC)
SELECT ColD, ColE, ColF
FROM SourceTable IN 'C:\Path\Data.mdb' WHERE Condition;SELECT A, B, C FROM TableA
WHERE A IN (SELECT D FROM TableB);
-- 或靜態清單
WHERE A IN (1, 2, 3);SELECT A.Title, A.UserName, B.LastReplyDate
FROM Articles A
JOIN (SELECT Title, MAX(AddDate) AS LastReplyDate FROM Articles GROUP BY Title) B
ON A.Title = B.Title;SELECT * FROM (SELECT A, B, C FROM TableA) T WHERE T.A > 1;-- 包含邊界
SELECT * FROM Table1 WHERE TimeCol BETWEEN '2023-01-01' AND '2023-12-31';
-- 排除邊界
SELECT * FROM Table1 WHERE ColA NOT BETWEEN 10 AND 20;SELECT TOP 10 B.*
FROM (
SELECT TOP 20 PrimaryKey, SortCol FROM TableName ORDER BY SortCol DESC
) A
INNER JOIN TableName B ON B.PrimaryKey = A.PrimaryKey
ORDER BY A.SortCol;SELECT TOP 10 * FROM Table1 WHERE Condition;SELECT A, B, C FROM TableName TA
WHERE A = (SELECT MAX(A) FROM TableName TB WHERE TB.B = TA.B);(SELECT A FROM TableA)
EXCEPT (SELECT A FROM TableB)
EXCEPT (SELECT A FROM TableC);-- SQL Server
SELECT TOP 10 * FROM TableName ORDER BY NEWID();
-- MySQL
SELECT * FROM TableName ORDER BY RAND() LIMIT 10;
-- Access
SELECT TOP 10 * FROM TableName ORDER BY RND(ID);DELETE FROM TableName
WHERE ID NOT IN (
SELECT MAX(ID) FROM TableName GROUP BY Col1, Col2, ...
);-- 所有使用者資料表
SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U';
-- 指定表的所有欄位
SELECT NAME FROM SYSCOLUMNS WHERE ID = OBJECT_ID('TableName');SELECT
Type,
SUM(CASE WHEN Vender = 'A' THEN Pcs ELSE 0 END) AS VenderA,
SUM(CASE WHEN Vender = 'C' THEN Pcs ELSE 0 END) AS VenderC,
SUM(CASE WHEN Vender = 'B' THEN Pcs ELSE 0 END) AS VenderB
FROM TableName
GROUP BY Type;輸出範例：電腦 A=2, B=0, C=0 | 光碟 A=2, B=2, C=0 | 手機 A=0, B=3, C=3
TRUNCATE TABLE TableName;SELECT TOP 5 * FROM (
SELECT TOP 15 * FROM TableName ORDER BY ID ASC
) T ORDER BY ID DESC;-- SQL Server / T-SQL
SELECT
CASE WHEN LEN(Field) > 10 THEN LEFT(Field, 10) + '...' ELSE Field END AS News_Name,
News_ID
FROM TableName;
-- Access / Jet SQL
SELECT IIF(LEN(Field) > 2, LEFT(Field, 2) + '...', Field) FROM TableName;Set Rs = Conn.Execute("SELECT * FROM Table")自動建立 Recordset 物件，查詢結果存入其中。
Conn.Execute "UPDATE Table SET Col=1", RecordsAffected, adCmdTextRecordsAffected：選擇性參數，接收影響列數。adCmdText：告知 ADO 後續字串為命令文本，提升解析效能。Conn.BeginTrans ' 開啟交易
' ... 多條 SQL 操作 ...
If Conn.Errors.Count > 0 Then
Conn.RollbackTrans ' 任一失敗全盤回滾
Else
Conn.CommitTrans ' 全部成功確認提交
End If交易將一組操作視為原子單位：全成或全敗，確保資料一致性。錯誤資訊可透過
Conn.Errors集合逐一檢視。
