0. SQL基础

• inner join和left join的区别
• union和union all的区别
• union, minus, intersect的区别
• where和having的区别
• distinct和unique的区别
• delete和truncate的区别

1. 日期函数的用法

• date_add/date_sub
• datediff
• year/month

2. 字符串函数的用法

• concat/group_concat
• replace
• substring/left/right

3. 窗口函数的用法

• row_number() over (partition by ... order by ...)
  例：返回某次交易是这一天的第几次交易
• rank/dense_rank() over (partition by ... order by ...)
  例：返回各部门工资前三高的同事
• lag/lead(column, offset, default) over ()
  例：返回连续出现三次的数字

4. 创建函数

create function getNthHighestSalary(N int)
return int
begin
declare M int;
set M = N - 1;
return
(
  select distinct salary from employee order by salary desc limit M, 1
) ;
end