综合运用DML、DDL、DCL、TCL语句与事务管理

最新推荐文章于 2024-01-30 17:20:18 发布

无情白

最新推荐文章于 2024-01-30 17:20:18 发布

阅读量564

点赞数

分类专栏： Oracle 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/m0_49929446/article/details/134588288

版权

Oracle 专栏收录该内容

29 篇文章 2 订阅

订阅专栏

文章目录

一、介绍

在Oracle数据库中，掌握和运用DML、DDL、DCL和TCL语句是必不可少的技能。
这些语句在数据库的增删改查、结构设计、权限控制和事务管理中发挥着重要作用。
通过实际例子，我们将更深入地理解这些语句的应用。

二、DML：数据操纵语言

DML（Data Manipulation Language）用于对数据库中的数据进行增删改查操作。主要包括以下几种语句：

INSERT：用于向表中插入新数据。
UPDATE：用于修改表中现有数据。
DELETE：用于删除表中的数据。
SELECT：用于查询表中的数据。

注意：
在Oracle数据库中，当你插入数据后，需要使用COMMIT语句来提交修改，以使更改永久生效。
如果你在插入数据后发现有问题或错误，可以在未发生COMMIT之前使用ROLLBACK语句来回滚刚才的插入操作。

通过使用ROLLBACK语句，你可以撤销最近的未提交更改，将数据库恢复到插入操作之前的状态。这为你提供了在数据插入出现问题时纠正错误的机会。

但请注意，如果你在插入数据后已经执行了COMMIT语句，那么你就无法使用ROLLBACK语句撤销该插入操作。这是因为COMMIT语句将更改永久保存到数据库中，无法回滚。

INSERT案例：
假设我们有一个"employees"表，包含员工信息。我们可以使用DML语句进行数据的增删改查操作：

1.INSERT语句：向"employees"表中插入一条新记录，如：

INSERT INTO employees (id, name, age) VALUES (1, 'John Doe', 30);

或者

INSERT INTO employees  VALUES (1, 'John Doe', 30);

ROLLBACK;–撤销刚才的插入
COMMIT;插入生效

2.UPDATE语句：修改"employees"表中现有记录的年龄，如：

UPDATE employees SET age = 31 WHERE id = 1;

ROLLBACK;–撤销刚才的插入
COMMIT;插入生效

3.DELETE语句：删除"employees"表中的某条记录，如：

DELETE FROM employees WHERE id = 1;

ROLLBACK;–撤销刚才的插入
COMMIT;插入生效

4.TRUNCATE语句,删除"employees"表中的所有记录，如：

TRUNCATE  TABLE employees;

注意：
TRUNCATE语句不能回滚，一旦执行，表中的数据将永久删除。
TRUNCATE语句将重置表的计数器，所以在执行TRUNCATE后，表中的自增字段将从起始值重新开始计数。
TRUNCATE语句是一种高效的清空表数据的方法，特别适用于需要清空整个表而不需要逐行删除数据的场景。

5.SELECT语句：查询"employees"表中的数据，如：

SELECT * FROM employees WHERE age > 25;

三、DDL：数据定义语言

DDL（Data Definition Language）是数据库管理系统中用于定义和管理数据库对象的语言。它包括创建、修改和删除数据库、表、索引、视图等对象。

CREATE：用于创建数据库对象，如表、视图、索引等。
ALTER：用于修改数据库对象的结构。
DROP：用于删除数据库对象。

1.CREATE TABLE: 创建表

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary DECIMAL(10,2)
);

2.ALTER TABLE: 修改表结构

ALTER TABLE employees
  ADD COLUMN email VARCHAR(100);

3.DROP TABLE: 删除表

DROP TABLE employees;

4.CREATE INDEX: 创建索引

CREATE INDEX idx_employees_name ON employees (name);

5.ALTER INDEX: 修改索引

ALTER INDEX idx_employees_name RENAME TO idx_employees_last_name;

DROP INDEX: 删除索引

DROP INDEX idx_employees_last_name;

7.CREATE VIEW: 创建视图

CREATE VIEW employee_view AS
  SELECT id, name, age FROM employees WHERE age > 30;

8.ALTER VIEW: 修改视图

ALTER VIEW employee_view AS
  SELECT id, name, age, salary FROM employees WHERE age > 25;

9.DROP VIEW: 删除视图

DROP VIEW employee_view;

四、DCL：数据控制语言

DCL（Data Control Language）是数据库管理系统中用于控制数据库访问权限和事务操作的语言。它包括授权、回收权限以及管理事务等操作。

GRANT：用于授予用户或角色访问数据库的权限。
REVOKE：用于收回用户或角色的访问权限。

GRANT：授权用户或用户组访问权限

GRANT SELECT, INSERT ON employees TO user1;

REVOKE：回收用户或用户组的访问权限

REVOKE INSERT ON employees FROM user1;

五、TCL：事务控制语言

TCL（Transaction Control Language）用于管理数据库中的事务，确保数据的完整性和一致性。它允许用户对数据库中的操作进行逻辑分组，并在需要时进行提交或回滚。

COMMIT：用于提交事务，将修改永久保存到数据库中。
ROLLBACK：用于回滚事务，撤销对数据库的修改。
SAVEPOINT：用于在事务中设置保存点，以便在必要时回滚到特定状态。

1.COMMIT：将事务中的所有修改保存到数据库，成功提交事务。

BEGIN TRANSACTION;
UPDATE customer SET balance = balance - 100 WHERE id = 1;
INSERT INTO transaction (customer_id, amount) VALUES (1, -100);
COMMIT;

2.ROLLBACK：撤销事务中的所有修改，回滚到事务开始前的状态。

BEGIN TRANSACTION;
UPDATE customer SET balance = balance - 100 WHERE id = 1;
INSERT INTO transaction (customer_id, amount) VALUES (1, -100);
ROLLBACK;

3.SAVEPOINT 和 ROLLBACK TO SAVEPOINT：设置和回滚到保存点（一个特定时间点的事务状态）。

BEGIN TRANSACTION;
UPDATE customer SET balance = balance - 100 WHERE id = 1;
SAVEPOINT savepoint1;
UPDATE customer SET balance = balance + 100 WHERE id = 2;
ROLLBACK TO SAVEPOINT savepoint1;
COMMIT;

六、SELECT 查询时锁定

FOR UPDATE这个语句常常用于防止并发时多个事务同时修改或读取同一条记录，
以保证数据的安全性和一致性，它将锁定所选行，直到事务结束时才会释放该行的锁定。

FOR UPDATE 语句锁定所选行的示例：

BEGIN TRANSACTION;


-- 选取行并加锁
SELECT * FROM my_table WHERE id = 1 FOR UPDATE;


-- 在事务中修改所选行
UPDATE my_table SET value = 'new value' WHERE id = 1;


COMMIT;