Организация Web-доступа к базам данных с использованием SQL-запросов
синтаксис предложений модификации
Модификация данных может выполняться с помощью предложений DELETE
(удалить), INSERT (вставить) и UPDATE (обновить). Подобно предложению
SELECT они могут оперировать как базовыми таблицами, так и представлениями.
Однако по ряду причин не все представления являются обновляемыми. Пока
зафиксируем этот факт, отложив описание представлений и особенностей их
обновления до главы 5, но будем помнить, что термин «представление»
относится только к обновляемым представлениям.
Предложение DELETE имеет формат
DELETE
FROM базовая таблица | представление
[WHERE фраза];
и позволяет удалить содержимое всех строк указанной таблицы (при отсутствии
WHERE фразы) или тех ее строк, которые выделяются WHERE фразой.
Предложение INSERT имеет один из следующих форматов:
INSERT
INTO {базовая таблица | представление} [(столбец [,столбец] …)]
VALUES ({константа | переменная} [,{константа | переменная}] …);
или
INSERT
INTO {базовая таблица | представление} [(столбец [,столбец] …)]
подзапрос;
В первом формате в таблицу вставляется строка со значениями полей,
указанными в перечне фразы VALUES (значения), причем i-е значение
соответствует i-му столбцу в списке столбцов (столбцы, не указанные в
списке, заполняются NULL-значениями). Если в списке VALUES фразы указаны
все столбцы модифицируемой таблицы и порядок их перечисления соответствует
порядку столбцов в описании таблицы, то список столбцов в фразе INTO можно
опустить. Однако не советуем этого делать, так как при изменении описания
таблицы (перестановка столбцов или изменение их числа) придется
переписывать и INSERT предложение.
Во втором формате сначала выполняется подзапрос, т.е. по предложению
SELECT в памяти формируется рабочая таблица, а потом строки рабочей таблицы
загружаются в модифицируемую таблицу. При этом i-й столбец рабочей таблицы
(i-й элемент списка SELECT) соответствует i-му столбцу в списке столбцов
модифицируемой таблицы. Здесь также при выполнении указанных выше условий
может быть опущен список столбцов фразы INTO.
Предложение UPDATE также имеет два формата. Первый из них:
UPDATE (базовая таблица | представление}
SET столбец = значение [, столбец = значение] …
[WHERE фраза]
где значение – это
столбец | выражение | константа | переменная
и может включать столбцы лишь из обновляемой таблицы, т.е. значение одного
из столбцов модифицируемой таблицы может заменяться на значение ее другого
столбца или выражения, содержащего значения нескольких ее столбцов, включая
изменяемый.
При отсутствии WHERE фразы обновляются значения указанных столбцов во
всех строках модифицируемой таблицы. WHERE фраза позволяет сократить число
обновляемых строк, указывая условия их отбора.
Второй формат описывает предложение, позволяющее производить
обновление значений модифицируемой таблицы по значениям столбцов из других
таблиц. К сожалению в ряде СУБД эти форматы отличаются друг от друга и от
стандарта. Для примера приведем один из таких форматов:
UPDATE {базовая таблица | представление}
SET столбец = значение [, столбец = значение] …
FROM {базовая таблица | представление} [псевдоним],
{базовая таблица | представление} [псевдоним]
[,{базовая таблица | представление} [псевдоним]] …
[WHERE фраза]
Здесь перечень таблиц фразы FROM содержит имя модифицируемой таблицы и
тех таблиц, значения столбцов которых используются для обновления. При
этом, естественно, таблицы должны быть связаны между собой в WHERE фразе,
которая, кроме того, служит для указания условий отбора обновляемых строк
модифицируемой таблицы.
В значениях, находящихся в правых частях равенств фразы SET, следует
уточнять имена используемых столбцов, предваряя их именем таблицы
(псевдонима).
Предложение INSERT
Вставка единственной записи в таблицу
Добавить в таблицу Блюда блюдо:
Шашлык (БЛ – 34, Блюдо – Шашлык, В – Г, Основа – Мясо, Выход – 150)
при неизвестной пока трудоемкости приготовления этого блюда.
INSERT
INTO Блюда (БЛ, Блюдо, В, Основа, Выход)
VALUES (34, 'Шашлык', 'Г', 'Мясо', 150);
Создается новая запись для блюда с номером 34, с неопределенным
значением в столбце Труд.
Порядок полей в INSERT не обязательно должен совпадать с порядком
полей, в котором они определялись при создании таблицы. Вполне допустима и
такая версия предыдущего предложения:
INSERT
INTO Блюда (Основа, В, Блюдо, БЛ, Выход)
VALUES ('Мясо', 'Г', 'Шашлык', 34, 150);
При известной трудоемкости приготовления шашлыка (например, 5 коп)
сведения о нем можно ввести с помощью укороченного предложения:
INSERT
INTO Блюда
VALUES (34, 'Шашлык', 'Г', 'Мясо', 150, 5);
в котором должен соблюдаться строгий порядок перечисления вводимых
значений, так как, не имея перечня загружаемых столб-цов, СУБД может
использовать лишь перечень, который определен при создании модифицируемой
таблицы.
В предыдущих примерах проводилась модификация стержневой сущности,
т.е. таблицы с первичным ключом БЛ. Почти все СУБД имеют механизмы для
предотвращения ввода не уникального первичного ключа, например, ввода
«Шашлыка» под номером, меньшим 34. А как быть с ассоциациями или другими
таблицами, содержащими внешние ключи?
Пусть, например, потребовалось добавить в рецепт блюда Салат летний
(БЛ = 1) немного (15 г) лука (ПР = 10), и мы воспользовались предложением
INSERT
INTO Состав (БЛ, ПР, Вес)
VALUES (1, 10, 15);
Подобно операции DELETE операция INSERT может нарушить
непротиворечивость базы данных. Если не принять специальных мер, то СУБД не
проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в
таблице Продукты – продукт с первичным ключом ПР = 10. Отсутствие любого из
этих значений породит противоречие: в базе появится ссылка на
несуществующую запись. Проблемы, возникающие при использовании внешних
ключей, подробно рассмотрены в литературе, а здесь отме-тим, что все
«приличные» СУБД имеют механизмы для предотв-ращения ввода записей со
значениями внешних ключей, отсутст-вующих среди значений соответствующих
первичных ключей.
Вставка множества записей
Создать временную таблицу К_меню, содержащую калорийность и стоимость
всех блюд, которые можно приготовить из имеющихся продуктов. (Эта таблица
будет использоваться шеф-поваром для составления меню на следующий день.)
Для создания описания временной таблицы можно, например,
воспользоваться предложением CREATE TABLE
CREATE TABLE К_меню
( Вид CHAR (10),
Блюдо CHAR (60),
Калор_блюда INTEGER,
Стоим_блюда REAL);
а для ее загрузки данными – предложение INSERT с вложенным подзапросами:
INSERT
INTO К_меню
SELECT Вид, Блюдо,
INT(SUM(((Белки+Углев)*4.1+Жиры*9.3) * Вес/1000)),
(SUM(Стоимость/К_во*Вес/1000) + MIN(Труд/100))
FROM Блюда, Вид_блюд, Состав, Продукты, Наличие
WHERE Блюда.БЛ = Состав.БЛ
AND Состав.ПР = Продукты.ПР
AND Состав.ПР = Наличие.ПР
AND Блюда.В = Вид_блюд.В
AND БЛ NOT IN
( SELECT БЛ
FROM Состав
WHERE ПР IN
( SELECT ПР
FROM Наличие
WHERE К_во = 0))
GROUP BY Вид, Блюдо
ORDER BY Вид, 3;
В этом запросе предложение SELECT выполняется так же, как обычно, но
результат не выводится на экран, а копируется в таблицу К_меню. Теперь с
этой копией можно работать как с обычной базовой таблицей (Блюда, Про-
дукты,…), т.е. выбирать из нее даннные на экран или принтер, обновлять в
ней данные и т.п. Никакая из этих операций не будет оказывать влияния на
исходные данные (например, изменение в ней названия блюда Салат летний на
Салат весенний не приведет к подобному изменению в таблице Блюда, где
сохранится старое название). Так как это может привести к противоречиям, то
подобные временные таблицы уничтожают после их использования. Поэтому
программа, обслуживающая шеф-повара, должна исполнять предложение DROP
TABLE К_меню после того, как будет закончено составление меню.
Использование INSERT…SELECT для построения внешнего соединения
Рассмотренное в естественное соединение двух таблиц не включает тех
строк какой-либо из них, для которых нет соответствующих строк в другой
таблице. Например, если в таблицу Блюда были занесены под номером 34
сведения о Шашлыке, а рецепт его приготовления не был занесен в таблицу
Рецепты, то при загрузке их естественного соединения в таблицу Временная:
CREATE TABLE Временная
( Вид CHAR (8),
Блюдо CHAR (60),
Рецепт CHAR (560));
INSERT
INTO Временная
SELECT Вид, Блюдо, Рецепт
FROM Блюда, Рецепты, Вид_блюд
WHERE Блюда.БЛ = Рецепты.БЛ
AND Блюда.В = Вид_блюд.В;
в ней не окажется строки с Шашлыком (в таблице Рецепты не обнаружен код 34,
и строка с этим кодом исключена из результата).
Следовательно, в некотором смысле можно считать, что при обычном
соединении теряется информация для таких несоответствующих строк. Однако
иногда (как и в приведенном примере) может потребоваться способность
сохранить эту информацию. В этом случае можно воспользоваться так
называемым внешним соединением:
INSERT
IN
| |  скачать работу |
Организация Web-доступа к базам данных с использованием SQL-запросов |
