oracle从入门到精通(5)------分页查询语句

oracle从入门到精通(5)

－－－－－－－－－－

分页查询语句

－－－－－－－－－－

select * from scott.emp;

select * from scott.dept;

select rownum,p.* from (select ename,sal from scott.emp order by sal desc) p;

select row_number() over(order by sal desc),ename,sal from scott.emp;

select row_number() over(order by sal ) 名次,ename from scott.emp;

select row_number() over(order by sal) from scott.emp;

select row_number() over(partition by deptno order by sal desc),ename,sal,deptno 

           

          from scott.emp;

          

select rank() over(partition by deptno order by sal desc) 排名,ename 员工名称,sal 薪水,

       deptno from scott.emp;

       

 select dense_rank() over(partition by a.deptno order by sal desc) 排名,

        a.ename 员工名称,a.sal 薪水, b.dname 部门名称 ，a.deptno

         from scott.emp a,scott.dept b

              where a.deptno=20 and a.deptno=b.deptno;      

      

  

create table emp as

             select * from  scott.emp;        

 

select * from emp;  

create table  myemp as

       select * from emp a,scott.dept b where a.deptno=b.deptno;

       

 

create table info5

(

  id number(10) primary key,

  name varchar2(20),

  age number(10),

  gender char(2)

)  

insert into info5 values(1,'java',18);    

insert into info5 values(2,'j2se',15);

insert into info5 values(3,'html',16);

insert into info5 values(4,'sql',20);

insert into info5 values(5,'mysql',22);

       

select name 姓名,decode(sign(age-18),1,'成年人','未成年人')提示  from info5;      

 

create  synonym syn_info5 for info5; 

grant all on info5 to zs;

grant all on syn_info5 to zs;

select * from syn_info5;

select * from zy.syn_info5;

create public synonym syn_info5_888 for info5;

 

   select * from  syn_info5_888; 

   

   drop public synonym syn_info5_888 ;