11.MySQL数值、日期和时间、信息、聚合函数

数值函数

进1法：CEIL 退1法：FLOOR 四舍五入：ROUND

mysql> SELECT CEIL(3.01),FLOOR(3.99),ROUND(3.44,1);
+------------+-------------+---------------+
| CEIL(3.01) | FLOOR(3.99) | ROUND(3.44,1) |
+------------+-------------+---------------+
|          4 |           3 |           3.4 |
+------------+-------------+---------------+
1 row in set (0.00 sec)
1 row in set (0.00 sec)

除法取商：DIV 除法取余：MOD

mysql> SELECT 10 DIV 3,10 MOD 3;
+----------+----------+
| 10 DIV 3 | 10 MOD 3 |
+----------+----------+
|        3 |        1 |
+----------+----------+
1 row in set (0.00 sec)

幂运算： POWER 小数截取： TRUNCATE

mysql> SELECT POWER(10,12),TRUNCATE(128.490,2);
+---------------+---------------------+
| POWER(10,12)  | TRUNCATE(128.490,2) |
+---------------+---------------------+
| 1000000000000 |              128.49 |
+---------------+---------------------+
1 row in set (0.00 sec)

在(不在)区间内：(NOT)BETWEEN…AND…

在(不在)枚举内：(NOT)IN(v1,v2,v3…)

是(否)为空：IS(NOT) NULL

mysql> SELECT 15 BETWEEN 3 AND 22,20 NOT BETWEEN 3 AND 22,502 IN(3,4,502,4),0 IS NULL;
+---------------------+-------------------------+-------------------+-----------+
| 15 BETWEEN 3 AND 22 | 20 NOT BETWEEN 3 AND 22 | 502 IN(3,4,502,4) | 0 IS NULL |
+---------------------+-------------------------+-------------------+-----------+
|                   1 |                       0 |                 1 |         0 |
+---------------------+-------------------------+-------------------+-----------+
1 row in set (0.00 sec)

日期显示：

mysql> SELECT NOW(),CURDATE(),CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2018-05-17 16:29:23 | 2018-05-17 | 16:29:23  |
+---------------------+------------+-----------+
1 row in set (0.03 sec)

日期变换：

mysql> SELECT DATE_ADD('2018-5-17',INTERVAL 365 DAY),
    -> DATEDIFF('2018-5-17','2016-2-17'),
    -> DATE_FORMAT('2018-5-17','%m/%d/%Y');
+----------------------------------------+-----------------------------------+-------------------------------------+
| DATE_ADD('2018-5-17',INTERVAL 365 DAY) | DATEDIFF('2018-5-17','2016-2-17') | DATE_FORMAT('2018-5-17','%m/%d/%Y') |
+----------------------------------------+-----------------------------------+-------------------------------------+
| 2019-05-17                             |                               820 | 05/17/2018                          |
+----------------------------------------+-----------------------------------+-------------------------------------+
1 row in set (0.00 sec)

连接ID,数据库名，用户名，版本号

mysql> SELECT CONNECTION_ID(),DATABASE(),USER(),VERSION();
+-----------------+------------+----------------+-----------+
| CONNECTION_ID() | DATABASE() | USER()         | VERSION() |
+-----------------+------------+----------------+-----------+
|               7 | zoom       | [email protected] | 5.7.22    |
+-----------------+------------+----------------+-----------+
1 row in set (0.00 sec)
1 row in set (0.00 sec)

最后插入的id值：LAST_INSERT_ID()(一次插入多条记录，为最先插入id)

mysql> ALTER TABLE 名单 ADD id  SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC 名单;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| 姓    | varchar(12)          | NO   |     | NULL    |                |
| 名    | varchar(24)          | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT*FROM 名单;
+----+--------+-----+
| id | 姓     | 名  |
+----+--------+-----+
|  1 | 张     | 飞  |
|  2 | 刘     | 备  |
|  3 | 关     | 羽  |
|  4 | 诸葛   | 亮  |
|  5 | 赵%    | 云  |
+----+--------+-----+
5 rows in set (0.00 sec)
mysql> INSERT 名单 VALUES(NULL,'马','超');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT*FROM 名单;
+----+--------+-----+
| id | 姓     | 名  |
+----+--------+-----+
|  1 | 张     | 飞  |
|  2 | 刘     | 备  |
|  3 | 关     | 羽  |
|  4 | 诸葛   | 亮  |
|  5 | 赵%    | 云  |
|  6 | 马     | 超  |
+----+--------+-----+
6 rows in set (0.00 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

聚合函数:平均数：AVG、个数：COUNT、最大值：MAX、最小值：MIN、和：SUM

mysql> SELECT AVG(id),COUNT(id),MAX(id),MIN(id),SUM(id) FROM 名单;
+---------+-----------+---------+---------+---------+
| AVG(id) | COUNT(id) | MAX(id) | MIN(id) | SUM(id) |
+---------+-----------+---------+---------+---------+
|  3.5000 |         6 |       6 |       1 |      21 |
+---------+-----------+---------+---------+---------+
1 row in set (0.00 sec)

MD5加密

mysql> SELECT MD5('toly');
+----------------------------------+
| MD5('toly')                      |
+----------------------------------+
| 5b136b5fb6d5ef084a36facf2aae2dd1 |
+----------------------------------+
1 row in set (0.00 sec)