79316

Добавление, удаление и изменение данных в таблицах

Лекция

Информатика, кибернетика и программирование

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

Русский

2015-02-10

109 KB

0 чел.

Добавление, удаление и изменение данных в таблицах

Запросы, рассмотренные в предыдущих главах, были направлены на то, чтобы получить данные, содержащиеся в существующих таблицах базы данных. Главным ключевым словом таких запросов на выборку данных является select. Запросы на выборку данных всегда возвращают виртуальную таблицу, которая отсутствует в БД и создается временно лишь для того, чтобы представить выбранные данные пользователю.

При создании и дальнейшем сопровождении БД обычно возникает задача добавления новых и удаления ненужных записей, а также изменения содержимого ячеек таблицы. В SQL для этого предусмотрены операторы insert (вставить), delete (удалить) и update (изменить). Запросы, начинающиеся с этих ключевых слов, не возвращают данные в виде виртуальной таблицы, а изменяют содержимое уже существующих таблиц БД. Запросы на модификацию (добавление, удаление и изменение) данных могут содержать вложенные запросы на выборку данных из той же самой таблицы или из других таблиц, однако сами не могут быть вложены в другие запросы. Таким образом, операторы insert, delete и update
в SQL-выражении могут находиться только в самом начале.

Добавление новых записей

Когда создается таблица базы данных, она не содержит никаких записей, т. е. является пустой. Чтобы наполнить таблицу данными, необходимо добавить (вставить) в нее хотя бы одну запись. Дня этого служит оператор insert (вставить), который имеет несколько форм:

  •  insert into имяТаблицы values  (списокЗначений)  

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

Более надежной и гибкой является следующая форма:

  •  insert into имяТаблицы (списокСтолбцов) values  (списокЗначений) 

вставляет пустую запись в указанную таблицу и вводит в заданные столбцы значения из указанного списка. При этом в первый столбец из списокСтолбцов вводится первое значение из списокЗначений, во второй столбец – второе значение и т. д. Порядок имен столбцов в списке может отличаться от их порядка, заданного при создании таблицы. Столбцы, которые не указаны в списке, заполняются значением null. Иногда требуется просто добавить пустую запись. В этом случае первая форма оператора insert требует после ключевого слова values указать список значений null, длина которого равна количеству столбцов. В данном случае рекомендуется использовать данную форму оператора insert:

  •  insert into имяТаблицы (имяЛюбогоСтолбца)  values  (null)

Следующий запрос добавляет новую запись в таблицу Клиенты, при этом в столбцы Имя, Телефон и Сумма_заказа вводятся значения соответственно 'Петров', '444-4444' и 25300.

insert into Клиенты (Имя, Телефон, Сумма_заказа) values  ('Петров', '444-4444', 25300);

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

insert into Клиенты (Имя, Телефон, Сумма_заказа)

values

('Петров', '444-4444', 25300),

('Иванов', '555-5555', 45100),

('Сидоров', '777-7777', 1200),

('Захаров', '423-9870', 7800);

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

  •  insert into имяТаблицы (списокСтолбцов)   select …

На практике нередко требуется загрузить в одну таблицу данные из другой таблицы. Например, ранее созданная таблица Контакты содержит имена и другие данные о клиентах, которые необходимо вставить в таблицу Клиенты. Для этого в операторе insert вместо values можно использовать оператор select, выбирающий записи, необходимые для вставки.

Допустим, таблицы Клиенты и Контакты имеют одноименные и однотипные столбцы Имя, Адрес и Телефон. Требуется добавить все записи из таблицы Контакты в таблицу Клиенты. Это можно сделать с помощью следующего запроса:

insert into Клиенты (Имя, Адрес, Телефон)  

select Имя, Адрес, Телефон from Контакты;

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

insert into Клиенты (Имя, Адрес, Телефон) 

select Имя, Адрес, Телефон from Контакты  

where Клиенты.Имя < > Контакты.Имя  and

Клиенты.Адрес < > Контакты.Адрес and 

Контакты.Имя is not null;

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

  •  таблица может иметь столбец типа serial (счетчик), имеющий уникальные значения, которые СУБД назначает автоматически. Это обеспечивает уникальность всех записей таблицы и, следовательно, ее соответствие 1-ой нормальной форме. Поэтому в списке столбцов в операторе insert не следует указывать столбцы типа serial;
  •  если список столбцов, указанный в операторе insert, содержит не все столбцы таблицы, то в оставшиеся столбцы (за исключением столбца типа serial) будут введены значения null (неопределенное значение), а в столбец типа serial правильное числовое значение введет СУБД;
  •  при введении значений в столбцы новой записи необходимо следить, чтобы типы значений соответствовали типам столбцов таблицы. При необходимости можно воспользоваться функцией преобразования типов cast ();
  •  таблица может иметь ограничения и если вводимые данные не удовлетворяют условиям этих ограничений, то запрос на добавление данных не будет выполнен. Так, например, если в ограничении на какой-либо столбец указано, что он не может иметь значения null, то попытка добавить пустую запись приведет к ошибке. В этом случае необходимо использовать оператор insert into с ключевым словом values, чтобы сразу ввести значение, удовлетворяющее ограничениям.

Удаление записей

Для удаления записей из таблицы применяется оператор delete (удалить):

delete from имяТаблицы where условие;

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

Следующий запрос удаляет записи из таблицы Клиенты, в которой значение столбца Имя равно 'Иванов':

delete prom Клиенты where Имя = 'Иванов';

Если таблица Клиенты содержит несколько записей, в которых есть клиент Иванов, то все они будут удалены. В операторе where может находиться подзапрос на выборку данных (оператор select). Подзапросы в операторе delete работают точно так же, как и в операторе select.

Пусть в базе данных имеется таблица Продажи (ID_товара, Сумма_заказа, ID_клиента), содержащая сведения о продажах товаров клиентам. Требуется удалить из этой таблицы записи о тех клиентах, которые приобрели товары на сумму, меньшую 1000. Для этого можно использовать такой запрос:

delete from Продажи Т1 where 1000 >=

(select sum(Т2.Сумма_заказа) from Продажи Т2 where T1. ID_клиента=
Т2. ID_клиента);

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

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

  •  Продажи (ID_товара, Сумма_заказа, ID_клиента);
  •  Клиенты (ID_клиента, Имя, Адрес).

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

delete from Клиенты T1 

where 1000  >=

(select sum(Т2.Сумма_заказа) from Продажи Т2 

where T1. ID_клиента= Т2. ID_клиента);

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

select * from Клиенты T1

where 1000  >=

(select sum(Т2.Сумма_заказа) from Продажи Т2 

where T1. ID_клиента= Т2. ID_клиента);

Для удаления всех записей из таблицы достаточно использовать оператор delete без ключевого слова where. При этом сама таблица со всеми определенными в ней столбцами остается и готова для вставки новых записей. Например: delete from Клиенты;

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

Изменение данных

Для изменения значений столбцов таблицы применяется оператор update (изменить, обновить). Чтобы изменить значения в одном столбце таблицы в тех записях, которые удовлетворяют некоторому условию, следует выполнить такой запрос:

update имяТаблицы set имяСтолбца = значение where условие;

За ключевым словом set (установить) следует выражение равенства, в левой части которого указывается имя столбца, а в правой – выражение, значение которого следует сделать значением данного столбца. Эти установки будут выполнены в тех записях, которые удовлетворяют условию в операторе where.

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

update имяТаблицы

set имяСтолбца1 = значение 1, имяСтолбца2 = значение2, ... , имяСтолбцаN = значениеN

where условие;

Например, следующий запрос изменяет значения столбцов Телефон и Сумма_заказа в таблице Клиенты для тех записей, в которых столбец Имя имеет значение 'Иванов':

update Клиенты

set Телефон = '333-1234', Сумма_заказа = 2570 where Имя = 'Иванов';

Использование оператора where в операторе update не обязательно. Если он отсутствует, то указанные в set изменения будут произведены для всех записей таблицы.

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

select * from Клиенты

where Имя = 'Иванов';

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

Условие в операторе where может содержать подзапросы, в том числе и связанные. Пусть в БД имеется таблица Продажи (ID_товара, Сумма_заказа, ID_клиента), содержащая сведения о продажах товаров клиентам. Предположим, что требуется сделать 5% скидку тем клиентам, которые приобрели товары на сумму, большую 1000. Для этого следует изменить значения столбца Cумма_заказа, просто умножить их на 0,95. Однако эти изменения должны быть выполнены, только если сумма значений этого поля для данного клиента превышает 1000. Таким образом, запрос на изменение данных должен содержать связанный подзапрос:

update Продажи Т1

set Сумма_заказа = Сумма_заказа*0.95

where 1000 <

(select SUM(Т2.Сумма_заказа) from Продажи Т2 

where T1.id_клиента = T2.id_клиента);

Здесь запрос на изменение данных из таблицы Продажи содержит связанный подзапрос, вычисляющий сумму значений столбца Сумма_заказа. Обратите внимание на использование двух различных псевдонимов для одной и той же таблицы, При выполнении данного запроса происходит последовательный просмотр записей в таблице Продажи. Для каждой записи проверяется условие оператора where, а именно выполняется подзапрос, вычисляющий сумму значений столбца Сумма_заказа для всех записей, в которых идентификатор клиента равен текущему значению этого идентификатора. Если вычисленное значение превышает 1000, то условие оператора where выполняется и происходит изменение данных в соответствии с выражением set, в противном случае изменения не вносятся. Далее происходит переход к следующей записи, рассматриваемой в качестве текущей, и описанные действия повторяются снова.

Нередко требуется обновить значения столбцов в зависимости от текущих значений. В SQL:2003 для этого можно использовать оператор case.

Допустим, имеется таблица Клиенты (Имя, Адрес, Регион, Телефон). Требуется изменить значения столбца Регион следующим образом: если значение столбца равно 'Северо-запад', то его следует заменить на 'Санкт-Петербург'; если значение равно 'Тверская область' то его следует заменить на 'Тверь'; во всех стальных случаях нужно оставить прежние значения. Эту задачу можно решить с помощью такого запроса:

update Клиенты 

set Регион = case

when Регион = 'Северо-Запад' then 'Санкт-Петербург' 

when Регион = 'Тверская область' then 'Тверь' 

else Регион

end;

Без оператора case данную задачу пришлось бы решать с помощью двух последовательных запросов:

update Клиенты

set Регион = 'Санкт-Петербург' where Регион = 'Северо-Запад';

update Клиенты

set Регион = 'Тверь' where Регион = 'Тверская область';

Допустим, что в таблице Клиенты было решено изменить значение 'Иркутск' столбца Регион. Однако новое значение пока не определено и поэтому вместо 'Иркутск' решили внести значение null. Так обычно поступают, чтобы обозначить тот факт, что значение не известно или еще не введено.

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

update Клиенты set Сумма_заказа = Продажи.Сумма_заказа from Продажи

where id_клиента = 5;

Проверка ссылочной целостности

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

Целостность данных может быть нарушена при попытке добавить запись в дочернюю таблицу, для которой нет соответствующей записи в родительской таблице. Например, в базе данных имеется родительская таблица Клиенты (ИмяКлиента, Имя, Адрес, Телефон) и дочерняя таблица Продажи (ID_заказа, Товар, Цена, Количество, ИмяКлиента). В таблице Клиенты столбец ИмяКлиента является первичным ключом (имеет уникальные определенные значения), а в таблице Продажи столбец ИмяКлиента не обязан иметь уникальные значения, поскольку один и тот же клиент может сделать несколько различных покупок. Чтобы эти две таблицы находились в связи, столбец ИмяКлиента в таблице Продажи должен быть внешним ключом, ссылающимся на первичный ключ ИмяКлиента в таблице Клиенты.

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

Поэтому вначале следует добавить запись в таблицу Клиенты с соответствующим значением столбца ИмяКлиента, а затем – в Продажи или, в случае с удалением, вначале нужно удалить соотсветствующие записи из таблицы Продажи, а затем – из Клиенты.

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

ЗначешеТипаЗаписи row match [unique]   [simple | partial | full]   (подзапрос);

Здесь в квадратных скобках указаны необязательные ключевые слова, а вертикальной чертой разделены их альтернативные варианты: unique (уникальный), simple (простой), partial (частичный), full (полный). Эти ключевые слова определяют правила обработки значений типа записи с полями, имеющими неопределенные значения.

Допустим, требуется определить, есть ли в таблице Продажи запись ('Иванов', 'Компьютер'). Для этого можно выполнить такой запрос:

select * from Продажи

where ('Иванов',  'Компьютер')

match (select ИмяКлиента, Товар from Продажи);

Если в таблице Продажи есть запись с указанным именем клиента и товаром, то предикат match вернет значение true.

PAGE   \* MERGEFORMAT 7


 

А также другие работы, которые могут Вас заинтересовать

4085. Определение коэффициента трения с помощью установки ФПМ-02 и ее компьютерного имитатора 646 KB
  Цель работы: изучить свободные затухающие колебания наклонного маятника освоить методику определения коэффициента трения. Приборы: установка для определения коэффициента трения ФПМ-02, а также IBM-совместимый персональный компьютер и пакет компьют...
4086. Определение энергетических характеристик электрической цепи постоянного тока 5.08 MB
  Цель работы – исследование зависимости энергетических характеристик электрической цепи от внешнего сопротивления. Приборы и принадлежности: лабораторный комплекс ЛКЭ-2П, включающий источник тока, мультиметр, магазин сопротивлений и комплект сое...
4087. Определение изменения энтропии при плавлении олова 127.5 KB
  Цель работы – изучение процессов плавления и кристаллизации олова и определение изменения энтропии. Теплоемкость твердых тел Теплоемкостью твердого тела называется величина, равная отношению количества теплоты Q, погл...
4088. Организационно-правовые формы предприятий 198 KB
  Введение Центральным звеном рыночной экономики, в котором принимаются и осуществляются решения об использовании ограниченного количества благ с учётом обстоятельств внешней среды (которые не могут быть изменены по воле принимающих решения лиц), выбо...
4089. Структуры и алгоритмы обработки данных. Расчет БД Жизнь замечательных людей 352 KB
  Хранящуюся в файле базу данных (БД) Жизнь замечательных людей загрузить в оперативную память компьютера, выполнить сортировку записей методом прямого слияния с использованием очередей. Построить индексный массив, провести поиск...
4090. Автоматизированная Информационная Система учета лекарственных препаратов на базе больничной аптеки 1.2 MB
  Автоматизированная Информационная Система учета лекарственных препаратов на базе больничной аптеки Введение В условиях рыночной экономики информация выступает как один из основных товаров. Успех коммерческой и предпринимательской деятельности связан...
4091. Деньги. Их сущность и функциии 106 KB
  Введение Тема реферата представляет интерес для меня тем, что деньги и банки в совокупности образуют одну из самых важных и притягательных областей экономики. Деньги и банки являются неотъемлемыми атрибутами современной цивилизации. Их изучение так ...
4092. Физические основы современных представлений в теории электромагнитного поля 348 KB
  Физические основы современных представлений в теории электромагнитного поля Рассматриваются базовые физические представления современной теории электромагнитного поля, основанные на концепции «корпускулярно-полевого дуализма» характеристик микрочаст...
4093. Быт и нравы русских женщин в XVI- XVII веках 83 KB
  Быт и нравы русских женщин в XVI- XVII веках Введение Несмотря на то, что уже в X в. (со времен Ольги) Русь узнала и, можно сказать, признала деятельность женщины-правительницы, в отечественной истории не было подобных примеров вплоть до XVIII в.. Н...