oracle基础

前言 正文 Oracle客户端配置 如果安装了服务器端，则不需要再安装客户端 要访问远程服务器上的oracle，需要配置网络服务名 用记事本打开oracle安装目录下的product\10.2.0\db_2\network\ADMIN\tnsnames.ora文件，加入远程数据库信息 注意 :oracle默认端口为1

前言

正文

Oracle客户端配置

如果安装了服务器端，则不需要再安装客户端

要访问远程服务器上的oracle，需要配置网络服务名

用记事本打开oracle安装目录下的product\10.2.0\db_2\network\ADMIN\tnsnames.ora文件，加入远程数据库信息

注意:oracle默认端口为1521,根据需要修改

登录

这里用scott用户登录数据库，密码oracle

在命令行输入: sqlplus用名名/密码@网络服务名

(网络服务名的配置参考上面)

Oracle体系结构

数据库和实例

?数据库

Database:物理操作系统文件或磁盘(disk) 的集合

?实例

instance:一组后台进程/线程和一个共享内存区

实例只能装载并打开一个数据库

一个数据库可以由一个或多个实例(使用RAC)装载并打开

连接到Oracle

1.专用服务器

为连接创建一个新的进程，客户端与这个专用服务器直接通信，并由这个专用服务器接收和执行SQL

2. 共享服务器

使用“共享进程”池为大量用户服务，实际上就是一种连接池机制。客户端不能直接与共享服务器通信，调度器将客户请求放入请求队列，由共享服务器处理后，把响应放入原调度器的响应队列中，最好调度器把响应传回给客户

文件系统机制

?操作系统(OS)文件系统:

文件存放在文 件系统中，操作系统中可以查看到这些文件

?原始分区(raw partitions,也称裸分区):

不是文件，而是原始磁盘，没有文件系统

?自动存储管理(Atuomatic Storage Management,ASM):

Oracle 10g的新特性，ASM是专为数据库设计的文件系统

?集群文件系统:

专用于RAC(集群)环境，集群中的多个节点共享的cooked文件系统

数据库可能包含上述所有文件系统中的文件，不必只选择一个

逻辑存储结构

Oracle存储层次体系

1.数据库由一个或多个表空间组成

2.表空间是一个逻辑存储容器，由一个或多个数据文件组成，表空间包含段

3.段就是占用存储空间的数据库对象，由一个或多个区组成，段在表空间中，但是可以包含这个表空间中多个数据文件中的数据

4.区是文件中一组逻辑连续的块，区段只在一个表空间中，而且总是在该表空间内的一个文件中

5.块是数据库中最小的分配单位，也是数据库使用的最小I/O单位

?段(segment):

段就是占用存储空间的数据库对象，如表、索引、回滚段等。创建表时，会创建一个表段。创建分区表时，每个分区会创建一个段。创建索引时，会创建一个索引段。依次类推，占用存储空间的每一个对象都会存储在一个段中，此外，还有回滚段(rollback segment)、临时段(temporary segment)、聚族段(cluster segment)、索引段(index segment)等。

?例子：

create table T(id int primary key, content clob)

这里创建了四个段

a) 1个Table T的段

b) 1个索引段(主键会自动添加唯一索引)

c) 2个clob段(一个clob段是lob索引,一个段是lob数据本身)

?区(extent)

区是文件中一个逻辑上连续分配的空间，即由一些连续分布的块组成，最大可以达到2GB

?块(block)

块是Oracle中最小的空间分配单位。数据行、索引条目或临时排序结果就存储在块中，通常Oracle从磁盘读写的就是块。块常见4种大小:2KB,4KB,8KB,16KB(有些情况下32KB也可以，受操作系统限制)

注：

?字典管理的表空间

由字典表管理区(extent)的分配,需要串行的查询、更改字典表，来获得空间

?本地管理的表空间

使用每个数据文件中存储的一个位图来管理区，需要得到一个区，系统只需在位图中将某一位设置为1，释放空间，再设置为0

?默认表空间

数据库默认有SYSTEM、SYSAUX、TEMP三个表空间

?创建表空间

create tablespace tbs_datdatafile'c:\oradata\tbs_dat.dbf' size 2000M;

注意要用拥有create tablespace权限的用户，比如sys

?扩充表空间大小

1.添加数据文件

alter tablespacetbs_datadddatafile 'c:\oradata\tbs_dat2.dbf' size100M;

2.改变数据文件大小

alter database datafile 'c:\oradata\tbs_dat.dbf' resize 2500M;

3.数据文件自动扩展大小

alter database datafile 'c:\oradata\tbs_dat.dbf' autoextend on next 1m maxsize 20m;

?修改表空间名称

alter tablespace tbs_datrename totbs_dat1;

?删除表空间

drop tablespacetbs_datincluding contents anddatafiles;

注意:and datafiles，表示同时也删除物理文件

物理存储结构

物理存储结构主要是指在操作系统中，Oracle数据的存储和管理方式。它的组成包括：

?数据文件(data file)

存储表、索引等实际数据的文件.一个表空间，可以有多个数据文件，一个数据文件，只能属于一个表空间

?控制文件(control file)

存储数据库的物理结构等信息的文件。

?重做日志文件(redo file)

记录数据库的修改操作和事务操作的文件

?其他文件

?系统全局区(Sytem Global Area),每个实例都只有一个SGA区。当多个用户连接到同一实例时，这些用户进程、服务进程共享SGA区。包括:

a)数据高速缓存区

b)字典缓存区

c)重做日志缓存区

d)SQL共享池

程序全局区PGA（PROCESS GLOBAL AREA）是一个内存区，包含单个进程的数据和控制信息，所以又称为进程全局区。

锁

?什么是锁

锁(lock)机制用于管理对共享资源的并发访问

?锁定数据行

select * from emp where emp.id=1

for update nowait

这样就锁定了emp表中id=1的那行数据

注意：通过for update锁定后，这些行不能修改了，但是还可以查询

for update和for update nowait

使用for update锁定行，对这行执行update,delete,select .. for update语句都会阻塞，即等待锁的释放后继续执行
使用for update nowait锁定行，对这行执行update,delete,select .. for update语句，会马上返回一个“ORA-00054:resource busy”错误，不用一直等待锁的释放后继续执行.

锁类型
1.DML锁(DML lock):用于确保一次只能修改某一行，而且别人不能删除你正在处理的表, DML锁包括
a)TX锁(事务锁),事务发起第一个修改时会得到TX锁，而且会一直持有这个锁，直至事务commit或者rollback
b)TM锁,用于确保在修改表的数据容时，表的结构不会改变,当更新了一个表的数据时，你就会得到这张表的一个TM锁

2. DDL锁,DDL操作过程中会自动为对象加DDL锁,保护这些对象不会被其它会话修改

3. latch,这是Oracle的内部锁，用于协调对其共享数据结构的访问

悲观锁的问题
1. 悲观锁性能差,不能并发操作，只能排队等待处理。实际上，查询的操作完全可以并发处理的。
2.可移植性差,依赖于特定数据库，而且并不是所有数据库提供悲观锁。

悲观锁的优点
数据库级别的解决办法，从而有效的保证的数据的正确性;通过各种途径操作数据库(java项目，pl/sql developer…)，都会得到很好的保护。

乐观锁
1.给表加一个version字段,保存数据行的版本
2.查询时，得到version的值，假设为100
3.通过类型下面语句保存
update emp set value=500
where id=1 and version=100
如果更新条数等于1，说明保存成功!
如果更新条数等于0，说明保存失败，说明有其它用户修改了这条记录
4.如果保存成功，更新version值加1

事务

是包含一系列步骤的完整操作。

1.原子性(atomicity):事务的所有步骤，要么都成功，要么都失败
2.一致性(consistency):事务将数据从一种一致状态转变为下一种一致状态
3.隔离性(isolation):是个事务的影响，在该事务提交之前对其它事务都不可见
4.持久性（durability):事务一旦提交，其结果就是永久性的

事务控制语句
隐含地，事务在修改数据的第1条SQL语句处自动启动
需要显式使用commit和rollback来终止事务，注意：rollback to savepoint不会结束事务
commit:提交事务，将事务期间所做修改保存
rollback:回滚事务，撤销事务期间所做的修改
savepoint:在事务中创建“标记点”，可以回滚到这些标记点
rollback to :回滚到标记点,而不回滚标记点之前的修改
set transaction:设置事务属性,如事务的隔离级别以及事务是只读还是可读写的。

表

由行和列组成，也称为二维表

记录：表中一行，称为一条记录
字段：构成记录的各数据项，比如姓名、性别

约束

主键约束
---添加主键
alter table EMP add constraint pk_emp_id primary key (EMP_ID);

唯一约束
alter table EMP add constraint uq_emp_code unique (EMP_CODE);

外键约束
alter table EMP
add constraint fk_dept_id foreign key (DEPT_ID)
references dept (DEPT_ID);

oracle自动为主键和唯一约束创建索引。

索引

包含以下的类型：
标准索引(B*树)
数据量非常大的情况下，查找依然很快
惟一索引(Unique Index)
比如员工编号,唯一索引查找最快
位图索引(Bitmap)
适合基数小的字段，比如性别,节约空间
基于函数的索引(FBI)

创建标准索引
create index IDX_DEPT_NAME
on DEPT (dept_name);
创建唯一索引
create unique index IDX_DEPT_CODE
on DEPT (dept_code);
创建位图索引
create bitmap index IDX_EMP_SEX
on EMP (sex);
创建函数索引
create index IDX_EMP_BDATE
on EMP (TO_CHAR(B_DATE,'YYYY-MM-DD'));

哪些字段建议建立索引呢？:1.表间关联字段(外键)2.查询的字段3.group by的字段4.order by的字段
select emp.e_mail,count(*) ct
from emp
join dept on emp.dept_id=dept.dept_id
where dept.dept_name = 'IT'
group by emp.e_mail
order by emp.e_mail

身份证这类唯一属性，应建唯一索引

性别,只有男、女、未定等少数几种状态值，应创建位图索引，位图索引更节约空间

对字段使用函数，会停用索引，可创建函数索引

索引的优缺点
优点:某些情况下，数据查找快
缺点:
a)在通过索引查找，返回结果比较多的情况下，由于需要占用非常多的磁盘I/O，这时全表扫描比索引查找更快
b)索引占用空间惊人，甚至超过表数据所占空间,不利于管理。
c)创建索引后，会降低插入，修改，删除等操作的效率。

分区

分区就是把表和索引分成几大块，每一块存放到一个表空间上，性能调优的重要手段。
有三种分区方式
1.散列分区
均匀分布数据，i/o设备负担均衡。
2.范围分区
按数据值的范围进行分区，比如将员工信息表，按入职时间分区，06年一个区，07年一个区，08年一个区,现在我要找一个06年入职的员工，只需要扫描06年那个分区，时间会快很多，磁盘i/o也会减少

3.复合分区
范围分区和散列分区结合起来使用，先把数据按范围分区，然后在每个分区内再使用散列分区，把数据均匀分布

范围分区例子
create table EMPS
(
SALARY NUMBER(24,4) not null,
EMP_ID NUMBER(24) not null
)
partition by range (SALARY)
(
partition P_SALARY_2000 values less than (2000),
partition P_SALARY_3000 values less than (3000)
);

数据类型

.binary_float:32单精度浮点数，oracle 10g开始提供
binary_double:64位双精度浮点数,oracle 10g开始提供

oracle常用函数

字符函数:

upper(str) ,转为大写
lower(str),转为小写
substr(str,n,m) ,从n位开始，截取m个字符
substr(str,n),从n位开始，截取后面字符
length(str),得到字符串的长度
ltrim(str),去掉左边空格
rtrim(str),去掉右边空格
instr(str,c),得到字符c在str的位置
lpad(str,n,c),将str补足为n位长度,不足左边用字符c代替
rpad(str,n,c),将str补足为n位长度,不足右边以字符c代替

数值函数
round(col,n) 四舍五入
round(457.628,2),小数点后2位四舍五入
结果 457.63
round(457.628,-1),小数点前1位四舍五入
结果460
trunc(col,n) 截断数值
trunc(457.628,2) 结果457.62
trunc(457.628,-1) 结果450

日期函数
months_between(date1,date2),两个日期间的月数，结果为实数
add_months(date,m),增加m个月，m可以为负数，结果为减少m个月
round,日期四舍五入
trunc,截断日期
last_day ,当月最后一天

日期函数例子
当前日期增加1个月
select add_months(sysdate,1) from dual;
去年同月
select add_months(sysdate,-12) from dual;
得到年初
select trunc(sysdate,'YYYY') from dual;
得到月初
select trunc(sysdate,'MM') from dual;
精确到天，截断小时分秒
select trunc(sysdate) from dual;
当月最后一天
select last_day(sysdate) from dual;

转换函数
日期转为字符:
to_char(date1,’format_model’)

format_model:转换后的显示格式
YYYY 年,MM 月,DD 日,HH24 小时,MI 分,SS 秒

例子:
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') rq
from dual;

转换函数
字符转为日期
to_date('2007-11-11','YYYY-MM-DD')

数值转为字符
select to_char(55676,'fm99,999.00') from dual
fm表示去掉前面的空格和0
结果: 55,676.00

基本查询

null ,表示不确定，包含null值的算术运算，结果都为null
字符连接用 ||
别名，以空格 或者as连接，如果别名包含空格或者区分大小写，需要用双引号
判断null值，is null 和 is not null
like 通配符，%代表零或多个字符,下划线_代表单个字符
日期类型加整数，表示加几天,两个日期类型相减，结果为天数
sysdate为当前日期时间

order by 排序
select swjg_dm dm,swjg_mc
from dm_swjg
order by swjg_dm
也可以使用别名 order by dm
还可以使用列的位置 order by 1

排序的时候，null值最大

聚合函数

avg,sum,max,min,count
除了count(*)之外，其它的不统计null值

count(*) ,所有行数量
count(swjg_mc),swjg_mc非null值的记录的数量
count(distinct swjg_dm),去掉重复的记录
count(1),第一列非null值的记录的数量

聚合函数，不能出现在where子句中，
比如where avg(salary) >4000

group by
select 列表 中的非聚合函数列，都必须出现在group by子句中
但是，group by子句中的列，不一定要出现在select列表中
group by 可以使用表达式,但不可以使用别名

rollup
rollup和group by一起使用
用来产生各分组的小计以及最后的合计
例：统计税务机关的入库数，并添加合计
select swjg_dm,sum(se) se from sb_zsxx group by rollup(swjg_dm)

grouping函数
用于判断是否由rollup产生的
group(swjg_dm)=1,表示由rollup产生
select case
when grouping(swjg_dm) = 1 then '合计'
else swjg_dm
end swjg_dm,
sum(se) se
from sb_zsxx
group by rollup(swjg_dm)

条件表达式

Case 表达式
语法:
case 表达式 when 值1 then 结果1
when 值2 then 结果2
…
else 默认结果
end

第二种方式
case when 条件1 then 结果1
when 条件2 then 结果2
…
else 默认结果
end

例子，交叉报表 select name, sum(case kechen when '语文' then fengshu end) yuwen, sum(case kechen when '数学' then fengshu end) shuxue, sum(case kechen when '化学' then fengshu end) huaxue from table group by name

Decode函数
Decode(表达式, 条件1,结果1,
条件2,结果2,
…
,默认结果)

用于解决一般SQL很难完成的问题
函数(参数) Over (partition by col_list
order by col_list)

elect zsxm_dm,
sum(se) se, --收入
sum(sum(se)) over() zse, --总收入
round(sum(se) * 100 / sum(sum(se)) over(), 2) || '%' bz --比重
from sb_zsxx
group by zsxm_dm

左连接

select e.emp_id,e.emp_name,d.dept_name
from emp e,dept d
where e.dept_id =d.dept_id(+)

给返回结果加上序号
rownum伪列，可以给返回结果加上序号
但是，序号是order by之前分配的，所以如果有order by,最后的序号是乱的!
加一个子查询可以解决这个问题
select t.*, rownum
from (select swjg_dm, swjg_mc
from dm_swjg
order by swjg_dm ) t

WITH子句
With子句，使用多个子查询，每个子查询的结果放入用户临时表中，方便使用及性能的提高
with
bq as (select swjg_dm,se …),--本期入库
qs as (select swjg-dm,se …)--欠税
select bq.swjg_dm,bq.se,qs.se
from bq,qs
where bq.swjg_dm=qs.swjg_dm

Update/Delete语句中使用子查询

Update table set col=(sub_query)
Where col = (sub_query)

Delete from table
Where col=(sub_query)

遍历树形数据

select [level],col1,col2..
from table
start with 条件
connect by prior 条件

level ,伪列，每条记录的层级

例子
递归查询鞍山市局的所有下级税务机构
select level, swjg_dm, swjg_mc
from dm_swjg
start with swjg_dm = '22103000000'--鞍山市局，往下找
connect by prior swjg_dm = sj_swjg_dm

还可以加where过滤一些节点
返回辽宁省局下面所有县级税务机构
select swjg_dm, swjg_mc
from dm_swjg
where yxws = 7 --县级
Start with swjg_dm = '22100000000' --省局开始
connect by prior swjg_dm = sj_swjg_dm

直接操作外部数据库

1.建立database link
create database link test_link --链接名
connect to 用户名 identified by 密码
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.30.186)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ctaiscs) --oracle9i以后推荐使用全局数据库名
)
)'
2.删除database link ,drop database link test_link
然后就可以使用外部数据库了，
比如select * from dm_swjg@test_link

2. 远程数据库导入数据的例子
Create or replace procedure test_proc
IS
Begin
for x in (select * from dj_nsrxx@test_link)
loop
insert into dj_nsrxx values x;
end loop;
commit;
end test_proc

查询结果批量插入表中
inert into table(col1,col2,…)
select col1,col2,…
以查询结果创建表
create table myTable
as
select * from dm_swjg

总结