第四章、WHERE子句中数据的比较和运算...15
4.1隐式比较与显式比较：...15
4.2运算符优先级：...15
4.3用BETWEENAND操作符来查询出在某一范围内的行.15
4.4模糊查询及其通配符：...16
4.5用IN操作符来检验一个值是否在一个列表中...16
4.6交互输入变量符&和&&的用途：...17
4.7使用逻辑操作符:AND;OR;NOT.18
第五章分组函数...19
5.1五个分组函数...19
5.2在组函数中使用NVL函数...19
5.3GROUPBY创建数据组...20
5.4分组函数的嵌套...20
第六章、数据限定与排序...21
6.1简单查询语句执行顺序...21
6.2排序(orderby)21
6.3空值（null)22
第七章、复杂查询之一：多表连接技术...26
7.1简单查询的解析方法：...26
7.2多表连接...26
7.3集合运算...32
7.4集合运算中只要类型能匹配上就可以连接...34
7.5关于orderby使用别名排序的问题：...35
第八章、复杂查询(下）：子查询与多列子查询...37
8.1单行子查询采用单行比较运算符（>，<，=，<>)37
8.2在多行子查询中使用all38
8.3在多行子查询中使用any.39
8.4在多行子查询中使用in.39
8.5多列子查询多行多列...40
8.6非成对比较多列子查询...43
8.7notin在子查询中的空值问题：...43
8.8from子句中使用子查询(这个也叫内联视图）...44
8.9关联子查询与非关联子查询...45
8.10简单查询与复杂查询练习题：...47
第九章用户访问控制...50
9.1创建和管理数据库用户账户...50
9.2管理概要文件...51
9.3系统权限，对象权限，角色...53
第十章Oracle的事务和锁...61
10.1什么是事务...61
10.2事务的开始和结束...61
10.3Oracle的事务保存点功能...62
10.4读一致性与锁定...62
10.5加锁模式...62
10.6五种表锁的含义：...63
10.7死锁和解锁...64
第十一章，索引...66
11.1索引结构及特点...66
11.2索引适用那些情况...66
11.3索引的类型与选项：...67
11.4索引扫描方式...67
11.5索引的碎片问题...71
11.6索引不可见（invisible),11g新特性...72
第十二章约束...73
12.1什么是约束...73
12.2约束的语法：...73
12.3五种约束的写法...73
第十三章视图...84
13.1为什么使用视图...84
13.2语法...84
13.3复杂视图的更新，键保留表概念。（不考）...86
第十四章同义词...87
14.1私有同义词;87
14.2公有同义词；大家都可使用,87
14.3关于同义词的几个要点：...87
第十五章序列...88
15.1序列是生成唯一整数值的结构，它的典型用途是用于主键值。...88
15.2几点说明：...88
第十六章外部表...89
第十七章insert语句总结...90
17.1单行insert90
17.2多行insert92
17.3Multitableinsert93
第十八章DML语句-MERGE.98
第十九章几个语句的用法...102
19.1with语句...102
19.2DDL操作及模式对象...103
19.3groupbyrollup，groupbycube,以及grouping的用法...104
第二十章ORACLE分层查询startwith&connectby.109
20.1树结构查询...109
20.2树结构的描述...110
20.3关于PRIOR.111
20.4定义查找起始节点...112
20.5使用LEVEL.114
20.6节点和分支的裁剪...115
20.7排序显示...117
21.1Timezone引入的背景...118
21.2模拟北京、东京、伦敦三地的时区，进一步理解Timezone。...119
21.4关于numtoyminterval函数和numtodsinterval函数...122
第二十二章正则表达式...123
22.1ORACLE中的支持正则表达式的函数主要有下面四个：...123
22.2POSIX正则表达式由标准的元字符（metacharacters）所构成：...123
22.3字符簇：...124
22.4OracleREGEXP_LIKE介绍和例子...124
数据操纵语言：DML:select;insert;delete;update.对表、试图操作查询、插入、删除、更新。
数据定义语言：DDL:create;alter;drop;truncate,rename.对数据库操作创建表、修改表、删除表、删除表内容保留表结构。
Delete与truncate的区别。
事务控制语言：TCL:commit;savepoint;rollback.保存断点
数据控制语言：DCL:grant;revoke.赋予权限、删除权限。
是指函数一次能处理表行的行数，函数总是接受零个或多个输入参数，但总是返回一个预定数据类型的结果。
单行函数：查询对每一行数据都返回一个结果。
SQL>selectempno,lower(ename)fromemp;
多行函数：对多数据行的群组进行操作，并且每组返回一个结果。（典型的是聚合函数）
SQL>selectsum(sal)fromemp;
lower('SQLCourse')----->sqlcourse返回小写
upper('sqlcourse')----->SQLCOURSE返回大写
initcap('SQLCourse')----->SqlCourse返回首字母大写
concat('good','string')---->goodstring拼接//只能拼接2个字符串多个字符嵌套有没有限制？
substr('String',1,3)---->str从第1位开始截取3位数
SQL>selectsubstr('qsedr456',3,5)fromdual;
SUBST
-----
edr45
instr('t#i#m#r#a#n#','#',3)--->从第3位起始找第一次出现#字符在那个绝对位置
SQL>selectinstr('123#45#2222','#',1)fromdual;
INSTR('123#45#2222','#',1)
--------------------------
4
length('String')---->6长度
lpad('first',10,'$')左填充——>$$$$$first
rpad(676768,10,'*')右填充——>676768****
round对指定的值做四舍五入,round(p,s)s为正数时，表示小数点后要保留的位数，s也可以为负数，意义不大。不写位数默认为1
round:按指定精度对十进制数四舍五入,如:round(45.923,1),结果,45.9
round(45.923,0),结果,46
round(45.923,-1),结果,50保留小数点左边的一位。
SQL>selectround(45.96,1)fromdual;
ROUND(45.96,1)
--------------
46
trunc对指定的值取整trunc(p,s)
trunc:按指定精度截断十进制数,如:trunc(45.923,1),结果,45.9
trunc(45.923),结果,45不写位数默认为0
trunc(45.923,-1),结果,40
mod返回除法后的余数
SQL>selectmod(100,12)fromdual;
MOD(100,12)
-----------
因为日期在oracle里是以数字形式存储的，所以可对它进行加减运算，计算是以天为单位。
缺省格式：DD-MON-YY.
可以表示（公元前）4712至（公元）9999
还有一种表示日期的格式DD-MON-RR02-03-13即191303.02。02-03-51即205103.02
SQL>selectto_date('2003-11-0400:00:00','YYYY-MM-DDHH24:Mi:ss')FROMdual;
MONTHS_BETWEEN//计算两个日期之间的月数
SQL>selectmonths_between('1994-04-01','1992-04-01')mmfromdual;
考点：很容易认为单行函数返回的数据类型与函数类型一致，那是对于数字函数类型而言是这样，但字符和日期函数可以返回任何数据类型的值。比如
instr是函数是字符型的，months_between函数是日期型的，但它们返回的都是数值，是一个数,不是字符或日期。
ADD_MONTHS//给日期增加月份
SQL>selectadd_months('1992-03-01',4)amfromdual;
LAST_DAY//日期当前月份的最后一天
SQL>selectlast_day('1989-03-28')l_dfromdual;
ROUND(p,s)，TRUNC(p,s)在日期中的应用，如何舍入要看具体情况，s是MONTH按30天计，应该是14舍15入，s是YEAR则按6舍7入计算。S是year时候，如果月份是7月，则四舍五入比如，日期是1981-07-02round之后结果是1982-01-01
SQL>selectemployee_id,hire_date,round(hire_date,'year')asroundfromemployeeswhereemployee_id=194;
EMPLOYEE_IDHIRE_DATEROUND
-------------------------------------------------
1941998-07-0100:00:001999-01-0100:00:00
SQL>
SELECTempno,hiredate,
round(hiredate,'MONTH')ASround,
trunc(hiredate,'MONTH')AStrunc
FROMemp
WHEREempno=7788;
round(hiredate,'YEAR')ASround,
trunc(hiredate,'YEAR')AStrunc
WHEREempno=7839;
1）case函数与decode函数：
实现sql语句中的条件判断语句，具有类似高级语言中的if语句的功能。
case函数源自sql标准，decode函数源自oracle,实现功能类似，decode语法更简单些。
case函数第一种用法：
selectjob,sal,casejob
when'ANALYST'thenSAL*1.1
when'CLERK'thenSAL*1.15
when'MANAGER'thenSAL*1.20
elsesalend
REVISED_SALARY
fromemp
/
case函数第二种用法：
selectjob,sal,
casewhenjob='ANALYST'thenSAL*1.1
whenjob='CLERK'thenSAL*1.15
whenjob='MANAGER'thenSAL*1.20
2）decode函数用法：
SELECTjob,sal,
DECODE(job,'ANALYST',SAL*1.1,
'CLERK',SAL*1.15,
'MANAGER',SAL*1.20,
SAL)
以上三种写法结果都是一样的：
JOBSALREVISED_SALARY
---------------------------------
CLERK800920
SALESMAN16001600
SALESMAN12501250
MANAGER29753570
MANAGER28503420
MANAGER24502940
ANALYST30003300
PRESIDENT50005000
SALESMAN15001500
CLERK11001265
CLERK9501092.5
CLERK13001495
已选择14行。
3）DISTINCT(去重)的用法：
SQL>selectdistinctjobfromemp;//消除表行重复值。
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
SQL>selectdistinctjob,deptnofromemp;//重复值是后面的字段组合起来考虑的
JOBDEPTNO
-------------------
MANAGER20
PRESIDENT10
CLERK10
SALESMAN30
ANALYST20
MANAGER30
MANAGER10
CLERK30
CLERK20
4）CHR()函数和ASCII()函数
chr()函数将ASCII码转换为字符：ASCII码–》字符
ascii()函数将字符转换为ASCII码：字符–》ASCII码
在oracle中chr()函数和ascii()是一对反函数。
求字符对应的ASCII值
SQL>selectASCII('A')FROMdual;
ASCII('A')
----------
65
SQL>selectchr(65)fromdual;
C
-
A
5）处理空值的几种函数（见第六章）
1、字符型，2、数值型，3、日期型，4、大对象型
char固定字符，最长2000个空间上固定长度不管字符多少。查询快。长度可扩展不可以缩短。
varchar2可变长字符，最长4000个,最小值是1
nchar/nvarchar2NCHAR/NVARCHAR2类型的列使用国家字符集
raw和longraw固定/可变长度的二进制数据长度最2G,可存放多媒体图象声音等。（老类型，逐步淘汰）
LONG可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，一个表中最多一个LONG列。（老类型，逐步淘汰）。
SQL>createtableb(xchar(10),yvarchar2(10));
表已创建。
SQL>altertablebmodifyxvarchar2(8);
表已更改。
SQL>insertintobvalues('a','abc');
已创建1行。
SQL>altertablebmodifyxvarchar2(2);
SQL>altertablebmodifyxchar(8);
SQL>insertintobvalues('ab','abcde');
SQL>altertablebmodifyxchar(6);
altertablebmodifyxchar(6)
*
第1行出现错误:
ORA-01441:无法减小列长度,因为一些值过大
SQL>altertablebmodifyyvarchars(2);
altertablebmodifyyvarchars(2)
ORA-01735:无效的ALTERTABLE选项
SQL>altertablebmodifyyvarchar2(2);
altertablebmodifyyvarchar2(2)
number（p,s)实数类型，以可变长度的内部格式来存储数字。这个内部格式精度可以高达38位。最大可支持到38个9。
int整数型，number的子类型，范围同上最大可支持到38个9。
扩展知识：
双精度、浮点数值类型。
date日期的普通形式，表示精度只能到秒级。
timestamp日期的扩展形式，表示精度可达秒后小数点9位（10亿分之1秒）。
timestampwithtimezone带时区
timestampwithlocaltimezone时区转换后的本地日期
大对象是10g引入的，在11g中又重新定义，在一个表的字段里存储大容量数据，最长可能达到4G
CLOB:用来存储单字节的字符数据，包含在数据库内。
NCLOB:用来存储多字节的字符数据国家字符集
BLOB：用于存储二进制数据，包含在数据库内。涵盖了CLOB，反之不一定成立。
BFILE:存储在数据库之外的二进制文件中，这个文件中的数据只能被只读访问。但该文件不被修改。
不能用insert语句插入大对象数据。
CLOB，NCLOB，BLOB都是内部的LOB类型，没有LONG只能有一列的限制
如要保存图片、文本文件、Word文件各自最好用哪种数据类型的呢？
BLOB最好，LONGRAW也不错，但LONG是oracle将要废弃的类型，因此建议用LOB。
当然说将要废弃，但还没有完全废弃，比如oracle11g里的重要视图dba_views，对于text(视图定义）仍然沿用了LONG类型。
隐性类型转换和显性类型转换。
是oracle自动完成类型转换，即在一些明显意图的表达式上oracle可以自主完成数据类型的转换。如：
SQL>SELECT'12.5'+11FROMdual;
'12.5'+11
23.5
SQL>SELECT10+('12.5'||11)FROMdual;
10+('12.5'||11)
---------------
22.511
SQL>SELECT10+('12.5'||'11')FROMdual;
10+('12.5'||'11')
-----------------
是强制完成类型转换（推荐），转换函数形式有三种：
TO_CHAR
TO_DATE
TO_NUMBER
1）日期-->字符
SQL>selectename,hiredate,to_char(hiredate,'DD-MON-YY')month_hiredfromempwhereename='SCOTT';
ENAMEHIREDATEMONTH_HIRED
-------------------------------------------
SCOTT1987-04-1900:00:0019-4月-87
fm压缩空格或左边的'0'
SQL>selectename,hiredate,to_char(hiredate,'fmyyyy-mm-dd')month_hiredfromempwhereename='SCOTT';
ENAMEHIREDATEMONTH_HIRE
---------------------------------------
SCOTT1987-04-1900:00:001987-4-19
2）字符-->日期
SQL>selectto_date('1983-11-12','YYYY-MM-DD')tmp_DATEfromdual;
TMP_DATE
1983-11-1200:00:00
3）数字-->字符：9表示数字，$本地化货币字符
SQL>selectename,to_char(sal,'$99,999.99')Salaryfromempwhereename='SCOTT';
ENAMESALARY
---------------------
SCOTT$3,000.00
4）字符-->数字：
SQL>SELECTto_number('$123.45','$999.99')resultFROMdual;
RESULT
123.45
考点：使用to_number时如果使用较短的格式掩码转换数字，就会返回错误。不要混淆to_number和to_char转换。
例如：
SQL>selectto_number(123.56,'999.9')fromdual;
selectto_number(123.56,'999.9')fromdual
ORA-01722:无效数字
SQL>selectto_char(123.56,'999.9')fromdual;
TO_CHA
------
123.6
SQL>selectto_number('$123.45','$999.99')resultfromdual;
SQL>selectto_number('$123.45','$99.99')resultfromdual;
selectto_number('$123.45','$99.99')resultfromdual
SQL>selectename,hiredatefromempwherehiredate>'1982-01-01';//oracle隐式转换了
SQL>selectename,hiredatefromempwherehiredate>to_date('1982-01-01','YYYY-MM-DD');//显式
SQL>selectename,job,sal,commfromempwherejob='SALESMAN'ORjob='PRESIDENT'ANDsal>1500;
考点：条件子句使用比较运算符比较两个选项，重要的是要理解这两个选项的数据类型，如有必要可以将它们包含在单引号内。
优先级NOT>AND>OR打破优先级用括号。
SQL>SELECTename,salFROMempWHEREsalBETWEEN1000AND1500;
//between低值and高值，含低值和高值。
在where字句中使用like谓词，常使用特殊符号"%"或"_"匹配查找内容，也可使用escape可以取消特殊符号的作用。
‘%’多个，‘-’一个。
createtabletest1(namechar(10));
insertintotest1values('sFdL');
insertintotest1values('AEdLHH');
insertintotest1values('A%dMH');
commit;
SQL>select*fromtest1;
NAME
sFdL
AEdLHH
A%dMH
SQL>select*fromtest1wherenamelike'A\%%'escape'\';
SQL>SELECTempno,ename,sal,mgrFROMempWHEREmgrIN(7902,7566,7788);
SQL>select*fromemployeeswhereEMPLOYEE_IDin(100,101,102,1000000000000000);
EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBER
-----------------------------------------------------------------------------------------------------
HIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID
----------------------------------------------------------------------------
100StevenKingSKING515.123.4567
1987-06-1700:00:00AD_PRES2400090
101NeenaKochharNKOCHHAR515.123.4568
1989-09-2100:00:00AD_VP1700010090
102LexDeHaanLDEHAAN515.123.4569
1993-01-1300:00:00AD_VP1700010090
（）中的数值可以不存在在表中。
&后的变量名可以随意起名。
Sql>Setverifyoff可以关闭验证。
SQL>selectempno,enamefromempwhereempno=&empnumber;
输入empnumber的值:7788
原值1:selectempno,enamefromempwhereempno=&empnumber
新值1:selectempno,enamefromempwhereempno=7788
EMPNOENAME
--------------------
7788SCOTT
&后面是字符型的，注意单引号问题，可以有两种写法：
SQL>selectempno,enamefromempwhereename='&emp_name';
输入emp_name的值:SCOTT
原值1:selectempno,enamefromempwhereename='&emp_name'
新值1:selectempno,enamefromempwhereename='SCOTT'
SQL>selectempno,enamefromempwhereename=&emp_name;
输入emp_name的值:'SCOTT'
原值1:selectempno,enamefromempwhereename=&emp_name
&&存储了第一次输入值，使后面的相同的&不在提问，自动取代。
SQL>selectempno,ename,&&salaryfromempwheredeptno=10orderby&salary;
输入salary的值:sal
原值1:selectempno,ename,&&salaryfromempwheredeptno=10orderby&salary
新值1:selectempno,ename,salfromempwheredeptno=10orderbysal
EMPNOENAMESAL
------------------------------
7934MILLER1300
7782CLARK2450
7839KING5000
还可以使用define命令常用于批处理。
AND两个条件都为TRUE，则返回TRUE
SQL>SELECTempno,ename,job,salFROMempWHEREsal>=1100ANDjob='CLERK';
EMPNOENAMEJOBSAL
7876ADAMSCLERK1100
7934MILLERCLERK1300
OR两个条件中任何一个为TRUE，则返回TRUE
SQL>SELECTempno,ename,job,salFROMempWHEREsal>=1100ORjob='CLERK';
7369SMITHCLERK800
7499ALLENSALESMAN1600
7521WARDSALESMAN1250
7566JONESMANAGER2975
7654MARTINSALESMAN1250
......
NOT如果条件为FALSE，返回TRUE
SQL>SELECTename,jobFROMempWHEREjobNOTIN('CLERK','MANAGER','ANALYST');
ENAMEJOB
ALLENSALESMAN
WARDSALESMAN
MARTINSALESMAN
KINGPRESIDENT
TURNERSALESMAN
sum();avg();count();max();min().
数值类型可以使用所有组函数
SQL>selectsum(sal)sum,avg(sal)avg,max(sal)max,min(sal)min,count(*)countfromemp;
分组：groupby
对日期类型可以使用MIN,MAX
SQL>selectmin(hiredate),max(hiredate)fromemp;
COUNT(*)函数返回表中行的总数，包括重复行与数据列中含有空值的行，而其他分组函数的统计都不包括空值的行。（考点）
COUNT(EXPR)返回expr标识的列所含非空行的数量。
SQL>selectdeptno,avg(nvl(comm,0))fromempgroupbydeptno;在统计过程中，遇到空值，自动取值为数值0
DEPTNOAVG(NVL(COMM,0))
30366.666667-----------基数6
200
100
SQL>selectdeptno,avg(comm)fromempgroupbydeptno;
DEPTNOAVG(COMM)
30550-------------基数4
20
10
想一想上面两个例子结果为何不一样？
SQL>selectdeptno,avg(nvl(sal,0))fromempgroupbydeptno;
对分组结果进行过滤
SQL>selectdeptno,avg(sal)avgcommfromempgroupbydeptnohavingavg(sal)>2000;
SQL>selectdeptno,avg(sal)avgcommfromempwhereavg(sal)>2000groupbydeptno;//错误的,应该使用HAVING子句
HAVING必须在GROUPBY中使用，不能单独使用，且后面不能直接跟列（select中出现列）
Where后不能直接跟聚合函数。
对分组结果排序
SQL>selectdeptno,avg(nvl(sal,0))avgcommfromempgroupbydeptnoorderbyavg(nvl(sal,0));
确保SELECT列表中除了组函数的项，所有列都包含在GROUPBY子句中（考点）。
例如，错误的写法：selectempno,sum(sal)fromemp;
单行函数可以嵌套任意层，但分组函数最多可以嵌套两层。（考点）
比如：count(sum(avg)))会返回错误“ORA-00935：groupfunctionisnestedtoodeeply”.
在分组函数内可以嵌套单行函数，如：要计算各个部门ename值的平均长度之和
SQL>selectsum(avg(length(ename)))fromempgroupbydeptno;
SUM(AVG(LENGTH(ENAME)))
-----------------------
14.9666667
from,where,groupby,having,orderby,select
where限定from后面的表或视图，限定的选项只能是表的列或列函数或列表达式，where后不可以直接使用分组函数即sum();avg();count();max();min().
SQL>selectempno,jobfromempwheresal>2000;实列
SQL>selectempno,jobfromempwherelength(job)>5;列的单行函数
SQL>selectempno,jobfromempwheresal+comm>2000;列的表达式
having限定groupby的结果，限定的选项必须是groupby后的聚合函数或分组列，不可以直接使用where后的限定选项。
SQL>selectsum(sal)fromempgroupbydeptnohavingdeptno=10;
SQL>selectdeptno,sum(sal)fromempgroupbydeptnohavingsum(sal)>9000;
如果要使用分组函数及having，有条件的话先使用where删选。
1）位置：orderby语句总是在一个select语句的最后面。
2）排序可以使用列名，表达式（虚列），列别名，列的位置等都没有限制，select选项也可以没有排序列。
Orderby2（select后面的第二列）。
3）升序和降序，升序ASC（默认),DESC降序。
SQL>selectename,job,sal+commfromemporderby3descnullslast;(或first。默认为first)
4）混合排序，使用多个列进行排序，多列使用逗号隔开，可以分别在各列后面加升降序。
SQL>selectename,deptno,jobfromemporderbydeptnoasc,jobdesc;
ENAMEDEPTNOJOB
-----------------------------
KING10PRESIDENT
CLARK10MANAGER
MILLER10CLERK
JONES20MANAGER
ADAMS20CLERK
SMITH20CLERK
SCOTT20ANALYST
FORD20ANALYST
MARTIN30SALESMAN
TURNER30SALESMAN
WARD30SALESMAN
ALLEN30SALESMAN
BLAKE30MANAGER
JAMES30CLERK
空值既不是数值0,也不是字符"",
1）空值（null）的数据行将对算数表达式返回空值
SQL>selectename,sal,comm,sal+commfromemp;
ENAMESALCOMMSAL+COMM
----------------------------------------
SMITH800
ALLEN16003001900
WARD12505001750
JONES2975
MARTIN125014002650
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER150001500
ADAMS1100
JAMES950
FORD3000
MILLER1300
2）空值（null）的数据行将比较表达式返回空值
SQL>selectename,sal,commfromempwheresal>=comm;
ENAMESALCOMM
ALLEN1600300
WARD1250500
TURNER15000
3）非空字段与空值字段做"||"时,返回非空字段内容。连字符
SQL>selectename,sal||commfromemp;
ENAMESAL||COMM
------------------------------------------------------------------------------------------
MARTIN12501400
4）notin在子查询中的空值问题（见第八章）
5）外键值可以为null
6）空值在where子句里使用“isnull”或“isnotnull”
SQL>selectempno,mgrfromempwheremgrisnull;
SQL>selectempno,mgrfromempwheremgrisnotnull;
7）空值在update语句和insert语句可以直接使用“=null”赋值
SQL>updateempsetcomm=nullwhereempno=7788;
1）nvl(expr1,expr2)
当第一个参数不为空时取第一个值，当第一个值为NULL时，取第二个参数的值。
SQL>selectnvl(1,2)fromdual;
NVL(1,2)
1
SQL>selectnvl(null,2)fromdual;
NVL(NULL,2)
2
2）nvl2(expr1,expr2,expr3)
当第一个参数不为NULL，取第二个参数的值，当第一个参数为NULL，取第三个数的值。
SQL>selectnvl2(1,2,3)fromdual;
NVL2(1,2,3)
SQL>selectnvl2(null,2,3)fromdual;
NVL2(NULL,2,3)
3
//注意:nvl和nvl2中的第二个参数不是一回事（考点）
3）NULLIF（expr1,expr2)/*比对两个值是否一样，一样就返回为空，否则不会为空*/
当第一个参数和第二个参数相同时，返回为空，当第一个参数和第二个数不同时，返回第一个参数值，第一个参数值不允许为null。第二个可以是NULL
SQL>selectnullif(2,2)fromdual;
NULLIF(2,2)
SQL>selectnullif(1,2)fromdual;
NULLIF(1,2)
4）coalesce（expr1,expr2........)返回第一个不为空的值，如果所有参数都为空，那么返回空值。
SQL>selectcoalesce(1,2,3,4)fromdual;
COALESCE(1,2,3,4)
SQL>selectcoalesce(null,2,null,4)fromdual;
COALESCE(NULL,2,3,4)
SQL>selectcoalesce(null,null,null)fromdual;
全表扫描：指针从第一条记录开始，依次逐行处理，直到最后一条记录结束；
横向过滤+纵向筛选=结果集
两大类：等值连接和非等值连接。
等值连接：例如joinon。等值连接使用‘=’。例如e.abc=b.abce表和b表的公共列abc
交叉连接（笛卡尔积）
典型代表：内连接
外连接（左外，右外，全连接）
自连接
自然连接（隐含连接条件,自动匹配连接字段）
集合运算
范例：
createtablea(idint,namechar(10));
createtableb(idint,locchar(10));
insertintoavalues(1,'a');
insertintoavalues(2,'b');
insertintoavalues(4,'d');
insertintobvalues(1,'A');
insertintobvalues(2,'B');
insertintobvalues(3,'C');
SQL>select*froma;
IDNAME
1a
2b
4d
SQL>select*fromb;
IDLOC
1A
2B
3C
当连接条件无效或被省略时，两个表的所有行都发生连接，所有行的组合都会返回（n*m）
SQL>select*froma,b;
IDNAMEIDLOC
1a1A
1a2B
1a3C
2b1A
2b2B
2b3C
4d1A
4d2B
4d3C
已选择9行。
内联运算效率高
SQL99写法：
SQL>select*fromainnerjoinbona.id=b.id;
oracle写法：
SQL>select*froma,bwherea.id=b.id;
1）左外连接
SQL99语法:
SQL>select*fromaleftjoinbona.id=b.id;
oracle语法:
SQL>select*froma,bwherea.id=b.id(+);左联右补全。
结果：
匹配不上则补全空值。
2）右外连接
SQL>select*fromarightjoinbona.id=b.id;
SQL>select*froma,bwherea.id(+)=b.id;右联左补全。
结果
3）全外连接
SQL>select*fromafulljoinbona.id=b.id;
SQL>select*froma,bwherea.id=b.id(+)
union
select*froma,bwherea.id(+)=b.id;
必须使用别名
SQL>select*fromaa1,aa2;
IDNAMEIDNAME
1a1a
1a2b
1a4d
2b1a
2b2b
2b4d
4d1a
4d2b
4d4d
在oralce中使用naturaljoin,也就是自然连接。在Oracle中的join连接中使用using关键字，
是相对于naturaljoin的。如果是使用natrauljoin，并且两张表中如果有多个字段是具有相同的名称和数据类型的，那么这些字段都将被oracle自作主张的将他们连接起来。但实际上我们有时候是不需要这样来连接的。我们只需要将他们的多个具有相同的名称和数据类型的字段中挑选一两个。这时候我们就需要用到using关键字了。当多列匹配时，用using指定匹配的列
先看自然连接：
SQL>select*fromanaturaljoinb;
IDNAMELOC
1aA
2bB
-----两个表分别再加一个列ABC后，则有两个公共列做连接条件。
IDNAMEABC
1as
2bt
4du
IDLOCABC
1Aw
2Bt
3Cr
IDABCNAMELOC
2tbB
在oracle连接(join)中使用using关键字
SQL>selectid,a.abc,name,locfromajoinbusing(id);可以显示两个表的abc
1saA
SQL>selectid,abc,name,locfromajoinbusing(id,abc);
注意：
1、如果在使用using关键字时，而且select的结果列表项中包含了using关键字所指明的那个关键字，那么，不要在select的结果列表项中对该关键字指明它属于哪个表。
2、using中可以指定多个列名。
3、natural关键字和using关键字是互斥的，也就是说不能同时出现。
SQL>selectx,a.abc,b.abcfromajoinbusing(x);
XABCABC
----------------------------
a123123
Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；包含相交的集合部分，计算一次。
UnionAll，对两个结果集进行并集操作，包括重复行，不进行排序；包含相交的集合部分，计算两次。
Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。
集合操作有并，交，差３种运算。
举例:
SQL>createtabledept1asselect*fromdeptwhererownum<2;从dept表中取第一行的值创建表dept1;
SQL>insertintodept1values(80,'MARKTING','BEIJING');
SQL>select*fromdept;
DEPTNODNAMELOC
-------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
SQL>select*fromdept1;
80MARKTINGBEIJING
SQL>select*fromdept
2union
3select*fromdept1;
2unionall
2intersect
2minus
SQL>select*fromdept1
3select*fromdept;
如果是char类型，长度也必须一样才可以集合运算。列名可以不一样。Verchar2可以不需要长度一样。
可以将CHAR长度扩展。
SQL>createtablea(id_aint,name_achar(10));
SQL>createtableb(id_bint,name_bchar(10),salnumber(10,2));
insertintoavalues(1,'sohu');
insertintoavalues(2,'sina’);
insertintobvalues(1,'sohu',1000);
insertintobvalues(2,'yahoo',2000);
ID_ANAME_A
1sohu
2sina
ID_BNAME_BSAL
1sohu1000
2yahoo2000
SQL>selectid_a,name_afroma
3selectid_b,name_bfromb;
2yahoo
1)，缺省情况下，集合运算后的结果集是按第一列隐式排序的（除unionall外）
如果不希望缺省的排序，也可以使用orderby显示排序。对整个结果集排序。
selectid_a,name_anamefroma
selectid_b,name_bnamefromb
orderbyname;
ID_ANAME
selectid_a,name_afroma
selectid_b,name_bfromb
orderby2;
2),显式orderby是参照第一个表的列元素，如果是补全的null值需要orderby，则需要使用别名。
SQL>selectid_a,name_aname,to_number(null)froma
3selectid_b,name_bname,salfromb
4orderbysal
5/
orderbysal
第4行出现错误:
ORA-00904:"SAL":标识符无效
4orderby3(第三列)
ID_ANAMETO_NUMBER(NULL)
-----------------------------------
SQL>selectid_b,name_bname,salfromb
3selectid_a,name_aname,to_number(null)froma
ID_BNAMESAL
SQL>selectid_a,name_aname,to_number(null)aafroma
3selectid_b,name_bname,salaafromb
4orderbyaa
ID_ANAMEAA
3)，排序是对结果集的排序，不能分别在两表中排序，orderby只能出现一次且在最后一行；
SQL>selectid_a,name_afromaorderbyid_a
3selectid_b,name_bfromborderbyid_b;
第2行出现错误:
ORA-00933:SQL命令未正确结束
升序排列时候，NULL默认排在最后。
子查询返回的值可以被外部查询使用，这样的复合查询等于执行两个连续的查询。
内部SELECT子句只返回一行结果（单列）
SQL>selectename,sal
wheresal>(
selectsalfromemp
whereename='JONES')
和员工7369从事相同工作并且工资大于员工7876的员工的姓名和工作
SQL>selectename,job
wherejob=(
selectjob
whereempno=7369
)
and
sal>(
selectsal
whereempno=7876
（>大于最大的，<小于最小的）
SQL>selectename,salfromempwheresal>all(2000,3000,4000);
ENAMESAL
查找高于所有部门的平均工资的员工（>比子查询中返回的列表中最大的大才行）
SQL>SQL>selectename,job,sal
2fromemp
3wheresal>all(
4selectavg(sal)
5fromemp
6groupbydeptno)
7/
ENAMEJOBSAL
JONESMANAGER2975
SCOTTANALYST3000
KINGPRESIDENT5000
FORDANALYST3000
SQL>selectavg(sal)fromempgroupbydeptno;//子查询结果
AVG(SAL)
1566.66667
2175
2916.66667
（>大于最小的，<小于最大的）
>any的意思是：比子查询中返回的列表中最小的大就行,注意和all的区别，all的条件苛刻，any的条件松阔，
any强调的是只要有任意一个符合就行了，所以>any只要比最小的那个大就行了，没必要比最大的还大。
selectename,salfromempwheresal>any(2000,3000,4000);
(逐个比较是否有匹配值）
SQL>selectename,salfromempwheresalin(800,3000,4000);
NOT运算操作符可以使用在IN操作上,但不能使用在ANY,ALL操作。
SQL>selectename,salfromempwheresalnotin(800,3000,4000);
ALLEN1600
WARD1250
MARTIN1250
TURNER1500
已选择11行。
多列子查询是返回多列结果的内部SELECT语句
将WHERE子句中多个条件合并成一个
列比较有成对比较与不成对比较两种
实验准备
SQL>createtableemp1asselect*fromemp;
SQL>updateemp1setsal=1600,comm=300whereename='SMITH';//SMITH是20部门的员工
SQL>updateemp1setsal=1500,comm=300whereename='CLARK';//CLARK是10部门的员工
SQL>select*fromemp1;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------------------------------
7369SMITHCLERK79021980-12-1700:00:00160030020
7499ALLENSALESMAN76981981-02-2000:00:00160030030
7521WARDSALESMAN76981981-02-2200:00:00125050030
7566JONESMANAGER78391981-04-0200:00:00297520
7654MARTINSALESMAN76981981-09-2800:00:001250140030
7698BLAKEMANAGER78391981-05-0100:00:00285030
7782CLARKMANAGER78391981-06-0900:00:00150030010
7788SCOTTANALYST75661987-04-1900:00:00300020
7839KINGPRESIDENT1981-11-1700:00:00500010
7844TURNERSALESMAN76981981-09-0800:00:001500030
7876ADAMSCLERK77881987-05-2300:00:00110020
7900JAMESCLERK76981981-12-0300:00:0095030
7902FORDANALYST75661981-12-0300:00:00300020
7934MILLERCLERK77821982-01-2300:00:00130010
查询条件：查找emp1表中是否有与30部门的员工工资和奖金相同的其他部门的员工。
（注意看一下：现在20部门的SIMTH符合这个条件，它与30部门的ALLEN有相同的工资和奖金）
成对比较多列子查询
主查询每一行中的列都要与子查询返回列表中的相应列进行比较
只有各列完全匹配时才显示主查询中的该数据行
分解一下：
第一步，我们可以先找出emp1表中30号部门的工资和奖金的结果集，（此例没有对comm的空值进行处理）
SQL>SQL>selectsal,commfromemp1wheredeptno=30;
SALCOMM
1600300
1250500
12501400
2850
15000
950
已选择6行。
第二步，列出emp1表中属于这个结果集的所有员工。
可以这样想：相当于谓词in（...)中有上面这6行内容
SQL>select*fromemp1where(sal,comm)in(selectsal,commfromemp1wheredeptno=30);必须一一对应。
上面句子相当于：
select*fromemp1where(sal,comm)in(
(1600,300),(1250,500),(1250,1400),(2850,null),(1500,0),(950,null)
);
第三步，再去掉30号部门后，就显示出了在emp1表中与30部门中任意一个员工的工资和奖金完全相同的，
但该员工不是来自30部门的员工信息。
selectename,deptno,sal,commfromemp1
where(sal,comm)in(selectsal,commfromemp1wheredeptno=30)
anddeptno<>30
ENAMEDEPTNOSALCOMM
SMITH201600300
SQL>selectename,deptno,sal,comm
fromemp1
wheresalin(
wheredeptno=30)
nvl(comm,0)in(
selectnvl(comm,0)
CLARK101500300
两个子查询返回的值分别与主查询中的sal和comm列比较
员工的工资与30部门任意一个员工相同，同时，奖金也与30部门的任意一个员工相同，则输出该员工信息。
in与notin遇到空值时情况不同，对于notin如果子查询的结果集中有空值，那么最终主查询的结果集是空。考点
查找出没有下属的员工,即普通员工，（该员工号不在mgr之列的）
SQL>selectename
whereempnonotin
(selectmgr
fromemp)
norowsselected
上面的结果不出所料，主查询没有返回记录。这个原因是在子查询中有一个空值，而对于notin这种形式，一旦子查询出现了空值，则主查询记录结果
也就返回空了。
例：排除空值的影响使用去空函数nvl
(selectnvl(mgr,0)
8rowsselected.
例：员工的工资大于他所在的部门的平均工资的话，显示其信息。
分两步来考虑：
第一步，先看看每个部门的平均工资，把这个结果集作为一个内联视图
SQL>selectdeptno,avg(sal)salavgfromempgroupbydeptno;
DEPTNOSALAVG
301566.66667
202175
102916.66667
第二步，把这个内联视图起一个别名b,然后和emp别名e做连接，满足条件即可。
selecte.ename,e.sal,e.deptno,b.salavg
fromempe,(selectdeptno,avg(sal)salavgfromempgroupbydeptno)b
wheree.deptno=b.deptnoande.sal>b.salavg
知识点：内联试图使用在from中，多表连接使用别名。
ENAMESALDEPTNOSALAVG
ALLEN1600301566.66667
JONES2975202175
BLAKE2850301566.66667
SCOTT3000202175
KING5000102916.66667
FORD3000202175
从主查询（外部）调用子查询（内部）来看，可以有分关联与非关联子查询之分
子查询部分可以独立执行，Oracle的in子查询一般用于非关联子查询，执行过程是这样的，首先执行子查询，并将获得的结果列表存放在一个加了索引
的临时表中。也就是说在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。
例：查找名称为ACCOUNTING的部门里所有员工的信息
selectename,deptnofromemp
wheredeptnoin(
selectdeptnofromdeptwheredname='ACCOUNTING')
ENAMEDEPTNO
CLARK10
KING10
MILLER10
其子查询（内部，inner)会引用主查询(外部，outer)查询中的一列或多列。在执行时,外部查询的每一行都被一次一行地传递给子查询.子查询依次读取
外部查询传递来的每一值,并将其用到子查询上,直到外部查询所有的行都处理完为止.然后返回查询结果.
同例：
selectename,deptnofromempe
selectd.deptnofromdeptdwheree.deptno=d.deptnoandd.dname='ACCOUNTING')
注意1）以上两例都是用了in查询，结果一样，但是Oracle的查询过程却截然不同。
2）in用于关联子查询效率不高，Oracle推荐使用exists()代替
SQL>selectename,deptnofromempe
whereexists(
可以是select*
其中“e.deptno=d.deptno”相当于一个关联表查询，如果你只执行括号里的语句，是会报语法错误的，这也是使用关联子查询的特点。
“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。因此，子查询中的“selectd.deptno”这里的“d.deptno”其实是无关紧要的，换成“*”也没问题，它只在乎括号里的数据能不能查找出来，是否存在这样的记录，如果存在，外部语句的where条件成立。
对于关联子查询，使用exists操作符可能比in更快。比较两种查询的执行有以下特点：
第一，差异在于两类查询的工作方式不同，in操作是在括号里的内容必须经过整个子表扫描之后，符合条件的内容（结果集）全部确定下来后才会进行
in的选取，而exist是逻辑判断，如果在子表逐行过滤中碰到一行符合条件，即逻辑比较为‘真’了，则立刻返回，该次子表查询也立刻结束。这就意味着它可能没有全表遍历，省去了子表后面扫描操作。
第二，dept表只有4行记录，如果是4万行，效果就出来了，比如，极端情况下，主查询第m次调用，子查询在第一条就满足了，则马上返回EXISTS='真'.那么第m次调用就结束了，也就是说子查询省去了39999行的后续扫描。所以子表越大，主表越小exists的效率越高。
第三，exists的效率是和表的数据有关的、也和表的数据的排列有关，子表遇到“真”时要看是靠前匹配，还是到靠后匹配，效果是大不一样的，当然如果不能匹配上，则EXISTS=“假”，这样的结果也是做了子表的全表扫描，同in相比的话，也并没有提高效率。
第四，in可以用于各种子查询，而exists只用于关联子查询,exists用于非关联查询是没有意义的（主查询要么返回所有结果，要么无结果。）
第五，exists是Oracle特有的，推荐使用。另外还有notin与notexists的比较，机制类似。
a）列出emp表工资最高的前三名员工信息
rownum,rowedrownum不能用大于号连接，只能用小于号或者等于号。
select*from(select*fromemporderbysaldesc)whererownum<4;
select*from(selectrownumasrn,employee_idasid,first_name||''||last_nameasname,salaryfromemployeesorderbysalarydesc);
b)列出emp表第5-第10名员工的信息（结果集的分页查询技术）
select*from(selectt1.*,rownumrnfromempt1)wherernbetween5and10;
c）从列出emp表中显示员工和经理对应关系表。(emp自连,利用笛卡尔积）
selecta.empno,a.ename,a.mgr,b.empno,b.ename,b.mgr
fromempa,empb
wherea.mgr=b.empno;
d)要求列出emp表中最高工资的员工所在工作地点，(emp+dept左外)
selecta.ename,d.locfrom
(select*fromempwheresal=
(selectmax(sal)fromemp))aleftjoindeptdona.deptno=d.deptno;
e）根据emp表和dept表组织一张新表emp1，要求新表能看到所有员工的员工号，姓名与工作地点,部门编号的对应信息。
createtableemp1as(selecte.empno,e.ename,d.loc,d.deptnofromempe,deptdwheree.deptno=d.deptno(+));
f）在e）的基础上将所有loc字段的记录修改为空值，再利用dept表更新（深入理解update语句执行过程、顺序,及关联子查询概念）
SQL>updateemp1setloc=null;
SQL>updateemp1esetloc=(selectd.locfromdeptdwheree.deptno=d.deptno);
g)三表联查，假设有三个表（显示如下），分别是emp1，title,detp要求列出各部门经理的信息，信息里包括员工编号、员工姓名、薪水、头衔、部门编号、工作地点。
表A----------表B----------------表C
表A与表B之间有公共列，表B与表C之间有公共列，即可将三个表连接。
7369SMITH800
7499ALLEN1600
7521WARD1250
7566JONES2975
7654MARTIN1250
7698BLAKE2850
7788SCOTT3000
7844TURNER1500
7876ADAMS1100
7900JAMES950
7902FORD3000
SQL>select*fromtitle;
EMPNOJOBDEPTNO
7369CLERK20
7499SALESMAN30
7521SALESMAN30
7566MANAGER20
7654SALESMAN30
7698MANAGER30
7782MANAGER10
7788ANALYST20
7839PRESIDENT10
7844SALESMAN30
7876CLERK20
7900CLERK30
7902ANALYST20
7934CLERK10
SQL>selecte.empno,e.ename,e.sal,t.job,t.deptno,d.loc
fromemp1e,titlet,deptd
wheret.deptno=d.deptno
ande.empno=t.empno
andt.job=UPPER('manager')
EMPNOENAMESALJOBDEPTNOLOC
--------------------------------------------------------------
7566JONES2975MANAGER20DALLAS
7698BLAKE2850MANAGER30CHICAGO
7782CLARK2450MANAGER10NEWYORK
Selecte.empno,e.ename,e.sal,t.job,t.deptno,d.locfromempeinnerjointitletone.empno=t.empnoinnerjoindeptdont.deptno=d.deptnowheret.job=UPPER(‘manager’)
创建一个用户，可以三种缺省：缺省表空间，缺省临时文件，缺省profile
每个用户账户都有默然的表空间，用户创建的任何对象（如表或索引）将保存在此表空间中，如果创建用户是没有指定默认表空间，那么属于数据库级的默认表空间（缺省安装时是users表空间）将应用于所有账户。Users表空间是所有用户的公共的默认表空间。
SQL>Select*fromv$tablespace;
TS#NAMEINCBIGFLAENC
----------------------------------------------------
0SYSTEMYESNOYES
1SYSAUXYESNOYES
4USERSYESNOYES
6EXAMPLEYESNOYES
8TESTYESNOYES
2UNDOTBS1YESNOYES
3TEMPNONOYES
数据库级的默认表空间可以更改
alterdatabasedefaulttablespacetablespace_name;
配额（quota)是表空间中为用户的对象使用的空间量，dba建立用户时就应该考虑限制用户的磁盘配额，否则用户可以将对他无限制配额的表空间撑爆。
--设置限额=10m
ALTERusertimquota10montest_tbs;
--限额无限=不受限
ALTERUSERtimQUOTAUNLIMITEDONtest_tbs;
--收回限额
ALTERUSERtimQUOTA0ONtest_tbs;
考点：
1）要创建表，用户必须具有执行createtable的权限，而且拥有在其中创建表的表空间上的配额。要创建表必须满足这两个条件：权限和配额。
2）用户不需要临时表空间上的配额。临时表空间是公共空间。
作用是对用户访问数据库做一些限制。有几个要点：
1）概要文件（profile)具有两个功能，一个是实施口令限制，另一个是限制会话可以占用的资源。
2）始终要实施口令控制，而对于资源限制，则只有实例参数RESOURE_LIMIT为TRUE时（默认是FALSE)才会实施（考点）。
3）系统自动使用概要文件，默认的概要文件限制很松，作用较小。
4）可以使用createprofile为用户创建它自己的概要文件，没有说明的参数就从defauleprofile的当前版本中提取。
Password_parameter部分：
Failed_login_attempts：指定在帐户被锁定之前所允许尝试登陆的的最大次数。
Password_lock_time：在到达Failed_login_attempts后锁定账户的天数。
Password_life_time：口令过期前的天数，口令在过期后仍可以使用，具体取决于Password_grace_time
Password_reuse_time：可以重新使用口令前的天数
password_reuse_max：可以重新使用口令的次数
Password_verify_function：更改口令时运行的函数名，此函数一般用于检查新口令所需的复杂程度。
Resource_parameter部分
Logical_reads_per_session：在强制终止会话前，会话可读取的块数（无论块在数据缓冲区还是磁盘）。
Logical_read_per_call：在强制终止单个语句前，此语句可读取的块数（无论块在数据缓冲区还是磁盘）。
Private_sga：对于通过共享服务器体系结构连接的会话，允许会话的会话数据在SGA中占用的字节数（KB）。
Composite_limit：前面几个参数的加权和。这是一个高级功能，其需要的配置不在OCP考试范围。
例：
1）创建一个概要文件，如果出现两次口令失误，那么将账户锁定。
SQL>createprofiletwo_errorlimitfailed_login_attempts2;
2）将概要文件分配给tim用户
SQL>alterusertimprofiletwo_error;
SQL>conntim/fdfd
ERROR:
ORA-28000:帐户已被锁定
Alterusertimaccountunlock解锁。
4）sys为tim解锁
SQL>conn/assysdba
SQL>alterusertimaccountunlock;
5）sys删掉了two_error概要文件
SQL>dropprofiletwo_error;
//profile这部分操作参数较多，使用命令有些啰嗦，可以使用EM方式来管理，比较方便。
删除profile不会删除用户。
数据库安全分为系统安全和数据安全
系统安全：用户名和口令，分配给用户的磁盘空间及用户的系统操作
数据库安全：对数据库对象的访问及操作
用户具备系统权限才能够访问数据库
具备对象权限才能访问数据库中的对象
简而言之：权限（privilege）：systemprivilegeandobjectprivilege
系统权限通常由DBA授予（11g有200多种，selectdistinctprivilegefromdba_sys_privs;也可被其他用户或角色授予）
典型DBA权限
CREATEUSER
DROPUSER
BACKUPANYTABLE
SELECTANYTABLE
CREATEANYTABLE
典型用户需要的系统权限
CREATESESSION
CREATETABLE
CREATESEQUENCE
CREATEVIEW
CREATEPROCEDURE
2）objectprivilege：针对于schema(用户）的object
对象权限有8种：ALTERDELETEEXECUTEINDEXINSERTREFERENCESSELECTUPDATE
主要的常用的对象
表
试图
序列
过程
alter
delete
Execute
index
insert
refrerences
select
update
授予系统权限语法：
GRANTsys_privs,[role]TOuser|role|PUBLIC[WITHADMINOPTION]
//授予角色与系统权限的语法格式是一样的,所以可以并列在一个句子里赋权考点。
授予对象权限语法
GRANTobject_privsONobjectTOuser|role|PUBLIC[WITHGRANTOPTION]
创建和删除角色
CREATErolemyrole;
DROProlemyrole;
角色相当于是N多权限的集合。可以包含系统角色，权限，对象权限等。
sys:
SQL>createusertimidentifiedbytim;//建一个tim用户
SQL>conntim/tim
警告:您不再连接到ORACLE。
已连接。
SQL>grantcreatesessiontotim;//授予tim系统权限createsession
SQL>select*fromtab;
未选定行
SQL>createtablea(idint);
createtablea(idint)
ORA-01031:权限不足
SQL>grantcreatetabletotim;//授予tim系统权限createtable
tim:
ORA-01950:对表空间'USERS'无权限
SQL>grantunlimitedtablespacetotim;//授予tim系统权限unlimitedtablespace，可以无限制的使用任何表空间
SQL>alterusertimquota5monusers;//仅对于使用users表空间加上了磁盘限额。
SQL>select*fromsession_privs;
PRIVILEGE
UNLIMITEDTABLESPACE
列出oracle所有系统权限;
SQL>selectdistinctprivilegefromdba_sys_privs;
可以看出由于系统权限太过繁杂，oracle引入角色（role),通过角色得授予是的用户访问管理得以大大简化。
SQL>dropusertimcascade;
SQL>createusertimidentifiedbytim;
SQL>grantconnect,resourcetotim;两个角色赋予TIM
CREATECLUSTER
CREATETRIGGER
CREATETYPE
CREATEOPERATOR
CREATEINDEXTYPE
已选择10行。
需要注意一点：在resource角色里包含了unlimitedtablesapce权限，对于所有的表空间不限制配额，此权限太大，它包括可以访问system表空间，在实际应用中一般要将此权限收回，然后再对用户限制配额。权限不能限制配额。
SQL>createtablespacetest_tbsdatafile'/u01/oradata/timran11g/test01.dbf'size10m;
SQL>createusertimidentifiedbytimdefaulttablespacetest_tbs;
SQL>grantconnect,resourcetotim;
SQL>revokeunlimitedtablespacefromtim;
SQL>alterusertimquota10montest_tbs;
回收配额之后，不可以新建表，但是可以修改插入原来已经创建的表。
SQL>selecttablespace_name,username,max_bytesfromDBA_TS_QUOTASwhereusername='TIM';
2）系统权限里的any含义：
SQL>grantcreateanytabletotim;tim可以给任何人（usera）建表。所创建的表不在tim下，在usera下可以查询到。
SQL>createtablescott.t100(idint);
3）可以使update对象权限精确到列：赋予update某一列的权限时候，确保之前没有给过update全表的权限，如果有，先收回，在赋予。
scott:
SQL>grantselect,update(sal)onemptotim;
SQL>revokeupdate(sal)onempfromtim;
revokeupdate(sal)onempfromtim
ORA-01750:UPDATE/REFERENCES只能从整个表而不能按列REVOKE
SQL>revokeupdateonempfromtim;
撤销成功。
4）对象权限的传递与回收：WITHGRANTOPTION选项
先建立两个测试用户tim和ran（sys）
CREATEUSERtimIDENTIFIEDBYtim;GRANTCREATESESSIONTOtim;
CREATEUSERranIDENTIFIEDBYran;GRANTCREATESESSIONTOran;
GRANTSELECTONempTOtimWITHGRANTOPTION;
检查tim用户能否访问scott.emp....
tim用户把该权限分给ran用户（tim）
GRANTSELECTONscott.empTOran;
检查ran用户能否访问scott.emp....
回收对象权限将会级联
REVOKESELECTONempFROMtim;（scott）
回收了tim的对象权限后，tim二次分配给ran的对象权限也隐式的收回了。
5）系统权限的传递与回收：WITHADMINOPTION选项
为tim用户授予系统权限
GRANTCREATETABLETOtimWITHADMINOPTION;（sys）
查看tim用户拥有的系统权限
SELECT*FROMUSER_SYS_PRIVS;（tim）
USERNAMEPRIVILEGEADMIN_OPT
--------------------------------------------
timCREATETABLEYES
timCREATESESSIONNO
二次分配给ran用户
GRANTCREATETABLETOran;（tim）
查看ran用户系统权限
SELECT*FROMUSER_SYS_PRIVS;（ran）
ranCREATETABLENO
ranCREATESESSIONNO
收回系统权限不会级联
REVOKECREATETABLEFROMtim;（sys）
回收了tim的系统权限后，tim二次分配给ran的系统权限并不会自动收回。
对象权限如果将tim用户删除，ran用户的对象权限也没有了。
6）对象权限不能和系统权限一起给，因为语法不一样。
7）对象权限可以由owner给也可以由sysdba给。
SESSION_PRIVS//用户当前会话拥有的系统权限
USER_ROLE_PRIVS//用户被授予的角色
ROLE_SYS_PRIVS//用户当前拥有的角色的系统权限
USER_SYS_PRIVS//直接授予用户的系统权限
USER_TAB_PRIVS//授予用户的对象权限
ROLE_TAB_PRIVS//授予角色的表的权限
练习:要掌握权限与角色的关系，以及如何查看信息，
数据字典
dba_xxx_privs
all_xxx_privs
user_xxx_privs
其中xxx：role表示角色，sys表示系统权限，tab表示对象权限。
从哪个角度看，非常重要！
我们举个例子：三个用户，分别是sys,scott,和tim，
1)建立myrole角色，把connect角色和createtable系统权限以及updateonscott.emp对象权限放进myrole。
2)把myrole角色授给tim。
3)把createtable系统权限授给tim。
把selectonemp表的对象权限授给tim
如此tim用户有了如下角色和权限：
myrole(connect,createtable，updateonscott.emp)
createtable
selectonemp
我们从三个角度分析一下，如何里数据字典里查看tim拥有的角色和权限信息。
从dba角度想看：
数据库里有多少用户？
selectusernamefromdba_users;
看用户tim所拥有的系统权限
select*fromdba_sys_privswheregrantee='TIM';
看用户tim所拥有的对象权限
select*fromdba_tab_privswheregrantee='TIM';
看用户tim所拥有的角色（不包含角色里的角色）
select*fromdba_role_privswheregrantee='TIM';
查看这个角色里包含的角色
select*fromdba_role_privswheregrantee='MYROLE';
查看这个角色里包含的系统权限
select*fromdba_sys_privswheregrantee='MYROLE';
查看这个角色里包含的对象权限
select*fromdba_tab_privswheregrantee='MYROLE';
从当前用户角度看：和自己有关的角色都有什么内容？
角色里包含的角色
select*fromrole_role_privs;
角色里包括的系统权限
select*fromrole_sys_privs;
角色里包括的对象权限
select*fromrole_tab_privs;
查看和自己有关的角色（不含角色中含有的角色）
select*fromuser_role_privs;
查看和自己有关的系统权限（不含角色中的系统权限）
select*fromuser_sys_privs;
查看和自己有关的对象权限（不含角色中的对象权限）
select*fromuser_tab_privs;
查看和自己有关的系统权限(包括角色里的权限）
select*fromsession_privs;
从scott用户看是个什么情况
select*fromall_tab_privswheregrantee='TIM';
select*fromall_tab_privswheretable_name='EMP';
必须具备以下四个属性，简称ACID属性：
原子性（Atimicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
一致性（Consistency）：一个查询的结果必须与数据库在查询开始的状态一致。
隔离性（Isolation）：对于其他部分来说，未完成的（也就是未提交的）事务必须不可见。
持久性（Durability）：事务完成后，它对数据库就不可以丢失这个事务，数据库通过日志能够保持事务的持久性
1）COMMIT（提交）或ROLLBACK（回滚）
2）DDL语句被执行（提交）数据定义语言包括createaltertruncaterenamedrop
3）DCL语句被执行（提交）数据控制语言包括grantrevoked
4）用户退出SQL*PLUS（正常退出是提交，非正常退出是回滚）还没提及就重启电脑
5）机器故障或系统崩溃（回滚）
6）shutdowmimmediate(回滚）
考点：在一个事务里如果某个语句失败，则此语句回滚，其他任何DML语句将保持完好，而且不会提交！
用sys查看事务。Select*fromv$transaction.
保存点就是允许在事务中设置一个标记，这个标记可以控制rollback的效果，即在一个事务中回滚掉最近的部分dml语句，保留下保存点之前的的dml语句，并使事务本身继续执行。也就是说回滚到保存点这个动作并不使事务结束。
SAVEPOINT实验
savepointsp1；
deletefromemp1whereempno=7900;
savepointsp2;
updateemp1setename='XIAOMAO'whereempno=9000;
select*fromemp1；
rollbacktosp2;
rollbacktosp1;
如果先回滚到sp1则无法再回滚到sp2.
行锁(TX)只有一个
表锁(TM)共有五个，分别是RS,RX,S,SRX,X。lmode从2开始算，rs:2,rx:3,s:4,srx:5,x:6
比如一个update语句，有行锁（即TX锁），和表级锁（RX)，在功能上对应，只是表级锁还有一层意思是规定了不许别人加的锁，特别防止DDL语句操作这个表。不允许其他DDL操作。
Oracle是先申请表级锁RX,获得后，系统再自动申请行锁(TX),并将实际锁定的数据行的锁标志置位（即指向该TX锁）
第一种方式：自动加锁
做DML操作时，如insert，update，delete，以及select....forupdate由oracle自动完成加锁
scott://用forupdate加锁
SQL>select*fromdeptwheredeptno>20forupdate;当其他用户在对结果中的记录操作时，会被挂起。
system://试探，以防被锁住以防被挂起。
SQL>select*fromscott.deptforupdatenowait;如果目标资源被锁住，则提示用户，不要把事物挂起。
SQL>select*fromscott.deptforupdatewait5;如果目标资源被加了锁，则等待五秒钟，如果未解锁，则结束防止session被挂起。
1）对整个表forupdate是不锁插入语句的。
2）参数wait5等5秒自动退出，防止被锁住。参数nowait，不等待。
select*fromemp1wheredeptno=10forupdate;
select*fromemp1forupdateskiplocked.跳过已经上锁的记录，锁住余下的所有记录。
第二种方式：人工方式加锁，显示的用lock命令加锁。
locktable表名inexclusivemode.（一般限于后三种表锁）
ROWSHARE行共享(RS)，允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁
ROWEXCLUSIVE行排他(RX)，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁
SHARE共享(S)，不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁
SHAREROWEXCLUSIVE(SRX)共享行排他，不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁
EXCLUSIVE(X)排他，其他用户禁止更新任何行，禁止其他用户同时加任何。
sql语句加锁模式许可其他用户的加锁模式
-----------------------------------------------------------------------------------------------
select*fromtable_name无RS,RX,S,SRX,X
insert,update,delete(DML操作）RXRS,RX
select*fromtable_nameforupdateRXRS,RX
locktabletable_nameinrowsharemodeRSRS,RX,S,SRX
locktabletable_nameinrowexclusivemodeRXRS,RX
locktabletable_nameinsharemodeSRS,S
locktabletable_nameinsharerowexclusivemodeSRXRS
locktabletable_nameinexclusivemodeX无
观察锁的动态视图v$locksid：session号。Lmode：锁级别。Block:阻塞了其他用户的DML操作，0表示没有阻塞Request:请求上锁，0表示没有请求。
观察锁的动态视图dba_locks
TM锁分为两类：排他，共享
select*fromv$lock;
select*fromdba_lockswheresession_id=149;
制作死锁案例：
ID_A
brain::
ID_B
Oracle的机制，当两个进程产生相互block的死锁时候，最早的进程会释放锁。
ORA-00060:deadlockdetectedwhilewaitingforresource等待资源时检测到死锁
scott://改自己,不提交
updatetableasetid=11whereid=1;
brain://改自己,不提交
updatetablebsetid=1100whereid=100;
scott://改对方,被锁住
updatetablebrain.bid=1000whereid=100;
brain://改对方,造成死锁
先看事务的锁，sid是session号
ADDRKADDRSIDTYPEID1ID2LMODEREQUESTCTIMEBLOCK
0036ABAC0036ABDC128TM7578803021850
37901E0437901E44128TX52431314876021850
再看看sid=128这个session的的事务里最后一条sql语句是：
selecta.sid,a.serial#,b.sql_textfromv$sessiona,v$sqlbwherea.prev_sql_id=b.sql_idanda.sid=128;
SIDSERIAL#SQL_TEXT
----------------------------------------------------------------------------------------------------
12820updateasetid=3whereid=1
确定无误后
杀掉这个session
SQL>ALTERSYSTEMKILLSESSION'128,20';
加入一个B树和位图的图示。
以B树索引为例分析它的结构（图）balancetree平衡树
当对表进行DML操作比如删除和插入等，系统也会自动的维护索引，当索引同步维护后才会打印执行完毕的信息。所以建立过多不必要的索引，对表经常操作，会使DML执行效率降低。Update表，相对而言，对索引的维护工作量会小很多。
如果SQL语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表，如果该语句同时还要访问除索引列之外的列，那么，数据库会使用rowid来查找表中的行，通常，为检索表数据，数据库以交替方式先读取索引块，然后读取相应的表块。
如果SQL语句没有用到被索引的列，那么数据库会遍历表，不会使用索引，所以要优化我们的查询语句，使之尽可能的使用索引，提高搜索速度。
B树中的叶子节点：存放了键值和rowid。
叶子节点是经过排序的。
适合情况不适合情况
--------------------------------------------------------------------------------------------
经常用于WHERE子句或作为连接条件的列表很小
所含数据值范围比较的列列很少在查询中作为条件
含有大量空值的列多数情况下查询出大于总记录的5%的表
总是使用两个或多个组合列查询频繁更新的表
多数情况下只查询总记录小于5%的表
考点：索引里不存空值。
1）B树索引，
2）位图索引
建立B树索引选项包括：
唯一或非唯一索引（Uniqueornon_unique):唯一索引指键值不重复。
组合索引(Composite)：绑定了两个或更多列的索引。（job,deptno）组合索引，当使用组合和job（即第一个字段）时，使用索引。当使用后一个字段查询时候，不使用索引，全表扫描。考点。
反向键索引(Reverse)：将字节倒置后组织键值。
函数索引(Functionbase)：以索引列值的函数值为键值去组织索引
压缩（Compressed)：重复键值只存储一次，就是说如果重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串。
升序或降序（Ascendingordescending)：叶节点中的键值排列默认是升序的。
USER_INDEXES//索引主要信息
USER_IND_CULUMNS//索引列的信息
Oracle的执行计划常见的四种索引扫描方式：
1）索引唯一扫描(indexuniquescan)
通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中。
2）索引范围扫描(indexrangescan)
在非唯一索引上，可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。
使用indexrangscan的3种情况：
(a)在唯一索引列上使用了range操作符(><<>>=<=between)
(b)在组合索引上，只使用部分列进行查询，导致查询出多行
(c)对非唯一索引列上进行的任何查询。
3）索引全扫描(indexfullscan)
对整个index进行扫描，并且顺序的读取其中数据。
全Oracle索引扫描只在CBO(COSTBASED基于成本的优化器，I/O读写，cpu,内存成本
)模式下才有效。CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。
并行顺序读。聚合函数常用到。
4）索引快速扫描(indexfastfullscan)
扫描索引中的所有的数据块，与indexfullscan很类似，但是一个显著的区别是fullscan是根据叶子块的双向列表顺序读取，读取的块是有顺序的，也是经过排序的，所以返回的列表也是排序的。而fastfullscan在读取叶子块时的顺序完全由物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。并行读。聚合函数常用到。
//分析器是根据要访问的数据量和索引的填充因子等属性判断使用RANGSCAN或FULLINDEXSCA
举例这四种索引扫描方式：
sys
SQL>setautotracetraceonlyexplain执行计划，不显示查询结果;setautotraceoff关闭计划。只能dba使用。
SQL>selectempnofromscott.emp1;
执行计划
----------------------------------------------------------
Planhashvalue:2226897347
--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
|0|SELECTSTATEMENT||14|182|3(0)|00:00:01|
|1|TABLEACCESSFULL|EMP1|14|182|3(0)|00:00:01|
SQL>createuniqueindexemp1_idxonemp1(empno);
索引已创建。
SQL>selectempnofromscott.emp1whereempno=7788;
Planhashvalue:1995401140
------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|13|0(0)|00:00:01|
|*1|INDEXUNIQUESCAN|EMP1_IDX|1|13|0(0)|00:00:01|
SQL>dropindexemp1_idx;
SQL>createindexemp1_idxonemp1(empno);
SQL>analyzeindex索引名validatestructure;先分析，实行一条dml语句
SQL>select*fromindex_stats;查看索引的分支节点,blocks等信息。
Hight层高，不要大于3
Planhashvalue:253836959
-----------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|13|1(0)|00:00:01|
|*1|INDEXRANGESCAN|EMP1_IDX|1|13|1(0)|00:00:01|
SQL>selectcount(empno)fromscott.emp1;
Planhashvalue:1026340756
|1|SORTAGGREGATE||1|13|||
|2|INDEXFULLSCAN|EMP1_IDX|14|182|1(0)|00:00:01|
scott：
SQL>insertintoemp1select*fromemp1;
已创建14行。
SQL>/
.....
已创建14336行。
SQL>altertablescott.emp1modify(empnonotnull);--因索引的叶子块不存空值，使empno字段非空。
SQL>selectempnofromscott.emp1;--数据库仅访问索引本身的数据。而无需访问表。
Planhashvalue:1892048637
---------------------------------------------------------------------------------
|0|SELECTSTATEMENT||28033|355K|29(0)|00:00:01|
|1|INDEXFASTFULLSCAN|EMP1_IDX|28033|355K|29(0)|00:00:01|
Note
-dynamicsamplingusedforthisstatement
可以看出：oracle在满足一定条件时会先选fastfullscan,而不是fullscan，因为前者的cost是29，比后者低许多。
可以加一行说明，强制使用fullscan的执行计划，cost是100.
SQL>select/*+index(emp1emp1_idx)*/empnofromscott.emp1;
Planhashvalue:4252953140
|0|SELECTSTATEMENT||28033|355K|100(0)|00:00:02|
|1|INDEXFULLSCAN|EMP1_IDX|28033|355K|100(0)|00:00:02|
查询索引的两个动态视图：
select*fromuser_indexes;
select*fromuser_ind_columnsuwhereu.index_name='IND1';
select*fromdba_indexes;
select*fromuser_ind_columnsuwhereu.index_name=’IND1’;
Oracle没有量化碎片，当执行了大量的DML语句，感觉索引比较慢了，然后当以下三种情况发生时，就说明积累的碎片改整理了。
1、hight>=4
2、pct_used<50%(排除，插入大量数据后，才创建的索引的情况。因为那时候创建的索引，pct_used值就有可能<50%)
3、del_lf_rows/lf_rows>0.2lf_row叶子行lf_blk叶子块
createindexind_tont(t1)
selectname,pct_usedfromindex_statswherename='IND_T';(运行此句之前，先分析analyzeindexind_1validatestructure。Analyze是基于session的分析。)
createtablet(idint);
createindexind_1ont(id);
begin
foriin1..1000000loop
insertintotvalues(i);
ifmod(i,100)=0then
endif;
endloop;
end;
analyzeindexind_1validatestructure;
selectname,pct_usedfromindex_stats;
deletetwhererownum<700000;
alterindexind_1rebuild[online];重建索引
alterindexind_1coalesce;
在Oracle11g中有了改进，实现了最终意义上的联机索引重建(rebuildindexonline)的特性，它重新设计了锁的模式，因此容许DML操作不受干扰。
在11g里，Oracle提供了一个新特性来降低直接删除索引或禁用索引的风险，那就是索引不可见（IndexInvisible)。我们可以在创建索引时指定
unusable索引不更新
考点：invisible和unusable的区别。
invisible属性或者用alter语句来修改索引为invisible(visible)
SQL>createtabletest(idint,namechar(10));
SQL>createindextest_idxontest(id)invisible;
SQL>alterindextest_idxvisible;
SQL>alterindextest_idxinvisible;
当索引被设为不可见后，实际上就是指该索引对于优化器不可见，而索引的正常更新仍然会由有Oracle自动完成的。对比11g前的unusable，（保存索引定义，不删除索引，也不更新索引）。
SQL>alterindextest_idxunusable;
约束是数据库能够实施业务规则以及保证数据遵循实体-关系模型的一种手段。
考点：如果违反约束，将自动回滚出现问题的整个语句，而不是语句中的单个操作，也不是整个事务。
列级定义：只能引用一个列，表中可以有多个列级约束。
表级定义：引用一个或多个列，通常用来定义主键。
追加定义：建表后，再通过altertable命令追加的约束。
查看约束的两个数据字典视图
select*fromuser_constraints;
selectconstraint_name,column_name,table_namefromuser_cons_columns;
列级定义：
createtablestud(idnumber(2)notnull,namevarchar(4))
追加非空约束：
notnull约束比较特殊，一般只是列级定义和表外定义，当使用表外（追加）时，要使用modify关键字，
如：altertableemp1modifyenamenotnull;
或altertableemp1modifyenameconstraintxyznotnull;
查看非空约束
selectconstraint_name,constraint_type,table_name
fromuser_constraints
whereowner='SCOTT'
andtable_name=UPPER('stud')
删除非空约束
altertablestuddropconstraintSYS_C005454//SYS_C005454是系统生成的约束名
列级定义
SQL>createtablea1(idnumber(2)unique,namevarchar2(4));
表级定义
createtablea2(idnumber(2),namevarchar2(4),constraintid_ukunique(id));
追加定义
altertablea2addCONSTRAINTid_ukUNIQUE(id);
删除唯一约束
altertablestuddropunique(id);
主键约束的六种写法
1）createtableu1(idchar(10)primarykey,namechar(20));--主键名字，Oracle起的
2）createtableu2(idchar(10)constraintpk_u2primarykey,namechar(20));--主键名字，自己指定
3）createtableu3(idchar(10),namechar(20),primarykey(id));--主键名字，Oracle起的
4）createtableu4(idchar(10),namechar(20),CONSTRAINTSpk_u4primarykey(id));--主键名字，自己指定
createtableu5(idchar(10),namechar(20));
5）altertableu5addprimarykey(id);
6）altertableu5addCONSTRAINTpk_u5primarykey(id);--表外，后来加上主键。
删除主键约束
altertabledept1dropconstraintd_pk;
建立主键约束和唯一约束时，如果该列没有索引，oracle会自动在该列上创建一个唯一性索引。为什么？主键是非空且唯一。
关于主键和索引关联的问题:（这个地方考点较多)
SQL>createtablet(idint,namechar(10));
SQL>insertintotvalues(1,'sohu');
SQL>insertintotvalues(2,'sina')
SQL>commit;
SQL>createindext_idxont(id);
下面这两句话是一样的效果，因为缺省情况下id列已经有索引t_id了，建主键时就会自动用这个索引（考点）。不能删除主键（或unique）创建的索引。删除约束之后索引还在。
SQL>altertabletaddconstraintpk_idprimarykey(id);
SQL>altertabletaddconstraintpk_idprimarykey(id)usingindext_idx;
SQL>selectCONSTRAINT_NAME,TABLE_NAME,INDEX_NAMEfromuser_constraints;
CONSTRAINT_NAMETABLE_NAMEINDEX_NAME
FK_DEPTNOEMP
PK_DEPTDEPTPK_DEPT
PK_EMPEMPPK_EMP
PK_IDTT_IDX
SQL>altertabletdropconstraintpk_id;//删除了约束，索引还在，本来就是借用的索引。
SQL>selectindex_namefromuser_indexes;
INDEX_NAME
PK_EMP
PK_DEPT
T_IDX
SQL>droptabletpurge;//t_idx是和t表关联的，关键字purge使表和索引一并永久删除了。
也可以使用using字句在建表建约束建约束一条龙下来，当然primarykey也会自动使用这个索引(考点）。
SQL>createtablet(idint,namechar(10),constraintpk_idprimarykey(id)usingindex
(createindext_idxont(id)));
作用：是为了和同一个表或其他表的主关键字或唯一关键字建立连接关系，外键值必须和父表中的值匹配或者为空值。
考点：外键约束和unique约束都可以有空值。
SQL>createtabledept1asselect*fromdept;
SQL>altertabledept1addprimarykey(deptno);
SQL>createtableemp100(empnoint,deptnointreferencesdept1(deptno),deptno2int);
//外键的列级定义不写foreignkey关键字，有点不习惯。
SQL>createtableemp200(empnoint,deptnoint,salint,foreignkey(deptno)referencesdept1(deptno));
ALTERTABLEemp100ADDCONSTRAINTe_fkFOREIGNKEY(deptno2)REFERENCESdept1(deptno);
关于ONDELETECASCADE关键字
SQL>droptableemp100purge;
SQL>droptableemp200purge;
SQL>ALTERTABLEemp1ADDCONSTRAINTe_fkFOREIGNKEY(deptno)REFERENCESdept1(deptno);
测试：
deletefromdept1wheredeptno=30
ERRORatline1:
ORA-02292:integrityconstraint(SCOTT.E_FK)violated-childrecordfound
删除外键约束
altertableemp1
dropconstrainte_fk
使用ONDELETECASCADE关键字重建外键（级联删除）不能用alter修改,只能建表的约束的时候用。
ALTERTABLEemp1
ADDCONSTRAINTe_fkFOREIGNKEY(deptno)
REFERENCESdept1(deptno)ONDELETECASCADE
1rowdeleted.
再查看emp1表的deptno
select*fromemp1;
ONDELETECASCADE要慎用，主表中删除一行数据就可能引发从表中大量数据丢失。
SQL>createtableemp100(empnoint,salintcheck(sal>0),commint);
SQL>createtableemp200(empnoint,salint,commint,check(sal>1000));
SQL>altertableemp200addconstrainte_no_ckcheck(empnoisnotnull);
验证
SQL>insertintoemp200values(null,1,1);
insertintoemp200values(null,1,1)
ORA-02290:违反检查约束条件(SCOTT.E_NO_CK)
测试表
CREATETABLEtest2(
pkNUMBERPRIMARYKEY,
fkNUMBER,
col1NUMBER,
col2NUMBER,
CONSTRAINTfk_constraintFOREIGNKEY(fk)REFERENCEStest2,一个表的时候可以省略主键pk
CONSTRAINTck1CHECK(pk>0andcol1>0),
CONSTRAINTck2CHECK(col2>0)
当删除列时,看看会发生什么
ALTERTABLEtest2DROP(col2);//这句可以执行
ALTERTABLEtest2DROP(pk);//这句不能执行，在constraintck1中使用了该列，约束级联问题）
ALTERTABLEtest2DROP(fk);//这句可以执行
ALTERTABLEtest2DROP(col1);//这句不能执行，在constraintck1中使用了该列，约束级联问题）要带上cascadeconstraints才行
ALTERTABLEtest2DROP(pk)cascadeconstraint;//所有与pk列有关的约束统统随该列被删掉。
ALTERTABLEtest1DROP(col1)cascadeconstraints;
CASCADECONSTRAINTS将丢弃在删除列上的唯一键或主键约束。
启用约束:
enablevalidate:启用约束,创建索引,对已有及新加入的数据执行约束.默认的。
enablenovalidate:启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据.约束只对新数据生效。
禁用约束:
disablenovalidate:关闭约束,删除索引,可以对约束列的数据进行修改等操作.约束不生效。
disablevalidate:关闭约束,删除索引,不能对表进行插入/更新/删除等操作,注意是相当于对整个表的readonly设定，不管列上有没有约束.不常用。
更改约束状态是一个数据字典更新，将对所有session有效。
举例：
以scottuser中的emp﹑dept两个表分别复制emp1,dpet1来进行例证.
SQLcreatetableemp1asselect*fromemp;
建立约束
SQL>altertableemp1addconstraintpk_emp1primarykey(empno)
;
SQL>altertabledept1addconstraintpk_dept1primarykey(deptno);
SQL>altertableemp1addconstraintfk_deptno1foreignkey(deptno)referencesdept1(deptno);
SQL>selectconstraint_name,constraint_type,status,validatedfromuser_constraints;
CONSTRAINT_NAMECSTATUSVALIDATED
FK_DEPTNO1RENABLEDVALIDATED
FK_DEPTNORENABLEDVALIDATED
PK_DEPTPENABLEDVALIDATED
PK_EMPPENABLEDVALIDATED
PK_EMP1PENABLEDVALIDATED
PK_DEPT1PENABLEDVALIDATED
6rowsselected.
一般来讲﹐默认的情况下enable时就会validate;disable时就会novalidate;
而这两个不同的动作分开来讲就是:
enable/disable是指是否现在要启动/失效这个约束.
validate/novalidate是指是否对现在的数据进行合法性的檢查.
现实操作中因为有一些违规的操作需要我们处理﹐如改变主细中有外FK关联的表结构﹐truncate表...
所以我们要打断数据库的约束关联后再进行操作。
SQL>altertableemp1disableconstraintFK_DEPTNO1;
Tablealtered
这样我们就可以操作emp1表中的deptno字段而不受外键的限制[无须在dept1表中存在或值为NULL了].
而当我们没有打断这种关联时﹐像下面这些操作都会出错:
SQL>deletefromdept1wheredeptno=10;
deletefromdept1wheredeptno=10
ORA-02292:integrityconstraint(SCOTT.FK_DEPTNO1)violated-childrecordfound
SQL>updateemp1setdeptno=50whereempno=7934;
updateemp1setdeptno=50whereempno=7934
ORA-02291:integrityconstraint(SCOTT.FK_DEPTNO1)violated-parentkeynotfound
此外如果没有disable关联时即使子表中没有资料﹐父表也不能truncate。
因为truncate是一条DDL指令﹐当进行truncate时,它并不会去一条条的记录去检查父中的这条记录在
子表是否已有引用﹐而是去检查这类的constraint_name的status是否为disable﹐否则不能truncate.
SQL>truncatetabledept1;
truncatetabledept1
ORA-02266:unique/primarykeysintablereferencedbyenabledforeignkeys
SQL>altertableemp1enablenovalidateconstraintFK_EMP_DEPT;
SQL>truncatetabledept;
Tabletruncated
前面讲到disable/enable与[novalidate|validate]两种操作就有四种组态.
而因为enablevalidate与disablenovalidate是默认的所以就不以讨论了。
============================================================
以下来说明enablenovalidate这种组态的实用用法
我们常用于当我们在表中输入了一些测试数据﹐而上线时我们并不想去清除这些
违规数据﹐只想从以后开始才执行这些约束时可以用到。
假设已经建立了一个emp1表，也插入了数据，如果有一天想在empno上加入primarykey但是之前有不符合（notnull+unique)约束的，怎样才能既往不咎呢？
createtableemp1asselect*fromemp;(约束并没有考过来）
updateemp1setempno=7788whereempno=7369;(设置一个重号)
altertableemp1addconstraintpk_emp1primarykey(empno);因要检查主键唯一索引，拒绝建立此约束。
任何违反（notnull+unique)的update或insert操作将被拒绝。
altertableemp1addconstraintpk_emp1primarykey(empno)enablenovalidate;（这句话也不行，为什么？原因是唯一索引在捣乱）。
createindexempno_indexonemp1(empno);建一个索引，一定要一个普通索引，不能是唯一索引，普通索引不受unquie的限制）
altertableemp1addconstraintpk_emp1primarykey(empno)enablenovalidate;（这句话可以了）。
从此之后，这个列的DML操作还是要符合（notnull+unique)。
----------------------------------------------------------------------------------------------------------------------
可延迟（deferrable）的约束可以指定为：
1.initiallyimmediate（初始化立即执行）
2.initiallydeferred（初始化延迟执行）
其中InitiallyImmediate意思是每次向表中添加数据,修改数据或是从表中删除数据时.都要检查这个约束.(这与约束默认行为相同).
而InitiallyDeferred.意思是只有事务被提交时才检查这个约束.
1、deferrable必须在简历约束的时候指明否则不可再追加。
2、多个DML组成一个事务，COMMIT时候如果有一个不符合约束，则所有DML都被回退。
3、在定义的时候，initially状态将应用于所有SESSION有效。Immediate和deferred的切换对当前session有效。考点。
在你操作table时，例如insert数据。以主键约束为例
如果有违反主键唯一性的约束时，系统应该报错。
现在你要决定系统什么时候报错。
1，initiallyimmediate在执行sql的时候报错。
2，initiallydeferred在commit的时候报错。
比方说，2个人先后在执行insert同一笔资料。
如果第一种方案，则后面的那个人无法执行sql。
如果第二种方案，则2个人都可以执行sql，但是先commit的人会成功。后commit的人会被提示报错。
可以通过查询User_Constraints表获得当前所有关于约束的系统信息.下面关于约束User_Constraints表常用信息字段说明:
Owner//约束所有者
Constraint_name//约束名称
Constraint_Type//约束类型:
[C:代表Check或NotNull约束.P:主键约束.R:外键约束.U:唯一约束.V:Checkoption束.O:Readonly只读约束]
Table//name——约束定义针对表名
Status_//约束的状态Enable或Disable可用或不可用
Deferrable//是否为延迟约束值为:Deferrable或NotDeferred.
Deferred://是否采用延迟值为:IMMEDIate或Deferred.
SQL>altertableemp1addconstraintfk_emp1foreignkey(deptno)referencesdept1(deptno)deferrable;
SQL>updateemp1setdeptno=50whereempno=7900;(因为不检查约束，可以暂时违反完整性约束）
SQL>insertintoemp1empno=7788whereename='SMITH';（暂时通过，插入记录也只在提交（commit）时检查）
SQL>commit;（这时候检查了，报错！）
考点：一旦有一条DML语句违反了约束，整个提交都将失败，全军覆没。
关于deferrable可延迟，提醒以下几点：
1）约束的默认方式下是：enable/validate和notdefferable。
2）如果创建约束时没有指定deferrable那么无法在后来使约束成为延迟约束的（只有再重建约束时指定它是延迟约束）
3）一个constraint如果被定义成deferrable，那么这个constraints可以在deferred和imediate两种状态间相互转换
4）因为是deferrable,可以使用setconstraint字句了。
SQL>setconstraintfk_emp1deferred;--约束fk_emp1已经进入了临时延迟状态
以上3）和4）合起来就相当于：
SQL>altertableemp1addconstraintfk_emp1foreignkey(deptno)referencesdept1(deptno)deferrableinitiallydeferred
限制数据的存取：用户只能看到基表的部分信息。
使得复杂的查询变得容易：视图中的数据可能来自多个表。
使得数据相对比较独立：从项目开发的角度，模块对应视图，模块包含多个表，模块发生变化后只需修改相应的视图，对应的表的结构无需修改。
代表对同一数据的不同视角：不同部门的员工只能看到本部门的信息。
CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview
[(alias[,alias]...)]
ASsubquery
[WITHCHECKOPTION[CONSTRAINTconstraint]]
[WITHREADONLY];
Selecttextfromuser_views;视图的数据字典。
考点：如果select字句中有表达式（sal+1000），需要给表达式加别名。
Create（orreplace）forceviewview1asselect*fromtest1;
因为基表不存在，执行报错。但是试图已经建立。
对视图where子句进行约束。不允许对限定关键字修改。
SQL>createviewview2asselect*fromempwheredeptno=10withcheckoption;
无法对DEPTNO字段键值进行修改。
禁止对视图执行DML操作
SQL>createviewview3asselect*fromempwheredeptno=10withreadonly;
1）定义视图的查询中列中有函数或表达式要加别名考点
2）只要视图中的数据不是来自基表的原始数据，就尽量不要对该数据做DML操作。考点
比如：createviewV1asselectsal+1000abcfromemp1;则这样的试图不能修改，因为基表emp1中没有abc列。
3）视图的DML操作
含有如下情况，则无法删除视图中的数据:
含有聚组函数
含有GROUPBY子句
含有DISTINCT关键字
含有ROWNUM这个伪列
含有如下情况，不能修改该视图中的数据:
上面提到的任何一种情况。
列是由表达式来进行定义的
含有如下情况，不能增加该视图中的数据:
在基表中包含有NOTNULL约束的列，然而该列并没有在视图中出现
比如，试图只映射了部分字段，不包括NOTNULL字段，如果插入的话，将对基表中的NOTNULL字段补空值，违反约束。
select*fromuser_views
如果建立了视图想查看其中的定义，可以访问如下视图dba_views中的text字段(long型)；
自建一pl/sql过程，参照一下。
declare
v_textdba_views.text%type;
v_namedba_views.view_name%type;
selecttext,view_nameintov_text,v_nameFROMdba_viewsWHEREview_name='V1';
dbms_output.put_line(v_name||'defineis:'||v_text);
sysdba下执行：
setserverouton;
aadba_views.text%type;
selecttextintoaaFROMdba_viewsWHEREOWNER='SCOTT';
dbms_output.put_line(aa);
sys用户看scott的emp:
select*fromscott.emp;
createsynonymsyn1forscott.emp;
select*fromsyn1;(私有同义词只能在当前用户下访问,scott使用不了)
但是创建同义词是要权限的(createsynonym,createpublicsynonym);
createpublicsynonymsyn2for...
（新用户要访问publicsynonym（代表emp表）,需要有访问基表的权限。
select*fromtab;(tab是什么？）；用下面的查看语句:
select*fromall_objectswhereobject_name='TAB';
select*fromall_synonymwheresynonym_name='TAB';
1）私有同义词是基于模式的对象，要么在自己的模式中，要么必须用模式名限定。
比如scott.syn1syn1是同义词，scott是模式名
2）公有同义词不是模式对象，不能用模式名做前缀。（考点）
3）私有和公有同义词同名但指向不同的别名时，私有同义词优先。
For后面的对象不一样的时候，私有优先。
4）同义词被引用的对象（表或视图）被删除，同义词仍然存在，这同视图类似，重新创建对象，下次访问同义词时自动编译。（考点）
结合真题演示
伪列nextval,currval用法下一个值，当前值
CREATESEQUENCEdept_deptno
INCREMENTBY10
STARTWITH50
MAXVALUE100
CYCLE转一圈回来之后，就不从50开始，而从1开始考点。
NOCACHE
第一次要引用一下nextval伪列
selectdept_deptno.nextvalfromdual;
以后就有currval伪列值了。
1）最简单的建立序列只需要createsequence序列名就可以，注意缺省值，起始是1，步长也是1。
2)如果启用cache，缺省只有20个号，经验表明这个数量会不够，可以设置多一些，根据需要10000个也可以。Cache10000
3)cycle其实没有什么意义，因为它使序列发出重复值，这对于基于序列是主键值的用法是个问题。
4)创建序列后可以使用alter命令再进行修改。alter命令和create命令基本相同，只有一点区别：alter命令不能设置起始值。如果要重启该序列，唯一的办法是删除并重新创建它。
5)循环后初始是从1开始的，不管原来的值是如何设的(考点）。
16.1读取外部表的方法
OS下建目录，
$mkdir-p/u01/oradata/timran11g/aaa_dir
chmod777/u01/oradata/timran11g/aaa_dir
建立txt外部表文件，并放到aaa_dir下
假如有如下的数据文件:
t1.txt文件内容：
7566,JONES,2975,20
7698,BLAKE,2850,30
7788,SCOTT,3000,20
7839,KING,9000,10
7902,FORD,3000,20
sql>createdirectorytest_diras'/u01/oradata/timran11g/aaa_dir';
sql>grantread,writeondirectorytest_dirtoscott;
createtabletest_table
(empnoint,
enamechar(20),
salint,
deptnoint)
ORGANIZATIONEXTERNAL
(
TYPEORACLE_LOADER
DEFAULTDIRECTORYtest_dir
ACCESSParameters
RECORDSDELIMITEDBYNEWLINE
badfile'bad_dev.txt'
LOGFILE'log_dev.txt'
FIELDSTERMINATEDBY','说明外部表里每个值之间的分隔符。
MISSINGFIELDVALUESARENULL
(empno,ename,sal,deptno)
LOCATION('t1.txt')
4),进行SELECT操作看是否正确;
SQL>select*fromtest_table
SQL>select*fromtest_tablewheredeptno=10;
查看外部表的两个字典视图
DBA_EXTERNAL_TABLES;
DBA_EXTERNAL_LOCATIONS;
SQL>SELECTOWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFR
FROMDBA_EXTERNAL_TABLES;
第二册部分
第一类insert语句：一次插入一行
1）SQL>createtablea(idint,namechar(10)default'aaa');//name列指定了default值
2）SQL>insertintoavalues(1,'abc');//表a后没有所选列，values必须指定所有字段的值。
3）SQL>insertintoavalues(2,default);//同上，name字段用default占位。
4）SQL>insertintoavalues(3,null);//表a后没有所选列，name字段用null占位。
5）SQL>insertintoa(id)values(4);//表a后有选择字段，未选定的字段如果指定了default,则以default的值代替null
6）SQL>insertinto(selectidfroma)values(5);//这种形式本质同上，只不过表a的形式以结果集代之。
NOTE：insert命令会有约束的问题，不符合约束条件的insert不能成功。
1abc
2aaa
4aaa
5aaa
7）insertWITHCHECKOPTION的用法
SQL>insertinto(selectidfromawhereid<100WITHCHECKOPTION)values(20);
20aaa
SQL>rollback;
SQL>insertinto(selectidfromawhereid<100WITHCHECKOPTION)values(101);
insertinto(selectidfromawhereid<100WITHCHECKOPTION)values(101)
ORA-01402:viewWITHCHECKOPTIONwhere-clauseviolation
这样的语法看起来很特殊，其实就是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。
看看这句话的另一种情况：
SQL>insertinto(selectnamefromawhereid<100WITHCHECKOPTION)values('NBA');
insertinto(selectnamefromawhereid<100WITHCHECKOPTION)values('NBA')
note:上例是想说明如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入（考点）。
SQL>insertinto(selectid,namefromawhereid<100WITHCHECKOPTION)values(10,'tim');
SQL>insertinto(selectnamefromawhereid<100)values('NBA');//不加WITHCHECKOPTION则在插入时不会检查。
SQL>insertinto(selectnamefromawhereid<100)values('NBA');
一次插入多行语法上去掉了values选项。
SQL>createtablebasselect*fromawhere1>2;//建立一个空表b。结构来自a表，where1>2使没有符合的记录被筛选出来.
SQL>insertintobselect*fromawherename='aaa';//插入的是结果集，注意没有values选项。
SQL>insertintob(id)selectidfromawhereidin(1,3);//使用子查询（结果集）插入，对位，注意b表没有default。复制表结构，除了非空约束、字段名、字段类型、字段长度之外，其他都复制不过来。
一条INSERT语句可以完成向多张表的插入任务。
insertall与insertfirst
1.创建表T并初始化测试数据，此表作为数据源。
createtablet(xnumber(10),yvarchar2(10));
insertintotvalues(1,'a');
insertintotvalues(2,'b');
insertintotvalues(3,'c');
insertintotvalues(4,'d');
insertintotvalues(5,'e');
insertintotvalues(6,'f');
2.查看表T的数据
SQL>select*fromt;
XY
3c
5e
6f
3.创建表T1和T2，作为我们要插入的目标表。
SQL>createtablet1asselect*fromtwhere0=1;
Tablecreated.
SQL>createtablet2asselect*fromtwhere0=1;
unconditionalinsertall（无条件insertall)
1）完成INSERTALL插入
SQL>insertallintot1intot2select*fromt;
12rowscreated.
这里之所以显示插入了12条数据，实际上表示在T1表中插入了6条，T2表插入了6条，一共是12条数据。
2）验证T1表中被插入的数据。
SQL>select*fromt1;
3）验证T2表中被插入的数据。
SQL>select*fromt2;
OK，完成INSERTALL命令的使命。
conditionalinsertfirst(有条件insertfirst)
1）清空表T1和T2
SQL>deletefromt1;truncatetablet1;
SQL>deletefromt2;truncatetablet2;
2）完成INSERTFIRST插入
SQL>insertfirstwhenx>=5thenintot1whenx>=2thenintot2select*fromt;
5rowscreated.
处理逻辑是这样的，首先检索T表查找X列值大于等于5的数据（这里是“5，e”和“6，f”）插入到T1表，然后将前一个查询中出现的数据排除后再查找T表，找到X列值大于等于2的数据再插入到T2表（这里是“2，b”、“3，c”和“4，d”）。注意INSERTFIRST的真正目的是将同样的数据只插入一次。
3）验证T1表中被插入的数据。
4）验证T2表中被插入的数据。
5)为真实的反映“数据只插入一次”的目的，我们把条件颠倒后再插入一次。
SQL>deletefromt1;
SQL>deletefromt2;
SQL>insertfirstwhenx>=2thenintot1whenx>=5thenintot2select*fromt;
OK，目的达到，可见满足第二个条件的数据已经包含在第一个条件里，所以不会有数据插入到第二张表。
同样的插入条件，我们把“INSERTFIRST”换成“INSERTALL”，对比一下结果。
5rowsdeleted.
0rowsdeleted.
SQL>insertallwhenx>=2thenintot1whenx>=5thenintot2select*fromt;//conditionalinsertall
7rowscreated.
createtablesales_source_data(
employee_idnumber(6),
week_idnumber(2),
sales_monnumber(8,2),
sales_tuenumber(8,2),
sales_wednumber(8,2),
sales_thurnumber(8,2),
sales_frinumber(8,2)
insertintosales_source_datavalues(176,6,2000,3000,4000,5000,6000);
createtablesales_info(
weeknumber(2),
salesnumber(8,2)
看上面的表结构,现在将要sales_source_data表中的数据转换到sales_info表中,这种情况就需要使用旋转Insert
示例如下:
insertall
intosales_infovalues(employee_id,week_id,sales_mon)
intosales_infovalues(employee_id,week_id,sales_tue)
intosales_infovalues(employee_id,week_id,sales_wed)
intosales_infovalues(employee_id,week_id,sales_thur)
intosales_infovalues(employee_id,week_id,sales_fri)
selectemployee_id,week_id,sales_mon,sales_tue,
sales_wed,sales_thur,sales_fri
fromsales_source_data;
从该例子可以看出,所谓旋转Insert是无条件insertall的一种特殊应用,但这种应用被oracle官方,赋予了一个pivotinginsert的名称,即旋转insert.
把数据从一个表复制到另一个表，插入新数据或替换掉老数据是每一个ORACLEDBA都会经常碰到的问题。
在ORACLE9i以前的年代，我们要先查找是否存在老数据，如果有用UPDATE替换，否则用INSERT语句插入，其间少不了还有一些标记变量等等，繁琐的很。
ORACLE9i后专为这种情况提供了MERGE语句，使这一工作变得异常轻松，MERGE命令能够在一个SQL语句中对一个表同时执行inserts和updates操作。
MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.
Oracle10g中MERGE有如下一些改进：
1、UPDATE或INSERT子句是可选的
2、UPDATE和INSERT子句可以加WHERE子句
3、ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表
4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行
首先创建示例表：
createtablePRODUCTS
PRODUCT_IDINTEGER,
PRODUCT_NAMEVARCHAR2(30),
CATEGORYVARCHAR2(30)
insertintoPRODUCTSvalues(1501,'VIVITAR35MM','ELECTRNCS');
insertintoPRODUCTSvalues(1502,'OLYMPUSIS50','ELECTRNCS');
insertintoPRODUCTSvalues(1600,'PLAYGYM','TOYS');
insertintoPRODUCTSvalues(1601,'LAMAZE','TOYS');
insertintoPRODUCTSvalues(1666,'HARRYPOTTER','DVD');
createtableNEWPRODUCTS
insertintoNEWPRODUCTSvalues(1502,'OLYMPUSCAMERA','ELECTRNCS');
insertintoNEWPRODUCTSvalues(1601,'LAMAZE','TOYS');
insertintoNEWPRODUCTSvalues(1666,'HARRYPOTTER','TOYS');
insertintoNEWPRODUCTSvalues(1700,'WAITINTERFACE','BOOKS');
SQL>select*fromproducts;
PRODUCT_IDPRODUCT_NAMECATEGORY
----------------------------------------------------------------------
1501VIVITAR35MMELECTRNCS
1502OLYMPUSIS50ELECTRNCS
1600PLAYGYMTOYS
1601LAMAZETOYS
1666HARRYPOTTERDVD
SQL>select*fromnewproducts;
1502OLYMPUSCAMERAELECTRNCS
1666HARRYPOTTERTOYS
1700WAITINTERFACEBOOKS
1、可省略的UPDATE或INSERT子句
在Oracle9i,MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle10g,你可以省略UPDATE或INSERT子句中的一个.下面的例子根据
表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:
SQL>MERGEINTOproductsp
USINGnewproductsnp
ON(p.product_id=np.product_id)
WHENMATCHEDTHEN
UPDATE
SETp.product_name=np.product_name,
p.category=np.category;
3rowsmerged.
在上面例子中,MERGE语句影响到是产品id为1502,1601和1666的行.它们的产品名字和种类被更新为表newproducts中的值.下面例子省略UPDATE
子句,把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中,对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理.从这个例子你能看到
PRODUCT_ID=1700的行被插入到表PRODUCTS中.
WHENNOTMATCHEDTHEN
INSERT
VALUES(np.product_id,np.product_name,
np.category);
1rowmerged.
select*fromproducts;
2、新增加的DELETE子句
Oracle10g中的MERGE提供了在执行数据操作时清除行的选项.你能够在WHENMATCHEDTHENUPDATE子句中包含DELETE子句.DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETEWHERE条件但不匹配ON条件的行不会被从表中删除.
下面例子验证DELETE子句.我们从表NEWPRODUCTS中合并行到表PRODUCTS中,但删除category为ELECTRNCS的行.
p.category=np.category
DELETEWHERE(p.category='ELECTRNCS')
VALUES(np.product_id,np.product_name,np.category);
4rowsmerged.
产品ID为1502的行从表PRODUCTS中被删除,因为它同时匹配ON条件和DELETEWHERE条件.产品ID为1501的行匹配DELETEWHERE条件但不匹配ON条件
,所以它没有被删除.产品ID为1700的行不匹配ON条件,所以被插入表PRODUCTS.产品ID为1601和1666的行匹配ON条件但不匹配DELETEWHERE条件,所
以被更新为表NEWPRODUCTS中的值.
好处：
1、使用with语句，可以避免在select语句中重复书写相同的语句块。
2、with语句将该子句中的语句块执行一次并存储到用户的临时表空间中。
3、使用with语句可以提高查询效率。
举例：一个with语句完成三个动作
建立一个dept_costs，保存每个部门的工资总和，
建立一个avg_cost，根据dept_costs求出所有部门总工资的平均值，
最后显示出部门总工资值小于部门总工资平均值的那些部门的信息(dname)。
WITH
dept_costsAS(
SELECTd.dname,SUM(e.sal)ASdept_total
FROMempe,deptd
WHEREe.deptno=d.deptno
GROUPBYd.dname),
avg_costAS(
SELECTSUM(dept_total)/COUNT(*)ASdept_avgFROMdept_costs)
SELECT*FROMdept_costs
WHEREdept_total<
(SELECTdept_avgFROMavg_cost)
ORDERBYdname
DNAMEDEPT_TOTAL
------------------------
ACCOUNTING8750
SALES9400
可以分三个部分来看:
第一AS建立一个dept_costs，保存每个部门的工资总和
第二个AS建立一个avg_cost，根据刚才的dept_costs求出所有部门总工资的平均值
最后，SELECT*FROM...显示部门总工资值小于部门总工资平均值的那些部门的信息
模式是一种逻辑结构，它对应于用户，每建一个用户就有一套模式与之对应。
我们通常说对象的唯一标识符是前缀为模式名的对象名称，那么对象的命名需要有名称空间的概念
名称空间定义了一组对象类型，在这个组中所有名称都必须由模式和名称唯一标识，不同的名称空间中的对象可以共享相同的名称。
1）同一模式下的相同对象类型是不可以重名的。
2）表，视图，序列，同义词是不同的对象，它们在一个名称空间里也是不可以重名的，比如scott下不可以使一个表名和一个视图名同名。
3）索引、约束有自己的名称空间，所以在scott模式下，可以有表A,索引A和约束A共存。
考点：在同一个模式中，表、视图和同义词不能同名。
语法：createtable[schema.]tableassubquery;
如：createtable表2asselect*from表1;是最简洁的复制表的命令，但是要注意，索引不会被复制到表2，约束只有notnull约束能够带到表2里来，如果有default也不会被复制表2。
原理：清除掉字典信息（撤消存储空间），不可恢复。
Oracle推荐：
可以使用SETUNUSED选项标记一列（或多列），使该列不可用。不可恢复
使用DROPUNUSEDcolumn选项删除被被标记为不可用的列。
语法：
ALTERTABLEtableSETUNUSED(COLlist多个)或者ALTERTABLEtableSETUNUSEDCOLUMNcol单个;
ALTERTABLEtableDROPUNUSEDCOLUMNS;
答：视图和同义词会失效，其中同义词再次引用即可重新编译，但是视图重新编译会报错，约束和索引会被自动删除。
SETUNUSED不会真地删除字段。
除了altertabledropfield外，也可以
altertablesetunusedfield;
altertabledropunusedcolumns;
setunused系统开销比较小，速度较快，所以可以先setunuased，然后在系统负载较小时，再drop。如系统负载不大，也可以直接drop。
不管用何种方法，都不会收回空间。
Oracle数据库中的rollup配合groupby命令使用，可以提供信息汇总功能（与"小计"相似）
CUBE，也是GROUPBY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。
示例如下：
SQL>selectjob,deptno,salfromemp;
JOBDEPTNOSAL
CLERK20800
SALESMAN301600
SALESMAN301250
MANAGER202975
MANAGER302850
MANAGER102450
ANALYST203000
PRESIDENT105000
SALESMAN301500
CLERK201100
CLERK30950
CLERK101300
14rowsselected.
SQL>selectjob,deptno,sum(sal)total_salfromempgroupbyrollup(job,deptno);
JOBDEPTNOTOTAL_SAL
CLERK201900
CLERK4150
ANALYST206000
ANALYST6000
MANAGER8275
SALESMAN305600
SALESMAN5600
PRESIDENT5000
29025
15rowsselected.
SQL>selectjob,deptno,sum(sal)total_salfromempgroupbycube(job,deptno);
108750
2010875
309400
18rowsselected.
可以看出,用了rollup的groupby子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，
同时也会对聚合列生成一个合计列。
selectdeptno,job,sum(sal)fromempgroupbydeptno,job;
会对每一个不同的dept,job生成一行独立的结果。
而selectdeptno,job,sum(sal)fromempgroupbyrollup(deptno,job);
的结果中除了上述的结果结果之外，还会对每一个deptno进行一个小结，并单独生成一行，除此之外还会对所有的sal求和并生成一列。
这里的groupby后面我们仅仅接了2列，实际上我们可以使用更多列的，这样的话oracle就会以从右向左的方式来进行逐个小结。
这里需要注意的是使用了groupby和rollup后，其后面的列要用括号括起来，否则将会出现ORA-00933:SQL命令未正确结束的错误。
看看grouping、grouping_id函数是什么？
GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
grouping_id函数
可以返回0,1,2,3...可以分别表示小计，合计等信息。
SQL>selectjob,deptno,sum(sal)total_sal,grouping(job)job_grp,grouping(deptno)deptno_grp,grouping_id(job,deptno)total_grpfrom
empgroupbycube(job,deptno);
JOBDEPTNOTOTAL_SALJOB_GRPDEPTNO_GRPTOTAL_GRP
-----------------------------------------------------------
29025113
108750102
2010875102
309400102
CLERK4150011
CLERK101300000
CLERK201900000
CLERK30950000
ANALYST6000011
ANALYST206000000
MANAGER8275011
MANAGER102450000
MANAGER202975000
MANAGER302850000
SALESMAN5600011
SALESMAN305600000
PRESIDENT5000011
PRESIDENT105000000
已选择18行。
但是我们大多数情况下需要在查询的结果集的汇总列加上“合计”，怎么办呢？用grouping和grouping_id函数，然后再用decode函数判断一下是否为空
就可以了
SQL>selectgrouping_id(job,deptno)asgroup_col,sum(sal)total_salfromempgroupbyrollup(job,deptno);
GROUP_COLTOTAL_SAL
01300
01900
0950
14150
06000
16000
02450
02975
02850
18275
05600
15600
05000
329025
已选择15行。
SQL>selectdecode(grouping_id(job,deptno),1,'合计',job||deptno)asgroup_col,sum(sal)total_salfromempgroupbyrollup(job,deptno);
合计4150
合计6000
合计8275
合计5600
合计5000
ORACLE是一个关系数据库管理系统,它用表的形式组织数据,在某些表中的数据还呈现出树型结构的联系。例如，我们现在讨论雇员信息表EMP，其中含
有雇员编号（EMPNO）和经理（MGR）两列，通过这两列反映出来的就是雇员之间领导和被领导的关系。他们之间的这种关系就是一种树结构。
图1.1EMP表树结构图
7839
|
------------------------------------------------------------------------------------------------
|||
756676987782
---------------------------------------------------------
||||||||
77887902749975217654784479007943--------
||
78767369
扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
第一步：从根节点开始；
第二步：访问该节点；
第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
第四步：若该节点为根节点，则访问完毕，否则执行第五步；
第五步：返回到该节点的父节点，并执行第三步骤。
总之：扫描整个树结构的过程也即是中序遍历树的过程。
树结构的数据存放在表中，数据之间的层次关系即父子关系，在表的每一行中都有一个表示父节点的MGR（除根节点外）。
在SELECT命令中使用CONNECTBY和STARTWITH子句可以查询表中的树型结构关系。其命令格式如下：
SELECT...
CONNECTBY{PRIOR列名1=列名2|列名1=PRIOR列名2}
[STARTWITH]；
其中：CONNECTBY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIOR运算符必须放置在连接关系的两列中某一个的前面。PRIOR运算符在的一侧表示父节点，另一侧表示子节点，从而确定查找树结构时的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。STARTWITH子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
例1以树结构方式显示EMP表的数据。
SQL>selectempno,ename,mgr
connectbypriorempno=mgr
startwithempno=7839
EMPNOENAMEMGR
7839KING
7566JONES7839
7788SCOTT7566
7876ADAMS7788
7902FORD7566
7369SMITH7902
7698BLAKE7839
7499ALLEN7698
7521WARD7698
7654MARTIN7698
7844TURNER7698
7900JAMES7698
7782CLARK7839
7934MILLER7782
仔细看empno这一列输出的顺序，就是上图树状结构每一条分支（从根节点开始）的结构。
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
PRIOR被置于CONNECTBY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式
。如：
CONNECTBYPRIOREMPNO=MGR//父节点与子节点的关系，在表内存储的数据上体现
PIROR运算符被置于CONNECTBY子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向
上的方式。例如：
CONNECTBYEMPNO=PRIORMGR
在这种方式中也应指定一个开始的节点。
例2从SMITH节点开始自底向上查找EMP的树结构。
connectbyempno=priormgr
startwithempno=7369
在这种自底向上的查找过程中，只有树中的一枝被显示，这是因为，在树结构中每一个节点只允许有一个父节点，其查找过程是从开始节点起，找到其
父节点，再由其父节点向上，找父节点的父节点。这样一直找到根节点为止，结果就是树中一枝的数据。
备注：例2的另外一种写法
SQL>SELECTEMPNO,ENAME,MGR
FROMEMP
CONNECTBYPRIORMGR=EMPNO
STARTWITHEMPNO=7369
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构
树的一枝。
例3查找7566(JONES)直接或间接领导的所有雇员信息。
SQL＞SELECTEMPNO,ENAME,MGR
CONNECTBYPRIOREMPNO=MGR
STARTWITHEMPNO=7566
STARTWITH不但可以指定一个根节点，还可以指定多个根节点。
例4查找由FORD和BLAKE领导的所有雇员的信息。
STARTWITHENAMEIN('FORD','BLAKE')
在自底向上查询树结构时,也要指定一个开始节点，以此开始向上查找其父节点，直至找到根节点，其结果将是结构树中的一枝数据。
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。
在查询中，可以使用伪列LEVEL显示每行数据的有关层次。LEVEL将返回树型结构中当前节点的层次。
伪列LEVEL为数值型，可以在SELECT命令中用于各种计算。
例5使用LEVEL改变查询结果的显示形式。
SQL>COLUMNLEVELFORMATA20
SQL>SELECTLPAD(LEVEL,LEVEL*3,'')
as"LEVEL",EMPNO,ENAME,MGR
STARTWITHENAME='KING'
LEVELEMPNOENAMEMGR
--------------------------------------------------
17839KING
27566JONES7839
37788SCOTT7566
47876ADAMS7788
37902FORD7566
47369SMITH7902
27698BLAKE7839
37499ALLEN7698
37521WARD7698
37654MARTIN7698
37844TURNER7698
37900JAMES7698
27782CLARK7839
37934MILLER7782
在SELECT使用了函数LPAD，该函数表示以LEVEL*3个空格进行填充，由于不同行处于不同的节点位置，具有不同的LEVEL值，因此填充的空格数将根据各
自的层号确定，空格再与层号拼接，结果显示出这种层次关系。
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，
但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
WHEREENAME!='SCOTT'
13rowsselected.
在这个查询中，仅剪去了树中单个节点SCOTT。若希望剪去树结构中的某个分支，则要用CONNECTBY子句。CONNECTBY子句是限定树型结构中的整个分
支，既要剪除分支上的单个节点，也要剪除其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
例8．显示KING领导下的全体雇员信息，除去SCOTT领导的一支。
ANDENAME!='SCOTT'
12rowsselected
这个查询结果就与例7不同，除了剪去单个节点SCOTT外，还将SCOTT的子节点ADAMS剪掉，即把SCOTT这个分支剪掉了。
当然WHERE子句可以和CONNECTBY子句联合使用，这样能够同时剪掉单个节点和树中的某个分支。
例9．显示KING领导全体雇员信息，除去雇员SCOTT，以及BLAKE领导的一支。
这个留给大家实践吧：）
像在其它查询中一样，在树结构查询中也可以使用ORDERBY子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。
例10以EMPNO的顺序显示树结构EMP中的数据。
ORDERBYEMPNO
在使用SELECT语句来报告树结构报表时应当注意，CONNECTBY子句不能作用于出现在WHERE子句中的表连接。如果需要进行连接，可以先用树结构建立
一个视图，再将这个视图与其他表连接，以完成所需要的查询。
TIMESTAMPWITHTIMEZONE
TIMESTAMPWITHLOCALTIMEZONE
1）Database的timezone可以在创建数据库的时候指定，如：
CREATEDATABASEdb01
...
SETTIME_ZONE='+08:00';
或者在数据库创建之后通过alterdatabase语句修改，但是只有重启数据库后有效：
ALTERDATABASESETTIME_ZONE='+08:00';
查看数据库时区信息：
SQL>selectdbtimezonefromdual;
DBTIME
+08:00
2）session的timezone可以简单通过altersession语句修改：
ALTERSESSIONSETTIME_ZONE='+08:00';
查看session时区信息：
SQL>selectsessiontimezonefromdual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
比如2005-4-614:00:00.000并不能说清楚到底这是日本的下午2点还是中国的下午两点。
假设有一个onlinemeetingsystem
DB服务器在英国dbtimezone(+0:00)[selectdbtimezonefromdual;]
一个客户端c-cn在中国sessiontimezone(+8:00)[selectsessiontimezonefromdual;]
一个客户端c-jp在日本sessiontimezone(+9:00)[selectsessiontimezonefromdual;]
管理客户端c-en在英国sessiontimezone(+0:00)[selectsessiontimezonefromdual;]
===================================================
Timestamp不能（没有）包含任何时区信息
DB有TABLE定义如下：
createtablemeeting_table1(idnumber(10)primarykey,ctimetimestamp);
中国用户插入了第一个会议，早上8点开会
insertintomeeting_table1values(1,to_timestamp('2005-06-298:00:00.0','yyyy-mm-ddhh24:mi:ss.ff'));
日本用户也插入了第二个会议，早上8点开会
(如果是中文字符集也可以使用下列格式转换一下：
insertintomeeting_table1values(2,to_timestamp('2005-06-298:00:00.0','yyyy-mm-ddhh24:mi:ss.ff'));
英国的管理员查询一下这张表，发现两个会议是同时的，
IDCTIME
12005-06-2908:00:00,000000
22005-06-2908:00:00,000000
而实际上应该日本的会议比中国的早一个小时。
英国的管理员如果想参加2号会议的话，他到底该几点去呢？
八点？0点？前一天的晚上11点？数据库完全不能给他一个明确的答复。
c-cn,c-jp来查询也都得到相同的模糊结果：
Timestampwithtimezone显式包含时区信息
createtablemeeting_table2(idnumber(10)primarykey,ctimetimestampwith
timezone);
中国、日本用户同样插入上例中的两个会议
英国的管理员查询表时，返回的结果就清晰多了：
select*frommeeting_table2;
12005-06-2908:00:00,000000+08:00
22005-06-2908:00:00,000000+09:00
他可以知道meeting2是在东九区的早上八点开始的，去参加的话，
前一天晚上11：00他就要接进webmeeting了。
c-cn,c-jp来查询也都与c-en结果相同
结果都包含时区信息，所以是精确的，不可能被混淆。
Timestampwithlocaltimezone隐式包含时区信息
客户session里面时区的不同自动转换：
*插入时，从客户端的时区转到数据库时区
*显示时，从数据库时区转到客户端的时区
createtablemeeting_table3(idnumber(10)primarykey,ctimetimestampwith
localtimezone);
c-cn,c-jp同样插入上例中的两个会议
c-en的查询结果：
12005-06-2900:00:00,000000
22005-06-2823:00:00,000000
c-cn的查询结果：
22005-06-2907:00:00,000000
c-jp的查询结果：
12005-06-2909:00:00,000000
INTERVAL"2-6"YEARTOMONTH
下面的例子表示3天12个小时30分钟6.7秒：
INTERVAL"312:30:06.7"DAYTOSECOND(1)
因为有精度问题，相对来讲，INTERVALDAYTOSECOND比INTERVALYEARTOMONTH要复杂一些
SQL>selectsysdate,sysdate+numtoyminterval(1,'month'),sysdate+numtoyminterval(1,'year')fromdual;
SYSDATESYSDATE+NUMTOYMINTESYSDATE+NUMTOYMINTE
2012-07-0908:18:592012-08-0908:18:592013-07-0908:18:59
SQL>selectsysdate,sysdate+numtodsinterval(1,'day'),sysdate+numtodsinterval(1,'second')fromdual;
SYSDATESYSDATE+NUMTODSINTESYSDATE+NUMTODSINTE
2012-07-0909:09:062012-07-1009:09:062012-07-0909:09:07
1，REGEXP_LIKE：与LIKE的功能相似
2，REGEXP_INSTR：与INSTR的功能相似
3，REGEXP_SUBSTR：与SUBSTR的功能相似
4，REGEXP_REPLACE：与REPLACE的功能相似
它们在用法上与OracleSQL函数LIKE、INSTR、SUBSTR和REPLACE用法相同，
但是它们使用POSIX正则表达式代替了老的百分号（%）和通配符（_）字符。
'^'匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$'匹配输入字符串的结尾位置。如果设置了RegExp对象的Multiline属性，则$也匹配'\n'或'\r'。
'.'匹配除换行符之外的任何单字符。
''匹配前面的子表达式零次或一次。
'+'匹配前面的子表达式一次或多次。
'*'匹配前面的子表达式零次或多次。
'|'指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
'()'标记一个子表达式的开始和结束位置。
'[]'标记一个中括号表达式。
'{m,n}'一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
\num匹配num，其中num是一个正整数。对所获取的匹配的引用。
[[:alpha:]]任何字母。
[[:digit:]]任何数字。
[[:alnum:]]任何字母和数字。
[[:space:]]任何白字符。
[[:upper:]]任何大写字母。
[[:lower:]]任何小写字母。
[[:punct:]]任何标点符号。
[[:xdigit:]]任何16进制的数字，相当于[0-9a-fA-F]。
各种操作符的运算优先级
\转义符
(),(:),(=),[]圆括号和方括号
*,+,,{n},{n,},{n,m}限定符
^,$,anymetacharacter位置和顺序
*/
--创建表
createtablefzq(idvarchar(4),valuevarchar(10));
--数据插入
insertintofzqvalues('1','1234560');
insertintofzqvalues('2','1234560');
insertintofzqvalues('3','1b3b560');
insertintofzqvalues('4','abc');
insertintofzqvalues('5','abcde');
insertintofzqvalues('6','ADREasx');
insertintofzqvalues('7','12345');
insertintofzqvalues('8','adcde');
insertintofzqvalues('9','adc,.de');
insertintofzqvalues('10','1B');
insertintofzqvalues('10','abcbvbnb');
insertintofzqvalues('11','11114560');
insertintofzqvalues('11','11124560');
--regexp_like
--查询value中以1开头60结束的记录并且长度是7位
select*fromfzqwherevaluelike'1____60';
select*fromfzqwhereregexp_like(value,'1....60');
--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。
--使用like就不是很好实现了。
select*fromfzqwhereregexp_like(value,'1[0-9]{4}60');
--也可以这样实现，使用字符集。
select*fromfzqwhereregexp_like(value,'1[[:digit:]]{4}60');
--查询value中不是纯数字的记录
select*fromfzqwherenotregexp_like(value,'^[[:digit:]]+$');
--查询value中不包含任何数字的记录。
select*fromfzqwhereregexp_like(value,'^[^[:digit:]]+$');
--查询以12或者1b开头的记录.不区分大小写。
select*fromfzqwhereregexp_like(value,'^1[2b]','i');
--查询以12或者1b开头的记录.区分大小写。
select*fromfzqwhereregexp_like(value,'^1[2B]');
--查询数据中包含空白的记录。
select*fromfzqwhereregexp_like(value,'[[:space:]]');
--查询所有包含小写字母或者数字的记录。
select*fromfzqwhereregexp_like(value,'^([a-z]+|[0-9]+)$');
--查询任何包含标点符号的记录。
select*fromfzqwhereregexp_like(value,'[[:punct:]]');
22.5REGEXP_REPLACE(字符串替换函数)
REPLACE函数是用另外一个值来替代串中的某个值。例如，可以用一个匹配数字来替代字母的每一次出现。REPLACE的格式如下所示：
原型：regexp_replace(x,pattern[,replace_string[,start[,occurence[match_option]]]])
每个参数的意思分别是：
x待匹配的函数
pattern正则表达式元字符构成的匹配模式
replace_string替换字符串
start开始位置
occurence匹配次数
举例来讲：
SQL>selectregexp_replace('helloeverybody,047courseswillbeoversoon,thanks.','b[[:alpha:]]{3}','one')fromdual;