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.
usernameroot with your username. )
sudo mysql -u usernameroot -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.
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.