Oracle基础（多表查询与分页）

绝地灬酷狼 2022-05-31 04:19 479阅读 0赞

**本篇文章中只考虑实现查询功能，有提到优化，但是不细讲，后续会单独将SQL语句优化。Oracle中所有的表**

## 多表查询 ##

--1、员工表emp和部门表dept的笛卡尔集（笛卡尔集表=列数之和，行数之积，笛卡尔集表内中有些数据是不符合要求的）
    select emp.ename,dept.dname from emp,dept;
    
    --2、使用等值连接/内连接（只能使用=号），显示员工的编号，姓名，部门名，使用表别名简化
    select emp.empno,emp.ename,dept.dname,dept.deptno from emp,dept where emp.deptno = dept.deptno;
    
    --3、使用非等值连接（不能使用=号，其它符号可以，例如：>=，<=，<>，betwen and等），显示员工的编号，姓名，月薪，工资级别
    select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
    
    --内连接查询：只能查询出符合条件的记录
    --外连接查询：既能查询出符合条件的记录，也能根据一方强行将另一个方查询出来
    
    /* 使用外连接，按部门10,20,30,40号，统计各部门员工人数，要求显示部门号，部门名，人数 部门号 部门名 人数 10 ACCOUNTING 3 20 RESEARCH 5 30 SALES 6 40 OPERATIONS 0 等值连接/非等值连接/内连接：只会查询出多张表中，根据某个字段匹配，符合条件的记录，不符合条件的记录是不会存在的 */
    
    --4、左外连接[（+）是oracle专用的，不是SQL99规则]：
    /* 对于外连接，也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项： 1.（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。 2.当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。 3.（+）操作符只适用于列，而不能用在表达式上。 4.（+）操作符不能与OR和IN操作符一起使用。 5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。 */
    select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数" from dept,emp where dept.deptno = emp.deptno(+) group by dept.deptno,dept.dname;
    
    --5、右外连接：
    select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数" from dept,emp where emp.deptno(+) = dept.deptno group by dept.deptno,dept.dname;
    
    --6、使用左外连接，按部门10,20,30,40号，统计各部门员工人数，要求显示部门号，部门名，人数，且按人数降序排列
    select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数" from dept,emp where dept.deptno = emp.deptno(+) group by dept.deptno,dept.dname order by 3 desc;
    
    --6、使用自连接，显示"SMITH的上级是FORD"这种格式
    select users.ename || '的上级是' ||boss.ename from emp users,emp boss where users.mgr = boss.empno;
    --只有13条记录，不含有KING
    
    --7、基于上述问题，将KING的上级是“”显示出来
    select users.ename || '的上级是' ||boss.ename from emp users,emp boss where users.mgr = boss.empno(+);
    --14条记录
    --注意：自连接也用到内连接和外连接

## 子查询 ##

/* 子查询的作用：查询条件未知的事物 查询条件已知的问题：例如：查询工资为800的员工信息 查询条件未知的问题：例如：查询工资为20号部门平均工资的员工信息 一个条件未知的问题，可以分解为多个条件已知的问题 */
    
    --1、查询工资比WARD高的员工信息
    --第一：查询WARD的工资?
          select sal from emp where ename = 'WARD';
    
    --第二：查询工资比1250高的员工信息？
          select * from emp where sal > 1250;
    
    --子查询：
        select * from emp where sal > ( select sal from emp where ename = 'WARD' );
    
    --2、查询部门名为'SALES'的员工信息（方式一：子查询）
    
    --第一：查询部门名为'SALES'的编号？
          select deptno from dept where dname = 'SALES';
    --第二：查询部门号为30的员工信息？ 
          select * from emp where deptno = 30;
    --子查询：
          select * from emp where deptno = ( select deptno from dept where dname = 'SALES' );
    
    /* 子查询细节： 1）子查询与父查询可以针对同一张表 2）子查询与父查询可以针对不同张表 3) 子查询与父查询在传统参数时，数量要相同 4) 子查询与父查询在传统参数时，类型要相同 5) 子查询与父查询在传统参数时，含义要相同 */
    
    --查询部门名为'SALES'的员工信息（方式二：多表查询）
    select emp.* from dept,emp where (dept.deptno=emp.deptno) and (dept.dname='SALES'); 
    
    --3、查询每个员工编号,姓名，部门名,工资等级（三表查询，这三张表并无外健关联）
    select e.empno,e.ename,d.dname,s.grade from emp e,dept d,salgrade s where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);
    
    --4、查询工资最低的员工信息（单行子查询，使用=号）
    --第一：查询出工资最低是多少？
          select min(sal) from emp;
    --第二：查询工资为800的员工信息？
          select * from emp where sal = 800;
    --子查询：
          select * from emp where sal = ( select min(sal) from emp );
    
    --5、查询部门名为'ACCOUNTING'或'SALES'的员工信息（多行子查询，使用in关键字） 
    --第一：查询部门名为'ACCOUNTING'或'SALES'的部门编号？
          select deptno from dept where dname in ('ACCOUNTING','SALES');
    --第二：查询部门号为10或30号的员工信息？
          select * from emp where deptno in (10,30);
    --子查询：
          select * from emp where deptno in ( select deptno from dept where dname in ('ACCOUNTING','SALES') );
    
    --6、查询工资比20号部门【任意any】一个员工工资【低<】的员工信息（多行子查询，使用any关键字） 
    --第一：查询20号部门的所有工资？
          select sal from emp where deptno = 20;
    --第二：查询工资比(800,2975,3000,1100,3000)任意一个低的员工信息？
          select * from emp where sal < any (800,2975,3000,1100,3000);   
    --在oracle看来，<any就等于<集合中最大的那个值
    --子查询：
          select * from emp where sal <any ( select sal from emp where deptno = 20 ); 
    
    --7、查询工资比30号部门【所有all】员工【低<】的员工信息（多行子查询，使用all关键字） 
    
    --第一：查询出30部门所有员工的工资？ 
          select sal from emp where deptno = 30;
    --第二：查询工资比(1600,1250,1250,2850,1500,950)中所有的工资都低的员工信息？
          select * from emp where sal <all (1600,1250,1250,2850,1500,950);
    --子查询：
          select * from emp where sal <all ( select sal from emp where deptno = 30 );
    
    /* 注意：不容易理解的几个概念： 单行函数：输入一个参数，输出一个结果 多行函数：扫描多个参数，输出一个结果 单行子查询：子查询只会返回一个结果，例如：800，父查询用=/<>/>=/<=这些符号来比较 多行子查询：子查询会返回多于一个结果，例如：30,20，父查询用in/any/all这些符号来比较 当多表查询，子查询同时能解决问题时，按如下优先方案选择： 多表查询-->子查询 注意：上述结果不是说多表查询可以替代子查询，某些情况下，只能用子查询解决，例如：oracle分页 */

## 集合查询 ##

--1、使用并集运算，查询20号部门或30号部门的员工信息
    select * from emp where deptno = 20 union select * from emp where deptno = 30;
    /* 注意： union：二个集合中，如果都有相同的，取其一 union all：二个集合中，如果都有相同的，都取 */
    
    --使用set time/timing on，打开时间的开关 (开启，命令行当前显示时间HH:MM:SS)
    set time on;
    set time off;
    
    --使用set tim/timing off，关闭时间的开关(开启，输入SQL语句显示已用时间)
    set timing on;
    set timing off;
    
    --2、使用交集运算[intersect]，查询工资在1000-2000和1500-2500之间的员工信息（方式一）
    select * from emp where sal between 1000 and 2000 intersect select * from emp where sal between 1500 and 2500;
    
    --用where行过滤，查询工资在1000-2000和1500-2500之间的员工信息（方式二）
    select * from emp where (sal between 1000 and 2000) and (sal between 1500 and 2500);
    
    --3、使用差集运算[minus]，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式一）
    select * from emp where sal between 1000 and 2000 minus select * from emp where sal between 1500 and 2500;
    
    --4、使用where行过滤，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式二）
    select * from emp where (sal between 1000 and 2000) and (sal not between 1500 and 2500);
    
    --5、集合查询的细节：
    --1）集合操作时，必须确保集合列数是相等
    select empno,ename,sal,comm from emp where deptno = 20 union select empno,ename,sal from emp where deptno = 30; --错
    
    --2）集合操作时，必须确保集合列类型对应相同
    select empno,ename,sal,comm from emp where deptno = 20 union select empno,ename,sal,hiredate from emp where deptno = 30;--错
    
    --3）A union B union C = C union B union A
    select * from emp where deptno = 10 union select * from emp where deptno = 20 union select * from emp where deptno = 30;
    
    --4）当多个集合操作时，结果的列名由第一个集合列名决定
    select empno "编号",ename "姓名",sal "薪水" from emp where deptno = 20 union select empno,ename,sal from emp where deptno = 10;
    
    --当多表查询，子查询，集合查询都能完成同样任务时，按如下优化方案选择：
    --多表查询->子查询->集合查询

## Oracle分页 ##

--1、mysql分页 用limit关键字 
    
    --查询users表中前二条记录
    select * from users limit 0,2 --或 select * from users limit 2;
    --0表示第一条记录的索引号，索引号从0开始
    --2表示最多选取二个记录
    
    --查询出users前三条记录
    select * from users limit 0,3 --或 select * from users limit 3 --查询出users第2条到第4条记录 select * from users limit 1,3; 
    
    --2、hibernate分页API
    Query.setFirstResult(0);
    Query.setMaxResult(3);          
    
    --3、Oracle分页
    /* 什么是rownum，有何特点 1）rownum是oracle专用的关健字 2）rownum与表在一起，表亡它亡,表在它在 3）rownum在默认情况下，从表中是查不出来的 4）只有在select子句中，明确写出rownum才能显示出来 5）rownum是number类型，且唯一连续 6）rownum最小值是1，最大值与你的记录条数相同 7）rownum也能参与关系运算 8）基于rownum的特性，我们通常rownum只用于<或<=关系运算 */
    
    --显示emp表中3-8条记录（方式一：使用集合减运算）
    select rownum "伪列",emp.* from emp where rownum<=8 minus select rownum,emp.* from emp where rownum<=2;
    
    --显示emp表中3-8条记录（方式二：使用子查询，在from子句中使用，重点）
    select xx.* from (select rownum ids,emp.* from emp where rownum<=8) xx where ids>2;
    --注意：在子查询中的别名，不可加""引号
    
    --显示emp表中5-9条记录
    select yy.* from (select rownum ids,emp.* from emp where rownum<=9) yy where ids>=5;
    /* 注意:在项目中，from后台可能有真实表名，也可能用子查询看作的表名， 同时真实表和子查询看作的表要做连接查询 */