1.2 DQL(Data Query Language数据查询语言)

1.2.1 基础查询(不带WHERE子句)

1.2.2 条件查询(带WHERE子句)

1.2.3 多表查询[蜡烛]

1.2.4 子查询

1.2.5 分组查询

1.2.6 组合查询

大家好，我是老梁.喜欢我的文章,麻烦点赞、收藏。

1.2.2节介绍了条件查询,本节主要讲解多表查询。日常工作中，多表查询涉及面很广，也是我们学习重点。

本节采用''尚硅谷教育''提供的示例数据库testdb作为演示，后续章节中有很多例子都使用这个数据库，请大家自行下载，安装。

1.下载链接:

2.testdb.sql文件，包括所有的创建语句和实际数据，如表、视图、存储过程、触发器等。

3.安装testdb示例数据库：打开CMD终端，登录MySQL，通过source命令安装。

4.本文演示数据库版本为MySQL8.0.34，基于Windows平台。

#打开CMD终端，登录MySQL。C:\WINDOWS\system32>mysql -uroot -pEnter password: *****Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 30Server version: 8.0.34 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.#导入数据库结构和数据root@localhost [(none)]>source D:\MySQL\testdb\testdb.sql 

1.2.3 多表查询

1.2.3.1 多表查询定义

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。多表查询查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

1.2.3.2 查询分类

等值连接(自然连接),不等值连接自连接,非自连接内连接,左连接,右连接,全外连接笛卡尔乘积(交叉连接)

注意:

1.SQL92语法中,MySQL只支持内连接,不支持外连接,而Oracle既支持内连接也支持外连接)

2.SQL99语法中(推荐),MySQL既支持内连接,外连接(全/满外连接除外),还支持交叉连接)

3.SQL92语法中,实现外连接可以使用+号,(+)代表从表所在的位置 (MySQL不支持SQL92语法中外连接的写法,而Oracle则支持.

1.2.3.2.1 等值连接(自然连接)/不等值连接

1.2.3.2.1.1 等值连接(自然连接,特殊的等值连接)

定义:

1.等值连接是指在连接条件中使用‘=’运算符比较被连接的列。

2.连接条件中的各个连接列的类型必须是可比的(建议类型相同,因为系统进行类型转换是要花费很多时间)，但不一定是相同的.(比如字符型或者日期型，也可以一个是整型一个是实型。)

3.如果按两个表的相同属性进行等值连接，且目标中去除重复列，保留所有不重复的列，则称为自然连接。

1.2.3.2.1.2 不等值连接

定义:

不等值连接是指连接条件中使用除了等于运算符以外的其他比较运算符比较被连接的列值.(可以使用的运算符:> >= < <= !> !< <>)

1.2.3.2.2 自连接/非自连接

定义:

自连接是指一个表同自身进行连接.

示例:

查询员工名和直接上级的名称

SELECT a.`last_name` 员工,a.`manager_id` 直属领导,b.`last_name` 领导名称FROM `employees` a,`employees` bWHERE a.`manager_id` = b.`employee_id`

1.2.3.2.3 内连接/外连接

1.2.3.2.3.1 内连接(join/inner join/cross join)

定义:

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

语法:

SELECT 字段列表FROM A表 INNER JOIN B表ON 关联条件WHERE 等其他子句;

示例:

SELECT e.`employee_id`, e.`last_name`, e.`department_id`,d.`department_id`, d.`location_id`FROM `employees` e JOIN `departments` dON (e.`department_id` = d.`department_id`);

1.2.3.2.3.2 外连接(left/right/full join)

定义:

两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

左外连接:

连接条件中左边的表也称为主表，右边的表称为从表。(表示在结果中包括左表中不满足条件的数据)

右外连接:

连接条件中右边的表也称为主表，左边的表称为从表。(表示在结果中包括右表中不满足条件的数据)

全外连接:

将两个表所有的行都显示在结果中,返回的结果除内连接的数据外,还包括两个表中不符合条件的数据,并在左表或右表的相应列中放入null值.(表示结果结果中包含左表和右表中不满足条件的数据.)

对于内连接的两个表,如果驱动表中的记录在被驱动表中找不到匹配的记录,则该记录不会加入到最后的结果集中.

对于外连接的两个表,即使驱动表中的记录在被驱动表中没有匹配的记录,也仍然需要加入到结果集中.

语法：

select 查询列表from 表1 别名left|right|full [outer] join 表2 别名 on 连接条件where 筛选条件group by 分组列表having 分组后的筛选order by 排序列表limit 子句;

特点：

1.查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null

2.外连接查询结果=内连接+主表中有而从表没有的记录.

3.left join 左边的就是主表，right join 右边的就是主表,full join 两边都是主表

4.左连接/右连接:一般用于查询除了交集部分的剩余的不匹配的行

5.全外连接:内连接的结果+左表有而右表没有+右表有而左表没有

6.对于左外连接或右外连接,on子句中的过滤条件是不能省略的.

1.2.3.2.4 交叉连接(笛卡尔称积)

语法：

select 查询列表from 表1 别名cross join 表2 别名;

特点：

类似于笛卡尔乘积

1.2.3.3 SQL99语法新特性

1.2.3.3.1 自然连接

SQL99在SQL92的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

在SQL92标准中：

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e JOIN `departments` dON e.`department_id` = d.`department_id`AND e.`manager_id` = d.`manager_id`;

在SQL99中你可以写成：

SELECT `employee_id`,`last_name`,`department_name` FROM `employees` e NATURAL JOIN `departments` d;

1.2.3.3.2 USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。比如：

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e JOIN `departments` dUSING (`department_id`);

你能看出与自然连接NATURAL JOIN不同的是，USING 指定了具体的相同的字段名称，你需要在USING 的括号()中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e ,`departments` dWHERE e.`department_id` = d.`department_id`;

总结:

sql92 vs sql99

优点:sql99支持连接类型较多,可读性强.

1.2.3.4 7种SQL JOINS的实现

图一

图二

1.内连接

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e JOIN `departments` dON e.`department_id` = d.`department_id`;

2.左连接

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e LEFT JOIN `departments` dON e.`department_id` = d.`department_id`;

3.右连接

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e RIGHT JOIN `departments` dON e.`department_id` = d.`department_id`;

4.左连接去交集

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e LEFT JOIN `departments` dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULL

5.右连接去交集

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e RIGHT JOIN `departments` dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL

6.全外连接

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e LEFT JOIN `departments` dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULLUNION ALLSELECT `employee_id`,`last_name`,`department_name`FROM `employees` e RIGHT JOIN `departments` dON e.`department_id` = d.`department_id`;

7.全外连接去交集

SELECT `employee_id`,`last_name`,`department_name`FROM `employees` e LEFT JOIN `departments` dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULLUNION ALLSELECT `employee_id`,`last_name`,`department_name`FROM `employees` e RIGHT JOIN `departments` dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL

今天分享到这，下期再见！