LabEx
ВходРегистрация

Структура таблиц и типы данных в MySQL

MySQLBeginner
Практиковаться сейчас

Введение

В этом практическом занятии (лабораторной работе) мы рассмотрим основы структур таблиц и типов данных в MySQL. Понимание того, как создавать, изменять и управлять таблицами, является важным навыком для любого, кто работает с базами данных. Мы узнаем о различных типах данных в MySQL, как выбирать подходящий тип данных для своих нужд и как выполнять основные операции с таблицами. К концу этого практического занятия вы получите практический опыт создания таблиц, определения столбцов с соответствующими типами данных и изменения структур таблиц.

Цели

После завершения этого практического занятия вы сможете:

  • Понимать основные типы данных в MySQL и когда их использовать
  • Создавать таблицы с соответствующими определениями столбцов
  • Изменять существующие структуры таблиц
  • Удалять таблицы, когда они больше не нужны
  • Просматривать и понимать метаданные таблиц

Понимание типов данных MySQL

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

Поскольку на этом этапе используются многие SQL - команды, мы рекомендуем использовать веб - терминал. Нажмите на вкладку "Terminal", чтобы открыть его; он работает так же, как и десктопный терминал.

Интерфейс веб - терминала MySQL

Начнем с подключения к MySQL:

sudo mysql -u root

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

CREATE DATABASE store;
USE store;

Рассмотрим основные категории типов данных в MySQL:

  1. Числовые типы:

    • INT: Для целых чисел
    • DECIMAL: Для точных десятичных чисел
    • FLOAT/DOUBLE: Для приблизительных десятичных чисел

  2. Строковые типы:

    • VARCHAR: Для строк переменной длины
    • CHAR: Для строк фиксированной длины
    • TEXT: Для длинного текста

  3. Типы даты и времени:

    • DATE: Для дат (ГГГГ-ММ-ДД)
    • TIME: Для времени (ЧЧ:ММ:СС)
    • DATETIME: Для даты и времени

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

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    weight FLOAT,
    in_stock BOOLEAN,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Разберем структуру этой таблицы:

  • id: Автоинкрементируемое целое число, которое служит в качестве первичного ключа
  • name: Строка переменной длины, которая не может быть NULL
  • price: Точное десятичное число с 10 цифрами в общей сложности и 2 десятичными знаками
  • description: Текстовое поле для более длинных описаний
  • weight: Число с плавающей точкой для приблизительных десятичных значений
  • in_stock: Булево поле (TRUE/FALSE)
  • created_at: Автоматически сохраняет временную метку создания
  • last_updated: Автоматически обновляется при изменении записи

Чтобы посмотреть структуру нашей таблицы:

DESCRIBE products;

Вы должны увидеть вывод, похожий на следующий:

+--------------+---------------+------+-----+---------------------+-------------------------------+
| Field        | Type          | Null | Key | Default             | Extra                         |
+--------------+---------------+------+-----+---------------------+-------------------------------+
| id           | int(11)       | NO   | PRI | NULL                | auto_increment                |
| name         | varchar(100)  | NO   |     | NULL                |                               |
| price        | decimal(10,2) | NO   |     | NULL                |                               |
| description  | text          | YES  |     | NULL                |                               |
| weight       | float         | YES  |     | NULL                |                               |
| in_stock     | tinyint(1)    | YES  |     | NULL                |                               |
| created_at   | datetime      | YES  |     | current_timestamp() |                               |
| last_updated | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+---------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

Создание таблиц с ограничениями

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

Создадим две связанные таблицы, чтобы понять отношения и ограничения:

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    sku VARCHAR(20) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Рассмотрим ограничения, которые мы использовали:

  1. PRIMARY KEY (Первичный ключ): Гарантирует уникальную идентификацию каждой записи
  2. UNIQUE (Уникальное значение): Предотвращает дублирование значений в столбце
  3. NOT NULL (Не NULL): Гарантирует, что столбец не может содержать значения NULL
  4. CHECK (Проверка): Проверяет данные перед их вставкой
  5. FOREIGN KEY (Внешний ключ): Гарантирует целостность ссылок между таблицами
  6. DEFAULT (Значение по умолчанию): Предоставляет значение по умолчанию, если оно не указано

Чтобы посмотреть структуру таблицы с ограничениями:

SHOW CREATE TABLE inventory_items;

Это покажет полный оператор CREATE TABLE, включая все ограничения:

MariaDB [store]> SHOW CREATE TABLE inventory_items;

<!-- Sample output -->

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `sku` varchar(20) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` >= 0),
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `inventory_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Изменение структуры таблицы

На этом этапе мы узнаем, как изменять существующие таблицы с помощью команд ALTER TABLE. Это распространенная задача, когда потребности в данных меняются со временем.

Изменим нашу таблицу products, добавив несколько новых столбцов и изменив существующие:

  1. Добавить новый столбец:
ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;

Эта команда добавляет новый столбец с именем manufacturer после столбца name.

  1. Изменить существующий столбец:
ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';

Эта команда изменяет столбец description на строку переменной длины с максимальной длиной 500 символов. Также устанавливается значение по умолчанию 'No description available' для новых строк.

  1. Переименовать столбец:
ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);

Эта команда переименовывает столбец weight в product_weight и изменяет его тип данных на десятичное число с 8 цифрами в общей сложности и 2 десятичными знаками.

  1. Удалить столбец:
ALTER TABLE products
DROP COLUMN in_stock;

Эта команда удаляет столбец in_stock из таблицы.

Добавим составной индекс для улучшения производительности запросов:

ALTER TABLE products
ADD INDEX idx_name_manufacturer (name, manufacturer);

Эта команда создает составной индекс на столбцах name и manufacturer.

Чтобы увидеть все изменения, которые мы внесли:

DESCRIBE products;
SHOW INDEX FROM products;

Вы должны увидеть обновленную структуру таблицы и индексы:

MariaDB [store]> DESCRIBE products;
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| Field          | Type          | Null | Key | Default                  | Extra                         |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| id             | int(11)       | NO   | PRI | NULL                     | auto_increment                |
| name           | varchar(100)  | NO   | MUL | NULL                     |                               |
| manufacturer   | varchar(100)  | YES  |     | NULL                     |                               |
| price          | decimal(10,2) | NO   |     | NULL                     |                               |
| description    | varchar(500)  | NO   |     | No description available |                               |
| product_weight | decimal(8,2)  | YES  |     | NULL                     |                               |
| created_at     | datetime      | YES  |     | current_timestamp()      |                               |
| last_updated   | timestamp     | NO   |     | current_timestamp()      | on update current_timestamp() |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [store]> SHOW INDEX FROM products;
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| products |          0 | PRIMARY               |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            1 | name         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            2 | manufacturer | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)

Работа с информацией о таблицах

На этом этапе мы рассмотрим различные способы получения информации о наших таблицах и их структурах.

Сначала посмотрим на информацию о статусе таблиц:

SHOW TABLE STATUS FROM store;

Эта команда показывает информацию о каждой таблице в базе данных, включая:

  • Хранилище (storage engine)
  • Формат строки (row format)
  • Количество строк
  • Средняя длина строки
  • Размер данных
  • Размер индексов

Чтобы увидеть все таблицы в нашей базе данных:

SHOW TABLES;

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

SHOW FULL COLUMNS FROM products;

Это предоставляет дополнительную информацию о каждом столбце, включая:

  • Тип столбца
  • Сравнение (collation)
  • Привилегии
  • Комментарии

Чтобы увидеть все индексы на таблице:

SHOW INDEX FROM products;

Мы также можем получить информацию о наших таблицах из базы данных INFORMATION_SCHEMA:

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'store';

Удаление таблиц и очистка

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

Перед удалением таблицы хорошей практикой является:

  1. Проверить, что вы находитесь в правильной базе данных.
  2. Убедиться, что таблица существует.
  3. Сделать резервную копию, если это необходимо.

Начнем с проверки текущей базы данных и таблиц:

SELECT DATABASE();
SHOW TABLES;

Для безопасного удаления таблицы можно использовать предложение IF EXISTS:

DROP TABLE IF EXISTS inventory_items;

Обратите внимание, что сначала нужно удалить таблицы с внешними ключами (foreign key constraints). Если мы попытаемся удалить таблицу categories первым, то получим ошибку, так как таблица inventory_items ссылается на нее.

Теперь можно удалить оставшиеся таблицы:

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

Для проверки того, что таблицы удалены:

SHOW TABLES;

Вы должны увидеть пустой набор, что означает, что все таблицы были удалены.

Резюме

В этом практическом занятии (lab) мы рассмотрели основные аспекты работы с таблицами и типами данных в MySQL:

  1. Понимание и использование различных типов данных MySQL
  2. Создание таблиц с соответствующими ограничениями
  3. Изменение структуры таблиц с использованием команды ALTER TABLE
  4. Получение метаданных и информации о таблицах
  5. Безопасное удаление таблиц, когда они больше не нужны

Эти навыки являются основой для эффективной работы с базами данных MySQL.

Связанные MySQL Курсы
MySQL для начинающих

MySQL для начинающих

databasemysql
Практические лабораторные работы по продвинутому MySQL

Практические лабораторные работы по продвинутому MySQL

databasemysql
Управление правами пользователей MySQL

Управление правами пользователей MySQL

databasemysql