Улучшите свои знания о базах данных с помощью этой шпаргалки по MariaDB и MySQL.

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

Когда вы пишете приложение или настраиваете его для сервера, в конечном итоге вам потребуется хранить постоянную информацию. Иногда подойдет файл конфигурации, например файл INI или YAML . В других случаях лучше использовать собственный формат файла, разработанный в формате XML, JSON или аналогичном.

Но иногда вам нужно что-то, что может проверять вводимые данные, быстро искать информацию, устанавливать связи между связанными данными и в целом умело управлять работой ваших пользователей. Именно для этого предназначена база данных, и MariaDB (вилка MySQL, созданная некоторыми из ее первоначальных разработчиков) является отличным вариантом. В этой статье я использую MariaDB, но информация в равной степени применима и к MySQL.

Обычно взаимодействие с базой данных осуществляется с помощью языков программирования. По этой причине существуют библиотеки SQL для Java, Python, Lua, PHP, Ruby, C++ и многих других. Однако прежде чем использовать эти библиотеки, полезно понять, что происходит с ядром базы данных и почему ваш выбор базы данных имеет важное значение. В этой статье представлены MariaDB и команда mysql, которые познакомят вас с основами обработки данных в базе данных.

Если у вас еще нет MariaDB, следуйте инструкциям в моей статье об установке MariaDB в Linux. Если вы не используете Linux, используйте инструкции, представленные на странице загрузки MariaDB.

Взаимодействовать с MariaDB

Вы можете взаимодействовать с MariaDB с помощью команды mysql. Сначала убедитесь, что ваш сервер запущен и работает, с помощью подкоманды ping, введя свой пароль MariaDB при появлении соответствующего запроса:

$ mysqladmin -u root -p ping
Enter password:
mysqld is alive

Чтобы упростить изучение SQL, откройте интерактивный сеанс MariaDB:

$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.
Commands end with ; or \g.
[...]
Type 'help;' or '\h' for help.
Type '\c' to clear the current input statement.

MariaDB [(none)]>

При этом вы попадаете в подоболочку MariaDB, и ваше приглашение теперь является приглашением MariaDB. Ваши обычные команды Bash здесь не работают. Вы должны использовать команды MariaDB. Чтобы просмотреть список команд MariaDB, введите help (или просто ?). Это административные команды для вашей оболочки MariaDB, поэтому они полезны для настройки вашей оболочки, но они не являются частью языка SQL.

Изучите основы SQL

Язык структурированных запросов (SQL) назван в честь того, что он предоставляет: метод запроса о содержимом базы данных с помощью предсказуемого и согласованного синтаксиса для получения полезных результатов. SQL во многом похож на обычное английское предложение, хотя и немного роботизированное. Например, если вы вошли на сервер базы данных и вам нужно понять, с чем вам придется работать, введите SHOW DATABASES; и нажмите Enter, чтобы увидеть результаты.

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.000 sec)

Это показывает, что присутствуют четыре базы данных: information_schema, mysql, Performance_schema и test. Чтобы отправлять запросы к базе данных, вы должны выбрать, какую базу данных вы хотите использовать MariaDB. Это делается с помощью команды MariaDB use. После того, как вы выберете базу данных, приглашение MariaDB изменится, отражая активную базу данных.

MariaDB [(none)]> use test;
MariaDB [(test)]> 

Показать таблицы базы данных

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

Чтобы просмотреть таблицы, доступные в базе данных (их можно рассматривать как вкладки в многостраничной электронной таблице), снова используйте ключевое слово SQL SHOW:

MariaDB [(test)]> SHOW TABLES;
empty set

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

MariaDB [(test)]> use mysql;
MariaDB [(mysql)]> SHOW TABLES;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
[...]
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.000 sec)

В этой базе данных гораздо больше таблиц! База данных mysql — это база данных управления системой для этого экземпляра MariaDB. Он содержит важные данные, включая всю структуру пользователей для управления привилегиями базы данных. Это важная база данных, и вам не всегда нужно взаимодействовать с ней напрямую, но ею нередко манипулируют в сценариях SQL. При изучении MariaDB также полезно понимать базу данных mysql, поскольку она может помочь продемонстрировать некоторые основные команды SQL.

Осмотрите стол

Последняя таблица, указанная в базе данных mysql этого экземпляра, называется user. Эта таблица содержит данные о пользователях, которым разрешен доступ к базе данных. Сейчас есть только пользователь root, но вы можете добавлять других пользователей с различными привилегиями, чтобы контролировать, может ли каждый пользователь просматривать, обновлять или создавать данные. Чтобы получить представление обо всех атрибутах, которые может иметь пользователь MariaDB, вы можете просмотреть заголовки столбцов в таблице:

> SHOW COLUMNS IN user;
MariaDB [mysql]> SHOW columns IN user;
+-------------+---------------+------+-----+----------+
| Field       | Type          | Null | Key | Default  |
+-------------+---------------+------+-----+----------+
| Host        | char(60)      | NO   | PRI |          |
| User        | char(80)      | NO   | PRI |          |
| Password    | char(41)      | NO   |     |          |
| Select_priv | enum('N','Y') | NO   |     | N        |
| Insert_priv | enum('N','Y') | NO   |     | N        |
| Update_priv | enum('N','Y') | NO   |     | N        |
| Delete_priv | enum('N','Y') | NO   |     | N        |
| Create_priv | enum('N','Y') | NO   |     | N        |
| Drop_priv   | enum('N','Y') | NO   |     | N        |
[...]
47 rows in set (0.001 sec)

Создать нового пользователя

Если вам нужна помощь человека для администрирования базы данных или вы настраиваете базу данных для использования на компьютере (например, при установке WordPress, Drupal или Joomla), обычно требуется дополнительная учетная запись пользователя в MariaDB. . Вы можете создать пользователя MariaDB, добавив его в таблицу user в базе данных mysql, или вы можете использовать ключевое слово SQL CREATE для запроса MariaDB сделает это за вас. Последний имеет некоторые вспомогательные функции, поэтому вам не придется генерировать всю информацию вручную:

> CREATE USER 'tux'@'localhost' IDENTIFIED BY 'really_secure_password';

Просмотр полей таблицы

Вы можете просмотреть поля и значения в таблице базы данных с помощью ключевого слова SELECT. В этом примере вы создали пользователя с именем tux, поэтому выберите столбцы в таблице user:

> SELECT user,host FROM user;
+------+------------+
| user | host       |
+------+------------+
| root | localhost  |
[...]
| tux  | localhost  |
+------+------------+
7 rows in set (0.000 sec)

Предоставить права пользователю

Просматривая список столбцов в таблице user, вы можете узнать статус пользователя. Например, новый пользователь tux не имеет разрешения на какие-либо действия с базой данных. Используя оператор WHERE, вы можете просмотреть только запись для tux:

> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux  | N           | N           | N           |
+------+-------------+-------------+-------------+

Используйте команду GRANT, чтобы изменить права пользователя:

> GRANT SELECT on *.* TO 'tux'@'localhost';
> FLUSH PRIVILEGES;

Подтвердите изменение:

> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux  | Y           | N           | N           |
+------+-------------+-------------+-------------+

Пользователь tux теперь имеет права выбирать записи из всех таблиц.

Создайте собственную базу данных

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

Создайте базу данных MariaDB.

Возможно, вы уже догадались, как создать новую базу данных в MariaDB. Это очень похоже на создание нового пользователя:

> CREATE DATABASE example;
Query OK, 1 row affected (0.000 sec)
> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
[...]

Сделайте эту новую базу данных активной с помощью команды use:

> use example;

Создать таблицу

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

Вот простая таблица для описания набора пользователей:

> CREATE table IF NOT EXISTS member (
    -> id INT auto_increment PRIMARY KEY,
    -> name varchar(128) NOT NULL,
    -> startdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.030 sec)

Эта таблица предоставляет уникальный идентификатор каждой строке с помощью функции автоматического приращения. Он содержит поле для имени пользователя, которое не может быть пустым (или null), и генерирует метку времени при создании записи.

Заполните эту таблицу некоторыми примерами данных, используя ключевое слово SQL INSERT:

> INSERT INTO member (name) VALUES ('Alice');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Bob');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Carol');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('David');
Query OK, 1 row affected (0.011 sec)

Проверьте данные в таблице:

> SELECT * FROM member;
+----+-------+---------------------+
| id | name  | startdate           |
+----+-------+---------------------+
|  1 | Alice | 2020-10-03 15:25:06 |
|  2 | Bob   | 2020-10-03 15:26:43 |
|  3 | Carol | 2020-10-03 15:26:46 |
|  4 | David | 2020-10-03 15:26:51 |
+----+-------+---------------------+
4 rows in set (0.000 sec)

Добавить несколько строк одновременно

Теперь создайте вторую таблицу:

> CREATE table IF NOT EXISTS linux (
    -> id INT auto_increment PRIMARY KEY,
    -> distro varchar(128) NOT NULL,
Query OK, 0 rows affected (0.030 sec)

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

> INSERT INTO linux (distro)
 -> VALUES ('Slackware'), ('RHEL'),('Fedora'),('Debian');
Query OK, 4 rows affected (0.011 sec)
Records: 4  Duplicates: 0  Warnings: 0
> SELECT * FROM linux;
+----+-----------+
| id | distro    |
+----+-----------+
|  1 | Slackware |
|  2 | RHEL      |
|  3 | Fedora    |
|  4 | Debian    |
+----+-----------+

Создание связей между таблицами

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

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

Создайте новый столбец в первой таблице для представления значения во второй таблице:

> ALTER TABLE member ADD COLUMN (os INT);
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0
> DESCRIBE member;
DESCRIBE member;
+-----------+--------------+------+-----+---------+------+
| Field     | Type         | Null | Key | Default | Extra|
+-----------+--------------+------+-----+---------+------+
| id        | int(11)      | NO   | PRI | NULL    | auto_|
| name      | varchar(128) | NO   |     | NULL    |      |
| startdate | timestamp    | NO   |     | cur[...]|      |
| os        | int(11)      | YES  |     | NULL    |      |
+-----------+--------------+------+-----+---------+------+

Используя уникальные идентификаторы таблицы linux, назначьте распределение каждому участнику. Поскольку записи уже существуют, используйте ключевое слово SQL UPDATE, а не INSERT. В частности, вы хотите выбрать одну строку, а затем обновить значение одного столбца. Синтаксически это выражается немного наоборот: обновление происходит первым, а сопоставление выбора — последним:

> UPDATE member SET os=1 WHERE name='Alice';
Query OK, 1 row affected (0.007 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Повторите этот процесс для других имен в таблице member, чтобы заполнить ее данными. Для разнообразия назначьте три разных распределения по четырем рядам (удвоение по одному).

Объединение таблиц

Теперь, когда эти две таблицы связаны друг с другом, вы можете использовать SQL для отображения связанных данных. В базах данных существует множество видов объединений, и вы сможете опробовать их все, если освоите основы. Вот базовое соединение для сопоставления значений, найденных в поле os таблицы member, с полем id в linux таблица:

SELECT * FROM member JOIN linux ON member.os=linux.id;
+----+-------+---------------------+------+----+-----------+
| id | name  | startdate           | os   | id | distro    |
+----+-------+---------------------+------+----+-----------+
|  1 | Alice | 2020-10-03 15:25:06 |    1 |  1 | Slackware |
|  2 | Bob   | 2020-10-03 15:26:43 |    3 |  3 | Fedora    |
|  4 | David | 2020-10-03 15:26:51 |    3 |  3 | Fedora    |
|  3 | Carol | 2020-10-03 15:26:46 |    4 |  4 | Debian    |
+----+-------+---------------------+------+----+-----------+
4 rows in set (0.000 sec)

Поля os и id образуют объединение.

Вы можете представить, что в графическом приложении поле os может быть установлено с помощью раскрывающегося меню, значения для которого берутся из содержимого поля distro таблица linux. Используя отдельные таблицы для уникальных, но связанных наборов данных, вы обеспечиваете согласованность и достоверность данных, а благодаря SQL вы можете впоследствии динамически связать их.

Загрузите шпаргалку по MariaDB и MySQL.

MariaDB — это база данных корпоративного уровня. Он разработан и зарекомендовал себя как надежный, мощный и быстрый механизм базы данных. Изучение его — отличный шаг к использованию его для таких задач, как управление веб-приложениями или библиотеками языков программирования. В качестве краткого руководства при использовании MariaDB загрузите нашу шпаргалку по MariaDB и MySQL.