Обновление единственной записи
Изменить название блюда с кодом БЛ=5 на Форшмак, увеличить его выход на 30 г и установить NULL-значение в столбец Труд.
UPDATE Блюда
SET Блюдо = 'Форшмак', Выход = (Выход+30), Труд = NULL
WHERE БЛ = 5;
Обновление множества записей
Утроить цену всех продуктов таблицы поставки (кроме цены кофе - ПР = 17).
UPDATE Поставки
SET Цена = Цена * 3
WHERE ПР <> 17;
Обновление с подзапросом
Установить равной нулю цену и К_во продуктов для поставщиков из Паневежиса и Резекне.
UPDATE Поставки
SET Цена = 0, К_во = 0
WHERE ПС IN
(SELECT ПС
FROM Поставщики
WHERE Город IN ('Паневежис', 'Резекне'));
Обновление нескольких таблиц
Изменить номер продукта ПР = 13 на ПР = 20.
UPDATE Продукты UPDATE Состав
SET ПР = 20 SET ПР = 20
WHERE ПР = 13; WHERE ПР = 13;
UPDATE Поставки UPDATE Наличие
SET ПР = 20 SET ПР = 20
WHERE ПР = 13; WHERE ПР = 13;
К сожалению в единственным запросе невозможно обновить более одной таблицы, а так как код продукта входит в четыре таблицы, то пришлось выдать четыре сходных запроса. Это может привести к противоречию базы данных (нарушению целостности по ссылкам), поскольку после выполнения первого предложения таблицы Состав, Поставки и Наличие ссылаются на уже несуществующий продукт. База становится непротиворечивой только после выполнения четвертого запроса.
4.5. О конструировании предложений модификации
Для тех, кто достаточно хорошо понял предложение SELECT, несложно овладеть конструированием предложений DELETE, INSERT и UPDATE. Но в процессе такого конструирования следует учитывать, что:
1. Если в WHERE фразе предложений DELETE и UPDATE используется вложенный подзапрос, то во фразе FROM этого подзапроса не должна упоминаться таблица, из которой удаляются (в которой обновляются) строки. Аналогично, в подзапросе предложения INSERT не должна упоминаться таблица, в которую загружаются данные.
Так, SQL отвергнет предложение
INSERT
INTO Выбрано
SELECT (33), Т, БЛ
FROM Выбрано
WHERE СМ = 17;
позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м месте. Ввод придется осуществить через какую-либо промежуточную таблицу, например, таблицу Выбор:
DELETE
FROM Выбор;
INSERT
INTO Выбор (СМ, Т, БЛ)
SELECT (33), Т, БЛ
FROM Выбрано
WHERE СМ = 17;
INSERT
INTO Выбрано
SELECT СМ, Т, БЛ
FROM Выбор;
2. Составляя предложения модификации данных, необходимо все время помнить о сохранении непротиворечивости базы данных. Об этом упоминалось ранее и подробно говорилось в литературе [2].
Глава 5. О предложениях определения данных и оптимизации запросов
Системный каталог
Системный каталог - это набор таблиц, в которых содержится информация, необходимая для правильного функционирования СУБД: о поддерживаемых базах данных и их базовых таблицах, представлениях, курсорах, индексах, пользователях и их правах доступа к информации, правилах модификации данных и т.д. В разных СУБД, поддерживающих SQL, существует от десятка до нескольких десятков системных таблиц, структура которых ничем не отличается от уже знакомой нам структуры пользовательских таблиц.
Так, в каждой строке системной таблицы SYSTABLES хранится описание одной из таблиц пользовательских или системной баз данных. Для каждой из них указывается имя таблицы, имя пользователя, который создал эту таблицу, число столбцов в ней и ряд других элементов информации. В таблице SYSCOLUMNS содержится строка для каждого столбца каждой таблицы, в которой указано имя столбца, имя таблицы, частью которой является данный столбец, тип данных для этого столбца и много другой информации о столбце.
С помощью предложения SELECT пользователь может получить информацию из любой системной таблицы. Например, он может дать запрос на получение имен таблиц, числа их столбцов и строк, владельца и краткого описания (если таковое вводилось в базу данных):
SELRCT Tab_name,N_col,N_row,Tab_owner,Comments
FROM SYSTABLES;
и получить результат, показанный на рис. 5.1,а.
Для получения же некоторых данных о столбцах таблицы Блюда можно дать запрос
SELECT Col_name, Type, Length, Comments
FROM SYSCOLUMNS
WHERE Tab_name = 'Блюда';
и получить результат, показанный на рис. 5.1,б.
а)
| Tab_name | N_col | N_row | Tab_owner | Comments |
| ... | ||||
| SYS_TABLES | SYSTEM | |||
| SYS_COLUMNS | SYSTEM | |||
| ... | ||||
| Блюда | KIRILLOW | Перечень блюд, известных шеф-повару | ||
| Поставки | GROMOW | Данные о поставляемых продуктах | ||
| Вид_блюд | KIRILLOW | Перечень видов блюд | ||
| Трапезы | GROMOW | Перечень трапез в пансионате | ||
| Состав | KIRILLOW | Состав блюд | ||
| Продукты | KIRILLOW | Таблица продуктов | ||
| ... |
б)
| Col_name | Type | Length | Comments |
| БЛ | INTEGER | Код блюда | |
| Блюдо | TEXT | Название блюда | |
| В | TEXT | Код вида блюда (З, С, ...) | |
| Основа | TEXT | Основной продукт в блюде | |
| Выход | REAL | Масса порции готового блюда | |
| Труд | INTEGER | Стоимость приготовления блюда (коп) |
Рис. 5.1. Результаты запросов по системным таблицам
Пользователь, не знакомый со структурой базы данных, может с помощью подобного рода запросов получить информацию о такой структуре. Для этого ему надо владеть языком SQL и немного подумать.
В заключение следует отметить, что СУБД не позволяет обновлять каталог с помощью предложений DELETE, INSERT и UPDATE. Обновление проводится только при создании, модификации или уничтожении таблиц, индексов, правил и т.п. с помощью предложений, рассматриваемых ниже.
5.2. Создание и уничтожение базовых таблиц
Базовые таблицы описываются в SQL с помощью предложения CREATE TABLE (создать таблицу), синтаксис которого имеет небольшие различия в различных СУБД. Однако все они поддерживают следующую минимальную форму:
CREATE TABLE базовая_таблица (столбец тип_данных [NOT NULL]
[,столбец тип_данных [NOT NULL]] ...);
где тип_данных должен принадлежать к одному из типов данных, поддерживаемых СУБД (например, одному из типов данных, перечисленных в п.1.2).
Так, описание таблицы Блюда может быть записано в виде
CREATE TABLE Блюда
( БЛ SMALLINT NOT NULL,
Блюда CHAR (70) NOT NULL,
В CHAR (1),
Основа CHAR (10),
Выход FLOAT,
Труд SMALLINT );
В результате создается пустая базовая таблица Блюда, а в системный каталог помещается строка, описывающая эту таблицу. Отметим, что в профессиональных СУБД имя таблицы дополняется именем пользователя, который издал предложение CREATE TABLE. Если этот пользователь зарегистрирован в системе под именем Kirillov, то в каталоге будет зарегистрирована таблица Kirillov.Блюда и указанный пользователь может обращаться к ней по имени Kirillov.Блюда или по сокращенному имени Блюда, которое использовалось во всех предшествующих примерах и будет использоваться далее.
Конструкция NOT NULL запрещает использование неопределенного значения, т.е. специального значения, которое вводится для представления "неизвестного значения" или "неприменимого значения". Например, строка поставки таблицы Поставки может содержать неопределенное значение в столбце Цена и (или) К_во (извесно, что поставщик поставляет указанный продукт, но на данный момент неизвестна цена этого продукта и (или) объем поставки).
Существующую базовую таблицу можно в любой момент уничтожить с помощью предложения DROP TABLE (уничтожить таблицу):
DROP TABLE базовая_таблица;
по которому удаляется описание таблицы, ее данные, связанные с ней представления и индексы, построенные для столбцов таблицы (см. п. 5.3).
В SQL существует также предложение ALTER TABLE (изменить таблицу), которое позволяет добавить справа к таблице новый столбец, т.е. модифицировать описание табицы. Так как без него "можно жить", а объем книги ограничен, то мы не будем здесь описывать это предложение.
5.3. О индексах и производительности
Для ускорения поиска данных можно создавать индексы. Индекс - это системная таблица, построенная по значениям заданного столбца заданной таблицы. В нем размещается перечень уникальных значений указанного столбца таблицы со ссылками на те ее строки, где встречаются эти значения (структура, похожая на предметный указатель книги). Например, индекс, построенный для столбца Основа таблицы Блюда, будет содержать следующие сведения:
| Значения столбца | Строки, в которых встречается такое значение | |||||||
| Кофе | ||||||||
| Крупа | ||||||||
| Молоко | ||||||||
| Мясо | ||||||||
| Овощи | ||||||||
| Рыба | ||||||||
| Фрукты | ||||||||
| Яйца |
Отметим, что такой индекс уже существовал (в несколько иной форме) в базе данных, хотя это обстоятельство никак не повлияло на текст иллюстрационных предложений SELECT, DELETE, INSERT и UPDATE. SQL намеренно не включает в свои конструкции ссылки на индексы. Решение о том, использовать или не использовать какой-либо индекс при обработке некоторого конкретного запроса принимается не пользователем, а оптимизатором СУБД, который учитывает множество факторов - размер таблиц, тип используемых структур хранения данных, статистическое распределение данных в таблицах и индексах и т.д. Однако чтобы оптимизатор смог использовать индексы, их нужно построить (чтобы выиграть в лотерею нужно, по крайней мере, иметь лотерейный билет).
Естественно, что поиск какого-либо значения путем последовательного перебора неупорядоченных данных будет во много раз медленнее, чем поиск с использованием упорядоченного списка (индекса). Ясно также, что таблицу можно упорядочить лишь по данным одного столбца, тогда как поиск часто приходится осуществлять по данным нескольких столбцов. По нескольким столб-цам производится и соединение таблиц. Поэтому, несмотря на то, что индексы увеличивают объем базы данных, их следует использовать как для отдельных столбцов таблицы, так и для комбинации нескольких ее столбцов (например, для комбинации: Фамилия, Имя, Отчество).
Для построения индекса в SQL существует предложение CREATE INDEX (создать индекс), имеющее формат
CREATE [UNIQUE] INDEX имя_индекса ON базовая_таблица
(столбец [[ASC] | DESC] [, столбец [[ASC] | DESC]] ...);
где UNIQUE (уникальный) указывает, что никаким двум строкам в индексируемой базовой таблице не позволяется принимать одно и то же значение для индексируемого столбца (или комбинации столбцов) в одно и то же время.
Например, индексы для столбцов БЛ и Основа таблицы Блюда создаются с помощью предложений
CREATE UNIQUE INDEX Блюда_БЛ ON Блюда (БЛ);
CREATE INDEX Блюда_Основа ON Блюда (Основа);
а индекс для первичного ключа (столбцы БЛ и ПР) таблицы Состав - с помощью предложения
CREATE UNIQUE INDEX Состав_БЛ_ПР ON Состав (БЛ, ПР);
В больших (более 1000 строк) таблицах поиск индексированных значений выполняется на порядок быстрее, чем поиск неиндексированных, а в очень больших таблицах - на два-три порядка.
Так может быть, если позволяет память, следует построить индексы для всех столбцов всех таблиц базы данных?
Если база данных не должна модифицироваться, то на этот вопрос можно дать положительный ответ. Однако при удалении или добавлении строки таблицы должны быть перестроены все индексы, построенные для ее столбцов, а при изменении значения индексированного столбца - индекс этого столбца. Когда модифицируется много - несколько сотен (тысяч) строк - и после модификации каждой строки перестраиваются все ее индексы, время модификации может быть на порядок (несколько порядков) больше времени модификации строк с неиндексированными столб-цами. Поэтому перед модификацией множества строк таблицы целесообразно уничтожить индексы ее столбцов, что можно сделать с помощью предложения DROP INDEX (уничтожить индекс), имеющего следующий формат:
DROP INDEX имя_индекса;
Так как индексы могут создаваться или уничтожаться в любое время, то перед выполнением запросов целесообразно строить индексы лишь для тех столбцов, которые используются в WHERE и ORDER BY фразах запроса, а перед модификацией большого числа строк таблиц с индексированными столбцами эти индексы следует уничтожить.
5.4. Представления