MySQL总结(八)数据库表与表之间的关系-详解

朱雀 2023-02-24 11:17 227阅读 0赞

[精选30+云产品，助力企业轻松上云！>>> ![hot3.png][]][30_ _hot3.png]

![up-a344f41e81d3568e3310b5da00c57ced8ea.png][]

# 表与表之间的关系 #

## 1.表关系的概念 ##

> 在现实生活中,实体与实体之间肯定是有关系的。比如：员工和部门，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系。

<table> 
 <thead> 
  <tr> 
   <th>表关系</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>一对一</td> 
   <td>相对使用比较少。如：员工表，简历表，护照表。</td> 
  </tr> 
  <tr> 
   <td>一对多</td> 
   <td>最常用的表关系。如：部门和员工。</td> 
  </tr> 
  <tr> 
   <td>多对多</td> 
   <td>学生选课表 和 学生表，一门课程可以有多个学生选择，一个学生可以选择多个课程。</td> 
  </tr> 
 </tbody> 
</table>

## 2. 一对一 ##

> 一对一（1:1） ：在实际的开发中应用不多，因为一对一可以创建成一张表。

*  两种建表原则：

<table> 
 <thead> 
  <tr> 
   <th>一对一的建表原则</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>外键唯一</td> 
   <td>主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique</td> 
  </tr> 
  <tr> 
   <td>外键是主键</td> 
   <td>主表的主键和从表的主键，形成主外键关系</td> 
  </tr> 
 </tbody> 
</table>

![up-e5f60cd1ee1126d12c3c3e3d372009675af.png][]

![up-401687110fac83970eecf140315357d2cb2.png][]

## 3. 一对多 ##

> 一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品

*  建表原则:

在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

![up-1c19eeed8401f2ea6c3afe61a353ad557bd.png][]

### 3.1 一对多关系案例 ###

#### 3.1.1 需求：一个旅游线路分类中有多个旅游线路 ####

*  界面：

![up-594f4a429ff20ec9f1afc73809844155c40.png][]

*  表与表的关系：

![up-d2d6cca3e5bb395655acc9c7bb4c54649d8.png][]

![up-5b07917ceeac2af55138bfc5b29b74726e4.png][]

#### 3.1.2 具体操作 ####

-- 创建旅游线路分类表 tab_category
    -- cid 旅游线路分类主键，自动增长
    -- cname 旅游线路分类名称非空，唯一，字符串 100
    create table tab_category (
      cid int primary key auto_increment,
      cname varchar(100) not null unique
    )
    -- 添加旅游线路分类数据：
    insert into tab_category (cname) values ('周边游'), ('出境游'), ('国内游'), ('港澳游');
    select * from tab_category;
    -- 创建旅游线路表 tab_route
    /*
    rid 旅游线路主键，自动增长
    rname 旅游线路名称非空，唯一，字符串 100
    price 价格
    rdate 上架时间，日期类型
    cid 外键，所属分类
    */
    create table tab_route(
      rid int primary key auto_increment,
      rname varchar(100) not null unique,
      price double,
      rdate date,
      cid int,
      foreign key (cid) references tab_category(cid)
    )
    -- 添加旅游线路数据
    INSERT INTO tab_route VALUES(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,'2018-01-27', 1),
    (NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3),
    (NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】', 1699, '2018-01-27', 2),
    (NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',2),
    (NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);
    
    select * from tab_route;

## 4. 多对多 ##

> 多对多（m:n） 例如：老师和学生，学生和课程，用户和角色

*  建表原则:

多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

![up-cc302a8bb6dbe862b3268f75896e690ab00.png][]

### 4.1 多对多关系案例 ###

#### 4.1.1 需求：一个用户收藏多个线路，一个线路被多个用户收藏 ####

*  界面:

![up-22136a69e569a7beacc0824189f3c77337d.png][]

*  表与表的关系:

![up-e8dacb96c3b641b3d7d702ebc600f3be897.png][]

对于多对多的关系我们需要增加一张中间表来维护他们之间的关系

![up-9bd48be97c230a2c215494d5af654d77237.png][]

#### 4.1.2 具体操作: ####

/*
    创建用户表 tab_user
    uid 用户主键，自增长
    username 用户名长度 100，唯一，非空
    password 密码长度 30，非空
    name 真实姓名长度 100
    birthday 生日
    sex 性别，定长字符串 1
    telephone 手机号，字符串 11
    email 邮箱，字符串长度 100
    */
    create table tab_user (
      uid int primary key auto_increment,
      username varchar(100) unique not null,
      password varchar(30) not null,
      name varchar(100),
      birthday date,
      sex char(1) default '男',
      telephone varchar(11),
      email varchar(100)
    )
    -- 添加用户数据
    INSERT INTO tab_user VALUES
    (NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
    (NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
    
    select * from tab_user;
    /*
    创建收藏表 tab_favorite
    rid 旅游线路 id，外键
    date 收藏时间
    uid 用户 id，外键
    rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
    */
    create table tab_favorite (
      rid int,
      date datetime,
      uid int,
      -- 创建复合主键
      primary key(rid,uid),
      foreign key (rid) references tab_route(rid),
      foreign key(uid) references tab_user(uid)
    )
    -- 增加收藏表数据
    INSERT INTO tab_favorite VALUES
    (1, '2018-01-01', 1), -- 老王选择厦门
    (2, '2018-02-11', 1), -- 老王选择桂林
    (3, '2018-03-21', 1), -- 老王选择泰国
    (2, '2018-04-21', 2), -- 小王选择桂林
    (3, '2018-05-08', 2), -- 小王选择泰国
    (5, '2018-06-02', 2); -- 小王选择迪士尼
    
    select * from tab_favorite;

## 5. 表与表之间的关系小结 ##

<table> 
 <thead> 
  <tr> 
   <th>表与表的关系</th> 
   <th>关系的维护</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>一对一</td> 
   <td>1) 特殊一对多,从表中的外键设置为唯一。2）从表中的主键，也是外键</td> 
  </tr> 
  <tr> 
   <td>一对多</td> 
   <td>主外键的关系</td> 
  </tr> 
  <tr> 
   <td>多对多</td> 
   <td>中间表，两个一对多</td> 
  </tr> 
 </tbody> 
</table>

## OK,这篇就到这里 ##

[hot3.png]: /images/20230209/f8e164a2c8594a7d93e638a69a8b3430.png
[30_ _hot3.png]: https://www.oschina.net/action/visit/ad?id=1221
[up-a344f41e81d3568e3310b5da00c57ced8ea.png]: /images/20230209/9e3ba6bb293f4ef5b69dabf1fa6ce1be.png
[up-e5f60cd1ee1126d12c3c3e3d372009675af.png]: /images/20230209/2b128c1936e747b39e86ce0113c2ec91.png
[up-401687110fac83970eecf140315357d2cb2.png]: /images/20230209/527a0f7923be4671821db4d93f076414.png
[up-1c19eeed8401f2ea6c3afe61a353ad557bd.png]: /images/20230209/ef0130b0c2c34ab98e1333ac64a19c39.png
[up-594f4a429ff20ec9f1afc73809844155c40.png]: https://oscimg.oschina.net/oscnet/up-594f4a429ff20ec9f1afc73809844155c40.png
[up-d2d6cca3e5bb395655acc9c7bb4c54649d8.png]: https://oscimg.oschina.net/oscnet/up-d2d6cca3e5bb395655acc9c7bb4c54649d8.png
[up-5b07917ceeac2af55138bfc5b29b74726e4.png]: https://oscimg.oschina.net/oscnet/up-5b07917ceeac2af55138bfc5b29b74726e4.png
[up-cc302a8bb6dbe862b3268f75896e690ab00.png]: https://oscimg.oschina.net/oscnet/up-cc302a8bb6dbe862b3268f75896e690ab00.png
[up-22136a69e569a7beacc0824189f3c77337d.png]: https://oscimg.oschina.net/oscnet/up-22136a69e569a7beacc0824189f3c77337d.png
[up-e8dacb96c3b641b3d7d702ebc600f3be897.png]: https://oscimg.oschina.net/oscnet/up-e8dacb96c3b641b3d7d702ebc600f3be897.png
[up-9bd48be97c230a2c215494d5af654d77237.png]: https://oscimg.oschina.net/oscnet/up-9bd48be97c230a2c215494d5af654d77237.png