【MySQL】内置函数

秋刀鱼的滋味@

于 2024-09-25 21:17:49 发布

阅读量1.2k

点赞数 29

分类专栏： MySQL 文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/jupangMZ/article/details/142532642

版权

MySQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

🌈前言🌈

欢迎收看本期【MySQL】,本期内容将介绍MySQL中的内置函数，其中包含了日期函数，字符串函数，数学函数以及其他的一些函数，从功能到使用等方面介绍这些函数。

📁 日期函数

● 获得年月日

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-09-25     |
+----------------+

● 获得时分秒

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 20:47:18       |
+----------------+

● 获得时间戳

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-09-25 20:48:04 |
+---------------------+

● 获得日期的基础上加日期

mysql> select date_add('2024-10-1',interval 10 day);
+---------------------------------------+
| date_add('2024-10-1',interval 10 day) |
+---------------------------------------+
| 2024-10-11                            |
+---------------------------------------+

//interval 后面的单位可以是 year,month,day,hour,minute,second

● 在日期的基础上减去时间：

mysql> select date_sub('2024-10-1' , interval 1 hour);
+-----------------------------------------+
| date_sub('2024-10-1' , interval 1 hour) |
+-----------------------------------------+
| 2024-09-30 23:00:00                     |
+-----------------------------------------+

● 计算两个日期之差：

mysql> select datediff('2024-10-1','1949-10-1');
+-----------------------------------+
| datediff('2024-10-1','1949-10-1') |
+-----------------------------------+
|                             27394 |
+-----------------------------------+

📁 字符串函数

● 获得表中某列的字符集

mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
| utf8mb3        |
+----------------+

● 拼接字符串

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  9 | 刘备      |   18 |
| 10 | 关羽      |   18 |
| 11 | 张飞      |   18 |
| 12 | 曹操      |   19 |
| 13 | 孙策      |   20 |
| 14 | 孙权      |   20 |
| 15 | 孙尚香    |   20 |
+----+-----------+------+
7 rows in set (0.00 sec)

mysql> select concat(name ,'的年龄是' ,age) as 年龄 from student;
+-------------------------+
| 年龄                    |
+-------------------------+
| 刘备的年龄是18          |
| 关羽的年龄是18          |
| 张飞的年龄是18          |
| 曹操的年龄是19          |
| 孙策的年龄是20          |
| 孙权的年龄是20          |
| 孙尚香的年龄是20        |
+-------------------------+

● 求某列所用的字节数

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  9 | 刘备      |   18 |
| 10 | 关羽      |   18 |
| 11 | 张飞      |   18 |
| 12 | 曹操      |   19 |
| 13 | 孙策      |   20 |
| 14 | 孙权      |   20 |
| 15 | 孙尚香    |   20 |
+----+-----------+------+
7 rows in set (0.00 sec)

mysql> select length(name) from student;
+--------------+
| length(name) |
+--------------+
|            6 |
|            6 |
|            6 |
|            6 |
|            6 |
|            6 |
|            9 |
+--------------+

● 替换字符串


mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  9 | 刘备      |   18 |
| 10 | 关羽      |   18 |
| 11 | 张飞      |   18 |
| 12 | 曹操      |   19 |
| 13 | 孙策      |   20 |
| 14 | 孙权      |   20 |
| 15 | 孙尚香    |   20 |
+----+-----------+------+

mysql> select replace(name,'孙','S') from student;
+-------------------------+
| replace(name,'孙','S')  |
+-------------------------+
| 刘备                    |
| 关羽                    |
| 张飞                    |
| 曹操                    |
| S策                     |
| S权                     |
| S尚香                   |
+-------------------------+

● 截取字符串的子串

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  9 | 刘备      |   18 |
| 10 | 关羽      |   18 |
| 11 | 张飞      |   18 |
| 12 | 曹操      |   19 |
| 13 | 孙策      |   20 |
| 14 | 孙权      |   20 |
| 15 | 孙尚香    |   20 |
+----+-----------+------+
7 rows in set (0.00 sec)

mysql> select substr(name,1,1) from student;
+------------------+
| substr(name,1,1) |
+------------------+
| 刘               |
| 关               |
| 张               |
| 曹               |
| 孙               |
| 孙               |
| 孙               |
+------------------+

● 大小写转换

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | smith     |   20 |
|  9 | 刘备      |   18 |
| 10 | 关羽      |   18 |
| 11 | 张飞      |   18 |
| 12 | 曹操      |   19 |
| 13 | 孙策      |   20 |
| 14 | 孙权      |   20 |
| 15 | 孙尚香    |   20 |
+----+-----------+------+
8 rows in set (0.00 sec)

mysql> select ucase(name) from student;
+-------------+
| ucase(name) |
+-------------+
| SMITH       |
| 刘备        |
| 关羽        |
| 张飞        |
| 曹操        |
| 孙策        |
| 孙权        |
| 孙尚香      |
+-------------+

📁 数学函数

● 绝对值

mysql> select abs(-100.1);
+-------------+
| abs(-100.1) |
+-------------+
|       100.1 |
+-------------+

● 向上取整

mysql> select ceiling(4.5);
+--------------+
| ceiling(4.5) |
+--------------+
|            5 |
+--------------+

● 向下取整

mysql> select floor(4.5);
+------------+
| floor(4.5) |
+------------+
|          4 |
+------------+

● 进制转换

mysql> select conv(10,10,16);
+----------------+
| conv(10,10,16) |
+----------------+
| A              |
+----------------+

● 格式化

mysql> select format(100.12345,2);
+---------------------+
| format(100.12345,2) |
+---------------------+
| 100.12              |
+---------------------+

● 产生随机数

mysql> select rand() * 1000;
+-------------------+
| rand() * 1000     |
+-------------------+
| 654.6384791421132 |
+-------------------+

📁 其他函数

● user()：查询当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

● md5(str)：对一个字符串进行md5摘要，摘要后得到一个32位字符串

mysql> select md5('ABCD123');
+----------------------------------+
| md5('ABCD123')                   |
+----------------------------------+
| c1a14d6139aee67b520c766321bd894d |
+----------------------------------+

● database()：显示当前正在使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| test_db    |
+------------+

● ifnull(val1,val2)：如果val1为null，返回val2，否则返回val1

mysql> select ifnull(null,4);
+----------------+
| ifnull(null,4) |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> select ifnull(5,4);
+-------------+
| ifnull(5,4) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)