MYSQL 高级 SQL 技巧

高级 SQL 技巧

以下是一些高级 SQL 技巧，可以帮助优化查询、提高性能并解决复杂的数据处理问题。

使用窗口函数

窗口函数允许在查询结果的行上进行计算，而不会减少行数。常见的窗口函数包括 ROW_NUMBER()、RANK()、DENSE_RANK() 和聚合函数如 SUM() 与 OVER() 子句结合使用。

SELECT 
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

窗口函数还可以分区计算，例如按部门分组计算薪水排名：

SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_salary_rank
FROM employees;

递归查询

递归查询通过 WITH RECURSIVE 实现，常用于处理层级数据，如组织结构或树形数据。

WITH RECURSIVE employee_hierarchy AS (
    -- 基础查询：选择顶层管理者
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：选择下属
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

动态 SQL

动态 SQL 允许在运行时构建和执行 SQL 语句，适用于灵活的查询需求。

DECLARE @sql NVARCHAR(MAX);
DECLARE @table_name NVARCHAR(50) = 'employees';

SET @sql = N'SELECT * FROM ' + @table_name;
EXEC sp_executesql @sql;

使用临时表和 CTE

临时表和公共表表达式（CTE）可以简化复杂查询，提高可读性和性能。

-- 使用 CTE
WITH high_salary_employees AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary_employees;

-- 使用临时表
CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees WHERE salary > 100000;

SELECT * FROM temp_employees;
DROP TEMPORARY TABLE temp_employees;

索引优化

创建合适的索引可以显著提高查询性能，尤其是在大表上。

-- 单列索引
CREATE INDEX idx_employee_name ON employees(name);

-- 复合索引
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

使用 CASE 表达式

CASE 表达式可以实现条件逻辑，类似于编程语言中的 if-else。

SELECT 
    employee_id,
    salary,
    CASE 
        WHEN salary > 100000 THEN 'High'
        WHEN salary > 50000 THEN 'Medium'
        ELSE 'Low'
    END as salary_level
FROM employees;

事务和锁

事务和锁机制确保数据的一致性和并发控制。

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

存储过程和触发器

存储过程和触发器封装复杂逻辑，减少网络传输并提高安全性。

-- 存储过程
CREATE PROCEDURE increase_salary(IN emp_id INT, IN amount DECIMAL)
BEGIN
    UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
END;

-- 触发器
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot decrease';
    END IF;
END;

使用 JSON 函数

现代数据库支持 JSON 数据类型和相关函数，方便存储和查询半结构化数据。

-- 从 JSON 字段提取数据
SELECT 
    employee_id,
    JSON_EXTRACT(profile, '$.skills') as skills
FROM employees;

-- 更新 JSON 字段
UPDATE employees
SET profile = JSON_SET(profile, '$.skills', '["SQL","Python"]')
WHERE employee_id = 1;

这些高级技巧可以根据实际需求灵活组合使用，以解决复杂的 SQL 查询和数据处理问题。