五 Oracle查询【重点】
单表查询
伪列 rowid rownum
rowid 表示记录的物理地址 用rowid查询比主键Id查询更快。直接定位物理地址。
rownum 用于分页
五个分组函数 count sum avg max min
连接查询
oracle的左外连接特殊写法 在oracle中外连接一般用oracle的特殊写法。
select
from a , b
where a.id = b.id(+) 与 from 后面的a 和 b 没有关系,主要看where中的 = 。
左外连接: 以左边的表为基准,右边表能匹配上的进行显示,不能匹配上的补NULL。
子查询 从内向外看
三种子查询
select 后面 select后面的子查询 select xxx from tab 只能有一个字段。 因为select后面的子查询相当于替换select查询中一个字段。
from 后面
where 后面
where后面的子查询用exits 替换 in:
exits的用法扩展:
select * from T_OWNERS t
where exists (select id,name from t_address ad where ad.id = t.addressid and ad.name like '%花园%');
当子查询中前后表用到了关联,则只查询出满足关联条件的前表中的记录。
select * from T_OWNERS t where exists (select id ,name from t_address ad );
当子查询中前后表没有关联关系,则根据后表中是否有记录返回,来查询前表中的所有记录。
分页查询rownum 不能使用 > ; >= ;=
原因: rownum在表扫描时产生,每次从1开始计数。
所以当扫描到第一行时,rownum>10 不满足,会将记录丢失,一直扫描下去都会丢掉记录。所以最后查询不出记录。
带排序的分页查询。 先查询出来数据 最后再加rownum进行排序 eg:
select * from
(select rownum r,t.* from (select * from T_ACCOUNT order by usenum desc) t where rownum<=20 )
where r>10
单行函数
字符函数
substr(从什么位置开始截取,截取的长度) 位置从1开始。有点类似于el表达式中的fn:substr
需要区分java中substring( begin,end) 从begin开始 到end结束 不包括end 位置从0开始。
|| 字符串拼接
length(str) 字符串长度
数值函数
round() 四舍五入
日期函数
add_months(sysdate,+/- 1) 加减月份
last_day(sysdate) 所在月最后一天
trunc(sysdate) 截取日期 去掉小时分钟秒
trunc(sysdate,'yyyy') 2018-01-01
trunc(sysdate,'mm') 2018-06-01
转换函数
to_char
to_date
null判断
nvl(maxnum,9999); 当maxnum为null时,显示为9999. 当要显示字符串时,将不适用。则可以使用nvl2.
nvl2(maxnum ,to_char(maxnum),‘不限’) 将maxnum类型转换为字符串。
条件取值
case when then sql标准的写法
case when xx =1 then xxx when xx =2 then yyy else zzzz end
decode 是oracle 特有的。 相当于java中的switch分支判断
decode(比较值,条件1,值1,条件2,值2 ,缺省值)
行列转换
case when xxx then xxx else end
分析函数 三种排名方式
rank() over( order by xxx ) 值相同,排名相同,排名跳跃
dense_rank() over() 值相同,排名相同,排名不跳跃
row_number() over() 无论值是否相同,按顺序排名
row_number() over() 可以用来实现分页。
eg:
select * from
(select row_number() over(order by usenum desc ) rownumber,usenum from T_ACCOUNT)
where rownumber>10 and rownumber<=20
集合运算
并集 union 不包含重复记录 / union all 包含重复记录
交集 intersect 取相同的记录
差集 minus 相当于a-b
六 视图 简化开发
虚拟表 封装sql
简单视图
语法:
create 【or replace】【force】 view viewname as --force表示允许创建带错误的视图,如果sql中的表不存在,也可以创建成功。
sql语句
【with check option】 --表示该视图不能修改sql语句中的条件字段
【with read only 】 --表示该视图只能读 不能修改
eg:
create or replace view v_address as
select * from address where areaid =2
with check option ;
update v_address set areaid = 3 。这条update语句不能执行,因为修改了sql中的条件字段areadid
创建增量刷新物化视图需要先创建物化视图日志
create materialized view log on t_address with rowid;
八 序列
简单序列 create sequence xxxx nextval currval
复杂序列 create sequence xxx increment by x start with x minvalue x maxvalue y cycle cache
循环索引时计算公式:
ceil( (maxvlue-minvalue)/increment by ) >= cache cache默认是20
十 索引 提高查询性能
简单索引 create index ind_xxxx on 表名(列名);
唯一索引
create unique inde inx_yyyy on 表名(列名)
构建索引的列唯一不重复时
复合索引
create index owners_index_ah on T_OWNERS(addressid,housenumber);
在sql查询语句中使用时,要注意 addressid 和 housenumber 的顺序要与索引一致。
反向键索引
create index ind_xxxx on 表名(列名) reverse;
当要构建索引的列是连续值使用。不使用反向键则构建歪脖子树,效率低。
位图索引 create bitmap index ind_xxxx on 表名(列名);
适合创建在低基数列上。比如性别,民族。
十一 PLSQL
变量申明
三种方式 v_name varchar2(30) ; v_name t_address.name%type; v_address t_address%type;
变量赋值
三种方式 v_name:='xxx' ; select name into v_name from t_address where id=1; select * into v_address from t_address where id=1;
需要注意 select into 只能是一行记录。
异常
exception
when 异常类型 then
xxx;
分支
第一种:
if 条件 then
xxx;
end if;
第二种:
if 条件 then
xxx;
else
yyy;
end if;
第三种:
if 条件 then
xxx;
elsif 条件 then
yyy;
else
zzz;
end if;
循环
不带条件循环
loop
xxxx;
exit when 条件;
end loop;
带条件循环
while 条件
loop
xxxx;
end loop;
for循环
for 变量 in 起始值 .. 截止值
loop
xxxx;
end loop;
游标
不带参数游标
declare
cursor 游标名称 is sql查询语句;
begin
open 游标名称;
loop
fetch 游标名称 into 变量; //提取游标
exit when 游标名称%notfound;
xxxx;
end loop;
close 游标名称;
end;
带参数游标
declare
cursor 游标名称(参数名称 参数类型) is sql查询语句; --sql语句中可以直接使用参数
begin
open 游标名称(参数名称); --open操作时才查询数据库
loop
fetch 游标名称 into 变量; //提取游标
exit when 游标名称%notfound;
xxxx;
end loop;
close 游标名称;
end;
for循环简写游标
declare
cursor 游标名称 is sql查询语句;
begin
for xxx in 游标名称
loop
xxxx;
end loop;
end;
十二 存储函数
有return返回值 一般在sql中调用
create or replace function fn_xxxx(参数名称 参数类型) -- 参数类型不用写长度
return varchar2 --返回值类型也不用写长度
is
变量申明;
begin
xxxx;
return xxx;
end;
十三 存储过程
无return返回值 但有out类型参数 并可以有多个。 一般通过程序调用 callablestatement { call p_xxxxx(?,?) }
create or replace procedure p_xxxx (参数名称 参数类型 ) --参数类型不用写长度
is
变量申明;
begin
xxxx; --注意dml操作后需要commit;
end;
十四 触发器
前置/后置; 行级/语句级 通常写的后置行级触发器
前置表示操作执行前触发器先执行;后置表示操作执行完成后触发器执行;
行级表示触发器作用于每一行;语句级表示触发器作用于一块语句;
create or replace trigger tri_xxxx
after
update of name on t_owners
for each row
declare
xxx;
begin
yyy;
end;
针对行级触发器 有:new 和 :old