Shulou(Shulou.com)06/01 Report--

Today, the editor will show you how to analyze the problem of nested subqueries in Mysql. The knowledge points in the article are introduced in great detail. Friends who feel helpful can browse the content of the article together with the editor, hoping to help more friends who want to solve this problem to find the answer to the problem. Let's follow the editor to learn more about "how to analyze the nested sub-query problem in Mysql".

Mysql nested subquery

Sub-query refers to the nesting of several small queries with different functions in a complete query sentence, so as to complete a writing form of complex query together, in order to make readers more clear about the concept of sub-query.

The subquery returns the result

There are four types of data that can be returned by a subquery:

Single row, single column: returns the contents of a specific column, which can be understood as a single-valued data

Multiple columns in a single row: returns the contents of multiple columns in a row of data

Multiple rows and single column: returns the contents of the same column in multiple rows of records, which is equivalent to giving a range of operations

Multiple rows and columns: the result returned by the query is a temporary table

Using subqueries in the WHERE clause

Deal with single-row single-column subqueries, multi-row single-column subqueries, single-row and multi-column subqueries in the WHERE clause.

Single row and single column subquery

* * example 1. * * query the complete information of the lowest-paid employees in the company

-- query the complete information of the lowest-paid employees in the company SELECT * FROM emp eWHERE e.sal.sal= (SELECT MIN (sal) FROM emp)

* * example 2. * * query the information of all employees whose basic salary is lower than ALLEN

-- query the information of all employees whose basic salary is lower than ALLEN SELECT * FROM emp eWHERE e.sal (SELECT AVG (sal) FROM emp); single-row and multi-column subquery.

* * example 4. * * find out the information of all employees who are engaged in the same job as ALLEN and whose basic salary is higher than employee number 7521

Find out the information of all employees who are engaged in the same job as ALLEN and whose basic salary is higher than employee number 7521, SELECT * FROM emp eWHERE e.jb = (SELECT job FROM emp WHERE ename='ALLEN') AND e.sal > (SELECT sal FROM emp WHERE empno=7521)

* * example 5. * * query the information of employees who are in the same job and have the same salary as SCOTT.

SELECT * FROM emp eWHERE (e.jobree.sal) = (SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename'SCOTT'

* * example 6. * * query all employee information for the same job and leadership as employee 7566

-- query all employee information SELECT * FROM emp eWHERE (e.jobree.mgr) = (SELECT job,mgr FROM emp WHERE empno=7566) that are in the same job as employee 7566 and have the same leadership.

* * example 7. * * query the information of all employees who work in the same job as ALLEN and are hired in the same year (including ALLEN)

-query the information of all employees (including ALLEN) SELECT * FROM emp eWHERE (e.jobjournal toddchar (e.hiredatedtemplyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

Three main operators are used: IN, ANY, and ALL

IN operation

* * example 8. * * query all employee information that is the same as the minimum wage in each department.

-- query all employee information SELECT * FROM emp eWHERE e.sal IN (SELECT MIN (sal) FROM emp GROUP BY deptno) which is the same as the minimum wage in each department.

* * example 9. * * query all employee information that is not the same as the minimum wage in each department.

-- query all employee information SELECT * FROM emp eWHERE e.sal NOT IN (SELECT MIN (sal) FROM emp GROUP BY deptno) that are not the same as the minimum wage in each department; From subquery:

The data source followed by the FROM sentence is not only the data table, but also a query result, which is the From subquery.

Usually a multi-row and multi-column result set javascript of the return type of a From subquery

Find out the name of the position whose average salary is higher than 2000 and the average salary java of the position.

The first way: you can use HAVING words to implement SELECT job,AVG (sal) FROM empGROUP BY jobHAVING AVG (sal) > 2000; the second way: use the From subquery SELECT job,avgsalFROM (SELECT job,AVG (sal) AS avgsalFROM empGROUP BY job) tempWHERE temp.avgsal > 2000

Find out the information of employees who have commission and whose salary is higher than commission: web

SELECT e1.empno,e1.ename,e1.comm,e2.salFROM emp e1,emp e2WHERE e1.comm IS NOT NULL AND e1.comm

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.