MySQL中的函数（大数据学习）

Shadow️

已于 2023-05-25 09:19:32 修改

阅读量138

点赞数

分类专栏：数据库学习文章标签： mysql 学习数据库

于 2023-04-14 20:23:35 首次发布

本文链接：https://blog.csdn.net/qq_51216447/article/details/130160229

版权

数据库学习专栏收录该内容

7 篇文章 2 订阅

订阅专栏

1、IFNULL（，）；

两个参数，第一个参数需要判断的字段，第二个是默认值，当前⾯的值是null的时候，使⽤后⾯的默认值。

select ifnull(null,100); --> 100
select ifnull(10,20);  --> 10
select ifnull(comm,0) from emp; --> ifnull 在此时会执行很多次，就会有很多的返回值。

2、IF （，，）；

if 函数有三个参数，第一个参数 boolean(布尔类型true false) , 第二个参数和第三个参数都是值，前⾯的条件如果成⽴，取值第⼀个，否则取值第⼆个。

select if(10>20,1,0);
mysql> select if(sal > ifnull(comm,0) ,sal,comm) from emp;

3、case （难点）

语法：case when ... then ... else ... end
分析：

case 开头
end 结尾
中间是类似于 if else

注：一行case when ... then ... else ... end 只能处理一个字段的数据。

1）行转列

# 使用if 来编写这个需求：
select sname,
  if(subject='语文',score,0) 语文,
  if(subject='数学',score,0) 数学,
  if(subject='英语',score,0) 英语,
  if(subject='历史',score,0) 历史,
  if(subject='政治',score,0) 政治,
  if(subject='体育',score,0) 体育
from sc;
+--------+------+------+------+------+------+------+
| sname  | 语文 | 数学 | 英语 | 历史 | 政治 | 体育 |
+--------+------+------+------+------+------+------+
| 张小三 |   78 |    0 |    0 |    0 |    0 |    0 |
| 张小三 |    0 |   77 |    0 |    0 |    0 |    0 |
| 张小三 |    0 |    0 |   90 |    0 |    0 |    0 |
| 张小三 |    0 |    0 |    0 |   89 |    0 |    0 |
| 张小三 |    0 |    0 |    0 |    0 |    0 |   80 |
| 李小四 |    0 |   90 |    0 |    0 |    0 |    0 |
| 李小四 |    0 |    0 |   80 |    0 |    0 |    0 |
| 李小四 |    0 |    0 |    0 |    0 |    0 |   88 |
| 李小四 |    0 |    0 |    0 |    0 |   88 |    0 |
| 李小四 |    0 |    0 |    0 |   78 |    0 |    0 |
| 王小五 |   90 |    0 |    0 |    0 |    0 |    0 |
| 王小五 |    0 |    0 |   80 |    0 |    0 |    0 |
| 王小五 |    0 |    0 |    0 |    0 |   89 |    0 |
| 王小五 |    0 |    0 |    0 |    0 |    0 |   90 |
+--------+------+------+------+------+------+------+

select sname,
  sum(if(subject='语文',score,0)) 语文,
  sum(if(subject='数学',score,0)) 数学,
  sum(if(subject='英语',score,0)) 英语,
  sum(if(subject='历史',score,0)) 历史,
  sum(if(subject='政治',score,0)) 政治,
  sum(if(subject='体育',score,0)) 体育
from sc group by sname;

select sname,
  sum(case  when subject='语文' then score else 0 end) '语文',
  sum(case  when subject='数学' then score else 0 end) '数学',
  sum(case  when subject='英语' then score else 0 end) '英语',
  sum(case  when subject='历史' then score else 0 end) '历史',
  sum(case  when subject='政治' then score else 0 end) '政治',
  sum(case  when subject='体育' then score else 0 end) '体育'
from sc group by sname;

+--------+------+------+------+------+------+------+
| sname  | 语文 | 数学 | 英语 | 历史 | 政治 | 体育 |
+--------+------+------+------+------+------+------+
| 张小三 | 78   | 77   | 90   | 89   | 0    | 80   |
| 李小四 | 0    | 90   | 80   | 78   | 88   | 88   |
| 王小五 | 90   | 0    | 80   | 0    | 89   | 90   |
+--------+------+------+------+------+------+------+

# 此时的需求是添加一列，这一列就是英文即可，一列 == 1个case
# 以下写法有点类似于if:
select *,
  case 
    when subject ='语文' then 'chinese'
    when subject ='数学' then 'Math'
    when subject ='英语' then 'english'
    when subject ='历史' then 'history'
    when subject ='政治' then 'politics'
    when subject ='体育' then 'sport'
  end 英文学科名称
from sc;
# case的另一种写法： 类似于switch的写法
select *,
 case subject
    when '语文' then 'chinese'
    when '数学' then 'Math'
    when '英语' then 'english'
    when '历史' then 'history'
    when '政治' then 'politics'
    when '体育' then 'sport'
  end 英文学科名称
from sc;

4、exists(难点)

顾名思义，就是判断数据是否存在的！exists的作用为判断一个表中的数据，是否在另外的一张表中能够查询到与之对应的数据

语法：
select xxx from 表 where [not] exists (集合)
注：使用exists 编写sql，效率要比连接查询和子查询高！

# 案例1: 查询有员工的部门
select * from dept where deptno in (select distinct deptno from emp);

select * from dept where exists(
   select * from emp where emp.deptno = dept.deptno
);

# 查询一个数据是否存在，存在里面的结果集中，如果存在，显示出来，不存在不显示。里面查询的是什么 # 结果集不重要，主要存在即可。

# 案例2: 查询没有员工的部门
select * from dept where not exists(
   select * from emp where emp.deptno = dept.deptno
);

# 案例3: 查询有下属的员工信息
select * from emp where exists(
    select * from emp e2 where e2.mgr = emp.empno
  );
# 若一个表中存在两级以上的关联，则关联自身查询 

# exists : 都可以使用我们以前的sql语句替换，但是使用exists 执行效率高。

5、字符串函数

# 字符串是万能的！！！不管在任何学科中！！！

# 1、获取字符串⻓度
select char_length('Hello');
# 2、使字符串中的小写字母变⼤写
select upper('Hello');
select UCASE('Hello');
# 3、使字符串中的大写字母变小写
select lower('Hello');
select lcase('Hello');
# 4、去除空⽩字符串
select trim(' Hello ');-- 左右两边的空⽩字符全部切掉
select ltrim(' Hello ');-- 只切除左边的空⽩字符
select rtrim(' Hello ');-- 只切右边
# 5、⽐较两个字符串是否相等，⽐较的肯定是内容
select strcmp('hello','hello');-- 如果相等返回0，不等于返回 1 或者 -1
select strcmp('hello2','hello');-- 1
# 6、截取⼀段字符串
select substr('hello',2,3);-- 字串第一个字母下标位置记为1，2代表的是第⼆个字符的位置，3代表的是截取的⻓度
# 7、将字符串进⾏反转
select reverse('hello');
# 8、将字符串中的某个字母替换为所给字母
select replace('hello','l','a');
# 9、字符串的拼接，有两种，可拼接多个字符串
# 1）使⽤某个拼接符进⾏拼接
select concat_ws(':','hello','world');
# 2) 不指定拼接符
select concat('hello','world');
# 10、数据进⾏格式化处理 最后⼀位进⾏四舍五⼊的处理
select format(3.1415926,2);

6、数学函数

# 绝对值
select abs(-1);
# 向上取整
select ceil(1.99);
#向下取整
select FLOOR(1.99);
# 除以
select 10 div 5;
select 10/5;
# 求最⼩值和最⼤值
select least(10,20,4,50,18);
select greatest(10,20,4,50,18) as 最⼤值; 
# 求余数
select 5%2;
select MOD(5,2);
# 求次⽅
select POW(2,3);
# 求PI
select PI();
# 获取0到1之间的随机数，不包含1
select rand();
# eg：随机获取 [3,10)
select floor (rand() * 7 + 3 );
# 求小数点后几位，四舍五⼊
select round(1.56); -- 2
select round(1.22); -- 1
# 保留⼩数点后⼏位，不会四舍五⼊
select TRUNCATE(1.35675,3);

7、日期函数（使用频率不是很高）

# 获取当前时间
now() ; 

# 查询当前时间按年⽉⽇的形式
CURRENT_DATE();
#            按时分秒的形式
CURRENT_TIME();
#            按年⽉⽇时分秒
CURRENT_TIMESTAMP();

# 某个⽇期多少天以后（这个值可以是负数）
ADDDATE('2022-07-21',INTERVAL -10 DAY); 
# 某个时间多少⼩时分钟秒之后
ADDTIME('2022-07-21 09:57:00','2:00:00');

# 获取两个时间的差值
abs(DATEDIFF('2022-07-11','2022-07-21'));
# 将数据格式化为其他的样式 %r 可以展示上午还是下午
DATE_FORMAT('2022-07-11','%y年%m⽉%d⽇');

# 获取所给时间的这个月的天数
day('2022-07-11');
# 获取所给时间的年份
YEAR('2022-07-11');
# 获取给定的⽉份
MONTH('2022-07-11');

# 该⽇期是这个⽉的第⼏天
DAYOFMONTH('2022-07-11');
# 该⽇期是这个⽉的第⼏周
DAYOFWEEK('2022-07-11'); -- 2
# 该⽇期是这一年的第⼏天
DAYOFYEAR('2022-07-11'); -- 192

# 获取当前⽇期⼀个⽉之后的⽇期，并且告知是星期⼏
DAYNAME(ADDDATE(CURRENT_DATE(),INTERVAL 1 MONTH)); -- Sunday
# 获取某个⽉的最后⼀天的⽇期
LAST_DAY(CURRENT_DATE());

# 专⻔⽤于减天数的函数
SUBDATE(now(),1);

8、其他函数

# 通过cast进⾏数据类型转换
select CAST('2022-07-21' AS DATE);

# 返回第⼀个⾮空字符串
select coalesce(null,null,'hello','world');

# 查看当前所在的数据库
select DATABASE();
select CURRENT_USER();

# 获取最后⼀个主键的id值 插入错误也算一次
select LAST_INSERT_ID();