知新_RL

这个屌丝很懒，什么也没留下！

热门标签

MySQL多表关联查询_mysql 多表关联

作者：知新_RL | 2024-02-29 22:12:56

踩

mysql 多表关联

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 | 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)

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/168808