Oracle子查询与分页查询

子查询

子查询是指嵌套在查询语句中的查询语句。子查询出现的位置一般为条件语句，如WHERE条件。Oracle会首先执行子查询，然后执行父查询。

子查询整个结果集会和父结果集中每个结果进行预算，通常效率比较低，因此建议在实际应用中如果能够不使用子查询则尽量较少使用。

 

查询复制填充表

查询复制数据填充新建表

CREATE TABLE backupStu AS SELECT name,age FROM students2

 

查询复制数据填充存在表

INSERT INTO copyTab SELECT name,age FROM students2

 

分页查询

分页的目的在于将过多符合条件的结果记录按照自定义数量显示数据，从而减少内存开销并提高查询效率

Oracle中通常使用联合、子查询以及结合伪列rowid和rownum实现对结果进行分页

 

minus结合rownum查询分页

select * from students2 where rownum <=5 minus

select * from students2 where rownum <1 --第1页

 

select * from students2 where rownum <=10 minus

select * from students2 where rownum <6 --第2页

 

select * from students2 where rownum <=15 minus

select * from students2 where rownum <11 --第3页

 

select * from students2 where rownum <=20 minus

select * from students2 where rownum <16 --第4页

 

子查询结合rownum查询分页

select * from(

       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=5)) s3

where s3.rn >=1;       --第1页

 

select * from(

       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=10)) s3

where s3.rn >=6;       --第2页

 

select * from(

       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=15)) s3

where s3.rn >=11;       --第3页

 

 

--子查询
--哪些部门没有员工
select ID 部门id,NAME 部门名称 FROM dep_table where id not in (select dep_id from emp_tab );

--哪些员工发放过工资
select ID,NAME FROM emp_tab WHERE ID IN (select emp_id from salary);
--哪些员工未发放过工资
select ID,NAME FROM emp_tab WHERE ID NOT IN (select emp_id from salary);

--查询数据填充新建表
SELECT * FROM salary;
DROP TABLE backupsalary;

CREATE TABLE backupSalary AS
SELECT emp_id ID,grantdate 发放年月,should 应发工资 FROM salary s;

select 应发工资 from backupSalary;

--查询数据填充已存在的表

CREATE TABLE back_sal
(
 ID NUMBER NOT NULL PRIMARY KEY,
 yingfa NUMBER
)

INSERT INTO back_sal select ID,salary.should from salary;
select * from back_sal;

--minus 结合rownum实现分页查询
select * from salary;
--第1页
select * from salary where rownum <=5
MINUS SELECT * FROM salary WHERE ROWNUM<1;
--第2页
select * from salary where rownum <=10
MINUS SELECT * FROM salary WHERE ROWNUM<6;
--第3页
select * from salary where rownum <=15
MINUS SELECT * FROM salary WHERE ROWNUM<11;

--子查询结合rownum实现分页查询
select * from(
       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=5)) s3
where s3.rn >=1;       --第1页

select * from(
       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=10)) s3
where s3.rn >=6;       --第2页

select * from(
       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=15)) s3
where s3.rn >=11;       --第3页
       
--使用子查询结合rowid,rownum not in语句实现分页
select * from salary where rowid not in (select rowid from salary
where rownum<1) and rownum <=5; --第一页

select * from salary where rowid not in (select rowid from salary
where rownum<6) and rownum <=5; --第二页

select * from salary where rowid not in (select rowid from salary
where rownum<11) and rownum <=5; --第三页

 

--1查询Sales表并使用结果数据填充一个名称为sales_backup的新表，要求查询数据只包含2018-6-1之后的数据。

select * from sales;
select * from emp;
select * from dept;

insert into sales values(seq_sales.nextval,to_date('2018-7-2','yyyy-MM-dd'),100,'661',2000,7000，'商品1');
insert into sales values(seq_sales.nextval,to_date('2021-7-2','yyyy-MM-dd'),100,'661',3000,8000，'商品2');
insert into sales values(seq_sales.nextval,to_date('2021-6-2','yyyy-MM-dd'),100,'661',3000,8000，'商品3');
insert into sales values(seq_sales.nextval,to_date('2018-6-2','yyyy-MM-dd'),100,'661',3000,8000，'商品4');

create table sales_backup
(
 ID number not null Primary key,
 salDate Date not null,
 PID number not null, 
 EID varchar2(64) not null,
 SalQuantity Number, 
 Price number,
 Descs varchar(1000)
)

INSERT INTO sales_backup select * from sales where sales.saldate>to_date('2018-6-1','yyyy-MM-dd');
select * from sales_backup;

--2分别使用内连接和左外连接和子查询实现2018年5月销售部没有销售业绩的员工姓名及其他若干信息。

--内连接
select distinct e.name from emp e inner join sales s on e.id not in s.eid and e.depid = 'NO300' 

--左外连接
select e.name 员工姓名,e.id 员工ID,e.depid 员工部门,s.id 销售id,s.saldate 销售日期,s.pid 销售员工id,s.eid 销售员工部门ID,s.descs 备注 from emp e left join sales s on e.id not in s.eid and e.depid = 'NO300' 

--右外连接
select * from emp e right join sales s on e.id not in s.eid and e.depid = 'NO300'

--子查询
select * from emp e where e.id not in (select distinct s.eid from sales s) and e.depid = 'NO300' 

--3 子查询实现2018-6哪种产品没有销售记录并显示产品编号和名称。
select id 产品编号,descs 产品名称 from sales where id not in(select s.id 产品编号 from sales s where s.saldate between to_date('2018-6','yyyy-MM') and to_date('2018-7','yyyy-MM'));

--4 使用minus联合及rownum伪列实现对sales销售表分页显示全部数据并未列定义别名，要求每页显示10条数据。
select * from sales where rownum<=2 minus select * from sales where rownum<1;--第1页
select * from sales where rownum<=4 minus select * from sales where rownum<3;--第2页
select * from sales where rownum<=6 minus select * from sales where rownum<5;--第3页

--5 使用子查询及rownum伪列实现对sales销售表分页显示全部数据并未列定义别名，要求每页显示5条数据。
select * from ((select rownum rn,s2.* from (select * from sales)s2 where rownum <=2 ))s3 where s3.rn >=1;--第1页
select * from ((select rownum rn,s2.* from (select * from sales)s2 where rownum <=4 ))s3 where s3.rn >=3;--第2页
select * from ((select rownum rn,s2.* from (select * from sales)s2 where rownum <=6 ))s3 where s3.rn >=5;--第3页

--6 使用子查询联合及rowid和rownum伪列实现对sales销售表分页显示全部数据，要求每页显示12条数据。
select * from sales where rowid not in(select rowid from sales where rownum<1) and rownum <=2;--第1页
select * from sales where rowid not in(select rowid from sales where rownum<3) and rownum <=4;--第2页
select * from sales where rowid not in(select rowid from sales where rownum<5) and rownum <=6;--第3页