Mysql常用关键字使用示例

limit 以及 offest 关键字常用技巧

# 仅使用limit关键字 此时 limit i，j后面两个参数各自表示的意义是 从第i+1条记录开始 取 j 条
# 如果此时limit 后只跟了1个参数 j，表示从第1条数据开始(包括第1条记录)，取 j 条
#使用limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量(从第1条记录开始) 。
#以下均为查询入职时间倒数第三的员工的所有信息
select * from employees order by hire_data limit 2,1;
select * from employees order by hire_data limit 1 offset 2;
#使用子查询
select * from employees where hire_data = (select distinct hire_data from employees order by hire_data limit 2,1)；

group by 以及having子句的常用技巧

#以下均为查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)

# 取from_date = hire_date
select emp.emp_no as "emp_no",sa.salary as "salary"
from employees as emp inner join salaries as sa on emp.emp_no = sa.emp_no
where sa.from_date = emp.hire_date 
order by emp.emp_no desc;

# 利用分组查询
select emp_no as "emp_no",salary as "salary"
from  salaries 
group by emp_no having min(from_date)
order by emp_no desc;

[Tips]:使用group by字段，select多个非聚合字段时的注意点：
虽然在MYSQL中使用GROUP BY分组时，我们可以select 多个非聚合字段，但是也正因为如此，这些非聚会非唯一字段的值无法唯一的确定，所以最终查询出来的的结果可能并不准确，
此时若select 中的多个非聚合字段不唯一时，此时查询的结果集中这些非聚合非唯一字段的值是随机选取该字段中的其中一行的值作为结果！！！，所以最终结果并不一定是准确的

# Q：对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
/**
错误示范：因为emp_no并不是主键，所以后面再进行group by 进行分组查询时，可能导致结果不正确。
这是因为虽然在MYSQL中使用GROUP BY分组时，我们可以select 多个非聚合字段，但是也正因为如此，这些非聚会非唯一字段的值无法唯一的确定，所以最终查询出来的的结果可能并不准确，
 此时select 中的多个非聚合字段若不唯一时，此时查询的结果集中这些非聚合非唯一字段的值是随机选取该字段中的其中一行的值作为结果！！！
 */
select s1.emp_no, s1.salary, count(distinct s2.salary) as rank from salaries as s1 inner join salaries as s2 
on s1.to_date = "9999-01-01" and s2.to_date = "9999-01-01"  where s1.salary <= s2.salary group by s1.emp_no
order by s1.salary desc , s1.emp_no; 

# 正确代码：先构建不含salary的rank表，再将rank表和salaries表内接，然后排序得到结果
select a.emp_no,a.salary ,b.rank from salaries as a inner join (select s1.emp_no,count(distinct s2.salary) as rank from salaries as s1 inner join salaries as s2 
 on s1.to_date = "9999-01-01" and s2.to_date = "9999-01-01" where s1.salary <= s2.salary group by s1.emp_no) as b
 on a.emp_no = b.emp_no where a.to_date = "9999-01-01" order by a.salary desc,a.emp_no ;

#  使用窗口函数：
select emp_no, salary, dense_rank(order by salary desc) as rank from salaries where to_date = "9999-01-01" order by rank, emp_no ;
/**
下面介绍三种用于进行排序的专用窗口函数：

1、RANK()
    在计算排序时，若存在相同位次，会跳过之后的位次。
    例如，有3条排在第1位时，排序为：1，1，1，4······

2、DENSE_RANK()
    这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
    例如，有3条排在第1位时，排序为：1，1，1，2······

3、ROW_NUMBER()
    这个函数赋予唯一的连续位次。
    例如，有3条排在第1位时，排序为：1，2，3，4······

窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ]
                                ORDER BY <排序用列清单> ）
*其中[ ]中的内容可以忽略
*/ 

distinct关键字，仅能作用于select语句中

当distinct应用到多个字段的时候，其应用的范围是其后面的所有字段（保证其后面的所有字段是唯一的），而不只是紧挨着它的一个字段
而且distinct只能放到所有查询字段的前面，如下语句是错误的：

SELECT country, distinct province from person; // 该语句是错误的
select distinct country,province from person; //正确写法

#比如查询从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

select title,count(emp_no) t from (select distinct emp_no,title from titles) group by title having t >= 2;#先去重再计数

奇偶数判定

# 判断一个值为奇数还是偶数
select * from employees where last_name != "Mary" and emp_no % 2 = 1
order by hire_date desc;

# 使用Mod函数
select * from employees where last_name != "Mary" and mod(emp_no) = 1
order by hire_date desc;

Q:查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

# case1 使用聚集函数
select max(salary) - min(salary) from salaries where emp_no = "10001";
# case2 假设入职离职是同一天，则薪水的涨幅可以理解为(2-1)+(3-2)+(4-3) = 4 - 1 很奇妙的思路
select sum(s1.salary - s2.salary) from salaries as s1 
inner join salaries as s2 on s1.emp_no = "10001" and s2.emp_no = "10001"
and s1.from_date = s2.to_date；

查询涨幅情况

# Q：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面）
# 【Tips】:题目中没有说明不存在降薪的情况，所以不能直接用薪水的最大值-最小值
#分析题义：要求的是从入职以来的薪水涨幅情况，所以可以先查出一个人刚入职时的薪水，在查出其最新的薪水情况，然后做差值即可
select a.emp_no,(a.salary-b.salary) growth
from (select emp_no,salary from salaries where to_date = '9999-01-01') a
inner join (select s.salary,s.emp_no from employees e join salaries s on e.hire_date = s.from_date and e.emp_no = s.emp_no)b
on a.emp_no=b.emp_no
order by growth