一、多表查询
雇员信息表中包含三张表,分别是员工表(emp)、部门表(dept)和工资等级表(salgrade)。
员工表(emp)中包含如下字段:
- 雇员编号(empno)。
- 雇员姓名(ename)。
- 雇员职位(job)。
- 雇员领导编号(mgr)。
- 雇佣时间(hiredate)。
- 工资月薪(sal)。
- 奖金(comm)。
- 部门编号(deptno)。
部门表(dept)中包含如下字段:
- 部门编号(deptno)。
- 部门名称(dname)。
- 部门所在地点(loc)。
工资等级表(salgrade)中包含如下字段:
- 等级(grade)。
- 此等级最低工资(losal)。
- 此等级最高工资(hisal)。
select * from 表1,表2...
上面查询会将表1,表2关联起来,如果表1有3行数据,表2有4行数据,那么多表查询就有3*4=12行数据。(本质是穷举)
正常来讲如果表1有列deptno,表2有列deptno,那么两个列应该是要相等的,而下面这样不相等了
解决办法如下,表名.列名指明是哪个表的列名
select * from emp,dept where emp.deptno = dept.deptno;//这样筛选出两个表中deptno列相等的数据
显示部门号为10的部门名、员工名和员工工资
select dname, ename, sal from emp, dept where emp.deptno = dept.deptno and emp.deptno = 10;//部门deptno要一样才有效
显示各个员工的姓名,工资,及工资级别
select ename, sal, grade from emp, salgrade where sal > losal and sal <hisal;//工资要在losal到hisal区间才有效
二、自连接
在通过一张表查询
显示员工FORD的上级领导的编号和姓名
select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD');
子查询
1.单行子查询
返回一行记录的子查询
显示SMITH同一部门的员工
select ename, deptno from emp where deptno = (select deptno from emp where ename = 'SMITH');
2.多行子查询
返回多行记录的子查询,记得select选中的和要比较的是同一个数据
in:查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的。
select ename, job, sal, deptno from emp where job in (select job from emp where deptno = 10) and deptno!=10;
- 首先select job from emp where deptno = 10找到工作岗位等于10的工作
- job in…相当于job =…
all:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
all和in都一样
select ename, sal, deptno from emp where sal > all(select sal from emp where deptno=30);
- select sal from emp where deptno=30找到部门30中的所有工资
- sal > all…相当于sal要大于…中的工资
any:显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)
select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30);
- select sal from emp where deptno=30找到部门30中的所有工资
- sal > any…相当于sal要大于…中的任意一个人的工资
3.多列子查询
返回多列数据的子查询
显示和SMITH的部门和岗位完全相同的员工,不包含SMITH本人
select ename, deptno, job from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH') and ename != 'SMITH';
记得select选中的和要比较的是同一个数据,返回多条数据,用括号包起来比较
4.在from子句中使用子查询
子查询语句出现from子句中,其查询结果将会被当作一个临时表使用
显示高于每个自己部门平均工资的员工的姓名、部门、工资和部门的平均工资
1.每个部门的平均工资
select deptno, avg(sal) 平均工资 from emp group by deptno;
2.将emp和每个部门的平均工资做笛卡尔积,每个部门平均工资重命名为tmp
select * from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) tmp;
3.在emp和tmp两个表中,筛选两个表部门相等,员工工资大于平均工资
select * from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) tmp where emp.deptno = tmp.deptno and emp.sal > tmp.平均工资; select * from emp, (select deptno, avg(sal) from emp group by deptno) tmp where emp.deptno = tmp.deptno and emp.sal > tmp.avg(sal);//错误写法,不能写tmp.avg(sal),.后面要写成汉字
显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资
1.将表emp按照deptno分组后选择deptno和最高工资
select deptno , max(sal) 最高工资 from emp group by deptno
2.将emp和每个部门最高工资做积,每个部门最高工资重命名为tmp
select ename, sal, emp.deptno from emp, (select deptno , max(sal) 最高工资 from emp group by deptno) tmp
3.在emp和tmp两个表中,筛选两个表部门相等,员工工资等于最高工资
select ename, sal, emp.deptno from emp, (select deptno , max(sal) 最高工资 from emp group by deptno) tmp where emp.deptno = tmp.deptno and sal = 最高工资; select ename, sal, emp.deptno from emp, (select deptno , max(sal) 最高工资 from emp group by deptno) tmp where emp.deptno = tmp.deptno and sal = max(sal);//错误写法,不能写 sal = max(sal),s
显示每个部门的部门名、部门编号、所在地址和人员数量
1.在表emp中将列deptno分类,统计人员数量
select deptno, count(*) 人员数量 from emp group by deptno
2.将表emp和人员数量做积,每个部门人员数量重命名为tmp
select * from dept, (select deptno, count(*) 人员数量 from emp group by deptno) tmp ;
3.在dept和tmp两个表中,筛选部门名、部门编号、所在地址和人员数量
select dname, dept.deptno, loc, 人员数量 from dept, (select deptno, count(*) 人员数量 from emp group by deptno) tmp where dept.deptno = tmp.deptno;
四、合并查询-union和union all
将多个查询结果进行合并
- union用于取得两个查询结果的并集,union会自动去掉结果集中的重复行。
- union all也用于取得两个查询结果的并集,但union all不会去掉结果集中的重复行。
显示工资大于2500或职位是MANAGER的员工
select sal, job from emp where sal > 2500 or job = 'MANAGER';
用union,会对合并后的结果进行去重
select sal, job from emp where sal > 2500
union
select sal, job from emp where job = 'MANAGER';
用union all,不会对合并后的结果进行去重
select sal, job from emp where sal > 2500
union all
select sal, job from emp where job = 'MANAGER';
注意:待合并的两个查询结果的列的数量必须一致,否则无法合并