Oracle 11g OCP051培训笔记文档格式.docx

1、同义词第十五章：序列第十六章：外部表第三部分：SQL 语言的扩展第十七章：INSERT 语句总结 第十八章：DML 和 DDL 语句的其他用法第十九章通过 GROUP BY 产生统计报告第二十章：ORACLE 分层查询第二十一章：Oracle 的 Timezone第二十二章：正则表达式第一章、Oracle 命令类别：数据操作语言：DML: select; insert; delete; update; merge.数据定义语言：DDL: create; alter; drop; truncate; rename; comment.事务控制语言：TCL: commit; rollback; sa

2、vepoint.数据控制语言：DCL: grant; revoke.第二章 、SQL 的基本函数2.1 单行函数与多行函数单行函数：指一行数据输入，返回一个值的函数。所以查询一个表时，对选择的每一行数据都返回一个结果。大写转小写SQLselect empno,lower（ename） from emp;多行函数：指多行数据输入，返回一个值的函数。所以对表的群组进行操作，并且每组返回一个结果。（典型的是聚合函数）select sum（sal） from emp;2.2 单行函数的几种类型2.2.1 字符函数lower（SQL Course）-sql course 返回小写upper（sql co

3、urseSQL COURSE 返回大写initcap（SQL course Sql Course 返回首字母大写concat（good,string）-good string 拼接 /只能拼接 2 个字符串substr（String,1,3）-Str 从第 1 位开始截取 3 位数instr（t#i#m#r#a#n#,3） -从第 3 位起始找#字符在那个绝对位置length（6 长度lpad（first,10,$）左填充rpad（676768,10,*）右填充replace（JACK and JUEJBLBLACK and BLUEtrim（m from mmtimranmtimran2.2

4、.2 数值函数round 对指定的值做四舍五入,round（p,s） s 为正数时，表示小数点后要保留的位数，s 也可以为负数，但意义不大。round:按指定精度对十进制数四舍五入,如:round（45.923, 1）,结果,45.9round（45.923, 0）,结果,46round（45.923, -1）,结果,50trunc 对指定的值取整 trunc（p,s）trunc:按指定精度截断十进制数,如:trunc（45.923, 1）,结果,45.9trunc（45.923）,结果,45trunc（45.923, -1）,结果, 40mod 返回除法后的余数 select mod（100

5、,12） from dual;MOD（100,12）-42.2.3 日期函数因为日期在 oracle 里是以数字形式存储的，所以可对它进行加减运算，计算是以天为单位。缺省格式：DD-MON-RR.可以表示日期范围：（公元前）4712 至（公元）9999时间格式select to_date（2003-11-04 00:00:00 ,YYYY-MM-DD HH24:MI:SS） FROM dual; select sysdate+2 from dual; /当前时间+2day select sysdate+2/24 from dual; /当前时间+2hour select （sysdate-hi

6、redate）/7 week from emp; /两个 date 类型差，结果是以天为整数位的实数。MONTHS_BETWEEN /计算两个日期之间的月数select months_between（1994-04-011992-04-01） mm from dual;查找 emp 表中参加工作时间30 年的员工select * from emp where months_between（sysdate,hiredate）/1230;考点：很容易认为单行函数返回的数据类型与函数类型一致，对于数值函数类型而言的确如此，但字符和日期函数可以返回任何数据类型的值。比如 instr 函数是字符型的，m

7、onths_between 函数是日期型的，但它们返回的都是数值。ADD_MONTHS /给日期增加月份select add_months（1992-03-01,4） am from dual;LAST_DAY /日期当前月份的最后一天select last_day（1989-03-28） l_d from dual;NEXT_DAY /NEXT_DAY 的第 2 个参数可以是数字 1-7，分别表示周日-周六（考点），比如要取下一个星期六，则应该是：select next_day（sysdate,7） FROM DUAL;ROUND（p,s），TRUNC（p,s）在日期中的应用，如何舍入要看具

8、体情况，s 是 MONTH 按 30 天计，应该是 15 舍 16 入，s 是 YEAR 则按 6 舍 7 入计算。SELECT empno, hiredate,round（hiredate,MONTH） AS round,trunc（hiredate,） AS truncFROM empWHERE empno=7788;YEARWHERE empno=7839;2.2.4 几个有用的函数1）decode 函数和case 表达式：实现 sql 语句中的条件判断语句，具有类似高级语言中的 if 语句的功能。decode 函数源自 oracle, case 表达式源自 sql 标准，实现功能类似，

9、decode 语法更简单些。decode 函数用法：SELECT job, sal,DECODE（job, ANALYST, SAL*1.1,CLERK, SAL*1.15,MANAGER, SAL*1.20,SAL）REVISED_SALARY/case 表达式第一种用法：select job, sal,case jobwhen then SAL*1.1 then SAL*1.15 then SAL*1.20else sal endfrom empcase 表达式第二种用法：select job, sal,case when job=when job=以上三种写法结果都是一样的：JOB SA

10、L REVISED_SALARY- - -CLERK 800 920SALESMAN 1600 1600SALESMAN 1250 1250MANAGER 2975 3570MANAGER 2850 3420MANAGER 2450 2940ANALYST 3000 3300PRESIDENT 5000 5000SALESMAN 1500 1500CLERK 1100 1265CLERK 950 1092.5CLERK 1300 1495case 第二种语法比第一种语法增加了搜索功能。形式上第一种 when 后跟定值，而第二种还可以使用比较符。看一个例子select ename,sal,cas

11、e when sal=3000 then 高级when sal=2000 then 中级else 低级 end级别ENAME SAL 级别- - -SMITH 800 低级ALLEN 1600 低级WARD 1250 低级JONES 2975 中级MARTIN 1250 低级BLAKE 2850 中级CLARK 2450 中级SCOTT 3000 高级KING 5000 高级TURNER 1500 低级ADAMS 1100 低级JAMES 950 低级FORD 3000 高级MILLER 1300 低级2）DISTINCT（去重）限定词的用法： /distinct 貌似多行函数，严格来说它不是

12、函数。 select distinct job from emp; /消除表行重复值。JOB-CLERKSALESMANPRESIDENTMANAGERANALYST select distinct job,deptno from emp; /重复值是后面的字段组合起来考虑的JOB DEPTNO- -MANAGER 20PRESIDENT 10CLERK 10SALESMAN 30ANALYST 20MANAGER 30MANAGER 10CLERK 30CLERK 203）CHR（）函数和 ASCII（）函数chr（）函数将 ASCII 码转换为字符： ASCII 码字符ascii（）函数将

13、字符转换为 ASCII 码：字符 ASCII 码在 oracle 中 chr（）函数和 ascii（）是一对反函数。求字符对应的 ASCII 值 select ASCII（AASCII（）-65 select chr（65） from dual;C-A4） sys_context 获取环境上下文的函数（很有用）scott 远程登录select SYS_CONTEXT（USERENVIP_ADDRESS） from dual;-192.168.0.136 select sys_context（userenvsidSYS_CONTEXT（SID129terminalTERMINALTIMRAN-2

14、22C75E55）处理空值的几种函数（见第四章）第三章、SQL 的数据类型3.1 四种基本的常用数据类型1、字符型， 2、数值型，3、日期型，4、大对象型3.1.1 字符型：char 固定字符，最长 2000 个varchar2 可变长字符，最长 4000 个,最小值是 1nchar/nvarchar2 NCHAR/NVARCHAR2 类型的列使用国家字符集raw 和 long raw 固定/可变长度的二进制数据长度最 2G,可存放多媒体图象声音等。（老类型，逐步淘汰）LONG 可变长的字符串数据，最长 2G，LONG 具有 VARCHAR2 列的特性，一个表中最多一个 LONG 列。（老类型

15、，逐步淘汰）。3.1.2 数值型：number（p,s） 实数类型，以可变长度的内部格式来存储数字。这个内部格式精度可以高达 38 位。int 整数型，number 的子类型，范围同上3.1.3 日期型：date 日期的普通形式，表示精度只能到秒级。timestamp 日期的扩展形式，表示精度可达秒后小数点 9 位（10 亿分之 1 秒）。timestamp with timezone 带时区timestamp with local timezone 时区转换成本地日期系统安装后，默认日期格式是 DD-MON-RR, RR 和 YY 都是表示两位年份，但 RR 是有世纪认知的，它将指定日期的年

16、份和当前年份比较后确定年份是上个世纪还是本世纪（如表）。当前年份 指定日期 RR 格式 YY 格式-1995 27-OCT-95 1995 19951995 27-OCT-17 2017 19172001 27-OCT-17 2017 20172013 27-OCT-95 1995 20953.1.4 LOB 型：大对象是 10g 引入的，在 11g 中又重新定义，在一个表的字段里存储大容量数据，所有大对象最大都可能达到 4GCLOB: 用来存储单字节的字符数据，包含在数据库内。NCLOB:用来存储多字节的字符数据BLOB：用于存储二进制数据，包含在数据库内。BFILE:存储在数据库之外的二进

17、制文件中，这个文件中的数据只能被只读访问。CLOB，NCLOB，BLOB 都是内部的 LOB 类型，没有 LONG 只能有一列的限制（考点）。保存图片或电影使用 BLOB 最好、如果是小说则使用 CLOB 最好。虽然 LONG RAW 也可以使用，但 LONG 是 oracle 将要废弃的类型，因此建议用 LOB。当然说将要废弃，但还没有完全废弃，比如 oracle 11g 里的重要视图 dba_views，对于 text（视图定义）仍然沿用了 LONG 类型。Oracle 11g 重新设计了大对象，推出 SecureFile Lobs 的概念，相关的参数是 db_securefile，采用

18、SecureFile Lobs 的前提条件是 11g 以上版本，ASSM 管理等，符合这些条件的 BasicFile Lobs也可以转换成 SecureFile Lobs。较之过去的 BasicFile Lobs, SecureFile Lobs 有几项改进：1）压缩，2）去重，3）加密。当create table定义LOB列时往往用到LOB_storage_clause。之后对LOB的操作通常使用Oracle提供的 DBMS_LOB 包，通过编写 PL/SQL 块完成 LOB 数据的管理。3.2 数据类型的转换隐性类型转换和显性类型转换。3.2.1 隐性类型转换：是指 oracle 自动完成

19、的类型转换。在一些带有明显意图的字面值上，可以由 Oracle 自主判断进行数据类型的转换。如： select empno,ename from emp where empno=7788; /empno 本来是数值类型的，这里字符隐性转换成数值 7788EMPNO ENAME- -7788 SCOTT select length（sysdate） from dual; /将 data 型隐转成字符型后计算长度LENGTH（SYSDATE）-19 SELECT 12.5+11 FROM dual; / 将字符型12.5隐转成数字型再求和+1123.5 SELECT 10+（|11） FROM d

20、ual; /将数字型 11 隐转成字符与12.5合并，其结果再隐转数字型与 10 求和10+（|11）22.5113.2.2 显性类型转换是强制完成类型转换（推荐），转换函数形式有三种：TO_CHARTO_DATETO_NUMBER1）日期-字符 select ename, hiredate, to_char（hiredate, DD-MON-YY） month_hired from emp whereename=SCOTTENAME HIREDATE MONTH_HIRED- - -SCOTT 1987-04-19 00:00 19-4 月 -87fm 压缩空格或左边的0fmyyyy-mm-

21、dd- - -00 1987-4-19/其实 DD-MM-YY 是比较糟糕的一种格式，因为当日期中天数小于 12 时，DD-MM-YY 和MM-DD-YY 容易造成混乱。2）数字-字符：9 表示数字，L 本地化货币字符 select ename, to_char（sal, L99,999.99） Salary from emp where ename=ENAME SALARY- -SCOTT ￥3,000.003）字符-日期 select to_date（1983-11-12, YYYY-MM-DD） tmp_DATE from dual;TMP_DATE-1983-11-12 00:004）

22、字符-数字： SELECT to_number（$123.45$9999.99） result FROM dual;RESULT123.45使用 to_number 时如果使用较短的格式掩码转换数字，就会返回错误。不要混淆to_number 和 to_char 转换。例如： select to_number（123.56999.9select to_number（123.56,） from dual*第 1 行出现错误:ORA-01722: 无效数字 select to_char（123.56,TO_CHA-123.64.1 运算符及优先级：算数运算符*，/，+，-，逻辑运算符not, and ,or比较运算符单行比较运算 =, =,=, 多行比较运算any,all,any,1500;条件子句使用比较运算符比较两个选项，重要的是要理解这两个选项的数据类型。4.2 用 BETWEEN AND 操作符来查询出在某一范围内的行. SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 1500;/between 低值 and 高值，包括低值和高值。4.3 模糊查询及其通配符：在 where 字句中使用 like 谓词，常使用特殊符号%或_