oracle中常用的SQL语句

一、oracle中常用的SQL语句
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](覆盖原有) [FORCE](错误视图)VIEW 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只有三个值