MySQL

MySQLMySQL – система управления базами данных (СУБД), которая наиболее часто применяется в сайтостроительстве. Она позволяет гибко управлять базами данных с помощью как веб-ориентированных, так и прикладных языков программирования (PHP, Perl, Python, Ruby, C, C++, Delphi, Java и др), посредством специального языка структурированных запросов (SQL – сокр. англ. "Structured Query Language").

MySQL сама по себе не является базой данных, а только позволяет управлять оной. При этом поддерживается несколько их типов: MyISAM, ISAM, MERGE, HEAP, InnoDB (по умолчанию), Berkeley_DB (BDB) и Maria. Различаются они между собой структурой, дополнительными функциями и масштабируемостью.

В статье ниже речь пойдёт об основах применения MySQL при создании сайта, основных SQL-запросах и программах для работы с MySQL.

Основы работы с базами данных

MySQL Основы работы с базами данных
Язык:Русский
Формат:
Обновлено:2016-10-22
Автор:





Основы работы с базами данных

Система управления базами данных MySQL на сегодняшний день является самой распространённой в веб-разработке. Поэтому, если хотите создать современный сайт, придётся немного разобраться с основами работы в ней.

Было время, когда сайты писались на простом HTML. В них приходилось всё править вручную и для больших проектов это было весьма затруднительно. Затем на смену простому гипертексту пришёл PHP. Он позволил значительно автоматизировать сборку HTML-страниц, однако, не решал проблему хранения больших массивов данных.

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

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

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

id name number address
1 Иванов Иван Иванович 111-111 ул. Пушкина, 1
2 Петров Пётр Петрович 222-222 ул. Лермонтова, 2
3 Сидоров Сидор Сидорович 333-333 ул. Гоголя, 3

Нетрудно понять, что индекс "id" содержит уникальный порядковый номер для всей строки, а остальные поля – различного вида информацию о наших виртуальных знакомых. Некоторые товарищи не нумеруют записи в телефонных книгах, однако, представьте, если она будет содержать сотни записей и среди них окажутся полные однофамильцы? Поэтому в базах данных очень желательно иметь индексы, которые при минимальном наборе символов смогли бы однозначно идентифицировать нужные нам данные. Такой индекс называют первичным ключом (Primary Key).

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

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

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

За счёт наличия возможности взаимосвязей подобные базы данных называют реляционными (от англ. "relation" – "взаимоотношение"). Таким образом MySQL является системой управления реляционными базами данных (СУРБД) и сейчас мы рассмотрим на практике принципы их создания, обработки и взаимодействия.


phpMyAdmin

Многие учебники советуют начинать изучение MySQL с SQL-запросов в SSH-консоли (например, PuTTY), скачав и установив саму MySQL с официального или русскоязычного сообщества. Однако, большинству пользователей сложно сразу привыкнуть к отсутствию визуального интерфейса, поэтому мы пойдём другим путём и воспользуемся замечательным серверным модулем phpMyAdmin.

Если у Вас нет собственного хостинга с PHP и MySQL, то чтобы не качать и не ставить вручную кучу компонентов, рекомендую установить готовый сервер, вроде Денвер или XAMPP (использует вместо MySQL её форк MariaDB с аналогичными командами). Далее рассмотрим всё на примере с Денвером (хотя, на остальных серверах всё примерно так же).

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

Запустим сервер и создадим тестовую базу данных, с которой будем работать. Для этого в браузере зайдём на локальный хост и воспользуемся утилитой "Заведение новых БД и пользователей MySQL", оставив пароль администратора без изменений, и указав имя базы, а также логин и пароль к ней (предлагаю сделать базу "testdb" с логином и паролем "test"):

Заведение базы данных в Денвер

После нажатия на кнопку "Создать БД и пользователя" страница обновится и это будет означать, что база создана. Теперь вернёмся на стартовую страницу сервера и перейдём непосредственно в компонент phpMyAdmin. Здесь в левой части мы увидим список созданных баз данных и нам нужно будет выбрать нашу только что созданную testdb:

phpMyAdmin

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

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

Создание таблицы базы данных в phpMyAdmin

Имя таблицы зададим произвольное (например, "phonebook"), а имена полей согласно разработанной нами выше структуре. Для каждого поля нужно задать тип и длину (обычно 255 для числовых и строковых). Типов полей в MySQL существует довольно много, но все их можно разделить на числовые, поля даты и времени, строковые и пространственные. По поводу числовых и временных полей, думаю, всё понятно, строчные же используются для хранения любых фрагментов текста различной длины или бинарных данных, а пространственные в основном для хранения географических координат любых объектов.

Основными типами полей MySQL являются:

  1. INT – целое число в диапазоне от -2147483648 до 2147483647 (числовой).
  2. FLOAT – дробное число с плавающей точкой (числовой).
  3. DOUBLE – дробное число с плавающей точкой удвоенной точности (числовой).
  4. DATE – поле для хранения даты в формате "год-месяц-день" (дата и время).
  5. TIME – поле для хранения времени в формате "часы:минуты:секунды" (дата и время).
  6. TIMESTAMP – поле для хранения числа-метки времени, прошедшей с начала эпохи UNIX (1970-01-01 00:00:00) в формате: "годмесяцденьчасыминутысекунды" (дата и время).
  7. CHAR – поле для хранения строки фиксированной длины до 255 латинских символов (строчный).
  8. VARCHAR – поле для хранения строки переменной длины до 255 латинских символов (строчный).
  9. TEXT – поле для хранения строк текста объёмом до 4 ГБ (в виде модификации LONGTEXT) (строчный).
  10. BLOB – поле для хранения любых бинарных данных объёмом до 4 ГБ (в виде модификации LONGBLOB) (строчный).

Помимо типов полей можно также задать и тип их индексации в разделе "Индекс":

  1. PRIMARY – указывает на уникальный первичный ключ, который не имеет повторяющихся записей, и является приоритетным при выборке из базы данных (может быть лишь один на всю таблицу).
  2. UNIQUE – указывает на уникальный ключ, который не является первичным, но содержит полностью уникальные записи.
  3. INDEX (или KEY) – создаёт из записей, которые могут повторяться, простой индекс.
  4. FULLTEXT – создаёт полнотекстовый индекс каждой записи с возможностью выборки отдельных слов или символов.

После указания типа индексации можно настроить ещё ряд свойств полей (а можно оставить их без изменений), а также задать общие настройки для всей таблицы внизу. К таким настройкам можно отнести выбор типа таблицы (для большинства случаев подходит значение по умолчанию – InnoDB), а также задать её кодировку в поле "Сравнение" (для русскоязычных ресурсов наиболее приемлемы кодировки cp1251_bin, cp1251_general_ci, koi8r_general_ci, utf8_bin, utf8_general_ci (последняя предпочтительнее)).

Когда все предварительные настройки заданы можно нажимать кнопку "Сохранить" и таблица будет создана. Выберем её и попробуем поработать.

Элементарные действия с таблицей базы данных

При выборе таблицы по умолчанию откроется её "Структура":

Структура таблицы в phpMyAdmin

Данный раздел служит для просмотра и быстрой сортировки данных таблицы по нужным критериям. При этом Вы можете использовать как прямые SQL-запросы, так и визуальные инструменты. В верхней части рабочей страницы находится панель со списком доступных действий. Поскольку таблица пока пуста, предлагаю перейти в раздел "Вставить" и заполнить её:

Добавление записей в phpMyAdmin

После завершения заполнения всех полей и нажатия кнопки "Ok" мы по умолчанию (изменить это можно при помощи выпадающих списков действий внизу) всякий раз будем переходить на вкладку "SQL", в которой сможем видеть валидный автоматически сгенерированный код SQL-запроса на добавление наших записей. На начальных порах, пока Вы не освоите основы SQL, такая наглядность, мне кажется, весьма кстати:

Запрос на добавление записей в phpMyAdmin

Добавляем в базу всех наших абонентов и для их просмотра переходим в раздел "Обзор". Обратите внимание, что в базах данных, как и во всех языках программирования, стартовой точкой отсчёта является не 1, а 0, поэтому нулевая запись (если Вы указывали "id" с 1) будет пустой. В принципе, это можно легко исправить, поскольку на данной вкладке доступна функция быстрого редактирования. Просто нажмите на запись которую нужно править и сможете вписывать нужные данные:

Просмотр таблицы в phpMyAdmin

Кроме правки записей здесь слева доступны функции изменения всех полей в отдельном окне (вкладка "Вставка"), копирования строки и её удаление. Также на этой вкладке имеются инструменты сортировки записей по одному из индексов на Ваш выбор, экспорта данных из таблицы и, конечно же, выполнения различных запросов.

Если Вам нужно быстро найти определённую запись в большой базе данных, то для этого удобнее всего использовать раздел "Поиск":

Поиск в phpMyAdmin

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

  1. Математическое сравнение – = (равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), != (не равно).
  2. Логическое сравнение – LIKE (точное совпадение последовательности символов), LIKE %...% (частичное совпадение), NOT LIKE (несовпадение).
  3. Регулярные выражения – REGEXP (проверка на соответствие регулярному выражению в любой позиции записи), REGEXP %...% (совпадение записи с регулярным выражением от начала и до конца строки), NOT REGEXP (несовпадение с выражением).
  4. Проверка на пустоту – ='' (соответствует отсутствию данных в записи), !='' (запись содержит любые данные), IS NULL (запись содержит пустой объект), IS NOT NULL (запись не содержит пустого объекта).
  5. Диапазоны – IN (...) (значения записей соответствуют нескольким значениям, заданным для поиска), NOT IN (...) (значения записей отличаются от нескольких значений, заданных для поиска), BETWEEN (значения записей входят в диапазон значений, заданных для поиска), NOT BETWEEN (значения записей не входят в диапазон значений, заданных для поиска).

Вроде, несложно, но на практике нужно чётко различать сферу применения всех операторов. Например, мы хотим получить данные о товарище Иванове. Если мы зададим условие поиска `name` = `Иванов`, то не получим результата, поскольку оператор равно используется для сравнения чисел. Также мы не добьёмся успеха, если пропишем условие `name` LIKE `Иванов`, поскольку полная строка ("Иванов Иван Иванович") не соответствует запросу. Здесь нам нужно использовать проверку на частичное совпадение, то есть `name` LIKE `%Иванов%`!

Работа с MySQL средствами PHP

Визуальное управление базой данных, конечно, вещь хорошая, но практической пользы от неё не так уж много. Вся мощь баз данных проявляется лишь при их использовании в контексте решения определённых задач. Наша задача – успешно применить их на нашем сайте для вывода нужного нам контента. Сделать это проще всего средствами языка программирования PHP, который весьма хорошо работает в связке с MySQL.

Алгоритм простейшего взаимодействия PHP и MySQL следующий:

  1. Авторизоваться.
  2. Получить доступ к нужной таблице (-ам) базы данных.
  3. Сделать выборку нужных записей.
  4. Закрыть базу.

Рассмотрим всё это на примерах.

Получение и вывод одной конкретной записи таблицы базы данных

В предыдущем разделе мы создали базу данных и таблицу с простенькой телефонной книгой. Теперь наша задача, например, вывести номер телефона Петрова Петра Петровича на странице сайта. На любом из виртуальных или реальных серверов создайте новый PHP-файл с именем, например, "phone-test.php" и пропишите в него такой код:

<?php

header('Content-Type: text/html; charset=UTF-8');

?>

<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8" />

<title>Телефонная книга</title>

</head>

<body>

<table>

<tr>

<td>Имя</td>

<td>Телефон</td>

</tr>

</tr>

<td>Петров Пётр Петрович</td>

<td>

<?php

$dbaddress = "localhost"; // Имя сервера

$dbuser = "test"; // Имя пользователя

$dbpass = "test"; // Пароль

$dbname = "testdb"; // Имя базы

$dbconnect = @mysql_connect($dbaddress,$dbuser,$dbpass)or die("Cервер базы данных не доступен."); // Подключаемся к серверу базы данных

mysql_select_db($dbname) or die("Не могу подключиться к базе."); //Выбираем нужную базу

mysql_query("SET NAMES utf8"); // Предотвращаем ошибку передачи запроса с кириллицей в кодировке UTF-8

$dbquery = "SELECT * FROM phonebook WHERE name LIKE '%Петров%'"; // Формируем запрос

$dbarray = mysql_query($dbquery); // Выполняем запрос

$dbstring = mysql_fetch_array($dbarray); // Обрабатываем массив полученных данных

echo $dbstring['number']; // Выводим нужное из ассоциативного массива

mysql_close($dbconnect); // Закрываем соединение

?>

</td>

</tr>

</table>

</body>

</html>

Страница с данными

Результат вывода приведённого выше кода должен быть, как на скриншоте. Немного пояснений. Прежде всего нас интересует код в четвёртой ячейке таблицы (который размещён в блоке <?php ... ?>). Из комментариев к каждой строке, думаю, Вы поняли, что сразу мы объявили в виде переменных адрес сервера, имя пользователя, пароль и нужную нам базу данных.

После объявлений идут действия подключения к базе и выбора нужной таблицы. Теперь важная деталь! Даже, если и база данных, и PHP-файл у Вас в кодировке UTF-8, запрос к базе, если он содержит русские буквы, может передаваться неверно! Чтобы избежать ошибки и пустого вывода используйте принудительную перекодировку: mysql_query("SET NAMES utf8").

Теперь сформируем сам запрос. Если Вы не знаете синтаксиса SQL, то для его формирования можете использовать результаты, полученные во вкладке "Поиск" в phpMyAdmin. Для простоты полученный запрос запишем в переменную и следующим шагом выполним его. В ответ мы получим массив данных, который нужно предварительно разобрать при помощи команды mysql_fetch_array().

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

Обновление и добавление записей в базу данных

Мы разобрались как читать данные из базы и теперь рассмотрим, как их туда записать средствами PHP. Алгоритм действий, в принципе, тот же, что и в примере выше. Отличаться будет, разве, SQL-запрос. Если помните, у нас в нулевой строке базы данных сейчас пусто. Предлагаю добавить туда некого Онуфреева Онуфрия Онуфриевича. Создадим новый файл add.php следующего содержания:

<?php

header('Content-Type: text/html; charset=UTF-8');

$dbaddress = "localhost"; // Имя сервера

$dbuser = "test"; // Имя пользователя

$dbpass = "test"; // Пароль

$dbname = "testdb"; // Имя базы

$dbconnect = @mysql_connect($dbaddress,$dbuser,$dbpass)or die("Cервер базы данных не доступен."); // Подключаемся к серверу базы данных

mysql_select_db($dbname) or die("Не могу подключиться к базе."); //Выбираем нужную базу

mysql_query("SET NAMES utf8"); // Предотвращаем ошибку передачи запроса с кириллицей в кодировке UTF-8

$dbquery = "UPDATE phonebook SET name = 'Онуфреев Онуфрий Онуфриевич', number = '000-000', address = 'ул. Ожегова, 0' WHERE id = 0"; // Формируем запрос

$dbarray = mysql_query($dbquery); // Выполняем запрос

mysql_close($dbconnect); // Закрываем соединение

?>

Запрос на обновление существующих полей таблицы всегда начинается со слова UPDATE с указанием имени таблицы. После этого следует перечисление обновляемых полей со служебным словом SET. А в самом конце мы указываем условие выбора строки со служебным словом WHERE.

Если мы захотим добавить новую строку с новым абонентом, нам нужно будет использовать другой запрос со служебным словом INSERT:

$dbquery = "INSERT INTO phonebook (id, name, number, address) VALUES ('4', 'Павлов Павел Павлович', '444-444', 'ул. Павленко, 4')";

Здесь мы задаём, в какую таблицу вставлять данные, затем перечисляем в скобках изменяемые поля, а после этого (так же в скобках) задаём их значения в той же последовательности, что и имена полей.

Средствами PHP мы можем полностью контролировать работу базы данных, выполняя сложные запросы на сортировку, вывод данных, изменения содержимого таблиц и даже их создания и удаления. Все эти команды нужно изучать отдельно. Данная статья ставит целью дать лишь азы понимания принципов работы с базами данных, но не объясняет всех нюансов работы с MySQL и SQL-запросами в частности.

Выводы

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

Поэтому, если Вы решили создавать долговременный проект, настоятельно рекомендую вникнуть в принципы работы с базами данных. А снизить порог вхождения в тему, надеюсь, Вам поможет наша статья. Успехов!

P.S. Разрешается свободно копировать и цитировать данную статью при условии указания открытой активной ссылки на источник и сохранения авторства Руслана Тертышного.







Полезный совет:



Система проверки ошибок от Mistakes.ru