MySQL学习足迹记录04--数据过滤--WHERE

页面导航:首页 > 数据库 > Mysql > MySQL学习足迹记录04--数据过滤--WHERE

MySQL学习足迹记录04--数据过滤--WHERE

来源: 作者: 时间:2016-02-18 10:28 【

MySQL学习足迹记录04--数据过滤--WHERE1.使用WHERE子句 eg: mysql SELECT prod_name,prod_price FROM products WHERE prod_price=2.50;+---------------+------------+| prod_name | prod...
MySQL学习足迹记录04--数据过滤--WHERE
 
1.使用WHERE子句
 eg:
  > 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        |      |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  |NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    |  |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        |     |
|   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)

 


Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<