How to Create a Table in MySQL

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.

Prerequisites

• 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  
);

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.

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:

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:

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 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.

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);

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:

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 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:

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.

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.

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.