Oracle Practical Knowledge (III) ____oracle

----TRUNCATE TABLE

TRUNCATE table deletes all the data in the table, the structure of the table does not change, and the data cannot be rolled back. ----Sequence

SEQUENCE: Generate a number, automatically generate a primary key, such as automatically add a number, you can use this, in order to order the data, 1,2,3,4,5 ...

Create sequence: Created SEQUENCE sequence name. CREATE SEQUENCR Id_seq;

Properties of the sequence: Nextval: The next value of the sequence, the value of each call sequence is changed;

Currval: The current value of the sequence, the value of each call sequence does not change;

For example, add a sequence to a piece of data:

INSERT into TEST (id,name) VALUES (id_seq. Nextval, ' SA ');

Executing this statement more than once will reveal that the value of the primary key ID in the test table is incremented sequentially. ----query for all table names in the current user:

SELECT tname from TAB; ----View

How the View works: simplifies SQL statements and improves security. But the view does not improve query efficiency, indexing improves query efficiency.

Creating a View: Create OR REPLACE View V1 as here is a large SELECT statement;

So the select * from V1 function is the same as that of the large list of SELECT statements. ----Inner Connection and outer connection

The inner join can only query the query that satisfies the condition of the connection, and the outer connection may query the query that does not meet the join condition.

The outer joins are divided into left, right and full connections.

Left JOIN connection: SELECT Last_name,department_name

From EMPLOYEES E left [OUTER] joins departments D on E.DEPARTMENT_ID=D.DEPARTMENT_ID;

Right connection: SELECT Last_name,department_name

From EMPLOYEES E right JOIN departments D on E.DEPARTMENT_ID=D.DEPARTMENT_ID;

Full connection: SELECT Last_name,department_name

From EMPLOYEES E full JOIN departments D on E.DEPARTMENT_ID=D.DEPARTMENT_ID;

Unique outer JOIN symbol in Oracle (+)

Left join: SELECT last_name,department_name from EMPLOYEES e,departments D

WHERE e.department_id=d.department_id (+); //Note the left connection is placed on the right side of the equal sign.

Right connection: SELECT last_name,department_name from EMPLOYEES e,departments D

WHERE e.department_id (+) =d.department_id; //Note the left connection is placed on the left side of the equal sign. ----Common aggregate functions (group functions) ----SUM

Sum function: SELECT sum (SALARY) from EMPLOYEES; ----AVG

Find the average function: SELECT avg (SALARY) from EMPLOYEES;

Note: When there is null in the average data, Oracle skips automatically, that is, if there are four data, one of which is null, and the average is divided by 4 instead of 3,

To solve this problem, you can use the NVL function in the first article: SELECT AVG (NVL (salary,0)); ----MIN, MAX

Seek minimum, maximum: SELECT Min[max] (SALARY) from EMPLOYEES; ----COUNT

Find the number of rows returned by the current query: SELECT COUNT (SALARY) from EMPLOYEES; Query for the number of people who are not empty salary.