Collation of SQL statements with Oracle features [Season 1]

We know that every RDBMS has its own characteristics in SQL. So today, let's take a look at what features Oracle has.

Meaning.

Feature 1:
Oracle analysis functions and window functions:
Syntax:

Function_name (<argument>, <argument> ...)
Over (<partition-clause> <order-by-clause> <drawing wing clause>)
For example:
Sum (SAL) over (partition by deptno order by ename rows ...)
Where sum is the function name,
Over () is a keyword. The direct point is to add conditions to the analysis function to identify whether sum () is an aggregate function or an analysis function.
Note:
(1) During statistical analysis, we usually want to select as many original columns and statistical value columns as possible. However, group by must be followed

With more columns, using the analysis function can avoid the trouble that the selected column names must appear in the group by list when using group.

(2) The aggregate function uses group by, and each group returns a statistical value. The analysis function uses partition by grouping, and each group has a row

Returns a statistical value.

(3) The analysis function contains an over () Window Function and three analysis words:
Order by window (rows)

(4) differences between two order by statements:

Analysis functions are performed after the entire SQL query (the execution of SQL statements is special), that is, the SQL statement

Order by also affects the execution result of the analysis function.

A) if the order by clause in the SQL statement meets the sorting requirements of the analysis function

Run the command. The analysis function does not have to be sorted during analysis.

B) if the order by statement in the SQL statement does not meet the sorting required by the analysis function

Will be executed first.

(5) The window is the data range to be processed during function analysis:

The first line is: unbounded preceding

The current row is: current row

The last line is unbounded following.

Window words cannot appear separately. They can only appear when order by clause is available. When the order by clause appears

Window clause. At this time, the window defaults to the first line to the last line;

When the window clause is omitted:

A) if order by exists, the default window is unbounded preceding.
And current row;

B) if order by is omitted at the same time, the default window is unbounded preceding and unbounded.

Following

Example:

1. Which of the following is the highest wage for each department?

Select * from

(

Select ename, Sal, deptno, rank () over (partition by deptno order by Sal DESC) mm from EMP

)

Where Mm = 1

 

Note:
1). row_number () cannot be used when you calculate the first place, because if there are two equal places in the same class,

Row_number () returns only one result.
2) The difference between rank () and dense_rank () is:
-- Rank () is the Skip sorting. When there are two second names, the next is the fourth name.
-- Dense_rank () L is a continuous sorting, with two second names still followed by the third

 
2. display the salaries of employees in each department with the highest salaries of the Department.

Select deptno, empno, ename, Sal, last_value (SAL) over (partition by deptno order by Sal rows

Between unbounded preceding and unbounded following)

Max_sal from EMP;

 
Feature 2:
Flexible use of the decode () function:

Syntax:
Decode (value, if1, then1, if2, then2, if3, then3,..., else) indicates the decode Function

The result returns then1,.... If it is not equal to any if value, else is returned. You can use functions or expressions to replace value, if,

Then, else to make some more useful comparisons.

Let's take a look at the specific application:
1. If we want to increase the salary for Baidu employees, the standard is: The salary below 8000 yuan will increase by 20%; the salary above 8000 yuan

15%
Then:

Select decode (sign (salary-8000), 1, salary * 1.15,-1, salary * 1.2, salary from employee

Table 2 table_subject has the subject_name column. Sort by language, number, and external order
Then:

Select * From table_subject order by deCODE (subject_name, '', 1, 'mat', 2, '', 3)

Feature 3:
Oracle update features:

Let's look at the question first:

There is a table A with the column ID and count; now there is table B, and the column is also the ID count. How can I change the Count corresponding to the ID in Table B?

New to Table?

The not strict solution is as follows:

Update

Set COUNT = count + nvl (select count from B where id = A. ID), 0)

If table A and table B are one-to-many, there is a problem because subqueries are used with operators such as equal signs and can only be single values. Later, subquery

You need to pay special attention to this problem when used with operators: look at the number of values returned by the subquery !!!

When you need to update the value of another table, Oracle has two solutions:

First, when using subqueries, you must pay attention to the where condition (usually followed by the exists clause) when using subqueries, unless the two tables

Is a one-to-one relationship. Otherwise, the where condition is indispensable. If the where condition is omitted, a large number of null values may be inserted.

Oracle will update all the values by default, even if you use a subquery and a value cannot be found in the subquery.

, You will assume that Oracle may skip these values. If you are wrong, Oracle will update the value of the row blank)

Update

Set a. Count = A. Count + (select nvl (sum (count), 0) from B where B. ID = A. ID)

Where exists (select 1 from B where B. ID = A. ID)

Second, the update method of the Class View is exclusive to Oracle. Extract all the corresponding data and update the table.

When updating data, you must ensure that the table data is unique (with a primary key)

Update (select a. Count acount, B. Count bcount from a, B where a. ID = B. ID)

Set acount = acount + bcount

 

Feature 4:
Introduction to Oracle rownum

(1) rownum is fake and will automatically generate a serialized number based on the returned record.

(2) Role: You can make some output results that were originally hard to implement.

Common Operations are as follows:

(3) Top N result output:

Select * from EMP where rownum <5

[Note the traps when removing Top N from sorting results]

(4) Paging query:

Use rownum to paging the results. The following 6th to 10 records are returned:

Select * from (select e. *, rownum as rn from EMP e where rownum <= 10) B
Where B. Rn> 5;

(5) Use rownum for grouping sub-sorting

If we want to rename the members in the group after grouping, then:

Select decode (ROWNUM-min_sno, 0, A. Job, null) Job, decode (ROWNUM-min_sno, 0, 1, rownum + 1-

Min_sno) SnO, A. ename
From (select * from EMP order by job, ename),
(Select job, min (rownum) min_sno from (select * from EMP order

Job, ename) group by job) B
Where a. Job = B. Job

(6) check whether a table contains data:
Select * from EMP where rownum = 1;

A common trap is as follows: Since rownum is a fake one, rownum has a corresponding value only when a result is recorded.

(7) Use rownum> (value greater than 1),> = (value greater than or equal to 1), = (value greater than 1), so that no result is output;

Because:

A: rownum is a pseudo column. A rownum value is generated for each returned record after a result is returned;

B: The rownum of the returned Result Records is sorted from 1, so the first record is always 1;

In this way, when the first record is queried, The rownum of the record is 1, but the condition must be rownum> 1, so it does not match.

One record. Because there is no matching record, the rownum of the next record is still 1, so the loop will not be closed.

Result.

However, you can implement the following operations by instantiating >,=, =:
For example:

Select deptno, ename
From (select deptno, ename, rownum as R from EMP)
Where R> 5

(8) rownum and order

When rownum is used, the query results are sorted and then calculated rownum only when the order by field is the primary key.
Let's perform a test:
Environment: For the EMP table in the Scott solution, set empno as the primary key:
Alter table emp add constraint emp_pk_empno primary key (empno );
Then,
When the order by field is a primary key:
[Code = SQL] [/Code]
SQL> select rownum, empno, ename from C
2 Where rownum <= 5
3 order by empno;

Rownum empno ename
-------------------------
1 7369 Smith
2 7499 Allen
3 7521 ward
4 7566 Jones
5 7654 Martin
The query result set is sorted before rownum is calculated. The situation is normal.
Let's take a look at what happens when the order by field is not the primary key?
SQL> select rownum, empno, ename from C
2 Where rownum <= 5
3 order by ename;

Rownum empno ename
-------------------------
2 7499 Allen
4 7566 Jones
5 7654 Martin
1 7369 Smith
3 7521 ward
Sort the ename, and the results will be messy in an instant.

Now, let's analyze the cause:
Oracle first extracts records that meet the rownum condition in the order of physical rowids, that is, the first five records at the physical location. There will be two

Case:
First, if the order by field is not a primary key, only order by is performed.
Second, if the order by field is the primary key, sort the result set by and calculate rownum.

However, in the industrial environment, it is impossible to sort the primary keys all the time. Therefore, we can sort non-primary keys Through instantiation.
[Code = SQL] [/Code]

SQL> select empno, ename from
2 (select empno, ename from C order by ename)
3 where rownum <= 5;
 
Empno ename
---------------
7876 Adams
7499 Allen
7698 Blake
7782 Clark
7902 Ford

Feature 5:
Use of common Oracle functions rollup (), cube (), and grouping:

1 rollup () applies only to the first column: divides the first column into several groups, subtotal for each group, and then total for all groups

. Cube () First subtotal to the first column, then subtotal to the second column,..., the processing of each column is consistent with rollup, and finally to all total

. Therefore, two rollup statements are required to create a cube ().

For example:

SQL> select deptno, job, sum (SAL) from C group by rollup (deptno, job );

+

SQL> select deptno, job, sum (SAL) from C group by rollup (job, deptno );

=

SQL> select deptno, job, sum (SAL) from C group by cube (deptno, job );

2 The grouping function can accept a column and returns 0 or 1. If the column value is null, grouping () returns 1; if the column value is

If it is not null, 0 is returned. Grouping can only be used in queries using rollup or cube. When a null value is returned

Value, grouping () is very useful. You can use case... When... then... else to increase readability.

SQL> select case grouping (deptno)

When 0 then 'Team'

When 1 then 'department summary'

End, sum (SAL)

From C group by rollup (deptno );

 

The following are some useful Oracle SQL statements:

1 Oracle computing time difference

Ceil (n): returns the smallest integer greater than N;

To_date (): The time format can be adjusted as needed, 'yyyy-mm-dd hh24: MI: ss'

Difference between mm and MI: SQL statements are case insensitive;

To_date (): Number of days after mutual Subtraction

Millisecond level

Select Ceil (to_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss')-to_date ('2017-04-30 23:59:59 ', 'yyyy-mm-dd hh24-mi-ss ') * 24*60*60*1000) %

From dual

Second-level, minute-level, time-level, and day-level can be adjusted as long as 24*60*60*1000.

2 Arabic English-Chinese Comparison

Select to_char (to_date (n, 'yyyy'), 'Year') from dual;

Input: N = 2

Output: Two

3. The returned scalar value can be tested in the dual table:

For example:

Select power (3, 2) from dual

Select sign (100), sign (-100), sign (0) from dual;

[Sign (n): Take the number n. If it is greater than 0, 1 is returned. If it is less than 0,-1 is returned. If it is equal to 0, 0 is returned]

4. Get the product of all rows in a column:

Select power (10, sum (log (10, columnname) from t

5. query the specified record:

For example:

Display 5th to 10th records

Select a. * from (select rownum num, E. * from EMP e)

Where a. Num> = 5 and A. Num <= 10

6. query the indexes created in a table of the current user:

Select index_name from user_indexes where table_name = 'table name ';

Query all tables of the current user:

Select table_name from user_tables;

7. Remove letters and retain numbers:

Select regexp_replace (v, '[[: Alpha:]', '') from B;

8. view the system parameter table:

Select * From nls_session_parameters;

If you want to modify it:

Alter session set .............