员工信息管理
单表查询
1. 查询所有员工的姓名和职位，列名改为中文名
SELECT name as "姓名",job as '职位' FROM EMP;
SELECT name "姓名",job '职位' FROM EMP;
2. 查询部门 ID 为10的员工信息
SELECT * FROM EMP WHERE deptid = 10;
3. 查询部门 ID 不为10的员工信息
SELECT * FROM EMP WHERE deptid != 10;
SELECT * FROM EMP WHERE deptid <> 10;
4. 查询所有月薪超过 2000 的员工ID、姓名和薪资
SELECT id,name,salary FROM EMP WHERE salary > 2000;
5. 查询月薪在 1000 到 3000 之间的员工信息
SELECT * FROM EMP WHERE salary >= 1000 and salary <= 3000;
SELECT * FROM EMP WHERE salary between 1000 and 3000;
6. 查询部门 ID 为 10、20 或 30 的员工信息
SELECT * FROM EMP WHERE deptid = 10 or deptid = 20 or deptid = 30;
SELECT * FROM EMP WHERE deptid in (10,20,30);
7. 查询职位是 'Clerk' 且月薪超过 3000 的员工信息
SELECT * FROM EMP WHERE job = 'Clerk' and salary > 3000;
8. 查询部门 ID 为 20 或者30且职位是 'Manager' 的员工信息
SELECT * FROM EMP WHERE deptid in(20,30) and job = 'Manager';
9. 查询姓名包含AM的所有员工信息
SELECT * FROM EMP WHERE name like '%AM%';
10. 查询名称以S开头的所有员工信息
SELECT * FROM EMP WHERE name like 'S%';
11. 查询所有员工信息，按薪资降序排列
SELECT * FROM EMP ORDER BY salary DESC;
12. 查询部门 ID 为 30 的员工信息，按出生日期升序排列
SELECT * FROM EMP WHERE deptid = 30 ORDER BY birthday asc;
13. 查询薪资最高的前 5 名员工信息
SELECT * FROM EMP ORDER BY salary DESC LIMIT 5;
14. 统计每个部门的员工数量
SELECT deptid,COUNT(id) AS emp_num FROM EMP GROUP BY deptid;
15. 计算每个职位的平均薪资
SELECT job,AVG(salary) AS avg_salary FROM EMP GROUP BY job;
16. 查询每个部门的最高和最低薪资
SELECT deptid,MAX(salary) AS max_salary,MIN(salary) AS min_salary FROM EMP GROUP BY deptid;
17. 查询员工数量超过 5 人的部门 ID 和人数
SELECT deptid,COUNT(id) AS emp_num FROM EMP GROUP BY deptid HAVING COUNT(id) > 5;
18. 查询平均薪资高于 2000 的职位名称和平均薪资
SELECT job,AVG(salary) AS avg_salary FROM EMP GROUP BY job HAVING AVG(salary) > 2000;
19. 查询至少有两个经理的部门 ID（job='Manager'）
SELECT deptid,COUNT(id) AS mng_num FROM EMP WHERE job = 'Manager' GROUP BY deptid HAVING COUNT(id) >= 2;
SELECT deptid,SUM(job = 'Manager') AS mng_num FROM EMP GROUP BY deptid HAVING SUM(job = 'Manager') >= 2;
20、查询每个部门下每种职位的员工数量
SELECT deptid,job,COUNT(id) as emp_num FROM EMP GROUP BY deptid,job;
多表查询
1. 查询每个员工的部门名称
select emp.*,dept.name as dept_name from emp join dept on emp.deptid = dept.id;
select emp.*,dept.name as dept_name from emp ,dept where emp.deptid = dept.id;
2. 查询每个员工的薪资等级
select e.*,s.grade from emp as e
join salgrade as s on e.salary between s.losal and s.hisal;
3. 查询薪资等级为 3 的员工以及他所在部门的名称和地址
select e.*,s.grade,d.name as dept_name,d.workaddress
from emp e
join dept d on e.deptid = d.id
join salgrade s on e.salary between s.losal and s.hisal
and s.grade = 3;
4. 查询平均薪资最高的部门名称和对应平均薪资
select dept.name,avg(salary) as avg_salary
from emp join dept on emp.deptid = dept.id
group by dept.name order by avg_salary desc limit 1
5. 查询每个部门薪资等级分布（如：部门 A 有 3 人在等级 2）
select e.deptid,s.grade,count(1) as emp_num from emp as e
join salgrade as s on e.salary between s.losal and s.hisal
group by e.deptid,s.grade;
6. 查询比部门平均薪资高的员工及其部门名称
-- 写法1
select * from emp join
(
select deptid,avg(salary) as avg_salary from emp group by deptid
) dept_avg_salary
on emp.deptid = dept_avg_salary.deptid
and emp.salary > dept_avg_salary.avg_salary;
-- 写法2
with dept_avg_salary as
(
select deptid,avg(salary) as avg_salary from emp group by deptid
)
select * from emp
join dept_avg_salary on emp.deptid = dept_avg_salary.deptid
and emp.salary > dept_avg_salary.avg_salary;
7. 查询每个员工的信息以及他直接上级的姓名（假设 managerid 关联员工表的 id）
select e1.*,e2.name as manager_name from emp e1 left join emp e2 on e1.managerid = e2.id
8. 查询没有员工的部门名称
select dept.name from dept left join emp on dept.id = emp.deptid
where emp.id is NULL;
9. 查询薪资不在任何等级范围内的员工
select * from emp e left join salgrade s on e.salary between s.losal and s.hisal
where s.grade is null;
窗口函数
1、 查询每个部门薪资排名前3的员工信息
SELECT
t1.deptid, t1.name,t1.salary
FROM
emp t1
LEFT JOIN
emp t2
ON t1.deptid = t2.deptid
AND t1.salary < t2.salary
GROUP BY
t1.deptid, t1.name,t1.salary
HAVING
COUNT(t2.salary) < 3 -- 比当前记录大的记录数 < 3，即当前是前3名
ORDER BY
t1.deptid, t1.salary DESC;
select * from
(
select *,
row_number() over(partition by deptid order by salary desc) as rank0
from emp
) t where t.rank0 <= 3
2、 查询每个部门薪资排名前10%的员工信息
with emp_info as
(
select *,
PERCENT_RANK() over(partition by deptid order by salary desc) * 100 as salary_percent_rank
from emp
)
select * from emp_info where salary_percent_rank <= 10;
3、查询工资比部门平均工资高的员工信息
select * from
(
select *,
avg(salary) over(PARTITION by deptid) as avg_salary
from emp
) t where t.avg_salary < t.salary
4、查询每个职位中，每个员工的薪资与同职位最高薪资的差值
SELECT
id,
name,
job,
salary,
MAX(salary) OVER (PARTITION BY job) as max_salary,
MAX(salary) OVER (PARTITION BY job) - salary as salary_ratio
FROM emp;
5、查询部门中每个员工与部门平均工资的差值
SELECT
id,
name,
deptid,
salary,
AVG(salary) OVER (PARTITION BY deptid) AS avg_salary,
salary - AVG(salary) OVER (PARTITION BY deptid) AS salary_diff
FROM emp;
客户商品订单管理
1、查询所有客户已完成订单信息以及对应商品信息
2、查询收货地址在广东的客户信息
3、查询购买过两种商品以上的客户+订单+商品信息
4、查询购买总价大于1000的客户+订单+商品信息
5、查询购买商品数量最多的客户信息
6、查询购买商品价格最多的客户信息
7、查询从未被购买过的商品信息
8、查询被买过且降价了的商品信息
9、查询被买过且涨价了的商品信息
10、查询被买过最多的商品
11、查询客户复购过的所有商品和客户信息
12、计算每个客户登陆时距离上次登陆的间隔时间