小白学习MySQL Day14-16 20240829-0831

小白stepbystep

已于 2024-10-01 17:48:40 修改

阅读量901

点赞数 10

文章标签：学习 mysql 数据库

于 2024-08-31 21:45:27 首次发布

本文链接：https://blog.csdn.net/stepbystep_0/article/details/141674550

版权

一、ORDER BY子句

1、语法与功能

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

注：[ ] 表示非必须optional, | 表示或or。

用于对结果集（查询结果）进行排序。

从单个或多个表中联合查询（如使用 JOIN）得到的结果集，

通过 ORDER BY 子句来指定：排序的列和排序的方向（升序或降序）。

• The ORDER BY clause sorts the results of a query

• Sort in asc ending (default) or desc ending order

• Multiple columns can be given

（1）排序的列

排序操作需要一个明确的依据，通常ORDER BY子句引用结果集中的列。

选择不在结果集中的列进行排序，可能会影响查询的可读性和维护性。

Should not choose to order by a column that is not in the result.

SELECT y / 100 AS y2 FROM a ORDER BY y DESC ;

ORDER BY子句使用的是y列，而这个列并没有直接出现在SELECT列表中，但是它确实存在于原始的表a中。

选择表达式： SELECT y / 100 AS y2

选择了表a中的y列，将其除以100，并为这个表达式结果命名为y2。

这意味着结果集中的列名为y2。

排序依据： ORDER BY y DESC

指定了排序的依据是列y，并且按照降序排列。

尽管y列没有在SELECT列表中直接出现，但它是表a的一个列，因此可以在ORDER BY子句中使用。

结果集：最终的结果集将包含列y2，但排序是基于原始的y列值进行的。

（2）排序方向 ASC | DESC

默认排序方向：升序排序

默认情况下，如果未明确指定排序方向，那么将按照升序（ASC）对结果集进行排序。

数值较小的值会排在前面，数值较大的值会排在后面；

对于字符串，则按照字典序进行排序。

多列排序

当在ORDER BY子句中指定多个列进行排序时，首先会按照第一个列的值进行排序。

如果第一个列的值相同，则会按照第二个列的值进行排序，以此类推。

每个列的排序方向可以独立指定，如果未指定，则默认为升序。

E.g.如果想先按照Age升序排序，然后在Age相同的情况下按照LastName升序排序：

SELECT LastName, FirstName, Age  
FROM Employees  
ORDER BY Age, LastName;

2、示例

二、GROUP BY子句

1、语法与功能

根据一个或多个列对结果集进行分组，

以便对每个组执行聚合函数来计算该组的统计信息，

从而生成一个摘要行（汇总行）来表示每个组的数据。

SELECT column_name(s), AGGREGATE_FUNCTION(column_name)

FROM table_name

[WHERE predicate]

GROUP BY column_name(s);

[ORDER BY column_name(s)]

[HAVING predicate];

注：[ ] 表示非必须optional。

SELECT子句：列出了想要在结果集中显示的列。这可以包括分组列和聚合函数的结果。

FROM子句：指定了查询将要使用的表。

WHERE子句（可选）：用于在分组之前过滤原始行。

GROUP BY子句：指定了用于分组的列。

ORDER BY子句（可选）：用于对结果集中的行进行排序。

HAVING子句（可选）：用于在分组之后过滤组。

（1）分组的列

GROUP BY子句中的列决定了结果集中的行如何分组，

具有相同分组列值的行会被归到同一个组中。

结果集：是查询执行后返回的数据集。

在应用了所有的过滤（WHERE子句）、连接（JOIN操作）和分组（GROUP BY子句）之后，

结果集中的行才最终确定。因此，FROM子句中的表并不直接等同于结果集。

GROUP BY子句正是在这个过程中起作用的，它决定了如何将这些行分组，以便可以对每个组进行进一步的处理（如应用聚合函数）。

（2）SELECT列表

SELECT列表：定义了查询结果集应该包含哪些列或计算结果。

是SELECT关键字和FROM关键字之间的内容。

可以是表中的实际列，也可以是通过对表中列进行计算的表达式，甚至可以是聚合函数的结果。

SELECT列表中，除了聚合函数和常量之外，所有非聚合的列都必须出现在GROUP BY子句中，

以确保查询的逻辑正确性和结果的准确性。

（3）排序（非必须）

使用ORDER BY子句，根据分组列或聚合函数的结果对结果进行排序。

2、示例

三、HAVING子句

1、语法与功能

过滤GROUP BY子句生成的分组。

SELECT column_name(s), AGGREGATE_FUNCTION(column_name)

FROM table_name

[WHERE predicate]

GROUP BY column_name(s);

[ORDER BY column_name(s)]

[HAVING predicate];

（1）与WHERE子句的区别

WHERE子句：

在数据分组前，对原始数据进行过滤，作用于原始表中的行。

refers to the rows of tables, so cannot make use of aggregate functions. 无法使用聚合函数

HAVING子句：

在数据分组和聚合后，对分组结果进行过滤，作用于GROUP BY生成的组。

refers to the groups of rows, and so cannot use columns or aggregate functions that does not exist after the step of column selection.

可以引用聚合函数的结果。

不能引用在之前步骤中被过滤掉的不存在的列或聚合函数。

引用别名：

可以引用通过聚合函数定义的别名。

不能引用在SELECT列表中定义的非聚合别名。

非聚合别名：某个列的简单别名，没有进行任何聚合操作。

因为聚合函数的结果是在分组之后计算的，而HAVING子句正是用于过滤这些分组后的结果的。

（2）HAVING predicate

包括对聚合函数的比较。

HAVING (...) AND (...) 两个分开的括号

HAVING (...OR...) 一个合起来的括号

predicate 可以是任何有效的条件表达式，比如：

1) 数值类型不需加单引号

HAVING SUM(column1) > 10000 假设 column1 是数值类型。
HAVING column2 > 10

2) 字符串类型需加单引号

HAVING column3 = 'someValue'

3) 是否为空（通常用于聚合后的结果）

HAVING MAX(column4) IS NULL

4) 日期与时间类型

HAVING column5 > '2023-01-01' 假设 column4 是日期类型。

5) 多条件查询。

2、示例

四、聚合函数 Aggregate Functions

使用场景

1. SELECT子句：在SELECT列表中调用聚合函数。

SELECT SUM(column_name), AVG(column_name), MIN(column_name), MAX(column_name)

FROM table_name

[WHERE predicate];

注：[ ]表示非必须 optional

2. HAVING子句：使用聚合函数来设置过滤条件，对分组后的结果进行过滤。

3. OVER子句（窗口函数）

由于执行顺序的差异，不能在WHERE子句的顶层查询中使用聚合函数。

WHERE子句是在数据分组（如果有GROUP BY子句）和聚合计算之前执行的，目的是为了过滤掉不满足条件的行，以便在后续的查询处理步骤中只处理满足条件的行。

然而，聚合函数是对一组行进行计算的，它们的结果是在行被分组并应用聚合操作之后才确定的。因此，在WHERE子句中使用聚合函数在逻辑上是不成立的，因为此时数据库系统还没有对行进行分组和聚合计算，所以无法知道聚合函数的结果。

当需要根据聚合函数的结果来过滤分组时，应使用HAVING子句。

HAVING子句是在数据分组和聚合计算之后执行的，因此它可以使用聚合函数的结果来过滤分组。

简而言之，WHERE子句用于在数据分组和聚合之前过滤行，而HAVING子句用于在数据分组和聚合之后过滤分组。

由于这个执行顺序的差异，WHERE子句不能使用聚合函数，而HAVING子句可以。

可以在WHERE子句的子查询中使用聚合函数，并将这个子查询的结果用在WHERE子句中。

E.g.

SELECT * FROM staff

WHERE age > (SELECT AVG(age) FROM staff);

当子查询在WHERE子句中使用时，它首先被执行，然后其结果被用于外层查询的过滤条件。

由于子查询是独立于外层查询执行的，它可以在其内部进行聚合计算，并返回单个值或值的集合。然后，外层查询可以根据这个返回的值或值的集合来过滤行。

1、COUNT( )

功能： 计算表中满足条件的行数 。

用法：

（1）计算表中的总行数

SELECT COUNT(*) FROM table_name;

* 表示选择所有列，但 COUNT(*) 实际上并不关心列的具体内容，它只计算行数。

因此，COUNT(*) 是计算表中总行数最快的方式。

（2）计算某一列中非 NULL 值的数量

SELECT COUNT(column_name) FROM table_name;

当对某一列使用 COUNT( ) 函数时，它会计算该列中非 NULL 值的数量。

如果列中有 NULL 值，这些值将不会被计入总数。

（3）计算某一列中不同（唯一）值的数量

SELECT COUNT( DISTINCT column_name) FROM table_name;

返回 column_name 列中不同值的数量。

如果列中有重复的值， DISTINCT关键字会确保每个值只被计算一次。

示例：

employees 表，包含 id, name, 和 department 列。

计算 employees 表中的总行数：SELECT COUNT(*) FROM employees;

计算 department 列中非 NULL 值的数量：SELECT COUNT(department) FROM employees;

计算 department 列中不同部门的数量：

SELECT COUNT(DISTINCT department) FROM employees;

2、SUM( )

功能：计算数值列中值的总和。

注：只能用于 数值列。

如果计算的列包含 NULL 值，这些 NULL 值会被忽略，不会计入总和。

3、AVG( )

功能： 计算并返回数值列的平均值。

注：只能用于数值列。

如果列中的所有值都是 NULL，则函数将返回 NULL。

如果列中没有任何行（即没有满足 WHERE 子句的行），则函数将返回 NULL。

4、MIN( ) 和 MAX( )

功能： MIN( ) 计算并返回指定列中的最小值； MAX( )计算并返回指定列中的最大值。

注：计算时，忽略 NULL 值；如果列中的所有值都是 NULL，则函数返回 NULL。

MIN( )和MAX( )函数可以应用于数值列、日期列和字符串列。

但当应用于字符串列时，会基于字符串的字典顺序（即字母顺序）来查找最小值和最大值。

示例

五、查询的处理流程

A query is processed as follows:

1、Tables are joined (or FROM clause)

确定需要访问的表 Identify the tables that need to be accessed for the query.

If there are multiple tables involved, the engine will perform joins between them based on the specified join conditions (e.g., INNER JOIN, LEFT JOIN, etc.).

The result of this step is a single, potentially large, intermediate result set that contains all the rows from the joined tables.

2、WHERE clause

过滤中间结果集中的行。

Filters the rows in the intermediate result set based on the specified conditions.

只有满足WHERE子句中条件的行才会被保留在结果集中，不满足条件的行将被排除。

Only rows that satisfy the WHERE clause conditions will be included in the result set for further processing.

3、GROUP BY clause and aggregates

The rows that remain after the WHERE clause is applied are grouped based on the columns specified in the GROUP BY clause.

For each group, aggregate functions (like SUM, AVG, COUNT, MAX, MIN, etc.) are calculated to produce summary information.

Note that aggregate functions can only be used in the SELECT clause or HAVING clause when a GROUP BY clause is present.

针对WHERE子句过滤出的满足条件的行，基于GROUP BY子句中指定的列对这些行进行分组。

对于每个形成的组，计算聚合函数（SUM, AVG, COUNT, MAX, MIN等），生成该组的统计结果。

当存在GROUP BY子句时，聚合函数只能在SELECT子句或HAVING子句中使用。

4、Column selection (SELECT clause)

While the SELECT clause appears first in the SQL query, its "execution" in terms of determining which columns to include in the final result set happens after the WHERE and GROUP BY clauses have been processed. This is because the database engine needs to know which rows and groups to select from before it can determine which columns to include in the final result.

The SELECT clause specifies which columns (or expressions) from the intermediate result set should be included in the final result set.

在SQL查询中，尽管SELECT子句在查询语句中首先出现，但从确定哪些列应包含在最终结果集中的“执行”角度来看，它是在WHERE和GROUP BY子句处理之后才进行的。这是因为数据库引擎需要先知道从哪些行和分组中选择数据，然后才能确定哪些列应该包含在最终的结果集中。

SELECT子句指定了中间结果集中的哪些列（或表达式）应该被包含在最终的结果集中。

5、HAVING clause

The HAVING clause is applied after the GROUP BY clause and aggregate functions have been processed. It allows you to filter the groups based on conditions involving aggregate functions. This is different from the WHERE clause, which filters individual rows before grouping.

The HAVING clause is optional and is only used when filtering based on aggregate functions is necessary.

HAVING子句是在GROUP BY子句和聚合函数处理之后才应用的。它允许你根据和聚合函数有关的条件来过滤分组。这与WHERE子句不同，WHERE子句是在分组之前过滤单个行的。

HAVING子句是可选的，并且仅在需要根据聚合函数进行过滤时才使用。这意味着，如果你想要基于某个分组整体的计算结果（如总和、平均值等）来排除或选择某些分组，你就会使用HAVING子句。

HAVING子句的设计初衷就是与聚合函数（如SUM、AVG、COUNT、MAX、MIN等）一起工作的，以过滤那些聚合计算后的结果不满足特定条件的分组。

基于整个分组（而不是单个行）的统计信息（如平均值、总和等）来决定是否保留或排除该分组。

6、ORDER BY

Finally, the ORDER BY clause is applied to the result set to sort the rows or groups (depending on whether a GROUP BY clause was used) in the specified order.

This is the last step in the processing of the query before the result set is returned to the user.

最后，ORDER BY子句被应用于结果集，以指定的顺序对行或分组（取决于是否使用了GROUP BY子句）进行排序。

这是查询处理中的最后一步，之后结果集才会返回给用户。