VFP Chapter Essentials to create and use tables
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.
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
Include: field name, data type, field width, number of decimal places, null value support
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, |
|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
|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
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
|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.
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
Records of the positioning
|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:
|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 |
DELETE ALL + PACK
|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
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.
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
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.
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.
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.
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
|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
|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>]|
FoxPro Tutorial Articles
- INTERNET use VFP to create a database in Dynamic Query
- VFP songs with the preparation of the on-demand multi-media devices
- VFP edit box method for automatic correction
- VFP to use the system variables _SCREEN
- VFP table to track the realization of the log method
- Modified to achieve by the standard class library VisualFoxPro
- Grid control the use of VFP General entry interface design
- VFP Chapter Essentials to create and use tables
- Essentials VFP Chapter IV to create and use databases
- VFP Chapter VI Essentials object-oriented programming
- VFP Chapter V Essentials to create queries and view
- VFP form the basis of Chapter VII of the Design Guide
- VFP Chapter Essentials class to create and use
- VFP Chapter IX Essentials menu and toolbar design
- VFP Chapter VIII of the basis of statements of curriculum design and labeling
- Chapter XI Essentials VFP application development
- VFP Chapter Essentials basic knowledge of database systems
- VFP chapter Essentials language based on VFP
- Batch print envelopes with VFP
- Animation of simulation in the VFP
Can't Find What You're Looking For?
Rating: Not yet rated