The difference and understanding of DDL, DML and DCL _mssql

DML, DDL, DCL differences.

General Explanation:

DML (Data Manipulation language):
They are select, UPDATE, INSERT, DELETE, just like its name, and 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 commands are create, ALTER, drop, etc., the DDL is mainly used to define or change the structure of tables (table), data type, table links and constraints, such as initialization work, they are mostly in the creation of tables using
DCL (Data control Language):
is the database control function. is a statement that sets or changes permissions for a database user or role, including (Grant,deny,revoke, and so on) statements. In the default state, only people like Sysadmin,dbcreator,db_owner or db_securityadmin have the power to execute DCL

Detailed Explanation:

One, the DDL is Data Definition Language statements. Some Examples: Data definition language, the language used to define and manage all objects in the 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 are
TRUNCATE table [table Name].
The following is a description of the usage and principle of the TRUNCATE statement in MSSQLServer2000:
Truncate table name is faster and more 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 pages used to store the table data, and only records the release of the page in the transaction log.
TRUNCATE table deletes all rows in the tables, but the table structure and its columns, constraints, indexes, and so on remain unchanged. The count value used for the new line identification is reset to the seed of the column. If you want to keep 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, but you should use a DELETE statement without a WHERE clause. Because the TRUNCATE TABLE is not logged in the log, 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 to reclaim permissions that have been granted

Second, the DML is Data manipulation Language statements. Some Examples: Data manipulation language, SQL processing data and other operations collectively referred to as the 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 spaces for the records remain delete
5.call-call a pl/sql or Java subprogram
6.EXPLAIN plan-explain access Path to data
Oracle RDBMS executes every SQL statement and must be evaluated by the Oracle Optimizer. So, understanding how the optimizer selects (searching) the path and how the index is used is a great help for optimizing SQL statements. Explain can be used to quickly and easily find out how the query data in a given SQL statement gets the search path (which we often call access path). So we choose the best query method to achieve maximum optimization effect.
7.LOCK Table-control concurrency lock for concurrency control

Three, DCL is Data control Language statements. Some Examples: Data Control language, which is used to grant or recycle certain privileges to access a database, and to control the time and effect of a database manipulation transaction and monitor the database.

1.commit-save work done Submit
2.savepoint-identify a point in a transaction to which your 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 attributes of the current transaction, which has no effect on subsequent transactions.

the understanding of DDL, DML and DCL(Zhangqingshan)

First give a diagram:

1. DDL

1-1. Overview of DDL
The DDL (data definition Language) is used to manipulate properties of objects and objects, including 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 attributes are embodied in the Create, Drop and Alter. Special note: The concept of "object" of DDL operation, "Object" includes object and object's attribute, and object is smallest than record large level. For example: Create a datasheet, alter can change the table's fields, drop can delete the table, from here we can see that the height of the DDL station, he does not operate on the specific data.

1-2, the main statement of the DDL (operation)
Create statement: Some objects that can create databases and databases.
Drop statement: You can delete data tables, indexes, triggers, conditional constraints, and permissions on data tables.
Alter statement: Modifies data table definitions and properties.
1-3, the operation of DDL Objects (table)
The concept of 1-3-1 and table
Table creation is used to store data, because we are storing data, so we need to define some data types to facilitate management.
1-3-2, Table Properties
Primary KEY properties: Primary key is the PRIMARY KEY constraint, but the name is different, the primary key of the naming bias to virtual (that is, description of the matter), the PRIMARY KEY constraint name is biased towards reality (that is, describing the implementation of the operation), describing the same thing, and the primary KEY constraint is a property in the table; there can be a maximum of one primary key in a table A primary key can be defined in one or more fields; A primary key makes the value of one or more fields must be unique and not empty, so that a record can be represented uniquely by the field or by the value in that group field.
Unique attribute: A table can have only one primary key attribute, a unique constraint for the table user; 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 can be empty, but it cannot be repeated.
Foreign key properties: Also called foreign key, also called foreign key constraints, with the primary key and primary KEY constraints are the same relationship; a foreign key constraint is for two tables, if Table A's primary key is a field in table B, the field called Table B's foreign key, table A is called the primary table, and table B is called from the table, but note that you must be aware that you are in this relationship.

Verification, NULL, and Default properties: Verification attributes are also called verification constraints, NULL attributes are also called NULL constraints, and default properties are called default constraints; These names describe one thing, describe a situation, or we can certainly do it artificially (input data is the attention on the line), but, Their intention is to automate, which is to let computers do this.
(Do you know why the index is automatically created when you create a primary key and a unique constraint?) And it's the only index, think about how the indexes are mostly used on those fields, and the role of the index. Such operations as primary KEY constraints, UNIQUE constraints, non-null constraints, foreign KEY constraints, verification constraints, and default constraints make the table have certain attributes, so I think they are all properties of the table here. ）

2. DML

2-1. DML overview
DML (data manipulation Language) is used to manipulate data contained in a database object, that is, the unit of operation is a record.
2-2, the main statement of DML (operation)
Insert statement: Inserts a record into the datasheet.
Delete statement: Deletes one or more records in a datasheet, or deletes all records in a datasheet, but its action object is still a record.
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 operations on records, be sure to be aware of some of the DDL's operations on them.

3, DCL

       3-1, overview of DCL
          The operations of the        DCL (Data Control Language) are the permissions of database objects, which make the data more secure.
       3-2, DCL's main statement (operations)
                 GRANT statement: Allows the creator of an object to give certain permissions to a user or to a group or to all users (public).
                Revoke statement: Can revoke a user or a group or all user access rights
       3-3, DCL Action Object (user)
    The user at              is referring to the database user. &NBSP