Oracle SQL语句5大分类：DDL、DML、DQL、DCL、TCL

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

SQL语句5大分类

1. DDL (Data Definition Language) 数据定义语言

DDL 操作用于定义、修改和删除数据库对象的结构和属性。这些操作不直接涉及数据的操作，而是影响数据库的整体结构。常见的 DDL 操作包括：

• CREATE(创建命令)
• ALTER(修改命令)
• DROP(删除命令)
• TRUNCATE(清空命令)

1. CREATE(创建命令)

CREATE: 用于创建数据库对象，如表、索引、视图等。

a.常规建表法

语法：

  CREATE TABLE 表名
    (字段名(列名) 数据类型 约束,
     字段名(列名) 数据类型
    );

创建用户信息表

  CREATE TABLE USER_INFO
    (ID NUMBER primary key,
     U_NAME VARCHAR2(50),
     SEX VARCHAR2(10),
     BIRTHDAY DATE
    );

b.根据查询结果集建表法

根据查询结果集建表法也称复制建表法 复制不了原表的约束条件（比如主键，外键）。
语法：

  CREATE TABLE 表名 AS
  SELECT 查询语句;

• a.建表加复制原表数据

  CREATE TABLE EMP_01 AS --会复制表数据
  SELECT * FROM EMP;

• b.只复制创建原表 表字段（但不涉及原表约束复制）

  CREATE TABLE EMP_1010 AS
  SELECT * FROM EMP 
  WHERE 1=2;  --整一个不成立的where过滤条件，就不会复制数据

2. ALTER(修改命令)

ALTER: 用于修改数据库对象的结构，如表结构的更改、列的添加等。

1.添加列

--语法：
  ALTER TABLE 表名 ADD 列名 数据类型;
--用户信息表中添加列（用户手机号码 字段）      
  ALTER TABLE USER_INFO ADD PHONE_NUMBER VARCHAR2(20)

2.修改列的数据类型

--语法：
  ALTER TABLE 表名 MODIFY 列名 新的数据类型;
--改变用户手机号码字段数据类型，修改为数字类型
  ALTER TABLE USER_INFO MODIFY PHONE_NUMBER NUMBER(20);

3.修改列名

--语法：
  ALTER TABLE 表名 RENAME COLUMN 原列名 TO 新列名;  --column关键字不能少
--将用户信息表手机号码字段PHONE_NUMBER重命名为P_NUMBER
  ALTER TABLE USER_INFO RENAME COLUMN PHONE_NUMBER TO P_NUMBER;  

4.删除列

--语法：
 ALTER TABLE 表名 DROP COLUMN 列名;  --column关键字不能少
--删除用户信息表用户手机号码P_NUMBER字段
 ALTER TABLE USER_INFO DROP COLUMN P_NUMBER;

5.修改表名

--语法：
  ALTER TABLE 原表名 RENAME TO 新表名;
--将用户信息表 USER_INFO 重命名为USER_INFO_TEST1
  ALTER TABLE USER_INFO RENAME TO USER_INFO_TEST1;

3. DROP(删除命令)

DROP: 用于删除数据库对象，如删除表、索引、视图等。

--语法：
  DROP TABLE 表名/索引/视图
--删除表 EMP_1009
  DROP TABLE EMP_1009;

4. TRUNCATE(清空命令)

TRUNCATE: 用于删除表中的所有数据，但保留表的结构。

• TRUNCATE清空表的实质是先删除整张表，然后重新创建该表

--语法：
  TRUNCATE TABLE 表名;
--清空用户表EMP_1010，保留表结构
  TRUNCATE TABLE EMP_1010;

清空表前：
清空表后：

2. DML (Data Manipulation Language) 数据操作语言

DML 操作用于操作和处理数据库中的数据。这些操作影响数据的内容，而不是数据库对象的结构。常见的 DML 操作包括：

• INSERT: 用于将新数据插入表中。
• UPDATE: 用于修改表中现有数据的值。
• DELETE: 用于从表中删除数据。
• MERGE: 用于根据条件执行插入、更新和删除操作，根据源数据与目标表之间的匹配情况。

1. INSERT 插入

a.查询结果插入法

--语法：
INSERT INTO 表名(字段名1,字段名2,...) select 查询结果集;
--例子：
INSERT INTO EMP_1010(ename,job,sal) select ename,job,sal from emp;  --将查询结果集插入表

b.常规插入法

--语法：
 INSERT INTO 表名(字段名1,字段名2,...) VALUES (值1,值2,...);
 COMMIT; --提交
--例子：
INSERT INTO USER_INFO_test1(ID,U_NAME,SEX,BIRTHDAY) VALUES (1234567,'AZHEN','W',TO_DATE('19990101', 'YYYYMMDD'));
INSERT INTO USER_INFO_test1(ID,U_NAME,SEX,BIRTHDAY) VALUES (1234568,'AQIANG','M',TO_DATE('19990201', 'YYYYMMDD'));
INSERT INTO USER_INFO_test1(ID,U_NAME,SEX,BIRTHDAY) VALUES (1234569,'FULAOSHI','M',TO_DATE('19580201', 'YYYYMMDD'));
COMMIT; --提交

2. UPDATE 更新

--语法：
update 表名 set 字段=新值 where 过滤条件
--例：
update ssm set name='xiaocui' where name='AZHEN';
update ssm set name='xiaoliu' where name='AZHEN' and age=27;

3. DELETE 删除

--语法：
delete from 表名 where ...
--例：
delete from ssm where name='AZHEN' and age=27; 

4. MERGE 条件修改

MERGE 语句在 Oracle 数据库中用于同时执行插入（INSERT）和更新（UPDATE）操作，通常用于将两个表之间的数据合并。这是一种非常强大的功能，特别适用于数据同步和更新场景。MERGE 语句的一般语法如下：

MERGE INTO target_table USING source_table
ON (condition)
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...) VALUES (value1, value2, ...);

以下是各个部分的解释：

• MERGE INTO target_table: 指定目标表，即你希望将数据插入或更新的表。
• USING source_table: 指定源表，即提供数据的表。
• ON (condition): 定义用于匹配目标表和源表之间数据的条件。如果条件成立，将执行 UPDATE 操作，否则执行 INSERT 操作。
• WHEN MATCHED THEN: 如果在目标表和源表之间存在匹配的数据，就会执行这一块。在这里，你可以定义如何更新目标表中的数据。
• UPDATE SET column1 = value1, column2 = value2, …: 在匹配的情况下，你可以使用 UPDATE 子句来更新目标表的列。
• WHEN NOT MATCHED THEN: 如果在目标表中找不到匹配的数据，就会执行这一块。在这里，你可以定义如何将源表的数据插入到目标表中。
• INSERT (column1, column2, …) VALUES (value1, value2, …): 在不匹配的情况下，你可以使用 INSERT 子句将源表中的数据插入到目标表中。

假设我们有一个目标表 employees 和一个源表 temp_employees，我们想要将源表中的员工数据合并到目标表中。我们可以执行以下 MERGE 语句：

MERGE INTO employees e
USING temp_employees t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = t.salary
WHEN NOT MATCHED THEN
    INSERT (e.employee_id, e.first_name, e.last_name, e.salary)
    VALUES (t.employee_id, t.first_name, t.last_name, t.salary);

这个示例中，如果在目标表中找到匹配的员工（根据 employee_id），则更新目标表中的 salary 列。如果在目标表中找不到匹配的员工，就插入新的员工数据。这样，你可以确保目标表中包含最新的员工数据。

3. DQL (Data Query Language) 数据查询语言

DQL 操作用于从数据库中检索数据。这些操作帮助你查询需要的数据以供查看和分析。唯一的 DQL 操作是：

• SELECT: 用于从一个或多个表中选择列，通过条件和约束来过滤数据，以及进行聚合、排序等操作。

select * from emp；

4. DCL (Data Control Language) 数据控制语言

DCL 操作用于控制对数据库对象的访问权限和权限管理。它用于管理数据库的安全性和访问级别。常见的 DCL 操作包括：

• GRANT: 用于授予用户和角色对数据库对象的访问权限。
• REVOKE: 用于撤销用户和角色对数据库对象的访问权限。
• 权限

1. GRANT 语句

GRANT 命令用于赋予用户或角色特定的数据库权限。以下是一些常见的权限类型，可以使用 GRANT 命令进行授予：

• SELECT：允许用户从表中检索数据。
• INSERT：允许用户向表中插入新数据。
• UPDATE：允许用户修改表中现有数据。
• DELETE：允许用户从表中删除数据。
• CREATE：允许用户创建新表、视图或其他数据库对象。
• ALTER：允许用户修改现有数据库对象的结构。
• DROP：允许用户删除数据库对象。
• EXECUTE：允许用户执行存储过程或函数。
• ALL：赋予用户所有可用的权限。
• DBA 赋予数据库管理权限。
• connect 赋予(登录oracle权限)
• resource赋予查看资源，创建表之类权限。
• select any table赋予查看任何表的权限。
• execute any procedure跨用户执行存储过程文件权限。
• WITH GRANT OPTION在使用 GRANT 命令时，你可以添加 WITH GRANT OPTION 选项，以允许受权用户将他们被授予的权限再授予其他用户或角色。

--语法：
grant 权限 to 用户 --授予用户权限
grant 权限1，权限2... to 用户 --同时授予用户多项权限

grant dba to xiaocui;  --赋予xiaocui用户数据库管理权限（DBA）
grant connect,resource to xiaocui;  --授予xiaocui用户基本权限（登录、资源访问）
grant select any table to xiaocui; --赋予查看任何表的权限

2. REVOKE 语句

REVOKE: 用于撤销用户和角色对数据库对象的访问权限。

--语法：
revoke 权限 from 用户 --收回用户权限
revoke 权限1，权限2... from 用户 --同时收回用户多项权限

revoke dba from xiaocui;  --收回用户xiaocui的DBA权限
revoke select any table from xiaocui; --收回用户xiaocui查看任何表的权限

5. TCL (Transaction Control Language) 事务控制语言

TCL 操作用于管理数据库中的事务。事务是一组数据库操作，要么全部成功执行，要么全部回滚。常见的 TCL 操作包括：

• COMMIT: 用于将挂起的事务更改永久保存到数据库中。
• ROLLBACK: 用于撤消尚未提交的事务更改，回到事务开始之前的状态。
• SAVEPOINT: 用于在事务中创建一个保存点，以便可以回滚到该点。

1.COMMIT

在 Oracle 数据库中，COMMIT 是用于确认和永久保存之前进行的事务操作的 SQL 命令。当你执行 COMMIT 时，它会将在当前事务中所做的更改保存到数据库中，并结束事务。这是事务处理的一个关键概念，用于确保数据的完整性和一致性。以下是关于 COMMIT 的一些重要信息和使用方法：

1. 提交事务：

• COMMIT 命令用于提交当前事务。一旦执行 COMMIT，所有之前在该事务中执行的 DML（数据操作语言）语句（如 INSERT、UPDATE、DELETE）所做的更改将永久保存到数据库中。
• 在提交之前，其他会话或事务无法看到当前事务所做的更改。一旦提交，其他会话或事务可以看到这些更改。

2. 语法：COMMIT 命令没有参数，其语法非常简单，只需执行 COMMIT; 即可。

commit;

3. 自动提交：

• 在 Oracle 数据库中，如果没有显式执行 COMMIT，一些操作（如 SELECT）通常会自动提交。这意味着每个单独的语句都被视为一个独立的事务，并且在执行后会自动提交。
• 要关闭自动提交模式，可以使用 SET AUTOCOMMIT OFF; 命令，这样你可以在执行多个语句后手动执行 COMMIT。

4. 回滚：

• 如果在执行 COMMIT 之前发生了错误或你不希望保存之前的更改，可以使用 ROLLBACK 命令来回滚事务。ROLLBACK 会取消事务中的所有更改，将数据库恢复到事务开始前的状态。
• 例如，ROLLBACK; 可以用来取消当前事务的所有更改。

5. 事务控制：
   使用 COMMIT 和 ROLLBACK 可以控制事务的开始和结束。在一个事务内，你可以执行多个操作，然后根据需要决定是否提交或回滚这些操作。

2.ROLLBACK

在 Oracle 数据库中，ROLLBACK 是用于撤销事务中所有未提交的更改的 SQL 命令。它允许你回滚事务，将数据库恢复到事务开始之前的状态，以确保数据的完整性和一致性。以下是关于 ROLLBACK 的一些重要信息和使用方法：

1. 回滚事务：

• ROLLBACK 命令用于回滚当前事务中的所有未提交更改。这包括在当前事务内执行的所有 DML（数据操作语言）操作（如 INSERT、UPDATE、DELETE）所做的更改。
• 执行 ROLLBACK; 会将数据库恢复到当前事务开始之前的状态，取消在该事务中执行的所有更改。

2. 语法：
   ROLLBACK 命令没有参数，其语法非常简单，只需执行 ROLLBACK; 即可。

rollback；

3. 回滚到保存点：

• 使用 ROLLBACK TO 命令将事务回滚到特定的保存点，而不是回滚整个事务。保存点是使用 SAVEPOINT 命令创建的，可以在事务内的不同阶段创建临时恢复点，然后在需要时回滚到这些点。
• 示例：ROLLBACK TO my_savepoint;

4. 回滚控制：
   ROLLBACK 允许你控制事务的回滚。如果在执行事务期间发生错误或者不希望保存之前的更改，可以使用 ROLLBACK 来撤销这些更改。
5. 自动回滚：
   在 Oracle 数据库中，如果事务内的任何语句导致错误，Oracle 会自动执行回滚操作，以确保数据库的一致性。这是数据库的默认行为。

3.SAVEPOINT

SAVEPOINT 是 Oracle 数据库中用于实现事务控制的一种机制，它允许你在事务中创建一个保存点，以便在后续的操作中可以回滚到这个保存点。这对于在事务内部部分回滚而不必回滚整个事务非常有用。SAVEPOINT 的一般语法如下：

SAVEPOINT savepoint_name;

以下是 SAVEPOINT 的使用示例和说明：

1. 创建保存点：
   你可以使用 SAVEPOINT 语句创建一个保存点，如下所示：

SAVEPOINT my_savepoint;

这将在当前事务中创建一个名为my_savepoint 的保存点。

2. 回滚到保存点：
   一旦创建了保存点，你可以在后续的操作中使用 ROLLBACK TO 语句来回滚到该保存点，如下所示：

ROLLBACK TO my_savepoint;

这将撤消从创建保存点到回滚点之间的所有更改，但会保留从事务开始到保存点创建的任何更改。

3. 释放保存点：
   你可以使用 RELEASE 命令来释放保存点，从而将其从事务中移除。一旦释放，你将无法再次回滚到该保存点。示例如下：

RELEASE SAVEPOINT my_savepoint;

4. 嵌套保存点：
   你可以在同一个事务中创建多个保存点，形成嵌套的保存点结构。这样，你可以在不同的时间点回滚到不同的保存点，而不会影响其他保存点之间的更改。

SAVEPOINT savepoint1;
-- 执行一些操作
SAVEPOINT savepoint2;
-- 执行一些操作
ROLLBACK TO savepoint1; -- 可以回滚到 savepoint1，但不会影响 savepoint2

SAVEPOINT 是一个有用的功能，特别是在复杂的事务中，它允许你在事务内的不同阶段创建临时的恢复点，以便在需要时回滚到特定的状态，而不必回滚整个事务。这对于数据完整性和一致性的维护非常重要。