1, SELECT statement basis

select <Listing>,
    from(Table name)；
where <Conditional expression>

Correlation rule

• An asterisk (*) means all columns.
• Line breaks can be used in SQL at will, which does not affect the execution of statements (but empty lines cannot be inserted).
• When setting Chinese aliases, you need to use double quotation marks (").
• Use DISTINCT in a SELECT statement to remove duplicate rows.
• Comments are the parts of SQL statements that are used to identify descriptions or precautions. There are two kinds of "/ * * /" including 1-line comment "--" and multi line comment.

• -- When you want to query all columns, you can use asterisks representing all columns(*). 
  SELECT *
    FROM <Table name>;
  -- SQL Statement can be used AS Keyword sets an alias for a column (double quotation marks ("") are required when using Chinese).
  SELECT product_id     AS id,
         product_name   AS name,
         purchase_price AS "Purchase unit price"
    FROM product;
  -- use DISTINCT delete product_type Duplicate data in column
  SELECT DISTINCT product_type
    FROM product;

  2, Arithmetic and comparison operators

• Common rules

• Constants or expressions can be used in the SELECT clause.
• When using comparison operators, be sure to pay attention to the position of unequal sign and equal sign.
• In principle, the data of string type shall be sorted according to the dictionary order and shall not be confused with the size order of numbers.
• When you want to select NULL records, you need to use the IS NULL operator in the conditional expression. When you want to select a record that IS NOT NULL, you need to use the IS NOT NULL operator in the conditional expression

• -- SQL Operational expressions can also be used in statements
  SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
    FROM product;
  -- WHERE Clause can also be used in conditional expressions
  SELECT product_name, sale_price, purchase_price
    FROM product
   WHERE sale_price-purchase_price >= 500;
  /* Use unequal sign on string
   First create chars and insert data
   SELECT a SELECT statement greater than '2'*/
  -- DDL: Create table
  CREATE TABLE chars
  (chr CHAR(3)NOT NULL，
  PRIMARY KEY(chr));
  -- Select greater than'2'Of data SELECT sentence('2'As string)
  SELECT chr
    FROM chars
   WHERE chr > '2';
  -- selection NULL Record of
  SELECT product_name，purchase_price
    FROM product
   WHERE purchase_price IS NULL;
  -- Select not as NULL Record of
  SELECT product_name，purchase_price
    FROM product
   WHERE purchase_price IS NOT NULL;

  3, Aggregate query on table

• COUNT: calculate the number of records (rows) in the table
• SUM: calculate the total value of the data in the value column in the table
• AVG: calculate the average value 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

• Use aggregate functions to remove duplicate values

-- Calculate the number of data rows after removing duplicate data
SELECT COUNT(DISTINCT product_type)
 FROM product;
 
-- Whether to use DISTINCT Action difference during( SUM Function)
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
 FROM product;

Common rules

• The result of the COUNT function varies according to the parameters. COUNT(*) will get the number of data rows containing NULL, while COUNT(< column name >) will get the number of data rows other than NULL.
• The aggregate function excludes NULL. The exception to COUNT(*) does not exclude NULL.
• The MAX/MIN function applies to columns of almost all data types. The SUM/AVG function is only applicable to columns of numeric type.
• When you want to calculate the type of value, you can use DISTINCT in the parameters of the COUNT function.
• You can remove duplicate data by using DISTINCT in the parameters of the aggregate function.

4, Group tables

GROUP BY statement

Previously, aggregation functions were used to process the data of the whole table. When you want to group and summarize (that is, summarize and count the existing data according to a column), GROUP BY can help you:

-- Statistical data lines by commodity type
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type;
 -- Not included GROUP BY
SELECT product_type, COUNT(*)
  FROM product

There are strict requirements for the clause writing order of GROUP BY. Failure to comply with the requirements will lead to the failure of normal SQL execution. The current clause writing * * * * order is:

1**.**SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

The first three items are used to filter data, and GROUP BY processes the filtered data

5, Specify criteria for aggregate results

HAVING features

The HAVING clause is used to filter groups, using numbers, aggregate functions, and column names (aggregate keys) specified in GROUP BY

-- number
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;
-- Wrong form (because product_name Not included in GROUP BY Aggregate key (in)
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING product_name = 'ball pen';

6, Sort query results

SELECT <Column name 1>, <Column name 2>, <Column name 3>, ......
  FROM <Table name>
 ORDER BY <Sort base column 1>, <Sort base column 2>, ......

The default is ascending order and DESC is descending order

-- Descending order
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;
-- Multiple sort keys
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price, product_id;
-- When the column name used for sorting contains NULL When, NULL It will be summarized at the beginning or end.
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY purchase_price;

As mentioned earlier in GROUP BY, the alias defined in the SELECT clause cannot be used in the GROUP BY clause, but the alias can be used in the ORDER BY clause. Why not in GROUP BY but in ORDER BY?

This is because when SQL uses the HAVING clause, the execution * * * * order of the SELECT statement is:

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

The execution order of SELECT is after the GROUP BY clause and before the ORDER BY clause. In other words, when using an alias in ORDER BY, you already know that the alias set in SELECT exists, but you don't know that the alias exists when using an alias in GROUP BY, so you can use an alias in ORDER BY, but you can't use an alias in GROUP BY

Author: Datawhale members: Wang Fuzhen, Yang Yu, Yan Zhongfeng, Yang Mengdi, Su Peng

https://tianchi.aliyun.com/forum/postDetail?spm=5176.20222307.J_9059755190.4.73dc4cb3zpexIX&postId=163422