Создание запросов и фильтров. Вычисление при помощи оператора SELECT. Встроенные функции

Цели:

1. Изучить создание запросов и фильтров
2. Понять процесс выполнения вычислений при помощи оператора SELECT. Встроенные функции

Создание запросов и фильтров

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

Для реализации запросов используют специальный язык запросов SQL (Structured Query Language).

В ИС Запросы могут находиться как на стороне клиентского приложения, так и на стороне сервера. Если запрос хранится на стороне клиента, то он прописывается внутри объекта связи. В этом случае клиентское приложение не зависит от файла данных. Файл данных содержит только таблицы, поэтому, мы легко можем модифицировать клиентское приложение, не затрагивая файл данных, но в этом случае запрос передается серверу через сеть, что может вызвать проблемы с безопасностью.

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

Все запросы делятся на:

1. статические;
2. динамические

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

Замечание. Обычно динамические запросы могут быть реализованы только при помощи запросов, выполняющихся на стороне клиента. Если необходимо реализовать динамические запросы, которые выполняются на стороне сервера, то в этом случае необходимо использовать хранимые процедуры.

Хранимые процедуры - SQL запрос, хранимый на стороне сервера и этот запрос имеет параметры, которые подставляются внутрь SQL кода. При вызове хранимой процедуры необходимо передавать в нее значения параметра.

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

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

Существует четыре вида связи между таблицами:

1. Одна к одной - одному полю в первичной таблице соответствует одно поле во вторичной таблице;
2. Одна ко многим - одному полю в первичной таблице соответствует несколько полей во вторичной таблице;
3. Многие к одной - нескольким полям в первичной таблице соответствует одно поле во вторичной таблице;
4. Многие ко многим - одному полю в первичной таблице соответствует несколько полей во вторичной таблице и наоборот.

Запросы с первым видами связи называются простыми, а с остальными видами связи - сложными. Если в БД есть хотя бы две связанных таблицы, то БД называется реляционной.

Чтобы создать запрос необходимо сделать активной БД для которой создается запрос, затем в рабочей области редактора запросов создать запрос с помощью команды SELECT, имеющей следующий синтаксис:

SELECT [ALL|DISTINCT]
[TOP|PERCENT n]
<Список полей>
[INTO <Имя новой таблицы>]
[FROM  <Имя таблицы >]
[WHERE <Условие>]
[GROUP BY <Поле>]
[ORDER BY <Поле > [ASC|DESC]]
[COMPUTE AVG|COUNT|MAX|MIN|SUM(<Выражение>)]

Здесь параметры ALL|DISTINCT показывают, какие записи обрабатываются: ALL обрабатывает все записи, DISTINCT только уникальные, удаляются повторения записей.

TOP n определяет какое количество записей обрабатывают, если указан PERCENT, то n указывает процент от общего числа записей. <Список полей> - здесь указываются отображаемые поля из таблиц через запятую.

Замечания:

1. Если имена отображаемых полей в разных таблицах не повторяются, то мы можем указывать только имена столбцов или полей без указания самих таблиц (ФИО, Должность). Если отображаются поля из разных таблиц с одинаковыми именами нужно указывать и имя таблицы <Имя поля>. <Имя таблицы> ;
2. Здесь же можно присваивать псевдонимы полям, следующим образом <Имя поля> AS <Псевдоним>
3. Если необходимо вывести все поля из таблицы, то их можно заменить значком "*"

Раздел INTO. Если присутствует этот раздел, то на основе результатов запроса создается новая таблица.

Раздел FROM. Здесь указываются таблицы и запросы, через запятую, которые участвуют в новом запросе.

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

FROM <Таблица1> INNER JOIN <Таблица2> ON <Таблица1>.<поле1> оператор <Таблица2>.<поле2> …

Здесь устанавливается взаимосвязь Таблицы 1 и Таблицы2 по Полю1 и Полю2 в зависимости от оператора сравнения. Таких разделов INNER JOIN может быть сколько угодно.

Раздел WHERE. Данный раздел используют для создания простых запросов, в этом случае в качестве условия указываем связываемые поля, либо этот раздел используют для создания фильтров, здесь указывают условия отбора. В условиях отбора мы можем использовать стандартные логические операторы NOT, OR, AND.

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

Раздел GROUP BY - определяет поле для группировки записей в запросе.

Раздел ORDER BY - определяет поле для сортировки записей в запросе. Если указан параметр ASC, то будет производиться сортировка по возрастанию, если DESC - по убыванию. По умолчанию используется сортировка по возрастанию.

Раздел COMPUTE позволяет в конце результатов выполнения запроса вывести некоторые итоговые вычисления по запросу. Возможны следующие виды вычислений: AVG - средняя параметра; COUNT - количество значений параметра не равных NULL ; MAX и MIN - максимальные и минимальные значения параметра; SUM - сумма всех значений параметра, где <Выражение> - сам параметр. В качестве параметра обычно выступают какие-либо поля таблиц, участвующих в запросе.

Пример: Данный запрос связывает две таблицы Сотрудники и Должности по полям Код. При своем выполнении он отображает первые 20 процентов сотрудников из обеих таблиц. Из таблицы сотрудники отображаются все поля, а из таблицы Должности только поле должность. В конце результатов выводится количество отображенных сотрудников.

SELECT TOP 20 PERCENT *. Cотрудники, Должность.Должности
FROM Сотрудники, Должности
WHERE Код.Сотрудники = Код.Должности
COMPUTE COUNT (ФИО.Сотрудники)

Пример: Данный запрос из таблицы Операции выводит все записи, значение поля Месяц у которых равняется "Май". Данные в результате группируются по полю операция и сортируются по сумме операции. В конце результатов запроса отображается общая сумма отобранных операций за май. Результаты данного запроса сохраняются в таблице "Сделки за май".

SELECT ALL Операция, Сумма
INTO [Сделки за Май]
FROM Операции
WHERE Месяц = 'Май'
GROUP BY Операция
ORDER BY Сумма
COMPUTE SUM (Сумма)

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