Insert Null value
Execute the following query:
INSERT into EMP (empno,ename,job,sal) VALUES (1005, ' Yang Hua ', ' clerk ', NULL);
copy data: This form can insert more than one row of data at a time.
Step 1: Create a new Table Manager:
CREATE TABLE Manager as SELECT empno,ename,sal from emp WHERE job= ' manager ';
Step 2: Copy data from the EMP table to the manager:
INSERT into Managerselectempno, ename, salfrom empwherejob = ' clerk ';
Step 3: Query results:
SELECT * from MANAGER;
using sequences
Step 1: Create a sequence ABC starting from 2000 with an increment of 1:
<span style= "White-space:pre" ></span>create SEQUENCE ABC INCREMENT by 1 START with < Span style= "White-space:pre" ></span>maxvalue 99999 CYCLE NOCACHE;
Step 2: Use the sequence in the INSERT statement, the name of the sequence is ABC:
<span style= "White-space:pre" ></span>insert into manager VALUES (Abc.nextval, ' Xiao Wang ', 2500); <span style= " White-space:pre "></span>insert into manager VALUES (abc.nextval, ' Xiao Zhao ', 2800);
Step 3: Use the SELECT statement to observe the results:
<span style= "White-space:pre" ></span>select empno,ename,sal from EMP;
Description: Step 1 creates the sequence, step 2 uses the sequence to populate the employee number when inserting, and uses Abc.nextval to get the next value in the sequence.
modifying data
The salary for modifying Xiao Li (numbered 1000) is 3000.
Execute the following query:
<span style= "White-space:pre" ></span>update emp SET sal = WHERE empno = 1000;
Change the employment date of Xiao Li (numbered 1000) to the current system date, and the department number to 50.
Execute the following query:
<span style= "White-space:pre" ></span>update emp<span style= "White-space:pre" ></span> SET hiredate=sysdate, Deptno=50<span style= "White-space:pre" ></span>where empno = 1000;
Another use of the UPDATE statement:
Modifies data based on other tables.
<span style= "White-space:pre" ></span>update managerset (ename, sal) = (select Ename,sal from emp WHERE< C9/>empno = 7788) WHERE empno = 1000;
Delete Data
Delete the newly inserted employee with employee number 1000.
<span style= "White-space:pre" ></span>delete from emp WHERE empno=1000;
Remove the contents of the manager table completely.
<span style= "White-space:pre" ></span>truncate TABLE Manager;
Deletion of the Delete command can be undone, but the deletion of the truncate command is irrevocable.
Note: The TRUNCATE table command is used to delete all of the table's data instead of deleting the table, which still exists
Basic additions and deletions, no in-depth research to performance
Oracle----Data Operations