Oracle

Oracle
与MySQL的区别
MySQL基于库，库下是数据表，Oracle基于用户，用户下对应的表

体系
 Oracle是一个全局数据库，整个Oracle软件就一个数据库
 实例：一个数据库可以有n个实例，之间互不干涉
 划分的单位：数据库->表空间段->段->区->数据块(最小的单位)[逻辑结构]->磁盘块[物理结构]
 
 
使用Oracle 
 创建表空间
 创建用户，[需要提供dba的权限后即可登陆]
   【grant dba to wateruser 】
 创建表
 
数据类型
 字符型
  char ：固定长度，<=2000字节
  *varchar2: 可变   <=4000字节
  long : 大文本   <2G
 数值型
  *number
  number(5)  :<=99999
  number(5,2):<=999.99
 日期型：[sysdate直接返回当前日期 adddate=adddate-3 时间是前三天]
  *date    :日期时间型，精确到秒
  timestamp：精确到秒的小数点后 9 位
 二进制型（大数据类型）
  CLOB : 存储字符,<= 4G
  BLOB：存储图像、声音、视频等二进制数据,<=4G

编辑表【增删改需要commit】
 追加字段 ..
 修改字段 ..
  修改字段名【ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名 】
 删除字段【ALTER TABLE T_OWNERS DROP COLUMN REMARK 】
  删除一个【ALTER TABLE 表名称 DROP COLUMN 列名 】
  删除多个【ALTER TABLE 表名称 DROP (列名 1,列名 2...) 】
 删除表
  【delete from 表名 where 删除条件;】需要commit提交，可回滚，会有碎片残留
  【truncate table 表名】不用提交，直接删除，不能回滚
 创建主键 :不为空且唯一【alter table 表名 add primary key(id)】
 创建外键约束:关联另一个表中的主键【alter table 表名 add foreign key(ownertypeid)】
 创建非空约束 not nul
 唯一约束 unique
 检查约束 【alter table 表名 modify addressid check(addressid>0);】    

 
 
JDBC连接Oracle 

数据的增删改

Oracle数据备份（cmd）
 导出【尽量不要全库】
  全库导出：Oracle导出整个数据库 exp system/itcast full=y
  按用户导出，指定file参数：exp system/itcast owner=wateruser file=wateruser.dmp
  按表导出：exp wateruser/itcast file=a.dmp tables=t_account,a_area
  dir 查询导出文件位置
 导入
  全库导入：imp system/itcast full=y
  按用户导入：imp system/itcast  file=wateruser.dmp fromuser=wateruser
  按表导入：imp wateruser/itcast file=a.dmp tables=t_account,a_area
  

  
  
  
【数据查询】
单表查询
  精确查询
  模糊查询  like %xx%
 运算符
  and/or/and+or  [and>or,可以用()来改变优先级]
 范围查询
  >/</between and
 空值查询
  is null
  is not null
 去重查询【distinct只能放在select后面】
  【select distinct addressid from T_OWNERS】
  【select distinct addressid，ownerType from T_OWNERS】需要满足两个都不重复的
 排序
  升序【select * from t_account order by usenum】
  降序【select * from t_account order by usenum desc】
 伪列[ROWID 和 ROWNUM，扫描查询时生成]
  rowid 创建查询时生成的ID，用此查询可以解锁改数据【查询语句后加 for update可以解锁】
   select rowid,id,name from t_owners
   select rowid,t.* from t_owners t
   select rowid,t.* from t_owners t where rowid='XXX'
  ROWNUM 会生成结果集的一个序号，用于分页
   select rownum,t.* from t_owners t

 聚合统计
  聚合函数：sum/avg/max/min/count(*/1/0)
   【select count(1) from T_OWNERS t】
  分组聚合：【group by】select后必须是分组聚合的条件or聚合函数
   分组后条件查询having
   
 为什么少用having【面试】
 因为having在group by 后执行，数据过大时，效率过低
 
多表查询(连接查询)
 内连接查询  找到关联的数据
 外连接查询 左外
     【sql1999标准：select ow.id,ow.name,year,month,money from t_owners ow left join t_account ac on ow.id=ac.owneruuid】
     【Oracle写法：select ow.id,ow.name,year,month,money from t_owners ow, t_account ac where ow.id=ac.owneruuid(+)】
     [如果是左外连接，就在右表所在的条件一端填上（+）]
    右外
     【Oracle写法：select ow.id,ow.name,year,month,money from t_owners ow, t_account ac where ow.id(+)=ac.owneruuid】
 
子查询(嵌套查询)
 单行子查询：只返回一条记录
 多行子查询：返回多条记录
  in运算符 ：等于列表中的任何一个 【in(1,3,4),not in(2,6)】
  all运算符：和子查询返回的所有值比较 【usnum>all(子查询结果)】
  any运算符: 和子查询返回的任意一个值比较
 
*分页查询[rownum只能是<或者<=]
 第一页：【select rownum,t.* from T_ACCOUNT t where rownum<=10】
 第二页：子查询后再筛选【select * from(select rownum r,t.* from T_ACCOUNT t where rownum<=20) where r>10】
 基于排序的分页：三层嵌套，先排序，再生成rownum，再截取ruwnum的值进行分页
  【select * from
   (select rownum r,t.* from
   (select * from T_ACCOUNT order by usenum desc) t where rownum<=20 )
   where r>10
  】
 
 

伪表 dual ：没有任何东西，单纯为了显示数据的表

单行函数 
 字符串长度  length():   【select length('ABCD') from dual 【4】】
 字符串的子串substr():   【select substr('abcd',2,2) from dual 【bc】】
 字符串拼接  concat():   【select concat(concat('a','b'),'c')from dual】
    【select 'ab'||'c'||'d' from dual】
    【数字和字符串拼接会自动转换】
数值函数
 四舍五入：round(10.2)默认从整数开始【select round(10.457,2) from dual】
     round(100.456,2) --100.46
 截取    ：trunc(100.456,2) --100.45
 取模    ：mod(10,4)        --2
 

日期类型
 sysdate：获取当前日期和时间
  【select sysdate from dual】
 加月函数【select add_months(sysdate,2) from dual】
 所在月的最后一天【select last_day(sysdate) from dual】
 日期截取【select trunc(sysdate)from dual】--按日截取
   【select trunc(sysdate,'mm')from dual】--按月截取

*转换函数 
 数字->字符串：to_char()
  【select to_char(100) from dual】
 日期->字符串：to_char()
  【select to_char(sysdate)from dual】
  【select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual】
 字符串->日期：to_date()
  【select to_date('2018-1-18','yyyy-mm-dd')from dual】
 字符串->数字：to_number()
  【select to_number('100') from dual】
  【select '100'+0 from dual】

其他函数
 空值处理：
  nvl(检测的值，如果为 null 的值)，当值为null时自动转成别的值
   【select nvl(null,0)from dual】
  nvl2(检测的值，如果不为 null 的值，如果为 null 的值)，
   【select nvl2(maxnum,to_char(maxnum),'不限') from t_pricetable  where ownertypeid=1】
 条件取值：
  decode【Oracle函数】
   【select decode(100,1,2,3,4,200,100) from dual--无】，
   【select decode(100,1,2,3,4,200) from dual--200】返回缺省
   【select name,decode(ownertypeid,1,'居民',2,'行政事业单位',3,'商业') from T_OWNERS】
  case when then 【通用】
   【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 '商业'
       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<='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
       
分析函数
 RANK 相同的值--排名相同，排名跳跃
  【select rank() over(order by usenum desc) ,t.* from t_account t】
 DENSE_RANK 相同的值排名相同，排名连续需求
  【select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT】
 ROW_NUMBER 返回连续的排名，无论值是否相等需求
  【select row_number() over(order by usenum desc ),usenum from T_ACCOUNT】
 分析函数分页
  【select * from
   (select row_number() over(order by usenum desc )
   rownumber,usenum from T_ACCOUNT) where rownumber>10 and rownumber<=20
  】
 
集合运算【同结果集】
 并集：返回各个查询的所有记录
   包括重复记录 union all
   去掉重复记录 union
 交集: intersect  返回两个查询共有的记录
 差集：minus 返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录
  差集运算
   select rownum,t.* from T_ACCOUNT t where rownum<=20
    minus
   select rownum,t.* from T_ACCOUNT t where rownum<=10

 

 

视图【是一个虚拟表，不能提高效率】
 sql语句可以封装成一个视图，视图被调用，视图嵌套视图【简化数据操作】
 创建视图：create or replace viewname as subsquery
 创建一个带错误的视图：create force view viewname as subsquery
 只读视图：
 删除视图：drop view viewname;
 待检查约束：
 键保留表：主键所在的外键表，只能修改键保留表
 
物化视图【真是存在的表，可以提高效率】
 create materialized view view_nmae ... as subquery
 创建一张表和一个视图，删除物化视图，对应建立的表也被消失
 
 手动刷新,插入数据要刷新，才能成功
  begin
   dbms_mview.refresh('view_name','c');
  end;
 自动刷新：插入数据无需刷新【用于实时性强】
  create materialized view view_nmae
  refresh on commit
  as subquery
 创建时不生成数据
  build deferred
 创建增量刷新【前提，有物化视图日志，记录基表的变化，根据变化更新物化视图】
  create meterialized view log on 表名 with rowid
  原表改变时就添加到此日志表
  刷新后就从日志表中消失
  
as的使用：快速创建表
 create table 表1 as select * from 表名2 where 1=2；
 【条件不成立时，表1就只有表2的结构，没有条件约束时，就复制表2到表1】

创建简单序列
 create sequence seq_test;--创建
 select seq_test.nextval from dual;--查询下一个值
 select seq_test.currval from dual; --查询当前值

创建复杂序列
 最大/小值 maxvalue/minvalue 10
 循环cycle
 开始数 start with 10
 一次增加的数 increment by 10 【减少用负数】
 缓存 cache 5 [一次循环的数=cache值*增长值,一次循环的值不能小于一次缓存的数]
 【公式：ceil((max-min)/increment)>=cache】

同义词
 create [public] synonym 别名 for 真名;
 私有[公有]同义词

索引【大数据】
 create index 索引名 on 表名(列名)
 主键创建时就自动创建索引
 查询速度：非按主键查询<按主键查询<根据rowid查询
 
 唯一索引:create unique index 索引名 on 表名(列名);
 组合索引:create index 索引名 on 表名(列名1，列名2);

反向索引【防止歪脖子树】
 十进制数->二进制->二进制反向
位图索引bitmap
 低基数列：有限个的值【性别，四季，星期...】
 不能按范围查询
 低基数才能用位图，
 不能用not关键字,禁止使用空值比较，通配符在词首时不可用

建立索引原则 
 唯一性索引>非唯一性索引
 基数大的列建立B数索引，小的建位图索引
 常用的列排前面
 限制索引的数量

 
 
 
 
【pl/sql】
 oracle对sql的语言扩展
 语法
  [declare --声明变量]
  begin
   --代码逻辑
  [exception --异常处理]
  end；
 
 变量定义：
  变量名 类型(长度);
   【v_num0 number(10,2);】
  引用类型  表名.列名%type;
   【v_num0 t_account.num0%type;】
  记录型  表名%rowtype; 行记录类型；
   【v_account t_account%rowtype;】//只能返回一条记录时使用
 变量赋值：
  变量名：=变量值;
  select 列名 into 变量名;
   【select 列名1,列名2 into 变量1,变量2 from 表名 where 条件;】
 输出：
 【dbms_output.put_line('金额：'||v_money);】
 
 异常：
  预定义异常：
   no_date_found:使用select into 未返回
   too_many_rows:使用insert into 返回多条
  语法结构：
   exception
    when 异常名 then 处理方式
   end;
 
 判断：
 1 if 条件 then
  
  end if;
  
 2 if 条件 then
  
  else
  end if;
 
 3 if 条件 then
   
  elseif 条件 then
  elseif 条件 then
  ...
  end if;
 
 循环：
  无条件循环：begin 循环初始值 loop 循环体
       [if 结束条件 then exit end if;]/[exit when 结束条件;]
       end loop;
     end;
  有条件循环：begin 循环初始值 while 执行条件
       loop 循环体 end loop;
     end;
  for循环：begin for 局部变量 in 循环条件
      loop 循环体 end loop;
      end;
 
 
 游标：
  声明：cursor 游标名 is SQL语句;
  使用1：open 游标名;
    loop
     fetch 游标名 into 变量
     exit when 游标名%notfound
    end loop;
     close 游标名;
  使用2：for 局部变量 in 游标名
    loop
     循环体
    end loop;  
  
 带参数的游标
  游标名(类型 参数)
 
 
【存储函数】
 创建或修改存储过程的语法
  create[or replace] function 函数名
   (传入的参数 类型)
   return varchar2
   is
    参数 类型;  /变量声明
   begin
    逻辑部分
   end;
 
【存储过程】
 类似于存储函数，执行效率最高
 参数的三种形式
  in
  out
  in out

 

触发器
 是一个与表关联的，存储pl/sql程序
 前置触发器：before
 后置触发器：after
 
 语法：
  CREATE  [or REPLACE] trigger  触发器名
     BEFORE | AFTER
     [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]   //多个触发器就用or连接
     ON  表名
     [FOR EACH ROW  ][WHEN(条件) ]
     declare
     ……
     begin
    PLSQL 块 
   End ；

 伪记录变量(行级)
  :old 操作之前
  :new 操作之后