Using SELECT syntax in ADO
Author：Eve Cole
Update Time：2009-05-30 19:54:37
SELECT expression
Next, let's look at the SELECT expression. SELECT is used to find records that meet specific conditions in the table. 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, and can be set to ALL, DISTINCT, DISTINCTROW, or TOP. If no predicate is specified, the default value is ALL.
*: Select all fields.
Alias: replace the original field name in the table.
Table expression: one or more comma-separated table names.
External table: If the table does not exist in the current table, the table name containing the table in the table operation must be set.
Executing the SELECT expression will not change the existing data in the table.
The most basic syntax of a SELECT expression is:
SELECT * FROM table expression
You can use an asterisk (*) to select all fields of a table. For example, the following example selects all fields in the [Product] table:
SELECT * FROM products
When a field name contains spaces or punctuation marks, it must be enclosed in brackets [ ]. For example:
SELECT [Computer products]
If the field name in the FROM clause contains multiple tables, you need to add the table name and the dot (.) operator before the field name, that is, table name.field name. For example, the following example selects the [Price] field of the [Product] table and the [Quantity] field of the [Order] table:
SELECT product.price, order.quantity
FROM products, orders
WHERE product.code = order.code
When you use the Recordset object, Recordset does not recognize the rs (table name. field name) format, that is, rs ("product. price") will cause an error, and you must use AS to set the alias of the field name. for example:
SELECT product.priceAS price, order.quantityAS quantity
FROM products, orders
WHERE product.code = order.code
In this way, you can use rs ("price") and rs ("quantity") to read the data of its fields.
Syntax related to SELECT expressions:
Keywords: ALL/ DISTINCT/ DISTINCTROW/ TOP.
WHERE: Find data records in the FROM table that meet specific conditions. WHERE can use the following BETWEEN, LIKE, and IN operators:
Between...And: Determine whether the value of the expression falls within the specified range.
Like: Find matching keywords.
IN expression: limited range.
NOT IN expression: Indicates that it does not belong to the specified range.
ORDER BY clause: You can set the sorting fields.
GROUP BY clause: Make statistics on the query results.
HAVING clause: Used in the SELECT expression to filter records that have been GROUP BY statistics.
Union: The results of multiple sets of queries can be combined.
JOIN: Joins field records in two tables.
Subquery: The expression can include a SELECT expression.
Select...Into: Create a generated table based on the query results.
The details are as follows:
ALL/DISTINCT/DISTINCTROW/TOP keywords
When using SELECT query, you can add keywords to select the records being queried. as follows:
ALL: Return all records.
DISTINCT: When there are duplicate records in the specified field, only one record will be returned, and the records will not be duplicated.
DISTINCTROW: None of the records in the specified field will be returned if there are duplicates.
TOP: Returns the previous records or percentages of records.
The syntax is as follows:
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table
ALL returns all records. When no keyword is added, it has the same meaning as adding ALL, and all records will be returned. For example, the following two examples have the same execution results, both returning all records from the product table:
SELECT ALL * FROM products
The execution result is the same as:
SELECT * FROM products
DISTINCT does not select duplicate data in the specified field. After using DISTINCT, the query results will list the data value of each field after SELECT DISTINCT. If they are the same, only one record will be taken. In other words, there will be no duplication of data in the specified field. For example, there are some products with the same product name in the product table, and the SQL expression of DISTINCT will only return one record whose field has the same product name data:
SELECT DISTINCT product name FROM product
If you do not include DISTINCT, the above example query will return several records containing the same product name.
If several fields are specified after the SELECT DISTINCT clause, the combined values of all fields in the query results will not be repeated.
DISTINCTROW does not return all duplicate records of the specified field.
TOP n [PERCENT], returns the previous records or percentage of records. The order of sorting can be specified using the ORDER BY clause. For example, find the names of the top 10 students:
SELECT TOP 10 Name
FROM students
ORDER BY scores
If you do not include the ORDER BY clause, the query will return an arbitrary 10 records from the Student table.
TOP does not choose between the same values. If the 10th and 11th results are the same, the query will return 11 records.
You can use PERCENT to set the records of the first few percentages, for example, to find the names of the top 10% students:
SELECT TOP 10 PERCENT Name
FROM students
ORDER BY scores
Let's look at an example of using this SQL command in an ASP program.
You can use Distinct to find unique records. For example, the ASP program rs7.asp is as follows, [Insert Into product (code name, name) Select Distinct code name, name From product where code name = 'C2000'] Using Distinct and Insert Into, the new code name is C2000 records:
<%
'Distinct code, name only determines the code, whether the names are the same, only add one record if they are the same
sql = "Insert Into product (codename, name) Select Distinct codename, 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 while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("codename")%></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 searches for data records that meet specific conditions in the FROM table. WHERE is used in SELECT, UPDATE, or DELETE expressions.
If the WHERE clause is not specified in the SELECT query, all data in the table will be returned. If you query multiple tables in SELECT without using the WHERE clause or JOIN clause, the query result will be the product of the data in multiple tables.
WHERE sets specific conditions, such as:
FROM product WHERE Category = 'Computer': Indicates selecting [Product] whose [Category] is [Computer]
WHERE Price Between 1000 And 5000: Indicates that the price is between 1000 and 5000.
A WHERE clause can contain up to 40 operational expressions, which are connected by logical operators such as AND or OR.
When setting specific conditions, add different symbols before and after according to the field type, for example:
Text: Add single quotes before and after, for example, WHERE category = 'Computer'.
Numbers: No signs before and after, such as WHERE quantity > 100.
Date: Add # signs before and after, for example, WHERE date = #5/15/99#.
WHERE can use the following BETWEEN, LIKE, and IN operators.
