%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
介绍: 数据库, 数据表 , 记录,字段 , 和对数据表的操作(create,alter,drop)
login as: oracle
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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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)
is
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)
is
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
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
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个的用法