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)
注：在使用的过程中根据需求联合使用。
