摘要：本文介绍数据库中的函数与约束，函数含字符串、数值、日期、流程四类，可实现字符串处理、数值计算等需求。约束分六类，重点讲外键约束的语法、删除更新行为，保证数据正确完整。

思维导图

1. 函数

函数是指一段可以直接被另一段程序调用的程序或代码。这意味着，在 MySQL 中已经为我们提供了这一段程序或代码，我们只需在合适的业务场景调用对应的函数，即可完成对应的业务需求。

MySQL 中的函数主要分为四类：字符串函数、数值函数、日期函数、流程函数                                   

1.1 字符串函数

MySQL 中内置了诸多字符串函数，常用的几个如下：

函数	功能
CONCAT(S1,S2,...Sn)	字符串拼接，将 S1、S2、...、Sn 拼接成一个字符串
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)	返回从字符串 str 的 start 位置起，长度为 len 的子字符串

使用方法：select  函数

---

演示如下：

A. concat：字符串拼接  ⭐⭐⭐

select concat('Hello' , ' MySQL');

B. lower：全部转小写

select lower('Hello');

C. upper：全部转大写

select upper('Hello');

D. lpad：左填充

select lpad('01', 5, '-');

E. rpad：右填充

select rpad('01', 5, '-');

F. trim：去除空格

select trim(' Hello MySQL ');

G. substring：截取子字符串 ⭐

select substring('Hello MySQL',1,5);

案例

由于业务需求变更，企业员工的工号统一改为 5 位数，目前不足 5 位数的需在前面补 0（例如：1 号员工的工号应改为 00001）。

update emp set workno = lpad(workno, 5, '0');

处理完毕后，具体的数据如下：

id	workno	name	gender	age	idcard	workaddress	entrydate
1	00001	抖音号：1332402852	女	20	123456789012345678	北京	2000-01-01
2	00002	张无忌	男	18	123456789012345670	北京	2005-09-01
3	00003	韦一笑	男	38	123456789712345670	上海	2005-08-01
4	00004	赵敏	女	18	123456757123845670	北京	2009-12-01
5	00005	小昭	女	16	123456769012345678	上海	2007-07-01
6	00006	杨逍	男	28	12345678931234567X	北京	2006-01-01
7	00007	抖音号：1232402856	男	40	123456789212345678	北京	2005-05-01
8	00008	黛绮丝	女	38	123456157123645670	天津	2015-05-01
9	00009	范凉凉	女	45	123156789012345678	北京	2010-04-01
10	00010	陈友谅	男	53	123456789012345670	上海	2011-01-01
11	00011	张士诚	男	55	123567897123465670	江苏	2015-05-01
12	00012	常遇奇	男	32	123446757152345678	北京	2004-02-01
13	00013	张三丰	男	88	123656789012345678	江苏	2020-11-01
14	00014	灭绝	女	65	123456719012345670	西安	2019-05-01
15	00015	胡青牛	男	70	12345674971234567X	西安	2018-04-01
16	00016	周芷若	女	18	null	北京	2012-06-01

1.2 数值函数

常见的数值函数如下：

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回 x 除以 y 的余数（模）
RAND() ⭐	返回 0~1 范围内的随机数
ROUND(x,y) ⭐	对参数 x 进行四舍五入，保留 y 位小数

演示如下：

A. ceil：向上取整

select ceil(1.1);

B. floor：向下取整

select floor(1.9);

C. mod：取模

select mod(7,4);

D. rand：获取随机数

select rand();

E. round：四舍五入

select round(2.344,2);

案例  ⭐⭐⭐

通过数据库的函数，生成一个六位数的随机验证码。

思路：通过 rand() 函数可获取 0~1 之间的随机数，将其乘以 1000000 后，舍弃小数部分，若结果长度不足 6 位，则在前面补 0。

select lpad(round(rand()*1000000 , 0), 6, '0');

1.3 日期函数

常见的日期函数如下：

函数	功能
CURDATE()	返回当前日期（格式：YYYY-MM-DD）
CURTIME()	返回当前时间（格式：HH:MM:SS）
NOW()	返回当前日期和时间（格式：YYYY-MM-DD HH:MM:SS）
YEAR(date)	获取指定 date 的年份
MONTH(date)	获取指定 date 的月份
DAY(date)	获取指定 date 的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期 / 时间值，为指定 date 加上一个时间间隔 expr 后的结果
DATEDIFF(date1,date2)	返回起始时间 date1 和结束时间 date2 之间的天数（date1 - date2）

演示如下：

A. curdate：当前日期

select curdate();

B. curtime：当前时间

select curtime();

C. now：当前日期和时间

select now();

D. YEAR、MONTH、DAY：获取当前年、月、日

select YEAR(now());select MONTH(now());select DAY(now());

E. date_add：增加指定的时间间隔

select date_add(now(), INTERVAL 70 YEAR );

F. datediff：获取两个日期相差的天数

select datediff('2021-10-01', '2021-12-01');

案例

查询所有员工的入职天数，并根据入职天数倒序排序。

思路：入职天数 = 当前日期 - 入职日期，可通过 datediff 函数实现。

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;

1.4 流程函数

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

函数	功能
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 默认值

演示如下：

A. if

select if(false, 'Ok', 'Error');

B. ifnull

select ifnull('Ok','Default');select ifnull('','Default');select ifnull(null,'Default');

C. case when then else end

需求：查询 emp 表的员工姓名和工作地址（北京 / 上海 → 一线城市，其他 → 二线城市）

select
name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
from emp;

案例

先创建并插入数据到学员成绩表 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, 'Tom', 67, 88, 95), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

查询学员成绩，并显示各科成绩等级（≥85 为优秀，≥60 为及格，否则为不及格）：

select id,name,(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学',(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' end ) '英语',(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' end ) '语文'from score;

---

函数场景回顾

1. 数据库中存储入职日期（如 2000-01-01），如何快速计算出入职天数？

   答案：datediff 函数。

2. 数据库中存储学生分数值（如 98、75），如何快速判定分数等级？

   答案：case ... when ... 流程函数。

2. 约束

2.1 概述

• 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

• 目的：保证数据库中数据的正确、有效性和完整性。

分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为 null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束⭐	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证字段值满足某一个条件	CHECK
外键约束⭐	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

注意：约束是作用于表中字段上的，可以在创建表 / 修改表的时候添加约束。

2.2 外键约束

2.2.1 介绍

外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

2.2.2 语法

1). 添加外键

-- 方式1：创建表时添加
​
CREATE TABLE 表名(
​
字段名 数据类型,
​
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
​
);
​
-- 方式2：修改表时添加
​
ALTER TABLE 表名 ADD CONSTRAINT [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);

            多了一个蓝色小钥匙 

2). 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

案例：删除 emp 表的外键 fk_emp_dept_id

alter table emp drop foreign key fk_emp_dept_id;

2.3.3 删除 / 更新

添加外键后，删除 / 更新父表数据时产生的约束行为，称为删除 / 更新行为，具体如下：

行为	说明
NO ACTION	当在父表中删除 / 更新对应记录时，先检查该记录是否有对应外键，若有则不允许删除 / 更新（与 RESTRICT 一致），默认行为
RESTRICT	当在父表中删除 / 更新对应记录时，先检查该记录是否有对应外键，若有则不允许删除 / 更新（与 NO ACTION 一致），默认行为
CASCADE	当在父表中删除 / 更新对应记录时，先检查该记录是否有对应外键，若有则同步删除 / 更新子表中关联的外键记录
SET NULL	当在父表中删除对应记录时，先检查该记录是否有对应外键，若有则将子表中该外键值设为 null（需外键允许取 null）
SET DEFAULT	父表有变更时，子表将外键列设为默认值（Innodb 不支持）

具体语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

演示

NO ACTION 是默认行为，已通过前面语法测试，此处演示 CASCADE 和 SET NULL 两种行为：

1). CASCADE

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

A. 修改父表数据：将 dept 表中 id 为 1 的记录修改为 6。

结果：子表 emp 中 dept_id 值为 1 的记录，同步变为 6，体现 CASCADE 级联更新效果。

B. 删除父表数据：删除 dept 表中 id 为 6 的记录。

结果：父表数据删除成功，子表中关联的记录也被级联删除，体现 CASCADE 级联删除效果。
注意：一般业务系统中，不会修改表的主键值。

2). SET NULL

先删除已建立的外键 fk_emp_dept_id，再恢复 emp、dept 表数据。

执行以下语句添加外键并设置 SET NULL 行为

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;

---

大功告成！