query

Simple query

Query all

mysql:

SELECT e.* From emp e

oracle:

SELECT e.* From emp e;

Query specific columns

mysql:

SELECT e.empno,e.ename,e.job from emp e

oracle:

SELECT e.empno,e.ename,e.job from emp e

Alias use

mysql:

select  e.empno 'Employee number',e.ename 'Employee name',e.job 'Employee position',e.mgr 'Superior leaders',
       e.hiredate 'Entry time',e.sal 'salary',e.comm 'bonus',e.deptno 'Department'
       from emp e

oracle:

select  e.empno 'Employee number',e.ename 'Employee name',e.job 'Employee position',e.mgr 'Superior leaders',
       e.hiredate 'Entry time',e.sal 'salary',e.comm 'bonus',e.deptno 'Department'
       from emp e

De duplication (use the keyword distinct)

Only when all the queried columns are the same can they be merged
mysql:

select  distinct  job from emp;

oracle

select  distinct  job from emp;

String join query

mysql:

select concat('Employee number is:',empno,'The name is:',ename,'Our work is:',job)
    'Employee's name is: work of' from  emp;

oracle:

select 'Employee number is'|| empno||'The name is:'||ename||'Our work is:'||job from  emp;

Four operations in query

Multiplication (the rest is the same)

mysql:

select ename,sal*12 from emp

select concat('The name is:',ename,'The annual salary is:',sal*12) as 'Name is: annual salary is:  ' from emp;

oracle:

select ename,sal*12 from emp

select concat('The name is:',ename,'The annual salary is:',sal*12) as 'Name is: annual salary is:  ' from emp;

Restricted query

Query all employees with salary greater than 1500

mysql:

select  ename 'full name',e.sal 'salary' from emp e where e.sal>1500

oracle:

select  ename 'full name',e.sal 'salary' from emp e where e.sal>1500

And non empty restrictions

Is not and is null. If it is not, it is not

SELECT *from emp WHERE comm is not null;
SELECT *from emp WHERE not(comm is null);

mysql:

select *from  emp where comm is not null

oracle:

select *from  emp where comm is not null

Multi condition query

Connect using and

mysql:

 select  *from  emp where sal>1500 and comm is not null;

oracle:

 select  *from  emp where sal>1500 and comm is not null;

Connect using or

 select  *from  emp where sal>1500 orcomm is not null;

oracle:

 select  *from  emp where sal>1500 or comm is not null;

Usage of between and

As like as two peas, sql is the same as the query result.

mysql:

SELECT *from emp WHERE sal>=1500 AND sal<=3000;
SELECT *from emp WHERE sal BETWEEN 1500 and 3000;

oracle:

SELECT *from emp WHERE sal>=1500 AND sal<=3000;
SELECT *from emp WHERE sal BETWEEN 1500 and 3000;

Usage of IN

Example: query the specific information of the employee number whose employee number is 736974997521
The following two sql statements are identical in query results
You can also use not in

mysql:

select *from  emp where empno='7369' or empno='7499' or empno='7521';

select *from emp where empno in('7369','7499','7521');
select *from emp where empno not in('7369','7499','7521');

oracle:

select *from  emp where empno='7369' or empno='7499' or empno='7521';

select *from emp where empno in('7369','7499','7521');
select *from emp where empno not in('7369','7499','7521');

wildcard

In common sites, there are often fuzzy queries, that is, enter a keyword, query all the matching contents, and complete them with LIKE statements in sql.
The following two wildcards are mainly used in LIKE
'%': can match any length of content
“_”: Can match a length of content
Example: find out the employees whose second character contains "M" in the names of all employees
mysql:

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

oracle:

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

In LIKE, if there is no keyword, it means to query all

Sort result set

In sql, you can use ORDER BY to sort query results
Syntax: SELECT * | column name FROM table name {WEHRE query criteria} ORDER BY column name 1 ASC|DESC, column name 2... ASC|DESC
Example: query employee's salary from low to high
Analysis: the default sorting rule for ORDER BY column names is ascending. ASC can not be specified. DESC must be specified if the column names are arranged in descending order
mysql:

# Ascending order
select *from emp order by sal asc;
# Descending order
select *from emp order by sal desc;

oracle:

# Ascending order
select *from emp order by sal asc;
# Descending order
select *from emp order by sal desc;

If there are multiple sort fields, they can be separated by commas

select *from emp order by sal asc,hiredate desc ;

Note that the ORDER BY statement should be executed at the end of the sql.