sql select 语句大部

涵盖大部分SQLselect语句用法

--------------mytieba---niit/niit-----------
select * from t_tieba_dir1;

select CLUB_DIR1_NAME ,COUNT_CLUBS from t_club_dir1;

create table t as select CLUB_DIR1_NAME ,COUNT_CLUBS from t_club_dir1;

select * from t_club_dir1 where count_clubs=0;

select * from t_club_dir1 where 1=1;

select * from t_club_dir1 where 1=2;

-----------scott/tiger-------------
select * from emp where deptno=20;

select * from emp where sal>2000;

select * from emp where comm is null;

select * from emp where comm is not null;

select emp.*,sal+comm from emp;

select emp.*,sal+nvl(comm,0) from emp;

select emp.*,nvl2(comm,comm,0) from emp;

select emp.*,to_char(hiredate,'yyyy-mm-dd') from emp;

select emp.*,to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp;

select emp.*,to_char(hiredate,'yyyy"年"mm"月"dd"日"') as chinese日期 from emp;

insert into emp values(8888,'9527','clerk',7698,to_date('1999-9-9','yyyy-mm-dd'),888,88,20);

select emp.* from emp where hiredate>to_date('1981-1-1','yyyy-mm-dd') and hiredate<to_date('1982-1-1','yyyy-mm-dd')

select emp.* from emp where hiredate between to_date('1981-1-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd')

select emp.* from emp where trunc(hiredate,'yyyy')= trunc(to_date('1981','yyyy'),'yyyy')

select trunc(sysdate,'yyyy') from dual

select sysdate from dual;

-----聚合函数-------
select count(*) from emp;

select count(job) from emp;

select distinct(job) from emp;

select count(distinct(job)) from emp;

select max(sal),min(sal),avg(sal) from emp;

-----模糊查询----%--_------

select * from emp where empno like '%8%'

select * from emp where empno like '%8%' or empno like '%9%'

select * from emp where ename like '%A%' or ename like '%O%'

select * from emp where ename like '%A%' and ename like '%O%'

select * from emp where ename not like '%A%' and ename not like '%O%'

select * from emp where sal>99 and sal<1000;

select * from emp where sal like '___';

---------集合操作符-------------------

create table t as select * from emp where deptno in(20,30)

create table m as select * from emp where deptno in(10,30)
------
select * from t
minus
select * from m;
--
deptno=20
------

------
select * from m
minus
select * from t;
--
deptno=10
------

------
select * from t
union
select * from m;
--
deptno=10,20,30
------


------
select * from t
union all
select * from m;
--
deptno=10,20,30,30
------

------
select * from t
intersect
select * from m;
--
deptno=30
------
-----------------排序--------------------

select * from emp order by sal;
select * from emp order by sal asc;
select * from emp order by sal desc;
select * from emp order by comm desc;
select * from emp order by deptn
o asc,sal desc;

----------分组-(输出列必须包含在group by字段中或被聚合函数所使用)---------
select empno,ename,job,deptno from emp group by deptno,job,empno,ename;

涵盖大部分SQLselect语句用法

select deptno,count(*) as 部门人数 from emp group by deptno;
------------------------------------------------------------------------------------------------
---------select * from 表 where 条件 group by 分组依据 order by 排序 -----having 再次条件-------
------------------------------------------------------------------------------------------------

------------------伪列--------------------
select emp.*,rowid,rownum from emp;

select emp.*,rowid,rownum from emp where rowid='AAAL+ZAAEAAAAAdAAA';
select emp.*,rowid,rownum from emp where rowid<'AAAL+ZAAEAAAAAdAAF';
select emp.*,rowid,rownum from emp where rowid>'AAAL+ZAAEAAAAAdAAF';
select emp.*,rowid,rownum from emp where rowid>'AAAL+ZAAEAAAAAdAAA' and rowid<'AAAL+ZAAEAAAAAdAAF' ;
select emp.*,rowid,rownum from emp where rowid between 'AAAL+ZAAEAAAAAdAAA' and 'AAAL+ZAAEAAAAAdAAF' ;
select rowid from emp where rownum<=1;
select min(rowid),max(rowid) from emp;


select emp.*,rowid,rownum from emp where rownum<6 ;
select emp.*,rowid,rownum from emp where rownum<=6 ;

select emp.*,rowid,rownum from emp order by rowid desc

select emp.*,rowid,rownum from emp where rownum<5 order by rowid desc;

--内部视图
select * from
(
select emp.* from emp order by rowid desc--形成内部视图
) where rownum<5;

--
select emp.* from emp where rownum<16
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 d …… 此处隐藏：4939字，全部文档内容请下载后查看。喜欢就下载吧 ……