一、表的加法:union
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/10651d59605a2d3c178283babeca46fa.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/0c5984e34b8ac6092f3eed0a23721007.jpg)
如果想保留两个表中的重复值,那么使用union all,那么重复值语文课程就被保留下来了
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/56ff50854be887de33df197ae7b916d9.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/98fd1f2d40446fb6d37fda81ebfd8bd7.jpg)
二、表的联结
- 交叉联结(也叫笛卡尔积 cross jion)
表中的每一行与另一个表中的每一行合并在一起,产生的行数是两个表行数的乘积,它的数据产生如下图所示
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/03ba8df42ed4923b84a23853ecc28d9a.jpg)
2.内联结(inner join):查找出同时存在于两张表的数据
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/88a5fa16ac5d6f8caa1f6d0c2dca6b0c.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/68c4bd706d4da9fd7e8439499a1a5671.jpg)
3.左联结(left join):取出左边表的全部数据,右边的表选出与左边相同数据的行,然后进行数据合并
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/161a911c9de98c2aa47dfb46fc845155.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/c0256acfad27c9d055862a01b8600e51.jpg)
如果我想查找在左表中有而在右表中没有的数据
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/a7eb8c7b115ec198e336de72663def78.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/acee3e0f480a21f81fd89728f9a08873.jpg)
4、右联结
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/6b8740b25a62159049147a55d5c15101.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/409f0a1d5eeab4e54079a8f1ebe8bf66.jpg)
如果我想查找在右表中有而在左表中没有的数据
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/b7671c81a2a7dcb46079e070b68a6abe.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/3f88e74c196b0e2f5d98f7011886e059.jpg)
5、全联结(full join)—取出左表和右表的所有数据,有相同数据就合并,没有则用Null来填充
- MySQL是不支持全联结的
总结:
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/7a5dd460a7f78400914c8b675404887d.jpg)
三、案例应用
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/58219e961a2276b551dbcf688c719523.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/7bf2fa3e91a1dbbd8ee5c70cda7b3ed7.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/657bc8ffb7fc68d04693f69d8e0813fb.jpg)
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/c44867f9cdf3a9ab65f8e0c1dda96413.jpg)
四、case表达式—相当于条件判断函数
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/da5ecd13e7eb2dd3cf48c7420ee32477.jpg)
案例1.查询score表中学生的成绩是否及格
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/51f970db0aa82ff5269c0259639f9333.jpg)
案例2.查询每门课程的及格人数和不及格人数
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/994ebb3bcdfe8d8ba815e5b1c526d645.jpg)
案例3.使用分段[100-85],[85-70][70-60],[<60]来统计各分段人数,课程号,课程名称
![SQL多表查询](https://www.isolves.com/d/file/p/2021/08-27/f498dd0e564e40f4c44d2e6aeb520da9.jpg)