Практическое занятие № 7

Тема: Запросы на изменение данных в разных СУБД

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

 

Ход работы

 

Пусть имеется БД "dbase" в формате SQL Server.  Данная БД состоит из 6 таблиц со следующей структурой:

 

Таблицы связаны так:

 

Главная таблица Подчиненная таблица Поле для связи Тип связи
otdelen spec id_otd 1 : ∞
spec grupy id_spec 1 : ∞
grupy students id_grup 1 : ∞
students ocenki id_stud 1 : ∞
predmety ocenki id_predmet 1 : ∞

 

 

Выполните подключение БД к серверу (если она отсутствует).

 

Указания: разархивируйте архив.

Запустите утилиту "Microsoft SQL Server Management Studio", пройдите регистрацию введя информацию: Параметры подлинности = "Проверка подлинности SQL Server", Имя пользователя=sa, пароль=111 (у Вас может быть свой пароль).

В дереве объектов вызовите контекстное меню папки "Базы данных" и выберите команду "Присоединить". В правой части появится окно, в котором с помощью кнопки "Добавить" укажите файл из архива и щелкните ОК.

В результате у вас на сервере будет зарегистрирована новая БД.

Для создания запроса выберите в левой части нужную БД и щелкните на кнопке "Создать запрос"   В правой части окна введите текст запроса.

 

 Для выполнения запроса щелкните на кнопке "Выполнить" . В нижней части окна отобразится набор отобранных данных.

 

C помощью команды Insert добавьте в таблицу students произвольную запись с заполнением всех полей, кроме поля id_stud (поле-счетчик).

 

Указания: введите  команду вида:

 

insert into students

(id_grup,fam,imya,otch,datar,finance,pol)

values

(1,'Яковлев','Александр','Николаевич','25.04.1995','Госзаказ','м');

 

Откройте таблицу students и убедитесь, что новая запись добавлена в конец таблицы.

 

С помощью команды Insert добавьте в таблицу students произвольную запись без заполнения полей со значениями по умолчанию (finance='Госзаказ', pol='м').

 

Указания:  введите  команду вида:

 

insert into students

(id_grup,fam,imya,otch,datar)

values

(1,'Новиков','Никита','Владимирович','15.11.1994');

 

Откройте таблицу students и убедитесь, что новая запись добавлена в конец таблицы, и поля finance и poёl заполнены значениями по умолчанию.

 

В таблице students в поле finance текст "Госзаказ" заменить на "Бесплатно".

 

Указания: введите текст запроса:

 

update students

set finance='Бесплатно'

where finance='Госзаказ';

 

После выполнения запроса в нижней части окна появится сообщение о количестве обновленных записей.

 

Для студентов из групп ПКС, увеличить балл по математике на 10.

 

Указания: напишем запрос с помощью связывания таблиц:

 

update ocenki

set ocenka=ocenka+10

from grupy inner join students on grupy.id_grup=students.id_grup

           inner join ocenki on students.id_stud=ocenki.id_stud

           inner join predmety on ocenki.id_predmet=predmety.id_predmet

where nazv_grup like '%ПКС%'

      and nazv_predmet='Математика';

 

После выполнения запроса в нижней части окна появится сообщение о количестве обновленных записей.

 

В таблице students для студентов, имеющих средний балл ниже среднего по учебному заведению, заменить значение в поле Отчество на значение «Плохо».

 

Указания:  Для работы со средними баллами студентов создадим представление с именем  "stud_srbal".

 

Create View stud_srbal

As

    Select id_stud, avg(ocenka) as srbal

    From ocenki

    Group by id_stud;

 

Напишем запрос на обновление с помощью связывания таблицы students с представлением и подзапроса для нахождения среднего балла:

 

update students

set otch='Плохо'

from students inner join stud_srbal on students.id_stud=stud_srbal.id_stud

where srbal<(select avg(srbal) from stud_srbal);

 

После выполнения запроса в нижней части окна появится сообщение о количестве обновленных записей.

 

Из таблицы students удалите всех студентов со средним баллом ниже 3,25.

 

Указания: Для работы со средними баллами студентов будем использовать созданное ранее представление "stud_srbal".

 

Напишем запрос с помощью связывания таблиц.

 

delete students

from students inner join stud_srbal

              on students.id_stud=stud_srbal.id_stud

where srbal<3.25;

 

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

 

Из таблицы students удалите всех студентов с номером группы 38.

 

Указания:  Напишем запрос с помощью связывания таблиц.

 

delete students

from grupy inner join students

           on grupy.id_grup=students.id_grup

where nazv_grup like '38%';

 

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

 

Из таблицы ocenki удалите все оценки по физике.

 

Указания: данный запрос выполняется по одной таблице ocenki:

 

delete ocenki

from ocenki inner join predmety on ocenki.id_predmet=predmety.id_predmet

where nazv_predmet='Физика';

 

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

 

Создайте копию таблицу students под именем students2.

 

Указания: запрос на создание таблицы будет иметь вид:

 

select *

into students2

from students;

 

Попробуйте выполнить этот запрос еще раз. У вас появится сообщение об ошибке, что таблица students2 уже существует. Внесите изменения в предыдущий пример, чтобы копия таблицы создавалась всегда заново.

 

Указания: запрос на создание таблицы будет иметь вид:

 

--вначале удаляем таблицу students2, если она существует

if object_id('students2', 'U') is not null

drop table students2;

 

go

 

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

select *

into students2

from students;

 

Скачайте исходную БД в формате MySQL.  Выполните все рассмотренные выше запросы в этой СУБД.

 

Указания: распакуйте архив. В архиве находится файл со скриптом БД.

 

Запустите утилиту EMS SQL Manager for MySQL. В правой части окна программы выберите команду "Выполнить SQL скрипт". В новом окне выберите команду "Выполнить скрипт из файла". В диалоговом окне укажите распакованный файл скрипта. Если появится окно регистрации, то укажите Пользовать=root, Пароль=111. Если такого окна не появилось, то нажимайте в окне ОК. Появится сообщение, что скрипт выполнен успешно и появится окно для регистрации БД. Перенесите ее в правую часть окна и нажмите ОК. В результате БД будет установлена, зарегистрирована и готова к работе.

В левой части окна можете открыть БД, просмотреть ее таблицы.

 

Для выполнения запросов в окне выполните команду "Инструменты - Показать редактор SQL". В результате в правой части окна откроется поле для ввода тестов запросов.

 

Замечения для MySQL.

 

1) в запросе insert при добавлении новой записи значение для поля datar нужно записывать в формате MySQL: yyyy-mm-dd

 

2) Обратите внимание, что в запросе на обновление по связанным таблицам команда запроса будет иметь иной вид.

Например, для студентов из групп ПКС, увеличить балл по математике на 10 можно с помощью запроса:

 

Напишем запрос с помощью связывания таблиц:

 

update grupy, students, ocenki, predmety

set ocenka=ocenka+10

where grupy.id_grup=students.id_grup

      and students.id_stud=ocenki.id_stud

      and ocenki.id_predmet=predmety.id_predmet

      and nazv_grup like '%ПКС%'

      and nazv_predmet='Математика';

 

Для студентов, имеющих средний балл ниже среднего по учебному заведению, заменить значение в поле Отчество на значение «Плохо» можно с помощью запроса:

 

update students, stud_srbal

set otch='Плохо'

where students.id_stud=stud_srbal.id_stud

      and srbal<(select avg(srbal) from stud_srbal);

 

3) При создании копии таблицы необходимо использовать другой синтаксис команды:

 

##удаляем копию таблицы students2

drop table if exists students2;

 

##создаем новую копию таблицы

create table students2

select * from students;