MySQL基础篇——多表查询

已于 2022-01-24 22:54:16 修改
阅读量847 收藏 1
点赞数
分类专栏： MySQL 数据库 文章标签： mysql 数据库 database
于 2022-01-24 22:51:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45680566/article/details/122675865
版权

多表查询

01、 一个案例引发的多表连接

1.1、案例说明

1
从多个表中获取数据：

#错误的实现方式：每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录

SELECT *
FROM employees;  #107条记录

SELECT *
FROM departments; # 27条记录
SELECT 107*27 FROM dual;

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。
错误的原因：缺少了多表的连接条件

1.2、 笛卡尔积的理解

  • 笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
  • SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

1.3、案例分析与问题解决

笛卡尔积的错误会在下面条件下产生：

  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接
    为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
#3、多表查询的正确方式：需要有连接条件(使多个表之间能够相联系起来)

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.department_id;
 

#4、如果查询语句出现了多个表中都存在的字段，则必须指明此字段所在的表。
#建议：从SQL优化的角度，建议多表查询时，每个字段前都指明其所在的表 。

SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

#5、可以给表起别名，在SELECT和WGERE中使用表的别名。
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;

#如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
#原因：在SQL中，FROM语句最先执行，一旦执行就会把我们自定义表的别名覆盖了表的原名，故不能再使用原名。
#如下的操作是错误的：
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

#6、结论：如果有n个表实现多表的查询，则至少需要n-1个连接条件，否则会出现笛卡尔积的错误。
#练习：查询员工的`employee_id`，`last_name`，`department_name`，`city`
SELECT emp.`employee_id`,emp.`last_name`,dept.`department_name`,loc.`city`
FROM employees emp,departments dept,locations loc
WHERE emp.`department_id` = dept.`department_id`
AND dept.`location_id` = loc.`location_id`;

02、多表查询

2.1、等值连接 vs 非等值连接

案例

7.1 等值连接  vs  非等值连接
#非等值连接的例子：查询员工`last_name`，`salary`，`grade_level`
SELECT e.`last_name`,e.`salary`,j.`grade_level`
FROM `employees` e,`job_grades` j
WHERE e.`salary` BETWEEN j.`lowest_sal`AND j.`highest_sal`;
#WHERE e.`salary`   >= j.`lowest_sal`AND e.salary <= j.`highest_sal`;

2.2、自连接 vs 非自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

案例

# 7.2、自连接  vs  非自连接

#自连接的例子：
#练习：查询员工id,员工姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

2.3、内连接 vs 外连接

  • 内连接: 合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  • 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。
#7.3、内连接  vs  外连接
# 内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;  #满足此等式只有106条记录

# 外连接：合并具有同一列的两个以上的表的行, 结果集中除了包含一个表与另一个表匹配的行之外，
#         还查询到了左表 或 右表中不匹配的行。

# 外连接的分类：左外连接、右外连接、满外连接

# 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
# 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

#练习：查询所有的员工的last_name,department_name信息 （若出现“所有的”字眼，且字段来自不同的表，则一定要使用外连接）

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

#SQL92语法实现内连接：见上，略
#SQL92语法实现外连接：使用 +  ----------MySQL不支持SQL92语法中外连接的写法！Oracle 对 SQL92 支持较好。
#不支持：
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);

03、SQL99语法实现多表查询

3.1、基本语法

使用JOIN…ON子句创建连接的语法结构：

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件

3.2 内连接的实现

#SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的。
#SQL99语法如何实现多表的查询。

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

#SQL99语法实现内连接：INNER JOIN  ... ON  (INNER可以省略)
SELECT e.last_name,d.department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.department_id;

SELECT e.last_name,d.department_name,l.city
FROM employees e JOIN departments d
ON e.`department_id` = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`; #连接条件必须是两者都有的，否则查不出任何数据

3.3 外连接( OUTER JOIN)的实现

3.3.1 左外连接(LEFT OUTER JOIN)

#SQL99语法实现外连接：
#语法：
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

#练习：查询所有的员工的last_name,department_name信息
#左外连接：LEFT OUTER JOIN ...ON (OUTER可以省略)
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

3.3.2 右外连接(RIGHT OUTER JOIN)

#语法：
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

#右外连接：RIGHT  OUTER JOIN ... ON (RIGHT可以省略)
SELECT last_name,department_name
FROM departments d RIGHT  OUTER JOIN employees e
ON e.`department_id` = d.`department_id`;

3.3.3 满外连接(FULL OUTER JOIN)

  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
  • 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
#满外连接：MySQL不支持FULL OUTER JOIN ... ON ,Oracle支持此写法。
SELECT last_name,department_name
FROM departments d FULL  OUTER JOIN employees e
ON e.`department_id` = d.`department_id`;

04、UNION的使用

UNION和UNION ALL的使用

  • UNION：会执行去重的操作
  • UNION ALL :不会执行去重的操作
  • 结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

05、7种SQL JOINS的实现（重点！！！）

2

# 7种JOIN的实现：

#中图：内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左中图：
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

# 右中图：
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 左下图：满外连接
# 方式1：左上图 UNION ALL 右中图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;


# 方式2：左中图 UNION ALL 右上图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右下图：左中图  UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

06、SQL99语法新特性

6.1、自然连接

NATURAL JOIN : 它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

# SQL99语法的新特性1:自然连接

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

# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

6.2、USING连接

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

# SQL99语法的新特性2:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

07、拓展

  • WHERE：适用于所有关联查询
  • ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

注意：
我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

#拓展：
SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

08、本章练习

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 2.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` = 90;


DESC departments;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name ,e.`commission_pct`, d.department_name , d.location_id , l.city
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE e.`commission_pct` IS NOT NULL; #也应该是35条记录


SELECT *
FROM employees
WHERE commission_pct IS NOT NULL; #35条记录


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 

SELECT e.last_name , e.job_id , e.department_id , d.department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE l.`city` = 'Toronto';

#sql92语法：
SELECT e.last_name , e.job_id , e.department_id , d.department_name
FROM employees e,departments d ,locations l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_name` = 'Executive';


DESC departments;

DESC locations;

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar		101	king	100

SELECT emp.last_name "employees",emp.employee_id "Emp#",mgr.last_name "manager", mgr.employee_id "Mgr#"
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id = mgr.employee_id;


# 7.查询哪些部门没有员工

SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

#本题也可以使用子查询：暂时不讲

# 8. 查询哪个城市没有部门 

SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

SELECT department_id
FROM departments
WHERE department_id IN (1000,1100,1200,1300,1600);


# 9. 查询部门名为 Sales 或 IT 的员工信息

SELECT e.employee_id,e.last_name,e.department_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN ('Sales','IT');

以上是MySQL基础篇的多表查询，本章的知识内容来源于尚硅谷MySQL基础，按照视频教学顺序总结出来的。文章总结的若有不足，欢迎大家斧正。谢谢！

傅里叶yeye
关注
专栏目录
MySQL基础（8）-多表查询
m0_66059113的博客
03-24 445
九.多表查询：从多张表中查询数据 ①多表关系 1）一对多（多对一） 案例：员工与部门的关系 关系：一个部门对应多个员工，一个员工对应一个部门 实现：在（多）的一方建立外键，指向（一）的一方的主键 2）多对多 案例：学生与课程的关系 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择 实现：建立第三张中间表，中间至少包含两个外键，分别关联两方主键 3）一对一 案例：用户与用户详情的关系 关系：一对一的关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张
MySQL基础篇——理解RDBMS
dengzhigang1998的博客
06-29 950
专业的东西，总要讲究个术语，才能显得学问高深。所以呢，在说MySQL之前，需要普及下这些数据库术语。 我们平常说的关系型数据库，如Oracle，MySQL等等，为啥叫关系型数据库？想过没有？ 关系型数据库，全称叫关系型数据库管理系统，就是我们常常看见某些书中写的RDBMS。我们解释下这个名词包含的意思，关系型数据库的概念就出来了。 一、RDBMS（Relational DataBse Management System） 1、数据库：就是RDBMS中的DB，全拼为DataBase，我们常常接触仓库的概念，如
pta mysql训练题集(361-380)
daybreak_alonely的博客
05-20 7579
10-361 查询商品表中部分字段 select id,category_id,name from sh_goods; 10-362 简单条件查询数据 select * from sh_goods where id = 1; 10-363 修改商品表价格数据 update sh_goods set price = 30 where id = 2 10-364 删除商品表数据 delete from sh_goods where category_id != 3; 10-365 获取每
实验五 单表操作——商品评论表的操作
最新发布
Kangkang12139的博客
12-21 911
单表操作——商品评论表的操作
题阶段二啊，加油
m0_66080836的博客
12-30 4164
题目就可以
SQL查询语句&&1、查询商品id等于8且有效的评论内容。 2、查询每个用户评论的商品数量。 3、查询最新发布的5条有效商品评论信息。 4、查询评论过两种以上不同商品的用户id及对应的商品id。
m0_61927474的博客
10-26 2133
SQL根据商品表(sh_goods)与商品评论表(sh_goods_comment ) > 1、查询商品id等于8且有效的评论内容。 > 2、查询每个用户评论的商品数量。 > 3、查询最新发布的5条有效商品评论信息。 > 4、查询评论过两种以上不同商品的用户id及对应的商品id。 > 5、查询没有任何评论信息的商品id和name。 > 6、查询商品评分为5星的商品评论信息。
面试准备——MySql基础篇
12-15
总的来说，面试准备中MySQL基础篇应涵盖客户端连接、SQL语法、数据库设计、索引优化以及事务处理等方面。熟悉这些知识点不仅能帮助面试者在技术面试中表现出色，也是日常数据库管理工作中不可或缺的技能。
mysql学习——数据表的基本操作
12-14
MySQL 数据表的基本操作是数据库管理的核心内容，涵盖了创建、查看、修改和删除等关键操作。在MySQL中，数据表就像是数据库中的一个个文件夹，用于存储具体的数据。本篇将详细介绍这些基本操作。 首先，创建数据表...
MYSQL基础篇1——增删改查
w13107130889的博客
05-12 3113
前期操作： 打开数据库： mysql -uroot -p000000 创建数据库： create database demo; 使用刚才创建的数据库： use demo; 创建student表： sno为学号（可变长字符类型），主键（能够标识表中的每一行），name 姓名（非主键的唯一键，不能有重名的存在） create table student(sno varchar(9) primary key , name varchar(50) unique, sex char(5),age smallin
mysql8.0作业
zeroplp的博客
06-06 3834
MySQL8.0作业
PTA——连接查询——Mysql——（11-20）
qq_42824842的博客
04-22 8198
10-11 查询S001学生选修而S003学生未选修的课程（MSSQL） (10 分) 本题目要求编写SQL语句， 检索出 sc表中学号为S001的学生选修的而S003学号学生未选修的课程号。 注意：查询结果忽略顺序只对比数据。 提示：MSSQLServer 评测SQL语句。 /*检索出sc表中学号为S001的学生选修的而S003学号学生未选修的课程号*/ select cno 课程号 from sc where cno not in( select a.cno as 课程名 from sc a
mysql三个表连接_mysql 三个表连接查询
weixin_42301321的博客
01-18 1375
权限表(permission)10字段名称类型约束描述authorityidintegerPknot null权限流水号id PKuserNameIdintnot null用户名id FKfunctionidintegerNot null功能 id FKlookPermissionintnot null浏览addPermissionintnot null添加editPermissio...
多表连接查询
weixin_30847865的博客
04-21 4864
本文主要列举两张和三张表来讲述多表连接查询。 新建两张表： 表1：student 截图如下： 表2：course 截图如下： （此时这样建表只是为了演示连接SQL语句，当然实际开发中我们不会这样建表，实际开发中这两个表会有自己不同的主键。） 一、外连接 外连接可分为：左连接、右连接、完全外连接。 1、左连接 left join 或 left outer join SQL语...
多表查询(含完整例子)
atu1111的博客
05-06 6835
多表查询 1.多表联合查询 1.1 语法 select 字段1,字段2... from 表1,表2... [where 条件]   不加等值条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积   笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积. select * from person,dept   上述语句查询结果： 1.2 等值连接 ...
MySQL中的多表查询(超级详细)
haust_允谦的博客
01-30 4281
MySQL中的多表查询 多表查询的分类 等值连接—— 非等值连接 自连接 —— 非自连接 内连接 —— 外连接 # 等值连接 vs 非等值连接 # 非等值连接的例子 SELECT* FROM job_grades; SELECT last_name, salary,grade_level FROM employees e, job_grades j WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` # 自连接 vs 非
MySQL的多表查询（1）
richest_qi的博客
08-02 1057
也称为，指两个或更多个表一起完成的查询操作。这些一起查询的表通过关联字段关联，可能一对一，也可能一对多。比如，以上通过3步查询，终于得到了结果last_name为’Abel’的员工在Oxford这座城市工作。但是，如果使用则会简便很多。接下来，我们会看到。...
MySQL多表查询
zhangyupeng的博客
06-02 912
查询之前要确定的事情 查询几张表 查询的链接条件 查询所用到的字段 查询的条件 一、交叉连接查询 多表查询 交叉连接查询 的结果会产生 笛卡尔积 是不能购使用的 查询的结果是两个表的数据相乘 select * from category,products; --交叉连接查询 二、内链接查询 特点：通过指定条件 去匹配两张表中的内容，匹配不上的就不显示 隐式内连接 语法格式：select 字段... from 左表...
python怎样实现多表连接_Python Day45多表连接查询
weixin_39715834的博客
12-04 461
一、多表连接查询1 交叉连接：不适用任何匹配条件。生成笛卡尔积mysql> select * fromemployee,department;+----+------------+--------+------+--------+------+--------------+| id | name | sex | age | dep_id | id | name ...
MySQL数据库基础：内连接与多表查询实践
"这篇资料主要介绍了MySQL数据库中的多表查询，特别是内连接(Inner Join)的应用。通过具体的SQL查询示例，展示了如何使用内连接来联接`Students`和`Sc`两个表，以获取学生的姓名、课程ID和成绩。同时，资料也提到了...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值