数据库函数

数据库函数

一、数学函数

常用的数学函数

● abs（x）返回x的绝对值

示例
mysql> select abs(-6);
+---------+
| abs(-6) |
+---------+
|       6 |
+---------+

● rand（）返回O到1的随机数

示例
mysql> select rand();
+-------------------+
| rand()            |
+-------------------+
| 0.986093550907535 |
+-------------------+

● mod（x，y）返回x除以y以后的余数

示例
mysql> select mod(9,2);
+----------+
| mod(9,2) |
+----------+
|        1 |
+----------+

● power（x，y）返回x的y次方

示例
mysql> select power(9,2);
+------------+
| power(9,2) |
+------------+
|         81 |
+------------+

● round（x）返回离x最近的整数 （ 四舍五入）

示例 
mysql> select round(9.6);
+------------+
| round(9.6) |
+------------+
|         10 |
+------------+

● round（x，y）保留x的y位小数四舍五入后的值

示例
mysql> select round(9.126,2);
+----------------+
| round(9.126,2) |
+----------------+
|           9.13 |
+----------------+

● Sqrt（x）返回x的平方根

示例
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
|       3 |
+---------+

● truncate（x，y）返回数字x截断为y位小数的值

示例

mysql> select truncate(9.12345,3);
+---------------------+
| truncate(9.12345,3) |
+---------------------+
|               9.123 |
+---------------------+

● ceil（x）返回大于或等于x的最小整数 （进位取整）

示例
mysql> select ceil(9.12345);
+---------------+
| ceil(9.12345) |
+---------------+
|            10 |
+---------------+

● floor（x）返回小于或等于x的最大整数

示例
mysql> select floor(9.12345);
+----------------+
| floor(9.12345) |
+----------------+
|              9 |
+----------------+

● greatest（x1，x2.）返回集合中最大的值

示例
mysql> select greatest(9,8,7);
+-----------------+
| greatest(9,8,7) |
+-----------------+
|               9 |
+-----------------+

● least（x1，x2…）返回集合中最小的值

示例
mysql> select least(9,8,7);
+--------------+
| least(9,8,7) |
+--------------+
|            7 |
+--------------+

二、聚合函数

■对表中数据记录进行集中概括而设计的一类函数常用的聚合函数

● avg（）返回指定列的平均值

● count（）返回指定列中非NULL值的个数

● min（）返回指定列的最小值

● max（）返回指定列的最大值sum（）返回指定列的所有值之和

三、字符串函数

■常用的字符串函数

● length（x）返回字符串x的长度

示例
mysql> select length('abcdx');
+-----------------+
| length('abcdx') |
+-----------------+
|               5 |
+-----------------+

● trim（）返回去除指定格式的值 （只能取出字符段两端空格，字段间空格不能去）

示例

mysql> select trim('  abcdx');
+-----------------+
| trim('  abcdx') |
+-----------------+
| abcdx           |
+-----------------+
1 row in set (0.00 sec)

mysql> select trim('  abc dx  ');
+--------------------+
| trim('  abc dx  ') |
+--------------------+
| abc dx             |
+--------------------+
1 row in set (0.00 sec)

●concat（x，y）将提供的参数x和y拼接成一个字符串

示例

mysql> select concat('hello',' world');
+--------------------------+
| concat('hello',' world') |
+--------------------------+
| hello world              |
+--------------------------+

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

示例

mysql> select upper('hello');
+----------------+
| upper('hello') |
+----------------+
| HELLO          |
+----------------+

● lower（x）将字符串x的所有字母变成小写字母

示例

mysql> select lower('HELLO');
+----------------+
| lower('HELLO') |
+----------------+
| hello          |

● left（x，y）返回字符串x的前y个字符

示例

mysql> select left('HELLO',3);
+-----------------+
| left('HELLO',3) |
+-----------------+
| HEL             |
+-----------------+

●right（x，y）返回字符串x的后y个字符

示例

mysql> select right('HELLO',3);
+------------------+
| right('HELLO',3) |
+------------------+
| LLO              |
+------------------+

●repeat（x，y）将字符串x重复y次

示例

mysql> select repeat('HELLO',3);
+-------------------+
| repeat('HELLO',3) |
+-------------------+
| HELLOHELLOHELLO   |
+-------------------+

●Space（x）返回x个空格

示例

mysql> select concat('HELLO',space(3),'world');
+----------------------------------+
| concat('HELLO',space(3),'world') |
+----------------------------------+
| HELLO   world                    |
+----------------------------------+

●replace（x，y，z）将字符串z替代字符串x中的字符串y

示例

mysql> select replace('HELLO','LO','');
+--------------------------+
| replace('HELLO','LO','') |
+--------------------------+
| HEL                      |

●strcmp（x，y）比较x和y，返回的值可以为-1，0，1 （只能自然数比较）

示例

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

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

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

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

示例

mysql> select substring('HELLO',2,3);
+------------------------+
| substring('HELLO',2,3) |
+------------------------+
| ELL                    |
+------------------------+
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（）返回当前时间的年月日

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-08-25 |
+------------+

● curtime（）返回当前时间的时分秒

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 18:41:41  |
+-----------+
1 row in set (0.00 sec)

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

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-08-25 18:42:17 |
+---------------------+
1 row in set (0.00 sec)

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

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

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

mysql> select week(curdate());
+-----------------+
| week(curdate()) |
+-----------------+
|              34 |
+-----------------+

● hour（x）返回x中的小时值

mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
|              18 |
+-----------------+

● minute（x）返回x中的分钟值

mysql> select minute(curtime());
+-------------------+
| minute(curtime()) |
+-------------------+
|                45 |
+-------------------+

● Second（x）返回x中的秒钟值

mysql> select second(curtime());
+-------------------+
| second(curtime()) |
+-------------------+
|                21 |
+-------------------+

● dayofweek（x）返回x是星期几，（1星期日，2星期一）

mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
|                3 |
+------------------+

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

mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
|                25 |
+-------------------+

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

mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
|              238 |
+------------------+

-----------------+
| dayofweek(now()) |
±-----------------+
| 3 |
±-----------------+

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

mysql> select dayofmonth(now());
±------------------+
| dayofmonth(now()) |
±------------------+
| 25 |
±------------------+

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

mysql> select dayofyear(now());
±-----------------+
| dayofyear(now()) |
±-----------------+
| 238 |
±-----------------+