MySQL2-多表查询、子查询、union、limit机制

亦凉 2024-03-17 09:41 60阅读 0赞

一、多表查询  
在实际开发中，大部分情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。一般一个业务都会对应多张表。  
1.表的连接方式的分类  
内连接：等值连接、非等值连接、自连接  
外连接：左外连接（又称左连接）、右外连接（又称右连接）  
全连接：（很少用，不介绍）  
2.笛卡尔积连接  
示例：  
员工表emp（员工编号EMPNO,员工姓名ENAME，员工职位JOB，MGR上级领导编号,入职时间HIREDATE，工资SAL，津贴COMM，所属部门编号DEPTNO）：  
![在这里插入图片描述][7ffae4d72fad4b338efa00eebc2e3078.png]  
部门表dept(部门编号DEPTNO,部门名称DNAME,部门位置LOC）：  
![在这里插入图片描述][62f9cc1701a14b349a76a19af4e2ea55.png]  
select ename,dname from emp,dept;  
上述sql语句emp表与dept表进行**笛卡尔积连接**，结果为56条记录（4\*14)。  
**表的别名**：  
select e.ename,d.dname from emp e,dept d;//emp表的别名为e，dept表的别名为d，使用表的别名是为了在select时区分不同表的同名字段  
3.避免笛卡尔积现象  
避免笛卡尔积现象的方法是加条件进行过滤，但是要注意，加条件进行过滤不会减少记录的匹配次数，只不过显示的是有效记录（即最开始的连接还是笛卡尔积连接，再在笛卡尔积连接的基础上进行过滤）。  
![在这里插入图片描述][b85f518eeb0e4fd3a46fcff0b109a686.png]  
说明：上述sql语法是sql92版本，现在很少使用。  
4.内连接之等值连接  
最大的特点是条件是等值关系。  
语法：… <表A > join <表B> on <连接条件> where …（sql99语法）  
示例：  
![在这里插入图片描述][9c57613dad244196bac6f760a840055b.png]  
上例中的sql语句也可以写为：select e.ename,d.dname from emp e join dept d on e.depton = d.deptno；  
说明：（1）shang述sql语句省略了inner，即“select e.ename,d.dname from emp e join dept d on e.depton = d.deptno；”等价于“select e.ename,d.dname from emp e innner join dept d on e.depton = d.deptno；"。  
（2）可以看出sql99语法的结构更清晰，把“表的连接条件”与“连接后的筛选条件（where)”区分开。  
5.内连接之非等值连接  
特点是连接条件是非等量关系。  
工资等级表salgrade（GRADE工资等级，LOSAL该等级工资下限，HISAL该等级工资上限）:  
![在这里插入图片描述][22768d6d69d64bdab19d5733214a65f7.png]  
员工表emp（员工编号EMPNO,员工姓名ENAME，员工职位JOB，MGR,入职时间HIREDATE，工资SAL，津贴COMM，所属部门编号DEPTNO）：  
![在这里插入图片描述][3adf8467a3df499094412366b66f4b1c.png]  
![在这里插入图片描述][fd085b4fb66547d3b5a3d377b5f4de11.png]  
实现的sql语句为：  
![在这里插入图片描述][a8c959014e7b43cc97b7d6be28d8ad29.png]  
可以看出非等值连接就是连接条件不为等式的内连接。  
6.内连接之自连接  
特点是一张表看作两张表，自己连自己。  
示例：  
员工表emp（员工编号EMPNO,员工姓名ENAME，员工职位JOB，MGR上级领导编号,入职时间HIREDATE，工资SAL，津贴COMM，所属部门编号DEPTNO）：  
![在这里插入图片描述][24f00255f3414dbe9248545485c8e4e8.png]  
![在这里插入图片描述][1f00ea52655d4e04894607393487b0b4.png]  
![在这里插入图片描述][a7a41d55d8384946a4a1a721983296f2.png]  
总结：可以看出，自连接适用于一个表的两个字段属于同一类，两个字段之间具有一定的关联，这种关联有些类似于表之间的外键。  
7.外连接  
内连接：表A和表B进行连接，使用内连接，凡是A表与B表能够匹配上的记录查询出来，A、B两张表没有主副之分，两张表是平等的。  
外连接：假设A和B进行连接，使用外连接，A、B两张表中有一张是主表，有一张是副表。主要查询主表中的数据，另外带上查询副表。当副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配。即外连接的特点是主表的数据无条件的全部查询出来。  
左外连接（左连接）：表示左边的表是主表。  
右外连接（右连接）：表示右边的表是主表。  
![在这里插入图片描述][e3219030ff564d58ba7dcb9dea29670a.png]  
可以看出，如果使用内连接的自连接，上图中的empno为7839的记录会被去除，因为mgr无法匹配。这里需要使用外连接。  
使用左外连接：  
![在这里插入图片描述][fba938ef8b814ab38eec02376688c804.png]  
等价于:  
![在这里插入图片描述][df6edb2ad3e34812a79bc3c7242b2d19.png]  
说明：任何一个左外连接都有一个对应的右外连接写法。left和right后省略了outer，outer可写可不写。  
8.三张以上表连接查询  
案例：找出每一个员工的部门名称以及工资等级。  
员工表emp（员工编号EMPNO,员工姓名ENAME，工资SAL，所属部门编号DEPTNO）  
![在这里插入图片描述][748c3b37ca9d4674999fad76262fe63b.png]  
部门表dept：  
![在这里插入图片描述][6084a79db580419d806d08fab026bc01.png]  
工资等级表salgrade（GRADE工资等级，LOSAL该等级工资下限，HISAL该等级工资上限）：  
![在这里插入图片描述][a690124a3e484ac786acf412371bbe4c.png]  
执行的sql语句为：  
![在这里插入图片描述][2b87695892f14fda8152d9a0d2104b44.png]  
再例：  
![在这里插入图片描述][1b2b31571d414cf8ba59ea866c3ed367.png]  
二、子查询（这里举的例子参考上面例子中出现的表）  
子查询就是select语句中嵌套了select语句，被嵌套的select语句是子查询。子查询可以出现的位置:  
select …<select子句>  
from …<select子句>  
where …<select子句>  
1.where后嵌套select语句  
员工表emp（员工编号EMPNO,员工姓名ENAME，工资SAL，所属部门编号DEPTNO）  
![在这里插入图片描述][271a958a5b164569ac3e2ecec447d078.png]  
![在这里插入图片描述][3fdc84d1c2b4482c899edcbe73b0afac.png]  
使用子查询：  
![在这里插入图片描述][ee685497b07a4da0a44a354f41010681.png]  
2.from后嵌套select子查询  
员工表emp（员工编号EMPNO,员工姓名ENAME，工资SAL，所属部门编号DEPTNO）  
![在这里插入图片描述][f8603de5852546cfa10e041fc36b5528.png]  
工资等级表salgrade（GRADE工资等级，LOSAL该等级工资下限，HISAL该等级工资上限）：  
![在这里插入图片描述][e2aec0ab00f841bbb92c4611500bb151.png]  
![在这里插入图片描述][62253ddb7d144694a74a6fbc605ed4cf.png]  
使用嵌套子查询：  
![在这里插入图片描述][baf9c019e8db4b8083f145461220472a.png]  
3.select后嵌套子查询  
![在这里插入图片描述][ff89a23afbf84d209213d71351ed866a.png]  
另一种方式是在select后嵌套子查询（能看懂就行）：  
![在这里插入图片描述][c008cab5f9424fa2860afd8bae13f262.png]  
解释：如果内层子查询不依赖外层子查询，称为不相关嵌套查询，可由内向外逐层处理，也就是每个内层子查询在上一层查询处理之前求解，内层子查询的结果用于建立其外层查询。如果内层查询依赖于外层查询称为相关嵌套查询，对于外层查询结果的每一行根据它与内层查村相关的列值处理内层查询。  
三、union（将查询结果集相加）  
![在这里插入图片描述][6ebbb078d1704c31a83cb33e579b2f84.png]  
![在这里插入图片描述][409cdacca04740b58d60bee19c418b44.png]  
注意：union的结果union前面的子句的查询结果排在前面，union后面的子句的查询结果排在后面。  
除此之外，unon可以处理or和in子句不能处理的情况，即将两张不相关的表的查询结果拼在一起：  
![在这里插入图片描述][b11715f2a50041a9a1323e4f9be9d3cd.png]  
注意：union连接的两个查询结果的列数需相同。  
四、limit（limit是mysql特有的，其他数据库中没有，不通用；Oracle中有一个相同的机制，叫做rownum）  
**limit机制很重要，是分页机制的基础。**  
limit取结果集中的部分数据。  
语法机制：limit startIndex, length  
startIndex表示起始位置，length表示取几个。如果只写了一个数字，则表示length，startIndex默认为0。  
![在这里插入图片描述][349a465dc6734f6da767ca7d92941327.png]  
注意：limit是select语句最后执行（在order by之后）的环节。

[7ffae4d72fad4b338efa00eebc2e3078.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/04227e9bd8ed4fe895bb9af2acdb6d29.png
[62f9cc1701a14b349a76a19af4e2ea55.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/289fc8ce7e7f492ca84365f77d9b7b95.png
[b85f518eeb0e4fd3a46fcff0b109a686.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/37520a63b8504fbca92ba5e5e01ddca1.png
[9c57613dad244196bac6f760a840055b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/a2db01911c5d4a9d8bebe8af32e8e5e8.png
[22768d6d69d64bdab19d5733214a65f7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/b91526524481464ebede34696b05e435.png
[3adf8467a3df499094412366b66f4b1c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/4f748815fcee420dac4cad76e914b1e1.png
[fd085b4fb66547d3b5a3d377b5f4de11.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/075d385847204a149865125e0f1392c4.png
[a8c959014e7b43cc97b7d6be28d8ad29.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/f3cd794391df495dbf7938487ba89489.png
[24f00255f3414dbe9248545485c8e4e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/75095776c59444f88ea5d0b9836287a3.png
[1f00ea52655d4e04894607393487b0b4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/eabe5707e5e1450e82a90c42d6e3c2e8.png
[a7a41d55d8384946a4a1a721983296f2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/abd39ac406b84cf29337650b011fbbb4.png
[e3219030ff564d58ba7dcb9dea29670a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/c5be3ddc822f49c3a199c6047e5d4bef.png
[fba938ef8b814ab38eec02376688c804.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/79eaea3cec9d44fb865a9e055b8306dc.png
[df6edb2ad3e34812a79bc3c7242b2d19.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/49f709c1796c4fedb2504197745e2c18.png
[748c3b37ca9d4674999fad76262fe63b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/3aa4b18690ce4251bf8c7589532e3a41.png
[6084a79db580419d806d08fab026bc01.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/0ce36ac371da4748a61c1488ef6edb74.png
[a690124a3e484ac786acf412371bbe4c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/0dcf3dd91e004b01adc43fdfdc3d22be.png
[2b87695892f14fda8152d9a0d2104b44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/b18657a14ce3447d9881e119a660b4f6.png
[1b2b31571d414cf8ba59ea866c3ed367.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/683a9e6b47124b6ab9c954557c8a631b.png
[271a958a5b164569ac3e2ecec447d078.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/c327553e545a4ca5803e2a9d3bf86145.png
[3fdc84d1c2b4482c899edcbe73b0afac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/aeb8b9acd5e842bfa64e712b1c5bcc80.png
[ee685497b07a4da0a44a354f41010681.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/d5e3c406021d4ffcb28bf0d088ed45c9.png
[f8603de5852546cfa10e041fc36b5528.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/493d05a5a5904b14ad88cb7d3ae5353c.png
[e2aec0ab00f841bbb92c4611500bb151.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/59e72f21a2b444f391454e592f49f4a8.png
[62253ddb7d144694a74a6fbc605ed4cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/9480898901514affb36c3784353b6405.png
[baf9c019e8db4b8083f145461220472a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/53b733d7fbf2468194e7f8283a996453.png
[ff89a23afbf84d209213d71351ed866a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/0a5c03c77327404fb3cdf57d57b2c265.png
[c008cab5f9424fa2860afd8bae13f262.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/0533c7bcb1aa4489829d56f4f14148ec.png
[6ebbb078d1704c31a83cb33e579b2f84.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/ee26c369ae9e41df8f5749e313f7ccb0.png
[409cdacca04740b58d60bee19c418b44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/d2b05a8b89544dabbcf669ac716fd32b.png
[b11715f2a50041a9a1323e4f9be9d3cd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/0a71d1f7e2ca453da8c88048749bca44.png
[349a465dc6734f6da767ca7d92941327.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/f71c5da7e0134a50a049bcbd953be48b.png