Oracle data manipulation and Control language (i)

oracle| Control | data | detailed
SQL language is divided into four categories: Data Query Language DQL, Data manipulation language DML, data definition language DDL, Data Control Language DCL. It is used to define the structure of the data, such as creating, modifying, or deleting the database; DCL is used to define the permissions of the database user; In this article I will describe in detail how these two languages are used in Oracle.

DML language DML is a subset of SQL that is used primarily to modify data, and the following table lists the DML statements supported by Oracle. Statement Purpose INSERT Add row update to table update data in table Delete row SELECT for update ban Lu Liling  not х dream 蔇 ml statement is processing a row. LOCK table prevents other users from using DML statements in tables

Inserting a data insert statement is often used to insert rows into a table, you can have special data fields in rows, or you can use subqueries to create new rows from existing data.　　The column directory is optional, and the default column's directory is all column names, including comlumn_id,comlumn_id can be found in the data dictionary view all_tab_columns,user_tab_columns, or Dba_tab_columns. The number of data inserted into the row and the data type must match the number of columns and the data type. Data types that do not conform to the column definition implement implicit data conversion for the inserted value. The null string inserts a null value into the appropriate column.　　Keyword NULL is often used to indicate that a column is defined as a 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 '); The Update Data Update command is used 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 ') deletes a data delete statement to delete one or more rows of data from a table, which contains two statements: 1, the keyword delete from followed by the name of the table from which to delete data. 2, where followed by delete condition delete from Po_lineswhere ship_to_state in (' TX ', ' NY ', ' IL ') and order_date empty table if you want to delete all the data in the table, empty the table, consider using the DDL The TRUNCATE statement of the language. Truncate is like a delete command without a WHERE clause. Truncate deletes all rows in the table. Truncate is not a DML statement that is a DDL statement, and he differs from the delete right. The TRUNCATE table (Schema) Table DROP (reuse) STORAGE STORAGE substring is optional and the default is drop STORAGE. When you use drop storage, the table and table indexes are shortened, the table is shrunk to a minimum, and the next parameter is reset.　　Reuse storage does not shorten the table or adjust the next parameter. Truncate and delete have the following differences 1, truncate on a variety of tables, whether large or small are very fast.　　If a rollback command delete is revoked, the truncate is not revoked.　　2, truncate is a DDL language, like all other DDL languages, he will be implicitly submitted, not to truncate use rollback command. 3, TRUNCATE will reset the high-level line and all indexes. When you have a full view of the entire table and index,The table after the truncate operation is much faster than the table after the delete operation.　　4, TRUNCATE cannot trigger any delete trigger.　　5, can not give anyone the right to empty other people's table.　　6, when the table is emptied after the table and table index is reset to the initial size, and delete is not.　　7, can not empty the parent table. The Select for UPDATE select FOR UPDATE statement is used to lock rows and prevent other users from modifying the data on that row.　　When the row is locked, other users can query the row's data with a SELECT statement, but cannot modify or lock the row. Locking a table lock statement is often used to lock an entire table. When a table is locked, most DML languages cannot be used on that table. The lock syntax is as follows: Lock schema table in Lock_mode where Lock_mode has two options: share shared Way Exclusive only example: Lock table intentory in excl　　Usive MODE Deadlock is known as a deadlock when two transactions are locked and each other is waiting for another to be unlocked. When a deadlock occurs, Oracle detects the deadlock condition and returns an exception.