WHERE常见语法

• WHERE主要是给选择语句添加条件, 进行筛选, 涉及到一些常见的运算符语句

# IN 运算
SELECT *
FROM customers
WHERE state='VA' OR state='GA' OR state='FL';

SELECT *
FROM customers
WHERE state IN ('VA', 'GA', 'FL');
# 练习
-- Return products with quantity in stock equal to 49, 38, 72
SELECT *
FROM products
WHERE quantity_in_stock IN (49, 38, 72);

# BETWEEN 
SELECT *
FROM customers
WHERE points >= 1000 AND points <= 3000;

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000;
# 练习
-- return customers born between 1/1/1990 and 1/1/2000
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01';

# LIKE 匹配字符串, % 多个任意字符, _ 单个任意字符
SELECT *
FROM customers
WHERE last_name LIKE 'b%';

SELECT *
FROM customers
WHERE last_name LIKE '%b%';

SELECT *
FROM customers
WHERE last_name LIKE '%y';

SELECT *
FROM customers
WHERE last_name LIKE '%y';

SELECT *
FROM customers
WHERE last_name LIKE '%y';

SELECT *
FROM customers
WHERE last_name LIKE '_y';

SELECT *
FROM customers
WHERE last_name LIKE '_____y';
-- % any number of characters
-- _ single character

# 练习
-- GET the customers whose
-- address contain TRAIL or AVENUE

SELECT *
FROM customers
WHERE address LIKE '%TRAIL%' OR address LIKE '%AVENUE%';
-- phone numbers end with 9
SELECT *
FROM customers
WHERE phone LIKE '%9';
SELECT *
FROM customers
WHERE phone NOT LIKE '%9';

-- REGEPX
SELECT *
FROM customers
WHERE last_name LIKE '%field%';

SELECT *
FROM customers
WHERE last_name REGEXP 'field';
-- 以field 开头
SELECT *
FROM customers
WHERE last_name REGEXP '^field';
-- 以 field 结尾
SELECT *
FROM customers
WHERE last_name REGEXP 'field$';
-- 搜寻多个单词
SELECT *
FROM customers
WHERE last_name REGEXP 'field$|mac|rose';
-- 前面包含多个选项[]
SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e';

SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e[lv]';
# exerise
-- GET the customers whose
-- First names are ELKA or AMBUR
SELECT *
FROM customers
WHERE first_name LIKE '%ELKA%' OR first_name LIKE '%AMBUR';

SELECT *
FROM customers
WHERE first_name REGEXP 'ELKA|AMBUR';
-- last names end with EY or ON
SELECT *
FROM customers
WHERE last_name REGEXP 'EY$|ON$';
-- last names start with MY or contains SE
SELECT *
FROM customers
WHERE last_name REGEXP '^MY|SE';
-- last names contain B followed by R or U
SELECT *
FROM customers
WHERE last_name REGEXP 'b[ru]';

-- NULL
SELECT *
FROM customers
WHERE phone IS NULL;

SELECT *
FROM customers
WHERE phone IS NOT NULL;
# Exercise 
-- Get the orders that are not shipped
SELECT *
FROM orders
WHERE shipped_date IS NULL;
# WHERE 用法
USE sql_store;
SELECT *
FROM customers
WHERE points > 3000;

SELECT *
FROM customers
WHERE state = 'va';

SELECT *
FROM customers
WHERE state != 'va';

SELECT *
FROM customers
WHERE birth_date > '1990-01-01';

# 练习
-- Get the orders placed this year
SELECT *
FROM orders
WHERE order_date >= '2019-01-01';
SELECT *
FROM orders
WHERE YEAR(order_date) = '2019';

# WHERE多条件
SELECT *
FROM customers
WHERE birth_date > '1990-01-01' AND points > 1000;

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR points > 1000;

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR (points > 1000 AND state = 'VA');

SELECT *
FROM customers
WHERE NOT(birth_date > '1990-01-01' OR points > 1000);

# 练习
-- FROM the order_items table, get the items for order #6
-- where the local price is greater than 30
SELECT *
FROM order_items
WHERE order_id = 6 AND (quantity * unit_price) > 30;