Выборка данных: оператор SELECT
Для формирования запросов на выборку данных в SQL используется оператор SELECT. Его формат представлен ниже:
SELECT [ ALL | DISTINCT ] select_item_cominalist FROM table_reference_commalist [ WHERE conditional_expression ]
[ GROUP BY column_name_commalist ]
[ ORDER BY order_item_commalist ]
SELECT является достаточно сложным оператором, позволяющим выбирать данные из одной или нескольких таблиц, выполнять группировку, обработку данных с помощью агрегатных функций, формировать вложенные запросы и т.д. Выражение SELECT обрабатывается целиком, а не «построчно», как обычно бывает в языках программирования. В несколько обобщенном виде схема выполнения оператора SELECT выглядит следующим образом:
- 1) выполняется раздел FROM;
- 2) выполняется раздел WHERE (если есть);
- 3) выполняется GROUP BY (если есть);
- 4) выполняется HAVING (если есть);
- 5) выполняются определения в разделе SELECT;
- 6) выполняется ORDER BY (если есть).
Начнем с рассмотрения обязательного раздела SELECT. В нем указывается список элементов выборки select-item-commalist, который не должен быть пустым. Также может использоваться ключевое слово ALL или DISTINCT. Первое из них указывает, что в результате запроса могут быть повторяющиеся строки, второе – что повторения отбрасываются. Например, используется ключевое слово DISTINCT и есть три совпадающих строки, тогда в результате из них останется только одна. Когда явно ничего не указано, то подразумевается ALL.
Вернемся к списку элементов выборки. В нем через запятую могут указываться имена столбцов, константы, операции над столбцами или функции, возвращающие скалярное значение. Кроме того, может использоваться символ «*», обозначающий все столбцы, или .*, что указывает на все столбцы данной таблицы.
Перед тем как перейти к рассмотрению примеров, необходимо отметить, что в разделе FROM указываются таблицы, из которых делается выборка. Если через запятую перечислены несколько таблиц, это соответствует их декартову произведению. Следующий запрос позволит получить содержимое всей таблицы Т1:
SELECT * FROM T1 Аналогичный результат даст запрос SELECT Tl.* FROM Т1
Пусть имеется таблица Students, аналогичная представленной в табл. 7.2. Получить перечень номеров студенческих групп без повторения позволит следующий запрос:
SELECT DISTINCT [Group] FROM Students
По поводу этого запроса надо отметить следующее. Во-первых, предполагается, что номер группы может упоминаться в таблице несколько раз. Поэтому для отбрасывания повторений явно указывается ключевое слово DISTINCT. Если бы в перечне столбцов был первичный или альтернативный ключ, это обеспечило бы уникальность строк в результате запроса и DISTINCT можно было бы опустить. Во-вторых, название столбца Group совпадает с названием инструкции SQL. Поэтому в большинстве случаев потребуется явно указать в СУБД, что речь идет о названии столбца. В частности, для MS SQL Server надо будет использовать двойные кавычки или квадратные скобки: [Group].
Рассмотрим пример с заданием имени столбца и использованием текстовой константы в столбце. Если необходимо явно указать, как столбец будет называться в выводимых результатах запроса, это можно сделать в списке элементов выборки в разделе SELECT. Новое имя указывается после исходного названия столбца через пробел или после необязательного ключевого слова «as». Ниже приведен пример, в котором список фамилий и инициалов студентов сопровождается подписью «Фамилия и инициалы»:
SELECT DISTINCT ‘Фамилия и инициалы’ as Labell, FIO FROM Students
Подпись задается с помощью строковой константы, которые в SQL берутся в одинарные кавычки. Называться столбец с подписью будет Labell. Результат выполнения этого запроса для набора данных из табл. 7.2 представлен в табл. 7.6. Как отмечалось выше, ключевое слово «as» в SELECT можно пропустить, но иногда оно позволяет сделать текст на SQL более понятным.
Результат запроса
Фамилия и инициалы
Фамилия и инициалы
Фамилия и инициалы
Приведем еще один пример. Пусть в таблице Т находится информация о товарах и их ценах в долларах (столбец PriceUSD). Необходимо вывести идентификатор товара Id и цену в рублях, пересчитав ее по курсу 31 рубль за доллар и назвав столбец PriceRUB. Соответствующий запрос приведен ниже:
SELECT Id, PriceUSD*31 as PriceRUB FROM T Как уже отмечалось выше, раздел FROM содержит список таблиц, данные из которых будут выбираться. Также там могут указываться имена представлений (апгл. view), речь о которых пойдет далее в этой главе. Тема соединения таблиц также будет подробно рассмотрена в параграфе 7.6. Сейчас же рассмотрим пример задания и использования псевдонима таблицы. Пусть необходимо вывести все сочетания номеров студенческих билетов (т.е. сочетания «каждый с каждым»). Для этого надо взять столбцы из результата декартова произведения таблицы Students на саму себя, а чтобы отличать в полученном результате столбцы друг от друга, потребуется ввести псевдоним для таблицы в разделе FROM. Как и в случае названия столбцов, псевдоним для таблицы задается через пробел или с использованием ключевого слова «as»:
SELECT Students.StudID, S.StudID FROM Students, Students S
Чтобы лучше разобраться в работе с псевдонимами таблиц, рассмотрим два внешне похожих запроса:
Select * FROM Т2, Т1 и
Select Tl.* FROM Т2 Т1
Первый запрос выводит результат декартова произведения таблиц Т2 и Т1. Во втором запросе таблице Т2 задается псевдоним Т1, после чего из нее выбираются все данные. Здесь работа идет только с одной таблицей.
Также надо отметить, что производная таблица, из которой выбираются данные, может быть определена прямо в разделе FROM, то есть в SQL допустимы конструкции, подобные приведенной ниже:
FROM (Select Id, . FROM MyTabl . ) as T
Перейдем к рассмотрению раздела WHERE. В соответствии с описанным выше порядком выполнения запроса после обработки инструкций в разделе FROM будет сформировано некоторое множество строк. Если в запросе присутствует раздел WHERE, то из этого множества будут отобраны только те строки, для которых приведенное в данном разделе логическое условие даст истину. При этом само условие может быть как простым, так и состоящим из нескольких, связанных логическими операторами NOT, AND, OR (отрицание, логическое «и», логическое «или»). Если раздел WHERE пропущен, дальнейшая обработка будет выполняться для всех строк.
FROM Students WHERE [Group]=382
Сравнение может производиться не только для чисел, но и для строковых значений (посимвольно), дат и т.д.
Если необходимо получить все значения из интервала, можно использовать конструкцию BETWEEN . AND. . Пусть имеется таблица Book с информацией о книгах, и в ней целочисленный столбец Year, содержащий год издания книги. Получить все книги, изданные с 1990 по 2002 г. включительно, можно с помощью приведенного ниже запроса (поскольку «Year» для MS SQL Server является ключевым словом, название столбца снова в квадратных скобках):
WHERE [Year] BETWEEN 1990 AND 2002 Аналогичный результат даст запрос SELECT *
WHERE [Year]>=1990 AND [Year]
Если, наоборот, нужны все книги, кроме изданных в этот период, можно использовать конструкцию NOT BETWEEN:
WHERE [Year] NOT BETWEEN 1990 AND 2002 Если нужно проверить определено или нет значение столбца, используется условие IS [NOT] NULL. Тут необходимо напомнить, что NULL означает «не определено», поэтому обычное сравнение с NULL может дать, на первый взгляд, неожиданный результат. Так, проверка условия 1 = 1 всегда даст значение «истина», а условие NULL = NULL должно давать «не определено». Например, если атрибут «цвет машины» у двух машин не определен (в столбце значение NULL), это не означает ни что они одинакового цвета, ни что они разных цветов. В каких-то СУБД могут быть особенности в интерпретации подобного условия, например может генерироваться ошибка. Поэтому, если нужно получить список студентов, номер группы которых не задан, корректный запрос будет выглядеть так:
SELECT * FROM Students WHERE [Group] IS NULL Проверить принадлежность значения столбца некоторому множеству можно с помощью условия вида
Здесь множество может быть явно задано или формироваться в результате запроса. Следующий запрос выводит строки из таблицы Т1, которые относятся к перечисленным в скобках городам:
SELECT * FROM T1
WHERE CityName IN (‘Санкт-Петербург’, ‘Псков’,
Если надо получить строки, в которых поле CityName имеет значение, не использовавшееся в таблице Т2, запрос будет следующим:
SELECT * FROM T1
WHERE CityName NOT IN (
SELECT DISTINCT CityName FROM T2)
Вложенные запросы (подзапросы) будут более подробно рассмотрены далее. Сейчас хотелось бы обратить внимание на то, что в запросах, подобных приведенному выше, подзапрос должен возвращать только один столбец. Например, следующий запрос в большинстве СУБД приведет к ошибке:
SELECT * FROM T1
WHERE CityName NOT IN (
SELECT Id, CityName FROM T2)
При работе с вложенными запросами (подзапросами) также используется инструкция [NOT] EXISTS (). EXISTS возвращает значение «истина» (true), если результаты подзапроса содержат хотя бы одну строку. Если подзапрос возвратит пустое множество строк, EXISTS примет значение «ложь» (false). Примеры использования этой конструкции будут приведены далее.
При работе с символьными строками часто требуется найти строку, соответствующую некоторому шаблону. Например, в столбце хранятся фамилия и инициалы, а нужно выбрать всех Ивановых. Подобные задачи можно решать, используя в разделе WHERE оператор сравнения с шаблоном LIKE. Его формат:
При описании шаблона в предикате LIKE можно использовать символ «%», обозначающий строку любого размера, включая пустую строку, и символ «_» – любой одиночный символ. Шаблон заключается в одинарные кавычки. Таким образом, если из таблицы Students надо выбрать записи о студентах, чья фамилия начинается на «Ив», запрос будет выглядеть так: SELECT * FROM Students WHERE FIO LIKE ‘Ив%’
Ключевое слово ESCAPE позволяет задать символьную последовательность, «экранирующую» символ подстановки. Когда в заданном с помощью LIKE шаблоне встречается эта последовательность, следующий за ней символ подстановки рассматривается как обычный символ. Например, требуется из таблицы DevTab выбрать строки, в которых в столбце DevName второй символ – «%».
Если в качестве экранирующей последовательности использовать восклицательный знак, запрос будет выглядеть следующим образом:
SELECT * FROM DevTab
WHERE DevName LIKE ‘_!%%’ ESCAPE ‘!’
В ряде диалектов SQL возможности LIKE дополнительно расширяются. Например, MS SQL Server 2008 допускает использование шаблона в виде списка или диапазона символов. Квадратные скобки со списком символов означают один символ из заданных в списке, с диапазоном символов – символ из заданного диапазона; символ ^ и список или диапазон символов – один любой символ, кроме указанных.
Следующий запрос выведет записи о студентах, чья фамилия начинается с букв от «А» до «И»:
SELECT * FROM Students WHERE FIO LIKE ‘[А-И]%’
Если нужно, чтобы фамилия не начиналась на «И» или «С», маска будет LIKE ‘[^ИС]%’.
Перейдем к рассмотрению следующего раздела – GROUP BY:
GROUP BY column-commalist.
Если в запросе присутствует этот раздел, то полученная в результате выполнения инструкций разделов FROM и WHERE таблица будет перегруппирована таким образом, чтобы каждое сочетание значений столбцов в группе встречалось только один раз.
Рассмотрим пример. Пусть исходная таблица RESULTS выглядит так, как представлено в табл. 7.7. После выполнения инструкции GROUP BY StudlD таблица будет перегруппирована так, как представлено в табл. 7.8.
В общем случае группированная таблица не соответствует требованиям 1НФ (значения столбцов не атомарные), и она не может быть выведена в качестве результата запроса.
Таблица RESULTS
Какие опции могут быть использованы в операторах выборки данных
Оператор SELECT является одним из важных и часто используемых операторов языка SQL. Он предназначен для выборки информации из таблиц базы данных.
Оператор SELECT состоит из нескольких предложений. Некоторые из предложений являются обязательными, другие – нет.
Предложение обычно состоит из ключевого слова Зарезервированное слово, являющееся частью языка SQL и предоставляемых данных.
Упрощенный синтаксис оператора SELECT выглядит следующим образом:
Выборка нескольких столбцов
Чтобы при помощи оператора SELECT извлечь данные из таблицы, нужно указать как минимум две вещи — что вы хотите выбрать и откуда.
Ключевое слово SELECT сообщает базе данных, что данное предложение является запросом на извлечение информации. После SELECT через запятую перечисляются названия полей, содержимое которых запрашивается. Обязательным ключевым словом в предложении-запросе SELECT является слово FROM. За ключевым словом FROM указывается список разделенных запятыми имен таблиц, из которых извлекается информация.
SQL:
SELECT name, lastname
FROM tbl_clients;
Результатом выполнения запроса на выборку всегда является таблица, содержащая выбранные записи. Приведенный выше запрос осуществляет выборку всех значений полей name и lastname из таблицы tbl_clients.
Порядок следования столбцов в этой таблице соответствует порядку полей, указанному в запросе, а не их порядку в исходной таблице.
Выборка всех столбцов таблицы
Помимо возможности осуществлять выборку определенных столбцов (одного или нескольких), при помощи оператора SELECT можно запросить все столбцы, не перечисляя каждый из них. Для этого вместо имен столбцов вставляется групповой символ «звездочка» (*).
SQL:
SELECT * FROM tbl_clients;
Представленный оператор извлекает все столбцы из таблицы tbl_clients и отображает их в соответствии с порядком, в котором они были определены при создании таблицы. При использовании символа «*» столбцы возвращаются в том порядке, в котором они были определены при создании таблицы.
Исключение дубликатов строк
Получаемые в результате SQL-запроса таблицы могут содержать повторяющиеся строки. Например, запрос для получения списка названий городов, где проживают клиенты, можно записать в следующем виде:
SQL:
SELECT region FROM tbl_clients
Его результатом будет таблица:
| region |
| Portland |
| Seattle |
| Seattle |
| New Jersey |
| Seattle |
| Seattle |
| Washington |
| Los Angeles |
| . |
Проблема состоит в том, что в набор результатов этого запроса включается каждое появление каждого названия города, обнаруженного в таблице tbl_clients. Например, если имеется 20 человек из, Сиэтла, 7 человек из Вашингтона и 3 из Лос-Анджелеса, то в наборе результатов будет 20 раз упомянут Сиэтл, 7 раз Вашингтон и 3-Вашингтон. Очевидно, что эта избыточная информация не нужна.
Для исключения из результата запроса повторяющихся записей используется ключевое слово DISTINCT. Если запрос SELECT извлекает множество полей, то DISTINCT исключает дубликаты строк, в которых значения всех выбранных полей идентичны.
Предыдущий запрос можно записать в следующем виде:
SQL:
SELECT DISTINCT region FROM tbl_clients
В результате получим таблицу, в которой дубликаты строк исключены:
| region |
| Portland |
| Seattle |
| New Jersey |
| Washington |
| Los Angeles |
| California |
| Oregon |
| New York |
Какие опции могут быть использованы в операторах выборки данных
Основным действием выполняемым с помощью SQL запросов в системе ZuluGIS является выборка данных для их вывода в виде таблицы в области результатов запроса. Выборка данных производится с помощью ключевого слова SELECT , после которого задаются параметры выборки.
Как правило, выборка данных производится из записей полей БД слоев карты, но ключевое слово SELECT также может использоваться для вывода в поле результатов вычисления произвольных выражений, приведенных в строке после SELECT . Можно одновременно вывести результаты вычисления нескольких выражений, перечислив их через запятую.
Например, команда SELECT «Результаты вычисления», 2+2 , выведет в области результатов таблицу из двух ячеек с данными « Результаты вычисления » и 4 .
Строка команды выборки состоит из трех основных частей, в которых задается какие поля таблиц выводятся в итоговую таблицу, из каких слоев берутся данные и по каким условиям отбираются данные для итоговой таблицы.
При запросе данных из одного слоя карты, для использования в команде выборки полей данных достаточно указывать только их названия. Если же в выборке используются поля из нескольких слоев, то названия полей требуется указывать в формате . .
Типовой запрос имеет следующий вид:
SELECT [ FROM ] [ WHERE ]
SELECT
Часть команды выборки в которой задаются колонки выводимой таблицы данных. В области через запятую перечисляются названия полей, значения которых выводятся в колонках таблицы, либо выражения, результаты расчета которых выводятся в таблице («Операторы и функции языка SQL»).
Для вывода в таблице значений всех полей данных из указанных в запросе слоев, задайте вместо списка полей символ « * » . В таком случае, значения полей в таблице будут выводиться в том же порядке, в котором они заданы в БД.
Для того чтобы в итоговой таблице выводились только отличающиеся друг от друга записи, задайте списком полей ключевое слово DISTINCT («Избавление от повторяющихся записей»).
FROM
Часть строки выборки в которой через запятую перечисляются слои карты из которых запрашиваются данные.
Если все поля в запросе указаны в формате . , часть запроса с ключевым словом FROM может быть опущена.
Если данные запрашиваются из более чем одного слоя карты, в итоговой таблице будет выведено декартово пересечение записей запрошенных слоев. Например, в случае запроса полей из двух слоев в итоговой таблице будет набор записей со всеми возможными комбинациями полей из записей первого и второго слоя, т.е., например при запросе поля А из слоя содержащего 2 записи и запросе поля B из слоя также содержащего две записи, в итоговой таблице будет четыре записи со следующими данными: A1 + B1 , A1 + B2 , A2 + B1 , A2 + B2 .
WHERE
Часть, в которой задаются условия, в соответствии с которыми отбираются записи данных в таблицу результатов.
В качестве условий могут использоваться операции сравнения, проверки равенства, вхождения значений полей в заданный диапазон, проверки относительного расположения элементов и т.д. Подробно синтаксис условий будет рассмотрен далее.
Если в таблице результатов требуется вывести все записи для указанных полей, эта часть запроса может быть опущена.
Также в команде выборки могут использоваться различные дополнительные команды, рассматриваемые в последующих подразделах.
Примеры выборок
Простейшая выборка
SELECT * FROM Кварталы
В результате данного запроса выводится таблицу со всеми записями данных об объектах слоя Кварталы , причем в таблице выводятся все доступные поля данных слоя.
Рисунок 712. Пример выполнения запроса
Команда выборки с перечислением требуемых полей
SELECT Sys, perimeter, [Количество этажей] FROM Здания
В результате запроса выводится таблица с полями Sys , perimeter , Количество этажей всех записей слоя Здания .
Команда выборки без FROM части
SELECT Здания.Sys, Здания.Адрес
В данном запросе для всех полей явно указан используемый слой, поэтому нет необходимости дополнительно указывать слой с помощью ключевого слова FROM .
Выборка с отбором по условию
SELECT [Номер дома] FROM Здания WHERE Улица='5й Южный пер.'
В результате данного запроса выводится таблица со значениями поля Номер дома для всех записей слоя Здания у которых значение поля Улица равняется строке 5й Южный пер. .
Выборка по нескольким слоям
SELECT Кварталы.sys, Здания.Улица + " " + Здания.[Номер дома] FROM Здания, Кварталы WHERE Здания.Geometry.STWithin(Кварталы.Geometry)
В результате такого запроса будут отобраны объекты слоя Здания располагаются в объектах слоя Квартал и будет выведена таблица из двух столбцов, в первом из которых выводятся поля Sys объектов слоя Квартал , а во втором — адреса зданий в слое Здания помещающихся в указанных объектах слоя Квартал . Используемая в данном запросе конструкция Здания.Geometry.STWithin(Кварталы.Geometry) проверяет, не располагается ли объект слоя Здания внутри объекта слоя Кварталы (подробнее «Работа с пространственными данными в запросах»).