之前做的项目都是用的MySql，进新公司之后开始使用Oracle，然而现在很多项目特别是金融类的项目基本都是用Oracle，所以学习一下还是很有必要的。一下是整理的一些笔记，便于以后查阅使用。后面还有一些补充！
oracle（数据库创建、删除、启动、关闭）：https://blog.csdn.net/qq_40006058/article/details/79868323
彻底卸载ORCLE：https://www.cnblogs.com/Leonar-do/p/5557905.html
笔记、尚硅谷pl/sql学习资料和表信息sql资料下载：点击下载
在给scott用户赋予操作视图权限时，登录system用户遇到错误如下：
ORA-12560: TNS:protocol adapter error错误
https://bbs.csdn.net/wap/topics/330080263
解决上述问题之后，给scott赋予创建视图的权限：
grant create view to scott;
给scott赋予创建同义词的权限：
grant create synonym to scott;
单行函数：
一、字符函数
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 去掉字符串首尾的某一个字符（只能去掉首尾字符，不能去除中间字符）
REPLACE(‘abcd’,’b’,’m’) amcd 用‘m’替换‘abcd’中的‘b’
二、数字函数
函数 结果 备注
ROUND(45.926,2) 45.93 四舍五入
TRUNC(45.926,2) 45.92 截断数字
MOD(1600,300) 100 求余
三、日期函数
1、在日期上加上或减去一个数字结果仍然是日期
2、两个日期相减返回日期之间相差的天数；日期之间不能做加法，无意义
3、可以用数字除24来向日期中加上或减去天数
函数 示例 结果 描述 备注
MONTHS_BETWEEN MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 19.6774194 两个日期相差的月数
ADD_MONTHS ADD_MONTHS('11-JAN-94',6) '11-JUL-94' 向指定日期中加上若干月数
NEXT_DAY NEXT_DAY('01-SEP-95','FRIDAY') '08-SEP-95' 指定日期的下一个星期*对应的日期
LAST_DAY LAST_DAY('01-FEB-95') '28-FEB-95' 本月的最后一天
ROUND ROUND(sysdate,'MONTH') 2018/10/1 日期四舍五入
ROUND(sysdate,'YEAR') 2019/1/1
TRUNC TRUNC(sysdate,'MONTH') 2018/10/1 日期截断
TRUNC(sysdate,'YEAR') 2018/1/1
四、转换函数
1、隐性
源数据类型 目标数据类型
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
2、显性
函数 示例 结果 描述
TO_CHAR(date,'format model') TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') 2018-10-11 09:47:35 对日期的转换
TO_CHAR(number,'format model') TO_CHAR(12345,'$99,999.00') $12,345.00 对数字的转换
TO_DATE(char,'Format Model') TO_DATE('2018/10/11','yyyy-mm-dd') 2018-10-11 对字符的转换
TO_NUMBER(CHAR,'Format model') TO_NUMBER('234234.4350','999999.0000') 234234.435 字符串转化成数字
日期格式元素
yyyy 2018
YEAR TWO THOUSAND AND FOUR
MM 02
MONTH JULY
MON JUL
DY MON
DAY MONSAY
DD 02
五、通用函数
适用于任何数据类型，同时也适用于空值
函数 示例 结果 备注
NVL(expr1,expr2) NVL(null,'this is null') this is null 如果expr1是空，就显示expr2
NVL2(expr1,expr2,expr3) NVL2('123','not null','this is null') not null 如果expr1是空，就显示expr3，否则显示expr2
NULLIF(expr1,expr2) NULLIF('abcd','abc') abcd 如果expr1和expr2相等，返回空；否则反回expr2
COALESCE(expr1,expr2,...exprn) COALESCE(null,null,'rwrw') rwrw 如果expr1是null，就取expr2，expr2是null，就取expr3，以此类推，如果全为null，就显示null
六、条件表达式
1、CASE表达式
语法：
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
示例：
select case 1 when 0 then 'this is 0'
when 1 then 'this is 1'
when 2 then 'this is 2'
else 'this is others' end as theResult
from dual;
2、DECODE函数
语法：
DECODE(col|expression, search1, result1 ,
[, search2, result2,...,]
[, default])
示例：
select decode(1,0,'this is 0',
1,'this is 1',
2,'this is 2',
'this is others')
as theResult
from dual;
七、嵌套函数
以上的各个单行函数是可以进行嵌套使用的。
示例：
将阿拉伯数字转化成英文
select to_char(to_date(trunc(4000.12),'J'),'JSP')
|| ' and ' ||
to_char(to_date(to_number(substr(4000.12,instr(4000.12,'.')+1)),'J'),'JSP')
FROM DUAL
多表查询
一、笛卡尔集（叉集）（少用）
笛卡尔集和叉集其实是相同的，都是两个表没有联合查询却没有查询条件，查询出来的结果数量就是联合查询的几个表的数据条数的乘积
笛卡尔集示例：
select e.last_name,d.department_name
from employees e,departments d
叉集示例：
select last_name,department_name
from employees
cross join departments
二、等值连接
等值连接示例：
select e.last_name,d.department_name
from employees e,departments d
where e.department_id=d.department_id
三、非等值连接
四、内连接
合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。
五、外连接
两个表在连接过程中出了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行。
没有匹配的行时，结果表中相应的列为NULL。外连接的WHERE字句条件类似于内部连接，但连
接条件中没有匹配行的表的列后面要加外连接运算符，即(+)。
右外连接：右边的表所有数据都显示，对应左边表没数据的左边表就显示NULL
方式一：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;
方式二：
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
左外连接：左边的表所有数据都显示，对应右边表没数据的左边表就显示NULL
方式一
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column(+);
方式二：
select e.last_name,e.department_id,d.department_name
from employees e
right outer join departments d
on e.department_id=d.department_id
满外连接：左表和右表的数据均都显示出来，对方表没有对应的数据就显示NULL
示例：
select e.last_name,e.department_id,d.department_name
from employees e
full outer join departments d
on e.department_id=d.department_id
六、自连接
例如：
select worker.last_name || 'works for ' || manager.last_name
from employees worker,employees manager
where worker.manager_id=manager.employee_id
七、自然连接：
使用NATURAL JOIN子句，会以两个表中具有相同名字的列为条件创建等值连接；如果列名相同而数据类型不同，就会产生错误
示例：
select department_id,department_name,location_id,city
from departments
NATURAL JOIN locations
八、使用USING子句创建连接
1、在使用NATURAL JOIN子句创建等值连接时，可以使用USING子句子句指定等值连接中需要用到的列；
2、使用USING可以在有多个列满足条件时进行选择
3、不要给选中的列加上表名前缀或别名
4、JOIN和USING子句经常同时使用
示例：
select e.last_name,d.department_name
from employees e
JOIN departments d USING(department_id)
九、使用ON子句创建连接
1、自然连接中是一具有相同名字的列作为连接条件的，可以使用ON子句指定额外的连接条件
2、ON子句使语句具有更高的可读性
示例1：
select e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
from employees e
join departments d
on (e.department_id=d.department_id)
示例2：
select employee_id,city,department_name
from employees e
join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
分组函数
一、分组函数
分组函数作用于一组数据，并对一组数据返回一个值
1、分组函数类型
函数 用途
AVG 求平均值
COUNT 求总条数
MAX 求最大值
MIN 求最小值
STDDEV
SUM 求和
2、分组函数语法
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
示例：
select avg(salary),max(salary),min(salary),sum(salary)
from employees
where job_id like '%REP%'
3、在分组函数中使用NVL函数
NVL函数使分组函数无法忽略空值。
select avg(NVL(commission_pct,0)) from employees
4、DISTINCT关键字
count(DISTINCT expr)返回expr非空且不重复的记录总数
select count(DISTINCT department_id) from employees
5、GROUP BY子句
在select列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。
包含在GROUP BY子句中的列不必包含在SELECT列表中。
使用一个列分组示例：
select department_id,AVG(salary)
from employees
GROUP BY department_id
使用多个列分组：
select department_id dept_id,job_id,AVG(salary)
from employees
GROUP BY department_id,job_id
6、过滤分组
不能在WHERE子句中使用组函数
可以在HAVING子句中使用组函数
使用HAVING过滤分组
语法：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
示例：
select department_id,AVG(salary)
from employees
GROUP BY department_id
HAVING MAX(salary)>10000
7、嵌套组函数
示例：
select MAX(AVG(salary))
from employees
GROUP BY department_id
子查询
一、子查询：
注意事项：
1、子查询要包含在括弧内
2、讲子查询放在比较条件的右侧
3、单行操作符对应单行子查询，多行操作符对应多行子查询
示例：
select last_name
from employees
where salary > (select salary from employees where last_name='Abel')
二、单行子查询和多行子查询
当行子查询使用比较操作符：=、>、>=、<、<=、<>
例如：
select last_name
from employees
where salary > (select salary from employees where last_name='Abel')
多行子查询使用多行比较操作符：IN（等于列表中的任意一个）、ANY（和子查询返回的某一值比较）、ALL（和子查询返回的所有值比较）
示例1：
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'
示例2：
select employee_id,last_name,job_id,salary
from employees
where salary < all(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG'
创建和管理表
一、常见的数据库对象
对象 描述
表 基本的数据存储集合，由行和列组成
视图 从表中抽出的逻辑上相关的数据集合
序列 提供有规律的数值
索引 提高查询效率
同义词 给对象起别名
二、表名和列名命名规则
1、必须以字母开头
2、必须在1-30个字符之间
3、必须只能包含A-Z、a-z、0-9、_、$和#
4、必须不能和用户定义的其他对象重名
5、必须不能是ORACLE的保留字
三、ORACLE中的数据类型
数据类型 描述
varchar2(size) 可变长字符数据
char(size) 定长字符数据
number(p,s) 可变长数值数据
date 日期型数据
long 可变长字符数据，最大可达2G
clob 字符数据，最大可达4G
raw(long raw) 原始的二进制数据
blob 二进制数据，最大可达4G
bfile 存储外部文件的二进制数据，最大可达4G
roeid 行地址
四、创建表：
1、create table语句创建表
语法：
create table table_name(
column0 dataType [default expr],
column1 dataType [default expr],
...
columnn dataType [default expr]
);
示例：
create table testTable(
id varchar2(10) not null,
name varchar2(50) default 'no name',
age number(5)
);
2、使用子查询创建表
子查询后面可以跟查询条件。
CREATE TABLE table_name
[(column, column...)]
AS sub_query;
示例：
create table emp1 as select * from employees;
五、ALTER TABLE语句
1、给表追加新的列
ALTER TABLE table_name ADD (column0 dataType [DEFAULT expr],
column1 dataType [DEFAULT expr],
...
columnn dataType [DEFAULT expr],)
2、修改表现有的列
ALTER TABLE table_name MODIFY (column0 dataType [DEFAULT expr],
column1 dataType [DEFAULT expr],
...
columnn dataType [DEFAULT expr],)
3、为新追加的列定义默认值
4、删除表的一个列
ALTER TABLE table_name DROP COLUMN column_name;
5、重命名表的一个列名
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
六、删除表和清空表
1、删除表
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE语句不能回滚
语句：DROP TABLE table_name;
2、清空表
删除表中的所有数据
释放表的存储空间
语句：DELETE TABLE table_name;
TRUNCATE语句不能回滚，DELETE可以回滚
七、改变对象的名称
可以改变表、视图、序列、或同义词的名称
RENAME old_object_name TO new_object_name;
数据处理
这里简单的增删改数据就不做记录了。主要看一下数据库事务。
一、数据库事务
事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。
数据库事务由一下部分组成：
1、一个或多个DML语句。DML：数据操纵语句，增删改查
2、一个DDL语句。DDL：数据定义语言，定义不同的数据段、数据库、表、列、索引等数据库对象
3、一个DCL语句。DCL：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句
数据库事务以第一个DML语句的执行作为开始，以COMMIT、ROLLBACK、DDL语句（自动提交）、用户会话正常结束、系统异常终止之一结束。
使用COMMIT和ROLLBACK语句的优点：
确保数据完整性、数据改变被提交之前预览、将逻辑上相关的操作分组。
使用 SAVEPOINT 语句在当前事务中创建保存点。
使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。
示例：
begin
insert into test values('3','333');
savepoint insert_3;
insert into test values('4','444');
rollback to insert_3;
commit;
end;
二、事务进程
自动提交在以下情况中执行:
1、DDL 语句。
2、DCL 语句。
3、不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。
4、会话异常结束或系统异常会导致自动回滚。
三、数据状态
提交或回滚前的数据状态：
1、改变前的数据状态是可以恢复的
2、执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
3、其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
4、DML语句所涉及到的行被锁定， 其他用户不能操作。
提交后的数据状态：
1、改变前的数据状态是可以恢复的
2、执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
3、其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
4、DML语句所涉及到的行被锁定， 其他用户不能操作。
数据回滚后的状态：
1、数据改变被取消。
2、修改前的数据状态被恢复。
3、锁被释放。
约束
一、几种约束
约束是表级的强制规定
一共五种：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK
二、使用约束时的几个注意事项
1、如果不指定约束名，Oracle自动按照SYS_Cn的格式指定约束名
2、创建和修改约束：建表的同时和建表之后
3、可以在表级和列级定义约束
4、可以通过数据字典视图查看约束
三、定义约束
语法格式：
CREATE TABLE table_name
(column datatype [DEFAULT expr] [column_constraint],
...
[table_constraint][,...]);
示例：
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));
四、FOREIGN KEY 约束的关键字
1、FOREIGN KEY在表级指定子表的列
2、REFERENCES表示在父表中的列
3、ON DELETE CASCADE（级联删除）：当父表中的列被删除时，子表中相应的列也被删除
4、ON DELETE SET NULL（级联置空）：子表中相应的列置空
五、添加约束的语法
使用ALTER TABLE语句：
1、添加或删除约束，但是不能修改约束
2、有效化或无效化余数
3、添加NOT NULL约束时要用MODIFY语句
当定义或激活UNIQUE或PRIMARY KEY约束时系统会自动创建UNIQUE或PRIMARY KEY索引
添加约束示例：
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
删除约束示例：
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
无效化约束示例：
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk;
激活约束示例：
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
六、查询约束
1、查询约束，查询数据字典USER_CONSTRAINTS
示例：
SELECT constraint_name, constraint_type,search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
2、查询定义约束的列，查询数据字典USER_CONS_COLUMNS
示例：
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
视图：
一、视图介绍
什么是视图：
1、视图是一种虚表
2、视图建立在已有表的基础上，视图赖以建立的这些表称之为基表
3、向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句
4、视图向用户提供基表数据的另一种表现形式
为什么使用视图：
1、控制数据访问
2、简化查询
3、避免重复访问相同的数据
二、创建、修改和删除视图
创建或修改视图语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
描述视图结构语法：
describe view_name;
删除视图语法：
drop view view_name;
简单的示例一：
create or replace view empview
as
select e.employee_id emp_id,e.last_name name,d.department_name
from employees e,departments d
where e.department_id = d.department_id
略微复杂的示例二：
create or replace view empview(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
三、视图中使用DML的规定
当视图定义中包含以下元素之一时不能使用delete：
1、组函数
2、GROUP BY子句
3、DISTINCT关键字
4、ROWNUM伪列
当视图定义中包含以下元素之一时不能使用update:
1、组函数
2、GROUP BY子句
3、DISTINCT 关键字
4、ROWNUM 伪列
5、列的定义为表达式
当视图定义中包含以下元素之一时不能使insert:
1、组函数
2、GROUP BY 子句
3、DISTINCT 关键字
4、ROWNUM 伪列
5、列的定义为表达式
6、表中非空的列在视图定义中未包括
四、屏蔽DML操作
可以使用WITH READ ONLY选项屏蔽对视图的DML操作，屏蔽之后，任何DML操作都会返回一个ORACLE server错误
示例：
create or replace view empview(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
with read only
五、Top-N分析
Top-N，根据某一规则进行排序，然后取其前N行数据。
查询最大的几个值的Top-N语法：
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;
注意：对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。
示例（查询工资最高的三名员工）：
select rownum as rank, last_name, salary
from (select last_name,salary from employees order by salary desc)
where rownum<=3
其他数据库对象
一、序列
序列：可供多个用户来产生唯一数值的数据库对象
1、自动提供唯一的数值
2、共享对象
3、主要用于提供主键值
4、将序列值装入内存可以提高访问效率
定义序列：
CREATE SEQUENCE sequence
[INCREMENT BY n] --每次增长的数值
[START WITH n] --从哪个值开始
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] --是否需要循环
[{CACHE n | NOCACHE}]; --是否缓存登录
创建序列示例：
create sequence test_id
increment by 10
start with 1
maxvalue 9999
nocache
nocycle;
查询序列示例：
select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences
在查询序列时，如果指定了nocache选项，则列last_number会显示序列中下一个有效的值
NEXTVAL和CURRVAL伪列：
1、NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
2、CURRVAL 中存放序列的当前值
3、NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误。
select test_id.currval from dual;返回序列当前值
select test_id.nextval from dual;返回序列下一个有效值
使用序列：
1、将序列值装入内存可提高访问效率
2、序列在下列情况下出现裂缝:
a、回滚
b、系统异常
c、多个表同时使用同一序列
3、如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值
修改序列：
alter sequence test_id
increment by 20
maxvalue 99999
nocache
nocycle
删除序列：
drop sequence test_id;
二、索引
索引介绍：
1、一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
2、索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
3、索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
4、在删除一个表时,所有基于该表的索引会自动被删除
5、通过指针加速 Oracle 服务器的查询速度
6、通过快速定位数据的方法，减少磁盘 I/O
创建索引：
1、自动创建：在定义PRIMARY KEY或UNIQUE约束后系统自动在相应的列上创建唯一索引
2、手动创建：用户可以在其他列上创建非唯一索引，以加速查询。
创建索引语法：
CREATE INDEX index
ON table(column[, column]...);
示例:
create index emp_last_name_idx
on employees(last_name)
什么时候创建索引：
1、列中数据值分布范围很广
2、列经常在WHERE子句或连接条件中出现
3、表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
什么时候不要创建索引：
1、表很小
2、列不经常作为连接条件或出现在WHERE子句中
3、查询的数据大于2%到4%
4、表经常更新
查询索引：
此处查询时ic.table_name = 'EMPLOYEES'区分大小写。
select ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
from user_indexes ix, user_ind_columns ic
where ic.index_name = ix.index_name
and ic.table_name = 'EMPLOYEES'
删除索引：
1、只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引
2、删除操作是不可回滚的
语法：
drop index index_name;
三、同义词-synonym
使用同义词访问相同的对象
1、方便访问其他用户的对象
2、缩短对象名字的长度
创建同义词语法：
CREATE [PUBLIC] SYNONYM synonym
FOR object;
示例：
CREATE SYNONYM e for employees;
此时可以直接使用：select * from e;来查询employees表的数据
删除同义词：
drop sysnonym e;
D:\工作&学习&软件\学习\尚硅谷视频\Oracle\[尚硅谷]_宋红康_oracle_sql_plsql课件_章节练习_资料\[尚硅谷]_宋红康_oracle_sql_plsql课件_章节练习_资料\sql扩充知识
创建用户权限
一、创建用户
创建用户：
CREATE USER user_name
IDENTIFIED BY password;
示例：create user mfcuser identified by mfcuser;
赋予用户权限：
grant create session,create table,
create sequence,create view
to mfcuser;
创建用户表空间
ALTER USER atguigu01 QUOTA UNLIMITED
ON users
创建角色：
CREATE ROLE manager;
为角色赋予权限：
GRANT create table,create view TO manager;
将角色赋予用户：
GRANT manager TO mfcuser;
修改密码：
ALTER USER mfcuser IDENTIFIED BY mfc123;
删除用户：
DROP USER mfcuser;
二、分配对象权限
分配表employees的查询权限：
只有对象所在的用户才能分配该对象的权限
grant select on employees to mfcuser;
分配表中各个列的更新权限
GRANT update
ON scott.departments
TO mfcuser
三、WITH GRANT OPTION和PUBLIC关键字
WITH GRANT OPTION 使用户同样具有分配权限的权利：
GRANT select, insert
ON departments
TO scott
WITH GRANT OPTION;
向数据库中所有用户分配权限：
GRANT select
ON alice.departments
TO PUBLIC;
四、查询权限分配情况
数据字典视图 描述
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 用户拥有的系统权限
五、收回对象权限
1、使用 REVOKE 语句收回权限
2、使用 WITH GRANT OPTION 子句所分配的权限同样被收回
语法：
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
示例：
REVOKE select, insert
ON departments
FROM scott;
SET 运算符
一、UNION操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集，去掉重复数据
示例：
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;
二、UNION ALL操作符
UNION ALL 操作符用于合并两个或多个 SELECT 语句的结果集，不去掉重复数据
示例：
SELECT employee_id, job_id
FROM employees
UNION ALL
SELECT employee_id, job_id
FROM job_history;
三、INTERSECT操作符
INTERSECT 操作符返回两个结果集的交集
示例：
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history
四、MINUS操作符
MINUS操作符：返回两个结果集的差集
示例：
SELECT employee_id, job_id
FROM employees
MINUS
SELECT employee_id, job_id
FROM job_history
高级子查询：
一、多列子查询
-- 问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id
-- 成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);
-- 不成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN (SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
二、在FROM子句中使用子查询
-- 问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
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;
-- 问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
select last_name,department_id,salary,
(select avg(salary)from employees e3
where e1.department_id = e3.department_id
group by department_id) avg_salary
from employees e1
where salary >
(select avg(salary)
from employees e2
where e1.department_id = e2.department_id
group by department_id
)
三、单列子查询表达式
-- 问题：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。
SELECT employee_id, last_name,
(CASE
WHEN department_id =
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
-- 问题：查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT employee_id, last_name
FROM employees e
ORDER BY
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
四、相关子查询
-- 问题：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id);
-- 问题：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_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操作符
1、EXISTS 操作符检查在子查询中是否存在满足条件的行
2、如果在子查询中存在满足条件的行:
a、不在子查询中继续查找
b、条件返回 TRUE
3、如果在子查询中不存在满足条件的行:
a、条件返回 FALSE
b、继续在子查询中查找
-- 问题：查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id =
outer.employee_id);
-- 问题：查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id
= d.department_id);
六、相关更新
-- 先在employees表中添加一个字段
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);
相关删除
-- 问题：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees E
WHERE employee_id =
(SELECT employee_id
FROM emp_history
WHERE employee_id = E.employee_id);
七、WITH子句
1、使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
2、WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
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 s
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
2018年11月9日19:51:30补充：Oracle创建定时任务：
定时任务可以直接使用PlSQL创建，也可以使用图形化界面，在PL/SQL里面有个文件夹DBMS_Jobs，在这里面根据要求填写相应参数，然后应用即可，下面主要记录使用命令创建和操作定时任务：
-- 创建一个存储自动任务执行日志的表
create table testJob(
tid varchar2(100), -- 执行ID
tdate date, -- 执行时间
tdesc varchar2(4000) -- 执行描述
);
-- 创建定时调用的存储过程
CREATE OR REPLACE PROCEDURE testJobPro AS
BEGIN
insert into testJob(tid,tdate,tdesc) values(to_char(sysdate,'yyyymmddhh24miss'),sysdate,'自动任务testJob执行' );
commit;
END;
-- 测试存储过程
begin
testJobPro();
end;
-- 创建定时任务
declare
tm_job number;
begin
dbms_job.submit(
job => tm_job,
what => 'testJobPro;', --定时执行的存储过程
next_date => sysdate,--next_date，可以不填
interval => 'sysdate+1/(24*60*10)'--'Interval时间字符串' --interval，关键设置
);
end;
-- 启动定时任务
BEGIN
dbms_job.run(5);
END;
-- 停止自动任务
begin
dbms_job.broken(5,true,next_date => sysdate);
commit;
end;
-- 删除自动任务
begin
dbms_job.remove(5);
commit;
end;
-- 查询定时任务
select job, what, next_date, next_sec, sysdate, failures, broken,interval from user_jobs a;
-- 查询自动任务调用的存储过程
select * from testJob;
创建定时任务时，dbms_job.submit的参数interval的值可取：
（1）.每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)
每小时执行
Interval => TRUNC(sysdate,'hh') + 1/ (24)
（2）.每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate+ 1) +1/ (24)
（3）.每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
（4）.每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
（5）.每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
（6）.每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
（7）.每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24
2019-5-22 补充，oracle存储过程中分割字符串，类似java中的split：
create or replace type type_str is table of varchar2(100)
create or replace function split(p_str varchar2,p_delimiter varchar2 default ',') return type_str
is
rs type_str:=type_str();
l_str varchar2(4000):='';
l_len number:=0;
begin
l_str:=p_str;
l_len:=length(p_delimiter);
while length(l_str)>0 loop
if instr(l_str,p_delimiter)>0 then
rs.extend;
rs(rs.count):=substr(l_str,1,instr(l_str,p_delimiter)-1);
l_str:=substr(l_str,instr(l_str,p_delimiter)+l_len);
else
rs.extend;
rs(rs.count):=l_str;
exit;
end if;
end loop;
return rs;
end;
测试：
SELECT COLUMN_VALUE
FROM TABLE(SPLIT('1001,1002,1003'));
结果（三行数据）：
1001
1002
1003
2019-9-27 补充：数据库表被锁之后解锁：
-- 查看哪些表被锁
SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE
FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
WHERE B.OBJECT_ID = A.OBJECT_ID;
-- 根据上面查询的SESSION_ID查询被锁的SERIAL#
SELECT B.USERNAME, B.SID, B.SERIAL#, LOGON_TIME
FROM V$LOCKED_OBJECT A, V$SESSION B
WHERE A.SESSION_ID = B.SID
ORDER BY B.LOGON_TIME;
-- 根据上面查询的SID查询时哪个机器连接数据库锁定了表
select username,serial#, SID,OSUSER,MACHINE from v$session WHERE SID='2913';
-- 解锁：alter system kill SESSION 'SID,serial#';
alter system kill SESSION '2913,34082';
2019-12-6 补充：存储过程被锁不能编译：
-- 查询出被锁的存储过程的SID
select va.sid,va.OBJECT from v$access va where object like 'ETL_PAYMENT_%';
-- 根据SID查询出 SERIAL#
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID= 2427;
-- 杀死Session ：SID,SERIAL#
alter system kill session '2427,32480';2020-1-17 补充：月份转为英文全写并且首字母大写：
select initcap(to_char(to_date('2012-10-20','yyyy-mm-dd'),'month','NLS_DATE_LANGUAGE = English')) from dual;
扫一扫
8514
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
