Oracle Database Learning (III)

6. About NULL

Null is an unknown value in the database , there are no values, NVL is used when the operation is performed, but the result is still empty, and Null is returned only if all of the records in the aggregation function are empty.

7.insert Insertion

(1) Single-line record insertion

Insert into tab (F_Z,F_A) VALUES (1,to_date (' 2017-10-11 ', ' yyyy-mm-dd ')).

syntax: syntax INSERT into data table (field name 1, field name 2,......) VALUES (the value of field name 1, the value of field Name 2,......).

Field names and values should correspond, the time date to use single quotation marks, non-empty columns must have a value corresponding.

(2) multi-line record insertion

Insert INTO table1 (f_id, f_m, f_r) Select Table2.nextval as f_id, ' new user ', sysdate from table1 where F_r <= to_date (' 2017-10-01 ', ' yyyy-mm-dd ').

syntax:INSERT INTO data table (field name 1, field name 2,......) (Select (Field name 1 or operation, field name 2 or operation,......) from data table where condition).

The data tables in the subquery and Insert can be the same or different, but the fields that require query results are exactly the same as the field properties in the Insert data table.

8.delete

Delete from tab where f_a >= 5;truncate table table1;truncate deletes the entire table and cannot recover data after deletion, but retains data table structure; Delete delete data can be recovered.

9.update

Direct Assignment Update:1) Syntax: Update tab SET f_a = New1,f_b = New2 ... where condition;

2) Update tab SET f_a = ' new name ' WHERE f_id = 2.

nested update:1) Syntax: Set field name 1= (select field list from data table where condition), field name 2= (select field list from data table where condition),......。

2) Update table1 set table1.f_a= (select Table2.f_b from table2 where table1.f_id=table2.f_id) where table1.f_id=5.

10.merge into

Merge into Table1 using table2 on (table1.f_id=table2.f_id) while matched then update set table1.f_a = ' new ' When not matche D then insert (table1.f_id) VALUES (table2.f_id)

Oracle Database Learning (III)