ru:https://highload.today/blogs/mysql-for-php-developer/ ua:https://highload.today/uk/blogs/mysql-for-php-developer/
logo
Работа с данными      11/02/2022

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

Андрей Мелешко BLOG

PHP Developer в NIX

Современные приложения производят все больше объемов данных. Таким образом растет необходимость структурировать все эти огромные массивы информации. Как это сделать? Обратимся за помощью к реляционной базе данных — 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 сервера. Чтобы войти в консольный клиент, введите команду:

Курс Front-end Basic.
Після успішного закінчення курсу студенти отримують достатньо знань і навичок для верстки сайту будь-якої складності.
Реєстрація на курс
mysql -u root -p

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

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

mysql -u root --password=123456

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

MySQL

После подключения к MySQL серверу

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

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

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

mysql> exit;

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

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

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

mysql> SHOW DATABASES;

Результат:

MySQL

Список баз данных на сервере

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

mysql> CREATE DATABASE db_name;

Результат:

MySQL

Результат создания базы данных

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

MySQL

Список всех баз данных на сервере

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

mysql> USE db_name;

Результат:

MySQL

Результат переключения базы данных

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

mysql> DROP DATABASE db_name;

Результат:

MySQL

Результат удаления базы данных

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

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

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

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

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

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

Результат:

MySQL

Результат создания пользователя

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

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

Результат:

MySQL

Результат выдачи прав пользователю на базу данных

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

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

Результат:

MySQL

Окно просмотра привилегий пользователя

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

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

mysql -u new_user -p new_user_db

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

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

mysql> SHOW TABLES;

Результат:

MySQL

Просмотр списка таблиц

Сейчас сервер говорит нам, что внутри этой базы данных нет ни одной таблицы. Попробуем ее создать. Пусть это будет таблица 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

Создание таблицы и просмотр списка таблиц

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

mysql> SHOW COLUMNS FROM cars;

Результат:

MySQL

Просмотр списка колонок в таблице

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

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

Результат:

MySQL

Модификация таблицы

Аналогичным образом можно удалять колонки, используя ключевое слово 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);

Результат:

MySQL

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

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

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

  • Получим все записи таблицы cars:
mysql> SELECT * FROM cars;

Результат:

MySQL

Все записи таблицы cars

  • Получим id и название автомобиля, у которого id равен 3:
mysql> SELECT id, title FROM cars WHERE id = 3;

Результат:

MySQL

Id и название автомобиля, у которого id равен 3

  • Получим id, название и цену автомобилей, у которых цена больше 40000 и тип «легковой»:
mysql> SELECT id, title, price FROM cars WHERE price > 40000 AND `type` = 'passenger';

Результат:

MySQL

Id, название и ценf автомобилей, у которых цена больше 40000 и тип «легковой»

  • Получим id и название автомобилей, название которых оканчивается на «maz»:
mysql> SELECT id, title FROM cars WHERE title LIKE '%maz';

Результат:

MySQL

Id и название автомобилей, название которых оканчивается на «maz»

  • Получим общую сумму, максимальную и минимальную цены всех автомобилей в таблице cars:
mysql> SELECT SUM(price), MAX(price), MIN(price) FROM cars;

Результат:

MySQL

Общая сумма, максимальная и минимальная цены всех автомобилей в таблице

  • Получим id и название автомобилей, отсортированных по убыванию цены:
mysql> SELECT id, title, price FROM cars ORDER BY price DESC;

Результат:

MySQL

Id и название автомобилей, отсортированных по убыванию цены

  • Получим количество автомобилей по типу:
mysql> SELECT `type`, COUNT(id) FROM cars GROUP BY `type`;

Результат:

MySQL

Количество автомобилей по типу

  • Получим id и название двух автомобилей, начиная со второго:
mysql> SELECT id, title FROM cars LIMIT 2 OFFSET 1;

Результат:

MySQL

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

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

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

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

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

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

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

Результат:

MySQL

Обновление записи в таблице

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

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

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

mysql> DELETE FROM cars WHERE id = 3;

Результат:

MySQL

Данные автомобиля успешно удалены

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

Итог

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

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

If you have found a spelling error, please, notify us by selecting that text and pressing Ctrl+Enter.

Курс GameDev-тестувальник.
Курс для тих, хто хоче познайомитись з роботою тестувальника в геймдеві. Опанувати воркфлоу спеціаліста, розширити скілсет і перетворити любов до ігор на професію.
Дійзнайтеся більше

Этот материал – не редакционный, это – личное мнение его автора. Редакция может не разделять это мнение.

Топ-5 самых популярных блогеров февраля

Всего просмотровВсего просмотров
181
#1
Всего просмотровВсего просмотров
181
Senior Project Manager at Nemesis
Всего просмотровВсего просмотров
92
#2
Всего просмотровВсего просмотров
92
Software Architect at Devlify
Всего просмотровВсего просмотров
88
#3
Всего просмотровВсего просмотров
88
Всего просмотровВсего просмотров
68
#4
Всего просмотровВсего просмотров
68
Android Team Lead у Balancуй Team
Всего просмотровВсего просмотров
46
#5
Всего просмотровВсего просмотров
46
Рейтинг блогеров

Ваша жалоба отправлена модератору

Сообщить об опечатке

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