中科院ORACLE数据库视频教程总结.doc

中科院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在传递给Bshow 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 orclconn / as sysdba为什么不写密码也能够进入数据呢？ 因为sysdba是操作系统验证，右击我的电脑，选择管理，组，双击ora_dba，在这个组里的 用户都是sysdba，所以不用密码，那么你可以将改组的用户给删除了，那么conn / as sysdba就不能进去了!然后conn 用户名/密码 as sysdba就采用密码文件验证为了安全应该删除操作系统验证，那么只能采用密码文件验证，如果密码忘记了. 密码文件在d:databasePWDorcl.ora，可以先把他删除，然后在建立： orapwd file=d:databasePWDorcl.ora password=123456 entries(允许在该文件中建立特殊用户的个数)=10创建用户：create user abc indentified by abcdefault 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:oracletest.txt 编辑缓存区的命令：edit 执行外部文件：c:oracletest.txt 查看外部文件：get c:oracletest.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:orcletest.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 性别, 所在部门 from e,d where e.id=d.id 内连接(完全匹配)： select eid 编号,ename 姓名,sex 性别, 所在部门 from e join d on e.id=d.id 外连接(不完全匹配)： 左外连接： select eid 编号,ename 姓名,sex 性别, 所在部门 from e,d where e.id=d.id(+) 右外连接： select eid 编号,ename 姓名,sex 性别, 所在部门 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;/变量声明： 赋予变量适当名称 赋予变量正确的数据类型 定义变量(标准，记录) 控制变量范围 变量长度范围：130例子：Declarex varchar2(10);beginx:=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 . ENDLOOP while循环 while expression LOOP . end LOOP; for循环 for counter in REVERSE start_value.end_value LOOP . end LOOP;例子：DECLAREx number;beginx:=0;LOOPx:=x+1;if x=3 thenexit;end if;DBMS_OUTPUT.PUT_LINE(in:x=|x);end loop;end;/DECLAREx number;beginx:=0;while x=3 LOOPx:=x+1;DBMS_OUTPUT.PUT_LINE(in:|x);end loop;end;/beginfor i in 1.5 LOOPDBMS_OUTPUT.PUT_LINE(i=|i);END LOOP;end;/beginfor i in reverse 1.5 LOOPDBMS_OUTPUT.PUT_LINE(i=|i);END LOOP;end;/DECLAREx number;beginx:=0;x:=x+1; DBMS_OUTPUT.PUT_LINE(x);if x3 then GOTO repeat_loop;end if;end;/异常处理：异常结构： EXCEPTION when.then .例子：DECLAREtest varchar2(10);beginselect name into test from deptment where id=tt;DBMS_OUTPUT.PUTLINE(test);exceptionwhen no_data_found thenDBMS_OUTPUT.PUTLINE(没有数据);end;/常见系统预定义异常 ZERO_DIVIDE 发生被零除 DUP_VAL_ON_INDEX:向有唯一约束的表中插入了重复行 NO_DATA_FOUND:在一个select into语句中没有返回值 TOO_AMNY_ROWS:select into语句返回了多行 VALUE_ERROR:一个算法，转换，截断或大小约束发生错误自定义异常：declaretname varchar2(10);e exception;beginselect name into tname from deptment where id=01;if tnameb部门 thenraise e;end if;DBMS_OUTPUT.PUTLINE(tname);exceptionwhen e thenDBMS_OUTPUT.PUTLINE(错误);end;/复合变量:记录记录是有几个相关值构成的复合变量，常用于支持select语句的返回值，使用记录可以将一行数据看成一个单元进行处理，而不必将每一列单独处理记录的声明：TYPE type_name IS RECORD(Variable_name datatype,Variable_name datatype,.);real_name type_name;例子:declaretype myrecoder id record(id varchar2(10),name varchar2(10);real_record myrecord;beginselect emp_id,emp_name into real_record from emp where emp_id=001;DBMS_OUTPUT.PUTLINE(real_record.id|real_);end;/定义记录类型中的每一个变量和表中的类型和长度一样：declaretype myrecoder id record(id emp.eid%TYPE,name varchar2(10);real_record myrecord;beginselect emp_id,emp_name into real_record from emp where emp_id=001;DBMS_OUTPUT.PUTLINE(real_record.id|real_);end;/定义记录类型和表中字段的类型和长度，个数一样：declaremyrec emp%ROWTYPE beginselect * 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例子:显示游标：declareCURSOR mycur IS select * from books;myrecord book%ROWTYPE;beginopen mycur;fetch mycur into myrecord;/第一行数据while mycur%FOUND LOOPDBMS_OUTPUT.PUTLINE(myrecord.book_id|myrecord.book_name);fetch mycur into myrecord;end loopclose mycur;end;/显示游标带参数：declareCURSOR mycur(id varchar2) IS select books_name from books where books_id=id;t_name books.books_name&TYPE;beginopen mycur(001);LOOPfetch mycur into t_name;/第一行数据exit when mycur%NOTFOUND;DBMS_OUTPUT.PUTLINE(myrecord.book_id|myrecord.book_name); end loopclose mycur;end;/第二种写法declareCURSOR mycur(id varchar2) IS select books_name from books where books_id=id; beginDBMS_OUTPUT.PUTLINE(*结果集为：*);for cur in mycur(001) LOOPDBMS_OUTPUT.PUTLINE(cur.book_name); end loop;end;/ISOPEN用法：declareCURSOR mycur(id varchar2) IS select books_name from books where books_id=id;t_name books.books_name&TYPE;beginif 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的用法：declareCURSOR mycur IS select from deptment;t_name varchar2(10);beginopen mycur;LOOPfetch 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利用游标修改数据declarecursor cur is select name from deptment for update;text varchar2(10);beginopen cur;fetch cur into text;while cur%FOUND LOOPupdate deptement set name=name|_t where current of cur;fetch cur into text;end loop;close cur;end;/隐式游标：就是我们不需要open和closebegin for cur in(select name from deptment) LOOPDBMS_OUTPUT.PUTLINE();end loop;end;/存储过程：创建语法：create or replace procedure procedurename(param1in|outin out param1_type(param2in|outin out param2_type.is|as.beginProc_body;end;/例子：create or replace procedure myproc(id in varchar2)isname varchar2(10);beginselect books_name into name from books where books_id=id;DBMS_OUTPUT.PUTLINE(name);end myproc;/查询存储过程错误：show errors procedure myproc;执行存储过程：declaretid varchar2(10);begintid:=0001;mypro(tid);end;/或者beginmyproc(0001);end;/或者execute myproc(0001);输出参数的使用：create or replace procedure myproc(id in varchar2，name out varchar2)isbeginselect books_name into name from books where books_id=id;end;/declaretid varchar2(10);tname varchar2(10);begintid:=0001;mypro(tid,tname);DBMS_OUTPUT.PUTLINE(tname);end;/6.视图，同义词，序列视图实际是一条查询语句，是数据的显示方式创建视图:create or replace view myviewasselect * from books;查询视图：select * from myview;插入:insert into myview(books_id) values(0008);那么book表中也插入了，视图中也插入create or replace view myviewasselect * from books where price30;插入:insert into myview(books_id，price) values(0008，23);那么book表中插入了，视图中没有插入create or replace view myviewasselect * from books where price30 with check option;插入:insert into myview(books_id，price) values(0008，32);如果插入的记录小于30的话，就不让插入了两个以上的表：create or replace view myviewasselect eid,ename,sex,d.id, from emp e,deptment d where e.id=d.id;插入:insert into myview values(0008,abc,M,007,tt);出现错误组成视图的两个以上的表，不能同时对两个表进行更新让组成的视图只读，不能更新：create or replace view myviewasselect 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 myseqstart with 1increment by 1order 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_deptidafter delete on deptmentfor each rowbegindelete from emp where id=:old.id;end del_deptid;/create or replace trigger del_deptidafter insert on deptmentfor each rowbegininsert.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_deptidafter update on deptmentfor each row