【学习】mysql学习

20190528

一、数据分析深入浅出

数据分析 深入浅出.png

二、mysql必知必会

mysql 必知必会.png

三、leecode题库

刷leecode数据库题，涉及的函数。
1、IFNULL函数：MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数，两个参数可以是文字值或表达式。
如题：
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

Select
    IFNULL(
      (Select DISTINCT Salary
       from Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary;

2、聚集函数不能直接用in匹配
问题：来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个
错误写法：

delete from person where id not in (select min(id) id from person group by email);

正确写法：

delete from person where id not in (select id from(select min(id) id from person group by email)t);

3、计算连续出现数字的问题
问题：编写一个 SQL 查询，查找所有至少连续出现三次的数字。
标准答案：

select distinct l.Num as 'ConsecutiveNums' from logs l,logs l1,logs l2 where l.Id=l1.Id-1 and l.Id=l2.Id-2 and l.Num=l1.Num and l.Num=l2.Num

还有种更复杂的联结方式

select  distinct(logs.num)as ConsecutiveNums from logs,(select (id-1)id,num from logs)a,(select (id-2)id,num from logs)b where logs.id=a.id and a.id=b.id and logs.num=a.num and a.num=b.num;

4、case+when的多级嵌套问题
问题：小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的。小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
标准答案(直接不进行多级嵌套，而是序列判断)：

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 (case 
        when (id mod 2)=1 and id=(select count(*) from seat ) then id
        else  when (id mod 2)=1 then id+1
                 else id-1 end)
        end
             )id,student from seat order by id

错误答案修正：

select (case 
        when (id mod 2)=1 and id=(select count(*) from seat ) then id
        else    (case when (id mod 2)=1 then id+1
                             else id-1 end)
        end
             )id,student from seat order by id

20190517
开始练习MySql经典50，有一些之前没涉及的函数，做一些笔记记录。

1、有这样一组数据：('01','A'),('01','B'),('02','B'),('02','C')，如何返回字母序列为｛A,B｝的序列号。

方法：限定数据域处在目标阈中+数据域数目和目标阈一致

create table AA(num varchar(6),aname varchar(6));

insert into AA(num,aname) values ('01','A'),('01','B'),('02','B'),('02','C');

select * from AA;

select AA.num from AA where AA.aname in ('A','B') group by AA.num having count(*)=2;

2、mysql的rank排序。
方法：
A：不可以用rank函数：
赋值排序

select student.*, (@a:=@a+1) from student, (select @a:=0) a;

image.png

按sname列排序(先内部排序+赋值排序)

select stt.*,(@a:=@a+1) as rankname 
from (select student.* from student order by sname)stt,(select @a:=0) 

image.png

普通rank：
思路1：score 然后进行用（@rank:=@rank+1）进行排序，再group by score 取min(rank)，再联立
思路2：a 表 left join a'，要求排序项a<a'，再group by并进行count(*)计数，再将非1的count值加上1 即为排名。

dense_rank排序：
思路1：distinct score 然后进行用（@rank:=@rank+1）进行排序，再联立

partition by排序：
思路1：先分组，再排序，再union一起；
思路2：自联结a和a'，联结条件是组类一致并且排序项a<=a'，再group by 排序项，并且count计算排序项的数目即为排名（但这个也只适用普通rank）。

B：可以用rank函数：
按sname列排序

select  student.*,rank() over (order by Sname) st_rank from student;

image.png

按sid，sname分组，组内按sname排序

select  student.*,rank() over (partition by sid,sname order by Sname) st_rank from student;

image.png

C:涉及到排序，但是不用返回具体排名的问题：
若不能用rank函数，能用limit解决，尽量用limit解决，不用先计算排名，如以下问题：
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资前三高的员工。

select d.name as Department,e.name as Employee,e.Salary 
from employee e,department d
where e.departmentid=d.id 
and ((e.departmentid,e.salary) in 
(select * from(select distinct departmentid,salary from employee where departmentid=1 order by salary desc limit 3)a) 
or
 (e.departmentid,e.salary) in (select * from(select distinct departmentid,salary from employee where departmentid=2 order by salary desc limit 3)b)
)
order by Department asc,salary desc;

或者自联结的方法（以下这种方法不用管部门到底有多少数目，因此不会有很多个or出现）

select d.name as Department,
       e.name as Employee,
       e.Salary 
from employee e 
inner join department d 
on e.departmentid=d.id 
left join employee as e1 
on e.departmentid=e1.departmentid and e.salary<e1.salary 
group by e.departmentid,e.id 
having count(DISTINCT(e1.Salary))<3
order by e.departmentid asc,e.salary desc;

3、null值排序问题
null值排序默认第一，所以针对有null值的列进行排序，需要先排序再联结（left join 保留null值）。
先联结再排序：

select *, rank()over(partition by cid01.cid order by cid01.score desc)rank01 
from student 
left join 
(select * from sc where sc.cid='01')cid01 on student.sid=cid01.sid;

image.png

先排序再联结：

select student.*,cid01.rank01
from student
left join
(select sc.sid,rank()over(partition by sc.cid order by sc.score desc)rank01 from sc where sc.cid='01')cid01
on student.sid=cid01.sid;

image.png