技术教程 荐 Mysql常用函数 正文

荐 Mysql常用函数

2022-08-08,,

一、单行函数

1. 字符函数

函数 功能
CONCAT 拼接字符串
UPPER 变大写
LOWER 变小写
SUBSTR、SUBSTRING 截取指定长度字符串
INSTR 返回子串第一次出现的索引，如果找不到返回0
TRIM 去掉前后缀
LENGTH 返回字符串的长度
LPAD 左填充指定长度
RPAD 右填充指定长度
REVERSE 字符串反转
REPLACE 字符串替换
REPEAT 将字符重复指定次数后返回
  1. CONCAT：拼接字符串
mysql> select concat(username,' love simth') from employee;
+--------------------------------+
| concat(username,' love simth') |
+--------------------------------+
| john love simth                |
+--------------------------------+
1 row in set (0.00 sec)
  1. UPPER：变大写
mysql> select upper(username) from employee;
+-----------------+
| upper(username) |
+-----------------+
| JOHN            |
+-----------------+
1 row in set (0.00 sec)
  1. LOWER：变小写
mysql> select lower(username) from employee;
+-----------------+
| lower(username) |
+-----------------+
| john            |
+-----------------+
1 row in set (0.00 sec)
  1. 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)
  1. INSTR：返回子串第一次出现的索引，如果找不到返回0
mysql> select instr(username,'h') from employee;
+---------------------+
| instr(username,'h') |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)
  1. 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)
  1. LENGTH：返回字符串的长度
mysql> select length('abcdefgh');
+--------------------+
| length('abcdefgh') |
+--------------------+
|                  8 |
+--------------------+
1 row in set (0.00 sec)
  1. LPAD：左填充指定长度
mysql> select lpad('abcdefgh',10,'*');
+-------------------------+
| lpad('abcdefgh',10,'*') |
+-------------------------+
| **abcdefgh              |
+-------------------------+
1 row in set (0.00 sec)
  1. RPAD：右填充指定长度
mysql> select rpad('abcdefgh',10,'*');
+-------------------------+
| rpad('abcdefgh',10,'*') |
+-------------------------+
| abcdefgh**              |
+-------------------------+
1 row in set (0.00 sec)
  1. REVERSE：字符串反转
mysql> select reverse('abcdefgh');
+---------------------+
| reverse('abcdefgh') |
+---------------------+
| hgfedcba            |
+---------------------+
1 row in set (0.00 sec)
  1. REPLACE：字符串替换
mysql> select replace('***I*** am*** Jhon******','*','');
+--------------------------------------------+
| replace('***I*** am*** Jhon******','*','') |
+--------------------------------------------+
| I am Jhon                                  |
+--------------------------------------------+
1 row in set (0.00 sec)
  1. REPEAT：将字符重复指定次数后返回
mysql> select repeat('haha',3);
+------------------+
| repeat('haha',3) |
+------------------+
| hahahahahaha     |
+------------------+
1 row in set (0.00 sec)

2. 数学函数

函数 功能
ROUND 四舍五入
CEIL 向上取整
FLOOR 向下取整
TRUNCATE 截断
MOD 取余
  1. 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)
  1. CEIL：向上取整
mysql> select ceil(4.2);
+-----------+
| ceil(4.2) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)
  1. FLOOR：向下取整
mysql> select floor(4.9);
+------------+
| floor(4.9) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)
  1. TRUNCATE：截断
mysql> select truncate(4.9879,2);
+--------------------+
| truncate(4.9879,2) |
+--------------------+
|               4.98 |
+--------------------+
1 row in set (0.00 sec)
  1. 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 将日期转换为字符
  1. NOW：返回当前系统时间和日期
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-07-04 13:58:46 |
+---------------------+
1 row in set (0.00 sec)
  1. CURDATE & CURTIME：分别返回当前日期和时间
mysql> select curdate(),curtime();
+------------+-----------+
| curdate()  | curtime() |
+------------+-----------+
| 2020-07-04 | 14:02:49  |
+------------+-----------+
1 row in set (0.00 sec)
  1. 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)
  1. 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)
  1. 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)
  1. 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. 流程控制函数

  1. 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)
  1. CASE：条件函数
    1. 用法一（相当于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)
  1. ifnull：如果为空
mysql> select ifnull(username,'a')  from employee;
+----------------------+
| ifnull(username,'a') |
+----------------------+
| john                 |
+----------------------+
1 row in set (0.00 sec)

5. 其它函数

  1. version：查询数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)
  1. database：查询当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| db         |
+------------+
1 row in set (0.00 sec)
  1. user：当前用户
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
  1. MD5：获取字符串的摘要值

二、聚合函数

  1. SUM：求和
mysql> select sum(id) from employee;
+---------+
| sum(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
  1. AVG：平均值
mysql> select avg(id) from employee;
+---------+
| avg(id) |
+---------+
|  1.0000 |
+---------+
1 row in set (0.30 sec)
  1. MIN：最小值
mysql> select min(id) from employee;
+---------+
| min(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
  1. MAX：最大值
mysql> select max(id) from employee;
+---------+
| max(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
  1. COUNT：总数
mysql> select count(id) from employee;
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.05 sec)

聚合函数小总结

  1. avg和sum一般用于处理数据值类型
  2. max,min,count可以处理任何数据类型
  3. 可以和distinct搭配实现去重
  4. count函数一般用count(*)，效率更高
  5. 和分组函数一起使用的字段是group by后的字段

本文地址：https://blog.csdn.net/weixin_43935907/article/details/107144522

《荐 Mysql常用函数.doc》

下载本文的Word格式文档，以方便收藏与打印。

Copyright 2023. 昆居客 - IT编程,互联网信息技术文档大全!