Oracle Learning (ii) Basic operation of the table +merge+truncate

(1) The basic syntax for creating a table is as follows:

CREATE TABLE TableName (columnName dataType [default Expression][column columnstraint],... N) 

[tablespace Tablespacename]

(2) The basic syntax for modifying a table is as follows:

ALTER TABLE TableName  
[Add (columnName dataType [default Expression][column columnstraint],... N)]--Add Columns  
[ Modify (ColumnName [Datatype][default expression][columnstraint],... N)]--Modify Column  
[drop Drop_clause]--delete column or constraint drop Column ColumnName

3 Delete The syntax of the table: drop table tablename.

(4) when inserting a record into a table, the value must be null if the value to insert a column is blank, and if the column value is specified as the default value for that column, use default.

(5) The merge statement, which enables you to update or insert a table. The syntax format is as follows:

Merge into TableName using TableName on (join_condition) when matched then update set ...  
    When not matched then insert (...) VALUES (...)

The meaning of this statement is to merge the using table into the into table. The merge condition is on (condition), which only updates the corresponding record in the into table when the condition is met, and when the condition is not satisfied, it can only add the corresponding data to the into table, and the data is only The data that corresponds to the current record in the using table can be used.

Examples are as follows:

Assuming there is a student table, the following statement enables you to add 2 to the age of all student when A's ID is greater than the ID of B, otherwise a record will be added.

Merge into student a using student B in (a.id>b.id) when matched then update set 

age=age+2 when not matched then ins ERT (id,name,age,sex,no)   
values (b.id+100,b.name,b.age,b.sex,b.no);

(6) Delete and truncate the records of the table.

The syntax format for delete is as follows:

Delete from TableName [Where condition]

The statement means to delete records in the TableName table that meet the condition criteria, and to delete all records in the table when condition is omitted.

The syntax format for truncate is as follows:

TRUNCATE TABLE TableName

This statement means that all the records in the TableName table are deleted and the truncate can be used to free up the occupied block tablespace. Truncate deletes cannot be rolled back, and delete deletes can be rolled back. This is why using truncate to delete all records is faster than deleting all records with delete.

See more highlights of this column: http://www.bianceng.cn/database/Oracle/