MySQL 常用 SQL 語句速查手冊：查詢、增刪改、統計、DDL 全覆蓋

一、數據篩選查詢

-- 精確匹配
SELECT * FROM table WHERE col = 'val' ORDER BY col [DESC];

-- 模糊匹配
SELECT * FROM table WHERE col LIKE '%val%' ORDER BY col [DESC];

-- 限制筆數
SELECT * FROM table WHERE col ORDER BY col [DESC] LIMIT 10;

-- IN 範圍
SELECT * FROM table WHERE col IN ('v1','v2','v3');

-- BETWEEN 範圍
SELECT * FROM table WHERE col BETWEEN val1 AND val2;

二、更新數據

UPDATE table SET col = 'val' WHERE condition;
UPDATE table SET col1='v1', col2='v2' WHERE condition;

三、刪除數據

DELETE FROM table WHERE condition;
DELETE FROM table;  -- 清空全表（慎用）

四、新增數據

INSERT INTO table (col1,col2,col3) VALUES (v1,v2,v3);
INSERT INTO target_table SELECT * FROM source_table;  -- 表間複製

五、統計聚合函數

用法：SELECT SUM(col) AS alias FROM table WHERE ...，應用層取 rs("alias")。

六、建表與刪表

CREATE TABLE tab01 (
    name VARCHAR(50),
    dt DATETIME DEFAULT NOW()
);
DROP TABLE tab01;  -- 永久刪除

七、實用技巧

• LIMIT 配合 ORDER BY 實現分頁。
• INSERT ... SELECT 批量遷移數據。
• 統計函數配合 GROUP BY 做分組聚合。