SQL language is divided into four major categories: data query language DQL, data control language DML, data definition language DDL, data control language DCL. The structure used to define data, such as creating, modifying, or deleting a database; DCL is used to define permissions for database users; in this article, I will tell the use of these two languages in Oracle.
DML language DML is a subset of SQL, mainly used to modify data, which lists the DML statements supported by Oracle.
Statement Use INSERT Add Row Update to Tables Stored in Table Data Delete Delete Row SELECT for Update Forbidden 渌 х 蔇 蔇 ML statement is being processed. LOCK TABLE prohibits other users from using DML statements in the table
Inserting data INSERT statements are often used to insert a row in the table, and there can be special data fields in the line, or you can use subquers to create new rows from existing data. Column directory is optional, the default column directory is all column names, including comlumn_id, and Comlumn_ID can be found in Data Dictionary view all_tab_columns, user_tab_columns, or dba_tab_columns. The quantity of the data inserted and the data type must match the number of columns and data types. The data type that does not meet the column definition will implement implicit data conversion to the insert value. NULL strings Insert a null value into the appropriate column. Keyword NULL is often used to indicate that a column is defined as NULL value. The following two examples are equivalent.
INSERT INTO CUSTOMERS (Cust_ID, State, Post_code) Value ('Ariel', NULL, '94501');
or
INSERT INTO CUSTOMERS (Cust_ID, State, Post_code) Value ('Ariel', '94501');
Update the data update command to modify the data in the table.
Update ORDER_ROLLUPSET (Qty, Price) = (SELECT SUM (QTY), SUM (Price) from Order_Lines Where Customer_ID = 'Kohl'where Cust_Id =' Kohl'and Order_Period = To_Date ('01 -OCT-2000 ')
Deleting the data delete statement is used to delete a row or multi-line data from the table, which contains two statements: 1. Key words delete from follow the deletion of data. 2, WHERE follows the delete condition
Delete from PO_LINESWHERE SHIP_TO_STATE IN ('TX', 'NY', 'IL') AND ORDER_DATE
Cleaning Table If you want to delete all the data in the table, you can take into account the TRUNCATE statement using the DDL language. Truncate is just like a delete command without a WHERE clause. Truncate will delete all rows in the table. TRUNCATE is not a DML statement is a DDL statement, he and DELETE different features.
Truncate Table (Schema) Table Drop (Reuse) Storage
The Storage substring is optional and the default is the Drop Storage. When using the DROP Storage, the table and table index will be shortened, shrink the table to the minimum range, and reset the next parameter. Reuse Storage does not shorten the table or adjust the next parameter. Truncate and Delete have the following differences 1. Truncate is very fast in various forms, whether it is big or small. If there is a rollback command delete will be revoked, and Truncate will not be revoked. 2, Truncate is a DDL language, like all other DDL languages, he will be implicit, and cannot use the rullback command to Truncate. 3, Truncate will reset the high level line and all indexes. When the entire table and index are fully browsed, the TRUNCATE operation is much more faster than the table after the DELETE operation. 4. Truncate cannot trigger any DELETE trigger. 5. You cannot grant any person to empty the table of the table. 6. When the table is emptied, the index of the table and the table is reset to the initial size, while Delete can not. 7, can't empty the parent table. The SELECT FOR UPDATE SELECT FOR UPDATE statement is used to lock rows to prevent other users from modifying data on that line. When the row is locked, other users can query the data of the line with the SELECT statement, but cannot modify or lock the row. Lock table LOCK statements are often used to lock the entire table. Most DML languages cannot be used on this table when the table is locked. The LOCK syntax is as follows: Lock Schema Table in Lock_Mode
Where Lock_Mode has two options: Share Sharing Method Exclusive Unique way:
Lock Table Intentory in Exclusive Mode
Dead lock When two transactions are locked, and they are waiting for another unlock, which is called dead lock. When there is a deadlock, Oracle will detect deadlock conditions and return an exception.
