Oracle on the road to increase, delete, change, check operation notes ____oracle

Oracle Increase, delete, change, check operation

1. Select
Select distinct *| field | expression as alias from table table alias
select * from table name;  Query All field information for all records in a table
select column name from table name;  query for the specified field information for all records in a table
select column name 1, column name 2 from table name;  Field 1 Field 2 for all records in a table
select distinct column name from table name;  Remove Duplicate records
select expression from table name;  query expression
select xxx as alias from table name table alias  use Alias

2. Insert
insert into table name [(field list)] VALUES (list of values); Add a record
You need to meet the criteria when adding a record
 Type length compatibility: Field compatible values
 value to meet constraints: PRIMARY KEY (unique + non-null) Non-empty (required) unique (not repeated) default (not filled in with default) check (satisfy bar
) foreign key (refer to the value of the primary key column in the main table)
 number must be the same: Specify the column, the number order and the same column;
Not specified, the number of columns and the order of the table structure is the same (null is also occupied, there is no default value)
Add data
Insert into table name values (data  that are consistent with table structure order and number and type can be handwritten or obtained from other tables);
Insert into table name (specify column name) VALUES (and column data of the specified column number, order, type)
eg
Insert into table (specified column) Select query column from source table where filter data;
Insert into table (specified column) values (List of values);
Insert into table name Select query column from source table where filter data;
Insert into table name values (value list);

3.update
Update to modify data
Update table name Set field = value [,....] where filter Row Records;
Requirements:
 Records exist
 Type length compatibility: Field compatible values
 the same number
Change data
To change field values by querying data from an existing table:
Update table name set (field list) = (select field list from source table where to filter source table records) where to update records condition
To manually change field values:
Update table name Set field = value [,....] where filter Row Records;

4. Delete
Delete Deletes data
delete [from] table name where filter Row Records
Description
1, delete the specified part of the record can be deleted, delete all records
2, when there is a primary foreign key association on the record, when the record of the associated primary table is deleted, note that the FOREIGN KEY constraint is not allowed to delete
Remove from table and delete primary table first

The difference between truncating data and deleting data truncate and delete:
1, TRUNCATE–>DDL, do not involve the transaction, you can not roll back
DELETE–>DML, involving transactions, can be rolled back
2, truncate truncate all the data delete can delete all or part of the record
3, truncate from the structure to check whether there is a primary foreign key, if there is, do not let delete
Delete checks that the primary foreign key exists from the record and, if it exists, deletes it by reference to the foreign key constraint.