Oracle database operation simple function usage

1. Distinct usage:

SELECT distiNCT a.name, a.student_id, a.sex from TABLE STUDENT

This SQL statement is from the Student table query name, school number, gender, but distinct query rule is as long as there is a difference, is eligible.

Example: source data Query Results

ID name sutdent_id sex name student_id sex

1 Zhang Ming 101 male Zhang Ming 101 male

2 Zhang Ming 102 male Zhang Ming 102 male

3 Zhang Ming 101 male Qian 103 female

4 Qian 103 women

2, Wmsys. Wm_concat usage: Used to connect characters, middle, separated.

SELECT Wmsys. Wm_concat (student_id) as student_id from STUDENT GROUP by (NAME)

Query results: 101,102

3, SUBSTR Usage: SUBSTR (string, starting position, intercept length), the default lookup order from left to right, when the starting position is negative, start looking from the right.

SELECT SUBSTR (' slighthost.com ', 0, 1) as M from DUAL//Returns the result is S, starting from the first position of the string to intercept a string of length 1.

SELECT SUBSTR (' slighthost.com ', 1, 1) as M from DUAL//return result is s,0 and 1 both indicate that the start position of the Intercept is the first character.

SELECT SUBSTR (' slighthost.com ', 1) as M from DUAL//Returns the result is M, starting with the last character intercept.

SELECT SUBSTR (' slighthost.com ',-1, 1) as M from DUAL//Returns the result is a m,-1 indicating that the intercept was started from the last character.

SELECT SUBSTR (' slighthost.com ',-8, 3) as M from DUAL///The result is a hos,-8 representation of the 8th character from right to left to intercept.

4. INSTR Usage: INSTR (the source string, the string to find, starting with the first few characters, to find the ordinal number of the first match), the lookup order is the same as substr.

SELECT INSTR (' Collaboration floor ', ' or ', $) as M from DUAL//return result is 18

SELECT INSTR (' Collaboration floor ', ' or ', -1,2) as M from DUAL//return result is 7

SELECT SUBSTR (' Collaboration floor ', INSTR ("Collaboration Floor", ' or ', -1,2), 3) from DUAL//return result is Ora

5.MERGE into usage:

In the case of data manipulation, it is sometimes necessary to add or update some fields of one datasheet to another, and there is a high-efficiency SQL statement.

Merge into a
using (select b.* from A, b where a.id = b.ID and A.qty <> b.qty) b
on (a.id = b.id)//This statement is a connection condition that describes two table copies
when matched then//if there is a.ID = b.id, then update a.qty = B.qty
Update Set a.qty = B.qty
when isn't matched then//if there is no a.id=b.id, then the ID, qty in B is added to table a
Insert (A.id,a.qty)
values (b.id,b.qty)

Oracle database operation simple function usage