Java面向对象系列[v1.0.0][SQL语法之数据库函数]

数据库函数

每个数据库都会在标准SQL基础上扩展一些函数，用于进行数据处理和复杂计算，例如前边的链接字符串函数concat，函数可以出现在SQL语句的各个位置，比较常用的是在select之后和where子句中

单行函数

单行函数对每行输入值单独计算，每行得到一个计算结果返回给用户，不同的数据库单行函数差别比较大，在MySQL中的单行函数：

• 单行函数的参数可以是变量、常量或数据列，单行函数可以接收多个参数，但返回一个值
• 单行函数会对每行单独起作用，每行（可能包含多个参数）返回一个结果
• 使用单行函数可以改变参数的数据类型，单行函数支持嵌套使用，即内层函数的返回值是外层函数的参数
  
  MySQL数据库的数据类型大致分为数值型、字符型和日期时间型，MySQL也提供了对应的函数；转换函数主要负责完成类型转换，除此之外还有位函数、流程控制函数、加解密函数、信息函数

char_length()

# 选出davieyang_test表中davieyang_name列的字符长度
select char_length(davieyang_name) from davieyang_test;

sin()

# 计算davieyang_name列的字符长度的sin值
select sin(char_length(davieyang_name)) from davieyang_test;
select sin(1.57);

DATE_ADD()

# 为指定日期添加一定的时间
# 在这种用法下interval是关键字，需要一个数值和单位
select DATE_ADD('2020-01-02', interval 2 MONTH);

ADDDATE()

select ADDDATE('2020-01-02', 3);

获取当前日期

select CURDATE();

获取当前时间

select curtime();

MD5()

select MD5('test');

处理null

• ifnull(expr1, expr2):如果expr1为null，则返回expr2，否则返回expr1
• nullif(expr1, expr2):如果expr1和expr2相等，则返回null, 否则返回expr1
• if(expr1,expr2,expr3):类似三目运算，如果expr1为true，不等于0，且不等于null，则返回expr2，否则返回expr3
• isnull(expr1):判断expr1是否为null，如果为null则返回true否则返回false

# 如果davieyang_name为null，则返回'没有名字'
select ifnull(davieyang_name, '没有名字') from davieyang_test;

# 如果davieyang_name等于'davieyang'，则返回null
select nullif(davieyang_name, 'davieyang') from davieyang_test;

# 如果davieyang_name为null，则返回'没有名字',否则返回'有名字'
select if(isnull(davieyang_name), '没有名字', '有名字') from davieyang_test;

流程控制函数case

case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end

select davieyang_name, case davieyang_age
when 1 then '1'
when 2 then '2'
else '3'
end
from davieyang_test;

case
when condition1 then result1
when condition2 then result2
...
else result
end

select davieyang_name, case
when davieyang_age>3 then '大于3'
when davieyang_age<=6 then '小于等于6'
else '要啥没啥就这个'
end
from davieyang_test;

通常不建议在程序中使用特定的数据库函数，这将导致程序代码与特定数据库耦合，移植到其他数据库上将会是非常麻烦的事

多行函数

多行函数对多行输入值整体计算，最后得到一个结果，多行函数也称为聚集函数，分组函数主要用于完成一些统计功能，在大部分数据库中基本相同

• avg([distinct|all]expr):计算多行expr的平均值，expr可以是变量，常量，数据列，但数据类型必须是数值型，还可以在变量、列前使用distinct或all关键字，使用distinct表明不计算重复值，all用和不用效果一样表明需要计算重复值
• count({*|[distinct|all]expr}):计算多行expr的总条数，expr可以是变量、常量、数据列，数据类型可以是任意类型，用星号表示统计该表内的记录行数，distinct表示不计算重复值，使用count统计行数时null不会被计算在内
• max(expr):计算多行expr的最大值，expr可以使变量、常量、数据列，数据类型可以是任意类型
• min(expr):计算多行expr的最小值，expr可以使变量、常量、数据列，数据类型可以是任意类型
• sum([distinct|all]expr):计算多行expr的综合，expr可以是变量、常量、数据列，数据类型必须是数值型，distinct表示不计算重复值

# 计算davieyang_test表中的记录条数
select count(*) from davieyang_test;

# 计算davieyang_age列总用有多少个值
select count(distinct davieyang_age) from davieyang_test;

# 统计所有davieyang_id的总和
select sum(davieyang_id) from davieyang_test;

# 计算的结果是20*记录的行数
select sum(20) from davieyang_test;

# 因为sum里的expr是常量34，所以每行的值都相同
# 使用distinct强制不计算重复值，所以计算结果为34
select sum(distinct 34) from davieyang_test;

select max(davieyang_id) from davieyang_test;

select min(davieyang_id) from davieyang_test;

# 计算davieyang_age列所有记录的平均值
select avg(ifnull(davieyang_age, 0)) from davieyang_test;

默认情况下，组函数会把所有记录当成一组，如果想显示的进行分组，可以使用group by子句，其后跟一个或多个列名，表明查询结果是根据一列或多列进行分组，当一列或多列组合的值完全相同时，系统会把这些记录当成一组

# count(*)将会对每组得到一个结果
select count(*) from davieyang_test
# 将davieyang_age列值相同的记录当成一组
group by davieyang_age;

如果对多列进行分组，则要求多列的值完全相同才会被当成一组

select count(*) from davieyang_test
# 当davieyang_age, davieyang_name两列值完全相同时才会被当成一组
group by davieyang_age, davieyang_name;

对于很多数据库而言，分组计算有严格的规则即如果查询列表中使用了组函数，或者select语句使用了group by分组子句，则要求出现在select列表中的字段，要么使用函数包起来，要么必须出现在group by子句中，但MySQL没有这个要求，如果某个数据列既没有出现在group by之后，也没有使用组函数包起来，则MySQL会输出该列的第一条记录的值

---

如果要对分组进行过滤，应该使用having子句，它也是一个条件表达式，只有满足该条件表达式的分组才会被选出来，having和where很容易混淆，区别如下：

• 不能在where子句中过滤组，只能用where过滤行，过滤组必须用having
• 不能在where子句中使用组函数，having子句中才能使用组函数

select * from davieyang_test group by davieyang_age having count(*)>2;