Создание баз данных

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

Для вставки  записей в таблицу используется оператор 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).

 

Лабораторная  работа №6: Представления

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

Механизм  представлений может использоваться по нескольким причинам.

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

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

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

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

Создание представлений  в Management Studio

В утилите SQL Server Management Studio представления можно создавать, редактировать, выполнять и вставлять в другие запросы.

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

В утилите  Management Studio представления перечислены в собственном узле в каждой базе данных.

Команда «Создать представление» в контекстном меню позволит запустить конструктор  запросов в режиме создания представлений.

Конструктор запросов утилиты Management Studio способен одновременно отображать множество панелей, выбранных на панели инструментов .

• Панель диаграммы (Область схемы). В запросе может участвовать множество таблиц и представлений. Для связывания их отношениями с целью формирования предложения FROM инструкции SELECT можно использовать это графическое представление.
• Панель сетки (Область условий). На этой панели перечисляются отображаемые, фильтруемые и сортируемые столбцы.
• Панель SQL. На этой панели можно в текстовом виде увидеть и отредактировать формируемую инструкцию SELECT.
• Панель результатов. Когда запрос выполняется с помощью действия «Выполнить код SQL» , на этой панели отображаются его результаты. Если результаты запроса остаются нетронутыми долгое время, Management Studio запрашивает у пользователя разрешение закрыть подключение к серверу.

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

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

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

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