MySQL---常见函数

字符函数

函数名 作用 CONCAT() 字符连接 CANCAT_WS(“分隔符”,”value”,”value”,..) 用指定分隔符进行字符连接 FORMAT() 数字格式化 LOWER() 转换成小写 UPPER() 转换成大写 LEFT() 从左侧开始截取 RIGHT() 从右侧开始截取

// 使用CONCAT拼接字符串mysql> SELECT CONCAT("A","B","C");+---------------------+| CONCAT("A","B","C") |+---------------------+| ABC                 |+---------------------+// CONCAT_WS用指定分隔符拼接字符串mysql> SELECT CONCAT_WS("-","A","B",'C');+----------------------------+| CONCAT_WS("-","A","B",'C') |+----------------------------+| A-B-C                      |+----------------------------+mysql> SELECT CONCAT_WS("-",id,username) FROM user ;+----------------------------+| CONCAT_WS("-",id,username) |+----------------------------+| 1-TOM                      || 2-JERRY                    || 3-ALICE                    |+----------------------------+// 格式化数字函数mysql> SELECT FORMAT(100.111,2);+-------------------+| FORMAT(100.111,2) |+-------------------+| 100.11            |+-------------------+// 小写mysql> SELECT LOWER("MYSQL");+----------------+| LOWER("MYSQL") |+----------------+| mysql          |+----------------+// 大写mysql> SELECT UPPER("mysql");+----------------+| UPPER("mysql") |+----------------+| MYSQL          |+----------------+// 从左侧截取mysql> SELECT LEFT("MYSQL",2);+-----------------+| LEFT("MYSQL",2) |+-----------------+| MY              |+-----------------+// 从右侧截取mysql> SELECT RIGHT("MYSQL",2);+------------------+| RIGHT("MYSQL",2) |+------------------+| QL               |+------------------+

函数名 作用 LENGTH() 获取字符串长度 LTRIM() 删除签到空格 RTRIM() 删除后续空格 TRIM() 删除和替换前导和后续空格(完整格式：TRIM([{BOTH / LEADING /TRAILING} [remstr] FROM] str)) SUBSTRING() 字符串截取 [NOT] LIKE 字符串匹配(%任意多个字符，_单个字符) REPLACE() 字符串替换

// 获得字符串长度mysql> SELECT LENGTH ("MY SQL");+-------------------+| LENGTH ("MY SQL") |+-------------------+|                 6 |+-------------------+// 清除左边的空格mysql> SELECT LTRIM("    MYSQL   ");+-----------------------+| LTRIM("    MYSQL   ") |+-----------------------+| MYSQL                 |+-----------------------+// 计算清除过后的长度mysql> SELECT LENGTH(LTRIM("   MY SQL "));+-----------------------------+| LENGTH(LTRIM("   MY SQL ")) |+-----------------------------+|                           7 |+-----------------------------+// 清除右侧的空格mysql> SELECT LENGTH(RTRIM("   MY SQL "));+-----------------------------+| LENGTH(RTRIM("   MY SQL ")) |+-----------------------------+|                           9 |+-----------------------------+// 清除左侧和右侧的空格mysql> SELECT LENGTH(TRIM("   MY SQL "));+----------------------------+| LENGTH(TRIM("   MY SQL ")) |+----------------------------+|                          6 |+----------------------------+// trim还可以用来替换指定字符mysql> SELECT TRIM(BOTH "?" FROM "?MY?SQL?");+--------------------------------+| TRIM(BOTH "?" FROM "?MY?SQL?") |+--------------------------------+| MY?SQL                         |+--------------------------------+// 求字串，序号从1开始mysql> SELECT SUBSTRING("MYSQL",1,2);+------------------------+| SUBSTRING("MYSQL",1,2) |+------------------------+| MY                     |+------------------------+mysql> SELECT SUBSTRING("MYSQL",-3);+-----------------------+| SUBSTRING("MYSQL",-3) |+-----------------------+| SQL                   |+-----------------------+// 模糊查找，% 表示任意多个字符mysql> SELECT * FROM user WHERE username LIKE "%O%"；+----+----------+------+| id | username | pid  |+----+----------+------+|  1 | TOM      |    1 |+----+----------+------+// \表示转义字符mysql> SELECT * FROM user WHERE username LIKE "%\%%";+----+-----------+------+| id | username  | pid  |+----+-----------+------+|  4 | TOM%JERRY |    1 |+----+-----------+------+// 使用escape自定义转移字符mysql> SELECT * FROM user WHERE username LIKE "%:%%" ESCAPE ":";+----+-----------+------+| id | username  | pid  |+----+-----------+------+|  4 | TOM%JERRY |    1 |+----+-----------+------+

数值运算符函数

函数名 作用 DIV 整数除法 MOD 取余数 CEIL() 进一取整 FLOOR() 舍一取证 POWER(a,b) 幂运算(a的b次方) ROUND(num[,length]) 四舍五入，指定位数的四舍五入 TRUNCATE() 数字截取（不进行四舍五入） [NOT]BETWEEN…AND… [不]在范围内 [NOT]IN() [不]在列出值范围内 IS [NOT] NULL 判断是否为空

// 进一mysql> SELECT CEIL(3.01);+------------+| CEIL(3.01) |+------------+|          4 |+------------+// 舍一mysql> SELECT FLOOR(3.99);+-------------+| FLOOR(3.99) |+-------------+|           3 |+-------------+// 指定位数的四舍五入mysql> SELECT ROUND(10.1234,3);+------------------+| ROUND(10.1234,3) |+------------------+|           10.123 |+------------------+// 数字的截取mysql> SELECT TRUNCATE(1.23456,2);+---------------------+| TRUNCATE(1.23456,2) |+---------------------+|                1.23 |+---------------------+// between .. and...mysql> SELECT 10 BETWEEN 1 AND 100 ;+----------------------+| 10 BETWEEN 1 AND 100 |+----------------------+|                    1 |+----------------------+mysql> SELECT 10 IN(10,20,30);+-----------------+| 10 IN(10,20,30) |+-----------------+|               1 |+-----------------+1 row in set (0.04 sec)

日期和时间函数

函数名 作用 NOW() 当前日期和时间(2017-07-10 11:16:20) CURDATE() 当前日期(2017-07-10) SURTIME() 当前时间(11:16:38 ) DATE_ADD(date,INTERVAL expr type) 日期变化(YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND) DATEDIFF() 日期差值 DATE_FORMAT(date,format) 日期格式化（%Y=年 %m=月 %d=日 %H=时 %i=分 %s=秒）

// 当前时间mysql> SELECT NOW();+---------------------+| NOW()               |+---------------------+| 2017-07-10 11:16:20 |+---------------------+// 当前日期mysql> SELECT CURDATE();+------------+| CURDATE()  |+------------+| 2017-07-10 |+------------+// 当前时间mysql> SELECT CURTIME();+-----------+| CURTIME() |+-----------+| 11:16:38  |+-----------+// 更改时间mysql> SELECT DATE_ADD("2017-07-10",INTERVAL 1 YEAR);+----------------------------------------+| DATE_ADD("2017-07-10",INTERVAL 1 YEAR) |+----------------------------------------+| 2018-07-10                             |+----------------------------------------+// 计算时间差mysql> SELECT DATEDIFF("2017-7-10","2017-6-10");+-----------------------------------+| DATEDIFF("2017-7-10","2017-6-10") |+-----------------------------------+|                                30 |+-----------------------------------+// 更改时间格式mysql> SELECT DATE_FORMAT(NOW(),"%Y年 %m月%d日%H时%i分%s秒");+------------------------------------------------+| DATE_FORMAT(NOW(),"%Y年 %m月%d日%H时%i分%s秒") |+------------------------------------------------+| 2017年 07月10日11时28分16秒                    |+------------------------------------------------+

信息函数

函数名 作用 CONNECTION_ID() 连接ID DATABASE() 返回当前数据库 LAST_INSERT_ID() 最后插入记录的数据表的ID字段 USER() 当前用户 VERSION() 当前版本信息

mysql> SELECT CONNECTION_ID();+-----------------+| CONNECTION_ID() |+-----------------+|               5 |+-----------------+mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| test       |+------------+mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                5 |+------------------+mysql> SELECT USER();+----------------+| USER()         |+----------------+| root@localhost |+----------------+mysql> SELECT VERSION();+------------+| VERSION()  |+------------+| 5.7.18-log |+------------+

聚合函数

函数名 作用 AVG() 平均值 COUNT() 计数 MAX() 求最大值 MIN() 最小值 SUM() 求总和

mysql> SELECT MAX(goods_price) AS price FROM tdb_goods;+-----------+| price     |+-----------+| 28888.000 |+-----------+1 row in set (0.00 sec)mysql> SELECT MIN(goods_price) AS price FROM tdb_goods;+--------+| price  |+--------+| 99.000 |+--------+1 row in set (0.00 sec)mysql> SELECT AVG(goods_price) AS price FROM tdb_goods;+--------------+| price        |+--------------+| 5845.1000000 |+--------------+1 row in set (0.00 sec)mysql> SELECT SUM(goods_price) AS price FROM tdb_goods;+------------+| price      |+------------+| 116902.000 |+------------+1 row in set (0.00 sec)mysql> SELECT COUNT(goods_id) AS COUNT FROM tdb_goods;+-------+| COUNT |+-------+|    20 |+-------+1 row in set (0.00 sec)

加密函数

函数名 作用 MD5() 信息摘要算法 PASSWORD() 密码算法

mysql> SELECT MD5("MYSQL ");+----------------------------------+| MD5("MYSQL ")                    |+----------------------------------+| 31d72f67ea8d0f7ee426456e83327af2 |+----------------------------------+mysql> SELECT PASSWORD("MYSQL ");+-------------------------------------------+| PASSWORD("MYSQL ")                        |+-------------------------------------------+| *50E5F8E9E5335C3578BF217737DB2DCDEDCB8B98 |+-------------------------------------------+