Рубрика: MariaDB

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

mysql -u root -p

Следующим шагом вам будет предложено ввести пароль вашего пользователя. Примечание. Если вы работаете под другим пользователем, то нужно указывать его. В моём примере — это пользователь root.

Вот пример удачного входа на сервер MySQL

mysql> это хороший знак! Значит вы успешно авторизовались. Для СУБД MariaDB будет справедливо MariaDB [(none)]>

Если вы находитесь на сервере под учётной записью root, то в некоторых случаях осуществить подключение может простая команда

mysql 

Ниже мы видим реальное выполнение команды под учётной записью root

Отлично! Теперь можно приступить к созданию базы данных.

Как создать базу данных из командной строки?

Создать базу данных мы можем при помощи оператора CREATE DATABASE

• Оператор CREATE DATABASE на сайте MySQL
• Оператор CREATE DATABASE на сайте MariaDB

Если используется выражение IF NOT EXISTS, то в ситуации, когда база данных уже существует, пользователю будет выдано предупреждение, а не сообщение об ошибке.

CREATE DATABASE IF NOT EXISTS db_name;

Результат вывода

+--------------------+
| Database           |
+--------------------+
| amo                |
| db_name            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.020 sec)

Поздравляю! База данных с именем db_name успешно создана.

Для ознакомления с базовыми операторами рекомендуется к прочтению эта публикация.

Как удалить базу данных из командной строки?

Тут всё очень просто. Идём от обратного. Оператор DROP удаляет, а выражение IF EXISTS проверяет присутствие базы с этим именем в системе

DROP DATABASE IF EXISTS db_name;

Настройка кодировки и правил сортировки

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

Вы можете начать с просмотра поддерживаемых параметров сортировки вашего сервера. Для этого воспользуйтесь оператором SHOW COLLATION

Отмечу, что в СУБД MariaDB и MySQL имеют набор символов по умолчанию, и в зависимости от дистрибутива параметры сортировки могут отличаться. И если ваш сайт, например, на двух языках: латинские и кириллические символы, то возможно при создании базы данных оптимальным выбором будет указать utf8mb4_general_ci | utf8mb4

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

CREATE DATABASE IF NOT EXISTS db_name
    CHARACTER SET = 'utf8mb4'
    COLLATE = 'utf8mb4_general_ci';

Но если вы сомневаетесь, или точно знаете, какие символы будут присутствовать в индексах, то можно остановиться на этом решении utf8mb4_unicode_ci — которое обеспечивает полную поддержку символов Unicode, или версией Unicode «520»: utf8mb4_unicode_520_ci, или более свежим алгоритмом сравнения символов utf8mb4_0900_ai_ci. Но тут по ситуации.

CREATE DATABASE IF NOT EXISTS db_name
    CHARACTER SET = 'utf8mb4'
    COLLATE = 'utf8mb4_unicode_ci';

В каких то случаях оптимально будет указать CHARACTER SET ‘utf8’, так как эта кодировка менее требовательна к памяти, но зато не может сохранить, например, смайлики, которые требуют выделения большего числа байтов для хранения (utf8mb4). Вобщем, тут нюансов не мало и зависит от архитектуры и требований к веб-приложению.

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

ALTER DATABASE db_name COLLATE utf8mb4_unicode_ci;

Что ж, база данных у нас есть и теперь нам необходимо создать пользователя.

Как создать нового пользователя в MySQL или MariaDB?

Работать под «рутом», как мы делали это раньше, не безопасно. Поэтому, давайте создадим отдельного пользователя и наделим его правами.

По аналогии с адресом электронной почты имя пользователя СУБД MySQL (или MariaDB) состоит из двух частей, разделенных символом «собачки»:

‘user’@’host’

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

При создании пользователя предлагаю сразу добавить пароль для аутентификации на сервере (IDENTIFIED BY) и по аналогии с созданием БД проверить на отсутствие в системе при помощи IF NOT EXISTS

CREATE USER IF NOT EXISTS 'vasya'@'localhost' IDENTIFIED BY '12345';

Убедимся, что наш пользователь добавился:

SHOW CREATE USER 'vasya'@'localhost';

Результат:

MariaDB [db_name]> SHOW CREATE USER 'vasya'@'localhost';
+----------------------------------------------------------------------------------------------------+
| CREATE USER for vasya@localhost                                                                    |
+----------------------------------------------------------------------------------------------------+
| CREATE USER `vasya`@`localhost` IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

Обратите внимание, что пароль хранится в хешированном виде, так как был хеширован функцией PASSWORD перед сохранением в таблице mysql.user.

Как удалить пользователя в MySQL или MariaDB?

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

DROP USER IF EXISTS 'vasya'@'localhost';

Как изменить пароль у пользователя СУБД?

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

ALTER USER 'vasya'@'localhost' IDENTIFIED BY 'new_password';

На самом деле нюансов и настроек большое количество при создании пользователей СУБД. Взглянуть можно тут.

Следующим шагом мы проверим подключение и пройдём аутоинтефикацию на сервере под именем нового пользователя. Для этого выйдем из оболочки MySQL при помощи команды exit; И затем осуществим вход под новым созданным пользователем:

mysql -u vasya -p

И затем попросим СУБД отобразить базы данных

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.002 sec)

Маленькая неприятность. Мы не видим всех баз данных, а только служебную. Это происходит потому что наш пользователь не наделён нужными правами.

Давайте проверим текущие права (полномочия) пользователя

SHOW GRANTS FOR 'vasya'@'localhost';

На текущий момент они отсутствуют. Вот пример сравнение двух пользователей: root и vasya

MariaDB [(none)]> SHOW GRANTS FOR 'root'@'localhost';
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'vasya'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for vasya@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `vasya`@`localhost` IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Что ж, давайте исправим это. И поможет нам в этом оператор GRANT, с помощью которого мы предоставим полный доступ к созданной базе данных пользователю ‘vasya‘.

В работе с оператором тоже не мало нюансов, нельзя смешивать сразу привилегии и роли, а иногда стоит выборочно указывать возможности (привилегии) пользователю и также выборочно предоставлять доступ, например, не ко всей БД, а к какой-нибудь отдельной таблице и т.п.

Но мы рассмотрим общую типичную ситуацию, когда мы создали базу данных, предположим для сайта на CMS WordPress, создали отдельного пользователя для работы с ней, чтобы он мог (от его имени) создавать новые таблицы, выполнять операции по удалению, обновлению данных (SELECT, INSERT, UPDATE…).

И всё что нам осталось — это наделить его всеми правами исключительно для созданной базы данных (напомню, что в нашем примере её имя db_name)

GRANT ALL ON db_name.* TO 'vasya'@'localhost';

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

GRANT ALL ON db_name.* TO 'vasya'@'localhost';
FLUSH PRIVILEGES;

Теперь если мы зайдём под учётной записью ‘vasya‘ и повторим запрос на просмотр доступных БД, то увидим в списке нашу db_name

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db_name            |
| information_schema |
+--------------------+
2 rows in set (0.001 sec)

А если сделаем запрос на просмотр привилегий, то можем заметить следующее:

MariaDB [(none)]> SHOW GRANTS FOR 'vasya'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for vasya@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `vasya`@`localhost` IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
| GRANT ALL PRIVILEGES ON `db_name`.* TO `vasya`@`localhost`                                                   |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

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

Сперва, давайте осуществим подключение к серверу MySQL или MariaDB указав специальные параметры в командной строке. В этом примере мы подключаемся по протоколу SSH от пользователя root к нашему серверу, где находится (установлена) наша СУБД

ssh root@5.35.80.180

Если вы увидите в консоли подобную картинку, то это хороший знак 🙂 Мы успешно прошли авторизацию как пользователь root

Теперь необходимо ввести название вашей СУБД:

mysql

или соответственно для MariaDB

mariadb

Если всё прошло успешно, то вы должны увидеть в консоле схожую картинку, где СУБД готова выполнять наши команды

Иногда, в тех случаях если вы авторизовались на сервере под другой учётной записью, нужно дописать sudo

sudo mariadb

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

Чтобы внести изменения в базу данных или получить данные, вам нужно будет ввести оператор SQL. SQL расшифровывается как Язык Структурированных Запросов. Оператор SQL, который запрашивает данные, называется запросом. Базы данных хранят информацию в таблицах. Они похожи на электронные таблицы, но гораздо более эффективны в управлении данными.

https://mariadb.com/kb/ru/a-mariadb-primer/

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

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

CREATE DATABASE IF NOT EXISTS poetry;

Запрос выполнен успешно.

Посмотреть все базы данных

Теперь при помощи оператора SHOW мы можем посмотреть все доступные нам на текущем сервере СУБД базы данных:

SHOW DATABASES;

Всё прошло успешно и мы видим созданную нами базу данных poetry. Прочие базы данных — это служебные базы самой СУБД и удалять их не нужно!

Выбор базы данных

Теперь, для дальнейшей работы, мы можем выбрать с помощью оператора USE нужную нам базу данных:

USE poetry;

База данных выбрана:

Похожей командой SHOW давайте посмотрим её таблицы:

SHOW TABLES;

Но мы будем немного разочарованы ответом: Empty set (0.001 sec)

Это говорит, о том, что в нашей базе данных нет ещё таблиц. Так давайте же скорее их создадим!

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

Давайте осуществим инструкцию для нашей СУБД — CREATE TABLE, которая создаст в базе таблицу. Мы будем создавать таблицу для хранение данных, о поэтах. Следовательно, мы неизбежно затрагиваем понятия типы данных. Углубляться мы сейчас в тонкости не будем, что лучше и для чего подходит и сколько оптимально выделять памяти для этих данных и т.п. В этот космос мы не полезем, а разберём простой пример. Нам нужны минимум два поля:

1. ID (poet_id) — уникальный идентификатор для поэта. Это как паспорт — у каждого свой, у каждого уникальный. Повтора быть не может.
2. Строка для хранения ФИО (poet_name) поэта

Отмечу, что создать таблицу можно просто при помощи инструкции (команды) CREATE TABLE, но мы добавим для эстетичности проверочку:

CREATE TABLE IF NOT EXISTS poets 
(poet_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
poet_name VARCHAR(100) NOT NULL);

В этом примере мы создали два поля для хранения данных:

• INT — числовое, тип данных целое число
• NOT NULL — не может быть не заполнено, своего рода предписание обязательно к заполнению
• PRIMARY KEY — первичный ключ, понадобится нам для связей, но углубляться сейчас в этот термин не будем. Для каждой таблицы может быть не более одного первичного ключа.
• AUTO_INCREMENT — автоматическое присвоение номере (айдишника поэта) путём увеличения значения. Важно понимать что после удаления значения, вновь созданному повторно эта цифра не присвоится
• VARCHAR -для хранения текстовых данных переменной длины. В нашем случае ограничили сотней символов, как мне показалась для ФИО поэта это более, чем достаточно

Результат данного запроса при повторном отображении (SHOW TABLES) уже отобразит нашу строку Поэтов:

Давайте при помощи инструкции DESCRIBE (можно указать сокращённо DESC) посмотрим информацию о нашей табличке поэтов:

DESCRIBE poets;

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

Во второй таблице нам нужно хранить произведения этих авторов (поэтов). Попробуйте самостоятельно создать таблицу для данных. Вот техническое задание:

• Название таблицы poems
• Айдишник стихотворения poem_id
• Название стихотворения poem_title

CREATE TABLE IF NOT EXISTS poems 
(poem_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
poem_name VARCHAR(150) NOT NULL);

Поздравляю! Теперь у нас уже две таблички для хранения данных. Самое время их наполнить!

Наполнение таблиц БД данными

Инструкция (команда) INSERT INTO. Тут важно сопоставить названия наших столбцов со значениями которые мы им будем присваивать. В примере я заполняю таблицу поэтов их именами. Обратите внимание на то, что ID будет присваиваться автоматически. Тут важно уловить суть в синтаксисе, что где и как разделяется запятыми. А значение, которые строго соответствуют схеме в INSERT INTO должны тоже разделяться запятыми, но для простоты примера я ограничился одним значением — именем поэта.

INSERT INTO poets (poet_name) 
VALUES('Александр Сергеевич Пушкин'), 
      ('Михаил Юрьевич Лермонтов'),
      ('Сергей Александрович Есенин'),  
      ('Анна Андреевна Ахматова'),
      ('Борис Леонидович Пастернак'), 
      ('Николай Алексеевич Некрасов'),
      ('Владимир Владимирович Маяковский');

Вот пример выполнения данного SQL запроса. Важно инструкцию для СУБД закрывать точкой с запятой, а то что пробелы и переносы строк у нас получились лишние — это ничего страшного, СУБД принимает их к исполнению, главное не забывайте объявлять конец инструкции. Получим:

Запрос данных. Оператор SELECT

Ну вот и пришла пора познакомиться нам с оператором «Селект», при помощи которого мы можем делать выборку наших данных.

Мы знаем, что у нас уже есть данные в табличке poets, так давайте их запросим!

Данный пример, благодаря «звёздочке» запрашивает все столбцы данных из (FROM) таблицы poets

SELECT * FROM poets;

Всё верно, не правда-ли?

Если бы нас интересовали данные исключительно из столбца с ФИО поэта, то запись запроса выглядела бы следующим образом:

SELECT poet_name FROM poets;

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

Давайте проведём небольшой эксперимент. Мы удалим данные из таблицы поэтов и снова повторно создадим, чтобы посмотреть какой идентификатор будет присвоен нашему новому поэту.

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

DELETE FROM poets;

Это удалит все данные из нашей таблицы поэтов. Нам это не нужно. Тогда нашу запись нужно дополнить условием WHERE

DELETE FROM poets WHERE poet_id = 7;

В этом примере мы строго удаляем поэта с номером 7. В итоге получаем следующее:

Теперь при создании нового поэта ему будет присвоен следующий порядковый номер (но не 7!). Здесь отрабатывает заданный параметр AUTO_INCREMENT, при помощи которого ведётся, так сказать, история данной таблицы и учёт созданных уникальных значений идентификаторов. P.S. конечно есть решения и для сброса истории AUTO_INCREMENT — инструкция TRUNCATE TABLE, которая делает полный сброс, но об этом точно не в этой публикации 🙂

Мы научились добавлять данные при помощи оператора INSERT, задавая значения VALUES. Но вдруг, случилось так, что нам необходимо обновить данные в какой то из строк. Для этих целей существует оператор UPDATE.

Нам нужно за что то зацепиться и этим что то будет являться первичный ключ — «айди» нужной нам строки, именно по ней мы будем ее уникально идентифицировать. Запрос будет выглядеть так:

UPDATE poets 
SET poet_name= "Анна Ахматова" 
WHERE poet_id = 4;

Попробуйте выполнить этот запрос и посмотреть в консоле как изменились ваши данные.

Изменение структуры таблицы

Однажды, нам может понадобиться дополнить нашу таблицу данными или изменить текущие. Представим, что цифровой век застал бы этих поэтов и им пришлось бы обзавестись почтой, например, на сервисе gmail.

Давайте дополним таблицу новым столбиком poet_email при помощи инструкции ALTER TABLE

ALTER TABLE poets
ADD poet_email VARCHAR(50);

Готово!

Итак, давайте дополним данные наших поэтов. Не сочтите за фамильярность, но е-мэйлы буду создавать просто по имени поэта, для того чтобы не было совпадений с реальными адресами, так как в Google в их сервисе разрешено создавать ящик минимум от 6-ти символов.

Обновим данные при помощи UPDATE

UPDATE poets
SET poet_email= "sasha@gmail.com" 
WHERE poet_id = 1;

В примере выше мы обновили данные в одно строке, а в следующем примере мы добавим нового поэта, но уже с указанием второго столбца — его почты

INSERT INTO poets (poet_name, poet_email) 
VALUES('Игорь Владимирович Тальков', 'igor@gmail.com');

Ну вот и всё! Надеюсь, основы основ взаимодействия с СУБД , будут вам полезны и не показались через чур сложными.