如何找出两张表之间的关系
分析步骤:
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)
#3、总结:
#多对一:
如果只有步骤1成立,则是左表多对一右表
如果只有步骤2成立,则是右表多对一左表
#多对多
如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可
建立表之间的关系
#一对多或称为多对一
三张表:出版社,作者信息,书
一对多(或多对一):一个出版社可以出版多本书
关联方式:foreign key
两张表之间的关系
1. 多对一
出版社 书(press_id int,foreign key (preess_id) references press(id))
2. 多对多
3. 一对一
例子
#一对多或称为多对一
三张表:出版社,作者信息,书
一对多(或多对一):一个出版社可以出版多本书
关联方式:foreign key
=====================多对一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('机械工业出版社'),
('人民出版社'),
('南京出版社')
;
insert into book(name,press_id) values
('计算机基础',1),
('python入门',2),
('mysql',2),
('机械原理',3),
('十万个为什么',2),
('社会科学',3)
;
# 操作过程
mysql> create table press(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> create table book(
-> id int primary key auto_increment,
-> name varchar(20),
-> press_id int not null,
-> foreign key(press_id) references press(id)
-> on delete cascade
-> on update cascade
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into press(name) values
-> ('机械工业出版社'),
-> ('人民出版社'),
-> ('南京出版社')
-> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> insert into book(name,press_id) values
-> ('计算机基础',1),
-> ('python入门',2),
-> ('mysql',2),
-> ('机械原理',3),
-> ('十万个为什么',2),
-> ('社会科学',3)
-> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> desc press;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from book;
+----+--------------------+----------+
| id | name | press_id |
+----+--------------------+----------+
| 1 | 计算机基础 | 1 |
| 2 | python入门 | 2 |
| 3 | mysql | 2 |
| 4 | 机械原理 | 3 |
| 5 | 十万个为什么 | 2 |
| 6 | 社会科学 | 3 |
+----+--------------------+----------+
6 rows in set (0.00 sec)
mysql> select * from press;
+----+-----------------------+
| id | name |
+----+-----------------------+
| 1 | 机械工业出版社 |
| 2 | 人民出版社 |
| 3 | 南京出版社 |
+----+-----------------------+
3 rows in set (0.00 sec)
多对多关系
#多对多
三张表 : 出版社,作者信息,书
多对多 : 一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多
关联方式: foreign key+一张新的表
cmz作者的写的书
计算机基础
python入门
mysql
机械原理
十万个为什么
社会科学
leco作者的写的书
机械原理
十万个为什么
社会科学
loocha作者的写的书
十万个为什么
社会科学
odes作者的写的书
社会科学
作者和书的对应关系(多对多)
演示例子
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
create table author(
id int primary key auto_increment,
name varchar(20)
);
#这张表就存放作者表与书表的关系,即查询二者的关系查这表就可以了
create table author2book(
id int not null unique 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,
primary key(author_id,book_id)
);
insert into author(name) values('cmz'),('leco'),('loocha'),('odes');
insert into book(name,press_id) values
('计算机基础',1),
('python入门',2),
('mysql',2),
('机械原理',3),
('十万个为什么',2),
('社会科学',3)
;
# 插入作者的和对应的书也就是插入到author2book表中
cmz作者的写的书
计算机基础
python入门
mysql
机械原理
十万个为什么
社会科学
leco作者的写的书
机械原理
十万个为什么
社会科学
loocha作者的写的书
十万个为什么
社会科学
odes作者的写的书
社会科学
# 插入
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(2,4),
(2,5),
(2,6),
(3,5),
(3,6),
(4,6);
解释:
括号内的左侧表示作者的id,右侧表示书的id.
(4,6) 表示,odes该作者对应的社会科学书籍
"""
mysql> select * from author;
+----+--------+
| id | name |
+----+--------+
| 1 | cmz |
| 2 | leco |
| 3 | loocha |
| 4 | odes |
+----+--------+
4 rows in set (0.00 sec)
mysql> select * from book;
+----+--------------------+----------+
| id | name | press_id |
+----+--------------------+----------+
| 1 | 计算机基础 | 1 |
| 2 | python入门 | 2 |
| 3 | mysql | 2 |
| 4 | 机械原理 | 3 |
| 5 | 十万个为什么 | 2 |
| 6 | 社会科学 | 3 |
+----+--------------------+----------+
6 rows in set (0.00 sec)
"""
# 操作过程
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table author2book(
-> id int not null unique 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,
-> primary key(author_id,book_id)
-> );
Query OK, 0 rows affected (0.04 sec)
# 插入作者
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| author |
| author2book |
| book |
| dep |
| emp |
| press |
| student |
| t1 |
+---------------+
8 rows in set (0.00 sec)
mysql> select * from author;
Empty set (0.00 sec)
mysql> insert into author(name) values('cmz'),('leco'),('loocha'),('odes');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from author;
+----+--------+
| id | name |
+----+--------+
| 1 | cmz |
| 2 | leco |
| 3 | loocha |
| 4 | odes |
+----+--------+
4 rows in set (0.00 sec)
mysql> desc author2book;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | UNI | NULL | auto_increment |
| author_id | int(11) | NO | PRI | NULL | |
| book_id | int(11) | NO | PRI | NULL | |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 2 | 4 |
| 7 | 2 | 5 |
| 8 | 2 | 6 |
| 9 | 3 | 5 |
| 10 | 3 | 6 |
| 11 | 4 | 6 |
+----+-----------+---------+
11 rows in set (0.00 sec)
一对一
#一对一
两张表:学生表和客户表
一对一:一个学生是一个客户,一个客户有可能变成一个学校,即一对一的关系
关联方式:foreign key+unique
#一定是student来foreign key表customer,这样就保证了:
#1 学生一定是一个客户,
#2 客户不一定是学生,但有可能成为一个学生
# 建立客户比表,让学生表关联客户表
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
);
#增加客户
insert into customer(name,qq,phone) values
('张三','31811231',13811341220),
('李四','123123123',15213146809),
('网五','283818181',1867141331),
('赵刘','283818181',1851143312),
('奥简爱','888818181',1861243314),
('凯文','112312312',18811431230)
;
#增加学生
/*insert into student(class_name,customer_id) values
('3班',3),
('19期',4),
('19期',5);*/
insert into student(class_name,customer_id) values('3期',3);
insert into student(class_name,customer_id) values('19期',4);
insert into student(class_name,customer_id) values('18期',5);
# 操作过程
mysql> use db5;
Database changed
mysql> create table customer(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(10) not null,
-> phone char(16) not null
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> 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
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+---------------+
| Tables_in_db5 |
+---------------+
| customer |
| student |
+---------------+
2 rows in set (0.00 sec)
mysql> insert into customer(name,qq,phone) values
-> ('张三','31811231',13811341220),
-> ('李四','123123123',15213146809),
-> ('网五','283818181',1867141331),
-> ('赵刘','283818181',1851143312),
-> ('奥简爱','888818181',1861243314),
-> ('凯文','112312312',18811431230)
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from customer;
+----+-----------+-----------+-------------+
| id | name | qq | phone |
+----+-----------+-----------+-------------+
| 1 | 张三 | 31811231 | 13811341220 |
| 2 | 李四 | 123123123 | 15213146809 |
| 3 | 网五 | 283818181 | 1867141331 |
| 4 | 赵刘 | 283818181 | 1851143312 |
| 5 | 奥简爱 | 888818181 | 1861243314 |
| 6 | 凯文 | 112312312 | 18811431230 |
+----+-----------+-----------+-------------+
6 rows in set (0.00 sec
mysql> insert into student(class_name,customer_id) values('3期',3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(class_name,customer_id) values('3期',3); # 不能在插入啦,一一对应
ERROR 1062 (23000): Duplicate entry '3' for key 'customer_id'
mysql> insert into student(class_name,customer_id) values('19期',4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(class_name,customer_id) values('18期',5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------------+-------------+
| id | class_name | customer_id |
+----+------------+-------------+
| 1 | 3期 | 3 |
| 3 | 19期 | 4 |
| 4 | 18期 | 5 |
+----+------------+-------------+
3 rows in set (0.01 sec)