-- ======================
-- create表
-- ======================
CREATE TABLE employees (
  employee_id int,
  name varchar(15),
  department_id int
);

CREATE TABLE departments (
  department_id int,
  department_name varchar(15)
);

CREATE TABLE salaries (
  employee_id int,
  salary int
);

-- ======================
-- insert数据
-- ======================
INSERT INTO employees(employee_id, name, department_id)
VALUES (1, 'Alice', 101),
       (2, 'Bob', 102),
       (3, 'Charlie', 103);

INSERT INTO departments(department_id, department_name)
VALUES (101, 'HR'),
       (102, 'IT'),
       (103, 'Finance');

INSERT INTO salaries(employee_id, salary)
VALUES (1, 50000),
       (2, 60000),
       (3, 70000);

-- ======================
-- query查询
-- ======================
-- 1、员工表
SELECT * FROM employees;

-- 2、部门表
SELECT * FROM departments;

-- 3、薪资表
SELECT * FROM salaries;

-- 4、查询所有员工 + 部门名称
SELECT 
     e.employee_id, 
     e.name, 
     d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

-- 5、查询所有员工 + 部门 + 薪资
SELECT 
     e.employee_id,
     e.name,
     d.department_name,
     s.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN salaries s
ON e.employee_id = s.employee_id;

-- 6、子查询：查询所有员工 + 部门 + 薪资，【子查询先把 salaries 表筛选出来当作临时表，再与其它表 INNER JOIN(子查询在 FROM 中，派生表)】
SELECT e.employee_id, 
       e.name, 
       d.department_name,
       s.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN (
    SELECT employee_id, salary
    FROM salaries
) s
ON e.employee_id = s.employee_id;

-- 7、子查询：查询薪资高于平均薪资的员工 + 部门名称 + 薪资
SELECT
    e.employee_id,
    e.name,
    d.department_name,
    s.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN salaries s
ON e.employee_id = s.employee_id
WHERE s.salary > (
    SELECT AVG(salary)
    FROM salaries
);

-- 8、子查询：查询在IT部门工作的员工，【先执行子查询找出 IT 部门的 department_id，再用结果过滤员工(子查询在 WHERE 中)】
SELECT
    employee_id,
    name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);

-- 9、子查询：查询所有员工的工资【每个员工记录都用子查询找对应的薪资(子查询在 SELECT 中，标量子查询)】
SELECT
  employee_id,
  name,
  (SELECT salary FROM salaries WHERE salaries.employee_id = employees.employee_id) AS salary
FROM employees;

-- 10、把employees表里employee_id为3的department_id更新为104
UPDATE employees
SET department_id = 104
WHERE employee_id = 3;

-- 11、再次查看员工表
SELECT * FROM employees;

-- 12、删除employees表里employee_id为3的数据
DELETE FROM employees
WHERE employee_id = 3;

-- 13、再次查看员工表
SELECT * FROM employees;