中科院oracle,中科院ORACLE数据库视频教程总结

三个默认的用户和密码：

sys    change_on_install  [as sysdba]

system manager

scott  tiger

启动：

监听程序：lsnrctl start

数据库实例：oradim -startup -sid orcl

登陆：sqlplus / as sysdba

创建用户：

create user lisi indentified by lisi;

系统权限：

grant create session to lisi;

grant create table to lisi;

grant unlimited tablespace to lisi;

revoke create session from lisi;

grant create any table to public;//把session权限授权给所有用户

select * from user_sys_privs;//查看当前用户有哪些系统权限  【数据字典】

select * from user_tab_privs;//查看当前用户有哪些对象权限set linesize 400;设置行的宽度

select * from user_col_privs;//查看当前用户有哪些对象权限(权限控制到列)

注意：查询和删除不能控制到列

对象权限：

grant select on mytab to lisi;

grant all on mytab to lisi;

grant update(name) on mytab to lisi;//把更新name列的权限授权给lisi(将权限限定到列)

(update wuangwu.mytab set name='lihuoming' where id=1;)

grant insert(id)  on mytab to lisi;

revoke select on mytab from lisi;

alter table mytab add name varchar(10);//修改表结构

oracle默认的是手动提交数据(commit)

ddl:数据定义语言(创建，修改表)

dml：数据操纵语言(插入，删除，更新等)需要提交

dcl：数据控制语言(授权和撤销权限)

权限的传递：把权限给A，A在传递给B

show user;查看当前用户

sys把alter权限给lisi：grant alter any table to lisi;

默认lisi不能把alter权限给wangwu

可以这样：grant alter any table to lisi with admin option;

对象权限传递：

sys创建表A，sys把查询的权限给lisi，但是lisi不能把该权限授给其他人

可以这样：grant select on A to lisi with grant option;

角色：

create role myrole;

grant create session to myrole;

grant create table to myrole;

create user zhangsan identified by zhangsan;

grant myrole to zhangsan;

drop role myrole;

有些系统权限不能够放在角色里面：

比如：grant unlimited tablespace to myrole;会出现无法将该权限放在myrole里面

只能直接将该权限给用户！

create any table的权限：不仅可以给自己创建表还可以给其他人创建表

不过要先给他赋予使用空间的权限

表是属于某一个用户的，角色不属于某个用户

丢失管理员密码：

比如普通用户的密码丢失了：sys登陆，然后alter user scott identified by 新密码

如果sys密码丢失：

oracle三种验证机制：

操作系统验证：

密码文件验证：

数据库验证：普通用户

sys用户可以启动和关闭数据库，那么数据库没有启动sys就可以进入数据库里面去(但是监听和实例必须启动，否则会出现协议适配器错误)，所以sys用户采用操作系统和密码文件验证

linux下oracle的启动过程

lsnrctl start(监听程序)

sqlplus sys/密码 as sysdba

startup启动数据库实例

windows下oracle的启动过程

lsnrctl start

oradim -startup -sid orcl

conn / as sysdba为什么不写密码也能够进入数据呢？？？

因为sysdba是操作系统验证，右击我的电脑，选择管理，组，双击ora_dba，在这个组里的 用户都是sysdba，所以不用密码，那么你可以将改组的用户给删除了，那么conn / as sysdba就不能进去了!

然后conn 用户名/密码 as sysdba就采用密码文件验证

为了安全应该删除操作系统验证，那么只能采用密码文件验证，如果密码忘记了.......

密码文件在d:\database\PWDorcl.ora，可以先把他删除，然后在建立：

orapwd file=d:\database\PWDorcl.ora password=123456 entries(允许在该文件中建立特殊用户的个数)=10

创建用户：

create user abc

indentified by abc

default tablespace Users(默认表空间，存放用户的数据)

Temporary Tablespace Temp(临时表空间，比如排序，将临时数据放在这个空间里面)

Quota 50M on users(abc用户只能用50M表空间)

临时表空间上不能使用限额

限制用户：

用户加锁：alter user 用户名 account lock

用户解锁：alter user 用户名 account unlock

注意:commit

用户口令失效：

alter user 用户名 password expire

删除用户：

drop user 用户名 [cascade强制删除用户下的所有文件]

中科院oracle视频：

1.oracle体系结构：

a.实例和数据库

.数据库实例也称为服务器，是指用于访问数据库文件集的存储结构(统称为SGA)和后台进程的集合，一个数据库可以被多个实例访问，数据库的物理结构和存储结构之间的关系是由后台进程来维持的，数据库拥有多个进程.........

.数据库指的是一个数据容器，包含了表，索引，视图，过程，函数，包等对象，并对其进行统一管理，用户只有和一个确定的数据库连接，才能使用和管理该数据库中的数据，数据库的内部结构：表空间，表，列，分区，用户，索引，视图权限，角色，段，盘区，块等；外部结构有：控制文件(.ctl)，日志文件(联机日志文件(redo01.log,第一个写满之后，写在第二个，然后写在第三个，然后再写在第一个，默认此时第一个文件被覆盖掉)，归档日志文件是在进行热备份时候选择的日志文件格式)，数据文件(.dbf).....

控制文件管理和控制数据文件和日志文件，启动实例后-->启动控制文件-->打开数据文件

.查看控制文件：

DESC v$controlfile

select status,name from v$controlfile

.查看数据文件：

DESC v$datafile

select file#,name from v$datafile

.查看日志文件：

DESC v$logfile

select member from v$logfile

.参数文件(并不是数据里面的有效组成部分，数据库启动时候参数文件不直接参与工作，不过控制文件是由参数文件来寻找的)：

物理结构

参数文件

控制文件                      控制文件

数据文件     数据文件          日志文件     日志文件

b.内存结构

.当oracle的一个实例运行的时候，他分配了一个称为SGA(系统全局区)的大的内存块，实例的SGA被该实例的所有后台进程所共享，整个SGA的区里面有：数据库高速缓存池，大共享区，共享池，日志缓存池，固定SGA。

.数据库高速缓存池：如果每执行一个sql查询的时候，oracle都从磁盘读取数据块，并且在改变之后，又必须把每一个数据块写入磁盘，那么oracle执行效率会非常慢，而oracle的缓存区经常能够使用内存里的数据块，在那里访问数据，内存中用来频繁访问数据的区域叫数据库的高速缓存池！数据库高速缓存池包括：默认缓存池()，保持缓存池(对于想在内存中长期保存的，频繁访问的使用该缓存池，这个缓存池在数据库关闭之前一直保留，并不释放内存空间)，再生缓存池(想尽快从内存中排除的对象我们可以使用再生缓存池,例如频繁访问的大表，除此之外还可以考虑默认缓存池)！

.共享池：主要由库缓存区和字典缓存区构成！用来缓存pl/sql的程序单元，sql语句的执行版本以及相关的执行计划。共享池的规模对于数据库的性能有着非常重要的影响！库缓存区中包括：PL/SQL区(保留了pl/sql的过程，函数等程序单元的编译版本，以便于所有的用户能够共享)和共享SQL区(保留了sql语句的解释版本，以便再次使用的时候，不用重新解释)

c.逻辑结构

.主要由块(block)，盘区(extent)，段(segment)，表空间(tablespace)组成！

.其中一个表空间对应一个或者多个数据文件，在物理结构时候，知道我们存储的对象信息，记录，数据都是存储在oracle的数据文件中的，但是在逻辑上，我们是将这些的表和表中的数据存储在表空间中

.一个表空间可以包含多个段

.段和物理的数据文件并不存在一一对应关系，一个段可能跨的不同的数据文件来存储

.一个段可以包含多个盘区

.一个盘区可以包含多个块

.oracle存储数据的基本单位是块，windows下默认块的大小是8k，oracle的块大小一定是操作系统的块大小的整数倍

2.sql*plus基础

.sys登陆必须以sysdba身份

.startup启动数据库实例-打开控制文件-打开数据文件，参数mount(启动数据库实例的时候，打开控制文件，不启动数据文件),nomount(启动实例，连控制文件都不打开，一般在控制文件丢失的时候使用此参数，然后ALTER DATABASE MOUNT，alter databases open)

.我们进行热备份的时候，是不允许进行非归档方式的！我们要修改在归档模式下进行热备份，但是在数据库已经启动的情况下是不允许修改的，那么我们只能先关闭数据库，关闭之后在启动实例startup mount打开控制文件，但是不能打开数据文件，然后:alter databases archivelog 然后alter databases open

.关闭：shutdown immediate(迫使每个用户执行完当前的sql语句后立即断开连接)/abort(强迫关闭数据库，很可能造成文件破坏)/transactional(迫使用户在当前执行完程序后，断开连接，终止实例，恢复破坏的文件和数据文件，不能同其他的数据库文件保持一致)

.想使用oracle数据库，那么首先就得启动两个服务：一个是启动监听程序lsnrctl start，一个是启动实例oradim -startup -sid orcl

.sql*plus也可以在浏览器中控制，建议使用这中方式

常用的一些sql*plus命令：

保存缓存区的命令：save c:\oracle\test.txt

编辑缓存区的命令：edit

执行外部文件：@c:\oracle\test.txt

查看外部文件：get c:\oracle\test.txt

列出缓存区的命令：list

再一次执行缓存区的命令：/

查看命令的用法：? 命令

对部门的名称给一个标签：col deprno Heading "编号"

或者是格式化输出：col department format A10(显示10个字符)/999，999，999(整型的格式化)  heading "编号"

设置报表：

设置行宽：set linesize 50

标题居中并且指定名称：ttitle center "我的主题" skip 1-(指的是报表空一行)

left "测试报表" right "页" -(指sql语句换一行写)

format 999 sql.pno skip 2(页和数字之间空2行)

关闭标题：ttitle off

报表的重要的命令：break和comp

重复的记录值显示一条:break on pub

select * from book

统计：comp count lable "计数" of books_name(字段) on pub

将数据保存起来：spool c:\orcle\test.txt

select * .....

spool off

3.sql语言基础：

.语言分类:  DDL(数据定义语言):create,drop,alter DCL(数据控制语言):grant,revoke DML(数据操纵语言):select insert delete update

.常用的系统函数：

字符：

查看字符个数select length('abcdef') from dual;

截掉左边空格： select ltrim('abcdef') from dual;

截掉右边空格： select rtrim('abcdef') from dual;

截掉空格： select trim('abcdef ') from dual;

查看字节个数:select lengthb('abcdef') from dual;

取子字符串(从第二个位置取三个bcd):select substr('abcdef',2,3) from dual;

右取三个：select substr('abcdef',length('abcdefg')-3+1,3)

时间：

查看当前时间:select sysdate from dual;                               select current_date from dual;

设定当前时间的格式: alter session set nls_date_format='dd-mon-yyyy hh:mi:ss';

当前日期为准，星期三是多少号:select next_day(sysdate,'星期三') from dual;

转换类型：

日期转换为字符串24小时制： select to_char(sysdate,'yyyy24-mm-dd hh:mi:ss') from dual;

日期转换为字符串16小时制： select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

字符串转化为日期：select to_date('12-3月-04') from dual;

字符型转换为整型:select to_number('99') from dual;

聚集函数：

sum:select sum(price) from books;

max:......

min:........

avg:平均值

count:count(*)表的行数count(price)：字段的行数

其他:

查看当前登陆用户:select user from dual;                查看性别为男和女分别有多少人:select sum(decode('sex','男',1,0)),sum(decode('sex','女'，1，0)) from e;    decode和if差不多

select a1,nvl(a2,'没有输入') a2 from aa;            nvl查看是否为空值

分组语句：

select pub,sum(price*qty) from books group by pub;       group by后面可以多于前面select中的字段，但是不能少！

聚集函数不能出现在where下面，只能用having

select pub,sum(price*qty) from books group by pub having sum(price)>50;

模糊查询：

select * from aa where a1 like 'a_' 'a__' 'a%' '__a' '%a' '%a%';

连接查询：

select eid 编号,ename 姓名,sex 性别,d.name 所在部门 from e,d where e.id=d.id

内连接(完全匹配)：

select eid 编号,ename 姓名,sex 性别,d.name 所在部门 from e join d on e.id=d.id

外连接(不完全匹配)：

左外连接：

select eid 编号,ename 姓名,sex 性别,d.name 所在部门 from e,d where e.id=d.id(+)

右外连接：

select eid 编号,ename 姓名,sex 性别,d.name 所在部门 from e,d where e.id(+)=d.id

子查询：

无关子查询：select * from e where id in (select id from d);

select * from e where exists (select id from d);

相关子查询：select * from e where id in (select id from d where id=e.id and id='03');

select * from e where exists (select id from d where id=e.id);把符合的查询出来

select * from e where not exists (select id from d where id=e.id);把不符合的查询出来

合并行数据:select eid,ename from e union select id,name from d;

选出都有的数据:select id from e intersec selct id from d;

每次插入多条记录：

insert into e(eid,ename) select id,name from d;把另外一个表中的数据写入e表中，类型要匹配。

复制表：

create table tt as(select * from e);

4.PL/SQL基础

pl/sql块结构如下：

declare

...(变量声明)

begin

....(代码处理)

exception

....(异常处理)

end;

/

变量声明：

赋予变量适当名称

赋予变量正确的数据类型

定义变量(标准，记录)

控制变量范围

变量长度范围：1~30

例子：

Declare

x varchar2(10);

begin

x:='this is..';

DBMS_OUTPUT.PUT_LINE('x is'||x);

END;

/

让服务器的输出打开:set SERVEROUTPUT ON SIZE 10000

上面的DBMS_OUTPUT是个包

行注释：--

块注释：/*       */

赋初值：x varchar2(10):='abcde';

x STRING(10):='abcde';

y INTEGER:=123;

y NUMBER:=123;

分支语句：

if分支

.....

case分支

case

when....then...

else

end case

例子：

declare

a number;

b varchar2(10);

begin

a:=2;

if a=1 then

b:='A';

elsif a=2 then

b:='B';

else

b:='C';

end if;

DBMS_OUTPUT.PUT_LINE('b is '||b);

end;

/

declare

a number;

b varchar2(10);

begin

a:=2;

case

when a=1 then b:='A';

when a=2 then b:='B';

when a=3 then b:='C';

else

b:='others';

end case;

DBMS_OUTPUT.PUT_LINE('b is '||b);

end;

/

循环语句

基本循环(Loop)

LOOP

....

END　LOOP

while循环

while expression LOOP

....

end LOOP;

for循环

for counter in [REVERSE] start_value..end_value LOOP

.....

end LOOP;

例子：

DECLARE

x number;

begin

x:=0;

LOOP

x:=x+1;

if x>=3 then

exit;

end if;

DBMS_OUTPUT.PUT_LINE('in:x='||x);

end loop;

end;

/

DECLARE

x number;

begin

x:=0;

while x<=3 LOOP

x:=x+1;

DBMS_OUTPUT.PUT_LINE('in:'||x);

end loop;

end;

/

begin

for i in 1..5 LOOP

DBMS_OUTPUT.PUT_LINE('i='||i);

END LOOP;

end;

/

begin

for i in reverse 1..5 LOOP

DBMS_OUTPUT.PUT_LINE('i='||i);

END LOOP;

end;

/

DECLARE

x number;

begin

x:=0;

<>

x:=x+1;

DBMS_OUTPUT.PUT_LINE(x);

if x<3 then

GOTO repeat_loop;

end if;

end;

/

异常处理：

异常结构：

EXCEPTION

when...then

.....

例子：

DECLARE

test varchar2(10);

begin

select name into test from  deptment where id='tt';

DBMS_OUTPUT.PUTLINE(test);

exception

when no_data_found then

DBMS_OUTPUT.PUTLINE("没有数据");

end;

/

常见系统预定义异常

ZERO_DIVIDE 发生被零除

DUP_VAL_ON_INDEX:向有唯一约束的表中插入了重复行

NO_DATA_FOUND:在一个select into语句中没有返回值

TOO_AMNY_ROWS:select into语句返回了多行

VALUE_ERROR:一个算法，转换，截断或大小约束发生错误

自定义异常：

declare

tname varchar2(10);

e exception;

begin

select name into tname from deptment where id='01';

if tname<>'b部门' then

raise e;

end if;

DBMS_OUTPUT.PUTLINE(tname);

exception

when e then

DBMS_OUTPUT.PUTLINE("错误");

end;

/

复合变量:记录

记录是有几个相关值构成的复合变量，常用于支持select语句的返回值，使用记录可以将一行数据看成一个单元进行处理，而不必将每一列单独处理

记录的声明：

TYPE type_name IS RECORD(

Variable_name datatype[,

Variable_name datatype[,

......

);

real_name type_name;

例子:

declare

type myrecoder id record(

id varchar2(10),

name varchar2(10)

);

real_record myrecord;

begin

select emp_id,emp_name into real_record from emp where emp_id='001';

DBMS_OUTPUT.PUTLINE(real_record.id||real_record.name);

end;

/

定义记录类型中的每一个变量和表中的类型和长度一样：

declare

type myrecoder id record(

id emp.eid%TYPE,

name varchar2(10)

);

real_record myrecord;

begin

select emp_id,emp_name into real_record from emp where emp_id='001';

DBMS_OUTPUT.PUTLINE(real_record.id||real_record.name);

end;

/

定义记录类型和表中字段的类型和长度，个数一样：

declare

myrec emp%ROWTYPE

begin

select * into myrec from emp where emp_id='001';

DBMS_OUTPUT.PUTLINE(myrec.eid||myrec.ename);

end;

/

PL/SQL高级应用

游标：是pl/sql控制结构，可以对sql语句的处理进行显示控制，便于对表的行数据进行处理

分类：隐士游标和显示游标

游标的属性:%FOUND %ROWCOUNT %ISOPEN %NOTFOUND

例子:

显示游标：

declare

CURSOR mycur IS select * from books;

myrecord book%ROWTYPE;

begin

open mycur;

fetch mycur into myrecord;//第一行数据

while mycur%FOUND LOOP

DBMS_OUTPUT.PUTLINE(myrecord.book_id||myrecord.book_name);

fetch mycur into myrecord;

end loop

close mycur;

end;

/

显示游标带参数：

declare

CURSOR mycur(id varchar2) IS select books_name from books where books_id=id;

t_name books.books_name&TYPE;

begin

open mycur('001');

LOOP

fetch mycur into t_name;//第一行数据

exit when mycur%NOTFOUND;

DBMS_OUTPUT.PUTLINE(myrecord.book_id||myrecord.book_name);

end loop

close mycur;

end;

/

第二种写法

declare

CURSOR mycur(id varchar2) IS select books_name from books where books_id=id;

begin

DBMS_OUTPUT.PUTLINE("*******结果集为：**********");

for cur in mycur('001') LOOP

DBMS_OUTPUT.PUTLINE(cur.book_name);

end loop;

end;

/

ISOPEN用法：

declare

CURSOR mycur(id varchar2) IS select books_name from books where books_id=id;

t_name books.books_name&TYPE;

begin

if mycur%ISOPEN then

DBMS_OUTPUT.PUTLINE("游标已经被打开");

else

open mycur('001');

end if;

fetch mycur into t_name;//第一行数据

close mycur;

DBMS_OUTPUT.PUTLINE(t_name);

end;

/

ROWCOUNT的用法：

declare

CURSOR mycur IS select from deptment;

t_name varchar2(10);

begin

open mycur;

LOOP

fetch mycur into t_name;

exit when mycur%NOTFOUND or mycur%NOTFOUND IS NULL;

DBMS_OUTPUT.PUTLINE('游标mycur的ROWCOUNT是:'||mycur%ROWCOUNT);

END LOOP;

close mycur;

end;

/

//如果没有做fetch的话，游标为null

利用游标修改数据

declare

cursor cur is select name from deptment for update;

text varchar2(10);

begin

open cur;

fetch cur into text;

while cur%FOUND LOOP

update deptement set name=name||'_t' where current of cur;

fetch cur into text;

end loop;

close cur;

end;

/

隐式游标：就是我们不需要open和close

begin

for cur in(select name from deptment) LOOP

DBMS_OUTPUT.PUTLINE(cur.name);

end loop;

end;

/

存储过程：

创建语法：

create [or replace] procedure procedurename

[(param1[{in|out{in out}] param1_type

[(param2[{in|out{in out}] param2_type

.......

is|as

..

begin

Proc_body;

end;

/

例子：

create or replace procedure myproc(id in varchar2)

is

name varchar2(10);

begin

select books_name into name from books where books_id=id;

DBMS_OUTPUT.PUTLINE(name);

end myproc;

/

查询存储过程错误：

show errors procedure myproc;

执行存储过程：

declare

tid varchar2(10);

begin

tid:='0001';

mypro(tid);

end;

/

或者

begin

myproc('0001');

end;

/

或者

execute myproc('0001');

输出参数的使用：

create or replace procedure myproc(id in varchar2，name out varchar2)

is

begin

select books_name into name from books where books_id=id;

end;

/

declare

tid varchar2(10);

tname varchar2(10);

begin

tid:='0001';

mypro(tid,tname);

DBMS_OUTPUT.PUTLINE(tname);

end;

/

6.视图，同义词，序列

视图实际是一条查询语句，是数据的显示方式

创建视图:

create or replace view myview

as

select * from books;

查询视图：

select * from myview;

插入:

insert into myview(books_id) values('0008');

那么book表中也插入了，视图中也插入

create or replace view myview

as

select * from books where price>30;

插入:

insert into myview(books_id，price) values('0008'，23);

那么book表中插入了，视图中没有插入

create or replace view myview

as

select * from books where price>30 with check option;

插入:

insert into myview(books_id，price) values('0008'，32);

如果插入的记录小于30的话，就不让插入了

两个以上的表：

create or replace view myview

as

select eid,ename,sex,d.id,d.name from emp e,deptment d where e.id=d.id;

插入:

insert into myview values('0008','abc','M','007','tt');出现错误

组成视图的两个以上的表，不能同时对两个表进行更新

让组成的视图只读，不能更新：

create or replace view myview

as

select eid,ename from emp with read only;

如果视图中含有聚合函数等也不允许更新！

查看用户视图：

select text from user_views where view_name='myview';

查看系统视图:

select text from dba_views where view_name='myview';

select text from all_views where view_name='myview';

同义词：可以方便的操纵不同用户模式下的对象

查看当前用户:select user from dual;

select * from dept;出错，因为dept是scott下的表

select * from scott.dept;正确，要加上模式

那么创建同义词可以解决上面的问题:

create synonym dept for scott.dept;这个是专有的

select * from dept;正确，如果换到其他的用户，在执行就会出现错误

创建公共的同义词，sys用户:create pulbic synonym dept for scott.dept;

删除:drop synonym dept;

查找当前用户的同义词

select synonym_name,table_name,table_owner from user_synonyms;

序列：(例如递增序列)

创建序列：

create sequence myseq

start with 1

increment by 1

order

nocycle;

查询

select myseq.nextval from dual;

select myseq.currval from dual;

写完nextval之后才能写currval

应用在表上：

create table auto(a number,b varchar2(10));

insert into auto vlaues(myseq.nextval,'dddd');

查询当前用户序列：

select sequence_name,sequence_owner from user_sequences;

更改递增量：alter sequence myseq increment by 3;

6.触发器：

创建触发器:

create or replace trigger del_deptid

after delete on deptment

for each row

begin

delete from emp where id=:old.id;

end del_deptid;

/

create or replace trigger del_deptid

after insert on deptment

for each row

begin

insert...values('kkk',:new.id);

end del_deptid;

/

上面的old和new表是在内存中的

我们对哪个表进行了delete那么old表就和他的结构一样

我们对哪个表进行了insert那么new表就和他的结构一样

插入数据时候先插入到new表中，然后在插入实际的表中

删除数据时候先把数据放在old表中，提交后在从old表中删除

insert涉及new表

delete涉及old表

update涉及old和new表

create or replace trigger del_deptid

after update on deptment

for each row

begin

update emp set id=:new.id where id=:old.id;

end del_deptid;

/

create or replace trigger del_deptid

after delete on deptment

for each row

begin

if :old.book_id='0001' then

raise_application_error(-20000,'不允许删除')；

end if

end del_deptid;

/

触发器中不能写rollback也不能写DBMS_OUTPUT.PUTLINE

-20999~-20000可以写，以外的错误号就不可以写了！

上面的是行级触发器

下面的是语句级触发器：

先构造一个表:

create table mylog(curr_usre varchar2(100),curr_date date,act char(1));

创建触发器：

create or replace trigger dml_aa

after insert or delete or update on aa

begin

if inserting then

insert into mylog values(user,sysdate,'I');

elsif deleting then

insert into mylog values(user,sysdate,'D');

else

insert into mylog values(user,sysdate,'U');

end if;

end;

/

语句级的不涉及到数据完整性的问题，只要做相应的语句动作就会触发，与行无关，无论多少行只要做了这个动作就触发！

替换触发器只能建在视图上面：

create or replace trigger tr_v_e_d

instead of insert on v_em_dept

for each row

begin

insert into deptment values(:new.id,new:name);

insert into emp(eid,ename,sex,id) vlaues(:new.eid,new.ename,:new.sex,:new.id);

end;

/

上面就是利用替换触发器解决视图的多表更新问题

7.表空间的管理

分配和管理表空间

创建表空间可以在浏览器中创建：

create tablespace tabs

datafile 'D:\oracle\oradata\orcl\tabs.dbf' size 10M;

修改用户的默认表空间：

alter user test default tablespace tabs;

create unlimited tablespace,dba to test;

那么用户在创建表的时候，默认是在默认表空间中的！

可以指定表空间：

create table ..... tablespace tt;

就是将这个表放在tt表空间中！

8.表的管理：

实体完整性通过主键来保证完整性

域完整性是输入的数据要符合定义的类型

参照完整性是外键

外键约束：

alter tbale mm add constraint fk_mm foreign key(n1) references nn(n1);

check约束：

alter table emp add constraint ck_emp_sex check(sex='男' or sex='女')；

查看当前用户模式下的约束：

select constraint_name,constraint_type from user_constraints where table_name='EMP';

索引：建立在表的字段上的

可以提高查询的速度

创建索引：

create index my_mm_idx on mm(m1);

索引建立在经常查询的字段上

创建位图索引：

create bitmap index bit_map on emp(sex)

这种索引是针对数据量很大，但是唯一值很少的类型数据，例如性别字段

9.SQL*Loader的使用：装载数据

准备好数据文件loader.txt:

abc,efg

def,rff

准备控制文件cont.ctl：

load data

infile 'c:\loader.txt'

append

into table scott.mm(

m1 position(1:3) char,

m2 position(5:7) char)

导入：sqlldr sys/password control=e:\cont.ctl data=e:\loader.txt

如果数据文件中：

abc,cde

dfsfdas,fd

那么控制文件：

oad data

infile 'c:\loader.txt'

append

into table scott.mm(

m1 char terminated by ",",

m2 char terminated by ",")

10.OEM的配置

11.监听程序和服务的配置

12.数据库备份与恢复

逻辑备份

使用exp进行逻辑备份(将数据库中的数据写在文件中)

逻辑恢复

使用imp进行逻辑恢复

物理备份

冷/热备份

冷备份是脱机备份

热备份是联机备份

冷备份：

停止备份：shutdown immediate

拷贝：将d:\oracle\...\orcl文件夹拷贝到备份目录

热备份

将日志改为归档方式(会把第一个日志文件备份到一个路径下，这个路径默认是(查看:archive log list):use_db_recovery_file_dest)

设置:alter system set log_archive_start=true scope=spfile

停止数据库:shutdown immediate;

启动:startup mount;

alter database archivelog;

alter database open;

进行备份:

假定备份tt模式下的tt表空间:

alter tablespace tt begin backup;

然后拷贝tt表空间文件:d:\...\tt.dbf

alter tablespace tt end backup;

归档当前的联机日志:alter system archive log current;

切换:alter system switch logfile;

在切换:alter system switch logfile;

停止数据库:shutdown immediate;

如果我们删除tt.dbf

然后启动:startup,就会出现错误提示

解决:

alter database datafile 6 offline drop;

alter database open;

将备份的文件拷贝到原来的路径下

recover datafile 6(会出现提示选择auto);(6从错误提示中可以看出来)

alter database datafile 6 online;

物理恢复

冷热恢复

如果数据库中控制文件丢失：

备份控制文件:alter database backup controlfile to trace(备份路径:d:\admin\orcl\udump\..时间最近的那个文件);

找到控制文件的备份，拷贝到一个路径下面:create_rctl.txt

停止数据库：shutdown immediate

执行脚本:@c:..\create_rctl.txt

如果日志文件丢失:

recover database until cancel;

alter database open resetlogs;重新生产日志文件

总结：我们在安装oracle的时候，已经让我们新建立了一个数据库，每一个数据库都会有一个或者多个数据库实例来访问，这个数据库实例有个SID，来唯一标示这个数据库实例，默认名称和数据库名字一样！安装成功后，系统自带的用户可以登陆访问这个新建的数据库，默认的也是进入这个数据库！如果我想新建一个数据库，可以通过dbca来创建！系统用户访问不用的数据库，他的密码可以不一样！我们也可以通过sqlplus这样的方式来让用户登陆到指定的数据库！

用户解锁:alter user scott account unlock;

dual表是个特殊的表！

select 2*3 from dual;

这个表就是这个作用！

表达式中有一个为null，那么这个结果就会为null

例如:sal*12+comm

如果comm为null值，那么sal*12+comm结果为null

字符串连接：select ename||sal from emp;

数据库中表示字符串都是用单引号，如果字符串里有一个单引号了，那么:select ename||'sdfasf''fsafds' from emp;

就是用两个单引号代替一个单引号

去掉重复的deptno：

select distinct deptno from emp;

去掉重复的组合：

select distinct deptno，ename from emp;

就是这个组合重复了才去掉！

根据日期来查找:

select ename,sal,hiredate from emp where hiredate > '20-2月-1981';

日期的格式查看sysdate

姓名中有%的，要用注意字符

select ename from emp where ename like '%\%%';

我也可以换转移字符:

select ename from emp where ename like '%$%%' escape '$';

把aci码转换为字符：

select chr(65) from dual;

把字符转换为aci码:

select asci(A) from dual;

四舍五入:

select round(23.65) from dual;结果为24

select round(23.654,2) from daul;结果为23.65是保留到小数点后两位

转换为特定格式的字符串:

select to_char(sal,'$99,999.9999') from emp;

9表示1位数字，如果没有就不显示

$表示美元

L表示本地货币

0表示1位数字，如果没有就用0填充

日期转换:

select to_char(hiredate,'YYY-MM-DD HH:MI:SS') from emp;

select ename,hiredate from emp where hiredate>to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');

select ename,hiredate from emp where sal>to_number('$1,250.00','$9,999.99');

处理null值:

表达式中有一个为null，那么这个结果就会为null

例如:sal*12+comm

如果comm为null值，那么sal*12+comm结果为null

那么解决如下:

select ename,sal*12+nvl(comm,0) from emp;

count某一个字段，如果这个字段不是null，就算1个

count(distinct deptno)

出现在选择列表里的字段，没有出现在组函数里面就必须出现在group by里面！

select deptno,max(sal) from emp group by deptno;

sql执行顺序:

select * from emp

where...

group by...

having...

order by...

按照关键字的先后顺序!先过滤---分组---限制组----排序--取出数据

取出前五行：

select * from emp where rownum<=5;

取出大于5行：

select * from (select rownum r,ename from emp) where r>10;

查看当前用户下面有多少张表：select table_name from user_tables;