How to Create a Table in MySQL

November 3, 2020

Introduction

MySQL is a well-known, free and open-source database application. Its high performance, ease of use and data security makes it a popular database solution.

One of the most crucial processes in MySQL is creating tables to store and organize data.

In this guide, you will learn how to create a table in MySQL and insert data, as well as different ways to query the data.

How to Create a Table in MySQL

Prerequisites

Create a Table in MySQL Shell

A MySQL table stores and organizes data in columns and rows as defined during table creation.

The general syntax for creating a table in MySQL is:

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

Note: [IF NOT EXISTS] verifies if there is an identical table in the database. The query will not be executed if an identical table already exists.

Step 1: Log into the MySQL Shell

1. Open a terminal window and log into the MySQL shell. Use either an existing MySQL user account or log in as root.

(Replace username\root with your username. )

sudo mysql -u username\root -p

2. Type the password for your account.

The mysql> prompt indicates that you are logged in the MySQL shell.

Login to MySQL shell.

Note: If you received a MySQL ‘Command Not Found’ error when trying to log into the MySQL shell, don’t worry. Read our article to fix the MySQL ‘Command Not Found’ error.

Step 2: Create a Database

Let’s create a movies database.

1. Create a database using the CREATE statement:

CREATE DATABASE movies;

2. Next, verify that the database was created by showing a list of all databases. Use the SHOW statement:

SHOW DATABASES;

The terminal prints out a list of databases and information about the time it took to perform the query:

Creating a database in MySQL.

3. Select the database to make changes to it by using the USE statement:

USE movies;

Step 3: Create a Table

We’ll create a table containing information about two movies:

TitleGenreDirectorRelease year
Jokerpsychological thrillerTodd Phillips2019
The Empire Strikes Backepic space operaIrvin Kershner1980

In the process of creating a table, you need to specify the following information:

  • Column names – We are creating the title, genre, director, and release year columns for our table.
  • Varchar of the columns containing characters – Specifies the maximum number of characters stored in the column.
  • The integer of the columns containing numbers – Defines numeric variables holding whole numbers.
  • Not null rule – Indicates that each new record must contain information for the column.
  • Primary key – Sets a column that defines a record.

1. Create a table using the CREATE command. Using the information from our movies example, the command is:

CREATE TABLE movies(title VARCHAR(50) NOT NULL,genre VARCHAR(30) NOT NULL,director VARCHAR(60) NOT NULL,release_year INT NOT NULL,PRIMARY KEY(title));
Creating a table.

2. Verify that the table is created using the DESCRIBE command:

DESCRIBE movies;

The terminal prints out information about the table:

  • Field – Indicates column name.
  • Type – Specifies data type for the column (varchar for characters, int for numbers).
  • Null – Indicates whether the column can remain with null values.
  • Key – Displays the primary column.
  • Default – Displays the column’s default value.
  • Extra – Indicates additional information about the columns.
Verifying the table creation.

3. Insert movie information in column order – title, genre, director, and release year. Use the INSERT command:

INSERT INTO movies VALUE ("Joker", "psychological thriller", "Todd Phillips", 2019);
Loading data into table.

4. Repeat the previous step with the second movie. Use the SELECT command to display the table:

SELECT * FROM movies;

The terminal prints out the movie table:

Displaying created table.

Note: If you want to delete the table, follow our guide on how to drop a table in MySQL.

Create a Table Using a File Script

There is an option to create a MySQL table by using a script.

1. Use your preferred text editor to create a file and enter the following syntax:

CREATE DATABASE movies1;
USE movies1;
CREATE TABLE movies1(title VARCHAR(50) NOT NULL,genre VARCHAR(30) NOT NULL,director VARCHAR(60) NOT NULL,release_year INT NOT NULL,PRIMARY KEY(title));
INSERT INTO movies1 VALUE ("Joker", "psychological thriller", "Todd Phillips", 2019);

2. After entering the syntax, save the file and exit the text editor.

Creating a file script.

3. Copy the file to MySQL using the following command:

sudo mysql -u root -p < movies1.sql/code>

The script runs automatically after copying. In our case, it creates a table with data from the movies1.sql file.

4. Log in to the MySQL shell:

Copying a script to MySQL.

5. Verify that the script ran successfully by selecting the newly created table:

SHOW DATABASES;
USE movies1;
SELECT * FROM movies1;
Navigating to the database.

Query MySQL Data

There are several options for querying data from a MySQL table. By using the SELECT and VIEW statements, you can manipulate and find data efficiently.

Display Column Data

Display column data using the SELECT command:

SELECT title FROM movies;

The output displays the selected column and the data associated with it:

Displaying column data.

Create a View

Views are SQL queries that display data based on defined parameters.

1. Create a view named minimum_release_year to display movie titles whose release year is after 1990. Use the CREATE VIEW command and define query parameters:

CREATE VIEW minimum_release_year AS SELECT title FROM movies WHERE release_year > 1990;

2. Display the view using the SELECT command:

SELECT * FROM minimum_release_year;

The output displays movies released after the year 1990.

Creating a view.

Alter a View

The ALTER VIEW MySQL statement modifies the query parameters of the previously created view. For example, we’ll modify the minimum_release_year view to display the titles of the movies whose release year is before 2018.

1. To do so, run the ALTER VIEW command with the new parameters:

ALTER VIEW minimum_release_year AS SELECT title FROM movies WHERE release_year < 2018;

2. Use the SELECT command to verify that the view has been altered:

SELECT * FROM minimum_release_year;

The output now displays movies released before 2018.

Using the ALTER VIEW command to modify previously created view.

Note: Use phoenixNAP Knowledge Base to find other MySQL guides such as MySQL Triggers or MySQL Date Function and more.

Conclusion

After reading this guide, you should know how to create a table in MySQL and crucial commands to display the data. Learn about the most important MySQL commands and how to use them in our MySQL commands cheat sheet article with a downloadable PDF.

Working with databases and tables is crucial for data organization. If you find the MySQL shell too intimidating, consider installing Workbench for a user-friendly GUI in which you can manage and create MySQL databases. If you are interested to learn more about proper organization of data tables, make sure to check what is Database Normalization. And to check the size of a table in MySQL database, read our article how to check MySQL database and table size.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is a technical writer at phoenixNAP who is passionate about programming. Her background in Electrical Engineering and Computing combined with her teaching experience give her the ability to easily explain complex technical concepts through her content.
Next you should read
How to Create or Add an Index in MySQL With Examples
August 31, 2020

An index is an integral part of MySQL, helping with organizing and searching information more easily. This...
Read more
How to Rename a Column in MySQL
March 30, 2020

This article offers two slick methods to rename a column in a MySQL table. Use ALTER TABLE command combined...
Read more
How to Allow Remote Connections to MySQL
March 26, 2020

Keep your database servers separate from your public-facing web servers to improve security, performance, and...
Read more
How to Import and Export MySQL Databases in Linux
April 25, 2019

MySQL is a versatile application. It can be used for something as simple as a product database, or as complex...
Read more