SELECT Room_Type, Length * Width AS
Living_Space
FROM Rooms
WHERE Room_Type = `Гостиная OR Room_Type =`Спальня`
В результирующей таблице не будет данных о длине и ширине, зато появится столбец с информацией о площади, которая была вычислена непосредственно при исполнении запроса
Второй вариант применения ключевого слова AS можно проиллюстрировать на примере о торговых агентах из предыдущей заметки, который теперь будет выглядеть так:
SELECT DISTINCT Name, Birth_Date
FROM Agents AS A1, Contracts AS C1
WHERE A1.Agent_id = C1.Agent_id AND C1.CDate
>`31.12.2004` AND C1.Gross_Income > 500000
Дав с помощью ключевого слова АS таблицам Agents и Contacts сокращенные имена, мы сделали текст более компактным. Запрос можно сделать многоступенчатым, тогда результат вложенного запроса станет исходными данными. Тот же самый пример с агентами можно выполнить в виде вложенного запроса:
SELECT Name, Birth_Day
FROM Agents
WHERE Agent_id IN (SELECT Agent_id
FROM Contracts
WHERE CDate >`31.12.2004` AND
Gross_Income >500000
В данном случае предикат IN последовательно проверяет, имеется ли среди результатов вложенного запроса по базе контрактов идентификатор каждого из агентов. Если он есть, то в результирующую таблицу головного запроса добавляются его данные (для противоположного результата можно использовать предикат NOT IN). В большинстве случаев предпочтительнее вместо вложенных запросов применять соединение таблиц по общим столбцам (...WHERE Agents.Ag_Num = Contracts.Ag_Num...), однако иногда бывает, что все-таки без вложений не обойтись.
Объединения и внешние соединения
Выше мы воспользовались оператором OR для выборки данных о спальнях и гостиных. Можно пойти другим путем и использовать оператор UNION для объединения двух запросов:
(SELECT Room_Type, Length1 * Width1 AS
Living_Space FROM Rooms WHERE Room_Type =
`Гостиная`) UNION (SELECT Room_Type, Length1 *
Width1 AS Living_Space FROM Rooms WHERE
Room_Type = `Спальня`)
Оператор UNION строит на основе двух таблиц третью, куда попадают строки, которые есть либо в первой исходной, либо во второй, либо в обеих вместе; строки-дубликаты при этом удаляются. Иногда для подобных целей удобнее пользоваться оператором OR, однако если условия объединяемых подзапросов сложные, UNION для их составления подходит больше. Суть такого инструмента как внешнее соединение можно пояснить на следующем примере. Допустим, нам необходимо сделать выборку по контрактам, заключенным агентами в июне 2005 года. Мы можем воспользоваться для этого таким запросом:
SELECT Name, CDate, Gross_Income
FROM Agents AS A1, Contracts AS C1
WHERE A1.Agent_id = C1.Agent_id AND C1.CDate
BETWEEN `01.06.2005`AND`30.06.2005`
Он, разумеется, выдаст правильные результаты, однако наличие имени агента после обработки запроса зависит от того, заключил ли он сделку в этот период. Если необходимо, чтобы в результирующей таблице всегда присутствовали все агенты, необходимо использовать так называемое левое внешнее соединение (LEFT OUTER JOIN). Его смысл состоит в том, что все строки таблицы, указанной слева от оператора LEFT OUTER JOIN, попадают в таблицу-результат, а из таблицы справа берутся только данные, которые соответствуют условию:
SELECT Name, CDate, Gross_Income
FROM Agents LEFT OUTER JOIN Contracts ON
Agents.Agent_id = Contracts.Agent_id
AND Contracts.CDate BETWEEN `01.06.2005` AND
`30.06.2005`
Каждый агент из таблицы Agents записанной слева от LEFT OUTER JOIN, попадет в результат запроса, даже если ему нельзя будет подобрать соответствующих строк из правой таблицы (поскольку не все агенты заключали контракты в июне 2005 года). Необходимо обратить внимание, что вместо ключевого слова WHERE здесь используется слово ON. Если использовать слово WHERE, результат будет тот же самый, что и с обычным запросом. Следует также помнить, что синтаксис левого внешнего соединения может сильно различаться в разных системах.
Математические функции и средства работы с датами
Поскольку SQL ориентирован на выборку данных, а не на управление вычислениями, его математический инструментарий довольно ограничен. Впрочем, перечень доступных функций в продуктах различных разработчиков может варьироваться. Как правило, в большинстве реализаций присутствуют следующие функции: POWER (возведение в степень), SQRT (квадратный корень), АВS (модуль), LN и LOG10 (натуральный и десятичный логарифмы), ЕХР (экспоненциальная функция). Функция ROUND(х, р) округляет число х до р десятичных знаков, TRUNCATE(х, р) — усекает. Функции FLOOR(х) и CEILING(х) возвращают ближайшие к нецелому х целые числа снизу и сверху соответственно. Предположим, нам зачем-то понадобилось найти не площадь, а диагональ каждой спальни из таблицы Rooms и округлить ее до двух знаков после запятой. Запрос будет иметь следующий вид:
SELECT Room_id,
ROUND(SQRT(POWER(Length,2) +
POWER(Width,2)), 2) AS Bias
FROM Rooms
WHERE Room_Type = `Спальня`
На выходе у нас получится таблица из двух столбцов, содержащих информацию об идентификаторе комнаты и ее длине по диагонали.
В большинстве реализаций SQL присутствует предикат BETWEEN, который несколько облегчает работу с интервалами чисел, в частности с временными и календарными интервалами (мы столкнулись в предыдущем разделе в примере с выборкой контактов за июнь). В общем случае синтаксис предиката таков:
Val1 BETWEEN Low AND High
Предикат вернет TRUE, если значение Val1 будет находиться внутри диапазона, ограниченного значениями Low и High, или в противном случае False. Для простого формирования дат в диалектах SQL многих современных СУБД присутствует соответствующая функция MAKEDATE, которая вызывается с такими аргументами:
MAKEDATE(Year, Month, Day)
Если необходимо выполнить обратную задачу — вычленить год, месяц или день из даты, применяют оператор EXTRACT. В частности, когда нужно определить текущий год, пользуются таким выражением:
EXTRACT (YEAR FROM CURRENT_DATE)
Для того чтобы сместиться относительно некой даты на заданное количество дней, месяцев или лет, используется ключевое слово INTERVAL. Например, следующее выражение возвращает дату, смещенную на пятнадцать дней вперед относительно даты MyDate:
MyDate + INTERVAL 15 DAYS
Группы и агрегатные функции
Иногда возникают ситуации, когда необходимо произвести группировку данных, отбросив ненужную индивидуальную информацию, зато добавив количественные оценки групп. Для этого в SQL есть оператор GROUP BY.
Допустим, что нам необходимо получить из таблицы Contracts данные относительно количества контрактов и общего объема продаж, приходящихся на одного агента. Каждая запись в таблице Contracts описывает один контракт. Одному агенту может соответствовать несколько таких записей. Следовательно, чтобы получить нужный результат, надо сгруппировать таблицу по полю «Ag_Num», содержащему индекс агента:
SELECT Agent_id, SUM(Gross_Income) AS
Gr_Income, COUNT(*) AS Contracts_Num
FROM Contracts
GROUP BY Agent_id
В результирующей таблице будет три столбца: в первом — номер агента, во втором — сумма всех заключенным им контрактов, в третьем — количество этих контрактов. Функции SUM, COUNT (а также AVG, MIN и МАХ) называются агрегатными. Их отличие от математических функций состоит в том, что аргументом может быть произвольное множество чисел. В нашем случае функция SUM складывает все значения Gross_Income для каждой отдельной группы, а COUNT(*) подсчитывает количество записей в ней. Усложним пример, чтобы увидеть использование функции AVG (вычисления среднего арифметического).
Начальнику отдела продаж нужно внимательно следить за отстающими — теми, чей объем продаж ниже среднего уровня. Для этого ему следует написать такой запрос:
SELECT Name, Gr_Income
FROM Agents AS A1, (SELECT Agent_id,
SUM(Gross_Income)
FROM Contracts
GROUP BY Agent_id) AS T1(Agent_id,
Gr_Income)
WHERE A1.Agent_id = T1.Agent_id, Gr_Income<
AVG (T1.Gr_Income)
В раздел FROM вложен уже знакомый нам (но слегка сокращенный) запрос, занимающийся компоновкой. С помощью ключевого слова АS мы даем временной таблице его результатов и столбцам этой таблицы символьные имена, чтобы сослаться на них в основном запросе. Интересующий нас столбец «Объем контрактов на одного работника» называется теперь Gr_Income. А дальше в разделе WHERE основного запроса мы отбираем тех агентов, у которых это значение ниже среднего.
Оператор CASE
Иногда бывает необходимо прямо в ходе выполнения запроса преобразовывать символьные данные в числовые, и наоборот. В предыдущей заметке мы рассмотрели простейший случай компоновки, когда для вычисления общих параметров достаточно было просуммировать значения, содержавшиеся в группируемых записях. Но не все значения можно просуммировать. Предположим, что мы имеем дело со школьной ведомостью School_Sheet, в которой содержится информация относительно идентификатора ученика (Pupil_id), его имени (Name), пола (Gender) и класса, в котором он учится (Group_id). Если теперь возникнет задача сгруппировать детей по классам и определить, сколько человек учится в том или ином классе, то мы уже знаем, как это делать:
SELECT Group_id, COUNT(*) AS Total
FROM School_Sheet
GROUP BY Group_id
Однако куда бежать и за что хвататься, если нужно подсчитать, сколько в каждом классе мальчиков и девочек? Здесь к нам и придет на помощь оператор CASE:
SELECT Group_id,
SUM (CASE WHEN Gender=`M` THEN 1 ELSE
0)AS Boys,
SUM (CASE WHEN Gender = `F` THEN 1 ELSE
0)AS Girls,
Boys +Girls AS Total
FROM School_Sheet
GROUP BY Group_id
На каждой записи оператор CASE возвращает 0 или 1 в зависимости от содержимого поля Gender — следовательно, на единицу увеличивается счетчик мальчиков или счетчик девочек. Возможна и другая нотация для записи оператора CASE. Допустим, нам необходимо перевести буквенные оценки знаний учащихся в цифровые для нахождения среднего бала. Соответствующий оператор перевода будет записан так:
CASE Mark WHEN A THEN 5
WHEN B THEN 4
WHEN C THEN 3
WHEN D THEN 2
WHEN E THEN 1
Некоторые системы не поддерживают оператор CASE. Обойти эту проблему можно с помощью таблиц соответствия. В нашем примере это будет таблица Convert_Table с полями «NMark» и «LMark», содержащими цифровой и буквенный варианты. Если в исходной таблице с оценками School_Marks значения прописаны в символьной форме, то конверсию можно осуществить так:
SELECT Name, Discipline, NMark
FROM School_Marks AS S1, Convert_Table AS C1
WHERE S1.Mark = C1.LMark
Создание таблиц и манипуляции с данными
Возможности SQL выходят за пределы одного лишь составления запросов. С его помощью можно создавать новые таблицы, добавлять, обновлять и удалять данные. Преимущество перед ручным редактированием таблиц с помощью оболочки СУБД очевидно: редактирование осуществляется автоматически по заданным правилам при минимальном участии оператора — а значит, очень быстро и без ошибок. Очень важная область применения автоматических манипуляций данными — построение промежуточных таблиц. В системах, не в полной мере поддерживающих SQL-92, часто возникают ситуации, когда результат промежуточного запроса необходимо сохранить в новой таблице. Таблица создается с помощью оператора CREATE TABLE, после чего в скобках указываются наименования и типы полей: