Mysql from entry to enchantment-5. aggregation, grouping, sub-query

Mysql from entry to enchantment-5. aggregation, grouping, sub-query

❤ Series content❤

Mysql from entry to enchantment-1. Data preparation (
starting chapter) Mysql from entry to enchantment-2. Manipulating databases and tables
Mysql from entry to enchantment-3. Query, sort, and WHERE filtering
Mysql from entry to enchantment- 4. Wildcards, calculated fields, functions
Mysql from entry to enchantment-5. Aggregation, grouping, and sub-query
Mysql from entry to enchantment-6. Table connection, combined query (to be updated...)
Mysql from entry to enchantment-7 Insert, update and delete (to be updated...)
Mysql from entry to enchantment-8. Views, stored procedures, transaction processing (to be updated...)
Mysql from entry to enchantment-9. Cursor, advanced SQL features (to be updated... )
Mysql from entry to enchantment——10. Shorthand of knowledge points (end) (to be updated...)

Main content of this article

The use of six aggregate functions, ORDER BYdata grouping, subqueries (correlated subqueries and uncorrelated subqueries). The content is a little bit more, you need to calm down and watch it slowly. If possible, you can practice it (the database file is on the series homepage). Not much to say, let's get to the point!

1. Data aggregation

1.1 Aggregate functions

Let's take a look first, our commonly used aggregate function and its description.

AVG()Returns the average value of a column
COUNT()Returns the number of rows in a column
MAX()Returns the maximum value of a column
MIN()Returns the minimum value of a column
SUM()Returns the sum of values ​​in a column

1.1.1 AVG() function

Queries supplier idto 'DLL01'the average price of all commodities.

mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM Products
    -> WHERE vend_id = 'DLL01';
| avg_price |
|  3.865000 |

1.1.2 COUNT() function

Two ways of use:

  • COUNT(*)The number of rows in the table of counts, including a null value .

Query Customersthe number of customers in the table.

mysql> SELECT COUNT(*) AS num_cust
    -> FROM Customers;
| num_cust |
|        5 |
  • COUNT(column)Central Africa for specific columns NULLto count lines.

Query Customerstable of non-null number of mailboxes.

mysql> SELECT COUNT(cust_email) AS num_cust
    -> FROM Customers;
| num_cust |
|        3 |

1.1.3 MAX() function

When columnas a sequence of numbers, MAX(column)returns columnthe maximum column.

When columnas text data, MAX(column)return columncolumn data sorting the last row after.

1.1.4 MIN() function

When columnas a sequence of numbers, MIN(column)returns columnthe minimum value in the column.

When columnas text data, MIN(column)return columncolumn data sorted foremost row after.

1.1.5 SUM() function

SUM()Returns the value for the specified column and the (total) (ignoring the value of the column NULLlines).
Queries OrderItemsTotal of tables and. First calculate (quantity * item unit price), and then sum them up.

mysql> SELECT SUM(quantity * item_price) AS total_price
    -> FROM OrderItems;
| total_price |
|     5730.70 |

1.2 Combining aggregate functions

Calculate Productsthe number of items in the table, the price of the highest value, lowest and average.

mysql> SELECT COUNT(*) AS num_items,
    -> MIN(prod_price) AS price_min,
    -> MAX(prod_price) AS price_max,
    -> AVG(prod_price) AS price_avg
    -> FROM Products;
| num_items | price_min | price_max | price_avg |
|         9 |      3.49 |     11.99 |  6.823333 |

2. Data grouping

2.1 Data grouping

Use grouping to divide data into multiple logical groups, and perform aggregation calculations on each group . Use aggregate functions to be able

Count the number of products of each supplier.

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM Products
    -> GROUP BY vend_id;
| vend_id | num_prods |
| BRS01   |         3 |
| DLL01   |         4 |
| FNG01   |         2 |

group by Matters needing attention :

  • GROUP BY Can be used in nesting.
  • GROUP BYEach column listed in the clause must be a retrieval column or a valid expression (but not an aggregate function). If you SELECTuse an expression, you must GROUP BYspecify the same expression clause. Cannot use aliases .
  • In addition to gathering the outer calculation statements, SELECTeach column must be in the statement GROUP BYgiven in clause.
  • If the packet contains a column NULLline value, then NULLas a return packet. If multiple rows in the column NULLvalue, they will be divided into one group.
  • GROUP BYClause must appear WHEREafter the clause, ORDER BYclause before.

2.2 Filter group

Use HAVINGclause filtered after the data packet.

Example: Query Productstable has two more product and its price is greater than or equal to 4 of suppliers.

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM Products
    -> WHERE prod_price >= 4
    -> GROUP BY vend_id
    -> HAVING COUNT(*) >= 2;
| vend_id | num_prods |
| BRS01   |         3 |
| FNG01   |         2 |

WHEREThe HAVINGmain differences:

  • WHEREIs a constraint statement, HAVINGis a filter statement.
  • WHEREFilter HAVINGbefore data grouping, and filter after data grouping.

SELECT Order of clauses:

ClauseDescriptionDo you have to use
SELECTThe column or expression to returnYes
FROMThe table from which to retrieve dataOnly used when selecting data from the table
WHERERow-level filteringno
GROUP BYGroup descriptionOnly used when calculating aggregation by group
HAVINGGroup level filteringno
ORDER BYOutput sort orderno

3. Subqueries

3.1 Division of subqueries

According to the correlation of points: divided into relevant sub-queries and uncorrelated subqueries .

Related subqueries

Description: A subquery can be executed independently, and a query that does not depend on the parent query table is called an uncorrelated subquery.

Execution process:
(1) Execute the subquery first, and the result will not be displayed, but return to the external query as the query condition of the external query;
(2) execute the external query according to the result of the subquery.

Irrelevant subqueries

Description: A certain attribute column of the parent query needs to be used in the query conditions of the subquery. Such a query is called a related subquery. Correlated sub-queries cannot be executed independently, because it must contain a reference to the amount of meta-components in the external query table.

Execution process:
(1) Take out a tuple from the external query in order, and pass the relevant component value of the tuple to the subquery;
(2) Execute the subquery and get the result value;
(3) The external query returns according to the subquery result or a result set is determined whether the extracted row tuples satisfying the condition; if the outer whereclause returns a true value, indicating compliance; otherwise not, is discarded.
(4) Repeat the above walk until all tuples in the external lookup table have been processed.

In accordance with sub-queries where the location of points: WHEREor HAVINGafter, SELECTafter, FROMafter, EXISTSafter sub-queries.
The following will be introduced according to the location division, during which it will be marked whether the example is a relevant subquery.

3.2 Subquery after WHERE or HAVING

3.2.1 Single-row subquery

Find the order information with the earliest date in the order table. The subquery returns a row of query results. (This example is an irrelevant subquery)

mysql> SELECT *
    -> FROM Orders
    -> WHERE order_date = (SELECT MIN(order_date)
    ->                     FROM Orders);
| order_num | order_date          | cust_id    |
|     20006 | 2012-01-12 00:00:00 | 1000000003 |

3.2.2 Multi-row subqueries

For ordering goods RGAN01for all customers. Then subquery start OrderItemstable to find include RGAN01the order number of items, then in accordance with the order number Ordersto find the corresponding customer table id. (This example is an irrelevant subquery)

mysql> SELECT cust_id
    -> FROM Orders
    -> WHERE order_num IN (SELECT order_num
    ->                     FROM OrderItems
    ->                     WHERE prod_id = 'RGAN01');
| cust_id    |
| 1000000004 |
| 1000000005 |
IN / NOT INIn the list / not in the list
ANY / SOMECompare with a value returned by the subquery
ALLCompare all values ​​in the result of the subquery

Usage comparison :

  • SELECT ... FROM ... WHERE num > ANY(num1, num2)
  • Equivalent to
  • SELECT ... FROM ... WHERE num > num1 OR num > num2


  • SELECT ... FROM ... WHERE num > ALL(num1, num2)
  • Equivalent to
  • SELECT ... FROM ... WHERE num > num1 AND num > num2

Note :

  • In the SELECTstatement, subqueries always treated from the inside out.

3.3 Subquery after SELECT

If you want to display Customersthe total number of orders for each customer's table. First, Customersretrieve the customer list table, then for each customer retrieved, its statistics in Ordersthe number of orders table. (This example is a related subquery)

mysql> SELECT cust_name,
    ->  (SELECT COUNT(*)
    ->   FROM Orders AS ord
    ->   WHERE ord.cust_id = cust.cust_id) AS order_count
    -> FROM Customers AS cust;
| cust_name     | order_count |
| Village Toys  |           2 |
| Kids Place    |           0 |
| Fun4All       |           1 |
| Fun4All       |           1 |
| The Toy Store |           1 |

Note : The above example is used Ord.cust_idand cust.cust_id, instead of using cust_id, because there are two tables in the cust_idcolumn, where it is possible to confuse you must use this syntax when the column name.

3.4 Subquery after FROM

Treat the result of the subquery as a table, and must be aliased.
The following example may be a bit complicated, and it also involves the table connection that we will learn later. The connection method used here is internal connection, which is equivalent to the set conditions.

Queries goal for the US vendor name and product name it, we have a step by step analysis, the first query to get the first sub-suppliers through Vendorsthe table for the national USAsupplier information, and then sub-query results as a USA_Vendorstable, the Productstable and the table join , two tables coupling conditions vend_idequal columns. (This example is an irrelevant subquery)

mysql> SELECT vend_name, prod_name
    -> FROM Products, (SELECT *
    ->                 FROM Vendors
    ->                 WHERE vend_country = 'USA') AS USA_Vendors
    -> WHERE Products.vend_id = USA_Vendors.vend_id;
| vend_name       | prod_name           |
| Bears R Us      | 8 inch teddy bear   |
| Bears R Us      | 12 inch teddy bear  |
| Bears R Us      | 18 inch teddy bear  |
| Doll House Inc. | Fish bean bag toy   |
| Doll House Inc. | Bird bean bag toy   |
| Doll House Inc. | Rabbit bean bag toy |
| Doll House Inc. | Raggedy Ann         |

3.5 Subqueries after EXISTS (correlated subqueries)

Now we use EXISTSto implement the above INquery to achieve. That query items ordered RGAN01all customers.

mysql> SELECT cust_id
    -> FROM Orders AS ord
    ->               FROM OrderItems AS ori
    ->               WHERE ord.order_num = ori.order_num
    ->                AND prod_id = 'RGAN01');
| cust_id    |
| 1000000004 |
| 1000000005 |

That's all there is to this article, if it feels good. ❤ Click a like before leaving! ! ! ❤Follow

Insert picture description here

-up will continue to share the "Mysql From Getting Started to Enchanting" series of articles, if you are interested, you can click to pay attention to not get lost~.