表与表之间建关系/修改表数据

表与表之间建关系

外键
　　一对多
　　多对多
　　一对一
所有的信息都记录在一张表中所带来的问题
    1.表的结构不清晰
    2.浪费硬盘空间
    3.表的扩展性极差(无法忽略的缺点)
        
PS:类似于我们将所有的代码都写在用一个py文件内

---

确立表与表之间的关系,一定要换位思考(必须两方都考虑周全之后才能得出结论)
以员工表和部门表为例:
    先站在员工表看能否有多个员工对应一个部门
        分析:
            一个部门能否有多个员工
            可以!!!(暂时只能确定员工单向多对一部门)
    再站在部门表看能否有多个部门对应一个员工
        分析:
            一个员工能否属于多个部门
            不可以!!!
结论:

• 员工表和部门表之间仅仅是单向的多对一
• 那么它们的表关系就是"一对多"
• 表关系中没有多对一一说,只有一对多(无论是多对一还是一对多都叫"一对多")

---

关于让两种表有代码层面上真正的关联,必须使用外键
什么是外键?
    让表与表有硬性层面上的关系
foreign key

• 外键约束
  • 1.在创建表的时候 必须先创建被关联表
  • 2.插入数据的时候 也必须先插入被关联表的数据

# 建表
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(128)
);
create table emp(
    id int primary key auto_increment,
    emp_name varchar(64),
    emp_gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)  
);
​
# 插入数据
insert into dep(dep_name,dep_desc) values('外交部','搞外交'),
    ('教学部','教书育人'),
    ('技术部','技术能力有限部门')
    ;
insert into emp(emp_name,dep_id) values('jason',1),
    ('egon',2),
    ('tank',2),
    ('kevin',3);

---

修改表数据

update dep set id=200 where id = 1;
update emp set dep_id = 300 where id = 1;
    
delete from dep where id = 3;

注意:外键虽然能够帮你强制建立表关系 但是也会给表之间增加数据相关的约束
    
1.删除数据的时候  先删员工表的数据 再删部门表的数据

delete from emp where id = 4;
delete from dep where id = 3;

2.级联更新级联删除

create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(128)
);
create table emp(
    id int primary key auto_increment,
    emp_name varchar(64),
    emp_gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade 
    on delete cascade
);
insert into dep(dep_name,dep_desc) values('外交部','搞外交'),
    ('教学部','教书育人'),
    ('技术部','技术能力有限部门')
    ;
insert into emp(emp_name,dep_id) values('jason',1),
    ('egon',2),
    ('tank',2),
    ('kevin',3);
update dep set id=200 where id = 3;
delete from dep where id = 2;

多对多
图书与作者表
一定要换位思考
　　先站在图书分析:
　　　　多本书能否有一个作者
　　　　　　一个作者能否写多本书  可以!!!
　　再站在作者分析:
　　　　多个作者能否和写一本书
　　　　　　一本书能否有多个作者  可以!!!
如果双方都是可以,那么就是多对多
强调:foreign key只是用来帮你建表关系的 不是某个关系特有的方法

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
​
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

注意:上述的建法是错误的,多对多关系的建立,必须手动创建第三张表,用来专门记录两种表之间的关系

# 先建两种普通的表  不需要设置外键
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int
);
create table book2author(
    id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);
​
insert into book(title,price) values('三体',199),('聊斋',299),('jason教你删别人的库,让别人跑去吧',1);
insert into author(name,age) values('jason',18),('tank',38);
insert into book2author(book_id,author_id) values(4,3);  # 报错
insert into book2author(book_id,author_id) values(1,1),(1,2),(2,1),(3,1),(3,2); 

---

一对一表关系

• 1.一对一的场景,当你的表特别庞大的时候,你可以考虑拆分表
• 2.联想老男孩的客户和学生
  • 当你没有交学费之前,你是老男孩的客户
  • 当你叫了学费之后,你就变成老男哈的学生
  • 并不是所有的客户都能变成学生

通常将关系字段称之为外键字段

• 一对多的外键字段建在多的一方
• 多对多:建在第三张表了
• 一对一:外键字段建在任意一方都可以,但是推荐你建在查询频率较高的一方
• 用户user:(id  name  age)
• 用户信息userdetail:(hobby addr IDCard)

create table authordetail1(
        id int primary key auto_increment,
        phone int,
        addr char(255)
);
create table author1(
    id int primary key auto_increment,
    name char(4),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail1(id)
    on update cascade
    on delete cascade
);

---

判断表关系的最简单的语法
图书与出版社
　　一本书可不可以有多个出版社  不可以!!!
　　一个出版社可不可以出版多本书  可以!!!
　　一对多的关系
图书与作者表
　　一本书可不可以有多个作者    可以!!!
　　一个作者可不可以写多本书     可以!!!
　　多对多的关系
作者与作者详情
　　一个作者可不可以有多个详情  不可以!!!
　　一个作者详情可不可以有多个作者  不可以!!!
　　要么两者是一对一
　　要么两者之间没任何关系

---

了解知识点
1.修改表的完整语句
　　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;