Oracle Database Learning 3 functions commonly used in--oracle databases

1. Character functions:

ASCII (x)--Returns the ASCII code of the X character

Concat (x, y)--connect Y to output on X

Select Concat (first_name,last_name) from customers;

Initcap (x) capitalize the first letter of each word in X

INSTR (' NameName ', ' e ', ')--start with the first letter of ' NameName ' to find the position of the second occurrence of ' e ';

Length (x) Gets the lengths of the X string;

LOWER (x) converts the letters in x to lowercase;

UPPER (x) converts the letters in x to uppercase;

Lpad (x,10, ' n ')--to the left of the X string is the ' n ' character, so that the total length of x reaches 10 characters;

Rpad (x,10, ' R ')

LTRIM

For example:

LTRIM (' Hello, my friends! ')--default intercept left empty string

RTRIM (' HI this is a dog. ', ' dog. ') -Intercept the ' dog ' on the right. Character

NVL NVL (phone, ' This is a null value ')--Converts the phone field's hollow values to a string ' This is a null value '

NVL2 (X,value1,value2) If x is non-null, then value1, otherwise returns value2

Replace (' name ', ' m ', ' n '), replacing ' m ' in name with n

SUBSTR (name,2,7) starts with the second character of name and takes a length of 7 characters;

2. Numeric functions

ABS (-10) Gets the absolute value of-10 10

CELL (5.8) Gets the smallest integer greater than or equal to 5.8 6

CELL ( -5.2)-5

Floor (5.8) Gets the largest integer less than or equal to X 5

Floor (-5.2)-6

MOD (8,3), take 8 divided by 5 remainder 3

POWER (2,1)-"Take 2 one-time square

ROUND (5.75) 5.75 rounding up to 6

To_char (1234.5678, ' 99999.99 ')--converts a number to a string and follows the specified format

Regexp_like ()--match regular expression

3. Aggregation functions

AVG (x) average

Count (x), COUNT (*) takes longer to execute than count (1), so avoid using count (*)

Max (x) returns the maximum value of X

MEDIAN (x) returns the middle value of X

MIN (x) returns the minimum value of x

STDDEV (x) returns the standard deviation of X

SUM (x) returns x and

Group by composes rows into multiple parts with the same column values

Aggregate functions are used in conjunction with GROUP by grouping

For example: Select product_type_id, AVG (price) from the products; Error Ora-00937:not a single-group group function.

Aggregate functions cannot be used in a WHERE clause

Example: Select product_type_id, AVG (Price)

From Products

where AVG (price) >30

Group BY PRODUCT_TYPE_ID; Error because where can only be filtered on a single row rather than on a group of rows, you can use having if you want to filter the conditions of the grouped rows.

Correct practice:

Select product_type_id, AVG (Price)

From Products

GROUP BY product_type_id

have avg (price) >20;

The combination of WHERE and group by uses

Where the rows in the Products table are filtered, only price<15 rows are preserved, and group by groups the reserved rows according to product_type_id

Select product_type_id, AVG (Price)

From Products

where Price <15

GROUP BY product_type_id

Order BY product_type_id;

WHERE, group BY, and having the combined use

Select Product_type_id,avg (Price)

From Products

where Price <15

GROUP BY product_type_id

have avg (price) >13

Order BY price_type_id;

Oracle Database Learning 3 functions commonly used in--oracle databases