【MySQL基础篇】函数及约束

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

#字符串函数
#concat
select concat ('hello',' mysql');
#lower
select lower('Hello');
#upper
select upper('Hello');
#lpad
select lpad('01',5,'-');
#rpad
select rpad('01',5,'-');
#trim
select trim('  hello mysql ');
#substring
select substring('Hello mysql',1,5);

案列：

由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0，比如1号员工应为00001

update staff_table set worknumber=lpad(worknumber,5,'0') where id;

 · 函数 - 数值函数

常见的数值函数如下：

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模长
RAND()	返回0~1内的随机数
ROUND()	求参数x的四舍五入的值，保留y位小数

#数值函数
#ceil(x)
select ceil(1.5);
#floor(x)
select floor(1.5);
#mod(x,y)
select mod(3,4);
#rand()
select rand();
#round(x,y)
select round(2.345,2);

 案例：

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

select lpad(round(rand()*1000000,0),6,'0');
#不加lpad函数可能会出现错误比如生成的随机数位0.019255*1000000得到19255只有五位数
select rpad(ceil(rand()*1000000),6,'0');

 · 函数 - 日期函数

常见的日期函数如下：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值将上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

#日期函数
#curdate()
select curdate();
#curtime()
select curtime();
#now()
select now();
#year(date)
select year(now());
#month(date)
select month(now());
#day(date)
select day(now());
#date_add(date,INTERVAL expr type)
select date_add(now(),INTERVAL 70 day);
#datediff
select datediff(now(),'2021-12-31');

案例：

查询所有员工的入职天数，并更具入职天数倒序排序

select name,datediff(curdate(),staff_date) 'days' from staff_table order by days 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
select if(true,'ok','error');
#ifnull
select ifnull(null,'hello');
select ifnull('true','mysql');
#case when then end
#需求：查询staff_table表的员工姓名和工作地址（如果员工的地址是北京/上海----->展示一线城市，其他城市----->展示二线城市）
select name,case dress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end '工作地址' from staff_table;create table score(id int comment 'ID',name varchar(20) comment '姓名',chines int comment '语文',math int comment '数学',english int comment '英语'
)comment '学生成绩表';
insert into score values(1,'米老鼠',90,80,66),(2,'唐老鸭',67,88,91),(3,'杰瑞',76,71,77),(4,'汤姆',88,55,62);
#需求：统计班级各个学员的成绩，展示规则如下：
#>=85,展示优秀
#>=60,展示几个
#否则展示不及格
selectid,name,(case when chines >=85 then '优秀' when chines >=65 then '及格' else '不及格' end)'语文',(case when math >=85 then '优秀' when math >=65 then '及格' else '不及格' end)'数学',(case when english >=85 then '优秀' when english >=65 then '及格' else '不及格' end)'英语'from score;

2、约束 

1、概述：约束作用与表中字段上的规则，用于限制存储在表中的数据。

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

3、分类：

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

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

· 约束 - 演示 

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

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	PRIMARY KEY,AUTO_INCREMENT
name	姓名	varchar(10)	不为空，并且唯一	NOT NULL,UNIQUE
age	年龄	int	大于0，并且小于等于120	CHECK
status	状态	char(1)	如果没有指定该值，默认为1	DEFAULT
gender	性别	char(1)	无

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 '性别'
)comment '用户表';

· 约束 - 外键约束

1、 概念：外键用来让两张表的数据之间建立联系，从而保证数据的一致性和完整性。

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

建立外键的语法：

CREATE TABLE 表名(

                字段名 数据类型,

                ...

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

); 

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名); 

create table dept(id int primary key auto_increment comment 'ID',name varchar(10) not null unique comment '部门名称'
)comment '部门表';
insert into dept (id,name) values(1,'研发部'),(2,'财务部'),(3,'销售部'),(4,'市场部'),(5,'总经办');
create table emp(id int primary key auto_increment comment 'ID',name varchar(10) not null unique comment '姓名',age int comment '年龄',job varchar(10) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id int comment '部门ID'
)comment '员工表';
insert into emp(id,name,age,job,salary,entrydate,managerid,dept_id) values(1,'金庸',66,'总裁',20000,'2000-01-01',null,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),(3,'杨逍',33,'开发',8400,'2000-11-03',4,1),(4,'韦一笑',48,'开发',11000,'2002-02-05',4,1),(5,'常遇春',43,'开发',10500,'2004-09-07',2,1);
#添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

 删除外键的语法：

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

#删除外键
alter table emp drop foreign key fk_emp_dept_id;

· 约束 - 外键删除更新行为 

删除/更新行为

行为	说明
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; 

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

---