Summary of SQL statements (iii)--aggregation functions, grouping, subqueries, and combinatorial queries-darly

–count: Number of statistics rows

–sum: Gets the aggregate value of a single column

–avg: Calculating the average of a column

– Max: Calculating the maximum value of a column

–min: Calculating the minimum value of a column

First, create the data table as follows:

performs column, row count ( Count ):

Standard format

SELECT COUNT (< count specification >) from < table name >

Among them, the Count specification includes:

-*: Counts all selected rows, including null values;

-All column name: counts all non-null value rows for the specified column, or all if not written;

-DISTINCT Column Name: Counts the unique non-null value row for the specified column.

For example, how many students are in the class:

SELECT COUNT (*) from T_student;

You can also include screening criteria, such as the number of female students:

SELECT COUNT (*) from t_student WHERE student_sex= ' woman ';

If you want to calculate the number of classes, you need to use distinct:

SELECT COUNT (DISTINCT student_class) from T_student;

Distinct is the de-weight, if not added distinct the result is the number of table rows--5.

returns the column aggregate value ( SUM ):

Note: Sum as long as all with distinct two count specification, none *.

Calculate the sum of students ' ages:

SELECT SUM (Student_age) from T_student;

returns the column average ( AVG ):

Calculate the average age of students:

SELECT AVG (Student_age) from T_student;

returns the maximum value / Minimum Value ( Max/min ):

Ask for the oldest student information (minimum value same):

SELECT MAX (Student_age) from T_student;

Note: You can only find the maximum age, to show the oldest students all information, need to use the following sub-query.

Data Grouping ( GROUP by) :

Data in SQL can be grouped by column name, which is useful with aggregation functions.

example, count the number of each class:

SELECT student_class,COUNT(all student_name) as total number from t_student GROUP by (Student_class);

As is a definition alias, the use of aliases will have a good effect when combining and joining queries, and then again.

Filter conditions can also be added to a group where, but it is important to note that the order of execution is: where filter → group → aggregate function. Remember!

Statistics on the number of students over 20 years of age in each class:

SELECT student_class,COUNT(student_name) as total number from t_student WHERE Student_age > GROUP by (Student_class);

having Filter Conditions:

The order of execution of the grouping operation, aggregate function, where filter was mentioned before, so what if we want to execute the filter after aggregation?

For example, we would like to inquire about classes with an average age of 20 years

Can you use the following statement?

SELECT student_class, avg(student_age) from t_student WHERE avg (student_age) > GROUP by Student_class;

The result is an error. Because the aggregation function executes after the where, it is not done here to add the aggregate function in the Where judgment condition.

This can be done using haiving:

SELECT student_class,avg(student_age) as average age from t_student GROUP by (Student_class) has AVG(student_age) >;

One more word here.

SQL Order of execution:

– First step: Execute from

– Step two: Where Condition filtering

– Step three: Group by group

– Fourth step: Execute the Select projection column

– Fifth step: Having conditional filtering

– Sixth step: Perform ORDER by ordering

Sub-query:

Why sub-query?

A list of existing data is as follows:

According to the previous knowledge we can find out the highest score of each subject, but to find out the highest score of the student information will not be able to do. In this case, we need to use the subquery to get the complete information.

What is a subquery? A subquery is a query that is nested in the main query.

Subqueries can be nested in all locations in the main query, including SELECT, from, WHERE, GROUP by, have, ORDER by.

But not every location nested subqueries are meaningful and practical, here are some practical sub-queries to explain.

Existing table two: A student table, a class table. ID associated

in the SELECT nested in:

Student information and class names are located in different tables, in the same table to identify the student's school number, name, class name:

Select s.student_id,s.student_name, (select class_name from t_class C WHERE c.class_id=s.class_id) from t_student s GROUP by s.student_id;

* First this SQL statement uses an alias, which is written to add a character, such as from T_student S, after the form's table name, so that when you call a column of t_student, you can use S.STUDENT_ID to emphasize the table from which the corresponding alias is derived.

The application of aliases in sub-query and join query has a good effect, when two tables have the same column name or to enhance readability, to add a different alias to the table, it is good to distinguish which columns belong to which table.

There is also the case that when a subquery or join query, the main query and sub-query are the same table operation, the main, sub-query table with a different alias can be good to distinguish which column operations are in the main query, which the operation of the column is in the subquery, the following is an example description.

Then go back to the SQL statement above and you can see that the nesting of this note query is in the select position (enclosed in parentheses), and it is separated from the student's name by commas in the select position, that is, it is a column we want to find out,

The subquery finds that the class ID in the class table is the same row as the class ID in the student table, and note where c.class_id=s.class_id is a good embodiment of alias usage, distinguishing between two columns with the same column name in the table.

Results:

The final group by can be understood as the deduplication of repeated rows, if not added:

in the WHERE nested in:

We want to find out about the students with the highest C language scores:

SELECT * from t_student WHERE student_subject= ' C language ' and Student_score>=all ( SELECT student_score from t_student WHERE student_subject= ' C language ');

Results:

An all is present here, which is a subquery operator

Classification:

–all operator

And the results of the subquery are compared to each other, the value of the expression must be satisfied when it is true.

–any operator

And the results of a subquery are compared one after the other, where one record satisfies the condition the value of the expression is true.

–exists/not EXISTS operator

exists determines whether the subquery has data, and if it exists, the expression is true, and vice versa is false. Not exists opposite.

In a subquery or related query, the maximum value of a column is required, usually with all to the effect that a value larger than the other row is the maximum value.

To find out more about C-language scores than Lee Shigao students:

SELECT * from t_student WHERE student_subject= ' C language ' and Student_score > ( SELECT student_score from t_student WHERE student_name= ' John Doe ' and student_subject= ' C language ' );

By the above two examples, it should be possible to understand the role of subqueries nesting in where. The value of the column returned in the subquery is used as the comparison object, and the result is obtained by comparing it with different comparison operators in where.

Now we go back to the very beginning of the question, how to find out the top scores of each class of students information:

Select * from t_student s1 WHERE s1.student_score >= All (select s2.student_ Score from t_student S2 WHERE s1. ' Student_subject ' =s2.student_subject);

Here is the second usage of the aliases mentioned above, where the primary and sub-queries operate on the same table, distinguishing between the same column names in the inside and outside tables.

Results:

Sub-query classification:

– Correlated Subqueries

Executes data that relies on external queries.

The outer query returns one row, and the subquery executes once.

– Non-correlated sub-query

Subqueries that are independent of the external query.

The subquery is executed once, and the value is passed to the external query after execution is complete.

In the example above, the first example asks the student for the class name, which is the correlated subquery, where c.class_id=s.class_id is the relevant condition. Other examples only operate on a single table, for non-correlated subqueries.

It is important to note that the query executes once, and the subquery executes once, which is time consuming, especially when there is more data.

Combination query:

Two tables are joined vertically by the Union operator, in the basic way:

SELECT column 1, column 2 from table 1

UNION

SELECT column 3, column 4 from table 2;

UNION all is reserved for repeating rows:

SELECT column 1, column 2 from table 1

UNION All

SELECT column 3, column 4 from table 2;

Combinatorial queries are not too practical, so here is a simple mention, not an example.

As mentioned above, the relevant sub-query is not recommended to use, combined query and use less, that need to relate to multiple tables how do we do?

This is the key table join, join query for SQL to be detailed in the next blog post. The purpose of this blog is to set the basis for nested queries, aliases, and so on, after all, only the wording has changed, the idea is similar.

Summary of SQL statements (iii)--aggregation functions, grouping, subqueries, and combinatorial queries-darly