Subquery of the front-end learning database and the sub-learning database

Subquery of the front-end learning database and the sub-learning database
* Directory [1] defines [2] comparison operator [3] modifier keyword [4] [NOT] IN [5] to store the query result IN front

When querying a database, subqueries are often required when the query conditions are complex. Subquery indicates the SELECT clause in other SQL statements. This topic describes subqueries in detail.

 

Definition

Subquery indicates the SELECT clause in other SQL statements.

SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

SELECT * FROM t1 is called the Outer Query (Outer Query/Outer Statement) and SELECT col2 FROM t2 is called the SubQuery)

When using subqueries, note that

1. subqueries are nested inside the query and must always appear in parentheses.

2. subquery statements can contain multiple keywords or conditions, such as DISTINCT, group by, order by, LIMIT, and functions.

3. The outer query of the subquery can be SELECT, INSERT, UPDATE, SET, or DO.

4. subquery can return values: Scalar, one row, one column, or subquery

 

Comparison Operators

The comparison operator is a type of subquery.

operand comparison_operator subquery

Comparison operators include = ,! =, <>, <=>,>, <,> =, <=

Data Preparation

Download data files, create databases, data tables, and store corresponding records

Calculate the average price of all computer products and retain two decimal places. AVG, MAX, MIN, COUNT, and SUM are Aggregate functions.

[Note] AVG () is a function used to calculate the average value.

Query all commodities whose prices are higher than the average price

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > 5391.30;

Achieve the same requirements through subqueries

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

Price of the item whose Query type is "supernote"

Query commodities whose prices are greater than or equal to the "super" Price

The system prompts an error. The subquery returns more than one row. Because the subquery has three results, the SELECT statement cannot determine which of the three results must be greater than the subquery. Therefore, we need to use the modified keywords to be introduced later.

 

Modify keywords

The modifier keywords include ANY, some, and all. If the subquery returns multiple values, you can use them.

Operand comparison_operator ANY (subquery) operand comparison_operator SOME (subquery) operand comparison_operator ALL (subquery)

Case handling

1. The operator is> or> =. When the ANY keyword is used, the value is greater than the minimum value in the subquery results.

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price> ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superice ');

The results show that all returned values are greater than 4299, that is, the minimum value.

2. The operator is> or> =. When the ALL keyword is used, the value is greater than the maximum value in the subquery results.

3. The operator is <or <=. When the ANY or SOME keyword is used, the value is smaller than the maximum value in the subquery result. When the ALL keyword is used, the value is smaller than the minimum value in the subquery result.

4. The operator is =. When the ANY or SOME keyword is used, it indicates that it is equal to ANY value in the subquery result. If the ALL keyword is used, null is returned.

 

[NOT] IN

operand comparison_operator [NOT] IN (subquery)

The second subquery is a subquery caused by IN or not in, which is basically the same as the method used by the comparison operator.

The = ANY operator is equivalent to the IN operator ,! = ALL or <> ALL operator is equivalent to not in Operator

[NOT] EXISTS

The third subquery is a subquery caused by EXISTS or not exists. If the subquery returns any row, EXISTS returns TRUE; otherwise, FALSE.

 

Store query results

We can store the query results in a new data table without entering them one by one.

Next, create an empty table named "item category"

Then, query all records in the tdb_goods table and group them by category.

Write the grouping result to the "product category" data table.

INSERT [INTO] tbl_name [(col_name),...)] SELECT...;