3.3 Creating and Using a Database

Now that you know how to enter commands, it's time to access a database.

Suppose 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:

• Create a database
• Create a table
• Load data into the table
• Retrieve data from the table in various ways
• Use multiple tables

The menagerie database will be 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. See section 4.3.1 GRANT and REVOKE Syntax.

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 you want to call yours menagerie. The administrator needs to execute a command like this:

mysql> GRANT ALL ON menagerie.* TO your_mysql_name;

where your_mysql_name is the MySQL user name assigned to you.

Subsections

• 3.3.1 Creating and Selecting a Database
• 3.3.2 Creating a Table
• 3.3.3 Loading Data into a Table
• 3.3.4 Retrieving Information from a Table
• 3.3.4.1 Selecting All Data
• 3.3.4.2 Selecting Particular Rows
• 3.3.4.3 Selecting Particular Columns
• 3.3.4.4 Sorting Rows
• 3.3.4.5 Date Calculations
• 3.3.4.6 Working with NULL Values
• 3.3.4.7 Pattern Matching
• 3.3.4.8 Counting Rows
• 3.3.4.9 Using More Than one Table

User Comments

German documentation, chapter 4.3:
the link for the download of the menagerie.tar.gz file is incorrect. It should be http:..../Downloads/.... with capital 'D' instead of 'd'..

In the menagerie files download readme (I used the tarball), it says:

% mysqlimport --local menagerie pets

and later,

% mysqlimport --local menagerie event

This is incorrect (at least for my version of mysql which is 3.23.55). 'pets' should be changed to 'pets.txt' and 'events' to 'events.txt', the actual names of the associated files.

Add your own comment.