Организация 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