Mysql常用函数

字符函数

length：获取字节个数（utf-8 一个汉字为3个字节，gbk为2个字节）

SELECT LENGTH('lee')    # 输出 3
SELECT LENGTH('lee大帅逼')   # 输出12

concat：拼接字符串

SELECT CONCAT('l','e','e','!')   # 输出 lee!

concat_ws：拼接字符串，用指定字符分隔

SELECT CONCAT_WS('_','l','e','e')   # 输出 l_e_e

upper/ucase：将字母变成大写

SELECT UPPER('lee')    # 输出 LEE

lower/lcase：将字母变成小写

SELECT LOWER('LEE')   # 输出 lee

substr / substring：截取字符串

# substr(str,pos) str:目标字符串 , pos:开始截取的下标
select substr('leeblog',4);     # blog
# substr(str,pos,len)  len:截取个数
select substr('leeblog',4,2);  # bl
# substring同理

left：截取字符串左侧指定个数

SELECT left('chinese', 4);    # chin

right：截取字符串右侧指定个数

SELECT right('chinese', 3);  # ese

instr：返回子串第一次出现的索引，如果没有则返回0

SELECT INSTR('lee','l')   # 输出 1 （mysql是从1开始算位数）

insert：将目标字符串指定下标指定个数替换为指定字符串

SELECT insert('China', 4, 2, 'nese');  # 将字符串'China'从4位置开始的2个字符替换为'nese'-Chinese

trim：字符串去【字符】

SELECT TRIM('  lee  ')   # 输出'lee'
SELECT TRIM('a' from 'aaaaleeaaaa')    # 输出'lee'

ltrim：去除字符串左侧空格

SELECT LTRIM('  lee  ');  # 输出'lee  '

rtrim：去除字符串右侧空格

SELECT RTRIM('  lee  ');  # 输出'  lee'

lpad：用指定字符实现左填充指定长度

SELECT LPAD('lee',6,'*')  # 输出 ***lee

rpad：用指定字符实现右填充指定长度

SELECT RPAD('lee',6,'*')  # 输出 lee***

replace：替换

SELECT REPLACE('lee','ee','ol') # 输出 lol

repeat：指定次数重复拼接字符串

SELECT repeat('lee', 3);  # 重复字符'lee'三次-'leeleelee'

reverse：反向排序

SELECT reverse('lee');   # eel
SELECT reverse('上海自来水来自海上');  # 上海自来水来自海上

数学函数：

abs：绝对值

SELECT abs(-8);     #输出 8

round：四舍五入

SELECT round(1.5)     # 输出  2
SELECT round(-1.5)    # 输出 -2 该四舍五入计算方式为：绝对值四舍五入加负号

ceil：向上取整,返回>=该参数的最小整数

SELECT CEIL(1.5);    # 输出  2
SELECT CEIL(-1.5);    # 输出 -1

floor：向下取整，返回<=该参数的最大整数

SELECT FLOOR(1.5);   # 输出  1
SELECT FLOOR(-1.5);   # 输出 -2

truncate：截断

SELECT TRUNCATE(3.1415926,2);   # 输出 3.14

mod：取余

SELECT MOD(10,3);     # 输出 1
SELECT MOD(10,-3);    # 输出 1

sqrt：平方根

SELECT sqrt(4);     #2

bin、oct、hex：二进制，八进制，十六进制

SELECT bin(15), oct(15), hex(15);#输出 1111 , 17 , F

pi：圆周率

SELECT pi();  # 3.141593

greatest：返回集合中最大的值

SELECT greatest(1,2,3,4,5,6,7,8);  # 8

least：返回集合中最小的值

SELECT least(1,2,3,4,5,6,7,8);  # 1

rand：随机值

SELECT rand();    # 返回０到１内的随机值，每次不一样
SELECT rand(5);   # 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

sign：返回参数正负符号

SELECT sign(-8);  # -1
SELECT sign(0);      # 0
SELECT sign(8);      # 1

日期函数：

now：返回当前系统日期+时间

SELECT NOW()   # 输出 2020-02-16 11:43:21

curdate：返回当前系统日期，不包含时间

SELECT CURDATE()  # 输出 2020-02-16

curtime：返回当前时间，不包含日期

SELECT CURTIME()  # 输出 11:45:35

year/month/day 可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW())  # 输出 2020   其他用法一致

str_to_date：将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('02-17 2020','%c-%d %Y')   # 输出 2020-02-17

date_format：将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')  # 输出 2020年02月17日

datediff：两个日期天数之差

SELECT DATEDIFF(NOW(),'2020-02-12')   # 输出    5

聚合函数

count：总行数

SELECT count(column_name) as total from table_name

sum：总和

SELECT sum(column_name) as sum from table_name

avg：平均值

SELECT avg(column_name) as avg from table_name

max：最大值

SELECT max(column_name) as max from table_name

min：最小值

SELECT min(column_name) as min from table_name

其他函数：

VERSION：查看mysql 版本

SELECT VERSION();   # 输出 5.7.17

DATABASE：查看当前数据库

SELECT DATABASE();   # 输出 cbuc_datebase

USER：查看当前用户

SELECT USER();   # 输出 root@localhost

流程控制函数：

if 函数：类似三目运算

SELECT IF(10<5,'大','小');   # 输出 小

ifnull：参数1为null则返回参数2，否则返回参数1

SELECT ifnull(NULL, 'lee');    # lee
SELECT ifnull(123, 'lee');    # 123

isnull：参数为null返回1，否则返回0

SELECT isnull(1);    # 0
SELECT isnull(null);    # 1

nullif：参数相同或成立返回null，不同或不成立则返回第一个参数

SELECT nullif('lee','lee');     # null
SELECT nullif('lee', 'leeblog');    # lee  
SELECT nullif(2+2=4,2+4=6),nullif(2+2=4,2+2=3);      # null  1--返回第一个参数true

switch case 的效果

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

类似于多重if

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end