How to Create a Table in MySQL

08/03/2021

Contents

In this article, you will learn how to create a table in MySQL.

Creating a table in MySQL

Creating a table is one of the fundamental operations in MySQL. A table is a structured set of data stored in rows and columns.

Syntax for creating a table in MySQL

The syntax for creating a table in MySQL is as follows:

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
  columnN datatype constraints
);
 
  • “table_name” is the name of the table you want to create.
  • “column1, column2, …, columnN” are the names of the columns in the table.
  • “datatype” specifies the data type of the column.
  • “constraints” are optional and can be used to define additional rules for the column (e.g. NOT NULL, UNIQUE, PRIMARY KEY, etc.).

Example of creating a table in MySQL

Let’s look at an example of how to create a simple table called “employees” with four columns: “id”, “first_name”, “last_name”, and “email”.

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

This command creates a table called “employees” with four columns: “id”, “first_name”, “last_name”, and “email”. The “id” column is an integer that will auto-increment for each new record. The “first_name”, “last_name”, and “email” columns are all strings with a maximum length of 50, 50, and 100 characters respectively. Additionally, we’ve set the “NOT NULL” constraint on each column, which means that these columns cannot be left blank. Finally, we’ve set the “id” column as the primary key for the table.

Altering a table in MySQL

If you need to make changes to an existing table, you can use the “ALTER TABLE” command in MySQL. Here’s an example of how to add a new column to an existing table:

ALTER TABLE employees
ADD salary DECIMAL(10,2);

This command adds a new column called “salary” to the “employees” table. The “DECIMAL(10,2)” data type specifies a decimal number with 10 digits in total and 2 decimal places.

#MySQL