Цель работы:
Изучить команды изменения содержимого таблицы данных и научиться использовать их на практике.
Задание:
а)изучить команды создания, удаления и модификации записи в таблице данных;
б)заполнить записями таблицы данных (из лабораторных работ N 2 и 3) не менее 15 штук (в режиме sql-скрипта, Interactive SQL, SQL Explorer);
в)научиться использовать команды удаления и модификации таблицы данных;
г)изучить команды по работе с индексами;
д)cоздать индексы на ключевые атрибуты таблиц;
е)оформить и сдать отчет по лабораторной работе N 3.
Правами применять команды добавления, модификации или удаления данных к той или иной таблице данных обладают: создатель базы или таблицы данных, SYSDBA, пользователи, которым были назначены соответствующие права (командами GRANT (назначить) и REVOKE (отменить право)).
Подробный синтаксис команд модификации таблицы данных представлен в документе Language References, мы ограничимся кратким описанием.
Создание (добавление) записи в таблицу данных
Команда INSERT предназначена для добавления данных в таблицу данных, с ее помощью можно добавить в указанную таблицу одну или несколько строк одновременно.
INSERT INTO <object> [{LIST_col}] {VALUES (LIST_<val>) | <select_epxr>};
<object> - имя таблицы или представления
<val> - список значений, которые необходимо вставить в соответствующие поля таблицы.
Замечание:
1) если в списке столбцов перечислены не все столбцы таблицы, то те столбцы, которые не включены в список получат значения NULL, кроме тех случаев, когда при описании полей таблицы использовалась опция DEFAULT (значение по умолчанию).
2) если в базе данных были созданы триггеры, активизирующиеся при команде INSERT и генерирующие свои данные при вставке в поле таблицы (например, генератор следующего значения), то значения таких столбцов могут отличаться от тех значений, которые были заданы в команде INSERT явно.
Пример. Добавим в таблицу Личность следующую запись: идентификатор - 9, ФИО - Попова Галина Анатольевна, дата рождения - 27.05.1977 (рисунок 1).
INSERT INTO Lichnost (ID, Family, Name, Surname, Birthday)
VALUES (9,'Попова','Галина','Анатольевна', '27.05.1977');
Рисунок 1 - Добавление записи в таблицу данных
Если вы работаете в режиме sql-скрипта, то необходимо вначале подсоединиться к базе данных, а затем вставлять в таблицу данных значения.
Пример. Вставим в таблицу данных Tel следующую запись ID - 9 (для предыдущей записи), Number - 52-11-99, Comment - Телефон домашний. Файл sql-скрипта должен иметь следующее содержание:
CONNECT "C:\BD\Tel.gdb" USER "SYSDBA" PASSWORD "masterkey";
INSERT INTO Tel (ID,Number,Comment)
VALUES(9,'52-11-99','Телефон домашний');
Модификация (изменение) данных
Команда UPDATE предназначена для изменения всех или части строк в таблице или представлении.
Краткий синтаксис команды можно описать следующим образом.
UPDATE {table | view} SET LIST_<sets> [WHERE <search_condition>];
Существенное значение в команде UPDATE играет конструкция WHERE, определяющая, какие именно строки будут подлежать изменению.
Конструкция SET задает перечень изменяемых столбцов и их новых значений. Новые значения могут быть любой конструкцией языка SQL, а также могут задаваться через значения других столбцов таблицы.
Пример, предположим, изменился адрес абонента с уникальным идентификатором ID=5.
Рассмотрим соответствующую команду изменения данных:
UPDATE Adr SET Street='пр. Победы', Home=87, Box=3
WHERE ID=5;
В одной команде UPDATE могут быть изменены значения нескольких строк таблицы данных, количество строк, попадающих под изменение, определяется конструкцией WHERE. Например, если бы мы забыли указать в качестве условия уникальный идентификатор личности, после выполнения команды изменилось бы содержание всех строк таблицы Adr (рисунок 2).
UPDATE Adr SET Street='пр. Победы', Home=87, Box=3
Рисунок 2 - Изменение данных в таблице данных
Удаление данных
Команда DELETE удаляет из таблицы одну или несколько строк в зависимости от задаваемых условий удаления. Краткий синтаксис команды:
DELETE [TRANSACTION transaction] FROM table {[WHERE <search_condition>]};
Исключительное значение в команде DELETE играет конструкция WHERE, определяющая, какие именно строки подлежат удалению. Если она отсутствует, то будут удалены все строки таблицы, то есть значением конструкции WHERE по умолчанию является истина (как и в случае модификации данных).
Например, удалим из таблицы Lichnost пользователя с ID=5.
Если мы непосредственно введем команду
DELETE FROM Lichnost WHERE ID=5;
то система выдаст ошибку (рисунок 3), поскольку по внешним ключам имеются связи в других таблицах.
Рисунок 3 - Нарушение целостности по внешним ключам
Поскольку с этим пользователем возможны связи по внешнему ключу из таблиц Adr и Tel, предварительно удалим из них соответствующие записи.
DELETE FROM Adr WHERE ID=5;
DELETE FROM Tel WHERE ID=5;
Только после выполнения двух предыдущих команд выполним исходную:
DELETE FROM Lichnost WHERE ID=5;
Индексы
Индексы предназначены для ускорения поиска данных на запросы в соответствии с заданными условиями. В индексе хранятся значения индексированного столбца или столбцов наряду с указателями на все дисковые блоки, которые содержат строки с соответствующими значениями.
При выполнении запроса InterBase вначале определяет список индексов, связанных с данной таблицей. Затем устанавливает оптимальный способ поиска: с использованием индексов или без использования. Если InterBase решает использовать индекс, то поиск ведется сначала по ключевым словам в индексе, а затем, используя указатели, осуществляется просмотр самих таблиц для дополнительной фильтрации и окончательной выборки требуемых данных.
Уменьшение времени поиска при помощи диска происходит за счет уменьшения количества обращений к диску, чем при последовательном чтении записей.
Индекс может быть определен как для отдельного столбца таблицы, так и на множество столбцов.
Однако индексирование имеет недостатки. Основной из них заключается в следующем. При добавлении и изменении данных в таблице должны обновляться и индексы, таким образом, платой за быстрый поиск служит увеличение времени на обновление данных.
В этих условиях при проектировании базы данных необходимо находить разумный компромисс между требованиями по ускорению поиска данных и требованию по скорости их обновления. В частности, использование индексов для таблиц небольшого объема неоправданно, или, если поиск по некоторым полям происходит редко, то и создавать индекс на это поле также не имеет смысла.
В тоже время индексы эффективно использовать при работе с данными, которые используются часто, а изменяются достаточно редко. Если часто используют запросы, требующие соединения нескольких таблиц по какому-либо полю, либо группе полей, то от индексирования этих полей может быть получен значительный эффект.
Создание индексов
Индекс можно создать отдельно при помощи команды CREATE INDEX, либо автоматически при создании таблицы.
Рассмотрим синтаксис команды создания индекса
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX index ON table (LIST_col);
ASC[ENDING] | DESC[ENDING] - задает способ упорядочения данных по возрастанию / убыванию ключей;
index - задает имя индекса;
table - задает имя индексируемой таблицы;
col - задает имя столбца, по значениям которого строится индекс (столбцов может быть несколько).
Пример. Создадим индексы на первичные ключи отношений базы данных "Телефон".
CREATE INDEX Ind_L ON Lichnost ID;
CREATE INDEX Ind_A ON Tel ID;
CREATE INDEX Ind_T ON Tel ID,Number;
Поскольку в таблице телефонов абонентов мы часто в запросах используем два атрибута - идентификатор абонента и номер его телефона, выгоднее будет использовать индекс на два поля одновременно. Такие индексы можно использовать как и одиночный для того поля, который указан в списке первым. В нашем случае это - ID.
Изменение индекса
Синтаксис
ALTER INDEX name {ACTIVE | INACTIVE};
Команда ALTER INDEX не предназначена для явного изменения индекса. Она позволяет лишь активировать, либо деактивировать существующий индекс.
Пример. Деактивируем индекс Ind_T, а затем вновь активизируем его.
ALTER INDEX Ind_T INACTIVE;
ALTER INDEX Ind_T ACTIVE;
На использование команды ALTER INDEX накладываются некоторые ограничения:
-нельзя выполнять команду, если изменяемый индекс используется в данный момент времени;
-команда неприменима для изменения состава столбцов в индексе. Для этого необходимо вначале удалить индекс, а затем создать его снова.
Удаление индексов
Синтаксис:
DROP INDEX name;
1. Кто имеет право применять команды добавления, модификации или удаления данных из таблицы данных?
2. Какие команды используются для добавления, модификации и удаления данных из таблицы?
3. При использовании команды INSERT требуется ли указывать значения для всех атрибутов? Обоснуйте ответ.
4. Если в команде INSERT не указан перечень атрибутов, то всегда ли будет выполняться команда?
5. К какому результату приведет выполнение следующих команд:
а) UPDATE Telephone SET Street='пр. Победы';
б) DELETE FROM Telephone;
6. В чем заключается сущность процесса индексирования данных? Цель его использования?
7. Расскажите принцип построения бинарных деревьев? За счет чего достигается ускорения поиска данных в случае использования бинарных деревьев?
8. Какие команды языка SQL используются для работы с индексами?