深入浅出SQL排序与函数

站长

2024年03月19日 19:35 · 阅读数 4

在SQL中，排序和函数是对数据进行处理和操作的重要工具。排序能够让数据按照特定顺序排列，而函数则提供了对数据进行计算、转换和操作的能力。在本篇博客中，我们将深入探讨SQL排序和函数的相关知识。

SQL排序

排序是对结果集中的数据按照指定的顺序进行排列的操作，使数据更易于理解和分析。以下是关于SQL排序的一些重要概念：

ORDER BY子句：ORDER BY子句是SQL中用于对结果集进行排序的关键字。它通常出现在SELECT语句的末尾，并指定要排序的列名。
ASC和DESC：ASC（升序）和DESC（降序）是可选的关键字，用于指定排序的顺序。如果不指定，默认情况下是升序排列。
多列排序：除了对单个列进行排序外，还可以对多个列进行排序。SQL首先按照第一个列排序，然后再按照第二个列排序，以此类推。
NULL值排序：在排序过程中，NULL值的处理方式可能会有所不同。默认情况下，NULL值被视为最小值，但可以使用NULLS FIRST或NULLS LAST来明确指定NULL值在排序顺序中的位置。
排序性能：对大型数据集进行排序可能会影响性能。为了提高性能，可以在需要排序的列上创建索引，以便数据库引擎使用索引执行排序。
隐式排序：在某些情况下，结果集可能会以某种特定的顺序返回，即使没有明确使用ORDER BY子句。这种情况通常发生在使用聚合函数或DISTINCT时，但不应依赖于这种行为。

案例

题目

Employee 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
在 SQL 中，id 是这个表的主键。
表的每一行包含员工的工资信息。

查询并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。

题解：

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

SELECT MAX(salary) AS SecondHighestSalary: 这个部分是主查询，它使用 MAX 函数来获取 Employee 表中的最高薪水，并且将其命名为 SecondHighestSalary。
FROM Employee: 这个部分指定了查询的数据来源表，即 Employee 表。
WHERE salary < (SELECT MAX(salary) FROM Employee) : 这个部分是一个子查询，它用于查找所有低于最高薪水的值中的最大值。子查询 SELECT MAX(salary) FROM Employee 返回 Employee 表中的最高薪水。然后，外部查询选择所有低于最高薪水的值，并且对这些值再次使用 MAX 函数来获取最大值。这个值即为第二高的薪水。

这样，通过使用子查询和 WHERE 子句，我们可以找到 Employee 表中第二高的薪水。

这个SQL查询的执行顺序如下：

子查询执行：首先，数据库执行子查询 SELECT MAX(salary) FROM Employee，它计算出 Employee 表中的最高薪水。
外部查询执行：然后，数据库执行外部查询 SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee) 。在这个查询中，数据库将会使用子查询返回的最高薪水值，然后选择所有低于最高薪水的值，并且对这些值再次使用 MAX 函数来获取最大值。这个值即为第二高的薪水。

通过这种方式，数据库完成了整个查询，得到了 Employee 表中第二高的薪水值，并将其命名为 SecondHighestSalary。

SQL函数

函数是对数据进行处理和操作的机制，SQL提供了多种类型的函数，包括聚合函数、标量函数和表值函数。

聚合函数：对一组值执行计算，并返回单个值作为结果，如SUM、AVG、COUNT等。
标量函数：对单个值进行操作，并返回单个值作为结果，如UPPER、LOWER、LEFT、RIGHT等。
表值函数：返回一个结果集作为输出，如存储过程、视图等。

除了内置函数外，SQL还允许创建自定义函数，以满足特定需求。自定义函数是用户根据特定需求自行定义的函数，它们允许用户将常用的逻辑封装到可重复使用的函数中。在 SQL 中，根据不同的数据库管理系统（如 MySQL、SQL Server、PostgreSQL 等），自定义函数的语法和特性可能会有所不同，我将以 MySQL 为例来详细介绍自定义函数的相关内容。

1. 创建自定义函数

在 MySQL 中，可以使用 CREATE FUNCTION 语句来创建自定义函数。以下是创建自定义函数的一般语法：


CREATE FUNCTION function_name(parameter1 datatype, parameter2 datatype, ...)
RETURNS return_datatype
[NOT DETERMINISTIC]
[LANGUAGE SQL | LANGUAGE SQL SECURITY {DEFINER | INVOKER}]
BEGIN
    -- 函数体
END;

其中：

function_name 是自定义函数的名称。
parameter1, parameter2, ... 是函数的参数，可以有零个或多个。
return_datatype 是函数的返回类型。
NOT DETERMINISTIC 是可选的关键字，用于指定函数是否是确定性的。
LANGUAGE SQL 是指定函数使用的语言，通常为 SQL。
LANGUAGE SQL SECURITY {DEFINER | INVOKER} 是指定函数的执行权限，可以是定义者（DEFINER）或调用者（INVOKER）。

2. 示例

以下是一个示例，演示了如何在 MySQL 中创建一个简单的自定义函数，用于计算两个数的和：


CREATE FUNCTION add_numbers(x INT, y INT)
RETURNS INT
BEGIN
    DECLARE result INT;
    SET result = x + y;
    RETURN result;
END;

3. 使用自定义函数

创建自定义函数后，可以像使用内置函数一样在 SQL 查询中调用它们。例如：

SELECT add_numbers(5, 3); -- 返回结果为 8

4. 注意事项

自定义函数可以是简单的数学运算，也可以是复杂的业务逻辑。
在创建自定义函数时，要注意参数的数量、类型和返回类型的定义。
可以在函数体内部使用变量、流程控制语句（如 IF、CASE）、循环语句等。
自定义函数可以提高代码的可维护性和可重用性，但要注意性能影响，避免过度使用。

5. 示例扩展

例子1：

以下是一个示例，展示了如何在 MySQL 中创建一个自定义函数，用于计算阶乘：

CREATE FUNCTION factorial(n INT)
RETURNS INT
BEGIN
    DECLARE result INT;
    IF n <= 1 THEN
        RETURN 1;
    ELSE
        SET result = n * factorial(n - 1);
        RETURN result;
    END IF;
END;

这个函数用递归方式计算了一个数的阶乘。例子2：

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
在 SQL 中，id 是该表的主键。
该表的每一行都包含有关员工工资的信息。

查询 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询结果应该为 null 。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N - 1;
 RETURN (

select salary from Employee
ORDER BY  salary DESC
  LIMIT N, 1   
 );
END

首先，将参数N减去1，这是因为LIMIT子句在索引从0开始的情况下指定要返回的行数。
然后，执行一个SELECT查询语句，从Employee表中选择薪水列，并按降序排序。
使用LIMIT子句获取排序后的结果集中的第N个值，即第N高的薪水。
最后，将这个薪水值作为函数的返回值。