Day04 MySql函数和约束的学习记录

一.函数

--字符串函数

concat（str1，str2）；   //连接字符串str1和str2

lower（str）；    //将大写变小写

upper（str）；   //小写变大写

lpad(str,n,pad);  //左填充，用字符串pad对str左边填充，达到n个字符串长度

rpad(str,n,pad);  //右填充

trim(str);     //去掉str头部和尾部的空格

substring(str,start,len);   //截取字符串，返回字符串str从start位置起的len个长度字符串

#字符串函数
select  concat('hello','world');    #输出hello world
select upper('hello');    #小写变大写
select  lower('HELLO');
select  lpad('01',5,'-');   #在‘01’左边填充‘-’直到达到五位数, 输出结果为‘---01’
select  rpad('01',5,'-');
select  trim('  hello , world ');  #去掉头尾空格
select  substring('hello,world',1,5) 'out'; #返回从字符串str从start位置起的len个长度字符串，输出‘hello’
#实例练习
#将企业员工的workno统一为五位数，不足五位数的在前面补0
update emp set workno=lpad(workno,5,'0');
select  *from emp;

--数值函数

ceil(x);   //对x向上取整

floor(x);  //对x向下取整

mod(x);   //对x取余

rand();    //生成随机数

round(x,y);   //对数字x保留y位小数

#数值函数
select  ceil(3.14);
select floor(3.14);
select  mod(3.14);
select rand();
select round(3.14,0);
#例 通过数据库的函数，生成一个六位数的随机验证码
select rpad(floor(rand()*100000),6,'0');  #rand()生成0-1间的小数，floor取整，floor防止生成五位数在数右边补0

--日期函数

curdate()  //当前 日期

curtime() //时间

now() //当前日期+时间

year（），month（），day（）  //当前年，月，日

date_add(date,interval expr type)   //在当前日期或时间值date的基础上加上时间间隔expr，type可以是年月日，时分秒

datediff（date1，date2）  //date1-date2，得到二者的时间间隔

select curdate();
select  curtime();
select now();
select year();
select month();
select day();
select  date_add('2022-02-03 13:19:30',interval  20 month );
select date_add(now(),interval  50 hour);
select datediff(now(),'2021-2-4');
#例 查询所有员工的入职天数，并根据入职天数倒序
select  name,datediff(now(),entrydate) days from emp order by days desc ;

--流程函数

if(value,t,f）//如果value为true 返回t，否则返回f

ifnull（value1，value2）  //如果value1不为空返回val1，否则返回val2

case[字段名] when [val1] then [res1]....else[default]  end //如果字段名的值等于val1，返回res1..否则返回default默认值

case when [条件] then [res1]...else[default] end   //如果条件为true 返回res1....否则返回default默认值

score表：

select if(true,'ok','error');
select  ifnull('','error');  #不为空
select  ifnull(null,'well');  #返回well
#将表格emp工作地址为“北京”“上海“的划分为一线城市，其他划分为二线城市
select name,
       case  workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end
from emp;

#创建一个成绩单score 将不同分数分为“优秀”，“及格”，“不及格”
create table score(
    id int(10) comment '序号',
    chinese float(4) comment 'chinese',
    math float(4) comment 'math',
   english float(4) comment 'english'
) comment '成绩单';
insert into score values(1,65,55,98),(2,99,88,55),(3,66,98,45),(4,77,58,90),(5,58,97,67),(6,88,98,78),(7,58,78,90);
select id,
       case when chinese>=80 then'优秀' when chinese>=60 then'及格' else '不及格' end,
       case when math>=80 then'优秀' when math>=60 then'及格' else '不及格' end,
       case when english>=80 then'优秀' when chinese>=60 then'及格' else '不及格' end
from score;

================================分割线====================================

二.约束

作用于表中字段上的规则，用于限制存储在表中的数据，目的是保障数据库的数据的准确性，有效性，完整性

--非空约束(not null),唯一约束(unique),主键约束(primary key),默认约束(default),检查约束(check),外键约束(foreign)

例子练习非空约束(not null),唯一约束(unique),主键约束(primary key),默认约束(default),检查约束(check),根据下面表格需求在datagrip创建一个表

字段名	字段含义	字段类型	约束条件	约束关键字
id	id唯一标识	int	主键且自增	primary key,auto_increment
name	姓名	varchar(10)	不为空且唯一	not null,unique
age	年龄	int	0<age<120	check
status	。	char(1)	若没有指定该值，默认为1	default
gender	性别	char(1)	无

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  '性别'
);
#存储用户数据
insert into tb_user (name,age,status,gender) values('Tom1',18,1,'男'),('Tom2',28,1,'男'),('Tom3',38,0,'男'),('Tom4',68,0,'男');
#若插入的name是null，看下会不会报错
insert into tb_user (name,age,status,gender) values(null,18,1,'男');  #报错，违背了name非空性约束，此语句没有申请到主键
#若插入的name是重复，看下会不会报错
insert into tb_user (name,age,status,gender) values('Tom1',18,1,'男');#b报错，违背了name唯一性约束，但申请到了主键，即此语句已经占用了一个id号
#查看主键(primary key)的自增性
insert into tb_user (name,age,status,gender) values('Tom5',98,1,'男');# 这里的Tom5的id=6，因为上一句的id=5
#若status没有指定值
insert into tb_user (name,age,gender) values('Tom7',98,'男');#没有指定status ，默认为1
insert into tb_user (name,age,status,gender) values('Tom6',98,null,'男');
#检查约束
insert into tb_user (name,age,gender) values('Tom8',121,'男');#报错了，121超出设定范围

体会到主键的自增性：

--外键

1.外键约束：外键是用来让两张表（父表与子表，或叫做主表和从表）的数据之间建立连接，从而保证数据的一致性和完整性

主表：

从表：

添加外键语法：

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

删除外键语法：

  alter table 表名 drop foreign key 外键名称;

例.创建父表dept，在子表emp建立外键

 练习代码：

create table dept(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
)comment  '部门表';
insert into dept(name) values('研发部'),('市场部'),('销售部'),('技术部'),('公关部');
#添加外键
alter table emp add constraint  foreign_key foreign key (dept_id) references  dept(id);
#删除外键
alter table emp drop foreign key  foreign_key;

添加外键前可以随意删除主表的任意条数据，添加外键后如果要删除会报错：

 2.外键的删除/更新行为

no action/restrict:在父表中删除/更新对应记录，首先检查是否有对应外键，有则不允许删/更新

cascade:在父表中删除/更新对应记录，首先检查是否有对应外键，有则也删除/更新子表对应记录

set null:在父表中删除记录时，首先检查该记录是否有对应外键，若有则设置子表中  为null

set default:父表有变更时，子表将外键列设置成一个默认的值

语法：

alter table 子表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名) on update cascade/set null/on action  on delete  cascade/set null/on action;

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

约束小结：

非空约束：not null

唯一约束：unique

主键约束：primary key

默认约束：default

检查约束：check

外键约束：foreign key

2023/2/4 有什么需要补充或者写错的地方欢迎大家评论区告诉我哦，谢谢！

继续加油！