Sqlо конструировании предложений модификации


Содержание

Структуризированный язык запросов (sql)

Название Структуризированный язык запросов (sql)
страница 8/10
Дата конвертации 28.08.2012
Размер 1.57 Mb.
Тип Учебное пособие
1. /Структуризированный язык запросов.doc Структуризированный язык запросов (sql)

Глава 4. Предложения модификации данных SQL

4.1. Особенности и синтаксис предложений модификации

Модификация данных может выполняться с помощью предложений DELETE (удалить), INSERT (вставить) и UPDATE (обновить). Подобно предложению SELECT они могут оперировать как базовыми таблицами, так и представлениями. Однако по ряду причин не все представления являются обновляемыми. Пока зафиксируем этот факт, отложив описание представлений и особенностей их обновления до главы 5, но будем помнить, что термин «представление» относится только к обновляемым представлениям.

Предложение DELETE имеет формат

FROM базовая таблица | представление

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

Предложение INSERT имеет один из следующих форматов:

В первом формате в таблицу вставляется строка со значениями полей, указанными в перечне фразы VALUES (значения), причем i-е значение соответствует i-му столбцу в списке столбцов (столбцы, не указанные в списке, заполняются NULL-значениями). Если в списке VALUES фразы указаны все столбцы модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы, то список столбцов в фразе INTO можно опустить. Однако не советуем этого делать, так как при изменении описания таблицы (перестановка столбцов или изменение их числа) придется переписывать и INSERT предложение.

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

Предложение UPDATE также имеет два формата. Первый из них:

UPDATE (базовая таблица | представление>

SET столбец = значение [, столбец = значение] .

где значение — это

столбец | выражение | константа | переменная

и может включать столбцы лишь из обновляемой таблицы, т.е.

значение одного из столбцов модифицируемой таблицы может заменяться на значение ее другого столбца или выражения, содержащего значения нескольких ее столбцов, включая изменяемый.

При отсутствии WHERE фразы обновляются значения указанных столбцов во всех строках модифицируемой таблицы. WHERE фраза позволяет сократить число обновляемых строк, указывая условия их отбора.

Второй формат описывает предложение, позволяющее производить обновление значений модифицируемой таблицы по значениям столбцов из других таблиц. К сожалению в ряде СУБД эти форматы отличаются друг от друга и от стандарта. Для примера приведем один из таких форматов:

SET столбец = значение [, столбец = значение] .

Здесь перечень таблиц фразы FROM содержит имя модифицируемой таблицы и тех таблиц, значения столбцов которых используются для обновления. При этом, естественно, таблицы должны быть связаны между собой в WHERE фразе (см.п.3.2.3), которая, кроме того, служит для указания условий отбора обновляемых строк модифицируемой таблицы.

В значениях, находящихся в правых частях равенств фразы SET, следует уточнять имена используемых столбцов, предваряя их именем таблицы (псевдонима).

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

4.2.1. Удаление единственной записи

Удалить поставщика с ПС = 7.

Если таблица Поставки содержит в момент выполнения этого предложения какие-либо поставки для поставщика с ПС = 7, то такое удаление нарушит непротиворечивость базы данных. К сожалению нет операции удаления, одновременно воздействующей на несколько таблиц. Однако в некоторых СУБД реализованы механизмы поддержания целостности (см.п.2.5 в литературе [2]), позволяющие отменить некорректное удаление или каскадировать удаление на несколько таблиц.

4.2.2. Удаление множества записей

Удалить все поставки.

Поставки — все еще известная таблица, но в ней теперь нет строк. Для уничтожения таблицы надо выполнить операцию DROP TABLE Поставки (см.п.5.2).

Удалить все мясные блюда.

DELETE FROM Блюда

WHERE Основа = ‘Мясо’;

4.2.3. Удаление с вложенным подзапросом

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

WHERE Город = ‘Паневежис’);

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

4.3.1. Вставка единственной записи в таблицу

Добавить в таблицу Блюда блюдо:

Шашлык (БЛ — 34, Блюдо — Шашлык, В — Г, Основа — Мясо, Выход — 150)

при неизвестной пока трудоемкости приготовления этого блюда.

INTO Блюда (БЛ, Блюдо, В, Основа, Выход)

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150);

Создается новая запись для блюда с номером 34, с неопределенным значением в столбце Труд.

Порядок полей в INSERT не обязательно должен совпадать с порядком полей, в котором они определялись при создании таблицы. Вполне допустима и такая версия предыдущего предложения:

INTO Блюда (Основа, В, Блюдо, БЛ, Выход)

VALUES (‘Мясо’, ‘Г’, ‘Шашлык’, 34, 150);

При известной трудоемкости приготовления шашлыка (например, 5 коп) сведения о нем можно ввести с помощью укороченного предложения:

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150, 5);

в котором должен соблюдаться строгий порядок перечисления вводимых значений, так как, не имея перечня загружаемых столб-цов, СУБД может использовать лишь перечень, который определен при создании модифицируемой таблицы.

В предыдущих примерах проводилась модификация стержневой сущности, т.е. таблицы с первичным ключом БЛ (см.п.2.4 в литературе [2]). Почти все СУБД имеют механизмы для предотвращения ввода не уникального первичного ключа, например, ввода «Шашлыка» под номером, меньшим 34. А как быть с ассоциациями или другими таблицами, содержащими внешние ключи?

Пусть, например, потребовалось добавить в рецепт блюда Салат летний (БЛ = 1) немного (15 г) лука (ПР = 10), и мы воспользовались предложением

INTO Состав (БЛ, ПР, Вес)

Подобно операции DELETE операция INSERT может нарушить непротиворечивость базы данных. Если не принять специальных мер, то СУБД не проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в таблице Продукты — продукт с первичным ключом ПР = 10. Отсутствие любого из этих значений породит противоречие: в базе появится ссылка на несуществующую запись. Проблемы, возникающие при использовании внешних ключей, подробно рассмотрены в литературе [2], а здесь отме-тим, что все «приличные» СУБД имеют механизмы для предотв-ращения ввода записей со значениями внешних ключей, отсутст-вующих среди значений соответствующих первичных ключей.

4.3.2. Вставка множества записей

Создать временную таблицу К_меню, содержащую калорийность и стоимость всех блюд, которые можно приготовить из имеющихся продуктов. (Эта таблица будет использоваться шеф-поваром для составления меню на следующий день.)

Для создания описания временной таблицы можно, например, воспользоваться предложением CREATE TABLE (см.п.5.2)

CREATE TABLE К_меню

а для ее загрузки данными – предложение INSERT с вложенным подзапросами:

SELECT Вид, Блюдо,

FROM Блюда, Вид_блюд, Состав, Продукты, Наличие

WHERE Блюда.БЛ = Состав.БЛ

AND Состав.ПР = Продукты.ПР

AND Состав.ПР = Наличие.ПР

AND Блюда.В = Вид_блюд.В

GROUP BY Вид, Блюдо

В этом запросе предложение SELECT выполняется так же, как обычно (см. описание запроса в п.3.6), но результат не выводится на экран, а копируется в таблицу К_меню. Теперь с этой копией можно работать как с обычной базовой таблицей (Блюда, Про-дукты. ), т.е. выбирать из нее даннные на экран или принтер, обновлять в ней данные и т.п. Никакая из этих операций не будет оказывать влияния на исходные данные (например, изменение в ней названия блюда Салат летний на Салат весенний не приведет к подобному изменению в таблице Блюда, где сохранится старое название). Так как это может привести к противоречиям, то подобные временные таблицы уничтожают после их использования. Поэтому программа, обслуживающая шеф-повара, должна исполнять предложение DROP TABLE К_меню после того, как будет закончено составление меню.

4.3.3. Использование INSERT. SELECT для построения внешнего соединения

Рассмотренное в п.3.2.3 естественное соединение двух таблиц не включает тех строк какой-либо из них, для которых нет соответствующих строк в другой таблице. Например, если в таблицу Блюда были занесены под номером 34 сведения о Шашлыке, а рецепт его приготовления не был занесен в таблицу Рецепты, то при загрузке их естественного соединения в таблицу Временная:

CREATE TABLE Временная

Рецепт CHAR (560));

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

в ней не окажется строки с Шашлыком (в таблице Рецепты не обнаружен код 34, и строка с этим кодом исключена из результата).

Следовательно, в некотором смысле можно считать, что при обычном соединении теряется информация для таких несоответствующих строк. Однако иногда (как и в приведенном примере) может потребоваться способность сохранить эту информацию. В этом случае можно воспользоваться так называемым внешним соединением:

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

SELECT Вид, Блюдо, “. ”

FROM Блюда, Вид_блюд

WHERE Блюда.В = Вид_блюд.В

В результате будет создана базовая таблица

Вид Блюдо Рецепт
Закуска Салат летний Помидоры и яблоки нарезать.
Закуска Салат мясной Вареное охлажденное мясо, .
. . .
Напиток Кофе черный Кофеварку или кастрюлю спо.
Напиток Кофе на молоке Сварить черный кофе, как .
Горячее Шашлык .

где первые 33 строки соответствуют первому INSERT и представляют собой проекцию естественного соединения таблиц Блюда и Рецепты по кодам блюд (БЛ), включающую три столбца. Последняя строка результата соответствует второму INSERT и сохраняет информацию о блюде Шашлык, рецепт котого пока не введен в таблицу Рецепты.

Заметим, что для внешнего соединения нужны два отдельных INSERT. SELECT. Однако тот же результат можно получить и одним INSERT. SELECT, используя фразу UNION, объединяющую предложения SELECT из двух INSERT:

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В

SELECT Вид, Блюдо, “. ”

FROM Блюда, Вид_блюд

WHERE Блюда.В = Вид_блюд.В

4.5. О конструировании предложений модификации

Для тех, кто достаточно хорошо понял предложение SELECT, несложно овладеть конструированием предложений DELETE, INSERT и UPDATE. Но в процессе такого конструирования следует учитывать, что:

  1. Если в WHERE фразе предложений DELETE и UPDATE используется вложенный подзапрос, то во фразе FROM этого подзапроса не должна упоминаться таблица, из которой удаляются (в которой обновляются) строки. Аналогично, в подзапросе предложения INSERT не должна упоминаться таблица, в которую загружаются данные.

Так, SQL отвергнет предложение

позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м месте. Ввод придется осуществить через какую-либо промежуточную таблицу, например, таблицу Выбор:

INTO Выбор (СМ, Т, БЛ)

FROM Выбор;

  1. Составляя предложения модификации данных, необходимо все время помнить о сохранении непротиворечивости базы данных. Об этом упоминалось ранее и подробно говорилось в литературе [2].

Организация Web-доступа к базам данных с использованием SQL-запросов

2.2.4. Модификация данных в таблицах SQL.

Особенности и синтаксис предложений модификации

Модификация данных может выполняться с помощью предложений DELETE (удалить), INSERT (вставить) и UPDATE (обновить). Подобно предложению SELECT они могут оперировать как базовыми таблицами, так и представлениями. Однако по ряду причин не все представления являются обновляемыми. Пока зафиксируем этот факт, отложив описание представлений и особенностей их обновления до главы 5, но будем помнить, что термин «представление» относится только к обновляемым представлениям.

Предложение DELETE имеет формат

FROM базовая таблица | представление

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

Предложение INSERT имеет один из следующих форматов:

В первом формате в таблицу вставляется строка со значениями полей, указанными в перечне фразы VALUES (значения), причем i-е значение соответствует i-му столбцу в списке столбцов (столбцы, не указанные в списке, заполняются NULL-значениями). Если в списке VALUES фразы указаны все столбцы модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы, то список столбцов в фразе INTO можно опустить. Однако не советуем этого делать, так как при изменении описания таблицы (перестановка столбцов или изменение их числа) придется переписывать и INSERT предложение.

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

Предложение UPDATE также имеет два формата. Первый из них:

UPDATE (базовая таблица | представление>


SET столбец = значение [, столбец = значение] …

где значение – это

столбец | выражение | константа | переменная

и может включать столбцы лишь из обновляемой таблицы, т.е. значение одного из столбцов модифицируемой таблицы может заменяться на значение ее другого столбца или выражения, содержащего значения нескольких ее столбцов, включая изменяемый.

При отсутствии WHERE фразы обновляются значения указанных столбцов во всех строках модифицируемой таблицы. WHERE фраза позволяет сократить число обновляемых строк, указывая условия их отбора.

Второй формат описывает предложение, позволяющее производить обновление значений модифицируемой таблицы по значениям столбцов из других таблиц. К сожалению в ряде СУБД эти форматы отличаются друг от друга и от стандарта. Для примера приведем один из таких форматов:

SET столбец = значение [, столбец = значение] …

Здесь перечень таблиц фразы FROM содержит имя модифицируемой таблицы и тех таблиц, значения столбцов которых используются для обновления. При этом, естественно, таблицы должны быть связаны между собой в WHERE фразе, которая, кроме того, служит для указания условий отбора обновляемых строк модифицируемой таблицы.

В значениях, находящихся в правых частях равенств фразы SET, следует уточнять имена используемых столбцов, предваряя их именем таблицы (псевдонима).

Вставка единственной записи в таблицу

Добавить в таблицу Блюда блюдо:

Шашлык (БЛ – 34, Блюдо – Шашлык, В – Г, Основа – Мясо, Выход – 150)

при неизвестной пока трудоемкости приготовления этого блюда.

INTO Блюда (БЛ, Блюдо, В, Основа, Выход)

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150);

Создается новая запись для блюда с номером 34, с неопределенным значением в столбце Труд.

Порядок полей в INSERT не обязательно должен совпадать с порядком полей, в котором они определялись при создании таблицы. Вполне допустима и такая версия предыдущего предложения:

INTO Блюда (Основа, В, Блюдо, БЛ, Выход)

VALUES (‘Мясо’, ‘Г’, ‘Шашлык’, 34, 150);

При известной трудоемкости приготовления шашлыка (например, 5 коп) сведения о нем можно ввести с помощью укороченного предложения:

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150, 5);

в котором должен соблюдаться строгий порядок перечисления вводимых значений, так как, не имея перечня загружаемых столб-цов, СУБД может использовать лишь перечень, который определен при создании модифицируемой таблицы.

В предыдущих примерах проводилась модификация стержневой сущности, т.е. таблицы с первичным ключом БЛ. Почти все СУБД имеют механизмы для предотвращения ввода не уникального первичного ключа, например, ввода «Шашлыка» под номером, меньшим 34. А как быть с ассоциациями или другими таблицами, содержащими внешние ключи?

Пусть, например, потребовалось добавить в рецепт блюда Салат летний (БЛ = 1) немного (15 г) лука (ПР = 10), и мы воспользовались предложением

INTO Состав (БЛ, ПР, Вес)

Подобно операции DELETE операция INSERT может нарушить непротиворечивость базы данных. Если не принять специальных мер, то СУБД не проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в таблице Продукты – продукт с первичным ключом ПР = 10. Отсутствие любого из этих значений породит противоречие: в базе появится ссылка на несуществующую запись. Проблемы, возникающие при использовании внешних ключей, подробно рассмотрены в литературе, а здесь отме-тим, что все «приличные» СУБД имеют механизмы для предотв-ращения ввода записей со значениями внешних ключей, отсутст-вующих среди значений соответствующих первичных ключей.

Вставка множества записей

Создать временную таблицу К_меню, содержащую калорийность и стоимость всех блюд, которые можно приготовить из имеющихся продуктов. (Эта таблица будет использоваться шеф-поваром для составления меню на следующий день.)

Для создания описания временной таблицы можно, например, воспользоваться предложением CREATE TABLE

CREATE TABLE К_меню

а для ее загрузки данными – предложение INSERT с вложенным подзапросами:

SELECT Вид, Блюдо,

FROM Блюда, Вид_блюд, Состав, Продукты, Наличие

WHERE Блюда.БЛ = Состав.БЛ

AND Состав.ПР = Продукты.ПР

AND Состав.ПР = Наличие.ПР

AND Блюда.В = Вид_блюд.В

GROUP BY Вид, Блюдо

В этом запросе предложение SELECT выполняется так же, как обычно, но результат не выводится на экран, а копируется в таблицу К_меню. Теперь с этой копией можно работать как с обычной базовой таблицей (Блюда, Про-дукты,…), т.е. выбирать из нее даннные на экран или принтер, обновлять в ней данные и т.п. Никакая из этих операций не будет оказывать влияния на исходные данные (например, изменение в ней названия блюда Салат летний на Салат весенний не приведет к подобному изменению в таблице Блюда, где сохранится старое название). Так как это может привести к противоречиям, то подобные временные таблицы уничтожают после их использования. Поэтому программа, обслуживающая шеф-повара, должна исполнять предложение DROP TABLE К_меню после того, как будет закончено составление меню.

Использование INSERT…SELECT для построения внешнего соединения

Рассмотренное в естественное соединение двух таблиц не включает тех строк какой-либо из них, для которых нет соответствующих строк в другой таблице. Например, если в таблицу Блюда были занесены под номером 34 сведения о Шашлыке, а рецепт его приготовления не был занесен в таблицу Рецепты, то при загрузке их естественного соединения в таблицу Временная:

CREATE TABLE Временная

Рецепт CHAR (560));

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

в ней не окажется строки с Шашлыком (в таблице Рецепты не обнаружен код 34, и строка с этим кодом исключена из результата).

Следовательно, в некотором смысле можно считать, что при обычном соединении теряется информация для таких несоответствующих строк. Однако иногда (как и в приведенном примере) может потребоваться способность сохранить эту информацию. В этом случае можно воспользоваться так называемым внешним соединением:

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

SELECT Вид, Блюдо, «. »

FROM Блюда, Вид_блюд

WHERE Блюда.В = Вид_блюд.В

В результате будет создана базовая таблица

Вид Блюдо Рецепт
Закуска Салат летний Помидоры и яблоки нарезать…
Закуска Салат мясной Вареное охлажденное мясо, …
. . .
Напиток Кофе черный Кофеварку или кастрюлю спо…
Напиток Кофе на молоке Сварить черный кофе, как …
Горячее Шашлык .

где первые 33 строки соответствуют первому INSERT и представляют собой проекцию естественного соединения таблиц Блюда и Рецепты по кодам блюд (БЛ), включающую три столбца. Последняя строка результата соответствует второму INSERT и сохраняет информацию о блюде Шашлык, рецепт котого пока не введен в таблицу Рецепты.

Заметим, что для внешнего соединения нужны два отдельных INSERT…SELECT. Однако тот же результат можно получить и одним INSERT…SELECT, используя фразу UNION, объединяющую предложения SELECT из двух INSERT:

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В

SELECT Вид, Блюдо, «. »

FROM Блюда, Вид_блюд

WHERE Блюда.В = Вид_блюд.В

Обновление единственной записи

Изменить название блюда с кодом БЛ=5 на Форшмак, увеличить его выход на 30 г и установить NULL-значение в столбец Труд.

SET Блюдо = ‘Форшмак’, Выход = (Выход+30), Труд = NULL

Обновление множества записей

Утроить цену всех продуктов таблицы поставки (кроме цены кофе – ПР = 17).

SET Цена = Цена * 3

Обновление с подзапросом

Установить равной нулю цену и К_во продуктов для поставщиков из Паневежиса и Резекне.

SET Цена = 0, К_во = 0

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 отвергнет предложение

позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м месте. Ввод придется осуществить через какую-либо промежуточную таблицу, например, таблицу Выбор:

INTO Выбор (СМ, Т, БЛ)

FROM Выбор;

  1. Составляя предложения модификации данных, необходимо все время помнить о сохранении непротиворечивости базы данных. Об этом упоминалось ранее и подробно говорилось в литературе.

Удаление единственной записи

Удалить поставщика с ПС = 7.

Если таблица Поставки содержит в момент выполнения этого предложения какие-либо поставки для поставщика с ПС = 7, то такое удаление нарушит непротиворечивость базы данных. К сожалению нет операции удаления, одновременно воздействующей на несколько таблиц. Однако в некоторых СУБД реализованы механизмы поддержания целостности, позволяющие отменить некорректное удаление или каскадировать удаление на несколько таблиц.

Удаление множества записей

Удалить все поставки.

Поставки – все еще известная таблица, но в ней теперь нет строк. Для уничтожения таблицы надо выполнить операцию DROP TABLE Поставки.

Удалить все мясные блюда.

DELETE FROM Блюда

WHERE Основа = ‘Мясо’;

Удаление с вложенным подзапросом

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

WHERE Город = ‘Паневежис’);

Вставка единственной записи в таблицу

Добавить в таблицу Блюда блюдо:

Шашлык (БЛ – 34, Блюдо – Шашлык, В – Г, Основа – Мясо, Выход – 150)

при неизвестной пока трудоемкости приготовления этого блюда.

INTO Блюда (БЛ, Блюдо, В, Основа, Выход)

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150);

Создается новая запись для блюда с номером 34, с неопределенным значением в столбце Труд.

Порядок полей в INSERT не обязательно должен совпадать с порядком полей, в котором они определялись при создании таблицы. Вполне допустима и такая версия предыдущего предложения:

INTO Блюда (Основа, В, Блюдо, БЛ, Выход)


VALUES (‘Мясо’, ‘Г’, ‘Шашлык’, 34, 150);

При известной трудоемкости приготовления шашлыка (например, 5 коп) сведения о нем можно ввести с помощью укороченного предложения:

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150, 5);

в котором должен соблюдаться строгий порядок перечисления вводимых значений, так как, не имея перечня загружаемых столб-цов, СУБД может использовать лишь перечень, который определен при создании модифицируемой таблицы.

В предыдущих примерах проводилась модификация стержневой сущности, т.е. таблицы с первичным ключом БЛ. Почти все СУБД имеют механизмы для предотвращения ввода не уникального первичного ключа, например, ввода «Шашлыка» под номером, меньшим 34. А как быть с ассоциациями или другими таблицами, содержащими внешние ключи?

Пусть, например, потребовалось добавить в рецепт блюда Салат летний (БЛ = 1) немного (15 г) лука (ПР = 10), и мы воспользовались предложением

INTO Состав (БЛ, ПР, Вес)

Подобно операции DELETE операция INSERT может нарушить непротиворечивость базы данных. Если не принять специальных мер, то СУБД не проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в таблице Продукты – продукт с первичным ключом ПР = 10. Отсутствие любого из этих значений породит противоречие: в базе появится ссылка на несуществующую запись. Проблемы, возникающие при использовании внешних ключей, подробно рассмотрены в литературе, а здесь отме-тим, что все «приличные» СУБД имеют механизмы для предотв-ращения ввода записей со значениями внешних ключей, отсутст-вующих среди значений соответствующих первичных ключей.

Обновление единственной записи

Изменить название блюда с кодом БЛ=5 на Форшмак, увеличить его выход на 30 г и установить NULL-значение в столбец Труд.

SET Блюдо = ‘Форшмак’, Выход = (Выход+30), Труд = NULL

Обновление множества записей

Утроить цену всех продуктов таблицы поставки (кроме цены кофе – ПР = 17).

SET Цена = Цена * 3

Обновление с подзапросом

Установить равной нулю цену и К_во продуктов для поставщиков из Паневежиса и Резекне.

SET Цена = 0, К_во = 0

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 отвергнет предложение

позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м месте. Ввод придется осуществить через какую-либо промежуточную таблицу, например, таблицу Выбор:

INTO Выбор (СМ, Т, БЛ)

FROM Выбор;

  1. Составляя предложения модификации данных, необходимо все время помнить о сохранении непротиворечивости базы данных. Об этом упоминалось ранее и подробно говорилось в литературе.

Организация Web-доступа к базам данных с использованием SQL-запросов (стр. 14 из 22)

Удаление единственной записи

Удалить поставщика с ПС = 7.

Если таблица Поставки содержит в момент выполнения этого предложения какие-либо поставки для поставщика с ПС = 7, то такое удаление нарушит непротиворечивость базы данных. К сожалению нет операции удаления, одновременно воздействующей на несколько таблиц. Однако в некоторых СУБД реализованы механизмы поддержания целостности, позволяющие отменить некорректное удаление или каскадировать удаление на несколько таблиц.

Удаление множества записей

Удалить все поставки.

Поставки – все еще известная таблица, но в ней теперь нет строк. Для уничтожения таблицы надо выполнить операцию DROP TABLE Поставки.

Удалить все мясные блюда.

DELETE FROM Блюда

WHERE Основа = ‘Мясо’;

Удаление с вложенным подзапросом

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

WHERE Город = ‘Паневежис’);

Вставка единственной записи в таблицу

Добавить в таблицу Блюда блюдо:

Шашлык (БЛ – 34, Блюдо – Шашлык, В – Г, Основа – Мясо, Выход – 150)

при неизвестной пока трудоемкости приготовления этого блюда.

INTO Блюда (БЛ, Блюдо, В, Основа, Выход)

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150);

Создается новая запись для блюда с номером 34, с неопределенным значением в столбце Труд.

Порядок полей в INSERT не обязательно должен совпадать с порядком полей, в котором они определялись при создании таблицы. Вполне допустима и такая версия предыдущего предложения:

INTO Блюда (Основа, В, Блюдо, БЛ, Выход)

VALUES (‘Мясо’, ‘Г’, ‘Шашлык’, 34, 150);

При известной трудоемкости приготовления шашлыка (например, 5 коп) сведения о нем можно ввести с помощью укороченного предложения:

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150, 5);

в котором должен соблюдаться строгий порядок перечисления вводимых значений, так как, не имея перечня загружаемых столб-цов, СУБД может использовать лишь перечень, который определен при создании модифицируемой таблицы.

В предыдущих примерах проводилась модификация стержневой сущности, т.е. таблицы с первичным ключом БЛ. Почти все СУБД имеют механизмы для предотвращения ввода не уникального первичного ключа, например, ввода «Шашлыка» под номером, меньшим 34. А как быть с ассоциациями или другими таблицами, содержащими внешние ключи?

Пусть, например, потребовалось добавить в рецепт блюда Салат летний (БЛ = 1) немного (15 г) лука (ПР = 10), и мы воспользовались предложением

INTO Состав (БЛ, ПР, Вес)

Подобно операции DELETE операция INSERT может нарушить непротиворечивость базы данных. Если не принять специальных мер, то СУБД не проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в таблице Продукты – продукт с первичным ключом ПР = 10. Отсутствие любого из этих значений породит противоречие: в базе появится ссылка на несуществующую запись. Проблемы, возникающие при использовании внешних ключей, подробно рассмотрены в литературе, а здесь отме-тим, что все «приличные» СУБД имеют механизмы для предотв-ращения ввода записей со значениями внешних ключей, отсутст-вующих среди значений соответствующих первичных ключей.

Обновление единственной записи

Изменить название блюда с кодом БЛ=5 на Форшмак, увеличить его выход на 30 г и установить NULL-значение в столбец Труд.

SET Блюдо = ‘Форшмак’, Выход = (Выход+30), Труд = NULL

Обновление множества записей

Утроить цену всех продуктов таблицы поставки (кроме цены кофе – ПР = 17).

SET Цена = Цена * 3

Обновление с подзапросом

Установить равной нулю цену и К_во продуктов для поставщиков из Паневежиса и Резекне.

SET Цена = 0, К_во = 0

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. Но в процессе такого конструирования следует учитывать, что:

3. Если в WHERE фразе предложений DELETE и UPDATE используется вложенный подзапрос, то во фразе FROM этого подзапроса не должна упоминаться таблица, из которой удаляются (в которой обновляются) строки. Аналогично, в подзапросе предложения INSERT не должна упоминаться таблица, в которую загружаются данные.

Так, SQL отвергнет предложение

позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м месте. Ввод придется осуществить через какую-либо промежуточную таблицу, например, таблицу Выбор:

INTO Выбор (СМ, Т, БЛ)

4. Составляя предложения модификации данных, необходимо все время помнить о сохранении непротиворечивости базы данных. Об этом упоминалось ранее и подробно говорилось в литературе.

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 сервер).


Организация Web-доступа к базам данных с использованием SQL-запросов

Такой результат, нестрого говоря, строился следующим образом. FROM. Эта фраза инициирует создание в рабочей памяти таблицы, являющейся декартовым произведением таблиц Блюда,

Организация Web-доступа к базам данных с использованием SQL-запросов

Другие материалы по предмету

Обновление единственной записи

Изменить название блюда с кодом БЛ=5 на Форшмак, увеличить его выход на 30 г и установить NULL-значение в столбец Труд.

SETБлюдо = ‘Форшмак’, Выход = (Выход+30), Труд = NULL

Обновление множества записей

Утроить цену всех продуктов таблицы поставки (кроме цены кофе ПР = 17).

SETЦена = Цена * 3

Обновление с подзапросом

Установить равной нулю цену и К_во продуктов для поставщиков из Паневежиса и Резекне.

SETЦена = 0, К_во = 0

WHERE Город IN (‘Паневежис’, ‘Резекне’));

Обновление нескольких таблиц

Изменить номер продукта ПР = 13 на ПР = 20.

SETПР = 20SETПР = 20

WHEREПР = 13;WHEREПР = 13;

SETПР = 20SETПР = 20

WHEREПР = 13;WHEREПР = 13;

К сожалению в единственным запросе невозможно обновить более одной таблицы, а так как код продукта входит в четыре таблицы, то пришлось выдать четыре сходных запроса. Это может привести к противоречию базы данных (нарушению целостности по ссылкам), поскольку после выполнения первого предложения таблицы Состав, Поставки и Наличие ссылаются на уже несуществующий продукт. База становится непротиворечивой только после выполнения четвертого запроса.

О конструировании предложений модификации

Для тех, кто достаточно хорошо понял предложение SELECT, несложно овладеть конструированием предложений DELETE, INSERT и UPDATE. Но в процессе такого конструирования следует учитывать, что:

  1. Если в WHERE фразе предложений DELETE и UPDATE используется вложенный подзапрос, то во фразе FROM этого подзапроса не должна упоминаться таблица, из которой удаляются (в которой обновляются) строки. Аналогично, в подзапросе предложения INSERT не должна упоминаться таблица, в которую загружаются данные.

Так, SQL отвергнет предложение

позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м месте. Ввод придется осуществить через какую-либо промежуточную таблицу, например, таблицу Выбор:

INTOВыбор (СМ, Т, БЛ)

  1. Составляя предложения модификации данных, необходимо все время помнить о сохранении непротиворечивости базы данных. Об этом упоминалось ранее и подробно говорилось в литературе.

2.3. Обзор основных SQL-серверов.

Общая характеристика продуктов 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 сервер).

Универсальный сервер Oracle позволяет хранить и обрабатывать самые разные типы данных. Кроме привычных структурированных данных (числа, строки, дата, время) можно работать с неструктурированными данными, такими как тексты, многомерные пространственные данные, изображения, видео, аудио. При этом Oracle обеспечивает надежность хранения и быстроту доступа к этим данным, а так же возможность создания приложений, работающих со всеми этими данными в комплексе.

Сегодня Oracle это реляционная СУБД, поддерживающая язык SQL и его расширения для работы с различными типами данных, а так же механизм транзакций. Особенности архитектуры Oracle Server обеспечивают очень высокое быстродействие системы в многопользовательском режиме. Оригинальный механизм многоверсионной записи позволяет получать согласованные результаты при выполнении запросов без блокировки данных. Автоматически выполняется блокировка данных на уровне записи при модификации данных. Это позволяет увеличивать число пользователей системы без снижения ее производительности.

Встроенные оптимизаторы запросов, использование алгоритмов хеширования, битовых индексов и B-деревьев, возможность тонкой настройки СУБД на возможности среды эксплуатации также позволяют обеспечить очень высокое быстродействие. Дополнительная компонента ядра Parallel Query Option позволяет ускорить работу существующих приложений за счет использования возможностей многопроцессорных машин. Эта компонента резко снижает время выполнения отдельного запроса, загрузки данных, построения индекса и т. д. За счет разбиения операций (например, оператора Select) на части и выполнения этих частей параллельно на разных процессорах. Увеличение числа процессоров с 1 до 10 позволяет ускорить выполнение запроса в 8 раз, что очень важно для работы с очень большими БД.

Компоненты Oracle Parallel Server позволяет СУБД Oracle и приложениям работать на МРР и кластерных архитектурах. Наиболее часто кластер реализуется на базе компьютеров фирм DЕC, Sequent, HP, Sun, IBM (RS 6000). При этом все машины кластера мог

Операторы модификации данных

Операторы модификации данных

Язык манипуляции данными (DML — Data Manipulation Language) помимо оператора SELECT, осуществляющего извлечение информации из базы данных, включает операторы, изменяющие состояние данных. Этими операторами являются:

INSERT Добавление записей (строк) в таблицу БД

UPDATE Обновление данных в столбце таблицы БД

DELETE Удаление записей из таблицы БД

Похожие главы из других книг

Глава 1 Типы данных, услоные операторы и массиы VBA

Глава 1 Типы данных, услоные операторы и массиы VBA

Инкапсуляция на основе методов чтения и модификации

Инкапсуляция на основе методов чтения и модификации Давайте снова вернемся к рассмотрению нашего класса Employee. Чтобы «внешний мир» мог взаимодействовать с частным полем данных fullName, традиции велят определить средства чтения (метод get) и модификации (метод set). Например://

Модификации программ

Модификации программ 1. «Сборки» Большую часть программ мы с вами получаем в готовом виде. Но даже при большом желании ее изменить не получится – практически все программы остаются интеллектуальной собственностью разработчика и защищены законом об авторском праве.

Операторы отношения и логические операторы

Операторы отношения и логические операторы Операторы отношения используются для сравнения значений двух переменных. Эти операторы, описанные в табл. П2.11, могут возвращать только логические значения true или false.Таблица П2.11. Операторы отношения Оператор Условие, при

14.8.3. Удаление файлов по времени модификации и другим критериям

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

Операторы

Операторы Операторов язык JavaScript поддерживает очень много — на все случаи жизни. Их можно разделить на несколько групп. Арифметические операторы Арифметические операторы служат для выполнения арифметических действий над числами. Все арифметические операторы,

15.3. Обеспечение невозможности модификации аргумента в функции

15.4. Обеспечение невозможности модификации своих объектов в функции-члене

15.4. Обеспечение невозможности модификации своих объектов в функции-члене ПроблемаТребуется вызывать функции -члены для константного объекта, но ваш компилятор жалуется на то, что он не может преобразовать тип используемого вами объекта из константного в

ОПЕРАТОРЫ

ОПЕРАТОРЫ Введение Операторы языка Си управляют процессом выполнения программы. Набор операторов языка Си содержит все управляющие конструкции структурного программирования. Ниже представлен полный список операторов:пустой операторсоставной оператор или

1.3. Модификации Ubuntu

1.3. Модификации Ubuntu Дистрибутив Ubuntu распространяется в нескольких модификациях:? Kubuntu (http://www.kubuntu.org/) — то же самое, что и Ubuntu, только основана на базе графической среды KDE, а не GNOME. Системные требования такие же. В состав дистрибутива входят программы, основанные на

Условия копирования, распространения и модификации программных продуктов

Условия копирования, распространения и модификации программных продуктов 0. Данная лицензия применяется к любой программе или другому продукту, который содержит замечание, внесенное владельцем авторских прав, где указано, что данный продукт может распространяться

9.2.5. Операторы

9.2.5. Операторы В awk существует достаточно много операторов, манипулирующих числами, строками, переменными, полями и элементами массива. Ниже приведен список основных операторов. =, += *= /= %= Операторы присваивания (простого и составного) ? ; Условный оператор || &&

9.2.8. Операторы присваивания и арифметические операторы

Глава 16. ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С КОМАНДАМИ МОДИФИКАЦИИ

4. ОПЕРАТОРЫ

4. ОПЕРАТОРЫ Операторы в языке Java — это специальные символы, которые сообщают транслятору о том, что вы хотите выполнить операцию с некоторыми операндами. Типы операций указываются с помощью операторов, а операнды — это переменные, выражения или литералы. Некоторые

12.5.6. Алгоритмы генерирования и модификации

12.5.6. Алгоритмы генерирования и модификации Шесть алгоритмов генерирования и модификации либо создают и заполняют новую последовательность, либо изменяют значения в существующей.fill(), fill_n(), for_each(), generate(),generate_n(),

Выборка и модификация данных в MS SQL Server

Читайте также:

  1. C. Этап 3. Подготовка данных
  2. Corporate Information Factory, Корпоративное хранилище данных
  3. D. Очистка данных
  4. Data Mart — Витрины данных
  5. Data Mining (DM) — интеллектуальный анализ данных
  6. Data Warehouse – хранилище данных — ХД — систем обработки данных
  7. Exchange Server.
  8. I. Создание баз данных
  9. If используется для разветвления процесса обработки данных на два направления.
  10. L ТРИПС регулирует вопросы правовой охраны произведений, созданных с применением новых технологий, а также новейшие способы использования произведений.
  11. LAN Server, IВМ Согр
  12. Nameserver 127.0.0.1
Илон Маск рекомендует:  addcslashes - Экранирует спецсимволы в стиле языка C

Создание таблиц и управление ими

Создав базу данных и определив типы данных, мы готовы к созданию таблиц, в которых будут храниться данные. В определение таблицы должно входить как минимум имя таблицы, имена столбцов, типы данных (с указанием длины, если нужно), а также указание того, допустимы ли в столбце пустые значения. Остальные свойства можно настроить позже, хотя чем больше свойств настроено при создании, тем эффективнее становится весь процесс.

Таблица — это набор данных о некоторой сущности, например покупателях, заказах или складских запасах. Таблица состоит из набора столбцов. Каждый столбец представляет атрибут табличных данных. Например, атрибутом сущности «заказ» может быть дата заказа. Каждый экземпляр данных представлен в таблице записью или строкой (иногда их также называют кортежами).

На этом этапе разработки базы данных вы должны собрать все сведения, необходимые для создания таблиц. В идеале все необходимые параметры таблицы определяется одновременно, в том числе PRIMARY KEY и другие ограничения. Сначала мы создадим простые таблицы (в определение которых входит имя, столбцы, типы данных, возможность пустых значений и, где нужно, значения столбцов).

Возможность хранения в столбце пустых значений определяет, могут ли соответствующие поля таблицы хранить пустые значения. Пустое значение (NULL), ноль, пробел и символьная строка нулевой длины (например, «») — это разные вещи. Пустое значение показывает, что в ячейку не были введены данные. Обычно пустое значение свидетельствует о том, что значение не известно или не определено. Например, пустое значение в столбце Price таблицы Titles из базы данных Publications означает не отсутствие цены на книгу, а то, что цена не известна или пока не установлена.

В целом, следует избегать возможности хранения пустых значений, поскольку они усложняют обработку запросов и обновлений, кроме того, их нельзя использовать в столбах с некоторыми параметрами, например с ограничением PRIMARY KEY. Операция сравнения двух пустых значений, а также пустого значения с любым другим значением возвращает неизвестное значение, поскольку значение любого NULL неизвестно. Пустые значения нельзя применять в качестве идентификаторов, позволяющих отличить одну строку таблицы от другой. Кроме того, стоит исключать пустые значения при вычислениях, поскольку некоторые вычисления (например, расчет среднего) могут быть неточны, если в них используется столбец, содержащий NULL. Когда требуется создать столбец, значения которого не известны, зачастую можно определить для него значение по умолчанию. Например, столбец Phone таблицы Authors из базы данных Publications не допускает пустых значений. В этом столбце определено значение по умолчанию UNKNOWN. Если в строке со сведениями об авторе номер телефона не указан, то значение в столбце Phone равно UNKNOWN. Ниже определение значений по умолчанию обсуждается более подробно.

Если при добавлении строки не указано значение поля, допускающего пустые значения, SQL Server присваивает ему пустое значение (если не определено значение или объект по умолчанию). В поле, определенном посредством ключевого слова NULL, пользователь может явно ввести пустое значение, при этом не играют роли ни тип данных поля, ни определенное для него значение по умолчанию. NULL нельзя заключать в кавычки, поскольку в этом случае оно интерпретируется как строка символов, а не как пустое значение.

Если мы зададим поле как не допускающее пустые значения, облегчается обеспечение целостности данных, так как данное поле во всех строках обязательно будут заполнено. Если пустые значения не разрешены, то заполняющий таблицу пользователь должен ввести в поле какое-либо значение, в противном случае строка не будет принята в базу данных.

Возможность ввода пустых значений определяется во время определения столбца, создания или модификации таблицы. Независимо от того, допускает ли столбец пустые значения, из-за сложностей обработки пустых значений в SQL Server в определении всех столбцов всегда приходится использовать ключевые слова NULL или NOT NULL. Если столбец допускает пустые значения, то применяется ключевое слово NULL, если нет — то NOT NULL.

Следующий SQL-сценарий создает посредством оператора CREATE TABLE таблицу Person. Столбцы Person_ID и LastName не допускают пустых значений, а столбец FirstName — допускает:

CREATE TABLE Persons

Person_ID char(4) NOT NULL,

FirstName varchar(30) NULL,

LastName varchar(30) NOT NULL

Все поля записи должны быть заполнены (даже с использованием пустых значений). Иногда требуется загрузить в таблицу строку, но значение какого-либо поля не известно (или не существует). Если поле допускает пустые значения, можно загрузить строку с пустым значением. Поскольку поля, допускающие пустые значения, нежелательны, лучше определить для поля значение по умолчанию (определить ограничение DEFAULT). Например, в качестве значения по умолчанию, которое используется, если значение не указано, для числовых полей обычно задают ноль, а для символьных — «N/A».

При загрузке в таблицу строки с неизвестным значением поля, для которого определено значение по умолчанию, мы неявно указываем серверу на загрузку в это поле значения по умолчанию.

Если поле не допускает пустых значений и для него не определено значение по умолчанию, необходимо явно задать его значения. В противном случае SQL Server вернет ошибку с сообщением, что это поле не допускает пустых значений.

Есть два способа определения значения по умолчанию:

• определить его во время создания самой таблицы (как часть определения таблицы);

• добавить это определение к существующей таблице (у любого столбца таблицы оно может быть только одно).

В следующем SQL-сценарии с помощью оператора CREATE TABLE создается таблица Persons. Ни один из трех ее столбцов не допускает пустых значений. Однако на самом деле столбец FirstName позволяет ввести неизвестное имя, поскольку в его определении содержится определение значения по умолчанию. Для его определения в операторе CREATE TABLE используется ключевое слово DEFAULT.

CREATE TABLE Persons

Person_ID char(4) NOT NULL,

FirstName varchar(30) NOT NULL DEFAULT ‘UnKnown’,

LastName varchar(30) NOT NULL

Существующее определение значения по умолчанию можно модифицировать или удалить. При модификации определения значения по умолчанию с помощью Transact-SQL необходимо сначала удалить существующее определение DEFAULT а затем создать новое определение «с нуля».

Невозможно создать определения DEFAULT в следующих столбцах:


• с типом данных timestamp;

• со свойствами IDENTITY или ROWGUIDCOL;

• с существующим определением или объектом по умолчанию.

Очевидно, что значение по умолчанию должно быть совместимо с типом данных столбца, к которому относится определение DEFAULT. Например, необходимо, чтобы значением по умолчанию для столбца с типом данных int было целое число, а не символьная строка.

Когда определение DEFAULT применяется к существующему столбцу, SQL Server применяет (по умолчанию) новое определение только к новым строкам, добавленным к таблице. Существующие данные, вставленные во время действия старого определения значения по умолчанию, не затрагиваются. Однако при добавлении к существующей таблице нового столбца можно указать серверу на вставку в существующие строки таблицы не пустых значений, а значений по умолчанию (заданные определением умолчания для этого столбца).

Автоматическая нумерация и столбцы с идентификатором

В любой таблице можно создать единственный столбец с идентификатором, который будет содержать сгенерированные системой последовательные значения, уникально идентифицирующие каждую строку в таблице. Например, по мере добавления строк в таблицу столбец с идентификатором может автоматически генерировать для приложения уникальные номера квитанций. Обычно значения столбца с идентификатором уникальны только в пределах таблицы, в которой они определяются. То есть в столбце с идентификатором допустимы значения, совпадающие со значениями идентификатора, которые используются в других таблицах. Однако в такой ситуации проблем обычно не возникает, поскольку значение идентификатора, как правило, используется в контексте одной таблицы, а столбцы с идентификатором не связаны с другими аналогичными столбцами других таблиц.

Для любой таблицы, значения которой уникальны для всех компьютеров мира, объединенных в сеть, можно создать столбец с глобально уникальным идентификатором. Столбец с гарантированно уникальными значениями полезен, когда необходимо свести похожие данные из нескольких серверов (например, в системах выписки счетов, данные о которых находятся в различных частях транснациональной корпорации, расположенных в различных странах). Когда на центральном узле выполняется сведение данных для консолидации и составления отчетов, использование глобально уникальных значений исключает совпадение идентификаторов и номеров счета у покупателей из различных стран. SQL Server использует столбцы с глобально уникальным идентификатором при репликации сведением для обеспечения уникальности идентификатора строк среди нескольких копий одной таблицы.

В любой таблице может быть создан только один столбец с идентификатором или глобально уникальным идентификатором.

Столбцы с идентификатором можно реализовать с помощью свойства IDENTITY, которое позволяет разработчику приложений задать номер идентификатора первой строки, добавленной в таблицу (свойство Identity Seed, исходное значение идентификатора) и инкремент (свойство Identity Increment), который будет прибавляться к исходному значению для определения последовательных значений идентификатора. SQL Server автоматически генерирует следующее значение идентификатора, прибавляя инкремент к исходному значению.

Определяя столбец с идентификатором при помощи свойства IDENTITY, необходимо учитывать следующие моменты:

• в таблице разрешен только один столбец со свойством IDENTITY. Возможные типы данных для этого столбца: decimal, int, numeric, smallint, bigint или tinyint;

• можно задать исходное значение и инкремент идентификатора. Для обоих значение по умолчанию равно 1;

• столбец с идентификатором не доложен допускать пустых значений, в нем запрещены определения или объект DEFAULT;

• когда установлено свойство IDENTITY, на этот столбец можно ссылаться в списке выбора с помощью ключевого слова IDENTITYCOL;

• чтобы узнать, есть ли в таблице столбец со свойством IDENTITY, предназначена функция OBJECTPROPERTY; функция COLUMN PROPERTY позволяет определить имя столбца IDENTITY.

Следующий сценарий создает таблицу Persons с помощью оператора языка Transact-SQL CREATE TABLE. Ни один столбец не допускает пустых значений. Кроме того, столбец Person_ID является столбцом с идентификатором. Исходное значение идентификатора равно 101, инкремент — 1.

CREATE TABLE Persons

Person_ID SMALLINT IDENTITY(101,1) NOT NULL,

PersonName VARCHAR(50) NOT NULL

Как показала практика, если из таблицы, в которой есть столбец с идентификатором, часто удаляют строки, то в последовательности значений идентификатора могут образоваться разрывы.

Удаленные значения идентификатора не используются повторно. Чтобы избежать подобных разрывов, лучше не используйте свойство IDENTITY. Вместо этого создается триггер, который при добавлении строки определит новое значение идентификатора (на основе существующих значений в столбце с идентификатором).

Хотя свойство IDENTITY автоматизирует нумерацию строк таблицы, значения идентификатора в различных таблицах, содержащих столбцы с идентификаторами, могут совпадать. Свойство IDENTITY гарантированно уникально в пределах той таблицы, в которой оно используется. Если разрабатываемое нами приложение должно генерировать столбец с идентификатором, уникальным в пределах всей базы данных (или всех баз данных на всех серверах сети), следует использовать свойство ROWGUIDCOL, тип данных uniqueidenlifier и функцию NEWID.

Когда мы определяем столбец с глобально уникальным идентификатором при помощи свойства ROWGUIDCOL, нужно помнить, что:

• в таблице может быть только один столбец со свойством ROWGUIDCOL, к тому же этот столбец должен быть определен с помощью типа данных uniqueidentifier;

• SQL Server не генерирует значения этого столбца автоматически; для добавления глобально уникального значения нужно создать в столбце определение DEFAULT, которое генерирует глобально уникальное значение с помощью функции NEWID;

• после того как свойство ROWGUIDCOL установлено, на этот столбец разрешается ссылаться с помощью ключевого слова ROWGUIDCOL, аналогично тому, как на столбец со свойством IDENTITY ссылаются с помощью ключевого слова IDENTITYCOL;

• функция OBJECTPROPERTY позволяет определить, есть ли в таблице столбец со свойством ROWGUIDCOL, а функция COLUMNPROPERTY — имя этого столбца;

• поскольку свойство ROWGUIDCOL не обеспечивает уникальности само по себе, следует использовать ограничение UNIQUE, чтобы гарантировать уникальность значений столбца ROWGUIDCOL.

В следующем сценарии создается таблица Persons с помощью оператора CREATE TABLE. Столбец Person_ID автоматически генерирует GUID для каждой новой строки, добавляемой к таблице:

CREATE TABLE Persons

Person_ID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL,

PersonName VARCHAR(60) NOT NULL

Методы создания таблиц

SQL Server предоставляет несколько методов создания таблиц: оператор языка Transact-SQL CREATE TABLE, консоль SQL Server Management Studio и Database Designer, который доступен через SQL Server Management Studio.

Оператор CREATE TABLE позволяет создать таблицу в базе данных SQL Server. При использовании этого оператора надо определить как минимум имя таблицы, столбцы и их типы данных (а также их значения, если возможно). Следующий сценарий иллюстрирует создание простой таблицы:

CREATE TABLE Ships

В дополнение к основным элементам таблицы (имени, столбцам и типам данных) оператор CREATE TABLE также позволяет определить ряд других свойств. Например, задать группу файлов, в которой должна храниться таблица, определить ограничения для отдельных столбцов или таблицы в целом. Полное описание оператора CREATE TABLE можно найдете в справочнике по языку Transact-SQL в SQL Server Books Online.

Таблицы можно создавать непосредственно в SQL Server Management Studio. Чтобы создать таблицу в существующей базе данных, нужно раскройте консоль до базы данных, далее раскрыть базу данных и щелкнуть вспомогательной кнопкой на узел Tables, а затем щелкните New Table. В появившемся окне New Table нужно ввести всю необходимую для определения таблицы информацию.

В SQL Server Management Studio средства Database Designer позволяют добавить таблицу к диаграмме базы данных, отредактировать структуру таблицы или связать ее с другими таблицами, показанными на диаграмме. К диаграмме можно добавлять как существующие, так и новые таблицы, еще не определенные в базе данных. В качестве альтернативы разрешается строить новые таблицы и модифицировать существующие с помощью Table Designer. Этот визуальный инструмент позволяет конструировать и визуализировать таблицу в базе данных, к которой мы подключились.

Управление таблицами в базе данных SQL Server

После создания таблицы в базе данных SQL Server можно просмотреть сведения о ней, модифицировать ее характеристики или удалить ее из базы данных.

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

В SQL Server существует несколько методов просмотра характеристик и зависимостей таблицы:

• для просмотра определения таблицы используется системная хранимая процедура sp_help или SQL Server Management для просмотра свойств таблицы;

• чтобы просмотреть зависимости таблицы, можно воспользоваться системной хранимой процедурой sp_depends или параметром Display Dependencies в SQL Server Management Studio;

• для просмотра свойств столбца используется оператор COLUMN PROPERTY, который возвращает сведения о столбце или параметре процедуры.

После создания таблицы многие параметры, определенные во время ее создания, разрешается изменять. Например, добавлять, модифицировать и удалять столбцы, в том числе изменять имя, длину, тип данных, точность (точность дробной части), возможность ввода пустых значений — последнее с некоторыми ограничениями. Более подробно об этом рассказано в SQL Server Books Online, здесь мы просто перечислим все возможные операции:

• добавление и удаление ограничения PRIMARY KEY и FOREIGN KEY;

• добавление и удаление ограничения UNIQUE и CHECK, а также определение ограничений (и объектов) DEFAULT;

• добавление и удаление столбцов с идентификатором с помощью свойств IDENTITY или ROWGUIDCOL. Разрешается добавлять и удалять свойство ROWGUIDCOL у существующих столбцов, но при этом в любой момент времени возможен только один столбец со свойством ROWGUIDCOL;

• таблицы и выбранные столбцы таблицы можно зарегистрировать для полнотекстового индексирования.

Кроме того, разрешается изменить имя или владельца таблицы. При этом также придется исправить имя таблицы во всех триггерах, хранимых процедурах, сценариях Transact-SQL и других программах, в которых используется старое имя или владелец таблицы.

Некоторые типы изменений свойств таблицы перечислены в следующей таблице. Здесь также указаны методы внесения этих изменений. Более подробную информацию об этом можно найти в SQL Server Books Online.

Тип изменения Методика внесения
Переименование таблицы Системная хранимая процедура sp_rename, параметр Rename в SQL Server Management Studio
Изменение владельца таблицы Системная хранимая процедура sp_changeobjectowner
Модификация свойств столбца Оператор ALTER TABLE, функция Table Designer в SQL Server Management Studio
Переименование столбца Системная хранимая процедура sp_rename, функция Table Designer в SQL Server Management Studio

Иногда, например, если нужно реализовать новую структуру или освободить место в базе данных, необходимо удалить таблицу. При этом структурное определение таблицы, данные, полнотекстовые индексы, ограничения и индексы полностью удаляются из базы данных, а место, прежде занятое таблицей и ее индексами, становится доступным для других таблиц. Можно явно удалить временную таблицу, если мы не хотим ждать ее автоматического удаления.

Илон Маск рекомендует:  Краткие описания основных функций и команд vb (для начинающих)

Если необходимо удалить таблицы, связанные посредством ограничения FOREIGN KEY, UNIQUE или PRIMARY KEY, то сначала следует удалить таблицы с ограничением FOREIGN KEY. Если требуется удалить таблицу, на которую есть ссылка в ограничении FOREIGN KEY, но при этом полностью удалять таблицу с внешним ключом нельзя, следует удалить ограничение FOREIGN KEY.

Удаляют таблицу из базы данных SQL Server с помощью оператора DROP TABLE или SQL Server Management Studio (который удаляет таблицу из узла Tables).

DROP TABLE Ships

Дата добавления: 2014-01-11 ; Просмотров: 1379 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть пятая

Предыдущие части

В данной части мы рассмотрим

Здесь мы в общих чертах рассмотрим работу с операторами модификации данных:

  • INSERT – вставка новых данных
  • UPDATE – обновление данных
  • DELETE – удаление данных
  • SELECT … INTO … – сохранить результат запроса в новой таблице
  • MERGE – слияние данных
  • Использование конструкции OUTPUT
  • TRUNCATE TABLE – DDL-операция для быстрой очистки таблицы

В самом конце вас ждут «Приложение 1 – бонус по оператору SELECT» и «Приложение 2 – OVER и аналитические функции», в которых будут показаны некоторые расширенные конструкции:

  • PIVOT
  • UNPIVOT
  • GROUP BY ROLLUP
  • GROUP BY GROUPING SETS
  • использование приложения OVER

Операции модификации данных очень сильно связаны с конструкциями оператора SELECT, т.к. по сути выборка модифицируемых данных идет при помощи них. Поэтому для понимания данного материала, важное место имеет уверенное владение конструкциями оператора SELECT.

Данная часть, как я и говорил, будет больше обзорная. Здесь я буду описывать только те основные формы операторов модификации данных, которыми я сам регулярно пользуюсь. Поэтому на полноту изложения рассчитывать не стоит, здесь будут показан только необходимый минимум, который новички могут использовать как направление для более глубокого изучения. За более подробной информацией по каждому оператору обращайтесь в MSDN. Хотя кому-то возможно и в таком объеме информации будет вполне достаточно.

Т.к. прямая модификация информации в РБД требует от человека большой ответственности, а также потому что пользователи обычно модифицируют информацию БД посредством разных АРМ, и не имеют полного доступа к БД, то данная часть больше посвящается начинающим ИТ-специалистам, и я буду здесь очень краток. Но конечно, если вы смогли освоить оператор SELECT, то думаю, и операторы модификации вам будут под силу, т.к. после оператора SELECT здесь нет ничего сверхсложного, и по большей части должно восприниматься на интуитивном уровне. Но порой сложность представляют не сами операторы модификации, а то что они должны выполняться группами, в рамках одной транзакции, т.е. когда дополнительно нужно учитывать целостность данных. В любом случае можете почитать и попытаться проделать примеры в ознакомительных целях, к тому же в итоге вы сможете получить более детальную базу, на которой можно будет отработать те или иные конструкции оператора SELECT.

Проведем изменения в структуре нашей БД

Давайте проведем небольшое обновление структуры и данных таблицы Employees:

А также для демонстрационных целей расширим схему нашей БД, а за одно повторим DDL. Назначения таблиц и полей указаны в комментариях:

Вот такой полигон мы должны были получить в итоге:

Кстати, потом этот полигон (когда он будет наполнен данными) вы и можете использовать для того чтобы опробовать на нем разнообразные запросы – здесь можно опробовать и разнообразные JOIN-соединения, и UNION-объединения, и группировки с агрегированием данных.

INSERT – вставка новых данных

Данный оператор имеет 2 основные формы:

  1. INSERT INTO таблица(перечень_полей) VALUES(перечень_значений) – вставка в таблицу новой строки значения полей которой формируются из перечисленных значений
  2. INSERT INTO таблица(перечень_полей) SELECT перечень_значений FROM … – вставка в таблицу новых строк, значения которых формируются из значений строк возвращенных запросом.

В диалекте MS SQL слово INTO можно отпускать, что мне очень нравится и я этим всегда пользуюсь.

К тому же стоит отметить, что первая форма в диалекте MS SQL с версии 2008, позволяет вставить в таблицу сразу несколько строк:

INSERT – форма 1. Переходим сразу к практике

Наполним таблицу EmployeesSalaryHistory предоставленными нам данными:

Таким образом мы вставили в таблицу EmployeesSalaryHistory 11 новых записей.

EmployeeID DateFrom DateTo Salary
1000 2013-11-01 2014-05-31 4000.00
1000 2014-06-01 2014-12-30 4500.00
1000 2015-01-01 NULL 5000.00
1001 2013-11-01 2014-06-30 1300.00
1001 2014-07-01 2014-09-30 1400.00
1001 2014-10-01 NULL 1500.00
1002 2014-01-01 NULL 2500.00
1003 2014-06-01 NULL 2000.00
1004 2014-07-01 2015-01-31 1400.00
1004 2015-02-01 2015-01-31 1500.00
1005 2015-01-01 NULL 2000.00

Хоть мы в этом случае могли и не указывать перечень полей, т.к. мы вставляем данные всех полей и в таком же виде, как они перечислены в таблице, т.е. мы могли бы написать:

Но я бы не рекомендовал использовать такой подход, особенно если данный запрос будет использоваться регулярно, например, вызываясь из какого-то АРМ. Опять же это чревато тем, что структура таблицы может изменяться, в нее могут быть добавлены новые поля, или же последовательность полей может быть изменена, что еще опасней, т.к. это может привести к появлению логических ошибок во вставленных данных. Поэтому лучше лишний раз не полениться и перечислить явно все поля, в которые вы хотите вставить значение.

Несколько заметок про INSERT:

  • Порядок перечисления полей не имеет значения, вы можете написать и (EmployeeID,DateFrom,DateTo,Salary) и (DateFrom,DateTo, EmployeeID,Salary). Здесь важно только то, чтобы он совпадал с порядком значений, которые вы перечисляете в скобках после ключевого слова VALUES.
  • Так же важно, чтобы при вставке были заданы значения для всех обязательных полей, которые помечены в таблице как NOT NULL.
  • Можно не указывать поля у которых была указана опция IDENTITY или же поля у которых было задано значение по умолчанию при помощи DEFAULT, т.к. в качестве их значения подставится либо значение из счетчика, либо значение, указанное по умолчанию. Такие вставки мы уже делали в первой части.
  • В случаях, когда значение поля со счетчиком нужно задать явно используйте опцию IDENTITY_INSERT.

В предыдущих частях мы периодически использовали опцию IDENTITY_INSERT. Давайте и здесь воспользуемся данной опцией для создания строк в таблице BonusTypes, у которой поле ID указано с опцией IDENTITY:

Давайте вставим информацию по начислению сотрудникам ЗП, любезно предоставленную нам бухгалтером:

Думаю, приводить содержимое таблицы уже нет смысла.

INSERT – форма 2

Данная форма позволяет вставить в таблицу данные полученные запросом.

Для демонстрации наполним таблицу с начислениями бонусов одним большим запросом:

В таблицу EmployeesBonus должно было вставиться 50 записей.

Результат каждого запроса объединенных конструкциями UNION ALL вы можете проанализировать самостоятельно. Если вы хорошо изучили базовые конструкции, то вам должно быть все понятно, кроме возможно конструкции с VALUES (конструктор табличных значений), которая появилась с MS SQL 2008.

Пара слов про конструкцию VALUES

В случае необходимости, данную конструкцию можно заменить, аналогичным запросом, написанным через UNION ALL:

Думаю, комментарии излишни и вам не составит большого труда разобраться с этим самостоятельно.

Так что, идем дальше.

INSERT + CTE-выражения

Совместно с INSERT можно применять CTE выражения. Для примера перепишем тот же запрос перенеся все подзапросы в блок WITH.

Для начала полностью очистим таблицу EmployeesBonus при помощи операции TRUNCATE TABLE:

Теперь перепишем запрос вынеся запросы в блок WITH:

Как видим вынос больших подзапросов в блок WITH упростил основной запрос – сделал его более понятным.

UPDATE – обновление данных

Данный оператор в MS SQL имеет 2 формы:

  1. UPDATE таблица SET … WHERE условие_выборки – обновлении строк таблицы, для которых выполняется условие_выборки. Если предложение WHERE не указано, то будут обновлены все строки. Это можно сказать классическая форма оператора UPDATE.
  2. UPDATE псевдоним SET … FROM … – обновление данных таблицы участвующей в предложении FROM, которая задана указанным псевдонимом. Конечно, здесь можно и не использовать псевдонимов, используя вместо них имена таблиц, но с псевдонимом на мой взгляд удобнее.

Давайте при помощи первой формы приведем даты приема каждого сотрудника в порядок. Выполним 6 отдельных операций UPDATE:

Вторую форму, где применялся псевдоним, мы уже тоже успели использовать в первой части, когда обновляли поля PositionID и DepartmentID, на значения возвращаемые подзапросами:

Сейчас конечно данный и следующий запрос не сработают, т.к. поля Position и Department мы удалили из таблицы Employees. Вот так можно было бы представить этот запрос при помощи операций соединений:

Надеюсь суть обновления здесь понятна, тут обновляться будут строки таблицы Employees.

Сначала вы можете сделать выборку, чтобы посмотреть какие данные будут обновлены и на какие значения:

А потом переписать это в UPDATE:

Эх, не могу я так, все-таки давайте посмотрим, как это работает наглядно.


Для этого опять вспомним DDL и временно создадим поля Position и Department в таблице Employees:

Зальем в них данные, предварительно посмотрев при помощи SELECT, что получится:

Теперь перепишем и выполним обновление:

Посмотрите, что получилось (должны были появиться значения в 2-х полях – Position и Department, находящиеся в конце таблицы):

Теперь и этот запрос:

Не забудьте только предварительно посмотреть (это очень полезная привычка):

И конечно же можете использовать здесь условие WHERE:

Все, убедились, что все работает. Если хотите, то можете снова удалить поля Position и Department.

Вторую форму можно так же использовать с подзапросом:

В данном случае подзапрос должен возвращать в явном виде строки таблицы Employees, которые будут обновлены. В подзапросе нельзя использовать группировки или предложения DISTINCT, т.к. в этом случае мы не получим явных строк таблицы Employees. И соответственно все обновляемые поля должны содержаться в предложении SELECT, если конечно вы не указали «SELECT *».

Так же с UPDATE вы можете использовать CTE-выражения. Для примера перенесем наш подзапрос в блок WITH:

DELETE – удаление данных

Принцип работы DELETE похож на принцип работы UPDATE, и так же в MS SQL можно использовать 2 формы:

  1. DELETE таблица WHERE условие_выборки – удаление строк таблицы, для которых выполняется условие_выборки. Если предложение WHERE не указано, то будут удалены все строки. Это можно сказать классическая форма оператора DELETE (только в некоторых СУБД нужно писать DELETE FROM таблица WHERE условие_выборки).
  2. DELETE псевдоним FROM … – удаление данных таблицы участвующей в предложения FROM, которая задана указанным псевдонимом. Конечно, здесь можно и не использовать псевдонимов, используя вместо них имена таблиц, но с псевдонимом на мой взгляд удобнее.

Для примера при помощи первого варианта:

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

Убедились, что все нормально. Переписываем запрос на DELETE:

В качестве таблицы Positions может выступать и подзапрос, главное, чтобы он однозначно возвращал строки, которые будут удаляться. Давайте добавим для демонстрации в таблицу Positions мусора:

Теперь для демонстрации используем вместо таблицы Positions, подзапрос, в котором отбираются только определенные строки из таблицы Positions:

Так же мы можем использовать CTE выражения (подзапросы, оформленные в блоке WITH). Давайте снова добавим для демонстрации в таблицу Positions мусора:

И посмотрим на тот же запрос с CTE-выражением:

Заключение по INSERT, UPDATE и DELETE

Вот по сути и все, что я хотел рассказать вам про основные операторы модификации данных – INSERT, UPDATE и DELETE.

Я считаю, что данные операторы очень легко понять интуитивно, когда умеешь пользоваться конструкциями оператора SELECT. Поэтому рассказ о операторе SELECT растянулся на 3 части, а рассказ о операторах модификации был написан в такой беглой форме.

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

В дополнение скажу, что в диалекте MS SQL cо всеми операциями модификации можно использовать предложение TOP (INSERT TOP …, UPDATE TOP …, DELETE TOP …), но мне пока ни разу не приходилось прибегать к такой форме, т.к. здесь непонятно какие именно TOP записей будут обработаны.

Если уж нужно обработать TOP записей, то я, наверное, лучше воспользуюсь указанием опции TOP в подзапросе и применю в нем нужным мне образом ORDER BY, чтобы явно знать какие именно TOP записей будут обработаны. Для примера снова добавим мусора:

И удалим 2 последние записи:

Я здесь привожу примеры больше в целях демонстрации возможностей языка SQL. В реальных запросах старайтесь выражать свои намерения очень точно, дабы выполнение вашего запроса не привело к порче данных. Еще раз скажу – будьте очень внимательны, и не ленитесь делать предварительные проверки.

SELECT … INTO … – сохранить результат запроса в новой таблице

Данная конструкция позволяет сохранить результат выборки в новой таблице. Она представляет из себя что-то промежуточное между DDL и DML.

Типы колонок созданной таблицы будут определены на основании типов колонок набора, полученного запросом SELECT. Если в выборке присутствуют результаты выражений, то им должны быть заданы псевдонимы, которые будут служить в роли имен колонок.

Давайте отберем следующие данные и сохраним их в таблице EmployeesBonusTarget (перед FROM просто пишем INTO и указываем имя новой таблицы):

Можете обновить список таблиц в инспекторе объектов и увидеть новую таблицу EmployeesBonusTarget:

На самом деле я специально создал таблицу EmployeesBonusTarget, я ее буду использовать для демонстрации оператора MERGE.

Еще пара слов про конструкцию SELECT … INTO …

Данную конструкцию иногда удобно применять при формировании очень сложных отчетов, которые требуют выборки из множества таблиц. В этом случае данные обычно сохраняют во временных таблицах (#). Т.е. предварительно при помощи запросов, мы сбрасываем данные во временные таблицы, а затем используем эти временные таблицы в других запросах, которые формируют окончательный результат:

Иногда данную конструкцию удобно использовать, чтобы сделать полную копию всех данных текущей таблицы:

Это можно использовать, например, для подстраховки, перед тем как вносить серьезные изменения в структуру таблицы Employees. Вы можете сохранить копию либо всех данных таблицы, либо только тех данных, которых коснется модификация. Т.е. если что-то пойдет не так, вы сможете восстановить данные таблицы Employees с этой копии. В таких случаях конечно хорошо сделать предварительный бэкап БД на текущий момент, но это бывает не всегда возможно из-за огромных объемов, срочности и т.п.

Чтобы не засорять основную базу, можно создать новую БД и сделать копию таблицы туда:

Для того чтобы увидеть новую БД TestTemp, соответственно, обновите в инспекторе объектов список баз данных, в ней и уже можете найти данную таблицу.

На заметку.
В БД Oracle так же есть конструкция для сохранения результата запроса в новую таблицу, выглядит она следующим образом:

MERGE – слияние данных

Данный оператор хорошо подходит для синхронизации данных 2-х таблиц. Такая задача может понадобится при интеграции разных систем, когда данные передаются порциями из одной системы в другую.

В нашем случае, допустим, что стоит задача синхронизации таблицы EmployeesBonusTarget с таблицей EmployeesBonus.

Давайте добавим в таблицу EmployeesBonusTarget какого-нибудь мусора:

Теперь при помощи оператора MERGE добьемся того, чтобы данные в таблице EmployeesBonusTarget стали такими же, как и в EmployeesBonus, т.е. сделаем синхронизацию данных.

Синхронизацию мы будем осуществлять на основании сопоставления данных входящих в первичный ключ таблицы EmployeesBonus (EmployeeID, BonusDate, BonusTypeID):

  1. Если для строки таблицы EmployeesBonusTarget соответствия по ключу не нашлось, то нужно сделать удаление таких строк из EmployeesBonusTarget
  2. Если соответствие нашлось, то нужно обновить строки EmployeesBonusTarget данными соответствующей строки из EmployeesBonus
  3. Если строка есть в EmployeesBonus, но ее нет в EmployeesBonusTarget, то ее нужно добавить в EmployeesBonusTarget

Сделаем реализацию всей этой логики при помощи инструкции MERGE:

Данная конструкция должна оканчиваться «;».

После выполнения запроса сравните 2 таблицы, их данные должны быть одинаковыми.

Конструкция MERGE чем-то напоминает условный оператор CASE, она так же содержит блоки WHEN, при выполнении условий которых происходит то или иное действие, в данном случае удаление (DELETE), обновление (UPDATE) или добавление (INSERT). Модификация данных производится в таблице приемнике.

В качестве источника может выступать запрос. Например, синхронизируем только данные по отделу 3 и для примера исключаем блок «NOT MATCHED BY SOURCE», чтобы данные не удались в случае не совпадения:

Я показал работу конструкции MERGE в самом общем ее виде. При помощи нее можно реализовывать более разнообразные схемы для слияния данных, например, можно включать в блоки WHEN дополнительные условия (WHEN MATCHED AND … THEN). Это очень мощная конструкция, позволяющая в подходящих случаях сократить объем кода и совместить в рамках одного оператора функционал всех трех операторов – INSERT, UPDATE и DELETE.

И естественно с конструкцией MERGE так же можно применять CTE-выражения:

В общем, я постарался вам задать направление, более подробнее, в случае необходимости, изучайте уже самостоятельно.

Использование конструкции OUTPUT

Конструкция OUTPUT дает возможность получить информацию по строкам, которые были добавлены, удалены или изменены в результате выполнения DML команд INSERT, DELETE, UPDATE и MERGE. Данная конструкция, представляет расширение для операций модификации данных и в каждой СУБД может быть реализовано по-своему, либо вообще отсутствовать.

Конструкция OUTPUT имеет 2 основные формы:

  1. OUTPUT перечень_выражений – используется для возврата результата в виде набора
  2. OUTPUT перечень_выражений INTO принимающая_таблица(список_полей) – используется для вставки результата в указанную таблицу

Рассмотрим первую форму

Добавим в таблицу Positions новые записи:

После выполнения данной операции, записи будут вставлены в таблицу Positions и в добавок мы увидим информацию по добавленным строкам на экране.

Ключевое слово «inserted» дает нам доступ к значениям добавленных строк. В данном случае использование «inserted.*» вернет нам информацию по всем полям, которые есть в таблице Positions (ID и Name).

Так же после OUTPUT вы можете явно указать возвращаемый на экран перечень полей посредством «inserted.имя_поля», также вы можете использовать разные выражения:

При использовании DML команды DELETE, доступ к значениям измененных строк получается при помощи ключевого слова «deleted»:

При использовании DML команды UPDATE, мы можем использовать ключевое слово:

  • deleted – для того, чтобы получить доступ к значениям строки, которые были до обновления (старые значения)
  • inserted – для того, чтобы получить новые значения строки

Продемонстрируем на таблице Employees:

ID Старая Фамилия Старое Имя ID Новая Фамилия Новое Имя
1005 NULL NULL 1005 Александров Александр

В случае MERGE мы можем так же использовать «inserted» и «deleted» для доступа к значениям обработанных строк.

Давайте для примера создадим таблицу PositionsTarget, на которой после будет показан пример с MERGE:

Добавим в PositionsTarget мусора:

Выполним команду MERGE с конструкцией OUTPUT:

Old_ID Old_Name New_ID New_Name OperType
NULL NULL 1 Бухгалтер I
2 Директор-old 2 Директор U
NULL NULL 3 Программист I
NULL NULL 4 Старший программист I
100 Qwert NULL NULL D
101 Asdf NULL NULL D

Думаю, назначение первой формы понятно – сделать модификацию и получить результат в виде набора, который можно вернуть пользователю.

Рассмотрим вторую форму

У конструкции OUTPUT, есть и более важное предназначение – она позволяет не только получить, но и зафиксировать (OUTPUT … INTO …) информацию о том, что уже произошло по факту, то есть после выполнения операции модификации. Она может оказаться полезна в случае логированния произошедших действий. В некоторых случаях, ее можно использовать как хорошую альтернативу тригерам (для прозрачности действий).

Давайте создадим демонстрационную таблицу, для логирования изменений по таблице Positions:

А теперь сделаем при помощи конструкции (OUTPUT … INTO …) запись в эту таблицу:

Посмотрите, что получилось:

TRUNCATE TABLE – DDL-операция для быстрой очистки таблицы

Данный оператор является DDL-операцией и служит для быстрой очистки таблицы – удаляет все строки из нее. За более детальными подробностями обращайтесь в MSDN.

Некоторые вырезки из MSDN. TRUNCATE TABLE – удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.

Если таблица содержит столбец идентификаторов (столбец с опцией IDENTITY), счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.

Инструкцию TRUNCATE TABLE нельзя использовать если на таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.

Заключение по операциям модификации данных

Здесь я наверно повторю, все что писал ранее.

Старайтесь в первую очередь написать запрос на модификацию как можно проще, в первую очередь попытайтесь выразить свое намерение при помощи базовых конструкций и в последнюю очередь прибегайте к использованию подзапросов.

Прежде чем запустить запрос на модификацию данных по условию, убедитесь, что он выбирает именно необходимые записи, а не больше и не меньше. Для этой цели воспользуйтесь операцией SELECT.

Не забывайте перед очень серьезными изменениями делать резервные копии, хотя бы той информации, которая будет подвергнута модификации, это можно сделать при помощи SELECT … INTO …

Помните, что модификация данных это очень серьезно.

Приложение 1 – бонус по оператору SELECT

Подумав, я решил дописать этот раздел для тех, кто дошел до конца.

В данном разделе я дам примеры с использованием некоторых расширенных конструкций:

  • PIVOT
  • UNPIVOT
  • GROUP BY ROLLUP
  • GROUP BY GROUPING SETS

Попробуйте разобрать каждый из следующих примеров самостоятельно, анализируя результаты выполнения запросов. Обращайте внимание на комментарии, которые я указал в текстах запросов, некоторые важные вещи указаны в них.

Получение сводных отчетов при помощи GROUP BY+CASE и конструкции PIVOT

Для начала давайте посмотрим, как можно создать сводный отчет при помощи конструкции GROUP BY и CASE-условий. Можно сказать, это классический способ создания сводных отчетов:

Теперь рассмотрим, как получить эти же данные при помощи конструкции PIVOT:

В конструкции PIVOT кроме SUM, как вы думаю догадались, можно использовать и другие агрегатные функции (COUNT, AVG, MIN, MAX, …).

Давайте теперь рассмотрим, как работает конструкция UNPIVOT. Для демонстрации сбросим сводный результат в таблицу DemoPivotTable:

Первым делом посмотрите, как у нас выглядят данные в данной таблице:

Теперь применим к данной таблице конструкцию UNPIVOT:

Обратите внимание, что NULL значения не войдут в результат.

Как вы наверно догадались, на месте таблицы может стоять и подзапрос с заданным для него псевдонимом.

GROUP BY ROLLUP и GROUP BY GROUPING SETS

Данные конструкции позволяют подбить промежуточные итоги по строкам.

Чтобы понять, как работает функции GROUPING, раскомментируйте поля g1, g2 и g3, чтобы они попали в результирующий набор, а также закомментируйте предложение HAVING.

Здесь для понимания, можете так же раскомментировать поле gID и закомментировать предложение HAVING.

При помощи GROUPING SET можно явно указать какие именно итоги нам нужны, поэтому здесь можно обойтись без предложения HAVING.

Т.е. можно сказать, что GROUP BY ROLLUP частный случай GROUP BY GROUPING SETS, когда делается вывод всех итогов.

Пример использования FULL JOIN

Здесь для примера выведем для каждого сотрудника сводные данные по начислениям бонусов и ЗП, поквартально:


Попробуйте самостоятельно разобрать, почему я здесь применил именно FULL JOIN. Посмотрите на результаты, которые дают запросы размещенные в блоке WITH.

Приложение 2 – OVER и аналитические функции

Предложение OVER служит для проведения дополнительных вычислений, на окончательном наборе, полученном оператором SELECT (в подзапросах или запросах). Поэтому предложения OVER может быть применено только в блоке SELECT, т.е. его нельзя использовать, например, в блоке WHERE.

Выражения с использованием OVER могут в некоторых ситуациях значительно сократить запрос. В данном приложении я постарался привести самые основные моменты с использованием данной конструкции. Надеюсь, что самостоятельная проработка каждого приведенного здесь запроса и их результатов, поможет вам разобраться с особенностями конструкции OVER и вы сможете применять ее по назначению (не злоупотребляя ими чрезмерно там, где можно обойтись без них и наоборот) при написании своих запросов.

Для демонстрационных целей, для получения более наглядных результатов, добавим немного новых данных:

Предложение OVER дает возможность делать агрегатные вычисления, без применения группировки

ID Name DepartmentID Salary AllSalary DepartmentSalary SalaryPercentOfDepSalary AllEmplCount DepEmplCount
1005 Александров А.А. NULL 2000.00 19900.00 2000.00 100.000 10 1
1000 Иванов И.И. 1 5000.00 19900.00 5000.00 100.000 10 1
1002 Сидоров С.С. 2 2500.00 19900.00 2500.00 100.000 10 1
1003 Андреев А.А. 3 2000.00 19900.00 5000.00 40.000 10 3
1004 Николаев Н.Н. 3 1500.00 19900.00 5000.00 30.000 10 3
1001 Петров П.П. 3 1500.00 19900.00 5000.00 30.000 10 3
1006 Антонов А.А. 4 1800.00 19900.00 1800.00 100.000 10 1
1007 Максимов М.М. 5 1200.00 19900.00 3600.00 33.333 10 3
1008 Данилов Д.Д. 5 1200.00 19900.00 3600.00 33.333 10 3
1009 Остапов О.О. 5 1200.00 19900.00 3600.00 33.333 10 3

Предложение «PARTITION BY» позволяет сделать разбиение данных по группам, можно сказать выполняет здесь роль «GROUP BY».

Можно задать группировку по нескольким полям, использовать выражения, например, «PARTITION BY DepartmentID,PositionID», «PARTITION BY DepartmentID,YEAR(HireDate)».

Поэкспериментируйте и с другими агрегатными функциями, которые мы разбирали – AVG, MIN, MAX, COUNT с DISTINCT.

Нумерация и ранжирование строк

Для цели нумерации строк используется функция ROW_NUMBER.

Пронумеруем сотрудников по полю Name и по нескольким полям LastName,FirstName,MiddleName:

ID Name EmpNoByName EmpNoByFullName
1005 Александров А.А. 1 6
1003 Андреев А.А. 2 7
1006 Антонов А.А. 3 1
1008 Данилов Д.Д. 4 2
1000 Иванов И.И. 5 8
1007 Максимов М.М. 6 3
1004 Николаев Н.Н. 7 4
1009 Остапов О.О. 8 5
1001 Петров П.П. 9 9
1002 Сидоров С.С. 10 10

Здесь для задания порядка в OVER используется предложение «ORDER BY».

Для разбиения на группы, здесь так же в OVER можно использовать предложение «PARTITION BY»:

ID EmpName DepName EmpNoInDepByName
1005 Александров А.А. NULL 1
1000 Иванов И.И. Администрация 1
1002 Сидоров С.С. Бухгалтерия 1
1003 Андреев А.А. ИТ 1
1004 Николаев Н.Н. ИТ 2
1001 Петров П.П. ИТ 3
1008 Данилов Д.Д. Логистика 1
1007 Максимов М.М. Логистика 2
1009 Остапов О.О. Логистика 3
1006 Антонов А.А. Маркетинг и реклама 1

Ранжирование строк – это можно сказать нумерация, только группами. Есть 2 вида нумерации, с дырками (RANK) и без дырок (DENSE_RANK).

ID EmpName PositionID EmpCountInPos RankValue DenseRankValue
1005 Александров А.А. NULL 1 1 1
1002 Сидоров С.С. 1 1 2 2
1000 Иванов И.И. 2 1 3 3
1001 Петров П.П. 3 2 4 4
1004 Николаев Н.Н. 3 2 4 4
1003 Андреев А.А. 4 1 6 5
1006 Антонов А.А. 10 1 7 6
1007 Максимов М.М. 11 3 8 7
1008 Данилов Д.Д. 11 3 8 7
1009 Остапов О.О. 11 3 8 7

Аналитические функции: LAG() и LEAD(), FIRST_VALUE() и LAST_VALUE()

Данные функции позволяют получить значения другой строки относительно текущей строки.

Рассмотрим LAG() и LEAD():

CurrEmpID CurrEmpName PrevEmpID PrevEmpName PrevPrevEmpID PrevPrevEmpName NextEmpID NextEmpName NextNextEmpID NextNextEmpName
1000 Иванов И.И. NULL NULL NULL not found 1001 Петров П.П. 1002 Сидоров С.С.
1001 Петров П.П. 1000 Иванов И.И. NULL not found 1002 Сидоров С.С. 1003 Андреев А.А.
1002 Сидоров С.С. 1001 Петров П.П. 1000 Иванов И.И. 1003 Андреев А.А. 1004 Николаев Н.Н.
1003 Андреев А.А. 1002 Сидоров С.С. 1001 Петров П.П. 1004 Николаев Н.Н. 1005 Александров А.А.
1004 Николаев Н.Н. 1003 Андреев А.А. 1002 Сидоров С.С. 1005 Александров А.А. 1006 Антонов А.А.
1005 Александров А.А. 1004 Николаев Н.Н. 1003 Андреев А.А. 1006 Антонов А.А. 1007 Максимов М.М.
1006 Антонов А.А. 1005 Александров А.А. 1004 Николаев Н.Н. 1007 Максимов М.М. 1008 Данилов Д.Д.
1007 Максимов М.М. 1006 Антонов А.А. 1005 Александров А.А. 1008 Данилов Д.Д. 1009 Остапов О.О.
1008 Данилов Д.Д. 1007 Максимов М.М. 1006 Антонов А.А. 1009 Остапов О.О. NULL not found
1009 Остапов О.О. 1008 Данилов Д.Д. 1007 Максимов М.М. NULL NULL NULL not found

В данных функциях вторым параметром можно указать сдвиг относительно текущей строки, а третьим параметром можно указать возвращаемое значение для случая если для указанного смещения строки не существует.

Для разбиения данных по группам, попробуйте самостоятельно добавить предложение «PARTITION BY» в OVER, например, «OVER(PARTITION BY emp.DepartmentID ORDER BY emp.ID)».

Рассмотрим FIRST_VALUE() и LAST_VALUE():

CurrEmpID CurrEmpName DepartmentID FirstEmpID FirstEmpName LastEmpID LastEmpName
1005 Александров А.А. NULL 1005 Александров А.А. 1005 Александров А.А.
1000 Иванов И.И. 1 1000 Иванов И.И. 1000 Иванов И.И.
1002 Сидоров С.С. 2 1002 Сидоров С.С. 1002 Сидоров С.С.
1001 Петров П.П. 3 1001 Петров П.П. 1004 Николаев Н.Н.
1003 Андреев А.А. 3 1001 Петров П.П. 1004 Николаев Н.Н.
1004 Николаев Н.Н. 3 1001 Петров П.П. 1004 Николаев Н.Н.
1006 Антонов А.А. 4 1006 Антонов А.А. 1006 Антонов А.А.
1007 Максимов М.М. 5 1007 Максимов М.М. 1009 Остапов О.О.
1008 Данилов Д.Д. 5 1007 Максимов М.М. 1009 Остапов О.О.
1009 Остапов О.О. 5 1007 Максимов М.М. 1009 Остапов О.О.

Думаю, здесь все понятно. Стоит только объяснить, что такое RANGE.

Параметры RANGE и ROWS

При помощи дополнительных параметров «RANGE» и «ROWS», можно изменить область работы функции, которая работает с предложением OVER. У каждой функции по умолчанию используется какая-то своя область действия. Такая область обычно называется окном.

Важное замечание. В разных СУБД для одних и тех же функций область по умолчанию может быть разной, поэтому нужно быть внимательным и смотреть справку конкретной СУБД по каждой отдельной функции.

Можно создавать окна по двум критериям:

  1. по диапазону (RANGE) значений данных
  2. по смещению (ROWS) относительно текущей строки

Общий синтаксис этих опций выглядит следующим образом:
Вариант 1:
<PRECEDING | CURRENT ROW>

Здесь проще понять если проанализировать в Excel результат запроса:

ID Salary Sum1 Sum2 Sum3 Sum4 Sum5 Sum6 Sum7 Sum8
1000 5000.00 19900.00 19900.00 5000.00 19900.00 6000.00 6500.00 5000.00 5000.00
1001 1500.00 19900.00 19900.00 6500.00 14900.00 6000.00 9000.00 6500.00 6500.00
1002 2500.00 19900.00 19900.00 9000.00 13400.00 5500.00 6000.00 9000.00 9000.00
1003 2000.00 19900.00 19900.00 11000.00 10900.00 5300.00 6000.00 11000.00 11000.00
1004 1500.00 19900.00 19900.00 12500.00 8900.00 5000.00 5500.00 7500.00 12500.00
1005 2000.00 19900.00 19900.00 14500.00 7400.00 4200.00 5300.00 8000.00 14500.00
1006 1800.00 19900.00 19900.00 16300.00 5400.00 3600.00 5000.00 7300.00 16300.00
1007 1200.00 19900.00 19900.00 17500.00 3600.00 2400.00 4200.00 6500.00 17500.00
1008 1200.00 19900.00 19900.00 18700.00 2400.00 1200.00 3600.00 6200.00 18700.00
1009 1200.00 19900.00 19900.00 19900.00 1200.00 NULL 2400.00 5400.00 19900.00

С RANGE все тоже самое, только здесь смещения идут не относительно строк, а относительно их значений. Поэтому в данном случае в ORDER BY допустимы значения только типа дата или число.

PositionID Salary Sum1 Sum2 Sum3 Sum4 Sum8
NULL 2000.00 2000.00 19900.00 2000.00 19900.00 2000.00
1 2500.00 2500.00 19900.00 4500.00 17900.00 4500.00
2 5000.00 5000.00 19900.00 9500.00 15400.00 9500.00
3 1500.00 3000.00 19900.00 12500.00 10400.00 12500.00
3 1500.00 3000.00 19900.00 12500.00 10400.00 12500.00
4 2000.00 2000.00 19900.00 14500.00 7400.00 14500.00
10 1800.00 1800.00 19900.00 16300.00 5400.00 16300.00
11 1200.00 3600.00 19900.00 19900.00 3600.00 19900.00
11 1200.00 3600.00 19900.00 19900.00 3600.00 19900.00
11 1200.00 3600.00 19900.00 19900.00 3600.00 19900.00

Заключение

Вот и все, уважаемые читатели, на этом я оканчиваю свой учебник по SQL (DDL, DML).

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

Учитесь, практикуйтесь, добивайтесь получения правильных результатов.

Спасибо за внимание! На этом пока все.

PS. Отдельное спасибо всем, кто помогал сделать данный материал лучше, указывая на опечатки или давая дельные советы!

Организация Web-доступа к базам данных с использованием SQL-запросов

2.2.4. Модификация данных в таблицах SQL.

Особенности и синтаксис предложений модификации

Модификация данных может выполняться с помощью предложений DELETE (удалить), INSERT (вставить) и UPDATE (обновить). Подобно предложению SELECT они могут оперировать как базовыми таблицами, так и представлениями. Однако по ряду причин не все представления являются обновляемыми. Пока зафиксируем этот факт, отложив описание представлений и особенностей их обновления до главы 5, но будем помнить, что термин «представление» относится только к обновляемым представлениям.

Предложение DELETE имеет формат

FROM базовая таблица | представление

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

Предложение INSERT имеет один из следующих форматов:

В первом формате в таблицу вставляется строка со значениями полей, указанными в перечне фразы VALUES (значения), причем i-е значение соответствует i-му столбцу в списке столбцов (столбцы, не указанные в списке, заполняются NULL-значениями). Если в списке VALUES фразы указаны все столбцы модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы, то список столбцов в фразе INTO можно опустить. Однако не советуем этого делать, так как при изменении описания таблицы (перестановка столбцов или изменение их числа) придется переписывать и INSERT предложение.

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

Предложение UPDATE также имеет два формата. Первый из них:

UPDATE (базовая таблица | представление>

SET столбец = значение [, столбец = значение] …

где значение – это

столбец | выражение | константа | переменная

и может включать столбцы лишь из обновляемой таблицы, т.е. значение одного из столбцов модифицируемой таблицы может заменяться на значение ее другого столбца или выражения, содержащего значения нескольких ее столбцов, включая изменяемый.

При отсутствии WHERE фразы обновляются значения указанных столбцов во всех строках модифицируемой таблицы. WHERE фраза позволяет сократить число обновляемых строк, указывая условия их отбора.

Второй формат описывает предложение, позволяющее производить обновление значений модифицируемой таблицы по значениям столбцов из других таблиц. К сожалению в ряде СУБД эти форматы отличаются друг от друга и от стандарта. Для примера приведем один из таких форматов:

SET столбец = значение [, столбец = значение] …

Здесь перечень таблиц фразы FROM содержит имя модифицируемой таблицы и тех таблиц, значения столбцов которых используются для обновления. При этом, естественно, таблицы должны быть связаны между собой в WHERE фразе, которая, кроме того, служит для указания условий отбора обновляемых строк модифицируемой таблицы.

В значениях, находящихся в правых частях равенств фразы SET, следует уточнять имена используемых столбцов, предваряя их именем таблицы (псевдонима).

Вставка единственной записи в таблицу

Добавить в таблицу Блюда блюдо:

Шашлык (БЛ – 34, Блюдо – Шашлык, В – Г, Основа – Мясо, Выход – 150)

при неизвестной пока трудоемкости приготовления этого блюда.

INTO Блюда (БЛ, Блюдо, В, Основа, Выход)

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150);

Создается новая запись для блюда с номером 34, с неопределенным значением в столбце Труд.

Порядок полей в INSERT не обязательно должен совпадать с порядком полей, в котором они определялись при создании таблицы. Вполне допустима и такая версия предыдущего предложения:

INTO Блюда (Основа, В, Блюдо, БЛ, Выход)

VALUES (‘Мясо’, ‘Г’, ‘Шашлык’, 34, 150);

При известной трудоемкости приготовления шашлыка (например, 5 коп) сведения о нем можно ввести с помощью укороченного предложения:

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150, 5);

в котором должен соблюдаться строгий порядок перечисления вводимых значений, так как, не имея перечня загружаемых столб-цов, СУБД может использовать лишь перечень, который определен при создании модифицируемой таблицы.

В предыдущих примерах проводилась модификация стержневой сущности, т.е. таблицы с первичным ключом БЛ. Почти все СУБД имеют механизмы для предотвращения ввода не уникального первичного ключа, например, ввода «Шашлыка» под номером, меньшим 34. А как быть с ассоциациями или другими таблицами, содержащими внешние ключи?

Пусть, например, потребовалось добавить в рецепт блюда Салат летний (БЛ = 1) немного (15 г) лука (ПР = 10), и мы воспользовались предложением

INTO Состав (БЛ, ПР, Вес)

Подобно операции DELETE операция INSERT может нарушить непротиворечивость базы данных. Если не принять специальных мер, то СУБД не проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в таблице Продукты – продукт с первичным ключом ПР = 10. Отсутствие любого из этих значений породит противоречие: в базе появится ссылка на несуществующую запись. Проблемы, возникающие при использовании внешних ключей, подробно рассмотрены в литературе, а здесь отме-тим, что все «приличные» СУБД имеют механизмы для предотв-ращения ввода записей со значениями внешних ключей, отсутст-вующих среди значений соответствующих первичных ключей.

Вставка множества записей

Создать временную таблицу К_меню, содержащую калорийность и стоимость всех блюд, которые можно приготовить из имеющихся продуктов. (Эта таблица будет использоваться шеф-поваром для составления меню на следующий день.)

Для создания описания временной таблицы можно, например, воспользоваться предложением CREATE TABLE

CREATE TABLE К_меню

а для ее загрузки данными – предложение INSERT с вложенным подзапросами:

SELECT Вид, Блюдо,

FROM Блюда, Вид_блюд, Состав, Продукты, Наличие

WHERE Блюда.БЛ = Состав.БЛ

AND Состав.ПР = Продукты.ПР

AND Состав.ПР = Наличие.ПР

AND Блюда.В = Вид_блюд.В

GROUP BY Вид, Блюдо

В этом запросе предложение SELECT выполняется так же, как обычно, но результат не выводится на экран, а копируется в таблицу К_меню. Теперь с этой копией можно работать как с обычной базовой таблицей (Блюда, Про-дукты,…), т.е. выбирать из нее даннные на экран или принтер, обновлять в ней данные и т.п. Никакая из этих операций не будет оказывать влияния на исходные данные (например, изменение в ней названия блюда Салат летний на Салат весенний не приведет к подобному изменению в таблице Блюда, где сохранится старое название). Так как это может привести к противоречиям, то подобные временные таблицы уничтожают после их использования. Поэтому программа, обслуживающая шеф-повара, должна исполнять предложение DROP TABLE К_меню после того, как будет закончено составление меню.

Использование INSERT…SELECT для построения внешнего соединения

Рассмотренное в естественное соединение двух таблиц не включает тех строк какой-либо из них, для которых нет соответствующих строк в другой таблице. Например, если в таблицу Блюда были занесены под номером 34 сведения о Шашлыке, а рецепт его приготовления не был занесен в таблицу Рецепты, то при загрузке их естественного соединения в таблицу Временная:

CREATE TABLE Временная

Рецепт CHAR (560));

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

в ней не окажется строки с Шашлыком (в таблице Рецепты не обнаружен код 34, и строка с этим кодом исключена из результата).

Следовательно, в некотором смысле можно считать, что при обычном соединении теряется информация для таких несоответствующих строк. Однако иногда (как и в приведенном примере) может потребоваться способность сохранить эту информацию. В этом случае можно воспользоваться так называемым внешним соединением:

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

SELECT Вид, Блюдо, «. »

FROM Блюда, Вид_блюд

WHERE Блюда.В = Вид_блюд.В

В результате будет создана базовая таблица

Вид Блюдо Рецепт
Закуска Салат летний Помидоры и яблоки нарезать…
Закуска Салат мясной Вареное охлажденное мясо, …
. . .
Напиток Кофе черный Кофеварку или кастрюлю спо…
Напиток Кофе на молоке Сварить черный кофе, как …
Горячее Шашлык .

где первые 33 строки соответствуют первому INSERT и представляют собой проекцию естественного соединения таблиц Блюда и Рецепты по кодам блюд (БЛ), включающую три столбца. Последняя строка результата соответствует второму INSERT и сохраняет информацию о блюде Шашлык, рецепт котого пока не введен в таблицу Рецепты.

Заметим, что для внешнего соединения нужны два отдельных INSERT…SELECT. Однако тот же результат можно получить и одним INSERT…SELECT, используя фразу UNION, объединяющую предложения SELECT из двух INSERT:

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В


SELECT Вид, Блюдо, «. »

FROM Блюда, Вид_блюд

WHERE Блюда.В = Вид_блюд.В

Обновление единственной записи

Изменить название блюда с кодом БЛ=5 на Форшмак, увеличить его выход на 30 г и установить NULL-значение в столбец Труд.

SET Блюдо = ‘Форшмак’, Выход = (Выход+30), Труд = NULL

Обновление множества записей

Утроить цену всех продуктов таблицы поставки (кроме цены кофе – ПР = 17).

SET Цена = Цена * 3

Обновление с подзапросом

Установить равной нулю цену и К_во продуктов для поставщиков из Паневежиса и Резекне.

SET Цена = 0, К_во = 0

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 отвергнет предложение

позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м месте. Ввод придется осуществить через какую-либо промежуточную таблицу, например, таблицу Выбор:

INTO Выбор (СМ, Т, БЛ)

FROM Выбор;

  1. Составляя предложения модификации данных, необходимо все время помнить о сохранении непротиворечивости базы данных. Об этом упоминалось ранее и подробно говорилось в литературе.

Удаление единственной записи

Удалить поставщика с ПС = 7.

Если таблица Поставки содержит в момент выполнения этого предложения какие-либо поставки для поставщика с ПС = 7, то такое удаление нарушит непротиворечивость базы данных. К сожалению нет операции удаления, одновременно воздействующей на несколько таблиц. Однако в некоторых СУБД реализованы механизмы поддержания целостности, позволяющие отменить некорректное удаление или каскадировать удаление на несколько таблиц.

Удаление множества записей

Удалить все поставки.

Поставки – все еще известная таблица, но в ней теперь нет строк. Для уничтожения таблицы надо выполнить операцию DROP TABLE Поставки.

Удалить все мясные блюда.

DELETE FROM Блюда

WHERE Основа = ‘Мясо’;

Удаление с вложенным подзапросом

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

WHERE Город = ‘Паневежис’);

Вставка единственной записи в таблицу

Добавить в таблицу Блюда блюдо:

Шашлык (БЛ – 34, Блюдо – Шашлык, В – Г, Основа – Мясо, Выход – 150)

при неизвестной пока трудоемкости приготовления этого блюда.

INTO Блюда (БЛ, Блюдо, В, Основа, Выход)

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150);

Создается новая запись для блюда с номером 34, с неопределенным значением в столбце Труд.

Порядок полей в INSERT не обязательно должен совпадать с порядком полей, в котором они определялись при создании таблицы. Вполне допустима и такая версия предыдущего предложения:

INTO Блюда (Основа, В, Блюдо, БЛ, Выход)

VALUES (‘Мясо’, ‘Г’, ‘Шашлык’, 34, 150);

При известной трудоемкости приготовления шашлыка (например, 5 коп) сведения о нем можно ввести с помощью укороченного предложения:

VALUES (34, ‘Шашлык’, ‘Г’, ‘Мясо’, 150, 5);

в котором должен соблюдаться строгий порядок перечисления вводимых значений, так как, не имея перечня загружаемых столб-цов, СУБД может использовать лишь перечень, который определен при создании модифицируемой таблицы.

В предыдущих примерах проводилась модификация стержневой сущности, т.е. таблицы с первичным ключом БЛ. Почти все СУБД имеют механизмы для предотвращения ввода не уникального первичного ключа, например, ввода «Шашлыка» под номером, меньшим 34. А как быть с ассоциациями или другими таблицами, содержащими внешние ключи?

Пусть, например, потребовалось добавить в рецепт блюда Салат летний (БЛ = 1) немного (15 г) лука (ПР = 10), и мы воспользовались предложением

INTO Состав (БЛ, ПР, Вес)

Подобно операции DELETE операция INSERT может нарушить непротиворечивость базы данных. Если не принять специальных мер, то СУБД не проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в таблице Продукты – продукт с первичным ключом ПР = 10. Отсутствие любого из этих значений породит противоречие: в базе появится ссылка на несуществующую запись. Проблемы, возникающие при использовании внешних ключей, подробно рассмотрены в литературе, а здесь отме-тим, что все «приличные» СУБД имеют механизмы для предотв-ращения ввода записей со значениями внешних ключей, отсутст-вующих среди значений соответствующих первичных ключей.

Обновление единственной записи

Изменить название блюда с кодом БЛ=5 на Форшмак, увеличить его выход на 30 г и установить NULL-значение в столбец Труд.

SET Блюдо = ‘Форшмак’, Выход = (Выход+30), Труд = NULL

Обновление множества записей

Утроить цену всех продуктов таблицы поставки (кроме цены кофе – ПР = 17).

SET Цена = Цена * 3

Обновление с подзапросом

Установить равной нулю цену и К_во продуктов для поставщиков из Паневежиса и Резекне.

SET Цена = 0, К_во = 0

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 отвергнет предложение

позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м месте. Ввод придется осуществить через какую-либо промежуточную таблицу, например, таблицу Выбор:

INTO Выбор (СМ, Т, БЛ)

FROM Выбор;

  1. Составляя предложения модификации данных, необходимо все время помнить о сохранении непротиворечивости базы данных. Об этом упоминалось ранее и подробно говорилось в литературе.

Упражнения по SQL

четверг, 23 февраля 2020 г.

Добавить в таблицу PC следующую модель:
code: 20
model: 2111
speed: 950
ram: 512
hd: 60
cd: 52x
price: 1100

INSERT INTO pc (code, model, speed, ram, hd,cd,price)
VALUES(20,2111,950,512,60, ’52x’, 1100)

INSERT INTO product (maker, type, model) VALUES
(‘Z’, ‘Printer’, 4003), (‘Z’, ‘PC’, 4001), (‘Z’, ‘Laptop’, 4002)

Добавить в таблицу PC модель 4444 с кодом 22, имеющую скорость процессора 1200 и цену 1350.

Отсутствующие характеристики должны быть восполнены значениями по умолчанию, принятыми для соответствующих столбцов.

INSERT INTO pc (model, code, speed, price) VALUES
(4444,22,1200,1350)

Для каждой группы блокнотов с одинаковым номером модели добавить запись в таблицу PC со следующими характеристиками:
код: минимальный код блокнота в группе +20;
модель: номер модели блокнота +1000;
скорость: максимальная скорость блокнота в группе;
ram: максимальный объем ram блокнота в группе *2;
hd: максимальный объем hd блокнота в группе *2;
cd: значение по умолчанию;
цена: максимальная цена блокнота в группе, уменьшенная в 1,5 раза.
Замечание. Считать номер модели числом.

insert into pc ( code, model, speed, ram, hd, price )
select min ( code ) + 20,
model + 1000,
max ( speed ),
max ( ram ) * 2,
max ( hd ) * 2,
max ( price ) / 1.5
from laptop group by model

Удалить из таблицы PC компьютеры, имеющие минимальный объем диска или памяти.

DELETE FROM PC WHERE
pc.ram=(select MIN(ram) from pc) or
pc.hd=(select MIN(hd) from pc)

Delete from Laptop where model not in (select model from Product where maker in (select maker from product where type =’Printer’))

Производство принтеров производитель A передал производителю Z. Выполнить соответствующее изменение.

UPDATE Product SET maker = ‘Z’ WHERE type = ‘Printer’ and maker = ‘A’

Удалите из таблицы Ships все корабли, потопленные в сражениях.

DELETE FROM Ships WHERE name In (SELECT ship FROM Outcomes WHERE result=’sunk’)

Добавить в таблицу PC те модели ПК из Product, которые отсутствуют в таблице PC.

При этом модели должны иметь следующие характеристики:

1. Код равен номеру модели плюс максимальный код, который был до вставки.

2. Скорость, объем памяти и диска, а также скорость CD должны иметь максимальные характеристики среди всех имеющихся в таблице PC.

3. Цена должна быть средней среди всех ПК, имевшихся в таблице PC до вставки.

INSERT INTO pc (code, model, speed, ram, hd, cd, price)
SELECT
(SELECT MAX(code) FROM PC) + model AS code,
model,
(SELECT MAX(speed) FROM PC) AS speed,
(SELECT MAX(ram) FROM PC) AS ram,
(SELECT MAX(hd) FROM PC) AS hd,
CAST((SELECT MAX(CAST (SUBSTRING(cd,1,LEN(cd) — 1) AS int)) FROM PC) AS VARCHAR) + ‘x’ AS cd,
(SELECT AVG(price) FROM PC) AS price
FROM product
WHERE type = ‘PC’
AND model not IN (SELECT model FROM pc)

Ответы к экзамену по базам данных. Создание, модификация и уничтожение таблиц. Предложение языка sql create table. Основные типы данных. Предложение alter table. Предложение drop table

Название Создание, модификация и уничтожение таблиц. Предложение языка sql create table. Основные типы данных. Предложение alter table. Предложение drop table
Дата 28.01.2020
Размер 0.53 Mb.
Формат файла
Имя файла Ответы к экзамену по базам данных.docx
Тип Документы
#35327
страница 1 из 6
Подборка по базе: СОП Уничтожение.doc, Создание таблиц.doc, Образец оформления списка использованных источников, сносок, рис, Образец оформления списка использованных источников, сносок, рис, Образец оформления списка использованных источников, сносок, рис, Образец оформления списка использованных источников, сносок, рис, Применение электронных таблиц.doc.

Экзаменационный билет № 1

  1. Создание, модификация и уничтожение таблиц. Предложение языка SQL CREATE TABLE. Основные типы данных. Предложение ALTER TABLE. Предложение DROP TABLE.


Операторы определения данных (Data Definition Language, DDL):

1. CREATE создает объект БД (саму базу, таблицу, представление, пользователя и т.д.)

2. ALTER изменяет объект

3. DROP удаляет объект
Оператор CREATE TABLE

CREATE TABLE базовая_таблица (столбец_1 тип_данных, столбец_2 тип_данных . );

где тип_данных должен принадлежать к одному из типов данных, поддерживаемых СУБД.
Применим DDL для создания в базе данных MyDB таблицы с именем Customer_Data

(Сведения_о_заказчиках). Для создания таблицы применяется оператор CREATE TABLE. Наша таблица-пример будет задана как имеющая четыре колонки, при помощи следующих операторов:

CREATE TABLE Customer_Data (customer_id smallint, first_name char(20), last_name char(20), phone char(10))

GO
CREATE TABLE имя_таблицы(имя_столбца тип_данных [NOT NULL],…

FOREIGN KEY имя_ограничения (имя_столбца,…)

)
Предложение NOT NULL предотвращает занесение в столбец пустых значений. PRIMARY KEY и FOREIGN KEY – столбец или столбцы первичного и внешнего ключей (в определениях столбцов первичного ключа должно быть указано, что они не могут содержать значения NULL) . UNIQUE – условие уникальности данных.
Оператор ALTER TABLE

Оператор ALTER TABLE применяется для изменения определения или атрибутов таблицы.

ALTER TABLE имя_таблицы

Применим для добавления в существующую таблицу Customer_Data колонки middle_initial:

ALTER TABLE Customer_Data

ADD middle_initial char(1)

Теперь определение таблицы содержит не четыре колонки, как было первоначально, а пять колонок.

Оператор DROP TABLE

Оператор DROP TABLE применяется для уничтожения определений таблиц и всех данных, индексов, триггеров, ограничений и специальных разрешений, относящихся к удаляемым таблицам. Для уничтожения нашей таблицы Customer_Data применяется команда:

DROP TABLE Customer_Data

DROP TABLE имя_таблицы [RESTRICT | CASCADE]

Если в операторе указано ключевое слово RESTRICT, то при наличии в базе данных хотя бы одного объекта, существование которого зависит от удаляемой таблицы, выполнение оператора DROP TABLE будет отменено.

Если указано ключевое слово CASCADE, автоматически удаляются и все прочие объекты базы данных, чье существование зависит от удаляемой таблицы, а также другие объекты, зависящие от удаляемых объектов.

  1. Коллективный доступ к данным. Проблемы коллективного доступа.

В СУБД используют протокол доступа к данным, позволяющий избежать проблемы параллелизма. Его суть заключается в следующем:

• транзакция, результатом действия которой на строку данных в таблице является ее извлечение, обязана наложить блокировку чтения на эту строку;

• транзакция, предназначенная для модификации строки данных, накладывает на нее блокировку записи;

• если запрашиваемая блокировка на строку отвергается из-за уже имеющейся блокировки, то транзакция переводится в режим ожидания до тех пор, пока блокировка не будет снята;

• блокировка записи сохраняется вплоть до конца выполнения транзакции.

Если в системе управления базами данных не реализованы механизмы блокирования, то при одновременном чтении и изменении одних и тех же данных несколькими пользователями могут возникнуть следующие проблемы одновременного доступа:

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

• проблема «грязного» чтения возможна в том случае, если пользователь выполняет сложные операции обработки данных, требующие множественного изменения данных перед тем, как они обретут логически верное состояние. Если во время изменения данных другой пользователь будет считывать их, то может оказаться, что он получит логически неверную информацию. Для исключения подобных проблем необходимо производить считывание данных после окончания всех изменений;

• проблема неповторяемого чтения является следствием неоднократного считывания транзакцией одних и тех же данных. Во время выполнения первой транзакции другая может внести в данные изменения, поэтому при повторном чтении первая транзакция получит уже иной набор данных, что приводит к нарушению их целостности или логической несогласованности;

• проблема чтения фантомов появляется после того, как одна транзакция выбирает данные из таблицы, а другая вставляет или удаляет строки до завершения первой. Выбранные из таблицы значения будут некорректны.
Для решения перечисленных проблем в специально разработанном стандарте определены четыре уровня блокирования. Уровень изоляции транзакции определяет, могут ли другие (конкурирующие) транзакции вносить изменения в данные, измененные текущей транзакцией, а также может ли текущая транзакция видеть изменения, произведенные конкурирующими транзакциями, и наоборот. Каждый последующий уровень поддерживает требования предыдущего и налагает дополнительные ограничения:

• уровень 0 – запрещение «загрязнения» данных. Этот уровень требует, чтобы изменять данные могла только одна транзакция; если другойbтранзакции необходимо изменить те же данные, она должна ожидать завершения первой транзакции;

• уровень 1 – запрещение «грязного» чтения. Если транзакция начала изменение данных, то никакая другая транзакция не сможет прочитать их до завершения первой;

• уровень 2 – запрещение неповторяемого чтения. Если транзакция считывает данные, то никакая другая транзакция не сможет их изменить. Таким образом, при повторном чтении они будут находиться в первоначальном состоянии;

• уровень 3 – запрещение фантомов. Если транзакция обращается к данным, то никакая другая транзакция не сможет добавить новые или удалить имеющие строки, которые могут быть считаны при выполнении транзакции. Реализация этого уровня блокирования выполняется путем использования блокировок диапазона ключей. Подобная блокировка накладывается не на конкретные строки таблицы, а на строки, удовлетворяющие определенному логическому условию. Блокировки, используемые уровнями изоляции, подразделяются на:

• разделяемые блокировки (S-locks), которые могут одновременно устанавливаться несколькими пользователями;

• исключительные блокировки (X-locks), которые устанавливаются только одним пользователем, получающим эксклюзивный доступ к данным.

Существуют следующие логические и физические уровни блокировок:

• блокировка на уровне таблицы (table-level locking);

• блокировка на уровне строк (row-level locking);

• блокировка на уровне элемента таблицы (item-level locking);

• блокировка на уровне БД (dbspace-level locking);

• блокировка на уровне табличного пространства (tablespace-level locking); блокировка на уровне страницы или блока (page-level locking).
В большинстве СУБД создается механизм обработки транзакций, при инициировании которого все изменения данных будут рассматриваться как предварительные до тех пор, пока пользователь (реже система) не выдаст предложения:

COMMIT (фиксировать), превращающее все предварительные обновления в окончательные (“зафиксированные”);

ROLLBACK (откат), аннулирующее все предварительные обновления.

  1. Предложение INSERT языка SQL. Вставка единственной записи. Вставка множества записей.

INSERT — осуществляет вставку строк в таблицу.
INSERT INTO имя_таблицы [( )] VALUES ( )
INSERT INTO Customers (cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_ZIP)

VALUES (‘4000000006’, NULL, NULL, ‘Toy Land’, ‘123 Any Street’, ‘New York’, ‘NY’, ‘11111’);
Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL. Так если нам требуется вставить несколько строк при помощи одного оператора INSERT, можно написать:
INSERT INTO Products

SELECT ‘Стул’, 1200

SELECT ‘Стол’, 3000

SELECT ‘Шкаф’, 5000
Эта форма оператора INSERT с параметром SELECT позволяет скопировать множество строк из одной таблицы в другую

Экзаменационный билет № 2

  1. Предложение UPDATE языка SQL. Обновление единственной записи. Обновление множества записей. Обновление с подзапросом. Обновление нескольких таблиц.

Оператор UPDATE применяется для изменения значений в группе записей или в одной записи указанной таблицы.
UPDATE имя_таблицы

SET имя_столбца= [. n]

[WHERE ]
Предложение WHERE является необязательным. Если оно опущено, значения указанных столбцов будут изменены во всех строках таблицы. Если предложение WHERE присутствует, то обновлены будут только те строки, которые удовлетворяют условию отбора.
UPDATE Customers

SET cust_email = ‘kim@thetoystore.com’

WHERE cust_ ;
Обновление нескольких столбцов:
UPDATE Customers

SET cust_contact = ‘Sam Roberts’, cust_email = ‘sam@toyland.com’

WHERE cust_ ;
Или утроить цену всех продуктов таблицы поставки:
UPDATE Поставки

SET Цена = Цена * 3

WHERE ПР <> 17;
Обновление с подзапросом: Установить вес деталей равным 10 для всех поставщиков из Лондона.
UPDATE Детали

WHERE Поставщики.НОМЕР_ПОСТАВЩИКА = Детали. НОМЕР_ПОСТАВЩИКА);
Невозможно обновить более одной таблицы в единственном запросе. Иными словами, в предложении UPDATE должна специфицироваться в точности одна таблица. Поэтому в данном примере мы сталкиваемся со следующей проблемой целостности (точнее, с проблемой целостности по ссылкам): база данных становится противоречивой после выполнения первого предложения UPDATE — она включает теперь некоторые поставки, для которых не имеется соответствующей записи о поставщике, и остается в таком состоянии до тех пор, пока не будет выполнено второе предложение UPDATE. Изменение порядка предложений UPDATE, конечно, не решает эту проблему. Поэтому важно обеспечить выполнение обоих этих предложений, а не только одного.

  1. Предложение DELETE языка SQL. Удаление единственной записи. Удаление множества записей. Удаление с подзапросом.

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

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

[WHERE Условие];
Удаление множества записей: Удалить поставщика с фамилией Адамс.
DELETE

WHERE Фамилия = ‘Адамс’;
Удаление множества записей: Удалить всех поставщиков из Лондона.
DELETE

WHERE Город = ‘Лондон’;
Удаление с подзапросом: Удалить все детали из Лондона для поставщиков.
DELETE

WHERE Поставщики.Номер_Поставщика = Детали.Номер_Поставщика);

  1. Проектирование реляционных БД с использованием нормализации: нормальная форма Бойса-Кодда, четвертая нормальная форма.

Детерминант − любой атрибут, от которого полностью функционально зависит некоторый другой атрибут.
БОЙС-КОДД − Отношение R находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, если каждый детерминант является возможным ключом.

Вариант третьей нормальной формы, в котором требуется, чтобы каждый столбец, от которого зависит другой столбец, сам должен быть уникальным ключом.

Отношение находится в нормальной форме Бойса – Кодда, когда оно находится в третьей нормальной форме и в нём отсутствуют функциональные зависимости атрибутов первичного ключа от неключевых атрибутов.

ЧЕТВЁРТАЯ ФОРМА − Отношение R находится в четвертой нормальной форме (4NF), если в случае существования многозначной зависимости A −> −> B все остальные атрибуты R функционально зависят от A.

Четвёртая нормальная форма запрещает существование многозначных зависимостей между столбцами. Если столбец вместо того, чтобы уникально идентифицировать другой столбец, ограничивает его значения некоторым предопределённым множеством значений – это означает, что между ними существует многозначная зависимость.

Отношение находится в четвёртой нормальной форме если оно находится в нормальной форме Бойса – Кодда и не содержит нетривиальных многозначных зависимостей, равно как и нефункциональных многозначных зависимостей.

Экзаменационный билет № 3

  1. Предложение SELECT языка SQL. Выборка с использованием IN, вложенный оператор SELECT. Подзапрос с несколькими уровнями вложенности. Коррелированный подзапрос.

SELECT — осуществляет выборку данных из таблиц по запросу.
SELECT */

ORDER BY < / >. ]
Оператор IN используется для сравнения некоторого значения со списком заданных значений, при этом проверяется, соответствует ли результат вычисления выражения одному из значений в предоставленном списке. При помощи оператора IN может быть достигнут тот же результат, что и в случае применения оператора OR, однако оператор IN выполняется быстрее.

NOT IN используется для отбора любых значений, кроме тех, которые указаны в представленном списке.
IN – “принадлежит”

Пример. Вывести список клиентов из Москвы или из Самары:

SELECT column_name FROM table_name

WHERE column_name IN (value1,value2. );
SELECT Фамилия, ГородКлиента

WHERE ГородКлиента IN («Москва», «Самара»)
Простые вложенные подзапросы используются для представления множества значений, исследование которых должно осуществляться в каком-либо предикате IN, что иллюстрируется в следующем примере: выдать название и статус поставщиков продукта с номером 11, т.е. помидоров.

SELECT Название, Статус

Подзапрос с несколькими уровнями вложенности можно проиллюстрировать на том же примере. Пусть требуется узнать не поставщиков продукта 11, как это делалось в предыдущих запросах, а поставщиков помидоров, являющихся продуктом с номером 11. Для этого можно дать запрос
SELECT Название, Статус

WHERE Продукт = ‘Помидоры’ ));
Коррелированный подзапрос — это такой подзапрос, результат которого зависит от некоторой переменной. Эта переменная принимает свое значение в некотором внешнем запросе. Обработка такого подзапроса, следовательно, должна повторяться для каждого значения переменной в запросе, а не выполняться раз навсегда.
Выдать название и статус поставщиков продукта с номером 11.
SELECT Название, Статус

WHERE ПС = Поставщики.ПС );

  1. Проектирование реляционных БД с использованием нормализации: первая, вторая и третья нормальные формы.

Нормальная форма — свойство отношения в реляционной модели данных, характеризующее его с точки зрения избыточности, которая потенциально может привести к логически ошибочным результатам выборки или изменения данных. Нормальная форма определяется как совокупность требований, которым должно удовлетворять отношение.

Процесс преобразования базы данных к виду, отвечающему нормальным формам, называется нормализацией. Нормализация предназначена для приведения структуры базы данных к виду, обеспечивающему минимальную избыточность, то есть нормализация не имеет целью уменьшение или увеличение производительности работы или же уменьшение, или увеличение объёма БД. Конечной целью нормализации является уменьшение потенциальной противоречивости хранимой в БД информации.

Цель нормализации — минимизировать повторения данных и возможные структурные изменения БД при процедурах обновления. Это достигается разделением (декомпозицией) одной таблицы в две или несколько с последующим использованием при запросах операции навигации.

Функциональная зависимость — в отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X (r) R.Y.

Множество атрибутов X называется детерминантом функциональной зависимости, а множество атрибутов Y называется зависимой частью.

Функциональная зависимость атрибутов утверждает лишь то, что для каждого конкретного состояния базы данных по значению одного атрибута (детерминанта) можно однозначно определить значение другого атрибута (зависимой части). Но конкретные значение зависимой части могут быть различны в различных состояниях базы данных.

ПЕРВАЯ ФОРМА − Отношение R находится в первой нормальной форме (1НФ), если значения каждого его атрибута являются атомарными, т.е. такими значениями, которые не являются множеством значений или повторяющейся группой.

В определении реляционной модели Кодда все отношения всегда находятся в 1НФ.

Каждый атрибут отношения должен хранить одно-единственное значение и не являться ни списком, ни множеством значений.
ВТОРАЯ ФОРМА − Отношение R находится во второй нормальной форме (2НФ), если никакие не ключевые атрибуты не являются функционально зависимыми лишь от части ключа.

Таким образом, 2НФ может оказаться нарушена только в том случае, когда ключ составной.

Каждый не ключевой столбец в таблице должен уникально идентифицироваться по первичному ключу
ТРЕТЬЯ ФОРМА − Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 2NF и каждый не ключевой атрибут не транзитивно зависит от первичного ключа.

Кроме удовлетворения требованиям второй нормальной формы, каждый не ключевой столбец таблицы должен быть независимым от других не ключевых столбцов

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL