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
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
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
INTonly allows (integer) numbers in the field
- The request
NOT NULLallows you to check that the field
idwas not left blank.
- The request
PRIMARY KEYdesignates the field
idas the primary key of the table. A table can only have one primary key and cannot contain two identical values.
- The request
AUTO_INCREMENTused to generate a unique identifier for new lines in the field
- The orders
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.
idwas 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
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
idwill 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
DESCreturns 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.
- 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.