Sql 简明教程

SQL - BETWEEN Operator

The SQL BETWEEN Operator

BETWEEN 运算符是 SQL 中的逻辑运算符，用于检索指定范围内的数据。检索的值可以是整数、字符或日期。

The BETWEEN operator is a logical operator in SQL, that is used to retrieve the data within a specified range. The retrieved values can be integers, characters, or dates.

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

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

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

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

假设我们要列出上表中年龄在 20 至 30 岁之间的人的名字。因此，我们将得到“Varma(21)”、“Nikhil(25)”和“Bhargav(29)”作为结果。

Suppose we want to list out the names from the above table who are aged BETWEEN 20 and 30. So, we will get "Varma(21)", "Nikhil(25)", and "Bhargav(29)" as a result.

Syntax

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

Following is the syntax of the BETWEEN operator in SQL −

SELECT column1, column2, column3,....columnN
FROM table_name
WHERE column BETWEEN value1 AND value2;

在此，

Here,

value1 is the beginning value of the range.
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 查询向表中插入一些值：

Once the table is created, let us insert some values into the 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 −

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

现在，我们正在使用 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 −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Example

此处，我们与 characters 一起使用 BETWEEN 运算符。让我们使用以下查询检索其名称以字母“A”和“L”之间开头的客户的详细信息 −

Here, we are using the BETWEEN operator with characters. Let us retrieve the details of the customers whose names starts in between the alphabets "A" and "L" using the following query −

SELECT * FROM CUSTOMERS WHERE NAME BETWEEN 'A' AND 'L';

Output

以下是以上查询的输出：

Following is the output of the above query −

ID	NAME	AGE	ADDRESS	SALARY
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

BETWEEN Operator with IN Operator

在 SQL 中，我们可以将 BETWEEN 运算符与 IN 运算符结合起来，以选择处于指定范围内的值，并且还与 IN 子句列表中指定的值匹配。

In SQL, we can combine the BETWEEN operator with the IN operator to select values that are within a specified range and also matches with values specified in the list of IN clause.

Example

在以下查询中，我们正在检索其薪水介于 4000 和 10000 之间的所有客户的详细信息。此外，我们仅使用 SQL 中的 IN 运算符检索住在海得拉巴和博帕尔的客户 −

In the following query, we are retrieving the details of all the customers whose salary is between 4000 and 10000. In addition, we are only retrieving the customers who lives in Hyderabad and Bhopal using the IN operator in SQL −

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

Output

执行以上查询后，输出显示如下 −

On executing the above query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

BETWEEN Operator with UPDATE Statement

我们还可以将 BETWEEN 运算符与 UPDATE 语句一起使用，以更新指定范围内的值。UPDATE 语句用于修改数据库表中的现有数据。

We can also use the BETWEEN operator with the UPDATE statement to update values within the specified range. The UPDATE statement is used to modify existing data in a database table.

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 25 AND 30;

Output

以上查询的输出如下 −

The output for the above query is as given below −

Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Verification

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

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

SELECT * FROM CUSTOMERS;

以上查询生成的表格如下所示 −

The table for the above query produced as given below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	10000.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	10000.00
5	Hardik	27	Bhopal	10000.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

BETWEEN Operator with DELETE Statement

我们还可以将 BETWEEN 运算符与 DELETE 语句一起使用，以删除指定范围内的行。

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

Example

现在，我们使用 DELETE 语句删除年龄介于 20 和 24 之间的客户 −

Now, let us delete the customers whose age is between 20 and 24 using the DELETE statement −

DELETE FROM CUSTOMERS
WHERE AGE BETWEEN 20 AND 24;

Output

如果我们编译并运行上述查询，则结果将生成如下：

If we compile and run the above query, the result is produced as follows −

Query OK, 3 rows affected (0.02 sec)

Verification

让我们使用以下查询验证是否删除了具有指定年龄值的记录 −

Let us verify whether the records with the specified age values are deleted or not, using the following query −

SELECT * FROM CUSTOMERS;

以上查询生成的表格如下所示 −

The table for the above query produced is as given below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00

NOT BETWEEN Operator

SQL 中的 NOT BETWEEN 运算符与 BETWEEN 运算符的工作方式恰恰相反。这用于检索不在指定范围内的数据。

The NOT BETWEEN operator in SQL works exactly opposite to BETWEEN operator. This is used to retrieve the data which is not present in the specified range.

让我们通过使用以下示例表格更深入地理解 −

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

假设我们希望从上表中列出年龄不在 20 到 30 之间的学生。因此，我们将获得“Prudhvi(45) 和 Ganesh(33)”作为结果。

Suppose we want to list out the students from the above table who are aged not between 20 and 30. So, we will get "Prudhvi(45) and Ganesh(33)" as result.

Syntax

以下是 SQL 中 NOT BETWEEN 运算符的语法 −

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

SELECT column_name1, column_name2, column_name3,......column_nameN
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example

考虑之前创建的 CUSTOMERS 表，让我们使用以下查询检索其年龄不大于或不等于 25 且小于或等于 30（数字数据）的客户的详细信息 −

Consider the previously created CUSTOMERS table and let us retrieve the details of customers whose age is not greater than or equal to 25 and less than or equal to 30 (numeric data) using the following query −

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

Output

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

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

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

NOT BETWEEN Operator with IN

NOT BETWEEN 运算符和 IN 运算符结合使用可选择超范围的值，而且不与指定的值列表匹配。

We can use the NOT BETWEEN operator in combination with the IN operator to select values that are outside a range and also do not match with the specified list of 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 customers who are living in Bhopal using the IN operator in SQL −

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

Output

执行以上查询后，输出显示如下 −

On executing the above query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00