您现在的位置是：首页 >技术交流 >06:mysql---约束网站首页技术交流

目录

1介绍

2:约束演示(建表)

3:外键约束    

4:外建行为

5:外建是否可以删除

6:多表查询

1介绍

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

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

3:分类:

注意:约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

2:约束演示(建表)

1:先建立表结构

create table user_a(
id int primary key  auto_increment comment"主键自增",
name varchar(10) not null unique comment "姓名",
age int check (age between 0 and 120 ) comment "年龄",
status char(1) default "1" comment"状态",
gender char(1) not null
) comment "用户表";

 插入数据

insert into user (name, age, status, gender) values ('Tom1', 19, '1', '男'),('Tom2', 25, '0', '男');

insert into user(name,age,status,gender) values ('Tom3',19,'1','男');

insert into user(name,age,status,gender) values ('Tom3',19,'1','男');

insert into user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into user(name,age,status,gender) values ('Tom5',-1,'1'，'男');
insert into user(name,age,gender) values ('Tom5',120,'男');

3:外键约束    

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

写外建时不加行为(on update cascade on delete cascade) 父表的数据不能删除

注意:目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

语法:

--添加外建
create table 表名 (

    字段名 数据类型,
    ........
    
    [constraint]  [文建名称] foreign key(外建字段名)  references 主表 (主表列名)

);

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

-->删除外键
alter table 表名 drop foreign key 外键名称;

准备数据

--准备数据
create table dept(
	id int auto_increment primary key  comment 'ID' ,
	name varchar(5) 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);

现在只是逻辑上存在外建练习,  需要使用代码在数据库层面加上外建关系

建立外建关系:

--添加外建
alter table 表名(子表) add constraint 外建名称 foreign key (外建字段名) references 主表 (主表列名);

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;

4:外建行为

 写外建时加行为(on update cascade on delete cascade) 父表的数据可以删除

语法:

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update cascade on delete cascade;

--外键的删除和更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

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

5:外建是否可以删除

写外建时不加行为(on update cascade on delete cascade) 父表的数据不能删除;

 写外建时加行为(on update cascade on delete cascade) 父表的数据可以删除.

6:多表查询

概述:
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种:
一对多(多对一),        多对多,          一对一

一对多(多对一)

案例: 部门与员工的关系V
关系:一个部门对应多个员工，一个员工对应一个部门
实现: 在多的一方建立外键，指向一的一方的主键

 多对多

>案例: 学生与 课程的关系
>关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
>实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键 

​​​​​​​

 数据准备:

--数据的准备
create table student (
	id int auto_increment primary key comment '主键',
	name varchar(10),
	no varchar(10) comment'学号'
)comment '学生表';
insert into student values (null,'燃绮丝','280100101'),(null,'谢逊','2000100102'),(null,'假天正','2000100103'),(null,'韦-笑','20');

create table course(
	id int auto_increment primary key ,
	name varchar(10)
)comment '课程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop')

create table student_course(
	id int auto_increment comment '主键' primary key,
	studentid int not null comment'学生ID',
	courseid int not null comment  '课程ID',
	constraint fk_courseid foreign key (courseid) references course (id),
	constraint fk_studentid foreign key (studentid) references student (id)
	)comment'学生课程中间表';
	
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

 一对一

案例: 用户与用户详情的关系
关系:一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现: 在任意一方加入外键，关联另外一方的主键，并且设置

​​​​​​​

 数据:

create table tb_user(
	id int auto_increment primary key ,
	name varchar(10) comment'姓名',
	age int comment'年龄',
	gender char(1) comment'1: 男 ，2: 女',
	phone char(11) comment'手机号'

 ) comment'用户基本信息表';

create table tb_user_edu(
	id int auto_increment primary key comment'主键ID',
	degree varchar(20) ,
	major varchar(50) comment '专业',
	primaryschool varchar(5) comment'小学',
	middleschool varchar(50) comment'中学',
	university varchar(5) comment '大学',
	userid int unique comment'用户ID',
	constraint fk_userid foreign key (userid) references tb_user(id)
	) comment '用户教育信息表';