Oracle-处理数据

SQL的类型

• DML（Data Manipulation Language 数据操作语言）： select insert、update、 delete。
• DDL(Data Definition Language 数据定义语言)：create、table、alter、table、truncate、table、drop、table，
  create/drop view、sequnece、index、synonym(同义词)。
• DCL(Data Control Language 数据控制语言)：grant(授权) revoke(撤销权限)。

数据操作语言

1.插入数据(insert)

SQL> --首先插入一张新表的员工表emp2,防止影响原来emp表数据
SQL> create table emp2 as select * from emp;
SQL> --在emp2表中插入名为TOM员工
SQL> insert into emp2(empno,ename,sal,deptno) values(1001,'TOM',3000,10);

1.向表中插入空值

2.地址符&

--使用地址符&在emp2表中插入名为JACK员工
SQL> --PreparedStatement pst = "insert into emp(empno,ename,sal,deptno) values(?,?,?,?)";
SQL> insert into emp2(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值:  1002
输入 ename 的值:  'JACK'
输入 sal 的值:  4000
输入 deptno 的值:  10
原值    1: insert into emp2(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值    1: insert into emp2(empno,ename,sal,deptno) values(1002,'JACK',4000,10)

已创建 1 行。
SQL> --提交事务
SQL> commit;

--使用地址符&查询job列
SQL> select empno,ename,sal,&t from emp2;
输入 t 的值:  job
原值    1: select empno,ename,sal,&t from emp2
新值    1: select empno,ename,sal,job from emp2
     EMPNO ENAME             SAL JOB
---------- ---------- ---------- ---------
      7369 SMITH             800 CLERK
      7499 ALLEN            1600 SALESMAN
      7521 WARD             1250 SALESMAN
      7566 JONES            2975 MANAGER
      7654 MARTIN           1250 SALESMAN
      7698 BLAKE            2850 MANAGER
      7782 CLARK            2450 MANAGER
      7788 SCOTT            3000 ANALYST
      7839 KING             5000 PRESIDENT
      7844 TURNER           1500 SALESMAN
      7876 ADAMS            1100 CLERK
      7900 JAMES             950 CLERK
      7902 FORD             3000 ANALYST
      7934 MILLER           1300 CLERK
      1001 TOM              3000
      1002 JACK             4000
      已选择 16 行。

SQL> /
输入 t 的值:  deptno
原值    1: select empno,ename,sal,&t from emp2
新值    1: select empno,ename,sal,deptno from emp2
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH             800         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            3000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
      1001 TOM              3000         10
      1002 JACK             4000         10
	  已选择 16 行。

3.从其它表中拷贝数据

SQL> --从其它表中拷贝数据
SQL> --快速建表,这里有where条件就是快速建表,没有where也是快速建表,并且把查询的数据插入新表中。
SQL> create table emp3 as select * from emp where 1=2;
	 表已创建。

SQL>--查看emp3表结构和查看emp表结构，新表中的not null约束已经不存在了。
SQL> desc emp3;
 名称                          是否为空?      类型
 ----------------------------- ----------- ----------------
 EMPNO                                       NUMBER(4)
 ENAME                                       VARCHAR2(10)                                                                       
 JOB                                         VARCHAR2(9)                                                                       
 MGR                                         NUMBER(4)                                                                     
 HIREDATE                                    DATE                                                                      
 SAL                                         NUMBER(7,2)                                                                     
 COMM                                        NUMBER(7,2)                                                                       
 DEPTNO                                      NUMBER(2)                                                                       

SQL>--查看emp表结构
SQL> desc emp;
 名称                          是否为空?      类型
 ----------------------------- ----------- -----------------
 EMPNO                         NOT NULL      NUMBER(4)                                                                           
 ENAME                                       VARCHAR2(10)                                                                        
 JOB                                         VARCHAR2(9)                                                                       
 MGR                                         NUMBER(4)                                                                      
 HIREDATE                                    DATE                                                                     
 SAL                                         NUMBER(7,2)                                                                   
 COMM                                        NUMBER(7,2)                                                                
 DEPTNO                                      NUMBER(2) 
SQL> select * from emp3;
	 未选定行

SQL> --把emp表10部门员工插入到emp3表中，不适合大数据量，海量数据有其他处理方式。
SQL> insert into emp3 select * from emp where deptno = 10;
	 已创建 3 行。
SQL> select * from emp3;
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

2.更新数据(update)

1.在UPDATE语句中使用子查询

3.删除数据(delete)

1.delete和truncate的区别

1、delete逐条删除；truncate先摧毁表 再重建2。
2、（*）delete是DML  truncate是DDL。
         (可以回滚)     （不可以回滚）
3、delete不会释放空间 truncate会。（举例：比如有一张表有10M数据使用delete删除后，该表还是10M，如果一些确定不要的表，使用truncate删除会释放空间。）
4、delete会产生碎片 truncate不会。
5、delete可以闪回(flashback其实就是一种恢复)  truncate不可以。（以前在Mysql删除数据后提交事务就不能撤销，但在Oracle是可以撤销的，不光是delete，Oracle绝大部分操作都是可逆的。）

1.delete的碎片图解

2.delete和truncate那个删除数据更快

• 在Oracle中delete比truncate更快。原因：（非常非常非常）undo数据（还原数据，闪回与这个也有关。）
• 在Mysql中则是truncate比delete更快。

SQL> --设置关闭插入数据完整返回反馈信息
SQL> set feedback off
SQL> --导入sql文件
SQL> @C:\Users\Administrator\Desktop\testdelete.sql
SQL> select count(*) from testdelete;
  COUNT(*)
----------
      5000
SQL> --开启执行sql时间
SQL> --set timing on;
SQL> delete from testdelete;
已用时间:  00: 00: 00.09
SQL> --关闭执行sql时间
SQL> set timing off

SQL> --删除表purge表示不经过回收站
SQL> drop table testdelete purge;

SQL> --在导入sql文件
SQL> @C:\Users\Administrator\Desktop\testdelete.sql
SQL> --设置关闭插入数据完整返回反馈信息
SQL> set feedback off
SQL> --开启执行sql时间
SQL> set timing on
SQL> truncate table testdelete;
已用时间:  00: 00: 00.51
SQL> --关闭执行sql时间
SQL> set timing off
SQL> --在Oracle中delete比truncate更快。原因：（非常非常非常）undo数据（还原数据）

数据库事务

1.事务保存点

SQL> --创建一张测试保存点的表
SQL> create table testsavepoint(
  2   tid number,
  3   tname varchar2(20));

SQL> --插入数据
已用时间:  00: 00: 00.21
SQL> insert into testsavepoint values(1,'Tom');
SQL> insert into testsavepoint values(1,'Mary');

SQL> --设置事务保存节点
SQL> savepoint a;
SQL> insert into testsavepoint values(1,'Jack');
SQL> select * from testsavepoint;
       TID TNAME
---------- --------------------
         1 Tom
         1 Mary
         1 Jack

SQL> --回滚到保存点
SQL> rollback to savepoint a;
SQL> select * from testsavepoint;
       TID TNAME
---------- --------------------
         1 Tom
         1 Mary

2.数据库的隔离级别

演示Orcale不在sql99中的事务隔离级别

SQL> --设置Oracle事务隔离级别为只读
SQL> set transaction read only;
set transaction read only
*
第 1 行出现错误:
ORA-01453: SET TRANSACTION 必须是事务处理的第一个语句

SQL> --先提交上一次事务
SQL> commit;
SQL> --设置Oracle事务隔离级别为只读
SQL> set transaction read only;

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