18-基础SQL-函数-流程函数

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

MySQL的函数主要包括：字符串函数、数值函数、日期函数、流程函数。

流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

IF(value,t,f) 演示：当条件为true时，返回"OK"，否则返回"ERROR"

SELECT IF(1=1,"OK","ERROR")

IFNULL(value1,value2) 演示：当第一个值为NULL时，返回"Default"

SELECT IFNULL(NULL,"Default")

案例1：创建一个 emp 员工表，添加一些员工数据

CREATE TABLE emp(
    id int comment "编号",
    workno varchar(10) comment "工号",
    name varchar(10) comment "姓名",
    gender char(1) comment "性别",
    age tinyint unsigned comment "年龄",
    idcard char(18) comment "身份证号",
    workaddress varchar(50) comment "工作地址",
    entrydate date comment "入职时间"
) comment "员工表";

INSERT INTO emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
VALUES
(1,"1","员工1","男","23","123456789000000000","江西","2022-8-31"),
(2,"2","员工2","男","26","123456789000000001","河北","2022-12-31"),
(3,"3","员工3","女","23","123456789000000002","河北","2022-5-31"),
(4,"4","员工4","女","24","123456789000000003","山西","2022-3-31"),
(5,"5","员工5","男","23","123456789000000000","江西","2022-8-21"),
(6,"6","员工6","男","26","123456789000000001","北京","2022-12-21"),
(7,"7","员工7","女","23","123456789000000002","北京","2022-5-21"),
(8,"8","员工8","女","24","123456789000000003","山西","2022-3-21"),
(9,"9","员工9","男","23","123456789000000000","江西","2022-8-11"),
(10,"10","员工10","男","26","123456789000000001","河北","2022-12-11"),
(11,"11","员工11","女","23","123456789000000002","河北","2022-5-11"),
(12,"12","员工12","女","24","123456789000000003","上海","2022-3-11")

1）查询 emp表 的员工姓名和工作地址（北京/上海 ==>"一线城市"，其它==> "二线城市"）

SELECT name,
IF(workaddress IN ("北京","上海"),"一线城市","二线城市") 
FROM emp;

# 或

SELECT name,
CASE workaddress WHEN "北京" THEN "一线城市" WHEN "上海" THEN "一线城市" ELSE "二线城市" END
FROM emp;

案例2：创建一个 score 学生成绩表，添加一些学生数据

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,"学生1",60,70,75),
(2,"学生2",70,90,85),
(3,"学生3",80,80,65),
(4,"学生4",85,90,95),
(5,"学生5",40,60,85)

1）统计班级各个学员的成绩，成绩 >= 85 展示"优秀"，成绩 >= 60 展示"及格"，否则展示不及格