Учимся работать с базами данных: подробный гайд по MySQL для PHP-разработчика

Современные приложения производят все больше объемов данных. Таким образом растет необходимость структурировать все эти огромные массивы информации. Как это сделать? Обратимся за помощью к реляционной базе данных — MySQL. Благодаря доступности, быстрой обработке запросов и надежности хранения данных эта БД уже практически стала стандартом написания веб-приложений.

Эта статья будет полезна начинающим PHP-разработчикам. Вы узнаете о преимуществах MySQL, ознакомитесь с теоретическими и практическими основами работы с базами данных.

---

Начнем с основ

Что такое электронная база данных? Это совокупность материалов, которые систематизированы таким образом, чтобы данные можно было легко найти и обработать с помощью электронно-вычислительной машины. База данных может являть собой как обычный текстовый файл, так электронную таблицу в виде Excel. Но в IT-проектах обычно требуется больше возможностей, чем предоставляют такие простые варианты. Поэтому речь пойдет о реляционных базах данных (далее — РБД).

Среди преимуществ реляционной модели можно выделить:

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

Именно последняя особенность и объясняет название модели «реляционная», от англ. relation — «связь».

Итак, если есть базы данных, значит должен быть и способ общения с ними. Тогда на помощь приходит SQL — стандартный высокоуровневый язык описания и манипулирования данными. Но одного SQL недостаточно. Существуют еще системы управления базами данных (СУБД). Благодаря им и происходит общение с базой данных посредством SQL.

Механизм работает по принципу «Клиент-Сервер». Что это значит? SQL-сервер реализует хранение и управление данными. Он принимает запросы на языке SQL, выполняет их и возвращает результаты (чаще всего в виде вновь построенных таблиц) клиентам.

Основные понятия в MySQL

Единственная структура представления данных РБД — таблица. Таблицы, в зависимости от своих свойств и возможностей, делятся по типам. На практике вы скорее всего столкнетесь с двумя из них — это MyISAM и InnоDB. Каждая из них имеет свои особенности, которые проявляются во время чтения, записи, изменения и удаления данных из таблиц.

Таблицы состоят из колонок и строк, а пересечение колонки и строки называется полем. Тип данных поля определяется колонкой. Давайте вспомним о типах колонок в MySQL:

• числовые;
• строковые;
• календарные (дата и время);
• пространственные;
• JSON (появился в последних версиях);
• NULL — специальный тип данных, который обозначает отсутствие информации.

Со всеми типами данных и их характеристиками можете ознакомиться на официальном сайте в разделе документации.

Индекс — это еще один важный термин. Это структура, которая обеспечивает прямой метод доступа к данным и позволяет исключить последовательный перебор всех строк таблицы.

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

Также существует такое понятие как внешний ключ (Foreign key). Он предоставляет возможность ссылаться на строку в другой таблице. Благодаря чему мы и можем строить связи между таблицами.

Теорию вспомнили, пора практиковаться!

Первый шаг — установить сервер MySQL. Для этого нужно открыть терминал и выполнить команду:

sudo wget http://repo.mysql.com/mysql-apt-config_w.x.y-z_all.deb 
sudo dpkg -i mysql-apt-config_w.x.y-z_all.deb 
sudo apt-get update 
sudo apt-get install mysql-server 

w.x.y-z — необходимая версия репозитория MySQL.

Таким образом нам удастся подтянуть актуальный репозиторий с MySQL в нашу систему и установить его. Конфигурационный скрипт запросит пароль для администратора (root) базы данных. Введите и запомните его.

Для того, чтобы проверить, прошла ли установка успешно, введите команду:

sudo service mysql status

Аналогичным образом можно останавливать и снова запускать службу:

sudo service mysql stop 
sudo service mysql start

Вы увидите информацию о версии установленного MySQL сервера. Чтобы войти в консольный клиент, введите команду:

mysql -u root -p

-u говорит, что далее последует имя пользователя. В этом случае это пользователь root, который доступен по умолчанию и имеет наибольшие привилегии.

-p указывает на то, что пароль пользователя будет вводиться отдельно в командной строке. Хотя можно написать и так:

mysql -u root --password=123456

Но помните, что такой вариант менее безопасный:

На изображении выше — приветствие от сервера и краткое руководство по использованию консольного клиента, а также приглашение на ввод команд. Стоит отметить, что все команды должны заканчиваться ;.

Обычно принято писать ключевые слова в верхнем регистре, так как это повышает читабельность кода.

Давайте введем команду, которая позволит выйти из консольного клиента:

mysql> exit;

Теперь снова войдите. Нажмите на клавиатуре кнопку «Вверх» и увидите последнюю введенную вами команду.

Операции с базами данных

Теперь посмотрим, какие базы данных есть на нашем сервере. Для этого введите команду:

mysql> SHOW DATABASES;

Результат:

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

mysql> CREATE DATABASE db_name;

Результат:

Команда успешно выполнена. Давайте убедимся в этом снова и просмотрим список баз данных:

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

mysql> USE db_name;

Результат:

Этим мы и говорим серверу, к какой базе данных будут относиться введенные нами команды. Еще одно действие, которое мы можем сделать с базой данных — удалить ее. Для этого есть команда:

mysql> DROP DATABASE db_name;

Результат:

На изображении видно, что запрос выполнен успешно — база данных удалена. Будьте внимательны, когда вошли под пользователем root и используете эту команду, чтобы случайно не удалить лишнее.

Создание пользователя и привилегии

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

Нам снова понадобится база данных. Поэтому давайте создадим новую. Назовем ее new_user_db.

После того, как вы убедились, что база данных успешно создана, создаем нового пользователя (с именем new_user и паролем 123456). Для этого выполним команду:

mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY '123456';

Результат:

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

mysql> GRANT ALL PRIVILEGES ON new_user_db.* TO 'new_user'@'localhost';

Результат:

Запрос выполнен успешно. Теперь можно посмотреть все права пользователя с помощью команды:

mysql> SHOW GRANTS FOR 'new_user'@'localhost';

Результат:

Как мы видим, у нашего пользователя появились права на базу данных new_user_db.

Теперь выйдем и снова зайдем, но уже под созданным пользователем. Для того, чтобы сразу попасть в нужную базу данных, укажите ее в команде:

mysql -u new_user -p new_user_db

Операции с таблицами

Мы находимся внутри базы данных new_user_db. Посмотрим список таблиц, которые есть в ней. Для этого выполним команду:

mysql> SHOW TABLES;

Результат:

Сейчас сервер говорит нам, что внутри этой базы данных нет ни одной таблицы. Попробуем ее создать. Пусть это будет таблица cars. Здесь мы укажем такие поля: id, title, description, type, price, created_at, updated_at.

Теперь давайте напишем команду для создания такой таблицы:

mysql> CREATE TABLE cars
( id INT NOT NULL AUTO_INCREMENT, 
title VARCHAR(30) NOT NULL, 
description TEXT NOT NULL,
type ENUM('passenger', 'truck') NOT NULL,
price INT DEFAULT 0, 
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (id) )
;

Результат:

Запрос успешно выполнен. Теперь посмотрим, какие колонки есть в этой таблице, выполнив команду:

mysql> SHOW COLUMNS FROM cars;

Результат:

Довольно часто в проектах возникает потребность менять структуру таблицы. Например, нам сообщили новое требование о том, что у автомобиля должно быть такое свойство как цвет, и оно будет принимать значения white, black, red, green и blue. Давайте попробуем добавить поле с помощью команды:

mysql> ALTER TABLE cars 
ADD COLUMN color
ENUM('white','black','red','green','blue')
NOT NULL AFTER type;

Результат:

Аналогичным образом можно удалять колонки, используя ключевое слово DROP вместо ADD.

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

Примечание: сейчас этого делать не стоит. Таблица нам еще пригодится:

mysql> DROP TABLE cars;

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

Пришло время поговорить о CRUD — Create, Read, Update, Delete. Это основные операции, которые производятся с данными. Вспомним каждую из них по очереди.

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

Первая операция — создание данных. За нее в MySQL отвечает оператор INSERT. Попробуем записать в нашу таблицу cars несколько строк. Для этого нужно выполнить следующие команды:

mysql> INSERT INTO cars (title, description, type, color, price) 
VALUES ('BMW X5', 'BMW description', 'passenger', 'black', 50000); 

mysql> INSERT INTO cars (title, description, type, color, price)
VALUES ('VW passat', 'VW description', 'passenger', 'white', 30000); 

mysql> INSERT INTO cars (title, description, type, color, price) 
VALUES ('Mercedes AMG', 'Mercedes description', 'passenger', 'black', 70000); 

mysql> INSERT INTO cars (title, description, type, color, price) 
VALUES ('Kamaz', 'Kamaz description', 'truck', 'red', 45000);

Результат:

Чтение данных

Теперь, когда в таблице cars есть записи, переходим к чтению данных. В MySQL за этот процесс отвечает оператор SELECT, который позволяет делать выборку по различным критериям, строить запросы с использованием арифметических, строковых и логических операций и функций. Сделаем же несколько запросов.

• Получим все записи таблицы cars:

mysql> SELECT * FROM cars;

Результат:

• Получим id и название автомобиля, у которого id равен 3:

mysql> SELECT id, title FROM cars WHERE id = 3;

Результат:

• Получим id, название и цену автомобилей, у которых цена больше 40000 и тип «легковой»:

mysql> SELECT id, title, price FROM cars WHERE price > 40000 AND `type` = 'passenger';

Результат:

• Получим id и название автомобилей, название которых оканчивается на «maz»:

mysql> SELECT id, title FROM cars WHERE title LIKE '%maz';

Результат:

• Получим общую сумму, максимальную и минимальную цены всех автомобилей в таблице cars:

mysql> SELECT SUM(price), MAX(price), MIN(price) FROM cars;

Результат:

• Получим id и название автомобилей, отсортированных по убыванию цены:

mysql> SELECT id, title, price FROM cars ORDER BY price DESC;

Результат:

• Получим количество автомобилей по типу:

mysql> SELECT `type`, COUNT(id) FROM cars GROUP BY `type`;

Результат:

• Получим id и название двух автомобилей, начиная со второго:

mysql> SELECT id, title FROM cars LIMIT 2 OFFSET 1;

Результат:

Это простые варианты запросов. На практике часто приходится иметь дело с более сложными конструкциями.

Манипулирование строками таблицы

Теперь рассмотрим способы манипулирования строками таблицы. Для удаления и изменения строк применяются операторы DELETE и UPDATE.

Обновление строк в таблице

Например, для того, чтобы изменить цену на автомобиль BMW X5, необходимо выполнить команду:

mysql> UPDATE cars SET price = 100000 WHERE id = 1;

Результат:

Строка успешно изменена.

Удаление строк в таблице

И, наконец, оператор DELETE позволяет удалить строку или строки из таблицы, которые соответствуют определенным условиям. Давайте удалим Mercedes AMG:

mysql> DELETE FROM cars WHERE id = 3;

Результат:

На изображении вы можете заметить, что строка с id = 3 отсутствует. На самом деле условия для удаления или обновления строк могут быть намного сложнее, что и обеспечивает гибкость и огромные функциональные возможности MySQL.

Итог

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

Читайте также: SQL-запросы по-быстрому: краткий и понятный гайд