DDL, DML, and DCL differences and understanding

This article focuses on the differences and understandings of DDL, DML, and DCL, and the need for friends to refer to the following

DML, DDL, DCL differences.

General Explanation:

DML (Data Manipulation language):
They are select, UPDATE, INSERT, DELETE, just like its name, these 4 commands are the languages used to manipulate the data in the database
DDL (data definition Language):
DDL is more than DML, the main command has create, alter, DROP, etc., DDL is mainly used in the definition or Change tables (table) of the structure, data types, links between tables and constraints such as initialization work, most of them in the creation of tables using
DCL (Data Control Language):
is the database control function. is a statement that is used to set or change permissions for a database user or role, including (Grant,deny,revoke, etc.) statements. By default, only people such as Sysadmin,dbcreator,db_owner or db_securityadmin have the power to execute the DCL

Detailed Explanation:

First, DDL is Data Definition Language statements. Some Examples: Data definition language for defining and managing the language of all objects in a SQL database
1.create-to create objects in the database
2.alter-alters the structure of the database modified
3.drop-delete objects from the database delete
4.truncate-remove all records from a table, including all spaces allocated for the records is removed
TRUNCATE table [table Name].
The following is a description of the usage and rationale of the TRUNCATE statement in MSSQLServer2000:
The Truncate table name is 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 less system and transaction log resources.
The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only 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 unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.
For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.
TRUNCATE table cannot be used for tables that participate in an indexed view.
5.comment-add comments to the data dictionary notes
6.grant-gives user ' s access privileges to database authorization
7.revoke-withdraw access privileges given with the grant command to reclaim permissions that have been granted

Two, DML is Data manipulation Language statements. Some Examples: Data manipulation language, SQL processing of data and other operations referred to as 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 deleted
5.call-call a PL/SQL or Java Subprogram
6.EXPLAIN plan-explain access Path to data
Each SQL statement executed by an Oracle RDBMS must be evaluated by the Oracle Optimizer. So, understanding how the optimizer chooses (search) the path and how the index is used is a 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 (which we commonly call access path). So that we choose the best query mode to achieve maximum optimization effect.
7.LOCK Table-control concurrency Lock for control of concurrency

Third, DCL is Data Control Language statements. Some Examples: A Data Control language used to grant or reclaim some kind of privilege to access a database, and to control the time and effect of database manipulation transactions, to monitor the database, etc.

1.commit-save work done Submit
2.savepoint-identify a point in a transaction to which you can later roll back SavePoint
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(Zhangqingshan)

Give a diagram first:

1. DDL

1-1. Overview of DDL
The DDL (data definition Language) is used to manipulate the properties of objects and objects, such as the database itself, as well as database objects, such as tables, views, and so on, and the DDL's management and definition of these objects and properties is represented in the Create, Drop and alter on. Special note: The concept of "object" for DDL operations, "Objects" includes the properties of objects and objects, and the smallest objects are more hierarchical than records. For example: Create creates a data table, alter can change the field of the table, 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: Some objects of the database and database can be created.
Drop statement: You can delete data tables, indexes, triggers, conditional constraints, and permissions for data tables.
Alter statement: Modifies the data table definition and properties.
1-3. DDL Operations Objects (table)
The concept of 1-3-1 and table
The creation of the table is used for storing data, because the data we stored is not available, so we need to define some data types to facilitate management.
1-3-2, Table Properties
Primary KEY properties: The primary key is the PRIMARY KEY constraint, but the name of the primary key is different from the virtual (that is, the description of the event), the PRIMARY KEY constraint name is biased to real (that is, the implementation of the description operation), the same thing is described, the PRIMARY KEY constraint is a property in a table, can have a maximum of one primary key A primary key can be defined in one or more fields; A primary key causes one or more fields to be unique and not NULL, so that a record can be represented by the field or by the value in that group field.
Unique attribute: Only one primary key attribute is allowed in a table, and a unique constraint is proposed for the user of the party table; a unique constraint can be defined on one or more fields; a unique constraint makes the field or the value in that Group field unique and nullable, but cannot be duplicated.
Foreign key attribute: Also called foreign key, also called FOREIGN KEY constraint, the relationship between the primary key and the PRIMARY KEY constraint is the same, the foreign KEY constraint is for the two tables, and if Table A's primary key is a field in table B, then the field is called Table B's foreign key, table A is called the primary table, table B is called from the table, but be aware that you must be aware of this relationship.

Verification, NULL, and Default properties: The verification attribute is also called the verification constraint, the null attribute is also called the null constraint, and the default attribute is also called the default constraint; These names describe one thing, describe a situation, this thing or this case we can certainly do it deliberately (input data is the attention on the line), but, Their intention was to automate, that is, to have the computer do it.
(Do you know why indexes are automatically created when you create a primary key and a unique constraint?) and is the only index, think of the index is mostly used in those fields, as well as the role of the index will know. Such operations as primary KEY constraints, unique constraints, non-null constraints, FOREIGN KEY constraints, verification constraints, and default constraints are all attributes of the table, so here I think they are all properties of the table. ）

2. DML

2-1. Overview of DML
DML (data manipulation Language) is used to manipulate the data contained in database objects, that is, the units of operations are records.
2-2. DML main statement (operation)
Insert statement: Inserts a record into the data sheet.
Delete statement: Deletes one or more records from a data table, or deletes all records in the datasheet, but its operands are still records.
UPDATE statement: Used to modify the contents of a record in an existing table.
2-3. DML operation Object--record
2-3-1, attention
When we insert, delete, and update records, it's important to note that some of the DDL's operations are clear.

3. DCL

3-1, the DCL overview
The action of the DCL (Data Control Language) is the permission of the database object, and the determination of these operations makes the data more secure.
3-2. Main statement of DCL (operation)
Grant statement: Allows the creator of an object to give certain permissions to a user or to a group or all users (public).
Revoke statement: You can revoke access rights for a user or group or all users
3-3. DCL Operation Object (user)
The user at this point refers to the database user.

DDL, DML, and DCL differences and understanding