Лабораторная работа « Основы языка SQL» (стр. 3 )

SELECT StrConv(Продукт,1) AS [Наименование продукта], ЕдИзм AS [Единица измерения]

FROM Продукты;

Результат такого запроса показан на рис. 3.7.

Рисунок 3.7. Результат запроса с указанием формата представления информации

Выбор данных из более чем одной таблицы

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

SELECT { * | [table1.]field1 [AS alias1] [,  [table2.]field2 [AS alias2] [, ...]]}

FROM  table1  [table1Alias]  [, table2 [table2Alias]  [,  ...] [WHERE criteria]

Например (Запрос 6):

SELECT Продукты. Продукт AS [Наименование продукта], Продукты. ЕдИзм AS [Единица измерения]

FROM Продукты;

Или (Запрос 7)

SELECT tab.Продукт AS [Наименование продукта], tab.ЕдИзм AS [Единица измерения]

FROM Продукты tab;

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

Следующая инструкция позволяет получить наименования (из таблицы Продукты) и количества (из таблицы Продажи) продуктов (результат выполнения — на рис. 3.8):

SELECT Продукты. Продукт AS [Наименование продукта], Продажи. Количество AS [Количество]

FROM Продукты, Продажи

WHERE Продукты. КодПрод = Продажи. КодПрод;

Рисунок 3.8. Выборка из двух таблиц

Обратите внимание на выражение Продукты. КодПрод = Продажи. КодПрод.

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

Результат выполнения предыдущей инструкции напоминает инвентаризационную ведомость без указания даты продажи. Следующая инструкция позволяет получить наименования и количества только для тех продуктов, которые проданы после заданной даты (рис. 3.9):

SELECT Продукты. Продукт AS [Наименование продукта], Продажи. Количество AS [Количество]

FROM Продукты, Продажи

WHERE Продажи. ДатаПродажи > #3/25/2003# AND Продукты. КодПрод = Продажи. КодПрод;

Рисунок 3.9. Выборка из двух таблиц с ограничением по дате

Обратите внимание на формат ограничения даты. Он не совпадает с форматом, использовавшимся при заполнении таблицы.

При помощи локальных псевдонимов можно сократить предыдущую инструкцию (Запрос10).

SELECT a.Продукт AS [Наименование продукта], b.Количество AS [Количество]

FROM Продукты a, Продажи b

WHERE b.ДатаПродажи > #3/25/2003# AND a.КодПрод = b.КодПрод;

Вывод выбранных данных в определенном порядке

Для сортировки данных в инструкции SELECT имеются слова ORDER BY:

SELECT { *  |  [table.]field1 [AS  alias1]  [,  [table.] field2  [AS  alias2] I,  .-.]]}

FROM table1 [table1Alias]  [,  table2  [table2Alias]  [,  ...]

[WHERE criteria]

[ORDER  BY  field1  [ASC  I  DESC  ][,  field2  [ASC  |  DESC  ]][,  ...]]]

Здесь к тем элементам SQL-запроса, которые уже рассмотрены, добавлено необязательное предложение ORDER BY. Как следует из синтаксиса инструкции SELECT, используя слова ASC и DESC, можно изменять «направление» сортировки («по возрастанию» и «по убыванию»). Сортировать можно по нескольким полям (сначала по одному, затем — по другому, и так далее) и даже по различным элементам одного и того же поля с использованием функций.

В следующей инструкции используется сортировка выводимого набора в порядке убывания количества продаж (Запрос11):

SELECT a.Продукт AS [Наименование продукта], b.Количество AS [Количество]

FROM Продукты a, Продажи b

WHERE b.ДатаПродажи > #3/25/2003# AND a.КодПрод = b.КодПрод

ORDER BY а. Продукт DESC;

Агрегирующие функции в инструкции SELECT

В инструкциях языка ANSI SQL предусмотрены так называемые агрегирующие функции, которые определяют количество записей, вычисляют суммы всех значений полей в наборе, находят минимальные или максимальные, а также средние значения. К агрегирующим функциям относятся функции COUNT, SUM, MAX, MIN и AVG.

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

SELECT COUNT(Продукт) AS [Всего наименований]

FROM Продукты;

На рис. 3.10 показан результат выполнения этого запроса.

Рисунок 3.10. Подсчет количества продуктов

Функция AVG в инструкции SELECT позволяет найти среднее значение для строк, входящих в запрос. Например, следующая инструкция определяет среднюю цену продажи для сделок, совершенных после указанной даты (Запрос13):

SELECT Format(AVG (ЦенаПродажи), "###  ##0.00р") AS [Средняя цена]

FROM Продажи

WHERE  ДатаПродажи > #3/25/2003#;

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

Группировка данных в инструкции SELECT

Часто в наборах, получаемых при помощи SELECT-инструкций, встречаются повторяющиеся значения. Для рассматриваемой нами базы данных это могут быть, например, наименования продуктов в запросе (рис. 3.8). Такие повторяющиеся значения можно объединять в группы, используя для групп агрегирующие функции.

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

Следующая инструкция имеет результатом список проданных продуктов и их суммарные количества (результат — на рис. 3.11):

SELECT a.Продукт AS [Наименование продукта], SUM(b.Количество) AS [Количество]

FROM Продукты a, Продажи b

WHERE b.ДатаПродажи > #3/25/2003# AND a.КодПрод = b.КодПрод

GROUP BY a. Продукт;

Рисунок 3.11. Группировка объема продаж по наименованиям продуктов

Предположим, что нам нужна информация о суммарном количестве только тех продуктов, для которых количество продаж меньше определенного числа. Чтобы выполнить этот запрос, мы не можем использовать предложение WHERE, так как оно «работает» со значениями данных, расположенными в таблице, а нам нужно наложить некоторые ограничения на вычисляемые данные. Для решения подобной задачи следует использовать предложение HAVING, которое так же связано с предложением GROUP BY, как WHERE сSELECT. Другими словами, предложение HAVING налагает некоторые условия на выбранные посредством предложения GROUP BY данные.

Следующая инструкция позволяет получить список продуктов и суммарное количество продаж. Причем в список включаются только те продукты, для которых суммарное количество продаж меньше чем 20 (результат — на рис. 3.12):

SELECT a.Продукт AS [Наименование продукта], SUM(b.Количество) AS [Количество]