/*
    查询部门和雇员的多表查询
*/

#准备数据

# 创建部门表dept  部门表中包含 部门id 部门名称  
CREATE TABLE dept(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'); 

# 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
) ; 

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小松松','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('鱼小鱼','女',3600,'2015-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小霈霈','男',8000,'2013-12-02',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('亮仔','男',5000,'2017-11-11',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('坤仔','男',8000,'2012-02-02',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('福姐','女',6500,'2011-09-12',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('熊姐','女',10500,'2018-12-02',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猛哥','男',9500,'2016-07-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('栋栋','男',8500,'2018-06-28',2); 

-- 1.查询员工和部门的名字
SELECT
    t1.`NAME`,t2.`NAME`
FROM
    emp t1,dept t2
WHERE
    t1.`dept_id`=t2.`id`;
    
-- 2.查询鱼小鱼的信息，显示员工id，姓名，性别，工资和所在的部门名称(使用显式内连接)
SELECT
    t1.`id`,t1.`NAME`,t1.`gender`,t1.`salary`,t2.`NAME`
FROM dept t2
JOIN emp t1
ON t1.`dept_id`=t2.`id` AND t1.`NAME`='鱼小鱼';

-- 3.将上面查到的内容 表头使用别名的形式展示 比如显示id为员工id  name为姓名 等
SELECT
    t1.`id` 员工id,t1.`NAME` 姓名,t1.`gender` 性别,t1.`salary` 工资,t2.`NAME` 部门
FROM dept t2
JOIN emp t1
ON t1.`dept_id`=t2.`id` AND t1.`NAME`='鱼小鱼';

-- 4.在部门表中增加一个销售部 
INSERT INTO dept (NAME) VALUES ('销售部');

-- 5.查询所有的部门信息关联查询出该部门中的所有员工信息 
SELECT *
FROM dept t2 LEFT JOIN emp t1
ON t1.`dept_id`=t2.`id`;

-- 6.查询所有的部门信息关联查询出该部门中的所有员工的名字  部门 以及 工资 
SELECT 
    t1.`NAME`,t2.`NAME`,t1.`salary`
FROM dept t2 LEFT JOIN emp t1
ON t1.`dept_id`=t2.`id`;

-- 7.统计出 每个部门的员工人数   查询显示 部门名称 人数 
SELECT 
    t2.`NAME`,COUNT(t1.`id`)
FROM 
    dept t2 LEFT JOIN emp t1
ON
    t1.`dept_id`=t2.`id` 
GROUP BY
    t2.`NAME`;
    
-- 8.统计出 每个部门员工 平均薪资 按照 薪资排序 查询显示 部门名称 平均薪资 
SELECT 
    t2.`NAME`,AVG(t1.`salary`)
FROM 
    dept t2 LEFT JOIN emp t1
ON
    t1.`dept_id`=t2.`id`
GROUP BY
    t2.`NAME`     
ORDER BY salary;

-- 9.统计出，每个部门的平均薪资 按照薪资排序 并且筛选出平均薪资>7000的部门
SELECT 
    t2.`NAME`,AVG(t1.`salary`) a
FROM 
    dept t2 LEFT JOIN emp t1
ON
    t1.`dept_id`=t2.`id`
GROUP BY
    t2.`NAME`
HAVING
    a>7000
ORDER BY salary;

-- 10.查询最高工资是多少
SELECT 
    MAX(salary)
FROM emp;

-- 11.根据最高工资到员工表查询到对应的员工信息
SELECT *
FROM emp 
WHERE salary = (SELECT MAX(salary)FROM emp);

-- 12.查询工资小于平均工资的员工有哪些
SELECT *
FROM emp 
WHERE salary < (SELECT AVG(salary)FROM emp);

-- 13.查询工资大于5000的员工，来自于哪些部门的名字
SELECT 
    t1.`NAME`,t2.`NAME`
FROM 
    dept t2, emp t1
WHERE
     t1.`dept_id`=t2.`id` AND salary >5000;

-- 14.查询开发部与财务部所有的员工信息
SELECT *
FROM emp
WHERE    
    dept_id IN (SELECT id FROM dept WHERE NAME IN ('开发部','财务部'));

-- 15.查询出2011年以后入职的员工信息，包括部门名称
SELECT *
FROM
    emp t1,dept t2
WHERE
    t1.`dept_id`=t2.`id` AND t1.`join_date`>'2011-01-01';