3.3. Creating and Using a Database
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:
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
(https://www.mysql.com/Downloads/Contrib/Examples/menagerie.tar.gz)
and Zip
(https://www.mysql.com/Downloads/Contrib/Examples/menagerie.zip)
formats.
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 do not have the
SHOW DATABASES privilege. See
Section 13.5.1.3, “GRANT 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.
