Oracle Common functions

SELECT * from Scott.dept;
SELECT * from Scott.emp;

--dual is a virtual table provided by Oracle
Select Length (' Hello ') from dual;

--Common functions
--lower uppercase to lowercase upper lowercase to uppercase
SELECT * from Scott.emp where ename= ' Smith ';
--Search for the result after the letter is converted to lowercase
SELECT * from Scott.emp where lower (ename) = ' Smith ';

Select Upper (' HelloWorld ') from dual;
Select lower (' HELLOWORLD ') from dual;

--initcap the first letter of all words in a string into uppercase
Select Initcap (' SQL Course ') from dual;

--concat connecting two strings
Select Concat (' Hello ', ' world ') from dual;

--substr takes a substring, starting with start, taking count
Select substr (' HelloWorld ', 1,5) from dual;

--SUBSTR substring, starting from 4 to the end
Select substr (' HelloWorld ', -4) from dual;

--length returns the length of a string
Select Length (' HelloWorld ') from dual;

--INSTR (String,char) searches for a specified character in a string, returns the position of the specified character, starting at 1
Select InStr (' HelloWorld ', ' O ') from dual;

--rpad paste the character to the right of the column, Lpad paste the character to the left of the column
Select Rpad (sal,8, ' * ') from scott.emp;

Select Lpad (sal,8, ' * ') from scott.emp;

--trim Delete an empty string
Select Trim (' HelloWorld ') from dual;
Select Length (' HelloWorld ') from dual;
Select Length (Trim (' HelloWorld ')) from dual;

--trim Delete the end-to-end H (case sensitive)
Select Trim (' H ' from ' HELLOWORLDH ') from dual;

--trim Delete the first H
Select Trim (Leading ' H ' from ' HELLOWORLDH ') from dual;

--trim Delete Tail of H
Select Trim (Trailing ' H ' from ' HELLOWORLDH ') from dual;

--trim Delete the end-to-end h
Select Trim (both ' H ' from ' HELLOWORLDH ') from dual;

--replace (' string ', ' s1 ', ' s2 ')
--string the character or variable that you want to be replaced
--s1 the string that needs to be replaced by the string S2 replacement
Select replace (' Helloworldh ', ' ll ', ' FF ') from dual;

--Numerical function round rounding
Select Round (3.14159,3) from dual;

--trunc truncation
Select TRUNC (3.14159,3) from dual;

--Take the mold
Select MOD (1600,300) from dual;

--Date function

CREATE TABLE Tb_test (
Currdate Date
);

SELECT * from Tb_test;

--Query the system time
Select Sysdate from dual;

--Insert Sysdate
Insert into Tb_test (currdate) values (sysdate);
--to_date inserting a custom date format string into date-type data
Insert into Tb_test (currdate) VALUES (to_date (' 2018/1/14 02:03:42 ', ' yyyy/mm/dd hh:mi:ss '));
Insert into Tb_test (currdate) VALUES (to_date (' 2018/1/14 ', ' yyyy/mm/dd '));
--Date function
Date type--oracle is Yyyy/mm/dd hh:mi:ss
Select Sysdate from dual;

--Add or subtract a number on a date the result is still a date
Select sysdate+84 from dual;

--Three conversion functions to_date To_char To_number
--to_date (date string, format)
Select To_date (' 2018/1/14 02:03:42 ', ' yyyy/mm/dd hh:mi:ss ') from dual;
Select To_date (' 2018/1/14 ', ' YYYY/MM/DD ') from dual;

--to_char (date, format) converts a date to a character
Select To_char (sysdate, ' Yyyy/mm/dd ') from dual;
Select To_char (sysdate, ' Yyyy/mm/dd hh:mi:ss ') from dual;
Select To_char (sysdate, ' mm ') from dual;
--turn numbers into characters
Select To_char (3.14159) from dual;

--to_number (String) converts the number of a string to type
Select To_number (' 123.123 ') from dual;
--Cannot convert non-numeric string to number type
Select Tb_number (' abc ') from dual;

--Two date subtracts the number of days between dates that return a date
Select To_date (' 2018/04/08 ', ' yyyy/mm/dd ')-sysdate from dual;
Select TRUNC (to_date (' 2018/04/08 ', ' yyyy/mm/dd ')-sysdate,0) from dual;
Select Sysdate-to_date (' 2018/04/08 ', ' YYYY/MM/DD ') from dual;

--You can add or subtract hours from the date with the number except 24来
Select sysdate+2/24 from dual;
--You can add minutes to the date with a number other than 24 and then 60.
Select SYSDATE+2/24/60 from dual;

--Returns the number of months between D1,D2, D1,d2 can be a string
Select Months_between (
To_date (' 2018-04-08 ', ' yyyy-mm-dd '),
To_date (' 2017-11-20 ', ' yyyy-mm-dd ')
) as months from dual;

--Return Date D plus 3 months after the date, 3 is an integer
Select Add_months (sysdate,3) from dual;

--Returns Date D after the first day of the week
Select Next_day (sysdate, ' Tuesday ') from dual;
--Foreign Sunday is 1, so 3 means Tuesday
Select Next_day (sysdate,3) from dual;

--Returns the date of the last day of the month
Select Last_day (sysdate) from dual;

--Get the next hour, 0 minutes, 0 seconds.
Select Trunc (sysdate+1/24, ' hh ') from dual;

--Get the next day, 0 minutes, 0 seconds.
Select Trunc (sysdate+1, ' DD ') from dual;

--Get Next January 1 0 minutes 0 seconds
Select Last_day (sysdate) from dual;
Select Trunc (Last_day (sysdate) +1) from dual;

--Get Next January 1, 1 0 minutes 0 seconds
Select Trunc (sysdate, ' yyyy ') from dual;
Select Add_months (trunc (sysdate, ' yyyy '), n) from dual;

The--round rounding function defaults to DD by 4 5 into the next day beyond 12 o'clock Noon
Select Round (sysdate) from dual;

--over 15th returns to the next one months
Select Round (sysdate, ' mm ') from dual;
-Return to the next January 1, 1 for more than June
Select Round (sysdate, ' yyyy ') from dual;

--Truncation Date
Select Trunc (sysdate, ' mm ') from dual;
Select Trunc (sysdate, ' DD ') from dual;
Select Trunc (sysdate, ' hh ') from dual;

--Timer
--Execute every minute
Select Trunc (sysdate, ' mi ') +1/24/60 from dual;

--Regular 3 daily execution
--Example: Daily 2 o'clock in the morning execution
Select Trunc (sysdate) +1+2/24 from dual;

--Regular weekly execution
--for example: every Monday 2 o'clock in the morning execution
Select Trunc (Next_day (sysdate,2)) +2/24 from dual;

--Regular monthly execution
--Example: 1st 2 o'clock in the morning every month to execute
Select Trunc (Last_day (sysdate)) +1+2/24 from dual;

--Quarterly scheduled execution
--such as 2 o'clock in the morning on the first day of each quarter
Select Trunc (Add_months (sysdate,3), ' Q ') +2/24 from dual;

--every half-yearly scheduled execution
-for example: every July 1 and January 1 2 o'clock in the morning
Select Add_months (trunc (sysdate, ' yyyy '), 6) +2/24 from dual;

--Scheduled execution every year
-for example: January 1 and 2 o'clock in the morning each year
Select Add_months (trunc (sysdate, ' yyyy '), +2/24 from dual;

--general functions to accomplish some functions
--NVL function If the first argument is null, take the second argument
Select comm from Scott.emp;
Select NVL (comm,0) from Scott.emp;

--use 0 to replace NULL comm, calculate annual income
Select Ename,sal,comm, (SAL+COMM) *12 annual income from Scott.emp;
Select ENAME,SAL,NVL (comm,0), (SAL+NVL (comm,0)) *12 from Scott.emp;

--NVL2 function If the first argument is null, take the third argument, otherwise take the second argument
Select NVL2 (comm,comm,0) from Scott.emp;

--nullif returns NULL, unequal returns EXPR1
Select Nullif (1,3) from dual;

--Multi-line function
--case
SELECT * from Scott.emp;

Select Ename,job,sal Base Pay,
Case Job
When ' salesman ' and then sal+sal*0.9
When the ' MANAGER ' then sal+sal*0.85
When ' ANALYST ' then sal+100
When ' clerk ' and then sal+200
else Sal
End
As real wages-as aliases
From Scott.emp;

--decode function
Select Ename,job,sal Base Pay,
Decode (Job,
' Salesman ', sal+sal*0.39,
' MANAGER ', sal+sal*0.85,
' ANALYST ', sal+100,
' Clerk ', sal+200,
' President ', Sal
As real wages--aliases
From Scott.emp;

--Multi-table connection
CREATE TABLE Tb_course (
Name VARCHAR2 (18),
Course VARCHAR2 (18),
Grade number
);

INSERT into Tb_course (name,course,grade) VALUES (' Tom ', ' JDBC ', 20);
INSERT into Tb_course (name,course,grade) VALUES (' Tom ', ' Hibernate ', 50);
INSERT into Tb_course (name,course,grade) VALUES (' Tom ', ' Spring ', 80);
INSERT into Tb_course (name,course,grade) VALUES (' Mary ', ' JDBC ', 30);
INSERT into Tb_course (name,course,grade) VALUES (' Mary ', ' Hibernate ', 60);
INSERT into Tb_course (name,course,grade) VALUES (' Mary ', ' Spring ', 70);

--Multi-table connection 1.
SELECT * from Tb_course;
Select Name,grade as JDBC from Tb_course where course= ' jdbc ';
Select Name,grade as Hibernate from Tb_course where course= ' hibernate ';
Select Name,grade as spring from Tb_course where course= ' spring ';

Select T4.name,t1. Jdbc,t2. Hibernate,t3. Spring
From
(select Name,grade as JDBC from Tb_course where course= ' jdbc ') T1,
(select Name,grade as Hibernate from Tb_course where course= ' hibernate ') T2,
(select Name,grade as Spring from Tb_course where course= ' Spring ') T3,
(select distinct name from Tb_course) T4
where t4.name = t1.name and T4.name = t2.name and t4.name = T3.name;

--Multi-table Connection 2.
Select
Name
Decode (course, ' jdbc ', grade,0) as JDBC,--if the value of ' Course ' column is ' JDBC ', the value of ' grade ' is displayed, otherwise 0
Decode (course, ' Hibernate ', grade,0) as Hibernate,
Decode (course, ' Spring ', grade,0) as Spring
From
Tb_course;

--decode (Column,value,cloumnvalue,default)
--Grouping, group functions, decode
Select
Name
SUM (Decode (course, ' jdbc ', grade,0)) JDBC,
SUM (Decode (course, ' Hibernate ', grade,0)) Hibernate,
SUM (Decode (course, ' Spring ', grade,0)) Spring
From
Tb_course T
GROUP by T.name;

Oracle Common functions