Oracle 19C学习 - 10. DML数据处理语言管理表 - LeoZhangJing

Oracle 19C学习 - 10. DML数据处理语言管理表

数据处理语言

当你执行DML语句时:

向表中添加新行
修改表中的现有行
从表中删除现有行
事务由构成逻辑工作单元的DML语句的集合组成。

INSERT语句语法

INSERT INTO    table [(column [, column...])]
VALUES        (value [, value...]);

1. 插入一个新行，其中包含每一列的值。以表中列的默认顺序列出值并对应插入。
或者：列出INSERT子句中的列。
2. 将字符和日期值括在单引号内。

--这两句话是等价的，添加一行数据并填充所有字段的值。
INSERT INTO departments(department_id, department_name, manager_id, location_id)  --列出所有字段
VALUES (999, 'DUMMY', 100, 1700);

INSERT INTO departments VALUES (888, 'DUMMY', 100, 1700);   --可以不填写字段，value包含了所有字段的值。

--可以填充部分字段，这样就必须在表名后指定要填充的字段名
INSERT INTO departments(department_id, department) 
VALUES (777, 'DUMMY'); 

--插入的时候可以显式的使用空值
INSERT INTO departments VALUES(666, 'FIN',  NULL, NULL);

--插入日期值
INSERT INTO employees
VALUES      (222, 'Leo', 'Zhang', 'leo.zhang@163.com', '515.127.4561', TO_DATE('3月 3, 2021', 'MONTH DD, YYYY'), 'SA_REP', 11000, 0.2, 100, 100);

COMMIT 和 ROLLBACK

对于数据表的插入、修改和删除，ORACLE中必须使用COMMIT提交才能生效。MYSQL不用COMMIT，因为他是自动COMMIT。

COMMIT之前如果发现数据有问题，可以使用ROLLBACK来撤销更改。例外：如果插入的时候使用了sequence序列，ROLLBACK以后序列的值不回滚。

INSERT INTO departments(department_id, department_name) VALUES (777 , 'DUMMY');  
SELECT * FROM departments;  --能够看到新加入的777
ROLLBACK;   --ROLLBACK进行回退，这时候新增加的777就消失了

用子查询复制行插入表

不要使用VALUES子句。
将INSERT子句中的列数与子查询中的列数匹配。
将子查询返回的所有行插入表中

INSERT INTO sales(employee_id, last_name, first_name)
    SELECT employee_id, last_name, first_name
    FROM employees
    WHERE job_id LIKE '%REP%';
COMMIT； --提交数据

UPDATE语句语法

UPDATE     table
SET        column = value [, column = value, ...]
[WHERE     condition];

注意：如果不加WHERE子句，则一次更新了所有的行。

将员工号=150的员工更名为Zhang San，去掉phone_number字段内容。

UPDATE employees
SET last_name = 'Zhang' , first_name = 'San'， phone_number = NULL
WHERE employee_id = 150;

使用150号员工的工作和工资，来更新130号员工的工作和工资

UPDATE employees
SET (job_id, salary) = (
                        SELECT job_id, salary 
                        FROM employees
                        WHERE employee_id = 150
                        )
WHERE employee_id = 130;

使用UPDATE的子查询来更新另外一张表的数据。

UPDATE employees_copy
SET phone_number = (
                    SELECT phone_number
                    FROM employees
                    WHERE employee_id = 150
                    )
WHERE job_id = (
                SELECT job_id
                FROM employees
                WHERE employee_id = 150
                );

DELETE语句语法

DELETE [FROM]      table
[WHERE             condition];

加不加FROM效果相同。

DELETE FROM employees_copy;
DELETE employees_copy;

通过子查询设定删除的condition。

DELETE employees_copy 
WHERE department_id IN (
                        SELECT DEPARTMENT_ID 
                        FROM departments 
                        WHERE UPPER(department_name) LIKE '%AN%'
                        );

TRUNCATE语句

TRUNCATE TABLE table_name;

从表中删除所有行，使表为空，并保持表结构完整
是数据定义语言（DDL）语句而不是DML语句；不能轻易撤消。ROLLBACK不回来数据。
删除大表数据的时候，TRUNCATE语句的速度比DELETE语句速度快。

数据库的事务

数据库事务由以下之一组成:

DML构成对数据的一致更改： INSERT, UPDATE, DELETE, MERGE, SELECT ... FOR UPDATE
一个DDL语句: CREATE, ALTER, DROP, TRUNCATE
一种数据控制语言（DCL）语句: GRANT, REVOKE
TCL语句： COMMIT, ROLLBACK (特殊：虽然这两个语句是控制事务的，执行的过程中，也在数据库底层产生底层事务)

数据库事务：开始和结束

从第一个DML SQL语句执行开始。
以下列事件之一结束: 发出COMMIT或ROLLBACK语句。
DDL或DCL语句执行（自动提交）。
用户退出SQL Developer或SQL * Plus，这会自动提交。
系统崩溃，这会自动回滚。

COMMIT和ROLLBACK的优点

使用COMMIT和ROLLBACK语句，您可以:
确保数据一致性
在永久更改之前预览数据更改
分组逻辑相关的操作

Savepoint语句

控制ROLLBACK撤销的位置。

SELECT * FROM EMPLOYEES_COPY;
SAVEPOINT before_change;   --Savepoint 已创建。
UPDATE employees_copy
SET phone_number = NULL;   --108 行已更新。
SAVEPOINT after_change_phone; --Savepoint 已创建
DELETE FROM employees_copy;  --108 行已删除。
ROLLBACK TO after_change_phone;  --回退的顺序不能反了
ROLLBACK TO before_change;

COMMIT或者ROLLBACK之前的数据状态

可以恢复数据的先前状态。
当前会话可以通过使用SELECT语句查看DML操作的结果。
其他会话无法查看当前会话发出的DML语句的结果。
受影响的行被锁定；其他会话无法更改受影响的行中的数据。

COMMIT之后的数据状态

数据更改保存在数据库中。
数据的先前状态将被覆盖。
所有会话都可以查看结果。
受影响行上的锁被释放；这些行可供其他会话操纵。
所有保存点被删除。

读取一致性

读取一致性可确保始终保持一致的数据视图。
一个用户所做的更改不会与另一用户所做的更改冲突。
读取一致性可确保在相同数据上:　　
- Readers do not wait for writers 读不等写，数据读取的时候，其他用户未COMMIT的数据不会出现在结果中。
- Writers do not wait for readers 写不等读，用户写入的时候，其他人的读取操作不影响，写入只是在当前会话中。
- Writers wait for writers 写只等写，同一行数据，一个用户的更改未提交的时候，其他用户对这行数据的更改都无法操作。

SELECT ... FOR UPDATE语句

对于SELECT查询出来的结果进行锁定，其他会话无法更改被锁定的数据。

执行COMMIT或者ROLLBACK之后，对于数据的锁定会被释放。

SELECT * 
FROM employees
WHERE employee_id = 200
FOR UPDATE;   --employee_id=200的记录被锁定。
COMMIT;  --释放对于employee_id=200的锁定.

如果SELECT语句尝试锁定被另一个用户锁定的行，则数据库将等待直到该行可用为止(进行了ROLLBACK或者COMMIT)，然后返回SELECT语句的结果.

例如：下图演示了其他会话对于查询条件的记录进行了FOR UPDATE操作以后，在执行这个语句就会一直等待。

posted on 2021-05-15 00:34 LeoZhangJing 阅读(112) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Oracle 19C学习 - 10. DML数据处理语言管理表

导航