幽灵资源网

MySQL多表查询实例详解【链接查询、子查询等】
数据库 2024/12/23 佚名
3 2
幽灵资源网 Design By www.bzswh.com

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

准备工作：准备两张表，部门表（department）、员工表（employee）

create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);


#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('nvshen','male',18,200),
('xiaomage','female',18,204)
;


# 查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id  | int(11)   | YES |   | NULL  |    |
| name | varchar(20) | YES |   | NULL  |    |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.19 sec)


mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra     |
+--------+-----------------------+------+-----+---------+----------------+
| id   | int(11)        | NO  | PRI | NULL  | auto_increment |
| name  | varchar(20)      | YES |   | NULL  |        |
| sex  | enum('male','female') | NO  |   | male  |        |
| age  | int(11)        | YES |   | NULL  |        |
| dep_id | int(11)        | YES |   | NULL  |        |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)


mysql> select * from department;
+------+--------------+
| id  | name     |
+------+--------------+
| 200 | 技术     |
| 201 | 人力资源   |
| 202 | 销售     |
| 203 | 运营     |
+------+--------------+
4 rows in set (0.02 sec)


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

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

一多表链接查询

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

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

mysql> select * from employee,department;
+----+----------+--------+------+--------+------+--------------+
| id | name   | sex  | age | dep_id | id  | name     |
+----+----------+--------+------+--------+------+--------------+
| 1 | egon   | male  |  18 |  200 | 200 | 技术     |
| 1 | egon   | male  |  18 |  200 | 201 | 人力资源   |
| 1 | egon   | male  |  18 |  200 | 202 | 销售     |
| 1 | egon   | male  |  18 |  200 | 203 | 运营     |
| 2 | alex   | female |  48 |  201 | 200 | 技术     |
| 2 | alex   | female |  48 |  201 | 201 | 人力资源   |
| 2 | alex   | female |  48 |  201 | 202 | 销售     |
| 2 | alex   | female |  48 |  201 | 203 | 运营     |
| 3 | wupeiqi | male  |  38 |  201 | 200 | 技术     |
| 3 | wupeiqi | male  |  38 |  201 | 201 | 人力资源   |
| 3 | wupeiqi | male  |  38 |  201 | 202 | 销售     |
| 3 | wupeiqi | male  |  38 |  201 | 203 | 运营     |
| 4 | yuanhao | female |  28 |  202 | 200 | 技术     |
| 4 | yuanhao | female |  28 |  202 | 201 | 人力资源   |
| 4 | yuanhao | female |  28 |  202 | 202 | 销售     |
| 4 | yuanhao | female |  28 |  202 | 203 | 运营     |
| 5 | nvshen  | male  |  18 |  200 | 200 | 技术     |
| 5 | nvshen  | male  |  18 |  200 | 201 | 人力资源   |
| 5 | nvshen  | male  |  18 |  200 | 202 | 销售     |
| 5 | nvshen  | male  |  18 |  200 | 203 | 运营     |
| 6 | xiaomage | female |  18 |  204 | 200 | 技术     |
| 6 | xiaomage | female |  18 |  204 | 201 | 人力资源   |
| 6 | xiaomage | female |  18 |  204 | 202 | 销售     |
| 6 | xiaomage | female |  18 |  204 | 203 | 运营     |

（2）内连接：只连接匹配的行，以双方为基准

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


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

 （3）外链接之左连接：优先显示左表全部记录

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

（4） 外链接之右连接：优先显示右表全部记录

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

（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;

 mysql> select * from employee left join department on employee.dep_id = department.id
     union
    select * from employee right join department on employee.dep_id = department.id
      ;
+------+----------+--------+------+--------+------+--------------+
| id  | name   | sex  | age | dep_id | id  | name     |
+------+----------+--------+------+--------+------+--------------+
|  1 | egon   | male  |  18 |  200 | 200 | 技术     |
|  5 | nvshen  | male  |  18 |  200 | 200 | 技术     |
|  2 | alex   | female |  48 |  201 | 201 | 人力资源   |
|  3 | wupeiqi | male  |  38 |  201 | 201 | 人力资源   |
|  4 | yuanhao | female |  28 |  202 | 202 | 销售     |
|  6 | xiaomage | female |  18 |  204 | NULL | NULL     |
| NULL | NULL   | NULL  | NULL |  NULL | 203 | 运营     |
+------+----------+--------+------+--------+------+--------------+
7 rows in set (0.01 sec)

#注意 union与union all的区别：union会去掉相同的纪录

二、符合条件连接查询

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

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

三、子查询

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

（1）带in关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from department
  where id in
    (select dep_id from employee group by dep_id having avg(age) > 25);
# 查看技术部员工姓名
select name from employee
  where dep_id in
    (select id from department where name='技术');
#查看不足1人的部门名
select name from department
  where id not in
    (select dep_id from employee group by dep_id);

（2）带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from employee where age > (select avg(age) from employee);
+---------+------+
| name  | age |
+---------+------+
| alex  |  48 |
| wupeiqi |  38 |
+---------+------+
#查询大于部门内平均年龄的员工名、年龄

思路：

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

mysql> select t1.name,t1.age from employee as t1
       inner join
      (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
      on t1.dep_id = t2.dep_id
      where t1.age > t2.avg_age;
+------+------+
| name | age |
+------+------+
| alex |  48 |

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

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

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

希望本文所述对大家MySQL数据库计有所帮助。

标签：
MySQL,多表查询,链接查询,子查询
幽灵资源网 Design By www.bzswh.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
幽灵资源网 Design By www.bzswh.com
www.bzswh.com 幽灵资源网
143,552互联网资源
29,117高清电影
46,608技术教程
224,608站长资源

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
幽灵资源网 Design By www.bzswh.com