1. 窗口函数
窗函数允许在查询结果的每一行上进行计算，而不需要将数据分组。这使得我们可以计算累积总和、排名等。
SELECT employee_id,salary,RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
2. 公用表表达式 (CTE)
CTE 提供了一种更清晰的方式来编写复杂查询，可以使查询更具可读性，并允许递归查询。
WITH SalesCTE AS (SELECT salesperson_id,SUM(sale_amount) AS total_salesFROM salesGROUP BY salesperson_id
)
SELECT salesperson_id,total_sales
FROM SalesCTE
WHERE total_sales > 10000;
3. 子查询
使用子查询，可以在主查询中嵌套其他查询。这在过滤、聚合和复杂条件下非常有用。
SELECT product_id,product_name
FROM products
WHERE product_id IN (SELECT product_id FROM order_details WHERE quantity > 10);
4. 使用索引优化查询
在需要频繁查询的列上创建索引可以显著提高查询性能。使用合适的索引类型（如 B-tree、Hash 索引）能带来更大的性能提升。
CREATE INDEX idx_employee_name ON employees (last_name, first_name);
5. 合并查询 (UNION)
使用 UNION 或 UNION ALL 合并结果集，可以在一次查询中获取不同来源的数据。
SELECT employee_id, employee_name FROM full_time_employees
UNION
SELECT employee_id, employee_name FROM part_time_employees;
6. JSON 和 XML 数据处理
现代数据库系统支持 JSON 和 XML 数据格式。利用这些格式，你可以直接在 SQL 查询中操作这些数据，进行筛选和聚合。
SELECT json_extract(data, '$.field_name') AS field_value
FROM json_table;
7. 动态 SQL
在一些情况下，需要根据不同的条件动态生成和执行 SQL 语句。可以使用存储过程和函数来实现。
CREATE PROCEDURE DynamicSearch(IN searchTerm VARCHAR(255))
BEGINSET @sql = CONCAT('SELECT * FROM employees WHERE first_name LIKE ', searchTerm);PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END;
8. 事务管理
使用事务可以保证数据一致性和完整性。确保在业务逻辑中正确使用 BEGIN, COMMIT, 和 ROLLBACK。
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- 或 ROLLBACK; 以避免失败
9. 分区表
在处理大数据集时，可以将表分区，优化查询性能并简化管理。
CREATE TABLE sales (sale_id INT,sale_date DATE,amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2021 VALUES LESS THAN (2021),PARTITION p2022 VALUES LESS THAN (2022)
);
10. 数据分析和聚合
使用复杂的聚合和分析技术，例如计算同比增长、移动平均等。
SELECT DATE(sale_date) AS sale_day,SUM(sale_amount) AS total_sales,LAG(SUM(sale_amount), 1) OVER (ORDER BY sale_date) AS previous_day_sales
FROM daily_sales
GROUP BY sale_day;
11. 使用视图
视图是以 SELECT 查询为基础的虚拟表。使用视图可以简化复杂查询，增强数据安全性。
CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 50000;
12. SQL 优化技巧
- **避免 SELECT ***：明确列名以减少不必要的 I/O 和内存使用。
- 使用 EXISTS 替代 IN：在子查询中，
EXISTS往往比IN更高效。 - 定期进行统计信息更新：让数据库管理系统优化查询。