Oracle查詢語句

select * from scott.emp ;

1.--dense_rank()分析函數(shù)（查找每個部門工資最高前三名員工信息）

select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) a from scott.emp) where a=3 order by deptno asc,sal desc ;

結(jié)果：

--rank()分析函數(shù)(運行結(jié)果與上語句相同)

select * from (select deptno,ename,sal,rank() over(partition by deptno order by sal desc) a from scott.emp ) where a=3 order by deptno asc,sal desc ;

結(jié)果：

--row_number()分析函數(shù)（運行結(jié)果與上相同）

select * from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) a from scott.emp) where a=3 order by deptno asc,sal desc ;

--rows unbounded preceding 分析函數(shù)（顯示各部門的積累工資總和）

select deptno,sal,sum(sal) over(order by deptno asc rows unbounded preceding) 積累工資總和 from scott.emp ;

結(jié)果：

--rows 整數(shù)值 preceding(顯示每最后4條記錄的匯總值)

select deptno,sal,sum(sal) over(order by deptno rows 3 preceding) 每4匯總值 from scott.emp ;

結(jié)果：

--rows between 1 preceding and 1 following（統(tǒng)計3條記錄的匯總值【當前記錄居中】）

select deptno,ename,sal,sum(sal) over(order by deptno rows between 1 preceding and 1 following) 匯總值 from scott.emp ;

結(jié)果：

--ratio_to_report(顯示員工工資及占該部門總工資的比例)

select deptno,sal,ratio_to_report(sal) over(partition by deptno) 比例 from scott.emp ;

結(jié)果：

--查看所有用戶

select * from dba_users ;

select count(*) from dba_users ;

select * from all_users ;

select * from user_users ;

select * from dba_roles ;

--查看用戶系統(tǒng)權(quán)限

select * from dba_sys_privs ;

select * from user_users ;

--查看用戶對象或角色權(quán)限

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.emp a where rownum=3 and a.empno not in(select b.empno from scott.emp b where rownum=9);

結(jié)果：

--not exists；查詢emp表在dept表中沒有的數(shù)據(jù)

select * from scott.emp a where not exists(select * from scott.dept b where a.empno=b.deptno) ;

結(jié)果：

--rowid;查詢重復數(shù)據(jù)信息

select * from scott.emp a where a.rowid>(select min(x.rowid) from scott.emp x where x.empno=a.empno);

--根據(jù)rowid來分頁(一萬條數(shù)據(jù)，查詢10000至9980時間大概在0.03秒左右)

select * from scott.emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,empno from scott.emp order by empno desc) where rownum10)where rn>=1)order by empno desc ;

結(jié)果：

--根據(jù)分析函數(shù)分頁(一萬條數(shù)據(jù)，查詢10000至9980時間大概在1.01秒左右)

select * from(select a.*,row_number() over(order by empno desc) rk from scott.emp a ) where rk10 and rk>=1;

結(jié)果：

--rownum分頁(一萬條數(shù)據(jù)，查詢10000至9980時間大概在0.01秒左右)

select * from(select t.*,rownum rn from(select * from scott.emp order by empno desc)t where rownum10) where rn>=1;

select * from(select a.*,rownum rn from (select * from scott.emp) a where rownum=10) where rn>=5 ;

--left outer join:左連接

select a.*,b.* from scott.emp a left outer join scott.dept b on a.deptno=b.deptno ;

--right outer join:右連接

select a.*,b.* from scott.emp a right outer join scott.dept b on a.deptno=b.deptno ;

--inner join

select a.*,b.* from scott.emp a inner  join scott.dept b on a.deptno=b.deptno ;

--full join

select a.*,b.* from scott.emp a full join scott.dept b on a.deptno=b.deptno ;

select a.*,b.* from scott.emp a,scott.dept b where a.deptno(+)=b.deptno ;

select distinct ename,sal from scott.emp a group by sal having ;

select * from scott.dept ;

select * from scott.emp ;

--case when then end (交叉報表)

select ename,sal,case deptno when 10 then '會計部' when 20 then '研究部' when 30 then '銷售部' else '其他部門' end 部門 from scott.emp ;

結(jié)果：

select ename,sal,case when sal>0 and sal1500 then '一級工資' when sal>=1500 and sal3000 then '二級工資' when sal>=3000 and sal4500 then '三級工資' else '四級工資' end 工資等級 from scott.emp order by sal desc ;

結(jié)果：

--交叉報表是使用分組函數(shù)與case結(jié)構(gòu)一起實現(xiàn)

select 姓名,sum(case 課程 when '數(shù)學' then 分數(shù) end)數(shù)學,sum(case 課程 when '歷史' then 分數(shù) end)歷史 from 學生 group by 姓名 ;

--decode 函數(shù)

select 姓名,sum(decode(課程,'數(shù)學',分數(shù),null))數(shù)學,sum(decode(課程,'語文',分數(shù),null))語文,sum(decode(課程,'歷史','分數(shù)',null))歷史 from 學生 group by 姓名 ;

--level。。。。connect by(層次查詢)

select level,emp.* from scott.emp connect by prior empno = mgr order by level ;

結(jié)果：

--sys_connect_by_path函數(shù)

select ename,sys_connect_by_path(ename,'/') from scott.emp start with mgr is null connect by prior empno=mgr ;

結(jié)果：

--start with connect by prior 語法

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

--level與prior關鍵字

select level,emp.* from scott.emp start with ename='SCOTT' connect by prior empno=mgr;

select level,emp.* from scott.emp start with ename='SCOTT' connect by empno = prior mgr ;

結(jié)果：

--等值連接

select empno,ename,job,sal,dname from scott.emp a,scott.dept b where a.deptno=b.deptno and (a.deptno=10 or sal>2500);

結(jié)果：

--非等值連接

select a.ename,a.sal,b.grade from scott.emp a,scott.salgrade b where a.sal between b.losal and b.hisal ;

結(jié)果：

--自連接

select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno ;

結(jié)果：

--左外連接

select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno(+);

結(jié)果：

--多表連接

select * from scott.emp ,scott.dept,scott.salgrade where scott.emp.deptno=scott.dept.deptno and scott.emp.sal between scott.salgrade.losal and scott.salgrade.hisal ;

結(jié)果：

select * from scott.emp a join scott.dept b on a.deptno=b.deptno join scott.salgrade s on a.sal between s.losal and s.hisal where a.sal>1000;

select * from(select * from scott.emp a join scott.dept b on a.deptno=b.deptno where a.sal>1000) c join scott.salgrade s on c.sal between s.losal and s.hisal ;

--單行子查詢

select * from scott.emp a where a.deptno=(select deptno from scott.dept where loc='NEW YORK');

select * from scott.emp a where a.deptno in (select deptno from scott.dept where loc='NEW YORK');

結(jié)果：

--單行子查詢在 from 后

select scott.emp.*,(select deptno from scott.dept where loc='NEW YORK') a from scott.emp ;

--使用 in ,all,any 多行子查詢

--in：表示等于查詢出來的對應數(shù)據(jù)

select ename,job,sal,deptno from scott.emp where job in(select distinct job from scott.emp where deptno=10);

--all:表示大于所有括號中查詢出來的對應的數(shù)據(jù)信息

select ename,sal,deptno from scott.emp where sal>all(select sal from scott.emp where deptno=30);

--any:表示大于括號查詢出來的其中任意一個即可（只隨機一個）

select ename,sal,deptno from scott.emp where sal>any(select sal from scott.emp where deptno=30);

--多列子查詢

select ename,job,sal,deptno from scott.emp where(deptno,job)=(select deptno,job from scott.emp where ename='SCOTT');

select ename,job,sal,deptno from scott.emp where(sal,nvl(comm,-1)) in(select sal,nvl(comm,-1) from scott.emp where deptno=30);

--非成對比較

select ename,job,sal,deptno from scott.emp where sal in(select sal from scott.emp where deptno=30) and nvl(comm,-1) in(select nvl(comm,-1) from scott.emp where deptno=30);

--其他子查詢

select ename,job,sal,deptno from scott.emp where exists(select null from scott.dept where scott.dept.deptno=scott.emp.deptno and scott.dept.loc='NEW YORK');

select ename,job,sal from scott.emp join(select deptno,avg(sal) avgsal,null from scott.emp group by deptno) dept on emp.deptno=dept.deptno where sal>dept.avgsal ;

create table scott.test(

       ename varchar(20),

       job varchar(20)

);

--drop table test ;

select * from scott.test ;

--Insert與子查詢(表間數(shù)據(jù)的拷貝)

insert into scott.test(ename,job) select ename,job from scott.emp ;

--Update與子查詢

update scott.test set(ename,job)=(select ename,job from scott.emp where ename='SCOTT' and deptno ='10');

--創(chuàng)建表時，還可以指定列名

create table scott.test_1(ename,job) as select ename,job from scott.emp ;

select * from scott.test_1 ;

--delete與子查詢

delete from scott.test where ename in('');

--合并查詢

--union語法（合并且去除重復行，且排序）

select ename,sal,deptno from scott.emp where deptno>10 union select ename,sal,deptno from scott.emp where deptno30 ;

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

--union all(直接將兩個結(jié)果集合并，不排序)

select ename,sal,deptno from scott.emp where deptno>10 union all select ename,sal,deptno from scott.emp where deptno30 ;

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

--intersect:取交集

select ename,sal,deptno from scott.emp where deptno>10 intersect select ename,sal,deptno from scott.emp where deptno30;

--顯示部門工資總和高于雇員工資總和三分之一的部門名及工資總和

select dname as 部門,sum(sal) as 工資總和 from scott.emp a,scott.dept b where a.deptno=b.deptno group by dname having sum(sal)>(select sum(sal)/3 from scott.emp c,scott.dept d where c.deptno=d.deptno);

結(jié)果：

--使用with得到以上同樣的結(jié)果

with test as (select dname ,sum(sal) sumsal  from scott.emp ,scott.dept where scott.emp.deptno=scott.dept.deptno group by dname) select dname as 部門,sumsal as 工資總和 from scott.test where sumsal>(select sum(sumsal)/3 from scott.test);

結(jié)果：

--分析函數(shù)

select ename,sal,sum(sal) over(partition by deptno order by sal desc) from scott.emp ;

--rows n preceding(窗口子句一)

select deptno,sal,sum(sal) over(order by sal rows 5 preceding) from scott.emp ;

結(jié)果：

--rum(..) over(..)..

select sal,sum(1) over(order by sal) aa from scott.emp  ;

select deptno,ename,sal,sum(sal) over(order by ename) 連續(xù)求和,sum(sal) over() 總和,100*round(sal/sum(sal) over(),4) as 份額 from scott.emp;

結(jié)果：

select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) 部門連續(xù)求和,sum(sal) over(partition by deptno) 部門總和,100*round(sal/sum(sal) over(),4) as 總份額 from scott.emp;

結(jié)果：

select deptno,sal,rank() over (partition by deptno order by sal),dense_rank() over(partition by deptno order by sal) from scott.emp order by deptno ;

結(jié)果；

select * from (select rank() over(partition by 課程 order by 分數(shù) desc) rk,分析函數(shù)_rank.* from 分析函數(shù)_rank) where rk=3 ;

--dense_rank():有重復的數(shù)字不跳著排列

--row_number()

select deptno,sal,row_number() over(partition by deptno order by sal) rm from scott.emp ;

結(jié)果：

--lag()和lead()

select deptno,sal,lag(sal) over(partition by deptno order by sal) 上一個,lead(sal) over(partition by deptno order by sal) from scott.emp ;

結(jié)果：

--max(),min(),avg()

select deptno,sal,max(sal) over(partition by deptno order by sal)最大,min(sal) over(partition by deptno order by sal)最小,avg(sal) over(partition by deptno order by sal)平均 from scott.emp ;

結(jié)果：

--first_value(),last_value()

select deptno,sal,first_value(sal) over(partition by deptno)最前,last_value(sal) over(partition by deptno )最后 from scott.emp ;

結(jié)果：

--分組補充 group by grouping sets

select deptno ,sal,sum(sal) from scott.emp group by grouping sets(deptno,sal);

select null,sal,sum(sal) from scott.emp group by sal union all select deptno,null,sum(sal) from scott.emp group by deptno ;

結(jié)果：

--rollup

select deptno,job,avg(sal) from scott.emp group by rollup(deptno,job) ;

--理解rollup等價于

select deptno,job,avg(sal) from scott.emp group by deptno,job union select deptno ,null,avg(sal) from scott.emp group by deptno union select null,null,avg(sal) from scott.emp ;

結(jié)果：

select deptno,job,avg(sal) a from scott.emp group by cube(deptno,job) ;

--理解CUBE

select deptno,job,avg(sal) from scott.emp group by cube(deptno,job) ;

--等價于

select deptno,job,avg(sal) from scott.emp group by grouping sets((deptno,job),(deptno),(job),());

結(jié)果：

--查詢工資不在1500至2850之間的所有雇員名及工資

select ename,sal from scott.emp where sal not in(select sal from scott.emp where sal between 1500 and 2850 );

--部門10和30中的工資超過1500的雇員名及工資

select deptno,ename,sal from scott.emp a where a.deptno in(10,30) and a.sal>1500 order by sal desc ;

結(jié)果：

--在1981年2月1日至1981年5月1日之間雇傭的雇員名，崗位及雇傭日期，并以雇傭日期先后順序排序

select ename as 姓名,job as 崗位,hiredate as 雇傭日期 from scott.emp a where a.hiredate between to_date('1981-02-01','yyyy-mm-dd') and to_date('1981-05-01','yyyy-mm-dd') order by a.hiredate asc ;

結(jié)果：

select * from scott.emp where hiredate >to_date('1981-02-01','yyyy-MM-dd');

--查詢獲得補助的所有雇傭名，工資及補助額，并以工資和補助的降序排序

select ename,sal,comm from scott.emp a where a.comm > all(0) order by comm desc;

--工資低于1500的員工增加10%的工資，工資在1500及以上的增加5%的工資并按工資高低排序（降序）

select ename as 員工姓名,sal as 補發(fā)前的工資,case when sal1500 then (sal+sal*0.1) else (sal+sal*0.05) end 補助后的工資 from scott.emp order by sal desc ;

結(jié)果：

--查詢公司每天，每月，每季度，每年的資金支出數(shù)額

select sum(sal/30) as 每天發(fā)的工資,sum(sal) as 每月發(fā)的工資,sum(sal)*3 as 每季度發(fā)的工資,sum(sal)*12 as 每年發(fā)的工資 from scott.emp;

結(jié)果：

--查詢所有員工的平均工資，總計工資，最高工資和最低工資

select avg(sal) as 平均工資,sum(sal) as 總計工資,max(sal) as 最高工資,min(sal) as 最低工資 from scott.emp;

結(jié)果：

--每種崗位的雇員總數(shù)和平均工資

select job as 崗位,count(job) as 崗位雇員總數(shù),avg(sal) as 平均工資 from scott.emp group by job order by 平均工資 desc;

結(jié)果：

--雇員總數(shù)以及獲得補助的雇員數(shù)

select count(*) as 公司雇員總數(shù),count(comm) as 獲得補助的雇員人數(shù) from scott.emp ;

--管理者的總?cè)藬?shù)

--雇員工資的最大差額

select max(sal),min(sal),(max(sal) - min(sal)) as 員工工資最大差額 from scott.emp ;

--每個部門的平均工資

select deptno,avg(sal) from scott.emp a group by a.deptno;

結(jié)果：

--查詢每個崗位人數(shù)超過2人的所有職員信息

select * from scott.emp a,(select c.job,count(c.job) as sl from scott.emp c group by c.job ) b where b.sl>2 and a.job=b.job;

結(jié)果：

select * from scott.emp a where a.empno in(select mgr from scott.emp ) and (select count(mgr) from scott.emp)>2 ;

結(jié)果：

--處理重復行數(shù)據(jù)信息（刪除，查找，修改）

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表數(shù)據(jù)信息重復問題

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

--initcap：返回字符串，字符串第一個字母大寫

select initcap(ename) Upp from scott.emp ;

結(jié)果：

--ascii:返回與指定的字符對應的十進制數(shù)

select ascii(a.empno) as 編號,ascii(a.ename) as 姓名,ascii(a.job) as 崗位 from scott.emp a ;

結(jié)果：

--chr:給出整數(shù)，返回對應的字符

select chr(ascii(ename)) as 姓名 from scott.emp ;

結(jié)果：

--concat:連接字符串

select concat(a.ename,a.job)|| a.empno as 字符連接 from scott.emp a;

結(jié)果：

--instr:在一個字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置

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.emp a ;

--lower：返回字符串，并將所返回的字符小寫

select a.ename as 大寫,lower(a.ename) as 小寫 from scott.emp a ;

結(jié)果：

--upper：返回字符串，并將返回字符串都大寫

select lower(a.ename) as 小寫名字,upper(a.ename) as 大寫名字 from scott.emp a ;

結(jié)果：

--rpad:在列的右邊粘貼字符，lpad: 在列的左邊粘貼字符(不夠字符則用*來填滿)

select lpad(rpad(a.ename,10,'*'),16,'*') as 粘貼 from scott.emp a ;

結(jié)果：

--like不同角度的使用

select * from scott.emp where ename like '%XXR%';

select * from scott.emp where ename like '%S';

select * from scott.emp where ename like 'J%';

select * from scott.emp where ename like 'S';

select * from scott.emp where ename like '%S_';

--每個部門的工資總和

select a.ename,sum(sal) from scott.emp a group by ename;

--每個部門的平均工資

select a.deptno,avg(sal) from scott.emp a group by deptno ;

--每個部門的最大工資

select a.deptno,max(sal) from scott.emp a group by deptno ;

--每個部門的最小工資

select a.deptno,min(sal) from scott.emp a group by deptno ;

--查詢原工資占部門工資的比率

select deptno ,sal,ratio_to_report(sal) over(partition by deptno) sal_ratio from scott.emp ;

--查詢成績不及格的所有學生信息（提示：沒有對應的表，只是意思意思。不及格人數(shù)大于等于三才能查）

select * from scott.emp where empno in(select distinct empno from scott.emp where 3(select count(sal) from scott.emp where sal3000) and empno in(select empno from scott.emp where sal3000));

結(jié)果：

--查詢每個部門的平均工資

select distinct deptno,avg(sal) from scott.emp group by deptno  order by deptno desc;

--union組合查出的結(jié)果，但要求查出來的數(shù)據(jù)類型必須相同

select sal from scott.emp where sal >=all(select sal from scott.emp ) union select sal from scott.emp ;

select * from scott.emp a where a.empno between 7227 and 7369 ;--只能從小到大

---------創(chuàng)建表空間  要用擁有create tablespace權(quán)限的用戶，比如sys

create tablespace tbs_dat datafile 'c:\oradata\tbs_dat.dbf' size 2000M;

---------添加數(shù)據(jù)文件

alter tablespace tbs_dat add datafile 'c:\oradata\tbs_dat2.dbf' size 100M;

---------改變數(shù)據(jù)文件大小

alter database datafile 'c:\oradata\tbs_dat.dbf' resize 250M;

---------數(shù)據(jù)文件自動擴展大小

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開始,結(jié)束s3

select substr(job,3,length(job)) from scott.emp ;

--replace：替換字符串

select replace(ename,'LL','aa') from scott.emp;

select * from scott.test;

insert into scott.test(ename,job) values('weather','好');

insert into scott.test(ename,job) values('wether','差');

--soundex：返回一個與給定的字符串讀音相同的字符串

select ename from scott.test where soundex(ename)=soundex('wether');

--floor：取整數(shù)

select sal,floor(sal) as 整數(shù) from scott.emp ;

--log（n,s）：返回一個以n為低，s的對數(shù)

select empno,log(empno,2) as 對數(shù) from scott.emp ;

--mod(n1,n2)：返回一個n1除以n2的余數(shù)

select empno,mod(empno,2) as 余數(shù) from scott.emp ;

結(jié)果：

--power(n1,n2)：返回n1的n2次方根

select empno,power(empno,2) as 方根 from scott.emp ;

--round和trunc：按照指定的精度進行舍入

select round(41.5),round(-41.8),trunc(41.6),trunc(-41.9) from scott.emp ;

--sign：取數(shù)字n的符號,大于0返回1,小于0返回-1,等于0返回0

select sign(45),sign(-21),sign(0) from scott.emp ;

結(jié)果：

select * from scott.emp;

oracle相關的數(shù)據(jù)庫SQL查詢語句：

1.  在職員表中查詢出基本工資比平均基本工資高的職工編號。

2.  查詢一個或者多個部門的所有員工信息，該部門的所有員工工資都高于公司的平均工資。

3.  現(xiàn)有張三的出生日期：1985-01-15 01:27:36，請各自新建表，將此日期時間插入表中，并計算出張三的年齡，顯示張三的生日。

4.  生日的輸出格式要求為MM-DD（未滿兩位的用0不全），張三的生日為01-15。

5.  算年齡要求用三個方式實現(xiàn)。

6.  生日要求用兩個方式實現(xiàn)。

7.  在數(shù)據(jù)庫表中有以下字符數(shù)據(jù)，如：

13-1,14-2,13-15，13-2，13-108，13-3，13-10,13-200,13-18,100-11,14-1

現(xiàn)在希望通過一條SQL語句進行排序，并且首先要按照前半部分的數(shù)字進行排序，然后再按照后半部分的數(shù)字進行排序，輸出要拍成如下所示：
13-1,13-2,13-3,13-10,13-15,13-18,13-108,13-200,14-1,14-2,100-11

數(shù)據(jù)庫表名：SellRecord;字段ListNumber;

8.  顯示所有雇員的姓名以及滿10年服務年限后的日期。

9.  顯示雇員姓名，根據(jù)其服務年限，將最老的雇員排在最前面。

10顯示所有雇員的姓名和加入公司的年份和月份，按雇員受雇日期所在月排序，將最早年份的職員排在最前面。

10.             顯示假設一個月為30天的情況下所有雇員的日薪金。

11.             找出在（任何年份的）2月受聘的所有雇員（用兩種方式實現(xiàn)）。

12.             對于每個雇員，顯示其加入公司的天數(shù)。

13.             以年，月和日的方式顯示所有雇員的服務年限（入職多少年/入職了多少月/入職了多少天）。

14.             找出各月最后一天受雇的所有雇員。

15.             找出早于25年之前受雇的雇員（用兩種方式實現(xiàn)）。

16.             工資最低1500的職員增加10%，1500以上的增加5%的工資，用一條update語句實現(xiàn)（用兩種方式實現(xiàn)）。

17.             按照部門統(tǒng)計每種崗位的平均工資，要求輸出的格式如下圖所示：

18.

19.

20.

21,。

22.

本人聲明：以上內(nèi)容出現(xiàn)任何錯誤與不足，皆與本人無關。