The standard interface for working with MySQL databases is to
connect to the MySQL server software (which you set up in Chapter
1) and type commands one at a time. To make this connection to
the server, you'll need the MySQL client program. If you
installed the MySQL server software yourself either under Windows
or under some brand of UNIX, you already have this program
installed in the same location as the server program. Under
Linux, for example, the program is called mysql and
is located by default in the /usr/local/mysql/bin
directory. Under Windows, the program is called
mysql.exe and is located by default in the
C:\mysql\bin directory.
If you didn't set up the MySQL server yourself (if, for example,
you'll be working on your Web host's MySQL server), there are two
ways to connect to the MySQL server. The first is to use Telnet
or a Secure Shell (SSH) connection to log into your Web host's
server, and then run mysql from there. The second is to download
and install the MySQL software from
http://www.mysql.com/ (available
free for Windows and Linux) on your own computer, and use it to
connect to the MySQL server over the Internet. Both ways work
fine, and your Web host may support one, the other, or both
(you'll need to ask).
Whichever method and operating system you use, you'll end up at a
command line, ready to run the MySQL client program and connect
to your MySQL server. Here's what you should type:
mysql -h hostname -u username -p
You need to replace hostname with the host
name or IP address of the computer on which the MySQL server is
running. If the client program is run on the same computer as the
server, you can actually leave off the -h
hostname part of the command instead of typing
-h localhost or –h 127.0.0.1.
username should be your MySQL user name. If
you installed the MySQL server yourself, this will just be
root. If you're using your Web host's MySQL server,
this should be the MySQL user name they assigned you.
The -p argument tells the program to prompt you for
your password, which it should do as soon as you enter the
command above. If you set up the MySQL server yourself, this
password is the root password you chose in Chapter 1. If you're
using your Web host's MySQL server, this should be the MySQL
password they gave you.
If you typed everything properly, the MySQL client program will
introduce itself and then dump you on the MySQL command line:
mysql>
Now, the MySQL server can actually keep track of more than one
database (this allows a Web host to set up a single MySQL server
for several of its subscribers to use, for example), so your next
step should be to pick a database to work with. First, let's
retrieve a list of databases on the current server. Type this
command (don't forget the semicolon!), and hit ENTER.
mysql> SHOW DATABASES;
MySQL will show you a list of the databases on the server. If
this is a brand new server (i.e. if you installed this server
yourself in Chapter 1), the list should look like this:
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.11 sec)
The MySQL server uses the first database, called
mysql, to keep track of users, their passwords, and
what they're allowed to do. We'll steer clear of this database
for the time being, and come back to it in Chapter 8 when we
discuss MySQL Administration. The second database, called
test, is a sample database. You can actually get rid
of this database. I won't be referring to it in this series of
articles (and we'll create our own example database momentarily).
Deleting something in MySQL is called "dropping" it, and the
command for doing so is appropriately named:
mysql> DROP DATABASE test;
If you type this command and press Enter, MySQL will obediently
delete the database, saying "Query OK" in confirmation. Notice
that you're not prompted with any kind of "are you sure?"
message. You have to be very careful to type your commands
correctly in MySQL because, as this example shows, you can
obliterate your entire database—along with all the
information it contains—with one single command!
Before we go any further, let's learn a couple of things about
the MySQL command line. As you may have noticed, all commands in
MySQL are terminated by a semicolon (;). If you
forget the semicolon, MySQL will think you haven't finished
typing your command, and will let you continue to type on another
line:
mysql> SHOW
-> DATABASES;
MySQL shows you that it's waiting for you to type more of your
command by changing the prompt from mysql> to
->. For long commands, this can be handy, as it
allows you to spread your commands out over several lines.
If you get halfway through a command and realize you made a
mistake early on, you may want to cancel the current command
entirely and start over from scratch. To do this, type
"\c" and press ENTER:
mysql> DROP DATABASE\c
mysql>
MySQL will completely ignore the command you had begun to type,
and will go back to the prompt to wait for another command.
Finally, if at any time you want to exit the MySQL client
program, just type "quit" or "exit" (either one will work). This
is the only command that doesn't need a semicolon, but you can
use one if you want to.
mysql> quit
Bye
» 
Comment and Contribute
