CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

SELECT column_name
INTO newtable
FROM table1;

select 'mobile' as platform union
select 'desktop' as platform union
select 'both' as platform 

#多个left join
select a.*,b.*,c.* 
from a left join b on a.id=b.id 
left join c on b.id=c.id

select *
from Trips T left join  Users  U1 on T.client_id =U1.users_id  
left join  Users U2 on T.driver_id  =U2.users_id  

from Trips T1
left join Users U1 on (T1.client_id =U1.users_id  and U1.banned ="Yes" )    不可

from Trips T1
join Users U1 on (T1.client_id =U1.users_id  and U1.banned ="Yes" ) 可

select a.Score as Score,
        (select count(DISTINCT b.Score) from Scores  b where b.Score >= a.Score) as 'Rank'
from Scores a
order by a.Score DESC

select * from table where name like "%三%"

--type1
CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END
--type2
CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END

# Write your MySQL query statement below
select t.business_id 
from 
(select *, avg(E1.occurences  ) over(partition by E1.event_type ) as ave_occu
from Events E1 ) t
where t.occurences > t.ave_occu
group by t.business_id
having    count(t.business_id)>=2

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

select distinct expression[,expression...] from tables [where conditions];

##建表
Create table If Not Exists Spending (user_id int, spend_date date, platform ENUM('desktop', 'mobile'), amount int);
Truncate table Spending;
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'desktop', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-01', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-02', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-01', 'desktop', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-02', 'desktop', '100');

###查询
select distinct spend_date ,count(user_id ) 
from Spending

##result
##返回结果就一行，distinct后多行，count一行，多行一行还是一行；count结果还是distinct前的数量
spend_date	count(user_id )
2019-07-01	6

WITH expression_name (column_list)
AS
(
    -- Anchor member
    initial_query  
    UNION ALL
    -- Recursive member that references expression_name.
    recursive_query  
)
-- references expression name
SELECT *
FROM   expression_name

SELECT 1,-1,N'Boss'
UNION  ALL
SELECT 11,1,N'A1'
UNION  ALL
SELECT 12,1,N'A2'
UNION  ALL
SELECT 13,1,N'A3'
UNION  ALL
SELECT 111,11,N'B1'
UNION  ALL
SELECT 112,11,N'B2'
UNION  ALL
SELECT 121,12,N'C1'

drop TABLE if EXISTS s1;
create table s1(
    id int ,

    age int,
    email varchar(30),
index(id)

    );

window_function ([expression]) OVER (
   [ PARTITION BY part_list ]
   [ ORDER BY order_list ]
   [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

sum(frequency) over(partiton by   num )  --分组累加
sum(frequency) over()         			--total_frequency  全部累加
sum(frequency) over(order by num desc)  --desc_frequency, 逆序累加
sum(frequency) over(order by num asc)   --asc_frequency,正序累加
SUM(Salary) OVER (PARTITION BY Id ORDER BY Month asc range 2 PRECEDING) --range 2 PRECEDING 当前以及前面2行

avg(frequency) over()  					--total_frequency ，全部平均
avg(frequency) over(order by num desc)  --desc_frequency, 逆序平均
avg(frequency) over(order by num asc)   --asc_frequency,  正序平均

select  distinct C1.seat_id as seat_id
from Cinema C1 join Cinema C2
on C1.free=1 and C2.free=1 and abs(C1.seat_id-C2.seat_id)=1
order by seat_id

# Write your MySQL query statement below
select  round(min(Power(Power(P1.x-P2.x,2)+Power(P1.y-P2.y,2),0.5)),2) as shortest 
from Point2D   P1 join Point2D  P2 
on P1.x!=P2.x or P1.y!=P2.y

1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选； 
3、group by子句将数据划分为多个分组； 
4、聚合函数进行计算； 
5、having子句筛选分组； 
6、计算所有的表达式； 
7、select字段； 
8、order by对结果集进行排序。