【SQL笔记】二、过滤数据

目录

1.过滤数据

1.1 WHERE

1.2 WHERE子句操作符

1.3 测试题

2.高级过滤数据（在WHERE子句中使用操作符）

2.1 AND OR 操作符

2.2 IN操作符

2.3 NOT操作符

2.4 测试题

3.用通配符进行过滤

3.1 百分号（%）通配符

3.2 下划线（_）通配符

3.3 方括号（[ ]）通配符

3.4 测试题

---

1.过滤数据

1.1 WHERE

        在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

        这条语句从products表中检索两个列，但不返回所有行，只返回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

注：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

1.2 WHERE子句操作符

 例如，列出所有价格小于 10 美元的产品

SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 10;

列出所有不是供应商DLL01制造的产品 

SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

SELECT vend_id, prod_name
FROM Products
WHERE vend_id != 'DLL01';

使用BETWEEN操作符，检索价格在 5 美元和 10 美元之间的所有产品

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

注：在使用 BETWEEN 时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用 AND 关键字分隔。BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。

空值检查：

NULL，无值（no value），它与字段包含 0、空字符串或仅仅包含空格不同。

确定值是否为 NULL，不能简单地检查是否等于 NULL。SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。这个 WHERE 子句就是 IS NULL 子句。例如

SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

1.3 测试题

        编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品。

SELECT prod_id, prod_name
FROM Products
WHERE prod_price = 9.49;

        编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9 美元或更高的产品。

SELECT prod_id, prod_name
FROM Products
WHERE prod_price >= 9;

        结合第 3 课和第 4 课编写 SQL 语句，从 OrderItems 表中检索出所有不同订单号（order_num），其中包含 100 个或更多的产品。

SELECT DISTINCT order_num
FROM OrderItems
WHERE quantity >=100;

        编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序。

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price;

2.高级过滤数据（在WHERE子句中使用操作符）

2.1 AND OR 操作符

        为了进行更强的过滤控制，SQL 允许给出多个WHERE子句。这些子句有两种使用方式，即以AND子句或OR子句的方式使用。

        用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符（logical operator）。

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

        OR操作符与AND操作符正好相反，它指示 DBMS 检索匹配任一条件的行。

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

注：SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。例如

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
      AND prod_price >= 10;

上例会先考虑vend_id ‘BRS01’和prod_price的逻辑关系，再去和vend_id 'DLL01'进行一个或的逻辑关系，如果要改变逻辑关系就需要加括号。

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
      AND prod_price >= 10;

2.2 IN操作符

        IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。例如

SELECT prod_name, prod_price
FROM Products
WHERE vend_id  IN ('DLL01','BRS01')
ORDER BY prod_name;

上例中表示要在Products中检索由供应商DLL01和BRS01制造的所有产品，从功能上看，上例中的IN操作符完成了和OR操作符一样的功能。

2.3 NOT操作符

        WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。例如

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

上例中的SELECT语句匹配的是DLL01之外的产品。

2.4 测试题

1.编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家 [USA] 和州 [CA] 进行过滤，没准其他国家也存在一个加利福尼亚州）。提示：过滤器需要匹配字符串。

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA';

2.编写 SQL 语句，查找所有至少订购了总量 100 个的 BR01、BR02 或 BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量，并按产品 ID 和数量进行过滤。提示：根据编写过滤器的方式，可能需要特别注意求值顺序。

-- Solution 1
SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE (prod_id='BR01' OR prod_id='BR02' OR prod_id='BR03')
 AND quantity >=100;

-- Solution 2
SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE prod_id IN ('BR01','BR02','BR03')
 AND quantity >=100;

3.现在，我们回顾上一课的挑战题。编写 SQL 语句，返回所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price）。使用 AND，然后按价格对结果进行排序。

SELECT prod_name, prod_price
FROM products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price;

3.用通配符进行过滤

        通配符（wildcard）是用来匹配值的一部分的特殊字符。搜索模式（search pattern）由字面值、通配符或两者组合构成的搜索条件。

        为在搜索子句中使用通配符，必须使用LIKE操作符。当操作符作为谓词时其不再称为操作符。从技术上说，LIKE是谓词而不是操作符。

注：通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

3.1 百分号（%）通配符

        在搜索串中，%表示任何字符出现任意次数。例如

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

上例中匹配了所有以Fish开头的产品。

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

上例中匹配了任何位置包含文本bean bag的值。

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

上例中匹配所有以F开头，以y结尾的值。（注：对于’Fucy  ‘这种y后面有空格的情况，’F%y是匹配不出来的‘，因为’Fucy‘是以空格结尾，不是以y结尾）

注：%还能匹配 0 个字符。%代表搜索模式中给定位置的 0 个、1 个或多个字符。

注：%不会与NULL匹配。

3.2 下划线（_）通配符

        下划线的用途与%一样，但它只匹配单个字符，不能多也不能少。

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '_ _ inch teddy bear';

输出：

prod_id      prod_name
--------     --------------------
BR02         12 inch teddy bear
BR03         18 inch teddy bear

        方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

3.3 方括号（[ ]）通配符

        方括号（[]）通配符用来指定一个字符集，

        例如，找出所有名字以 J 或 M 起头的联系人，可进行如下查询：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

        其中，[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。此通配符可以用前缀字符 ^（脱字号）来否定。

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

        上例中查询匹配以J和M之外的任意字符起头的任意联系人名。也可以使用NOT操作符得出类似结果，例如

SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

3.4 测试题

1.编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品。

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%';

2.反过来再来一次。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品。这次，按产品名称对结果进行排序。

SELECT prod_name, prod_desc
FROM Products
WHERE NOT prod_desc LIKE '%toy%'
ORDER BY prod_name;

3.编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';

4.来个比较棘手的。我没有特别向你展示这个语法，而是想看看你根据目前已学的知识是否可以找到答案。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%';