Основы MySQL

Бесплатная платформа для управления базами данных MySQL использует стандартный язык SQL – Structured Query Language. Он представляет собой высокоуровневый инструмент описания данных и манипулирования ими в различных СУБД. Популярность MySQL объясняется поддержкой практически всех платформ: разных релизов Windows, Linux, macOS, облачных платформ timeweb.cloud. Сегодня она развивается под эгидой компании Oracle, поэтому будущее продукта весьма перспективно.

Основные моменты

Свежая версия MySQL доступна на официальном сайте https://dev.mysql.com/downloads/mysql/. При запросе дистрибутива будет предложено авторизоваться при помощи учетной записи Oracle, но это необязательная процедура. Файлы доступны и неавторизованным пользователям. При установке достаточно следовать подсказкам инсталлятора. Например, нужно указать, какие компоненты будут нужны или выбрать максимальный пакет платформы.

Варианты установки:

1. Developer – платформа для разработчика, включает дополнительные инструменты.
2. Server Only – активирует инсталляцию только серверной части MySQL.
3. Client Only – аналогично, но в отношении клиентского модуля.
4. Full – установка всех блоков, включенных в состав инсталлятора.
5. Custom – выборочная инсталляция, подходит только для профессионалов.

Основы работы с MySQL включают инсталляцию, настройку платформы, создание локальной или удаленной базы данных (подключение к существующим БД). В процессе управления понадобится понимать способы ввода и форматирования информации, протоколирования результатов, создания резервных копий и восстановления из архивов. Заметно упрощают работу с системой инструменты для визуального проектирования в СУБД вроде MySQL Workbench.

Один из важных моментов установки MySQL включает создание пользователя. Первым делают доступ для администратора. Впоследствии есть возможность добавлять аккаунты и удалять их по мере смены исполнителей, подключения к работе дополнительных специалистов. Выбирать пароль для пользователя MySQL со статусом Admin желательно тщательно, чтобы исключить доступ путем того же перебора стандартных комбинаций.

Структура данных

Продукт предполагает работу с двумя логическими блоками – серверным и клиентским. В первый включена непосредственно база данных MySQL, которой затем предстоит управлять. Технически она представляет собой набор файлов с расширением FRM, MYD, MYI. В каждой БД имеется одна или несколько таблиц, используемых для структурированного хранения данных (в полях и записях). В зависимости от объема информации в базу данных вносят одну или несколько записей.

Структура БД одинакова независимо от проекта, где ее используют. Меняется только состав и назначение отдельных блоков.

Клиентская часть обычно состоит из набора скриптов на PHP, предназначенных для приема данных из внешних источников, их обработки и отправки на сервер. Или для извлечения ранее сохраненных сведений для отображения, различных вычислений. Типичный пример – контент сайта хранится в БД, подключенной к нему (тексты, ссылки на изображения и анимацию и прочие элементы). При открытии страницы они извлекаются оттуда согласно заданной программе.

Основа структуры БД – таблицы. Они в свою очередь состоят из полей с определенным порядковым номером ID. Название таблиц обычно соответствует содержимому, например, блок USER содержит информацию о зарегистрированных пользователях. Тогда в полях будет храниться логин, пароль, имя, электронный адрес, другие реквизиты. Перечисленные данные распределяются по отдельным записям – это ячейки, заполненные значениями соответственно назначенному типу данных.

Основные операции с данными

К базовым командам MySQL относится создание БД, таблиц внутри них, добавление и получение данных, их фильтрация, обновление, удаление. Такого набора достаточно для полноценной работы сайта, любого другого облачного ресурса. Ниже приведем примеры реализации отдельных функций через Node.js.

Создание базы данных

Создадим базу данных usersdb2.

const mysql = require("mysql2");
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "123456"
});
connection.query("CREATE DATABASE usersdb2",
  function(err, results) {
    if(err) console.log(err);
    else console.log("База данных создана");
});
connection.end();

Создание таблиц

Создадим в базе usersdb2 таблицу users.

const mysql = require("mysql2");
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  database: "usersdb2",
  password: "123456"
});
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("Таблица создана");
});
connection.end();

Добавление данных

Добавим данные в таблицу users.

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

Получение данных

Получим все данные из выбранной таблицы users.

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

Фильтрация данных

Отфильтруем данные по имени и возрасту.

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

Обновление данных

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

Удаление данных

const mysql = require("mysql2");
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  database: "usersdb2",
  password: "123456"
});
const sql = "DELETE FROM users WHERE name=?";
const data = ["Sam"]; // удаляем пользователей с именем Sam
connection.query(sql, data, function(err, results) {
    if(err) console.log(err);
    console.log(results);
});
connection.end();

Запросы

Следующая по важности категория – запросы MySQL. Они представляют собой формат обращения к базе данных. Результатом такой операции становится получение, изменение, удаление, сортировка или добавление информации. По способу применения запросы делят на простые и сложные. Первые предполагают взаимодействие всего с одной таблицей. Вторые предназначены для обработки потока, поступающего из двух и более таблиц.

Примеры простых запросов:

1. Select – предназначены для чтения данных из БД.

SELECT count(*) FROM table_name;

Выводит количество всех записей в таблице.

SELECT * FROM table_name;

Выбираем все записи из таблицы БД.

SELECT * FROM table_name LIMIT 2,3;

Выбирает 3 записи из таблицы, начиная со второй.

2. Update – нацелены на изменение данных, ранее сохраненных в БД.

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

Меняет значение поля site на domain.com в таблице table_name с ID=3.

3. Delete – удаляют записи из указанных таблиц.

DELETE from table_name where id = '2'

Очистит запись из table_name с идентификатором, равным 2.

Сложные запросы выглядят более длинными, ведь они предназначены для работы с несколькими таблицами, в том числе для обмена данными между ними. Пример запроса:

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

Он выведет все уникальные фамилии людей, которые проживают в городе с названием L. Формат не имеет строгих правил, поэтому часто встречается код, идентичный по исполнению, но разный по структуре. Запрос:

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

отобразит ту же информацию, что и предыдущий, хотя их написание чуть отличается. Несмотря на название, сложные запросы лишь выглядят таковыми. Разработчику достаточно представлять всю логику обработки данных, относительно которых он формирует команды, и последовательно внести их в разрабатываемый код.

Заключение

Также отдельного внимания требует функция соединения таблиц. Здесь придется поработать над исключением пересекающихся результатов, заданием уникальных ID для сходных данных и т.д. На практике используют несколько режимов объединения – левое, правое и полное внешнее или внутреннее, перекрестное. Везде требуется определенный опыт, который появится со временем, если регулярно практиковаться в манипулировании базами данных.

Перечень, описание и примеры использования встроенных функций содержится в официальной документации по СУБД MySQL. Именно от нее рекомендуется отталкиваться при изучении этой системы с нуля или для расширения знаний, если ранее полученных оказалось недостаточно.