Using the mysql Client

In order to create tables, add records, and request information from a database, some sort of client application is necessary to communicate with the database server. Although there are oodles of client applications available (see the sidebar "Alternatives to the mysql Client"), I'll focus on using the mysql client (or mysql monitor, as it is also called). Although this application does not have a pretty graphical interface, it's a reliable, standard tool that's easy to use and behaves consistently on many different operating systems.

The mysql client is accessed from a command-line interface, be it the Terminal application in Linux or Mac OS X, or a DOS prompt in Windows. It can take several arguments up front, including the username, password, and hostname (computer name or URL). You establish these arguments like so:

 mysql -u username -p -h hostname 

The -p option will cause the client to prompt you for the password. You can also specify the password on this line if you preferby typing it directly after the -p promptbut it will be visible, which is insecure. The -h hostname argument is optional, and I tend to leave it off unless I cannot connect to the MySQL server otherwise.

Within the mysql client, every statement (SQL command) needs to be terminated by a semicolon. These semicolons are an indication to MySQL that the query is complete; the semicolons are not part of the SQL itself. What this does mean is that you can continue the same SQL statement over several lines within the mysql client, to make it easier to read.

As a quick demonstration of accessing and using the mysql client, I will show you how to start the mysql client, select a database to use, and quit the client. Before following these steps,

• The MySQL server must be running.
  
  
• You must have a username and password with proper access.
  
  

Both of these steps are explained in Appendix A. If you are using MySQL hosted on another computer (such as a Web host), that system's administrator should provide you with access and you may need to use another interface tool, such as phpMyAdmin (again, see the sidebar). If you are using another client application, where and how you run your SQL commands will vary, but the SQL commands themselves and most of the results will be exactly the same.

As a side note, in the following steps and throughout the rest of the chapter, I will continue to provide images using the mysql client on both Windows and Mac OS X. While the appearance differs, the steps and results will be identical. So in short, don't be concerned about why one image shows the DOS prompt and the next a Terminal.

To use the mysql client

1.	Access your system from a command-line interface. On Unix systems and Mac OS X, this is just a matter of bringing up the Terminal or a similar application. If you are using Windows and followed the instructions in Appendix A, you can choose Start > Programs > MySQL > MySQL Server X.X > MySQL Command Line Client (Figure 4.1). Then you can skip to Step 3. If you don't have a MySQL Command Line Client option available, you'll need to choose Run from the Start menu, type cmd in the window, and press Enter to bring up a DOS prompt (then follow the instructions in the next step). Figure 4.1. The MySQL Windows installer creates a link in your Start menu so that you can easily get into the mysql client.
2.	Invoke the mysql client, using the appropriate command (Figure 4.2). /path/to/mysql/bin/mysql -u username -p Figure 4.2. Access the mysql client by entering the full path to the utility, along with the proper arguments. The /path/to/mysql part of this step will be largely dictated by the operating system you are running and where MySQL was installed. This might therefore be Alternatives to the mysql Client Since the mysql client is a command-line tool, you may not be able to use it if you are working with an ISP's or Web host's server. Here are two things you can try: Telnet or SSH into the remote server and then use mysql. Install the MySQL software on your computer and use the mysql client to connect to the remote server by specifying it as the hostname (mysql -u username -p -h www.site.com). If neither of these options work, you have other choices, beginning with phpMyAdmin. A popular open source tool written in PHP, phpMyAdmin, provides a Web-based interface for MySQL. Available from www.phpmyadmin.net, this software is so common that many Web hosting companies offer it as the default way for their users to interface with a MySQL database. If you cannot access MySQL through the mysql client, you can still do practically everything with phpMyAdmin. The SQL tab in the latest version allows you to directly type in SQL commands, although many common commands have their own shortcuts in the program. The good people at MySQL have other tools available for download, including the MySQL Query Browser, which will let you interact with the server through a graphical interface. If this isn't to your liking, there are dozens of others available (from third parties) if you search the Web. /usr/local/mysql/bin/mysql - u username -p (on Mac OS X and Unix) or C:\mysql\bin\mysql -u username -p (on Windows) The basic premise is that you are running the mysql client, connecting as username, and requesting to be prompted for the password. For this and the next chapter, you can use the root user, if you have that access information, but any user that has permission to create tables and databases is fine.
3.	Enter the password at the prompt and press Return/Enter. The password you use here should be for the user you specified in the preceding step. If you used the MySQL Command Line Client link on Windows (Figure 4.1), the user is root, so you should use that password (probably established during installation and configuration, see Appendix A). If you used the proper username/password combination (i.e., someone with valid access), you should be greeted as shown in Figure 4.3. Figure 4.3. If you are successfully able to log in, you'll see a welcome message like this.
4.	Select the database you want to use (Figure 4.4). Figure 4.4. The USE command selects a database to use. USE test; The USE command tells MySQL which database you want to deal with from here on out (saving typing the database name over and over again later). The test database is one of two that MySQL installs by default. Assuming it exists on your server, all users should be able to access it.
5.	Quit out of mysql (Figure 4.5). Figure 4.5. Type either exit or quit to terminate your session and leave the mysql client. exit You can also use the command quit to leave the client. This stepunlike most other commands you enter in the mysql clientdoes not require a semicolon at the end. If you used the MySQL Command Line Client, this will also close the DOS prompt window.

Tips

• If you know in advance which database you will want to use, you can simplify matters by starting mysql with

   /path/to/mysql/bin/mysql -u username  -p To see what else you can do with the mysql utility, type
   /path/to/mysql/bin/mysql --help 
  
  

• The mysql client on most systems allows you to use the up and down arrows to scroll through previously entered commands. This can save you oodles of time when working with a database.

• If you are in a long statement and make a mistake, cancel the current operation by typing c and pressing Return or Enter. If mysql thinks a closing single or double quotation mark is missing (as indicated by the '> and "> prompts), you'll need to enter the appropriate quotation mark first.

• To be particularly safe when using mysql, start the application using the --i-am-a- dummy argument. And no, I am not making this up (the argument limits what you can and cannot do).