دروس قواعد البيانات فوكس برو 6 - Fox Pro 6

  • Published on
    30-May-2018

  • View
    219

  • Download
    0

Embed Size (px)

Transcript

  • 8/14/2019 Fox Pro 6 - 6

    1/66

    6

    Microsoft Visual FoxPro 6.0

    com.arab4hdhd@c

    com.mhmd_hdhd@hotmail

    .1 .2 .3"".4

    .56.SQL7.REPORTCreating Views .8

    9. 1.10 .11

    com.a4cb.www

  • 8/14/2019 Fox Pro 6 - 6

    2/66

    FoxPro6

    " "

    :

    FOXPRO MicrosoftVisual Studio6.0 Microsoft

    Access Microsoft Visual FoxPro 6.0

    new

    new file table

    1

  • 8/14/2019 Fox Pro 6 - 6

    3/66

    save

    fields

    name id

    id

    tab type

    1.character 2.numeric 3.date 8 mm/dd/yy

  • 8/14/2019 Fox Pro 6 - 6

    4/66

    4.logical 5.memo

    numeric tab width 3

    1.decimal 2.index 3.null

    am_call o_phone h_phone OK

    yes

  • 8/14/2019 Fox Pro 6 - 6

    5/66

    * *

    View-

  • 8/14/2019 Fox Pro 6 - 6

    6/66

    Windows-

  • 8/14/2019 Fox Pro 6 - 6

    7/66

    create lesson1 modify

    structure use

    phone

    file -> new

    new database new file

    -& -

  • 8/14/2019 Fox Pro 6 - 6

    8/66

    firstname caption

    input format display

    mask Field validation

    "field "

    comment memo

    ID index

  • 8/14/2019 Fox Pro 6 - 6

    9/66

    indexes type PRIMARY

    *

    Set date dmy

    *

    _screen.caption='NAME"

    _vfp.caption='NAME'

    NAME

    *

    Container EDIT

    relationship

    !

  • 8/14/2019 Fox Pro 6 - 6

    10/66

    !

    one to one

    one to many

    parent table

    primary table

    child table

    related table

    :

    1.Inner JOIN 2.Left JOIN 3.Right JOIN

  • 8/14/2019 Fox Pro 6 - 6

    11/66

    4.Full JOIN

    112033

    =

    INNER JOIN

    1133

    LEFT JOIN

    11233

    RIGHT JOIN

    11 2

    123

  • 8/14/2019 Fox Pro 6 - 6

    12/66

    33

    FULL JOIN

    1102332

    4

    :

    1-

    2-

    3-

    4-

  • 8/14/2019 Fox Pro 6 - 6

    13/66

    .1 .23.

    4. SQL 5. 6.

    .78.

  • 8/14/2019 Fox Pro 6 - 6

    14/66

    9.

    10.

    11.

    .12

  • 8/14/2019 Fox Pro 6 - 6

    15/66

    .13

    Criteria Example

    example

    = 5000=

    Like Cairo=

    == Cairo==

  • 8/14/2019 Fox Pro 6 - 6

    16/66

    > 100>

  • 8/14/2019 Fox Pro 6 - 6

    17/66

    .t. TRUE t

    Expression Builder

  • 8/14/2019 Fox Pro 6 - 6

    18/66

    Query

    Query New File

  • 8/14/2019 Fox Pro 6 - 6

    19/66

    CREATE QUERY COMMAND

    Open

    OK

  • 8/14/2019 Fox Pro 6 - 6

    20/66

    Add Table

    Ctrl +E

  • 8/14/2019 Fox Pro 6 - 6

    21/66

    )( Query

    SQL

    SELECT Table1., Table1., Table1.;

    FROM table1

    table1

    Filter

    Criteria= Example T

    F T ..T

    SQL

    SELECT Table1., Table1., Table1.;

    FROM table1;

    WHERE Table1. = .T.

    table1 =" "

  • 8/14/2019 Fox Pro 6 - 6

    22/66

    200AND

    Order By

  • 8/14/2019 Fox Pro 6 - 6

    23/66

    SQL

    SELECT Table1., Table1., Table1.;

    FROM table1;

    WHERE Table1. = .T.;

    AND Table1. = .T.;

    AND Table1.

  • 8/14/2019 Fox Pro 6 - 6

    24/66

    CREAT

    ALTER

    DROP

    CREAT

    CREATE TABLE PHONE ;(PHONE c(7) PRIMARY KEY, ;Name C(20))

    PHONE

    PHONE C

    20 7

    *

    CREATE TABLE salesman ;(SalesIDc(6) PRIMARY KEY,;

    SaleNameC(20))

    *CREATE TABLE customer ;(SalesIDc(6), ;CustIdi PRIMARY KEY, ;CustNamec(20) UNIQUE,;SalesBranchc(3), ;FOREIGN KEY SalesId TAG SalesId REFERENCES salesman)

    3IUNIQUE

    SalesId SalesId salesman

    *

    CREATE TABLE orders ;(OrderId iPRIMARY KEY, ;

    CustId i REFERENCES customer TAG CustId, ;OrderAmty(4), ;OrderQtyi ;DEFAULT 10 ;CHECK (OrderQty > 9) ;ERROR "Order Quantity must be at least 10", ;

    DiscPercent n(6,2) NULL ;

  • 8/14/2019 Fox Pro 6 - 6

    25/66

    DEFAULT .NULL., ;CHECK (OrderAmt > 0);ERROR "Order Amount Must be > 0" )

    CustId CustId TAG

    CustId i REFERENCES customer TAG CustId

    =10

    DEFAULT 10

    9

    CHECK(OrderQty > 9)

    ERROR"Order Quantity must be at least 10"

    DiscPercent n(6,2) NULL

    n(6,2) 6

    NULL

    n d

    C n C(n)

    D

    T

    N n d N(n,d)

    F n d F(n,d)

    I

    B d B(d)

    Y

    L

    M

  • 8/14/2019 Fox Pro 6 - 6

    26/66

    G

    ALTER

    ALTERTABLE customer ADD COLUMN fax ;

    DROPADD

    ALTERTABLE customer DROP COLUMN fax;

    CHECK

    ; ALTERTABLE orders ALTERCOLUMN quantity DROPCHECK

    DROP

    DROP TABLE | | ? [RECYCLE]

    DROP TABLE

    [RECYCLE]

    DCL

    :GRANT

    GRANT ON TO ALL,SELECT,UPDATE, DELETE _ _ EX1 GRANT ALL ON PHONE TO

    PUBLIC;

    EX2 GRANT DELETE ON PHONE TOAHMED,HDHD;

    EX3 GRANT UPDATE (NAME) ONPHONE TO PUBLIC;

    REVOKE

  • 8/14/2019 Fox Pro 6 - 6

    27/66

    ONALL,SELECT,UPDATE, DELETREVOKETO

    DML

    SELECT

    UPDATE

    DELET

    INSERT

    UPDATE

    =SETUPDATE

    ;*1.1=SETUPDATE

    10%

    UPDATE ) (

    UPDATE [DatabaseName1!]TableName1SET Column_Name1 = eExpression1[, Column_Name2 = eExpression2 ...]WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

    [DatabaseName1!]TableName1

    SET Column_Name1 = eExpression1

    WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

    UPDATE customer SET maxordamt = 25

    DELET

    1. DELET FROM BOOKS;2. DELET FROM BOOKS WHERE PRICE >20;

  • 8/14/2019 Fox Pro 6 - 6

    28/66

    1(

    2( 20

    DELETE FROM [DatabaseName!]TableName[WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

    DELETE FROM customer WHERE country = "USA"

    country "USA"

    INSERT

    INSERT INTO dbf_name [(fname1 [, fname2, ...])]VALUES (eExpression1 [, eExpression2, ...])

    dbf_name ) (

    [(fname1 [, fname2 [, ...]])]

    VALUES (eExpression1 [, eExpression2 [, ...]])

    INSERT INTO employee (emp_no, fname, lname, officeno) ;

    VALUES (3022, "John", "Smith", 2101)

    INSERT INTO TableName (F1,F2) VALUES(V1,V2,);

    TableName F1 V1

    2

    ;INSERT INTO BOOKS VALUES(VB6,25,400)

  • 8/14/2019 Fox Pro 6 - 6

    29/66

    3

    INSERT INTO BOOKS (TITLE,PRICE,PAGE) VALUES(VB6,25,400);

    4

    INSERT INTO BOOKS SELECT TITLES.* FROM TITLES.*;

    BOOKS TITLES

    SELECT

    SELECT

    SELECT

    FROM

    WHERE

    GROUP BY

    HAVING

    ORDER BY

    SQL

    SELECT [DISTINCT|ALL] {*|[column_name [AS new_name]] [,.]}

    FROM table_name [alias] [,]

    [WHERE condition]

  • 8/14/2019 Fox Pro 6 - 6

    30/66

    [GROUP BY column_list] [HAVING condition]

    [ORDER BY column_list

    DISTINCT " "

    Alias

    column_list

    SQL

    SELECT fields FROM table;

    FROM table;*SELECT

    SELECT FIELED , "" FIELD2 FROM TABLE

  • 8/14/2019 Fox Pro 6 - 6

    31/66

    1 2

    WHERE

    SELECT *FROM TABLE WHERE Fn=VALUE

    =

    )< >=>== 1000

    1000

    UK

    (SELECT company FROM customer WHERE country = "UK")

    customer.postalcode BETWEEN 90000 AND 99999

    customer.postalcode NOTIN ("98052","98072","98034")

    NOT LIKE

    customer.country NOT LIKE "UK"

    % _LIKE

    %A A

    %A A

    A A_ _ _

    A _ _ _A

  • 8/14/2019 Fox Pro 6 - 6

    32/66

    SELECT country, postalcode, company ;

    FROM customer ;

    ORDER BY country, postalcode, company

    SELECT a.company, b.order_date, b.shipped_on ;FROM customer a, orders b ;WHERE a.cust_id = b.cust_id ;INTO TABLE custship.dbf

    16/2/1994

    SELECT a.company, b.order_date, b.shipped_on ;FROM customer a, orders b ;WHERE a.cust_id = b.cust_id ;AND b.order_date < {^1994-02-16}

    ; Select Sysdate from Dual Select Next_day (sysdate) from Dual ;

    Select Last_day (sysdate) from Dual ;; Select Sysdate + 10 from Dual

    Select Sysdate - 10 from Dual ; ; 22/5/1970Select Next_day ('21-5-70') from Dual

    C

    SELECT * FROM customer a WHERE a.company LIKE "C%"

    U

    SELECT * FROM customer a WHERE a.country LIKE "U_"

    % \ % \% % \ ESCAPE "\"

    SELECT * FROM customer;

    WHERE company LIKE "%\%%" ESCAPE "\"

    _

    WHERE company LIKE "%\_%" ESCAPE "\"

  • 8/14/2019 Fox Pro 6 - 6

    33/66

    %_ _

    WHERE company LIKE "%-%--%" Escape "-"

    SELECT

    SELECT MIN (Select_Item)

    SELECT MAX (Select_Item)

    SELECT SUM (Select_Item)

    SELECT AVG (Select_Item)

    SELECT COUNT (Select_Item)

    SELECT COUNT (*)

    SELECT MIN(PRICE) ,MAX(PRICE)

    FROM ORDERSWHERE ITEM="TEA"

    GROUP BY HAVING

    SELECT EMPLOYEE , SUM(SALARY)

    FROM ORDERS

    WHERE SALARY>200

  • 8/14/2019 Fox Pro 6 - 6

    34/66

    GROUP BY COUNTRY

    HAVING SUM(SALARY)>500

    500

    SELECT

    PRIMARY KEY FOREIGN KEY

    SELECT

    SELECT company FROM customer a WHERE ;

    EXISTS (SELECT * FROM orders b WHERE a.postalcode = b.postalcode)

    DISTINCT

    SELECT DISTINCT NAME FROM EMPLOYEE ;

    1.Inner Join ) ( ) (

    2.Left Join

    3.Right Join

    4.FULL JOIN

    Inner Join

    SELECT Books.title, Authors.city;

    FROM books!books INNER JOIN books!authors ;

    ON Books.book_id = Authors.author_id;

  • 8/14/2019 Fox Pro 6 - 6

    35/66

  • 8/14/2019 Fox Pro 6 - 6

    36/66

    NewREPORT Wizard

    creat report

    " Tools-

  • 8/14/2019 Fox Pro 6 - 6

    37/66

    .56.

    open

  • 8/14/2019 Fox Pro 6 - 6

    38/66

    finish

  • 8/14/2019 Fox Pro 6 - 6

    39/66

    LEDGER

  • 8/14/2019 Fox Pro 6 - 6

    40/66

    31

    "

    "

    2-3-4

  • 8/14/2019 Fox Pro 6 - 6

    41/66

    Back Preview

  • 8/14/2019 Fox Pro 6 - 6

    42/66

  • 8/14/2019 Fox Pro 6 - 6

    43/66

    Creating Views

    1.Local View

    2.Remote View FoxPro

    View

    BOOKS.DBCProgramFiles\Microsoft Visual

    Studio\Vfp98\Wizards\Template\Books\Data

    OPEN DATABASE "c:\program files\microsoft visualstudio\vfp98\wizards\template\books\data\books.dbc"

    View Wizard New File"

    Update Criteria

    1.Table 2.Reset Key

    3.Update All

  • 8/14/2019 Fox Pro 6 - 6

    44/66

    4.Send SQL Updates

    5.Field Name 6.SQL WHERE Clause Includes

    7.Update Using

    "

    Wizard Books

    "

    "

  • 8/14/2019 Fox Pro 6 - 6

    45/66

  • 8/14/2019 Fox Pro 6 - 6

    46/66

    Back

  • 8/14/2019 Fox Pro 6 - 6

    47/66

    Remote View

    Access Next MS Access Database

    BIBLIO

  • 8/14/2019 Fox Pro 6 - 6

    48/66

    The ApplicationWizard

  • 8/14/2019 Fox Pro 6 - 6

    49/66

    Browse

    Application Builder

  • 8/14/2019 Fox Pro 6 - 6

    50/66

    General Name About Image AboutCommon Dialogs :Splash Screen Quick Start form About dialog User Logins " AppUser

    "PassWord UserIP Application Type Normal ""Module Top_Level Icon

    Credits About

  • 8/14/2019 Fox Pro 6 - 6

    51/66

    Data

    Select "

    " Datasource Form Report

    Clear Generate

    FormStyle Report Style Generate

    Forms

  • 8/14/2019 Fox Pro 6 - 6

    52/66

    Single Instance

    Use Navigation Toolbar Use Navigation Menu Appear in File New dialog Appear in File Open dialog

    oAdd oEdit oRemove

    Reports

    Name Appear in Print Reports dialog Add Edit Remove

    Advanced

  • 8/14/2019 Fox Pro 6 - 6

    53/66

    Help file Default data directory Menus Standard toolbar Favorites menu Cleanup

    OK Project Manager

  • 8/14/2019 Fox Pro 6 - 6

    54/66

    o

    oRebuild Project oBuild Application appoBuild Executable exeoBuild OLE DLL dlloOptionsoRecompile All Files oDisplay Errors oRun After Build oRegenerate Automation server IDs

    dll oVersion

    o

    oMajor oMinor oRevision oAuto Increment oVersion Information oType oComments, Company Name, File

    Description, Legal Copyright, LegalTrademarks, Product Name,Language ID

  • 8/14/2019 Fox Pro 6 - 6

    55/66

    oValue

    ---

    1. Tools-

  • 8/14/2019 Fox Pro 6 - 6

    56/66

    ),20,,10,,80,,10( null

  • 8/14/2019 Fox Pro 6 - 6

    57/66

  • 8/14/2019 Fox Pro 6 - 6

    58/66

    SaveTable for later Use

    save save as

  • 8/14/2019 Fox Pro 6 - 6

    59/66

    8. Data contacts.dbf formreport

    Generate

    9.10.OK

    Build Build Application

  • 8/14/2019 Fox Pro 6 - 6

    60/66

    11.save save as .12

    13..APP .1415. OK

    16.17. ) (

    1. 2.

  • 8/14/2019 Fox Pro 6 - 6

    61/66

    .3 .4 .56. C SetupBackup .7

    Tools-

  • 8/14/2019 Fox Pro 6 - 6

    62/66

  • 8/14/2019 Fox Pro 6 - 6

    63/66

    Setup .01. 2.

    Disk1

    - - Index

  • 8/14/2019 Fox Pro 6 - 6

    64/66

    ActiveX

  • 8/14/2019 Fox Pro 6 - 6

    65/66

    Finish

  • 8/14/2019 Fox Pro 6 - 6

    66/66

    --

    Done Setup DISK144

Recommended

View more >