Oracle Day05 collection and data processing

1. Collection

--Set operation: set, intersection, Difference .

Select Deptno,job,sum (SAL) from the EMP Group by Deptno,job
Union
Select Deptno,to_char (null), SUM (SAL) from the EMP Group by Deptno
Union
Select To_number (null), TO_CHAR (null), SUM (SAL) from EMP;

Select Deptno,job,sal

From EMP

GROUP BY rollup (deptno,job,sal);

Both of these expressions are identical.

--Things to be aware of in a collection operation:

1). All columns of the collection should have the same number and type.

2). The result of the collection operation is the table header of the first SQL statement.

3). Set the operation as sparingly as possible .

2. Data Processing

　　Type of SQL:
1). DML : Data Manipulation Language Insert/update/delete/select
2). DDL : data definition language create/alter/drop/truncate
3). DCL : Data Control Language Grant (AUTHORIZATION) Revoke (remove authorization)

--Insert adds a row of records to the table

(1) Some data in and out of a row

INSERT into EMP (empno,job,sal) VALUES (' Sal ', 8000);

(2) inserting all the data in a row in the order of the table

INSERT into EMP values (' Zhangsan ', ' MANAGER ', 2016, ' 1 August-March-16 ', 5000,3000,20);

--Format of custom dates

Alter session Set Nls_date_format = ' Yyyy-mm-dd ';

--Create a table

--Create a table quickly with a template

CREATE TABLE Testemp as SELECT * from emp where 1 = 2;

--Fast insertion of data

INSERT INTO testemp SELECT * from EMP; --Do not add keywords when working in bulk

--Create a table with no templates

CREATE TABLE TESTEMP1 (
ID Number (5),
Name varchar (20),
Weight number (5,2)
);

-- both varchar and VARCHAR2 can be used to represent strings, but it is recommended to use VARCHAR2 in Oracle. 　　VarChar is that each field is fixed to occupy N bytes, regardless of whether the content is n length or not, VARCHAR2 will adjust his length according to the content.

--Delete data

Delete from emp where empno=2016 or empno=2015;

Rollback: Rollback, if you delete the error, you can use rollback to roll back data.

You can create a rollback node: SavePoint A;

And then roll back: rollback to A;

-DML can be flashed back, DDL cannot.

--formatted table truncate

TRUNCATE TABLE testemp;

What is the difference between delete and truncate deleting data?
(1). Delete is a line-by-row deletion, truncate is the first to delete the table and recreate a new table
(2). Delete can flash back (flashback), while truncate cannot flash back
(3). Delete Does not free up memory space, and truncate will
(4). Delete will produce fragmentation, and truncate will not
(5). Delete is a DML statement, and TRUNCATE is a DDL statement

(6). Delete Does not actually delete the data, it moves all the data into a space called undo tablespace.

--Drop operation

drop table testemp; Delete Table Testemp

--Things: A series of DML, formed by an atomic operation

When do things start:
(1). Display Open: Start transaction
(2). Implicit open: The first DML statement executed
When do things close:
(1). Show off: commit,rollback;
(2). Implicit shutdown
>> normal shutdown: When a DDL operation is performed
>> Abnormal shutdown: Power off the net ....

　　　　　　

　　　　　　　　

　　　　　　

Oracle Day05 collection and data processing