sql select 语句大部(2)

minus
select emp.* from emp where rownum<11;
--
select * from emp where rowid in(select rowid from emp where rownum<16)
and rowid not in(select rowid from emp where rownum<11);

--
select * from emp where
rowid in
(
select rowid from emp where rownum<
(
select count(*) from emp
)
)
and
rowid not in
(
select rowid from emp where rownum<
(
select count(*)-5 from emp
)
);

----------排列函数-----------
select emp.*,rownum as 顺序 from emp order by sal desc;

select empno,ename,mgr,job,sal,comm,deptno,rowid,rownum from (select emp.* from emp order by sal desc);

select emp.*,rank() over(order by sal desc) as orders from emp;

select emp.*,dense_rank() over(order by sal desc) as orders from emp;

------------特殊表dual------conn system/manager as sysdba------------
select * from dual;
insert into dual values(1);
update dual set d='y' where d='x';
delete from dual;
-------------------------
select 1+2 from dual;
select 10/4 from dual;
select sysdate from dual;
select floor(10/4) from dual;
select ceil(10/4) from dual;
---------------------------------------------------------------------
dual:oracle保留关键字，永远1行1列,确保语法树的完整
---------------------------------------------------------------------
-----------子查询:将一个查询的结果作为另一查询的条件-------------------

结果:单值
-----------------------------------------------------------------
select * from emp where sal>(select avg(sal) from emp);
select * from emp where sal>(select avg(sal) from emp)-500 and sal<(select av

涵盖大部分SQLselect语句用法

g(sal)+500 from emp);
-----------------------------------------------------------------
结果:多值
-----------------------------------------------------------------
select * from emp where job in('CLERK','ANALYST');

select * from emp where deptno in(select deptno from dept where loc in('NEW YORK','BOSTON'))

SELECT * from emp where sal>any(1000,2000);

SELECT * from emp where sal>all(1000,2000);

-----------连接查询-----------------
----无条件连接--交叉连接--笛卡尔积
create table d as select * from dept where deptno in(10,30,40);----15 records
create table e as select * from emp where deptno in(10,20,30);----3 records

select e.*,d.* from e,d ----15*3 records
----有条件连接--等同于内连接
select e.*,d.* from e,d where e.deptno=d.deptno-----9 records

----左外连接√
标准SQL语法:select emp.*,dept.* from emp left outer join dept on emp.deptno=dept.deptno;
oracle语法 :select emp.*,dept.* from emp,dept where emp.deptno=dept.deptno(+);
表别名 :select e.*,d.* from emp e,dept d where e.deptno=d.deptno(+);

----右外连接√
select emp.*,dept.* from emp right outer join dept on emp.deptno=dept.deptno;
select emp.*,dept.* from emp,dept where emp.deptno(+)=dept.deptno;

----内连接---取这张表中的共同项（对应项）√
select emp.*,dept.* from emp inner join dept on emp.deptno=dept.deptno;
select emp.*,dept.* from emp,dept where emp.deptno=dept.deptno;

----自连接√
select e1.emp
no,e1.ename,e1.mgr,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+);




-----------对象-----------------

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说公务员考试sql select 语句大部(2)在线全文阅读。