MySQL_多表查询

最新推荐文章于 2022-07-08 07:16:47 发布
最新推荐文章于 2022-07-08 07:16:47 发布
阅读量659 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_51923997/article/details/122547681
版权

文章目录

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

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

1、案例说明:

在这里插入图片描述
从多个表中获取数据：
在这里插入图片描述

#案例：查询员工的姓名及其部门名称
SELECT employee_id,last_name,department_name
FROM employees,departments;   #查询出2889（107*27）条记录

错误原因：

SELECT COUNT(employee_id) FROM employees;
#输出107行

SELECT COUNT(department_id)FROM departments;
#输出27行

SELECT 107*27 FROM dual;

2、笛卡尔积（或交叉连接）的理解

在这里插入图片描述

3、案例分析与问题解决

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

    • 省略多个表的连接条件（或关联条件）
    • 连接条件（或关联条件）无效
    • 所有表中的所有行互相连接

  • 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

  • 加入连接条件后，查询语法：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

  • 在 WHERE子句中写入连接条件。

正确写法：

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
  • 在表中有相同列时，在列名之前加上表名前缀。

二、多表查询的分类

分类一：等值连接和非等值连接

1.等值连接

employees（107条记录）
在这里插入图片描述
departments（27条记录）
在这里插入图片描述
locations
在这里插入图片描述

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

在这里插入图片描述

一、区分重复的列名（多个表中有相同字段）
  • 多个表中有相同列时，必须在列名之前加上表名前缀
  • 在不同表中具有相同列名的列可以用表名加以区分
SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

在这里插入图片描述

二、 表的别名
  • 使用别名可以简化查询
  • 列名前使用表名前缀可以提高查询效率（很多表名比较长）。
  • 如果给表起了别名，一旦SELECT和WHERE中使用了表名的话，则必须使用表的别名，而不能再使用表的原名
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id =dept.department_id;

在这里插入图片描述

三、连接多个表
  • 如果有n个表实现多表的查询,则需要至少n-1个连接条件。比如，连接三个表，至少需要两个连接条件。
# 练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;

在这里插入图片描述

# 练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;

在这里插入图片描述

2.非等值连接

在这里插入图片描述

SELECT e.employee_id,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;

在这里插入图片描述

分类二：自连接和非自连接

分类三：内连接和外连接

1、内连接

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

2、外连接

  • 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。
  • 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
  • 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表

3、SQL92语法实现内连接（如上）

4、SQL92语法实现外连接（使用"+"）\

注意：MySQL中不支持SQL92语法中外连接的写法

三、SQL99语法实现多表查询

1、基本语法

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

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

  1. 语法说明
  • 可以使用 ON 子句指定额外的连接条件。
  • 这个连接条件是与其它条件分开的。
  • ON 子句使语句具有更高的易读性
  • 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

2、内连接（INNER JOIN）的实现（INNER可省略）

  • 语法：

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

  • 练习1:
SELECT e.employee_id,e.last_name,e.department_id,
       d.department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id;

在这里插入图片描述

  • 练习2：
SELECT e.last_name,e.department_id,d.department_name,d.location_id,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、外连接（OUTER JOIN）的实现（OUTER可省略）

1、左外连接（LEFT OUTER JOIN）
  • 语法：

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

  • 练习：
# 左外连接
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、右外连接（RIGHT OUTER JOIN）
  • 语法：

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

  • 练习
SELECT e.last_name,d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;

在这里插入图片描述

3、满外连接（FULL OUTER JOIN）
  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
  • 注意：MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

四、UNION的使用

合并查询结果
利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

  • 语法格式：

SELECT column,… FROM table1
UNION [ALL]
SELECT column,… FROM table2

  • UNION操作符
    在这里插入图片描述
    UNION 操作符返回两个查询的结果集的并集，去除重复记录。（有去重的操作，降低效率）

  • UNION ALL操作符
    在这里插入图片描述
    UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：
执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

五、7种SQL JOINS的实现

在这里插入图片描述
（中间图：内连接，左上图：左外连接，右上图：右外连接）

  1. 中间图（内连接）
#中图：内连接
SELECT e.employee_id,d.department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id;
  1. 左上图：左外连接
#左上图：左外连接
SELECT employee_id,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id;
  1. 右上图：右外连接
#右上图：右外连接
SELECT employee_id,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id=d.department_id;
  1. 左中图
# 左中图
SELECT e.employee_id,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id <=> NULL;(去掉左表和右表的公共部分)
  1. 右中图
#右中图
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;(去掉左表和右表的公共部分)

6、 左下图：满外连接

  • 方式一：左上图 UNION ALL 右中图
  • 方式二：右上图 UNION ALL 左中图
#方式一：左上图 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;

#方式二：右上图 UNION ALL 左中图
SELECT employee_id,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id=d.department_id
UNION ALL
SELECT e.employee_id,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id <=> NULL;

7、右下图

  • 左中图 UNION ALL 右中图
#右下图：左中图 UNION ALL 右中图
SELECT e.employee_id,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id <=> 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;
Nil_cxc
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql 多表 3表4联合查询语句
11-30
sql 多表 3表4联合查询语句 对于多个表 或者其中存在两个表相同但是需要和不同的表进行不同条件的；联合查询
数据库多表查询SQL语句
beyond_mk的博客
09-25 7367
最近在做“进销存业务系统”，作为项目组成员的一部分，我负责统计查询，简单说就是多表查询，网上有很多，但是很明确的却没有，所以让我这个初学者了费，最终还是搞出来了，在这里也和大家共享一下成果和其中出现的问题，高手可绕道，不求点赞，只为方便大家。下面开始：我一共建了4个表，分别是Supplier、PurchaseMaster、PruchaseDetail、Product四个表（大家在测试的时候可以随意建
多表查询sql语句
06-01 452
多表查询sql语句 1 --解锁SCOTT用户 2 alter user scott account unlock 3 --检索指定的列 4 select job,ename,empno from emp; 5 --带有表达是的select子句 6 select sal*(1+0.2),sal from emp; 7 --显示不重复的记录 8 sele...
MySQL多表查询语句
孙正_大正
05-30 8860
MySQL多表查询语句 1.内连接查询 1.隐式内连接：使用where条件消除无用数据 例子： --查询所有员工信息和对应的部门信息 SELECT * FROM emp,dept WHERE emp.dept_id=dept.id; --查询员工表的姓名，性别，部门表的名称 SELECT t1.name, -- 员工表姓名 t1.gender, -- 员工表性别 t2.name -- 部门表名称 FROM emp t1, dept t2
MySQL多表查询详解上
09-08
主要介绍了MySQL多表查询详解上，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
MySQL 多表查询实现分析
09-11
在一个数据库中，可能存在多个表，这些表都是相互关联的。
MySQL多表查询详解下
09-08
主要介绍了MySQL多表查询详解下，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
mysql多表联合查询操作实例分析
09-09
主要介绍了mysql多表联合查询操作,结合实例形式分析了mysql多表联合查询的语法、功能、相关操作技巧与注意事项,需要的朋友可以参考下
mysql 多表查询语句
qq_65924224的博客
07-08 4387
多表查询语句
mysql语句的多表查询
gxl_1314520的博客
12-31 578
1.数据库 文件系统 2.sql 结构化查询语言 3.对数据库的CRUD1)创建数据库 create database 数据库 [character set 字符集] 2）查看数据库 show databases; show create database 数据库名； 3)删除数据库 drop database 数据库； 4）修改数据库 alter database 数据库 character se
【多表查询】一条sql语句
啊迪
04-14 1019
sql 多表 union
Mysql多表查询语句整理
定格空间
09-25 405
在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型： INNER JOIN：取交集，如果表中有至少一个匹配，则返回行 LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行（在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN） RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行 FULL JOIN：只要其中一个表中存在匹配，则返回行...
mysql
qq_21729419的博客
05-05 198
字段类型 数值型，字符串，日期 （一）单表查询 1.查询一张表： select * from 表名； 2.查询指定字段：select 字段1，字段2，字段3….from 表名； 3.where条件查询：select字段1，字段2，字段3 frome 表名 where 条件表达式； 例：select * from t_studect where id=1; select * from t_student where age>22; 4.带in关键...
mysql多表查询sql语句怎么写_SQL多表查询语句怎么写
weixin_42517569的博客
02-18 2898
展开全部SQL中多表查询可以使用链接查询1、unionunion [all]all：表示将查询的所e68a843231313335323631343130323136353331333337623431有结果都合并到结果集中，若不加all会将重复的行只保留一行--示例：select*fromaunionselect*fromb2、join连接分为内连接、外连接、交叉连接2.1、内连...
Mysql语句整理(多表查询)
nanhaierbuku的博客
12-27 417
Mysql语句整理(多表查询) [1] 合并结果集 [2] 连接查询 [一次查询多个表] [3] 子查询 (查询里面的嵌套查询)
MySql的各种语句（最详细）-4（多表查询）
qq_42564058的博客
03-29 145
#多表查询 #出现了笛卡尔积的错误 SELECT employee_id,last_name,department_name FROM employees,departments; #2889 SELECT 2889 / 107 FROM DUAL; # 27 SELECT department_id FROM departments; #27 #正确的： SELECT employee_...
MYSQL语句和多表查询
weixin_30814329的博客
07-25 430
数据库 什么是数据库 存储数据的仓库,本质上是一套CS结构的软件程序,分为客户端和服务器, 我们通常说安装数据,装的其实是服务器 库的语句 create database drop database alter database show databases show create database 表的语句 create tabl...
mysql数据库多表查询
最新发布
07-28
多表查询是指在MySQL数据库中一次性查询多个数据表的操作。常见的多表查询方法包括内连接查询、外连接查询、自连接查询、联合查询、子查询、标量子查询、行子查询、列子查询、表子查询等。其中，连接查询可以直接查询一个或两个表中符合某个条件的所有数据，而联合查询则将两次查询的结果合并在一张表中呈现出来。子查询是指在SELECT语句中嵌套使用SELECT语句，一般情况下先执行子查询再执行父查询。通过使用这些多表查询的方法，我们可以方便地查询多个表或查询一个表中存在某种关系的两列数据。 #### 引用[.reference_title] - *1* *2* [MySql的多表查询](https://blog.csdn.net/qq_53960242/article/details/125173937)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] - *3* [MySQL数据库——多表查询](https://blog.csdn.net/lyf1997115/article/details/82528016)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值