Using the Mysql Client
Once you have successfully installed and started MySQL, you need some
sort
of way to interact with it. Whereas
mysqld
is the MySQL server that
manages
all the data, you need a client application that will talk to
mysqld
. The most common way to interface with
mysqld
besides using a programming languageis to use the
mysql client
(or
mysql monitor
, as it is also called). This application can be used to connect to
mysqld
running on the same machine, or even on another. Most of the examples throughout the rest of this book will be accomplished via
mysql
(the lowercase word
mysql
will refer to the client, as opposed to MySQL, which refers to the software as a whole).
The
mysql
client can take several arguments up front, including the
user
name, password, and hostname (computer
name
). You establish these arguments like so:
mysql -u
user name
-p -h
hostname
The
-p
option will cause
mysql
to prompt you for the user's password, just as the
mysqladmin
tool does. You can specify the password on this line if you prefer, by typing it after the
-p
prompt, but it will be visible, which is less secure.
Within the
mysql
client, practically every statement (or SQL command) needs to be
terminated
by a semicolon. This means that you can continue the same statement over several lines to facilitate typing. With this in mind, you will also see a few different prompts when using the interface, as
illustrated
in
Figure 2.30
and listed in Table 2.1.
Table 2.1. These prompts, also represented in
Figure 2.30
, are used to clue you in as to what the
mysql
client is expecting.
|
mysql Client Prompts
|
|
Prompt
|
Meaning
|
|
mysql>
|
Ready
|
|
->
|
Continuing a command
|
|
'>
|
Need to finish a single quote
|
|
">
|
Need to finish a double quote
|
|
`>
|
Need to finish a backtick
|
|
/*>
|
Need to finish a comment
|
As a quick demonstration of working with the
mysql
client, I will show you how to access
mysql
, select a database to use, and quit the application. As always, the MySQL server must be running to connect to it from the
mysql
client.
Before going through the steps, there's an alternative beginning for Windows users. The latest versions of MySQL have created a shortcut to the
mysql
client. You can access it by selecting Start > All Programs > MySQL > MySQL Server 5.0 > MySQL Command Line Client. This was created during the installation process, but the particulars may
differ
, depending upon your version of MySQL. If you have this shortcut, you can follow it and skip ahead to Step 4.
To use the mysql client:
|
1.
|
Log in to your system from a command-line interface.
|
|
2.
|
Move to the MySQL installation directory.
Steps 1 and 2 should be old hat for you by now.
|
|
3.
|
Enter the following text (
Figure 2.31
):
./bin/mysql -u root -p
The
-h
hostname
argument described previously is optional, and I tend to leave it off unless I cannot get into
mysql
otherwise
. If you set a password for the
root
user, as detailed earlier in this chapter, you can use the
root
username and password now.
|
|
|
|
|
4.
|
At the prompt, enter the user's password.
If you used
root
in Step 3 or used the Windows Start menu trick, you would enter the
root
user's password here.
If you ever want to connect to the
mysql
client using another username, just replace
root
with that username (in Step 3) and enter that user's password at the prompt. On Windows, you'll either need to use a configuration file or manually go through the
preceding
steps.
|
|
5.
|
Show all the available databases (
Figure 2.32
).
SHOW DATABASES;
The
SHOW DATABASES
command asks MySQL to list every database which you, the logged-in user, can access. The semicolon that terminates the command is a requirement in the
mysql
client.
|
|
6.
|
Select which database you want to use (
Figure 2.33
).
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
mysql_install_db
script run during the installation process creates two starter databases
mysql
and
test
.
If you know in advance which database you will want to use, you can simplify matters by starting
mysql
with
mysql -u
username
-p
databasename
|
|
7.
|
Quit out of mysql.
exit
You can also use the command
quit
to leave the client. This stepunlike most other commands you enterdoes not require a semicolon at the end.
On Windows, using the Start Menu shortcut, this should also close the window.
|
Tips
-
To see what else you can do with the
mysql
client, type
help
; at any time (at the
mysql
prompt, after logging in).
-
The
mysql
client makes use of the Unix readline tool, allowing you to use the up and down arrows to scroll through previously entered commands. This can save you oodles of time later on.
-
You can also expedite your work within the
mysql
client by pressing the Tab key to complete words (type a single
#
and press Return to see what words can be completed), using Ctrl+A to move your cursor to the beginning of a line, and using Ctrl+E to move the cursor to the end of the line.
-
If you are in a long statement and make a mistake, cancel the current operation by typing \
c
and pressing Return (it must be a lowercase "c"). If
mysql
thinks a closing single or double quotation mark is missing, you'll need to enter that first.
-
Depending upon how MySQL is installed on your system, some Windows users can run the
mysql
clientand other utilities described in this chaptersimply by double-clicking the executable file found within the
mysql/bin
folder. You can also directly run the
mysql
client from the Start > Run menu.
-
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).
|
The
mysql
client (or
mysql
monitor) is just one of many tools you can use to work with MySQL. Although this will be the application used almost exclusively in this book, you don't have to use the mysql client. There are many other programs available, all of which will provide the same functionality but in a different way.
MySQL also makes two
related
applications, both of which are free. The first is the MySQL Administrator. The second is the MySQL Query Browser. Both offer a functionality that overlaps with the
mysql
client and with
mysqladmin
, but they use a nicer graphical interface.
There are also plenty of third-party tools available. Of these, phpMyAdmin is a popular choice (available from www.
phpmyadmin
.net). It's a Web-based tool, which requires that you also have a Web server and PHP installed.
If none of these are to your liking, just search the Internet (or your favorite download repository) for MySQL applications for your particular platform.
|
|
