MySQL多表操作(一对一 一对多 多对多)
参考：https://blog.csdn.net/zuosixiaonengshou/article/details/53011452
一，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> 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可以为空代表为单身狗
mysql> insert into woman values('3', '小红', null);
查看数据
查询夫妻信息
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;
3，添加一些演示数据
//实体表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标准，可用，但效率不高
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)
