mysql常用函数

字符函数

length 获取字符串长度

select length('huyongjian') length;
+--------+
| length |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

concat 连接字符串

select concat(id,'_',name) as new_name from student;
+--------------+
| new_name     |
+--------------+
| 1_张三       |
| 2_王五       |
| 3_李四       |
| 4_小红       |
| 5_小花       |
| 6_huyongjian |
| 7_Join       |
+--------------+
7 rows in set (0.00 sec)

upper 转换成大写

select upper('huyongjian') upper;
+------------+
| upper      |
+------------+
| HUYONGJIAN |
+------------+
1 row in set (0.00 sec)

lower 转换成小写

select lower('HuYongJian') upper;
+------------+
| upper      |
+------------+
| huyongjian |
+------------+
1 row in set (0.00 sec)

substr 截取字符串

select substr('小红是一个小学生',6,3) as substr;
+-----------+
| substr    |
+-----------+
| 小学生    |
+-----------+
1 row in set (0.00 sec)

instr 查找子字符串位置

select instr('小红今年18岁','18') instr;
+-------+
| instr |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

trim 去除字符串前后空格

select trim(' huyongjian ') as out_put;
+------------+
| out_put    |
+------------+
| huyongjian |
+------------+
1 row in set (0.00 sec)

lpad 用指定字符左填充

select lpad('胡勇健',5,'*') as out_put;
+-------------+
| out_put     |
+-------------+
| **胡勇健    |
+-------------+
1 row in set (0.00 sec)

rpad 用指定字符右填充

select rpad('胡勇健',5,'*') as out_put;
+-------------+
| out_put     |
+-------------+
| 胡勇健**    |
+-------------+
1 row in set (0.00 sec)

replace 用指定字符串替换查找的字符串

select replace('小红是男生','男生','女生') as out_put;
+-----------------+
| out_put         |
+-----------------+
| 小红是女生      |
+-----------------+
1 row in set (0.00 sec)

md5 字符串md5加密

select md5('huyongjian') as ciphertext;
+----------------------------------+
| ciphertext                       |
+----------------------------------+
| 4d8b497cedde8a5bcc039637b6da1710 |
+----------------------------------+
1 row in set (0.00 sec)

sha 字符串sha加密

select sha('huyongjian') password;
+------------------------------------------+
| password                                 |
+------------------------------------------+
| eb08a98ebc630c80683c5d259bc22598cb62008e |
+------------------------------------------+
1 row in set (0.00 sec)

数学函数

round 四舍五入取整或保留指定小数位

select round(1.45,1) as out_put;
+---------+
| out_put |
+---------+
|     1.5 |
+---------+
1 row in set (0.00 sec)

ceil 向上取整

select ceil(3.446) as out_put;
+---------+
| out_put |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

floor 向下取整

select floor(3.5) as out_put;
+---------+
| out_put |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

truncate 截断指定小数位

select truncate(5.35,1) as out_put;
+---------+
| out_put |
+---------+
|     5.3 |
+---------+
1 row in set (0.00 sec)

mod 取余

select mod(4,3) as out_put;
+---------+
| out_put |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

日期函数

now 获取当前时间

select now() as date_time;
+---------------------+
| date_time           |
+---------------------+
| 2021-07-08 09:39:45 |
+---------------------+
1 row in set (0.00 sec)

curdate 获取当前日期

select curdate() as date;
+------------+
| date       |
+------------+
| 2021-07-08 |
+------------+
1 row in set (0.00 sec)

curtime 获取当前时间

select curtime() as time;
+----------+
| time     |
+----------+
| 09:40:53 |
+----------+
1 row in set (0.01 sec)

year 获取年份

select year(now()) 年;
+------+
| 年   |
+------+
| 2021 |
+------+
1 row in set (0.01 sec)

month 获取月份

select month(now()) 月;
+------+
| 月   |
+------+
|    7 |
+------+
1 row in set (0.00 sec)

str_to_date 时间字符串转成日期时间

select str_to_date('2021-07-07','%Y-%m-%d') as date;
+------------+
| date       |
+------------+
| 2021-07-07 |
+------------+
1 row in set (0.01 sec)

统计函数

avg 获取平均数

select avg(age) as 平均年龄 from student;
+--------------+
| 平均年龄     |
+--------------+
|      21.1429 |
+--------------+
1 row in set (0.01 sec)

count 获取总数

select count(id) as 总条数 from student;
+-----------+
| 总条数    |
+-----------+
|         7 |
+-----------+
1 row in set (0.01 sec)

max 获取最大值

select max(age) from student;
+----------+
| max(age) |
+----------+
|       30 |
+----------+
1 row in set (0.01 sec)

min 获取最小值

select min(age) as 最小年龄 from student;
+--------------+
| 最小年龄     |
+--------------+
|           18 |
+--------------+
1 row in set (0.00 sec)

sum 获取数据相加总和

SELECT sum(age) from student;
+----------+
| sum(age) |
+----------+
|      148 |
+----------+
1 row in set (0.00 sec)

格式化函数

format 格式化数据

select format(34.343,2) format;
+--------+
| format |
+--------+
| 34.34  |
+--------+
1 row in set (0.00 sec)

date_format 格式化时间

select date_format(now(),'%Y-%m-%d') as date;
+------------+
| date       |
+------------+
| 2021-07-08 |
+------------+
1 row in set (0.00 sec)

inet_aton ip转成数值

select inet_aton('192.168.1.100') as ip_num;
+------------+
| ip_num     |
+------------+
| 3232235876 |
+------------+
1 row in set (0.00 sec)

inet_ntoa ip数值转成IP

select inet_ntoa(3232235876) as ip;
+---------------+
| ip            |
+---------------+
| 192.168.1.100 |
+---------------+
1 row in set (0.00 sec)

系统函数

version 获取系统版本

select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

database 获取当前数据库名

select database();
+-------------+
| database()  |
+-------------+
| school_info |
+-------------+
1 row in set (0.00 sec)

user 获取当前用户

select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
上一篇:MySQL主从复制与读写分离


下一篇:MySQL5.7在线开启/关闭GTID