Организация Web-доступа к базам данных с использованием SQL-запросов
Использование INSERT…SELECT для построения внешнего соединения
Рассмотренное в естественное соединение двух таблиц не включает тех
строк какой-либо из них, для которых нет соответствующих строк в другой
таблице. Например, если в таблицу Блюда были занесены под номером 34
сведения о Шашлыке, а рецепт его приготовления не был занесен в таблицу
Рецепты, то при загрузке их естественного соединения в таблицу Временная:
CREATE TABLE Временная
( Вид CHAR (8),
Блюдо CHAR (60),
Рецепт CHAR (560));
INSERT
INTO Временная
SELECT Вид, Блюдо, Рецепт
FROM Блюда, Рецепты, Вид_блюд
WHERE Блюда.БЛ = Рецепты.БЛ
AND Блюда.В = Вид_блюд.В;
в ней не окажется строки с Шашлыком (в таблице Рецепты не обнаружен код 34,
и строка с этим кодом исключена из результата).
Следовательно, в некотором смысле можно считать, что при обычном
соединении теряется информация для таких несоответствующих строк. Однако
иногда (как и в приведенном примере) может потребоваться способность
сохранить эту информацию. В этом случае можно воспользоваться так
называемым внешним соединением:
INSERT
INTO Временная
SELECT Вид, Блюдо, Рецепт
FROM Блюда, Рецепты, Вид_блюд
WHERE Блюда.БЛ = Рецепты.БЛ
AND Блюда.В = Вид_блюд.В;
INSERT
INTO Временная
SELECT Вид, Блюдо, «???»
FROM Блюда, Вид_блюд
WHERE Блюда.В = Вид_блюд.В
AND БЛ NOT IN
( SELECT БЛ
FROM Рецепты);
В результате будет создана базовая таблица
|Вид |Блюдо |Рецепт |
|Закуска |Салат летний |Помидоры и яблоки нарезать… |
|Закуска |Салат мясной |Вареное охлажденное мясо, … |
|. . . | | |
|Напиток |Кофе черный |Кофеварку или кастрюлю спо… |
|Напиток |Кофе на молоке |Сварить черный кофе, как … |
|Горячее |Шашлык |??? |
где первые 33 строки соответствуют первому INSERT и представляют собой
проекцию естественного соединения таблиц Блюда и Рецепты по кодам блюд
(БЛ), включающую три столбца. Последняя строка результата соответствует
второму INSERT и сохраняет информацию о блюде Шашлык, рецепт котого пока не
введен в таблицу Рецепты.
Заметим, что для внешнего соединения нужны два отдельных
INSERT…SELECT. Однако тот же результат можно получить и одним
INSERT…SELECT, используя фразу UNION, объединяющую предложения SELECT из
двух INSERT:
INSERT
INTO Временная
SELECT Вид, Блюдо, Рецепт
FROM Блюда, Рецепты, Вид_блюд
WHERE Блюда.БЛ = Рецепты.БЛ
AND Блюда.В = Вид_блюд.В
UNION
SELECT Вид, Блюдо, «???»
FROM Блюда, Вид_блюд
WHERE Блюда.В = Вид_блюд.В
AND БЛ NOT IN
( SELECT БЛ
FROM Рецепты);
Предложение UPDATE
Обновление единственной записи
Изменить название блюда с кодом БЛ=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;
К сожалению в единственным запросе невозможно обновить более одной
таблицы, а так как код продукта входит в четыре таблицы, то пришлось выдать
четыре сходных запроса. Это может привести к противоречию базы данных
(нарушению целостности по ссылкам), поскольку после выполнения первого
предложения таблицы Состав, Поставки и Наличие ссылаются на уже
несуществующий продукт. База становится непротиворечивой только после
выполнения четвертого запроса.
О конструировании предложений модификации
Для тех, кто достаточно хорошо понял предложение 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. Составляя предложения модификации данных, необходимо все время помнить
о сохранении непротиворечивости базы данных. Об этом упоминалось ранее
и подробно говорилось в литературе.
Предложение DELETE
Удаление единственной записи
Удалить поставщика с ПС = 7.
DELETE
FROM Поставщики
WHERE ПС = 7;
Если таблица Поставки содержит в момент выполнения этого предложения
какие-либо поставки для поставщика с ПС = 7, то такое удаление нарушит
непротиворечивость базы данных. К сожалению нет операции удаления,
одновременно воздействующей на несколько таблиц. Однако в некоторых СУБД
реализованы механизмы поддержания целостности, позволяющие отменить
некорректное удаление или каскадировать удаление на несколько таблиц.
Удаление множества записей
Удалить все поставки.
DELETE
FROM Поставки;
Поставки – все еще известная таблица, но в ней теперь нет строк. Для
уничтожения таблицы надо выполнить операцию DROP TABLE Поставки.
Удалить все мясные блюда.
DELETE FROM Блюда
WHERE Основа = 'Мясо';
Удаление с вложенным подзапросом
Удалить все поставки для поставщика из Паневежиса.
DELETE
FROM Поставки
WHERE ПС IN
(SELECT ПС
FROM Поставщики
WHERE Город = 'Паневежис');
Предложение 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. Отсутствие любого из
этих значений породит противоречие: в базе появится ссылка на
несуществующую запись. Проблемы, возникающие при использовании внешних
ключей, подробно рассмотрены в литературе, а здесь отме-тим, что все
«приличные» СУБД имеют механизмы для предотв-ращения ввода записей со
значениями внешних ключей, отсутст-вующих среди значений соответствующих
первичных ключей.
Предложение UPDATE
Обновление единственной записи
Изменить название блюда с кодом БЛ=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;
К сожалению в единственным запросе невозможно обновить более одной
таблицы, а так как код продукта входит в четыре таблицы, то пришлось выдать
четыре сходных запроса. Это может привести к противоречию базы данных
(нарушению целостности по ссылкам), поскольку после выполнения первого
предложения таблицы Состав, Поставки и Наличие ссылаются на уже
несуществующий продукт. База становится непротиворечивой только после
выполнения четвертого запроса.
О конструировании предложений модификации
Для тех, кто достаточно хорошо понял предложение 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.3. Обзор основных SQL-серверов.
2.3.1. SQL-сервер Oracle.
Общая характеристика продуктов Oraсle
Все продукты Oracle (СУБД, средства разработки, средства для конечного
пользователя, сетевые компоненты) являются открытыми, масштабируемыми и
программируемыми. Они позволяют разрабатывать приложения, как уровня
небольшой рабочей группы, так и уровня огромного предприятия с тысячами
пользователей, террабайтными базами, размещенными в различных зданиях и
даже странах.
Средства Oracle позволяют надежно защитить эти данные, обеспечить их
целостность и непротиворечивость. Продукты Oracle работают более чем на ста
вычислительных платформах (компьютер + операционная система), поддерживают
все основные промышленные сетевые протоколы и графические оконные среды.
Это позволяет с минимальными затратами переносить готовые приложения с
одной платформы на другую. Например, разработав приложение на
однопроцессорном персональном компьютере с MS Windows, Вы можете далее
выполнять его на Unix – машинах, больших IBM машинах, SMP и MPP
архитектурах, высоко надежных и кластерных архитектурах.
При повышении нагрузки на приложение, можно заменить сервер на более
мощный, добавить еще один сервер, вынести часть обработки в другой узел и
т. д. Если клиент работал через терминал, а затем решил перейти к
архитектуре клиент – сервер или даже переместился в другой город, он сможет
продолжать работать с теми же БД Oracle. Все это не потребует модификации
кода приложений.
С помощью средств Oracle можно реализовать оперативную обработку (OLTP –
системы), системы поддержки принятия решений (DSS – системы) и системы
накопления и анализа больших объемов данных (Data Warehouse и OLAP –
системы). Oracle поддерживает все основные стандарты:
FIPS 127-2, ANSI X3-135.1992 – для БД;
NCSC TDI C2, B1, ITSEC F – C2/E3, F – B1/B3 – по защите данных;
OSI, DNSIX (MaxSix), SNMP – для сети;
ODBC, TSIG, X/Open, DCE, DDE, OLE, OCX, VBX – для взаимодействия
приложений.
Классификация продуктов Oracle
Все многообразие продуктов фирмы Oracle можно разделить на следующие
группы:
Oracle7 Server – ядро СУБД и дополнительные компоненты ядра (опции). Они
необходимы для хранения, поиска, извлечения, обработки и администрирования
данных;
инструментальные средства разработки приложений. Это, в первую очередь,
набор средств разработчика Developer/2000, а также прекомпиляторы с языков
3GL и библиотека CALL-интерфейса;
средства автоматизации проектирования и разработки (CASE-средства) –
Designer/2000;
средства для конечных пользователей. Это набор средств Descoverer/2000,
офисная система Oracle Office, средства хранения и обработки текстов Text
Server (c Context и CoAutor);
средства для анализа данных и создания OLAP (online analyse processing)
приложений – Express – продукты;
средства для обеспечения работы продуктов Oracle в компьютерной сети. Это
SQL*Net с драйверами различных сетевых протоколов, средства управления
сетью, кодирования данных, преобразования протоколов;
средства для взаимодействия с пакетами других фирм. Это шлюзы по данным
(Transparent Gateway) к различным СУБД и процедурные шлюзы; ODBC драйвер,
Oracle Objects for OLE, универсальный пакет связи Oracle Glue;
продукты для рабочих групп – Workgroup/2000. К этой группе относится
нерасширяемое ядро Oracle для персональных компьютеров,
однопользовательский персональный Oracle, средства разработки небольших
приложений-Oracle Power Objects. Продукты для рабочих групп отличаются
компактностью, простотой установки и использования, а так же низкими
ценами;
готовые прикладные системы – Oracle Applications. Среди них наиболее
известными являются: Oracle Financial – финансовые, Oracle Manufacturing –
управление производством, Oracle Human Resources – кадры, бухгалтерия;
новые направления. К этой группе можно отнести продукты для работы с
мультимедиа (Media Server, Media Net, Media Objects), средства для работы с
БД по медленным и ненадежным сетям (радиомодемы, телефоны, сотовая связь) –
Oracle Mobile Agents, средства для работы с БД по Internet (WWW Viewer и
WWW сервер).
Oracle7 Server
Универсальный сервер Oracle позволяет хранить и обрабатывать самые
разные типы данных. Кроме привычных структурированных данных (числа,
Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
|