Select * from + name of the table;
Example: the select * from emp;
Select the column 1, column 2... The from + name of the table;
Example: the select ename, job from emp
Select the column 1, column 2... The from + name of the table where + conditions;
Please show the department number for 10 employees information
Select * from emp where deptno=10;
* *
Knowledge
Example:=, & gt; , & lt; , & gt;=, & lt;=, & lt;> ,!=
Please find the salary is more than 2500 employees information
Select * from emp where sal & gt; 2500;
Not in 20 staff number and department number
The select empno, deptno from emp where deptno & lt;> 20;
Not to take the is null is not null
Please show no bonus employee number, employee name, employee wages select a. *, empno employee number, ename, sal from
Emp where a comm is null;
Not in 20 staff number and department number
The select empno, deptno from emp where deptno & lt;> 20;
And/or/between the and in... Between
Department number of 20 staff, salary more than 2500 employee information
Select * from emp where deptno=20 and sal & gt; 2500;
Department number for 20 employees or pay more than 2500 employee information
Select * from emp where deptno=20 or sal & gt; 2500;
Practice
1. Salary is more than 1500 sales information SALESMAN
Select * from emp where job="SALESMAN" and sal & gt; 1500;
2. Salary employee information between 1000-2000
Select * from emp where sal between 1000 and 2000;
Note: between the and contain boundary value
3. The staff number in 7300-7500 between employee information
Select * from emp where empno between 7300 and 7500;
The calculation of numerical value type
+ - */()
Please show the salary of more than 10000 employees information
Select * from emp where sal * 12 & gt; 15000;
Connect the | |
The select empno, ename, empno | | ename from emp;
Employee number is: empno employees is: ename.
Select 'staff Numbers for:' | | empno | | 'staff is:' | | ename from emp;
Set in (the value 1, 2,...)
20 and 10 departments employee information
Method 1: select * from emp where deptno=10 or deptno=20;
Method 2: select * from emp where deptno in (10, 20).
Please according to type of work for the SALESMAN and CLERK staff Numbers, employee name
The select empno, ename from emp where job in (" SALESMAN ", "CLERK");
Any/all any/all
Any (1000200, 0300) is greater than the set of any equivalent to more than the minimum value
"Exact match
Please find the employees begin with S employee information
Select * from emp where ename like '% S';
Please find A beginning, the second from bottom is E employee information
Select * from emp where ename like 'A % E_';
9... Sort syntax
Select * from table name the where condition order by asc + column | desc ascending | descending) order by is sorted by the order listed after
Note: asc is the default, could not write a
Case 1: select * from emp where deptno=10 order by sal asc.
Example 2: according to the department, name ascending sort | descending order
Select * from emp order by deptno desc, ename desc;
Aggregation function
Count the sum () the avg () () () Max min ()
Example: 10 staff salary sum
Select sum (sal) from emp where deptno=10;
Practice:
The maximum of 20 department salary, salary sum, average wage
Select Max (sal), sum (sal), avg (sal) from emp where deptno=20;
O the bonuses of their staff combined
Select sum (comm) from emp;
For how many employees have bonus
Select count (comm) from emp;
Group
Select the column 1, column 2 aggregation function...
From the table name
The where condition
Group by column 1 and column 2...
Having condition
Note: 1) the group by the back of the column is a sequential
Summary
Select the column 1, column 2, aggregation function...
From the name of the table where conditions
Group by column 1 and column 2...
Having condition
The order by
Column 1 and column 2... Asc | desc;
Note: to filter the results after the group by having group by will