SQL 常见函数

概述

大体分为两类

• 单行函数：执行之后，返回结果里 字段的值不会减少，一般用于对字段进行操作。
  • 比如： length()、ifnull()、concat()
• 聚合函数：又称分组函数，执行之后，字段里的值会减少，一般用于统计。
  • 比如：

单行函数

字符函数

length() 参数的字节长度

select length("aaa");

拼接 concat()

select concat("aaa","bbb");

大小写 upper | lower

select upper("aa");
select lower("AA");

截取 索引从 1 开始

# 第三个参数是 截取长度
select substring("abc",2,3);

instr 返回子串第一次出现的位置，没有返回0

select instr("abcd","b")  as output;

trim() 去掉前后空格，可以指定去掉的字符串

select trim("  aaa   ");
select trim("a" from "aba") as output;
-------- 
output|
------|
b     |

lpad() 左填充到指定长度，可指定填充字符

select lpad("吼吼吼",10,"*") as outpat;

rpad() 右填充到指定长度，可指定填充字符

select rpad("吼吼吼",10,"*") as outpat;

replace() 替换指定字符串，是全部替换

select replace("aa吼吼吼","aa","bbb") as outpat;

数学函数

四舍五入

select round(4.56);
select round(4.567,2); # 保留两位小数

向上取整 ， >= 这个数

select ceil(4.00);

向下取整

select floor(4.56);

取余数，跟 % 一样

select mod(10,3);

保留几位小数，不会进行四舍五入

select truncate(1.5665,2);

日期函数

现在时间

select now() as 现在时间; 
------------------------
现在时间              |
---------------------|
2020-02-28 16:40:44.0|

现在日期

select curdate() as 现在日期; 
----------------------------
现在日期   |
----------|
2020-02-28|

现在时间

select curtime() as 现在时间; 
----------------------------
现在时间 |
--------|
16:45:56|

只截取日期格式里的某一部分，如 年、月、日…

其他就比如：year()、month()、day()、hour()、minute()、second()

select year(now()) as 现在的年; 
-------------------------------
现在的年|
----|
2020|

提取字符串中的时间

select str_to_date('2019--01-20 16:01:45', '%Y--%m-%d') as 提取日期;
-------------------------
提取日期   |
----------|
2019-01-20|

时间 转换 时间字符串

select date_format("1999-1-12","%Y年%m月%d日") as 日期字符串; 
------------------------------------
日期字符串  |
-----------|
1999年01月12日|

其他函数

版本号

select version();

当前数据库

select database();

当前用户

select user();

流程控制函数

if 流程控制，实现 if else 效果

select if(10>5,"大","小") as 结果;
---------------------------------
结果|
--|
大 |

case 相当于 Scala 的模式匹配

第一种匹配

# 格式

select 
case 字段或者表达式
when 待匹配值 then 值或表达式
when 待匹配值 then 值或表达式
...
else 未匹配到的处理
end 结束标志
----------------------------

select 
case 10%3
when 1 then "奇数"
when 0 then "偶数"
else "未知"
end as "类型"
-----------------
类型|
--|
奇数|

第二中匹配，主要区别是 case 不用写值，其他都一样。

# 注意先匹配的先后顺序
select Name,Population,
case 
when Population > 120000 then "一级" 
when Population > 110000 then "二级"
else "其他"
end as "等级"
from world.city 
---------------
Name                             |Population|等级|
---------------------------------|----------|--|
Serang                           |    122400|一级|
Probolinggo                      |    120770|一级|
Cilegon                          |    117000|二级|
Cianjur                          |    114300|二级|
Ciparay                          |    111500|二级|
Lhokseumawe                      |    109600|其他|

聚合函数

简单的函数

• max(): 最大值
• min(): 最小值
• avg(): 平均值
• count(): 统计数量
• sum(): 求和

1. 以上函数 都忽略 null 值，比如: count() 结果 不会统计 null 值，所以结果并不是 字段中有多少个值。
2. 都可以与 distinct去重 配合使用，常与 count() 搭配更多。count(distinct Population)
3. 单独使用 count():
   1. count(*)：这样返回的就是真正的行数，全部字段一排里只要有一个字段不为 null就会加 1。
   2. count(1)：会生成一行虚拟的值(值全为1)，统计这一行。
4. 和分组函数一同查询的字段的要求是 group by 操作后的字段。

select max(Population) 最大值,min(Population) 最小值,avg(Population) 平均值,count(Population) 数量,sum(Population) 和  
from world.city 
---------------
最大值 |最小值|平均值    |数量  |和       |
--------|---|-----------|----|----------|
10500000| 42|350468.2236|4079|1429559884|