Запросы SQL и их использование

СУБД ACCESS

Практическое занятие № 3.

Тема: “Технология конструирования запросов ”

Цель занятия:

• Научиться создавать различные виды запросов в СУБД ACCESS
• Ознакомиться с основными инструкциями языка структурирования запросов SQL.

Инструкция/строка SQL - выражение, определяющее команду SQL, например, SELECT, UPDATE или DELETE, и включающее предложения, например, WHERE или ORDER BY. Инструкции/строки SQL обычно используются в запросах и в статистических функциях.

Запросы SQL и их использование

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

Запрос на объединение

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

Запрос к серверу

Запросы этого типа отправляют команды непосредственно в базы данных ODBC, такие как Microsoft FoxPro, причем используются только команды, поддерживаемые сервером. Например, запрос к серверу можно использовать для загрузки записей или изменения данных.

Управляющий запрос

Запросы данного типа создают, удаляют и изменяют таблицы или создают индексы в базах данных, таких как таблицы Microsoft Access или Microsoft FoxPro.

Подчиненный запрос

Запрос этого типа представляет собой инструкцию SQL SELECT, вложенную в запрос на выборку или запрос на изменение. Чтобы определить новое поле, данную инструкцию можно ввести в строку Поле в бланке запроса. Чтобы указать для данного поля условие отбора, введите инструкцию в строку Условие отбора. Подчиненные запросы используются для:

• проверки наличия результатов подчиненного запроса (используются зарезервированные слова EXISTS или NOT EXISTS);
• поиска значений в основном запросе, которые равны, превышают или меньше значений, возвращаемых подчиненным запросом (используются зарезервированные слова ANY, IN или ALL);
• создания подчиненных запросов внутри подчиненных запросов (вложенные запросы).

Примеры выражений в инструкциях SQL

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

Выражение Результат SELECT [Имя],[Фамилия] FROM [Сотрудники] WHERE [Фамилия]="Сидоров"; Выводит значения полей «Имя» и «Фамилия» для сотрудников, имеющих фамилию Сидоров. SELECT [КодТовара],[Марка] FROM [Товары] WHERE [КодТипа]=Forms![Новые товары]![КодТипа]; Выводит значения полей «КодТовара» и «Марка» из таблицы «Товары» для записей, у которых значение поля «КодТипа» совпадает со значением элемента управления «КодТипа» в открытой форме «Новые товары». SELECT Avg([ОтпускнаяЦена]) AS [Средняя отпускная цена] FROM [Сведения о заказах] WHERE [Отпускная цена]>1000; Вычисляет среднюю отпускную цену для заказов, у которых значение в поле «ОтпускнаяЦена» превышает 1000, и выводит их в поле «Средняя отпускная цена». SELECT [КодТипа],Count([КодТовара]) AS [КодКоличестваТоваров] FROM [Товары] GROUP BY [КодТипа] HAVING Count([КодТовара])>10; Выводит в поле «КодКоличестваТоваров» общее количество товаров для типов, имеющих более 10 товаров.

Технология конструирования запросов по образцу(QBE)

Конструирование однотабличного запроса на выборку

Рассмотрим технологию конструирования однотабличного запроса на выборку на примере получения информации из таблицы ПРЕДМЕТ базы данных Учебный процесс.

SELECT Предмет.НП, Предмет.Часы, Предмет.Лек, Предмет.ЧС

FROM Предмет

WHERE (((Предмет.Часы)<=100) AND ((Предмет.Лек)<>0)) OR (((Предмет.Часы)>150) AND ((Предмет.ЧС)<3));

Рис.4.1. Окно конструктора запроса на выборку с логическими операциями в условии отбора

Рис.4.2. Окно конструктора запроса на выборку с использованием в условии отбора выражений с именами полей

Microsoft Access выводит эквивалентную инструкцию SQL для запроса, созданного в режиме конструктора (Вид/Режим SQL)

FROM Предмет

WHERE (((Предмет.Часы)<>[ЛЕК]+[ПР]));

SELECT СТУДЕНТ.ФИО, Предмет.НП, Успеваемость.ОЦЕНКА

FROM (СТУДЕНТ INNER JOIN Успеваемость ON (СТУДЕНТ.НС = Успеваемость.НС) AND (СТУДЕНТ.НГ = Успеваемость.НГ)) INNER JOIN Предмет ON Успеваемость.КП = Предмет.КП

WHERE (((СТУДЕНТ.ФИО)=[Фамилия и инициалы студента]) AND ((Предмет.НП)=[Наименование предмета]));

Замечания. При использовании в условии отбора имен полей разных таблиц, помимо имени самого поля необходимо указать имя таблицы. Например, для обращения к подюЧАСЫ таблицы ИЗУЧЕНИЕ необходимо в условии отбора использовать следующее выражение :[ИЗУЧЕНИЕ]![ЧАСЫ].

FROM Предмет

WHERE ((([Часы]-[ПР]-[Лек])<>0));

Замечание. Для формирования сложного выражения в вычисляемом поле целесообразно использовать построитель выражений.

FROM СТУДЕНТ

GROUP BY СТУДЕНТ.НГ;

Группа Count-НС

Группа Фактическое число студентов

Рис.4.7.Таблицы результата запроса

Создать запрос на выборку на основе двух таблиц ГРУППА и ЧИСЛО СТУДЕНТОВ

Последовательность действий

Создать запрос на выборку на основе двух таблиц ГРУППА и СТУДЕНТ

Перечень заданий

Задание 1. Подсчитать средний проходной балл в группе. Сохранить запрос под именем СРБАЛЛ. Привести эквивалентную инструкцию SQL.

Задание 2. Выполнить расчет студентов и среднего проходного балла в группе в одном запросе. Сохранить запрос под именем Число студентов и средний ПБАЛЛ группы. Привести эквивалентную инструкцию SQL.

Задание 3. Подсчитать число студентов в группах с проходным баллом большим, чем 4,5. Привести эквивалентную инструкцию SQL.

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

Задание 5. Произвести обновление поля ПБАЛЛ( средний проходной балл) в таблице ГРУППА значениями из ранее созданной таблицы СРБАЛЛ.

СУБД ACCESS

Практическое занятие № 3.

Тема: “Технология конструирования запросов ”

Цель занятия:

• Научиться создавать различные виды запросов в СУБД ACCESS
• Ознакомиться с основными инструкциями языка структурирования запросов SQL.

Инструкция/строка SQL - выражение, определяющее команду SQL, например, SELECT, UPDATE или DELETE, и включающее предложения, например, WHERE или ORDER BY. Инструкции/строки SQL обычно используются в запросах и в статистических функциях.

Запросы SQL и их использование

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

Запрос на объединение

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

Запрос к серверу

Запросы этого типа отправляют команды непосредственно в базы данных ODBC, такие как Microsoft FoxPro, причем используются только команды, поддерживаемые сервером. Например, запрос к серверу можно использовать для загрузки записей или изменения данных.

Управляющий запрос

Запросы данного типа создают, удаляют и изменяют таблицы или создают индексы в базах данных, таких как таблицы Microsoft Access или Microsoft FoxPro.

Подчиненный запрос

Запрос этого типа представляет собой инструкцию SQL SELECT, вложенную в запрос на выборку или запрос на изменение. Чтобы определить новое поле, данную инструкцию можно ввести в строку Поле в бланке запроса. Чтобы указать для данного поля условие отбора, введите инструкцию в строку Условие отбора. Подчиненные запросы используются для:

• проверки наличия результатов подчиненного запроса (используются зарезервированные слова EXISTS или NOT EXISTS);
• поиска значений в основном запросе, которые равны, превышают или меньше значений, возвращаемых подчиненным запросом (используются зарезервированные слова ANY, IN или ALL);
• создания подчиненных запросов внутри подчиненных запросов (вложенные запросы).

Примеры выражений в инструкциях SQL

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

Выражение Результат SELECT [Имя],[Фамилия] FROM [Сотрудники] WHERE [Фамилия]="Сидоров"; Выводит значения полей «Имя» и «Фамилия» для сотрудников, имеющих фамилию Сидоров. SELECT [КодТовара],[Марка] FROM [Товары] WHERE [КодТипа]=Forms![Новые товары]![КодТипа]; Выводит значения полей «КодТовара» и «Марка» из таблицы «Товары» для записей, у которых значение поля «КодТипа» совпадает со значением элемента управления «КодТипа» в открытой форме «Новые товары». SELECT Avg([ОтпускнаяЦена]) AS [Средняя отпускная цена] FROM [Сведения о заказах] WHERE [Отпускная цена]>1000; Вычисляет среднюю отпускную цену для заказов, у которых значение в поле «ОтпускнаяЦена» превышает 1000, и выводит их в поле «Средняя отпускная цена». SELECT [КодТипа],Count([КодТовара]) AS [КодКоличестваТоваров] FROM [Товары] GROUP BY [КодТипа] HAVING Count([КодТовара])>10; Выводит в поле «КодКоличестваТоваров» общее количество товаров для типов, имеющих более 10 товаров.