Основные команды при работе с СУБД MySQL или MariaDB

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

Сперва, давайте осуществим подключение к серверу 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');

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