荐 Mysql常用函数
程序员文章站
2022-09-14 14:06:24
一、单行函数1. 字符函数函数功能CONCAT拼接字符串UPPER变大写LOWER变小写SUBSTR、SUBSTRING截取指定长度字符串INSTR返回子串第一次出现的索引,如果找不到返回0TRIM去掉前后缀LENGTH返回字符串的长度LPAD左填充指定长度RPAD右填充指定长度REVERSE字符串反转REPLACE字符串替换REPEAT将字符重复指定次数后返回CONCAT:拼接字符串...
一、单行函数
1. 字符函数
函数 | 功能 |
---|---|
CONCAT | 拼接字符串 |
UPPER | 变大写 |
LOWER | 变小写 |
SUBSTR、SUBSTRING | 截取指定长度字符串 |
INSTR | 返回子串第一次出现的索引,如果找不到返回0 |
TRIM | 去掉前后缀 |
LENGTH | 返回字符串的长度 |
LPAD | 左填充指定长度 |
RPAD | 右填充指定长度 |
REVERSE | 字符串反转 |
REPLACE | 字符串替换 |
REPEAT | 将字符重复指定次数后返回 |
- CONCAT:拼接字符串
mysql> select concat(username,' love simth') from employee;
+--------------------------------+
| concat(username,' love simth') |
+--------------------------------+
| john love simth |
+--------------------------------+
1 row in set (0.00 sec)
- UPPER:变大写
mysql> select upper(username) from employee;
+-----------------+
| upper(username) |
+-----------------+
| JOHN |
+-----------------+
1 row in set (0.00 sec)
- LOWER:变小写
mysql> select lower(username) from employee;
+-----------------+
| lower(username) |
+-----------------+
| john |
+-----------------+
1 row in set (0.00 sec)
- SUBSTR、SUBSTRING:字符串截取
// 参数1:需要截取的字符串,参数2:开始截取的位置(从1开始),参数3:截取的长度
mysql> select substr(username,1,2) from employee;
+----------------------+
| substr(username,1,2) |
+----------------------+
| jo |
+----------------------+
1 row in set (0.00 sec)
mysql> select substr(username,2) from employee;
+--------------------+
| substr(username,2) |
+--------------------+
| ohn |
+--------------------+
1 row in set (0.00 sec)
- INSTR:返回子串第一次出现的索引,如果找不到返回0
mysql> select instr(username,'h') from employee;
+---------------------+
| instr(username,'h') |
+---------------------+
| 3 |
+---------------------+
1 row in set (0.00 sec)
- TRIM:去掉前后缀
mysql> select trim(' aaaaaaaaaa ');
+---------------------------------------------+
| trim(' aaaaaaaaaa ') |
+---------------------------------------------+
| aaaaaaaaaa |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select trim('a' from 'aaaaaaaaaaaaaaaaaaaI am Jhonaaaaaaaaaaaaaaaaaaaaaaaa');
+-----------------------------------------------------------------------+
| trim('a' from 'aaaaaaaaaaaaaaaaaaaI am Jhonaaaaaaaaaaaaaaaaaaaaaaaa') |
+-----------------------------------------------------------------------+
| I am Jhon |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
- LENGTH:返回字符串的长度
mysql> select length('abcdefgh');
+--------------------+
| length('abcdefgh') |
+--------------------+
| 8 |
+--------------------+
1 row in set (0.00 sec)
- LPAD:左填充指定长度
mysql> select lpad('abcdefgh',10,'*');
+-------------------------+
| lpad('abcdefgh',10,'*') |
+-------------------------+
| **abcdefgh |
+-------------------------+
1 row in set (0.00 sec)
- RPAD:右填充指定长度
mysql> select rpad('abcdefgh',10,'*');
+-------------------------+
| rpad('abcdefgh',10,'*') |
+-------------------------+
| abcdefgh** |
+-------------------------+
1 row in set (0.00 sec)
- REVERSE:字符串反转
mysql> select reverse('abcdefgh');
+---------------------+
| reverse('abcdefgh') |
+---------------------+
| hgfedcba |
+---------------------+
1 row in set (0.00 sec)
- REPLACE:字符串替换
mysql> select replace('***I*** am*** Jhon******','*','');
+--------------------------------------------+
| replace('***I*** am*** Jhon******','*','') |
+--------------------------------------------+
| I am Jhon |
+--------------------------------------------+
1 row in set (0.00 sec)
- REPEAT:将字符重复指定次数后返回
mysql> select repeat('haha',3);
+------------------+
| repeat('haha',3) |
+------------------+
| hahahahahaha |
+------------------+
1 row in set (0.00 sec)
2. 数学函数
函数 | 功能 |
---|---|
ROUND | 四舍五入 |
CEIL | 向上取整 |
FLOOR | 向下取整 |
TRUNCATE | 截断 |
MOD | 取余 |
- ROUND:四舍五入
mysql> select round(4.5);
+------------+
| round(4.5) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
mysql> select round(4.56231,2);
+------------------+
| round(4.56231,2) |
+------------------+
| 4.56 |
+------------------+
1 row in set (0.00 sec)
- CEIL:向上取整
mysql> select ceil(4.2);
+-----------+
| ceil(4.2) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
- FLOOR:向下取整
mysql> select floor(4.9);
+------------+
| floor(4.9) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
- TRUNCATE:截断
mysql> select truncate(4.9879,2);
+--------------------+
| truncate(4.9879,2) |
+--------------------+
| 4.98 |
+--------------------+
1 row in set (0.00 sec)
- MOD:取余
公式:余值=a-a/b*b
mysql> select truncate(4.9879,2);
+--------------------+
| truncate(4.9879,2) |
+--------------------+
| 4.98 |
+--------------------+
1 row in set (0.00 sec)
3. 日期函数
函数 | 功能 |
---|---|
NOW | 返回当前系统日期和时间 |
CURDATE | 返回当前系统日期 |
CURTIME | 返回当前系统时间 |
YEAR | 返回年 |
MONTH | 返回月 |
MONTHNAME | 返回英文月份 |
DAY | 返回日 |
HOUR | 返回小时 |
MINUTE | 返回分 |
SECOND | 发挥秒 |
STR_TO_DATE | 将字符串转化为时间 |
DATE_FORMAT | 将日期转换为字符 |
- NOW:返回当前系统时间和日期
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-07-04 13:58:46 |
+---------------------+
1 row in set (0.00 sec)
- CURDATE & CURTIME:分别返回当前日期和时间
mysql> select curdate(),curtime();
+------------+-----------+
| curdate() | curtime() |
+------------+-----------+
| 2020-07-04 | 14:02:49 |
+------------+-----------+
1 row in set (0.00 sec)
- YEAR & MONTH & DAY:分别返回年月日
mysql> select year(now()),month(now()),monthName(now()),day(now());
+-------------+--------------+------------------+------------+
| year(now()) | month(now()) | monthName(now()) | day(now()) |
+-------------+--------------+------------------+------------+
| 2020 | 7 | July | 4 |
+-------------+--------------+------------------+------------+
1 row in set (0.28 sec)
- HOUR & MINUTE & SECOND:分别返回时分秒
mysql> select hour(now()),minute(now()),second(now());
+-------------+---------------+---------------+
| hour(now()) | minute(now()) | second(now()) |
+-------------+---------------+---------------+
| 14 | 5 | 30 |
+-------------+---------------+---------------+
1 row in set (0.00 sec)
- STR_TO_DATE:将字符串转化为时间
mysql> select str_to_date('04-25-2018','%m-%d-%Y');
+--------------------------------------+
| str_to_date('04-25-2018','%m-%d-%Y') |
+--------------------------------------+
| 2018-04-25 |
+--------------------------------------+
1 row in set (0.00 sec)
- DATE_FORMAT:将日期转化为字符串
mysql> select date_format(now(),'%m-%d-%Y');
+-------------------------------+
| date_format(now(),'%m-%d-%Y') |
+-------------------------------+
| 07-04-2020 |
+-------------------------------+
1 row in set (0.00 sec)
补充:
序号 | 格式 | 功能 |
---|---|---|
1 | %Y | 四位的年份 |
2 | %y | 两位的年份 |
3 | %m | 月份(01,02,03…) |
4 | %d | 日(01,02,03…) |
5 | %H | 小时(24小时制) |
6 | %h | 小时(12小时制) |
7 | %i | 分(00,01,…,59) |
8 | %s | 秒(00,01,…,59) |
9 | %c | 月(1,2…12) |
4. 流程控制函数
- IF:条件函数
mysql> select date_format(now(),'%m-%d-%Y');
+-------------------------------+
| date_format(now(),'%m-%d-%Y') |
+-------------------------------+
| 07-04-2020 |
+-------------------------------+
1 row in set (0.00 sec)
- CASE:条件函数
- 用法一(相当于java的switch…case):
mysql> select id,
-> case id
-> when 1 then 'one'
-> when 2 then 'two'
-> when 3 then 'three'
-> else 'others'
-> end
-> from employee;
+----+-----------------------------------------------------------------------------------+
| id | case id
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
else 'others'
end |
+----+-----------------------------------------------------------------------------------+
| 1 | one |
+----+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2. 用法二(相当于java的if...else if...else...):
mysql> select username,
-> case
-> when salary>20000 then 'a'
-> when salary>10000 then 'b'
-> else 'c'
-> end
-> from employee;
+----------+-------------------------------------------------------------------------+
| username | case
when salary>20000 then 'a'
when salary>10000 then 'b'
else 'c'
end |
+----------+-------------------------------------------------------------------------+
| john | b |
+----------+-------------------------------------------------------------------------+
1 row in set (0.29 sec)
- ifnull:如果为空
mysql> select ifnull(username,'a') from employee;
+----------------------+
| ifnull(username,'a') |
+----------------------+
| john |
+----------------------+
1 row in set (0.00 sec)
5. 其它函数
- version:查询数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20 |
+-----------+
1 row in set (0.00 sec)
- database:查询当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| db |
+------------+
1 row in set (0.00 sec)
- user:当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
- MD5:获取字符串的摘要值
二、聚合函数
- SUM:求和
mysql> select sum(id) from employee;
+---------+
| sum(id) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
- AVG:平均值
mysql> select avg(id) from employee;
+---------+
| avg(id) |
+---------+
| 1.0000 |
+---------+
1 row in set (0.30 sec)
- MIN:最小值
mysql> select min(id) from employee;
+---------+
| min(id) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
- MAX:最大值
mysql> select max(id) from employee;
+---------+
| max(id) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
- COUNT:总数
mysql> select count(id) from employee;
+-----------+
| count(id) |
+-----------+
| 1 |
+-----------+
1 row in set (0.05 sec)
聚合函数小总结
- avg和sum一般用于处理数据值类型
- max,min,count可以处理任何数据类型
- 可以和distinct搭配实现去重
- count函数一般用count(*),效率更高
- 和分组函数一起使用的字段是group by后的字段
本文地址:https://blog.csdn.net/weixin_43935907/article/details/107144522
上一篇: mybatis和hibernate的区别