目录
函数使用
字符串函数
数值函数
日期函数
流程函数
约束
外键约束
约束规则
函数使用
函数是指一段可以直接被另一段程序调用的程序或代码，在mysql当中有许多常见的内置函数，接下来开始对这些内置函数及其作用进行简单的讲解和使用：
字符串函数
mysql中内置了很多的字符串函数，常见的有如下几个：
| 函数 | 功能 |
|---|---|
| concat(s1,s2,s3...) | 字符串拼接，将s1，s2，s3...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个长度的字符串 |
接下来对上面的这些字符串函数一一进行一个演示，具体如下所示：
需求案例： 由于业务需求变更，企业员工的工号统一为5位数，目前不足5位数的全部在前面补0，比如1号员工的工号应该为00001，以下面这张员工表为例进行补0：
根据上面这张表想想我们该如何给工号前面补0呢？这里我们用到update函数，直接执行如下命令
update emp set workno = lpad(workno, 5, '0');
数值函数
mysql中内置了很多的数值函数，常见的有如下几个：
| 函数 | 功能 |
|---|---|
| ceil(x) | 向上取整 |
| floor(x) | 向下取整 |
| mod(x,y) | 返回x/y的模 |
| rand() | 返回0~1内的随机数 |
| round(x,y) | 求参数x的四舍五入的值，保留y位小数 |
接下来对上面的这些字符串函数一一进行一个演示，具体如下所示：
需求案例：通过数据库的函数，生成一个六位数的随机验证码，这个时候应该第一时间想到数值函数当中的生成随机数，但是其生成的是小数，如何操作呢？请往下看：
这里我们就借助数值函数的随机数，四舍五入以及左填充的方式实现，语句如下：
-- 生成六位随机数，包含小数
select rand()*1000000;
-- 生成六位随机数, 进行四舍五入保留0位小数
select round(rand()*1000000, 0);-- 生成六位随机数，可以出现小于6位的情况，如0.0012312，不足6位前面补0
select lpad(round(rand()*1000000, 0), 6, '0');
日期函数
mysql中内置了很多的日期函数，常见的有如下几个：
| 函数 | 功能 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| year(date) | 获取指定date的年份 |
| month(date) | 获取指定date的月份 |
| day(date) | 获取指定date的日期 |
| date_add(date, interval, expr type) | 返回应该日期/时间加上一个时间间隔expr后的时间值 |
| datediff(date1, date2) | 返回起始时间date1和结束时间date2之间的天数 |
接下来对上面的这些日期函数一一进行一个演示，具体如下所示：
需求案例：查询所有员工的入职天数并根据入职天数倒序排序，这里我们使用如下这张表进行演示讲解：
想想应该怎么做呢？这里直接获取表中所有的员工姓名，其入职天数等于当前时间和入职时间的间隔，然后再以这个间隔进行降序排序即可，语句如下：
select name, datediff(curdate(), entrydate) as 'entryDays' from emp order by entryDays desc;
流程函数
流程函数也是很常用的一类函数，可以在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默认值 |
接下来对上面的这些流程函数一一进行一个演示，首先这里先演示一下if语句，具体如下所示：
接下来我们要实现对员工表当中的工作地址进行筛选，如果工作地址是北京/上海等地返回一线城市，否则返回二线城市，具体如下所示：
需求案例：根据一张学生表的语数外成绩，分别设置优秀、及格、不及格三种情况，如下：
我们先创建一张学生表，想想如何创建表并赋值数据呢？直接看如下语句：
create table students (id int comment '学号',name varchar(20) comment '姓名',chinese int comment '语文成绩',math int comment '数学成绩',english int comment '英语成绩'
) comment '学生表';
insert into students values (1, '张三', 80, 90, 75), (2, '李四', 15, 80, 90), (3, '王五', 70, 65, 40);
接下来我们开始对这张学生表的成绩进行分类，大于85优秀、大于60及格、小于60不及格：
约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据的，是为了保证数据库中数据的正确、有效性和完整性，对于约束的分类主要分为以下几种：
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制字段的数据不能为null | not null |
| 唯一约束 | 保证字段的所有数据唯一、不重复 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据如果未指定该字段的值则采用默认值 | default |
| 检查约束(8.0.16版本之后) | 保证字段值满足某一条件 | check |
| 外键约束 | 用来让两张表之间的数据建立连接，保证数据的一致性和完整性 | forelgn key |
如下我们可以根据下面这张表的需求完成表的结构创建：
这里我们直接执行如下命令并往表中插入一些数据：
-- 创建表
create table user (id int primary key auto_increment comment '主键',name varchar(10) not null unique comment '姓名',age int check ( age > 0 && age < 120 ) comment '年龄',status char(1) default '1' comment '状态',gender char(1) comment '性别'
) comment '用户表';
-- 插入数据
insert into user (name, age, status, gender) values ('张三', 20, '1', '男'), ('李四', 30, '1', '女' );
insert into user (name, age, status, gender) values ('王五', 40, '2', '男');如下生成的表中自动生成主键并递增，大家可以尝试输入错误的年龄，重复的姓名试一试，数据库都是创建数据失败的：
当然我们也可以借助图形化工具datagrip，直接新建表格然后设置约束，如下所示：
外键约束
外键是用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性，如下可以通过员工表的dept_id字段与部门表建立连接：
在主外键关系当中我们把部门表称为父表(外键所关联的表称为父表)，员工表称为子表(具有外键的表称为子表)，当然有时候我们也可以将其称为主表和从表，目前下面这两张表在数据库层面并未建立外键关联，所以是无法保证数据的一致性和完整性的：
添加外键：为了让两张表之间产生关联，这里我们就需要借助外键约束，添加外键的方式进行，语法如下：
create table 表名 (字段名 字段类型...constraint 外键名称 foreign key 外键字段名 references 主表(主表列名)
);
alter table 表名 add constraint 外键名称 foreign key 外键字段名 references 主表(主表列名);接下来我们先创建一下员工表和部门表这两张表出来，语句如下：
-- 准备数据
create table dept(id int auto_increment primary key comment 'ID',name varchar(50) not null comment '部门名称'
)comment '部门表';
insert into dept(id, name) values (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '人事部'), (5, '行政部');create table emploee(id int auto_increment primary key comment 'ID',name varchar(50) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',manager_id int comment '直属上级ID',dept_id int comment '部门ID'
)comment '员工表';
insert into emploee(id, name, age, job, salary, entrydate, manager_id, dept_id) values(1, '张三', 20, '经理', 5000, '2019-03-04', null, 1),(2, '李四', 30, '主管', 3500, '2019-04-07', 1, 1),(3, '王五', 40, '主管', 3500, '2019-06-07', 1, 2), (4, '赵六', 35, '主管', 3000, '2019-08-07', 1, 3),(5, '钱七', 28, '主管', 3000, '2019-07-07', 1, 4), (6, '孙八', 25, '主管', 3000, '2019-06-07', 1, 4)
两张表创建完成之后，我们直接执行下面这条语句，给员工表添加外键约束，关联主表的id：
-- 添加外键
alter table emploee add constraint fk_emploee_dept foreign key (dept_id) references dept(id);执行完成之后可以看到下图dept_id这个字段出现了蓝色的小钥匙，蓝色代表外键，而黄色的小钥匙代表的是主键：
ok，然后我们可以在datagrip中删除一下部门表中的数据，可以看到数据是不能被删除的，因为部门表是关联着员工表的数据的，是不能被删除的，这里保证了数据的一致性和完整性：
删除外键：如果真的想删除外键关联的主表数据，这里我们可以将外键删除掉，这里两张没有外键约束的表就可以任意进行删除了，执行如下语句删除外键即可：
alter table 表名 drop foreign key 外键名称;这里我们直接执行如下语句删除刚刚我们创建的外键，可以看到我们可以删除主表数据了：
-- 删除外键
alter table emploee drop foreign key fk_emploee_dept;
约束规则
其实外键更新和删除的时候都遵循以下表格中的规则，如下所示：
| 行为 | 说明 |
|---|---|
| no action | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键， 如果有则不允许删除/更新。(与RESTRICT一致) |
| restrict | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键， 如果有则不允许删除/更新。(与NOACTION一致) |
| cascade | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键， 如果有则也删除/更新外键在子表中的记录。 |
| set null | 当在父表中删除对应记录时，首先检查该记录是否有对应外键， 如果有则设置子表中该外键值为nul（这就要求该外键允许取null）. |
| set default | 父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持) |
这里我们可以在创建外键的时候，可以设置on update在更新时如何操作，on delete在删除时如何操作，添加这样的规则可以更加方便的处理外键的联系，举例语句如下：
alter table 表名 add constraint 外键名称 foreign key 外键字段 references 主表名(主表字段名) on update cascade on delete cascade;这里我们做一个演示，添加外键的时候设置删除和更新的规则是cascade，也就是删除和更新主表 的时候，子表也会跟着发送变化，如下所示：
当然如果我们设置更新和删除的时候，规则设置set null，效果也是如下所示：
当然这里我们也可以借助图形化工具直接给表设置外键的规则，很方便：
