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.
- Access to a terminal window/command line
- A system running MySQL (learn how to check MySQL version)
- A MySQL user account with root or admin privileges
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 );
[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.
username\root with your username. )
sudo mysql -u username\root -p
2. Type the password for your account.
mysql> prompt indicates that you are logged in the 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 DATABASE movies;
2. Next, verify that the database was created by showing a list of all databases. Use the
The terminal prints out a list of databases and information about the time it took to perform the query:
3. Select the database to make changes to it by using the
Step 3: Create a Table
We’ll create a table containing information about two movies:
|Joker||psychological thriller||Todd Phillips||2019|
|The Empire Strikes Back||epic space opera||Irvin Kershner||1980|
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));
2. Verify that the table is created using the
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.
3. Insert movie information in column order – title, genre, director, and release year. Use the
INSERT INTO movies VALUE ("Joker", "psychological thriller", "Todd Phillips", 2019);
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:
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.
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:
5. Verify that the script ran successfully by selecting the newly created table:
SHOW DATABASES; USE movies1; SELECT * FROM movies1;
Query MySQL Data
There are several options for querying data from a MySQL table. By using the
VIEW statements, you can manipulate and find data efficiently.
Display Column Data
Display column data using the
SELECT title FROM movies;
The output displays the selected column and the data associated with it:
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 * FROM minimum_release_year;
The output displays movies released after the year 1990.
Alter a View
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.
Note: Use phoenixNAP Knowledge Base to find other MySQL guides such as MySQL Triggers or MySQL Date Function and more.
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.