Выборка данных. Оператор select

 

Примеры приведены на основании БД «Продажи».

 

 

Рис. 1. Таблицы БД «Продажи»

 

Рис. 2. Схема данных БД «Продажи»

Оператор SELECT определяет поля (столбцы), которые будут входить в результат выполнения запроса. В списке они разделяются запятыми и приводятся в такой очередности, в какой должны быть представлены в результате запроса. Если используется имя поля, содержащее пробелы или разделители, его следует заключить в квадратные скобки. Символом * можно выбрать все поля, а вместо имени поля применить выражение из нескольких имен. Если обрабатывается несколько таблиц, то при наличии одноименных полей в разных таблицах в списке полей используется полная спецификация поля, т.е. Имя Таблицы. Имя Поля.

Обработка элементов оператора SELECT выполняется в следующей последовательности:

Ø FROM — список таблиц базы данных, из которых будет происходить выборка данных;

Ø WHERE — выполняется фильтрация строк объекта в соответствии с заданными условиями;

Ø GROUP BY — образуются группы строк, имеющих одно и то же значение в указанном столбце;

Ø HAVING — фильтруются группы строк объекта в соответствии с указанным условием;

Ø SELECT — устанавливается, какие столбцы должны присутствовать в выходных данных;

Ø ORDER BY — определяется упорядоченность результатов выполнения операторов.

Основные типы условий поиска (или предикатов) приведены ниже.

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

2. Диапазон: проверяется, попадает ли значение столбца либо результат вычисления выражения в заданный диапазон значений.

3. Принадлежность множеству: проверяется, принадлежит ли значение столбца либо результат вычислений выражения заданному множеству значений.

4. Соответствие шаблону: проверяется, отвечает ли некоторое строковое значение заданному шаблону.

5. Значение NULL: проверяется, содержит ли данный столбец определитель NULL (неизвестное значение).

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

= равно

< меньше

>больше

<= меньше или равно

>= больше или равно

<>не равно

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

Оператор NOT IN используется для отбора любых значений, кроме тех, которые указаны в представленном списке. Например, для того чтобы вывести список клиентов, не проживающих ни в Москве, ни в Санкт-Петербурге, необходимо выполнить запрос:

SELECT Клиент, Город

FROM КЛИЕНТЫ

WHERE Город NOT IN (‘Москва’, ‘Санкт-Петербург’);

Оператор IS NULL используется для сравнения текущего значения со значением NULL — специальным значением, указывающим на отсутствие любого значения (важно подчеркнуть, что NULL — это не знак пробела или нуль). Выражение IS NOT NULL используется для проверки присутствия значения в каком-либо поле.

Например, необходимо найти клиентов, у которых не указан телефон (поле Телефон не содержит никакого значения):

SELECT Клиент

FROM КЛИЕНТЫ

WHERE Телефон IS NULL

Выборка клиентов, у которых есть телефон (поле Телефон содержит какое-либо значение):

SELECT Клиент

FROM КЛИЕНТЫ

WHERE Телефон IS NOT NULL

 

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

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

SELECT Товары.Товар, Year(Дата) AS Год, Month(Дата)

AS Месяц

FROM Товары, Продажи

WHERE Товары.Товар=Продажи.Товар;

В запросе использованы встроенные функции Year и Month длявыделения года и месяца из даты.

Агрегатные (итоговые) функции предназначены для вычисления итоговых значений операций над всеми записями набора данных. К агрегатным относятся следующие функции:

COUNT (<выражение>) - определяет количество записей в выходном наборе SQL-запроса (число вхождений значения выражения во все записи результирующего набора данных);

SUM (<выражение>) - суммирует значения выражения;

AVG (<выражение>) - эта функция позволяет рассчитать среднее значение множества значений, хранящихся в определенномполе отобранных запросом записей. Оно является арифметическим средним значением, т. е. суммой значений, деленной на ихколичество;

МАХ (<выражение>) - определяет максимальное значение;

MIN (<выражение>) -определяет минимальное значение.

Все эти функции оперируют со значениями в единственномстолбце таблицы (столбцами нескольких таблиц) или с арифметическим выражением и возвращают единственное значение. Привычислении результатов любых функций сначала исключаютсявсе пустые значения, после чего требуемая операция применяется только к оставшимся конкретным значениям столбца. Вариант COUNT (*) — особый случай использования функции COUNT, его назначение состоит в подсчете всех строк в результирующей таблице, независимо от того, содержатся там пустые, дублирующиеся или любые другие значения. В результате выполнения следующего запроса будет подсчитано количество строк в таблице ПРОДАЖИ:

SELECT Count (*) AS [Количество Продаж]

FROM Продажи;

 

Если из группы одинаковых записей нужно учитывать только одну, то перед выражением в скобках включают ключевое слово DISTINCT:

COUNT(DISTINCT Клиент);

 

DISTINCT не имеет смысла для функций MIN и МАХ, однако его использование может повлиять на результаты выполнения функций SUM и AVG, поэтому необходимо заранее обдумать, должно ли оно присутствовать в каждом конкретном случае. Кроме того, ключевое слово DISTINCT может быть указано в любом запросе не более одного раза.

Например, в следующем запросе вычисляется количество покупателей, приобретавших товары на складе:

SELECTCOUNT(DISTINCTПродажи.Клиент) AS[КоличествоКлиентов]

FROM Продажи

 

В другом примере вычисляется общая стоимость отпущенных товаров за один день:

SELECT SUM (Продажи.Количество*Товары.Цена) AS ОбщСтоимость

FROM Продажи, Товары

WHERE (Продажи.Товар=Товары.Товар) AND (Продажи.Дата=#02/15/2018#);

 

Функции COUNT, MIN, МАХ применимы как к числовым, так и нечисловым полям. Функции SUM и AVG могут использоваться только в случае числовых полей.

 

Иногда в запросах требуется формировать промежуточные итоги, т. е. получить результат выполнения функции не для всего набора данных, а для определенных групп записей (например, подсчитать итог для каждого клиента, для каждого товара, за определенную дату). Для этой цели в операторе SELECT используется предложение GROUP BY. Запрос, в котором присутствует GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT. Для каждой отдельной группы создается единственная итоговая строка. После оператора SELECT перечисляются те столбцы, которые не меняют значения в группе, т. е. участвуют в группировке. Например, группируя запрос по товарам в таблице ПРОДАЖИ, нельзя в предложении SELECT указать еще и дату, так как каждому товару могут соответствовать разные даты продажи и, следовательно, подобный запрос будет неверен:

SELECT П.Товар, П.Дата, SUM (П.Количество)

FROM ПРОДАЖИ П

GROUP BY П.Товар

 

Для того чтобы получить общее количество проданного товара, следует использовать запрос:

SELECT П.Товар, Sum(П.Количество) AS [Всего Количество]

FROM Продажи AS П

GROUP BY П.Товар;

 

А чтобы получить количество проданного товара за каждую дату, нужно откорректировать предыдущий запрос следующим образом:

SELECT П.Товар, П.Дата, SUM (П.Количество) AS [Количество в день]

FROM Продажи П

GROUPBYП.Товар, П.Дата;

Другими словами, все имена полей, приведенные в списке предложения SELECT, должны присутствовать и во фразе GROUPBY, заисключением случаев, когда имя столбца используется в агрегатнойфункции. В то же время в предложении GROUPBY могут быть имена столбцов, отсутствующие в списке предложения SELECT.

 

Еще один пример использования группировки в запросах — подсчет количества покупок, осуществленных каждым клиентом:

SELECT Клиент, COUNT (*) AS [Количество покупок]

FROM Продажи

GROUP BY Клиент;

 

Если совместно с GROUP BY in пользуется предложение WHERE, то оно обрабатывается первым, а группированию подвергаются только те строки, которые удовлетворяют условию поиска.

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

SELECT П.Товар, SUM (П.Количество*Т.Цена) AS [Сумма продажи]

FROM Продажи П, Товары Т

WHERE Т.Товар = П.Товар

GROUP BY П.Товар

 

Чтобы определить количество покупателей на каждую дату, нужно выполнить такой запрос:

SELECT Дата,COUNT(DISTINCT) AS [Количествоклиентов]

FROM Продажи

GROUP BY Дата;

 

Подзапрос (вложенный запрос) — это инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Текст подзапроса должен быть заключен в скобки. Оператор SELECT с подзапросом имеет следующий вид:

SELECT...

FROM...

WHERE Сравниваемое значение><оператор> (SELECT...)

Существует два типа подзапросов.

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

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

Подзапросы бывают:

■  некоррелированные — не содержат ссылки на запрос верхнего уровня, вычисляются один раз для запроса верхнего уровня;

■  коррелированные — содержат условия, зависящие от значений полей в основном запросе; вычисляются для каждой строки запроса верхнего уровня.

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

Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции.

Внутренние запросы могут быть помещены непосредственно после оператора сравнения (=, <, >,<=, >=, <>) в предложения WHERE и HAVING внешнего оператора SELECT. Кроме того, внутренние операторы SELECT могут применяться в операторах INSERT, UPDATE и DELETE (вставка, обновление и удаление записей).

К подзапросам применяются следующие правила и ограничения:

■  нельзя использовать ORDER BY, хотя эта фраза может присутствовать во внешнем запросе;

■  список в предложении SELECT состоит из имен отдельных столбцов или составленных из них выражений, за исключением случая, когда в подзапросе присутствует ключевое слово EXISTS;

■  по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM. Однако допускается ссылка и на столбцы таблицы, указанной во фразе FROM внешнего запроса, для чего применяются специфицированные имена столбцов (т. е. с указанием таблицы) либо их псевдонимы;

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

Следующий запрос возвращает даты, когда было продано максимальное количество товара:

SELECT Количество, Дата

FROM ПРОДАЖИ

WHERE Количество = (SELECT МАХ (Количество) FROM ПРОДАЖИ)

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

WHERE Количество = Мах(Количество),

поскольку применять обобщающие функции в предложениях WHERE запрещено.

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

SELECT П.Количество, П.Дата, К.Клиент, К.Город

FROM Продажи П, Клиенты К

WHERE (П.Клиент = К.Клиент) AND П.Количество =(SELECT MAX(Количество) FROM Продажи);

По сравнению с предыдущим примером в запрос включено внутреннее соединение таблиц ПРОДАЖИ и КЛИЕНТЫ.

Еще один пример вложенного запроса — нужно определить даты покупок, в которых количество товара превышает общее среднее значение и указать размер отклонения:

SELECT Дата, Количество, Количество-(SELECT AVG (Количество) FROM Продажи) AS Отклонение

FROM Продажи

WHERE Количество (SELECT AVG (Количество) FROM ПРОДАЖИ)

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

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

Применяемые к подзапросу операции основаны на тех операциях, которые в свою очередь применяются к множеству, а именно:

[NOT] IN;

{ALL | SOME | ANY};

[NOT] EXISTS;

Оператор IN используется для сравнения некоторого значения со списком значений, при этом проверяется, входит ли значение в предоставленный список или сравниваемое значение не является элементом представленного списка.

В следующем примере выводится список продаж клиентов, которые однажды приобрели максимальную партию какого-либо товара:

SELECT * FROM Продажи П

WHEREП.КлиентIN (SELECT П1.Клиент FROM Продажи П1

WHERE П1.Количество=(SELECTMAX(П2.Количество) FROM Продажи П2))

Разъясним логику выполнения этого запроса. Сначала определяется максимальное значение в столбце Количество:

SELECT МАХ (П2.Количество) FROM ПРОДАЖИ П2

Далее в следующем запросе определяются клиенты, осуществившие эту покупку:

SELECT П1.Клиент FROM ПРОДАЖИ П1 WHERE П1. Количество =

(SELECT МАХ (П2.Количество) FROM ПРОДАЖИ П2)

А затем основной запрос выбирает все записи с найденными клиентами из таблицы ПРОДАЖИ с помощью оператора IN, т.е. в результирующий набор данных попадают только те записи из таблицы ПРОДАЖИ, в которых значение поля КЛИЕНТ принадлежит множеству, полученному во вложенных запросах.

 

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

Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел.

Отношение сравниваемого значения и значений, возвращаемых подзапросом, устанавливается операторами ALL и SOME (ANY):

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

■  SOME (или ANY) — условие поиска истинно, когда сравниваемое значение находится в нужном отношении хотя бы с одним значением, возвращаемым подзапросом, т.е. выполняется хотя бы для одного из значений в результирующем столбце подзапроса.

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

Ключевое слово SOME является синонимом слова ANY.

Проиллюстрируем вышесказанное примерами. Определим все факты продажи товаров, в которых количество единиц продаваемого товара превышает среднее значение:

SELECT * FROMПРОДАЖИП1

WHEREП1.Количество>ALL

(SELECTAVG (П2.Количество)

FROM ПРОДАЖИ П2

GROUP BY П2.Клиент)

И еще один пример использования предложения HAVING и ключевого слова ALL. Нужно вывести сведения о клиенте, который приобрел наибольшее количество товаров:

SELECT К.*

FROM Клиенты К

WHERE К.Клиент =(SELECT П.Клиент FROM Продажи П

GROUP BY П.Клиент

HAVING SUM(П.Количество) >= ALL

(SELECT SUM (П1.Количество) FROM ПРОДАЖИП1

GROUP BY П1.Клиент))

 

Перечислим все факты продажи товаров, в которых количествоединиц продаваемого товара превышает среднее значение продажихотя бы одного товара:

SELECT * FROM ПРОДАЖИ П1

WHERE П1.Количество>SOME

(SELECTAVG (П2.Количество)

FROM ПРОДАЖИ П2

GROUPBY П2.Клиент)

 

Ключевые слова EXISTS и NOTEXISTS предназначены для использования только совместно с подзапросами. Результат их обработки представляет собой логическое значение TRUE или FALSE.

Для ключевого слова EXISTS результат равен TRUE в том итолько в том случае, если в возвращаемой подзапросом результирующей таблице присутствует хотя бы одна строка. Если результирующая таблица подзапроса пуста, результатом обработки операцииEXISTS будет значение FALSE. Для ключевого слова NOTEXISTSиспользуются правила обработки, обратные по отношению к ключевому слову EXISTS. Поскольку по ключевым словам EXISTS иNOTEXISTS проверяется лишь наличие строк в результирующейтаблице подзапроса, то эта таблица может содержать произвольноеколичество столбцов. Используя ключевое слово EXISTS, можносоставить список всех клиентов, которые хотя бы один раз приобретали товар:

SELECT *

FROMКЛИЕНТЫК

WHERE EXISTS

(SELECT П.*FROM ПРОДАЖИ ПWHEREК.Клиент = П.Клиент)

 

Для того чтобы получить список клиентов, которые не сделалини одной покупки в этот же запрос, нужно вставить слово NOT:

SELECT *

FROM КЛИЕНТЫ К

WHERE NOT EXISTS

(SELECTП.*FROMПРОДАЖИПWHEREК.Клиент = П.Клиент)

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

Изменим немного предыдущий запрос и получим совсем другой результат:

SELECT *

FROM КЛИЕНТЫК

WHERE SINGULAR

(SELECT П. ПРОДАЖИ FROM ПРОДАЖИП WHERE К.Клиент =

П.Клиент)

Результатом выполнения запроса является список всех клиентов, приобретавших только один вид товара и только один раз, т.е. в таблице ПРОДАЖИ для такого клиента присутствует только одна запись.