⑤多表查询（MySQL）

最新推荐文章于 2023-09-04 14:05:08 发布
最新推荐文章于 2023-09-04 14:05:08 发布
阅读量405 收藏 1
点赞数
分类专栏： MySQL 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yihjh/article/details/124942936
版权

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

气体条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有一定的关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

1.一个案例引发的多表连接

1.1案例说明

我们可以看到EMPLOYEES表、DEPARTMENTS表和LOCATIONS表，这三张表之间引发的多表连接。

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

笛卡尔积是一个数学运算。假设有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自于Y的所有可能。组合的个数即为两个集合中元素个数的乘积数。

  • 笛卡尔积的错误会在下面条件下产生
    • 省略多个表的连接条件（或关联条件）
    • 连接条件（或关联条件）无效
    • 所有表中的所有行互相关联
  • 为了避免笛卡尔积，可以在WHERE加入有效的连接条件。
  • 加入连接条件后的查询语法
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column1 = table2.column2;   #连接条件
  • 正确写法 
    #查询员工姓名及其部门名称
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

    其查询部分结果为

     

  • 注意：在表中有相同列时，在列名之前加上表名前缀。

2.多表查询分类

2.1等值连接 VS 非等值连接

2.1.1等值连接

  • 区分重复的列名
    • 多个表中有相同列时，必须在列名之前加上表名前缀。
    • 在不同表中具有相同列名的列可以用 表名 加以区分  。
  •  表的别名
    • 使用别名可以简化查询
  • 列名前使用表名前缀可以提高查询效率。 
    SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e , departments d 
WHERE e.department_id = d.department_id;

    需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

    【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

    【说明】：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

总结：连接n个表，至少需要n-1个连接条件。比如，连接三个表，则至少需要两个连接条件。

2.1.2非等值连接

 查询EMPLOYEES表中每个员工的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`;

其查询结果如下

 2.2自连接 VS 非自连接

 WORKER 表中的 MANAGER_ID 和 MANAGER 表中的 EMPLOYEE_ID相等

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

查询employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name,'works for',mang.last_name)
FROM employees worker,employees mang
WHERE worker.`manager_id` = mang.`employee_id`;

其查询结果如下：

 查询出last_name为 ‘Chen’ 的员工的 manager 的信息。

SELECT worker.last_name,leader.employee_id,leader.last_name
FROM employees worker,employees leader
WHERE worker.`manager_id` = leader.`employee_id`;

其查询结果如下

  •  内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。
  • 外连接：两个表在连接过程中除了返回满足条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空（Null）。
  • 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
  • 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
SQL92 ：使用 (+) 创建连接
在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中， (+) 表示哪个是从表。
Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。 
#左外连接 
SELECT last_name,department_name 
FROM employees ,departments 
WHERE employees.department_id = departments.department_id(+);
#右外连接 
SELECT last_name,department_name 
FROM employees ,departments 
WHERE employees.department_id(+) = departments.department_id;

3.SQL99语法实现多表查询

3.1

  • 使用JOIN...ON子句创建连接的语法结构（它的嵌套结构类似于我们使用的for循环）
SELECT table1.column,table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件
  • 语法说明
    • 可以使用ON子句指定额外的连接条件。
    • 这个连接条件是与其他条件分开的。
    • ON子句使语句具有更高的易读性。
    • 关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都表示内连接

3.2内连接（INNER JOIN）的实现

语法

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

3.3外连接（OUTER JOIN）的实现

3.3.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 JOIN departments d
ON e.`department_id` = d.`department_id`;

其查询部分结果为：

 3.3.2右外连接（RIGHT OUTER JOIN）

语法

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

举例

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

其查询部分结果如下：
 

 需要注意的是，LEFT JOIN 和RIGHT JOIN 只存在于SQL99及以后的标准中，在SQL 92中不存在，只能用（+）表示。

3.3.3满外连接（FULL OUTER JOIN）

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

4.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;

5. 7种SQL JOINS的实现

 5.1代码实现

#中图：内连接 
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 LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

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

#左中图
SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

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

#右下图
SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

6.SQL99语法新特性

6.1自然连接

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

在SQL92标准中：

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`;

在SQL99中可以写成：

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

6.2USING连接

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

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

我们可以看出与自然连接 NATURAL JOIN 不同的是，USING指定了具体的相同的字段名称，我们需要在 USING 的括号（）中填入要指定的同名字段。同时使用JOIN ... USING 可以简化 JOIN ON的等值连接。 

表连接的约束条件可以有三种方式：WHERE,ON,USING

  • WHERE:适用于所有关联查询
  • ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但是分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段相等。
#关联条件 #把关联条件写在where后面 
SELECT last_name,department_name 
FROM employees,departments 
WHERE employees.department_id = departments.department_id; 

#把关联条件写在on后面，只能和JOIN一起使用 
SELECT last_name,department_name 
FROM employees INNER JOIN departments 
ON employees.department_id = departments.department_id; 

SELECT last_name,department_name 
FROM employees CROSS JOIN departments 
ON employees.department_id = departments.department_id; 

SELECT last_name,department_name 
FROM employees JOIN departments 
ON employees.department_id = departments.department_id; 

#把关联字段写在using()中，只能和JOIN一起使用 
#而且两个表中的关联字段必须名称相同，而且只能表示= 
#查询员工姓名与基本工资 
SELECT last_name,job_title 
FROM employees INNER JOIN jobs 
USING(job_id); 

#n张表关联，需要n-1个关联条件 
#查询员工姓名，基本工资，部门名称 
SELECT last_name,job_title,department_name 
FROM employees,departments,jobs 
WHERE employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id; 

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;

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

欢欢**
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
部分·MySQL多表联合查询
南璃
08-31 1358
1.查询管理部目前薪资大于50000的员工的详细情况(员工) SELECT *FROM 员工 WHERE 目前薪资>5000 and 部门='管理部' 2.统计各部门男员工，女员工的最高薪资 (员工) select DISTINCT 姓名, case 性别 when -1 then '女' when 0 then '男' end as 性别,部门, max(目前薪资) as 最高薪资 from 员工 group by 性别,部门 3、统计各部门平均年龄(员工) SELECT 部门,AVG(YEA
数据库Mysql某公司送水系统课程设计
12-14
⑤ 创建触发器，实现入库、出库时相应类型矿泉水的数量的增加或减少； ⑥ 创建存储过程统计每个送水员工指定月份送水的数量； ⑦ 创建存储过程查询指定月份用水量最大的前10个用户，并按用水量递减排列； ⑧ 建立...
头歌-第1关：多表查询(数据库实验三 数据查询二)
Zjyzzy123456789的博客
03-23 3272
对于连接查询，在FROM短语后多个数据表的名称之间用逗号隔开，在WHERE短语中须指定数据表之间进行连接的条件 在连接查询中引用两个表的公共字段时，必须在字段前添加表名作为前缀，否则系统会提示出错。第二种方法 from 数据表1 join 数据表2 on 连接条件 数据表1和数据表2为需要连接的数据表 on后面是连接的条件 通常是关键字段的值相同。三个表的查询 如果要实现三个表的查询，可以 From 数据表1 ,数据表2 ,数据表3 where 连接条件1 and 连接条件2。
MySQL数据库管理系统的数据查询之多表查询SQL语句
码砖师傅的博客
05-18 1703
1.多表关系 项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种： 一对多(多对一) 多对多 一对一 ①一对多 案例: 部门 与 员工的关系 关系: 一个部门对应多个员工，一个员工对应一个部门 实现: 在多的一方建立外键，指向一的一方的 ②多对多 案例: 学生 与 课程的关系 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择 实现: 建立第三张中间表，中间表至少包含两个外键，分
Mysql11——多表查询01
tonyshi1989的博客
09-04 319
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。多表查询是数据库中一种常见的查询操作，它涉及对多个表进行联合查询，以获取更丰富的数据信息。通过多表查询，可以将不同表中的数据进行关联，从而通过一个查询语句获取到多个表的数据，并根据指定的条件进行筛选和排序。多表查询常用于解决以下情况：需要注意的是，在进行多表查询时，需要根据表之间的关系选择合适的连接方式（如JOIN、INNER JOIN、OUTER JOIN等），并指定连接条件，以保证查询结果的准确性和完整性。合理设计表结构和索引也有助于提高多表查
MySQL学习笔记-多表查询1
CrazyRabbitttt
01-21 457
本文是学习MySQL多表查询第一部分的笔记，希望能够帮助到大家
MySQL中文全文索引插件64位版本
11-16
　④、支持版本多：支持所有的MySQL 5.1 Release Candidate版本，即MySQL 5.1.22 RC～最新的MySQL 5.1.25 RC； 　⑤、支持字符集：支持包括GBK、GB2312、UTF-8、Latin1、BIG5在内的MySQL字符集（其他字符集没有测试...
MySQL 轻快掌握 视频+ppt+代码+数据 全套
05-08
2. **SQL基础**：讲解SQL语言的基本语法，包括创建、查询、更新和删除数据表。还会涉及SELECT语句、WHERE子句、JOIN操作和聚合函数如COUNT、SUM等。 3. **数据库设计**：讨论数据库范式、实体关系模型（ER模型）...
基于QT mysql 实现的选课管理系统
最新发布
04-15
使用QT+Mysql 实现学生选课成绩管理系统 数据库原理实验 系统包含两类用户，即管理员和学生，管理员登录系统后具有如下功能： ① 维护（添加、删除和修改等基本任务）学生基本信息、教师基本信息和课程基本信息等...
C语言连接mysql，实现通讯录功能
05-19
①　实现通讯录的信息中包括姓名、电话号码、地址、邮箱等数据项。 ②　支持从文件读入通讯录 ③　插入、删除、修改通讯录记录 ...⑤　查询通讯录记录，可按姓名和电话号码查询 ⑥　输出通讯录到数据库，数据库用mysql
mysql的多表查询（1）（一站到底，从简到难）
a15929748502的博客
06-26 385
经过前面的共同探讨相信大家对查找已经有了不错的理解。现在我将和大家一起共同探讨MySQL数据库的多表查询。相信不少人都有这样的疑惑，为什么需要多表查询？因为实际开发过程中往往数据来自不同的表，所以需要多表查询。老习惯，我们建三张表以用来演示下面的例子。这三张表构成立一个简易的学生管理系统。 因为希望便于大家阅读，表项起名我先用中文，（大家实际开发中视自己的情况而定） 表一，是一个成...
头歌：数据库 || SQL练习——简单查询 (第1关：简单查询 第2关：多表查询 第3关：集合操作 )
Molly 的博客
03-27 4604
头歌：数据库 || SQL练习——简单查询 (第1关：简单查询 第2关：多表查询 第3关：集合操作 )
多表联合查询 连接查询
qq_35677589的博客
01-06 948
实际中不是单表进行查询而是多表进行联合查询，这时就要用到联合查询。 1、什么时连接查询 2、连接查询的分类 3、笛卡尔乘积现象 对表进行别名命名是为了当两个表的关键字相同时，能够正常进行搜索 4、内连接 上面的代码和下面的代码作用相同： 这里的on后面连接筛选的条件，join的作用是连接两个表，也就是说要在两个表里找，就要用join来将两个表都添加进来。 5、内连接之非等值连接 6、自连接 将一个表看成是两章一张表的一个字段是另一个表.
MySql（9）多表查询
gtd54789的博客
06-04 238
Mysql专栏入口从这章开始使用新的数据库记得切换为什么要多表查询？ 练习：查询员工名为’Abel’的人在哪个城市工作 首先我需要在员工表中查询这个人的部门 这时就查到了这个人的部门id 知道部门号后在从部门表中查询其地点id 知道地点id后再地点表中查询地点 我们需要三个表employees、departments、locations才能找出我们需要的数据............
SQL Server第六章-多表连接查询
YUqiongqlm_的博客
10-20 811
第1关：带 WHERE 子句的多表查询 任务描述 本关任务：使用带 WHERE 子句的多表查询方法，检索数据表中的指定内容。 相关知识 为了完成本关任务，你需要掌握：如何使用带 WHERE 子句的方法查询多表数据。 理解连接 SQL 最强大的功能之一，就是能使用数据检索语句来连接多张数据表。 连接 (Join) 是使用数据库时最重要的操作，所以理解什么情况下，能连接和如何连接是学习 SQL 中极其重要的部分。 在你使用连接查询之前，一定要搞清楚数据表之间的关系。那怎么理解数据表之间是有关系的呢？ 举个例子
多表查询以及外键约束
weixin_53455615的博客
09-06 3030
alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名);constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段);删除和变更数据主表数据时, 先删除从表中的数据或将外键设置为null, 再删除主表中的数据。添加从表数据时, 从表中添加的外键值, 必须在主表的主键中存在。从表的外键类型必须和主表的主键类型保持一致。
【MySQL】入门Day3之多表查询及常用函数
m0_69171567的博客
10-17 256
很多时候我们需要多个表中的几个字段才能完成一项工作任务，这时我们要用到多表查询
MySQL多表查询 （超详细）
weixin_55772633的博客
08-05 7060
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）（开发中一般取消外键的设定，即在多的一方建立字段指向一的一方的主键即可）（开发中一般取消外键的设定，即在任意一方加入字段关联另外一方的主键即可）（开发中一般取消外键的设定，即在中间表建立两个字段指向两方的主键即可）关系：一个部门对应多个员工，一个员工对应一个部门。实现：在多的一方建立外键，指向一的一方的主键。
mysql约束条件之外键详解(建立表与表之间的关系)
weixin_46407419的博客
01-25 1764
目录外键如何查找表关系外键约束条件：多对多表关系之一对一外键 在介绍外键之前呢我们先来点 前戏 1、定义一个员工表 id name age dep_name dep_desc 1 jason 18 外交部 搞外交 2 kevin 28 教学部 教书育人 3 tony 38 教学部 教书育人 4 ...
通过root用户登录Linux后，执行以下步骤，请写出具体的过程命令： ① 下载http://dev.woyebuzhidao.com/mysql.rpm包； ② 使用rpm方式安装所下载的文件； ③ 重启主机； ④ 查询mysql包是否已经安装； ⑤ 删除mysql安装包。
06-09
4. 查询mysql包是否已经安装： ``` rpm -qa | grep mysql ``` 5. 删除mysql安装包： ``` rm -f mysql.rpm ``` 注意：以上步骤只是安装mysql的基本步骤，如果需要根据具体情况进行配置和管理，请参考mysql的官方...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值