MySQL >>> 表的操作二

今日内容：

　　　　外键、表关系、修改表的完整语句、复制表

 

问题的发现：

　　首先定义一张部门员工表：

　　　　　

　　发现：把所有数据都存放于一张表的 弊端
　　　　　1. 组织结构不清晰                 没有突出重点
　　　　　2. 浪费硬盘空间                    dep_name 多个重复
　　　　　3. 扩展性极差                       增加内容不易

　　解决方案：

　　　　　　拆 ！！！

　　　　如何拆 ？？？

　　　　　　首先要判断表与表之间的关系：一定要 换位思考 (必须两方都考虑周全之后才能得出结论)       然后再拆

 　　　　　  

　　　　　　　　##########################################################

　　　　　　　　判断表关系的最简单的语法：
  　　　　　　　　  ^_^  一个    可不可以   的故事  ^_^

　　　　　　　　　图书与出版社：
  　　　　　　　　　　　　 一本书可不可以有多个出版社       不可以!!!
  　　　　　　　　　　　　 一个出版社可不可以出版多本书    可以!!!
  　　　　　　　　　　　　　　　　 一对多 的关系：　表关系中没有 多对一 一说，只有一对多     (无论是多对一还是一对多都叫"一对多")

  　　　　　　　　　　图书与作者：
 　　　　　　　　　　　　  一本书可不可以有多个作者     可以!!!
  　　　　　　　　　　　　 一个作者可不可以写多本书     可以!!!
  　　　　　　　　　　　　　　　　 多对多 的关系

  　　　　　　　　　　作者与作者详情：
  　　　　　　　　　　　　 一个作者可不可以有多个详情             不可以!!!
  　　　　　　　　　　　　 一个作者详情可不可以有多个作者        不可以!!!
   　　　　　　　　　　　　　　　　 要么两者是 一对一
    　　　　　　　　　　　　　　　　要么两者之间 没任何关系

　　　　　　　　##########################################################

 　　　　　　

　　　　　　此外：让两张表有代码层面上真正的关联，就必须使用 外键

 

外键

　　作用：让表与表有硬性层面上的关系

　　关键词：foreign key   会限制输入内容必须在被关联表的范围内

　　使用外键有一定的约束：

　　　　　　1. 在创建表的时候 必须先创建被关联表

　　　　　　2. 插入数据的时候 也必须先插入被关联表的数据

 

　　三种外键关系都是用 foreign key，区别在于 如何使用 以及 其他条件限制 即可做出三种关系

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

 

1.表与表之间的三种关系

 

1.1 一对一     外键字段 建在 任意一方 都可以   但是推荐你建在查询频率较高的一方

　　

　　使用场景：表特别庞大的时候，可以考虑拆分表

　　关联表 内的一条记录 唯一对应  被关联表  的一条记录，反之也一样

 　  创建表：先建被关联的表 ！！！

　　　　　　被关联表

　　　　　　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    级联更新
　　　　　　　　　 );

　　　　　　　　

 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
　　　　　　　　　　);

　　　　　　　　

1.3  多对多     多对多   建在第三张表

　　关联表 内的一条记录 对应被关联表  的多条记录，被关联表 内的一条记录 对应关联表  的多条记录　

　　多对多关系的建立，必须手动创建 第三张表，用来专门记录两张表之间的关系

　　创建表：先创两张表，再创被关联表　　

　　　　　　被关联表　　　　　  

　　　　　　create table author(
   　　　　　　　 id int primary key auto_increment,
    　　　　　　　name char(16)
　　　　　　　　　　);

 

　　　　　　create table book(
    　　　　　　　id int primary key auto_increment,
    　　　　　　　name char(16),
    　　　　　　　price int
　　　　　　　　　 );

 

 

　　　　　　create table author_book(
   　　　　　　　 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
　　　　　　　　　);

　　　　　　　　

 

2. 修改表的完整语句

2.1 修改表名 

　　

　　alter table 表名 rename 新表名;

2. 2 增加字段
      

　　alter table 表名 add 字段名 数据类型[完整性约束条件...],

　　　　　　            add 字段名 数据类型[完整性约束条件...];

       alter table 表名 add 字段名 数据类型[完整性约束条件...]  first;                  # 直接移到最前面

       alter table 表名 add 字段名 数据类型[完整性约束条件...] after 字段名;       # 寻找插哪个字段的后面                   

 

2.3 删除字段
     

　　alter table 表名 drop 字段名;

 

2. 4 修改字段 

      alter table 表名 modify 字段名 数据类型[完整约束条件...];

           　　 # modify 只能改字段数据类型完整约束，不能改字段名，但是change可以！

      alter table 表名 change 旧字段名 新字段名 新数据类型[完整约束条件...];
         

 

 

 3. 复制表

　　

　　查询语句执行的结果是 虚拟表 ：在内存中的虚拟表

 

　　复制表结构＋记录 （key不会复制；包括 主键、外键和索引）  只复制表结构

　　　　语句：create table 新表名 select * from 旧表名;