select
USE sql_store;
/*
多行运行（所有结果都ouput）command + shift + enter
*/
-- SELECT * FROM sql_store.orders; # 取出sql_store下面的orders这个表
SELECT * FROM sql_store.customers; # 取出sql_store下面的customers这个表
/*
第二章：SELECT
*/
2.1 where
SELECT first_name, last_name # 指定列
FROM customers
WHERE points > 2000; # 条件查询，遍历逐个对比，然后返回
2.2 ORDER BY
SELECT *
FROM customers
-- WHERE state = "MA" # where 要在 order by 前面
ORDER BY first_name; # 按照first name 排序
SELECT
last_name,
first_name,
points,
points * 1.1 + 100 AS 'new points',
points % 10 AS '整除'
FROM customers
ORDER BY points;
SELECT DISTINCT state # np.unique
FROM customers;
2.3 <=>!
SELECT *
FROM customers
WHERE state !='va'; # 字符大小写相同
SELECT *
FROM customers
WHERE birth_date < '1980-01-01'; # 日期也是可以对比大小的
2.4 and or not in between
SELECT *
FROM customers
-- WHERE birth_date > '1990-01-01' and points > 1000;
-- WHERE birth_date > '1990-01-01' or points > 1000;
-- WHERE NOT (birth_date > '1990-01-01' OR points > 1000);
WHERE (birth_date > '1990-01-01' OR points > 1000) AND
state = 'VA';
SELECT *, unit_price * quantity AS totle_price
FROM order_items
WHERE order_id = 6 AND unit_price * quantity >30;
SELECT *
FROM customers
-- WHERE customer_id IN (1,4,7);
-- WHERE state IN ('va','fl','ga');
WHERE state NOT IN ('va','fl','ga');
SELECT *
FROM customers
-- WHERE points BETWEEN 2000 AND 3000
WHERE birth_date BETWEEN "1990-01-01" and "1999-12-31" ;
2.7 正则表达式匹配 like
SELECT *
FROM customers
-- WHERE last_name LIKE 'b%'; # 正则表达式：b开头
-- WHERE last_name LIKE '%o%'; # 正则表达式：包含o
-- WHERE last_name LIKE '%y'; # 正则表达式：包含o
-- WHERE last_name LIKE 'b____y'; # 正则表达式：b+4个字母+y
WHERE last_name LIKE 'b%y'; # 正则表达式：b+字母+y
SELECT *
FROM customers
WHERE phone LIKE "%9%" AND
address LIKE '%trail%' OR '%avenue%';
2.8 正则表达式匹配 regexp 包含某个字符/单词/..
SELECT *
FROM customers
-- WHERE last_name REGEXP 'field|MAC|rose'; # 匹配包含
-- WHERE last_name REGEXP '^field|MAC|rose'; # 匹配开头
-- WHERE last_name REGEXP '[gim]e'; # 匹配包含ge/ie/me
WHERE last_name REGEXP '[a-h]e'; # 匹配包含“a-h的单词+e”
/*
基本规则：
^ beginning
$ end
| or
[] any characters in []
[-] range
*/
小练习
SELECT *
FROM customers
-- WHERE first_name IN ('elka','ambur');
-- WHERE last_name REGEXP '^my|se';
WHERE last_name REGEXP 'b[ru]'; # 包含br or bu
2.9 空值 IS NULL
select * from customers
-- where phone is not null;
where phone is null;
select * from orders
where (shipped_date is null) or (comments is null);
2.10 order by （默认升序，加上desc就是降序）
mysql 独有的：orderby可以用表中任意列，无论列是否在select中
select customer_id,first_name,state,10 from customers
-- order by first_name;
-- order by birth_date desc;
order by state desc, first_name; #首先按state降序排，再按firstname升序排
### 更复杂的情况，可以用列的运算来排序：
select * from order_items
where order_id = 2
order by (quantity * unit_price) desc; # 用单位价格*销量来排序
2.11 limit，返回前几条
select * from customers
limit 6,3 ; # 跳过前6条，选择3条
select *, quantity * unit_price as total_price
from order_items
order by (quantity * unit_price) desc
limit 5; # 选取总额最高的三个