ocp11g培训内部教材_051课堂笔记(047)_SQL

OCP 051课堂笔记

OCP 051课堂笔记... 1

第一章、Oracle命令类别：... 4

第二章、SQL的基本函数... 4

2.1 单行函数与多行函数... 4

2.2 单行函数的几种类型... 4

第三章、SQL的数据类型... 10

3.1 四种基本的常用数据类型... 10

3.2 数据类型的转换... 12

第四章、WHERE子句中数据的比较和运算... 15

4.1 隐式比较与显式比较：... 15

4.2 运算符优先级：... 15

4.3 用BETWEEN AND操作符来查询出在某一范围内的行. 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.3 GROUP BY创建数据组... 20

5.4 分组函数的嵌套... 20

第六章、数据限定与排序... 21

6.1 简单查询语句执行顺序... 21

6.2 排序(order by) 21

6.3 空值（null) 22

第七章、复杂查询之一：多表连接技术... 26

7.1 简单查询的解析方法：... 26

7.2 多表连接... 26

7.3 集合运算... 32

7.4 集合运算中只要类型能匹配上就可以连接... 34

7.5 关于order by 使用别名排序的问题：... 35

第八章、复杂查询(下）：子查询与多列子查询... 37

8.1 单行子查询采用单行比较运算符（>，<，=，<>) 37

8.2 在多行子查询中使用all 38

8.3 在多行子查询中使用any. 39

8.4 在多行子查询中使用in. 39

8.5 多列子查询多行多列... 40

8.6 非成对比较多列子查询... 43

8.7 not in 在子查询中的空值问题：... 43

8.8 from子句中使用子查询(这个也叫内联视图）... 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.3 Oracle 的事务保存点功能... 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 单行insert 90

17.2 多行insert 92

17.3 Multitable insert 93

第十八章 DML语句-MERGE. 98

第十九章几个语句的用法... 102

19.1 with语句... 102

19.2 DDL操作及模式对象... 103

19.3 group by rollup，group by cube,以及grouping的用法... 104

第二十章 ORACLE分层查询start with&connect by. 109

20.1 树结构查询... 109

20.2 树结构的描述... 110

20.3 关于PRIOR. 111

20.4 定义查找起始节点... 112

20.5 使用LEVEL. 114

20.6 节点和分支的裁剪... 115

20.7 排序显示... 117

第二十一章 Oracle 时间类型及Timezone. 118

21.1 Timezone引入的背景... 118

21.2 模拟北京、东京、伦敦三地的时区，进一步理解Timezone。... 119

21.3（INTERVAL YEAR TO MONTH）和（INTERVAL DAY TO SECOND）时间间隔数据类型 121

21.4 关于numtoyminterval函数和numtodsinterval函数... 122

第二十二章正则表达式... 123

22.1 ORACLE中的支持正则表达式的函数主要有下面四个：... 123

22.2 POSIX 正则表达式由标准的元字符（metacharacters）所构成：... 123

22.3 字符簇：... 124

22.4 Oracle REGEXP_LIKE介绍和例子... 124

第一章、Oracle命令类别：

数据操纵语言：DML: select; insert; delete; update. 对表、试图操作查询、插入、删除、更新。

数据定义语言：DDL: create; alter; drop; truncate,rename. 对数据库操作创建表、修改表、删除表、删除表内容保留表结构。

Delete 与 truncate 的区别。

事务控制语言：TCL: commit; savepoint; rollback. 保存断点

数据控制语言：DCL: grant; revoke. 赋予权限、删除权限。

第二章、SQL的基本函数

2.1 单行函数与多行函数

是指函数一次能处理表行的行数，函数总是接受零个或多个输入参数，但总是返回一个预定数据类型的结果。

单行函数：查询对每一行数据都返回一个结果。

SQL>select empno,lower(ename) from emp;

多行函数：对多数据行的群组进行操作，并且每组返回一个结果。（典型的是聚合函数）

SQL>select sum(sal) from emp;

2.2 单行函数的几种类型

2.2.1 字符函数

lower('SQL Course')----->sql course 返回小写

upper('sql course')----->SQL COURSE 返回大写

initcap('SQL Course')-----> Sql Course 返回首字母大写

concat('good','string')---->good string 拼接 //只能拼接2个字符串 多个字符嵌套有没有限制？

substr('String',1,3)---->str 从第1位开始截取3位数

SQL> select substr('qsedr456',3,5) from dual;

SUBST

-----

edr45

instr('t#i#m#r#a#n#','#',3) --->从第3位起始找第一次出现#字符在那个绝对位置

SQL> select instr('123#45#2222','#',1) from dual;

INSTR('123#45#2222','#',1)

--------------------------

length('String')---->6 长度

lpad('first',10,'$')左填充 ——> $$$$$first

rpad(676768,10,'*')右填充 ——> 676768****

2.2.2 数值函数

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> select round(45.96,1) from dual;

ROUND(45.96,1)

--------------

trunc 对指定的值取整 trunc(p,s)

trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9

trunc(45.923),结果,45 不写位数默认为0

trunc(45.923, -1),结果, 40

mod 返回除法后的余数

SQL> select mod(100,12) from dual;

MOD(100,12)

-----------

2.2.3 日期函数

因为日期在oracle里是以数字形式存储的，所以可对它进行加减运算，计算是以天为单位。

缺省格式：DD-MON-YY.

可以表示（公元前）4712 至（公元）9999

还有一种表示日期的格式 DD-MON-RR 02-03-13 即1913 03.02。02-03-51即2051 03.02 ?????

时间格式

SQL>select to_date('2003-11-04 00:00:00' ,'YYYY-MM-DD HH24:Mi:ss') FROM dual;

MONTHS_BETWEEN //计算两个日期之间的月数

SQL>select months_between('1994-04-01','1992-04-01') mm from dual;

考点：很容易认为单行函数返回的数据类型与函数类型一致，那是对于数字函数类型而言是这样，但字符和日期函数可以返回任何数据类型的值。比如

instr是函数是字符型的，months_between函数是日期型的，但它们返回的都是数值，是一个数,不是字符或日期。

ADD_MONTHS //给日期增加月份

SQL>select add_months('1992-03-01',4) am from dual;

LAST_DAY //日期当前月份的最后一天

SQL>select last_day('1989-03-28') l_d from dual;

ROUND(p,s)，TRUNC(p,s)在日期中的应用，如何舍入要看具体情况，s是MONTH按30天计，应该是14舍15入，s是YEAR则按6舍7入计算。 S是year时候，如果月份是7月，则四舍五入 ?????? 比如，日期是1981-07-02 round之后结果是1982-01-01

SQL> select employee_id,hire_date,round(hire_date,'year') as round from employees where employee_id=194;

EMPLOYEE_ID HIRE_DATE ROUND

----------- ------------------- -------------------

194 1998-07-01 00:00:00 1999-01-01 00:00:00

SQL>

SELECT empno, hiredate,

round(hiredate,'MONTH') AS round,

trunc(hiredate,'MONTH') AS trunc

FROM emp

WHERE empno=7788;

SQL>

SELECT empno, hiredate,

round(hiredate,'YEAR') AS round,

trunc(hiredate,'YEAR') AS trunc

FROM emp

WHERE empno=7839;

2.2.4 几个特殊函数

1）case函数与decode函数：

实现sql语句中的条件判断语句，具有类似高级语言中的if语句的功能。

case函数源自sql标准，decode函数源自oracle, 实现功能类似，decode语法更简单些。

case函数第一种用法：

SQL>

select job, sal,case job

when 'ANALYST' then SAL*1.1

when 'CLERK' then SAL*1.15

when 'MANAGER' then SAL*1.20

else sal end

REVISED_SALARY

from emp

case函数第二种用法：

SQL>

select job, sal,

case when job='ANALYST' then SAL*1.1

when job='CLERK' then SAL*1.15

when job='MANAGER' then SAL*1.20

else sal end

REVISED_SALARY

from emp

2）decode函数用法：

SQL>

SELECT job, sal,

DECODE(job, 'ANALYST', SAL*1.1,

'CLERK', SAL*1.15,

'MANAGER', SAL*1.20,

SAL)

REVISED_SALARY

FROM emp

以上三种写法结果都是一样的：

JOB SAL REVISED_SALARY

--------- ---------- --------------

CLERK 800 920

SALESMAN 1600 1600

SALESMAN 1250 1250

MANAGER 2975 3570

SALESMAN 1250 1250

MANAGER 2850 3420

MANAGER 2450 2940

ANALYST 3000 3300

PRESIDENT 5000 5000

SALESMAN 1500 1500

CLERK 1100 1265

CLERK 950 1092.5

ANALYST 3000 3300

CLERK 1300 1495

已选择14行。

3）DISTINCT(去重) 的用法：

SQL> select distinct job from emp; //消除表行重复值。

JOB

---------

CLERK

SALESMAN

PRESIDENT

MANAGER

ANALYST

SQL> select distinct job,deptno from emp; //重复值是后面的字段组合起来考虑的

JOB DEPTNO

--------- ----------

MANAGER 20

PRESIDENT 10

CLERK 10

SALESMAN 30

ANALYST 20

MANAGER 30

MANAGER 10

CLERK 30

CLERK 20

4）CHR()函数和ASCII()函数

chr()函数将ASCII码转换为字符： ASCII码 –》字符

ascii()函数将字符转换为ASCII码：字符 –》 ASCII码

在oracle中chr()函数和ascii()是一对反函数。

求字符对应的ASCII值

SQL> select ASCII('A') FROM dual;

ASCII('A')

----------

SQL> select chr(65) from dual;

5）处理空值的几种函数（见第六章）

第三章、SQL的数据类型

3.1 四种基本的常用数据类型

1、字符型， 2、数值型，3、日期型，4、大对象型

3.1.1 字符型：

char 固定字符，最长2000个 空间上固定长度不管字符多少。查询快。长度可扩展不可以缩短。

varchar2 可变长字符，最长4000个,最小值是1

nchar/nvarchar2 NCHAR/NVARCHAR2类型的列使用国家字符集

raw和long raw 固定/可变长度的二进制数据长度最2G,可存放多媒体图象声音等。（老类型，逐步淘汰）

LONG 可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，一个表中最多一个LONG列。（老类型，逐步淘汰）。

SQL> create table b (x char(10),y varchar2(10));

表已创建。

SQL> alter table b modify x varchar2(8);

表已更改。

SQL> insert into b values ('a','abc');

已创建 1 行。

SQL> alter table b modify x varchar2(2);

表已更改。

SQL> alter table b modify x varchar2(8);

表已更改。

SQL> alter table b modify x char(8);

表已更改。

SQL> insert into b values ('ab','abcde');

已创建 1 行。

SQL> alter table b modify x char(6);

alter table b modify x char(6)

第 1 行出现错误:

ORA-01441: 无法减小列长度, 因为一些值过大

SQL> alter table b modify y varchars(2);

alter table b modify y varchars(2)

第 1 行出现错误:

ORA-01735: 无效的 ALTER TABLE 选项

SQL> alter table b modify y varchar2(2);

alter table b modify y varchar2(2)

第 1 行出现错误:

ORA-01441: 无法减小列长度, 因为一些值过大

3.1.2 数值型：

number（p,s) 实数类型，以可变长度的内部格式来存储数字。这个内部格式精度可以高达38位。最大可支持到38个9。

int 整数型，number的子类型，范围同上 最大可支持到38个9。

扩展知识：

双精度、浮点 数值类型。

3.1.3 日期型：

date 日期的普通形式，表示精度只能到秒级。

timestamp 日期的扩展形式，表示精度可达秒后小数点9位（10亿分之1秒）。

timestamp with timezone 带时区

timestamp with local timezone 时区转换后的本地日期

3.1.4 LOB型：

大对象是10g 引入的，在11g中又重新定义，在一个表的字段里存储大容量数据，最长可能达到4G

CLOB: 用来存储单字节的字符数据，包含在数据库内。

　 NCLOB:用来存储多字节的字符数据　国家字符集

　　BLOB：用于存储二进制数据，包含在数据库内。涵盖了CLOB，反之不一定成立。

BFILE:存储在数据库之外的二进制文件中，这个文件中的数据只能被只读访问。但该文件不被修改。

不能用insert 语句插入大对象数据。

　　CLOB，NCLOB，BLOB都是内部的LOB类型，没有LONG只能有一列的限制

　　如要保存图片、文本文件、Word文件各自最好用哪种数据类型的呢？

　　BLOB最好，LONG　RAW也不错，但LONG是oracle将要废弃的类型，因此建议用LOB。

当然说将要废弃，但还没有完全废弃，比如oracle 11g里的重要视图dba_views，对于text(视图定义）仍然沿用了LONG类型。

3.2 数据类型的转换

隐性类型转换和显性类型转换。

3.2.1 隐性类型转换：

是oracle自动完成类型转换，即在一些明显意图的表达式上oracle可以自主完成数据类型的转换。如：

SQL> SELECT '12.5'+11 FROM dual;

'12.5'+11

----------

23.5

SQL> SELECT 10+('12.5'||11) FROM dual;

10+('12.5'||11)

---------------

22.511

SQL> SELECT 10+('12.5'||11) FROM dual;

10+('12.5'||11)

---------------

22.511

SQL> SELECT 10+('12.5'||'11') FROM dual;

10+('12.5'||'11')

-----------------

22.511

3.2.2 显性类型转换

是强制完成类型转换（推荐），转换函数形式有三种：

TO_CHAR

TO_DATE

TO_NUMBER

1）日期-->字符

SQL> select ename, hiredate, to_char(hiredate, 'DD-MON-YY') month_hired from emp where ename='SCOTT';

ENAME HIREDATE MONTH_HIRED

---------- ------------------- --------------

SCOTT 1987-04-19 00:00:00 19-4月 -87

fm压缩空格或左边的'0'

SQL> select ename, hiredate, to_char(hiredate, 'fmyyyy-mm-dd') month_hired from emp where ename='SCOTT';

ENAME HIREDATE MONTH_HIRE

---------- ------------------- ----------

SCOTT 1987-04-19 00:00:00 1987-4-19

2）字符-->日期

SQL> select to_date('1983-11-12', 'YYYY-MM-DD') tmp_DATE from dual;

TMP_DATE

-------------------

1983-11-12 00:00:00

3）数字-->字符：9表示数字，$本地化货币字符

SQL> select ename, to_char(sal, '$99,999.99') Salary from emp where ename='SCOTT';

ENAME SALARY

---------- -----------

SCOTT $3,000.00

4）字符-->数字：

SQL> SELECT to_number('$123.45','$999.99') result FROM dual;

RESULT

----------

123.45

考点：使用to_number时如果使用较短的格式掩码转换数字，就会返回错误。不要混淆to_number和to_char转换。

例如：

SQL> select to_number(123.56,'999.9') from dual;

select to_number(123.56,'999.9') from dual

第 1 行出现错误:

ORA-01722: 无效数字

SQL> select to_char(123.56,'999.9') from dual;

TO_CHA

------

123.6

SQL> select to_number('$123.45','$999.99') result from dual;

RESULT

----------

123.45

SQL> select to_number('$123.45','$99.99') result from dual;

select to_number('$123.45','$99.99') result from dual

第 1 行出现错误:

ORA-01722: 无效数字

第四章、WHERE子句中数据的比较和运算

4.1 隐式比较与显式比较：

SQL>select ename, hiredate from emp where hiredate > '1982-01-01'; //oracle隐式转换了

SQL>select ename, hiredate from emp where hiredate > to_date('1982-01-01','YYYY-MM-DD'); //显式

4.2 运算符优先级：

SQL>select ename, job, sal ,comm from emp where job='SALESMAN' OR job='PRESIDENT' AND sal> 1500;

考点：条件子句使用比较运算符比较两个选项，重要的是要理解这两个选项的数据类型，如有必要可以将它们包含在单引号内。

优先级NOT>AND>OR 打破优先级用括号。

4.3 用BETWEEN AND操作符来查询出在某一范围内的行.

SQL> SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 1500;

//between 低值 and 高值，含低值和高值。

4.4 模糊查询及其通配符：

在where字句中使用like谓词，常使用特殊符号"%"或"_"匹配查找内容，也可使用escape可以取消特殊符号的作用。

‘%’多个，‘-’一个。

SQL>

create table test1 (name char(10));

insert into test1 values ('sFdL');

insert into test1 values ('AEdLHH');

insert into test1 values ('A%dMH');

commit;

SQL> select * from test1;

NAME

----------

sFdL

AEdLHH

A%dMH

SQL> select * from test1 where name like 'A\%%' escape '\';

NAME

----------

A%dMH

4.5 用IN操作符来检验一个值是否在一个列表中

SQL> SELECT empno, ename, sal, mgr FROM emp WHERE mgr IN (7902, 7566, 7788);

SQL> select * from employees where EMPLOYEE_ID in (100,101,102,1000000000000000);

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER

----------- -------------------- ------------------------- ------------------------- --------------------

HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

------------------- ---------- ---------- -------------- ---------- -------------

100 Steven King SKING 515.123.4567

1987-06-17 00:00:00 AD_PRES 24000 90

101 Neena Kochhar NKOCHHAR 515.123.4568

1989-09-21 00:00:00 AD_VP 17000 100 90

102 Lex De Haan LDEHAAN 515.123.4569

1993-01-13 00:00:00 AD_VP 17000 100 90

（）中的数值可以不存在在表中。

4.6 交互输入变量符&和&&的用途：

&后的变量名可以随意起名。

Sql>Set verify off 可以关闭验证。

SQL> select empno,ename from emp where empno=&empnumber;

输入 empnumber 的值: 7788

原值 1: select empno,ename from emp where empno=&empnumber

新值 1: select empno,ename from emp where empno=7788

EMPNO ENAME

---------- ----------

7788 SCOTT

&后面是字符型的，注意单引号问题，可以有两种写法：

SQL> select empno,ename from emp where ename='&emp_name';

输入 emp_name 的值: SCOTT

原值 1: select empno,ename from emp where ename='&emp_name'

新值 1: select empno,ename from emp where ename='SCOTT'

EMPNO ENAME

---------- ----------

7788 SCOTT

SQL> select empno,ename from emp where ename=&emp_name;

输入 emp_name 的值: 'SCOTT'

原值 1: select empno,ename from emp where ename=&emp_name

新值 1: select empno,ename from emp where ename='SCOTT'

EMPNO ENAME

---------- ----------

7788 SCOTT

&&存储了第一次输入值，使后面的相同的&不在提问，自动取代。

SQL> select empno,ename,&&salary from emp where deptno=10 order by &salary;

输入 salary 的值: sal

原值 1: select empno,ename,&&salary from emp where deptno=10 order by &salary

新值 1: select empno,ename,sal from emp where deptno=10 order by sal

EMPNO ENAME SAL

---------- ---------- ----------

7934 MILLER 1300

7782 CLARK 2450

7839 KING 5000

还可以使用define命令常用于批处理。

4.7 使用逻辑操作符: AND; OR; NOT

AND 两个条件都为TRUE ，则返回TRUE

SQL> SELECT empno,ename,job,sal FROM emp WHERE sal>=1100 AND job='CLERK';

EMPNO ENAME JOB SAL

---------- ---------- --------- ----------

7876 ADAMS CLERK 1100

7934 MILLER CLERK 1300

OR 两个条件中任何一个为TRUE，则返回TRUE

SQL> SELECT empno,ename,job,sal FROM emp WHERE sal>=1100 OR job='CLERK';

EMPNO ENAME JOB SAL

---------- ---------- --------- ----------

7369 SMITH CLERK 800

7499 ALLEN SALESMAN 1600

7521 WARD SALESMAN 1250

7566 JONES MANAGER 2975

7654 MARTIN SALESMAN 1250

......

已选择14行。

NOT 如果条件为FALSE，返回TRUE

SQL> SELECT ename,job FROM emp WHERE job NOT IN ('CLERK','MANAGER','ANALYST');

ENAME JOB

---------- ---------

ALLEN SALESMAN

WARD SALESMAN

MARTIN SALESMAN

KING PRESIDENT

TURNER SALESMAN

第五章分组函数

5.1 五个分组函数

sum(); avg(); count(); max(); min().

数值类型可以使用所有组函数

SQL>select sum(sal) sum, avg(sal) avg, max(sal) max, min(sal) min, count(*) count from emp;

分组：group by

对日期类型可以使用MIN,MAX

SQL>select min(hiredate), max(hiredate) from emp;

COUNT(*)函数返回表中行的总数，包括重复行与数据列中含有空值的行，而其他分组函数的统计都不包括空值的行。（考点）

COUNT(EXPR)返回expr标识的列所含非空行的数量。

5.2 在组函数中使用NVL函数

SQL> select deptno, avg(nvl(comm,0)) from emp group by deptno; 在统计过程中，遇到空值，自动取值为数值0

DEPTNO AVG(NVL(COMM,0))

---------- ----------------

30 366.666667-----------基数6

20 0

10 0

SQL> select deptno, avg(comm) from emp group by deptno;

DEPTNO AVG(COMM)

---------- ----------

30 550-------------基数4

想一想上面两个例子结果为何不一样？

5.3 GROUP BY创建数据组

SQL>select deptno, avg(nvl(sal,0)) from emp group by deptno;

对分组结果进行过滤

SQL>select deptno, avg(sal) avgcomm from emp group by deptno having avg(sal)>2000;

SQL>select deptno, avg(sal) avgcomm from emp where avg(sal)>2000 group by deptno; //错误的,应该使用HAVING子句

HAVING必须在GROUP BY中使用，不能单独使用，且后面不能直接跟列（select中出现列）

Where后不能直接跟聚合函数。

对分组结果排序

SQL>select deptno, avg(nvl(sal,0)) avgcomm from emp group by deptno order by avg(nvl(sal,0));

确保SELECT列表中除了组函数的项，所有列都包含在GROUP BY子句中（考点）。

例如，错误的写法：select empno,sum(sal) from emp;

5.4 分组函数的嵌套

单行函数可以嵌套任意层，但分组函数最多可以嵌套两层。（考点）

比如：count(sum(avg)))会返回错误“ORA-00935：group function is nested too deeply”.

在分组函数内可以嵌套单行函数，如：要计算各个部门ename值的平均长度之和

SQL> select sum(avg(length(ename))) from emp group by deptno;

SUM(AVG(LENGTH(ENAME)))

-----------------------

14.9666667

第六章、数据限定与排序

6.1 简单查询语句执行顺序

from, where, group by, having, order by, select

where限定from后面的表或视图，限定的选项只能是表的列或列函数或列表达式，where后不可以直接使用分组函数即sum(); avg(); count(); max(); min().

SQL> select empno,job from emp where sal>2000; 实列

SQL> select empno,job from emp where length(job)>5; 列的单行函数

SQL> select empno,job from emp where sal+comm>2000; 列的表达式

having限定group by的结果，限定的选项必须是group by后的聚合函数或分组列，不可以直接使用where后的限定选项。

SQL> select sum(sal) from emp group by deptno having deptno=10;

SQL> select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;

如果要使用分组函数及having，有条件的话先使用where删选。

6.2 排序(order by)

1）位置：order by语句总是在一个select语句的最后面。

2）排序可以使用列名，表达式（虚列），列别名，列的位置等都没有限制，select选项也可以没有排序列。

Order by 2 （select后面的第二列）。

3）升序和降序，升序ASC（默认),DESC 降序。

SQL> select ename,job,sal+comm from emp order by 3 desc nulls last; (或first。默认为first)

4）混合排序，使用多个列进行排序，多列使用逗号隔开，可以分别在各列后面加升降序。

SQL> select ename,deptno,job from emp order by deptno asc,job desc;

ENAME DEPTNO JOB

---------- ---------- ---------

KING 10 PRESIDENT

CLARK 10 MANAGER

MILLER 10 CLERK

JONES 20 MANAGER

ADAMS 20 CLERK

SMITH 20 CLERK

SCOTT 20 ANALYST

FORD 20 ANALYST

MARTIN 30 SALESMAN

TURNER 30 SALESMAN

WARD 30 SALESMAN

ALLEN 30 SALESMAN

BLAKE 30 MANAGER

JAMES 30 CLERK

6.3 空值（null)

空值既不是数值0,也不是字符" ",

6.3.1 空值参与运算或比较时要注意几点。

1）空值（null）的数据行将对算数表达式返回空值

SQL> select ename,sal,comm,sal+comm from emp;

ENAME SAL COMM SAL+COMM

---------- ---------- ---------- ----------

SMITH 800

ALLEN 1600 300 1900

WARD 1250 500 1750

JONES 2975

MARTIN 1250 1400 2650

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

TURNER 1500 0 1500

ADAMS 1100

JAMES 950

FORD 3000

MILLER 1300

已选择14行。

2）空值（null）的数据行将比较表达式返回空值

SQL> select ename,sal,comm from emp where sal>=comm;

ENAME SAL COMM

---------- ---------- ----------

ALLEN 1600 300

WARD 1250 500

TURNER 1500 0

3）非空字段与空值字段做"||"时, 返回非空字段内容。连字符

SQL> select ename, sal||comm from emp;

ENAME SAL||COMM

---------- --------------------------------------------------------------------------------

SMITH 800

ALLEN 1600300

WARD 1250500

JONES 2975

MARTIN 12501400

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

TURNER 15000

ADAMS 1100

JAMES 950

FORD 3000

MILLER 1300

已选择14行。

4）not in 在子查询中的空值问题（见第八章）

5）外键值可以为null

6）空值在where子句里使用“is null”或“is not null”

SQL> select empno,mgr from emp where mgr is null;

SQL> select empno,mgr from emp where mgr is not null;

7）空值在update语句和insert语句可以直接使用“=null” 赋值

SQL> update emp set comm=null where empno=7788;

6.3.2 处理空值的几种函数方法：

1）nvl(expr1,expr2)

当第一个参数不为空时取第一个值，当第一个值为NULL时，取第二个参数的值。

SQL>select nvl(1,2) from dual;

NVL(1,2)

----------

SQL> select nvl(null,2) from dual;

NVL(NULL,2)

-----------

2）nvl2(expr1,expr2,expr3)

当第一个参数不为NULL，取第二个参数的值，当第一个参数为NULL，取第三个数的值。

SQL> select nvl2(1,2,3) from dual;

NVL2(1,2,3)

-----------

SQL> select nvl2(null,2,3) from dual;

NVL2(NULL,2,3)

--------------

//注意: nvl和nvl2中的第二个参数不是一回事（考点）

3）NULLIF（expr1,expr2) /*比对两个值是否一样，一样就返回为空，否则不会为空*/

当第一个参数和第二个参数相同时，返回为空，当第一个参数和第二个数不同时，返回第一个参数值，第一个参数值不允许为null。第二个可以是NULL

SQL> select nullif(2,2) from dual;

NULLIF(2,2)

-----------

SQL> select nullif(1,2) from dual;

NULLIF(1,2)

-----------

4）coalesce（expr1,expr2........) 返回第一个不为空的值，如果所有参数都为空，那么返回空值。

SQL> select coalesce(1,2,3,4) from dual;

COALESCE(1,2,3,4)

-----------------

SQL> select coalesce(null,2,null,4) from dual;

COALESCE(NULL,2,3,4)

--------------------

SQL> select coalesce(null,null,null) from dual;

第七章、复杂查询之一：多表连接技术

7.1 简单查询的解析方法：

全表扫描：指针从第一条记录开始，依次逐行处理，直到最后一条记录结束；

横向过滤+纵向筛选=结果集

7.2 多表连接

两大类：等值连接和非等值连接。

等值连接：例如join on 。等值连接使用‘=’。例如e.abc=b.abc e表和b表的公共列abc

交叉连接（笛卡尔积）

典型代表：内连接

外连接（左外，右外，全连接）

自连接

自然连接（隐含连接条件,自动匹配连接字段）

集合运算

范例：

create table a (id int, name char(10));

create table b (id int, loc char(10));

insert into a values (1,'a');

insert into a values (2,'b');

insert into a values (4,'d');

insert into b values (1,'A');

insert into b values (2,'B');

insert into b values (3,'C');

commit;

SQL> select * from a;

ID NAME

---------- ----------

1 a

2 b

4 d

SQL> select * from b;

ID LOC

---------- ----------

1 A

2 B

3 C

7.2.1 交叉连接（笛卡尔积）

当连接条件无效或被省略时，两个表的所有行都发生连接，所有行的组合都会返回（n*m）

SQL>

SQL> select * from a,b;

ID NAME ID LOC

---------- ---------- ---------- ----------

1 a 1 A

1 a 2 B

1 a 3 C

2 b 1 A

2 b 2 B

2 b 3 C

4 d 1 A

4 d 2 B

4 d 3 C

已选择9行。

7.2.2 内连接或简单连接

内联运算效率高

SQL99写法：

SQL> select * from a inner join b on a.id=b.id;

oracle写法：

SQL> select * from a,b where a.id=b.id;

ID NAME ID LOC

---------- ---------- ---------- ----------

1 a 1 A

2 b 2 B

7.2.3 外连接包括左外连接，右外连接，全外连接

1）左外连接

SQL99语法:

SQL> select * from a left join b on a.id=b.id;

oracle语法:

SQL> select * from a,b where a.id=b.id(+);左联右补全。

结果：

ID NAME ID LOC

---------- ---------- ---------- ----------

1 a 1 A

2 b 2 B

4 d

匹配不上则补全空值。

2）右外连接

SQL99语法:

SQL>select * from a right join b on a.id=b.id;

oracle语法:

SQL> select * from a,b where a.id(+)=b.id;右联左补全。

结果

ID NAME ID LOC

---------- ---------- ---------- ----------

1 a 1 A

2 b 2 B

3 C

3）全外连接

SQL99语法:

SQL> select * from a full join b on a.id=b.id;

ID NAME ID LOC

---------- ---------- ---------- ----------

1 a 1 A

2 b 2 B

3 C

4 d

oracle语法:

SQL> select * from a,b where a.id=b.id(+)

union

select * from a,b where a.id(+)=b.id;

ID NAME ID LOC

---------- ---------- ---------- ----------

1 a 1 A

2 b 2 B

4 d

3 C

7.2.4 自连接

必须使用别名

SQL> select * from a a1,a a2;

ID NAME ID NAME

---------- ---------- ---------- ----------

1 a 1 a

1 a 2 b

1 a 4 d

2 b 1 a

2 b 2 b

2 b 4 d

4 d 1 a

4 d 2 b

4 d 4 d

已选择9行。

7.2.5 自然连接

在oralce中使用natural join,也就是自然连接。在Oracle中的join连接中使用using关键字，

是相对于natural join的。如果是使用natraul join，并且两张表中如果有多个字段是具有相同的名称和数据类型的，那么这些字段都将被oracle自作主张的将他们连接起来。但实际上我们有时候是不需要这样来连接的。我们只需要将他们的多个具有相同的名称和数据类型的字段中挑选一两个。这时候我们就需要用到using 关键字了。当多列匹配时，用using指定匹配的列

先看自然连接：

SQL> select * from a;

ID NAME

---------- ----------

1 a

2 b

4 d

SQL> select * from b;

ID LOC

---------- ----------

1 A

2 B

3 C

SQL> select * from a natural join b;

ID NAME LOC

---------- ---------- ----------

1 a A

2 b B

-----两个表分别再加一个列ABC后，则有两个公共列做连接条件。

SQL> select * from a;

ID NAME ABC

---------- ---------- ----------

1 a s

2 b t

4 d u

SQL> select * from b;

ID LOC ABC

---------- ---------- ----------

1 A w

2 B t

3 C r

SQL> select * from a natural join b;

ID ABC NAME LOC

---------- ---------- ---------- ----------

2 t b B

在oracle连接(join)中使用using关键字

SQL> select id,a.abc,name,loc from a join b using(id); 可以显示两个表的abc

ID ABC NAME LOC

---------- ---------- ---------- ----------

1 s a A

2 t b B

SQL> select id,abc,name,loc from a join b using(id,abc);

ID ABC NAME LOC

---------- ---------- ---------- ----------

2 t b B

注意：

1、如果在使用using关键字时，而且select的结果列表项中包含了using关键字所指明的那个关键字，那么，不要在select的结果列表项中对该关键字指明它属于哪个表。

2、using中可以指定多个列名。

3、natural 关键字和using关键字是互斥的，也就是说不能同时出现。

SQL> select x,a.abc,b.abc from a join b using (x);

X ABC ABC

-------- ---------- ----------

a 123 123

7.3 集合运算

Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；包含相交的集合部分，计算一次。

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；包含相交的集合部分，计算两次。

Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

集合操作有　并，交，差　３种运算。

举例:

SQL> create table dept1 as select * from dept where rownum <2;从dept表中取第一行的值创建表dept1;

SQL> insert into dept1 values (80, 'MARKTING', 'BEIJING');

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

80 MARKTING BEIJING

7.3.1 union

SQL> select * from dept

2 union

3 select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

80 MARKTING BEIJING

7.3.2 union all

SQL> select * from dept

2 union all

3 select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

10 ACCOUNTING NEW YORK

80 MARKTING BEIJING

7.3.3 intersect

SQL> select * from dept

2 intersect

3 select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

7.3.4 minus (注意谁minus谁）

SQL> select * from dept

2 minus

3 select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select * from dept1

2 minus

3 select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

80 MARKTING BEIJING

7.4 集合运算中只要类型能匹配上就可以连接

如果是char类型，长度也必须一样才可以集合运算。列名可以不一样。Verchar2可以不需要长度一样。

可以将CHAR长度扩展。

SQL> create table a (id_a int,name_a char(10));

SQL> create table b (id_b int,name_b char(10),sal number(10,2));

insert into a values (1, 'sohu');

insert into a values (2, 'sina’);

insert into b values (1, 'sohu', 1000);

insert into b values (2, 'yahoo', 2000);

commit;

SQL> select * from a;

ID_A NAME_A

---------- ----------

1 sohu

2 sina

SQL> select * from b;

ID_B NAME_B SAL

---------- ---------- ----------

1 sohu 1000

2 yahoo 2000

SQL> select id_a, name_a from a

2 union

3 select id_b,name_b from b;

ID_A NAME_A

---------- ----------

1 sohu

2 sina

2 yahoo

7.5 关于order by 使用别名排序的问题：

1)，缺省情况下，集合运算后的结果集是按第一列隐式排序的（除union all 外）

如果不希望缺省的排序，也可以使用order by显示排序。 对整个结果集排序。

select id_a, name_a name from a

union

select id_b, name_b name from b

order by name;

ID_A NAME

---------- ----------

2 sina

1 sohu

2 yahoo

select id_a, name_a from a

union

select id_b, name_b from b

order by 2;

ID_A NAME_A

---------- ----------

2 sina

1 sohu

2 yahoo

2), 显式order by 是参照第一个表的列元素，如果是补全的null值需要order by，则需要使用别名。

SQL> select id_a, name_a name,to_number(null) from a

2 union

3 select id_b, name_b name,sal from b

4 order by sal

5 /

order by sal

第 4 行出现错误:

ORA-00904: "SAL": 标识符无效

SQL> select id_a, name_a name,to_number(null) from a

2 union

3 select id_b, name_b name,sal from b

4 order by 3 (第三列)

5 /

ID_A NAME TO_NUMBER(NULL)

---------- ---------- ---------------

1 sohu 1000

2 yahoo 2000

1 sohu

2 sina

SQL> select id_b, name_b name,sal from b

2 union

3 select id_a, name_a name,to_number(null) from a

4 order by sal

5 /

ID_B NAME SAL

---------- ---------- ----------

1 sohu 1000

2 yahoo 2000

1 sohu

2 sina

SQL> select id_a, name_a name,to_number(null) aa from a

2 union

3 select id_b, name_b name,sal aa from b

4 order by aa

5 /

ID_A NAME AA

---------- ---------- ----------

1 sohu 1000

2 yahoo 2000

1 sohu

2 sina

3)，排序是对结果集的排序，不能分别在两表中排序，order by 只能出现一次且在最后一行；

SQL> select id_a, name_a from a order by id_a

2 union

3 select id_b, name_b from b order by id_b;

union

第 2 行出现错误:

ORA-00933: SQL 命令未正确结束

升序排列时候，NULL默认排在最后。

第八章、复杂查询(下）：子查询与多列子查询

子查询返回的值可以被外部查询使用，这样的复合查询等于执行两个连续的查询。

8.1 单行子查询采用单行比较运算符（>，<，=，<>)

内部SELECT子句只返回一行结果（单列）

SQL>select ename,sal

from emp

where sal > (

select sal from emp

where ename='JONES')

和员工7369从事相同工作并且工资大于员工7876的员工的姓名和工作

SQL>select ename,job

from emp

where job=(

select job

from emp

where empno=7369

)

and

sal > (

select sal

from emp

where empno=7876

)

8.2 在多行子查询中使用all

（>大于最大的，<小于最小的）

SQL> select ename, sal from emp where sal >all (2000,3000,4000);

ENAME SAL

---------- ----------

KING 5000

查找高于所有部门的平均工资的员工（>比子查询中返回的列表中最大的大才行）

SQL>SQL> select ename, job, sal

2 from emp

3 where sal > all(

4 select avg(sal)

5 from emp

6 group by deptno)

7 /

ENAME JOB SAL

---------- --------- ----------

JONES MANAGER 2975

SCOTT ANALYST 3000

KING PRESIDENT 5000

FORD ANALYST 3000

SQL> select avg(sal) from emp group by deptno; //子查询结果

AVG(SAL)

----------

1566.66667

2175

2916.66667

8.3 在多行子查询中使用any

（>大于最小的，<小于最大的）

>any的意思是：比子查询中返回的列表中最小的大就行, 注意和all的区别，all的条件苛刻，any的条件松阔，

any强调的是只要有任意一个符合就行了，所以>any只要比最小的那个大就行了，没必要比最大的还大。

select ename, sal from emp where sal >any (2000,3000,4000);

ENAME SAL

---------- ----------

JONES 2975

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

FORD 3000

8.4 在多行子查询中使用in

(逐个比较是否有匹配值）

SQL> select ename, sal from emp where sal in (800,3000,4000);

ENAME SAL

---------- ----------

SMITH 800

SCOTT 3000

FORD 3000

NOT运算操作符可以使用在IN操作上,但不能使用在ANY,ALL操作。

SQL> select ename, sal from emp where sal not in (800,3000,4000);

ENAME SAL

---------- ----------

ALLEN 1600

WARD 1250

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK 2450

KING 5000

TURNER 1500

ADAMS 1100

JAMES 950

MILLER 1300

已选择11行。

8.5 多列子查询多行多列

多列子查询是返回多列结果的内部SELECT语句

将WHERE子句中多个条件合并成一个

列比较有成对比较与不成对比较两种

实验准备

SQL>create table emp1 as select * from emp;

SQL>update emp1 set sal=1600,comm=300 where ename='SMITH'; //SMITH是20部门的员工

SQL>update emp1 set sal=1500,comm=300 where ename='CLARK'; //CLARK是10部门的员工

SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 1980-12-17 00:00:00 1600 300 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981-06-09 00:00:00 1500 300 10

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

已选择14行。

查询条件：查找emp1表中是否有与30部门的员工工资和奖金相同的其他部门的员工。

（注意看一下：现在20部门的SIMTH符合这个条件，它与30部门的ALLEN 有相同的工资和奖金）

成对比较多列子查询

主查询每一行中的列都要与子查询返回列表中的相应列进行比较

只有各列完全匹配时才显示主查询中的该数据行

分解一下：

第一步，我们可以先找出emp1表中30号部门的工资和奖金的结果集，（此例没有对comm的空值进行处理）

SQL> SQL> select sal,comm from emp1 where deptno=30;

SAL COMM

---------- ----------

1600 300

1250 500

1250 1400

2850

1500 0

950

已选择6行。

第二步，列出emp1表中属于这个结果集的所有员工。

可以这样想：相当于谓词in（...)中有上面这6行内容

SQL> select * from emp1 where (sal,comm) in (select sal,comm from emp1 where deptno=30); 必须一一对应。

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7369 SMITH CLERK 7902 1980-12-17 00:00:00 1600 300 20

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

上面句子相当于：

select * from emp1 where (sal,comm) in(

(1600,300),(1250,500),(1250,1400),(2850,null),(1500,0),(950,null)

);

第三步，再去掉30号部门后，就显示出了在emp1表中与30部门中任意一个员工的工资和奖金完全相同的，

但该员工不是来自30部门的员工信息。

SQL>

select ename,deptno,sal,comm from emp1

where (sal,comm) in (select sal,comm from emp1 where deptno=30)

and deptno<>30

ENAME DEPTNO SAL COMM

---------- ---------- ---------- ----------

SMITH 20 1600 300

8.6 非成对比较多列子查询

SQL>select ename,deptno,sal,comm

from emp1

where sal in(

select sal

from emp1

where deptno=30)

and

nvl(comm,0) in (

select nvl(comm,0)

from emp1

where deptno=30)

and deptno<>30

ENAME DEPTNO SAL COMM

---------- ---------- ---------- ----------

SMITH 20 1600 300

CLARK 10 1500 300

两个子查询返回的值分别与主查询中的sal和comm列比较

员工的工资与30部门任意一个员工相同，同时，奖金也与30部门的任意一个员工相同，则输出该员工信息。

8.7 not in 在子查询中的空值问题：

in与not in遇到空值时情况不同，对于not in 如果子查询的结果集中有空值，那么最终主查询的结果集是空。考点

查找出没有下属的员工,即普通员工，（该员工号不在mgr之列的）

SQL>select ename

from emp

where empno not in

(select mgr

from emp)

no rows selected

上面的结果不出所料，主查询没有返回记录。这个原因是在子查询中有一个空值，而对于not in这种形式，一旦子查询出现了空值，则主查询记录结果

也就返回空了。

例：排除空值的影响 使用去空函数nvl

SQL>select ename

from emp

where empno not in

(select nvl(mgr,0)

from emp)

8 rows selected.

8.8 from子句中使用子查询(这个也叫内联视图）

例：员工的工资大于他所在的部门的平均工资的话，显示其信息。

分两步来考虑：

第一步，先看看每个部门的平均工资，把这个结果集作为一个内联视图

SQL> select deptno,avg(sal) salavg from emp group by deptno;

DEPTNO SALAVG

---------- ----------

30 1566.66667

20 2175

10 2916.66667

SQL>

第二步，把这个内联视图起一个别名b, 然后和emp 别名e 做连接，满足条件即可。

select e.ename, e.sal, e.deptno, b.salavg

from emp e, (select deptno,avg(sal) salavg from emp group by deptno) b

where e.deptno=b.deptno and e.sal > b.salavg

知识点：内联试图使用在from中，多表连接使用别名。

ENAME SAL DEPTNO SALAVG

---------- ---------- ---------- ----------

ALLEN 1600 30 1566.66667

JONES 2975 20 2175

BLAKE 2850 30 1566.66667

SCOTT 3000 20 2175

KING 5000 10 2916.66667

FORD 3000 20 2175

8.9 关联子查询与非关联子查询

从主查询（外部）调用子查询（内部）来看，可以有分关联与非关联子查询之分

8.9.1 非关联子查询：

子查询部分可以独立执行，Oracle的in子查询一般用于非关联子查询，执行过程是这样的，首先执行子查询，并将获得的结果列表存放在一个加了索引

的临时表中。也就是说在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。

例：查找名称为ACCOUNTING的部门里所有员工的信息

SQL>

select ename,deptno from emp

where deptno in (

select deptno from dept where dname='ACCOUNTING')

ENAME DEPTNO

---------- ----------

CLARK 10

KING 10

MILLER 10

8.9.2 关联子查询：

其子查询（内部，inner)会引用主查询(外部，outer)查询中的一列或多列。在执行时,外部查询的每一行都被一次一行地传递给子查询.子查询依次读取

外部查询传递来的每一值,并将其用到子查询上,直到外部查询所有的行都处理完为止.然后返回查询结果.

同例：

select ename,deptno from emp e

where deptno in (

select d.deptno from dept d where e.deptno=d.deptno and d.dname='ACCOUNTING')

ENAME DEPTNO

---------- ----------

CLARK 10

KING 10

MILLER 10

注意 1）以上两例都是用了in查询，结果一样，但是Oracle的查询过程却截然不同。

2）in 用于关联子查询效率不高，Oracle推荐使用exists()代替

8.9.3 关联子查询操作符exists(）

同例：

SQL>select ename,deptno from emp e

where exists (

select d.deptno from dept d where e.deptno=d.deptno and d.dname='ACCOUNTING')

可以是 select *

ENAME DEPTNO

---------- ----------

CLARK 10

KING 10

MILLER 10

其中 “e.deptno=d.deptno” 相当于一个关联表查询，如果你只执行括号里的语句，是会报语法错误的，这也是使用关联子查询的特点。

“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。因此，子查询中的“select d.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特有的，推荐使用。另外还有not in 与not exists的比较，机制类似。

8.10 简单查询与复杂查询练习题：

a）列出emp表工资最高的前三名员工信息

rownum, rowed rownum不能用大于号连接，只能用小于号或者等于号。

select * from (select * from emp order by sal desc) where rownum < 4;

select * from (select rownum as rn,employee_id as id,first_name||' '||last_name as name ,salary from employees order by salary desc);

b) 列出emp表第5-第10名员工的信息（结果集的分页查询技术）

select * from (select t1.*,rownum rn from emp t1) where rn between 5 and 10;

c）从列出emp表中显示员工和经理对应关系表。(emp自连,利用笛卡尔积）

select a.empno,a.ename,a.mgr , b.empno,b.ename, b.mgr

from emp a, emp b

where a.mgr=b.empno;

d)要求列出emp表中最高工资的员工所在工作地点，(emp+dept左外)

select a.ename, d.loc from

(select * from emp where sal=

(select max(sal) from emp)) a left join dept d on a.deptno=d.deptno ;

e）根据emp表和dept表组织一张新表emp1，要求新表能看到所有员工的员工号，姓名与工作地点, 部门编号的对应信息。

create table emp1 as (select e.empno,e.ename,d.loc,d.deptno from emp e,dept d where e.deptno=d.deptno(+));

f）在e）的基础上将所有loc字段的记录修改为空值，再利用dept表更新（深入理解update语句执行过程、顺序,及关联子查询概念）

SQL> update emp1 set loc=null;

SQL> update emp1 e set loc=(select d.loc from dept d where e.deptno=d.deptno);

g) 三表联查，假设有三个表（显示如下），分别是emp1，title, detp 要求列出各部门经理的信息，信息里包括员工编号、员工姓名、薪水、头衔、部门编号、工作地点。

表A----------表B----------------表C

表A与表B之间有公共列，表B与表C之间有公共列，即可将三个表连接。

SQL> select * from emp1;

EMPNO ENAME SAL

---------- ---------- ----------

7369 SMITH 800

7499 ALLEN 1600

7521 WARD 1250

7566 JONES 2975

7654 MARTIN 1250

7698 BLAKE 2850

7782 CLARK 2450

7788 SCOTT 3000

7839 KING 5000

7844 TURNER 1500

7876 ADAMS 1100

7900 JAMES 950

7902 FORD 3000

7934 MILLER 1300

已选择14行。

SQL> select * from title;

EMPNO JOB DEPTNO

---------- --------- ----------

7369 CLERK 20

7499 SALESMAN 30

7521 SALESMAN 30

7566 MANAGER 20

7654 SALESMAN 30

7698 MANAGER 30

7782 MANAGER 10

7788 ANALYST 20

7839 PRESIDENT 10

7844 SALESMAN 30

7876 CLERK 20

7900 CLERK 30

7902 ANALYST 20

7934 CLERK 10

已选择14行。

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select e.empno,e.ename,e.sal,t.job,t.deptno,d.loc

from emp1 e, title t, dept d

where t.deptno=d.deptno

and e.empno=t.empno

and t.job=UPPER('manager')

EMPNO ENAME SAL JOB DEPTNO LOC

---------- ---------- ---------- --------- ---------- -------------

7566 JONES 2975 MANAGER 20 DALLAS

7698 BLAKE 2850 MANAGER 30 CHICAGO

7782 CLARK 2450 MANAGER 10 NEW YORK

Select e.empno,e.ename,e.sal,t.job,t.deptno,d.loc from emp e inner join title t on e.empno=t.empno inner join dept d on t.deptno=d.deptno where t.job=UPPER(‘manager’)

第九章用户访问控制

9.1 创建和管理数据库用户账户

9.1.1用户的缺省表空间

创建一个用户，可以三种缺省：缺省表空间，缺省临时文件，缺省profile

每个用户账户都有默然的表空间，用户创建的任何对象（如表或索引）将保存在此表空间中，如果创建用户是没有指定默认表空间，那么属于数据库级的默认表空间（缺省安装时是users表空间）将应用于所有账户。Users表空间是所有用户的公共的默认表空间。

SQL> Select * from v$tablespace;

TS# NAME INC BIG FLA ENC

---------- ------------------------------ --- --- --- ---

0 SYSTEM YES NO YES

1 SYSAUX YES NO YES

4 USERS YES NO YES

6 EXAMPLE YES NO YES

8 TEST YES NO YES

2 UNDOTBS1 YES NO YES

3 TEMP NO NO YES

数据库级的默认表空间可以更改

alter database default tablespace tablespace_name;

9.1.2 配额的概念：

配额（quota)是表空间中为用户的对象使用的空间量，dba建立用户时就应该考虑限制用户的磁盘配额，否则用户可以将对他无限制配额的表空间撑爆。

--设置限额=10m

ALTER user tim quota 10m on test_tbs;

--限额无限=不受限

ALTER USER tim QUOTA UNLIMITED ON test_tbs;

--收回限额

ALTER USER tim QUOTA 0 ON test_tbs;

考点：

1）要创建表，用户必须具有执行create table的权限，而且拥有在其中创建表的表空间上的配额。要创建表必须满足这两个条件：权限和配额。

2）用户不需要临时表空间上的配额。 临时表空间是公共空间。

9.2 管理概要文件

作用是对用户访问数据库做一些限制。有几个要点：

1）概要文件（profile)具有两个功能，一个是实施口令限制，另一个是限制会话可以占用的资源。

2）始终要实施口令控制，而对于资源限制，则只有实例参数RESOURE_LIMIT为TRUE时（默认是FALSE)才会实施（考点）。

3）系统自动使用概要文件，默认的概要文件限制很松，作用较小。

4）可以使用create profile为用户创建它自己的概要文件，没有说明的参数就从defaule profile的当前版本中提取。

Password_parameter部分：

Failed_login_attempts：指定在帐户被锁定之前所允许尝试登陆的的最大次数。

Password_lock_time：在到达Failed_login_attempts后锁定账户的天数。

Password_life_time：口令过期前的天数，口令在过期后仍可以使用，具体取决于Password_grace_time

Password_grace_time：口令过期（将生成更改口令的提示）后第一次成功登录后的天数，在此期间，旧口令仍然可用。

Password_reuse_time：可以重新使用口令前的天数

password_reuse_max：可以重新使用口令的次数

Password_verify_function：更改口令时运行的函数名，此函数一般用于检查新口令所需的复杂程度。

Resource_parameter部分

Session_per_user：对同一个用户账户执行的并发登录数。

Cpu_per_session：在强制终止会话前，允许会话的服务器进程使用的CPU时间（厘秒）。

Cpu_per_call：在强制终止某条SQL语句前，允许会话的服务器进程用于执行此语句的CPU时间（厘秒）。

Connect_time：在强制终止会话前，会话的最长持续时间（分钟）。

Idle_time：在强制终止会话前，允许会话处于闲置状态的最长时间（分钟）。

Logical_reads_per_session：在强制终止会话前，会话可读取的块数（无论块在数据缓冲区还是磁盘）。

Logical_read_per_call：在强制终止单个语句前，此语句可读取的块数（无论块在数据缓冲区还是磁盘）。

Private_sga：对于通过共享服务器体系结构连接的会话，允许会话的会话数据在SGA中占用的字节数（KB）。

Composite_limit：前面几个参数的加权和。这是一个高级功能，其需要的配置不在OCP考试范围。

例：

1）创建一个概要文件，如果出现两次口令失误，那么将账户锁定。

SQL> create profile two_error limit failed_login_attempts 2;

2）将概要文件分配给tim用户

SQL> alter user tim profile two_error;

3）tim尝试两次登录使用错误密码，系统报出ORA-28000错误

SQL> conn tim/fdfd

ERROR:

ORA-28000: 帐户已被锁定

Alter user tim account unlock 解锁。

4）sys为tim解锁

SQL> conn / as sysdba

SQL> alter user tim account unlock;

5）sys删掉了two_error概要文件

SQL> drop profile two_error;

//profile这部分操作参数较多，使用命令有些啰嗦，可以使用EM方式来管理，比较方便。

删除profile不会删除用户。

9.3 系统权限，对象权限，角色

9.3.1 权限的引入：

数据库安全分为系统安全和数据安全

系统安全：用户名和口令，分配给用户的磁盘空间及用户的系统操作

数据库安全：对数据库对象的访问及操作

用户具备系统权限才能够访问数据库

具备对象权限才能访问数据库中的对象

简而言之：权限（privilege）：system privilege and object privilege

1） system privilege：针对于database 的相关权限

系统权限通常由DBA授予（11g 有200多种，select distinct privilege from dba_sys_privs;也可被其他用户或角色授予）

典型DBA权限

CREATE USER

DROP USER

BACKUP ANY TABLE

SELECT ANY TABLE

CREATE ANY TABLE

典型用户需要的系统权限

CREATE SESSION

CREATE TABLE

CREATE SEQUENCE

CREATE VIEW

CREATE PROCEDURE

2） object privilege：针对于schema (用户）的object

对象权限有8种：ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE

主要的常用的对象	表	试图	序列	过程
alter	1		1
delete	1	1
Execute				1
index	1
insert	1	1
refrerences	1
select	1	1	1
update	1	1

9.3.2 权限的授权

授予系统权限语法：

GRANT sys_privs,[role] TO user|role|PUBLIC [WITH ADMIN OPTION]

//授予角色与系统权限的语法格式是一样的,所以可以并列在一个句子里赋权考点。

授予对象权限语法

GRANT object_privs ON object TO user|role|PUBLIC [WITH GRANT OPTION]

创建和删除角色

CREATE role myrole;

DROP role myrole;

9.3.3 角色的引入

角色相当于是N多权限的集合。可以包含系统角色，权限，对象权限等。

sys:

SQL> create user tim identified by tim; //建一个tim用户

SQL> conn tim/tim

ERROR:

ORA-01045: 用户 TIM 没有 CREATE SESSION 权限; 登录被拒绝

警告: 您不再连接到 ORACLE。

SQL> conn / as sysdba

已连接。

SQL> grant create session to tim; //授予tim系统权限create session

SQL> conn tim/tim

已连接。

SQL> select * from tab;

未选定行

SQL> create table a (id int);

create table a (id int)

第 1 行出现错误:

ORA-01031: 权限不足

sys:

SQL> grant create table to tim; //授予tim系统权限 create table

tim:

SQL> create table a (id int);

create table a (id int)

第 1 行出现错误:

ORA-01950: 对表空间 'USERS' 无权限

sys:

SQL> grant unlimited tablespace to tim; //授予tim系统权限unlimited tablespace，可以无限制的使用任何表空间

SQL> alter user tim quota 5m on users; //仅对于使用users 表空间加上了磁盘限额。

tim:

SQL> create table a (id int);

表已创建。

SQL> select * from session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

UNLIMITED TABLESPACE

CREATE TABLE

SQL>

列出oracle所有系统权限;

SQL> select distinct privilege from dba_sys_privs;

可以看出由于系统权限太过繁杂，oracle引入角色（role), 通过角色得授予是的用户访问管理得以大大简化。

sys:

SQL>drop user tim cascade;

SQL>create user tim identified by tim;

SQL>grant connect,resource to tim; 两个角色赋予TIM

SQL> select * from session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

UNLIMITED TABLESPACE

CREATE TABLE

CREATE CLUSTER

CREATE SEQUENCE

CREATE PROCEDURE

CREATE TRIGGER

CREATE TYPE

CREATE OPERATOR

CREATE INDEXTYPE

已选择10行。

需要注意一点：在resource角色里包含了unlimited tablesapce 权限，对于所有的表空间不限制配额，此权限太大，它包括可以访问system表空间，在实际应用中一般要将此权限收回，然后再对用户限制配额。权限不能限制配额。

SQL> create tablespace test_tbs datafile '/u01/oradata/timran11g/test01.dbf' size 10m;

SQL> create user tim identified by tim default tablespace test_tbs;

SQL> grant connect, resource to tim;

SQL> revoke unlimited tablespace from tim;

SQL> alter user tim quota 10m on test_tbs;

回收配额之后，不可以新建表，但是可以修改插入原来已经创建的表。

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='TIM';

9.3.4 几个有关权限的考点

1）sys, system 拥有普通用户的所有对象权限，并有代理授权资格。

2）系统权限里的any含义：

sys:

SQL> grant create any table to tim; tim可以给任何人（user a）建表。所创建的表不在tim下，在user a下可以查询到。

tim:

SQL> create table scott.t100 (id int);

3）可以使update对象权限精确到列： 赋予 update 某一列的权限时候，确保之前没有给过update全表的权限，如果有，先收回，在赋予。

scott:

SQL> grant select, update(sal) on emp to tim;

SQL> revoke update(sal) on emp from tim;

revoke update(sal) on emp from tim

第 1 行出现错误:

ORA-01750: UPDATE/REFERENCES 只能从整个表而不能按列 REVOKE

SQL> revoke update on emp from tim;

撤销成功。

4）对象权限的传递与回收：WITH GRANT OPTION 选项

先建立两个测试用户tim和ran （sys）

CREATE USER tim IDENTIFIED BY tim; GRANT CREATE SESSION TO tim;

CREATE USER ran IDENTIFIED BY ran; GRANT CREATE SESSION TO ran;

为tim用户授权（使用WITH GRANT OPTION）（scott）

GRANT SELECT ON emp TO tim WITH GRANT OPTION;

检查tim用户能否访问scott.emp ....

tim用户把该权限分给ran用户（tim）

GRANT SELECT ON scott.emp TO ran;

检查ran用户能否访问scott.emp ....

回收对象权限将会级联

REVOKE SELECT ON emp FROM tim; （scott）

回收了tim的对象权限后，tim二次分配给ran的对象权限也隐式的收回了。

5）系统权限的传递与回收：WITH ADMIN OPTION 选项

为tim用户授予系统权限

GRANT CREATE TABLE TO tim WITH ADMIN OPTION;（sys）

查看tim用户拥有的系统权限

SELECT * FROM USER_SYS_PRIVS;（tim）

USERNAME PRIVILEGE ADMIN_OPT

--------------- -------------------- ---------

tim CREATE TABLE YES

tim CREATE SESSION NO

二次分配给ran用户

GRANT CREATE TABLE TO ran; （tim）

查看ran用户系统权限

SELECT * FROM USER_SYS_PRIVS; （ran）

USERNAME PRIVILEGE ADMIN_OPT

--------------- -------------------- ---------

ran CREATE TABLE NO

ran CREATE SESSION NO

收回系统权限不会级联

REVOKE CREATE TABLE FROM tim; （sys）

回收了tim的系统权限后，tim二次分配给ran的系统权限并不会自动收回。

对象权限如果将tim用户删除，ran用户的对象权限也没有了。

6） 对象权限不能和系统权限一起给，因为语法不一样。

7）对象权限可以由owner给也可以由sysdba给。

9.3.5 与权限有关的数据字典

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，

sys:

1)建立myrole角色，把connect角色和create table 系统权限以及update on scott.emp对象权限放进myrole。

2)把myrole角色授给tim。

3)把create table 系统权限授给tim。

scott:

把 select on emp表的对象权限授给tim

如此tim用户有了如下角色和权限：

myrole(connect,create table，update on scott.emp)

create table

select on emp

我们从三个角度分析一下，如何里数据字典里查看tim拥有的角色和权限信息。

从dba角度想看：

数据库里有多少用户？

select username from dba_users;

看用户tim所拥有的系统权限

select * from dba_sys_privs where grantee='TIM';

看用户tim所拥有的对象权限

select * from dba_tab_privs where grantee='TIM';

看用户tim所拥有的角色（不包含角色里的角色）

select * from dba_role_privs where grantee='TIM';

查看这个角色里包含的角色

select * from dba_role_privs where grantee='MYROLE';

查看这个角色里包含的系统权限

select * from dba_sys_privs where grantee='MYROLE';

查看这个角色里包含的对象权限

select * from dba_tab_privs where grantee='MYROLE';

从当前用户角度看：和自己有关的角色都有什么内容？

角色里包含的角色

select * from role_role_privs;

角色里包括的系统权限

select * from role_sys_privs;

角色里包括的对象权限

select * from role_tab_privs;

查看和自己有关的角色（不含角色中含有的角色）

select * from user_role_privs;

查看和自己有关的系统权限（不含角色中的系统权限）

select * from user_sys_privs;

查看和自己有关的对象权限（不含角色中的对象权限）

select * from user_tab_privs;

查看和自己有关的系统权限(包括角色里的权限）

select * from session_privs;

从scott用户看是个什么情况

select * from all_tab_privs where grantee='TIM';

select * from all_tab_privs where table_name='EMP';

第十章 Oracle的事务和锁

10.1 什么是事务

必须具备以下四个属性，简称ACID 属性：

原子性（Atimicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。

一致性（Consistency）：一个查询的结果必须与数据库在查询开始的状态一致。

隔离性（Isolation）：对于其他部分来说，未完成的（也就是未提交的）事务必须不可见。

持久性（Durability）：事务完成后，它对数据库就不可以丢失这个事务，数据库通过日志能够保持事务的持久性

10.2 事务的开始和结束

10.2.1 事务开始于第一条DML语句

10.2.2 事务结束于：

1）COMMIT（提交）或ROLLBACK（回滚）

2）DDL语句被执行（提交）数据定义语言包括create alter truncate rename drop

3）DCL语句被执行（提交）数据控制语言包括 grant revoked

4）用户退出SQL*PLUS（正常退出是提交，非正常退出是回滚）还没提及就重启电脑

5）机器故障或系统崩溃（回滚）

6）shutdowm immediate(回滚）

考点：在一个事务里如果某个语句失败，则此语句回滚，其他任何DML语句将保持完好，而且不会提交！

用sys查看事务。Select * from v$transaction.

10.3 Oracle 的事务保存点功能

保存点就是允许在事务中设置一个标记，这个标记可以控制rollback的效果，即在一个事务中回滚掉最近的部分dml语句，保留下保存点之前的的dml语句，并使事务本身继续执行。也就是说回滚到保存点这个动作并不使事务结束。

SAVEPOINT实验

savepoint sp1；

delete from emp1 where empno=7900;

savepoint sp2;

update emp1 set ename='XIAOMAO' where empno=9000;

select * from emp1；

rollback to sp2;

select * from emp1；

rollback to sp1;

如果先回滚到sp1则无法再回滚到sp2.

10.4 读一致性与锁定

因为有事务才有锁的概念。我们关注的是Oracle的DML锁(又叫数据锁）

行锁(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锁）

10.5 加锁模式

第一种方式：自动加锁

做DML操作时，如insert，update，delete，以及select....for update由oracle自动完成加锁

scott: //用for update加锁

SQL> select * from dept where deptno>20 for update; 当其他用户在对结果中的记录操作时，会被挂起。

DEPTNO DNAME LOC

---------- -------------- -------------

30 SALES CHICAGO

40 OPERATIONS BOSTON

system: //试探，以防被锁住 以防被挂起。

SQL>select * from scott.dept for update nowait; 如果目标资源被锁住，则提示用户，不要把事物挂起。

SQL>select * from scott.dept for update wait 5; 如果目标资源被加了锁，则等待五秒钟，如果未解锁，则结束防止session被挂起。

1）对整个表for update 是不锁插入语句的。

2）参数wait 5 等5秒自动退出，防止被锁住。参数nowait，不等待。

select * from emp1 where deptno=10 for update;

select * from emp1 for update skip locked. 跳过已经上锁的记录，锁住余下的所有记录。

第二种方式：人工方式加锁，显示的用lock命令加锁。

lock table 表名 in exclusive mode.（一般限于后三种表锁）

10.6 五种表锁的含义：

ROW SHARE 行共享(RS)，允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁

ROW EXCLUSIVE 行排他(RX)，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁

SHARE 共享(S)，不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁

SHARE ROW EXCLUSIVE(SRX) 共享行排他，不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁

EXCLUSIVE (X)排他，其他用户禁止更新任何行，禁止其他用户同时加任何。

sql语句加锁模式许可其他用户的加锁模式

------------------------------------------------- --------------------- -------------------------

select * from table_name 无 RS,RX,S,SRX,X

insert, update, delete(DML操作） RX RS,RX

select * from table_name for update RX RS,RX

lock table table_name in row share mode RS RS,RX,S,SRX

lock table table_name in row exclusive mode RX RS,RX

lock table table_name in share mode S RS,S

lock table table_name in share row exclusive mode SRX RS

lock table table_name in exclusive mode X 无

观察锁的动态视图v$lock sid：session号。Lmode：锁级别。Block:阻塞了其他用户的DML操作，0表示没有阻塞 Request:请求上锁，0表示没有请求。

观察锁的动态视图dba_locks

TM锁分为两类：排他，共享

select * from v$lock;

select * from dba_locks where session_id=149;

10.7 死锁和解锁

10.7.1 Oracle自动侦测死锁，自动解决锁争用。

制作死锁案例：

scott:

SQL> select * from a;

ID_A

----------

brain::

SQL> select * from b;

ID_B

----------

100

200

Oracle的机制，当两个进程产生相互block的死锁时候，最早的进程会释放锁。

ORA-00060: deadlock detected while waiting for resource 等待资源时检测到死锁

scott: //改自己,不提交

update table a set id=11 where id=1;

brain: //改自己,不提交

update table b set id=1100 where id=100;

scott: //改对方,被锁住

update table brain.b id=1000 where id=100;

brain: //改对方,造成死锁

update table brain.b id=1000 where id=100;

10.7.2 管理员如何解鎖

先看事务的锁，sid是session号

select * from v$lock;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

0036ABAC 0036ABDC 128 TM 75788 0 3 0 2185 0

37901E04 37901E44 128 TX 524313 1487 6 0 2185 0

再看看sid=128这个session的的事务里最后一条sql语句是：

select a.sid,a.serial#,b.sql_text from v$session a,v$sql b where a.prev_sql_id=b.sql_id and a.sid=128;

SID SERIAL# SQL_TEXT

---------- ---------- --------------------------------------------------------------------------------

128 20 update a set id=3 where id=1

确定无误后

杀掉这个session

SQL>ALTER SYSTEM KILL SESSION '128,20';　

更详细的信息，可以从多个视图得出，相关的视图有：v$session,v$sql,v$seqtext,v$locked_object,v$sqlarea等等

第十一章，索引

加入一个B树和位图的图示。

11.1 索引结构及特点

以B树索引为例分析它的结构（图）balance tree 平衡树

索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化DML操作，Oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护。

当对表进行DML操作比如删除和插入等，系统也会自动的维护索引，当索引同步维护后才会打印执行完毕的信息。所以建立过多不必要的索引，对表经常操作，会使DML执行效率降低。Update表，相对而言，对索引的维护工作量会小很多。

如果SQL语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表，如果该语句同时还要访问除索引列之外的列，那么，数据库会使用rowid来查找表中的行，通常，为检索表数据，数据库以交替方式先读取索引块，然后读取相应的表块。

如果SQL语句没有用到被索引的列，那么数据库会遍历表，不会使用索引，所以要优化我们的查询语句，使之尽可能的使用索引，提高搜索速度。

B树中的叶子节点：存放了键值和rowid。

叶子节点是经过排序的。

11.2 索引适用那些情况

适合情况不适合情况

--------------------------------------------------------- -----------------------------------

经常用于WHERE子句或作为连接条件的列表很小

所含数据值范围比较的列列很少在查询中作为条件

含有大量空值的列多数情况下查询出大于总记录的5%的表

总是使用两个或多个组合列查询频繁更新的表

多数情况下只查询总记录小于5%的表

考点：索引里不存空值。

11.3 索引的类型与选项：

1）B树索引，

2）位图索引

建立B树索引选项包括：

唯一或非唯一索引（Unique or non_unique): 唯一索引指键值不重复。

组合索引(Composite)：绑定了两个或更多列的索引。（job,deptno）组合索引，当使用组合和job（即第一个字段）时，使用索引。当使用后一个字段查询时候，不使用索引，全表扫描。考点。

反向键索引(Reverse)：将字节倒置后组织键值。

函数索引(Function base)：以索引列值的函数值为键值去组织索引

压缩（Compressed)：重复键值只存储一次，就是说如果重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串。

升序或降序（Ascending or descending)：叶节点中的键值排列默认是升序的。

索引相关的数据字典

USER_INDEXES //索引主要信息

USER_IND_CULUMNS //索引列的信息

11.4 索引扫描方式

Oracle的执行计划常见的四种索引扫描方式：

1）索引唯一扫描(index unique scan)

通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中。

2）索引范围扫描(index range scan)

在非唯一索引上，可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。

使用index rang scan的3种情况：

(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)

(b) 在组合索引上，只使用部分列进行查询，导致查询出多行

3）索引全扫描(index full scan)

对整个index进行扫描，并且顺序的读取其中数据。

全Oracle索引扫描只在CBO(COST BASED基于成本的优化器，I/O读写，cpu,内存成本

)模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

并行顺序读。聚合函数常用到。

4）索引快速扫描(index fast full scan)

扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别是full scan是根据叶子块的双向列表顺序读取，读取的块是有顺序的，也是经过排序的，所以返回的列表也是排序的。而fast full scan 在读取叶子块时的顺序完全由物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。并行读。聚合函数常用到。

//分析器是根据要访问的数据量和索引的填充因子等属性判断使用RANG SCAN或FULL INDEX SCA

举例这四种索引扫描方式：

scott:

SQL> create table emp1 as select * from emp;

sys

SQL> set autotrace traceonly explain执行计划，不显示查询结果; set autotrace off 关闭计划。只能dba使用。

SQL> select empno from scott.emp1;

执行计划

----------------------------------------------------------

Plan hash value: 2226897347

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 182 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP1 | 14 | 182 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

scott:

SQL> create unique index emp1_idx on emp1(empno);

索引已创建。

sys:

SQL> select empno from scott.emp1 where empno=7788;

执行计划

----------------------------------------------------------

Plan hash value: 1995401140

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |

|* 1 | INDEX UNIQUE SCAN| EMP1_IDX | 1 | 13 | 0 (0)| 00:00:01 |

------------------------------------------------------------------------------

scott:

SQL> drop index emp1_idx;

SQL> create index emp1_idx on emp1(empno);

SQL>analyze index 索引名 validate structure;先分析，实行一条dml语句

SQL>select * from index_stats; 查看索引的分支节点,blocks等信息。

Hight层高，不要大于3

SQL> select empno from scott.emp1 where empno=7788;

执行计划

----------------------------------------------------------

Plan hash value: 253836959

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| EMP1_IDX | 1 | 13 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

SQL> select count(empno) from scott.emp1;

执行计划

----------------------------------------------------------

Plan hash value: 1026340756

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | INDEX FULL SCAN| EMP1_IDX | 14 | 182 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

scott：

SQL> insert into emp1 select * from emp1;

已创建14行。

SQL> /

.....

已创建14336行。

sys:

SQL> alter table scott.emp1 modify (empno not null); --因索引的叶子块不存空值，使empno字段非空。

SQL> select empno from scott.emp1; --数据库仅访问索引本身的数据。而无需访问表。

执行计划

----------------------------------------------------------

Plan hash value: 1892048637

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 28033 | 355K| 29 (0)| 00:00:01 |

| 1 | INDEX FAST FULL SCAN| EMP1_IDX | 28033 | 355K| 29 (0)| 00:00:01 |

---------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

可以看出：oracle在满足一定条件时会先选fast full scan,而不是full scan，因为前者的cost是29，比后者低许多。

可以加一行说明，强制使用full scan的执行计划，cost是100.

SQL> select /*+ index(emp1 emp1_idx) */ empno from scott.emp1;

执行计划

----------------------------------------------------------

Plan hash value: 4252953140

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 28033 | 355K| 100 (0)| 00:00:02 |

| 1 | INDEX FULL SCAN | EMP1_IDX | 28033 | 355K| 100 (0)| 00:00:02 |

-----------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

SQL>

查询索引的两个动态视图：

select * from user_indexes;

select * from user_ind_columns u where u.index_name='IND1';

select * from dba_indexes;

select * from user_ind_columns u where u.index_name=’IND1’;

11.5 索引的碎片问题

Oracle没有量化碎片，当执行了大量的DML语句，感觉索引比较慢了，然后当以下三种情况发生时，就说明积累的碎片改整理了。

1、 hight>=4

2、 pct_used <50% (排除，插入大量数据后，才创建的索引的情况。因为那时候创建的索引，pct_used值就有可能<50%)

3、 del_lf_rows/lf_rows >0.2 lf_row叶子行lf_blk叶子块

create index ind_t on t(t1)

select name,pct_used from index_stats where name='IND_T';(运行此句之前，先分析analyze index ind_1 validate structure。 Analyze是基于session的分析。)

create table t (id int);

create index ind_1 on t(id);

begin

for i in 1..1000000 loop

insert into t values (i);

if mod(i, 100)=0 then

commit;

end if;

end loop;

end;

analyze index ind_1 validate structure;

select name,pct_used from index_stats;

delete t where rownum<700000;

alter index ind_1 rebuild [online]; 重建索引

alter index ind_1 coalesce;

联机重建索引通常比删除后再重建要更实用，Oracle9i和10g一直提供联机索引重建功能--rebuild online,但由于涉及到使用表的排他锁，通常一段时间内其他用户的DML操作需要等待。

在Oracle11g中有了改进，实现了最终意义上的联机索引重建(rebuild index online)的特性，它重新设计了锁的模式，因此容许DML操作不受干扰。

11.6 索引不可见（invisible),11g新特性

在11g里， Oracle提供了一个新特性来降低直接删除索引或禁用索引的风险，那就是索引不可见（Index Invisible)。我们可以在创建索引时指定

unusable 索引不更新

考点：invisible和unusable的区别。

invisible属性或者用alter语句来修改索引为invisible(visible)

SQL> create table test (id int,name char(10));

SQL> create index test_idx on test(id) invisible;

SQL> alter index test_idx visible;

SQL> alter index test_idx invisible;

当索引被设为不可见后，实际上就是指该索引对于优化器不可见，而索引的正常更新仍然会由有Oracle自动完成的。对比11g前的unusable，（保存索引定义，不删除索引，也不更新索引）。

SQL> alter index test_idx unusable;

第十二章约束

12.1 什么是约束

约束是数据库能够实施业务规则以及保证数据遵循实体-关系模型的一种手段。

考点：如果违反约束，将自动回滚出现问题的整个语句，而不是语句中的单个操作，也不是整个事务。

12.2 约束的语法：

列级定义：只能引用一个列，表中可以有多个列级约束。

表级定义：引用一个或多个列，通常用来定义主键。

追加定义：建表后，再通过alter table命令追加的约束。

查看约束的两个数据字典视图

select * from user_constraints;

select constraint_name,column_name,table_name from user_cons_columns;

12.3 五种约束的写法

12.3.1非空约束

列级定义：

create table stud (id number(2) not null, name varchar(4))

追加非空约束：

not null约束比较特殊，一般只是列级定义和表外定义，当使用表外（追加）时，要使用modify关键字，

如：alter table emp1 modify ename not null;

或 alter table emp1 modify ename constraint xyz not null;

查看非空约束

select constraint_name,constraint_type,table_name

from user_constraints

where owner='SCOTT'

and table_name=UPPER('stud')

删除非空约束

alter table stud drop constraint SYS_C005454 //SYS_C005454是系统生成的约束名

12.3.2.唯一性约束（唯一性约束允许列中输入空值）null可以重复。

列级定义

SQL> create table a1(id number(2) unique,name varchar2(4));

表级定义

create table a2(id number(2),name varchar2(4),constraint id_uk unique(id));

追加定义

alter table a2 add CONSTRAINT id_uk UNIQUE (id);

删除唯一约束

alter table stud drop unique (id);

12.3.3 主键约束（每个表只能有一个主键，集唯一性和非空于一体）

主键约束的六种写法

列级定义

1）create table u1 (id char(10) primary key , name char(20)); -- 主键名字，Oracle起的

2）create table u2 (id char(10) constraint pk_u2 primary key , name char(20) ); -- 主键名字，自己指定

表级定义

3）create table u3 (id char(10) , name char(20) , primary key(id) ); -- 主键名字，Oracle起的

4）create table u4 (id char(10) , name char(20) , CONSTRAINTS pk_u4 primary key (id ) ); --主键名字，自己指定

追加定义

create table u5 (id char(10) , name char(20) );

5）alter table u5 add primary key(id);

6）alter table u5 add CONSTRAINT pk_u5 primary key (id ) ; --表外，后来加上主键。

删除主键约束

alter table dept1 drop constraint d_pk;

建立主键约束和唯一约束时，如果该列没有索引，oracle会自动在该列上创建一个唯一性索引。为什么？主键是非空且唯一。

关于主键和索引关联的问题:（这个地方考点较多)

SQL> create table t (id int, name char(10));

SQL> insert into t values (1, 'sohu');

SQL> insert into t values (2, 'sina')

SQL> commit;

SQL> create index t_idx on t(id);

下面这两句话是一样的效果，因为缺省情况下id列已经有索引t_id了，建主键时就会自动用这个索引（考点）。不能删除主键（或unique）创建的索引。删除约束之后索引还在。

SQL> alter table t add constraint pk_id primary key (id);

SQL> alter table t add constraint pk_id primary key (id) using index t_idx;

SQL> select CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME from user_constraints;

CONSTRAINT_NAME TABLE_NAME INDEX_NAME

------------------------------ ------------------------------ ------------------------------

FK_DEPTNO EMP

PK_DEPT DEPT PK_DEPT

PK_EMP EMP PK_EMP

PK_ID T T_IDX

SQL> alter table t drop constraint pk_id; //删除了约束，索引还在，本来就是借用的索引。

SQL> select index_name from user_indexes;

INDEX_NAME

------------------------------

PK_EMP

PK_DEPT

T_IDX

SQL> drop table t purge; //t_idx是和t表关联的，关键字purge使表和索引一并永久删除了。

也可以使用using字句在建表建约束建约束一条龙下来，当然primary key 也会自动使用这个索引(考点）。

SQL> create table t (id int, name char(10), constraint pk_id primary key(id) using index

(create index t_idx on t(id)));

表已创建。

SQL> select CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME from user_constraints;

CONSTRAINT_NAME TABLE_NAME INDEX_NAME

------------------------------ ------------------------------ ------------------------------

FK_DEPTNO EMP

PK_DEPT DEPT PK_DEPT

PK_EMP EMP PK_EMP

PK_ID T T_IDX

SQL> select index_name from user_indexes;

INDEX_NAME

------------------------------

PK_EMP

PK_DEPT

T_IDX

12.3.4.外键约束（引用完整性约束）

作用：是为了和同一个表或其他表的主关键字或唯一关键字建立连接关系，外键值必须和父表中的值匹配或者为空值。

考点：外键约束和unique约束都可以有空值。

列级定义

SQL> create table dept1 as select * from dept;

SQL> alter table dept1 add primary key(deptno);

SQL> create table emp100 (empno int,deptno int references dept1(deptno),deptno2 int);

//外键的列级定义不写foreign key关键字，有点不习惯。

表级定义

SQL> create table emp200 (empno int,deptno int,sal int,foreign key(deptno) references dept1(deptno));

追加定义

ALTER TABLE emp100 ADD CONSTRAINT e_fk FOREIGN KEY(deptno2) REFERENCES dept1(deptno);

关于ON DELETE CASCADE关键字

SQL> drop table emp100 purge;

SQL> drop table emp200 purge;

SQL> create table emp1 as select * from emp;

SQL> ALTER TABLE emp1 ADD CONSTRAINT e_fk FOREIGN KEY(deptno) REFERENCES dept1(deptno);

测试：

delete from dept1 where deptno=30

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.E_FK) violated - child record found

删除外键约束

alter table emp1

drop constraint e_fk

使用ON DELETE CASCADE关键字重建外键（级联删除） 不能用alter修改 ,只能建表的约束的时候用。

ALTER TABLE emp1

ADD CONSTRAINT e_fk FOREIGN KEY(deptno)

REFERENCES dept1(deptno) ON DELETE CASCADE

测试：

delete from dept1 where deptno=30

1 row deleted.

再查看emp1表的deptno

select * from emp1;

ON DELETE CASCADE要慎用，主表中删除一行数据就可能引发从表中大量数据丢失。

12.3.5.CHECK约束

列级定义

SQL> create table emp100 (empno int,sal int check (sal>0),comm int);

表级定义

SQL> create table emp200 (empno int,sal int,comm int,check(sal>1000));

追加定义

SQL> alter table emp200 add constraint e_no_ck check (empno is not null);

验证

SQL> insert into emp200 values(null,1,1);

insert into emp200 values(null,1,1)

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (SCOTT.E_NO_CK)

12.3.6 级联约束(考点）

测试表

CREATE TABLE test2 (

pk NUMBER PRIMARY KEY,

fk NUMBER,

col1 NUMBER,

col2 NUMBER,

CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test2, 一个表的时候可以省略主键pk

CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),

CONSTRAINT ck2 CHECK (col2 > 0)

)

当删除列时, 看看会发生什么?

ALTER TABLE test2 DROP (col2); //这句可以执行

ALTER TABLE test2 DROP (pk); //这句不能执行，在constraint ck1 中使用了该列，约束级联问题）

ALTER TABLE test2 DROP (fk); //这句可以执行

ALTER TABLE test2 DROP (col1); //这句不能执行，在constraint ck1 中使用了该列，约束级联问题）要带上cascade constraints才行

ALTER TABLE test2 DROP (pk) cascade constraint; //所有与pk列有关的约束统统随该列被删掉。

ALTER TABLE test1 DROP (col1) cascade constraints;

CASCADE CONSTRAINTS 将丢弃在删除列上的唯一键或主键约束。

12.3.7 约束的四种状态重点

启用约束:

enable validate :启用约束,创建索引,对已有及新加入的数据执行约束. 默认的。

enable novalidate :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据. 约束只对新数据生效。

禁用约束:

disable novalidate:关闭约束,删除索引,可以对约束列的数据进行修改等操作. 约束不生效。

disable validate :关闭约束,删除索引,不能对表进行插入/更新/删除等操作,注意是相当于对整个表的read only设定，不管列上有没有约束. 不常用。

更改约束状态是一个数据字典更新，将对所有session有效。

举例：

以 scott user 中的 emp﹑dept 两个表分别复制emp1,dpet1来进行例证.

SQLcreate table emp1 as select * from emp;

SQL>create table dept1 as select * from dept;

建立约束

SQL>alter table emp1 add constraint pk_emp1 primary key (empno)

;

SQL>alter table dept1 add constraint pk_dept1 primary key (deptno);

SQL>alter table emp1 add constraint fk_deptno1 foreign key (deptno) references dept1(deptno);

SQL> select constraint_name,constraint_type,status,validated from user_constraints ;

CONSTRAINT_NAME C STATUS VALIDATED

------------------------------ - -------- -------------

FK_DEPTNO1 R ENABLED VALIDATED

FK_DEPTNO R ENABLED VALIDATED

PK_DEPT P ENABLED VALIDATED

PK_EMP P ENABLED VALIDATED

PK_EMP1 P ENABLED VALIDATED

PK_DEPT1 P ENABLED VALIDATED

6 rows selected.

一般来讲﹐默认的情况下 enable 时就会 validate ;disable时就会 novalidate;

而这两个不同的动作分开来讲就是:

enable/disable 是指是否现在要启动/失效这个约束.

validate/novalidate 是指是否对现在的数据进行合法性的檢查.

现实操作中因为有一些违规的操作需要我们处理﹐如改变主细中有外FK关联的表结构﹐truncate表...

所以我们要打断数据库的约束关联后再进行操作。

SQL> alter table emp1 disable constraint FK_DEPTNO1 ;

Table altered

这样我们就可以操作 emp1 表中的 deptno 字段而不受外键的限制[无须在 dept1表中存在或值为 NULL了].

而当我们没有打断这种关联时﹐像下面这些操作都会出错:

SQL> delete from dept1 where deptno =10;

delete from dept1 where deptno =10

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.FK_DEPTNO1) violated - child record found

SQL> update emp1 set deptno=50 where empno=7934;

update emp1 set deptno=50 where empno=7934

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.FK_DEPTNO1) violated - parent key not found

此外如果没有 disable 关联时即使子表中没有资料﹐父表也不能 truncate 。

因为 truncate 是一条 DDL 指令﹐当进行truncate 时,它并不会去一条条的记录去检查父中的这条记录在

子表是否已有引用﹐而是去检查这类的 constraint_name 的 status 是否为 disable﹐否则不能truncate.

SQL> truncate table dept1;

truncate table dept1

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> alter table emp1 enable novalidate constraint FK_EMP_DEPT ;

Table altered

SQL> truncate table dept;

Table truncated

前面讲到 disable/enable 与 [novalidate|validate] 两种操作就有四种组态.

而因为 enable validate 与 disable novalidate 是默认的所以就不以讨论了。

============================================================

以下来说明 enable novalidate 这种组态的实用用法

我们常用于当我们在表中输入了一些测试数据﹐而上线时我们并不想去清除这些

违规数据﹐只想从以后开始才执行这些约束时可以用到。

假设已经建立了一个emp1表，也插入了数据，如果有一天想在empno上加入primary key 但是之前有不符合（not null+unique)约束的，怎样才能既往不咎呢？

create table emp1 as select * from emp;(约束并没有考过来）

update emp1 set empno=7788 where empno=7369;(设置一个重号)

alter table emp1 add constraint pk_emp1 primary key (empno);因要检查主键唯一索引，拒绝建立此约束。

任何违反（not null+unique)的update或insert操作将被拒绝。

alter table emp1 add constraint pk_emp1 primary key (empno) enable novalidate;（这句话也不行，为什么？原因是唯一索引在捣乱）。

create index empno_index on emp1(empno);建一个索引，一定要一个普通索引，不能是唯一索引，普通索引不受unquie的限制）

alter table emp1 add constraint pk_emp1 primary key (empno) enable novalidate;（这句话可以了）。

从此之后，这个列的DML操作还是要符合（not null+unique)。

----------------------------------------------------------------------------------------------------------------------

12.3.8 延迟约束

可延迟（deferrable）的约束可以指定为：

1. initially immediate（初始化立即执行）

2. initially deferred （初始化延迟执行）

其中Initially Immediate意思是每次向表中添加数据,修改数据或是从表中删除数据时.都要检查这个约束.(这与约束默认行为相同).

而Initially Deferred.意思是只有事务被提交时才检查这个约束.

1、deferrable必须在简历约束的时候指明否则不可再追加。

2、多个DML组成一个事务，COMMIT时候如果有一个不符合约束，则所有DML都被回退。

3、在定义的时候，initially状态将应用于所有SESSION有效。Immediate和deferred的切换对当前session有效。 考点。

在你操作table时，例如insert数据。以主键约束为例

如果有违反主键唯一性的约束时，系统应该报错。

现在你要决定系统什么时候报错。

1，initially immediate 在执行sql的时候报错。

2，initially deferred 在commit的时候报错。

比方说，2个人先后在执行insert同一笔资料。

如果第一种方案，则后面的那个人无法执行sql。

如果第二种方案，则2个人都可以执行sql，但是先commit的人会成功。后commit的人会被提示报错。

可以通过查询User_Constraints表获得当前所有关于约束的系统信息.下面关于约束User_Constraints表常用信息字段说明:

Owner //约束所有者

Constraint_name //约束名称

Constraint_Type //约束类型:

[C:代表Check或Not Null约束. P:主键约束. R:外键约束. U:唯一约束. V:Check option束.O:Readonly 只读约束]

Table //name——约束定义针对表名

Status_ //约束的状态 Enable或Disable 可用或不可用

Deferrable //是否为延迟约束值为:Deferrable或Not Deferred.

Deferred: //是否采用延迟值为:IMMEDIate或Deferred.

举例：

SQL>create table dept1 as select * from dept;

SQL>create table emp1 as select * from emp;

SQL>alter table dept1 add constraint pk_dept1 primary key (deptno);

SQL>alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) deferrable;

SQL>update emp1 set deptno=50 where empno=7900; (因为不检查约束，可以暂时违反完整性约束）

SQL>insert into emp1 empno=7788 where ename='SMITH';（暂时通过，插入记录也只在提交（commit）时检查）

SQL>commit; （这时候检查了，报错！）

考点：一旦有一条DML语句违反了约束，整个提交都将失败，全军覆没。

关于deferrable 可延迟，提醒以下几点：

1）约束的默认方式下是：enable/validate 和not defferable。

2）如果创建约束时没有指定deferrable 那么无法在后来使约束成为延迟约束的（只有再重建约束时指定它是延迟约束）

3）一个constraint如果被定义成deferrable，那么这个constraints可以在deferred和imediate两种状态间相互转换

4）因为是deferrable,可以使用set constraint字句了。

SQL>set constraint fk_emp1 deferred; --约束fk_emp1 已经进入了临时延迟状态

以上3）和4）合起来就相当于：

SQL>alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) deferrable initially deferred

----如果删除某个表，那么和表相关的索引，约束都自动被删除，但是视图和序列依然保留着。

第十三章视图

13.1 为什么使用视图

限制数据的存取：用户只能看到基表的部分信息。

使得复杂的查询变得容易：视图中的数据可能来自多个表。

使得数据相对比较独立：从项目开发的角度，模块对应视图，模块包含多个表，模块发生变化后只需修改相应的视图，对应的表的结构无需修改。

代表对同一数据的不同视角：不同部门的员工只能看到本部门的信息。

13.2 语法

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

[(alias[, alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY];

Select text from user_views;视图的数据字典。

考点：如果select字句中有表达式（sal+1000），需要给表达式加别名。

13.2.1 FORCE作用：可以先建视图，后建基表

Create （or replace） force view view1 as select * from test1;

因为基表不存在，执行报错。但是试图已经建立。

13.2.2 WITH CHECK OPTION作用：

对视图where子句进行约束。不允许对限定关键字修改。

SQL>create view view2 as select * from emp where deptno=10 with check option;

无法对DEPTNO字段键值进行修改。

13.2.3 WITH READ ONLY作用：

禁止对视图执行DML操作

SQL>create view view3 as select * from emp where deptno=10 with read only;

13.2.4 关于视图注意事项

1）定义视图的查询中列中有函数或表达式要加别名考点

2）只要视图中的数据不是来自基表的原始数据，就尽量不要对该数据做DML操作。考点

比如：create view V1 as select sal+1000 abc from emp1;则这样的试图不能修改，因为基表emp1中没有abc列。

3）视图的DML操作

含有如下情况，则无法删除视图中的数据:

含有聚组函数

含有GROUP BY 子句

含有 DISTINCT 关键字

含有ROWNUM 这个伪列

含有如下情况，不能修改该视图中的数据:

上面提到的任何一种情况。

列是由表达式来进行定义的

含有如下情况，不能增加该视图中的数据:

上面提到的任何一种情况。

在基表中包含有 NOT NULL约束的列，然而该列并没有在视图中出现

比如，试图只映射了部分字段，不包括NOT NULL字段，如果插入的话，将对基表中的NOT NULL字段补空值，违反约束。

13.3 复杂视图的更新，键保留表概念。（不考）

select * from user_views

-----------------------------------------------------------------------------

如果建立了视图想查看其中的定义，可以访问如下视图dba_views中的text字段(long型)；

自建一pl/sql过程，参照一下。

declare

v_text dba_views.text%type;

v_name dba_views.view_name%type;

begin

select text, view_name into v_text,v_name FROM dba_views WHERE view_name='V1';

dbms_output.put_line(v_name||' define is :'||v_text);

end;

-------------------------------------------------

sysdba下执行：

set serverout on;

declare

aa dba_views.text%type;

begin

select text into aa FROM dba_views WHERE OWNER='SCOTT';

dbms_output.put_line(aa);

end;

第十四章同义词

14.1 私有同义词;

sys用户看scott的emp:

select * from scott.emp;

create synonym syn1 for scott.emp;

select * from syn1;(私有同义词只能在当前用户下访问,scott使用不了)

14.2 公有同义词；大家都可使用,

但是创建同义词是要权限的(create synonym,create public synonym);

create public synonym syn2 for ...

（新用户要访问public synonym（代表emp表）, 需要有访问基表的权限。

select * from tab; (tab是什么？）；用下面的查看语句:

select * from all_objects where object_name='TAB';

select * from all_synonym where synonym_name='TAB';

14.3 关于同义词的几个要点：

1）私有同义词是基于模式的对象，要么在自己的模式中，要么必须用模式名限定。

比如 scott.syn1 syn1是同义词，scott是模式名

2）公有同义词不是模式对象，不能用模式名做前缀。（考点）

3）私有和公有同义词同名但指向不同的别名时，私有同义词优先。

For 后面的对象不一样的时候，私有优先。

4）同义词被引用的对象（表或视图）被删除，同义词仍然存在，这同视图类似，重新创建对象，下次访问同义词时自动编译。（考点）

第十五章序列

15.1 序列是生成唯一整数值的结构，它的典型用途是用于主键值。

结合真题演示

伪列nextval, currval用法 下一个值，当前值

CREATE SEQUENCE dept_deptno

INCREMENT BY 10

START WITH 50

MAXVALUE 100

CYCLE 转一圈回来之后，就不从50开始，而从1开始 考点。

NOCACHE

第一次要引用一下 nextval伪列

select dept_deptno.nextval from dual;

以后就有currval伪列值了。

select dept_deptno.nextval from dual;

15.2 几点说明：

1）最简单的建立序列只需要create sequence 序列名就可以，注意缺省值，起始是1，步长也是1。

2) 如果启用cache，缺省只有20个号，经验表明这个数量会不够，可以设置多一些，根据需要10000个也可以。Cache 10000

3) cycle其实没有什么意义，因为它使序列发出重复值，这对于基于序列是主键值的用法是个问题。

4) 创建序列后可以使用alter命令再进行修改。alter命令和create命令基本相同，只有一点区别：alter命令不能设置起始值。如果要重启该序列，唯一的办法是删除并重新创建它。

5) 循环后初始是从1开始的，不管原来的值是如何设的(考点）。

第十六章外部表

16.1 读取外部表的方法

OS下建目录，

$mkdir -p /u01/oradata/timran11g/aaa_dir

chmod 777 /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

2)，创建目录,并用DBA进行授权;

sys:

sql>create directory test_dir as '/u01/oradata/timran11g/aaa_dir';

sql>grant read,write on directory test_dir to scott;

3).使用被授权的用户scott创建外部表:

create table test_table

(empno int,

ename char(20),

sal int,

deptno int)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY test_dir

ACCESS Parameters

(

RECORDS DELIMITED BY NEWLINE

badfile 'bad_dev.txt'

LOGFILE 'log_dev.txt'

FIELDS TERMINATED BY ',' 说明外部表里每个值之间的分隔符。

MISSING FIELD VALUES ARE NULL

(empno,ename,sal,deptno)

)

LOCATION('t1.txt')

);

4),进行SELECT 操作看是否正确;

SQL>select * from test_table

SQL>select * from test_table where deptno=10;

查看外部表的两个字典视图

DBA_EXTERNAL_TABLES;

DBA_EXTERNAL_LOCATIONS;

SQL>SELECT OWNER, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_PARAMETERSFR

FROM DBA_EXTERNAL_TABLES;

第二册部分

第十七章 insert语句总结

17.1 单行insert

第一类insert语句：一次插入一行

1）SQL> create table a (id int,name char(10) default 'aaa'); //name列指定了default值

2）SQL> insert into a values(1,'abc'); //表a后没有所选列，values必须指定所有字段的值。

3）SQL> insert into a values(2,default); //同上，name字段用default占位。

4）SQL> insert into a values(3,null); //表a后没有所选列，name字段用null占位。

5）SQL> insert into a (id) values(4); //表a后有选择字段，未选定的字段如果指定了default,则以default的值代替null

6）SQL> insert into (select id from a) values (5); //这种形式本质同上，只不过表a的形式以结果集代之。

NOTE：insert命令会有约束的问题，不符合约束条件的insert不能成功。

SQL> select * from a;

ID NAME

---------- ----------

1 abc

2 aaa

4 aaa

5 aaa

SQL> commit;

7）insert WITH CHECK OPTION的用法

SQL> insert into (select id from a where id<100 WITH CHECK OPTION) values (20);

SQL> select * from a;

ID NAME

---------- ----------

1 abc

2 aaa

4 aaa

5 aaa

20 aaa

6 rows selected.

SQL> rollback;

SQL> select * from a;

ID NAME

---------- ----------

1 abc

2 aaa

4 aaa

5 aaa

SQL> insert into (select id from a where id<100 WITH CHECK OPTION) values (101);

insert into (select id from a where id<100 WITH CHECK OPTION) values (101)

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> select * from a;

ID NAME

---------- ----------

1 abc

2 aaa

4 aaa

5 aaa

这样的语法看起来很特殊，其实就是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。

看看这句话的另一种情况：

SQL> insert into (select name from a where id<100 WITH CHECK OPTION) values ('NBA');

insert into (select name from a where id<100 WITH CHECK OPTION) values ('NBA')

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

note:上例是想说明如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入（考点）。

SQL> insert into (select id,name from a where id<100 WITH CHECK OPTION) values (10,'tim');

SQL> insert into (select name from a where id<100) values ('NBA'); //不加WITH CHECK OPTION则在插入时不会检查。

SQL> insert into (select name from a where id<100) values ('NBA');

17.2 多行insert

一次插入多行语法上去掉了values选项。

SQL> create table b as select * from a where 1>2; //建立一个空表b。结构来自a表， where 1>2 使没有符合的记录被筛选出来.

SQL> insert into b select * from a where name='aaa'; //插入的是结果集，注意没有values选项。

SQL> select * from b;

ID NAME

---------- ----------

2 aaa

4 aaa

5 aaa

SQL> insert into b(id) select id from a where id in(1,3); //使用子查询（结果集）插入，对位，注意b表没有default。复制表结构，除了非空约束、字段名、字段类型、字段长度之外，其他都复制不过来。

SQL> select * from b;

ID NAME

---------- ----------

2 aaa

4 aaa

5 aaa

17.3 Multitable insert

一条INSERT语句可以完成向多张表的插入任务。

insert all与insert first

1.创建表T并初始化测试数据，此表作为数据源。

create table t (x number(10), y varchar2(10));

insert into t values (1,'a');

insert into t values (2,'b');

insert into t values (3,'c');

insert into t values (4,'d');

insert into t values (5,'e');

insert into t values (6,'f');

commit;

2.查看表T的数据

SQL>select * from t;

X Y

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

3.创建表T1和T2，作为我们要插入的目标表。

SQL>create table t1 as select * from t where 0=1;

Table created.

SQL>create table t2 as select * from t where 0=1;

Table created.

17.3.1 第一种多表插入方法INSERT ALL

unconditional insert all（无条件insert all)

1）完成INSERT ALL插入

SQL>insert all into t1 into t2 select * from t;

12 rows created.

这里之所以显示插入了12条数据，实际上表示在T1表中插入了6条，T2表插入了6条，一共是12条数据。

2）验证T1表中被插入的数据。

SQL>select * from t1;

X Y

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

3）验证T2表中被插入的数据。

SQL>select * from t2;

X Y

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

OK，完成INSERT ALL命令的使命。

17.3.2 第二种多表插入方法INSERT FIRST

conditional insert first(有条件insert first)

1）清空表T1和T2

SQL>delete from t1; truncate table t1;

SQL>delete from t2; truncate table t2;

SQL>commit;

2）完成INSERT FIRST插入

SQL>insert first when x>=5 then into t1 when x>=2 then into t2 select * from t;

5 rows created.

处理逻辑是这样的，首先检索T表查找X列值大于等于5的数据（这里是“5，e”和“6，f”）插入到T1表，然后将前一个查询中出现的数据排除后再查找T表，找到X列值大于等于2的数据再插入到T2表（这里是“2，b”、“3，c”和“4，d”）。注意INSERT FIRST的真正目的是将同样的数据只插入一次。

3）验证T1表中被插入的数据。

SQL>select * from t1;

X Y

---------- ----------

5 e

6 f

4）验证T2表中被插入的数据。

SQL>select * from t2;

X Y

---------- ----------

2 b

3 c

4 d

5)为真实的反映“数据只插入一次”的目的，我们把条件颠倒后再插入一次。

SQL>delete from t1;

SQL>delete from t2;

SQL> insert first when x>=2 then into t1 when x>=5 then into t2 select * from t;

5 rows created.

SQL>select * from t1;

X Y

---------- ----------

2 b

3 c

4 d

5 e

6 f

SQL>select * from t2;

no rows selected

OK，目的达到，可见满足第二个条件的数据已经包含在第一个条件里，所以不会有数据插入到第二张表。

同样的插入条件，我们把“INSERT FIRST”换成“INSERT ALL”，对比一下结果。

SQL>delete from t1;

5 rows deleted.

SQL>delete from t2;

0 rows deleted.

SQL>insert all when x>=2 then into t1 when x>=5 then into t2 select * from t; //conditional insert all

7 rows created.

SQL>select * from t1;

X Y

---------- ----------

2 b

3 c

4 d

5 e

6 f

SQL>select * from t2;

X Y

---------- ----------

5 e

6 f

17.3.3 第三种，旋转Insert (pivoting insert) 考点

create table sales_source_data (

employee_id number(6),

week_id number(2),

sales_mon number(8,2),

sales_tue number(8,2),

sales_wed number(8,2),

sales_thur number(8,2),

sales_fri number(8,2)

);

insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);

create table sales_info (

employee_id number(6),

week number(2),

sales number(8,2)

);

看上面的表结构,现在将要sales_source_data表中的数据转换到sales_info表中,这种情况就需要使用旋转Insert

示例如下:

insert all

into sales_info values(employee_id,week_id,sales_mon)

into sales_info values(employee_id,week_id,sales_tue)

into sales_info values(employee_id,week_id,sales_wed)

into sales_info values(employee_id,week_id,sales_thur)

into sales_info values(employee_id,week_id,sales_fri)

select employee_id,week_id,sales_mon,sales_tue,

sales_wed,sales_thur,sales_fri

from sales_source_data;

从该例子可以看出,所谓旋转Insert是无条件 insert all 的一种特殊应用,但这种应用被oracle官方,赋予了一个pivoting insert的名称,即旋转insert.

第十八章 DML语句-MERGE

把数据从一个表复制到另一个表，插入新数据或替换掉老数据是每一个ORACLE DBA都会经常碰到的问题。

在ORACLE9i以前的年代，我们要先查找是否存在老数据，如果有用UPDATE替换，否则用INSERT语句插入，其间少不了还有一些标记变量等等，繁琐的很。

ORACLE9i后专为这种情况提供了MERGE语句，使这一工作变得异常轻松，MERGE命令能够在一个SQL语句中对一个表同时执行inserts和updates操作。

MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.

Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的

2、UPDATE和INSERT子句可以加WHERE子句

3、ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

首先创建示例表：

create table PRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2(30),

CATEGORY VARCHAR2(30)

);

insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');

insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');

insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');

insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');

insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');

commit;

create table NEWPRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2(30),

CATEGORY VARCHAR2(30)

);

insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');

insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');

insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');

insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');

commit;

SQL>select * from products;

PRODUCT_ID PRODUCT_NAME CATEGORY

---------- ------------------------------ ------------------------------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS IS50 ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER DVD

SQL> select * from newproducts;

PRODUCT_ID PRODUCT_NAME CATEGORY

---------- ------------------------------ ------------------------------

1502 OLYMPUS CAMERA ELECTRNCS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

1700 WAIT INTERFACE BOOKS

1、可省略的UPDATE或INSERT子句

在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据

表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:

SQL>MERGE INTO products p

USING newproducts np

ON (p.product_id = np.product_id)

WHEN MATCHED THEN

UPDATE

SET p.product_name = np.product_name,

p.category = np.category;

3 rows merged.

SQL> commit;

SQL> select * from products;

PRODUCT_ID PRODUCT_NAME CATEGORY

---------- ------------------------------ ------------------------------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS CAMERA ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

SQL>

在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行. 它们的产品名字和种类被更新为表newproducts中的值. 下面例子省略UPDATE

子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理. 从这个例子你能看到

PRODUCT_ID=1700的行被插入到表PRODUCTS中.

SQL> MERGE INTO products p

USING newproducts np

ON (p.product_id = np.product_id)

WHEN NOT MATCHED THEN

INSERT

VALUES (np.product_id, np.product_name,

np.category);

1 row merged.

select * from products;

PRODUCT_ID PRODUCT_NAME CATEGORY

---------- ------------------------------ ------------------------------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS CAMERA ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

1700 WAIT INTERFACE BOOKS

6 rows selected.

SQL> commit;

2、新增加的DELETE子句

Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.

下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行.

SQL>MERGE INTO products p

USING newproducts np

ON (p.product_id = np.product_id)

WHEN MATCHED THEN

UPDATE

SET p.product_name = np.product_name,

p.category = np.category

DELETE WHERE (p.category = 'ELECTRNCS')

WHEN NOT MATCHED THEN

INSERT

VALUES (np.product_id, np.product_name, np.category);

4 rows merged.

SQL> select * from products;

PRODUCT_ID PRODUCT_NAME CATEGORY

---------- ------------------------------ ------------------------------

1501 VIVITAR 35MM ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

1700 WAIT INTERFACE BOOKS

产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件

, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所

以被更新为表NEWPRODUCTS中的值.

第十九章几个语句的用法

19.1 with语句

好处：

1、使用with语句，可以避免在select语句中重复书写相同的语句块。

2、with语句将该子句中的语句块执行一次并存储到用户的临时表空间中。

3、使用with语句可以提高查询效率。

举例：一个with语句完成三个动作

建立一个dept_costs，保存每个部门的工资总和，

建立一个avg_cost，根据dept_costs求出所有部门总工资的平均值，

最后显示出部门总工资值小于部门总工资平均值的那些部门的信息(dname)。

WITH

dept_costs AS (

SELECT d.dname, SUM(e.sal) AS dept_total

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY d.dname ),

avg_cost AS (

SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs )

SELECT * FROM dept_costs

WHERE dept_total <

(SELECT dept_avg FROM avg_cost)

ORDER BY dname

DNAME DEPT_TOTAL

-------------- ----------

ACCOUNTING 8750

SALES 9400

SQL>

可以分三个部分来看:

第一AS建立一个dept_costs，保存每个部门的工资总和

第二个AS建立一个avg_cost，根据刚才的dept_costs求出所有部门总工资的平均值

最后，SELECT * FROM... 显示部门总工资值小于部门总工资平均值的那些部门的信息

19.2 DDL操作及模式对象

19.2.1 对象名称空间

模式是一种逻辑结构，它对应于用户，每建一个用户就有一套模式与之对应。

我们通常说对象的唯一标识符是前缀为模式名的对象名称，那么对象的命名需要有名称空间的概念

名称空间定义了一组对象类型，在这个组中所有名称都必须由模式和名称唯一标识，不同的名称空间中的对象可以共享相同的名称。

1）同一模式下的相同对象类型是不可以重名的。

2）表，视图，序列，同义词是不同的对象，它们在一个名称空间里也是不可以重名的，比如scott下不可以使一个表名和一个视图名同名。

3）索引、约束有自己的名称空间，所以在scott模式下，可以有表A,索引A和约束A共存。

考点：在同一个模式中，表、视图和同义词不能同名。

19.2.2 使用子查询创建表

语法：create table [schema.]table as subquery;

如：create table 表2 as select * from 表1;是最简洁的复制表的命令，但是要注意，索引不会被复制到表2，约束只有not null约束能够带到表2里来，如果有default也不会被复制表2。

19.2.3 要drop某列前建议使用set unused

原理：清除掉字典信息（撤消存储空间），不可恢复。

如果你有这个需求，要删除某一个表格上的某些字段，但是由于这个表格拥有非常大量的资料，如果你在尖峰时间直接执行 ALTER TABLE ABC DROP （COLUMN）；可能会收到 ORA-01562 - failed to extend rollback segment number string，这是因为在这个删除字段的过程中你可能会消耗光整个RBS，造成这样的错误出现，因此这样的做法并不是一个好方法，就算你拼命的加大RBS空间来应付这个问题，也不会是个好主意。

Oracle推荐：

可以使用 SET UNUSED 选项标记一列（或多列），使该列不可用。不可恢复

使用DROP UNUSED column 选项删除被被标记为不可用的列。

语法：

ALTER TABLE table SET UNUSED (COLlist多个) 或者 ALTER TABLE table SET UNUSED COLUMN col单个;

ALTER TABLE table DROP UNUSED COLUMNS;

有一道考题问：当使用alter table set unused column语法之后，会对其相关联的视图，约束、同义词和索引有什么作用？

答：视图和同义词会失效，其中同义词再次引用即可重新编译，但是视图重新编译会报错，约束和索引会被自动删除。

SET UNUSED不会真地删除字段。

除了alter table drop field外，也可以

alter table set unused field;

alter table drop unused columns;

set unused系统开销比较小，速度较快，所以可以先set unuased，然后在系统负载较小时，再drop。如系统负载不大，也可以直接drop。

不管用何种方法，都不会收回空间。

19.3 group by rollup，group by cube,以及grouping的用法

Oracle数据库中的rollup配合group by命令使用，可以提供信息汇总功能（与"小计"相似）

CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。

示例如下：

SQL> select job,deptno,sal from emp;

JOB DEPTNO SAL

--------- ---------- ----------

CLERK 20 800

SALESMAN 30 1600

SALESMAN 30 1250

MANAGER 20 2975

SALESMAN 30 1250

MANAGER 30 2850

MANAGER 10 2450

ANALYST 20 3000

PRESIDENT 10 5000

SALESMAN 30 1500

CLERK 20 1100

CLERK 30 950

ANALYST 20 3000

CLERK 10 1300

14 rows selected.

19.3.1 rollup的用法

SQL> select job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno);

JOB DEPTNO TOTAL_SAL

--------- ---------- ----------

CLERK 10 1300

CLERK 20 1900

CLERK 30 950

CLERK 4150

ANALYST 20 6000

ANALYST 6000

MANAGER 10 2450

MANAGER 20 2975

MANAGER 30 2850

MANAGER 8275

SALESMAN 30 5600

SALESMAN 5600

PRESIDENT 10 5000

PRESIDENT 5000

29025

15 rows selected.

19.3.2 cube的用法

SQL> select job,deptno,sum(sal) total_sal from emp group by cube(job,deptno);

JOB DEPTNO TOTAL_SAL

--------- ---------- ----------

29025

10 8750

20 10875

30 9400

CLERK 4150

CLERK 10 1300

CLERK 20 1900

CLERK 30 950

ANALYST 6000

ANALYST 20 6000

MANAGER 8275

MANAGER 10 2450

MANAGER 20 2975

MANAGER 30 2850

SALESMAN 5600

SALESMAN 30 5600

PRESIDENT 5000

PRESIDENT 10 5000

18 rows selected.

可以看出,用了rollup的group by子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，

同时也会对聚合列生成一个合计列。

select deptno,job,sum(sal) from emp group by deptno,job;

会对每一个不同的dept,job生成一行独立的结果。

而select deptno,job,sum(sal) from emp group by rollup(deptno,job);

的结果中除了上述的结果结果之外，还会对每一个deptno进行一个小结，并单独生成一行，除此之外还会对所有的sal求和并生成一列。

这里的group by后面我们仅仅接了2列，实际上我们可以使用更多列的，这样的话oracle就会以从右向左的方式来进行逐个小结。

这里需要注意的是使用了group by和rollup后，其后面的列要用括号括起来，否则将会出现ORA-00933: SQL 命令未正确结束的错误。

看看grouping、grouping_id函数是什么？

19.3.3 grouping函数

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。

grouping_id函数

可以返回0,1,2,3...可以分别表示小计，合计等信息。

SQL>

SQL> select job,deptno,sum(sal) total_sal,grouping(job) job_grp, grouping(deptno) deptno_grp, grouping_id(job,deptno) total_grp from

emp group by cube(job,deptno);

JOB DEPTNO TOTAL_SAL JOB_GRP DEPTNO_GRP TOTAL_GRP

--------- ---------- ---------- ---------- ---------- ----------

29025 1 1 3

10 8750 1 0 2

20 10875 1 0 2

30 9400 1 0 2

CLERK 4150 0 1 1

CLERK 10 1300 0 0 0

CLERK 20 1900 0 0 0

CLERK 30 950 0 0 0

ANALYST 6000 0 1 1

ANALYST 20 6000 0 0 0

MANAGER 8275 0 1 1

MANAGER 10 2450 0 0 0

MANAGER 20 2975 0 0 0

MANAGER 30 2850 0 0 0

SALESMAN 5600 0 1 1

SALESMAN 30 5600 0 0 0

PRESIDENT 5000 0 1 1

PRESIDENT 10 5000 0 0 0

已选择18行。

但是我们大多数情况下需要在查询的结果集的汇总列加上“合计”，怎么办呢？用grouping和grouping_id函数，然后再用decode函数判断一下是否为空

就可以了

SQL>select grouping_id(job,deptno) as group_col,sum(sal) total_sal from emp group by rollup(job,deptno);

GROUP_COL TOTAL_SAL

---------- ----------

0 1300

0 1900

0 950

1 4150

0 6000

1 6000

0 2450

0 2975

0 2850

1 8275

0 5600

1 5600

0 5000

1 5000

3 29025

已选择15行。

SQL>select decode(grouping_id(job,deptno),1,'合计',job||deptno) as group_col,sum(sal) total_sal from emp group by rollup(job,deptno);

GROUP_COL TOTAL_SAL

------------------------------------------------- ----------

CLERK10 1300

CLERK20 1900

CLERK30 950

合计 4150

ANALYST20 6000

合计 6000

MANAGER10 2450

MANAGER20 2975

MANAGER30 2850

合计 8275

SALESMAN30 5600

合计 5600

PRESIDENT10 5000

合计 5000

29025

15 rows selected.

第二十章 ORACLE分层查询start with&connect by

20.1 树结构查询

ORACLE是一个关系数据库管理系统,它用表的形式组织数据,在某些表中的数据还呈现出树型结构的联系。例如，我们现在讨论雇员信息表EMP，其中含

有雇员编号（EMPNO）和经理（MGR）两列，通过这两列反映出来的就是雇员之间领导和被领导的关系。他们之间的这种关系就是一种树结构。

图1.1 EMP表树结构图

7839

------------------------------------------------------------------------------------------------

| | |

7566 7698 7782

----------- ------------------------------------------ ----

| | | | | | | |

7788 7902 7499 7521 7654 7844 7900 7943 ---- ----

| |

7876 7369

扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：

第一步：从根节点开始；

第二步：访问该节点；

第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；

第四步：若该节点为根节点，则访问完毕，否则执行第五步；

第五步：返回到该节点的父节点，并执行第三步骤。

总之：扫描整个树结构的过程也即是中序遍历树的过程。

20.2 树结构的描述

树结构的数据存放在表中，数据之间的层次关系即父子关系，在表的每一行中都有一个表示父节点的MGR（除根节点外）。

在SELECT命令中使用CONNECT BY 和 START WITH 子句可以查询表中的树型结构关系。其命令格式如下：

SELECT ...

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}

[START WITH]；

其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIOR运算符必须放置在连接关系的两列中某一个的前面。PRIOR运算符在的一侧表示父节点，另一侧表示子节点，从而确定查找树结构时的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

例1 以树结构方式显示EMP表的数据。

SQL>select empno,ename,mgr

from emp

connect by prior empno=mgr

start with empno=7839

EMPNO ENAME MGR

---------- ---------- ----------

7839 KING

7566 JONES 7839

7788 SCOTT 7566

7876 ADAMS 7788

7902 FORD 7566

7369 SMITH 7902

7698 BLAKE 7839

7499 ALLEN 7698

7521 WARD 7698

7654 MARTIN 7698

7844 TURNER 7698

7900 JAMES 7698

7782 CLARK 7839

7934 MILLER 7782

14 rows selected.

仔细看empno这一列输出的顺序，就是上图树状结构每一条分支（从根节点开始）的结构。

20.3 关于PRIOR

运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。

PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式

。如：

CONNECT BY PRIOR EMPNO=MGR //父节点与子节点的关系，在表内存储的数据上体现

PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向

上的方式。例如：

CONNECT BY EMPNO=PRIOR MGR

在这种方式中也应指定一个开始的节点。

例2 从SMITH节点开始自底向上查找EMP的树结构。

SQL>select empno,ename,mgr

from emp

connect by empno=prior mgr

start with empno=7369

EMPNO ENAME MGR

---------- ---------- ----------

7369 SMITH 7902

7902 FORD 7566

7566 JONES 7839

7839 KING

SQL>

在这种自底向上的查找过程中，只有树中的一枝被显示，这是因为，在树结构中每一个节点只允许有一个父节点，其查找过程是从开始节点起，找到其

父节点，再由其父节点向上，找父节点的父节点。这样一直找到根节点为止，结果就是树中一枝的数据。

备注：例2的另外一种写法

SQL>SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR MGR=EMPNO

START WITH EMPNO=7369

20.4 定义查找起始节点

在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构

树的一枝。

例3 查找7566(JONES)直接或间接领导的所有雇员信息。

SQL＞SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH EMPNO=7566

EMPNO ENAME MGR

---------- ---------- ----------

7566 JONES 7839

7788 SCOTT 7566

7876 ADAMS 7788

7902 FORD 7566

7369 SMITH 7902

START WITH 不但可以指定一个根节点，还可以指定多个根节点。

例4 查找由FORD和BLAKE 领导的所有雇员的信息。

SQL>SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME IN ('FORD','BLAKE')

EMPNO ENAME MGR

---------- ---------- ----------

7902 FORD 7566

7369 SMITH 7902

7698 BLAKE 7839

7499 ALLEN 7698

7521 WARD 7698

7654 MARTIN 7698

7844 TURNER 7698

7900 JAMES 7698

8 rows selected.

在自底向上查询树结构时,也要指定一个开始节点，以此开始向上查找其父节点，直至找到根节点，其结果将是结构树中的一枝数据。

20.5 使用LEVEL

在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。

在查询中，可以使用伪列LEVEL显示每行数据的有关层次。LEVEL将返回树型结构中当前节点的层次。

伪列LEVEL为数值型，可以在SELECT 命令中用于各种计算。

例5 使用LEVEL改变查询结果的显示形式。

SQL> COLUMN LEVEL FORMAT A20

SQL> SELECT LPAD(LEVEL,LEVEL*3,' ')

as "LEVEL",EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME='KING'

LEVEL EMPNO ENAME MGR

-------------------- ---------- ---------- ----------

1 7839 KING

2 7566 JONES 7839

3 7788 SCOTT 7566

4 7876 ADAMS 7788

3 7902 FORD 7566

4 7369 SMITH 7902

2 7698 BLAKE 7839

3 7499 ALLEN 7698

3 7521 WARD 7698

3 7654 MARTIN 7698

3 7844 TURNER 7698

3 7900 JAMES 7698

2 7782 CLARK 7839

3 7934 MILLER 7782

14 rows selected.

在SELECT使用了函数LPAD，该函数表示以LEVEL*3个空格进行填充，由于不同行处于不同的节点位置，具有不同的LEVEL值，因此填充的空格数将根据各

自的层号确定，空格再与层号拼接，结果显示出这种层次关系。

20.6 节点和分支的裁剪

在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，

但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

SQL>SELECT LPAD(LEVEL,LEVEL*3,' ')

as "LEVEL",EMPNO,ENAME,MGR

FROM EMP

WHERE ENAME!='SCOTT'

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME='KING'

LEVEL EMPNO ENAME MGR

-------------------- ---------- ---------- ----------

1 7839 KING

2 7566 JONES 7839

4 7876 ADAMS 7788

3 7902 FORD 7566

4 7369 SMITH 7902

2 7698 BLAKE 7839

3 7499 ALLEN 7698

3 7521 WARD 7698

3 7654 MARTIN 7698

3 7844 TURNER 7698

3 7900 JAMES 7698

2 7782 CLARK 7839

3 7934 MILLER 7782

13 rows selected.

在这个查询中，仅剪去了树中单个节点SCOTT。若希望剪去树结构中的某个分支，则要用CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分

支，既要剪除分支上的单个节点，也要剪除其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

例8．显示KING领导下的全体雇员信息，除去SCOTT领导的一支。

SQL>SELECT LPAD(LEVEL,LEVEL*3,' ')

as "LEVEL",EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

AND ENAME!='SCOTT'

START WITH ENAME='KING'

LEVEL EMPNO ENAME MGR

-------------------- ---------- ---------- ----------

1 7839 KING

2 7566 JONES 7839

3 7902 FORD 7566

4 7369 SMITH 7902

2 7698 BLAKE 7839

3 7499 ALLEN 7698

3 7521 WARD 7698

3 7654 MARTIN 7698

3 7844 TURNER 7698

3 7900 JAMES 7698

2 7782 CLARK 7839

3 7934 MILLER 7782

12 rows selected

这个查询结果就与例7不同，除了剪去单个节点SCOTT外，还将SCOTT的子节点ADAMS剪掉，即把SCOTT这个分支剪掉了。

当然WHERE子句可以和CONNECT BY子句联合使用，这样能够同时剪掉单个节点和树中的某个分支。

例9．显示KING领导全体雇员信息，除去雇员SCOTT，以及BLAKE领导的一支。

这个留给大家实践吧：）

20.7 排序显示

像在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。

例10 以EMPNO的顺序显示树结构EMP 中的数据。

SQL> SELECT LPAD(LEVEL,LEVEL*3,' ')

as "LEVEL",EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME='KING'

ORDER BY EMPNO

LEVEL EMPNO ENAME MGR

-------------------- ---------- ---------- ----------

4 7369 SMITH 7902

3 7499 ALLEN 7698

3 7521 WARD 7698

2 7566 JONES 7839

3 7654 MARTIN 7698

2 7698 BLAKE 7839

2 7782 CLARK 7839

3 7788 SCOTT 7566

1 7839 KING

3 7844 TURNER 7698

4 7876 ADAMS 7788

3 7900 JAMES 7698

3 7902 FORD 7566

3 7934 MILLER 7782

14 rows selected.

在使用SELECT 语句来报告树结构报表时应当注意，CONNECT BY子句不能作用于出现在WHERE子句中的表连接。如果需要进行连接，可以先用树结构建立

一个视图，再将这个视图与其他表连接，以完成所需要的查询。

第二十一章 Oracle 时间类型及Timezone

21.1 Timezone引入的背景

Oracle中有很多特性支持国际化，如字符集、时区等等。和时区相关的两个日期类型是：

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

timestamp时间点 interval 时间段

1）Database的timezone可以在创建数据库的时候指定，如：

CREATE DATABASE db01

...

SET TIME_ZONE='+08:00';

或者在数据库创建之后通过alter database语句修改，但是只有重启数据库后有效：

ALTER DATABASE SET TIME_ZONE='+08:00';

查看数据库时区信息：

SQL> select dbtimezone from dual;

DBTIME

------

+08:00

2）session的timezone可以简单通过alter session语句修改：

ALTER SESSION SET TIME_ZONE='+08:00';

查看session时区信息：

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

---------------------------------------------------------------------------

+08:00

21.2 模拟北京、东京、伦敦三地的时区，进一步理解Timezone。

全球的一个统一的时间应由时刻+时区来指定，

比如2005-4-6 14:00:00.000并不能说清楚到底这是日本的下午2点还是中国的下午两点。

2005-4-6 14:00:00.000 +8:00 才是北京时间

2005-4-6 14:00:00.000 +9:00 则是东京时间

假设有一个online meeting system

DB服务器在英国 dbtimezone (+0:00) [select dbtimezone from dual;]

一个客户端c-cn在中国 session timezone (+8:00) [select sessiontimezone from dual;]

一个客户端c-jp在日本 session timezone (+9:00) [select sessiontimezone from dual;]

管理客户端c-en在英国 session timezone (+0:00) [select sessiontimezone from dual;]

===================================================

Timestamp 不能（没有）包含任何时区信息

===================================================

DB有TABLE定义如下：

create table meeting_table1( id number(10) primary key, ctime timestamp );

中国用户插入了第一个会议，早上8点开会

insert into meeting_table1 values (1, to_timestamp('2005-06-29 8:00:00.0', 'yyyy-mm-dd hh24:mi:ss.ff'));

commit;

日本用户也插入了第二个会议，早上8点开会

(如果是中文字符集也可以使用下列格式转换一下：

insert into meeting_table1 values (2, to_timestamp('2005-06-29 8:00:00.0', 'yyyy-mm-dd hh24:mi:ss.ff'));

commit;

英国的管理员查询一下这张表，发现两个会议是同时的，

ID CTIME

---------- ------------------------------

1 2005-06-29 08:00:00,000000

2 2005-06-29 08:00:00,000000

而实际上应该日本的会议比中国的早一个小时。

英国的管理员如果想参加2号会议的话，他到底该几点去呢？

八点？0点？前一天的晚上11点？数据库完全不能给他一个明确的答复。

c-cn, c-jp来查询也都得到相同的模糊结果：

ID CTIME

---------- ------------------------------

1 2005-06-29 08:00:00,000000

2 2005-06-29 08:00:00,000000

===================================================

Timestamp with time zone 显式包含时区信息

===================================================

DB有TABLE定义如下：

create table meeting_table2( id number(10) primary key, ctime timestamp with

time zone);

中国、日本用户同样插入上例中的两个会议

英国的管理员查询表时，返回的结果就清晰多了：

select * from meeting_table2;

ID CTIME

---------- ----------------------------------------

1 2005-06-29 08:00:00,000000 +08:00

2 2005-06-29 08:00:00,000000 +09:00

他可以知道 meeting 2是在东九区的早上八点开始的，去参加的话，

前一天晚上11：00他就要接进web meeting了。

c-cn, c-jp来查询也都与c-en结果相同

结果都包含时区信息，所以是精确的，不可能被混淆。

===================================================

Timestamp with local time zone 隐式包含时区信息

===================================================

用Timestamp with local time zone插入或显示的时间信息会根据

客户session里面时区的不同自动转换：

* 插入时，从客户端的时区转到数据库时区

* 显示时，从数据库时区转到客户端的时区

DB有TABLE定义如下：

create table meeting_table3( id number(10) primary key, ctime timestamp with

local time zone);

c-cn, c-jp同样插入上例中的两个会议

c-en 的查询结果：

ID CTIME

---------- ----------------------------------------

1 2005-06-29 00:00:00,000000

2 2005-06-28 23:00:00,000000

c-cn 的查询结果：

ID CTIME

---------- ----------------------------------------

1 2005-06-29 08:00:00,000000

2 2005-06-29 07:00:00,000000

c-jp 的查询结果：

ID CTIME

---------- ----------------------------------------

1 2005-06-29 09:00:00,000000

2 2005-06-29 08:00:00,000000

这样连换算都不需要了，每个客户查出来的时间直接就是客户端所在的区域的当地时间。

21.3（INTERVAL YEAR TO MONTH）和（INTERVAL DAY TO SECOND）时间间隔数据类型

从Oracle 9i开始，按照SQL99标准，增加了时间间隔型数据INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND。

用YEAR TO MONTH表示时间间隔大小时要在年和月之间用一个连字符(-) 连接。

而DAY TO SECOND表示时间间隔大小时要在日和时间之间用一个空格连接。

举个例子来说，下面是2年6个月的时间间隔的表示方法：

INTERVAL "2-6" YEAR TO MONTH

下面的例子表示3天12个小时30分钟6.7秒：

INTERVAL "3 12:30:06.7" DAY TO SECOND(1)

时间间隔可以为正，也可以为负。它们可以从各种TIMESTAMP数据类型中加上或者减去，从而得到一个新的TIMESTAMP数据类型。

因为有精度问题，相对来讲，INTERVAL DAY TO SECOND比INTERVAL YEAR TO MONTH要复杂一些

看看下面时间间隔关于INTERVAL DAY TO SECOND的字面含义说明

INTERVAL ‘3’ DAY //时间间隔为3天

INTERVAL ‘2’ HOUR //时间间隔为2小时

INTERVAL ‘25’ MINUTE //时间间隔为25分钟

INTERVAL ‘45’ SECOND //时间间隔为45秒

INTERVAL ‘3 2’ DAY TO HOUR //时间间隔为3天零2小时

INTERVAL ‘3 2:25’ DAY TO MINUTE //时间间隔为3天零2小时25分

INTERVAL ‘3 2:25:45’ DAY TO SECOND //时间间隔为3天零2小时25分45秒

INTERVAL ‘123 2:25:45.12’ DAY(3) TO SECOND(2) //时间间隔为123天零2小时25分45.12秒；天的精度是3位，秒的部分的精度是2位.

INTERVAL ‘-3 2:25:45’ DAY TO SECOND //时间间隔为负数，值为3天零2小时25分45秒

INTERVAL ‘1234 2:25:45’ DAY(3) TO SECOND //时间间隔无效，因为天的位数超过了指定的精度3

INTERVAL ‘123 2:25:45.123’ DAY TO SECOND(2) //时间间隔无效，因为秒的小数部分的位数超过了指定的精度2

21.4 关于numtoyminterval函数和numtodsinterval函数

numtoyminterval 用于产生是个指定的时间间隔，可以作为interval year to month 型的数据插入到表中。

SQL> select sysdate,sysdate+numtoyminterval(1,'month'),sysdate+numtoyminterval(1,'year') from dual;

SYSDATE SYSDATE+NUMTOYMINTE SYSDATE+NUMTOYMINTE

------------------- ------------------- -------------------

2012-07-09 08:18:59 2012-08-09 08:18:59 2013-07-09 08:18:59

可以看出 numtoyminterval产生了一个月的时间间隔和一个年的时间间隔

SQL> select sysdate,sysdate+numtodsinterval(1,'day'),sysdate+numtodsinterval(1,'second') from dual;

SYSDATE SYSDATE+NUMTODSINTE SYSDATE+NUMTODSINTE

------------------- ------------------- -------------------

2012-07-09 09:09:06 2012-07-10 09:09:06 2012-07-09 09:09:07

可以看出 numtodsinterval产生了一天的时间间隔和一秒的时间间隔

第二十二章正则表达式

22.1 ORACLE中的支持正则表达式的函数主要有下面四个：

1，REGEXP_LIKE ：与LIKE的功能相似

2，REGEXP_INSTR ：与INSTR的功能相似

3，REGEXP_SUBSTR ：与SUBSTR的功能相似

4，REGEXP_REPLACE ：与REPLACE的功能相似

它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，

但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。

22.2 POSIX 正则表达式由标准的元字符（metacharacters）所构成：

'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。

'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'。

'.' 匹配除换行符之外的任何单字符。

'?' 匹配前面的子表达式零次或一次。

'+' 匹配前面的子表达式一次或多次。

'*' 匹配前面的子表达式零次或多次。

'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。

'( )' 标记一个子表达式的开始和结束位置。

'[]' 标记一个中括号表达式。

'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。

\num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。

22.3 字符簇：

[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:space:]] 任何白字符。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[:punct:]] 任何标点符号。

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

各种操作符的运算优先级

\转义符

(), (?:), (?=), [] 圆括号和方括号

*, +, ?, {n}, {n,}, {n,m} 限定符

^, $, anymetacharacter 位置和顺序

22.4 Oracle REGEXP_LIKE介绍和例子

--创建表

create table fzq (id varchar(4),value varchar(10));

--数据插入

insert into fzq values ('1','1234560');

insert into fzq values ('2','1234560');

insert into fzq values ('3','1b3b560');

insert into fzq values ('4','abc');

insert into fzq values ('5','abcde');

insert into fzq values ('6','ADREasx');

insert into fzq values ('7','123 45');

insert into fzq values ('8','adc de');

insert into fzq values ('9','adc,.de');

insert into fzq values ('10','1B');

insert into fzq values ('10','abcbvbnb');

insert into fzq values ('11','11114560');

insert into fzq values ('11','11124560');

--regexp_like

--查询value中以1开头60结束的记录并且长度是7位

select * from fzq where value like '1____60';

select * from fzq where regexp_like(value,'1....60');

--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。

--使用like就不是很好实现了。

select * from fzq where regexp_like(value,'1[0-9]{4}60');

-- 也可以这样实现，使用字符集。

select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');

-- 查询value中不是纯数字的记录

select * from fzq where not regexp_like(value,'^[[:digit:]]+$');

-- 查询value中不包含任何数字的记录。

select * from fzq where regexp_like(value,'^[^[:digit:]]+$');

--查询以12或者1b开头的记录.不区分大小写。

select * from fzq where regexp_like(value,'^1[2b]','i');

--查询以12或者1b开头的记录.区分大小写。

select * from fzq where regexp_like(value,'^1[2B]');

-- 查询数据中包含空白的记录。

select * from fzq where regexp_like(value,'[[:space:]]');

--查询所有包含小写字母或者数字的记录。

select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');

--查询任何包含标点符号的记录。

select * from fzq where regexp_like(value,'[[:punct:]]');

22.5 REGEXP_REPLACE(字符串替换函数)

REPLACE 函数是用另外一个值来替代串中的某个值。例如，可以用一个匹配数字来替代字母的每一次出现。REPLACE 的格式如下所示：

原型：regexp_replace(x,pattern[,replace_string[,start[,occurence[match_option]]]])

每个参数的意思分别是：

x 待匹配的函数

pattern 正则表达式元字符构成的匹配模式

replace_string 替换字符串

start 开始位置

occurence　匹配次数

match_option　匹配参数，这里的匹配参数和regexp_like是完全一样的，可参考前面的一篇文章。

举例来讲：

SQL> select regexp_replace('hello everybody,047courses will be over soon,thanks.','b[[:alpha:]]{3}','one') from dual;

REGEXP_REPLACE('HELLOEVERYBODY,047COURSESWILLBEOVER

---------------------------------------------------

hello everyone,047courses will be over soon,thanks.

posted @ 2013-09-04 22:14 cphmvp 阅读(1825) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

cphmvp

简单 简约 It's me

ocp11g培训内部教材_051课堂笔记(047)_SQL

OCP 051课堂笔记

第一章、Oracle命令类别：

第二章、SQL的基本函数

2.1 单行函数与多行函数

2.2 单行函数的几种类型

2.2.1 字符函数

2.2.2 数值函数

2.2.3 日期函数

2.2.4 几个特殊函数

第三章、SQL的数据类型

3.1 四种基本的常用数据类型

3.1.1 字符型：

3.1.2 数值型：

3.1.3 日期型：

3.1.4 LOB型：

3.2 数据类型的转换

3.2.1 隐性类型转换：

3.2.2 显性类型转换

第四章、WHERE子句中数据的比较和运算

4.1 隐式比较与显式比较：

4.2 运算符优先级：

4.3 用BETWEEN AND操作符来查询出在某一范围内的行.

4.4 模糊查询及其通配符：

4.5 用IN操作符来检验一个值是否在一个列表中

4.6 交互输入变量符&和&&的用途：

4.7 使用逻辑操作符: AND; OR; NOT

第五章 分组函数

5.1 五个分组函数

5.2 在组函数中使用NVL函数

5.3 GROUP BY创建数据组

5.4 分组函数的嵌套

第六章、数据限定与排序

6.1 简单查询语句执行顺序

6.2 排序(order by)

6.3 空值（null)

6.3.1 空值参与运算或比较时要注意几点。

6.3.2 处理空值的几种函数方法：

第七章、复杂查询之一：多表连接技术

7.1 简单查询的解析方法：

7.2 多表连接

7.2.1 交叉连接（笛卡尔积）

7.2.2 内连接或简单连接

7.2.3 外连接包括左外连接，右外连接，全外连接

7.2.4 自连接

7.2.5 自然连接

7.3 集合运算

7.3.1 union

7.3.2 union all

7.3.3 intersect

7.3.4 minus (注意谁minus谁）

7.4 集合运算中只要类型能匹配上就可以连接

7.5 关于order by 使用别名排序的问题：

第八章、复杂查询(下）：子查询与多列子查询

8.1 单行子查询 采用单行比较运算符（>，<，=，<>)

8.2 在多行子查询中使用all

8.3 在多行子查询中使用any

8.4 在多行子查询中使用in

8.5 多列子查询 多行多列

8.6 非成对比较多列子查询

8.7 not in 在子查询中的空值问题：

8.8 from子句中使用子查询(这个也叫内联视图）

8.9 关联子查询与非关联子查询

8.9.1 非关联子查询：

8.9.2 关联子查询：

8.9.3 关联子查询操作符exists(）

8.10 简单查询与复杂查询练习题：

第九章 用户访问控制

9.1 创建和管理数据库用户账户

9.1.1用户的缺省表空间

9.1.2 配额的概念：

9.2 管理概要文件

9.3 系统权限，对象权限，角色

9.3.1 权限的引入：

9.3.2 权限的授权

9.3.3 角色的引入

9.3.4 几个有关权限的考点

9.3.5 与权限有关的数据字典

简单简约 It's me

第五章分组函数

8.1 单行子查询采用单行比较运算符（>，<，=，<>)

8.5 多列子查询多行多列

第九章用户访问控制

第十二章约束

12.3.2.唯一性约束（唯一性约束允许列中输入空值）null可以重复。

12.3.3 主键约束（每个表只能有一个主键，集唯一性和非空于一体）

12.3.4.外键约束（引用完整性约束）

12.3.7 约束的四种状态重点

第十三章视图

第十四章同义词

第十五章序列

第十六章外部表

第十九章几个语句的用法