After you have planned your database, you can then build it. A database has two parts

  1. Structure to hold the data
  2. Data

First, you create an empty database with no structure at all, and then you add tables to it.

When you create a database, you create a new subdirectory in your data directory with the database name that you assign. Files are then added to this subdirectory later, when you add tables to the database. The data directory is usually a subdirectory in the directory where MySQL is installed.

Creating New Database

You can create the database by using SQL statements. To create a database, you must use a MySQL account that has permission to create, alter, and drop databases and tables.

Your first step in creating a new database is to create an empty database, giving it a name. Your database name can be up to 64 characters long.

To create a new, empty database, use the following SQL statement:

CREATE DATABASE databasename

In this statement, replace databasenamewith the name that you give your database.

Deleting Database

You can delete any database, as long as you’re using a MySQL account with the DROP privilege. When you drop a database, all the tables and data in the database are dropped.

You can remove a database with the following SQL statement:

DROP DATABASE databasename

Use DROP carefully because it’s irreversible. After you drop a database, that database is gone forever. Any data that was in it is also gone.

Adding Tables

You can add tables to any database, whether it’s a new, empty database or an existing database that already has tables and data in it. When you create a table in a database, a file named tablename.frm is added to the database directory.

When you create a table, you include the table definition. You define each column by giving it a name, assigning it a data type, and specifying any other definitions required.

NOT NULL: This column must have a value; it can’t be empty.

DEFAULT value: This valueis stored in the column when the row is created if no other value is given for the column.

AUTO_INCREMENT: This definition creates a sequence number. As each row is added, the value of this column increases by one integer from the last row entered. You can override the auto number by assigning a specific value to the column.

UNSIGNED: This definition indicates that the values for this numeric field will never be negative numbers.

Primary Key

You also specify the unique identifier for each row, the primary key. A table must have a field or a combination of fields that’s different for each row. row. No two rows can have the same primary key.

You can use the CREATE statement to add tables to a database.

CREATE TABLE tablename

After that, you add a list of column names with definitions. Separate the information for each column from the information for the following column by a comma. Enclose the entire list in parentheses. Follow each column name by its data type and any other definitions required.

The last item in a CREATE TABLE statement indicates which column or combination of columns is the primary key.

CREATE TABLE Customer (
CustomerID SERIAL,
lastName VARCHAR(50),
firstName VARCHAR(40),
city VARCHAR(50),
state CHAR(2),
zip CHAR(10),
email VARCHAR(50),
phone CHAR(15),
PRIMARY KEY(customerID) );

If you attempt to create a table that already exists, you will get an error message.

Removing a Table

You can remove a table, whether it’s empty or contains data. Removing a table is irreversible. After you drop a table, that table along with any data stored in it is gone.

DROP TABLE tablename