二、mysql常用函数

目录

一、Mysql数值型函数

二、Mysql字符串函数

 三、Mysql日期和时间函数

四、Mysql聚合函数

五、Mysql流程控制函数

六、其他函数

---

一、Mysql数值型函数

函数名称	作用
abc	求绝对值
sqrt	求二次方根
mod	求余数
ceil 和 ceiling	功能一样，都是返回不小于参数的最小整数，即向上取整
floor	向下取整，返回值转化为一个bigint
rand	生成一个0~1之间的随机数，传入整数参数是用来重复序列
round	对所参数进行四舍五入
sign	返回参数的符号
pow 和 power	功能一样，都是输出所传参数的次方的结果值
sin	求正弦值
asin	求反正弦值，与函数sin互为反函数
cos	求余弦值
acos	求反余弦值，与函数cos互为反函数
tan	求正切值
atan	求反正切值，与函数tan互为反函数
cot	求余切值

1、abs()求绝对值
    (root@localhost) [(none)]> select abs(5),abs(-2.4);
    +--------+-----------+
    | abs(5) | abs(-2.4) |
    +--------+-----------+
    |      5 |       2.4 |
    +--------+-----------+
2、sqrt() 求二次方根
(root@localhost) [(none)]> select sqrt(25),sqrt(120),sqrt(-9);
+----------+--------------------+----------+
| sqrt(25) | sqrt(120)          | sqrt(-9) |
+----------+--------------------+----------+
|        5 | 10.954451150103322 |     NULL |
+----------+--------------------+----------+
3、 mod()求余数
函数mod(x,y)返回x被y除后的余数
(root@localhost) [(none)]> select mod(65,8),mod(120,20);
+-----------+-------------+
| mod(65,8) | mod(120,20) |
+-----------+-------------+
|         1 |           0 |
+-----------+-------------+

二、Mysql字符串函数

函数名称	作用
length	计算字符串长度函数，返回字符串的字节长度
concat	合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
insert	替换字符串函数
lower	将字符串中的字母转换为小写
upper	将字符串中的字母转换为大写
left	从左侧截取字符串，返回字符串左边的若干字符
right	从右侧截取字符串，返回字符串右边的若干字符
trim	删除字符串左右两侧的空格
replace	字符串替换函数，返回替换后的新字符串
substr 和substring	截取字符串，返回从指定位置开始的指定长度的字符串
reverse	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
substring_index()	获取字符串左边或者右边的字符

1、length() :返回字符串的长度。一个汉字是3个字节，一个数字或字母是一个字节
(root@localhost) [(none)]> select length("asdcfa"),length("我爱你123");
+------------------+------------------------+
| length("asdcfa") | length("我爱你123")    |
+------------------+------------------------+
|                6 |                     12 |
+------------------+------------------------+

2、concat(s1,s2,s3...):合并字符串
    若有任何一个连接参数为null，则返回值为null；
    
(root@localhost) [(none)]> select concat("我爱你","123");
+---------------------------+
| concat("我爱你","123")    |
+---------------------------+
| 我爱你123                 |
+---------------------------+

3、substring_index(str, delim, count)
str：处理的字符串delim：分隔符count：计数
如果count是正数，那么就是从左往右数，第N个分隔符左边的全部内容，不包括该分隔符；
相反，如果是负数，那么就是从右往左数，第N个分隔符右边的所有内容，当然也不包括该分隔符。
(root@localhost) [test]> SELECT CONCAT(SUBSTRING_INDEX('四川省成都市高新区吉龙一街188号1栋2单元3楼4号','号',1),'号');
+-----------------------------------------------------------------------------------------------------------+
| CONCAT(SUBSTRING_INDEX('四川省成都市高新区吉龙一街188号1栋2单元3楼4号','号',1),'号')                      |
+-----------------------------------------------------------------------------------------------------------+
| 四川省成都市高新区吉龙一街188号                                                                           |
+-----------------------------------------------------------------------------------------------------------+
(root@localhost) [test]> SELECT CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX('四川省成都市高新区吉龙一街188号1栋2单元3楼4号','高新区',-1),'号',1),'号') asd;
+--------------------+
| asd                |
+--------------------+
| 吉龙一街188号      |
+--------------------+
1 row in set (0.00 sec)

4、left():
    语法：LEFT(string, number_of_chars)
    string: 需要提取字符的原始字符串。
    number_of_chars: 需要从字符串左侧提取的字符数。
    如果你有一个包含税号的字段，并且你想提取名字的前4个字符，你可以这样使用 LEFT() 函数：
    select LEFT (nsrsbh, 4) = '9151' as "四川省"
5、right() :从右侧截取，意义同上

 三、Mysql日期和时间函数

函数名称	作用
curdate 和 current_date	作用相同，返回当前系统的日期值，不包含时间部分
curtime 和 current_time	作用相同，返回当前系统的时间值
now 和 sysdate	作用相同，返回当前系统的日期值和时间值
unix_timestamp	获取unix时间戳函数，返回一个以unix时间戳为基础的无符号整数
from_unixtime	将unix时间戳转换为时间格式，与unix_timestamp互为反函数
day	获取指定日期中的日期
dayname	获取指定日期中对应的星期几的英文名称
month	获取指定日期中的月份
monthname	获取指定日期中的月份英文名称
year	获取指定日期中的年份，范围1970 ~ 2069
dayofweek	获取指定日期是一周中的第几天，返回值范围是1~7，1=周日
dayofmonth	获取指定日期是一月中的第几天，返回值范围是1~31
dayofyear	获取指定日期是一年中的第几天，返回值范围是1~366
week	获取指定日期是一年中的第几周，返回值的范围是0 ~ 52或1 ~ 53
time_to_sec	将时间参数转换为秒数
sec_to_time	将秒数转换为时间，与time_to_sec互为反函数
date_add 和 adddate	用于给日期或时间值添加指定的时间间隔
date_sub 和 subdate	用于从日期或时间值中减去指定的时间间隔
addtime	时间加法运算，在原始时间上添加指定的时间
subtime	时间减法运算，在原始时间上减去指定的时间
datediff	获取两个日期之间间隔，返回参数1减去参数2的值
date_format	格式化指定日期，根据参数返回指定格式的值
weekday	获取指定日期在一周内的对应的工作索引
LAST_DAY(DATE)	返回指定日期所在月份的最后一天

1、curdate 和 current_date：返回当前系统的日期值
(root@localhost) [(none)]> select curdate(),current_date();
+------------+----------------+
| curdate()  | current_date() |
+------------+----------------+
| 2023-10-10 | 2023-10-10     |
+------------+----------------+

2、curtime 和 current_time：返回当前系统的时间值
(root@localhost) [(none)]> select curtime(),current_time();
+-----------+----------------+
| curtime() | current_time() |
+-----------+----------------+
| 09:53:05  | 09:53:05       |
+-----------+----------------+

3、now 和 sysdate：获取当前系统时间日期
(root@localhost) [(none)]> select now(),sysdate();
+---------------------+---------------------+
| now()               | sysdate()           |
+---------------------+---------------------+
| 2023-10-10 09:53:35 | 2023-10-10 09:53:35 |
+---------------------+---------------------+

4、month：获取指定日期的月份
(root@localhost) [(none)]> select month(now());
+--------------+
| month(now()) |
+--------------+
|           10 |
+--------------+

5、date_format：格式化指定的日期
(root@localhost) [(none)]> select date_format('2023-10-10','%Y%m%d'),date_format(now(),'%Y-%m-%d');
+------------------------------------+-------------------------------+
| date_format('2023-10-10','%Y%m%d') | date_format(now(),'%Y-%m-%d') |
+------------------------------------+-------------------------------+
| 20231010                           | 2023-10-10                    |
+------------------------------------+-------------------------------+

6、date_add()函数：
    语法: `date_add(date, INTERVAL expr unit)` 
    参数:  
        + `date`: 要添加时间间隔的日期或时间值。  
        + `expr`: 要添加的值。  
        + `unit`: 时间间隔的单位（例如，年、月、日、小时等）。
    示例：如果你想将当前日期加上 5 天，你可以使用以下查询：
    SELECT date_add(NOW(), INTERVAL 5 DAY);

7、date_sub()函数：
    * 语法: `date_sub(date, INTERVAL expr unit)`  
    * 参数:  
        + `date`: 要减去时间间隔的日期或时间值。  
        + `expr`: 要减去的值。  
        + `unit`: 时间间隔的单位（例如，年、月、日、小时等）。
   示例：如果你想从当前日期减去 2 小时，你可以使用以下查询：
   SELECT date_sub(NOW(), INTERVAL 2 HOUR);
   其中 unit 参数常用的有：
    * 年（YEAR）  
    * 月（MONTH）  
    * 日（DAY）  
    * 小时（HOUR）  
    * 分钟（MINUTE）  
    * 秒（SECOND）  
    * 周（WEEK）

8、获取今天的时间
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';

9、获取昨天的时间
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';

10、获取本周的时间
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';

11、获取上周时间
SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';

12、获取本月时间
SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';

13、获取上个月的时间
AND kprq >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')  
AND kprq <= DATE_FORMAT(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59')

四、Mysql聚合函数

函数名称	作用
max	查询指定列的最大值
min	查询指定列的最小值
count	统计查询结果的行数
sum	求和，返回指定列的总和
avg	求平均值，返回指定列数据的平均值

五、Mysql流程控制函数

函数名称	作用
if	判断，流程控制
ifnull	判断是否为空
case	搜索语句

1、if：判断
if(expr,v1,v2)函数，当expr为真时，返回v1，否则返回v2。
(root@localhost) [(none)]> select if(1>2,'对','错');
+---------------------+
| if(1>2,'对','错')   |
+---------------------+
| 错                  |
+---------------------+

2、ifnull：判断是否为空
ifnull(v1,v2)：v1为空返回v2，否则返回v1。
(root@localhost) [(none)]> select ifnull(null,'gaga'),ifnull('123','gaga');
+---------------------+----------------------+
| ifnull(null,'gaga') | ifnull('123','gaga') |
+---------------------+----------------------+
| gaga                | 123                  |
+---------------------+----------------------+

3、case：搜索语句，类似于java中的if…else if…else
类似java中的if…else if…else。有两种写法
（1）方式 1
    case <表达式>
        when <值1> then <操作>
        when <值2> then <操作>
        ...
        else <操作>
    end case;

（2）方式 2
    case
        when <条件1> then <命令>
        when <条件2> then <命令>
        ...
        else 命令
    end case;

示例：
create table t_stu(
     id int not null primary key auto_increment comment '编号',
     name varchar(10) not null default '' comment '姓名',
     sex tinyint not null comment '性别，0：未知,1：男,2：女'
) comment '学生表';
insert into t_stu(name,sex) values('张学友',1),('刘德华',1),('郭富城',1),('蔡依林',2),('xxx',0);
需求：查询所有学生信息，输出：姓名，性别（男、女、未知）
(root@localhost) [test]> select t.name 姓名,
    -> (case t.sex
    ->     when 1
    ->         then '男'
    ->     when 2
    ->         then '女'
    ->     else '未知' end
    -> )性别
    ->   from t_stu t;
(root@localhost) [test]> select t.name 姓名, (case when t.sex=1 then '男' when t.sex=2 then '女' else '未知' end) 性别 from t_stu t;
+-----------+--------+
| 姓名      | 性别   |
+-----------+--------+
| 张学友    | 男     |
| 刘德华    | 男     |
| 郭富城    | 男     |
| 蔡依林    | 女     |
| xxx       | 未知   |
+-----------+--------+

六、其他函数

函数名称	作用
version	数据库版本号
database	当前的数据库
user	当前连接用户
password	返回字符串密码
md5	返回字符串的md5数据

(root@localhost) [test]> select version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+