Oracle数据库SQL查询语句使用

--dense——rank()分析函数(查找每个部门工资最高前三名员工信息)
select * from (select deptno,ename,sal,dense_rank() over (partition by deptno order by sal desc) a from scott(数据库表名)) where a<=3 order by deptno asc, sal desc;
 
--rank()分析函数(运行结果与上句相同)
select * from (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) a from scott) where a<=3 order by deptno asc,sal desc;
 
--rows unbounded preceding 分析函数(显示各部门的积累工资总和)
select deptno,sal,sum(sal) over(order by deptno asc rows unbounded preceding) 积累工资总和 from scott;
 
--rows 整数值 preceding(显示每最后4条记录的汇总值)
select deptno,sal,sum(sal) over(order by deptno rows 3 preceding) 每4汇总值 from scott;
 
--查询所有用户
select * from dba_users;
select count(*) from dba_users;
select * from all_users;
select * from user_users;
select * from dba_roles;
 
--查询用户系统权限
select * from dba_sys_privs;
select * from user_users;
 
--查询用户对象或角色权限
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
 
--查询用户或角色所拥有的角色
select * from dba_role_privs;
select * from user_role_privs;
 
--rownum:查询10到12的信息
select * from scott a whererownum<=3 and a.empno not in(select b.empno from scott b where rownum<=9);
 
--not exists:查询scott表在dept表中没有的数据
select * from scoot a where not exists (select * from dept b where a.empno=b.deptno);
 
--rowid：查询重复数据信息
select * from scott a where a.rowid>(select min(x.rowid)from scoot x where x.empno=a.empno);
 
--根据rowid来分页(一万条数据，查询10000至9980时间大概在0.03秒左右)
select * from scott where rowid in(select rid from(select rownum rn,rid from(select rowid rid,empno from scott order by empno desc) where rownum<10)where rn>=1)order by empno desc;
 
--left outer join:左连接
select a.*,b.* from scott a left outer join dept b on a.deptno = b.deptno;
 
--right outer join:右连接
select a.*,b.* scott a right outer outer join dept b on a.deptno = b.deptno;
 
--inner join 
select a.*,b.* from scott a inner join dept b on a.deptno = b.deptno;
 
--full join
select a.*,b.* from scoot a full join dept b on a.deptno = b.deptno;
 
select a.*,b.* from scoot a,dept b where a.deptno(+) = b.deptno;
 
select distinct ename,sal from scott a froup by sal having;
 
--case when then end（交叉报表）
select ename,sal,case deptno when 10 then '会计部' when 20 then '研究部' when 30 then '销售部' else '其他部门' end 部门 from scott;
 
--交叉报表是使用分组函数与case结构一起实现
select 姓名,sum(case 课程 when '数学' then 分数 end)数学,sum(case 课程 when '历史' then 分数 end) 历史 from 学生 group by 姓名;
 
--decode函数
select 姓名,sum(decode(课程,'数学',分数,null))数学,sum(decode(课程,'语文',分数,null))语文,sum(decode(课程,'历史','分数',null))历史 from 学生 group by 姓名;
 
--lecel  connect by(层次查询)
select level,scott.* from scott connect by prior empno = mgr order by level;
 
--sys_connect_by_path函数
select ename,sys_connect_by_path(ename,'/')from scott start with mgr is null connect by prior empno=mgr;

--start with connect by prior 语法
select lpad(ename,3*(level),'')姓名,lpad(ename,3*(level),'')姓名 from scott where job<>'CLERK' start with mgr is null connect by prior mgr = empno;

--level与prior关键字
select level,scott.* from scott start wiht ename='SCOTT' connect by prior empno = mgr;
select level,scott.* from scott start wiht ename='SCOTT' connect by empno = prior mgr;

--等值连接
select empno,ename,job,sal,dname from scott a,dept b wherea.deptno = b.depno and (a.deptno = 10 or sal>2500);

--非等值连接
select a.ename,a.sal,b.grade from scott a,salgrade b where a.sal between b.losal and b.hisal;

--自连接
select a.ename,a.sal,b.ename from scott a,scott b where a.mgr=b.empno;

--左外连接
select a.ename,a.sal,b.ename from scott a,scott b where a.mgr=b.empno(+);

--多表连接
select * from scott ,dept,scott.salgrade where scott.deptno = dept.deptno and scott.sal between scott.losal and scott.hisal;

--单行子查询
select * from scott a where a.deptno=(select deptno from dept where loc='NEW YORK');
select * from scott a where a.deptno in (select deptno from dept where loc='NEW YORK');

--单行子查询在 from 后
select scott.*,(select deptno from dept where loc='NEW YORK') a from scott.emp;

--in:表示等于查询出来的对应数据
select ename,job,sal,deptno from scott where job in(select distinct job from scott where deptno=10);

--all:表示大于所有括号中查询出来的对应的数据信息
select ename,sal,deptno from scott where sal>all(select sal from scott where deptno=30);

--any:表示大于括号查询出来的其中任意一个即可（只随机一个）
select ename,sal,deptno from scott where sal>any(select sal from scott where deptno=30);

--多列子查询
select ename,job,sal,deptno from scott where(deptno,job)=(select deptno,job from scott where ename='SCOTT');

--非成对比较
select ename,job,sal,deptno from scott where sal in(select sal from scott where deptno=30) and nvl(comm,-1) in(select nvl(comm,-1) from scott where deptno=30);

--其他子查询
select ename,job,sal,deptno from scott where exists(select null from dept where dept.deptno=scott.deptno and dept.loc='NEW YORK');

--Insert与子查询(表间数据的拷贝)
insert into test(ename,job) select ename,job from scott.emp;

--Update与子查询
update test set(ename,job)=(select ename,job from scott where ename='SCOTT' and deptno ='10');

--delete与子查询
delete from test where ename in('');

--union语法（合并且去除重复行，且排序）
select ename,sal,deptno from scott where deptno>10 union select ename,sal,deptno from scott where deptno<30;

select a.deptno from scott a union select b.deptno from dept b ;

--union all(直接将两个结果集合并，不排序)
select ename,sal,deptno from scott.emp where deptno>10 union all select ename,sal,deptno from scott where deptno<30;

select a.deptno from scott a union all select b.deptno from dept b ;

--intersect:取交集
select ename,sal,deptno from scott where deptno>10 intersect select ename,sal,deptno from scott where deptno<30;

--处理重复行数据信息（删除，查找，修改）
select * from a1 a where not exists (select b.rd from (select rowid rd,row_number() over(partition by LOAN, BRANCH order by BEGIN_DATE desc) rn from a1) b where b.rn = 1 and a.rowid = b.rd);

--查询emp表数据信息重复问题
select * from scott.emp a where exists(select b.rd from(select rowid rd,row_number() over(partition by ename,job,mgr,hiredate,sal,comm,deptno order by empno asc) rn from scott.emp) b where b.rn=1 and a.rowid=b.rd);

-instr:在一个字符串中搜索指定的字符,返回发现指定的字符的位置
select instr(a.empno,a.mgr,1,1) from scott.emp a;

--length:返回字符串的长度
select ename,length(a.ename) as 长度,a.job,length(a.job) as 长度 from scott a;

--lower：返回字符串，并将所返回的字符小写
select a.ename as 大写,lower(a.ename) as 小写 from scott a;

---------创建表空间  要用拥有create tablespace权限的用户，比如sys
create tablespace tbs_dat datafile 'c:\oradata\tbs_dat.dbf' size 2000M;

---------添加数据文件
alter tablespace tbs_dat add datafile 'c:\oradata\tbs_dat2.dbf' size 100M;

---------改变数据文件大小
alter database datafile 'c:\oradata\tbs_dat.dbf' resize 250M;

---------数据文件自动扩展大小
alter database datafile 'c:\oradata\tbs_dat.dbf' autoextend on next 1m maxsize 20m;

---------修改表空间名称
alter tablespace tbs_dat rename to tbs_dat1;

---------删除表空间  and datafiles 表示同时删除物理文件
drop tablespace tbs_dat including contents and datafiles;

--substr(s1,s2,s3)：截取s1字符串,从s2开始,结束s3
select substr(job,3,length(job)) from scott ;

--replace：替换字符串
select replace(ename,'LL','aa') from scott;

select * from test;
insert into scott.test(ename,job) values('weather','好');
insert into scott.test(ename,job) values('wether','差');