Главная | Обратная связь | Поможем написать вашу работу!

Использование T-SQL для создания объектов базы данных в SSMS

Лабораторная работа № 3 (4 часа)

Цель работы: изучить использование T-SQL в SSMS для создания объектов базы данных, ввода и изменение данных в таблицах.

Задание. Загрузить среду управления SSMS.

Загрузка SSMS выполняется как в лабораторной работе №1.

Задача 1. Создать сценарий SQL базы данных.

Любую созданную базу данных можно представить с помощью команд T-SQL. Этот процесс называется создание сценария SQL базы данных. Создадим сценарий базы данных «Заказы0».

Для этого

1. В окне проводника объектов (Object Explorer) следует выбрать узел Заказы0 и правой кнопкой «мыши» вызвать контекстное меню этого объекта и выбрать в нем пункт «Tasks» (задачи), а затем - команду «Generate Scripts …» (Сформировать сценарий). В результате будет запушен мастер формирования сценариев.

Для продолжения нажмите кнопку «Next».

2. В следующем окне мастера формирования сценариев следует выбрать базу данных «Заказы0». Если вы хотите, чтобы все объекты базы данных попали в сценарий, нужно установить флажок «Script all objects in the selected database» и нажать кнопку «Finish», которая будет активирован. Если требуется только некоторые объекты базы данных, то следует перейти к следующему этапу создания сценария, сразу нажав кнопку «Next».

3. В следующем окне мастера формирования сценариев (см. нижеприведенный рисунок) предлагается определить целый ряд параметров создания сценария. Эти параметры будут находиться в том состоянии, в котором они установлены в узле T-SQL SQL Server Object Explorer\Scripting меню Tools\Option. В этом окне параметры создания сценария можно изменить.

Установите параметры по указанию преподавателя.

*Для справки

---------------------------------------------------------------------------------------------------------------------------

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

§ Delimit individual statements (Разделить отдельные инструкции). Каждая инструкция T-SQL сопровождается пакетным разделителем Go (batch separator), так что каждая инструкция выполняется в собственном пакете.

§ Include descriptive headers (Включить описательные заголовки). Этот параметр предназначен для помещения в верхнюю часть сценария короткого описательного заголовка. Как правило, он содержит подробную информацию, например, среди прочего, дату и время создания, а также имя объекта.

§ includeVarDecimal. Эта функциональная возможность в следующей версии SQL Server будет недоступна. Данный параметр позволяет определять десятичные типы данных с переменной длиной.

§ Script change tracking (Внести в сценарий отслеживание изменений). Эта новая для SQL Server 2008 возможность отслеживания изменений позволяет определить, какие изменения были выполнены в строках данных. С помощью установки этого параметра сценарий будет компоноваться так, что любая таблица с отслеживанием изменений будет иметь код Т-SQL для выполнения отслеживания изменений.

§ Script for server version (Сценарий для версии сервера). Этот параметр определяет, для какой версии SQL Server предназначен сценарий.

§ Script full-text catalogs (Внести в сценарий полнотекстовые каталоги). Если вы хотите, чтобы созданный сценарий включал каталоги, содержащие подробные полнотекстовые данные, то этот параметр следует установить в значение True.

§ Script USE <database> (Внести в сценарий инструкцию USE <база данных>). Если необходимо, чтобы каждому действию предшествовала инструкция USE, определяющая, для какой базы данных должен выполняться сценарий, установите этот параметр в значение True.

§ Generate script for dependent objects (Внести в сценарий зависимые объекты). При создании сценария можно вписывать в сценарий только некоторые объекты. Используйте этот параметр, если хотите гарантировать, что и выбранные вами объекты, и любые другие объекты, использующие эти объекты, также будут вписаны в сценарий.

§ Include IF NOT EXISTS clause (Включить предложение IF NOT EXISTS). Предложение if not exists проверяет, существует ли объект базы данных, и если он существует, то удаляет перед созданием объекта.

§ Schema qualify object names (Указывать схему в именах объектов). Этот параметр позволяет такое группирование объектов.

§ Script Data Compression Options (Внести в сценарий параметры данных). Данные можно сжать для уменьшения пространства, занимаемого на жестких дисках. Его установка в значение True включит в сценарий любые возможности сжатия.

§ Script extended properties (Внести в сценарий расширенные свойства). Для объектов можно установить расширенные свойства сверх стандартных. Чтобы поместить такие свойства сценарий, установите параметр в значение True.

§ Script permissions (Внести в сценарий разрешения). Этот параметр позволяет задать разрешения для каждого объекта в сценарии. Кроме того, он определяет, кто к какому объекту может получить доступ.

§ Convert user-defined data types (Преобразовать определяемые пользователем типы данных в базовые типы). Определяемые пользователем типы данных компонуются из базовых типов данных. Установка этого параметра в значение True вызовет преобразование любого типа данных, определенного пользователем для таблицы, обратно в базовый тип SQL Server.

§ Generate SET ANSI PADDING commands (Сформировать инструкции SET ANSI PADDING). При включении в сценарий инструкции create table инструкция окружается инструкциями заполнения ANSI. Заполнение ANSI детализирует, как хранятся данные, когда не заполнено пространство, отведенное под этот тип данных.

§ Include collation (Включите, параметры сортировки). Этот параметр позволяет указать, следует ли включать в сценарий сортировку.

§ Include IDENTITY property (Включить свойство IDENTITY). Установка параметра в значение True включит в сценарий определения для свойства identity.

§ Schema qualify foreign key references (Указывать схему в ссылках на внешние ключи). Этот параметр помещает квалификатор схемы в качестве префикса табличных ссылок для внешних ключей.

§ Script bound defaults and rules (Внести в сценарии привязанные правила и значения по умолчанию).Оставим значение false.

§ Script CHECK constraints (Внести в сценарий ограничения CHECK). С помощью ограничения проверки можно определить допустимое множество или диапазон значений для столбца в таблице. Для включения этой возможности в сценарий установите значение True.

§ Script defaults (Внести в сценарий значения по умолчанию) и т.п. другие и параметры. Установка в True гарантирует, что они будут внесены в сценарий.

---------------------------------------------------------------------------------------------------------------------------

Установите параметры и перейдите к следующему этапу создания сценария, нажав кнопку «Next».

4. В следующем окне мастера формирования сценариев предлагается выбрать список объектов, которые нужно включить в сценарий. Выберите все объекты, как показано на нижеприведенном рисунке и нажмите кнопку «Next».

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

6. В окне «Output Option» (параметры ввода) необходимо выбрать место сохранения сценария (см. нижеследующий рисунок).

Выберете сохранение сценария в новом окне редактора запросов и переходите к следующему окну, нажав кнопку «Next».

7. В следующем окне появится сводные данные по создаваемому сценарию, которые можно просмотреть. Если эти данные вы захотите изменить, то нажимайте клавишу «Back» и возвращайтесьв то окно, где эти параметры устанавливались.

8. Нажмите кнопку «Finish». Мастер начнет формировать сценарий и выведет окно с итоговым результатом.

Задание 2. Разберитесь с созданным сценарием.

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

Задание 3. Создайте сценарий на создание базы данных «Заказы0». Для этого,

1. В окне проводника объектов (Object Explorer) выберите узел Заказы0 и, вызвав на нём правой кнопкой «мыши» контекстное меню, выберите пункт «Script DataBase as», далее «CREATE To», далее «New Query Editor Window» (Новое окно редактора запросов). В результате в новом окне редактора запросов будет сформирован сценарий на создание базы данных «Заказы0».

2. Рассмотрите команды, используемые в полученном сценарии.

3. Добавьте сценарий на создание базы данных «Заказы0» в начало предыдущего сценария и сохраните общий сценарий в файле с именем «SQL cоздания Заказы0».

Задача 2. Создать командами T-SQL объекты базы данных «Заказы1», схема которой представленную на следующем рисунке.

Рис. 1 Схема базы данных «Заказы1»

Дополнения к схеме:

1.РК в табл. Заказы, Клиенты, Организации и Склад имеют свойство IDENTITY(1,1)

2. Столбцы «Общая сумма» и «ДатаЗаказа» имеют соответственно значения по умолчанию 0 и текущая дата.

Задание 1. Создать копию базы данных «Заказы0».

Для этого

1. В созданном сценарий базы данных «Заказы0» замените название базы данных «Заказы0» на «Заказы1» и выполните сценарий в редакторе запросов.

2. Сохраните полученный сценарий создания базы данных «Заказы1» в файле.

3. Добавьте в сценарий базы данных «Заказы1» команды на удаление из таблицы «Клиенты» лишних столбцов, а затем добавление нового столбца «ОрганизацииID».

4. Выполните только добавленные команды. В случае возникновения ошибок, исправьте их и повторите выполнение.

5. Добавьте в сценарий команды на создание таблиц «Организации» и «Платежи».

6. Выполните только добавленные команды. В случае возникновения ошибок, исправьте их и повторите выполнение.

7. Добавьте в сценарий команды на создание первичного ключа в таблицах «Организации» и «Платежи».

8. Выполните только добавленные команды. В случае возникновения ошибок, исправьте их и повторите выполнение.

9. Добавьте в сценарий команды на создание связей между таблицами «Организации» и «Клиенты», «Организации» и «Платежи», «Заказы» и «Платежи».

10. Выполните только добавленные команды. В случае возникновения ошибок, исправьте их и повторите выполнение.

11. Добавьте в сценарий команду установки значения по умолчанию для поля «Состояния» таблицы «Заказы», равное «-».

12. Выполните только добавленную команду. В случае возникновения ошибок, исправьте их и повторите выполнение.

13. Сохраните полученный сценарий в файле.

14. Предъявите результаты преподавателю.

Задание 2. Используя команды T-SQL создайте сценарий внесения в таблицы базы данных «Заказы1» следующий информации.

Таблица ОГРАНИЗАЦИИ

1 100100300 CD_Life Брошин И.И. г.Миник, пр-кт Машерова 89, ком 1089 289-56-89 289-40-67

2 100020110 ЗАО "Оптрон" Рыков И.С. г.Могилев, ул. Орловская 54 278-16-19 278-16-00

3 100040111 ОАО "Бестбанк" Прохоров К.П. г.Минск, ул.Плеханова 16

245-78-23 245-70-20

Таблица КЛИЕНТЫ

1 Скорый И.С. 222-30-17 1

2 Сафронов Н.С. 234-30-12 1

3 Боровой А.С. 251-46-87 2

4 Алексеев О.В. 211-26-17 3

5 Макаров Д.С. 221-06-07 3

6 Коновалов Р.Л. 222-30-17 3

Таблица ЗАКАЗЫ

1 1 X000100 2010-01-14 2600 3 2010-01-20 NULL

2 2 X000103 2010-02-04 3200 3 2010-02-19 Оплата налич.

3 3 Z020320 2010-03-24 3600 3 2010-03-29 NULL

4 4 Y900109 2010-01-24 3175 3 2010-02-02 NULL

5 3 NULL 2010-03-20 5600 2 NULL NULL

6 5 NULL 2010-04-05 7300 1 NULL NULL

7 2 NULL 2010-04-02 5000 1 NULL NULL

Таблица КАТАЛОГТОВАРОВ

100 Телевизор

101 Монитор

200 Модуль памяти

500 HD

510 CD-ROM

700 Принтер

Таблица СКЛАД

1 100 Горизонт 700 250 110 1

2 100 Горизонт 800 300 94 1

3 101 Samsung 16 215 138 2

4 101 Viewsonic 17 200 0 2

5 101 LG 19 260 120 3

6 500 HP 3,5gb 160 20 13

7 500 HP 10gb 210 120 13

8 500 LG 5gb 150 120 14

9 700 Canon L111E 150 120 33

10 700 Canon L112E 160 100 33

11 700 HP 800M 100 50 34

Таблица ЗАКАЗАНОТОВАРОВ

1 1 4 250

1 2 10 160

2 6 20 160

3 2 10 160

3 4 10 200

4 7 10 210

4 3 5 215

5 9 20 150

5 10 10 160

5 11 10 100

6 5 20 260

6 7 10 210

7 1 20 250

Таблица ПЛАТЕЖИ

1 1 2010-01-15 20 2600

2 1 2010-02-14 199 5200

3 2 2010-03-26 311 3600

4 3 2010-01-26 24 3000

4 3 2010-01-30 29 175

5 3 2010-03-24 67 5600

Задача 3. Командами T-SQL внести следующие изменения в базу данных «Заказы1».

1. Уменьшить на складе остаток по указанным товарам на заданную величину:

товара с IDСклада=6 на 20 шт.

Мониторов на 10 шт. по каждой спецификации.

2. Увеличить на складе остаток по указанным товарам на указанную величину*:

700 HP 800M на 50 шт.

HD HP 10gb 180*у.е. 100 шт. (тут надо добавить 100шт а отпускную цену пересчитать как ЦенаОтпускнаяНовая=(ЦенаОтпускнаяСтарая*Остаток+100*180)/(Остаток+100)

3. Уменьшить стоимость всех в наличии на складе мониторов на 5% с точностью до целых**.

4. Удалить:

1. Оплату по договору № 5 от 2010-03-24

2. Записи о товарах из каталога товаров, которые не поступали на склад.

3. Клиентов, у которых нет заказов.

5. Сохраните полученный сценарий в файле.

6. Предъявите результаты преподавателю.

* Цена товара на складе рассчитывается как средняя величина стоимости остатков и стоимости добавляемого количества.

**Используйте встроенную функцию Round (…)