mysql中表与表之间的关系

1.如何分析表与表之间的关系

分析步骤:
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id#3、总结:
#多对一:
如果只有步骤1成立,则是左表多对一右表
如果只有步骤2成立,则是右表多对一左表

#多对多
如果步骤12同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果12都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可

2.mysql中表与表之间的关系

2.1 一对多

关联方式

  • 外键 foreign key

表与表之间的关系为一对多

例如,出版社与书之间的关系就是一对多,一个出版社可以出版多个书

例如,班级表和学生表,一个班级可以有多个学生,但是一个学生只能属于一个班级

例如,服务器和机房,一个机房可以有多台服务器,但是一个服务器只能属于一个机房

2.1.1示例1,出版社表与图书表

一个出版社可以出版多个书

#创建出版社表
mysql> create table chubanshe(id int primary key auto_increment,name char(10));
Query OK, 0 rows affected (0.02 sec)

#创建图书表
mysql> create table book(
  id int primary key auto_increment,
  name char(20),
  chubanshe_id int not null,
  foreign key(chubanshe_id) references chubanshe(id) on delete cascade on update cascade);
Query OK, 0 rows affected (0.04 sec)

//向出版社表中插入数据
mysql> insert into chubanshe(name) values
       ('人民出版社'),
       ('邮电出版社'),
       ('机械出版社');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from chubanshe;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | 人民出版社      |
|  2 | 邮电出版社      |
|  3 | 机械出版社      |
+----+-----------------+
3 rows in set (0.00 sec)


//向图书表中插入数据
mysql> insert into book(name,chubanshe_id) values
       ('童话故事',1),
       ('阿童木',2),
       ('老人与海',1),
       ('会飞的鸟',3),
       ('葵花宝典',2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from book;
+----+--------------+--------------+
| id | name         | chubanshe_id |
+----+--------------+--------------+
|  1 | 童话故事     |            1 |
|  2 | 阿童木       |            2 |
|  3 | 老人与海     |            1 |
|  4 | 会飞的鸟     |            3 |
|  5 | 葵花宝典     |            2 |
+----+--------------+--------------+
5 rows in set (0.00 sec)


现在出版社表与图书表就是一对多关系,图书表中的chubanshe_id对应出版社表中的出版社id,一个出版社可以出版多个书

2.1.2示例2,学生表和班级表

一个班级可以有多个学生,但是一个学生只能属于一个班级

#创建学生表
mysql> create table student(
       sid int primary key auto_increment,
       sname char(10) not null,
       class_id int not null,
       foreign key(class_id) references class(cid));
Query OK, 0 rows affected (0.02 sec)

#创建班级表
mysql> create table class(cid int auto_increment primary key);
Query OK, 0 rows affected (0.04 sec)


//向学生表中插入数据
mysql> insert into student(sname,class_id) values('小明',1),('小红',1),('小洲',2),('小肖',3),('小丽',3);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> select * from student;
+-----+--------+----------+
| sid | sname  | class_id |
+-----+--------+----------+
|   1 | 小明   |        1 |
|   2 | 小红   |        1 |
|   3 | 小洲   |        2 |
|   4 | 小肖   |        3 |
|   5 | 小丽   |        3 |
+-----+--------+----------+
5 rows in set (0.00 sec)   

//向班级表中插入数据,因为只有一个cid,自动增长
mysql> insert into class values();
Query OK, 1 row affected (0.01 sec)

mysql> select * from class;
+-----+
| cid |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
3 rows in set (0.00 sec)



//错误示例,向学生表中插入一条数据,班级id指定一个不存在的,会报错
mysql> insert into student(sname,class_id) values('小黑',4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ppp`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`))

//错误示例,尝试删除班级表中的一个记录,报错,不可以删除,因为学生表中有对应的班级,这个无法删除
mysql> delete from class where cid=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ppp`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`))

2.1.3示例3,机房表与服务器表

一个机房可以有多个服务器,但是一个服务器只能归属一个机房

#创建机房表
mysql> create table server_room(
       rid int primary key auto_increment,
       rname char(10) not null);
Query OK, 0 rows affected (0.03 sec)

#创建服务器表
mysql> create table server(
       sid int primary key auto_increment,
       sname char(10) not null,room_id int not null,
       foreign key(room_id) references server_room(rid));
Query OK, 0 rows affected (0.02 sec)


//向机房表中插入数据
mysql> insert into server_room(rname) values('房山机房'),('石景山机房'),('丰台 机房');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from server_room;
+-----+-----------------+
| rid | rname           |
+-----+-----------------+
|   1 | 房山机房        |
|   2 | 石景山机房      |
|   3 | 丰台机房        |
+-----+-----------------+
3 rows in set (0.00 sec)

//向服务器表中插入数据
mysql> insert into server(sname,room_id) values('HP',1),('DELL',1),('联想',2),('IBM',3),('华为',3);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from server;
+-----+--------+---------+
| sid | sname  | room_id |
+-----+--------+---------+
|   1 | HP     |       1 |
|   2 | DELL   |       1 |
|   3 | 联想   |       2 |
|   4 | IBM    |       3 |
|   5 | 华为   |       3 |
+-----+--------+---------+
5 rows in set (0.00 sec)


//错误示例,尝试修改机房表中的机房编号,结果报错,因为服务器表中有对应编号为3的丰台机房的服务器
mysql> select * from server_room;
+-----+-----------------+
| rid | rname           |
+-----+-----------------+
|   1 | 房山机房        |
|   2 | 石景山机房      |
|   3 | 丰台机房        |
+-----+-----------------+
3 rows in set (0.00 sec)

mysql> update server_room set rid=5 where rid=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ppp`.`server`, CONSTRAINT `server_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `server_room` (`rid`))

2.2 一对一

关联方式

  • foreign key+unique

表与表之间的关系是一对一

例如,公司中员工与员工的企业邮箱就是一对一关系

2.2.1示例1,员工表与企业邮箱表

#创建员工表
mysql> create table employee(
  eid int primary key auto_increment,    #员工id,主键,自增
  ename char(20) not null,    #员工姓名
  sex enum('F','M'),    #员工性别
  enterprise_mail_id int unique);    #企业邮箱id,不能重复
Query OK, 0 rows affected (0.03 sec)

#创建企业邮箱表
mysql> create table enterprise_mail(
  mid int primary key,    #企业邮箱id,主键
  email char(50),                #邮箱信息
  employee_id int unique not null,    #员工id,不能重复
  foreign key(employee_id) references employee(eid));    #企业邮箱表中的员工id是外键,关联员工表中的员工id
Query OK, 0 rows affected (0.03 sec)


//向员工表中插入数据
mysql> insert into employee(ename,sex,enterprise_mail_id) values
    -> ('小明','M',101),
    -> ('小洲','M',102),
    -> ('小颖','F',103);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+-----+--------+------+--------------------+
| eid | ename  | sex  | enterprise_mail_id |
+-----+--------+------+--------------------+
|   1 | 小明   | M    |                101 |
|   2 | 小洲   | M    |                102 |
|   3 | 小颖   | F    |                103 |
+-----+--------+------+--------------------+
3 rows in set (0.00 sec)


//向企业邮箱表中插入数据
mysql> insert into enterprise_mail values
     (101,'xiaoming@testin.cn',1),
     (102,'xiaozhou@testin.cn',2),
     (103,'xiaoying@testin.cn',3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from enterprise_mail;
+-----+--------------------+-------------+
| mid | email              | employee_id |
+-----+--------------------+-------------+
| 101 | xiaoming@testin.cn |           1 |
| 102 | xiaozhou@testin.cn |           2 |
| 103 | xiaoying@testin.cn |           3 |
+-----+--------------------+-------------+
3 rows in set (0.00 sec)


//尝试删除员工表中的任意一条数据,因为有外键约束,因此无法删除
mysql> delete from employee where eid=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`enterprise_mail`, CONSTRAINT `enterprise_mail_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`eid`))

//尝试删除企业邮箱表中的任意一条数据
mysql> delete from enterprise_mail where mid=101;
Query OK, 1 row affected (0.01 sec)

2.3 多对多

关联方式

  • 外键foreign key+一张新的表

表与表之间的关系是多对多

例如,一本书可以有多个作者,一个作者可以写多本书

iShot2020-10-14 14.06.28

2.3.1示例1,书籍表、作者表、关联书籍表与作者表的第3张表

#创建书籍表
mysql> create table book(
       bid int primary key auto_increment,
       bname char(20) not null);
Query OK, 0 rows affected (0.02 sec)

#创建作者表
mysql> create table author(
       aid int primary key auto_increment,
       aname char(20) not null);
Query OK, 0 rows affected (0.03 sec)

#创建关联书籍与作者表,表中有书籍id和作者id,分别作为书籍表中bid和作者表中aid的外键
mysql> create table book_and_author(
       book_id int not null,
       author_id int not null,
       foreign key(book_id) references book(bid),
       foreign key(author_id) references author(aid));
Query OK, 0 rows affected (0.03 sec)


//向书籍表中插入数据
mysql> insert into book(bname) values('童话故事'),('那个女孩'),('上课必备三件套');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from book;
+-----+-----------------------+
| bid | bname                 |
+-----+-----------------------+
|   1 | 童话故事              |
|   2 | 那个女孩              |
|   3 | 上课必备三件套        |
+-----+-----------------------+
3 rows in set (0.00 sec)      


//向作者表中插入数据
mysql> insert into author(aname) values('作者小明'),('作者小丽'),('作者小洲');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from author;
+-----+--------------+
| aid | aname        |
+-----+--------------+
|   1 | 作者小明     |
|   2 | 作者小丽     |
|   3 | 作者小洲     |
+-----+--------------+
3 rows in set (0.00 sec)


//向关联表中插入数据
mysql> insert into book_and_author values(1,1),(1,2),(2,2),(2,3),(3,1),(3,3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from book_and_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       1 |         1 |    #童话故事的作者是小明
|       1 |         2 |    #童话故事的作者是小丽
|       2 |         2 |    #那个女孩的作者是小丽
|       2 |         3 |    #那个女孩的作者是小洲
|       3 |         1 |    #上课必备三件套的作者是小明    
|       3 |         3 |    #上课必备三件套的作者是小洲
+---------+-----------+
6 rows in set (0.00 sec)
这个关联书籍表与作者表的关联表中就是一个作者写了多本书,一本书有多个作者


//错误示范,尝试删除书籍表与作者表中的任意一条数据
mysql> delete from book where bid=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`book_and_author`, CONSTRAINT `book_and_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`bid`))

mysql> delete from author where aid=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`book_and_author`, CONSTRAINT `book_and_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `author` (`aid`))


//删除关联表中的数据,是可以删除的,因为这只是一个关联表,删除了关联id,只是把原来两张表的关联关系删除了
这里删除了关联表中的图书id,这样就是原来的图书表中为1的书找不到对应的作者了
mysql> delete from book_and_author where book_id=1;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from book_and_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       2 |         2 |
|       2 |         3 |
|       3 |         1 |
|       3 |         3 |
+---------+-----------+
4 rows in set (0.00 sec)

//删除关联id后就可以删除原先不能删除的图书或者作者id了
mysql> delete from book where bid=1;
Query OK, 1 row affected (0.00 sec)

//关联id2的没有删除,因此不能删除图书表中bid为2的
mysql> delete from book where bid=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`book_and_author`, CONSTRAINT `book_and_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`bid`))
泡泡吐肥皂o © gitbook.pptfz.top 2021 all right reserved,powered by Gitbook文件修订时间: 秃笔南波湾!!!

results matching ""

    No results matching ""