MySQL CREATE TABLE – How to create a table in MySQL?

By /
MySQL CREATE Table

In this tutorial, we will learn about the MySQL CREATE TABLE Statement. A table is a structure that is used to organize and store data in rows and columns. Each table in a database must have a unique name to identify it with. One of the harder parts of large scale projects is determining what columns it should contain.

Let us take an example. Suppose you need to create a table that stores the records of students in a class. Take a minute and think of what columns you can have.

  • Well, to start with, we will need to have a Names column that will contain the student’s name. But what if two or more students happen to share the same first name and last name?
  • Hence, we should also have a unique ID number for each student.
  • Next, we could also have a column for the gender of the student, date of birth, address, Parent/Guardian details.

The possibilities of having columns here are endless and depending on what information you need, you can add a column for that.

MySQL provides you with the CREATE TABLE statement. The CREATE command is one of the Data Definition Language (DDL) commands in MySQL.

Syntax of MySQL CREATE TABLE

CREATE TABLE table_name (
column_1 data_type [constraints], 
column_2 data_type [constraints], 
column_3 data_type [constraints]......, 
[table_constraints]
);Code language: SQL (Structured Query Language) (sql)

You may mention constraints on individual columns or the table as a whole.

Let us understand the MySQL CREATE TABLE syntax better. 

  • Table_name – It should be unique to that database. Two tables in the same database may not share the same value.
  • Column_1, column_2, … column_N – This should specify the names of columns you want in your table.
  • Data_type – Columns can have different data types namely – INT, TEXT, VARCHAR, DATE, DATETIME, TIMESTAMP, FLOAT, YEAR, LONGINT, and so on.
  • Column constraints – Column constraints are optional. You should specify them only if you need to. Examples of some constraints are:
    • DEFAULT <value> – Used to specify a default value of a column. If not specified, the default value is NULL.
    • AUTO_INCREMENT – Sets columns to auto-increment their value. In
    • NOT NULL – Constraint that specifies that any value in that particular column should not be NULL.
  • Table constraints – Table constraints may be used to specify constraints like primary keys, foreign keys, and so on.

How To Use The MySQL CREATE TABLE?

Let us create a Student table. Suppose I need this table for storing the general details of the students along with their marks in English, Maths, and Science.

1. Decide the Columns You Need

Let us brainstorm what columns can this table has and what constraints can be associated with these columns or the table as a whole.

For the Student table, we will need the following columns with the given constraints and data types –

  • ID – The ID column will store the unique ID of the student. The type of this field can be INT and should have constraints of NOT NULL.
  • Name – The Name column will store the name of the student and can be of type VARCHAR or TEXT. It should have a NOT NULL constraint.
  • DOB – The DOB column will store the Date of Birth of the student and will be of type DATE. This will have a NOT NULL constraint.
  • English – Marks gained in English. The type should be INT. 
  • Maths – Store the marks gained in Maths. The type should be INT. 
  • Science – Store the marks gained in Science. The type should be INT. 

2. Query to Create A MySQL Table

So how will we create such a table? We will use the following query,

CREATE TABLE Students(
ID INT NOT NULL AUTO_INCREMENT, 
Name TEXT NOT NULL, 
DOB DATE NOT NULL, 
English INT, 
Maths INT, 
Science INT, 
PRIMARY KEY(ID)
);Code language: SQL (Structured Query Language) (sql)

Now, after you execute this query, you would want to check if your table was created or not. To do so, we use the following query,

SHOW tables;Code language: SQL (Structured Query Language) (sql)

We will get the output as,

Example Create Table

As you can see, our table name is seen after executing the SHOW tables command.

3. Verify All Fields Match The Requirements

Now, in the Describe tables article, I explained how DESC works. To see if we have got our table structure right, we will use this query –

DESC Students;Code language: SQL (Structured Query Language) (sql)

And the output helps us confirm if our table structure is what we wanted.

MySQL Describe Students Table
MySQL Describe Students Table

Conclusion

CREATE TABLE statement is one of the most basic statements in MySQL. You create tables using this command, which is a primary step in practical database management systems. Knowing how to create suitable tables with the right columns and constraints is the first step towards mastering MySQL. I would highly recommend you to go through the links below for further reading.

References