Oracle self study notes sorting (IV)
8, Functions
8.1 distinct
When the keyword DISTINCT is followed by only one column 1, it represents the non duplicate data of the query result of a single field. When it is followed by multiple column values, it represents all the unique values of the query result composed of multiple fields, and the grouping elimination of multiple fields is performed.
select` `distinct` `b.coursename, t.score ``from` `score t, course b ``where` `t.courseid = b.courseid ``and` `t.courseid = ``'R20180101'``;
8.2 single line function
When each function is applied to the records of the table, you can only enter the column value in one row as the input parameter (or constant) and return a result.
8.2. 1 character function
String function:
TO_ Char (field to be converted, conversion format) to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
DECODE()
| **Functions** | **Explain** | **Case** | **Results** |
|---|---|---|---|
| ASCII(X) | Find the ASCII code of character X | select ASCII('A') FROM DUAL; | 65 |
| CHR(X) | Find the character corresponding to ASCII code | select CHR(65) FROM DUAL; | 'A' |
| LENGTH(X) | Find the length of string X | select LENGTH('ORACLE technology circle ') from DUAL; | 9 |
| CONCATA(X,Y) | Returns the result of connecting two strings X and Y | select CONCAT('ORACLE ',' technology circle ') from DUAL; | ORACLE technology circle |
| INSTR(X,Y[,START]) | To find the position of string Y in string X, you can specify to Start the search from the Start position without filling in the default Start from scratch | SELECT INSTR('ORACLE technology circle ',' technology ') FROM DUAL; | 7 |
| LOWER(X) | Converts uppercase letters in string X to lowercase | SELECT LOWER('ORACLE technology circle ') FROM DUAL; | oracle technology circle |
| UPPER(X) | Converts lowercase letters in string X to uppercase | SELECT UPPER('Oracle technology circle ') FROM DUAL; | ORACLE technology circle |
| INITCAP(X) | Converts the first letter of all words in string X to uppercase and the rest to lowercase. | SELECT INITCAP('ORACLE is good ') FROM DUAL; | Oracle Is Good |
| LTRIM(X[,Y]) | Remove the Y string to the left of string X. when y is not filled in, the default is to leave a space to the left of string X | SELECT LTRIM('– ORACLE technology circle', '-') FROM DUAL; | ORACLE technology circle |
| RTRIM(X[,Y]) | Remove the Y string on the right of string X. when y is not filled in, the default is to remove the space on the right of string X | SELECT RTRIM('ORACLE technology circle – ',' - ') FROM DUAL; | ORACLE technology circle |
| TRIM(X[,Y]) | Remove the Y string on both sides of string X. when y is not filled in, the default is to leave a space around string X | SELECT TRIM('– ORACLE technology circle –', '-') FROM DUAL; | ORACLE technology circle |
| REPLACE(X,old,new) | Find the old character in string X and replace it with the new character | SELECT REPLACE('ORACLE technology circle ',' technology circle ',' Technology Exchange ') FROM DUAL; | ORACLE Technical Exchange |
| SUBSTR(X,start[,length]) | Intercept string x, and intercept the string with length from the start position (where start starts from 1). If length is not filled in, it will be intercepted to the end of string X by default | SELECT SUBSTR('ORACLE technology circle ', 1,6) FROM DUAL; | ORACLE |
| RPAD(X,length[,Y]) | Right complement character Y to string X to make the string length reach length | SELECT RPAD('ORACLE',9,'-') from DUAL; | ORACLE— |
| LPAD(X,length[,Y]) | The left complement character Y is applied to the string X to make the string length reach the length length | SELECT LPAD('ORACLE',9,'-') from DUAL; | —ORACLE |
8.2. 2 date type function
1. System date and time functions
**SYSDATE function: * * this function has no parameters and can get the current time of the system
**SYSTIMESTAMP function: * * this function has no parameters. You can get the current time of the system. This time contains time zone information, accurate to microseconds
2. Add the specified month function to the date:
**ADD_MONTHS (r,n) function: * * this function returns the date after adding the number of months n to the specified date r
r: The specified date.
n: The number of months to be added. If n is negative, it indicates the number of months to be subtracted.
select` `to_char(add_months(to_date(``'2018-11-12'``,``'yyyy-mm-dd'``),1),``'yyyy-mm-dd'``),
``to_char(add_months(to_date(``'2018-10-31'``,``'yyyy-mm-dd'``),1),``'yyyy-mm-dd'``),
``to_char(add_months(to_date(``'2018-09-30'``,``'yyyy-mm-dd'``),1),``'yyyy-mm-dd'``)
``from` `dual;
3. Last day of month function:
**LAST_DAY ® Function: * * returns the last day of the current month of the specified r date
``select` `last_day(sysdate) ``from` `dual;
4. Date function one week after the specified date:
**NEXT_DAY(r,c) function: * * returns the date corresponding to the R date character (C: indicates the day of the week) of the week after the specified r date
``select` `next_day(to_date(``'2018-11-12'``,``'yyyy-mm-dd'``),``'Thursday'``) ``from` `dual;
5. Function that returns a specific part of a specified date:
**EXTRACT (time) function: * * returns the year, month, day, minute, etc. of the specified time.
select` `extract( ``year` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `year``,
``extract( ``month` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `month``,
``extract( ``day` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `day``,
``extract( ``minute` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `minute``,
``extract( ``second` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `second
``from` `dual;
6. Returns the number of months between two dates
**MONTHS_BETWEEN(r1,r2) function: * * this function returns the month directly between r1 date and r2 date. When r1 > r2, it returns a positive number. If r1 and r2 are the same day in different months, it returns an integer, otherwise it returns a decimal. When r1 < r2, a negative number is returned.
select` `months_between(to_date(``'2018-11-12'``, ``'yyyy-mm-dd'``),
``to_date(``'2017-11-12'``, ``'yyyy-mm-dd'``)) ``as` `zs, ``--integer
``months_between(to_date(``'2018-11-12'``, ``'yyyy-mm-dd'``),
``to_date(``'2017-10-11'``, ``'yyyy-mm-dd'``)) ``as` `xs, ``--decimal
``months_between(to_date(``'2017-11-12'``, ``'yyyy-mm-dd'``),
``to_date(``'2018-10-12'``, ``'yyyy-mm-dd'``)) ``as` `fs ``--negative
``from` `dual;
7. Date interception function
**ROUND (r[,f]) function: * * ROUND the date r in the format of F. if f is not filled in, it will be rounded to the nearest day
select` `sysdate, ``--current time
``round(sysdate, ``'yyyy'``) ``as` `year``, ``--By year
``round(sysdate, ``'mm'``) ``as` `month``, ``--monthly
``round(sysdate, ``'dd'``) ``as` `day``, ``--By day
``round(sysdate) ``as` `mr_day, ``--It is not filled in by day by default
``round(sysdate, ``'hh24'``) ``as` `hour` `--By hour
``from` `dual;
**TRUNC (r[,f]) function: * * intercept the date r in the format of F. if f is not filled in, it will be intercepted to the current date.
select` `sysdate, ``--current time
``trunc(sysdate, ``'yyyy'``) ``as` `year``, ``--By year
``trunc(sysdate, ``'mm'``) ``as` `month``, ``--monthly
``trunc(sysdate, ``'dd'``) ``as` `day``, ``--By day
``trunc(sysdate) ``as` `mr_day, ``--It is not filled in by day by default
``trunc(sysdate, ``'hh24'``) ``as` `hour` `--By hour
``from` `dual;
8.2. 3 numerical function
| **Count** | **Explain** | **Case** | **Results** |
|---|---|---|---|
| ABS(X) | Find the absolute value of the value X | select abs(-9) from dual; | 9 |
| COS(X) | Find the cosine of the value X | select cos(1) from dual; | 0.54030230586814 |
| ACOS(X) | Find the inverse cosine of the value X | select acos(1) from dual; | 0 |
| CEIL(X) | Find the minimum value greater than or equal to the value X | select ceil(7.8) from dual; | 8 |
| FLOOR(X) | Find the maximum value less than or equal to the value X | select floor(7.8) from dual; | 7 |
| log(x,y) | Find the logarithm of x as the base y | select log(2,8) from dual; | 3 |
| mod(x,y) | Find the remainder of x divided by y | select mod(13,4) from dual; | 1 |
| power(x,y) | Find the y power of x | select power(2,4) from dual; | 16 |
| sqrt(x) | Find the square root of x | select sqrt(16) from dual; | 4 |
| round(x[,y]) | Find the value x and round it in y-bit. If y is not filled in, the default value is y=0; When y > 0, it is rounded to the Y digit to the right of the decimal point. When y < 0, it is rounded to the | y | place to the left of the decimal point. | select round(7.816, 2), round(7.816), round(76.816, -1) from dual; | 7.82 / 8 / 80 |
| trunc(x[,y]) | The value x is directly intercepted in the Y position. When y is not filled in, the default value is y=0; When y > 0, it is intercepted to the Y digit to the right of the decimal point. When y < 0, it is truncated to the | y | digit to the left of the decimal point. | select trunc(7.816, 2), trunc(7.816), trunc(76.816, -1) from dual; | 7.81 / 7 / 70 |
8.2. 4 conversion function
| **Functions** | **Explain** | **Case** | **Results** |
|---|---|---|---|
| asciistr(x) | Converts the string x to the ASCII value corresponding to the database character set | select asciistr('Oracle technology circle ') from dual; | Oracle\6280\672F\5708 |
| bin_to_num(x1[x2...]) | Convert binary values to corresponding decimal values | select bin_to_num(1,0,0) from dual; | 4 |
| cast(x as type) | Data type conversion function, which can convert x to the data type of the corresponding type. Basically, it is used to install database rules for mutual conversion of numbers, characters and time types, | select cast('123' as number) num,cast(123 as varchar2(3)) as ch,cast(to_date('20181112','yyyymmdd') as varchar2(12)) as time from dual; | 123 / '123' / December November - 18 (three column values separated by "/") |
| convert(x,d_chset[,r_chset]) | String conversion function between character sets. String x is converted according to the original character set R_ Convert chset to target character set d_chset, when R_ If chset is not filled in, the database server character set is selected by default. | select CONVERT('oracle technology circle ',' US7ASCII ',' ZHS16GBK ') from dual; | oracle??? |
| to_char(x[,f]) | Converts a string or time type x into a string in format f. | select to_char(123.46,'999.9') from dual; select to_char(sysdate,'yyyy-mm-dd') from dual; | 123.52018-11-13 |
| to_date(x[,f]) | The string x can be formatted according to format f and converted to time type results. | select to_date('2018-11-13','yyyy-mm-dd') from dual; | 2018/11/13 |
| to_number(x[,f]) | The string x can be formatted according to format f and converted to a numeric result. | select to_number('123.74','999.99') from dual | 123.74 |
8.2. 5 aggregate function
AVG(), SUM(), MIN(), MAX(), COUNT(), etc
8.3 windowing function
select` `table``.``column``, Analysis_function() OVER( [partition ``by` `field] [``order` `by` `field [windos]] ) ``as` `Statistical value from` `table
Syntax parsing:
1,Analysis_function: Specifies the name of the analysis function. The commonly used analysis functions are sum, max, and first_value,last_value,rank,row_number, etc.
2. over(): the name of the windowing function. partition by specifies the field for data grouping, order by specifies the field for sorting, and windos specifies the data window (that is, specifies the number of rows to be operated by the analysis function)
eg:
select` `c.stuname,
``b.coursename,
``t.score,
``--Get the maximum score in the group
``max``(t.score) over(partition ``by` `t.courseid) ``as` `score_max,
``--Get the lowest score in the group
``min``(t.score) over(partition ``by` `t.courseid) ``as` `score_min,
``--The first value of the grouping window (Specifies that the window is the first to last line in the group)
``first_value(t.score) over(partition ``by` `t.courseid
``order` `by` `t.score ``desc` `ROWS` `BETWEEN` `UNBOUNDED PRECEDING ``AND` `UNBOUNDED FOLLOWING) ``as` `score_first,
``--The last value of the grouping window(Specifies that the window is the first to last line in the group)
``last_value(t.score) over(partition ``by` `t.courseid
``order` `by` `t.score ``desc` `ROWS` `BETWEEN` `UNBOUNDED PRECEDING ``AND` `UNBOUNDED FOLLOWING) ``as` `score_last,
--The first line is unbounded preceding，The current line is current row，The last line is unbounded following
``--First value of the grouped window (no window specified)
``first_value(t.score) over(partition ``by` `t.courseid ``order` `by` `t.score ``desc` `) ``as` `score_first_1,
``--Last value of the grouped window (no window specified)
``last_value(t.score) over(partition ``by` `t.courseid ``order` `by` `t.score ``desc` `) ``as` `score_last_1
``from` `STUDENT.SCORE t, student.course b, student.stuinfo c
``where` `t.courseid = b.courseid
``and` `t.stuid = c.stuid
8.3.1 ROW_NUMBER/RANK
ROW_NUMBER/RANK: the position in the group can be obtained by returning the ordered offset in the group according to the sorted fields in the windowing function.
select` `c.stuname,
``b.coursename,
``t.score,
``--Intra group ranking
``row_number() over(partition ``by` `t.courseid ``order` `by` `t.score ``desc``) ``as` `"row_number ranking"``,--The ranking of the same score is not repeated and increases in turn
``--Intra group ranking
``rank() over(partition ``by` `t.courseid ``order` `by` `t.score ``desc``) ``as` `"rank ranking"--Same score, same ranking, skip
``from` `STUDENT.SCORE t, student.course b, student.stuinfo c
``where` `t.courseid = b.courseid
``and` `t.stuid = c.stuid
1,ROW_ The number function returns a unique value. When the same data is encountered, the ranking increases in order of records in the record set.
2. The rank function returns a unique value, but when the same data is encountered, the ranking of all the same data is the same, and the ranking will be vacated between the ranking of the last same record and the ranking of the next different record. For example, two people with 84 points in mathematics tied for second place, but "Zhang Sanfeng" was directly fourth.
3. We often use row_ The number function uses the ranking mechanism (uniqueness of ranking) to filter duplicate data, that is, according to a specific sorting condition, obtain the data ranked as 1 to obtain the latest data value in the duplicate data.