黑马程序员技术交流社区

标题: MySQL多表查询几种不同示例 [打印本页]

作者: DinoChan 时间: 2019-9-28 20:00
标题: MySQL多表查询几种不同示例
多表查询的概述: 同时查询多张表获取到需要的数据
例如:我们想查询到开发部有多少人，需要将部门表和员工表同时进行查询
多表查询的分类:
内连接查询
显示内连接
隐式内连接
外连接查询
左外连接
右外连接
子查询
子查询的结果是一个值的
子查询的结果是单列多行的
子查询的结果是多列多行的
内连接的概述:用左边表的记录去匹配右边表的记录，如果符合条件的则显示
隐式内连接: 看不到JOIN关键字，条件使用WHERE指定
语法: select 查询的字段 from 左表,右表 where 条件；
多表查询步骤的结论:
1.确定需要查那几张表
2.确定关联条件(表的关联条件,其他条件)
3.确定需要查询的字段
4.确定是内连接,外连接,子查询
子查询的注意事项：子查询结果只要是多列，肯定在FROM后面作为表
SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;子查询作为表需要取别名，否则这张表没用名称无法访问表中的字段。
-- 练习1:查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述 2张表
-- 1.确定需要查那几张表: emp e,job j
-- 2.确定关联条件: e.job_id=j.id
-- 3.确定需要查询的字段: e.id,e.ename,e.salary,j.jname,j.description
-- 4.确定内连接
SELECT e.id,e.ename,e.salary,j.jname,j.description FROM emp e,job j WHERE e.job_id=j.id

-- 练习2:查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置  3张表
-- 1.确定需要查那几张表: emp e,job j,dept d
-- 2.确定关联条件: e.job_id=j.id and e.dept_id=d.id
-- 3.确定需要查询的字段: e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc
-- 4.确定内连接
SELECT e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc FROM emp e,job j,dept d WHERE e.job_id=j.id AND e.dept_id=d.id

-- 练习3:查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级  4张表
-- 1.确定需要查那几张表: emp e,job j,dept d,salarygrade s
-- 2.确定关联条件: e.job_id=j.id and e.dept_id=d.id and e.salary between s.losalary and s.hisalary
-- 3.确定需要查询的字段: e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade
-- 4.确定内连接
SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,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

-- 练习4:查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
-- 1.确定需要查那几张表: emp e,job j,dept d,salarygrade s
-- 2.确定关联条件: e.job_id=j.id and e.dept_id=d.id and e.salary between s.losalary and s.hisalary and j.jname='经理'
-- 3.确定需要查询的字段: e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade
-- 4.确定内连接
SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,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 AND j.jname='经理'

-- 练习5:查询出部门编号、部门名称、部门位置、部门人数
-- 子查询:查询部门人数
SELECT dept_id,COUNT(*) total FROM emp GROUP BY dept_id
-- 练习8: 列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示
-- 1.确定需要查那几张表:emp e1,emp e2
-- 2.确定关联条件:e1.mgr = e2.id
-- 3.确定需要查询的字段:e1.ename,e2.ename
-- 4.确定左外连接
SELECT e1.ename,e2.ename FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr = e2.id

-- 练习9:查询入职期早于直接上级的所有员工编号、姓名、部门名称
-- 1.确定需要查那几张表:emp e1,emp e2,dept d
-- 2.确定关联条件:e1.mgr = e2.id and e1.dept_id=d.id and e1.joindate < e2.joindate
-- 3.确定需要查询的字段:e1.id,e1.ename,d.dname
-- 4.确定内连接
SELECT e1.id,e1.ename,d.dname FROM emp e1,emp e2,dept d WHERE e1.mgr = e2.id AND e1.dept_id=d.id AND e1.joindate < e2.joindate

-- 练习10:查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级
-- 查询公司的平均工资
SELECT AVG(salary) FROM emp  -- 20732.1

-- 1.确定需要查那几张表:emp e1,emp e2,dept d,salarygrade s
-- 2.确定关联条件:e1.mgr = e2.id and e1.dept_id=d.id and e1.salary between s.losalary and s.hisalary and e1.salary > (SELECT AVG(salary) FROM emp )
-- 3.确定需要查询的字段:e1.*,d.dname,e2.ename,s.grade
-- 4.确定内连接
SELECT e1.*,d.dname,e2.ename,s.grade FROM emp e1,emp e2 , dept d , salarygrade s
WHERE  e1.mgr = e2.id AND e1.dept_id=d.id AND e1.salary BETWEEN s.losalary AND s.hisalary AND e1.salary > (SELECT AVG(salary) FROM emp )

SELECT * FROM emp ORDER BY salary DESC

SELECT e1.*,d.dname,e2.ename,s.grade FROM emp e1 LEFT JOIN emp e2 ON e1.mgr = e2.id
LEFT JOIN dept d ON e1.dept_id=d.id
LEFT JOIN salarygrade s ON e1.salary BETWEEN s.losalary AND s.hisalary
WHERE e1.salary > (SELECT AVG(salary) FROM emp )

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2