MYSQL多表设计;

一对多 ，多对多，一对一。

• 外键的使用；

一对多 的关系；

在一对多的关系时候，用单的主键作为多的外键约束。保证数据的完整性。PS；AVG（）聚合函数不能和SUM（）聚合函数嵌套使用，需要进行ifnull判断来使用：

 

对表添加外键约束2中方式：

（1）创建table时候确定；

（2）后期添加 外键约束；

-- 一对多 多对多 一对多  表关系之间的外键约束



-- 创建表的时候就添加外键约束
create table dept(
id int primary key auto_increment,
name varchar(20)
);

create table employee1(
id int primary key auto_increment,
name varchar(20),
age int ,
salary double,
-- 添加外键foreign key
dept_id int,
foreign key (dept_id) references dept (id)

);

insert into dept values(null,'人事部');
insert into dept values(null,'财务部');
insert into dept values(null,'公关部');
insert into dept values(null,'总经理办公室');

/*给雇员表添加数据*/

insert into employee1 values(null,'小乔',18,10000,1);
insert into employee1 values(null,'大乔',19,10000,1);
insert into employee1 values(null,'曹操',20,12000,2);
insert into employee1 values(null,'周瑜',21,13000,3);
insert into employee1 values(null,'刘备',22,14000,4);
--  测试外键是否起作用    删除了一列失败 说明存在外键约束作用了
delete from dept where name = '人事部';
delete from employee1 where name= '大乔';  -- 可以删除 
insert into employee1 values(2,'大乔',19,10000,1);
-- ---------------------------------------------------------------------------------------

--   创建完成表以后没有添加外键约束   仍可以添加外键约束

-- 语法 :  alter table 从表名称 add foreign key (外键列的名称) references 主表名称(主键)

create table dept2(
id int primary key auto_increment,
name varchar(20)
);

create table employee2(
id int primary key auto_increment,
name varchar(20),
age int ,
salary double
);
--   插入数据
insert into dept2 values(null,'人事部');
insert into dept2 values(null,'财务部');
insert into dept2 values(null,'公关部');
insert into dept2 values(null,'总经理办公室');

/*给雇员表添加数据*/

insert into employee2 values(null,'小乔',18,10000);
insert into employee2 values(null,'大乔',19,10000);
insert into employee2 values(null,'曹操',20,12000);
insert into employee2 values(null,'周瑜',21,13000);
insert into employee2 values(null,'刘备',22,14000);

-- 添加外键 
alter table  employee2   add foreign key (dept2_id)  REFERENCES  dept2(id);   

-- 报错> 1072 - Key column 'dept2_id' doesn't exist in table
-- 插入外键的列
alter table employee2 add dept2_id int ;   
update employee2 set dept2_id =1 where id = 1;
update employee2 set dept2_id =1 where id = 2;
update employee2 set dept2_id =2 where id = 3;
update employee2 set dept2_id =3 where id = 4;
update employee2 set dept2_id =4 where id = 5;

--  然后指定外键    
alter table  employee2   add foreign key (dept2_id)  REFERENCES  dept2(id);   

--  无法删除 dept2 中数据
delete from dept2;


-- -----------------------------------------------------------------------------------------------

--  truncate 和delete  的区别；
truncate table dept;
truncate table employee1;

-- 使用 truncate 是摧毁表重建   主键key不存在 ，delete table 主键key存在

delete from dept;    
delete from employee1;   

 

 

--------------------------------------------------------------------------------------------------------------------------------------

多对多的关系：

 

多对多，需要创建一张新的table，对2张表进行外键约束。

中间表对另外两张表关联，中间表删除才可以删除其他表。

 

--   多对多关系   的外键约束 需要创建中间表 来约束2张表的关系

create table coder(
id int primary key auto_increment,
name varchar(20),
salary varchar(20)

);
create table project(
id int primary key auto_increment,
name varchar(10)

);

create table coder_project(
coder_id int ,
project_id int ,
-- 添加外键

foreign key (coder_id) references coder(id),
foreign key (project_id )  references project(id )

);

-- 删除主表

drop table project;   -- 不能删除
drop table coder;    -- 不鞥删除

drop table coder_project ;   -- 删除从表之后  才能删除主表  下面不一一测试

-- -------------------------------------------------------------------------------------------------------
show tables;  -- 查看数据库表信息

 

 

 

 

---------------------------------------------------------------------------------------------------------------------------------------

一对一；

两边表，每张表都可以添加外键约束；

 

---------------------------------------------------------------------------------------------------------------------------------------