Oracle做的练习.docx
- 文档编号:26144661
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:14
- 大小:29.40KB
Oracle做的练习.docx
《Oracle做的练习.docx》由会员分享,可在线阅读,更多相关《Oracle做的练习.docx(14页珍藏版)》请在冰豆网上搜索。
Oracle做的练习
1.分别用case和decode函数列出员工所在的部门,deptno=10显示部门10',
deptno=20显示'部门20'
deptno=30显示'部门30'
deptno=40显示'部门40'
否则为'其他部门'
selectt.ename,
caset.deptnowhen10then'部门10'
when20then'部门20'
when30then'部门30'
else'其他'end部门
fromempt;
selectt.ename,
decode(t.deptno,10,'部门10',20,'部门20',30,'部门30','其他')部门
fromempt
2.得到工资大于自己部门平均工资的员工信息
selectt1.ename,t1.job,t1.sal
fromempt1,
(selectt.deptno,avg(sal)avgsalfromemptgroupbyt.deptno)t2
wheret1.deptno=t2.deptno
andt1.sal>t2.avgsal
3,验证各种关联
createtablea1(col1number
(2),col2varchar2
(2))
createtableb1(col3number
(2),col4varchar2
(2))
insertintoa1(col1,col2)values(1,'a');
insertintoa1(col1,col2)values(2,'b');
insertintoa1(col1,col2)values(3,'c');
insertintoa1(col1,col2)values(4,'d');
insertintoa1(col1,col2)values(5,'e');
insertintob1(col3,col4)values(1,'f');
insertintob1(col3,col4)values(2,'g');
insertintob1(col3,col4)values(3,'h');
等值连接
selectcol1,col2,col3,col4froma1,b1wherea1.col1=b1.col3;
左连接
select*froma1leftouterjoinb1oncol1=col3;
select*froma1,b1wherea1.col1=b1.col3(+);
右连接
select*froma1rightouterjoinb1oncol1=col3;
select*froma1,b1wherea1.col1(+)=b1.col3;
全连接
select*froma1fullouterjoinb1oncol1=col3;
select*froma1,b1wherea1.col1=b1.col3(+)
union
select*froma1,b1wherea1.col1(+)=b1.col3;
4.oracle之any、some、all解析
因为很少用到,所以几乎忘记了这几个函数,不过它们还是很有用的使用它们可以大大简化一些SQL文的语法,至于效率问题,
如CCW所说它们和EXISTS,IN之类没有什么差别,而且要具体问题具体分析
其中ANY和SOME在意思上是相同的,
可以相互替代.
举几个例子来说明ALL和ANY的用法
1.SELECT*FROMTABLEAWHEREFLD>ALL(SELECTFLDFROMTABLEA)
这相当于
SELECT*FROMTABLEAWHEREFLD>(SELECTMAX(FLD)FROMTABLEA)
2.SELECT*FROMTABLEAWHEREFLD>ANY(SELECTFLDFROMTABLEA)
这相当于
SELECT*FROMTABLEAWHEREFLD>(SELECTMIN(FLD)FROMTABLEA)
3.SELECT*FROMTABLEAWHEREFLD=ANY(SELECTFLDFROMTABLEA)
这相当于
SELECT*FROMTABLEAWHEREFLDIN(SELECTFLDFROMTABLEA)
5.行转列(max(decode()))
教师号星期号是否有课
12有
13有
21有
32有`
12有
写一条sql语句让你变为这样的表
教师号星期一星期二星期三
121
21
31
各星期下的数字表示:
对应的教师在星期几已经排的课数
createtablea1(col1number
(2),col2number
(2),col3varchar2(5))
insertintoa1(col1,col2,col3)values(1,2,'有');
insertintoa1(col1,col2,col3)values(1,3,'有');
insertintoa1(col1,col2,col3)values(2,1,'有');
insertintoa1(col1,col2,col3)values(3,2,'有');
insertintoa1(col1,col2,col3)values(1,2,'有');
selectcol1教师号,sum(decode(col2,1,decode(col3,'有',1,null),null))星期一,
sum(decode(col2,2,decode(col3,'有',1,null),null))星期二,
sum(decode(col2,3,decode(col3,'有',1,null),null))星期三
froma1groupbycol1
行转列例子:
DateExample:
110001,MB_MD,MBID000001
210001,TVE_MD,TVEID000001
310001,MODEM_MD,MODEMID000001
410002,MB_MD,MBID000002
510002,TVE_MD,TVEID000002
610002,MODEM_MD,MODEMID000002
710003,MB_MD,MBID000003
810003,TVE_MD,TVEID000003
910003,MODEM_MD,MODEMID000003
==========================================
SQL:
selectserial_number,max(decode(group_name,'MB_MD',lot_id,''))MB_MD,
max(decode(group_name,'TVE_MD',lot_id,''))TVE_MD,
max(decode(group_name,'MODEM_MD',lot_id,''))MODEM_MD
frommaterial
groupbyserial_number
==========================================
結果:
serial_numberMB_MDTVE_MDMODEM_MD
10001,MBID000001,TVEID000001,MODEMID000001
10002,MBID000002,TVEID000002,MODEMID000002
10003,MBID000003,TVEID000003,MODEMID000003
6.in和exists区别
对于in和exists的性能区别:
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了另外IN是不对NULL进行处理
如:
select1fromdualwherenullin(0,1,2,null)
为空
7.列出员工表中每个部门的员工数(员工数必须大于3),和部门名称
selectt1.dname,t2.numb
fromdeptt1,
(selectt.deptno,count(*)numb
fromempt
groupbyt.deptno
havingcount(*)>3)t2
wheret1.deptno=t2.deptno
8.查找出不在部门20,且比部门20中任何一个人工资都高的员工姓名、部门名称
selecta.ename,b.dname
fromempa,deptb
wherea.deptno!
='20'
anda.sal>(selectmax(sal)fromemptwheret.deptno='20')
anda.deptno=b.deptno
9.查找出部门10和部门20中,工资最高第3名到工资第5名的员工的员工名字,部门名字,部门位置
selecta.dname,b.ename,a.loc
fromdepta,
(selectrownumnumb,c.*from(select*
fromempt
wheret.deptno=10
ort.deptno=20
orderbysaldesc)c)b
wherea.deptno=b.deptnoand(b.numb>=3andb.numb<=5)
10.按部门统计员工数,查处员工数最多的部门的第二名到第五名(列出部门名字,部门位置)
selectb.dname,b.loc
fromdeptb,
(selectrownumnumb,a.*
from(selectcount(*)sl,t.deptno
fromempt
groupbyt.deptno
orderbycount(*)desc)a)c
whereb.deptno=c.deptnoandc.numbbetween2and5
11.查询出king所在部门的工作年限最大的员工名字
selectename,hiredate
fromemp
wherehiredatein
(selectmin(hiredate)
fromemp
wheredeptnoin(selectdeptnofromempwhereename='KING'));
12.随机返回5条记录
Select*from(selectename,jobfromemporderbydbms_random.value())whererownum<=5
13.处理空值排序
select*fromemporderbycommdescnullslast;
select*fromemporderbycommdescnullsfirst;
14.查询跳过表中的偶数行
selectenamefrom(selectrow_number()over(orderbyename)rn,enamefromemp)xwheremod(rn,2)=1;
selectenamefrom(selectrownumrn,enamefromemporderbyename)xwheremod(rn,2)=1;
15.查询员工信息与其中工资最高最低员工
selectename,sal,max(sal)over(),min(sal)over()fromemp;
16.连续求和
selectename,sal,sum(sal)over(),sum(sal)over(orderbyename)fromemp;
sum(sal)over(orderbyename)指的是连续求和.是以ename来排序的。
若有两个这样的窗口函数,以后面的排序为主。
17.分部门连续求和
selectdeptno,sal,sum(sal)over(partitionbydeptnoorderbyename)assfromemp;分部门连续求和
sum(sal)over(partitionbydeptno)分部门求和
18.得到当前行上一行或者下一行的数据
selectename,sal,lead(sal)over(orderbysal)aaa,lag(sal)over(orderbysal)bbbfromemp;
monthpersonincome
月份人员收入
要求用一个SQL语句,统计每个月及上月和下月的总收入要求列表输出为
月份当月收入上月收入下月收入
selectmonth,sum(income),lead(sum(income))over(orderbysum(income)),lag(sum(income))over(orderbysum(income))fromtablegroupbymonth;
19.确定一年内的天数
selectadd_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')fromdual;
20.查询EMP员工表下每个部门工资前二名的员工信息
selectdeptno,ename,sal
fromempe1
where
(selectcount
(1)
fromempe2
wheree2.deptno=e1.deptnoande2.ename!
=e1.enameande2.sal>e1.sal)
<2
orderbydeptno,saldesc;
---------------------------------------
select*from
(selectdeptno,ename,sal,row_number()over(partitionbydeptno
orderbysaldesc)rn
fromemp)
wherern<3;
21.SQL优化
删除一张表的重复记录(ID是自增唯一主键,重复记录:
其他字段都是一样)
(数据量很大,性能要求很高)
表名:
T
Idnameage
1louis20
2louis20
3jimmy30
4louis20
做法一:
Deletefromtwhereidnotin(Selectmin(id)fromtGroupbyname,age);
做法二:
deletefromtwhereidin(Selectdistincta2.idfromta1,ta2wherea1.id>a2.idanda1.name=a2.nameanda1.age=a2.age);
做法三:
deletefromta1wherenotexists(select*
fromta2
wherea1.id>a2.idanda1.name=a2.nameanda1.age=a2.age
);
以上三种做法,均可。
但是第三种做法的性能最佳。
第一种用notin没办法用到索引.第三种方式也不会用到索引
数据量1000100000100,0000
方法一0.0473.7772
方法二0.2865.7765
第二种方式快于第一种方式。
SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,执行过程中访问尽量少的数据块,减少表扫描的I/O次数,尽量避免全表扫描和其他额外开销。
1、尽量少用IN操作符
基本上所有的IN操作符都可以用EXISTS代替,在选择IN或EXIST操作时,要根据主子表数据量大小来具体考虑
2、尽量用NOTEXISTS或者外连接替代NOTIN操作符
因为NOTIN不能应用表的索引
3、尽量不用“<>”或者“!
=”操作符
不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。
比如:
a<>0改为a>0ora<0
4、在设计表时,把索引列设置为NOTNULL
判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。
5、尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
当通配符“%”或者“_”作为查询字符串的第一个字符时,索引不会被使用。
比如用T表中Column1LIKE„%5400%‟这个条件会产生全表扫描,如果改成Column1‟X5400%‟ORColumn1LIKE‟B5400%‟则会利用Column1的索引进行两个范围的查询,性能肯定大大提高。
6、Where子句中避免在索引列上使用计算
如果索引不是基于函数的,那么当在Where子句中对索引列使用函数时,索引不再起作用。
因此Where子句中避免在索引列上使用计算。
比如:
substr(no,1,4)=‟5400‟,优化处理:
nolike„5400%‟trunc(hiredate)=trunc(sysdate),优化处理:
hiredate>=trunc(sysdate)andhiredate 7、用“>=”替代“>” 大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。 那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引 8、利用SGA共享池,避开parse阶段 同一功能同一性能不同写法SQL的影响如一个SQL在A程序员写的为Select*fromzl_yhjbqkB程序员写的为Select*fromdlyx.zl_yhjbqk(带表所有者的前缀)C程序员写的为Select*fromDLYX.ZLYHJBQK(大写表名)D程序员写的为Select*fromDLYX.ZLYHJBQK(中间多了空格)以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。 不同区域出现的相同的Sql语句要保证查询字符完全相同,建议经常使用变量来代替常量,以尽量使用重复sql代码,以利用SGA共享池,避开parse阶段,防止相同的Sql语句被多次分析,提高执行速度。 因此使用存储过程,是一种很有效的提高sharepool共享率,跳过parse阶段,提高效率的办法。 9、WHERE后面的条件顺序要求 WHERE后面的条件,表连接语句写在最前,可以过滤掉最大数量记录的条件居后。 比如: Select*fromzl_yhjbqkwheredy_dj='1KV以下'andxh_bz=1Select*fromzl_yhjbqkwherexh_bz=1anddy_dj='1KV以下'以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj='1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。 10、使用表的别名,并将之作为每列的前缀 当在Sql语句中连接多个表时,使用表的别名,并将之作为每列的前缀。 这样可以减少解析时间 11、进行了显式或隐式的运算的字段不能进行索引 比如: ss_df+20>50,优化处理: ss_df>30„X‟||hbs_bh>‟X5400021452‟,优化处理: hbs_bh>‟5400021542‟sk_rq+5=sysdate,优化处理: sk_rq=sysdate-5hbs_bh=5401002554,优化处理: hbs_bh=‟5401002554‟,注: 此条件对hbs_bh进行隐式的to_number转换,因为hbs_bh字段是字符型。 12、用UNIONALL代替UNION UNION是最常用的集操作,使多个记录集联结成为单个集,对返回的数据行有唯一性要求,所以oracle就需要进行SORTUNIQUE操作(与使用distinct时操作类似),如果结果集又比较大,则操作会比较慢;UNIONALL操作不排除重复记录行,所以会快很多,如果数据本身重复行存在可能性较小时,用unionall会比用union效率高很多! 13、其他操作 尽量使用packages: Packages在第一次调用时能将整个包load进内存,对提高性能有帮助。 尽量使用cachedsequences来生成primarykey: 提高主键生成速度和使用性能。 很好地利用空间: 如用VARCHAR2数据类型代替CHAR等 使用Sql优化工具: sqlexpert;toad;explain-table;PL/SQL;OEM 14、通过改变oracle的SGA的大小
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 练习