Quick Introduction to the MySQL Monitor

We will now cover the basic use of the MySQL monitor. We covered logging in to mysql in Chapter 1. Just as a reminder, you can connect to MySQL using

 mysql -u username -p 

The client program has some other switches you may find useful. If you are connecting to a MySQL database on another machine, you can use the -h switch to specify the host; for example:

 mysql -h hostname -u username -p 

A really useful option to mysql is the --i-am-a- dummy option. You can also invoke this option in a less pejorative way using -- safe-updates . For example:

 mysql --i-am-a-dummy -u root -p 

The effect of this switch is to limit the damage you can do with a single command. This is an especially useful option (available for the command-line tool only) while you are first learning to use MySQL. You might like to use it while working your way through this book. (Specifically, this switch stops you from updating or deleting rows unless you specify a key value for those rows. If you don't know what this means yet, don't worry. All will become clear in Part III, "Using MySQL.")

After you're logged in, you can see what databases exist on the system by using the SHOW command:

 show databases; 

For most of you, this will be a short list at this stage. You should see the database called mysql in the list. This is the system database that holds information about user accounts and privileges. We'll discuss it later in the book.

Notice that the command has a semicolon at the end of the line. Most commands you type in the monitor need to be terminated with a semicolon; otherwise , MySQL will not execute them. Try typing

 show databases 

and pressing Enter. MySQL just sits there and waits. You can now type a semicolon and press Enter, and the command is executed. This allows you to split complex commands over multiple lines for readability. You can also type \g (backslash g) instead of the semicolon, but most people use the semicolon.

You can select a database from this list and type this:

 use  databasename  ; 

(Substitute the name of the database you want to use.)

This tells MySQL that you want to work with a particular database. Pick one and do this. (You may not have sufficient privileges to select any of the databases. If you get a message to this effect, pick a different database and try again.)

After you have selected a database, you can see what tables are in it by typing

 show tables; 

You can get information on a particular table by typing

 describe  tablename  ; 

You can log out of the monitor by typing

 \q 

You will notice that this command does not end with a semicolon. There is a set of commands that begin with \ (backslash). None of them need a terminating semicolon. You can get a list of these commands by typing

 \h 

(The h is for help.)

You can type commands and SQL statements directly into the monitor. However, you can also put these commands and statements into a file and run them all at once, like a script. We will do this later in the book, when creating databases in Chapter 4, "Creating Databases, Tables, and Indexes," for example.

If you are logged in to the MySQL monitor, you can run a file of commands by typing

 source  filename  

If you are not logged into the monitor, you can execute a file of commands by using file redirection; for example:

 mysql -u username -p <  filename  

You now know the basics of how to use the mysql client program.