1. Query introduction

2. Comparison, logical operation

Writing paradigm

SELECT <Column name 1>,<Column name 2>, <Column name 3>, ......
  FROM <Table name>
 GROUP BY <Column name 1>, <Column name 2>, <Column name 3>, ......;

among

The column names in select < column name 1 >, < column name 2 >, < column name 3 > can be used with addition, subtraction, multiplication and division

Examples of addition, subtraction and multiplication are as follows:

SELECT (sale_price-purchase_price) as 'profit',
(sale_price*0.8-purchase_price) as 'Profit after 20% discount',
(sale_price-purchase_price)/purchase_price as 'profit margin'
  FROM product;

2.1 comparison symbols

Compared with C language, there are only two differences, as follows

• Equal to... An equal sign =, No==
• And... Are not equal to < >, no=

give an example

SELECT product_type,regist_date from product
where regist_date <> '2009-01-08' ;-- The query registration date is not 2009-01-08 Product type

2.2 logical operators

• Symbol, word meaning, mathematical meaning
• Intersection of and
• Or, or, collection
• not, no, reverse

Logical symbol, and, example

SELECT product_type,regist_date from product-- Query 2009-01-08 By 2009-06-08 Registered product type
where regist_date > '2009-01-08' and regist_date < '2009-06-08'; 

Logical symbol, or, example

SELECT product_type,regist_date from product-- Query 2009-01-08 Before or 2009-06-08 Product type registered later
where regist_date < '2009-01-08' or regist_date > '2009-06-08'  ;

Logical symbol, not, example

SELECT product_name, product_type, sale_price
  FROM product
 WHERE NOT sale_price >= 1000;
 -- Equivalent to
 SELECT product_name, product_type, sale_price
  FROM product
 WHERE  sale_price < 1000;

3. Aggregate function

-Count: counts the number of rows of a record in the table, and does not count the rows of null

• Sum: calculate the sum of the data of an attribute (column). It is only suitable for numeric types
• avg: calculates the average value of an attribute (column) data. It is only suitable for numeric types
• max/min: calculates the maximum value of an attribute (column) data. It is only suitable for numeric types
• Note that duplicate data can be eliminated for recalculation, such as sum(distinct sale_price)

-- Calculate the number of rows of all data (including NULL)
SELECT COUNT(*)
  FROM product;
-- calculation NULL Rows of data other than
SELECT COUNT(purchase_price)
  FROM product;
-- Calculate the total value of sales unit price and purchase unit price
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;
-- Calculate the average value of sales unit price and purchase unit price
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;
-- MAX and MIN It can also be used for non numeric data
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;

4. Grouping and sorting

The most important thing is to understand the execution order of select:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

After understanding this order, you can understand that the alias named in select cannot be used in group by because the computer has not read the alias information in the clause before the selection is executed. order by can use the alias because the computer has already executed the selection.

For example, query commodity types whose total sales unit price (sales_price column) is greater than 1.5 times the total purchase unit price (purchase_price column), and arrange them in descending order based on the sales unit price (sales_price column).

The writing order is as follows

select product_type, sum(sale_price) as 'Sales unit price and',sum(purchase_price) 
 from product
 group by product_type
having sum(sale_price) > sum(purchase_price) *1.5 
order by 'Sales unit price and' desc;

Execute according to

 from product
 (where)
 group by product_type
having sum(sale_price) > sum(purchase_price) *1.5 
select product_type, sum(sale_price) as 'Sales unit price and',sum(purchase_price) 
order by 'Sales unit price and' desc;

First find the table product, where has no content, that is, there are no filter conditions for the table data. Take product_type as the statistical grouping target, and make requirements for the grouping: the sum of sales unit price is greater than the sum of purchase unit price. The attributes to be displayed in the query are product_type, sum (sale_price) as, sum (purchase_price) , the final displayed items are processed in descending order based on 'sales unit price and'.

4.1 grouping by

give an example

SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = 'clothes'
 GROUP BY purchase_price;

Among them, using having to filter grouped information is equivalent to where. The difference lies in the different objects. Having, grouped information; where, ungrouped table data

4.2 sorting order by

desc decreases and asc increases

give an example

select *  from product
order by regist_date desc,sale_price asc;