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

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


  • 48 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

Free Table

3.1

3.2

3.3

3.4

3.5

3.6

3.7

3.8

3.9SQL


Free table

Free Table

VFP.DBF


Free table

  • 3.1

  • 09NAMEXH-1

  • :

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

  • (2)(Numeric)(N)0


  • Free table

    (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


    Free table

    OLE

    (7)Date Time(T)yyyymmddhhmmss

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

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


    Free table

    (10) (Integer) (I)

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

    (12) (binary)

    (13)(Binary)


    Free table

    254(N)=++1()

    1

    8(Binary)4

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

    4:(N)(F)


    Free table

    CREATE [<>|?]

    .DBF

    CREATE STU.DBF

    P40


    Free table

    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


    Free table

    create DFDBF


    Free table

    2STU2.DBF

    1 2

    1


    Free table

    MODIFY STRUCTURE

    STU2.DBF

    MODIFY STRU

    2


    Free table

    3.2

    VFP?Y

    *

    APPEND

    APPEND [BLANK]

    ()

    --


    Free table

    INSERT

    INSERT [BEFORE] [BLANK]

    BROWSE

    BROWSE

    3


    Free table

    3.3

    1USE [<>]

    <>,

    2USE

    3 close all


    Free table

    1

    LIST

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

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

    LIST

    [OFF]


    Free table

    RECORD n

    NEXTn (n)

    REST

    ALL [<>]ALL

    [<>]

    [[FIELDS]<>]

    WHILE<>]


    Free table

    <> (WHILE<>])

    <>.T.)(,F.)

    [FOR<>]

    [TO PRINT]

    [TO FILE<>]<>

    1STU180

    LIST ALL FOR >=80


    Free table

    2STU180


    Free table

    3STU2


    Free table

    4STU2


    Free table

    DISPLAY

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

    DISPLAY LIST

    (1) VFPDISPLAYLIST

    (2) DISPLAYLIST

    3STU24


    Free table

    2

    LIST STRUCTURE [TO PPRINT|TO FILE<>]

    3

    LIST STATUS [TO PRINT|TO FILE<>]


    Free table

    GOTOSKIP

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

    GO[TO] TOP1

    GO[TO] BOTTOM


    Free table

    5:

    USE stu1

    GO BOTTOM

    DISPLAY


    Free table

    GO TOP

    DISPLAY

    GO 3

    disp


    Free table

    2SKIP [n]

    (-)(+N

    n=+1

    SKIPGOTO

    SKIP


    Free table

    4.6


    Free table

    3.4

    1 EDIT

    EDIT

    7


    Free table

    2BROWSE

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

    BROWSE

    [LOCK<>]

    [FREEZE <>]


    Free table

    [FOR<>]<>

    8

    use STU1

    browse lock 1 freeze


    Free table

    3

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

    <><>

    1use stu1


    Free table

    4(General)

    VFPWindows(OLE)MODIFY GENERALOLE(General)

    MODIFY GENERAL<>

    :(General), ,.


    Free table

    1

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

    .,.

    *, .


    Free table

    9


    Free table

    2

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


    Free table

    3

    PACK


    Free table

    4ZAP

    ZAP [IN<>|<>]

    ZAPDELETE ALLPACK


    Free table


    Free table

    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


    Free table

    3

    1:


    Free table

    1

    . IDXCOMPACT]

    . CDX ()


    Free table

    2

    .IDX

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

    <>TO

    2


    Free table

    .CDX

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

    [OF<>]


    Free table

    STU2


    Free table

    1

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

    3 <>

    INDEX ON + TO A2

    INDEX ON STR)+STR TO A3

    4[ADDITIVE]


    Free table

    (Table Designer)

    ()(Primary CandidateUnigue(Expression)

    1(Table Designer).CDX

    2


    Free table

    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


    Free table

    1<>

    2 [ORDER [TAG]<>]


    Free table

    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<>


    Free table

    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<>]


    Free table

    1 FIND

    FIND <>|<>

    :<>()

    2 SEEK

    SEEK <>

    SEEKFIND,CND


    Free table

    3LOCATECONTINUE

    LOCATE

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

    [<>]

    CONTINUE

    CONTINUE


    Free table

    use stu-1

    locate all for >=80

    disp

    continue

    disp

    continue

    disp

    ...


    Free table

    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


    Free table

    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


    Free table

    3AVERAGE

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

    4.34Ffile().

    USE Ffile

    AVERAGE ALL TO N1,N2 FOR =""

    ? =N1 =N2

    = 22611.00 = 42


    Free table

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

    <>

    1.TOTAL<>

    2.


    Free table

    ()

    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


    Free table

    TOTAL ON TO NSUM

    USE NSUM

    LIST

    Record#

    12010143 1050.001193.00

    23001175 1600.001775.00


    Free table

    3.7

    1

    1

    JW.DBFP77:

    2

    STUDENT,LESSONLINK

    ()STUDENTLESSONLINKSTUDENTLESSON


    Free table

    2

    VFP255()32767

    1 10110

    AJ1132767 w11w32767)

    2

    3

    SELECT </ ><>


    Free table

    select a

    use stu1

    select b

    use stu2

    select 1

    list

    use

    VFP11<>

    4<>ALIAS USE <> ALIAS <>

    ALIAS


    Free table

    sele 3

    use stu1 alias s1

    sele stu2

    List

    sele s1

    List

    close all

    5

    < / / >.<>

    < / / >><>

    < / / > <>


    Free table

    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

    ()


    Free table

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

    ()


    Free table

    (1)

    (2)

    (3)

    (4)EOF()=.T.

    (5)VEPABAC

    (6)[ADDITIVE]

    (7)(SET RELATION TO )


    Free table

    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


    Free table

    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


    Free table

    3

    3-1


    Free table

    VFP

    1

    (Open)

    2


    Free table

    .VUE


    Free table

    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


    Free table

    • 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]

    <>()()


    Free table

    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


    Free table

    3.8

    CLOSE ALL

    1

    1

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

    (1)(2)<>


    Free table

    Use stu1

    Copy to stu3

    Use stu3

    list

    2COPY FILE <> TO <>

    (1

    (2)copy


    Free table

    2

    COPY STRUCTURE TO <> [FIELDS<>]

    <>

    Use stu1

    Copy structure to stu4 fislas ,,

    Use stu4

    list


    Free table

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

    :


    Free table

    3.9

    SQL


    Free table

    3.9.1 SQL

    3. 9.2

    3. 9.3

    3. 9.4


    Free table

    3. 9.1 SQL

    SQLStructured Query LanguageSQL2070IBMSEQUEL2080SQLANSI

    OracleSybaseSQL ServerVisual FoxProSQLSQL SQL

    SQLSQL


    Free table

    SQL

    1SQLSQL

    Visual FoxPro


    Free table

    31 SQL

    SQL

    CREATEALTERDROP

    SELECT

    INSERTUPDATEDELETE

    GRANTREVOKE


    Free table

    2SQLSQLSQL


    Free table

    3SQLSQL


    Free table

    31 SQL

    SQL

    CREATEALTERDROP

    SELECT

    INSERTUPDATEDELETE

    GRANTREVOKE


    Free table

    4 SQLSQLVisual FoxPro


    Free table

    VFPSQL VFP

    VFP.PRG.SCX


    Free table

    SQLSQL

    SQL SQLSQL

    Visual FoxPro SQL


    Free table

    3. 9.2

    SQL

    SELECT-SQL

    SELECT-SQLSQL


    Free table

    SQLSELECT

    Visual FoxProSQLSELECTSELECT


    Free table

    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]


    Free table

    • SELECT

    • FROM

    • WHERE

    • ORDER BY

    • GROUP BY

    • HAVINGGROUP BY


    Free table

    SELECTWHEREFROMSELECTGROUP<>GROUPHAVINGHAVINGORDER<>

    SELECTSELECTFROMWHERE


    Free table

    3. 9.2.1

    SELECT [ALL|DISTINCT]

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

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

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

    *

    ALL

    DISTINCT


    Free table

    OPEN DATABASE D:

    SELECT * FROM

    *

    SELECT DISTINCT

    AS FROM


    Free table

    32

    SELECT SELECT


    Free table

    SELECT AVG() AS

    FROM


    Free table

    3. 9.2.2

    WHERE

    WHERE <>


    Free table

    33


    Free table

    NOT

    SELECT , AVG()

    AS FROM

    WHERE =

    SELECT ,, FROM

    WHERE IN (,)

    SELECT ,, FROM

    WHERE = OR =


    Free table

    560650

    SELECT ,, FROM WHERE BETWEEN 560 AND 650

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

    SELECT , FROM

    WHERE LIKE %


    Free table

    • SELECTSQLSELECT

    • SELECTSELECT


    Free table

    3. 9.2.3

    SELECTSELECTSELECTWHERESELECT WHERE

    SELECT

    Visual FoxPro


    Free table

    1.

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

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

    SELECT FROM WHERE =;

    (SELECT FROM WHERE =)

    SQL0101


    Free table

    2.

    WHEREANYSOMEALLIN


    Free table

    1ANY

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

    0102

    SELECT , FROM

    WHERE =01 AND >ANY

    (SELECT FROM

    WHERE =02)

    029052010252


    Free table

    2ALL

    0102

    SELECT ,FROM

    WHERE =01

    AND >ALL

    (SELECT FROM

    WHERE =02)

    029052010290


    Free table

    3IN

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

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

    SELECT FROM WHERE IN;

    (SELECT FROM

    WHERE =

    OR =)

    IN=ANY


    Free table

    3. 9.2.4

    SELECT

    1.

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

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


    Free table

    2.

    02200109

    SELECT a., a. FROM a, b;

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


    Free table

    3. 9.2.5

    1. ORDER

    SELECT

    ORDER BY <1> [ASC | DESC]

    [,<2>[ASC | DESC]]

    SELECT1

    ASC() DESC


    Free table

    550650

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


    Free table

    2. INTO

    [INTO <>]

    | [TO FILE<>[ADDITIVE]

    | TO PRINTER]


    Free table

    <> 3

    ARRAY <>

    CURSOR <>

    DBF <>|TABLE <>(dbf)


    Free table

    TO FILE <>[ADDITIVE]

    (txt) ADDITIVE

    TO PRINTER


    Free table

    test1.txt

    SELECT ,, FROM

    WHERE BETWEEN 550 AND 650

    ORDER BY DESC TO FILE test1


    Free table

    3. UNION

    [UNION [ALL] <SELECT>]

    ALLALL

    1

    2SELECT

    3

    4<SELECT>ORDER BY


    Free table

    0102

    SELECT FROM

    WHERE =01

    UNION SELECT FROM

    WHERE =02


    Free table

    4.

    GROUP BY <1>[,<2>]

    <>SQL

    HAVING <>

    HAVINGGROUP BY


    Free table

    SQLWHEREGROUP BYHAVINGWHEREGROUP BYHAVING

    WHEREHAVINGWHEREHAVING


    Free table

    SELECT ,COUNT() FROM GROUP BY

    SELECT AVG() FROM ;

    GROUP BY HAVING COUNT(*)>=2


    Free table

    C120C140C150C16080

    SELECT ,AVG() AS ;

    FROM ;

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

    GROUP BY ;

    HAVING AVG()>=80

    : WHEREC120C140C150C160GROUP BYAVG80


    Free table

    3. 9.3

    3. 9.3.1

    CREATE TABLE <>[FREE]

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

    [NULL|NOT NULL]

    [CHECK <1>[ERROR<>]]

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

    [<2> ]) )


    Free table

    FREEFREE

    12

    NULLNOT NULLNULL

    CHECK


    Free table

    ERROR

    DEFAULT

    PRIMARY KEY

    UNIQUE


    Free table

    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 )


    Free table

    SQLCREATE TABLE


    Free table

    3. 9.3.2

    DROP TABLE <>

    DROP TABLE

    DROP TABLE


    Free table

    3. 9.3.3

    SQLALTER TABLE

    1

    ALTER TABLE <>

    [ADD /ALTER[COLUMN]

    <> <>[<> []

    [NULL/NOT NULL]

    [CHECK <> [ERROR <>]]

    [DEFAULT <>]

    [PRIMARY KEY/UNIQUE]

    ADD

    ALTER


    Free table

    >=500500

    ALTER TABLE ;

    ALTER COLUMN N(3)

    CHECK>=500

    ERROR 500


    Free table

    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 <>]


    Free table

    DROP

    SET

    ADD

    RENAME

    ALTER TABLE

    RENAME COLUMN TO


    Free table

    3. 9.4

    SQL

    3. 9.4.1

    Visual FoxProSQL

    1

    INSERT INTO <>

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

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


    Free table

    VALUES


    Free table

    INSERT INTO

    VALUES(231002,,

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

    ,610,)

    INSERT INTO (,)

    VALUES( 231002 ,)


    Free table

    2

    INSERT INTO <>

    FROM ARRAY <> |FROM MEMVAR]


    Free table

    A5

    A(1)=231013

    A(2)=

    A(3)=

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

    A(5)=.T.

    INSERT INTO FROM ARRAY A

    INSERT INTOFoxProINSERT


    Free table

    3. 9.4.2

    Visual FoxProDELETE

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


    Free table

    DELETE FROM WHERE =PACKRECALL


    Free table

    3. 9.4.3

    UPDATESELECT

    UPDATE [<>!]<>

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

    WHERE


    Free table

    UPDATE SET =

    WHERE =

    20

    UPDATE SET =+20

    WHERE IN

    SELECT FROM

    WHERE =


    Thank you

    Thank you