Database principle and application (five) – aggregation query, sorting tables, grouping

This is the fifth day of my participation in Gwen Challenge

First, perform aggregate query on the table

Aggregation function

Note: in the aggregate function, except COUNT, NULL values are ignored in the calculation of other functions. The WHERE clause cannot use aggregate functions as function expressions.

Note: The MAX/MIN function works for almost all data types of columns. The SUM/AVG function only works with columns of numeric type.

example

SELECT COUNT(*) FROM product; SELECT COUNT(pur_price) FROM product; SELECT SUM(sale_price), SUM(pur_price) FROM product; SELECT AVG(sale_price), AVG(pur_price) FROM product; SELECT MAX(regist_date), MIN(regist_date) FROM product;Copy the code

-- is a comment statement in an SQL statement.

SELECT COUNT(DISTINCT PRj_num) FROM Out_stock;Copy the code

When you want to calculate the type of a value, you can use DISTINCT in the arguments of the COUNT function.

Using DISTINCT in the arguments of an aggregation function removes duplicate data.

Group tables

GROUP BY statement

SELECT < name >,... [n] the FROM < table name > GROUP BY < name >,... [n].Copy the code

If grouping statements are used, each column in the query list must be grouped by column or the aggregation function ！！！！

The group column contains NULL

SELECT score, COUNT(*)
  FROM student
 GROUP BY score;
Copy the code

NULL is treated as a special set of data

GROUP BY Writing position

There are strict requirements on the sequence of GROUP BY clauses. Failure to comply with the requirements will lead to SQL failure. The sequence of GROUP BY clauses is ****.

1**.**SELECT → 2. FROM → 3

The first three are used to screen data, and GROUP BY is used to process the screened data

Specify conditions for aggregation results

The HAVING clause is used to filter grouped results. It functions like the WHERE clause, but it is used for groups rather than individual records.

Example: Query the student id of a student who has taken two or more courses

SELECT sno
FROM SC
GROUP BY sno
HAVING COUNT(*)>=2 ; 
Copy the code

Fourth, sort the query results

Sort the format of the clause as follows: the ORDER BY < name > [ASC | DESC], [n]…

Example: Output student information in descending order

SELECT *
FROM s
ORDER BY sno DESC;
Copy the code

The ORDER of execution of SQL SELECT statements is as follows: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

SELECT is executed after the GROUP BY clause and before the ORDER BY clause. That is, the alias set BY SELECT is known to exist when the alias is used in ORDER BY, but not when the alias is used in GROUP BY, so the alias can be used in ORDER BY, but not **** in GROUP BY

Welcome to pay attention to my database learning column!

Database principles and Applications juejin.cn/column/6968…

The author is a sophomore student, welcome big guys to give advice!

Use Go to make a client based on UDP protocol server communication procedures | Go theme month juejin.cn/post/694581…

Use of GoBench [Go Study Notes] | Go Theme month juejin.cn/post/694346…

Database principle and application (three) – SQL statement (1) juejin.cn/post/696942…