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); ...