MySQL---常见函数
版权声明：本文为博主原创文章，转载请注明出处。 https://blog.csdn.net/twilight_karl/article/details/74942227
字符函数
| 函数名 | 作用 |
|---|---|
| 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 |
+-------------------------------------------+