自学第六章 过滤数据
1、WHERE 子句
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
场景:由于数据库的规模大小不一,所以通常不需要也不必要检索所有的行,因此通过WHERE子句进行过滤
note:
需要特别注意的是,WHERE 和 ORDER BY 一起使用时,使用顺序为 WHERE ===> ORDER BY ,否则会出现报错;
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50 ORDER BY prod_name;
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50 ORDER BY prod_name DESC;
2、子句操作符
相等:
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
- 字符类型的数据需要加双引号或者单引号
- MySQL在执行匹配时默认不区分大小写,所以fuses与Fuses匹配。
小于:
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
小于等于:
SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;
不等于:
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;
区间范围:
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
- 使用BETWEEN时,必须指定两个值——所需范围的低端值和高端值。
- 这两个值必须用AND关键字分隔。
- BETWEEN匹配范围中所有的值,包括指定的开始值和结束值。
空值检查:
SELECT prod_name FROM products WHERE prod_price IS NULL;
SELECT cust_id FROM customers WHERE cust_email IS NULL;