SELECT statement
Retrieves data from one or more tables.
Syntax:

SELECT [STRAIGHT_JOIN][ DISTINCT ][Alias.]Select_Item[ AS Column_Name][,[Alias.]Select_Item[ AS Column_Name]...]
FROM Table[ AS Alias]
 [[INNER | LEFT | RIGHT | FULL JOIN ]Table[[ AS ]Alias][ ON JoinCondition ¡­]
 [WHERE JoinCondition[ AND JoinCondition ...][ AND | OR FilterCondition[ AND|OR FilterCondition ...]]]
 
 [GROUP BY GroupColumn[,GroupColumn ...]]
 [HAVING FilterCondition]
 
 [UNION SELECTCommand]
 
 [ORDER BY Order_Item[ ASC | DESC][,Order_Item[ ASC | DESC] ...]] 
 [[INTO Destination] 

Note: the relative positions of the above clauses  are fixed!

Arguments

SELECT
Specifies the fields, constants, and expressions that are displayed in the query results.

SELECT *
 The character * expresses all fields of TableName.

STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. 

DISTINCT
Excludes duplicates of any rows from the query results.
 specifies an item included in the query results. An item can be one of the following:
    (a)The name of a field from a table in the FROM clause.
    (b) A constant specifying that the same constant value is to appear in every row of the query results.
    (c)An expression that can be the name of a user-defined function.

AS Column_Name or Column_Name=
You want to give the column a meaningful name,when Select_Item is an expression or contains a field function. 

FROM
Lists the tables containing the data that the query retrieves.

[AS] Alias
Specifies a temporary name for the table named in Table. When not specifying it,Huayi-SQL automatically set alias as a,b,c...; see example 8.

INNER JOIN specifies that the query result contains only rows from a table that match one or more rows in another table.

LEFT  JOIN specifies that the query result contains all rows from the table to the left of the JOIN keyword and only matching rows from the table to the right of the JOIN keyword. 

RIGHT JOIN specifies that the query result contains all rows from the table to the right of the JOIN keyword and only matching rows from the table to the left of the JOIN keyword.

FULL JOIN specifies that the query result contains all matching and non matching rows from both tables. .

ON JoinCondition specifies the conditions for which the tables are joined.

WHERE 
1. JoinContition
specifies fields that link the tables in the FROM clause. If you include more than one table in a query, you should specify a join condition for every table after the first.   
You must use the AND operator to connect multiple join conditions. Each join condition has the following form:

FieldName1 Comparison FieldName2
FieldName1 is the name of a field from one table, FieldName2 is the name of a field from another table, and Comparison is one of the following operators:

2.  FilterCondition:
specifies the criteria that records must meet to be included in the query results. You can include as many filter conditions as you like in a query, connecting them with the AND or OR operator. You can also use the NOT operator to reverse the value of a logical expression

FilterCondition can take any of the forms in the following examples:

(1) When omitting WHERE clause ,select all records in the table sepified in FROM clause .
(2)When the FilterCcondition includes IN, the field must contain one of the values before its record is included in the query results. example: WHERE DEPTNO NOT IN (10,20)
(3)The mode-match word [NOT] LIKE,example: WHERE ENAME LIKE 'CN%'
You can use the percent sign (%) and underscore ( _ ) wildcards as part of cExpression. The percent sign represents any sequence of unknown characters in the string. An underscore represents a single unknown character in the string.

the IS [NOT] NULL clause to check for an empty field.

GROUP BY
Groups rows in the query based on values in one or more columns. GroupColumn can be the name of a regular table field, a field that includes a SQL field function,or A numeric expression indicating the location of the column in the result table.(the leftmost column number is 1).

1. GROUP BY clause is usually used with the field fuctions.
Grouping is to calculate the result tuple from every group of tuples,so that the field fuctions are widely used to group.
2. Keep in mind that GroupColumn must be corresponding to SelectItem.
Besides the field fuctions, GroupColumn also can caculate the result tuple from every  group of tuples,other things can't be a part of the result tuple.

HAVING
Specifies a filter condition which groups must meet to be included in the query results. HAVING should be used with GROUP BY. It can include as many filter conditions as you like, connected with the AND or OR operators. You can also use NOT to reverse the value of a logical expression.

FilterCondition cannot contain a subquery.

A HAVING clause without a GROUP BY clause acts like a WHERE clause. You can use local aliases and field functions in the HAVING clause. Use a WHERE clause for faster performance if your HAVING clause contains no field functions. 

[UNION SELECTCommand]

Combines the final results of one SELECT with the final results of another SELECT. By default, UNION checks the combined results and eliminates duplicate rows. Use parentheses to combine multiple UNION clauses.

UNION clauses follow these rules:

ORDER BY
Sorts the query results based on the data in one or more columns. Each Order_Item must correspond to a column in the query results and can be one of the following:

      (1) A field in a FROM table that is also a select item in the main SELECT clause (not in a subquery).

      (2) A numeric expression indicating the location of the column in the result table. (The leftmost column is number 1.)

ASC specifies an ascending order for query results, according to the order item or items, and is the default for ORDER BY.

DESC specifies a descending order for query results.

Query results appear unordered if you don't specify an order with ORDER BY.

INTO Destination

Specifies a temporary table where to store the query results. 

Remarks

A subquery, referred to in the following arguments, is a SELECT within a SELECT and must be enclosed in parentheses. You can have up to two subqueries at the same level (not nested) in the WHERE clause. Subqueries can contain multiple join conditions.

 Distinguish the WHERE clause from the HAVING clause:
WHERE clause restricts the condition of the original table tuple,Having clause restricts the middle table after grouping,so that they are different and can't be interchanged.But there is a specifical example,HAVING clause only restricts the grouping attributes,you can change it to WHERE clause to increase the executing efficiency.

      (1) The following field functions are available for use with a select item that is a field or an expression involving a field:

      (2) AVG(Select_Item), which averages a column of numeric data.

      (3) COUNT(Select_Item), which counts the number of select items in a column. COUNT(*) counts the number of rows in the query output.

      (4) MIN(Select_Item), which determines the smallest value of Select_Item in a column.

      (5) MAX(Select_Item), which determines the largest value of Select_Item in a column.

      (6) SUM(Select_Item), which totals a column of numeric data.

Example 1
Basic Query
There is the following right table,to query the person whose age is more than twenty,use the following left statements:
                                                    PersonnelTable 

To show how convenient querying is,we list a database example which is the employee management database.The database only  contains the following two tables:
                       EmployeeTable(EMP)                                         DepartmentTable(DEPT)

Example 2
Display the deployees's names and salaries in DEPTNO 8.
          SELECT ENAME,SAL
          FROM EMP
          WHERE DEPTNO=8 AND JOB='operator'

Example 3
Display the message of the employees  whose salary is between 1000 and 2000.
            SELECT *
            FROM EMP
            WHERE SAL>=1000 AND SAL<=2000
                            Don't support the syntax:WHERE SAL BETWEEN 1000 AND 2000

Example 4
Display  the job without commision.
          SELECT DISTINCT JOB
          FROM EMP
          WHERE COMM IS NULL

Example 5
Order
List employees's message in DEPTNO 10 by a salary descending order .
       SELECT *
       FROM EMP
       WHERE DEPTNO=10
       ORDER BY SAL DESC

Example 6
Group 
Calculate the total salary and average salary of each department.
      SELECT DEPTNO,SUM(SAL),AVG(SAL)
      FROM EMP
      GROUP BY DEPTNO

Example 7
Calculate the total salary by job except manager of each department.
      SELECT DEPTNO,JOB,SUM(SAL)
      FROM EMP
      GROUP BY DEPTNO,JOB
      HAVING JOB!='manager'
      The above query is equals to the following:
      SELECT DEPTNO,JOB,SUM(SAL)
      FROM EMP
      WHERE JOB!='manger'
      GROUP BY DEPTNO, JOB

Example 8
Join
Query concerning two or more tables must use JOIN clause.
Where does Wang work?
To query the location where Wang works, the query must concern two tables:EMP and DEPT,JoinCondition is the same DEPTNO of the two tables.
      SELECT LOC
      FROM EMP AS a INNER JOIN DEPT AS b ON  a.DEPTNO=b.DEPTNO 
      WHERE ENAME='Wang'
Equal to   
      SELECT LOC
      FROM EMP,DEPT  
      WHERE a.DEPTNO=b.DEPTNO AND ENAME='Wang'

  Return