牛客网-数据库61题做题打卡（1-10题）

本文链接：https://blog.youkuaiyun.com/weixin_43288829/article/details/106591231

建表准备

create database nk_exe;
use nk_exe;
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 `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 `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`)
) ;

CREATE TABLE `departments` (
  `dept_no` CHAR(4) NOT NULL,
  `dept_name` VARCHAR (40) NOT NULL,
  PRIMARY KEY (`dept_no`)
) ;
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'1990-08-05','9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'1989-09-12','9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'1986-12-01','9999-01-01');
INSERT INTO dept_manager VALUES('d005',10010,'1996-11-24','2000-06-26');
INSERT INTO dept_manager VALUES('d006',10010,'2000-06-26','9999-01-01');

INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

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

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

2、查找入职员工时间排名倒数第三的员工所有信息
【注：查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天】

SELECT 
	e.* 
FROM(
	SELECT 
	  emp_no,
	 row_number () over (
	ORDER BY hire_date DESC) AS pm 
	FROM 
	  employees ) r
JOIN employees e ON e.`emp_no`=r.emp_no
where pm=3;
# 本题采用开窗函数-序号函数row_number 显示分区中不重复不间断的序号

3、查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
【注:请以salaries表为主表进行查询，输出结果以salaries.emp_no升序排序，并且请注意输出结果，dept_no列是最后一列】

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

4、查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)

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

#员工表employees中可能有部分员工无部门（如CEO）；
#而部门表dept_emp中正常情况下每个员工都有部门；

5、查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工

SELECT 
	e.last_name,
	e.first_name,
	d.dept_no
FROM employees e
LEFT JOIN dept_emp d  ON e.emp_no=d.emp_no;
# 注意区分第4题和第5题题目条件

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

select 
	e.emp_no,
	s.salary
from employees e
left join salaries s on e.emp_no=s.emp_no
where e.hire_date=s.from_date
order by e.emp_no desc;

#employees是一表，一个员工可能存在多次涨薪的情况，故将employees作为主表

7、查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

#方一：
select 
	f_n.* 
from(
	select emp_no,count(*) as t
	from salaries
	group by emp_no) as f_n
where t>15;
#方二：
select 
	emp_no,
	count(*) as t
from salaries
group by emp_no
having t>15;

#注：少用子查询，在数据量大的时候，子查询的效率低下

8、找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

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

#对于distinct与group by的使用: 1、当对系统的性能高并数据量大时使用group by 2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用group by

9、获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

select 
	d.dept_no,
	d.emp_no,salary
from dept_manager d
left join salaries s on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01';
#注：同一emp_no在salaries表中对应多条涨薪记录，而当s.to_date = '9999-01-01'时
#是该员工当前的薪水记录

10、获取所有非manager的员工emp_no

select 
	e.emp_no
from employees e
where e.emp_no not in (
	select
		emp_no
	from dept_manager );

以上代码均可在牛客网通过，如有更优代码欢迎交流探讨，若文章有不妥之处，望各位指正。