MYSQL-多表操作-外键约束

  简介:在一个项目当中,通常需要许多个表之间建立一定的联系才能够完成,所以我们需要在单表操作的基础之上学会如何多表操作
-- MYSQL当中的表跟表之间的联系可以大概的分为三种:
-- 1.一对一
-- 2.一对多 || 多对一 (相对的)
-- 3.多对多

多表操作--外键约束:

外键约束通常跟主键约束一起进行使用,例如,对于两个相互有关联的表来说,相关联字段的主键所在的表就叫做是主表,外键所在的表就是从表
-- 关键字:  foreign key

一:建立外键约束的要求:

1、在表上建立外键，主表应先存在。

2、一个表可以建立多个外键约束。

3、从表的外键列必须指向主表的主键列。

4、从表的外键列可以与主表参考的列名不同，但数据类型必须相同。

二:操作方法:有两种设置外键约束的方法:

-- 方法一:
create table if not exists bumen(
detpno varchar(20) primary key,
name VARCHAR(20)
);

create table if NOT exists emp(
eid VARCHAR(20) PRIMARY KEY,
name VARCHAR(20),
age int,
dept_id VARCHAR(20),
constraint bumen_emp-- 随便起的有关两个表之间联系的名称,方便之后进行删除 
FOREIGN key emp(dept_id) 
REFERENCES bumen(detpno) 
);

-- 方法二:
create table if NOT exists emp(
eid int PRIMARY KEY auto_increment,
name VARCHAR(20),
age int,
dept_id VARCHAR(20)
);
ALTER table emp add constraint bumen_emp
foreign key (dept_id)
REFERENCES bumen(detpno);

二:外键约束操作:

        1>.增加数据:

 tips:

        1.必须首先给主键进行添加数据,之后才能添加从表的数据!两者之间存在某种联系

        2.在给从表添加数据的时候,不能随便的进行添加,要随时记得从表当中某些列跟主表有关系!基于主表的数据进行添加

insert into bumen VALUES('1001','销售部');
insert into bumen VALUES('1002','研发部');
insert into bumen VALUES('1003','财务部');
insert into bumen VALUES('1004','人事部');

insert into emp values(null,'乔峰',20,'1001');
insert into emp values(null,'段誉',21,'1001');
insert into emp values(null,'虚竹',23,'1001');
insert into emp values(null,'阿紫',18,'1002');
insert into emp values(null,'扫地僧',35,'1002');
insert into emp values(null,'李秋水',33,'1003');
insert into emp values(null,'鸠摩智',50,'1003');

-- 在这里,这条数据无法进行添加,是因为在主表当中并没有存在这个数据,所以从表无法根据主表进行添加!
insert into emp values(null,'天山童姥',20,'1005');

        2>删除数据:

                1.外键约束删除表的时候,只能够先删除从表,之后才能够删除主表,因为两者之间存在约束

                2.在删除表当中的数据上,主表的数据如果在从表当中有使用到,那么不能够随便删除,从表当中没有才能够删除

-- 示例:
DELETE from bumen where deptno='1001'; -- 无法删除,因为从表当中有相关的数据
DELETE from bumen where detpno='1004';-- 可以删除,因为从表当中没有存在有关1004的数据

                3.对于从表当中的数据,所有的数据都能够进行删除,并没有条件的限制

DELETE from emp where eid=5;
-- 也可以直接清空整个表当中的数据
DELETE from emp;

TRuncate emp;

三:外键约束删除:

-- 删除外键约束
alter table emp drop FOREIGN key bumen_emp;
-- alter table 从表名称 drop foreign 外键约束的名称

四:多对多表的操作:

两个表之间,可能有多对多之间的关联,所以,如果需要建立多对多之间的关系,就需要中间表将两者联系起来才行,因此,多对多的外键约束建立,实际上就是多个  一对多||多对一  联系起来即可

关键语句:alter table (从表表名)(tips:中间表的表名) add [constraint (名称) ]  foreign key (从表列) 

references 主表名(主表列);

-- 首先需要先建立主表,以及中间的从表
create table if not exists student(
id int,
name VARCHAR(20) PRIMARY KEY,
age int,
gender VARCHAR(20)
);

CREATE table course(
id int,
course varchar(20) PRIMARY key
);


CREATE table mids(
name VARCHAR(20),
course VARCHAR(20),
score int
);

-- 给课程表添加数据
insert into course VALUES(1,'语文');
insert into course VALUES(2,'数学');
insert into course VALUES(3,'英语');

-- 建立两个主表跟从表之间的联系
-- tips:从表的外键列必须!指向主表的主键列才行!!!
alter table mids 
add CONSTRAINT mid_student
foreign key(name) references student(name);

alter table mids 
add CONSTRAINT mid_course
FOREIGN key(course)
REFERENCES course(course);


-- 给学生表当中先添加数据
insert into student VALUES(1,'王1',19,'男');
insert into student VALUES(2,'秦1',19,'男');
insert into student VALUES(3,'王2',18,'男');
DELETE from student;
-- 给中间表添加数据
DELETE from mids;
insert into mids VALUES('王1','语文',98);
insert into mids VALUES('王1','数学',97);
insert into mids VALUES('秦1','英语',96);
insert into mids VALUES('秦1','语文',99);
insert into mids VALUES('王2','英语',100);

select * from mids where name like '王%' ;