字符函数

函数名作用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()连接IDDATABASE()返回当前数据库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 | +-------------------------------------------+