MySQL多表关联查询方式及实际应用

目录

• 一、关联查询方式
• 1、 inner join--内连接
• 2、left join--左连接
• 3、 right join--右连接
• 4、自连接
• 5、交叉连接
• 6、子查询
• 二、关联查询应用
• 1、inner join--内连接
• 2、left join--左连接
• 3、right join--右连接
• 4、自连接
• 5、交叉连接
• 6、子查询
• 7、group_concat（字段名）函数的使用 
• 总结

一、关联查询方式

1、 inner join--内连接

代表选择的是两个表的交差部分。

内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。

基本语法

SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

2、left join--左连接

代表选择的是前面一个表的全部。

左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。

基本语法

SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

3、 right join--右连接

代表选择的是后面一个表的全部

同理，右连接将会以右边作为基准，进行检索。

基本语法

SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

4、自连接

自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)。

基本语法

SELECT 字段名列表  FROM 表1 别名1,表名1 别名2 
WHERE 别名1.字段名=别名1.字段名;

5、交叉连接

不适用任何匹配条件。生成笛卡尔积

基本语法

SELECT 列表名 FROM 表名1,表名2;

6、子查询

1）：子查询是将一个查询语句嵌套在另一个查询语句中。内部嵌套其他select语句的查询，称为外查询或主查询

2）：内层查询语句的查询结果，可以为外层查询语句提供查询条件。

3）：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

4）：还可以包含比较运算符：= 、 !=、> 、<等

基本语法

SELECT 字段列表 FROM 表名 
WHERE 字段名=(SELECT 字段名 FROM 表名);

二、关联查询应用

基于这三个表

mysql> select * from dept3;
+--------+-----------+
| deptno | name      |
+--------+-----------+
| 1001   | 研发部    |
| 1002   | 销售部    |
| 1003   | 财务部    |
| 1004   | 人事部    |
+--------+-----------+
4 rows in set (0.00 sec)

mysql> select * from emp3;
+-----+--------------+------+---------+
| eid | ename        | age  | dept_id |
+-----+--------------+------+---------+
| 1   | 乔峰         |   20 | 10php01    |
| 10  | 丁春秋       |   71 | 1005    |
| 2   | 段誉         |   21 | 1001    |
| 3   | 虚竹         |   23 | 1001    |
| 4   | 阿紫         |   18 | 1001    |
| 5   | 扫地僧       |   85 | 1002    |
| 6   | 李秋水       |   33 | 1002    |
| 7   | 鸠摩智       |   50 | 1002    |
| 8   | 天山童姥     |   60 | 1003    |
| 9   | 慕容博       |   58 | 1003    |
+-----+--------------+------+---------+
10 rows in set (0.00 sec)
​
mysql> select * from emp;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm  | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
|  1001 | 甘宁      | 文员      | 1013 | 2000-12-17 |  8000 |  NULL |     20 |
|  1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000 |  3000 |     30 |
|  1003 | 殷天正    | 销售员    | 1006 | 2001-02-22 | 12500 |  5000 |     30 |
|  1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750 |  NULL |     20 |
|  1005 | 谢逊      | 销售员    | 1006 | 2001-09-28 | 12500 | 14000 |     30 |
|  1006 | 关羽      | 经理      | 1009 | 2001-05-01 | 28500 |  NULL |     30 |
|  1007 | 张飞      | 经理      | 1009 | 2001-09-01 | 24500 |  NULL |     10 |
|  1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000 |  NULL |     20 |
|  1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000 |  NULL |     10 |
|  1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000 |     0 |     30 |
|  1011 | 周泰      | 文员      | 1006 | 2007-05-23 | 11000 |  NULL |     20 |
|  1012 | 程普      | 文员      | 1006 | 2001-12-03 |  9500 |  NULL |     30 |
|  1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000 |  NULL |     20 |
|  1014 | 黄盖      | 文员      | 1007 | 2002-01-23 | 13000 |  NULL |     10 |
|  1015 | 张三      | 保洁员    | 1001 | 2013-05-01 | 80000 | 50000 |     50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.00 sec)

​

1、inner join--内连接

查询销售部的员工信息。

mysql> select name,emp3.* from dept3 inner join emp3 
    -> on dept3.deptno=emp3.dept_id
    -> where name='销售部';
QrPjmYz+-----------+-----+-----------+------+---------+
| name      | eid | ename     | age  | dept_id |
+-----------+-----+-----------+------+---------+
| 销售部    | 5   | 扫地僧    |   85 | 1002    |
| 销售部    | 6   | 李秋水    |   33 | 1002    |
| 销售部    | 7   | 鸠摩智    |   50 | 1002    |
+-----------+-----+-----------+------+---javascript------+
3 rows in set (0.00 sec)

2、left join--左连接

查看每员工所对应的部门。

mysql> select emp3.*,name from emp3 left join dept3 
    -> on emp3.dept_id=dept3.deptno;
+-----+--------------+------+---------+-----------+
| eid | ename        | age  | dept_id | name      |
+-----+--------------+------+---------+-----------+
| 1   | 乔峰         |   20 | 1001    | 研发部    |
| 2   | 段誉         |   21 | 1001    | 研发部    |
| 3   | 虚竹         |   23 | 1001    | 研发部    |
| 4   | 阿紫         |   18 | 1001    | 研发部    |
| 5   | 扫地僧       |   85 | 1002    | 销售部    |
| 6   | 李秋水       |   33 | 1002    | 销售部    |
| 7   | 鸠摩智       |   50 | 1002    | 销售部    |
| 8   | 天山童姥     |   60 | 1003    | 财务部    |
| 9   | 慕容博       |   58 | 1003    | 财务部    |
| 10  | 丁春秋       |   71 | 1005    | NULL      |
+-----+--------------+------+---------+-----------+
10 rows in set (0.00 sec)

3、right join--右连接

查看每个部门对应的员工信息。

mysql> select name,emp3.* from dept3 right join emp3 
    -> on dept3.deptno=emp3.dept_id;
+-----------+-----+--------------+------+---------+
| name      | eid | ename        | age  | dept_id |
+-----------+-----+--------------+------+---------+
| 研发部    | 1   | 乔峰         |   20 | 1001    |
| 研发部    | 2   | 段誉    编程     |   21 | 1001    |
| 研发部    | 3   | 虚竹         |   23 | 1001    |
| 研发部    | 4   | 阿紫         |   18 | 1001    |
| 销售部    | 5   | 扫地僧       |   85 | 1002    |
| 销售部    | 6   | 李秋水       |   33 | 1002    |
| 销售部    | 7   | 鸠摩智       |   50 | 1002    |
| 财务部    | 8   | 天山童姥     |   60 | 1003    |
| 财务部    | 9   | 慕容博       |   58 | 1003    |
| NULL      | 10  | 丁春秋       |   71 | 1005    |
+-----------+-----+--------------+------+---------+

4、自连接

在emp表中查询姓名的上级领导（mgr）的名字。

mysql> select a.ename,b.ename 领导名字 from emp a,emp b 
    -> where a.mgr=b.empno;
+-----------+--------------+
| ename     | 领导名字     |
+-----------+--------------+
| 甘宁      | 庞统         |
| 黛绮丝    | 关羽         |
| 殷天正    | 关羽         |
| 刘备      | 曾阿牛       |
| 谢逊      | 关羽         |
| 关羽      | 曾阿牛       |
| 张飞      | 曾阿牛       |
| 诸葛亮    | 刘备         |
| 韦一笑    | 关羽         |
| 周泰      | 关羽         |
| 程普      | 关羽         |
| 庞统      | 刘备         |
| 黄盖      | 张飞         |
| 张三      | 甘宁         |
+-----------+--------------+
14 rows in set (0.00 sec)

5、交叉连接

mysql> select * from emp3,dept3;
+-----+--------------+------+---------+--------+-----------+
| eid | ename        | age  | dept_id | deptno | name      |
+-----+--------------+------+---------+--------+-----------+
| 1   | 乔峰         |   20 | 1001    | 1001   | 研发部    |
| 1   | 乔峰         |   20 | 1001    | 1002   | 销售部    |
| 1   | 乔峰         |   20 | 1001    | 1003   | 财务部    |
| 1   | 乔峰         |   20 | 1001    | 1004   | 人事部    |
| 10  | 丁春秋       |   71 | 1005    | 1001   | 研发部    |
| 10  | 丁春秋       |   71 | 1005    | 1002   | 销售部    |
| 10  | 丁春秋       |   71 | 1005    | 1003   | 财务部    |
| 10  | 丁春秋       |   71 | 1005    | 1004   | 人事部    |
| 2   | 段誉         |   21 | 1001    | 1001   | 研发部    |
| 2   | 段誉         |   21 | 1001    | 1002   | 销售部    |
| 2   | 段誉         |   21 | 1001    | 1003   | 财务部    |
| 2   | 段誉         |   21 | 1001    | 1004   | 人事部    |
| 3   | 虚竹         |   23 | 1001    | 1001   | 研发部    |
| 3   | 虚竹         |   23 | 1001    | 1002   | 销售部    |
| 3   | 虚竹         |   23 | 1001    | 1003   | 财务部    |
| 3   | 虚竹         |   23 | 1001    | 1004   | 人事部    |
| 4   | 阿紫         |   18 | 1001    | 1001   | 研发部php    |
| 4   | 阿紫         |   18 | 1001    | 1002   | 销售部    |
| 4   | 阿紫         |   18 | 1001    | 1003   | 财务部    |
| 4   | 阿紫         |   18 | 1001    | 1004   | 人事部    |
| 5   | 扫地僧       |   85 | 1002    | 1001   | 研发部    |
| 5   | 扫地僧       |   85 | 1002    | 1002   | 销售部    |
| 5   | 扫地僧       |   85 | 1002    | 1003   | 财务部    |
| 5   | 扫地僧       |   85 | 1002    | 1004   | 人事部    |
| 6   | 李秋水       |   33 | 1002    | 1001   | 研发部    |
| 6   | 李秋水       |   33 | 1002    | 1002   | 销售部    |
| 6   | 李秋水       |   33 | 1002    | 1003   | 财务部    |
| 6   | 李秋水       |   33 | 1002    | 1004   | 人事部    |
| 7   | 鸠摩智       |   50 | 1002    | 1001   | 研发部    |
| 7   | 鸠摩智       |   50 | 1002    | 1002   | 销售部    |
| 7   | 鸠摩智       |   50 | 1002    | 1003   | 财务部    |
| 7   | 鸠摩智       |   50 | 1002    | 1004   | 人事部    |
| 8   | 天山童姥     |   60 | 1003    | 1001   | 研发部    |
| 8   | 天山童姥     |   60 | 1003    | 1002   | 销售部    |
| 8   | 天山童姥     |   60 | 1003    | 1003   | 财务部    |
| 8   | 天山童姥     |   60 | 1003    | 1004   | 人事部    |
| 9   | 慕容博       |   58 | 1003    | 1001   | 研发部    |
| 9   | 慕容博       |   58 | 1003    | 1002   | 销售部    |
| 9   | 慕容博       |   58 | 1003    | 1003   | 财务部    |
| 9   | 慕容博       |   58 | 1003    | 1004   | 人事部    |
+-----+--------------+------+---------+--------+-----------+
40 rows in set (0.00 sec)

6、子查询

在emp3表中年龄大于平均年龄的姓名。

mysql> select ename,age from emp3   
    -> where age>(select avg(age) from emp3);
+--------------+------+
| ename        | age  |
+--------------+------+
| 丁春秋       |   71 |
| 扫地僧       |   85 |
| 鸠摩智       |   50 |
| 天山童姥     |   60 |
| 慕容博       |   58 |
+--------------+------+
5 rows in set (0.00 sec)

#验证是否查询正确，看看平均年龄是多少
mysql> select avg(age) 平均年龄 from emp3;
+--------------+
| 平均年龄     |
+--------------+
|      43.9000 |
+--------------+
1 row in set (0.00 sec)

7、group_concat（字段名）函数的使用 

查询每个部门的员工姓名。

emp表

mysql> select job,group_concat(ename) from emp group by job;
+-----------+--------------------------------------+
| job       | group_concat(ename)                  |
+-----------+--------------------------------------+
| 保洁员    | 张三                                 |
| 分析师    | 诸葛亮,庞统                          |
| 文员      | 甘宁,周泰,程普,黄盖                  |
| 经理      | 刘备,关羽,张飞                       |
| 董事长    | 曾阿牛                               |
| 销售员    | 黛绮丝,殷天正,谢逊,韦一笑            |
+-----------+--------------------------------------+
6 rows in set (0.00 sec)

emp3和dept3表 

mysql> select name,group_concat(ename) from dept3 left join emp3 
    -> on dept3.deptno=emp3.dept_id
    -> group by dept3.name;
+-----------+-------------------------------+
| name      | group_concat(ename)           |
+-----------+-------------------------------+
| 人事部    | NULL                          |
| 研发部    | 段誉,阿紫,乔峰,虚竹           |
| 财务部    | 天山童姥,慕容博               |
| 销售部    | 李秋水,扫地僧,鸠摩智          |
+-----------+-------------------------------+
4 rows in set (0.00 sec)

 注：在使用的过程中根据需求联合使用。 

总结

到此这篇关于MySQL多表关联查询方式及实际应用的文章就介绍到这了,更多相关MySQL多表关联查询内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！