第四章 过滤

1.基本描述

    本章主要讲解在SELECT、UPDATE、DELETE语句中的WHERE子句所能使用的各种类型的过滤条件。

2.基本样例

SELECT pt.name product_type, p.name product FROM product p INNER JOIN product_type pt
ON p.product_type_cd = pt.product_type_cd WHERE pt.name = 'Customer Accounts';

SELECT pt.name product_type, p.name product FROM product p INNER JOIN product_type pt
ON p.product_type_cd = pt.product_type_cd WHERE pt.name <> "Customer Accounts";

SELECT emp_id, fname, lname, start_date FROM employee 
WHERE start_date < '2007-01-01';

SELECT emp_id, fname, lname, start_date FROM employee
WHERE start_date < '2007-01-01' AND start_date >= '2005-01-01';

SELECT emp_id, fname, lname, start_date FROM employee 
WHERE start_date BETWEEN '2005-10-01' AND '2007-01-01';

SELECT emp_id, fname, lname, start_date FROM employee
WHERE start_date BETWEEN '2007-01-01' AND '2005-01-01';

SELECT emp_id, fname, lname, start_date FROM employee
WHERE start_date >= '2007-01-01' AND start_date <= '2005-01-01';

SELECT emp_id, fname, lname, start_date FROM employee 
WHERE start_date >= '2007-01-01' AND start_date <= '2005-01-01';

SELECT account_id, product_cd, cust_id, avail_balance FROM account
WHERE avail_balance BETWEEN 3000 AND 5000;

SELECT cust_id, fed_id FROM customer WHERE cust_type_cd = 'I'
AND fed_id BETWEEN '50-00-0000' AND '999-99-9999';

SELECT account_id, product_cd, cust_id, avail_balance FROM account
WHERE product_cd = 'CHK' OR product_cd = 'SAV'
OR product_cd = 'CD' OR product_cd = 'MM';

SELECT account_id, product_cd, cust_id, avail_balance FROM account
WHERE product_cd IN ('CK', 'SAV', 'CD', 'MM');

SELECT account_id, product_cd, cust_id, avail_balance FROM account
WHERE product_cd IN (SELECT product_cd FROM product WHERE product_type_cd = 'ACCOUNT');

SELECT account_id, product_cd, cust_id, avail_balance FROM account 
WHERE product_cd NOT IN ('CHK', 'SAV', 'CD', 'MM');

#SELECT emp_id, fname, lname, FROM employee WHERE LEFT(lname, 1) = 'T';

SELECT lname FROM employee WHERE lname LIKE '_a%e%';

SELECT cust_id, fed_id FROM customer WHERE fed_id LIKE '___-__-___';

SELECT emp_id, fname, lname FROM employee WHERE lname LIKE 'F%' OR lname LIKE 'G%';

SELECT emp_id, fname, lname FROM employee WHERE lname REGEXP '^[FG]';

SELECT emp_id, fname, lname, superior_emp_id FROM employee WHERE superior_emp_id IS NULL;

SELECT emp_id, fname, lname, superior_emp_id FROM employee WHERE superior_emp_id = NULL;

SELECT emp_id, fname, lname, superior_emp_id FROM employee WHERE superior_emp_id IS NOT NULL;

SELECT emp_id, fname, lname, superior_emp_id FROM employee WHERE superior_emp_id != 6;

SELECT emp_id, fname, lname, superior_emp_id FROM employee 
WHERE superior_emp_id != 6 OR superior_emp_id IS NULL;