Mysql子查询 , 内连接

-- 题目准备表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;


-- 部门表
CREATE TABLE dept
(
    id    INT PRIMARY KEY PRIMARY KEY, -- 部门id
    dname VARCHAR(50),                 -- 部门名称
    loc   VARCHAR(50)                  -- 部门所在地
);


-- 职务表，职务名称，职务描述
CREATE TABLE job
(
    id          INT PRIMARY KEY,
    jname       VARCHAR(20),
    description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp
(
    id       INT PRIMARY KEY, -- 员工id
    ename    VARCHAR(50),     -- 员工姓名
    job_id   INT,             -- 职务id
    mgr      INT,             -- 上级领导
    joindate DATE,            -- 入职日期
    salary   DECIMAL(7, 2),   -- 工资
    bonus    DECIMAL(7, 2),   -- 奖金
    dept_id  INT,             -- 所在部门编号
    CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
    CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade
(
    grade    INT PRIMARY KEY, -- 级别
    losalary INT,             -- 最低工资
    hisalary INT              -- 最高工资
);

-- 添加4个部门
INSERT INTO dept(id, dname, loc)
VALUES (10, '教研部', '北京'),
       (20, '学工部', '上海'),
       (30, '销售部', '广州'),
       (40, '财务部', '深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description)
VALUES (1, '董事长', '管理整个公司，接单'),
       (2, '经理', '管理部门员工'),
       (3, '销售员', '向客人推销产品'),
       (4, '文员', '使用办公软件');


-- 添加员工
INSERT INTO emp(id, ename, job_id, mgr, joindate, salary, bonus, dept_id)
VALUES (1001, '孙悟空', 4, 1004, '2000-12-17', '8000.00', NULL, 20),
       (1002, '卢俊义', 3, 1006, '2001-02-20', '16000.00', '3000.00', 30),
       (1003, '林冲', 3, 1006, '2001-02-22', '12500.00', '5000.00', 30),
       (1004, '唐僧', 2, 1009, '2001-04-02', '29750.00', NULL, 20),
       (1005, '李逵', 4, 1006, '2001-09-28', '12500.00', '14000.00', 30),
       (1006, '宋江', 2, 1009, '2001-05-01', '28500.00', NULL, 30),
       (1007, '刘备', 2, 1009, '2001-09-01', '24500.00', NULL, 10),
       (1008, '猪八戒', 4, 1004, '2007-04-19', '30000.00', NULL, 20),
       (1009, '罗贯中', 1, NULL, '2001-11-17', '50000.00', NULL, 10),
       (1010, '吴用', 3, 1006, '2001-09-08', '15000.00', '0.00', 30),
       (1011, '沙僧', 4, 1004, '2007-05-23', '11000.00', NULL, 20),
       (1012, '李逵', 4, 1006, '2001-12-03', '9500.00', NULL, 30),
       (1013, '小白龙', 4, 1004, '2001-12-03', '30000.00', NULL, 20),
       (1014, '关羽', 4, 1007, '2002-01-23', '13000.00', NULL, 10);


-- 添加5个工资等级
INSERT INTO salarygrade(grade, losalary, hisalary)
VALUES (1, 7000, 12000),
       (2, 12010, 14000),
       (3, 14010, 20000),
       (4, 20010, 30000),
       (5, 30010, 99990);


-- 题目
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
 员工编号在emp表中 职务名称职务描述在job表中
 一个工作对应多个员工
 emp中job_id 对应 job表中 id
 */
-- 隐示内连接
select emp.id, emp.ename, emp.salary, job.jname, job.description
from emp,
     job
where emp.job_id = job.id;
-- 显示内连接
select emp.id, emp.ename, emp.salary, job.jname, job.description
from emp
         inner join job on emp.job_id = job.id;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
-- 隐示内连接
select e.id, e.ename, e.salary, j.jname, j.description, d.dname, d.loc
from emp e,
     job j,
     dept d
where e.job_id = j.id
  and e.dept_id = d.id;

-- 显示内连接
select e.id, e.ename, e.salary, j.jname, j.description, d.dname, d.loc
from emp e
         inner join
     job j
         inner join
     dept d
     on e.job_id = j.id
         and e.dept_id = d.id;


-- 3.查询员工姓名，工资，工资等级
-- 隐示内连接
select emp.ename, emp.salary, sa.grade
from emp,
     salarygrade sa
where emp.salary between sa.losalary and sa.hisalary;
-- 显示内连接
select emp.ename, emp.salary, sa.grade
from emp
         inner join salarygrade sa
where emp.salary between sa.losalary and sa.hisalary;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
select emp.ename, emp.salary, job.jname, job.description, salarygrade.grade
from emp,
     job,
     salarygrade
where emp.job_id = job.id
   and emp.salary between losalary and hisalary;
-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
  部门编号 , 名称 位置 属于 dept 表中
  部门人数需要统计

 */
select dept.id, dept.dname, dept.loc, count()
from emp,
     dept
where emp.dept_id = dept.id;

-- 分组查询部门人数
select dept_id , count(*) from emp group by dept_id;
select * from  dept;

-- 使用子查询进行内连接
select dept.id, dept.dname, dept.loc, d1.count
from dept,
     (select dept_id, count(*) count from emp group by dept_id) d1
where d1.dept_id = dept.id;
)