MySQL 查询构建

SELECT 语法

包含过滤、排序和分组的核心 SELECT 模式。

-- Basic SELECT
SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Aggregate with GROUP BY / HAVING
SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING cnt > 5
ORDER BY avg_sal DESC;

-- DISTINCT values
SELECT DISTINCT country FROM customers;

-- Column alias
SELECT price * quantity AS total_amount FROM order_items;

INSERT / UPDATE / DELETE

用于写入和修改数据的 DML 语句。

-- INSERT single row
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

-- INSERT multiple rows
INSERT INTO tags (name, slug) VALUES
  ('MySQL', 'mysql'),
  ('PostgreSQL', 'postgresql'),
  ('Redis', 'redis');

-- INSERT … ON DUPLICATE KEY UPDATE (upsert)
INSERT INTO page_views (page_id, views)
VALUES (42, 1)
ON DUPLICATE KEY UPDATE views = views + 1;

-- UPDATE with JOIN
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.discount = 0.10
WHERE c.tier = 'gold';

-- DELETE with subquery
DELETE FROM sessions
WHERE user_id IN (
  SELECT id FROM users WHERE status = 'banned'
);

JOIN 连接

INNER、LEFT、RIGHT、CROSS 和自连接。

-- INNER JOIN
SELECT o.id, u.name, o.total
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN (keep all left rows)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Multiple JOINs
SELECT o.id, u.name, p.title, oi.qty
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN users u ON o.user_id = u.id
JOIN products p ON oi.product_id = p.id;

-- Self JOIN (employee hierarchy)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

子查询

关联子查询、IN/EXISTS 和派生表。

-- Subquery in WHERE (IN)
SELECT name FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE parent_id = 1
);

-- Correlated subquery (EXISTS)
SELECT c.name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
);

-- Derived table (inline view)
SELECT dept, avg_sal
FROM (
  SELECT department AS dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE avg_sal > 60000;

-- Scalar subquery in SELECT
SELECT name,
  (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_cnt
FROM users u;

CASE 表达式

用于条件逻辑的简单和搜索式 CASE 表达式。

-- Searched CASE
SELECT name, salary,
  CASE
    WHEN salary < 40000 THEN 'Junior'
    WHEN salary < 80000 THEN 'Mid'
    ELSE 'Senior'
  END AS level
FROM employees;

-- Simple CASE
SELECT name,
  CASE status
    WHEN 1 THEN 'Active'
    WHEN 0 THEN 'Inactive'
    ELSE 'Unknown'
  END AS status_label
FROM users;

-- CASE inside aggregate (pivot)
SELECT
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees;

常用子句参考