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.

Steps

Method 1 of 2: Create and manipulate a database

Step 1. Create your database

In the MySQL command line, enter the command:

CREATE DATABASE;

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

Step 2. Display the list of your databases

Enter the command

SHOW DATABASES;

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

mysql

(which manages access and privileges) and a database

test

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

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

states_america

.

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:

id

,

state

and

population

.

• The command
  INT
  only allows (integer) numbers in the field
  id

• The request
  NOT NULL
  allows you to check that the field
  id
  was not left blank.
• The request
  PRIMARY KEY
  designates the field
  id
  as the primary key of the table. A table can only have one primary key and cannot contain two identical values.
• The request
  AUTO_INCREMENT
  used to generate a unique identifier for new lines in the field
  id
  .
• The orders
  TANK
  (characters) and
  INT
  (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.

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
  id
  was specified with the command
  NOT NULL
  , so this field will be filled in automatically and there will be an incrementation (+1) compared to the previous entry thanks to the command
  AUTO_INCREMENT
  .

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.

  

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
  id
  will not appear since you have only chosen the entries
  state
  and
  population
  .
• 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
  DESC
  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

Step 1. Install MySQL on your Windows PC

Find out how to install MySQL on your home PC.

Step 2. Eliminate the MySQL database

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

Step 3. Learn PHP

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

Step 4. Know how to back up

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

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.

Advice

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