Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what is the meaning of DDL, DCL and DML in the database?" the content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn "what is the meaning of DDL, DCL and DML in the database?"

DML (Data Manipulation Language):

They are SELECT, UPDATE, INSERT and DELETE, and as its name suggests, these four commands are used to manipulate the data in the database.

DDL (Data Definition Language):

DDL is more than DML, and the main commands are CREATE, ALTER, DROP and so on. DDL is mainly used to define or change the structure, data type, links and constraints of tables (TABLE). Most of them are used when creating tables.

DCL (Data Control Language):

It's a 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 people such as sysadmin,dbcreator,db_owner or db_securityadmin have the right to execute DCL

Detailed explanation:

1. DDL is Data Definition Language statements. Some examples: data definition language, a language for defining and managing all objects in an SQL database

CREATE-to create objects in the database create ALTER-alters the structure of the database modify DROP-delete objects from the database delete TRUNCATE-remove all records from a table, including all spaces allocated for the records are removed

TRUNCATE TABLE [Table Name] .

The following is an explanation of the usage and principle of the Truncate statement in MSSQLServer2000:

Truncate table table names are fast and efficient because:

TRUNCATE TABLE is functionally the same as a DELETE statement without a WHERE clause: both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources.

The DELETE statement deletes one row at a time and records one entry for each row deleted in the transaction log. TRUNCATE TABLE deletes the data by releasing the data pages used to store the table data, and only records the release of the pages in the transaction log.

TRUNCATE TABLE deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on remain the same. The count value used for the new row identity is reset to the seed of the column. If you want to keep the identity count value, use DELETE instead. Use the DROP TABLE statement if you want to delete the table definition and its data.

For tables referenced by FOREIGN KEY constraints, you cannot use TRUNCATE TABLE, but instead use DELETE statements without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate the trigger.

TRUNCATE TABLE cannot be used for tables that participate in indexed views.

COMMENT-add comments to the data dictionary comment GRANT-gives user's access privileges to database authorizes REVOKE-withdraw access privileges given with the GRANT command to take back permissions that have been granted

2. DML is Data Manipulation Language statements. Some examples: data manipulation language. Operations such as processing data in SQL are collectively called data manipulation language.

The SELECT-retrieve data from the a database query INSERT-insert data into a table adds UPDATE-updates existing data within a table to update DELETE-deletes all records from a table, and the space for the records remain removes CALL-call a PL/SQL or Java subprogramEXPLAIN PLAN-explain access path to dataOracle RDBMS to execute each SQL statement, which must be evaluated by the Oracle optimizer. Therefore, understanding how the optimizer selects (search) paths and how indexes are used is of great help in optimizing SQL statements. Explain can be used to quickly and easily find out how the query data in a given SQL statement is obtained, that is, the search path (we often call it Access Path). So that we can choose the best query method to achieve the maximum optimization effect. LOCK TABLE-control concurrency lock for concurrency control

3. DCL is Data Control Language statements. Some examples: a data control language used to grant or reclaim certain privileges to access a database, to control the time and effect of database manipulation transactions, to monitor the database, etc.

COMMIT-save work done commit SAVEPOINT-identify a point ina transaction to which you can later rollback SavePoint ROLLBACK-restore database to original since the last COMMIT rollback SET TRANSACTION-Change transaction options like what rollback segment to use sets the characteristics of the current transaction, which has no effect on subsequent transactions. the above is all the content of the article "what is the meaning of DDL, DCL, and DML in the database?" thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.