MYSQL（多表查询练习） - G-holic/Java-Web GitHub Wiki

多表查询练习（数据库tset1）

use test1;
select * from dept ;
select * from emp ;
select * from job j ;
select * from salarygrade s ;

需求：

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

/**
 * 分析：
 * 		1.员工信息，员工编号，员工姓名，工资，需要查询emp表	职务名称，职务描述 需要查询job表。
 * 		2.查询条件 emp.job-id = job.id
 */
select 
		e.id ,
		e.ename ,
		e.salary ,
		j.jname ,
		j.description 
from 
		emp e ,	job j 
where 
		e.job_id = j.id ;

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

/*
 * 		分析：
 * 			1.员工编号，员工姓名，工资emp。职务名称，职务描述 job。 部门名称，部门位置 dept。
 * 			2.条件： emp.job_id = job.id and emp.dep_id = dep.id 
 */
select
		e.id ,
		e.ename ,
		e.salary ,
		j.jname ,
		j.description ,
		d.dname ,
		d.`local`
from 
		dept d ,emp e  ,job j 
where 
		e.dept_id =d.id and e.job_id = j.id ;

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

/*
 * 分析：
 * 		1.员工姓名，工资 emp。工资等级 salarygrade
 * 		2.条件：emp.salary >= salarygrade.losalary and emp.salary <= salaryhisalary
 * 				emp.salary between salarygrade.losalary and salaryhisalary
 */
select 
		e.ename ,
		e.salary ,
		s.grade 
from 
		emp e ,salarygrade s 
where 
		e.salary between s.losalary and s.hisalary ;

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

/*
 * 分析：
 * 		1.员工姓名，工资 emp。职务名称，职务描述 job。部门名称，部门位置 dept。工资等级 salarygrade。
 * 		2.条件：emp.job_id = job.id and emp.dep_id = dep.id and emp.salary between salarygrade.losalary and salaryhisalary
 */
select 
		e.ename ,
		e.salary ,
		j.jname ,
		j.description ,
		d.dname ,
		d.`local` ,
		s.grade 
from 
		emp e ,job j ,dept d ,salarygrade s 
where 
		e.job_id = j.id and e.dept_id = d.id and e.salary between s.losalary and s.hisalary ;

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

/*
 * 分析：
 * 		1.部门编号，部门名称，部门位置dept。 部门人数 emp。
 * 		2.使用分组查询。按照emp.dept_id完成分组，查询count(id)
 * 		3.使用子查询将第二步的查询结果和dept表进行关联查询
 */
select 
		d.id ,d.dname ,d.`local` ,t1.total
from
		dept d,
	(select 
		dept_id ,count(id)  total
	from 
			emp e 
				group by dept_id ) t1
where 
		d.id =t1.dept_id ;

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

/*
 * 分析：
 * 		1.员工的姓名 emp。 直接上级的姓名emp
 * 					* emp 表的id 和 mgr 是'自关联'(重点)
 * 		2.条件：emp.id = emp.mgr
		 select 
				e.ename ,
				-- e.mgr ,
				-- e2.id ,
				e2.ename 
		from 
				emp e ,emp e2 
		where 
				e.mgr =e2.id ;
 * 		3.查询左表的所有数据，和交集数据
 * 			* 使用左外连接查询
 */

select
		e.ename ,
		e2.ename 
from 
		emp e 		
left join 
		emp e2 
on e.mgr = e2.id ;