Creating a Table

A table is the primary structure in a database that stores data in rows and columns. Creating a well-structured table with the right design is the key to database design.

CREATE TABLE Syntax

The basic CREATE TABLE syntax is as follows:

CREATE TABLE table_name (
    column1 tipe_data,
    column2 tipe_data,
    column3 tipe_data
);

Each column must have a name and a data type defined. We can also add constraints such as PRIMARY KEY, NOT NULL, and others.

Example: Creating the EMPLOYEE Table

Let's create a EMPLOYEE table to store employee data for a company:

CREATE TABLE EMPLOYEE (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    position VARCHAR(50),
    salary DECIMAL(12,2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE
);

Explanation of each column:

• id — INT type, acts as the PRIMARY KEY to uniquely identify each employee
• name — VARCHAR(100), required (NOT NULL)
• email — VARCHAR(100), must be unique for each employee
• position — VARCHAR(50), stores the job title
• salary — DECIMAL(12,2), stores the salary with precision up to 2 decimal places
• hire_date — DATE, stores the date the employee started working
• is_active — BOOLEAN, indicates whether the employee is still active, defaults to TRUE

Listing Tables

Use SHOW TABLES to see all tables in the currently active database:

SHOW TABLES;

This command displays the list of all tables in the currently selected database.

Viewing Table Structure

Use the DESC (or DESCRIBE) command to see the detailed structure of a table:

DESC EMPLOYEE;

The output displays information such as column names, data types, whether NULL is allowed, keys, default values, and other additional features.

Dropping a Table

Use DROP TABLE to delete a table along with all of its data:

DROP TABLE EMPLOYEE;

Use IF EXISTS to prevent an error if the table does not exist yet:

DROP TABLE IF EXISTS EMPLOYEE;

Creating a Table from Another Table

We can create a new table that is a copy of the structure or a combination of data from an existing table using CREATE TABLE ... AS SELECT:

-- Create a new table with the structure and data from the EMPLOYEE table
CREATE TABLE EMPLOYEE_BACKUP AS
SELECT * FROM EMPLOYEE;

The EMPLOYEE_BACKUP table will have the same structure and data as the EMPLOYEE table. However, note that constraints such as PRIMARY KEY and AUTO_INCREMENT will not be copied to the new table.

To copy only the structure without data:

CREATE TABLE EMPLOYEE_EMPTY AS
SELECT * FROM EMPLOYEE WHERE 1 = 0;

The WHERE 1 = 0 clause ensures that no data rows are copied, only the table structure.

Summary

Creating tables is a fundamental step in database design. By understanding the CREATE TABLE syntax and the various available options, we can design a data structure that fits our application's needs. In the next chapter, we will learn about constraints — the rules that can be added to a table to maintain data integrity.