MySQL Database: MySQL Tutorial

This chapter provides a tutorial introduction to MySQL by showing how to use the mysql client program to create and use a simple database. mysql (sometimes referred to as the ``terminal monitor\\'\\' or just ``monitor\\'\\') is an interactive program that allows you to connect to a MySQL server, run queries, and view the results. mysql may also be used in batch mode: you place your queries in a file beforehand, then tell mysql to execute the contents of the file. Both ways of using mysql are covered here.

To see a list of options provided by mysql, invoke it with the --help option:

shell> mysql --help

This chapter assumes that mysql is installed on your machine and that a MySQL server is available to which you can connect. If this is not true, contact your MySQL administrator. (If you are the administrator, you need to consult other sections of this manual.)

This chapter describes the entire process of setting up and using a database. If you are interested only in accessing an existing database, you may want to skip over the sections that describe how to create the database and the tables it contains.

Because this chapter is tutorial in nature, many details are necessarily omitted. Consult the relevant sections of the manual for more information on the topics covered here.

Connecting to and Disconnecting from the Server

To connect to the server, you\\'ll usually need to provide a MySQL username when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you\\'ll also need to specify a hostname. Contact your administrator to find out what connection parameters you should use to connect (that is, what host, username, and password to use). Once you know the proper parameters, you should be able to connect like this:

shell> mysql -h host -u user -p
Enter password: ********

host and user represent the hostname where your MySQL server is running and the username of your MySQL account. Substitute appropriate values for your setup. The ******** represents your password; enter it when mysql displays the Enter password: prompt.

If that works, you should see some introductory information followed by a mysql> prompt:

shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 25338 to server version: 4.0.14-log

Type \\'help;\\' or \\'\\h\\' for help. Type \\'\\c\\' to clear the buffer.

mysql>

The prompt tells you that mysql is ready for you to enter commands.

Some MySQL installations allow users to connect as the anonymous (unnamed) user to the server running on the local host. If this is the case on your machine, you should be able to connect to that server by invoking mysql without any options:

shell> mysql

After you have connected successfully, you can disconnect any time by typing QUIT (or \\q) at the mysql> prompt:

mysql> QUIT
Bye

On Unix, you can also disconnect by pressing Control-D.

Most examples in the following sections assume that you are connected to the server. They indicate this by the mysql> prompt.

back

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Make sure that you are connected to the server, as discussed in the previous section. Doing so does not in itself select any database to work with, but that\\'s okay. At this point, it\\'s more important to find out a little about how to issue queries than to jump right in creating tables, loading data into them, and retrieving data from them. This section describes the basic principles of entering commands, using several queries you can try out to familiarize yourself with how mysql works.

Here\\'s a simple command that asks the server to tell you its version number and the current date. Type it in as shown here following the mysql> prompt and press Enter:

mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| VERSION()    | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19   |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

This query illustrates several things about mysql:

Keywords may be entered in any lettercase. The following queries are equivalent:

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

Here\\'s another query. It demonstrates that you can use mysql as a simple calculator:

mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
|    0.707107 |      25 |
+-------------+---------+

The queries shown thus far have been relatively short, single-line statements. You can even enter multiple statements on a single line. Just end each one with a semicolon:

mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| VERSION()    |
+--------------+
| 3.22.20a-log |
+--------------+

+---------------------+
| NOW()               |
+---------------------+
| 1999-03-19 00:15:33 |
+---------------------+

A command need not be given all on a single line, so lengthy commands that require several lines are not a problem. mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. (In other words, mysql accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.)

Here\\'s a simple multiple-line statement:

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+--------------------+--------------+
| USER()             | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18   |
+--------------------+--------------+

In this example, notice how the prompt changes from mysql> to -> after you enter the first line of a multiple-line query. This is how mysql indicates that it hasn\\'t seen a complete statement and is waiting for the rest. The prompt is your friend, because it provides valuable feedback. If you use that feedback, you can always be aware of what mysql is waiting for.

If you decide you don\\'t want to execute a command that you are in the process of entering, cancel it by typing \\c:

mysql> SELECT
    -> USER()
    -> \\c
mysql>

Here, too, notice the prompt. It switches back to mysql> after you type \\c, providing feedback to indicate that mysql is ready for a new command.

The following table shows each of the prompts you may see and summarizes what they mean about the state that mysql is in:

Multiple-line statements commonly occur by accident when you intend to issue a command on a single line, but forget the terminating semicolon. In this case, mysql waits for more input:

mysql> SELECT USER()
    ->

If this happens to you (you think you\\'ve entered a statement but the only response is a -> prompt), most likely mysql is waiting for the semicolon. If you don\\'t notice what the prompt is telling you, you might sit there for a while before realizing what you need to do. Enter a semicolon to complete the statement, and mysql executes it:

mysql> SELECT USER()
    -> ;
+--------------------+
| USER()             |
+--------------------+
| joesmith@localhost |
+--------------------+

The \\'> and "> prompts occur during string collection. In MySQL, you can write strings surrounded by either \\'\\'\\' or \\'"\\' characters (for example, \\'hello\\' or "goodbye"), and mysql lets you enter strings that span multiple lines. When you see a \\'> or "> prompt, it means that you\\'ve entered a line containing a string that begins with a \\'\\'\\' or \\'"\\' quote character, but have not yet entered the matching quote that terminates the string. That\\'s fine if you really are entering a multiple-line string, but how likely is that? Not very. More often, the \\'> and "> prompts indicate that you\\'ve inadvertently left out a quote character. For example:

mysql> SELECT * FROM my_table WHERE name = \\'Smith AND age < 30;
    \\'>

If you enter this SELECT statement, then press Enter and wait for the result, nothing happens. Instead of wondering why this query takes so long, notice the clue provided by the \\'> prompt. It tells you that mysql expects to see the rest of an unterminated string. (Do you see the error in the statement? The string \\'Smith is missing the second quote.)

At this point, what do you do? The simplest thing is to cancel the command. However, you cannot just type \\c in this case, because mysql interprets it as part of the string that it is collecting! Instead, enter the closing quote character (so mysql knows you\\'ve finished the string), then type \\c:

mysql> SELECT * FROM my_table WHERE name = \\'Smith AND age < 30;
    \\'> \\'\\c
mysql>

The prompt changes back to mysql>, indicating that mysql is ready for a new command.

The `> prompt is similar to the \\'> and "> prompts, but indicates that you have begun but not completed a backtick-quoted identifier.

It\\'s important to know what the \\'>, ">, and `> prompts signify, because if you mistakenly enter an unterminated string, any further lines you type appear to be ignored by mysql---including a line containing QUIT. This can be quite confusing, especially if you don\\'t know that you need to supply the terminating quote before you can cancel the current command.

back

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Creating and Using a Database

Once you know how to enter commands, it\\'s time to access a database.

Suppose that you have several pets in your home (your menagerie) and you\\'d like to keep track of various types of information about them. You can do so by creating tables to hold your data and loading them with the desired information. Then you can answer different sorts of questions about your animals by retrieving data from the tables. This section shows you how to:

The menagerie database is simple (deliberately), but it is not difficult to think of real-world situations in which a similar type of database might be used. For example, a database like this could be used by a farmer to keep track of livestock, or by a veterinarian to keep track of patient records. A menagerie distribution containing some of the queries and sample data used in the following sections can be obtained from the MySQL Web site. It\\'s available in either compressed tar format (http://www.mysql.com/Downloads/Contrib/Examples/menagerie.tar.gz) or Zip format (http://www.mysql.com/Downloads/Contrib/Examples/menagerie.zip).

Use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

The list of databases is probably different on your machine, but the mysql and test databases are likely to be among them. The mysql database is required because it describes user access privileges. The test database is often provided as a workspace for users to try things out.

Note that you may not see all databases if you don\\'t have the SHOW DATABASES privilege.

If the test database exists, try to access it:

mysql> USE test
Database changed

Note that USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if you like; it does no harm.) The USE statement is special in another way, too: it must be given on a single line.

You can use the test database (if you have access to it) for the examples that follow, but anything you create in that database can be removed by anyone else with access to it. For this reason, you should probably ask your MySQL administrator for permission to use a database of your own. Suppose that you want to call yours menagerie. The administrator needs to execute a command like this:

mysql> GRANT ALL ON menagerie.* TO \\'your_mysql_name\\'@\\'your_client_host\\';

where your_mysql_name is the MySQL username assigned to you and your_client_host is the host from which you connect to the server.

back

------------------------------------------------------------------------------------------------------------------------------------------------------------------

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE menagerie;

Under Unix, database names are case sensitive (unlike SQL keywords), so you must always refer to your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true for table names. (Under Windows, this restriction does not apply, although you must refer to databases and tables using the same lettercase throughout a given query.)

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this command:

mysql> USE menagerie
Database changed

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql. Just specify its name after any connection parameters that you might need to provide. For example:

shell> mysql -h host -u user -p menagerie
Enter password: ********

Note that menagerie is not your password on the command just shown. If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -pmypassword, not as -p mypassword). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.

back

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Creating the database is the easy part, but at this point it\\'s empty, as SHOW TABLES tells you:

mysql> SHOW TABLES;
Empty set (0.00 sec)

The harder part is deciding what the structure of your database should be: what tables you need and what columns should be in each of them.

You\\'ll want a table that contains a record for each of your pets. This can be called the pet table, and it should contain, as a bare minimum, each animal\\'s name. Because the name by itself is not very interesting, the table should contain other information. For example, if more than one person in your family keeps pets, you might want to list each animal\\'s owner. You might also want to record some basic descriptive information such as species and sex.

How about age? That might be of interest, but it\\'s not a good thing to store in a database. Age changes as time passes, which means you\\'d have to update your records often. Instead, it\\'s better to store a fixed value such as date of birth. Then, whenever you need age, you can calculate it as the difference between the current date and the birth date. MySQL provides functions for doing date arithmetic, so this is not difficult. Storing birth date rather than age has other advantages, too:

You can probably think of other types of information that would be useful in the pet table, but the ones identified so far are sufficient: name, owner, species, sex, birth, and death.

Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR is a good choice for the name, owner, and species columns because the column values vary in length. The lengths of those columns need not all be the same, and need not be 20. You can pick any length from 1 to 255, whatever seems most reasonable to you. (If you make a poor choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.)

Several types of values can be chosen to represent sex in animal records, such as \\'m\\' and \\'f\\', or perhaps \\'male\\' and \\'female\\'. It\\'s simplest to use the single characters \\'m\\' and \\'f\\'.

The use of the DATE data type for the birth and death columns is a fairly obvious choice.

Once you have created a table, SHOW TABLES should produce some output:

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

To verify that your table was created the way you expected, use a DESCRIBE statement:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

You can use DESCRIBE any time, for example, if you forget the names of the columns in your table or what types they have.

back

------------------------------------------------------------------------------------------------------------------------------------------------------------------

After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

Suppose that your pet records can be described as shown here. (Observe that MySQL expects dates in \\'YYYY-MM-DD\\' format; this may be different from what you are used to.)

Because you are beginning with an empty table, an easy way to populate it is to create a text file containing a row for each of your animals, then load the contents of the file into the table with a single statement.

You could create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \\N (backslash, capital-N). For example, the record for Whistler the bird would look like this (where the whitespace between values is a single tab character):

To load the text file pet.txt into the pet table, use this command:

mysql> LOAD DATA LOCAL INFILE \\'/path/pet.txt\\' INTO TABLE pet;

Note that if you created the file on Windows with an editor that uses \\r\\n as a line terminator, you should use:

mysql> LOAD DATA LOCAL INFILE \\'/path/pet.txt\\' INTO TABLE pet
    -> LINES TERMINATED BY \\'\\r\\n\\';

You can specify the column value separator and end of line marker explicitly in the LOAD DATA statement if you wish, but the defaults are tab and linefeed. These are sufficient for the statement to read the file pet.txt properly.

If the statement fails, it is likely that your MySQL installation does not have local file capability enabled by default.

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement. Suppose that Diane gets a new hamster named Puffball. You could add a new record using an INSERT statement like this:

mysql> INSERT INTO pet
    -> VALUES (\\'Puffball\\',\\'Diane\\',\\'hamster\\',\\'f\\',\\'1999-03-30\\',NULL);

Note that string and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL directly to represent a missing value. You do not use \\N like you do with LOAD DATA.

From this example, you should be able to see that there would be a lot more typing involved to load your records initially using several INSERT statements rather than a single LOAD DATA statement.

back

------------------------------------------------------------------------------------------------------------------------------------------------------------------

What if you forget the name of a database or table, or what the structure of a given table is (for example, what its columns are called)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports.

You have previously seen SHOW DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use the DATABASE() function:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

If you haven\\'t selected any database yet, the result is NULL (or the empty string before MySQL 4.1.1).

To find out what tables the current database contains (for example, when you\\'re not sure about the name of a table), use this command:

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

If you want to find out about the structure of a table, the DESCRIBE command is useful; it displays information about each of a table\\'s columns:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Field indicates the column name, Type is the data type for the column, NULL indicates whether the column can contain NULL values, Key indicates whether the column is indexed, and Default specifies the column\\'s default value.

If you have indexes on a table, SHOW INDEX FROM tbl_name produces information about them.

back

------------------------------------------------------------------------------------------------------------------------------------------------------------------

In the previous sections, you used mysql interactively to enter queries and view the results. You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file:

shell> mysql < batch-file

If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this:

C:\\> mysql -e "source batch-file"

If you need to specify connection parameters on the command line, the command might look like this:

shell> mysql -h host -u user -p < batch-file
Enter password: ********

When you use mysql this way, you are creating a script file, then executing the script.

If you want the script to continue even if some of the statements in it produce errors, you should use the --force command-line option.

Why use a script? Here are a few reasons:

The default output format is different (more concise) when you run mysql in batch mode than when you use it interactively. For example, the output of SELECT DISTINCT species FROM pet looks like this when mysql is run interactively:

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

In batch mode, the output looks like this instead:

species
bird
cat
dog
hamster
snake

If you want to get the interactive output format in batch mode, use mysql -t. To echo to the output the commands that are executed, use mysql -vvv.

You can also use scripts from the mysql prompt by using the source or \\. command:

mysql> source filename;
mysql> \\. filename

back

--------------------------------------------------------------------------------------------------------------------------

There are programs that let you authenticate your users from a MySQL database and also let you write your log files into a MySQL table.

You can change the Apache logging format to be easily readable by MySQL by putting the following into the Apache configuration file:

LogFormat \\
        "\\"%h\\",%{%Y%m%d%H%M%S}t,%>s,\\"%b\\",\\"%{Content-Type}o\\",  \\
        \\"%U\\",\\"%{Referer}i\\",\\"%{User-Agent}i\\""

To load a log file in that format into MySQL, you can use a statement something like this:

LOAD DATA INFILE \\'/local/access_log\\' INTO TABLE tbl_name
FIELDS TERMINATED BY \\',\\' OPTIONALLY ENCLOSED BY \\'"\\' ESCAPED BY \\'\\\\\\'

The named table should be created to have columns that correspond to those that the LogFormat line writes to the log file.