oracle学习资料6140702711.doc

目录第一章 Oracle数据库基础31.3 SQL、SQL*Plus及 PL/SQL31.3.1 SQL和SQL*PLUS的差别31.3.2 PL/SQL语言51.7 ORACLE数据类型51.8 SQL 语句基础111.8.1 SQL语句所用符号111.8.2 简单select 查询13第二章 查询基础132.1 SELECT语句132.2 SQL中的单记录函数142.2.1 单记录字符函数142.2.2 单记录数字函数192.2.3 单记录日期函数252.2.4 单记录转换函数282.2.5 其它的单记录函数312.3 SQL中的组函数362.3.1 多记录组函数362.3.2 带 GROUP BY 的计算382.3.3 用 HAVING 来限制分组的计算382.4 控制和格式化输出392.4.1 用 ORDER BY 子句来对结果进行排序392.4.2 用 BREAK 命令 来对结果进行排列392.4.3 用 COMPUTE 命令对结果进行格式化41第三章 表及索引的定义操作453.1 建立表结构453.1.1 建立表结构命令463.1.2 建立表结构例子473.1.3 建立临时表结构493.3 修改表结构503.3.1 修改表结构命令503.3.2 修改表结构例子513.3.3 删除表结构523.3.4 使用CHECK作限制约束533.3.5 使用UNRECOVERABLE创建表533.3.6 将表移动到新的数据段或新的表空间543.3.7 手工分配表的存储空间553.3.8 标记不使用的列和删除不使用的列563.3 主键583.3.1 创建主键583.3.2 改变主键603.3.3 删除主键613.4 外部键613.4.1 建立外部键623.4.2 修改外部键633.4.3 删除外部键64第六章 使用SQL 进行数据操作646.1 INSERT操作646.1.1 用文字插入操作656.1.2 用子查询插入操作656.2 UPDATE操作666.2.1 用文字更新操作66UPDATE accountsboston676.2.2 用查询更新操作676.2.3 用相关子查询更新操作676.3 DETELE操作686.3.1 用delete删除全部记录的操作686.3.2 用delete有条件删除部分记录696.3.3 用delete分段删除大量记录696.4 insert、delete及update 的提交和撤消706.4.1 自动提交的设置706.4.2 保留点和撤消71第七章 复杂查询语句的使用737.1 复杂查询语句的使用747.1.1 相关子查询747.1.2 外连接747.1.3 自我连接757.1.4 UNION,INTERSECT及MINUS76第九章 安全管理779.1 CREATE USER 命令779.2 建立用户789.2.1 外部验证（Authenticated ）用户789.2.2 全局(Globally)验证用户-企业验证789.3 ALTER USER 命令799.4 DROP USER 命令799.5 GRANT 命令与REVOKE 命令809.5.1 GRANT 命令809.5.2 REVOKE 命令809.6 权限和角色819.6.1 建立角色819.6.2 给角色授权829.6.3 授权角色给用户83第一章 Oracle数据库基础1.3 SQL、SQL*Plus及 PL/SQL下面用简单的语言解释Oracle的常用产品所包含的SQL*PLUS和PL/SQL的关系。1.3.1 SQL和SQL*PLUS的差别SQL是标准结构查询语言，而SQL*PLUS实际是包括标准的SQL和Oracle公司的一些命令组成的产品，因而Oracle公司将其取名为SQL*PLUS。下面是它们的解释。1. SQL（Structured Query Language）SQL有许多关键字，以下语句是常用于开头的语句：Alter Insert Audit Lock Commit NoauditComment RenameCreate RevokeDelete SelectDrop UpdateGrant Validate注：1.Oracle的SQL缓冲区一次只能存放一条SQL命令；2.Validate (使生效) 在 oracle 中跟 Enable 一起用，但可以省去 Validate,所以许多资料都不介绍Validate 的用法。其语法如：EnableValidateNovalidate UNIQUEPRIMARY KEY. .2. SQL*PLUS除SQL外，SQL*PLUS还包括称为SQL*PLUS命令的附加命令，这些命令主要用于形成复杂报表，编辑SQL命令，提供帮助信息，维护系统等。SQL*PLUS包括的命令如下： Connect Host Set# Copy Input Show$ Define List Spool/ Del Newpage SqlplusAccept DescribePause StartAppend DisconnectQuit Timing Break Document Remark TtitleBtitle Edit Prompt UndefineChang Execute Print SaveClear ExitRun ColumnGet RunformCompute Help3. DDL（Data Define Language）对于结构查询语言（有时称SQL命令），可以将它们分成两组，一组是数据定义语言(DDL)；另一组是数据操纵语言（DML）。其中用于数据定义的语言如下： Alter procedure 重编译过程 Alter table 修改表的属性 Analyze 统计数据库对象性能值 Alter table add Constraint 对已有的表加约束 Create table 建立表结构 Create index 建立索引 Drop table 删除表实体及相关的索引 Drop index 删除索引 Grant 授权给用户或角色 Truncate 删除表中的所有行 Revoke 从用户或角色收回权限4DML（Data Manipulation Language）对于结构查询语言的另一组是数据操纵语言（DML）。DML其中用于数据操纵的命令如下： Insert Delete Update Select Commit work Rollback1.3.2 PL/SQL语言PL/SQL是Oracle RDBMS (Oracle 6 之后版本)的一个组成部分，PL 是“过程化语言（Procedure Language ）”的缩写。PL/SQL语言是在SQL语言中结合了结构化过程语言成分的准第四代语言。使用PL/SQL的目的：由于大多数PL/SQL是在服务端来运行，这样可减少由客户端运行程序时所需的网络数据流量。可以使用PL/SQL的地方：l PL/SQL可以单独进行程序的编写，完成一般的处理功能；l 在高级语言中可嵌入PL/SQL 块；l 在4GL中可以嵌入PL/SQL 块；l 在PL/SQL程序中可以嵌入 HTML和XML。1.7 ORACLE数据类型Oracle数据库的数据类型与其它的数据库系统相比，它的数据类型不多，Oracle在表示数据方面比其他数据库系统来说要省去许多关键字。Oracle只用NUMBER(m,n)就可以表示任何复杂的数字数据。其它如日期类型等也简单得多，只DATE就表示日期和时间。下面以列表形式给出各个版本的Oracle系统数据类型的表示方法。下面给出Oracle旧版本的数据类型的目的是让读者了解Oracle的变化，另外就是你在对旧版本进行升级或数据转换时要注意各个版本的差别。ORACLE5、ORACLE6数据类型数据类型说明Char可变长字符型，254Varchar2可变长字符型，2000Number(m,n)数字类型，含整数、小数等Date日期型，含时间，缺省格式为mmm-dd-yyyy hh:mi:ss（占7字节）Long存储大型可变长字符串，2GBRaw存储短二进制串，2GBLong raw存储长二进制串，2GB ORACLE7数据类型数据类型说明Char定长字符，255个字符Varchar变长字符，2000个字符Varchar2变长字符，2000个字符Number(m,n)数字类型，含整数、浮点、双精度等Long存储大型可变长字符串，2GBRaw存储可变短二进制数，2000Long raw存储大型可变长二进制数，2GB ORACLE8/8i 数据类型数据类型说明Char定长字符，2000个字符Varchar（同Varchar2）可变字符，4000个字符Varchar2变长字符，4000个字符Date固定长度（7字节）的日期型Number数字型，可存放实型和整型Long可变字符，2GB个字符Raw可变二进制数据，4000字节Long raw可变二进制数据，2GBMLSLABEL仅Trusted Oracle 用长度在25字节间Blob大二进制对象，4GBClob大字符串对象，4GBNclob多字节字符集的Clob，4GBBfile外部二进制文件，大小由OS决定CHAR()定长字符型（在Oracle5 、Oracle6 是变长），字符长度不够自动在右边加空格符号。当字符长度超出2000个则错误。不指定大小缺省为 1。VARCHAR()可变字符型，当前与VARCHAR2()相同。VARCHAR2()可变字符型，当前与VARCHAR()相同。VARCHAR2 类型的字段（列）可存放4000个字符；但是VARCHAR2 变量可以存放32,767 个字符。大小必须指定。NCHAR()和NVARCHAR2()NCHAR 和NVARCHAR2 分别与 CHAR和VARCHAR2 有相同的大小。并用于于存放 National Language Support (NLS)数据，Oracle 允许以本地语言存放数据和查询数据。如果将列名声明成NCHAR、NVARCHAR2这样的类型，则insert和 select 等语句中的具体值前加N，不能直接按照普通字符类型进行操作。看下面例子：SQL create table nchar_tst(name nchar(6),addr nvarchar2(16),sal number(9,2);表已创建。SQL insert into nchar_tst values(N赵元杰,N北京市海淀区,9999.99);已创建 1 行。SQL select * from nchar_tst where name like N赵%;NAME ADDR SAL- - -赵元杰 北京市海淀区 9999.99SQL select * from nchar_tst where name like 赵%;select * from nchar_tst where name like 赵% *ERROR 位于第 1 行:ORA-12704: 字符集不匹配.提示：虽然Oracle可以使用nchar, nvarchar2 类型来存放字符数据，但建议设计者不要使用NCHAR和NVARCHAR2。因为CHAR和VARCHAR2就能存放汉字。NUMBER(,) 是数据的整数部分，是数据的精度（即小数）部分,注意，部分可以表示负的精度。用可以表示从小数点往右或往左保留多少位。如下表：实际值数据类型存储值1234567.89Number1234567.891234567.89Number(8)12345681234567.89Number(6)出错1234567.89Number(9,1)1234567.91234567.89Number(9,3)出错1234567.89Number(7,2)出错1234567.89Number(5,-2)12346001234511.89Number(5,-2)12345001234567.89Number(5,-4)12300001234567.89Number(*,1)1234567.9Sal number(7,2), -表示5位整数，2位小数.DATEOracle 的日期型用7个字节表示，每个日期型包含如下内容：Century (世纪)Year （年）Month（月）Day （天）Hour （小时）Minute （分）Second （秒）日期型字段有下面特点：日期型字段的插入和更新可以数据型或字符并带to_date 函数说明即可。缺省的日期格式有NLS_DATE_FORMAT参数控制，它的缺省格式为DD-MON-YY。缺省的时间是夜里 00:00:00 (即0点 0分0秒)。sysdate返回的是服务器的时间，见下面例子。日期格式的显示可以设置，见下面例子。日期型可以运算，见下面例子。见下面例子。世纪用cc 表示；年用yyyy表示，月用mm表示，日用dd表示，小时用hh24表示，分用mi表示，秒用ss表示。例子：SQL create table save_info(per_id varchar2(20),name varchar2(20),tran_date date, 2 tran_val number(12,2);表已创建。SQL insert into save_info values ( 110105540609811,赵元杰, 2 to_date(2001.06.18,yyyy.mm.dd),12345.66);已创建 1 行。SQL select * from save_info;PER_ID NAME TRAN_DATE TRAN_VAL- - - -110105540609811 赵元杰 18-6月 -01 1234.66SQL select per_id,name,to_char(tran_date,yyyy/mm/dd),tran_val from save_info;PER_ID NAME TO_CHAR(TR TRAN_VAL- - - -110105540609811 赵元杰 2001/06/18 12345.66SQL show parameter nls_date_formatNAME TYPE VALUE- - -nls_date_format stringSQL alter session set nls_date_format= 2 公元yyyy年mm月dd日;会话已更改。SQL select sysdate from dual;SYSDATE-公元2001年05月18日SQL select to_char(sysdate,cc yyyy.mm.dd) from dual;TO_CHAR(SYSDA-21 2001.05.18关于日期型的使用方法详细请参考Oracle8i National Language Support Guide。BLOB大二进制对象，每条记录可存储达4GB的数据，详细见后面章节。CLOB大字符对象，每条记录可存储达4GB的数据，详细见后面章节。BFILE外部二进制文件，每条记录可存储达4GB的数据（与OS有关），详细见后面章节。RAW非结构的二进制数据，这些数据不被数据库系统解释。RAW可以存储达2,000字节。LONGRAW大的二进制类型数据，LONGRAW是非结构的二进制数据，这些数据不被数据库系统解释。LONGRAW可以存储达2GB字节。LONGRAW不能被索引，而RAW可以被索引。ROWIDROWID在Oracle数据库中是一个虚的列，即系统用的特殊的列，不是我们建立的列。用于对数据库中的每条记录进行定位。详细见“Rowid的使用”章节。UROWIDUROWID 是Universal ROWID 的意思。即全球ROWID，它支持逻辑和物理ROWID，也作为外部表的（通过getway 访问的非Oracle 表）的ROWID。UROWID类型可以存储所有的ROWID类型的数据。%TYPE类型的匹配： books_printed number(6)； books_sold books_printed%TYPE； books_sold的数据类型与book_printed的类型一致；( %TYPE类型 在 PL/SQL中介绍 )。空值与字符型、数字型的运算： null + =null （空值+数字仍是空值） null =null （空值与数字比较结果为空值） null | 字符串 = 字符串number类型与以下类型具有同等的值域： DEC Decimal Double PREcision Float Integer Int Numeric Real Smallint提示：虽然Oracle可以使用上面的子数据类型，但建议还是采用NUMBER(n,m)为好。因为如果使用子数据类型定义字段类型不当，可能引起数据在运算方面的问题。Long数据类型的限制：select中可以用long；update中可以用select子句；insert中可以用Valus子句；每个表只能允许一个long列；long列不能列出完整性约束（null、not null除外）；long列不能被索引；过程或存储函数不能接收long型函数；存储函数不能返回long型值。long 目前不能出现在以下情况中：select中的Where，Group by，order by，Connect by，distinct；不能对long列作substr，instr；表达式或条件；子查询或集合中不能用long；Create table .as select 中不能用long；1.8 SQL 语句基础下面给出SQL语句的基本介绍，更详细的描述见后面的章节。1.8.1 SQL语句所用符号 操作符用途例子+ - 表示正数或负数，正数可省去 +-1234.56+将两个数或表达式进行相加A=c+b-将两个数或表达式进行相减34-12*将两个数或表达式进行相乘12*34/除以一个数或表达式18*11NULL空值判断Where name is null;|字符串连接101-|tel_num=等于测试Select * from emp where name=赵元杰;!= 或或=不等于测试Select * from emp where name !=赵元杰;小于测试Select * from emp Where sal 大于测试Select * from emp Where sal 5000;=小于等于测试Select * from emp Where sal =大于等于测试Select * from emp Where sal = 5000;Not in测试某值是否在一个指定的结果集中Select name,addr from expertwhere local not in(北京,上海);ANY将一个值与一组值进行比较，返回满足条件的结果。必须跟!=,=select ename,sal from emp where sal= any(select sal from emp where deptno=10)SOME同ANY，必须跟!=,=ALL将一个值与一组值比较，返回满足条件的所有列值。必须跟!=,=Select name,sal from empWhere sal= all ( 500,800,1200);Not betweenA and B判断某个值是否界于两者之间。Select name,sal from emp Where sal between 500 and 1200;notexists判断某个列是否存在于一组值中。select dname,deptno from dept where exists(select * from emp where dept.deptno=emp.deptno)Anotlike bEscape char比较两个模式是否相似，当使用like 语句时Oracle不去访问索引。Select * from empWhere ename like TH%;Is not null 测试值是否为空。Select ename,deptno from empWhere comm. Is null or comm.=0;Not 对结果的否定。Select * from empWhere sal not(sal=1000;AND用于判断两个条件十分都满足。Select * from emp where Ename=SIMTH and sal=1000;OR 用于判断两个条件中是否有一个满足。Select * from emp where Ename=SIMTH or ename=SCOTT;UNION用于返回（组合）两个查询中所有唯一的行。Select ename from emp unionSelect ename from emp;UNION ALL用于返回（组合）两个查询中所有所有的行。INTERSECT用于返回两个查询中相同的行。Select ename from emp1 intersect select ename from emp2;MINUS用于返回两个查询中的不同的行。1.8.2 简单select 查询 当我们可以用SQL*PLUS登录到SQL下后，我们可以用DESC 显示某表的结构，也可以用select 语句简单查询表中的一些列的内容。例：要查询EMP表中员工的姓名、工资及出生日期，则：SQLselect ename, sal, hiredate from emp;第二章 查询基础下面给出查询的一些基本的操作描述，如果你是位Oracle老手则可以跳这一章。2.1 SELECT语句在关系数据库中，使用频率最高要算SELECT语句了。尽管SELECT 语句的使用非常简单，但确很有学问。下面简单介绍有关SELECT 语句的常用方法。1.命令语法： SELECT 的简单语法：SELECT DISTINCT | ALL * | column1, column2.FROM table_1 | (subquery) alias, table_2 | (subquery) alias.WHERE conditionCONNECT BY condition START WITH conditionGROUP BY expn HAVING expn UNION ALL | INTERSECT | MINUS SELECT . . . ORDER BY expn ASC | DESC FOR UPDATE OF user.table | view column NOWAIT详细语法结构需查阅最新ORACLE 原版ORACLE8i SQL REFERENCE MANUAL或ORACLE9i SQL REFERENCE MANUAL2.2 SQL中的单记录函数许多资料（包括Oracle 的资料）把Oracle的SQL语句中用到的函数分为单值函数和多值函数，单值函数又分为字符函数和数字函数。下面分别介绍它们。2.2.1 单记录字符函数函 数 说 明 ASCII返回对应字符的十进制值 CHR给出十进制返回字符 CONCAT拼接两个字符串，与 | 相同 INITCAT将字符串的第一个字母变为大写 INSTR找出某个字符串的位置 INSTRB找出某个字符串的位置和字节数 LENGTH以字符给出字符串的长度 LENGTHB以字节给出字符串的长度 LOWER将字符串转换成小写 LPAD使用指定的字符在字符的左边填充 LTRIM在左边裁剪掉指定的字符 RPAD使用指定的字符在字符的右边填充 RTRIM在右边裁剪掉指定的字符 REPLACE执行字符串搜索和替换 SUBSTR取字符串的子串 SUBSTRB取字符串的子串（以字节） SOUNDEX返回一个同音字符串 TRANSLATE执行字符串搜索和替换 TRIM裁剪掉前面或后面的字符串 UPPER将字符串变为大写 NVL以一个值来替换空值ASCII()是字符串。返回与指定的字符对应的十进制数。SQL select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual; A a ZERO SPACE- - - -65 97 48 32SQL select ascii(赵) zhao,length(赵) leng from dual; ZHAO LENG- - 54740 1CHR(NCHAR)给出整数，返回对应字符。如：SQL select chr(54740) zhao,chr(65) chr65 from dual;ZH C- -赵 ACONCAT(,)SQL select concat(010-,8801 8159)|转23 赵元杰电话 from dual;赵元杰电话-010-8801 8159转23INITCAP()返回字符串c1 并第一个字母变为大写。例如：SQL select initcap(simth) upp from dual;UPP-SimthINSTR(, )在一个字符串中搜索指定的字符，返回发现指定的字符的位置。C1: 被搜索的字符串C2: 希望搜索的字符串I: 搜索的开始位置，缺省是1J: 出现的位置，缺省是1。SQL SELECT INSTR (Oracle Training, ra, 1, 2) Instring FROM DUAL; Instring- 9INSTRB(, )除了返回的字节外 ，与INSTR 相同，LENGTH( )返回字符串 c 的长度。SQL l 1 select name,length(name),addr,length(addr),sal,length(to_char(sal) 2* from nchar_tstSQL /NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)- - - - - - 赵元杰 3 北京市海淀区 6 99999.99 8LENGTHB( )以字节返回字符串的字节数。SQL select name,lengthb(name),length(name) from nchar_tst;NAME LENGTHB(NAME) LENGTH(NAME)- - -赵元杰 6 3LOWER ( )返回字符串并将所有字符变为小写。SQL select lower(AaBbCcDd) AaBbCcDd from dual;AABBCCDD-aabbccddUPPER( )与 LOWER 相反，将给出字符串变为大写。如：SQL select upper(AaBbCcDd) AaBbCcDd from dual;AABBCCDD-AABBCCDDRPAD和LPAD(粘贴字符)RPAD(string，Length，set)LPAD(string，Length，set) RPAD在列的右边粘贴字符； LPAD在列的左边粘贴字符。例1：SQLselect RPAD(City，35，.)，temperature from weather; RPAD(City，35，.) temperature - - CLEVELAND. 85 LOS ANGELES. 81 . （即不够35个字符用.填满）LTRIM(左截断)RTRIM（右截断） 函数LTRIM (string ,set)Left TRIM (左截断)删去左边出现的任何set 字符。RTRIM (string ,set)Right TRIM (右截断)删去右边出现的任何set 字符。例1：SELECT RTRIM (Mother Theresa, The, The) “Example of RightTrimming” FROM DUAL;Example of Right-Mother Theresa,SUBSTR Substr(string，start，Count)取子字符串中函数对字串（或字段），从start字符 开始，连续取 count 个字符并返回结果，如果没有指count则一直取到尾。select phone,substr(phone,1,3) | 0 | substr(phone,4)from telecommunication where master=中国电信;SUBSTRB(string，start，Count)对字串（或字段），从start字节 开始，连续取 count 个字节并返回结果，如果没有指count则一直取到尾。REPLACE (string ,string_in,string_out)String: 希望被替换的字符串或变量。String_in: 被替换字符串。String_out: 要替换字符串。SQL select replace(Informaix中国公司,Informaix,IBM Informix) 2 IBM数据库 from dual;IBM数据库-IBM Informix中国公司SOUNDEX( )返回一个与给定的字符串读音相同的字符串（不管拼写是否一样）。SELECT DPL_NAME FROM DENIED_PARTIES_LIST WHERESOUNDEX(DPL_NAME) = SOUNDEX(Saddam Hussain) ;DPL_NAME-Al HusseniSadda Al Sada.REPLACE (string ,string_in,string_out)String:希望被替换的字符串或变量。String_in: 被替换字符串。String_out: 要替换字符串。SELECT REPLACE (Oracle, Or, Mir) “Example “ FROM DUAL;Example-MiracleTRIM（ FROM ）TRIM可以使你对给定的字符串进行裁剪（前面，后面或前后）。如果指定 LEADING, Oracle 从trim_char 中裁剪掉前面的字符；如果指定TRAILING, Oracle 从trim_char 中裁剪掉尾面的字符；如果指定两个都指定或一个都没有给出，Oracle从trim_char 中裁剪掉前面及尾面的字符；如果不指定 trim_character, 缺省为空格符；如果只指定trim_source, Oracle Oracle从trim_char 中裁剪掉前面及尾面的字符。例子：将下面字符串中的前面和后面的0字符都去掉：SELECT TRIM (0 FROM 0009872348900) TRIM Example FROM DUAL;TRIM example-987234892.2.2 单记录数字函数函数说明Value1 + value2Value1 - value2Value1 * value2Value1 / value2ABS(value)CEIL(value)COS(value)COSH(value)EXP(value)FLOOR(value)LN(value)LOG(value)MOD(valu