Изучите MySQL/MariaDB для начинающих. Часть 1.

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

Предполагается, что вы ранее 1) установили необходимые пакеты в свою систему Linux и 2) выполнили mysql_secure_installation для повышения безопасности сервера базы данных. . Если нет, следуйте инструкциям ниже для установки сервера MySQL/MariaDB.

Установите последнюю версию базы данных MySQL в системах Linux
Установите последнюю версию базы данных MariaDB в системах Linux

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

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

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

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

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

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

[root@TecMint ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>

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

Знакомство с типами данных MariaDB

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

Наиболее распространенными типами данных в MariaDB являются следующие (полный список можно найти в официальной онлайн-документации MariaDB):

Числовой:

BOOLEAN считает 0 ложным, а любые другие значения истинными.
TINYINT, если он используется с SIGNED, охватывает диапазон от -128 до 127, тогда как диапазон UNSIGNED составляет от 0 до 255.
SMALLINT при использовании со знаком SIGNED охватывает диапазон от -32768 до 32767. Диапазон UNSIGNED составляет от 0 до 65535.
INT, если используется с UNSIGNED, охватывает диапазон от 0 до 4294967295 и от -2147483648 до 2147483647 в противном случае.

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

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

Нить :

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

Дата и время:

DATE представляет дату в формате ГГГГ-ММ-ДД.
TIME представляет время в формате ЧЧ:ММ:СС.сс (часы, минуты, секунды и миллисекунды).
DATETIME — это комбинация DATE и TIME в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС.
TIMESTAMP используется для определения момента добавления или обновления строки.

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

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

Создание таблиц с первичными и внешними ключами

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

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

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

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

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> 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)
);

MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> 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)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]>

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

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

Сначала мы заполним таблицу AuthorsTBL. Почему? Потому что нам нужно иметь значения для AuthorID, прежде чем вставлять записи в BooksTBL.

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

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

После этого мы выберем все записи из AuthorsTBL. Помните, что нам понадобится AuthorID для каждой записи, чтобы создать запрос INSERT для BooksTBL.

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

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Альтернативно, вы можете выбрать все записи одновременно:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>

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

MariaDB [BookstoreDB]> 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);

MariaDB [BookstoreDB]> 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);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

На этом этапе мы выполним SELECT, чтобы просмотреть записи в BooksTBL. Затем давайте ОБНОВИМ цену на «Алхимика» Пауло Коэльо и снова ВЫБЕРИМ эту конкретную запись.

Обратите внимание, что поле BookLastUpdated теперь показывает другое значение. Как мы объясняли ранее, поле TIMESTAMP показывает значение, когда запись была вставлена или в последний раз изменена.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]>

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

Для этого мы будем использовать оператор DELETE следующим образом:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

Как и в случае с UPDATE, рекомендуется сначала выполнить SELECT, чтобы просмотреть записи, на которые потенциально может повлиять . УДАЛИТЬ.

Кроме того, не забудьте добавить предложение WHERE и условие (BookID=6) для выбора конкретной записи, которую нужно удалить. В противном случае вы рискуете удалить все строки таблицы!

Если вы хотите объединить два (или более) поля, вы можете использовать оператор CONCAT. Например, предположим, что мы хотим вернуть набор результатов, состоящий из одного поля с названием книги и автора в виде «Алхимик (Пауло Коэльо)» и другого столбца с ценой.

Для этого потребуется JOIN между AuthorsTBL и BooksTBL в общем поле, совместно используемом обеими таблицами (AuthorID):

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

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

Вывод приведенного выше запроса показан на изображении ниже:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

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

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

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

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

Дополнительные советы по MySQL

Чтобы очистить приглашение MariaDB, введите следующую команду и нажмите Enter:

MariaDB [BookstoreDB]> \! clear

Чтобы проверить конфигурацию данной таблицы, выполните:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

Например,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

Быстрая проверка показывает, что поле BookIsAvailable допускает значения NULL. Поскольку мы не хотим этого допускать, мы ИЗМЕНИМ таблицу следующим образом:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Не стесняйтесь снова отобразить столбцы — выделенное ДА на изображении выше теперь должно быть НЕТ).

Наконец, чтобы просмотреть все базы данных на вашем сервере, выполните:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;

[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

На следующем изображении показан результат выполнения приведенной выше команды после доступа к приглашению MariaDB от имени bookstoreuser (обратите внимание, что эта учетная запись не может «видеть» какие-либо базы данных, кроме BookstoreDB и information_schema (доступно всем пользователям):

Краткое содержание

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

Администрирование базы данных MySQL, часть – 1
Администрирование базы данных MySQL. Часть 2.
Настройка и оптимизация производительности MySQL – Часть 3

Если у вас есть какие-либо вопросы по поводу этой статьи, не стесняйтесь, дайте нам знать! Не стесняйтесь использовать форму комментариев ниже, чтобы связаться с нами.