就业班_JavaEE_day32_35_Oracle

一  分布式和集群的区别：
         分布式指的是将一个应用拆分成多个模块，每个模块部署在一台机器上对外提供服务。
         集群指的是将一个应用复制多份 部署在多台机器上，每台机器上的应用程序一样。
         
二  Oracle体系结构
        1 一个数据库；
        2 多个实例  通常是一个实例；
        3 一个实例下可以创建多个用户 。 1个用户相当于一个应用。 这里的用户相当于mysql中的数据库；
        4 一个用户只能设置一个default tablespace,但是通过resource角色授予unlimited tablespace权限，用户将可以使用任意表空间。
                比如创建表的时候，不指定tablespace默认将该表放在default tablespace里面。如果加入tablespace参数 ，就存放在指定的表空间；
        5 表空间是oracle独有的逻辑概念  ， 一个表空间可以包含多个实际的物理数据文件。
        6 用户创建的表对象是随机存储在一个或者多个数据文件上；


三  一个新应用在oracle的创建过程：
        1 需求分析
        2 数据库设计
        3 创建多个表空间
        4 创建用户并授权
        5 创建表
        
        创建表语法：
        CREATE TABLE 表名称(
         字段名 类型(长度) 约束,  
         ...
         字段名 类型(长度)
        );
        修改表语法，主要关注添加字段和修改类型长度：
        ALTER TABLE 表名称 ADD(
         列名 1 类型 [DEFAULT 默认值]
        );
        ALTER TABLE 表名称 MODIFY(
         列名 1 类型 [DEFAULT 默认值]
        )
        
        truncate 与 delete 实现数据删的区别
        1. delete 删除的数据可以 rollback
        2. delete 删除可能产生碎片，并且不释放空间
        3. truncate 是先摧毁表结构，再重构表
        truncate属于ddl delete属于dml
        
        


四   JDBC连接Oracle的4个jdbc工具类迭代
        jdbcutils1    driverclass url username password 写在Java类中
        jdbcutils2    driverclass url username password 写在配置文件中
        jdbcutils3    driverclass url username password 写在配置文件中，并通过连接池来管理连接
        jdbcutils4    通过threadlocal加入事务管理


五  Oracle查询【重点】
        单表查询
        伪列  rowid  rownum  
        rowid 表示记录的物理地址  用rowid查询比主键Id查询更快。直接定位物理地址。
        rownum  用于分页
        五个分组函数 count sum avg max min


        连接查询
        oracle的左外连接特殊写法  在oracle中外连接一般用oracle的特殊写法。
        select
        from a , b
        where a.id = b.id(+)      与 from 后面的a 和 b 没有关系，主要看where中的 = 。
        左外连接： 以左边的表为基准，右边表能匹配上的进行显示，不能匹配上的补NULL。


        子查询 从内向外看
        三种子查询  
        select 后面     select后面的子查询 select xxx from tab 只能有一个字段。  因为select后面的子查询相当于替换select查询中一个字段。     
        from 后面   
        where 后面
        where后面的子查询用exits 替换 in：
        exits的用法扩展：
        select *    from T_OWNERS t
        where exists (select id,name   from t_address ad    where ad.id = t.addressid    and ad.name like '%花园%');
        当子查询中前后表用到了关联，则只查询出满足关联条件的前表中的记录。
        select  *    from T_OWNERS t  where exists (select id  ,name from t_address ad  );
        当子查询中前后表没有关联关系，则根据后表中是否有记录返回，来查询前表中的所有记录。


        分页查询rownum 不能使用 > ; >= ；=
        原因： rownum在表扫描时产生，每次从1开始计数。
        所以当扫描到第一行时，rownum>10 不满足，会将记录丢失，一直扫描下去都会丢掉记录。所以最后查询不出记录。
        带排序的分页查询。 先查询出来数据 最后再加rownum进行排序  eg：
        select * from  
        (select rownum r,t.* from     (select * from T_ACCOUNT order by usenum desc) t        where rownum<=20 )
        where r>10


        单行函数
        字符函数
        substr(从什么位置开始截取，截取的长度)   位置从1开始。有点类似于el表达式中的fn：substr
        需要区分java中substring( begin,end) 从begin开始 到end结束 不包括end  位置从0开始。   
        || 字符串拼接
        length(str) 字符串长度
        数值函数
        round()  四舍五入
        日期函数
        add_months（sysdate，+/- 1） 加减月份
        last_day(sysdate)  所在月最后一天
        trunc(sysdate) 截取日期  去掉小时分钟秒
        trunc(sysdate,'yyyy')  2018-01-01
        trunc(sysdate,'mm')  2018-06-01
        转换函数
        to_char
        to_date
        null判断
        nvl(maxnum,9999);  当maxnum为null时，显示为9999.   当要显示字符串时，将不适用。则可以使用nvl2.
        nvl2（maxnum ，to_char(maxnum)，‘不限’）   将maxnum类型转换为字符串。
        条件取值
        case when  then    sql标准的写法
        case when xx =1 then xxx when xx =2 then yyy  else zzzz end
        decode 是oracle 特有的。 相当于java中的switch分支判断
        decode（比较值，条件1，值1，条件2，值2 ，缺省值）


        行列转换
        case when    xxx   then  xxx  else   end   


        分析函数 三种排名方式
        rank() over( order by  xxx )         值相同，排名相同，排名跳跃
        dense_rank() over()   值相同，排名相同，排名不跳跃
        row_number() over()   无论值是否相同，按顺序排名
        row_number() over() 可以用来实现分页。
        eg：
        select * from  
        (select row_number() over(order by usenum desc ) rownumber,usenum from T_ACCOUNT)
        where rownumber>10 and rownumber<=20


        集合运算
        并集   union  不包含重复记录 /  union all  包含重复记录
        交集   intersect   取相同的记录
        差集   minus   相当于a-b  


六  视图 简化开发
        虚拟表 封装sql
        简单视图
                语法：
                create 【or replace】【force】 view viewname as   --force表示允许创建带错误的视图，如果sql中的表不存在，也可以创建成功。
                sql语句
                【with check option】 --表示该视图不能修改sql语句中的条件字段
                【with read only 】    --表示该视图只能读 不能修改
                eg：
                create or replace view v_address  as
                select * from address where areaid =2
                with check option ;
                update v_address set areaid = 3   。这条update语句不能执行，因为修改了sql中的条件字段areadid
               
        复杂视图
        多表关联视图中的【键保留表】 是指： 视图中保留了主键的表 。 多表关联视图只能更新键保留表中的数据，其它表的数据不能修改。


七  物化视图 提高查询性能
        真实表 封装sql
        语法：
        CREATE METERIALIZED VIEW view_name
        [BUILD IMMEDIATE | BUILD DEFERRED ]     【BUILD IMMEDIATE】 表示立即插入表数据，默认为该方式。【BUILD DEFERRED】表示延迟插入表数据。
        REFRESH [FAST|COMPLETE|FORCE]   【fast】 表示增量刷新  【complete】 表示全量刷新  【force】智能判断选择增量或者全量 。默认为该方式。
        [
        ON [COMMIT |DEMAND ]   【commit】表示基表数据有变动，自动刷新视图。 【demand】表示手动刷新视图  默认是手动刷新视图
        ]
        AS
        sql语句
        
        创建增量刷新物化视图需要先创建物化视图日志
        create materialized view log on t_address with rowid；


八  序列
        简单序列 create sequence  xxxx       nextval  currval
        复杂序列 create sequence xxx increment by x start with  x minvalue x  maxvalue y cycle cache
    循环索引时计算公式：
    ceil( (maxvlue-minvalue)/increment by ) >= cache    cache默认是20
        
九   同义词 别名
    create synonym 别名 for 对象;
    私有 只有创建用户能使用
        公共 所有用户都能使用


十  索引  提高查询性能
        简单索引 create index ind_xxxx on 表名(列名);
        唯一索引
        create unique inde inx_yyyy on 表名(列名)
        构建索引的列唯一不重复时
        复合索引
        create index owners_index_ah on T_OWNERS(addressid,housenumber);
        在sql查询语句中使用时，要注意 addressid 和 housenumber 的顺序要与索引一致。
        反向键索引
        create index ind_xxxx on 表名(列名) reverse;
        当要构建索引的列是连续值使用。不使用反向键则构建歪脖子树，效率低。
        位图索引   create bitmap index ind_xxxx on 表名(列名);
        适合创建在低基数列上。比如性别，民族。
        
十一   PLSQL
         变量申明
         三种方式  v_name varchar2(30) ; v_name t_address.name%type; v_address  t_address%type;
         变量赋值
         三种方式 v_name:='xxx' ; select name into v_name from t_address where id=1; select * into v_address from t_address where id=1;
         需要注意 select into 只能是一行记录。
         异常
         exception
         when  异常类型 then
         xxx；
         分支
                 第一种：
                 if 条件 then
                 xxx;
                 end if;
                 第二种：
                 if 条件 then
                 xxx;
                 else
                 yyy;
                 end if;
                 第三种：
                 if 条件  then
                 xxx;
                 elsif 条件 then
                 yyy;
                 else
                 zzz;
                 end if;
         循环
                不带条件循环
                loop
                        xxxx;
                        exit when 条件;
                end loop;
                带条件循环
                while 条件
                loop
                        xxxx;
                end loop;
                for循环
                for 变量 in 起始值 .. 截止值
                loop
                xxxx;
                end loop;
         游标
                不带参数游标
                declare
                cursor 游标名称  is sql查询语句;
                begin
                        open 游标名称;
                                loop
                                        fetch 游标名称  into 变量; //提取游标
                                        exit when 游标名称%notfound;
                                        xxxx;
                                end loop;
                        close  游标名称;
                end;
                带参数游标
                declare
                cursor 游标名称(参数名称 参数类型)  is sql查询语句; --sql语句中可以直接使用参数
                begin
                        open 游标名称(参数名称);   --open操作时才查询数据库
                                loop
                                        fetch 游标名称  into 变量; //提取游标
                                        exit when 游标名称%notfound;
                                        xxxx;
                                end loop;
                        close  游标名称;
                end;
                for循环简写游标
                declare
                cursor 游标名称  is sql查询语句;
                begin
                        for xxx in 游标名称
                        loop
                        xxxx;
                        end loop;
                end;
               
十二  存储函数
        有return返回值  一般在sql中调用
        create or replace function fn_xxxx(参数名称 参数类型) -- 参数类型不用写长度
        return varchar2    --返回值类型也不用写长度
        is
        变量申明;
        begin
                xxxx;
                return xxx;
        end;
        
十三  存储过程
        无return返回值  但有out类型参数 并可以有多个。 一般通过程序调用  callablestatement  { call p_xxxxx(?,?) }
        create or replace procedure p_xxxx (参数名称 参数类型 )   --参数类型不用写长度
        is
        变量申明;
        begin
                xxxx;   --注意dml操作后需要commit;
        end;
        
十四  触发器
   前置/后置; 行级/语句级   通常写的后置行级触发器
   前置表示操作执行前触发器先执行；后置表示操作执行完成后触发器执行；
   行级表示触发器作用于每一行；语句级表示触发器作用于一块语句；
   create or replace trigger tri_xxxx
   after
   update of name on t_owners
   for each row
   declare
   xxx;
   begin
   yyy;
   end;
   针对行级触发器 有:new  和 :old  

就业班_JavaEE_day32_35_Oracle

一  分布式和集群的区别：
         分布式指的是将一个应用拆分成多个模块，每个模块部署在一台机器上对外提供服务。
         集群指的是将一个应用复制多份 部署在多台机器上，每台机器上的应用程序一样。
         
二  Oracle体系结构
        1 一个数据库；
        2 多个实例  通常是一个实例；
        3 一个实例下可以创建多个用户 。 1个用户相当于一个应用。 这里的用户相当于mysql中的数据库；
        4 一个用户只能设置一个default tablespace,但是通过resource角色授予unlimited tablespace权限，用户将可以使用任意表空间。
                比如创建表的时候，不指定tablespace默认将该表放在default tablespace里面。如果加入tablespace参数 ，就存放在指定的表空间；
        5 表空间是oracle独有的逻辑概念  ， 一个表空间可以包含多个实际的物理数据文件。
        6 用户创建的表对象是随机存储在一个或者多个数据文件上；


三  一个新应用在oracle的创建过程：
        1 需求分析
        2 数据库设计
        3 创建多个表空间
        4 创建用户并授权
        5 创建表
        
        创建表语法：
        CREATE TABLE 表名称(
         字段名 类型(长度) 约束,  
         ...
         字段名 类型(长度)
        );
        修改表语法，主要关注添加字段和修改类型长度：
        ALTER TABLE 表名称 ADD(
         列名 1 类型 [DEFAULT 默认值]
        );
        ALTER TABLE 表名称 MODIFY(
         列名 1 类型 [DEFAULT 默认值]
        )
        
        truncate 与 delete 实现数据删的区别
        1. delete 删除的数据可以 rollback
        2. delete 删除可能产生碎片，并且不释放空间
        3. truncate 是先摧毁表结构，再重构表
        truncate属于ddl delete属于dml
        
        


四   JDBC连接Oracle的4个jdbc工具类迭代
        jdbcutils1    driverclass url username password 写在Java类中
        jdbcutils2    driverclass url username password 写在配置文件中
        jdbcutils3    driverclass url username password 写在配置文件中，并通过连接池来管理连接
        jdbcutils4    通过threadlocal加入事务管理


五  Oracle查询【重点】
        单表查询
        伪列  rowid  rownum  
        rowid 表示记录的物理地址  用rowid查询比主键Id查询更快。直接定位物理地址。
        rownum  用于分页
        五个分组函数 count sum avg max min


        连接查询
        oracle的左外连接特殊写法  在oracle中外连接一般用oracle的特殊写法。
        select
        from a , b
        where a.id = b.id(+)      与 from 后面的a 和 b 没有关系，主要看where中的 = 。
        左外连接： 以左边的表为基准，右边表能匹配上的进行显示，不能匹配上的补NULL。


        子查询 从内向外看
        三种子查询  
        select 后面     select后面的子查询 select xxx from tab 只能有一个字段。  因为select后面的子查询相当于替换select查询中一个字段。     
        from 后面   
        where 后面
        where后面的子查询用exits 替换 in：
        exits的用法扩展：
        select *    from T_OWNERS t
        where exists (select id,name   from t_address ad    where ad.id = t.addressid    and ad.name like '%花园%');
        当子查询中前后表用到了关联，则只查询出满足关联条件的前表中的记录。
        select  *    from T_OWNERS t  where exists (select id  ,name from t_address ad  );
        当子查询中前后表没有关联关系，则根据后表中是否有记录返回，来查询前表中的所有记录。


        分页查询rownum 不能使用 > ; >= ；=
        原因： rownum在表扫描时产生，每次从1开始计数。
        所以当扫描到第一行时，rownum>10 不满足，会将记录丢失，一直扫描下去都会丢掉记录。所以最后查询不出记录。
        带排序的分页查询。 先查询出来数据 最后再加rownum进行排序  eg：
        select * from  
        (select rownum r,t.* from     (select * from T_ACCOUNT order by usenum desc) t        where rownum<=20 )
        where r>10


        单行函数
        字符函数
        substr(从什么位置开始截取，截取的长度)   位置从1开始。有点类似于el表达式中的fn：substr
        需要区分java中substring( begin,end) 从begin开始 到end结束 不包括end  位置从0开始。   
        || 字符串拼接
        length(str) 字符串长度
        数值函数
        round()  四舍五入
        日期函数
        add_months（sysdate，+/- 1） 加减月份
        last_day(sysdate)  所在月最后一天
        trunc(sysdate) 截取日期  去掉小时分钟秒
        trunc(sysdate,'yyyy')  2018-01-01
        trunc(sysdate,'mm')  2018-06-01
        转换函数
        to_char
        to_date
        null判断
        nvl(maxnum,9999);  当maxnum为null时，显示为9999.   当要显示字符串时，将不适用。则可以使用nvl2.
        nvl2（maxnum ，to_char(maxnum)，‘不限’）   将maxnum类型转换为字符串。
        条件取值
        case when  then    sql标准的写法
        case when xx =1 then xxx when xx =2 then yyy  else zzzz end
        decode 是oracle 特有的。 相当于java中的switch分支判断
        decode（比较值，条件1，值1，条件2，值2 ，缺省值）


        行列转换
        case when    xxx   then  xxx  else   end   


        分析函数 三种排名方式
        rank() over( order by  xxx )         值相同，排名相同，排名跳跃
        dense_rank() over()   值相同，排名相同，排名不跳跃
        row_number() over()   无论值是否相同，按顺序排名
        row_number() over() 可以用来实现分页。
        eg：
        select * from  
        (select row_number() over(order by usenum desc ) rownumber,usenum from T_ACCOUNT)
        where rownumber>10 and rownumber<=20


        集合运算
        并集   union  不包含重复记录 /  union all  包含重复记录
        交集   intersect   取相同的记录
        差集   minus   相当于a-b  


六  视图 简化开发
        虚拟表 封装sql
        简单视图
                语法：
                create 【or replace】【force】 view viewname as   --force表示允许创建带错误的视图，如果sql中的表不存在，也可以创建成功。
                sql语句
                【with check option】 --表示该视图不能修改sql语句中的条件字段
                【with read only 】    --表示该视图只能读 不能修改
                eg：
                create or replace view v_address  as
                select * from address where areaid =2
                with check option ;
                update v_address set areaid = 3   。这条update语句不能执行，因为修改了sql中的条件字段areadid
               
        复杂视图
        多表关联视图中的【键保留表】 是指： 视图中保留了主键的表 。 多表关联视图只能更新键保留表中的数据，其它表的数据不能修改。


七  物化视图 提高查询性能
        真实表 封装sql
        语法：
        CREATE METERIALIZED VIEW view_name
        [BUILD IMMEDIATE | BUILD DEFERRED ]     【BUILD IMMEDIATE】 表示立即插入表数据，默认为该方式。【BUILD DEFERRED】表示延迟插入表数据。
        REFRESH [FAST|COMPLETE|FORCE]   【fast】 表示增量刷新  【complete】 表示全量刷新  【force】智能判断选择增量或者全量 。默认为该方式。
        [
        ON [COMMIT |DEMAND ]   【commit】表示基表数据有变动，自动刷新视图。 【demand】表示手动刷新视图  默认是手动刷新视图
        ]
        AS
        sql语句
        
        创建增量刷新物化视图需要先创建物化视图日志
        create materialized view log on t_address with rowid；


八  序列
        简单序列 create sequence  xxxx       nextval  currval
        复杂序列 create sequence xxx increment by x start with  x minvalue x  maxvalue y cycle cache
    循环索引时计算公式：
    ceil( (maxvlue-minvalue)/increment by ) >= cache    cache默认是20
        
九   同义词 别名
    create synonym 别名 for 对象;
    私有 只有创建用户能使用
        公共 所有用户都能使用


十  索引  提高查询性能
        简单索引 create index ind_xxxx on 表名(列名);
        唯一索引
        create unique inde inx_yyyy on 表名(列名)
        构建索引的列唯一不重复时
        复合索引
        create index owners_index_ah on T_OWNERS(addressid,housenumber);
        在sql查询语句中使用时，要注意 addressid 和 housenumber 的顺序要与索引一致。
        反向键索引
        create index ind_xxxx on 表名(列名) reverse;
        当要构建索引的列是连续值使用。不使用反向键则构建歪脖子树，效率低。
        位图索引   create bitmap index ind_xxxx on 表名(列名);
        适合创建在低基数列上。比如性别，民族。
        
十一   PLSQL
         变量申明
         三种方式  v_name varchar2(30) ; v_name t_address.name%type; v_address  t_address%type;
         变量赋值
         三种方式 v_name:='xxx' ; select name into v_name from t_address where id=1; select * into v_address from t_address where id=1;
         需要注意 select into 只能是一行记录。
         异常
         exception
         when  异常类型 then
         xxx；
         分支
                 第一种：
                 if 条件 then
                 xxx;
                 end if;
                 第二种：
                 if 条件 then
                 xxx;
                 else
                 yyy;
                 end if;
                 第三种：
                 if 条件  then
                 xxx;
                 elsif 条件 then
                 yyy;
                 else
                 zzz;
                 end if;
         循环
                不带条件循环
                loop
                        xxxx;
                        exit when 条件;
                end loop;
                带条件循环
                while 条件
                loop
                        xxxx;
                end loop;
                for循环
                for 变量 in 起始值 .. 截止值
                loop
                xxxx;
                end loop;
         游标
                不带参数游标
                declare
                cursor 游标名称  is sql查询语句;
                begin
                        open 游标名称;
                                loop
                                        fetch 游标名称  into 变量; //提取游标
                                        exit when 游标名称%notfound;
                                        xxxx;
                                end loop;
                        close  游标名称;
                end;
                带参数游标
                declare
                cursor 游标名称(参数名称 参数类型)  is sql查询语句; --sql语句中可以直接使用参数
                begin
                        open 游标名称(参数名称);   --open操作时才查询数据库
                                loop
                                        fetch 游标名称  into 变量; //提取游标
                                        exit when 游标名称%notfound;
                                        xxxx;
                                end loop;
                        close  游标名称;
                end;
                for循环简写游标
                declare
                cursor 游标名称  is sql查询语句;
                begin
                        for xxx in 游标名称
                        loop
                        xxxx;
                        end loop;
                end;
               
十二  存储函数
        有return返回值  一般在sql中调用
        create or replace function fn_xxxx(参数名称 参数类型) -- 参数类型不用写长度
        return varchar2    --返回值类型也不用写长度
        is
        变量申明;
        begin
                xxxx;
                return xxx;
        end;
        
十三  存储过程
        无return返回值  但有out类型参数 并可以有多个。 一般通过程序调用  callablestatement  { call p_xxxxx(?,?) }
        create or replace procedure p_xxxx (参数名称 参数类型 )   --参数类型不用写长度
        is
        变量申明;
        begin
                xxxx;   --注意dml操作后需要commit;
        end;
        
十四  触发器
   前置/后置; 行级/语句级   通常写的后置行级触发器
   前置表示操作执行前触发器先执行；后置表示操作执行完成后触发器执行；
   行级表示触发器作用于每一行；语句级表示触发器作用于一块语句；
   create or replace trigger tri_xxxx
   after
   update of name on t_owners
   for each row
   declare
   xxx;
   begin
   yyy;
   end;
   针对行级触发器 有:new  和 :old