DQL - MySQL

1. Basic Query SQL syntax

Get all of the data

SELECT <field_name | *> FROM <table_name>;

• * means everything in a table
• field_name can be multiple, seperated by ,

Get data based on condition

• By using WHERE to define a condition, to filter the data we want

SELECT <field_name | *> 
FROM <table_name> 
WHERE <condition>;

• <condition> can have multiple.
• By using AND, OR to join the condition / requirments.

2. GROUP BY (分组聚合 (Data Aggregation) )

Syntax

SELECT <field>, <Aggregation_Function(field)> 
FROM <table_name> 
WHERE <condition> 
GROUP BY <field_name>;

• For <field>, <Aggregation_Function(field)> will return a table with label + data of <field>, <Aggregation_Function(field)>
• Ex: name, AVG(age) will have 2 field will returned, which is name, avg(age)
• IF GROUP BY is applied, it will render the group-by-field to first, then <field>, <Aggregation_Function(field)>.

Aggregation / polymerization function

Aggregation / polymerization function 聚合函数	Usage
SUM(field)	Get SUM of the value
AVG(field)	Get the AVERAGE number value
MIN(field)	Get MINIMUM value
MAX(field)	Get MAXIMUM value
COUNT(field / *)	Get AMOUNT of record

3. ORDER BY [ASC / DESC]

SELECT <field>, <Aggregation_Function(field)> 
FROM <table_name> 
WHERE <condition> 
GROUP BY <field_name>
ORDER BY <DESC | ASC>;

• ASC means ascending (from 0 -> n), it's also default value
• DESC means descending (from n -> 0)

4. LIMIT

• Use for pagination or limit the data returning from query.

SELECT <field>, <Aggregation_Function(field)> 
FROM <table_name> 
WHERE <condition> 
GROUP BY <field_name>
ORDER BY <DESC | ASC>
LIMIT n[, m];

• LIMIT can either is number or range.
• Ex: LIMIT 5, limiting 5 records will be retuned.
• LIMIT 10, 5, means from 10th records of data, take 5 records after 10 (11, 12, 13, 14, 15)