摘要
- case when
- if
- inner join
- table 简写
- union
1.case when
#增加一列的正常命令
alter table student add (column) state varchar(2) (after gg);
#还可以有
select custom ,
(case when custom_num between 10 and 100 then 1
when custom_num between 100 and 200 then 2
else 3 end) as custom_group
from customers
#新形成一个列叫custom_group，如果case1 的话 值为1，case2为2剩下为3
注意end不要忘记
2.if
SELECT status,
(CASE WHEN status = 'Shipped' THEN 'YES' ELSE 'NO' END) AS Whether_Shipped_CASE,
(IF(status = 'Shipped', 'YES', 'NO')) AS Whether_Shipped_IF
FROM Orders;
单个的条件筛选用if比较合适
3. inner join
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
Left Join 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
Right Join 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
4.table简写
在table后可紧跟简写，但只在本条命令有效。
create table Person_Order
SELECT B.LastName, FirstName, City, A.OrderNo FROM Orders A
LEFT JOIN Persons B
ON A.Id_P=B.Id_P;
5. union
纵向合并
SELECT column_name(s) FROM table_name1
UNION / UNION ALL
SELECT column_name(s) FROM table_name2
