oracle 常用数据库

1.模糊查询
(1).含and
select * from t_owners where name like ‘%刘%’ and housenumber like ‘%5%’
(1).含or
select * from t_owners where name like ‘%刘%’ or housenumber like ‘%5%’
(1).混合使用(因为 and 的优先级比 or 大，所以我们需要用 ( ) 来改变优先级。)
select * from t_owners where (name like ‘%刘%’ or housenumber like ‘%5%’) and addressid=3
2.范围查询
(1).方式一
select * from T_ACCOUNT
where usenum between 10000 and 20000
(2).方式二
select * from T_ACCOUNT
where usenum between 10000 and 20000
3.空值查询
select * from T_PRICETABLE t where maxnum is null
4.去重查询
select distinct addressid from T_OWNERS
5.排序查询
(1).升序
select * from T_ACCOUNT order by usenum
(2).降序
select * from T_ACCOUNT order by usenum desc
6.基于伪列查询
(1).通过ROWID查询 – 自带的物理id
(1)-1.select rowID,t.* from T_AREA t
(1)-2.select rowID,t.* from T_AREA t where ROWID=‘AAAM1uAAGAAAAD8AAC’;
(2).通过ROWNUM查询 – 有序号的,与jsp中state.count相似
select rownum,t.* from T_OWNERTYPE t
7.聚合函数
(1).sum – 求和
select sum(usenum) from t_account
(2).avg – 求平均数
select avg(usenum) from T_ACCOUNT
(3).max – 求最大值
select max(usenum) from T_ACCOUNT where year=‘2012’
(4).min – 求最小值
select min(usenum) from T_ACCOUNT where year=‘2012’
(5).count – 统计个数
select count() from T_OWNERS t where ownertypeid=1
8.分组聚合
(1).分组 Group by
select areaid,sum(money) from t_account group by areaid
(2).分组后条件查询 having
select areaid,sum(money) from t_account group by areaid having sum(money)>169000
9.连接查询(多表)
(1).内连接
select * from A a,B b where a.aid=b.bid
select * from A a join B b on a.aid=b.bid
(2).左外连接
select * from A a left join B b on a.aid=b.bid – SQL1999标准
select * from A a,B b where a.aid=b.bid(+) – oracle特有写法,如果是左外连接,就在右表所在的条件一端填上（+）
(3).右外连接
select * from A a right join B b on a.aid=b.bid – SQL1999标准
select * from A a,B b where a.aid(+)=b.bid
10.子查询
(一).where语句中
(1).单行子查询(=,>,<,>=,<=,<>) – 结果是一条数据
select * from T_ACCOUNT where year=‘2012’ and month=‘01’ and usenum>
( select avg(usenum) from T_ACCOUNT where year=‘2012’ and month=‘01’ )
(2).多行子查询(in,any,all) – 结果是多条数据
select * from T_OWNERS where addressid in ( 1,3,4 )
select * from T_OWNERS where addressid not in ( 1,3,4 )
(二).from语句中 – 结果相当于一个表
select * from
(select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id)
where 业主类型=‘居民’
(三).select 子句中的子查询
(1).需求：列出业主信息，包括 ID，名称，所属地址。
select id,name,
(select name from t_address where id=addressid) addressname – id(是括号里面表的列)=addressid(是括号外面表的列)
from t_owners
(2).需求：列出业主信息，包括 ID，名称，所属地址(是t_address的列)，所属区域(是t_address间接关联的t_area中的列)。
select id,name,
( select name from t_address where id=addressid ) – 所属地址
addressname,
( select (select name from t_area where id=areaid ) from
t_address where id=addressid ) – 所属区域
adrename
from t_owners;
11.分页查询
(1).前10条
select rownum,t. from T_ACCOUNT t where rownum<=10
(2).11-20条
select * from
(select rownum r,t.* from
(select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 )
where r>10
12.数值函数
(1).四舍五入
select round(100.567,2) from dual
(2).数字截取
select trunc(100.567) from dual
(3).取模
select mod(10,3) from dual
13.日期函数
(1).查看当前系统时间
select sysdate from dual
(2).在当前系统时间上加两个月(-2就是减两个月)
select add_months(sysdate,2) from dual
(3).求所在月最后一天 LAST_DAY
select last_day(sysdate) from dual
select last_day(sysdate-4) from dual //sysdate-4是系统时间-4天,再求出上个月的最后一天
(4).日期截取
select TRUNC(sysdate) from dual – 按日截取
select TRUNC(sysdate,‘mm’) from dual – 按月截取
select TRUNC(sysdate,‘yyyy’) from dual – 按年截取
select TRUNC(sysdate,‘hh’) from dual – 按小时截取
select trunc(sysdate,‘mi’) from dual – 按分钟截取
14.转换函数
(1).数字转字符串
select TO_CHAR(1024)||‘分’ from dual
(2).日期转字符串(年月日)
select TO_CHAR(sysdate,‘yyyy-mm-dd’) from dual – 2018-04-24
select TO_CHAR(sysdate,‘yyyy’)||‘年’||TO_CHAR(sysdate,‘mm’)||‘月’ ||TO_CHAR(sysdate,‘dd’)||‘日’from dual --2018年4月24日
(3).日期转字符串(年月日时分秒)
select TO_CHAR(sysdate,‘yyyy-mm-dd hh:mi:ss’) from dual
(4).字符串转日期
select TO_DATE(‘2017-01-01’,‘yyyy-mm-dd’) from dual
(5).字符串转日期
select to_number(‘100’) from dual
自动运算
select ‘100’+0 from dual – 100数字
字符拼接
select ‘100’||0 from dual --1000
15.其他函数
(1).空值处理
select PRICE,MINNUM,NVL(MAXNUM,0) from T_PRICETABLE where OWNERTYPEID=1 – 把MAXNUM列中有null的都转成0
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , ‘不限’)from T_PRICETABLE where OWNERTYPEID=1 – 把MAXNUM列中有null的都转成’不限’,此时MAXNUM列这列都是字符串
(2).条件取值(oracle特有写法)
decode(条件,值 1,翻译值 1,值 2,翻译值 2,…值 n,翻译值 n,缺省值)
【功能】根据条件返回相应值
例:
select name,decode( ownertypeid,1,‘居民’,2,‘行政事业单位’,3,‘商业’) as 类型 from T_OWNERS
(3).条件取值(sql1999标准写法)
写法一:
select name ,(case ownertypeid
when 1 then ‘居民’
when 2 then ‘行政事业单位’
when 3 then ‘商业’
else ‘其它’
end
) from T_OWNERS
写法二:
select name,(case
when ownertypeid= 1 then ‘居民’
when ownertypeid= 2 then ‘行政事业’
when ownertypeid= 3 then ‘商业’
end
)from T_OWNERS
(4).字符函数
字符截取
select substr(‘abcsgwsag’,1,2) from dual
字符长度
select length(‘sbcv’) from dual
16.行列转换
(1).按月查询各地水费(月报表)
select (select name from T_AREA where id= areaid ) 区域,
sum( case when month=‘01’ then money else 0 end) 一月,
sum( case when month=‘02’ then money else 0 end) 二月,
sum( case when month=‘03’ then money else 0 end) 三月,
sum( case when month=‘04’ then money else 0 end) 四月,
sum( case when month=‘05’ then money else 0 end) 五月,
sum( case when month=‘06’ then money else 0 end) 六月,
sum( case when month=‘07’ then money else 0 end) 七月,
sum( case when month=‘08’ then money else 0 end) 八月,
sum( case when month=‘09’ then money else 0 end) 九月,
sum( case when month=‘10’ then money else 0 end) 十月,
sum( case when month=‘11’ then money else 0 end) 十一月,
sum( case when month=‘12’ then money else 0 end) 十二月
from T_ACCOUNT where year=‘2012’ group by areaid
(2).按季度查询各地水费(季度报表)
select (select name from T_AREA where id= areaid ) 区域,
sum( case when month>=‘01’ and month<=‘03’ then money else
0 end) 第一季度,
sum( case when month>=‘04’ and month<=‘06’ then money else
0 end) 第二季度,
sum( case when month>=‘07’ and month<=‘09’ then money else
0 end) 第三季度,
sum( case when month>=‘10’ and month<=‘12’ then money else
0 end) 第四季度
from T_ACCOUNT where year=‘2012’ group by areaid
17.分析函数
(1).排名方式一:rank 相同的值排名相同，排名跳跃
select rank() over(order by usenum desc ),usenum from T_ACCOUNT
(2).排名方式二:DENSE_RANK 相同的值排名相同，排名连续
select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT
(3).排名方式三:ROW_NUMBER 返回连续的排名，无论值是否相等
select row_number() over(order by usenum desc ),usenum from T_ACCOUNT
(4).用 row_number()分析函数实现的分页查询
select * from
(select row_number() over(order by usenum desc )
rownumber,usenum from T_ACCOUNT)
where rownumber>10 and rownumber<=20
18.集合运算
(1).并集(去掉重复记录)-- 1 2 3 4 5 6 7 8 9 10
select * from t_owners where id>5 – 6 7 8 9 10
union
select * from t_owners where id<8 – 1 2 3 4 5 6 7
(2).并集(包含重复记录)-- 6 7 8 9 10 1 2 3 4 5 6 7
select * from t_owners where id>5 – 6 7 8 9 10
union all
select * from t_owners where id<8 – 1 2 3 4 5 6 7
(3).交集 – 6 7
select * from t_owners where id>5 – 6 7 8 9 10
INTERSECT
select * from t_owners where id<8 – 1 2 3 4 5 6 7
(4).差集 – 8 9 10
select * from t_owners where id>5 – 6 7 8 9 10
MINUS
select * from t_owners where id<8 – 1 2 3 4 5 6 7
(5).差集分页
select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10

19.视图:
定义:就是一个虚拟的表,一个视图所存储的并不是数据，而是一条 SQL语句
优点:(1).简化数据操作
(2).使不必要的数据可以不出现在视图中
(3).可以定制不同用户对数据的访问权限
(4).提供向后兼容性
格式:
(1).创建.
CREATE OR REPLACE FORCEVIEW view_name
AS 查询语句
[WITH CHECK OPTION ]约束条件不能修改
[WITH READ ONLY] (不能修改)
(2).删除.
drop view view_name
1.简单视图(没有聚合函数):可以增删改查
create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1
2.带约束视图:with check option,不能改条件里的数据,其他数据可以改
create or replace view view_address2 as
select * from T_ADDRESS where areaid=2
with check option
3.只读视图:with read only,不能修改
create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1
with read only
4.带错误视图(T_TEMP这个表不存在)
create or replace view view_TEMP as
select * from T_TEMP
5.复杂视图(有聚合函数或多表关联查询)
(1).多表关联查询
create or replace view view_owners as
select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id
T_OWNERS 表就是键保留表
键保留表的数据能修改,不是键保留表的数据不能修改,
键保留表:表的键值在一个连接视图中也是键值，那么就称这个表为键保留表,主键所在的表叫键保留表
(2).分组聚合统计查询
create view view_accountsum as
select year,month,sum(money) moneysum
from T_ACCOUNT
group by year,month
order by year,month
此例用到聚合函数，没有键保留表，所以无法执行 update
20.物化视图(实体化视图)
定义:它类似于一张表，需要占用存储空间,而对一个物化视图查询的执行效率与查询一个表是一样的,
作用:提高查询效率,但占用空间
语法:
CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE(默认:创建时生成数据) | BUILD DEFERRED (需要时生成数据)]
REFRESH [FAST(快速)|COMPLETE(完整)|FORCE(选择,默认)]
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery(查询语句)
(1).创建手动刷新的物化视图
create materialized view mv_address
as
select ad.id,ad.name adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id
手动刷新
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS’,‘C’);
end;
或者在命令窗口(command window)执行下面语句
EXEC DBMS_MVIEW.refresh(‘MV_ADDRESS’,‘C’);
(2).创建自动刷新的物化视图:多一个refresh on commit
create materialized view mv_address2
refresh
on commit
as
select ad.id,ad.name adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id
(3)创建时不生成数据的物化视图:build deferred
只有执行刷新语句的时候才会生成数据
create materialized view mv_address3
build deferred
refresh
on commit
as
select ad.id,ad.name adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id;
(4)创建增量刷新的物化视图
1.如果创建增量刷新的物化视图，必须首先创建物化视图日志
create materialized view log on t_address with rowid；-- 以rowid方式创建物化视图日志
create materialized view log on t_area with rowid
2.创建物化视图
create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name
adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id;
3.当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS4’,‘C’);
end;
21.序列
定义:序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象。
(1).创建简单序列:create sequence 序列名称
提取下一个值
select 序列名称.nextval from dual
提取当前值
select 序列名称.currval from dual
(2).创建复杂序列
CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中
(3).修改和删除
ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE; – 不能更改序列的 START WITH 参数
DROP SEQUENCE 序列名称;
22.同义词()
varchar是varchar2的同义词(为了兼容)
create [public] SYNONYM synooym(别名) for object(取别名的对象);
public:所有用户可使用,不加的话只能当前用户使用
(1).私有同义词
create synonym OWNERS for T_OWNERS; – 创建
select * from OWNERS ; – 查询
select * from wateruser.OWNERS – 别的用户访问(wateruser必须要有dba权限)
(2).公有同义词
create public synonym OWNERS2 for T_OWNERS; – 所有用户都可以访问
23.索引
(一)普通索引(btree*)
(1).定义:索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次
数,从而提高数据访问性能。
语法:create index 索引名称 on 表名(列名);
create index index_owners_name on T_OWNERS(name)
(2).索引性能测试
1.创建
create table T_INDEXTEST (
ID NUMBER,
NAME VARCHAR2(30)
);
2.插入 100 万条记录
BEGIN
FOR i in 1…1000000
loop
INSERT INTO T_INDEXTEST VALUES(i,‘AA’||i);
end loop;
commit;
END;
3. name 列创建索引
CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST(name)
4.测试
SELECT * from T_INDEXTEST where ID=765432; – 普通查询
SELECT * from T_INDEXTEST where NAME=‘AA765432’; – 根据索引查询
(二)唯一索引
创建索引的列不能有重复数据
create unique index index_owners_watermeter on T_OWNERS(watermeter); – watermeter是水表编号,是唯一的
(三)复合(组合)索引(是一棵树)
create index owners_index_ah on T_OWNERS(addressid,housenumber);
(四)反向键索引
根据二进制数,形成反向二进制数,产生的数是随机的数,使索引的值变得不规则,从而使索引树能够均匀分布。
create index 索引名称 on 表名(列名) reverse;
(五)位图索引
使用场景：位图索引适合创建在低基数列(如性别,民族,颜色列)上
create bitmap index 索引名称 on 表名(列名);
create bitmap index index_owners_typeid on T_OWNERS(ownertypeid) – 在ownertypeid(低基数列)建立索引,在ownertypeid只有三个值