SELECT - SQL Command
Retrieves data from one or more tables.The Visual FoxPro ODBC Driver supports the native Visual FoxPro language syntax for this command. For driver-specific information, see Driver Res.SyntaxSELECT [ALL | DISTINCT]
[Alias.] Select_Item [AS Column_Name]
[, [Alias.] Select_Item [AS Column_Name] ...]
FROM [DatabaseName!]Table [Local_Alias]
[, [DatabaseName!]Table [Local_Alias] ...]
[WHERE JoinCondition [AND JoinCondition
…]
[AND | OR FilterCondition [AND | OR FilterCondition ...]]]
[GROUP BY GroupColumn [, GroupColumn ...]]
[HAVING FilterCondition]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]
Note 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. (See that section of the arguments.) Subqueries can contain multiple join conditions.
Arguments [ALL | DISTINCT]
[Alias.] Select_Item [AS Column_Name]
[, [Alias.] Select_Item [AS Column_Name] ...]
The SELECT clause specifies the fields, constants, and expressions that are displayed in the query results.
ALL, by default, displays all the rows in the query results. DISTINCT excludes duplicates of any rows from the query results. Note You can use DISTINCT only once per SELECT clause.
Alias. qualifies matching item names. Each item you specify with Select_Item generates one column of the query results. If two or more items have the same name, include the table alias and a period before the item name to prevent columns from being duplicated. Select_Item specifies an item to be included in the query results. An item can be one of the following: The name of a field from a table in the FROM clause.
A constant specifying that the same constant value is to appear in every row of the query results.
An expression that can be the name of a user-defined function.
User-Defined Functions with SELECT Although using user-defined functions in the SELECT clause has obvious benefits, you should also consider the following restrictions: The speed of operations performed with SELECT might be limited by the speed at which such user-defined functions are executed. High-volume manipulations involving user-defined functions might be better accomplished by using API and user-defined functions written in C or assembly language.
The only reliable way to pass values to user-defined functions invoked from SELECT is by the argument list passed to the function when it is invoked.
Even if you experiment and discover a supposedly forbidden manipulation that works correctly in a certain version of FoxPro, there is no guarantee it will continue to work in later versions.
Apart from these restrictions, user-defined functions are acceptable in the SELECT clause. However, remember that using SELECT might slow performance. The following field functions are available for use with a select item that is a field or an expression involving a field: AVG(Select_Item)—Averages a column of numeric data.
COUNT(Select_Item)—Counts the number of select items in a column. COUNT(*) counts the number of rows in the query output.
MIN(Select_Item)—Determines the smallest value of Select_Item in a column.
MAX(Select_Item)—Determines the largest value of Select_Item in a column.
SUM(Select_Item)—Totals a column of numeric data.
You cannot nest field functions. AS Column_Name
Specifies the heading for a column in the query output. This is useful when Select_Item is an expression or contains a field function and you want to give the column a meaningful name. Column_Name can be an expression but cannot contain characters (for example, spaces) that aren't permitted in table field names.
FROM [DatabaseName!]Table [Local_Alias]
[, [DatabaseName!]Table [Local_Alias] ...]
Lists the tables containing the data that the query retrieves. If no table is open, Visual FoxPro displays the Open dialog box so that you can specify the file location. Once open, the table remains open after the query is complete.
DatabaseName! specifies the name of a database other than the one specified with the data source. You must include the name of the database containing the table if the database is not specified with the data source. Include the exclamation point (!) delimiter after the database name and before the table name. Local_Alias specifies a temporary name for the table named in Table. If you specify a local alias, you must use the local alias in place of the table name throughout the SELECT statement. The local alias doesn't affect the Visual FoxPro environment. WHERE JoinCondition [AND JoinCondition ...]
[AND | OR FilterCondition [AND | OR FilterCondition ...]]
Tells Visual FoxPro to include only certain records in the query results. WHERE is required to retrieve data from multiple tables.
JoinCondition 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. Important Keep the following information in mind when creating join conditions:
If you include two tables in a query and don't specify a join condition, every record in the first table is joined with every record in the second table as long as the filter conditions are met. Such a query can produce lengthy results.
Use caution when joining tables with empty fields because Visual FoxPro matches empty fields. For example, if you join on CUSTOMER.ZIP and INVOICE.ZIP and if CUSTOMER contains 100 empty zip codes and INVOICE contains 400 empty zip codes, the query output contains 40,000 extra records resulting from the empty fields. Use the EMPTY( ) function to eliminate empty records from the query output.
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 operators described in the following table. Operator Comparison
= Equal
== Exactly equal
LIKE SQL LIKE
<>, !=, # Not equal
> More than
>= More than or equal to
< Less than
<= Less than or equal to When you use the = operator with strings, it acts differently, depending on the setting of SET ANSI. When SET ANSI is set to OFF, Visual FoxPro treats string comparisons in a manner familiar to Xbase users. When SET ANSI is set to ON, Visual FoxPro follows ANSI standards for string comparisons. See SET ANSI and SET EXACT for additional information about how Visual FoxPro performs string comparisons. FilterCondition specifies the criteria that records must meet to be included in the query results. You can include as many filter conditions in a query as you want, connecting them with the AND or OR operator. You can also use the NOT operator to reverse the value of a logical expression, or you can use EMPTY( ) to check for an empty field. FilterCondition can take any of the forms in the following examples:
详细请参见：
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/vfplngselect___sql.asp
Retrieves data from one or more tables.The Visual FoxPro ODBC Driver supports the native Visual FoxPro language syntax for this command. For driver-specific information, see Driver Res.SyntaxSELECT [ALL | DISTINCT]
[Alias.] Select_Item [AS Column_Name]
[, [Alias.] Select_Item [AS Column_Name] ...]
FROM [DatabaseName!]Table [Local_Alias]
[, [DatabaseName!]Table [Local_Alias] ...]
[WHERE JoinCondition [AND JoinCondition
…]
[AND | OR FilterCondition [AND | OR FilterCondition ...]]]
[GROUP BY GroupColumn [, GroupColumn ...]]
[HAVING FilterCondition]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]
Note 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. (See that section of the arguments.) Subqueries can contain multiple join conditions.
Arguments [ALL | DISTINCT]
[Alias.] Select_Item [AS Column_Name]
[, [Alias.] Select_Item [AS Column_Name] ...]
The SELECT clause specifies the fields, constants, and expressions that are displayed in the query results.
ALL, by default, displays all the rows in the query results. DISTINCT excludes duplicates of any rows from the query results. Note You can use DISTINCT only once per SELECT clause.
Alias. qualifies matching item names. Each item you specify with Select_Item generates one column of the query results. If two or more items have the same name, include the table alias and a period before the item name to prevent columns from being duplicated. Select_Item specifies an item to be included in the query results. An item can be one of the following: The name of a field from a table in the FROM clause.
A constant specifying that the same constant value is to appear in every row of the query results.
An expression that can be the name of a user-defined function.
User-Defined Functions with SELECT Although using user-defined functions in the SELECT clause has obvious benefits, you should also consider the following restrictions: The speed of operations performed with SELECT might be limited by the speed at which such user-defined functions are executed. High-volume manipulations involving user-defined functions might be better accomplished by using API and user-defined functions written in C or assembly language.
The only reliable way to pass values to user-defined functions invoked from SELECT is by the argument list passed to the function when it is invoked.
Even if you experiment and discover a supposedly forbidden manipulation that works correctly in a certain version of FoxPro, there is no guarantee it will continue to work in later versions.
Apart from these restrictions, user-defined functions are acceptable in the SELECT clause. However, remember that using SELECT might slow performance. The following field functions are available for use with a select item that is a field or an expression involving a field: AVG(Select_Item)—Averages a column of numeric data.
COUNT(Select_Item)—Counts the number of select items in a column. COUNT(*) counts the number of rows in the query output.
MIN(Select_Item)—Determines the smallest value of Select_Item in a column.
MAX(Select_Item)—Determines the largest value of Select_Item in a column.
SUM(Select_Item)—Totals a column of numeric data.
You cannot nest field functions. AS Column_Name
Specifies the heading for a column in the query output. This is useful when Select_Item is an expression or contains a field function and you want to give the column a meaningful name. Column_Name can be an expression but cannot contain characters (for example, spaces) that aren't permitted in table field names.
FROM [DatabaseName!]Table [Local_Alias]
[, [DatabaseName!]Table [Local_Alias] ...]
Lists the tables containing the data that the query retrieves. If no table is open, Visual FoxPro displays the Open dialog box so that you can specify the file location. Once open, the table remains open after the query is complete.
DatabaseName! specifies the name of a database other than the one specified with the data source. You must include the name of the database containing the table if the database is not specified with the data source. Include the exclamation point (!) delimiter after the database name and before the table name. Local_Alias specifies a temporary name for the table named in Table. If you specify a local alias, you must use the local alias in place of the table name throughout the SELECT statement. The local alias doesn't affect the Visual FoxPro environment. WHERE JoinCondition [AND JoinCondition ...]
[AND | OR FilterCondition [AND | OR FilterCondition ...]]
Tells Visual FoxPro to include only certain records in the query results. WHERE is required to retrieve data from multiple tables.
JoinCondition 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. Important Keep the following information in mind when creating join conditions:
If you include two tables in a query and don't specify a join condition, every record in the first table is joined with every record in the second table as long as the filter conditions are met. Such a query can produce lengthy results.
Use caution when joining tables with empty fields because Visual FoxPro matches empty fields. For example, if you join on CUSTOMER.ZIP and INVOICE.ZIP and if CUSTOMER contains 100 empty zip codes and INVOICE contains 400 empty zip codes, the query output contains 40,000 extra records resulting from the empty fields. Use the EMPTY( ) function to eliminate empty records from the query output.
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 operators described in the following table. Operator Comparison
= Equal
== Exactly equal
LIKE SQL LIKE
<>, !=, # Not equal
> More than
>= More than or equal to
< Less than
<= Less than or equal to When you use the = operator with strings, it acts differently, depending on the setting of SET ANSI. When SET ANSI is set to OFF, Visual FoxPro treats string comparisons in a manner familiar to Xbase users. When SET ANSI is set to ON, Visual FoxPro follows ANSI standards for string comparisons. See SET ANSI and SET EXACT for additional information about how Visual FoxPro performs string comparisons. FilterCondition specifies the criteria that records must meet to be included in the query results. You can include as many filter conditions in a query as you want, connecting them with the AND or OR operator. You can also use the NOT operator to reverse the value of a logical expression, or you can use EMPTY( ) to check for an empty field. FilterCondition can take any of the forms in the following examples:
详细请参见：
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/vfplngselect___sql.asp
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货