SQL积累

SQL看似简单其实也包含了相当多的内容

慢慢积累吧，最近状态不咋好，一点点来

--查找最晚入职员工的所有信息
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 * from employees
order by hire_date desc
limit 0,1
--但是这个答案有个问题，当一天由多个同事入职的时候会出现歧义
--所以用下面的方法是绝对正确的
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`));


--LIMIT m,n : 表示从第m+1条开始，取n条数据；
--LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。
--考察点是limit的用法
select * from employees
order by hire_date desc
limit 2,1

--与此同时还有一种想法觉得入职日期，只要是同一天的也就不分前后，也就是说，题目转换为了倒数三天前入职的所有同事。
select * 
from employees 
where hire_date = 
    (select distinct hire_date 
     from employees 
     order by hire_date DESC 
     limit 2,1)
--同时考虑到distinct效率问题还可以改用group by
--经过测试，这种写法确实比上面的效率要高一点，同时应该要注意到这个应该和数据量也有关系
select * 
from employees
where hire_date = 
	(select hire_date
    from employees
    group by hire_date
    order by hire_date desc
    limit 2,1)

--查找各个部门当前(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
--答案一：先在两个表里用where过滤出现任的人选，然后用相等简单相等关联即可。
select 
    s.*, dm.dept_no
from
    salaries as s,
    dept_manager as dm
where
    s.to_date = '9999-01-01'
and
    dm.to_date = '9999-01-01'
and
    s.emp_no = dm.emp_no;
--答案二：
select 
    s.* , d.dept_no
from
    salaries as s
join
    dept_manager as d
on
    s.emp_no = d.emp_no
where
    s.to_date = '9999-01-01'
and
    d.to_date = '9999-01-01'
--此题比较坑，限制了两个to_date，是因为薪水可能会变，人员也可能会变。
然后两个表的前后位置不能动，否则和输出不符，姑且理解为必须小表join大表吧。

--查找所有已经分配部门的员工的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`));
--我首先考虑的是没有使用join的情况
select 
    e.last_name, e.first_name, d.dept_no
from
    dept_emp as d, employees as e
where
    d.emp_no = e.emp_no
--其实从效率方面考虑，使用join会不会好一点，好像使用自然连接不用on就可以
select 
    e.last_name, e.first_name, d.dept_no
from
    dept_emp as d
natural join
    employees as e

--下面这道还是类似的
--查找所有员工的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 
    e.last_name, e.first_name, d.dept_no
from
    employees as e
left join
    dept_emp as d
on
    e.emp_no = d.emp_no
--简单的left join

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