15820

Основы Transact SQL: Добавление, изменение и удаление данных

Лекция

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

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

Русский

2013-06-18

63 KB

19 чел.

Основы Transact SQL: Добавление, изменение и удаление данных.

Основы Transact SQL: Добавление, изменение и удаление данных в таблицах

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

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

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

 INSERT INTO имяТаблицы VALUES (списокЗначений)

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

 INSERT INTO имяТаблицы (списокСтолбцов) VALUES (списокЗначений)

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

INSERT INTO City(CityName)

VALUES('Калуга')

Обратите внимание, что столбец IdCity не задается, поскольку он является счетчиком и заполняется СУБД автоматически.

 INSERT INTO имяТаблицы (списокСтолбцов) SELECT ...

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

 
INSERT INTO City(CityName)
SELECT 'Уфа'
UNION
SELECT 'Волгоград'

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

Для удаления записей из таблицы применяется оператор DELETE:DELETE FROM имяТаблицы WHERE условие;

Данный оператор удаляет из указанной таблицы записи (а не отдельные значения столбцов), которые удовлетворяют указанному условию. Условие — это логическое выражение, различные конструкции которого были рассмотрены в предыдущих лабораторных занятиях.
Следующий запрос удаляет записи из таблицы Customer, в которой значение столбца LName равно 'Иванов':

 DELETE FROM Customer
WHERE LName = 'Иванов'

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

 DELETE FROM Customer
WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName = 'Москва')

 Transact-SQL расширяет стандартный SQL, позволяя использовать в инструкции DELETE еще одно предложение FROM. Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк. Оно позволяет задавать данные из второго FROM и удалять соответствующие строки из таблицы в первом предложении FROM. В частности предыдущий запрос может быть переписан следующим образом

 DELETE FROM Customer

FROM Customer k INNER JOIN

City c ON k.IdCity = c.IdCity AND c.CityName = 'Москва' 

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

 SELECT *

FROM Customer k INNER JOIN City c ON k.IdCity = c.IdCity AND c.CityName = 'Москва'

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

 DELETE FROM Product

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

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

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

 UPDATE имяТаблицы SET имяСтолбца = значение WHERE условие;

 За ключевым словом SET (установить) следует выражение равенства, в левой части которого указывается имя столбца, а в правой — выражение, значение которого следует сделать значением данного столбца. Эти установки будут выполнены в тех записях, которые удовлетворяют условию в операторе WHERE.
Чтобы одним оператором UPDATE установить новые значения сразу для нескольких столбцов, вслед за ключевым словом SET записываются соответствующие выражения равенства, разделенные запятыми.

 UPDATE имяТаблицы SET имяСтолбца1 = значение1, имяСтолбца2 = значение2, ... , имяСтолбцаN = значениеN WHERE условие;

 Например, следующий запрос изменяет фамилию и имя клиента с кодом 5.

 UPDATE Customer

SET FName='Иван', LName='Иванов' WHERE IdCust = 5

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

 Так же как и в инструкции DELETE условие в операторе WHERE инструкции UPDATE может содержать подзапросы, в том числе и связанные. Transact-SQL расширяет стандартный SQL, позволяя использовать в инструкции UPDATE предложение FROM (по аналогии с DELETE). Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания обновляемых строк.

 Если обновляемый объект тот же самый, что и объект в предложении FROM, и в предложении FROM имеется только одна ссылка на этот объект, псевдоним объекта указывать необязательно. Если обновляемый объект встречается в предложении FROM несколько раз, одна и только одна ссылка на этот объект не должна указывать псевдоним таблицы. Все остальные ссылки на объект в предложении FROM должны включать псевдоним объекта.
Предположим, что требуется сделать 5% скидку по тем заказам клиентов, суммарная стоимость которых превышает 1000. Для этого следует изменить значения столбца Price, просто умножить их на 0,95. Однако эти изменения должны быть выполнены, только если суммарная стоимость заказа превышает 1000. Таким образом, в качестве критерия обновления записей в таблице OrdItem может быть задан запрос возвращающий список всех заказов с суммарной стоимостью более 1000.

 UPDATE OrdItem
SET Price = Price * 0.95
FROM OrdItem o INNER JOIN
(
SELECT IdOrd
FROM OrdItem
GROUP BY IdOrd
HAVING SUM(Qty*Price) > 1000) a ON o.IdOrd = a.IdOrd

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

 SELECT *
FROM OrdItem o INNER JOIN
(
SELECT IdOrd
FROM OrdItem
GROUP BY IdOrd
HAVING SUM(Qty*Price) > 1000) a ON o.IdOrd = a.IdOrd

 Задание для самостоятельной работы: Сформулируйте на языке SQL запрос имитирующий поступление на склад новой партий определенного товара (Обновление столбца InStock в таблице Product).


 

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

55449. Психогімнастика 53 KB
  На цьому етапі найдоречнішими є групові завдання які допоможуть зняти скутість або надмірне збудження налаштувати дітей на заняття. Мета цього етапу психогімнастики корекція настрою та поведінки дітей тренінг змодельованих стандартних ситуацій.
55452. Психология будущего 2.03 MB
  Холотропные переживания такого рода являются базовым источником существования космологических мифологических философских и религиозных систем описывающих духовную природу и...
55455. ПСИХОЛОГИЧЕСКИЙ АНАЛИЗ УРОКА 85.5 KB
  Важнейшей составляющей учительского профессионализма как и вообще уровня развития личности является высокий уровень развития педагогической рефлексии то есть умения по ходу урока анализировать конкретную обстановку...
55457. Психологический анализ урока. Методическое пособие 212.5 KB
  Целенаправленная организация учителем деятельности учащихся на уроке обеспечивает значимые изменения всех сфер их психической жизни. Опираясь на общую структуру деятельности А. Леонтьев и на структуру учебной деятельности...