萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

精妙 SQL 語句收藏集:建表、索引、視圖、JOIN、子查詢、分頁、條件邏輯與跨庫拷貝

一、DDL 基礎操作

操作SQL 範例
建庫CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
刪庫DROP DATABASE db_name;
建表 (完整)CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
依表建表 (結構)CREATE TABLE t_new LIKE t_old;
依查詢建表 (結構+資料)CREATE TABLE t_new AS SELECT id, name FROM t_old WHERE status=1;
刪表DROP TABLE IF EXISTS t;
加欄位ALTER TABLE t ADD COLUMN email VARCHAR(100) AFTER name;
加主鍵ALTER TABLE t ADD PRIMARY KEY (id);
刪主鍵ALTER TABLE t DROP PRIMARY KEY;
建索引CREATE UNIQUE INDEX uk_email ON t(email);
刪索引DROP INDEX uk_email ON t;
建視圖CREATE VIEW v_active_users AS SELECT id, name FROM t WHERE status=1;
刪視圖DROP VIEW IF EXISTS v_active_users;

二、DML 核心語句

-- 查詢
SELECT * FROM t WHERE name LIKE '%keyword%';

-- 插入
INSERT INTO t (name, email) VALUES ('John', 'john@example.com');

-- 刪除
DELETE FROM t WHERE id = 10;

-- 更新
UPDATE t SET status=2 WHERE id = 10;

-- 聚合
SELECT COUNT(*) AS total, SUM(amount) AS sum_amt, AVG(score) AS avg_score, MAX(created_at) AS latest, MIN(created_at) AS earliest FROM orders;

三、高級集合運算

運算關鍵字行為去重
聯集UNION / UNION ALL合併結果集UNION 去重 / ALL 保留重複
差集EXCEPT / EXCEPT ALL (MySQL 8.0+)在第一集不在第二集同
交集INTERSECT / INTERSECT ALL (MySQL 8.0+)兩集合皆有同

注意:參與運算的 SELECT 欄位數、類型、順序**必須一致**。

四、外連接完全圖解

-- LEFT JOIN:左表全保留
SELECT a.id, a.name, b.order_no
FROM users a
LEFT JOIN orders b ON a.id = b.user_id;

-- RIGHT JOIN:右表全保留
SELECT a.id, a.name, b.order_no
FROM users a
RIGHT JOIN orders b ON a.id = b.user_id;

-- FULL JOIN (MySQL 不支援,用 UNION 模擬)
SELECT * FROM a LEFT JOIN b ON a.id=b.id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id=b.id;

LEFT JOIN 找「只在左表」

SELECT a.id, a.name
FROM users a
LEFT JOIN orders b ON a.id = b.user_id
WHERE b.user_id IS NULL;  -- b.user_id 必須 NOT NULL

五、子查詢與衍生表

-- IN 子查詢
SELECT * FROM users WHERE id IN (SELECT user_id FROM vip_members);

-- 衍生表 (內聯視圖)
SELECT * FROM (SELECT id, name FROM users WHERE status=1) AS active_users WHERE id > 100;

六、實用進階技巧

1. BETWEEN 邊界陷阱

-- 含邊界 [start, end]
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31 23:59:59'
-- 推薦半開區間
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

2. 條件邏輯 IF / CASE

SELECT IF(priv=1, 'admin', 'guest') AS role FROM users;
SELECT CASE WHEN score>=90 THEN 'A' WHEN score>=60 THEN 'B' ELSE 'C' END AS grade FROM exams;

3. 索引提示 (強制/忽略)

SELECT * FROM t USE INDEX (idx_name) WHERE name='John';
SELECT * FROM t IGNORE INDEX (idx_bad) WHERE name='John';

4. 跨資料庫拷貝 (Access/同機)

INSERT INTO db1.t (a,b) SELECT a,b FROM db2.t WHERE cond;

5. 分頁優化 (延遲關聯)

SELECT * FROM t WHERE id IN (SELECT id FROM t WHERE cond ORDER BY id LIMIT 100000, 20);

七、命名規範建議

  • 資料庫/表/欄位:snake_case、小寫、名詞單數 (user_order)
  • 主鍵:id (BIGINT UNSIGNED)
  • 外鍵:user_id, order_id
  • 時間:created_at, updated_at, deleted_at
  • 布林:is_deleted, has_paid (TINYINT(1))
  • 索引:idx_欄位名, uk_欄位名 (唯一)
copyright © 萬盛學電腦網 all rights reserved