🔤 Азбука SQL в примерах. Часть 3. Обрабатываем результаты и делаем сложные запросы

Подсчет строк в результате запроса

Для подсчета количества строк вы можете использовать агрегатную функцию COUNT().

        SELECT COUNT(*) AS Count
FROM Pets;

    

Результат:

        +---------+
| Count   |
|---------|
| 8       |
+---------+

    

В результате выполнения запроса мы видим, что наша таблица Pets содержит 8 строк. Мы можем быть уверены, что это так, поскольку выбрали все строки и все столбцы с помощью звездочки *.

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

Функция COUNT() подсчитывает только непустые значение (NOT NULL), поэтому при выборе столбцов, содержащих значения NULL, не все записи будут посчитаны. Я продемонстрирую это на следующем примере:

        SELECT COUNT(DOB) AS Count
FROM Pets;

    

Результат:

        +---------+
| Count   |
|---------|
| 6       |
+---------+
Warning: Null value is eliminated by an aggregate or other SET operation.

    

Вспомните, что таблица Pets содержит две записи со значением NULL в столбце DOB (у двух питомцев не указана дата рождения). Именно поэтому выражение COUNT(DOB) показало всего 6 записей, в то время как COUNT(*) показывала, что их 8. Выражение COUNT(*) возвращает результат 8 потому, что эти две записи содержат данные в других столбцах.

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

Помимо COUNT() в список агрегатных функций входят AVG(), SUM(), MIN(), и MAX() (англ.).

Оператор GROUP BY

Теперь давайте рассмотрим оператор GROUP BY. Его название «сгруппировать по» говорит само за себя: этот оператор позволяет сгруппировать результаты запроса по выбранным столбцам.

        SELECT
	PetTypeId,
	COUNT(PetTypeId) AS Count
FROM Pets
GROUP BY PetTypeId
ORDER BY Count DESC;

    

Результат:

        +-------------+---------+
| PetTypeId   | Count   |
|-------------+---------|
| 3           | 4       |
| 2           | 3       |
| 1           | 1       |
+-------------+---------+

    

В этом примере мы посчитали, сколько питомцев каждого вида у нас есть, а затем отсортировали в порядке убывания по видам питомцев (от большего идентификатора к меньшему).

Оператор HAVING

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

        SELECT
	PetTypeId,
	COUNT(PetTypeId) AS Count
FROM Pets
GROUP BY PetTypeId
HAVING COUNT(PetTypeId) > 2
ORDER BY Count DESC;

    

Результат:

        +-------------+---------+
| PetTypeId   | Count   |
|-------------+---------|
| 3           | 4       |
| 2           | 3       |
+-------------+---------+

    

В этом примере мы получили те виды питомцев, для которых количество животных больше 2.

Соединения

В SQL соединения позволяют создавать запросы, комбинирующие данные из нескольких таблиц. Предыдущие два примера неплохи, но было бы лучше, если бы мы вместо числовых идентификаторов видов питомцев (1,2,3 и т. п.) увидели их названия (Кошка, Собака, Птица и другие). Проблема состоит в том, что таблица Pets не содержит названия видов, эти данные хранятся в таблице PetTypes.

К счастью, мы можем соединить эти две таблицы. Вот пример использования левого соединения с помощью оператора LEFT JOIN:

        SELECT
	PetTypes.PetType,
	COUNT(Pets.PetTypeId) AS Count
FROM Pets
LEFT JOIN PetTypes
ON Pets.PetTypeId = PetTypes.PetTypeId
GROUP BY PetTypes.PetType
ORDER BY Count DESC;

    

Результат:

        +-----------+---------+
| PetType   | Count   |
|-----------+---------|
| Dog       | 4       |
| Cat       | 3       |
| Bird      | 1       |
+-----------+---------+

    

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

Синтаксис использует тип соединения (в данном случае LEFT JOIN), перед которым указана первая (левая) таблица, а следом после типа соединения указана вторая (правая) таблица. После перечисления таблиц следует слово ON, и за ним уже условие соединения.

Давайте используем внутреннее соединение INNER JOIN, чтобы получить имена питомцев с указанием их вида:

        SELECT
	Pets.PetName,
	PetTypes.PetType
FROM Pets
INNER JOIN PetTypes
ON Pets.PetTypeId = PetTypes.PetTypeId;

    

Результат:

        +-----------+-----------+
| PetName   | PetType   |
|-----------+-----------|
| Fluffy    | Cat       |
| Fetch     | Dog       |
| Scratch   | Cat       |
| Wag       | Dog       |
| Tweet     | Bird      |
| Fluffy    | Dog       |
| Bark      | Dog       |
| Meow      | Cat       |
+-----------+-----------+

    

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

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

Псевдонимы

Мы можем сделать следующий шаг и назначить псевдоним (alias) каждому имени таблицы или столбца.

        SELECT
	p.PetName AS Pet,
	pt.PetType AS "Pet Type"
FROM PetTypes pt
LEFT JOIN Pets p
ON p.PetTypeId = pt.PetTypeId;

    

Результат:

        +---------+------------+
| Pet     | Pet Type   |
|---------+------------|
| Tweet   | Bird       |
| Fluffy  | Cat        |
| Scratch | Cat        |
| Meow    | Cat        |
| Fetch   | Dog        |
| Wag     | Dog        |
| Fluffy  | Dog        |
| Bark    | Dog        |
| NULL    | Rabbit     |
+---------+------------+

    

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

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

Псевдоним назначается с помощью ключевого слова AS, использовать которое необязательно, поэтому вы можете смело опустить его. Обратите внимание, что СУБД Oracle не поддерживает ключевое слово AS для псевдонимов таблиц (но оно поддерживает псевдонимы для столбцов). В приведенном выше примере я вставил ключевое слово AS при назначении псевдонимов столбцов, но опустил его при назначении псевдонимов таблиц.

Псевдоним может быть любым, но обычно его выбирают коротким, чтобы код было проще читать. В нашем случае мы изменили имена таблиц на top и pt, а имена столбцов – на Pet и Pet Type. Обратите внимание, что я заключил псевдоним Pet Type в двойные кавычки. Я сделал так, потому что в названии есть пробел. Для псевдонимов без пробелов вам не нужно этого делать. В СУБД SQL Server вы можете использовать квадратные скобки [ ] вместо двойных кавычек, но двойные кавычки она тоже поддерживает.

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

Обратите внимание: нам все равно нужно использовать полные имена столбцов при построении соединения (JOIN) после ключевого слова ON.

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

Изменение данных

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

        UPDATE Owners
SET LastName = 'Stallone'
WHERE OwnerId = 3;

    

В этом примере мы записываем новое значение Stallone в столбец LastName только в тех записях таблицы Owners, где значение в столбце OwnerId равно 3.

Чтобы изменить значения в нескольких столбцах, перечислите все пары «Столбец = Значение» через запятую. Но ни в коем случае не забывайте про оператор WHERE, если вы не собираетесь изменить все записи в таблице.

Удаление данных

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

        DELETE FROM Owners
WHERE OwnerId = 5;

    

Здесь мы удаляем из таблицы Owners записи, у которых значение в столбце OwnerId равно 5.

Как и в случае с оператором UPDATE ни в коем случае не забывайте про оператор WHERE, иначе вы удалите все записи из таблицы.

Удаление объектов из базы данных

Если до этого мы говорили об удалении записей, то применительно к объектам базы данных (таблицам, представлениям, хранимым процедурам и другим объектам) очень часто применяют слово «уничтожение». Например, когда таблица нам больше не нужна мы «уничтожаем» её. Синтаксис этого оператора чрезвычайно прост и выглядит так:

        DROP TABLE Customers;

    

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

Синтаксис оператора для удаления других видов объектов точно такой же, вам только нужно заменить ключевое слово table на другое (например, запрос DROP VIEW vPets уничтожит представление с именем vPets)

Если вы попытаетесь уничтожить таблицу, на которую ссылаются вторичные ключи, вы, вероятно, получите сообщение об ошибке. В этом случает вам придется сначала убрать вторичные ключи с помощью оператора ALTER TABLE или уничтожить все таблицы, использующие эти вторичные ключи (такие таблицы называют дочерними по отношению к таблице с первичным ключом).

Операторы SQL

В отличие от команд и операторов запроса, с которыми мы познакомились ранее (SELECT, UPDATE, USE и других) в SQL существуют еще математические и логические операторы. Такие операторы представляют собой один или несколько символов, обозначающих какое-то действие, которое должно быть выполнено в выражении.

Мы уже видели некоторые такие операторы в действии. Наши предыдущие примеры запросов содержали оператор WHERE, которое включало оператор «Равно» (=). Мы также выполняли запрос, в котором использовался оператор «Больше, чем» (>). Это операторы сравнения – они сравнивают две части выражения (операнды).

Представления SQL

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

Чтобы создать представление, нужно написать запрос, а затем сохраняете его как представление, используя оператор CREATE VIEW. Вот пример создания представления:

        CREATE VIEW vPetTypeCount AS
SELECT
	PetTypes.PetType,
	COUNT(Pets.PetTypeId) AS Count
FROM Pets
LEFT JOIN PetTypes
ON Pets.PetTypeId = PetTypes.PetTypeId
GROUP BY PetTypes.PetType;

    

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

        SELECT * FROM vPetTypeCount;

    

Результат:

        +-----------+---------+
| PetType   | Count   |
|-----------+---------|
| Bird      | 1       |
| Cat       | 3       |
| Dog       | 4       |
+-----------+---------+

    

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

Сортировка результатов представления

Должен обратить ваше внимание, что в исходном запросе был оператор ORDER BY, но я не включил его в представление. Стандарт SQL не допускает использование оператора ORDER BY при создании представления. Кроме того, большинство СУБД выдадут сообщение об ошибке, если вы попытаетесь включить оператор ORDER BY в запрос внутри представления.

Однако это не проблема, ведь вы можете отсортировать результаты в запросе, использующем представление. Например, вот так:

        SELECT * FROM vPetTypeCount
ORDER BY Count DESC;

    

Результат:

        +-----------+---------+
| PetType   | Count   |
|-----------+---------|
| Dog       | 4       |
| Cat       | 3       |
| Bird      | 1       |
+-----------+---------+

    

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

Хранимые процедуры

Хранимая процедура — это набор инструкций SQL, скомпилированных и сохраненных в базе данных. Хранимые процедуры в некотором смысле похожи на представления, но сильно отличаются в других моментах. Одним из преимуществ хранимых процедур является то, что они позволяют хранить сложные скрипты на сервере. Хранимые процедуры часто содержат условные конструкции, такие как IF… ELSE. Хранимые процедуры также могут принимать параметры.

Вот пример создания простой хранимой процедуры для СУБД SQL Server, чтобы получить информацию о домашних животных из нашей базы данных:

        CREATE PROCEDURE
	@PetId int
AS
	SET NOCOUNT ON;
	SELECT
		p.PetName,
		p.DOB,
		pt.PetType,
		CONCAT(o.FirstName, ' ', o.LastName) AS OwnerName
	FROM Pets p
	INNER JOIN PetTypes pt
	ON p.PetTypeId = pt.PetTypeId
	INNER JOIN Owners o
	ON o.OwnerId = p.OwnerId
	WHERE p.PetId = @PetId;

    

Эта хранимая процедура получает параметр с именем @PetId. Поэтому, когда вы вызываете процедуру uspGetPetById, вы должны передать ей идентификатор питомца, о котором нужно получить информацию. Процедура выберет данные из нескольких таблиц и вернет результат. Чтобы вызвать хранимую процедуру, нужно использовать инструкцию EXECUTE. Для удобства вы можете сократить её до EXEC. В СУБД PostgreSQL вместо EXECUTE используется инструкция CALL.

        EXEC uspGetPetById @PetId = 3;

    

Результат:

        +-----------+------------+-----------+-------------+
| PetName   | DOB        | PetType   | OwnerName   |
|-----------+------------+-----------+-------------|
| Scratch   | 2018-10-01 | Cat       | Bart Pitt   |
+-----------+------------+-----------+-------------+

    

В приведенном примере нас интересовал питомец с идентификатором 3 и в результате мы получили информацию о нем.

Должен упомянуть, что синтаксис для создания хранимых процедур может довольно существенно отличаться в разных СУБД (так же, как и реализация различных операторов SQL и команд, которые вы будете использовать внутри процедуры), поэтому стоит ознакомиться с документацией для вашей конкретной СУБД прежде, чем создавать хранимую процедуру.

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

Триггеры SQL

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

Большинство основных СУБД поддерживают триггеры, которые выполняются, когда пользователь пытается изменить данные с помощью языка манипулирования данными (Data Manipulation Language, сокращенно DML). Событиями DML являются выполнение таких операторов, как INSERT, UPDATE или DELETE.

Некоторые СУБД (например, SQL Server и PostgreSQL) позволяют связывать триггеры как с таблицами, так и с представлениями. Другие разрешают связывать триггеры только с таблицами.

SQL Server также поддерживает триггеры для языка описания данных (data definition language, сокращенно DDL) и триггеры входа пользователей в СУБД. Триггеры DDL выполняются в ответ на события, порождаемые операторами CREATE, ALTER и DROP, а также определенными системными хранимыми процедурами, действия которых аналогичны операторам DDL. Триггеры входа в систему запускаются в ответ на событие, которое возникает при начале сеанса пользователя.

Транзакции SQL

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

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

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

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

        START TRANSACTION
Списать деньги со счета account 1
Зачислить деньги на счет account 2
Записать транзакцию в журнал транзакций
END TRANSACTION

    

Мы могли бы написать условную логику внутри этой транзакции, которая откатывает её, если что-то пойдет не так. Главное, чтобы наша транзакция либо была завершена полностью, либо не выполнена совсем. Она не должна остановиться на половине.

Функции SQL

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

Пользовательские функции

Пользовательская функция (user-defined function, UDF) — это функция, которую вы создаете для определенной цели и сохраняете в базе данных. Такие функции создаются для задач, для которых не предусмотрены встроенные функции.

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

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

Разнообразие встроенных функций может быть довольно большим и зависит от используемой СУБД, поэтому я не буду подробно останавливаться на них в этом руководстве по SQL. Но я бы посоветовал вам попробовать выяснить, какие встроенные функции поддерживает ваша СУБД.