文章目录

Mysql数据库函数（数字，字符串，日期时间）数学函数字符串函数日期函数

Mysql数据库函数（数字，字符串，日期时间）

数学函数

abs(x) ：返回x的绝对值rand() ：返回0到1的随机数 注意：取不到1mod(x,y)：返回x除以y以后的余数power(x,y)：返回x的y次方round(x)：返回离x最近的整数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(-1),abs(-3.14);+---------+------------+| abs(-1) | abs(-3.14) |+---------+------------+| 1 | 3.14 |+---------+------------+1 row in set (0.00 sec)

rand()返回0到1的随机数

mysql> select rand();+--------------------+| rand() |+--------------------+| 0.6230614116630158 |+--------------------+1 row in set (0.00 sec)

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

mysql> select mod(10,3);+-----------+| mod(10,3) |+-----------+| 1 |+-----------+1 row in set (0.00 sec)

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

mysql> select power(3,2);+------------+| power(3,2) |+------------+|9 |+------------+1 row in set (0.00 sec)

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

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

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

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

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

mysql> select round(1.51);+-------------+| round(1.51) |+-------------+| 2 |+-------------+1 row in set (0.00 sec)

mysql> select round(1.51,1); 保留一位有效数字

+---------------+| round(1.51,1) |+---------------+| 1.5 |+---------------+1 row in set (0.00 sec)

sqrt(x）返回x的平方根

mysql> select sqrt(4);+---------+| sqrt(4) |+---------+| 2 |+---------+1 row in set (0.00 sec)

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

mysql> select truncate(3.1415926,3);+-----------------------+| truncate(3.1415926,3) |+-----------------------+| 3.141 |+-----------------------+1 row in set (0.00 sec)

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

mysql> select ceil(1.9);+-----------+| ceil(1.9) |+-----------+| 2 |+-----------+1 row in set (0.00 sec

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

mysql> select floor(2.9);+------------+| floor(2.9) |+------------+|2 |+------------+1 row in set (0.00 sec)

greatest(x1,×2.-.)返回集合中最大的值

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

mysql> select greatest(10,20,30),least(10,20,30);+--------------------+-----------------+| greatest(10,20,30) | least(10,20,30) |+--------------------+-----------------+| 30 | 10 |+--------------------+-----------------+1 row in set (0.00 sec)

字符串函数

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,1 ## 注意：只能进行个位运算substring(z,y,z)：获取从字符串x中的第y个位置开始长度为z的字符串reverse(x)：将字符串x反转length(x）返回字符串x的长度

mysql> select length('abcd');+----------------+| length('abcd') |+----------------+| 4 |+----------------+1 row in set (0.00 sec)

trim(）返回去除指定格式的值

mysql> select trim(' bdqn ');+----------------+| trim(' bdqn ') |+----------------+| bdqn |+----------------+

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

mysql> select concat('abc','def');+---------------------+| concat('abc','def') |+---------------------+| abcdef |+---------------------+1 row in set (0.00 sec)

综合应用

mysql> select concat ('abc',trim('bdqn'));+-----------------------------+| concat ('abc',trim('bdqn')) |+-----------------------------+| abcbdqn |+-----------------------------+1 row in set (0.00 sec)

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

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

mysql> select upper('abc'),lower('ABC');+--------------+--------------+| upper('abc') | lower('ABC') |+--------------+--------------+| ABC| abc|+--------------+--------------+1 row in set (0.00 sec)

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

mysql> select left('adcdefg',4);+-------------------+| left('adcdefg',4) |+-------------------+| adcd |+-------------------+1 row in set (0.00 sec)

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

mysql> select right('abcdefg',3);+--------------------+| right('abcdefg',3) |+--------------------+| efg|+--------------------+1 row in set (0.00 sec)

综合实验

mysql> select concat(left('adcdef',3),right('abcdefg',3));+---------------------------------------------+| concat(left('adcdef',3),right('abcdefg',3)) |+---------------------------------------------+| adcefg |+---------------------------------------------+1 row in set (0.00 sec)

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

mysql> select repeat('abc',2);+-----------------+| repeat('abc',2) |+-----------------+| abcabc|+-----------------+1 row in set (0.00 sec)

space(x)）返回x个空格

mysql> select concat('a',space('4'),'b');+----------------------------+| concat('a',space('4'),'b') |+----------------------------+| a b |+----------------------------+1 row in set (0.00 sec)

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

mysql> select replace('abc','bc','de');+--------------------------+| replace('abc','bc','de') |+--------------------------+| ade |+--------------------------+1 row in set (0.00 sec)sec

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

mysql> select strcmp(9,7);+-------------+| strcmp(9,7) |+-------------+| 1 |+-------------+1 row in set (0.00 sec)mysql> select strcmp(5,6);+-------------+| strcmp(5,6) |+-------------+|-1 |+-------------+1 row in set (0.00 sec)mysql> select strcmp(5,5);+-------------+| strcmp(5,5) |+-------------+| 0 |+-------------+1 row in set (0.00 sec)

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

mysql> select substring('adcdefg',3,4);+--------------------------+| substring('adcdefg',3,4) |+--------------------------+| cdef |+--------------------------+1 row in set (0.00 sec)

reverse(x)将字符串x反转

mysql> select reverse ('hello');+-------------------+| reverse ('hello') |+-------------------+| olleh |+-------------------+1 row in set (0.00 sec)

日期函数

curdate(x)：返回当前时间的年月日curtime(x)：返回当前时间的时分秒now(x)：返回房钱时间的日期和时间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)：计算日期x是本年的第几天

curdate(）返回当前时间的年月日

mysql> select curdate();+------------+| curdate() |+------------+| -08-25 |+------------+1 row in set (0.00 sec)

curtime(）返回当前时间的时分秒

mysql> select curtime();+-----------+| curtime() |+-----------+| 16:30:34 |+-----------+1 row in set (0.00 sec)

now(）返回当前时间的日期和时间

mysql> select now();+---------------------+| now()|+---------------------+| -08-25 16:29:37 |+---------------------+1 row in set (0.00 sec)

month(x）返回日期x中的月份值

mysql> select month('-08-25');+---------------------+| month('-08-25') |+---------------------+| 8 |+---------------------+1 row in set (0.00 sec)

week(x）返回日期x是年度第几个星期

mysql> select week('-08-25');+--------------------+| week('-08-25') |+--------------------+| 34 |+--------------------+1 row in set (0.00 sec)

hour(x）返回x中的小时值

mysql> select hour(curtime());+-----------------+| hour(curtime()) |+-----------------+| 16 |+-----------------+1 row in set (0.00 sec)

minute(x）返回x中的分钟值

mysql> select minute(curtime());+-------------------+| minute(curtime()) |+-------------------+|36 |+-------------------+1 row in set (0.00 sec)

second(x)返回x中的秒钟值

mysql> select second(curtime());+-------------------+| second(curtime()) |+-------------------+|58 |+-------------------+1 row in set (0.00 sec)

dayofweek(x）返回x是星期几，1星期日，2星期

mysql> select dayofweek(curtime());+----------------------+| dayofweek(curtime()) |+----------------------+|3 |+----------------------+1 row in set (0.00 sec)

dayofmonth(x）计算日期x是本月的第几天

mysql> select dayofmonth(curtime());+-----------------------+| dayofmonth(curtime()) |+-----------------------+|25 |+-----------------------+1 row in set (0.00 sec)

dayofyear(x）计算日期x是本年的第几天

mysql> select dayofyear(curtime());+----------------------+| dayofyear(curtime()) |+----------------------+| 238 |+----------------------+1 row in set (0.00 sec)