Лекция № 8

Тема: "Запросы на изменение данных"

План

1. Запросы на изменение данных

2. Запросы на добавление записей в таблицу

3. Запросы на обновление данных в таблице

4. Запросы на удаление данных из таблицы

5. Запросы на создание таблицы

1. Запросы на изменение данных

Важное значение для решения различных информационных задач по работе с БД имеют запросы на изменение данных. Такие запросы вносят изменения сразу в группу записей за одну операцию. Таким образом, результатом запросов на изменение является не набор данных, как в запросах на выборку, а изменение данных в самих таблицах.

Запросы на изменение данных широко применяются для ввода данных при импорте из внешних источников, перемещения записей или их элементов из одних таблиц в другие таблицы, при массовой однотипной коррекции или чистке данных, а также для архивации и экспорта данных.

Существует четыре разновидности запросов на изменение:

• запросы на добавление;

• запросы на обновление;

• запросы на удаление;

• запросы на создание таблицы.

2. Запросы на добавление записей в таблицу

Данный тип запроса позволяет добавить в таблицу новую запись и заполнить ее поля указанными значениями.Для добавления новой записи в таблицу используют команду вида:

Insert

Into имя_таблицы(список полей)

Values(список значений)

Список полей и список значений – это перечень значений через запятую. При этом важно, чтобы очередность и типы соответствующих элементов в этих списках совпадали.

Пример. Добавим в таблицу students новую запись.

Insert

Into students (id_stud, fam, imya, otch, datar, pol, finance)

Values (123, 'Иванов', 'Иван', 'Иванович', '1995-02-15', 'м', 'Госзаказ')

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

Если ключевое поле является счетчиком, то мы не можем явно задавать для него новое значение и должны позволить сделать это самой СУБД . В этом случае полю-счетчику целесообразно присваивать значение NULL, а СУБД сама запишем подходящее значение.

Insert

Into students (id_stud, fam, imya, otch, datar, pol, finance)

Values (null, 'Иванов', 'Иван', 'Иванович', '1995-02-15', 'м', 'Госзаказ')

Если в списке Values задаются значений для всех полей таблицы без исключения и в порядке их физического следования, то список полей можно не указывать.

Insert

Into students

Values (null, 'Иванов', 'Иван', 'Иванович', '1995-02-15', 'м', 'Госзаказ')

Если в таблице имеются поля с ограничением NULL (не обязательны для заполнения) или поля со значениями по умолчанию (default), или поле-счетчик, то такие поля можно не заполнять.

Предположим, что поле id_stud - это счетчик, поле datar не обязательное для заполнения, поле pol имеет значение по умолчанию 'м', поле finance имеет значение по умолчанию 'Бюджет'. Добавим новую запись, не заполняя перечисленные поля.

Insert

Into students (fam, imya, otch)

Values ('Иванов', 'Иван', 'Иванович')

3. Запросы на обновление данных в таблице

Запрос на обновление позволяет изменить значения заданных полей таблицы. При этом можно изменить как все записи, так и записи, удовлетворяющиеся некоторому условию.

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

Update имя_таблицы

Set поле=новое_значение, поле=новое_значение . . .

[Where условия]

Пример. В таблице Ocenki все оценки по Физике увеличить на 2.

Update ocenki

Set ocenka=ocenka+2

Where predmet='Физика'

Обновление данных в связанных таблицах

В запросах на изменении данных допускается использовать связанные таблицы. В этом случае команда запроса для разных СУБД имет разный вид.

SQL Server

В данной СУБД запрос на обновление имеет вид:

update таблица

set поле = новое значение

from список связанных таблиц

[Where условия]

Пример. В таблице Ocenki все оценки по Физике для группы 38 увеличить на 2. Запрос обращается к 3-м таблицам: grupy, students, ocenki.

update Ocenki

set ocenka = ocenka + 2

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

           inner join ocenki on students.id_stud=ocenki.id_stud

Where grupa like '38%'

      and predmet='Физика'

MySQL

В данной СУБД запрос на обновление имеет вид:

update список_таблиц_через_запятую

set поле = новое значение

where условие для связи таблиц

[and дополнительны условия]

Пример. В таблице Ocenki все оценки по Физике для группы 38 увеличить на 2. Запрос обращается к 3-м таблицам: grupy, students, ocenki.

update grupy, students, ocenki

set ocenka = ocenka+2

where grupy.id_grup=students.id_grup and students.id_stud=ocenki.id_stud

and grupa like '38%' and predmet='Физика'

Microsoft Access

В данной СУБД запрос на обновление имеет вид:

Update список_связанных_таблиц

Set поле=новое_значение, поле=новое_значение . . .

[Where условия]

Пример. В таблице Ocenki все оценки по Физике для группы 38 увеличить на 2. Запрос обращается к 3-м таблицам: grupy, students, ocenki.

Update grupy inner join students

             on grupy.id_grup=students.id_grup

             inner join ocenki

             on students.id_stud=ocenki.id_stud

Set ocenka=ocenka+2

Where grupa like '38%'

      and predmet='Физика'

Замечание. Использование запроса на изменение данных требует от пользователя повышенного внимания. В случае его неправильного или невнимательного применения можно безвозвратно изменить огромные массивы информации в таблицах. Поэтому, перед ее использование рекомендуется создавать резервные копии оригинальных таблиц (см .ниже).

4. Запросы на удаление данных из таблицы

Запрос на удаление позволяет удалить из таблицы все записи, которые удовлетворяют условию. Если условие не задано, то таблица очищается полностью.

Как и для запрос ана обновление рекомендуется перед удалением обязательно создавать копии оригинальных таблиц.

Сама команда имеет вид:

Delete

From имя_таблицы

[Where условия]

Пример. Из таблицы ocenki удалить все оценки по Физике.

Delete

From ocenki

Where predmet='Физика'

В запросах на удаление данных допускается использовать связанные таблицы. В этом случае команда удаления записывается так:

Delete таблица_для_удаления

From список_связанных_таблиц

[Where условия]

Пример. В таблице Ocenki удалить оценки по Физике для группы 38.

Delete ocenki

From grupy inner join students

           on grupy.id_grup=students.id_grup

           inner join ocenki

           on students.id_stud=ocenki.id_stud

Where grupa like '38%'

      and predmet='Физика'

5. Запросы на создание таблицы

Запросы на создание таблицы за одну операцию создают новую таблицу с заполненными данными на основе всех или части данных из одной или нескольких таблиц. Такие запросы чаще всего решают задачи по реорганизации базы данных, архивированию или резервированию данных, а также могут применяться для создания отчетов или состояний базы данных по определенным временным промежуткам. такой запрос основан на обычном запросе выбора Select, но с указанием  параметра Into. Команда для создания таблицы имеет вид:

--Для SQL Server

Select список_полей

Into новая_таблица

From исходная_таблица

[Where условия]

#Для MySQL

Create Table новая_таблица

Select список_полей

From исходная таблица

[Where условия]

Пример. Отобрать в новую таблицу с именем t1996 всех студентов, которые родились в 1996 году.

--Для SQL Server

Select *

Into t1996

From students

Where datar between '01.01.1996' and '31.12.1996'

#Для MySQL

Create Table t1996

Select *

From students

Where datar between '01.01.1996' and '31.12.1996'

Если раздел Where не указан, то создается полная копия исходной таблицы.

Пример. Пусть пользователь планирует применить к таблице students запрос на обновление данных. Необходимо выполнить резервное копирование таблицы под именем students2.

--Для SQL Server

Select *

Into students2

From students

#Для MySQL

Create table students2

Select *

From students

Допускается создание ноой таблицу на основе связанных таблиц.

Пример. Отобрать название группы, ФИО и дату рождения студентов мужского пола. Результат отбора поместить в таблицу studMen.

--Для SQL Server

Select nazv_grup, fam, imya, otch, datar

Into studMen

From grupy inner join students

           on grupy.id_grup=students.id_grup

Where pol = 'м'

#Для MySQL

Create Table studMen

Select nazv_grup, fam, imya, otch, datar

From grupy inner join students

           on grupy.id_grup=students.id_grup

Where pol = 'м'

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

Для предотвращения появления такой ошибки рекомендуется перед запросом на создание таблицы пытаться удалить ее, если она уже создана. Для этого используют команду вида:

--Для SQL Server

If OBJECT_ID('имя_таблицы', 'U') IS NOT NULL

Drop Table имя_таблицы

#Для MySQL

Drop Table If Exists имя_таблицы

Пример. Пусть необходимо выполнить резервирование таблицы students под именем students2. При этом о наличии таблицы students2 в базе заранее неизвестно.

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

If OBJECT_ID('students2', 'U') IS NOT NULL

Drop Table students2

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

Select *

Into students2

From students

Пример. Запишем предыдущий пример для MySQL.

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

Drop Table If Exists students2

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

Select *

Into students2

From students

Вопросы для самоконтроля

1. Для чего используются запросы на изменение данных? Какие типы таких запросов вы знаете?

2. Опишите запрос на добавление данных в таблицу. Как задать значение для поля счетчика?

3. Опишите запрос на обновление данных. Как в запросе указать связанные таблицы? Пример.

4. Опишите запрос на удаление данных. Как в запросе укаазать связанные таблицы?

5. Опишите запрос на создание таблицы в SQL Server. Как удалить существующую таблицу, если она существует?

6. Опишите запрос на создание таблицы в MySQL. Как удалить существующую таблицу, если она существует?