mysql多表查询案例

4个数据表，如下图

-- 1,查询所有员工信息。查询员工编号,员工姓名,工资,职务

-- 隐式连接查询
SELECT t1.id as 员工编号,t1.ename,t1.salary,t2.jname from emp t1,job t2 WHERE t1.job_id = t2.id;
-- 显式连接查询
SELECT t1.id as 员工编号,t1.ename,t1.salary,t2.jname from emp t1 INNER JOIN job t2 ON t1.job_id = t2.id;


-- 2,查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
-- 隐式连接查询
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.daname,t3.loc from emp t1,job t2,dept t3 WHERE t1.job_id = t2.id AND t1.dept_id = t3.id;
-- 显式连接查询
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.daname,t3.loc from emp t1 INNER JOIN job t2 ON t1.job_id = t2.id INNER JOIN dept t3 ON t1.dept_id = t3.id;

SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.daname,t3.loc from emp t1 INNER JOIN job t2 INNER JOIN dept t3 ON t1.job_id = t2.id AND t1.dept_id = t3.id;

-- 3,查询员工姓名,工资,工资等级

-- 隐式连接查询
SELECT emp.ename,emp.salary,salarygrade.grade from emp,salarygrade WHERE emp.salary >= salarygrade.losalaty and emp.salary <= salarygrade.hisalaty;

SELECT emp.ename,emp.salary,salarygrade.grade from emp,salarygrade WHERE emp.salary BETWEEN salarygrade.losalaty and  salarygrade.hisalaty;

-- 显式连接查询
SELECT emp.ename,emp.salary,salarygrade.grade from emp INNER JOIN salarygrade ON emp.salary BETWEEN salarygrade.losalaty and  salarygrade.hisalaty;

-- 4,查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
-- 显式连接查询
SELECT emp.ename,emp.salary,job.jname,job.description,dept.daname,dept.loc,salarygrade.grade FROM emp,job,dept,salarygrade WHERE emp.job_id = job.id AND emp.dept_id = dept.id AND emp.salary >= salarygrade.losalaty AND emp.salary <= salarygrade.hisalaty;

-- 隐式连接查询
SELECT emp.ename,emp.salary,job.jname,job.description,dept.daname,dept.loc,salarygrade.grade FROM emp INNER JOIN job ON emp.job_id = job.id INNER JOIN dept ON emp.dept_id = dept.id INNER JOIN salarygrade ON emp.salary BETWEEN salarygrade.losalaty AND salarygrade.hisalaty;

-- 5·查询出部门编号、部门名称、部门位置、部门人数
/*
分析：
1.查询出部门编号、部门名称、部门位置
2.查询出部门人数
3.使用子查询作为虚拟表得出结果
*/

-- 显式连接查询
SELECT dept.id,dept.daname,dept.loc,t1.count FROM (SELECT  dept_id,COUNT(dept_id)as count FROM emp GROUP BY emp.dept_id) t1, dept WHERE t1.dept_id = dept.id
-- 显式连接查询
SELECT dept.id,dept.daname,dept.loc,t1.count from (SELECT dept_id,COUNT(*)as count from emp GROUP BY emp.dept_id) t1, dept WHERE t1.dept_id = dept.id