在实际工作中,我们用到的数据库表一般都比较大,记录条数在百万级甚至千万级。对于记录数比较多的表,只有在极少数情况下,才需要查询表里的全部数据。当我们只需要查询表里的部分数据时,需要我们在查询数据的时候指定查询条件。我们可以使用 WHERE 子句指定查询条件来过滤数据。可以在 WHERE
子句中使用的操作符包括:=,>,<,>=,<=,<>,BETWEEN AND,LIKE
等。
使用数值类型的列进行过滤
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 9.49;
SELECT prod_name, prod_price
FROM products
WHERE prod_price > 9.49;
SELECT prod_name, prod_price
FROM products
WHERE prod_price < 9.49;
SELECT prod_name, prod_price
FROM products
WHERE prod_price >= 9.49;
SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 9.49;
SELECT prod_name, prod_price
FROM products
WHERE prod_price <> 9.49;
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5.99 AND 11.99;
SELECT prod_name, prod_price
FROM products
WHERE prod_price >= 5.99 AND prod_price <= 11.99;
使用字符类型的列进行过滤
如果列的类型为字符串,在和值进行比较时,需要给值加上单引号。
SELECT prod_name, prod_price, vend_id
FROM products
WHERE vend_id = 'DLL01';
SELECT prod_name, prod_price, vend_id
FROM products
WHERE vend_id <> 'DLL01'
对值为 NULL
的值进行过滤
在数据库里还有一类值叫 NULL
,它表示没有任何值,它与字段包含 0、空字符串或仅仅包含空格不同。确定值是否为 NULL
不能简单的用 = NULL
,要用 IS NULL
。
SELECT cust_id, cust_name
FROM customers
WHERE cust_email IS NULL;
使用 OR、AND
操作符进行过滤
刚才在过滤数据时使用的都是单一条件,可以使用 OR、AND
操作符将多个条件组合在一起。AND
用来指示查询满足所有给定条件的行。
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id = 'BRS01' AND prod_price < 10;
OR
用来指示检索满足任一给定条件的行。
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id = 'BRS01' OR prod_price < 10;
AND
和 OR
组合在一起。假设我们要查询由 DLL01 或 FNG01 制造,且价格大于 9 美元的商品。如果我们将 SQL 写成如下这样:
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'FNG01' AND prod_price > 9;
我们发现结果是不对的,查询出来的有些商品的价格是不大于 9 美元的。这是因为 AND
操作符的运算优先级大于 OR
,查询的条件变成了商品由供应商 DLL01 制造或者由供应商 FNG01 制造并且商品价格大于 9 美元。正确的 SQL 语句应该写成如下形式:
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE (vend_id = 'DLL01' OR vend_id = 'FNG01') AND prod_price > 9;
建议:当过滤条件里包括 AND
和 OR
时,都应该使用括号明确地指定操作符的执行顺序,不要依赖操作符的默认求值顺序,即使默认求值顺序如希望的那样。使用括号没有什么坏处,它能够消除歧义。
假设我们要查询由 DLL01、FNG01 或 BRS01制造的商品。我们可以将 SQL 写成如下的形式:
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'FNG01' OR vend_id = 'BRS01';
使用 IN
操作符进行过滤
像上面这种查询,我们还有一种比较简便的写法:
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id IN ('DLL01', 'FNG01', 'BRS01');
IN
的用法同样适用于数值型字段,例如:
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_price IN (3.49, 11.99, 9.49);
IN
操作符后面还可以跟上 SQL 语句,例如:
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_price IN (SELECT DISTINCT prod_price
FROM products
WHERE prod_id = 'BR03' OR prod_id = 'BNBG01');
IN
操作符的优点
IN
操作符的语法更清楚、更直观。- 在与
AND
或OR
组合使用时,求值顺序更容易管理。 IN
操作符一般比一组OR
操作符执行得更快。IN
的最大优点是可以包含其他SELECT
语句。
使用 NOT
操作符进行过滤
NOT
操作符, 表示对后面的过滤条件取反
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE NOT vend_id = 'DLL01';
和下面的语句是等价的。
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id <> 'DLL01';
在这种简单的条件语句中,NOT
确实没有什么优势,但是当条件语句更复杂时,我们使用 NOT
可以非常方便地找出和条件不匹配的记录。
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_price NOT IN (3.49, 11.99, 9.49);
使用 LIKE
和 通配符进行过滤
前面介绍的过滤条件中使用的值都是已知的,但有时候需要过滤的值不是已知的,例如,我们检索产品名以 doll 为结尾的产品。这时候我们便用到 LIKE
操作符以及通配符。
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_name LIKE '%doll';
%
为通配符,表示任何字符出现任意次数。 通配符查询只能用于文本字段,非文本数据类型字段不能使用通配符搜索。 %
通配符可在搜索模式的任意位置使用。并且可以使用多个通配符。
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_name LIKE 'Fish%';
SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_name LIKE '%teddy%';
通配符不可以匹配 NULL
。另外一个通配符是下划线_。下划线的用途与 %
是一样的,但它只匹配单个字符,而不是多个字符。
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name LIKE '_ inch teddy bear';
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name LIKE '__ inch teddy bear';
- 与
%
能匹配0个字符不同,_总是刚好匹配一个字符。 - 通配符很好用,但是这种好用是有代价的,代价就是搜索要耗费更长的处理时间。
*使用通配符的注意事项: *
1.不用滥用通配符,如果其他操作符可以达到目的,就选用其他操作符。
2.在确实需要使用通配符时,也不要把通配符放在搜索模式的开始处,把通配符置于开始处,搜索起来是最慢的。
在过滤数据时,我们还可以适用正则匹配。在 MySQL 里使用REGEXP
关键字。
使用正则匹配进行过滤
在过滤数据时,我们还可以适用正则匹配。在 MySQL 里使用 REGEXP
关键字。
REGEXP
匹配开头
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^Fish';
REGEXP
匹配结尾
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP 'doll$';
[]
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[123]';
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[1-8]';
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[^123]';
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[^1-8]';
备注:示例中用到的表和数据可以通过链接(https://github.com/songw/sql)下载。
本系列目录:零基础入门 SQL 系列之(一)查询数据
零基础入门 SQL 系列之(二)排序
零基础入门 SQL 系列之(三)过滤数据
零基础入门 SQL 系列之(四)内置函数
零基础入门 SQL 系列之(五)数据汇总
零基础入门 SQL 系列之(六)表连接
零基础入门 SQL 系列之(七)组合
零基础入门 SQL 系列之(八)插入、更新、删除
零基础入门 SQL 系列之(九)创建和操作表
零基础入门 SQL 系列之(十)视图