Основы MariaDB

Подключение к MariaDB

MariaDB — это система управления базами данных, сервер баз данных. Для взаимодействия с сервером MariaDB можно использовать программу-клиент или написать программу или скрипт на одном из популярных языков программирования (например, PHP), используя API (интерфейс прикладного программирования) для взаимодействия с сервером MariaDB. В этой статье мы сосредоточимся на использовании стандартного клиента, поставляемого с MariaDB, называемого mariadb. С помощью этого клиента вы можете вводить запросы из командной строки или переключаться в режим мониторинга терминала. Начнём с последнего.

Из командной строки Linux вы введёте следующее, чтобы войти как пользователь root и перейти в режим мониторинга:

mariadb -u root -p -h localhost

Опция -u предназначена для указания имени пользователя. Замените root на другое имя пользователя, если необходимо. Это имя пользователя MariaDB, а не имя пользователя Linux. Пароль для пользователя MariaDB root вероятно отличается от пароля пользователя Linux root. Кстати, не рекомендуется использовать пользователя root, если у вас нет конкретной административной задачи, для выполнения которой только у root есть необходимые права.

Опция -p выше указывает клиенту mariadb запросить у вас пароль. Если пароль для пользователя root ещё не задан, то пароль пустой, и вам нужно будет нажать [Ввод], когда вас попросят. Опция -h предназначена для указания имени хоста или IP-адреса сервера. Это необходимо, если клиент работает на другом компьютере, отличном от сервера. Если вы подключились к серверу с использованием защищённой оболочки, вам, вероятно, не нужно будет использовать опцию хоста. На самом деле, если вы вошли в Linux как root, вам не понадобится опция пользователя — вам понадобится только -p. После ввода строки выше вместе с паролем, когда вас попросят, вы войдёте в MariaDB через клиента. Чтобы выйти, введите quit или exit и нажмите [Ввод].

Создание структуры

Для добавления и изменения данных сначала необходимо создать структуру базы данных. Создание базы данных простое. Вы введёте что-то вроде следующего в клиенте MariaDB:

CREATE DATABASE bookstore;

USE bookstore;

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

Следующим шагом является начало создания таблиц. Это немного сложнее. Чтобы создать простую таблицу, которая будет содержать основные данные о книгах, мы можем ввести что-то вроде следующего:

CREATE TABLE books (
isbn CHAR(20) PRIMARY KEY, 
title VARCHAR(50),
author_id INT,
publisher_id INT,
year_pub CHAR(4),
description TEXT );

Этот SQL-запрос создаёт таблицу books с шестью полями, или, точнее, столбцами. Первый столбец (isbn) — это идентификационный номер каждой строки — это имя, связанное с уникальным идентификатором, используемым в издательском деле. Он имеет фиксированную ширину типа символа 20 символов. Он будет первичным ключом, по которому будут индексироваться данные. Тип данных столбца для названия книги — столбец символов переменной ширины максимум 50 символов. Третий и четвёртый столбцы будут использоваться для идентификационных номеров автора и издателя. Они являются целыми типами данных. Пятый столбец используется для года публикации каждой книги. Последний столбец — для ввода описания каждой книги. Это тип данных TEXT, что означает, что это столбец переменной ширины и он может содержать до 65535 байтов данных для каждой строки. Есть и другие типы данных, которые можно использовать для столбцов, но это даёт вам хорошее представление.

Чтобы увидеть, как выглядит созданная таблица, введите следующий SQL-запрос:

DESCRIBE books;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| isbn         | char(20)    | NO   | PRI | NULL    |       |
| title        | varchar(50) | YES  |     | NULL    |       |
| author_id    | int(11)     | YES  |     | NULL    |       |
| publisher_id | int(11)     | YES  |     | NULL    |       |
| year_pub     | char(4)     | YES  |     | NULL    |       |
| description  | text        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

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

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

CREATE TABLE authors
(author_id INT AUTO_INCREMENT PRIMARY KEY,
name_last VARCHAR(50),
name_first VARCHAR(50),
country VARCHAR(50) );

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

Незначительные моменты

Прежде чем перейти к следующему шагу — добавлению данных в таблицы, позвольте мне отметить несколько незначительных моментов, которые я опустил. SQL-запросы заканчиваются точкой с запятой (или \G). Вы можете разбить SQL-запрос на несколько строк. Однако он не будет передан на сервер клиентом, пока вы не завершите его точкой с запятой и не нажмёте [Ввод]. Чтобы отменить SQL-запрос после начала ввода, введите \c и нажмите [Ввод].

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

Ввод данных

Основной метод ввода данных в таблицу — это использование оператора INSERT. В качестве примера введём информацию об авторе Франце Кафке в таблицу authors. Сделаем это так:

INSERT INTO authors
(name_last, name_first, country)
VALUES('Kafka', 'Franz', 'Czech Republic');

Это добавит имя и страну автора Франца Кафки в таблицу authors. Нам не нужно указывать значение для author_id, так как этот столбец был создан с опцией AUTO_INCREMENT. MariaDB автоматически назначит идентификационный номер. Вы можете вручную назначить номер, особенно если хотите начать отсчёт с числа больше 1 (например, 1000). Поскольку мы не предоставляем данных для всех столбцов таблицы, мы должны указать столбцы, для которых мы предоставляем данные, и в том порядке, в котором данные предоставляются в наборе после ключевого слова VALUES. Это означает, что мы могли бы предоставить данные в другом порядке.

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

INSERT INTO books
(title, author_id, isbn, year_pub)
VALUES('The Castle', '1', '0805211063', '1998');

Это добавляет запись для книги Кафки «Замок». Обратите внимание, что мы изменили порядок столбцов, но это всё равно работает, потому что оба набора согласованы. Мы указываем, что автором является Кафка, задав значение 1 для author_id. Это значение было присвоено MariaDB при вводе строки для Кафки ранее. Давайте введём ещё несколько книг Кафки, но другим способом:

INSERT INTO books
(title, author_id, isbn, year_pub)
VALUES('The Trial', '1', '0805210407', '1995'),
('The Metamorphosis', '1', '0553213695', '1995'),
('America', '1', '0805210644', '1995');

В этом примере мы добавили три книги в одном операторе. Это позволяет нам указать список имён столбцов один раз. Мы также указываем ключевое слово VALUES только один раз, за которым следует отдельный набор значений для каждой книги, каждый заключённый в скобки и разделённый запятыми. Это сокращает написание и ускоряет процесс. Любой способ подходит, и оба имеют свои преимущества. Чтобы продолжить примеры, предположим, что данные по тысячам книг были введены. Учитывая это, давайте посмотрим, как извлекать данные из таблиц.

Извлечение данных

Основной метод извлечения данных из таблиц — это использование оператора SELECT. Существует много вариантов оператора SELECT, но вы можете начать просто. В качестве примера давайте извлечём список названий книг из таблицы books:

SELECT title 
FROM books;

Это отобразит все строки из таблицы books. Если таблица содержит тысячи строк, MariaDB отобразит тысячи. Чтобы ограничить количество извлекаемых строк, мы можем добавить предложение LIMIT к оператору SELECT так:

SELECT title 
FROM books
LIMIT 5;

Это ограничит количество отображаемых строк пятью. Чтобы перечислить имя автора каждой книги вместе с названием, вам необходимо объединить таблицу books с таблицей authors. Для этого мы можем использовать предложение JOIN:

SELECT title, name_last 
FROM books 
JOIN authors USING (author_id);

Обратите внимание, что основная таблица, из которой мы извлекаем данные, указана в предложении FROM. Таблица, которую мы объединяем, указана в предложении JOIN вместе с обычно используемым столбцом (т.е. author_id), который мы используем для объединения.

Чтобы извлечь названия только книг, написанных Кафкой, на основе его имени (а не author_id), мы будем использовать предложение WHERE с оператором SELECT. Это будет введено следующим образом:

SELECT title AS 'Kafka Books'
FROM books 
JOIN authors USING (author_id)
WHERE name_last = 'Kafka';

+-------------------+
| Kafka Books       |
+-------------------+
| The Castle        |
| The Trial         |
| The Metamorphosis |
| America           |
+-------------------+

Этот запрос выведет названия книг Кафки, хранящихся в базе данных. Обратите внимание, что я добавил параметр AS рядом с именем столбца title, чтобы изменить заголовок столбца в наборе результатов на «Книги Кафки». Это называется псевдонимом. Ознакомившись с результатами, мы можем увидеть, что название одной из книг Кафки некорректно. В таблице выше слово Amerika написано с «c» вместо «k». Это приводит к следующему разделу о изменении данных.

Изменение и удаление данных

Для изменения существующих данных распространённым методом является использование оператора UPDATE. Однако при изменении данных нам нужно убедиться, что мы изменяем правильные строки. В нашем примере может быть другая книга с названием «Америка», написанная другим автором. Поскольку столбец-ключ isbn содержит только уникальные номера, и мы знаем номер ISBN книги, которую хотим изменить, мы можем использовать его для указания строки.

UPDATE books
SET title = 'Amerika'
WHERE isbn = '0805210644';

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

Если нужно удалить строку данных, можно использовать оператор DELETE. Например, предположим, что наша вымышленная книжная лавка решила больше не продавать книги Джона Гришэма. Сначала выполнив оператор SELECT, мы определяем идентификационный номер автора, который равен 2034. Используя этот идентификатор автора, можно ввести следующее:

DELETE FROM books
WHERE author_id = '2034';

Этот оператор удалит все строки из таблицы books для заданного author_id. Чтобы сделать это корректно, необходимо выполнить аналогичные действия для таблицы authors. Просто замените имя таблицы в операторе выше; всё остальное останется неизменным.

Заключение

Это очень базовый учебник по использованию MariaDB. Надеемся, он даст вам представление о том, как начать работу с MariaDB. Каждый из упомянутых здесь операторов SQL имеет ещё несколько дополнительных опций и условий. Мы рассмотрим эти и другие операторы более подробно в других статьях. Сейчас же вы можете узнать больше об этом, экспериментируя и изучая онлайн-документацию базы знаний. Скачиваемый PDF-файл большей части документации доступен здесь.