Oracle_Day04

数据的分组函数（组函数）

max min avg    （where 后面不能使用分组函数）

问题：求员工表中最大工资和最小工资的员工信息

select max(sal),min(sal) from emp;

问题：求表中员工最大公子的员工信息

    select * from emp where sal = max(sal); error;

    where 后面不能使用分组函数    解决办法：嵌套子查询

        select * from emp where sal = (select max(sal) from emp);

问题：查询员工的平均工资

select avg(sal) from emp;

问题：显示共有多少员工

select count(*) from emp; #count(*)为求和

select count(empno) from emp;#empno 为表中的工号

问题：查询工资低于平均工资的员工信息

select ename,sal from emp where  sal < (select avg(sal) from emp);

distinct关键字 去重

问题：查询员工表内员工分布在几个部门

select count (distinct deptno) from emp;

group by 子句（分组查询）：对查询结果进行分组统计，如果在select后面使用group by子句，就可以将行划分为较小的组，一旦使用分组后select操作的对象就变成了分组后的数据

问题：查询每个部门的平均工资

select deptno ||'号部门的平均工资'||avg(sal) from emp group by deptno

select a,c,函数(b) from tablename group by a,c; 

注意：在select列表中所有未包含在组函数中的列都应该包含在grouo by子句

问题：查询每个部门的最高工资和最低工资

select deptno,max(sal),min(sal) from emp gropu by deptno

having 子句（一般跟在group by之后，作用：对分组查询的结果作出限制）：对查询结果进行分组统计

问题：查询部门工资大于3000的部门编号

select avg(sal) from emp group by deptno having avg(sal)>3000;

总结：

1、组函数只能在select、having、order by 中使用，顺序为select having order by

2、group by->having->order by

where 与 having 的区别

where后面不能跟分组函数，having可以通用（查询一号部门的平均工资），在相同情况下，首先用where

多表查询：从多张表中查数据，根据需求分析出要得到的数据来自于多张表

问题：每个员工的姓名和工资和部门名称

select 姓名，工资，表from 表一，表二 where 判断条件

select t1.ename,t1.sal,t2.dname from emp t1 ,dept t2 where t1.deptno=t2.deptno

如果在多表查询的时候不指定连接条件，就会导致讲一个表中单的所有行都连接在另一个表的所有行上，成为笛卡尔积，如果一个表中有5000条数据，另一个表中有10条数据，将返回50000行数据

为了避免笛卡尔积，需要在where中加入有效的连接条件

等值连接（连接条件中为等号）：

问题：每个员工的姓名和工资和部门名称：select t1.ename,t1.sal,t2.dname from emp t1 ,dept t2 where t1.deptno=t2.deptno

不等值连接（连接条件中不为等号）：select t1.ename,t1.sal,t2.grade from emp t1,salgrade t2 where t1.sal>=t2.losal and t1.sal<=t2.hisal;（也可以用between and）

外连接（通过外连接，可以把对于连接条件不成立的记录依然包括在最后的结果中）：

左外连接：当连接条件不成立时，条件左边的数据要被包含，对等号右边的数据进行操作

右外连接：当连接条件不成立时，条件右边的数据要被包含，对等号左边的数据进行操作

问题：统计每个部门的员工数（要求你显示部门号，部门名字，人数）：

不包括外连接：select t2.deptno,t2.dname,count(t1.empno) from emp t1,dept t2 where t1.deptno(+) = t2.deptno group by t2.deptno,t2.dname;

自连接：将一张表用别名 视为多张表(自连接不适合做大表的查询，如果员工表中有10000条数据，那么自连接的话就会产生一亿条数据)

问题：查询员工的姓名和其所属领导的姓名

   select t1.ename,boss.ename from emp t1,emp t2 where t1.mgr = t2.empno

问题：显示上级领导是lary的员工名字

    select t1.ename,boss.ename from emp t1,emp t2 where t1.mgr = t2.empno and t2.ename=larry"

层次查询：

    层次查询可以替代自连接，并且本质上是个单表查询。

    构建树：将上一层的员工号=当前层员工的领导编号

    select level,enamel ,mgr from emp connect by prior empno = mgr start with mgr is null;

        level：伪列， 为层级及树的深度

    对比自连接和层次查询的区别，自连接慢，层次查询数据显示不直观

子查询：嵌套查询

    即一个select中嵌套了一个或者多个select，查询和Smith在同一个部门的员工的姓名和部门号

    第一步：select deptno from emp where ename=Smith"

    第二步：select ename from emp where deptno = (select deptno from emp where ename=Smith")

    应用场景：

        当一条查询语句需要依赖另一条查询语句的结果的时候。

            单行子查询：只返回一行数据的子查询

            多行子查询：返回多行数据的子查询

                问题：查询与一号部门工作相同的员工的姓名和工作和工资

                select distinct job from emp where deptno = 1;

                select ename,job,sal from emp where job in (select distinct job from emp where deptno = 1);

                问题：显示所有比部门1的所有员工的工资低的员工的姓名和工资

                select min(sal) from emp where deptno = 1;

                select ename,sal from emp where sal<(select min(sal) from emp where deptno = 1);

                或者    select ename,sal from emp where sal< all (select sal from emp where deptno = 1);

                问题：显示所有比部门1的所有员工的工资高的员工的姓名和工资

                select ename,sal from emp where sal > all (select sal from emp where deptno = 1);

                问题：显示所有比部门1的任意员工的工资高的员工的姓名和工资

                 select ename,sal from emp where sal > any (select sal from emp where deptno = 1);

         多列子查询：

                问题：查询和Smith部门相同部门并且相同工种的信息

                select deptno,job from emp where ename = 'Smith'

                select ename,job,deptno from emp where (deptno,job)=(select deptno,job from emp where ename = 'Smith');

                问题：查询和Smith相同部门相同工作相同领导相同工资的员工信息

                select deptno,job,leader,sal from emp where ename = 'Smith'

                select ename,deptno,job,leader,sal from emp where (deptno,job,leader,sal)=( select deptno,job,leader,sal from emp where ename = 'Smith');

                在from使用子查询（将子查询查出的数据当做新表看待）

                    问题：显示高于自己部门平均工资的员工的信息

                    select avg(sal),deptno from emp group by deptno;

                    select 工资.ename,工资.sal from emp 工资,(select avg(sal),deptno from emp group by deptno) 平均工资 where  工资.deptno=平均工资.deptno and 工资.sal>平均工资.sal

                在create中使用子查询，（对表的复制）

                    create table myemp(id,name,sal) as (select empno,ename,sal from emp);

                    建立一张新表，新表的数据类型，为emp中三个字段的数据类型。

                在insert中使用子查询，

                    insert into myemp (select empno,ename,sal from briup_emp where ename='Smith')

                

                在update中使用子查询

                    要把larry的工资改成和Smith一样的

                    update briup_emp set sal=(select sal from briup_emp where ename='Smith')where ename='Larry’