数据库

一、 引入

1、sqlplus /nolog
conn / as sysdba
将scott用户解锁，设置密码为tiger
alter user scott identified by tiger account unlock;

sqlplus 命令行窗口客户端
nolog 不产生日志
sysdba oracle上的最高权限进入，认证系统，若是以administrator进入则不需要验证密码

2、sqlplus:oracle的客户端工具（纯字符界面）
pl/sql developer:oracle的客户端工具（图形界面）
D:\software\oracle11g_64\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

客户端->(IP)->硬件服务端->(端口)->对应软件
客户端的端口一般随机产生，服务端的端口是固定的。

3、oracle是关系数据库，用于存储数据
关系型数据库：体现关系非常的强烈
NOSQL数据库：是能够解决关系型数据库不好解决的问题的数据库（key value，列式）

与oracle平级的数据库有mysql，db2, mariadb

二、oracle体系结构

oracle server = instance + database（单机下）
databse = data files + redo log files + control files + archived log files
instance = SGA(System Global Area 内存) + background process
SGA = database buffer cache + redo log buffer + shared pool + java pool + large pool
Instance = memory + background process
memory = SGA + PGA（Service自动生成）

dirty date ： （脏数据）在Instance的SGA中上存在，但是磁盘还是不存在的数据。

CTL - Control files
DBF - Data files
LOG - Redo Log files

数据更新：
（数据）Database Buffer cache->(DBWR更新)->Data files
（日志）Redo Log Buffer->(LGWR更新)->Redo Log files

Control files 记录文件所在的位置
日志文件通常是两个，暂时称为1,2，每一个都会写两份一样的且在不同的地方，提高容错率，写完1，再写2，写2的时候，对1进行归档。

Parameter files - 记录 Control files 所在的位置，分区的大小
Library cache - 存储sql的解析后的结果，即为编译解析后的sql语句
Data Dictionary Cache - 存放系统的表
SMON - 系统的一个进程，当服务端出现故障的时候，用以维护数据的完整性（系统监控，垃圾回收）
PMON - 监视进程(进程监控)
CRPT - 检查，恢复数据时发挥作用，减少恢复的时间

三、SQL(structure query language)

增加(insert)
修改(update)
删除(delete)
查询(select)

行- 记录 列- 字段
SELECT * FROM 表名
* 可以表示所有的列，也可以用单一的列名来代替，若是有多列，那么在列名中间用逗号隔开。

关键字大写，每行仅仅只写一个关键字，其他的小写，但是从严格意义上来说，可以不区分大小写。
字符列左，日期数字列右。
空值等同 Java中的null，既不是空格，也不是0，任何包含空的运算的结果为空。
as 别名 as可以用空格代替，重命名，可以使用汉字，当别名中有空格存在的时候，那么需要用单引号括起来表示里面是一个字符串，否则会报错。
|| 连接符 字符串用单引号
DISTINCT 去重 SELECT DISTINCT ename
DESC 表名—— sqlplus的命令，不能直接在sql中运行，但是可以在sqlplus里运行，例如cmd和Command里面，需要用分号表示一句话的结束。

四、 优先级

PS：AND 优先级 高于OR

五、基本知识

字符对于大小写敏感
<> 亦或是 ！= 都表示 不等于，但是前者更加常见
IN（a，b） 表示的是a和b，不是a到b
WHERE ename LIKE ‘%\%%’ ESCAPE(‘\’) 表示查找ename含有%的所有
ESCAPE 表示括号内的东西为 转意符 ，可以随意写
排序 ORDER BY 升序ASC(可以省略) 降序DESC（sql语句）

PS：要先查询再排序，即为WHERE在ORDER BY 的前面，一个DESC只管最近的一个的倒序。

六、函数

单行函数：单进单出
多行函数：多进单出

要求掌握：LOWER UPPER INITCAP CONCAT LENGTH SUBSTR LPAD RPAD TRIM
SUBSTR：截取 （‘’，初始位置，截取长度）：初始位置为1或者0都表示从第一个开始， 当为-1的时候表示从后面开始。

ROUND: 四舍五入
TRUNC: 截断
MOD: 求余
eg： ROUND(45.926, 2) 表示保留两位小数，四舍五入，当后面的数字为负数的时候，那么 这个时候就会从个位数向前数。

同java一样，若是知道需要转换的类型，那么就应当直接注明，变成显式转换，而不是让其自己去判断，这样做非常消耗内存。

显示转换： TO_需要转换成为的类型名称（‘’，‘格式’）

dual： 哑表 伪表 只有1列 一般用于计算
SYSDATE: 返回当前的日期时间。
NVL（列名，0）： 当此列中的值为空的时候，按0处理，不为空就按照本身。
NVL2（1,2,3）： 1不为空，按照2来处理，1为空的时候，按照3来处理。

IF - THEN - ELSE:(分为以下两种)
（1）
–CASE
SELECT empno,ename,job,sal,CASE job WHEN ‘SALESMAN’ THEN 1.2*sal
WHEN ‘CLERK’ THEN 1.5*sal
WHEN ‘ANALYST’ THEN 1.1*sal
ELSE sal end

FROM emp

（2）
–decode
SELECT empno,ename,job,sal,decode(job,’SALESMAN’,1.2*sal,
‘CLERK’,1.5*sal,
‘ANALYST’,1.1*sal,
sal)
FROM emp

七、多表查询

笛卡尔集 和 叉集：多个表连接的时候，每一个表的每一行数据都会与其他表的每一行数据连接。（在连接表的时候首先应当需要注意的问题。）
避免措施：加入限制条件(WHERE、JOIN ON)

在oracle里，在连接时对于少的那一个表，可以在其表名末尾加上一个（+），以完善数据。
但是并不能在两边都加上（+）已达到满外连接的效果。

等值连接 非等值连接 外连接 外左连接 外右连接 自连接 满外连接
LIFT (OUTER) JOIN 左边多 / RIGHT (OUTER) JOIN 右边多
FULL (OUTER) JOIN 两边多
–等值连接
SELECT e.,d.
FROM emp e,dept d
WHERE e.deptno=d.deptno

SELECT e.,d.
FROM emp e JOIN dept d ON e.deptno=d.deptno

–外连接
SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)

SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno

SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno

SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno

–自连接
SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+)

八、分组函数

主要用于统计。
PS：所有的空都不参与计算。
常见的分组函数：AVG SUM MAX MIN COUNT

注意事项：
（1）出现在SELECT 后的字段（列），若是有分组函数的存在，若是没有出现在分组函数中，就该出现在GROUP BY 中。
（2）但是出现在GROUP BY中的字段可以不必出现在SELECT中。
（3）分组函数加上限制条件来筛选的时候不能使用WHERE，而是应当使用HAVING。

格式顺序：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

九、Sqlplus

1.User process<- 链接 ->Server Process <- 会话 ->Oracle Process
2.使用 VERIFY 在 iSQL*Plus 中显示变量被替代前和变量被替代后的SQL语句。
3.
(1)@ 路径 执行文件内容的语句
(2)spool 路径
文件语句(SELECT * FROM emp)
Spool off;
作用：将表里面的信息全部放入到文件中去。

十、处理数据

1.sql语句分类：
（1）DML(数据操作语言) insert delete update
（2）DDL(数据定义语言) create drop alter
（3）DCL(数据控制语言) grant revoke
PS：select有的时候被分在DML中，有的时候被分在DQL中！
增加：INSERT INTO 表名 VALUES(值1,值2…)
INSERT INTO 表名(字段1,字段2…) VALUES(值1,值2…)
INSERT INTO 表名1(字段1,字段2…) SELECT 字段1,字段2… FROM 表名2
修改:UPDATE 表名 SET 字段1=值1 WHERE
UPDATE 表名 SET 字段1=值1,字段2=值2… WHERE
UPDATE 表1 SET 字段1=(SELCT 字段1
FROM 表2
WHERE 表1.关联字段=表2.关联字段)
WHERE
删除: DELETE FROM 表名 WHERE
DELETE FROM 表1 WHERE 字段1=(SELCT 字段1
FROM 表2
WHERE 表1.关联字段=表2.关联字段)

2.查询SELECT 有的分在DML上，有的分在DQL上。

3.每次操作DML的时候，需要在执行操作后提交或者是回滚（相当于撤销），必须要避免锁等。

4.事物（本地事务）：是指作为单个逻辑工作单元执行的一系列操作，要么完整地执行，要么完全地不执行。
（1）作用：保证数据一致
（2）特征：ACID（atomicity，consistency，isolation，durability）
A：原子性（不可分） C：一致性（同时） I：隔离性（对外界隔离） D：持久性
（3）隔离级别
未提交读
提交读
可重复读
串读
（4）以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 或 DCL 语句（自动提交）
用户会话正常结束
系统异常终了

若是一个针对同一个表，A修改了里面的数据，但是没有提交，此时，B看到的表是没有修改的，但是A看到的表是修改了的样子。

5.锁：
（1）排它锁(x锁,独占锁，写锁）：不可读，也不可写
（2）共享锁(s锁，读锁）：可读，不可写

十一、创建和管理表

1.VARCHAR2(size)，CHAR(size)的区别？
VARCHAR2:长度可变，输入的是多长就是多长
CHAR：长度固定，不足的话以空格补齐，但是查询的时候一般不需要自己补上空格，若是 不行，那么就是用trim
PS：两者都不可以接收超过size限制长度的字符串。

2.NUMBER(p,s)
P：整数部分的长度
S：小数部分的长度

3.LONG ，CLOB，BLOB的区别？
LONG:可变长字符数据，最大可达到2G，但是一张表里面最多能有一个列是long 型的，只能存储文本。
CLOB：字符数据，最大可达到4G，只能存储文本。
BLOB:二进制数据，最大可达到4G，可以存储文本，图片，声音。

4.rowid：隐藏的列，记录了数据存在磁盘的具体位置。

5.
（1）增加列
ALTER TABLE 表名
ADD （列名 类型（size））
（2）修改列：可以修改列的数据类型, 尺寸, 和默认值，不可以修改列名
ALTER TABLE 表名
MODIFY （列名 类型（size））
（3）删除列
ALTER TABLE 表名
DROP COLUMN 列名

6.改变对象的名称(表的重命名)：执行RENAME语句改变表, 视图, 序列, 或同义词的名称。
RENAME dept TO detail_dept;
Table renamed.
必须是对象的拥有者

7.SET UNUSED 一般针对大数据，但是不能重新设置回可用，只能删除
ALTER TABLE table
DROP UNUSED COLUMNS;

8.Delete drop truncate的区别？
Delete：可以回滚，删除数据
Drop：不可以回滚，删除表的结构和数据
Truncate：不可以回滚，删除表的所有数据

PS:存储数据的地方有一个叫做高水位线（high water），高水位线以后的部分是从来没有存储过数据的。除了本身要存储数据所需要的空间外，通常还会有预留的空间，能够满足正常修改数据的需求，若是某一个数据更改的过大，那么会导致 行迁移 ，效率极低，所以需要避免行迁移。DELETE删除数据的时候是一行一行的删除数据的，但是TRUNCATE删除数据是直接把高水位线移到初始的位置，直接清除里面所有的数据，所以效率比DELETE高很多。

9.(1)创建表
CREATE TABLE dept
(
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13)
);
(2)删除表DROP TABLE dept80;

十二、约束

1.五种基本约束
（1）NOT NULL：
不能插入空值
create table T_CLASS
(
ID NUMBER not null,
NAME VARCHAR2(10)
)
（2）UNIQUE
UN_开头 不能有两行数据相同，但是可以两个空值
alter table T_STUDENT
add constraint UN_T_STUDENT2 unique (NAME)
（3）PRIMARY KEY
PK_ 开头，主键，不能为空，唯一（一张表有且仅能有一个主键），但是可以创建联合主键（多个列一起充当主键，全部都相同才算作是相同）
alter table T_CLASS
add constraint PK_T_CLASS primary key (ID)
（4）FOREIGN KEY
FK_开头，外键，一张表的一个列引用（指向，对应）另外的一个表的列，这个列需要有唯一约束，原来表的那个列就被叫做外键。在增加数据的时候，必须是另外一张表主键中有的值。主要体现在关联之后，作用：是表中的数据更加有意义。
alter table T_STUDENT
add constraint PK_T_STUDENT1 foreign key (CLASS_ID)
references T_CLASS (ID);

references T_CLASS (ID) on delete cascade ;(级联)
（5）CHECK
alter table T_CLASS
add constraint C_T_CLASS
check (id>0);

2.删除约束：
（1）
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
（2）
ALTER TABLE departments
DROP PRIMARY KEY CASCADE;

3.无效化约束：
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;

4.激活约束：
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
PS：当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY索引

5.级联约束：
（1）CASCADE CONSTRAINTS 子句在 DROP COLUMN 子句中使用
（2）在删除表的列时 CASCADE CONSTRAINTS 子句指定将相关的约束一起删除
（3）在删除表的列时 CASCADE CONSTRAINTS 子句同时也删除多列约束
Eg.1
ALTER TABLE test1
DROP (pk) CASCADE CONSTRAINTS;
Eg.2
ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;

6.查询约束 USER_CONSTRAINTS 这是一个系统的表
查询定义约束的列USER_CONS_COLUMNS

十三、视图

1.常见的数据库对象
（1）表——基本的数据存储集合，由行和列组成。
（2）视图——从表中抽出的逻辑上相关的数据集合。
（3）序列——提供有规律的数值。
（4）索引——提高查询的效率
（5）同义词——给对象起别名

2.创建视图需要给权限，操作的时候基本上可以把视图当做一个表
(普通视图)
conn /as sysdba
grant create view to scott;
特点：
（1）视图和原表是同一个行地址，表更新，视图的数据也会更新。
（2）视图也可以修改，改了原表的数据也会被修改，但是视图不应该修改。
（3）视图也可以创建视图。
PS：在创建的视图语句之后加上 with read only就可以让视图变成只读。

3.Rownum
必须从1开始，通常情况下是在分页的时候用的，若不是以1开始的话，那么该条数据将会被丢弃，所以rownum>1永远都找不到数据。
SELECT *
FROM(
SELECT rownum r,e.*
FROM v_emp e
WHERE rownum <= 10
) a
WHERE a.r >= 6

4.删除视图:DROP VIEW view;

5.物化视图：数据和原表的数据存储的位置（行地址）不同
作用：提高性能（利用查询重写）
PS：此时，需要打开查询重写开关！！！

十四、其他的数据库对象

1 序列
（1）是什么？
自动提供唯一的数值
共享对象
主要用于提供主键值（工作中一般不如此，例如订单号通常等长度且复杂）
代替应用代码
将序列值装入内存可以提高访问效率
（2）如何创建 CREATE SEQUENCE seq_名字
[INCREMENT BY n]（步长）
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]（是否循环）
[{CACHE n | NOCACHE}];（是否存在缓存，若是缓存5，从1开始，那么查看的下一个值就是6）
（3）NEXTVAL 和 CURRVAL
NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
CURRVAL 中存放序列的当前值
NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效

Eg.
SELECT dept_deptid_seq.CURRVAL
FROM dual;
（4）序列在下列情况下出现裂缝:
回滚
系统异常
多个表同时使用同一序列
（5）修改序列ALTER SEQUENCE
PS：
必须是序列的拥有者或对序列有 ALTER 权限
只有将来的序列值会被改变
改变序列的初始值只能通过删除序列之后重建序列的方法实现
（6）删除序列DROP SEQUENCE

2 索引
（1）是什么？
一种数据库对象
通过指针加速 Oracle 服务器的查询速度(一般的索引都是有序的，order+索引)
通过快速定位数据的方法，减少磁盘 I/O
索引与表相互独立（分盘，数据更新会影响索引效率）
Oracle 服务器自动使用和维护索引
（2）查看执行计划
选中需要查看的语句，然后按下F5，cost（成本），主要是指CPU，内存，IO
（3）创建索引
CREATE INDEX index
ON table (column[, column]…);
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建 唯一性索引
手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询
（4）以下情况可以创建索引:
列中数据值分布范围很广
列中包含大量空值
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
（5）下列情况不要创建索引:
表很小
列不经常作为连接条件或出现在WHERE子句中
查询的数据大于2%到4%
表经常更新
加索引的列包含在表达式中
（6）查询索引
可以使用数据字典视图USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
（7）基于函数的索引
若是索引的后面接的不是直接的列，而是对某一个列使用的函数，那么在进行WHERE的时候，若是直接使用列名是不会使用索引的，需要同时加上和索引后面相同的函数，此时，才会使用索引。

3 同义词
（1）使用
CREATE SYNONYM synonym
FOR object;
（2）作用
方便访问其它用户的对象
缩短对象名字的长度

十五、表的设计和范式

1 三大范式：
1NF：字段不可分
2NF：满足第一范式的基础上，表中要有主键列
3NF：满足第二范式的基础上，非主键列相互独立
PS：通常情况下，需要满足，但是在实际工作中很多时候都是违背了的，目的是提高效率。

2 表之间的关系：（至少两张表，双向来看）
（1）一对一：主键关联 亦或是 外键关联
（2）一对多：在多的一端增加外键
（3）多对多：引入中间表，联合主键（其实拆开来看就是两个一对多）

3 行列转换（http://blog.sina.com.cn/s/blog_67be3b4501017hbc.html）
第一部分：行转列
新建一个表：
CREATE TABLE HANG2LIE
( “ID” NUMBER,
“NAME” VARCHAR2(20),
“COURSE” VARCHAR2(20),
“SCORE” NUMBER
)
数据列出来如下：
ID NAME COUR SCORE

---

1 name_1 语文 33
1 name_1 数学 63
1 name_1 英语 71
1 name_1 历史 68
1 name_1 化学 94
2 name_2 语文 85
2 name_2 数学 4
2 name_2 英语 98
2 name_2 历史 9
2 name_2 化学 12
3 name_3 语文 49
3 name_3 数学 96
3 name_3 英语 30
3 name_3 历史 60
3 name_3 化学 2
要实现的行转列的效果如下(或者类似的结果)：
ID NAME SCORES

---

1 name_1 33,63,71,94,68
2 name_2 85,4,98,12,9
3 name_3 49,2,60,96,30

1、通过Oracle数据库自带的wm_concat()函数来实现：
select id,name,wm_concat(score) scores
from HANG2LIE
group by id,name;

2、通过decode函数：
select id,name,sum(decode(course,’语文’,score,null)) “语文”,
sum(decode(course,’数学’,score,null)) “数学”,
sum(decode(course,’英语’,score,null)) “英语”,
sum(decode(course,’历史’,score,null)) “历史”,
sum(decode(course,’化学’,score,null)) “化学”
from HANG2LIE
group by id,name;
得到的结果：
ID NAME 语文 数学 英语 历史 化学

---

2 name_2 85 4 98 9 12
1 name_1 33 63 71 68 94
3 name_3 49 96 30 60 2
3、通过case表达式
select id,name,sum(case when course=’语文’ then score end) “语文”,
sum(case when course=’数学’ then score end) “数学”,
sum(case when course=’英语’ then score end) “英语”,
sum(case when course=’历史’ then score end) “历史”,
sum(case when course=’化学’ then score end) “化学”
from HANG2LIE
group by id,name;
得到的结果和第二种实际上是一样的，其实语句也是一样的，只不过把decode函数换成了case when表达式而已

列转行
create table lie2hang as
select id,name,sum(case when course=’语文’ then score end) Chinese,
sum(case when course=’数学’ then score end) Math,
sum(case when course=’英语’ then score end) English,
sum(case when course=’历史’ then score end) History,
sum(case when course=’化学’ then score end) Chemistry
from hang2lie
group by id,name;
结构如下：
ID NAME Chinese Math English History Chemistry

---

2 name_2 85 4 98 9 12
1 name_1 33 63 71 68 94
3 name_3 49 96 30 60 2
我们要实现如下的查询效果：
ID NAME COUR SCORE

---

2 name_2 语文 85
1 name_1 语文 33
3 name_3 语文 49
2 name_2 数学 4
1 name_1 数学 63
3 name_3 数学 96
2 name_2 英语 98
1 name_1 英语 71
3 name_3 英语 30
2 name_2 历史 9
1 name_1 历史 68
3 name_3 历史 60
2 name_2 化学 12
1 name_1 化学 94
3 name_3 化学 2
1、集合查询
实现的SQL语句：
select id,name,’语文’ course,chinese score from lie2hang
union
select id,name,’数学’ course,math score from lie2hang
union
select id,name,’英语’ course,english score from lie2hang
union
select id,name,’历史’ course,history score from lie2hang
union
select id,name,’化学’ course,chemistry score from lie2hang;
这就是比较常见的列传行操作，主要原理是利用SQL的union集合查询。
2、insert all操作
语句如下：
create table lie2hang_result(
id number,
name varchar2(20),
course varchar2(20),
score number
);
insert all
into lie2hang_result(id,name,course,score) values(id,name,’语文’,chinese)
into lie2hang_result(id,name,course,score) values(id,name,’数学’,math)
into lie2hang_result(id,name,course,score) values(id,name,’英语’,english)
into lie2hang_result(id,name,course,score) values(id,name,’历史’,history)
into lie2hang_result(id,name,course,score) values(id,name,’化学’,chemistry)
select id,name,chinese,math,english,history,chemistry from lie2hang;

十六、其他知识

通常DBA才能用的上
1 控制用户权限
（1）创建用户
CREATE USER user
IDENTIFIED BY password;（设置密码）
（2）给权限
GRANT create session, create table,
create sequence, create view
TO scott;
（3）角色：就是将权限打包，类似于 类与其各个属性的关系
（4）修改密码
ALTER USER scott
IDENTIFIED BY lion;
（5）分配具体的权限
GRANT select
ON employees
TO sue, rich;
（6）WITH GRANT OPTION 使用户同样具有分配权限的权利，能分配的权限是当前用拥有的权限。
（7）PUBLIC：若是不是TO 某一个对象或者是用户且是为public，那么就是向数据库中所有用户分配权限。
（8）回收权限
REVOKE {privilege [, privilege…]|ALL}
ON object
FROM {user[, user…]|role|PUBLIC}
[CASCADE CONSTRAINTS];
（9）数据库联接
CREATE PUBLIC DATABASE LINK hq.acme.com
USING ‘sales’;

也可以先去tnsnames.ora中配置一个，修改IP地址，服务器的名称，访问的对象名称，然后登陆自己的用户到database links中new，其中NAME随意（就是之后访问需要@的东西），然后输入想要访问的用户，对应的密码，以及database（里面填写前面自己配置的访问的对象名称））

访问：
SELECT *
FROM emp@HQ.ACME.COM;

2 SET 运算符
（1）UNION 操作符返回两个查询的结果集的并集
Eg.
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;
（2）UNION ALL 操作符返回两个查询的结果集的并集以及两个结果集的重复部分（不去重）
（3）INTERSECT 操作符返回两个结果集的交集
（4）MINUS 操作符返回两个结果集的补集
通俗解释：前者减去与后者相同的部分

3 数据小结
（1）ROLLUP：
GROUP BY ROLLUP(department_id, job_id)
先对department_id, job_id进行分组计算，然后再按照department_id进行小结计算。
（2）CUBE：
使用同ROLLUP，先整体分组计算，然后按照每一个进行小结。
（3）复合列
复合列是被作为整体处理的一组列的集合ROLLUP (a，（b，c），d)
使用括号将若干列组成复合列在ROLLUP 或 CUBE 中作为整体进行操作
在ROLLUP 或 CUBE中, 复合列可以避免产生不必要的分组结果

4 高级子查询
（1）相关子查询
Eg.1
SELECT column1, column2, ..
FROM table1 outer
WHERE column1 operator
(SELECT colum1, column2
FROM table2
WHERE expr1 =outer.expr2);
Eg.2
SELECT e.*
FROM emp e
WHERE EXISTS(SELECT d.–d.可以写成常量，一般写成“1”
FROM dept d
WHERE e.deptno = d.deptno)

插入：
INSERT INTO 表名1(字段1,字段2…) SELECT 字段1,字段2… FROM 表名2
修改：
UPDATE 表1 SET 字段1=(SELCT 字段1
FROM 表2
WHERE 表1.关联字段=表2.关联字段)
WHERE
删除：
DELETE FROM 表1 WHERE 字段1=(SELCT 字段1
FROM 表2
WHERE 表1.关联字段=表2.关联字段)
（2）Exists
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT ‘X’
FROM employees
WHERE manager_id =
outer.employee_id);
基本上可以等同于in，但是执行的效率比之in要高很多。
（3）With
With
dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
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 department_name;
就相当于重命名。

5 分级查询
（1）树
有下级的 节点
没有下级的 叶子节点
遍历
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
左边高就是底到顶，左边低就是顶到底

Level和Lpad可以使得分级更加明显
Eg.1
SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,’_’)
AS org_chart
FROM employees
START WITH last_name=’King’
CONNECT BY PRIOR employee_id=manager_id
把level当做一个列来使用就可以了

6 外部表
（1）是什么
外部表是只读的表，其数据存储在数据库外的平面文件中
外部表的各种参数在 CREATE TABLE 语句中指定
使用外部表, 数据可以存储到外部文件或从外部文件中上载数据到数据库
数据可以使用 SQL访问, 但不能使用 DML 后在外部表上创建索引
（2）创建路径
CREATE DIRECTORY emp_dir AS ‘/flat_files’ ;
Eg.
CREATE TABLE oldemp (
empno NUMBER, empname CHAR(20), birthdate DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE ‘bad_emp’
LOGFILE ‘log_emp’
FIELDS TERMINATED BY ‘,’
(empno CHAR,
empname CHAR,
birthdate CHAR date_format date mask “dd-mon-yyyy”))
LOCATION (‘emp1.txt’))
PARALLEL 5
REJECT LIMIT 200;
外部表可以当做正常的表一样的使用，但是并不能够在oracle里面直接对表上的数据进行更改，不过可以在数据的源文件中去更改数据，此时，若是再次查询这个表，那么查询到的是已经更改过后的数据。