Sql 简明教程

SQL - MIN() - MAX() function

SQL 中的 MIN() 和 MAX() 函数是聚合函数。它们用于比较集合中的值，并分别检索最大值和最小值。

The MIN() and MAX() functions in SQL are aggregate functions. They are used to compare values in a set and, retrieve the maximum and minimum values respectively.

MAX() 和 MIN() 聚合函数通常以两种方式使用：

MAX() and MIN() aggregate functions are generally used in two ways:

  1. As functions, they are used with the GROUP BY clause of the SELECT statement.

  2. As expressions, they are used with a subquery and HAVING clause of SELECT statement.

The SQL MAX() Function

MAX() 函数比较列中的值，并返回其中的最大值。

The MAX() function compares the values in a column and returns the largest value among them.

Syntax

以下是 SQL MAX() 函数的语法 −

Following is the syntax of SQL MAX() function −

MAX(column_name);

Example

在以下示例中，我们针对名为 CUSTOMERS 的表运行 MAX() 函数查询。目的是从此表检索最大薪资值。首先，让我们使用以下查询创建 CUSTOMERS 表 −

In the following example, we are running a query for MAX() function on a table named CUSTOMERS. The objective is to retrieve the maximum salary value from this table. First of all, let us create the CUSTOMERS table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，使用如下所示的 INSERT 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

CUSTOMERS 表将创建为 −

The CUSTOMERS table will be created as −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

4500.00

7

Muffy

24

Indore

10000.00

此处，我们正在比较 CUSTOMERS 的工资，并使用以下查询检索最大工资 −

Here, we are comparing the salaries of CUSTOMERS and retrieving the maximum salary using the following query −

SELECT MAX(SALARY) FROM CUSTOMERS;

执行以上查询时，结果显示为 −

When the above query is executed, the result is displayed as −

MAX(SALARY)

10000.0000

HAVING with MAX() Function

在以下查询中，我们使用 MAX() 函数和 HAVING 子句来获取 CUSTOMERS 的 ID、NAME 和 SALARY。

In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MAX() function along with HAVING clause.

SELECT ID, NAME, SALARY
FROM CUSTOMERS
GROUP BY NAME, ID
HAVING MAX(SALARY) < 8000;

执行以上查询时，我们将获取最大工资小于 8000 的员工的详细信息 −

When the above query is executed, we get the details of the employees whose maximum salary is less than 8000 −

ID

NAME

SALARY

1

Ramesh

2000.00

2

Khilan

1500.00

3

Kaushik

2000.00

4

Chaitali

6500.00

6

Komal

4500.00

MAX() Function in Subqueries

在以下示例中，我们在子查询中使用 MAX() 函数从 CUSTOMERS 表中检索最大工资的记录。

In the following example, we are using the MAX() function in a subquery to retrieve the record with maximum salary, from the CUSTOMERS table.

SELECT * FROM CUSTOMERS
WHERE SALARY = (SELECT MAX(SALARY) FROM CUSTOMERS);

执行以上查询时，我们将获得以下结果 −

When we execute the above query, we will get the following result −

ID

NAME

AGE

ADDRESS

SALARY

7

Muffy

24

Indore

10000.00

MAX() Function with Strings

此查询使用 MAX() 函数检索 CUSTOMERS 表中客户名称中最大值（按字母顺序排列） −

This query retrieves the maximum value (alphabetically) among the names of customers in the CUSTOMERS table using the MAX() function −

SELECT MAX(NAME) AS max_name FROM CUSTOMERS;

以下是以上查询的结果 −

Following is the result of the above query −

max_name

Ramesh

Aliases with MAX() Function

在以下示例中，我们使用 MAX() 函数从 CUSTOMERS 表中检索包含最大年龄的记录。我们把结果显示为一个名为“max_age”的新列。

In the following example, we use the MAX() function to retrieve the record containing maximum age from the CUSTOMERS table. We are displaying the results as a new column with the alias "max_age".

SELECT MAX(age) AS 'max_age' FROM CUSTOMERS;

以下是以上查询的输出：

Following is the output of the above query −

max_age

32

The SQL MIN() Function

MIN() 函数比较列中的值，并返回其中的最小值。

The MIN() function compares values in a column and returns the smallest value among them.

Syntax

以下是 SQL MIN() 函数的语法 −

Following is the syntax of SQL MIN() function −

MIN(column_name);

Example

在此示例中，我们正在比较 CUSTOMERS 表的 SALARY 列中的值，并使用以下查询显示最小工资 −

In this example, we are comparing values in the SALARY column of CUSTOMERS table and displaying the minimum salary using the following query −

SELECT MIN(SALARY) FROM CUSTOMERS;

执行以上查询时，结果显示为 −

When the above query is executed, the result is displayed as −

MIN(SALARY)

1500.0000

HAVING with MIN() Function

在以下查询中，我们使用 MIN() 函数和 HAVING 子句来获取 CUSTOMERS 的 ID、NAME 和 SALARY。

In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MIN() function along with HAVING clause.

SELECT ID, NAME, SALARY
FROM CUSTOMERS
GROUP BY NAME, ID
HAVING MIN(SALARY) > 5000;

执行以上查询时，我们将获取最小工资大于 5000 的员工的最大工资的详细信息，如下表所示 −

When the above query is executed, we get the details of the maximum salary for employees whose minimum salary is more than 5000, as we can see in the table that follows −

ID

NAME

MAX_Salary

4

Chaitali

6500.0000

5

Hardik

8500.0000

7

Muffy

10000.0000

MIN() Function in Subqueries

在以下示例中，我们在子查询中使用 MIN() 函数从 CUSTOMERS 表中检索最小工资的记录。

In the following example, we are using the MIN() function in a subquery to retrieve the record with minimum salary, from the CUSTOMERS table.

SELECT * FROM CUSTOMERS
WHERE SALARY = (SELECT MIN(SALARY) FROM CUSTOMERS);

执行以上查询时，我们将获得以下结果 −

When we execute the above query, we will get the following result −

ID

NAME

AGE

ADDRESS

SALARY

2

Khilan

25

Delhi

1500.00

MIN() Function with Strings

以下是使用 MIN() 函数检索 CUSTOMERS 表中客户名称中最小值（按字母顺序排列）的查询 −

Following is the query to retrieve the minimum value (alphabetically) among the names of customers in the CUSTOMERS table using the MIN() function −

SELECT MIN(NAME) AS min_first_name FROM CUSTOMERS;

以下是以上查询的结果 −

Following is the result of the above query −

min_first_name

Chaitali

Aliases with MIN() Function

以下是使用 MIN() 函数从 CUSTOMERS 表中获取最小年龄的 SQL 查询 −

Following is the SQL query that will fetch the minimum age from the CUSTOMERS table using the MIN() function −

SELECT MIN(age) AS 'min_age' FROM CUSTOMERS;

执行以上查询时，age 字段的最小值将显示如下所示。

When we execute the above query, the minimum value in the age field is displayed as shown below.

min_age

22