1. 引言
总结一些技巧
2. 查询技巧
2.1 子查询
子查询是嵌套在其他SQL查询中的查询。它们可以用在SELECT、INSERT、UPDATE或DELETE语句中，以及在WHERE、FROM或SELECT子句中。
示例：
SELECT employee_id, name, sales_amount
FROM sales
WHERE sales_amount > (
SELECT AVG(sales_amount)
FROM sales
GROUP BY department_id
HAVING department_id = sales.department_id
);
2.2 连接
连接（Joins）是SQL中用于合并两个或多个表的强大工具。理解不同类型的连接及其用途对于构建有效的SQL查询至关重要。
示例：左连接和右连接
-- 左连接示例
SELECT employees.name, sales.sales_amount
FROM employees
LEFT JOIN sales ON employees.employee_id = sales.employee_id;
-- 右连接示例
SELECT employees.name, sales.sales_amount
FROM employees
RIGHT JOIN sales ON employees.employee_id = sales.employee_id;
3. 性能优化技巧
3.1 索引
索引是提高数据库查询性能的关键工具。合理的索引可以显著减少数据检索的时间，但不恰当的索引可能导致性能下降。
示例：创建和使用索引
-- 创建索引
CREATE INDEX idx_sales_amount ON sales(sales_amount);
-- 使用索引的查询
SELECT * FROM sales WHERE sales_amount > 10000;
3.2 查询优化实践
优化SQL查询是提高数据库性能的重要方面。这包括选择合适的数据类型、合理使用聚合函数、避免SELECT *等。
示例：优化GROUP BY
-- 优化前
SELECT department_id, AVG(sales_amount)
FROM sales
GROUP BY department_id;
-- 优化后
SELECT department_id, AVG(sales_amount)
FROM sales
WHERE sales_amount > 1000
GROUP BY department_id;
4. 窗口函数
窗口函数允许对数据集的子集进行计算，这在进行复杂的数据分析时非常有用。
4.1 窗口函数的应用
示例：计算移动平均
SELECT sales_date, sales_amount,
AVG(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM sales;
5. 递归查询
递归查询是处理层次或递归数据模型的强大工具，如树结构或图结构。
5.1 使用递归WITH查询
示例：构建组织结构树
WITH RECURSIVE org_chart AS (
SELECT employee_id, manager_id, name
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.name
FROM employees e
INNER JOIN org_chart o ON o.employee_id = e.manager_id
)
SELECT * FROM org_chart;
6. 动态SQL和存储过程
动态SQL和存储过程可以极大地增强SQL的灵活性和功能，特别是在处理复杂的业务逻辑时。
6.1 动态SQL的使用
示例：动态生成查询
SET @table_name = 'sales';
SET @sql_query = CONCAT('SELECT * FROM ', @table_name);
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
6.2 存储过程的构建
示例：创建和调用存储过程
-- 创建存储过程
CREATE PROCEDURE GetTopSales()
BEGIN
SELECT * FROM sales ORDER BY sales_amount DESC LIMIT 10;
END;
-- 调用存储过程
CALL GetTopSales();
7. 集合运算
MySQL 提供了一系列集合运算符,可以帮助我们在多个查询结果之间进行复杂的组合和处理。
UNION 和 UNION ALL
UNION 和 UNION ALL 可以将多个查询结果合并为一个结果集,前者会自动去重,后者不会。
但是需要查询的字段是一一对应的，不然无法正常使用
SELECT ；
UNION
SELECT ;
INTERSECT 和 EXCEPT
INTERSECT 可以找出两个查询结果的交集,EXCEPT 可以找出第一个查询结果中存在但第二个查询结果中不存在的部分。
SELECT customer_id FROM orders_2022
INTERSECT
SELECT customer_id FROM orders_2023;
SELECT customer_id FROM orders_2022
EXCEPT
SELECT customer_id FROM orders_2023;
WITH 子句
WITH 子句允许我们定义一个临时表,然后在主查询中使用它。这在处理复杂查询时非常有用。
WITH order_summary AS (
SELECT
customer_id,
SUM(order_amount) AS total_order_amount
FROM
orders
GROUP BY
customer_id
)
SELECT
o.customer_id,
o.total_order_amount,
RANK() OVER (ORDER BY o.total_order_amount DESC) AS customer_rank
FROM
order_summary o;
8.时间处理（经常使用）
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
9.通配符通配符
只能用于文本字段:
% 匹配 >=0 个任意字符；_ 匹配 ==1 个任意字符；[ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。使用 Like 来进行通配符匹配。
SELECT *
FROM mytable
WHERE col LIKE '[^ABC]%'; -- 不以 ABC开头的字段