大数据-玩转数据-Oracle系统知识小结

一、说明

Oracle数据库是甲骨文的核心产品，Oracle 的操作都遵循sql标准，所以各个版本在基本操作方面都变化不大。Oracle划分在大型关系数据库范畴，比较Mysql或Nosql数据库来说，还是缺少些扩容的灵活性，但传统金融、通信、电力、航空、保险、大型企业用得比较多。

二、背景知识、数据建模和Linux

1.关系数据库

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。主流的关系数据库有oracle、db2、sqlserver、sybase、mysql等。

关系模型满足的确定约束条件称为范式，关系型数据库的三范式：
根据满足约束条件的级别不同，范式由低到高分为：1NF（第一范式）、2NF（第二范式）、3NF（第三范式）、BNF（BC范式）、4NF（第四范式），不同级别的范式性质不同。

第一范式（1NF）

1NF是最低的规范化要求。如果关系R中所有属性的值域都是简单域，其元素（即属性）不可再分，是属性项而不是属性组，那么关系模型R是满足第一范式的。通俗点也就是说一张关系表中，所有的字段的值都必须是不可再拆分的。例如：表1所示结构就不满足1NF的定义。

第二范式（2NF）

如果一个关系 R 属于 1NF，且所有的非主属性都完全依赖于主属性，则称之为第二范式（也就是除了主键以外的其他字段，都必须完全依赖于主键）。例如表3所示结构就不满足第二范式。

第三范式（3NF）
如果一个关系 R 属于 2NF，且每个非主属性不传递依赖于主属性，这种关系是 3NF。为了理解这种关系，将以表6中的字段作为讲解。

2.数据库设计

关系数据建模设计工具常用有Erwin，PowerDesigner.完成模型设计后将其转化为各种类型的对象和关系。

个人总结了数据库设计对象命名规则：
数据库对象命名规则

2.1 约定

a.数据库对象如表、字段、索引、序列、存储过程等的命名约定；
b.命名使用富有意义的英文词汇，中间以下划线分割；
c.避免使用Oracle的保留字如LEVEL、关键字如TYPE（见Oracle保留字和关键字）；
d.各表之间相关列名尽量同名；命名只能使用英文字母，数字和下划线；
e.索引和数据使用单独表空间存放

2.2 表名

报表: RPT_TABLEANME
临时表:TMP_TABLEANME（一次性使用的临时表用完及时清理；非程序使用临时表，表名须包含使用人标识，以便清理；如WANGXF/ZHOUSY/CAOY/…）
系统类型:SYS_TABLEANME
子系统表采用:xxx_TABLEANME（xxx指子系统名称）
共用类的表:PUB_TABLENAME
各业务类型:T_TABLENAME（TABLENAME指业务类型）；
程序及系统表名须完善表注释；
例如：

create table TMP_XXXX_01
(
LOAN_NO VARCHAR2(32) NOT NULL, 
CUST_NO VARCHAR2(64),
CUST_NAME VARCHAR2(64)
) TABLESPACE PCL;
comment on table TMP_XXXX_01 is '表业务含义';

2.3 存储过程

存储过程采用PROC_TABLENAME
存储过程代码书写全部采用范例PROC_DEMO

2.4 索引

索引采用IDX_TABLENAME_SEQ (SEQ指序号)，并指定对应的表空间
例如：

CREATE INDEX IDX_TMP_XXXX_01 ON TMP_XXXX_01(CUST_NO) TABLESPACE PCL_IDX;

2.5 主外键

主键采用PK_TABLENAME
外键采用FK_TABLENAME
例如：

ALTER TABLE TMP_XXXX_01 ADD CONSTRAINT PK_TMP_XXXX_01 PRIMARY KEY (LOAN_NO) USING INDEX TABLESPACE PCL_IDX;

2.6 函数

1、函数采用F_XXXX (XXXX 业务含义)

例如：

CREATE OR REPLACE FUNCTION F_GET_AGE

–该函数获取年龄

2.7 表空间命名

表空间命名:TBS_SCHEMANAME_DATA TBS_SCHEMANAME_IDX
数据文件: tbs_schemaname_dataxx.dbf
tbs_schemaname_idxxx.dbf其中xx代表序号

2.8 字段命名

1、业务含义:对应的英文缩写，切记使用关键字，尽量使用varchar2代替char类型，时间类型不要用字符串，使用NUMBER数据类型时给定长度
2、系统及程序使用表须完善字段注释

Oracle关键字:

ACCESS DECIMAL INITIAL ON START ADD NOT INSERT ONLINE SUCCESSFUL ALL DEFAULT INTEGER OPTION SYNONYM ALTER DELETE INTERSECT OR SYSDATE AND DESC INTO ORDER TABLE ANY DISTINCT IS PCTFREE THEN AS DROP LEVEL PRIOR TO ASC ELSE LIKE PRIVILEGES TRIGGER AUDIT EXCLUSIVE LOCK PUBLIC UID
BETWEEN EXISTS LONG RAW UNION BY FILE MAXEXTENTS RENAME UNIQUE FROM FLOAT MINUS RESOURCE UPDATE CHAR FOR MLSLABEL REVOKE USER CHECK SHARE MODE ROW VALIDATE CLUSTER GRANT MODIFY ROWID VALUES COLUMN GROUP NOAUDIT ROWNUM VARCHAR COMMENT HAVING NOCOMPRESS ROWS VARCHAR2 COMPRESS IDENTIFIED NOWAIT SELECT VIEW CONNECT IMMEDIATE NULL SESSION WHENEVER CREATE IN NUMBER SET WHERE CURRENT INCREMENT OF SIZE WITH DATE INDEX OFFLINE SMALLINT CHAR VARHCAR VARCHAR2 NUMBER DATE LONG CLOB BLOB BFILE  INTEGER DECIMAL  SUM COUNT GROUPING AVERAGE TYPE

3.Linux

见Linux专题文章。

三、体系结构、模式和事务管理

1. 几个重要的Oracle术语

要学习Oracle的体系结构，先要搞明白几个重要的术语：Oracle服务器、Oracle实例、Oracle数据库。

Oracle服务器：即Oracle server，由Oracle实例和Oracle数据库组成。

Oracle实例：即Oracle instance，是在Oracle启动的第一个阶段根据参数文件，生成的一系列的后台进程和一块共享内存SGA共同组成。

Oracle数据库：即Oracle database，是由Oracle所有的物理文件所组成。其中最关键的有：控制文件、数据文件、redo log文件等。

Oracle实例与Oracle数据库进行交互，Oracle实例来对数据库进行各种操作，从而对外提供数据库的存储和检索服务。

2. Oracle总体结构

Oracle server由Oracle instance和Oracle database组成。而Oracle instance又由后台进程和共享内存组成，所以Oracle的结构又包含了内存结构和进程结构；而Oracle database有物理文件组成，所以Oracle结构也包含了存储结构。

下面分别对Oracle内存结构、Oracle进程结构、Oracle存储结构进行概述，让我们对Oracle有一个对初步的概念。

3. Oracle内存结构

总体而言Oracle的内存由两大部分组成：PGA和SGA，其结构如下图所示：

4. Oracle进程结构

Oracle的进程主要有后台进程和Server process(其实按照Linux的严格意义来说，Server process也是属于后台进程)。后台进程主要对Oracle数据库进程各种维护和操作，而Server process主要来处理用户的请求：

用户进程通过监听器来访问Oracle instacne，那么就会触发生成一个Server process进程，来对该用户进程的请求进程处理。后台进程一般有：LGWR, DBWn, ARCn, CKPT, SMON, PMON等等。
1)DBWn(database writer数据库写)：
主要作用是将被修改过的buffer cache按照一定的条件写入物理磁盘。
2)LGWR(log writer，日志写)：
主要作用是将log buffer中的redo log记录按照一定的条件写入联机的redo log文件。
3)CKPT(checkpoint,检查点进程)：
主要作用是将检查点位置(checkpoint position)写入控制文件和数据文件的头部。
4)SMON(system monitor,系统监控进程)：
主要作用是在数据库启动时，判断实例上次是否正常关闭，如果是非正常关闭，则进程实例恢复。另外，还会合并相连的可以空间。
5)PMON(process monitor,进程监控)：
监控Server process, 如果Server process非正常关闭，则PMON负责清理它占用的各种资源。

5. Oracle存储结构

存储结构即物理文件的组成结构，Oracle涉及的物理文件如下所示：

其中的控制文件、数据文件、重做日志文件是不可或缺的关键文件：
1)control file(控制文件)：
包含了数据库物理结构的信息，比如各种文件的存放位置，当前数据库的运行状态等。十分重要，丢失则数据库实例不能启动。
2)datafile(数据文件)：
存放数据的文件。
3)online redo log file(联机重做日志文件)：
存放redo log的文件。维护数据库的一致性，用于数据库恢复。

6. Oracle逻辑结构与物理结构关系

四、数据库对象管理

1. Oracle的启动与关闭

启动Oracle

1.#su - oracle 切换到 oracle 用户且切换到它的环境
2.$lsnrctl status 查看监听及数据库状态
3.$lsnrctl start 启动监听
4.$sqlplus / as sysdba 以 DBA 身份进入 sqlplus
5.SQL>startup 启动 db

停止Oracle

1.#su - oracle 切换到 oracle 用户且切换到它的环境
2.$lsnrctl stop 停止监听
3.$sqlplus / as sysdba 以 DBA 身份进入 sqlplus
4.SQL>SHUTDOWN IMMEDIATE 关闭 db

2. 手工创建数据库及用户

参考：
创建数据库文件

CREATE TABLESPACE MyDataBase LOGGING DATAFILE 'D:\Oracle\database\MyDataBase.dbf' SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 500M EXTENT MANAGEMENT LOCAL;

MyDataBase:数据库名称
D:\Oracle\database\MyDataBase.dbf：数据库文件目录
创建数据库临时文件

create temporary TABLESPACE MyDataBase_temp tempfile 'D:\Oracle\database\MyDataBase_temp.dbf' SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 500M EXTENT MANAGEMENT LOCAL;

MyDataBase_temp：数据库临时文件名称
D:\Oracle\database\MyDataBase_temp.dbf：数据库临时文件目录
创建用户与上述两个文件形成映射关系

CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE MyDataBase TEMPORARY TABLESPACE MyDataBase_temp;

username：用户名
password：密码
MyDataBase：映射的数据库名称
MyDataBase_temp：映射的数据库临时文件名称
添加用户权限

grant connect,resource,dba to username;
grant create session to username;

删除数据库

conn sys/dwh as sysdba;

drop tablespace MyDataBase including contents and datafiles;

drop tablespace MyDataBase_temp including contents and datafiles;

删除用户

drop user username cascade;

改变用户表空间

alter user 用户 quota unlimited on 表空间A;
alter user 用户 quota unlimited on 表空间B;

或者放开所有表空间

grant unlimited tablespace to 用户;

或者索性给所有权限

grant resource,connect,dba to 用户;

查询用户

select * from all_users;

授权

grant connect to XH_Bai_History_WS;

– --是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作

grant resource to XH_Bai_History_WS;

–是授予开发人员的，能在自己的方案中创建表、序列、视图等。

grant dba to XH_Bai_History_WS;

–是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限

revoke  connect,resource from java_user;

–取消权限revoke

grant create view to java_user

–创建视图权限，一般网上找都是说的这句，但是光有这句还是无法创建

grant create  view to XH_Bai_History_WS; 

–授予查询权限

grant select any table to B; 

–授予权限

grant select any dictionary to B; 

–以上3项地后就能正常创建视图了。

3. 表空间管理

select distinct tablespace_name from dba_free_space;

SELECT A.TABLESPACE_NAME,
       FILENUM,
       TOTAL "TOTAL (MB)",
       F.FREE "FREE (MB)",
       TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
       TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
       ROUND(MAXSIZES, 2) "MAX (MB)"
  FROM (SELECT TABLESPACE_NAME,
              COUNT(FILE_ID) FILENUM,
               SUM(BYTES / (1024 * 1024)) TOTAL,
               SUM(MAXBYTES) / 1024 / 1024 MAXSIZES
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F 
 WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME
 --查询表所涉及对象
 select * from dba_source t where upper(t.TEXT) LIKE '%TMP_T_FACEBANK_LOAN_ITEM%'
 --清理无用的表
 DROP TABLE TMP_T_FACEBANK_LOAN_ITEM PURGE
 --处理
10G:
--回收HWM
alter table &table shrink space

--重组该表中现有的行并回收HWM
alter table &table shrink space cascade

4. Oracle原用户

Sys:超级管理员（校长）
System:管理员 修改密码 解锁 授权（老师）（orcl）
system@orcl as sysdba
Alter user scott identified by 新密码
–the accout is locked
解锁：Alter user scott account unlock
Scott：普通用户 （学生）

5. 数据语言PL/SQL

1.数据定义语言（DDL）:create drop alter
创建，修改，删除数据库对象（表），操作的是表的结构，不是表的数据

2.数据操作语言（DML）:insert delete update ,操作是表中数据

注意：oracle事务需要手动提交
添加数据
Insert into 表名（列名，列名,…）values（值，值，…）
删除数据
Delete from 表名 【where】
更新数据
Update 表名 set 列名=值，列名=值，… 【where】

下面就数据的DML,DDL举例

1.创建表

Create table 表名(
列名 数据类型  【约束】,
列名 数据类型  【约束】,
....
列名 数据类型  【约束】
)

create table stu_b(
  stuId int primary key,
  stuName varchar2(50) not null,
  age int check(age>18)
)

insert into stu_b(stuId,stuname,age) values(1,'小明',19);

insert into stu_b values(2,'张三',20);
select * from stu_b;

create table text_b(
  txtId int primary key,
  txtName varchar2(20) not null,
  txtAge int check(txtAge>18)
)

insert into text_b values(1,'历史',20);

select * from text_b;

数据类型：
字符型：char varchar varchar2
Char:固定长度 char(10):abc 占10
Varchar2:可变（在存空字符时varchar存储的是空字符，varchar2存的是null）
数字型：number(m,n):数字的精度 n:小数点后几位 int float
时间：date
约束：非空（not null）检查（check）默认（default）唯一（unique）
主键（primary key）外键（foreign key）
注意：oracle没有主键自增，需要借助序列（sequence）例：6序列讲解
2.删除表
Drop table 表名 [purge]
Purge:表就会被彻底删除

drop table text_b purge;

3.找回删除的表
Flashback table 表名 to before drop;

flashback table text_b to before drop;

4.重命名
Rename 原来表名 to 新表名

rename stu_b to stu;

5.增加一列（修改表的结构）
Alter table 表名
Add 列名 数据类型 [约束]

alter table stu add phone varchar2(20);

6.删除一列
Alter table 表名
drop column 列名

alter table stu drop column age;

7.修改已有列的数据类型
Alter table 表名 modify 列名 修改类型

alter table stu modify phone varchar2(50);

8.列重命名
Alter table 表名
Rename column 原来列名 to 新名字

alter table stu rename column phone to phones;

9.查询select
Emp:雇员表 dept：部门表

1. 基本查询
   Select * from 表名
   说明：*代表所有的列，直接写列名

2. 模糊查询：like
   查询条件不精确，通过关键字进行查询
   %：0或n个字符
   _：1个字符

select * from emp where ename like '%A%' order by empno desc;
select * from emp where ename like '_L%';
select sum(sal) from emp group by deptno;
select * from emp;
select * from dept;

3. 分组查询：group by
   一般聚合函数（sum,avg,max,min,count），统计
   注意：1.聚合函数 2.分组列名
   Order by ：排序（升序，降序 desc）
4. 多表联合查询:两张以上表联合查询，主外键关系
   注意：避免笛卡尔积，加上主外键约束关系
   (1). Where

select empno,ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno;

(2). 内连接 inner join

select empno,ename,sal,dname,loc from emp inner join dept on emp.deptno=dept.deptno

(3). 外链接 ：left join right join full join
主表，附表：主表数据全部显示。，附表匹配主表进行显示
主表有的列附表没有，附表以空格的形式填充
主表没有附表有的列，附表的列就不在显示
5. 子查询
查询语句嵌套了查询语句
注意:子查询必须加上（）
相关子查询：子查询不可以脱离父查询而单独执行
先执行父查询，子查询利用父查询的列执行查询，父查询在利用子查询返回的结果作为查询条件
非相关子查询（独立子查询）：先执行子查询（内查询），将子查询的结果父查询（外查询）的条件,子查询都可以脱离父查询而单独执行
(1). 单行子查询:子查询返回的结果一个
运算符：>,<,>=,<=,=,<>
(2). 多行子查询:子查询返回的结果又多个值
运算符：in, all, any，not in

/**
 查询工资比Allen高的(非相关子查询)
*/
select sal from emp where ename='ALLEN';
select * from emp where sal>(select sal from emp where ename='ALLEN');

--和Allen同部门
select deptno from emp where ename='ALLEN';
select * from emp where deptno=(select deptno from emp where ename='ALLEN');

--比平均工资高的
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);

--每部门工资最低的员工信息
select min(sal) from emp group by deptno

--多行子查询 in all any notin 
select * from emp where sal in (select min(sal) from emp group by deptno);

--查询工资高于所有部门的平均工资
select avg(sal) from emp group by deptno
select * from emp where sal>all(select avg(sal) from emp group by deptno);

--查询工资高于任意部门的平均工资
select * from emp where sal>any(select avg(sal) from emp group by deptno);

--查询工资高于本部门平均工资的所有员工（相关子查询）
select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
--
select count(*),deptno from emp group by deptno

6. 分页查询 rownum(伪列)
   Rownum从1开始执行

select rownum r,deptno,dname,loc from dept
select * from (select rownum r,deptno,dname,loc from dept) where r>2 and r<5;

6.序列（掌握）
在oracle中sequence就是序号，每次取的时候它会自动增加。sequence与表没有关系
Create sequence 名称(seq_表名)
注意：需要先执行一次nextval,才能执行currval

--简单创建序列
create sequence seq_stu start with 2;
select seq_stu.nextval from dual;

--借助序列实现主键自增
insert into stu values(seq_stu.nextval,'张珊珊','126743453','上海');

操作数据：

delete from stu where stuId=2;
update stu set stuName='丽水'，phones='13478564' where stuId=3;

序列字段说明
create sequence SEQ_LOG_ID
minvalue 1 --增长最小值
maxvalue 9999999999 --增长最大值,也可以设置NOMAXvalue – 不设置最大值
start with 101 --从101开始计数
increment by 1 --自增步长为1
cache 50 --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为—NOCACHE防止跳号
cycle; --循环,当达到最大值时,不是从start with设置的值开始循环。而是从1开始循环
使用
insert into 表名(id,name)values(seqtest.Nextval,‘sequence 插入测试’);CurrVal：返回 sequence的当前值 NextVal：增加sequence的值，然后返回 增加后sequence值
注意：第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。
CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。
一次NEXTVAL会增加一次 SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。- 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组 到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。
7.创建视图

create view user_view
as 
select stuName 员工姓名,stuAge 员工年龄 from userStu;

select * from user_view

8.函数/过程/触发器