In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
One-line function:
One-line functions are mainly divided into the following five categories: character functions, numeric functions, date functions, conversion functions, and general functions.
Character function
UPPER (string | column): returns the input string in uppercase
LOWER (string | column): returns the input string in lowercase
INITCAP (string | column): initials are capitalized
LENGTH (string | column): find the length of the string
REPLACE (string | column): replace
SUBSTR (string | column, start point [, end point]): string interception
Turn to uppercase functions:
SELECT UPPER ('hello') FROM dual
Operation of turning to lowercase:
SELECT LOWER (ename) FROM emp
Capitalize the initials of each employee's name:
SELECT INITCAP (ename) FROM emp
Find out the length of each employee's name:
SELECT ename,LENGTH (ename) FROM emp
Replace all the letters "A" in the name with the letter "_":
SELECT REPLACE (ename,'A','_') FROM emp
There are two syntax for string interception
Syntax 1: SUBSTR (string | column, starting point), which means that it is truncated from the starting point to the end
SELECT ename,SUBSTR (ename,3) FROM emp
SELECT ename,SUBSTR (ename,0,3) FROM emp
SELECT ename,SUBSTR (ename,1,3) FROM emp
Numeric function
ROUND (number | column [, number of decimal places retained]): the operation of rounding
TRUNC (number | column [, number of decimal places retained]): discard the contents of the specified location
MOD (number 1, number 2): take the module, take the remainder
SELECT ROUND (903.53567), ROUND (- 903.53567), ROUND (903.53567), ROUND (903.53567) FROM dual
SELECT MOD (10jue 3) FROM dual
Date function
Current date:
SELECT SYSDATE FROM dual
Several calculations can also be made in the date:
Date + number = date, indicating the date after several days:
SELECT SYSDATE + 3SYSDATE + 300FROM dual
Date-number = date, indicating the date several days ago:
SELECT SYSDATE-3 Syndate-300 FROM dual
Date-date = number, which indicates the number of days between two days, but it must be a big date-a small date
LAST_DAY (date): find the last day of the specified date:
SELECT LAST_DAY (SYSDATE) FROM dual
NEXT_DAY (date, day of the week): find the date of the next specified week X:
SELECT NEXT_DAY (SYSDATE,' Monday') FROM dual
ADD_MONTHS (date, number): find the date after a number of months:
SELECT ADD_MONTHS (SYSDATE,4) FROM dual
MONTHS_BETWEEN (date 1, date 2): find the month experienced between two dates:
SELECT ename,hiredate,TRUNC (MONTHS_BETWEEN (SYSDATE,hiredate)) FROM emp
Conversion function
TO_CHAR (string | column, format string): change a date or number into a string to display
TO_DATE (string, format string): turn a string into a DATE data display
TO_NUMBER (string): turn a string into a numeric display
SELECT TO_CHAR (SYSDATE,'yyyy-mm-dd'), TO_CHAR (SYSDATE,'yyyy') year,TO_CHAR (SYSDATE,'mm') months,TO_CHAR (SYSDATE,'dd') day FROM dual
SELECT TO_DATE (1989-09-12) FROM dual
SELECT TO_NUMBER ('1') + TO_NUMBER ('2') FROM dual
General function
The NVL () function, which handles null:
SELECT ename,sal,comm, (sal+NVL (comm,0)) * 12meme NVL (comm,0) FROM emp
DECODE () function: multi-value judgment
SELECT empno,ename,job,DECODE (job,'CLERK',' Clerk, SALESMAN',' salesperson, MANAGER',' Manager, ANALYST',' analyst, PRESIDENT',' President) FROM emp
Statistical function
COUNT (): query the data records in the table
AVG (): find the average
SUM (): summation
MAX (): find the maximum
MIN (): find the minimum
SELECT COUNT (empno), SUM (sal), AVG (sal) FROM emp
SELECT MAX (sal), MIN (sal) FROM emp
SELECT COUNT (ename) FROM BONUS
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.