MySQL: Sub-query

For the following table,

1. Scenario: Query the teacher's information for the maximum number of days to substitute.

Method One: Select% from teacher order BY day DESC LIMIT 1;

There is a loophole in this approach: the teacher with the most days of teaching actually has two people: Hanna and Luna.

Setting limit 1 directly restricts the output to only 1 teachers. And actually we do not know that there are a few substitute the most teachers, do not know how to set limit.

"Improvement" is done in two steps:

First step: Get the maximum number of days for a substitute day: select Max from teacher;

The second step: Judge which teacher's substitute days and the maximum value is the same.

MySQL allows you to save the query result of the first step as a value using:

var1 = select Max (days) from teacher;

SELECT * from teacher where days = var1;

The above two sentences are equivalent to select * from teacher where days = (select Max (days) from teacher);

"Definition" if a query statement appears inside another statement (not necessarily a query statement) , the first statement is called a subquery .

Requirement: The subquery statement must use parentheses.

Pros: You can split the target into several steps.

2. Classification criteria (different classifications, there will be different ways of using).

① the location where the subquery appears.

· Where type: appears after the where; From type: appears after from; • Exists type: appears after exists.

The return value form of the ② subquery.

· Single value: • A column: · Multiple columns: • Table (multiple rows and columns).
3. how to use .

① scalar Quantum query.

② Query (Use the collection class's operator to complete in-not-| any | all | some).

"Give me a chestnut" retrieves all the class information taken by the teachers who have taken the ' php0228 ' class.

"Analysis" The first step: Select T_name from teacher where c_name= ' php0228 ';

Step two: Select T_name,c_name,days from teacher where T_name in (select T_name from teacher where c_name= ' php0228 ');

Tip: = any equivalent in; ! = all equals not in

③ Returns a row (limit 1):

"Give me a chestnut" find a teacher with 0331 classes, with Linda having the same substitute days.

"Analysis" select T_name,gender,c_name from teacher where (gender,c_name) = (select Gender,c_name from teacher where T_name= ' Linda ' and c_name= ' php0331 ');

These are called "row subqueries" and are less commonly used.

④ Returns a table:

SELECT * FROM (select T_name,c_name,days from teacher where days>15) as temp ;

If you write only (subquery), you return multiple lines, you need to name these lines, with as+ "temporary name".

Tip: The keyword as can be used to alias, for example select T_name as teach from teacher;//equivalent to T_name alias teach.

⑤exists subquery.

How to use: exists (subquery statement)

Judgment basis: If the subquery can return data, it is considered that the exists table swears to return true;

Otherwise, returns false.

MySQL: Sub-query