I Aggregate query

Aggregate function	meaning
count()	Calculate the number of records (rows) in the table
sum()	Calculate the total value of the values in the value column in the table
avg()	Calculate the average of the data in the value column in the table
max()	Find the maximum value of data in any column in the table
min()	Find the minimum value of data in any column in the table

1.count() function

--count()Function example
select count(*)
    from product;
/* *Sign indicates all columns
count(*)Rows with null key value are included in the calculation */
select count(sale_price)
    from product;
--In the above routine count(sale_price)The key value is not calculated during calculation null Line of

2.SUM() function

--example
select SUM(sale_price)
    from product;
/*
SUM The function will directly exclude null values when calculating columns with null values
*/

3.AVG() function

--example
select AVG(sale_Price)
    from product;
/*
null values are excluded from calculation and do not participate in calculation
*/

4.MAX() and MIN() functions

--example
select MAX(sale_price),MIN(sale_price)
    from product;

Note: in principle, the MAX() and MIN() functions can be applied to columns of any data type

Note 2: SUM() and AVG() Korean are only applicable to columns of data type

5. Use distinct in aggregate function

select count(distinct product_type)
    from product;
--Calculated that there are several different product_type

II Group tables

1.group by clause

select <Column name 1>,<Column name 2>,<Listing 3>....
    from <Table name>
    group by <Column name 1>,<Column name 2>,<Listing 3>...；

example

--Example 1
select product_type, count(*) as 'total'
    from product
    group by product_type;
--Example 2
select product_type, product_name, count(*) as 'total'
    from product
    group by product_type, product_name;

Note: writing order of clauses

select clause - > from clause - > where clause - > group by Clause - > having clause - > order by clause

Note 2: when null key values are included, null is also regarded as a separate set of data

Note 3: two point column names other than aggregate keys cannot be written in the select clause

Note 4: alias cannot be used in group by clause

Note 5: the order of results obtained by the group by clause is random, and other clauses are needed to sort them.

Note 6: aggregate functions cannot be used in the where clause. In fact, aggregate functions can only be used in the select clause, the having clause and the order by clause

III Specify criteria for aggregate results

1.having clause (specify the condition of the group)

select <Column name 1>,<Column name 2>,<Listing 3>,...
    from <Table name>
    group by <Column name 1>,<Column name 2>,<Listing 3>...
    having <Grouping corresponding conditions>;

Note: the having clause must be after the group by clause

--example
select product_type, count(*)
    from product
    group by product_type
    having count(*)=2;

Note: the constituent elements of the having clause: common attribute, aggregate function, column name specified in group by (aggregate key)

IV Sort the results order by clause

select <Column name 1>,<Column name 2>,<Listing 3>...
    from<Table name>
    order by <Sort benchmark 1>,<Sort benchmark 2>,...；

1. Ascending and descending order

select product_id, product_name,sale_price, purchase_price
    from product
    order by sale_peice asc;
--asc Ascending order
select product_id, product_name,sale_price, purchase_price
    from product
    order by sale_peice desc;
--desc Descending order

1.1 specify multiple sort keys

select product_id,product_name,sale_price, product_type
    from product
    order by sale_price asc, product_id desc;

Note: when the column where null is located is used as the sorting key, null will not participate in sorting, so it always appears at the beginning or end of the result

Note 2: aliases should not be used in the order by clause of the sort key

Note 3: sequence of program execution clauses

from clause - > where clause - > group by Clause - > having clause - > select clause - > order by clause

1.2 columns that exist in the table but are not included in the select clause can be used in the order by clause

select product_name, sale_price, product_type
    from product
    order by product_id;

1.3 you can also use the column number (the column number refers to the number corresponding to the columns in the select clause arranged from left to right, not the column number when creating the table)

--Column name assignment
select product_id, product_name, sale_price, product_type
    from product
    order by sale_price desc, product_id asc;
--Column number assignment
select product_id, product_name, sale_price, product_type
    from product
    order by 3 desc, 1 asc;
--select A change in the order of the columns in the clause results in a change in the result
select product_id, product_name, regist_date, product_type,sale_price
    from product
    order by 3 desc, 1 asc;