作者简介:一名云计算网络运维人员、每天分享网络与运维的技术与干货。
座右铭:低头赶路,敬事如仪
个人主页:网络豆的主页
前言
本章将会讲解MySQL数据库的函数与约束的讲解。
一.函数
其实,上述的这一类的需求呢,我们通过 MySQL 中的函数都可以很方便的实现 。
1.字符串函数
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);
update emp set workno = lpad(workno, 5, '0');
2.数值函数
演示如下:
select ceil(1.1);
select floor(1.9);
select mod(7,4);
select rand();
select round(2.344,2);
select lpad(round(rand()*1000000 , 0), 6, '0');
3.日期函数
演示如下:
select curdate();
select curtime();
select now();
select YEAR(now());
select MONTH(now());
select DAY(now());
select date_add(now(), INTERVAL 70 YEAR );
select datediff('2021-10-01', '2021-12-01');
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by
entrydays desc;
4.流程函数
演示如下:
select if(false, 'Ok', 'Error');
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
select
name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else
'二线城市' end ) as '工作地址'
from emp;
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95
), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);
select
id,
name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )
'数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'
end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'
end ) '语文'
from score;
二. 约束
1.概述
注意:约束是作用于表中字段上的,可以在创建表 / 修改表的时候添加约束。
2.约束演示
对应的建表语句为:
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',19,'1','男'),
('Tom2',25,'0','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user(name,age,status,gender) values (null,19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男');
insert into tb_user(name,age,gender) values ('Tom5',120,'男');
3.外键约束
(1)介绍
注意:目前上述两张表,只是在逻辑上存在这样一层关系;在数据库层面,并未建立外键关联, 所以是无法保证数据的一致性和完整性的。
没有数据库外键关联的情况下,能够保证一致性和完整性呢,我们来测试一下。
准备数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办');
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) 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', 2,1),(4, '韦一笑', 48, '开
发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程
序员鼓励师',6600, '2004-10-12', 2,1);
接下来,我们可以做一个测试,删除id为1的部门信息。
文章来源:https://www.toymoban.com/news/detail-611370.html
(2)语法
1). 添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名) ;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
2). 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
alter table emp drop foreign key fk_emp_dept_id;
4.删除/更新行为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
由于 NO ACTION 是默认行为,我们前面语法演示的时候,已经测试过了,就不再演示了,这里我们再演示其他的两种行为: CASCADE 、 SET NULL 。
1). CASCADE
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update cascade on delete cascade ;
我们发现,原来在子表中dept_id值为1的记录,现在也变为6了,这就是cascade级联的效果。
在一般的业务系统中,不会修改一张表的主键值。
我们发现,父表的数据删除成功了,但是子表中关联的记录也被级联删除了。
2). 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;
创作不易,求关注,点赞,收藏,谢谢~ 文章来源地址https://www.toymoban.com/news/detail-611370.html
到了这里,关于MySQL——函数与约束的讲解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!