Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 13
ЛАБОРАТОРНАЯ РАБОТА №6
по курсу “Компьютерные информационные технологии”
ЗАПРОСЫ В ACCESS
Запросы предназначены для выборки информации из базы данных или для внесения изменений в базу данных.
В Access имеются следующие основные виды запросов:
При построении любого QBE-запроса для него автоматически строится описание на языке SQL, и наоборот (за исключением некоторых SQL-запросов, которые не могут быть построены как QBE-запросы). Переход от описания в виде SQL-запроса к QBE-запросу (и наоборот) выполняется с помощью команд меню Вид.
Имеются два способа создания запросов:
Источником данных для запроса может быть как таблица, так и другой запрос (созданный ранее).
Обычно удобно создавать запрос с помощью Мастера, а затем вносить в него необходимые изменения с помощью Конструктора.
Пример 2.1 (запрос для получения информации из одной таблицы). Пусть требуется создать запрос для вывода списка всех рабочих. В списке должна содержаться вся информация о рабочих, кроме шифра объекта, на котором он работает.
Чтобы снова выполнить запрос, требуется выделить его и нажать Открыть, или просто дважды щелкнуть по отметке запроса.
Пример 2.2 (запрос для получения информации из нескольких таблиц). Пусть требуется создать запрос для вывода списка рабочих. Для каждого рабочего указываются его табельный номер, фамилия, разряд, шифр объекта, а также вид объекта, на котором он работает.
Пример 3.1. Пусть требуется создать запрос для вывода списка объектов. Требуется также предусмотреть следующее: а) запрос должен быть упорядочен по названию заказчика, а для одного заказчика – по дате окончания строительства (первыми должны быть указаны объекты, строительство которых должно быть закончено раньше); б) вид объекта должен указываться после шифра объекта.
Пример 3.2 (создание вычисляемого поля). Внести изменения в созданный запрос Список объектов: добавить в него поле с именем Налог, в котором должна указываться величина, равная 10% от стоимости.
Примечание. Здесь Стоимость контракта – имя поля, используемого в выражении. Оно должно быть заключено в квадратные скобки. Никаких знаков в конце выражения не ставится.
Примечание. Для записи выражения можно использовать построитель (мастер) выражений. Для этого следует нажать правую кнопку мыши и выбрать команду Построить.
Примечание. Вместо указания свойства Подпись можно заменить отметку Выражение1 на название поля Налог.
Прежде чем выполнять следующее задание, необходимо добавить в таблицу Рабочие данные о рабочих, приведенные в таблице П1.
Пример 4.1 (простое условие выборки). Создать запрос для получения списка рабочих-штукатуров, имеющих разряд не ниже пятого. В запросе должны указываться следующие данные: табельный номер, фамилия, имя и отчество, разряд, допуск к работам на высоте, а также шифр объекта и название заказчика, у которого работает данный рабочий. Запрос должен быть упорядочен по фамилиям.
Пример 4.2 (сложное условие выборки). Изменить запрос Отобранные, созданный в примере 4.1, таким образом, чтобы получить список штукатуров и каменщиков, имеющих разряд не ниже пятого.
Примечание. Хотя условие >=5 уже было указано для поля Разряд в строке Условие отбора, его необходимо также указать в строке Или. Если не сделать этого, то в запросе будет получен список штукатуров, имеющих разряд не ниже пятого, и всех каменщиков.
Групповые операции – это операции суммирования по отдельным полям, вычисление средних, подсчет количества записей и т.д.
Пример 5.1 (создание запроса с помощью Мастера). Создать запрос для подсчета суммарной стоимости контрактов и количества контрактов каждого заказчика.
Пример 5.2. Создать запрос для подсчета суммарной стоимости контрактов по годам окончания строительства.
Пример 5.3 (создание запроса с помощью конструктора). Создать запрос для подсчета количества рабочих каждой профессии. Запрос должен быть упорядочен по количеству рабочих (первыми должны выводиться профессии с максимальным количеством рабочих).
Примечание. Если в запросе требуется только групповая операция подсчета (без каких-либо других операций), то такие запросы удобно создавать с помощью Конструктора.
Пример 5.4 (создание запроса с источником-запросом). Используя Конструктор, создать запрос для подсчета суммы налогов.
Так как данных о налогах нет ни в одной таблице, в качестве источника будет использоваться запрос, в котором такие данные есть (запрос Список объектов).
Пример 6.1. Создать запрос для подсчета количества рабочих каждой профессии, работающих на каждом из объектов. Результат запроса будет представлять собой таблицу, где в строках будут указаны объекты, а в столбцах – профессии.
Рисунок 1 – Результаты перекрестного запроса
Пример 6.2. Создать запрос для подсчета количества рабочих каждой профессии, работающих на объектах каждого вида (жилые дома, офисы, цехи и т.д.).
Все данные, необходимые для построения перекрестного запроса, должны содержаться в одной таблице или запросе (другими словами, в перекрестном запросе невозможно использовать данные из нескольких таблиц или запросов). Поэтому сначала необходимо создать обычный запрос, содержащий поля Табельный номер и Профессия (из таблицы Рабочие) и Вид объекта (из таблицы Объекты). Присвоить ему имя Распределение рабочих по видам объектов.
Создать перекрестный запрос, как показано в примере 6.1. На шаге 2 потребуется выбрать для переключателя Показать значение Запросы (или Все) и выбрать запрос Распределение рабочих по видам объектов. В качестве строк создаваемого запроса следует выбрать виды объектов, в качестве столбцов – названия профессий (или наоборот). Присвоить созданному отчету имя Распределение профессий по видам объектов.
Пример 7.1 (запрос на обновление). Создать запрос для выполнения следующей операции: для всех каменщиков и штукатуров, имеющих разряд не ниже пятого, установить шифр объекта П80.
Условия отбора (профессии – каменщики и штукатуры, разряд – не ниже пятого) уже были заданы в запросе Отобранные (см. раздел 4).
Пример 7.2 (запрос на удаление). Создать запрос для удаления данных обо всех рабочих, имеющих разряд не выше второго и не имеющих допуска к работам на высоте.
Примечание. Если требуется снова преобразовать запрос для удаления в обычный запрос (для выборки данных), то необходимо выполнить команду Запрос – Выборка.
Пример 8.1. Создать запрос для получения списка рабочих заданной профессии, имеющих разряд не ниже заданного. В списке, получаемом в результате выполнения запроса, должны быть указаны табельные номера, фамилии и разряды рабочих. Профессия и разряд, должны вводиться пользователем при выполнении запроса.
Рисунок 2 – Создание перекрестного запроса
SQL – стандартный язык управления базами данных, используемый в различных СУБД.
При построении любого QBE-запроса для него автоматически строится описание на языке SQL, и наоборот (за исключением некоторых SQL-запросов, которые не могут быть построены как QBE-запросы). Все запросы, рассмотренные выше, представляли собой QBE-запросы. Чтобы перейти от описания в виде QBE-запроса к SQL-запросу, необходимо открыть запрос в режиме Конструктора и выбрать команду Вид – Режим SQL. Для возврата к описанию в виде QBE-запроса используется команда Вид – Конструктор.
Обычно удобно сначала создать запрос в режиме QBE, а затем перейти в режим SQL и внести необходимые изменения. При изменении запроса в режиме SQL автоматически изменяется его описание в режиме QBE (и наоборот).
При переходе в новую строку в любой команде SQL необходимо нажимать комбинацию клавиш CTRL-ENTER (а не просто клавишу ENTER). В конце команды языка SQL всегда ставится точка с запятой (;).
В данной работе предлагается изучить примеры SQL-запросов, приведенные в подразделах 9.1 – 9.6, и самостоятельно создать SQL-запросы, указанные в разделе 9.7.
Основная команда языка SQL – команда SELECT. Общий вид этой команды следующий:
SELECT поля
FROM таблицы или запросы
WHERE условия
ORDER BY поля для сортировки;
Здесь поля – имена полей, которые должны быть выведены на экран при выполнении запроса; таблицы или запросы – источники данных для запроса; условия – условия отбора данных; поля для сортировки – поля, по которым выполняется упорядочение данных в запросе.
Пример 9.1. Запрос для вывода списка всех рабочих. В списке должна содержаться вся информация о рабочих, кроме шифра объекта, на котором он работает (эта же задача решалась в примере 2.1).
SELECT [Табельный номер], Фамилия, [Имя и отчество], [Дата рождения], Профессия,
Разряд, [Дата приема на работу], [Допуск на высоту]
FROM Рабочие;
Из этого примера видно, что в случае, если в имени поля имеются пробелы (например, в имени Табельный номер), оно должно заключаться в квадратные скобки.
Пример 9.2. Получить список всех рабочих, имеющих разряд не ниже пятого и допуск к работе на высоте. Вывести фамилию, профессию, разряд.
SELECT Фамилия, Профессия, Разряд
FROM Рабочие
WHERE (Разряд>=5) AND ([Допуск на высоту]=true);
Пример 9.3. Получить список всех каменщиков и штукатуров, имеющих разряд не ниже пятого. Вывести табельный номер, фамилию, профессию, разряд, допуск к работе на высоте. Список должен быть отсортирован по профессии и фамилии (т.е. список сортируется по профессии, а при одинаковой профессии – по фамилии).
SELECT [Табельный номер], Фамилия, Профессия, Разряд, [Допуск на высоту]
FROM Рабочие
WHERE ((Профессия="каменщик") OR (Профессия="штукатур")) AND (Разряд>=5)
ORDER BY Профессия, Фамилия;
Следует обратить внимание, что построить такой запрос в режиме QBE было бы достаточно сложно, так как список рабочих должен быть отсортирован по профессии и фамилии, а в запросе сначала указывается фамилия, затем - профессия. Если построить этот запрос в режиме QBE так, как описывалось в примерах выше (ввести в отчет поля Табельный номер, Фамилия, Профессия и Допуск на высоту, для полей Фамилия и Профессия в строке Сортировка выбрать значение По возрастанию), то упорядочение в запросе будет выполняться неправильно: записи будут упорядочиваться по фамилии, и только при одинаковой фамилии –по профессии. Использование SQL-запроса позволяет легко избежать этой проблемы.
Пример 9.4. Получить те же данные, что и в примере 9.4. Однако сортировка должна выполняться по профессии, разряду и фамилии. Это значит, что список рабочих сортируется по профессии; рабочие с одинаковой профессией сортируются по разряду (причем первыми указываются рабочие, имеющие более высокий разряд), а при одинаковой профессии и разряде – по фамилии.
SELECT [Табельный номер], Фамилия, Профессия, Разряд, [Допуск на высоту]
FROM Рабочие
WHERE ((Профессия="каменщик") OR (Профессия="штукатур")) AND (Разряд>=5)
ORDER BY Профессия, Разряд DESC, Фамилия;
Здесь DESC означает, что сортировка по разряду выполняется по убыванию (сначала указываются рабочие с более высоким разрядом).
Для выборки данных из нескольких таблиц используется команда объединения таблиц, включаемая в команду SELECT:
Таблица1 INNER JOIN Таблица2 ON Таблица1.Поле1=Таблица2.Поле2
Таблица1 и Таблица2 должны быть связаны (обычно используется связь 1:М). Таблица1 должна находиться со стороны 1, а Таблица2 – со стороны М. Поле1 и Поле2 – поля, по которым связываются таблицы (обычно они имеют одинаковые имена).
Пример 9.5. Создать запрос для получения списка штукатуров и каменщиков, имеющих разряд не ниже пятого. В запросе должны указываться следующие данные: табельный номер, фамилия, имя и отчество, разряд, допуск к работам на высоте, а также шифр объекта и название заказчика, у которого работает данный рабочий. Запрос должен быть упорядочен по фамилиям рабочих.
SELECT Рабочие.[Табельный номер], Рабочие.Фамилия, Рабочие.[Имя и отчество],
Рабочие.Профессия, Рабочие.Разряд, Рабочие.[Допуск на высоту],
Рабочие.[Шифр объекта], Объекты.Заказчик
FROM Объекты INNER JOIN Рабочие ON Объекты.[Шифр объекта] =
Рабочие.[Шифр объекта]
WHERE ((Рабочие.Профессия="штукатур") OR (Рабочие.Профессия="каменщик"))
AND (Рабочие.Разряд>=5)
ORDER BY Рабочие.Фамилия;
Здесь часть команды
FROM Объекты INNER JOIN Рабочие ON Объекты.[Шифр объекта] = Рабочие.[Шифр объекта]
означает, что данные должны выбираться из таблиц Объекты и Рабочие, между которыми имеется связь 1:М (причем таблица Объекты находится со стороны 1, а Рабочие – со стороны М, т.е. одному объекту может соответствовать несколько рабочих). Таблицы связаны по полю Шифр объекта, которое имеется в обеих таблицах.
Так как в данном запросе используются поля из двух таблиц, перед каждым именем поля указывается имя таблицы, отделяемое точкой (например, Объекты.Заказчик - поле Заказчик из таблицы Объекты).
Пример 9.6. Создать запрос для получения списка объектов. Для каждого объекта должно указываться все данные об объекте, имеющиеся в таблице Объекты, а также величина налога, составляющая 10% от стоимости контракта. Список объектов должен быть упорядочен по названию заказчика, а для каждого заказчика – по дате окончания строительства. Аналогичная задача решалась в примере 3.2.
SELECT [Шифр объекта], Заказчик, [Вид объекта], [Стоимость контракта],
[Дата заключения], [Дата окончания], [Стоимость контракта]*0.1 AS [Налог]
FROM Объекты
ORDER BY Заказчик, [Дата окончания];
Как видно из примера, вычисляемое поле описывается следующим образом: формула для вычисления (в данном случае [Стоимость контракта]*0.1), затем слово AS, затем – имя вычисляемого поля (в данном случае – Налог).
Так как в этом примере все данные берутся из одной таблицы (Объекты), указывать имя таблицы перед именами полей необязательно.
Общий вид команды SELECT, применяемой для таких запросов, следующий:
SELECT поля и статистические функции
FROM таблицы или запросы
GROUP BY поля для группировки
HAVING условия
ORDER BY условия сортировки;
Здесь поля и статистические функции – данные, которые должны быть получены в результате выполнения запроса; поля для группировки – поля, используемые для групповых операций (например, если требуется вычислить сумму стоимостей или количество контрактов для каждого заказчика, то в разделе GROUP BY будет указано поле Заказчик); условия – условия выборки данных. Раздел HAVING в запросах с групповыми операциями аналогичен разделу WHERE в обычных запросах. Он всегда указывается после раздела GROUP BY.
Пример 9.7. Создать запрос для подсчета суммарной стоимости контрактов и количества контрактов каждого заказчика (эта же задача решалась в примере 5.1).
SELECT Заказчик, Sum([Стоимость контракта]) AS [Стоимость контрактов],
Count([Шифр объекта]) AS [Количество]
FROM Объекты
GROUP BY Заказчик;
Здесь выражение GROUP BY Заказчик означает, что записи в таблице Объекты (указанной в разделе FROM) будут сгруппированы по значению поля Заказчик. Для записей с одинаковым значением этого поля будет подсчитана сумма значений поля Стоимость контракта; для этого указано выражение Sum([Стоимость контракта]). Результат будет выводиться на экран под именем Стоимость контрактов (указывается после слова AS). Будет также подсчитано количество значений шифра объекта (т.е. количество записей) с одинаковым значением поля Заказчик; для этого используется функция Count.
Примечание. Вместо выражения Count([Шифр объекта]) можно указать Count(*). Такая операция представляет собой подсчет количества записей.
Пример 9.8. Создать запрос для получения тех же данных, что и в примере 9.7, но требуется только информация о заказчиках, для которых стоимость контрактов составляет не менее 100 млн ден.ед.
SELECT Заказчик, Sum([Стоимость контракта]) AS [Стоимость контрактов],
Count([Шифр объекта]) AS [Количество]
FROM Объекты
GROUP BY Заказчик
HAVING Sum([Стоимость контракта])>=100000000;
Пример 9.9. Создать запрос для подсчета количества рабочих каждой профессии. Запрос должен быть упорядочен по количеству рабочих (первыми должны выводиться профессии с максимальным количеством рабочих), а при одинаковом количестве рабочих – по профессии (в алфавитном порядке). Аналогичная задача решалась в примере 5.3.
SELECT Профессия, Count([Табельный номер]) AS [Количество]
FROM Рабочие
GROUP BY Профессия
ORDER BY Count([Табельный номер]) DESC, Профессия;
Пример 9.10. Создать запрос для подсчета суммы налогов (аналогичная задача решалась в примере 5.4).
Так как данных о налогах нет ни в одной таблице, в качестве источника будет использоваться запрос, в котором такие данные есть. Этот запрос был создан в примере 9.7. Пусть он сохранен под именем Список объектов.
SELECT Sum([Налог]) AS [Сумма налогов]
FROM [Список объектов];
Здесь Налог – имя вычисляемого поля, имеющегося в запросе Список объектов.
Общий вид таких запросов следующий:
UPDATE таблица
SET поле=значение
WHERE условия
Пример 9.11. Создать запрос для выполнения следующей операции: для всех каменщиков и штукатуров, имеющих разряд не ниже пятого, установить шифр объекта П80 (аналогичная задача решалась в примере 7.1).
UPDATE Рабочие
SET [Шифр объекта] = "П100"
WHERE ((Профессия="штукатур") OR (Профессия="каменщик")) AND (Разряд>=5);
Пример 9.12. Создать запрос для выполнения следующей операции: снять всех рабочих с объектов заказчика “Автозавод”.
UPDATE Объекты INNER JOIN Рабочие ON Объекты.[Шифр объекта]=
Рабочие.[Шифр объекта]
SET Рабочие.[Шифр объекта] = Null
WHERE (Объекты.Заказчик="Автозавод");
Здесь требуется изменять значения поля Шифр объекта в таблице Рабочие, но при этом используется информация из таблицы Объекты, где указаны названия заказчиков. Поэтому в команде используется раздел INNER JOIN (связь между таблицами), и перед именами полей указываются имена таблиц. Значение Null, присваиваемое полю Шифр объекта, обозначает, что это поле становится пустым. Важно понимать, что значение Null – это не то же самое, что пустая строка (“”).
Общий вид таких запросов следующий:
DELETE FROM таблица WHERE условие
Пример 9.13. Создать запрос для удаления данных обо всех рабочих, имеющих разряд не выше второго и не имеющих допуска к работам на высоте (аналогичная задача решалась в примере 7.1).
DELETE
FROM Рабочие
WHERE (Разряд<=2) AND ([Допуск на высоту]=False);
Как и в QBE-запросах, вместо конкретных величин в параметрических SQL-запросах указываются тексты сообщений (подсказок), которые должны выводиться на экран при выполнении запроса. Эти подсказки заключаются в квадратные скобки.
Пример 9.14. Создать запрос для получения списка рабочих заданной профессии, имеющих разряд не ниже заданного. В списке, получаемом в результате выполнения запроса, должны быть указаны табельные номера, фамилии и разряды рабочих. Профессия и разряд, должны вводиться пользователем при выполнении запроса (эта же задача решалась в примере 8.1).
SELECT [Табельный номер], Фамилия, Разряд
FROM Рабочие
WHERE (Профессия=[Укажите профессию]) AND (Разряд>=[Укажите разряд]);
При выполнении этого запроса на экран будут выводиться подсказки Укажите профессию и Укажите разряд.
Для создания SQL-запроса “с нуля” (т.е. без предварительного создания QBE-запроса) необходимо выполнить следующее.
Предлагается самостоятельно создать следующие SQL-запросы (не создавая предварительно QBE-запросы):
а) список всех объектов, представляющих собой жилые дома или офисы. Для каждого объекта должны указываться все данные, имеющиеся в таблице Объекты. Список объектов должен быть упорядочен по названию заказчика, а для каждого заказчика – по шифру объекта;
б) список всех рабочих, занятых на объектах, где заказчиком является автозавод. Для каждого рабочего указывается табельный номер, фамилия, профессия, шифр объекта. Список должен быть упорядочен по фамилиям рабочих (если есть однофамильцы – то по табельному номеру);
в) то же, что в задании б), но для произвольного заказчика. Название заказчика должно вводиться пользователем при выполнении запроса;
г) список всех объектов. Для каждого объекта должны указываться все данные, имеющиеся в таблице Объекты, а также количество дней, оставшихся до окончания строительства. Для вычисления количества дней до конца строительства использовать формулу: [дата окончания]-date() (здесь date() – стандартная функция, возвращающая текущую дату);
д) то же, что в задании г), но только для объектов, у которых до окончания строительства остается не более 30 дней;
е) список всех объектов. Для каждого объекта указывается его шифр, заказчик, вид объекта и количество занятых рабочих. Список должен быть отсортирован по шифрам объектов (по алфавиту);
ж) повышение стоимости контракта на 20% для всех объектов – офисов. Указание: в разделе SET использовать выражение примерно следующего вида: [Стоимость контракта] = [Стоимость контракта]*1,2;
з) удаление данных об указанном рабочем (фамилия рабочего должна вводиться пользователем).
ПРИЛОЖЕНИЕ
Таблица П1
|
Табельный номер |
Фамилия |
Имя и отчество |
Дата рождения |
Профессия |
Разряд |
Дата приема на работу |
Допуск на высоту |
Шифр объекта |
|
120 |
Иванов |
Сергей Петрович |
20.08.1982 |
маляр |
1 |
05.08.2002 |
нет |
П80 |
|
121 |
Демин |
Юрий Петрович |
12.07.1980 |
штукатур |
3 |
10.08.2002 |
да |
П100 |
|
122 |
Мишин |
Иван Андреевич |
10.02.1980 |
каменщик |
5 |
14.08.2001 |
нет |
П100 |