MySQL Cookbook, 2nd Edition

Creating a Database and a Sample Table

Problem

You want to create a database and set up tables within it.

Solution

Use a CREATE DATABASE statement to create the database, a CREATE TABLE statement for each table that you want to use, and INSERT statements to add rows to the tables.

Discussion

The GRANT statement shown in Setting Up a MySQL User Account sets up privileges for accessing the cookbook database but does not create the database. You need to create it 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.

Connect to the MySQL server as shown at the end of Setting Up a MySQL User Account. After you’ve connected successfully, create the database:

mysql>CREATE DATABASE cookbook;

Now you have a database, so you can create tables in it. First, select cookbook as the default database:

mysql>USE cookbook;

Then issue the following statements to create a simple table and populate it with a few rows:^[1]

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 record 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 rows were inserted properly into the table 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 |
+--------------+------+------+

At this point, you’re all set up with a database and a table. For general instructions on issuing SQL statements, see Issuing SQL Statements .

Note

Statements in this book are shown with SQL keywords such as SELECT or INSERT in uppercase for distinctiveness. However, that’s just a typographical convention. You can enter keywords in any lettercase.

---

^[1] If you don’t want to enter the complete text of the INSERT statements (and I don’t blame you), skip ahead to Repeating and Editing SQL Statements for a shortcut. If you don’t want to type in any of the statements, skip ahead to Telling mysql to Read Statements from a File.