Oracle Series: (19) Increased data erasure

Get ready

Col empno for 9999;col ename for A10;col job for A10;col Mgr for 9999;col hiredate for A12;col sal for 9999;col Comm for 9 999;col Deptno for 99;col tname for A40;set pagesize 80;

--Create a new table xxx_emp, copy the structure in the EMP table, and copy all the data from the EMP table to create TABLE Xxx_empas select * from EMP;

Review of four categories of sql92/99 standards

(1) DML (Data Manipulation language): Select,insert,update,delete

(2) DDL (data definition Language): Create Table,alter table,drop table,truncate table

(3) DCL (Data Control Language): Grant Select any table to Scott/revoke Select any table from Scott

(4) TCL (Transaction Control Language): Commit,rollback,savepoint to rollback point

Insert a record into the EMP table (in one: Default structure order by table) insert into table name values ... Grammar

INSERT into EMP values (1111, ' JACK ', ' IT ', 7788,sysdate,1000,100,40);

Insert a record into the EMP table (way two: in a custom order) insert into table name (column name) values ... Grammar

INSERT into EMP (ENAME,EMPNO,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (' MARRY ', 2222, ' IT ', 7788,sysdate,1000,100,40);

Inserting null values into the EMP table (mode one: inserting null values with display)

INSERT into EMP values (3333, ' SISI ', ' IT ', 7788,sysdate,1000,null,40);

Inserts a null value into the EMP table (mode two: implicitly inserting a null value), provided that the inserted field allows null values to be inserted

INSERT into EMP (ENAME,EMPNO,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (' SOSO ', 4444, ' IT ', 7788,sysdate,1000,40);

The& placeholder applies To insert using the & placeholder, the dynamic input value,& can be used in any DML statement, in the values clause , for example: ' &ename ' and & Sal

INSERT into EMP values (&empno, ' &ename ', ' &job ', &mgr,&hiredate,&sal,&comm,&xxxxxxxx );

Note: The& is a placeholder provided by the Sqlplus tool, and if it is a string or date type, the numeric type does not need to be added '

The& placeholder applies to the table name of select using the & placeholder, the dynamic input value,& can be used in any DML statement, in the FROM clause

SELECT * FROM &table;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/87/15/wKiom1fTJkiiJl16AABKA85Xawo220.png "title=" 001. PNG "alt=" Wkiom1ftjkiijl16aabka85xawo220.png "/>

The& placeholder applies to the column name of a select using the & placeholder, the dynamic input value,& can be applied in any DML statement, in the SELECT clause, using the

Select Empno,ename,&colname from EMP;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/87/13/wKioL1fTJqyjilFYAAAqBgEzwl8795.png "title=" 002. PNG "alt=" Wkiol1ftjqyjilfyaaaqbgezwl8795.png "/>

The& placeholder applies to whereusing the & placeholder, the dynamic input value,& can be applied in any DML statement, used in the WHERE clause

SELECT * from emp where sal > &money;

& placeholders apply to group by andhave use & placeholders, dynamic input values,& can be used in any DML statement, in the group BY and HAVING clauses

Select Deptno,avg (SAL) from Empgroup by &deptnohaving avg (SAL) > &money;

Delete all records in the EMP table

Delete from EMP;

Copy the employees from all 20th departments in the Xxx_emp table to the EMP table, insert in bulk, and insert into table name Select ... Grammar

INSERT INTO Empselect * from Xxx_empwhere deptno=20;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/87/15/wKiom1fTKJ-Tq6lyAAAyNRJMBp8474.png "title=" 003. PNG "alt=" Wkiom1ftkj-tq6lyaaaynrjmbp8474.png "/>

Increase the salary of ' SMITH ' by 20%

Update emp Set sal=sal*1.2 where ename = Upper (' Smith ');

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/87/13/wKioL1fTKTaRf88HAAA3QKNYH5w214.png "title=" 004. PNG "alt=" Wkiol1ftktarf88haaa3qknyh5w214.png "/>

Set the ' SMITH ' salary to the average wage of unit 20th, which is an unknown condition, giving priority to subqueries

Number one: Average wage in sector 20th

Select AVG (SAL) from EMP where deptno=20;

Second: Set the ' SMITH ' salary to 2207

Update emp Set sal=2207 where ename = ' SMITH ';

Sub-query:

Update emp Set sal = (select AVG (SAL) from EMP where deptno=20) where ename = ' SMITH ';

Remove employees who have a lower average wage than all departments , which is an unknown condition, giving precedence to subqueries

First: Check the average salary for all departments

Select AVG (SAL) from the EMP group by DEPTNO;

Second: Remove employees with a lower pay ratio (*,*,*)

Delete from emp where Sal<all (*,*,*);

Sub-query:

Delete from emp where Sal < All (select AVG (SAL) from EMP Group by DEPTNO);

Remove non-Commission employees

Delete from the EMP where comm is null;

Drop the EMP table into the Recycle Bin, dropping the table name

drop table emp;

Flash the EMP table back from the Recycle Bin, Flashback table name to before drop

Flashback table EMP to before drop;

Query Recycle Bin, show RecycleBin

Show RecycleBin;

Emptying the Recycle Bin, purge RecycleBin

Purge RecycleBin;

Using the keyword purge, completely remove the EMP table, that is, the EMP table will not be dropped into the Recycle Bin, permanently delete the EMP table, drop table name purge

drop table EMP Purge;

Creates the structure of an EMP table based on the XXX_EMP table structure, but does not insert data

CREATE TABLE Empasselect * from Xxx_emp where 1<>1;

CREATE TABLE EMP (empno,ename) Asselect empno,ename from Xxx_emp where 1=2;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/87/15/wKiom1fTLILBuzG4AAATrmoe8aE520.png "title=" 005. PNG "alt=" Wkiom1ftlilbuzg4aaatrmoe8ae520.png "/>

Create an EMP table, copy the structure in the Xxx_emp table, and copy all the data from the Xxx_emp table

CREATE TABLE Empasselect * from xxx_emp where 1=1;

Note: The default is True if the where is not written

Truncates the EMP and automatically creates an EMP table, TRUNCATE TABLE name

TRUNCATE TABLE EMP;

Bulk Insert employee information from the department number 20 in the Xxx_emp table to the EMP table, including only the Empno,ename,job,sal field

INSERT into EMP (empno,ename,job,sal) Select Empno,ename,job,sal from Xxx_emp where deptno=20;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/87/13/wKioL1fTLQfwaUDhAAAkgzYODfc430.png "title=" 006. PNG "alt=" Wkiol1ftlqfwaudhaaakgzyodfc430.png "/>

Bulk INSERT employee information for department number 20 in the Xxx_emp table to the EMP table (containing only the Empno and ename fields)

INSERT into EMP (empno,ename) Select Empno,ename from Xxx_emp where deptno=20;

drop table and truncate table and delete from differences :

drop table 1) belongs to the DDL 2) do not roll back 3) do not take where 4) Table content and structure deletion 5) Fast removal speed

TRUNCATE TABLE 1) belongs to the DDL 2) do not roll back 3) do not take where 4) Table Content deletion 5) Fast removal speed

Delete from 1) belongs to DML 2) can be rolled back 3) can take where 4) Table structure in, the table content depends on where to execute the situation 5) Slow deletion, need to delete line by row

Oracle Series: (19) Increased data erasure