Mysql-多表查询子查询 案例

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
分析：
1.员工编号，员工姓名，工资 信息在emp表
2.职务名称，职务描述 信息在job表
3.job职务表和emp员工表是一对多的关系 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;
-- 显示内连接
SELECT
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description
FROM
emp t1
INNER JOIN job t2 ON t1.job_id = t2.id;
-- 2. 有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/
分析：
1.员工编号，员工姓名，工资 信息在emp表
2.职务名称，职务描述 信息在job表
3.job职务表和emp员工表是一对多的关系 emp.job_id=job.id
4.部门名称和部门位置来自于部门表dept表
5.dept和emp一对多的关系 dept.id=emp.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 t3.id = t1.dept_id;
-- 显示内连接
SELECT
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description,
t3.dname,
t3.loc
FROM
emp t1
INNER JOIN job t2 ON t1.job_id = t2.id
INNER JOIN dept t3 ON t3.id = t1.dept_id;
--3.查询员工姓名，工资，工资等级 /
分析：
1.员工姓名，工资 信息在emp表
2.工资等级 信息在salarygrade 工资等级表
3.emp.salary>=salarygrade.losalary and emp.salary <= salarygrade.hisalary

*/

SELECT
emp.ename,
emp.salary,
t2.*
FROM
emp,
salarygrade t2
WHERE
emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary;

-- 使用between and 优化
SELECT
emp.ename,
emp.salary,
t2.*
FROM
emp,
salarygrade t2
WHERE
emp.salary
BETWEEN t2.losalary
AND t2.hisalary;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
分析：
1.员工编号，员工姓名，工资 信息在emp表
2.职务名称，职务描述 信息在job表
3.job职务表和emp员工表是一对多的关系 emp.job_id=job.id
4.部门名称和部门位置来自于部门表dept表
5.dept和emp一对多的关系 dept.id=emp.dept_id
6工资等级 信息在salarygrade 工资等级表
7emp.salary>=salarygrade.losalary and emp.salary <= salarygrade.hisalary

*/
-- 显示内连接
SELECT
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description,
t3.dname,
t3.loc,
t4.grade
FROM
emp t1
INNER JOIN job t2 ON t1.job_id = t2.id
INNER JOIN dept t3 ON t3.id = t1.dept_id
INNER JOIN salarygrade t4 oN t1.salary BETWEEN t4.losalary and t4.hisalary
;

-- 查询出部门的编号，部门名称，部门位置，部门人数

/*
分析：
	1.部门的编号，部门名称，部门位置 在dept部门表
	2.部门人数：在emp表中按照对应的dept_id 分组 用count(*)统计数量
	3.使用子查询让部门表和分组后的表进行内连接

*/
select * from dept ;

SELECT dept_id,count(*) from emp GROUP BY dept_id;

SELECT
dept.id,
dept.dname,
dept.loc,
t1.count
FROM
dept,(
SELECT
dept_id,
count(*) count
FROM
emp
GROUP BY
dept_id
) t1
WHERE
dept.id = t1.dept_id;