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

1、
order by rand()--随机抽取数据
SELECT * FROM tablename ORDER BY rand() LIMIT 100
随机取前100的数据

2、limit用法:
limit n : 返回前面n个数据
limit i,j :i开始的位置，返回的数量
查询第一条数据
select * from student limit 1;
select * from student limit 0,1
select * from employees order by hire_date desc limit 1 offset 0;
查询第二条数据
select * from student limit 1,1

limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

res:
查询入职最晚的员工所有信息
select * from employees order by hire_date desc limit 1 offset 0;
select* from employees where hire_date = (SELECT MAX(hire_date) FROM employees);
select * from employees order by hire_date desc limit 1;
select * from employees order by hire_date desc limit 0,1;

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

res:
select * from employees order by hire_date desc limit 2,1
select * from employees order by hire_date desc limit 1 offset 2

with as的用法：
with a as (select *,row_number() over(order by hire_date desc ) as rn from employees)
select emp_no,birth_date,first_name,last_name,gender,hire_date from a where rn=3;

经典排序
select emp_no,birth_date,first_name,last_name,gender,hire_date
from (
select *,row_number() over(order by hire_date desc ) as rn from employees
) a
where rn=3

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

res:
select s., d.dept_no from dept_manager d, salaries s
where d.emp_no=s.emp_no # 因为只是主键，必须使用其连接
order by emp_no;
第二种 使用内连接
select s., d.dept_no
from dept_manager d inner join salaries s
on d.emp_no=s.emp_no
order by emp_no;

第三种
select salaries.*,dept_manager.dept_no -- 选择要呈现的项
from salaries left join dept_manager -- 使用左外连接查询(要求输出结果里面dept_no列是最后一列)
on salaries.emp_no=dept_manager.emp_no -- 设置连接方式(员工编号相等)
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01' -- 设置筛选条件
order by salaries.emp_no asc -- 设置排序方式