SQL

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

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

max()

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

select * from employees
where hire_date=(select hire_date from employees order by hire_date desc limit 2,1)

limit m,n 从第m+1条，显示n个数据；limit 0，1对应第一条

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

select e.emp_no,s.salary 
from employees as e 
inner join salaries as s 
on e.emp_no=s.emp_no and e.hire_date=s.from_date #注意此处date条件
order by e.emp_no desc;

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

count() as ;group by having

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

找出所有员工当前薪水salary情况,相同薪水显示一次

select distinct salary
from salaries where to_date="9999-01-01" order by salary desc;

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

主键和默认值
列表 类型 是否为NULL 含义
actor_id smallint(5) not null 主键id
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏
last_update timestamp not null 最后更新时间，默认是系统的当前时间

create table actor(
actor_id smallint(5) not null primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update timestamp not null default (datetime('now','localtime'))
);

批量插入数据

INSERT INTO actor
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33')

创建一个actor_name表

创建表插入查询结果
创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。

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

获取所有非manager的员工emp_no

方法一：子查询

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

方法二：左连接
非manager的emp_no is null

select employees.emp_no from employees
left join dept_manager on employees.emp_no=dept_manager.emp_no
where dept_manager.emp_no is null;

获取所有员工当前的manager

获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。
1,当前的manager,所以最后的to_date要使用manager表中的字段
2,如果manager是自己，那么不显示。是自己也就是员工编号emp_no和经理编号emp_no是一样的
3,关联条件应该是同一个部门，这样才有上下级关系。也就是 on de.dept_no = dm.dept_no

select de.emp_no, dm.emp_no as manager_no 
from dept_emp as de inner join dept_manager as dm
on de.dept_no = dm.dept_no 
where dm.to_date = '9999-01-01' and de.to_date = '9999-01-01' and de.emp_no <> dm.emp_no

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

1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；
2、选取每个员工当前的工资水平，用d.to_date = ‘9999-01-01’ AND s.to_date = '9999-01-01’作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；
3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；
4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no

从titles表获取按照title进行分组

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
count(),having的使用

select title ,count(*)as t from titles group by title having t >=2;

*从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。

去重distinct emp_no

select title, count(distinct emp_no) as t from titles
group by title having t >= 2;

统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

平均 avg(s.salary);
各个titile类型 group by

select t.title,avg(s.salary) as avg
from salaries as s inner join titles as t
on s.emp_no = t.emp_no
and s.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
group by  t.title

获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

薪水第二多可能不止一个，所以salary用子查询
第二多：降序排序显示第二个 order by salary desc limit 1,1

select emp_no, salary from salaries
where to_date = '9999-01-01' and salary = (select distinct salary from salaries order by salary desc limit 1,1)

查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

运用子查询和max（）

select e.emp_no, max(s.salary) as salary, e.last_name, e.first_name
from employees as e inner join salaries as s on e.emp_no=s.emp_no
where s.to_date='9999-01-01'and s.salary not in
(select max (salary) from salaries )

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

使用left join

select e.last_name, e.first_name, dp.dept_name from employees as e
left join dept_emp as d on e.emp_no = d.emp_no
left join departments as dp ON d.dept_no = dp.dept_no;

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

子查询 找到最后一次-第一次

select((select salary from salaries where emp_no="10001" order by to_date desc limit 1)-
      (select salary from salaries  where emp_no="10001" order by to_date  limit 1)) as growth;

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

连接两次salaries，第一次获得入职时的薪水，第二次获得当前薪水，然后两次连接表的薪水相减得到growth

select e.emp_no as emp_no, (cur.salary - st.salary) as growth
from employees as e
inner join salaries as st on e.emp_no = st.emp_no and e.hire_date = st.from_date
inner join salaries as cur on e.emp_no = cur.emp_no and cur.to_date = '9999-01-01'
order by growth;

统计各个部门对应员工的工资记录总数

select dee.dept_no,dep.dept_name,count(s.salary) as sum
from dept_emp as dee 
inner join salaries as s on dee.emp_no=s.emp_no
inner join departments as dep on dee.dept_no=dep.dept_no group by dee.dept_no;

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

思路：重复使用salaries表，排名时通过计算count(b.salary>=a.salary)但相同薪水是同一个排名，因此，distinct；不同薪水需显示所以以emp_no来分组，然后逐级排序

select a.emp_no,a.salary, count(distinct b.salary) as rank
from salaries as a,salaries as b
where a.to_date='9999-01-01'and b.to_date='9999-01-01'
and b.salary >=a.salary
group by a.emp_no 
order by a.salary desc ,a.emp_no asc