Database Basic Query statement

Select Basic Query statement

Basic query statements using tables from the Scott user in Oracle

SELECT * from EMP;
--The number of the employee who listed the position as manager, name--
Select Empno,ename from emp where job = ' MANAGER ';
--like Fuzzy Query I don't know, either.
SELECT * from EMP WHERE ename like '%*_% ' ESCAPE ' * ';
--Finding employees with bonuses above their salaries
SELECT * FROM EMP where comm>sal;
--Find out the sum of each employee's salary and bonus
Select Sal+comm,ename from EMP;
--Identify the manager in department 10 and the General Staff in department 20
SELECT * FROM EMP where
(Deptno = ten and job = ' MANAGER ') or (Deptno = ' job = ' clerk ');
--Find out that department 10 is not a manager or a regular employee, and that wages are greater than or equal to 2000 of employees
SELECT * FROM EMP where
Deptno = ten and Job not in (' MANAGER ', ' Clerk ') and Sal >= 2000;
--Find out the different jobs of employees with bonuses
SELECT DISTINCT job from EMP where Comm are not null and comm>0;
-Find employees with no bonuses or bonuses below 500
SELECT * FROM EMP where comm is null or comm<500;
--Show the employee's name, according to their service life, the oldest employees in front
Select ename from emp order by HireDate;
--upper into uppercase
Select Upper (' ADCDE ') from dual;
SELECT * from emp where ename = Upper (' Smith ');
--lower into lowercase
Select lower (' ADGDFD ') from dual;
--initcap first letter to uppercase
Select Initcap (ename) from EMP;
--Concat Connection two characters
Select Concat (' A ', ' B ') from dual;
Select ' A ' | | ' B ' from dual;
--substr Intercept string
Select substr (' ABCDE ', Length (' abced ')-2) from dual;
--intercepts a character from a bit, a negative value represents the beginning of the end, and a positive value represents a number from the beginning
Select substr (' ABCdef ', 3) from dual;
--length get the length of a property
Select Length (ename) from EMP;
--replace Replace the name in the ename column so that a in the name is replaced by a
Select Replace (ename, ' a ', ' a ') from EMP;
--instr gets the position of the specified character in the original character
Select InStr (' Hello World ', ' or ') from dual;
--lpad left padding (The specified character fills the subsequent character with the specified length)
Select Lpad (' Sminth ', one, ' * ') from dual

--rpad Right padding
SELECT rpad (' King ', *, ' * ') from dual;
--Filter the leading and trailing spaces
SELECT TRIM (' Hello World ') from dual
----------------------------------------------------
--Numerical functions
--round rounding
Select Round (413,-2) from dual;
Select Round (446,-2) from dual;
Select Round (450,-2) from dual; --round the forward two bits starting from the single digit
Select Round (412.313,2) from dual;
Select Round (421.353,2) from dual; --Keep two bits after the decimal point for rounding
--trunc two digits before decimal point rounding
Select Trunc (412.13,-2) from dual;
Select Trunc (412.131,2) from dual;
--mod the number in front of the remainder to take the remainder of the remainder
Select mod (412.323,3) from dual
Select mod (10,3) from dual;
------------------------------------
--Date function
--month_between ()
Select Months_between (sysdate,hiredate) from EMP;
--add_months () Add a specified month
Select Add_months (sysdate,1) from dual;
Select Add_months (sysdate, -2) from dual;
--next_day next Specify a date for the day of the week
Select Next_day (sysdate, ' Monday ') from dual;
Select Next_day (sysdate, ' Tuesday ') from dual;
--last_day the last day of the current date month
Select Last_day (sysdate) from dual;
------------------------------------------
--Conversion function
--to_char
--Get the current year
Select To_char (sysdate, ' yyyy ') from dual;
--Displays the current date in the current format
Select To_char (sysdate, ' Fmyyyy-mm-dd ') from dual;
Select To_char (sysdate, ' dd-mm-yyyy ') from dual;
Select To_char (sysdate, ' mm-dd-yyyy ') from dual;
Select To_char (Sal, ' l999,999,999 ') from emp;--returns the character in the specified format
Select To_char (sysdate, ' D ') from dual;--shows that the current date is the day ordinal of the week
--to_number
Select To_number (' +to_number ') from dual;--adds two characters
Select To_char (' +to_number ') from dual;
Select To_char (' 3 ') *to_number (' 4 ') from dual;
--to_date Specify format output date
Select To_date (' 20090210 ', ' YYYYMMDD ') from dual;
-----------------------------------

----General functions
--NVL ()
-Returns the value if there is a value in the specified attribute column, or 0 if no return value is given, only the number in parentheses
Select NVL (comm,0) from EMP;
Select NVL (comm,11) from EMP;

--NVL2 () Expressions 1 and 2 compare, return the value of expression 3 if the value is the same, and return the value of expression 2 if not equal. Total Return worth Listing

Select EMPNO,ENAME,SAL,COMM,NVL2 (comm,sal+comm,sal) total from EMP;

Database Basic Query statement