Oracle的数据处理

SQL的类型

SQL的类型:
1.DML(data manipulation language 数据操作语言):insert update delete select 
2.DDL(data definition language 数据定义语言):create table,alter table,drop table,truncate table,create/drop view,sequence(序列),index,synonym(同义词)
3.DCL(data control language 数据控制语言):grant(授权) revoke(插销权限).

insert 语句语法
insert into emp(empno,ename,sal,deptno) values (1001,'Tom',3000,10);

--JDBC:PreparedStatement pst="insert into emp(empno,ename,sal,deptno) values (?,?,?,?)";
--地址符 &(依次输入,在sqlpus窗口中使用)
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);

--批处理
create table emp10 as select * from emp where 1=2; -- 创建测试表

-- 一次性将emp中,所有10号部门的员工插入到emp10中.
insert into emp10 select * from emp where deptno=10;

-- 更新114号员工的工作和工资使其与205号员工相同
update employees set job_id =(select job_id from employees where employee_id=205),salary=(select salary from employees where employee_id=205) where employee_id=114;

-- 删除表中数据
delete和truncate的区别
1.delete逐条删除;truncate先摧毁表,在重建.
2.(根本)delete是DML(可以回滚),truncate是DDL(不可以回滚).
3.delete不会释放空间 truncate会.
4.delete可以闪回(flashback)插销已经提交的结果,truncate不可以.
5.delete产生碎片,truncate不会.

--undo数据(还原数据),因此delete比truncate快.

去掉碎片
1.alter table<表名> move;
2.导入导出(exp imp expdp impdp);

海量拷贝数据
1.数据泵(datapump)-->plsql
2.SQL*Loader
3.(数据仓库)外部表
4.可传输的表空间.

数据库事务

数据库事务:1.本地事务 2.全局事务.

事务的标志:
1.起始标志:事务中的第一条DML语句.
2.结束标志:
提交:显式:commit,隐式:正常退出(exit),DDL,DCL.
回滚:显式:rollback,隐式:非正常退出,掉电,宕机.

设置回滚点:saveponit a;
返回: rollback to savepoint a;

数据库提供的四种事务隔离级别

隔离级别	描述
read uncommited(读未提交数据)	允许事务读取未被其他事务提交的变更,脏读,不可重复读和幻读问题都会出现.
read commited(读已提交数据)	只允许事务读取已经被其他事务提交的变更,可以避免脏读,但不可重复读和幻读问题仍然可能出现.
repeatable read(可重复读)	确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新,可以避免脏读和不可重复读,但幻读的问题仍然存在.
serializable(串行化)	去报事务可以从一表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作,所有并发问题都可以避免,但性能十分低下.

Read uncommitted 事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

Read committed 事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。


Repeatable read 事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？
事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

Serializable 序列化是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

值得一提的是：大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

oracle支持2种事务隔离级别:read commited/serializable.oracle默认是隔离级别是read commited;
oracle独有的:read only,set transaction read only;

mysql支持4中事务隔离级别:mysql默认的事务隔离级别为:repeatable read;

DDL语句创建和管理表

--行地址 rowid
select rowid,empno,ename,sal from emp;

-- 创建一张表
create table empinfo as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno;
Table created

--修改表:追加新列,修改咧,删除列,重命名列,重命名表

alter table test3 modify tname varchar2(40);

alter table test3 drop column photo;

alter table test3 rename column tname to username;

rename test3 to test5;


-- oracle的回收站
show recyclebin; --查看回收站.
purge recyclebin; --清空回收站.

注意:管理员没有回收站

-- 约束是表一级的限制
如果存在依赖关系,约束可以防止错误的删除数据
约束的类型:1.not null 2.unique 3.primary key 4.foreign key 5.check

foreign key:在子表中定义了表级约束.
references:制定表和父表中列.
on delete cascade:当删除父表时,级联删除子表记录.
on delete set null:将子表中的相关依赖记录的外键值置为NULL.

例子:
create table student
(
sid number constraint student_pl primary key,
sname varchar2(20) constraint student_name_notnull not null,
gender varchar2(2) constraint student_gender check(gender in('男','女')),
email varchar2(40) constraint student_email_unique unique
                   constraint_student_notnull not null,
deptno number constraint student_fk references dept(deptno) on delete set null
);

其他数据库对象

-- 使用sys用户授权;
grant create view to scott;
grant create synonym to scott;

-- 某张表的查询权限
grant select on hr.employees to scott;

1.视图(一般只用于select,简化查询):从表中抽出的逻辑上相关的数据集合.
视图:是虚表,建立在已有表的基础上,视图赖以简历的这些表成为基表.
向视图提供数据内容的语句为select语句,可以将视图理解为存储起来的select语句.
视图向用户提供基表数据的另一种表吸纳形式.

create or replace view empinfoview 
as 
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno
with read only; --只读

create or replace view v_t3 
as
select id, name,salary from t3 where salary>=7000 
with check option;--保证让你只能在视图的条件之内对视图进行DML
插入一个要salary小于7000的行,将会报出错误.
插入一个大于7000的行，可以成功插入;

-- 物化视图(缓存数据);

2.序列:提供有规律的数值.
可供多个用户用来产生唯一数值的数据库对象.
1)自动提供唯一的数值
2)共享对象
3)主要用于提供主键值.
4)将序列值装入内存可以提供访问效率.
序列---->数组(默认1,步长1)---->内存

create sequence testseq
    increment by 10 --步长,默认为1
    start with 120  --起始值,默认为1
    maxvalue 9999   --最大值,默认为nomaxvalue,同理最小值mixvalue/nominvalue
    nocache         --不缓存,若缓存写为cache n
    nocycle;        --不循环,若循环为cycle

select myseq.currval from dual; --如果从未使用,那么将会报错,初始化指针指向初始值的前一位.
select myseq.nextval from dual;

drop sequence testseq; -- 删除序列.

3.索引:提高查询的效率.
--单列,在deptno上建立索引
create index myindex on emp(deptno);
--多列:
create index myindex on emp(deptno,job);

索引的类型:
1)B树索引(默认)--平衡树 insert/update/delete(OLTP).
2)位图索引--矩阵 select(OLAP 数据仓库).
create bitmap index *** on ***;

--删除索引
drop index indexname;
注意:只有索引的拥有者或拥有drop any index权限的用户才可以删除索引.


4.同义词:给对象起别名(安全简化表名).
--为emp表器别名
create synonym myemp for emp;
--创建共有同义词
create public synonym myemp for emp;