три Лабораторные работы по дисциплине Базы данных. Лабораторная работа № 2 по дисциплине- «Базы данных». Лабораторная работа 2 Создание запросов в субд ms access по дисциплине Базы данных Вариант 8 студент группы
 Скачать 3.51 Mb.
|
|
Министерство науки и высшего образования Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования ТОМСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ СИСТЕМ УПРАВЛЕНИЯ И РАДИОЭЛЕКТРОНИКИ (ТУСУР) Кафедра компьютерных систем в управлении и проектировании (КСУП) Лабораторная работа № 2 «Создание запросов в СУБД MS Access» по дисциплине: «Базы данных» Вариант № 8 Выполнил: студент группы з-511П8-5 Кузнецов Сергей Сергеевич Проверил: преподаватель Мурзин Евгений Сергеевич Томск 2023 Оглавление 1 Цель работы 3 2 Задание 4 3 Создание запросов с помощью построителя запросов 5 3.1 Запрос на выборку 5 3.2 Запрос на выборку с параметрами с приглашением на ввод условий отбора 5 3.3 Запрос на выборку с параметрами с явным указанием условия отбора 6 3.4 Запрос на обновление данных 6 3.5 Запрос на удаление данных 7 4 Создание SQL-запросов 9 4.1 Создание новой таблицы с использованием CREATE TABLE 9 4.2 Создание новой таблицы с использованием CREATE TABLE с внешним ключом 10 4.3 Создание нового индекса с использованием CREATE INDEX 11 4.4 Добавление записей с использованием INSERT INTO 13 4.5 Обновление данных с использованием UPDATE 15 4.6 Выборка записей с использованием SELECT 16 4.7 Перекрестный запрос с использованием TRANSFORM 18 4.8 Удаление таблицы и индекса с использованием DROP 19 1 Цель работы Создать запросы в среде MS Access. 2 Задание Создать с помощью построителя запросов: запрос на выборку; запрос на выборку с параметрами; запрос на обновление данных; запрос на удаление записей; Реализовать на языке SQL без помощи построителя запросов: используя инструкцию CREATE TABLE, запрос на создание новой таблицы, содержащей пять полей различных типов данных, определив в запросе первичный ключ и проиндексировав соответствующие поля, используя предложение CONSTRAINT, а затем на создание еще одной таблицы, содержащей внешний ключ по отношению к первичному ключу предыдущей таблицы; используя команду CREATE INDEX, запрос на создание нового индекса, используя различные условия назначения индексов (IGNORE NULL, DISALLOW NULL, PRIMARY), а также типы сортировки; используя команду INSERT INTO, запросы на добавление группы записей (из дополнительной таблицы) и одной записи в существующую таблицу; используя команду UPDATE, запрос на обновление данных в созданных ранее таблицах; используя команду SELECT, запрос на выборку записей из двух (или более) таблиц, используя правила внешнего и внутреннего соединения, а также различные условия отбора и сортировки; используя команду TRANSFORM, перекрестный запрос; используя команду DROP, запросы на удаление таблицы и индекса, созданных ранее в БД. Предметная область в соответствии с вариантом №8: поликлиника. 3 Создание запросов с помощью построителя запросов 3.1 Запрос на выборку Создадим в СУБД MS Access с помощью построителя запросов запрос на выборку. Запрос «Поставленные диагнозы» будет отображать ФИО пациента, дату приема и название диагноза, который был поставлен на этом приеме, а также статус диагноза.  Создание запроса в конструкторе представлено на рисунке 3.1. 3.2 Запрос на выборку с параметрами с приглашением на ввод условий отбора Создадим в СУБД MS Access с помощью построителя запросов запрос на выборку с параметрами с приглашением на ввод условий отбора. Запрос «Сотрудники определенной должности» будет отображать ФИО и табельные номера сотрудников определенной должности, которую нужно ввести при запуске запроса в поле выводящейся на экран подсказки «Введите название должности:». Создание запроса в конструкторе представлено на рисунке 3.2.  3.3 Запрос на выборку с параметрами с явным указанием условия отбора Создадим в СУБД MS Access с помощью построителя запросов запрос на выборку с параметрами с явным указанием условия отбора. Запрос «Сотрудники терапевтического кабинета» будет отображать ФИО всех сотрудников отдела «Терапевтический кабинет», их табельные номера и должности.  Создание запроса в конструкторе представлено на рисунке 3.3. 3.4 Запрос на обновление данных Создадим в СУБД MS Access с помощью построителя запросов запрос на обновление данных. Запрос «Обновление стажа» будет прибавлять к стажу всех неуволенных сотрудников 1 год. Сначала создадим запрос на выборку с параметрами с явным указанием условия отбора «Сотрудники, которые не уволены». Он будет отображать ФИО и стаж сотрудников, которые не были уволены со своих должностей.  Создание запроса в конструкторе представлено на рисунке 3.4. Теперь с помощью данного запроса создадим запрос «Обновление стажа».  Создание запроса в конструкторе представлено на рисунке 3.5. 3.5 Запрос на удаление данных Создадим в СУБД MS Access с помощью построителя запросов запрос на удаление данных. Пусть какой-либо пациент был откреплен от поликлиники по какой-либо причине, и требуется удалить информацию о нем из базы данных. Запрос «Открепление пациента» будет удалять записи из таблицы «Пациенты» по номеру медицинской карточки, который нужно ввести при запуске запроса в поле выводящейся на экран подсказки «Введите № медицинской карточки:»., а также записи с данным пациентом из подчиненных таблиц «Обращения», «Прием», «Диагнозы пациентов» и «Анализы».  Создание запроса в конструкторе представлено на рисунке 3.6. 4 Создание SQL-запросов 4.1 Создание новой таблицы с использованием CREATE TABLE Используя инструкцию CREATE TABLE, создадим запрос на создание новой таблицы, содержащей пять полей различных типов данных, определив в запросе первичный ключ и проиндексировав соответствующие поля, используя предложение CONSTRAINT. В следующем запросе в примере 4.1 создается таблица «Оборудование» с ключевым полем «Код_оборудования». Пример 4.1 CREATE TABLE Оборудование ( Код_оборудования AUTOINCREMENT, Инвентарный_номер INTEGER, Наименование TEXT(255), Стоимость MONEY, Дата_ввода_в_эксплуатацию DATE, CONSTRAINT ind_1 PRIMARY KEY (Код_оборудования));  В результате выполнения этого запроса в БД СУБД MS Access будет создана таблица «Оборудование», представленная в схеме БД следующим образом (рисунок 4.1). На рисунке 4.2 таблица «Оборудование» представлена в режиме конструктора.  4.2 Создание новой таблицы с использованием CREATE TABLE с внешним ключом Создадим запрос на создание еще одной таблицы, содержащей внешний ключ по отношению к первичному ключу предыдущей таблицы. В следующем запросе в примере 4.2 создается таблица «Списанное_оборудование» с внешним ключом «Код_оборудования», связанным с полем «Код_оборудования» в таблице «Оборудование» с каскадным обновлением связанных полей и каскадным удалением связанных записей в схеме БД. Ключевое поле пока что назначать не будем – это будет сделано отдельным запросом. Пример 4.2 CREATE TABLE Списанное_оборудование ( Код_списанного_оборудования AUTOINCREMENT, Код_оборудования INTEGER, Дата_списания DATE, CONSTRAINT ind_2 UNIQUE (Код_оборудования), FOREIGN KEY (Код_оборудования) REFERENCES Оборудование (Код_оборудования) ON UPDATE CASCADE ON DELETE CASCADE);  На рисунке 4.3 представлено, как выглядит часть схемы БД после создания новой таблицы. Как видим, все изменения отобразились.  На рисунке 4.4 представлена информация о связи таблиц «Оборудование» и «Списанное_оборудование». 4.3 Создание нового индекса с использованием CREATE INDEX Используя команду CREATE INDEX, создадим запрос на создание нового индекса, используя различные условия назначения индексов (IGNORE NULL, DISALLOW NULL, PRIMARY), а также типы сортировки. В примере 4.3 создается уникальный индекс, не допускающий ввод повторяющихся значений в поле «Инвентарный_номер» в таблице «Оборудование»: Пример 4.3 CREATE UNIQUE INDEX ind_3 ON Оборудование (Инвентарный_номер DESC) WITH DISALLOW NULL; Ключевые слова DISALLOW NULL не позволяют добавлять в таблицу записи по оборудованию без обязательного ввода инвентарного номера. Зарезервированное слово DESC располагает элементы индекса в убывающем порядке.  Результат создания индекса представлен на рисунке 4.5. В примере 4.4 создается неуникальный индекс с ключевыми словами IGNORE NULL, что позволяет добавлять в таблицу «Оборудование» записи без обязательного ввода стоимости оборудования: Пример 4.4 CREATE INDEX ind_4 ON Оборудование (Стоимость) WITH IGNORE NULL;  Результат создания индекса представлен на рисунке 4.6. Теперь определим ключевое поле таблицы «Списанное_оборудование» – им будет поле «Код_списанного_оборудования». В примере 4.5 создается индекс с зарезервированным словом PRIMARY, что позволяет назначить индексированное поле ключом. Так как такой индекс по умолчанию является уникальным, зарезервированное слово UNIQUE можно опустить. Пример 4.5 CREATE INDEX ind_5 ON Списанное_оборудование (Код_списанного_оборудования) WITH PRIMARY; Результат создания индекса представлен на рисунке 4.7.  4.4 Добавление записей с использованием INSERT INTO Используя команду INSERT INTO, создадим запросы на добавление группы записей (из дополнительной таблицы) и одной записи в существующую таблицу.  Сначала добавим таблицу «Новое_оборудование» со следующими записями (рисунок 4.8): В следующем запросе в примере 4.6 выполняется добавление группы записей из дополнительной таблицы «Новое_оборудование» в таблицу «Оборудование». Пример 4.6 INSERT INTO Оборудование (Инвентарный_номер, Наименование, Стоимость, Дата_ввода_в_эксплуатацию) SELECT [Новое_оборудование].Инвентарный_номер, [Новое_оборудование].Наименование, [Новое_оборудование].Стоимость, [Новое_оборудование].Дата_ввода_в_эксплуатацию FROM Новое_оборудование;  На рисунке 4.9 представлено, как выглядела таблица «Оборудование» до добавления новых записей.  На рисунке 4.10 показаны изменения в таблице «Оборудование» после выполнения запроса. В следующем запросе в примере 4.7 выполняется добавление одной записи в таблицу «Списанное_оборудование». Пример 4.7 INSERT INTO Списанное_оборудование (Код_оборудования, Дата_списания) VALUES (6, '25.04.2018');  На рисунке 4.11 представлено, как выглядела таблица «Списанное_оборудование» до добавления новой записи.  На рисунке 4.12 показаны изменения в таблице «Списанное_оборудование» после выполнения запроса. 4.5 Обновление данных с использованием UPDATE Используя команду UPDATE, создадим запрос на обновление данных в созданных ранее таблицах. В следующем запросе в примере 4.8 сумма оклада по всем должностям из таблицы «Должности» увеличивается на 10%. Пример 4.8 UPDATE Должности SET Сумма_оклада=Сумма_оклада * 1.1; На рисунке 4.13 представлено, как выглядела таблица «Должности» до выполнения запроса.   На рисунке 4.14 показана данная таблица после выполнения запроса. 4.6 Выборка записей с использованием SELECT Используя команду SELECT, создадим запрос на выборку записей из двух (или более) таблиц, используя правила внешнего и внутреннего соединения, а также различные условия отбора и сортировки. В примере 4.9 приведен запрос с использованием правила внутреннего соединения, выдающий сведения о сотрудниках, которые выходят в отпуск летом, с сортировкой в алфавитном порядке по ФИО сотрудника. Пример 4.9 SELECT Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Табельный_номер_сотрудника AS Табельный_номер, Отпуска.Дата_отпуска AS Дата_выхода_в_отпуск, Должности.Название_должности AS Должность FROM Отпуска INNER JOIN (Должности INNER JOIN (Сотрудники INNER JOIN Занимаемая_должность ON Сотрудники.Код_сотрудника=Занимаемая_должность.Табельный_номер_специалиста) ON Должности.Код_должности=Занимаемая_должность.Должность) ON Отпуска.Табельный_номер_сотрудника=Занимаемая_должность.Табельный_номер_специалиста WHERE Отпуска.Дата_отпуска Between #6/1/2019# And #8/31/2019# ORDER BY Сотрудники.Фамилия ASC;  Результат выполнения запроса представлен на рисунке 4.15. В примере 4.10 приведен запрос с использованием правила внешнего соединения, выдающий сведения об обращениях пациентов, при этом в результирующий набор данных будут вкючены также пациенты, чьи сведения об обращениях отсутствуют. Сведения отсортированы в порядке убывания № медицинских карточек, то есть наверху выборки находятся пациенты, зарегистрированные недавно. Пример 4.10 SELECT Пациенты.№_медицинской_карточки, Пациенты.Фамилия, Пациенты.Имя, Пациенты.Отчество, Обращения.Дата_обращения, Услуги.Наименование AS Услуга FROM Пациенты LEFT JOIN ((Услуги RIGHT JOIN Услуги_специалистов ON Услуги.Код_услуги_специалиста=Услуги_специалистов.Услуга) RIGHT JOIN Обращения ON Услуги_специалистов.Код_услуги_специалиста=Обращения.Услуга) ON Пациенты.Код_пациента=Обращения.№_медицинской_карточки ORDER BY Пациенты.№_медицинской_карточки DESC;  Результат выполнения запроса представлен на рисунке 4.16. 4.7 Перекрестный запрос с использованием TRANSFORM Используя команду TRANSFORM, создадим перекрестный запрос. В примере 4.11 приведен запрос, отображающий количество посещений типов врачей по датам. Пример 4.11 TRANSFORM Count (Обращения.Услуга) SELECT Обращения.Дата_обращения AS Дата FROM Обращения INNER JOIN (((Должности INNER JOIN Занимаемая_должность ON Должности.Код_должности=Занимаемая_должность.Должность) INNER JOIN Сотрудники ON Занимаемая_должность.Табельный_номер_специалиста=Сотрудники.Код_сотрудника) INNER JOIN Услуги_специалистов ON Сотрудники.Код_сотрудника=Услуги_специалистов.Специалист) ON Обращения.Услуга=Услуги_специалистов.Код_услуги_специалиста GROUP BY Обращения.Дата_обращения PIVOT Должности.Название_должности;  Результат выполнения запроса представлен на рисунке 4.17. 4.8 Удаление таблицы и индекса с использованием DROP Используя команду DROP, создадим запросы на удаление таблицы и индекса, созданных ранее в БД. В примере 4.12 приведен запрос, который удаляет ранее созданную таблицу «Списанное_оборудование». Пример 4.12 DROP TABLE Списанное_оборудование;  Изменения в схеме данных после удаления таблицы приведены на рисунке 4.18. В примере 4.13 приведен запрос, который удаляет ранее созданный индекс ind_4 из таблицы «Оборудование». Пример 4.13. DROP INDEX ind_4 ON Оборудование;  Изменение списка индексов таблицы «Оборудование» показано на рисунке 4.19. |