MySQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database, defining its columns, their data types, and other constraints.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

• table_name: The name of the table to be created.
• column1, column2, column3, ...: The names of the columns to be created in the table.
• datatype: Specifies the data type of each column (e.g., INT, VARCHAR, DATE, DECIMAL, etc.).

Example

Creating a simple table called Employees with three columns: EmployeeID, FirstName, and LastName.

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

This creates an Employees table with three columns:

• EmployeeID of type INT
• FirstName of type VARCHAR(50)
• LastName of type VARCHAR(50)

Create Table Using Another Table

You can create a new table based on the structure and data of an existing table using the CREATE TABLE AS syntax.

Syntax:

CREATE TABLE new_table_name AS
    SELECT column1, column2, ...
    FROM existing_table_name
    WHERE ...;

• new_table_name: The name of the new table to be created.
• existing_table_name: The table from which data and structure are copied.
• SELECT ... FROM: Specifies the columns and data to be copied from the existing table.

Example:

Create a new table TopEmployees by copying the data of employees who have a salary greater than 50000 from the existing Employees table:

CREATE TABLE TopEmployees AS
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000;

This will create a TopEmployees table with the same structure (columns EmployeeID, FirstName, LastName, and Salary) and insert all records where the salary is greater than 50,000.

Tips for Creating Tables

• Ensure you select appropriate data types for the columns based on the kind of data they will hold (e.g., INT for integers, VARCHAR for text, DATE for dates).
• Use constraints such as PRIMARY KEY, NOT NULL, and UNIQUE to define how the data should be stored and validated.