day05多表查询01( 二 ) _生活百科

-- 请统计每个部门group by 的平均avg工资， -- 并且是大于1000的， having-- 并且按照平均工资从高到低排序， order by-- 取出前两行记录 limitSELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 1000 ORDER BY avg_sal DESC LIMIT 0,2

文章插图
2.多表查询2.1笛卡尔积

说明

多表查询是指基于两个或两个以上的表查询，在实际的应用中，查询单个表可能不能满足需求，这时候就要用到多表查询

例子-笛卡尔集（积）

SELECT * FROM emp,dept;显示的结果如下：共有52行记录

文章插图
emp表：共有13行记录

文章插图
dept表：共有4行记录

文章插图
分析如下：
当两张表查询时，规则为

从第一张表中，取出一行和第二张表的每一行进行组合，返回结果[含有两张表的所有列]
一共返回的记录数=第一张表的行数*第二张表的行数
这样多表查询默认处理返回的结果，称为笛卡尔集（积）
解决这个多表的关键就是要写出正确的过滤条件 where
多表查询的条件不能少于表的个数 -1 ，否则会出现笛卡尔积

练习

显示雇员名，雇员工资以及所在部门的名字
如何显示部门号为10的部门名，员工名和工资
显示各个员工的姓名，工资及其工资的级别

-- 1. 显示雇员名，雇员工资以及所在部门的名字SELECT ename,sal,dname,emp.deptnoFROM emp,deptWHERE emp.deptno = dept.deptno;-- 2. 如何显示部门号为10的部门名，员工名和工资SELECT ename,sal,dname,emp.deptnoFROM emp,deptWHERE emp.deptno = dept.deptno AND emp.deptno =10;-- 3. 显示各个员工的姓名，工资及其工资的级别SELECT ename,sal,gradeFROM emp,salgradeWHERE sal BETWEEN losal AND hisal;-- 4.显示雇员名，雇员工资以及所在部门的名字，并按照部门名排序SELECT ename,sal,dnameFROM emp,deptWHERE emp.deptno = dept.deptnoORDER BY dept.dname DESC;

2.2自连接

自连接

自连接是指在同一张表的连接查询

自连接的特点
- 将同一张表看做两张表使用
- 需要给表取别名，格式为表名表别名

思考：显示公司员工和他上级的名字

分析：可以发现员工的名字和上级的名字都是在emp表中
【day05多表查询01】员工和上级是通过emp表的mgr列关联的

文章插图

-- 显示公司员工和他上级的名字SELECT worker.ename AS '职员名', boss.ename AS '上级名'-- 列的别名	FROM emp worker,emp boss -- 为表起别名	WHERE worker.mgr = boss.empno; -- 过滤条件

文章插图
3.子查询

什么是子查询

子查询是指嵌入在其他SQL语句的select语句，也叫嵌套查询

单行子查询

单行子查询是指只返回一行数据的子查询语句

请思考：如何显示与Smith同一部门的所有员工？

多行子查询

多行子查询指返回多行数据的子查询使用关键字 in
3.1多行子查询

练习1

-- 请思考：如何显示与Smith同一部门的所有员工？/*	1.先查询到Smith的部门编号	2.把上面的select语句当做是一个子查询来使用*/SELECT deptnoFROM empWHERE ename = 'SMITH';-- 先查询到Smith的部门编号-- 单行子查询SELECT *	FROM emp	WHERE deptno = (SELECT deptnoFROM empWHERE ename = 'SMITH');-- 多行子查询-- 如何查询和部门10的工作相同 的雇员的名字、岗位、工资、部门号 ， 但是不含10号部门自己的雇员/*	1.查询到10号部门有哪些工作岗位	2.把上面的查询结果当做是一个子查询来使用*/SELECT DISTINCT job	FROM emp	WHERE deptno=10;SELECT ename,job,sal,deptno	FROM emp	WHERE job IN( -- 返回了一个集合 ， 用inSELECT DISTINCT jobFROM empWHERE deptno=10)AND deptno !=10; -- 不含10号部门自己的雇员