Oracle----Data Operations

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.