常用的函数分类

数字函数常用的数学函数附加 聚合函数常用的聚合函数 字符串函数常用的字符串函数案例 日期时间函数

数字函数

常用的数学函数

函数描述abs(x)返回x的绝对值rand()返回0到1的随机数mod(x,y)返回x除以y以后的余数power(x,y)返回x的y次方round(x)返回离x最近的整数round(x,y)保留x的y位小数四舍五入后的值sqrt(x)返回x的平方根truncate(x,y)返回数字x截断为y位小数的值ceil(x)返回大于或等于x的最小整数floor(x)返回小于或等于x的最大整数greatest(x1,x2…)返回集合中最大的值least(x1,x2…)返回集合中最小的值

abs(x) 返回x的绝对值

mysql> select abs(-10); +----------+ | abs(-10) | +----------+ | 10 | +----------+

rand() 返回0到1的随机数 (能取到0但取不到1，无线接近于1。）

mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.5652262238461768 | +--------------------+

mod(x,y) 返回x除以y以后的余数

mysql> select mod(7,2); +----------+ | mod(7,2) | +----------+ | 1 | +----------+

power(x,y) 返回x的y次方

mysql> select power(2,3); +------------+ | power(2,3) | +------------+ | 8 | +------------+

round(x) 返回离x最近的整数

mysql> select round(1.5); +------------+ | round(1.5) | +------------+ | 2 | +------------+ mysql> select round(1.49); +-------------+ | round(1.49) | +-------------+ | 1 | +-------------+

round(x,y) 保留x的y位小数四舍五入后的值

mysql> select round(1.49,1); +---------------+ | round(1.49,1) | +---------------+ | 1.5 | +---------------+ mysql> select round(1.4449,3); +-----------------+ | round(1.4449,3) | +-----------------+ | 1.445 | +-----------------+

sqrt(x) 返回x的平方根

mysql> select sqrt(9); +---------+ | sqrt(9) | +---------+ | 3 | +---------+ mysql> select sqrt(8); +--------------------+ | sqrt(8) | +--------------------+ | 2.8284271247461903 | +--------------------+

truncate(x,y) 返回数字x截断为y位小数的值

mysql> select truncate(1.4449,2); +--------------------+ | truncate(1.4449,2) | +--------------------+ | 1.44 | +--------------------+

ceil(x) 返回大于或等于x的最小整数

mysql> select ceil(1.01); +------------+ | ceil(1.01) | +------------+ | 2 | +------------+

floor(x) 返回小于或等于x的最大整数

mysql> select floor(1.01); +-------------+ | floor(1.01) | +-------------+ | 1 | +-------------+

greatest(x1,x2…) 返回集合中最大的值

mysql> select greatest(1,15,-8,17); +----------------------+ | greatest(1,15,-8,17) | +----------------------+ | 17 | +----------------------+

least(x1,x2…) 返回集合中最小的值

mysql> select least(1,15,-8,17); +-------------------+ | least(1,15,-8,17) | +-------------------+ | -8 | +-------------------+

附加

（1）取1-100随机数

mysql> select ceil(rand()*100); +------------------+ | ceil(rand()*100) | +------------------+ | 59 | +------------------+

随机输出0-2

mysql> select mod(ceil(rand()*100),3); +-------------------------+ | mod(ceil(rand()*100),3) | +-------------------------+ | 1 | +-------------------------+

聚合函数

对表中数据记录进行集中概括而设计的一类函数。

常用的聚合函数

函数描述avg()返回指定列的平均值count()返回指定列中非NULL值的个数min()返回指定列的最小值max()返回指定列的最大值sum()返回指定列的所有值之和

字符串函数

常用的字符串函数

函数描述length(x)返回字符串x的长度trim()返回去除指定格式的值concat(x,y)将提供的参数x和y拼接成一个字符串upper(x)将字符串x的所有字母变成大写字母lower(x)将字符串x的所有字母变成小写字母left(x,y)返回字符串x的前y个字符right(x,y)返回字符串x的后y个字符repeat(x,y)将字符串x重复y次space(x)返回x个空格replace(x,y,z)将字符串z替代字符串x中的字符串ystrcmp(x,y)比较x和y，返回的值可以为-1,0,1substring(x,y,z)获取从字符串x中的第y个位置开始长度为z的字符串reverse(×)将字符串x反转

案例

** length(x) 返回字符串x的长度**

mysql> select length('abc'); +---------------+ | length('abc') | +---------------+ | 3 | +---------------+ mysql> select length(' '); +-------------+ | length(' ') | +-------------+ | 1 | +-------------+

**trim(）返回去除指定格式的值 **（只去头和尾，中间不去）

mysql> select trim('abc '); +--------------+ | trim('abc ') | +--------------+ | abc | +--------------+ mysql> select length(trim('abc ')); +----------------------+ | length(trim('abc ')) | +----------------------+ | 3 | +----------------------+ mysql> select length(trim(' abc ')); +-----------------------+ | length(trim(' abc ')) | +-----------------------+ | 3 | +-----------------------+ mysql> select length(trim(' ab c ')); +------------------------+ | length(trim(' ab c ')) | +------------------------+ | 4 | +------------------------+

concat(x,y) 将提供的参数x和y拼接成一个字符串

mysql> select length(concat('abc','cba')); +-----------------------------+ | length(concat('abc','cba')) | +-----------------------------+ | 6 | +-----------------------------+

upper(x）将字符串x的所有字母变成大写字母

mysql> select upper('abc'); +--------------+ | upper('abc') | +--------------+ | ABC | +--------------+

lower(x) 将字符串x的所有字母变成小写字母

mysql> select lower('ABC'); +--------------+ | lower('ABC') | +--------------+ | abc | +--------------+

left(x,y) 返回字符串x的前y个字符

mysql> select left('abcdse',2); +------------------+ | left('abcdse',2) | +------------------+ | ab | +------------------+

right(x,y) 返回字符串x的后y个字符

mysql> select right('abcdse',2); +-------------------+ | right('abcdse',2) | +-------------------+ | se | +-------------------+

repeat(x,y) 将字符串x重复y次

mysql> select repeat('abcdse',2); +--------------------+ | repeat('abcdse',2) | +--------------------+ | abcdseabcdse | +--------------------+

space(x）返回x个空格

mysql> select concat('a',space(3),'b'); +--------------------------+ | concat('a',space(3),'b') | +--------------------------+ | a b | +--------------------------+ mysql> select length(concat('a',space(3),'b')); +----------------------------------+ | length(concat('a',space(3),'b')) | +----------------------------------+ | 5 | +----------------------------------+

replace(x,y,z) 将字符串z替代字符串x中的字符串y

mysql> select replace('abcddf','bc','12'); +-----------------------------+ | replace('abcddf','bc','12') | +-----------------------------+ | a12ddf | +-----------------------------+ mysql> select replace('abcddf','bc',''); +---------------------------+ | replace('abcddf','bc','') | +---------------------------+ | addf | +---------------------------+

strcmp(x,y) 比较x和y，返回的值可以为-1,0,1

mysql> select strcmp(4,5); +-------------+ | strcmp(4,5) | +-------------+ | -1 | +-------------+ mysql> select strcmp(6,5); +-------------+ | strcmp(6,5) | +-------------+ | 1 | +-------------+ mysql> select strcmp(6,15); #比较同个数的字符串，这里是6与1比，而不是6与15比 +--------------+ | strcmp(6,15) | +--------------+ | 1 | +--------------+

substring(x,y,z）获取从字符串x中的第y个位置开始长度为z的字符串

mysql> select substring('absdkxj',2,3); +--------------------------+ | substring('absdkxj',2,3) | +--------------------------+ | bsd | +--------------------------+ mysql> select substring('absdkxj',6,5); #超过就不显示 +--------------------------+ | substring('absdkxj',6,5) | +--------------------------+ | xj | +--------------------------+

reverse(×) 将字符串x反转

mysql> select reverse('Hello'); +------------------+ | reverse('Hello') | +------------------+ | olleH | +------------------+

日期时间函数

MySQL也支持日期时间处理，提供了很多处理日期和时间的函数。

字符串函数描述curdate（）返回当前时间的年月日curtime（）返回当前时间的时分秒now（）返回当前时间的日期和时间month（x）返回日期x中的月份值week（x）返回日期x是年度第几个星期hour（x）返回x中的小时值minute（x）返回x中的分钟值second（x）返回x中的秒钟值dayofweek（x）返回x是星期几，1星期日，2星期一dayofmonth（x）计算日期x是本月的第几天dayofyear（x）计算日期x是本年的第几天 mysql> select curdate(),curtime(),now(),month('2020-10-17'),week('2020-10-17'),hour('21:13:52'); +------------+-----------+---------------------+---------------------+--------------------+------------------+ | curdate() | curtime() | now() | month('2020-10-17') | week('2020-10-17') | hour('21:13:52') | +------------+-----------+---------------------+---------------------+--------------------+------------------+ | 2020-10-20 | 23:18:49 | 2020-10-20 23:18:49 | 10 | 41 | 21 | +------------+-----------+---------------------+---------------------+--------------------+------------------+ mysql> select minute('21:22:15'),second('21:22:15'),dayofweek('2020-10-18'),dayofmonth('2020-10-18'),dayofyear('2020-10-18'); +--------------------+--------------------+-------------------------+--------------------------+-------------------------+ | minute('21:22:15') | second('21:22:15') | dayofweek('2020-10-18') | dayofmonth('2020-10-18') | dayofyear('2020-10-18') | +--------------------+--------------------+-------------------------+--------------------------+-------------------------+ |                 22 |                 15 |                       1 |                       18 |                     292 | +--------------------+--------------------+-------------------------+--------------------------+-------------------------+