How to create a MySQL database: 12 steps (with pictures)

Table of contents:

How to create a MySQL database: 12 steps (with pictures)
How to create a MySQL database: 12 steps (with pictures)

MySQL is far from easy to implement! All commands must be entered through a command prompt, there is no visual interface. This is why it takes good programming skills to create and manipulate a database, otherwise you could waste a lot of time or even fail. The guide you are going to read is intended to be concrete: we are going to create a database of certain American states with their respective populations.


Method 1 of 2: Create and manipulate a database

Create a Database in MySQL Step 1

Step 1. Create your database

In the MySQL command line, enter the command:


. Replace with the name of your database without any spaces.

  • You want to create a database covering the 50 states of the United States. You will need to enter something like:

    CREATE DATABASE american_states;

  • Note: the commands do not need to be in upper case.
  • Note: a MySQL command always ends with ";". If you forget, put the semicolon on the next line to validate the previous command.
Create a Database in MySQL Step 2

Step 2. Display the list of your databases

Enter the command


which lists the databases available on the MySQL server. At the same time, you will see a database appear


(which manages access and privileges) and a database


(which is used by users to perform their tests). For now, you can ignore these remarks.

Create a Database in MySQL Step 3

Step 3. Select your database

Once the database has been created, you must select it in order to start editing it. Enter the command

USE American_states;

. A message is displayed:

Database changed

, letting you know that your active database is now



Create a Database in MySQL Step 4

Step 4. Create a table for yourself

Initially, it is empty. It is in the table that you will enter certain information. To create one, you need to start by structuring it with the initial command. To create a table, enter the following query:

CREATE TABLE states (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, state CHAR (25), population INT (9));

. So you will create a table called "states" with three columns:







  • The command


    only allows (integer) numbers in the field


  • The request


    allows you to check that the field


    was not left blank.
  • The request


    designates the field


    as the primary key of the table. A table can only have one primary key and cannot contain two identical values.
  • The request


    used to generate a unique identifier for new lines in the field


  • The orders


    (characters) and


    (integers) allow you to specify the type of data expected in a given field. The number after the command specifies the number of characters or integers that can be entered in the field.
Create a Database in MySQL Step 5

Step 5. Create an entry in the table

The table is now created, you must now enter your information. Here is the command to enter the first State:

INSERT INTO states (id, state, population) VALUES (NULL, ‘Alabama’, ‘4822023’);


  • This is to indicate to the database that it must distribute the different information in the three fields.
  • Field


    was specified with the command


    , so this field will be filled in automatically and there will be an incrementation (+1) compared to the previous entry thanks to the command


Create a Database in MySQL Step 6

Step 6. Create other entries

You can enter multiple entries at once using a single command. For example, to enter the following three states, do:

INSERT INTO states (id, state, population) VALUES (NULL, 'Alaska', '731449'), (NULL, 'Arizona', '6553255'), (NULL, 'Arkansas', '2949131');


  • The resulting table will look something like this.

Create a Database in MySQL Step 7

Step 7. Run a query on your database

Your database is now created (minimalist, admittedly!), You can enter queries to get specific results. To start, make the request:

SELECT * FROM american_states;

. You will get the whole database since the "*" sign actually means "all".

  • For a more specific query, enter the following command:

    SELECT state, population FROM american_states ORDER BY population;

    . This will return the table to you, no longer in alphabetical order, but by increasing population. The column


    will not appear since you have only chosen the entries




  • To list the states in decreasing order of population, enter the following command:

    SELECT state, population FROM american_states ORDER BY population DESC;

    . The command


    returns a descending order from largest to smallest, while by default, the display is in ascending order.

Method 2 of 2: To go further with MySQL

Create a Database in MySQL Step 8

Step 1. Install MySQL on your Windows PC

Find out how to install MySQL on your home PC.

Create a Database in MySQL Step 9

Step 2. Eliminate the MySQL database

Sometimes, getting rid of obsolete databases requires completely eliminating the ones you have.

Create a Database in MySQL Step 10

Step 3. Learn PHP

By learning PHP and MySQL, you will be able to create successful sites for fun or for your work.

Create a Database in MySQL Step 11

Step 4. Know how to back up

It is always advisable to create a backup of your databases, especially if the content is important.

Create a Database in MySQL Step 12

Step 5. Modify the structure of a database

If over time you have different requirements, you can modify your database structure to suit your needs.


  • Here is a list of the most common data types (for the complete list, see the MySQL documentation at this address:

    • TANK(length) - Fixed character string, number of mandatory characters. Characters not included will be replaced by spaces.
    • VARCHAR(length) - Variable length character string with a maximum length (length).
    • TEXT - Variable length character string with a maximum of 64KB.
    • INT(length) - to put a 32-bit signed integer with a maximum length of 'length in terms of digits (the' - 'is ​​counted as' digit' for negative numbers)
    • DECIMAL(length, dec) - to put a decimal number of length length (including comma). The dec field sets the maximum number of decimal places to use.
    • DATED - for dates (year, month, day)
    • TIME - for the time (hours, minutes, seconds)
    • ENUM("value1", "value2",…) - List of choices
  • Some optional parameters:

    • NOT NULL - You must enter a value, because the field cannot be left blank.
    • DEFAULT default-value - If the field is left empty, it will be filled with the value assigned by the command (here default-value).
    • UNSIGNED - In the case of a numeric field, check that the number is not negative.
    • AUTO_INCREMENT - The value will be incremented each time a row is added to the table.

Popular by topic