SQL基础操作02-组函数、分组、子查询_行转列、rowid_rownum、表连接（92语法）、表连接（99语法）、视图和索引

组函数： 

-- avg(字段) min max sum count ---确定结果集,对结果集的数据求组函数
--注意: 当select后面一旦出现组函数,只能和其他的组函数,或者分组字段一起使用
      ----null 值不参与组函数计算
-- 统计一下一共有多少个员工
select count(*) from emp;
select count(empno) from emp;
select count(1) from emp;
select count(deptno) from emp;
select deptno from emp;

-- 统计一共有几个部门 
select count(deptno) from dept;
select count(loc) from dept;

-- 统计有员工存在的部门总数
select count( distinct deptno) from emp;

-- 统计20部门一共有多少人
select count(1) from emp where deptno = 20;

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;

--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp  where deptno = 30;

-- 请查询出 20部门的平均工资
select avg(sal) from emp where deptno = 20;

--null 值不参与组函数计算
-- 计算出所有员工的奖金总和
select comm from emp;
select sum(comm) from emp;

-- 统计有奖金的员工有几个
select count(comm) from emp;

--查询 最高薪水的员工姓名,及薪水
select max(sal) from emp; --最高薪资
select ename,sal from emp where sal = (最高薪资);
select ename,sal from emp where sal = (select max(sal) from emp);

-- 查询工资低于平均工资的员工编号，姓名及工资
--平均工资
select avg(sal) from emp;
-- 查询工资低于平均工资的员工编号，姓名及工资
select *  from emp where sal<(平均工资);
select *  from emp where sal<(select avg(sal) from emp);

--练习
--查询高于SMITH所在部门平均薪资的员工信息
select *
  from emp
 where sal >
       (select avg(sal)
          from emp
         where deptno = (select deptno from emp where ename = 'SMITH'))
   and deptno = (select deptno from emp where ename = 'SMITH');
         
--SMITH所在部门的平均薪资      
select avg(sal) from emp where deptno = (select deptno from emp where ename = 'SMITH');

--SMITH所在部门
select deptno from emp where ename = 'SMITH';

--查看高于本部门平均薪水员工姓名
select ename
  from emp e1
 where sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno);

select avg(sal) from emp where deptno = 10;

分组： 

--select 数据 from 数据源 where 行过滤条件 group by 分组字段  having 组过滤条件 order by 排序字段;
--执行流程: from --> where --> group by --> having --> select --> order by

--注意: 
   --1.如果一旦分组,select的后面就只能使用组函数与分组字段
   --2.当有需求想要以某个数据为单位计算数据值的时候,就可以使用分组 
   --3.where后不能使用组函数,having后可以使用组函数

--20部门的平均工资以及部门编号
select deptno,avg(sal) from emp where deptno = 20 group by deptno;

-- 找出20部门和30部门的最高工资 
--先过滤后分组
select max(sal),deptno from emp where deptno in(20,30) group by deptno;

--先分组后过滤
select max(sal),deptno from emp group by deptno having deptno in(20,30);

-- 求出每个部门的平均工资
select avg(sal),min(sal),deptno,empno from emp group by deptno;
select avg(sal) from emp where deptno = 10
UNION
select avg(sal) from emp where deptno = 20
UNION
select avg(sal) from emp where deptno = 30;

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal),deptno from emp where sal>1000 group by deptno order by avg(sal) desc;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal),deptno from emp where sal>1000 and deptno in(10,20) group by deptno order by avg(sal) desc;
select avg(sal),deptno from emp where sal>1000group by deptno having deptno in(10,20)  order by avg(sal) desc;

-- 找出每个部门的最高工资
select max(sal),deptno from emp group by deptno;

-- 求出平均工资高于2000的部门编号和平均工资
select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

select * from  (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal>2000;

--where后面不能使用字段名
select empno,ename e from emp where e='SMITH';

--按 部门岗位(job) 查询 平均工资
select avg(sal) from emp group by job;

--按 岗位查询 平均工资，且平均工资大于2000的岗位
select avg(sal) from emp group by job having avg(sal) > 2000;

--查询最低平均工资的部门编号
select min(avg(sal)) from emp group by deptno;

-- 统计每个部门的员工数，和部门编号,按照员工个数升序排序
select count(*),deptno from emp group by deptno order by count(*);

-- 查询每个工种的最高工资以及工种
select max(sal),job from emp group by job ;

-- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select avg(sal),deptno from emp group by deptno having avg(sal) between 1500 and 2000;

-- 查询工资高于20部门平均工资的员工
select * from emp where sal > (select avg(sal) from emp where deptno = 20);

子查询_行转列 ：

--学生
create table tb_student(
 id number(4) ,
 name varchar2(20),
 course varchar2(20),
 score number(5,2)
);

insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
rollback;

select * from tb_student;
--使用一条sql语句，查询每门课都大于80分的学生姓名
--有几门课程考了几门课程      最小分数>80
--一共有存在多少门课程
select count(distinct course) from tb_student;

--每个人的最小分数
select min(score) from tb_student group by name;

select name from tb_student group by name having count(course) = (课程总数) and min(score)>80;

select name
  from tb_student
 group by name
having count(course) = (select count(distinct course) from tb_student) and min(score) > 80;

--行转列
select name,decode(course,'语文',score) 语文,decode(course,'数学',score) 数学,decode(course,'英语',score) 英语 from tb_student;

select name,
       min(decode(course, '语文', score)) 语文,
       max(decode(course, '数学', score)) 数学,
       min(decode(course, '英语', score)) 英语
  from tb_student
 group by name;

rowid_rownum ：

--rowid 与 rownum 伪列

--rowid 行的唯一标识,相当于对象的地址,在插入到数据库表中时候就已经存在的
--主键: 唯一非空

--区分行记录的唯一|去重:
      --1.根据主键|唯一字段的值区别
      --2.如果一个表中没有主键或者唯一字段,可以根据记录的rowid
select empno,ename,sal,rowid from emp;

insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);

select * from tb_student;

--查询到要保留的数据
select distinct id,name,course,score,rowid from tb_student;  --显示去重
select max(rowid),id,name,course,score from tb_student group by id,name,course,score;  --要保留数据的rowid
select * from tb_student where rowid in (select max(rowid) from tb_student group by id);
--查询到要删除的数据
select * from tb_student where not rowid in (select max(rowid) from tb_student group by id);

--删除重复数据
delete from tb_student where not rowid in (select max(rowid) from tb_student group by id);

--rownum: 结果集序号 : 从1开始,每次+1
-- 基于结果集,没有结果集,就没有rownum,先确定结果集,再确定rownum
-- 使用rownum做分页分页的前提: 已经确定,有序从1开始,每次+1 ,如果不满足要求,只需在外部 嵌套select
select deptno,dname,loc,rowid,rownum from dept;

--分页查询:  rownum

select empno,ename,sal,rownum from emp;  --已经确定了结果集,已经确定了rownum,这个rownum可以拿来使用
--一页显示4 num条,3页显示完全,现在要显示第二 i页

--这一页其实数据编号: (i-1)*num+1    这一页的结束数据标号: i*4
select * from (select empno,ename,sal,rownum rn from emp) where rn>=5 and rn<=8;

--当sql中存在rownum与排序: 嵌套select,保证rownum已经确定,并且按照要求进行排序,并且rownum从1开始,每次加一有规律
select empno,ename,sal,rownum from emp order by sal desc;  --先确定rownum,后排序
select deptno,dname,loc,rownum from dept order by deptno desc;  --先排序后确定rownum

select empno,ename,sal,rd,rownum from (select empno,ename,sal,rownum rd from emp order by sal desc);

--根据薪资做降序排序,分页查询,每页显示3条,查询第2页的数据
select empno,ename,sal,rownum from emp order by sal desc
select empno,ename,sal,rownum rn from (select empno,ename,sal,rownum rn from emp order by sal desc) where rn>=4 and rn<=6;

表连接（92语法）：

--连表查询
--当想要查询得到数据来自于多个数据源的时候,需要使用连表查询
--92语法  99语法
--select 数据 from 数据源1,数据源2...;
--注意:  如果表连接中,使用了多个数据源中的同名字段,需要指明出处

-- 查询所有员工的信息以及所在部门的信息
--数据: 员工信息  部门信息
--来源: 员工表   部门表

--笛卡尔积 : 对乘
select * from emp,dept;

--表连接条件: 等值连接(值的相等或者不相等)   非等值连接(区间)
--位置: 定义在where后面

--等值连接
select * from emp e, dept d where e.deptno = d.deptno;

--每一个员工的员工姓名以及所在的部门名称
select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno = d.deptno;
 
--找出30部门的员工名称及部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and d.deptno = 30;  --线连接后过滤
select * from emp where deptno = 30;
select * from dept where deptno = 30;
select *
  from (select * from emp where deptno = 30),   --先过滤后连接-->效率更高
       (select * from dept where deptno = 30);


--非等值连接
-- 2500 所在的工资等级是多少
select * from salgrade;

select grade from salgrade where 2500 between losal and hisal;

--查询员工姓名，工资及等级
select empno,ename,sal,grade,losal,hisal from emp e,salgrade s where sal between losal and hisal;

-- 10和30部门的员工信息,部门信息以及工资等级
select * from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal between losal and hisal and e.deptno in(10,30);

--自连接
-- 找出有上级的员工的名称和对应的上级名称
--数据: 员工信息 emp e1  上级信息 emp e2
select * from emp e1,emp e2 where e1.mgr = e2.empno;

--以上都为内连接: 满足连接条件才显示,不满足连接条件不显示

--外连接
--主表:  主表中的数据,无论是否满足连接条件都会显示
--左边外连接|左连接 :  from emp e1,emp e2 主表在左边就是左连接
--右边外连接|右连接 :  主表在右边就是有链接
--在连接条件的位置,做为主表的表的对面添加(+)
--所有员工的员工信息 与 上级经理人信息
select * from emp e1,emp e2 where e1.mgr = e2.empno(+);  --左连接
select * from emp e2,emp e1 where e1.mgr = e2.empno(+);  --右连接

-- 找出所有有员工的部门名称以及员工数
select dname,count(1) from emp e,dept d where e.deptno = d.deptno group by e.deptno,dname;

--所有部门的部门名称与部门人数
--部门 表作为主表
select dname,count(empno) from emp e,dept d where e.deptno(+) = d.deptno group by e.deptno,dname;

表连接（99语法）： 

--表连接 99语法
--from 数据源1,数据源2...
--数据源1 join 数据源2.. 
--笛卡尔积 A cross join B..
select empno,ename,d.deptno,dname from emp e cross join dept d;
--笛卡尔积: 同名字段需要指明出处

--等值连接
--自然连接:natural join  字段关联两张表中的同名字段|主外键关系的字段做等值连接
select empno,ename,deptno,dname from emp e natural join dept d ;
--自然连接: 同名字段不要指明出处

--指明同名字段中使用哪一个字段做等值连接
--A join B using(同名字段)
select empno,ename,deptno,dname from emp e join dept d using(deptno);
--join..using: 同名字段不要指明出处

--A join B on 连接条件
--可以实现等值连接,可以实现非等值连接
select empno,ename,e.deptno,dname from emp e join dept d on e.deptno = d.deptno;
--join..on : 同名字段需要指明出处

--查询员工信息以及员工的薪资登记信息
select empno,ename,sal,grade,losal,hisal from emp e join salgrade s on e.sal between s.losal and s.hisal;

--30部门的查询员工信息,所在部门信息,薪资登记信息
select *
  from emp e
  inner join dept d
    on e.deptno = d.deptno
  join salgrade s
    on e.sal between s.losal and s.hisal
 where e.deptno = 30
 order by e.sal desc;

--查询所有有上级的员工信息,以及上级信息
select * from emp e1 join emp e2 on e1.mgr  = e2.empno;

--内连接 (inner) join: 满足条件显示,不满足条件不显示

--外连接 : 主表中的数据无论是否满足条件都会显示
--左外连接 : 主表在左边  left join
--右外连接 : 主表在右边  right join
--全连接 : 主表在两边    full join
select * from emp e1 right join emp e2 on e1.mgr  = e2.empno;

select * from emp e1 full join emp e2 on e1.mgr  = e2.empno;

select dname,count(empno) from emp join dept on emp.deptno = dept.deptno group by dept.deptno,dname;

视图和索引 ：

--视图:
  --虚拟表
  --逻辑视图 (√) 物理视图
  --逻辑视图 : 不存储数据,数据存在预原数据源中,修改也是修改的元数据源中的数据
  
--create or replace view 视图名 as select语句 [with read only];
create or replace view vw_haha as select empno,ename,sal,deptno from emp where deptno in(10,30) with read only;
  
--授权与权限回收
--授权: grant dba to SCOTT;
--回收: revoke dba from SCOTT;

SELECT * FROM VW_HAHA ;
SELECT * FROM EMP ;

--修改视图数据 DML->操作 的是原数据源emp表中的数据
delete from vw_haha where empno = 7934;

--drop view 视图名
drop view vw_haha;

--索引
--加快检索效率
--索引是透明的,对sql使用无影响
--相当于字典的目录
--可以为某张表的某个字段设置索引,当根据这个字段进行使用的时候,效率较高
--大量查询,少量做增删的字段适合设置索引,因为索引也是数据库维护的对象之一,需要维护和更新
--oracle默认为表的主键设置索引

--创建索引
--create index 索引名 on表名 (字段列表...)
create index haha on emp(sal);
--drop index 索引名
drop index haha;

select * from emp where sal>1500;

--查询每一个部门中部门经理的平均工资最低的部门名称

select dname,min(avg(e1.sal))
  from emp e1
  join emp e2
    on e1.mgr = e2.empno
  join dept d1
    on e1.deptno = d1.deptno 
 group by d1.deptno,dname;