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.