Summary of SQL statements (iii)--aggregate functions, groupings, subqueries, and combined queries

Aggregation functions:

The aggregate functions provided in SQL can be used to count, sum, find the most value, and so on.

Classification:

–count: Count rows –sum: Gets the aggregate value of a single column –avg: Calculate the average of a column – max: Calculate the maximum value of a column –min: Calculate the minimum value of a column first, create the data table as follows: To perform a 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= ' female ';

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 total 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;

Return column average (AVG):

Calculate the average age of students:

SELECT AVG (student_age) from T_student;

Returns the maximum/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 >20 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) >20 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:

One more word here. execution Order of sql:– Step two: Perform from– second step: where conditional filtering – step three: Group by group – Fourth step: Execute Select Projection Column – Step fifth: Having conditional filtering – Sixth step: performing ORDER by ordering Sub-query:Why sub-query? The existing data sheet is as follows: We can find out the highest score of each subject according to the previous knowledge, but we can't do it if we want to find out the students who get the highest score. 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 nesting in select: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 B Y 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, you can see that the nesting of this note query is in the select position (enclosed in parentheses), it with the school number, student names separated by commas in the select position, that is, it is we want to find out a column, the subquery is detected, The class ID in the class table is the same row as the class ID in the student table, 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. Result: The final group by can be understood as the deduplication of repeated rows, if not added: nesting in Where: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 s Tudent_subject= ' C language ');

Results:

Here is an all, categorized as a subquery operator: the –all operator and the result of the subquery are compared one after the other, and the value of the expression must be satisfied when it is true. The –any operator and the result of the subquery are compared one after the other, where one record satisfies the condition and the value of the expression is true. The –exists/not EXISTS operator EXISTS Determines whether the subquery has data, if there is a true expression, and vice versa. 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:

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_su Bject ' =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 Execute data that relies on external queries. The outer query returns one row, and the subquery executes once.　　– The non-correlated subquery is independent of the subquery 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 1UNIONSELECT column 3, column 4 from table 2;

UNION all is reserved for repeating rows:

SELECT column 1, column 2 from table 1UNION allselect column 3, column 4 from table 2;

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