oracle知识

来源：互联网发布：金属小手提箱淘宝编辑：程序博客网时间：2024/05/23 01:16

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

介绍: 数据库，数据表，记录，字段，和对数据表的操作（create,alter,drop）

oracle@192.168.136.128's password:

Last login: Fri Nov 26 18:15:50 2010 from192.168.136.1

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0Production on Mon Nov 29 06:51:02 2010

SQL> startup

ORA-01031: insufficient privileges

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1336960 bytes

Variable Size 264243584 bytes

Database Buffers 150994944 bytes

Redo Buffers 6094848 bytes

Database mounted.

//查询oracle的用户

select username,account_status fromdba_users;

//一般用于演示的scott用户

USERNAME ACCOUNT_STATUS

--------------------------------------------------------------

SCOTT EXPIRED & LOCKED

因为scott用户是锁定

对scott用户进行解锁

alter user scott account unlock;

设置 scott用户密码为tiger

alter user scott identified by tiger;

//连接到scott用户

SQL>conn scott/tiger

Connected

//查看当前是哪个用户

SQL>show user

USER is "SCOTT"

//查看当前用户下的表名

select table_name from user_tables;

======================================================

新建一个数据表 xue_sheng

SQL>create table xue_sheng(idinteger,xing_ming varchar(25));

查看表的字段和数据类型，DESC 表名

SQL> DESC xue_sheng;

SQL> insert into xue_shengvalues(1,'ZhanSan');

SQL> insert into xue_shengvalues(2,'LiSi');

SQL> insert into xue_shengvalues(3,'WangXiaoEr');

SQL> select * from xue_sheng;

ID XING_MING

---------- -------------------------

1 ZhanSan

2 LiSi

3 WangXiaoEr

//添加一个年龄字段的操作

SQL> ALTER TABLE xue_sheng ADD nian_lingnumber;

//删除表中某个字段(年龄)的操作

SQL> ALTER TABLE xue_sheng DROP COLUMNnian_ling;

//删除xue_sheng表

SQL> DROP TABLE xue_sheng;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

对数据记录的操作(select,insert,update,delete)

常用函数(count,max,min,avg,sum,decode,distinct)

SQL>create table xue_sheng(idinteger,xing_ming varchar(25),nian_ling number);

SQL> insert into xue_shengvalues(1,'ZhanSan',24);

SQL> insert into xue_shengvalues(2,'LiSi',23);

//查询

SQL> SELECT * FROM xue_sheng;

//插入数据

SQL> insert into xue_shengvalues(3,'WangXiaoEr',25);

SQL> insert into xue_sheng(id,nian_ling)values(4,25);

//查找order by desc（降序）或者asc（升序）排序

SQL> SELECT * FROM xue_sheng ORDER BYnian_ling DESC; ASC

//查找字段为空或者非空

SQL>SELECT * FROM xue_sheng wherexing_ming IS NULL; IS NOT NULL

//过滤重复字段

SQL> SELECT DISTINCT nian_ling FROMxue_sheng;

//更新表字段

SQL> UPDATE xue_sheng SETxing_ming='ZhanWu';//注意此处的使用没有用到条件，更新的整个表

SQL> UPDATE xue_sheng SETxing_ming='LiSi' where id=2;

-------------------------------------------------------------------------------------------------

删除，需要注意DELETE FROM 表名，表示把表的数据全部清空

所以我们使用一般带上条件 where 例如：

SQL> DELETE FROM xue_sheng where id= 4;

===========================================================

一些常用函数

SQL> select count(*) from xue_sheng;

SQL> select sum(nian_ling) fromxue_sheng;

SQL> select max(nian_ling) fromxue_sheng;

SQL> select min(nian_ling) fromxue_sheng;

SQL> select avg(nian_ling) fromxue_sheng;

DECODE函数使用，可以理解成是一个判断分类函数

SQL> SELECT SUM(DECODE(nian_ling,25,1,0)),SUM(DECODE(nian_ling,24,1,0)) FROM xue_sheng;

SQL> INSERT INTO xue_sheng(id,nian_ling)values(5,25);

SQL> SELECT SUM(DECODE(nian_ling,25,1,0)) n_25,SUM(DECODE(nian_ling,24,1,0)) n_24 FROM xue_sheng;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

内容：学习分组查询group by, 模糊查询/搜索like, 表连接join on, 子查询in() / not in()

1. 分组查询

create table xue_sheng(id integer,xing_mingvarchar(25),xing_bie number,fen_shu number,b_id integer);

insert into xue_shengvalues(1,'ZhanSan',1,80,1);

insert into xue_shengvalues(2,'LiSi',1,90,2);

insert into xue_shengvalues(3,'ZhanHong',0,75,2);

insert into xue_shengvalues(4,'ChenXiaoMing',1,85,1);

查询要求：分组显示男女同学的总分(先把性别分组，然后进行一个求和的统计)

SELECT xing_bie,sum(fen_shu) FROM xue_shengGROUP BY xing_bie;

=============================================================================

2. 模糊查询或者模糊查找

使用LIKE关键字，通用字符'%'

select * from xue_sheng where xing_minglike 'Zhan%';

select * from xue_sheng where xing_minglike '%g';

select * from xue_sheng where xing_minglike '%a%';

==========================================================================

3. 表连接

新建一个班级表：ban_ji

create table ban_ji(id integer,ban_jivarchar(25));

insert into ban_ji values(1,'1-(1)');

insert into ban_ji values(2,'1-(2)');

insert into ban_ji values(3,'1-(3)');

学生表，班级表一起查询

别名的使用

select x.id,xing_ming,ban_ji from xue_shengx,ban_ji b;

select x.id,x.xing_ming,b.ban_ji fromxue_sheng x,ban_ji b where x.b_id = b.id;

select x.id,xing_ming,ban_ji from xue_shengx join ban_ji b on x.b_id=b.id;

=======================================================================

子查询 IN() 或者 NOT IN() ,又叫嵌套查询

SELECT * FROM xue_sheng where b_id IN(1,3);

SELECT * FROM xue_sheng where b_id=1 ORb_id=3;

两条语句相似的效果

--------------------------------------------------------------------------------------------------------------

SELECT * FROM xue_sheng where b_id IN(SELECT id FROM ban_ji); -->( orderby id desc)

//显示在1-(2)班级的所有同学

select * from xue_sheng where b_idin(select id from ban_ji where ban_ji='1-(2)');

做一个分解步骤来理解

第一步骤先执行select id from ban_ji where ban_ji='1-(2)';

第二步，在执行 select * from xue_sheng where b_id in(第一步结果);

NOT IN() 的使用

select * from xue_sheng where b_id notin(select id from ban_ji where ban_ji='1-(2)');

======================================================================

总结一下，配合上面的例子，然后写一下语句，查询出来的结果，进行分析理解。慢慢来熟悉这些语句

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

内容：视图的介绍和使用

表和视图的区别，表是占用硬盘空间物理表，而视图可以理解为一个虚表，并不存储在硬盘上，

不占用硬盘空间，实际上就是一个查询语句，方便查询。

对视图里面的数据操作（增删改）其实就是对真实的表增删改，它们始终保持一致性。

哪为什么还需要视图？

视图可以理解成一个封装过的表，例如不让用户清楚知道表的某些字段信息，比较安全。

===========================================================================

create table xue_sheng(id integer,xing_mingvarchar(25),xing_bie number,fen_shu number,b_id integer);

insert into xue_shengvalues(1,'ZhanSan',1,80,1);

insert into xue_shengvalues(2,'LiSi',1,90,2);

insert into xue_shengvalues(3,'ZhanHong',0,75,2);

insert into xue_shengvalues(4,'ChenXiaoMing',1,85,1);

增加一个视图：

SQL> CREATE VIEW xs_view AS SELECT *FROM xue_sheng;

CREATE VIEW xs_view AS SELECT * FROMxue_sheng

ERROR at line 1:

ORA-01031: insufficient privileges scott没有创建视图的权限

SQL> conn /as sysdba;

SQL> grant connect,dba to scott;

Grant succeeded.

SQL> conn scott/tiger;

SQL> CREATE VIEW xs_view AS SELECT *FROM xue_sheng;

SQL> INSERT INTO xs_view(id,xing_ming)values(5,'test');

SQL> select * from xs_view;

操作视图，其实是操作真实的表中

CREATE OR REPLACE的使用和设置视图的权限 WITH READ ONLY 只读

修改原来的视图，其实就是做一个替换

（如果 xs_view 视图存在要用 'OR REPLACE' 才能替换）

CREATE OR REPLACE VIEW xs_view AS SELECT *FROM xue_sheng WITH READ ONLY;

测试一下设置好的视图

SQL> INSERT INTO xs_view(id,xing_ming)values(6,'test2');

INSERT INTO xs_view(id,xing_ming)values(6,'test2')

ERROR at line 1:

ORA-42399: cannot perform a DML operationon a read-only view

提示这是一个 read-only view 只读视图

==============================================================================

CREATE OR REPLACE VIEW xs_view AS SELECT *FROM xue_sheng WHERE fen_shu >= 80;

SELECT * FROM xs_view;

这样使用视图比较方便。

-----------------------------------------------------------------------------------------------------------------------------

create table ban_ji(id integer,ban_jivarchar(25));

insert into ban_ji values(1,'1-(1)');

insert into ban_ji values(2,'1-(2)');

insert into ban_ji values(3,'1-(3)');

select x.id,xing_ming,ban_ji from xue_shengx join ban_ji b on x.b_id=b.id;

建立一个简单的视图，取代复杂的查询语句

create or replace view xs_view as selectx.id,xing_ming,ban_ji from xue_sheng x join ban_ji b on x.b_id=b.id;

select * from xs_view;

显示视图的字段和数据类型

desc xs_view;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

内容：存储过程PROCEDURE(介绍，输入，输出参数，使用，维护)

存储过程 - 执行一个任务，改任务包括了一系列的PL SQL语句，存储在数据库中，成为数据库的一个对象。

- 效率比较高的，但你创建一个存储过程它会进行一个判断编译的。

============================================================================

创建一个简单的存储过程

SQL> CREATE OR REPLACE PROCEDURE xs_proc

2 IS

3 BEGIN

4 NULL;

5 END;

6 /

如何执行：

SQL> execute xs_proc;（在一些没有输入输出的参数里面使用该方式方便）

PL/SQL procedure successfully completed.

或者执行

（如果有输入参数要用到）

SQL> BEGIN

2 xs_proc;

3 END;

3 /

PL/SQL procedure successfully completed.

==================================================================

存储过程显示一些信息

SQL> CREATE OR REPLACE PROCEDURE xs_proc

2 IS

3 BEGIN

4 DBMS_OUTPUT.PUT_LINE('hello');

5 END;

6 /

SQL> execute xs_proc;

SQL> set serveroutput on;(这个输出要打开，否则的话不能输出)

SQL> execute xs_proc;

==================================================================

create table xue_sheng(id integer,xing_mingvarchar(25),yu_wen number,shu_xue number);

insert into xue_shengvalues(1,'ZhanSan',80,90);

insert into xue_shengvalues(2,'LiSi',85,87);

==================================================================

只带输入一个参数，把查询的结果显示出来

SQL> create or replace procedurexs_proc(temp_id in integer)

2 is

3 name varchar2(25);

4 begin

5 select xing_ming into name from xue_sheng where id=temp_id;

6 dbms_output.put_line(name);

7 end;

8 /

SQL> execute xs_proc(1);

ZhanSan

---------------------------------------------------------------------------------------------------

当输入学生的名字，就会把他的总分显示出来。

create or replace procedurexs_proc(temp_name in varchar2)

num_1 number;

num_2 numver;

begin

selectyu_wen,shu_xue into num_1,num_2 from xue_sheng where xing_ming=temp_name;

dbms_output.put_line(num_1+num_2);

end;

SQL> execute xs_proc('ZhanSan');

170

PL/SQL procedure successfully completed.

==========================================================================

输入参数和输出参数一起使用

create or replace procedurexs_proc(temp_name in varchar2,temp_num out number)

num_1 number;

num_2 number;

begin

selectyu_wen,shu_xue into num_1,num_2 from xue_sheng where xing_ming=temp_name;

temp_num:= num_1 + num_2;

end;

Procedure created.

SQL> declare

1 tname varchar2(25);

2 tnum number;

3 begin

4 tname:='ZhanSan';

5 xs_proc(tname,tnum);

6 dbms_output.put_line(tnum);

7 end;

8 /

170

PL/SQL procedure successfully completed.

=====================================================================

维护存储过程

1、查看过程状态

select object_name,status from user_objectswhere object_type='PROCEDURE';

2、重新编译过程

alter procedure xs_proc compile;

3、查看过程源代码

select * from user_source wheretype='PROCEDURE';//type='(大写)'

select name,line from user_source wheretype='PROCEDURE';

4、删除存储过程

drop procedure xs_proc;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

内容：介绍事物的四大特性（原子性，一致性，隔离性，永久性）和相应的例子来演示

=============================================================================

银行账号里的转账，就是一个好的学习事物例子

假设有2个账号，A账号和B账号。

A账号转给B账号100块钱，

（2个动作在里面，1是A账号减去100块，2是B账号增加100块钱，2个动作不可分割-原子性）

如果当B账号钱没有增加的时候，那么A账号的钱不应该减少，保持一致性。

create table zhang_hao(id integer,zhang_huvarchar(25),jin_e integer);

insert into zhang_hao values(1,'A',1000);

insert into zhang_hao values(2,'B',500);

commit;

现在进行一个删除操作，你会发现其实并不是真正的删除

用scott用户删除

SQL>delete from zhang_hao where id=2;

commit;/rollback;

永久性 - 一旦commit提交了就不能回滚了，数据将真正写入到表中

============================================================================

更新一条数据，会出现2个账户同时更新的情况

解决并发一个办法：

当我在更新的时候，其他用户不能进行修改，可以说是加上一个排它锁（隔离性）。

select * from zhang_hao for update;

这样sys账号就不能更新，在一个等待的状态中

update scott.zhang_hao set jin_e=200 whereid=1;

==========================================================================

ms sql sever中的begin...transaction控制事务的一致性，

在oracle中有 commit 和exception,rollback

如果你想多条语句提交一起执行一起回滚，用savepoint

也就是说多条语句中，任意一条出现错误都会导致全部语句不执行，回滚。

---------------------------------------------------------------------------------------------------------------------

下面一个完整的例子说明：

A帐号转800块钱给B帐号。

这就要注意一个问题，当A帐号的钱转出去以后，中途出现错误，B帐号没有收到。

这种情况我们就不应该减少A帐号的钱，不执行操作，做一个回滚。

------------------------------------------------------------------------------------------------------------------

下面代码演示上面所说的情况

先随便创建一张表

create table test(tt varchar(30));

SQL> create or replace procedure zh_proc

2 as

3 begin

4 savepoint mystart;

5 update zhang_hao set jin_e=200where zhang_hu='A';

6 insert into test values('dd');

7 update zhang_hao set jin_e=1300where zhang_hu='B';

8 commit;

9 exception

10 whenothers then

11 rollback to mystart;

12 end;

13 /

也就是说：

5 update zhang_hao set jin_e=200where zhang_hu='A';

6 insert into test values('dd');

7 update zhang_hao set jin_e=1300where zhang_hu='B';

这3条语句任意一条出现执行错误，都会回滚rollback到开始的地方 mystart

SQL> drop table test;

目的为了执行 6 insert into test values('dd'); 出现错误

SQL> execute zh_proc;

BEGIN zh_proc; END;

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00905: object SCOTT.ZH_PROC is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> select * from zhang_hao;

ID ZHANG_HU JIN_E

---------- -----------------------------------

1 A 1000

2 B 500

可以证明 update zhang_haoset jin_e=200 where zhang_hu='A'; 这条语句被回滚了。把3条语句看成一个整体。

===================================================================

现在我们把test表新建回去。

create table test(tt varchar(30));

SQL> execute zh_proc;

PL/SQL procedure successfully completed.

SQL> select * from zhang_hao;

ID ZHANG_HU JIN_E

---------- -----------------------------------

1 A 200

2 B 1300

这样他就会成功提交执行那3条sql语句

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

内容：触发器trigger - 介绍，创建，使用，级联（删除，插入和更新）

=========================================================================

触发器是一个特殊的存储过程。

区别就是在于，存储过程需要去调用，而触发器无需调用，在执行某些操作的时候，会自动执行。

一般当表或者试图执行增，删，改操作的时候，就会自动执行触发器中的额PL SQL语句块。

还有一个区别，创建触发器是不带参数的，而存储过程可带可不带参数

========================================================================

下面一个例子进行演示

数据库行级触发器 - 对每一行（每一条记录进行检查）动作都触发 for each row

创建一个学生表：

create table xue_sheng(id integer,xing_mingvarchar(25),xing_bie number,fen_shu number,b_id integer);

insert into xue_shengvalues(1,'ZhanSan',1,80,1);

insert into xue_shengvalues(2,'LiSi',1,90,2);

insert into xue_shengvalues(3,'ZhanHong',0,75,2);

insert into xue_shengvalues(4,'ChenXiaoMing',1,85,1);

创建一个班级表：

create table ban_ji(id integer,ban_jivarchar(25));

insert into ban_ji values(1,'1-(1)');

insert into ban_ji values(2,'1-(2)');

----------------------------------------------------------------------------

创建一个删除行级触发器

当删除班级表的一个id，那么它会自动把学生表所属的班级的学生也会删除

SQL> create or replace triggerdel_ban_id (del_ban_id 触发器的名字)

2 after delete on ban_ji (删除的触发器建立在ban_ji表上面)

3 for each row (行级的触发器)

4 begin

5 delete from xue_sheng whereb_id=:old.id; (:old.id - 这个就是下面执行delete的时候的 id=2 的 2)

6 end;

7 /

Trigger created.

delete from ban_ji where id=2;

查看，检查触发器是否自动执行了

select * from ban_ji;

select * from xue_sheng;

执行删除操作的时候，建立一个old内存表，old表和ban_ji表结果完全一样

所以上面的 old.id 可以理解成 ban_ji 班级表的 id

------------------------------------------------------------------------------

创建一个插入行级触发器

SQL> create or replace triggerinsert_ban_ji (insert_ban_ji 触发器的名字)

2 after insert on ban_ji (插入的触发器建立在ban_ji表上面)

3 for each row (行级的触发器)

4 begin

5 insert into xue_shengvalues('5','test',0,83,:new.id); (:new.id - 这个就是下面执行insert 的时候的id)

6 end;

7 /

Trigger created.

insert into ban_ji values(3,'1-(3)');

查看，检查触发器是否自动执行了

select * from ban_ji;

select * from xue_sheng;

当插入数据时候，先插入到 new 表，new表和班级表结构也是一样的。然后在插入到真正的表，所以new.id 和ban_ji班级表id对应的。

-------------------------------------------------------------------------------------------

级联更新同时涉及到 old.id 和 new.id

例如：我要更新班级表的班级id，当然学生表的班级id也要同时更新

SQL> create or replace triggerupdate_ban_ji (update_ban_ji 触发器的名字)

2 after update on ban_ji (更新的触发器建立在ban_ji表上面)

3 for each row (行级的触发器)

4 begin

5 update xue_sheng setb_id=:new.id where b_id=:old.id;

6 end;

7 /

Trigger created.

先查看一下原来2个表的数据

select * from ban_ji;

select * from xue_sheng;

然后更新班级的id

update ban_ji set id=8 where id=1;

最后查看一下效果

select * from ban_ji;

select * from xue_sheng;

alter trigger 名称 disable;

drop trigger 名称;

===========================================================================

总结一下触发器：主要是对 old.id 和 new.id 的理解，还有要注意触发器建立所在表的选择

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

内容：数据完整性 - 创建约束(主键，外键，CHECK，非空) 和索引的使用

===========================================================================

为什么需要主键 - 主键的唯一性（数据不能出现重复），基本上每一张表都会有这个主键

创建一个学生表：

create table xue_sheng(id integer,xing_mingvarchar(25),xing_bie number,fen_shu number,b_id integer);

insert into xue_shengvalues(1,'ZhanSan',1,80,1);

insert into xue_shengvalues(2,'LiSi',1,90,2);

insert into xue_shengvalues(3,'ZhanHong',0,75,2);

insert into xue_shengvalues(4,'ChenXiaoMing',1,85,1);

如果不设置学号id这个唯一性，那么就会出现学号重复的现象，2个同学拥有相同的学号。

insert into xue_shengvalues(1,'test',0,75,1);

select * from xue_sheng;

delete from xue_sheng wherexing_ming='test';

----------------------------------------------------------------------------

把 id 这个字段设置为主键：

alter table xue_sheng add constraintpk_xue_sheng primary key(id);

这样你再插入重复的id，就会出错

insert into xue_shengvalues(1,'test',0,75,1);

insert into xue_shengvalues(1,'test',0,75,1)

ERROR at line 1:

ORA-00001: unique constraint(SCOTT.PK_XUE_SHENG) violated

删除主键：

alter table xue_sheng drop constraintpk_xue_sheng;

-----------------------------------------------------------------------------

修改表中的 xing_ming 字段不能为空

insert into xue_sheng values(5,'',0,75,1);

delete from xue_sheng where id=5;

alter table xue_sheng modify xing_ming notnull;

insert into xue_sheng values(5,'',0,75,1)

ERROR at line 1:

ORA-01400: cannot insert NULL into("SCOTT"."XUE_SHENG"."XING_MING")

desc xue_sheng;

------------------------------------------------------------------------------

CHECK约束，指定字段的值的内容，例如学生性别只能是1或者0

insert into xue_shengvalues(6,'dd',3,50,1);

SQL> alter table xue_sheng addconstraint ck_xue_sheng check(xing_bie=1 or xing_bie=0);

ERROR at line 1:

ORA-02293: cannot validate (SCOTT.CK_XUE_SHENG)- check constraint violated

delete from xue_sheng where id=6;

创建一个check约束，xing_bie 只能是 1 或者 0

SQL> alter table xue_sheng addconstraint ck_xue_sheng check(xing_bie in (1,0));

删除约束

SQL> alter table xue_sheng dropconstraint ck_xue_sheng;

=====================================================================================

外键的例子演示：

所属的班级

创建一个班级表：

create table ban_ji(id integer,ban_jivarchar(25));

insert into ban_ji values(1,'1-(1)');

insert into ban_ji values(2,'1-(2)');

-------------------------------------------------------------------------------------

insert into xue_shengvalues(5,'dd',0,50,3);

现在在学生表插入数据，如果不对应班级表的id也是可以插入的，不过这是没有意义的记录

alter table xue_sheng add constraintfk_xue_sheng foreign key(b_id) references ban_ji(id);

ERROR at line 1:

ORA-02270: no matching unique or primarykey for this column-list

错误提示要班级表的id必须是主键或者具有唯一值

alter table ban_ji add constraint pk_ban_jiprimary key(id);

一旦创建了这个外键，就不能在学生表随意插入数据，要参照班级表的id。

insert into xue_shengvalues(5,'dd',0,50,3);

还有班级表的id也不能随便修改或者删除，因为如果修改了，学生表就没有数据参照了。

============================================================================

索引：

当数据量非常大的时候，查询速度明显提高，对数据的一个有序排列

其实创建主键的时候已经对主键做了一个唯一索引

还有一个要注意的，如果你有大量数据要插入表中，先把数据插入数据表，在建立索引，否则会导致插入数据慢。

SQL> create index xs_xm_index onxue_sheng(xing_ming);

Index created.

SQL> select * from xue_sheng wherexing_ming='ZhanSan';

SQL> select * from xue_sheng wherexing_ming like '%a%';

-------------------------------------------------------------------------------------------

对于唯一值很少的字段，可以建立位图索引，例如：性别只有男，女

SQL> create bitmap index bit_xb onxue_sheng(xing_bie);

Index created.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

内容：sql*loader使用格式如下：

sqlldr userid control data

也就是说要必须要先创建好 control控制文件，data数据文件。

sql loader其实就是把数据文件的数据插入到oracle数据表中。

新建一个班级表：ban_ji

create table ban_ji(id integer,ban_jivarchar(25));

insert into ban_ji values(1,'1-(1)');

insert into ban_ji values(2,'1-(2)');

有规律分割的数据文件以"#" 井号，分割数据

新建一个数据文件 mydata.txt,内容如下：

3#1-(5)

4#2-(7)

5#3-(13)abc

-----------------------------------------------------------------------------------------------------------------

接下来新建一个控制文件mycontrl.ctl,针对数据文件如下：

load data

infile 'mydata.txt' -->这里指mydata.txt放置的位置（这里是在同一个目录下，不再同一个目录下，指定路径）

append

into table ban_ji(

idchar terminated by "#",

ban_jichar terminated by "#")

-------------------------------------------------------------------------------------------------------------------

load data 读取数据

append 追加到表

sqlldr scott/tiger control=./mycontrl.ctldata=./mydata.txt

SQL*Loader: Release 11.2.0.1.0- Production on Fri Dec 3 13:17:08 2010

Commit point reached - logical record count3

提示有3条记录提交上去了，添加到表里面

=============================================================================

验证一下，是否真的添加到数据表中

sqlplus scott/tiger

select * from ban_ji;

另外当你执行 sqlldr 系统会自动产生2个文件，log 和 bad 文件

针对是上面的例子就会产生 mycontrl.log mydata.bad 这两个文件

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

内容：数据库的备份和恢复

数据存储中，有时遇到数据丢失的情况，我们就需要定期做一个数据备份的工作。

在oracle中，使用EXP程序导出数据到文件进行备份，而是用IMP就可以进行恢复。

EXP可以导出一个数据库，也可以指定导出数据库的某个对象相关信息，

例如：数据表，表的某一列，或者表的相关信息。

============================================================================

create table xue_sheng(id integer,xing_mingvarchar(25));

insert into xue_sheng values(1,'ZhanSan');

insert into xue_sheng values(2,'LiSi');

commit;

--------------------------------------------------------------------------------------------------

下面开始进行备份：(备份学生表里的全部数据)

[oracle@localhost ~]$ exp scott/tiger <- 这里输入帐号

Export: Release 11.2.0.1.0- Production on Fri Dec 3 13:37:32 2010

Connected to: Oracle Database 11g EnterpriseEdition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Enter array fetch buffer size: 4096 >4096 <-这里是缓冲区大小，默认4096

Export file: expdat.dmp >mydata2010_1202.dmp <-输入备份文件名字，默认名字expdat.dmp

(1)E(ntire database), (2)U(sers), or(3)T(ables): (2)U > T <-因为我要备份表，所以选择T

Export table data (yes/no): yes >yes <-是否导出表中的数据

Compress extents (yes/no): yes >yes <-是否对数据进行压缩

Export done in US7ASCII character set andAL16UTF16 NCHAR character set

server uses AL32UTF8 character set(possible charset conversion)

About to export specified tables viaConventional Path ...

Table(T) or Partition(T:P) to be exported:(RETURN to quit) > xue_sheng <-输入备份表的名字

. . exporting table XUE_SHENG 2 rows exported <-导出提示信息

Table(T) or Partition(T:P) to be exported:(RETURN to quit) > <- 推出就直接回车

Export terminated successfully withoutwarnings.

如果刚才没有指定备份文件的具体路径，备份文件mydata2010_1202.dmp就会在当前目录下

[oracle@localhost ~]$ ls my*

mydata2010_1202.dmp

===========================================================================

先把xue_sheng表的数据全部删除

[oracle@localhost ~]$ sqlplus scott/tiger

SQL> select * from xue_sheng;

SQL> delete from xue_sheng;

SQL> commit;

SQL> select * from xue_sheng;

SQL> exit;

下面是用IMP进行数据恢复

[oracle@localhost ~]$ imp scott/tiger

Import: Release 11.2.0.1.0- Production on Fri Dec 3 13:55:02 2010

Connected to: Oracle Database 11g EnterpriseEdition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Import data only (yes/no): no >yes

Import file: expdat.dmp >mydata2010_1202.dmp

Enter insert buffer size (minimum is 8192)30720>

Export file created by EXPORT:V11.02.00via conventional path

import done in US7ASCII character set andAL16UTF16 NCHAR character set

import server uses AL32UTF8 character set(possible charset conversion)

List contents of import file only (yes/no):no >

Ignore create error due to object existence(yes/no): no >

Import grants (yes/no): yes >

Import table data (yes/no): yes >

Import entire export file (yes/no): no >yes

. importing SCOTT's objects into SCOTT

. . importing table "XUE_SHENG" 2 rows imported

Import terminated successfully withoutwarnings.

[oracle@localhost ~]$

-------------------------------------------------------------------------------------------------------------

验证一下数据是否被恢复

[oracle@localhost ~]$ sqlplus scott/tiger

SQL> select * from xue_sheng;

============================================================================

还有拷贝文件的备份方式

例如将 /u01/oradata/wilson 目录下的所有文件拷贝到其他地方

control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

============================================================================

总结一下：主要讲解EXP和IMP这2个的用法