MySQL数据库之-foreign key 外键(一对多、多对…

2019-05-16 23:58:37来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

今日重点:外键 一对多

        多对多

           一对一

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

一、引言:

我们在同一数据库创建的表时候,很多时候会出现相同数据的冗余问题,也就是说几个id会有一个或者同n个相同字段,这样就导致数据表结构数据重复冗余,

冗余还无所谓,关键是如果我要改其中一个相同的字段信息,其他跟他相同字段的信息也都要同步修改,这就增加了很多工作量,特别是如果要处理大量数据的时候

所以需要有一种解决方式来处理这种显而易见的问题。

例如:

id name gender dep_name dep_desc
1 jason male 外交部 形象代言人
2 egon male 教学部 教书育人
3 kevin male 教学部 教书育人
4 tank male 教学部 教书育人
5 jerry female 技术部 技术负责人

很显然,上表的id为2/3/4的人员同属于一个字段名(部门)教育部,部门描述也是一样。解决方法:拆分为2个表

+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | egon  | male   |      2 |
|  3 | kevin | male   |      2 |
|  4 | tank  | male   |      2 |
|  5 | jerry | female |      3 |
+----+-------+--------+--------+

+----+-----------+--------------------------+
| id | dep_name  | dep_desc                 |
+----+-----------+--------------------------+
|  1 | 外交部    | 形象代言人                 |
|  2 | 教育部    | 教书育人                   |
|  3 | 技术部    | 技术负责人            |
+----+-----------+--------------------------+
"""
把所有数据都存放于一张表的弊端
1.组织结构不清晰
2.浪费硬盘空间
3.扩展性极差
"""
# 上述的弊端产生原因类似于把代码全部写在一个py文件中,你应该怎么做?>>>解耦合!将上述一张表拆成员工和部门两张表!

# 分析表数据之间的关系:多个用户对应一个部门,一个部门对应多个用户。禁止一个用户对应多个部门

# 如何查找表与表之间的关系
"""
老师与课程表
1.站在老师表的角度:一名老师能否教授多门课程(限制死,不能,一名老师只能教python,不能同时教python和linux)
2.站在课程表的角度:一门课程能否可以被多个老师教,完全可以!
那就是课程表多对一老师表,如何表示这种关系?在课程表中创建一个字段(tea_id)指向老师表的id字段

学生与班级表
1.站在学生表的角度:???
2.站在班级表的角度:???
那就是学生表多对一班级表,如何表示这种关系?在学生表中创建一个字段(class_id)指向班级表的id字段
"""

二、一对多(foreign key)

 foreign key 会将本表中的一个字段与另外一个表的字段(通常是主键字段)进行关联。

在创建表时,必须先建被关联的表dep,才能建关联表emp
create database foreign_ts; # 创建数据库 use foreign_ts; # 进入数据库 # 先创建被关联表: create table dep( id int primary key auto_increment, # 设置主键,自增长 dep_name char(16), dep_desc char(16) ); # 再创建关联表emp: create table emp( id int primary key auto_increment, # 设置主键,自增长 name char(16), gender enum('male','female','others'), dep_id int, foreign key(dep_id) references dep(id) # 外键 关联 on update cascade # 同步更新 on delete cascade # 同步删除 ); # 插入数据:
在插入记录时,必须先插入被关联的表dep,才能插关联表emp
insert into dep(dep_name,dep_desc) values ('外交部','形象代言人'),('教育部','教书育人'),('技术部','技术能力有限部门'); insert into emp(name,gender,dep_id) values ('jason','male',1), ('egon','male',2), ('kevin','male',2), ('tank','male',2), ('jerry','female',3);

三、多对多:

一对多是A表中多个字段关联B表中一个字段。

而多对多就是在此基础上B表中也有多个字段关联A表中一个字段。

此时如果依然以上面一对多的形式去创建表格的话,就会出现互相矛盾的问题,也就是我再关联你的时候你还没有被创建的情况。

示例:

# 图书表与作者表之间的关系
"""
仍然站在两张表的角度:
1.站在图书表:一本书可以有多个作者
2.站在作者表:一个作者可以写多本书
双方都能一条数据对应对方多条记录,这种关系就是多对多!
"""
# 先来想如何创建表?图书表需要有一个外键关联作者,作者也需要有一个外键字段关联图书。问题来了,先创建谁都不合适!如何解决?
# 建立第三张表,该表中有一个字段fk左表的id,还有一个字段是fk右表的id
----------------------------------
先将2个表建起了:
mysql> create table author(                  #建作者表,id和name足以
    -> id int primary key auto_increment,
    -> name char(16)
    -> );

mysql> create table book(                    # 建书表,id、书名、价格
    -> id int primary key auto_increment,
    -> title char(16),
    -> price int
    -> );
插入表数据:
mysql> insert into author(name) values
    -> ('唐家三少'),
    -> ('金庸'),
    -> ('萧鼎')
    -> ;

mysql> insert into book(title,price) values
    ->     ('光之子',99),
    ->     ('冰火魔厨',88),
    ->     ('斗罗大陆',77),
    ->     ('倚天屠龙记',98),
    ->     ('神雕侠侣',96),
    ->     ('诛仙',100);
mysql> select* from author;    # 查看建表结果
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 唐家三少     |
|  2 | 金庸         |
|  3 | 萧鼎         |
+----+--------------+
mysql> select* from book;      # 查看建表结果
+----+-----------------+-------+
| id | title           | price |
+----+-----------------+-------+
|  1 | 光之子          |    99 |
|  2 | 冰火魔厨        |    88 |
|  3 | 斗罗大陆        |    77 |
|  4 | 倚天屠龙记      |    98 |
|  5 | 神雕侠侣        |    96 |
|  6 | 诛仙            |   100 |
+----+-----------------+-------+
--------------------------------------------------------------------------
建立第三张表,这张表作用就是关联author表和book表。
mysql> 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   # 设置外键
    -> );
# 插入关联数据
mysql> insert into book2author(book_id,author_id) values
    -> (1,1),
    -> (2,1),
    -> (3,1),
    -> (4,2),
    -> (5,2),
    -> (6,3);
# 查看结果:
mysql> select * from book2author;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         1 |
|  2 |       2 |         1 |
|  3 |       3 |         1 |
|  4 |       4 |         2 |
|  5 |       5 |         2 |
|  6 |       6 |         3 |
+----+---------+-----------+

这样就将book表和author表之间通过第三张表建立了多对多外键关联。

四、一对一:

 A表的字段与B表中的某一唯一字段形成一对一的关联

示例:

# 我们可以通过客户表和投保人之间的关系来描述一对一
保险销售人员有个客户表,在每天的不断努力经营客户之下,促进了多个客户成为了投保人,这个多个投保人作为一张投保人表,他们之间的关系可以说是一对一关系:
或者说是一种类似于集合的父集与子集的关系。
mysql> create table customer(
    -> id int primary key auto_increment,
    -> name char(16),
    -> age int,
    -> sex enum('male','female','other'),
    -> wechart char(16)
    -> );

mysql> create table holder(          # 创建投保人表
    -> id int primary key auto_increment,
    -> name char(16),
    -> holder_price int,
    -> customer_id int unique,       # 将客户id设为唯一,因为客户表中客户id必须唯一
    -> foreign key(customer_id) references customer(id) on update cascade on delete cascade
    -> );   # 客户id与客户表中id进行关联。同步改删
--------------------------------------------------
插入数据:(先插入客户表(被关联),再插入投保人表)
mysql> insert into customer(name,age,sex,wechart) values
    -> ('张三丰',18,'male','17375898'),
    -> ('张小凡',16,'male','65652456'),
    -> ('陆雪琪',17,'female','62612384'),
    -> ('秦祥林',25,'male','545654655'),
    -> ('至尊宝',999,'male','66666666'),
    -> ('韦小宝',30,'male','475247124');

mysql> insert into holder(name,holder_price,customer_id) values
    -> ('张小凡',5000000,2),
    -> ('陆雪琪',10000000,3),
    -> ('韦小宝',990000,6);

# 查看结果:
mysql> select * from customer;
+----+-----------+------+--------+-----------+
| id | name      | age  | sex    | wechart   |
+----+-----------+------+--------+-----------+
|  1 | 张三丰    |   18 | male   | 17375898  |
|  2 | 张小凡    |   16 | male   | 65652456  |
|  3 | 陆雪琪    |   17 | female | 62612384  |
|  4 | 秦祥林    |   25 | male   | 545654655 |
|  5 | 至尊宝    |  999 | male   | 66666666  |
|  6 | 韦小宝    |   30 | male   | 475247124 |
+----+-----------+------+--------+-----------+
mysql> select * from holder;
+----+-----------+--------------+-------------+
| id | name      | holder_price | customer_id |
+----+-----------+--------------+-------------+
|  1 | 张小凡    |      5000000 |           2 |
|  2 | 陆雪琪    |     10000000 |           3 |
|  3 | 韦小宝    |       990000 |           6 |
+----+-----------+--------------+-------------+

五、修改表:

语法:
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;                       
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;
4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

六、复制表:

在创建表的时候 通过select * from 表名 来获取复制表的来源

 

# 复制表结构+记录 (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/suguangti/p/10863867.html
如有疑问请与原作者联系

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:windows中Python多版本与jupyter notebook中使用虚拟环境

下一篇:day06