Программирование. Лекция №4. Операции обновления.

Предыдущая лекция | Содержание | Следующая лекция
Программирование. Лекция №4. Операции обновления.

Введение.

Обратим внимание на предложения обновления данных UPDATE (обновить), DELETE (удалить) и INSERT (вставить).

Предложение UPDATE.

Предложение UPDATE имеет следующий общий формат:

	UPDATE таблица
	SET       поле = выражение
		[,поле = выражение] ... 
	[WHERE предикат];

Все записи в "таблице", которые удовлетворяют "предикату", обновляются в соответствии с присваиваниями "поле = выражение" во фразе SET (установить).

ОБНОВЛЕНИЕ ЕДИНСТВЕННОЙ ЗАПИСИ

Изменить фамилию поставщика 2 на Сандерс и город на 'Берлин':

	UPDATE    Поставщики
	SET	     Фамилия = 'Сандерс', Город = 'Берлин'
	WHERE      Номер_Поставщика = 2;

Для каждой записи, которая должна быть обновлена (т. е. для каждой записи, которая удовлетворяет предикату WHERE, или для всех записей, если фраза WHERE опущена), ссылки во фразе SET на поля этой записи обозначают значения этих полей перед тем, как будет выполнено какое-либо присваивание в этой фразе SET.

ОБНОВЛЕНИЕ МНОЖЕСТВА ЗАПИСЕЙ

Удвоить вес всех деталей, производимых в Лондоне.

	UPDATE Детали
	SET Вес = 2*Вес
	WHERE Город = 'Лондон';

ОБНОВЛЕНИЕ С ПОДЗАПРОСОМ

Установить вес деталей равным 10 для всех поставщиков из Лондона.

	UPDATE Детали
	SET Вес = 10
	WHERE 'Лондон' =
		(SELECT Город
		FROM Поставщики 
		WHERE Поставщики.НОМЕР_ПОСТАВЩИКА = Детали.НОМЕР_ПОСТАВЩИКА);

ОБНОВЛЕНИЕ НЕСКОЛЬКИХ ТАБЛИЦ

Невозможно обновить более одной таблицы в единственном запросе. Иными словами, в предложении UPDATE должна специфицироваться в точности одна таблица. Поэтому в данном примере мы сталкиваемся со следующей проблемой целостности (точнее, с проблемой целостности по ссылкам): база данных становится противоречивой после выполнения первого предложения UPDATE - она включает теперь некоторые поставки, для которых не имеется соответствующей записи о поставщике, и остается в таком состоянии до тех пор, пока не будет выполнено второе предложение UPDATE. Изменение порядка предложений UPDATE, конечно, не решает эту проблему. Поэтому важно обеспечить выполнение обоих этих предложений, а не только одного.

ПРЕДЛОЖЕНИЕ COMMIT ИЛИ ROLLBACK

Необходимо сначала дать определение транзакции.
Транзакция - это неразрывная последовательность действий, составленная из одного или нескольких предложений SQL. Любая транзакция оканчивается предложением COMMIT или ROLLBACK.

Когда какие-либо предложения SQL обновляют базу данных, такое обновление следует рассматривать, лишь как предварительное, в том смысле, что если в дальнейшем что-то выполнится с ошибкой, то это предложение может быть аннулировано самой программой или системой. Обновления остаются предварительными до тех пор, пока не произойдет одно из двух:

исполнится предложение COMMIT (фиксировать), которая все предварительные обновления сделает окончательными (зафиксирует);
исполнится предложение ROLLBACK (откат), которая аннулирует сделанные вами изменения в базе данных.

	UPDATE Детали 
	SET ЦВЕТ = 'Желтый'
	WHERE НОМЕР_Поставщика = 3;
	COMMIT

Предложение DELETE.

Предложение DELETE имеет следующий общий формат:

	DELETE
	FROM таблица
	[WHERE Условие];

Удаляются все записи в "таблице", которые удовлетворяют "условию".

УДАЛЕНИЕ ЕДИНСТВЕННОЙ ЗАПИСИ

Удалить поставщика с фамилией Адамс:

	DELETE
	FROM Поставщики
	WHERE Фамилия = 'Адамс';

И снова, если таблица Детали в настоящее время содержит какие-либо поставки для поставщика Адамса, это удаление нарушит непротиворечивость базы данных; как и в случае предложения UPDATE, нет операций DELETE, воздействующих на несколько таблиц.

УДАЛЕНИЕ МНОЖЕСТВА ЗАПИСЕЙ

Удалить всех поставщиков из Лондона.

	DELETE
	FROM Поставщики
	WHERE Город = 'Лондон';

Поставщики - все еще известная таблица, но она теперь пуста. Удалить все записи - это не уничтожить таблицу (операция DROP).

УДАЛЕНИЕ С ПОДЗАПРОСОМ

Удалить все детали из Лондона для поставщиков.

	DELETE
	FROM Детали
	WHERE 'Лондон' =
	(SELECT Город 
	FROM Поставщики
	WHERE Поставщики.Номер_Поставщика = Детали.Номер_Поставщика);

Предложение INSERT.

Предложение INSERT имеет следующий общий формат:

	INSERT
	INTO таблица [(поле [,поле] . . .)]
	VALUES (константа [,константа] ...);
или
	INSERT
	INTO таблица [(поле [,поле] . . .)]
	подзапрос;

В первом формате в "таблицу" вставляется строка, имеющая заданные значения для указанных полей, причем i-я константа в списке констант соответствует i-му полю в списке полей. Во втором формате вычисляется "подзапрос"; копия результата, представляющего собой, вообще говоря, множество строк, вставляется в "таблицу". При этом i-й столбец этого результата соответствует i-му полю в списке полей. В обоих случаях отсутствие списка полей эквивалентно спецификации списка всех полей в таблице.

ВСТАВКА ЕДИНСТВЕННОЙ ЗАПИСИ

	INSERT
	INTO Поставщики (Номер_Поставщика, Фамилия, Город)
	VALUES (1, 'Смит', 'Лондон');

	INSERT
	INTO Поставщики (Номер_Поставщика, Фамилия, Город)
	VALUES (2, 'Джонс', 'Париж');

	INSERT
	INTO Поставщики (Номер_Поставщика, Фамилия, Город)
	VALUES (3, 'Блейк', ' Париж');

Создается новая запись для поставщика с заданным номером, городом и фамилией.

ВСТАВКА ЕДИНСТВЕННОЙ ЗАПИСИ С ОПУЩЕННЫМИ ИМЕНАМИ ПОЛЕЙ

	INSERT
	INTO Поставщики
	VALUES (4, 'Кларк', 'Лондон');

	INSERT
	INTO Поставщики 
	VALUES (5, 'Адамс', 'Атенс');

Отсутствие списка полей эквивалентно спецификации списка всех полей в таблице в порядке слева - направо, как они были определены в предложении CREATE (или ALTER). Как и "SELECT *", такая краткая нотация может быть удобной для интерактивного SQL. Она потенциально опасна, однако, во встроенном SQL, т. е. в предложениях SQL, используемых в прикладной программе, в связи с тем, что предполагаемый список полей может изменяться, если для программы заново осуществляется связывание, а определение таблицы было в этом промежутке времени изменено.

Заключение.

Мы подошли к концу детального обсуждения четырех предложений манипулирования данными - SELECT, UPDATE, DELETE и INSERT. Сложности этих предложений (и достаточно серьезные!) связаны, по большей части, с предложением SELECT. Как Вы могли убедиться, после того как достигнуто достаточное понимание предложения SELECT, другие предложения становятся довольно понятными. Конечно, на практике предложение SELECT обычно бывает также довольно простым. Однако, вопреки сказанному выше, с операциями обновления также связаны две проблемы, которые заслуживают, чтобы о них здесь упомянуть.
Первой и более важной является проблема целостности по ссылкам, которую мы уже несколько раз затрагивали.
Вторая проблема состоит в том, что более сложным формам трех предложений обновления свойственно следующее небольшое ограничение. Если фраза WHERE в предложении UPDATE или DELETE включает подзапрос, то во фразе FROM этого подзапроса не должна упоминаться целевая таблица этого UPDATE или DELETE. Аналогичным образом в форме предложения INSERT с подзапросом во фразе FROM в подзапросе не должна упоминаться таблица, которая является целевой для этого предложения INSERT. Так, например, если нужно удалить всех поставщиков, состояние которых меньше среднего, то следующий запрос не будет работать.

	DELETE 
	FROM Детали 
	WHERE Вес <
		(SELECT AVG (Вес)
		FROM Детали);

Вместо этого нужно последовательно выполнить следующие шаги:

	SELECT AVG (Вес) 
	FROM Детали;

Результат: 15,8
В соответствии с этим:

	DELETE
	FROM Детали
	WHERE Вес < 15,8;

Для указанных ограничений нет каких-либо внутренне присущих причин.
В заключение укажем, что наличие в языке SQL лишь четырех операций манипулирования данными - одна из причин легкости использования этого языка. Поскольку имеется только один способ представления всех данных, необходима лишь одна операция для каждой из четырех базисных функций (выбрать, изменить, вставить, удалить). В противоположность этому в системах, основанных на более сложных структурах данных, теоретически требуется 4n операций, где n - число способов представления данных в такой системе. Например, в системах, основанных на концепциях КОДАСИЛ, где данные могут быть представлены либо как записи, либо как связи между записями, можно обычно обнаружить операцию STORE (запомнить) для создания записи и операцию CONNECT (включить) для создания связи, операцию ERASE (стереть) для уничтожения записи и операцию DISCONNECT (исключить) для уничтожения связи, операцию MODIFY (модифицировать) для изменения записи и операцию RECONNECT (переключить) для изменения связи и т. д. На самом деле системы типа КОДАСИЛ обычно предусматривают более двух способов представления данных, но записи и связи - наиболее важные из них.