摘要

• 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