MySQL SQL 語法速查手冊：DDL 建表索引、DML 增刪改查、組合查詢與日期比較

一、資料定義語言 (DDL)

建表

CREATE TABLE table_name (
    col1 datatype [NOT NULL] [PRIMARY KEY],
    col2 datatype [NOT NULL],
    ...
);

datatype：INT、VARCHAR、DATE、TEXT 等；NOT NULL 禁止空值；PRIMARY KEY 定義主鍵。

修改表

ALTER TABLE table_name ADD COLUMN col_name datatype;
ALTER TABLE table_name ADD PRIMARY KEY (col_name);
ALTER TABLE table_name DROP PRIMARY KEY;

⚠ MySQL 不支援 `DROP COLUMN`（舊版本），需重建表或用工具。

索引

CREATE INDEX idx_name ON table_name (col_list);
DROP INDEX idx_name ON table_name;

刪除

DROP TABLE table_name;
DROP INDEX idx_name;

二、資料操作語言 (DML)

新增

INSERT INTO table_name (col1, col2) VALUES (val1, val2);
INSERT INTO table_name (col1, col2) SELECT colx, coly FROM other_table;

查詢

-- 基本
SELECT col1, col2 FROM table_name;
SELECT * FROM table_name WHERE col1 = 'xxx' AND col2 > 100;
SELECT col1, col2 FROM table_name ORDER BY col2 DESC;

-- 多表聯結
SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1;

-- 聚合
SELECT COUNT(*) FROM table_name WHERE col = 'xxx';
SELECT SUM(col1), AVG(col2), MAX(col3), MIN(col4) FROM table_name;
SELECT col1, AVG(col2) FROM table_name GROUP BY col1 HAVING AVG(col2) > 50;

-- 子查詢
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE conditions);
SELECT * FROM t1 WHERE col1 IN (SELECT col1 FROM t2 WHERE conditions);

-- 模糊/範圍/集合
SELECT * FROM t WHERE col LIKE 'prefix%';
SELECT * FROM t WHERE col IN ('a','b','c');
SELECT * FROM t WHERE col BETWEEN 10 AND 20;

更新

UPDATE table_name SET col1 = 'new_val' WHERE conditions;

刪除

DELETE FROM table_name WHERE conditions;

三、日期比較的跨資料庫寫法差異

MySQL 日期字面值支援 'YYYY-MM-DD'、'YYYY-MM-DD HH:MM:SS'，直接字串比較即可，最為簡潔。