Язык запросов
Самый простой запрос – запрос на выбор всех записей из одной таблицы
«SELECT * FROM Автор»
Этот запрос вернет набор всех строк таблицы «Автор», причем в каждой строке имеются все поля таблицы.
Можно задать выбор не всех полей, а только некоторых. Например, в нашей задаче нет требования показа адреса автора. Тогда мы можем использовать следующий запрос:
«SELECT Имя, Телефон, Код FROM Автор»
Как видите, порядок полей тоже можно изменять. Если мы получаем данные с удаленного компьютера, то сокращение объема передаваемой по сети информации увеличивает производительность системы и усиливает защищенность (т.к. не передаются, возможно, конфиденциальные сведения).
Можно задать выбор не всех строк таблицы. Так нам может потребоваться запрос, возвращающий книги некоторого жанра. Запрос
«SELECT Название, Объем, Автор FROM Книга WHERE Жанр=”детектив”»
вернет все строки таблицы «Книга», в поле «Жанр» которых стоит значение «детектив». Обратите внимание на то, что в выражении where указано поле, не входящее в список полей select. То есть это поле есть в исходной таблице, как раз по значению этого поля происходит отбор записей, но в конечный набор его нет смысла включать, т.к. его значение у всех записей выборки одинаковое.
В полученном наборе записей вместо фамилии автора имеется его код, что совершенно не приемлемо для нормального пользователя, но мы к этому вернемся чуть позже.
В выражении раздела where можно использовать логические операции, операции сравнения и множественные операции. Перечислим их: равно =, не равно < >, больше >, меньше <, меньше либо равно <=, больше либо равно >=, и AND, или OR, между BETWEEN, похоже LIKE, содержится во множестве IN.
Так как в поле «Автор» таблицы «Книга» содержатся коды авторов (коды записей таблицы «Автор»), то следующий запрос вернет все детективы, написанные автором, код которого равен 2.
«SELECT Название, Объем FROM Книга WHERE Жанр=”детектив” AND Автор=2»
Маловероятно, что подобная форма запроса подойдет пользователю. Скорее всего, пользователь задаст фамилию автора книг. Но фамилии хранятся в другой таблице! Таким образом, нам необходим запрос, одновременно выбирающий данные из двух таблиц. Получить список всех детективов с фамилиями авторов можно следующим образом:
«SELECT Автор.Имя, Книга.Название, Книга.Объем FROM Книга, Автор WHERE Книга.Жанр=”детектив” AND Автор.Код=Книга.Автор»
В этом запросе три отличительных новшества. Во-первых, имена полей теперь специфицированные, то есть дополненные именем таблицы, из которой они взяты. И это неудивительно, если подумать о том, что имена полей в разных таблицах могут совпадать, как, например поле с именем «Код» в нашем случае. Второй важный момент состоит в том, что если мы выбираем последовательно строки из таблицы «Книга» и хотим присоединить к выбранным полям поле из другой таблицы («Автор»), то нам необходимо указать правило нахождения соответствующей записи в таблице «Автор». Это правило тоже может быть записано в разделе WHERE, в нашем примере это следующее: Автор.Код=Книга.Автор. А для того чтобы составить сложное выражение нам потребовалась логическая операция AND – третий важный момент.
Очевидно, что можно выбирать данные и из трех (и более) таблиц. Построим запрос, возвращающий список авторов и их книг, изданных в 2008 году с указанием тиража.
«SELECT Автор.Имя, Книга.Название, Издание.Тираж FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008»
Такой список выглядит не очень хорошо потому, что он никак не упорядочен. По-видимому, удобнее видеть рядом все книги одного автора, а авторов хочется упорядочить по фамилии. Таким образом, нам требуется инструкция ORDER BY. В диалектах SQL могут устанавливаться ограничения на то, что следует за этой директивой. Иногда это – только набор полей, а иногда – произвольное выражение, включающее даже вами написанные функции. Хотя произвольные выражения обычно гораздо дольше вычисляются.
«SELECT Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008 ORDER BY авт, назв»
В приведенном запросе записи упорядочены сначала по имени автора, а потом по названию книги. Можно упорядочить записи по возрастанию и по убыванию. Обратите внимание на то, что используются так называемые псевдонимы, например, «авт» для «Автор.Имя». Чаще всего псевдонимы применяют для сокращения имени поля. Псевдоним совершенно необходим в том случае, когда в запросе указывается вычисляемое поле. В следующем запросе мы получим объем изданных страниц по каждой книге, равный объему книги в страницах, умноженному на объем тиража в штуках книг.
«SELECT Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж * Книга.Объем AS квостраниц FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008 ORDER BY квостраниц DESC»
Упорядочивать можно по возрастанию и по убыванию, в последнем случае необходимо указать слово DESC, в первом можно (но не обязательно) указать ASC. Направление сортировки указывается после каждого выражения для упорядочивания.
Теперь составим запрос, возвращающий список всех авторов, книги которых были изданы в 2008 году. В предыдущем запросе мы указывали названия всех книг, считали страницы. Теперь все это не нужно.
«SELECT Автор.Имя AS авт FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008 ORDER BY авт»
Запрос выше вернет почти то, что нам необходимо. Если же в 2008 году издавались две или более книг некоторого автора, то его фамилия встретится два и более раз. Это нам не нужно. Избавиться от лишних записей помогает директива DISTINCT (единственный).
«SELECT DISTINCT Автор.Имя AS авт FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008 ORDER BY авт»
Директива DISTINCT обеспечивает уникальность каждой записи в выходном наборе. Причем уникальность определяется по полям, указанным в SELECT. Иногда необходимо узнать, различались ли исходные записи. В этом случае следует использовать директиву DISTINCT ROW. В результате последнего рассмотренного нами запроса будет получаться одна строка и в случае, если в 2008 году несколько раз издавалась одна и та же книга, и в случае, если издавались разные книги одного автора. Если же использовать директиву DISTINCT ROW, то во втором случае появятся несколько строк в результате (столько, сколько разных книг одного автора издавались в 2008 году).
По умолчанию часто используется директива ALL (все записи), хотя она может быть указана явно. Есть еще директива TOP n, позволяющая отобрать n первых записей. С ее помощью можно, например, выбрать 10 самых дешевых товаров или 25 книг с самым большим тиражом, изданных нашим издательством.
«SELECT TOP 25 Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж AS трж FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор ORDER BY трж DESC»
Директива JOIN позволяет извлекать данные сразу из нескольких таблиц. Она появилась в стандарте ISO92. Ее можно рассматривать как расширение возможностей, предоставляемых директивой WHERE. Мы уже неоднократно обращались к нескольким таблицам с помощью WHERE. Но, например, последний запрос может быть записан по-другому, при помощи внутреннего объединения.
«SELECT TOP 25 Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж AS трж FROM Книга INNER JOIN Автор ON Автор.Код=Книга.Автор INNER JOIN Издание ON Книга.Код=Издание.Книга ORDER BY трж DESC»
Хотя возвращаемый результат в обоих случаях одинаков, преимуществ у последней формы записи объединения таблиц два. Во-первых, синтаксически четче указан способ, при помощи которого отыскиваются соответствующие записи в дополнительной таблице (равенство значений двух полей вынесено в отдельное синтаксическое место). Во-вторых, директива JOIN может использоваться со словами LEFT OUTER и RIGHT OUTER, задавая таким образом внешнее объединение. Слово OUTER можно при этом не писать. Попробуем разобраться с тем, что нам дает внешнее объединение. Пусть мы, составляя план издательства на следующий год, хотим получить список всех авторов, книги которых когда-либо издавались нашим издательством с указанием книги, изданной в текущем 2009 году. Если мы воспользуемся директивой WHERE или INNER JOIN, то получим список только авторов, книги которых издавались в 2009 году. Внешнее объединение позволяет выбрать все записи из основной таблицы, даже если во вспомогательной таблице не найдется соответствующих записей. В этом случае незаполненные поля выборки имеют значение null.
«SELECT Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж AS трж FROM Автор LEFT JOIN Книга ON Автор.Код=Книга.Автор LEFT JOIN Издание ON Книга.Код=Издание.Книга ORDER BY авт DESC»
Директива RIGHT JOIN объявляет, что из правой таблицы объединения должны выбираться все записи, даже, если в левой (указанной в директиве FROM) таблице не найдется соответствующих записей. Следующий оператор вернет то же самое, что и предыдущий.
«SELECT Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж AS трж FROM Издание RIGHT JOIN Книга ON Книга.Код=Издание.Книга RIGHT JOIN Автор ON Автор.Код=Книга.Автор ORDER BY авт DESC»
Имена-псевдонимы можно давать не только полям, но и таблицам. Это можно делать с целью сокращения имени. Псевдонимы для таблиц необходимы в том случае, если в запросе используется одна и та же таблица два или более раз, то есть при составлении одной выходной записи выбираются значения полей из двух или более записей одной таблицы. Чаще всего это необходимо при работе с так называемыми рекурсивными таблицами. В каждой записи рекурсивной таблицы есть ссылка на другую запись этой же таблицы. Рекурсивные таблицы используются для хранения информации об объектах, имеющих иерархическую структуру. Например, узел изделия входит как часть в другой узел. Например, велосипед состоит из рамы 1шт., колеса 2 шт., руль 1 шт. Колесо, в свою очередь, состоит из спицы 18 шт., обода 1 шт. и т.д. Еще пример, пусть есть таблица работников предприятия. Для каждого работника мы можем указать непосредственного начальника, тоже являющегося работником предприятия. Запрос, возвращающий список работников предприятия с указанием для каждого их непосредственного руководителя, дважды откроет таблицу «Работники».
«SELECT рбт.Имя, рук.Имя FROM Работник рбт INNER JOIN Работник рук ON рбт.подчиняется=рук.Код»
Обратите внимание на то, что если в последнем запросе изменить INNER на LEFT, то в результат попадет и директор предприятия. В первом же запросе мы получаем только тех работников, у которых есть начальник.
В операторе SELECT допускается использовать вычисляемые поля. Причем существуют так называемые агрегирующие функции: SUM(), MAX(), MIN() и другие. Значение этой функции считается по нескольким записям исходных таблиц. Какие это будут записи, определяется директивой GROUP BY (группировка по). Результат упорядочивается по выражению группировки. Понимать такой запрос следует так: группируются все строки, имеющие одинаковые значения в полях, указанных после GROUP BY. Запрос ниже возвратит суммарный по годам тираж нашего издательства, упорядоченный по годам.
«SELECT SUM(Издание.Тираж) AS трж FROM Издание GROUP BY Издание.Год»
Следующий запрос возвратит список книг с суммарным за все годы объемом тиража.
«SELECT Книга.Название, SUM(Издание.Тираж) AS трж FROM Издание INNER JOIN Книга ON Книга.Код=Издание.Книга GROUP BY Книга.Код»
Еще один запрос возвратит список книг с суммарным за каждый год объемом тиража. Это интересно при условии, что одна и та же книга может переиздаваться дважды и более раз в год.
«SELECT Книга.Название, SUM(Издание.Тираж) AS трж FROM Издание INNER JOIN Книга ON Книга.Код=Издание.Книга GROUP BY Книга.Код, Издание.Год»
В последних запросах мы получали все строчки. Можно из них отобрать некоторые, удовлетворяющие заданному условию. Директива WHERE для этого не подходит, т.к. она используется для отбора записей в исходных таблицах. А нам необходимо указать условие для результирующих строк. Это делается при помощи директивы HAVING. Обратите внимание на то, что директиву HAVING имеет смысл использовать в том случае, если в запросе есть агрегирующая функция. Не является синтаксической ошибкой применение этой директивы и без агрегирующей функции, тогда ее действие даст тот же результат, что и WHERE. Следующий запрос возвратит только те книги, суммарный тираж которых превышает 100000 экземпляров.
«SELECT Книга.Название, SUM(Издание.Тираж) AS трж FROM Издание INNER JOIN Книга ON Книга.Код=Издание.Книга GROUP BY Книга.Код HAVING трж>100000»
В стандарте есть еще одно объединение: объединение по строкам – UNION. Предположим, нам необходимо составить список людей, которых мы хотим поздравить с Новым годом. В этот список должны войти работники нашего предприятия (издательства), а также авторы, книги которых издавались нашим издательством.
«SELECT Имя, место_жит as Адрес FROM Работник
UNION SELECT Имя, Адрес FROM Автор»
В конкретных СУБД может быть следующее ограничение: объединяемые таблицы должны иметь одинаковую структуру: порядок и типы полей, т.к. этого требует имеющаяся реализация. Если же данные в таблицах имеют разный тип, можно попробовать составлять вычисляемые поля так, чтобы обеспечить соответствие. В некоторых СУБД не поддерживается такой подход, там можно найти выход, применяя так называемые вложенные запросы.
Немного уточним предыдущий запрос. Пусть в список поздравляемых будут внесены не все авторы, а те, книги которых чаще всего издавались нашим издательством (наши постоянные клиенты).
«SELECT Имя, место_жит as Адрес FROM Работник
UNION SELECT Имя, Адрес FROM Автор WHERE Автор.Код IN (
SELECT Авт FROM (
SELECT TOP 20 Книга.Автор as Авт, Издание.Книга, SUM(Издание.Тираж) as трж FROM Издание GROUP BY Издание.Книга INNER JOIN Книга ON Книга.Код=Издание.Книга))»
Количество вложенных запросов и уровней вложенности не ограничено.