SQL实战01

SQL实战01

转载自牛客网：

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`));

输出描述: 

 插入8条数据记录：

INSERT INTO employees VALUES(10001,'1982-01-22','Sami','Kate','F','2010-06-10');INSERT INTO employees VALUES(10002,'1983-09-22','Simi','Kim','F','2011-06-18');INSERT INTO employees VALUES(10003,'1984-01-12','Tomas','Yang','M','2011-09-11');INSERT INTO employees VALUES(10004,'1985-08-19','Alice','Jim','F','2012-03-16');INSERT INTO employees VALUES(10005,'1985-11-13','Weddy','van','F','2013-01-08');INSERT INTO employees VALUES(10006,'1986-12-1','Wed','Water','M','2013-07-18');INSERT INTO employees VALUES(10007,'1987-12-19','Gorge','Lee','M','2013-10-18');INSERT INTO employees VALUES(10008,'1991-12-19','Rory','Ben','M','2020-1-18');

查询表数据内容：

SELECT * FROM employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1982-01-22 | Sami       | Kate      | F      | 2010-06-10 |
|  10002 | 1983-09-22 | Simi       | Kim       | F      | 2011-06-18 |
|  10003 | 1984-01-12 | Tomas      | Yang      | M      | 2011-09-11 |
|  10004 | 1985-08-19 | Alice      | Jim       | F      | 2012-03-16 |
|  10005 | 1985-11-13 | Weddy      | van       | F      | 2013-01-08 |
|  10006 | 1986-12-01 | Wed        | Water     | M      | 2013-07-18 |
|  10007 | 1987-12-19 | Gorge      | Lee       | M      | 2013-10-18 |
|  10008 | 1991-12-19 | Rory       | Ben       | M      | 2020-01-18 |
+--------+------------+------------+-----------+--------+------------+
8 rows in set (0.00 sec)

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

SELECT * FROM employees ORDER BY hire_date DESC limit 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10008 | 1991-12-19 | Rory       | Ben       | M      | 2020-01-18 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

思路：查询雇佣表，排序根据hire_date(倒序)，限制数据条目为1

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

select *  from employees  order by hire_date desc  limit 2,1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10006 | 1986-12-01 | Wed        | Water     | M      | 2013-07-18 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

注释：select * from table limit 2,1;

//跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数

select * FROM employees order by hire_date desc limit 1 offset 2;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10006 | 1986-12-01 | Wed        | Water     | M      | 2013-07-18 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

注释：select * from table limit 1 offset 2;  

//从第1条（不包括）数据开始取出1条数据，limit后面跟的是1条数据，offset后面是从第2条开始读取，即读取第2条数据

3 查找各个部门当前(_date='9999-01-01')领导当前(_date='9999-01-01')薪水详情以及其对应部门编号dept_no

(注:请以salaries表为主表进行查询，输出结果以p_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`));
Query OK, 0 rows affected, 2 warnings (0.02 sec)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`));
Query OK, 0 rows affected, 1 warning (0.01 sec)

插入数据

 select * FROM salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  72567 | 2000-09-11 | 9999-01-01 |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
|  10003 |  49881 | 2001-07-30 | 9999-01-01 |
|  10004 |  94692 | 2001-09-09 | 9999-01-01 |
|  10005 |  93392 | 2001-03-01 | 9999-01-01 |
|  10006 |  87692 | 2001-02-09 | 9999-01-01 |
|  10007 |  94382 | 2002-01-11 | 9999-01-01 |
|  10008 |  87301 | 2001-06-29 | 9999-01-01 |
+--------+--------+------------+------------+
8 rows in set (0.00 sec)

SELECT * FROM dept_manager;
+---------+--------+------------+
| dept_no | emp_no | to_date    |
+---------+--------+------------+
| d001    |  10001 | 9999-01-01 |
| d002    |  10002 | 9999-01-01 |
| d003    |  10003 | 9999-01-01 |
| d004    |  10004 | 9999-01-01 |
| d005    |  10005 | 9999-01-01 |
| d006    |  10006 | 9999-01-01 |
| d007    |  10007 | 9999-01-01 |
| d008    |  10008 | 9999-01-01 |
+---------+--------+------------+
8 rows in set (0.00 sec)

根据题干描述，需要两张表联合查询需要用到inner join，on (结合起来的条件s.emp_no&#p_no)，salaries表为主表，限制条件为to_date='9999-01-01' ,使用emp_no字段进行排序,表名可以用as进行简写

SELECT s.*,d.dept_no from salaries as s 
inner join dept_manager as d p_no&#p_no 
_date='9999-01-01' _date='9999-01-01' 
order p_no;
+--------+--------+------------+------------+---------+
| emp_no | salary | from_date  | to_date    | dept_no |
+--------+--------+------------+------------+---------+
|  10001 |  72567 | 2000-09-11 | 9999-01-01 | d001    |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 | d002    |
|  10003 |  49881 | 2001-07-30 | 9999-01-01 | d003    |
|  10004 |  94692 | 2001-09-09 | 9999-01-01 | d004    |
|  10005 |  93392 | 2001-03-01 | 9999-01-01 | d005    |
|  10006 |  87692 | 2001-02-09 | 9999-01-01 | d006    |
|  10007 |  94382 | 2002-01-11 | 9999-01-01 | d007    |
|  10008 |  87301 | 2001-06-29 | 9999-01-01 | d008    |
+--------+--------+------------+------------+---------+
8 rows in set (0.00 sec)

或者：

SELECT salaries.*,dept_manager.dept_no from salaries 
inner join dept_manager p_no=p_no 
where _date='9999-01-01' _date='9999-01-01' 
order p_no;
+--------+--------+------------+------------+---------+
| emp_no | salary | from_date  | to_date    | dept_no |
+--------+--------+------------+------------+---------+
|  10001 |  72567 | 2000-09-11 | 9999-01-01 | d001    |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 | d002    |
|  10003 |  49881 | 2001-07-30 | 9999-01-01 | d003    |
|  10004 |  94692 | 2001-09-09 | 9999-01-01 | d004    |
|  10005 |  93392 | 2001-03-01 | 9999-01-01 | d005    |
|  10006 |  87692 | 2001-02-09 | 9999-01-01 | d006    |
|  10007 |  94382 | 2002-01-11 | 9999-01-01 | d007    |
|  10008 |  87301 | 2001-06-29 | 9999-01-01 | d008    |
+--------+--------+------------+------------+---------+
8 rows in set (0.00 sec)

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

 SELECT e.last_name,e.first_p_no from employees as e inner join dept_manager as d p_no&#p_no;
+-----------+------------+--------+
| last_name | first_name | emp_no |
+-----------+------------+--------+
| Kate      | Sami       |  10001 |
| Kim       | Simi       |  10002 |
| Yang      | Tomas      |  10003 |
| Jim       | Alice      |  10004 |
| van       | Weddy      |  10005 |
| Water     | Wed        |  10006 |
| Lee       | Gorge      |  10007 |
| Ben       | Rory       |  10008 |
+-----------+------------+--------+
8 rows in set (0.00 sec)

5 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)

select e.last_name,e.first_name,d.dept_no 
from employees e left join dept_manager d 
p_no&#p_no;
+-----------+------------+---------+
| last_name | first_name | dept_no |
+-----------+------------+---------+
| Kate      | Sami       | d001    |
| Kim       | Simi       | d002    |
| Yang      | Tomas      | d003    |
| Jim       | Alice      | d004    |
| van       | Weddy      | d005    |
| Water     | Wed        | d006    |
| Lee       | Gorge      | d007    |
| Ben       | Rory       | d008    |
+-----------+------------+---------+
8 rows in set (0.00 sec)

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

p_no,s.salary from salaries s 
left join employees e p_no&#p_no 
WHERE e.hire_date=s.from_date 
ORDER p_no DESC;
+--------+--------+
| emp_no | salary |
+--------+--------+
|  10008 |  87301 |
|  10007 |  94382 |
|  10006 |  87692 |
|  10005 |  93392 |
|  10004 |  94692 |
|  10003 |  49881 |
|  10002 |  72527 |
|  10001 |  72567 |
+--------+--------+
8 rows in set (0.00 sec)

思路：薪水情况要查salaries表，以及employees表，使用left join关键词为两表关键词为emp_no，然后限制条件为from_date = hire_date雇佣日期等于开始日期（入职时间），然后使用逆序（emp_no）

第七题：查找薪水变动超过2次的员工号emp_no以及其对应的变动次数t

p_no,count(*) t from salaries a 
inner join salaries b p_no&#p_no 
_date=b.from_date 
where a.salary < b.salary 
group p_no having t >= 2;
+--------+---+
| emp_no | t |
+--------+---+
|  10001 | 3 |
|  10002 | 2 |
|  10003 | 2 |
|  10005 | 2 |
+--------+---+
4 rows in set (0.00 sec)

思路：count(*) 为计算全部数据的行数地意思，比较关键的一个点就是联结条件a.to_date = b.from_date，这个条件限定了两个工资之比必须是相邻的，如果没有这个条件，那同一个emp_no下的任意两个salary都可以做对比，可以把这个条件去掉，对比两个查询结果，就明白了。

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

 select salary from salaries 
where to_date='9999-01-01' 
group by salary order by salary DESC;
+--------+
| salary |
+--------+
|  99901 |
|  99011 |
|  98012 |
|  94692 |
|  94382 |
|  92901 |
|  87692 |
|  87301 |
|  58901 |
+--------+
9 rows in set (0.00 sec)

9 获取当前部门所有manager的薪水情况

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

SELECT d.dept_p_no,s.salary FROM dept_manager AS d 
INNER JOIN salaries AS s p_no&#p_no 
_date='9999-01-01' _date='9999-01-01' 
ORDER p_no;
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001    |  10001 |  99901 |
| d002    |  10002 |  92901 |
| d003    |  10003 |  58901 |
| d004    |  10004 |  94692 |
| d004    |  10004 |  98012 |
| d005    |  10005 |  99011 |
| d006    |  10006 |  87692 |
| d007    |  10007 |  94382 |
| d008    |  10008 |  87301 |
+---------+--------+--------+
9 rows in set (0.00 sec)

解释：按照员工工号排序，使用内联inner join连接两张表

10 获取所有非manager的员工emp_no

题目描述
获取所有非manager的员工emp_no

SELECT emp_no FROM employees 
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);
+--------+
| emp_no |
+--------+
|  10009 |
|  10010 |
+--------+
2 rows in set (0.01 sec)

解释：查询employees里面的员工编号不再dept_manager里的 使用NOT IN 字段