MySQL学习笔记——函数和约束

学习黑马MySQL课程记录笔记，用于复习。

函数

字符串函数

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('Hello' , ' MySQL');
select lower('Hello');
select upper('Hello');
select lpad('01', 5, '-');
select rpad('01', 5, '-');
select trim(' Hello MySQL ');
select substring('Hello MySQL',1,5);

数值函数

常见的数值函数如下：

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand()	返回0~1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

日期函数

常见的日期函数如下：

函数	功能
curdate()	当前日期
curtime()	当前时间
now()	当前日期和时间
YEAR(date)	指定date的年份，如YEAR(now())
MONTH(date)	指定date的月份
DAY(date)	获取指定date的日期
date_add(date, INTERVAL exprtype)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	起始时间date1和结束时间date2之间的天数

流程函数

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

#case when then else end
#查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select name,
(case workaddress when '北京' then '一线城市'  when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

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

select name if(workaddress in('北京','上海'),'一线城市','二线城市') as '工作地址' from emp;

约束

作用于表中字段的规则，在创建表/修改表的时候添加约束，用来限制表中数据。

分类

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

CREATE TABLE tb_user(
	id int AUTO_INCREMENT PRIMARY KEY 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 '性别'
);

检查约束(CHECK)和非空约束 (NOT NULL)在数据到达数据库之前即被评估并阻止了操作
违反唯一约束(UNIQUE)约束的操作通常是在尝试插入数据时检测到的。

外键约束

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

添加外键：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
ALTER TABLE emp1 ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ;
alter table emp1 add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

#删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
alter table emp1 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;

CASCADE

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

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 ;