Учебное пособие Власовой, Самойловой MS Access

данных. Не закрывая окна Настройка, щелкнуть правой клавишей в строке меню по категории Новое меню и в контекстном меню заменить имя категории на Формы.

6.Добавить в меню категорию Отчеты аналогично пункту 5.

7.Аналогично добавить в меню Формы новое подменю, назвав его

Простые.

8.В окне Настройка на вкладке Команды выделить категорию Все формы. Перетащить строку с названием одной из созданных ранее форм – Формы с полем для списка, Главной или Подчиненной в

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

9.Аналогично добавить в область команды категории

Формы/Простые пункты с названием форм – Студент-простая Группа и Простая форма по запросу.

Рис. 40. Создание категории Формы в строке пользовательского меню

10. Аналогично добавить в меню Управление базой данных в категорию Отчеты пункты с названиями отчетов. Закрыть окно Настройка. Проверить работу меню.

72

11.Выполнить команду СЕРВИС/Параметры запуска и

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

(рис. 41):

Ввести в качестве заголовка приложения название Академия;

Выбрать в качестве строки меню строку Управление базой данных.

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

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

Рис. 41. Окно Параметры запуска с выполненной настройкой

13. Восстановить для базы данных Академия отображение окна базы

данных, полного набора меню Access, стандартных панелей инструментов. Для этого перезагрузить базу данных и при повторном открытии держать нажатой клавишу SHIFT. Выполнить команду СЕРВИС/Параметры запуска и восстановить исходное состояние флажков.

73

Лабораторная работа № 6. Конструирование SQL запросов

Цель работы – конструирование и использование SQL запросов к базе данных

Основные сведения

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

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

QBE-запросы (Query By Example – запрос по образцу), которые строятся с помощью конструктора в окне конструктора запросов (лабораторная работа №2);

SQL-запросы, написанные на языке SQL. SQL (Structured Query Language) – структурированный язык запросов. В Access 2000

используется версия SQL - Jet SQL (Microsoft Jet Database Engine SQL).

Все запросы можно разделить на 2 группы:

запросы-выборки;

запросы-действия.

Запросы-выборки извлекают данные из таблиц в соответствии с заданными условиями

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

SQL-запросы на выборку

Задание 1. Просмотреть запрос Выборка 2, созданный в главе №2, в командах SQL.

Технология

1. В окне базы данных выбрать объект Запросы, выбрать из списка запрос Выборка 2 и щелкнуть по кнопке Конструктор.

2. В окне конструктора запросов выполнить команду ВИД/Режим SQL. Появится окно SQL, содержащее эквивалентную QBE-запросу инструкцию SQL.

74

Задание 2. Изменить структуру запроса Выборка 2, удалив из запроса поле Номер зачетной книжки.

Технология

1.Открыть запрос Выборка 2 в режиме SQL.

2.В окне запроса из списка полей следующих за инструкцией SELECT

удалить поле - Студент.[Номер зачетной книжки].

3.Нажать на панели инструментов кнопку Запуск для выполнения запроса. Проанализировать результат.

4.Выбрать команду ВИД/Конструктор и просмотреть структуру QBE-запроса.

SQL-запросы на выборку создаются инструкцией SELECT. При выполнении этой операции ядро базы данных Microsoft Jet находит указанную таблицу или таблицы, извлекает заданные столбцы, выделяет строки, соответствующие условию отбора, и сортирует или группирует результирующие строки в указанном порядке. Инструкция SELECT не изменяет данные в базе данных.

Команда имеет синтаксис: SELECT [предикат] список полей

FROM имена_таблиц [WHERE критерий поиска]

[GROUP BY критерий группировки] [HAVING групповой критерий] [ORDER BY критерий_столбца]

Аргументы инструкции SELECT имеют следующий смысл:

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

символом *, что означает выбрать все поля;

таблица.*, что означает все поля из таблицы с именем таблица;

75

[таблица.]поле_1 [AS псевдоним_1] [, [таблица.]поле_2 [AS псевдоним_2] [,...]]

Вэтом случае:

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

поле_1, поле_2 указывают имена полей, из которых должны быть отобраны данные. Если включить несколько полей, они будут извлекаться в указанном порядке. Поля в списке разделяются запятой;

псевдоним1, псевдоннм_2 задают имена, которые станут заголовками столбцов вместо исходных названий полей (столбцов) в таблице.

oпредложение FROM имена_таблиц определяет имена одной или нескольких таблиц, которые содержат отбираемые данные.

Примечание. Если имена полей, таблиц, псевдонимы содержат пробелы, или знаки разделители, то они должны заключаться в квадратные скобки (например, Студент.[Номер зачетной книжки]).

Задание 3. Создать SQL-запрос для выборки всех сведений о студентах из таблицы Студент

Технология

1.Выбрать в окне базы данных объект Запросы и щелкнуть по кнопке

Создать.

2.В окне Новый запрос выбрать режим конструктор и нажмите кнопку ОК.

3.Закрыть окно Добавление таблицы.

4.В окне конструктора запросов выполнить команду ВBL/Режнм

SQL.

5.В окне запроса в режиме SQL набрать текст запроса:

SELECT *

FROM Студент;

76

Примечание. Если при наборе SQL-запроса требуется перейти на новую строку, следует использовать клавишную команду Ctrl+Enter. Инструкции SQL должны заканчиваться ; (точкой с запятой).

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

7.Вернуться в режим SQL в случае, если выборка содержит ошибки, и откорректировать запрос.

8.Сохранить запрос с именем Задание 3.

Задание 4. Создать SQL-запрос Задание 4 для выборки фамилий и дат рождения студентов из таблицы Студент.

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

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

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

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

ТОР позволяет ограничить количество выводимых записей.

Например, следующим запросом из выборки будут отобраны ФИО и дата рождения только из трех первых записей:

SELECT TOP 3 Фамилия, [Дата рождения] FROM Студент;

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

77

oпредложение WHERE критерий_поиска определяет, какие записи из таблиц, перечисленных в предложении FROM, следует включить в

результат выполнения инструкции SELECT.

Критерий_поиска задается выражением. Элементами выражения могут быть операторы, идентификаторы, функции, литералы и константы.

Операторы определяют – операцию, выполняемую над одним или несколькими элементами. Используются операторы: >, <, =, <> (не равно), &, And, Or, Like, In, Between. Предложение WHERE может содержать до 40 выражений, связанных логическими операторами, такими как And и Or.

Оператор Like используется для сравнения строкового выражения с образцом в выражении SQL. Синтаксис оператора: выражение Like

"образец"

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

* – произвольное количество символов (например, оператор Студент.Фамилия Like "С*" в запросе возвратит все фамилии, начинающиеся на С);

? – один символ;

.# – одна цифра.

Оператор In проверяет, совпадает ли значение выражения с одним из элементов указанного списка. Синтаксис оператора: выражение [Not]

In (значепие_1, значение_2, ...)

Например, оператор Студент.ФИО In ('Иванов',Петров','Смирнов')

определяет совпадает ли значение поля «ФИО» со значением одного из элементов списка.

Оператор Between определяет принадлежность значения выражения указанному диапазону.

Синтаксис оператора: выражение [Not] Between значепие_1 And

значение_2

Например, оператор Студент.[Дата рождения] Between #01/01/80# And #31/12/80# определяет принадлежность значения поля Дата рождения интервалу дат 1980 года.

78

Если один из аргументов оператора Between...And, выражение, значение_1 или значение_2, имеет значение Null, то оператор

Between...And возвращает значение Null.

Поскольку подстановочные знаки, такие как символ звездочки * считаются текстовыми символами, их нельзя использовать в операторе

Between...And.

Идентификаторы в выражении задают ссылку на значение поля и заключаются в квадратные скобки, если содержат пробелы или знакb препинания.

Функция в выражении возвращает значение, найденное в результате расчета или другой операции.

Литерал в выражении представляет значение, например, число, строку или дату, которое используется именно в том виде, как оно записано. Даты заключаются в символы номера (#), а строки в прямые кавычки (") или одинарные кавычки (например, 100, # 1 -янв-94#, "New York").

Константа (True, False, Да, Нет, Null) представляет значение, остающееся неизменным.

Задание 6. С помощью SQL-запроса Задание 6 сформировать список студентов заданной группы. Список должен содержать полные сведения о студенте.

Запрос должен содержать следующий текст:

SELECT *

FROM Студент

WHERE [Номер группы]=n;,

где n – номер группы.

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

[Номер группы] =[Введите номер группы]. Такой запрос является запросом с параметром.

79

Задание 7. С помощью SQL-запроса Задание 7 сформировать список студентов, фамилии которых начинаются на «К». Список должен содержать полные сведения о студенте.

Задание 8. С помощью SQL-запроса Задание 8 сформировать список студентов, фамилии которых начинаются на «А», «Б» и «В». Список должен содержать номер группы, фамилию и номер зачетной книжки студента.

Задание 9. С помощью SQL-запроса Задание 9 сформировать список студентов родившихся в заданном году. Список должен содержать номер группы, фамилию и дату рождения студента.

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

o-предложение ORDER BY определяет порядок сортировки выводимых записей. Критерий столбца задает поле, по значениям которого упорядочиваются записи. С помощью ключевых слов ASC или DESC можно явно указать порядок сортировки:

ASC – по возрастанию значений (действует по умолчанию);

DESC – по убыванию

Задание 11. С помощью SQL-запроса Задание 11 сформировать список студентов родившихся в заданном году. Список должен содержать номер группы, фамилию и дату рождения студента и отсортирован по номеру группы.

Задание 12. С помощью SQL-запроса Задание 12 сформировать список студентов родившихся в заданном году. В список выводить полные сведения о студентах упорядоченные по убыванию значений поля коммерческий, а внутри поля коммерческий по полю номер группы.

80

o предложение GROUP BY определяет критерий объединения записей в группу для выполнения групповых операций. Критерий группировки определяет поле, на основе которого выполняется группировка. А аргумент список_полей задается в виде:

статисптческая_функция(поле) AS псевдоним

Статистические функции, например Sum, Count (количество записей, возвращаемых запросом), Avg (арифметическое среднее набора чисел), Min, Max (минимальное и максимальное значения из набора значений, содержащихся в указанном поле запроса), используются для вычисления итоговых значений.

Совместно с предложением GROUP BY инструкция SELECT может содержать предложения WHERE и HAVING. В этом случае предложение WHERE выявляет множество записей, соответствующих критерию поиска. Только после этого в соответствии с критерием группировки, множество записей объединяется в группы. Предложение HAVING определяет условия, которые накладываются на значения полученные в результате выполнения групповой операции (значение столбца статистическая функция).

Задание 13. Составить QBE-запрос для подсчета количества коммерческих студентов в каждой группе.

Технология

1.Создать новый QBE-запрос с использованием таблицы Студент.

2.Ввести в 1-ый столбец бланка запроса поле Номер группы, в 2-й

столбец – поле Коммерческий.

3.Установить в качестве условия выборки для 2-го столбца значение

Да.

4.Установить для 1-го столбца бланка сортировку по возрастанию.

5.Отключить вывод на экран данных 2-го столбца.

81