【 MYSQL | 基础篇 函数与约束 】

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

思维导图

1. 函数

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

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

1.1 字符串函数

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

演示如下：

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');

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

1.2 数值函数

常见的数值函数如下：

演示如下：

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 日期函数

常见的日期函数如下：

演示如下：

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 语句中实现条件筛选，从而提升语句效率。

演示如下：

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 概述

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

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

分类：

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

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 删除 / 更新

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

具体语法

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 ;

大功告成！