Querying the database, when the query conditions are more complex, often need to use subqueries. A subquery (subquery) refers to a SELECT clause that appears within other SQL statements. This article describes the subquery in detail
Defined
A subquery (subquery) refers to a SELECT clause that appears within other SQL statements
SELECT * from t1 WHERE col1 = (select col2 from T2);
where select * from T1, called the outer query (Outer query/outer Statement), select col2 from T2, called subquery (subquery)
When using subqueries, it is important to note that
1, subqueries are nested inside the query, and must always appear inside the parentheses
2. Subqueries can contain multiple keywords or conditions, such as distinct, GROUP by, ORDER by, LIMIT, function, etc.
3. The outer query of a subquery can be: SELECT, INSERT, UPDATE, set, or do
4. A subquery can return a value: scalar, row, column, or subquery
Comparison operators
Using a comparer is one of the sub-queries
Operand Comparison_operator subquery
Comparison operators include =,! =, <>, <=>, >, <, >=, <=
Data preparation
Download data files, set up database, data tables, and deposit corresponding records
Average price for all computer products, and retain two decimal places, AVG, MAX, MIN, COUNT, sum as aggregate functions
Note AVG () is a function for averaging
Find all items with a price greater than the average price
SELECT Goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price > 5391.30;
Using subqueries to achieve the same requirements
Select Goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price > (SELECT ROUND (AVG. Goods_price), 2) from Tdb_ Goods);
The price of the item with the query type "super-billed"
Search for items with a price greater than or equal to the "super" price
The system prompts for an error, and the subquery returns more than one row because the subquery has 3 results, and select cannot know which is greater than the 3 results in the subquery. So, then, you need to use the following modifier keywords
Modifier keywords
The modifier keywords include any, some, all three, which can be used if the subquery returns multiple values
Operand comparison_operator any (subquery) operand comparison_operator SOME (sub-query) operand comparison_operator All (sub-query)
Case Processing
1, the operator is > or >=, when using the Any keyword, represents greater than the minimum value in the subquery results
Select Goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price > No (select Goods_price from Tdb_goods W Here goods_cate = ' Super Ben ');
The result shows that the value returned is greater than 4299, which is the minimum value
2, the operator is > or >=, when using the ALL keyword, represents greater than the maximum value in the subquery results
3, the operator is < or <=, when using the any or some keyword, represents less than the maximum value in the subquery results, when using the ALL keyword, is less than the minimum value in the subquery results
4, operator =, when using any or some keyword, is equal to any value in the subquery results, when using the ALL keyword, the return of the empty
[NOT] Inch
Operand comparison_operator [NOT] in (subquery)
The second seed query is a subquery thrown by in or not, and is basically the same as the method used by the comparison operator
where the = any operator is equivalent to IN,! = ALL or the <> all operator is equivalent to not in
[NOT] EXISTS
The third seed query is a subquery that is raised by exists or not exists. exists returns TRUE if the subquery returns any rows, otherwise false is returned
Store query Results
We can store the query results uniformly in a new data table, without needing to be entered in a single line.
Below, first create a "Product Classification" empty table
Then, query all records for the Tdb_goods table, and GROUP by category
Write grouped results to the "Product Classification" datasheet
INSERT [into] tbl_name [(col_name),...)] SELECT ...;
-Reprint
Sub-query of front-end learning Database