MySQL學習足跡記錄04--數據過濾--WHERE 1.使用WHERE子句
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_price=2.50; +---------------+------------+ | prod_name | prod_price | +---------------+------------+ | Carrots | 2.50 | | TNT (1 stick) | 2.50 | +---------------+------------+
TIPs: *在同時使用ORDER BY和WHERE子句時,ORDER BY位於WHERE之後,否則出錯。 2.WHERE子句的操作符 等於:= 不等於:<>或!= 小於:< 小於等於:<= 大於:> 大於等於:>= 在指定的兩個值之間 BETWEEN 3.檢測單個值
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_name='fuses'; +-----------------+---------------+ #和字符串比較需用單引號限定 | prod_name | prod_price | +-----------------+---------------+ | Fuses | 3.42 | +-----------------+----------------+
4.小於限定
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_price<10; +---------------+------------+ | prod_name | prod_price | +---------------+------------+ | .5 ton anvil | 5.99 | | 1 ton anvil | 9.99 | | Carrots | 2.50 | | Fuses | 3.42 | | Oil can | 8.99 | | Sling | 4.49 | | TNT (1 stick) | 2.50 | +---------------+------------+ 7 rows in set (0.00 sec)
5.小於等於限定
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_price<=10; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | .5 ton anvil | 5.99 | | 1 ton anvil | 9.99 | | Bird seed | 10.00 | | Carrots | 2.50 | | Fuses | 3.42 | | Oil can | 8.99 | | Sling | 4.49 | | TNT (1 stick) | 2.50 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 9 rows in set (0.00 sec)
6.不匹配檢查
eg: mysql> SELECT vend_id,prod_name FROM products WHERE vend_id <>1003; #等效於SELECT vend_id,prod_name FROM products # WHERE vend_id != 1003; +---------+--------------+ | vend_id | prod_name | +---------+--------------+ | 1001 | .5 ton anvil | | 1001 | 1 ton anvil | | 1001 | 2 ton anvil | | 1002 | Fuses | | 1005 | JetPack 1000 | | 1005 | JetPack 2000 | | 1002 | Oil can | +---------+--------------+ 7 rows in set (0.00 sec)
7.范圍值檢查(BETWEEN)
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5.99 AND 10.00; +----------------+------------+ #注意,BETWEEN兩邊的取值為閉區間 | prod_name | prod_price | +----------------+------------+ | .5 ton anvil | 5.99 | | 1 ton anvil | 9.99 | | Bird seed | 10.00 | | Oil can | 8.99 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 5 rows in set (0.00 sec)
8.空值檢查(IS NULL) *NULL:無值(no value),並不等於0,空字符串或僅僅包含空
eg: 先列出包含空值的表:customers; mysql> SELECT * FROM customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | [email protected] | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse |NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | [email protected] | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | [email protected] | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd |NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 5 rows in set (0.00 sec) mysql> SELECT cust_id FROM customers WHERE cust_email IS NULL; +---------+ | cust_id | +---------+ | 10002 | | 10005 | +---------+ 2 rows in set (0.00 sec)