Oracle总结数据库
--Oracle支持的SQL语言
DDL数据定义语言
DML数据操纵语言（包括事务控制语言）
DCL数据控制语言
--Oracel支持的PL/SQL
过程化查询SQL语言,结合了过程语言的成分
--两种常用的开发工具
SQLPLUS和PL/SQL Develpment
--客户端和服务器端是用net8协议连接（服务器端需要监听服务才能就收到客户端的请求）
--运行命令符中启动sqlplus-----sqlplusw scott/tiger@主机字符串
--每一次客户端和服务器端的连接都建立一个session
--数据字典以三种类型开头
USER当前用户的数据字典
如：USER_TABLES，会自动判断是哪个用户登陆的
ALL数据库管理员使用的数据字典
DBA所有用户的数据字典
--ORACLE数据库服务器由数据库实例和数据库文件组成，提供到客户端会话的数据提供服务
数据库实例，在服务区端管理数据的内存区域（SGA，System Global Area）和服务依赖的后台进程
SGA就是一个缓存，它缓存从数据库文件读取出来的数据
它也包含提供服务的后台进程（比如SGA和数据库文件的数据写入写出进程）
数据库文件，存在于硬盘上的数据文件
--表空间---->表---->段（数据段和索引段）---->区间----->块
表空间分为，系统表空间、用户表空间、临时表空间、工具表空间
--什么是事务，就是一组DML语言的逻辑集合
--DML语句总结
Oracle和java一样使用utf-8字符集
插入日期insert into table values(to_date('2009-01-01','yyyy-mm-dd'));但是他的现实格式不是插入时候的格式，当然可以修改session来修改显示格式
使用绑定变量
insert into table values(&int,'&varchar');会使用具体值替换临时变量
复制表数据
insert into tableB select * from tableA//但是tableB是必须存在的
select * from tableA to tableBl;//在Oracle中不能这样使用
--事务--
多个DML语句组成一个事务
单个DDL或者单个MCL组成一个事务
当单独执行一条insert等DML语句后，Oracle只是将数据写到session级别内存中（其他的回话是看不到该数据的），但是没有写入到具体的数据库文件中
事务中的保存点如
insert into table ......;
savePoint point_one;
update ......;
rollback to point_one;
--select查询语句---
null值的处理，默认情况下和null值的运算结果为null,这在某些情况下是不合理的
比如select NVL(a+b,a) from ...如果a+b的结果为null,则返回a,否则返回a+b
如果列或者表别名中有空格，则使用双引号括起来
在sqlserver中字符串连接使用"+"而oracle使用"||"来连接两个字符串
--oracle中常用函数--
单行函数
字符函数
initcap('good luck')---->Good Luck
concat('good ','luck')---->good luck
substr('good',1,3)--->good(从1开始索引的)
instr('good','d')--->4
lpad('100',5,'*')--->**100
rpad('100',5,'*')--->100**
数值函数
round(1.567,2)--->1.57
round(1.567,0)--->2
round(1.567,-1)--->0
trunc(1.567,2)--->1.56
trunc(1.567,0)--->1
trunc(1.567,-1)--->0
mod(13,3)-->1(求余)
日期函数
返回系统当前时间select sysdate from dual;该方法不能写括号
Month_Between(day1,day2)；日期相差的月份数（day2--day1）
Month_Add(day1,5)；返回day1五个月后的日期
Next_Day(day1,5)；返回day1的下一个星期五（使用数字）
Last_Day(day1)；返回day1在本月中最后一天的日期
Round(sysdate,'MONTH')-->2009-06-01
数据类型转换函数
隐式转换
只要符合逻辑的都能隐式转换，只有日期和数值类型的转换时不符合逻辑的
显式转换
to_Char(number | date)
tox_Number(varchar)
to_date(varchar)
其中在字符和日期双向的转换中都需要使用日期格式字符，如：
to_Char(date,'yyyy-mm-dd');和to_Date(varchar,'yyyy-mm-dd');
decode函数
select decode('lisi','zhangsan','value is zhangsan',
'lisi','value is lisi',
'default','value is other') from dual;
如果第一个参数为zhangsan则返回value is zhangsan
如果第一个参数为lisi则返回value is lisi
如果两个都不是则返回default的值
如果参数个数为偶数则最后一个参数为default
如果参数个数为基础则表示没有default
聚集函数
很简单，max,min,count等等
--服务器端监听(listener)
Listener的职责是迅速建立客户端连接到Oracle数据库的工作，一旦连接NTS建立，Listener便完成使命自动退出
在net manager中配置监听
监听位置设为服务器的IP地址
数据库服务指定全局数据库实例名称和SID
--本地服务名(Tnsname)
本地服务名是Oracle客户端网络配置的一种,使用它能建立和数据库服务器端服务器实例的连接
你可以将多个不同的本地服务名和服务器端数据库实例绑定
conn scott/tiger@后面的东西就是该本地服务名，如果不写的话默认连接到本机的数据库服务器实例上，如果本机没有数据库服务器，而只有客户端软件的话，你当然需要使用本地服务名连接到数据库服务器的数据库实例上
--dual;表通常称为名义表
--数据库的三大范式（只是一总推荐的原则，实际上很多时候还是需要容易字段来提高数据库的查询速度的）
1.字段不可分，它要求如果字段才能在分，就在抽取一个表出来，这样让关系更加明确
2.如果列中出现冗余字段，就意味着应该把表拆分为多个表
3.与主键没有直接关系的数据列必须消除，将它抽取到另一个表里，主力于解决职责单一的问题
--数据库事务的ACID属性
原子性：要么全成功，要么全失败
一致性：这是程序员需要维护的特性，它侧重于解决不可重复读的问题
隔离性：也是程序员或数据库维护人员需要维护的特性，它侧重于解决幻读的问题
持久性：事务完成之后，它对于系统的影响是永久性的，不能恢复的
实际上事务的一致性和隔离性不光光是解决读的问题，它还保证更新，插入，删除的事务原则，我这样解释只是为了去理解它而已
事务的一致性和隔离性是使用数据库隔离级别配合数据库锁的机制来完成的
--oracle中锁的概念（所有的锁的不会限制查看权利，因为查看并不会对并发性有任何影响）
任何一个数据库产品都会为了防止脏读、不可重复读、幻读都会有对应的锁的机制，但是锁的强度越的事务的隔离级别就越严重，数据的并发性访问就越差，所以使用锁需要建立一种则中点上
所按他所作用的范围一般分为
行级锁（解决不可重复读的问题,其他用户不能更新结果集和删除结果集）
update，或者delete的结果集默认加上行级锁
select ..... for update of wait 10 如果我试图访问别人已经锁的结果，但是我又试图去锁定这些结果，那我就会无限期的等待，有了wait子句的话，我等10秒后还不能锁定的话，回滚事务
select ..... for update of colunm 锁定记录的某列，这和默认的行级锁没有区别，为什么会这样呢？原来of是用在多表中锁定指定的表才有用处的奥，，呵呵他不用在单独的列上
结论只能精确到行，这是oracle中的bug
行级别锁是不能共享的，它是排他锁
表级锁（解决幻读的问题）
共享（可能或出现死锁，两个用户都加上了共享锁，并且都试图修改数据，造成永久等待，于是造成死锁，A等B解锁，B也等A解锁，接造成死锁，oracle会自动中断死锁，并回滚事务）
lock table in share mode
能锁住表，但是其他表也能锁定，它的效果在于，如果都锁住了表，不能对表进行DML语句（插删改都不行），只能查询
而行级锁不会限制插入
排他
简单了，lock table in exclusive mode;其他用户只能查看，其他用户不能放置任意锁，不能插入删除修改
还有个共享更新锁，
但是经过自己大量的测试它没有任何作用，这也许就像for update of colunm没有任何作用一样都是oracle的bug(或者在windows操作系统上的问题)
NOWART表示如果目标对象设置了非共享锁或者行级别锁，本次操作立即返回错误信息，而不再等待
--数据库数据类型--
VARCHER2(size)变长字符串，该类型是数据库实际的存储类型，varchar是它的子类型
NVARCHAR2(size)根据字符集而定的可变长度字符串
CHAR(size)定长字符串
NCHAR (size)根据字符集而定的固定长度字符串
NUMBER(5，2)表示最大为999.99,他是int,bigint,float,double等数值类型的父类型
DATE时间类型
LONG超长可变字符串
----还有大型对象类型---
注意：oracle没有货币类型
--DDL语句
alter table t add(columnName type);--添加列
alter table t drop colum columnName;--删除列
alter table t modify(columnNane newType);--修改列
总结添加和修改列都是用方法，而删除使用关键字"drop column"
alter table t set nuesed column columnName;--设置该列不可用，不能恢复，但不是删除
alter table t drop nuesed columns;--删除该无用的列
alter table emp add constraint constraintName primary key(columnName);--增加约束
alter table emp add constraint uq_test unique(ename)--添加唯一约束
alter table t drop constraint constraintName--删除约束
--表分区
当数据量很大的时候使用表分区有如下优势
表的一个分区出现故障不影响其他表的分区
均衡IO，可以将分区表存放在不同的磁盘上
提高检索速度，不用全表收索
一、范围分区法
create table Student(
id int primary key,
name varchar(10) not null,
score int
)partition by range(score)(
partition p1 values less than(60),
partition p2 values less than(80),
partition p3 values less than(maxvalue)//score为null的也放在该区中
);
二、散列分区（使用hash函数），用一套特定的算法，将数据平均的分配到各个表分区中
怎样选择hash的列呢？一般使用主键,使用唯一的标识列
create table Student(
id int,
name varchar(10)
)Partition by hash(id)(
partition p1,
partition p2
);
三、列表分区
通过列的具体值进行分区
create table Student(
id int primary key,
name varchar(10) not null,
score int
)partition by list(name)(
partition p1 values('张','李'),
partition p2 values('王','肖'),
partition p3 values('陈','万')
);
四、复合分区，在范围分区中再次hash分区（或者list分区）
create table Student(
id int primary key,
maxNumber int,
minNumber int,
name varchar(10) not null,
score int
)partition by range(score)
subpartition by hash(maxNumber,minNumber)(
partition p1 values less than(60),
(subpartition sp1,subpartition sp2),
partition p2 values less than(80),
(subpartition sp1,subpartition sp2),
partition p3 values less than(maxvalue)
(subpartition sp1,subpartition sp2)
);
--同义词
公有同义词(管理员用户创建的同义词)
私有同义词(普通用户创建的同义词)
语法：
create synonym synonymName for tableName
drop synonym synonymName
--序列（主键）
创建
create sequence sequenceName
increment by 每次增加几
start with 从几开始
maxValue 最大值 | noMaxValue(默认)
minValue 最小值 | noMinValue(默认)，因为可以设置向下增长，increment by 负数就向下增长了
cycle | noCycle |是否循环
cache 20| noCache(默认)，每次将连续的20个值放入缓存中，下次直接从缓存取值
使用（都是属性）
sequence.nextVal;
sequence.currVal;//只有使用了一次sequence.nextVal()才能初始化序列，将currVal()设置为起始值
--试图
它不存数据，它只是一个查询语句而已（从设计上来讲他就是一种分层思想，也可以理解成一个facade）
强制创建错误视图
create or replace force view as select * from 不存在的table
当试图从没有修正的错误视图的时候，会报出错误的视图错误
查询工资由大到小的前5个员工的信息
select * from (select * from emp order by sal desc) where sal is not null and rownum<=5;
只有继续单表的视图才能通过视图修改和删除记录，但能往视图插东西吗,也可以，只要不违反本数据的各种约束就行
但是关联多表的视图就不能通过视图修改，删除，添加原表的数据了
*视图中如果涉及到两个以上的表.不好直接更新视图,如果只从一个表查数据,则该视图可以更新,
如果视图两个以上的表组成, 要更新视图数据,可以考虑使用替代触发器来更新基表，或者你只能修改视图中的键保留表的值咯
--键保留表
键保留表:复杂视图中出现的表。
如果满足条件: 主键列全部显示在视图中，并且它们的值在视图中都是唯一且非空的.（比如emp和dept的左连接建立的视图，其中emp就叫该视图的键保留表）
可以修改，添加，删除视图中的键保留表的值，而不能修改非键保留表的值,ok
--分区视图
没什么视图分区的概念奥，分区视图只不过将两个数据结构的表用union连接起来(实际上是一种查询技巧而已)
如create view v_name as
select * from tableA
union all
select * from tableB
--强制视图刷新（比如我表的列名表了，如果不刷新视图的话可能会出现问题的）
alert view v_name compile;
--索引
索引不像视图，索引是真实的存在于硬盘的中的物理介质上的
--索引的分类
唯一键索引
create unique index index_name on emp(ename);
表的主键或者唯一列自动创建唯一值索引
在查询语句中如果通过主键ID或者唯一键列查询数据
则先从索引中找数据，然后直接定位到正式数据表
组合索引
create index index_test on emp(empno,deptno);
当经常通过表中的这两列组合来查询数据的时候，需要考虑使用组合索引
在查询语句中如果使用这两列作为条件则使用该索引定位数据
组合唯一索引
唯一索引和组合索引的综合
create unique index index_test emp(empno,deptno);
反向键索引（不常用）
create reverse index ......
普通索引 反向键索引
10001 10001
10002 20001
10003 30001
有些情况下使用反向键索引会提高索引在比较时候的效率（相当于编程语言中的短路特性一样），如上面的例子，只需要去比较第一个位就够了，所以看情况使用
位图索引
列中重复比较多适合创建位图索引
这种索引的原理是将重复的值放在一起，当然放在一起搜索索引的时候就会非常快
--索引组织表（按照索引创建的表），rowid没有任何意义,因为他是按主键唯一标示数据行的
create table t_name (id int,name varchar) organization index;
普通表（也有主键索引） 单独有索引文件 有rowid
索引组织表 没有单独的索引 没有rowid
需要在表中创建主键，在往表中插入数据的时候，存放的数据是按照索引的规则决定存放顺序的
--基于函数的索引
create index index_name on emp(lower(ename));
当在where中常使用函数，则可以考虑使用基于函数的索引，
--键压缩索引（节省磁盘空间而已）
原理，通常使用某项来共享其他项来节省磁盘空间,将被共享的项相同的数据压缩
column1 column2
zhangsan good
zhangsan luck
这种情况可以将这两列建立压缩键索引
create index index_name on emp(column1,column2) compress 1;
----簇（就是表的一部分，面向对象的聚集的概念），节省磁盘空间而已
create cluster cluster_name (
nameInCluster varchar
);
create table t1(
id int,
name varchar
) cluster cluster_name(name);//是表中的列，而不是簇中列
create table t2(
id int,
name varchar
) cluster cluster_name(name);//是表中的数据，而不是簇中列
--用户管理
ORACLE的用户的口令不区分大小写
sys>system>scott
create user userid identifed by password
锁定用户（失效，不能进行任何操作）alter user userName Account lock;
解锁用户（恢复到未锁定状态）alter user userName Account unlock;
用户失效（并没有将用户下的表删除），alter user userName password expire，不可逆的操作
删除用户 drop user userName [cascode];//删除用户和与之关联的所有资源，不使用cascode的话，如果有与用户关联的资源则无法删除用户
--权限管理
授予
grant 权限|角色
如果是权限需要执行on xxx在什么资源上的权限
to User|public
with zhangsan option//将该次授予的权限同时给与zhangsan这个用户
grant all on emp to User//将对emp表上的所有的权限授予User,相当于DBA了
收回
invoke 权限|角色 from User
--角色管理
授予
create role roleName;
grant 权限 to roleName;
grant roleName to User;
收回
invoke 权限 from roleName;
删除
drop role roleName;
--sys,system,scott的默认表空间都是system,默认临时表空间为temp
ORACLE常用工具
sqlplus(查询分析器)
plsql develpment(第三方基于sqlpluse可视化工具)
oracle enterprise manager console(企业管理器)
-------------------------------------PL/SQL-------------------------------------------------
*是过程语言和结构化查询语言的一种集合
*就是在过程语言中嵌入标准的结构化查询语言
*它和sql server的PL/SQL的重大区别在于他支持OOP，但很少用
*原理：将pl/sql语句块交由pl/sql执行引擎
在pl/sql执行引擎中将其分为
1、普通sql语句---->sql执行器
2、过程语言--->过程执行器
*PL/SQL块的组成
declare
---申明区（可选的）
begin
---过程区（必须的）
exception
--异常处理区（可选的）
end;
注意这是一个匿名PL/SQL过程块，匿名块就相当于js中直接写处理语句，存储过程相当于JS中的方法声明，而已，在方法体里面当然不能使用select ....语句，它不会打印出任何东西，而需要使用dbms_output包下面的put_line()方法打印东西出来
所以看看下面的匿名PL/SQL块的例子，熟悉一下哈
declare
name emp.ename%type; //拿到emp.ename的类型作为name的类型
row emp%rowtype; //将一行的类型作为行级变量row的类型
于是可以写select * into row from emp where ......
然后通过row.ename返回列的值
isBoy boolean default false; //oracle中这样定义bool类型
age int :=10; //给初始值的方式
address constant varchar(10) :='北京'; //定义只读变量，当然只读必须要初始化了
begin
age :=20; //这样的方式给变量赋值
select ename,age into name,age from emp where empno=123; //当然是只能是一条记录
dbms_output.put_line(name); //调用dbms_output包的put_line()方法输出消息
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
end;
所有的语句都用";"隔开，end后要有";"
单行注释--
多行注释/* */
-------注意
pl/sql中不能将一个逻辑运算符放在方法的参数里面，只能放在if后面，pl/sql中不能与为"!="而sql中的不等于为"<>"
----if语句
if then
...
elseif then
...
else
...
end if;
----循环语句
方式一
loop
......;
exist then 条件
end loop;
方式二
while 条件 loop
......;
end loop;
方式三
for i in 1..100 loop
......;
end loop;
--------异常和游标的处理--------
---游标(isOpen,rowcount,found,notfound)
它是存在于服务器的，所以他会使用服务器的内存，当你需要对select出来的结果循环处理的时候就需要用到游标
游标分为
静态游标
隐式游标（游标名为SQL,自动打开自动关闭，当DML执行完了他就自动关闭了，所以SQL%isOpen始终为关闭状态）
begin
insert into myTable values(01,'zhangsan');
if SQL%found then//自动开启，当执行了一条SQL语句之后就能使用了
dbms_output.put_line('成功');
dbms_output.put_line(SQL%rowcount);//本次DML所操作的条数
else
dbms_output.put_line('失败');
end if;
end;
显式游标(用户申明，用户打开，用户关闭，在申明的时候就需要关联sql语句)
declare
cursor myCousor is select * from emp;
empRow emp%rowtype;
begin
open myCousor;
loop
fetch myCousor into empRow;
exit when myCousor%notfound;
dbms_output.put_line(empRow.ename);
dbms_output.put_line(myCousor%rowcount);//当前提取的是第几行
end loop;
close myCousor;
end;
动态游标（在运行时可以修改动态游标所依赖的SQL语句）
declare
type refCousor is ref cursor;
myCousor refCousor;
empRow emp%rowtype;
begin
open myCousor for select * from emp;
loop
fetch myCousor into empRow;
exit when myCousor%notfound;
dbms_output.put_line(empRow.ename);
dbms_output.put_line(myCousor%rowcount);
end loop;
close myCousor;
end;
--子程序分为过程和函数--
过程（无返回值）
create or replace procedure 过程名(参数1 in varchar(10),参数2 out varchar,参数3 in out carchar)----注意out 或 in out 不要加入类型的精度
as
变量1 类型;
变量2 类型;
begin
处理输入或者输如输出参数;
然后直接给输出或输入输出参数给值;
在外部程序使用输出或输入输出参数;--要在外部调用的时候使用输出参数,那么输出参数只能有一个
过程中的return 不能跟参数，它表示方法结束
end;
怎样调用过程
1.excute 过程名，如果有参数必须写（），而sqlserver不需要写（）
2.在块中直接调用--支持这两中方式 但是不能在sql语句中调用
函数 （有返回值）
create or replace function 函数名(参数列表--和过程一样) return 随便的变量名 varchar----注意不加精度
as
变量1 类型;
变量2 类型;
begin
......;
return 变量1;
end;
怎样调用呢?
一.在匿名块中调用;
二.在SQL语句中取返回值;--不支持excute调用
所有子程序都不自动提交事务，
所以可以在里面执行commit;
或者在declare部分，执行编译指令为自动提交事务模式pragma autonomous_transaction;
--触发器的使用
DML触发器
create or replace trigger 触发器名 before|after//区别不大
insert[or update][or delete] on column of 表名 [refencing new,old]//可以对表中的列指定触发器，比如当该列的值修改的时候，当然只有行级触发器才能使用:new ,:old
[for each row]//表示行级触发器，每行更新都触发，只有这种模式才能使用:new 或者:old
[when 条件] //如果为true则继续执行触发器，否则不继续，在里面可以使用old或者new,如when(old.sex='男')
[declare]
变量
begin
if inserting then//这种方法一般用在该触发器，insert,update,delete都会触发的情况下
......
if updating then
......
if deleting then
......
end;
替代触发器
替代触发器一般是建立在视图上,它的功能是能够通过视图操作真实数据（就是解决多变连接的视图不能通过视图更新原始数据的情况下）
它没有before after 的概念 所以只需要将DML 的before after 更换成 instead of 就可以了
替代触发器的创建方法
create or replace trigger 触发器名 instead of
insert[or update][or delete] on 视图名 [refencing new,old N,O]//其中refencing表示从命名而已
[for each row]
[when 条件]
[declare]
变量
begin
if inserting then
......
if updating then
......
if deleting then
......
end;
1123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
