「牛客网SQL实战」| Day1

☺☺☺
数据库的知识一直想复习，既然如此就专门开一个模块来练习题叭~
打算半个月完成实战的练习，中间还会穿插一些理论和其他知识。
然后每个题的流程依然是：题目描述+分析+代码+笔记。

Day1六道题

考查知识点

题号	知识点
1	MAX()求最大值； DESC降序排列（默认为升序排列ASC）
2	limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据；或者limit y offset x； DISTINCT去重
3	考察表的链接，用INNER JOIN...ON ... JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段；或者WHERE 限制链接也可以
4	考察表的链接
5	LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据
6	考察分析题的能力；ORDER BY排序

一、查找最晚入职员工的所有信息

• 题目描述：

查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)
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));

• 分析：
  在选择数据时让入职时间一栏值为最大，即这个人入职时间最晚。用MAX()函数。
• 代码：

SELECT a.emp_no, a.birth_date, a.first_name, a.last_name, a.gender, MAX(a.hire_date)
FROM employees a

或者：

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

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

• 题目描述：

查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
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));

• 分析：

1. 用ORDER BY 对入职时间进行排序；
2. LIMIT y OFFSET x对要输出的数据个数以及位置进行限制，表示跳过x条数据从第y个数据开始输出；
3. DISTINCT去重：去除重复的日期
   注意：DESC降序排列，因为这个多次没有通过用例

• 代码：

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

• 笔记：SQL查询语句中的 limit 与 offset

1. limit y 分句表示: 读取 y 条数据
2. limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
3. limit y offset x 分句表示: 跳过 x 条数据，从第x+1条数据开始读取 y 条数据（OFFSET表偏移）

三、查找当前薪水详情以及部门编号dept_no

• 题目

查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
(注:请以salaries表为主表进行查询，输出结果以salaries.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));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL, – ‘部门编号’
emp_no int(11) NOT NULL, – ‘员工编号’
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

• 思路

1. 使用INNER JOIN... ON将表连接到一起，然后排序输出。INNER JOIN 与 JOIN 是相同的。
2. 或者使用WHERE将表的连接到一起，排序输出
3. 即以下两句代码的效果是一样的

SELECT * FROM a INNER JOIN b ON a.id = b.id
和
SELECT * FROM a,b WHERE a.id = b.id

• 代码：两种实现的效果是一样的
  使用INNER JOIN的代码：

SELECT a.*, b.dept_no
FROM salaries a INNER JOIN dept_manager b ON a.emp_no=b.emp_no
WHERE a.to_date='9999-01-01' AND b.to_date='9999-01-01'
ORDER BY a.emp_no

不使用INNER JOIN 的代码：

SELECT a.*, b.dept_no
FROM salaries a , dept_manager b 
WHERE a.to_date='9999-01-01' AND b.to_date='9999-01-01' and a.emp_no=b.emp_no
ORDER BY a.emp_no

四、查找所有已经分配部门的员工的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));

• 思路
  考察表的链接
• 代码

SELECT a.last_name, a.first_name, b.dept_no
FROM employees a, dept_emp b
WHERE a.emp_no = b.emp_no

或者

SELECT a.last_name, a.first_name, b.dept_no
FROM employees a INNER JOIN dept_emp b ON a.emp_no = b.emp_no

五、查找所有员工的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));

• 思路：
  用左链接LEFT JOIN ON 将两个表链接起来，这样左边的表中数据无论存在与否都会输出。
• 代码：

SELECT a.last_name, a.first_name, b.dept_no
FROM employees a LEFT JOIN dept_emp b ON a.emp_no=b.emp_no

• 笔记：

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

六、查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

• 题目描述：

查找所有员工入职时候的薪水情况，给出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));

• 思路：

1. 查找入职时候的薪水情况，所以条件要使用salaries.from_date = employees.hire_date 来进行限制；
2. 按照emp_no逆序，则最后需要排序，并且降序排列；
3. 一个员工可能又多次涨薪的情况，因此salaries中的值不唯一，但employees中的值是唯一的，用a.emp_no = b.emp_no条件限制即可。

• 代码：

SELECT a.emp_no, b.salary
FROM employees a, salaries b 
WHERE a.emp_no = b.emp_no
AND a.hire_date = b.from_date
ORDER BY a.emp_no
DESC