单行函数
字符函数
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
定义:截取字符串
格式:
-
substr(str, pos)
-
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
定义: 四舍五入
格式:
- round(x)
- 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
定义: 返回当前日期
格式:
- curdate()
- current_date()
实例:
select curdate(); # 2022-02-15
select current_date(); # 2022-02-15
CURTIME/CURRENT_TIME
定义: 返回当前时间
格式:
- curtime()
- 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
定义: 将时间/日期间隔添加到日期,然后返回日期
格式:
- adddate(date, days)
- 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
定义: 返回给定日期的年和周数
格式:
- yearweek(date)/weekofyear(date)
- 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时间戳的形式
格式:
- unix_timestamp()
- unix_timestamp(date)
实例:
select unix_timestamp(); #1651852800
select unix_timestamp(curtime()); #1651852800
FROM_UNIXTIME
定义: 将UNIX时间戳的时间转换为普通格式的时间
格式:
- from_unixtime(unix_timestamp)
- 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
格式:
-
语法和switch case相同
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end ;
-
语法和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)
实例: