MySQL —

MySQL Tools

Now you know that MySQL is a client-server DBMS and to use MySQL you'll need a client, an application that you'd use to interact with MySQL.

There are many client application options (like Per or PHP etc.), but when learning MySQL you are best off using a utility designed for just that purpose. And there are three tools:

mysql Command-Line Utility

Every MySQL installation comes with a simple command-line utility called mysql. mysql is simple SQL shell and does not have any drop-down menus, fancy user interfaces, mouse support, or anything like that.

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.4-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

MySQL Options and Parameters. If you just type mysql by itself, you might receive an error message. This will likely be because security credentials are needed or because MySQL is not running locally or on the default port. mysql accepts an array of command-line parameters you can use. For example, to specify a user login name of bell, you'd use mysql u bell. To specify a username, host name, port, and be prompted for a password, you'd use mysql u bell p h myserver P 31333.

A complete list of command-line options and parameters can be obtained using mysql help.

Of course, your version and connection information might differ, but you'll be able to use this utility regardless. You'll note that

  • Commands are typed after the mysql> prompt.

  • Just pressing Enter will not execute the command, commands should be end with ; or \g;.

  • Type help or \h to obtain help. You can also provide additional text to obtain help on specific commands (for example, help select to obtain help on using the SELECT statement).

  • Type quit or exit to quit the command-line utility.

The mysql command-line utility is one of the most used and is invaluable for quick testing and executing scripts.

MySQL Administrator

MySQL Administrator is a graphical interactive client designed to simplify the administration of MySQL servers. You can download this utility from http://dev.mysql.com/downloads/.

MySQL Administrator prompts for server and login information and then displays icons that allow for the selection of different views. Amongst these are

  • Server Information: displays status and version information about the connected server and client.

  • Service Control: allows you to stop and start MySQL as well as specify server features.

  • User Administration: is used to define MySQL users, logins, and privileges.

  • Catalogs: lists available databases and allows for the creation of databases and tables.

Quick Access to Other Tools The MySQL Administrator Tools menu contains options to launch the mysql command-line utility and the MySQL Query Browser.

In fact, the MySQL Query Browser also contains menu options to launch the mysql command-line utility and the MySQL Administrator.

MySQL Query Browser

The MySQL Query Browser is a graphical interactive client for creating, executing, and optimizing MySQL commands. You can download this utility from http://dev.mysql.com/downloads/.

MySQL Query Browser prompts for server and login information and then displays the application interface. See the following:

  • MySQL commands are typed into the window at the top of the screen. When the statement has been entered, click the Execute button to submit it to MySQL for processing.

  • Results are displayed in a grid in the large area to the left of the screen.

  • Multiple statements and results can be rendered in their own tabs to allow for rapid switching between them.

  • On the right of the screen is a tab that lists all available datasources (called schemata here), expand any datasource to see its tables, and expand any table to see its columns.

  • You can also select tables and columns to have MySQL Query Browser write MySQL statements for you.

  • Help on MySQL syntax, functions, and more is available at the bottom right of the screen.

In this first tutorial, you learned what SQL is and why it is useful. Because SQL is used to interact with databases, you also reviewed some basic database terminology. You also learned what exactly MySQL is and introduced to several client utilities.