метод лабы. Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования
 Скачать 1.67 Mb.
|
|
select * from examen where Ocenka < ALL (select AVG(Ocenka) from examen group by Nom_predm); 4.4 Примеры создания представлений в MySQL В EMS SQL Manager for MySQL для работы с представлениями суще- ствует удобный интерфейс, который доступен из подменю «Представления» выбранной базы данных. Для сохранения представления используется ко- манда «Компилировать». Так же можно использовать для создания представ- ления инструмент SQL-скрипт. Воспользуемся SQL-скриптом для создания представления для базы данных «Учебные заведения, абитуриенты и экзамены». Представление бу- дет отображать наименования ВУЗов, коды специальностей, количество мест, режим обучения и срок обучения для специальностей с количеством мест больше 20. create view v as select vuz.Name_vuza, specialnost.kod_spec, Kol_mest, Regim_obuch, Srok_obuch from vuz, vuz_spec, specialnost where vuz.Name_vuza=vuz_spec.Name_vuza AND specialnost.kod_spec = vuz_spec.Kod_spec AND Kol_mest > 20 with check option; Рисунок 4.3 демонстрирует данные, которые отображает представление. 37 Рисунок 4.3 Следующий оператор UPDATE выполняет обновление представления v. Рисунок 4.4 демонстрирует данные, которые отображает представление по- сле обновления. update v set Kol_mest = 50 where Name_vuza = 'Алт.ГТУ'; Рисунок 4.4 Однако, если попробовать установить значение Kol_mest меньше 20, то новое значение не будет удовлетворять условию WHERE Kol_mest > 20 в определении представления и обновления не произойдет (рис 4.5). update v set Kol_mest = 10 where Name_vuza = 'Алт.ГТУ'; Рисунок 4.5 Следующее представление не является обновляемым. create view v2 as select Kod_spec, AVG(ocenka) from examen group by Kod_spec; 38 Лабораторная работа № 5 Хранимые процедуры и функции. Триггеры. Цель работы: Научиться создавать хранимые процедуры, функции и триггеры в процедурных расширениях SQL. Теоретический материал, необходимый для выполнения работы приведен в слайдах лекций и литературе [3, 5]. 5.1 Задание Для базы данных, созданных в лабораторной работе №3 создать: Хранимые процедуру и функцию с параметрами, реализующие биз- нес – правила предметной области индивидуального варианта (MySQL (MariaDB) или PostgreSQL). Триггеры для событий INSERT, UPDATE и DELETE, реализующие нестандартные правила обеспечения ссылочной целостности или бизнес – правила предметной области индивидуального варианта (MySQL (MariaDB) или PostgreSQL). 5.2 Отчет по лабораторной работе должен содержать: - титульный лист; - цель работы; - задание; - формулировки и SQL – коды триггеров, хранимых процедур и функ- ций, а также результаты их выполнения; - описание инструментальных средств, используемых для создания и выполнения триггеров, хранимых процедур и функций. 5.3 Примеры создания хранимых процедур и функций в MySQL Создадим в EMS SQL Manager for MySQL хранимые процедуры для ба- зы данных «Учебные заведения, абитуриенты и экзамены». Для создания процедур будем использовать SQL-скрипт, а для их выполнения удобный интерфейс, который доступен из подменю «Процедуры» выбранной базы данных. Создадим процедуру new_proc, которая выводит информацию об оцен- ках, полученных абитуриентами на экзамене по заданной дисциплине. CREATE PROCEDURE new_proc( IN n_predm CHAR(20)) BEGIN select * from examen where Nom_predm = 39 (select Nom_predm from predmet where Name_predm = n_predm); END; Выполним процедуру new_proc (рис. 5.1-5.2). Рисунок 5.1 Рисунок 5.2 Создадим функцию new_func, которая определяет количество оценок 5 по заданному предмету. CREATE FUNCTION new_func(n_predm CHAR(20)) RETURNS INT BEGIN DECLARE Var INT DEFAULT 0; select COUNT(ocenka) INTO Var from examen, Predmet where Predmet.Nom_predm = examen.Nom_predm AND ocenka = 5 AND Name_predm = n_predm; RETURN Var; END; Выполним функцию new_func (рис. 5.3). 40 Рисунок 5.3 5.4 Примеры создания триггеров в MySQL Воспользуемся SQL-скриптом в EMS SQL Manager for MySQL для соз- дания триггеров для базы данных «Учебные заведения, абитуриенты и экза- мены». Допустим, что в учебных заведениях существует бизнес-правило: срок обучения на любой специальности не может быть больше 7. Для поддержки этого правила создадим триггер для таблицы «Специальность», который при изменении у специальности срока обучения проверяет, не превышает ли он число 7. Если срок обучения будет больше 7, то операция обновления отме- няется и выдается сообщение «Срок обучения не может быть больше 7». CREATE TRIGGER before_upd_tr1 BEFORE UPDATE ON specialnost FOR EACH ROW BEGIN DECLARE ERROR CONDITION FOR SQLSTATE '45000'; IF (NEW.Srok_obuch>7) then signal ERROR set message_text ='Срок обучения не может быть больше 7'; END IF; END; Замечание. После изменения в таблице specialnost срока обучения (Srok_obuch) необходимо нажать флажок «Фиксировать транзакцию». Изменение срока обучения в таблице specialnost и результат работы триггера демонстрирует рисунок 5.4. 41 Рисунок 5.4 Допустим, что в учебных заведениях существует еще одно бизнес- правило: Абитуриент не может поступать более чем в 1 ВУЗ. Создадим триг- гер для таблицы «Абитуриент_ВУЗ_Спец», который при добавлении нового абитуриента в вуз проверяет, не превысило ли число вузов, в которых он яв- ляется абитуриентом, число 1, что недопустимо. CREATE TRIGGER before_ins_tr1 BEFORE INSERT ON abiturient_vuz_spec FOR EACH ROW BEGIN DECLARE kol_vuz INTEGER(11); DECLARE ERROR CONDITION FOR SQLSTATE '45000'; SELECT COUNT(DISTINCT Name_vuza) into kol_vuz FROM abiturient_vuz_spec WHERE Nom_pasp = NEW.Nom_pasp; IF (kol_vuz >=1) THEN signal ERROR set message_text ='Абитуриент не может поступать бо- лее чем в 1 ВУЗ'; END IF; END Замечание. После добавления в таблицу Abiturient_vuz_spec необходи- мо нажать флажок «Фиксировать транзакцию». Результат работы триггера после добавления в таблицу Abiturient_vuz_spec представлен на рисунке 5.5. 42 Рисунок 5.5 Создадим триггеры для поддержки обязательного потомка для связи между таблицами vuz и vuz_spec (рисунок 5.6). У ВУЗа должна быть одна или более специальностей. Рисунок 5.6 Создадим триггер для таблицы «ВУЗ_Специальность», который при удалении специальности вуза проверяет, если удаляемая специальность единственная в вузе, то удалять ее нельзя. CREATE TRIGGER vuz_spec_before_del_tr1 BEFORE DELETE ON vuz_spec FOR EACH ROW BEGIN DECLARE ERROR CONDITION FOR SQLSTATE '45000'; SET @p=(SELECT COUNT(OLD.Name_vuza) FROM vuz_spec WHERE Name_vuza=OLD.Name_vuza); IF (@p=1)THEN 43 signal ERROR set message_text ='В ВУЗе осталась только одна специ- альность. Удалять специальность нельзя'; END IF; END; Результат работы триггера после удаления из таблицы vuz_spec пред- ставлен на рисунке 5.7. Замечание. После удаления из таблицы vuz_spec необходимо нажать флажок «Фиксировать транзакцию». Рисунок 5.7 Аналогично можно создать триггер для таблицы «ВУЗ_Специальность», который при обновлении внешнего ключа Name_vuza проверяет, если у обновляемого вуза одна специальность, то об- новлять вуз нельзя. Вместо триггера, который при удалении специальности вуза проверяет, если удаляемая специальность единственная в вузе, то запрещает ее удалять, выдавая об этом сообщение пользователю, можно создать триггер для табли- цы «ВУЗ_Специальность», который при удалении специальности вуза про- веряет, остались ли в вузах, в которые она входила, еще специальности. Если нет, то удаляются и вузы. CREATE TRIGGER before_del_tr1 BEFORE DELETE ON vuz_spec FOR EACH ROW BEGIN SET @p=(SELECT COUNT(OLD.Name_vuza) FROM vuz_spec WHERE Name_vuza=OLD.Name_vuza); 44 IF (@p-1=0)THEN DELETE FROM vuz WHERE Name_vuza=OLD.Name_vuza; END IF; END; Исходные таблицы представлены на рисунке 5.8, а результат работы триггера после выполнения оператора DELETE - на рисунке 5.9. DELETE FROM vuz_spec WHERE Kod_spec = 555; Замечание. После выполнения оператора DELETE для просмотра ре- зультата выполнения триггера необходимо обновить таблицы vuz_spec и vuz. Рисунок 5.8 Рисунок 5.9 Создадим триггер для таблицы «ВУЗ», который после добавления вуза добавляет для этого вуза специальность в таблицу vuz_spec. CREATE TRIGGER vuz_after_ins_tr1 AFTER INSERT ON vuz FOR EACH ROW BEGIN insert into vuz_spec (Name_vuza, Kod_spec, Kol_mest) values (NEW.Name_vuza, 111, 10); END; Результат работы триггера представлен на рисунке 5.10. 45 Рисунок 5.10 Лабораторная работа № 6 Администрирование и защита баз данных Цель работы: Научиться работать с транзакциями и индексами, соз- давать пользователей базы данных с разными привилегиями, создавать резервные копии баз данных и восстанавливать базы данных. Теоретический материал, необходимый для выполнения работы приведен в слайдах лекций и литературе [3, 5]. 6.1 Задание 1. Создать транзакцию, включающую 2-3 оператора INSERT,UPDATE или DELETE, вносящие изменения в таблицу БД. Убедится в присутствии изменений в таблице БД (командой SELECT). Затем откатить транзакцию и убедится, что таблица возвратилась к состоянию, в котором она находилась до начала транзакции. Создать транзакцию, вносящую изменения в таблицу БД. Убедится в присутствии изменений в таблице БД (командой SELECT). Затем фиксировать транзакцию и убедится, что изменения сохранились в таблице. Задание выполнить в консольных клиентах MySQL (MariaDB) или PostgreSQL. 2. Создать двух пользователей. Установить пользователям привилегии: первому – SELECT, второму – SELECT, INSERT и UPDATE. Проверить ус- тановленные привилегии. Отменить у второго пользователя привилегию UPDATE. Проверить установленные привилегии. Выполнить от имени ново- го пользователя некоторые выборки и изменения (с ограниченными правами доступа) в таблицах БД. Убедится в правильном контроле прав доступа. За- дание выполнить в консольных клиентах MySQL (MariaDB) или PostgreSQL. 3. Создать резервную копию базы данных с использованием консольно- го клиента СУБД MySQL (MariaDB) или PostgreSQL и утилиты mysqldump или pg_dump. Удалить базу данных. Восстановить базу данных из резервной копии. 4. Выполнить демонстрационный сценарий для работы с индексами. В этом сценарии есть однотипные запросы на выборку из одной таблицы, и на выборку из соединения таблиц для случаев с индексом по фильтруемому объекту и без него. Посмотреть план выполнения запросов и сравнить их для обоих случаев (MySQL (MariaDB) или PostgreSQL). 46 6.2 Отчет по лабораторной работе должен содержать: - титульный лист; - цель работы; - задание; - описание процесса выполнения работы. 6.3 Управление пользователями в MySQL Список пользователей: mysql> SELECT User, Host FROM mysql.user; Текущий пользователь: mysql> SELECT user(); Список прав у пользователя root@localhost: mysql > SHOW GRANTS FOR root @ localhost ; Создание нового пользователя mysql> CREATE USER 'user' @ 'localhost' IDENTIFIED BY 'secret' ; Добавление и удаление привилегий пользователям: Добавим выбранные привилегии для всех таблиц БД dbname пользова- телю 'user'@'localhost': mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON dbname.* TO 'user' @ 'localhost' ; Добавим все привилегии для всех таблиц БД dbname пользователю 'user'@'localhost': mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user' @ 'localhost' ; Удаление прав пользователя 'user'@'localhost' для БД dbname: mysql> REVOKE ALL ON dbname.* FROM 'user' @ 'localhost' ; Удаление пользователя mysql > DROP USER user @ localhost ; Перезагрузка привилегий mysql> FLUSH PRIVILEGES; 6.4 Транзакции в MySQL В MySQL по умолчанию каждая инструкция воспринимается системой как отдельная транзакция. То есть, после каждого изменения данных — про- исходит автоматическая запись в физическую базу данных. Для того чтобы это изменить и создавать транзакции, состоящие из не- скольких инструкций необходимо использовать команды: Start transaction; (также, можно написать BEGIN; начало транзакции), Commit; (подтверждаем транзакцию, производится запись в физическую БД), Rollback; (отменяем транзакцию, не производится запись в физическую БД). При параллельном доступе к данным возникает целый ряд проблем: 47 - потерянные изменения (когда вторая транзакция начинается посреди первой, а они пишут и читают одни и те же данные — возникают искажения в записи / чтении); - «грязное чтение» — первая транзакция ещё не зафиксирвала измене- ния в физической БД, а вторая уже начала чтение (по сути более частный случай потерянных изменений); - неповторяющееся чтение — одна транзакция считала значение и на- чала его обрабатывать, вторая обновила до завершения первой транзакции, в результате искажение информации. - чтение строк фантомов… (вклинивание в длительную транзакцию и искажение). Для того, чтобы избежать искажения информации при параллельном доступе были разработаны различные уровни изоляции транзакций: Read uncommitted (незафиксированное чтение) — наименее защищен- ный уровень транзакций. Этот уровень рекомендуется использовать только в тех случаях, когда все транзакции работают в режиме чтения. Read committed (фиксированное чтение) — исключается «грязное чте- ние», но другим транзакциям разрешено изменять заблокированные строки. Repeatable read (повторяемое чтение) — накладывает блокировки на обрабатываемые транзакцией строки и не допускает их изменение другими транзакциями, но не запрещает добавление новых записей, что может при- вести к появлению строк-фантомов. Используется по умолчанию в MySQL. Serializable (сериализуемость) — самый надежный уровень изоляции, полностью исключающий взаимное влияние транзакций. Для изменения уровня изоляции транзакций используется оператор SET TRANSACTION ISOLATION LEVEL. Его синтаксис в MySQL: SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } По умолчанию уровень изоляции устанавливается для последующей (не начальной) транзакции. При использовании ключевого сло- ва GLOBAL данная команда устанавливает уровень изоляции по умолчанию глобально для всех новых соединений, созданных от этого момента. Однако для того чтобы выполнить данную команду, необходима привилегия SUPER. При использовании ключевого слова SESSION устанавливается уровень изо- ляции по умолчанию для всех будущих транзакций, выполняемых в текущем соединении. Для просмотра используемого уровня изоляции транзакций использует- ся оператор: SHOW VARIABLES LIKE '%tx_isolation%'; 48 6.5 Создание дампа и восстановление базы данных в MySQL Утилита mysqldump позволяет получить дамп содержимого базы дан- ных. Дамп будет содержать набор команд SQL для создания и/или заполне- ния таблиц. Создадим резервную копию базы данных new_vuz (хост: localhost, порт: 3306) с помощью утилиты mysqldump (рис. 6.1). После ключа –u указывается имя пользователя (root), ключ –p задает пароль (root), опера- ция ‘>’ позволяет разместить дамп в файле new_vuz.sql, опция –databases (сокращенный вариант –B) добавляет в дамп команды create database new_vuz; use new_vuz; опция –no-data (сокращенный вариант –d) указыва- ет mysqldump, не скидывать данные в дамп. Чтобы в дамп попали хранимые процедуры и функции, нужно добавить ключ –R.Триггеры и представления попадают в дамп по умолчанию. C:\Users\admin>mysqldump -uroot -proot -B -R new_vuz > C:\Works\new_vuz.sql Рисунок 6.1 Базы данных в MySQL находятся в C:\ProgramData\MySQL\MySQL Server 5.5\data. C помощью консольного клиента MySQL удалим базу данных new_vuz (рис. 6.2): mysql>drop database new_vuz; Рисунок 6.2 Восстановим базу данных new_vuz из резервной копии с помощью ути- литы mysql (рис. 6.3): C:\Users\admin>mysql -uroot -proot < C:\Works\new_vuz.sql Рисунок 6.3 49 В консольном клиенте MySQL подключимся к базе данных new_vuz и посмотрим ее содержимое (рис. 6.4). Рисунок 6.4 6.6 Индексы в MySQL Индексы – это структуры данных, создаваемые с целью повышения производительности поиска записей в таблицах. Таблицы в базе данных мо- гут иметь большое количество строк, которые хранятся в произвольном по- рядке, и их поиск по заданному критерию путем последовательного про- смотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указа- телей на соответствующие строки таблицы и, таким образом, позволяет ис- кать строки, удовлетворяющие критерию поиска. Индексы - ключ к высокой производительности MySQL, их важность увеличивается по мере роста объема данных в базе. Индексы нужно созда- вать для столбцов, по которым производится поиск в части WHERE соединяются таблицы при JOIN сортируются и группируются записи при ORDER BY и GROUP BY производится поиск MIN() и MAX() Индексы могут быть составными, в этом случае важен порядок столбцов. Чтобы оптимизировать запрос, сначала его нужно исследовать. Для это- го допишите перед SELECT слово EXPLAIN, и MySQL покажет план выпол- нения запроса. В первую очередь интерес представляет информация об ис- пользовании индексов. 50 Описание всех значений команды EXPLAIN и пример оптимизации за- проса можно посмотреть в документации. Добавить индексы можно с помощью запросов вида: ALTER TABLE table_name ADD INDEX index_name (column_name);. Лабораторная работа № 7 Технологии доступа к базам данных Цель работы: Научится использовать технологии доступа к базе данных в клиентских программах. Теоретический материал, необходимый для выполнения работы приведен в слайдах лекций и литературе [6, 7, 8]. 7.1 Задание - Создать программу на Qt С++ или С# или Java для доступа к базе дан- ных, созданной в предыдущих лабораторных работах. В программе должно быть реализовано: просмотр содержимого таблиц базы данных; добавление, удаление и редактирование содержимого таблиц базы данных; выполнение параметризованных SQL-запросов; выполнение хранимой процедуры и триггера. 7.2 Отчет по лабораторной работе должен содержать: - титульный лист; - цель работы; - задание; - описание и тестирование клиентских приложений. 7.3 Рекомендации по выполнению лабораторной работы Примеры клиентских приложения для работы с базами данных с ис- пользованием технологий ODBC, JDBC, ADO .Net и языков программи- рования Qt С++, Java и С# находятся в слайдах лекций. 51 Список использованных источников 1. Диго С.М. Базы данных. Проектирование и создание: учебно- методический комплекс. – М.: Изд. центр ЕАОИ. 2008. – 171 с. 2. Руководство по MySQL/ [Электронный ресурс]. - URL: https://metanit.com/sql/mysql/ 3. Кузнецов М.В. Самоучитель MySQL5/ М.В. Кузнецов, И.B. Симдянов. – СПб.: БХВ-Петербург, 2007. – 560 с. 4. Руководство по PostgreSQL/ [Электронный ресурс]. - URL: https://metanit.com/sql/postgresql/ 5. Моргунов Е.П. Язык SQL. Базовый курс: учеб. – практ. пособие/ Е.П. Моргунов; под ред. Е.В. Рогова, П.В. Лузанова; Postgres Professional.- M., 2017. – 256 c. 6. Java и базы данных / [Электронный ресурс]. - URL: https://metanit.com/java/database/1.1.php 7. Шлее М. Qt 5.3 Профессиональное программирование на C++.– СПб.:БХВ-Петербург, 2015. – 928 с. 8. Троелсен, Э. Язык программирования C# 5.0 и платформа .NET 4.5, 6- е изд.: Пер. с англ. — М.: ООО “И.Д. Вильямс”, 2013. — 1312 с. |