Oracle-sql the subtleties of the place

This article summarizes the usual SQL statements and the subtleties of some Oracle functions. Welcome everyone to supplement the usual most commonly used SQL statement, for everyone to learn reference.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

SQL> select * from temp2; NAMESORCE ---------- ---------- 1                  43 2                  23 3                  42 4                  87 5                  12 <span style="font-size: 14px;"><strong><span style="color: rgb(255, 0, 0);">1、数据累加</span></strong></span> SQL> SELECT NAME, sum(sorce) OVER(ORDER BY NAME)   2    FROM temp2   3   ORDER BY NAME; NAMESUM(SORCE)OVER(ORDERBYNAME) ---------- --------------------------- 1                                   43 2                                   66 3                                  108 4                                  195 5                                  207 <span style="font-size: 14px;"><strong><span style="color: rgb(255, 0, 0);">2、去掉最大值和最小值</span></strong></span> SQL> SELECT NAME,   2         sorce,   3         LAG(sorce) over(order by sorce)  Lag_List,   4         LEAD(sorce) over(order by sorce)  Lead_List   5    FROM temp2; NAMESORCE        Lag       Lead ---------- ---------- ---------- ---------- 5                  12                    23 2                  23         12         42 3                  42         23         43 1                  43         42         87 4                  87         43

3. Famous analytic function--sort

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> SELECT name,   2         value,   3         RANK() OVER(order by value) RANK_SORT,   4         DENSE_RANK() OVER(order by value) DENSE_SORT,   5         ROW_NUMBER() OVER(order by value) ROW_SORT   6    FROM sorce; NAMEVALUE       RANK_SORT DENSE_SORT ROW_SORT ---------- ------ ---------- ---------- ---------- wu          21          1          1          1 zhang       60          2          2          2 Li          70          3          3          3 xue         119         5          5          5 <span style="color: rgb(255, 0, 0);">wang        130         6          6          6 chen        130         6          6          7 sun         175         8          7          8</span> zhao        285         9          8          9 su          359         10         9          10 Li          480         11         10         11<br>可见三者的区别：<br>RANK()OVER():如果值相同，则两者顺序号相同，随机一个在另外一个的上边，而且顺序号会有间断，不是连续的；<br>DENSE_RANK():如果值相同，则两者顺序号相同，随机一个在另外一个的上边，而且顺序号仍然是连续的，不存在断层的现象；<br>ROW_NUMBER():如果值相同，则两种顺序号不同，安装顺序号依次排开，而且顺序号是连续的。

4. TRANSLATE () function

The translate function is similar to replace, but unlike replace, translate specifies the from_str that appear in the string, replacing each character in the from_str with the same to_str as the position order in To_ The characters in Str.

Examples given in SQL reference: SELECT TRANSLATE (' Sql*plus User ' s Guide ', '/* ', ' ___ ') from DUAL;

Skillfully used:

(1) Determine if a string is a number

SELECT TRANSLATE (' ABC123 ', ' #1234567890. ', ' # ') from DUAL;

(2) Number of occurrences of the statistical character E

SELECT LENGTHB (TRANSLATE (' abcdefgefgdbe ', ' E ' | | ' Abcdefgefgdbe ', ' E ')) from DUAL;

5. ROUND () function

We usually use the most is the first, with the round () function as the data rounding operation, in fact, the round function has a second form, the date is formatted operation, and the trunc () function is similar.

such as: SELECT ROUND (sysdate, ' yyyy ') from DUAL;
SELECT ROUND (sysdate, ' MM ') from DUAL;
SELECT ROUND (sysdate, ' HH24 ') from DUAL;

6. NVL Correlation function

NVL related functions are: NVL (EXPR1,EXPR2), NVL2 (EXPR1,EXPR2,EXPR3), Nullif (EXPR1,EXPR2), DECODE (expr1,expr2,value1,expr3,value2 ..., default)

(1) NVL (EXPR1,EXPR2): If the expr1 is empty, replace it with EXPR2.

(2) NVL2 (EXPR1,EXPR2,EXPR3): Returns EXPR2 if EXPR1 is not empty, otherwise returns EXPR3.

(3) Nullif (EXPR1,EXPR2): Compare Expr1 and EXPR2, or return NULL if you want, otherwise return EXPR1.

(3) DECODE (Expr1,expr2,value1,expr3,value2...,default): If Expr1 is equal to EXPR2, then value1 is returned, EXPR1 is returned if EXPR3 equals value2, ... Otherwise, the default is returned.

7, collect the statistics of the table

There are many ways to collect a table's statistics: (1) ANALYZE (2) dbms_stats

It looks very cumbersome, in fact it is very simple to use.

If you are analyzing a table:

ANALYZE TABLE table_name COMPUTE STATISTICS;

Currently, Oracle recommends the second method. There are a number of processes and methods in the Dbms_stats package that collect statistical information about the schema, table, and index.

　　EXEC Dbms_stats. Gather_table_stats (ownname=> ' SCOTT ',tabname=> ' EMP ',degree=>4,cascade=> ' TRUE ');

8, not finished, to be continued ....