MySQL Basics

MySQL is a free database management system that uses the SQL language. Its popularity is due to its support for virtually all platforms and operating systems. MySQL can be deployed locally on your Windows, Linux, or macOS machine, on a cloud server, or as a cloud database. 

In this MySQL tutorial, we will give a general overview of MySQL installation and data structure, as well as provide basic MySQL commands for working with data.

Key Points

The latest version of MySQL is available on the official website: https://dev.mysql.com/downloads/mysql/. 

Before downloading, you will be prompted to log in with an Oracle account, but this is optional. Files are available to unauthenticated users as well. 

During installation, just follow the installer prompts. For example, you need to specify which components you need or select the maximum platform package.

Installation Options:

• Developer is a platform for developers, which includes special tools.

• Server Only installs only the server part of MySQL.

• Client Only installs only the client MySQL module.

• Full installs all components included in the installer.

• Custom allows for selective installation and is suitable for advanced users.

The basics of working with MySQL include installation, platform configuration, creating a local or remote database, and connecting to existing databases. When managing databases in MySQL, you need an understanding of how to input and update information, create backups, and restore data. Tools like MySQL Workbench provide a GUI and significantly simplify working with the system.

One of the important points of MySQL installation includes creating a user. The administrator account is created first. Later, you can add and additional delete accounts. An example of creating a user looks like this:

CREATE USER ‘hostman’@'localhost' IDENTIFIED BY 'password';

Data Structure

Working with MySQL involves working with two logical blocks: server and client. The server block includes the MySQL database itself, which will then be managed. Technically, it is a set of files with FRM, MYD, MYI extensions. Each database contains one or more tables used for structured data storage. Depending on the volume of information, one or more records are added to the database.

The database structure is the same regardless of the project where it is used.

The client part usually consists of PHP scripts designed to receive data from external sources, process it, and send it to the server or retrieve previously stored information for display and various calculations. A typical example is that website content (texts, image links, animations, and other elements) is stored in a connected database. When a page is opened, it is retrieved according to the specified program.

The core of the database structure is tables. They consist of fields with a specific ID number. Table names usually correspond to their contents, for example, the USER block contains information about registered users. In this case, the fields store login, password, name, email address, and other details. These data are distributed across individual records – these are cells filled with values according to the assigned data type.

Basic Commands

The basic MySQL commands include creating databases and tables within them, adding and retrieving data, filtering, updating, and deleting data. This set is sufficient for a website or any other cloud resource to function fully.

Below are examples of basic SQL queries for performing operations with data and examples of how to implement those functions with Node.js.

Creating a database

Let's create a database named hostmandb.

SQL:

CREATE DATABASE hostmandb; 

Node.js:

const mysql = require("mysql2");
const connection = mysql.createConnection({
host: "localhost",
user: "root",
password: "mysecurepass"
});
connection.query("CREATE DATABASE hostmandb",
function(err, results) {
if(err) console.log(err);
else console.log("Database created");
});
connection.end();

Creating a table

Let's create a users table in our hostmandb database.

SQL:

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT ); 

Node.js:

const mysql = require("mysql2");
const connection = mysql.createConnection({
host: "localhost",
user: "root",
database: "hostmandb",
password: "mysecurepass"
});
const sql = `create table if not exists users(
id int primary key auto_increment,
name varchar(255) not null,
age int not null
)`;
connection.query(sql, function(err, results) {
if(err) console.log(err);
else console.log("Table created");
});
connection.end();

Inserting data

Now let's insert data into the users table.

SQL:

INSERT INTO users(name, age) VALUES('Sam', 31);

Node.js:

const mysql = require("mysql2");
const connection = mysql.createConnection({
host: "localhost",
user: "root",
database: "hostmandb",
password: "mysecurepass"
});
const sql = `INSERT INTO users(name, age) VALUES('John', 41)`;
connection.query(sql, function(err, results) {
if(err) console.log(err);
console.log(results);
});
connection.end();

Querying data

Let's request all data from the users table.

SQL:

SELECT * FROM users;

Node.js:

const mysql = require("mysql2");
const connection = mysql.createConnection({
host: "localhost",
user: "root",
database: "hostmandb",
password: "mysecurepass"
});
const sql = `SELECT * FROM users`;
connection.query(sql, function(err, results) {
if(err) console.log(err);
console.log(results);
});
connection.end();

Filtering query results

Let's request the data and filter it by name and age.

SQL:

SELECT * FROM users WHERE name=John AND age=41;

Node.js:

const mysql = require("mysql2");
const connection = mysql.createConnection({
host: "localhost",
user: "root",
database: "hostmandb",
password: "mysecurepass"
});
const sql = `SELECT * FROM users WHERE name=? AND age=?`;
const filter = ["John", 41];
connection.query(sql, filter, function(err, results) {
if(err) console.log(err);
console.log(results);
});
connection.end();

Updating data

SQL:

UPDATE users SET age = 35 WHERE name = 'Jenny'; 

Node.js:

const mysql = require("mysql2");
const connection = mysql.createConnection({
host: "localhost",
user: "root",
database: "hostmandb",
password: "mysecurepass"
});
const sql = `UPDATE users SET age=? WHERE name=?`;
const data = [35, "Jenny"];
connection.query(sql, data, function(err, results) {
if(err) console.log(err);
console.log(results);
});
connection.end();

Deleting data

SQL:

DELETE FROM users WHERE name = John; 

Node.js:

const mysql = require("mysql2");
const connection = mysql.createConnection({
host: "localhost",
user: "root",
database: "hostmandb",
password: "mysecurepass"
});
const sql = "DELETE FROM users WHERE name=?";
const data = ["John"]; // deleting users with the name John
connection.query(sql, data, function(err, results) {
if(err) console.log(err);
console.log(results);
});
connection.end();

More queries examples

Queries are divided into simple and complex based on their application. Simple ones involve interaction with just one table. Complex ones are designed to handle data streams from two or more tables.

Simple Queries:

• SELECT is designed to read data from the database.

Output the number of records in the table:

SELECT count(*) FROM table_name;

Select all records from the table:

SELECT * FROM table_name;

Selects 3 records from the table, starting from the second one:

SELECT * FROM table_name LIMIT 2,3;

• UPDATE allows you to change data previously saved in the database.

Change the value of the site field to domain.com in the table table_name with ID=3:

UPDATE table_name set site = 'domain.com' where id = '3';

• DELETE deletes records from specified tables.

Clear the record from table_name with ID=2:

DELETE from table_name where id = '2';

Complex queries are longer since they are designed to work with multiple tables. Here's an example:

SELECT DISTINCT last_name FROM person p, address adr WHERE p.adress_no = adr.address_no AND city LIKE 'L%';

This will display all unique last names of people living in a city starting with L. The format is not strict, so you often see code identical in execution but different in structure. The query:

SELECT DISTINCT last_name FROM person, address WHERE person.adress_no = address.address_no AND city LIKE 'L%';

will display the same information as the previous one, although their syntax differs slightly. Despite the name, complex queries only appear complex. You simply need to understand the data processing logic and consistently include it in the code.

Conclusion

In this tutorial, we went over MySQL basics and provided example queries for working with a MySQL database.

For a deeper dive, we recommend checking the official MySQL documentation.