Shulou(Shulou.com)06/01 Report--

A basic single table query

1.1 query table structure

Desc table name

SQL > desc emp

Name Null? Type

EMPNO NOT NULL NUMBER (4)

ENAME VARCHAR2 (10)

JOB VARCHAR2 (9)

MGR NUMBER (4)

HIREDATE DATE

SAL NUMBER (7 dint 2)

COMM NUMBER (7 dint 2)

DEPTNO NUMBER (2)

1.2 find null values

Use is null

SQL > select empno from emp where comm is null

EMPNO 7369 7566 7698 7782 7839 7900 7902 7934

8 rows selected.

1.3 convert null values to actual values. It is recommended to use coalesce

SQL > select empno,nvl (comm,0) from emp where comm is null

EMPNO NVL (COMM,0) 7369 0 7566 0 7698 0 7782 0 7839 0 7900 0 7902 0 7934 0

8 rows selected.

SQL > select empno,nvl2 (comm,comm,0) from emp where comm is null

EMPNO NVL2 (COMM,COMM,0) 7369 0 7566 0 7698 0 7782 0 7839 0 7900 0 7902 0 7934 0

8 rows selected.

SQL > select empno,nullif (0Query comm) from emp where comm is null

EMPNO NULLIF (0MagneCom) 7369 0 7566 0 7698 0 7782 0 7839 0 7900 0 7902 0 7934 0

8 rows selected.

SQL > select empno,coalesce (comm,0) from emp where comm is null

EMPNO COALESCE (COMM,0) 7369 0 7566 0 7698 0 7782 0 7839 0 7900 0 7902 0 7934 0

8 rows selected.

NVL (expr1,expr2)

If the data types of expr1 and expr2 are the same, then:

If expr1 is empty (null), expr2 is displayed

If the value of expr1 is not empty, expr1 is displayed.

NVL2 (expr1,expr2, expr3)

If expr1 is not NULL, return expr2; expr1 is NULL and return expr3.

If the types of expr2 and expr3 are different, expr3 will be converted to the type of expr2. If it cannot be converted, an error will be reported.

NULLIF (expr1,expr2)

Returns empty (NULL) if expr1 and expr2 are equal, otherwise returns expr1.

Coalesce (expr1, expr2, expr3... .. Exprn)

Returns the first non-empty expression in the expression, or a null value if all are empty.

All expressions must be of the same type or can be implicitly converted to the same type, otherwise an error is reported.

The Coalese function is similar to the NVL function, but with more options.

1.4 use conditional logic in SELECT statements

SQL > select empno

2 ename

3 sal

4 case

5 when sal=4000 then 'too high'

7 else 'OK'

8 end as status

9 from emp

10 where deptno=10

EMPNO ENAME SAL STATUS 7782 CLARK 2450 OK 7839 KING 5000 too high 7934 MILLER 1300 too low

1.5 limit the number of rows returned

SQL > select empno from emp where rownum select empno,ename from (select empno,ename from emp order by dbms_random.value ()) where rownum select empno,ename from (select empno,ename from emp order by dbms_random.value ()) where rownum select TRANSLATE ('ab Hello abcdef','abcdef','123456') as newstring from dual

NEWSTRING

12 Hello 123456

SQL > select TRANSLATE ('ab Hello abcdef','abcdef','1234') as newstring from dual

NEWSTRING

12 Hello 1234

SQL > select TRANSLATE ('ab Hello abcdef','acdef','1234') as newstring from dual

NEWSTRING

1B Hello, 1b234

SQL > select TRANSLATE ('ab Hello abcdef','acdef','') as newstring from dual

N

The replacement value is empty and returns null.

SQL > select TRANSLATE ('ab Hello abcdef','1abcdef','1') as newstring from dual

NEWSTRING

Hello

Delete if there are no characters to replace the wei position

1.8 mixed strings are sorted alphabetically

SQL > set line 100

SQL > col TRANSLATE (EMPNO | |''| | ENAME,'-1234567890','-') format A40

SQL > select empno | |'| | ename as data,translate (empno | |''| | ename,'- 1234567890) from emp e order by 2

DATA TRANSLATE (EMPNO''ENAME,'-1234567890'

7499 ALLEN ALLEN

7698 BLAKE BLAKE

7782 CLARK CLARK

7902 FORD FORD

7900 JAMES JAMES

7566 JONES JONES

7839 KING KING

7654 MARTIN MARTIN

7934 MILLER MILLER

7369 SMITH SMITH

7844 TURNER TURNER

DATA TRANSLATE (EMPNO''ENAME,'-1234567890'

7521 WARD WARD

12 rows selected.

SQL > select empno | |'| | ename as data from emp e order by translate (empno | |''| | ename,'- 1234567890)

DATA

7499 ALLEN

7698 BLAKE

7782 CLARK

7902 FORD

7900 JAMES

7566 JONES

7839 KING

7654 MARTIN

7934 MILLER

7369 SMITH

7844 TURNER

DATA

7521 WARD

12 rows selected.

1.9 NULL sorting using NULLS FIRST/LAST

1.10 sort by the values of different columns in the condition area

SQL > select empno

2 ename

3 sal

4 from emp

5 where deptno=30

6 order by Case

7 when sal > = 1000 and sal select empno

2 ename

3 sal

4 from emp

5 where deptno=30

6 order by Case

7 when sal > = 1000 and sal select 'a'as C1 from dual

2 union all

3 select''as C1 from dual

C

A

2.2 union and or

SQL > select empno,ename from emp where empno=7782 or ename='WARD'

EMPNO ENAME 7521 WARD 7782 CLARK

SQL > select empno,ename from emp where empno=7782

2 union

3 select empno,ename from emp where ename='WARD'

EMPNO ENAME 7521 WARD 7782 CLARK

SQL > alter session set "_ b_tree_bitmap_plans" = false

Session altered.

SQL > explain plan for select empno,ename from emp where empno=7782 or ename='WARD'

Explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 3956160932

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | | 0 | SELECT STATEMENT | | 2 | 20 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | EMP | 2 | 20 | 3 (0) | 00:00:01 | Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT |

1-filter ("EMPNO" = 7782 OR "ENAME" = 'WARD')

13 rows selected.

SQL > explain plan for select empno,ename from emp where empno=7782

2 union

3 select empno,ename from emp where ename='WARD'

Explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 1027572458

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Ti |

Me |

PLAN_TABLE_OUTPUT

| | 0 | SELECT STATEMENT | | 2 | 20 | 6 (34) | 00 |

: 00:01 |

| | 1 | SORT UNIQUE | | 2 | 20 | 6 (34) | 00 |

: 00:01 |

2UNION-ALL

| | 3 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 10 | 1 (0) | 00 |

: 00:01 |

PLAN_TABLE_OUTPUT

| | * 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00 |

: 00:01 |

| | * 5 | TABLE ACCESS FULL | EMP | 1 | 10 | 3 (0) | 00 |

: 00:01 |

PLAN_TABLE_OUTPUTPredicate Information (identified by operation id):

4-access ("EMPNO" = 7782)

5-filter ("ENAME" = 'WARD')

18 rows selected.

In fact, ENAME can also build indexes that much faster.

What needs to be paid attention to

SQL > select deptno from emp where EMPNO=7698 or job='SALESMAN' ORDER BY 1

DEPTNO 30 30 30

SQL > select deptno,empno from emp where EMPNO=7698 or job='SALESMAN' ORDER BY 1

DEPTNO EMPNO 30 7499 30 7521 30 7844 30 7698 30 7654 SQL > select deptno from emp where EMPNO=7698

2 union

3 select deptno from emp where job='SALESMAN'

DEPTNO 30

To avoid this problem, you can use a unique column, primary key column, or rowid

SQL > select deptno,empno from emp where EMPNO=7698

2 union

3 select deptno,empno from emp where job='SALESMAN'

DEPTNO EMPNO 30 7499 30 7521 30 7654 30 7698 30 7844

SQL > with

2 e as (select rownum as sn,deptno,empno,job from emp)

3 select deptno

4 from

5 (

6 select sn,deptno from e where EMPNO=7698

7 union

8 select sn,deptno from e where job='SALESMAN'

9)

10 order by 1

DEPTNO 30 30 30

2.3 combine related rows

SQL > select e.empnoree.enamered.dnamered.loc

2 from emp e

3 inner join dept d on (e.deptno=d.deptno)

4 where e.deptno = 10

EMPNO ENAME DNAME LOC 7782 CLARK ACCOUNTING NEW YORK 7839 KING ACCOUNTING NEW YORK 7934 MILLER ACCOUNTING NEW YORKSQL > select e.empnoree.enamered.dnamerecod.loc

2 from emp e

3 inner join dept d using (deptno)

4 where deptno = 10

EMPNO ENAME DNAME LOC 7782 CLARK ACCOUNTING NEW YORK 7839 KING ACCOUNTING NEW YORK 7934 MILLER ACCOUNTING NEW YORK

2.4 IN,EXISTS and INNER JOIN

SQL > alter session set "_ b_tree_bitmap_plans" = false

Alter session set "_ b_tree_bitmap_plans" = false

*

ERROR at line 1:

ORA-12571: TNS:packet writer failure

SQL > conn scott/tiger@clonepdb_plug

Connected.

SQL > alter session set "_ b_tree_bitmap_plans" = false

Session altered.

SQL > explain plan for select empno,ename,job,deptno,sal

2 from emp

3 where (empno,ename,sal) in (select empno,ename,sal from emp)

4

Explained.

SQL > select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

Plan hash value: 3956160932

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | | 0 | SELECT STATEMENT | | 12 | 300 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | EMP | 12 | 300 | 3 (0) | 00:00:01 | Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT |

1-filter ("ENAME" IS NOT NULL AND "SAL" IS NOT NULL)

13 rows selected.

SQL > explain plan for select empno,ename,job,deptno,sal

2 from emp a

3 where exists (select null

4 from emp b

5 where b.ename=a.ename

6 and b.job=a.job

7 and b.sal=a.sal)

Explained.

SQL > select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

Plan hash value: 977554918

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | | 0 | SELECT STATEMENT | | 12 | 516 | 6 (0) | 00:00:01 |

| | * 1 | HASH JOIN SEMI | | 12 | 516 | 6 (0) | 00:00:01 |

| | 2 | TABLE ACCESS FULL | EMP | 12 | 300 | 3 (0) | 00:00:01 |

| 3 | TABLE ACCESS FULL | EMP | 12 | 216 | 3 (0) | 00:00:01 | PLAN_TABLE_OUTPUTPredicate Information (identified by operation id):

1-access ("B". "ENAME" = "A". "ENAME" AND "B". "JOB" = "A". "JOB" AND

"B". "SAL" = "A". "SAL")

16 rows selected.

SQL > select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

Plan hash value: 3638257876

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | | 0 | SELECT STATEMENT | | 12 | 516 | 6 (0) | 00:00:01 |

| | * 1 | HASH JOIN | | 12 | 516 | 6 (0) | 00:00:01 |

| | 2 | TABLE ACCESS FULL | EMP | 12 | 300 | 3 (0) | 00:00:01 |

| 3 | TABLE ACCESS FULL | EMP | 12 | 216 | 3 (0) | 00:00:01 | PLAN_TABLE_OUTPUTPredicate Information (identified by operation id):

1-access ("B". "JOB" = "A". "JOB" AND "B". "ENAME" = "A". "ENAME" AND

"B". "SAL" = "A". "SAL")

16 rows selected.

SQL > explain plan for select a.empnoreenamerejobpr salrea.deptno

2 from emp an inner join emp b using (job,ename,sal)

3

Explained.

SQL > select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

Plan hash value: 3638257876

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | | 0 | SELECT STATEMENT | | 12 | 516 | 6 (0) | 00:00:01 |

| | * 1 | HASH JOIN | | 12 | 516 | 6 (0) | 00:00:01 |

| | 2 | TABLE ACCESS FULL | EMP | 12 | 300 | 3 (0) | 00:00:01 |

| 3 | TABLE ACCESS FULL | EMP | 12 | 216 | 3 (0) | 00:00:01 | PLAN_TABLE_OUTPUTPredicate Information (identified by operation id):

1-access ("A". "SAL" = "B". "SAL" AND "A". "ENAME" = "B". "ENAME" AND

"A". "JOB" = "B". "JOB")

16 rows selected.

2.5 differences between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN

INNER JOIN returns required data

The left table of LEFT JOIN is dominant, the right table only returns matching data of the left table, and the blank that is not shown in the right table is equal to the right (+).

RIGHT JOIN is the opposite of the above equals left (+)

Both the left and right tables of FULL JOIN return index data, and the matching tables display a row.

2.6 self-correlation

SQL > run/

1 select a.empno as "employee number"

2 a.ename as "employee name"

3 a.job as "position"

4 b.empno as "Supervisor number"

5 b.ename as "Supervisor name"

6 from emp a

7 left join emp b on (a.mgr=b.empno)

8 * order by 1

Employee number employee name position supervisor number supervisor name

7369 SMITH CLERK 7902 FORD 7499 ALLEN SALESMAN 7698 BLAKE 7521 WARD SALESMAN 7698 BLAKE 7566 JONES MANAGER 7839 KING 7654 MARTIN SALESMAN 7698 BLAKE 7698 BLAKE MANAGER 7839 KING 7782 CLARK MANAGER 7839 KING 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 BLAKE 7900 JAMES CLERK 7698 BLAKE 7902 FORD ANALYST 7566 JONES

Employee number employee name position supervisor number supervisor name

7934 MILLER CLERK 7782 CLARK

12 rows selected.

2.7 NOT IN, NOT EXISTS and LEFT JOIN

SQL > select count (*) from emp where deptno = 40

COUNT (*) 0

SQL > select * from dept where deptno not in (select deptno from emp where deptno is null)

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGOSQL > select * from dept where not exists (select null from emp where emp.deptno=dept.deptno)

No rows selected

SQL > select dept.* from dept left join emp on dept.deptno=emp.deptno where emp.deptno is null

No rows selected

2.8 the conditions of external connection cannot be misplaced.

SQL > select dept.* from dept left join emp on (dept.deptno=emp.deptno and emp.deptno is null)

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO

SQL > alter session set "_ b_tree_bitmap_plans" = false

Session altered.

SQL > explain plan for select dept.* from dept left join emp on (dept.deptno=emp.deptno and emp.deptno is null)

Explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 2251696546

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Ti |

Me |

PLAN_TABLE_OUTPUT

| | 0 | SELECT STATEMENT | | 3 | 69 | 6 (17) | 00 |

: 00:01 |

| | 1 | MERGE JOIN OUTER | | 3 | 69 | 6 (17) | 00 |

: 00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 3 | 60 | 2 (0) | 00 |

: 00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 3 | | 1 (0) | 00 |

: 00:01 |

PLAN_TABLE_OUTPUT

| | * 4 | SORT JOIN | | 1 | 3 | 4 (25) | 00 |

: 00:01 |

| | * 5 | TABLE ACCESS FULL | EMP | 1 | 3 | 3 (0) | 00 |

: 00:01 |

PLAN_TABLE_OUTPUTPredicate Information (identified by operation id):

4-access ("DEPT". "DEPTNO" = "EMP". "DEPTNO" (+))

Filter ("DEPT". "DEPTNO" = "EMP". "DEPTNO" (+))

5-filter ("EMP". "DEPTNO" (+) IS NULL)

19 rows selected.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 1353548327

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Ti |

Me |

PLAN_TABLE_OUTPUT

| | 0 | SELECT STATEMENT | | 1 | 23 | 6 (17) | 00 |

: 00:01 |

| | 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17) | 00 |

: 00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 3 | 60 | 2 (0) | 00 |

: 00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 3 | | 1 (0) | 00 |

: 00:01 |

PLAN_TABLE_OUTPUT

| | * 4 | SORT UNIQUE | | 12 | 36 | 4 (25) | 00 |

: 00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 12 | 36 | 3 (0) | 00 |

: 00:01 |

PLAN_TABLE_OUTPUTPredicate Information (identified by operation id):

4-access ("DEPT". "DEPTNO" = "EMP". "DEPTNO")

Filter ("DEPT". "DEPTNO" = "EMP". "DEPTNO")

18 rows selected.

2.9 check whether the number of data and corresponding data items in the two tables are equal

SQL > run

1 select a.empno,a.ename,b.empno,b.ename

2 from emp a

3 full join emp b on (b.empno=a.empno)

4 * where b.empno is null or b.empno is null

No rows selected

SQL > 4

4 where b.empno is null or b.empno is null

SQL > del

SQL > run

1 select a.empno,a.ename,b.empno,b.ename

2 from emp a

3 full join emp b on (b.empno=a.empno)

EMPNO ENAME EMPNO ENAME 7369 SMITH 7369 SMITH 7499 ALLEN 7499 ALLEN 7521 WARD 7521 WARD 7566 JONES 7566 JONES 7654 MARTIN 7654 MARTIN 7698 BLAKE 7698 BLAKE 7782 CLARK 7782 CLARK 7839 KING 7839 KING 7844 TURNER 7844 TURNER 7900 JAMES 7900 JAMES 7902 FORD 7902 FORD EMPNO ENAME EMPNO ENAME 7934 MILLER 7934 MILLER

12 rows selected.

2.10 Null value processing of multi-table query

Lower than the ALLEN commission.

SQL > select a.ename.comm

2 from emp a

3 where coalesce (a. Commcomm0) create table test (

2 C1 varchar2 (10) default 'default 1'

3 c2 varchar2 (10) default 'default 2'

4 c3 varchar2 (10) default 'default 3'

5 c4 date default sysdate

6)

Table created.

SQL > insert into test (C1, c2, c3) values (default,null,'test')

1 row created.

SQL > select * from test

2

C1 C2 C3 C4

Default 1 test 2017-12-26 09:46:20

3.1 prevent insertions on certain columns

SQL > create or replace view v_test as select C1 from test c2m c3 from test

View created.

SQL > insert into V_TEST values ('lost by one hand but not changed by hand' no change 4')

1 row created.

SQL > select * from test

C1 C2 C3 C4

Default 1 test 2017-12-26 09:46:20

Losing one hand will not change 4 2017-12-26 09:57:36

SQL > insert into V_TEST values (default,null,' does not change 4')

Insert into V_TEST values (default,null,' does not change 4')

*

ERROR at line 1:

ORA-32575: Explicit column default is not supported for modifying views

3.2 copy table definition and structure

SQL > create table test1 as select * from test where 1: 2

Table created.

SQL > select * from test1

No rows selected

SQL > create table test2 as select * from test

Table created.

SQL > select * from test2

C1 C2 C3 C4

Default 1 test 2017-12-26 09:46:20

Losing one hand will not change 4 2017-12-26 09:57:36

3. 3 restrict data entry using with check option

SQL > alter table test modify c3 not null

Table altered.

SQL > create or replace view v_test1 as select C1 from test with check option c2m c3 from test with check option

View created.

SQL > insert into V_TEST1 values ('test',null,null)

Insert into V_TEST1 values ('test',null,null)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SCOTT". "TEST". "C3")

3.4 Multi-table insert statement

Unconditional insert

SQL > insert all

2 into test1 (C1 ~ c2 ~ c3) values ('1 ~ 2 ~ 2 ~ 3')

3 into test2 (C1, c2, c3) values ('1, 2, 2, 3, 3)

4 into test (C1, c2, c3) values ('1, 2, 2, 3')

5 select from test1

The number of inserts depends on the number of select rows

It is recommended to use select from dual if you want one line.

Conditional insert

SQL > run

1 insert all

2 when job in ('CLERK','SALESMAN') then

3 into test (C1, c2, c3) values (ENAME,JOB,mgr)

4 when job='MANAGER' then

5 into test1 (C1, c2, c3) values (ENAME,JOB,mgr)

6 else

7 into test2 (C1, c2, c3) values (ENAME,JOB,mgr)

8 select from emp

12 rows created.

SQL > select * from test

2

C1 C2 C3 C4

Default 1 test 2017-12-26 09:46:20

Losing one hand will not change 4 2017-12-26 09:57:36

12 3 2017-12-26 10:29:31

SMITH CLERK 7902 2017-12-26 10:39:54

ALLEN SALESMAN 7698 2017-12-26 10:39:54

WARD SALESMAN 7698 2017-12-26 10:39:54

MARTIN SALESMAN 7698 2017-12-26 10:39:54

TURNER SALESMAN 7698 2017-12-26 10:39:54

JAMES CLERK 7698 2017-12-26 10:39:54

MILLER CLERK 7782 2017-12-26 10:39:54

10 rows selected.

SQL > select * from test1

C1 C2 C3 C4

11 12 13

JONES MANAGER 7839

BLAKE MANAGER 7839

CLARK MANAGER 7839

SQL > select * from test2

C1 C2 C3 C4

Default 1 test 2017-12-26 09:46:20

Losing one hand will not change 4 2017-12-26 09:57:36

21 22 23

KING PRESIDENT

FORD ANALYST 7566

SQL > insert first

2 when job in ('CLERK','SALESMAN') then

3 into test (C1, c2, c3) values (ENAME,JOB,mgr)

4 when empno in (7900, 7934, 754, 7566) then

5 into test1 (C1, c2, c3) values (ENAME,JOB,mgr)

6 else

7 into test2 (C1, c2, c3) values (ENAME,JOB,mgr)

8 select job,ename,mgr,empno from emp

12 rows created.

SQL > select * from test

C1 C2 C3 C4

SMITH CLERK 7902 2017-12-26 10:53:18

ALLEN SALESMAN 7698 2017-12-26 10:53:18

WARD SALESMAN 7698 2017-12-26 10:53:18

MARTIN SALESMAN 7698 2017-12-26 10:53:18

TURNER SALESMAN 7698 2017-12-26 10:53:18

JAMES CLERK 7698 2017-12-26 10:53:18

MILLER CLERK 7782 2017-12-26 10:53:18

7 rows selected.

SQL > select * from test1

C1 C2 C3 C4

JONES MANAGER 7839

SQL > select * from test2

C1 C2 C3 C4

BLAKE MANAGER 7839

CLARK MANAGER 7839

KING PRESIDENT

FORD ANALYST 7566

Summary of 3.5Merge into usage

MERGE INTO table_name alias1

USING (table | view | sub_query) alias2

ON (join condition)

WHEN MATCHED THEN

UPDATE table_name

SET col1 = col_val1

Col2 = col_val2

WHEN NOT MATCHED THEN

INSERT (column_list) VALUES (column_values)

Strictly speaking, "in a Merge statement with both Insert and Update syntax, the total number of Insert/Update records is the number of alias2 records in the Using statement."

3.6 Delete duplicate records

SQL > insert into test values (1, 2, 3, default)

2

1 row created.

SQL > insert into test values (1, 2, 3, default)

1 row created.

SQL > select * from test

C1 C2 C3 C4

12 3 2017-12-26 11:08:14

12 3 2017-12-26 11:08:18

SQL > select rowid as rid

2 c1

3 row_number () over (partition by C1 order by c4) as seq

4 from test

5 order by 2,3

RID C1 SEQ

AAASXpAALAAAACuAAA 1 1

AAASXpAALAAAACuAAB 1 2

SQL > delete

2 from test

3 where rowid in (select rid

4 from (select rowid as rid

5 row_number () over (partition by C1 order by c4) as seq

6 from test)

7 where seq > 1)

1 row deleted.

SQL > select * from test

C1 C2 C3 C4

12 3 2017-12-26 11:08:14

SQL > delete

2 from test a

3 where exists (select / + hash_sj/ null from test b where b.c1=a.c1 and b.rowid > a.rowid)

Keep the latest select * from test

C1 C2 C3 C4

12 3 2017-12-26 13:32:18

Fourth string

4.1 traversal string

SQL > select level from dual connect by level select "Pinyin", level,substr ("Pinyin", level,1) from (select 'TTXS' as "Pinyin" FROM DUAL) connect by level select Q' [g'day mate] 'qmarks from dual

QMARKS

G'day mate

SQL > select q' {g'day mate} 'qmarks from dual

QMARKS

G'day mate

SQL > select Q'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.