SQL 四大語言分類
| 類別 | 全稱 | 核心語句 | 用途 |
|---|
| DDL | Data Definition Language | CREATE, DROP, ALTER, TRUNCATE | 定義/管理數據庫對象（庫、表、視圖、索引） |
| DML | Data Manipulation Language | INSERT, UPDATE, DELETE | 操作表中數據記錄 |
| DQL | Data Query Language | SELECT | 查詢數據，佔日常 60%+ |
| DCL | Data Control Language | GRANT, REVOKE, COMMIT, ROLLBACK | 權限控制、事務控制 |
DDL 範例：標準建庫建表腳本（可存文件批量執行）
CREATE TABLE IF NOT EXISTS cats (
id INT NOT NULL AUTO_INCREMENT,
pid INT NOT NULL DEFAULT 0,
name VARCHAR(30) NOT NULL DEFAULT '',
desn TEXT NOT NULL DEFAULT '',
PRIMARY KEY (id),
INDEX idx_name_pid (name, pid)
);
CREATE TABLE IF NOT EXISTS products (
id INT NOT NULL AUTO_INCREMENT,
cid INT NOT NULL DEFAULT 0,
name VARCHAR(60) NOT NULL DEFAULT '',
price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
num INT NOT NULL DEFAULT 0,
desn TEXT,
ptime INT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY idx_pname_price (name, price)
);
DML 核心模板
| 操作 | 語法 | 關鍵點 |
|---|
| INSERT | INSERT INTO tbl [(col1,col2...)] VALUES (v1,v2...), (...); | 顯式列出列名，值與列一一對應，順序可調整 |
| UPDATE | UPDATE tbl SET col1=expr1[,col2=expr2...] [WHERE cond]; | WHERE 是靈魂，缺省更新全表 |
| DELETE | DELETE FROM tbl [WHERE cond]; | 無 WHERE 清空全表；TRUNCATE tbl 更快並重置自增 |
DQL：SELECT 基礎骨架
SELECT [ALL | DISTINCT]
{* | table.* | [table.]col1 [AS alias1] [, [table.]col2 [AS alias2]] ...}
FROM table_name
[WHERE ...]
[GROUP BY ... [HAVING ...]]
[ORDER BY ... [ASC|DESC]]
[LIMIT count]
WHERE 條件常用運算符速查
| 分類 | 操作符 | 示例 |
|---|
| 邏輯 | AND/&&, OR/||, NOT/! | WHERE id>10 AND status=1 |
| 比較 | =, <=>(NULL安全), !=/<>, <, <=, >, >= | WHERE col <=> NULL |
| 範圍 | BETWEEN ... AND ..., NOT BETWEEN | WHERE id BETWEEN 10 AND 20 |
| 集合 | IN (...), NOT IN (...) | WHERE id IN (5,10,15) |
| 模糊 | LIKE _ %, NOT LIKE, REGEXP/RLIKE | WHERE name REGEXP '^java' |
下一篇預告：「SELECT 進階：別名、DISTINCT 作用域、表達式列、多表連接、子查詢、排序分頁、分組聚合」
