开始MySQL之路——外键关联和多表联合查询详细概述

比眉伴天荒 2024-03-24 21:51 92阅读 0赞

### 多表查询和外键关联 ###

实际开发中，一个项目通常需要很多张表才能完成。例如，一个商城项目就需要分类表，商品表，订单表等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

![4baf1966ce374f08b1fa927ea4a443d0.png][]

##### 多表关系 #####

MySQL多表之间的关系可以概括为：

###### 一对一关系 ######

*  一个学生只有一张身份证；一张身份证只能对应一学生。
 *  在任一表中添加唯一外键，指向另一方主键，确保一对一关系。
 *  一般一对一关系很少见，遇到一对一关系的表最好是合并表。

![bf420c7e1b1c4f72a491a37799b69ccc.png][]

###### 一对多/多对一关系 ######

> 部门和员工
> 
> 分析：一个部分有多个员工，一个员工只能对应一个部门，实际原则：在多的一方建立外键，指向一的一方。

![9e16bf363616423c82ea712fd850bc90.png][]

![f07d5cbaa3594ec6ac9e442e1684d253.png][]

###### 多对多关系 ######

学生和课程

分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择，原则：多对多关系实际需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那张两张表的主键

![62471936d6214ca599e13d2eca44c82c.png][]

#### 外键约束 ####

##### 介绍 #####

MySQL外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据简历连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果，桃子，李子，西瓜等4种水果，那么，你来到水果摊要买水果只能选择苹果，桃子，李子和西瓜，其它的水果都是不能购买的。

![160db5fb79b54725b20d1f79903daa58.png][]

##### 特点 #####

定义一个外键时，需要遵循下列规则：

> 1.  主表必须已经存在于数据库中，或者是当前正在创建的表。
> 2.  必须为主表定义主键。
> 3.  主键不能包含空值，但允许在外键中出现空值。也就是说，只有外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
> 4.  在主键的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
> 5.  在外键中的数目必须和主键的主键中列的数目相同。
> 6.  外键中列的数据类型必须和主表主键中对应列的数据类型相同。

#### 创建外键约束-第一种方式 ####

方式一：在创建表时设置外键约束

在create table语句中，通过foreign key关键字来指定外键，具体的语法格式如下：

> \[constraint<外键名>\] foreign key 字段名\[,字段名2,...\] references<主键名> 主键列1 \[,主键列2,...\]

实现：

> create database mydb3;  
> use mydb3;  
> \-- 创建部门表  
> create table if not exists dept(  
> depton varchar(20) primary key, -- 部门号  
> name varchar(20) --部门名字  
> );

方式1-在创建表时设置外键约束

> create table if not exists emp(  
> eid varchar(20) primary key,-- 员工编号  
> ename varchar(20), -- 员工名字  
> age int,-- 员工年龄  
> dept\_id varchar(20), --员工所属部门  
> constaraint emp\_fk foreign key(dept\_id) references dept(deptno) -- 外键约束  
> );

代码实现

> 外键约束  
> create database mydb3;  
> use mydb3;  
>   
> 创建部门表  
> create table if not exists dept(  
> deptno varchar(20) primary key, -- 部门编号  
> name varchar(20) -- 部门名字  
> );  
>   
> 创建员工表，并创建外键约束 -方式1  
> \[constraint<外键名> \] foreign key 字段名 \[,字段名2,...\] references<主表名> 主键列1\[,主键列2，...\]  
>   
> create table if not exists emp(  
> eid varchar(20) primary key, -- 员工编号  
> ename varchar(20),-- 员工姓名  
> age int ,-- 员工年龄  
> dept\_id varchar(20), -- 员工所属部门  
> constraint emp\_fk foreign key (dept\_id) references dept(deptno)  
> );

#### 创建外键约束-第二种方式 ####

外键约束也可以在修改表时添加，但是添加外键约束的前提是：才表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

> alter table <数据表名> add constarint <外键名> foreign key(<列名>) references <主表名>(<列名>);

![ca7298e8df0e492089a465bdbdc2a090.png][]

![95e8272419884fc38a5d693b6f8feddb.png][]

#### 在外键约束下的数据操作 ####

![3dbb7b446c5c4c049d5b08861224de76.png][]

#### 删除外键约束 ####

![022d6b1c4d3c4a9bb208f8e58a62413a.png][]

![52d5c7b4c4624b9f8887ae4ab20c063b.png][]

#### 多对多关系 ####

![f9b90d1f0711494787ded8dc5b739755.png][]

![be7fa69703934d8b9cd8d4227f1dd57e.png][]

![9e19f413740442f3abfc172798f6a9c2.png][]![b6ad9fb19f8847eb8930bce0bef57bca.png][]

![9e99700ff717415a9773ef65857f3e9c.png][]![a964f9b5687842e38c7e43886ce024c9.png][]

![10567ab0939d48f58240c453cf5d915a.png][]

### 多表联合查询 ###

![3f6a6fce12a74ea88ca3f87b53df0d82.png][]

![eb22f16547e04b4ebb1a4e1053a12650.png][]

![963236e5011c4adabc62b4e7dbea583d.png][]

![29096ba3f5f34b9abdec8c56e334ddd8.png][]

![e722bcbcc37f4b13a8e007585064ce6f.png][]

![640d8189990947889d52211bf7f11644.png][]

![a507c00f047a448ea16553c62a646dfa.png][]![c8129af0fc104e229987ca4246a7702a.png][]

![ef4f033f59874c5aa9bcafcc3b4b76ff.png][]

#### 内连接查询 ####

![1157d5c5b7e049cd9acb6cc2b64b2e3f.png][]

![5c314c607f0d44b6987f8e136d7e3d40.png][]

![0ce7b18284f740059b84e5057179b6cf.png][]

![873d85d722fa4cdc88fb16f8d3b4c312.png][]

![2e5becf3185743e098ab4967a6aee1ec.png][]

![7082b65a29934bb78f43f980d02b863a.png][]

![4d5581d6323b402a80f755fe408a3771.png][]

![08b7aec4b7974b85b5b7ff89adc659cf.png][]![efaa140d3e834200b4607f8582145779.png][]

![454273d6c4e34de2a77c4ec722a1d27c.png][]

![7d003d601e0242dd9c1a7df2eb06164a.png][]

![bf959793a06d4a9f8acbe6b0f0f0e5fc.png][]

![81f39d8ef4114d399075bd0dd254c8a2.png][]

#### 外连接查询 ####

![da590ab6310f46ffa82cf76ff2c5033c.png][]

![cf6f4535d5394b5bbd54590963f4a77e.png][]

![d4754e38f8a94377ad28ee21b9c9c14a.png][]

![3554826b08e840a78ee23a061c5fa208.png][]

![1f037717aae84206aeb6be319062c5dc.png][]

![00ff0c894f1c46e098afd81948989f5c.png][]

#### 子查询 ####

![b5aef59526de4cff91708ab4013e60a5.png][]

##### ①：基本子查询 #####

![4361be9737e9457b86b5200eb4dd1a86.png][]

![b6a5e63d4ec24dc195cadc0f48dfe2d0.png][]

![d65886036d2f49b1b53ff532d319414b.png][]

![1d14596a24b94db49b286f5c6832dc06.png][]

![270ba2685c5f481c94c2fd615326740c.png][]![86dead92c107446e9c2bbb3c07ec7365.png][]

![e54f563d51834624a923dea3d682c259.png][]

![ad768f4d990d403d9c49c4fef7a4aeee.png][]

![afad8f862f3247c999616baf519991e5.png][]

![dcbbebe9b0854604af978d841f68b641.png][]

##### ②：all关键字 #####

![49c8d1f7f6834d988b6f9d2a2f146248.png][]

![eda323551cdd47109003a7e4864b757f.png][]

##### ③：Any和some #####

![9867d5d815d247d4a662684c81b4402d.png][]

![f27ab41790324583ba99157fcf61d1d1.png][]

##### ④：in #####

![bd1d0d0227be4292b94f60c78b85aa18.png][]

![e32fd9050a53487ea89c6412fde37752.png][]

##### ⑤：Exists #####

![e908e42e71dd4ff489b4860c5f42ddec.png][]

![5986c30513eb4a288d0e6f66b55a218d.png][]

![e2aedef092e54a8085e8c808df5c2660.png][]

#### 自关联查询 ####

![4c1b3e43149345b8b5a31f840cc8c817.png][]

![2483433be219447e9f79081524224b64.png][]

![db1ff181b4864db2a4fd417c1d58fef3.png][]

![b9670940a7b0407596d0e692b25201d7.png][]

![59b21bb7c4cd46df97d2325a1554c428.png][]

![16a0584fd8084efb9095bb02387cf934.png][]

[4baf1966ce374f08b1fa927ea4a443d0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/28c9f13e17444a919b4533cbb7feba68.png
[bf420c7e1b1c4f72a491a37799b69ccc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/9f28d73ee1ce4ca68c5b1f80819ff40b.png
[9e16bf363616423c82ea712fd850bc90.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/a1840a9f268941089a9f8bca1cdecfa8.png
[f07d5cbaa3594ec6ac9e442e1684d253.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/c8ce5272ed664ebaa11d41642fadfb1c.png
[62471936d6214ca599e13d2eca44c82c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/bdbdb5d2407a410ca687c531934e0e9c.png
[160db5fb79b54725b20d1f79903daa58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b0bbc3f44fca4b32b6a2f3ce6b0318e0.png
[ca7298e8df0e492089a465bdbdc2a090.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/9148d830692c4253b7581ed7f3c73bfa.png
[95e8272419884fc38a5d693b6f8feddb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/eefb03cb29b843148839649566c584ed.png
[3dbb7b446c5c4c049d5b08861224de76.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/6d9b65cdbcae41bd9e82097a8dca7a16.png
[022d6b1c4d3c4a9bb208f8e58a62413a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/edc484b047494f9e8758cefd97352366.png
[52d5c7b4c4624b9f8887ae4ab20c063b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/87af2bb32e684ce1bedb7406f3e6592c.png
[f9b90d1f0711494787ded8dc5b739755.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/f55f2c6157074c209cfa6910c71abd6f.png
[be7fa69703934d8b9cd8d4227f1dd57e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/822cade48f2f4674a2b174cf5b2540dd.png
[9e19f413740442f3abfc172798f6a9c2.png]: https://img-blog.csdnimg.cn/9e19f413740442f3abfc172798f6a9c2.png
[b6ad9fb19f8847eb8930bce0bef57bca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/2492584203bf4d0e9f245a38e14e2cb5.png
[9e99700ff717415a9773ef65857f3e9c.png]: https://img-blog.csdnimg.cn/9e99700ff717415a9773ef65857f3e9c.png
[a964f9b5687842e38c7e43886ce024c9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/440a4d70369d4d95b266f7e016432eaf.png
[10567ab0939d48f58240c453cf5d915a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/41488531a78746f5b4e8b70cda869c75.png
[3f6a6fce12a74ea88ca3f87b53df0d82.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b97b578a953a4feabc37afdd4b13e0a7.png
[eb22f16547e04b4ebb1a4e1053a12650.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/833fff12299842899089b77b4ac66fee.png
[963236e5011c4adabc62b4e7dbea583d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/8defc55c2f7349a7a053ba878fc817e3.png
[29096ba3f5f34b9abdec8c56e334ddd8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/1898ed404f7943a786bcd3475468ff3c.png
[e722bcbcc37f4b13a8e007585064ce6f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/f7cb084d05c1469ead4d0f3ff4d66fb3.png
[640d8189990947889d52211bf7f11644.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/792a6097b5d044e49f2a412a1a9c221f.png
[a507c00f047a448ea16553c62a646dfa.png]: https://img-blog.csdnimg.cn/a507c00f047a448ea16553c62a646dfa.png
[c8129af0fc104e229987ca4246a7702a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/5fbcbb038cc04a26b8a5785d9938a70b.png
[ef4f033f59874c5aa9bcafcc3b4b76ff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/2c4d8b95352f4397bfa36ddba11d1540.png
[1157d5c5b7e049cd9acb6cc2b64b2e3f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/d068b8e4b30b4636b6379e4cd63d762c.png
[5c314c607f0d44b6987f8e136d7e3d40.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/5ba8cecd6f0440ba95317190d97fdbb3.png
[0ce7b18284f740059b84e5057179b6cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/8bb5a26a051e46a884a1ceefd995ff88.png
[873d85d722fa4cdc88fb16f8d3b4c312.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/e8f64823817040dc80cc6da39c122f7c.png
[2e5becf3185743e098ab4967a6aee1ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/363d82db60da4dea8373483bee3d06bf.png
[7082b65a29934bb78f43f980d02b863a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/13b70a99911044f1bbbf99a1448b30d7.png
[4d5581d6323b402a80f755fe408a3771.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/77a0d159104843ef864b24c87300d78c.png
[08b7aec4b7974b85b5b7ff89adc659cf.png]: https://img-blog.csdnimg.cn/08b7aec4b7974b85b5b7ff89adc659cf.png
[efaa140d3e834200b4607f8582145779.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/2dfc487e3e1e4527ba0cdf38a3fce8b7.png
[454273d6c4e34de2a77c4ec722a1d27c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/d8f3e9f4f2fb4c48a11d2cf23ddd285e.png
[7d003d601e0242dd9c1a7df2eb06164a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b57b50a71174419896518a61b2b05186.png
[bf959793a06d4a9f8acbe6b0f0f0e5fc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/54ea966a7e024d7e842f5ec89440879b.png
[81f39d8ef4114d399075bd0dd254c8a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/17cd9c16a52c4d6aa17a729cd232fb16.png
[da590ab6310f46ffa82cf76ff2c5033c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/5c979973997649c09c4d61b3fe2a2091.png
[cf6f4535d5394b5bbd54590963f4a77e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/1e50797bb46744219d1e6340efc35f2a.png
[d4754e38f8a94377ad28ee21b9c9c14a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b4060156d35248369d66bd01d29aa102.png
[3554826b08e840a78ee23a061c5fa208.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/e9e46f6c480345348a6c259e85eb6617.png
[1f037717aae84206aeb6be319062c5dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/97e78b50b22845b7b0b2c0a84057ecbb.png
[00ff0c894f1c46e098afd81948989f5c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/f745ba2e75464780853bae7ec473ffa4.png
[b5aef59526de4cff91708ab4013e60a5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7d0fcb006eb548eab717cfebf5388a9c.png
[4361be9737e9457b86b5200eb4dd1a86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/f473a4738ae44f208814152aebb2ec02.png
[b6a5e63d4ec24dc195cadc0f48dfe2d0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/11ac9d92c1b142e3a8f60ed2a024912c.png
[d65886036d2f49b1b53ff532d319414b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/13f677dec4da4fb19da9430ec7160ce7.png
[1d14596a24b94db49b286f5c6832dc06.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/2748fe3175204e4595a9b2a205088b71.png
[270ba2685c5f481c94c2fd615326740c.png]: https://img-blog.csdnimg.cn/270ba2685c5f481c94c2fd615326740c.png
[86dead92c107446e9c2bbb3c07ec7365.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/4b4b34dc945d46a1b6e990535e277827.png
[e54f563d51834624a923dea3d682c259.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/79b52c2b29894f09b1559885aad4c188.png
[ad768f4d990d403d9c49c4fef7a4aeee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/68d3071b13374820b17cc0882c7e3560.png
[afad8f862f3247c999616baf519991e5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/c8c126d4eac943ad8fc49ffdda7b7386.png
[dcbbebe9b0854604af978d841f68b641.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/5bbde2024f944a228e8678e03d5f8733.png
[49c8d1f7f6834d988b6f9d2a2f146248.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/c00018bd84b6484bb2186d68c356f513.png
[eda323551cdd47109003a7e4864b757f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/299b0de7480349959b41b5b361152ac2.png
[9867d5d815d247d4a662684c81b4402d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/22f7342bfa774dccab413be026ff268d.png
[f27ab41790324583ba99157fcf61d1d1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/d82be89333d54523acee2836ab5bbae0.png
[bd1d0d0227be4292b94f60c78b85aa18.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/ab825bd193614578918739e47bfb0622.png
[e32fd9050a53487ea89c6412fde37752.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/f553d4392a1b43c4bd3b1070fbb86738.png
[e908e42e71dd4ff489b4860c5f42ddec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/2cf0ef0665cc4e0eb03d1f0b5bc9f1d5.png
[5986c30513eb4a288d0e6f66b55a218d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/e4b6ebb98b9242c48d9abc11ab13546f.png
[e2aedef092e54a8085e8c808df5c2660.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/29ea530970824408ad7774d15a2f4f36.png
[4c1b3e43149345b8b5a31f840cc8c817.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/fe17279eadc64c71811497b249384109.png
[2483433be219447e9f79081524224b64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/e15c2a257afb45e2aa420b8a5fa1e797.png
[db1ff181b4864db2a4fd417c1d58fef3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/03bbf178a3a84f80ac55f8281256421a.png
[b9670940a7b0407596d0e692b25201d7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/1ff3e1fd763d40648f76012fbffc73e5.png
[59b21bb7c4cd46df97d2325a1554c428.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/1c385b1ef6b84022977e0ffc6ef64cb8.png
[16a0584fd8084efb9095bb02387cf934.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/ba771c82c2ab4f7fa689ff80460ab381.png