Mysql 简明教程

MySQL - Between Operator

MySQL Between Operator

MySQL 中的 BETWEEN 运算符是 SQL 提供的逻辑运算符，用于限制检索数据值的范围。已检索的值可以是整数、字符或日期。

The BETWEEN operator in MySQL is a logical operator provided by SQL, that is used to restrict the range from which the data values are to be retrieved. The retrieved values can be integers, characters, or dates.

可以使用 BETWEEN 运算符来替换“大于等于且小于等于”条件的组合。

You can use BETWEEN operator to replace a combination of “greater than equal AND less than equal” conditions.

我们通过使用以下示例表来更好地理解：

Let us understand in a better way by using the following example table −

between

Syntax

以下是 MySQL 中 BETWEEN 运算符的语法：

Following is the syntax of the BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

在此，

Here,

  1. value1 is the beginning value of the range.

  2. value2 is the ending value of the range (inclusive).

Example

首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：

First of all, let us create a table named CUSTOMERS 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 查询向该表中插入一些值：

Let us insert some values into this table using the following INSERT query −

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 );

创建的表如下所示：

The table created is as follows −

现在，我们正在使用 BETWEEN 运算符来检索 AGE ( numeric data ) 介于 20 和 25 之间的 CUSTOMERS 的详细信息 −

Now, we are using the BETWEEN operator to retrieve the details of the CUSTOMERS whose AGE (numeric data) is between 20 and 25 −

SELECT * FROM CUSTOMERS
WHERE AGE BETWEEN 20 AND 25;

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

MySQL BETWEEN with IN Operator

BETWEEN 运算符和 IN 运算符可以一起在 MySQL 查询中使用，以选择在指定范围内且也与指定值匹配的值。

The BETWEEN operator and the IN operator can be used together in a MySQL query, to select values that are within a specified range and also match with specified values.

Syntax

以下是 MySQL 中 BETWEEN 运算符的语法：

Following is the syntax of the BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
AND column_name IN (list_of_values);

Example

在此示例中，我们选择所有工资在 4000 到 10000 之间的客户。此外；我们仅检索使用 SQL 中的 IN 运算符居住在 MP 和博帕尔的客户。

In this example, we are selecting all the customers whose salary is between 4000 and 10000. In addition; we are only retrieving the customers who are living in MP and Bhopal using IN operator in SQL.

SELECT * FROM CUSTOMERS
WHERE SALARY BETWEEN 4000 AND 10000
AND ADDRESS IN ('Hyderabad', 'Bhopal');

Output

获得以下内容：

The following is obtained −

MySQL BETWEEN with UPDATE statement

MySQL 中的 UPDATE 语句用于修改数据库表中的现有数据。在 UPDATE 语句中使用 BETWEEN 运算符来更新指定范围内的值。

The UPDATE statement in MySQL is used to modify existing data in a database table. Using the BETWEEN operator in an UPDATE statement to update values within the specified range.

Example

让我们使用以下查询更新年龄介于 25 到 30 之间的客户的薪水 −

Let us update the salaries of the customers whose age lies between 25 to 30 using the following query −

UPDATE CUSTOMERS
SET SALARY = 10000
WHERE AGE BETWEEN 20 AND 25;

Verification

让我们使用以下查询验证是否更新了薪水 −

Let us verify whether the salaries are updated or not using the following query −

BETWEEN operator with DELETE statement

我们还可以在 DELETE 语句中使用 BETWEEN 运算符来删除指定范围内的行。

We can also use the BETWEEN operator in a DELETE statement to delete rows within a specified range.

Example

现在，让我们使用 DELETE 命令删除年龄在 18 到 20 之间的客户。

Now, let us delete the customers whose age is between 18 and 20 using the DELETE command.

DELETE FROM CUSTOMERS
WHERE AGE BETWEEN 20 AND 22;

Verification

让我们使用以下查询验证是否已删除指定年龄的员工：

Let us verify whether the specified aged employees are deleted or not using the following query −

MySQL NOT BETWEEN Operator

MySQL 中的 NOT BETWEEN 运算符是否定了 BETWEEN 运算符。这用于检索不在指定范围或时间间隔中的数据，即 not 。

The NOT BETWEEN operator in MySQL is a negation of the BETWEEN operator. This is used to retrieve the data which is not present in the specified range or time interval.

Syntax

以下是 MySQL 中 NOT BETWEEN 运算符的语法：

Following is the syntax of the NOT BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example

考虑 CUSTOMERS 表，以使用以下查询检索年龄不在 20 到 25（ numeric data ）之间的客户的详细信息。

Consider the CUSTOMERS table to retrieve the details of customers whose age is not between 20 and 25 (numeric data) using the following query.

SELECT * FROM CUSTOMERS
WHERE AGE NOT BETWEEN 20 AND 25;

Output

输出如下：

Following is the output −

NOT BETWEEN operator with IN operator

与 BETWEEN 运算符一样，我们还可以在 NOT BETWEEN 运算符中结合使用 IN 运算符。这是为了选择超出范围并且不与指定值匹配的值。

Like the BETWEEN operator, we can also use the NOT BETWEEN operator in combination with the IN operator. This is to select values that fall outside a range and also do not match with the specified values.

Example

在以下查询中，我们选择工资不在 1000 到 5000 之间的客户。此外；我们不检索使用 SQL 中的 IN 运算符居住在博帕尔的员工。

In the following query, we are selecting the customers whose salary is NOT between 1000 and 5000. In addition; we are not retrieving the employees who are living in Bhopal using IN operator in SQL.

SELECT * FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 1000 AND 5000
AND ADDRESS NOT IN ('Bhopal');

Output

输出如下：

Following is the output −

Between Operator Using Client Program

我们也可以使用客户端程序对 MySQL 表应用 BETWEEN 运算符。

We can also apply the BETWEEN operator on a MySQL table using a client program.

Syntax

以下是在不同编程语言中 MySQL 表中 Between 运算符的语法：

Following are the syntaxes of the Between Operator in MySQL table in various programming languages −

Example

以下是该操作在各种编程语言中的实现 −

Following are the implementations of this operation in various programming languages −