系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

MySQL多表查询实例详解【链接查询、子查询等】

时间:2019-12-22来源:系统城作者:电脑系统城

本文实例讲述了MySQL多表查询。分享给大家供大家参考,具体如下:

准备工作:准备两张表,部门表(department)、员工表(employee)


 
  1. create table department(
  2. id int,
  3. name varchar(20)
  4. );
  5. create table employee(
  6. id int primary key auto_increment,
  7. name varchar(20),
  8. sex enum('male','female') not null default 'male',
  9. age int,
  10. dep_id int
  11. );
  12.  

 
  1. #插入数据
  2. insert into department values
  3. (200,'技术'),
  4. (201,'人力资源'),
  5. (202,'销售'),
  6. (203,'运营');
  7. insert into employee(name,sex,age,dep_id) values
  8. ('egon','male',18,200),
  9. ('alex','female',48,201),
  10. ('wupeiqi','male',38,201),
  11. ('yuanhao','female',28,202),
  12. ('nvshen','male',18,200),
  13. ('xiaomage','female',18,204)
  14. ;
  15.  

 
  1. # 查看表结构和数据
  2. mysql> desc department;
  3. +-------+-------------+------+-----+---------+-------+
  4. | Field | Type | Null | Key | Default | Extra |
  5. +-------+-------------+------+-----+---------+-------+
  6. | id | int(11) | YES | | NULL | |
  7. | name | varchar(20) | YES | | NULL | |
  8. +-------+-------------+------+-----+---------+-------+
  9. 2 rows in set (0.19 sec)
  10.  

 
  1. mysql> desc employee;
  2. +--------+-----------------------+------+-----+---------+----------------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +--------+-----------------------+------+-----+---------+----------------+
  5. | id | int(11) | NO | PRI | NULL | auto_increment |
  6. | name | varchar(20) | YES | | NULL | |
  7. | sex | enum('male','female') | NO | | male | |
  8. | age | int(11) | YES | | NULL | |
  9. | dep_id | int(11) | YES | | NULL | |
  10. +--------+-----------------------+------+-----+---------+----------------+
  11. 5 rows in set (0.01 sec)
  12.  

 
  1. mysql> select * from department;
  2. +------+--------------+
  3. | id | name |
  4. +------+--------------+
  5. | 200 | 技术 |
  6. | 201 | 人力资源 |
  7. | 202 | 销售 |
  8. | 203 | 运营 |
  9. +------+--------------+
  10. 4 rows in set (0.02 sec)
  11.  

 
  1. mysql> select * from employee;
  2. +----+----------+--------+------+--------+
  3. | id | name | sex | age | dep_id |
  4. +----+----------+--------+------+--------+
  5. | 1 | egon | male | 18 | 200 |
  6. | 2 | alex | female | 48 | 201 |
  7. | 3 | wupeiqi | male | 38 | 201 |
  8. | 4 | yuanhao | female | 28 | 202 |
  9. | 5 | nvshen | male | 18 | 200 |
  10. | 6 | xiaomage | female | 18 | 204 |
  11. +----+----------+--------+------+--------+
  12. 6 rows in set (0.00 sec)
  13.  

ps:观察两张表,发现department表中id=203部门在employee中没有对应的员工,发现employee中id=6的员工在department表中没有对应关系。

一多表链接查询

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

(1)先看第一种情况交叉连接:不适用任何匹配条件。生成笛卡尔积.--->重复最多


 
  1. mysql> select * from employee,department;
  2. +----+----------+--------+------+--------+------+--------------+
  3. | id | name | sex | age | dep_id | id | name |
  4. +----+----------+--------+------+--------+------+--------------+
  5. | 1 | egon | male | 18 | 200 | 200 | 技术 |
  6. | 1 | egon | male | 18 | 200 | 201 | 人力资源 |
  7. | 1 | egon | male | 18 | 200 | 202 | 销售 |
  8. | 1 | egon | male | 18 | 200 | 203 | 运营 |
  9. | 2 | alex | female | 48 | 201 | 200 | 技术 |
  10. | 2 | alex | female | 48 | 201 | 201 | 人力资源 |
  11. | 2 | alex | female | 48 | 201 | 202 | 销售 |
  12. | 2 | alex | female | 48 | 201 | 203 | 运营 |
  13. | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
  14. | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
  15. | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
  16. | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
  17. | 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
  18. | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
  19. | 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
  20. | 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
  21. | 5 | nvshen | male | 18 | 200 | 200 | 技术 |
  22. | 5 | nvshen | male | 18 | 200 | 201 | 人力资源 |
  23. | 5 | nvshen | male | 18 | 200 | 202 | 销售 |
  24. | 5 | nvshen | male | 18 | 200 | 203 | 运营 |
  25. | 6 | xiaomage | female | 18 | 204 | 200 | 技术 |
  26. | 6 | xiaomage | female | 18 | 204 | 201 | 人力资源 |
  27. | 6 | xiaomage | female | 18 | 204 | 202 | 销售 |
  28. | 6 | xiaomage | female | 18 | 204 | 203 | 运营 |
  29.  

(2)内连接:只连接匹配的行,以双方为基准


 
  1. #找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了匹配的结果
  2. #department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来
  3. mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
  4. +----+---------+------+--------+--------------+
  5. | id | name | age | sex | name |
  6. +----+---------+------+--------+--------------+
  7. | 1 | egon | 18 | male | 技术 |
  8. | 2 | alex | 48 | female | 人力资源 |
  9. | 3 | wupeiqi | 38 | male | 人力资源 |
  10. | 4 | yuanhao | 28 | female | 销售 |
  11. | 5 | nvshen | 18 | male | 技术 |
  12. +----+---------+------+--------+--------------+
  13. 5 rows in set (0.00 sec)
  14.  

 
  1. #上述sql等同于
  2. mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
  3.  

 (3)外链接之左连接:优先显示左表全部记录


 
  1. #以左表为准,即找出所有员工信息,当然包括没有部门的员工
  2. #本质就是:在内连接的基础上增加左边有,右边没有的结果
  3. mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
  4. +----+----------+--------------+
  5. | id | name | depart_name |
  6. +----+----------+--------------+
  7. | 1 | egon | 技术 |
  8. | 5 | nvshen | 技术 |
  9. | 2 | alex | 人力资源 |
  10. | 3 | wupeiqi | 人力资源 |
  11. | 4 | yuanhao | 销售 |
  12. | 6 | xiaomage | NULL |
  13. +----+----------+--------------+
  14. 6 rows in set (0.00 sec)
  15.  

(4) 外链接之右连接:优先显示右表全部记录


 
  1. #以右表为准,即找出所有部门信息,包括没有员工的部门
  2. #本质就是:在内连接的基础上增加右边有,左边没有的结果
  3. mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
  4. +------+---------+--------------+
  5. | id | name | depart_name |
  6. +------+---------+--------------+
  7. | 1 | egon | 技术 |
  8. | 2 | alex | 人力资源 |
  9. | 3 | wupeiqi | 人力资源 |
  10. | 4 | yuanhao | 销售 |
  11. | 5 | nvshen | 技术 |
  12. | NULL | NULL | 运营 |
  13. +------+---------+--------------+
  14. 6 rows in set (0.00 sec)
  15.  

(5) 全外连接:显示左右两个表全部记录(了解)

#外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意:mysql不支持全外连接 full JOIN
#强调:mysql可以使用此种方式间接实现全外连接

语法:select * from employee left join department on employee.dep_id = department.id
       union all
      select * from employee right join department on employee.dep_id = department.id;


 
  1. mysql> select * from employee left join department on employee.dep_id = department.id
  2. union
  3. select * from employee right join department on employee.dep_id = department.id
  4. ;
  5. +------+----------+--------+------+--------+------+--------------+
  6. | id | name | sex | age | dep_id | id | name |
  7. +------+----------+--------+------+--------+------+--------------+
  8. | 1 | egon | male | 18 | 200 | 200 | 技术 |
  9. | 5 | nvshen | male | 18 | 200 | 200 | 技术 |
  10. | 2 | alex | female | 48 | 201 | 201 | 人力资源 |
  11. | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
  12. | 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
  13. | 6 | xiaomage | female | 18 | 204 | NULL | NULL |
  14. | NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
  15. +------+----------+--------+------+--------+------+--------------+
  16. 7 rows in set (0.01 sec)
  17.  
  18. #注意 union与union all的区别:union会去掉相同的纪录
  19.  

二、符合条件连接查询

以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门


 
  1. select employee.name,department.name from employee inner join department
  2.   on employee.dep_id = department.id
  3.   where age > 25;
  4.  

三、子查询

#1:子查询是将一个查询语句嵌套在另一个查询语句中。
#2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
#3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4:还可以包含比较运算符:= 、 !=、> 、<等

(1)带in关键字的子查询


 
  1. #查询平均年龄在25岁以上的部门名
  2. select id,name from department
  3. where id in
  4. (select dep_id from employee group by dep_id having avg(age) > 25);
  5. # 查看技术部员工姓名
  6. select name from employee
  7. where dep_id in
  8. (select id from department where name='技术');
  9. #查看不足1人的部门名
  10. select name from department
  11. where id not in
  12. (select dep_id from employee group by dep_id);
  13.  

(2)带比较运算符的子查询


 
  1. #比较运算符:=、!=、>、>=、<、<=、<>
  2. #查询大于所有人平均年龄的员工名与年龄
  3. mysql> select name,age from employee where age > (select avg(age) from employee);
  4. +---------+------+
  5. | name | age |
  6. +---------+------+
  7. | alex | 48 |
  8. | wupeiqi | 38 |
  9. +---------+------+
  10. #查询大于部门内平均年龄的员工名、年龄
  11.  

思路:

(1)先对员工表(employee)中的人员分组(group by),查询出dep_id以及平均年龄。
(2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。
(3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。


 
  1. mysql> select t1.name,t1.age from employee as t1
  2. inner join
  3. (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
  4. on t1.dep_id = t2.dep_id
  5. where t1.age > t2.avg_age;
  6. +------+------+
  7. | name | age |
  8. +------+------+
  9. | alex | 48 |
  10.  

 (3)带EXISTS关键字的子查询


 
  1. #EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。而是返回一个真假值。True或False
  2. #当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询
  3. #department表中存在dept_id=203,Ture
  4. mysql> select * from employee where exists (select id from department where id=200);
  5. +----+----------+--------+------+--------+
  6. | id | name | sex | age | dep_id |
  7. +----+----------+--------+------+--------+
  8. | 1 | egon | male | 18 | 200 |
  9. | 2 | alex | female | 48 | 201 |
  10. | 3 | wupeiqi | male | 38 | 201 |
  11. | 4 | yuanhao | female | 28 | 202 |
  12. | 5 | nvshen | male | 18 | 200 |
  13. | 6 | xiaomage | female | 18 | 204 |
  14. +----+----------+--------+------+--------+
  15. #department表中存在dept_id=205,False
  16. mysql> select * from employee where exists (select id from department where id=204);
  17. Empty set (0.00 sec)
  18.  

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载