1.3 Creating a Database and a Sample Table
1.3.1 Problem
You want to create a database and to set up tables within it.
1.3.2 Solution
Use a CREATE DATABASE statement
to create a database, a
CREATE TABLE statement
for each table you want to use, and
INSERT to add records to the tables.
1.3.3 Discussion
The GRANT statement used in the previous section
defines privileges for the cookbook database, but
does not create it. You need to create the database explicitly before
you can use it. This section shows how to do that, and also how to
create a table and load it with some sample data that can be used for
examples in the following sections.
After the cbuser account has been set up, verify
that you can use it to connect to the MySQL server. Once
you've connected successfully, create the database.
From the host that was named in the GRANT
statement, run the following commands to do this (the host named
after -h should be the host where the MySQL server
is running):
% mysql -h localhost -p -u cbuser
Enter password: cbpass
mysql> CREATE DATABASE cookbook;
Query OK, 1 row affected (0.08 sec)
Now you have a database, so you can create tables in it. Issue the
following statements to select cookbook as the
default database, create a simple table, and populate it with a few
records:
mysql> USE cookbook;
mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('octopus',0,8);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);
The table is named limbs and contains three
columns to records the number of legs and arms possessed by various
life forms and objects. (The physiology of the alien in the last row
is such that the proper values for the arms and
legs column cannot be determined;
NULL indicates "unknown
value.")
Verify that the table contains what you expect by issuing a
SELECT statement:
mysql> SELECT * FROM limbs;
+--------------+------+------+
| thing | legs | arms |
+--------------+------+------+
| human | 2 | 2 |
| insect | 6 | 0 |
| squid | 0 | 10 |
| octopus | 0 | 8 |
| fish | 0 | 0 |
| centipede | 100 | 0 |
| table | 4 | 0 |
| armchair | 4 | 2 |
| phonograph | 0 | 1 |
| tripod | 3 | 0 |
| Peg Leg Pete | 1 | 2 |
| space alien | NULL | NULL |
+--------------+------+------+
12 rows in set (0.00 sec)
At this point, you're all set up with a database and
a table that can be used to run some example queries.
|
