Mysql基础篇-函数

函数是指一段可以直接被另一段程序调用的程序或代码

字符串函数

函数(字符大写)	功能
concat(s1,s2,…sn)	字符串拼接，将s1,s2…拼接成一个字符串
lower(str)	字符串str转化为小写
upper(str)	字符串str转化为大写
lpad(str,n,pad)	左填充，用pad字符串对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用pad字符串对str的右边进行填充，达到n个字符串长度
trim(str)	去点字符串头部和尾部的空格
substring	截取字符串str从start位置起的len个长度的字符串

-- DataGrip 练习：
-- 拼接
select concat('hello','mysql');
-- 小写大写
select lower('Hello');  -- hello
select upper('hello');  -- HELLO
-- 填充
select lpad('01',5,'-'); -- 输出为 '---01'
select rpad('01',5,'-'); -- 输出为 '01---'
-- 去掉空格
select trim('  h e l l o  '); -- 输出为'h e l l o'
-- 截取
select substr('hello mysql',7,5); -- 输出为 'mysql'
-- 案例： 由于业务需求，员工的工号统一为五位数，目前不足五位数的全在前面补零
update employee set workno = lpad(workno,5,'0');

数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand	返回0~1之间的随机数
round(x,y)	求参数x四舍五入的值，保留y为小数

-- 向上，向下取整
select ceil(1.5);  -- 输出 2
select floor(1.5); -- 输出 1
-- 求模
select mod(5,4);   -- 输出 1
-- 随机数
select rand();   -- 输出 0.6011...
-- 四舍五入
select round(12.34567,4); -- 输出 12.3457

-- 案例： 通过数据库的函数，生成一个六位数的随机验证码
select LPAD(round(rand()*1000000,0),6,'0');  -- 输出 752549

日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval_expr type)	返回一个日期/时间加上一个时间间隔后的时间值
datediff(date1,date2)	返回起始时间date1和结束时间date2之间的天数

-- 现在是 2023-12-15 15:04:49
-- 返回当前__
select curdate();  -- 输出 2023-12-15
select curtime();  -- 输出 15:04:32
select now(); -- 输出 2023-12-15 15:04:49

-- 获取指定date的年份月份天
select year(now());  -- 输出 2023
select month((now())); -- 输出 12
select day(now()); -- 输出 15

-- 返回时间值相加
select date_add(now(),interval 70 day ); -- 输出 2024-02-23 15:07:42
select date_add(now(),interval 70 month ); -- 输出 2029-10-15 15:08:41
select date_add(now(),interval 70 year );-- 输出 2093-12-15 15:08:15

-- 返回时间间隔 datediff
select datediff('2029-10-15 15:08:41','2093-12-15 15:08:15'); -- 输出 -23437 所以是第一个时间减去第二个时间

-- 案例：查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(),entrydate) as 'entrydays' from employee order by entrydays desc ;  -- 有点绕，好好思考一下

流程函数

函数	功能
if(value,t,f)	如果value为true,返回t,否则返回f
ifnull(value1,value2)	如果value1不为空，返回value1,否则返回value2
case when[val1] then [res1]… else[default] end	如果val1为true，返回res1，否则返回default默认值
case [expr] when [val1] then [res1]… else [default] end	如果expr的值等于val1,返回res1,否则返回default默认值

-- 判断正确
select if(true,'ok','error');  -- 输出 ok

-- 判断为空
select ifnull('ok','default'); -- 输出 ok
select ifnull('','default'); -- 输出 ’‘
select ifnull(null,'default'); -- 输出 default

-- case 字段名 when 条件 then 结果 (可写多个when then.)else 其他 end (结束)
-- 需求1： 查询employee表的员工姓名和工作地址
select  -- 更类似于C语言中 SWITCH CASE 语法
    name ,
    (case workno
        when '北京' then '一线城市'
        when 'shanghai' then '二线城市'
        else '三线城市'
        end )  as '工作地址'
from employee ;  -- 输出 全是三线哈哈哈哈
    
-- 需求2： 查询employee表年龄age大于1的人
select   -- 更类似与 C语言中if else 语法
    name ,
    (case 
        when age >= 1 then '三岁儿童' 
        else '未出生婴儿' 
        end)
from employee;  
-- -----------------------------------------------------------------------------------
-- 案例：统计班级各个学院的成绩(大于等于八十为优秀，大于等于六十为及格，其他为不及格)
-- 案例准备
create table score (
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
)comment '学院成绩表';  -- 创建表结构

insert into score (id, name, math, english, chinese) VALUES (1,'Tom',67,88,95),(2,'Rose',23,66,90),(3,'Jack',56,98,76); -- 插入成绩

select
    id,
    name,
    (case when math >= 80 then '优秀'
          when math >= 60 then '及格'
          else '不及格' end ) as '数学成绩',
    (case when english >= 80 then '优秀'
          when english >= 60 then '及格'
          else '不及格' end ) as '英语成绩',
    (case when chinese >= 80 then '优秀'
          when chinese >= 60 then '及格'
          else '不及格' end ) as '语文成绩'
    from score  ;  -- 输出一个表格反正

案例-具体数据

id	name	math	english	chinese
1	Tom	67	88	95
2	Rose	23	66	90
3	Jack	56	98	76

案例-结果

id	name	math	english	chinese
1	Tom	及格	优秀	优秀
2	Rose	不及格	及格	优秀
3	Jack	不及格	优秀	及格

这一块函数的一些运用都是比较简单的，多回顾回顾之前的知识，忘得差不多了已经~~