1, select statement

Overall form of Select statement:

select col1, col2... // Column name to find
 from table_name // Which table do you want to search
where condition // Set search criteria
group by col... // Set grouping
having condtion // Set grouping conditions
order by col... // Set sort

The following examples illustrate the specific usage:
(1) Query all records of all employees

 select * from emp 

(2) Query employee number, name, monthly salary, bonus and annual salary

select empno, ename, sal, comm, sal*12 from emp; 

(3) Use alias for example (2)

select empno, ename, sal as wages, comm bonus, sal*12 "Annual salary" from emp; 

(4) Query employee number, name, monthly salary, bonus, annual salary and annual income

select empno, ename, sal wages, comm bonus, sal*12 "Annual salary", sal*12+nvl(comm, 0) "annual income" from emp;//nvl(a, b). If a is empty, take the value of B; A is not empty, take the value of A

(5) View different department numbers in the employee table

select distinct deptno from emp;//distinct de duplication, i.e. no duplicate data

1. View different types of work in different departments and demonstrate the scope of distinct

select distinct deptno, job from emp;//distinct scope: applies to all subsequent columns

(7) The output calculation expression demonstrates the system time and pseudo table

select 3+20*5, sysdate from dual;//sysdate displays the current time, dual: pseudo table. In order to meet the SQL syntax, the from must be followed by the table name

2, Conditional filtering

Basic structure:

select .... from table where condtion;

The specific usage is as follows:
(1) Query the employee information of department 10

select * from emp where deptno = 10;

(2) Query the employee information whose name is KING

select * from emp where ename = 'KING'; //Case sensitive here

(3) Find information about employees whose salary is not equal to 1250

select * from emp where sal != 1250;
select * from emp where sal <> 1250;//The two are equivalent

(4) Query the employee information whose employment date is November 17, 1981

select * from emp where hiredate = '17-11 month-1981' ;//Note the date format NLS_DATE_FORMAT, DD-MON-RR by default, can also be set by yourself

(5) Query employee information with salary between 1000-2000 > > = <<=

select * from emp where sal>=1000 and sal<=2000; 
select * from emp where sal between 1000 and 2000;//The two are equivalent

3, Logical operators: or and not

(1) Query the employee information of department 10 or department 20

select * from emp where depton = 10 or depton = 20 ;

(2) Query the information of employees whose salary is 1300 in department 10

select * from emp where depton = 10 and sal = 1300 ; 

(3) Query February 81 (including February) to February 82 (excluding February)

select * from emp where hiredate >= '01-2 month-81' and hiredate < '01-2 month-82' ;//In this case, you cannot use between and to query

Pay attention to the date format. Don't add 0 in front of the month, or an error will be reported
(4) Query employee information with empty bonus

select * from emp where comm is null; //If you judge whether it is empty after the where condition, you should use is or is not, not = or=

(5) Description of short circuit between and or
For and, it is necessary to put the most false on the right, because once the right side of and is false, it will not judge the true and false on the left side of and, so the efficiency can be improved; Similarly, or put the easy to appear on the far right
(6) Query the employee information of department 10 and department 30 with salary of 1250

select * from emp where (deptno=10 or deptno=30) and sal=1250;//In the where condition with or and, and has higher priority than or

Note: when there is or in the where conditional expression, it should be enclosed with ().

4, Set operators: in and not in

(1) Query employee information whose department number is 10 or 20

select * from emp where deptno in (10, 20, null); 

(2) Query employee information not in departments 10 and 20

select * from emp where deptno not in (10, 20);

be careful:

select * from emp where deptno not in (10, 20, null);

No records. Because the previous sentence is equivalent to

select * from emp where deptno!=10 and deptno!=20 and deptno!=null;

deptno!=null is equivalent to null, because and, the whole condition is false. If you search according to the empty condition, you will not find the record naturally
Summary: use in in the set, not not in the set
Null can appear after in; Do not appear null in the collection after not in.

5, like fuzzy search

(1) Query employee information whose initial letter is S

select * from emp where ename like 'S%';

(2) Query employee information starting with employee number 79

select * from emp where empno like '79%';

(3) Query employee information whose name is four letters long

select * from emp where ename like '____';

(4) Query employee name band_ Employee information for

select * from emp where ename like '%\_%' escape '\'; // Escape '\' indicates that \ is an escape character