本文详细介绍了关系型数据库中的连接查询，涵盖内连接（等值连接、非等值连接）、外连接（左外连接、右外连接）、多条件连接和多表连接实例。通过实例演示了如何避免笛卡尔积，以及如何使用链式查询和临时表技巧进行复杂查询。
连接查询
1、连接是关系型数据库的主要特点。连接查询是关系型数据库中最主要的查询，主要包括内连接、外连接等
⑴连接查询：也可以叫跨表查询，需要关联多个表进行查询
2、通过连接运算符可以实现多个表查询
3、在关系数据库管理系统中，表建立时各数据之间的关系不必确定，通常把一个实体的所有信息存放在一个表中
⑴当查询数据时，通过连接操作查询出存放在多个表中的不同实体信息
⑵当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询
注：
1、在实际开发中，如果把所有数据都放在一个表中的话，那么这个表就会显得非常臃肿，不利于表、数据的维护等
2、因此，一般一个业务都会对应多张表，比如：学生和班级，起码两张表
3、这种情况下，大部分的情况都不是从单表中查询数据的，一般都是多张表联合查询取出最终的结果
⑴比如：这里要查询学生班级的信息，那么就会涉及到两张表，需要将两张表以一定的方式关联起来，然后进行查询。这种就是连表查询
4、连接查询总的来说，就是：将多张表连接起来进行查询，最终查询出来的数据可以来自于不同的表
⑴同时查询不同表中的数据
连接查询的分类
连接查询可以根据连接方式的不同来进行划分，主要分为两类：
1、内连接：
⑴等值连接
⑵非等值连接
⑶自连接
2、外连接：
⑴ 左外连接：又叫做左连接
⑵ 右外连接：又叫做右连接
连接查询的语法
连接查询基本形式如下：
SELECT ... FROM 表1 [连接方式] JOIN 表2 [ON 连接条件] WHERE 过滤条件注：
1、可见，连接查询只是作为from子句的“数据源”
2、或者说，连接查询是扩大了数据源，从原来的一个表作为数据源，扩大为多个表作为数据源
3、筛选的顺序on优先于where
⑴ON关键字表示：两张表的连接条件(外键关联)， where会把前面的结果集进行筛选(可以筛选所有字段条件)
连接查询中的笛卡尔积现象
1、连接查询，是将两个表的每一行，以“两两横向对接”的方式，所得到的所有行的结果
⑴即一个表中的某行，跟另一个表中的某行，进行“横向对接”，而得到一个新行
2、整体连接结构如下图所示
注：
1、可见，假设：表1有n1行，m1列。表2有n2行，m2列
⑴则表1和表2“连接”之后，就会有：n1*n2行，m1+m2列
2、如果只是简单的将两个或多个表连接起来的话(无连接条件)，就会造成一种笛卡尔积现象
⑴就是：不管两个表中的两行有无关系，都会两两连接起来(n1*n2行)
3、笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积
例1：查询每一个员工的部门名称，要求显示员工名和部门名
mysql> SELECT * FROM dept;
+----+------------+
| id | name |
+----+------------+
| 1 | 人力资源部 |
| 2 | 财务部 |
| 3 | 后勤部 |
| 4 | 法务部 |
+----+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM emp;
+----+------+---------+
| id | name | dept_id |
+----+------+---------+
| 1 | 刘备 | 3 |
| 2 | 关羽 | 3 |
| 3 | 张飞 | 3 |
| 4 | 张角 | 2 |
| 5 | 张宝 | 2 |
| 6 | 张梁 | 2 |
| 7 | 曹操 | 1 |
| 8 | 曹丕 | 1 |
| 9 | 曹植 | 1 |
| 10 | 曹冲 | 1 |
| 11 | 孙权 | 0 |
+----+------+---------+
11 rows in set (0.00 sec)
mysql> SELECT e.name,d.name FROM emp e JOIN dept d;
+------+------------+
| name | name |
+------+------------+
| 刘备 | 人力资源部 |
| 刘备 | 财务部 |
| 刘备 | 后勤部 |
| 刘备 | 法务部 |
| 关羽 | 人力资源部 |
| 关羽 | 财务部 |
| 关羽 | 后勤部 |
| 关羽 | 法务部 |
| 张飞 | 人力资源部 |
| 张飞 | 财务部 |
| 张飞 | 后勤部 |
| 张飞 | 法务部 |
| 张角 | 人力资源部 |
| 张角 | 财务部 |
| 张角 | 后勤部 |
| 张角 | 法务部 |
| 张宝 | 人力资源部 |
| 张宝 | 财务部 |
| 张宝 | 后勤部 |
| 张宝 | 法务部 |
| 张梁 | 人力资源部 |
| 张梁 | 财务部 |
| 张梁 | 后勤部 |
| 张梁 | 法务部 |
| 曹操 | 人力资源部 |
| 曹操 | 财务部 |
| 曹操 | 后勤部 |
| 曹操 | 法务部 |
| 曹丕 | 人力资源部 |
| 曹丕 | 财务部 |
| 曹丕 | 后勤部 |
| 曹丕 | 法务部 |
| 曹植 | 人力资源部 |
| 曹植 | 财务部 |
| 曹植 | 后勤部 |
| 曹植 | 法务部 |
| 曹冲 | 人力资源部 |
| 曹冲 | 财务部 |
| 曹冲 | 后勤部 |
| 曹冲 | 法务部 |
| 孙权 | 人力资源部 |
| 孙权 | 财务部 |
| 孙权 | 后勤部 |
| 孙权 | 法务部 |
+------+------------+
44 rows in set (0.00 sec)注：
1、首先确定最终查询的数据：查询每一个员工的部门名称，要求显示员工名和部门名
⑴员工名来自于emp表
⑵部门名来自于dept表
⑶最终查询的数据来自于两张表，因此需要使用连接查询
2、从上面的数据结果可以看出：员工表中的一个员工都会和部门表中的每一个部门拼接一次，因此最后拼接的数据行数为11*4=44
⑴造成上面这个现象的原因就是：在连接表时，没有指名连接的条件
⑵这种现象就叫做：笛卡尔积现象
3、连接查询的原理是：A表与B表连接的时候，依次将A表中的每一行数据拿来与B表中的所有数据行都进行匹配
⑴将A表中的每一行数据都拿来匹配拼接到B表中的每一行后(可以把拼接后的数据看做一个新表，只是实际不存在)
4、因此在进行表的连接时，一定要加上连接条件进行过滤
5、还有一点是：这个例子中在使用表名和列名时都加上了对应的别名，这样做的话能很清楚的明白该字段属于哪张表(建议这么做)
6、连接查询的核心思想是：通过一定的条件(连接条件)，将表与表之间的数据行关联起来(此时就可以把多张表看成一张表)，从这张合并的表中查询数据
⑴这张合并的表中的字段是全部所关联表中的所有字段
①所以可以从合并的表中查询出任意表中的任意字段：可以指定查询的字段也可以使用*号来查询所有字段
⑵连接条件：一般都是分别位于两个表中的两个(多个)字段的值分别对应相等(等值连接)，也可以是非等值连接
⑶连接条件注重的是字段的值对应相等，与字段名是没关系的(字段名可以一样也可以不一样)
例2：避免笛卡尔积现象
mysql> SELECT * FROM dept;
+----+------------+
| id | name |
+----+------------+
| 1 | 人力资源部 |
| 2 | 财务部 |
| 3 | 后勤部 |
| 4 | 法务部 |
+----+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM emp;
+----+------+---------+
| id | name | dept_id |
+----+------+---------+
| 1 | 刘备 | 3 |
| 2 | 关羽 | 3 |
| 3 | 张飞 | 3 |
| 4 | 张角 | 2 |
| 5 | 张宝 | 2 |
| 6 | 张梁 | 2 |
| 7 | 曹操 | 1 |
| 8 | 曹丕 | 1 |
| 9 | 曹植 | 1 |
| 10 | 曹冲 | 1 |
| 11 | 孙权 | 0 |
+----+------+---------+
11 rows in set (0.00 sec)
-- 这里是返回两个表中的所有字段
mysql> SELECT * FROM dept d JOIN emp e ON e.dept_id=d.id;
+----+------------+----+------+---------+
| id | name | id | name | dept_id |
+----+------------+----+------+---------+
| 3 | 后勤部 | 1 | 刘备 | 3 |
| 3 | 后勤部 | 2 | 关羽 | 3 |
| 3 | 后勤部 | 3 | 张飞 | 3 |
| 2 | 财务部 | 4 | 张角 | 2 |
| 2 | 财务部 | 5 | 张宝 | 2 |
| 2 | 财务部 | 6 | 张梁 | 2 |
| 1 | 人力资源部 | 7 | 曹操 | 1 |
| 1 | 人力资源部 | 8 | 曹丕 | 1 |
| 1 | 人力资源部 | 9 | 曹植 | 1 |
| 1 | 人力资源部 | 10 | 曹冲 | 1 |
+----+------------+----+------+---------+
10 rows in set (0.00 sec)注：上面例子中
1、增加了两个表之间的连接条件"e.dept_id=d.id"(ON关键字之后)
2、表示：员工表在与部门表进行匹配拼接的时候，需要员工表中的dept_id字段值等于部门表中的id字段值时才进行匹配拼接
⑴如果：dept_id字段值不等于id字段值时，也会进行匹配，但是不会拼接返回
3、避免了笛卡尔积现象，但是并不会减少数据行的匹配次数，只是减少返回的数据行
⑴比如：上面例子中，"刘备"的部门为3，在进行连接匹配的时候，"刘备"依旧会与所有部门进行匹配，但是只会返回部门id为3的数据行
内连接查询
1、内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的数据行
⑴也就是说：在内连接查询中，只有满足条件的记录才能出现在结果关系中
2、语法形式：from 表1 [inner] join 表2 on 连接条件 where 过滤条件
⑴内连接通过on条件来筛选出来符合连接条件的数据
⑵关键字“inner”可以省略，但建议写上
3、内连接是应用最广泛的一种连接查询，其本质是根据条件筛选出“有意义的数据”
内连接：等值连接
1、内连接中的等值连接最大的特点是：条件是等量关系
2、等值连接使用的是"="操作符，表示相等关系
例3：
mysql> SELECT * FROM dept d INNER JOIN emp e ON e.dept_id=d.id;
+----+------------+----+------+---------+
| id | name | id | name | dept_id |
+----+------------+----+------+---------+
| 3 | 后勤部 | 1 | 刘备 | 3 |
| 3 | 后勤部 | 2 | 关羽 | 3 |
| 3 | 后勤部 | 3 | 张飞 | 3 |
| 2 | 财务部 | 4 | 张角 | 2 |
| 2 | 财务部 | 5 | 张宝 | 2 |
| 2 | 财务部 | 6 | 张梁 | 2 |
| 1 | 人力资源部 | 7 | 曹操 | 1 |
| 1 | 人力资源部 | 8 | 曹丕 | 1 |
| 1 | 人力资源部 | 9 | 曹植 | 1 |
| 1 | 人力资源部 | 10 | 曹冲 | 1 |
+----+------------+----+------+---------+
10 rows in set (0.00 sec)
-- 查询部门id大于2的员工、部门信息
mysql> SELECT d.id,e.name,d.name FROM dept d INNER JOIN emp e ON e.dept_id=d.id WHERE d.id > 2;
+----+------+--------+
| id | name | name |
+----+------+--------+
| 3 | 刘备 | 后勤部 |
| 3 | 关羽 | 后勤部 |
| 3 | 张飞 | 后勤部 |
+----+------+--------+
3 rows in set (0.00 sec)注：
1、在查询"查询部门id大于2的员工、部门信息"的SQL语句中，可以将其看做两步
⑴先查询出员工、部门信息，类似于这个例子中的" SELECT * FROM dept d INNER JOIN emp e ON e.dept_id=d.id;"
⑵此时就可以将步骤1中的结果想象成一个表，只是这个表实际不存在
⑶然后在步骤2中的这个想象表中过滤查询部门id大于2的数据(WHERE关键字在ON关键字之后)
⑷自己感觉有一个比较重要的思想：将查询结果看做一张临时表，然后继续在这个临时表中过滤查询数据、这张临时表继续关联其他表等
2、在SQL语句中还可以在SELECT关键字之后指定返回的数据列：数据列可以来自不同的表
⑴在指定列名时，注意加上列的别名
内连接：非等值连接
1、内连接中的非等值连接最大的特点是：条件是非等量关系
2、非等值连接使用的是BETWEEN...AND...关键字，表示在某个范围内
例4：查询员工工资等级
mysql> SELECT * FROM emp;
+----+------+---------+------+
| id | name | dept_id | sal |
+----+------+---------+------+
| 1 | 刘备 | 3 | 2975 |
| 2 | 关羽 | 3 | 1250 |
| 3 | 张飞 | 3 | 2850 |
| 4 | 张角 | 2 | 2450 |
| 5 | 张宝 | 2 | 800 |
| 6 | 张梁 | 2 | 5000 |
| 7 | 曹操 | 1 | 710 |
| 8 | 曹丕 | 1 | 3000 |
| 9 | 曹植 | 1 | 1300 |
| 10 | 曹冲 | 1 | 950 |
| 11 | 孙权 | 0 | 2000 |
+----+------+---------+------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM salgrade;
+----+-------+-------+
| id | losat | hisat |
+----+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 99999 |
+----+-------+-------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losat AND s.hisat;
+----+------+---------+------+----+-------+-------+
| id | name | dept_id | sal | id | losat | hisat |
+----+------+---------+------+----+-------+-------+
| 1 | 刘备 | 3 | 2975 | 4 | 2001 | 3000 |
| 2 | 关羽 | 3 | 1250 | 2 | 1201 | 1400 |
| 3 | 张飞 | 3 | 2850 | 4 | 2001 | 3000 |
| 4 | 张角 | 2 | 2450 | 4 | 2001 | 3000 |
| 5 | 张宝 | 2 | 800 | 1 | 700 | 1200 |
| 6 | 张梁 | 2 | 5000 | 5 | 3001 | 99999 |
| 7 | 曹操 | 1 | 710 | 1 | 700 | 1200 |
| 8 | 曹丕 | 1 | 3000 | 4 | 2001 | 3000 |
| 9 | 曹植 | 1 | 1300 | 2 | 1201 | 1400 |
| 10 | 曹冲 | 1 | 950 | 1 | 700 | 1200 |
| 11 | 孙权 | 0 | 2000 | 3 | 1401 | 2000 |
+----+------+---------+------+----+-------+-------+
11 rows in set (0.00 sec)例5：
mysql> SELECT e.name, e.sal, s.id FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losat AND s.hisat;
+------+------+----+
| name | sal | id |
+------+------+----+
| 刘备 | 2975 | 4 |
| 关羽 | 1250 | 2 |
| 张飞 | 2850 | 4 |
| 张角 | 2450 | 4 |
| 张宝 | 800 | 1 |
| 张梁 | 5000 | 5 |
| 曹操 | 710 | 1 |
| 曹丕 | 3000 | 4 |
| 曹植 | 1300 | 2 |
| 曹冲 | 950 | 1 |
| 孙权 | 2000 | 3 |
+------+------+----+
11 rows in set (0.00 sec)
-- 查询员工工资大于3000的工资等级
mysql> SELECT e.name, e.sal, s.id FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losat AND s.hisat WHERE e.sal > 3000;
+------+------+----+
| name | sal | id |
+------+------+----+
| 张梁 | 5000 | 5 |
+------+------+----+
1 row in set (0.00 sec)自连接
1、自连接最大的特点是：一张表看做两张表，自己连接自己
2、自连接其实还是两个表连接，只是将一个表用不同的别名，当做两个表
3、自连接适用于一个表中的某个字段的值“来源于”当前表的另一个字段的情况
4、语法为：from 表1 as a [连接形式] join 表1 as b on a.xx字段1=b.xx字段名
例6：查询每个员工的上级领导
mysql> SELECT * FROM emp;
+----+------+---------+------+-----------+
| id | name | dept_id | sal | leader_id |
+----+------+---------+------+-----------+
| 1 | 刘备 | 3 | 2975 | 2 |
| 2 | 关羽 | 3 | 1250 | 1 |
| 3 | 张飞 | 3 | 2850 | 1 |
| 4 | 张角 | 2 | 2450 | 3 |
| 5 | 张宝 | 2 | 800 | 2 |
| 6 | 张梁 | 2 | 5000 | 1 |
| 7 | 曹操 | 1 | 710 | 7 |
| 8 | 曹丕 | 1 | 3000 | 4 |
| 9 | 曹植 | 1 | 1300 | 11 |
| 10 | 曹冲 | 1 | 950 | 5 |
| 11 | 孙权 | 0 | 2000 | 1 |
+----+------+---------+------+-----------+
11 rows in set (0.00 sec)
mysql> SELECT e1.name,e2.name FROM emp e1 INNER JOIN emp e2 ON e1.leader_id = e2.id;
+------+------+
| name | name |
+------+------+
| 刘备 | 关羽 |
| 关羽 | 刘备 |
| 张飞 | 刘备 |
| 张角 | 张飞 |
| 张宝 | 关羽 |
| 张梁 | 刘备 |
| 曹操 | 曹操 |
| 曹丕 | 张角 |
| 曹植 | 孙权 |
| 曹冲 | 张宝 |
| 孙权 | 刘备 |
+------+------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM emp e1 INNER JOIN emp e2 ON e1.leader_id = e2.id;
+----+------+---------+------+-----------+----+------+---------+------+-----------+
| id | name | dept_id | sal | leader_id | id | name | dept_id | sal | leader_id |
+----+------+---------+------+-----------+----+------+---------+------+-----------+
| 1 | 刘备 | 3 | 2975 | 2 | 2 | 关羽 | 3 | 1250 | 1 |
| 2 | 关羽 | 3 | 1250 | 1 | 1 | 刘备 | 3 | 2975 | 2 |
| 3 | 张飞 | 3 | 2850 | 1 | 1 | 刘备 | 3 | 2975 | 2 |
| 4 | 张角 | 2 | 2450 | 3 | 3 | 张飞 | 3 | 2850 | 1 |
| 5 | 张宝 | 2 | 800 | 2 | 2 | 关羽 | 3 | 1250 | 1 |
| 6 | 张梁 | 2 | 5000 | 1 | 1 | 刘备 | 3 | 2975 | 2 |
| 7 | 曹操 | 1 | 710 | 7 | 7 | 曹操 | 1 | 710 | 7 |
| 8 | 曹丕 | 1 | 3000 | 4 | 4 | 张角 | 2 | 2450 | 3 |
| 9 | 曹植 | 1 | 1300 | 11 | 11 | 孙权 | 0 | 2000 | 1 |
| 10 | 曹冲 | 1 | 950 | 5 | 5 | 张宝 | 2 | 800 | 2 |
| 11 | 孙权 | 0 | 2000 | 1 | 1 | 刘备 | 3 | 2975 | 2 |
+----+------+---------+------+-----------+----+------+---------+------+-----------+
11 rows in set (0.00 sec)注：
1、在员工表中：同时记录的员工和其领导的id(有些既是员工又是领导)
2、此时就需要将一张表看做两张表：一张单纯的员工表e1，一张单纯的领导表e2，两个表通过dept_id和id关联
⑴因此：关联的条件为"员工的领导编号=领导的员工编号"
3、这个例子中就充分体现出了给表取别名的重要性了
⑴如果没有给表取别名，那么在查询时数据库都不知道leader_id和id是属于那张表的
外连接查询
1、在内连接查询时，返回的查询结果集中仅是符合查询条件和连接条件的行
2、但是有时候需要包含没有关联的行中的数据，即返回的查询结果集中不仅需要包含符合连接条件的行，还需要包含左表(左外连接或左连接)、右表(右外连接或右连接)或两个连接表(全外连接)中的所有行
3、外连接分为左外连接和右外连接
⑴LEFT JOIN(左外连接)：返回包括左表中的所有记录和右表中连接字段相等的记录
⑵RIGHT JOIN(右外连接)：返回包括右表中的所有记录和左表中连接字段相等的记录
外连接与内连接的区别
1、内连接：
⑴假设A表和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录都将被查询出来
⑵A、B两张表之间没有主副之分，两张表是平等的
⑶也就是：A、B两张表中的数据能匹配上的话(符合连接条件)，那么就查询出来。匹配不上的话，那么就不查询出来
2、外连接
⑴假设A表和B表进行连接，使用外连接的话，A、B两张表中有一张表是主表，一张表是副表，主要查询的是主表中的数据，捎带着查询副表中的数据
⑵当副表中的数据没有和主表中的数据匹配上时，副表将自动模拟出NULL与之匹配
⑶也就是：不管符合不符合连接条件，主表中的所有数据一定会查询出来。副表中的数据如果能匹配，那么就返回匹配的数据；不能匹配的话，就用一个NULL来代替匹配
左外连接
1、左外连接又叫做左连接：表示左边这张表是主表
⑴left关键字之前的表为主表(左边的表为主表)
2、左连接的结果包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配的行，那么右表将使用空值来与之匹配
3、左连接有右连接的写法，右连接也有对应的左连接的写法
4、语法形式：from 表1 left [outer] join 表2 on 连接条件 where 过滤条件
⑴左外连接其实是保证左边表的数据都能够取出的一种连接
⑵左外连接其实是在内连接的基础上，再加上左边表中所有不能满足条件的数据
⑶关键字“outer”可以省略
例7：查询所有员工的部门
mysql> SELECT * FROM emp;
+----+------+---------+------+-----------+
| id | name | dept_id | sal | leader_id |
+----+------+---------+------+-----------+
| 1 | 刘备 | 3 | 2975 | 2 |
| 2 | 关羽 | 3 | 1250 | 1 |
| 3 | 张飞 | 3 | 2850 | 1 |
| 4 | 张角 | 2 | 2450 | 3 |
| 5 | 张宝 | 2 | 800 | 2 |
| 6 | 张梁 | 2 | 5000 | 1 |
| 7 | 曹操 | 1 | 710 | 7 |
| 8 | 曹丕 | 1 | 3000 | 4 |
| 9 | 曹植 | 1 | 1300 | 11 |
| 10 | 曹冲 | 1 | 950 | 5 |
| 11 | 孙权 | 0 | 2000 | 1 |
+----+------+---------+------+-----------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM dept;
+----+------------+
| id | name |
+----+------------+
| 1 | 人力资源部 |
| 2 | 财务部 |
| 3 | 后勤部 |
| 4 | 法务部 |
| 5 | 开发部 |
+----+------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
+----+------+---------+------+-----------+------+------------+
| id | name | dept_id | sal | leader_id | id | name |
+----+------+---------+------+-----------+------+------------+
| 7 | 曹操 | 1 | 710 | 7 | 1 | 人力资源部 |
| 8 | 曹丕 | 1 | 3000 | 4 | 1 | 人力资源部 |
| 9 | 曹植 | 1 | 1300 | 11 | 1 | 人力资源部 |
| 10 | 曹冲 | 1 | 950 | 5 | 1 | 人力资源部 |
| 4 | 张角 | 2 | 2450 | 3 | 2 | 财务部 |
| 5 | 张宝 | 2 | 800 | 2 | 2 | 财务部 |
| 6 | 张梁 | 2 | 5000 | 1 | 2 | 财务部 |
| 1 | 刘备 | 3 | 2975 | 2 | 3 | 后勤部 |
| 2 | 关羽 | 3 | 1250 | 1 | 3 | 后勤部 |
| 3 | 张飞 | 3 | 2850 | 1 | 3 | 后勤部 |
| 11 | 孙权 | 0 | 2000 | 1 | NULL | NULL |
+----+------+---------+------+-----------+------+------------+
11 rows in set (0.00 sec)例8：
mysql> SELECT e.id AS id,e.name AS NAME,e.sal AS sal,d.name AS dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
+----+------+------+------------+
| id | NAME | sal | dept_name |
+----+------+------+------------+
| 7 | 曹操 | 710 | 人力资源部 |
| 8 | 曹丕 | 3000 | 人力资源部 |
| 9 | 曹植 | 1300 | 人力资源部 |
| 10 | 曹冲 | 950 | 人力资源部 |
| 4 | 张角 | 2450 | 财务部 |
| 5 | 张宝 | 800 | 财务部 |
| 6 | 张梁 | 5000 | 财务部 |
| 1 | 刘备 | 2975 | 后勤部 |
| 2 | 关羽 | 1250 | 后勤部 |
| 3 | 张飞 | 2850 | 后勤部 |
| 11 | 孙权 | 2000 | NULL |
+----+------+------+------------+
11 rows in set (0.00 sec)注：
1、使用左(外)连接时，LEFT关键字之前的表为主表(左边的表为主表)：主表中的数据会全部查询出来
⑴主表中的数据在副表中没有与之匹配的，那么将使用NULL来代替
⑵副表中的数据在主表中没有匹配的数据则不会被返回
2、可以看到员工"孙权"在部门表中没有与之匹配的数据(其无部门信息)，但是依旧被查询出来了，且其部门信息使用NULL代替了
⑴部门id为5的数据在副表中且在主表中没有与之匹配的数据，则该条数据不会被返回
3、在SQL语句中还可以在SELECT关键字之后指定返回的数据列：数据列可以来自不同的表
⑴在指定列名时，注意加上列的别名
右外连接
1、右外连接又叫做右连接：表示右边这张表是主表
⑴right关键字之后的表为主表(右边的表为主表)
2、右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值
3、左连接有右连接的写法，右连接也有对应的左连接的写法
4、语法形式：from 表1 right [outer] join 表2 on 连接条件 where 过滤条件
⑴右外连接其实是保证右边表的数据都能够取出的一种连接
⑵右外连接其实是在内连接的基础上，再加上右边表中所有不能满足条件的数据
⑶关键字“outer”可以省略
例9：
mysql> SELECT * FROM emp;
+----+------+---------+------+-----------+
| id | name | dept_id | sal | leader_id |
+----+------+---------+------+-----------+
| 1 | 刘备 | 3 | 2975 | 2 |
| 2 | 关羽 | 3 | 1250 | 1 |
| 3 | 张飞 | 3 | 2850 | 1 |
| 4 | 张角 | 2 | 2450 | 3 |
| 5 | 张宝 | 2 | 800 | 2 |
| 6 | 张梁 | 2 | 5000 | 1 |
| 7 | 曹操 | 1 | 710 | 7 |
| 8 | 曹丕 | 1 | 3000 | 4 |
| 9 | 曹植 | 1 | 1300 | 11 |
| 10 | 曹冲 | 1 | 950 | 5 |
| 11 | 孙权 | 0 | 2000 | 1 |
+----+------+---------+------+-----------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM dept;
+----+------------+
| id | name |
+----+------------+
| 1 | 人力资源部 |
| 2 | 财务部 |
| 3 | 后勤部 |
| 4 | 法务部 |
| 5 | 开发部 |
+----+------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM dept d RIGHT JOIN emp e ON d.id=e.dept_id;
+------+------------+----+------+---------+------+-----------+
| id | name | id | name | dept_id | sal | leader_id |
+------+------------+----+------+---------+------+-----------+
| 1 | 人力资源部 | 7 | 曹操 | 1 | 710 | 7 |
| 1 | 人力资源部 | 8 | 曹丕 | 1 | 3000 | 4 |
| 1 | 人力资源部 | 9 | 曹植 | 1 | 1300 | 11 |
| 1 | 人力资源部 | 10 | 曹冲 | 1 | 950 | 5 |
| 2 | 财务部 | 4 | 张角 | 2 | 2450 | 3 |
| 2 | 财务部 | 5 | 张宝 | 2 | 800 | 2 |
| 2 | 财务部 | 6 | 张梁 | 2 | 5000 | 1 |
| 3 | 后勤部 | 1 | 刘备 | 3 | 2975 | 2 |
| 3 | 后勤部 | 2 | 关羽 | 3 | 1250 | 1 |
| 3 | 后勤部 | 3 | 张飞 | 3 | 2850 | 1 |
| NULL | NULL | 11 | 孙权 | 0 | 2000 | 1 |
+------+------------+----+------+---------+------+-----------+
11 rows in set (0.00 sec)多条件连接查询
1、前面介绍的内连接、外连接例子中都是通过一个连接条件进行的查询
⑴在实际中连接查询也是可以同时存在多个连接条件的
⑶一般多连接条件的的场景是：两个表之间通过多个字段关联，需要所有关联条件均成立
2、在连接查询(内连接、外连接)中，连接条件可以是一个也可以是多个
⑴当连接条件为多个时，可以使用and或or关键字来将多个条件关联起来
⑵and："且"关系，表示所有条件必须同时成立
⑶or："或"关系，表示其中有一个条件成立即可
3、在多连接条件查询中一般用得最多的就是"且"关系：多个条件同时成立时才返回
例10：
SELECT * FROM fruits;
+------+--------+---------+----------+-------------+---------+---------------------+
| f_id | f_name | f_price | f_origin | f_suppliers | f_color | f_time |
+------+--------+---------+----------+-------------+---------+---------------------+
| 1 | 苹果 | 111 | 1 | 1 | 1 | 2022-06-17 20:40:32 |
| 2 | 香蕉 | 121 | 2 | 3 | 2 | 2022-06-17 20:40:35 |
| 3 | 梨 | 131 | 3 | 3 | 3 | 2022-06-17 20:40:38 |
| 4 | 火龙果 | 141 | 2 | 2 | 1 | 2022-06-17 20:40:40 |
| 5 | 榴莲 | 151 | 2 | 3 | 2 | 2022-06-17 20:40:42 |
| 6 | 橘子 | 161 | 1 | 2 | 3 | 2022-06-17 20:40:43 |
+------+--------+---------+----------+-------------+---------+---------------------+
6 rows in set (0.02 sec)
mysql> SELECT * FROM suppliers;
+------+----------------+----------+
| s_id | s_name | s_origin |
+------+----------------+----------+
| 1 | 西南第一水果商 | 成都 |
| 2 | 华东第一水果商 | 山东 |
| 3 | 西北第一水果商 | 山西 |
+------+----------------+----------+
3 rows in set (0.00 sec)
-- 内连接中使用多条件查询
mysql> SELECT * FROM fruits f INNER JOIN suppliers s ON f.f_origin=s.s_id AND f.f_suppliers=s.s_id;
+------+--------+---------+----------+-------------+---------+---------------------+------+----------------+----------+
| f_id | f_name | f_price | f_origin | f_suppliers | f_color | f_time | s_id | s_name | s_origin |
+------+--------+---------+----------+-------------+---------+---------------------+------+----------------+----------+
| 1 | 苹果 | 111 | 1 | 1 | 1 | 2022-06-17 20:40:32 | 1 | 西南第一水果商 | 成都 |
| 3 | 梨 | 131 | 3 | 3 | 3 | 2022-06-17 20:40:38 | 3 | 西北第一水果商 | 山西 |
| 4 | 火龙果 | 141 | 2 | 2 | 1 | 2022-06-17 20:40:40 | 2 | 华东第一水果商 | 山东 |
+------+--------+---------+----------+-------------+---------+---------------------+------+----------------+----------+
3 rows in set (0.00 sec)
-- 外连接中使用多条件查询
mysql> SELECT * FROM fruits f left join suppliers s ON f.f_origin=s.s_id AND f.f_suppliers=s.s_id;
+------+--------+---------+----------+-------------+---------+---------------------+------+----------------+----------+
| f_id | f_name | f_price | f_origin | f_suppliers | f_color | f_time | s_id | s_name | s_origin |
+------+--------+---------+----------+-------------+---------+---------------------+------+----------------+----------+
| 1 | 苹果 | 111 | 1 | 1 | 1 | 2022-06-17 20:40:32 | 1 | 西南第一水果商 | 成都 |
| 4 | 火龙果 | 141 | 2 | 2 | 1 | 2022-06-17 20:40:40 | 2 | 华东第一水果商 | 山东 |
| 3 | 梨 | 131 | 3 | 3 | 3 | 2022-06-17 20:40:38 | 3 | 西北第一水果商 | 山西 |
| 2 | 香蕉 | 121 | 2 | 3 | 2 | 2022-06-17 20:40:35 | NULL | NULL | NULL |
| 5 | 榴莲 | 151 | 2 | 3 | 2 | 2022-06-17 20:40:42 | NULL | NULL | NULL |
| 6 | 橘子 | 161 | 1 | 2 | 3 | 2022-06-17 20:40:43 | NULL | NULL | NULL |
+------+--------+---------+----------+-------------+---------+---------------------+------+----------------+----------+
6 rows in set (0.00 sec)多表连接查询
1、前面介绍的例子都是两张表之间的连接查询，两张以上的表也是可以进行关联查询的
2、多张表的连接查询的基础语法：SELECT * FROM A JOIN B ON 连接条件 JOIN C ON 连接条件
⑴其表示：A表和B表先进行表连接，连接之后A(B)表继续和C表进行连接
⑵也可以理解为：A表和B表先进行表连接，连接之后的结果继续与C表进行连接
3、关于连表查询可以有不同的写法，这里介绍两种
写法一
1、第一种写法就是前面介绍的：SELECT * FROM A JOIN B ON 连接条件 JOIN C ON 连接条件
2、这种写法我自己叫它链式查询：所有的表都是依次关联(两两依次关联)
例11：查询价格大于等于120的水果的名字、供货商名字、水果颜色、水果产地
-- fruits：水果表
-- f_suppliers表示该水果供应商id，关联suppliers表
-- f_origind表示该水果产地，关联fruit_info表
-- f_color表示该水果颜色，关联fruit_info表
SELECT * FROM fruits;
+------+--------+---------+-------------+----------+---------+
| f_id | f_name | f_price | f_suppliers | f_origin | f_color |
+------+--------+---------+-------------+----------+---------+
| 1 | 橙子 | 110 | 1 | 2 | 3 |
| 2 | 苹果 | 120 | 2 | 2 | 3 |
| 3 | 梨 | 130 | 3 | 1 | 2 |
| 4 | 香蕉 | 111 | 3 | 4 | 3 |
| 5 | 橘子 | 122 | 1 | 3 | 1 |
| 6 | 火龙果 | 140 | 2 | 3 | 3 |
+------+--------+---------+-------------+----------+---------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM suppliers;
+------+----------------+--------+-------------+
| s_id | s_name | s_city | s_telephone |
+------+----------------+--------+-------------+
| 1 | 西南第一水果商 | 成都 | 123456 |
| 2 | 华东第一水果商 | 山东 | 234567 |
| 3 | 西北第一水果商 | 山西 | 345678 |
+------+----------------+--------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM fruit_info;
+----+---------+----------+
| id | f_color | f_origin |
+----+---------+----------+
| 1 | red | 四川 |
| 2 | green | 山东 |
| 3 | red | 山西 |
| 4 | pink | 西安 |
+----+---------+----------+
4 rows in set (0.00 sec)
--先关联查询供应商信息
mysql> SELECT f.f_name, f.f_price, s.s_name
-> FROM fruits f
-> LEFT JOIN suppliers s ON f.f_suppliers = s.s_id
-> WHERE f.f_price >= 120;
+--------+---------+----------------+
| f_name | f_price | s_name |
+--------+---------+----------------+
| 橘子 | 122 | 西南第一水果商 |
| 苹果 | 120 | 华东第一水果商 |
| 火龙果 | 140 | 华东第一水果商 |
| 梨 | 130 | 西北第一水果商 |
+--------+---------+----------------+
4 rows in set (0.00 sec)
--继续关联查询颜色信息
mysql> SELECT f.f_name, f.f_price, s.s_name, i1.f_color
-> FROM fruits f
-> LEFT JOIN suppliers s ON f.f_suppliers = s.s_id
-> LEFT JOIN fruit_info i1 ON i1.id = f.f_color
-> WHERE f.f_price >= 120;
+--------+---------+----------------+---------+
| f_name | f_price | s_name | f_color |
+--------+---------+----------------+---------+
| 橘子 | 122 | 西南第一水果商 | red |
| 苹果 | 120 | 华东第一水果商 | red |
| 火龙果 | 140 | 华东第一水果商 | red |
| 梨 | 130 | 西北第一水果商 | green |
+--------+---------+----------------+---------+
4 rows in set (0.00 sec)
--继续关联查询产地信息
mysql> SELECT f.f_name, f.f_price, s.s_name, i1.f_color, i2.f_origin
-> FROM fruits f
-> LEFT JOIN suppliers s ON f.f_suppliers = s.s_id
-> LEFT JOIN fruit_info i1 ON i1.id = f.f_color
-> LEFT JOIN fruit_info i2 ON i2.id = f.f_origin
-> WHERE f.f_price >= 120;
+--------+---------+----------------+---------+----------+
| f_name | f_price | s_name | f_color | f_origin |
+--------+---------+----------------+---------+----------+
| 橘子 | 122 | 西南第一水果商 | red | 山西 |
| 苹果 | 120 | 华东第一水果商 | red | 山东 |
| 火龙果 | 140 | 华东第一水果商 | red | 山西 |
| 梨 | 130 | 西北第一水果商 | green | 四川 |
+--------+---------+----------------+---------+----------+
4 rows in set (0.00 sec)注：
1、上面例11中可以看到fruits关联了两次fruit_info表
⑴这是因为：fruits表分别通过f_origin字段和f_color字段关联fruit_info表
①可以将fruit_info表中的id、f_color和id、f_origin看成两个表中的字段，只是它们的id值一样而已
⑵如果一个表A通过不同的字段来关联同一张表B，那么就需要将被关联的表B看做不同的表来关联多次
⑶比如这里，分别两次将fruit_info表看做i1表和i2表，分两次与fruits表关联
2、在连接查询中最重要的还是要找好表与表之间的关联字段、关联关系，以及需要查询的字段
⑴比如上面例子中：虽然"水果颜色和产地"是在同一张表中(可以一次性查询出颜色和产地)
⑵但是fruits表是通过两个不同的字段来关联的它们，因此必须关联两次(它们相互独立的字段)
⑶两次关联分别查询不同的字段，不能关联一次就两个字段都查询出来
⑷当然，也不是说每次关联只能查询出一个字段，不管被关联的表有多少个字段都是可以查询出来的，只是这个例子有点特殊而已
3、还有就是注意区分多连接条件查询
⑴多连接条件：表示的是条件必须同时成立(且)等
⑵多次连接：每次连接是分来的、独立的互不影响的
例12：错误查询
-- 错误使用多连接条件
mysql> SELECT f.f_name, f.f_price, s.s_name, i1.f_color, i1.f_origin
-> FROM fruits f
-> LEFT JOIN suppliers s ON f.f_suppliers = s.s_id
-> LEFT JOIN fruit_info i1
-> ON i1.id = f.f_color
-> AND i1.id = f.f_origin
-> WHERE f.f_price >= 120;
+--------+---------+----------------+---------+----------+
| f_name | f_price | s_name | f_color | f_origin |
+--------+---------+----------------+---------+----------+
| 橘子 | 122 | 西南第一水果商 | NULL | NULL |
| 苹果 | 120 | 华东第一水果商 | NULL | NULL |
| 火龙果 | 140 | 华东第一水果商 | red | 山西 |
| 梨 | 130 | 西北第一水果商 | NULL | NULL |
+--------+---------+----------------+---------+----------+
4 rows in set (0.00 sec)
-- 错误的一次性查询出所有字段
SELECT f.f_name, f.f_price, s.s_name, i1.f_color, i1.f_origin
-> FROM fruits f
-> LEFT JOIN suppliers s ON f.f_suppliers = s.s_id
-> LEFT JOIN fruit_info i1 ON i1.id = f.f_color
-> WHERE f.f_price >= 120;
+--------+---------+----------------+---------+----------+
| f_name | f_price | s_name | f_color | f_origin |
+--------+---------+----------------+---------+----------+
| 橘子 | 122 | 西南第一水果商 | red | 四川 |
| 苹果 | 120 | 华东第一水果商 | red | 山西 |
| 火龙果 | 140 | 华东第一水果商 | red | 山西 |
| 梨 | 130 | 西北第一水果商 | green | 山东 |
+--------+---------+----------------+---------+----------+
4 rows in set (0.00 sec)
例13：
SELECT * FROM fruits;
+------+--------+---------+-------------+----------+---------+
| f_id | f_name | f_price | f_suppliers | f_origin | f_color |
+------+--------+---------+-------------+----------+---------+
| 1 | 橙子 | 110 | 1 | 2 | 3 |
| 2 | 苹果 | 120 | 2 | 2 | 3 |
| 3 | 梨 | 130 | 3 | 1 | 2 |
| 4 | 香蕉 | 111 | 3 | 4 | 3 |
| 5 | 橘子 | 122 | 1 | 3 | 1 |
| 6 | 火龙果 | 140 | 2 | 3 | 3 |
+------+--------+---------+-------------+----------+---------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM suppliers;
+------+----------------+--------+-------------+
| s_id | s_name | s_city | s_telephone |
+------+----------------+--------+-------------+
| 1 | 西南第一水果商 | 成都 | 123456 |
| 2 | 华东第一水果商 | 山东 | 234567 |
| 3 | 西北第一水果商 | 山西 | 345678 |
+------+----------------+--------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM fruit_color;
+----+---------+
| id | f_color |
+----+---------+
| 1 | yellow |
| 2 | green |
| 3 | red |
| 4 | pink |
+----+---------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM fruit_origin;
+----------+--------+
| originId | origin |
+----------+--------+
| 1 | 四川 |
| 2 | 湖南 |
| 3 | 新疆 |
| 4 | 山西 |
+----------+--------+
4 rows in set (0.00 sec)
mysql> SELECT f.f_name, f.f_price, s.s_name
-> FROM fruits f
-> LEFT JOIN suppliers s ON f.f_suppliers = s.s_id
-> WHERE f.f_price >= 120;
+--------+---------+----------------+
| f_name | f_price | s_name |
+--------+---------+----------------+
| 橘子 | 122 | 西南第一水果商 |
| 苹果 | 120 | 华东第一水果商 |
| 火龙果 | 140 | 华东第一水果商 |
| 梨 | 130 | 西北第一水果商 |
+--------+---------+----------------+
4 rows in set (0.00 sec)
mysql> SELECT f.f_name, f.f_price, s.s_name, o.origin
-> FROM fruits f
-> LEFT JOIN suppliers s ON f.f_suppliers = s.s_id
-> LEFT JOIN fruit_origin o ON f.f_origin = o.originId
-> WHERE f.f_price >= 120;
+--------+---------+----------------+--------+
| f_name | f_price | s_name | origin |
+--------+---------+----------------+--------+
| 橘子 | 122 | 西南第一水果商 | 新疆 |
| 苹果 | 120 | 华东第一水果商 | 湖南 |
| 火龙果 | 140 | 华东第一水果商 | 新疆 |
| 梨 | 130 | 西北第一水果商 | 四川 |
+--------+---------+----------------+--------+
4 rows in set (0.00 sec)
mysql> SELECT f.f_name, f.f_price, s.s_name, o.origin,c.f_color
-> FROM fruits f
-> LEFT JOIN suppliers s ON f.f_suppliers = s.s_id
-> LEFT JOIN fruit_origin o ON f.f_origin = o.originId
-> LEFT JOIN fruit_color c ON f.f_color = c.id
-> WHERE f.f_price >= 120;
+--------+---------+----------------+--------+---------+
| f_name | f_price | s_name | origin | f_color |
+--------+---------+----------------+--------+---------+
| 橘子 | 122 | 西南第一水果商 | 新疆 | yellow |
| 苹果 | 120 | 华东第一水果商 | 湖南 | red |
| 火龙果 | 140 | 华东第一水果商 | 新疆 | red |
| 梨 | 130 | 西北第一水果商 | 四川 | green |
+--------+---------+----------------+--------+---------+
4 rows in set (0.00 sec)例14：查询每一个员工的部门名称以及工资等级
mysql> SELECT * FROM emp;
+----+------+---------+------+-----------+
| id | name | dept_id | sal | leader_id |
+----+------+---------+------+-----------+
| 1 | 刘备 | 3 | 2975 | 2 |
| 2 | 关羽 | 3 | 1250 | 1 |
| 3 | 张飞 | 3 | 2850 | 1 |
| 4 | 张角 | 2 | 2450 | 3 |
| 5 | 张宝 | 2 | 800 | 2 |
| 6 | 张梁 | 2 | 5000 | 1 |
| 7 | 曹操 | 1 | 710 | 7 |
| 8 | 曹丕 | 1 | 3000 | 4 |
| 9 | 曹植 | 1 | 1300 | 11 |
| 10 | 曹冲 | 1 | 950 | 5 |
| 11 | 孙权 | 0 | 2000 | 1 |
+----+------+---------+------+-----------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM dept;
+----+------------+
| id | name |
+----+------------+
| 1 | 人力资源部 |
| 2 | 财务部 |
| 3 | 后勤部 |
| 4 | 法务部 |
| 5 | 开发部 |
+----+------------+
5 rows in set (0.00 sec)
-- 这里emp和dept使用的是内连接，所以"孙权"未返回
mysql> SELECT e.name,d.name,s.id FROM emp e JOIN dept d ON e.dept_id = d.id JOIN salgrade s ON e.sal BETWEEN s.losat AND s.hisat;
+------+------------+----+
| name | name | id |
+------+------------+----+
| 刘备 | 后勤部 | 4 |
| 关羽 | 后勤部 | 2 |
| 张飞 | 后勤部 | 4 |
| 张角 | 财务部 | 4 |
| 张宝 | 财务部 | 1 |
| 张梁 | 财务部 | 5 |
| 曹操 | 人力资源部 | 1 |
| 曹丕 | 人力资源部 | 4 |
| 曹植 | 人力资源部 | 2 |
| 曹冲 | 人力资源部 | 1 |
+------+------------+----+
10 rows in set (0.00 sec)例15：
-- 上面这个例子可以分为两步来思考
-- 先查询员工的部门、工资信息
-- 在查询员工工资等级
mysql> SELECT e.name,d.name,e.sal FROM emp e JOIN dept d ON e.dept_id = d.id;
+------+------------+------+
| name | name | sal |
+------+------------+------+
| 刘备 | 后勤部 | 2975 |
| 关羽 | 后勤部 | 1250 |
| 张飞 | 后勤部 | 2850 |
| 张角 | 财务部 | 2450 |
| 张宝 | 财务部 | 800 |
| 张梁 | 财务部 | 5000 |
| 曹操 | 人力资源部 | 710 |
| 曹丕 | 人力资源部 | 3000 |
| 曹植 | 人力资源部 | 1300 |
| 曹冲 | 人力资源部 | 950 |
+------+------------+------+
10 rows in set (0.00 sec)
-- 这个SQL中关联了salgrade表，因此就可以把s.id添加到返回列中(SELECT关键字后)，如果没有关联salgrade 表，那么返回列中肯定不能有salgrade表中的字段
mysql> SELECT e.name,d.name,e.sal,s.id FROM emp e JOIN dept d ON e.dept_id = d.id JOIN salgrade s ON e.sal BETWEEN s.losat AND s.hisat;
+------+------------+------+----+
| name | name | sal | id |
+------+------------+------+----+
| 刘备 | 后勤部 | 2975 | 4 |
| 关羽 | 后勤部 | 1250 | 2 |
| 张飞 | 后勤部 | 2850 | 4 |
| 张角 | 财务部 | 2450 | 4 |
| 张宝 | 财务部 | 800 | 1 |
| 张梁 | 财务部 | 5000 | 5 |
| 曹操 | 人力资源部 | 710 | 1 |
| 曹丕 | 人力资源部 | 3000 | 4 |
| 曹植 | 人力资源部 | 1300 | 2 |
| 曹冲 | 人力资源部 | 950 | 1 |
+------+------------+------+----+
10 rows in set (0.00 sec)
写法二
1、将查询结果看做一张临时表，然后继续在这个临时表中过滤查询数据、这张临时表继续关联其他表等
2、使用临时表时需要给临时表取别名且临时表中的字段名也需要别名
例16：
mysql> SELECT e.name AS e_name,d.name AS d_name,e.sal AS e_sal FROM emp e JOIN dept d ON e.dept_id = d.id;
+--------+------------+-------+
| e_name | d_name | e_sal |
+--------+------------+-------+
| 刘备 | 后勤部 | 2975 |
| 关羽 | 后勤部 | 1250 |
| 张飞 | 后勤部 | 2850 |
| 张角 | 财务部 | 2450 |
| 张宝 | 财务部 | 800 |
| 张梁 | 财务部 | 5000 |
| 曹操 | 人力资源部 | 710 |
| 曹丕 | 人力资源部 | 3000 |
| 曹植 | 人力资源部 | 1300 |
| 曹冲 | 人力资源部 | 950 |
+--------+------------+-------+
10 rows in set (0.00 sec)
-- 这两个SQL是一样的，只是说下面这个SQL给表和查询结果字段取了别名t1，然后从结果(临时表t1)中查询所有字段
mysql> SELECT t1.* FROM (SELECT e.name AS e_name,d.name AS d_name,e.sal AS e_sal FROM emp e JOIN dept d ON e.dept_id = d.id) AS t1;
+--------+------------+-------+
| e_name | d_name | e_sal |
+--------+------------+-------+
| 刘备 | 后勤部 | 2975 |
| 关羽 | 后勤部 | 1250 |
| 张飞 | 后勤部 | 2850 |
| 张角 | 财务部 | 2450 |
| 张宝 | 财务部 | 800 |
| 张梁 | 财务部 | 5000 |
| 曹操 | 人力资源部 | 710 |
| 曹丕 | 人力资源部 | 3000 |
| 曹植 | 人力资源部 | 1300 |
| 曹冲 | 人力资源部 | 950 |
+--------+------------+-------+
10 rows in set (0.00 sec)
-- 上面结果(临时表)继续关联另一张表,返回t1表中的所有字段和s表中的部分字段
-- 需要注意的是t1表关联salgrade 表时，需要用t1表的字段名来关联了，而不能是e表中的字段名
mysql> SELECT t1.*,s.id FROM (SELECT e.name AS e_name,d.name AS d_name,e.sal AS e_sal FROM emp e JOIN dept d ON e.dept_id = d.id) t1 JOIN salgrade s ON t1.e_sal BETWEEN s.losat AND s.hisat;
+--------+------------+-------+----+
| e_name | d_name | e_sal | id |
+--------+------------+-------+----+
| 刘备 | 后勤部 | 2975 | 4 |
| 关羽 | 后勤部 | 1250 | 2 |
| 张飞 | 后勤部 | 2850 | 4 |
| 张角 | 财务部 | 2450 | 4 |
| 张宝 | 财务部 | 800 | 1 |
| 张梁 | 财务部 | 5000 | 5 |
| 曹操 | 人力资源部 | 710 | 1 |
| 曹丕 | 人力资源部 | 3000 | 4 |
| 曹植 | 人力资源部 | 1300 | 2 |
| 曹冲 | 人力资源部 | 950 | 1 |
+--------+------------+-------+----+
10 rows in set (0.00 sec)例17：
mysql> SELECT f.f_name, f.f_price, s.s_name FROM fruits f LEFT JOIN suppliers s ON f.s_id = s.s_id;
+------------+---------+----------------+
| f_name | f_price | s_name |
+------------+---------+----------------+
| orange | 111 | 西南第一水果商 |
| banana | 101 | 西南第一水果商 |
| huolongguo | 100 | 西南第一水果商 |
| apple | 120 | 华东第一水果商 |
| melon | 130 | 西北第一水果商 |
| grape | 150 | 西北第一水果商 |
+------------+---------+----------------+
6 rows in set (0.00 sec)
--将上面SQL的查询结果看做一张临时表t1
mysql> SELECT t1.*,i1.f_origin AS i1_origin FROM(SELECT f.f_name, f.f_price,f.f_origin,f.f_color, s.s_name FROM fruits f LEFT JOIN suppliers s ON f.s_id = s.s_id) t1 LEFT JOIN fruit_info i1 ON i1.id = t1.f_origin;
+------------+---------+----------+---------+----------------+-----------+
| f_name | f_price | f_origin | f_color | s_name | i1_origin |
+------------+---------+----------+---------+----------------+-----------+
| orange | 111 | 1 | 1 | 西南第一水果商 | 四川 |
| banana | 101 | 1 | 2 | 西南第一水果商 | 四川 |
| huolongguo | 100 | 2 | 2 | 西南第一水果商 | 山东 |
| apple | 120 | 2 | 1 | 华东第一水果商 | 山东 |
| melon | 130 | 3 | 2 | 西北第一水果商 | 山西 |
| grape | 150 | 1 | 3 | 西北第一水果商 | 四川 |
+------------+---------+----------+---------+----------------+-----------+
6 rows in set (0.00 sec)
--继续将上面SQL的查询结果看做一张临时表t2
mysql> SELECT t2.*,i2.f_color AS i2_color FROM (SELECT t1.*,i1.f_origin AS i1_origin FROM(SELECT f.f_name, f.f_price,f.f_origin,f.f_color, s.s_name FROM fruits f LEFT JOIN suppliers s ON f.s_id = s.s_id) t1 LEFT JOIN fruit_info i1 ON i1.id = t1.f_origin) t2 LEFT JOIN fruit_info i2 ON i2.id = t2.f_color WHERE t2.f_price >= 120;
+--------+---------+----------+---------+----------------+-----------+----------+
| f_name | f_price | f_origin | f_color | s_name | i1_origin | i2_color |
+--------+---------+----------+---------+----------------+-----------+----------+
| apple | 120 | 2 | 1 | 华东第一水果商 | 山东 | yellow |
| melon | 130 | 3 | 2 | 西北第一水果商 | 山西 | green |
| grape | 150 | 1 | 3 | 西北第一水果商 | 四川 | red |
+--------+---------+----------+---------+----------------+-----------+----------+
3 rows in set (0.00 sec)注：
1、上面两种写法都是可以到达同一目的的：只是说第二种的写法稍微有点复杂(只是介绍下有这种写法，主要是想加强下把查询结果看做临时表的这种思想)
⑴将查询结果看做一张临时表，然后继续在这个临时表中过滤查询数据、这张临时表继续关联其他表等
⑵使用临时表时需要给临时表取别名且临时表中的字段名也需要别名
2、多张表之间的关联查询最好还是使用第一种的写法，毕竟这种写法要方便点
例18：
注：
1、使用第二种写法的好处就是：可以先过滤出数据在关联查询
⑴使用第一种写法"链式关联"：是一次性将所有表都关联上了，最后在过滤数据(WHERE关键字必须位于ON关键字之后)。这样的话在关联时不符合条件的数据也会进行关联匹配
⑵使用第二种写法：可以先将数据过滤出来。这样就只有符合条件的数据才会进行关联匹配
⑶当然，第二种中的减少匹配次数指的是非第一次关联：第一次关联肯定是关联再过滤(WHERE关键字必须位于ON关键字之后)
备注
例19：
-- 这里emp和dept使用的是左连接，所以"孙权"返回
-- 需要注意LEFT写的位置：emp和dept左连接，emp与salgrade依旧是内连接
mysql> SELECT e.name,d.name,s.id FROM emp e LEFT JOIN dept d ON e.dept_id = d.id JOIN salgrade s ON e.sal BETWEEN s.losat AND s.hisat;
+------+------------+----+
| name | name | id |
+------+------------+----+
| 刘备 | 后勤部 | 4 |
| 关羽 | 后勤部 | 2 |
| 张飞 | 后勤部 | 4 |
| 张角 | 财务部 | 4 |
| 张宝 | 财务部 | 1 |
| 张梁 | 财务部 | 5 |
| 曹操 | 人力资源部 | 1 |
| 曹丕 | 人力资源部 | 4 |
| 曹植 | 人力资源部 | 2 |
| 曹冲 | 人力资源部 | 1 |
| 孙权 | NULL | 3 |
+------+------------+----+
11 rows in set (0.00 sec)例19：查询所有员工的部门、工资等级以及部门领导
-- 一定要注意LEFT的位置
mysql> SELECT e.name '员工',d.name,s.id,e1.name '领导' FROM emp e LEFT JOIN dept d ON e.dept_id = d.id JOIN salgrade s ON e.sal BETWEEN s.losat AND s.hisat LEFT JOIN emp e1 ON e.leader_id=e1.id;
+------+------------+----+------+
| 员工 | name | id | 领导 |
+------+------------+----+------+
| 刘备 | 后勤部 | 4 | 关羽 |
| 关羽 | 后勤部 | 2 | 刘备 |
| 张飞 | 后勤部 | 4 | 刘备 |
| 张角 | 财务部 | 4 | 张飞 |
| 张宝 | 财务部 | 1 | 关羽 |
| 张梁 | 财务部 | 5 | 刘备 |
| 曹操 | 人力资源部 | 1 | 曹操 |
| 曹丕 | 人力资源部 | 4 | 张角 |
| 曹植 | 人力资源部 | 2 | 孙权 |
| 曹冲 | 人力资源部 | 1 | 张宝 |
| 孙权 | NULL | 3 | 刘备 |
+------+------------+----+------+
11 rows in set (0.00 sec)注：
扫一扫
7818
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
