MySQL多表查询篇

目录

多表关系

多表查询的概述

连接查询和子查询

内连接

外连接

左外连接：

右外连接 

内连接

联合查询

总结

---

多表关系

• 一对多
• 多对多
• 一对一 

一对多案例：

如下图所示：

一个部门可以对应多个员工，但是一个员工只能对应一个部门。

---

多对多案例：

多对多中如上图所示，一个学生可以选多个课程，一个课程可以被多个学生选择。这种多对多结构通常需要去建立第三张表来同步信息。 

---

一对一案例： 

---

 介绍完三种多表结构后，我们进入正题，多表查询。

多表查询的概述

我们在之前介绍过查询语句DQL，当时只介绍了简单的单表查询，今天我们介绍多表查询。

最简单的一种多表查询就是什么也不管直接查询，在这里我们提供执行所用的代码供阅读者参考：

-- --------------员工表创建 --------------------------------
create table emp_4_15(
    id int primary key auto_increment comment'主键ID',
    name varchar(20) not null comment '姓名',
    gender char(5) not null comment '性别',
    depno int comment '工作部门号',
    age int not null check(age>0 && age<120) comment '年龄'
)comment '员工表';


-- ---------------部门表创建-------------------------------
create table dep_4_15(
    depno int primary key auto_increment comment '部门编号',
    depname varchar(20) not null unique comment '部门名称'
)comment '部门信息表';

-- 插入数据-----------------------------
INSERT INTO emp_4_15(name, gender, depno, age)
VALUES
    ('张三', '男', 1, 25),
    ('李四', '女', 2, 32),
    ('王五', '男', 3, 28),
    ('赵六', '女', 1, 22),
    ('刘七', '男', 2, 35),
    ('周八', '女', 3, 27),
    ('钱九', '男', 1, 29),
    ('孙十', '女', 2, 30),
    ('吴十一', '男', 3, 26),
    ('郑十二', '女', 1, 24);

-- 插入数据-------------------------------------
INSERT INTO dep_4_15(depname)
VALUES
    ('研发部'),
    ('市场部'),
    ('财务部');

以上便是我们下面需要的数据，现在我们执行下面的语句看看是什么结果。

select * from dep_4_15,emp_4_15;

结果如下：

可以看到我们得到了40条结果。这种情况得到的结果是两个表的笛卡尔积。

 

 但是我们应该如何消除笛卡尔积呢？

很简单，我们只要加上查询条件就可以了：

select * from emp_4_15,dep_4_15 where emp_4_15.depno=dep_4_15.depno;

查询结果：

可以看到我们的查询结果去除了不必要的结果。但是显然这种方式有很多时候满足不了需求。所以我们我们接下来要介绍多表查询的两种主要方式：连接查询和子查询。 

连接查询和子查询

具体分类我们可以看下面的图：

---

内连接

隐式内连接：

select emp_4_15.name,dep_4_15.depname from emp_4_15,dep_4_15 where emp_4_15.depno=dep_4_15.depno;

运行结果：

显式内连接：

select emp_4_15.name,dep_4_15.depname from emp_4_15 inner join dep_4_15 on emp_4_15.depno=dep_4_15.depno;

运行结果：

为了方便大家对照观看，我把两个表贴到这里：

---

外连接

左外连接：

右外连接 

这两个连接其实很好理解，比如说左外连接，最后查询的结果会把左边的表的内容全部输出，不管对应的右边的表是否是null值。

---

内连接

首先我们创建一个表并插入数据：

-- 自连接 ---------------------------------------------
create table test_emp(
    id int primary key auto_increment comment 'ID',
    name char(20) not null comment '姓名',
    age int check ( age>0 ) comment '年龄',
    job varchar(20) comment '工作',
    salary int comment '薪资',
    empdate date not null comment '入职日期',
    managerid int comment '上级ID',
    depid int comment '所属部门'
)comment '员工表';

-- 插入数据
INSERT INTO test_emp(name, age, job, salary, empdate, managerid, depid)
VALUES
('张三', 25, '程序员', 8000, '2020-01-01', 2, 1),
('李四', 30, '设计师', 10000, '2019-03-15', 1, 2),
('王五', 35, '项目经理', 15000, '2018-05-01', 2, 3),
('赵六', 28, '销售', 5000, '2021-02-01', 3, 4),
('钱七', 29, '人事', 6000, '2017-11-11', 2, 1),
('孙八', 27, '文员', 4000, '2018-08-08', 4, 2),
('周九', 32, '财务', 7000, '2019-06-01', 5, 3),
('吴十', 26, '行政', 5000, '2020-09-01', 6, 4),
('郑一', 24, '客服', 4500, '2021-04-01', 3, 1),
('王二', 28, '技术支持', 5500, '2018-12-01', 1, 2),
('李三', 31, '产品经理', 12000, '2017-04-01', 2, 3),
('张四', 29, '市场', 6000, '2019-08-01', 4, 4),
('陈五', 33, '研发', 10000, '2016-09-01', 5, 1),
('刘六', 27, '采购', 5500, '2020-02-01', 6, 2),
('黄七', 30, '测试', 8000, '2018-11-01', 3, 3);

然后我们进行自连接查询：

select e1.name,e2.name from test_emp e1 ,test_emp e2 where e1.managerid=e2.id;

这段代码的意义式查询每个员工的上级。

当我们要进行自连接查询的时候有一个小技巧，就是把一张表当作两张表。拿本题举例，我们把本张表当作一个员工表和一个经理表。e1,e2分别是员工表和经理表。这样就可以更好的解决问题。

注意：自连接可以内查询也可以外查询。

---

联合查询

select * from test_emp where salary>=5000
union all
select * from test_emp where age>30;

联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集合。

执行结果：

 

---

---

总结

多表查询是MySQL中很常用的查询方式，希望大家可以掌握