lesson38：MySQL数据库核心操作详解：从基础查询到高级应用

引言

一、条件查询：精准筛选数据

1.1 基本语法

1.2 比较运算符

1.3 逻辑运算符

1.4 特殊条件查询

1.4.1 模糊查询（LIKE）

1.4.2 IN和NOT IN

1.4.3 BETWEEN AND

1.4.4 IS NULL和IS NOT NULL

二、聚合函数：数据统计与分析

2.1 常用聚合函数

2.2 COUNT()的使用

2.3 SUM()和AVG()

2.4 MAX()和MIN()

2.5 聚合函数与DISTINCT

三、分组查询：GROUP BY与HAVING

3.1 GROUP BY基础

3.2 HAVING子句

3.3 GROUP BY与多个列

四、排序：ORDER BY

4.1 基本排序

4.2 多列排序

4.3 排序与聚合函数

五、分页查询：LIMIT

5.1 基本语法

5.2 实际应用

5.3 分页公式

六、去重：DISTINCT

6.1 基本用法

6.2 多列去重

6.3 DISTINCT与聚合函数

七、综合应用：多操作组合

7.1 示例1：复杂条件查询

7.2 示例2：分组统计与排序

7.3 示例3：分页查询热门城市

八、最佳实践与性能优化

8.1 索引优化

8.2 查询优化

8.3 分组和聚合优化

8.4 分页优化

九、常见问题与解决方案

9.1 NULL值处理

9.2 数据类型不匹配

9.3 分组与排序的顺序

十、总结

附录：常用查询示例

引言

MySQL作为一款开源的关系型数据库管理系统，凭借其高效、稳定、易用的特性，成为Web开发领域中最受欢迎的数据库之一。无论是小型网站还是大型企业应用，MySQL都扮演着至关重要的角色。本文将详细介绍MySQL中条件查询、聚合函数、分页、排序、分组和去重等核心操作，帮助读者从基础到进阶，全面掌握MySQL的查询技巧。

一、条件查询：精准筛选数据

条件查询是MySQL中最常用的操作之一，通过WHERE子句可以实现对数据的精准筛选。

1.1 基本语法

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件表达式;

1.2 比较运算符

运算符	描述	示例
=	等于	WHERE age = 18
<>	不等于	WHERE age <> 18
>	大于	WHERE salary > 5000
<	小于	WHERE score < 60
>=	大于等于	WHERE quantity >= 100
<=	小于等于	WHERE price <= 99.99

1.3 逻辑运算符

AND：同时满足多个条件

SELECT * FROM students WHERE age > 18 AND gender = '男';

OR：满足任意一个条件

SELECT * FROM products WHERE price < 50 OR category = '电子产品';

NOT：取反

SELECT * FROM orders WHERE NOT status = '已取消';

1.4 特殊条件查询

1.4.1 模糊查询（LIKE）

%：匹配任意字符（包括零个）
_：匹配单个字符

-- 查询姓张的学生
SELECT * FROM students WHERE name LIKE '张%';-- 查询第二个字是"小"的学生
SELECT * FROM students WHERE name LIKE '_小%';

1.4.2 IN和NOT IN

-- 查询id为1、3、5的学生
SELECT * FROM students WHERE id IN (1, 3, 5);-- 查询不在北京、上海的学生
SELECT * FROM students WHERE city NOT IN ('北京', '上海');

1.4.3 BETWEEN AND

-- 查询成绩在80到90之间的学生
SELECT * FROM students WHERE score BETWEEN 80 AND 90;

1.4.4 IS NULL和IS NOT NULL

-- 查询没有填写邮箱的学生
SELECT * FROM students WHERE email IS NULL;-- 查询填写了电话的学生
SELECT * FROM students WHERE phone IS NOT NULL;

二、聚合函数：数据统计与分析

聚合函数用于对数据进行统计和计算，返回单个结果值。

2.1 常用聚合函数

函数	描述
COUNT()	统计记录数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

2.2 COUNT()的使用

-- 统计学生总数
SELECT COUNT(*) AS student_count FROM students;-- 统计有邮箱的学生数
SELECT COUNT(email) AS email_count FROM students;

2.3 SUM()和AVG()

-- 计算所有学生的总分
SELECT SUM(score) AS total_score FROM students;-- 计算平均分
SELECT AVG(score) AS avg_score FROM students;

2.4 MAX()和MIN()

-- 查询最高和最低分
SELECT MAX(score) AS max_score, MIN(score) AS min_score FROM students;

2.5 聚合函数与DISTINCT

-- 统计不同城市的数量
SELECT COUNT(DISTINCT city) AS city_count FROM students;

三、分组查询：GROUP BY与HAVING

3.1 GROUP BY基础

GROUP BY用于将数据按照指定列进行分组，通常与聚合函数一起使用。

-- 按性别分组统计学生数量
SELECT gender, COUNT(*) AS count FROM students GROUP BY gender;-- 按城市分组计算平均分
SELECT city, AVG(score) AS avg_score FROM students GROUP BY city;

3.2 HAVING子句

HAVING用于过滤分组后的结果，类似于WHERE，但WHERE用于分组前过滤，HAVING用于分组后过滤。

-- 找出平均分大于85的城市
SELECT city, AVG(score) AS avg_score 
FROM students 
GROUP BY city 
HAVING avg_score > 85;-- 找出学生数量大于10人的城市
SELECT city, COUNT(*) AS count 
FROM students 
GROUP BY city 
HAVING count > 10;

3.3 GROUP BY与多个列

-- 按城市和性别分组统计
SELECT city, gender, COUNT(*) AS count 
FROM students 
GROUP BY city, gender;

四、排序：ORDER BY

4.1 基本排序

-- 按分数升序排列
SELECT * FROM students ORDER BY score ASC;-- 按分数降序排列
SELECT * FROM students ORDER BY score DESC;

4.2 多列排序

-- 先按城市升序，再按分数降序
SELECT * FROM students ORDER BY city ASC, score DESC;

4.3 排序与聚合函数

-- 按城市分组计算平均分并排序
SELECT city, AVG(score) AS avg_score 
FROM students 
GROUP BY city 
ORDER BY avg_score DESC;

五、分页查询：LIMIT

在处理大量数据时，分页查询非常重要，可以提高查询效率和用户体验。

5.1 基本语法

SELECT * FROM 表名 LIMIT [offset,] rows;

offset：起始位置（可选，默认为0）
rows：返回行数

5.2 实际应用

-- 获取前10条数据
SELECT * FROM students LIMIT 10;-- 获取第11-20条数据
SELECT * FROM students LIMIT 10, 10;-- 按分数降序，获取前5名学生
SELECT * FROM students ORDER BY score DESC LIMIT 5;

5.3 分页公式

对于第n页，每页显示m条数据：

SELECT * FROM students LIMIT (n-1)*m, m;

-- 第3页，每页10条
SELECT * FROM students LIMIT 20, 10;

六、去重：DISTINCT

DISTINCT用于返回唯一不同的值。

6.1 基本用法

-- 查询所有不同的城市
SELECT DISTINCT city FROM students;

6.2 多列去重

-- 查询不同城市和性别的组合
SELECT DISTINCT city, gender FROM students;

6.3 DISTINCT与聚合函数

-- 统计不同城市的数量
SELECT COUNT(DISTINCT city) AS city_count FROM students;

七、综合应用：多操作组合

在实际应用中，通常需要将多个操作组合使用。

7.1 示例1：复杂条件查询

-- 查询北京或上海的女生，分数在80-90之间，按分数降序排列
SELECT * FROM students 
WHERE (city = '北京' OR city = '上海') 
AND gender = '女' 
AND score BETWEEN 80 AND 90 
ORDER BY score DESC;

7.2 示例2：分组统计与排序

-- 按城市分组，统计每个城市的男生人数和平均分，只显示平均分大于80的，按平均分降序排列
SELECT city, 
COUNT(*) AS male_count, 
AVG(score) AS avg_score 
FROM students 
WHERE gender = '男' 
GROUP BY city 
HAVING avg_score > 80 
ORDER BY avg_score DESC;

7.3 示例3：分页查询热门城市

-- 查询学生数量最多的前5个城市，按数量降序排列
SELECT city, COUNT(*) AS count 
FROM students 
GROUP BY city 
ORDER BY count DESC 
LIMIT 5;

八、最佳实践与性能优化

8.1 索引优化

为WHERE、JOIN、ORDER BY等子句中的列创建索引
避免在索引列上使用函数或表达式
合理设计复合索引

8.2 查询优化

只查询需要的列，避免使用SELECT *
使用EXPLAIN分析查询执行计划
避免在WHERE子句中使用!=、<>、NOT IN等操作符
合理使用连接查询代替子查询

8.3 分组和聚合优化

尽量缩小分组前的数据量
避免在HAVING中使用复杂条件，可先通过WHERE过滤

8.4 分页优化

对于大数据量分页，使用"延迟关联"或"书签"技术
避免使用LIMIT offset过大的值

九、常见问题与解决方案

9.1 NULL值处理

NULL与任何值比较都返回NULL
使用IS NULL/IS NOT NULL判断NULL值
聚合函数通常忽略NULL值

9.2 数据类型不匹配

确保比较和计算的数据类型一致
使用CAST()或CONVERT()进行类型转换

9.3 分组与排序的顺序

GROUP BY子句在WHERE之后，ORDER BY之前
聚合函数不能直接用于WHERE子句

十、总结

本文详细介绍了MySQL中的条件查询、聚合函数、分组、排序、分页和去重等核心操作。这些操作是MySQL查询的基础，也是进行数据分析和业务开发的必备技能。通过灵活组合这些操作，可以实现复杂的查询需求，高效地处理和分析数据。

在实际应用中，除了掌握基本语法外，还需要关注查询性能优化，合理设计索引，编写高效的SQL语句。同时，要注意处理特殊情况如NULL值、数据类型转换等，确保查询结果的准确性。

希望本文能帮助读者全面理解MySQL的查询操作，为后续的数据库开发和优化打下坚实基础。在实际使用中，建议多练习、多总结，不断提升SQL编写能力。

附录：常用查询示例

查询每个城市的男生和女生数量及平均分

SELECT 
city,
gender,
COUNT(*) AS count,
AVG(score) AS avg_score
FROM students
GROUP BY city, gender
ORDER BY city ASC, gender ASC;

查询成绩排名前10%的学生

SELECT * FROM students
WHERE score >= (SELECT AVG(score) FROM students)
ORDER BY score DESC
LIMIT (SELECT COUNT(*) * 0.1 FROM students);

查询连续三个月没有订单的用户

SELECT user_id, username
FROM users
WHERE user_id NOT IN (
SELECT DISTINCT user_id
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
);

通过不断实践这些查询技巧，结合具体业务场景进行灵活运用，相信你一定能成为MySQL查询的高手。