【MySQL】高级查询技巧 JOIN、GROUP BY、ORDER BY、UNION 应用案列解析

ops/2025/1/18 4:05:07/

🐇明明跟你说过：个人主页

🏅个人专栏：《MySQL技术精粹》🏅

🔖行路有良友，便是天堂🔖

目录

一、引言

1、MySQL起源

2、MySQL应用场景

二、MySQL高级查询技巧 

1、连接查询（JOIN）

2、分组查询（GROUP BY） 

3、排序查询（ORDER BY） 

4、联合查询（UNJOIN）

一、引言

1、MySQL起源

MySQL 是一款广泛使用的开源关系型数据库管理系统（RDBMS），它最初由瑞典公司 MySQL AB 开发。MySQL 以其高效性、可靠性、开放源代码等特点广受开发者和企业的欢迎。它作为一个流行的数据库系统，广泛用于网站开发、企业应用、数据仓库等领域。

  

2、MySQL应用场景

Web 应用开发：

  • MySQL 是 LAMP（Linux、Apache、MySQL、PHP/Perl/Python）和 WAMP（Windows、Apache、MySQL、PHP/Perl/Python）等流行开发堆栈的一部分。
  • 它被广泛用于支撑网站和网络应用程序，例如博客平台、内容管理系统（CMS）、电子商务网站等。

在线事务处理（OLTP）系统：

  • 适用于需要频繁进行插入、更新和查询操作的应用，如银行系统、股票交易系统等。
  • MySQL 提供了 ACID（原子性、一致性、隔离性、持久性）特性以确保数据的完整性和可靠性。

数据分析与报告：

  • 虽然 MySQL 主要是一个 OLTP 数据库，但它也可以用于轻度的数据分析工作，如生成报表或执行简单的聚合查询。
  • 对于更复杂的数据分析任务，通常会将数据导出到专门的数据仓库中。

分布式应用：

  • MySQL 支持主从复制和集群技术，可以在分布式的环境中提供高可用性和容错能力。
  • 这对于需要跨多个地理位置运行的服务非常重要。

云服务：

  • MySQL 可以部署在云平台上，作为即服务（DBaaS）提供给用户，方便快捷地创建和管理数据库实例。
  • 例如，阿里云、AWS、Google Cloud 等都提供了基于 MySQL 的云数据库服务。

移动应用后端：

  • 移动应用经常需要连接到服务器端数据库来存储用户信息、设置、游戏进度等。
  • MySQL 经常用作这些应用的后端数据库解决方案。

二、MySQL高级查询技巧 

1、连接查询（JOIN）

在 MySQL 中，连接查询（JOIN） 是一种非常强大的查询工具，它允许我们从两个或更多的表中检索数据，并将它们基于某些条件组合成一行。JOIN 是处理关系型数据库中多个表之间关系的核心功能之一。通过连接查询，我们可以实现复杂的数据提取，合并多个表的数据。

连接查询的基本概念
连接查询有不同的类型，最常见的几种类型有：

  • INNER JOIN：内连接
  • LEFT JOIN (OUTER)：左外连接
  • RIGHT JOIN (OUTER)：右外连接
  • FULL JOIN (OUTER)：全外连接
  • CROSS JOIN：交叉连接

INNER JOIN - 内连接
内连接是最常用的连接类型，它返回两个表中符合连接条件的交集部分。如果两个表中的某行没有匹配的记录，那么这一行就不会出现在查询结果中。

语法：

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

示例：

假设有两个表：

  • employees（员工表），包括字段：employee_id, name, department_id
  • departments（部门表），包括字段：department_id, department_name

如果要查询每个员工及其所在的部门名称，可以使用内连接：  

SELECT employees.employee_id, employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

这个查询将返回所有在 employees 表和 departments 表中都存在的匹配记录。

  

LEFT JOIN (OUTER) - 左外连接
左外连接返回左边表（table1）中的所有记录，以及右边表（table2）中与之匹配的记录。如果右边表中没有匹配记录，那么查询结果中右边表的字段将返回 NULL。

语法：

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

示例：

如果我们想要查询所有员工的名字以及他们的部门名称，即使有些员工没有分配部门，可以使用左连接：

SELECT employees.employee_id, employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

如果某个员工没有部门，那么 department_name 列将返回 NULL。

  

RIGHT JOIN (OUTER) - 右外连接
右外连接与左外连接类似，但它返回的是右边表（table2）中的所有记录，以及左边表（table1）中与之匹配的记录。如果左边表中没有匹配记录，那么左边表的字段将返回 NULL。

语法：

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

示例：

假设我们想查询所有部门及其员工，即使某些部门没有员工，我们可以使用右连接：

SELECT employees.employee_id, employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

如果某个部门没有员工，那么 employee_id 和 name 列将返回 NULL。

FULL JOIN (OUTER) - 全外连接
全外连接返回左右两个表中的所有记录，如果没有匹配的记录，则填充 NULL。MySQL 本身并不直接支持 FULL JOIN，但是我们可以通过组合 LEFT JOIN 和 RIGHT JOIN 来模拟全外连接。

语法（模拟）：

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

示例：

模拟全外连接查询员工和部门表中的所有记录：

SELECT employees.employee_id, employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.employee_id, employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

这个查询将返回所有员工和所有部门，无论是否有匹配记录。

  

CROSS JOIN - 交叉连接
交叉连接返回两个表的笛卡尔积，即 table1 中的每一行都会与 table2 中的每一行组合。交叉连接没有 ON 子句，它会返回 table1 行数乘以 table2 行数的结果。

语法：

SELECT columns
FROM table1
CROSS JOIN table2;

示例：

假设有 colors 表和 sizes 表，colors 表有三种颜色（Red, Green, Blue），sizes 表有两种尺寸（Small, Medium）。交叉连接将返回每个颜色与每个尺寸的组合。

SELECT colors.color, sizes.size
FROM colors
CROSS JOIN sizes;

这个查询将返回：

Red    Small
Red    Medium
Green  Small
Green  Medium
Blue   Small
Blue   Medium

   

2、分组查询（GROUP BY） 

在 MySQL 中，分组查询（GROUP BY） 是一个非常常见且强大的操作，用于将结果集按照某一列或多列进行分组。分组查询通常与聚合函数（如 COUNT()、SUM()、AVG()、MAX()、MIN() 等）结合使用，用于计算每个分组的统计信息。

GROUP BY 基本语法
基本的 GROUP BY 语法如下：

SELECT column1, column2, ..., aggregate_function(columnN)
FROM table
WHERE condition
GROUP BY column1, column2, ...;
  • column1, column2, ... 是你希望进行分组的字段。
  • aggregate_function(columnN) 是一个聚合函数，用于对每个分组的数据进行计算（如 COUNT(), SUM(), AVG() 等）。
  • WHERE 子句可用于在分组前筛选数据。
  • GROUP BY 子句指定按照哪些列进行分组。

GROUP BY 的常见聚合函数

  • COUNT(): 计算分组中的行数。
  • SUM(): 计算某列的总和。
  • AVG(): 计算某列的平均值。
  • MAX(): 返回某列中的最大值。
  • MIN(): 返回某列中的最小值。

GROUP BY 示例
基本示例：统计每个部门的员工人数
假设有一个 employees（员工表）如下：

  

如果我们要统计每个部门的员工数量，可以使用 GROUP BY 和 COUNT() 函数：

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

结果：

    

使用多个列进行分组：统计每个部门和职位的员工数量
假设 employees 表中还包含 job_title 字段。如果要查询每个部门和职位的员工数量，可以使用多个字段进行分组：

SELECT department_id, job_title, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id, job_title;

 结果：  

聚合函数：求每个部门的平均工资
假设在 employees 表中还有一个 salary（工资）字段，查询每个部门的平均工资：

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

 结果：

  

3、排序查询（ORDER BY） 

在 MySQL 中，排序查询（ORDER BY） 用于对查询结果进行排序。排序操作可以基于一个或多个列进行，并且可以选择升序（ASC）或降序（DESC）排序。默认情况下，ORDER BY 会按照升序排序。

基本语法

SELECT column1, column2, ...
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
  • column1, column2, ...：指定排序的列，可以是一个或多个列。
  • ASC：升序排序（默认值）。
  • DESC：降序排序。

单列排序
假设有一个 employees 表，如下所示：

  

按工资升序排序：

SELECT employee_id, name, salary
FROM employees
ORDER BY salary ASC;

结果： 

  

使用 ORDER BY 排序日期列
如果列是日期类型，ORDER BY 也会按照日期的顺序进行排序。

按雇佣日期升序排序：

SELECT employee_id, name, hire_date
FROM employees
ORDER BY hire_date ASC;

结果：

  

4、联合查询（UNOIN）

在 MySQL 中，联合查询（UNION） 用于将两个或多个查询的结果集合并为一个结果集。UNION 会将不同查询的结果合并，并去掉重复的行。如果你希望保留所有的行（包括重复的行），可以使用 UNION ALL。

需要注意的是，UNION 和 JOIN 是两种不同的操作，UNION 用于将结果集按行合并，而 JOIN 用于在多个表之间按列关联数据。

UNION 基本语法

SELECT column1, column2, ...
FROM table1
WHERE condition
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition;
  • 每个查询的列数必须相同，而且相应列的数据类型应当兼容。
  • UNION 默认会去掉重复的记录。如果需要保留重复记录，使用 UNION ALL。

UNION 示例
假设有两个表 employees 和 contractors，他们都有 name 和 salary 字段。

  

   

基本联合查询
我们想将 employees 和 contractors 表的 name 和 salary 合并成一个结果集：

SELECT name, salary FROM employees
UNION
SELECT name, salary FROM contractors;

结果：

   

  • 去重：UNION 会自动去除重复的记录。例如，如果 employees 和 contractors 表中有重复的名字（如相同的 name 和 salary），它们只会显示一次。 

保留重复记录（使用 UNION ALL）
如果我们希望保留重复的记录，可以使用 UNION ALL：

SELECT name, salary FROM employees
UNION ALL
SELECT name, salary FROM contractors;

结果：

 如果 employees 和 contractors 表中的某些记录完全相同，它们会在结果集中出现多次。

💕💕💕每一次的分享都是一次成长的旅程，感谢您的陪伴和关注。希望这些关于MySQL的文章能陪伴您走过技术的一段旅程，共同见证成长和进步！😺😺😺

🧨🧨🧨让我们一起在技术的海洋中探索前行，共同书写美好的未来！！！  


http://www.ppmy.cn/ops/150992.html

相关文章

npm发布组件（vue3+webpack）

npm发布组件（vue3+webpack）

1.初始化Vue项目 vue create my-app 2.本地运行 npm run serve 3.新增目录和文件 1. src/package/index.js 2. src/package/wlz-btn/index.vue 3. src/package/wlz-input/index.vue // src\package\index.js import WlzBtn from "./wlz-btn"; import WlzInput …
阅读更多...
海康MV-EB435i立体相机SDK安装（ROS 2）

海康MV-EB435i立体相机SDK安装（ROS 2）

文章目录 一、简介二、驱动配置小结 一、简介 MV-EB435i相机是一款低成本、小体积、配置全面的立体相机，凭借硬件级的深度图像处理方案，相机可在高性能输出的同时维持低功耗的水平。相机采用海康MV3D SDK，并提供跨平台支持，广泛应…
阅读更多...
【MyDB】3-DataManager数据管理 之 4-数据页缓存

【MyDB】3-DataManager数据管理 之 4-数据页缓存

【MyDB】3-DataManager数据管理 之 3-数据页管理 页面缓存设计与实现PageImpl页面定义getForCache() 文件中读取页面数据releaseForCache() 驱逐页面AtomicInteger 记录当前打开数据库文件页recoverInsert()和recoverUpdate() 参考资料 本章涉及代码：top/xianghua/m…
阅读更多...
工业视觉2-相机选型

工业视觉2-相机选型

工业视觉2-相机选型 一、按芯片类型二、按传感器结构特征三、按扫描方式四、按分辨率大小五、按输出信号六、按输出色彩接口类型 这张图片对工业相机的分类方式进行了总结，具体如下： 一、按芯片类型 CCD相机：采用电荷耦合器件（CC…
阅读更多...
【PGCCC】PostgreSQL 临时文件的使用

【PGCCC】PostgreSQL 临时文件的使用

临时文件 某些查询操作（例如sort或hash表）需要一些内存功能。此内存由运行时配置提供work_mem。 来自官方文档work_mem work_mem (整数) 设置在写入临时磁盘文件之前查询操作（例如排序或哈希表）使用的基本最大内存量。 请注意&…
阅读更多...
C# OpenCV机器视觉:图片去水印

C# OpenCV机器视觉:图片去水印

阿强是个不折不扣的动漫迷，最近他疯狂迷上了一部超火的老动漫，每天茶不思饭不想，心心念念就盼着能多看几集。然而，他在网上找到的资源却像是调皮孩子脸上的脏手印，布满了各种乱七八糟的水印，这可把阿强给郁…
阅读更多...
HTTPS与HTTP：区别及安全性对比

HTTPS与HTTP：区别及安全性对比

目录 一、基础概念 二、安全性对比 1. 加密传输 2. 身份验证 3. 数据完整性 4. 端口 5. 浏览器展示方式 三、使用场景与性能 1. 使用场景 2. 性能开销 四、成本与维护 五、搜索引擎优化（SEO） 六、案例分析 七、隐私保护与中间人攻击 八、…
阅读更多...
Debian没有reboot命令记录

Debian没有reboot命令记录

在安装服务并按照官方推荐的方式安装 Debian 系统时，发现无法使用 reboot 命令。经过一番排查，发现问题的根源在于系统环境变量中未包含常用命令所在的路径。具体来说，/sbin/ 和 /usr/sbin/ 目录通常包含系统管理命令，而这些路径默…
阅读更多...
最新文章

Copyright @ 2022~2023