Практические. Практические работы по скл Сервер. Лабораторная работа 30 Запросы на редактирование и удаление данных Создание базы данных в ms server
 Скачать 0.53 Mb.
|
Лабораторная работа №30Запросы на редактирование и удаление данныхСоздание базы данных в MSServerЦель работы: научиться создавать реляционную базу данных в системе управления базами данных MS SQL Express.Ход выполнения работы 1. Изучите теоретическую часть 2. Выполните практическое задание 3. Оформите отчет о выполнении лабораторной работы 4. Ответьте на контрольные вопросы Теоретическая часть Утилита SQL Server Management Studio. Подавляющую массу задач администрирования SQL Server можно выполнить в графической утилите SQL Server Management Studio. В ней можно создавать базы данных и все ассоциированные с ними объекты (таблицы, представления, хранимые процедуры), можно выполнить последовательности инструкций Transact-SQL(запросы). В этой утилите можно выполнить типовые задачи обслуживания баз данных, такие как резервирование и восстановление, настраивать систему безопасности базы данных и сервера, просматривать журнал ошибок и многое другое. Для запуска Management Studio в меню «Пуск» операционной системы выберите пункт «Microsoft SQL Server 2008 R2/Среда SQL Server Management Studio». Когда откроется окно программы, вас попросят подключиться к какому либо серверу баз данных SQL Server. Подключение к серверу. В окне «Соединение с сервером» необходимо указать следующую информацию: Тип сервера. Здесь следует выбрать, к какой именно службе необходимо подключится. Имя сервера. Позволяет указать, к какому серверу будет осуществляться подключение. По умолчанию имя SQL Server совпадает с именем компьютера. Проверка подлинности. Способ аутентификации, можно выбрать «Проверка подлинности Windows» или «Проверка подлинности SQLServer». Первый способ использует учетную запись, под которой текущий пользователь осуществил вход в Windows. Вариант SQL Server использует свою собственную систему безопасности.  или  После подключения экземпляр сервера будет отображаться на панели «Обозреватель объектов».  Окно Management Studio имеет следующую структуру: Оконное меню – содержит полный набор команд для управления сервером и выполнения различных операций. Панель инструментов – содержит кнопки для выполнения наиболее часто производимых операций. Внешний вид данной панели зависит от выполняемой операции. Панель «Обозреватель объектов». Это панель с древовидной структурой, отображающая все объекты сервера, а также позволяющая производить различные операции, как с самим сервером, так и с его базами данных и их объектами. Обозреватель объектов является основным инструментом для разработки. Рабочая область. В рабочей области производятся все действия с базой данных, а также отображается её содержимое. Прежде чем перейти к созданию своих собственных рабочих баз данных рассмотрим служебные базы данных SQL Server, которые создаются автоматически в процессе его установки. Если мы раскроем узел «Базы данных – Системные базы данных» в обозревателе объектов, то увидим следующий набор служебных баз данных: master. Главная служебная база данных всего сервера. В ней хранится общая служебная информация сервера: настройки его работы, список баз данных на сервере с информацией о настройках каждой базы данных и ее файлах, информация об учетных записях пользователей, серверных ролях. msdb. Эта база данных в основном используется для хранения информации службы SQL Server Agent, но в нее записывается и другая служебная информация (например, история резервного копирования). model. Эта база данных является шаблоном для создания новых баз данных в SQL Server. Если внести в нее изменения, например, создать набор таблиц, то эти таблицы будут присутствовать во всех создаваемых базах данных. tempdb. Эта база данных предназначена для временных таблиц и хранимых процедур, создаваемых пользователями и самим SQL Server. Эта база данных создается заново при каждом запуске SQL Server. Создание пользовательских баз данных. База данных представляет собой группу файлов, хранящихся на жестком диске. Эти файлы могут относиться к трем типам: файлы с первичными данными, файлы с вторичными данными и файлы журнала транзакций. Любая база данных SQL Server содержит, по крайней мере, два файла: первичный файл данных (с расширением .mdf) и файл журнала транзакций (с расширением .ldf). Существует два способа их создания: графически с помощью SQL Server Management Studio посредством кода Transact-SQL Создание баз данных с помощью Transact-SQL. Для программного создания базы данных (например, в программе установки приложения) используется инструкция CREATE DATABASE языка T-SQL (сокращенная форма от Transact-SQL). Данная инструкция может включать в себя множество опций, определяющих различные параметры новой базы данных. Сценарий создания новой базы данных может быть сгенерирован на основе уже существующей базы данных. Для этого в SQL Server Management Studio в контекстном меню узла базы данных выберите команду «Создать сценарий для базы данных – Используя CREATE– Буфер обмена». В результате в буфер обмена будет сохранен текст запроса на создание новой базы данных с параметрами, указанными при создании базы данных. Рассмотрим основные опции инструкции CREATEDATABASE: Имя новой базы данных. Указывается непосредственно после ключевого слова CREATE DATABASE. ON. Это опция указывает на файловую группу, которая представляет собой логическую группу вторичных файлов данных и используется для управления размещением пользовательских объектов (таких как таблицы и индексы). Опция PRIMARY после аргумента ON используется для указания группы файлов PRIMARY, в которую по умолчанию входят все созданные файлы, и которая является единственной группой файлов, содержащей первичный файл данных. NAME. Логическое имя базы данных, которое будет применяться для ссылки на нее из кодаT-SQL. FILENAME. Это имя и путь файла базы данных, хранящегося на жестком диске. SIZE. Исходный размер файлов данных. MAXSIZE. Максимальный размер, до которого может расти база данных. FILEGROWTH. Это приращение расширения файла Параметры в разделе LOG ON аналогичны параметрам в разделе CREATE DATABASE. Однако они определяют параметры файла журнала транзакций. Общий синтаксис инструкции CREATE DATABASE со всеми возможными опциями можно посмотреть в справочной системе. Для этого в редакторе запросов выделите слова CREATE DATABASEи нажмите клавишуF1. Создание таблиц. Таблицы представляют собой объекты базы данных, используемые непосредственно для хранения всех данных. Одним из самых главных правил организации баз данных является то, что в одной таблице должны храниться данные лишь об одном конкретном типе сущности. Данные в таблицах организованы по полям и записям. Поля (или столбцы таблицы) содержат определенный тип информации, например, фамилию, адрес, телефонный номер. Запись (или строка таблицы) ‑ группа связанных полей, содержащих информацию об отдельном экземпляре сущности. Любое поле таблицы характеризуется как минимум тремя обязательными свойствами: Имя столбца. Реализует способ обращения к конкретному полю в таблице. Рекомендуется всегда присваивать полям смысловые имена. Тип данных. Определяет, информация какого типа может храниться в данном поле. Разрешить значения null. Определяет, допустимо ли для данного поля отсутствие фактических данных, для обозначения которого используется так называемый маркер пустого значения null. Типы данных. При выборе типа данных для столбца следует отдавать предпочтение типу, который позволит хранить любые возможные для этого столбца значения и занимать при этом минимальное место на диске. Типы данных в MS SQL Server можно разделить на восемь категорий: Целочисленные данные bit (1 байт). Может хранить только значения 0, 1 или null(пустое значение, сообщающее об отсутствии данных). Его удобно использовать в качестве индикатора состояния – включено/выключено, да/нет, истина/ложь. tinyint(1 байт). Целые значения от 0 до 255. smallint(2 байта). Диапазон значений от -215(-32768) до 215(3767). int (4 байта). Может содержать целочисленные данные от -231(-2147483648) до 231(21474833647). bigint(8 байт). Включает в себя данные от -263(9223372036854775808) до 263(9223372036854775807). Удобен для хранения очень больших чисел, не помещающихся в типе данныхint. Текстовые данные char. Содержит символьные неUnicode-данные фиксированной длины до 8000 знаков. varchar. Содержит символьные неUnicode-данные переменной длины до 8000 знаков. nchar. Содержит данныеUnicodeфиксированной длины до 4000 символов. Подобно всем типам данныхUnicodeего удобно использовать для хранения небольших фрагментов текста, которые будут считываться разноязычными клиентами. nvarchar. Содержит данныеUnicodeпеременной длины до 4000 символов. Десятичные данные decimal. Содержит числа с фиксированной точностью от -1038-1 до 1038-1. Он использует два параметра: точность и степень. Точностью называется общее количество знаков, хранящееся в поле, а степень – это количество знаков справа от десятичной запятой. numeric. Это синоним типа данныхdecimal– они идентичны. Денежные типы данных money(8 байт). Содержит денежные значения от -263до 263с десятичной точностью от денежной единицы. Удобен для хранения денежных сумм, превышающих 214768,3647. smallmoney(4 байта). Содержит значения от -214748,3648 до 214748,3647 с десятичной точностью. Данные с плавающей точкой float. Содержит числа с плавающей запятой от -1,79Е+38 до 1,79Е+38. real. Содержит числа с плавающей запятой от -3,40Е+38 до 3,40Е+38. Типы данных даты и времени datetime(8 байт). Содержит дату и время в диапазоне от 1 января 1753 года до 31 декабря 9999 года с точностью 3,33 мс. smalldatetime(4 байта). Содержит дату и время, начиная от 1 января 1900 года и заканчивая 6 июнем 2079, с точностью до 1 минуты. Двоичные типы данных binary. Содержит двоичные данные фиксированной длины до 8000 байт. varbinary. Содержит двоичные данные переменной длины до 8000 байт. Специализированные типы данных sql_variant. Используется для хранения значения с различными типами данных. timestamp. Используется для установки временных меток записей при вставке, которые соответствующим образом обновляются. Удобен для отслеживания изменений в данных. uniqueidentifier. Глобальный уникальный идентификатор. xml. Используется для хранения целых документов или фрагментов XML. Ограничения Перед тем как начать работать с таблицами следует ограничить вводимые в них данные в целях обеспечения так называемой целостности данных, т. е. ограничить возникновение в базе данных некорректных или противоречивых данных вследствие добавления, изменения или удаления какой-либо записи, например, ввод отрицательной цены или количества товара. Существует четыре типа целостности данных: доменная, сущностная, ссылочная и пользовательская (или бизнес-правила). Рассмотрим основные инструменты, предоставляемые в SQL Server для их реализации. Обеспечение доменной целостности. Ограничение диапазона данных, вводимых пользователем в поле. Основными инструментами обеспечения доменной целостности являются ограничения проверки и значения по умолчанию. Использование проверочных ограничений. Ограничения на проверку используются для ограничения данных, принимаемых полем, даже если они имеют корректный тип. Например, поле Почтовый индекс имеет тип nchar (5), т.е. чисто теоретически оно может принимать буквы. Это может стать проблемой, поскольку не существует почтовых индексов с буквами. Обеспечение сущностной целостности. Обеспечение гарантии уникальности записей в таблицах и предотвращение их дублирования. Основными инструментами обеспечения целостности сущностей являются первичные ключи и ограничения уникальности. Создание первичных ключей. Первичный ключ используется для обеспечения гарантии уникальности каждой записи в таблице. Он состоит из одного (простой ключ) или нескольких (составной ключ) столбцов с гарантированно уникальными значениями для каждой записи таблицы. Если пользователь попытается ввести в поля первичного ключа дублирующее значение будет сгенерирована ошибка и модификация данных будет отменена. Использование ограничений на уникальность. Между ограничениями первичного ключа и ограничениями на уникальность существует два отличия. Первое состоит в том, что первичные ключи используются вместе с внешними ключами для обеспечения целостности ссылок (рассматривается в следующем разделе). Второе отличие заключается в том, что ограничения на уникальность позволяют вставлять в его поля пустые значения (null), чего нельзя делать с первичными ключами. Во всем остальном они служат одной цели – обеспечить уникальность данных, вставляемых в поле. Ограничение на уникальность следует использовать в тех случаях, когда нужно гарантировать, что дублирующие значения не будут добавляться в поле, не являющееся частью первичного ключа, в частности, все потенциальные ключи должны быть организованы в виде ограничений уникальности. Хорошим примером такого поля, требующего ограничение на уникальность, является поле ИНН или серия и номер паспорта, поскольку эти поля должны быть уникальными у каждого человека. Практическая часть Задание 1. Запустите утилиту SQL Server Management Studio (Пуск/Все программы/ MS SQL Server 2008R2). Настройте подключение к серверу: компонентDatabaseEngine локальный компьютер (localhost\SQLEXPRESS) проверки подлинностиWindows Перечислите имена сервера, которые предлагает. Какое имя использовали вы? Задание 2. Перечислите команды Меню открывшегося окна SQL Server Management Studio Задание 3. Вызовите команду Вид. Запишите назначение пиктограмм  ,  ,  ,  ,  .Задание 4. Вызовите команду Вид/Панели инструментов. Перечислите предлагаемые панели инструментов. Задание 5. Создайте базу данных ОборотКомпанииФамилия (вместо слова Фамилия добавьте вашу фамилию) с помощью графической утилиты SQL Server Management Studio. Для этого: В окне «Обозреватель объектов» найдите и раскройте папку «Базы данных». Щелкните на ней правой кнопкой мыши и выберите команду «Создать базу данных…». В открывшемся диалоговом окне «Создание базы данных» на странице «Общие» введите следующую информацию: Имя базы данных: ОборотКомпанииФамилия Владелец: sa В таблице «Файлы базы данных» измените путь к файлам данных и журнала на каталог С:\Temp.  Для всех остальных параметров оставьте значения по умолчанию. Для создания базы данных щелкните «OK». Вы должны увидеть свою новую базу данных в окне «Обозреватель объектов». Зайдите в каталог С:\Temp, найдите два файла созданной вами базы данных:  Задание 6. Создайте базу данных ОборотКомпанииФамилия (вместо слова Фамилия добавьте вашу фамилию) с помощью SQL команды CREATE. Для этого: Cгенерируйте сценарий базы данных на основе уже существующей базы данных. Для этого в SQL Server Management Studio в контекстном меню узла «ОборотКомпанииФамилия»  Выберите команду «Создать сценарий для базы данных – Используя CREATE– Буфер обмена». В результате в буфер обмена будет сохранен текст запроса на создание новой базы данных с параметрами, указанными при создании базы данных в Management Studio. Для проверки работоспособности сгенерированного запроса на создание базы данных удалите созданную вами базу данных. В контекстном меню базы данных выберите команду «Удалить» и в появившемся диалоговом окне нажмите кнопку «OK». База данных со всеми файлами должна исчезнуть. Чтобы воспользоваться сгенерированным заранее запросом на создание базы данных выполните следующие шаги: На панели инструментоввыберите команду «Создать запрос»  .В открывшемся окне редактора SQL вставьте из буфера обмена (Ctrl+V) сгенерированный запрос. Для запуска запроса на выполнение щелкните кнопку  на панели инструментов или нажмите клавишу F5.Обновите содержимое дерева обозревателя объектов командой «Обновить» из контекстного меню узла «Базы данных». База данных ОборотКомпанииФамилия должна вновь появиться в списке доступных.  Задание 7. Cоздайте пользовательский тип данных phone, который будет использоваться в таблице Customer для хранения телефонного номера клиента. Для его создания воспользуйтесь утилитой Management Studio. В дереве обозревателя объектов раскройте папки «Базы данных – ОборотКомпанииФамилия – Программирование - Типы». В контекстном меню узла «Определяемые пользователем типы данных» выберите команду «Создать определяемый пользователем тип данных». В появившемся окне в текстовом поле «Имя» введите phone. В раскрывающемся списке «Тип данных» выберите nchar. В качестве длины введите 10. Отметьте параметр «Разрешить значения null», чтобы иметь возможность не указывать телефонный номер при добавлении нового клиента. В секции «Привязки» оставьте пустые значения и щелкните на кнопке Ok. Созданный пользовательский тип данных должен появиться в дереве обозревателя объектов.  Задание 8. Создайте в базе данных пять таблицы. Первая таблица, Customer, будет хранить информацию о клиентах, вторая таблица City– справочник городов, третья, Product, ‑ информацию о товарах, четвертая, Order, будет содержать подробную информацию о заказах и пятая, OrdItem, - о составе заказа (перечне товаров входящих в заказ). Ниже представлены все поля этих таблиц и их основные свойства.
Таблицы можно создавать как в графическом интерфейсе (в утилите Management Studio), так и с помощью кодаT-SQL. Воспользуемся самым простым, графическим способом. Сначала создадим таблицу Customer: В дереве обозревателя объектов в базе данных в контекстном меню узла «Таблицы» выберите команду «Создать таблицу…». В рабочей области должна появиться вкладка с конструктором таблиц. В первую строку в столбце «Имя столбца» введите IdCust, в столбце «Тип данных» выберите int. Убедитесь что параметр «Разрешить значения null» отключен. В нижней половине экрана в разделе «Свойства столбцов» введите описание поля и измените значение параметра «Спецификация идентификатора / (Идентификатор)» на «Да» для того чтобы значения номера клиента формировались автоматически. Свойство «Идентифицирующий столбец» (Identity), обычно используемое совместно с типом данных int, предназначено для автоматического приращения значения на единицу при добавлении каждой новой записи. К примеру, клиент, добавленный в таблицу первым, будет иметь значение идентификатора 1, вторым – 2, третьим – 3, и т.д. Аналогичным образом введите описания всех остальных полей и закройте окно конструктора таблиц. Введите в качестве имени таблицы Customer. Вновь созданная таблица должна появиться в дереве обозревателя объектов в папке «Таблицы».  В соответствие с вышеприведенным описанием создайте оставшиеся четыре таблицы: City, Product, Order и OrdItem. Задание 9. В Обозревателе объектов найдите созданную вами на прошлой лабораторной работе базу данных ОборотКомпанииФамилия.mdf, которая должна состоять из пяти таблиц: Customer, City, Product, Order, OrdItem. Создайте сценарий для первой таблицы, используя команду Create, в буфер обмена и в новое окно редактора запросов. Вставьте созданный сценарий в отчет по лабораторной работе. Задание 10. Создайте ограничения для поля Zip (почтовый индекс) таблицы Customer, запрещающее вводить в поле буквы. В контекстном меню папки «Ограничения» таблицы Customer выберите команду «Создать ограничение». В открывшемся окне «Проверочные ограничения» заполните следующие поля: Имя: CK_Zip Выражение: ([zip] like '[0-9][0-9][0-9][0-9][0-9]'). Данное выражение описывает ограничение, принимающее пять символов, которыми могут быть только цифры от 0 до 9. Описание: Ограничение на значения почтового индекса  Задание 11. Создайте ограничения для полей InStock таблицы Product, Price таблицы OrdItem, запрещающие ввод в них отрицательных значений. В данном случае выражение проверки будет иметь вид (Имя поля > 0) для поля Price и (Instock>=0) для столбца InStock. Задание 12. Создайте ограничения Использование значений по умолчанию. Установка для полей значений по умолчанию это отличный способ избавить пользователя от излишней работы, если значения этих полей во всех записях, как правило, принимают одни и те же значения. Так в таблице заказов Order вполне логично определить по умолчанию значение поля OrdDate (дата заказа) в виде текущей даты. В этом случае при добавлении записи о новом заказе в случае пропуска этого поля оно будет автоматически заполняться значением системной даты. Для создания такого свойства выполните следующие шаги: Раскройте папку «Столбцы» таблицы Order и в контекстном меню поля «OrdDate» выберите команду «Изменить». В свойстве столбца «Значение или привязка по умолчанию» введите getdate(). Эта функция T-SQL возвращает текущую системную дату. Щелкните на кнопке Сохранить и выйдите из конструктора таблиц.  Задание 13. Установите для поля InStock (количество единиц продукта на складе) таблицы Product в качестве значения по умолчанию ноль. Задание 14. Создайте первичный ключ для таблицы Customer ‑ столбец IdCust. Для создания первичного ключа в таблице Customer выполните следующие шаги: В контекстном меню таблицы Customer выберите команду «Проект». В окне конструктора таблиц щелкните правой кнопкой мыши на поле IdCust и выберите команду «Задать первичный ключ» или нажмите кнопку  на панели инструментов. Обратите внимание на то, что слева от поля IdCustтеперь отображается значок ключа, указывающий, что поле является первичным ключом.Закройте конструктор таблиц с сохранением изменений  Задание 15. Аналогичным образом создайте первичные ключи для остальных таблиц в соответствие с ниже приведенной таблицей.
Задание 16. Создайте ограничения на уникальность в таблице Customer по полю Phone, которое повторяться у разных клиентов не должно. Для открытия конструктора таблиц в контекстном меню таблицы Customer выберите команду «Проект». На панели инструментов нажмите на кнопку «Управление индексами и ключами»  .В открывшемся окне «Индексы и ключи» щелкните кнопку «Добавить» и введите следующие параметры для нового уникального ключа: Столбцы: Phone Тип: Уникальный ключ (Имя): CK_Phone  Закройте конструктор таблиц с сохранением изменений. Задание 17. Аналогичным образом создайте ограничение уникальности по полю CityName таблицы City, чтобы обеспечить отсутствие в справочнике городов с одинаковыми названиями, а также по полю Description таблицы Product, чтобы иметь возможность отличить один товар от другого. Задание 18. Создайте диаграмму базы данных: В контекстном меню папки «Диаграммы базы данных» выберите команду «Создать диаграмму базы данных». В диалоговом окне «Добавление таблиц» выберите все таблицы и нажмите на кнопку «Добавить». Добавив таблицы, щелкните на кнопке «Закрыть» и вы увидите созданную диаграмму базы данных (на рисунке представлен окончательный вид диаграммы: некоторые связи у вас могут отсутствовать).  Используя диаграмму базы данных ограничения внешнего ключа можно создавать значительно быстрее: лишь перетаскивая поля из одной таблицы в другую. В качестве примера создадим внешний ключ в таблице Customer по полю IdCity для связи с таблицей City: Выделите в таблице City поле IdCity и, не отпуская кнопку мыши, перетащите его на поле IdCity таблицы Customer. В диалоговых окнах «Таблицы и столбцы» и «Связь по внешнему ключу» примите настройки по умолчанию. Сохраните диаграмму базы данных под именем ILM. Расположите таблицы в канонической форме (главные таблицы выше подчиненных) в соответствии с вышеприведенным рисунком. Задание 19. Аналогичным образом создайте связь между таблицами Product и OrdItem по полю IdProduct. Окончательный список связей между таблицами со всеми их характеристиками представлен в следующей таблице:
Задание 20. После настройки всех ограничений можно наполнить таблицы данными. Для этого в контекстном меню таблицы выберите команду «Изменить первые 200 строк» и появившейся в рабочей области вкладке введите новые записи, заполняя все необходимые столбцы. В процессе внесения данных проверьте работоспособность всех созданных ранее ограничений: Ограничений проверки: попробуйте ввести в поле Zip (почтовый индекс) таблицы Customer нечисловые значения, а в поля InStock таблицыProduct,Price таблицы OrdItem - отрицательные. Значений по умолчанию: убедитесь, что при пропуске полей OrdDate и InStock таблиц Order и Product для них устанавливаются значения по умолчанию в виде текущей системной даты и нуля соответственно. Ограничений первичного и уникального ключа: попробуйте ввести в таблицы записи с дублирующими значениями первичного или уникального ключа. Ограничений внешнего ключа: попробуйте ввести несогласованные данные в связанные таблицы, например, заказ для несуществующего клиента или удалить запись из любой главной таблицы при наличии связанных записей в подчиненной при отсутствии правил каскадирования. Правил каскадирования: убедитесь, что при удалении записи из таблицы Order все связанные записи из таблицы OrdItem удаляются автоматически. Вопросы 1. Для чего предназначена утилита SQLServer Management Studio? 2. Перечислите предлагаемые программой SQL Server способы аутентификации. 3. Что отображается на панели Обозреватель серверов после подключения к серверу с помощью утилиты утилита SQLServer Management Studio? 4. Опишите структуру окна утилиты Management Studio? | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||