MySQL 实例练习---day01

One that would the fruit must climb the tree.
要吃果子就得上树。 ——Thomas Fuller

SQL1

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

方法一：使用子查询语句
1、先查询到 hire_date 字段中的最大值，即最晚入职日期
2、将上面的查询结果作为 employees 表的 where 子句条件

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

方法二：使用 order by 降序排序，limit 分页查询
1、使用 order by 子句对 hire_date 字段进行排序。默认是生序排序（ASC），在这里采用降序排序（DESC），将日期按从大到小排序
2、再结合使用 limit 子句取出想要的查询结果集，limit m, n（m 代表从 m+1 行开始取，n 代表取出多少行）

SELECT * FROM employees 
    ORDER BY hire_date DESC 
    LIMIT 1;

对比：当同一天有多个员工同时入职时，方法一可以查询到日期最大的所有记录，但方法二只能查询到 limit 指定显示的数量

SQL2

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

SELECT * FROM employees
    ORDER BY hire_date DESC
    LIMIT 2, 1;

运行时间52ms 占用内存7480KB

#如果同一天有多个员工同时入职时，应该使用 
#distinct 关键字对相同日期进行去重
SELECT * FROM employees
    WHERE hire_date=(
        SELECT DISTINCT hire_date
        FROM employees
        ORDER BY hire_date DESC
        LIMIT 2, 1);

运行时间54ms 占用内存7476KB

SQL3

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

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

SELECT salaries.*, dept_manager.dept_no 
    FROM salaries, dept_manager
    WHERE salaries.emp_no = dept_manager.emp_no
    ORDER BY salaries.emp_no ASC;

SQL4

查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示

/*使用 where 子句进行过滤查询，找到两张表关联的字段
employees.emp_no = dept_emp.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;

SQL5

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

根据题目要求，可以使用多表查询中外连接的左外连接（左侧表查询出的字段全部显示），已分配部门的员工显示对应的部门号，未分配部门的员工部门号则为空

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

SQL7

查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

根据题目要求，分析可得使用分组函数 count() 和分组子句 group by，分组子句将员工号相同的放在一个集合中，count() 只统计非空字段，再用一个having 子句对分组后的结果进行过滤，条件是 COUNT(emp_no) > 15

SELECT emp_no, COUNT(emp_no) AS t
    FROM salaries
    GROUP BY emp_no
    HAVING COUNT(emp_no) > 15;

SQL8

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

distinct 关键字的作用是去重，order by 字段 desc 是降序显示

SELECT DISTINCT salary from salaries
    ORDER BY salary DESC;

SQL10

找出所有非部门领导的员工emp_no

先查询出 dept_manager 表中的所有员工，再从 employees 的表中筛选员工不在 dept_manager 中的记录

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

使用左外连接将两张表利用 emp_no 作为桥梁连接起来，where 子句过滤出没有部门号的员工

SELECT employees.emp_no 
    FROM employees LEFT JOIN dept_manager
    ON employees.emp_no = dept_manager.emp_no
    WHERE dept_manager.dept_no IS NULL;

SQL11

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

分析：在同一个部门里的员工和经理，他们的部门号是相同的；员工有两种可能的身份，不是员工，就是经理，所以 where 子句过滤掉员工本身是经理的情况

SELECT dept_emp.emp_no AS emp_no, dept_manager.emp_no AS manager
    FROM dept_emp LEFT JOIN dept_manager
    ON dept_emp.dept_no = dept_manager.dept_no
    WHERE dept_emp.emp_no  !=  dept_manager.emp_no;

SQL12 （困难）

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列

这道题我刚开始只考虑到每个部门的最高薪水，忽略了员工包含于部门并且一个部门中薪水最高的员工可能不止一个，导致自测运行成功，但是在提交代码时报错，显示测试用例未通过。然后我先自己思考了一会儿，未果。之后我就开始在网上搜查发现了一个博主写的关于这道题的解析，思路清晰，令我豁然开朗。
解题步骤如下：
1、按照部门分组并生序排序（丢失了员工信息），获取每个部门的编号和最高薪水

#按照部门分组并生序排序（丢失了员工信息），获取每个部门的编号和最高薪水
SELECT d.dept_no, MAX(salary) AS max_salary
	FROM dept_emp d LEFT JOIN salaries s
	ON d.emp_no = s.emp_no
	GROUP BY dept_no
	ORDER BY dept_no

2、按照员工分组（没有部门信息），获取每个员工的编号和最高薪水

#按照员工分组（没有部门信息），获取每个员工的编号和最高薪水
SELECT d.emp_no, d.dept_no, s.salary
	FROM dept_emp d LEFT JOIN salaries s
	ON d.emp_no = s.emp_no

3、连接两张表，结合题目要求进行条件限定，取出相关结果集

#连接两张表，结合题目要求进行条件限定，取出相关结果集
SELECT r1.dept_no, r2.emp_no, r2.salary AS maxsalary FROM (
SELECT d.dept_no, MAX(salary) AS max_salary
	FROM dept_emp d LEFT JOIN salaries s
	ON d.emp_no = s.emp_no
	GROUP BY dept_no
	ORDER BY dept_no) AS r1
LEFT JOIN(
SELECT d.emp_no, d.dept_no, s.salary
	FROM dept_emp d LEFT JOIN salaries s
	ON d.emp_no = s.emp_no) AS r2
ON r1.dept_no = r2.dept_no AND r1.max_salary = r2.salary
ORDER BY r1.dept_no

好了，今天的SQL练习就到这里了，接下来我会继续在这里分享我的SQL练习历程，加油，坚持就是胜利！