常用函数

常用函数分为单行函数和聚合函数

1.数学函数

数学函数主要用来处理数值数据，主要的数学函数有：绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。有错误产生时，数学函数将会返回空值null。

 

函数名

描述

实例

结果

pi()

返回圆周率的值

seelct pi();

3.141593

abs(x)

返回x的绝对值

select abs(-1005);

1005

sqrt(x)

返回非负数 x 的二次方根

select sqrt(29);

5.385164807134504

pow(x,y)

返回 x 的 y 次乘方

select pow(9, 5);

59049

ceiling(x)

返回大于 x 的最小整数值

select ceiling(1005.5);

1006

floor(x)

返回小于 x 的最大整数值

select floor(1005.6);

1005

mod(x,y)

返回x/y的模（余数）

select mod(10.52,3);

1.52

round(x,y)

返回参数 x 的四舍五入的

有 y 位小数的值

select round(10.05,2);

10.05

truncate(x,y)

返回数字 x 截断为 y 位小数的结果

select truncate(1.00591,4);

1.0059

rand()

返回0到1内的随机值，可以通过提供一个参数（种子）使rand()随机数生成一个指定的值

select rand();

select rand(10);

0.9522263807730995

0.6570515219653505

2.字符串函数

字符串函数主要用来处理数据库中的字符串数据，MySQL 中字符串函数有：计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数】查找指定字符串位置函数等。

函数名

描述

示例

结果

char_length(str)

返回字符串str所含的字符个数

select char_length('你好 xz');

5

length(str)

返回字符串str中的字符的字节长度

select length('你好 xz');

9

ascii(char)

返回字符的ASCII码值

select ascii('z');

122

concat(s1,s2..,sn)

将s1,s2...,sn连接成一个字符串

select concat('I ', 'am ' ,'fine');

I am fine

concat_ws(sep,s1,s2...,sn)

将s1,s2...,sn连接成一个字符串，并用sep字符间隔

select concat_ws('/', '2020','12','4');

2020/12/4

insert(str,x,y,instr)

将字符串str从第x位置开始的y个字符长的字符串替换为字符串instr，返回结果

select insert('you me',3,3,'and');

 yoande

 lcase(str) 或 lower(str)

返回字符串str中所有字符改变为小写后的结果 

 select lcase('china');

select lower('china');

 china

china

ucase(str) 或 upper(str) 

 返回字符串str中所有字符改变为大写后的结果 

 select ucase('china');

select upper('china');

CHINA

CHINA 

left(str, x) 

返回将字符串str中最左边的 x 个字符 

 select left('hello', 2);

 he

right(str, x)

返回将字符串str中最右边的 x 个字符 

select right('hello', 2);

lo

ltrim(str)

从字符串 str 中去掉开头的空格

select         ltrim('  seanxiao')

seanxiao

instr(str,substr)

返回字符串 substr 在字符串 str 第一个出现的位置

select instr('seanxiao', 'ao');

7

position(substr in str)

返回字符串 substr 在字符串 str 第一个出现的位置

select position('i' in 'seanxiao');

6

reverse(str)

返回颠倒字符串str的值

select reverse('oaixnaes');

seanxiao

strcmp(s1, s2)

比较字符串s1和s2,所有字符均相同，返回0；第一个小于第二个，返回-1，其他返回1

select strcmp('xz', 'ab');

1

trim(str)

去除字符串首部和尾部的所有空格

select trim(' me ');

me

lpad(st, len, padstr)

rpad(st, len, padstr)

用字符串 padstr 填补 st 左或右端，直到字符串长度为len

select lpad('seanxiao', 10, '*' );

select rpad('seanxiao', 10, '*');

**seanxiao

seanxiao**

substring(str, n, len)

从 str 字符串的第 n 个位置截取 len 长度个字符。n 若为负值，则从末尾倒数

select substring('seanxiao', -2, 3);

ao

replace(str, s1, s2)

使用字符串s2替代字符串 str 中所有的字符串s1

select replace('xxx.mysql.com', 'x', 'w');

www.mysql.com

space(n)

返回一个由 n 个空格组成的字符串

select concat('*', space(6), '*');

*      *

3.日期和时间函数 

日期和时间函数主要用来处理日期和时间值，一般大额日期函数除了使用 date 类型的参数外，也可以使用 datetime 或者 timestamp 类型的参数，但是忽略这些值的时间部分。相同的，以 time 类型值为参数的函数，可以接受 timestamp 类型的参数，但会忽略日期部分，许多日期函数可以同时接受数字和字符串类型的两种参数。

函数名

描述

实例

结果

curdate()或current_date()或current_date

返回当前的日期

select current_date();

current_date();

2020-12-04| 2020-12-04

curtime()或current_time()

返回当前的时间

select curtime(),current_time();

10:48:22| 10:48:22

now()

返回当前时间和日期

select now();

2020-12-04 10:49:39

date_add(date,interval int keyword)

返回日期date加上间隔时间的结果（int 必须按照关键字进行格式化）

select date_add(current_date,interval 6 month);

2021-06-04

dayofweek(date)

返回date所代表的一个星期中的第几天（1~7）

select dayofweek('1991-10-05');

7

dayofmonth(date)

返回date是一个月的第几天（1~31）

select dayofmonth('1999-09-15');

15

dayofyear(date)

返回date是一年的第几天（1~366）

select dayofyear('1991-10-05');

278

monthname(date)

返回date是几月（按英文名返回）

select monthname('1991-10-05');

October

dayname(date)

返回date的星期名

select dayname(current_date);

Friday

weekday(date)

返回日期date是星期几（0 =星期一，以此类推）

select weekday(current_date);

4

week(date)

返回日期date为一年中第几周（0~53）

select week(current_date);

48

year(date)

返回日期date的年份（1000~9999）

select year(current_date);

2020

hour(time)

返回日期time的最小值（0~23）

select hour(current_time);

11

date_format(date, fmt)

依照指定的fmt格式格式化日期date值

select date_format('2020-12-04', '%w %m %y');

5 12 20

date_format(date, fmt)函数中的fmt字符串中可用的格式

格式

描述

%a

缩写星期名

%b

缩写月名

%c

月，数值

%D

带有英文前缀的月中的天

%d

月的天，数值（00-31）

%e

月的天，数值（0-31）

%f

微秒

%H

小时（00-23）

%h

小时（01-12）

%I

小时（01-12）

%i

分钟，数值（00-59）

%j

年的天（001-366）

%k

小时（0-23）

%l

小时（1-12）

%M

月名

%m

月，数值（00-12）

%p

AM或PM

%r

时间，12-小时（hh:mm:ss AM 或 PM）

%S

秒（00-59）

%s

秒（00-59）

%T

时间，24-小时（hh:mm:ss）

%U

周（00-53）星期日是一周的第一天

%u

周（00-53）星期一是一周的第一天

%V

周（01-53）星期日是一周的第一天，与%X使用

%v

周（01-53）星期一是一周的第一天，与%x使用

%W

星期名

%w

周的天（0=星期天，6=星期六）

%X

年，其中的星期日是周的第一天，4位，与%V使用

%x

年，其中的星期一是周的第一天，4位，与%v使用

%Y

年，4位

%y

年，2位

4.转换函数

使用类型转换函数可以在各种类型数据之间转换数据类型。MySQL中常有的转换函数有cast() 和 convert() 函数，可用来获取一个类型的值，并产生另一个类型的值。

  1)cast(value as type)，即cast(xxx as 类型)

  2)convert(value, type) ，即convert(xxx, 类型)

5.聚合函数

select子句的表达式中可以包含聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。

函数名

描述

实例

avg(all | distinct | col)

返回指定字段的平均值

select avg(age) from stu;

count(all | distinct | col | *)

返回指定字段中非null值的个数

select count(*) from stu;

min(all | distinct | col)

返回指定字段的最小值

select min(age) from stu;

max(all | distinct | col)

返回指定字段的最大值

select max(age) from stu;

sum(all | distinct | col)

返回指定字段所有的所有值

select sum(score) from stu;

group_concat(col)

返回由属于一组的字段值连接组合而成的结果

select class, group_concat(name) from stu group by class;

6.条件判断函数

条件判断函数也称为流程流程函数，根据满足的条件的不同，执行相关的流程。

    (1) if(expr,v1,v2)函数

　　　　如果表达式expr是true(expr != 0 and expr != null )，if() 的返回值为v1；否则返回值为v2。if() 的返回值为数值或字符串值，具体情况是其所在语境而定。

    (2) ifunll(v1,v2)函数

　　　　假如 v1 不为 null，则 ifnull() 的返回值为 v1；否则其返回值为 v2。ifnull() 的返回值是数字或字符串，具体情况取决于其所在的语境。