【oracle学习】4.处理数据

还是先介绍一下我们要用到的两张表以及数据：
员工信息表

create table EMP(
    EMPNO NUMBER,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER,
    HIREDATE DATE,
    SAL BINARY_DOUBLE,
    COMM BINARY_DOUBLE,
    DEPTNO NUMBER
);

其中job是职位，mgr是该员工的上司的id，sal是工资，comm是提成，deptno是所属部门。

SQL> select * from emp;
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1110 张三                 主管                          1110 12-3月 -14      5200     0          20
           1111 李四                 销售                          1116 03-11月-15      3400   500          30
           1112 王五                 销售                          1116 25-4月 -12      4400   800          30
           1113 赵二                 后勤                          1110 30-5月 -11      3450     0          40
           1114 李磊磊               会计                          1110 22-12月-15      2500     0          50
           1115 张少丽               销售                          1110 11-3月 -16      2400  1400          30
           1116 林建国               主管                          1116 22-1月 -16      5700     0          20
           1117 马富邦               后勤                          1116 22-7月 -13      2800     0          40
           1118 沈倩                 会计                          1116 06-5月 -10      2100     0          50

部门表

create table dept(
    DEPTNO NUMBER,
    DNAME VARCHAR2(50)
);

SQL> select * from dept t;

DEPTNO    DNAME
--------  --------
20        管理部门
30        销售部门
40        后勤部门
50        金融部门

SQL语句分为三种语句:
1. DML语句(Data Manipulation Language 数据操作语言): insert update delete select
2. DDL语句(Data Definition Language 数据定义语言): create/alter/drop/truncate table
                                                   create/drop view,create/drop index(sequence,synonym)
3. DCL语句(Data Control Language 数据控制语言): commit rollback

这次主要探讨DML语句的数据处理。

(1)地址符

首先看一下我们员工信息表emp的表结构
desc emp
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER
 HIREDATE                                           DATE
 SAL                                                BINARY_DOUBLE
 COMM                                               BINARY_DOUBLE
 DEPTNO                                             NUMBER

如果我们插入员工，可以使用:
insert into emp(empno,ename,job,sal,hiredate,deptno)
values(1119,'汪强','主管',2300,sysdate,20);

还有一种动态的插入方法，就是使用地址符“&”的插入方法：
我们在控制台连接数据库之后，输入以下语句
SQL> insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);

接下来控制台就会输出语句，让你填写相应的值，最终拼接到原来的语句上
输入 empno 的值:  1120
输入 ename 的值:  '刘丽丽'
输入 sal 的值:  3400
输入 deptno 的值:  50
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值    1: insert into emp(empno,ename,sal,deptno) values(1120,'刘丽丽',3400,50)

当我们执行完一次之后，只需要输入“/”，就可以再次使用刚刚那个语句，进行插入:
SQL> /
输入 empno 的值:  1121
输入 ename 的值:  '马德华'
输入 sal 的值:  2800
输入 deptno 的值:  40
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值    1: insert into emp(empno,ename,sal,deptno) values(1121,'马德华',2800,40)

已创建 1 行。

我们注意到，我们输入的ename是一个字符串，每次填写都需要写一对单引号('')，我们可以在定义
语句的时候提前将单引号定义在含有地址符的那个参数上，那么我们输入数据的时候就不需要输入单引号了。
SQL> insert into emp(empno,ename,sal,deptno) values(&empno,'&ename',&sal,&deptno);
输入 empno 的值:  1122
输入 ename 的值:  朱丽洁
输入 sal 的值:  4000
输入 deptno 的值:  20
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,'&ename',&sal,&deptno)
新值    1: insert into emp(empno,ename,sal,deptno) values(1122,'朱丽洁',4000,20)

已创建 1 行。

当然，我们也可以在select语句中使用它:
SQL> select empno,ename,&a from emp;
输入 a 的值:  sal
原值    1: select empno,ename,&a from emp
新值    1: select empno,ename,sal from emp

     EMPNO ENAME                  SAL
---------- -------------------- -----
      1119 汪强                  2300
      1120 刘丽丽                3400
      1121 马德华                2800
      1122 朱丽洁                4000
      1110 张三                  5200
      1111 李四                  3400
      1112 王五                  4400
      1113 赵二                  3450
      1114 李磊磊                2500
      1115 张少丽                2400
      1116 林建国                5700

     EMPNO ENAME                  SAL
---------- -------------------- -----
      1117 马富邦                2800
      1118 沈倩                  2100

已选择13行。

可以把a参数换成job
输入 a 的值:  job
原值    1: select empno,ename,&a from emp
新值    1: select empno,ename,job from emp

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      1119 汪强                 主管
      1120 刘丽丽
      1121 马德华
      1122 朱丽洁
      1110 张三                 主管
      1111 李四                 销售
      1112 王五                 销售
      1113 赵二                 后勤
      1114 李磊磊               会计
      1115 张少丽               销售
      1116 林建国               主管

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      1117 马富邦               后勤
      1118 沈倩                 会计

已选择13行。

还可以动态指定从哪张表取数据:
SQL> select * from &a;
输入 a 的值:  emp
原值    1: select * from &a
新值    1: select * from emp

上面是从emp，然后也可以指定取dept表的数据
SQL> /
输入 a 的值:  dept
原值    1: select * from &a
新值    1: select * from dept
DEPTNO    DNAME
--------  --------
20        管理部门
30        销售部门
40        后勤部门
50        金融部门


(2)插入数据的各种方式

①借鉴老表，创建新表。只插入表结构，不拷贝数据
SQL> create table emp02 as select * from emp where 1=2;

表已创建。

SQL> desc emp
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER
 HIREDATE                                           DATE
 SAL                                                BINARY_DOUBLE
 COMM                                               BINARY_DOUBLE
 DEPTNO                                             NUMBER

②从其他表向目标表导入数据
一次性将emp中所有20号部门的员工插入到emp02表
SQL> insert into emp02 select * from emp where deptno=20;

已创建4行

SQL> commit;

提交完成。

查看一下结果:
SQL> select * from emp02;

     EMPNO ENAME                JOB                       MGR HIREDATE         SAL  COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ----- ----- ----------
      1119 汪强                 主管                          26-8月 -16      2300               20
      1122 朱丽洁                                                             4000               20
      1110 张三                 主管                     1110 12-3月 -14      5200     0         20
      1116 林建国               主管                     1116 22-1月 -16      5700     0         20

(3)删除表

删除方法一:
truncate table emp20;

删除方法二:
delete from emp20;

delete和truncate table的区别
1. delete是DML，truncate是DDL (DML可以rollback，DDL不可以)
2. delete逐条删除；truncate先摧毁，再重建
3. delet会产生碎片；truncate不会
4. delete不会释放空间；truncate会

当删除的数据比较多的时候，如果数据不重要，没有回滚的可能性，那就使用truncate。
因为truncate比delete的删除速度快很多，因为它不需要考虑数据，是直接摧毁表然后再建立一个新表。


(4)保存点
保存点是用来标记目前的操作进程，当下一次rollback回滚的时候，可以设置从自己
设置的某个保存点开始回滚。
SQL> create table testsavepoint
  2  (tid number,tname varchar2(20));

表已创建。

SQL> insert into testsavepoint values(1,'Tom');

已创建 1 行。

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

已创建 1 行。

然后我们创建一个保存点
SQL> savepoint a;

保存点已创建。

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

已创建 1 行。

SQL> rollback to savepoint a;

回退已完成。

然后看一下我们的数据
SQL> select * from testsavepoint;

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

我们的数据，还是和之前创建保存点之前一样，所以我们的回滚，是发生在保存点之后。

转载请注明出处：http://blog.csdn.net/acmman/article/details/52350259