Oracle8i 培训课程

1. Oracle8i培训课程

2. 自我介绍 • 姓名：郭福仁 • 电话：7378980

3. ORACLE介绍 • ORACLE公司自86年推出版本5开始,系统具有分布数据库处理功能.88年推出版本6,ORACLE RDBMS(V6.0)可带事务处理选项(TPO),提高了事务处理的速度.1992年推出了版本7,在ORACLE RDBMS中可带过程数据库选项(procedural database option)和并行服务器选项（parallel server option）,1996年推出ORACLE8.0.6增加了表分区的功能，1998年推出了ORACLE8I，增加了对数据库的增强性管理、一起在数据库中存储对象等，2000年，推出了ORACLE9I，使数据库的操作更加的简便，而且取消的SVRMGRL、INTERNAL的，增强的数据的安全，同时，能够对数据库的权限细分到ROW的级别，对数据库有着更好的控制，而且起主要面向WWW的应用，数据库功能得到非常强大的更新。

4. ORACLE的物理结构 • ORACLE的物理结构主要是有以下的数据文件组成：

5. ORACLE的物理结构介绍 • Init.ora参数文件：存放数据库名称及内存参数设置的初始化文件、数据块的大小； • 控制文件：存放数据库状态的文件，例如：数据文件当前是否online或者offline，参数和数据文件的的存放的地方等； • 系统文件：system01.dbf主要存放数据字典；rbs01.dbf数据库的回滚段文件；temp01.dbf数据库的临时段文件 • 数据文件：user01.dbf、indx01.dbf多是数据文件，主要存放用户的数据，如果你新增加数据库文件，可以取自己的名称，如：train01.dbf • 密码文件：主要用语internal用户的初始密码；默认的密码是oracle，可以通过orapwd来修改；

6. ORACLE的逻辑结构 • 逻辑结构图： 数据字典区 Log_Buffer 日志缓冲区 Data_Buffer 数据缓冲区 SQL缓冲区 PMON 进程监控 SMON 系统监控 DBWR 数据库读写 LGWR 日志读写 CHPT 检查点进程 LCKnnn 锁进程 RECO 恢复进程 SNPnnn 工作进程 ARCH 工作进程

7. ORACLE的逻辑介绍 • 字典缓冲区：主要存放数据库操作中的数据字典数据，如：表名、列名、存储过程名、函数名、视图名、序列名等； • SQL缓冲区：存放当前执行的SQL语句、p-code代码、SQL语句的执行树等； • 数据缓冲区：存放从文件中读取的数据、用户操作写入数据库的数据，按LRU算法存放（访问最频繁的数据放在缓冲区，最少访问的在 队列的后面，如果缓冲去够，最少访问的将被丢弃） • 日志缓冲区：一旦用户对数据库进行操作，将产生事务(transaction)

8. ORACLE的逻辑结构 • PMON：进程监视器，主要监控其它的进程的运行状态，如果发现有进程失败，将重新启动； • SMON：系统事务监控器，主要保证数据库的完整性，保证事务的正常； • DBWR：数据的读写进程，将data_buffer中的数据写入文件或者读取数据文件； • LGWR：日志的读写器，将日志写入数据库； • CHPT：检查点进程，触发DBWR的写入数据文件中； • LCKnnn：锁进程，数据库锁的管理进程，主要是管理数据库的锁； • RECO：恢复进程，在数据库启动或者数据库恢复的时候，负责恢复数据库； • SNPnnn:数据库的工作进程，如果数据库中有任务，将通过该进程进行运行

9. Oracle的网络结构 • 网络架构图： Net 8 LISNERTER 数据库监听器 网络 Net 8

10. oracle的网络结构图说明 • 客户机:主要是不是数据库的应用,如:SQL*PLUS、应用程序等； • NET8：是ORACLE数据库的组件，作为数据库的客户端的连接器； • 网络：可以是局域网、INTERNET、DCN专线网; • Listener：数据库的监听器，主要监听、接受来自客户端的连接； • 数据库主机：安装数据库的服务器，处理用户的确请求； • 数据文件：存储用户的数据；

11. SQL*PLUS介绍 • Col column format a20 ->字符格式化显示; • Col column format 099999->数字格式化显示; • Edit [filename] ->编辑当前的脚本或者指定一个文件的内容; • @/start filename ->执行某个文件,执行一个数据表脚本的时经常使用; • / 或者run ->运行上次的语句(最后执行的语句); • Spool filename/spool off ->将输出到入一个指定的文件; • Get [filename] ->显示某个文件的内容; • Host command ->执行主机的一条命令; • Set linesize 100 ->将当行的长度设置为100; • Set pagesize 200 ->设置一页显示200行记录 • Set pause on ->设置暂停

12. Select语句的使用（一） • 在使用SELECT之前，你必须先明白你所要查询的表的结构；如果你知道表的结构，如表明名:emp(用户scott/tiger)，用desc emp: • SQL> desc emp • 名称 空? 类型 • ----------------------------------- -------- ---------------- • EMPNO NOT NULLNUMBER(4) • ENAME VARCHAR2(10) • JOB VARCHAR2(9) • MGR NUMBER(4) • HIREDATE DATE • SAL NUMBER(7,2) • COMM NUMBER(7,2) • DEPTNO NUMBER(2)

13. Select语句的使用（二） • 如果你想看当前用户下面有什么表,如下: • SQL> select * from tab; • TNAME TABTYPE CLUSTERID • -------------------------- -------------- ---------- • BONUS TABLE • DEPT TABLE • EMP TABLE • SALGRADE TABLE

14. Select语句的格式 • Select [distinct] { *,{column [alias],….} from table_name [where column_name=column_value [and ….]] [order by column_name [asc/desc]] • Distinct是让所有选择的列是必须唯一的,distinct对所有的列产生作用,而不是第一个列; • 查询所有的列,用select * from emp,将emp表中的数据完全的选择出来;如: • SQL> select * from emp; • EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO • ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- • 7369 SMITH CLERK 7902 17-12月-80 800 20 • 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 • 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 • 7566 JONES MANAGER 7839 02-4月 -81 2975 20 • 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 • 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 • 7782 CLARK MANAGER 7839 09-6月 -81 2450 10

15. Select语句的使用（三） • 在select中可以使用嵌套的select 语句,如:select a.* from emp a,(select deptno from dept) b where a.deptno=b.deptno; • 当然,也可以在where语句后面嵌套select 语句,如: select * from emp where deptno in (select deptno from dept) • 在where语句中，如果是条件必须同时成立的话,用and将条件联合起来,如果是几个条件中只要一个成立就可以,用or联合起来就可以 • 用and的语句:select * from emp where id in (7499,7599) and deptno=10 • 用or的语句:select * from emp where id=7499 or deptno=10;

16. Select语句的使用（四） • 以下两句语句是不一样的,特别需要注意: • Select * from emp where deptno in (select * from dept); • Select * from emp where deptno=(select max(deptno) from dept); • In和=的区别是,用in语句,你可以返回多个值,而用=号的话,你只能返回一个只,否则它会产生出错的信息; • 我们执行=的语句,而且将MAX函数去掉: • select * from emp where deptno=(select deptno from dept) • * • ERROR 位于第 1 行: • ORA-01427: single-row subquery returns more than one row • 而用IN的语句: • SQL> Select * from emp where deptno in (select deptno from dept); • EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO • ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- • 7369 SMITH CLERK 7902 17-12月-80 800 20 • 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 • 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

17. Select语句的使用（五） • 在where语句中,可以使用以下的条件函数; • 如果你不能明确一个用户的名称,可以用模糊查询,关键字是like,如你不知道一个员工含有MIL的字符,不知道详细的名字,可以用以下模糊查询: • Select * from emp where ename like ‘%MIL%’; • 如果你是要查询某一个范围内的数据,可以使用between…and …如: • Select * from emp where empno between 7500 and 7800; • 当然,以上语句可以使用>= and <=的方式来实现; • 在列或者在where语句中,可以使用算术表达式,如:select empno,ename,comm/1024 from emp where (sal+100)>1000;

18. Select语句的使用（六） • 如果是与NULL进行比较，不能用=或者>或者<等操作符，应该用IS NULL 或者IS NOT NULL； • NULL与别的值进行+、—、*、/，都是NULL； • SQL> Select null+100 from dual; • NULL+100 • ---------- • SQL> select * from emp where comm=null; • 未选定行 • SQL> select * from emp where comm is null; • EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO • ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------------------------------- • 7369 SMITH CLERK 7902 17-12月-80 800 20 • 7566 JONES MANAGER 7839 02-4月 -81 2975 20

19. Select语句的使用（七） • 在select语句中,你可以使用order by子句,主要是对数据进行排序; • 如果没有指定按什么方式排序,那么数据库将按生序进行排序; • 如果你在条件中需要不同的排序,需要你自己特定指出; • Select * from emp order by empno,deptno; • Select * from emp order by empno,deptno desc; • Select * from emp order by empno desc,deptno; • 第一个语句中,empno、deptno将按升序排列；而第二个语句则是，empno是按升序，而deptno是按降序；第三个语句按empno是降序，而deptno是升序

20. 单行函数（一） • Decode 对需要的值进行不同的转换，使用于范围较小的情况；如： • Select decode(deptno,10,’财务部’,20,’调研部’,30,’市场部’,’生产部’) from emp; • 以上的语句的意思是，如果deptno=10，则，显示为’财务部‘，如果为20，则为’调研部’,如果是30，则为’市场部’,如果不为10、20、30，则为’生产部’，该函数类似于C语言中switch； • Decode的功能就是对字段进行转义的表示，这种转义在数据迁移的时候经常使用；

21. 单行函数（二） NVL函数，是将如果是空值时，将该列显示成为你需要的值， SQL>select empno,ename,sal,nvl(comm,0) from emp; EMPNO ENAME SAL NVL(COMM,0) ----- ---------- ---------- ----------- 7369 SMITH 800 0 7499 ALLEN 1600 300 7521 WARD 1250 500 7566 JONES 2975 0 而如果没有NVL，则显示为空: SQL> select empno,ename,sal,comm from emp; EMPNO ENAME SAL COMM ---------- ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 300 7521 WARD 1250 500 7566 JONES 2975

22. 单行函数 • Substr( v,m,n)将字符串进行截取，得到需要的字符串,对V字符串的第M开始，截取总数为N的长度; • SQL> select substr(ename,2,3) from emp; • SUBSTR • ------ • MIT • LLE • ARD • ONE • ART • 该函数主要的功能就是得到想要的字符串，在系统迁移的时候也经常的使用；

23. 常用单行函数 • Abs 求绝对值； • Ceil 求最小的整数； • Floor 求最大的整数； • Ltrim/Rtrim 将字符串的空格所含的空格删除；Ltrim是消左边的空格，Rtrim是消右边的空格;(该函数对于字段的数据类型为char的很有效); • Length 求一个字符串的长度； • Lower 将字符串变为小写； • Upper 将字符串变为大写； • Initcap 除第一个字母大写，其余小写； • Sysdate 取系统的当前时间； • To_char 将数值转换成字符； • To_char(date_v,date_format)将日期按你指定的方式转换成字符串； • To_number 将字符串转换为数值； • To_date(char_v,date_format)将字符串转换成为时期格式； • Concat || 将两个字符串相连接

24. 常用组函数 • Count 求总数，对null的值不进行屏蔽； • Sum 求综合，对null的值进行屏蔽； • Avg 求平均值，对null的值进行屏蔽； • Max 求最大值，对null的值进行屏蔽； • Min 求最小值，对null的值进行屏蔽； • Distinct 可以与组合函数一起使用；

25. 组合函数的使用（一） • 如果在select中，除组合函数以外，还有没有使用组合函数的列，必须使用group by，而且列要放在组合函数的前面，而且该列必须在group by的后面； • SQL> select deptno,count(*) from emp group by deptno; • DEPTNO COUNT(*) • ---------- ---------- • 10 3 • 20 5 • 30 6 • 如果没有使用group by ，将产生出错； • SQL> select deptno,count(*) from emp ; • select deptno,count(*) from emp • * • ERROR 位于第 1 行: • ORA-00937: not a single-group group function

26. 组合函数的使用（二） • 如果用户想得到组合函数计算后得到值大于某个范围，必须使用having； • SQL> select deptno,count(*) from emp • 2 group by deptno having count(*)>4; • DEPTNO COUNT(*) • ---------- ---------- • 20 5 • 30 6 • 而不能使用where count(*)>4 • SQL> select deptno,count(*) from emp • 2 where count(*)>4 group by deptno; • where count(*)>4 group by deptno • * • ERROR 位于第 2 行: • ORA-00934: group function is not allowed here

27. 联合查询 • Union 将查询出来的数据合并,但是取消掉重复的记录; • Union all 将查询出来的数据合并,但是不取消掉重复的记录 • Minus 将查询出来的数据进行相减,得到不同的记录; • 自连接 select a.* from emp a,emp b where a.mgr=b.empno ; • 等值连接 select a.*,b.dname from emp a,dept b where a.deptno=b.deptno; • 外连接 select a.*,b.dname from emp a,dept b where a.deptno=b.deptno (+); • 外连接的主要作用是将A表中的所有数据输出,不管是否在B中有对应的巨轮;

28. 数据库的模型 • 关系模型 • 网状模型 • 层次模型 • 现有的数据库主要是关系模型的数据库，如: ORACLE 公司的oracle IBM公司的db2、informix SYBASE公司的sybase MICROSOFT公司的sql server

29. 数据库模型和设计 • 数据库的设计主要由以下的阶段 需求分析 数据库设计 建立/文档 承受力测试 系统测试 最终产品

30. 数据库的设计工具 • Erwin ->CA公司出产针对数据库的专业的设计工具; • Power desginer ->sybase 公司出产的软件开发的设计工具,其中包含数据库的设计模块; • ROSE 2002 ->软件建摸工具,包含数据库的模块data model ; • Visio 2002 ->建摸的设计工具,包含数据库建立模块; • 以上所有的实际工具都能从现有的数据库中导出ER关系模型,并形成脚本; • 这些工具能够将设计规划的实体关系直接导入到数据库中,形成需要的数据表;

31. 模型对象 • 实体(ISMGW) • 属性(PASSWORD) • 关系(FK) • 规则(PK、NN 、 CHECK)

32. 数据类型 • Char(n)->定长字符串如果没有指定长度， 将是默认为长度1，输入的数据如果没有达到指定的长度，将自动补空格，最大2的16次方； • Varchar2(n) ->变长字符串，如果没有指定长度，将产生出错。输入不满指定长度，数据库不会补空格，最大2的16次方； • number(n)->如果没有指定长度，将占用38字节，n<=38; • Date->占的长度为7，固定形式存储格式，精确到秒 • Long/long row->现在基本不用，别lob代替，最大可以2G； • Clob/blob ->存储巨大的数据，一般是varchar2/char存储不下的数据； • Object ->数据库对象，需要自己定义；

33. 建表 • Create table [schema.]table( column datatype [default express], [column constraint], … [table_constraint]); 表主要是存储用户的数据，而如果是系统的表，主要是存储数据字典；

34. 建表（一） • 以下是一个包含有主键、NOT NULL 的表 • Create table department( dept_no number(5) primary key, dept_name varchar2(20) not null, description varchar2(64) null );

35. 建表（二） • 以下脚本主要是建立一个有 FK(外键)、check、default 的表 • Create table employee( empno number(10) primary key, ename varchar2(60) not null, salary number(5) default 1000, comm number(5) check(comm<40000), dept_no number(5) references department(dept_no));

36. 约束 • Primary key ->主键 • Foreign key ->外键 • Not null ->非空的约束 • Check ->满足一定条件的约束 • Default ->默认值的约束 • Unique ->唯一值约束（可包含多个空值） • 如果你不对约束进行命名的话，系统将用SYS_Cnn来显示，如果有约束名，对删除约束将非常方便；

37. 建表（三） • 以下是包含有约束名称的表： • Create table department( dept_no number(5) , constraint pk_emp primary key(dept_no), dept_name varchar2(20) constraint nn_emp1 not null, description varchar2(64) );

38. 建表（四） • 以下是包含有约束名称的表： • Create table employee( empno number(10) primary key, ename varchar2(60) not null, salary number(5) default 1000, comm number(5) check(comm<40000), dept_no number(5), constraint fk_emp foreign key(dept_no) references department(dept_no));

39. 常用数据字典 • Tab 查询常用的表 • User_tables 用户所属的表 • User_tab_columns 表所包含的列； • User_constraints 表的约束； • User_cons_columns关系到列的约束； • User_sequences 序列； • User_indexes 索引； • User_source 存储过程、包、函数； • User_synonyms 同义词； • User_db_links 数据库链； • User_objects 包含以上所有表中的数据; • Dictionary 存放数据字典的字典；

40. 数据字典的说明 • User_ 用户所有的对象； • All_ 除用户所有的对象外，还有属于PUBLIC的部分的对象； • Dba_ 所有的用户的对象； • V$ 数据库的视图（性能视图、动态视图） • User_、all_、dba_的数据所包含的字符全部是大写，而v$包含的数据，经常多时小写的。

41. 改表 • 数据表建立后，可以通过alter来进行修改； • 删除列 alter table employee drop column ename; • 修改列的类型 alter table employee modify(ename char(2)); • 修改列的约束 alter table employee modify(ename null); • 增加列 alter table employee add( address varchar2(64)); • 不能对字段的名称进行修改；新增加的列将加在表的最后；

42. 修改约束 • 删除主键 alter table employee drop primary key; • 增加主键 alter table employee add constraint pk_emp priamry key(empno); • 删除外键：alter table employee drop constraint fk_emp; • 增加外键：alter table employee add constraint fk_emp foreign key(dept_no) references dept(dept_no)); • 增加默认值：alter table employee modify( salaris default 2000);

43. 创建序列 • Create sequence [schema.]sequence_name [start with n] ---开始值 [increment by n] --步长 [maxvalue n] --最大值 [minvalue n] --最小值 [cache n|nochache] 是否缓存在内存中 [cycle/nocycle] 是否可以循环使用 • SQL> Create sequence seq_test • 2 Maxvalue 9999999999 • 3 Minvalue 1000000000 • 4 Nocycle • 5 Nocache • 6 ; • 序列已创建。 • 在系统的数据字典中user_sequences，能够查询到所有的序列，以及其变化； select sequence_name,min_value,max_value,increment_by,last_number from user_sequences;

44. 使用序列(1) • 将序列下个值取出 • SQL> Select seq_test.nextval from dual; • NEXTVAL • ---------- • 1000000000 • 取出序列的当前值 • SQL> select seq_test.currval from dual; • CURRVAL • ---------- • 1000000000 • 在使用currval之前，必须执行过nextval的过程，否则会产生出错；如果执行一个nextval,可以多次执行currval;

45. 使用序列（2） • 序列最经常的使用除了select外，还使用在insert 语句中使用: • Insert into employee (id,ename) values(seq_test.nextval,’guofr’); • 在update语句中，偶尔也使用： • Update employee set id=seq_test.nextval; • Delete语句中非常少使用到； • 序列的主要作用是产生一序列的唯一数，避免用户的自己产生，产生差错； • 如果你要保证序列是递增的，最好用nocache,避免由于系统关闭时，序列产生丢失；

46. 改变序列 • 以下是改变序列的语法： • Alter sequence [schema.]sequence_name [increment by n] [maxvalue n|nomaxvalue ] [minvalue n|nominvalue] [cycle|nocycle] [cache n|nocache] • SQL> alter sequence seq_test • 2 nomaxvalue; • 序列已更改。 • 删除序列 • SQL> drop sequence seq_test; • 序列已丢弃。

47. 视图 • 视图上存储在服务器上的SQL语句，视图本身不存储数据，在执行时，从相关的表中取数据； • 视图的作用有以下方面： • 限制用户的访问，只让用户读取某些列； • 让用户能够得到更直观的数据表示； • 简单用户的查询（如：复杂试图）； • 同样数据的不同显示，用户可通过不同角度得到同样的数据；

48. 创建视图的语法 • Create [or replace] [force|noforce] view v_name ([alias(,alias)]) as subquery with chech option [constraint constraint] with read only • Force强迫建立视图，不管数据表是否存在；noforce，默认方式，只有subquery存在，才能建立视图； • With check option 如果视图是不可更新的，不能有DML操作； • With read only 视图是只读的，不能有任何的DML操作；

49. 创建视图 • 简单视图： • Create view v_emp as select empno,ename from employee; • 复杂的视图： • Create view v_emp_stat as select deptno, count(*) emp_total from employee where deptno<100 group by deptno • 如果你有创建视图，可以通过数据字典user_views来查看用户已经创建的视图；

50. 删除视图 • 从数据字典中，你能够查找到用户所有的视图以及相关的SQL语句； • 试图能够想表一样，用desc 进行描述； • Select view_name , text from user_views; • 删除视图：drop view v_emp;