在本教學中，您將學習如何使用MySQL BETWEEN運算子，使用它來確定值是否在一個值範圍內。
BETWEEN運算子允許指定要測試的值範圍。 我們經常在SELECT，INSERT，UPDATE和DELETE語句的WHERE子句中使用BETWEEN運算子。
下面說明了BETWEEN運算子的語法：
expr [NOT] BETWEEN begin_expr AND end_expr;
expr是在由begin_expr和end_expr定義的範圍內測試的表示式。
所有三個表示式：expr，begin_expr和end_expr必須具有相同的資料型別。
如果expr的值大於或等於(>=)begin_expr的值且小於等於(<=)end_expr的值，則BETWEEN運算子返回true，否則返回0。
如果expr的值小於(<)begin_expr的值或大於end_expr的值的值，則NOT BETWEEN將返回true，否則返回0。
如果任何表示式為NULL，則BETWEEN運算子返回NULL值。如果想指定一個不含邊界值的範圍，則使用大於(>)和小於(<)運算子。
下面我們來練習一些使用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
假設您想要查詢價格在90和100(含90和100)元範圍內的商品，可以使用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
要查詢購買價格不在20到100(含20到100)之間的產品，可將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
當使用BETWEEN運算子與日期型別值時，要獲得最佳結果，應該使用型別轉換將列或表示式的型別顯式轉換為DATE型別。
例如，要查詢獲取所需日期(requiredDate)從2013-01-01到2013-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運算子來測試值是否在值的範圍內。