MySQL 高级查询

时间:2023-02-14 12:54:23

1.左关联

MySQL中的左关联(Left Join)是一种基于共同列的连接操作,
它将左侧表中的所有行与右侧表中匹配的行结合在一起,
如果右侧表中没有匹配的行,则结果集中右侧表中的所有列将显示为NULL。
左侧表是指在关键字LEFT JOIN中出现在关键字左侧的表。

下面是一个使用MySQL的LEFT JOIN进行连接操作的简单示例:

假设我们有两个表,一个是学校表(school),包含学校的ID和名称:

MySQL 高级查询

另一个是年级表(grade),包含年级的ID、学校ID和年级名称:

MySQL 高级查询

编程目标:我们可以使用以下代码来获取所有学校及其年级信息

SELECT school.school_name as "学校名称", grade.grade_name as "年级名称"
FROM 
school LEFT JOIN grade ON school.id = grade.school_id;

运行结果:
MySQL 高级查询

这将返回一个结果集,其中包含所有学校及其年级信息,
如果学校没有年级,则年级名称将显示为NULL。
左关联操作确保我们可以获取所有学校的信息,即使它们没有年级数据。

2.右关联

MySQL中的右关联(Right Join)是一种基于共同列的连接操作,
它将右侧表中的所有行与左侧表中匹配的行结合在一起,
如果左侧表中没有匹配的行,则结果集中左侧表中的所有列将显示为NULL。

右侧表是指在关键字RIGHT JOIN中出现在关键字右侧的表。

右关联与左关联的区别在于哪个表是主表和次要表,
即右关联的主表是右侧表,次要表是左侧表,
而左关联的主表是左侧表,次要表是右侧表。

假设有两个表,一个是学校表(school),包含学校的ID和名称,

另一个是年级表(grade),包含年级的ID、学校ID和年级名称。

编程目标:我们可以使用以下代码来获取所有学校及其年级信息:

select school.school_name as "学校名称", grade.grade_name as "年级名称"
from 
school right join grade
on school.id = grade.school_id;

运行结果:

MySQL 高级查询

这将返回一个结果集,其中包含所有年级及其所属学校信息,
如果年级没有对应的学校信息,则学校名称将显示为NULL。

右关联操作确保我们可以获取所有年级的信息,即使它们没有对应的学校数据。

注意:右关联和左关联的区别在于哪个表是主表和次要表,即
右关联的主表是年级表,次要表是学校表,
而左关联的主表是学校表,次要表是年级表。

3.子查询

MySQL中的子查询(Subquery)是一种在SELECT语句中使用的嵌套查询,用于从另一个查询的结果集中检索数据。子查询可以用作WHERE或HAVING语句中的条件,或者用作SELECT语句中的列。子查询可以返回单个值、一列值或一组行。

现在,我们假设有两个表,
一个是学校表(school),包含学校的ID和名称,
另一个是年级表(grade),包含年级的ID、学校ID和年级名称。

编程目标:以下是一个使用子查询的示例,用于检索包含特定年级名称的学校列表:

SELECT school_name
FROM school
WHERE id IN (
  SELECT school_id
  FROM grade
  WHERE grade_name = '三年级'
);

运行结果:

MySQL 高级查询

这将返回一个结果集,其中包含所有包含名为"三年级"的年级的学校的名称。
子查询用于获取包含特定年级的学校ID,
主查询用于从学校表中检索对应的学校名称。

另一个示例是使用子查询和聚合函数检索每个学校的年级数。
例如,以下查询使用子查询和COUNT函数从"年级表"中获取每个学校的年级数,
并将其与"学校表"中的学校名称一起返回:

SELECT school.school_name, 
  (SELECT COUNT(*) 
   FROM grade 
   WHERE grade.school_id = school.school_id) as grade_count
FROM school;

运行结果:

MySQL 高级查询

这将返回一个结果集,
其中包含每个学校的名称和年级数。

子查询在内部查询中使用COUNT函数从年级表中检索与学校ID匹配的行数。

主查询用于从学校表中检索学校名称,并将子查询返回的年级数作为附加列一起返回。

4.联合查询

MySQL中的联合查询(UNION)用于将多个SELECT语句的结果组合成一个结果集。
每个SELECT语句必须具有相同的列数和相似的数据类型,
而联合查询将自动对结果进行排序和去重。
可以使用UNION ALL来保留重复的行。

假设有两个表,一个是学校表(school),包含学校的ID和名称,
另一个是年级表(grade),包含年级的ID、学校ID和年级名称。

以下是一个使用UNION查询的示例,用于检索所有学校的名称和所有年级的名称,这些学校和年级存储在不同的表中:

SELECT school_name FROM school
UNION
SELECT grade_name FROM grade;

运行结果:

MySQL 高级查询

这将返回一个结果集,其中包含所有学校的名称和所有年级的名称。UNION操作将自动对结果集进行去重,并按名称排序。

如果要将学校和年级的名称分开,可以使用别名将两个SELECT语句的结果列重命名:

SELECT school_name, NULL AS grade_name FROM school
UNION
SELECT NULL AS school_name, grade_name FROM grade;

运行结果:
MySQL 高级查询

这将返回一个结果集,其中包含学校名称和年级名称的两个列。

注意,由于两个表中的名称列位于不同的位置,因此必须使用NULL作为占位符,以确保SELECT语句中的列数相同。

5.分组查询

MySQL中的分组查询(GROUP BY)用于将查询结果按照一个或多个列进行分组,并对每个分组应用聚合函数。GROUP BY子句必须跟在SELECT语句之后,并指定要分组的列名称。

假设有两个表,一个是学校表(school),包含学校的ID和名称,
另一个是年级表(grade),包含年级的ID、学校ID和年级名称。

以下是一个使用分组查询的示例,用于检索每个学校的年级数:

SELECT school_name, COUNT(g.id) AS grade_count
FROM
school s LEFT JOIN grade g ON s.id = g.school_id
GROUP BY s.id;

运行结果:

MySQL 高级查询

这将返回一个结果集,其中包含每个学校的名称和年级数。

在这个查询中,使用LEFT JOIN将两个表连接起来,并使用GROUP BY子句按学校ID分组。

注意,在GROUP BY子句中使用了学校表的ID列,而不是名称列。
使用COUNT函数计算每个学校的年级数。