Using SELECT-SQL syntax in ASP's ADO (I.)

Select expression

Next, let's take a look at the select expression, which is used to find records in a table that meet certain criteria, and the syntax is as follows:

SELECT [keyword] {* | table name. * | [table name.] Field Name 1 [as Alias 1] [, table name.] Field Name 2 [as Alias 2] [, ...]]}
From table expression [, ...] [in external table]
[WHERE ...]
[GROUP by ...]
[Having ...]
[Order BY ...]
[With Owneraccess OPTION]

Keyword: can be used to limit the number of records returned, which can be set to all, DISTINCT, Distinctrow, or top. If the language is not specified, the default value is all.

*: Select all the fields.

Alias: Replaces the field name in the table.

Table expression: One or more table names separated by commas.

External table: If the table does not exist in the current table, you must set the table name that contains the table in the table expression.

Executing a SELECT expression does not change the existing data in the table.

The most basic syntax for a SELECT expression is:

SELECT * FROM table expressions

You can use the asterisk (*) to select all the fields of the table. For example, the following example selects all the fields in the [Products] table:

SELECT * FROM Product

When a field name contains spaces or punctuation marks, use parentheses [] to enclose it. For example:

SELECT [Computer Products]

If you have more than one table in the field name in the FROM clause, precede the field name with the table name and the point (.) operator, which is the table name. Field name. For example, the following example selects the [Price] field for the product table and the Quantity field in the Order table:

SELECT products. Price, order, quantity

From Products, orders

WHERE product. Code = order. Code number

When you use a Recordset object, the recordset does not recognize the RS (table name. field name) format, which is an error in RS ("product. Price"), and you must use as to set the alias of the field name. Such as:

SELECT products. Price as price, order. Quantity as Quantity

From Products, orders

WHERE product. Code = order. Code number

This allows you to use RS ("price") and RS ("quantity") to read the data for its fields.

Syntax associated with a SELECT expression:

Keywords: all/distinct/distinctrow/top.

WHERE: Find records in from where tables meet specific criteria. WHERE You can use the following between, like, in operators:

Between ... And: Determines whether the value of an expression falls within a specified range.

Like: Look for matching keywords.

In expression: limited scope.

Not in expression: represents a range that does not belong to the specified scope.

ORDER BY clause: A field in which you can set the sort.

GROUP BY clause: counts the results of a query.

HAVING clause: Use in a Select expression to filter records that are already group by statistics.

Union: The results of multiple groups of queries can be merged.

Join: Joins the field records in two tables.

Subquery (sub query): In an expression, you can include a select expression.

Select ... into: The result of the query is created by creating a table.

The details are as follows:

all/distinct/distinctrow/top keyword

When you use a select query, you can add keywords to select the records you are querying. As follows:

All: Returns all records.

DISTINCT: Only one record is returned when a record in the specified field is duplicated, and the record is not duplicated.

Distinctrow: The records in the specified field do not return when they are duplicated.

Top: Returns the previous records or a few percentage records.

The syntax is as follows:

SELECT [All | DISTINCT | Distinctrow | [Top N [PERCENT]]]
From table

All returns all the records. When you do not add a keyword, the same meaning as plus all, will return all the records. For example, the following two examples have the same results, and all records are returned from the product table:

SELECT all * from product

Is the same as the following execution results:

SELECT * FROM Product

Distinct does not select duplicate data in the specified field. After using distinct, the result of the query, the data value of each field that is listed after the select DISTINCT, if the same, takes only one record, in other words, the data for the specified field will not be duplicated. For example, there are products with the same product name in the product table, and a distinct SQL expression returns only one record field for the same record as the product name data:

SELECT DISTINCT Product name from product

If you do not add distinct, the previous query returns several records that contain the same product name.

If you specify several fields after the SELECT DISTINCT clause, the result of the query will not duplicate the combined values of all the fields.

Distinctrow does not return all duplicate records for the specified field.

Top n [PERCENT], which returns a few previous records or a few percentage records. The order of arrangement can be specified by using the Orders by clause. For example, find the first 10 students name:

SELECT Top 10 Name
From students
ORDER BY Score

If you do not include an ORDER BY clause, the query returns any 10 records from the student table.

Top is not selected for the same value, and if the 10th and 11th grades are the same, the query returns 11 records.

You can use percent to set a number of previous percentages, such as the names of the top 10% students:

SELECT Top PERCENT Name
From students
ORDER BY Score

Let's look at an example of using this SQL instruction in an ASP program.

You can use Distinct to find records that are not duplicated, such as the ASP program rs7.asp as follows, [Insert into product (code, name) Select Distinct code, name from product where code = ' C2000 '] use Distinct and Insert into new C2000 record:

<%

' Distinct code, name only judge code, name is the same, the same person only add a record

sql = "Insert into product (code-named, name) Select Distinct code, name from product where code = ' C2000 '"

Set a = conn1. Execute (SQL)

Set RS3 = Server.CreateObject ("ADODB. Recordset ")

sql = "SELECT * from product where code = ' C2000 '"

Rs3. Open sql,conn1,1,1,1

%>

<table colspan=8 cellpadding=5 border=0>

<TR>

<TD align=center bgcolor= "#800000" ><font color= "#FFFFFF" > Code </FONT></TD>

<TD align=center bgcolor= "#800000" ><font color= "#FFFFFF" > Name </FONT></TD>

<TD align=center bgcolor= "#800000" ><font color= "#FFFFFF" > Price </FONT></TD>

<TD align=center bgcolor= "#800000" ><font color= "#FFFFFF" > Quantity </FONT></TD>

</TR>

<% do, not RS3. EOF%>

<TR>

<TD bgcolor= "F7efde" align=center><%= rs3 ("code")%></td>

<TD bgcolor= "F7efde" align=center><%= rs3 ("name")%></td>

<TD bgcolor= "F7efde" align=center><%= rs3 ("Price")%></td>

<TD bgcolor= "F7efde" align=center><%= rs3 ("Quantity")%></td>

</TR>

<%

Rs3. MoveNext

Loop

Rs3. Close

%>

</TABLE>

WHERE

Where to find a record of a table in from that meets a specific condition, where it is used in a select, UPDATE, or delete expression.

If a WHERE clause is not specified in the Select query, all the data in the table is returned. If you query multiple tables in a select and do not use a WHERE clause, or a JOIN clause, the query result is the product of multiple table data.

Where to set a specific condition, such as:

From product WHERE category = ' computer ': means [Products] that choose [Category] as [computer]

WHERE Price Between 1000 and 5000: Indicates a price between 1000 and 5000.

A WHERE clause that can contain up to 40 expressions, which are connected by logical operators such as and OR OR.

When you set a specific condition, follow the field type, and then add a different symbol, such as:

Text: Put single quotes around, such as where to sort = ' computer '.

Number: No sign before and after, such as where number > 100.

Date: Plus #, such as where date = #5/15/99#.

Where you can use the following between, like, in operator.