Oracle基础

Oracle：
Oracle数据库是美国Oracle公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务端（Client/Service）或B/S体系的数据库之一。
Oracle通常应用于大型系统的数据库产品。
Oracle数据库具有以下的特点：
（1）支持多用户，大量事务的事务处理
（2）数据安全性和完整性控制
（3）支持分布式数据处理
（4）可移植性
Oracle体系结构：
1、数据库
2、实例（Oracle Instance）
3、数据文件（dbf或ora？）
4、表空间（逻辑映射）
5、用户
虚拟机中：sqlplus system/……
本地：sqlplus system/……@192.169.80.10:1521/orcl

创建表空间：
create tablespace waterboss
datafile ‘c:\waterboss.dbf’
size 100m
autoextend on
next 10m

创建Oracle的用户
create user wateruser
indentified by itcast
default tablespace waterboss
用户赋权：
grant dba to wateruser

表的创建，修改，删除
1、创建表
create table 表名（
字段名 类型 (长度) primary key,
字段名 类型（长度），
……
）;

数据类型：
char:固定的字符长度，最多存储2000个字节
varchar2：可变长度的字符类型，最多存储4000个字节
long：大文本类型。最大可以存储2个G
number：数值类型
date:日期时间型，精确到秒
timestamp：精确到秒的小数点后6位
二进制型（大数据类型）：
CLOB：存储字符，最大可以存4个G
BLOB：存储图像、声音、视频等二进制数据，最多4个G

2、修改表：
添加字段语法：alter table 表名 add(列名 类型，……)
修改字段语法：alter table 表名 modify（列名 类型，……）
修改字段名： alter table 表名 rename column 原列名 to 新列名
删除字段名;alter table 表名 drop colume 列名

3、删除表
drop table 表名

数据增删改：
1、插入数据
insert into 表名[(列1，列2……)] values（值1，值2……）；注意：执行insert语句之后一定要进行commit提交事务
2、修改数据：update 表名 set 列名1=值1，……where 修改条件；执行完update之后也要进行commit进行事务提交
3、删除数据：
delete from 表名 where 删除条件；执行完delete语句之后一定要进行commit事务提交
truncate table 表名
比较truncate和delete实现数据删除：
1、delete删除的数据可以rollback
2、delete删除可能产生碎片，并且不能释放空间
3、truncate是先摧毁表结构，再重构表结构

JDBC连接Oracle：
1、创建工程，引入驱动
2、BaseDAO：加载驱动、数据库连接、关闭资源
3、代码编写
JDBC驱动为：
oracle.jdbc.driver.OracleDriver
连接字符串（瘦连接）：
jdbc:oracle:thin:@虚拟机的IP：1521：orcl
数据导出与导入：
整库导出： exp system/…… full=y [file=文件名]
整库导入： imp system/…… full=y [file=water.dmp]
按用户导出：exp system/…… owner=wateruser file=wateruser.dmp
按用户导入：imp system/…… fromuser=wateruser filr=wateruser.dmp
按表导出：
exp wateruser/itcast file=a.dmp tables=t_account,a_area（多个表用逗号隔开）
按表导入;
imp wateruser/itcast file=a.dmp tables=t_account

Oralce查询：
一：单表查询
精确查询：select * from 表名 where 条件
模糊查询：select * from 表名 where name like ‘%李’
and运算符
or运算符
范围查询：1、使用>=,<= 2、使用between .. and..
控制查询：where 字段名 is null(is not null)
去掉重复记录：select distinct 字段 from 表名
排序查询：order by (升序) order by desc （降序）
基于伪列的查询：rowid和rownum
rowid:表中的每一行在数据文件中都有一个物理地址，rowID伪列返回的就是该行的物理地址，rowID可以唯一的标识表中的每一行 语句;select rowID,t.* from 表名 t
rownum:rownum为结果集中每一行标识一个行号，第一行返回1，第二行返回2……
聚合统计：（通过分组函数来实现的，与MySQL一致）
聚合函数：求sum avg max min count
分组聚合：Group by…(having)…
二：连接查询
多表内连接查询：select o.id 业主编号,o.name 业主名称,ot.name 业主类型 from t_owners o,t_ownertype ot where o.ownertypeid=ot.id
左外连接查询：
SQL1999标准语法：select ow.id,ow.name,ac.year,ac.month,ac.money from t_owners ow left join t_account ac on ow.id=ac.owneruuid
Oracle特有语法：select ow.id,ow.name,ac.year,ac.month,ac.money from t_owners ow,t_account ac where ow.id=ac.owneruuid(+)
右外连接查询：+放在左边
三：子查询
①：where子句中的子查询：
（单行）select * from T_ACCOUNT where year=’2012’and month=’01’ and usenum >( select avg(usenum) from T_ACCOUNT where year=’2012’ and month=’01’ )
（多行）select * from T_OWNERS where addressid in ( 1,3,4 ) （可以使用in not in like ）
②：from子句中的子查询：
select * from (select o.id 业主编号,o.name 业主名称,ot.name 业主类型 from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id) where 业主类型=’居民’
③：select子句中的子查询：
select id,name,(select name from t_address where id=addressid) addressname from t_owners
四：分页查询
简单分页：select * from (select rownum r,t.* from T_ACCOUNT t where rownum<=20) where r>10
基于排序的分页：select * from
(select rownum r,t.* from
(select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 )
where r>10
五：单行函数
①:字符函数
求字符串长度的函数：select length(‘ABCD’) from dual;
求字符串的子串 SUBSTR：select substr(‘ABCD’,2,2) from dual;
字符串拼接 CONCAT：select concat(‘ABC’,’D’) from dual; select ‘ABC’||’D’ from dual;
②：数值函数：
四舍五入函数 ROUND：select round(100.567) from dual
截取函数 TRUNC：select trunc(100.567) from dual
取模 MOD：select mod(10,3) from dual
③：日期函数：
select sysdate from dual
加月函数 ADD_MONTHS ：在当前日期基础上加指定的月：select add_months(sysdate,2) from dual
求所在月最后一天 LAST_DAY：select last_day(sysdate) from dual
日期截取 TRUNC：select TRUNC(sysdate,’yyyy’) from dual
④：转换函数：
数字转字符串 TO_CHAR：select TO_CHAR(1024) from dual
日期转字符串 TO_CHAR：select TO_CHAR(sysdate,’yyyy-mm-dd’) from dual
字符串转日期 TO_DATE：select TO_DATE(‘2017-01-01’,’yyyy-mm-dd’) from dual
字符串转数字 TO_NUMBER：select to_number(‘100’) from dual
⑤：其他函数：
空值处理函数 NVL 用法：NVL（检测的值，如果为 null 的值）；
select NVL(NULL,0) from dual
空值处理函数 NVL2 用法：NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , ‘不限’) from T_PRICETABLE where OWNERTYPEID=1
条件取值 decode：select name,decode( ownertypeid,1,’居民’,2,’行政事业单位’,3,’商业’) as 类型 from T_OWNERS
上边的语句也可以用 case when then 语句来实现：
select name,(case
when ownertypeid= 1 then ‘居民’
when ownertypeid= 2 then ‘行政事业’
when ownertypeid= 3 then ‘商业’
end )
from T_OWNERS
六：行列转换

七：分析函数
RANK 相同的值排名相同，排名跳跃
select rank() over(order by usenum desc ),usenum from T_ACCOUNT
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
用 row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:
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 * from t_owners where id<=7
union all
select * from t_owners where id>=5

····Oracle对象
一、视图
创建或修改视图语法
CREATE [OR REPLACE] [FORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION ]
[WITH READ ONLY]

删除视图语法DROP VIEW view_name键保留表：键保留表是理解连接视图修改限制的一个基本概念。该表的主键列全部显示在视图中，并且他们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连接视图中也是键值，那么就称这个表为键保留表。

二、物化视图
物化视图与普通视图的相比的区别是物化视图是建立的副本，它类似与一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一张表是一样的。
创建物化视图语法：
CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE]
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery

手动刷新物化视图：
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS’,’C’);
end;

注意：创建增量刷新的物化视图，必须：
1. 创建物化视图中涉及表的物化视图日志。
2. 在查询语句中，必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志 )

三、序列
create sequence 序列名称
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值
CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中
四、同义词
create [public] SYNONYM synooym for object;
五、索引
1\普通索引：create index 索引名称 on 表名(列名);
2\唯一索引：create unique index 索引名称 on 表名(列名);
3\复合索引：create index 索引名称 on 表名(列名,列名…..);
4\反向键索引：create index 索引名称 on 表名(列名) reverse;应用场景：当某个字段的值为连续增长的值
5\位图索引：create bitmap index 索引名称 on 表名(列名);使用场景：位图索引适合创建在低基数列上
位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射
优点：减少响应时间，节省空间占用

····Oracle 编程：

一、PL/SQL
[declare
– 声明变量
]
begin
– 代码逻辑
[exception
– 异常处理
]
end;

声明变量的语法：
变量名 类型（长度）；

变量赋值的语法：
变量名:=变量值

变量：select 列名 into 变量名 from 表名 where 条件
属性类型：%TYPE 引用型

异常：
NO_DATA_FOUND 使用 select into 未返回行
TOO_MANY_ROWS 执行 select into 时，结果集超过一行
语法结构：
exception
when 异常类型 then
异常处理逻辑
条件判断：
if 条件 then
业务逻辑
elsif 条件 then
业务逻辑
else
业务逻辑
end if;

循环：
无条件循环：
declare
v_num number:=1;
begin
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
exit when v_num>100;
end loop;
end ;
条件循环：
while 条件
loop
end loop;

for循环
基本语法
for 变量 in 起始值..终止值
loop
end loop;

游标：游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们可以把游标理解为 PL/SQL 中的结果集。
在声明区声明游标，语法如下：
cursor 游标名称 is SQL 语句;

使用游标语法
open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
end loop;
close 游标名称

二、存储函数
存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理。
CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, …）
RETURN 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量;
[EXCEPTION
异常处理部分]
END;

三、存储过程
创建或修改存储过程的语法如下:
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;
四、触发器
数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。
触发器分类
? 前置触发器（BEFORE）
? 后置触发器（AFTER）

CREATE [or REPLACE] TRIGGER 触发器名
BEFORE | AFTER
[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
ON 表名
[FOR EACH ROW ][WHEN(条件) ]
declare
……
begin
PLSQL 块
End ；