Compound query

The query of mysql table mentioned before is to query a table, but it is not enough in the actual development.

select * from where (sal>500 or job='MANAGER')and eame like 'J%'

Sort by department number in ascending order and employee's salary in descending order

select * from EMP rorder by deptno,sal desc;

Sort by annual salary in descending order

select ename,sal*12+ifnull(comm,0) as'Annual salary'from EMP order by Annual salary desc;

Display employee information with salary higher than average

select ename,salfrom EMP where sal>(select avg(sal)from EMP);

Show average and maximum wage for each department

select deptno,format(avg(sal),2),max(sal) from EMP group by deptno;

multi-table query

In actual development, data often comes from different tables, so multi table query is needed
case analysis
Display the employee name, employee salary and the name of the Department. Since the above data is from the EMP and DEPT tables, you need to query jointly

In fact, all you need is the record of deptno = deptno field in emp table

select EMP.ename,EMP.sal,DEPT.dnamefrom EMP,DEPT where EMP.deptno=DEPT.deptno;

Display department name, employee name and salary with department No. 10

select ename,sal,dname from EMP,DEPT where EMP.deptno=DEPT.deptno and DEPT. deptno=10;

**

Self connect

Self join refers to joining queries in the same table
Case:
Display the number and name of the superior leader of the employee FORD (mgr is the number of the employee leader – empno)
Subqueries used

select empno,ename fromemp where emp.empno=(select mgr from emp where ename='FORD')

Use multi table query (self query)

select leader.empno,leader.ename from emp leader,emp worker where leader.empno=worker.mgr and worker.ename='FORD';

Subquery
A subquery is a select statement embedded in other sql statements, also called a nested statement
Single line sub query: returns the sub query of multi line records
In keyword: query the name, position, salary and department number of the same employee working in department 10, but do not include 10's own

select ename,sal,deptno from EMP where sal>all(select salfrom EMP where deptno=30);

Multiple-column subqueries
Single row sub query refers to the sub query that only returns single column, single row data, and multi row sub query refers to the return of single column and multi row data, both for single column
Case: query all employees with the same department and position as SMITH, excluding SMITH

select ename from EMP where(deptno,job)=(select deptno,job from EMP where ename='SMITH')and ename<>'SMITH';

Using subqueries in the from clause
The sub query statement appears in the from clause. Here we need to use the data query technique to use a sub query as a temporary table
case
Display the name, Department, salary, average salary of the employee who is higher than the average salary of his / her department

select ename,deptno,sal,format(asal,2)from EMP,
(select avg(sal) asal,deptnodt fromEMP group by deptno)tmp
where EMP.sal>tmp.asal and EMP.deptno=tmp.dt;

Find the name, salary, department and maximum salary of the person with the highest salary in each department

select EMP.ename,EMP.sal,EMP.deptno,ms from EMP,
(select max(sal)ms,deptno from EMP group by deptno)tmp
where EMP.deptno=tmp.deptno andEMP.sal=tmp.ms;

Display information (Department name, number, address) and number of personnel for each department

Merge query
In practice, in order to merge the execution results of multiple select, you can use the set operator union,union all
union
This operator is used to get the union of two result sets. When this operator is used, the duplicate rows in the result set will be automatically removed
Case:
Find out the person whose salary is more than 2500 or whose position is MANAGER

select ename,sal,job from EMP where sal>2500 union
select ename,sal,job from EMP where job='MANAGER';

union all
This operator is used to get the union of two result sets. When this operator is used, duplicate lines in the result set will not be removed
Case: find out the person whose salary is more than 25000 or whose position is MANAGER

select ename,sal,job from EMP where sal>2500 union all
select ename,sal,job from EMP where job='MANAGER';