Oracle培训文档.doc

ORACLE培训序言针对我们公司人员对ORACLE的应用主要倾向于SQL语句，所以我们这次我们一块来研究研究在ORACEL中使用的SQL的语法，以及常用到的一些函数。培训内容 ORACLE数据库的连接 查询语句的使用 常用的一些函数 SQL语句中的子句 操作数据 创建和操作表,视图 创建序列 创建触发器 创建用户和修改用用户权限 游标的使用详细内容一、概论 ORACLE 是以高级结构化查询语言(SQL)为基础的大型关系数据库，通俗地讲它是用方便逻辑管理的语言操纵大量有规律数据的集合。是目前最流行的客户/服务器(CLIENT/SERVER)体系结构的数据库之一。 二、特点 1、ORACLE7.X以来引入了共享SQL和多线索服务器体系结构。这减少了ORACLE的资源占用，并增强了ORACLE的能力，使之在低档软硬件平台上用较少的资源就可以支持更多的用户，而在高档平台上可以支持成百上千个用户。 2、提供了基于角色(ROLE)分工的安全保密管理。在数据库管理功能、完整性检查、安全性、一致性方面都有良好的表现。 3、支持大量多媒体数据，如二进制图形、声音、动画以及多维数据结构等。 4、提供了与第三代高级语言的接口软件PRO*系列，能在C,C+等主语言中嵌入SQL语句及过程化(PL/SQL)语句，对数据库中的数据进行操纵。加上它有许多优秀的前台开发工具如 POWER BUILD、SQL*FORMS、VISIA BASIC 等，可以快速开发生成基于客户端PC 平台的应用程序，并具有良好的移植性。 5、提供了新的分布式数据库能力。可通过网络较方便地读写远端数据库里的数据，并有对称复制的技术。 三、存储结构 1、物理结构 ORACLE数据库在物理上是存储于硬盘的各种文件。它是活动的，可扩充的，随着数据的添加和应用程序的增大而变化。 2、逻辑结构 ORACLE数据库在逻辑上是由许多表空间构成。主要分为系统表空间和非系统表空间。非系统表空间内存储着各项应用的数据、索引、程序等相关信息。我们准备上马一个较大的ORACLE应用系统时，应该创建它所独占的表空间，同时定义物理文件的存放路径和所占硬盘的大小。 四、分布式数据库管理介绍 1、原理 物理上存放于网络的多个ORACLE数据库，逻辑上可以看成一个单个的大数据库。用户可以通过网络对异地数据库中的数据同时进行存取，而服务器之间的协同处理对于工作站用户及应用程序而言是完全透明的：开发人员无需关心网络的连接细节、无需关心数据在网络接点中的具体分布情况、也无需关心服务器之间的协调工作过程。 2、过程 由网络相连的两个ORACLE数据库之间通过数据库链接(DB-LINKS)建立访问机制，相当于一方以另一方的某用户远程登录所做的操作。但ORACLE采用的一些高级管理方法，如同义词(SYNONME)等使我们觉察不到这个过程，似乎远端的数据就在本地。数据库复制技术包括：实时复制、定时复制、储存转发复制。对复制的力度而言，有整个数据库表的复制，表中部分行的复制。在复制的过程中，有自动冲突检测和解决的手段。数据库的备份与恢复数据库的备份主要分为三种类型：冷备份；热备份；逻辑备份；Oracle的启动与关闭1、shutdown normal 正常方式关闭数据库。 2、shutdown immediate 立即方式关闭数据库。 在SVRMGRL中执行shutdown immediate，数据库并不立即关闭， 而是在Oracle执行某些清除工作后才关闭（终止会话、释放会话资源）， 当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。 3、shutdown abort 直接关闭数据库，正在访问数据库的会话会被突然终止， 如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。1、shutdown normal （从字面就可理解这是一个用正常的方式来关闭数据库。） 2、shutdown immediate （立即方式关闭数据库。） 当执行shutdown immediate时，数据库并不立即关闭，而是在Oracle执行完内部的必要的工作后才关闭，shutdown immediate可以完成shutdown不能对数据库关闭的操作。建议DBA们常用此方法关闭数据库 3、shutdown abort （直接关闭数据库） 这个停止的方法不推荐，因为对于正在访问数据库的会话会被突然终止，可能会造成数据库的数据丢失，并且如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。 startup 启动数据库$ lsnrctl start 启动监听 $lsnrctl status 查看监听的状态 ORACLE数据库的连接SID，SID在ORACLE中是一个数据库的实例。它是用来区分同一台服务器上的数据库的。服务名，在ORACLE中，服务名是用来指定要连接的数据库服务器和数据库实例。一般是以SID+“_”+服务器地址组成。如：我们要连ZX（192.168.1.7）服务器上的ZZYXDB这个数据库实例，那么我们就可以通过“ZZYXDB_ZX或ZZYXDB_192.168.1.7”服务名连接到ZX服务器上的ZZYXDB这个数据库实例。另外我们也可以通过Net8 Assistant来注册一个服务名，在用Net8 Assistant来注册服务名时我们可以以任意的一个合法的标识符来为服务名命名，在注册服务名时ORACLE会提示我们指定SID和服务器，请注意，当我们在Net8 Assistant中注册有ORACLE服务名时，我们必须使用我们注册的服务名来连接我们须要连接的数据库实例。查询语句的使用SELECT 语句在ORACLE和SQL SERVER2000中是一样的。所以我们就不说细介绍,只写出几个SELECT 语句的示例。为了便于培训，我在ZX服务器上的ZZYXDB实例上的TEST用户在创建了两个表STUDENT，SCORE,TEST用户的口令是“zx”。我们可以查看一下STUDENT 表的结构。SQL desc student; 名称 空? 类型 - - - CID NOT NULL NUMBER NAME VARCHAR2(15) OLD NUMBER ADDRESS VARCHAR2(15) BIRTHDAY DATE查看一下该表中所有的数据SQL select * from student; CID NAME OLD ADDRESS BIRTHDAY- - - - - 22 王刚 19 安阳 05-4月 -83 21 李明 18 信阳 18-10月-84 23 郑朋 20 信阳 11-10月-84 24 王芳 19 郑州 09-9月 -85 25 王锋 20 南阳 02-10月-83 26 李小丽 19 许昌 06-2月 -85已选择6行。选择不重复的记录SQL select distinct address from student;ADDRESS-安阳南阳信阳许昌郑州指定条件查找SQL select * from student where cid = 25; CID NAME OLD ADDRESS BIRTHDAY- - - - -25 王锋 20 南阳 02-10月-83查询中的运算在ORACLE中“+”只能用来求和，不能用于两字符串的连接，ORACLE中的连接符中“|”。如果我们在两个字符串中间使用“+”来进行连接，则会出现“ORA-01722: 无效数字”的错误提示。SQL select cid,name,old,address,birthday,old+1,address|市city from student; CID NAME OLD ADDRESS BIRTHDAY OLD+1 CITY- - - - - - - 22 王刚 19 安阳 05-4月 -83 20 安阳市 21 李明 18 信阳 18-10月-84 19 信阳市 23 郑朋 20 信阳 11-10月-84 21 信阳市 24 王芳 19 郑州 09-9月 -85 20 郑州市 25 王锋 20 南阳 02-10月-83 21 南阳市 26 李小丽 19 许昌 06-2月 -85 20 许昌市已选择6行。集合运算现在我们来查看一下另一张表中的所有数据SQL select * from score; CID NAME SUBJECT SCORE- - - - 21 李明 数学 80 21 李明 语文 95 21 李明 English 90 22 王刚 数学 86 22 王刚 语文 95 24 王芳 English 90 24 王芳 数学 80 24 王芳 语文 80 26 李小丽 数学 78 26 李小丽 语文 88 26 李小丽 English 85已选择14行。返回两个查询的结果，但去掉其中重复的部分。注意：两个查询的列必须数目相同，数据类型相对应，否错会出现错误。SQL select name from score 2 union 3 select name from student;NAME-李明李小丽王芳王锋王刚郑朋已选择6行。返回两个查询的结果，包括重复的记录SQL select name from score 2 union all 3 select name from student;NAME-李明李明李明王刚王刚王芳王芳王芳李小丽李小丽李小丽王刚李明郑朋王芳王锋李小丽已选择17行。返回两个查询结果中，共有的行，即相交SQL select name from score 2 intersect 3 select name from student;NAME-李明李小丽王芳王刚返回两个查询中，第一个查询中存在，而第二个查询中不存在的记录。注意：在使用时应注意先后顺序，位是前面的查询是第一个查询。在ORACLE中，如果提示“末选定行”表示查询结果的记录数是0行，不是错误提示。SQL select name from score 2 minus 3 select name from student;未选定行交换两个查询的位置SQL select name from student 2 minus 3 select name from score;NAME-王锋郑朋常用的一些函数Count 返回记录数SQL select count(*) from score; COUNT(*)- 11Sum 返回某列符合条件的所有行的和SQL select sum(score) from score where name = 李明;SUM(SCORE)- 265Avg 返回某一列符合条件所有行的平均值SQL select avg(score) from score where name = 李明;AVG(SCORE)-88.3333333Add_months 返回给定日期的下一月。SQL select student.*,add_months(birthday,1) from student ; CID NAME OLD ADDRESS BIRTHDAY ADD_MONTHS- - - - - - 22 王刚 19 安阳 05-4月 -83 05-5月 -83 21 李明 18 信阳 18-10月-84 18-11月-84 23 郑朋 20 信阳 11-10月-84 11-11月-84 24 王芳 19 郑州 09-9月 -85 09-10月-85 25 王锋 20 南阳 02-10月-83 02-11月-83 26 李小丽 19 许昌 06-2月 -85 06-3月 -85已选择6行。LAST_DAY 返回指定日期当月月份的最后一天SQL select student.*,last_day(birthday) from student ; CID NAME OLD ADDRESS BIRTHDAY LAST_DAY(B- - - - - - 22 王刚 19 安阳 05-4月 -83 30-4月 -83 21 李明 18 信阳 18-10月-84 31-10月-84 23 郑朋 20 信阳 11-10月-84 31-10月-84 24 王芳 19 郑州 09-9月 -85 30-9月 -85 25 王锋 20 南阳 02-10月-83 31-10月-83 26 李小丽 19 许昌 06-2月 -85 28-2月 -85已选择6行。SYSDATE 将返回系统的日期和时间SQL select sysdate from student;SYSDATE-04-3月 -0404-3月 -0404-3月 -0404-3月 -0404-3月 -0404-3月 -04已选择6行。CEIL 返回与给定参数相等或比给定参数在的最小整数。FOOR 则正好相反它返回与给定参数相等或比给定参数小的最大整数。SQL select ceil(7.85),floor(7.85) from student where cid=21;CEIL(7.85) LOOR(7.85)- -8 7Sign 如果参数为负则返回-1 如果参数的值为正数那么返回1 当参数为零那么则返回0.SQL select sign(-3),sign(5),sign(0) from student where cid=21; SIGN(-3) SIGN(5) SIGN(0)- - - -1 1 0Concat将数值转换为字符串。SQL select concat(cid,name) from student;CONCAT(CID,NAME)-22王刚21李明23郑朋24王芳25王锋26李小丽已选择6行。LOWER 将参数转换为全部小写字母,UPPER 则把参数全部转换成大写字Ltrim 去掉左边指定的字符，Rtrim 去掉右边指定的字符，如果没有指定字符则默认为空格。SQL select cid,subject,rtrim(subject,h) from score; CID SUBJECT RTRIM(SUBJECT,h)- - - 21 数学 数学 21 语文 语文 21 English Englis 22 数学 数学 22 语文 语文 24 English Englis 24 数学 数学 24 语文 语文 26 数学 数学 26 语文 语文 26 English Englis已选择11行。Replace完成替换，它有三个参数，第一个参数为原始字符串，第二个参数为要替换的字符串，第三参数为要替换成什么字符串。如果省略第三个字符串则默认为空格。SQL select cid,subject,replace(subject,li,re) from score; CID SUBJECT REPLACE(SUBJECT,LI,RE)- - - 21 数学 数学 21 语文 语文 21 English Engresh 22 数学 数学 22 语文 语文 24 English Engresh 24 数学 数学 24 语文 语文 26 数学 数学 26 语文 语文 26 English Engresh已选择11行。Substr 取字符串的子串，他有三个参数，第一个参数是原字符串，第二个参数是子串的起始位置，第三个参数是子串的长度。SQL select cid,subject,substr(subject,1,2) from score; CID SUBJECT SUBS- - - 21 数学 数学 21 语文 语文 21 English En 22 数学 数学 22 语文 语文 24 English En 24 数学 数学 24 语文 语文 26 数学 数学 26 语文 语文 26 English En已选择11行。Instr 返一个字符串在原字符串中的位置。如果原字符串在不存在要找的字符串，那么，将返回0。SQL select cid, subject ,instr(subject ,li,1) from score; CID SUBJECT INSTR(SUBJECT,LI,1)- - - 21 数学 0 21 语文 0 21 English 4 22 数学 0 22 语文 0 24 English 4 24 数学 0 24 语文 0 26 数学 0 26 语文 0 26 English 4已选择11行。Length 返回一个字符串的长度。SQL select cid,subject,length(subject) from score; CID SUBJECT LENGTH(SUBJECT)- - - 21 数学 2 21 语文 2 21 English 7 22 数学 2 22 语文 2 24 English 7 24 数学 2 24 语文 2 26 数学 2 26 语文 2 26 English 7已选择11行。To_char 将其它数据类型的数据转换为字符。SQL select cid,birthday,to_char(birthday),length(to_char(birthday) from student; CID BIRTHDAY TO_CHAR(BI LENGTH(TO_CHAR(BIRTHDAY)- - - - 22 05-4月 -83 05-4月 -83 9 21 18-10月-84 18-10月-84 9 23 11-10月-84 11-10月-84 9 24 09-9月 -85 09-9月 -85 9 25 02-10月-83 02-10月-83 9 26 06-2月 -85 06-2月 -85 9已选择6行。To_number 将字符串转换为数字。为了演示，我们先student表中插入一条，name为256.7的学生信息。SQL select name,to_number(name)+5 from student where name = 256.7;NAME TO_NUMBER(NAME)+5- -256.7 261.7User 返回当前使用的用户。SQL select user from student;USER-TESTTESTTESTTESTTESTTESTTEST已选择7行。SQL语句中的子句Where 子句。Order by 子句，排序。SQL select * from student order by cid; CID NAME OLD ADDRESS BIRTHDAY- - - - - 21 李明 18 信阳 18-10月-84 22 王刚 19 安阳 05-4月 -83 23 郑朋 20 信阳 11-10月-84 24 王芳 19 郑州 09-9月 -85 25 王锋 20 南阳 02-10月-83 26 李小丽 19 许昌 06-2月 -85 41 256.7 18 演示数据已选择7行。Group by 分组语句。SQL select subject, sum(score) from score group by subject;SUBJECT SUM(SCORE)- -English 265数学 324语文 358操作数据Insert 插入数据SQL insert into student(name,old,address,birthday) 2 values(张华,19,平顶山,to_date(1984-5-6,yyyy-mm-dd) 3 ;已创建 1 行。Insert select 插入数据，来自于表SQL insert into student(name,old) 2 select name,1 from score;已创建11行。Update 更新语句，语法：UPDATE table_name SET columnname1 = value1 , columname2 = value2.WHERE search_conditionDelete 删除语句，语法：DELETE FROM tablename WHERE conditionSQL delete student where name = 256.7;已删除 1 行。创建和操作表,视图 Create table 创建表。SQL create table testtable( 2 col1 varchar2(10), 3 col2 varchar2(10) not null, 4 col3 number(18,2) not null 5 );表已创建。SQL desc testtable 名称 空? 类型 - - - COL1 VARCHAR2(10) COL2 NOT NULL VARCHAR2(10) COL3 NOT NULL NUMBER(18,2)用一个已经存在的表创建一个新表。SQL create table student1 as( select * from student);表已创建。SQL select * from student1; CID NAME OLD ADDRESS BIRTHDAY-