Skip navigation links

Additional languages
MySQL 5.1 Reference Manual :: 3 Tutorial :: 3.3 Creating and Using a Database
3.3.1 Creating and Selecting a Database »
Section Navigation      [Toggle]

3.3. Creating and Using a Database

[+/-]

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

Once you know how to enter commands, you are ready to access a database.

Suppose that you have several pets in your home (your menagerie) and you would 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 perform the following operations:

  • 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 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 is available in both compressed tar file and Zip formats at http://dev.mysql.com/doc/.

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

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

The mysql database describes user access privileges. The test database often is available as a workspace for users to try things out.

The list of databases displayed by the statement may be different on your machine; SHOW DATABASES does not show databases that you have no privileges for if you do not have the SHOW DATABASES privilege. See Section 12.5.5.15, “SHOW DATABASES 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 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 user name assigned to you and your_client_host is the host from which you connect to the server.


User Comments

Posted by Steven Ginzbarg on May 15 2006 3:56pm[Delete] [Edit]

I found the README.txt in the menagerie database download difficult to follow. Here is my revised version.

In what situations would one want to use one's command interpreter rather than the MySQL program? It seems tedious to have to execute mysql and supply connection parameters for each command.

README.txt

This directory contains files that can be used to set up the menagerie database that is used in the tutorial chapter of the MySQL Reference Manual.

First, you should create the database. In the mysql program, issue this statement:

mysql> CREATE DATABASE menagerie;

The examples below assume that you have unzipped menagerie.zip or menagerie.tar.gz to the C: drive creating a temporary folder C:\menagerie containing the downloaded files.

To create the pet table:

mysql> use menagerie
Database changed
mysql> source c:/menagerie/cr_pet_tbl.sql

(Note the use of forward slashes for specifying paths at the mysql> prompt.)

To load the pet table, use this command in mysql:

mysql> LOAD DATA LOCAL INFILE 'c:/menagerie/pet.txt' INTO TABLE pet;

To add Puffball's record, use this command:

mysql> source c:/menagerie/ins_puff_rec.sql

To create the event table:

mysql> source c:/menagerie/cr_event_tbl.sql

To load the event table, use this command:

mysql> LOAD DATA LOCAL INFILE 'c:/menagerie/event.txt' INTO TABLE event;

The commands above were entered in the MySQL program. If you have created an account for yourself and granted yourself privileges (see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html) you can also set up the menagerie database by using your command interpreter. This will require executing mysql.exe for each command. If you set the PATH environmental variable (search your operating system's help for 'environmental variable') to include the path of mysql.exe you will be able to execute mysql commands from any directory.

To set up the menagerie database using your command interpreter:

(Note, the ‘shell>’ is used to indicate any command prompt, e.g. c:\> or c:\temp>.)

Start the mysql program.

shell>mysql <connection parameters>
mysql>

(Note that for the mysql and mysqlimport commands, you must supply any connection parameters necessary (host, user, password) on the command line after ‘mysql’. For example, your command might be

shell>mysql –-user=username –password=somepass
mysql>

Create the database. In the mysql program, issue
this statement:

mysql> CREATE DATABASE menagerie;

Quit the mysql program.
mysql>quit
Bye

shell>

To create the pet table:

shell>mysql <connection parameters> menagerie < c:/menagerie/cr_pet_tbl.sql

To load the pet table, use the following command:

shell> mysqlimport <connection parameters> menagerie c:/menagerie/pet.txt

(shell> mysql <connection parameters> menagerie < c:/menagerie/load_pet_tbl.sql gives an error because the sql file contains a reference to pet.txt w/o a path.)

To add Puffball's record, use this command:

shell> mysql <connection parameters> menagerie < c:/menagerie/ins_puff_rec.sql

To create the event table:

shell> mysql <connection parameters> menagerie < c:/menagerie/cr_event_tbl.sql

To load the event table, use this command:

shell> mysqlimport <connection parameters> menagerie c:/menagerie/event.txt

Add your own comment.