ABS()
:返回绝对值。
mysql> select abs(-100);
+-----------+
| abs(-100) |
+-----------+
| 100 |
+-----------+
1 row in set (0.01 sec)
AVG()
:返回指定数值字段的平均值。或与 group by 联用,返回一个分组的平均值。
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 52117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10002 | 72527 | 1996-08-03 | 1997-08-03 |
| 10002 | 72527 | 1997-08-03 | 1998-08-03 |
| 10002 | 72527 | 1998-08-03 | 1999-08-03 |
| 10003 | 43616 | 1996-12-02 | 1997-12-02 |
| 10003 | 43466 | 1997-12-02 | 1998-12-02 |
+--------+--------+------------+------------+
7 rows in set (0.01 sec)
mysql> select avg(salary) from salaries;
+-------------+
| avg(salary) |
+-------------+
| 59840.2857 |
+-------------+
1 row in set (0.00 sec)
mysql> select emp_no, avg(salary) from salaries group by emp_no;
+--------+-------------+
| emp_no | avg(salary) |
+--------+-------------+
| 10001 | 57109.5000 |
| 10002 | 72527.0000 |
| 10003 | 43541.0000 |
+--------+-------------+
3 rows in set (0.00 sec)
CURRENT_TIMESTAMP
:返回当前的时间和日期。
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2021-01-31 13:27:21 |
+---------------------+
1 row in set (0.00 sec)
LENGTH()
:返回字符串的长度。
mysql> select length('hello');
+-----------------+
| length('hello') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
MAX()
,MIN()
:返回指定数值字段中的最大值。或与 group by 联用,返回一个分组中的最大值。MIN()
与 MAX()
相反。
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 52117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10002 | 72527 | 1996-08-03 | 1997-08-03 |
| 10002 | 72527 | 1997-08-03 | 1998-08-03 |
| 10002 | 72527 | 1998-08-03 | 1999-08-03 |
| 10003 | 43616 | 1996-12-02 | 1997-12-02 |
| 10003 | 43466 | 1997-12-02 | 1998-12-02 |
+--------+--------+------------+------------+
mysql> select max(salary) from salaries;
+-------------+
| max(salary) |
+-------------+
| 72527 |
+-------------+
1 row in set (0.00 sec)
mysql> select emp_no, max(salary) from salaries group by emp_no;
+--------+-------------+
| emp_no | max(salary) |
+--------+-------------+
| 10001 | 62102 |
| 10002 | 72527 |
| 10003 | 43616 |
+--------+-------------+
3 rows in set (0.00 sec)
SECOND()
:返回日期的秒数部分。
mysql> select second('98-02-03 10:05:03');
+-----------------------------+
| second('98-02-03 10:05:03') |
+-----------------------------+
| 3 |
+-----------------------------+
1 row in set (0.00 sec)
MINUTE()
:返回日期的秒数部分。
mysql> select minute('98-02-03 10:05:03');
+-----------------------------+
| minute('98-02-03 10:05:03') |
+-----------------------------+
| 5 |
+-----------------------------+
1 row in set (0.00 sec)
HOUR()
:返回日期的小时部分。
mysql> select hour('98-02-03 10:05:03');
+---------------------------+
| hour('98-02-03 10:05:03') |
+---------------------------+
| 10 |
+---------------------------+
1 row in set (0.00 sec)
MONTH()
:返回日期的月份部分。
mysql> select month('1999-01-01');
+---------------------+
| month('1999-01-01') |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
YEAR()
:返回日期的年份部分。
mysql> select year('98-02-03 10:05:03');
+---------------------------+
| year('98-02-03 10:05:03') |
+---------------------------+
| 1998 |
+---------------------------+
1 row in set (0.00 sec)
INTERVAL
:给日期做加减法。用法为 d + INTERVER i DAY
。
mysql> select date('98-02-03 10:05:03') + interval 5 day;
+------------------------------------------+
| date('98-02-03 10:05:03')+interval 5 day |
+------------------------------------------+
| 1998-02-08 |
+------------------------------------------+
1 row in set (0.00 sec)
SUBSTRING()
:返回字符串的子串。具体用法:SUBSTRING(字符串,从哪个字符开始,取几个字符)
。
mysql> select substring('hello world', 2, 3);
+--------------------------------+
| substring('hello world', 2, 3) |
+--------------------------------+
| ell |
+--------------------------------+
1 row in set (0.00 sec)
POSITION()
:查找子串在主串中首次出现的位置。
mysql> select position('ello' in 'hello world');
+-----------------------------------+
| position('ello' in 'hello world') |
+-----------------------------------+
| 2 |
+-----------------------------------+
1 row in set (0.00 sec)
SUM()
:返回指定数值字段的总和。或与 group by 联用,返回一个分组的总和。
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 52117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10002 | 72527 | 1996-08-03 | 1997-08-03 |
| 10002 | 72527 | 1997-08-03 | 1998-08-03 |
| 10002 | 72527 | 1998-08-03 | 1999-08-03 |
| 10003 | 43616 | 1996-12-02 | 1997-12-02 |
| 10003 | 43466 | 1997-12-02 | 1998-12-02 |
+--------+--------+------------+------------+
7 rows in set (0.00 sec)
mysql> select sum(salary) from salaries;
+-------------+
| sum(salary) |
+-------------+
| 418882 |
+-------------+
1 row in set (0.00 sec)
mysql> select emp_no, sum(salary) from salaries group by emp_no;
+--------+-------------+
| emp_no | sum(salary) |
+--------+-------------+
| 10001 | 114219 |
| 10002 | 217581 |
| 10003 | 87082 |
+--------+-------------+
3 rows in set (0.00 sec)
DIV()
:整除,舍去小数部分。
mysql> select 8 DIV 3;
+---------+
| 8 DIV 3 |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
QUARTER()
:传入一个日期作为参数,返回该日期所属的季度。一至三月为第一季度,四至六月为第二季度,以此类推。
- 1 月至 3 月返回 1
- 4 月至 6 月返回 2
- 7 月至 9 月返回 3
- 10 月至 12 月返回 4
mysql> select quarter('98-04-01');
+---------------------+
| quarter('98-04-01') |
+---------------------+
| 2 |
+---------------------+
1 row in set (0.00 sec)
CAST()
:能将参数的数据类型转化为指定的数据类型。
RANK()
:返回记录的排名(不过有点小缺陷,缺陷如下面 demo 所示)。语法:RANK() OVER (ORDER BY f [DESC])
。
mysql> select salary from salaries order by salary desc;
+--------+
| salary |
+--------+
| 72527 |
| 72527 |
| 72527 |
| 62102 |
| 52117 |
| 43616 |
| 43466 |
+--------+
7 rows in set (0.04 sec)
mysql> select rank() over (order by salary desc) from salaries;
+------------------------------------+
| RANK() OVER (order by salary desc) |
+------------------------------------+
| 1 |
| 1 |
| 1 |
| 4 |
| 5 |
| 6 |
| 7 |
+------------------------------------+
7 rows in set (0.16 sec)
MOD()
:取余。
mysql> select mod(27,2);
+-----------+
| mod(27,2) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
CEIL()
:向上取整。也可写作 CEILING()
。
mysql> select ceil(1.7);
+-----------+
| ceil(1.7) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
mysql> select ceiling(1.7);
+--------------+
| ceiling(1.7) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
FLOOR()
:向下取整。
mysql> select floor(1.7);
+------------+
| floor(1.7) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
TRIM()
:移除字符串两侧的空白。
mysql> select trim('Hello world ');
+-----------------------+
| trim('Hello world ') |
+-----------------------+
| Hello world |
+-----------------------+
1 row in set (0.00 sec)
REPLACE()
:替换字符串中的子串。
mysql> select replace('vessel','e','a');
+---------------------------+
| replace('vessel','e','a') |
+---------------------------+
| vassal |
+---------------------------+
1 row in set (0.00 sec)
mysql> select replace('vessel','es','a');
+----------------------------+
| replace('vessel','es','a') |
+----------------------------+
| vasel |
+----------------------------+
1 row in set (0.00 sec)
NULLIF()
:传入两个参数,若这两个参数相同,则返回 NULL,否则返回第一个参数值。
mysql> select nullif('hello', 'hello');
+--------------------------+
| nullif('hello', 'hello') |
+--------------------------+
| NULL |
+--------------------------+
1 row in set (0.00 sec)
mysql> select nullif('hello', 'hell');
+-------------------------+
| nullif('hello', 'hell') |
+-------------------------+
| hello |
+-------------------------+
1 row in set (0.00 sec)
LEFT()
:返回字符串从左侧起指定个数的字符。
mysql> select left('Hello world', 4);
+------------------------+
| left('Hello world', 4) |
+------------------------+
| Hell |
+------------------------+
1 row in set (0.00 sec)
RIGHT()
:返回字符串从右侧起指定个数的字符。
mysql> select right('Hello world', 4);
+-------------------------+
| right('Hello world', 4) |
+-------------------------+
| orld |
+-------------------------+
1 row in set (0.00 sec)
CONCAT()
:连接字符串。
mysql> select concat('hello ', 'world');
+---------------------------+
| concat('hello ', 'world') |
+---------------------------+
| hello world |
+---------------------------+
1 row in set (0.00 sec)
ROUND()
:保留指定位数的小数。
INSTR()
:返回子串在主串中首次出现的位置。
mysql> select instr('Hello World', 'll');
+----------------------------+
| instr('Hello World', 'll') |
+----------------------------+
| 3 |
+----------------------------+
1 row in set (0.00 sec)
COUNT()
:计数。
数学函数
TAN()
:返回指定数值的正切值。
SIN()
:返回指定数值的正弦值。
COS()
:返回指定参数的余弦值。