Oracle Basic Function Application

Select Sal,sal as Saly from EMP;
--to find the average of a column
SELECT * from EMP;
--
Select Sal as NB from EMP;
--Sort By default ascending ASC Nearest principle
SELECT * from emp order by Empno,sal;
--Descending desc
SELECT * from emp order BY Sal Desc;
--concat Connection | |
Select concat (' Hello ', ' Nihao ') from dual;
Select Empno,empno+25| | ' is a ' | | Job as TA from EMP;
--Turn the field all lowercase
Select Lower (ename) from EMP;
--Capitalize first letter
Select Initcap (ename) from EMP;
--All Caps
Select Upper (ename) from EMP;
--SUBSTR Select the specified position dual the virtual table
Select substr (' Hello ', 2,5) from dual;
--instr the position of the specified letter
Select InStr (' Hkjhdkk ', ' J ') from dual;
--Replace the specified letter
Select replace (' Jlkdj ', ' j ', ') from dual;
--trim remove front and rear spaces
Select Trim (' Derf ') from dual;
--Take the remainder mod; Round rounding Round (222.325,2) number, reserved digits

Select mod (254,12) from dual;
--Truncation of trunc negative number for integer part
Select Trunc (5125.2545,-2) from dual;
--Get the current date
Select Sysdate from dual;
SELECT * from EMP;
--date can be added and reduced
Select Round ((sysdate-hiredate)/7,1) from EMP;
--Select the values between dates to be discharged in descending order
SELECT * FROM emp
where hiredate between ' February-January -81 ' and ' February-January -91 ' ORDER by hiredate Desc;
--fill the front of the Lpan with 8 bits in front of each of the * things; Rpan back
Select Lpad (ename,8, ' * ') ename from EMP;
--Subtracting the selected date date by the current date can only be reduced by
Select (sysdate-hiredate)/365 from EMP;
--number of months between two dates
Select Months_between (hiredate,sysdate) from EMP;
--Add a few months to the specified date
Select Add_months (hiredate,34) from EMP;
--last_day last day of the month
Select Last_day (hiredate) from EMP;
--System date rounding year, month no day
Select round (Sysdate, ' year ') from dual;
Select round (sysdate, ' month ') from dual;
-Interception of the year, the month can not day
Select Hiredate,trunc (HireDate, ' year ') from EMP;
Select Hiredate,trunc (hiredate, ' month ') from EMP;
Select Hiredate,add_months (hiredate,7) from EMP;
--Replacement
Select Sal,replace (Sal, ' 1 ', ' 5 ') from EMP;
--next_day the next day of the specified date
Select Next_day (sysdate, ' Monday ') from dual;
--Output in fixed format
Select To_char (sysdate, ' yyyy ') from dual;
Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;
--output numbers in fixed format
Select To_char (Sal, ' 999,999,999 ') from EMP;
--Return date Sunday is 1
Select Sysdate,to_char (sysdate, ' d ') from dual;
--Return numbers
Select To_number (12*12+25) from dual;
--Output The specified date in a fixed format
Select to_date (' 2015126 ', ' YYYYMMDD ') from dual;
--Returns the number of rows (tuples) of the table if the table has no data, returns null instead of 0
Select COUNT (*) from EMP;
Select COUNT (*) from dept;
SELECT * from EMP;
--Find out the countdown day of the Month
SELECT * from emp where Last_day (hiredate) -2=hiredate;
--Sort Sal Descending
SELECT * FROM emp ORDER by (SAL) desc;

Oracle Basic Function Application