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;
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符 “速评一下”
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页