This presentation is the property of its rightful owner.
Sponsored Links
1 / 155

第三章 自由表( Free Table ) PowerPoint PPT Presentation


  • 51 Views
  • Uploaded on
  • Presentation posted in: General

第三章 自由表( Free Table ). 3.1 表结构的创建和修改. 3.2 表 数据的输入. 3.3 表的打开、关闭、显示和定位. 3.4 记录数据 的修改和删除. 3.5 表 的排序、索引和查询. 3.6 表 的计算、汇总. 3.7 多表的 关联、连接和更新. 3.8 其它 有关命令操作. 3.9 结构化查询语言 SQL. 第三章 自由表( Free Table ). 本章学习 VFP 的自由表文件 .DBF 的建立和操作,分为二种方式来学习:

Download Presentation

第三章 自由表( Free Table )

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Free Table

3.1

3.2

3.3

3.4

3.5

3.6

3.7

3.8

3.9SQL


Free Table

VFP.DBF


  • 3.1

  • 09NAMEXH-1

  • :

  • (1)Character(C):ASC(Text)

  • (2)(Numeric)(N)0


  • (3)Logical(L)....T(t)Y(y)F(f)N(n)

    (4)Date(D)VFPYYYYMMDD(4)SET DATEYY.MM.DD

    (5)Memo(M).FPT

    (6)General(G)MicrosoftOLE(Object linking and embedding).FPT


    OLE

    (7)Date Time(T)yyyymmddhhmmss

    (8)Float(F)NASC()IEEE(Institute of Electrical and Electronics Engineers)

    (9)Double(B)(Numeric) Double()


    (10) (Integer) (I)

    (11)Currency(Y)(N)2337203685477.580822337203685477.5807$ $388.88

    (12) (binary)

    (13)(Binary)


    254(N)=++1()

    1

    8(Binary)4

    (F)=++1()(Binary)1254

    4:(N)(F)


    CREATE [<>|?]

    .DBF

    CREATE STU.DBF

    P40


    1:DF.DBF

    (1)(C)9

    (2)(D)8

    (3)(C)11

    (4)(C)10

    (5)(C)50

    (6)(C)0

    (7)(C)50

    (8)(C)20

    (9)(D)8

    (10)(N)3

    (11)(N)11 4

    (12)(C)50

    (13)(M4


    create DFDBF


    2STU2.DBF

    1 2

    1


    MODIFY STRUCTURE

    STU2.DBF

    MODIFY STRU

    2


    3.2

    VFP?Y

    *

    APPEND

    APPEND [BLANK]

    ()

    --


    INSERT

    INSERT [BEFORE] [BLANK]

    BROWSE

    BROWSE

    3


    3.3

    1USE [<>]

    <>,

    2USE

    3 close all


    1

    LIST

    LIST [OFF] [<>][[FIELDS]<>]

    [WHILE<>][FOR<>][TO PRINT|TO FILE<>]

    LIST

    [OFF]


    RECORD n

    NEXTn (n)

    REST

    ALL [<>]ALL

    [<>]

    [[FIELDS]<>]

    WHILE<>]


    <> (WHILE<>])

    <>.T.)(,F.)

    [FOR<>]

    [TO PRINT]

    [TO FILE<>]<>

    1STU180

    LIST ALL FOR >=80


    2STU180


    3STU2


    4STU2


    DISPLAY

    DISPLAY [OFF][<>][[FIELDS]<>][WHILE<>][FOR<>][TO PRINT|TO FILE<>]

    DISPLAY LIST

    (1) VFPDISPLAYLIST

    (2) DISPLAYLIST

    3STU24


    2

    LIST STRUCTURE [TO PPRINT|TO FILE<>]

    3

    LIST STATUS [TO PRINT|TO FILE<>]


    GOTOSKIP

    1GO[TO] [ RECORD <n>]n

    GO[TO] TOP1

    GO[TO] BOTTOM


    5:

    USE stu1

    GO BOTTOM

    DISPLAY


    GO TOP

    DISPLAY

    GO 3

    disp


    2SKIP [n]

    (-)(+N

    n=+1

    SKIPGOTO

    SKIP


    4.6


    3.4

    1 EDIT

    EDIT

    7


    2BROWSE

    BROWSE[FIELDS<>][LOCK<>][FREEZE<>]

    BROWSE

    [LOCK<>]

    [FREEZE <>]


    [FOR<>]<>

    8

    use STU1

    browse lock 1 freeze


    3

    :REPLACE [<>] <1> WITH <1>[,<2> WITH <2>[ADDITIVE]][FOR<>][WHILE<>]

    <><>

    1use stu1


    4(General)

    VFPWindows(OLE)MODIFY GENERALOLE(General)

    MODIFY GENERAL<>

    :(General), ,.


    1

    DELETE [<>][WHILE<>][FOR<>]

    .,.

    *, .


    9


    2

    RECALL [<>] [FOR<>] [WHILE<>]


    3

    PACK


    4ZAP

    ZAP [IN<>|<>]

    ZAPDELETE ALLPACK



    3.5

    SORT TO <> ON <1> [/A][/D][/C][,<2>[/A][/C][/D]] [<>] [FIELDS<>] [FOR<>] [WHILE<>]

    /A/D.DBF)

    1

    2>[/A][/D][/C]09AaZz[/C]ASC09AZaz


    3

    1:


    1

    . IDXCOMPACT]

    . CDX ()


    2

    .IDX

    1: INDEX ON <> TO <> [COMPACT] [ADDITIVE]

    <>TO

    2


    .CDX

    2:INDEX ON <> TAG <> [OF<>] [FOR <>]

    [OF<>]


    STU2


    1

    22[OF<>] TAG <> .CDX[OF<>] TAG <> <>

    3 <>

    INDEX ON + TO A2

    INDEX ON STR)+STR TO A3

    4[ADDITIVE]


    (Table Designer)

    ()(Primary CandidateUnigue(Expression)

    1(Table Designer).CDX

    2


    3

    1

    1 USE <> INDEX <>

    2 USE <> [ORDER [TAG] <>]

    USE STU2 INDEX A2

    USE STU2 INDEX BB ORDER TAG XB

    USE STU2 ORDER TAG XB

    USE STU2 INDEX STU2 ORDER TAG XH


    1<>

    2 [ORDER [TAG]<>]


    2

    1SET INDEX TO [<>>|[TAG]<>|<>[OF<>]]]

    SET INDEX TO A1A2 && A1

    SET INDEX TO BB

    SET INDEX TO BB TAG XBOF BB

    SET INDEX TO STU2 TAG XB

    SET INDEX TO BB TAG XBXH

    SET INDEX TO <>

    SET INDEX TO <>TAG<>


    2SET ORDER TO [<>|<>|[TAG]<>[OF<>][IN<>] [ASCENDING|DESCENDING]]

    SET ORDER TO A2SET ORDER TO 2 && A2

    SET ORDER TO TAG XB OF BB

    SET ORDER TO TAG XB

    SET ORDER TO <>

    SET ORDER TOTAG <>[OF<>]


    1 FIND

    FIND <>|<>

    :<>()

    2 SEEK

    SEEK <>

    SEEKFIND,CND


    3LOCATECONTINUE

    LOCATE

    LOCATE [<>] [FOR<>] [WHILE<>]

    [<>]

    CONTINUE

    CONTINUE


    use stu-1

    locate all for >=80

    disp

    continue

    disp

    continue

    disp

    ...


    3.6

    VFP

    1

    COUNT[<>][FOR<>][WHILE<>][TO<>]

    3.32 P74Ffile(P633-4),12000M1

    USE Ffile

    COUNT FOR ="".AND.12000.00 TO M1

    ? M1=M1

    M1=2


    2

    SUM [<>][<>][TO <>|TO ARRAY <>][FOR <>][WHILE<>][NOOPTIMIZE]

    P744.33Ffile

    USE Ffile

    SUM ALL , TO G1,G2 FOR =""

    ? =G1

    = 45222.00

    = G2

    = 84

    COUNT ALL FOR ="" TO N

    ? =G1/N =G2/N

    = 22611.00 = 42


    3AVERAGE

    AVERAGE[<>][<>][TO <>|TO ARRAY <>] [FOR<>][WHILE<>][NOOPTIMIZE]

    4.34Ffile().

    USE Ffile

    AVERAGE ALL TO N1,N2 FOR =""

    ? =N1 =N2

    = 22611.00 = 42


    TOTAL TO <> ON <>[FIELDS <>][<>][FOR<>] [WHILE<>][NOOPTIMIZE]

    <>

    1.TOTAL<>

    2.


    ()

    3.35SY

    USE SY

    INDEX ON TO SYI

    SET INDEX TO SYI

    LIST

    Record#

    1201048.00 250.00 298.00

    2202050.00 300.00 350.00

    3203045.00 500.00 545.00

    43001 80.00 650.00 730.00

    5300850.00 450.00 500.00

    63007 45.00 500.00 545.00


    TOTAL ON TO NSUM

    USE NSUM

    LIST

    Record#

    12010143 1050.001193.00

    23001175 1600.001775.00


    3.7

    1

    1

    JW.DBFP77:

    2

    STUDENT,LESSONLINK

    ()STUDENTLESSONLINKSTUDENTLESSON


    2

    VFP255()32767

    1 10110

    AJ1132767 w11w32767)

    2

    3

    SELECT </ ><>


    select a

    use stu1

    select b

    use stu2

    select 1

    list

    use

    VFP11<>

    4<>ALIAS USE <> ALIAS <>

    ALIAS


    sele 3

    use stu1 alias s1

    sele stu2

    List

    sele s1

    List

    close all

    5

    < / / >.<>

    < / / >><>

    < / / > <>


    3.36STU1STU2

    SELE 1

    USE STU1

    SELE 3

    USE STU2

    SELE 1

    LIST NEXT 3 ,C>,C>C

    Record# C-CC

    1 78/05/20

    2 78/05/20

    3 78/05/20

    ()


    SET RELATION [TO <>|<> INTO <>][TO <>|<> INTO <>]ADDITIVE

    ()


    (1)

    (2)

    (3)

    (4)EOF()=.T.

    (5)VEPABAC

    (6)[ADDITIVE]

    (7)(SET RELATION TO )


    1

    SELE 1

    USE STU1

    INDEX ON TO XH1

    SET INDEX TO XH1

    SELE 3

    USE STU2

    INDEX ON TO XH

    SET INDEX TO XH

    SELE 1

    SET RELATION TO INTO C

    LIST NEXT 3 C>C>C>

    Record# C>C>C>

    1 78/05/20

    2 77/09/07

    3 78/11/11


    2

    SELE 1

    USE STU1

    SELE 2

    USE STU2

    SELE 1

    SET RELATION TO RECNO() INTO B

    LIST NEXT 3 C>C>C>

    Record# C>C>C>

    1 78/05/20

    2 77/09/07

    3 78/11/11


    3

    3-1


    VFP

    1

    (Open)

    2


    .VUE


    JOIN WITH <> TO <> [FIELDS <>] FOR <>

    <>.DBF

    FOR<>

    3.42 STU1STU2STU4

    SELE 1

    USE STU1

    SELE 2

    USE STU2

    JOIN WITH A TO STU4 FIELDS ,A,,A FOR =A


    • USE STU4

    • LIST

  • Recorde# A A

  • 1962101 90.00

  • 2962102 87.00

  • 3962103 80.00

  • 4962104 94.00

  • 5962105 76.00

  • REPLAC

    UPDATE ON <> FROM <> REPLACE <> WITH <> [<> WITH <>] [RANDOM]

    <>()()


    UPDATE <><><><>

    5STU1STU2

    SELE 1

    USE STU1

    SELE 2

    USE STU2

    INDEX ON TO XH

    SET INDEX TO XH

    UPDATE ON FROM A REPLACE WITH A>

    LIST

    3-2


    3.8

    CLOSE ALL

    1

    1

    1COPY TO <> [<>][FIELDS<>][WHILE<>][FOR<>]

    (1)(2)<>


    Use stu1

    Copy to stu3

    Use stu3

    list

    2COPY FILE <> TO <>

    (1

    (2)copy


    2

    COPY STRUCTURE TO <> [FIELDS<>]

    <>

    Use stu1

    Copy structure to stu4 fislas ,,

    Use stu4

    list


    APPEND FROM <> [FIELDS<>][WHILE<>][FOR<>]

    :


    3.9

    SQL


    3.9.1 SQL

    3. 9.2

    3. 9.3

    3. 9.4


    3. 9.1 SQL

    SQLStructured Query LanguageSQL2070IBMSEQUEL2080SQLANSI

    OracleSybaseSQL ServerVisual FoxProSQLSQL SQL

    SQLSQL


    SQL

    1SQLSQL

    Visual FoxPro


    31 SQL

    SQL

    CREATEALTERDROP

    SELECT

    INSERTUPDATEDELETE

    GRANTREVOKE


    2SQLSQLSQL


    3SQLSQL


    31 SQL

    SQL

    CREATEALTERDROP

    SELECT

    INSERTUPDATEDELETE

    GRANTREVOKE


    4 SQLSQLVisual FoxPro


    VFPSQL VFP

    VFP.PRG.SCX


    SQLSQL

    SQL SQLSQL

    Visual FoxPro SQL


    3. 9.2

    SQL

    SELECT-SQL

    SELECT-SQLSQL


    SQLSELECT

    Visual FoxProSQLSELECTSELECT


    SELECT [ALL|DISTINCT]

    [<>.]<>[AS <>][,[<>.]<>[AS <>]]

    FROM [<!]<>[[AS] <>]

    [[INNER | LEFT [OUTER] | RIGHT[OUTER]|FULL [OUTER]

    JOIN <>!]<>[[AS]<>][ON <>]

    [[INTO <>|[TO FILE<>][ADDITIVE]

    |TO PRINTER [PROMPT]|TO SCREEN]]

    [PREFERENCE <>][NOCONSOLE][PLAIN][NOWAIT]

    [WHERE <1>[AND <2>]

    [AND|OR <1>[AND|OR <2>]]]

    [ORDER BY <1>[ASC|DESC][,<2>[ASC|DESC]]]

    [GROUP BY <1>[,<2>]]

    [HAVING <>]

    [UNION[ALL]SELECT]


    • SELECT

    • FROM

    • WHERE

    • ORDER BY

    • GROUP BY

    • HAVINGGROUP BY


    SELECTWHEREFROMSELECTGROUP<>GROUPHAVINGHAVINGORDER<>

    SELECTSELECTFROMWHERE


    3. 9.2.1

    SELECT [ALL|DISTINCT]

    [<>.]<>[AS <>],

    [<>.]<>[AS <>]

    FROM <1>[1>][,<2>[2>]

    *

    ALL

    DISTINCT


    OPEN DATABASE D:

    SELECT * FROM

    *

    SELECT DISTINCT

    AS FROM


    32

    SELECT SELECT


    SELECT AVG() AS

    FROM


    3. 9.2.2

    WHERE

    WHERE <>


    33


    NOT

    SELECT , AVG()

    AS FROM

    WHERE =

    SELECT ,, FROM

    WHERE IN (,)

    SELECT ,, FROM

    WHERE = OR =


    560650

    SELECT ,, FROM WHERE BETWEEN 560 AND 650

    SELECT ,, FROM WHERE >=560 AND <=650

    SELECT , FROM

    WHERE LIKE %


    • SELECTSQLSELECT

    • SELECTSELECT


    3. 9.2.3

    SELECTSELECTSELECTWHERESELECT WHERE

    SELECT

    Visual FoxPro


    1.

    CREATE TABLE ( C(5), C(20))

    CREATE TABLE ( C(5) C(5), I

    SELECT FROM WHERE =;

    (SELECT FROM WHERE =)

    SQL0101


    2.

    WHEREANYSOMEALLIN


    1ANY

    CREATE TABLE ( C(5) C(5), I )

    0102

    SELECT , FROM

    WHERE =01 AND >ANY

    (SELECT FROM

    WHERE =02)

    029052010252


    2ALL

    0102

    SELECT ,FROM

    WHERE =01

    AND >ALL

    (SELECT FROM

    WHERE =02)

    029052010290


    3IN

    CREATE TABLE ( C(5), C(20))

    CREATE TABLE ( C(5) C(5), I )

    SELECT FROM WHERE IN;

    (SELECT FROM

    WHERE =

    OR =)

    IN=ANY


    3. 9.2.4

    SELECT

    1.

    SELECT a., a., b., c., b.FROM a, b, c;

    WHERE a.=b. AND b.=c.


    2.

    02200109

    SELECT a., a. FROM a, b;

    WHERE a.>b.AND a.=b.AND b.=02AND b.=200109


    3. 9.2.5

    1. ORDER

    SELECT

    ORDER BY <1> [ASC | DESC]

    [,<2>[ASC | DESC]]

    SELECT1

    ASC() DESC


    550650

    SELECT ,, FROM WHERE BETWEEN 550 AND 650 ORDER BY DESC


    2. INTO

    [INTO <>]

    | [TO FILE<>[ADDITIVE]

    | TO PRINTER]


    <> 3

    ARRAY <>

    CURSOR <>

    DBF <>|TABLE <>(dbf)


    TO FILE <>[ADDITIVE]

    (txt) ADDITIVE

    TO PRINTER


    test1.txt

    SELECT ,, FROM

    WHERE BETWEEN 550 AND 650

    ORDER BY DESC TO FILE test1


    3. UNION

    [UNION [ALL] <SELECT>]

    ALLALL

    1

    2SELECT

    3

    4<SELECT>ORDER BY


    0102

    SELECT FROM

    WHERE =01

    UNION SELECT FROM

    WHERE =02


    4.

    GROUP BY <1>[,<2>]

    <>SQL

    HAVING <>

    HAVINGGROUP BY


    SQLWHEREGROUP BYHAVINGWHEREGROUP BYHAVING

    WHEREHAVINGWHEREHAVING


    SELECT ,COUNT() FROM GROUP BY

    SELECT AVG() FROM ;

    GROUP BY HAVING COUNT(*)>=2


    C120C140C150C16080

    SELECT ,AVG() AS ;

    FROM ;

    WHERE IN ("c120","c140","c150","c160");

    GROUP BY ;

    HAVING AVG()>=80

    : WHEREC120C140C150C160GROUP BYAVG80


    3. 9.3

    3. 9.3.1

    CREATE TABLE <>[FREE]

    (<1> <>(<>[,<])

    [NULL|NOT NULL]

    [CHECK <1>[ERROR<>]]

    [DEFAULT <1>][PRIMARY KEY | UNIQUE]

    [<2> ]) )


    FREEFREE

    12

    NULLNOT NULLNULL

    CHECK


    ERROR

    DEFAULT

    PRIMARY KEY

    UNIQUE


    CREATE TABLE ;

    ( C (8) PRIMARY KEY NOT NULL;

    C (8) NOT NULL,

    C (2) DEFAULT CHECK =; OR =ERROR ,;

    D CHECK<={^1990-01;

    -01} AND >={^1970-01-01},;

    N (3) ,;

    L;

    M,;

    G )


    SQLCREATE TABLE


    3. 9.3.2

    DROP TABLE <>

    DROP TABLE

    DROP TABLE


    3. 9.3.3

    SQLALTER TABLE

    1

    ALTER TABLE <>

    [ADD /ALTER[COLUMN]

    <> <>[<> []

    [NULL/NOT NULL]

    [CHECK <> [ERROR <>]]

    [DEFAULT <>]

    [PRIMARY KEY/UNIQUE]

    ADD

    ALTER


    >=500500

    ALTER TABLE ;

    ALTER COLUMN N(3)

    CHECK>=500

    ERROR 500


    2

    ALTER TABLE <>

    [DROP[COLUMN] <>

    [SET DEFAULT <>] [DROP DEFAULT]

    [SET CHECK <> [ERROR <>]] [DROP CHECK]

    [ADD PRIMARY KEY <>] [DROP PRIMARY KEY]

    [ADD UNIQUE<>] [DROP UNIQUE TAG <> ]

    [RENAME COLUMN <> TO <>]


    DROP

    SET

    ADD

    RENAME

    ALTER TABLE

    RENAME COLUMN TO


    3. 9.4

    SQL

    3. 9.4.1

    Visual FoxProSQL

    1

    INSERT INTO <>

    [(1[<2>[,]])]

    VALUES(<1>[,<2>[,]])


    VALUES


    INSERT INTO

    VALUES(231002,,

    ,{^1988-09-10},.T.,

    ,610,)

    INSERT INTO (,)

    VALUES( 231002 ,)


    2

    INSERT INTO <>

    FROM ARRAY <> |FROM MEMVAR]


    A5

    A(1)=231013

    A(2)=

    A(3)=

    A(4)={^1988-09-10}

    A(5)=.T.

    INSERT INTO FROM ARRAY A

    INSERT INTOFoxProINSERT


    3. 9.4.2

    Visual FoxProDELETE

    DELETE FROM [<>!] <> [WHERE <> ]


    DELETE FROM WHERE =PACKRECALL


    3. 9.4.3

    UPDATESELECT

    UPDATE [<>!]<>

    SET<1>=<1>[,<2>=<2>] [WHERE<>]

    WHERE


    UPDATE SET =

    WHERE =

    20

    UPDATE SET =+20

    WHERE IN

    SELECT FROM

    WHERE =


    Thank you