SELECT - SQL Command
See
Retrieve data from one or more tables.
grammar
SELECT [All | Distinct] [Top Nexpr [Percent]] [ALIAS.] Select_Item [As Column_name] [, [ALIAS.] SELECT_ITEM [As Column_name] ...]
From [Force] [DatabaseName!] Table [[as] local_alias] [[inner | left "| Right [Outer] | fulltatabaseName!] Table [[As] local_alias] [on joincondition ...]
[[INTO DESTINATION] | [To file filename [add] | to printer [prompt] | to screen]]
[Preference preferencename]
[Noconsole]
[Plain]
[NoWait]
[Where joincondition [and | or filtercondition [and | or filtercondition ...]]]]]]]
[Group by groupcolumn [, groupcolumn ...]]]]
[Having Filtercondition]
[Union [all] select command]
[ORDER by Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]]]]]]]]
parameter
SELECT
Specify the fields, constants, and expressions included in the query results in the Select clause.
All
The query results include all rows (including repetition values). All is the default setting.
Distinct
Remove the repeated row in the query results.
Note that each Select clause can only use a Distinct.
Top nexpr [percent]
In all records that meet the query conditions, select the specified number or percentage record. The TOP clause must be used at the same time as the Order By clause. The ORDER BY clause specifies the number of rows determined by the TOP text in the column included in the query result, and the TOP clause selects the first NEXPR or NexPR% record in accordance with this.
You can specify 1 to 32767 records. Sort by the field specified by the ORDER BY clause, the case where the side is generated, for example, there may be multiple records, which are the same on the selected field; so, if you specify NEXPR 10, in the query result may be more 10 records because there may be several record locations.
If the number of records in the query result is included, the number of records may be a decimal number, and it is made. When the Percent keyword is included, the NEXPR ranges from 0.01 to 99.99.
Alias.
Limited the name of the match. Each specified by Select_Item generates a column in the query results. If multiple items have the same name, you should add the alias and one period before these items to prevent repetition columns.
SELECT_ITEM specifies the item included in the query result. One item can be:
The field name in the table contained in the FROM clause. A constant, each line has this constant value in each line in the query results. An expression can be a user-defined function name.
Ask column_name
Specifies the title of the column in the query results. When select_item is an expression or a field function, this clause can generally use this clause if you want to give this column. COLUMN_NAME can be an expression, but it cannot contain characters (such as spaces) that are not allowed in those table field names.
From
List all the tables that retrieve data from which data are retrieved. If you do not open the table, Visual FoxPro displays the "Open" to specify the file location. After the table is open until the query is closed. If you contain the Force keyword, Visual FoxPro will strictly follow the order of sequential connection you declared in the FROM clause. If you do not include force keywords, Visual FoxPro will try to optimize the query. Use the Force clause to avoid optimization processes, which may speed up the speed of the query execution.
DatabaseName!
When the database containing the table is not the current database, DatabaseName! Specifies the name of this database. If the database is not a current database, you must specify the name of the database containing the table. The exclamation mark (!) Separator should be added before the name of the database name.
[As] Local_Alias
Specify a temporary name for tables in Table. If a local alias is specified, this alignname must be used instead of the table name throughout the SELECT statement. Local alias does not affect Visual FoxPro environments. Inner Join only appears in the query results only in other tables containing records (one or more) records.
Inner Join only appears in the query results only in other tables containing records (one or more) records.
LEFT [Outer] Join contains: Join left side tables in all records, and records in the right side of the Join. Outer keywords can be omitted; including Outer emphasizes that this is an outer join.
Right [Outer] Join contains: Join Right Side Table All records, and records in the left side table. The Outer keyword can be omitted; including Outer emphasizes this is an outer join.
Full [Outer] Join contains all matched records on both sides of the query, and mismatched records; containing Outer emphasizes this is an outer join.
ON Joincondition Specifies the connection condition.
INTO DESTINATION
Specify where to save the query results. If the INTO clause and the TO clause is included in the same query, the TO clause does not work. If the INTO clause is not included, the query result is displayed in the Browse window. You can also use TO to output the query result to the printer or file.
Destination can be one of the following clauses:
Array ArrayName saves the query results into the variable array. This array is not created if any records are not included in the query result. CURSOR CURSORNAME saves the query results into the temporary table. If a name that has been opened is specified, Visual FoxPro generates an error message. After executing the SELECT statement, the temporary table remains open, act, but read-only. Once the temporary table is closed, it automatically deletes it. The temporary table exists as a temporary file on the Sortwork specified drive. Contains Nofilter to create a pointer that can be used in later queries. The previous version of Visual Justice requires an additional constant or expression as a filter to create a pointer that can be used in later inquiry. Select *, .t. From customs INTO CURSORMYQUERY
The occurrence of Nofilter can reduce the inclusion because the temporary table is built on disk, and when the temporary table is deleted, the pointer is to be turned off. DBF TABLENAME | TABL TABLENAME, saving the query results into a table. If the specified table has been opened, the Set Safety is set to OFF, Visual FoxPro will override the table without a warning. If there is no specified extension, Visual FoxPro specifies the extension of the table is .dbf. After the SELECT statement is executed, the table still remains open. Contains Database DatabaseName to specify a database that adds a table. Contains NamelongTableName can list up to 128 characters for the list and can replace the short name in the database. To File FileName
If the TO clause is included in the command, but does not include an INTO clause, the query result is output to the ASCII code file named FileName, the printer, or the Visual FoxPro main window.
Additive outputs the query result orients to the existing directory of the text file specified by To File FileName.
To printer [prompt] outputs the query result to the printer. Use an optional PROMPT clause to display a dialog using an optional PROMPT clause before printing. You can adjust the printer settings based on the currently installed printer driver. Place the PROMPT clause after keeping up with To Printer.
TO Screen makes the query result to output to the Visual FoxPro main window or active user-defined window.
Preference PreferenceAme
If the query result is sent to the browse window, you can use Preference to save the properties and options for the browsing window for use. Preferenceence Saves the feature property or parameter option in the resource file of the Foxuser, and can be retrieved any time.
Create a parameter option when performing a select command with Preference PreferenceName. After performing the Select command with the same parameter option name, the browsing window will return to the original parameter option status. Update the parameter option when the browsing window is turned off.
If you press the Ctrl Q W key to exit the Browse window, your changes made to the Browse window will not be saved in the resource file.
Noconsole
Do not display query results sent to files, printers, or Visual FoxPro main windows.
Plain
Prevent column headings appear in the displayed query results. The PLAIN clause can be used regardless of the TO clause. If the INTO clause is included in the SELECT statement, ignore the Plain clause.
NOWAIT
Open the browse window and output the query result to the execution of the program after this window. The program doesn't wait to close the browsing window, but immediately perform the line behind the SELECT statement.
The select command includes TO Screen to output the query result to the Visual FoxPro main window or a user-defined window. If the Visual FoxPro main window or user-defined window is written, the output is paused. Press any key to view the contents of the query results. However, if the NOWAIT clause is included, it will not be paused when the query result is displayed, and the call is waiting in the Visual FoxPro main window or the user-defined window. If the command contains an INTO clause, ignore the NOWAIT clause.
WHERE
Notifying Visual OxPro only contains only a certain number of records in the query results. If you want to retrieve data from multiple tables, the WHERE clause is required.
JoinCondition
Specify a field that connects the table in the FROM clause. If there is more than one table in the query, you should specify the connection condition for each table after the first table.
Connecting multiple query conditions must use operator and. Each connection condition has the following form: FIELDNAME1 Comparison FieldName2
WherefieldName1 is a field name in a table, fieldName2 is a field name in another table, and Comparison is listed in the table below.
Operators compare relationship = equal == complete equal likesql like <>,! =, # Non-equal> greater than> = greater than or equal to When the string uses = operator, the resulting result is related to the set ANSI settings. When the SET ANSI is set to OFF, the Visual FoxPro comparison string is the same as XBase's users. When the set ANSI is set to ON, the Visual FoxPro compares the ANSI standard when the string is compared. For details on the comparison method of the Visual FoxPro character, see SET ANSI and SET EXACT. The WHERE clause supports the ESCAPE operator, allowing execution of meaningful queries that contain data containing SELECT? Sql% and wildcard. The ESCAPE clause allows you to specify a SELECT? SQL wildcard that can be seen as a text character. The Escape clause allows you to specify a character. Once it is placed in the wildcard character, it means that this wildcard is viewed as a text character. Filtercondition Specifies that the required conditions that must be included in the query result. With the AND or OR operator, you can include a number of filters. You can also use the NOT operator to take the logical expression value, or use the EMPTY () function to check the empty field. Filtercondition can be any of the forms in the examples: Example 1 Example 1 Display FLITER CONDITION in the fieldName1 Comparison FieldName Form Customer.cust_id = Orders.cust_ID Example 2 Example 2 Displaying FLITER CONDITION in the FieldName Comparison Expression Form Payments.amount> = 1000 Example 3 Example 3 Displaying FLITER CONDITION in the FieldName Comparison All (Subquery) Form When the filter condition includes all, only the specified field satisfies all subqueries result, it can be added to the query result. COMPANY (Select Company from Customer WHERE COUNTRY = "UK") Example 4 Example 4 Display FLITER CONDITION in the FieldName Comparison Any | Some (Subquery) Form When the filter condition contains ANY or SOME, the field must meet at least a comparison condition determined by the value generated by the subquery. COMPANY (Select Company from Customer WHERE COUNTRY = "UK") Example 5 Example 5 Display FieldName [NOT] BetWeen Start_Range And End_Range Forms Fliter Condition This example checks if the value in the field is within the specified range. Customer.Postalcode Between 90000 and 99999 Example 6 Example 6 Displaying the FLITER CONDITION in the [NOT] EXISTS (Subquery) Form This example checks if there is at least one line to meet the conditions in the subquery. When the screening conditions include exists, as long as the subquery is not empty, the screening conditions are "true" (.t.). EXISTS; (Select * from orderers where customer.postalcode = orders.postalcode) Example 7 Example 7 Displaying FLITER CONDITION in the fieldName [NOT] in Value_set Form When IN is included in the screening conditions, the conditions added to the field where the field is located to the query result is that the field must contain an element of the value collection. Customer.Postalcode Not in ("98052", "98072", "98034") Example 8 Example 8 Display Fliter Condition in the fieldName [not] in (Subquery) form Here, the record containing the condition in the query result is that the field must contain a returning value of a subquery. Customer.cust_id. (Select ORDERS.CUST_ID from ORDERS WHERE ORDERS.CITY = "seattle") Example 9 Example 9 Displaying FLITER CONDITION in the fieldName [NOT] Like Cexpression Form Customer.Country Not Like "UK" This filter condition finds a field that matches string expressions. A percentage (%) and underscore (_) wildcard can be used in a string expression. The underscore represents an arbitrary character in the string. Group by groupcolumn [, groupcolumn ...] The row of query results is packetified by column values. GroupColumn can be a regular table field name, or a field name containing the SQL field function, but also a numeric expression, specifying the column position in the query result table (the leftmost column number 1). Having filtercondition Specifies the filter criteria that must be met in the query result. Having should be used with Group By. It can contain unlimited filter conditions, filter conditions with and or OR connections, and use Not to reverse logical expressions. Filtercondition cannot include subqueries. If the command to use the Having clause If you do not use the Group By clause, its role is the same as where clauses. Local alias and field functions can be used in the HAVING clause. If the Having clause does not contain a field function, use the WHERE clause to get a faster speed. [Union [all] select command] Combine the final query result of a SELECT statement with another Select statement final query result. By default, UNION checks the result of the combination and excludes duplicate rows. To combine multiple UNION clauses, brackets can be used. All ALL prevents the duplicate row in the combination result. UNION clause abide by the following rules: Union cannot be used to combine subqueries. The number of columns in the query results of the two select commands must be the same. The corresponding columns in the two SELECT query results must have the same data type and width. Only the final select can include the Order By clause, and must be selected by the number to indicate the output of the output. If an ORDER BY clause is included, it will affect the entire result. ORDER by Order_Item Sort query results according to the data of the column. Each ORDER_ITEM must correspond to a column in the query result. It can be one of the following: The field of the list of the FROM clause is also a selection item of the SELECT main sentence (not in subquery). A numerical expression indicates the location of the column in the query result (the leftmost column number 1). ASC specifies that the query results are arranged in ascending according to the sort item. It is the default option of ORDER BY. DESC specifies that the query is arranged in sequence. If you do not use the Order By to specify the order of query results, the query results are not sorted. Description Like other Visual FoxPro commands, select is an internal SQL command for Visual FoxPro. When using SELECT, Visual FoxPro first explains the query request and then queries and retrieves the specified data from the table. You can establish a SELECT query in the following environments: Command window A Visual FoxPro program (like other Visual FoxPro commands) query designer If the SET TALK ON command is executed, execute the select command, Visual FoxPro shows the number of time and query results used by the query. _Tally contains the number of records recorded in the query results. The filter criteria for Set Filter settings don't work. The following section will mention the subquery (Subquery), and subqueries refer to the select command included in the select command. In the WHERE clause of the select command, you can contain up to two graded (non-nested) subquers. Multiple connection conditions can be available in subqueys. When you create a query output, the naming of the column follows the following rules: If the selection is a field with a unique name, use the field name as the output column name. If multiple selection items have the same name. For example, if the table named Customer has a street field, the table named Employees also has a street field, and the output column is named extension_a and extension_b (street_a and street_b). If the selected item name has 10 characters long, the name can be shorter and the next line and the letter. For example, Department becomes departme_a. If the selection is an expression, its output column is named exp_a. Other expressions are named exp_b, exp_c, respectively, and so on. If the selection contains a field function such as count (), the output column is named CNT_A. If another selection contains Sum (), its output column is named SUM_B. The user-defined function in the SELECT clause: Use the user-defined function in the SELECT clause There is a significant advantage, but the following restrictions should be considered when used: The running speed of the SELECT clause will be affected by the user-defined function. Therefore, if the user's custom function is large, the functionality of these functions is best to call the API or user-defined function written in the C language or assembly language to complete. It is difficult to predict the environment of Visual FoxPro input / output (I / O) and tables in the Select activated user-defined function. In general, I don't know which one selected work area is, I don't know the name of the current table, and I don't even know the name of the field being processed. The values of these variables are entirely dependent on where the user-defined function is activated in the optimization process. Modifying Visual FoxPro I / O or table in a user-defined function called by a Select clause is unsafe. In general, this result is unpredictable. The uniquely reliable method passed from SELECT to the user's own function is to activate the user-defined function. Parameters. After practical, it is possible to find some operations that are considered to be illegal to run in a certain FoxPro version, but this does not guarantee that it can also run correctly in the later version. Do not say these restrictions, user-defined functions are acceptable in the SELECT statement. But don't forget to use SELECT might to reduce performance. The following field functions can be used with the selected item, the selected item can be a field or an expression containing a field: AVG (SELECT_ITEM), the average of the values in the column. COUNT (SELECT_ITEM), the number of selected items in the column. Calculate the number of rows output from the query. MIN (SELECT_ITEM), determine the minimum of select_item in the column. Max (SELECT_ITEM), determine the maximum value of select_item in the column. SUM (SELECT_ITEM), the sum of the values in the column. Field functions cannot be nested. Connection (JOIN) Visual FoxPro Supports an ANSI SQL '92 Connection (JOIN) syntax, connects their records together to generate queries by comparing fields in two or more tables. For example, an internal connection is to select the same record in the two tables to the query. Visual FoxPro Supports Nested Connections (NESTED JOINS) When you create a connection condition, keep in mind the following: If you include two tables in the query, there is no specified connection condition, then each record in the first table Each of the two tables is connected, and as long as the screening conditions are met, it will be connected. This result is very long.
