SQL 經典語句大全：從基礎 CRUD 到進階查詢、分頁、優化技巧

SQL 經典語句大全：從基礎 CRUD 到進階查詢、分頁、優化技巧

SQL（Structured Query Language）是關聯式資料庫的標準語言。本文彙整常用語句分類參考，涵蓋 DDL、DML、DQL、進階查詢、分頁、效能技巧，適用於 MySQL、SQL Server、PostgreSQL 等主流資料庫（語法細節請參考各資料庫文件）。

一、基礎 DDL（資料定義）

-- 建立/刪除資料庫
CREATE DATABASE db_name;
DROP DATABASE db_name;

-- 建立資料表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 依現有表建立新表
CREATE TABLE new_table LIKE old_table;              -- 僅結構
CREATE TABLE new_table AS SELECT id, name FROM old_table; -- 結構+資料

-- 修改表結構
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ADD PRIMARY KEY (id);
ALTER TABLE users DROP PRIMARY KEY;

-- 索引
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX uk_username ON users(username);
DROP INDEX idx_email ON users;

-- 視圖
CREATE VIEW active_users AS SELECT * FROM users WHERE status = 1;
DROP VIEW active_users;

二、基礎 DML/DQL（資料操作與查詢）

-- 新增
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- 查詢
SELECT * FROM users WHERE id = 1;
SELECT id, username FROM users WHERE username LIKE 'john%';
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 更新
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 刪除
DELETE FROM users WHERE id = 1;

-- 聚合
SELECT COUNT(*) AS total FROM users;
SELECT SUM(amount) FROM orders;
SELECT AVG(score), MAX(score), MIN(score) FROM exams;

三、進階查詢運算子

-- LEFT JOIN：左表全保留
SELECT u.username, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN：右表全保留
SELECT u.username, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN：雙邊全保留（MySQL 不支援，可用 UNION 模擬）
SELECT * FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

五、分組與聚合

-- 子查詢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- CTE (WITH，MySQL 8.0+、PostgreSQL、SQL Server)
WITH high_value AS (
    SELECT user_id FROM orders WHERE amount > 1000
)
SELECT * FROM users WHERE id IN (SELECT user_id FROM high_value);

七、分頁查詢

深分頁優化：避免 LIMIT 1000000, 10 全表掃描。改用「延遲關聯」或「游標分頁」：