查询语句

多表查询

内连接 (INNER JOIN)

SELECT users.username, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

SELECT u.username, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

左连接 (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;

交叉连接 (CROSS JOIN)

SELECT * FROM users
CROSS JOIN departments;

自连接

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

子查询

标量子查询

SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

列子查询

SELECT * FROM users
WHERE department_id IN (SELECT id FROM departments WHERE name = 'IT');

行子查询

SELECT * FROM users
WHERE (department_id, salary) = (
    SELECT department_id, MAX(salary)
    FROM users
    GROUP BY department_id
);

表子查询

SELECT * FROM (
    SELECT username, age FROM users
) AS temp
WHERE age > 25;

EXISTS 子查询

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

联合查询

UNION

SELECT username FROM users
UNION
SELECT name FROM customers;

UNION ALL

SELECT username FROM users
UNION ALL
SELECT name FROM customers;

UNION vs UNION ALL

操作符	说明
UNION	去重合并
UNION ALL	直接合并（不去重）

高级查询

CASE WHEN

SELECT
    username,
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age >= 18 AND age < 60 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users;

SELECT
    username,
    CASE gender
        WHEN 'M' THEN '男'
        WHEN 'F' THEN '女'
        ELSE '未知'
    END AS gender_name
FROM users;

IF 函数

SELECT
    username,
    IF(age >= 18, '成年', '未成年') AS status
FROM users;

IFNULL 函数

SELECT
    username,
    IFNULL(phone, '未填写') AS phone
FROM users;

NULLIF 函数

SELECT NULLIF(10, 10);

窗口函数

MySQL 8.0+ 支持窗口函数。

ROW_NUMBER

SELECT
    username,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM users;

RANK 和 DENSE_RANK

SELECT
    username,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM users;

分区窗口函数

SELECT
    username,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM users;

聚合窗口函数

SELECT
    username,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM users;

LAG 和 LEAD

SELECT
    username,
    score,
    LAG(score, 1) OVER (ORDER BY score) AS prev_score,
    LEAD(score, 1) OVER (ORDER BY score) AS next_score
FROM users;

公用表表达式 (CTE)

基本 CTE

WITH user_stats AS (
    SELECT
        department_id,
        COUNT(*) AS count,
        AVG(salary) AS avg_salary
    FROM users
    GROUP BY department_id
)
SELECT
    d.name,
    u.count,
    u.avg_salary
FROM user_stats u
JOIN departments d ON u.department_id = d.id;

递归 CTE

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

查询优化提示

使用索引

SELECT * FROM users FORCE INDEX (idx_username) WHERE username = '张三';
SELECT * FROM users USE INDEX (idx_username) WHERE username = '张三';
SELECT * FROM users IGNORE INDEX (idx_username) WHERE username = '张三';

SQL_CACHE

SELECT SQL_CACHE * FROM users;
SELECT SQL_NO_CACHE * FROM users;

下一步学习

• 索引优化 - 学习索引优化
• 事务处理 - 掌握事务处理