Mysql常用方法

# 常用
1、select ifnull((select distinct salary from Employee order by salary desc limit 1,1), null) as 'SecondHighestSalary'
2、BEGIN  set N = N - 1;  END
3、对结果集进行排序  PARTITION BY 分组， ORDER BY 排序
dense_rank()   select salary, dense_rank over(partition by 字段 order by salary desc) as 'index' from 表名
   对分组后的结果集拼接  group_concat   distinct 去重，separator 分隔符 （默认',')
group_concat()  select score, group_concat(distinct student separator '%') from exam group by score;
4、编写一个 SQL 查询，查找所有至少连续出现三次的数字。 
select distinct num as 'ConsecutiveNums' from Logs where (id+1,num) in (select * from Logs) and (id+2,num) in (select * from Logs)

select distinct l1.num as 'ConsecutiveNums' from Logs as l1, Logs as l2, Logs as l3 where l1.num = l2.num and l2.num = l3.num and l1.id = l2.id + 1 and l2.id = l3.id + 1

5、编写SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。
select (case
        when mod(id,2) = 1 and id = (select count(*) from Seat) then id # 奇数，最后一名 不用动
        when mod(id,2) = 1 then id+1                                    # 奇数，不是最后一名
        else id-1  #偶数
        end) as id, student from Seat order by id
select if(id % 2 = 0, id - 1, if(id=(select count(*) from Seat),id,id+1)) as id, student from Seat
6、判断某个值为null： is null
   当询问 id not in (select p_id from tree)时, 因为p_id有null值, 返回结果全为false
   =, <, or <> 任何与null值的对比都将返回null.
7、avg(函数表达式)   x1+x2+x3/count(*)      sum(函数表达式)  除了count，所有聚合函数运算规则都是先排除null在进行计算
8、MySQL不需要group by的字段怎么查询
  5.7和以后的版本提供了一个函数any_value()来解决分组时获取group by中没有的字段
  MAX() 函数
  自连接、左连接
8、like  %多个字符  _单个字符
9、select e.employee_id  from Employees e left join Salaries s on e.employee_id = s.employee_id where s.employee_id is null
union 
SELECT s.employee_id
FROM salaries s LEFT JOIN employees e ON s.employee_id = e.employee_id WHERE e.employee_id IS NULL order by employee_id # 共同的字段 order by