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

Today, I would like to talk to you about the difference and understanding of DDL, DML and DCL, which may not be understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can gain something according to this article.

The difference between DML, DDL and DCL.

General explanation: DML (data manipulation language): they are SELECT, UPDATE, INSERT, DELETE, just like its name, these four commands are the language DDL (data definition language) used to manipulate the data in the database: DDL is more than DML, and the main commands are CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the structure of tables, data types, links and constraints between tables and other initialization work Most of them use DCL (Data Control Language): a database control function when creating tables. 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

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 deletion

4.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.

5.COMMENT-add comments to the data dictionary comment

6.GRANT-gives user's access privileges to database authorization

7.REVOKE-withdraw access privileges given with the GRANT command reclaims 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.

1.SELECT-retrieve data from the a database query

2.INSERT-insert data into a table add

3.UPDATE-updates existing data within a table update

4.DELETE-deletes all records from a table, the space for the records remain deletion

5.CALL-call a PL/SQL or Java subprogram

6.EXPLAIN PLAN-explain access path to data Oracle RDBMS executes every SQL statement that 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.

7.LOCK TABLE-control concurrency lock, used to control concurrency. 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.

1.COMMIT-save work done submission

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 sets the characteristics of the current transaction, which has no effect on subsequent transactions.

Understanding of DDL, DML and DCL

1 、 DDL

1-1. Overview of DDL DDL (Data Definition Language data definition language) is used to manipulate the properties of objects and objects, including the database itself, as well as database objects, such as tables, views, and so on. DDL's management and definition of these objects and attributes are embodied in Create, Drop and Alter. Special note: the concept of "object" in DDL operation. "object" includes the object and its properties, and the object is at least a larger hierarchy than the record. Take the table as an example: Create creates a data table, Alter can change the fields of the table, and Drop can delete the table. From here, we can see the height of the DDL station, he will not operate on the specific data.

1-2, DDL main statement (operation) Create statement: you can create the database and some objects of the database. Drop statement: you can delete data tables, indexes, triggers, conditional constraints, and permissions for data tables. Alter statement: modify the definition and properties of the data table.

1-3. Operands of DDL (table)

1-3-1, the creation of the concept table of the table is used to store data, because the data we store is impassable, so we need to define some data types to facilitate management.

1-3-2, the primary key attribute of the table: the primary key is the primary key constraint, but the name of the primary key is different, the name of the primary key tends to be virtual (that is, to describe the matter), and the name of the primary key constraint tends to be real (that is, to describe the implementation of the operation), describing the same thing, the primary key constraint is an attribute in the table; there can be at most one primary key in a table. A primary key can be defined in one or more fields; the primary key makes the values of one or more fields unique and not empty, which can uniquely represent a record through the values in the field or group of fields. Unique attribute: there can be only one primary key attribute in a table, a unique constraint is proposed for the square table user, a unique constraint can be defined on one or more fields, and the unique constraint makes the value in the field or group of fields unique and can be empty, but can not be repeated. Foreign key attributes: also known as foreign keys, also known as foreign key constraints, the relationship between primary keys and primary key constraints is the same; foreign key constraints for the two tables, if the primary key of Table An is the field in Table B, then the field is called the foreign key of Table B, Table An is called the master table, Table B is called the slave table, but it must be noted that the computer must know that you are in this relationship. Audit, Null and default attributes: audit properties are also called audit constraints, Null properties are also called Null constraints, and default attributes are also called default constraints; these names describe a thing, a situation, of course, we can do it artificially (just pay attention to entering data), but their intention is to automate, that is, to let the computer do it. Do you know why indexes are created automatically when primary keys and unique constraints are created? And it is a unique index. Think about the fields in which the index is mostly used, and the role of the index. Operations such as primary key constraints, unique constraints, non-empty constraints, foreign key constraints, audit constraints, and default constraints all make the table have certain characteristics, so here I think they are all table properties. )

2 、 DML

2-1. Overview of DML DML (Data Manipulation Language data manipulation language) is used to manipulate the data contained in database objects, that is, the unit of operation is a record.

2-2, the main statement of DML (operation) Insert statement: insert a record into the datasheet. Delete statement: delete one or more records in the data table, or you can delete all records in the data table, but it still operates on records. Update statement: used to modify the contents of records that already exist in the table.

2-3. The operating object of DML-record

2-3-1. Note that when we perform Insert, Delete and Update operations on records, we must pay attention to and be aware of some of the operations done by DDL.

3 、 DCL

3-1. Overview of DCL the operation of DCL (Data Control Language data Control statement) is the permission of database objects, and the determination of these operations makes the data more secure.

3-2, the main statement of DCL (operation) Grant statement: allows the creator of an object to give certain permissions to a user or a group or all users (PUBLIC). Revoke statement: you can revoke a user or a group or all user access rights

3-3. The operating object (user) of DCL the user at this time refers to the database user.

After reading the above, do you have any further understanding of the difference and understanding of DDL, DML and DCL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.