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
MERGE: Performs the update action if the row for the specified column value is already in the table. If no record is specified for the column value, an Insert action is performed.
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.
Oracle Merge into Usage summary
There is a table T, there are two fields A, B, we want to do insert/update in table T, if the condition is satisfied, then update the value of B in T, otherwise insert a record in T. In Microsoft's SQL syntax, a simple sentence is OK, and the syntax in SQL Server is as follows:
1 from T where t.a= ' 1001 ')
update t set t.b=2 where t.a= ' 1001 '
else
inserts into T (A,B) Valu Es (' 1001 ',2
In Oracle, to implement the same functionality, the merge into is used to implement (the features introduced by Oracle 9i) with the following syntax:
MERGE into table_name alias1
USING (table|view|sub_query) alias2 in
(join condition) when
matched THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col_val2 when not
matched THEN
Strictly speaking,"in a merge statement that has both INSERT and update syntax, the total number of insert/update records is the number of records ALIAS2 in the using statement." so, to achieve the above function, you can write this:
MERGE into T T1
USING (SELECT ' 1001 ' as A,2 as B to dual) T2 on
(t1.a=t2.a) when
matched then< C13/>update SET t1.b = t2.b when not
matched THEN
INSERT (a,b) VALUES (t2.a,t2.b);
