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

 

转载请注明来处。

 

转载于:https://www.cnblogs.com/malw/p/10631706.html