MySQL DDL, DML, DCL, DQL distinguish

MySQL [Structure Query Language] consists of 4 parts:

• DDL [Data mefinition Language] Data definition language
• DML [Data manipulation Language] manipulation language
• DCL [Data Control Language] the controls language
• DQL [data query Language] querying language

　　

1. DDL Data definition

Key points: CREATE table, ALTER table, DROP table, Create/drop INDEX, and more

It is mainly used in the initialization of the structure, data types, links between tables, and constraints, and so on, mostly when tables are created.

DDL operations are implicitly committed! Can't rollback.

2. DML Data manipulation

Key points: INSERT, UPDATE, DELETE

The main is to operate the table data, such as the table data to increase, delete, change.

3. DCL Data Control

Key points: GRANT, REVOKE, ROLLBACK, COMMIT

used primarily to grant or reclaim certain privileges to access the database, and to control The time and effect of database manipulation transaction, and the monitoring of database.

Such as:

User Authorized Grant

ROLLBACK [Work] to [savepoint]: fallback to a point.

Roll Back---ROLLBACK

The rollback command brings the database state back to the last committed state. The format is:

sql>rollback;

commit [work]: commit a transaction

When the data is added, deleted, and changed, the transaction is completed only when it is committed to the database.

Before a transaction is committed, only the person who operates the database has permission to see what is being done, and others can only see it after the final commit is completed.

There are three types of submission data: explicit commit, implicit commit, and auto-commit.

1) Explicit Commit
Commits that are completed directly with the commit command are explicitly committed. The format is:
sql>commit;

(2) Implicit submission
Commits that are indirectly completed with SQL commands are implicitly committed. These commands are:
Alter,audit,comment,connect,create,disconnect,drop,
Exit,grant,noaudit,quit,revoke,rename.

(3) Automatic submission
If the autocommit is set to ON, after the insert, modify, DELETE statement executes,
The system will commit automatically, which is automatic submission. The format is:
Sql>set autocommit on; open

Sql>set autocommit off;

In real development, when creating a transaction, you need to turn off autocommit (which is turned on by default), and then explicitly commit the transaction when all successful operations succeed. After the transaction commits, turn on autocommit again.

4, DQL data query

Key point: SELECT

The DQL basic structure is a query block consisting of a SELECT statement, a from statement, a WHERE statement:

Basic syntax: SELECT < field name table > from < table or view name > WHERE < query conditions >

MySQL DDL, DML, DCL, DQL distinguish