- •Лабораторная работа № 1. Создание таблиц
- •Основные понятия СУБД Access
- •Учебная база данных "Борей"
- •Основные сведения о таблицах
- •Создание базы данных
- •Ввод данных в таблицы
- •Освоение приемов работы с фильтрами в таблицах
- •Общие сведения о MDE-файлах
- •Лабораторная работа № 2. Конструирование запросов
- •Основные сведения
- •Использование вычисляемых полей в запросах
- •Элементы выражений
- •Запросы на выборку
- •Запросы-действия
- •Лабораторная работа № 3. Конструирование форм
- •Общие сведения
- •Создание простой формы
- •Формирование составной формы
- •Лабораторная работа № 4. Конструирование отчетов
- •Основные сведения
- •Создание простого отчета
- •Технология
- •Группировка данных в отчёте
- •Технология
- •Формирование перекрестного отчёта
- •Создание связанного отчета
- •Лабораторная работа № 5. Создание макросов
- •Основные сведения
- •Формирование форм-меню
- •Создание пользовательского ниспадающего меню
- •Лабораторная работа № 6. Конструирование SQL запросов
- •Основные сведения
- •SQL-запросы на выборку
- •Соединение таблиц
- •Использование вычисляемых полей в SQL-запросах
- •QSL-запросы на изменение
- •Заключение
Лабораторная работа № 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
6.Ввести в 3-й столбец поле Коммерческий и замените название столбца на Количество коммерческих. Для этого ячейка с названием поля должна содержать:
Количество коммерческих: Коммерческий
(новое и старое названия столбца разделены символом «двоеточие»).
7.Щелкнув по кнопке инструментальной панели Групповые операции, добавить в бланк строку Групповая операция и выбрать из списка для 3-го столбца операцию Count.
8.Просмотреть выборку с подсчетом итогов, щелкнув по кнопке инструментальной панели Запуск, либо выполнив команду ЗАПРОС/Запуск.
9.Вернуться в режим конструктора запросов, щелкнув по кнопке Вид инструментальной панели.
10.Сохранить запрос, присвоив ему имя Задание 13.
Задание 14. Создать SQL-запрос с именем Задание 14, выполняющий подсчет количества коммерческих студентов в каждой группе, используя данные из таблицы Студент.
Запрос должен содержать следующий текст:
SELECT [Номер группы], соunt(Коммерческий) AS [Количество коммерческих]
FROM Студент
WHERE Коммерческий=ТRUЕ
GROUP BY [Номер группы]
ORDER BY [Номер группы];
Выполнить запрос. Сравнить результаты работы запросов из заданий 13 и 14. Просмотреть, сравнить и проанализировать структуры этих запросов сначала в режиме SQL, затем в режиме конструктора.
Примечание. При создании сложного SQL-запроса можно с целью проверки синтаксиса запроса открывать его в окне конструктора QBEзапроса. Если команды запроса содержат орфографические или синтаксические ошибки, то Access не сможет преобразовать SQL-запрос в QBE-запрос и выведет окно сообщения с описанием ошибки.
82