MySQL函数篇——流程函数

基本流程函数如下：

演示如下：

1.if

select if(true,'ok','no');
select if(false,'ok','no');

2.ifnull

select ifnull('ok','error');
select ifnull(NULL,'error');

3.case when then else end

实例运行：

查询emp表的员工姓名和工作地址（北京/伤害 -------> 一线城市  ， 其它 ----------> 二线城市)

为了方便接下来的测试，在此给出表结构和插入的数据

表结构：

插入数据：

-- 插入数据
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES
('1', 'WN001', 'John', 'M', 28, '123456789012345678', 'Beijing', '2023-03-01'),
('2', 'WN002', 'Alice', 'F', 24, '123456789012345679', 'Shanghai', '2023-03-05'),
('3', 'WN003', 'Bob', 'M', 32, '123456789012345680', 'Guangzhou', '2023-03-08'),
('4', 'WN004', 'Cathy', 'F', 27, '123456789012345681', 'Shenzhen', '2023-03-11'),
('5', 'WN005', 'David', 'M', 30, '123456789012345682', 'Tianjin', '2023-03-15'),
('6', 'WN006', 'Emily', 'F', 26, '123456789012345683', 'Chongqing', '2023-03-18'),
('7', 'WN007', 'Frank', 'M', 33, '123456789012345684', 'Xian', '2023-03-22'),
('8', 'WN008', 'Grace', 'F', 29, '123456789012345685', 'Hangzhou', '2023-03-25'),
('9', 'WN009', 'Henry', 'M', 31, '123456789012345686', 'Nanjing', '2023-03-29'),
('10', 'WN010', 'Ivy', 'F', 25, '123456789012345687', 'Chengdu', '2023-04-02'),
('11', 'WN011', 'Jack', 'M', 29, '123456789012345688', 'Shenyang', '2023-04-06'),
('12', 'WN012', 'Kate', 'F', 27, '123456789012345689', 'Wuhan', '2023-04-09'),
('13', 'WN013', 'Leo', 'M', 33, '123456789012345690', 'Ningbo', '2023-04-13'),
('14', 'WN014', 'Maggie', 'F', 26, '123456789012345691', 'Changsha', '2023-04-16'),
('15', 'WN015', 'Nancy', 'F', 24, '123456789012345692', 'Harbin', '2023-04-20');

 查询代码：

select
name,workaddress,
       case
            when workaddress='Beijing' then '一线城市'
            when workaddress='Shanghai' then '一线城市'
            else '二线城市' end as '城市线级'
       from emp;

运行结果：

在这里我们再次给出case 语句的结构：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

 总结

流程函数是MySQL中比较常用的一种函数，多多练习，每天坚持！