Oracle Learning Summary 1

1: Unlocking the user
Alter user username account unlock;

2: Get system time, random number
Select Sysdate, Sys_guid () from dual;

3: Alias, make it more readable, if you want to preserve the original appearance of the field, you can
Surround with double quotes
Select ename,salary*12 from EMP;
Select Ename,salary*12 annuary_sal from EMP;
Select Ename,salary*12 as annuary_sal from EMP;
Select Ename,salary*12 "Annuary sal" from EMP;

4: null value
Select Ename,sal*12+comm from EMP; if there is a value of NULL in an arithmetic expression, the value of the expression is null
Select ENAME,SAL*12+NVL (comm,0) from EMP;NVL () function, if a value is calculated, NULL is replaced by 0

5: Connector | | Concat
Select Ename| | Sal from EMP;
Select Concat (ename,sal) from EMP;

6: Single quotes inside data, using two single quotes instead of one single quote
Select ' I ' M lilei! ' from dual;

7:distinct removing duplicate values from data
SELECT DISTINCT deptno from EMP; --Gets the value of the deptno that is not duplicated
SELECT distinct deptno,job from EMP; --when distinct modifies two or more fields, the Modifier field combination is not duplicated

8: Conditional Query where
Select Ename,sal from emp where sal>5000; --Query employees with a salary greater than 5000
Select Ename,sal from emp where sal<5000; --Query employees with a salary of less than 5000
Select Ename,sal from emp where sal<>5000; --inquire about employees who are not equal to 5000 salary
Select Ename,sal from emp where sal>=2000 and sal<=7000; --Query for employees with a salary greater than or equal to 2000 and less than or equal to 7000
Select Ename,sal from emp where Sal between and 7000;--ibid.
Select Ename,sal from emp where comm is null;--query bonus NULL employee
Select Ename,sal from EMP where comm are not null;--query bonuses are NOT NULL employees
Note: null is used as a condition after the where, but is used when the value is modified after update =

9:in querying multiple values for a field
Select Ename,deptno from emp where Deptno in (10,20); --Search for employees with department Number 10 or 20
Select Ename,deptno from emp where Deptno not in (10,20); --Query for employees who are not 10 or 20 of the department number

10:and at the same time, or or, not to negate
Select Ename, Deptno from EMP where deptno=10 and sal>5000;--query department Number 10 and employees with a salary greater than 5000
Select Ename, Deptno from emp where deptno=10 or sal>5000; --Query for employees with a department number of 10 or a salary greater than 5000

11: Fuzzy query like% match multiple characters _ match one character
Select ename from emp where ename like '%a% '; --Query the employee whose name contains a
Select ename from emp where ename like ' _a& '; --Query the employee whose first name is the second character
Select ename from emp where ename like '%\%% '; --If the name includes%, then you need to escape, default is \, you can specify, as follows
Select ename from emp where ename like '%$%% ' escape ' $ '--specifies that the $ symbol is an escape character

12: Sort ORDER BY ASC DESC
SELECT * FROM Dept order by Deptno; --Sort by deptno, by default ascending (ASC)
Equivalent to select * FROM Dept ORDER by DEPTNO ASC;

SELECT * FROM dept where deptno=10 the order by dname;--and where combination, first filtering out the divisions of Deptno 10, sorted by department name.

SELECT * FROM dept where deptno=10 ORDER by dname Asc,loc desc;--sorted by department name in ascending order, if the department name has the same, in accordance with
The addresses are sorted in descending order.

13: Common SQL functions

Select Lower (ename) from EMP; --lower (): Convert to lowercase

Select Upper (ename) from EMP; --upper (): Convert to uppercase

Select substr (ename,1,3) from emp;--starts with the Ename field 1, intercepts the substring of length 3 substr (): Gets the substring

Select CHR from dual; --CHR (): Converts a number to the corresponding character

Select ASCII (' a ') from dual; --ascii (): Converts a character to an ASCII code

Select Round (2.35,1) from dual;--accurate to 1 digits after decimal point pound (): rounded
Select Round (2.35) from dual;--default precision to Integer

Select To_char (Sal, ' $999,999.9999 ') from EMP; --Convert numbers to strings, start with $, 999 format, L represents localized currency

Select To_char (sysdate, ' Yyyy-mm-dd HH:mm:ss ') from dual; --Convert the date format to a string 12-hour system
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mm:ss ') from dual; --24-hour system

Oracle Learning Summary 1