Child elements of the front-end learning Database

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

Child elements of the front-end learning Database