Thursday-Basic functions of the Practice database

Tag:man   where   last    Rounding    and     location    str    add    init   

SELECT * from EMP where job = ' Clerk ' or job = ' MANAGER ' and Sal >= 1200; SELECT * from emp ORDER by Hiredate;select * from EMP, deptwhere EMP. DEPTNO = DEPT. deptno;--to sal This column in ascending select * from emp order by sal;--to sal this column in descending select * from emp ORDER by SAL desc;--Insert Column Select Upper (' ABCDE '), sal+ ' emp;select lower (' ABCDE '), sal+1000 from emp;--Change the case of the character select * from EMP where ename = Upper (' Allen ');-- Select Initcap (ename) from emp;--Add the contents of select Concat (' Changed ', '? '), ename from emp;--to reduce the content of select substr (' ABCDE ', Length (' ABCDE ')-2) from emp;--represents the position of the processing byte select substr (' 412.313 ', 0,2) From Emp;select substr (' 412.313 ', -2) from Emp;select substr (' 412.313 ', 2) from emp;--to determine content byte length select length (ename) from emp;--turns all occurrences of a ename column into Aselect replace (ename, ' a ', ' a ') from emp;--where the string is located select InStr (' Hello World ', ' ld ') from emp;-- Left padding Select Lpad (' Smith ', Ten, ' * ') from emp;--right fill select Rpad (' Smith ', ' Ten, ' * ') from emp;--filter first space select Trim (' Mr Smith ') From emp;--a value based on location select round (412,1) from emp;--rounding select round (412.513,0) froM emp;select Round (412.313,-2) from emp;--rounding select Trunc (412.53,2) from emp;--the current month to a specified time month select Months_between ( sysdate,hiredate) from Emp;select months_between (to_date (' 2017-10-14 ', ' yyyy-mm-dd '), to_date (' 2017-08-12 ', ' Yyyy-mm-dd ')) from dual;--current month plus 1select add_months (hiredate,1) from emp;--jump to next specified week select Next_day (sysdate, ' Tuesday ') From dual;--to the last day of the month Select Last_day (sysdate) from dual;--get current time select Sysdate from dual;--get year select To_char (Sysdate, ' yyyy ') from dual;--get current date Select To_char (sysdate, ' Fmyyyy-mm-dd ') from dual;--convert Sal to renminbi format select To_char (Sal, ' L999999999 ') from emp;--returns the current week as a number select To_char (sysdate, ' D ') from dual;--calculates two numbers of subtraction select To_number (' 987 ') +to_ Number (' + ') from Dual;select to_number (' 258 ')-to_number (' + ') from Dual;select to_number (' 258 ') *to_number (' + ') from Dual;select to_number (' 2200 ')/to_number (' 4 ') from dual;--convert string to date Select To_date (' 20170713 ', ' YYYYMMDD ') from dual;-- Assign NULL to 8select NVL (comm,8) from emp;--take out select mod (9,5) from dual;--average select AVG (comm) from emp;--of all numeric values and sElect sum (COMM) from emp;--employee employed on the third day of each month SELECT * from EMP where Last_day (hiredate) -2=hiredate; Employee hired--25 years ago select * from EMP where hiredate<=add_months (SYSDATE,-25*12);--employee name preceded by Dearselect ' Dear ' | | Initcap (ename) from emp;--find a person whose name is 5 letters SELECT * FROM emp where length (ename) = 5; --Find the person who has no R in the name select * from emp where ename isn't like '%r_% '; --Display the first word of all employee names Select substr (ename,0,1) from emp;--name in descending order select * from emp ORDER by ename desc;--employee Day wage select Sal/30 from E mp;--February onboarding Staff select * from emp where TO_CHAR (hiredate, ' fmmm ') = ' 2 ';
Use of various types of database functions

Thursday-Basic functions of the Practice database