Oracle ---- Data Operations

Oracle ---- Data Operations
Insert Null Value
Run the following query:

Insert into emp (empno, ename, job, sal) VALUES (1005, 'yang hua', 'cler', null );

Copy Data:Multiple rows of data can be inserted 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;

Use Sequence
Step 1: Create a sequence abc that starts from 2000 and increments to 1:

<span style="white-space:pre"></span>CREATE SEQUENCE abc INCREMENT BY 1  START  WITH  2000 <span style="white-space:pre"></span>MAXVALUE  99999  CYCLE  NOCACHE;

Step 2: Use the sequence in the INSERT statement. The sequence name is abc:

<Span style = "white-space: pre"> </span> insert into manager VALUES (abc. nextval, 'wang ', 2500); <span style = "white-space: pre"> </span> insert into manager VALUES (abc. nextval, 'zhao Xiao, 2800 );

Step 3: Use the SELECT statement to observe the results:

<span style="white-space:pre"></span>SELECT empno,ename,sal FROM emp;

Note: Step 1 creates a sequence. Step 2 uses the sequence to fill in the employee number during insertion, and abc. nextval is used to obtain the next value in the sequence.

Modify data
Change the salary of Xiao Li (number: 1000) to 3000.
Run the following query:

<span style="white-space:pre"></span>UPDATE emp SET    sal = 3000 WHERE empno = 1000;

Change the employment date of Xiao Li (number 1000) to the current system date, and the department number to 50.
Run 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 usage of the UPDATE statement:
Modify data based on other tables.

<span style="white-space:pre"></span>UPDATE  managerSET (ename, sal) =(SELECT ename,sal FROM emp WHERE   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;

Delete the contents of the manager table.

<span style="white-space:pre"></span>TRUNCATE TABLE manager;

The DELETE command can be undone, but the TRUNCATE command cannot be undone.
Note: The truncate table command is used to delete all data in a TABLE instead of deleting a TABLE. The TABLE still exists.

Basic addition, deletion, modification, and query are not thoroughly studied in terms of performance.