Building of Database

After you have planned your database, you can then build it. A database has two parts: (i) Structure to hold the data (ii) Data.

images/articles/mysql/building-of-database.jpg

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 database_name

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 database_name

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

Creating Tables

A database is a collection of two-dimensional tables, each of which has a collection of closely related attributes. The attributes are stored in columns of the tables. You can add tables to any database, whether it is a new, empty database or an existing database that already has tables and data in it.

You can use SQL's CREATE statement to create a table, with its associated columns. You define each column by giving it a name, assigning it a data type, and specifying any other definitions required.

Attributes

NOT NULL: This column must have a value. It cannot be empty.

DEFAULT value: This value is 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.

UNIQUE: A column assigned the UNIQUE attribute will ensure that all values possess distinct values, except that NULL values are repeatable. 

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

CREATE TABLE table_name

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 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 table_name