How to create a database in MySQL

MySQL can be a scary program. All commands must go through the command line interpreter program (command prompt) without any intuitive interface. Therefore, the basic knowledge of how to create and manipulate on a database in MySQL can save you time and avoid nuisance.

Follow the instructions below to create a database of US state populations. Invite you to consult!

Method 1: Create and manipulate on a database

1. Create the database

How to create a database in MySQL Picture 1 How to create a database in MySQL Picture 1

From the MySQL command line, enter the CREATE DATABASE ; command data_name CREATE DATABASE ; . Replace data_name by your database name. This name cannot contain spaces.

For example: To create a database for all US states, you can use the command: CREATE DATABASE us_states;
Note: Do not capitalize commands. All MySQL commands must be terminated with a " ; " sign. If you forget to put the semicolon in the command, you simply add it to the next line to execute the previous command.

2. Display the list of existing databases

How to create a database in MySQL Picture 2

Enter the SHOW DATABASES; command SHOW DATABASES; to list all the databases you have. Besides the database you just created, you will see the mysql database and test database. At this point, you can "ignore" them.

3. Select your database

How to create a database in MySQL Picture 3

Once you have created the database, you need to select it to start the editing process. Enter USE us_states; command USE us_states; . The notice Database changed ( Database changed has been changed) will appear, indicating that the database has been activated.

4. Create a data table

How to create a database in MySQL Picture 4

Table is the place to store database information. To create the table, you need to enter the entire format of the table into the original command. To create the data table, enter the following command: CREATE TABLE states (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, state CHAR(25), population INT(9)); . This command will help create a table named "states" consisting of three fields: id (code), state (state) and population .

The INT command ensures that the code field contains only numbers (integers).
The NOT NULL command ensures that the code field is not left blank.
The PRIMARY KEY command specifies the code field as the key field of the table. Key fields should be fields without any duplication.
The AUTO_INCREMENT command will automatically assign the increased values to the code, basically automatically numbering each data entry.
The CHAR (character) and INT (integer) instructions specify the type of data allowed to enter these fields. The next number represents the maximum number of characters or integers that the school can receive.

5. Enter data into the table

How to create a database in MySQL Picture 5 How to create a database in MySQL Picture 5

When creating the table successfully, start entering information. Use the command below to enter the first data: INSERT INTO states (id, state, population) VALUES (NULL, 'Alabama', '4822023'); .

Basically, this command requires the database to enter the information given into the three corresponding fields on the data table. Since the code section containing the NOT NULL statement, thanks to the AUTO_INCREMENT command, entering NULL as the parameter value will force it to automatically get a value of 1.

6. Enter more data

How to create a database in MySQL Picture 6 How to create a database in MySQL Picture 6

You can import multiple streams of data at the same time with one command. To enter the next three states, use the command: INSERT INTO states (id, state, population) VALUES (NULL, 'Alaska', '731449'), (NULL, 'Arizona', '6553255'), (NULL, 'Arkansas', '2949131'); .

This command will create a data table as shown below:

How to create a database in MySQL Picture 7 How to create a database in MySQL Picture 7

7. Query the new database

How to create a database in MySQL Picture 8 How to create a database in MySQL Picture 8

When the basic database is set up, you can enter query commands to retrieve specific results. First, enter the command: SELECT * FROM states; . This command will return the entire database, as shown by the " * " command - meaning "whole".

For advanced queries, enter the command: SELECT state, population FROM states ORDER BY population; . This command will return the data table with the states sorted by population field value instead of alphabetical order. The code field will not be displayed because you only retrieve data state and population .
To list the state's population in reverse order, enter the command: (SELECT state, population FROM states ORDER BY population DESC); . The DESC command will list them by descending value, from high to low instead of low.