MySQL BETWEEN運算子

2019-10-16 22:57:32

在本教學中,您將學習如何使用MySQL BETWEEN運算子,使用它來確定值是否在一個值範圍內。

MySQL BETWEEN運算子介紹

BETWEEN運算子允許指定要測試的值範圍。 我們經常在SELECTINSERTUPDATEDELETE語句的WHERE子句中使用BETWEEN運算子。

下面說明了BETWEEN運算子的語法:

expr [NOT] BETWEEN begin_expr AND end_expr;

expr是在由begin_exprend_expr定義的範圍內測試的表示式。

所有三個表示式:exprbegin_exprend_expr必須具有相同的資料型別

如果expr的值大於或等於(>=)begin_expr的值且小於等於(<=)end_expr的值,則BETWEEN運算子返回true,否則返回0

如果expr的值小於(<)begin_expr的值或大於end_expr的值的值,則NOT BETWEEN將返回true,否則返回0

如果任何表示式為NULL,則BETWEEN運算子返回NULL值。如果想指定一個不含邊界值的範圍,則使用大於(>)和小於(<)運算子。

MySQL BETWEEN範例

下面我們來練習一些使用BETWEEN運算子的例子。

MySQL BETWEEN與數位範例

請參見範例資料庫(yiibaidb)中的以下產品(products)表,表的結構如下所示:


mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode        | varchar(15)   | NO   | PRI | NULL    |       |
| productName        | varchar(70)   | NO   |     | NULL    |       |
| productLine        | varchar(50)   | NO   | MUL | NULL    |       |
| productScale       | varchar(10)   | NO   |     | NULL    |       |
| productVendor      | varchar(50)   | NO   |     | NULL    |       |
| productDescription | text          | NO   |     | NULL    |       |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |       |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |       |
| MSRP               | decimal(10,2) | NO   |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
9 rows in set

假設您想要查詢價格在90100(含90100)元範圍內的商品,可以使用BETWEEN運算子作為以下查詢:

mysql> SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 90 AND 100;
+-------------+--------------------------------------+----------+
| productCode | productName                          | buyPrice |
+-------------+--------------------------------------+----------+
| S10_1949    | 1952 Alpine Renault 1300             | 98.58    |
| S10_4698    | 2003 Harley-Davidson Eagle Drag Bike | 91.02    |
| S12_1099    | 1968 Ford Mustang                    | 95.34    |
| S12_1108    | 2001 Ferrari Enzo                    | 95.59    |
| S18_1984    | 1995 Honda Civic                     | 93.89    |
| S18_4027    | 1970 Triumph Spitfire                | 91.92    |
| S24_3856    | 1956 Porsche 356A Coupe              | 98.3     |
+-------------+--------------------------------------+----------+
7 rows in set

也可以通過使用大於或等於(>=)和小於或等於(<=)運算子來實現相同的結果,如以下查詢:

mysql> SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice >= 90 AND buyPrice <= 100;
+-------------+--------------------------------------+----------+
| productCode | productName                          | buyPrice |
+-------------+--------------------------------------+----------+
| S10_1949    | 1952 Alpine Renault 1300             | 98.58    |
| S10_4698    | 2003 Harley-Davidson Eagle Drag Bike | 91.02    |
| S12_1099    | 1968 Ford Mustang                    | 95.34    |
| S12_1108    | 2001 Ferrari Enzo                    | 95.59    |
| S18_1984    | 1995 Honda Civic                     | 93.89    |
| S18_4027    | 1970 Triumph Spitfire                | 91.92    |
| S24_3856    | 1956 Porsche 356A Coupe              | 98.3     |
+-------------+--------------------------------------+----------+
7 rows in set

要查詢購買價格不在20100(含20100)之間的產品,可將BETWEEN運算子與NOT運算子組合使用,如下:

mysql> SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice NOT BETWEEN 20 AND 100;
+-------------+-------------------------------------+----------+
| productCode | productName                         | buyPrice |
+-------------+-------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V              | 103.42   |
| S18_2238    | 1998 Chrysler Plymouth Prowler      | 101.51   |
| S24_2840    | 1958 Chevy Corvette Limited Edition | 15.91    |
| S24_2972    | 1982 Lamborghini Diablo             | 16.24    |
+-------------+-------------------------------------+----------+
4 rows in set

您也可以使用少於(>),大於(>)和邏輯運算子(AND)重寫上述查詢,如下所示 -

SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice < 20 OR buyPrice > 100;

執行上面查詢語句,得到以下結果 -

mysql> SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice < 20 OR buyPrice > 100;
+-------------+-------------------------------------+----------+
| productCode | productName                         | buyPrice |
+-------------+-------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V              | 103.42   |
| S18_2238    | 1998 Chrysler Plymouth Prowler      | 101.51   |
| S24_2840    | 1958 Chevy Corvette Limited Edition | 15.91    |
| S24_2972    | 1982 Lamborghini Diablo             | 16.24    |
+-------------+-------------------------------------+----------+
4 rows in set

MySQL BETWEEN與日期型別資料範例

當使用BETWEEN運算子與日期型別值時,要獲得最佳結果,應該使用型別轉換將列或表示式的型別顯式轉換為DATE型別

例如,要查詢獲取所需日期(requiredDate)從2013-01-012013-01-31的所有訂單,請使用以下查詢:

SELECT orderNumber,
         requiredDate,
         status
FROM orders
WHERE requireddate
    BETWEEN CAST('2013-01-01' AS DATE)
        AND CAST('2013-01-31' AS DATE);

執行上面查詢語句,得到以下結果 -

mysql> SELECT orderNumber,
         requiredDate,
         status
FROM orders
WHERE requireddate
    BETWEEN CAST('2013-01-01' AS DATE)
        AND CAST('2013-01-31' AS DATE);
+-------------+--------------+---------+
| orderNumber | requiredDate | status  |
+-------------+--------------+---------+
|       10100 | 2013-01-13   | Shipped |
|       10101 | 2013-01-18   | Shipped |
|       10102 | 2013-01-18   | Shipped |
+-------------+--------------+---------+
3 rows in set

因為requiredDate列的資料型別是DATE,所以我們使用轉換運算子將文字字串「2013-01-01」和「2013-12-31」轉換為DATE資料型別。

在本教學中,您已經學會了如何使用BETWEEN運算子來測試值是否在值的範圍內。