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 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(+);




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