mysql基础之函数、约束

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

一、函数

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

1、字符串函数

函数	功能
concat(s1,s2...sn)	字符串拼接
lower(str)	字符串转换小写
upper(str)	字符串转换大写
lpad(str,n,pad)	左填充,将str用pad填充到n位
rpad(str,n,pad)	右填充,将str用pad填充到n位
trim(str)	去除字符串首尾空格
substring(str,start,len)	从start截取len位字符

案例：将员工的工号同一5位，不足5位前面补0，如：1------00001

alter table test modify id varchar(50);
update test set id = lpad(id, 5 , '0');

2、数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(a,b)	模出A%B的值
rand()	随机值
roud(x,y)	对x保留y位小数

案例：生成六位的随机验证码

select round(rand()*1000000,0);

3、日期函数

函数	功能
curdate()	获取当前日期
curtime()	获取当前时间
now()	获取当前日期+时间
year(date)	获取date的年份
month(date)	获取date的月份
day(date)	获取date的日期
date_add(date,INTERVAL expr type)	返回date加上expr后的时间
datediff(date1,date2)	返回两个日期间隔

案例：查询所有员工的入职天数，并根据入职天数降序排序

alter table test add entrydate date;
update test set entrydate = '2021-12-01';
select id, datediff(curdate(),entrydate) as entryday from test order by entryday desc;

4、流程函数

函数	功能
if(value,t,f)	如果value为真返回t否则返回f
isnull(value1,value2)	如果value1不为空，返回value1否则返回value2
case [expr] when [val1] then [res1]...else [default] end	如果expr等于val1,返回res1,否则返回defalut默认值
case when [val1] then [res1]...else [default] end	如果varl1为true,返回res1..否则返回default

案例：查询员工姓名和地址，北京和上海为一线城市，其余为二线

select
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from tb_user;     
    
select
    name,
    case when math >=85 then '优秀' when math >= 60 then '及格' else '不及格' end
from tb_user;

二、约束

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

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

常见的约束有：非空(not null)、唯一(unique)、主键(primary)、外键(foreign)、检查(check)、默认

        3、外键约束

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

check:
​​​​​​​create table user(
    id int primary key auto_increment,
    name varchar(10) not null unique ,
    age int check ( age>0 && age<=120 ),
    status char(1) default '1',
    gender char(1)
)comment '用户表';

  

添加外键：
alter table [子表名] add constraint [外键名] foreign key (字段) references [主表名](字段);
删除外键：
alter table [表名称] drop foreign key [外键名];

例如：
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
alter table emp drop foreign key fk_emp_dept_id;

行为	说明
no action(默认)	当在父表删除\更新数据时，检查是否存在外键，存在则不允许删除\更新
restrict	当在父表删除\更新数据时，检查是否存在外键，存在则不允许删除\更新
cascade	当在父表删除\更新数据时，检查是否存在外键，存在则也更新\删除子表的记录
set null	当在父表删除\更新数据时，检查是否存在外键，存在将子表的外键值设置为null
set default	父表变更时，子表的外键列设置成一个默认值(innodb不支持)

alter table 表名 add constraint 外键名 foreign key (外键字段) 主表名(主表字段) on update 行为 on delete 行为;