MySQL数据库管理与常用函数(三)——MySQL常用函数

最新推荐文章于 2023-05-29 11:51:02 发布

老蛙@

最新推荐文章于 2023-05-29 11:51:02 发布

阅读量146

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/m0_48468380/article/details/108678158

版权

笔记专栏收录该内容

84 篇文章 3 订阅

订阅专栏

MySQL常用函数

数学函数(SELECT + ?)

CEIL()：进一取整
FLOOR()：舍弃掉小数部分
ROUND()：四舍五入
TRUNCATE()：截取小数点后几位
MOD()：取余数
ABS()：取绝对值
POWER()：幂运算
PI()：圆周率
RAND()/RAND(X)：0~1之间的随机数，若已指定一个整数参数x，则它被用作种子值，用来产生重复序列。
SIGN(X)：得到数字符号，x的值为负数时返回-1，x的值为零时返回0，x的值为正数时返回1.
EXP()：计算e的X次方

mysql> -- 进一取整
mysql> SELECT CEIL(1.2);
+-----------+
| CEIL(1.2) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

mysql> -- 舍弃小数部分
mysql> SELECT FLOOR(1.123);
+--------------+
| FLOOR(1.123) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> -- 四舍五入
mysql> SELECT ROUND(5.5);
+------------+
| ROUND(5.5) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

mysql> -- 截取小数点后几位
mysql> SELECT TRUNCATE(1.123,2);
+-------------------+
| TRUNCATE(1.123,2) |
+-------------------+
|              1.12 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(1.123,1);
+-------------------+
| TRUNCATE(1.123,1) |
+-------------------+
|               1.1 |
+-------------------+
1 row in set (0.00 sec)

mysql> -- 取余数
mysql> SELECT MOD(12,10);
+------------+
| MOD(12,10) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

mysql> -- 取绝对值
mysql> SELECT ABS(-12);
+----------+
| ABS(-12) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

mysql> -- 幂运算
mysql> SELECT POWER(2,2);
+------------+
| POWER(2,2) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> -- 圆周率
mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

mysql> -- 随机数
mysql> SELECT RAND();
+---------------------+
| RAND()              |
+---------------------+
| 0.19453336130318544 |
+---------------------+
1 row in set (0.00 sec)

mysql> -- 得到数字符号
mysql> SELECT SIGN(-1);
+----------+
| SIGN(-1) |
+----------+
|       -1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SIGN(1);
+---------+
| SIGN(1) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> -- 计算e的X次方
mysql> SELECT EXP(2);
+------------------+
| EXP(2)           |
+------------------+
| 7.38905609893065 |
+------------------+
1 row in set (0.00 sec)

字符串函数

CHAR_LENGTH(s)：得到字符串的字符数
LENGTH(s)：得到字符串的长度
CONCAT(S1,S2,…)：将字符串拼接成一个字符串，若字符串中含NULL则结果为NULL
CONCAT_WS(x,s1,s2,s3…)：以指定分隔符x拼接字符串
UPPER(s)/UCACE(s)：将字符串转化为大写
LOWER(s)/LCASE(s)：将字符串转化为小写
REVERSE(s)：字符串反转
LEFT(x)/RIGHT(x)：返回字符串的前几个或者后几个字符
LPAD(s1,x,s2)/RPAD(s1,x,s2)：用字符串填充到指定长度
TRIM()/LTRIM()/RTRIM()：去掉字符串两端的空格
REPEAT(s,x)：重复字符串指定次数
SUBSTRING(s,x1,x2)：截取部分字符串
STRCMP(s1,s2)：比较字符串，前者大则返回1，后者大则返回-1

mysql> -- 得到字符串的字符数
mysql> SELECT CHAR_LENGTH("你好啊");
+-----------------------+
| CHAR_LENGTH("你好啊") |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.01 sec)

mysql> -- 得到字符串的长度
mysql> SELECT LENGTH("你好啊");
+------------------+
| LENGTH("你好啊") |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

mysql> -- 将字符串拼接
mysql> SELECT CONCAT("你","好");
+-------------------+
| CONCAT("你","好") |
+-------------------+
| 你好              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('!',"你","好");
+--------------------------+
| CONCAT_WS('!',"你","好") |
+--------------------------+
| 你!好                    |
+--------------------------+
1 row in set (0.00 sec)

mysql> -- 转化字符串的大小写
mysql> SELECT UPPER("abc");
+--------------+
| UPPER("abc") |
+--------------+
| ABC          |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT LOWER("ABC");
+--------------+
| LOWER("ABC") |
+--------------+
| abc          |
+--------------+
1 row in set (0.00 sec)

mysql> -- 字符串反转
mysql> SELECT REVERSE("ABC");
+----------------+
| REVERSE("ABC") |
+----------------+
| CBA            |
+----------------+
1 row in set (0.00 sec)

mysql> -- 获取前几个或后几个字符
mysql> SELECT LEFT("ABC",2);
+---------------+
| LEFT("ABC",2) |
+---------------+
| AB            |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT("ABC",2);
+----------------+
| RIGHT("ABC",2) |
+----------------+
| BC             |
+----------------+
1 row in set (0.00 sec)

mysql> -- 用字符串填充到指定长度
mysql> SELECT LPAD("HELLO",10,"!");
+----------------------+
| LPAD("HELLO",10,"!") |
+----------------------+
| !!!!!HELLO           |
+----------------------+
1 row in set (0.00 sec)

mysql> -- 去掉字符串两端的空格
mysql> SELECT TRIM("HELLO");
+---------------+
| TRIM("HELLO") |
+---------------+
| HELLO         |
+---------------+
1 row in set (0.00 sec)

mysql> -- 重复字符串指定次数
mysql> SELECT REPEAT("A",3);
+---------------+
| REPEAT("A",3) |
+---------------+
| AAA           |
+---------------+
1 row in set (0.00 sec)

mysql> -- 截取部分字符串
mysql> SELECT SUBSTRING("ABCDEFG",2,6);
+--------------------------+
| SUBSTRING("ABCDEFG",2,6) |
+--------------------------+
| BCDEFG                   |
+--------------------------+
1 row in set (0.00 sec)

mysql> -- 比较字符串
mysql> SELECT STRCMP("ABC","ACD");
+---------------------+
| STRCMP("ABC","ACD") |
+---------------------+
|                  -1 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT STRCMP("ACC","ABD");
+---------------------+
| STRCMP("ACC","ABD") |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

日期时间函数

CURDATE()/CURRENT_DATE()：获取当前日期
CURTIME()/CURRENT_TIME()：获取当前时间
NOW()/CURRENT_TIMESTAMP()/SYSDATE()：获取当前日期时间
MONTH()：获取指定日期的月份
DAYNAME()：获取指定日期是星期几
DAYOFWEEK()/WEEK()/YEAR()：返回一周内的第几天
DATEDIFF()：计算两个日期相差的天数

mysql> -- 获取当前日期
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2020-09-19     |
+----------------+
1 row in set (0.00 sec)

mysql> -- 获取当前时间
mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 14:54:28       |
+----------------+
1 row in set (0.00 sec)

mysql> -- 获取当前日期时间
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-09-19 14:54:49 |
+---------------------+
1 row in set (0.00 sec)

mysql> -- 获取指定日期的月份
mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            9 |
+--------------+
1 row in set (0.00 sec)

mysql> -- 获取指定日期是星期几
mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Saturday       |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 返回一周的第几天
mysql> SELECT DAYOFWEEK(NOW());
+------------------+
| DAYOFWEEK(NOW()) |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

mysql> -- 计算日期相差天数
mysql> SELECT DATEDIFF(NOW(),"2001-01-01");
+------------------------------+
| DATEDIFF(NOW(),"2001-01-01") |
+------------------------------+
|                         7201 |
+------------------------------+
1 row in set (0.00 sec)

其他常用函数

VERSION()/CONNECTION_ID()：获取版本号
USER()/CURRENT_USER()/SYSTEM_USER()/SESSION_USER()：获取用户名
LAST_INSERT_ID()：得到上一步插入操作产生的AUTO_IINCREMENT值
MD5()：字符串加密

mysql> -- 获取版本号
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

mysql> -- 获取用户名
mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> -- 获取MD5格式
mysql>
mysql> SELECT MD5("213");
+----------------------------------+
| MD5("213")                       |
+----------------------------------+
| 979d472a84804b9f647bc185a877a8b5 |
+----------------------------------+
1 row in set (0.00 sec)

老蛙@

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL数据库管理与常用函数(三)——MySQL常用函数

MySQL常用函数数学函数(SELECT + ?)CEIL()：进一取整FLOOR()：舍弃掉小数部分ROUND()：四舍五入TRUNCATE()：截取小数点后几位MOD()：取余数ABS()：取绝对值POWER()：幂运算PI()：圆周率RAND()/RAND(X)：0~1之间的随机数，若已指定一个整数参数x，则它被用作种子值，用来产生重复序列。SIGN(X)：得到数字符号，x的值为负数时返回-1，x的值为零时返回0，x的值为正数时返回1.EXP()：计算e的X次方mysql
复制链接

扫一扫