【Mysql】教程全解（二）运算符AND、OR、IN、BETWEEN、IS NULL

L罗乐 2018-05-06

展开全文

2.4 AND运算符

AND运算符是组合两个或多个布尔表达式的逻辑运算符，只有当两个表达式求值为true时才返回true。如果两个表达式中的一个求值为false，则AND运算符返回false。

WHERE boolean_expression_1 AND boolean_expression_2

SQL

以下说明AND运算符组合true，false和null时的结果。

-	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

AND运算符通常用在SELECT，UPDATE，DELETE语句的WHERE子句中以形成布尔表达式。AND运算符也用于INNER JOIN或LEFT JOIN子句的连接条件。

当求值具有AND运算符的表达式时，MySQL会计算表达式的其余部分，直到可以确定结果为止。该功能称为短路求值。请参见以下示例。

SELECT 1 = 0 AND 1 / 0 ;

Shell

执行上面查询时，得到以下结果 -

mysql> SELECT 1 = 0 AND 1 / 0 ;
 ----------------- 
| 1 = 0 AND 1 / 0 |
 ----------------- 
|               0 |
 ----------------- 
1 row in set

Shell

请注意，在MySQL中，0被认为是false，非零被视为true。

MySQL只计算表达式1 = 0 AND 1/0的第一部分1 = 0，因为表达式1 = 0返回false，所以MySQL得出结论：整个表达式的结果是false。 MySQL不对表达式的剩余部分求值，即不对1/0进行求值; 如果对1/0进行求值，它将发出一个错误消息，因为除以零错误。

MySQL AND运算符示例

下面使用示例数据库中的customers表进行演示。customers表的结构如下所示 -

mysql> desc customers;
 ------------------------ --------------- ------ ----- --------- ------- 
| Field                  | Type          | Null | Key | Default | Extra |
 ------------------------ --------------- ------ ----- --------- ------- 
| customerNumber         | int(11)       | NO   | PRI | NULL    |       |
| customerName           | varchar(50)   | NO   |     | NULL    |       |
| contactLastName        | varchar(50)   | NO   |     | NULL    |       |
| contactFirstName       | varchar(50)   | NO   |     | NULL    |       |
| phone                  | varchar(50)   | NO   |     | NULL    |       |
| addressLine1           | varchar(50)   | NO   |     | NULL    |       |
| addressLine2           | varchar(50)   | YES  |     | NULL    |       |
| city                   | varchar(50)   | NO   |     | NULL    |       |
| state                  | varchar(50)   | YES  |     | NULL    |       |
| postalCode             | varchar(15)   | YES  |     | NULL    |       |
| country                | varchar(50)   | NO   |     | NULL    |       |
| salesRepEmployeeNumber | int(11)       | YES  | MUL | NULL    |       |
| creditLimit            | decimal(10,2) | YES  |     | NULL    |       |
 ------------------------ --------------- ------ ----- --------- ------- 
13 rows in set

以下声明选择国家是USA和CA的客户。我们在WHERE子句中使用AND运算符。

SELECT customername, country, state FROM customers WHERE country = 'USA' AND state = 'CA';

执行上面查询得到以下结果 -

mysql> SELECT customername, country, state FROM customers WHERE country = 'USA' AND state = 'CA';
 ------------------------------ --------- ------- 
| customername                 | country | state |
 ------------------------------ --------- ------- 
| Mini Gifts Distributors Ltd. | USA     | CA    |
| Mini Wheels Co.              | USA     | CA    |
| Technics Stores Inc.         | USA     | CA    |
| Toys4GrownUps.com            | USA     | CA    |
| Boards & Toys Co.            | USA     | CA    |
| Collectable Mini Designs Co. | USA     | CA    |
| Corporate Gift Ideas Co.     | USA     | CA    |
| Men 'R' US Retailers, Ltd.   | USA     | CA    |
| The Sharp Gifts Warehouse    | USA     | CA    |
| West Coast Collectables Co.  | USA     | CA    |
| Signal Collectibles Ltd.     | USA     | CA    |
 ------------------------------ --------- ------- 
11 rows in set

Shell

使用AND运算符，可以组合两个以上的布尔表达式。例如，以下查询返回位于美国加州的客户，并且信用额度大于100K。

SELECT   customername,
         country,
         state,
         creditlimit
FROM customers
WHERE country = 'USA'
        AND state = 'CA'
        AND creditlimit > 100000;

Shell

执行上面查询语句，得到以下结果 -

mysql> SELECT customername, country, state, creditlimit FROM customers
WHERE country = 'USA' AND state = 'CA' AND creditlimit > 100000;
 ------------------------------ --------- ------- ------------- 
| customername                 | country | state | creditlimit |
 ------------------------------ --------- ------- ------------- 
| Mini Gifts Distributors Ltd. | USA     | CA    | 210500      |
| Collectable Mini Designs Co. | USA     | CA    | 105000      |
| Corporate Gift Ideas Co.     | USA     | CA    | 105000      |
 ------------------------------ --------- ------- ------------- 
3 rows in set

2.5 OR运算符

MySQL OR运算符组合了两个或两个以上布尔表达式。当任一条件为真时，返回true。

下面说明了OR运算符的语法。

boolean_expression_1 OR boolean_expression_2

Shell

boolean_expression_1和boolean_expression_2是布尔表达式，它可能返回的结果是：true，false或NULL。

下表显示了OR运算符的结果。

—	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

MySQL OR短路求值

MySQL使用OR运算符进行短路评估(求值计算)。换句话说，当MySQL可以确定结果时，MySQL会停止评估(求值计算)语句的其余部分。

请参见以下示例。

SELECT 1 = 1 OR 1 / 0;

SQL

执行上面代码，得到以下结果 -

mysql> SELECT 1 = 1 OR 1 / 0;
 ---------------- 
| 1 = 1 OR 1 / 0 |
 ---------------- 
|              1 |
 ---------------- 
1 row in set

Shell

因为表达式1 = 1总是返回true，MySQL不会对1/0进行求值。如果是这样，它会发出一个除以零错误的错误消息。

运算符优先级

当您在语句中使用多个逻辑运算符时，MySQL会在AND运算符之后再对OR运算符进行求值。这称为运算符优先级。

运算符优先级决定运算符的求值顺序。 MySQL首先对优先级较高的运算符进行求值。

请参见以下示例。

SELECT true OR false AND false;

SQL

执行上面查询，得到如下结果 -

mysql> SELECT true OR false AND false;
 ------------------------- 
| true OR false AND false |
 ------------------------- 
|                       1 |
 ------------------------- 
1 row in set

Shell

上面得出的结果，运算的过程是怎么样呢？

首先，MySQL对AND运算符求值，因此，false AND false返回false。
其次，MySQL对OR运算符求值，根据返回的false值再执行AND运算，因此true OR false返回true。

要更改评估/求值的顺序，请使用括号，例如：

SELECT (true OR false) AND false;

SQL

执行上面查询，得到如下结果 -

mysql> SELECT (true OR false) AND false;
 --------------------------- 
| (true OR false) AND false |
 --------------------------- 
|                         0 |
 --------------------------- 
1 row in set

Shell

上面得出的结果，运算的过程是怎么样呢？

首先，MySQL计算小括号中的表达式(true OR false)返回true
第二，MySQL评估求值语句的剩余部分，将上面第一步中计算出的表达式结果-true和剩余部分求值，即：true AND false返回false。

MySQL OR运算符示例

下面，我们将使用示例数据库(yiibaidb)中的customers表进行演示。customers表的结果如下所示 -

mysql> desc customers;
 ------------------------ --------------- ------ ----- --------- ------- 
| Field                  | Type          | Null | Key | Default | Extra |
 ------------------------ --------------- ------ ----- --------- ------- 
| customerNumber         | int(11)       | NO   | PRI | NULL    |       |
| customerName           | varchar(50)   | NO   |     | NULL    |       |
| contactLastName        | varchar(50)   | NO   |     | NULL    |       |
| contactFirstName       | varchar(50)   | NO   |     | NULL    |       |
| phone                  | varchar(50)   | NO   |     | NULL    |       |
| addressLine1           | varchar(50)   | NO   |     | NULL    |       |
| addressLine2           | varchar(50)   | YES  |     | NULL    |       |
| city                   | varchar(50)   | NO   |     | NULL    |       |
| state                  | varchar(50)   | YES  |     | NULL    |       |
| postalCode             | varchar(15)   | YES  |     | NULL    |       |
| country                | varchar(50)   | NO   |     | NULL    |       |
| salesRepEmployeeNumber | int(11)       | YES  | MUL | NULL    |       |
| creditLimit            | decimal(10,2) | YES  |     | NULL    |       |
 ------------------------ --------------- ------ ----- --------- ------- 
13 rows in set

Shell

例如，要获得美国(USA)或者法国(France)的客户，请在WHERE子句中使用OR运算符，如下所示：

SELECT 
    customername, country
FROM
    customers
WHERE
    country = 'USA' OR country = 'France';

SQL

执行上面代码，得到如下结果 -

mysql> SELECT  customername, country FROM customers WHERE country = 'USA' OR country = 'France';
 ------------------------------ --------- 
| customername                 | country |
 ------------------------------ --------- 
| Atelier graphique            | France  |
| Signal Gift Stores           | USA     |
| La Rochelle Gifts            | France  |
| Mini Gifts Distributors Ltd. | USA     |
| Mini Wheels Co.              | USA     |
| Land of Toys Inc.            | USA     |
| Saveley & Henriot, Co.       | France  |
| Muscle Machine Inc           | USA     |
| Diecast Classics Inc.        | USA     |
| Technics Stores Inc.         | USA     |
... ...
 ------------------------------ --------- 
48 rows in set

Shell

以下声明返回位于美国(USA)或者法国(France)，并且信用额度大于10000的客户。

SELECT 
    customername, country, creditLimit
FROM
    customers
WHERE
    (country = 'USA' OR country = 'France')
    AND creditlimit > 100000;

SQL

执行上面代码，得到如下结果 -

mysql> SELECT  customername, country, creditLimit FROM customers WHERE (country = 'USA' OR country = 'France') AND creditlimit > 100000;
 ------------------------------ --------- ------------- 
| customername                 | country | creditLimit |
 ------------------------------ --------- ------------- 
| La Rochelle Gifts            | France  | 118200      |
| Mini Gifts Distributors Ltd. | USA     | 210500      |
| Land of Toys Inc.            | USA     | 114900      |
| Saveley & Henriot, Co.       | France  | 123900      |
| Muscle Machine Inc           | USA     | 138500      |
| Diecast Classics Inc.        | USA     | 100600      |
| Collectable Mini Designs Co. | USA     | 105000      |
| Marta's Replicas Co.         | USA     | 123700      |
| Mini Classics                | USA     | 102700      |
| Corporate Gift Ideas Co.     | USA     | 105000      |
| Online Diecast Creations Co. | USA     | 114200      |
 ------------------------------ --------- ------------- 
11 rows in set

Shell

请注意，如果不使用括号，查询将返回位于美国的客户或者位于法国并且信用额度大于10000的客户。

SELECT
    customername, country, creditLimit
FROM
    customers
WHERE
    country = 'USA' OR country = 'France' AND creditlimit > 100000;

SQL

执行上面代码，得到如下结果(共 38 行) -

mysql> SELECT customername, country, creditLimit FROM customers WHERE country = 'USA' OR country = 'France' AND creditlimit > 100000; ------------------------------ --------- ------------- | customername                 | country | creditLimit | ------------------------------ --------- ------------- | Signal Gift Stores           | USA     | 71800       || La Rochelle Gifts            | France  | 118200      || Mini Gifts Distributors Ltd. | USA     | 210500      || Mini Wheels Co.              | USA     | 64600       || Land of Toys Inc.            | USA     | 114900      || Saveley & Henriot, Co.       | France  | 123900      || Muscle Machine Inc           | USA     | 138500      || Diecast Classics Inc.        | USA     | 100600      || Technics Stores Inc.         | USA     | 84600       || American Souvenirs Inc       | USA     | 0           || Cambridge Collectables Co.   | USA     | 43400       || Gift Depot Inc.              | USA     | 84300       || Vitachrome Inc.              | USA     | 76400       || Auto-Moto Classics Inc.      | USA     | 23000       || Online Mini Collectables     | USA     | 68700       || Toys4GrownUps.com            | USA     | 90700       || Boards & Toys Co.            | USA     | 11000       || Collectable Mini Designs Co. | USA     | 105000      || Marta's Replicas Co.         | USA     | 123700      |
| Mini Classics                | USA     | 102700      |
| Mini Creations Ltd.          | USA     | 94500       |
| Corporate Gift Ideas Co.     | USA     | 105000      |
| Tekni Collectables Inc.      | USA     | 43000       |
| Classic Gift Ideas, Inc      | USA     | 81100       |
| Men 'R' US Retailers, Ltd.   | USA     | 57700       || Gifts4AllAges.com            | USA     | 41900       || Online Diecast Creations Co. | USA     | 114200      || Collectables For Less Inc.   | USA     | 70700       || Classic Legends Inc.         | USA     | 67500       || Gift Ideas Corp.             | USA     | 49700       || The Sharp Gifts Warehouse    | USA     | 77600       || Super Scale Inc.             | USA     | 95400       || Microscale Inc.              | USA     | 39800       || FunGiftIdeas.com             | USA     | 85800       || West Coast Collectables Co.  | USA     | 55400       || Motor Mint Distributors Inc. | USA     | 72600       || Signal Collectibles Ltd.     | USA     | 60300       || Diecast Collectables         | USA     | 85100       | ------------------------------ --------- ------------- 38 rows in set

2.6 IN运算符

IN运算符允许您确定指定的值是否与列表中的值或子查询中的任何值匹配。下面说明了IN操作符的语法。

SELECT 
    column1,column2,...

FROM
    table_name
WHERE 
 (expr|column_1) IN ('value1','value2',...);

SQL

下面我们更详细的来看看上面的查询：

可以在WHERE子句中与IN运算符一起使用，可使用列或表达式(expr)。
列表中的值必须用逗号(，)分隔。
IN操作符也可以用在其他语句(如INSERT，UPDATE，DELETE等)的WHERE子句中。

如果column_1的值或expr表达式的结果等于列表中的任何值，则IN运算符返回1，否则返回0。

当列表中的值都是常量时：

首先，MySQL根据column_1的类型或expr表达式的结果来计算值。
第二步，MySQL排序值。
第三步，MySQL使用二进制搜索算法搜索值。因此，使用具有常量列表的IN运算符的查询将执行得非常快。

请注意，如果列表中的expr或任何值为NULL，则IN运算符计算结果返回NULL。

可以将IN运算符与NOT运算符组合，以确定值是否与列表或子查询中的任何值不匹配。

MySQL IN示例

下面练习一些使用IN操作符的例子。首先来看看办事处表：offices 的结构 -

mysql> desc offices;
 -------------- ------------- ------ ----- --------- ------- 
| Field        | Type        | Null | Key | Default | Extra |
 -------------- ------------- ------ ----- --------- ------- 
| officeCode   | varchar(10) | NO   | PRI | NULL    |       |
| city         | varchar(50) | NO   |     | NULL    |       |
| phone        | varchar(50) | NO   |     | NULL    |       |
| addressLine1 | varchar(50) | NO   |     | NULL    |       |
| addressLine2 | varchar(50) | YES  |     | NULL    |       |
| state        | varchar(50) | YES  |     | NULL    |       |
| country      | varchar(50) | NO   |     | NULL    |       |
| postalCode   | varchar(15) | NO   |     | NULL    |       |
| territory    | varchar(10) | NO   |     | NULL    |       |
 -------------- ------------- ------ ----- --------- ------- 
9 rows in set

Shell

如果您想查找位于美国和法国的办事处，可以使用IN运算符作为以下查询：

SELECT 
    officeCode, city, phone, country
FROM
    offices
WHERE
    country IN ('USA' , 'France');

SQL

执行上面查询语句，得到以下结果 -

mysql> SELECT officeCode, city, phone, country FROM offices WHERE country IN ('USA' , 'France');
 ------------ --------------- ----------------- --------- 
| officeCode | city          | phone           | country |
 ------------ --------------- ----------------- --------- 
| 1          | San Francisco |  1 650 219 4782 | USA     |
| 2          | Boston        |  1 215 837 0825 | USA     |
| 3          | NYC           |  1 212 555 3000 | USA     |
| 4          | Paris         |  33 14 723 4404 | France  |
 ------------ --------------- ----------------- --------- 
4 rows in set

Shell

也可以使用OR运算符执行得到与上面查询相同的结果，如下所示：

SELECT 
    officeCode, city, phone
FROM
    officesWHERE
    country = 'USA' OR country = 'France';

SQL

执行上面查询语句，得到以下结果 -

mysql> SELECT officeCode, city, phone FROM offices WHERE country = 'USA' OR country = 'France';
 ------------ --------------- ----------------- 
| officeCode | city          | phone           |
 ------------ --------------- ----------------- 
| 1          | San Francisco |  1 650 219 4782 |
| 2          | Boston        |  1 215 837 0825 |
| 3          | NYC           |  1 212 555 3000 |
| 4          | Paris         |  33 14 723 4404 |
 ------------ --------------- ----------------- 
4 rows in set

Shell

如果列表中有很多值，使用多个OR运算符则会构造一个非常长的语句。因此，使用IN运算符则会缩短查询并使查询更易读。

要获得不在美国和法国的办事处，请在WHERE子句中使用NOT IN如下：

SELECT 
    officeCode, city, phoneFROM
    officesWHERE
    country NOT IN ('USA' , 'France');

SQL

执行上面查询语句，得到以下结果 -

mysql> SELECT officeCode, city, phone FROM offices WHERE country NOT IN( 'USA', 'France');
 ------------ --------- ------------------ 
| officeCode | city    | phone            |
 ------------ --------- ------------------ 
| 5          | Beijing |  86 33 224 5000  |
| 6          | Sydney  |  61 2 9264 2451  |
| 7          | London  |  44 20 7877 2041 |
 ------------ --------- ------------------ 
3 rows in set

Shell

MySQL IN与子查询

IN运算符通常用于子查询。子查询不提供常量值列表，而是提供值列表。

我们来看看两张表：orders和orderDetails表的结构以及它们之间的关系：

例如，如果要查找总金额大于60000的订单，则使用IN运算符查询如下所示：

SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (SELECT 
                          orderNumber        
                FROM
                          orderDetails        
                GROUP BY  orderNumber        
                HAVING   SUM(quantityOrdered * priceEach) > 60000);

执行上面语句，得到以下结果 -

mysql> SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (SELECT 
            orderNumber
        FROM
            orderDetails
        GROUP BY orderNumber
        HAVING SUM(quantityOrdered * priceEach) > 60000);
 ------------- ---------------- --------- ------------- 
| orderNumber | customerNumber | status  | shippedDate |
 ------------- ---------------- --------- ------------- 
|       10165 |            148 | Shipped | 2013-12-26  |
|       10287 |            298 | Shipped | 2014-09-01  |
|       10310 |            259 | Shipped | 2014-10-18  |
 ------------- ---------------- --------- ------------- 
3 rows in set

Shell

上面的整个查询可以分为2个查询。

首先，子查询使用orderDetails表中的GROUP BY和HAVING子句返回总额大于60000的订单号列表。

SELECT 
    orderNumber
FROM
    orderDetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000;

SQL

执行上面语句，得到以下结果 -

mysql> SELECT 
    orderNumber
FROM
    orderDetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000;
 ------------- 
| orderNumber |
 ------------- 
|       10165 |
|       10287 |
|       10310 |
 ------------- 
3 rows in set

Shell

其次，主查询从orders表中获取数据，并在WHERE子句中应用IN运算符。

SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (10165,10287,10310);

SQL

执行上面语句，得到以下结果 -

mysql> SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (10165,10287,10310);
 ------------- ---------------- --------- ------------- 
| orderNumber | customerNumber | status  | shippedDate |
 ------------- ---------------- --------- ------------- 
|       10165 |            148 | Shipped | 2013-12-26  |
|       10287 |            298 | Shipped | 2014-09-01  |
|       10310 |            259 | Shipped | 2014-10-18  |
 ------------- ---------------- --------- ------------- 
3 rows in set

2.7 BETWEEN运算符

BETWEEN运算符允许指定要测试的值范围。我们经常在SELECT，INSERT，UPDATE和DELETE语句的WHERE子句中使用BETWEEN运算符。

下面说明了BETWEEN运算符的语法：

expr [NOT] BETWEEN begin_expr AND end_expr;

SQL

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值。如果想指定一个不含边界值的范围，则使用大于(>)和小于(<)运算符。

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

Shell

假设您想要查找价格在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

Shell

也可以通过使用大于或等于(>=)和小于或等于(<=)运算符来实现相同的结果，如以下查询：

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

Shell

要查找购买价格不在20到100(含20到100)之间的产品，可将BETWEEN运算符与NOT运算符组合使用，如下：

您也可以使用少于(>)，大于(>)和逻辑运算符(AND)重写上述查询，如下所示 -

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

SQL

执行上面查询语句，得到以下结果 -

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

Shell

MySQL BETWEEN与日期类型数据示例

当使用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);

SQL

执行上面查询语句，得到以下结果 -

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

Shell

因为requiredDate列的数据类型是DATE，所以我们使用转换运算符将文字字符串“2013-01-01”和“2013-12-31”转换为DATE数据类型。

2.8 IS NULL运算符

要测试值是否为NULL值，需要使用IS NULL运算符。以下显示IS NULL运算符的语法：

value IS NULL

SQL

如果值为NULL，该表达式将返回true。否则返回false。

请注意，MySQL没有内置的BOOLEAN类型。它使用TINYINT(1)来表示BOOLEAN值，即1表示true，0表示false。

因为IS NULL是一个比较运算符，所以您可以在任何使用运算符的地方使用它，例如在SELECT或WHERE子句中。如下面的例子：

SELECT 1 IS NULL,  # -- 0
       0 IS NULL,  # -- 0
       NULL IS NULL; # -- 1;

SQL

要检查值是否不为NULL，请使用IS NOT NULL运算符，如下所示：

value IS NOT NULL

SQL

如果该值不为NULL，则此表达式返回true(也就是1)。否则返回false(也就是0)。请考虑以下示例：

SELECT 1 IS NOT NULL, #-- 1
       0 IS NOT NULL, #-- 1
       NULL IS NOT NULL; #-- 0;

SQL

上面查询语句，执行后得到以下结果 -

mysql> SELECT 1 IS NOT NULL, #-- 1
       0 IS NOT NULL, #-- 1
       NULL IS NOT NULL; #-- 0
 --------------- --------------- ------------------ 
| 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL |
 --------------- --------------- ------------------ 
|             1 |             1 |                0 |
 --------------- --------------- ------------------ 
1 row in set

Shell

2. MySQL IS NULL示例

我们将使用示例数据库(yiibaidb)中的customers表进行演示，customers表的结构如下所示 -

mysql> desc customers; ------------------------ --------------- ------ ----- --------- ------- | Field                  | Type          | Null | Key | Default | Extra | ------------------------ --------------- ------ ----- --------- ------- | customerNumber         | int(11)       | NO   | PRI | NULL    |       || customerName           | varchar(50)   | NO   |     | NULL    |       || contactLastName        | varchar(50)   | NO   |     | NULL    |       || contactFirstName       | varchar(50)   | NO   |     | NULL    |       || phone                  | varchar(50)   | NO   |     | NULL    |       || addressLine1           | varchar(50)   | NO   |     | NULL    |       || addressLine2           | varchar(50)   | YES  |     | NULL    |       || city                   | varchar(50)   | NO   |     | NULL    |       || state                  | varchar(50)   | YES  |     | NULL    |       || postalCode             | varchar(15)   | YES  |     | NULL    |       || country                | varchar(50)   | NO   |     | NULL    |       || salesRepEmployeeNumber | int(11)       | YES  | MUL | NULL    |       || creditLimit            | decimal(10,2) | YES  |     | NULL    |       | ------------------------ --------------- ------ ----- --------- ------- 13 rows in set

SQL

要查询没有销售代表的客户，请使用IS NULL运算符，如下所示：

SELECT 
    customerName, 
    country, 
    salesrepemployeenumberFROM
    customersWHERE
    salesrepemployeenumber IS NULLORDER BY customerName;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT 
    customerName, 
    country, 
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NULL
ORDER BY customerName;
 -------------------------------- -------------- ------------------------ 
| customerName                   | country      | salesrepemployeenumber |
 -------------------------------- -------------- ------------------------ 
| ANG Resellers                  | Spain        | NULL                   |
| Anton Designs, Ltd.            | Spain        | NULL                   |
| Asian Shopping Network, Co     | Singapore    | NULL                   |
| Asian Treasures, Inc.          | Ireland      | NULL                   |
| BG&E Collectables              | Switzerland  | NULL                   |
| Cramer Spezialitten, Ltd       | Germany      | NULL                   |
| Der Hund Imports               | Germany      | NULL                   |
| Schuyler Imports               | Netherlands  | NULL                   |
| Stuttgart Collectable Exchange | Germany      | NULL                   |
| Warburg Exchange               | Germany      | NULL                   |
... ...
 -------------------------------- -------------- ------------------------ 
22 rows in set

Shell

要查询有销售代表的客户，请使用IS NOT NULL运算符，如下查询语句 -

SELECT 
    customerName, 
    country, 
    salesrepemployeenumberFROM
    customersWHERE
    salesrepemployeenumber IS NOT NULLORDER BY customerName;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT 
    customerName, 
    country, 
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NOT NULL
ORDER BY customerName;
 ------------------------------------ ------------- ------------------------ 
| customerName                       | country     | salesrepemployeenumber |
 ------------------------------------ ------------- ------------------------ 
| Alpha Cognac                       | France      |                   1370 |
| American Souvenirs Inc             | USA         |                   1286 |
| Amica Models & Co.                 | Italy       |                   1401 |
| Anna's Decorations, Ltd            | Australia   |                   1611 |
| Atelier graphique                  | France      |                   1370 |
| Australian Collectables, Ltd       | Australia   |                   1611 |
| Australian Collectors, Co.         | Australia   |                   1611 |
| Australian Gift Network, Co        | Australia   |                   1611 |
| Auto Associs & Cie.                | France      |                   1370 |
| Auto Canal  Petit                  | France      |                   1337 |
| Auto-Moto Classics Inc.            | USA         |                   1216 |
| AV Stores, Co.                     | UK          |                   1501 |
| Baane Mini Imports                 | Norway      |                   1504 |
| Bavarian Collectables Imports, Co. | Germany     |                   1504 |
... ...
 ------------------------------------ ------------- ------------------------ 
100 rows in set

Shell

3. MySQL IS NULL的专用功能

为了兼容ODBC程序，MySQL支持IS NULL运算符的一些专门功能。

(1). 如果具有NOT NULL约束的DATE或DATETIME列包含特殊日期’0000-00-00‘，则可以使用IS NULL运算符来查找这些行。如下示例 -

CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT,
    title VARCHAR(255),
    begin_date DATE NOT NULL,
    complete_date DATE NOT NULL,
    PRIMARY KEY(id));
INSERT INTO projects(title,begin_date, complete_date)
VALUES('New CRM','2020-01-01','0000-00-00'),
      ('ERP Future','2020-01-01','0000-00-00'),
      ('VR','2020-01-01','2030-01-01');
SELECT 
       *
FROM
    projects
WHERE
    complete_date IS NULL;

SQL

在这个例子中，创建了一个 projects 新表，并将一些数据插入到表中。最后一个查询使用IS NULL来获取complete_date列中的值为“0000-00-00”的行。

(2). 如果变量@@sql_auto_is_null设置为1，则可以使用IS NULL运算符在执行INSERT语句后获取生成列的值。请注意，默认情况下，变量@@sql_auto_is_null为0。请参见以下示例。

首先，将变量@@sql_auto_is_null设置为1。

SET @@sql_auto_is_null = 1;

SQL

第二步，在projects表中插入一个新行：

INSERT INTO projects(title,begin_date, complete_date)VALUES('MRP III','2010-01-01','2020-12-31');

SQL

第三步，使用IS NULL运算符来获取id列的生成值：

SELECT 
    id
FROM
    projects
WHERE
    id IS NULL;

SQL

4. MySQL IS NULL优化

MySQL对于IS NULL运算符执行相同的优化方式与等于(=)运算符相同。

例如，MySQL在使用IS NULL运算符搜索NULL时使用索引，如以下查询所示：

SELECT 
    customerNumber, 
    salesRepEmployeeNumberFROM
    customersWHERE
    salesRepEmployeeNumber IS NULL;

SQL

查看EXPLAIN查询过程：

EXPLAIN SELECT 
    customerNumber, 
    salesRepEmployeeNumberFROM
    customersWHERE
    salesRepEmployeeNumber IS NULL;

SQL

执行上面查询语句，输出以下结果 -

mysql> EXPLAIN SELECT 
    customerNumber, 
    salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL;
 ---- ------------- ----------- ------------ ------ ------------------------ ------------------------ --------- ------- ------ ---------- -------------------------- 
| id | select_type | table     | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                    |
 ---- ------------- ----------- ------------ ------ ------------------------ ------------------------ --------- ------- ------ ---------- -------------------------- 
|  1 | SIMPLE      | customers | NULL       | ref  | salesRepEmployeeNumber | salesRepEmployeeNumber | 5       | const |   22 |      100 | Using where; Using index |
 ---- ------------- ----------- ------------ ------ ------------------------ ------------------------ --------- ------- ------ ---------- -------------------------- 
1 row in set

Shell

MySQL也可以优化组合col = value OR col IS NULL。请参阅以下示例：

EXPLAIN SELECT 
    customerNumber,
    salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber = 1370 OR salesRepEmployeeNumber IS NULL;

SQL

执行上面查询语句，得到以下结果 -

mysql> EXPLAIN SELECT 
    customerNumber,
    salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber = 1370 OR
    salesRepEmployeeNumber IS NULL;
 ---- ------------- ----------- ------------ ------------- ------------------------ ------------------------ --------- ------- ------ ---------- -------------------------- 
| id | select_type | table     | partitions | type        | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                    |
 ---- ------------- ----------- ------------ ------------- ------------------------ ------------------------ --------- ------- ------ ---------- -------------------------- 
|  1 | SIMPLE      | customers | NULL       | ref_or_null | salesRepEmployeeNumber | salesRepEmployeeNumber | 5       | const |   29 |      100 | Using where; Using index |
 ---- ------------- ----------- ------------ ------------- ------------------------ ------------------------ --------- ------- ------ ---------- -------------------------- 
1 row in set

Shell

在这个例子中，当应用优化时，EXPLAIN会显示ref_or_null。

如果您有一个列的组合键，MySQL可以对任何关键部分执行优化。假设在表t1的列c1和c2上有一个索引，以下查询被优化：

SELECT 
    *
FROM
    t1
WHERE
    c1 IS NULL;

SQL

本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。

转藏分享

QQ空间 QQ好友新浪微博微信

献花（0） +1

来自： L罗乐 > 《SQL》

举报/认领

0条评论

发表

请遵守用户评论公约

类似文章 更多

L罗乐

关注对话

TA的最新馆藏

劳动法丨第二次劳动合同到期公司必须续签！
超过退休年龄工作中突发疾病死亡算工伤吗？（高院最新案例）| 劳动法库
医疗期5大疑难问题详解 | 劳资在线
劳动者患病或非因工负伤，医疗期满后不能从事原工作和另行安排的工作的，用人单位可以解除劳动合同 | 劳...
女职工“三期”待遇有哪些 | 劳资在线
出差路途期间是否算作加班时间？

喜欢该文的人也喜欢更多

热门阅读换一换

【Mysql】教程全解（二） 运算符AND、OR、IN、BETWEEN、IS NULL

MySQL AND运算符示例

MySQL OR短路求值

运算符优先级

MySQL OR运算符示例

MySQL IN示例

MySQL IN与子查询

MySQL BETWEEN示例

MySQL BETWEEN与日期类型数据示例

2. MySQL IS NULL示例

3. MySQL IS NULL的专用功能

4. MySQL IS NULL优化

【Mysql】教程全解（二）运算符AND、OR、IN、BETWEEN、IS NULL