要求
代码
# 准备数据
CREATE DATABASE IF NOT EXISTS company;USE company;CREATE TABLE IF NOT EXISTS employees(employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT
);DESC employees;CREATE TABLE IF NOT EXISTS departments(department_id INT PRIMARY KEY,department_name VARCHAR(50)
);DESC departments;CREATE TABLE IF NOT EXISTS salaries (employee_id INT PRIMARY KEY,salary DECIMAL(10, 2)
);DESC salaries;CREATE TABLE IF NOT EXISTS managers (manager_id INT PRIMARY KEY,department_id INT
);DESC managers;-- 插入员工信息
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES(1, 'John', 'Doe', 1),(2, 'Jane', 'Smith', 2),(3, 'Michael', 'Johnson', 1),(4, '图图', 'Brown', NULL),(5, 'David', 'Williams', 3),(6, 'Sarah', 'Jones', 1);-- 插入部门信息
INSERT INTO departments (department_id, department_name)
VALUES(1, 'HR'),(2, 'Finance'),(3, 'IT');-- 插入员工薪资信息
INSERT INTO salaries (employee_id, salary)
VALUES(1, 50000),(2, 60000),(3, 55000),(4, 48000),(5, 70000),(6, 52000);-- 插入经理信息
INSERT INTO managers (manager_id, department_id)
VALUES(1, 1),(2, 2),(3, 3);SHOW TABLES;# 实现
#1、找出每个部门的平均工资。 部门的信息 , 平均工资
# 员工表 和 薪资表
# 查询语法: 连表查询 -> 员工表 | 分组查询 -> 分组字段 和 聚合函数
SELECT d.department_name,AVG(s.salary) AS ssav FROM employees eLEFT JOIN salaries s ON e.employee_id =s.employee_idLEFT JOIN departments d ON e.department_id = d.department_idGROUP BY d.department_name;#2、列出每个部门的经理姓名以及他们管理的员工数目。
#2.1 查询每个部门以及部门对应的经理的名称 SELECT * FROM departments dLEFT JOIN managers m ON d.department_id = m.department_idLEFT JOIN employees e01 ON e01.department_id =m.department_id;#2.2 查询每个员工以及员工对应的部门以及部门的名称
SELECT d.department_name, CONCAT(e1.first_name,e1.last_name) AS manager_name , COUNT(1) ct FROM departments d LEFT JOIN managers m ON d.department_id = m.department_idLEFT JOIN employees e1 ON e1.employee_id = m.manager_idLEFT JOIN employees e2 ON e2.department_id = d.department_idGROUP BY manager_name,d.department_name ;#3、列出没有分配到部门的员工。
SELECT * FROM employees WHERE department_id IS NULL; #4、列出每个部门的员工数目以及该部门的总工资。SELECT e.department_id,AVG(s.salary) , COUNT(1) FROM employees e LEFT JOIN salaries s ON e.employee_id = s.employee_idGROUP BY e.department_id;#5、列出每个员工的名字以及他们的薪资等级（低于平均工资的员工为低级别，高于平均工资的员工为高级别）。# id , 姓名 , 薪水等级 -> 比较 平均薪水 [流程语句 case when | 聚合函数单独使用,窗口函数]
SELECT e.employee_id,CONCAT(e.first_name,e.last_name) , s.salary ,CASE WHEN s.salary < AVG(salary) OVER () THEN '低级别'ELSE '高级别'END AS salary_level FROM employees e LEFT JOIN salaries s ON e.employee_id = s.employee_id; #6、找出薪资排名前10的员工。
SELECT * FROM employees e LEFT JOIN salaries s ON e.employee_id = s.employee_idORDER BY s.salary DESC LIMIT 1,1;#7、找出至少有2名员工的部门。
# 至少有2名员工的部门 > 2 分组后的条件
# where 分组前的条件
# on 主 = 外
# having -> group by 分组后的条件
SELECT department_id , COUNT(1) num FROM employees e GROUP BY department_id HAVING num > 2;#8、找出每个部门的平均工资，但排除经理的薪资。
SELECT e.department_id,AVG(s.salary) FROM employees e LEFT JOIN salaries s ON e.employee_id = s.employee_idWHERE e.employee_id NOT IN (SELECT manager_id FROM managers)GROUP BY e.department_id#9、列出每个部门的员工姓名、薪资，以及该部门内工资排名。
# rank() 多行函数->窗口函数使用
SELECT e.first_name,s.salary,e.department_id , RANK() OVER(PARTITION BY e.department_id ORDER BY s.salary DESC)FROM employees e LEFT JOIN salaries s ON e.employee_id = s.employee_id#10、找出每个部门薪资最低的员工。# from where select SELECT * FROM (SELECT e.first_name,s.salary,e.department_id , RANK() OVER(PARTITION BY e.department_id ORDER BY s.salary ASC) numFROM employees e LEFT JOIN salaries s ON e.employee_id = s.employee_id ) temp WHERE temp.num = 1 ;DROP DATABASE IF EXISTS company;
