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

select * from employees 
    where hire_date = (select max(hire_date) from employees);

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

写法一

select * from employees
where hire_date = (
        select distinct hire_date from employees
        order by hire_date desc
        limit 1 offset 2
);
#有可能存在多个并列第一第二，所以要去重再排序 

写法2

select * from employees e1
where 2 = (
    select count(distinct e2.hire_date) 
    from employees e2 
    where e1.hire_date < e2.hire_date
)

查找所有已经分配部门的员工的last_name和first_name以及dept_no

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

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

select emp_no, count(emp_no) as t from salaries group by emp_no having t > 15;

获取所有非manager的员工emp_no

select e.emp_no from employees as e left join dept_manager as d on e.emp_no = d.emp_no where dept_no is null;

部门工资前三高的所有员工

# Write your MySQL query statement below
select 
    d.name as 'Department', e1.Name as 'Employee', e1.Salary
from
    Employee e1
        join
    Department d ON e1.DepartmentId = d.Id
where
    3 > (select
            count(distinct e2.Salary)
        from
            Employee e2
        where
            e2.Salary > e1.Salary
                and e1.DepartmentId = e2.DepartmentId
        );

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

select e.emp_no, e.salary, e.from_date, e.to_date, d.dept_no 
from salaries as e join dept_manager as d on e.emp_no = d.emp_no order by e.emp_no asc

查找所有员工的last_name和first_name以及对应部门编号dept_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

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下:

select e.emp_no as 'emp_no', d.emp_no as 'manager' from dept_emp as e left join dept_manager as d on e.dept_no = d.dept_no where e.emp_no != d.emp_no

获取每个部门中当前员工薪水最高的相关信息

先得到部门中的最高薪t1,再得到组合表t2,再把t1、t2组合来查询，!!!注意这个题不能用having，因为having里面不能包含查询字段!!!

select t1.dept_no, t2.emp_no, t1.salary as 'maxSalary'
from (
    select d.dept_no, max(s.salary) as salary
    from dept_emp as d join salaries as s
    on d.emp_no = s.emp_no group by d.dept_no 
) as t1 join (
    select d.dept_no, d.emp_no, s.salary
    from dept_emp as d left join salaries as s on d.emp_no = s.emp_no
) as t2 on t1.dept_no = t2.dept_no
where t1.dept_no = t2.dept_no and t1.salary = t2.salary
order by t1.dept_no

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select s1.emp_no, s1.salary 
from salaries as s1 
where 1 = (
    select count(distinct s2.salary) 
    from salaries as s2 where s1.salary<s2.salary 
) order by s1.emp_no

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select t1.emp_no, t1.salary, t1.last_name, t1.first_name 
from (
select s.emp_no, s.salary, e.last_name, e.first_name 
from employees as e join salaries as s on e.emp_no = s.emp_no )
as t1 where t1.emp_no = (
    select s1.emp_no from salaries as s1 
            where 1 = (
            select count(distinct s2.salary) from salaries as s2
            where s1.salary < s2.salary)
)

查找所有员工的last_name和first_name以及对应的dept_name

select e.last_name, e.first_name, t1.dept_name
from employees as e left join 
(select d1.emp_no, d1.dept_no, d2.dept_name 
from dept_emp as d1 join departments as d2 on d1.dept_no = d2.dept_no) 
as t1 on e.emp_no = t1.emp_no

查找在职员工自入职以来的薪水涨幅情况

select t2.emp_no, (t2.salary-t1.salary) as growth 
from 
(select e.emp_no, s.salary 
from employees as e join salaries as s 
on e.emp_no = s.emp_no and e.hire_date = s.from_date) as t1
inner join
(select e.emp_no, s.salary 
from employees as e right join salaries as s 
on e.emp_no = s.emp_no and s.to_date ='9999-01-01') as t2
on t1.emp_no = t2.emp_no 
order by growth

统计各个部门的工资记录数

select t2.dept_no, t2.dept_name, count(t2.dept_no) as sum
from
(select t1.dept_no, t1.dept_name
from salaries as s left join
(select e.emp_no, e.dept_no, d.dept_name 
from dept_emp as e left join departments as d
on e.dept_no = d.dept_no) as t1
on s.emp_no = t1.emp_no) as t2 group by dept_no order by dept_no asc

对所有员工的薪水按照salary降序进行1-N的排名

select s1.emp_no, s1.salary, (
    select count(distinct s2.salary) 
    from salaries as s2 where s1.salary<=s2.salary
) as 'rank'
from salaries as s1
order by s1.salary desc, s1.emp_no asc

获取所有非manager员工当前的薪水情况

select t2.dept_no, t2.emp_no, t2.salary
from
(select t1.emp_no, t1.dept_no, s.salary
from 
    (select e.emp_no, d.dept_no
    from employees as e join dept_emp as d 
    on e.emp_no = d.emp_no) as t1 
    left join salaries as s
    on t1.emp_no = s.emp_no) as t2 where t2.emp_no not in
    (
        select dm.emp_no from dept_manager as dm
    )

获取员工其当前的薪水比其manager当前薪水还高的相关信息

select es.emp_no, ms.manager_no, es.salary, ms.manager_salary
from 
(select dm.emp_no as 'manager_no', dm.dept_no, s.salary as 'manager_salary' from dept_manager as dm  left join salaries as s on dm.emp_no = s.emp_no) as ms
join 
(select de.emp_no, de.dept_no, s.salary from dept_emp as de join salaries as s on de.emp_no = s.emp_no) as es
on ms.dept_no = es.dept_no where ms.manager_salary<es.salary

将employees表的所有员工的last_name和first_name拼接起来作为Name

select concat(last_name," ",first_name) as Name from employees

使用join查询方式找出没有分类的电影id以及名称

select f1.film_id, f1.title from film as f1
where f1.film_id not in
(select f.film_id as 'id' from film as f join film_category as fc
on f.film_id = fc.film_id)

创建一个actor表，包含如下列信息

create Table actor(
    actor_id smallint(5) primary key,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update date not null);

批量插入数据

insert into actor(
    actor_id,
    first_name,
    last_name,
    last_update)
VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
      (2,'NICK','WAHLBERG','2006-02-15 12:34:33');

批量插入数据，不使用replace操作

insert ignore actor values(3, 'ED', 'CHASE', '2006-02-15 12:34:33')

创建一个actor_name表

create table actor_name(
    first_name varchar(45) not null,
    last_name varchar(45) not null
)
select first_name, last_name from actor

对first_name创建唯一索引uniq_idx_firstname

create index idx_lastname on actor(last_name);
create unique index uniq_idx_firstname on actor(first_name);

针对actor表创建视图actor_name_view

create view actor_name_view as 
select first_name as first_name_v, last_name as last_name_v from actor

针对上面的salaries表emp_no字段创建索引idx_emp_no

select * from salaries force index(idx_emp_no) where emp_no=10005

在last_update后面新增加一列名字为create_date

alter table actor add (create_date datetime not null default '2020-10-01 00:00:00');

删除emp_no重复的记录，只保留最小的id对应的记录。

delete from titles_test
where id not in
(
    select * from(
        select min(id)
        from titles_test
        group by emp_no 
    ) as a
)

将所有to_date为9999-01-01的全部更新为NULL

update titles_test set to_date = null, from_date = '2001-01-01' where to_date = '9999-01-01'