Getting Started with ASP (19)-SELECT statement

SELECT syntax

The complete syntax for SELECT in Access is as follows:

SELECT [predicate] { * | table. * | [table.] field1 [As alias1] [, [table.] Field2 [As Alias2] [, ...]]}
from tableexpression [, ...] [In externaldatabase]
[WHERE...]
[GROUPby ...]
[having ...]
[ORDERby ...]
[With Owneraccess OPTION]

• The predicate predicate, which is used to limit the number of records returned, can be all (default),DISTINCT,distinctrow , or TOP .
• *, specify all the fields in the table
• table, names of tables
• Field1, field2, field name
• ALIAS1, ALIAS2, as the name of the column header, which is the alias
• Tableexpression, name of the table
• Externaldatabase, external database

SELECT Basic Structure

There are several elements in the SELECT statement that must be written, and its basic structure is

SELECT  from table name

Fields and table names must be specified.

Example 1, we can also use the AS keyword as a field alias, the following query

SELECT  as  from Customer

The query results are as follows:

In Example 2, we need to select the Contact Name field from the Customer table and choose product name from the product table, with the following SQL statement:

SELECT from customer, product

Query results, the above query two tables without any restrictions, producing results for M * N combination of data results (m data in the Customer table, N data in the product table) are as follows:

Example 3, query the Customer table in the company address is "Shijiazhuang" company, here need to use where statement, as follows:

SELECT *  from Customer WHERE City =' shijiazhuang '

The City field is a text type, so the query uses single quotation marks to represent the string contents, and the query results are as follows:

Example 4, query the Customer table, the Contact field contains the words "Manager" record, query as follows:

SELECT *  from Customer WHERE  like ' * Manager * '

The query results are as follows:

We use the like keyword for fuzzy matching, and the following wildcard characters can be used in fuzzy matching:

• ? _ represents a single character
• *% represents any number of characters
• # represents a single number
• [charlist] Any single character in a character descriptor
• [!charlist] Any single character that is not in the character table

Example 5: We would like to get a list of products with a price range from large to small, so we can use this SQL statement at this time.

SELECT *  from Product ORDER  by DESC

The query results are as follows:

Example 6, select the first 10 rows of the product table, the SQL statement is as follows:

SELECT TOP Ten *  from Product

Select the first 10% records of the product table, and the SQL statements are as follows:

SELECT TOP Ten PERCENT *  from Product

Select the contents of the Shipper Area field in the Order table, remove duplicates, and the SQL statements are as follows:

SELECT DISTINCT  from order;

Example 7, we want to calculate the sales of each product according to the order schedule, the SQL statement is as follows:

SELECT SUM * * (1- as sales  from Order Details GROUP by Product ID

The query results are as follows:

Example 8, in the above grouping statistics, we want to select the statistics records of sales more than 15000 yuan, then to use the HAVING clause, the specific SQL is as follows:

SELECT SUM * * (1- as sales  from Order Details GROUP by Product ID  havingSUM** (1->  15000;

The query results are as follows:

Getting Started with ASP (19)-SELECT statement