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 ...)

-- 1. 基本语法：
-- select 字段列表 from 表名 where 字段 between 最小值 and 最大值;

-- 2. 数值范围查询示例：
-- 查询年龄在20到30岁之间的用户（包含20岁和30岁）
select * from 用户表 where age between 20 and 30;

-- 等价于：
select * from 用户表 where age >= 20 and age <= 30;

-- 3. 日期范围查询示例：
-- 查询2023年1月1日到2023年12月31日之间的订单
select * from 订单表 where 下单时间 between '2023-01-01' and '2023-12-31';

-- 4. 时间范围查询示例：
-- 查询上午9点到下午5点之间的预约记录
select * from 预约表 where 预约时间 between '09:00:00' and '17:00:00';

-- 5. 字符串范围查询示例：
-- 查询名字首字母在A到M之间的用户
select * from 用户表 where name between 'A' and 'M';

-- 6. 取反：查询不在指定范围内的数据
-- 查询年龄不在20到30岁之间的用户
select * from 用户表 where age not between 20 and 30;

-- 等价于：
select * from 用户表 where age < 20 or age > 30;

-- 7. 结合其他条件使用
select * from 用户表 
where age between 20 and 30 
and sex = '男'
order by age;

-- 8. 注意NULL值处理
-- between不会匹配NULL值
-- 如果字段可能有NULL值，需要额外处理
select * from 用户表 
where (age between 20 and 30) or age is null;

输出示例：

-- 原始表数据：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 1  | 张三    | 20       |
| 2  | 李四    | 25       |
| 3  | 王五    | 30       |
| 4  | 赵六    | 18       |
| 5  | 钱七    | 35       |
+----+---------+----------+

-- 执行 select * from 表名 where age between 20 and 30; 的结果：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 1  | 张三    | 20       |
| 2  | 李四    | 25       |
| 3  | 王五    | 30       |
+----+---------+----------+

-- 执行 select * from 表名 where age not between 20 and 30; 的结果：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 4  | 赵六    | 18       |
| 5  | 钱七    | 35       |
+----+---------+----------+

区间查询要点：

• between A and B 是一个闭区间查询，会包含边界值A和B
• 适用于数值、日期、时间和字符串等多种数据类型的范围查询
• 使用 not between 可以查询不在指定范围内的数据
• 比使用 >= 和 <= 的组合更加简洁易读
• 对于NULL值，between 不会匹配，需要额外使用 is null 条件
• 在日期查询中要注意日期格式，推荐使用 YYYY-MM-DD 格式

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

-- 1. 基本语法：
-- IN语法：select 字段列表 from 表名 where 字段 in (值1, 值2, ...);
-- NOT IN语法：select 字段列表 from 表名 where 字段 not in (值1, 值2, ...);

-- 2. IN查询示例：
-- 查询年龄为20、25、30岁的用户
select * from 用户表 where age in (20, 25, 30);

-- 等价于：
select * from 用户表 where age = 20 or age = 25 or age = 30;

-- 3. NOT IN查询示例：
-- 查询年龄不是20、25、30岁的用户
select * from 用户表 where age not in (20, 25, 30);

-- 等价于：
select * from 用户表 where age != 20 and age != 25 and age != 30;

-- 4. 在子查询中使用IN：
-- 查询参加了编号为1、2、3课程的学生
select * from 学生表 where id in (
    select student_id from 选课表 where course_id in (1, 2, 3)
);

-- 5. 在字符串字段上使用IN：
-- 查询姓名为张三、李四、王五的用户
select * from 用户表 where name in ('张三', '李四', '王五');

-- 6. 注意NULL值处理：
-- IN和NOT IN对NULL值的处理
-- IN查询中，不会匹配到NULL值
select * from 用户表 where age in (20, 25, NULL);  -- 不会匹配NULL值的记录

-- NOT IN查询中，如果集合中有NULL值，整个结果将为空
select * from 用户表 where age not in (20, 25, NULL);  -- 可能返回空结果

-- 正确处理NULL的方法：
select * from 用户表 where age in (20, 25) or age is null;
select * from 用户表 where age not in (20, 25) and age is not null;

-- 7. 大量值的情况：
-- 当IN条件中包含大量值时，可以考虑使用临时表或JOIN操作提高性能
-- 创建临时表存储查询条件：
create temporary table temp_ages (age int);
insert into temp_ages values (20), (25), (30), (35);
select * from 用户表 inner join temp_ages on 用户表.age = temp_ages.age;

-- 8. IN与枚举类型结合：
-- 对于状态字段，常用IN进行多状态查询
select * from 订单表 where status in ('已付款', '已发货', '已签收');

-- 9. 复合IN语法（仅MySQL 5.7+支持）：
-- 同时匹配多个字段组合
select * from 用户表 
where (id, status) in ((1, 'active'), (2, 'inactive'));

-- 等价于：
select * from 用户表 
where (id = 1 and status = 'active') or (id = 2 and status = 'inactive');

-- 复合IN语法的多字段示例：
select * from 员工表
where (部门ID, 职位) in ((1, '经理'), (2, '主管'), (3, '经理'));

-- 注意：复合IN中的每个元组必须包含相同数量的值，且类型匹配

输出示例：

-- 原始表数据：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 1  | 张三    | 20       |
| 2  | 李四    | 25       |
| 3  | 王五    | 30       |
| 4  | 赵六    | 18       |
| 5  | 钱七    | 22       |
+----+---------+----------+

-- 执行 select * from 表名 where age in (20, 25); 的结果：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 1  | 张三    | 20       |
| 2  | 李四    | 25       |
+----+---------+----------+

-- 执行 select * from 表名 where age not in (20, 25, 30); 的结果：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 4  | 赵六    | 18       |
| 5  | 钱七    | 22       |
+----+---------+----------+

集合查询要点：

• IN 操作符允许在 WHERE 子句中指定多个值，相当于多个 OR 条件的简化写法
• NOT IN 操作符用于排除特定值集合，相当于多个 AND 条件的简化写法
• 集合查询比使用多个 OR/AND 条件的查询在语法上更简洁，且性能通常更好
• 当集合中的值较多时，IN 查询的性能通常优于同等的 OR 条件查询
• 处理 NULL 值时需格外小心，特别是在使用 NOT IN 时
• 集合查询可用于数值型、字符型等多种数据类型
• 可以结合子查询使用，灵活性很高
• 当需要匹配大量值时，考虑使用临时表或JOIN操作来提高性能

9. 模糊查询 (like)

-- 1. 基本语法：
-- select 字段列表 from 表名 where 字段 like 匹配模式;

-- 2. 通配符说明：
-- '%'：匹配任意数量的字符（包括0个字符）
-- '_'：匹配任意单个字符（必须是1个字符）

-- 3. 以特定字符开头：
-- 查询以"张"开头的姓名
select * from 用户表 where name like '张%';
-- 例如：张三、张四、张晓明、张等

-- 4. 以特定字符结尾：
-- 查询以"市"结尾的地址
select * from 用户表 where address like '%市';
-- 例如：北京市、上海市、广州市等

-- 5. 包含特定字符：
-- 查询包含"大学"的学校名称
select * from 学校表 where name like '%大学%';
-- 例如：北京大学、清华大学、复旦大学等

-- 6. 匹配特定长度：
-- 查询正好3个字符的名称
select * from 用户表 where name like '___';  -- 三个下划线
-- 例如：张三、李四、王五等

-- 7. 匹配特定位置的字符：
-- 查询第二个字是"小"的名称
select * from 用户表 where name like '_小%';
-- 例如：张小明、李小红、王小花等

-- 8. 组合匹配：
-- 查询以"张"开头且以"明"结尾的名称
select * from 用户表 where name like '张%明';
-- 例如：张明、张小明、张大明等

-- 9. 转义字符使用：
-- 如果需要查询包含特殊字符'%'或'_'的数据
-- 使用反斜杠'\'作为转义字符
-- 查询包含'%'符号的数据
select * from 商品表 where name like '%\%%';  -- 查询包含%的商品名称
-- 例如：50%折扣、100%纯棉等

-- 或者使用ESCAPE自定义转义字符
select * from 商品表 where name like '%$%%' escape '$';  -- 使用$作为转义字符

-- 10. NOT LIKE：排除特定模式
-- 查询不以"张"开头的姓名
select * from 用户表 where name not like '张%';

-- 11. 性能注意事项：
-- like查询，特别是前缀模糊匹配（如'%张'），可能导致索引失效
-- 如需频繁模糊查询，考虑使用全文索引或搜索引擎如Elasticsearch

-- 12. 大小写敏感性：
-- MySQL默认是大小写不敏感的，如需区分大小写，可使用BINARY关键字
select * from 用户表 where name like binary 'a%';  -- 只匹配小写a开头

输出示例：

-- 原始表数据：
+----+-----------+----------+------------------+
| id | name      | age      | address          |
+----+-----------+----------+------------------+
| 1  | 张三      | 20       | 北京市朝阳区     |
| 2  | 李小红    | 25       | 上海市浦东新区   |
| 3  | 王五      | 30       | 广州市天河区     |
| 4  | 赵小明    | 22       | 深圳市南山区     |
| 5  | 张大明    | 35       | 杭州市西湖区     |
+----+-----------+----------+------------------+

-- 执行 select * from 表名 where name like '张%'; 的结果：
+----+-----------+----------+------------------+
| id | name      | age      | address          |
+----+-----------+----------+------------------+
| 1  | 张三      | 20       | 北京市朝阳区     |
| 5  | 张大明    | 35       | 杭州市西湖区     |
+----+-----------+----------+------------------+

-- 执行 select * from 表名 where name like '_小%'; 的结果：
+----+-----------+----------+------------------+
| id | name      | age      | address          |
+----+-----------+----------+------------------+
| 2  | 李小红    | 25       | 上海市浦东新区   |
| 4  | 赵小明    | 22       | 深圳市南山区     |
+----+-----------+----------+------------------+

-- 执行 select * from 表名 where address like '%市%'; 的结果：
+----+-----------+----------+------------------+
| id | name      | age      | address          |
+----+-----------+----------+------------------+
| 1  | 张三      | 20       | 北京市朝阳区     |
| 2  | 李小红    | 25       | 上海市浦东新区   |
| 3  | 王五      | 30       | 广州市天河区     |
| 4  | 赵小明    | 22       | 深圳市南山区     |
| 5  | 张大明    | 35       | 杭州市西湖区     |
+----+-----------+----------+------------------+

模糊查询要点：

• % 匹配任意数量的字符（包括0个），_ 只匹配一个字符
• 前缀匹配（如 '张%'）性能较好，可以利用索引
• 后缀匹配（如 '%明'）和包含匹配（如 '%大%'）通常无法使用索引
• 使用 \ 或自定义的 ESCAPE 字符来转义特殊字符 % 和 _
• 模糊查询在数据量大时可能性能较差，考虑使用全文索引或专门的搜索引擎
• not like 可用于排除特定模式的数据
• MySQL 默认的 like 查询是大小写不敏感的，使用 binary 关键字可以实现大小写敏感

10. 使用别名 (as)

-- 1. 基本语法：为字段指定别名
-- 完整语法：select 字段 as 别名 from 表名;
-- 简化语法：select 字段 别名 from 表名;（as可以省略）

-- 为单个字段指定别名
select name as 姓名 from 表名;
select age as 年龄 from 表名;

-- 同时为多个字段指定别名
select name as 姓名, age as 年龄, sex as 性别 from 表名;

-- 省略as关键字（效果相同）
select name 姓名, age 年龄 from 表名;

-- 2. 别名中包含空格时需要使用引号
select name as "学生姓名", age as '学生年龄' from 表名;

-- 3. 为表指定别名
select s.name, s.age from students as s;
select s.name, s.age from students s;  -- as可以省略

-- 4. 在多表查询中使用表别名
select s.name as 学生姓名, c.name as 课程名称
from students as s, courses as c
where s.course_id = c.id;

-- 5. 在复杂查询中使用别名简化表达式
select 
    name as 姓名,
    age as 年龄,
    score/100 as 得分率,
    CONCAT(name, '-', grade) as 学生信息
from 表名;

-- 6. 在ORDER BY和GROUP BY中使用别名
select name as 姓名, age as 年龄 
from 表名
order by 年龄 desc;  -- 使用别名排序

-- 注意：在WHERE子句中不能使用别名
-- 错误示例：select name as 姓名 from 表名 where 姓名='张三';
-- 正确写法：select name as 姓名 from 表名 where name='张三';

输出示例：

-- 原始表数据：
+----+---------+----------+--------+
| id | name    | age      | sex    |
+----+---------+----------+--------+
| 1  | 张三    | 20       | 男     |
| 2  | 李四    | 25       | 男     |
| 3  | 王五    | 30       | 女     |
+----+---------+----------+--------+

-- 执行 select name as 姓名, age as 年龄 from 表名; 的结果：
+---------+----------+
| 姓名    | 年龄     |
+---------+----------+
| 张三    | 20       |
| 李四    | 25       |
| 王五    | 30       |
+---------+----------+

-- 执行带表达式的别名查询：select name as 姓名, age*2 as 翻倍年龄 from 表名;
+---------+----------+
| 姓名    | 翻倍年龄 |
+---------+----------+
| 张三    | 40       |
| 李四    | 50       |
| 王五    | 60       |
+---------+----------+

别名使用要点：

• 别名可以让查询结果更直观易读，特别适合中文显示和报表生成
• 表别名可以简化SQL语句，减少代码冗余，提高可读性
• 别名在ORDER BY和GROUP BY中可以使用，但不能在WHERE子句中使用
• 别名如果包含空格或特殊字符，需要使用引号（单引号或双引号）
• 别名可以应用于字段、表、表达式和函数

11. 排序查询 (order by)

-- 1. 基本语法：
-- select 字段列表 from 表名 order by 字段1 [asc|desc], 字段2 [asc|desc];
-- asc: 升序排列（默认值，可省略）
-- desc: 降序排列

-- 2. 单字段排序：
-- 按年龄升序排序（从小到大）
select * from 用户表 order by age asc;
-- 或简写为（asc是默认值，可省略）：
select * from 用户表 order by age;

-- 按年龄降序排序（从大到小）
select * from 用户表 order by age desc;

-- 3. 多字段排序：
-- 先按性别排序，性别相同的再按年龄降序排序
select * from 用户表 order by sex, age desc;

-- 先按部门ID升序，再按薪资降序
select * from 员工表 order by dept_id asc, salary desc;

-- 4. 使用字段位置排序（不推荐）：
-- 按查询结果中的第2列排序
select id, name, age from 用户表 order by 2;  -- 按name排序
-- 注意：这种写法可读性差，不推荐使用

-- 5. 使用表达式排序：
-- 按姓名长度排序
select name, length(name) as 姓名长度 from 用户表 order by length(name);

-- 按工资与奖金之和排序
select name, salary, bonus, (salary + bonus) as 总收入 
from 员工表 order by (salary + bonus) desc;

-- 6. 使用别名排序：
-- 使用计算结果的别名进行排序
select name, (salary + ifnull(bonus, 0)) as 总收入 
from 员工表 order by 总收入 desc;

-- 7. 对NULL值的处理：
-- 默认情况下，NULL值会被排在最前面（升序）或最后面（降序）
-- 自定义NULL值排序位置
select * from 用户表 order by ifnull(phone, 'zzzz');  -- NULL值排在最后
select * from 用户表 order by phone is null, phone;  -- NULL值排在最前

-- 8. 随机排序：
-- 使用RAND()函数实现随机排序
select * from 用户表 order by rand();
-- 随机抽取5条记录
select * from 用户表 order by rand() limit 5;

-- 9. 排序的性能注意事项：
-- 对非索引字段排序可能会导致临时表和文件排序，影响性能
-- 在大数据量下，建议对排序字段创建索引
-- 使用limit优化排序查询的效率：
select * from 大表 order by create_time desc limit 10;  -- 只排序前10条

输出示例：

-- 原始表数据：
+----+---------+------+----------+--------+
| id | name    | age  | sex      | salary |
+----+---------+------+----------+--------+
| 1  | 张三    | 20   | 男       | 8000   |
| 2  | 李四    | 25   | 男       | 10000  |
| 3  | 王五    | 30   | 女       | 12000  |
| 4  | 赵六    | 22   | 男       | 9000   |
| 5  | 钱七    | 28   | 女       | 11000  |
+----+---------+------+----------+--------+

-- 执行 select * from 表名 order by age desc; 的结果：
+----+---------+------+----------+--------+
| id | name    | age  | sex      | salary |
+----+---------+------+----------+--------+
| 3  | 王五    | 30   | 女       | 12000  |
| 5  | 钱七    | 28   | 女       | 11000  |
| 2  | 李四    | 25   | 男       | 10000  |
| 4  | 赵六    | 22   | 男       | 9000   |
| 1  | 张三    | 20   | 男       | 8000   |
+----+---------+------+----------+--------+

-- 执行 select * from 表名 order by sex, age; 的结果：
+----+---------+------+----------+--------+
| id | name    | age  | sex      | salary |
+----+---------+------+----------+--------+
| 1  | 张三    | 20   | 男       | 8000   |
| 4  | 赵六    | 22   | 男       | 9000   |
| 2  | 李四    | 25   | 男       | 10000  |
| 3  | 王五    | 30   | 女       | 12000  |
| 5  | 钱七    | 28   | 女       | 11000  |
+----+---------+------+----------+--------+

排序查询要点：

• 不指定ORDER BY时，查询结果的顺序是不确定的
• asc（升序）是默认排序方式，可以省略不写
• 多字段排序时，会按照从左到右的优先级依次排序
• ORDER BY可以使用别名、表达式或字段位置（不推荐）
• NULL值在升序中排在最前面，在降序中排在最后面
• 排序会消耗系统资源，特别是大数据量时，应适当使用LIMIT子句
• 对经常排序的字段创建索引可以提高排序效率
• 如果ORDER BY的字段有索引，排序会更快

12. 分页查询 (limit)

-- 基本语法：limit 偏移量, 返回条数
-- 偏移量：从第几条记录开始（从0开始计数）
-- 返回条数：要返回多少条记录

-- 1. 最简单的用法：只指定返回条数
select * from 表名 limit 3;
-- 含义：从第1条记录开始，返回3条记录（相当于 limit 0, 3）

-- 2. 完整语法：指定偏移量和返回条数
select * from 表名 limit 0, 5;
-- 含义：从第1条记录开始（偏移量0），返回5条记录

select * from 表名 limit 5, 5;
-- 含义：从第6条记录开始（偏移量5），返回5条记录

select * from 表名 limit 10, 5;
-- 含义：从第11条记录开始（偏移量10），返回5条记录

-- 3. 分页查询实际应用示例
-- 假设每页显示10条记录：
-- 第1页：
select * from users limit 0, 10;   -- 显示第1-10条记录

-- 第2页：
select * from users limit 10, 10;  -- 显示第11-20条记录

-- 第3页：
select * from users limit 20, 10;  -- 显示第21-30条记录

-- 第n页的公式：
-- 偏移量 = (页码 - 1) × 每页条数
-- select * from 表名 limit (页码-1)*每页条数, 每页条数;

-- 4. 结合排序的分页查询
select id, name, age from users 
order by age desc 
limit 0, 5;
-- 含义：按年龄降序排列后，返回前5条记录

-- 5. 结合条件查询的分页
select id, name, age from users 
where age > 18 
order by name 
limit 10, 5;
-- 含义：查询年龄大于18的用户，按姓名排序后，从第11条开始返回5条记录

输出示例：

-- 假设表中有以下数据：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 1  | 张三    | 20       |
| 2  | 李四    | 25       |
| 3  | 王五    | 30       |
| 4  | 赵六    | 22       |
| 5  | 钱七    | 28       |
+----+---------+----------+

-- 执行 limit 0, 2 的结果：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 1  | 张三    | 20       |
| 2  | 李四    | 25       |
+----+---------+----------+

-- 执行 limit 2, 2 的结果：
+----+---------+----------+
| id | name    | age      |
+----+---------+----------+
| 3  | 王五    | 30       |
| 4  | 赵六    | 22       |
+----+---------+----------+

重要说明：

• 偏移量从0开始计数：0表示第1条记录，1表示第2条记录，以此类推
• 返回条数：指定要返回多少条记录（不是指字段列数）
• 字段选择：select 后面的字段列表决定返回哪些列（字段），与 limit 无关
• 分页公式：第n页的偏移量 = (n-1) × 每页条数
• 性能提示：在大数据量时，建议结合索引和 order by 使用，避免深度分页

13. 分组查询 (group by)

-- 1. 基本语法：
-- select 分组字段, 聚合函数 from 表名 group by 分组字段;

-- 2. 单字段分组：
-- 按性别分组，统计每种性别的平均年龄
select sex, avg(age) as 平均年龄 from 用户表 group by sex;

-- 3. 多字段分组：
-- 按部门和职位分组，统计每个部门各职位的人数
select dept_id, position, count(*) as 人数 
from 员工表 
group by dept_id, position;

-- 4. 分组后使用聚合函数：
-- COUNT：统计行数
select dept_id, count(*) as 部门人数 from 员工表 group by dept_id;

-- SUM：求和
select dept_id, sum(salary) as 部门工资总和 from 员工表 group by dept_id;

-- AVG：平均值
select dept_id, avg(salary) as 部门平均工资 from 员工表 group by dept_id;

-- MAX/MIN：最大/最小值
select dept_id, max(salary) as 最高工资, min(salary) as 最低工资 
from 员工表 group by dept_id;

-- 5. 结合WHERE条件：
-- 先筛选后分组（WHERE在GROUP BY之前执行）
select dept_id, avg(salary) as 平均工资 
from 员工表 
where salary > 5000  -- 只考虑工资大于5000的员工
group by dept_id;

-- 6. GROUP BY配合HAVING：
-- 对分组结果进行筛选（HAVING在GROUP BY之后执行）
select dept_id, avg(salary) as 平均工资 
from 员工表 
group by dept_id 
having avg(salary) > 10000;  -- 只保留平均工资大于10000的部门

-- 7. GROUP BY与ORDER BY结合：
-- 按部门分组，并按平均工资降序排列
select dept_id, avg(salary) as 平均工资 
from 员工表 
group by dept_id 
order by 平均工资 desc;

-- 8. GROUP BY与LIMIT结合：
-- 找出平均工资最高的前3个部门
select dept_id, avg(salary) as 平均工资 
from 员工表 
group by dept_id 
order by 平均工资 desc 
limit 3;

-- 9. 处理NULL值：
-- NULL值会被当作一个独立的组
select dept_id, count(*) as 人数 
from 员工表 
group by dept_id;  -- NULL部门会单独显示一组

-- 10. GROUP_CONCAT函数：
-- 将分组内的值连接成一个字符串
select dept_id, group_concat(name) as 员工名单 
from 员工表 
group by dept_id;

-- 11. WITH ROLLUP 汇总：（MySQL特有）
-- 添加一个包含所有分组汇总行
select dept_id, sum(salary) as 工资总和 
from 员工表 
group by dept_id with rollup;  -- 会额外显示所有部门的工资总和

输出示例：

-- 原始表数据：
+----+---------+------+----------+--------+
| id | name    | age  | sex      | dept_id|
+----+---------+------+----------+--------+
| 1  | 张三    | 20   | 男       | 1      |
| 2  | 李四    | 25   | 男       | 1      |
| 3  | 王五    | 30   | 女       | 2      |
| 4  | 赵六    | 22   | 男       | 2      |
| 5  | 钱七    | 28   | 女       | 1      |
+----+---------+------+----------+--------+

-- 执行 select sex, avg(age) as 平均年龄 from 表名 group by sex; 的结果：
+---------+----------+
| sex     | 平均年龄 |
+---------+----------+
| 男      | 22.3     |
| 女      | 29.0     |
+---------+----------+

-- 执行 select dept_id, count(*) as 部门人数 from 表名 group by dept_id; 的结果：
+---------+----------+
| dept_id | 部门人数 |
+---------+----------+
| 1       | 3        |
| 2       | 2        |
+---------+----------+

-- 执行 select dept_id, group_concat(name) as 员工名单 from 表名 group by dept_id; 的结果：
+---------+-------------------+
| dept_id | 员工名单          |
+---------+-------------------+
| 1       | 张三,李四,钱七    |
| 2       | 王五,赵六         |
+---------+-------------------+

分组查询要点：

• GROUP BY 根据指定的一个或多个字段将数据分成若干组
• 每个分组通常使用聚合函数（COUNT, SUM, AVG, MAX, MIN等）进行统计计算
• SELECT 中出现的非聚合字段必须包含在 GROUP BY 子句中
• WHERE 用于在分组前过滤记录，HAVING 用于在分组后过滤结果
• GROUP BY 可以和 ORDER BY, LIMIT 等子句结合使用
• GROUP_CONCAT 函数可以将分组内的多个值合并成一个字符串
• NULL 值在分组时会被视为一个单独的分组
• WITH ROLLUP 可以为结果添加汇总行

关于"SELECT 中出现的非聚合字段必须包含在 GROUP BY 子句中"的详细解释：

当使用GROUP BY进行分组查询时，SELECT子句中可以包含两类字段：

1. 聚合字段：使用了聚合函数(如COUNT(), SUM(), AVG()等)的字段
2. 非聚合字段：没有使用聚合函数的普通字段

这条规则意味着：如果你在SELECT中列出了没有使用聚合函数的普通字段，那么这些字段必须出现在GROUP BY子句中，否则会导致SQL错误或不可预期的结果。

例如：

-- 正确的查询：dept_id出现在GROUP BY中
SELECT dept_id, AVG(salary) as 平均工资
FROM 员工表 
GROUP BY dept_id;

-- 错误的查询：name没有出现在GROUP BY中
SELECT name, AVG(salary) as 平均工资
FROM 员工表 
GROUP BY dept_id;

第二个查询是错误的，因为当你按部门(dept_id)分组时，每个部门可能有多个员工，每个员工有不同的name。数据库不知道该显示哪个员工的name，因为一个分组内有多个name值。

简单来说，就是：你只能选择分组依据的字段或者聚合计算的结果，不能选择组内的其他字段(除非将它们也加入分组条件)。

这是SQL的标准规则，虽然MySQL在某些模式下可能不严格执行，但养成这个习惯可以避免得到不可预期的结果。

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

```sql
-- 常见的聚合函数包括 `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)

-- 1. 基本语法：
-- select 分组字段, 聚合函数 from 表名 group by 分组字段 having 聚合条件;

-- 2. WHERE与HAVING的区别：
-- WHERE：在分组前过滤行，不能使用聚合函数
-- HAVING：在分组后过滤结果，可以使用聚合函数

-- 3. 简单HAVING示例：
-- 查询平均年龄超过25岁的性别分组
select sex, avg(age) as 平均年龄 
from 用户表 
group by sex 
having avg(age) > 25;

-- 4. 完整的查询流程示例：
-- 先用WHERE筛选年龄大于18的用户，按部门分组，再用HAVING筛选平均工资大于8000的部门
select dept_id, avg(salary) as 平均工资 
from 员工表 
where age > 18  -- 先筛选：年龄大于18的员工
group by dept_id  -- 再分组：按部门分组
having avg(salary) > 8000;  -- 最后过滤：平均工资大于8000的部门

-- 5. 在HAVING中使用多个条件：
-- 查询平均工资大于8000且员工数超过5人的部门
select dept_id, avg(salary) as 平均工资, count(*) as 员工数 
from 员工表 
group by dept_id 
having avg(salary) > 8000 and count(*) > 5;

-- 6. 在HAVING中引用SELECT中的别名（MySQL支持，但不是SQL标准）：
select dept_id, avg(salary) as 平均工资 
from 员工表 
group by dept_id 
having 平均工资 > 8000;  -- MySQL支持使用别名，但不是标准SQL

-- 7. 按计算结果过滤的场景：
-- 查询总销售额超过10000的月份
select month(order_date) as 月份, sum(amount) as 月销售额 
from 订单表 
group by month(order_date) 
having sum(amount) > 10000;

-- 8. 与ORDER BY结合使用：
-- 查询平均分在80分以上的班级，并按平均分降序排列
select class_id, avg(score) as 平均分 
from 学生表 
group by class_id 
having 平均分 >= 80 
order by 平均分 desc;

-- 9. 注意事项：HAVING中不要使用非聚合、非分组字段
-- 错误示例：having name = '张三'（name不是分组字段也不是聚合结果）
-- 正确做法：在WHERE中过滤非聚合条件

-- 10. 性能考虑：
-- WHERE比HAVING更高效，因为WHERE先过滤减少了参与分组的数据量
-- 能用WHERE做的过滤尽量不用HAVING

输出示例：

-- 原始表数据：
+----+---------+------+----------+--------+--------+
| id | name    | age  | sex      | dept_id| salary |
+----+---------+------+----------+--------+--------+
| 1  | 张三    | 20   | 男       | 1      | 7000   |
| 2  | 李四    | 25   | 男       | 1      | 8000   |
| 3  | 王五    | 30   | 女       | 2      | 9000   |
| 4  | 赵六    | 22   | 男       | 2      | 6000   |
| 5  | 钱七    | 28   | 女       | 1      | 10000  |
+----+---------+------+----------+--------+--------+

-- 执行 select sex, avg(age) as 平均年龄 from 表名 group by sex having avg(age) > 25; 的结果：
+--------+----------+
| sex    | 平均年龄 |
+--------+----------+
| 女     | 29.0     |
+--------+----------+

-- 执行复合查询的结果：
select dept_id, avg(salary) as 平均工资, count(*) as 员工数 
from 表名 
group by dept_id 
having avg(salary) > 7000;

+--------+----------+--------+
| dept_id| 平均工资 | 员工数 |
+--------+----------+--------+
| 1      | 8333.33  | 3      |
| 2      | 7500.00  | 2      |
+--------+----------+--------+

HAVING要点与最佳实践：

• HAVING子句用于对GROUP BY分组后的结果进行筛选
• 与WHERE的主要区别：HAVING可以使用聚合函数(COUNT, SUM, AVG等)，WHERE不能
• 执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
• WHERE在分组前过滤，HAVING在分组后过滤，WHERE通常更高效
• 当需要基于聚合结果进行过滤时，必须使用HAVING
• HAVING中应当只使用聚合函数或GROUP BY中包含的字段
• 虽然MySQL允许在HAVING中使用SELECT中的别名，但这不符合SQL标准
• 优化建议：能用WHERE做的过滤尽量不用HAVING，可以提高查询性能

16. NULL值查询 (is null/is not null)

-- 1. 基本语法：
-- 查询NULL值：select 字段列表 from 表名 where 字段 is null;
-- 查询非NULL值：select 字段列表 from 表名 where 字段 is not null;

-- 2. 查询NULL值示例：
-- 查询电话号码为NULL的用户
select * from 用户表 where phone is null;

-- 3. 查询非NULL值示例：
-- 查询已填写电话号码的用户
select * from 用户表 where phone is not null;

-- 4. 注意：NULL不能用等号比较
-- 错误的写法（不会返回预期结果）：
select * from 用户表 where phone = null;  -- 错误！

-- 正确的写法：
select * from 用户表 where phone is null;  -- 正确

-- 5. 在条件组合中使用NULL判断
-- 查询未填写电话但已填写邮箱的用户
select * from 用户表 
where phone is null 
and email is not null;

-- 6. IFNULL函数：替换NULL值
-- 如果phone为NULL，则显示"未填写"，否则显示phone的值
select name, IFNULL(phone, '未填写') as 联系方式 
from 用户表;

-- 7. COALESCE函数：返回参数中的第一个非NULL值
-- 优先使用phone，如果phone为NULL则使用email，如果email也为NULL则使用"无联系方式"
select name, COALESCE(phone, email, '无联系方式') as 联系方式 
from 用户表;

-- 8. 在聚合函数中处理NULL
-- 注意：COUNT(*)会计算所有行，而COUNT(字段)会忽略该字段为NULL的行
select 
    COUNT(*) as 总用户数,
    COUNT(phone) as 有电话的用户数,
    AVG(IFNULL(age, 0)) as 平均年龄
from 用户表;

-- 9. 在ORDER BY中处理NULL
-- NULL值默认排在最前面（升序）或最后面（降序）
-- 让NULL值始终排在最后
select * from 用户表
order by IFNULL(phone, 'ZZZZZZ');

-- 10. 在分组中处理NULL
-- NULL值会被当作一个独立的组
select 部门, COUNT(*) as 人数
from 员工表
group by 部门;

输出示例：

-- 原始表数据：
+----+---------+----------+---------------+
| id | name    | age      | phone         |
+----+---------+----------+---------------+
| 1  | 张三    | 20       | 13800138000   |
| 2  | 李四    | 25       | NULL          |
| 3  | 王五    | NULL     | 13900139000   |
| 4  | 赵六    | 22       | NULL          |
| 5  | 钱七    | NULL     | NULL          |
+----+---------+----------+---------------+

-- 执行 select * from 表名 where phone is null; 的结果：
+----+---------+----------+---------------+
| id | name    | age      | phone         |
+----+---------+----------+---------------+
| 2  | 李四    | 25       | NULL          |
| 4  | 赵六    | 22       | NULL          |
| 5  | 钱七    | NULL     | NULL          |
+----+---------+----------+---------------+

-- 执行 select * from 表名 where age is not null; 的结果：
+----+---------+----------+---------------+
| id | name    | age      | phone         |
+----+---------+----------+---------------+
| 1  | 张三    | 20       | 13800138000   |
| 2  | 李四    | 25       | NULL          |
| 4  | 赵六    | 22       | NULL          |
+----+---------+----------+---------------+

-- 执行 select name, IFNULL(phone, '未填写') as 联系方式 from 表名; 的结果：
+---------+---------------+
| name    | 联系方式      |
+---------+---------------+
| 张三    | 13800138000   |
| 李四    | 未填写        |
| 王五    | 13900139000   |
| 赵六    | 未填写        |
| 钱七    | 未填写        |
+---------+---------------+

NULL值处理要点：

• MySQL中的NULL表示"未知"或"不存在的值"，不同于空字符串('')
• 必须使用is null和is not null来判断，不能用=或!=
• 所有与NULL值的比较都返回NULL，包括NULL = NULL
• NULL值在聚合函数中通常被忽略（除了COUNT(*)）
• 使用IFNULL()或COALESCE()函数可以替换或处理NULL值
• 在分组查询中，所有NULL值被视为相同的值，归为一组
• 在ORDER BY排序中，NULL值默认被视为最小值

17. 引号使用 (单引号、双引号和反引号)

-- 1. 单引号（''）：用于字符串值
-- 用于表示字符串常量
select * from 用户表 where name = '张三';
select * from 用户表 where address = '北京市朝阳区';

-- 包含单引号的字符串需要使用转义字符（\'）或双单引号
select * from 用户表 where name = 'O\'Reilly';  -- 使用转义字符
select * from 用户表 where name = 'O''Reilly';  -- 使用双单引号

-- 2. 双引号（""）：可用于字符串值（MySQL特有）
-- MySQL允许使用双引号表示字符串，但不是SQL标准
select * from 用户表 where name = "张三";
select * from 用户表 where address = "北京市朝阳区";

-- 包含双引号的字符串需要使用转义字符（\"）
select * from 用户表 where description = "这是一个\"特殊\"商品";

-- 3. 反引号（``）：用于标识符（表名、列名等）
-- 用于避免与MySQL关键字冲突，或包含特殊字符的标识符
select `name`, `age` from `用户表`;

-- 当字段名与MySQL关键字冲突时，必须使用反引号
select * from 用户表 where `order` = 100;  -- order是MySQL关键字
select `select`, `from` from `关键字表`;   -- select和from都是关键字

-- 4. 引号混合使用示例
-- 在反引号内的表名/字段名中包含单引号或双引号
select * from `user's_table` where `client"s_name` = '张三';

-- 5. 特殊情况：在字符串中包含不同类型的引号
select * from 用户表 where note = '他说："这很好"';  -- 在单引号字符串中包含双引号
select * from 用户表 where note = "这是'引用'文本";  -- 在双引号字符串中包含单引号

引号使用示例：

-- 原始表数据：
+----+-----------+----------+------------------+
| id | name      | age      | description      |
+----+-----------+----------+------------------+
| 1  | 张三      | 20       | 普通用户         |
| 2  | O'Reilly  | 25       | VIP用户          |
| 3  | 李"四"    | 30       | 管理员           |
+----+-----------+----------+------------------+

-- 执行含有单引号的查询：
select * from 表名 where name = 'O\'Reilly';
+----+-----------+----------+------------------+
| id | name      | age      | description      |
+----+-----------+----------+------------------+
| 2  | O'Reilly  | 25       | VIP用户          |
+----+-----------+----------+------------------+

-- 使用反引号引用表名和列名：
select `id`, `name` from `表名` where `age` > 20;
+----+-----------+
| id | name      |
+----+-----------+
| 2  | O'Reilly  |
| 3  | 李"四"    |
+----+-----------+

引号使用要点：

• 单引号：SQL标准用于表示字符串常量，几乎所有数据库都支持
• 双引号：MySQL特有的字符串表示方法，为了兼容性建议优先使用单引号
• 反引号：MySQL特有，用于引用表名、列名等标识符，特别是与关键字冲突或包含特殊字符时
• 避免将不同类型的引号混淆使用，特别是在编写跨数据库的SQL时
• 字符串中如需包含引号，可以使用转义字符或另一种引号包裹
• 标识符（表名、列名）即使不与关键字冲突，使用反引号也是一种良好习惯

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

-- 1. 基本语法：
-- AND：同时满足多个条件（逻辑与）
-- select 字段列表 from 表名 where 条件1 and 条件2 [and 条件3...];

-- OR：满足任意一个条件（逻辑或）
-- select 字段列表 from 表名 where 条件1 or 条件2 [or 条件3...];

-- 2. AND条件查询示例：
-- 查询年龄大于20且性别为男的用户
select * from 用户表 where age > 20 and sex = '男';

-- 查询年龄在20到30之间的用户（包含边界值）
select * from 用户表 where age >= 20 and age <= 30;
-- 等价于：select * from 用户表 where age between 20 and 30;

-- 3. OR条件查询示例：
-- 查询年龄小于18或年龄大于60的用户
select * from 用户表 where age < 18 or age > 60;

-- 查询性别为女或VIP等级大于3的用户
select * from 用户表 where sex = '女' or vip_level > 3;

-- 4. AND和OR混合使用（优先级：AND高于OR）：
-- 查询年龄大于25的男性或任何年龄的女性
select * from 用户表 where (age > 25 and sex = '男') or sex = '女';

-- 注意：不使用括号可能导致逻辑错误
-- 这个查询结果可能与预期不符
select * from 用户表 where age > 25 and sex = '男' or sex = '女';

-- 5. AND与IN结合：
-- 查询在北京或上海的男性用户
select * from 用户表 
where sex = '男' and city in ('北京', '上海');

-- 6. OR与IN的等价转换：
-- 使用OR：
select * from 用户表 where city = '北京' or city = '上海' or city = '广州';
-- 等价的IN写法（更推荐）：
select * from 用户表 where city in ('北京', '上海', '广州');

-- 7. 多条件组合查询：
-- 查询(年龄小于18或大于60)且活跃状态为1的用户
select * from 用户表 
where (age < 18 or age > 60) and active_status = 1;

-- 8. NOT与AND/OR结合使用：
-- 查询不是(男性且年龄大于25)的用户
select * from 用户表 
where not (sex = '男' and age > 25);
-- 等价于：
select * from 用户表 
where sex != '男' or age <= 25;

-- 9. 使用AND/OR处理NULL值：
-- 查询电话号码不为NULL且年龄大于20的用户
select * from 用户表 
where phone is not null and age > 20;

输出示例：

-- 原始表数据：
+----+---------+------+----------+--------+
| id | name    | age  | sex      | city   |
+----+---------+------+----------+--------+
| 1  | 张三    | 20   | 男       | 北京   |
| 2  | 李四    | 25   | 男       | 上海   |
| 3  | 王五    | 30   | 女       | 广州   |
| 4  | 赵六    | 16   | 男       | 北京   |
| 5  | 钱七    | 65   | 女       | 上海   |
+----+---------+------+----------+--------+

-- 执行 select * from 表名 where age > 20 and sex = '男'; 的结果：
+----+---------+------+----------+--------+
| id | name    | age  | sex      | city   |
+----+---------+------+----------+--------+
| 2  | 李四    | 25   | 男       | 上海   |
+----+---------+------+----------+--------+

-- 执行 select * from 表名 where age < 18 or age > 60; 的结果：
+----+---------+------+----------+--------+
| id | name    | age  | sex      | city   |
+----+---------+------+----------+--------+
| 4  | 赵六    | 16   | 男       | 北京   |
| 5  | 钱七    | 65   | 女       | 上海   |
+----+---------+------+----------+--------+

-- 执行带括号的混合条件查询：
-- select * from 表名 where (age > 25 and sex = '男') or sex = '女';
+----+---------+------+----------+--------+
| id | name    | age  | sex      | city   |
+----+---------+------+----------+--------+
| 3  | 王五    | 30   | 女       | 广州   |
| 5  | 钱七    | 65   | 女       | 上海   |
+----+---------+------+----------+--------+

AND/OR使用要点：

• AND：所有条件都必须满足，相当于"逻辑与"（&&）
• OR：满足任意一个条件即可，相当于"逻辑或"（||）
• 优先级：AND的优先级高于OR，复杂条件时建议使用括号明确分组
• 括号可以改变条件的计算顺序，应当使用括号明确表达查询意图
• 当有多个OR条件针对同一字段时，可以使用IN来简化查询
• 使用NOT可以对AND/OR条件进行否定，但要注意逻辑转换
• 在处理可能包含NULL的字段时，必须使用IS NULL或IS NOT NULL
• 复杂条件查询中正确使用AND/OR可以减少多次查询，提高效率

19. 常用MySQL函数

MySQL提供了大量内置函数，可以在查询中使用这些函数对数据进行处理和转换。以下是按类型分类的常用函数及其使用方法。

19.1 字符串函数

-- 1. CONCAT：连接字符串
-- 语法：CONCAT(str1, str2, ...)
select CONCAT('Hello', ' ', 'World') as 结果;  -- 输出：Hello World
select CONCAT(first_name, ' ', last_name) as 姓名 from 用户表;

-- 2. LENGTH/CHAR_LENGTH：获取字符串长度
-- LENGTH：返回字符串的字节长度
-- CHAR_LENGTH：返回字符串的字符数量（对中文更友好）
select LENGTH('Hello') as 长度;  -- 输出：5
select CHAR_LENGTH('你好') as 字符数;  -- 输出：2（而LENGTH返回6，因为中文通常占3字节）

-- 3. UPPER/LOWER：大小写转换
select UPPER('hello') as 大写;  -- 输出：HELLO
select LOWER('WORLD') as 小写;  -- 输出：world

-- 4. SUBSTRING：截取字符串
-- 语法：SUBSTRING(str, position, [length])
select SUBSTRING('HelloWorld', 1, 5) as 结果;  -- 输出：Hello（从第1个字符开始，取5个）
select SUBSTRING('HelloWorld', 6) as 结果;  -- 输出：World（从第6个字符开始到结束）

-- 5. REPLACE：替换字符串
-- 语法：REPLACE(str, from_str, to_str)
select REPLACE('Hello World', 'World', 'MySQL') as 结果;  -- 输出：Hello MySQL

-- 6. TRIM：去除首尾空格或指定字符
select TRIM('  MySQL  ') as 结果;  -- 输出：MySQL
select TRIM(BOTH 'x' FROM 'xxxMySQLxxx') as 结果;  -- 输出：MySQL

-- 7. LEFT/RIGHT：从左/右截取指定长度
select LEFT('HelloWorld', 5) as 左侧;  -- 输出：Hello
select RIGHT('HelloWorld', 5) as 右侧;  -- 输出：World

-- 8. INSTR：查找子字符串位置
-- 返回子字符串在字符串中的位置（从1开始），未找到返回0
select INSTR('HelloWorld', 'World') as 位置;  -- 输出：6

-- 9. FORMAT：格式化数字
-- 语法：FORMAT(X, D)，X为数字，D为小数位数
select FORMAT(1234.5678, 2) as 格式化数字;  -- 输出：1,234.57（保留2位小数并加千分位逗号）

19.2 数值函数

-- 1. ROUND：四舍五入
-- 语法：ROUND(X, D)，X为数字，D为小数位数
select ROUND(1.23456, 2) as 结果;  -- 输出：1.23
select ROUND(1.56) as 结果;  -- 输出：2（不指定小数位数时，四舍五入到整数）

-- 2. CEIL/CEILING：向上取整
select CEIL(1.23) as 结果;  -- 输出：2
select CEILING(1.01) as 结果;  -- 输出：2

-- 3. FLOOR：向下取整
select FLOOR(1.99) as 结果;  -- 输出：1

-- 4. TRUNCATE：截断小数（不四舍五入）
-- 语法：TRUNCATE(X, D)，X为数字，D为保留小数位数
select TRUNCATE(1.23456, 2) as 结果;  -- 输出：1.23
select TRUNCATE(1.99, 0) as 结果;  -- 输出：1

-- 5. ABS：绝对值
select ABS(-10) as 结果;  -- 输出：10

-- 6. MOD：取余/取模
-- 语法：MOD(N, M) 或 N % M
select MOD(10, 3) as 结果;  -- 输出：1
select 10 % 3 as 结果;  -- 输出：1

-- 7. POWER：幂运算
-- 语法：POWER(X, Y)，X的Y次方
select POWER(2, 3) as 结果;  -- 输出：8（2的3次方）

-- 8. SQRT：平方根
select SQRT(16) as 结果;  -- 输出：4

-- 9. RAND：随机数
-- 生成0-1之间的随机小数
select RAND() as 随机数;  -- 输出：0.xxx（每次不同）
-- 生成指定范围的随机整数，如1-100
select FLOOR(1 + RAND() * 100) as 随机整数;  -- 输出：1-100间的随机数

19.3 日期和时间函数

-- 1. NOW()：当前日期时间
select NOW() as 当前时间;  -- 输出如：2023-11-15 14:30:45

-- 2. CURDATE()：当前日期
select CURDATE() as 当前日期;  -- 输出如：2023-11-15

-- 3. CURTIME()：当前时间
select CURTIME() as 当前时间;  -- 输出如：14:30:45

-- 4. DATE_FORMAT：格式化日期
-- 语法：DATE_FORMAT(date, format)
select DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒') as 格式化日期;
-- 输出如：2023年11月15日 14时30分45秒
-- 常用格式符：
-- %Y：四位年份，%y：两位年份
-- %m：月份(01-12)，%c：月份(1-12)
-- %d：日(01-31)，%e：日(1-31)
-- %H：24小时制(00-23)，%h：12小时制(01-12)
-- %i：分钟(00-59)，%s：秒(00-59)
-- %W：星期名(Sunday-Saturday)，%w：星期(0=周日, 6=周六)

-- 5. YEAR/MONTH/DAY：提取年/月/日
select YEAR(NOW()) as 年;  -- 输出如：2023
select MONTH(NOW()) as 月;  -- 输出如：11
select DAY(NOW()) as 日;  -- 输出如：15

-- 6. HOUR/MINUTE/SECOND：提取时/分/秒
select HOUR(NOW()) as 时;  -- 输出如：14
select MINUTE(NOW()) as 分;  -- 输出如：30
select SECOND(NOW()) as 秒;  -- 输出如：45

-- 7. DATEDIFF：计算两个日期之间的天数差
-- 语法：DATEDIFF(date1, date2)，返回date1 - date2的天数
select DATEDIFF('2023-12-31', '2023-01-01') as 天数差;  -- 输出：364

-- 8. DATE_ADD/DATE_SUB：日期加减
-- 语法：DATE_ADD(date, INTERVAL value unit)
-- 单位可以是：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND等
select DATE_ADD(NOW(), INTERVAL 1 DAY) as 明天;  -- 输出：明天的日期时间
select DATE_SUB(NOW(), INTERVAL 1 MONTH) as 上个月;  -- 输出：上个月的今天

-- 9. TIMESTAMPDIFF：计算两个日期时间的差值
-- 语法：TIMESTAMPDIFF(unit, datetime1, datetime2)
-- 单位可以是：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND等
select TIMESTAMPDIFF(YEAR, '2000-01-01', NOW()) as 年龄;  -- 输出：当前距2000年的年数
select TIMESTAMPDIFF(MINUTE, '2023-11-15 14:00:00', '2023-11-15 15:30:00') as 分钟差;  -- 输出：90

-- 10. UNIX_TIMESTAMP/FROM_UNIXTIME：Unix时间戳转换
-- UNIX_TIMESTAMP：日期转时间戳（秒数）
-- FROM_UNIXTIME：时间戳转日期
select UNIX_TIMESTAMP('2023-11-15') as 时间戳;  -- 输出：1700006400（示例值）
select FROM_UNIXTIME(1700006400) as 日期时间;  -- 输出：2023-11-15 00:00:00

19.4 条件函数

-- 1. IF：简单条件判断
-- 语法：IF(expr, true_value, false_value)
select IF(1 > 0, '正确', '错误') as 结果;  -- 输出：正确
select name, IF(age >= 18, '成年', '未成年') as 年龄段 from 用户表;

-- 2. IFNULL：NULL值处理
-- 语法：IFNULL(expr1, expr2)，如果expr1为NULL则返回expr2，否则返回expr1
select IFNULL(NULL, '默认值') as 结果;  -- 输出：默认值
select name, IFNULL(phone, '未填写') as 联系方式 from 用户表;

-- 3. NULLIF：相等比较
-- 语法：NULLIF(expr1, expr2)，如果expr1 = expr2则返回NULL，否则返回expr1
select NULLIF(1, 1) as 结果;  -- 输出：NULL
select NULLIF(1, 2) as 结果;  -- 输出：1

-- 4. CASE：多条件判断
-- 语法一：CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2]... [ELSE default] END
select 
    name,
    CASE 
        WHEN age < 18 THEN '青少年'
        WHEN age BETWEEN 18 AND 30 THEN '青年'
        WHEN age BETWEEN 31 AND 50 THEN '中年'
        ELSE '老年'
    END as 年龄段
from 用户表;

-- 语法二：CASE expr WHEN value1 THEN result1 [WHEN value2 THEN result2]... [ELSE default] END
select 
    name,
    CASE status
        WHEN 1 THEN '正常'
        WHEN 2 THEN '禁用'
        WHEN 3 THEN '删除'
        ELSE '未知'
    END as 账户状态
from 用户表;

-- 5. COALESCE：返回第一个非NULL值
-- 语法：COALESCE(expr1, expr2, ..., exprn)
select COALESCE(NULL, NULL, '第三个值', '第四个值') as 结果;  -- 输出：第三个值
select name, COALESCE(mobile, phone, email, '无联系方式') as 联系方式 from 用户表;

19.5 聚合函数

-- 1. COUNT：计数
-- COUNT(*)：计算行数，包括NULL值
-- COUNT(列名)：计算非NULL值的行数
select COUNT(*) as 总行数 from 用户表;
select COUNT(phone) as 有电话的用户数 from 用户表;

-- 2. SUM：求和
select SUM(salary) as 工资总和 from 员工表;
-- 忽略NULL值，可以与IFNULL结合使用
select SUM(IFNULL(bonus, 0)) as 奖金总和 from 员工表;

-- 3. AVG：平均值
select AVG(age) as 平均年龄 from 用户表;
-- 忽略NULL值，如果需要将NULL视为0，可以结合IFNULL
select AVG(IFNULL(score, 0)) as 平均分 from 成绩表;

-- 4. MAX/MIN：最大值/最小值
select MAX(salary) as 最高工资, MIN(salary) as 最低工资 from 员工表;
-- 也可用于日期、字符串
select MAX(birth_date) as 最晚出生日期, MIN(birth_date) as 最早出生日期 from 用户表;
select MAX(name) as 字母顺序最后, MIN(name) as 字母顺序最前 from 用户表;

-- 5. GROUP_CONCAT：组合字符串
-- 将组内值连接成一个字符串，常与GROUP BY一起使用
-- 语法：GROUP_CONCAT([DISTINCT] expr [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC]] [SEPARATOR str_val])
select 
    dept_id,
    GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ', ') as 员工列表
from 员工表
group by dept_id;
-- 示例输出：1, 张三, 李四, 王五（部门1的所有员工名，按工资降序）

19.6 窗口函数 (MySQL 8.0+)

-- 1. ROW_NUMBER：行号
-- 为结果集的每一行分配一个唯一的顺序号
select 
    name,
    salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) as 工资排名
from 员工表;

-- 2. RANK：排名（相同值相同排名，排名不连续）
-- 相同值获得相同排名，排名间有"空档"
select 
    name,
    salary,
    RANK() OVER(ORDER BY salary DESC) as 工资排名
from 员工表;
-- 如果有两人工资相同且排第2，下一个人排第4

-- 3. DENSE_RANK：密集排名（相同值相同排名，排名连续）
-- 相同值获得相同排名，排名间没有"空档"
select 
    name,
    salary,
    DENSE_RANK() OVER(ORDER BY salary DESC) as 工资排名
from 员工表;
-- 如果有两人工资相同且排第2，下一个人排第3

-- 4. PARTITION BY：分区
-- 按指定列分组，然后在每组内排序或计算
select 
    name,
    dept_id,
    salary,
    ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) as 部门内工资排名
from 员工表;
-- 每个部门内部分别排名

-- 5. SUM OVER：累计求和
-- 计算截至当前行的累计总和
select 
    date,
    amount,
    SUM(amount) OVER(ORDER BY date) as 累计金额
from 订单表;

-- 6. LEAD/LAG：访问后续/前面行
-- LEAD：获取当前行之后的行
-- LAG：获取当前行之前的行
select 
    date,
    amount,
    LAG(amount, 1, 0) OVER(ORDER BY date) as 前一天金额,
    LEAD(amount, 1, 0) OVER(ORDER BY date) as 后一天金额
from 订单表;

19.7 JSON函数 (MySQL 5.7.8+)

-- 1. JSON_EXTRACT：提取JSON值
-- 语法：JSON_EXTRACT(json_doc, path)
select JSON_EXTRACT('{"name": "张三", "age": 30}', '$.name') as 姓名;  -- 输出："张三"
-- 简化语法使用->运算符
select '{"name": "张三", "age": 30}'->'$.name' as 姓名;  -- 输出："张三"
-- 使用->>运算符去除引号
select '{"name": "张三", "age": 30}'->>'$.name' as 姓名;  -- 输出：张三

-- 2. JSON_OBJECT：创建JSON对象
select JSON_OBJECT('name', name, 'age', age) as 用户信息 from 用户表;
-- 输出如：{"name": "张三", "age": 30}

-- 3. JSON_ARRAY：创建JSON数组
select JSON_ARRAY(id, name, age) as 用户数据 from 用户表;
-- 输出如：[1, "张三", 30]

-- 4. JSON_CONTAINS：检查JSON是否包含值
-- 语法：JSON_CONTAINS(json_doc, val[, path])
select JSON_CONTAINS('{"tags": ["mysql", "database"]}', '"mysql"', '$.tags') as 是否包含;  -- 输出：1（表示true）

-- 5. JSON_MERGE_PATCH：合并JSON
select JSON_MERGE_PATCH('{"name": "张三"}', '{"age": 30}') as 合并结果;
-- 输出：{"name": "张三", "age": 30}

-- 6. JSON_TABLE：将JSON数据转为表格
-- 处理JSON数组数据
select *
from JSON_TABLE(
    '[{"id": 1, "name": "张三"}, {"id": 2, "name": "李四"}]',
    '$[*]' COLUMNS(
        id INT PATH '$.id',
        name VARCHAR(50) PATH '$.name'
    )
) as jt;
-- 输出表格：
-- +----+--------+
-- | id | name   |
-- +----+--------+
-- | 1  | 张三   |
-- | 2  | 李四   |
-- +----+--------+