SQL: DDL DML DCL TCL

[dyrnq]

1.DDL (Data Definition Language) statements are used to define the database structure or schema.

DDL is one of the four major functions of SQL language.

It is used to define the three-level structure of the database, including external model, conceptual model, internal model and their mutual mapping, and define constraints such as data integrity and security control.

DDL does not require commit.

CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

2.DML (Data Manipulation Language) statements are used for managing data within schema objects.

It is provided by DBMS for users or programmers to operate on data in the database.

DML is divided into interactive DML and embedded DML.

DML can be divided into procedural DML and non-procedural DML according to the level of language.

Requires commit.

SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

3.DCL (Data Control Language) Database Control Language Authorization, role control, etc.

GRANT Authorization
REVOKE Cancel authorization

4.TCL (Transaction Control Language) Transaction Control Language

SAVEPOINT Set save point
ROLLBACK Rollback
SET TRANSACTION

SQL is mainly divided into four parts:

• (1) Data definition. (SQL DDL) Used to define the creation and undo operations of SQL modes, basic tables, views and indexes.
• (2) Data manipulation. (SQL DML) Data manipulation is divided into two categories: data query and data update. Data update is further divided into three operations: insert, delete, and modify.
• (3) Data control. Including authorization for basic tables and views, description of integrity rules, transaction control, etc.
• (4) Rules for the use of embedded SQL. Involves the rules for using SQL statements embedded in host language programs.