mysql必知必会 （三）过滤数据（where子句指定搜索条件）

相等测试

mysql> select prod_name,prod_price
    -> from products
    -> where prod_price = 3.49;
+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
+---------------------+------------+
3 rows in set (0.00 sec)

where子句操作符

mysql> select prod_name, prod_price
    -> from products
    -> where prod_name = 'fuses';
Empty set (0.00 sec)

注意相等测试不是用==，就用赋值符号，这和其他语言都不一样

不匹配测试

mysql> select prod_name, prod_price
    -> from products
    -> where prod_name <> 'fuses';
+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| 8 inch teddy bear   |       5.99 |
| 12 inch teddy bear  |       8.99 |
| 18 inch teddy bear  |      11.99 |
| Raggedy Ann         |       4.99 |
| King doll           |       9.49 |
| Queen doll          |       9.49 |
+---------------------+------------+
9 rows in set (0.00 sec)

范围值检查

mysql> select prod_name, prod_price
    -> from products
    -> where prod_price between 3 and 8;
+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| 8 inch teddy bear   |       5.99 |
| Raggedy Ann         |       4.99 |
+---------------------+------------+
5 rows in set (0.00 sec)

空值检查

mysql> select prod_name, prod_price
    -> from products
    -> where prod_price is null;
Empty set (0.00 sec)

总结

• order by子句必须在where子句之后