个人技术分享

MySQL 函数与约束

1 函数

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

1.1 字符串函数

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

-- 左填充
select lpad('01',5,'-');	-- 得到---01
-- 右填充
select rapd('01',5.'-');	-- 得到01---

1.2 数值函数

常见的数值函数如下：

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

思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

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

1.3 日期函数

常见的日期函数如下：

-- 增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR );

1.4 流程函数

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

select if(false, 'Ok', 'Error');
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
-- 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select
name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else
'二线城市' end ) as '工作地址'
from emp;

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

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;

2 约束

2.1 概述

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

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

• 分类：

  

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

2.2 约束演示

案例：根据需求 完成表结构的创建

create table user(
	id int PRIMARY KEY AUTO_INCREMENT comment 'ID唯一标识',
	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 '性别'
);

2.3 外键约束

• 概念

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

左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。

注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的

• 添加外键语法

  CREATE TABLE 表名(

  ​ 字段名 数据类型,

  ​ ...

  ​ [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表（主表列名）

  );

-- 为emp表的dept_id字段添加外键约束,关联dept表的主键id
alter table emp add CONSTRAINT waijian FOREIGN KEY(dept_id) REFERENCES dept(id);

• 删除外键

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

  -- 删除emp表的外键waijian
  alter table emp drop foreign key waijian;
  

2.4 删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:

• 语法：

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

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

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