Oracle语法及基础函数

一 . 数据类型

数据类型:
    char : 固定长度类型,最多存2000个字节
    varchar2:可变字符类型,最多存储4000个字节
    long:大文本类型,最多存2个G

    number:数值类型(默认18)  number(5) 存五位数  最大存99999
    number(5,2) 最大存999.99

    integer : 相当于number 小数位数为0

                   oracle本来就没有int类型，为了与别的数据库兼容，新增了int类型作为number类型的子集。
                   int类型只能存储整数;
                   number可以存储浮点数，也可以存储整数；
                   number(8,1)存储小数位为1位，总长度为8的浮点数，如果小数位数不足，则用0补全；
                   number(8)存储总长度为8的整数；
                   int相当于number(22),存储总长度为22的整数。

                   decimal,numeric,int等都为SQL、DB2等数据库的数据类型，ORACLE为了兼容才将其引入；但实际上在ORACLE                     内 部还是以NUMBER的形式将其存入。

    date:日期时间型,精确到秒

     java.util.date与java.sql.date相互转换问题:  
    先转long再转java.sql.date   new java.sql.Date(date.getTime());

    timestamp:精确到秒后9位

    clob:存字符,最大4个G
    blob:存二进制,最多4个G

创建表空间:  (在Tablespaces下可见)
    create tablespace waterboss
        datafile 'c:\waterboss.dbf'
    size 100m
    autoextend on
    next 10m

 创建用户:   (user下可见)
    create user wateruser
    identified by admin
    default tablespace waterboss

 以system登录给wateruser赋权
    grant dba to wateruser

 创建表:
    create table T_OWNERS(
        ID NUMBER PRIMARY KEY,
        NAME varchar2(30),
        address number,
        housenumber varchar2(30),
        watermeter varchar2(30),
        adddate date,
        ownertypeid number
    )

 修改表:
    追加字段:
    alter table T_OWNER add(
        remark varchar2(20),
        outdate date
    )

    修改字段:
    alter table T_OWNER modify(
        remark char(20),
        outdate timespamp
    )

    修改字段名:
    alter table T_OWNER rename column XXXX to XXXX

    删除字段名:
    alter table T_OWNER drop column NAME 

 删除表
    drop table 表名称

 数据删除
    delete from T_OWNER where id=?   (并未真正删除)
    commit

    truncate table T_OWNER   (不需提交,先摧毁表结构,再重建表结构)

 插入数据:
    insert into T_OWNERS values(X,X,XX)
    commit

        插入当前日期: sysdate
 修改语句:
    update T_OWNERS set adddate=adddate-3 where id=1;

 

-----------------------------------------------------------------------------
JDBC连接:
    驱动包:c:/oracle/product/10.2.0/db_1/jdbc/lib/ojdbc14.jar
    
    Class.forName("oracle.jdbc.driver.OracleDriver");

    DriverManager.getConnection("jdbc:oracle:thin@192.168.168.7:1521:orcl", "wateruser",
                "admin");

-------------------------------------------------------------------
数据导出和导入(全库)
     导出: 在虚拟机中打开命令提示符(一般用system用户)
        exp system/admin full=y     默认导成EXPDATE.DMP文件
        exp system/admin full=y file=water.dmp  重命名
     导入: 
        imp system/admin full=y
        exp system/admin full=y file=water.dmp  

    指定用户导入导出:
        exp system/admin owner=wateruser file=wateruser.dmp
        imp system/admin file=wateruser.dmp fromuser=wateruser

    按表导入导出:
        exp wateruser/admin file=a.dmp tables=t_account,a_area
        imp wateruser/admin file=a.dmp tables=t_account,a_area

-----------------------------------------------------------------------------
条件查询:
    where 是分组之前的条件  having是分组之后的条件,对聚合统计之后结果再统计

子查询:  where   单行   多行
         from    
    select   单行

分页查询
    简单分页:  select * from t_account t where rownum<10  (只能用<或<=号)
        一般用子查询实现先产生值
    嵌两层
    select * from (select rownm r,t.* from t_account t)
    where r<=20 and r>10

    
    基于排序的分页:
        嵌三层:
    select * from 
        (select rownum r,t.* from 
        (select * from t_account t order by usenum desc) t
        )  
    where r<=20 and r>10

--------------------------------------------------------------------
单行函数
    字符函数:  length('abcd') 
           select length('abcd') from dual

           substr('原字符串',从第几位,截取字符数)
           select substr('abcd',2,2) from dual

           concat('abc','d')
           select concat('abc','d') from dual
           
           select 'abc'||'d'||'ef' from dual

    数值函数:
           四舍五入:round(100.5) 
           select round(100.567,2) from dual

           数字截取:trunc(100.567)
           select trunc(100.567,2) from dual
           
                   取模:mod(10,3)
           select mod(10,4) from dual
           
    日期函数:
           sysdate:当前日期
           
           加月:
           select add_months(sysdate,2) from dual

           所在月最后一天:last_day
           select last_day(sysdate) from dual

           日期截取:
           select trunc(sysdate) from dual   按日截取
           select trunc(sysdate,'mm') from dual 按月截取
           select trunc(sysdate,'yyyy') from dual  年
           select trunc(sysdate,'hh') from dual 

    转换函数:
           数字转字符串:to_char()
           select to_char(100) from dual
           select 100||'' from dual
        
           日期转字符串:to_char(sysdate)
           select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
           select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月' from dual
            
           字符串转日期:to_date('2016_03_10','yyyy-mm-dd') 
           select to_date('2016-03-10','yyyy-mm-dd') from dual

           字符串转数字: to_number('100')
           select to_number('100')+10 from dual
           select '100'+0 from dual

    其他函数:  
           空值处理:nvl(null,0)  判断第一个值是否为null 是则返回第二个值
           select nvl(null,0) from dual  为null是返回0

               nvl2(null,0,100)   为null则第二个参数, 否则为第一个餐数1
           
           条件取值: decode 条件判断
           select decode(100,1,2,3,4,100,200) from dual
           select name,decode(ownertypeid,1,'居民',2,'行政',3,'商业','其他')
    
           sql1999
           case when then
           select name,(case ownertypeid
            when 1 then '居民'
            when 2 then '行政'
            when 3 then '商业'
            else '其他'
           end
           ) from t_owners
           
           或者
           case when then
           select name,(case 
            when ownertypeid=1 then '居民'
            when ownertypeid=2 then '行政'
            when ownertypeid=3 then '商业'
            else '其他'
           end
           ) from t_owners

行列转换:   报表常用
       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<='08' 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

分析函数: 值相同,排名相同,序号条跃
      select rank() over(order by usenum desc) , t.* from t_account t

      值相同,排名相同,序号连续
      select dense_rank() over(order by usenum desc) 排名,t.* from t_account t

      序号连续,不管值是否相同
      select row_number() over(order by usenum desc) 排名,t.* from t_account t

分析函数分页:
      select * from (
        select row_number() over(order by usenum desc) rownunber ,t.* from t_account t)
      where rownumber<=20 and rownunber>10    
      

集合运算:
      并集
      select * from t_owners where id>5
      union all(有重复 union 无重复)
      select * from t_owners where id<8
        
          
          交集
      select * from t_owners where id>5
      intersect
      select * from t_owners where id<8

      差集
       minus 去掉交叉部分
      select * from t_owners where id>5
      minus
      select * from t_owners where id<8

      select * from t_owners where id<8
      minus
      select * from t_owners where id>5

      差集分页:
          select rownum ,t.* from t_account t where rownum<=20
      minus
      select rownum, t.* from t_account t where rownum<=10

写法顺序:  select ... from ... where ... group by... having... order by ...
执行顺序:  from   where    group by    having     select     order by

----------------------------------------------------------------------------------
视图:
    一种虚表,封装sql语句
    简化数据操作,不让看到敏感数据,权限,

    创建或修改视图:
        create [or replace] view view_name as subquery

    简单视图:
        创建简单视图:
        create view view_owners1 as
            select * from t_owners where ownertypeid=1
            查询视图
        select count(*) from view_owners1 where id in(1,2,3,4,5)
            修改视图
        update view_name set  XXX where

    带检查约束的视图:
        create view view_address2 as 
        select * from t_address where areaid=2
        with check option  --  带检查约束
    
        update view_address2 set areaid=3 where id=4   --修改失败 因为该视图的条件是areaid=2

        
    只读视图--只能查,不能改,删,增
        create or replace view view_owners1 as
        select * from t_owners where ownertypeid=1
        with read only

    带错误的视图(t_test不存在)
        create force view view_test as
        select * from t_test

    

    复杂视图
         多表关联:
        create or replace view view_owners as
        select * from t_owenrs ow,t_ownertype ot where ow.ownertypeid=ot.id

        --键保留表:把主键保留下来的表,只能修改键保留表的数据
        --注意视图最好加上别名

    分组聚合统计: 无键保留表,所以不能修改
         create view view_accountsum as
         select year,month ,sum(money) money
         from t_account 
         group by year,month
          order by year,month
         select * from view_accountsum

    
    物化视图: 相当于真实的表,效率高,但占用空间
         创建一个手动刷新的视图:
        create materialized view mv_address1 as
        select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
        where ad.areaid=ar.id
            
        begin
            DBMS_MVIEW.refresh('MV_ADDRESS1','C');
        end;

         自动刷新
        create materialized view mv_address1 
        refresh
        on commit
        as
        select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
        where ad.areaid=ar.id

         创建时不生成数据:
        create materialized view mv_address3
        build deferred
        refresh
        on commit
        as
        select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
        where ad.areaid=ar.id

        --产生数据
        begin
            DBMS_MVIEW.refresh('MV_ADDRESS3','C');
        end;
        
        
        
         增量刷新的视图: 前提先有物化视图日志:基表发生了那些变化
        create materialized view log on t_address with rowid
        create materialized view log on t_area with rowid

        create materialized view mv_address4
        build deferred
        refresh fast  
        as
        select ad.rowid,ar.rowid,ad.id,ad.name,ar.name arname from t_address ad,t_area ar
        where ad.areaid=ar.id