Приложение 3. Предложение IN применяется ко всем таблицам, указанным в предложении FROM и в подчиненных запросах

Предложение IN применяется ко всем таблицам, указанным в предложении FROM и в подчиненных запросах. В запросе можно ссылаться только на одну внешнюю БД. Если необходимо обращение к нескольким БД и файлам, то они присоединяются как таблицы (см ЛР N3), и используются уже как присоединенные.

Если для ODBS не указать DSN и DATABASE в строке подключения, то ACCESS выводит диалоговое окно с перечнем доступных источников данных и дает возможность выбрать нужный.

Если опустить UID и PWD, а сервер данных их требует, то ACCESS выводит окно регистрации пользователя.

Для баз dBASE, FoxPro, Paradox и Brieve можно вместо <Имени_ба-зы_данных_источника> ввести пустую строку "" и указать путь или имя

Файла с помощью параметра DATABASE в <строке_подклю-чения_источника_данных>.

Создадим запрос, выводящий таблицу "Результаты" из удаленной базы "Fil_1", содержащую список результатов пошедшей сессии, причем результатов, которые получены в течении сессии.

SELECT Результаты.Номер_С, Результаты.Предмет,

Результаты.Оценка_П, Результаты.Оценка_Т, Результаты.Оценка_Л,

Результаты.Дата_сдачи,Результаты.Задолженность

FROM Результаты

IN 'FIL_1.MDB'

WHERE (Результаты.Дата_Сдачи<=[Введите дату отбора] OR Результаты.Задолженность=0)

ORDER BY Результаты.Номер_С;

Сохраним запрос под именем "Результаты прошедшей сессии".

2.8. Запросы объединения инструкций SELECT.

При необходимости создания результирующей таблицы, содержащей строки нескольких инструкций SELECT, применяется операция UNION. Эта возможность не поддерживается QBE, и реализуется только в режиме SQL.

Синтаксис операции UNION:

инструкция_SELECT

UNION [ALL]

инструкция_SELECT

[ORDER BY { имя_столбца [ ASC | DESC ] },...]

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

Не рекомендуется использовать предикат ORDER BY внутри инструкции SELECT, но можно его использовать после последней такой инструкции. В качестве имен столбцов используются имена из первой инструкции SELECT.

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

SELECT Результаты.Номер_С, Результаты.Предмет,

Результаты.Оценка_Т,

Результаты.Оценка_П, Результаты.Оценка_Л,

Результаты.Задолженность, Результаты.Дата_сдачи

FROM Результаты

IN 'FIL_1.MDB'

UNION

SELECT Результаты.Номер_С, Результаты.Предмет,

Результаты.Оценка_Т, Результаты.Оценка_П, Результаты.Оценка_Л,

Результаты.Задолженность,

Результаты.Дата_сдачи

FROM Результаты

ORDER BY Номер_С;

Сохраним запрос под именем "Объединенные результаты".

2.9. Перекрестные запросы на SQL.

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

Синтаксис инструкции TRANSFORM:

TRANSFORM выражение_с_итоговой_функцией

<инструкция_SELECT>

PIVOT выражение

где <выражение_с_итоговой_функцией> − выражение, использующее одну из итоговых функций. Оно определяет значения, которые должны появиться в ячейках выходной (перекрестной) таблицы.

Выражение <инструкция_SELECT> должна содержать предложение GROUP BY.

Конструкция <PIVOT выражение> определяет столбец или выражение, значения которого используются в качестве заголовков столбцов перекрестной таблицы.

При задании условий группировки для строк можно использовать несколько

столбцов или выражений.

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

TRANSFORM MAX(INT((Результаты.Оценка_Т+Результаты.Оценка_П+ Результаты.Оценка_Л)/З))

SELECT Результаты.Номер_С

FROM Результаты

GROUP BY Результаты.Номер_С

PIVOT Предмет;

Запросы можно строить не только на основе данных таблиц, но и по данным, которые возвращает запрос. В этом случае имя вызываемого запроса указывается в квадратных скобках. Так, при необходимости сформировать сетку успеваемости за учебный год по прошедшему и текущему семестрах, SQL не дает возможности напрямую произвести объединение таблиц в перекрестном запросе. Приходится создавать запрос на объединение таблиц − "Объединенные результаты", а затем создаваемый перекрестный запрос "Сетка успеваемости за год", который использует результаты уже запроса-объединения:

TRANSFORM MAX(INT((Результаты.Оценка_Т+Результаты.Оценка_П+ Результаты.Оценка_Л)/))

SELECT [Объединенные результаты].Номер_С

FROM [Объединенные Результаты]

GROUP BY [Объединенные результаты].Номер_С

PIVOT Предмет;

2.10. Запросы − действия на SQL.

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

2.10.1. Запрос на создание таблицы.

Инструкция SELECT...INTO создает новую таблицу, используя значения, выбранные из одной или нескольких таблиц. Обычно этот тип запросов применяется при резервировании данных таблицы, или для вывода отчетов за определенный период.

Синтаксис команды:

SELECT [ALL | DISTINCT | DISTINCTROW | TOP число [PERCENT]] список_выбора

INTO имя_новой_таблицы

FROM {{имя_таблицы [[AS] псевдоним] |

имя_запроса_выборки [[AS] псевдоним]} |

<таблица_объединение> }

[WHERE условие_отбора]

[GROUP BY имя_столбца,...]

[HAVING условие_отбора]

[UNION [ALL] инструкция_выбора]

[ORDER BY { имя_столбца [ ASC | DESC ] },...

IN<"имя_базы_источника_данных"> <[строка_подключения_источника_дан-ных]>

Модификация вставляемых данных возможна только в режиме

SELECT DISTINCTROW.

Данный запрос создает новую таблицу с именем, задаваемым в <имя_новой_таблицы>. Если таблица с таким именем уже существует ACCESS запрашивает, удалять ли старую перед созданием новой.

Создадим запрос "Результат по практике".

SELECT DISTINCTROW Фамилия, Имя, Отчество, Студенты.Номер_С,

Count(Результат.Оценка_П) AS Практика

FROM Студенты

INNER JOIN Результаты

ON Студенты.Номер_С = Результаты.Номер_С

WHERE ((Результаты.Предмет=[Введите предмет])

GROUP BY Фамилия, Имя, Отчество, Студенты.Номер_С;

После этого создадим запрос, который будет по полученной выборке формировать таблицу "Результат":

SELECT DISTINCTROW [Результат по практике].*

INTO [Результат]

FROM [Результат по практике];

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

2.10.2 Запросы на модификацию данных с использованием SQL.

Инструкция UPDATE позволяет изменять данные в заданных таблицах или запросах. При необходимости можно задавать условия для выбора строк, над которыми будет производиться модификация.

Синтаксис команды UPDATE:

UPDATE { { имя_таблицы [[AS] псевдоним ] |

имя_запроса_выборки [[AS] псевдоним ] } |

<таблица_объединение> },...

SET { имя_столбца = { выражение | NULL } },...

[WHERE условие_отбора]

Модификация вставляемых данных возможна только в режиме

SELECT DISTINCTROW.

Если необходимо внести изменения в связанные таблицы, то изменять содержимое столбцов можно только в таблице со стороны "многие" при связи "один_ко_многим". При связи "один_к_одному" можно обновлять столбцы любой таблицы.

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

Для перерасчета оценок по 40 бальной системе в созданной таблице "Результат" с учетом параметрически заданного коэффициента создадим запрос "Перерасчет результатов":

UPDATE DISTINCTROW [Результат]

SET [Результат].Практика =

[Результат]![Практика]*[Коэффициент новой системы];

Название таблицы заключено в квадратные скобки, так как содержит в себе пробелы.

2.10.3 Запросы на удаление данных на SQL.

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

Синтаксис команды DELETE:

DELETE [список_выбора]

FROM [{ имя_таблицы [[AS] псевдоним ] |

имя_запроса_выборки [[AS] псевдоним ] } |

<таблица_объединения> },...

[WHERE условие_отбора]

где <список_выбора> есть:

[ * | имя_таблицы.* | список_столбцов ]

Создадим запрос, удаляющий из таблицы "Результат" записи для студентов, которые имеют по практике отрицательные оценки.

DELETE DISTINCTROW [Результат].Практика

FROM [Результат]

WHERE (([Результат].Практика=[Введите оценку]));

Сохраним ее под именем "Положительные результаты”, и запустим на выполнение.

2.10.4 Запрос на добавление данных в таблицу.

Инструкция INSERT вставляет одну или несколько строк в указанную таблицу или запрос.

Синтаксис команды:

INSERT INTO имя_таблицы [({имя_столбца},...)]

{VALUES ({литерал},...) | инструкция_выбора}

Если не заданы имена столбцов, то ACCESS вставляет значения во все столбцы в том порядке, в каком они определены в описании таблицы. Если же имена столбцов заданы, то данные заносятся в поля в той − же последовательности, как и определены. Характеристики столбцов и вводимых данных должны совпадать.

Когда используется предложение VALUES, то вставляется единственная

строка. INSERT можно комбинировать с SELECT, и строки, возвращаемые SELECT будут вставлены в объект-приемник для SELECT.

В список столбцов необходимо занести все те из них, для которых условие

"Обязательное поле" установлено в "Да".

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

Создадим запрос, дополняющий таблицу "Результаты" новыми записями. Новые данные будут формироваться по той-же выборке − "Результат по практике", но по другому предмету.

INSERT INTO [Результат]

SELECT DISTINCTROW [Результат по практике].*

FROM [Результат по практике];

Сохраним его под именем "Дополнение результатов по практике".