MySQL 单表查询

1. SQL 查询基本语法

在 MySQL 中，select 查询的基本语法顺序是固定的，不可更改。其结构如下：

select 字段列表          -- 选择要查询的字段，可以是一个或多个字段，用逗号分隔。
from 表名               -- 指定查询的数据来源表，可以是一个或多个表。
where 条件              -- 进行数据过滤的条件，筛选出符合条件的记录。
group by 分组字段       -- 按指定字段进行分组，通常用于聚合查询。
having 分组后的条件     -- 对分组后的结果进行过滤，仅保留符合条件的分组。
order by 排序字段       -- 按指定字段进行排序，可以按升序或降序排列。
limit 限制数量          -- 限制查询结果的返回行数或分页查询。

示例语法：

select 字段1, 字段2
from 表名
where 条件
group by 分组字段
having 分组条件
order by 排序字段 asc|desc
limit 偏移量, 行数;

2. SQL 查询语句执行顺序

SQL 查询语句在执行时，并不是按照我们书写的顺序执行的。下面是 SQL 查询的执行顺序，及每个限制条件的作用：

1. from：
   首先从指定的表中获取数据。这一步涉及从磁盘或内存中读取数据，并将其加载到 MySQL 的查询缓存中。

2. join (如果有)：
   如果查询中包含多个表的 join 操作，MySQL 会根据指定的连接条件将多个表连接起来。

3. where：
   在 join 之后，应用 where 条件来筛选数据。只有符合 where 条件的数据才会进入下一步。where 是对原始数据进行筛选，不能使用聚合函数。

4. group by：
   在筛选完数据后，如果有 group by 子句，MySQL 会按照指定字段对数据进行分组。这一步通常与聚合函数（如 count、sum、avg 等）一起使用。

5. 聚合函数 (如 sum、avg 等)：
   在分组后，应用聚合函数对每个分组的数据进行计算。例如，计算每个分组的总和、平均值、最大值等。

6. having：
   having 是在分组和聚合之后对结果进行进一步过滤的条件，常用于聚合函数的过滤。与 where 不同，having 是对分组后的数据进行过滤。

7. select：
   在前面的步骤中，数据已经被筛选、连接和分组。此时根据 select 子句中指定的字段，从结果中选择要返回的数据。

8. distinct (如果有)：
   如果查询中包含 distinct 关键字，重复的行将被去除，只保留唯一的行。

9. order by：
   在返回结果之前，根据指定的字段对数据进行排序。排序可以是升序（asc，默认）或降序（desc）。

10. limit：
    最后，如果查询中包含 limit 子句，只返回指定数量的行。常用于分页查询或限制结果数量。

综合示例和解读

select name, avg(score) as 平均分
from students
where age > 18
group by class
having avg(score) > 70
order by 平均分 desc
limit 5;

执行顺序说明：

1. from students：从 students 表中获取数据。
2. where age > 18：筛选出年龄大于 18 岁的学生。
3. group by class：按 class 字段对学生分组。
4. having avg(score) > 70：只保留平均分大于 70 的班级。
5. select name, avg(score) as 平均分：选择每个班级的名字和平均分作为结果。
6. order by 平均分 desc：按平均分降序排序。
7. limit 5：只返回前 5 个结果。

3. 查询所有数据

-- 使用 `select * from 表名;` 查询表中的所有数据
select * from 表名;

-- 输出示例
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 1  | 张三    | 20       |
| 2  | 李四    | 25       |
| 3  | 王五    | 30       |
+----+---------+----------+

-- 解释：
-- `select *` 会返回表中的所有字段和所有记录，通常用于查看整个表的数据结构和内容。
-- 在实际生产环境中，建议明确指定需要查询的字段，而不是直接使用 `*`，以减少不必要的数据传输。

4. 查询指定字段

-- 使用 `select 字段1, 字段2, ... from 表名;` 查询指定字段的数据
select 字段1, 字段2 from 表名;

-- 输出示例
+---------+----------+
| name    | age      |
+---------+----------+
| 张三    | 20       |
| 李四    | 25       |
| 王五    | 30       |
+---------+----------+

-- 解释：
-- 明确指定要查询的字段可以提高查询效率，减少数据库资源消耗。
-- 在生产环境中，按需查询字段是最佳实践，有助于优化性能。

5. 查询并限制条件（where）

-- 使用 `select 字段列表 from 表名 where 条件;` 查询满足条件的数据
select 字段1, 字段2 from 表名 where 条件;

-- 输出示例
+---------+----------+
| name    | age      |
+---------+----------+
| 张三    | 20       |
+---------+----------+

-- 解释：
-- `where` 子句用于筛选符合条件的数据。常见条件包括比较运算符（如 `=`、`>`、`<`）、逻辑运算符（如 `and`、`or`）以及范围查询（如 `between ... and ...`）。

6. 去除重复数据（distinct）

-- 使用 `select distinct 字段名 from 表名;` 去除重复数据
select distinct 字段名 from 表名;

-- 输出示例
+----------+
| age      |
+----------+
| 20       |
| 25       |
| 30       |
+----------+

-- 解释：
-- `distinct` 关键字用于去除查询结果中重复的行，仅保留唯一的值。
-- 通常用于过滤重复的记录，例如在统计不同年龄段时。

7. 区间查询 (between ... and ...)

-- 使用 `select 字段列表 from 表名 where 字段 between 最小值 and 最大值;` 进行区间查询
select 字段1, 字段2 from 表名 where age between 20 and 30;

-- 输出示例
+---------+----------+
| name    | age      |
+---------+----------+
| 张三    | 20       |
| 李四    | 25       |
| 王五    | 30       |
+---------+----------+

-- 解释：
-- `between ... and ...` 是一个闭区间查询，包含边界值。适用于查找在特定范围内的数据，例如年龄段、日期范围等。

8. 集合查询 (in/not in)

-- 使用 `select 字段列表 from 表名 where 字段 in (值1, 值2, ...);` 查询指定集合中的数据
select 字段1, 字段2 from 表名 where age in (20, 25);

-- 输出示例
+---------+----------+
| name    | age      |
+---------+----------+
| 张三    | 20       |
| 李四    | 25       |
+---------+----------+

-- 解释：
-- `in` 关键字用于筛选字段值在指定集合中的数据，`not in` 则用于筛选字段值不在指定集合中的数据。
-- 这种查询方式在筛选多个具体值时非常高效。

9. 模糊查询 (like)

-- 使用 `select 字段列表 from 表名 where 字段 like 模式;` 进行模糊查询
select 字段1, 字段2 from 表名 where name like '张%';

-- 输出示例
+---------+----------+
| name    | age      |
+---------+----------+
| 张三    | 20       |
+---------+----------+

-- 解释：
-- `like` 关键字用于模糊匹配，常见的通配符包括 `%`（匹配任意多个字符）和 `_`（匹配单个字符）。
-- 适用于姓名、地址等需要部分匹配的场景，例如查找以“张”开头的名字。

10. 使用别名 (as)

-- 使用 `select 字段 as 别名 from 表名;` 为查询结果中的字段指定别名
select name as 姓名, age as 年龄 from 表名;

-- 输出示例
+---------+----------+
| 姓名    | 年龄     |
+---------+----------+
| 张三    | 20       |
| 李四    | 25       |
| 王五    | 30       |
+---------+----------+

-- 解释：
-- `as` 关键字用于给字段或表指定别名，便于在查询结果中显示更友好的字段名称。
-- 别名可以使查询结果更加直观和易读，尤其是在生成报表时。

11. 排序查询 (order by)

-- 使用 `select 字段列表 from 表名 order by 字段1 [asc|desc], 字段2 [asc|desc];` 进行排序查询
select 字段1, 字段2 from 表名 order by age desc;

-- 输出示例
+---------+----------+
| name    | age      |
+---------+----------+
| 王五    | 30       |
| 李四    | 25       |
| 张三    | 20       |
+---------+----------+

-- 解释：
-- `order by` 子句用于对查询结果进行排序，`asc` 表示升序（默认），`desc` 表示降序。
-- 可以按多个字段进行排序，优先级从左到右。例如，可以按年龄降序，再按姓名升序。

12. 分页查询 (limit)

-- 使用 `select 字段列表 from 表名 limit 偏移量, 行数;` 进行分页查询
select 字段1, 字段2 from 表名 limit 0, 2;

-- 输出示例
+---------+----------+
| name    | age      |
+---------+----------+
| 张三    | 20       |
| 李四    | 25       |
+---------+----------+

-- 解释：
-- `limit` 子句用于限制返回结果的行数，通常用于分页。第一个参数表示偏移量（从 0 开始），第二个参数表示返回的行数。
-- 例如，`limit 0, 2` 表示从第 1 行开始返回 2 条记录。

13. 分组查询 (group by)

-- 使用 `select 字段, 聚合函数 from 表名 group by 分组字段;` 进行分组查询
select sex, avg(age) as 平均年龄 from 表名 group by sex;

-- 输出示例
+---------+----------+
| sex     | 平均年龄 |
+---------+----------+
| 男      | 22.5     |
| 女      | 25.0     |
+---------+----------+

-- 解释：
-- `group by` 子句用于将数据按指定字段分组，常与聚合函数（如 `count`、`sum`、`avg` 等）结合使用。
-- 分组查询的典型场景包括按性别、部门、城市等分组统计数据。

14. 聚合查询 (聚合函数)

-- 常见的聚合函数包括 `count`、`sum`、`avg`、`max`、`min`，用于对多行数据进行运算并返回一个结果
select count(*) as 总人数, avg(age) as 平均年龄, max(age) as 最大年龄, min(age) as 最小年龄 from 表名;

-- 输出示例
+---------+----------+----------+----------+
| 总人数  | 平均年龄 | 最大年龄 | 最小年龄 |
+---------+----------+----------+----------+
| 3       | 25.0     | 30       | 20       |
+---------+----------+----------+----------+

-- 解释：
-- 聚合函数用于对一组数据进行汇总计算，通常与分组查询结合使用。
-- `count` 计算记录总数，`sum` 计算数值字段的总和，`avg` 计算平均值，`max` 返回最大值，`min` 返回最小值。

15. 过滤分组结果 (having)

-- 使用 `select 字

段, 聚合函数 from 表名 group by 分组字段 having 聚合条件;` 过滤分组结果
select sex, avg(age) as 平均年龄 from 表名 group by sex having avg(age) > 20;

-- 输出示例
+---------+----------+
| sex     | 平均年龄 |
+---------+----------+
| 女      | 25.0     |
+---------+----------+

-- 解释：
-- `having` 子句用于对分组后的结果进行筛选，与 `where` 不同，`having` 可以使用聚合函数。
-- `where` 是在数据分组前进行过滤，而 `having` 是在数据分组后进行过滤。

16. 复杂条件查询 (and/or)

-- 使用 `select 字段列表 from 表名 where 条件1 and/or 条件2;` 进行复杂条件查询
select 字段1, 字段2 from 表名 where age > 20 and sex = '女';

-- 输出示例
+---------+----------+
| name    | age      |
+---------+----------+
| 李四    | 25       |
+---------+----------+

-- 解释：
-- `and` 和 `or` 逻辑运算符用于组合多个条件。`and` 需要所有条件同时满足，`or` 只需满足其中一个条件。
-- 在组合复杂条件时，可以使用括号来控制优先级。

17. 关于group by的理解

group by 的分组是按照字段中相同的内容进行分组。它会将表中具有相同值的记录归为一组，并对每组的数据进行进一步的计算或统计。

假设有一张学生表 students，内容如下：

id	name	sex	age	score
1	张三	男	20	85
2	李四	男	21	90
3	王五	女	22	88
4	赵六	男	20	78
5	钱七	女	21	91

使用 group by 按照 sex 分组

select sex, avg(score) as 平均分数 
from students 
group by sex;

执行后的结果：

sex	平均分数
男	84.33
女	89.5

解释：

1. group by sex 会将 sex 列中相同的值归为一组。

   • 所有 sex 为 "男" 的记录分为一组。
   • 所有 sex 为 "女" 的记录分为另一组。

2. 然后对每组的数据进行计算，示例中使用的是 avg(score)，计算每组的平均分数。

   • 男组：(85 + 90 + 78) / 3 = 84.33
   • 女组：(88 + 91) / 2 = 89.5

最终得到的结果是每个分组对应的计算值。

小结

• group by 是按照字段中相同值进行分组的。
• 每个分组中的数据都会被聚合到一起，通常与聚合函数（如 count、sum、avg 等）结合使用。
• 典型场景包括按部门、性别、城市等分组统计。

1. group by 中的字段与 select 中的字段关系

在使用 group by 时，select 中非聚合函数的字段必须在 group by 中出现，否则会产生不确定的结果或错误。

-- 正确示例
select sex, avg(age) as 平均年龄 
from students 
group by sex;

-- 错误示例（可能会报错或产生不确定结果）
select name, sex, avg(age) 
from students 
group by sex;

解释：在分组查询中，select 语句中出现的字段如果不是聚合函数的结果，必须出现在 group by 中。否则，MySQL 无法确定应该返回哪个记录的值。

2. 聚合函数与 group by 的关系

group by 和聚合函数一起使用时，聚合函数的计算结果是针对每个分组的内容进行的。具体来说，group by 会先按照指定的字段将数据进行分组，然后对每个分组分别应用聚合函数，得到每个分组的计算结果。

select sex, count(*) as 人数, avg(age) as 平均年龄 
from students 
group by sex;

注意：聚合函数会忽略 null 值，因此在计算平均值、总和时要特别留意数据中是否包含 null。

3. group by 的顺序与 order by 的配合

当需要对分组结果进行排序时，可以使用 order by，通常与 group by 配合使用。

select sex, avg(age) as 平均年龄 
from students 
group by sex 
order by 平均年龄 desc;

解释：order by 可以对聚合结果进行排序，如按平均值降序排列分组结果。

4. having 与 where 的区别

• where：在数据分组之前进行过滤，不能使用聚合函数。
• having：在数据分组之后进行过滤，可以使用聚合函数。

-- 使用 where 过滤分组前的数据
select sex, avg(age) as 平均年龄 
from students 
where age > 18 
group by sex;

-- 使用 having 过滤分组后的结果
select sex, avg(age) as 平均年龄 
from students 
group by sex 
having avg(age) > 20;

注意：如果需要对分组结果进行过滤，必须使用 having 子句，而不是 where。

5. 在 group by 中使用多个字段

可以在 group by 中使用多个字段，以实现更复杂的分组操作。例如，可以按性别和部门同时分组：

select sex, department, avg(salary) as 平均薪资 
from employees 
group by sex, department;

注意：group by 中的多个字段会按照顺序进行分组，从左到右依次分组。

6. group by 时的空值处理

如果分组字段中包含 null，则所有 null 值会被归为一组。

select department, count(*) as 人数 
from employees 
group by department;

解释：如果某个分组字段为 null，group by 会将所有 null 归为一组。这在实际数据处理中需要特别注意。

总结

• 在使用 group by 和聚合函数时，确保 select 中的字段与分组字段一致。
• 使用 having 来过滤聚合结果，避免误用 where。
• group by 可以与 order by 配合使用，对分组后的结果进行排序。
• 在处理复杂数据时，可以结合多个字段进行分组。