Oracle 11g OCP051培训笔记文档格式.docx
《Oracle 11g OCP051培训笔记文档格式.docx》由会员分享，可在线阅读，更多相关《Oracle 11g OCP051培训笔记文档格式.docx（143页珍藏版）》请在冰豆网上搜索。
同义词
第十五章：
序列
第十六章：
外部表
第三部分：
SQL语言的扩展
第十七章：
INSERT语句总结
第十八章：
DML和DDL语句的其他用法
第十九章通过GROUPBY产生统计报告
第二十章：
ORACLE分层查询
第二十一章：
Oracle的Timezone
第二十二章：
正则表达式
第一章、Oracle命令类别：
数据操作语言：
DML:
select;
insert;
delete;
update;
merge.
数据定义语言：
DDL:
create;
alter;
drop;
truncate;
rename;
comment.
事务控制语言：
TCL:
commit;
rollback;
savepoint.
数据控制语言：
DCL:
grant;
revoke.
第二章、SQL的基本函数
2.1单行函数与多行函数
单行函数：
指一行数据输入，返回一个值的函数。
所以查询一个表时，对选择的每一行数据
都返回一个结果。
大写转小写
SQL>
selectempno,lower（ename）fromemp;
多行函数：
指多行数据输入，返回一个值的函数。
所以对表的群组进行操作，并且每组返回
一个结果。
（典型的是聚合函数）
selectsum（sal）fromemp;
2.2单行函数的几种类型
2.2.1字符函数
lower（'
SQLCourse'
）----->
sqlcourse返回小写
upper（'
sqlcourse'
SQLCOURSE返回大写
initcap（'
SQLcourse'
SqlCourse返回首字母大写
concat（'
good'
'
string'
）---->
goodstring拼接//只能拼接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（'
JACKandJUE'
J'
BL'
BLACKandBLUE
trim（'
m'
from'
mmtimranm'
timran
2.2.2数值函数
round对指定的值做四舍五入,round（p,s）s为正数时，表示小数点后要保留的位数，s也可以
为负数，但意义不大。
round:
按指定精度对十进制数四舍五入,如:
round（45.923,1）,结果,45.9
round（45.923,0）,结果,46
round（45.923,-1）,结果,50
trunc对指定的值取整trunc（p,s）
trunc:
按指定精度截断十进制数,如:
trunc（45.923,1）,结果,45.9
trunc（45.923）,结果,45
trunc（45.923,-1）,结果,40
mod返回除法后的余数
selectmod（100,12）fromdual;
MOD（100,12）
-----------
4
2.2.3日期函数
因为日期在oracle里是以数字形式存储的，所以可对它进行加减运算，计算是以天为单位。
缺省格式：
DD-MON-RR.
可以表示日期范围：
（公元前）4712至（公元）9999
时间格式
selectto_date（'
2003-11-0400:
00:
00'
'
YYYY-MM-DDHH24:
MI:
SS'
）FROMdual;
selectsysdate+2fromdual;
//当前时间+2day
selectsysdate+2/24fromdual;
//当前时间+2hour
select（sysdate-hiredate）/7weekfromemp;
//两个date类型差，结果是以天为整数位
的实数。
MONTHS_BETWEEN//计算两个日期之间的月数
selectmonths_between（'
1994-04-01'
1992-04-01'
）mmfromdual;
查找emp表中参加工作时间>
30年的员工
select*fromempwheremonths_between（sysdate,hiredate）/12>
30;
考点：
很容易认为单行函数返回的数据类型与函数类型一致，对于数值函数类型而言的确如
此，但字符和日期函数可以返回任何数据类型的值。
比如instr函数是字符型的，
months_between函数是日期型的，但它们返回的都是数值。
ADD_MONTHS//给日期增加月份
selectadd_months（'
1992-03-01'
4）amfromdual;
LAST_DAY//日期当前月份的最后一天
selectlast_day（'
1989-03-28'
）l_dfromdual;
NEXT_DAY//NEXT_DAY的第2个参数可以是数字1-7，分别表示周日--周六（考点），比如
要取下一个星期六，则应该是：
selectnext_day（sysdate,7）FROMDUAL;
ROUND（p,s），TRUNC（p,s）在日期中的应用，如何舍入要看具体情况，s是MONTH按30天计，
应该是15舍16入，s是YEAR则按6舍7入计算。
SELECTempno,hiredate,
round（hiredate,'
MONTH'
）ASround,
trunc（hiredate,'
）AStrunc
FROMemp
WHEREempno=7788;
YEAR'
WHEREempno=7839;
2.2.4几个有用的函数
1）decode函数和case表达式：
实现sql语句中的条件判断语句，具有类似高级语言中的if语句的功能。
decode函数源自oracle,case表达式源自sql标准，实现功能类似，decode语法更简单些。
decode函数用法：
SELECTjob,sal,
DECODE（job,'
ANALYST'
SAL*1.1,
'
CLERK'
SAL*1.15,
MANAGER'
SAL*1.20,
SAL）
REVISED_SALARY
/
case表达式第一种用法：
selectjob,sal,casejob
when'
thenSAL*1.1
thenSAL*1.15
thenSAL*1.20
elsesalend
fromemp
case表达式第二种用法：
selectjob,sal,
casewhenjob='
whenjob='
以上三种写法结果都是一样的：
JOBSALREVISED_SALARY
---------------------------------
CLERK800920
SALESMAN16001600
SALESMAN12501250
MANAGER29753570
MANAGER28503420
MANAGER24502940
ANALYST30003300
PRESIDENT50005000
SALESMAN15001500
CLERK11001265
CLERK9501092.5
CLERK13001495
case第二种语法比第一种语法增加了搜索功能。
形式上第一种when后跟定值，而第二种还
可以使用比较符。
看一个例子
selectename,sal,
casewhensal>
=3000then'
高级'
whensal>
=2000then'
中级'
else'
低级'
end
级别
ENAMESAL级别
------------------------
SMITH800低级
ALLEN1600低级
WARD1250低级
JONES2975中级
MARTIN1250低级
BLAKE2850中级
CLARK2450中级
SCOTT3000高级
KING5000高级
TURNER1500低级
ADAMS1100低级
JAMES950低级
FORD3000高级
MILLER1300低级
2）DISTINCT（去重）限定词的用法：
//distinct貌似多行函数，严格来说它不是函数。
selectdistinctjobfromemp;
//消除表行重复值。
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
selectdistinctjob,deptnofromemp;
//重复值是后面的字段组合起来考虑的
JOBDEPTNO
-------------------
MANAGER20
PRESIDENT10
CLERK10
SALESMAN30
ANALYST20
MANAGER30
MANAGER10
CLERK30
CLERK20
3）CHR（）函数和ASCII（）函数
chr（）函数将ASCII码转换为字符：
ASCII码–》字符
ascii（）函数将字符转换为ASCII码：
字符–》ASCII码
在oracle中chr（）函数和ascii（）是一对反函数。
求字符对应的ASCII值
selectASCII（'
A'
ASCII（'
）
----------
65
selectchr（65）fromdual;
C
-
A
4）sys_context获取环境上下文的函数（很有用）
scott远程登录
selectSYS_CONTEXT（'
USERENV'
IP_ADDRESS'
）fromdual;
--------------------------------------------------------------------------------
192.168.0.136
selectsys_context（'
userenv'
sid'
SYS_CONTEXT（'
SID'
129
terminal'
TERMINAL'
TIMRAN-222C75E5
5）处理空值的几种函数（见第四章）
第三章、SQL的数据类型
3.1四种基本的常用数据类型
1、字符型，2、数值型，3、日期型，4、大对象型
3.1.1字符型：
char固定字符，最长2000个
varchar2可变长字符，最长4000个,最小值是1
nchar/nvarchar2NCHAR/NVARCHAR2类型的列使用国家字符集
raw和longraw固定/可变长度的二进制数据长度最2G,可存放多媒体图象声音
等。
（老类型，逐步淘汰）
LONG可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，一个表
中最多一个LONG列。
（老类型，逐步淘汰）。
3.1.2数值型：
number（p,s）实数类型，以可变长度的内部格式来存储数字。
这个内部格式精度可以
高达38位。
int整数型，number的子类型，范围同上
3.1.3日期型：
date日期的普通形式，表示精度只能到秒级。
timestamp日期的扩展形式，表示精度可达秒后小数点9位（10亿分之1秒）。
timestampwithtimezone带时区
timestampwithlocaltimezone时区转换成本地日期
系统安装后，默认日期格式是DD-MON-RR,RR和YY都是表示两位年份，但RR是有世纪认
知的，它将指定日期的年份和当前年份比较后确定年份是上个世纪还是本世纪（如表）。
当前年份指定日期RR格式YY格式
-------------------------------------------------------
199527-OCT-9519951995
199527-OCT-1720171917
200127-OCT-1720172017
201327-OCT-9519952095
3.1.4LOB型：
大对象是10g引入的，在11g中又重新定义，在一个表的字段里存储大容量
数据，所有大对象最大都可能达到4G
CLOB:
用来存储单字节的字符数据，包含在数据库内。
NCLOB:
用来存储多字节的字符数据
BLOB：
用于存储二进制数据，包含在数据库内。
BFILE:
存储在数据库之外的二进制文件中，这个文件中的数据只能被只读访问。
CLOB，NCLOB，BLOB都是内部的LOB类型，没有LONG只能有一列的限制（考点）。
保存图片或电影使用BLOB最好、如果是小说则使用CLOB最好。
虽然LONGRAW也可以使用，但LONG是oracle将要废弃的类型，因此建议用LOB。
当然说将要废弃，但还没有完全废弃，比如oracle11g里的重要视图dba_views，对于text（视
图定义）仍然沿用了LONG类型。
Oracle11g重新设计了大对象，推出SecureFileLobs的概念，相关的参数是db_securefile，采
用SecureFileLobs的前提条件是11g以上版本，ASSM管理等，符合这些条件的BasicFileLobs
也可以转换成SecureFileLobs。
较之过去的BasicFileLobs,SecureFileLobs有几项改进：
1）压缩，2）去重，3）加密。
当createtable定义LOB列时往往用到LOB_storage_clause。
之后对LOB的操作通常使用Oracle
提供的DBMS_LOB包，通过编写PL/SQL块完成LOB数据的管理。
3.2数据类型的转换
隐性类型转换和显性类型转换。
3.2.1隐性类型转换：
是指oracle自动完成的类型转换。
在一些带有明显意图的字面值上，可以由Oracle自主判
断进行数据类型的转换。
如：
selectempno,enamefromempwhereempno='
7788'
;
//empno本来是数值类型的，这
里字符'
隐性转换成数值7788
EMPNOENAME
--------------------
7788SCOTT
selectlength（sysdate）fromdual;
//将data型隐转成字符型后计算长度
LENGTH（SYSDATE）
---------------
19
SELECT'
12.5'
+11FROMdual;
//将字符型‘12.5’隐转成数字型再求和
+11
23.5
SELECT10+（'
||11）FROMdual;
//将数字型11隐转成字符与‘12.5’合并，
其结果再隐转数字型与10求和
10+（'
||11）
22.511
3.2.2显性类型转换是强制完成类型转换（推荐），转换函数形式有三种：
TO_CHAR
TO_DATE
TO_NUMBER
1）日期-->
字符
selectename,hiredate,to_char（hiredate,'
DD-MON-YY'
）month_hiredfromempwhere
ename='
SCOTT'
ENAMEHIREDATEMONTH_HIRED
-------------------------------------------
SCOTT1987-04-1900:
0019-4月-87
fm压缩空格或左边的'
0'
fmyyyy-mm-dd'
-----------------------------------------
001987-4-19
//其实DD-MM-YY是比较糟糕的一种格式，因为当日期中天数小于12时，DD-MM-YY和
MM-DD-YY容易造成混乱。
2）数字-->
字符：
9表示数字，L本地化货币字符
selectename,to_char（sal,'
L99,999.99'
）Salaryfromempwhereename='
ENAMESALARY
------------------------------
SCOTT￥3,000.00
3）字符-->
日期
selectto_date（'
1983-11-12'
'
YYYY-MM-DD'
）tmp_DATEfromdual;
TMP_DATE
-------------------
1983-11-1200:
00
4）字符-->
数字：
SELECTto_number（'
$123.45'
$9999.99'
）resultFROMdual;
RESULT
123.45
使用to_number时如果使用较短的格式掩码转换数字，就会返回错误。
不要混淆
to_number和to_char转换。
例如：
selectto_number（'
123.56'
999.9'
selectto_number（123.56,'
）fromdual
*
第1行出现错误:
ORA-01722:
无效数字
selectto_char（123.56,'
TO_CHA
------
123.6
4.1运算符及优先级：
算数运算符
*，/，+，-，
逻辑运算符
not,and,or
比较运算符
单行比较运算=,>
>
=,<
<
=,<
>
多行比较运算>
any,>
all,<
any,<
all,in，notin
模糊比较like（配合“%”和“_”）
特殊比较isnull
（）优先级最高
selectename,job,sal,commfromempwherejob='
SALESMAN'
ORjob='
PRESIDENT'
ANDsal>
1500;
条件子句使用比较运算符比较两个选项，重要的是要理解这两个选项的数据类型。
4.2用BETWEENAND操作符来查询出在某一范围内的行.
SELECTename,salFROMempWHEREsalBETWEEN1000AND1500;
//between低值and高值，包括低值和高值。
4.3模糊查询及其通配符：
在where字句中使用like谓词，常使用特殊符号"
%"
或"
_
升级会员 