MySQL 查詢語句大全：從基礎 CRUD 到進階連接、子查詢與優化技巧

基礎 DDL/DML 快速參考

-- 庫操作
SHOW DATABASES;
CREATE DATABASE mydb;
USE mydb;
DROP DATABASE mydb;

-- 表操作
SHOW TABLES;
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(20), sex CHAR(1));
DESCRIBE t;
DROP TABLE t;
TRUNCATE TABLE t;  -- 快速清空，不記錄日誌

-- 增刪改查
INSERT INTO t (name, sex) VALUES ('hyq', 'M');
LOAD DATA LOCAL INFILE 'D:/data.txt' INTO TABLE t;
SOURCE d:/script.sql;

UPDATE t SET sex='F' WHERE name='hyq';
DELETE FROM t WHERE name='hyq';
SELECT * FROM t WHERE name='hyq';

用戶權限管理

-- 刪除匿名用戶、設定 root 密碼
USE mysql;
DELETE FROM user WHERE User='';
UPDATE user SET Password=PASSWORD('newpwd') WHERE User='root';
FLUSH PRIVILEGES;

-- GRANT 授權範例
GRANT ALL ON mydb.* TO 'app'@'%' IDENTIFIED BY 'pwd';
GRANT SELECT,INSERT,UPDATE ON mydb.* TO 'readonly'@'localhost' IDENTIFIED BY 'pwd';
REVOKE DELETE ON mydb.* FROM 'app'@'%';

權限代碼對照表

查詢進階：排序、分組、去重、限制

-- 排序
SELECT * FROM t ORDER BY age DESC, id ASC;

-- 去重
SELECT DISTINCT city FROM users;

-- 限制行數
SELECT * FROM t LIMIT 10;           -- 前 10 行
SELECT * FROM t LIMIT 20, 10;       -- 偏移 20，取 10 行（分頁）

-- 聚合與分組
SELECT dept, COUNT(*), AVG(salary) FROM emp GROUP BY dept;
SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVING AVG(salary) > 5000;

多表連接查詢詳解

內連接（INNER JOIN）

-- 等值連接
SELECT a.*, b.dept_name
FROM emp a INNER JOIN dept b ON a.dept_id = b.id;

-- 自然連接（自動匹配同名欄位，去重）
SELECT * FROM emp NATURAL JOIN dept;

外連接

-- 左連接：保留左表所有行
SELECT a.*, b.dept_name
FROM emp a LEFT JOIN dept b ON a.dept_id = b.id;

-- 右連接：保留右表所有行
SELECT a.*, b.dept_name
FROM emp a RIGHT JOIN dept b ON a.dept_id = b.id;

-- 全連接：MySQL 不直接支援，用 UNION 模擬
SELECT * FROM emp a LEFT JOIN dept b ON a.dept_id = b.id
UNION
SELECT * FROM emp a RIGHT JOIN dept b ON a.dept_id = b.id;

交叉連接（笛卡爾積）

SELECT * FROM t1 CROSS JOIN t2; -- 無 WHERE 條件

子查詢三大模式

-- 1. 標量子查詢（返回單值）
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

-- 2. IN / NOT IN 子查詢（返回集合）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip=1);

-- 3. EXISTS / NOT EXISTS（相關子查詢，效能通常優於 IN）
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

聯合查詢（UNION）

SELECT name, city FROM suppliers WHERE country='Brazil'
UNION
SELECT name, city FROM customers WHERE country='Brazil';
-- UNION ALL 保留重複行

核心技巧：INSERT ... SELECT / SELECT ... INTO

-- 複製表結構與資料
CREATE TABLE new_t AS SELECT * FROM old_t;
-- 僅複製結構
CREATE TABLE new_t AS SELECT * FROM old_t WHERE 1=0;
-- 異表備份
INSERT INTO backup_t SELECT * FROM t WHERE create_time < '2023-01-01';

SQL Server / Access 差異備註

• 日期字面值：Access 用 `#2023-01-01#`，SQL Server 用 `'2023-01-01'`。
• `TOP n` / `TOP n PERCENT` 為 SQL Server/Access 語法，MySQL 用 `LIMIT`。
• `ISNULL()` / `IFNULL()` / `COALESCE()` 跨平台處理 NULL。