Oracle系列十一 数据处理

数据操纵语言

  • DML(Data Manipulation Language – 数据操纵语言) 可以在下列条件下执行:
  1. 向表中插入数据
  2. 修改现存数据
  3. 删除现存数据
  • 事务是由完成若干项工作的DML语句组成的

INSERT 语句语法

  • 使用 INSERT 语句向表中插入数据。
  • 使用这种语法一次只能向表中插入一条数据。
INSERT INTO    table [(column [, column...])]
VALUES        (value [, value...]);

插入数据

  • 为每一列添加一个新值。
  • 按列的默认顺序列出各个列的值。
  • 在 INSERT 子句中随意列出列名和他们的值。
  • 字符和日期型数据应包含在单引号中。
INSERT INTO DEPARTMENTS (
    department_id,
    department_name,
    manager_id,
    location_id
) VALUES (
    280,
    'Public Relations',
    100,
    1700
);
INSERT INTO employees (
    employee_id,
    last_name,
    email,
    hire_date,
    job_id
) VALUES (
    300,
    'Tom',
    'tom@126.com',
    TO_DATE('2012-3-21','yyyy-mm-dd'),
    'SA_RAP'
);

向表中插入空值

  • 隐式方式: 在列名表中省略该列的值。
INSERT INTO departments (
    department_id,
    department_name  
) VALUES (
    290,
    'Purchasing'
);
  • 显示方式: 在VALUES 子句中指定空值
INSERT INTO departments VALUES (
    100,
    'Finance',
    NULL,
    NULL
);

插入指定的值

  • SYSDATE 记录当前系统的日期和时间。
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
) VALUES (
    113,
    'Louis',
    'Popp',
    'LPOPP',
    '515.124.4567',
    SYSDATE,
    'AC_ACCOUNT',
    6900,
    NULL,
    205,
    100
);

插入指定的值

INSERT INTO employees VALUES (
    114,
    'Den',
    'Raphealy',
    'DRAPHEAL',
    '515.127.4561',
    TO_DATE('FEB 3, 1999','MON DD, YYYY'),
    'AC_ACCOUNT',
    11000,
    NULL,
    100,
    30
);

创建脚本

  • 在SQL 语句中使用 & 变量指定列值。
  • & 变量放在VALUES子句中。
INSERT INTO departments (
    department_id,
    department_name,
    location_id
) VALUES (
    &department_id,
    '&department_name',
    &location
);


从其它表中拷贝数据

  • 在 INSERT 语句中加入子查询。
  • 不必书写 VALUES 子句。
  • 子查询中的值列表应与 INSERT 子句中的列名对应
INSERT INTO emp2
    SELECT
        *
    FROM
        employees
    WHERE
        department_id = 90;
INSERT INTO sales_reps (
    id,
    name,
    salary,
    commission_pct
)
    SELECT
        employee_id,
        last_name,
        salary,
        commission_pct
    FROM
        employees
    WHERE
        job_id LIKE '%REP%';

更新数据

UPDATE 语句语法

  • 使用 UPDATE 语句更新数据。
  • 可以一次更新多条数据。
  • 使用 WHERE 子句指定需要更新的数据。
  • 如果省略 WHERE 子句,则表中的所有数据都将被更新
UPDATE        table
SET        column = value [, column = value, ...]
[WHERE         condition];
UPDATE employees
    SET
        department_id = 70
WHERE
    employee_id = 113;
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 中使用子查询,使更新基于另一个表中的数据。

示例:调整与employee_id 为200的员工job_id相同的员工的department_id为employee_id为100的员工的department_id。

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
    );

更新中的数据完整性错误

UPDATE employees
    SET
        department_id = 55
WHERE
    department_id = 110;


删除数据
DELETE 语句

  • 使用 DELETE 语句从表中删除数据。
DELETE FROM      table
[WHERE      condition];
  • 使用 WHERE 子句删除指定的记录
DELETE FROM departments WHERE
    department_name = 'Finance';
  • 如果省略 WHERE 子句,则表中的全部数据将被删除
DELETE FROM  copy_emp;

在 DELETE 中使用子查询

  • 在 DELETE 中使用子查询,使删除基于另一个表中的数据。

示例:从emp1表中删除dept1部门名称中含Public字符的部门id

DELETE FROM emp1
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            dept1
        WHERE
            department_name LIKE '%Public%'
    );

删除中的数据完整性错误

DELETE FROM departments WHERE
    department_id = 60;


数据库事务

  • 事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
  • 数据库事务由以下的部分组成:
  1. 一个或多个DML 语句
  2. 一个 DDL(Data Definition Language – 数据定义语言) 语句
  3. 一个 DCL(Data Control Language – 数据控制语言) 语句

 

  • 以第一个 DML 语句的执行作为开始


以下面的其中之一作为结束:

  • COMMIT 或 ROLLBACK 语句
  • DDL 语句(自动提交)
  • 用户会话正常结束
  • 系统异常终止

COMMIT和ROLLBACK语句的优点


使用COMMIT 和 ROLLBACK语句,我们可以:

  • 确保数据完整性。
  • 数据改变被提交之前预览。
  • 将逻辑上相关的操作分组。

控制事务

回滚到保留点

 

  • 使用 SAVEPOINT 语句在当前事务中创建保存点。
  • 使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.

事务进程

  • 自动提交在以下情况中执行:
  1. DDL 语句。
  2. DCL 语句。
  3. 不使用 COMMIT 或 ROLLBACK 语句提交或回滚,正常结束会话。
  • 会话异常结束或系统异常会导致自动回滚。
  • 提交或回滚前的数据状态
  • 改变前的数据状态是可以恢复的
  • 执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
  • 其他用户不能看到当前用户所做的改变,直到当前用户结束事务。
  • DML语句所涉及到的行被锁定, 其他用户不能操作。

提交后的数据状态

  • 数据的改变已经被保存到数据库中。
  • 改变前的数据已经丢失。
  • 所有用户可以看到结果。
  • 锁被释放,其他用户可以操作涉及到的数据。
  • 所有保存点被释放。

提交数据

  1. 改变数据
DELETE FROM employees
WHERE  employee_id = 99999;

提交数据

COMMIT;

数据回滚后的状态


使用 ROLLBACK 语句可使数据变化失效:

  • 数据改变被取消。
  • 修改前的数据状态被恢复。
  • 锁被释放。
DELETE FROM copy_emp;
22 rows deleted.
ROLLBACK;
Rollback complete.

 

posted on 2019-11-19 21:07  LoaderMan  阅读(251)  评论(0编辑  收藏  举报

导航