行业知识
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 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 | 1001 || 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='销售部';+-----------+-----+-----------+------+---------+| name | eid | ename | age | dept_id |+-----------+-----+-----------+------+---------+| 销售部 | 5 | 扫地僧 | 85 | 1002 || 销售部 | 6 | 李秋水 | 33 | 1002 || 销售部 | 7 | 鸠摩智 | 50 | 1002 |+-----------+-----+-----------+------+---------+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 | 研发部 || 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)
注：在使用的过程中根据需求联合使用。
下一篇：电脑服务器必学指令怎么用?
