Java学习之路-oracle数据库

数据库呀，想了想，到数据库了，一开始学的第一个数据库就是oracle，学sql语句，虽然后面也接触了pl，但感觉还是建立在sql上面学的。

oracle安装emmm都到这了，应该开源软件的安装没什么问题的吧。

官方的下载地址： www.oracle.com/technetwork…

下载安装，网上随便就能找到个教程。

1、数据库简介

1.1、Oracle公司

甲骨文软件系统有限公司，目前是全球第2大软件公司（第1：微软），Oracle公司在数据库市场，毫无争议第1

1977 ：软件开发实验室”（Software Development Labs）；

1978 ：关系式软件公司”（RSI）

1982 ：甲骨文软件系统有限公司（Oracle Systems Corporation）

Oracle合伙人：Ed Oates，Bob Miner，Larry Ellison；**Bruce Scott **是Oracle雇佣的第1个员工

1.1、Oracle数据库客户端

启动OracleServiceOrcl —– sqlplus (1)

启动OracleOraDb11g_home1TNSListener: sqldeveloper(2)

启动OracleDBConsoleorcl:web管理客户端(3)

https://localhost:1158/em

小结：安装Oracle数据库，产生7个服务

OracleServiceOrcl :必须启动

OracleOraDb11g_home1TNSListener：可以不启动（使用远程连接访问Oracle数据库需要启动）

OracleDBConsoleorcl:可以不启动（web管理需要启动）

OracleJobSchedulerORCL ：可以不启动（作业调度）

OracleMTSRecoveryService | OracleOraDb11g_home1ClrAgent :可以不启动 微软技术相关需要启动的服务

Oracle ORCL VSS Writer Service：可以不启动 （卷映射拷贝写入服务）

Oracle数据库端口：1521 | MySql数据库端口：3306 | SQL Server端口：1433

1.2、SqlPlus设置

sqlplus是Oracle命令行客户端程序

启动sqlplus 并登录：sqlplust scott/tiger

show user :显示当前登录用户
select * from tab;查看当前用户下有哪些表资源(默认看到4张表 --EMP|DEPT|SALGRADE|BONUS)
set pagesize 40 设置每页显示的条数为40（大小可以自己定）
set linesize 300 设置每行显示的字符数300(大小自己定)
quit:退出sqlplus

1.3、账号管理基础

如果忘记sys账号的密码，怎么登录

sqlplus
sys/nolog as sysdba  :不需要密码直接进入系统;
alter user sys identified by orcl :修改密码;
conn scott/tiger  切换账号
alter user scott account lock; 给scott账号锁定
alter user scott account unlock:给scott解锁账号

1.4、数据库系统

一个完整的数据库系统由下面几个部分构成

硬件系统

操作系统

数据库管理系统(Oralce带管理系统)

数据库（数据仓库：存数据） — 文件(DBF — 数据文件，CTL(控制文件),LOG(日志文件));

数据库管理员(DBA)

1.5、数据库发展历史

内存：不能持久化

文件：存数据

层次|网状数据库：可以存贮 ，可以维护、统计、分析

**关系型数据库：**数据以表为单位保存

1.6、数据库产品

大型数据库：甲骨文的Oracle数据库 | Sysbase公司的Sysbase数据库|IBM公司的DB2数据库

中型数据库：MySQL数据库(开源) | 微软 SQL Server

小型数据库：Access数据库(MS) — ASP(.NET) | Sqlite(嵌入式数据库 –一个数据库文件，不需要安装任何服务)

Oracle数据库有很多对象（表、视图、索引、程序包、过程、函数……….）,数据是存贮在表这个对象中；

表结构：行（数据）、列（表结构）；表的每一行称为记录(实体)

Oracle数据库的几个关键版本：i(版本oracle8i,oracle9i)、g版本(oracle10g,oralce11g),c版本(oracle12c)

2、SCOTT账号下的表对象

select * from tab #查看 scott账号下所有的表对象（4个）
DEPT #部门表
EMP #员工表
SALGRADE #工资等级表
BONUS #奖金表（没数据）

DESC 表名 #显示表结构

DEPT #部门表
DEPTNO	NUMBER(2)
DNAME	VARCHAR2(14)
LOC		VARCHAR2(13)

EMP #雇员员
EMPNO   NUMBER(4)
ENAME   VARCHAR2(10)
JOB     VARCHAR2(9)
MGR  	NUMBER(4)
HIREDATE DATE
SAL		 NUMBER(7,2)
COMM	 NUMBER(7,2)
DEPTNO   NUMBER(2)

SALGRADE #工资等级表
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER

BONUS #奖金表
ENAME     VARCHAR2(10)
JOB		  VARCHAR2(9)
SAL		  NUMBER
COMM	  NUMBER

3、SQL

SQL：Structure Query Language(结构化查询语言) 是所有关系型数据库都遵守的语言，为了满足各数据库特定业务需要，每一个数据库厂商在尊守SQL的基础上，对SQL语法做了扩展（此部分每个数据库是不一样），Oracle数据库对SQL进行了扩展，扩展的语法称为：PL/SQL,SQLServer数据库扩展的语法称为：T-SQL

SQL分为以下种：DQL | DML | DDL|DCL|TCL

DQL ：Data Query Language 数据查询语言; —– SELECT 【重点|难点】

DML : Data Manipulation  Language 数据操作语言； —– INSERT | UPDATE | DELETE

DDL：Data Definition Language 数据定义语言 — CREATE | ALTER | DROP

DCL： Data Control Language 数据控制语言 — GRANT | INVOKE

TCL：Transaction Control Language 事务控制语言 — COMMIT | ROLLBACK | SAVEPOINT

面试题：写SQL语句题（以查询为主）

3.1 基本 SELECT 语句

基本 SELECT 语句
SELECT 
	*|{[DISTINCT] column|expression [alias],...}
FROM table;

#显示emp表所有属性数据
SELECT * FROM emp;(SELECT EMPNO,ENAME, JOB,MGR ,HIREDATE,SAL,COMM, DEPTNO FROM emp;)
#显示emp表的EMPNO，ENAME，JOB，HIREDATE,SAL
SELECT empno,ename,job,hiredate,sal FROM emp;
#显示emp表部门编号
 SELECT DISTINCT deptno  FROM emp;
#表达式：查询语句中可以包含表达式（主要是算述表达式）

#显示emp表员工的编号，姓名，职务、薪水，及加200后的薪水;
SELECT empno,ename,job,sal, sal+200  FROM emp;
#显示emp表员工的编号，姓名，职务、本月工资
SELECT empno,ename,job,sal+NVL(comm,0) FROM emp;
#显示emp表员工的编号，姓名，职务、本月工资 并取别名工资
SELECT empno,ename,job,sal+NVL(comm,0) monthsal  FROM emp;
SELECT empno,ename,job,sal+NVL(comm,0) AS monthsal  FROM emp;

3.2 条件过滤

SELECT 
	*|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]

#查询员工编号为7369的员工 #查询部门编号为10的员工;
SELECT * FROM emp WHERE empno=‘7369’

要求：设置条件时，属性的值建议用单引号括起来(不管什么类型的数据)

#条件查询：比较运算符(=,> >= < <= <>|!=)
#查询薪水大于等于2000的所有员工
ELECT * FROM emp WHERE sal>=2000;

#BETWEEN ... AND
#查询工资在2000 到3000之间的所有员工
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;
SELECT * FROM emp WHERE sal >=2000 AND sal<=3000;

#IN (适合属性的值为固定的某些值) | NOT IN
#查询EMP表中职务为：CLERK,SALESMAN的所有员工
SELECT * FROM emp WHERE JOB IN('CLERK','SALESMAN');
SELECT * FROM emp WHERE JOB NOT IN('CLERK','SALESMAN','DEVELOPER');

#LIKE:实现模糊查询，配合模糊查询的两个通配符：%:0个或者任意多个字符 _：任意的单个字符
#查询EMP表中姓名中有A字符的所有员工;
 SELECT * FROM emp WHERE ename LIKE '%A%';
#查询EMP表中job以A开始的所有员工;
SELECT * FROM emp WHERE job LIKE 'A%';
#查询EMP表中姓名第2个字符为A的员工？
SELECT * FROM emp WHERE ename like '_A%';

#IS NULL | IS NOT NULL
#查询佣金为空的所有员工
SELECT * FROM emp WHERE comm IS NULL;

#多条件：需要使用逻辑运算符：NOT AND OR
#查询部门编号为30并且工资大于2500的员工;
SELECT * FROM emp WHERE deptno=30 AND sal > 2500
#查询部门编号为30并且工资大于2500的员工以及部门编号为20工资小于1500;
SELECT * FROM emp WHERE (deptno=30 AND sal > 2500) OR (deptno=20 AND sal < 1500)

3.3 排序

SELECT 
	*|{[DISTINCT] column|expression [alias],...}
FROM table 
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];

#对EMP表，按工资升序排序;
SELECT * FROM emp ORDER BY sal ASC;
默认为升序（如果是升序，可以省略 ASC）

#查询部门为30的员工，按工资降序排序;
SELECT * FROM emp  
WHERE　deptno=30　 
ORDER BY sal DESC
#排序可以设置多个排序属性（列）
 SELECT * FROM emp ORDER BY sal ASC,empno DESC;

SELECT 
FROM 
WHERE
ORDER BY

4、Oracle单行函数

函数是Oracle数据库中一个对象,函数分为内置函数（单行函数），自定义函数；函数的主要功能是用来执行运算的； Oracle中的单行函数：字符函数，数值函数，日期函数，转换函数，通用函数

dual：oracle数据库中的一张虚拟表，主要用来做函数与表达式测试的;

4.1 字符函数

#常见的字符函数：lower upper initcap
SELECT LOWER('HELLO'),UPPER('hello'),INITCAP('hello,world') FROM dual;
SELECT  LOWER(ename),UPPER(ename),INITCAP(ename)  FROM emp;
#常见的字符函数：CONCAT SUBSTR LENGTH LPAD | RPAD  TRIM REPLACE INSTR
#CONCAT(字符串1,字符串2),
#SUBSTR(字符串,start,len)
#LPAD(字符串，len ,'补全的字符') | RPAD(字符串，len ,'补全的字符')
#TRIM:去左右空格(中间去不掉)
#REPLACE(替换的字符串,'oldchar','newchar')
#INSTR(字符串,'字符')

4.2 数值函数

数值运算的几个常用函数：round trunc ,mod

SELECT ROUND(12.1256,2),ROUND(12.1256,1) ,ROUND(12.1256)FROM dual;
SELECT TRUNC(12.123) ，TRUNC(12.125,2) FROM dual;#可以保留小数，但不会做四舍五入处理;
SELECT mod(10,3) FROM dual;

4.3 日期函数

sysdate可以获取当前的日期与时间，oracle数据库默认的日期格式为：日-月-年,如果需要按按中文的格式显示需要用到转换函数：to_char;

SELECT sysdate from dual;
yyyy || yy :年份
MM|mm :月分
dd |d :日
HH24|hh12 :时
MI|mi :分
SS|ss : 秒

oracle中的日期函数：months_between |add_months|next_day|last_day; 需要理解日期运算

日期 – 日期 = 天数

日期 + 数字（天数） = 日期

日期 – 数字（天数 ）= 日期

#显示每一个雇员雇佣月分,天数
select 
	ename,
	to_char(hiredate,'yyyy-MM-dd'),
	trunc(months_between(sysdate,hiredate)) ,
	trunc(sysdate-hiredate)
from emp;
#5个月后的今天
select 
	to_char(sysdate,'yyyy-MM-dd'),
	to_char(add_months(sysdate,5),'yyyy-MM-dd') 
from dual;
#next_day:求某一日期的下一个日期
select sysdate,next_day(sysdate,'星期四') ,next_day(sysdate,4) from dual;
next_day(curdate,'format')format的值可以为：[1-7]或者[星期一,星期日]
#5月的最后一天
select last_day(sysdate) from dual;
SELECT * FROM emp WHERE hiredate = last_day(hiredate) ;

算出每一个雇员 雇佣了多少年，多少月，多少天

select  
	ename,
	to_char(hiredate,'yyyy-MM-dd') 雇佣日期,
	trunc(months_between(sysdate,hiredate) /12) 年 ,
	trunc(mod(months_between(sysdate,hiredate),12)) 月,
	sysdate - add_months(hiredate, (months_between(sysdate,hiredate))) 天	
from emp;

4.4 转换函数

#oracle中常用的转换函数：to_char,to_number,to_date
to_date(char,'format') :将一个字符串格式的日期转成一个日期类型
select to_date('2012-05-22 20:49:10','yyyy-mm-ss hh24:mi:ss') from dual;

4.5 空值处理函数

#空值处理函数：NVL | NVL2 | 
#计算年终奖:佣金不为空，年终奖为基本工资的1.5倍，佣金为空，年终奖为基本工资1.2倍
select ename, nvl2(comm,sal*1.5,sal*1.2) from emp;
#判断佣金，如果佣金为空，显示此雇员上司，如果此雇员没有上司，则显示"老板"
SELECT COALESCE(TO_CHAR(comm),TO_CHAR(mgr),'老板') FROM emp;

4.6 条件表达式

条件表达式：CASE表达式、DECODE表达式，此2个表达式功能类似；

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 
  empno,ename,job,sal,
  case deptno
  	when 10 then 'ACCOUNTING'
  	when 20 then 'RESEARCH'
  	when 30 then 'SALES'
  	when 40 then 'OPERATIONS'
  	else '没有此部门'
  end  部门名称
from emp;

select 
  empno,ename,job,sal,
  decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','没有此部门')
	部门名称
from emp;

5、多表查询【这是个重点】

当需要同时取出多张表的数据时，就需要联合多表来查询，多表查询会产生笛卡尔乘积会产生的记录数：N1*N2,由此可知，多表查询效率是很底的（如果单表查询能解决，尽量不要用多表查询）

多表联合查询条件设置： 根据两张表的关联属性设条件(emp表中有一个属性deptno — 外键,dept表中有一个属性deptno — 主键) —往往是一个表的主键在另一张表中充当外键;

也可以根据两张表的间接属性设置条件（上下文语义匹配）

5.1 等值查询

#查询雇员表中员工编号，姓名，职务，工资，及雇员的部门编号，部门名称，部门地址
select 
emp.empno ,emp.ename,emp.job,emp.sal,dept.deptno,dept.dname,dept.loc
from emp ,dept
where emp.deptno=dept.deptno;

#命名用别名
select  
e.empno ,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;

#查询雇员表中员工编号，姓名，职务，工资，及雇员的部门名称，部门地址及雇员的工资等级;
select
e.empno,e.ename,e.job,e.sal,d.dname,d.loc,s.grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal

#查询雇员表中员工编号，姓名，职务，工资，及雇员的部门名称，部门地址及雇员的工资等级;
#grade为1：显示 一级， 2 ：二级 3 :三级 4 四级 5 五级
select
e.empno,e.ename,e.job,e.sal,d.dname,d.loc,
case s.grade
when 1 then '实习工资'
when 2 then '初级工资'
when 3 then '中级工资'
when 4 then '高级工资'
when 5 then '架构师级工资'
end 等级
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

#查询雇员表中员工编号，姓名，职务，工资，雇员的部门名称，部门地址，雇员的工资等级以及雇员的上司;

select
e1.empno,e1.ename,e2.ename 上司,e1.job,e1.sal,d.dname,d.loc,s.grade
from emp e1,dept d,salgrade s,emp e2
where 
	e1.deptno=d.deptno 
	and e1.sal between s.losal and s.hisal
	and e1.mgr=e2.empno

5.2 连接查询

连接查询是多表查询实现方式之一，oralce 数据库中的连接查询分为：外连接(outer join)和内连接(inner join)

内连接：等效于等值查询(完全一样)

#查询雇员表中员工编号，姓名，职务，工资，及雇员的部门编号，部门名称，部门地址
select 
e.empno ,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
from emp e inner join dept d on e.deptno=d.deptno;
#查询雇员表中员工编号，姓名，职务，工资，及雇员的部门名称，部门地址及雇员的工资等级;
select
e.empno ,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc,s.grade
from emp e 
	inner join dept d on e.deptno=e.deptno 
	inner join salgrade s on e.sal between s.losal and s.hisal;
#查询雇员表中员工编号，姓名，职务，工资，雇员的部门名称，部门地址，雇员的工资等级以及雇员的上司; 
select
e1.empno ,e1.ename,e2.ename 上司,e1.job,e1.sal,d.deptno,d.dname,d.loc,s.grade
from emp e1 
	inner join dept d on d.deptno=e1.deptno  
	inner join salgrade s on e1.sal between s.losal and s.hisal
	inner join emp e2 on e1.mgr=e2.empno;

外连接： 分为左向外连接、右向外连接、完全外连接

左连接：显示左侧表所有数据及右则表满足条件的数据（去笛卡尔乘积）

右连接：显示右侧表所有数据及左则表满足条件的数据（去笛卡尔乘积）

左连接与右连接是可以转换的（只是表的位置不一样而以）

#显示所有的部门以及部门的所有员工(左接接实现的)
select 
d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from dept d left outer join emp e on d.deptno=e.deptno;
#显示所有的部门以及部门的所有员工(右接接实现的)
select 
d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from emp e right outer join dept d on e.deptno=d.deptno;

完全外连接:显示左侧、右侧表所有数据（去笛卡尔乘积）

#显示emp表所有数据及部门表所有数据（去笛卡尔乘积）
select 
d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from emp e full outer join dept d on e.deptno=d.deptno;

5.3 自然连接

等值查询或者连接查询语法的简化版，当关联的属性名称在2张表中相同时才可以使用;

查询雇员表中的姓名、职务、薪水以及雇员所属的部门名称及部门地址 ----(deptno)
SELECT 
 e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e natural join dept d  

5.4 using子句

using子句 :两张表有相同的关联属性(deptno),可以直接使用using简化条件设置

查询雇员表中的姓名、职务、薪水以及雇员所属的部门名称及部门地址 ----(deptno)
SELECT 
 e.ename,e.job,e.sal,d.dname,d.loc
FROM  emp e join dept d  using(deptno)

6、子查询

子查询：一个查询内部包含的查询称为子查询(外部的查询称为主查询),子查询的结果往往是外部查询（主查询）的条件，所以子查询要先运行; 子查询需要用括号括起来; —子查询的结果是单列的

单行子查询：子查询的结果就一个值
#查询比FORD工资高的所有员工
select * from emp where sal > select sal from emp where ename='FORD'

#查询与FORD同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename='FORD')


#小结：单行子查询 支持的比较运算符：>,>= <,<= = <>

多行子查询：子查询的结果有多个值; 过滤条件时需要结合谓词：ALL 、ANY,IN(SOME),不能再用:= >,>=,< <= <>

#查询比部门20所有员工 工资都高的所有员工
select * from emp where sal > all ( select sal from emp where deptno=20)
#查询工资高于部门20其中一个员工工资的所有员工
select * from emp where sal > any ( select sal from emp where deptno=20)

IN子查询询
查询部门编号为：10，20的员工
select * from emp where deptno in (10,20) #查询与部门编号为20的雇员工资相同的员工;
 select * from emp where sal in (select sal from emp where deptno=20) 

oracle数据库分页问题 — 子查询

Oracle数据库表的分页展示，要求表中有一个具有连续行号的一个属性，此属性Oracle直接提供，通过ROWNUM得到行号(ROWNUM生成的列称为伪例)；Oracle数据库的分页需要定一个三层嵌套的SQL

第一层SQL：解决排序的

第二层SQL：解决生成伪例（产生一个连续的行号–分页需要）

第三层SQL：解决获取某一页数据

分页：数据库得到2个参数：pageindex=1,pagesize=4
select * from 
 (select e.*,rownum rn from  
  	(select * from emp order by empno asc) e ) #得到一个结果集
where rn between startposition and endposition

startposition:(pageindex-1)* pagesize + 1
endposition : pagesize * pageindex

以上可以实现Oracle数据库表数据分页，但运行时，效率低一些; --上面SQL优化后


 SELECT * FROM 
   (SELECT e1.*,rownum rn FROM
	  (SELECT * FROM emp order by empno asc) e1  WHERE rownum <=endposition)
 WHERE RN >=startposition
 
startposition:(pageindex-1)* pagesize + 1
endposition : pagesize * pageindex


select * from emp limit startposition,endposition  :mysql分页

分页显示数据，必须向数据库传递2个参数：pageindex(当前页), pagesize(每页大小)

表：总记录确定 / pagesize —> 总页数 —— pageindex —> 得到当前页数据

pageindex=1,pagesize =4

查询表中第31 到40 之间的数据（users）

 select * from 
 	(select e.*,rownum rn from  
 		(select * from users) e) 
 where rn between 31 and 40

7、分组查询

分组查询：得到的是以组为单位的数据，分组查询是为统计服务的,分组首先需要知道基于组进行统计的组函数(聚合函数)

Oracle 数据库常用的几个分组用的组函数：sum ,avg,max,min,count

 select 
 	sum(sal+nvl(comm,0)) 每月分的总工资,
 	avg(sal+nvl(comm,0)) 平均工资,
 	max(sal + nvl(comm,0)) 最高工资 ,
 	min(sal+nvl(comm,0)) 最低工资,
 	count(*) 总人数  
 from emp;

count(*),count(1),count(列) 区别

分组总是和组函数结合在一起的（不分家），分组查询得到是以组为单位的统计结果;按什么进行分组,分组查询能显示的结果：分组的列，组函数

经验：按表的哪一个属性进行分组（分组的列往往是有重复数据的列：部门|职务|性别）

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
HAVING group_condition] -- 对分组后的数据进行过滤，用having
[ORDER BY column];

#查询emp表数据，按部门进行分组，统计每个部门每月发放的总工资，最高工资，最低工资，平均工资，总人数;
select 
 deptno, sum(sal),max(sal),min(sal),avg(sal),count(*)
from emp 
group by deptno having min(sal) < 3000

总结：找准分组的列 | 显示的数据（组函数+分组的列）

ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2) 
RANK() OVER(PARTITION BY col1 ORDER BY col2)
DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col2)

8、DML

DML : Data Manipulation Language 数据操作语言； —– INSERT | UPDATE | DELETE

INSERT INTO table_name (column1,column2,...) values ( value1,value2, ...);
UPDATE table_name set column1=new value,column2=new value,... [WHERE <条件>]
DELETE FROM <table_name> WHERE <条件>

删除表全部数据

delete from emp -- 删除表所有数据 （可以恢复）
truncate table emp; --截断表所有数据 (不能恢复的)
区别：delete 删除表所有数据与truncate删除表数据区别

9.、事务(transaction)

会话（session）： 每建立一个与Oracle数据库的连接，表示与Oracle 数据库建立一个会话;

在Oracle 数据库中，对表进行增、删、改，默认并不没有持久化写入到表数据中，而是放在session缓冲区，如果没有提交数据，是不会持久化的;

想要提交数据，执行：commit — 作为一个事务提交的;当数据在提交之前，是可以回滚的,但一旦提交完成，不能回滚; 回滚操作：rollback 即可;

事务（transaction）：是一个工作单元，可以封装一系列操作，事务要求此系列操作要么都成功，要么都失败;事务执行完后，是不能回滚的;

事务特点|特性：

原子性 ：系列操作是不能分割的（必须作为一个整体一起提交到数据库中运行）

转帐：（2步操作：一个账号要减，一个账号要加）

一致性：事务操作前后数据必须操作一致;

隔离性：事务的操作不受其它事务影响

持久性：事务一旦完成，将持久写入数据

Oracle数据库默认（增、删、改）操作并没有开启事务,所以需要手动提交,可以开启Oracle数据库自动事务

set autocommit on;

set autocommit off;

10、DDL（表相关）

DDL：Data Definition Language 数据定义语言 — CREATE | ALTER | DROP

Oracle数据库对象的创建默认是基于事务的;

10.1 Oracle数据类型

10.2 创建表

CREATE TABLE <table_name>( 
column1 DATATYPE [NOT NULL] [PRIMARY KEY], 
column2 DATATYPE [NOT NULL],
...
[constraint <约束名> 约束类型 (要约束的字段)
... ] ）

创建一张表(tb_students)

tb_students
---------------------------------------------------
属性名        类型           约束     备注
sid			varchar2(50)            学生编号
sname       varchar2(100)           学生姓名
sgender     varchar2(10)            学生性别
semail      varchar2(50)	        学生邮箱
stel		varchar2(20)			电话
sbirthday   date                    出生日期
......

关于表主键属性的赋值：主键约束要求属性值不能为空，属性值不能重复，主键属性的值一般采用以下2种方案：

1、标识列 : 是数字类型（整数）,会自动生成，在mysql，sqlserver数据库中很方便，在oracle数据库中不太方便

2、uuid ：全球唯一的一串字符串值;生成uuid可以java语言中生成，再传到数据库中，也可以直接使用oralce生成唯一的uuid 值;

产生UUID两种方式 调用oracle数据库中的：SYS_GUID() 函数生成 |  通过JAVA生成
String uuid =UUID.randomUUID().toString();

10.3 序列

CREATE SEQUENCE <sequencen_name> 
INCREMENT BY n
START WITH n
[MAXVALUE n][MINVALUE n]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];

1 CURRVAL :获取序列当前值;
2.NEXTVAL :下一个序列值

序列：生成一个连续的标识值

mysql数据库生成标识列：autoincrement ,sqlserver数据库中产生标识列：identity

10.4 删除表

drop table tablename;

10.5 修改表结构

添加列 | 删除列 | 修改列

ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL]
alter table tb_students add saddress varchar2(100) 
ALTER TABLE <table_name> drop (字段1,字段2.... );
alter table tb_students drop (saddress);
ALTER TABLE <table_name> modify(字段1 类型,字段2 类型.... );
alter table tb_students modify (sname varchar2(50))

10.6 Oracle闪回机制

oracle数据库10g版本后，增加闪回机制，表对象删除默认放到回收站对象中存放;

查看oracle 回收站
show recyclebin;
从回收站中恢复被删除的表;
flashback table 表名 to before drop [rename to 新表名]
从回收站中再次册除表 ---彻底没了
purge table 表名
清空回收站
purge recyclebin
删除表对象，不放到回收站（永久删除）
drop table tablename purge;

10.7 修改表名

RENAME <table_name> to <new table_name>;

10.8 复制表

复制表结构与数据 | 复制表结构

create table deptbak as select * from dept;
create table deptbak as select * from dept where 1=2;

11、约束(constraint)

约束对象：表数据约束 — 有效性 + 完整性 === 正确性

约束添加的时机：可以在创建表时同时添加约束; 也可以表创建完后，再追加约束;

主键约束：一般建表是需要添加主键约束的（主键是确定表数据的唯一标识） 主键约束要求：非空 + 维一

非空约束：值不能为空;

检查约束：验证属性值的有效性

外键约束：表中的属性值关联外部表的某一个属性值

唯一约束：值不重复（可以为空值，但只能有一个空值）

create table tb_students
(
  sid varchar2(50),
  sname varchar2(100)  constraint null_students_sname not null ,
  sgender varchar2(10) ,
  mid varchar2(50),
  stel varchar2(20),
  constraint pk_students_sid primary key (sid) ,
  constraint ck_students_sgender check(sgender='男' or sgender='女'),
  constraint fk_students_mid foreign key (mid) references tb_majors(mid),
  constraint un_students_stel unique(stel)
)

追加约束 
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> 约束类型 (针对的字段名);
删除约束
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;


alter table tb_students add constraint pk_students_sid primary key(sid)
alter table tb_students add constraint ck_students_sgender check(sgender='男' or sgender='女')
alter table tb_students add constraint fk_students_mid  foreign key (mid) references tb_majors(mid);
alter table tb_students add constraint un_students_stel  unique(stel)
alter table tb_students  modify sname varchar2(50) constraint null_students_sname  not null 

12、视图(View)

视图：基于源表查询得到的结果，视图是不存放数据的（数据存在源表中） — 虚拟表,

视图对象的作用：可以屏蔽源表中比较敏感的数据; 可以保存复杂的SQL

CREATE [OR REPLACE] VIEW <view_name> 
AS
<SELECT 语句>;
#屏蔽 emp 表中的sal,comm信息，其它信息用户可查看;

create or replace view v_emp
as
	select empno,ename,job,hiredate,deptno from emp;
	
create or replace view v_select
as
    select e1.ename,e2.ename 上司,d.dname,e1.sal,s.grade  from emp e1 
		join dept d on e1.deptno=d.deptno
		join salgrade s on e1.sal between s.losal and s.hisal
		join emp e2 on e1.mgr = e2.empn
		
		
select * from v_emp;
select * from v_select;

DROP VIEW <view_name>  -- 删除视图

13、同义词

同义词：对象的别名称为同义词

CREATE SYNONYM <synonym_name> for <tablename/viewname>

DROP SYNONYM synonym_name

14、索引

理解索引：索引服务于查询，索引是数据库的一个对象，索引对象类似图书目录 ，当对创建索引后，在查询时，oracle数据库会自动使用索引; 当对表数据进行增删改，oracle会自动维护索引;

创建索引环境：表数据比较多，表的属性值不需要频繁更新数据的属性，经常设置条件过滤的属性

不适合创建索引：表数据比较小，频繁更新数据的属性

CREATE  INDEX <index_name> ON <table_name>(字段 [ASC|DESC]);

create index index_ename on emp (ename,asc)
select * from emp where ename ='';

对表来说，如果存在主键约束、维一约束，数据库会自动创建基于主键字段的索引及维一约束属性上索引

15、权限控制

熟悉Oracle 数据库的权限设置

以管理员的身份进入系统：创建账号
create user 账号 identified by 密码;

conn stu/123 --- > ORA-01045: user STU lacks CREATE SESSION privilege; logon denied
标识当前账号没有会话的权限

授予会话权限
grant 权限 to 账号
grant create session to stu
grant create table to stu 
grant resource to stu :此授权后，可以访问默认的表空间(USERS)


grant create view to stu;
grant create sequence to stu;
......

常见权限
Create session
Create table
Create view
Create sequence
Create procedure
Create trigger
Create function

撤销权限
revoke 权限 from 账号

授予某一账号访问本账号下资源的权权限

ORA-01950: 对表空间 'USERS' 无权限  :不能访问（读写）USERS表空间的资源,需要通过管理员授权
grant resource to stu :此授权后，可以访问默认的表空间(USERS)

grant 权限 on 表|视图…. To 账号
grant select|insert|update|delete on users to scott

当账号比较多时，需要基于角色管理权限
创建角色
create role 角色名称; | drop role 角色名称
给角色授权
 grant
 create session,
 create table,
 create view,
 create sequence,
 create trigger,
 create function,
 create procedure,
 resource to developer
 
再将角色赋给某一账号
grant developer to 账号1,账号2......; | revoke developer from user02;

16、PL/SQL

SQL 是所有关系型数据库都遵守的语法规则，PL/SQL是SQL语法的增强版，只适用于Oralce数据库，增强的主要是编程需要一些基本语法（面向过程语言中的语法基础）

常量、变量 、运算符、流程控制语句、异常处理等；

PL/SQL基本程序结构

[DECLARE]       --声明部分
        声明语句
BEGIN       --执行部分
        执行语句

[EXCEPTION]         --异常处理部分
        执行语句

END; 

# 在控制台输出hello,world
SET SERVEROUTPUT ON; --默认为OFF;
BEGIN
  DBMS_OUTPUT.PUT_LINE('hello,wrold');
END;
# 使用PL/SQL语法结构 添加数据V1
BEGIN
  INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (50,'研发中心','北京');
  --COMMIT;
END;
# 使用PL/SQL语法结构 添加数据V2
DECLARE
 vdeptno NUMBER := 70 ;
 vdname VARCHAR2 := '人事1';
 vloc VARCHAR2 := '天津1';
BEGIN
  INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (vdeptno,vdname,vloc);
  DBMS_OUTPUT.PUT_LINE('添加成功');
END;
#特殊类型 %type  :取出属性的类型 %rowtype :行类型
DECLARE
 vdeptno SCOTT.DEPT.DEPTNO%type := 70 ;
 vdname SCOTT.DEPT.DNAME%type := '人事1';
 vloc SCOTT.DEPT.LOC%type := '天津1';
BEGIN
  INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (vdeptno,vdname,vloc);
  DBMS_OUTPUT.PUT_LINE('添加成功');
END;
#取出deptno为10的部门并打印出当前记录的数据 ；V1
DECLARE
 vno DEPT.DEPTNO%type :=10; 
 vdname DEPT.DNAME%type ;
 vloc DEPT.LOC%type;
BEGIN
  SELECT * INTO vno, vdname,vloc FROM DEPT WHERE DEPTNO=vno;
   DBMS_OUTPUT.PUT_LINE('dno:' || vno || ' dname:' || vdname || ',loc' || vloc);
END;
#取出deptno为10的部门并打印出当前记录的数据 ；V2
DECLARE
  vrow DEPT%rowtype;
BEGIN
  SELECT * INTO vrow  FROM DEPT WHERE DEPTNO='10';
  DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc);
END;
#用户交互：&
DECLARE
  vrow DEPT%rowtype;
BEGIN
  SELECT * INTO vrow  FROM DEPT WHERE DEPTNO='&请输入部门编号';
  DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc);
END;
#测试异常
DECLARE
  vrow DEPT%rowtype;
BEGIN
  SELECT * INTO vrow  FROM DEPT WHERE DEPTNO='&请输入部门编号';
  DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc);
  EXCEPTION
    WHEN no_data_found THEN
     DBMS_OUTPUT.PUT_LINE('没有此部门');
    WHEN OTHERS  THEN
     DBMS_OUTPUT.PUT_LINE('未知错误.....');
END;

PL/SQL流程控制
DECLARE
vscore number(3) := '&请输入分数';
BEGIN
  IF vscore >= 60 THEN
   DBMS_OUTPUT.PUT_LINE('通过了考试');
  END IF;
END;

DECLARE
vscore number(3) := '&请输入分数';
BEGIN
  IF vscore >= 60 THEN
   DBMS_OUTPUT.PUT_LINE('通过了考试');
  ELSE
  DBMS_OUTPUT.PUT_LINE('考试未通过');
  END IF;
END;
--根据薪水判断：>4000 富裕 >3000 :小康 > 2000 :温饱 贫困 
DECLARE
 vempno EMP.EMPNO%type :='&empno';
 vsal EMP.SAL%type ;
BEGIN
 select sal into vsal from emp where empno=vempno;
 IF vsal >=4000 THEN
    DBMS_OUTPUT.PUT_LINE('富裕级别');
 ELSIF vsal >=3000 THEN
    DBMS_OUTPUT.PUT_LINE('小康级别');
 ELSIF vsal >=2000 THEN
    DBMS_OUTPUT.PUT_LINE('温饱级别');
 ELSE
    DBMS_OUTPUT.PUT_LINE('贫困级别');
 END IF;
END;

---------------------------------------------------------------------------------------
DECLARE
 v_i number :=1;
 v_sum number :=0;
BEGIN
  LOOP
    v_sum := v_sum + v_i;
    v_i := v_i + 1;
    EXIT WHEN  v_i > 100 ;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('1+2+3+...+100 = '  || v_sum );
END;

DECLARE
 v_i number :=1;
 v_sum number :=0;
BEGIN
  WHILE v_i <=100
  LOOP
    v_sum := v_sum + v_i;
    v_i := v_i + 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('1+2+3+...+100 = '  || v_sum );
END;

DECLARE
v_sum number :=0;
BEGIN
  FOR v_i IN 1..100
  LOOP
     v_sum := v_sum + v_i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('1+2+3+...+100 = '  || v_sum );
END;

17、游标(cursor)

游标（CURSOR ）： 是一指向查询结果集的一个对象（取名叫游标|指针），通过游标对象可以对结果集中的数据逐行读取；

Oracle数据库中游对象的使用：声明游标 —打开游标 —-获取数据 –关闭游标

声明游标： CURSOR <游标名> IS <SELECT 语句>
打开游标： OPEN　<游标名> 
获取数据： FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
                或者
    	 FETCH <游标名> INTO 行对象;         --取出游标当前位
关闭游标  CLOSE <游标名>
游标对象属性：%ISOPEN   |  %NOTFOUND

DECLARE
  vrow dept%rowtype;
  -- 1声明游标
  CURSOR curdept IS select * from dept order by deptno asc ; -- curemp游标类型的变量
BEGIN
  -- 2打开游标
  IF curdept%ISOPEN THEN
   CLOSE curdept;
  END IF;
  OPEN curdept;
  -- 3 获取数据 (FETCH <游标名> INTO 行对象| 变量; )
  LOOP
    FETCH curdept  INTO vrow; --第1次:移动游标对象到第1行，再取数据;
    DBMS_OUTPUT.PUT_LINE('DEPTNO: ' || vrow.deptno || ' ,DNAME: ' || vrow.dname || ',LOC : ' ||vrow.loc);
    EXIT WHEN curdept%NOTFOUND;
  END LOOP;
  -- 4 关闭游标
  CLOSE curdept;
END;

18、函数

函数：主要用来实现计算的，并且有返回值的；

定义:CREATE [OR REPLACE] FUNCTION <过程名>[(参数列表)] RETURN 数据类型 IS
         [局部变量声明]
         BEGIN
            可执行语句
          EXCEPTION
            异常处理语句
          END [<过程名>];

变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;

#声明一个函数，用来根据账号统计员工的年薪；
create or replace function fn_yearsal(eno in SCOTT.EMP.SAL%TYPE) RETURN NUMBER
IS
yearsal SCOTT.EMP.SAL%TYPE :=0;
BEGIN
  select (sal + nvl(comm,0))*12 into yearsal from emp where empno=eno;
  return yearsal;
END;

set serveroutput on;
declare
    n emp.sal%type;
    m emp.sal%type;
begin
    n:=&请输入员工号;
    m:=fn_yearsal(n);
    dbms_output.put_line('m的值为 '||m);
end;

19、过程

过程：oracle数据库中用业封装 对表进行操作的或者数据库维护的SQL；

定义:CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS
         [局部变量声明]
         BEGIN
            可执行语句
          EXCEPTION
            异常处理语句
          END [<过程名>];

变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;

#示例：定义一个过程 ，向表添加一条数据;
CREATE OR REPLACE PROCEDURE proc_insert
IS
BEGIN
  INSERT INTO dept(deptno,dname,loc) VALUES(50,'人事','北京');
  COMMIT;
END;

BEGIN
  proc_insert();
END;
#示例：定义一个过程 ，向表添加一条数据;
CREATE OR REPLACE PROCEDURE proc_insert2(dno IN DEPT.DEPTNO%TYPE,dn IN DEPT.DNAME%TYPE,loca IN DEPT.LOC%TYPE)
IS
BEGIN
  INSERT INTO dept(deptno,dname,loc) VALUES(dno,dn,loca);
  COMMIT;
END;

DECLARE
  dno NUMBER   := &dno;
  dname VARCHAR2(14) := '&dn';
  loc VARCHAR2(13) := '&loc';
BEGIN
  PROC_INSERT2(dno,dname,loc); 
END

#示例 ：统计表中记录数
create or replace PROCEDURE PROC_COUNT (dno IN NUMBER, total OUT NUMBER)
IS
BEGIN
  select count(*) into  total from emp where deptno=dno;
END;

DECLARE
  DNO NUMBER;
  TOTAL NUMBER;
BEGIN
  DNO := 10;

  PROC_COUNT(
    DNO => DNO,
    TOTAL => TOTAL
  );
  :TOTAL := TOTAL;
END;

20、[数据]包

包（package)：oracle对象包装; oracle中的包分为两部分：包头，包体

CREATE [OR REPLACE] PACKAGE <数据包名> AS 
               --公共类型和对象声明
               --子程序说明
      END;
    定义包的主体
      CREATE [OR REPLACE] PACKAGE BODY <数据包名> AS 
               --公共类型和对象声明
               --子程序主体
      BEGIN
            -初始化语句
      END;

create or replace PACKAGE DEPTPACKAGE AS 
  -- 部门表操作创建的对象组织到包中;
  FUNCTION getTotalRecoreds RETURN NUMBER;
  -- 2,3,4
  PROCEDURE PROC_INSERT;
END ;

CREATE OR REPLACE
PACKAGE BODY DEPTPACKAGE AS

  FUNCTION getTotalRecoreds RETURN NUMBER AS
  num number := 0;
  BEGIN
    select count(*) into num from dept;
    RETURN num;
  END ;

  PROCEDURE PROC_INSERT AS
  BEGIN
     INSERT INTO DEPT VALUES (11,'aa','bb');
  END;

END DEPTPACKAGE;

21、触发器

触发器：触发器是Oracle数据库中自动执行的存贮过程;当条件满足，自动执行；Oracle触发器分为DML触发器，系统触发器；对程序员来说，主要关注的业务是DML触发器；

要阻止添加|删除|修改操作，只需要触发器抛出一个错误即可;

RAISE_APPLICATION_ERROR(错误号(-20000到-20999),消息[,{true|false}]);

CREATE [OR REPLACE] TRIGGER <触发器名>
BEFORE|AFTER
INSERT|DELETE|UPDATE [OF <列名>] ON <表名>
[FOR EACH ROW]
BEGIN
	
END;


例1：对dept表执行删除操作(前)，自动给出提示
CREATE OR REPLACE TRIGGER tri1
BEFORE DELETE
ON dept
BEGIN
	DBMS_OUTPUT.PUT_LINE('正在删除数.....');
END;
例2：写一个更新提示的触发器(要求每更新一条，都打印出提示信息)
CREATE OR REPLACE TRIGGER tri2
AFTER UPDATE
ON dept
FOR EACH ROW
BEGIN
	DBMS_OUTPUT.PUT_LINE('一条数据完成更新.....');
END;
FOR EACH ROW :添加此设置，表中的每一行发生改变，都有触发 触发器的自动运行;

例3：数据在休息日不允许修改|删除|添加;

create or replace TRIGGER tri3
BEFORE DELETE OR UPDATE OR INSERT
ON dept
BEGIN
  
	IF DELETING THEN
  BEGIN
      IF TO_CHAR(SYSDATE,'day') = '星期六' OR TO_CHAR(SYSDATE,'day') = '星期日' THEN
      RAISE_APPLICATION_ERROR(-20001,'星期六、星期天不允许对数据进行删除操作....',true);
      END IF;
  END;
	END IF;
  
  IF UPDATING THEN
  BEGIN
      IF TO_CHAR(SYSDATE,'day') = '星期六' OR TO_CHAR(SYSDATE,'day') = '星期日' THEN
      RAISE_APPLICATION_ERROR(-20001,'星期六、星期天不允许对数据进行修改操作....',true);
      END IF;
  END;
	END IF;
  
  IF INSERTING THEN
  BEGIN
      IF TO_CHAR(SYSDATE,'day') = '星期六' OR TO_CHAR(SYSDATE,'day') = '星期日' THEN
      RAISE_APPLICATION_ERROR(-20001,'星期六、星期天不允许对数据进行添加操作....',true);
      END IF;
  END;
	END IF;
  
END;
例4：删除数据时自动备份
create or replace TRIGGER tri4
BEFORE 
DELETE
ON dept
FOR EACH ROW
BEGIN
  INSERT INTO deptbak VALUES(:old.deptno,:old.dname,:old.loc);
END;


例5：测试 :old 与 :new 
create or replace TRIGGER tri5
BEFORE 
UPDATE
ON dept
FOR EACH ROW
BEGIN
  -- 更新前的数据| 更新后的数据;
  DBMS_OUTPUT.PUT_LINE(:old.deptno);
  DBMS_OUTPUT.PUT_LINE(:old.dname);
  DBMS_OUTPUT.PUT_LINE(:old.loc);
  DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
  DBMS_OUTPUT.PUT_LINE(:new.deptno);
  DBMS_OUTPUT.PUT_LINE(:new.dname);
  DBMS_OUTPUT.PUT_LINE(:new.loc);
END;

：old :原数据对象 ，：new 新数据对象

22、数据备份与恢复

1、导入（import）|导出(export)

表方式
备份：Exp 账号/密码@数据库 file =备份文件 log= 日志文件 tables=要备份的表;
exp scott/tiger@orcl file=f:/dbbak/databk.dmp log=f:/dbbak/databklog.log tables=emp,dept

恢复： imp 账号/密码@数据库 file =备份文件 log= 日志文件 tables=表名 fromuser="源账号" touser=目标账号 commit=y(事务提交) ignore=y(重复数据忽略)

imp scott/tiger@orcl file=f:/dbbak/databk.dmp log=f:/dbbak/databklog.log tables=emp fromuser=scott touser=scott commit=y ignore=y

用户方式 ： 
exp scott/tiger@orcl  file=f:/dbbak/databk2.dmp log=f:/dbbak/databklog2.log
imp scott/tiger@orcl   file=f:/dbbak/databk2.dmp log=f:/dbbak/databklog2.log tables=emp fromuser=scott touser=scott commit=y ignore=y


全库方式
exp system/orcl@orcl file=f:/dbbak/databk3.dmp log=f:/dbbak/databklog3.log
imp sys/orcl@orcl  file=f:/dbbak/databk3.dmp log=f:/dbbak/databklog3.log full=y ignore=y destroy=y

2、冷备份

如果要进行冷备份，则需要备份出数据库中的几个核心内容：

● 控制文件：指的是控制整个oracle数据库的实例服务的核心文件，直接通过”v$contronlfile”找到；

● 重做日志文件：可以进行数据库的灾难恢复，直接通过”v$logfile”找到；

● 数据文件：表空间文件，通过”v
$datafile”和”v$tablespace”找到；

● 核心操作的配置文件（pfile），通过”show paramter pfile”找到；

从实际的Oracle的部署来讲，所有的文件为了达到IO的平衡操作，要分别保存在不同的硬盘上。