牛客网SQL实战二刷 | Day1

最近开始复习和巩固SQL，每天边做边在word文档做笔记，发现刚好10天刷完所有61道习题。打算二刷题目，重新整理题目和思路，转战博客做笔记。这是写给自己的学习笔记，但也希望，正在看这篇博文的你，也发现它很有用～

每篇笔记的格式大致为，三大板块：

• 大纲
• 题目（题目描述、思路、代码、参考资料）
• 回顾

题目来源：https://www.nowcoder.com/ta/sql 那我们开始Day 1的笔记吧！

一、大纲

题号	知识点
1	MAX() 函数
2	LIMIT 和 OFFSET 的用法
3	INNER JOIN 连接两张表
4	INNER JOIN 连接两张表
5	LEFT JOIN 连接两张表
6	INNER JOIN/ 并列查询/ ORDER BY 逆序排列

二、题目

1. 查找最晚入职员工的所有信息

• 题目描述

查找最晚入职员工的所有信息
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

• 输出描述

emp_no	birth_date	first_name	last_name	gender	hire_date
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15

• 思路
  用MAX() 函数找出“最晚”的记录。

• 代码

SELECT * FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees)

• MAX() 函数用法参考
  https://www.runoob.com/sql/sql-func-max.html

2. 查找入职员工时间排名倒数第三的员工所有信息

• 题目描述

查找入职员工时间排名倒数第三的员工所有信息
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

• 输出描述

emp_no	birth_date	first_name	last_name	gender	hire_date
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12

• 思路

1. 用LIMIT和OFFSET找出「倒数第三」条记录；
2. 注意，用distinct去重，因为可能有多个重复的日期。

• 代码

SELECT * FROM employees 
WHERE hire_date = 
(SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 1 OFFSET 2)

• Limit 与offset 一起使用的用法
  举个例子，LIMIT 3 OFFSET 1， 这意味着，跳过第1条记录（即从第2条记录开始），返回接下来3条记录。即最终得到，原本的第2，3，4条记录。

  参考：https://www.runoob.com/sqlite/sqlite-limit-clause.html

3. 查找当前薪水详情以及部门编号dept_no

• 题目

查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

• 输出描述

emp_no	salary	from_date	to_date	dept_no
10002	72527	2001-08-02	9999-01-01	d001
10004	74057	2001-11-27	9999-01-01	d004
10005	94692	2001-09-09	9999-01-01	d003
10006	43311	2001-08-02	9999-01-01	d002
10010	94409	2001-11-23	9999-01-01	d006

• 思路

1. 两张表内连接（inner join），通过emp_no关联
2. 两个“当前” —— 当前领导、当前薪水，用to_date = “9999-01-01” 限制

• 代码

SELECT s.*, dm.dept_no
FROM salaries AS s INNER JOIN dept_manager AS dm
ON s.emp_no = dm.emp_no 
WHERE s.to_date = '9999-01-01' AND dm.to_date = '9999-01-01'

• 关于几种连接方式
  
  这张图的韦恩图表述的很清晰，我摘录在之前的笔记里，现找不到出处了，来源网络。

4. 查找所有已经分配部门的员工的last_name和first_name

• 题目

查找所有已经分配部门的员工的last_name和first_name
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

• 输出描述

last_name	first_name	dept_no
Facello	Georgi	d001
省略	省略	省略
Piveteau	Duangkaew	d006

• 思路
  为了筛选出「已经分配部门的员工 」，用INNER JOIN通过关键字 emp_no连接两张表即可。类似题3。

• 代码

SELECT e.last_name, e.first_name, de.dept_no
FROM employees AS e
INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no

5. 查找所有员工的last_name和first_name以及对应部门编号dept_no

• 题目

查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

• 输出描述

last_name	first_name	dept_no
Facello	Georgi	d001
省略	省略	省略
Sluis	Mary	NULL(在sqlite中此处为空,MySQL为NULL)

• 思路
  区别于题目4， 「也包括展示没有分配具体部门的员工」，此时，用LEFT JOIN连接两张表。employees 表左连接dept_emp表，会返回employees表的全部数据，即便dept_emp表无对应数据。

  JOINS的用法可查看题目3的参考资料。

• 代码

SELECT e.last_name, e.first_name, de.dept_no
FROM employees AS e
LEFT JOIN dept_emp AS de ON e.emp_no = de.emp_no

6. 查找所有员工入职时候的薪水情况

• 题目

查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

• 输出描述

emp_no	salary
10011	25828
省略	省略
10001	60117

• 思路

1. 用emp_no关键字连接两张表；
2. 「所有员工入职时候的薪水情况」，即通过 employees表的hire_date = salaries表的from_date 限制；
3. 「按照emp_no进行逆序」，即用ORDER BY 对emp_no进行排序。

• 代码

1. 方法一：INNER JOIN连接两张表

SELECT s.emp_no, s.salary
FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY s.emp_no DESC

2. 方法二：并列查询

SELECT s.emp_no, s.salary
FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY s.emp_no DESC

• INNER JOIN VS 并列查询
  Q: 这两种方法有什么不同呢？
  A: 内连接是取左右两张表的交集形成一个新表，用FROM并列两张表后仍然还是两张表。如果还要对新表进行操作则要用内连接。从效率上看应该FROM并列查询比较快，因为不用形成新表。本题从效果上看两个方法没区别。

  来源：牛客网，作者：wasrehpic；https://www.nowcoder.com/questionTerminal/23142e7a23e4480781a3b978b5e0f33a

三、回顾

对应着大纲，再次梳理归类Day1的六道题，

题号	知识点
1	运用函数MAX（）
2	运用子句 LIMIT，搭配OFFSET
3、4、5、6	表连接（INNER JOIN / LEFT JOIN)
6	ORDER BY语句排序

这是我的第一篇博文～ 喜欢，请继续关注，谢谢！

————————————————

2019.7.24 更新：
这是我的第一篇博文，也是「牛客网SQL实战二刷」系列的第一天。如今，系列已经完成，可以点击以下链接，查看完整解析～❤️

《牛客网SQL实战二刷 | 完整解析 – 目录索引》