MySQL查询技巧

一、基础查询

SELECT语句

-- 查询所有列
SELECT * FROM table_name;

-- 查询指定列
SELECT column1, column2 FROM table_name;

-- 别名
SELECT column1 AS alias_name FROM table_name;

-- 去重
SELECT DISTINCT column FROM table_name;

条件查询

-- 基本条件
SELECT * FROM table WHERE column = 'value';
SELECT * FROM table WHERE column > 100;

-- 多条件
SELECT * FROM table WHERE condition1 AND condition2;
SELECT * FROM table WHERE condition1 OR condition2;

-- 范围查询
SELECT * FROM table WHERE column BETWEEN 10 AND 20;
SELECT * FROM table WHERE column IN (1, 2, 3);

-- 模糊查询
SELECT * FROM table WHERE column LIKE '%keyword%';
SELECT * FROM table WHERE column LIKE 'prefix%';

-- NULL判断
SELECT * FROM table WHERE column IS NULL;
SELECT * FROM table WHERE column IS NOT NULL;

二、排序和分组

排序

-- 升序
SELECT * FROM table ORDER BY column ASC;

-- 降序
SELECT * FROM table ORDER BY column DESC;

-- 多列排序
SELECT * FROM table ORDER BY column1 ASC, column2 DESC;

分组

-- 基本分组
SELECT column, COUNT(*) FROM table GROUP BY column;

-- 分组条件
SELECT column, COUNT(*) 
FROM table 
GROUP BY column 
HAVING COUNT(*) > 10;

-- 分组排序
SELECT column, COUNT(*) as cnt 
FROM table 
GROUP BY column 
ORDER BY cnt DESC;

三、聚合函数

-- 计数
SELECT COUNT(*) FROM table;
SELECT COUNT(DISTINCT column) FROM table;

-- 求和
SELECT SUM(column) FROM table;

-- 平均值
SELECT AVG(column) FROM table;

-- 最大最小值
SELECT MAX(column), MIN(column) FROM table;

四、连接查询

内连接

SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id;

左连接

SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id;

右连接

SELECT a.*, b.*
FROM table_a a
RIGHT JOIN table_b b ON a.id = b.a_id;

子查询

-- WHERE子查询
SELECT * FROM table 
WHERE column = (SELECT MAX(column) FROM table);

-- FROM子查询
SELECT * FROM (
    SELECT column, COUNT(*) as cnt 
    FROM table 
    GROUP BY column
) t WHERE cnt > 10;

-- EXISTS子查询
SELECT * FROM table_a a
WHERE EXISTS (SELECT 1 FROM table_b b WHERE b.a_id = a.id);

五、高级查询

分页查询

-- LIMIT分页
SELECT * FROM table LIMIT 0, 10;
SELECT * FROM table LIMIT 10 OFFSET 0;

-- 优化分页
SELECT * FROM table 
WHERE id > last_id 
ORDER BY id LIMIT 10;

UNION查询

-- 合并结果（去重）
SELECT column FROM table_a
UNION
SELECT column FROM table_b;

-- 合并结果（不去重）
SELECT column FROM table_a
UNION ALL
SELECT column FROM table_b;

CASE WHEN

SELECT 
    column,
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END AS alias
FROM table;

窗口函数

-- 排名
SELECT *, 
    ROW_NUMBER() OVER (ORDER BY column DESC) as rank
FROM table;

-- 分组排名
SELECT *,
    ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY column DESC) as rank
FROM table;

-- 累计求和
SELECT *,
    SUM(amount) OVER (ORDER BY date) as cumulative_sum
FROM table;

六、查询优化

使用索引

-- 确保WHERE条件使用索引列
SELECT * FROM table WHERE indexed_column = 'value';

-- 避免索引失效
-- 错误：使用函数
SELECT * FROM table WHERE YEAR(date_col) = 2023;

-- 正确：范围查询
SELECT * FROM table WHERE date_col BETWEEN '2023-01-01' AND '2023-12-31';

避免全表扫描

-- 避免SELECT *
SELECT column1, column2 FROM table;

-- 避免在WHERE中对字段进行表达式操作
-- 错误
SELECT * FROM table WHERE column + 1 = 10;
-- 正确
SELECT * FROM table WHERE column = 9;

-- 使用EXISTS代替IN
-- 数据量大时
SELECT * FROM table_a 
WHERE EXISTS (SELECT 1 FROM table_b WHERE table_b.id = table_a.id);

查询计划分析

-- 查看执行计划
EXPLAIN SELECT * FROM table WHERE column = 'value';

-- 关注指标
-- type：ALL(全表扫描)、index、range、ref、const
-- key：使用的索引
-- rows：扫描行数
-- Extra：Using index、Using where等

七、常见场景

行列转换

-- 行转列
SELECT 
    id,
    MAX(CASE WHEN type = 'A' THEN value END) AS A,
    MAX(CASE WHEN type = 'B' THEN value END) AS B
FROM table
GROUP BY id;

分组取TopN

SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY score DESC) as rank
    FROM table
) t WHERE rank <= 3;

查找重复数据

SELECT column, COUNT(*) 
FROM table 
GROUP BY column 
HAVING COUNT(*) > 1;

八、总结

MySQL查询技巧：

• 熟练使用条件、排序、分组
• 掌握连接查询和子查询
• 善用窗口函数处理复杂逻辑
• 关注查询性能，合理使用索引
• 使用EXPLAIN分析查询计划

掌握这些技巧可以高效地进行数据查询和分析。