Oracle

DBS
数据库系统，指带有数据库并利用数据库技术进行数据管理的计算机系统。一般由4个部分组成：
1.数据库（DB）。存储在磁盘、光盘或其他外存介质上、按一定结构组织在一起的相关数据的集合。
2.数据库管理系统（DBMS）。一组能完成描述、管理、维护数据库的程序系统。目前常用的有Oracle、SQL Server、MySQL。
3.数据库管理员（DBA）。
4.用户和应用程序。

数据库三范式
第一范式：字段不可再拆分。
第二范式：表要有唯一性主键。
第三范式：表中字段不能包含其它表的非主键字段。

数据库管理系统必须提供的功能
1.定义数据库结构：DBMS提供DDL来定义数据库结构，用来搭建数据库框架，并被保存在数据字典中。
2.存取数据库内容：DBMS提供DML实现对数据库数据的基本存取操作，即插入、删除、修改、检索等。
3.数据库的运行管理：DBMS提供数据控制功能，即数据的安全性、完整性和并发控制，对数据库运行进行控制和管理，以确保数据正确有效。
4.数据库的建立和维护：包括数据库初始化的装入，数据库的转储、恢复、重组织，系统性能监视、分析等功能。
5.数据库的传输：DBMS提供处理数据的传输，实现用户程序与DBMS之间的通信，通常与操作系统协调完成。

SQL
结构化查询语言，用来与DBMS通信。尽管SQL语言已经被ISO组织定义了具有国际标准的SQL规范，但是各种数据库系统厂商在其数据库管理系统中都对SQL规范做了某些编改和扩充。所以，不同数据库管理系统之间的SQL不能完全通用。目前常用的SQL语言有微软的T-SQL和Oracle的PL/SQL。

SQL语言的分类
DDL：数据定义语言。用于定义构成数据库的数据结构。create、alter、drop、rename、truncate
DML：数据操纵语言。用于实现对数据的存取、修改操作。insert、delete、update
DCL：数据控制语言。用于修改数据库的操作权限。grant、revoke
TC：事物控制语句。用于将所做的修改永久性地保存。commit、rollback、savepoint
SELECT：查询语句。

表：表与现实世界中的对象具有对应关系，在设计数据表时，一般使用ER图来构造实体关系模型，ER图通常是对现实世界中业务进行的建模。这些ER图在变成数据库对象时，最终要转换成表。

表又被称为二维数据集合，表的结构由列进行定义，列包含类型和一些约束信息，表行是这些列的数据，表中的行又被称为记录，由一条记录来描述一个实例。

(1)约束：约束是保证数据完整性的机制，强制用户遵从某个业务逻辑。
1.主键约束：唯一性约束+非空约束。
2.外键约束：实现表之间的关联，保证数据完整性。
3.唯一性约束：指定列的值在表中唯一。
4.非空约束：验证字段的值不能为空。
5.检查约束：检查字段是否满足特定的要求。

(2)索引：索引是一种用来快速访问表中数据的机制。使用索引可以提高对数据的查询效率，减少磁盘的IO操作，提升整个数据库系统的性能。
索引的优点：
1.索引可以加快检索数据的速度。
2.唯一性索引可以保证表中数据的唯一性。
3.通过索引可以加快表与表之间的连接。
4.可以减少查询中分组和排序的时间。

缺点：
索引需要在表的基础上创建，需要占用额外的物理空间，而去对表进行修改时，比如增、删、改数据时，需要动态进行维护，降低了数据维护速度。

索引原理：
在Oracle数据表中，每一张表都有一个rowid伪列，这个rowid是用来唯一标志一条记录所在物理位置的一个ID号。

当创建索引时，会对索引表进行一次全表扫描，获取每条记录索引列的数据，并进行升序排序。同时会获取每条记录的rowid值，连同排序后的索引列一起存储到索引段中，其格式是(索引列值，rowid)，这种组合也称为索引条目。

当检索数据时，比如使用where子句按指定条件检索数据时，oracle将首先对索中的列进行快速搜索，由于索引列已经排序，因此可以使用各种快速的搜索算法，这样就可以避免全表进行扫描。在找到所要检索的数据后，通过rowid读取具体的记录值。

创建索引的方式
自动创建：在定义主键约束或唯一约束时，oracle自动在相应的约束列上建立唯一索引，oracle不推荐人为地创建唯一索引。
手动创建：用户可以在其它列上创建非唯一索引。

索引的类型
单列索引：索引基于单个列所创建。
复合索引：索引基于多个列所创建。
b树索引：oracle的默认索引，可以是单列、复合、唯一、非唯一索引。
位图索引：为索引列的每个取值创建一个位图。
函数索引：索引的取值不直接来自列，而是来自包含有列的函数或表达式。

(3)视图：视图是表的另外一种表示形式，它通过使用select语句定义一个视图所需显示数据的虚表，这个虚表只有对视图的定义，并不包含实际的数据。可以说，视图是在表的基础上用来展示数据的一种方式。

视图的优点：
1.视图限制数据的访问，因为视图能够选择性地显示表中的列。
2.视图可以用来构成简单的查询以取回复杂的查询结果。

(4)事务：事务(Transaction)是一个由多条SQL语句组成的工作逻辑单元，这些语句要么全部执行成功，要么全部不执行，只要有一条SQL语句执行失败，则已执行的SQL语句会回滚到执行之前的状态，从而保证数据的一致性。

Oracle中，当第一条SQL开始执行时，一个新的事务自动开始，除非显示地使用commit进行提交，或使用rollback进行回滚，或退出某个Oracle工具时，这些事务才结束，否则SQL语句的操作仅在会话级别进行，并没有保存到数据库中。

事务的四个特性：原子性，一致性，隔离性，持久性。所谓的ACID。

原子性：事务必须是原子工作单元。对其进行的数据修改，要么全部执行，要么全部不执行。
一致性：事务在完成时，必须使所有的数据都保持一致状态，即所有的数据都要发生改变，以保证数据的完整性。
隔离性：两个事务的执行互不干扰，独立进行。
持久性：一旦事务被提交后，数据库的变化就会被永久保留下来。

commit：
1.永久的保存DML语句进行的修改。
2.自动释放事务上的所有锁和事务占有的一切资源(如游标、内存)。

rollback：
1.误删、误改数据时，使用回滚恢复原始的数据。
2.触发了异常而不能完成事务时，使用回滚恢复到初始化状态。

(5)存储过程：存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。
函数(function)与存储过程(procedure)的区别：
1.函数实现的功能针对性比较强，而存储过程实现的功能要复杂一点。
2.函数返回的是变量，而存储过程返回的参数。
3.执行本质不同，函数可以在查询语句中使用，存储过程不行。

(6)游标：游标是一个指向查询语句返回的结果的指针，因此在游标定义时，将包含一个查询定义。当游标打开后，数据被接收到一块内存区域存储，直到游标关闭。

oracle在执行SQL语句时，总是需要创建一块内存区域(游标实际上指向这里)，这块内存区域称为上下文区域。在上下文区域中包含了处理语句的信息，这些信息包含当前语句已经处理了多少行、指向被分析语句的指针和查询语句的返回的信息。

上下文区域包含了3类信息：
1.查询返回的数据行。
2.查询所处理的数据的行号。
3.指向共享池中已分析的SQL语句。

游标定义时并不会获取游标数据，只有在游标被打开后，游标相关的查询语句被执行，然后将检索到的结果保存到内存中。

可以使用open、fetch和close语句来控制游标，open语句用来打开游标并使得游标指向结果集的第一行，fetch会检索当前行的信息并把游标指向下一行，close语句用来在游标移动最后一行后关闭游标。

游标分类
显示游标：使用cursor语句显示定义的游标，游标被定义后，需要打开并提取游标。
隐式游标：由Oracle为每一个不属于显示游标的DML语句都创建一个隐式的游标，隐式游标没有名称。

隐式游标由Oracle动态创建，因此不能显示地打开、关闭或提取一个隐式游标。Oracle会隐式地打开、处理，然后关闭隐式游标。

(7)触发器：触发器和存储过程相似，它主要是通过事件进行触发而被执行。比如在修改表、建立对象、登陆数据库、操纵表数据时执行。触发器总是隐式地被调用，不能接受任何参数。

使用触发器的限制：
1.触发器代码的大小不能超过32KB，可以分隔为多个存储过程。
2.触发器只能包含select、insert、delete、update。

PS：尽管触发器可以实现较多的功能，但是不要过度使用触发器，否则会导致系统变得难以维护。

(8)序列：序列是一种数据库对象，主要工作是为表产生唯一值，由Oracle内部程序产生并增加或减少。序列被创建后可以通过数据字典找到，因此序列可以被多个对象共享。
create sequence seq_test increment by 1 start with 100000000 nocycle nocache;

(9)同义词：同义词的目的是为了简化对目标对象的访问，同义词并不占用实际存储空间，只在数据字典中保存同义词的定义。Oracle中大部分数据库对象，如表、视图、序列、存储过程、包等，都可以为它们定义同义词。

同义词可分为以下两种：
1.公用同义词：能被所有用户访问。
2.私有同义词：只能由创建的用户的访问。

create public synonym scottemp for scott.emp;
drop public synonym scottemp;

(10)SQL内置函数：函数是具有一定功能，被事先封装好的功能块。在SQL语言中，函数分为2类
1.单行函数：仅对单个行值进行计算，并且对每行返回一个结果。单行函数包括字符、数字、日期、转换函数等。
2.多行函数：能够操纵成组的行，给每个组一个结果，比如sum，avg等。

1.字符型函数
lower - 将字母转换为小写。
upper - 将字幕转换为大写。
initcap - 将每个单词的首字母转换为大写，其它为小写。
concat - 连接两个指定字符串，与||操作符的作用相同。
trim - 去掉字符串两边的空格。
2.数字型函数
round(x,y) - 四舍五入x的值为y位小数点。
trunc(x,y) - 截断x的值到y位小数。
mod(x,y) - 返回x除以y的余数。
3.日期时间函数
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
months_between(date1,date2) - 计算date1和date2之间的月数。
add_months(dte,n) - 添加n个月到date。
next_day(date,'char') - 计算在date后之后的下一个周(‘char’)的指定天的日期。
last_day(date) - 计算当前月的最后一天的日期。
4.类型转换函数
to_char - 将日期或数字转换为varchar2类型的值。
to_date - 将varchar2转换为日期值。
to_number - 将varchar2转换为number值。
5.分组函数
avg - 返回平均值。
count - 返回行的数目。
sum - 返回列的总和。
min - 返回列的最小值。
max - 返回列的最大值。
6.其它函数
nvl(exp1,exp2) - 转换null为一个指定值。
nvl2(exp1,exp2,exp3) - 如果exp1不为null，返回exp2，否则返回exp3。
nullif(exp1,exp2) - 如果exp1=exp2，返回null，否则返回exp1。
coalesce(exp1,exp2 ... expn) - 返回第一个不为null的exp。
decode(col,search1,result1[,search2,result2,...][,default]) -- 如果col=search1，返回result1，否则返回default。

(11)SQL优化
1.避免使用*号。*号会去数据字典中查询所有的列信息，然后依次转换成所有列名。
2.选择最有效率的表名顺序。把数据量最少的表作为基础表，放在from语句末尾。
3.where子句的连接顺序。把能过滤掉最大数据的条件写在where子句的末尾。
4.使用decode。decode函数可以避免重复扫描相同记录或重复连接相同的表。
5.使用where替代having过滤。
6.使用exists替代in。
7.使用union all替代union。union会排除重复记录。
8.使用“>=”替代“>”。

海量数据的优化
1.建立分区。把表中的数据存放在多个表空间中。
2.拆分成多个表。
3.建立索引。
4.优化SQL。

truncate与delete：
truncate速度快，占用的事务少。因为它属于DDL，不能回滚。
delete属于DML，可以回滚。

rowid和rownum有什么区别：
rowid是唯一标识一条记录物理位置的一个ID。
rownum是oracle系统为查询返回的行顺序分配的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。

Oracle初始化口令
sys/change_on_install system/manager sysman/oem_temp

数据库对比：

-- 查询闪回
select * from t_csp as of timestamp to_timestamp('2013/4/5 1:35:00', 'yyyy-mm-dd hh24:mi:ss');
-- 表闪回
alter table t_csp enable row movement;
flashback table t_csp to timestamp to_timestamp('2013/4/5 20:00:00','yyyy-mm-dd HH24:mi:ss');
-- 恢复被drop的表
flashback table emp to before drop;

-- 查看当前数据库的名称
select * from global_name;
-- 查看所有表空间
select * from dba_tablespaces;
-- 查看表空间大小
select tablespace_name,sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name;
-- 查看当前用户所属的表空间
select username,default_tablespace from user_users;
-- 查看表空间的数据文件存放位置
select tablespace_name, bytes/1024/1024 file_size_mb, file_name from dba_data_files;
-- 创建表空间
create tablespace tablespace_csp datafile 'D:\oracle_database\oradata\csp\tablespace_csp.dbf' size 20m;
-- 删除表空间
drop tablespace tablespace_csp including contents and datafiles;
-- 查看所有用户
select * from all_users;
-- 查看所有用户状态
select username, account_status, expiry_date, default_tablespace from dba_users;
-- 查看所有角色
select * from dba_roles;
-- 查看当前用户所有表占用的空间
select sum(bytes)/(1024*1024) "size(M)" from user_segments;

-- 创建用户
create user user_csp identified by password_csp default tablespace tablespace_csp;
-- 删除用户
drop user user_csp cascade;
-- 解锁用户
alter user scott account unlock;
-- 修改用户密码
alter user scott identified by tiger;
-- 赋予用户角色
grant resource,connect,dba to user_csp;
-- 回收用户角色
revoke dba from user_csp;
-- 查看当前用户拥有的角色
select * from user_role_privs;
-- 查看当前用户的所有表
select * from user_tables;
-- 查看当前用户的所有索引
select index_name,index_type,table_name from user_indexes;
-- 查看当前用户的所有视图
select * from user_views;
-- 查看当前用户的所有函数
select object_name,status from user_objects where object_type='FUNCTION';
-- 查看当前用户的所有存储过程
select object_name,status from user_objects where object_type='PROCEDURE';
-- 查看表的外键约束信息
select * from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name = 'DEPT');

-- 创建表
create table t_csp
(
c_id varchar2(32),
p_id number(2),
c_name varchar2(64),
c_date date,
constraint pk_t_csp_id primary key(c_id)
);
-- 删除表
drop table t_csp;
-- 重命名表
rename t_csp to t_csp2;
-- 创建表副本
create table csp as select * from emp;
-- 增加多条记录
insert into csp select * from emp;
-- 删除主键
alter table t_csp drop constraint pk_t_csp_id;
-- 添加主键
alter table t_csp add constraint pk_t_csp_id primary key(c_id);
-- 查看表主外键
select * from user_cons_columns where table_name = upper('t_csp');
-- 增加外键
alter table t_csp add constraint fk_t_csp_p_id foreign key(p_id) references dept(deptno);
-- 删除外键
alter table t_csp drop constraint fk_t_csp_p_id;
-- 创建索引
create index t_csp_index_no on t_csp(p_id);
-- 删除索引
drop index t_csp_index_no;
-- 增加字段
alter table t_csp add add1 timestamp;
-- 修改字段类型
alter table t_csp modify add1 varchar2(64);
-- 重命名字段
alter table t_csp rename column add1 to add2;
-- 删除字段
alter table t_csp drop column add2;
-- 增加注释
comment on table t_csp is 't_csp注释';
comment on column t_csp.c_id is 't_csp_id';

select t.*,t.rowid from t_csp t;

insert into t_csp(c_id,p_id,c_name,c_date) values(1001,10,'name1001',sysdate);
insert into t_csp(c_id,p_id,c_name,c_date) values(1002,10,'name1002',sysdate);
insert into t_csp(c_id,p_id,c_name,c_date) values(1003,10,'name1003',sysdate);

-- 删除单条记录
delete from t_csp where c_id = '1003';
-- 删除所有记录
delete from t_csp;
-- 删除所有记录，释放占用空间
truncate table t_csp;
-- 修改记录
update t_csp set c_date = to_date('2013/3/4 22:22:22','yyyy/mm/dd hh24:mi:ss') where c_id = '1001';

--内连接，inner可省略
select e.* from emp e inner join dept t on e.deptno = t.deptno where t.deptno = 10;
select e.* from emp e, dept t where e.deptno = t.deptno and t.deptno = 10;

-- 左外连接:返回左表的所有行，如果左表的某行在右表中没有匹配，则结果集中右表相关行的字段为null。
select * from dept t left outer join emp e on t.deptno = e.deptno;
select * from emp e, dept t where e.deptno(+) = t.deptno;

-- 右外连接：返回右表的所有行，如果右表的某行在左表中没有匹配，则结果集中左表相关行的字段为null。
select * from emp e right outer join dept d on e.deptno = d.deptno;
select * from emp e, dept t where e.deptno = t.deptno(+);

-- 全连接
select * from emp e full outer join dept d on e.deptno = d.deptno;

-- 交叉连接（笛卡尔积：表1的记录数X表2的记录数）
select * from emp e cross join dept d;
select * from emp e, dept d;

-- 自然连接（合并两个表的数据，去掉重复列）
select * from emp natural join dept;

-- 联合运算
select deptno from emp where deptno = 10
union all
select deptno from dept where deptno = 20;

-- 相减运算
select deptno from dept
minus
select deptno from emp;

-- not exists(select去掉子select)
select e.* from emp e where e.deptno = 20 and
not exists
(select 1 from emp e1 where e.empno = e1.empno and e.empno = 7369) or e.deptno = 10;

-- 常用函数
nvl(exp1,exp2) -- 如果exp1为null，则返回exp2
nvl2(exp1,exp2,exp3) -- 如果exp1不为null，返回exp2，否则返回exp3
coalesce(exp1,exp2,..expn) -- 返回第一个非空表达式
select decode(deptno, 10,'a') deptno from dept;
select decode(deptno, 10,'a',
20,'b',
deptno) deptno from dept;

-- 取出表中第6行到第10行的记录。
select t2.* from (select rownum rn,t1.* from t_csp t1 where rownum <= 10) t2 where t2.rn >= 5;

-- 最高效的删除重复记录。
delete from t_csp t1 where t1.rowid > (select min(t2.rowid) from t_csp t2 where t2.c_id = t1.c_id);

-- 过滤重复记录。
select distinct job from emp;
select * from t_csp t1 where t1.rowid =(select min(t2.rowid) from t_csp t2 where t1.c_id = t2.c_id);

-- 存储过程
drop procedure newdept;
create or replace procedure newdept(
p_deptno in number,
p_dname in varchar2,
p_loc in varchar2
)
as
v_count number;
begin
select count(deptno) into v_count from dept where deptno=p_deptno;
if v_count > 0 then
-- execute immediate 'drop table t_csp';
update dept set dname = p_dname,loc = p_loc where deptno = p_deptno;
else
insert into dept(deptno,dname,loc) values(p_deptno,p_dname,p_loc);
end if;
commit;
end newdept;
select * from dept;
1.call newdept(50,'mydname','abcd');
2.begin
newdept(60,'leichun','abcd');
end;

-- 函数
create or replace function getsex(p_sex in number)
return varchar2
as
v_sex varchar2(2);
begin
if mod(p_sex,2)=0 then
v_sex := '男';
else
v_sex := '女';
end if;
return v_sex;
end getsex;
select getsex(0) from dual;

-- 过程内使用游标
create or replace procedure setdate
as
cursor test_cursor is select c_id from t_csp where c_date is null;
begin
for test_row in test_cursor loop
update t_csp set c_date = sysdate where c_id = test_row.c_id;
end loop;
commit;
end setdate;
call setdate();

-- 触发器
create or replace trigger test_trigger
after update on t_csp
for each row
begin
insert into t_csplog(c_id) values(:NEW.c_id);
end;

-- 普通用户登录
sqlplus username@oracle_client.csp password

-- 管理员登录
sqlplus sys@oracle_client.csp as sysdba csp

-- 设置显示宽度
set linesize 100

-- 设置每页行数
set pagesize 20

数据导入导出
1 将数据库orcl完全导出
exp system/manager@orcl file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@orcl file=d:\daochu.dmp owner=(system,sys)

imp asean/rongji@csp file=E:\daochu.dmp full=y ignore=y;

ps：若导出出错，可进入oracle_database\product\11.1.0\db_1\BIN目录，再导出。