Oracle的一些用法

基础：

1.DDL语句：

创建表空间：create tablespace 表空间名  

修改表空间：alter tablespace 表空间  add  datafile ‘文件.dbf’ size 10M 

删除表空间：drop tablespace 表空间  

                     Drop tablespace 表空间  including contents and datafiles

创建用户： create user 用户名  identified by 密码  default tablespace 表空间名

修改用户密码  alter user 用户名  identified by 新密码

修改用户默认表空间： alter user 用户名 default tablespace 新的表空间名

删除用户：drop user 用户名

创建表： create table 表名

修改表,新增字段：alter table 表名  add (.. )   

修改表,修改字段类型：alter table 表名  modify ( ….  )

修改表,修改字段名称：alter table 表名 rename column 旧字段 to 新字段

修改表,删除字段： alter table 表名 drop（字段名,…）

删除表：drop table 表名

用户授权：

系统权限：grant  create session,create table,alter table  to  用户

 对象权限：grant select  on 表名  to 用户名

                   grant select,insert  on 用户1.表名  to 用户2

2、JDBC连接ORACLE：

     驱动：oracle.jdbc.driver.OracleDriver

     连接字符串：jdbc:oracle:thin:@IP:端口号:实例名

3、数据的导出与导入：

   导出：  

         exp  用户名/密码  full=y  file=文件名.dmp   --全库导出

         exp  用户名/密码  owner=用户名  file=文件名.dmp  --按用户名导出

         exp  用户名/密码  tables=表名1,表名2,..  file=文件名.dmp   按表名导出

  导入：

         imp  用户名/密码  full=y  file=文件名.dmp   --全库导入

         imp  用户名/密码  fromuser=用户名  file=文件名.dmp  --按用户名导入

         imp  用户名/密码  tables=表名1,表名2,..  file=文件名.dmp   按表名导入

查询：

1.简单查询：精确查询、模糊查询(like)、范围查询(between...and)、空值查询(null/not null)、排序查询(asc/desc)、去重(distinct)

2.聚合统计：

  聚合函数(sum/avg/max/min/count)、group by、having

3.伪列查询：

  Rowid：存储表中每一行记录的物理地址。使用场景：用来做去重复记录

  Rownum：给查询出来的结果集加个行号。 一般用于分页查询

4.多表查询：内连接和外连接

  方式：1、先确定要查询的字段属于哪个表；2、确定表和表之间的关系

5.子查询：

  相关子查询： 内部的查询不能单独执行，需要依赖外部的查询。  先查询外部，再查内部

  非相关子查询：内部的查询可以单独执行。   先查内部，再查外部

6.分页查询：

  简单分页：

    Select * from  (Select rownum rn,t.* from 表名 t where rownum<=上限值) where rn > 下限值

 基于排序的分页：  select 优先于order by先执行。    先进行排序，再获取rownum

    Select * from (

         Select rownum rn, t.* from

              ( Select * from 表名 order by 字段 desc )  t

                     Where rownum <= 上限值

    )where rn >下限值

7.单行函数：

  字符函数：长度length()  替换replace()  子串substr()

  数值函数：四舍五入round()  截取trunc()  取模mod()

  日期函数：加月add_months()   最后一天last_day()    日期截取trunc()

  转换函数：数字/日期转字符串to_char(numbet类型/date类型, ‘指定格式’)   字符串转日期to_date()   字符串转数字to_number

  空值处理函数： nvl()   nvl2()  

  条件取值：decode(字段名,值1,显示的值1,值2,显示的值2,….,缺省值)        多数值判断

8.报表查询：行列转换(case when... then... else...end)、集合运算（并集union all/union、交集intersect、差集minus）

对象：

1、视图：封装了SQL查询语句的对象

什么时候用视图?

当SQL查询语句比较复杂(有逻辑、代码过长)，可以把SQL封装到视图中，在程序中就直接查询视图

使用视图提供数据表和程序之间向后兼容性。

优点：1.简化数据操作：视图可以简化用户处理数据的方式。

           2.着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。

           3.视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。

           4.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

语法：create [or replace][force] view view_name

          as  subquery(完整的select语句)

          [with check option]   --创建带检查约束的视图

          [with read only]   –创建只读视图

视图分为：简单视图(单表查询、没有聚合函数)、复杂视图(多表查询、有聚合函数)

2、序列：为数据表中的数字类型的主键进行自增长。

序列和表没有关系，是独立表的。  开发中通常一张表对应一个序列对象

 简单序列：create sequence 序列名

获取序列对象的下一个值：nextval

获取序列对象的当前值：currval

利用序列实现主键增长：Insert into 表名(主键列,列2,…) values(序列名.nextval,’数据2’)

序列的语法：

Start with 初始值

Increment by 增长值

Maxvalue  最大值

Minvalue最小值

Cycle 开启循环    默认nocycle

Cache 缓存值    默认20 

在序列开启循环后，需要设置最大值、缓存值(缓存值*增长值)必须小于一轮循环的值(最大值-最小值)

假设：最大值 100     最小值10      增长值10      一轮循环就10个值

  使用默认缓存20      一次取20个值

序列中针对校验缓存和一轮循环的公式： cache * increment < max-min+ increment

结论：不循环的序列，对缓存的值是否满足一次循环值没有限制。

3、同义词：给对象起个别名

语法：create [public]  synonym synonym_name  for  object;

使用场景：1、针对对象的名称过长时，起个简短的名称

                  2、使用公共同义词，解决其它用户访问当前用户下的特定对象。

4、索引：提高SQL查询效率（降序IO次数）。

底层使用树结构存储数据。

ORACLE中使用：B *Tree索引

在ORACLE中，在建立索引树时，是通过字段(列)的值和值的长度来决定是存放树结构的哪个位图。

普通索引：在开发中使用最多的，给表中的某个列建立索引(允许有重复值)。

语法：create index 索引名称 on 表名(列名);

复合索引：给表中的多个列建立索引(允许有重复值)。

语法：create index 索引名称 on 表名(列名,列名.....);

唯一索引：建立索引的列不允许有重复值。

语法：create unique index 索引名称 on 表名(列名);

反向索引：当列中的所存储的数据长度相同且数据大小相近时，避免产生歪脖子树。

语法：create index 索引名称 on 表名(列名) reverse;

例：  200000000001   200000000003

        反向：100000000002   300000000002

 位图索引：当字段经常作为检索条件时，且字段中存在大量的重复数据(低基数列)。使用位图索引

语法：create bitmap index  索引名 on 表名(字段名)。

编程：

1、SQL编程：PL/SQL。

PL/SQL（Procedure Language/SQL）是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力（具有编程能力）。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

PL/SQL可以分为三个部分：声明部分、代码逻辑部分(可执行部分)、异常处理部分。

声明部分：通常就是定义变量。变量语法：变量名   类型(长度);      给变量赋值：变量名:=变量值;select into

代码逻辑部分：程序体  SQL+逻辑。

异常处理部分：处理SQL中异常。预定义异常和用户定义异常

基本语法：

[declare  

   --声明变量

   ]

 begin

   --代码逻辑  

 [exception

     when 异常类型  then 

     --异常处理逻辑

   ]

 end;

2、条件判断：语法一：if 条件 then

                                        业务逻辑

                                       end if;

                        语法二：if 条件 then

                                      业务逻辑

                                      else

                                      业务逻辑

                                      end if;

3、循环：无条件循环语法：loop

                                             --循环语句

                                             end loop;

                  条件循环语法：while 条件/for 变量  in 起始值..终止值

                                           loop

                                           --循环语句

                                           end loop;

4.游标：游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。可理解为PL/SQL中的结果集。

在声明区声明游标的语法：cursor 游标名称 is SQL查询语句;

使用游标语法：

                      open 游标名称

                      loop

                      fetch 游标名称 into变量(行记录)

                      exit  when  游标名称%notfound

                      --逻辑代码

                      end loop;

                      close 游标名称

5、存储函数：用户自己编写的函数(自定义函数)

创建或修改存储函数的语法：

create  [or replace]   function  函数名称

(参数名称 参数类型, 参数名称 参数类型, ...）

return  结果变量数据类型   --函数中的参数类型、返回值类型  不能声明长度

is

  变量声明部分;

begin

  逻辑部分;

  return 结果变量;

[exception

  异常处理部分]

end;

   使用场景：当遇到复杂的SQL语句

例：查询表中的”密码”字段，此时需要对密码进行加密，可以使用存储函数

（在存储函数中进行再次密码加密）。

6、存储过程：提高程序的安全性。   当把SQL封装到存储过程后，SQL就为预定义SQL。

创建或修改存储过程的语法：

create [or replace]  procedure  存储过程名称

 （参数名 类型, 参数名 类型, 参数名 类型）

is|as

  变量声明部分;

begin

  逻辑部分

[exception

  异常处理部分]

end;

使用场景：

1. 当需要提升程序的安全性时，可以使用存储过程
2. 当SQL语句需要进行大量的逻辑操作时：方式1：使用程序，方式2:存储过程

7、触发器：可以自动执行程序（当触发某个动作时）。

分类：

1.前置触发器（BEFORE）   在没有commit之前执行

2.后置触发器（AFTER）    在commit之后执行

语法：

CREATE  [or REPLACE] TRIGGER  触发器名

   BEFORE | AFTER

   [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名,列2]]

   ON  表名

   [FOR EACH ROW  ]

declare

    ……

begin

   PLSQL 块

end ；

使用场景：

  1、数据二次校验。  当表中的数据插入完后，再次对表中的某些数据进行校验。

  2、日志记录。  当对表进行增删改操作时，在日志表中记录增删改的信息。

  3、数据的备份和同步。

ORACLE也可以对文件进行读写。