Oracle简单入门

NAT虚拟网络: 如果本机电脑能上网,就是虚拟机内的系统可以上网


1.oracle数据库特点: 
    支持多用户,大事务量的事务处理


     数据安全性和完整性控制


     支持分布式数据处理


     可移植性; 


2. oracle 体系:


     1.只有一个数据库   2.实例:一个数据库可以有多个实例  3: 存在磁盘上的数据文件. 


     4:表空间:不会直接操作数据文件,而是操作表空间来找到数据文件.---特有的


     5.数据库--->表空间--->段----区----数据块 操作磁盘块




3. 用户: 一个数据库下面分为多个用户,用户下建表, 而mysql是数据库下建立表




每个项目都要执行4,5,6步骤


4. 创建表空间:  create tablespace 空间名   datafile 'c:\文件地址\文件.dbf'


     size 文件大小m  autoextend on 超过100兆自动增长  next 10m 增长10m 分号结尾
  




5.  一个表空间可以建立多个用户创建用户:   
       create user 用户名   identified by 密码  default tablespace 表空间




6.赋权限:  grant dba to 用户名;




7.数据类型
      1. 字符型 : char 2000字节   varchar2 4000字节   long大文本类型,存2G;


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


      3. 日期型 : date : 精确到秒,  timestamp: 精确到秒的小数点后9位;


      4.  二进制型(大数据类型) : clob : 存储字符, 最大4个G   blob: 存储图像,生硬,视频,4个G  




8.修改,删除 drop column一个列,  添加日期: sysdate 当前时间. 


  非空 not null  唯一约束 unique  检查约束: alter table 表名 modify 列名  check(列名>0)




9.创建工程,引入ojdbc14.jar. 驱动名: oracle.jdbc,driver,OracleDriver, 
 
瘦连接,可以远程连接服务器,本地无需安装oracle;  胖连接,依托本地软件


加载三个参数:  jdbc:oracle:thin:@192.168.80.10:1521:orcl




10.数据导出与导入: 在服务器中执行,不用进入任何文件夹
      
      1.整库导出与导入:  导出: exp 数据库名/密码 full=y file 文件名.dmp 
                         导入 imp 数据库名/密码 full=y




      2.导出: exp 数据库名/密码 owner=用户名 文件名.dmp 

          导入: imp system/itcast    file=文件名.dmp fromuser=用户名 

1.  select 查询 加括号改变优先级






2.伪列  
   1. rowid ,每一个行数的UID   select rowid, t.*  from t_owners t; 物理地址 查询更快


   2. rownum ,每一行的行号,   select rownum, t.*  from t_owners t;  结果集的序号






3.分组聚合统计
  
    分组可以加两个分组.




4.多表查询  左外连接在 where 右边表的id加(+), 右边连接在 where左边表的id加(+)




5.子查询:  单行子查询 大于等于小于一个数


  多行子查询  多个数 in,all,any(和子查询返回的任意一个值比较)






6.分页查询:   简单分页where rownum <=10,  运算符只能是小于等于,小于;
 
     条件查询分页:   一定注意先产生记录才能根据行号来进行选择记录, 然后就是记录先排序,然后在扫描,行号不会乱




7.单行函数:   伪表 dual




   1.字符函数
 
     1. 求字符串长度 select length('ABCD') from dual;


     2.字符串子串 select substr('ABCD',从第几位截取,截取字符数) 从1开始. from dual;


     3.字符串拼接,concat('abc','d')只能拼两个   ,  或者 select 'ab' || 'd' || 'g' 可以拼多个
  
 


   2.数值函数
 
     1. 四舍五入 select round(100.456) from dual; 默认整数四舍五入, (100.343,2)从小数点后两位


     2.数字截取  select trunc(100.434,从小数点留下几位) from dual;


     3.取余数    mod(10,4)
  




  3.日期函数 日期截取mi按分钟截取.






  4.转换函数  有自动转换功能


     1.数字转为字符串 select to_char(100) from dual, 靠右显示是数字,靠左是字符串.


       日期转字符串  select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual




     2.字符串转为日期:  select to_date('2016-03-10','yyyy-mm-dd')
       字符串转为数值:  select to_number('100') from dual






  5.其他函数 
 
      1.空值处理函数 NVL  NVL(列名,自己随便写值) 代表如果列值为null, 就显示自己写的值;前后类型一致,


        如果不一致 用 NVL2(列名,to_char(不为null显示的值),为null显示的值)




      2.条件取值 decode
   
        decode(条件,值1,翻译值1,值2,翻译值2.....缺省值,可变参数)




       SQL1999标准
             select (case 列名
                           when month>'01' and moth<='03' then '不好不坏'
                           when 2 then '好'
                           when 3 then '坏'
                           else '其他'
                     end) from 表


8. 行列转换




9. 分析函数 select rank(), over(order by usenum desc) 排名 from 表  ,
            1.rank() 值相同,序号跳跃
             
            2.dense_rank() : 值相同,序号连续


            3.row_number() : 序号连续,不管值相同,相同的值只显示一个  可以做分页




10. 集合运算: 并集 union, 不包括重复,  加 union all ,包括重复 
              交集 intersect 
              差集: minus : 可以做分页


1.  select 查询 加括号改变优先级






2.伪列  
   1. rowid ,每一个行数的UID   select rowid, t.*  from t_owners t; 物理地址 查询更快


   2. rownum ,每一行的行号,   select rownum, t.*  from t_owners t;  结果集的序号






3.分组聚合统计
  
    分组可以加两个分组.




4.多表查询  左外连接在 where 右边表的id加(+), 右边连接在 where左边表的id加(+)




5.子查询:  单行子查询 大于等于小于一个数


  多行子查询  多个数 in,all,any(和子查询返回的任意一个值比较)






6.分页查询:   简单分页where rownum <=10,  运算符只能是小于等于,小于;
 
     条件查询分页:   一定注意先产生记录才能根据行号来进行选择记录, 然后就是记录先排序,然后在扫描,行号不会乱




7.单行函数:   伪表 dual




   1.字符函数
 
     1. 求字符串长度 select length('ABCD') from dual;


     2.字符串子串 select substr('ABCD',从第几位截取,截取字符数) 从1开始. from dual;


     3.字符串拼接,concat('abc','d')只能拼两个   ,  或者 select 'ab' || 'd' || 'g' 可以拼多个
  
 


   2.数值函数
 
     1. 四舍五入 select round(100.456) from dual; 默认整数四舍五入, (100.343,2)从小数点后两位


     2.数字截取  select trunc(100.434,从小数点留下几位) from dual;


     3.取余数    mod(10,4)
  




  3.日期函数 日期截取mi按分钟截取.






  4.转换函数  有自动转换功能


     1.数字转为字符串 select to_char(100) from dual, 靠右显示是数字,靠左是字符串.


       日期转字符串  select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual




     2.字符串转为日期:  select to_date('2016-03-10','yyyy-mm-dd')
       字符串转为数值:  select to_number('100') from dual






  5.其他函数 
 
      1.空值处理函数 NVL  NVL(列名,自己随便写值) 代表如果列值为null, 就显示自己写的值;前后类型一致,


        如果不一致 用 NVL2(列名,to_char(不为null显示的值),为null显示的值)




      2.条件取值 decode
   
        decode(条件,值1,翻译值1,值2,翻译值2.....缺省值,可变参数)




       SQL1999标准
             select (case 列名
                           when month>'01' and moth<='03' then '不好不坏'
                           when 2 then '好'
                           when 3 then '坏'
                           else '其他'
                     end) from 表


8. 行列转换




9. 分析函数 select rank(), over(order by usenum desc) 排名 from 表  ,
            1.rank() 值相同,序号跳跃
             
            2.dense_rank() : 值相同,序号连续


            3.row_number() : 序号连续,不管值相同,相同的值只显示一个  可以做分页




10. 集合运算: 并集 union, 不包括重复,  加 union all ,包括重复 
              交集 intersect 
              差集: minus : 可以做分页


mysql多台数据库之间如何保持一致:写库,产生日志, 读库根据日志来进行显示;




1. 视图: 是一种数据库对象,一种虚拟表,作用可以封装SQL语句,再次查询的时候,不用写SQL语句了.


         简化数据操作.着重于特定数据,提供了简单安全机制,提供向后兼容性


         列要加别名


2. 视图的创建 create [or replace(OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；)


               [force]表不存也可存 ] view view_name as subquery(查询语句) 


               [with check option]是否符合语句条件  [with read only] 只读试图




            删除:  drop  view view_name






3. 只是单表查询,没有聚合函数,就是简单视图;  查询简单视图 select * from 视图名 可以自己再次加条件


    修改简单视图:  update 表名  set 列名='' where ...;  改视图的表的数据就改了表的数据,占了一条数据大小;


    视图是伪表;可以该表基表的数据




4.带检查约束的视图:  with check option ;   修改表时会检查条件where




5.只读视图 : with read only ;   创建带错误(或不存在表)的视图;  force ; 表可能会动态产生会用到这种情况




6. 复杂的视图: SQL语句有聚合函数或多表关联. 查询: 可以加条件;


     修改复杂视图(多表关联)的数据: 基表数据会发生变化; 前提是 修改的字段属于键保留表(把主键保留下的表;)




7. 聚合统计的视图: 所有的列不能修改.没有键保留表




8. 物化视图: 上面创建的视图都是虚拟视图,知识SQL语句, 现在创建一张表就是物化视图.执行效率更高,只需查找一张表;
             缺点: 占用存储空间




     1. create  materialized view 视图名  [build immediate (创建视图就生成数据,默认) 


            build deferred(创建视图不生成数据),显示数据第一次就要执行刷新命令, 第二次根据3条件来决定是否刷新






     2.  refresh [fast (快速刷新,增量刷新)| complete (完全刷新) | force(自动选择,自动匹配前两个模式,默认的)]
          
           创建增量刷新:  前提是: 必须有物化视图日志: 记录基表发生了哪些变化;然后用记录来更新物化视图




           创建日志: create materialized view log on 表名 with (主键或rowid),如果是多表的话,就要创建多个日志
                     基表发生增删改会有数据
 
                
           第二个前提是: 创建物化视图的语句中,必须有rowid或主键,多个表都要有rowid,




            然后增量刷新: refresh fast , 但手动刷新的时候日志被清空,物化视图刷新




     2.1 自动刷新的物化视图, refresh on commit 基表发生commit操作时候,自动刷新视图;




     3.1  on [commit] 在基表做提交操作就刷新物化视图; 自动刷新
 
                 创建自动刷新物化视图: refresh on commit
         




     3.2   on demand 手动刷新(默认的), 执行begin DBMS_MVIEW.refresh('物化视图表名','c')  end; 来刷新


                                             c指的是complete完全刷新


                                    窗口执行 EXEC DEMS_MVIEW.refresh('物化视图表名','c'); 来刷新




序列只能用于SQL 语句


9.序列:可以产生连续数字  create sequence 序列名  ,   序列名.nextval伪列 自动增长,可以应用在主键id生成


        序列名.currval 当前值, 是固定的; 默认起始值0;




      1.创建复杂序列: 


        create sequence 序列名  maxvalue 数值 ; increment 增长多少个 start with从多少开始 minvalue 最小值
          cycle 循环


         注意1: 默认有缓存并且值为20个,所以设置最大值和最小值要注意 


         注意2: cache 最大值-最小值/每次增长 向上取整 大于等于缓存值;


         注意3: 循环序列要最小和最大值




      2.修改和删除序列, 起始参数不能更改;








10. 同义词: 别名 create[public(private默认)] SYNONYM 同义词名称 for object; object 表, 视图....


             varchar() 是varchar2() 的同义词;是内置的  , 可以基于同义词再次创建一个同义词;






11. 索引:  加速数据存取的数据对象, 数据量大的时候用.类似于树,占据存储空间. 1-4 统称为 b tree * 索引


       1.普通索引  create index索引名称 on 表名(经常查询的列名)  主键是系统自建索引;
                   当查询时候,先找索引,然后再找列;




       2.唯一索引:  这一列的值不会重复create unique index, 不仅有索引还有唯一约束 




       3.复合索引:  为多列创建复合索引  on 表名(列1,列名2) ,列的顺序也有要求.




       4.反向键索引: on 表名(列名) reverse  当某个字段的值为连续增长的时候,层数比较多,查询慢 


                     反向运算,把数值的二进制反过来,数值就随机了,层数少






        5.位图索引:  适合创建在低基数列上,查询时只能用等号 比如sex='男'; 一个值一个位图.
                     create bitmp index






12.视图和索引是数据库通用的




                                                   oracle的编程


1.PLSQL : 
 
   declare 声明变量  


   begin 
         代码逻辑 


        DBMS_output.put_line('自己起的名字' || 变量名);


   exception异常处理  


   end;




   声明变量   变量名   类型(长度);  
 
   赋值:      变量名:=变量值   或  select 列名 into 变量名 from 表名 where


              注意：结果必须是一条记录 ，有多条记录和没有记录都会报错




2. 属性类型:  


          当不确定这个类型  引用类型   表名.列名%type   用于声明变量名类型;


          记录型:  引用类型的变量名    表名%rowtype    作用: 标识某个表的行记录类型


          当取出表内的某一列值时, 变量名.列名;




3.异常处理:   预定义异常:21种  自定义异常


   exception异常处理  
   
      when  异常类型1 then
        异常处理逻辑(  DBMS_output.put_line(错误);)


      when  异常类型2 then
        异常处理逻辑(  DBMS_output.put_line(错误);)




4.条件判断


 一:
  if  then


  end if;




 二:
  if 条件  then


  else


  end if;




 三:


   if 条件 then


       代码逻辑;


   elsif 条件  then


     代码逻辑;


    else
       代码逻辑;
   end if;






5. 循环: 三种  


   1.无条件


    loop
       循环语句


       exit when 条件; 退出
    end loop;




   2.有条件


       while 条件
       loop
       end loop;
    


   3.for循环  如: 1-100个数


      for 变量名 in 起始值..终止值
      loop
       循环语句
      end loop; 






6. 游标: 就是结果集,然后读取数据的;
 


  declare


   1.声明游标 cursor 游标名称(参数名 number) is sql语句条件=参数名;
 
  begin
   2.使用游标 
          
        open 游标名称(指定参数值);


        loop 


           fetch 游标名称  into 变量;


           exit  when 游标名称%notfound;


           输出语句 , 注意是输出最后一行后,游标再次循环,直接就退出了,不输出了所有顺序不能颠倒;


        end loop;


        close 游标名称;


   end;






 2.for循环带参数游标;
  
   declare
     声明部分可以省略定义游标类型 ;直接给游标赋值


   begin


      2.使用游标 
          
        for 变量名 in 游标名称(参数)
        loop 
          输出
        end loop;
   end;






7.存储函数 又称自定义函数,可以接受一个或多个参数,返回一个结果;
           用于查询比较多




  create(或修改 replace) function 函数名称(变量名1)
  (参数名称 参数类型,...)


   return 结果变量数据类型(varchar2不用写长度,number)


   is
    变量2声明部分;varchar2(30); 写长度


    begin
       给变量2赋值sql语句;
       return 变量2;
     end;




  测试函数
  select fn_getaddress(变量名) from dual




8.存储过程, 返回多个值


   create or replace(创建或修改)  procedure 存储过程名称
   (参数名称 参数类型(不指明长度),...)


   is或as


   begin


   end;




   




 1. 调用不带传出参数的存储过程 , 可以省略in(因为默认传入参数) ;


      1.  call  存储过程名();
     
      2.begin
           存储过程名();
        end ;


   
 2.JDBC的第三种调用存储过程


    用callablestatement = conn.preparecall("{call 存储过程名(?,?,?..)}");


    stmt.setString(1,);






 3.传出参数的存储过程
  (参数名称 out 参数类型,...)


   对传出参数赋值
   select 要赋的值 into 参数名 from ...;




 4.调用传出参数只能 用 begin  end;


   declare 


      定义变量名
   begin  
      存储过程名(...,一个参数为上面变量名);
      变量名就有值了;
    end;




  5.用java代码调用带传出参数
   
   //注册传出参数的类型
   stmt.registeroutparameter(第几个参数, oracleTypes.类型);


    stmt.execute()
   //接受参数


   stmt.getlong(第几个参数);




9. 触发器: 存储了PL/SQL程序,  数据确认, 实施复杂的安全性检查, 跟踪表上所做的数据操作等,数据的备份与同步;


   分类: (行级 执行多句 或语句级 不管影响多少行,只执行一句话) 前置触发器类似拦截器,  后置触发器,类似于备份;  




   create [或 修改 or replace]  trlgger 触发器名
  
       before 或 after
      
      delete [or] insert [or] update []of 列名1,列名2] 


      on 表名
      
      for each row 或 when (条件)
        
    declare


    begin
          PLSQL块, 不用写commit
    end;




   2.只能用在行级触发器,后置触发器只能读 伪记录变量 :old  :new 
 


   3.前置触发器建立后不用手动调用