相关文章推荐
痛苦的红金鱼  ·  mysql查某字段数量大于2的_mob64c ...·  22 小时前    · 
要出家的灭火器  ·  Android ...·  1 月前    · 
慈祥的针织衫  ·  了解 MySQL的数据行、行溢出机制吗？ ...·  3 月前    · 
风流倜傥的灭火器  ·  关于WPF ...·  1 年前    · 
Code  ›  MySQL多表操作(一对一 一对多 多对多)__mysql 一对一 一对多
https://blog.51cto.com/u_13045706/3832260
忐忑的豆芽
1 年前

参考： https://blog.csdn.net/zuosixiaonengshou/article/details/53011452(https://blog.csdn.net/zuosixiaonengshou/article/details/53011452javascript:void(0)

一，1对1

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

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

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

mysql> create table man(
 id varchar(32) primary key , 
name varchar(30) 

 　　创建woman表


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) 

 　　建表语句解析


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

 　　查看建表语句


 　　一一对应关系


　　首先插入3个男人


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)

 　　查询夫妻信息


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

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


 　　查询语句解析


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字段相同的则满足条件即夫妻的一对一关系

  　　查询小花的丈夫是谁


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）


　　创建人员表


CREATE TABLE `person2` (
  `id` varchar(32) primary key,
  `name` varchar(30),
  `sex` char(1),

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


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`)

　　首先插入人员数据


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)

# 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)

 　　查询那些人有那些车


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有哪些车


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


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)

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


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进行匹配


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

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为空即可查出谁没有车


　　查询那些人没有车


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)

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


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


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、分别建议两个实体表(没有外键，但有自己的主键, 没有冗余信息)


//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，另外补建一个关系表


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;
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标准，可用，但效率不高


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标准，效率高，推荐使用---关联


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)

 　　查询那些人没有选课


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


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未选择任何课程


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)

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


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)
 
推荐文章
痛苦的红金鱼  ·  mysql查某字段数量大于2的_mob64ca12dbdb81的技术博客_51CTO博客
22 小时前
要出家的灭火器  ·  Android RatingBar:如果我自定义它的颜色，它无法填充部分ratingBar-腾讯云开发者社区-腾讯云
1 月前
慈祥的针织衫  ·  了解 MySQL的数据行、行溢出机制吗？ - 赐我白日梦 - 博客园
3 月前
风流倜傥的灭火器  ·  关于WPF RadioButton和CheckBox绑定问题_radiobutton wpf checkbox_晚安苏州的博客-CSDN博客
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号