Oracle数据库课程讲义.docx

1、Oracle数据库课程讲义Oracle数据库简介1. 介绍课程特点和目标2. 相关背景知识回顾3. 介绍oracle数据库的优点，并比较与db2,Sybase ,sql server数据库的优势4. 介绍oracle数据库的历史和版本特点5. 介绍安装和使用的基本技巧Oracle 体系结构体系结构可以分为三个层面来理解：1. 逻辑结构粒度从细到粗可以分为如下结构：块，区，段，表空间，数据库，可以用图表述如下：块区段表空间数据文件数据库 段可以跨数据文件存储2. 物理结构可以分为数据文件，日志文件，控制文件。此外参数文件虽然不属于数据库物理文件，但是在启动过程中也起到重要作用。3. 内存结构需要

2、理解数据库和数据库实例的关系。主要的内存结构是SGA,可以分为如下：A. DBBuffer保持buffer,如代码表再生buffer，使用后就释放的内存，如频繁访问的大表B. 共享池1. 库缓冲区.共享sql区保持访问过的sql命令的解释版本.pl/sql区保持pl/sql过程，函数的编译版本2. 字典缓冲区保存数据库字典表的相关内容C. 大共享池可选的内存区，提供大内存块的隔离内存区D. 固定sqa区不能手工控制，完全由oracle管理E. Redobuffer重做日志缓冲区，管理用于回滚或者重做的内存块Sql plus介绍介绍sql plus的概念和使用方法登陆的命令格式Dos命令提示符下

3、输入sqlplusscott/tiger如果希望以管理员sys身份登陆必须使用sqlplus sys/manager as sysdba关闭和启动oracle实例的命令Shutdown,shutdowntransactional,shutdownimmediate,shutdown abort,区别如图所示：允许新连接等待回话结束等待事务结束做检查点并关闭数据库文件启动需要实例恢复ShutdownnoyesyesYes yesnoShutdown transactionalnonoyesYes yesnoShutdown immediatenononoYes yesnoShutdown abo

4、rtnononoNo noyes启动命令startupStartup mount,startupnomount ,startup启动次序和关闭相反，先要启动数据库实例，然后装配控制文件，最后打开数据和日志文件。Startup nomount ,只启动实例，不装配控制文件Startup mount ,启动数据库实例，装配控制文件Startup,启动数据实例，装配打开所有数据库文件。运行脚本文件可以用加上物理文件路径。Help index可以列出所有上下文帮助主题Set命令可以设置相关环境变量值，常用的有Set pagesize 100Set linesize 100Set echo on/off

5、? set可以列出所有可以设置的环境参数和使用方法。常用的编辑sql的命令：Append ,change/old/newClear buffer,del ,input,list ,run or/Edi将缓冲区命令保持至文件命令save将查询结果保存至文件命令spool 格式化报表的命令:Column sal format $99,990 heading 工资Ttitile center 我的标题 skip 1 left 测试报表 right page: format 999 sql.pnoBreak on+compute命令组合可以产生小计统计的效果Break on deptno skip 1

6、 on job skip2Compute on sum of sal jobSelect * from emp order by deptno,job;Compute可以列出当前compute命令的定义Clear compute可以清除该定义可以在报表结束处现实统计结果Break on reportCompute sum of sal on reportSelect * from emp;Sql(一)分类1. DdlCreate ,alter ,drop ,truncateCreate user kp identified by kp account unlock;Alter user kp

7、identified by pk;Drop user kp;Truncate table emp;Create table abc(a varchar2(10),b char(10);Alter table abc modify(a varchar2(20);Alter table abc add c number;Alter table abc drop column c；Alter table abc add constraint c1 check(c between 1 and 10);alter table abc add constraint c2 primary key (a);a

8、lter table abc add constraint c3 primary key(a);alter table abc add constraint c3 foreign key(b) references a(x);(create table a (x char(10);alter table a add constraint c1 primary key(x)2. DmlSelect ,update,delete,insert3. DclGrant,revoke,rollback,commit,savepoint Grant resource to kp;Grant select

9、on emp to kp;Revoke select on emp from kp;上机的正常操作顺序应该是先创建表空间，然后创建用户，然后创建相关db objectCreate temporary tablespacekp_temptempfile size 50m autoextend on next 50m maxsize 20480m;Create tablespacekp_datadatafilesie 50m autoextend on next 50m maxsize 20480m;Create user kp identified by kp default tablespac

10、ekp_data temporary tablespacekp_temp;Grant connect ,resource to kp;Sql(二)系统函数1. 字符Length,lengthb,ltrim,rtrim ,trim,replace,substr2. 日期函数Sysdate,current_date,next_dayCurrent_date,sysdate区别a. 前者是当前会话时间，后者是服务器时间b. 有时前者比后者块1秒，四舍五入结果c. 修改时区，将中国从东八区改为东九区，则前者比后者快一个小时Alter session set nls_data_format=yyyy-m

11、m-ddhh:mi:ss;Alter session set nls_data_format=yyyy-mm-dd hh24:mi:ss;Alter session set time_zone=+09:00可以用命令 select * from v$nls_parameters;查询当前nls_date_languageAlter session set nls_date_language=AMERICAN;(simplified Chinese)可以通过 select username,sid ,serial# from v$session;Alter system kill session

12、 sid,serial#杀死相关会话Next_day根据相关参数给出对应周几的具体日期3. 类型转换To_char,to_date,to_number4. 聚集函数Sum,avg,max ,min,count5. 其他User,decode,nvl,nvl2Select sum(decode(sex,男,1,0),sum(decode(sex,女,1,0) from emp;问题：统计10,20部门人数？Select sum(decode(deptno,10,1,0),sum(decode(deptno,20,1,0) from emp;工资分为三等，一千以内，一千至两千，两千以上，分别统计各

13、部门各等人数Select deptno,sum(decode(greatest(sal,0),least(sal,1000),1,0)，sum(decode(greatest(sal,1000),least(sal,2000),1,0),sum(decode(greatest(sal,2000),sal,1,0) from emp;也可以用case命令来实现：select x,count(*) as cfrom ( select eno, (case when sal between 0 and 999 then 一等 when sal between 1000 and 1999 then 二

14、等 else 三等 end) x from emp) group by x 或者Select count(case when sal2000 then 1 else null end) 三等 from emp;也可以写成：Select sum(case when sal2000 then 1 else 0 end) 三等 from emp;要注意其异同。6. 分组7. 模糊查询记住通配符_和%8. 表连接分为三种，内连接（自然连接），外连接（左，右，全），自连接A内连接Select emp.empno,dept.loc form emp inner join dept on emp.deptn

15、o=dept.deptnoOrSelect emp.empno,dept.loc from emp natural join on emp.deptno=dept.deptno;B外连接Selectemp.empno,dept.deptno from emp left outer join dept on emp.deptno=dept.deptnoOr select emp.empno,dept.deptno from emp where emp.deptno=dept.deptno(+) (左连接)Select emp.empno,dept.deptno from emp right ou

16、ter join dept on emp.deptno=dept.deptnoOr select emp.empno,dept.deptno from emp where emp.deptno(+)=dept.deptno (右连接)Select emp.empno,dept.deptno from emp full outer join dept on emp.deptno=detp.deptno(全外连接)自连接考虑查询员工和其领导的名字Select x.ename ,y.ename from empx,emp y where x.mgr=y.empno9. 子查询10. 代数运算Inte

17、rsect,union,minusSql(三)伪列rownum,rowid的使用什么是伪列RowID？1.首先是一种数据类型，唯一标识一条记录物理位置的一个id，基于64位编码的18个字符显示。2.未存储在表中，可以从表中查询，但不支持插入，更新，删除它们的值。RowID的用途1.在开发中使用频率应该是挺多的，特别在一些update语句中使用更加频繁2.能以做快的方式访问表中的一行3.能显示表的行是如何存储的4.作为表中唯一标识删除重复数据比较高效Delete empa Where ROWID Not In(Select Min(ROWID) From empa Group By empno)

18、;什么是rownum对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。注意select rownum ,id,name from student order by name;和select rownum ,id,name from (select * from student order by name);的区别对emp表，显示工资排名第三高的职工信息select empno,sal from(select rownum as rn,empno,

19、sal from (select * from emp order by saldesc ) )where rn=3或者select * from (select emp.*,dense_rank() over (order by saldesc) rank from emp) where rank=3;层次查询中的伪列levelSelect level,employee_id,manager_id,first_name|last_name from more_employees start with Employee_id=1 connect by prioremployee_id=mana

20、ger_id;可以通过Select count(distinct level) from more_employees start with employee_id=1 connect by priorEmployee_id=manager_id获得树的层次。格式化查询结果Set pagesize 999Column employee format a25Select level,lpad(,2*level-1)|first_name|last_name as employee from more_employees start with employee_id=1 connect by pr

21、ior employee_id=manager_id;Sql(四)小计统计函数rollup,cubeSelect deptno,job ,sum(sal) from emp group by rollup(deptno,job);如果希望对以上结果格式化输出，如何更改？将rollup改为cube，观察结果不同点使用评价函数rank,dense_rank考虑问题如下：找到各部门工资前三高的员工信息Select * from (select rank() over (partition by deptno order by saldesc) rk ,emp.* from emp) T where

22、t.rk=2;select distinct x.s# from scx,sc y where x.s#=y.s# and x.c#!=y.c#d.查询全部学生都选修的课程号与课程名select c#,cname from cwhere c# in (select c# from sc group by c# having count(*)=(select count(*) from s);select c# ,cname from c where not exists (select * from s where not exists(select * from sc where s#=s.

23、s# and c#=c.c#)e.查询选修了王五老师所教授所有课程的学生学号select distinct s# from sc x where not exists(select * from c where techar=wang and not exists(select * from sc y where y.s#=x.s# and y.c#=c.c#)2.关系模式如下markext(mno,mname,city),item(ino,iname,type,color),sales(mno,ino,price)a.列出北京每个商场都有售，且售价都超过10000元的商品号和商品名selec

24、t ino,iname from item where not exists(select * from markedt where city=bj and not exists (select * from sales where price10000 and markext.mno=sales.mno and sales.ino=item.ino)b.列出在不同商场中最高售价和最低售价之差超过100的商品号，最高售价和最低售价select ino ,max(price),min(price) from sales group by ino having max(price)-min(pri

25、ce)100select deptno from dept where not exits(select * from emp where dept.deptno=emp.deptno)Sql(六)索引和优化索引可以分为B树索引，位图索引，基于函数的索引，分别对应不同的应用需求索引的创建语法：CREATE UNIUQE | BITMAP INDEX .ON .( | ASC | DESC, | ASC | DESC,.)TABLESPACE STORAGE LOGGING | NOLOGGINGCOMPUTE STATISTICSNOCOMPRESS | COMPRESSNOSORT | REVERSEPARTITION | GLOBAL PARTITION相关说明1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。2） | ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)4）STORAGE：可进一步设置表空间的存储参数5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)6）COMPUTE STATISTI