Using MySQL Navigator


Preliminaries
To find out how to start MySQL and to install MySQL Navigator, visit http://sql.kldp.org/mysql/, click on Document in the left frame, and follow the instructions.
 

To create a database
  1. Start MySQL as root user
  2. Start MySQL Navigator
  3. In the server window, double click on the server: root@localhost
  4. In the schema window, right click on Database
  5. Click on Create Database
  6. Enter name for database: new   When naming databases, use only alphanumeric characters and substitute underscores for spaces. Database names are case-sensitive. Do not use MySQL reserved words. For a list of reserved words, see http://www.mysql.com/doc/en/Reserved_words.html
  7. Click on OK

    8. The screen after step 6:




 

To delete a database
  1. In the server window, double click on the server: root@localhost
  2. In schema window, open Database by clicking on the plus in the check box
  3. Right click on database: new
  4. Click on Delete database
  5. Click on Yes

    6. The screen after step 4:




 

To create a MySQL table

 

Below is a row of field names with data for four records in the remaining rows:
 
idmakepurchase_datehd_gbram_mbcpucomments
1EMachine1999-10-274.3064Celeron 400 High#14
2PC Magic2000-07-0710.0064Celeron 533 High#2
3EMachine2000-04-094.3032Celeron 500 High#11
4PC Magic2000-11-1530.00128Celeron 667High#12

 

 
  1. Double click on server: root@localhost
  2. Click on MySQL on the menu bar
  3. Click on Table
  4. On the table form, choose a database: test
  5. Enter a name for the table: inventory  When naming tables, use only alphanumeric characters and substitute underscores for spaces. In Linux MySQL, table names are case-sensitive. Do not use MySQL reserved words. For a list of reserved words, see http://www.mysql.com/doc/en/Reserved_words.html

 

Enter the following data:
 
  1. Field

    2.    Name: id  Unlike database and table names, field names are case insensitive

       Type: SMALLINT

       Length: 2

       Null: NOT NULL

       Extra: AUTO_INCREMENT

       Click on Save

     

    The screen before clicking on Save:




     

     
  2. Field

    3.    Name: make

       Type: VARCHAR

       Length: 20

       Null: NULL

       Click on Save

     

     
  3. Field

    4.    Name: purchase_date

       Type: DATE

       Null: NULL

       Click on Save

     

     
  4. Field

    5.    Name: hd_gb

       Type: DECIMAL

       Length: 2,2

       Null: NOT NULL

       Click on Save

     

     
  5. Field

    6.    Name: ram_mb

       Type: MEDIUM

       Length: 3

       Null: NOT NULL

       Click on Save

     
     
  6. Field

    7.    Name: cpu

       Type: VARCHAR

       Length: 20

       Null: NOT NULL

       Click on Save

     

     
  7. Field

    8.    Name: comments

       Type: VARCHAR

       Length: 30

       Null: NULL

       Click on Save

     

     
  8. Click on Key tab

    9.    Name: id

       Type: PRIMARY KEY

       Click on Save

     
  9. Click on Fire
To delete a table
  1. In server window, double click on server: root@localhost
  2. In schema window, click on database containing table to be deleted
  3. Right click on table to be deleted and click on Delete

 

To move a table from one database to another:
  1. In server window, double click on server: root@localhost
  2. In schema window, open database containing table to be moved: test
  3. In schema window, right click on table to be moved: inventory
  4. Click on Move table
  5. Type in name of database destination for table: hsccomputer, for example
  6. Click Ok

The screen after step 5:




 

To open an existing table
  1. In server window, double click on server: root@localhost
  2. In schema window, open database: test
  3. Double click on table: inventory

 

To load data into an existing table
  1. Open MySQL Navigator
  2. In the menu bar, click on MySQL and on Schema
  3. Open database: test
  4. Right click on table inventory and click on Record in table
  5. In table window, click on Tools and click on Insert
  6. Insert into Values list data corresponding to the table field names. Number of entries must match number of field names and be in the same order
  7. To view results: double click on table name inventory in the schema window

The screen after step 7:




 

To remove a record
  1. Double click on server: root@localhost
  2. Open database: test
  3. Double click on table name: inventory
  4. Right click on check box for record created in previous section
  5. Click on Delete

 

To edit existing table contents
  1. Double click on server: root@localhost
  2. Click on MySQL in the menu bar
  3. Click on Edit
  4. In the Edit window, click on File and on Open
  5. Find file and open it
  6. Make your changes
  7. Click on Save in the file menu
  8. To view results: double click on table name inventory in the schema window

The screen after step 5:




 

Querying an existing table using MySQL statements


 

To retrieve information
  1. Double click on server: root@localhost
  2. In drop down menu, click database: test
  3. Enter query in query box:  select * from inventory where hd_gb >5
  4. Press Enter or click the Fire button (just to the right of the query box)

    5. The screen after step 4:



     

    1. Enter query in query box:  select * from inventory where source = "PC Magic"
    2. Press Enter or click the Fire button

     
    1. Enter query in query box:  select * from inventory where hd_gb > 10 and ram > 128
    2. Press Enter or click the Fire button

    To update a table
    1. In server window, double click on server: root@localhost
    2. In dropdown menu, click database: test
    3. Enter query in query box: update inventory set comments = "High#10" where id = 4
    4. Press Enter or click the Fire button
    5. To view result: double click on the table name in the schema window

     

    To insert a row
    1. In server window, double click on server: root@localhost
    2. Choose database: test
    3. Enter query in query box: insert into inventory values ("5","PC Magic","2002-02-06","40","256","AMD TB 900","High#6")
    4. Press Enter or click the Fire button
    5. To view result: double click on the table name in the schema window

     

     

    To delete a row
    1. In server window, double click on server: root@localhost
    2. Choose database: test
    3. Enter query in query box: delete from inventory where id = 5
    4. Press Enter or click the Fire button
    5. To view result: double click on the table name in the schema window