数据库函数
一、数学函数
常用的数学函数
● 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 |
±-----------------+