Simple ORACLE database operation function usage, simple oracle operation

Simple ORACLE database operation function usage, simple oracle operation

1. DISTINCT usage:

Select distinct a. NAME, A. STUDENT_ID, A. SEX FROM TABLE STUDENT

This SQL statement queries the name, student ID, and gender from the student table, but the query rules of DISTINCT are consistent as long as there is a difference.

Example: source data query results

Id name sudent_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 Li Qian 103 female

4 Li Qian, 103 female

2. WMSYS. WM_CONCAT usage: used to connect characters separated by commas.

Select wmsys. WM_CONCAT (STUDENT_ID) AS STUDENT_ID from student group by (NAME)

Query Result: 101,102

3. SUBSTR usage: SUBSTR (string, start position, truncation length). The default search order is from left to right. When the start position is negative, the query starts from right.

Select substr ('slighthost. com', 0, 1) as m from dual // The returned result is s. The string with a length of 1 is truncated FROM the first position of the string.

Select substr ('slighthost. com', 1, 1) as m from dual // The returned result is s. Both 0 and 1 indicate that the starting position of the truncation is the first character.

Select substr ('slighthost. com', 14, 1) as m from dual // The returned result is m, which is truncated FROM the last character.

Select substr ('slighthost. com',-1, 1) as m from dual // The returned result is m, and-1 indicates the truncation starting FROM the last character.

Select substr ('slighthost. com',-8, 3) as m from dual // The returned result is hos.-8 indicates that the truncation starts FROM 8th characters on the right to the left.

4. INSTR usage: INSTR (source string, the string to be searched, starting from the nth character and finding the matched sequence number). The search order is the same as that of SUBSTR.

Select instr ('collaboration floor ',' or ', 1, 2) as m from dual // The returned result is 18.

Select instr ('collaboration floor ',' or ',-) as m from dual // The returned result is 7

Select substr ('collaboration floor ', INSTR ('collaboration floor', 'or',-), 3) from dual // The returned result is ora.

5. merge into usage:

During data operations, some fields in a data table may need to be added or updated to another data table. In this case, there is an efficient SQL statement.

Merge into
Using (select B. * from a, B where a. id = B. id and a. qty <> B. qty) B
On (a. id = B. id) // This statement specifies the connection conditions for copying two tables.
When matched then // If a. id = B. id exists, update a. qty = B. qty
Update set a. qty = B. qty
When not matched then // If a. id = B. id does not exist, add id and qty in B to Table.
Insert (a. id, a. qty)
Values (B. id, B. qty)

Oracle Database functions have several functions

Numeric function: Operation numeric type
Balanced functions: operate complex functions.
Date functions: Date Functions
Conversion Function: Conversion Type
Grouping functions: operations such as sum, difference, and product
Analysis functions: continuous summation and sorting
Other functions: Many without table Classification

How to use command lines to operate oracle databases

We recommend that you use sqlplus when you get started with oracle. If it is installed on windows, you can directly input sqlplus in the run. In linux or unix, you only need to enter sqlplus/as sysdba in the command line.