Материал: 3. Лабораторная работа №3

Лабораторная работа №3 «Создание запросов»

Справка

Запрос (query) – это средство выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос. Применяются два типа запросов: по образцу (QBE – Query by example) и структурированный язык запросов (SQL – Structured Query Language).

QBE-запрос по образцу – средство для отыскания необходимой информации в базе данных. Он формируется не на специальном языке, а путем заполнения бланка запроса в окне Конструктора запросов.

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

В SQL различают две основные категории операторов: язык манипулирования данными (DML) и язык описания данных(DDL).

Функции языков DDL определяются первым словом в предложении (часто называемом запросом), которое почти всегда является глаголом. В случае с SQL это глаголы(операторы) — «create» («создать»), «alter» («изменить»), «drop» («удалить»). Эти запросы или команды часто смешиваются с другими командами SQL, в связи с чем DDL не является отдельным компьютерным языком.

Запрос «create» используется для создания базы данных, таблицы, индекса, представления или хранимой процедуры. Запрос «alter» используется для изменения существующего объекта базы данных (таблицы, индекса, представления или хранимой процедуры) или самой базы данных. Запрос «drop» используется для удаления существующего объекта базы данных (таблицы, индекса, представления или хранимой процедуры) или самой базы данных. И наконец, в DDL существуют понятия первичного и внешнего ключа, которые осуществляют соблюдение целостности данных. Команды "первичный ключ" primary key, "внешний ключ" foreign key включаются в запросы «create table", «alter table».

Языки DML изначально использовались только компьютерными программами, но с появлением SQL стали также использоваться и людьми.

Функции языков DML определяются первым словом в предложении (часто называемом запросом), которое почти всегда является глаголом. В случае с SQL эти глаголы(операторы) — «select» («выбрать»), «insert» («вставить»), «update» («обновить»), и «delete» («удалить»). Это превращает природу языка в ряд обязательных утверждений (команд) к базе данных.

Языки DML могут существенно различаться у различных производителей СУБД. Существует стандарт SQL, установленный ANSI, но производители СУБД часто предлагают свои собственные «расширения» языка.

Языки DML разделяются в основном на два типа:

• Procedural DMLs — описывают действия над данными.

• Declarative DMLs — описывают сами данные.

Задания:

1. На основе результатов выполнения второй лабораторной работы выполнить построение запросов.

2. Создать не менее 9-ти вопросов с помощью мастера запросов, конструктора запросов или написанием запроса вручную, используя режим SQL.

3. Необходимо создать три запроса на выборку, три запроса на вставку и три запроса на удаление.

4. В отчете описать выбранный способ создания запросов и свести все построенные запросы в одну таблицу (форма таблицы будет описана ниже).

Ход работы:

1. Создание запросов с помощью «Конструктора запросов».

Вызвать конструктор запросов можно на вкладке «Создание» в разделе «Запросы», выбрав соответствующую иконку и нажав на него. Интерфейс примет вид как на рисунке 1. Сначала необходимо выбрать таблицы участвующие в запросы (над данными которых будут производиться операции). Выполним простой запрос на выборку данных, в котором выберем всех посетителей, которые взяли книги из библиотеки. Для этого понадобятся таблицы «Книга», «Книга_посетитель», «Посетитель». Выделяем их и нажимаем кнопку «Добавить»(рисунок 2). На вкладке «Конструктор» в разделе «Тип запроса» можно выбрать какой запрос необходимо создать: «Выборка», «Создание таблиц», «Добавление», «Обновление». По умолчанию сразу программа выбирает тип «Выборка», но тип можно изменить.

Для запроса необходимо выбрать поля, которые надо отобразить. Это можно сделать в разделе интерфейса в самом низу (вид на рисунке 3). Галочками «вывод на экран» выбираются те столбцы, которые нужно вывести в качестве результата. Так же можно добавить сортировку строк и условие выбора.

Рисунок 1

Рисунок 2

Рисунок 3

После выполнения всех необходимых процедур по формированию запроса необходимо можно выполнить запрос, нажав иконку «Выполнить» на вкладке «Конструктор» раздела «Результаты». Так же можно получить результат переключив «Режим». На рисунке 4 представлена результирующая таблица запроса.

Рисунок 4

По результату видно, что посетитель по фамилии Иванова взяла две книги.

2. Создание запросов с помощью «Мастера запросов».

При выборе «Мастера запроса» достаточно следовать по шагам:

Шаг 1: Тип запроса.

Мастер запросов предлагает первым шагом выбрать один тип запроса из 4х: Простой запрос, перекрестный запрос, повторяющийся запрос и записи без подчинений.

При переключении между типами запросов на выборку на форме появляется краткое описание каждого типа. Создадим простой запрос.

Рисунок 5

Шаг 2: Выбор таблицы или запроса.

Для построения запросов необходимо выбрать таблицу или запрос, на основе которых нужно сделать выборку. Простой запрос выполняется для одной таблицы. Выберем таблицу «Книги» и необходимые поля для отображения и нажмем кнопку «Далее».

Рисунок 6

Шаг 3:Заключительные действия

На этом шаге нужно задать имя запроса и выполнить настройку действий мастера после окончания формирования запроса. В этом примере название запроса носит имя «Книги и авторы».

Рисунок 7

После всех шагов нажимаем кнопку «Готово» и получаем результат в виде таблицы на рисунке 8.

Рисунок 8

Запрос выдал результат в соответствии с выбранными полями(рисунок 6).

3. Создание запроса в режиме sql

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

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

Приведем некоторый синтаксис.

Структура запроса на выборку:

SELECT [ALL/DISTINCT] <список атрибутов>/*

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

[WHERE <условие выборки>]

[ORDER BY <список атрибутов>]

[GROUP BY <список атрибутов>]

[HAVING <условие>]

[UNION<выражение с оператором SELECT>]

В квадратных скобках указываются элементы, которые могут в запросе отсутствовать.

Ключевое слово ALL означает, что результатом будут все строки, удовлетворяющие условию запроса, в том числе и одинаковые строки. DISTINCT означает, что в результирующий набор не включаются одинаковые строки. Далее идет список атрибутов исходной таблицы, которые будут включены в таблицу-результат. Символ * означает, что в таблицу-результат включаются все атрибуты исходной таблицы.

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

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

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

В предложении с ключевым словом GROUP BY задается список атрибутов группировки (разъяснение этого и последующего ключевого слова будет представлено немного позднее).

В предложении HAVING задаются условия, накладываемые на каждую группу.

Отдельно отметим, что ключевые слова FROM, WHERE, ORDER BY используются аналогичным образом и в других операторах манипулирования данными языка SQL.

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

Запрос выглядит следующим образом:

SELECT [Посетитель].[ИД_Посетителя], [Посетитель].[Имя], [Посетитель].[Фамилия]

FROM [Посетитель]

WHERE [Посетитель].[Должник] =true;

А его результат представлен на рисунке 9:

Рисунок 9

Если открыть таблицу «Посетитель» можно увидеть, что посетители Медведева Людмила и Денисов Степан действительно числятся в базе как должники(рисунок 10).

Рисунок 10

Рекомендации по оформлению

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

Для того, чтобы скопировать код запроса, нужно переключить режим на «Режим SQL» на вкладке конструктор раздела «Результаты».

Дальнейшее оформление производится в соответствии с ГОСТ 7.32-2001. В отчете должны быть такие разделы как «Содержание», «Отчет» и «Вывод».

Пример запроса на выборку в MS Access. (выполнен в английской версии MS Access 2010)

Работа с конструктором запроса.

1. Идем на вкладку «Создание»(Create), выбираем «Конструктор запросов»(Query Design) как на рисунке(выделено красным)

Рисунок 1

2. После выбора конструктора откроется пустое поле и конструктор предложит выбрать таблицы в отдельном окошке. НИЧЕГО не выбираем, ОКНО (как на рисунке) ЗАКРЫВАЕМ.

Рисунок 2

3. Далее на вкладке «Конструктор запросов», здесь она Query Tool, и выбираем «Добавление»(Append)

Рисунок 3

4. После того как выбрали добавление всплывет окно и предложит выбрать таблицы для добавления.

Рисунок 4

Имя таблицы(Table Name) – выбираем таблицу, в которую хотим занести новые строки

Текущая база данных(Current Database) – говорит о то, что таблицы вы будете брать из этой базы данных.

5. Далее заполняем поля конструктора запросов:

Строка «Поле»(Field) – заполняем значения, которые хотим внести. НЕ забываем, что текстовые данные надо писать в кавычках, а это все те, которые не числовые.