MySQL---常见函数

最新推荐文章于 2024-01-23 10:57:20 发布

twilight0402

最新推荐文章于 2024-01-23 10:57:20 发布

阅读量312

点赞数

分类专栏： MySQL 文章标签： MySQL 函数

本文链接：https://blog.csdn.net/twilight_karl/article/details/74942227

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

字符函数

函数名	作用
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 |
+-------------------------------------------+

twilight0402

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL---常见函数

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

扫一扫