多表查询-练习

【多表查询练习】

– 部门表

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

– 添加4个部门

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

– 职务表，职务名称，职务描述

CREATE TABLE job (
  id INT PRIMARY KEY,-- 主键
  jname VARCHAR(20),
  description VARCHAR(50)
);

– 添加4个职务

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

– 员工表

CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id（主键）
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资 decimal:压缩严格的定点数
  bonus DECIMAL(7,2), -- 奖金  decimal:压缩严格的定点数
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), -- 外键约束:外键对应主表job的主键
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)  -- 外键约束:外键对应主表dept的主键
);

– 添加员工

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);

– 工资等级表

CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别（主键）
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

– 添加5个工资等级

INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

– ===============================
查询语句：

 SELECT *FROM dept;
 SELECT *FROM emp;
 SELECT *FROM job;
 SELECT *FROM salarygrade;

– 需求：

– 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
分析：1. 查询员工编号，员工姓名，工资 ==还是员工信息emp表里的，查询emp表即可！
职务名称，职务描述 ==是职务job表里的，查询job表即可！
2. 考虑如何查询？查询的条件是什么？
看新架构设计器：发现
emp表里的job_id外键————reference(引用/关联着/参考)————job表里的id主键
即查询条件：emp.job_id = job.id
*/

SELECT
     t1.`id`,    
     t1.`ename`, 
     t1.`salary`, 
     t2.`jname`,   
     t2.`description` 
FROM
      emp t1, job t2
WHERE
      t1.`job_id`=t2.`id`;

– 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
分析：首先得想清楚，这是一次就查询的结果，所以要把三张表关联起来（也就是所谓的主键-外键）
1.员工编号，员工姓名，工资 —表emp
职务名称，职务描述 —表job
部门名称，部门位置 —表dept
2.查询条件：
员工表的俩个外键 关联 职务表的主键 and 部门表的主键
*/

SELECT
    t1.`id`,
    t1.`ename`,
    t1.`salary`,
    t2.`jname`,
    t2.`description`,
    t3.`dname`,
    t3.`loc`
FROM
    emp t1,
    job t2,
    dept t3
WHERE
   t1.`job_id`=t2.`id`AND t1.`dept_id`=t3.`id`;

– 3.查询员工姓名，工资，工资等级
/*
分析：1.员工姓名，工资—表emp
工资等级 —表salarygrade
2.查询条件：
t1.salaryBETWEEN t2.losalary AND t2.hisalary ;

*/

SELECT
    t1.`ename`,t1.`salary`,t2.`grade` 
FROM
    emp t1,salarygrade t2
WHERE
    t1.`salary`BETWEEN t2.`losalary` AND t2.`hisalary`;

– 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
分析：
1.员工姓名，工资 ----表emp
职务名称，职务描述 ----表job
部门名称，部门位置 ----表dept
工资等级 ----表salargrade
2.查询条件：
t1.job_id=t2.idAND t1.dept_id=t3.id AND t1.salaryBETWEEN t2.losalary AND t2.hisalary ;
*/

SELECT
     t1.`ename`,
     t2.`jname`,
     t2.`description`,
     t3.`dname`,
     t3.`loc`,
     t4.`grade`
FROM
     emp t1, job t2,dept t3, salarygrade t4
WHERE
     t1.`job_id`=t2.`id`
     AND t1.`dept_id`= t3.`id`
     AND t1.`salary` BETWEEN t4.`losalary`AND t4.`hisalary`;

– 5.查询出部门编号、部门名称、部门位置、部门人数
/*
分析：SELECT *FROM dept;—发现部门人数不在表里
1.部门编号、部门名称、部门位置—【需要表dept】
部门人数，如何解决呢？
分析：在员工信息emp表中有dept-id外键——关联——部门dept表id—【需要表emp】
那么计算每个人部门的人数，只需计算员工信息表的dept-id外键分别有几个一样的
计算每个部门就要采取分组查询，按照员工信息表的dept_id分组，并count（id）统计个数
*如何将俩张表联立起来一起查询出来？
需要通过子查询

2.查询条件：
   用部门表的id与员工表的dept_id联立起来 

*/

SELECT
     t1.`id`,
     t1.`dname`,
     t1.`loc`,
     t2.total
     
FROM
       dept t1, 
       (SELECT  -- 根据emp表的dept_id外键分组，然后根据主键id统计人数  
         dept_id, COUNT(id) total
        FROM 
          emp 
       GROUP BY dept_id) t2 -- dept_id后不能添加分号 -- t2作为虚拟表
                            
WHERE  
     t1.`id`=t2.dept_id;

– 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*
分析：1.查询所有员工的姓名–emp表
及其直接上级的姓名–emp表
自己关联自己
2.查询条件：
在emp表里的主键id 找对应的管理者mgr
emp.id=emp.mgr

*/
/*查询所有员工的姓名及其直接上级的姓名
select
t1.id,
t1.ename,
t2.mgr,
t2.ename
from
emp t1,
emp t2

where
t1.id=t2.mgr;

*/
– 现在还要将 没有领导的员工也要查询 出来
– 分析：
– 刚才只是把emp表里的交集部分查询出来啦
– 所以先通过查询没有领导的员工这个表的所有数据====也就是把这些人的id、姓名信息查询出来
– 然后在查询交集的数据
– 【右外连接查询】

 SELECT
     
     t2.`id`,
     t2.`ename`,
     t2.`mgr`,
     t1.`ename` ,
     t1.`id`
 FROM
     emp t1
     RIGHT JOIN emp t2 -- 右外连接查询 获取 右边外的所有信息
 ON
     t1.`id`=t2.`mgr`;

– ================================
;