MySQL: основы создания таблиц

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

В этом тексте мы рассмотрим основы работы с MySQL — одной из самых популярных СУБД. Создадим виртуальный сервер, установим СУБД, поработаем с типами данных в MySQL изучим популярные команды манипуляций с таблицами и на примере покажем принцип работы с данными в MySQL.

Что такое MySQL

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

Со временем SQL стал незаменим для взаимодействия с реляционными базами данных (в которых информация организована в таблицы). Но есть и СУБД, в которых используется другой язык программирования. Например, Python или JavaScript.

Поскольку SQL наиболее распространенный язык запросов, есть множество СУБД, поддерживающих его. Среди них можно выделить PostgreSQL, MySQL, Microsoft SQL Server и SQLite. Особой популярностью пользуются PostgreSQL и MySQL. Мы предметно сосредоточимся только на второй.

GUI vs CLI

Работать с MySQL можно как через графический (GUI), так и через текстовый (CLI) интерфейс. Предпочтительно работать через командную строку по нескольким причинам:

  1. Эффективность. Использование командной строки ускоряет выполнение всех операций, ведь ресурсы не тратятся на функционирование графического интерфейса.
  2. Возможность автоматизации. С помощью командной строки можно писать сценарии для автоматизации рутинных процессов.
  3. Удаленный доступ. Через командную строку можно быстро подключиться к удаленным MySQL-серверам через защищенный протокол (например, SSH).
  4. Гибкость. Командная строка предлагает более широкий набор команд и возможностей для выполнения сложных запросов и операций, которые могут быть ограничены в графическом интерфейсе.
  5. Прозрачность операций и легкая интеграция. В командной строке четко отображается все, что происходит в системе. Так легче отслеживать и анализировать любые действия. Кроме этого, с помощью командной строки можно легко интегрировать другие инструменты и системы.

Таким образом, управление MySQL через CLI предпочтительнее благодаря возможностям создания сценариев, экономии ресурсов, гибкости и автоматизации многих задач. Поэтому мы будем рассматривать управление СУБД через CLI. 

Переходим к настройке!

Устанавливаем MySQL и проверяем работу

MySQL существует в разных версиях, но бесплатная среди них только одна — Community Edition. Также у MySQL есть популярный аналог MariaDB, который используется в LEMP стеках. О том, как установить все компоненты LEMP на Ubuntu вы можете прочитать в нашем материале из раздела FAQ. У нас также есть подробный материал и по отдельной установке только MySQL, но ниже мы разберем основные шаги.

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

  • CPU: минимум 2 ядра (если планируются большие нагрузки, то лучше заказать 4 ядра)

  • RAM: минимум 2 GB (для средних и больших баз данных 4 GB)

  • SSD: минимум 20 GB (подойдет для хранения небольших объемов данных)

Мы создадим ВМ именно на такой конфигурации, а ОС выберем Ubuntu 22.04. Рекомендуем включить резервное копирование и подключаться по SSH. Также уместно настроить файрвол и использовать инструменты для отслеживания загрузки сервера.

После того, как ВМ будет готова, можно к ней подключаться и приступать к установке MySQL. Сперва обновим пакеты до последних доступных версий:

sudo apt update

Далее MySQL устанавливается и автоматически запускается следующим образом:

sudo apt install mysql-server

Проверяем статус сервиса командой sudo systemctl status mysql. Вот что увидим в ответе:

  • Статус загрузки (Loaded). В нашем случае указано, что служба загружена и настроена на автоматический запуск. Помимо этого могут быть следующие сообщения:
    • not-found: Служба не найдена, возможно, она не установлена
    • disabled: Служба загружена, но не активирована для автоматического запуска
    • static: Служба не предназначена для прямого управления запуском, она может вызываться другими службами
    • masked: Служба заблокирована и не может быть запущена
  • Информация о запуске и состоянии службы (Active)
  • Идентификатор главного процесса MySQL (Main PID) 
  • Статус работы сервера (Status)
  • Количество активных задач и их лимит (Tasks)
  • Объем используемой памяти (Memory)
  • Общее время использования процессора (CPU)
  • Группа контроля ресурсов для службы (CGroup)

В выводе также указано, когда служба была запущена в системе.

Отдельно стоит рассказать про строку CGroup. Так называется механизм в ядре Linux, позволяющий организовать процессы в группы и управлять ресурсами, которые они могут использовать. Он обеспечивает ограничение и мониторинг использования CPU, памяти, I/O и других ресурсов для каждой группы процессов.

В контексте MySQL поле CGroup отображает, к какой группе контроля ресурсов принадлежит служба. Это позволяет системе управлять расходом ресурсов, предотвращая ситуации, когда одна служба может исчерпать все доступные ресурсы и повлиять на работу других.

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

Итак, после того как мы установили MySQL на Ubuntu и проверили, что всё работает, можем приступать к работе с таблицами!

Повышаем безопасность

Кроме стандартных инструментов безопасности, о которых мы упомянули выше (резервное копирование, SSH, файрвол) у MySQL есть полезный инструмент в виде интерактивного скрипта. 

Запустить его можно так:

sudo mysql_secure_installation

Далее скрипт в несколько шагов настроит дополнительную защиту. Вам лишь нужно принимать или отклонять потенциальные изменения. Мы, конечно же, рекомендуем все их принять (нажать y).

Разберем скрипт пошагово.

  1. Сначала нужно установить плагин, который проверяет пароли. Можно выбрать соответствующий уровень сложности: low, medium и strong. 

  2. На следующем этапе стоит удалить учетные записи без имени пользователя и пароля. С их помощью можно входить в систему анонимно, что лучше исключить.

  3. Третьим шагом запрещаем заходить через сеть root-пользователю. Только локально.

  4. Теперь удаляем тестовые базы данных, потому что оставлять их в продакшн среде небезопасно.

  5. Осталось только перезагрузить привилегии.

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

Создаем и редактируем таблицы

Все операции в MySQL проводятся внутри оболочки. Входим в нее командой sudo mysql. Появится приветствие системы, а начало строки изменится на mysql>.

Сначала стоит вывести перечень уже имеющихся БД. Достаточно ввести следующую команду и в конце поставить символ ‘;’. Его можно написать как в одной строке с командой, так и на следующей:

SHOW DATABASES;

В ответ мы получим список всех доступных на сервере БД с учетом прав доступа пользователя, который выполняет команду. Базы данных отображаются в виде таблицы с одним столбцом, где указаны их имена. Первыми указываются системные БД (information_schema, mysql, performance_schema), а за ними идут уже созданные пользователями. 

В MySQL всегда есть БД, которая используется по умолчанию. Соответственно все команды будут применяться именно к ней. Для смены БД по умолчанию есть отдельная команда.

Create Database test;
Use test

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

Теперь все новые таблицы будут появляться в БД test. Для создания таблиц используется команда CREATE TABLE. Нужно придумать понятные имена для таблицы и столбцов, а также указать тип данных. 

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

CREATE TABLE books
(
    id INT,

    title VARCHAR(255),

    author VARCHAR(100),

    publication_year INT,

    genre VARCHAR(50),

    copies INT

  );

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

Ограничения в MySQL

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

NOT NULL

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

CREATE TABLE employees 
(
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL
);

UNIQUE  

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

CREATE TABLE users
(
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

PRIMARY KEY  

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

CREATE TABLE products 
(
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

FOREIGN KEY  

Используется для связывания таблиц, чтобы невозможно было добавить несоответствующие значения или случайно удалить связанные.

CREATE TABLE users 
(
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders 
(
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

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

Выше мы создали две таблицы — users и orders. Во второй мы назначили внешний ключ полю user_id (FOREIGN KEY) и связали его со значениями поля id в таблице users (REFERENCES).

Так мы связали заказы с существующими пользователями.

CHECK

Позволяет задать критерии, которым должны соответствовать указываемые данные. Например, что в поле “возраст” значение не может быть меньше 0.

CREATE TABLE persons 
(
    id INT PRIMARY KEY,
    age INT CHECK (age >= 0)
);

DEFAULT  

Устанавливает стандартное значение для поля. Оно будет появляться в том случае, если при заполнении таблицы не будет указано иного значения. Вот таким образом можно задать по умолчанию статус "в ожидании":

CREATE TABLE tasks 
(
   id INT PRIMARY KEY,
   status VARCHAR(20) DEFAULT 'pending'
);

Обновляем таблицу

Теперь сделаем процесс заполнения нашей таблицы более системным.

CREATE TABLE books 
(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100) NOT NULL,
    publication_year INT NOT NULL CHECK (publication_year > 0),
    genre VARCHAR(50) DEFAULT 'Unknown',
    copies INT NOT NULL DEFAULT 1 CHECK (copies >= 0)
);

Разберем, что мы указали.

  1. id. Здесь будем указывать уникальный идентификатор каждой записи. Тип данных укажем INT. Следом укажем атрибут AUTO_INCREMENT, который автоматически увеличивает значение с каждой новой записью. Изначально он равен единице. Уникальный статус обеспечим благодаря атрибуту PRIMARY KEY.

  2. title. В этом столбце будем хранить название книги. Для этого мы указываем тип данных VARCHAR (хранение строковых значений переменной длины). Также ограничиваем длину в 255 символов и устанавливаем обязательность заполнения этого поля.

  3. author. Здесь мы указываем имя автора, поэтому нам также нужен тип данных VARCHAR. Максимальную длину ограничим 100 символами установим обязательность заполнения.

  4. publication_year. Столбец для хранения года издания книг. Здесь будем хранить целые числа, а значит указываем тип данных INT. Это поле также не может быть пустым, поэтому добавляем NOT NULL. Кроме этого, с помощью ограничения CHECK указываем, что значение должно быть больше или равно нулю. 

  5. genre. Указываем жанр книги. Ограничили ввод информации в 50 символов. Не обязательно заполнять это поле — в таком случае в нем будет указано Unknown. 

  6. copies. Здесь указываем количество экземпляров книги, следовательно ставим INT. Оно обязательно для заполнения и по умолчанию равно 1.

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

Добавляем данные

Пока наша новая таблица пустая. Добавим информацию по нескольким книгам в нее. Для этого используем команду INSERT INTO. Достаточно указать название таблицы и нужные значения, но также часто указывают список столбцов, куда вставить новые данные. Эту команду можно разбить на две части. После первой (INSERT INTO) указывается название таблицы и опционально список столбцов. После второй (VALUES) указываются значения, которые нужно добавить. Главное, отделять запятыми каждую новую строку.

Вот как будет выглядеть добавление трех новых книг в нашу таблицу:

INSERT INTO books (title, author, publication_year, genre, copies)
VALUES
('Бесконечная шутка', 'Дэвид Фостер Уоллес', 1996, 'Сатира', 5),
('100 лет одиночества', 'Габриэль Гарсиа Маркес', 1967, 'Магический реализм', 3),
('Убик', 'Филип Дик', 1969, 'Научная фантастика', 2);

После этой команды программа укажет вам, сколько строк записалось, сколько из них было дубликатов и сколько произошло ошибок. Проверить нашу таблицу можно командой select * from books;.

Редактируем таблицу

Добавляем и удаляем столбцы

Работать со структурой таблицы (изменить тип данных столбца, добавить новые столбцы, добавить ограничения и т.д.) помогает команда ALTER TABLE. Например, мы можем добавить новый столбец с помощью опции ADD внутри оператора ALTER TABLE. Сначала указываем имя таблицы, а затем после ADD прописываем название для столбца и необходимые ограничения. Для примера, добавим столбец, в котором будем указывать количество страниц в книге.

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

Удаляются ненужные столбцы также внутри оператора ALTER TABLE с помощью опции DROP COLUMN:

ALTER TABLE books

DROP COLUMN page;
Теперь, чтобы обновить новый столбец и указать количество страниц каждой книги, нужно воспользоваться командой UPDATE.

Обновляем данные

На первый взгляд команда UPDATE похожа на INSERT INTO. Выделим ограничения, чтобы не произошло путаницы.

С помощью INSERT INTO мы добавляем новые строки в таблицу и сразу же указываем нужные значения для вставки. Предположим, есть необязательные для заполнения поля и мы не указали значения для них в новой строке. Тогда строка добавится в таблицу, а в необязательном поле будет нулевое значение (NULL). Обновить его можно командой UPDATE, которая как раз предназначена для редактирования существующих полей и значений.

Резюмируем:

  • INSERT INTO используется для добавления новых записей (строк) в таблицу. Если при добавлении не указаны уникальные поля, которые уже существуют, может произойти ошибка (например, нарушение уникального ограничения).

  • UPDATE используется для изменения уже существующих записей. Данные изменяются согласно указанному условию. Без него обновляются все строки в таблице.

В общем случае синтаксис команды будет выглядеть вот таким образом:

UPDATE table_name 
SET column_name = new_value 
WHERE condition;

Однако, если мы пойдем этим путем, нам придется три раза вводить эту команду, чтобы обновить значения во всех полях таблицы. Чтобы вставить разные значения в разные поля столбца за одну команду используется оператор CASE внутри оператора UPDATE. Воспользуемся этим способом:

UPDATE books
SET page = CASE
    WHEN title = 'Бесконечная шутка' THEN 1279
    WHEN title = '100 лет одиночества' THEN 480
    WHEN title = 'Убик' THEN 320
END
WHERE title IN ('Бесконечная шутка', '100 лет одиночества', 'Убик');

Проверим, что все данные внесены корректно.

Если мы внимательно посмотрим на синтаксис команды, то может возникнуть вопрос — зачем повторно указывать строки для внесения изменений через WHERE в последней строке, если мы и так уже предметно указали на каждую строку с помощью WHEN. Дело в том, что последняя строка с WHERE помогает нам избежать нежелательных изменений в других записях. В нашем случае таких строк нет. Однако, если бы у нас были другие строки с данными в page, а мы бы решили обновить только поля в трех первых строках и явно не указали на них, то данные в этих полях обновились, а в других обнулились (ведь мы не указали, какие значения в них должны быть).

Удаляем данные и таблицу

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

Если нам нужно удалить все записи из таблицы, стоит воспользоваться командой DELETE FROM. С помощью условия WHERE мы можем удалить конкретную строку. Давайте попробуем это сделать:

DELETE FROM books WHERE id = 3;

Теперь в нашей таблице осталось только две строки:

Важно отметить, что теперь, если мы добавим новые строки, id будет присваиваться с 4, а не с 3, т.к. AUTO_INCREMENT накапливается и не откатывается даже при удалении строк. Если мы хотим сбросить AUTO_INCREMENT, то стоит воспользоваться командой TRUNCATE TABLE *имя таблицы* — она полностью очищает таблицу и обнуляет AUTO_INCREMENT. 

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

***

Итак, мы установили MySQL на VPS от 1cloud, настроили безопасность СУБД, создали первую таблицу и научились базово работать с данными в ней, а также менять саму таблицу. Конечно же, это лишь базовые возможности MySQL, но уже их хватить для работы с небольшой таблицей данных. 

В Панели 1cloud вы можете за несколько минут создать виртуальный сервер под любые задачи, не только для ведения базы данных. Вы даже можете связать сетью несколько виртуальных серверов и создать виртуальный ЦОД. Регистрируйтесь в 1cloud и пробуйте все возможности Панели управления в рамках тестового периода.

Зарегистрироваться