MySQL多表查询—练习题答案

练习题链接地址在这里

目录

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

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

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

四.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

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

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

---

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

拿到需求之后我们先进行分析：：

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

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

分析：

1.员工编号、姓名、工资属于emp；职务名称、职务描述  属于job；部门名称、部门位置属于dept

2.条件：关联关系是emp.job_id=job.id；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;

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

分析：

1.员工姓名、工资属于emp；工资等级属于salarygrade

2.条件：

emp.`salary` >= salarygrade.`losalary` AND emp.`salary` <= salarygrade.`hisalary`;

emp.`salary` 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`;

四.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

分析：

1.员工姓名、工资属于emp；职务名称、职务描述  属于job；

   部门名称、部门位置属于dept；工资等级属于salarygrade

2.条件：emp.job_id=job.id

AND emp.dept_id=dept.id

AND emp.`salary` 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`;

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

分析：

1.部门编号、部门名称、部门位置属于dept；部门人数需要emp表

2.使用分组查询，按照emp.dept_id完成分组；查询count(id)

3.使用子查询将第2步的查询结果和dept表进行关联查询

第一步:

-- 使用分组查询，按照emp.dept_id完成分组；查询count(id)
SELECT
    dept_id,COUNT(id)
FROM 
    emp
GROUP BY dept_id;

第二步:

将上面的结果和dept表进行关联查询

所以查询语句这样写：

SELECT 
    t1.`dname`,t1.`id`,t1.`loc`,t2.`total`
FROM
    dept t1,
    (SELECT
        dept_id,COUNT(id) total
    FROM 
        emp
    GROUP BY dept_id) t2
WHERE
    t1.id=t2.dept_id;

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

分析：

1.员工姓名属于emp，直接上级的姓名也属于emp;emp表的id和mgr是自关联

2.条件：emp.id=emp.mgr

3.查询左表的所有数据和交集数据（使用左外连接查询）

查询所有员工的姓名及其直接上级的姓名：

SELECT
    t1.`ename`,
    t1.`mgr`,
    t2.`id`,
    t2.`ename`
FROM
    emp t1,emp t2
WHERE
    t1.`mgr`=t2.`id`;

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

SELECT
    t1.`ename`,
    t1.`mgr`,
    t2.`id`,
    t2.`ename`
FROM emp t1
LEFT JOIN emp t2
ON t1.`mgr`=t2.`id`;