How to create a database in MySQL

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

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.

1. For example: To create a database for all US states, you can use the command: CREATE DATABASE us_states;
2. 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

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

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

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 .

1. The INT command ensures that the code field contains only numbers (integers).
2. The NOT NULL command ensures that the code field is not left blank.
3. The PRIMARY KEY command specifies the code field as the key field of the table. Key fields should be fields without any duplication.
4. The AUTO_INCREMENT command will automatically assign the increased values ​​to the code, basically automatically numbering each data entry.
5. 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

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

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:

7. Query the new database

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

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

Method 2: Learn more about MySQL

1. Install MySQL on Windows-based computers

1. Instructions for installing MySQL on Windows (and remote access)

2. Delete MySQL database

3. Learn PHP and MySQL

Learning PHP and MySQL will help you create entertainment and business websites.

4. Back up MySQL database

Data backup is recommended, especially with important databases.

5. Change the database structure

If the need to use the database changes, you may have to adjust the structure to handle other information.

Having fun!

Lesley Montoya

Update 25 May 2019