十五、多表连接查询

四哥1个月前MySQL46

引言

连接查询是关系数据库中最主要的查询,主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中,表建立时各数据之间的关系不必确定,常把一个实体的所有信息存放在一个表中。当检索数据时,通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性,他们可以在任何时候增加新的数据类型。为不同实体创建新的表,然后通过连接进行查询。

目录

  • 多表连接查询概述

  • 内连接

  • 左/右连接

1、多表连接查询概述

1.1、什么是多表查询

连接是在多个表之间通过一定的连接条件,使表之间发生关联,进而能从多个表之间获取数据。

比如:

有一个部门表,有一个员工表,我想查询某部门中的所有员工的信息。这时我们要先找出部门ID,通过部门ID查询出对应的员工信息。

这样我们在查询我们需要的信息的时候就应用了多表。所以这就是我们的多表查询。

1.2、多表查询的作用

比如:

我们想查询员工A的名字和他所在的部门的名字,则需要使用多表查询。

那么我们使用一条 SQL 语句查询多张表,因为查询结果在多张不同的表中。而我们的结果要从每张表取 1 列或多列。这就是多表查询的作用。

1.3、多表查询分类

多表查询可以分为二类查询:

内连接:隐匿内连接、显示内连接

外连接:左外连接、右外连接

1.4、笛卡尔积现象

1.4.1、数据准备

创建表和数据

#部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept (name) values ('研发部'),('渠道部'),('教务部');

# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键)
);
insert into emp(name,gender,salary,join_date,dept_id) values('张三','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('李四','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('王五','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('赵六','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('吴七','女',4500,'2011-03-14',1);

显示表中数据

mysql> select * from dept;
+----+-----------+
| id | name      |
+----+-----------+
| 1  | 研发部 |
| 2  | 渠道部 |
| 3  | 教务部 |
+----+-----------+
3 行于数据集 (0.01 秒)

mysql> select * from emp;
+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
| 1  | 张三 | 男    | 7200   | 2013-02-24 | 1       |
| 2  | 李四 | 男    | 3600   | 2010-12-02 | 2       |
| 3  | 王五 | 男    | 9000   | 2008-08-08 | 2       |
| 4  | 赵六 | 女    | 5000   | 2015-10-07 | 3       |
| 5  | 吴七 | 女    | 4500   | 2011-03-14 | 1       |
+----+--------+--------+--------+------------+---------+
5 行于数据集 (0.01 秒)

1.4.2、什么是笛卡尔积

案例:

查询所有员工和所有部门

mysql> select * from emp,dept;

image-20200215173505470

image-20200215173505470.png


左表的每条数据和右表的每条数据组合,这种效果就是笛卡尔积

1.4.3、清除笛卡尔积

我们发现笛卡尔积所产生的数据并不是都是有用的,只有员工.dept_id=部门.id的值才是我们想要的。

所以我们需要过滤掉没有用的数据。那么如何设置过滤条件呢?

mysql> select * from emp,dept where emp.dept_id=dept.id;
+----+--------+--------+--------+------------+---------+-------+-----------+
| id | name   | gender | salary | join_date  | dept_id | id(2) | name(2)   |
+----+--------+--------+--------+------------+---------+-------+-----------+
| 1  | 张三 | 男    | 7200   | 2013-02-24 | 1       | 1     | 研发部 |
| 5  | 吴七 | 女    | 4500   | 2011-03-14 | 1       | 1     | 研发部 |
| 2  | 李四 | 男    | 3600   | 2010-12-02 | 2       | 2     | 渠道部 |
| 3  | 王五 | 男    | 9000   | 2008-08-08 | 2       | 2     | 渠道部 |
| 4  | 赵六 | 女    | 5000   | 2015-10-07 | 3       | 3     | 教务部 |
+----+--------+--------+--------+------------+---------+-------+-----------+
mysql> select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;
+--------+-----------+
| name   | name(2)   |
+--------+-----------+
| 张三 | 研发部 |
| 吴七 | 研发部 |
| 李四 | 渠道部 |
| 王五 | 渠道部 |
| 赵六 | 教务部 |
+--------+-----------+
5 行于数据集 (0.01 秒)

2、内连接

用左边表的记录去匹配右边表的记录,如果符合条件的则显示。如:从表.外键=主表.主键

2.1、隐式内连接

看不到 join 关键字,条件使用 where 指定

格式:
select */字段列表 from 左表,右表 where 条件表达式;

案例:

查询员工表中所有员工及所在部门

mysql> select * from emp,dept where emp.dept_id=dept.id;
+----+--------+--------+--------+------------+---------+-------+-----------+
| id | name   | gender | salary | join_date  | dept_id | id(2) | name(2)   |
+----+--------+--------+--------+------------+---------+-------+-----------+
| 1  | 张三 | 男    | 7200   | 2013-02-24 | 1       | 1     | 研发部 |
| 5  | 吴七 | 女    | 4500   | 2011-03-14 | 1       | 1     | 研发部 |
| 2  | 李四 | 男    | 3600   | 2010-12-02 | 2       | 2     | 渠道部 |
| 3  | 王五 | 男    | 9000   | 2008-08-08 | 2       | 2     | 渠道部 |
| 4  | 赵六 | 女    | 5000   | 2015-10-07 | 3       | 3     | 教务部 |
+----+--------+--------+--------+------------+---------+-------+-----------+

2.2、显式内连接

使用inner join...on语句,可以省略inner

格式:
select */字段列表 from 左表 [inner] join 右表 on 条件表达式;

案例:

查询王五的信息,显示员工 id,姓名,性别,工资和所在的部门名称。

mysql> select emp.id,emp.name,emp.gender,emp.salary,dept.name from emp join dept on emp.dept_id=dept.id where emp.name='王五';
+----+--------+--------+--------+-----------+
| id | name   | gender | salary | name(2)   |
+----+--------+--------+--------+-----------+
| 3  | 王五 | 男    | 9000   | 渠道部 |
+----+--------+--------+--------+-----------+
1 行于数据集 (0.02 秒)

SQL优化

select emp.id,emp.name,emp.gender,emp.salary,dept.name from emp join dept on emp.dept_id=dept.id where emp.name='王五';

select e.id,e.name,e.gender,e.salary,d.name from emp e join dept d on e.dept_id=d.id where e.name='王五';

select e.id,e.name,gender,salary,d.name from emp e join dept d on dept_id=d.id where e.name='王五';

2.3、内连接使用步骤

确认查询的数据库表

确认数据库表连接条件

确认数据库表查询条件

确认数据库表显示字段

3、左/右连接

3.1、左连接

使用 left outer join...on,outer 可以省略

格式:
select */字段列表 from 左表 left [outer] join 右表 on 条件表达式;

用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示 NULL

案例:

在部门表中增加一个部门

mysql> insert into dept(name) values('执行部');
Query OK, 1 rows affected (0.08 秒)

用内连接查询信息

mysql> select * from dept d inner join emp e on d.id=e.dept_id;

image-20200215182047845

image-20200215182047845.png


用左连接查询信息

mysql> select * from dept d left join emp e on d.id=e.dept_id;

image-20210402122104492.png

注意:

左连接表示的是在内连接的基础上保证左表的信息全部显示

3.2、右连接

使用 right outer join...on,outer 可以省略

格式:
select */字段列表 from 左表 right [outer] join 右表 on 条件表达式;

用右边表的记录去匹配左边表的记录,如果符合条件的则显示;否则,显示 NULL

案例:

在员工表中加入一个新员工

mysql> insert into emp values (null, '王一','男',6666,'2013-12-05',null);
Query OK, 1 rows affected (0.02 秒)

用内连接查询信息

mysql> select * from dept d inner join emp e on d.id=e.dept_id;

image-20200215182047845image-20200215182047845.png

用右连接查询信息

mysql> select * from dept d right join emp e on d.id=e.dept_id;

image-20200215183504384

image-20200215183504384.png


注意:

右连接表示的是在内连接的基础上保证右表的信息全部显示


相关文章

七、DQL语句

七、DQL语句

引言DQL(Data QueryLanguage )数据查询语言,基本结构是由SELECT子句,FROM子句,WHERE子句组成的查询块。目录DQL概述记录查询条件查询1、DQL概述1.1、什么是DQ...

十二、DQL-模糊查询

十二、DQL-模糊查询

引言模糊查询即模糊检索,是指搜索系统自动按照用户输入关键词的同义词进行模糊检索,从而得出较多的检索结果。与之相反的是“精准搜索”。模糊检索也可以说是同义词检索,这里的同义词是用户通过“检索管理”中的“...

五、DDL语句

五、DDL语句

引言数据库模式定义语言DDL(Data Definition Language),是用于描述数据库中要存储的现实世界实体的语言。数据库模式定义语言并非程序设计语言,DDL数据库模式定义语言是SQL语言...

十六、子查询

十六、子查询

引言在SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作,首先必须创建一个查询来确定用户不知道但包含在数据库中的值,将一个查询块嵌套在另一个...

十三、约束

十三、约束

引言约束是按照约定(特定)条件限制,管束等意思。约束的作用是添加、删除。在数据库中对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建...

八、DQL-排序语句

八、DQL-排序语句

引言排序是计算机内经常进行的一种操作,其目的是将一组“无序”的记录序列调整为“有序”的记录序列。分内部排序和外部排序,若整个排序过程不需要访问外存便能完成,则称此类排序问题为内部排序。反之,若参加排序...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。