首页 > 其他 > 详细

外键、复制表、修改表

时间:2019-08-20 17:21:24      阅读:94      评论:0      收藏:0      [点我收藏+]

前言

  提出问题

    所有的信息都记录在同一张表中所带来的问题:
    1. 表的结构不清晰
    2. 浪费硬盘空间
    3. 表的扩展性差(这一点是无法忽略的)???

  解决方案

    解耦,拆分表
    拆分表 就要搞明白表与表之间的关系

  ??如何确认表与表之间的关系:

    技术分享图片
“换位思考”,必须两方都考虑周全之后才能得出结论?

举例:员工表和部门表之间的关系?:
?先站在员工表看是否可以:
        ??多个员工对应一个部门
        ??翻译过来:一个部门是否能有多个员工?
        ??可以(所以暂时确认了员工反向多对一部门)
再站在部门表看是否可以:
        ??多个部门对应一个员工
        ??翻译过来:一个员工是否可以属于多个部门
        ??不可以?????

结论:员工表和部门表之间 仅仅是单向的多对一
那么他们的关系就是“一对多”

注意:关系表中没有多对一之说,只有一对多(无论是多对一还是一对多,都叫一对多)????
View Code

表关系之 一对多

  1. 在创建表示,先创建被关联的表dep,才能创建关联表emp 

    技术分享图片
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)??
??)??;
View Code

  2. 在插入记录是,必须先插入被关联表dep,才能插入关联表emp

    技术分享图片
insert into dep(dep_name, dep_commont) values
?(教学部辅导学生),
(外交部, 形象公关);

insert into emp(name, gender, dep_id) values
(alex, male, 1),
(egon, male, 2),
(xxx, male, 1);
View Code

  3. 删除?

  问题:无法随意删除

    1. 当想删除“被关联表dep?”的id 或者?emp的dep_id时,都报错
    2. 想整个删除“被关联表dep?”的某个字段  如教学部时,也报错
    因为这些都是被关联的东西,无法直接删除

  临时处理方案:

    方式一:?先删除教学部对应的所有员工(相当于解除了关联关系),就可以删除教学部了

    方式二:新的方法,如下:?
    问题描述:
    ?受限于外键的约束,导致操作数据变得非常复杂,
    ?能否有一个简单的方式,让我们不需要考虑在操作目标表的时候还需要考虑关联表的情况?
    比如删除部门的时候,那么这个部门对应的员工就应该自动跟着立即清空(这也是符合常理的)

    解决方案:??
    在创建表的时候,加上两个东西:on udate cascade 和 on delete cascade?
    # 删除部门后,对应的部门里面的员工表数据对应删除
    # 更新部门后,对应员工表中的标示部门的字段同步更新
    技术分享图片
??????????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
);
?
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);
级联删除,级联修改

表关系之 多对多

    # 图书表与作者表之间的关系

    """
    仍然站在两张表的角度:
    1.站在图书表:一本书可不可以有多个作者,可以!那就是书多对一作者
    2.站在作者表:一个作者可不可以写多本书,可以!那就是作者多对一书
    双方都能一条数据对应对方多条记录,这种关系就是多对多!
    """

   # 先来想如何创建表?
    图书表需要有一个外键关联作者,
    作者也需要有一个外键字段关联图书。

    问题来了,先创建谁都不合适!如何解决?
    # 建立第三张表,该表中有一个字段??是左表的id,有一个字段是右表的id
     技术分享图片
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
);
?
insert into author(name) values
(egon),
(alex),
(wxx);
?
insert into book(bname,price) values
(python从入门到入土,200),
(葵花宝典切割到精通,800),
(九阴真经,500),
(九阳神功,100);
?
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
);
?
insert into author2book(author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,1),
(3,2),
(3,3),
(3,4);
代码演示

表关系之 一对一

    客户表和学生表
    ?(老男孩的客户与学生之间,报名之前都是客户,只有报了名的才能是学生)
?
    # 左表的一条记录唯一对应右表的一条记录,反之也一样??
    技术分享图片
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
);
?
# 三种外键关系都是用foreign key,区别在于如何使用以及其他条件限制即可做出三种关系
View Code

表关系之 小结

    
     通常将关系字段 称之为 外键字段    一对多的外键字段 建在多的一方    多对多 建在第三张表了    一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率较高的一方

修改表

    # mysql对大小写不敏感!!!
    语法:
      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 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
        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;

 

外键、复制表、修改表

原文:https://www.cnblogs.com/buzaiyicheng/p/11384011.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!