随笔- 446 文章- 0 评论- 0 阅读- 42440

多表查询练习详解

（表在上一个博客）

1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

3. 查询员工姓名，工资，工资等级

4.员工的姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

5.查询部门编号、部门名称、部门位置、部门人数

6.查询所有员工的姓名及其直接上级的姓名，没有领导的员工也需要查询

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*

    分析：
        1.员工编号，员工姓名，工资，需要查询emp表 职务名称，职务描述 需要查询job表
        2.查询的条件 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.emp.job_id = job.id AND emp.dept_id = dept.id
*/
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.条件：emp.salay >= salarygrade.losalary and emp.salay <= salarygrade.hisalary
            emp.salay BETWEEN salarygrade.losalary and salarygrade.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，工资等级salarygrade
        2.条件：emp.job_id = job.id AND emp.dept_id = dept.id AND emp.salay BETWEEN salarygrade.losalary and salarygrade.hisalary
*/
SELECT
    t1.ename,
    t1.salary,
    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.查询部门编号、部门名称、部门位置、部门人数
/*
    分析：
        1.部门编号、部门名称、部门位置 dept表，部门人数 emp表
        2.使用分组查询。按照emp.dept_id完成分组，查询count(id)
        3.使用子查询将第2步的查询结果和dept表进行关联查询
*/
SELECT
    t1.id,t1.dname,t1.loc,t2.total
FROM
    dept t1,
    (SELECT
        dept_id,COUNT(id) AS total
    FROM
        emp
    GROUP BY dept_id) t2
WHERE t1.id = t2.dept_id;

-- 6.查询所有员工的姓名及其直接上级的姓名，没有领导的员工也需要查询
/*
    分析：
        1.姓名 emp，直接上级的姓名 emp
                * emp表的id和mgr是自关联
        2.条件 emp.id= emp.mgr
        3.查询左表的所有数据，和交集数据
                * 使用左外连接查询
*/
SELECT
    t1.ename,
    t1.mgr,
    t2.id,
    t2.ename
FROM
    emp t1
LEFT JOIN
    emp t2
ON
    t1.mgr = t2.id