Mysql知识点整理
刷Leecode时遇到的MySQL知识点整理
1. case ... when ... then ...[when ... then ...] else ... end
https://blog.csdn.net/helloxiaozhe/article/details/78124138
2. limit offset
二者通常与order by联合使用以求取第几大第几小的记录。
使用区别：假设要返回某一字段的第2-4大的数据
limit 3 offset 1 --读取三条，从第二条开始读。读取数量在前，偏移量在后
limit 1,3 --从第二条（偏移量从0开始）开始读，读取三条。读取数量在后，偏移量在前
select distinct salary from employee order by salary desc limit 3 offset 1
select distinct salary from employee order by salary desc limit 1,3
此外，使用limit,offset可以实现分页查询,pageNumber:页码，numberPerPage：每页数据量
select * from table_name [过滤条件] limit pageNumber offset (pageNumber-1)*numberPerPage
select * from table_name [过滤条件] limit (pageNumber-1)*numberPerPage,pageNumber
3. IFNULL(expr1,expr2)
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值
对于2的第一个例子，更严谨的写法是(当查不到数据时返回null，参考https://leetcode-cn.com/problems/second-highest-salary)，这个也是提醒我们要考虑严谨。
select ifnull((select distinct salary from employee order by salary desc limit 1,3),null) as conSalary
4. MySQL Date 函数
http://www.w3school.com.cn/sql/sql_dates.asp
DATE_ADD(date,INTERVAL expr type) : 函数向日期添加指定的时间间隔，例date_add('2019-03-31',interval 1 day)
DATE_SUB(date,INTERVAL expr type) : 函数从日期减去指定的时间间隔，例SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
DATEDIFF(date1,date2) ： 函数返回两个日期之间的天数，例SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate 返回1，要是第一个参数小于第二个参数就返回负值
EXTRACT(unit FROM date) ： 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
CURTIME()： 函数返回当前的时间。
CURDATE()： 函数返回当前的日期。
NOW()： 函数返回当前的日期和时间。
DATE(date)： 函数返回日期或日期/时间表达式的日期部分
DATE_FORMAT(date,format)：函数用于以不同的格式显示日期/时间数据。
5. join、inner join、left join、right join、full join
参考http://www.w3school.com.cn/sql/sql_join.asp
join和inner join相同，full join相当于left join、right join查询的并集
6.判断字段奇偶的方式
https://blog.csdn.net/zhazhagu/article/details/80452473
7.group by，order by，having，聚合函数等作用于结果集上
8.错误
You can't specify target table 'person' for update in FROM clause
https://zhidao.baidu.com/question/68619324.html
不能对同一个表select的同时进行更新操作，例如下面的select t.id from就是避免对同一表操作
Every derived table must have its own alias
子查询必须有别名，例如下面的别名t必须有
例：题目来自于https://leetcode-cn.com/problems/delete-duplicate-emails/
Unknown column 't.id' in 'field list'
min(id)默认列名就是min(id)，所以要记得加别名id
delete from person where id not in (
select t.id from (
select min(p.id) id
from person p
group by p.email) t
)
9.distinct c1,c2,...
过滤掉c1,c2,....都相同的数据记录，即判断多列同时不重复
10. and,or优化查询
https://www.cnblogs.com/ouhouki/p/9661927.html
11.MySQL中实现rank排名查询
https://blog.csdn.net/justry_deng/article/details/80597916
例子：https://leetcode-cn.com/problems/rank-scores/
select score,
convert(case
when @preS = score then @curR
when @preS := score then @curR := @curR+1
when @preS = 0 then @curR := @curR+1
end,unsigned integer) as rank
from Scores , (select @curR:=0, @preS:=NULL ) r
order by score desc
---------------------------------------
SELECT Score, (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS Rank FROM Scores s ORDER BY Score DESC ;
第二种性能不如第一种
12.自定义函数
https://leetcode-cn.com/problems/nth-highest-salary/
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n = n-1;
RETURN (
# Write your MySQL query statement below.
select t.salary from(select distinct salary from employee order by salary desc limit 1 offset n) t
);
END
13. case when then或者if交换座位问题
https://leetcode-cn.com/problems/exchange-seats/
--------------------------------方法一----------------------------------
select t.id,
case
when t.id%2=0 then (select s.student from seat s where s.id = t.id-1 )
when t.id=(select max(id) from seat) then student
when t.id%2=1 then (select s.student from seat s where s.id = t.id+1 )
end as student
from seat t
----------------------------------方法二----------------------------------
select if(mod(id,2)=1 and id = (select count(1) from seat),id,if(mod(id,2)=1,id+1,id-1)) id,student from seat order by 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;
方法二三，性能相当，if性能稍快于case（可能受网络影响），都比第一种快了一倍，第一种因为两个子查询耗时。
14. 重命名不能出现在where后面吗？
（下面会报Unknown column 'c' in 'where clause'）
https://leetcode-cn.com/problems/consecutive-numbers/
select num,
convert((
case
when @pre=num then @n:=@n+1
when @pre:=num then @n:=1
end
),unsigned int) as c
from logs ,(select @n:=0,@pre:=null)r where c>=3（去掉“where c>=3”就正确了）
-------------------------------------------正确答案-------------------------------------------------------------------------
select distinct t.num as ConsecutiveNums from (select num,
convert((
case
when @pre=num then @n:=@n+1
when @pre:=num then @n:=1
end
),unsigned int) as c
from logs ,(select @n:=0,@pre:=null)r)t
where t.c>=3
注意点：
①变量前的@一定不要忘带，“:”不要忘写
②别名c不能在当前where中使用，会报c找不到
③distinct过滤掉重复的（[1,3],[1,4],[1,5],[1,6],[1,7]，如果不过滤的话会输出[1,1,1,1,1]）
15.聚合函数只能select被聚合的字段以及分组字段，其他字段查询，会发生错位，因为其它字段与被聚合字段和分组字段是多对一的关系。
16. 派生表关联，多表关联
题目来自https://leetcode-cn.com/problems/department-highest-salary/
有Employee，Department 两张表和由Employee生成的派生表，共有3张表，每张表取一个字段。一定要让三个表互相关联到，不然会出现数据错乱
三表关联where测试比join关系性能好点，不知原理为何？
select d.name Department , e.name Employee, m.salary
from Employee e,Department d,
(select max(salary) salary,departmentid from employee group by departmentid)m
where e.Salary = m.Salary and d.id=m.departmentid and e.DepartmentId =d.id
------------------
select d.name Department, e.name Employee, m.salary
from
(select max(salary) salary,departmentid from employee group by departmentid) m
join Employee e on e.Salary = m.Salary
join Department d on d.id=m.departmentid and d.id = e.departmentid
要加强学习，做到知其然，也要知其所以然。
17.round(x,d)：x保留d位小数
为了得到而努力
2019-04-01
转载请注明来处。
