MySQL CREATE TABLE Statement

MySQL CREATE TABLE Statement

The MySQL CREATE TABLE statement is used to create a new table in an existing database. This statement is essential for defining the structure of a table and specifying its columns and data types.

Syntax

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

The CREATE TABLE statement has the following components:

  • table_name: The name of the table to be created. It must be unique within the database.
  • column1, column2, ...: The names of the columns in the table.
  • datatype: The data type of the column (e.g., INT, VARCHAR(100), DATE).
  • [constraints]: Optional constraints for the column (e.g., PRIMARY KEY, NOT NULL, AUTO_INCREMENT).

Example MySQL CREATE TABLE Statement

Let's look at an example of the MySQL CREATE TABLE statement:

Step 1: Creating the Table

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

This query creates a new table named employees with columns for id, first_name, last_name, and email. The id column is set as the primary key and auto-increments. The first_name and last_name columns cannot be null, and the email column must be unique.

MySQL CREATE TABLE

Step 2: Verifying Table Creation

To verify that the table has been created, you can describe the table structure:

DESCRIBE employees;

This query provides a detailed description of the table structure, including column names, data types, and constraints.

MySQL DESCRIBE Table

Additional Example: Using IF NOT EXISTS

To create a table only if it does not already exist, use the IF NOT EXISTS option:

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

This query creates the employees table only if it does not already exist, preventing an error if the table already exists.

MySQL CREATE TABLE IF NOT EXISTS

Conclusion

The MySQL CREATE TABLE statement is a fundamental tool for defining the structure of tables in a database. Understanding how to use the CREATE TABLE statement and verifying table creation is essential for effective database management in MySQL.