If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE menagerie;  

Under Unix, database names are case sensitive (unlike SQL keywords), so you must always refer to your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true for table names. (Under Windows, this restriction does not apply, although you must refer to databases and tables using the same lettercase throughout a given query. However, for a variety of reasons, our recommended best practice is always to use the same lettercase that was used when the database was created.)

So far you have learnt how to put information into your MySQL database, view the information in it and select which information you would like to view. In this part I will show you how to do the two final actions, updating your database and deleting records from it.

Last week I explained how to create a link for each record to point to your update script. By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it.

When you are trying to optimize your queries to run quickly and efficiently, you may encounter queries that really should run faster. That's where EXPLAIN comes in handy. This article shows you how to use EXPLAIN in query analysis. It is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

13.2 Using EXPLAIN to Analyze Queries

When a SELECT query does not run as quickly as you think it should, use the EXPLAIN statement to ask the MySQL server for information about how the query optimizer processes the query. This information is useful in several ways:

• EXPLAIN can provide information that points out the need to add an index.

• If a table already has indexes, you can use EXPLAIN to find out whether the optimizer is using them. (To see what indexes a table has, use SHOW INDEX, as described in section 13.1.2, "Obtaining Table Index Information.")

Navicat MySQL GUI for Windows (ver. 7.2.12) is now available. This version includes some minor bugs fix and improvements on Access to MySQL and Excel to MySQL features.

Navicat is a powerful yet easy to use MySQL GUI that provides extensive functionality for managing and developing MySQL.

Advanced queries should be used in preference to extracting data then formatting it using another language. Many programmers either do not have the knowledge or just do not understand the power and speed of MySQL. The MySQL server is all about speed and efficiency. Manipulate and format the data as much as possible using MySQL before passing it on to Perl or PHP. Use built in functions and nested functions to achieve your desired result. In most cases, you will be able to extract all necessary records from all tables in one call to the database.