Introduction to MySQL

SQL is a programming language created for managing and manipulating data within relational databases. A relational database structures information into one or more tables, where each table consists of data arranged in rows and columns.

After creating an ERD diagram with DBDesigner, we can export it as an SQL script. This way, we have the structure ready without writing any code and can start inserting data immediately.

To write in MySQL, we need a server. We can host a local one on our computer using the XAMPP Control Panel. You can download it from here. After installation, start the MySQL server, enter the shell, and start writing. XAMPP uses an RDBMS called MariaDB (it can be accessed through the shell).

Creating a database


chcp 65001 -- enabling UTF-8 formatting in the console
mysql -u root -p -- entering the RDBMS
CREATE DATABASE database_name; -- creating the database
USE [database_name]; -- entering the database

Importing and exporting a database


mysql -u root -p < [path] -- importing the database
mysql -u root -p [database_name] -- entering the RDBMS and the database

EXIT; -- exiting the RDBMS
mysqldump -u root -p --routines --triggers --events --add-drop-database --create-options --databases [database_name] > [path_to_the_output_file] -- exporting the database to a .sql file

We should add these lines at the top of the exported file to make importing easier:


CREATE DATABASE IF NOT EXISTS database_name;
USE database_name;

These lines ensure that the database is created (if it doesn’t already exist) and that it is selected for the operations that follow. However, if it is impossible to add them to the file for some reason, use this sequence of commands while importing the database instead of the one given earlier:


mysql -u root -p -- entering the RDBMS
CREATE DATABASE database_name; -- creating the database
EXIT; -- exiting the RDBMS
mysql -u root -p database_name < [path] -- importing the data into the created database
mysql -u root -p [database_name] -- entering the RDBMS and the database

Important commands
`SHOW databases;`	Displaying all databases available on a server.
`SHOW tables;`	Displaying all tables.
`DESCRIBE table_name;`	Displaying the structure of a given table.
`LOAD DATA INFILE [path] INTO TABLE table_name FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES (id, value1, value2);`	Loading the data from a file into the database. The data has to be separated by TABs (`\t`), and the end-of-line sign must be an enter (`\n`). The first line will be ignored (the column titles).