sql select 语句大部
时间:2025-04-04
时间:2025-04-04
涵盖大部分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() ove …… 此处隐藏:4865字,全部文档内容请下载后查看。喜欢就下载吧 ……
下一篇:预算会议纪要表格