Tutorials to .com

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

Essentials VFP Chapter IV to create and use databases

Print View , by: iSee ,Total views: 19 ,Word Count: 3218 ,Date: Sat, 18 Apr 2009 Time: 8:07 PM

4.1 vfp database

1. The fundamental component database

database by more than one interrelated data table that can contain one or more tables, views, to the remote data source connections and stored procedures.

View (view):

Stored in a database, by reference to one or more tables, or other relevant data view virtual table, which can be local, remote or without parameters.

Stored procedure (stored procedure):

Is stored in the database of a process. The process can include a user-defined functions and functions of any order.

The creation of the database system automatically generates three documents:

Database file: extension. DBC

Notes database file: extension. DCT

Database index file: extension. DCX

2. The database design process

1) clearly established the purpose of the database and use

2) design of data tables (including table structure and table records)

3) to establish the relationship between form

4) Improved design

4.2 Create Database

(Small map View larger map)

Essentials VFP Chapter IV to create and use databases

1. The creation of a database

Database creation process will usually be addressed in a number of commonly used the following steps:

1) create a new table using Table Designer → (set field properties, and table attributes)

2) Table → Add button with the database design or database menu

3) create a view using the View → Wizard, View Designer

4) the establishment of relations → parent table with the mouse to drag the index of the relevant sub-index on table

5) → Relations with the database editor menu or shortcut menu generator → referential integrity

6) → Relations with the removal or delete the shortcut menu key

7) Revise Table → Table Designer

8) to delete or view → button with the database design or database menu

2. The new database, open, closed

(1) new database:

From the File menu, click New → In the New dialog box, select the database and click the New Document dialog box → given in the creation of database file name and location → Save in the database designer to create the required database.

Or from a command window, type the command: create database database name

(2) Open the database:

From the File menu, click Open → Open dialog box is given in the library file name and save location and determined.

Or from a command window, type the command: open database database name

(3) shut down the database:

From a command window, type the command:

close database & & Close the current database

Or close all & & close all open database

Note: 1) the closure of the database table does not mean the closure of a database, but the database is shut down the data sheet was closed at the same time;

2) use the mouse to the closure of the database design can not represent the browser window and close the database.

3. With database design database design

(1) out of the design, and create a new data table

(Small map View larger map)

Essentials VFP Chapter IV to create and use databases

Method 1: From the database menu, select New Table.

Method 2: Right-click the Database Designer window, from the shortcut menu, select New Table.

Method 3: Click the Database Designer toolbar button to a new table.

Method 4: From the File menu, click New, select the table in the dialog box.

Note:

Database tables in the Table Designer Table of contents and more than freedom, an increase of field attributes and set attribute table. Set up to verify the rules are intended to make the data input to meet the requirements, there are contradictions in sending the wrong message.

(2) Senior attribute database table

Attribute Type Property Name Role
Field properties Field display Properties Format Determine the field content is displayed in the style
Input mask Designated field of the format of input data (that is, the input of any of the contents are displayed as the symbols)
Title When used in the browser instead of the meaning of the name of the field names not intuitive
Field validation rules Rules So that the transmission of data in line with the conditions set
Information When the transmission of data in violation of the rules, the system prompts wrong
Default value Input data to reduce the workload of repetitive
Field Notes So that the field has better readability
Table Properties Long table Different file name with the table, set up a long table at a glance
Records to verify the rules Rules So that the lost records of compliance with the conditions set
Information Lost when the records in violation of the rules, the system prompts wrong
Trigger Insert Trigger When the inserted records in line with this rule when it is inserted into the table
Update Trigger When the records of the revised rules in line with this, it can be modified
Delete trigger When the records to be deleted in line with the rules before they can be removed
Table Notes So that table has better readability

Field-level rules:

A field related to the effectiveness of the rules or modify the insert is activated when field values are used for testing the accuracy of data input.

Field set up rules for the method of authentication:

. In the form designer to create rules to select the field names.

. In the "rules" to choose the box next to "..." button.

. In the expression of the effectiveness of generator set expression, and select "OK."

. In the "information" box, type the use of quotation marks from error messages.

. In the "default value" box, type a reasonable initial value.

. Attention to different types of data representation.

. Select "OK"

Record-level rules:

A record of the effectiveness of the relevant rules, when to insert or modify records activation, commonly used to test data entry and correct. Records are deleted when the effectiveness of the rules of non-use. Record-level rules in the field-level rules and triggers to activate before, in the work of buffer updates.

Trigger:

In an insert, update or delete the records after the operation code-level events. Events can be different to different actions. They are commonly used in the cross-table integrity.

(3) add a table in the database

Method 1: From the Add menu, select the database table, from the Open dialog box to select the desired table and determined.

Method 2: Right-click the Database Designer window, from the shortcut menu, select Add Table.

Method 3: Click the database design tool to add button column.

Note:

A data table at the same time can only belong to one database, other databases have been part of the table can not be added, to be first out of the database restored to its free form.

(4) to create and add the view

View used to display one or more records in the table and be able to update the data source table, the view can only exist in the database.

Method 1: From the database menu or select a new long-range view of the new local view

Method 2: Right-click the Database Designer window, from the shortcut menu or select a new long-range view of the new local view

Method 3: Click the Database Designer toolbar or a new long-range view of the new local view button

(5) operation in the database tables and views

Database designer can use the toolbar is very easy to carry out the following steps:

Browse: in the browser window to display the contents of table or view.

Modifications: the design out or View Designer to modify table or view.

Remove: to restore the table from the database into a free form. Of view equivalent to the deletion of the removal.

Delete: to remove completely from the disk data tables and views, and can not be recovered.

(6) in the table to establish a permanent relationship between

Permanent relationship:

Database table is a relationship between not only the existence of run-time and has been retained. Form a permanent relationship between the index established by.

One-to-many relationships:

Table between a relationship in which, the main table with the relevant records of each table associated with a number of records (for each primary key value in the relevant table may appear several times).

One-on-one relationships:

Table between a relationship in which, the main table records each of the relevant table only with a record associated.

Create a permanent relationship between tables:

Designer in the database, select the name you want associated with the index, and then drag it to the relevant table of the index, the parent table by dragging the index must be a candidate for the main index or index. After the establishment of good relations, such relations in the database designer will be shown as a straight line connecting the two tables.

Note: need to create the index before they are able to build relationships.

Permanently delete the relationship between tables:

In database design, click the relationship between the two line form. line relationship, indicating that the relationship has been selected, press the delete key.

Editor relations:

Click the desired relationship between the lines, right-click the mouse, from the shortcut menu, select Edit relations, relations in the Edit dialog box table indexing other relevant election or modify the rules of referential integrity.

Referential integrity (RI):

Control of data consistency, in particular, is different from the table outside the main keywords and the rules of the relationship between keywords. Visual foxpro to use user-defined field and record-level rules with reference to the completion of class rules.

(7) in the temporary relationship between form

Temporary relations:

Open the data in the table set relation between the command used to establish a temporary relationship, or work in the data view window of the establishment. After the establishment of a temporary relationship, the target sub-table with the main table record pointer movement. Table has been closed, the relationship between an automatic discharge.

Temporary relations and permanent contact with the distinction between relations:

Contact:

(1) must be a clear relationship between the two tables is indeed objectively there is a relationship between the (one-to-many or one-on-one relationship).

(2) permanent relations can be on many occasions as the default temporary relations.

Distinction:

(1) temporary relations are used to open the table between the two control records related to the visit between tables; and permanent relationship is primarily used for storage related to the referential integrity between tables, can be attached as the default temporary relations or query terms default connection.

(2) open the temporary relations in the table after the establishment of the use of set relation command, with the lifting of the closure of the table; a permanent relationship between the permanently stored in the database without having to use the table every time to re-create.

(3) temporary relationship between a free table, library table or free table between the table and between the Treasury, and a permanent relationship can only be established between the database table.

(4) The relations between the two can not have a table the main table (unless the two main table through the sub-table with a master index to establish the relationship between the interim), the relationship is not permanent.

4. The way to operate the database with the command

A list of commonly used commands

Command format Function
create database of library files Create a new database file
Library open database file name Open the specified library file
close database Close the current database and data table
close all Close all the database and data tables, and a home work area for the current work area, as well as the closure of a number of window
modify database Modify the current database file structure
delete database base file name Delete the specified library file
Library open database file name

add table table name

Add a table in the database
Library open database file name

remove table table name

Will be removed from the database table
Library open database file name

remove table table file name delete

Will be removed from the database table and deleted from the disk
use database file name! Table Name

browse

"!" That do not use a database table in the current
Set relation to the relationship between expression into code | alias The establishment of a temporary relationship between tables
Set relation to Delete temporary relationships between tables
alter table child add foreign key table name tag index keyword index logo references the parent table name [tag index logo] Create a permanent relationship
alter table child table drop foreign key tag index logo Permanent relationship between the deletion

5. With CREATE TABLE-SQL command to create database tables

The general form of the command:

Create table table 1 [free] (Field Name Field Type code 1 [(field width [, number of decimal places])] [null | not null] [check field rules 1 [error "message 1"]] [default default] [primary key | unique] [references table 2 [tag index ID 1]] [, field names 2 field type code [(field width [, number of decimal places])] [null | not null]] [check field rules 2 [error "message 2"]] [default default value]) | from array of arrays

The use of examples:

creat data xscjgl &&
create table xs (;
xh c(4) default "1201" primary key,;
xm c(6), ;
xb c(2) check xb="" or xb="" error"" default "",;
nl n(2) null;
) &&
create table kc (;
kch c(3) unique, ;
kcm c(10),;
xf n(3,1),;
bxk l;
) &&
create table cj(;
xh c(4) default "1201" reference xs tag xh, ;
kch c(8),;
ksrq d,;
cj n(5,1);
) &&
modi data
alter table cj add primary key xh+kch+str(cj,4,1) tag dh &&
creat data xscjgl &&
create table xs (;
xh c(4) default "1201" primary key,;
xm c(6), ;
xb c(2) check xb="" or xb="" error"" default "",;
nl n(2) null;
) &&
create table kc (;
kch c(3) unique, ;
kcm c(10),;
xf n(3,1),;
bxk l;
) &&
create table cj(;
xh c(4) default "1201" reference xs tag xh, ;
kch c(8),;
ksrq d,;
cj n(5,1);
) &&
modi data
alter table cj add primary key xh+kch+str(cj,4,1) tag dh &&

6. View and set the attributes of a database

(1) dbgetprop () function to view the database properties:

Command format: dbgetprop (cName, cType, cProperty)

(2) dbsetprop () function set the database properties:

Command format: dbsetprop (cName, cType, cProperty, ePropertyValue)

(3) dbsetprop () function to add fields to the table title and description

The general form: dbsetprop ( 'table name. Fieldname', 'field', 'caption | comment', 'title | description')

Cases: dbsetprop ( 'xs.xh', 'field', 'caption', 'Student ID')

dbsetprop ( 'cj.xh', 'field', 'comment', 'this table should be the No. school students learn in the table corresponds to No.')

7. The restraint mechanism database table

Constraint mechanism Level Activation time
NULL value Field When leaving the field in from the browser, or perform INSERT or change the field values REPLEASE
Field-level rules Field When leaving the field in from the browser, or perform INSERT or change the field values REPLEASE
Main index / candidate index Record Occurred when record updated
Record-level rules Record Occurred when record updated
Trigger Table In the INSERT, UPDATE or DELETE events, changes in the value table
VALID clause Form Out records


FoxPro Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.