Oracle学习

一、Oracle概述

（1）实例
一个Oracle数据库由一到多个实例组成（1：n即Oracle集群RAC）。实例是后台进程和内存的集合，必须启动实例才能访问数据库中的数据。startup open 实例名；

集群优点：
1.Load Balance负载均衡：将客户压力均分到每一台服务器上。
2.Fail Over失败迁移：事务执行到某一步失败了，会将操作转到其他服务器继续执行。提高系统的可靠性

Oracle启动时将分配一个系统全局区（SGA），我们访问Oracle其实就是访问Oracle的实例。一个实例只能打开并使用一个数据库。Oracle分为单进程实例和多进程实例，现在普遍使用多进程实例，即多用户，不同用户使用不同进程来执行Oracle的不同部分。
实例名（instance_name）
指的是用于响应某个数据库操作的数据库管理系统的名字
Oracle_SID
SID其实Oracle实例的一个标识。但两者是有区别的，instance_name是oracle数据库参数。而Oracle_SID是操作系统的环境变量。Oracle_SID用于与操作系统交互，也就是说，从操作系统的角度访问实例名，必须通过Oracle_SID。并且Oracle_SID必须与instance_name的值一致，否则，你将会收到一个错误，在unix平台，是“ORACLEnot available”,在windows平台，是“TNS:协议适配器错误”。
（2）数据库
数据库是一个数据的集合，该集合被视为一个逻辑单元。
Oracle数据库由操作系统文件组成，这些文件为数据库信息提供实际物理存储区
Oracle数据库包括逻辑结构和物理结构。
物理结构：是指存储数据库信息的一组操作系统文件，每个数据库有一个逻辑结构和一个物理结构。物理结构是指构成数据库的一组操作系统文件，主要由3种类型文件组成：数据文件，控制文件和重做日志文件。

数据文件(后缀名DBF)：数据文件是物理存储数据库的文件，它是逻辑概念表空间的组成部分。特点：每个数据文件只与一个数据库相联系，一个表空间可以包含一个或多个数据文件，一个数据文件只能属于一个表空间

重做日志文件（后缀LOG）：它记录了对数据的所有更改信息，并提供一种数据恢复机制

控制文件（后缀CTL）：一个数据库至少有两份控制文件，oracle11g默认包括3个控制文件，各个控制文件内容相同

逻辑结构：是指数据库数据的逻辑存储结构，包括：表空间，数据段，表，视图等。

表空间：表空间是数据库中最大的逻辑单位。它隶属于Oracle数据库实例下面，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。

二、Oracle服务（进程）

Oracle有5个后台进程是必须启动的，否则数据库实例无法启动成功，分别是：进程监控（PMON）进程、系统监控（SMON）进程、数据写入（DBWR）进程、日志写入（LGWR）进程、检查点（CKPT）进程。

三、Oracle用户

当创建一个新数据库时，Oracle将自行创建一些默认的数据库用户，如SYS,SYSTEM和SCOTT等用户。

1、默认数据库用户

（1）SYS
SYS用户是Oracle中的一个超级用户，数据库中所有数据字典和视图都存在SYS模式中，数据字典存储了用来管理数据库对象的所有信息，是oracle数据库中非常重要的系统信息。SYS用户主要用来维护系统信息和管理实例。SYS用户只能以SYSOPER或SYSDBA角色登录系统。
（2）SYSTEM
SYSTEM用户是oracle中默认的数据库管理员，它拥有DBA权限。该用户模式中存储了oracle管理工具使用的内部表和视图。通常通过SYSTEM用户管理数据库的用户、权限和存储等。不建议在SYSTEM模式中创建用户表。SYSTEM不能以SYSOPER和SYSDBA角色登录系统，只能以默认方式登录。
（3）SCOTT
SCOTT用户是oracle数据库的一个示范用户，一般在数据库安装时创建。SCOTT用户模式包含4个示例表，其中一个是EMP表，使用USERS表空间存储模式对象。
（4）Sysdba和sysoper两个系统权限区别
sysdba和sysoper属于system privilege，也称为administrative privilege，拥有例如数据库开启关闭之类一些系统管理级别的权限。
sysdba拥有最高系统权限，登录后是sys
sysoper主要用来启动、关闭数据库，sysoper登录后用户是public

Oracle中的create user命令用于创建用户，每个用户都有一个默认表空间和一个临时表空间。如果没有指定，oracle就将USERS设为默认表空间，将TEMP设为临时表空间。

2、创建自定义用户

步骤：
1) 选择用户名和密码
2) 识别用户需用于存储对象的表空间
3) 决定每个表空间的限额
4) 分配缺省表空间和临时表空间
5) 创建用户
6) 向用户授予权限和角色
语法：

CREATEUSER user       IDENTIFIED BY password       [DEFAULT TABLESPACE tablespace]       [TEMPORARY  TABLESPACE tablespace]       [QUOTA {integer [K|M] | UNLIMITED}ONtablespace       [QUOTA {integer [K|M] | UNLIMITED}ONtablespace ] ...]       [PASSWORD EXPIRE ]

3、修改用户密码

alter user 用户 identified by 修改后的密码;

4、删除用户

drop user 用户 cascade;

当用户拥有模式对象时无法删除，必须使用cascade选项来全部删掉。

5、查询Oracle的所有用户

select username,status from dba_users;

6、解锁用户

alter user 用户 account unlock;

7、格式化输出

(1)查看/设置行宽

show linesizset linesize 120

(2)设置列宽

col 列名 for a8(设置字符串，表示是8个字符的字符串；for：format；col:column)cou 列名 for 9999(代表四位数字)

host cls 清屏（Linux下：host clear）
/ 执行上一条sql语句

SQL优化原则：
1、尽量使用列名的方式取代*
2、where语句解析的顺序是从右往左！！！（and、or）
——sql语句有个“执行计划”。

Oracle中的伪表：dual表
Oracle中的伪列：

SQL中的null：
1、包含null的表达式都为null
2、null永远不等于null
3、如果集合中含有null，不能使用not in ，但可以使用in
4、null的排序（要想让null值在最后，在语句最后加 nulls last）
滤空函数：nvl(a,b)——如果a的值不为空，则值为a；如果为空，则值为b。

---

部分图片引用自http://zpf666.blog.51cto.com/11248677/1927384

四、Oracle函数

1、单行函数

（1）字符函数

（2）数值函数

（3）日期函数
Oracle中的日期型数据实际含有两个值：日期和时间。
默认的日期格式是：DD-MON-RR
可以使用to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)函数来转换日期格式。

next_day()应用：每个星期一自动备份表中的数据。
（4）转换函数
to_char()
to_number()
to_date()
（5）通用函数
NVL(expr1,expr2）
NVL2(a,b,c)当a=null时，返回c，否则返回b
NULLIF(a,b)当a和b相等时返回null，否则返回a
COALESCE（a,b,…..,n）从左向右找到第一个不为null的值
（6）条件表达式
在sql语句中使用IF-THEN-ELSE
实现方式：
CASE表达式：SQL99语法，通用。

DECODE函数：Oracle特有，比较简洁。

2、多行函数