多表查询练习

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
分析：员工编号，员工姓名，工资 用到emp；职务名称，职务描述 用到了job
emp.job_id=job.id
*/
--隐式内连接
SELECT
emp.id,emp.ename,emp.salary,job.jname,job.description
FROM emp,job
WHERE emp.job_id=job.id;
--显示内连接
SELECT
emp.id,emp.ename,emp.salary,job.jname,job.description
FROM emp JOIN job ON emp.job_id=job.id;
--左外连接
SELECT
emp.id,emp.ename,emp.salary,job.jname,job.description
FROM emp LEFT JOIN job ON emp.job_id=job.id;
--查询的是左表的所有数据和右表的交集部分
--右外连接
SELECT
emp.id,emp.ename,emp.salary,job.jname,job.description
FROM job RIGHT JOIN emp ON job.id=emp.job_id;
--查询的是右表的所有数据和左表的交际部分

 

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
分析：emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc

emp,job,dept
emp.job_id=job.id and emp.dept_id=dept.id

*/
--隐式内连接
SELECT
emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc
FROM emp,job,dept
WHERE emp.job_id=job.id AND emp.dept_id=dept.id;

-- 3.查询员工姓名，工资，工资等级
/*
emp.id,emp.salary,salarygrade
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;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
emp.id,emp.salary,job.jname,job.description,dept.dname,dept.loc,salarygrade
emp.job_id=job.id,emp.dept_id=dept.id
emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/

SELECT a.ename,a.salary,b.jname,b.description,c.dname,c.loc,d.grade
FROM emp a,job b,dept c,salarygrade d
WHERE a.job_id=b.id
AND a.dept_id=c.id
AND a.salary BETWEEN d.losalary AND d.hisalary ;

-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
1.dept.id,dept.dname,dept.loc，部门人数 emp表
2.使用分组查询。按照emp.dept_id 查询count（id）
3.使用子查询将第二步的查询结果和dept表进行关联查询
*/
SELECT
a.id,a.dname,a.loc,b.tatal
FROM
dept a,
(SELECT
dept_id,COUNT(id) tatal
FROM emp
GROUP BY dept_id) b
WHERE a.id=b.dept_id;

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

/*
分析1.姓名 emp ，直接上级的名字 emp emp表的id和mgr是自关联
2.条件emp.mgr=emp.id
3.用左外连接查询到左表的数据和右表的交集
*/
SELECT
a.ename,
a.mgr,
b.id,
b.ename
FROM emp a,emp b
WHERE a.mgr = b.id;

SELECT
a.ename,
a.mgr,
b.id,
b.ename
FROM emp a
LEFT JOIN emp b
ON a.mgr=b.id;