Difference and understanding of DDL, DML, and DCL

DML, DDL, DCL differences.

Overall explanation:

DML (data manipulation language ):
They are SELECT, UPDATE, INSERT, and DELETE, just like their names. These four commands are used to operate the data in the database.
DDL (data definition language ):
DDL is more than DML. The main Commands include CREATE, ALTER, and DROP. DDL is mainly used to define or change the TABLE structure and data type, most of them use
DCL (Data Control Language ):
Is the database control function. Is a statement used to set or change database user or role permissions, including (grant, deny, revoke, etc.) statements. By default, only sysadmin, dbcreator, db_owner, db_securityadmin, and other personnel are authorized to execute DCL.

Explanation:

1. DDL is Data Definition Language statements. Some examples: the Data Definition Language used to define and manage all objects in the SQL database.
1. CREATE-to create objects in the database creation
2. ALTER-alters the structure of the database Modification
3. DROP-delete objects from the database to delete
4. TRUNCATE-remove all records from a table, including all spaces allocated for the records are removed
Truncate table [Table Name].
The following describes the methods and principles of the Truncate statement in MSSQLServer2000:
The Truncate table name is fast and efficient because:
The truncate table function is the same as the DELETE statement without the WHERE clause: both DELETE all rows in the TABLE. However, truncate table is faster than DELETE and uses less system and transaction log resources.
The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.
Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.
For tables referenced by the foreign key constraint, the truncate table cannot be used, but the DELETE statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.
The truncate table cannot be used in the index view.
5. COMMENT-add comments to the data dictionary Annotation
6. GRANT-gives user's access privileges to database authorization
7. REVOKE-withdraw access privileges given with the GRANT command to REVOKE the granted Permissions

2. DML is Data Manipulation Language statements. Some examples: Data operation Language. operations such as Data processing in SQL are collectively referred to as Data operation languages.

1. SELECT-retrieve data from the database Query
2. Add INSERT-insert data into a table
3. UPDATE-updates existing data within a table UPDATE
4. DELETE-deletes all records from a table, the space for the records remain DELETE
5. CALL-call a PL/SQL or Java subprogram
6. explain plan-explain access path to data
Each SQL statement executed by Oracle RDBMS must be evaluated by the Oracle optimizer. Therefore, it is helpful to know how the optimizer selects (Search) paths and how indexes are used. Explain can be used to quickly and conveniently find out how the query data in a given SQL statement is obtained, that is, the search Path (which is usually called the Access Path ). So that we can select the optimal query method to achieve the maximum optimization effect.
7. lock table-control concurrency LOCK, used to control concurrency

3. DCL is Data Control Language statements. some examples: the data control language, used to grant or revoke a certain privilege to access the database, control the time and Effect of database operation transactions, and monitor the database.

1. COMMIT-save work done submit
2. SAVEPOINT-identify a point in a transaction to which you can later roll back save point
3. ROLLBACK-restore database to original since the last COMMIT ROLLBACK
4. set transaction-Change transaction options like what rollback segment to use to SET the features of the current TRANSACTION, which has no impact on subsequent transactions.

DDL, DML, and DCL(Zhang Qingshan)

First, a diagram is given:

1. DDL

1-1. DDL Overview
DDL (Data Definition Language) is used to operate on attributes of objects and objects. Such objects include the database itself and database objects, such as tables and views, DDL manages and defines these objects and attributes as shown in Create, Drop, and Alter. Note: The definition of "object" in DDL operations. "object" includes attributes of objects and objects, and the minimum value of an object is greater than that of a record. For example, Create a data table. Alter can change the field of the table. Drop can delete the table. Here we can see the height of the DDL site, it does not operate on specific data.

1-2. DDL statements (Operations)
Create statement: You can Create database and database objects.
Drop statement: You can delete data tables, indexes, triggers, condition constraints, and permissions of data tables.
Alter statement: Modify the definition and attributes of a data table.
1-3. DDL operation object (table)
1-3-1. Table Concept
Table creation is used to store data. Because we cannot store the data, we need to define some data types for convenient management.
1-3-2. Table attributes
Primary Key Attribute: The primary key is the primary key constraint, but the names of the primary keys are different. The names of the primary keys tend to be virtual (that is, descriptions of this issue ), the name of the primary key constraint tends to be real (that is, the implementation of the description operation). It describes the same thing. The primary key constraint is an attribute in the table. A table can have at most one primary key; A primary key can be defined in one or more fields. A primary key makes the values of one or more fields unique and not empty, in this way, you can use this field or the value in this field to uniquely represent a record.
Unique attribute: A table can have only one primary key attribute. A unique constraint is proposed for the users of a square table. A unique constraint can be defined on one or more fields; the unique constraint makes this field or the value in this group of fields unique. It can be blank, but it cannot be repeated.
Foreign key attribute: it is also called a foreign key constraint. It has the same relationship with the primary key and primary key constraints. The foreign key constraint applies to two tables, if the primary keyword of Table A is A field in Table B, this field is called the foreign key of Table B. Table A is called the primary table and table B is called the slave table. Note that, the computer must know this relationship.

Check, Null, and default attributes: Check attributes are also called verification constraints. Null attributes are also called Null constraints, and default attributes are also called default constraints. These names are a description, describe a situation. We can do this or this situation as intended (only pay attention to the input data), but their intention is to achieve automation, that is, let the computer do this.
(Do you know why indexes are automatically created when primary keys and unique constraints are created? It is also a unique index. Think about the fields used by the index and the function of the index. Operations such as primary key constraint, unique constraint, non-empty constraint, foreign key constraint, verification constraint, and default constraint make the table have some features, so here I think they are all table attributes .)

2. DML

2-1. DML Overview
DML (Data Manipulation Language) is used to manipulate Data contained in database objects, that is, the operation unit is record.
2-2. Main DML statements (Operations)
Insert statement: Insert a record into a data table.
Delete statement: Delete one or more records in a data table. You can also Delete all records in a data table. However, its operation object is still a record.
Update statement: used to modify the content of records in an existing table.
2-3. DML operation object-record
2-3-1. Note
When performing Insert, Delete, and Update operations on records, you must be aware of some DDL operations.

3. DCL

3-1. DCL Overview
DCL (Data Control Language Data Control statement) operations are the permissions of database objects. The determination of these operations makes Data more secure.
3-2. Main DCL statements (Operations)
Grant statement: allows the Object Creator to Grant certain permissions to a user or a group or all users (PUBLIC.
Revoke statement: revokes the access permission of a user, a group, or all users.
3-3. DCL operation object (User)
At this time, the user refers to the database user.