Detailed explanation of the concepts DDL, DML, DCL, and TCL

As an experienced developer, you may not be unfamiliar with acronyms (nouns) such as DDL, DML, DCL, and TCL, but as a novice in Yiyi program development, you may have a lot of confusion. What follows? I will give you a detailed explanation, I hope it will be of some help to readers.

1. Data Definition Language DDL
DDL ((Data Definition Language): The main commands include CREATE, ALTER, DROP, etc., use Initialization work such as defining/modifying/deleting data structures, data types, links and constraints between data objects (such as tables), etc. is mostly used when creating tables. In other words, the objects operated by DDL language are objects in the database. Rather than the data contained in the object.

DDL contains the following statements:

 1、CREATE : 在数据库中创建新的数据对象
 2、ALTER : 修改数据库中对象的数据结构
 3、DROP : 删除数据库中的对象
 4、DISABLE/ENABLE TRIGGER : 修改触发器的状态
 5、UPDATE STATISTIC : 更新表/视图统计信息
 6、TRUNCATE TABLE : 清空表中数据
 7、COMMENT : 给数据对象添加注释
 8、RENAME : 更改数据对象名称

Note:
When executing a DDL statement, before and after each statement, the database (oracle/ Mysql) will submit the current transaction. If the user executes a DDL statement (such as create table) after using the insert command, the data from the insert command will be submitted to the database when the DDL statement is executed. , the DDL statement will be automatically submitted and cannot be rolled back.

2. Data Manipulation Language DML
DML: used to add/delete/modify/query/merge data in the database. For example, SELECT, UPDATE, INSERT, DELETE, and MERGE operations are languages ​​used to operate table data in the database.

DML contains the following statements:

 1、INSERT ：将数据插入到表或视图
 2、DELETE ：从表或视图删除数据
 3、SELECT ：从表或视图中获取数据
 4、UPDATE ：更新表或视图中的数据
 5、MERGE ： 对数据进行合并操作(插入/更新/删除)

Note:
If the DML command is not submitted, it will not be seen by other sessions. Unless the DDL command or DCL command is executed after the DML command, or the user exits the session, or terminates the instance, the system will automatically issue a commit command. Submit unsubmitted DML commands.

3. Data Control Language DCL
DCL (Data Control Language): is a statement used to set or change database user or role permissions, including (grant, deny, revoke, etc.).

DCL contains the following statements:

 1、GRANT : 赋予用户某种控制权限
 2、REVOKE ：取消用户某种控制权限

The data control language only needs to create, delete, change passwords, and grant permissions and delete permissions to users. Operation.

4. Transaction Control Language (TCL)
TCL (Transaction Control Language): Used to manage transactions.

TCL contains the following statements:

  1、COMMIT : 保存已完成事务动作结果
  2、SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作
  3、ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后
  4、SET TRANSACTION : 设置事务选项

Since DML statements will add row-level locks when operating table data, after the confirmation is completed, the command COMMIT to end the transaction must be added to officially take effect, otherwise the changes may not be written to the database if you want to withdraw them. operation, can be restored with the command ROLLBACK.
Before running INSERT, DELETE and UPDATE statements, it is best to estimate the record range of possible operations. It should be limited to a smaller range, such as 10,000 records, otherwise ORACLE will use a large rollback to process this matter. part. The program responds slowly or even becomes unresponsive. If there are hundreds of thousands or more of these operations recorded. These SQL statements can be completed in sections. Add COMMIT to confirm the transaction processing.

According to the different target of statement operation, it is still easy to distinguish these four types: DDL-data object; DML-data; DCL-permission; TCL-transaction.
The only thing that needs attention is TRUNCATE. Although it is functionally equivalent to DELETE all the data in the table, it operates on the table level instead of row (when the table data cannot be deleted immediately for some reason, TRUNCATE will lock the entire table, while DELETE locks row), so it is included in DDL.​​

The above is the detailed content of Detailed explanation of the concepts DDL, DML, DCL, and TCL. For more information, please follow other related articles on the PHP Chinese website!