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

1. В контекстном меню папки «Ключи» таблицы Order выберите команду «Создать внешний ключ…».

2. В открывшемся окне «Отношения внешнего ключа» заполните следующие поля:
• (Имя): FK_Order_Customer
• Спецификация таблиц и столбцов: Для заполнения данного блока щелкните на кнопке с многоточием и в появившемся окне «Таблицы и столбцы» в качестве таблицы первичного ключа выберите Customer, а полей связи - IdCust.

3. Закройте все открывшиеся окна с сохранением изменений.

Использование каскадной ссылочной  целостности

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

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

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

 

Задание для самостоятельной работы: Создайте ограничение внешнего ключа FK_OrdItem_Order в таблице OrderItem для связи таблиц Order и OrderItem по полю IdOrd. При этом настройте правило каскадного удаления, установив в качестве параметра «Спецификация INSERT и UPDATE\Правило удаления» значение «Каскадно», что приведет к автоматическому удалению всех товаров из заказа при удалении самого заказа.

Использование диаграмм баз данных

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

1. В контекстном меню папки «Диаграммы базы данных» выберите команду «Создать диаграмму базы данных».
2. В диалоговом окне «Добавление таблиц» выберите все таблицы и нажмите на кнопку «Добавить».
3. Добавив таблицы, щелкните на кнопке «Закрыть» и вы увидите созданную диаграмму базы данных (на рисунке представлен окончательный вид диаграммы: некоторые связи у вас могут отсутствовать).

Используя диаграмму  базы данных ограничения внешнего ключа  можно создавать значительно  быстрее: лишь перетаскивая поля из одной  таблицы в другую. В качестве примера  создадим внешний ключ в таблице  Customer по полю IdCity для связи с таблицей City:

1. Выделите в таблице City поле IdCity и, не отпуская кнопку мыши, перетащите его на поле IdCity таблицы Customer.
2. В диалоговых окнах «Таблицы и столбцы» и «Связь по внешнему ключу» примите настройки по умолчанию.
3. Сохраните диаграмму базы данных под именем ILM.
4. Расположите таблицы в канонической форме (главные таблицы выше подчиненных) в соответствии с вышеприведенным рисунком.

Задание для самостоятельной работы: Аналогичным образом создайте связь между таблицами Product и OrdItem по полю IdProduct. Окончательный список связей между таблицами со всеми их характеристиками представлен в следующей таблице:

Главная таблица	Подчиненная таблица	Поле связи (внешний ключ)	Правила каскадирования
City	Customer	IdCity	Без действия
Customer	Order	IdCust	Без действия
Order	OrdItem	IdOrd	Каскадное удаление
Product	OrdItem	IdProd	Без действия

 

Задание для самостоятельной работы: После настройки всех ограничений можно наполнить таблицы данными. Для этого в контекстном меню таблицы выберите команду «Изменить первые 200 строк» и появившейся в рабочей области вкладке введите новые записи, заполняя все необходимые столбцы. В процессе внесения данных проверьте работоспособность всех созданных ранее ограничений:

• Ограничений проверки: попробуйте ввести в поле Zip (почтовый индекс) таблицы Customer нечисловые значения, а в поля InStock таблицы Product и Qty, Price таблицы OrdItem - отрицательные.
• Значений по умолчанию: убедитесь, что при пропуске полей OrdDate и InStock таблиц Order и Product для них устанавливаются значения по умолчанию в виде текущей системной даты и нуля соответственно.
• Ограничений первичного и уникального ключа: попробуйте ввести в таблицы записи с дублирующими значениями первичного или уникального ключа.
• Ограничений внешнего ключа: попробуйте ввести несогласованные данные в связанные таблицы, например, заказ для несуществующего клиента или удалить запись из любой главной таблицы при наличии связанных записей в подчиненной при отсутствии правил каскадирования.
• Правил каскадирования: убедитесь, что при удалении записи из таблицы Order все связанные записи из таблицы OrdItem удаляются автоматически.

 

Лабораторная  работа №3: Основы Transact SQL: Простые (однотабличные) выборки данных

SQL — это  аббревиатура выражения Structured Query Language (язык структурированных запросов). SQL основывается на реляционной алгебре и специально разработан для взаимодействия с реляционными базами данных.

SQL является, прежде всего, информационно-логическим  языком, предназначенным для описания  хранимых данных, их извлечения и модификации. SQL не является языком программирования. Вместе с тем конкретные реализации языка, как правило, включают различные процедурные расширения.

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

• DDL (Data Definition Language) - операторы определения данных
• DML (Data Manipulation Language) - операторы манипуляции данными
• DCL (Data Control Language) - операторы определения доступа к данным
• TCL (Transaction Control Language) - операторы управления транзакциями

SQL является стандартизированным языком. Стандартный SQL поддерживается комитетом стандартов ANSI (Американский национальный институт стандартов), и соответственно называется ANSI SQL.

Многие разработчики СУБД расширили возможности SQL, введя  в язык дополнительные операторы  или инструкции. Эти расширения необходимы для выполнения дополнительных функций  или для упрощения выполнения определенных операций. И хотя часто  они очень полезны, эти расширения привязаны к определенной СУБД и  редко поддерживаются более чем  одним разработчиком. Все крупные  СУБД и даже те, у которых есть собственные расширения, поддерживают ANSI SQL (в большей или меньшей  степени). Отдельные же реализации носят  собственные имена (PL-SQL, Transact-SQL и т.д.). Transact-SQL (T-SQL) – реализация языка SQL корпорации Microsoft, используемая, в частности, и в SQL Server.

Запросы на выборку данных (оператор SELECT)

SELECT – наиболее часто используемый SQL оператор. Он предназначен для выборки информации из таблиц. Чтобы при помощи оператора SELECT извлечь данные из таблицы, нужно указать как минимум две вещи — что вы хотите выбрать и откуда.

Выборка отдельных столбцов

SELECT [Description]

FROM Product

В приведенном  выше операторе используется оператор SELECT для выборки одного столбца  под названием Description из таблицы Product. Искомое имя столбца указывается сразу после ключевого слова SELECT, а ключевое слово FROM указывает на имя таблицы, из которой выбираются данные.

Для создания и тестирования данного запроса  в Management Studio выполните следующие шаги:

1. В контекстном меню базы Sales выберите команду «Создать запрос» или щелкните соответствующую кнопку на панели инструментов .
2. В открывшемся окне создания нового запроса введите представленные выше инструкции SQL.
3. Для запуска запроса на выполнение щелкните кнопку на панели инструментов или нажмите клавишу F5. В нижней части экрана должны появиться результаты.

4. Management Studio позволяет сохранять пакеты SQL. Это полезно для сохранения сложных запросов, которые будут повторно запускаться в будущем. Для этого щелкните кнопку на панели инструментов. По умолчанию файлы запросов сохраняются с расширением .sql. В дальнейшем сохраненный запрос может быть открыт командой «Открыть файл».

Выборка нескольких столбцов

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

SELECT [Description], InStock

FROM Product

Выборка всех столбцов

Помимо возможности  осуществлять выборку определенных столбцов (одного или нескольких), при  помощи оператора SELECT можно запросить  все столбцы, не перечисляя каждый из них. Для этого вместо имен столбцов вставляется групповой символ “звездочка” (*). Это делается следующим образом.

SELECT *

FROM Product

Сортировка данных

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

SELECT IdProd, [Description], InStock

FROM Product

ORDER BY InStock

Это выражение  идентично предыдущему, за исключением  предложения ORDER BY, которое указывает СУБД отсортировать данные по возрастанию значений столбца InStock.

Сортировка по нескольким столбцам

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

SELECT IdProd, [Description], InStock

FROM Product

ORDER BY InStock, [Description]

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

Указание направления сортировки

В предложении ORDER BY можно также использовать порядок сортировки по убыванию. Для этого необходимо указать ключевое слово DESC. В следующем примере продукция сортируется по количеству в убывающем порядке плюс по названию продукта.

SELECT IdProd, [Description], InStock

FROM Product

ORDER BY InStock DESC, [Description]

Ключевое  слово DESC применяется только к тому столбцу, после которого оно указано. В предыдущем примере ключевое слово DESC было указано для столбца InStock, но не для Description. Таким образом, столбец InStock отсортирован в порядке убывания, а столбец Description в возрастающем порядке (принятым по умолчанию).

Фильтрация данных

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