Tutorials to .com

Tutorials to .com » Database » Foxpro » VFP Chapter V Essentials to create queries and view

VFP Chapter V Essentials to create queries and view

Print View , by: iSee ,Total views: 20 ,Word Count: 2574 ,Date: Sat, 18 Apr 2009 Time: 7:07 PM

5.1 Create query

1. The concept of inquiry

Inquiry: a database is to issue a request to retrieve information, extracted from the records that meet specific conditions.

Query File:

That is, save the query of the SELECT-SQL command file. Query file, the system automatically given the extension. Qpr; query is run, the system will generate a compiled query file, extension. Qpx.

Results:

Document by running a query table and based on a dynamic view of the data set. Query results can be used to store different forms. Query data is read-only.

Query the data source: could be related to one or more free forms, database tables, views.

2. Using query designer to create queries

VFP Chapter V Essentials to create queries and view

Basic steps:

→ Open the query designer to create queries to add data tables based on the definition of output → Settings → Connection, screening, sorting, grouping conditions → output query results to choose the form of documents → → Save Query to run queries.

(1) open the query designer

Method 1:

From the File menu or toolbar, click Query → New → → new document into the query designer

Method 2:

When the data tables used in the project from project management data window, click Query → New → → → New inquiry into the query designer

Method 3: from a command window, type the command:

create query query to create a new file name & Inquiries

query file name modify query & modify the existing query

(2) the definition of query output

Click the tab field → field from the available list box, click the desired field (when the output is not directly out of the field from the table, click the box edge function and expression of the ... button to open expression generator, construction of expression required) → → Click the Add button the required fields automatically appear in the selected fields box.

(3) Set the query filter conditions

Screening of conditions for deciding what records are displayed.

Box structure in the filter expression filter conditions, it is necessary to pay attention to box in the example of the type of different data formats:

1) string without quotation marks can be (when the source table in the same field names when using quotation marks);

2) the date-type values to use () brackets;

3) the logic of data on both sides to take. Its like. T.,. F.

(4) set up to sort query results based on

Decides whether to sort the output in the order of records show.

Set Method:

Click Sort by →in the field from the selected field to choose ascending or descending → → click Add.

(5) sub-set of query results based on

Refers to the grouping of similar records of a group of compression as a result of record in order to complete the records based on the calculation of the group, such as: for the average sum of the number of statistics, including the maximum, minimum and so on.

Several commonly used statistical functions

Function For average For the sum of For maximum For the minimum Statistics on the number of
Name AVG () SUM () MAX () MIN () COUNT ()

The field for the packet may not necessarily be selected output field, but field can not be a sub-field calculation.

Can be used to meet the conditions of the division ... to further filter the results.

(6) Results of the other settings

Query results can be excluded from all the duplicate rows, and set a record of the scope of the results.

(7) Select the output type of query results

By default, the query results will be output in the browser window, and the data is read-only.

Set other output types of methods:

Open Query menu → → select query where the query dialog box to choose a destination. General or multi-select statements.

(8) to run queries

Open the query designer in the state, click on the toolbar used! Button or from the Query menu, select Run Query. Other cases, the project manager can select the file and click Run Query button, or choose from the menu to implement the order, or from a command window, type: DO query the file name.

(9) to create multi-table query

→ Open the query designer will be required to add a number of related tables connected to the conditions set in. → → according to above (2) to (8) to carry out step-by-step.

3. To create queries with Query Wizard

(1) create a standard query:

Open the data sheet to be → query from the file menu, click New → In the New Query dialog box, select and click Select Query Wizard Wizard → → leaflets or more from the table related to the field conditions of selected filter settings → → Set sort order of the results of select query → approach → Save the file name given query and select the saved query → The results showed that in the query window.

Note: At this point the data query is read-only, can not be updated.

(2) cross-table queries

Open the data sheet to be → query from the file menu, click New → In the New dialog box, select the query and click Crosstab Wizard Wizard → Select → Select from the leaflets field layout and design → → → select by adding the sum of the information query results → given way to preserve the file name and select query saved → query results appear in the query window.

Note: tables are not suitable for the use of any form of cross.

(3) SELECT-SQL command to create query

Commonly used format:

SELECT 1. 1 [AS 1], 1. 2 [AS 2], …
FROM ! 1 [,! 2 ] [, ! 3 ]
[TO FILE | into table | into cursor ]
[ WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY 1 [ASC | DESC] [, 2 [ASC | DESC] ...]]

Example:

For inquiries, visit the students in the student student.qpr :
SELECT xs.xh as , xs.xm as , xs.xb as , ;
xs.csrq as , xs.bj as ;
FROM test!xs;
WHERE xs.bj =cs1;
ORDER BY xs.xh;
INTO TABLE xs
student.qpr :
SELECT xs.xh as , xs.xm as , xs.xb as , ;
xs.csrq as , xs.bj as ;
FROM test!xs;
WHERE xs.bj =cs1;
ORDER BY xs.xh;
INTO TABLE xs

View results in the query according to the course sub_cj.qpr :
SELECT xs.bj as , COUNT(xs.xh) as , AVG(cj.cj) as , ;
MAX(cj.cj) as , MIN(cj.cj) as ;
FROM test!xs, test!cj, test!kc;
WHERE cj.xh = xs.xh;
AND kc.kcm = cj.kcm;
AND cj.kcm = sc1;
GROUP BY xs.bj;
ORDER BY xs.bj;
INTO TABLE sub_cj
sub_cj.qpr :
SELECT xs.bj as , COUNT(xs.xh) as , AVG(cj.cj) as , ;
MAX(cj.cj) as , MIN(cj.cj) as ;
FROM test!xs, test!cj, test!kc;
WHERE cj.xh = xs.xh;
AND kc.kcm = cj.kcm;
AND cj.kcm = sc1;
GROUP BY xs.bj;
ORDER BY xs.bj;
INTO TABLE sub_cj
sub_cj.qpr :
SELECT xs.bj as , COUNT(xs.xh) as , AVG(cj.cj) as , ;
MAX(cj.cj) as , MIN(cj.cj) as ;
FROM test!xs, test!cj, test!kc;
WHERE cj.xh = xs.xh;
AND kc.kcm = cj.kcm;
AND cj.kcm = sc1;
GROUP BY xs.bj;
ORDER BY xs.bj;
INTO TABLE sub_cj

5.2 Create the View

1. View of the characteristics of

1) exists in the database view is a virtual table, not to preserve the form of stand-alone document;

2) The data view can be changed, it not only has the functions of inquiry, and the results can be updated to reflect the source data table;

3) View open, the base table automatically open, but the view of the closure, the base table does not follow automatically shut down;

4) the data source view can be a free table, database table or another view.

2. With the design view to create a local view

VFP Chapter V Essentials to create queries and view

From the project manager to select a database to choose the local view → → → click New button to choose a new view to add the required data → → Table in the View Designer in accordance with the same steps to create a query (2) ~ (6) the establishment of View → Settings → Save to update the conditions given view name → View → Close View Designer (to see if you can run).

View of the creation parameters:

Examples of pages in the selection box, enter:? → parameters were from the View menu, choose Query → parameters given the parameters in the dialog box and select parameters were determined type → View → Save → Close → name given view View Designer (to see if you can run).

3. To use the View Wizard to create a local view

From the project manager to select a database to choose the local view → → → click button to choose a new view of the wizard to select the fields → → → Table related records record operating range → screening → Records → Sort → choose to save the way completed.

4. CREATE SQL VIEW command used to create view

Open the database, use the command to create a view:

OPEN DATABASE database name

CREATE SQL VIEW view the file name AS SQL-SELECT statement

Cases: OPEN DATABASE SJ
CREAT SQL VIEW SCORE AS SELECT SJCJ.XH, SJCJ.CJ ;
FROM SJ!SJCJ WHERE SJCJ.KCH=””
OPEN DATABASE SJ
CREAT SQL VIEW SCORE AS SELECT SJCJ.XH, SJCJ.CJ ;
FROM SJ!SJCJ WHERE SJCJ.KCH=””
OPEN DATABASE SJ
CREAT SQL VIEW SCORE AS SELECT SJCJ.XH, SJCJ.CJ ;
FROM SJ!SJCJ WHERE SJCJ.KCH=””

5. View the use of

Use the menu on the view edit records in the method and operation of the same data table. Can also be used to operate the following command.

View the basic operation order

View document to open and browse OPEN DATABASE database name

USE view the file name

BROWSE

Edit View View file name MODIFY VIEW
Rename View RENAME VIEW view the original file name file name TO new view
Delete view View file name DELETE VIEW

6. Update the source table using the data view

View Designer in the condition of the updated page to achieve the following set of data on the source table update:

1) select from the table you want to update the source table;

2) In the Field Name box, click a field before update key column B and column! To as the primary key and update the field;

3) to send SQL update check box is selected

5.3 SQL command language statement

SQL is a standard increasingly popular language of the database system management, will enable data retrieval is very convenient and flexible.

VFP5.0 support SQL command a list of seven

Order Function Example Remarks
CREATE TABLE-SQL Field to create a designated table create table xs (;

xh c (4) default "1201" primary key,;

xm C++ (6),;

xb c (2) check xb = "M" or xb = "F" error "of gender can only be male or female" default "female";

nl n (2) null)

New table for each field by name, type, precision, proportion, whether to support the NULL value and referential integrity rules to the definition of the command itself or from the array to obtain these definitions.
CREATE CURSOR-SQL Create a temporary table CREATE CURSOR teacher;

(TeacherID N (5), Name C (20), Address;

C (30), OfficeNo C (8) NULL, Specialty M)

ALTER TABLE - SQL Programmatically modify an existing structure. ALTER TABLE xscj ADD COLUMN kcmc C (14) Can modify the table of each field name, type, precision, proportion, whether to support the NULL value and referential integrity rules.
ALTER TABLE cj;

ALTER COLUMN cj SET CHECK cj> = 0;

ERROR "results can not be negative"

SELECT - SQL From one or more tables to retrieve data. SELECT Table xs.xh, Table xs.xm, Table xs.xb,;

FROM data test! Table xs;

WHERE table xs.xh>'81991025 ';

ORDER BY table xs.xh

Union command can be used to connect two conditions.
DELETE - SQL The use of SQL statements will be recorded in the table with the deletion of markers. DELETE FROM cj WHERE cj <60
INSERT - SQL Already exists in the form of a new record at the end of additional INSERT INTO xs (xh, xm, xb) VALUES;

( "8399101", "Zhang Li", "F")

The new record contains the data listed in the INSERT command, or from the array.
UPDATE - SQL Update the table records UPDATE cj SET cj = cj * 1.05 WHERE cj> 80 Can be based on the SELECT - SQL statement to update records of the results


FoxPro Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.