Oracle的SQL

目录

(1) 1.select语句

一、select作用
1.列选择
select xxx from table1
2.行选择
select * from table1
3.多表连接查询
select table1.aaa,table2.bbb from table1,table2
二、select语法
select * | {DISTINCT column | expression }
from table1
三、关于null的概念
null表示不可用、未赋值、不知道、不适用、不是0也不是空格
四、select的时候给列起别名
1.select last_name as name
2.select last_name name
3.select last_name “name”
五、字符串连接符 ||
1.例：select last_name || jobs_id As “employee” from employee
2.例：select last_name || ‘is a ’ || job_id As “employeeDetail” from employee
六、去重复行
例：SELECT DISTINCT department_id FROM employees;

---

(2) 2.条件限制和排序

一、条件限制关键词
select … where [conditions]
二、比较操作符
= 等于

大于
= 大于等于
< 小于
<= 小于等于
<> 不等于
BETWEEN …AND… 在两个值之间
IN(set) 在一个集合范围内，加（aaa,bbb,ccc,ddd,…）
LIKE 匹配一个字符串样子，可以使用%通配符
IS NULL 是一个空值，注意不能使用 =NULL
三、使用LIKE做模糊匹配
可使用% 或者_ 作为通配符：
1.% 代表 0 个或者多个 字符.
2._ 代表一个单个字符
3.查询通配符本身：需要转义字符
例：select * from t_char where a like ‘%\%%’ escape ‘\’;

四、逻辑操作符：
AND 所有条件都满足，返回TRUE
OR 只要有一个条件满足，返回TRUE
NOT 如果条件是FALSE,返回TRUE
五、排序
1.升序：asc
2.降序：desc
句式： select … from … oder by aaa desc

---

(3) 3.单行函数

一、字符函数
1.大小写转换函数
LOWER(‘SQL Course’) sql course
UPPER(‘SQL Course’) SQL COURSE
INITCAP(‘SQL course’) Sql Course
2.字符串操作函数
CONCAT(‘Hello’, ‘World’) HelloWorld
SUBSTR(‘HelloWorld’,1,5) Hello
LENGTH(‘HelloWorld’) 10
INSTR(‘HelloWorld’, ‘W’) 6
LPAD(salary,10,’*’) *****24000
RPAD(salary, 10, ‘‘) 24000****
TRIM(‘H’ FROM ‘HelloWorld’) elloWorld
TRIM(’ HelloWorld’) HelloWorld
TRIM(‘Hello World’) Hello World
二、数字操作函数
ROUND(45.926, 2) 45.93
TRUNC(45.926, 2) 45.92
MOD(1600, 300) 100
三、日期操作函数
MONTHS_BETWEEN (‘01-SEP-95’,’11-JAN-94’) 19.6774194
ADD_MONTHS (‘11-JAN-94’,6) 11-Jul-94
NEXT_DAY (‘01-SEP-95’,’FRIDAY’) 8-Sep-95
NEXT_DAY (‘01-SEP-95’,1) 3-Sep-95
NEXT_DAY (‘1995-09-01’,1) ORA-
01861:literal
does not match
format string
NEXT_DAY (to_date(‘1995-09-01’,’YYYY-MM-DD’),1) 3-Sep-95
LAST_DAY(‘01-FEB-95’) 28-Feb-95
ROUND(‘25-JUL-95’,’MONTH’) 1-Aug-95
ROUND(‘25-JUL-95’ ,’YEAR’) 1-Jan-96
TRUNC(‘25-JUL-95’ ,’MONTH’) 1-Jul-95
TRUNC(‘25-JUL-95’,’YEAR’) 1-Jan-95
四、日期的运算操作
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS, sysdate+1 as
tomorrow , hire_date + 8/24
FROM employees
WHERE department_id = 90;
五、不同的数据类型转换
1.字数转字符（to_char()）
to_char(number,’格式’)
9 表示一个数字
0 强制显示0
$ 放一个美元占位符
L 使用浮点本地币种符号
. 显示一个小数点占位符
, 显示一个千分位占位符
例：①SELECT TO_CHAR(salary, ‘L99,999.00’)
②设置本地货币符号：alter session set NLS_CURRENCY = ’ ￥’;
2.日期转字符（to_char()）
to_char(date,’格式’)
YYYY 4位数字表示的年份
YEAR 英文描述的年份
MM 2位数字表示的月份
MONTH 英文描述的月份
MON 三个字母的英文描述月份简称
DD 2位数字表示的日期
DAY 英文描述的星期几
DY 三个字母的英文描述的星期几简称
HH24:MI:SS AM 时分秒的格式化
DDspth 英文描述的月中第几天
fm 格式化关键字，可选
3.字符转数字：to_number（）
4.字符转日期
select to_date(‘2011-2-22’,’YYYY-MM-DD’) from dual;
select to_date(‘2-22-2011’,’MM-DD-YYYY’) from dual;
select to_date(‘2011-FEB-22’,’YYYY-MON-DD’) from dual;
5.RR格式与yy格式
RR :离此时时间最近的年份
YY: 与此时时间同一世纪的年份
例 TO_DATE( ‘27-OCT-98’, ‘DD-MON-RR’) 1998
TO_DATE( ‘27-OCT-98’, ‘DD-MON-YY’) 2098
六、函数嵌套
单行函数可以被无限层的嵌套，计算时先计算里层，再计算外层
七、其他单行函数
NVL (expr1, expr2) 如果expr1为空，就返回expr2
NVL2 (expr1, expr2, expr3) 如果expr1为空，就返回expr3（第2个结果）否则返回expr2
NULLIF (expr1, expr2) 如果expr1和expr2相等，则返回空
COALESCE (expr1, expr2, …, exprn)如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到
一个不为NULL的值 或者如果全部为NULL，也只能返回NULL了
八、条件表达式
1.case语句
case [expr1]
when … then …
else …
[end …]
2.decode
decode(col | expression,search1,result1,search2,result2,…,…,[defualt_result])

---

(4) 4.多表关联查询

1 、等于链接
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
2 、不等链接（包括> , < , !=, between）
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 > table2.column2;
3 、外连接（将其中一方全列，不管另一个表是否为null，可细分为左外连接、右外连接）
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;
4 、自连接（某个table 和自己本身链接）
SELECT table1.column, table2.column
FROM table1, table1 table2
WHERE table1.column1 = table2.column2;
5、交叉连接（没有连接，相当于笛卡尔积，很少用到）SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
6 、自然链接（相当于等于连接）
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;
7、 Using 子句（自然连接的补充）
我们知道自然连接会让系统自动查找两张表中的
段 所有列名相同的字段，并试图建立”等于连接”；但有的时候我们不期望这么做，而只是期望某个特定的字段
用 作为”等于连接”的条件，这种情况下可以使用Using 子句来做限制。
SELECT e.employee_id, e.last_name, d.location_id
FROM employees e JOIN departments d
USING (department_id) ;
8、内连接（相当于等于连接）
SELECT employee_id, city, department_name
FROM employees e
INNER JOIN departments d ON d.department_id = e.department_id
INNER JOIN locations l ON d.location_id = l.location_id;
9、左/右/全外连接（left join,right join，full join）
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

---

(5) 5分组计算函数和Group By子句

一、分组计算函数
1.函数
求和 sum
求平均值 avg
计数 count
求标准差 stddev
求方差 variance
求最大值 max
求最小值 min
备注：min和max可以用于任何类型数据，avg,sum,variance,stddev仅适用于数值型字段。
2.count用法说明：
count（*） 返回满足条件的所有行的行数，包括空的行和重复的行
count（expr）返回满足条件的且表达式不为空行数
count(distinct expr) 返回满足条件且表达式不为空，不重复的行数。

二、Group By
1.可按照某一个字段分组，也可以按照多个字段的组合。
例：SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
2.select查询语句中同时选择分组计算函数表达式和其他独立字段时，其他字段必须在Group By子句中。
错误实例：SELECT department_id, COUNT(last_name)
FROM employees;
3.不能在where条件中使用分组计算函数，当出现这种需求时，where换成having
SELECT department_id, AVG(salary) FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;
4.分组函数也可以嵌套使用
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

---

(6) 6.子查询

一、语法
select select_list
from table
where exper operator
(
select select_list
from table
)
二、注意
1.单行比较必须对应单行子查询（返回单一结果值的查询）；比如=，>
2.多行比较必须对应多行子查询（返回一个数据集合）；比如in,>any(至少大于一个值),>all（大于每个值）等。
例：SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = ‘IT_PROG’)
AND job_id <> ‘IT_PROG’;

---

(7) 7.DML（数据操纵）语句与事务控制

一、insert语句：向表中添加数据
1.方式一：insert into table (column1,column2…)
values (value1,value2…)
注意：对于不允许为null的列，必须写出来；对于允许为null的列，可以不写出来
在value中，对于列中为写出的列，默认赋值null。
2.方式二：insert into table
values （value1，value2，。。。）
注意：在value中必须对应写出每个列的值，即使是允许为空的，也必须显示写出null
3.方式三：从另一个表中copy一行，不需要values关键字
例：INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE ‘%REP%’;
4.方式四：使用子查询作为插入目标
例： INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary, department_id
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99999, ‘Taylor’, ‘DTAYLOR’, TO_DATE(‘07-JUN-99’, ‘DD-MON-RR’),
‘ST_CLERK’, 5000, 50);
WITH CHECK OPTION 可以检查要插入的内容，是否符合目标子查询的where条件
二：update语句，修改数据库中的数据（当存在某些约束的时候，某些更新可能会失败）
1.方式一：更新符合条件的行中某些列为具体的值
update table set column1=…
where …
2.方式二：使用子查询的结果作为更新后的值
例：UPDATE employees
SET job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 205),
salary =
(SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 114;
三、delete语句，删除表中数据（当存在某些约束的时候，某些删除可能会失败）
1.语法：delete from table
where …
如果遇到这种需求，也可以使用TRUNCATE 语句， ，TRUNCATE TABLE copy_emp ,
但要注意，TRUNCATE 语句无法回滚，因此除非是单独执行，并非常确认，否则慎用
四、merge语句：比较整合语句
MERGE INTO copy_emp c –要更新的表
USING employees e –来源表
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN –当匹配时，执行更新：将来源表中的数据对应的赋值给目标表
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
…
c.department_id = e.department_id
WHEN NOT MATCHED THEN –当不匹配时，执行插入
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);
五、事务控制
1.隐式的事务提交或回滚动作
commit和rollback是显示的提交和回滚语句，还有一些隐式的提交和回滚要注意的。
当如下事件发生时，会隐式的执行commit动作：
①数据定义语句被执行的时候，比如新建一张表
②数据控制语句被执行的时候，比如赋值grant…(或者deny)
③正常退出SQL*Plus或者PLSQL DEVELOPER,而没有显示的执行commit或rollback。
当如下事件发生时，会隐式的执行commit动作：
非正常 退出 iSQL*Plus , PLSQL DEVELOPER, 或者发生系统错误。
2.在commit或者rollack前后数据的状态：
①在数据已经被更改，但没有commit前，被更改记录处于被锁定状态，其他用户无法进行更改。
②在数据已经被更改，但没有commit前，只有当前session的用户可以看到这种变更，其他session的用户看不到数据的变化。
③在数据已被更改，并且被commit后，被更改记录自动解锁，其他用户可以进行修改。
④在数据已被更改，并且被commit后，其他session用户看到的数据时修改后的数据。
3.读一致性
①在任何时候，确保提供数据的一致性视图。
②一个用户对数据的更改不会影响另一个用户对数据的更改。
③”读一致性” 确保在同一时刻：
读数据的人不需要等待写数据的人
写数据的人不需要等待读数据的人
4.oracle读一致性的实现方式
当有用户修改数据时，Oracle先把那部分原始数据备份到回滚段，在commit之前，其他
用户读到的是回滚段上的，在提交之后，回滚段被释放。

---

(8) 8.锁

一、锁的概念
1.Oracle的锁的主要作用就是：防止并发事务对相同的资源（所谓资源是指表、行、共享的数据结构、数据字典行等）进行更改时，相互破坏。
2.锁既有隐式的，也有显式的；当某用户对某一批数据进行更改而未提交前，Oracle会隐式的进行加锁。
二、查锁
select a.*, C.type, C.LMODE
from v$locked_object a, all_objects b, v$lock c
where a.OBJECT_ID = b.OBJECT_ID
and a.SESSION_ID = c.SID
and b.OBJECT_NAME = ‘TESTTAB3’
三、注意问题
1.有主键的表中，由于程序问题导致Insert 相同主键会导致死锁的可能情形
2.使用下面语句查session之间的阻塞关系
SELECT decode(request, 0, ‘Holder: ‘, ‘Waiter: ‘) || sid sess,
id1,
id2,
lmode,
request,
TYPE
FROM gv$lock WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$lock
WHERE request > 0
AND TYPE != ‘HW’)
ORDER BY id1, request;

---

(9) 9.数据库对象-表

一、表的建立
1.表的命名要求和表中列的命名要求
①必须以字母开头
②长度不能超过30个字符
③只能包含A-Z,a-z,0-9,$,#。
④不能使用Oracle数据库的保留字
⑤不能与数据库中已有对象重名
2.建表的语法
creat table tabename
(column1,datatype[default wxpr]
column2,datatype[default wxpr]
column3,datatype[default wxpr]
column4,datatype[default wxpr]
…
)
3.数据类型
VARCHAR2(size) 可变长字符串
CHAR(size) 定长字符串
NUMBER(p,s) 可变长数值
DATE 日期时间
LONG 可变长大字符串，最大可到2G
CLOB 可变长大字符串数据，最大可到4G
RAW and LONG RAW 二进制数据
BLOB 大二进制数据，最大可到4G
BFILE 存储于外部文件的二进制数据，最大可到4G
ROWID 64进制18位长度的数据，用以标识行的地址
TIMESTAMP 精确到分秒级的日期类型（9i以后提供的增强数据类型）
INTERVAL YEAR TO MONTH 表示几年几个月的间隔（9i以后提供的增强数据类型-极其少见）
INTERVAL DAY TO SECOND 表示几天几小时几分几秒的间隔（9i以后提供的增强数据类型-极其少见）
4.建表的几种方式
①常规方式
②从别的表复制
create table tablename
例：create table tablename as select * from table2（复制了包括表结构和内容）
create table tablename as select * from table2 where 1=2（只复制表结构，不复制内容）
二、更改表的语法
1.添加列
alter table tablename
add (column1,datatype [default]
column2,datatype [default] )
2.更改列：
alter table tablename
modify （column1,datatype [default]
column2,datatype [default] ）
3.删除列
alter table tablename
drop （column）；
4.删除表
drop table tablename
注意：表被删除后，任何依赖于这张表的视图、Package 等数据库对象都自动变为无效。
5.更改表名
rename oldtablename to newtablename
6.清空表内容，保留表结构（不能回滚，HWM标记复位）
truncate table tablename

---

(10) 10.数据库对象-约束

一、约束的概念
1.约束的概念：
Oracle数据库使用”约束”来组织对数据库表中数据的不合法的”增删改”动作
2.常见的约束
not null 非空约束
unique 唯一性约束
primary key 主键约束
foreign key 外键约束
check 自定义约束
二、创建约束
①在创建表的时候同时创建约束
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
…
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
②另外单独创建约束
ALTER TABLE tablename
ADD CONSTRAINT constraintname
constrainttype (column1,…);
例：ALTER TABLE CUX_LES_JE_LINES
ADD CONSTRAINT CUX_LES_JE_LINES_PK
PRIMARY KEY(JE_LINE_ID);
三、约束详情
1.指定约束名
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,–没有指定约束名，系统自动给予命名
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE，
CONSTRAINT emp_hire_date_nn NOT NULL,–用户指定的约束名
）
2.外键约束：
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id)
3.外键约束类型：
REFERENCES: 表示列中的值必须在父表中存在
ON DELETE CASCADE: 当父表记录删除的时候自动删除子表中的相应记录.
ON DELETE SET NULL: 当父表记录删除的时候自动把子表中相应记录的值设为NULL
4.自定义约束：
…, salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary > 0),…
4.删除约束
①普通的删除
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
②级联的删除：
ALTER TABLE departments
DROP PRIMARY KEY CASCADE;–级联删除
5.失效/生效 约束
①生效
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
②失效
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
6.当某列被级联时，此列为级联列的父项，删除此列会失败，删除时可以加上CASCADE CONSTRAINTS;
ALTER TABLE tablename
DROP (column) CASCADE CONSTRAINTS;
7.查询表中所有的约束
SELECT constraint_name, constraint_type,
search_condition
FROM user_constraints
WHERE table_name = ‘…’;

---

(11) 11.数据库对象-视图

一、视图的概念
1.视图的概念
有时候我们需要关联多张表获得一个查询结果集，有时我们需要些很复杂的条件得到一个想要的结果，我们并不想每次都想这些数据的时候都重新去写很复杂的SQL语句。我们可以把这些结果集为视图view。
2.视图的分类：简单视图和复杂视图
特性 简单视图 复杂视图
关联的表数量 1个 1个或多个
查询中包含函数 否 是
查询中包含分组数据 否 是
允许对视图进行DML操作 是 否
二、视图的创建与删除
1.创建视图：
格式：create view viewname
as ….
简单视图创建
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
复杂视图创建：
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
2.删除视图：
DROP VIEW empvu80;
3.获取数据集的前n项
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;

---

(12) 12.数据库对象-序列、索引、同义词

一、序列
1.概念：当定义某张表的主键后，为保持此列的唯一性，可以设置自增长。
2.序列的创建：
create sequence sequencename
[increment by n]
[start with n]
[{maxvalue n | momaxvalue}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
3.序列的取值：当插入数据时，可以保持自增长
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
‘Support’, 2500);
4.查询序列当前的数值
SELECT seq_name.CURRVAL
FROM dual;
5.更改序列
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE NOCYCLE;
或：
create or replace table

6.删除序列
DROP SEQUENCE ID_SQ
二、索引
1.索引的概念：
但我们的表中数据很多的时候（比如有1 亿条数据），我们想找出一条符合特定条件的记录就会比较慢，这个
时候，我们希望表中的数据是有序的，这样我们可以使用诸如二分法之类的方法加快查询，而不是做全表扫描，
？ 但我们每次要查询的数据可能来自不同的列，我们也无法保证插入表中的数据就是有序的，怎么办呢？ Oracle
数据库提供”索引”来解决这个问题。
2.索引的创建：
CREATE INDEX emp_last_name_idx
ON employees(last_name);
3.创建索引的场景
在什么样的情况下创建索引对加快查询有利呢：
答：查询条件中使用到这个列（或者这个列于其他列的组合），且这个列（或者与其他列的组合）上的数字
量 范围跨度很大，而大多数情况下我们要获取的数据的量占整个表的数据总量 小于4% ；
在什么样的情况下不适合创建索引呢：
答: 1 ）被查询的表本身就很小，即是是全表扫描也非常快； ； 或者基于这张表的查询，大多数情况下需要获取
的数据量都超过了总量的4% ；或者这张表需要频繁的被更新，建立索引的话会引起索引的频繁更新，从而反而
降低数据库的整体效率。
4.函数索引
当查询语句的Where 条件中，对于某些列使用了函数表达式时，普通索引对查询没有帮助，如果想利用索引，则
， 必须创建函数索引，
CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
三、同义词
1.同义词概念
当数据库用户A 要访问数据库用户B 中的一张表Table1 的时候，需要加前缀
Select * from B.table1
但我们要通过DB-LINK 访问另一个数据库中的某张表的时候我们需要加@ 后缀
Select * from table1@db-link-name
2.为了在程序中能够简化写法，Oracle 提供同义词，也就是可以在A 用户下建立一个同义词指向B 用户下的
Table1, 以后访问的时候可以直接访问这个同义词，而不用加前缀了。
CREATE SYNONYM Table1 for B.Table1

---

(13) 13.控制用户权限&使用集合操作

一、控制用户权限
1.概念
每个人登录Oracle 数据库都是以某个特定的数据库用户登录的，用户能否创建表？ 该用户能否访问其他用户
下面的表。。等等这些事情都是可以利用Oracle
2.如果要给多个用户赋予相同的权限，可以通过角色来简化管理
CREATE ROLE manager ;–创建角色
GRANT create table, create view to manager; –授予该角色权利
GRANT manager to DEHAAN, KOCHHAR;–将该角色授予给用户
3.对象权限
区别于系统权限，细化到某个具体的数据库对象上的权限访问控制，
各种数据库对象适合赋予的权限名称列表：
[PICTURE]
语法：
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
例：GRANT update (department_name, location_id)
ON departments
TO scott, manager;
如果你想让其他用户也有权 把你赋给他的权限进一步赋予给别人，那么需要带 WITH GRANT OPTION;
GRANT select, insert
ON departments
TO scott
WITH GRANT OPTION;
如果 你想让所有人都有相关权限，那么可以把该权限赋予给Public
GRANT select
ON alice.departments
TO PUBLIC;
4.通过数据字典查询系统中的赋权情况
ROLE_SYS_PRIVS 角色对应的系统权限
ROLE_TAB_PRIVS 角色对应的表权限
USER_ROLE_PRIVS 用户的角色分配表
USER_TAB_PRIVS_MADE 用户对象上赋权者与被赋者的历史赋权情况
USER_TAB_PRIVS_RECD 用户对象上拥有者与被赋者的历史赋权情况
USER_COL_PRIVS_MADE 用户对象列上赋权者与被赋者的历史赋权情况
USER_COL_PRIVS_RECD 用户对象列上拥有者与被赋者的历史赋权情况
USER_SYS_PRIVS 用户的系统权限
5.收回权限
REVOKE {privilege [, privilege…]|ALL}
ON object
FROM {user[, user…]|role|PUBLIC}
6.数据库连接的概念
Database Link , 如果你需要在当前数据库中访问另一个数据库中表，最简单的方法是在当前数据库中创建一个
过 数据库连接指向另一个数据库，然后通过@数据库连接的后缀就可以访问另一个数据库中的表了。
创建连接：
CREATE PUBLIC DATABASE LINK hq.acme.com
USING ‘sales’;

SELECT *
FROM emp@HQ.ACME.COM;
二、使用集合操作
1.并集：union / union all
SELECT employee_id, job_id
FROM employees
UNION –union去除重复的记录，union all保留重复记录
SELECT employee_id, job_id
FROM job_history;
2.交集：INTERSECT
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
3.差集(自身减去交集)：MINUS

---

(14) 14.Group By子句增强

一、在Group By 中使用Rollup 产生常规分组汇总行以及分组小计：
以部门为单位，进行分组小计，最后再总计
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);
二、在 在Group By 中使用Cube 产生Rollup 结果集 + 多维度的交叉表数据源：
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY CUBE (department_id, job_id) ;
三、GROUPING 函数
使用Grouping 函数； 没有被Grouping 到返回1 ，否则返回0
SELECT department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
四、使用Grouping Set 来代替多次UNION
SELECT department_id, job_id,
manager_id,avg(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));–以这两者group方式进行汇总

---

(15) 15.子查询进阶

一、非相关子查询当做一张表来用
例：查询高于本部门平均工资的员工的信息
SELECT a.last_name, a.salary,
a.department_id, b.salavg
FROM employees a, (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
二、相关子查询的概念: 子查询中参考了外部主查询中的表。
例：查询高于本部门平均工资的员工的信息
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id) ;
例：查询工作历史超过两条的员工信息
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
三、使用Exists
例：查询manager的信息SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT ‘X’
FROM employees
WHERE manager_id =
outer.employee_id);
或：
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);
四、使用not exit
例：查询不是manager的员工的信息
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT ‘X’
FROM employees
WHERE department_id
= d.department_id);
或：
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees);
五、在 在Update 语句中使用相关子查询。
例：向employee中增加deparment_name字段，并将department表中的department_name同步上去
ALTER TABLE employees
ADD(department_name VARCHAR2(14));

UPDATE employees e
SET department_name =
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
六、在 在DELETE 语句中使用相关子查询
DELETE FROM job_history JH
WHERE employee_id =
(SELECT employee_id
FROM employees E
WHERE JH.employee_id = E.employee_id
AND start_date =
(SELECT MIN(start_date)
FROM job_history JH
WHERE JH.employee_id = E.employee_id)
AND 5 > (SELECT COUNT(*)
FROM job_history JH
WHERE JH.employee_id = E.employee_id
GROUP BY employee_id
HAVING COUNT(*) >= 4));
七、使用with子句 1） 如果在后面多次使用则可以简化SQL ; 2) 适当提高性能
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;

---

(16) 16.递归查询

一、句式
SELECT [LEVEL], column, expr…
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;
二、例子
1.查询从King 开始，从上往下的各级员工。SELECT last_name||’ reports to ‘|| PRIOR last_name “Walk Top Down”
FROM employees
START WITH last_name = ‘KingA’
CONNECT BY PRIOR employee_id = manager_id ;
2、查询从101 开始，从下往上的各级员工。
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
3.上下级关系

[PICTURE]

4.使用LEVEL 关键字和 LPAD 函数 ，在OUTPUT 中显示树形层次
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

---

(17) 17.insert增强

一、一个来源插入多个目标表（无条件）。
INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)

SELECT employee_id EMPID, hire_date HIREDATE,salary SAL, manager_id MGR
FROM employeess
二、一个来源插入多个目标表（有条件）。
INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;

三、一个来源插入多个目标表（有条件，首次匹配即跳到下一条）

INSERT FIRST
WHEN SAL > 25000 THEN
INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like (‘%00%’) THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like (‘%99%’) THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;
四、列转行（一行变多行，交叉报表的反操作）
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;

---

(18) 18.SQL进阶功能

一、分析函数
分析函数提供一系列比较高级的SQL 功能。分析函数时建立在数据窗口（over 在一定的数据库范
围进行数据分析），在一定的数据范围进行排序、汇总等。
查询当前员工所在部门的平均工资、最高工资和最低工资，即可通过分析函数来实现
SELECT e.last_name,
e.salary,
d.department_name,
AVG(e.salary) over(PARTITION BY d.department_name) department_avg_salary,
MAX(e.salary) over(PARTITION BY d.department_name) department_max_salary,
MIN(e.salary) over(PARTITION BY d.department_name) department_min_salary
FROM employees e, departments d
WHERE 1 = 1
AND e.department_id = d.department_id
二、删除的数据闪回。
DELETE FROM departments WHERE department_name = ‘Finance’;
COMMMIT;
SELECT * FROM departments WHERE department_name = ‘Finance’;
此时不会查询到数据。
闪回到5 分钟之前，再执行下查询试试有没有结果。
SELECT *
FROM departments AS OF TIMESTAMP SYSDATE - 5 / (24 * 60)
WHERE department_name = ‘Finance’;
三、全局临时表 在我们开发程序中也是经常使用 的，在不同的回话之间可以屏蔽数据，可以有触发器，
检查约束，索引 等。比如在程序的执行过程的当前回话中需要临时存放一些数据，这些数据是其
他回话无法访问的，此时全局临时表就是一个很好的方案。
基于会话
CREATE GLOBAL TEMPORARY TABLE temp_table_session (…) ON COMMIT PRESERVE ROWS;
基于会话的临时表，在会话断开之前，或者通过一个delete 或truncate 物理地删除行之前 ，
这些 行会一直存在于这个临时表中。只有我的会话能看到这些行，即使我已经提交，其他会话也无法看到我的行。
基于事务
CREATE GLOBAL TEMPORARY TABLE temp_table_session (…) ON COMMIT DELETE ROWS;
基于事务的临时表，我的会话提交时，临时表中的行就不见了。只需把分配给这个表的临时区段交回 ，
这些 行就会消失，在临时表的自动清除过程中不存在开销。
四、物化 视图使用的也比较普遍，是一种特殊的物理表，有别于普通的数据视图。在一段时间内保存。
固定的数据，提供自动刷新和手动刷新的机制。物化视图涵盖的内容很多，包括高级复制功能等，
需要仔细认真的去学习研究。
CREATE MATERIALIZED VIEW materialized_view_name
BUILD [IMMEDIATE|DEFERRED] –1. 创建方式
REFRESH [COMPLETE|FAST|FORCE|NEVER] –2. 物化视图刷新方式
ON [COMMIT|DEMAND] –3. 刷新触发方式
START WITH (start_date) –4. 开始时间
NEXT (interval_date) –5. 间隔时间
WITH [PRIMARY KEY|ROWID] –6. 默认 primary key
ENABLE QUERY REWRITE –7. 是否启用查询重写
AS –8. 关键字
SELECT STATEMENT; –9. 基表选取数据的 select 语句
五、很多时候在用字符函数时会忽略如下函数的不同点，其实是需要重点关注的，以免写出有BUG的 的程序。
length 是字符 个数，lengthb 指的是字节数。
substr值 是按字来取值，substrb 是按字节来取值汉字。六、VPD 全称Virtual Private Database，这个 技术提供了对 数据库信息的细粒度访问控制。
是数据库层面的一种非常成熟的数据访问控制技术，通过策略函数来实现的具体的控制
1 ）创建策略函数 Function:
CREATE OR REPLACE FUNCTION vpd_test(p_schema VARCHAR2, p_object VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN ‘employee_id >= 100’;
END;
2 ）对数据库对象应用策略函数：
BEGIN
dbms_rls.add_policy(object_schema => ‘STUDENT1’,
object_name => ‘EMPLOYEES’,
policy_name => ‘VPD_TEST’,
function_schema => ‘STUDENT1’,
policy_function => ‘VPD_TEST’);
END;
3 ）通过查询数据字典，确认数据库对象上是否有策略函数：
SELECT * FROM dba_policies t WHERE t.object_name = ‘EMPLOYEES’;·