Лабораторная работа «Microsoft Access. Создание SQL-запросов»

Лабораторная  работа «Microsoft Access. Создание SQL-запросов»

Тема: Microsoft Access. Создание SQL-запросов.

Цель работы: изучить правила создания различных запросов с использованием структурированного языка запросов–SQL.

Формирование запросов SQL

SQL (Structure Query Language – Структурированный Язык Запросов) – стандартный язык запросов для работы с базами данных. SQL содержит примерно 40 операторов для выполнения различных действий внутри СУБД. Рассмотрим некоторые из них. Категория операторов Data Manipulation Language (DML) содержит операторы, позволяющие выбирать, добавлять, удалять и модифицировать данные. Операторы DML представлены в таблице 1.

Таблица 1

Операторы языка SQL категории DML

Оператор	Описание
SELECT	Применяется для  выбора данных
INSERT	Применяется для  добавления строк к таблице
DELETE	Применяется для  удаления строк из таблицы
UPDATE	Применяется для  изменения данных

Иногда  оператор SELECT относят к отдельной  категории, называемой Data Query Language (DQL).

Примеры использования SQL языка в СУБД Microsoft Access будут приводиться на основе базы данных Магазин. Схема базы данных приведена на рисунке 1.

 

Рис. 1. Схема данных БД Магазин

 

При использовании SQL языка в СУБД Microsoft Access название таблиц отделяется от названия полей точкой или восклицательным знаком. Если название поля или таблицы состоит из нескольких слов, разделенных пробелом, то название берется в квадратные скобки.

При создании запроса на основе полей  нескольких таблиц данные таблицы необходимо объединить. Существует несколько правил объединения. Рассмотрим внешнее соединение на основе INNER JOIN. Синтаксис данного объединения двух таблиц:

…

From<таблица_1> INNER JOIN<таблица_2> ON таблица_1.Поле_связи = таблица_2. Поле_связи

…

 

Синтаксис объединения трех таблиц:

…

From<таблица_1> INNER JOIN<таблица_2> (<таблица_2> INNER JOIN<таблица_3> ON таблица_2.Поле_связи = таблица_3. Поле_связи) ON таблица_1.Поле_связи = таблица_2. Поле_связи

…

Пример 1. Показать информацию о заказах с номером счета и датой счета.

SELECT Заказ. КодЗаказа, Заказ. НазваниеКомпании, Заказ. ДатаИсполнения, Счет. КодСчета, Счет. ДатаСчета

FROM Заказ INNER JOIN Счет ON Заказ.КодЗаказа = Счет.[Код заказа];

 

Рис. 2. Пример запроса с объединением двух таблиц

 

Оператор SELECT

Формат оператора SELECT:

Select [all |*|distinct] <список полей>

From<список таблиц>

Where<условия выборки строк>

Group by<список полей группировки>

Having<условия для групп>

Order by<список полей для сортировки>

 

Особенности использования  предложения SELECT

1. Вычисления  в запросах и переименование  столбцов.

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

Пример 2: Показать общую стоимость каждого заказа.

SELECT Заказ.КодЗаказа, Заказ.НазваниеКомпании, Заказ.ДатаИсполнения, Товары.ТипТовара, Товары.МаркаТовара, Заказ.Количества, Товары.Цена, Заказ.СтоимостьДоставки, (Товары.Цена * Заказ.Количества) + Заказ.СтоимостьДоставки AS [Общая стоимость заказа]

FROM Товары INNER JOIN Заказ ON Товары.КодТовара = Заказ.КодТовара;

 

 

Рис. 3. Пример запроса с вычисляемым полем

 

2. Использование  операции сцепление строк.

Операция & соединяет два строковых значения, которые могут быть представлены выражениями: <строковое значение 1> & <строковое значение 2>.

Пример 3: Показать информацию о сотрудниках с указанием их домашних телефонов:

SELECT [Сотрудники]![Фамилия] & " " & [Сотрудники]![Имя] & " (домашний_телефон " & [Сотрудники]![ДомашнийТелефон] & ")" AS Информация

FROM Сотрудники;

Рис. 4. Пример запроса с операцией сцепления строк

3. Устранение повторяющихся значений производится через ключевое слово DISTINCT.

Пример 4: Показать все заказанные товары.

SELECT DISTINCT Товары.ТипТовара, Товары.МаркаТовара

FROM Товары INNER JOIN Заказ ON Товары.КодТовара = Заказ.КодТовара;

4. Отображение  всех полей таблицы возможно  при использовании * или ALL.

Пример 5: Показать информацию о товарах.

SELECT Товары.*

FROM Товары;

или

SELECT Товары. All

FROM Товары;

 

 

Особенности использования  предложения WHERE

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

 

 

 

Таблица 2

Операторы отношения

Оператор	Описание
<	Меньше
<=	Меньше или равно
<>	Не равно
=	Равно
>	Больше
>=	Больше или равно

Пример 6: Показать информацию о товарах, цена которых более 100.

SELECT Товары.КодТовара, Товары.ТипТовара, Товары.МаркаТовара, Товары.Цена

FROM Товары

WHERE Товары.Цена>100;

 

2. Использование  диапазона значений возможно  через операторы отношения или  через оператор BETWEEN.

Пример 7: Показать информацию о товарах цена которых от 100 до 1000 включительно.

SELECT Товары.*

FROM Товары

WHERE Товары.Цена>=100 and Товары.Цена<=1000;

или

SELECT Товары.*

FROM Товары

WHERE Товары.Цена BETWEEN 100 and 1000;

 

3. Использование логических операций  NOT, OR, AND.

Пример 8: Показать информацию о директорах и менеджерах.

SELECT Сотрудники.*

FROM Сотрудники

WHERE (Сотрудники.Должность="Директор") OR (Сотрудники. Должность = "Менеджер");

Порядок выполнения логических операций NOT®AND®OR.

4. Использование  выражения 'IS NOT NULL', означающего, что соответствующая колонка результирующего набора данных не может содержать пустых значений.

Пример 9: Показать всех сотрудников, имеющих домашние телефоны.

SELECT Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Должность, Сотрудники.ДомашнийТелефон

FROM Сотрудники

WHERE Сотрудники.ДомашнийТелефон Is Not Null;

 

Пример 10: Показать всех сотрудников, не имеющих домашних телефонов.

SELECT Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Должность, Сотрудники.ДомашнийТелефон

FROM Сотрудники

WHERE Сотрудники.ДомашнийТелефон Is Null;

 

5. Использование  оператора сравнения с указанным  образцом LIKE для создания запросов с масками. Символы замещения:

* замещает  несколько символов;

? замещает  один символ.

Пример 11. Показать всех сотрудников, фамилии которых начинаются на букву К.

SELECT Сотрудники.*

FROM Сотрудники

WHERE Сотрудники.Фамилия Like "К*";

 

6. Использование  оператора IN для сравнения значения поля со списком.

Пример 12: Показать всех покупателей, которые приобрели товары типа журнал, книга, комикс.

SELECT Заказ.НазваниеКомпании, Товары.ТипТовара

FROM Товары INNER JOIN Заказ ON Товары.КодТовара = Заказ.КодТовара

WHERE Товары.ТипТовара In ("книга","комикс","журнал");

 

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

Таблица 3

Агрегирующие  функции

Функция	Назначение
AVG	Вычисляет среднее  значение
COUNT	Вычисляет количество непустых значений
MAX	Вычисляет наибольшее значение
MIN	Вычисляет наименьшее значение
SUM	Вычисляет сумму  значений

 

Пример 13: Показать доход за услугу Доставка.

SELECT Sum(Заказ.СтоимостьДоставки) AS [Общая стоимость доставки]

FROM Заказ;

 

Сортировка результатов  запроса

Определение сортировки происходит при помощи предложения  ORDER BY. По умолчанию данные сортируются в порядке возрастания (ключевое слово ASC) можно не ставить. Для изменения сортировки в порядке убывания ставится слово DESC.

Пример 14: Показать все товары в порядке возрастания цены.

SELECT Товары.*

FROM Товары

ORDER BY Товары.Цена;

 

Пример 15: Показать все товары в порядке убывания цены.

SELECT Товары.*

FROM Товары

ORDER BY Товары.Цена DESC;

 

Группировка записей 

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

Пример 16: Показать количество продаж по каждому типу товара.

SELECT Товары.ТипТовара, Sum(Заказ.Количества) AS [Общее Количество]

FROM Товары INNER JOIN Заказ ON Товары.КодТовара = Заказ.КодТовара

GROUP BY Товары.ТипТовара;

 

 

Рис. 5. Пример запроса с группировкой данных

 

Ограничение на группировку  записей

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

Предложение HAVING работает аналогично предложению WHERE, но применяется не к строкам, а к группам строк. Предложение HAVING накладывает условие на полученные группы. Если HAVING использует несколько условий, то они соединяются оператором OR или AND.

Пример 17: Показать количество продаж по каждому типу товара, если количество продаж не превышает 1000.

SELECT Товары.ТипТовара, Sum(Заказ.Количества) AS [Общее Количество]

FROM Товары INNER JOIN Заказ ON Товары.КодТовара = Заказ.КодТовара

GROUP BY Товары.ТипТовара

HAVING Sum(Заказ.Количества)<1000;

 

Рис. 6. Пример запроса с группировкой данных и использованием предложения HAVING

 

Объединение однотипных запросов

При необходимости  объединения нескольких однотипных результирующих наборов данных используют оператор UNION.

Пример 18: Показать цену товаров и цену товаров со скидкой. Скидка на товары, цена которых > 100 р. – 3%, остальных – 5%.

SELECT Товары.КодТовара, Товары.ТипТовара, Товары.МаркаТовара, Товары.Цена, [Товары]![Цена]*0.03 AS [Цена со скидкой]

FROM Товары

WHERE Товары.Цена>100

UNION

SELECT Товары.КодТовара, Товары.ТипТовара, Товары.МаркаТовара, Товары.Цена, [Товары]![Цена]*0.05 AS [Цена со скидкой]

FROM Товары

WHERE Товары.Цена<=100;

 

Структурированные запросы  или вложенные запросы

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

• предложение SELECT;
• предложение WHERE;
• предложение HAVING;
• оператор INSERT;
• оператор UPDATE;
• оператор DELETE.

Пример синтаксиса подзапроса, вложенного в предложение SELECT.

Select <список полей>

From<список таблиц>

Where<условия выборки строк>

(Select <список полей>

From<список таблиц>

Where<условия выборки строк>)

 

Пример 19: Найти покупателя самого большого заказа.

SELECT Заказ.НазваниеКомпании, Заказ.СтоимостьДоставки + Заказ.Количества * Товары.Цена AS [Стоимость заказа]

FROM Товары INNER JOIN Заказ ON Товары.КодТовара = Заказ.КодТовара

WHERE Заказ.СтоимостьДоставки+Заказ.Количества*Товары.Цена=

(SELECT MAX( Заказ.СтоимостьДоставки + Заказ.Количества * Товары.Цена) AS [Стоимость заказа]

FROM Товары INNER JOIN Заказ ON Товары.КодТовара = Заказ.КодТовара);

 

Запросы на удаление

Оператор  DELETE предназначен для удаления группы записей из объекта. Удаляются все записи из объекта, удовлетворяющие условию поиска. Если опустить WHERE <условия поиска>, из объекта будут удалены все записи.