MYSQL回顾（表关系相关）

VV木公子

发布于 2020-02-18 17:13:39

5.9K00

代码可运行

文章被收录于专栏：TechBoxTechBox

运行总次数：0

代码可运行

数据库表有三种关系：

多对一
多对多
一对一

多对一

外键在“多”侧

比如有两个表，分别是书籍表和出版社表。书籍和出版社是典型的多对一关系，即一本书只能由一个出版社出版，一个出版社可以出版多本书。则书籍表应该有一个外键press_id指向出版社表的id primary key。

建表顺序

先建被关联表（多）即出版社表，再建关联表即书籍表（一）关联表(书籍表)有一个外键press_id指向被关联表的主键（出版社id）

实践

# 先建被关联表
mysql> create table press(

    -> id int primary key,

    -> name char(40)

    -> ) auto_increment=1;

Query OK, 0 rows affected (0.05 sec)

# 再建关联表
mysql> create table book(

    -> id int primary key,

    -> name char(60) not null,

    -> press_id int,

    -> constraint fk_press foreign key(press_id) references press(id)

    -> on delete cascade

    -> on update cascade

    -> ) auto_increment=1;

Query OK, 0 rows affected (0.02 sec)

插入数据

先插入被关联表记录再插入关联表记录

mysql> insert into press(id, name) values(1, "北京邮电出版社"), (2, "中信出版社");

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into book(id, name, press_id) values

    -> (1, "计算机基础", 1),

    -> (2, "操作系统", 1),

    -> (3, "编译原理", 1),

    -> (4, "C语言", 2);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> delete from press where id=2;

Query OK, 1 row affected (0.00 sec)

mysql> select * from book;

+----+-----------------+----------+

| id | name            | press_id |

+----+-----------------+----------+

|  1 | 计算机基础      |        1 |

|  2 | 操作系统        |        1 |

|  3 | 编译原理        |        1 |

+----+-----------------+----------+

3 rows in set (0.00 sec)

多对多

多对多因为都存在外键的依赖关系，所以建表的时候不会成功。需要第三张表来建立他们的外键关系，如下：

image.png

建表顺序

先建立author表和book表，在建立author2book表 author2book表中有两个外键 author_id 和 book_id

author_id指向author表的id book_id指向book表的id

实践

mysql> create table author(

    -> id int primary key auto_increment,

    -> name char(20)

    -> );

Query OK, 0 rows affected (0.02 sec)

mysql> create table book(

    -> id int primary key auto_increment,

    -> name char(60)

    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> create table author2book(

    ->     id int primary key auto_increment,

    ->     author_id int not null,

    ->     book_id int not null,

    ->     constraint fk_author foreign key(author_id) references author(id)

    ->     on delete cascade

    ->     on update cascade,

    ->     constraint fk_book foreign key(book_id) references book(id)

    ->     on delete cascade

    ->     on update cascade

    -> );

Query OK, 0 rows affected (0.02 sec)

在author2book表中最使用联合唯一的方式指定主键，即author_id 和 book_id联合成为primary key

create table author2book2(

    id int not null unique auto_increment,

    author_id int not null,

    book_id int not null,

    foreign key(author_id) references author(id)

    on delete cascade

    on update cascade,

    foreign key(book_id) references book(id)

    on delete cascade

    on update cascade,

    primary key(author_id, book_id)

    );

建立author2book表：

可以使用constraint给外键起别名（可选）

image.png

建表时遇到以下错误，原因是最后一行不需要逗号，去掉逗号即可：

image.png

不允许存在同名的外键（因为已经在author2book表中有一个外键的名字叫做fk_author）：

image.png

mysql> insert into author(name) value("jack"),("mark");

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into book(name) value("操作系统"),("C语言");

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from author;

+----+------+

| id | name |

+----+------+

|  1 | jack |

|  2 | mark |

+----+------+

2 rows in set (0.00 sec)

mysql> select * from book;

+----+--------------+

| id | name         |

+----+--------------+

|  1 | 操作系统     |

|  2 | C语言        |

+----+--------------+

2 rows in set (0.00 sec)

mysql> insert into author2book(author_id, book_id)

    -> value(1, 1),(2,2)

    -> ;

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from author2book;

+----+-----------+---------+

| id | author_id | book_id |

+----+-----------+---------+

|  1 |         1 |       1 |

|  2 |         2 |       2 |

+----+-----------+---------+

2 rows in set (0.00 sec)

mysql> delete from author2book where author_id=1 and book_id=1;

Query OK, 1 row affected (0.00 sec)

mysql> select * from author2book;

+----+-----------+---------+

| id | author_id | book_id |

+----+-----------+---------+

|  2 |         2 |       2 |

+----+-----------+---------+

1 row in set (0.00 sec)

mysql> select * from author;

+----+------+

| id | name |

+----+------+

|  1 | jack |

|  2 | mark |

+----+------+

2 rows in set (0.00 sec)

mysql> select * from book;

+----+--------------+

| id | name         |

+----+--------------+

|  1 | 操作系统     |

|  2 | C语言        |

+----+--------------+

2 rows in set (0.00 sec)

可见，在多对多关系中，删除author2book表中的记录后，author表和book表的记录并没有删除

一对一

两张表：学生表和客户表对于一些教育培训机构而言，客户即使潜在的学生，是一对一的关系
一对一：一个学生是一个客户，一个客户有可能变成一个学生，即一对一的关系
关联方式：foreign key+unique
对于多对一的关系而言：关联表中的外键无需指定为unique，因为存在多个记录的外键指向被关联表的同一个记录
但对于一对一的关系而言：为了保证两张表记录的一一对应，需要把关联表的外键约束为unique

image.png

一定是student来foreign key表customer，这样就保证了：
学生一定是一个客户，
客户不一定是学生，但有可能成为一个学生4. create table customer(

 create table customer(
 id int primary key auto_increment,
 name varchar(20) not null,
 qq varchar(10) not null,
 phone char(16) not null
 );
 create table student(
 id int primary key auto_increment,
 class_name varchar(20) not null,
 customer_id int unique, #该字段一定要是唯一的
 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
 on delete cascade
 on update cascade
 );

实践：

mysql> create table customer(

    -> id int primary key AUTO_INCREMENT,

    -> name char(20)

    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> create table student(

    -> id int primary key AUTO_INCREMENT,

    -> name char(20),

    -> cus_id int unique,

    -> foreign key(cus_id) references customer(id)

    -> on delete cascade

    -> on update cascade

    -> );

Query OK, 0 rows affected (0.01 sec)

和多对一一样，更新或删除时：

删除被关联表的记录，关联表的相关记录也会被删除删除关联表的记录，被关联表的相关记录不会被删除

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

image