Differences in DML DDL DML commands in SQLite [goto]

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 languages, data manipulation in SQL, etc.

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:

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 What do rollback segment to use

Sets the characteristics of the current transaction, which has no effect on subsequent transactions

Commits that are indirectly completed with SQL commands are implicitly committed. These commands are: Alter,audit,comment,connect,create,disconnect,drop,exit,grant,noaudit,quit,revoke,rename.

DCL is not a transaction, that is, the operation can not be rolled back, the transaction is mainly for the DML statement. The DML statement's modification of the data requires a commit to take effect, and if rollback, your modifications will be rolled back. If there is neither commit nor rollback, the DML is in the middle State and the lock involved is not released. For Sqlplus, by default, commit,dml that are not displayed are not committed. Another situation is that the previously executed DML is not manually committed, exiting the SQL interface or replacing the user, will be automatically submitted, but directly closed, point to the upper right corner (fork)

Differences in DML DDL DML commands in SQLite [goto]