Oracle的使用

表空间

一个Oracle数据库由一个或多个逻辑存储单元——表空间构成，表空间存储着数据库中所有的数据；
除了普通表空间外，Oracle还有几种特殊的表空间。系统表空间（SYSTEM表空间）、UNDO表空间（回滚表空间）、临时表空间(TEMP表空间)。
系统表空间中除了保存数据字典外还保存所有的存储过程、函数、包、触发器和对象。
UNDO表空间用于保存被DML语句影响的记录的原始状态，以便在事务失败的时候可以进行回滚。
USER表空间
TOOLS表空间

表空间(users)和数据文件

表空间由多个数据文件组成
数据文件只能属于一个表空间
表空间为逻辑概念，数据文件为物理概念

段、区和块

段存在于表空间中
段是区的集合
区是数据块的集合
数据块会被映射到磁盘块

使用DDL语句管理表—用户

用户赋权限
新创建的用户没有任何权限，登录后提示

用户权限

一、创建
　　sys;系统管理员，拥有最高权限
　　system;本地管理员，次高权限
　　scott;普通用户，密码默认为tiger,默认未解锁
　　二、登陆
　　sqlplus / as sysdba;登陆sys帐户
　　sqlplus sys as sysdba;同上
　　sqlplus scott/tiger;登陆普通用户scott
　　三、管理用户
　　create user zhangsan在管理员帐户下，创建用户zhangsan
　　alert user scott identified by tiger;修改密码

用户授权

默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限
grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限
grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)
oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权
grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限
grant drop on tablename to zhangsan;//授予删除表的权限
grant insert on tablename to zhangsan;//授予插入的权限
grant update on tablename to zhangsan;//授予修改表的权限
grant insert(id) on tablename to zhangsan;
grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update
grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限

创建表

表名和列名
必须以字母开头
必须在1-30个字符之间
必须只能包含A-Z，a-z,0-9,_ $和#
必须不能喝用户定义的 其他对象重名
必须不能是Oracle的保留字
Oracle默认存储是都存为大写
数据库名只能是1-8位，datalink可以是128位，和其他一些特殊字符

连接ORACLE的几种手段

配置了ORACLE的连接之后，可以使用各种客户端工具连接远程服务器。ORACLE提供了SQLPLUSE来管理和操作数据库，SQLPLUSy使用有命令行和图形两类途径。
1.CMD

sqlplus /nolog
conn scott@zdoracle 注：scott是用户名，zdoracle服务名
请输入口令：
已连接。

PL/SQL与SQL语法比较
PL/SQL：PL/SQL(process languange)是ORACLE的过程化语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构，PL/SQL可以执行SQL语句，SQL语句中也可以使用PL/SQL函数。
Transact-SQL:（又称T-SQL），是在Microsoft SQL Server 和Sybase SQL Server 使用，与Oracle 的PL/SQL 性质相近。
例如：1、字符串连接||,+
2、LENGTH和LEN
3、Rownum与Top

索引

1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
　　2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
　　3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
　　4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
　　5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
　　6）oracle创建主键时会自动在该列上创建索引
　　
CREATE [UNIQUE] | [BITMAP] INDEX index_name --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2 --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1] --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING] --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT]; --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

序列

在oracle中，是通过使用sequence来处理自动的增长列 |—可以为表中的列自动产生值 |—有用户创建数据库对象，并可以由多个用户共享 |—般用于主键的一列 （1）语法： create sequence seq_test(序列名) increment by 2 -->设置每次自增2 start with 10 -->设置初始值 minvalue 10 -->设置最小值 maxvalue 100 -->这是最大值 cycle/nocycle -->设置是否循环 cache 10/nocache -->设置基数或缓存值，只对循环队列有限制，如果是循环序列，缓存值必须小于序列的基数 —（2）序列的两个函数 nextval;序列的下一个值（序列第一次使用之前必须用该函数初始化） currval;序列的当前值

创建表

修改表

约束

在数据库开发中，约束是必不可少，使用约束可以更好的保证数据的完整性。在Oracle数据库中，约束的类型包括：
主键约束
非空约束
唯一约束
外键约束
检查性约束

使用DML处理数据

基本查询 SELECT语句

空值是无效的，未指定的，未知的或不可预知的值
空值不是空格或者0
包含空值的数学表达式的值为空值

列的别名：
重名名一个列
便于计算
紧跟列名，也可以在列名和别名之间加入关键字‘AS’，
别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写
AS可以省略
把列与列，列与字符连接在一起
用‘||’表示
可以用来‘合成’列

字符串可以是SELECT列表中的一个字符，数字，日期
日期和字符只能在单引号中出现
每当返回一行时，字符串被输出一次

默认情况下，查询会返回全部行，包括重复行

在SElECT子句中使用关键字‘DISTINCT’删除重复行

条件查询与排序

1.非空的空的限制
1.1示例：查询每月能得到奖金的雇员
1.2分析：只要字段中存在内容表示不为空，如果不存在内容就是null，
1.3语法：列名 IS NOT NULL
为空 列名 IS NULL

范例：查询工资大于1500并且有奖金领取的雇员
分析：多个查询条件同时满足之间’AND’

范例：查询工资大于1500或者有奖金雇员
分析：多个查询条件或满足，条件之间用‘OR’

范例：基本工资大于1500小于3000全部雇员
分析： sal>1500,sal<3000

范例：查询1981-1-1到1981-12-31号入职的雇员
分析：between and 不仅可以使用在数值之间，也可以用在日期区间

范例：查询雇员名字叫smith的雇员
分析：在oracle中的查询条件中查询条件的值是区分大小写的

范例：查询雇员编号是7369，7499，7521的雇员编号具体信息
其实此时指定了查询范围，那么sql可以使用IN关键字

模糊查询

在常用的站点中经常会有模糊查询，即：输入一个关键字，把符合的内融合全部的查询出来，在sql中使用LIKE语句完成。

在LIKE中主要使用以下两种通配符

“%”：可以匹配任意长度的内容

“_”：可以匹配一个长度的内容

Order by排序

在sql可以使用ORDER对查询结果进行排序
语法： SELECT * |列名 FROM 表明{WEHER 查询条件} ORDER BY 列名 1 ASC|DESC,列名2…ASC|DESC

当排序时有可能存在null时就会产生问题，我们可以用nulls first，null last来制定null值显示的位置

–查询雇员的工资从低到高
select * form emp order by sal nulls first;
select * form emp order by sal desc nulls last;

子查询

Oracle中的事务

视图

视图就是封装了一条复杂查询的语句
视图是一个虚表
最大优点就是简化复杂的查询

Oracle锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

DML锁（data locks，数据锁），用于保护数据的完整性；
DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；
内部锁和闩（internal locks and latches），保护数据库的内部结构，应用于SGA；在我们实际应用开发中涉及较多的是DML锁，其他两种的话DBA会更加关心点；

PL/SQL和存储过程

PL/SQL、存储过
PL/SQL是对SQL语句的再次扩展，在以往java开发过程中，有些复杂的业务逻辑可能并不能使用一条SQL语句就搞定，需要按照顺序执行多条SQL语句操作，然而这无疑增加了对数据库的压力。我们也会感觉到繁琐。
使用PL/SQL可以将多条sql语句操作这件事交由oracle管理.
PL/SQL中可以使用判断，循环，等等元素来执行特殊操作。
将这个语句存储起来便于我们直接调用，就是存储过程

优势：执行效率高，减少java和数据库连接数
劣势：不利于数据库移植，万一数据库要从oracle变成mysql或者sqlserver，系统业务改动很大。

存储过程结构

Declare
				声明变量区域
			Begin
				执行语句区域
			Exception
				异常处理区域
			End；

声明变量
1、用数据库数据类型定义

declare
		eno number(4);                             --类似 int eno
		enm varchar2(20);                         --类似String enm
	begin......

2、引用表中字段类型定义变量

declare
			--和emp表中empno字段类型一致
			eno emp.empno%type; 
			enm emp.ename%type;
		begin

…

变量赋值

declare
		eno number(4):=7782;                  -- 类似 int eno=7782;
		enm varchar2(20):='小黄';              --类似String enm;
	begin
		eno:=7783;				       --类似 eno=7783;
		enm:='小绿';
		dbms_output.put_line(enm);        --类似System.out.println(enm);
	end;

declare
		eno emp.empno%type; 
		enm emp.ename%type;
	begin
		select ename,empno into enm,eno from emp where empno=7782;		
		dbms_output.put_line(enm||' 和  '||eno);        
		--类似System.out.println(enm+" 和 "+eno);
	end;

取出一行数据

DECLARE
		erow emp%rowtype;					--定义行变量
	BEGIN
		select * into erow FROM emp where empno=7782;   --查出7782这行对象
		dbms_output.put_line(erow.ename);			    --输出对象中ename值	
	end;   

if 判断语句

declare
	deptno EMP.deptno%type;
begin
	select deptno into deptno from EMP where empno='7902';
	IF deptno=30 THEN                          --类似 if(deptno==30)
		update emp set sal=sal+1.1 WHERE empno='7902';
	ELSIF deptno=20 THEN 			--类似 else if(deptno==20)
		update emp set sal=sal+1.2 WHERE empno='7902';
	ELSE 						--类似else
		update emp set sal=sal+1.3 WHERE empno='7902';
	END IF;
end;		

switch case 判断语句

declare
	deptno EMP.deptno%type;
begin
	deptno:=10;
	CASE deptno
		 WHEN 10 THEN
			dbms_output.put_line('部门10');     
		WHEN 20 THEN
		 	dbms_output.put_line('部门20');     
		ELSE
			dbms_output.put_line('部门30');    
	end CASE;
end;		

for循环

begin
	FOR i in 1..10					--类似 for(int i =1;i<11;i++)
	loop							--类似{
		dbms_output.put_line(i||'我很晕');     --类似 System.out.println(i+"我很晕");
	end loop;						--类似}
end;	

while 循环语句

declare
	num number:=1;
begin
	WHILE num<=10						--类似while(num<=10)
	loop 								--类似{
		dbms_output.put_line('我更晕了');		
	    	num:=num+1;					--类似num=num+1;
	end loop;							--类似}
end;	

create or replace procedure getavgby (
        -- 入参、出参列表， 逗号分隔。
        dno in number,                          -- 输入参数 不能带长度信息
       chengji out number                       -- 输出参数
)
as
begin
     select avg(sal) into chengji from emp where deptno = dno;
end;  

Oracle游标

游标
之前我们可以获取到一个数据、一行数据。那么如何获取多行数据，就需要用到游标了。

DECLARE
	cursor ca is select * from emp where deptno=10;		--相当于 ca是arraylist
	em ca%rowtype;							--定义ca中的行对象名
BEGIN
	for em in ca loop						--相当于for(Emp em:ca)
	dbms_output.put_line(em.ename);			--相当于em.ename;
	end loop;		
end;   

Oracle触发器 TRIGGER

.触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。

详细：
因此触发器不需要人为的去调用，也不能调用。
触发器的触发条件其实在你定义的时候就已经设定好了。

语句级触发器
	语句级的触发器可以在某些语句执行前或执行后被触发。
	比如：你向数据库一次插入1000条数据，语句级触发器只执行一次
行级触发器
	在定义的了触发的表中的行数据改变时就会被触发一次。
	比如：你向数据库一次插入1000条数据，行级触发器会执行1000次