美文网首页
Oracle 表复杂查询(五)

Oracle 表复杂查询(五)

作者: Down1 | 来源:发表于2020-10-23 10:15 被阅读0次

      表基本查询用 PL/SQL 的 scott 用户自带的四个表,选用三个表进行练习。

    • 雇员表 emp


      emp表
    • 部门表 dept


      dept表
    • 工资级别表 salgrade


      salgrade表

    一、数据分组

    ① max、min、avg、sum、count

    • 显示所有员工中最高工资和最低工资
    select max(sal),min(sal) from emp;
    
    • 显示最高工资的员工的名字(涉及子查询)
     select ename,sal from emp where sal=(select max(sal)from emp);
    
    • 显示所有员工的平均工资和工资总和
    select avg(sal),sum(sal) from emp;
    
    • 计算共有多少员工
    select count (*) from emp;
    
    • 显示工资最高的员工的名字,工作岗位
     select ename,job,sal from emp where sal=(select max(sal)from emp);
    
    • 显示工资高于平均工资的员工的信息
    select avg(sal) from emp;
    select * from emp where sal>(select avg(sal) from emp);
    

    ② group by 和 having 子句

    注:
      group by :用于对查询的结果分组统计。
      having子句:用于限制分组显示结果。

    • 显示每个部门的平均工资和最高工资
    select avg(sal),max(sal),deptno from emp group by deptno;
    
    • 显示每个部门的每种岗位的平均工资和最低工资
    select min(sal),avg(sal),max(sal),deptno from emp group by deptno,job;
    
    • 显示平均工资低于 2000 的部门号和它的平均工资
    select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;
    

    总结:数据分组

    1. 分组函数只能出现再选择列表、having、order by 子句种。

    2. 如果在 select 语句种同时包含有 group by、having、order by,那么他们的出现顺序必须是 group by、having,order by。

    3. 在选择列种如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在 group by 子句种中,否则就会出错。

    比如:

    select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000; 
     //这里deptno就一定要出现在group by中。
    

    二、多表查询

      多表查询是指:基于两个或两个以上的表或视图的查询。

    ① 笛卡尔积,利用别名

    • 显示雇员名,雇员工资及所在部门的名字
    select a1.ename,a1.sal,a2.deptno from emp a1,dept a2 where a1.deptno=a2.deptno;
    

    注意:
       多表查询的条件是:至少不能少于表的个数-1。也就是说,如果有两个表,他的条件就有一个。如果是三个表,他的条件就有两个。

    • 显示部门号为 10 的部门名、员工名和工资
    select al.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;
    
    • 显示各个员工的姓名、工资、及其工资的级别
      工资级别表在:salgrade 表中
    select * from salgrade;
    
         GRADE      LOSAL      HISAL
    ---------- ---------- ----------
             1        700       1200
             2       1201       1400
             3       1401       2000
             4       2001       3000
             5       3001       9999
    
    select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
    
    • 显示雇员名、雇员工资及所在部门的名字、并按部门排序
    select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;
    

    ② 自连接 (利用别名)

      自连接:同一张表的连接查询

    • 显示某个员工的上级领导的姓名
    select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno;
    
    • 显示 'FORD' 的上级
    select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename='FORD';
    

    ③ 子查询

      子查询:嵌入在其他 sql 语句中的 select 语句,也叫嵌套查询
      单行子查询:只返回一行数据的子查询语句

    select * from table where = (select …… select())  //嵌套格式
    
    • 显示与SMITH同一部门的所有员工
    select deptno from emp where ename='SMITH';//先查询出smith的部门号
    select * from emp where deptno=(select deptno from emp where ename='SMITH');//再嵌套显示显示与SMITH同一部门的所有员工
    

    注:数据库在执行sql是从左到右顺序执行。


    ④ 多行子查询:返回多行数据的子查询

    • 查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号
    select distinct job from emp where deptno=10;//先查询10号部门的工作
    select * from emp where job in (select distinct job from emp where deptno=10);//再嵌套查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
    

    ⑤ 在多行子查询中使用 all 操作符

    • 显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
    //方法1
    select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);
    //方法2
    select max(sal) from emp where deptno=30;
    select * from emp where sal>(select max(sal) from emp where deptno=30);
    

    ⑥ 多列子查询

      多列子查询:查询返回多个列数据的子查询语句。
      单行子查询:是子查询只返回单列,单行数据。
      多行子查询:返回单列多行数据,都是针对单列而言。

    • 查询与 SMITH 的部门和岗位完全相同的所有雇员
    select deptno,job from emp where ename='SMITH';//先查询出SMITH的部门号,岗位
    select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');//再嵌套查询与SMITH的部门和岗位完全相同的所有雇员
    

    ⑦ 在 from 子句中使用子查询

    • 显示高于自己部门平均工资的员工的信息
    select deptno,avg(sal) mysal from emp group by deptno;//先查出各个部门的平均工资和部门号
    select e.ename,e.deptno,e.sal,ds.mysal from emp e,
    (select deptno,avg(sal)mysal from emp group by deptno)ds 
    where e.deptno =ds.deptno and e.sal>ds.mysal;//再把上面的查询结果看做是一张子表
    

    总结:
      当 from 子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌式图。
      当 from 子句中使用子查询时,必须给子查询指定别名。
    注意:
      给表取别名的时候,不能加 as ;但是给列取别名是可以加 as 的。
      别名不能用 as 的情况,如:

    (select deptno,avg(sal)mysal from emp group by deptno)ds where e.deptno =ds.deptno and e.sal>ds.mysal;  //在ds前不能加as,否则会报错
    

    ⑧ 分页查询

    Oracle分页三种方式:
      1. 按 rowid 分页(效率最高,复杂)
      2. 按分析函数来分
      3. 按 rownum 分页

    其中利用 rownum 分页较为简单,所以这里以 rownum 为例:

    • 第一步:子查询
    select * from emp;
    
    • 第二步:显示 rownum(Oracle分配的)
    select a1.*,rownum rn from (select * from emp) al;
    
    • 第三步:选出 rn 编号大于等于 10 的员工信息。
    select a1.*,rownum rn from (select * from emp) al where rownum<=10;
    
    • 选出 rn 行号大于等于 10 的员工中再次截取大于等于 6 的行号的员工信息。
        如果需在这 rn 行号大于等于 10 的员工中再次截取大于等于 6 的行号就不能再使用 rownum 。需要使用子查询再次切割。
    select * from (select a1.*,rownum rn from (select * from emp) al where rownum<=10) where rn >=6;
    

    注意:
      指定查询列,只需要修改最里层的子查询。例如:

    select * from (select a1.*,rownum rn from (select ename,sal from emp) al where rownum<=10) where rn >= 6;
    

    ⑨ 其他

    • 使用特点格式插入日期值
        使用to_date函数

    • 使用子查询插入数据(插入大量数据)
        当使用Values子句时,一次只能插入一行数据;
        当使用子查询插入数据时,一条insert语句可以插入大量得数据;
        当处理行迁移或者装载外部表的数据到数据库时,可以使用子查询来插入数据。

    //先新建一张表,这张表想存储emp表中的部分数据,则导入。
    create table kkk(myId number(4),myname varcher2(50),myDept number(5));
    //把emp表中的10号员工的信息导入到kkk表
    insert into kkk (myID,myname,myDept) select empno,ename,deptno from emp where deptno =10;
    

    相关文章

      网友评论

          本文标题:Oracle 表复杂查询(五)

          本文链接:https://www.haomeiwen.com/subject/despmktx.html