MySQL Learning Note 1-----subqueries

1. What is a subquery?

In layman's words, there are queries in the query, and there are multiple SELECT statements in the SQL statement.

2. Where can I embed a subquery?

SELECT column (not within the standard)

From table (can be embedded, exists as a table)

WHERE condition (can be embedded, exists as a condition)

3. Illustrative examples

If there is an EMP at this time, the employee's number, name, job, salary and department number are recorded.

The table structure is as follows:

Inquire about employees with the highest pay

SELECT * from emp WHERE sal = (SELECT max (SAL) from EMP); At this point the subquery is nested behind the where.

Name and employee number of the employee who queried the department number 30

Select E.empno, E.ename from (SELECT * from emp WHERE deptno =) e; At this point the subquery is embedded in the From, where you can also use the Where condition to achieve the same effect.

Find employee information for wages greater than 39 of all employees

SELECT * from emp where sal > All (select Sal from emp where deptno = 30);

Query employee information for wages greater than any manager's salary

SELECT * from emp where sal > No (select Sal from emp where job= ' manager ');

MySQL Learning Note 1-----subqueries