Создание и управление таблицами в SQL: полное руководство

Что такое таблицы в SQL?

Таблицы в SQL — это объекты, которые используются для хранения данных в реляционных БД. Каждая из них состоит из строк, или записей и столбцов, или полей: столбцы представляют из себя атрибуты (свойства/характеристики) записей в таблице. 

Типы таблиц в SQL

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

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

Во временных же хранятся данные, которые нужны только во время текущей сессии (например, какие-то промежуточные результаты): как только она будет закрыта, временная таблица удалится. 

Основные операции с таблицами в SQL

К основным операциям относят создание, изменение и удаление. Рассмотрим их подробнее.

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

• Для создания таблиц в SQL используется команда CREATE TABLE, вместе с которой нужно указать имя таблицы и набор столбцов с их типами:
  

CREATE TABLE products ( 

    product_id INT, 

    product_name VARCHAR(100), 

    price DECIMAL(10, 2) 

);

Здесь создается таблица products со столбцами product_id, product_name и price с типами данных int, varchar и decimal соответственно.

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

Так можно добавить новый столбец description c типом данных text в таблицу products:

ALTER TABLE products 

ADD description TEXT;

Затем этот же столбец можно переименовать (в разных СУБД (системах управления базами данных) синтаксис может отличаться):

ALTER TABLE products 

RENAME COLUMN description TO new_name;

Удалить этот же столбец можно следующим образом:

ALTER TABLE products 

DROP COLUMN description;

Чтобы изменить тип данных столбца, тоже используется ALTER TABLE, но для разных СУБД синтаксис отличается. Например, в SQL Server и MS Access это будет выглядеть так:

ALTER TABLE products

ALTER COLUMN description VARCHAR(255);

Для MySQL синтаксис будет таким:

ALTER TABLE products

MODIFY COLUMN description VARCHAR(255);

Также можно изменить название самой таблицы:

ALTER TABLE products 

RENAME TO new_name;

• Для того чтобы удалить таблицу, используется команда DROP TABLE:

DROP TABLE products;

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

Также существует команда TRUNCATE TABLE — она удаляет все данные, но оставляет в базе данных структуру таблицы:

TRUNCATE TABLE products;

Команда CREATE TABLE: синтаксис и особенности

Определение столбцов, типов данных и ограничений

Чтобы рассмотреть команду CREATE TABLE более подробно, немного модифицируем пример из прошлого пункта:

CREATE TABLE products ( 

    product_id INT PRIMARY KEY, 

    product_name VARCHAR(100) UNIQUE, 

    price DECIMAL(10, 2) CHECK (price > 0)

);

Здесь появились ограничения, но сначала стоит уделить внимание определению столбцов:

• в одной таблице каждый столбец должен иметь уникальное имя;
• имя столбца не должно совпадать с ключевыми словами (SELECT и другие);
• лучше именовать столбцы кратко и в соответствии с их содержимым, например, если в столбце будут записываться названия товаров, то можно назвать его product_name;
• как правило, используются стили именования snake_case или CamelCase.

За названием столбца следует указание его типа данных. В SQL существует много типов данных, рассмотрим некоторые из них:

• int — для хранения целых чисел. Также существует bigint для хранения очень больших чисел и tinyint для хранения небольших чисел (менее 255). Оценивать, какой диапазон числовых значений будет храниться в столбце, важно, так как разные типы данных занимают различный объем памяти (tinyint — 1 байт, int — 4, bigint — 8);
• decimal — для хранения точных чисел с фиксированным количеством знаков после запятой. Например, запись DECIMAL(10, 2) в примере означает, что цена может состоять из 10 цифр, при этом две из них после запятой;
• Для хранения дат есть три основных типа данных: date (хранит только дату), time (хранит только время), timestamp (хранит дату и время без учета часового пояса);
• boolean — для хранения значений true и false;
• varchar — для строк с ограничением максимальной длины. Максимальное количество знаков указывается в скобках, например столбец с varchar(10) может хранить строки длиной до 10 символов;
• text — для строк без ограничения длины. Обычно этот тип данных используется, когда столбец должен содержать большие тексты, например объемные описания.

Также в SQL существуют ограничения, которые указываются после типа столбца:

• PRIMARY KEY — это ограничение задает уникальный идентификатор для каждой записи (строки). Столбцы с этим ограничением не могут содержать NULL и повторяющиеся значения:

product_id INT PRIMARY KEY

В таблице может быть только один PRIMARY KEY, но состоять он может из нескольких столбцов:

CONSTRAINT PK_Products PRIMARY KEY (product_id, product_name)

• FOREIGN KEY — это ограничение, которое означает, что столбец (или несколько столбцов) в данной таблице ссылается на столбец с PRIMARY KEY в другой:

FOREIGN KEY (product_id) REFERENCES products(product_id)

• UNIQUE — значение столбца должно быть уникальным (если попробовать добавить уже существующее значение, то будет выдана ошибка):

product_name VARCHAR(100) UNIQUE

• NOT NULL — значение столбца не может быть NULL:

product_name VARCHAR(100) NOT NULL

• DEFAULT — позволяет задать значение столбца по умолчанию:

product_name VARCHAR(100) DEFAULT 'Наименование не указано'

Незаполненные строки в столбце «Наименование продукта» будут заполнены значением «Наименование не указано».

• CHECK — значение столбца должно соответствовать указанному условию:

price DECIMAL(10, 2) CHECK (price > 0)

В данном случае цена на товар должна быть больше нуля.

Управление данными в таблицах

Рассмотрим основные команды для управления данными в SQL:

• Для того чтобы вставить новые данные, используется команда INSERT INTO:

INSERT INTO products (product_id, product_name, price) 
VALUES (1, 'Телефон', 30000.00);

Так данные будут вставлены во все столбцы таблицы products, но можно добавить значения и только в определенные столбцы:

INSERT INTO products (product_id, price) 

VALUES (1, 30000.00);

• Чтобы изменить данные в строке, можно использовать команду UPDATE:

UPDATE products 

SET price = 18000.00, product_id = 2

WHERE product_name = 'Телефон';

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

• Для выбора столбцов используется команда SELECT. Так можно выбрать все столбцы таблицы products:

SELECT * FROM products;

Для выбора конкретных столбцов нужно просто перечислить их имена:

SELECT product_name, price FROM products;

В комбинации с SELECT также можно использовать WHERE для фильтрации данных:

SELECT * FROM products 

WHERE price > 15000.00;

Выбраны будут только те строки, где значение столбца price больше 15000.00.

Индексация таблиц для оптимизации запросов

Когда пользователю нужно, например, найти определенную запись, сервер БД начинает перебирать каждую строку, чтобы найти соответствия. Для маленьких таблиц это приемлемо, однако с ростом объема данных производительность запросов стремительно падает. Эту проблему может решить индексация: индексы в SQL — это структура данных, хранящая ссылки на строки в отсортированных по определенному признаку столбцах.

Для создания индекса используется команда CREATE INDEX:

CREATE INDEX idx_product_name ON products(product_name);

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

Для того чтобы удалить индекс, используется команда DROP INDEX:

DROP INDEX idx_product_name ON products;

Соединение таблиц в SQL — JOIN-операции

JOIN-операции, как и следует из их названия, используются для объединения строк нескольких таблиц по определенному критерию.

Основные виды соединений и их использование

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

Первая (назовем ее products) содержит информацию о товарах интернет-магазина, а вторая (orders) — о заказах. Рассмотрим основные виды операций объединения: 

• INNER JOIN. Возвращает строки, для которых совпадения найдены в обеих таблицах:

- - Дополнительно выведем стоимость заказа (цена * количество)

SELECT products.product_name, orders.quantity, (products.price * orders.quantity) AS total_cost

FROM products

INNER JOIN orders ON products.product_id = orders.product_id;

Строки будут возвращаться, если product_id из products совпадает с product_id из orders. Результат будет выглядеть так:

Можно заметить, что одного из заказов (с product_id равным 5) здесь нет — это произошло из-за того, что в products товара с таким id не существует.

• FULL JOIN. Возвращает все строки, вне зависимости от того, совпадают ли значения (если совпадения нет, то строка будет содержать NULL):

SELECT products.product_name, orders.quantity, (products.price * orders.quantity) AS total_cost 

FROM products 

FULL JOIN orders ON products.product_id = orders.product_id;

Полученный результат:

Так как планшета нет в orders, столбцы «Количество» и «Итоговая стоимость» содержат NULL. Такая же ситуация с товаром, id которого равен 5: товара с таким id в products нет, поэтому значение столбцов «Название товара» и «Итоговая стоимость» для него NULL.

• CROSS JOIN. Возвращает все комбинации строк указанных таблиц (декартово произведение).

SELECT products.product_name, orders.quantity

FROM products

CROSS JOIN orders;

Полученный результат (здесь он сокращен):

• LEFT JOIN. Возвращает все строки из левой таблицы и соответствующие строки из правой (если совпадения нет, то вернется значение NULL).

SELECT products.product_name, orders.quantity, (products.price * orders.quantity) AS total_cost 

FROM products 

LEFT JOIN orders ON products.product_id = orders.product_id; 

Результат:

Так как в правой таблице (orders) нет id планшета, столбцы quantity и total_cost содержат NULL.

• RIGHT JOIN. Работает аналогично LEFT JOIN, но возвращает все строки из правой таблицы и соответствующие строки из левой.

SELECT orders.order_id, products.product_name, orders.quantity, (products.price * orders.quantity) AS total_cost 

FROM products 

RIGHT JOIN orders ON products.product_id = orders.product_id;

Полученный результат:

В итоге возвращены все строки из правой таблицы (orders), но товар с product_id, равным 5, не найден в products, поэтому поля product_name и total_cost содержат NULL.

• SELF JOIN. Используется для объединения таблицы с самой собой. Стоит сказать, что команды SELF JOIN как таковой не существует: для ее реализации используется JOIN, LEFT JOIN и другие команды. Разница лишь в том, что в запросе указывается одна и та же таблица с добавлением псевдонимов. Для иллюстрации работы SELF JOIN построим новую таблицу:

Здесь появилось третье поле — подходящие товары (например, к телефону подходят наушники), он представлен в виде id. Теперь можно вывести товары с подходящими для них позициями в другом формате:

SELECT p1.product_name AS Product, p2.product_name AS Suitable_Product 

FROM products p1 

LEFT JOIN products p2 ON p1.suitable_product_id = p2.product_id;

В данном случае псевдонимы — это p1 и p2.

Результат:

Для планшета подходящего товара нет, поэтому в соответствующем столбце записан NULL.

Оптимизация работы с таблицами в SQL

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

• грамотный выбор типов данных. Здесь нужно отталкиваться от того, что будет записываться в столбец, например, если он будет хранить возраст человека, то оптимальным типом данных является tinyint, а не int или bigint;
• индексация. Это хороший способ оптимизации, однако создавать индексы нужно по тем столбцам, по которым операции будут выполняться часто — избыточная индексация, наоборот, снизит производительность;
• нормализация. Этот процесс подразумевает минимизацию дублирования и избыточных зависимостей в данных. Также существует обратный процесс — денормализация, она полезна, например, когда нужно сократить количество JOIN-операций (большое количество операций объединения также приводит к снижению производительности);
• использовать агрегатные функции в больших таблицах стоит только при явной необходимости.

Лучшие практики по управлению таблицами в MySQL и SQL Server

Советы по оптимизации и управлению таблицами

Рассмотрим несколько способов оптимизации таблиц в двух популярных СУБД: MySQL и SQL Server.

Для MySQL:

• Команда OPTIMIZE TABLE. Эта команда уменьшает объем, занимаемый таблицей в хранилище и, соответственно, повышает производительность операций. Синтаксис OPTIMIZE TABLE:

OPTIMIZE TABLE table_name;

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

• Оптимизация через терминал. Для этого нужно выполнить следующую команду:

sudo mysqlcheck -o <schema> <table_name> -u <username> -p <password>

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

Для SQL Server:

• использование Database Engine Tuning Advisor. Этот инструмент проводит анализ производительности и создает рекомендации: действия, которые стоит выполнить для повышения производительности;

• оптимизация запросов. Этот процесс подразумевает выявление запросов, на выполнение которых тратится наибольшее количество ресурсов. Часто это запросы, содержащие функции агрегации/операторы GROUP BY, ORDER BY и другие. После того как такие запросы определены, стоит пересмотреть необходимость их использования и сократить объем обрабатываемых в них данных. Также такие запросы лучше выполнять, когда сервер не нагружен;

• обновление индексов. Здесь важно контролировать количество индексов и регулярно удалять те, что больше не используются.

Примеры создания таблиц в SQL

Практические примеры для бизнеса и аналитики

Рассмотрим практический пример для аналитики, вернемся к созданию таблицы products — допустим, она содержит данные о товарах интернет-магазина:

CREATE TABLE products ( 

    product_id INT PRIMARY KEY, 

    product_name VARCHAR(100), 

    price DECIMAL(10, 2) 

);

Также создадим таблицу sales — она содержит информацию о продажах:

CREATE TABLE sales ( 

    sale_id INT PRIMARY KEY, 

    product_id INT, 

    quantity INT, 

    sale_date DATE, 

    FOREIGN KEY (product_id) REFERENCES products(product_id) 

);

На основе данных из этих двух таблиц можно провести анализ, например, выяснить, какова средняя сумма заказа:

SELECT AVG(s.quantity * p.price) AS average_order_value 

FROM sales s 

JOIN products p ON s.product_id = p.product_id;

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

SELECT p.product_name, SUM(s.quantity * p.price) AS total_revenue 

FROM products p 

JOIN sales s ON p.product_id = s.product_id 

GROUP BY p.product_name 

ORDER BY total_revenue DESC 

LIMIT 5;

Также, учитывая то, что чаще всего поиск/группировка, вероятно, будет осуществляться по столбцам product_id, sale_date и product_name, лучше создать для них индексы.

Заключение

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

• создание, изменение, удаление — команды CREATE TABLE, ALTER TABLE, DROP TABLE соответственно;
• управление данными: изменение типа данных, вставка данных, добавление/удаление столбцов;
• типы данных и ограничения в SQL;
• виды операций соединения (JOIN-операций);
• способы оптимизации: индексация (команда CREATE INDEX), нормализация, важность грамотного выбора типа данных;
• практические примеры создания таблиц.