MySQL 高级查询

子查询

1. 基本子查询

-- 在WHERE子句中使用子查询
SELECT *
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- 在FROM子句中使用子查询
SELECT t.department, AVG(t.salary) as avg_salary
FROM (
    SELECT * FROM employees
    WHERE status = 'active'
) t
GROUP BY t.department;

2. 相关子查询

-- 查找每个部门工资最高的员工
SELECT *
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e1.department = e2.department
);

高级连接

1. 自连接

-- 查找所有员工及其管理者
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

2. 多表连接

-- 连接多个表
SELECT 
    c.name as customer,
    o.order_date,
    p.name as product
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

窗口函数

1. 排名函数

-- ROW_NUMBER(): 唯一排名
SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- RANK(): 相同值相同排名，跳过重复的排名
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

2. 分析函数

-- 计算累计总和
SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;

-- 计算移动平均
SELECT 
    date,
    amount,
    AVG(amount) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg
FROM sales;

视图

1. 创建视图

-- 创建简单视图
CREATE VIEW employee_summary AS
SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department;

-- 创建带条件的视图
CREATE VIEW high_salary_employees AS
SELECT *
FROM employees
WHERE salary > 50000;

2. 使用视图

-- 查询视图
SELECT * FROM employee_summary;

-- 基于视图创建新视图
CREATE VIEW dept_high_salary AS
SELECT department, COUNT(*) as high_salary_count
FROM high_salary_employees
GROUP BY department;

存储过程

1. 创建存储过程

-- 简单存储过程
DELIMITER //
CREATE PROCEDURE get_employee_count()
BEGIN
    SELECT COUNT(*) FROM employees;
END //
DELIMITER ;

-- 带参数的存储过程
DELIMITER //
CREATE PROCEDURE get_employees_by_dept(
    IN dept_name VARCHAR(50)
)
BEGIN
    SELECT * FROM employees
    WHERE department = dept_name;
END //
DELIMITER ;

2. 调用存储过程

-- 调用存储过程
CALL get_employee_count();

-- 使用参数调用
CALL get_employees_by_dept('IT');

触发器

-- 创建触发器
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id)
    VALUES ('INSERT', NEW.id);
END;

事务和锁

-- 事务示例
START TRANSACTION;

UPDATE accounts SET balance = balance - 100
WHERE id = 1;

UPDATE accounts SET balance = balance + 100
WHERE id = 2;

-- 检查余额是否合法
IF (SELECT balance FROM accounts WHERE id = 1) >= 0 THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

-- 使用锁
SELECT * FROM accounts
WHERE id = 1
FOR UPDATE;

性能优化

1. 索引优化

-- 创建复合索引
CREATE INDEX idx_name_email ON users(name, email);

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users
WHERE name = 'John' AND email LIKE 'john%';

2. 查询优化

-- 使用EXISTS代替IN
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c
    WHERE c.id = o.customer_id
    AND c.status = 'active'
);

-- 使用UNION ALL代替UNION
SELECT * FROM sales_2021
UNION ALL
SELECT * FROM sales_2022;

最佳实践

1. 使用适当的索引

   • 为常用查询条件创建索引
   • 避免过度索引
   • 定期维护索引

2. 优化查询性能

   • 只选择需要的列
   • 使用适当的连接类型
   • 避免子查询滥用

3. 合理使用存储过程

   • 封装复杂业务逻辑
   • 提高代码重用性
   • 注意维护成本

练习题

1. 编写复杂的多表查询
2. 创建和优化存储过程
3. 实现触发器实现业务逻辑

下一步

学习数据库优化和性能调优的更多技巧。