六，MySQL之表关系

凡梦_leo

已于 2024-07-21 14:12:54 修改

阅读量675

点赞数 25

分类专栏： SQL 文章标签： mysql 数据库大数据后端 sql 前端 python

于 2024-06-20 16:49:19 首次发布

本文链接：https://blog.csdn.net/qq_55006020/article/details/139833124

版权

SQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

很高兴您打开了本篇博客
主要作用：通过学习和复习，整理mysql笔记，为前端/后端/大数据项目铺设基石

一，数据库概叙，与MySql的环境图文安装步骤_nosql-CSDN博客

二，SQL数据类型_int sql格式-CSDN博客

三，MYSQL-数据库定义语言 DDL（Data Definition Language）-CSDN博客

四，MySql数据库之表关系与外键约束-CSDN博客

五，MySQL 数据库操作语言DML(Data Manipulation Language)-CSDN博客

本篇文档是SQL专栏的第六篇
主要内容：MySQL 数据库表关系，一对多关系，多对多关系，一对一关系，自关联关系，多对多自关联
学习：知识的初次邂逅
复习：知识的温故知新

一，一对一表关系（One-To-One）

二，一对多表关系(One-To-One)

三，多对多表关系（Many- To -Many）

四，自关联表关系(One-TO-Self)

五，多对多自关联

关系型数据库SQL
常见的表关系分为三种一对多，多对多，一对一；
特殊的表结构自关联，多对多自关联；

一，一对一表关系（One-To-One）

应用场景：

一对一关系     
一个人有一个唯一的社保卡号。     建立2个表  一个表有id 有人名    一个表示主键关联a表的id  有社保号   然后2个表 建立一对一的关联  建立再那边都可以  
每个驾驶员有一个对应的驾驶证。    建立2个表 一个有个id 有驾驶员名   b表的主键和a表id建立关联关系  其次列驾驶证号   
每本书有一个唯一的ISBN（国际标准书号）。  同上
每架飞机有一个唯一的注册号码。
每位公民有一个唯一的身份证号码。
一个通讯录有 有id 有联系人姓名，一个表有联系人详细信息，电话，地址，通话记录等；

由上可见，一对一关系通常应用于，外表用来存储通用信息，内表用来存储私密信息。

代码

#表一
create table user(
    id int primary key auto_increment,
    name varchar(30)
)

#表二
create table user2(
    id int primary key auto_increment,
    psd varchar(20),
    tel varchar(20),
    ……
    foreign key(id) references user(id)
)

核心代码

foreign key(从表主键字段或者 和主表主键是同类型的字段) references 主表(id)

二，一对多表关系(One-To-One)

常见的场景:

一对多关系
一个公司有多个员工。 a表有id 有公司名称 b表有id 有员工有a_id（员工关联的主表公司的id）
一个班级有多个学生。
一个图书馆有多个书籍。 a表有id 有图书馆名称 b表有id 有书籍名称有p_id 书籍名称关联的是图书馆的id
一个家庭有多个孩子。
一个医院有多个病房。 a表有 id 有医院名称 b表有id 病房科室名称对应的是哪个医院的id

由上可见，一对多关系太常见，再我们生活中比比皆是。也是我们最常见的数据结构，没有之一。多对多其实也是2个一对多。

代码

#主表   一的一方
create table 图书馆(
    id int primary key auto_increment,
    name varchar(20),
    ……
);

#从表  多的一方
create table 书籍(
    id int primary key auto_increment,
    name varchar(20),
    ……
    图_id int,
    foreign key(图_id) references 图书馆(id)
);

核心代码

foreign key(从表的字段名) references 主表(通常是主键id)

三，多对多表关系（Many- To -Many）

多对多关系常见的应用场景：

1，学生们可以选择多门课程，而每门课程也可能被多个学生选择。

学生表有id 有学生名称
课程表有id 有课程名称再课程表里面建立多对多关系且正确的指向我们自定义的三方表
自定义的三方表有id 有学生id 有课程id

2，作家可以写多本书，每本书也可以由多个作家合作完成。

作家表：有id 有作者
书籍表有id 有书名建立多对多关系，并且指定我们第三方表
三方表有id 有作者id 有书名id 多个书名id可以指向同一个作者id 多个作者id可以指向同一个书名id

3，播放列表

一个用户播放列表里面有多个歌曲，一个歌曲可以被多个用户收录；
用户表有id 有name
歌曲表有id 有name 有作者，有连接
三方表有id 有用户id 有歌曲表id

4，顾客可以在多个商店购物，商店也可以有多个顾客。

顾客表有id 有顾客名
商店表有id 有商店名建立多对多关系并且指定我们的第三方表
三方表有id 有顾客id 有商店id 多个顾客id可以再同一个商店id里面一个商店id里面也有多个顾客的id

5，演员可以出演多部电影，电影也可以有多个演员。

6，社交媒体用户可以关注多个用户，同时他们也可以被多个用户关注。

代码：

#表1 用户表
create table user(
    id int primary key auto_increment,
    name varchar(20),
    ……
);
#表2 歌曲目录表
create table miusc(
    id int primary key auto_increment,
    name varchar(20),
    ……
);
#三方表
create table t_miusc_t_user(
    id int primary key auto_increment,
    u_id int,
    m_id int,
    foreign key(u_id) references user(id),
    foreign key(m_id) references miusc(id)
)

核心代码

建立一个三方表
三方表 和前面的2个表都是一堆多的关系  
本表的外键 必须和主键 是同类型 
foreign key(本表的字段1) references 主表1(通常是主表的主键字段)，
foreign Key(本表的字段2) references 主表2（通常是主表的主键字段）

四，自关联表关系(One-TO-Self)

这个是一个特殊的表结构自己的一个字段链接自己的主键

一对多自关联
一个文件夹下可以有多个子文件夹，这些子文件夹又可以有自己的子文件夹，形成了文件夹的树状结构。
通常级联关系的是我们会用到自关联
a表有id 有文件夹名称有文件夹等级有pid
其中pid 某个子文件夹属于哪个母文件夹，
一个分类可以有多个子分类，子分类又可以继续有子分类。

一个省下面有多个市市下面有多个区，区下面有多个县，

代码

#自关联表格
create table addr(
    id int primary key auto_increment,
    name varchar(20),
    p_id int,
    foreign key(p_id) references addr(id)
);

表结构展示：

这是一个需要很少代码就可以构建一个一对多的关系的自关联表。

五，多对多自关联

这种表设计用于及其的特殊情况

比如好友列表
多对多关系朋友之间也存在双向好友，单向好友
a表有id 人名 a表建立多对多自关联
b表有id 有从人名到人名例如人名a和人名c是好友人名b和人名c也是好友

代码案例

用户表
CREATE TABLE Users (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    name VARCHAR(255) NOT NULL UNIQUE,  
    -- 其他用户信息字段...  
);


朋友表
CREATE TABLE Friendships (  
    F_ID INT PRIMARY KEY AUTO_INCREMENT,  
    U_ID1 INT NOT NULL,  
    U_ID2 INT NOT NULL,  
    -- 其他关系信息字段...  
    FOREIGN KEY (U_ID1) REFERENCES Users(ID),  
    FOREIGN KEY (U_ID2) REFERENCES Users(ID),  
    UNIQUE (UserID1, UserID2) -- 确保不会有重复的好友关系记录（A是B的好友和B是A的好友是同一关系）  
);