ОП.08 - 04 - Основные типы данных MySQL. Создание и удаление таблиц

Введение

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

Основные типы данных MySQL

MySQL поддерживает различные типы данных, которые можно разделить на несколько категорий: числовые, строковые, календарные и специальные. Рассмотрим их подробнее.

Числовые типы данных

Числовые данные в MySQL делятся на целые, вещественные и битовые. Каждый тип имеет свои особенности и ограничения.

Целые числа

Целые числа используются для хранения чисел без дробной части. Они могут быть как со знаком (положительные и отрицательные), так и без знака (UNSIGNED).

[!NOTE]
Используйте UNSIGNED, если вам не нужны отрицательные значения. Это увеличивает диапазон положительных чисел.

Пример использования:

-- Количество товара на складе
quantity TINYINT UNSIGNED

Здесь поле quantity может хранить значения от 0 до 255, и не может быть отрицательным, что достаточно для хранения небольших неотрицательных целых чисел.

Вещественные числа

Вещественные числа используются для хранения чисел с дробной частью. Они делятся на точные и приближённые.

Точные вещественные числа

• P — общее количество цифр (точность).
• S — количество цифр после запятой (масштаб).

Пример использования:

-- Хранение цены товара
price DECIMAL(10,2)

Этот столбец может хранить числа в диапазоне от -99999999.99 до 99999999.99.

[!WARNING]
Используйте DECIMAL для финансовых данных, где важна точность. Для других случаев можно использовать приближённые типы.

Приближённые числа

• FLOAT — одинарная точность (32 бита), примерно 7 значащих цифр.
• DOUBLE — двойная точность (64 бита), примерно 15 значащих цифр.

Пример использования:

-- Хранение координат GPS
latitude DOUBLE     -- широта
longitude DOUBLE    -- долгота

-- Хранение данных о физических параметрах
temperature FLOAT  -- температура

[!TIP]
Если вам нужна высокая точность, используйте DECIMAL. Для научных вычислений подойдут FLOAT и DOUBLE.

Битовые числа

Пример использования:

-- Хранение состояния единичного признака
is_active BIT(1)  -- 1 = активен, 0 = неактивен

-- Хранение состояния переключателей
switches BIT(3)

Этот столбец может хранить значения от 000 до 111.

[!NOTE]
Если назначаемое значение в колонке с типом BIT использует меньше M бит, то происходит дополнение нулями слева. Например, при попытке записать значение b'101' в BIT(6) храниться будет b'000101'.

Строковые типы данных

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

Фиксированная и переменная длина

Пример использования:

-- Хранение кода страны (всегда 2 символа)
country_code CHAR(2);

-- Хранение имени пользователя (переменная длина)
username VARCHAR(255);

Большие данные

Подтипы TEXT и BLOB:

• TINYTEXT/TINYBLOB: до 255 байт.
• MEDIUMTEXT/MEDIUMBLOB: до 16 МБ.
• LONGTEXT/LONGBLOB: до 4 ГБ.

Пример использования:

-- Хранение длинного текстового описания
description TEXT

-- Хранение изображения
image BLOB

[!NOTE]
Используйте TEXT для текстовых данных и BLOB для бинарных данных, таких как изображения.

Календарные типы данных

Календарные типы данных используются для хранения даты и времени.

Пример использования:

-- Хранение даты рождения пользователя
birthdate DATE

-- Хранение времени начала и/или окончания событий в пределах одного дня
start_time TIME,  -- время начала смены
end_time TIME     -- время окончания смены

-- Хранение даты и времени событий в прошлом и будущем
check_in_date DATETIME,   -- Дата/время заезда в гостиницу
check_out_date DATETIME,  -- Дата/время выезда из гостиницы

-- Отслеживание времени создания и обновления записи
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

[!TIP]
Параметр CURRENT_TIMESTAMP автоматически передаёт текущую метку времени с точностью до секунды и может быть использован как при создании так и при редактировании записи. Это полезно для записей которые могут часто меняться в процессе работы с БД.

[!WARNING]
Учтите, что TIMESTAMP ограничен диапазоном до 2038 года. Для более широкого диапазона используйте DATETIME.

Тип данных ENUM

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

Описание

• ENUM хранит одно значение из списка, указанного при создании таблицы.
• Значения хранятся в виде чисел (индексов), что делает этот тип эффективным с точки зрения производительности.
• Максимальное количество элементов в списке — 65535.

Пример использования:

status ENUM('pending', 'processing', 'shipped', 'cancelled') DEFAULT 'pending'

Объяснение:

• Поле status может принимать только одно из четырёх значений: 'pending', 'processing', 'shipped', или 'cancelled'.
• Если значение явно не указано, по умолчанию будет установлено 'pending'.

[!TIP]
Используйте ENUM, если заранее известен ограниченный набор значений. Однако будьте осторожны при изменении списка значений, так как это требует изменения структуры таблицы.

Тип данных SET

Тип данных SET похож на ENUM, но позволяет хранить несколько значений из предопределённого списка в одном поле.

Описание

• SET может содержать от 0 до 64 элементов.
• Каждое значение в списке представляется битом, что делает этот тип компактным.
• При вставке данных можно указывать несколько значений через запятую.

[!NOTE]
Используйте SET, если нужно хранить несколько атрибутов в одном поле. Однако помните, что работа с этим типом может быть менее интуитивной, чем с отдельными столбцами для каждого значения.

Пример использования:

permissions SET('read', 'write', 'execute') DEFAULT 'read'

Объяснение:

• Поле permissions может содержать одно или несколько значений из списка: 'read', 'write', 'execute'.
• По умолчанию установлено значение 'read'.

[!TIP]
Чтобы проверить, содержит ли поле определённое значение, можно использовать функцию FIND_IN_SET:

SELECT * FROM users WHERE FIND_IN_SET('write', permissions);

Тип данных NULL

NULL указывает, что значение может отсутствовать. Это не тип данных, а маркер отсутствия значения.

Пример использования:

-- Возраст может быть не указан
age INT NULL

[!TIP]
Используйте NOT NULL, если значение обязательно должно присутствовать.

Например:

email VARCHAR(255) NOT NULL

Создание и удаление таблиц

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

Создание таблицы

Перед созданием таблицы необходимо выбрать базу данных, в которую таблица будет записана. Это делается с помощью оператора USE:

USE имя_базы_данных;

Для создания таблицы используется оператор CREATE TABLE. Его базовый синтаксис имеет следующий вид:

CREATE TABLE [IF NOT EXISTS] имя_таблицы (
    колонка_1 тип_данных,
    [колонка_2 тип_данных,]
    [колонка_n тип_данных,]
    PRIMARY KEY (колонка_1)
);

Пример:

CREATE TABLE students (
    student_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    group_name VARCHAR(50),
    PRIMARY KEY (student_id)
);

[!NOTE]
В данном примере student_id является первичным ключом. Он автоматически увеличивается при добавлении новых записей благодаря параметру AUTO_INCREMENT.

Первичные ключи и их задание при создании таблицы

Первичный ключ (Primary Key, PK) — это поле (или группа полей), которое однозначно идентифицирует каждую запись в таблице. Значения первичного ключа всегда уникальны и не могут повторяться.

Простой первичный ключ

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

Пример:

CREATE TABLE orders (
    order_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id)
);

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

Составной первичный ключ

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

Пример:

CREATE TABLE order_details (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

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

Дополнительные параметры определения колонок

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

Автоматическая инкрементация

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

Пример использования:

user_id INT NOT NULL AUTO_INCREMENT

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

Параметр DEFAULT

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

Пример использования:

age INT NOT NULL DEFAULT 18

В этом примере, если возраст клиента не указан, то по умолчанию будет установлено значение 18.

[!NOTE]
Параметр DEFAULT нельзя использовать с типами данных BLOB, TEXT, GEOMETRY и JSON.

Параметр UNIQUE

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

Пример использования:

email VARCHAR(255) UNIQUE

В этом примере поле email не может содержать повторяющиеся значения, что гарантирует уникальность электронной почты для каждого клиента.

Другие действия с таблицами

Просмотр структуры таблицы

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

Пример:

DESCRIBE students;

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

• Field : Имя столбца.
• Type : Тип данных столбца.
• Null : Может ли столбец содержать NULL (YES или NO).
• Key : Указывает, является ли столбец ключом (PRI — первичный ключ).
• Default : Значение по умолчанию (если указано явно).
• Extra : Дополнительная информация (например, AUTO_INCREMENT).

Переименование таблиц

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

Пример:

RENAME TABLE old_table_name TO new_table_name;

Полное удаление данных

Для очистки таблицы (удаления всех данных, но сохранения структуры) используется команда TRUNCATE TABLE.

Пример:

TRUNCATE TABLE table_name;

Удаление таблицы

Для удаления таблицы используется команда DROP TABLE.

Пример:

DROP TABLE table_name;

Просмотр списка таблиц

Чтобы просмотреть список всех таблиц в текущей базе данных, используется команда SHOW TABLES. Она особенно полезна, если вы хотите быстро проверить, какие таблицы уже существуют.

Пример использования:

SHOW TABLES;

[!TIP]
Если вы работаете с большой базой данных, команда SHOW TABLES поможет вам быстро сориентироваться и избежать дублирования имен таблиц.

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

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

-- 1. Выбор базы данных
USE test_db;

-- 2. Создание таблицы
CREATE TABLE IF NOT EXISTS users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT DEFAULT 18,
    PRIMARY KEY (user_id)
);

-- 3. Просмотр списка таблиц
SHOW TABLES;

-- 4. Просмотр структуры таблицы
DESCRIBE users;

-- 5. Добавление данных
INSERT INTO users (username, email, age) VALUES
('john_doe', 'john@example.com', 25),
('jane_doe', 'jane@example.com', 30);

-- 6. Очистка таблицы
TRUNCATE TABLE users;

-- 7. Удаление таблицы
DROP TABLE IF EXISTS users;

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

1. Именование таблиц и столбцов:

   • Используйте осмысленные имена для таблиц и столбцов. Например, вместо tbl1 лучше использовать users или orders.
   • Избегайте пробелов и специальных символов в именах. Если нужно разделить слова, используйте нижнее подчеркивание (_), например, user_profile.

2. Первичные ключи:

   • Каждая таблица должна иметь первичный ключ (PRIMARY KEY). Это гарантирует уникальность каждой записи и упрощает работу с данными.
   • Для первичных ключей часто используется тип INT с атрибутом AUTO_INCREMENT.

3. Использование NOT NULL:

   • По умолчанию столбцы могут содержать значение NULL. Если данные в столбце обязательны, всегда добавляйте атрибут NOT NULL.

4. Значения по умолчанию:

   • Используйте параметр DEFAULT, чтобы задать значение по умолчанию для столбца. Это особенно полезно для столбцов, которые часто имеют одинаковые значения.

5. Удаление таблиц с осторожностью:

   • Команда DROP TABLE полностью удаляет таблицу и все её данные. Будьте осторожны при её использовании, особенно в рабочих базах данных.
   • Рекомендуется использовать конструкцию DROP TABLE IF EXISTS, чтобы избежать ошибок, если таблица не существует.

6. Очистка таблицы:

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

Заключение

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

Практические задания

Создайте таблицу orders для хранения информации о заказах в интернет-магазине. Таблица должна содержать следующие поля:

1. order_id

   • Первичный ключ.
   • Целое число (INT).
   • Автоматически инкрементируется (AUTO_INCREMENT).

2. client_id

   • Целое число (INT).
   • Должен быть объявлен как NOT NULL.
   • Используется для связи с таблицей клиентов (без явного указания внешнего ключа).

3. total_amount

   • Общая сумма заказа.
   • Вещественное число (DECIMAL(10, 2)), чтобы обеспечить точность до двух знаков после запятой.

4. created_at

   • Время создания заказа.
   • Используйте тип данных TIMESTAMP.
   • По умолчанию должно устанавливаться текущее время (DEFAULT CURRENT_TIMESTAMP).

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    client_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id)
);

Дополнительные вопросы для размышления:

• Какие ограничения следует добавить к полю total_amount, чтобы избежать отрицательных значений?
• Как можно изменить таблицу, если потребуется добавить поле для хранения статуса заказа (например, "в обработке", "доставлен", "отменён")?
• Объясните, почему для хранения временной метки лучше использовать TIMESTAMP, а не DATETIME.

Полезные ссылки

• Официальная документация MySQL
• Типы данных в MySQL