MySQL多表操作(一对一 一对多 多对多)

　　参考：https://blog.csdn.net/zuosixiaonengshou/article/details/53011452

　　一，1对1

　　1方建主表(id为主键字段)， 一方或多方建外键字段（参考主表的主键id，加unique）

　　示例：一个女人（woman）对应一个丈夫(man)不能对应多个丈夫，一个丈夫也不能对应多个女人，妻子

　　创建man表（建表前创建一个test库）

 　　创建woman表

?
1
2
3
4
5
6
mysql> create table woman(
id varchar(32) primary key ,
name varchar(30),
husband varchar(32) unique,
constraint wm_fk foreign key(husband) references man(id)
);

 　　建表语句解析

?
1
2
unique # 设置约束 才是1对1否则为1对多
constraint wm_fk foreign key(husband) references man(id) #创建外键名为wm_fk 本表字段husband关联表man的id字段

 　　查看建表语句

 

 　　一一对应关系

 

 　　插入数据

　　首先插入3个男人

?
1
2
3
4
5
6
7
8
mysql> insert into man values('1', '小明');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into man values('2', '小聪');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into man values('3', '老王');
Query OK, 1 row affected (0.00 sec)

 

 

 　　插入女人并设置对应丈夫关系

?
1
2
3
4
5
mysql> insert into woman values('1', '小花', 2);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into woman values('2', '小静', 1);
Query OK, 1 row affected (0.00 sec)

 　　以下插入报错

 

 　　husband可以为空代表为单身狗

?
1
mysql> insert into woman values('3', '小红', null);

 　　查看数据

 　　查询夫妻信息

?
1
mysql> SELECT man.name AS 丈夫, woman.name AS 妻子 FROM man INNER JOIN woman ON man.id=woman.husband;

 　　当man.id和woman.huaband相同时查询数据即显示丈夫和妻子对应信息

 　　查询语句解析

?
1
2
3
SELECT man.name AS 丈夫, woman.name AS 妻子 # 把表man的name字段以丈夫显示 woman的name字段以妻子显示
FROM man INNER JOIN woman # 内联查询查询两个表有值相同的字段
ON man.id=woman.husband;    # 设置查询条件即woman的husband字段和man的id字段相同的则满足条件即夫妻的一对一关系

  　　查询小花的丈夫是谁

?
1
mysql> SELECT man.name AS 丈夫, woman.name AS 妻子 FROM man INNER JOIN woman ON woman.name='小花' and man.id=woman.husband;

 　　注意：需要加and同时满足条件man.id=woman.husband 否则会在表man查询出3跳数据

 

 

 　　二，1对多

　　1方建主表(id为主键字段)， 一方或多方建外键字段（参考主表的主键id，不加unique）

　　创建人员表

?
1
2
3
4
5
CREATE TABLE `person2` (
  `id` varchar(32) primary key,
  `name` varchar(30),
  `sex` char(1),
);

　　创建对应汽车表,外键为pid连接表person2的主键id 外键未加unique参数代表一个car可以对应多个person即多辆汽车可以对应1个人即一个人可以拥有多辆汽车

?
1
2
3
4
5
6
7
REATE TABLE `car` (
  `id` varchar(32) PRIMARY KEY,
  `name` varchar(30),
  `price` decimal(10,2),
  `pid` varchar(32) ,
  CONSTRAINT `car_fk` FOREIGN KEY (`pid`) REFERENCES `person2` (`id`)
)

 　　插入数据 

　　首先插入人员数据

?
1
2
3
4
5
6
7
8
mysql> insert into person2 values('P01', 'Jack', 1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into person2 values('P02', 'Tom', 1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into person2 values('P03', 'Rose', 0);
Query OK, 1 row affected (0.01 sec)

 

 

 　　插入汽车数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# C001 002 003属于人P01
mysql> insert into car values('C001', 'BMW', 30, 'P01');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into car values('C002', 'BEnZ', 40, 'P01');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into car values('C003', 'Audi', 40, 'P01');
Query OK, 1 row affected (0.00 sec)
# C004属于人员P02
mysql> insert into car values('C004', 'QQ', 5.5, 'P02');
Query OK, 1 row affected (0.00 sec)
 
# 也可以插入两辆汽车不属于任何人
mysql> insert into car values('C005', 'ABC', 10, null);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into car values('C006', 'BCD', 10, null);
Query OK, 1 row affected (0.44 sec)

 

 

 　　查询那些人有那些车

?
1
2
3
4
5
6
7
8
9
10
mysql> select person2.name, car.name from person2 inner join car on person2.id=car.pid;
+------+------+
| name | name |
+------+------+
| Jack | BMW  |
| Jack | BEnZ |
| Jack | Audi |
| Tom  | QQ   |
+------+------+
4 rows in set (0.00 sec)

 　　查询Jack有哪些车

?
1
2
3
4
5
6
7
8
9
10
mysql> select person2.name, car.name from person2 inner join car on person2.id=car.pid and person2.name='Jack';
 
+------+------+
| name | name |
+------+------+
| Jack | BMW  |
| Jack | BEnZ |
| Jack | Audi |
+------+------+
3 rows in set (0.00 sec)

 　　注意：这里条件也可以使用where

?
1
2
3
4
5
6
7
8
9
mysql> select person2.name, car.name from person2 inner join car on person2.id=car.pid where person2.name='Jack';
+------+------+
| name | name |
+------+------+
| Jack | BMW  |
| Jack | BEnZ |
| Jack | Audi |
+------+------+
3 rows in set (0.00 sec)

 　　查询谁有两辆及两辆以上的汽车

?
1
2
3
4
5
6
7
8
9
mysql> SELECT person2.name, car.NAME,car.price  FROM car   INNER JOIN person2 ON car.pid=person2.id WHERE personn2.id IN(      SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2   );
+------+------+-------+
| name | NAME | price |
+------+------+-------+
| Jack | BMW  | 30.00 |
| Jack | BEnZ | 40.00 |
| Jack | Audi | 40.00 |
+------+------+-------+
3 rows in set (0.00 sec)

 　　其中语句把两辆汽车以上的pid取到，然后在使用person2.id进行匹配

?
1
SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2;

 

 

 　　演示左关联

?
1
2
3
4
5
6
7
8
9
10
11
mysql> select * from person2 left join car on car.pid=person2.id;
+-----+------+------+------+------+-------+------+
| id  | name | sex  | id   | name | price | pid  |
+-----+------+------+------+------+-------+------+
| P01 | Jack | 1    | C001 | BMW  | 30.00 | P01  |
| P01 | Jack | 1    | C002 | BEnZ | 40.00 | P01  |
| P01 | Jack | 1    | C003 | Audi | 40.00 | P01  |
| P02 | Tom  | 1    | C004 | QQ   |  5.50 | P02  |
| P03 | Rose | 0    | NULL | NULL |  NULL | NULL |
+-----+------+------+------+------+-------+------+
5 rows in set (0.00 sec)

 　　左关联得到左边表全部数据以及满足某一条件的右边表数据，如果不存在则填充null

　　由全表可知只需条件是car.id或NAME或price或pid为空即可查出谁没有车

　　查询那些人没有车

?
1
2
3
4
5
6
7
mysql> select person2.name from person2 left join car on car.pid=person2.id where car.name is null;
+------+
| name |
+------+
| Rose |
+------+
1 row in set (0.00 sec)

 　　其实右关联跟左关联一样，只需要把左关联的表调换一下位置便成了右关联的结果，所以只要会了左关联，右关联也是一样的。

　　使用右连接查询那些车没有卖出去

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select * from person2 right join car on car.pid=person2.id where person2.id is null;
+------+------+------+------+------+-------+------+
| id   | name | sex  | id   | name | price | pid  |
+------+------+------+------+------+-------+------+
| NULL | NULL | NULL | C005 | ABC  | 10.00 | NULL |
| NULL | NULL | NULL | C006 | BCD  | 10.00 | NULL |
+------+------+------+------+------+-------+------+
2 rows in set (0.00 sec)
 
mysql> select car.name from person2 right join car on car.pid=person2.id where person2.id is null;
+------+
| name |
+------+
| ABC  |
| BCD  |
+------+
2 rows in set (0.00 sec)

 　　

　　三， 多对多

　　两个实体都建成独立的主表, 另外再单独建一个关系表（采用联合主键）

　　1、分别建议两个实体表(没有外键，但有自己的主键, 没有冗余信息)

?
1
2
3
4
5
6
7
8
9
10
11
//DROP TABLE stud;
//学生表
CREATE TABLE stud(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30)
);
//课程表
CREATE TABLE ject(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30)
);

 　　2，另外补建一个关系表

?
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE sj(
  studid VARCHAR(32) NOT NULL,
  jectid VARCHAR(32)
);
//注意，要先建联合主键，再添加外键。顺序不能反了。
ALTER TABLE sj ADD CONSTRAINT sj_pk PRIMARY KEY(studid,jectid);
ALTER TABLE sj ADD CONSTRAINT sj_fk1 FOREIGN KEY(studid) REFERENCES stud(id);
ALTER TABLE sj ADD CONSTRAINT sj_fk2 FOREIGN KEY(jectid) REFERENCES ject(id);
 
//删除外键约束
//ALTER TABLE sj DROP FOREIGN KEY sj_fk1;
//ALTER TABLE sj DROP FOREIGN KEY sj_fk2;

 　　

 

 　　3，添加一些演示数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//实体表1
INSERT INTO stud VALUES('S001','Jack');
INSERT INTO stud VALUES('S002','Rose');
INSERT INTO stud VALUES('S003','Tom');
 
//实体表2
INSERT INTO ject VALUES('J001','Java');
INSERT INTO ject VALUES('J002','Oracle');
INSERT INTO ject VALUES('J003','XML');
INSERT INTO ject VALUES('J004','JSP');
INSERT INTO ject VALUES('J005','Game');
 
//关系表
INSERT INTO sj VALUES('S001','J001');
INSERT INTO sj VALUES('S001','J003');
INSERT INTO sj VALUES('S001','J004');
INSERT INTO sj VALUES('S002','J002');
INSERT INTO sj VALUES('S002','J003');
INSERT INTO sj VALUES('S002','J004');

 　　查询那些人选了那些课

　　SQL组织的1992标准，可用，但效率不高

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT stud.name, ject.NAME FROM stud,ject,sj WHERE stud.id=sj.studid AND ject.id=sj.jectid;
+------+--------+
| name | NAME   |
+------+--------+
| Jack | Java   |
| Jack | XML    |
| Jack | JSP    |
| Rose | Oracle |
| Rose | XML    |
| Rose | JSP    |
+------+--------+
6 rows in set (0.00 sec)

 　　//SQL组织的1996标准，效率高，推荐使用---关联

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT stud.name, ject.NAME FROM stud INNER JOIN sj ON stud.id=sj.studid                                       INNER JOIN ject ON ject.id=sj.jectid;
+------+--------+
| name | NAME   |
+------+--------+
| Jack | Java   |
| Jack | XML    |
| Jack | JSP    |
| Rose | Oracle |
| Rose | XML    |
| Rose | JSP    |
+------+--------+
6 rows in set (0.00 sec)

 　　查询那些人没有选课

　　首先使左连接查询学生表和关系表对应表

?
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from stud left join sj on stud.id=sj.studid;
+------+------+--------+--------+
| id   | name | studid | jectid |
+------+------+--------+--------+
| S001 | Jack | S001   | J001   |
| S001 | Jack | S001   | J003   |
| S001 | Jack | S001   | J004   |
| S002 | Rose | S002   | J002   |
| S002 | Rose | S002   | J003   |
| S002 | Rose | S002   | J004   |
| S003 | Tom  | NULL   | NULL   |
+------+------+--------+--------+
7 rows in set (0.00 sec)

 　　当学生表对应的关系表stuid或者jectid为空则代表该学生未选择任何课程

　　即可知道学生Tom未选择任何课程

?
1
2
3
4
5
6
7
mysql> select * from stud left join sj on stud.id=sj.studid where sj.studid is NULL;
+------+------+--------+--------+
| id   | name | studid | jectid |
+------+------+--------+--------+
| S003 | Tom  | NULL   | NULL   |
+------+------+--------+--------+
1 row in set (0.00 sec)

 　　同理，查询那些课程没有人选

?
1
2
3
4
5
6
7
mysql> select * from ject left join sj on ject.id=sj.jectid where sj.studid is null;
+------+------+--------+--------+
| id   | name | studid | jectid |
+------+------+--------+--------+
| J005 | Game | NULL   | NULL   |
+------+------+--------+--------+
1 row in set (0.00 sec)

 



 

 

 　　

 

 

　　

posted @   minseo  阅读(3869)  评论(0)  编辑  收藏  举报
编辑推荐：
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
历史上的今天：
2019-01-23 使用Zabbix监控RabbitMQ消息队列
2019-01-23 Jenkins设置备份
点击右上角即可分享
微信分享提示
AI IDE Trae