MySQL must know to learn notes (1)

Chapter 13

13.1 Data grouping

What if you want to divide the data into logical groups??

To group with data

13.2 Creating groupings

Use the GROUP BY clause. Group by instructs MySQL to group and then aggregates each group.

Note: When using GROUP BY, if an expression is used in Select, you must use the same expression in group by, not an alias

13.2 Filtering grouping

Because there is no grouping concept in the Where condition filter, there is a need to group filtering with having and having all the where operators supported.

The difference between having and where: where filter rows, having filter groups

13.4 Grouping and filtering

Order BY and GROUP by

In general, when you use group BY, you must also sort by the ORDER BY clause, and never rely on the default ordering of group by.

SELECT clause Order,

Chapter 14 Using Subqueries

14.1 Sub-query subquery

The concept of subqueries: queries nested within other queries.

14.2 Filtering with subqueries

In a SELECT statement, subqueries are always handled from inside out. But SQL statements that contain subqueries are difficult to debug and read

14.3 using subqueries as calculated fields

Correlated subqueries: Subqueries that involve external queries, which should use fully qualified column names in the where condition to prevent erroneous query results.

Common usage Scenarios:

The in operator in the WHERE clause, and the fill computed column

Chapter 15 Junction Table

15.1 Junction (join) Table

15.1.1 Why does a relational table appear?

Scenario Simulation: Saving data from related sources by category

Primary key primary key and foreign key foreign key concept: Primary key is uniquely identified

A foreign key is a column in a table that contains the primary key value of another table and defines the relationship between the two tables

How do I maintain referential integrity? By specifying a primary key and a foreign key implementation in the table definition.

15.2 Creating a junction

Emphasize again the importance of fully qualifying column names.

MySQL must know to learn notes (1)