常用的 SQL 内部函数

AVG()
[sql]
mysql> SELECT * FROM tbl_1;
+----+------+------+
| id | a | b |
+----+------+------+
| 2 | 1 | a |
| 4 | 2 | a |
| 6 | 3 | b |
| 8 | 4 | c |
+----+------+------+
4 rows in set (0.00 sec)

mysql> SELECT AVG(a) FROM tbl_1;
+--------+
| AVG(a) |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)
[/sql]


COUNT()
[sql]
mysql> SELECT COUNT(*) FROM tbl_1 WHERE b = 'a';
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
[/sql]


MAX() 返回数据集里最大值
[sql]
mysql> SELECT MAX(a) FROM tbl_1;
+--------+
| MAX(a) |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
[/sql]


MIN() 返回数据集里最小值
[sql]
mysql> SELECT MIN(a) FROM tbl_1;
+--------+
| MIN(a) |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
[/sql]


SUM() 求和
[sql]
mysql> SELECT SUM(a) FROM tbl_1;
+--------+
| SUM(a) |
+--------+
| 10 |
+--------+
1 row in set (0.00 sec)
[/sql]


ABS() OR ABSVAL() 计算绝对值
[sql]
mysql> SELECT ABS(a) FROM tbl_1;
+--------+
| ABS(a) |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
+--------+
4 rows in set (0.00 sec)
[/sql]

CEILING()
[sql]
mysql> SELECT CEILING(1.1), CEILING(1.5), CEILING(-1.1), CEILING(-1.5);
+--------------+--------------+---------------+---------------+
| CEILING(1.1) | CEILING(1.5) | CEILING(-1.1) | CEILING(-1.5) |
+--------------+--------------+---------------+---------------+
| 2 | 2 | -1 | -1 |
+--------------+--------------+---------------+---------------+
1 row in set (0.00 sec)
[/sql]


ROUND() 四舍五入
[sql]
mysql> SELECT ROUND(111.111, 1), ROUND(111.111, 2), ROUND(111.111, 3), ROUND(111.111, 4), ROUND(111.111, 5), ROUND(111.111, 0), ROUND(111.111, -1), ROUND(111.111, -2), ROUND(111.111, -3)\G
*************************** 1. row ***************************
ROUND(111.111, 1): 111.1
ROUND(111.111, 2): 111.11
ROUND(111.111, 3): 111.111
ROUND(111.111, 4): 111.1110
ROUND(111.111, 5): 111.11100
ROUND(111.111, 0): 111
ROUND(111.111, -1): 110
ROUND(111.111, -2): 100
ROUND(111.111, -3): 0
1 row in set (0.01 sec)

mysql> SELECT ROUND(111.116, 1), ROUND(111.116, 2), ROUND(111.116, 3), ROUND(111.116, 4), ROUND(111.116, 5), ROUND(111.116, 6), ROUND(111.116, 0), ROUND(111.116, -1), ROUND(111.116, -2), ROUND(11.116, -3)\G
*************************** 1. row ***************************
ROUND(111.116, 1): 111.1
ROUND(111.116, 2): 111.12
ROUND(111.116, 3): 111.116
ROUND(111.116, 4): 111.1160
ROUND(111.116, 5): 111.11600
ROUND(111.116, 6): 111.116000
ROUND(111.116, 0): 111
ROUND(111.116, -1): 110
ROUND(111.116, -2): 100
ROUND(11.116, -3): 0
1 row in set (0.00 sec)
[/sql]


CURTIME() 返回系统时间
[sql]
mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 13:40:30 |
+-----------+
1 row in set (0.00 sec)
[/sql]


CURDATE() 返回系统日期
[sql]
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2009-08-03 |
+------------+
1 row in set (0.00 sec)
[/sql]


DATE()
[sql]
mysql> SELECT DATE('2009-08-03');
+--------------------+
| DATE('2009-08-03') |
+--------------------+
| 2009-08-03 |
+--------------------+
1 row in set (0.00 sec)
[/sql]


DAY() 返回日期的日部分
[sql]
mysql> SELECT * FROM tbl_2;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2009-08-03 00:00:00 | 2009-07-03 00:00:00 |
| 2009-08-03 00:00:00 | 2009-07-01 00:00:00 |
| 2009-08-08 00:00:00 | 2009-07-01 00:00:00 |
| 2009-08-09 00:00:00 | 2009-07-10 00:00:00 |
+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT DAY(a), DAY(b) FROM tbl_2;
+--------+--------+
| DAY(a) | DAY(b) |
+--------+--------+
| 3 | 3 |
| 3 | 1 |
| 8 | 1 |
| 9 | 10 |
+--------+--------+
4 rows in set (0.00 sec)
[/sql]


DAYOFMONTH() 返回参数日部分
[sql]
mysql> SELECT DAYOFMONTH(a) FROM tbl_2;
+---------------+
| DAYOFMONTH(a) |
+---------------+
| 3 |
| 3 |
| 8 |
| 9 |
+---------------+
4 rows in set (0.00 sec)
[/sql]


DAYOFWEEK() 返回参数的星期值1~7，1-星期日；7-星期六
[sql]
mysql> SELECT DAYOFWEEK(a) FROM tbl_2;
+--------------+
| DAYOFWEEK(a) |
+--------------+
| 2 |
| 2 |
| 7 |
| 1 |
+--------------+
4 rows in set (0.00 sec)
[/sql]


DAYOFYEAR() 返回值1~366
[sql]
mysql> SELECT DAYOFYEAR(a), DAYOFYEAR(b) FROM tbl_2;
+--------------+--------------+
| DAYOFYEAR(a) | DAYOFYEAR(b) |
+--------------+--------------+
| 215 | 184 |
| 215 | 182 |
| 220 | 182 |
| 221 | 191 |
+--------------+--------------+
4 rows in set (0.00 sec)
[/sql]

HOUR() 返回参数小时部分，参数为时间或时间戳类型
[sql]

mysql> SELECT * FROM tbl_2;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2009-08-03 00:00:00 | 2009-07-03 00:00:00 |
| 2009-08-03 00:00:00 | 2009-07-01 00:00:00 |
| 2009-08-08 00:00:00 | 2009-07-01 00:00:00 |
| 2009-08-09 00:00:00 | 2009-07-10 00:00:00 |
| 2009-08-03 01:11:11 | 2009-08-03 02:21:12 |
+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT HOUR(a), HOUR(b) FROM tbl_2;
+---------+---------+
| HOUR(a) | HOUR(b) |
+---------+---------+
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 1 | 2 |
+---------+---------+
5 rows in set (0.00 sec)
[/sql]