Знакомство с SQL

Концепция SQL

SQL появился в середине 70‑х годов XX века сразу после возникновения реляционной модели данных. Разработчики стремились создать язык, который позволял бы создавать базы данных, таблицы и другие объекты, а также выполнять основные операции с данными (вставку, изменение, удаление) и формировать запросы, преобразующие хранящиеся данные в нужный формат. Одной из ключевых особенностей стало использование трёхзначной логики, когда помимо истинного и ложного значений учитывается значение «UNKNOWN» (True, False, UNKNOWN), что отражает неопределённость данных. Кроме того, язык был разработан как декларативный, чтобы пользователь мог описывать требуемый результат, не заботясь о деталях реализации.

SQL был впервые стандартизирован благодаря совместным усилиям ANSI и ISO в 1976г.:

• В 1986 году SQL был стандартизирован ANSI и ISO (часто именуется ANSI SQL).
• SQL:92 (принят в 1992 году) стал общепризнанным стандартом, и большинство СУБД стремятся его поддерживать.
• SQL:99 (SQL-3) в 1999 году расширил возможности языка (например, введены пользовательские типы данных и типизированные таблицы).
  Замечание: Некоторые специалисты критиковали SQL-3 за незавершённость.
• SQL:2003 модернизировал стандарт, включив все части SQL:99 (с некоторыми изменениями) и добавив поддержку взаимодействия с Java и XML.
• С тех пор новые версии стандарта появляются примерно каждые 3–5 лет. На момент написания последним считается SQL:2023.

Возможности SQL

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

DDL (Data Definition Language) – подъязык определения данных, предназначенный для создания, изменения и удаления объектов базы данных. С помощью команды CREATE можно создавать базы данных и их объекты, такие как таблицы, представления, индексы, курсоры и определения доменов, что задаёт «скелет» базы данных. Например:

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100)
);

Команда ALTER позволяет изменять уже созданные объекты (например, добавлять новые столбцы или изменять их типы), а DROP – удалять объекты из базы.

Прим.: Изменения, внесённые через DDL, обычно фиксируются автоматически, отражая изменения в архитектуре базы данных.

DML (Data Manipulation Language) – подъязык манипулирования данными, который отвечает за их добавление, изменение, удаление и извлечение. Здесь применяются такие команды, как INSERT для добавления новых записей, UPDATE для изменения существующих данных и DELETE для их удаления. Наиболее часто используемой является команда SELECT, которая извлекает данные из одной или нескольких таблиц и позволяет объединять, фильтровать, сортировать и группировать информацию. Пример вставки записи:

INSERT INTO Employees (ID, Name) VALUES (1, 'Ivan Ivanov');

и выборки данных:

SELECT * FROM Employees;

Эта комбинация операций обеспечивает динамичное взаимодействие с содержимым базы данных.

Для обеспечения безопасности и контроля доступа к данным используются команды GRANT и REVOKE. Администратор с помощью GRANT предоставляет пользователям или группам права на выполнение определённых операций (чтение, запись, изменение данных), а REVOKE позволяет отозвать ранее предоставленные права.

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

Когда требуется обработка результата запроса по одной строке за раз, применяется механизм управления курсором. Курсор – это специальный объект, позволяющий осуществлять итеративную обработку данных, то есть построчно получать и обрабатывать результаты запроса. Сначала курсор объявляется с помощью команды DECLARE CURSOR, затем открывается командой OPEN CURSOR, строки извлекаются по одной через FETCH CURSOR, и, наконец, курсор закрывается с помощью CLOSE CURSOR. Пример использования курсора:

DECLARE myCursor CURSOR FOR SELECT Name FROM Employees;
OPEN myCursor;
FETCH myCursor INTO @EmployeeName;
-- Обработка строки (например, вывод или дополнительные вычисления)
CLOSE myCursor;

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

Наконец, управление транзакциями обеспечивает целостность данных при выполнении группы взаимосвязанных операций. Транзакция представляет собой логическую единицу, объединяющую несколько SQL-команд. При успешном выполнении всех операций транзакция фиксируется командой COMMIT; в случае возникновения ошибки все изменения отменяются командой ROLLBACK. Команда BEGIN TRANSACTION инициирует транзакцию, а SET TRANSACTION позволяет задать параметры, например, уровень изоляции, влияющий на видимость изменений другими пользователями. Пример транзакции:

BEGIN TRANSACTION;
UPDATE Employees SET Name = 'Ivan Petrov' WHERE ID = 1;
-- Дополнительные операции...
COMMIT;

Если возникает ошибка, можно выполнить:

ROLLBACK;

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

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

Типы данных в SQL

SQL предлагает богатый набор типов данных, который позволяет представлять и обрабатывать самые разные виды информации. Эти типы делятся на две большие группы: стандартные (предопределённые) типы, поддерживаемые, как минимум, стандартом SQL:92, и расширенные (непредопределённые) типы, введённые в более поздних версиях стандарта (SQL:1999 и SQL:2003).

Стандартные типы

Точные числовые типы

Эти типы предназначены для хранения целых чисел и чисел с дробной частью без потерь точности. При их использовании необходимо указывать два параметра: точность (n) и масштаб (m). Точность определяет общее количество значащих цифр, а масштаб – число цифр после десятичной точки. Например, тип NUMERIC(5,2) позволяет хранить число, содержащее до пяти цифр, из которых две располагаются после запятой:

-- Пример использования точного числового типа
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    Amount NUMERIC(5,2)
);

Прим.: При указании параметров обязательно должно выполняться условие: n > m. Если масштаб не указан, он считается равным 0.

Ниже приведена таблица, суммирующая основные точные числовые типы:

Приближенные числовые типы

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

Примеры:

• REAL – обычно занимает 6 байт и может хранить числа в диапазоне от –3,4E–38 до +3,4E+38 с точностью до 7 знаков.
• FLOAT(n) – если n не указан, требует 8 байт памяти; позволяет хранить числа с точностью до 15 знаков.
• DOUBLE PRECISION – имеет ещё большую точность, чем REAL, и зависит от реализации СУБД.

-- Пример объявления столбца с приближённым числовым типом
CREATE TABLE Measurements (
    MeasurementID INT PRIMARY KEY,
    Value FLOAT
);

Дата и время

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

• DATE – хранит дату в формате yyyy-mm-dd (например, 2020-02-01).
• TIME[(n)] – хранит время в формате hh:mm:ss; аргумент n позволяет задать точность для долей секунд.
• TIMESTAMP[(n)] – объединяет DATE и TIME, создавая метку даты-времени.
• TIME WITH TIME ZONE и TIMESTAMP WITH TIME ZONE – учитывают смещение от Гринвича.

-- Пример создания таблицы с типами даты и времени
CREATE TABLE Events (
    EventID INT PRIMARY KEY,
    EventDate DATE,
    EventTime TIME,
    EventTimestamp TIMESTAMP
);

Интервалы

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

• Интервалы год-месяц (year-month interval) – используются для представления длительных периодов (например, интервал в 5 лет и 1 месяц).
• Интервалы день-время (day-time interval) – позволяют хранить разницу в днях, часах, минутах и секундах с точностью до долей секунды.

Пример:

-- Пример задания интервала в 12 часов 54 минуты
SELECT INTERVAL '12:54' HOUR TO MINUTE;

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

Логический тип

Логический тип данных (BOOLEAN) реализует трёхзначную логику: помимо стандартных значений TRUE и FALSE, используется значение UNKNOWN для обозначения неопределенности. Это особенно важно при обработке значений, где может возникнуть неопределённость (например, при сравнении с NULL).

-- Пример использования логического типа в условии
SELECT * FROM Orders WHERE IsProcessed = TRUE;

Строковые типы

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

• Фиксированной длины (CHAR или CHARACTER [n]) – если строка короче указанной длины, оставшиеся позиции заполняются пробелами.
• Переменной длины (VARCHAR или CHARACTER VARYING [n]) – позволяет экономить память, так как неиспользуемые позиции не выделяются.
• Национальные варианты (NCHAR, NVARCHAR) – предназначены для хранения символов национальных наборов.
• Большие объекты (CLOB) – используются для хранения больших текстовых данных.

-- Пример создания таблицы со строковыми типами
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50),
    UserDescription CLOB
);

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

Битовые строки

Битовые типы предназначены для хранения двоичной информации. Они позволяют описывать как простейшие логические данные (например, поля типа BIT для хранения значения "Да/Нет"), так и сложные объекты, такие как файлы мультимедиа.

• BIT [n] – фиксированная битовая последовательность.
• BIT VARYING [n] – последовательность переменной длины.
• BLOB (Binary Large Object) – для хранения больших двоичных объектов, таких как изображения или аудиофайлы.

-- Пример использования BLOB для хранения изображений
CREATE TABLE Pictures (
    PictureID INT PRIMARY KEY,
    ImageData BLOB
);

Расширенные (непредопределенные) типы

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

Коллекции и массивы

Стандарт SQL позволяет объявлять одномерные массивы, где индекс начинается с 1 (в отличие от большинства языков программирования).
Пример:

-- Пример одномерного массива целых чисел
DECLARE numbers INT ARRAY[10];

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

Множество и мультимножество

• Множество (SET) – не допускает повторяющихся значений.
• Мультимножество (MULTISET) – допускает дублирование значений.
  Эти типы полезны для хранения наборов значений, где важна уникальность или, наоборот, повторение элементов.

Последовательности (ROW types)

Последовательности создаются с помощью конструктора типа ROW, который позволяет объединять несколько полей в одну структурированную запись. Например, чтобы хранить ФИО в одном столбце, можно определить структуру, которая разделяет фамилию, имя и отчество:

-- Пример создания таблицы с использованием ROW
CREATE TABLE Demotable (
    Demotable_Key INTEGER PRIMARY KEY,
    FIO ROW (SName VARCHAR(20),
             FName VARCHAR(15),
             LName VARCHAR(15)),
    BDay DATE
);

Пользовательские типы (UDT)

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

-- Пример создания пользовательского типа для коротких строк
CREATE TYPE SHORT_STRING_TYPE AS CHAR(10);

Также возможна иерархия типов. Например, можно создать родительский тип для адреса, а затем на его основе – дочерний тип, добавляющий номер телефона:

-- Пример создания родительского типа ADDRESS_TYPE
CREATE TYPE ADDRESS_TYPE AS (
    ZIPCODE   CHAR(6),
    CITYNAME  VARCHAR(20) NOT NULL,
    STREET    VARCHAR(20) NOT NULL,
    HOME      VARCHAR(3) NOT NULL
)
NOT FINALL;

-- Пример создания дочернего типа ADDRESSEX_TYPE
CREATE TYPE ADDRESSEX_TYPE
UNDER ADDRESS_TYPE
AS (PHONENUM CHAR(11))
FINALL;

Ссылочные типы, XML и JSON

• Ссылочные типы (REF) – позволяют создавать переменные, которые ссылаются на строки в типизированных таблицах.
• XML – предназначен для хранения и обработки структурированных данных с помощью расширяемой разметки.
• JSON – текстовый формат обмена данными, основанный на JavaScript, удобный для взаимодействия с веб-приложениями.

Прим.: Многие СУБД поддерживают дополнительные типы, не входящие в стандарт, что позволяет решать специфичные задачи, например, хранение геометрических данных (как в PostgreSQL: BOX, CIRCLE, LINE и т.д.).

Преобразование данных

В SQL поддерживаются два вида преобразования типов данных:

Неявное преобразование

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

-- Пример неявного преобразования: строка '2' преобразуется в число 2
SELECT 2 + '2';  -- Результат: 4

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

-- Пример, демонстрирующий недостаток неявного преобразования
SELECT 'Hello' + 2;  -- Результат: 2 (ожидалось бы возникновение ошибки)

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

Явное преобразование

Явное преобразование выполняется с помощью функции CAST, которая позволяет точно указать целевой тип данных. Например:

-- Пример явного преобразования: преобразование даты в строку
SELECT CAST(DNDATE AS CHAR(24)) AS DNDATE_TXT
FROM DELIVERYNOTE;

Операторы в SQL

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

6.1. Арифметические операторы

• Присваивание:
  Обычно используется знак = (иногда :=), например:

  SELECT @X := COUNT(*) FROM SUPPLIERS;
  

• Арифметические операторы:

  • Стандартные: +, -, *, /
  • Дополнительные:
    • DIV – целочисленное деление (например, 7 DIV 2 даёт 3)
    • % (или MOD) – остаток от деления (например, 7 % 2 даёт 1)

• Особенности:
  Арифметические операции могут применяться к числам, а также – с датами, временем и интервалами (см. таблицу ниже).

6.2. Логические операторы

• Основные: AND, OR, NOT
• Дополнительно во многих диалектах поддерживается XOR.
  Таблицы 11.3 и 11.9 иллюстрируют работу логических операторов с учетом значения NULL (неопределённости).

6.3. Операторы сравнения

• Сравнения: <, >, <=, >=, =, <> (или !=)
• Специфические для NULL: <=>, а также конструкции IS NULL и IS NOT NULL

Пример проверки на неопределённость:

SELECT NULL IS NULL, 1 IS NULL;  -- Результат: 1 0
SELECT ISNULL(NULL), ISNULL(1);   -- Результат: 1 0

6.4. Конкатенация строк

• Стандартно: используется оператор +
• В некоторых диалектах (InterBase, FireBird): применяется оператор ||

6.5. Встроенные функции

Набор встроенных функций SQL включает:

Манипулирование данными SQL

Ниже приведён конспект по использованию инструкции SELECT, предложения WHERE, псевдонимов (alias) и агрегирующих функций с пояснениями и примерами. Этот материал поможет понять базовую синтаксическую конструкцию запросов SQL, а также как фильтровать, переименовывать и агрегировать данные.

Инструкция SELECT

Инструкция SELECT – ключевой элемент SQL, предназначенный для выборки данных из таблиц или представлений. Результатом запроса всегда является отношение (набор строк), которое можно выводить на экран или использовать в последующих операциях (например, для создания представлений или хранимых процедур).

Базовая структура запроса:

SELECT [DISTINCT|ALL]
  { имя_столбца [AS псевдоним] | функция_агрегирования [AS псевдоним] | выражение [AS псевдоним] | * }
FROM имя_таблицы [AS псевдоним]
[WHERE условие]
[GROUP BY имя_столбца [,...]]
[HAVING условие]
[ORDER BY имя_столбца [ASC|DESC] [,...]];

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

Примеры:

• Выбор всех данных из таблицы поставщиков:

  SELECT * FROM suppliers;
  

• Выбор конкретного столбца:

  SELECT supplier FROM suppliers;
  

• Выбор нескольких столбцов:

  SELECT dnnum, dndate FROM deliverynotes;
  

Условие (WHERE)

Предложение WHERE используется для ограничения набора строк, возвращаемых запросом, на основании заданного условия. Оно позволяет фильтровать данные по значениям, сравнениям, диапазонам, шаблонам и другим критериям.

Основные виды условий:

• Сравнение:
  Пример – выбор записей с датой позже 1 января 2017 года:

  SELECT * FROM deliverynotes
  WHERE dndate >= '2017-01-01';
  

• Комбинированные условия:
  С использованием логических операторов AND и OR:

  SELECT * FROM goodslist
  WHERE SizeX <= 1000 AND SizeY <= 1000 AND SizeZ <= 1000;
  

• Диапазон (BETWEEN):
  Пример – выбор записей с датой между 1 января и 31 декабря 2018 года:

  SELECT * FROM deliverynotes
  WHERE dndate BETWEEN '2018-01-01' AND '2018-12-31';
  

• Соответствие шаблону (LIKE):
  Пример – выбор поставщиков, в имени которых присутствует символ «о»:

  SELECT * FROM suppliers
  WHERE supplier LIKE '%о%';
  

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

Псевдонимы (Alias)

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

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

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

  SELECT CONCAT('№ ', dnnum, ' от ', dndate) AS dinfo
  FROM deliverynotes;
  

• Присвоение псевдонима таблице для сокращения кода:

  SELECT g.goods, t.amount
  FROM goodslist g, transfers t
  WHERE t.goodslist_id = g.goodslist_id;
  

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

Агрегирующие функции

Агрегирующие функции применяются для вычисления единого значения по набору строк. Обычно они используются вместе с предложением GROUP BY, которое группирует строки по определённому признаку. Основные агрегатные функции:

Примеры:

• Подсчёт количества записей в таблице поставщиков:

  SELECT COUNT(*) AS total_suppliers
  FROM suppliers;
  

• Вычисление средней цены товара:

  SELECT AVG(price) AS average_price
  FROM products;
  

• Группировка данных и применение SUM для подсчёта общего количества единиц товара по наименованию:

  SELECT goods, SUM(amount) AS total_amount
  FROM goodslist
  WHERE deliverynote_id = 100
  GROUP BY goods;
  

• Применение предиката HAVING для фильтрации групп с суммарной стоимостью более 1000:

  SELECT goods, SUM(amount * factoryprice) AS total_price
  FROM goodslist
  GROUP BY goods
  HAVING SUM(amount * factoryprice) > 1000;
  

Прим.: Если в запросе GROUP BY используются агрегирующие функции, все столбцы, не являющиеся результатом агрегирования, должны присутствовать в списке группировки. Квантификатор DISTINCT может быть применён внутри агрегатных функций для исключения дубликатов.

Ознакомиться и выполнить упражнения из разделов:

• Простой оператор SELECT. Упражнения - 1, 2, 3, 4, 5, 6, 9, 14, 31, 33, 42.
• Переименование столбцов и вычисления в результирующем наборе. Упражнения - 32.
• Получение итоговых значений. Упражнения - 10, 11, 12, 13, 18, 24, 25, 26, 27, 40, 41, 43, 51, 53, 54, 58, 61, 62, 75, 77, 79, 80, 81, 85, 86, 88, 91, 92, 93, 94, 95, 96, 103, 109, 127, 129.