Descriptions:
SELECT
- [ ALL | DISTINCT | DISTINCTROW ]
- The ALL and DISTINCT modifiers specify whether duplicate rows should be returned.
- ALL (the default) specifies that all matching rows should be returned, including duplicates.
- DISTINCT (DISTINCTROW) specifies removal of duplicate rows from the result set.
- DISTINCT can be used with a query that also uses WITH ROLLUP.
- select_expr [, select_expr] …
- Each select_expr indicates a column that you want to retrieve.
- Can be
col1_name - col2_name AS new_name
- There must be at least one
select_expr
-
- denotes select all select_expr
- [ FROM ]
table_references
table_references is returned from JOIN clause
- Even if only used
TableA, TableB or TableA
- [ WHERE condition]
- Indicates the condition or conditions that rows must satisfy to be selected.
- In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregated function (group).
- See Section 9.5, “Expressions”, and Chapter 12, Functions and Operators.
- Where col_name LIKE “XXX%”;
- [ GROUP BY {col_name | expr | position}, … [ WITH ROLLUP ]]
- SELECT CITY, COUNT(*) AS NumberOfVendors FROM VENDORS GROUP BY City;
- The Group By clause calculates the number of vendors in each city.
- Using Group By we don’t need to specify each city. The statement will find all cities in the City column.
- The Group By statement can contain multiple columns
- The Group By statement can be nested (group inside group)
- Most SQL implementations do not allow to group a column with variable length data type (we will cover data types later)
- Group By clause come after any WHERE clause and before any ORDER BY clause
- When using GROUP BY, instead of WHERE keyword, the HAVING clause should be used.
- If we use the WHERE condition, it will be applied before grouping.
- [ HAVING where_condition]
- [ ORDER BY {col_name | expr | position}
[ASC | DESC]
SELECT … INTO Statement