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

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

Create a Database in MySQL Step 1

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.
Create a Database in MySQL Step 2

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.

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

states_america

.

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:

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

    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

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

    Screenshot_20150730_103118
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

    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

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.

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.

Popular by topic