mysql连接查询

MySQL连接查询

在项目中的数据组成往往是复合的，所以单独的一张表中的数据难以支撑数据的完整显示，所以这个时候就需要用到连接查询，把多个表中的数据联合起来显示，以达到我们的预期。

而且，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。

不同的数据分析工具	支持的表连接方式
Oracle/ sql server/ Tableau/ Python	内连接(inner join)、左连接(left join)、右连接(right join)、全连接(full join)
MySQL	内连接(inner join)、左连接(left join)、右连接(right join)
Power BI	内连接、左连接、右连接、全连接、左反连接、右反连接

准备测试数据

drop table if exists team;
create table team(
    id int not null AUTO_INCREMENT PRIMARY KEY comment '组id', 	     team_name varchar(32) not null default '' comment '名称'
) comment '组表';

drop table if exists employee;
create table employee(
    id int not null AUTO_INCREMENT PRIMARY KEY comment '部门id',     emp_name varchar(32) not null default '' comment '员工名称',       team_id int not null default 0 comment '员工所在组id'
) comment '员工表表';

insert into team values (1,'架构组'),(2,'测试组'),(3,'前端组'),(4,'后端组'); 
insert into employee values (1,'李二',1),(2,'张三',2),(3,'李四',3),(4,'王五',0),(5,'赵六',0);

#team表4条记录
mysql> select * from team;
+----+-----------+
| id | team_name |
+----+-----------+
|  1 | 架构组    |
|  2 | 测试组    |
|  3 | 前端组    |
|  4 | 后端组    |
+----+-----------+
4 rows in set (0.00 sec)

#employee表5条记录
mysql> select * from employee;
+----+----------+---------+
| id | emp_name | team_id |
+----+----------+---------+
|  1 | 李二     |       1 |
|  2 | 张三     |       2 |
|  3 | 李四     |       3 |
|  4 | 王五     |       0 |
|  5 | 赵六     |       0 |
+----+----------+---------+
5 rows in set (0.00 sec)

内连接

语法：

#sql99语法,inner可以省略
select 字段 from 表1 [inner] join on 连接条件；

#sql92语法
select 字段 from 表1，表2 [where 关联条件]；

内连接相当于在笛卡尔积的基础上加上了连接的条件。

当没有连接条件的时候，内连接上升为笛卡尔积。

分类也可以大致分为等值或者非等值连接，等值就是on后面条件用等号，非等值on后面接一些其他条件，比如说on a between 字段1 and 字段2

示例1：有连接条件

查询出员工的名字和所属部门

#inner可以省略
mysql> select emp_name,team_name from team a inner join employee b on a.id = b.team_id;
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
| 张三     | 测试组    |
| 李四     | 前端组    |
+----------+-----------+
3 rows in set (0.00 sec)

mysql> select emp_name,team_name from team a,employee b where a.id = b.team_id;
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
| 张三     | 测试组    |
| 李四     | 前端组    |
+----------+-----------+
3 rows in set (0.00 sec)

上面的查询，相当于获取两张表的交集

示例2：无连接条件

无连接条件时，内连接为笛卡尔积

mysql> select emp_name,team_name from employee join team;
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
| 李二     | 测试组    |
| 李二     | 前端组    |
| 李二     | 后端组    |
| 张三     | 架构组    |
| 张三     | 测试组    |
| 张三     | 前端组    |
| 张三     | 后端组    |
| 李四     | 架构组    |
| 李四     | 测试组    |
| 李四     | 前端组    |
| 李四     | 后端组    |
| 王五     | 架构组    |
| 王五     | 测试组    |
| 王五     | 前端组    |
| 王五     | 后端组    |
| 赵六     | 架构组    |
| 赵六     | 测试组    |
| 赵六     | 前端组    |
| 赵六     | 后端组    |
+----------+-----------+
20 rows in set (0.00 sec)

示例3：组合条件查询

查询架构组的员工名

mysql> select emp_name,team_name from team a inner join employee b on a.id = b.team_id and a.team_name='架构组';
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
+----------+-----------+
1 row in set (0.00 sec)

mysql> select emp_name,team_name from team a inner join employee b on a.id = b.team_id where a.team_name='架构组';
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
+----------+-----------+
1 row in set (0.00 sec)

mysql> select emp_name,team_name from team a,employee b where a.id = b.team_id and a.team_name='架构组';
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
+----------+-----------+
1 row in set (0.00 sec)

方法1：on中使用组合条件

方法2：对连接之后的结果在进行过滤。先获得连接之后的结果，再使用where中的条件对连接结果进行过滤。

方法3：直接在where后面进行过滤

外连接

外连接涉及两表，分为：主表和从表，要查询的信息主要来自于哪个表，谁就是主表。

外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。

最终：外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。

外连接分为2种：

左连接（左外连接）：

​ 关键字：left join on / left outer join on

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右连接（右外连接）：

​ 关键字：right join on / right outer join on

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。

与左(外)连接相反，右(外)连接，左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来。左表记录不足的地方均为NULL。

左连接

语法：

select 字段 from 主表 left join 从表 on 连接条件;

示例1：

查询所有员工名字和所在组

mysql> select a.emp_name,b.team_name from employee a left join team b  on a.team_id = b.id;
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
| 张三     | 测试组    |
| 李四     | 前端组    |
| 王五     | NULL      |
| 赵六     | NULL      |
+----------+-----------+
5 rows in set (0.00 sec)

左边的employee作为主表，右边记录不足的则用null标识了

示例2：

继续增加对示例1的要求，要求结果不带null

#对示例1连接查询后的语句进行where过滤，或是直接使用内连接也可以达到效果
#增加where条件
mysql> select a.emp_name,b.team_name from employee a left join team b  on a.team_id = b.id where b.team_name is not null;
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
| 张三     | 测试组    |
| 李四     | 前端组    |
+----------+-----------+
3 rows in set (0.00 sec)

右连接

语法：

select 列 from 从表 right join 主表 on 连接条件;

示例：

使用右连接来实现上面的示例1，其实只需要将left改为right,在将左右两边的表换位置即可

mysql> select a.emp_name,b.team_name from team b right join employee a on a.team_id = b.id;
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
| 张三     | 测试组    |
| 李四     | 前端组    |
| 王五     | NULL      |
| 赵六     | NULL      |
+----------+-----------+
5 rows in set (0.00 sec)

mysql> select a.emp_name,b.team_name from team b right join employee a on a.team_id = b.id where b.team_name is not null;
+----------+-----------+
| emp_name | team_name |
+----------+-----------+
| 李二     | 架构组    |
| 张三     | 测试组    |
| 李四     | 前端组    |
+----------+-----------+
3 rows in set (0.00 sec)

扩展

表连接中还可以使用分页和排序，分组查询，函数等等各种条件

jion两边的表并不是表名，也可以是子查询

多表查询也是一样的，只是多个join。