【MySQL基础】常用函数

单行函数

字符函数

LENGTH

定义：获取参数值的字节个数

格式： length(str)

实例：

select length('MySQL');	#5
select length('你好MySQL');   #11

备注： UTF-8编码下，一个汉字三个字节；GBK编码下，一个汉字两个字节。

CONCAT

定义： 拼接字符串

格式： concat(str1,str2…)

实例：

select concat('MySQL','123');  # MySQL123

UPPER

定义： 将字符串中的小写转换成大写

格式： upper(str)

实例：

select upper('MySQL');  # MYSQL

LOWER

定义： 将字符串中的大写转换成小写

格式： lower(str)

实例：

select upper('MySQL');  # mysql

SUBSTR/SUBSTRING

定义：截取字符串

格式：

1. substr(str, pos)

2. substr(str, pos, len)

实例：

select substr('Mysql123', 5);	# 123
select substr('Mysql123', 1, 5);	# Mysql

INSTR

定义： 返回子串第一次出现的索引，如果找不到返回0

格式： instr(str, substr)

实例：

select instr('aaaaaMySQL123MySQL' , 'MySQL');	# 6

TRIM

定义： 去除首尾指定字符，默认去除空格

格式： trim([remstr from] str)

实例：

select trim('	MySQL123 '); # MySQL123
select trim('aaa' from 'aaaaaMySQL123'); # aaMySQL123 

LPAD

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

格式： lpad(str, len, padstr)

实例：

select lpad('MySQL123', 10, 'a'); #aaMySQL123

RPAD

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

格式： rpad(str, len, padstr)

实例：

select rpad('MySQL123', 10, 'a'); #MySQL123aa

REPLACE

定义： 替换

格式： replace(str, from_str, to_str)

实例：

select replace('MySQL123', 'My', 'mY'); #mYSQL123

数学函数

ABS

定义： 绝对值

格式： abs(x)

实例：

select abs(-1); #1

ROUND

定义： 四舍五入

格式：

1. round(x)
2. round(x, d)

实例：

select round(1.234); # 1
select round(1.254, 1); # 1.3

CEIL

定义： 向上取整，返回> =到数字的最小整数值

格式： ceil(x)

实例：

select ceil(1.2);	#2
select ceil(-1.2);	#-1

FLOOR

定义： 向下取整，返回<=到数字的最大整数值

格式： floor(x)

实例：

select floor(1.2);	#1
select floor(-1.2);	#-2

TRUNCATE

定义： 将数字截断为指定的小数位数

格式： truncate(x, d)

实例：

select truncate('231.666', 1);	# 231.6
select truncate('231', 1);	# 231.0

MOD

定义： 取余

格式： mod(n, m)

实例：

select mod(10, 3);	#1
select mod(10, -3);	#1
select mod(-10, 3);	#-1
select mod(-10, -3);	#-1

备注： 结果可以套用公式 n-n/m*m 验证

POW

定义： 幂函数

格式： pow(x, y)

实例：

select pow(2,2); #4
select pow(4,1/2); #2

RAND

定义： 随机函数

格式： rand()

实例：

select rand(); #0.6328324355490808

日期函数

NOW/SYSDATE

定义： 返回当前日期和时间

格式： now()/sysdate()

实例：

select now(); # 2022-02-15 13:47:50

CURDATE/CURRENT_DATE

定义： 返回当前日期

格式：

1. curdate()
2. current_date()

实例：

select curdate(); # 2022-02-15
select current_date(); # 2022-02-15

CURTIME/CURRENT_TIME

定义： 返回当前时间

格式：

1. curtime()
2. current_time()

实例：

select curtime(); # 13:47:50
select current_time(); # 13:47:50

STR_TO_DATE

定义： 返回基于字符串和格式的日期

格式： str_to_date(str, format)

实例：

/*
%Y  四位的年份;  %y 2位的年份
%m 月份(01,02....11,12; %c 月份(1,2...11,12)
%d 日(01,02,...)
%H 小时(24小时制);   %h 小时(12小时制)
%i 分钟(00,01...59);
%s 秒(00,01...59);
*/
select str_to_date('2022-02-15 13:47:50','%Y-%m-%d %H:%i:%s'); # 2022-02-15 13:47:50

DATE_FORMAT

定义： 将日期转换成字符

格式： date_format(date, format)

实例：

/*
%Y  四位的年份;  %y 2位的年份
%m 月份(01,02....11,12; %c 月份(1,2...11,12)
%d 日(01,02,...)
%H 小时(24小时制);   %h 小时(12小时制)
%i 分钟(00,01...59);
%s 秒(00,01...59);
*/
select date_format(now(),'%Y-%m-%d %H:%i:%s'); # 2022-02-15 13:47:50

YEAR、MONTH、DAY、HOUR、MINUTE、SECOND

定义： 获取指定的部分，年、月、日、小时、分钟、秒

格式： year(date)、month(date)、day(date)、hour(date)、minute(date)、second(date)

实例：

select year(now()) 年; # 2022
select year('2022-1-1') 年; # 2022
select month(now()) 月; # 2

ADDDATE/DATE_ADD/DATE_SUB

定义： 将时间/日期间隔添加到日期，然后返回日期

格式：

1. adddate(date, days)
2. adddate(date, INTERVAL expr unit) /date_add(date, INTERVAL expr unit)/date_sub(date, INTERVAL expr unit)

实例：

select adddate('2022-02-15 13:47:50', 1);  #2022-02-16 13:47:50
select ADDDATE('2022-02-15 13:47:50', INTERVAL 2 year);  #2024-02-15 13:47:50

DATEDIFF

定义： 返回两个日期值之间的天数

格式： datediff(expr1, expr2)

实例：

select datediff("2022-02-15", "2022-02-01");  #14

YEARWEEK/WEEKOFYEAR/WEEK

定义： 返回给定日期的年和周数

格式：

1. yearweek(date)/weekofyear(date)
2. yearweek(date, mode)/week(date, mode)

实例：

select yearweek("2010-3-14");  # 201011
/*
mode
0 - 星期的第一天是星期日
1 - 一周的第一天是星期一，第一周是超过3天
2 - 星期的第一天是星期天
3 - 一周的第一天是星期一，第一周是超过3天
4 - 一周的第一天是星期日，第一周是超过3天
5 - 一周的第一天是星期一
6 - 周的第一天是星期日，第一周的时间超过3天
7 - 一周的第一天是星期一
*/
select yearweek("2010-3-14", 1);  # 201010

UNIX_TIMESTAMP

定义： 将日期(含时分秒)转换成UNIX时间戳的形式
格式：

1. unix_timestamp()
2. unix_timestamp(date)

实例：

select unix_timestamp(); #1651852800
select unix_timestamp(curtime());  #1651852800

FROM_UNIXTIME

定义： 将UNIX时间戳的时间转换为普通格式的时间
格式：

1. from_unixtime(unix_timestamp)
2. from_unixtime(unix_timestamp, format)

实例：

select from_unixtime(1651852800); #2022-05-07 00:00:00
select from_unixtime(1651852800,'%Y-%m-%d');  #2022-05-07

流程控制函数

IF

定义： 如果条件为TRUE则返回值，如果条件为FALSE则返回另一个值

格式： if(expr1, expr2, expr3)

实例：

select if(500<1000, 5, 10); # 5

CASE

格式：

1. 语法和switch case相同

   case 要判断的字段或表达式

   when 常量1 then 要显示的值1或语句1

   when 常量2 then 要显示的值2或语句2

   …

   else 要显示的值n或语句n

   end ;

2. 语法和if else相同

   case

   when 条件1 then 要显示的值1或语句1

   when 条件2 then 要显示的值2或语句2

   …

   else 要显示的值n或语句n

   end

实例：

select  case 5>4 
when  0 then  'result1'
when 1 then 'result2'  
else  'result3' end; # result2

select  case 
when 5>4 then  'result1'
when 5<6 then 'result2'  
else  'result3' end; # result1

其他函数

VERSION

定义： 返回MySQL数据库的当前版本

格式： version()

实例：

select version(); # 5.7.28

DATABASE

定义： 返回当前数据库的名称

格式： database()

实例：

select database(); # northwind

USER

定义： 返回当前的MySQL用户名和主机名

格式： user()

实例：

select user(); #demo@localhost

CAST/CONVERT

定义： 将值（任何类型）转换为指定的数据类型

格式： cast(expr as type)/convert(expr, type)

实例：

/*
type
DATE 日期格式：“YYYY-MM-DD”
DATETIME 日期和时间格式：“YYYY-MM-DD HH：MM：SS”
TIME 时间格式：“HH：MM：SS”
CHAR 固定长度的字符串
SIGNED 签名的64位整数
UNSIGNED 无符号的64位整数
BINARY 二进制字符串
*/
select cast("2017-08-29" as date); # 2017-08-29 

ISNULL

定义： 返回1或0，具体取决于表达式是否为NULL

格式： isnull(expr)

实例：

select isnull(null); #1 

IFNULL

定义： 如果表达式为NULL，则返回指定的值，否则返回表达式

格式： ifnull(expr1,expr2)

实例：

select ifnull(null, 'www'); #www
select ifnull('www', 'qqq'); #www

分组函数

用作统计，又称为聚合函数或统计函数或组函数，和分组函数一同查询的字段要求是group by后的字段

SUM

定义： 计算某列数值之和，忽略NULL值，只能用于数值型。

格式： sum([distinct] expr)

实例：

AVG

定义： 计算某列平均值，忽略NULL值，只能用于数值型。

格式： avg([distinct] expr)

实例：

MAX

定义： 计算某列的最大值，忽略NULL值。

格式： max([distinct] expr)

实例：

MIN

定义： 计算某列的最小值，忽略NULL值。

格式： min([distinct] expr)

实例：

COUNT

定义： 计算某列的最小值，忽略NULL值，一般使用count(*)用作统计行数。

效率：
​ MYISAM存储引擎下，count(*)的效率高

​ INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高些。

格式： min([distinct] expr)

实例：