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 | root@localhost | 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)

转载于:https://www.cnblogs.com/toly-top/p/9782015.html