第12章 SQL函数
12.1 SQL函数概述
12.1.1 函数的概念
SQL函数是一种预定义的操作，用于对数据进行转换、计算和处理。函数可以接受一个或多个参数，并返回一个结果值。SQL函数在查询、数据处理和报表生成中起着重要作用。
12.1.2 函数的特点
- 封装性：函数将复杂的逻辑封装起来，使用时只需要调用函数名
- 可重用性：函数可以在多个查询中重复使用
- 提高效率：函数可以提高查询的执行效率
- 简化代码：函数可以简化SQL语句的编写
12.1.3 函数的分类
SQL函数可以分为以下几类：
- 单行函数：对每一行数据进行处理，返回一个结果
- 聚合函数：对一组数据进行处理，返回一个结果
- 窗口函数：对一组数据进行处理，返回多个结果
- 自定义函数：用户根据需要自定义的函数
12.2 单行函数
12.2.1 字符函数
字符函数用于处理字符串数据，常见的字符函数包括：
12.2.1.1 字符串拼接函数
- CONCAT()：连接两个或多个字符串
- ||：字符串连接运算符（某些数据库支持）
12.2.1.2 字符串长度函数
- LENGTH()：返回字符串的长度（字节数）
- CHAR_LENGTH()：返回字符串的长度（字符数）
12.2.1.3 字符串截取函数
- SUBSTRING()：截取字符串的一部分
- LEFT()：返回字符串左边的指定长度
- RIGHT()：返回字符串右边的指定长度
12.2.1.4 字符串转换函数
- UPPER()：将字符串转换为大写
- LOWER()：将字符串转换为小写
- INITCAP()：将字符串的首字母转换为大写，其余转换为小写
12.2.1.5 字符串替换函数
- REPLACE()：替换字符串中的指定子串
12.2.1.6 字符串修剪函数
- TRIM()：去除字符串首尾的空格或指定字符
- LTRIM()：去除字符串左边的空格或指定字符
- RTRIM()：去除字符串右边的空格或指定字符
12.2.2 数值函数
数值函数用于处理数值数据，常见的数值函数包括：
12.2.2.1 四舍五入函数
- ROUND()：对数值进行四舍五入
12.2.2.2 截断函数
- TRUNC()：截断数值，不进行四舍五入
12.2.2.3 取整函数
- CEIL()：向上取整
- FLOOR()：向下取整
12.2.2.4 绝对值函数
- ABS()：返回数值的绝对值
12.2.2.5 幂运算函数
- POWER()：计算数值的幂
12.2.2.6 平方根函数
- SQRT()：计算数值的平方根
12.2.2.7 随机数函数
- RAND()：生成0到1之间的随机数
12.2.3 日期和时间函数
日期和时间函数用于处理日期和时间数据，常见的日期和时间函数包括：
12.2.3.1 获取当前日期和时间
- CURRENT_DATE：返回当前日期
- CURRENT_TIME：返回当前时间
- CURRENT_TIMESTAMP：返回当前日期和时间
12.2.3.2 日期和时间提取函数
- EXTRACT()：从日期或时间中提取指定的部分
- YEAR()：返回日期的年份
- MONTH()：返回日期的月份
- DAY()：返回日期的日
12.2.3.3 日期和时间计算函数
- DATE_ADD()：向日期添加指定的时间间隔
- DATE_SUB()：从日期减去指定的时间间隔
- DATEDIFF()：计算两个日期之间的天数差
12.2.3.4 日期格式化函数
- DATE_FORMAT()：将日期格式化为指定的字符串
12.2.4 转换函数
转换函数用于在不同数据类型之间进行转换，常见的转换函数包括：
12.2.4.1 CAST()函数
- CAST()：将一个数据类型转换为另一个数据类型
12.2.4.2 CONVERT()函数
- CONVERT()：将一个数据类型转换为另一个数据类型（与CAST类似）
12.2.5 条件函数
条件函数用于根据条件返回不同的结果，常见的条件函数包括：
12.2.5.1 CASE函数
- CASE：根据条件返回不同的结果
12.2.5.2 IF()函数
- IF()：根据条件返回不同的结果（某些数据库支持）
12.2.5.3 NULL处理函数
- IFNULL()：如果第一个参数为NULL，则返回第二个参数
- COALESCE()：返回第一个非NULL的参数
- NULLIF()：如果两个参数相等，则返回NULL，否则返回第一个参数
12.3 聚合函数
12.3.1 聚合函数概述
聚合函数用于对一组数据进行计算并返回一个结果，常用于统计分析。聚合函数忽略NULL值，除非特别说明。
12.3.2 常用聚合函数
12.3.2.1 COUNT()函数
- COUNT()：计算行数
12.3.2.2 SUM()函数
- SUM()：计算数值列的总和
12.3.2.3 AVG()函数
- AVG()：计算数值列的平均值
12.3.2.4 MAX()函数
- MAX()：返回列的最大值
12.3.2.5 MIN()函数
- MIN()：返回列的最小值
12.3.2.6 GROUP_CONCAT()函数
- GROUP_CONCAT()：将分组后的结果连接成字符串（某些数据库支持）
12.3.3 聚合函数的使用
12.3.3.1 基本使用
12.3.3.2 与GROUP BY一起使用
12.3.3.3 与HAVING一起使用
12.4 窗口函数
12.4.1 窗口函数概述
窗口函数是一种特殊的函数，它对一组数据进行处理，但返回多个结果，每个结果对应原表中的一行。窗口函数可以用于计算排名、累计和、移动平均等。
12.4.2 窗口函数的语法
12.4.3 常用窗口函数
12.4.3.1 排名函数
- ROW_NUMBER()：为每一行分配一个唯一的序号
- RANK()：为每一行分配一个排名，相同值的排名相同，后续排名跳过
- DENSE_RANK()：为每一行分配一个排名，相同值的排名相同，后续排名不跳过
12.4.3.2 聚合窗口函数
- SUM() OVER()：计算累计和
- AVG() OVER()：计算移动平均
12.4.3.3 分布函数
- PERCENT_RANK()：计算相对排名（0到1之间）
- CUME_DIST()：计算累积分布（0到1之间）
12.4.4 窗口函数的使用示例
12.4.4.1 计算每个班级学生的排名
12.4.4.2 计算每个部门员工的累计工资
12.4.4.3 计算移动平均值
12.5 自定义函数
12.5.1 自定义函数概述
自定义函数是用户根据需要自己编写的函数，用于实现特定的功能。自定义函数可以提高代码的重用性和可维护性。
12.5.2 自定义函数的类型
12.5.2.1 标量函数
标量函数返回一个单一的值，常用于数据转换和计算。
12.5.2.2 表值函数
表值函数返回一个结果集，类似于一个表，可以在FROM子句中使用。
12.5.3 自定义函数的创建
12.5.3.1 创建标量函数
12.5.3.2 创建表值函数
12.5.4 自定义函数的管理
12.5.4.1 修改函数
12.5.4.2 删除函数
12.5.4.3 查看函数
12.6 函数的使用最佳实践
12.6.1 选择合适的函数
- 根据需要选择合适的函数类型（单行函数、聚合函数、窗口函数）
- 优先使用内置函数，内置函数经过优化，性能更好
- 对于复杂的逻辑，可以考虑使用自定义函数
12.6.2 注意函数的性能
- 避免在WHERE子句中使用函数，会导致索引失效
- 避免在大表上使用复杂的窗口函数
- 自定义函数要注意性能，避免复杂的逻辑和大量的IO操作
12.6.3 注意函数的兼容性
- 不同数据库之间的函数语法可能有所不同
- 如果需要跨数据库兼容，要注意函数的兼容性
- 可以使用数据库抽象层或ORM框架来处理函数的兼容性
12.6.4 注意函数的安全性
- 自定义函数要注意安全性，避免SQL注入
- 限制函数的执行权限，只授予必要的权限
- 定期审查自定义函数，确保没有安全隐患
12.7 综合示例
12.7.1 示例1：学生成绩分析
12.7.2 示例2：员工薪资分析
12.8 常见问题与解决方案
12.8.1 问题1：函数返回NULL
- 原因：输入参数为NULL，或者函数内部逻辑导致返回NULL
- 解决方案：使用NULL处理函数（如IFNULL、COALESCE）处理NULL值，或者在函数内部添加NULL检查
12.8.2 问题2：函数执行效率低
- 原因：函数内部逻辑复杂，或者在大表上频繁调用函数
- 解决方案：优化函数逻辑，减少函数调用次数，或者考虑使用内置函数替代自定义函数
12.8.3 问题3：函数语法错误
- 原因：函数语法不正确，或者参数类型不匹配
- 解决方案：检查函数语法，确保参数类型匹配，参考数据库文档
12.8.4 问题4：窗口函数结果不符合预期
- 原因：窗口定义不正确，或者排序方式不符合预期
- 解决方案：检查窗口定义，确保PARTITION BY和ORDER BY子句正确，调整窗口范围
12.9 小结
SQL函数是数据库查询和数据处理的重要工具，包括单行函数、聚合函数、窗口函数和自定义函数。单行函数用于处理每行数据，返回一个结果；聚合函数用于对一组数据进行计算，返回一个结果；窗口函数用于对一组数据进行处理，返回多个结果；自定义函数用于实现特定的功能。
在使用SQL函数时，需要根据实际需求选择合适的函数类型，并注意函数的性能、兼容性和安全性。通过合理使用SQL函数，可以简化SQL语句的编写，提高查询效率，实现复杂的数据处理和分析。
12.10 练习题
- 写出计算字符串长度的函数，并举例说明其用法。
- 写出将字符串转换为大写和小写的函数，并举例说明其用法。
- 写出计算当前日期和时间的函数，并举例说明其用法。
- 写出计算两个日期之间天数差的函数，并举例说明其用法。
- 写出使用CASE函数根据成绩返回等级的查询语句。
- 写出使用聚合函数计算学生平均成绩的查询语句。
- 写出使用GROUP BY和聚合函数计算每个班级学生人数的查询语句。
- 写出使用窗口函数计算学生成绩排名的查询语句。
- 写出创建自定义函数的语法，并举例说明。
- 写出使用自定义函数计算年龄的查询语句。
