MySQL(一)

牛客网题霸SQL篇(001-010)

---

001

题目描述：请查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

背景描述：

员工表

部门表

题解：

select s.last_name,s.first_name,d.dept_no 
from employees s 
left join dept_emp d 
on d.emp_no = s.emp_no;

知识：

1. INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。
2. ON与WHERE有什么区别，两个表连接时用on，在使用left jion时，on和where条件的区别如下：on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

002

题目描述：请查找employees里入职员工时间排名倒数第三的员工所有信息

背景描述：

员工表

题解：

select * 
from employees 
order by hire_date desc limit 2,1;

知识：

1. LIMIT m,n : 表示从第m+1条开始，取n条数据；LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。

2. 首先需要加distinct去重。若不加distinct去重，那么按照日期倒序，limit 2,1（从倒数第2行开始，取一条数据）；加了distinct去重，会按入职日期进行分组，多个相同入职日期会分为一组

3. 外层的where条件中根据子查询查出的倒数第三晚入职的日期，就能查询出符合条件的员工信息。

003

题目描述：请查找employees里最晚入职员工的所有信息

背景描述：

员工信息表

题解：

select * 
from employees 
order by hire_date DESC limit 1; 

select * 
from employees 
where hire_date = (
select max(hire_date)
from employees);

知识：

SELECT *   选取所有列

FROM employees    提取哪个表的数据

ORDER BY hire_date desc   以哪个数据降序排列

LIMIT 1 取第一个

004

题目描述：请查找各个部门领导薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

背景描述：

全部员工薪水表

各部门领导表

题解：

select s.*,d.dept_no 
from dept_manager d,salaries s 
where s.to_date='9999-01-01' 
and d.to_date='9999-01-01' 
and s.emp_no = d.emp_no;

select s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no 
from salaries s
join dept_manager d
on d.emp_no = s.emp_no;

知识：

用隐式内连接，显式内连接，和外连接均可。需要注意的是左外连接 on后不能直接加and当作限制条件，显示外连接可以。

005

题目描述：请查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示。

背景描述：

员工表

部门表

题解：

select s.last_name,s.first_name,d.dept_no 
from dept_emp d 
left join employees s 
on d.emp_no = s.emp_no;

select employees.last_name,employees.first_name,dept_emp.dept_no 
from employees,dept_emp 
where employees.emp_no = dept_emp.emp_no;

知识：

1. INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。
2. WHERE 连接也可以。

006

题目描述：请你查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t。

背景描述：

薪水表

题解：

select emp_no,count(emp_no) as t 
from salaries 
group by emp_no 
having t>15;

知识：

1. 用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2. 根据题意，输出的变动次数为t，故用AS语句将COUNT(emp_no)的值转换为t
3. 由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件

007

题目描述：查找所有员工入职时候的薪水情况，给出emp_no以及salary,并按照emp_no继续您逆序（注意，一个员工可能有多次涨薪）

背景：

员工表

薪水表

题解：

select e.emp_no,s.salary
from employees e
inner join salaries s #表的链接
on e.emp_no = s.emp_no 
where e.hire_date = s.from_date #入职时间
order by e.emp_no desc; #逆序排列

运行结果：

008

题目描述：找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

背景描述：

薪水表

题解：

select salary 
from salaries 
where to_date='9999-01-01' 
group by salary 
order by salary desc;

select distinct salary #distinct 去重
from salaries s
where s.to_date = '9999-01-01'
order by salary desc; #desc 逆序

运行结果：

009

题目描述：获取所有部门当前manager的当前薪水情况，给出dept_no,emp_no以及salary，当前表示to_date='9999-01-01'。

背景描述：

部门管理者表

薪水表

题解：

select d.dept_no,d.emp_no,s.salary 
from dept_manager d 
inner join salaries s 
on d.emp_no = s.emp_no 
where d.to_date ='9999-01-01' 
and s.to_date = '9999-01-01';

运行结果：

010

题目描述：找出所有非部门领导的员工emp_no。

背景描述：

员工表

部门领导表

题解：

select emp_no 
from employees 
where emp_no not in ( 
    select e.emp_no 
    from employees e 
    inner join dept_manager d 
    on e.emp_no = d.emp_no )

SELECT emp_no 
FROM employees 
WHERE emp_no NOT IN (
    SELECT emp_no 
    FROM dept_manager)

运行结果：