oracle--处理数据

处理数据

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 departments (department_id
                         department_name)
values      (30,'Purchasing');

显式方式：在values子句中指定空值
insert into departments
values      (100,'Finance',null,null);

插入指定的值：

SQL> insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);

已创建 1 行。

SQL> -- PreparedStatement pst = "insert into emp(empno,ename,sal,deptno) values(?,?,?,?)";
SQL> --地址符 &
&相当于？

例：

SQL> insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值:  1002
输入 ename 的值:  'Mary'
输入 sal 的值:  2000
输入 deptno 的值:  30
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值    1: insert into emp(empno,ename,sal,deptno) values(1002,'Mary',2000,30)

已创建 1 行。

SQL> /
输入 empno 的值:  1003
输入 ename 的值:  'Mike'
输入 sal 的值:  5000
输入 deptno 的值:  20
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值    1: insert into emp(empno,ename,sal,deptno) values(1003,'Mike',5000,20)

已创建 1 行。

&可以应用于多处：

代表一列：
SQL> select empno,ename,sal,&t
  2  from emp;
输入 t 的值:  job
原值    1: select empno,ename,sal,&t
新值    1: select empno,ename,sal,job

代表一个表：
SQL> select * from &t;
输入 t 的值:  dept
原值    1: select * from &t
新值    1: select * from dept

批处理：

SQL> create table emp10 as select * from emp where 1=2;

表已创建。

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

海量拷贝数据：

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

更新 update：

语法：

update table
set column=value[,column=value,...]
[where condition];

可以一次更新多条数据

使用where子句指定需要更新的数据：

update employees
set department id=70
where employee_id=113;

如果省略where子句，则表中的所有数据都将被更新。

update copy_emp
set department_id=110;

在update语句中使用子查询

更新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;

使更新基于另一个表中的数据:

update copy_emp
set    department_id=(select department_id
                      from employees
                      where employee_id=100)
where job_id        =(select job_id
                      from employees
                      where employee_id=200);

删除 delete：

delete [from] table 
[where condition];

使用where子句指定删除的记录：

delete from departments
where department_name='Finance';

如果省略where子句，则标准功能的全部数据将被删除。

delete from copy_emp;

在delete中使用子查询，使删除基于另一个表中的数据。

delete from employees
where department_id=
                    (select department_id
                     from departments
                     where department_name like'%public%');

delete和truncate

区别：

1.delete逐条删除；truncate先摧毁表，再重建
2.delete是DML（可以回滚），truncate是DDL（不可以回滚）(最根本的区别)
3.delete不会释放空间 truncate会
4.delete可以闪回  truncate不可以
  (flashback)通过闪回 可以撤销一个已经提交了的操作
5.delete会产生碎片；truncate不会

碎片：

效率：

比较删除5000条数据所需时间：delete更快

SQL> @d:\temp\testdelete.sql
SQL> select count(*) from testdelete;

  COUNT(*)                                                                      
----------                                                                      
      5000                                                                      
SQL> set timing on
SQL> delete from testdelete;
已用时间:  00: 00: 00.06
SQL> set timing off
SQL> drop table testdelete purge;
SQL> @d:\temp\testdelete.sql
SQL> select count(*) from testdelete;

  COUNT(*)                                                                      
----------                                                                      
      5000                                                                      
SQL> set timing on
SQL> truncate table testdelete;
已用时间:  00: 00: 00.15

undo数据（还原数据）

事务

事务的标志：

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

commit和rollback语句的优点：

1.确保数据完整新
2.数据改变被提交之前预览
3.将逻辑上相关的操作分组

控制事务：

回滚到保存点：

使用savepoint语句在当前事务中创建保存点。
使用rollback to savepoint语句回滚到创建的保存点。

SQL> create table testsavepoint
  2  (tid number, tname varchar2(20));
SQL> set feedback on
SQL> insert into testsavepoint values(1,'Tom');

已创建 1 行。

SQL> insert into testsavepoint values(2,'Mary');

已创建 1 行。

SQL> --定义保存点
SQL> savepoint a;

保存点已创建。

SQL> select * from testsavepoint;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

SQL> insert into testsavepoint values(3,'Maake');

已创建 1 行。

SQL> select * from testsavepoint;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 
         3 Maake                                                                

已选择 3 行。

SQL> rollback to savepoint a;

回退已完成。

SQL> select * from testsavepoint;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

oracle中事务的隔离级别：
1.read commited(默认)
2.serializable
3.read only(oracle独有的)

SQL> set transaction read only;

事务处理集。

SQL> select * from testsavepoint;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

SQL>  insert into testsavepoint values(3,'Maake');
 insert into testsavepoint values(3,'Maake')
             *
第 1 行出现错误: 
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作