数据库基础（二）函数及约束

数据库基础（二）函数及约束

一、函数

1、字符串函数

函数名	功能
concat(str1,str2,…)	字符串拼接
lower(str)	j转换为小写
upper(str)	转换为大写
trim(str)	去除头部和尾部空格
substring(str,start,len	从字符串str的start位置开始获取长度为len的子串
lpad(str,n,pad)	使用pad进行左填充，填充到长度为n
rpad(str,n,pad)	使用pad进行右填充，填充到长度为n

//将用户id统一为4位数，不足四位的前面补0
update user set id=lpad(id,4,'0')；

2、数值函数

函数名	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	取模运算
rand()	返回0~1之间随机数
round(x,n)	四舍五入，保留n位小数

//生成一个六位验证码
select lpad(round(rand()*1000000,0),6,'0')；

3、日期函数

函数名	功能
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 datediff('2022-10-1','2022-11-11');
//查询用户注册天数，并降序排序
select name,dateddiff(curdate(),register_date) as 'register_days' from user order by register_days desc;

4、流程函数

函数名	功能
if(value,a,b)	如果value为true 返回a，否则返回b
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,'success','failed');
//查询user表,用户权限  0为管理员  1为普通用户 其它为游客
select username,(case authority when '0' then'管理员' when '1' then '普通用户' else '游客' end) as '用户权限' from user;

二、约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据，以保证数据库的正确性、有效性和完整性。

约束分类	功能	关键字
非空约束	该字段不能为null	NOT NULL
唯一约束	限制该字段数据唯一，不能重复	UNIQUE
主键约束	声明为为主键，要求非空且唯一	PRIMARY KRY
默认约束	未指定该字段值，则采用默认值	DEFAULT
检查约束	保证字段满足设置条件	CHECK
外键约束	将两张表的数据之间建立连接，保证数据一致性和完整性	FOREIGN KEY

create table user(
id int primart key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名'，
age int check(age>0 && age<=100) comment '年龄'，
status char(1) default '1'
) comment '用户表'；

外键约束

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

员工表(子表)

id	name	age	dept_id
1	张三	18	001
2	里斯	22	002
3	万我	23	001

.

部门表(主表)

id	dept_name
001	研发部
002	算法部

外键

create table 表名(
	字段名 数据类型,
	...
	[constraint] [外键名称] foreing key(外键字段名） references 主表（主表列名）
	);

alert table 表名 add constraint 外键名称 foreign key(外键字段名） references 主表 (主表列名);


//添加外键
alert table emp add constraint fk_id foreign key(dept_id) references dept(id);
//删除外键
alert table 表名 drop foreign key 外键名称;

外键约束

关键字	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录。
SET BULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null.
SET DEFAULT	父表变更时，子表将外键列设置成一个默认值