MySQL subquery detailed tutorial

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about subqueries, including nested subqueries after where, nested subqueries after from, Nesting subqueries and other contents behind select, let's take a look at it, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

Definition: Select statements are nested in select statements, and the nested select statements are subquery.

Subquery can appear in:

select 
	....(select)..
from 
	....(select)..
where 
	....(select)..

1. Nested subquery behind where

select * from emp where sal > (select avg(sal) from emp) ;

2. Nested subquery behind from *

SELECT d.dname,AVG(s.grade) '部门平均薪资' 
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal 
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname

3. Nest a subquery after select

Find out the department name of each employee

写法一：看作临时表
SELECT t.ename,d.dname
FROM dept d JOIN (SELECT e.ename,e.deptno FROM emp e) t ON t.deptno = d.deptno   

写法二：连接查询
SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno

写法三：select后嵌套子查询
SELECT e.ename,(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) AS dept FROM emp e ;

4.union

Can Add the query result sets

SELECT ename,job FROM emp e WHERE job = 'CLERK'
UNION
SELECT ename,job FROM emp e WHERE job = 'SALESMAN'

and the data in two unrelated tables can also be spliced ​​together. (But the number of columns remains the same)

SELECT ename FROM emp 
UNION
SELECT dname FROM dept

5.*limit

The key point, paging query depends on it

1) limit is unique to mysql, others There is no such mechanism in the database, but there is a similar mechanism called rownum in Oracle.

2) limit gets some data from the result set

3) Syntax mechanism: limit startIndex, length

startindex indicates the starting position, starting from 0, 0 indicates the first Article data.

length indicates how many to take.

4) limit is the last execution link in the sql statement.

(Current page number - 1) * Page size = Current page starting subscript

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL subquery detailed tutorial. For more information, please follow other related articles on the PHP Chinese website!