Oracle DML 高级查询

/*
Message={"Author":"lucheng","Date":"2017.12.6",Host:"localhost","Database":"scott","Password":"tiger"}
*/
/*连接scott用户*/
conn scott/tiger;
/*
设置每行记录显示的长度为180
注意：DOC窗口宽度建议调成180
*/
set linesize 180;
/*设置每页显示的记录数*/
set pagesize 20;
/*查询scott中的表*/
select * from tab;
/*查看表结构*/
desc dept;
desc emp;
desc bonus;
desc salgrade;
/*查询scott中表的全部记录 *:意为所有 */
select * from dept;
select * from emp;
select * from bonus;
select * from salgrade;
select deptno,dname,loc from dept;
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
select ename,job,sal,comm from bonus;
select grade,losal,hisal from salgrade;
/*
如果按照集合的概念就相当于出现了一个乘积的概念，在数据库上称为迪卡尔积
*/
select * from emp,dept;
/*
多表查询产生迪卡尔积，显示时消除迪卡尔积的影响
多表查询在大数据、海量数据表中不可能去用的
*/
select * from emp,dept where emp.deptno = dept.deptno;
/*显示每个雇员的编号、姓名、工资、部门名称、部门位置*/
--emp表：雇员的编号、姓名、职位、工资
    --select empno,ename,job,sal from emp e;
--dept表：部门名称、部门位置
	--select dname,loc from dept d;
select e.empno,e.ename,e.sal,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;
/*
交叉连接：显示迪卡尔积
内连接：所有满足条件的数据都会被显示出来
外连接(左外连接、右外连接、全连接)：控制左表与右表的数据是否全部显示
自然连接：自动筛选符合相同列(例名)数据相关联的记录
*/
--交叉连接
select * from emp cross join dept;
--内连接
select * from emp e join dept d on (e.deptno = d.deptno);
select * from emp e join dept d using(deptno);
--左外连接，一个谓词只能引用一个外部联接的表
select * from emp e left join dept d on e.deptno = d.deptno;
select * from emp e left outer join dept d on e.deptno = d.deptno;
select * from emp e,dept d where e.deptno(+) = d.deptno;
--右外连接，一个谓词只能引用一个外部联接的表
select * from emp e right join dept d on e.deptno = d.deptno;
select * from emp e right outer join dept d on e.deptno = d.deptno;
select * from emp e,dept d where e.deptno = d.deptno(+);
--全外连接
select * from emp e left join dept d on e.deptno = d.deptno
union
select * from emp e right join dept d on e.deptno = d.deptno;
select * from emp full outer join dept using(deptno);
--自然连接
select * from emp natural join dept;
/*
union:并集
union:并集（显示重复数据）
intersect:交集
minus：差集
*/
select * from emp where deptno = 10
union
select * from emp where deptno = 20;
/*
分组统计查询：往往指的是部分数据具有某些共性，将具有共性的对象分为一组。
优先级：from子句 > where子句 >  group by子句 > having子句 >select子句 > order by子句。
在没有编写group by 子句之前，全表分为一组。
having子句在group by分组后进行筛选操作。
聚组函数用于分组查询中。
*/
select deptno from emp group by deptno;
/*查询每个部门中最高的基本工资*/
select deptno,max(sal) from emp group by deptno; 
/*查询每个部门中平均的基本工资*/
select deptno,avg(sal) from emp group by deptno;
/*查询每个部门中最低的基本工资*/
select deptno,min(sal) from emp group by deptno; 
/*
子查询：指的是在一个查询里面嵌套查询
出现的位置:
	where子句:子查询一般会返回单行单列、单行多列、多行单列
	having子句:子查询一般会返回单行单列，同时表示要使用统计函数计算
	from子句:子查询会返回多行多列数据
	select子句:返回单行单列
*/
/*查询低于公司平均基本工资的雇员信息*/
--公司平均基本工资
select avg(sal) from emp;
--雇员基本工资低于平均工资的雇员信息
select * from emp where sal < (select avg(sal) from emp);
/*与scott从事相同一工作，并且工资相同的雇员信息*/
--scott的工作和工资
select job,sal from emp where ename = 'SCOTT';
select * from emp where (job,sal) = (select job,sal from emp where ename = 'SCOTT');
/*查询与其中一个经理工资相等的雇员信息*/
--经理工资
select sal from emp where job='MANAGER';
select * from emp where sal in (select sal from emp where job='MANAGER');
--“=any”与“in”功能一样
select * from emp where sal =any (select sal from emp where job='MANAGER');
/*查询与所有经理工资都不相等的雇员信息*/
select * from emp where sal not in (select sal from emp where job='MANAGER');
/*查询比其中一个经理工资高的雇员信息*/
--“>any” 比子查询返回值中最小值大
select * from emp where sal >any (select sal from emp where job='MANAGER');
/*查询比经理中最高工资低的雇员信息*/
--“<any” 比子查询返回值中最大值小
select * from emp where sal <any (select sal from emp where job='MANAGER');
/*查询比所有经理工资高的雇员信息*/
select * from emp where sal >all (select sal from emp where job='MANAGER');
/*查询比所有经理工资低的雇员信息*/
select * from emp where sal <all (select sal from emp where job='MANAGER');
/*查询出高于公司平均工资的职位名称、职位人数、职位平均工资*/
--公司的平均基本工资
select avg(sal) from emp;
--职位名称、职位人数、职位平均工资
select job,count(empno),avg(sal) from emp group by job;
--高于公司平均工资的职位名称、职位人数、职位平均工资
select job,count(empno),avg(sal) from emp group by job having avg(sal) > (select avg(sal) from emp);
/*查询雇员编号、姓名、工作以及部门名称*/
select e.empno,e.ename,e.job from emp e;
--select子句中的子查询会执行多次，一般不会去使用（需要回避 1+n次查询）
select e.empno,e.ename,e.job,(select d.dname from dept d where d.deptno=e.deptno) from emp e;
/*查询出每个部门名称、位置、部门人数*/
--方式一：连表查，产生大量迪卡尔积
select d.dname,d.loc,count(e.empno) from emp e,dept d where e.deptno(+) = d.deptno group by d.dname,d.loc;
--方式二：使用子查询，产生较少迪卡尔积
select d.deptno,d.dname,d.loc from dept d;
select deptno,count(empno) from emp group by deptno;
select d.deptno,d.dname,d.loc,temp.amount from dept d,(select deptno,count(empno) amount from emp group by deptno) temp where d.deptno = temp.deptno(+);