玩转oracle 实战教程oracle表查询3.docx
《玩转oracle 实战教程oracle表查询3.docx》由会员分享，可在线阅读，更多相关《玩转oracle 实战教程oracle表查询3.docx（15页珍藏版）》请在冰豆网上搜索。
玩转oracle实战教程oracle表查询3
（玩转oracle实战教程）oracle表查询（3）
分页查询
按雇员的id号升序取出
oracle的分页一共有三种方式
1.根据rowid来分
select*fromt_xiaoxiwhererowidin（selectridfrom（selectrownumrn,ridfrom（selectrowidrid,cidfromt_xiaoxiorderbyciddesc）whererownum<10000）wherern>9980）orderbyciddesc;
执行时间0.03秒
2.按分析函数来分
select*from（selectt.*,row_number（）over（orderbyciddesc）rkfromt_xiaoxit）whererk<10000andrk>9980;
执行时间1.01秒
3.按rownum来分
select*from（selectt.*,rownumrnfrom（select*fromt_xiaoxiorderbyciddesc）twhererownum<10000）wherern>9980;
执行时间0.1秒
其中t_xiaoxi为表名称，cid为表的关键字段，取按cid降序排序后的第9981-9999条记录，t_xiaoxi表有70000多条记录。
个人感觉1的效率最好，3次之，2最差。
//测试通过的分页查询okokok
select*from（selecta1.*,rownumrnfrom（selectename,jobfromemp）a1whererownum<=10）wherern>=5;
下面最主要介绍第三种：
按rownum来分
1.rownum分页
SELECT*FROMemp;
2.显示rownum[oracle分配的]
SELECTe.*,ROWNUMrnFROM（SELECT*FROMemp）e;
rn相当于Oracle分配的行的ID号
3.挑选出6—10条记录
先查出1-10条记录
SELECTe.*,ROWNUMrnFROM（SELECT*FROMemp）eWHEREROWNUM<=10;
如果后面加上rownum>=6是不行的，
4.然后查出6-10条记录
SELECT*FROM（SELECTe.*,ROWNUMrnFROM（SELECT*FROMemp）eWHEREROWNUM<=10）WHERErn>=6;
5.几个查询变化
a.指定查询列，只需要修改最里层的子查询
只查询雇员的编号和工资
SELECT*FROM（SELECTe.*,ROWNUMrnFROM（SELECTename,salFROMemp）eWHEREROWNUM<=10）WHERErn>=6;
b.排序查询，只需要修改最里层的子查询
工资排序后查询6-10条数据
SELECT*FROM（SELECTe.*,ROWNUMrnFROM（SELECTename,salFROMempORDERbysal）eWHEREROWNUM<=10）WHERErn>=6;
用查询结果创建新表
这个命令是一种快捷的建表方式
CREATETABLEmytable（id,name,sal,job,deptno）asSELECTempno,ename,sal,job,deptnoFROMemp;
创建好之后，descmytable；和select*frommytable;看看结果如何？
合并查询
合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，unionall，intersect，minus
多用于数据量比较大的数据局库，运行速度快。
1）.union
该操作符用于取得两个结果集的并集。
当使用该操作符时，会自动去掉结果集中重复行。
SELECTename,sal,jobFROMempWHEREsal>2500
UNION
SELECTename,sal,jobFROMempWHEREjob='MANAGER';
2）.unionall
该操作符与union相似，但是它不会取消重复行，而且不会排序。
SELECTename,sal,jobFROMempWHEREsal>2500
UNIONALL
SELECTename,sal,jobFROMempWHEREjob='MANAGER';
该操作符用于取得两个结果集的并集。
当使用该操作符时，会自动去掉结果集中重复行。
3）.intersect
使用该操作符用于取得两个结果集的交集。
SELECTename,sal,jobFROMempWHEREsal>2500
INTERSECT
SELECTename,sal,jobFROMempWHEREjob='MANAGER';
4）.minus
使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。
SELECTename,sal,jobFROMempWHEREsal>2500
MINUS
SELECTename,sal,jobFROMempWHEREjob='MANAGER';
（MINUS就是减法的意思）
#登录到sqlplus;
#用户名:
scott密码:
tiger
#或者在命令提示符下
sqlplussys/dysassysdba; #管理员登录到sqlplus
alteruserscottaccountunlock; #解开用户
#清空sqlplus屏幕中的记录
clearscr;
#查看表结构
descemp;
#select语句
selectename,sal*12fromemp;
descemp;
#表字段的别名
selectename,sal*12asannual_salfromemp;
#在表中运用运算
select2*3fromemp;#查出来的是14条记录,因为emp表中有14条记录
#查看虚表dual
descdual;
#在dual表中运算
select2*3fromdual;
#查看系统时间
selectsysdatefromdual; #显示一条记录
#使用""来保持原来的格式
selectename,sal*12as"annualsal"fromemp;
#查看表中的字段值为空的记录
selectename,commfromemp;#因为任何含有空值的表达式最后显示结果仍是空值
#字符串连接,使用||
selectename||salfromemp;
#distinct查询,去除重复行
selectdistinctdeptnofromemp;
selectdistinctdeptno,jobfromemp; #两个在一起的组合重复的去掉,distinct可以修饰一个或多个字段
#where过滤条件
#查询部门编号是10的员工信息
select*fromempwheredeptno=10;
#查询名称是CLARKR部门员工信息
select*fromempwhereename='CLARK';
selectename,salfromempwheresal>1500;
#比较每个字符的ascii码谁大谁小
selectename,salfromempwhereename>'CBA';
#查询工资在800和1500之间
selectename,salfromempwheresalbetween800and1500;
#或者
selectename,salfromempwheresal>=800andsal<=1500;
#空值处理
selectename,sal,commfromempwherecommisnull;
selectename,sal,commfromempwherecomm=null;#这样写是错误的
selectename,sal,commfromempwherecommisnotnull;
#in查询
selectename,sal,commfromempwheresalin（800,1500,2000）;
selectename,sal,commfromempwhereenamein（'SMITH','KING','ABC'）;#表示查询符合在集合范围内的一个或者多个
selectename,salfromempwheresalnotin（800,1000）;
#日期处理
selectename,sal,hiredatefromempwherehiredate>'20-2月-81';
#或者
selectename,sal,hiredatefromempwherehiredate>'20-2月-1981';
#and处理,or处理
selectename,salfromempwheredeptno=10andsal>1000;
selectename,salfromempwheredeptno=10orsal>1000;
#模糊查询
selectenamefromempwhereenamelike'%ALL%'; #%百分号表示1个或者多个
#查出名称第二个是A的
selectenamefromempwhereenamelike'_A%';
#如果是%,用转义字符
selectenamefromempwhereenamelike'%/%%';
#用escape识别转义字符
selectenamefromempwhereenamelike'%$%%'escape'$';
#按部门编号降序排列
select*fromdeptorderbydeptnodesc;
#按empno升序排列
selectempno,enamefromemporderbyempnoasc; #默认是升序排列
#过滤后再排序
selectempno,enamefromempwheredeptno<>10orderbyempnoasc;
selectename,sal,deptnofromemporderbydeptnoasc;
#排序两个条件
selectename,sal,deptnofromemporderbydeptnoasc,sal;
#综合排序条件
selectename,sal*12annual_salfromempwhereenamenotlike'_A%'andsal
800orderbydeptno;
#SQL函数
selectlower（ename）fromemp;#转换成小写
#第二个字母是a的
selectenamefromempwherelower（ename）like'_a%';
#或者
selectenamefromempwhereenamelike'_a%'orenamelike'_A%';
#截取字符串
selectsubstr（ename,2,3）fromemp;#从第二个字符串开始,截取3个字符串
selectascii（'A'）fromdual; #查出ascii码
selectround（23.652）fromdual; #四舍五入
#四舍五入,保留小数点两位
selectround（23.652,2）fromdual;
selectround（23.652,1）fromdual;
selectround（23.652,-1）fromdual;
#把数字或者日期转换为某种形式
selectto_char（sal,'$99,999.9999'）fromemp; #9代表一位数字
#人民币
selectto_char（sal,'L99,999.9999'）fromemp;
#对日期进行转换
selecthiredatefromemp;
selectto_char（hiredate,'YYYY-MM-DDHH:
MI:
SS'）fromemp;
selectto_char（sysdate,'YYYY-MM-DD'）fromdual;
selectename,hiredatefromempwherehiredate>to_date（'1981-2-2012:
34:
36
','YYYY-MM-DDHH24:
Mi:
SS'）;
selectsalfromempwheresal>to_number（'$1,250.00','$9,999.99'）;
#计算年薪,如果comm是为null的话,则让其显示0
selectename,sal*12+nvl（comm,0）fromemp;
#max函数
selectmax（sal）fromemp;
#min函数
selectmin（sal）fromemp;
#avg函数
selectavg（sal）fromemp;
#avg函数,将其转换成to_char形式
selectto_char（avg（sal）,'99,999.9999'）fromemp;
#保留两位小数
selectround（avg（sal）,2）fromemp;
selectsum（sal）fromemp;
selectcount（*）fromemp;
selectcount（*）fromempwheredeptno=10;
selectcount（ename）fromemp; #14条记录
selectcount（distinct（deptno））fromemp;#3条记录
#groupby语句
selectavg（sal）fromempgroupbydeptno;
selectmax（sal）fromempgroupbydeptno,job;
selectenamefromempwheresal=（selectmax（sal）fromemp）;
selectdeptno,max（sal）fromempgroupbydeptno;
#having子句,使用having对分组进行限制
selectavg（sal）,deptnofromempgroupbydeptno;
selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）>2000;
selectavg（sal）fromempwheresal>1200groupbydeptnohavingavg（sal）>15
00orderbyavg（sal）desc;
#子查询
selectename,salfromempwheresal=（selectmax（sal）fromemp）;
selectename,salfromempwheresal>（selectavg（sal）fromemp）;
#下面这么写法是错误的
selectename,sal,deptnofromempwheresal=（selectmax（sal）fromempgroupby
deptno）; #第1行出现错误:
#RA-01427:
单行子查询返回多个行
selectename,salfromempjoin（selectmax（sal）max_sal,deptnofromempgr
oupbydeptno）ton（emp.sal=t.max_salandemp.deptno=t.deptno）;
#理解子查询的关键是把其当成一张表
#求出每个员工的上司是谁
selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;
selectename,dname,gradefromempe,deptd,salgradeswheree.deptno=
d.deptnoande.salbetweens.losalands.hisalandjob<>'CLEAK';
#没有表连接,默认是笛卡儿积
selectename,dnamefromemp,dept;
#或者
selectename,dnamefromempcrossjoindept;
#有连接条件
selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno;
#或者
selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）;
selectename,dnamefromempjoindeptusing（deptno）; #使用using的条件是两个表中有相同的字段
#1.PL/SQL
#PL/SQL是Oracle内部使用的编程语言,ProcedureLanguage过程语言,其语言格式比较固定
begin
dbms_output.put_line（'HelloWorld'）;
end;
/ #/表示执行
setserveroutputon; #执行输出命令,默认是off（关闭）
declare #声明变量
v_namevarchar2（20）;
begin
v_name:
='myname'; #给变量赋值
dbms_output.putline（v_name）;
end;
/
#打印:
myname,输出myname
#PL/SQL过程已成功完成
declare
v_namenumber:
=0;
begin
v_name:
=2/v_name;
dbms_output.put_line（v_name）;
exception
whenothersthen
dbms_output.put_line（'error'）;
end;
/
#去掉exception语句块
declare
v_namenumber:
=0;
begin
v_name:
=2/v_name;
dbms_output.put_line（v_name）;
end;
/
#则会出现如下错误:
除数为0在line4
#2.变量声明
#变量名不能使用保留字,如from,select等
#第一个字符必须是字母
#变量名最多包含30个字符
#不要与数据库的表或者列同名
#每一行只能声明一个变量
#3.常用变量类型
#binary_integer:
整数,主要用来计数而不是用来表示字段类型
#number:
数字类型
#char:
定长字符串
#varchar2:
变长字符串
#date:
日期
#long:
长字符串,最长2GB
#boolean:
布尔类型,可以取值为true,false和null值.
declare
v_tempnumber
（1）;
v_countbinary_integer:
=0; --binary_integer是用来记数的,效率比较高一点.
v_salnumber（7,2）:
=4000.00;
v_datedate:
=sysdate;
v_piconstantnumber（3,2）:
=3.14;
v_validboolean:
=false;
v_namevarchar2（20）notnull:
='MyName';
begin
dbms_output.put_line（'v_tempvalue:
'||v_date）;
end;
#4.变量声明,使用%type属性
declare
v_empnonumber（4）;
v_empno2emp.empno%type; --表示emp表中empno的类型
v_empno3v_empno2%type;
begin
dbms_output.put_line（'Test'）;
end;
--Table变量类型
declare
typetype_table_emp_empnoistableofemp.empno%typeindexbybinary_integer;
v_empnostype_table_emp_empno;
begin
v_empnos（0）:
=7369;
v_empnos
（2）:
=7839;
v_empnos（-1）:
=9999;
升级会员 