Oracl commonly used data manipulation statements, Oracl command Daquan

Oracle data manipulation and control language 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	Use
INSERT	Add a row to a table
UPDATE	Update data stored in a table
DELETE	Delete Row
SELECT for UPDATE	Prevents other users from accessing the rows that the DML statement is processing.
LOCK TABLE	Prevent other users from using DML statements in tables

　　Inserting data

Insert statements are often used to insert rows into a table, there can be 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 ');

　　Update data
The update command is used to modify the data in the table.

UPDATE Order_rollup SET (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 ')

　　Delete data

The DELETE statement is used to delete one or more rows of data from a table, which contains two statements:

1, keyword Delete from followed by the name of the table from which you want to delete data.

2, where followed by the deletion condition

DELETE from Po_lines WHERE ship_to_state in (' TX ', ' NY ', ' IL ') and Order_date<trunc (SYSTEM) -90< td>

　　Empty table

If you want to delete all the data in the table and clear the table, consider using the TRUNCATE statement in the DDL 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.

TRUNCATE table (Schema) Table DROP (reuse) STORAGE

The

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. The following are the differences between

Truncate and delete

1, truncate on various tables both large and small are very fast. If a rollback command delete is revoked, the truncate is not revoked.

2, TRUNCATE