-- 过滤数据
SELECT prod_name,prod_price
FROM Products
WHERE prod_price = 3.49;
-- WHERE子句操作符
SELECT prod_name,prod_price
FROM Products
WHERE prod_price < 10;
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 != 'DLL01';
-- 范围值检查
SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
-- 空值检查
SELECT cust_name
FROM Customers
WHERE cust_email IS NULL;
-- 高级数据过滤
-- AND操作符
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <=4
ORDER BY prod_name;
-- OR操作符
SELECT prod_name,prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
-- 求值顺序
SELECT prod_name,prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01' )
AND prod_price >= 10;
-- IN操作符
SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;
-- IN 操作符与 OR 操作符可以实现相同的功能
SELECT prod_name,prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
ORDER BY prod_name;
-- NOT 操作符
SELECT prod_name,vend_id
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
SELECT prod_name,vend_id
FROM Products
WHERE vend_id != 'DLL01'
ORDER BY prod_name;
-- 用通配符进行过滤
-- LIKE 操作符
-- % 通配符,可以匹配0个，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 结尾的名字
-- _ 通配符,只能匹配单个字符
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '% inch teddy bear';
-- [] 通配符 mysql 不支持
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
-- mysql 不支持 [] 通配符,可以写成以下这种形式
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE 'J%' OR cust_contact LIKE 'M%'
ORDER BY cust_contact;
