Oracle learning notes 6 SQL common functions, oracle learning notes SQL

Oracle learning notes 6 SQL common functions, oracle learning notes SQL
Function category

Oracle provides a series of functions for executing specific operations

An SQL function has one or more parameters and returns one value. The following are the types of SQL functions: the single-row function returns only one value for each row queried from the table. It can appear in the SELECT clause and in the WHERE clause. The single-row function can be roughly divided into: 1. date functions 2. number function 3. character functions 4. conversion Function 5. other functions single-row functions date functions perform operations on date values and generate date data type or value type results date functions include: 1. ADD_MONTHS 2. MONTHS_BETWEEN 3. LAST_DAY 4. ROUND 5. NEXT_DAY 6. TRUNC 7. EXTRACT

-- Sysdate: Obtain the system time function Select sysdate from dual; -- Add_months: specify the date Increase (decrease) month time Select add_months ('24-October-15', 12) from dual; select add_months (sysdate, 10) from dual; -- Months_between: returns the difference of the month between two times. Select months_between (sysdate, '24-December-15') from dual; -- Last_day: return Select last_day (sysdate) from dual; -- Next_day: when is the next date Select next_day (sysdate, 'saturday') from dual; -- To_date (date, date format): convert a date into a date data in the specified format; Select to_date ('1970/24', 'yyyy-mm-dd') from dual;

The character function accepts character input and returns a character or value.

Function	Input	Output	Description
Initcap (char)	Select initcap ('hello') from dual;	Hello	Uppercase letters
Lower (char)	Select lower ('fun ') from dual;	Fun	Convert uppercase to lowercase
Upper (char)	Select upper ('sun') from dual;	SUN	Convert lowercase to uppercase
Ltrim (char, set)	Select ltrim ('xyzadams', 'xyz') from dual;	Adams	Start from the left and delete the specified character.
Rtrim (char, set)	Select rtrim ('xyzadams ', 'ams') from dual;	Xyzad	Start from the right side and delete the specified character
Translate (char, from,)	Select translate ('jack', 'J', 'B') from dual;	Back	Replacement character
Replace (char, searchstring, [rep string])	Select replace ('Jack and jue ', 'J', 'bl') from dual;	Black and blue	Replace all specified characters
Instr (char, m, n)	Select instr ('worldwid', 'D') from dual;	5	Locate the location of the specified character
Substr (char, m, n)	Select substr ('abcdefg', 3, 2) from dual;	Cd	Find the character at the specified position
Concat (expr1, expr2)	Select concat ('hello', 'World') from dual;	Hello world	Concatenated characters

 

The following are some other character functions:

• CHR and ascii chr: Convert the ASCII code to the char character. ASCII: Get the ASCII code of the character.
• LPAD and RPAD fill in the following characters:
  • Example:

  • Select rpad ('192. 5', 10, '0') from dual; -- output '192. 123'

  • select job, lpad(job, 10, ' ') from emp;

• TRIM spaces on both sides of TRIM
• LENGTH calculation LENGTH

• Select * from emp2 where length (ename) = 4; -- data with a name of 4 characters

• DECODE

 

The numeric function accepts numeric input and returns numerical results.

Function	Input	Output	Description
Abs (n)	Select abs (-15) from dual;	15	Take absolute value
Ceil (n)	Select ceil (1, 44.778) from dual;	45	Obtain the entire function
Cos (n)	Select cos (180) from dual;	-5984601	CosineFunction
Sin (n)	Select sin (0) from dual;	0	Sine function
Floor (n)	Select floor (100.2) from dual;	100	Rounding
Power (m, n)	Select power (4, 2) from dual;	16	PowerFunction
Mod (m, n)	Select mod (10, 3) from dual;	1	Evaluate the remainder Function
Round (m, n)	Select round (100.256, 2) from dual;	100.26	Rounds up the value field to the specified decimal place.
Trunc (m, n)	Select trunc (100.256, 2) from dual;	100.25	Round down the value field to the specified decimal place.
Sqrt (n)	Select sqrt (4) from dual;	2	Square RootFunction
Sign (n)	Select sign (-30) from dual;	-1	SymbolFunction When x <0, sgn (x) =-1 when x = 0, sgn (x) = 0 when x> 0, sgn (x) = 1

Conversion functions convert values from one data type to another. Common conversion functions include: TO_CHAR -- convert to character TO_DATE -- convert to time type TO_NUMBER -- convert to Number type convert null value function: The following are the functions used to convert null values: NVL NVL2 NULLIF

 

Select sal, comm, sal + nvl (comm, 0) from emp; -- If comm is null, 0 indicates select itemdesc, NVL (re_level, 0) from itemfile; select itemdesc, NVL2 (re_level, re_level, max_level) from itemfile; select itemdesc, NULLIF (re_level, max_level) from itemfile;

 

Grouping functions grouping functions return results for each group of rows based on a group of rows and return a value

Avg: calculates the average value of the entire column.

Select avg (sal) from emp; -- average salary

 

Min: Minimum value

Select min(sal) from emp;

 

Max: Maximum Value

Sum: Sum

Count: calculates the number of entries. Note: If the column name is written, null rows are not counted.

Select count (*) from emp where job = 'manager'; -- count the number of managers Select sum (sal) from emp; -- count the sum of wages Select count (*) from emp where deptno = 30; -- how many individuals with the Statistical Department number 30 select count (distinct job) from emp; -- how many jobs are there to remove duplicate data

Note: The query results cannot be displayed together with other columns:

Select ename, min (sal) from emp; -- incorrect syntax

The group by clause is used to divide information into smaller groups. Each GROUP of rows returns a single result for this GROUP.

select p_category, MAX(itemrate) from itemfile group by p_category;

HAVING clause is used to specify the conditions for the group by clause to retrieve rows.

select p_category, MAX(itemrate) from itemfile group by p_category having p_category not in ('accessories');

 

The Analysis Function calculates the aggregate value based on a group of rows. It is used to calculate the accumulated aggregation ranking, moving average, and other analysis functions. For each group of records, multiple rows are returned. The following three analysis functions are used for calculation. the ranking of a row in a group of ordered rows, sequence Number starts from 1. ROW_NUMBER returns a consecutive ranking, regardless of whether the value is equal. RANK has the same row ranking with equal values, and the ordinal number jumps 3. DENSE_RANK has the same row ranking with equal values, and the sequence number is continuous.

select d.dname, e.ename, e.sal, DENSE_RANK()  OVER (partition by e.deptno order by e.sal desc)  as denrankfrom emp e, dept d WHERE e.deptno = d.deptno;