数据库3表之间的关系

一.一对多

当我们把所有的信息存在同一张表中:

缺点:

　　结构不清晰,分不清主次(员工信息还是部门信息是主体)

　　扩展性差(假设要取消销售部需要一个个去删除)

　　其中有重复的内容,造成内存的浪费

解决方式:(将表进行拆分)

当我们拆分成2张表的时候,这时候表和表之间就没有关系了,现在我们需要找出其中的关系并给他们建立关系.这时候就用到外键了.

站在员工的角度:
    一名员工只能属于一个部门
站在部门的角度:
    一个部门可以有多名员工

一对多关系的建立:

ps:当理出关系后,建表时一定要先创建被关联表,要不会报错

　

#创建被关联表dep
create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);

#创建emp表
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);

ps:当我们建好表之后插入数据,这是也要先插入被关联表中的数据,要不也会报错

#先插入被关联的表数据
insert into dep(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('nb技术部','nb技术能力有限部门');

#在插入关联表数据
insert into emp(name,gender,dep_id)  values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);

删除值:若此时删除dep表中的id=1的值,这时候会报错:

原因是此时这两张表还在关联,你要是删除了dep中的id=1的信息,那表emp中dep_id=1的值就是无家可归的孤儿了,所以不能删除.

修改:当我们要修改emp表中的dep_id值,若要将其改成dep中存在的id值是可以的,但是若要修改为不存在的值也会报错:

若想改变着这种情况有以下方法:

1.删表重建

2.先删除员工表中的数据,再删部门表

这些方式当数据量较大时都比较耗费时间.所以都不采用,联想将两表的数据进行关联,同步删除同步修改

create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade  # 级联修改  (同步修改)
    on delete cascade  # 级联删除  (同步删除)
);

二.多对多

当表之间的关系时多对多的时候,若我们还像一对多一样去创建表的话,就会出现和之前创建表一样的情况,那是因为两表之间都存在外键,相互关联,先建谁都不行,这时候就需要一个第三方来和他们关联,将其数据进行整合关联.这时候这两张表看起来就没有任何关系了

#创建作者表
create table author(
    id int primary key auto_increment,
    name char(16)
);

#创建书表
create table book(
    id int primary key auto_increment,
    bname char(16),
    price int
);

#创建第三方表
create table author2book(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
ps:一定要注意逗号的使用

三.一对一

左边的一张表一条数据唯一对应右边表的一条数据(或不存在对应),反过来也成立

create table customer(
    id int primary key auto_increment,
    name char(20) not null,
    qq char(10) not null,
    phone char(16) not null
);

create table student(
    id int primary key auto_increment,
    class_name char(20) not null,
    customer_id int unique, #该字段一定要是唯一的
    foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
    on delete cascade
    on update cascade
);
ps:外键一般放置在查询较多的表中

总结:总结表和表之间的关系一定要站在双方的角度去看,不能只看一边,当有两个一对多的时候就是多对多,当有一个一对多就是一对多,当没有一对多关系时可能为两情况,要么没关系,要么就是一对一的关系,这需要我们自己去判断

修改表:

语法：
1. 修改表名  
      ALTER TABLE 表名 
                          RENAME 新表名;
2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;                       
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;
4. 修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

复制表:

# 查询语句执行的结果也是一张表，可以看成虚拟表

# 复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table new_service select * from service;

# 只复制表结构
select * from service where 1=2;        //条件为假，查不到任何记录

create table new1_service select * from service where 1=2;  

create table t4 like employees;