Tutorials to .com

Tutorials to .com » Database » Foxpro » VFP Chapter Essentials to create and use tables

VFP Chapter Essentials to create and use tables

Print View , by: iSee ,Total views: 69 ,Word Count: 5745 ,Date: Sat, 18 Apr 2009 Time: 8:37 PM

Table 3.1 VFP6.0

1. Form the concept of

Table (data table):

Refers to documents stored in the disk of a two-dimensional table. (The equivalent of foxpro 2.x versions of the database)

Free table: do not belong to any database table.

database tables: a database included in the table.

Table File Name:

Table name can be letters, numbers or underscore the composition, the system automatically given the extension. DBF.

Note the file name:

When the table in the Remarks field or general-purpose, the system automatically generate data tables with their table name the same as the extension:. FPT file name.

Table Structure: Table storage structure of public records. (Table 1 refers to the first structure of the table, that is, the properties of fields).

Record: his table. It is a collection of a number of fields, each with a table field has the same record.

Field: a table. It provides the characteristics of the data.

The relationship between the characteristics of data table:

(1) no longer a field for each decomposition, we can not have the same field name;

(2) the data in each column have the same data type;

(3) the contents of the table is not exactly the same line (record).

2. The basic properties of field

Field attributes:

Include: field name, data type, field width, number of decimal places, null value support

Field Name:

That is, each field name, must start with a letter, by letters, numbers, underscores combinations, letters, and lowercase without distinction, but no spaces or other characters. Freedom of the table field names of up to 10 characters, the database table field names in support of long names, up to 128 characters. Removed from the database if a table, then this form of the long field who will be truncated into 10 characters.

vfp allow long table field names and long, but the use of long names for the operation of cross-platform may have unpredictable results.

Data types: the data refers to the characteristics of the field.

Field Width: that the field data can accommodate the largest number of bytes.

Number of decimal places: index data will be retained to several decimal places. At this time the field width = integer median +1 + the number of decimal places.

NULL value (null value):

No clear value. NULL value is not equivalent to zero or spaces. A NULL value can not be considered more than a certain value (including another NULL value) is large or small, equal or different.

VFP 6.0 table field data types

Field Type Code Description Field width The use of sample
Character c Letters, numeric characters and text Each character is a byte, a maximum of 254 characters Students or the name, "8199101" or 'Li'
Currency type Y Currency Unit 8 bytes Wages, $ 1246.89
Date Type D Contains the well-established and on and on data 8 bytes Date of birth,

(^ 1980/07/08)

Date Time Type T Contains the years, months, days, hours, minutes, seconds of data 8 bytes Working hours,

(^ 2005/02/20 9:15:15 AM)

Logic-based L "True" or "false" boolean value 1 byte Courses are compulsory,

. T. or. F.

Numerical model N Integer or decimal Accounted for in memory 8 bytes; in the table account for 1-20 bytes Examination results, 83.5
Double-precision type B Double-precision floating-point numbers 8 bytes The requirements of high-precision experimental data
Floating-point type F With the same numerical
Integer I Value without the decimal point 4 bytes The number of students
General-purpose G OLE object Accounted for in the table 4 bytes Picture or sound
Type Remarks M A period of indefinite length text Accounted for in the table 4 bytes Student Resume
Character (binary) C++ Code page without any modification to maintain the character data Each character with a byte, a maximum of 254 characters
Remarks type (binary) M Code page without any modification of data maintained Remarks Accounted for in the table 4 bytes

3. Form the basic operation

Create a table: Design table name and table structure, enter the record index

Data Maintenance: increase records, modify records, delete records

Create a table of the main steps: design table structure input record → → → index data maintenance

4. Table operation on a few basic commands

Command format Function
CREATE table file name The freedom to create a new structure
USE table file name Open the file specified table
USE The closure of the current table file
LIST ALL Table shows the current contents of the entire record (similar to the DOS command dir)
display ALL Sub-screen shows all the records of the current contents of the table (similar to DOS's dir / p)
DISPLAY STRUCTURE Table shows the current structure of (sub-screen display)
LIST STRUCTURE Table shows the current structure (regardless of screen display)
MODIFY STRUCTURE Out the design, and modify the current structure.
CLEAR The main window to remove all the contents of


(1) must first open the data required to operate the above table can only be carried out;

(2) clause with an order, the only records of the specified operation;

(3) LIST command is the default scope of all records, DISPLAY command is the default scope of the current record.

Table 3.2 to create and modify the structure of

1. Table structure to create

VFP Chapter Essentials to create and use tables

Create a table structure is actually the basic properties of the design field. Can use the Table Designer, Table Wizard or the SQL command to create a table structure.

(1) the use of the design to create table


From the File menu, click New → In the New dialog box, select the table and click the New Document dialog box → give in to create the file name and determine the location of the necessary preservation of → in the table by Designer dialog box enter the word paragraph (↓ or use the mouse line), after the completion of all input fields click OK.

(2) create a table using the Table Wizard


From the File menu, click New → In the New dialog box, select the table and click Table Wizard Wizard → done in Step 1: Select the field, click Next → after the completion of the wizard in the table to do the first step 1a: Select database, click Next → after the completion of the wizard in the table to do Step 2: modify the field settings, click Next → after the completion of the wizard in the table to do Step 3: build the index for the table, click the next step after the completion of → in the table wizard to do Step 4: complete, select the Save method of the table, after the completion click Finish → Save As dialog box in the given file name and determine the location of the required preservation.


First select a data table to be constructed with a similar kind of table, and then select the required part of the field, and then in the next step need to be amended in accordance with the actual or direct the use of the width of the original field.

(3) the use of CREATE TABLE - SQL command

The general form of the order as follows:

CREATE TABLE | dbf <List file name> (1 Field Name Field Type [(field width [, number of decimal places ])][, field names 2 field type [(field width [, number of decimal places] )]] ... ...)

Case: CREATE TABLE xscj (xh c (8), xm c (6), xb c (2),; cj n (5,1), ksrq d)

2. Table structure changes

(1) modify the design of

Out with the menu table Designer:

Documents required to open the table show → click menu → Table Designer dialog box in the Table Designer to modify fields in the modified → finished click OK and in a message box, select Yes.

Out with an order form designer:

USE <List file name>


(2) the use of ALTER TABLE - SQL command

Function Order form sample
Add field ALTER TABLE xscj ADD COLUMN kcmc C (14)
Rename field ALTER TABLE xscj RENAME COLUMN kcmc TO kc
Delete field ALTER TABLE xscj DROP COLUMN kc

Table 3.3 records the treatment of

1. Record input

(1) immediate input:

The design of the structure, the system will be prompted to enter data directly into the state. If the election is, then enter the edit window, lost after click "X" button to exit, the system automatically save. Remarks must be imported or general-purpose data field, double-click the "memo" or "gen", the editor in the new window opens, type the required content, lost after click "X" button to automatically save the file back to the the original window, when the field in the "memo" or "gen" into "Memo" or "Gen".

(2) additional methods:

If the design of the structure of a good table and did not enter data, you can use an additional method:

From the File menu (or from a command window, type: USE table file name) file → open the table from the display menu, select View → Display menu and then choose from an additional way of → in the browser window, type the required records.

Additional application of different occasions

Additional record Applicable to the occasion
Show that the additional mode menu Table in the rear of the existing data to add one or more new record
Table menu in the additional new record In the rear of the existing data tables to add a new record
Table menu in the additional records ... From other data tables will be in line with the requirements (field names and field types of the same) add in the records of the tail current data table
APPEND [BLANK] In the current table is automatically added at the end of one or more new records. Equivalent to show that the additional mode menu. Add BLANK when there is an empty record, which is equivalent to the additional menu table a new record.
APPEND FROM table file name [FIELDS fields of 1, Field Name 2 ...] [FOR conditions] From other data tables will be in line with the requirements (field names and field types of the same) add in the records of the tail current data table. Table menu in the equivalent of an additional record ....
APPEND FROM text file of SDF Will meet the requirements of the text file all the additional data in the current form of the tail.

Note: the text to the field and table structure.

The File menu to import Other types of files will be converted into a complete data sheet VFP.

(3) the use of INSERT-SQL command

The general form of the order as follows:

INSERT INTO <table name> [(Field Name 1 [, field names 2 ...])] VALUES (expression 1 [, expression 2, ...])

Cases: INSERT INTO xscj (xh, xm, cj) VALUES ( "81991001", "Zhang", 85.5)

2. Records of the browser

(1) through the browser window

(2) through the BROWSE command

BROWSE command to open the table browser window, the clause can be different to achieve a specific record of the browser.

VFP commands commonly used in the clause

1) the scope of clause

Clause format Function
ALL Documents on the table that records all operation
NEXT n That record from the beginning of the current records for a total of n operations, n a positive integer
RECORD n Operation is specified in Table No. paper records the first n
REST To play a file from the current record of all records at the end of the operation

2) FOR clause

Operate for the specified conditions.

Format: FOR <conditions>

Specified only in designated areas who meet the conditions given for the current operation.

For example: list NEXT 6 FOR cj <85

3) WHILE clause

Are also used to identify the conditions to operate.

Format: WHILE <conditions>

For example: list NEXT 6 WHILE cj <85

4) FIELDS clause

Specified for the operation of the current related to table a document which field.

Format: BROWSE [FIELDS fields of 1, Field Name 2 ... ...]

For example: BROWSE FIELDS xh, xm, cj

3. Records the position

(1) system to record signs of the three control

Record the beginning of signs: the first record is located before. The value function BOF () for testing.

Record pointer Logo: points to the current record. The value function RECNO () for testing.

Record marks the end: at the last record. The value function EOF () for testing.

Record pointer can be understood as to preserve the current record number of variables, and its initial value is always 1, it is impossible for 0 or negative, the maximum value is +1 the total number of table records.

Just open the table record pointer at the situation

Table records BOF () value RECNO () value EOF () value
No records . T. 1 . T.
Record . F. 1 . F.

(2) record the positioning of the way

Absolute positioning: the means to move the pointer to the location specified.

Relative positioning:

Refers to the location of the pointer from the current year, compared with the current record to move forward or backward a number of location records.

Conditions of location:

Refers to certain conditions by the whole table or tables in a designated area to find the records of compliance with the conditions.

Positioning through the menu

Menu, select from the table to record: a record number - to achieve absolute positioning

Location ... - positioning to achieve the conditions

Positioning command

Records of the positioning

Command format

Function Remarks
Absolute positioning GO TOP Record pointer to locate the first table documents Or GOTO TOP
GO BOTTOM Record pointer to locate the end of the table file Or GOTO BOTTOM
GO <n> The record pointer to the specified location record No. n
Relative positioning SKIP <n> Will record the current record pointer from moving upward or downward n months n is a positive move down

n is negative then move up

N down without a

Conditions for positioning LOCATE FOR <condition> [scope] By order of the search form, to meet the specified conditions until they found the first record Default scope is all records

4. Modify, and delete records

A table to open a non-read-only, you can browse through the menu in the window (or edit windows) or command from a command window directly with records edit, modify, and delete.

(1) menu mode

Amend the record:

From the table menu, select the field to replace ... → field in the dialog box, select Replace to replace the field and to be constructed to replace the conditions of expression and the replacement of the replacement →.

To delete records:

Select from the Table menu → Delete Record dialog box, select the Delete to delete the scope and delete button → → click Delete from the Table menu, select → completely erased in a message box click Yes.

Delete records, including two steps: marking the records you want to delete and completely delete the record with the deletion of tags

Marked with the restoration of deleted records:

Select from the Table menu in the resumption of the restoration of records → Record dialog box, select the scope and conditions of record → click Restore button.

(2) SQL command mode (Table advance can not open the way for programming)

Amend the record: The UPDATE-SQL command.

Command format: UPDATE <table> SET field 1 = expression 1 [, field 2 = expression 2 ...];

WHERE conditional expression

Cases: UPDATE xscj SET cj = cj * 1.05 WHERE cj> 80

To delete records: DELETE-SQL command to use.

Command format: DELETE FROM <table name> [WHERE conditional expression]

Cases: DELETE FROM xscj WHERE cj <60

(3) In order to use VFP command window:

Command format

Function Remarks
Insert INSERT [BLANK] [BEFORE] Designated in the current table position to insert an empty record or a number of new records BLANK: Under the current record, insert an empty record

BEFORE: Insert before the current record number of new records

Modified CHANGE [FIELDS fields of 1, ...] [FOR conditions] [LEDIT] [REDIT] In the editing window to modify the field LEDIT: out of the edit window

REDIT: out browser window

Replacement REPLACE field of 1 WITH expression 1 [ADDITIVE] [, field names 2 WITH expression 2 [ADDITIVE] ...] [Scope] [FOR conditions] The field values specified for the replacement of the expression. Such as: payroll of wages to some long The default is the current record of the scope of

ADDITIVE: Remarks field to add the replacement of the contents of the original behind Remarks

Remove DELETE [range] [FOR conditions] On the current form to meet the conditions for designated areas in the deletion of records marked with * The default is the current record of the scope of
PACK [MEMO] [DBF] Marked with the deletion of all records erased from the table (not to restore) DBF: Remarks document does not affect

MEMO: Remarks files deleted from the unused space

ZAP To delete all records of the current table, leaving only the structure Equivalent to


To resume RECALL [scope] [FOR conditions] The abolition of the current table to delete the specified tag records The default is the current record of the scope of

5. Screening records and field

(1) using the menu approach

From the table menu, select Properties → Properties dialog box in the workspace data filter, click the button box → Generator dialog box in the expression of structural conditions necessary for the expression and determine the screening field → click ... button → in the filter field to select the desired field and click the Options button → to determine: field → field screening to determine specified.

(2) use the command mode

Command format: SET FILTER TO condition expression

Cases: SET FILTER TO xb = 'M'

Note: SQL command access to the records from the restrictions of this screening order.

The use of Table 3.4

1. Workspace

Work area:

Used to identify a means to open the table area. A work area can only be opened at some point in a form, but they can work in a number of areas at the same time to open more than one table, one can work in many areas has been opened many times. Each work area has a number.

Table alias:

Open work area in the table definition for the table name. Can customize the alias, otherwise the system default on the table name as an alias. If a work area in a number of tables were open, the system default name in the table followed by increases _a, _b ....

Custom format alias: USE <table> ALIAS <alias>

The current work area:

The work area is being used. Can use the "data view window" or SELECT command to set up a work area for the current work area.

SELECT command format: SELECT <working code> | <alias>

To the operation of non-current form of the work area, other work areas can be selected as the current work, or in order to force the designated work area.

Command format: IN <working code> | <alias>

ALIAS ([working code]) function:

Designated work area to test the table alias, without referring to the current parameters of the alias table work area.

SELECT ([alias]) function:

Test designated alias of the table where the work of code, without parameters means the current work area code.

2. Table of open and closed

Way to open the table

Ways to close the table

Open the File menu On the toolbar to open Data view window to close USE
Data view window USE <table name> USE IN <alias> | <working code> CLOSE ALL
USE <table> IN <working code> USE <table> AGAIN CLOSE DATABASE CLOSE TABLE
USE <table> ALIAS <alias> System from the VFP, then close all of the table

3. Table of exclusive use and sharing of

Exclusive use of: a table can only be opened by a user. VFP6.0 in the default way to open exclusive.

Shared use of: a table can be opened multiple users at the same time.

Open the default system through the Tools menu to set the option ... or use the command:

SET EXCLUSIVE OFF & & default to open the way for the sharing of

SET EXCLUSIVE ON & & default to open the way for the exclusive

Or to force open: USE cj SHARED & & to share open cj Table

USE cj EXCLUSIVE & & to open exclusive table cj

4. The use of buffer access data in the table below

Data Buffer:

First record of the table stored in the buffer zone changes, the user decide whether to use the data in the buffer zone file update form. It is VFP6.0 in the multi-user environment for the protection of the data table to update records done and data technology to maintain operation.

Data buffer of the type:

Records of the buffer:

When the record pointer to move or close the table, the buffer automatically updates the corresponding record in the table.

Table buffer: When issuing a command to update the table on or off when Update table.

Lock buffer means: there are two types: open and conservative.

3.5 Table of Index

1. The basic concepts of

Recorded in the order:

Physical order: that is, the table records the order of storage. That with record number.

Logical order: Table to open the records were used to deal with the order.


Refer to table the document in accordance with paragraph a keyword or expression of the logical order of the establishment of record. It is composed of a series of record number of a list to provide quick access to data. Index table record does not change the physical order. Table file record is modified or deleted, the index file can be automatically updated.

Keyword index (index expression): used to create the index expression of a field or fields.

Note: 1) expression of a number of fields to index, the expression will have an impact on the calculation results of the index;

2) different types of expression constitute a field, it must convert data types.

Logo index (index name):

That is, the name of the keyword index. Must be underlined, at the beginning of letters or characters, and not more than 10 characters.

Index type: the main index, the candidate index, general index, index only.

Main Index:

Keyword index composed of the main field or expression, of all records in the table can not have duplicate values. Master Index Database Table is only applicable to the structure of composite index. Table can not be free to establish the main index; database and each table can only be the creation of a master index.

Candidate Index:

Paragraph in the specified keyword or expression does not allow duplication of the value of the index. In the database table and free the table for each table can create multiple candidate index.

General Index:

May decide to deal with the order of records, but allows duplication of the value field. In a table into a number of general index.

The only Index:

To participate in the keyword index or expression in the above table can be repeated, but in the index table, the records have duplicate values in which only the first store.

2. Index of the role of

Use Index used in the type of
Sort records to display, query, or print The use of the general index, index or main index candidate
In the field to repeat the value of control input and sort records Database table of the main index or use of candidate indexes, the free use of the candidate index table
Ready to set up relations between tables Based on the relationship between tables in the role, use the general index, the main index or candidate index

3. The type of index file

Index file types Features The number of keywords Restrictions
Composite index file


And the table file name to use the same basic name, with the table open automatically open. Can be seen as part of table structure. Multi-keyword expression, known as the logo. Effectively limit the expression of 240 characters.
Non-structural compound index file


Must be explicitly opened, use, and the basic form of different. One can not create the main index Multi-keyword expression, known as the logo. Effective at limiting the expression of 240 words.
Independent index file


Must explicitly open files by user-defined basic. General as a temporary index file. Expression of a single keyword. Effectively limit the expression of 100 characters.

Composite index file (extension. CDX) characteristics:

. In the creation of the index is automatically created when the logo.

. When in the open-open.

. In the same index file can contain multiple sequencing program, or a keyword index.

. When you add, change or delete records automatically to maintain.

4. Create a composite index

VFP to create a index file in two ways: Table Designer mode and command mode.

(1) Table Designer mode

File → Open from the displayed menu, select Table Designer Table Designer → click enter index index → index type name and select the index → select direction (ascending or descending order according to record) → box in the expression input as a basis for the index to sort → keyword filter in the filter expression box → click OK, finished.

(2) order the way

Order Function
INDEX ON <index keyword paragraph> TAG <index ID> [FOR conditions] [ASCENDING | DESCENDING] [CANDIDATE | UNIQUE] INDEX ON command with the establishment of an index file
ALTER TABLE <table name> ADD PRIMARY KEY <paragraph name keyword> TAG <index ID name> SQL command used to create the main index
ALTER TABLE <table name> ADD UNIQUE <paragraph name keyword> TAG <index ID name> SQL command used to create a candidate index

Note: 1) Remarks-type field and general-purpose keyword field can not be used as the index segment;

2) Do not create useless index, so as not to reduce the system performance;

3) no longer timely clean-up logo to use the index to improve the system efficiency.

4) in the composite index more than the index, a moment that there is only one index on the table work.

5. Modify, and delete index

Modified: Open Table Designer dialog box in the index necessary changes;

Or use the same command to re-establish a labeling index and expression of a different index.

Delete: Open Table Designer, the index does not need dialog to remove the logo can be indexed;

Or use the command: DELETE TAG ALL | Index ID 1 [, index ID 2] ...

Logo does not need to delete the index, ALL said that all the logos.

6. The use of index

Function Command format
At the same time, open the table designated master index USE <table> ORDER <logo name>
Open the table for the master index to determine SET ORDER TO <logo name> [ASCENDING | DESCENDING]]
Zhang has been the establishment of a search index table FIND <expression>
The first time in search table records SEEK <expression> [<index ID name>]
SEEK <expression>

FoxPro Tutorial Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.