ZaLinux.ru

Изучение MySQL / MariaDB для начинающих

В этой статье я покажу, как создать базу данных (также известную как schema, схема), таблицы (с типами данных) и объясню, как выполнять операции языка управления данными (Data Manipulation Language (DML)) на MySQL / MariaDB сервере.

Подразумевается, что вы уже установили MySQL или MariaDB сервер.

Для краткости я буду говорить MariaDB, но все концепции и команды из этой статьи также применимы к MySQL.

Создание баз данных, таблиц и авторизированных пользователей

Под базой данных понимается набор организованной информации. MariaDB является системой управления базами данных (СУБД). Для создания, модификации и управления данными используется язык структурированных запросов, по-английски structured query language, т.е. SQL. Именно основы этого языка мы и будем осваивать в этой статье. Чтобы не путаться, помните, что MariaDB использует термины «база данных» и «схема» как взаимозаменяемые.

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

Подключение к базе данных

Запросы к базе данных можно передавать из большинства языков программирования, например, из PHP, а также возможно напрямую. Для непосредственного подключения к СУБД MariaDB введите:

mysql -u root -p

После появления запроса, введите пароль, приглашение оболочки Bash смениться на приглашение MariaDB:

Именно сюда мы будет вводить последующие команды.

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

Для создание новой базы данных с название BooksDB, введите в приглашение командной строки MariaDB следующую команду:

CREATE DATABASE BookstoreDB;

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

Введение в типы данных MariaDB

Как сказано ранее, таблицы – это объекты базы данных, где мы будет хранить постоянную информацию. Каждая таблица состоит из колонок. Колонка содержит данные определённого типа.

Самыми распространёнными типами данных в MariaDB являются следующие

Число:

  • BOOLEAN значение 0 считается false (т.е. ложь), а любые другие данные расцениваются как true (т.е. истина).
  • TINYINT (tiny integer, т.е. буквально крошечное целое число), можно использовать как SIGNED (т.е. со знаком), тогда этим типом охватываются числа от -128 до 127, также можно использовать как UNSIGNED (т.е. без знака), тогда в охватываемый диапазон входят целые числа от 0 до 255.
  • SMALLINT (small integer, т.е. буквально маленькие целые числа), опять же, если используется с SIGNED, то этим типом охватывается диапазон от -32768 до 32767. Диапазон UNSIGNED от 0 до 65535.
  • MEDIUMINT (medium integer, т.е. буквально средние целые числа, с SIGNED это от -8388608 до 8388607. Без знака это диапазон от 0 до 16777215.
  • INT (integer, буквально целое число), если используется с SIGNED, охватывает диапазон от 0 до 4294967295, и -2147483648 до 2147483647 в противном случае.
  • BIGINT (big integer, т.е. буквально большое целое число), со знаком это диапазон от -9223372036854775808 до 9223372036854775807. Без знака от 0 до 18446744073709551615.

Помните: В TINYINT, SMALLINT, MEDIUMINT, INT и BIGINT, SIGNED предполагается по умолчанию.

  • DOUBLE(M, D), где M – это общее количество цифр, а D – это количество цифр после десятичной точки, представляет собой числа с двойной точностью с плавающей запятой. Если указана UNSIGNED, отрицательные значения не разрешены.

Строка:

  • VARCHAR(M) представляет строку переменной длины, где M – это максимально разрешённая длина колонки в байтах (65,535 в теории). В большинстве случае количество байтов идентично количеству символов, кроме символов, которым может потребоваться до 3 байтов (utf8). Например, испанская буква ñ представляет собой один символ, но использует 2 байта.
  • TINYTEXT – это текстовые значения, с максимальной длиной 255 символов. Эффективная максимальная длина меньше, если значение содержит многобайтную кодировку.
  • TEXT(M) представляет колонку с максимальной длиной в 65,535 символов. Тем не менее, как и с VARCHAR(M), реальная максимальная длина уменьшается при сохранении многобайтных символов. Если M указана, создаётся самая маленькая колонка типа TEXT, которая достаточно большая для хранения значения длиной в M символов.
  • MEDIUMTEXT(M) и LONGTEXT(M) похожи на TEXT(M), разница только в максимально разрешённой длине значения, которая составляет, соответственно 16,777,215 и 4,294,967,295.

Дата и время:

  • DATE представляет дату в формате YYYY-MM-DD.
  • TIME представляет время в формате HH:MM:SS.sss (чисы, минуты, секунды и милисекунды).
  • DATETIME является комбинацией DATE и TIME в формате YYYY-MM-DD HH:MM:SS.
  • TIMESTAMP используется для определения момента, когда строка была добавлена или обновлена.

Теперь, когда вы бегло ознакомились с типами данных, вам будет проще определить, данные какого типа назначить заданной колонке в таблице.

Например, имя человека может легко поместиться в VARCHAR(50), в то время как сообщение в блоге потребует тип TEXT (можно указать M под специфические нужды).

Создание таблицы с Primary и Foreign ключами

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

Ключ primary (главный) содержит значение, которое индивидуально определяет каждый ряд или запись в таблице. А foreign (буквально «внешний») используется для создания связи между данными в двух таблицах, и контролирования данных, которые могут сохраняться в таблице, где размещён foreign ключ. Обычно для primary и foreign ключей выбирают тип INT.

Для иллюстрации, давайте воспользуемся BookstoreDB и создадим две таблицы с именами AuthorsTBL и BooksTBL как показано ниже. Константа NOT NULL означает, что данное поле требует значение и не может быть NULL (не заданным).

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

Если сейчас мы начнём вводить данные, СУБД не будет знать, для какой базы данных они предназначаются. На сервере может быть множество БД – в какой из них пользователь создаёт таблицы? Чтобы разрешить эту неопределённость используется команда USE, после которой указывается имя базы данных, с который вы собираетесь работать:

USE BookstoreDB;

Посмотрите на изменившееся приглашение командной строки:

Теперь все введённые команды и данные будут относиться к БД BookstoreDB.

Скопируйте целиком и вставьте следующие команды в приглашение командной строки:

CREATE TABLE AuthorsTBL (
	AuthorID INT NOT NULL AUTO_INCREMENT,
	AuthorName VARCHAR(100),
	PRIMARY KEY(AuthorID)
);
CREATE TABLE BooksTBL (
	BookID INT NOT NULL AUTO_INCREMENT,
	BookName VARCHAR(100) NOT NULL,
	AuthorID INT NOT NULL,
	BookPrice DECIMAL(6,2) NOT NULL,
	BookLastUpdated TIMESTAMP,
	BookIsAvailable BOOLEAN,
	PRIMARY KEY(BookID),
	FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);

Как видим, команда создания таблицы имеет вид

CREATE TABLE имя_таблицы (
	имя_колонки1 ТИП ДАННЫХ,
	имя_колонки2 ТИП ДАННЫХ
);

Команду можно было вводить построчно или скопировать и вставить за один раз. При построчном вводе СУБД в качестве окончания вводимой команды ожидает точки с запятой (;). Также команду можно было записать в одну строку, например:

CREATE TABLE имя_таблицы (имя_колонки1 ТИП ДАННЫХ, имя_колонки2 ТИП ДАННЫХ);

Никакой разницы нет.

Теперь мы может продолжить и начать вводить данные в AuthorsTBL и BooksTBL.

Выбор, вставка, обновление и удаление рядов

Начнём с заполнения таблица AuthorsTBL. Почему? Нам нужно иметь значения AuthorID перед вставкой записей в BooksTBL.

Выполните следующий запрос к MariaDB:

INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Команда вставки строк в таблицу называется INSERT и имеет общий вид:

INSERT INTO имя_таблицы (имя_поля1, имя_поля2) VALUES ('значение_первого_поля', 'значение_второго_поля');

В результате этой команды была бы вставлена одна новая строка. За один раз можно вставить сразу несколько строк. Команда вставки нескольких строк имеет вид:

INSERT INTO имя_таблицы (имя_поля1, имя_поля2) VALUES ('значение первого поля в первой строке', 'значение второго поля в первой строке'), ('значение первого поля во второй строке', 'значение второго поля во второй строке');

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

INSERT INTO AuthorsTBL (AuthorID, AuthorName) VALUES ('1', 'Agatha Christie'), ('2', 'Stephen King'), ('3', 'Paulo Coelho');

Но поскольку в таблице AuthorsTBL при характеристике типа AuthorID был установлен флаг AUTO_INCREMENT, который означает автоматическую установку уникального номера, то мы смогли из нашей команды убрать информацию, относящуюся к AuthorID. СУБД всё сделала сама: сама заполнила эти поля значениями 1, 2, 3.

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

SELECT * FROM AuthorsTBL;

Команда SELECT используется для получения (выбора) записей из таблицы.

Общий синтаксис команды:

SELECT поле1, поле2 FROM таблица WHERE условие;

Как мы могли убедиться, часть WHERE условие; является опциональной. Если она не определена, то выбираются все строки. После SELECT можно указать название полей, которые вас интересуют, например:

SELECT AuthorID, AuthorName FROM AuthorsTBL;

Символ звёздочки (*) означает сразу все поля, т.е. вывод последней команды полностью идентичен  SELECT * FROM AuthorsTBL;

Можно указать (через запятую) любой набор полей по желанию:

SELECT AuthorName FROM AuthorsTBL;

После WHERE можно указывать различные условия. Например, я хочу выбрать все поля, в строках которых автором является Agatha Christie, команда для этого:

SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Теперь сделаем вставку (INSERT) записей в таблицу BooksTBL, мы будем использовать соответствующий AuthorID автора для каждой книги. Значение 1 в BookIsAvailable говорит о наличии книги, а 0 – об её отсутствии:

INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);

Посмотрим содержимое таблицы BooksTBL:

SELECT * FROM BooksTBL;

Мы допустили ошибку, у книги The Alchemist должна быть цена 22.75. Для изменения данных в таблице используется команда UPDATE. Её общий синтаксис:

UPDATE таблица SET столбец=новое значение WHERE условие;

Для того, чтобы книги, у которой BookID равен 6 присвоить столбцу BookPrice новое значение 22.75 выполним следующую команду:

UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;

Проверим:

SELECT * FROM BooksTBL WHERE BookID=6;

Как можно убедиться, данные изменились:

При желании удалить запись (строку), можно воспользоваться командой DELETE. Синтаксис этой команды:

DELETE FROM таблица WHERE условие

Например, команда для удаления из таблицы BooksTBL строки, у которой значение поля столбца равно 6:

DELETE FROM BooksTBL WHERE BookID=6;

Не забывайте с командами UPDATE и DELETE использовать условие WHERE, поскольку без него можно удалить все строки в таблице или изменить значение столбца сразу для всех записей.

Объединение вывода из нескольких таблиц

Если вы хотите объединить два (или более) полей, в том числе разных таблиц, вы можете использовать оператор CONCAT. Например, допустим мы хотим получить результат, состоящий из полей имени книги и автора, в виде “The Alchemist (Paulo Coelho)” и другой клонки с ценой.

Команду можно прочитать как SELECT (выбрать) поле1, поле2, поле3 FROM (из) таблицы1 JOIN (объединённой с) таблицей2 ON (по общим полям) таблица1.поле = таблица2.поле):

SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Как мы видим, CONCAT позволяет объединить несколько строковых выражений, разделённых запятой. Также для представления результата объединения мы выбрали псевдоним Description.

Ещё обратите внимание на новый синтаксис обращения к данным в виде имя_таблицы.имя_колонки (через точку)

Создание пользователя для доступа к безе данных

Использование root для выполнения операций языка управления данными (DML) в базе данных – это плохая идея. Чтобы избежать это, мы можем создать новый пользовательский аккаунт MariaDB (мы назовём его bookstoreuser) и назначим необходимые разрешения для BookstoreDB:

CREATE USER bookstoreuser@localhost IDENTIFIED BY 'ЗдесьВашПароль';
GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
FLUSH PRIVILEGES;

Имея выделенных, отдельных пользователей для каждой базы данных предотвратит вред для всех баз данных от компрометации одного аккаунта.

Дополнительные подсказки MySQL

Для очистки приглашения MariaDB, можно воспользоваться сочетанием клавиш Ctrl+l или набрать следующую команду и нажать Enter:

\! clear

Если вы ещё не догадались, последовательность символов \! отправляет последующую команду в шелл Linux (а не в СУБД) и выводят их результат на экран. После \! можно использовать любые команды Bash.

MariaDB [BookstoreDB]> \! pwd
/home/mial
MariaDB [BookstoreDB]> \! ls -l
итого 48
drwxr-xr-x 7 mial mial 4096 дек 25 09:13 bin
-rw-r--r-- 1 mial mial 8980 дек 22 08:40 examples.desktop
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Видео
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Документы
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Загрузки
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Изображения
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Музыка
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Общедоступные
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Рабочий стол
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Шаблоны

Для анализа конфигурации данной таблицы сделайте:

SHOW COLUMNS IN [имя таблицы];

Например,

SHOW COLUMNS IN BooksTBL;

Для SHOW COLUMNS IN можно использовать сокращение DESC:

DESC BooksTBL;

Быстрая проверка обнаружила, что поле BookIsAvailable может иметь значение NULL. Поскольку мы не хотим разрешать это, мы изменим таблицу командой ALTER:

ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

При повторной проверке теперь YES на пересечении BookIsAvailable и Null смениться на NO.

Вход под другим пользователем

Как вы помните, мы создали пользователя bookstoreuser. Давайте зайдём в MariaDB как этот пользователь. Для завершения сессии введите

exit

или нажмите Ctrl + d

mysql –u имя_пользователя -p

В нашем случае:

mysql -u bookstoreuser -p

После ключа -p можно сразу указать пароль. Между ключами -u и -p и следующими за ними именем пользователя и паролем необязательно ставить пробел.

Т.е. можно так:

mysql -ubookstoreuser -p

Показ всех баз данных

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

SHOW DATABASES;
# ИЛИ
SHOW SCHEMAS;

У пользователя bookstoreuser имеются привилегии на просмотр только одной базы данных – BookstoreDB, т.е. другие базы данных, размещённые на сервере, он не может увидеть.

Как показать все таблицы в базе данных

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

SHOW TABLES

Также можно непосредственно указать интересующую базу данных после FROM или IN. Например, я хочу увидеть таблицы в базе данных db_softocracy.ru, тогда я выполняю:

SHOW TABLES IN `db_softocracy.ru`;

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

База данных удаляется командой DROP DATABASE:

DROP DATABASE имя_БД;

Например:

DROP DATABASE BookstoreDB;

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

DROP DATABASE IF EXISTS имя_БД;

Например:

DROP DATABASE IF EXISTS BookstoreDB;

Если БД существует, то она будет удалена, если она отсутствует, то данная команда не вызовет ошибку.

Заключение

В этой статье мы разобрались, как выполнять DML операции и как создать базу данных, таблицы, отдельного пользователя базы данных в MariaDB. Дополнительно, мы рассмотрели несколько подсказок, которые могут сделать вашу жизнь системного администратора / администратора базы данных проще.

Рекомендуемые статьи:

1 Комментарий

  1. starper

    Отличный материал как раз для таких, как я 🙂

    Когда уяснил, с какого конца браться за лопату, то можно уже копать глубже.

    Спасибо.

Оставить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *