MySQL Create Table

Last Updated: 28 May, 2023

In a MySQL table, data is logically organized in a row-and-column format where each row represents a unique record and each column represents a field in the record.

To create a new table in MySQL, we can use the CREATE TABLE statement followed by the table name and the list of columns along with their data types and any constraints.

See the syntax and example below.

Syntax:

  CREATE TABLE table_name (  
    column_definition1,  
    column_definition2,  
    ........,  
    table_constraints  
  );  

Example:

  CREATE TABLE student (
    stu_id INT AUTO_INCREMENT PRIMARY KEY,
    stu_name VARCHAR(100) NOT NULL,
    class VARCHAR(30) NOT NULL,
    roll SMALLINT,
    age TINYINT,
    gender VARCHAR(1),
    date_of_admission DATE,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ); 

Create a table with IF NOT EXISTS option

To create a table with the "IF NOT EXISTS" option, creates a new table with the specified name if it doesn't already exist. If the table already exists, then this statement will have no effect.

Syntax:

  CREATE TABLE IF NOT EXISTS table_name (  
    column_definition1,  
    column_definition2,  
    ........,  
    table_constraints  
  );  

Example:

  CREATE TABLE IF NOT EXISTS student (
    stu_id INT AUTO_INCREMENT PRIMARY KEY,
    stu_name VARCHAR(100) NOT NULL,
    class VARCHAR(30) NOT NULL,
    roll SMALLINT,
    age TINYINT,
    gender VARCHAR(1),
    date_of_admission DATE,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ); 

Create a table with Primary ana Foreign Key

  CREATE TABLE course (
    course_id INT AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    course_duration VARCHAR (20),
    PRIMARY KEY (course_id),
    FOREIGN KEY (stu_id) REFERENCES student (stu_id)
  );

In this example, the "course" table has a primary key of "course_id", which uniquely identifies each course record. The course table also has a foreign key of "stu_id", which refers to the primary key in the student table that contains student records. This allows for the creation of a relationship between the two tables, where each student can have multiple courses.

• Note that we need appropriate permissions to create a TABLE. If we are using the MySQL command-line client, we need to log in as a user with the CREATE privilege.
• In Unix, database names and table names are case-sensitive (unlike SQL keywords) while writing the database names and table names need to take care lettercase.