Всё для Учёбы — студенческий файлообменник
1 монета
doc

Студенческий документ № 043098 из НИТУ МИСиС

Управление данными

методические указания

к выполнению лабораторных работ

для студентов обучающихся по направлению

230400 - "Информационные системы и технологии"

(очная, очно-заочная, заочная формы обучения)

220700 Автоматизация технологических процессов и производств

Содержание

Введение 5

ЛАБОРАТОРНАЯ РАБОТА N 1 6

Создание таблиц и схемы данных 6

ЛАБОРАТОРНАЯ РАБОТА N 2 15

ЗАПРОСЫ В Microsoft Access 15

ЛАБОРАТОРНАЯ РАБОТА N 3 25

Создание форм и отчетов. Использование макросов. 25

ЛАБОРАТОРНАЯ РАБОТА №4 45

Создание баз данных в SQL Server Management Studio 45

ЛАБОРАТОРНАЯ РАБОТА №5 57

Модификация, добавление и удаление данных в базах данных SQL Server 57

ЛАБОРАТОРНАЯ РАБОТА №6 72

Хранимые процедуры в базах данных SQL Server 72

ЛАБОРАТОРНАЯ РАБОТА №7 78

Триггеры в базах данных SQL Server 78

Литература 88

1. Цели и задачи дисциплины. Целью освоения дисциплины "Управление данными" является формирование у студентов целостного системного представления, необходимого для проектирования схем баз данных, а также изучения средств манипулирования данными современных промышленных систем управления базами данных

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

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

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

* Приобретение умения применять различные методы и средства анализа для моделирования предметных областей;

* овладение современными системами управления базами данных и соответствующих средств программирования;

* формирование готовности использования методов математического моделирования при построении схемы реляционной базы данных по нормализованной концептуальной ER- модели

2. Место дисциплины в структуре ООП.

Дисциплина относится к базовой (обязательной) части профессионального цикла П.Б структуры ООП по направлению подготовки "Информационные системы и технологии".

По направлению подготовки "Автоматизация технологических процессов и производств" дисциплина относится к вариативной (дисциплины по выбору) части П.ВС.

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

.

3. Требования к уровню освоения дисциплины.

3.1 Требования к уровню освоения дисциплины в соответствии с ФГОС ВПО и ООП.

Процесс изучения дисциплины направлен на формирование следующих компетенций:

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

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

(ОК-12);

- владеть способностью выбирать программно-аппаратные средства автоматизации и управления производственными процессами и объектами (ИПК-1).

3.3 Требования к конечным результатам освоения дисциплины.

В результате освоения дисциплины студент должен:

- знать: методы математического анализа и моделирования, теоретического и экспериментального исследования, основные понятия в области разработки банков данных и знаний, архитектуру банка данных, роль и место банков данных в информационных системах; преимущества централизованного управления данными, современные системы управления базами данных, администрирование базы данных, модели данных, основные операции и ограничения; представление структур данных в памяти ЭВМ, современные тенденции построения файловых систем, характеристики промышленных СУБД; тенденции развития банков данных(ОК-10, ОК-10, СПК-1, СПК-7, ОПК-4, ИПК-1, СПК-7, СПК-8, ОПК-5, ОК-12);

- уметь: применять теоретические знания в области баз данных для решения конкретных практических задач по выбранному направлению подготовки , самостоятельно приобретать знания в области управления данными с использованием разнообразных источников информации, в том числе электронных образовательных изданий и ресурсов; моделировать предметную область, разрабатывать модели базы данных на различных уровнях представления данных, разрабатывать пользовательский интерфейс с использованием различных сред программирования (ОК1,ОПК1,ОПК2, СПК1, ОК6, СПК8, ОПК-4, ИПК-1, СПК-7);

- владеть: навыками моделирования предметной области, навыками работы с клиент-серверными архитектурами, навыками работы по проектированию баз данных с использованием языка реляционных баз данных, навыками разработки пользовательского интерфейса на внешнем уровне трехуровневой архитектуры баз данных, методикой использования программных средств и навыками применения современной вычислительной техники для решения задач оптимизации баз данных (СПК1, ОК10, ОПК4, ИПК1,ИПК2ОПК-4, ИПК-3, ОПК-5, ОК-12,СПК-5)

4. Структура дисциплины.

Таблица 2. Модули (разделы) дисциплины, трудоемкость в часах и виды учебной работы.

№ п/п № модуля Наименование работы Количество

часов 5-й семестр

1 I Создание таблиц и схемы данных 6

2 Запросы В Microsoft Access

4 3 II Создание форм и отчетов. Использование макросов 6 4 Создание баз данных в SQL Server Management Studio

6 5 III Модификация, добавление и удаление данных в базах данных SQL Server 4 6 Хранимые процедуры в базах данных SQL Server

4 7 Триггеры в базах данных SQL Server 4

Введение

Microsoft Access - представляет собой приложение Microsoft Office, которое позволяет создавать программные средства для получения определенных результатов. Главное отличие от других СУБД заключается в том, что под базой данных MS Access понимается совокупность структурированных и взаимосвязанных данных и методов, обеспечивающих добавление, изменение, выборку и отображение данных. Как правило, многие системы, связанные с БД, позволяют хранить все таблицы в одном файле и не включают формы и отчеты в файл БД. Для добавления, изменения и отображения данных в таких системах используют приложения конечного пользователя, которые называются также терминальными приложениями.

Microsoft Access называет объектами все, что может иметь имя. В БД Access основными объектами являются таблицы, запросы, формы, отчеты, макросы и модули.

Таблица.

Объект, который вы определяете и используете для хранения данных. Каждая таблица содержит информацию об объекте определенного типа. Таблица содержит поля /столбцы/ и записи /строки/.

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

Запрос. Объект, который позволяет пользователю получить нужные данные из одной или нескольких таблиц. Для создания запроса можно использовать бланк QBE /запрос по образцу/ или инструкции SQL. Вы можете создать запросы на выборку, обновление, удаление или добавление данных. С помощью запросов можно создавать новые таблицы, используя данные из одной или нескольких существующих таблиц.

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

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

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

Модуль. Объект, содержащий программы, написанные на языке Visual Basic для приложений, позволяющих разбивать процесс на несколько небольших процедур. Модули могут быть самостоятельными объектами и могут быть непосредственно "привязаны" к отдельным формам или отчетам.

В таблицах хранятся данные, которые вы можете извлекать с помощью запросов. Используя формы, вы можете выводить данные на экран или изменять их. События, происходящие в формах или отчетах, могут запускать макросы. Событие - любое изменение состояния объекта Microsoft Access. С помощью макросов и модулей вы можете изменять ход выполнения приложения: открывать, фильтровать и изменять данные в формах и отчетах, выполнять запросы, создавать таблицы. Используя Visual Basic, вы можете создать, модифицировать и удалить любой объект Access, обрабатывать данные по строкам и столбцам.

ЛАБОРАТОРНАЯ РАБОТА N 1

Создание таблиц и схемы данных

1. Цель работы

Научиться создавать базы данных, изучить систему именования объектов, а также порядок связывания таблиц с целью построения результативной и эффективной БД, которая полностью удовлетворяет требованием заказчика.

2. Общие рекомендации по созданию таблиц и схемы данных

Основными объектами БД являются таблицы, которые связаны между собой в схему данных. Именно с этих объектов необходимо начинать работу.

В MS Access существуют различные возможности по созданию таблиц:

- Режим таблицы;

- Конструктор;

- Мастер таблиц;

- Импорт таблиц

- Связь с таблицами

Рассмотрим создание таблицы в режиме Конструктора.

Для создания новой таблицы окно базы данных должно быть активно, перейдите на вкладку Таблицы и нажмите кнопку Создать.

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

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

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

Типы данных.

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

Тип данных Использование Размер Текстовый Алфавитно-цифровые данные. До 255 байтов Поле МЕМО Алфавитно-цифровые данные - предложения, абзацы, тексты До 64000 байтов Числовой Числовые данные 1,2,.4 или 8 байтов

16 байтов для кода реплики Дата/Время Даты и время 8 байтов Денежный Данные о денежных суммах, хранящиеся с 4 знаками после запятой 8 байтов Счетчик Уникальное длинное целое, генерируемое при создании новой записи 4 байта Логический Логические данные 1 бит Поле объекта OLE Картинки, диаграммы и др. объекты из приложений Windows До 1 гигабайта

Для символьных данных выбирают Текстовый тип и указывают максимальную длину поля. Поле МЕМО следует использовать только в тех случаях, когда размер текста может превысить 255 символов, или когда в тексте встречаются символы форматирования, такие как табуляция или возврат каретки. Если вы выбрали Числовой тип, вам следует тщательно обдумать вопрос о значении свойства Размер поля. Тип Дата/Время используется для хранения календарных дат и позволяет выполнять вычисления в единицах измерения времени /минутах, секундах, часах, днях, годах/. Денежный тип имеет ту же точность, что и тип Числовой, но с фиксированным числом знаков после запятой. Счетчик специально предназначен для автоматической генерации значений первичного ключа. Таблица не может содержать более одного поля с типом данных Счетчик. Логический тип данных используется для хранения значений Истина и Ложь. Он особенно полезен, если вам нужно отметить оплачены ли счета, выполнены ли тесты и т. п. Тип Поле объекта OLE позволяет хранить и редактировать документы Microsoft Word, электронные таблицы Microsoft Excel, картинки Microsoft Power Point, звуковые файлы, видеофайлы.

Свойства полей

Свойства на вкладке Общие:

* Размер поля. Поле с текстовым типом данных может иметь размер от 1 до 255 символов. По умолчанию устанавливается 50 символов. Для числового типа данных размер поля может быть следующим:

Байт Целые числа от 0 до 255, занимает при хранении 1 байт Целое Целые числа от -32768 до +32767, занимает 2 байта Длинное целое Целые числа от -2147483648 до +2147483647, занимает 4 байта С плавающей точкой/4 байта/ 38 38

Числа с точностью до 6 знаков от -3,4х10 до+3,4х10, занимает 4 байта С плавающей точкой/8байт/ 308 308

Числа с точностью до 10 знаков от -1,797х10 до +1,97х10, занимает 8 байт Код репликации 16-байтовый Globally Unique Identifier (GUID) * Формат поля. Вы можете задать формат представления данных при выводе на экран или печать. Для типов данных Текстовый и Поле МЕМО вы можете задать специальные форматы. Для типов данных Числовой, Денежный, Счетчик существует стандартный набор форматов поля, приведенный ниже:

Основной формат Устанавливается по умолчанию / отсутствуют разделители групп разрядов, число десятичных знаков зависит от точности данных/ Денежный Символы денежной единицы и два знака после запятой Фиксированный По крайней мере, один знак до и два после запятой С разделителями разрядов Два знака после запятой и разделители групп разрядов Процентный Процент Экспоненциальный 3

Экспоненциальная запись /например 1,05х10/

Для типа данных Дата/Время существует следующий набор форматов:

Дата Время Полный формат Устанавливается по умолчанию 15.04.99 05:30:10 РМ Длинный формат Среда 15 Апрель 1999 17:30:10 Средний формат 15-апр-99 05:30 РМ Краткий формат 15.04.99 17:30

Для логического типа данных используются следующие форматы:

Да/Нет Устанавливается по умолчанию

Истина/Ложь

Вкл/Выкл * Число десятичных знаков. Для числового и денежного типов вы можете задать число знаков после запятой. По умолчанию устанавливается значение Авто, при котором для денежного, фиксированного, с разделителями разрядов и процентного форматов поля выводятся два десятичных знака после запятой, а для основного формата число десятичных знаков определяется текущей точностью числовых значений. Вообще оно может изменяться от 1 до 15.

* Маска ввода. Для типов данных Текстовый, Числовой, Денежный и Дата/Время можно задать маску, которую пользователь увидит при вводе данных в поле.

* Подпись. Вы можете определить более содержательное название поля, которое Access будет выводить во всех элементах управления форм и в заголовках отчетов.

* Значение по умолчанию. Вы можете определить значение по умолчанию для всех типов данных, кроме Счетчика и Поля объекта OLE . Для числового типа значением по умолчанию является 0, а для текстового и МЕМО - Null.

* Условие на значение. Можно задать выражение, которое при вводе или редактировании значения поля всегда должно быть истинным. Например, 50 And Больше >= Больше или равно = Равно <> Не равно IN Проверяет на равенство любому значению из списка, операндом является список, заключенный в круглые скобки BETWEEN Проверяет, что значение поля находится в заданном диапазоне, верхняя и нижняя границы диапазона разделяются логическим оператором And LIKE Проверяет соответствие текстового или МЕМО поля заданному шаблону символов Если вам потребуется проверить соответствие значения текстового или МЕМО поля заданному шаблону, вы можете воспользоваться оператором сравнения LIKE. В качестве операнда задайте символьную строку, используя символы шаблона, приведенные ниже:

Символ шаблона Описание ? Заменяет один произвольный символ * Заменяет любое, включая нулевое, количество произвольных символов, используется для замены последовательностей символов # Заменяет одну цифру

Задание маски ввода

Чтобы облегчить ввод форматированных данных, Access позволяет задать маску ввода для поля с любым типом данных, кроме Поля МЕМО, Счетчика и Поля объекта OLE. Маска ввода определяется с помощью символов, приведенных ниже. Маска ввода состоит из трех частей, разделенных точкой с запятой.

Первая часть - собственно маска, состоящая из символов маски и постоянных символов. Вторя вторая часть указывает Access, нужно ли сохранять введенные промежуточные символы в таблице, например дефис или скобка. Если они включаются в значение поля, укажите во второй части 0, если сохраняться будут только введенные пользователем символы, укажите 1.

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

Символы, используемые для задания маски ввода:

Символ маски Описание 0 В данную позицию должна быть введена цифра, не допускается ввод знаков плюс + и минус -. 9 В данную позицию может быть введена цифра или пробел. Если пользователь пропустит эту позицию, Access не занесёт в эту позицию никакой информации. # В данную позицию может быть введена цифра, пробел, плюс, минус. Если пользователь пропустит эту позицию, Access занесёт в нее пробел. L В данную позицию должна быть введена произвольная буква. ? В данную позицию может быть введена буква. Если пользователь пропустит ее, Access не занесёт никакой информации. A В данную позицию должна быть введена буква или цифра.

А В данную позицию может быть введена буква или цифра. Если пользователь пропустит её, Access не занесёт никакой информации. & В данную позицию должен быть введен произвольный символ или пробел. C В данную позицию может быть введен любой символ или пробел. Если пользователь пропустил её, Access не занесет никакой информации. . Место для десятичной запятой /зависит от установок в окне Язык и стандарты панели управления Windows/. , Разделитель групп разрядов /зависит от установок в окне Язык и стандарты панели управления Windows/. :-/ Разделители компонентов даты и времени /зависят от установок в окне Язык и стандарты панели управления Windows/. Преобразует все символы справа к верхнему регистру. ! Указывает, что маску надо заполнять справа налево. \ Указывает, что следующий символ следует рассматривать в качестве постоянного символа, даже если он является символом маски. "литерал" Вместо того чтобы многократно использовать символ "\", вы можете заключить любой литерал в двойные кавычки.

Создание первичного ключа

Каждая таблица в реляционной базе должна иметь первичный ключ. Создать его в Access очень просто. Для выбора первого ключевого поля таблицы в окне конструктора щелкните в области выделения, расположенной слева от имени поля. Если вы хотите включить в первичный ключ несколько полей, нажмите клавишу Ctrl и, не отпуская ее, щелкните по области выделения полей. Затем нажмите кнопку Ключевое поле на панели инструментов или выберите команду Правка ->Ключевое поле. В подтверждение того, что ключ задан, Access выведет в области выделения слева от каждого поля, символ ключа. Остался всего один шаг - сохранить созданную таблицу. Выберите команду Файл ->Сохранить. Access откроет окно диалога Сохранение, введите имя таблицы и нажмите кнопку ОК.

Задание условия на значения для таблицы

Условие на значение для поля проверяется при вводе в это поле нового значения, а условие на значение для таблицы проверяется при сохранении или добавлении записи. В режиме конструктора нажмите кнопку Свойства на панели инструментов или выберите команду Вид ->Свойства, в результате чего откроется окно Свойства таблицы. Первая строка предназначена для ввода описания таблицы. Во вторую строку вы можете ввести любое допустимое выражение. Обратите внимание, что в условии на значение для таблицы можно сравнить содержимое одного поля с содержимым другого. Третья строка предназначена для сообщения, выводимого при нарушении этого условия на значения. Свойство Фильтр позволяет задать условие отбора записей, которые будут выводиться на экран в режиме таблицы. В строке Порядок сортировки можно указать поля, по которым будет производиться сортировка записей в режиме таблицы. По умолчанию Access выводит записи в порядке возрастания значений первичного ключа.

Определение связей

После того, как вы определите несколько таблиц, вам нужно сообщить Access, как они связаны друг с другом. Чтобы определить связи, вернитесь в окно базы данных, выберите команду Сервис->Схема данных. Если вы впервые определяете связи в этой базе данных, Access откроет пустое окно Схема данных, а затем выведет на экран окно диалога Добавление таблицы. В этом окне выделите все таблицы и нажмите кнопку Добавить. Затем нажмите кнопку Закрыть.

Установка связей между таблицами

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

Установка параметров связи в диалоговом окне Связи

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

3. Задание на лабораторную работу

Создать базу данных Доставка товара, в которой разработка объектов БД будет происходить в соответствии со следующими требованиями:

- некоторая фирма занимается реализацией и доставкой товаров;

- информация, с которой приходится сталкиваться фирме, касается имеющихся товаров и заказчиков. Следует также учесть, что адрес доставки товаров может не совпадать с адресом заказчика;

- один и тот же заказчик может в течение непродолжительного времени приобрести товары, которые проходят по различным накладным.

3.1 Создать базу данных, используя следующие таблицы (таблицы разработать в режиме конструктора):

Таблица 1. Товар

Название поля Тип данных Условия и ограничения (свойства поля) Описание Код_товара Числовой Уникальные значения (первичный ключ); размер поля-длинное целое; условие на значение - не могут быть отрицательными; сообщение об ошибке - "Введите правильный код товара!"; обязательное поле - да Используется для идентификации товара Название Текстовый Размер поля - 100 символов; обязательное поле - да; индексированное поле - нет Название товара Цена денежный Формат вывода: # ##0,00" p." условие на значение - не могут быть отрицательными; сообщение об ошибке - "Цена не может быть отрицательной!"; обязательное поле - да; индексированное поле - нет Денежный эквивалент товара

Таблица 2. Заказчик

Название поля Тип данных Условия и ограничения (свойства поля) Описание Код_заказчика Числовой Уникальные значения (первичный ключ); размер поля - длинное целое; условие на значение - не могут быть отрицательными; сообщение об ошибке - "Введите правильный код заказчика!"; обязательное поле - да Используется для идентификации заказчика Название Текстовый Размер поля - 100 символов; обязательное поле - да; индексированное поле - нет Название организации либо фамилия заказчика Адрес Текстовый Размер поля - 100 символов; обязательное поле - да; индексированное поле - нет Адрес заказчика Телефон Числовой Размер поля - длинное целое; формат вывода: 00-00-00; маска ввода - # 00\ - 00\ - 00;обязательное поле - нет; индексированное поле - нет Телефон заказчика

Таблица 3. Доставка

Название поля Тип данных Условия и ограничения (свойства поля) Описание Код_доставки Числовой Уникальные значения (первичный ключ); размер поля - длинное целое; условие на значение - не могут быть отрицательными; сообщение об ошибке - "Введите правильный код доставки!"; обязательное поле - да Используется для идентификации доставки Адрес Текстовый Размер поля - 100 символов; обязательное поле - да; индексированное поле - нет Адрес доставки Телефон Числовой Размер поля - длинное целое; формат вывода: 00-00-00; маска ввода - # 00\ - 00\ - 00;обязательное поле - нет; индексированное поле - нет Телефон доставки

Таблица 4. Заказ

Название поля Тип данных Условия и ограничения (свойства поля) Описание Код_заказа Числовой Уникальные значения (первичный ключ); размер поля - длинное целое; условие на значение - не могут быть отрицательными; сообщение об ошибке - "Введите правильный код заказа!"; обязательное поле - да Используется для идентификации заказа Код_заказчика Числовой Размер поля - длинное целое; обязательное поле - да; индексированное поле - да (допускаются совпадения); вводятся из списка соответствующих значений табл. 2 (поле со списком) Внешний ключ к табл.2 Дата_выписки Дата/время Формат поля - краткий формат даты; обязательное поле - да; индексированное поле - нет; установить маску ввода Фиксируется дата оформления заказа Дата_исполнения Дата/время Формат поля - краткий формат даты; обязательное поле - да; индексированное поле - нет; установить маску ввода Фиксируется дата исполнения заказа Код_доставки Числовой, длинное целое Размер поля - длинное целое; обязательное поле - да; индексированное поле - да (допускаются совпадения); вводятся из списка соответствующих значений табл. 3 (поле со списком) Внешний ключ к табл.3 Оплата Логический Формат поля - да/нет; обязательное поле - нет; индексированное поле - нет Фиксируется статус оплаты

Таблица 5. Артикул заказа

Название поля Тип данных Условия и ограничения (свойства поля) Описание Код_заказа Числовой Размер поля - длинное целое; обязательное поле - да; индексированное поле - да (допускаются совпадения); вводятся из списка соответствующих значений табл. 4 Внешний ключ к табл.1 Код_товара Числовой Размер поля - длинное целое; обязательное поле - да; индексированное поле - да (допускаются совпадения); вводятся из списка соответствующих значений табл. 1 Внешний ключ к табл.2 Количество Числовой Размер поля - длинное целое; условие на значение - не могут быть отрицательными; сообщение об ошибке - "Количество не может быть отрицательным!"; обязательное поле - нет; индексированное поле - нет Фиксируется количество проданных товаров

3.2 Связать таблицы в схему данных, используя связи "один-ко-многим", первичные и внешние ключи таблиц (задать параметры связи в диалоговом окне Изменение связей).

3.3 Заполните базу данными в режиме таблицы (10 записей)

4. Cодержание отчета

Отчет по лабораторной работе должен состоять из двух частей:

- первая часть - файл ЛАБОРАТОРНАЯ_1.mdb c выполненной работой;

- вторая часть - файл в формате WORD ЛАБОРАТОРНАЯ_1.doc с отчетом о выполненной работе. Отчет содержит краткие пояснения по каждому пункту задания к лабораторной работе и выводы.

ЛАБОРАТОРНАЯ РАБОТА N 2

ЗАПРОСЫ В Microsoft Access

1. Цель работы

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

2. Общие рекомендации по технологии разработки запросов

Для выполнения лабораторной работы необходимо повторить тему - "Запросы в Access"

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

Чтобы открыть окно нового запроса в режиме конструктора, перейдите на вкладку Запрос и нажмите кнопку Создать. У вас выбор: создать запрос самостоятельно или воспользоваться помощью мастера. Окно конструктора запросов разделено на две части. В верхней части находятся списки полей таблиц или запросов, на основе которых создается новый запрос. В нижней части - бланк QBE(Query By Example-запрос по образцу), в котором вы будете выполнять всю работу по созданию запроса. Каждый столбец бланка представляет одно поле, используемое в запросе. Первая строка бланка запроса служит для выбора полей, которые должны присутствовать в наборе записей. Во второй строке бланка Access выведет имя таблицы, из которой выбрано поле. В третьей строке вы можете задать, нужно ли выполнять сортировку по выбранному или вычисляемому полю. Флажки в строке бланка Вывод на экран отвечают за вывод полей в наборе записей. По умолчанию Access выводит все поля, включенные в бланк запроса. Чтобы исключить поле из набора записей снимите его флажок в строке Вывод на экран. Для ввода условия отбора записей используется строка Условие отбора и строки или.

Включение полей в запрос

Первым шагом при создании запроса является выбор полей, включаемых в набор записей. Это можно сделать несколькими способами. Чтобы увидеть список полей, перейдите к нужному столбцу и нажмите клавиши Alt и со стрелкой вниз. Чтобы включить поле в бланк запроса переместите выделение к этому полю и нажмите Enter. Можно просто перетащить поле из списка полей в верхней части окна. При перетаскивании поля указатель мыши превращается в маленький прямоугольник. Если вы хотите включить в запрос все поля таблицы достаточно перетащить в бланк запроса специальный символ "*", означающий "все поля". Другой способ включить в запрос все поля - дважды щелкнуть по заголовку списка полей в верхней части окна: таким образом вы выделяете все поля таблицы. Затем перетащите выделенные поля в строку Поле бланка запроса. Указатель мыши превратится в значок с изображением нескольких прямоугольников, показывающий, что вы перетаскиваете несколько полей. Когда вы отпустите кнопку мыши, Access поместит в бланк все поля таблицы.

Установка свойств полей

В общем случае поля наследуют те свойства, что заданы для соответствующих полей таблицы. Вы можете задать другие значения следующих свойств: Описание, Формат поля, Число десятичных знаков, Маска ввода и Подпись. Чтобы задать свойства некоторого поля, щелкните по любой ячейке соответствующего столбца и нажмите кнопку Свойства на панели инструментов или выберите команду Вид ->Свойства. Хотя поля запроса наследуют свойства соответствующих полей исходных таблиц, вы не увидите их в окне свойств. После установки новых свойств полей, перейдя в режим таблицы, вы увидите набор записей запроса в новом виде.

Ввод условий отбора

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

* Выбор между Or и And. Когда вы вводите условие отбора для нескольких полей, то все выражения в строке Условие отбора или в строке или должны принимать значение Истина для любой записи, включаемой в набор записей запроса.

Таблица 1. Результат применения к двум условиям логической операции And

AND Истина Ложь Истина Истина

(отбирается) Ложь

(отвергается) Ложь Ложь

(отвергается) Ложь

(отвергается)

Когда вы задаете для некоторого поля несколько условий отбора, соединенных логическим оператором Or, то для того, чтобы запись была отобрана запросом, истинным должно быть хотя бы одно из них. Есть два способа задать несколько связанных оператором Or условий для одного поля. Можно ввести все условия в одну ячейку строки Условие отбора, соединив их оператором Or. Другой вариант: ввод каждого условия в отдельную ячейку строки или. При использовании нескольких строк или для отбора записей достаточно выполнения всех условий в одной из строк или. Чтобы результат операции Or имел значение Истина и запись отбиралась запросом, достаточно истинности одного условия.

Таблица 2. Результат применения к двум условиям логической операции Or

OR Истина Ложь Истина Истина

(отбирается) Истина

(отбирается) Ложь Истина

(отбирается) Ложь

(отвергается)

* BETWEEN, IN, LIKE

Кроме обычных операторов сравнения, Access предоставляет три специальных оператора, полезных для отбора данных.

BETWEEN определяет диапазон значений.

IN задает используемый для сравнения список значений.

LIKE оператор, полезный при поиске образцов в текстовых полях. В образец поиска вы можете включить символы шаблона. "?" заменяет любой символ в данной позиции, "*" обозначает любое количество символов в данной позиции, "#" указывает, что в данной позиции должна стоять цифра.

* Условие отбора для дат и времени

Чтобы сообщить Access о том, что вы вводите дату и время, заключайте значение в символы числа(#).

Day (дата) Возвращает значение дня месяца в диапазоне от 1 до 31

Month (дата) Возвращает месяца года в диапазоне от 1 до12

Year (дата) Возвращает значение года в диапазоне от 100 до 9999

Weekday (дата) По умолчанию возвращает целое число от 1(воскресенье) до

7(суббота), соответствующее дню недели.

Hour (дата) Возвращает целое число от 0 до 23, представляющее значение

часа.

DatePart (интервал, дата) Возвращает номер квартала или номер недели в

зависимости от того, какой код интервала вы

задаете.

Date() Возвращает текущую системную дату. Например, чтобы выбрать даты не позже, чем за 30 дней до текущей, введите = 100 Количество наименований товара больше или равно 100 Заказ Дата_

выписки YEAR([ДатаВыписки])=2005 Накладные, выписанные за 2005 г. Заказ Дата_

выписки BETWEEN #01.09.04#

AND #31.12.04# Накладные, выписанные в последнем квартале 2004 г. Заказ Оплата IS NULL Неоплаченные

накладные Товар Цена BETWEEN 10000 AND 25000 Товары, цена которых находится в пределах от 10 000 до 25 000 р.

8. Определить параметры выборки запроса (нужно ли при выполнении запроса вводить некоторую уточняющую информацию). Как отмечалось ранее, параметрический запрос может быть совместим с запросом любого вида. Для

Выбор записей, формирующих группы

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

Выбор групп

В итоговом запросе можно фильтровать не только отдельные записи, но и группы. Введите условие отбора для любого поля, имеющего в строке Групповая операция либо установку Группировка, либо одну из девяти итоговых функций Access, либо выражение, использующее итоговые функции.

Использование параметров запроса

До сих пор вы вводили условия отбора непосредственно в бланке запроса в режиме конструктора. Однако на этапе создания запроса не всегда можно определить какие именно значения должен отыскивать Access. Включите в запрос параметр, и при каждом выполнении запроса Access будет запрашивать конкретные условия отбора. Чтобы определить параметр, введите в строку Условие отбора вместо конкретного значения имя или фразу, заключенную в квадратные скобки. То, что заключено внутри квадратных скобок, Access рассматривает как имя параметра. Оно выводится в окне диалога при выполнении запроса, поэтому в качестве имени параметра разумно использовать содержательную фразу. В одном запросе можно задать несколько параметров, при этом имя каждого параметра должно быть уникальным и информативным. Для каждого параметра запроса можно указать тип данных. Access использует эту информацию для проверки введенного значения. Например, если вы определили параметр как числовой, Access отвергнет буквенные символы в значении параметра. По умолчанию Access присваивает параметрам запроса текстовый тип данных. Если понадобится изменить тип данных, выберите команду Запрос->Параметры, и Access выведет на экран окно диалога Параметры запроса. В этом окне диалога введите имя каждого параметра, тип которого вы хотите определить, в столбец Параметр точно в таком же виде, в каком оно было указано в бланке запроса, но без квадратных скобок. В столбце Тип данных установите нужный, выбрав его из раскрывающегося списка. После определения всех параметров нажмите кнопку ОК.

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

Перекрестные запросы

Access поддерживает особый тип итоговых запросов, называемый перекрестными запросами. Они позволяют увидеть вычисляемые значения в виде перекрестной таблицы, напоминающей электронную. Для построения перекрестного запроса выделите имя нужной таблицы в окне БД и выберите Новый запрос в раскрывающемся списке кнопки Новый объект на панели инструментов. В окне диалога Новый запрос выберите Конструктор, а затем в окне конструктора выберите команду Запрос->Перекрестный. Access добавит в бланк запроса строку Перекрестная таблица. В этой строке для каждого поля может быть выбрана одна из четырех установок: Заголовки строк, Заголовки столбцов, Значение и Не отображается. Для перекрестного запроса надо определить, по крайней мере, одно поле в качестве заголовков строк, одно для заголовков столбцов и одно поле значений. Каждое поле, являющееся заголовком столбцов, должно иметь в строке Групповая операция установку Группировка. Для поля, использующегося в качестве заголовков строк, в строке Групповая операция должна быть установлена операция Группировка, выбрана одна из итоговых функций или введено выражение, содержащее итоговую функцию. Для поля с установкой Значение выберите одну из итоговых функций или введите выражение, использующее итоговую функцию. Как и в других типах итоговых запросов, для отбора данных, включаемых в набор записей, вы можете использовать дополнительные поля. Для них необходимо выбрать установку Условие в строке Групповая операция и Не отображается в строке Перекрестная таблица, а затем ввести условие отбора. Условие отбора можно также задать для любого поля, используемого в качестве заголовка столбцов, а данные можно сортировать по любым полям.

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

Многотабличные запросы

Рассмотрев возможности запросов, основанных на одной таблице, на базе полученных знаний легко организовать просмотр объединенных данных из нескольких связанных таблиц. В окне БД перейдите на вкладку Запросы и нажмите кнопку Создать. В окне диалога Новый запрос выберите Конструктор и нажмите кнопку ОК. Access откроет окно нового запроса в режиме конструктора и выведет на экран окно диалога Добавление таблицы. Это окно диалога позволяет выбрать таблицы и запросы, которые будут базовыми для нового запроса.

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

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

Внешние объединения

Для большинства многотабличных запросов набор записей формируется на основе совпадения связанных полей базовых таблиц. Такой тип запроса называется симметричным объединением. Дополнительную информацию вы можете получить, создав внешнее объединение. Для этого нужно изменить параметры объединения: откройте запрос в режиме конструктора. Чтобы открыть окно диалога Параметры объединения, дважды щелкните по линии связи между таблицами в верхней части окна запроса. По умолчанию в этом окне устанавливается переключатель Объединение только тех записей, в которых связанные поля обеих таблиц совпадают. В вашем распоряжении есть еще две возможности выбрать тип объединения. Установите переключатель 2 или 3 и нажмите кнопку ОК. Теперь в окне конструктора запросов вы должны увидеть на линии связи между таблицами стрелку, указывающую на то, что создается внешнее объединение

Создание запроса на основе другого запроса

При создании запроса в режиме конструктора окно диалога Добавление таблицы позволяет выбирать в качестве источника данных для нового запроса не только таблицы, но и запросы. Действительно, построение одного запроса на основе другого - это еще один способ работы с данными из нескольких таблиц: сначала создается один запрос, с помощью которого решается определенный круг задач и отбирается подмножество данных из нескольких таблиц, а затем не его основе строится другой для получения окончательного набора записей. Сохранив запрос, выделите его имя в окне БД, затем выберите в раскрывающемся списке кнопки Новый объект пункт Новый запрос и приступите к построению нового запроса. Выполните команду Запрос->Добавить таблицу, выберите таблицу или запрос в окне диалога Добавление таблицы и нажмите кнопку Добавить. Access добавит в окно запроса список полей выбранной таблицы или запроса и автоматически свяжет ее с вашим запросом. Щелкните дважды по линии связи, чтобы открыть окно диалога Параметры объединения, и установите переключатель для создания внешнего объединения.

Ограничения при использовании запросов на выборку для обновления данных

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

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

При работе с набором записей, полученным в результате объединения, Access разрешает изменять значения любых полей таблицы, находящейся на стороне отношения "многие", но не позволяет обновлять ключевые поля таблицы со стороны "один". При разработке сложной формы вы познакомитесь с приемами, исключающими возможность непреднамеренного обновления полей в запросах.

Настройка свойств запроса

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

Чтобы открыть окно свойств запроса, щелкните в любом месте верхней части окна запроса в режиме конструктора вне списка полей и нажмите кнопку Свойства на панели инструментов. В зависимости от сложности запроса Access может понадобиться несколько секунд, чтобы найти все строки и начать вывод информации на экран. При выполнении запроса часто возникают ситуации, при которых Access находит повторяющиеся строки. По умолчанию он включает в результирующий набор только уникальные записи. Это означает, что идентификатор каждой строки уникален. Если вы не просите Access возвращать уникальные значения, в набор записей запроса будут включены только отличающиеся друг от друга строки. Если вы хотите увидеть все данные, установите оба свойства - Уникальные значения и Уникальные записи - в значение Нет.

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

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

4 Задание на лабораторную работу

4.1 Запросы на выборку

- Получить даты выписки накладных и даты исполнения для всех заказчиков (внутреннее соединение по одному полю). Для поля Название установить сортировку по возрастанию. В поле Вывод на экран проверить соответствующую отметку для всех полей, включенных в запрос.

- Получить список товаров по накладным с заказанным количеством и ценой

- Получить список заказчиков, у которых физический адрес и адрес доставки совпадают (внутреннее соединение по нескольким полям)

- Получить список заказчиков с номерами заказов, датами выписки и исполнения, оплатой. Добавить в запрос параметр, требующий при выполнении запроса указать название заказчика (левое внешнее соединение с параметром)

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

- Получить список фирм-заказчиков, которые имеют разный физический адрес и адрес доставки (соединение по отношению)

- Получить стоимость отдельного товара в заказах (запрос с вычисляемым полем)

4.2 Групповые запросы

- Получить суммарную стоимость конкретных товаров, оформленных в заказах (запрос с вычисляемым полем и групповой операцией)

- Определить суммарную стоимость товара: "шкаф трехстворчатый", "вешалка для одежды" и "доска объявлений", дата выписки которых по накладным начинается с 1 января 2013г., рассчитать также налоговые отчисления за конкретный товар и общую сумму оплаты за товар (стоимость и налоговые отчисления).

Правило для расчета налоговых отчислений: отчисления производятся со стоимости каждого проданного товара и представляют итоговое значение в денежном формате. На сумму отчислений налагаются условия: стоимость товара менее 600000р. - удерживается 10% суммы, стоимость товара более 600000р. - удерживается 50% .

4.3 Запросы на изменение (модифицирующие запросы)

- Создать таблицу Адрес_доставки, включающую информацию о клиентах, имеющих разные физический адрес и адрес доставки (запрос на создание таблицы)

- Добавить в таблицу Адрес_доставки записи о совпадающих адресах заказчиков (запрос на добавление)

- Удалить из таблицы Адрес_доставки записи, которые удовлетворяют следующим критериям: название фирмы заказчика содержит первую букву "М" либо букву "о" в середине названия и в физическом адресе фирмы (запрос на удаление)

- Обновить данные в таблице Адрес_доставки в соответствии со следующими критериями: для фирмы "МОКА" физический адрес изменился на "Кленовая, 14", адрес доставки _ "Жукова, 12" (запрос на обновление).

4.4 Перекрестные запросы

- Получить стоимость заказов с учетом товаров, приобретенных после 1 января 2013г. (перекрестный запрос)

5. Cодержание отчета

Отчет по лабораторной работе должен содержать:

- первая часть - файл ЛАБОРАТОРНАЯ_2.mdb c выполненной работой; каждый пункт задания должен быть подтвержден соответствующим запросом;

- вторая часть - файл в формате WORD ЛАБОРАТОРНАЯ_2.doc с отчетом о выполненной работе. Отчет содержит краткие пояснения по каждому пункту задания к лабораторной работе и выводы.

ЛАБОРАТОРНАЯ РАБОТА N 3

Создание форм и отчетов. Использование макросов.

1. Цель работы

Научиться создавать формы и отчетов в MS Access, включая использование макросов, что позволит придать приложению, созданному на основе базы данных, законченный вид: оконный интерфейс, автоматизация действий пользователя, распечатка необходимых документов, полученных на основе хранимых данных.

2. Общие рекомендации по созданию форм и отчетов

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

Объектно-ориентированное программирование основано на понятиях объектов и действий над ними. Объекты могут содержать другие подчиненные им объекты. Когда объект определяет новое действие над другим объектом, то наследуются все атрибуты и свойства другого объекта и тем самым расширяет его определение. В Access запросы определяют действия над таблицами, а результаты запросов становятся новыми логическими таблицами, называемыми наборами записей. Запросы наследуют правила форматирования и обеспечения целостности данных, определяемые для базовых таблиц. Далее формы определяют действия над таблицами или запросами, и поля, включаемые в формы, изначально наследуют такие свойства базовых таблиц или запросов, как правила форматирования и условия на значения.

Внутри БД Access вы можете задавать взаимосвязь данных и объектов. Например, вы можете создать макрос, который подготавливает приложение к выполнению. Этот макрос (называемый Autoexec) обычно открывает форму, с которой начинается работа в приложении. Эта форма может воздействовать на некоторые данные или содержать элементы управления, открывающие другие формы, печатающие отчеты или закрывающие приложение.

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

Системы объектно-ориентированного программирования достаточно гибко реагируют на изменение последовательности событий. Благодаря этому пользователю можно предоставить простой способ выполнения операции, включив в форму соответствующую командную кнопку.

Создание простой формы для ввода данных

Чтобы начать создание формы, откройте БД и выберите в окне БД таблицу или запрос, которые вы хотите использовать для создания формы. Выполните команду Вставка->Форма или выберите Новая форма в раскрывающемся списке Новый объект на панели инструментов. Access откроет окно диалога Новая форма. В поле со списком, расположенном в нижней части окна диалога, Access выводит имя таблицы или запроса, выбранного в окне БД. Если вы хотите выбрать другую таблицу или запрос, раскройте список этого поля, содержащий перечень всех таблиц и запросов в текущей БД.

Выберите Конструктор в окне диалога и нажмите кнопку ОК. Access окно формы в режиме конструктора и выведет на экран несколько инструментов конструктора форм. В новой форме будет только область данных с серой сеткой. Вы можете установить указатель мыши на краю этой области и перетащить его, уменьшая или увеличивая его. Вы можете удалить сетку с точками, выполнив команду Вид->Сетка. Если вы хотите добавить в форму заголовки и примечания, выберите команду Формат->Заголовок/примечание формы.

Панель элементов - это своеобразный "командный центр" создания формы. Вы можете переместить панель элементов, перетащив заголовок ее окна в нужное место на экране, или изменить ее форму, перетащив края или углы панели. Чтобы закрыть панель элементов нужно выбрать команду Вид->Панель элементов или нажать кнопку Панель элементов на панели инструментов.

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

Ниже перечислены кнопки панели элементов слева направо и сверху вниз.

* Кнопка Выбор объектов. По умолчанию эта кнопка нажата. Она используется для выделения, изменения размера, перемещения и редактирования элементов управления.

* Кнопка Мастера элементов. Нажмите эту кнопку, чтобы активизировать мастера по созданию элементов управления. Если кнопка выглядит "нажатой", то мастер поможет вам ввести свойства элемента управления при создании группы, поля со списком, списка или командной кнопки.

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

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

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

* Кнопка Выключатель используется для создания элемента управления Выключатель. Если вы щелкните по выключателю, он будет изображен нажатым, а его значение будет равняться -1(Вкл., Истина, Да). Щелкните по выключателю еще раз и его значение станет равным 0 (Выкл., Ложь, Нет). Вы можете включить выключатель в группу элементов управления и назначить ему уникальное числовое значение. Если вы щелкните по выключателю, переводя его в состояние Вкл., то любой ранее нажатый элемент группы (выключатель, переключатель или флажок) будет переключен в состояние Выкл. Если выключатель связан с полем базового запроса или таблицы, то, щелкнув по нему, вы измените значение поля на противоположное.

* Кнопка Переключатель используется для создания элемента управления Переключатель, иногда называемого радиокнопкой и принимающего значения Вкл./Выкл., Истина/Ложь, Да/нет. Если вы щелкните по переключателю, то в его центре появится темный кружок, его значение станет равным -1. Щелкните еще раз, значение переключателя станет равным 0. Вы можете включить переключатель в группу элементов управления и назначить ему уникальное числовое значение. Если вы щелкните по переключателю, переводя его в состояние Вкл., то любой ранее нажатый элемент группы (выключатель, переключатель или флажок) будет переключен в состояние Выкл. Если переключатель связан с полем базового запроса или таблицы, то, щелкнув по нему, вы измените значение поля на противоположное.

* Кнопка Флажок используется для создания элемента управления Флажок, принимающего значения Вкл./Выкл., Истина/Ложь, Да/нет. Если вы щелкните по флажку, то в центре появится пометка в виде косого креста, а его значение станет равным-1, щелкните еще раз, и его значение станет равным 0, а пометка исчезнет. Вы можете включить флажок в группу элементов управления и назначить ему уникальное числовое значение. Если вы щелкните по флажку, переводя его в состояние Вкл., то любой ранее нажатый элемент группы (выключатель, переключатель или флажок) будет переключен в состояние Выкл. Если флажок связан с полем базового запроса или таблицы, то, щелкнув по нему, вы измените значение поля на противоположное.

* Кнопка Поле со списком используется для создания элемента управления Поле со списком, которое можно рассматривать как объединение двух элементов: поле и список. Вы можете задать список явно, введя список значений в строке свойства Источник записей. В качестве источника значений списка можно также использовать таблицу или запрос. Значение, выбранное в списке, отображается в поле. Чтобы раскрыть список, нажмите кнопку со стрелкой вниз. При выборе нового значения в списке изменяется значение элемента управления. Если поле со списком связано с полем базовой таблицы или запроса, вы можете изменить значение этого поля, выбрав в списке новое значение. Список может содержать несколько столбцов, вы можете скрыть часть столбцов списка, установив для них ширину, равную нулю. Значением элемента управления может быть значение любого столбца, в том числе и скрытого. Если список закрыт, Access выводит на экран первого столбца, ширина которого больше нуля.

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

* Кнопка элемента Кнопка используется для создания элемента управления Командная кнопка, с помощью которой можно запустить макрос или процедуру VBA.

* Кнопка Рисунок позволяет поместить в форму статический рисунок. Его нельзя будет редактировать в форме, но Access хранит рисунок в наиболее эффективном сточки зрения быстродействия приложения и использования памяти формате.

* Кнопка Свободная рамка объекта используется для включения в форму объекта из другого, поддерживающего OLE, приложения. Объект становится частью формы, но не хранится в БД. Чтобы сделать форму более выразительной и наглядной, вы можете включить в нее рисунки, звуковые эффекты, диаграммы или слайды.

* Кнопка Присоединенная рамка объекта используется для включения в форму объекта OLE, хранящегося в базовой таблице. Access может отобразить большинство рисунков и диаграмм непосредственно в форме. Для других объектов Access выводит в форме значок приложения, в котором был создан объект.

* Кнопка Конец страницы позволяет вставлять разрыв страницы в многостраничной форме.

* Кнопка Подчиненная форма/отчет используется для включения в текущую форму некоторой другой формы. С помощью подчиненной формы вы можете вывести на экран данные таблицы или запроса, связанные с текущими данными основной формы.

* Кнопка Линия используется для включения в форму прямых линий, помогающих сделать ее более выразительной.

* Кнопка Прямоугольник используется для включения в форму прямоугольников.

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

Список полей

Размещение в форме присоединенных элементов управления (связанных с полями таблицы или запроса) упрощается при использовании списка полей. Последний открывается с помощью кнопки Список полей на панели инструментов или команды Вид->Список полей. В строке заголовка окна списка полей Access выводит имя базовой таблицы или запроса. Чтобы увидеть длинные имена полей полностью, можно увеличить размер окна. Чтобы создать присоединенный элемент управления с помощью списка полей, выберите его тип, нажав соответствующую кнопку на панели элементов. Затем перетащите поле из списка в нужное место формы. Если выбран элемент управления, не соответствующий типу данных связанного с ним поля, Access создаст элемент управления, используемый по умолчанию для этого типа данных.

Окно свойств

Сама форма, каждый ее раздел, и элементы управления имеют свойства, и вы можете установить их значения с помощью окна свойств. Набор свойств зависит от объекта. Чтобы открыть окно свойств, выделите интересующий вас объект и кнопку Свойства на панели инструментов или выберите команду Вид->Свойства. Access откроет окно свойств формы. Поскольку форма имеет более 70 свойств, а большинство элементов управления - более 30, значения которых вы можете устанавливать, то окно свойств содержит несколько вкладок. На вкладках представлены определенные категории свойств: свойства данных, макета, событий, другие свойства.

Очень удобным средством являются построители, помогающие создавать значения свойств, в которых используется сложное выражение, определение запроса, макрос или процедура VBA. Если выбрано свойство, для которого можно использовать построитель, рядом с ячейкой появляется кнопка с многоточием (кнопка построителя). Например, если на вкладке Данные в окне свойств вы щелкните по ячейке свойства Источник записей и нажмете кнопку построителя, Access запустит построитель запросов. Построитель спросит, хотите ли вы построить новый запрос на основе таблицы, которая в данный момент является источником записей для формы. Если вы ответите Да, откроется окно нового запроса в режиме конструктора со списком полей вашей таблицы.

Предположим, вы хотите построить форму на основе запроса с сортировкой по возрастанию названий. Выделите все поля таблицы и перетащите их в бланк запроса. В бланке запроса для поля с наименованием в строке Сортировка установите значение По возрастанию. Если вы сейчас закроете окно запроса, Access спросит, хотите ли вы обновить свойство. Если в окне сообщения вы нажмете кнопку Да, Access сохранит инструкцию SQL для созданного запроса в ячейке свойства Источник записей. Но лучше сохранить запрос и присвоить ему имя. Когда вы будете закрывать его, Access спросит, хотите ли вы обновить свойство. Если вы ответите Да, Access поместит в ячейке свойства имя запроса, а не инструкцию SQL.

Панель инструментов форматирования

Для придания элементу управления нужного внешнего вида не обязательно изменять его свойства, можно воспользоваться кнопками панели инструментов форматирования. Кроме того, эта панель позволяет устанавливать цвет фона для разделов формы. Ниже приведены описания кнопок панели инструментов форматирования (слева направо):

* Выбор объекта Выделяет объект в форме, что особенно удобно, когда объекты рас

полагаются поверх других.

* Шрифт Позволяет выбрать шрифт для текста надписей, командных кнопок,

выключателей, полей, списков и полей со списком.

* Размер шрифта Используется для установки размера шрифта.

* Полужирный Полужирное начертание.

* Наклонный Наклонное начертание.

* Подчеркнутый Подчеркнутое начертание.

* По левому краю Выравнивает текст по левому краю.

* По центру Выравнивает текст по центру.

* По правому краю Выравнивает текст по правому краю.

* Цвет фона Используется для изменения цвета фона элемента управления или

раздела формы.

* Цвет текста Изменение цвета символов в элементе управления.

* Цвет границы Изменение цвета границы элемента управления.

* Ширина границы Установка толщины границы элемента управления от сверхтонкой

до 6 пунктов.

* Оформление Позволяет применять специальные эффекты: обычное, приподнятое, утопленное, вдавленное, с тенью и рельефное.

Установка свойств поля

После размещения полей в форме вам, возможно, придется изменить значения некоторых их свойств. Если поле является счетчиком, оно не может быть изменено пользователем, запретите выбор этого элемента в форме, установив для свойства Доступ значение Нет. Поскольку Access затеняет незаблокированный элемент управления, к которому нет доступа, установите свойство Блокировка в значение Да, чтобы показать, что это поле - не обновляемое. Элемент управления не будет затенен, но пользователь не сможет перейти к нему или выделить его в режиме формы.

Свойства Формат поля, Число десятичных знаков, Маска ввода, определенные для поля таблицы, Access копирует в соответствующие свойства элементов управления, связанного с этим полем. Однако в форме можно определить более жесткое условие на значение. Свойство Значение по умолчанию наследуется из базовой таблицы. Если вы хотите использовать другое значение по умолчанию, введите его в окне свойств.

Свойство Текст строки состояния устанавливается в значение, совпадающее с установкой свойства Описание. Другие свойства также могут быть настроены в соответствии с вашими требованиями.

Установка свойств надписи

Вы можете отдельно определить свойства надписи, присоединенной к элементу управления. Чтобы увидеть окно свойств, выделите надпись поля. Access копирует значение свойства Подпись, определенное для поля базовой таблицы, в свойство Подпись для присоединенной надписи.

Другой способ изменить надписи: выделите ее в форме и поместите указатель внутри надписи. Щелкните еще раз, чтобы указать точное место вставки символов. Удалите ненужные и введите новый текст. Если размер надписи не соответствует длине нового текста, измените размеры надписи с помощью маркеров или задайте новые размеры в окне свойств.

Чтобы быстро настроить размер надписи по размеру содержащегося в ней текста, выделите надпись и выберите команду Формат->Размер->По размеру данных.

Установка свойств формы

Щелкните в любом месте формы вне области данных или выберите команду Правка->Выделить форму, и в окне свойств будут показаны свойства формы. Значение свойства Подпись будет использоваться в режиме формы и таблицы в качестве заголовка окна формы. Свойства событий, следующие за свойством Дополнительные сведения, могут использоваться для запуска макрокоманд, макросов, процедур VBA для приложений.

Свойства Число делений по Х и Число делений по У, определяющие плотность точек в сетке. Для более точного размещения объектов в форме можно использовать команду Формат->Привязать к сетке или увеличить плотность точек в сетке.

Окончательная настройка формы

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

Переключитесь в режим формы, нажав кнопку Представление формы на панели инструментов. Для настройки окна формы точно по размеру макета выберите команду Окно->По размеру формы. Сохраните созданную форму, пользуясь кнопкой сохранить на панели инструментов или командой Файл- >Сохранить.

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

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

Списки и поля со списком

Список или поле со списком - удобные способы отображения перечня возможных значений элемента управления. Чтобы задать список, можно ввести все возможные значения прямо в ячейке свойства Источник записей. В качестве источника записей можно определить таблицу или запрос. Access отображает текущее значение в верхней части поля со списком или выделяет его в списке. Для того чтобы создать поле со списком, выполните следующие действия:

1. На панели инструментов нажмите кнопку Мастера элементов, а затем кнопку Поле со списком и перетащите в форму нужное поле из списка полей исходной таблицы. Вы увидите в форме новый элемент управления, и Access выведет на экран окно диалога Создание полей со списком.

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

3. Во втором окне диалога Access выводит список доступных таблиц и запросов. Выберите таблицу и нажмите кнопку Далее.

4. В третьем окне мастер выводит список полей таблицы и список столбцов для поля со списком. Нажмите кнопку с двойной стрелкой, если хотите включить оба поля таблицы в список столбцов поля со списком.

5. В четвертом окне мастер выводит на экран выбранные вами столбцы с данными из таблицы. Здесь устанавливается ширина столбцов (столбцы с нулевой шириной не выводятся). Чтобы столбец с кодами отображался в раскрывающемся списке, можно снять флажок Скрыть ключевой столбец.

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

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

8. В последнем окне можно изменить предлагаемый мастером текст для присоединенной надписи поля со списком. Нажмите кнопку Готово, и мастер закончит создание поля со списком.

Если окно свойств открыто, вы можете изучить свойства поля со списком, установленные мастером. Свойство Данные показывает с каким полем связано поле со списком. Свойство Тип источника строк указывает из какой таблицы или запроса поступают данные. Ячейка свойства Источник строк содержит инструкцию SQL. Для свойства Число столбцов установлено количество используемых столбцов. Свойство Заглавия столбцов установлено в значении Нет (заголовки не выводятся).

Выключатели, флажки и переключатели

Если таблица содержит поле, принимающее значения Да/Нет, Истина/Ложь, Вкл./Выкл., то помните, что три типа элементов управления позволяют графически представлять и изменять значения таких полей: выключатель, флажок, переключатель. Чтобы изменить тип элемента управления, сначала удалите поле, принимающее указанные значения из формы. Затем нажмите кнопку Флажок на панели элементов и перетащите это поле из списка полей таблицы в правый верхний угол формы. Нажмите кнопку Представление формы на панели инструментов и вы увидите окончательный вид формы. Интересным аспектом использования в форме элементов управления подобного типа является то, что их свойства сохраняются при переходе в режим таблицы. Поэтому во многих случаях вместо создания специальной формы, предназначенной для использования в режиме таблицы, достаточно настроить соответствующим образом существующую форму, заменяя в ней поля другими элементами управления.

Разработка отчетов

Использование отчетов

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

- они предоставляют широкие возможности для группировки и вычисления промежуточных и общих итогов для больших наборов данных;

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

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

- для обеспечения иерархического представления данных можно определить до 10 условий группировки.

- для каждой из групп можно задать отдельные заголовки и примечания.

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

- в дополнение к верхнему и нижнему колонтитулу можно записать заголовок и примечание для всего отчета в целом.

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

Также как в форме, вы можете вставить в любой раздел отчета рисунки или диаграммы. Они могут быть внедрены как свободные, так и присоединенные объекты OLE.

Создание простого отчета

Построение отчета очень похоже на разработку формы. Выделите в окне БД запрос или таблицу и выберите Новый отчет в раскрывающемся списке кнопки Новый объект на панели инструментов (или выберите команду Вставка->Отчет). Access выведет на экран окно диалога Новый отчет. Access показывает имя выбранного запроса в поле со списком в нижней части окна диалога. Вы можете открыть список и выбрать в нем другую таблицу или запрос. Затем выберите Конструктор и нажмите кнопку ОК. В верхней части окна вы увидите панели инструментов конструктора отчетов и форматирования. Кроме того, конструктор отчетов предоставляет такие средства разработки, как окно свойств, список полей и панель элементов, которые вы можете выводить на экран по мере необходимости с помощью команд Вид->Список полей, Вид->Свойства, Вид->Панели элементов. Пустой макет отчета содержит разделы Верхний колонтитул, Нижний колонтитул, между которыми находится Область данных. Линейки по верхнему и левому краям окна помогают планировать расположение данных на странице. Вы можете изменить размер любого раздела, перетащив его границу. Добавить или удалить верхний или нижний колонтитулы можно с помощью команды Вид->Колонтитулы.

Отчеты в отличие от форм предоставляют возможность группировать вводимую в них информацию с помощью окна Сортировка и группировка. В нем можно определить до 10 полей или выражений, которые будут использоваться в отчете для группировки данных. Первый элемент в списке определяет основную группу, а последующие - подгруппы внутри групп. Если вы щелкните в первой строке столбца Поле/Выражение, то в правом углу ячейки появится кнопка со стрелкой вниз. Щелкните по ней, чтобы открыть список полей базовой таблицы или запроса. Выберите в списке поле, чтобы поместить его в столбец. Вы можете ввести в этом столбце выражение, содержащее ссылку на любое поле базовой таблицы или запроса. По умолчанию Access сортирует значения по возрастанию. Вы можете изменить порядок сортировки, выбрав значение По убыванию в раскрывающемся списке, который появляется после щелчка в ячейке столбца Порядок сортировки.

В отчете должно быть зарезервировано место для заголовков каждой групп и примечания для вычисляемых полей. Чтобы добавить в отчет эти разделы, установите для свойств Заголовок группы и Примечание группы значение Да в окне сортировки и группировки. Когда вы это сделаете, Access добавит в отчет требуемые разделы.

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

Наконец, в правом нижнем углу нижнего колонтитула создайте свободное поле. В качестве значения его свойства Данные введите выражение =''Page''&[Page] & "of" &[Pages]. [Page] - это свойство отчета, содержащее номер текущей страницы. [Pages] - другое свойство отчета, содержащее общее число страниц.

Для просмотра результатов нажмите кнопку Предварительный просмотр на панели инструментов.

3. Общие сведения о макросах

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

* для открытия/закрытия любой таблицы, запроса, формы, отчета в любом режиме.

* для открытия отчета в режиме предварительного просмотра и непосредственного вывода на принтер.

* для выполнения запроса на выборку или на изменение.

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

* для установки значения любого элемента управления формы или отчета.

* для применения фильтра, перехода к любой записи и поиска данных.

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

* для выполнения любой команды любого меню Access.

* для перемещения, изменения размеров, сворачивания или восстановления любого окна внутри рабочей области Access.

* для вывода на экран информационных сообщений и подачи звуковых сигналов для привлечения внимания.

* для переименования любого объекта БД, копирования выбранного объекта в текущую или другую БД, импортирования, экспортирования или связывания таблиц из других БД.

* для запуска приложения, для обмена данными с приложением с помощью механизма DDE или буфера обмена.

Макросы особенно полезны для построения небольших персональных приложений. Изучение макросов - прекрасное введение в программирование в Access в целом.

Создание простого макроса

В окне БД перейдите на вкладку Макросы и нажмите кнопку Создать, чтобы открыть окно нового макроса. Верхняя часть окна используется для определения нового макроса, а нижняя предназначена для ввода значений аргументов макрокоманд, включенных в макрос. В верхней части присутствуют, по крайней мере, два столбца с заголовками Макрокоманда и Примечание. Вы можете увидеть все четыре столбца, нажав кнопки Имена макросов и Условия на панели инструментов. Обратите внимание на область в правой нижней части окна макроса, в которой выводится краткая справка. Содержание сообщения меняется в зависимости от положения курсора в верхней части окна макроса (для получения контекстной справки всегда можно нажать клавишу F1). В столбце Макрокоманда задается одна из более 49 макрокоманд, предоставляемых Access. Если вы щелкните в любой ячейке столбца Макрокоманда, то в правом углу появится кнопка со стрелкой вниз. Нажатие этой кнопки открывает список макрокоманд. Чтобы познакомится с работой окна макроса, выберите в списке макрокоманду Сообщение. Она используется для открытия монопольного окна диалога с сообщением. Это прекрасный способ для ввода в приложение разного рода предупреждающих или информационных сообщений без создания для этого специальной формы.

Пусть, например, сообщение представляет собой приветствие. В соответствующую ячейку столбца Примечание введите текст. В нем можно вводить дополнительные комментарии, размещая их на пустых строках, не содержащих макрокоманд. После выбора макрокоманды Access выведет в нижней части макроса ее аргументы. Значение аргумента Сообщение представляет собой текст, который будет выводиться в окне диалога. Аргумент Сигнал задает, будет ли звуковой сигнал сопровождать появление окна диалога. Аргумент Тип позволяет вставить в окно сообщения значок. В ячейку аргумента Заголовок вводится текст, который появится в заголовке окна диалога.

Перед запуском макрос необходимо сохранить. Выберите команду Файл->Сохранить. Access откроет окно диалога для сохранения. Введите имя и нажмите кнопку ОК.

Некоторые макросы могут быть запущены непосредственно из окна БД или окна макроса, поскольку они не зависят от элементов управления открытой формы или отчета. Если макрос зависит от формы или отчета, его надо связать с соответствующим событием и запускать при возникновении этого события. Перед запуском макроса неплохо проверить его работу, выполнив макрокоманды в пошаговом режиме. Чтобы начать пошаговую проверку, перейдите в окно БД, выделите имя макроса, который вы хотите протестировать и нажмите кнопку Конструктор. После открытия окна макроса нажмите кнопку По шагам на панели инструментов либо выберите команду Запуск->По шагам. Теперь после запуска макроса Access будет открывать окно диалога Пошаговое исполнение макроса перед выполнением каждого шага. В этом окне вы увидите имя макроса, название макрокоманды, условие ее выполнения и аргументы макрокоманды. Если во время выполнения приложения в каком-нибудь макросе встретится ошибка, то Access сначала выведет окно диалога, объясняющее ее. Затем вы увидите, похожее на Пошаговое исполнение макроса, окно диалога Ошибка макрокоманды с информацией о макрокоманде, вызвавшей ошибку. В этот момент можно нажать только кнопку Прервать и отредактировать макрос.

Группы макросов

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

Задание условий выполнения макрокоманд

Иногда необходимо, чтобы макрокоманды в макросе выполнялись только при определенных условиях. Например, вы хотите обновлять запись лишь в том случае, когда новые данные в элементах управления формы удовлетворяют условиям на значение или хотите выводить или скрывать определенные элементы управления в зависимости от значений других элементов управления. На вкладке Макросы выделите имя макроса и нажмите кнопку Конструктор, чтобы открыть окно макроса. Щелкните в столбце Условие на первой строке и нажмите клавиши Shift-F2, чтобы вывести на экран окно Область ввода. (Если столбец Условие не выводится, нажмите кнопку Условия на панели инструментов). При включении условия в макрос Access выполняет макрокоманду в этой строке только в том случае, если условное выражение имеет значение Истина. Ввод многоточия в столбце Условие в последующих строках позволяет распространить действие условия на ряд следующих макрокоманд. В этом случае Access вычисляет условное значение только один раз и выполняет группу макрокоманд, если условие, заданное для первой макрокоманды в этой группе, является истинным. Использование групп макросов позволяет легко находить и поддерживать нужные макросы. Вы можете создавать группы макросов не только по задачам, но и по функциям. Предположим, что имеется форма с рядом командных кнопок, каждой из которых назначен макрос, выполняющий определенную задачу. Вы можете собрать эти макросы в одну группу и дать ей имя, указывающее на связь с формой.

Обзор макрокоманд

Макрокоманды подразделяются на несколько классов, сформированных по функциональному принципу:

* Открытие и закрытие таблиц, запросов, форм и отчетов.

* Печать данных.

* Выполнение запроса.

* Проверка истинности условий и управление выполнением макрокоманд.

* Установка значений.

* Поиск данных.

* Построение специального меню и выполнение команд меню.

* Управление выводом на экран и фокусом.

* Сообщение пользователю о выполняемых действиях.

* Переименование, копирование, удаление, импорт и экспорт объектов.

* Запуск других приложений.

Открытие и закрытие таблиц, запросов, форм и отчетов

Макрокоманда Назначение Закрыть Закрывает заданное окно или активное окно таблицы, запроса, формы или отчета. Кроме того, вы можете указать, нужно ли сохранять закрываемый объект Открыть

Форму Открывает форму в режиме конструктора, формы или предварительного просмотра. При открытии можно применить фильтр или условие отбора. Открыть

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

Запрос Открывает запрос в режиме таблицы, конструктора, предварительного просмотра. Если указан запрос на изменение, то Access выполнит обновление данных в соответствии с запросом Открыть Отчет Открывает отчет в режиме конструктора, предварительного просмотра или печатает отчет. Можно указать фильтр или условие отбора. Открыть

Таблицу Открывает таблицу в режиме таблицы, конструктора или предварительного просмотра. Вы можете разрешить добавление, обновление или "только чтение" записей.

Печать данных

Макрокоманда Назначение Открыть

Форму Может открыть форму в режиме предварительного просмотра, вы можете применить фильтр или задать условие отбора. Открыть

Запрос Может открыть запрос в режиме предварительного просмотра. Открыть Отчет Печатает или открывает отчет в режиме предварительного просмотра. Вы можете применить фильтр или задать условие отбора Открыть

Таблицу По выбору открывает таблицу в режиме предварительного просмотра. Вывести в Формате Выводит заданный объект в файл .xls, .rtf, ,txt, а также запускает соответствующее приложение для редактирования файла. Печать Выполняет печать активной таблицы, формы, отчета или модуля. Вы можете задать диапазон печатаемых страниц, качество печати, число копий и печать с раскладкой по копиям.

Выполнение запроса

Макрокоманда Назначение Открыть Запрос Запускает запрос на выборку и выводит набор записей в режиме таблицы или предварительного просмотра. Выполняет запрос на изменение. Запуск Запроса SQL Выполняет инструкцию SQL: INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX.

Проверка истинности условий и управление выполнением макрокоманд

Макрокоманда Назначение Отменить Событие Отменяет событие, запускающее макрос. Макрокоманду Отменить Событие нельзя использовать в макросе, определяющем команды меню. Может отменять следующие события: Применение фильтра, До подтверждения DEL, До вставки, До обновления, Двойное нажатие кнопки, Удаление, Выход, Фильтрация, Форматирование, Нажатие клавиши, Кнопка вниз, Отсутствие данных, Открытие, Печать и Выгрузка. Команда Меню Выполняет команду стандартного меню. Вы можете использовать эту команду в макросе, определяющем специальную строку меню. Выход Закрывает все окна Access и завершает сеанс работы. Запуск Программы Выполняет процедуру-функцию VBA. Макрокоманды, следующие за данной, выполняются после завершения работы функции. Запуск Макроса Запускает другой макрос. Макрокоманды, следующие за данной, выполняются после завершения работы макроса. Остановить все Макросы Останавливает работу всех макросов, включая и макрос, запускающий эту макрокоманду. Остановить Макрос Прекращает выполнение текущего макроса

Установка значений

Макрокоманда Назначение Обновление Обновляет данные в элементе управления, связанном с запросом. Эту команду также можно использовать без аргументов для обновления данных в активном объекте (в режиме формы или таблицы). Команды Клавиатуры Сохраняет последовательность нажатий клавиш в специальном буфере. Если последовательность нажатия клавиш нужно переслать в монопольную форму или окно диалога, то эта макрокоманда должна быть выполнена до открытия монопольной формы или окна диалога. Задать Значение Изменяет значение любого элемента управления или свойства, которое можно обновлять.

Поиск данных

Макрокоманда Назначение Применить

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

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

Построение специального меню и выполнение команд меню

Макрокоманда Назначение Добавить Меню Добавляет раскрывающееся меню в специальную строку меню либо специальное контекстное меню для формы или отчета. Это единственная макрокоманда, которую можно использовать в макросе, указанном в свойстве Строка меню или Контекстное меню. Задать Команду Меню Эта макрокоманда задает состояние (доступна или нет, отмечена или нет) команды специальной строки меню или специального контекстного меню. Команда Меню Выполняет команду одного из стандартных меню Access

Управление выводом на экран и фокусом

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

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

объект Выделяет заданный объект БД. Восстанавливает окно объекта до обычного размера, если оно было свернуто в значок. Если объект находился в процессе открытия, то эта макрокоманда заставляет Access закончить открытие до перехода к другим действиям. Установить

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

инструментов Выводит на экран или скрывает любую из стандартных или специальных панелей инструментов.

Сообщения пользователю

Макрокоманда Назначение Сигнал Выдает звуковой сигнал Сообщение Выводит предупреждающее или информационное сообщение, может подавать звуковой сигнал. Чтобы закрыть окно диалога и продолжить работу, вы можете нажать кнопку ОК. Установить

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

Переименование, копирование, удаление, сохранение, импорт и экспорт объектов

Макрокоманда Назначение Копировать объект Копирует объект текущей БД в другую БД Access или в ту же самую под новым именем Удалить объект Удаляет любой объект Access Сохранить Сохраняет любую таблицу, запрос, форму, отчет, макрос или модуль Вывести в Формате Выводит заданный объект в формате Excel, Word, Notepad, а также запускает соответствующее приложение для редактирования файла Переименовать Присваивает новое имя заданному объекту в текущей БД Отправить объект Выводит таблицу, запрос или форму в режиме таблицы, данные полей отчета или листинг модуля в файл Excel, Word, Notepad и внедряет данные в сообщение электронной почты. На вашем компьютере должно быть установлено программное обеспечение, отвечающее стандарту MAPI Преобразовать Базу Данных Используется при экспорте или импорте данных между текущей и другой БД Access, dBASE, Paradox, FoxPro, SQL. Преобразовать Электронную Таблицу Используется при экспорте или импорте данных между текущей БД и файлами электронных таблиц Excel, Lotus 1-2-3 Преобразовать текст Используется при экспорте или импорте данных между текущей БД и текстовым файлом

Запуск других приложений для MS-DOS и Microsoft Windows

Макрокоманда Назначение Запуск Приложения Запускает приложение MS-DOS или Microsoft Windows

Автоматизация приложения с помощью макросов

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

Ссылки на формы и отчеты

Вы можете ссылаться на форму или отчет по имени, но прежде нужно сообщить Access в какое семейство входит данный объект. Открытые формы находятся в семействе Forms, открытые отчеты - в семействе Reports. Поэтому ссылка на форму или отчет включает в себя имя семейства, за которым следует восклицательный знак, а затем - имя объекта. Если имя объекта не содержит пробелов или специальных символов, то можно просто ввести это имя. Если же пробелы или специальные символы есть, то имя необходимо заключить в квадратные скобки.

Ссылки на свойства форм и отчетов

Для задания ссылки на свойство формы или отчета укажите идентификатор формы или отчета, затем введите точку и после точки добавьте имя свойства. Большинство свойств формы или отчета можно увидеть в режиме конструктора, открыв окно свойств. В Access многие свойства формы можно изменять в режиме формы, а свойства отчета Печать и Форматирование - во время вывода на экран или печати отчета.

Ссылки на элементы управления форм и отчетов и на их свойства

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

Ссылки на подчиненные формы и отчеты

Форма, внедренная в другую форму или отчет, содержится в элементе управления Подчиненная форма. Отчет, внедренный внутрь другого отчета, содержится в элементе управления Подчиненный отчет. Ссылаться на подчиненную форму или отчет можно точно так же, как и на любой другой элемент управления. Элемент управления Подчиненная форма имеет специальное свойство Form, которое позволяет ссылаться на форму, внедренную в этот элемент управления. Аналогично, Элемент управления Подчиненный отчет имеет свойство Report, позволяющее ссылаться на отчет, внедренный в этот элемент управления.

Открытие связанной формы

Использование подчиненной формы непрактично в следующих случаях:

* Для просмотра связанных данных требуется внедрение двух или больше подчиненных форм;

* Основная форма слишком мала для отображения всей подчиненной формы;

* Связанная информация вам требуется только время от времени;

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

Синхронизация связанных форм

При открытии дополнительной формы из основной на основе совпадения двух связанных полей в этих формах, данные в дополнительной форме не изменяются при перемещении по записям в основной форме. Необходимо создать макрос для синхронизации вывода данных в двух связанных формах. В окне БД перейдите на вкладку Макросы и нажмите кнопку Создать. Создайте макрос, выбирая макрокоманды в столбце Макрокоманды и вводя соответствующие комментарии в столбце Примечания. Этот макрос имеет пару условий выполнения макрокоманд. Первое условие можно выразить словами: Если основная форма не загружена, выполнить первую макрокоманду - Остановить макрос. Таким образом, если основная форма не открыта, то выполняется макрокоманда Остановить макрос и работа макроса прекращается. Вы можете использовать макрокоманду Применить фильтр для отбора записей в основной форме, но она работает только в том случае, если в текущий момент форма имеет фокус. Макрокоманда Выделить объект позволяет решить эту задачу, но зачем использовать две макрокоманды, если достаточно одной? Оказывается, что вы можете снова выполнить макрокоманду Открыть форму с тем же самым условием отбора, чтобы вывести в форме нужную строку. Если форма уже открыта, макрокоманда Открыть форму устанавливает в ней фокус и применяет заданное условие отбора. Однако ссылаться на "пустое" значение не очень хорошо. При переходе на последнюю запись в основной форме или выборе команды Правка->Перейти->Новая запись вы окажетесь на новой записи, в которой связанное поле не имеет значения. Поэтому имеет смысл организовать проверку значения Null, чтобы скрыть дополнительную форму при нахождении на пустой строке в основной форме. Для проверки этого условия во второй строке макроса используется встроенная функция IsNull. Если значение связанного поля равно Null, макрос скрывает дополнительную форму, устанавливая ее свойство Вывод на экран в значение Ложь. Обратите внимание, что хотя форма не видна на экране, она все равно остается открытой. После создания синхронизирующего макроса необходимо связать его со свойством Текущая запись основной формы. Перейдите на вкладку Формы окна БД и откройте основную форму в режиме конструктора. В конструкторе форм нажмите кнопку Свойства на панели инструментов, затем щелкните в ячейке свойства Текущая запись и в раскрывающемся списке выберите созданный макрос. Сохраните форму и откройте ее в режиме формы, проверьте работу созданного макроса, переходя на другие записи в основной форме. При переходе на пустую запись, дополнительная форма должна исчезнуть с экрана.

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

Создание специальной панели инструментов для форм

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

Чтобы создать и открыть новую нестандартную (специальную) панель инструментов, щелкните в окне БД, выберите команду Вид->Панели инструментов и нажмите кнопку Создать в окне диалога Панели инструментов. Access предоставит вам возможность присвоить панели инструментов содержательное имя. После появления новой панели вы можете нажать кнопку Настройка в окне диалога Панели инструментов, чтобы добавить в нее кнопки и расположить их по своему усмотрению, вы также можете изменить внешний вид кнопок.

Создание специальной строки меню для форм

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

Чтобы определить специальную строку меню, сначала создайте макрос строки меню, определяющий меню, которые будут появляться в этой строке. Для каждого меню вы должны включить в макрос макрокоманду Добавить Меню, и в качестве значения аргумента Название меню указать название этого меню. При этом аргумент Имя макроса должен указывать на макрос, определяющий команды меню. Символ & (амперсанд) определяет так называемую назначенную клавишу для этого меню, с его помощью вы можете определить для каждой команды клавишу быстрого доступа. Вы можете вставить линию между разделами меню, введя в столбец Имя макроса знак "минус" (-). Макрос, определяющий команду меню, может содержать либо другую макрокоманду Добавить Меню, добавляющую подчиненное меню, либо последовательность макрокоманд, которые будут выполняться при выборе команды. В большинстве случаев вы будете использовать макрокоманду Команда Меню, чтобы сделать команду встроенного меню доступной из специальной строки меню. Но можно выполнять и любые другие макрокоманды, включая те, что запускают другой макрос или функцию VBA. Определение полного набора макросов - довольно трудоемкий процесс, особенно в том случае, если вы хотите создать различные специальные строки меню для разных форм. К счастью, при создании макросов меню в Access вы можете воспользоваться помощью построителя меню. Переключитесь в окно БД и выберите команду Сервис->Надстройки->Построитель меню. Access запустит построитель меню и выведет на экран его окно диалога. Здесь перечислены все макросы в текущей БД. Выделите имя макроса, определяющего строку меню, нажмите кнопку Изменить и просмотрите определение строки меню. При необходимости внесите в него изменения. В этом же окне можно удалить макрос, выделив его имя и нажав кнопку Удалить. При удалении макроса строки меню построитель меню удалит также макросы, определяющие меню, входящие в эту строку. Чтобы начать определение нового набора макросов для строки меню, нажмите кнопку Создать. Построитель меню выведет на экран окно диалога для выбора шаблона. Поскольку мы хотим лишь немного изменить строку меню для всех форм в текущем приложении, хорошей основой может стать строка меню Форма. Выберите шаблон Форма и нажмите ОК. Построитель меню выведет этот шаблон в окне диалога Построитель меню - [Новая строка меню]. В списке, расположенном в нижней части окна диалога, вы можете выбрать любой элемент определения строки меню и просмотреть его подробное описание. Если этот элемент выполняет макрокоманду, то в верхней части окна вы увидите имя макрокоманды и ее аргументы. Построитель меню поддерживает следующие макрокоманды: Добавить Меню, Команда Меню, Запуск Макроса и Запуск Программы. Если вам нужно определить команду меню, выполняющую более сложное действие, вы можете с помощью построителя меню создать скелет макроса, а затем отредактировать его. Для перемещения выделенного элемента в списке можно пользоваться кнопками со стрелками. Нажатие кнопки Далее перемещает выделение на следующий элемент в списке.

Во многих случаях, вы, наверное, не захотите, чтобы пользователь мог открывать объекты в режиме конструктора или создавать новые объекты. Тогда удалите команду Создать из меню Файл. Вероятно вам не потребуются команды Сохранить форму и Сохранить форму как/Экспорт. Из меню Вид можно удалить команды Конструктор форм, Панели инструментов. Скорее всего, вам не понадобятся команды Скрыть, Показать и По размеру формы из меню Окно.

Оптимизация базы данных с помощью анализатора быстродействия

Даже опытные разработчики приложений Access не всегда используют все имеющиеся средства для повышения эффективности работы приложения. Access предоставляет анализатор быстродействия, который помогает выполнить окончательный анализ после построения основных частей приложения. Выберите команду Сервис?Анализ?Быстродействие, и Access откроет начальное окно мастера. Это окно позволяет выбрать категорию анализируемых объектов. После выбора категории укажите конкретные объекты, установив флажки рядом с их именами. Если хотите проанализировать все объекты, нажмите кнопку Выбрать все. В случае ошибки можно нажать кнопку Очистить все и начать выбор заново. Чтобы запустить анализатор быстродействия нажмите кнопку ОК. После завершения работы мастер выведет окно с результатами анализа. Это окно содержит список рекомендаций, предложений и идей. Выберите понравившиеся и нажмите кнопку Применить. Примененные рекомендации и предложения мастер помечает "галочкой". Идеи, предлагаемые мастером, нельзя применить прямо из анализатора быстродействия. Использование некоторых из них может быть связано с большим объемом дополнительной работы.

Создание начальной кнопочной формы

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

Здесь стоит обратить внимание на использование амперсанда (&) в значениях свойства Подпись командных кнопок. С помощью этого символа определяется назначенная клавиша для командной кнопки. Главное - быть внимательным и не использовать одну и ту же назначенную клавишу для разных кнопок.

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

Установка параметров запуска БД

На данные момент вы знаете, как построить основные части своего приложения. Но как сделать, чтобы при открытии БД автоматически запускалось приложение? Можно создать макрос с именем Autoexec, и Access будет запускать его при каждом открытии БД. Но более удобный способ заключается в использовании параметров запуска для задания начальной формы приложения. Переключитесь в окно БД и выберите команду Сервис?Параметры запуска. В окне диалога Параметры запуска нажмите кнопку Дополнительно, и вы увидите окно диалога Параметры запуска. Поле со списком позволяет выбрать форму, которая будет выводиться на экран при открытии БД. В этом же окне можно задать заголовок и значок приложения, а также специальную строку меню и специальное контекстное меню, используемые по умолчанию в текущей БД. Если снять флажок Окно базы данных, то при запуске приложения Access будет скрывать окно базы данных. Точно также можно скрыть строку состояния, сняв флажок Строка состояния. Access имеет набор встроенных меню с сокращенным списком команд, которые, в частности не предоставляют доступ к средствам разработки. Если флажок Полный набор меню Access снят, то по умолчанию будет использоваться набор кратких меню.

С помощью флажка в нижней части окна диалога Параметры запуска можно сделать невозможным просмотр программы после не перехваченных ошибок.

4. Задание на лабораторную работу

Спроектировать формы:

- для ввода, просмотра и поиска данных в базе;

- для соответствующих разработанных запросов;

- для учета, при необходимости, логически связанных объектов, а также возможности просмотра отчета;

- на основании информации, хранимой в базе, сформировать отчет в виде таблицы Код накладной

Название товара Цена, руб. Количество Стоимость Итого *****

- спроектировать главную кнопочную форму, используя рисунки и кнопки в качестве элементов управления. Кнопкам назначить макросы, открывающие созданные формы и отчет, а также поместить кнопку для выхода из приложения.

5. Содержание отчета

Отчет по лабораторной работе должен содержать:

- первая часть - файлы с разработанными формами и отчетами;

- вторая часть - файл в формате WORD ЛАБОРАТОРНАЯ_3.doc с отчетом о выполненной работе. Отчет содержит краткие пояснения к лабораторной работе и выводы.

ЛАБОРАТОРНАЯ РАБОТА №4

Создание баз данных в SQL Server Management Studio

1. Цель работы

Научиться создавать базы данных, изучить систему именования объектов, а также порядок связывания таблиц с целью построения результативной и эффективной БД, которая полностью удовлетворяет требованием заказчика в с помощью Transact-SQL

2. Общие рекомендации по созданию таблиц и схемы данных

с помощью Transact-SQL

База данных представляет собой группу файлов, хранящихся на жестком диске. Эти файлы являются лишь предварительно локализованным местом на жестком диске, где хранятся такие объекты SQL Server, как таблицы и представления. Эти файлы на жестком диске могут относиться к трем типам: файлы с первичными данными, файлы с вторичными данными и файлы журналатранзакций.

Первичный файл данных (с расширением .MDF) является первым файлом, созданным для базы данных. Этот файл можно использовать для хранения двух типов объектов: пользовательских и системных. К пользовательским объектам относятся таблицы, представления, хранимые процедуры, а также объекты, которые используются для модификации или хранения информации, введенной пользователем. Системные таблицы содержат информацию, требуемую SQL Server для поддержки такой функциональности базы данных, как имена таблиц, локализация индексов, учетные записи пользователей базы данных, а также информация о других объектах. Системные таблицы должны храниться в файле с первичными данными, а пользовательскую информацию и другие объекты можно перемещать и в файлы со вторичными данными. Если вы выходите за пределы емкости жесткого диска, содержащего файл с первичными данными, то можете создать на отдельном жестком диске вторичный файл

данных (с расширением .NDF). После создания вторичного файла вы можете использовать

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

Третий тип файлов требует некоторых разъяснений. Файл журнала транзакций функционирует подобно постоянному резервному копированию путем хранения транзакций. Транзакцией называется группа команд модификации данных (например, INSERT, UPDATE или DELETE), содержащаяся в блоке BEGIN TRAN. . .COMMIT и выполняющаяся как единое целое. Это означает, что в базе данных будут выполнены либо все команды транзакции, либо ни одна. SQL Server понимает два типа транзакций: неявные и явные. Неявная транзакция выполняется при отправке команды модификации данных программе SQL Server без упаковки в блок BEGIN TRAN. . . COMMIT. В этом случае SQL Server добавит этот блок за вас. Явная транзакция выполняется при вводе инструкций BEGIN TRAN и COMMIT в начале и конце блока инструкций. Типичная явная транзакция выглядит следующим образом:

BEGIN TRAN

INSERT RECORD DELETE RECORD

COMMIT TRAN В данном примере SQL Server "видит" команды INSERT и DELETE как единый модуль модификации. Эти команды либо выполняются обе, либо не выполняются вообще. В терминологии SQL Server это означает, что произойдет либо выполнение, либо откат. Команда DELETE не может выполниться без команды INSERT и наоборот. В SQL Server каждая команда, модифицирующая данные, интерпретируется как транзакция и содержит команды BEGIN и COMMIT независимо от вашего желания (если вы не добавите эти команды сами, то это сделает за вас SQL Server). Если вы думаете, что все эти транзакции записываются прямо в файл базы данных, то заблуждаетесь. Когда пользователь пытается модифицировать запись в базе данных, SQL Server локализует страницу данных в базе данных, содержащую указанную запись. После локализации данная страница загружается в особую область памяти, называемую кешем данных, который использует SQL Server для хранения модифицируемых данных. Все изменения страницы находятся в оперативной памяти, поскольку она работает примерно в 100 раз быстрее жесткого диска.

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

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

Ранее мы уже говорили, что база данных содержит по крайней мере два файла: первичный файл данных (с расширением . MDF) и файл журнала транзакций (с расширением . LDF). Вам также могут потребоваться вторичные файлы данных, если жесткий диск с первичным файлом переполнен, так что мы обсудим эти вопросы далее в этой главе. Для создания базы данных вам нужно лишь создать первичный файл данных и файл журнала транзакций. Вы можете выполнить эту задачу двумя способами:

• графически с помощью SQL Server Management Studio,

• посредством кода Transact-SQL.

Самый простой способ создания базы данных состоит в использовании утилитыSQL Server Management Studio. Мы создадим базу данных BookShopDb, которую позже заполним таблицами, представлениями и другими объектами, предназначенными для отдела продаж.

1. Откройте SQL Server Management Studio и выполните подключение с использованием аутентификации Windows.

2. В окне Object Explorer найдите и раскройте папку Databases.

3. Щелкните правой кнопкой мыши на папке Databases и выберите команду New Database.

4. В левой панели вы увидите список Select A Page. Перейдите во вкладку General и введите следующую информацию:

Database name: BookShopDb

Owner: sa

Collation: [ , . . . n ] ]

[ , [ , . . . n ] ]

] [ [ LOGON { [ , . . . n ] } ]

[ COLLATE имя_сортировки ]

• [ FOR { ATTACH [ WITH ]

| ATTACH_REBUILD_LOG } ]

[ WITH ] ] [ ; ]

::=

[ PRIMARY ] ( [ NAME = логическое_имя_файла , ]

FILEMANE = ' имя_файла_ОС

[ , SIZE = размер [ KB | MB | GB | ТВ ] ]

[ , MAXSIZE = {макс_размер [ KB | MB | GB | ТВ ] | UNLIMITED } ]

[ , FILEGROWTH = приращение_размера [ KB | MB | % ] ]

) [ ,...n ] ::=

FILEGROUP имя_группы_файлов

[ ,...n ]

::= DB_CHAINING { ON | OFF }

I TRUSTWORTHY { ON | OFF }

::= ENABLE_BROKER

| NEW_BROKER

I ERROR_BROKER_CONVERSATIONS

Далее следует описание каждого элемента синтаксиса.

• имя_базы_данных. Имя новой базы данных длиной до 128 символов.

• ON. Эта опция указывает на файловую группу, на основе которой следует создавать

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

PRIMARY, в которую по умолчанию входят все созданные файлы, и которая является единственной группой файлов, содержащей первичный файл данных.

• PRIMARY. Эта опция указывает на то, что связанный список определяетпервичные файлы. Первичные файлы содержат логическое начало базыданных и все требуемые системные таблицы.

• LOG ON. Данная опция указывает место и размер создаваемых файлов журналов. Если опция LOG ON не задана, то SQL Server создаст файл журнала с размером 25% от размера всех файлов данных и сгенерированным системой именем, и при этом поместит его в тот же каталог, где лежат файлы данных. Опцию LOG ON лучше всего использовать для помещения файла журнала транзакций на отдельный физический диск, чтобы в случае аварийного отказа системы вы могли получить доступ ко всем транзакциям, выполненными до сбоя.

• COLLATE имя_сортировки. Это предложение указывает на сортировку для базы данных, используемую по умолчанию с именем сортировки Windows или SQL Server.

• FOR ATTACH. Используйте это предложение для создания базы данных путем присоединения существующего набора файлов базы данных. Чтобы присоединить базу данных, вам потребуется файл .mdf и все файлы . ndf. Если у вас множество файлов данных и журналов, убедитесь, что собраны все файлы, иначе эта команда не будет выполнена.

• FOR ATTACH_REBUILD_LOG. Вы можете использовать это предложение для создания базы данных путем присоединения существующего набора файлов базы данных, причем вам не понадобятся все файлы журналов. Данную команду удобно использовать, когда в целях отчетности требуется хранить копию базы данных "только для чтения" на другом сервере. При использовании этого предложения вам не потребуется копировать все файлы журналов транзакций (. ndf), однако потребуются все файлы данных (.mdf).

• NAME. Данная опция указывает логическое имя базы данных, которое будет применяться для ссылки на нее из кода Transact-SQL. При использовании опции FOR ATTACH этот параметр не требуется.

• FILENAME. Это имя и путь файла базы данных, хранящегося на жестком диске. Каталог должен быть локальным (не сетевым), и его нельзя сжимать.

• SIZE. Исходный размер файлов данных. Его можно задать в килобайтах или мегабайтах. Если вы не предоставите размер для исходного файла данных, то SQL Server сгенерирует файл с размером, который задан в системной базе данных Model. Если же не предоставить размер для вторичного файла, то SQL Server автоматически выберет размер 1 Мбайт.

• MAXSIZE. Максимальный размер, до которого может расти база данных. Его также можно задать в мегабайтах или килобайтах. В качестве альтернативы вы можете задать опцию UNLIMITED, разрешающую SQL Server расширять файл данных до размера всего жесткого диска.

• UNLIMITED. Эта опция указывает, что определяемый файл может расти до тех пор, пока не заполнит весь диск. Использовать ее не рекомендуется, разве что в том случае, если файл помещается на специально выделенный диск.

• FILEGROWTH. Это приращение расширения файла. Оно задается в мегабайтах, килобайтах или процентах (%). Если единица измерения не задана, то задается размер в мегабайтах.

• FILEGROUP. Данная опция указывает логическое имя для группы файлов, на основе которых создается файл данных.

• DB_CHAINING{ON | OFF}. Если пользователь извлекает из представления данные, то оно должно иметь доступ к соответствующим таблицам. Связь между представлением и таблицами называется цепочкой принадлежности. Если задать этой опции значение ON, то база данных сможет стать началом или концом цепочки принадлежности. В случае присваивания этой опции значения OFF база данных не сможет быть вовлечена в цепочку.

• TRUSTWORTHY{ON | OFF}. Если этой опции задать значение ON, то модули базы данных, использующие имперсонифицированный контекст, смогут получать доступ к ресурсам вне базы данных. Задавать эту опцию могут только члены фиксированной серверной роли sysadmin.

• ENABLE_BROKER. Данная опция указывает на то, что для базы данных включен брокер служб.

• NEWBROKER. Эта опция активизирует в базе данных брокер служб с идентификатором

service_broker_guid.

• ERROR_BROKER_CONVERSATION. Эта опция заканчивает все диалоги с ошибкой,

указывающей на создание копии брокера.

Настройка параметров базы данных

Если вы покупали новый автомобиль или хотя бы разговаривали с продавцами, то знаете что машины продаются с некоторыми функциональными элементами, напри мер радио и кондиционером, не входящими в минимальный набор. Эти элементы могут заставить автомобиль вести себя несколько иначе. Базы данных SQL Server также имеют свои опции - параметры, от значения которых зависит их поведение. Перед тем как использовать базу данных, мы рассмотрим некоторые из этих настроек. Большинство настроек баз данных можно задавать с помощью утилиты SQL Server Management Studio. Если вы щелкнете правой кнопкой мыши на одной из баз данных выберете в контекстном меню пункт Properties и откроете вкладку Options, то увидите окно.

• Параметр Auto Close. Когда пользователь подключается к базе данных, она должна быть открыта. Когда база данных открыта, она потребляет такие ресурсы, как оперативная память и процессор. Если данному параметру присвоить значение True, то после отключения последнего пользователя база данных будет закрыта. Поскольку, как правило, в любой системе избытка ресурсов не наблюдается, по умолчанию в редакции Express Edition этому параметру присвоено значение True. Таким образом, неиспользуемая база данных будет закрываться.

Во всех остальных версиях этому параметру присвоено значение False - пользователи смогут открывать и закрывать базу данных днем и ночью, однако это существенно затормозит работу системы.

• Параметр Auto Create Statistics. При отправлении запроса на сервер базы данных он перехватывается оптимизатором запросов, единственное назначение которого состоит в поиске самого быстрого способа возврата результирующего набора данных. Он выполняет эту задачу путем считывания статистики о каждом столбце, упомянутом в инструкции SELECT (эта статистика основана на количестве уникальных значений в столбце и количестве дубликатов). Если этому параметру присвоить значение True, то SQL Server автоматически создаст статистику для любого столбца, являющегося частью индекса. Если же присвоить значение False, то вам придется создавать собственную статистику. Опять-таки, лучше всего оставить этот параметр включенным до тех пор, пока вы не станете достаточно хорошо разбираться в SQL Server, чтобы мудрить с оптимизатором запросов.

• Параметр Auto Shrink. SQL Server периодически сканирует базы данных, чтобы определить не содержат ли они более 25% свободного пространства. Если это так, то SQL Server может автоматически уменьшить размер базы данных, чтобы в ней было лишь 25% свободного пространства. Если присвоить этому параметру значение True (как это делается по умолчанию в Desktop Edition), то может выполняться автоматическое сжатие базы данных. Лучше всего оставить этот параметр со значением по умолчанию, поскольку процесс сжатия может затребовать дополнительные системные ресурсы на сервере - о дисковом пространстве можно позаботиться и самому. Позже мы обсудим процесс сжатия баз данных вручную.

• Параметр Auto Update Statistics. Присваивание этой опции значения True укажет SQL Server время от времени автоматически обновлять статистику. Если же ей задать значение False, то вам придется обновлять статистику вручную. Если у вас дефицит системных ресурсов (таких как процессор и оперативная память), отключите эту опцию. Вы можете создать план поддержки базы данных, который будет выполнять эту задачу по указанной схеме.

• Параметр Cursor Close On Commit Enabled. Курсор можно представить себе как итоговый набор данных. Курсоры возвращают отдельные строки данных и поэтому ускоряют процесс их извлечения в случае большого итогового набора. Если присвоить этому параметру значение True, то курсоры будут закрываться сразу же после подтверждения транзакций. Будет благоразумным оставить данный параметр со значением False, чтобы курсоры оставались открытыми до завершения процесса модификации данных. После этого курсор можно закрыть

вручную.

• Параметр Default Cursor. Если присвоить этому параметру значение Local, то любой созданный курсор будет локальным для процедуры, его вызвавшей. Это означает, что если вы выполняете хранимую процедуру (предварительно записанный запрос, хранящийся в SQL Server), которая создает курсор, то этот курсор сможет использовать только данная хранимая процедура. Если же данному параметру присвоить значение Global (значение по умолчанию), то использовать созданный курсор смогут все процедуры, используемые тем же подключением. Поэтому, если некий Вася выполняет хранимую процедуру, создающую курсор, то со значением Global данного параметра созданный курсор сможет использовать любая иная процедура, выполняемая им. Если же этому параметру присвоить значение Local, то на курсор сможет ссылаться только та хранимая процедура, которая его создала.

• Параметр ANSI Null Default. При создании таблицы в SQL Server вы можете определить в ней возможность наличия пустых столбцов - так называемое условие пустого значения. Если при создании или модификации таблицы вы явно не задали возможность появления пустых значений, а этой опции задано значение False, то в ваших столбцах не сможет быть значений null. Если присвоить этому параметру опции значение True и не предусмотреть возможности появления пустых значений, то столбцы все равно смогут принимать значения null . Этот параметр используется по усмотрению. Если большинство ваших столбцов не должны содержать значения null , то эту опцию лучше оставить со значением по умолчанию False .

• Параметр ANSI Nulls Enabled. Если этому параметру присвоить значение True, то любое сравнение с пустым значением n u l l будет выдавать результат n u l l . Если же ей присвоить значение F a l s e , то сравнения данных не в кодировке Unicode со значениями n u l l будут выдавать F a l s e , а сравнения значений n u l l со значениями n u l l будут выдавать True. По умолчанию этой опции присваивается

значение F a l s e .

• Параметр ANSI Padding Enabled. Эта настройка управляет методом хранения в столбце значений короче определенного размера. Если ей присвоить значение True, то столбцы с типами данных c h a r (л) NOT NULL, c h a r (л)NULL, и b i n a r y (л) NULL будут дополняться до длины столбца, а столбцы с типами v a r c h a r (л) и v a r b i n a r y (л) не будут дополняться, и данные не будут обрезаться. Если же ей присвоить значение F a l s e , то столбцы с типами данных c h a r (л) NOT NULL и c h a r (л) NULL будут дополняться до длины столбца, а столбцы с типами c h a r (л) NULL, b i n a r y (л) NULL, v a r c h a r (л) и v a r b i n a r y (л) не будут дополняться, что приведет к сжатию данных.

• Параметр ANSI Warning Enabled. Мы знаем, что деление на нуль невозможно, но об этом следует сообщить и компьютеру. Если присвоить этому параметру значение F a l s e и попытаться выполнить деление на нуль или использовать значение n u l l в математическом уравнении, то в результате вы получите значение n u l l , а ошибки не будет. Если же присвоить параметру значение True, то вы получите предупреждение. По умолчанию этому параметру присваиваетсязначение F a l s e .

• Параметр Arithmetic Abort Enabled. Эту опция указывает SQL Server, что делать в случае переполнения или арифметической ошибки деления на нуль. Если ей присвоить значение True, то будет выполнен откат всего запроса или транзакции. Если же присвоить данной опции значение F a l s e , то запрос или транзакция продолжат выполняться, но будет выдано предупреждение.

• Параметр Concatenate Null Yields Null. Конкатенация строк комбинирует множество строк в одну с помощью оператора +. Например, конкатенация Привет, меня зовут + Вася вернет Привет, меня зовут Вася в виде одной строки. Если присвоить этому параметру значение True и попытаться выполнить

конкатенацию Привет, меня зовут + n u l l , то вы получите значение n u l l . Если же присвоить параметру значение F a l s e и попытаться выполнить конкатенациюПривет, меня зовут + n u l l , то вы получите строку Привет, менязовут. Значение F a l s e принято по умолчанию.

Встроенные типы данных

• bit. Этот тип данных может содержать только значения 0 или 1 (или "пустое" значение null). Его очень удобно использовать в качестве двоичного индикаторасостояния - on/off, yes/no, t r u e / f a l s e .

• int. Этот тип может содержать целочисленные данные от (-2 147 483 648) до (2 147 483 647). Он занимает 4 байт на жестком диске и удобен для хранения больших чисел, используемых в математических функциях.

• bigint. Тип данных включает в себя целочисленные данные от (-9 223 372 036 854 775 808) до (9 223 372 036 854 775 807). Он занимает 8 байт на жестком диске и удобен для хранения очень больших чисел, не помещающихсяв типе данных i n t .

• smallint. Этот тип данных содержит целочисленные данные от (-32 768) до (32 767). Он занимает 2 байт на жестком диске и удобен для хранения меньших чисел, чем те, которые хранятся в типе данных i n t .

• tiny int. Этот тип данных содержит целочисленные значения от 0 до 255. Он занимает один байт на жестком диске. Тип t i n y i n t удобно использовать для хранения чего-нибудь наподобие кода продукта, если у вас их не больше 255.

• decimal. Этот тип данных содержит числа с фиксированной точностью от -1038-1 до 1038-1 (единица с 38 нулями). Он использует два параметра: точность и степень. Точностью называется общее количество знаков, хранящееся в поле, а степень - это количество знаков справа от десятичной запятой. Таким образом,

если у вас имеется точность 5 и степень 2, то поле будет иметь формат 111,22. Этот тип данных следует использовать при хранении дробных чисел (чисел с десятичной запятой).

• numeric. Это синоним типа данных decimal - они идентичны.

• money. Этот тип данных содержит денежные значения от - 263(-922 337 203 685 477,5808) до 263 (922 337 203 685 477,5807) с десятитысячной точностью от денежной единицы. Он занимает 8 байт на жестком диске и удобен для хранения денежных сумм, превышающих 214 748,3647.

• smallmoney. Содержит значения от -214 748,3648 до 214 748,3647 с десятитысячной точностью. Он занимает 4 байт на жестком диске и удобен для хранения небольших денежных сумм.

• float. Содержит числа с плавающей запятой от -1.79Е + 38 до 1.79Е + 38. Некоторые числа вообще не заканчиваются после десятичной запятой, например число я. Такие числа нужно аппроксимировать, что и делается с помощью плавающей запятой. Если, к примеру, вы зададите тип данных f l o a t (2), то

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

• real. Содержит числа с плавающей запятой от -3,40Е + 38 до 3,40Е + 38. Например, r e a l (24) представляет тип с плавающей запятой и 24 знаками последесятичной запятой.

• datetime. Содержит дату и время в диапазоне от 1 января 1753 года до 31 декабря 9999 года с приращением значений ,000, ,003 и,007. Этот тип данных занимает 8 байт на жестком диске и должен использоваться при отслеживании специфических дат и времени.

• smaldatetype. Содержит дату и время, начиная с 1 января 1900 года и заканчивая 6 июнем 2079 года, с точностью до 1 минуты. Он занимает лишь 4 байт на жестком диске и должен использоваться для хранения дат и времени.

• timestamp. Используется для установки временнък меток записей при вставке, которые соответствующим образом обновляются. Удобен для отслеживания изменений в данных.

• uniqueidentifier. Функция NEWID () используется для создания глобально уникальных идентификаторов, которые выглядят примерно так: 6F9619FF-8B86-D011-B42D-00C04FC964FF. Эти уникальные числа могут храниться в поле с типом данных uniquidentif i e r и удобны для создания сопровождающих чисел

или серийных номеров, которые нельзя дублировать.

• char. Этот тип данных содержит символьные не Unicode-данные фиксированной длины до 8000 знаков. Он удобен для хранения символьных данных одинаковой длины. В качестве примера можно привести поле S t a t e , в каждойзаписи которого содержится только два символа. Этот тип данных постоянно использует один и тот же объем на диске независимо от количества символов, реально хранящихся в поле. Например, тип данных char (5) всегда использует 5 байт на жестком диске, даже если в поле содержится лишь два символа.

• varchar. Этот тип данных содержит не Unicode-данные переменной длины до 8000 символов. Он удобен для хранения данных переменной длины, например, для полей с именем и фамилией, где в каждой фамилии содержится разное число символов. Чем меньше символов хранится в этом типе, тем меньше места

он занимает на жестком диске. Например, если у вас имеется поле с типом данных varchar (20), в котором вы храните лишь 10 символов, то поле займет лишь 10 байт жесткого диска, а не 20. Максимальное количество символов, допустимое в типе varchar (20), равно 20.

• varchar(max). Этот тип подобен типу данных varchar с указанным максимально возможным размером (max) и может хранить 231-1 (2 147 483 67) байт данных.

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

• nvarchar. Этот тип содержит данные Unicode переменной длины до 4000 символов. Он не отличается от типа nchar, за исключением того, что тип nvarchar использует меньше дискового пространства при хранении меньшего количества символов.

• nvarchar(max). Этот тип подобен типу nvarchar с заданным максимально возможным размером (max) и может хранить 231-1 (2 147 483 67) байт данных.

• binary. Содержит двоичные данные фиксированной длины до 8000 байт. Интерпретируется как битовая строка (например, 11011001011) и удобен для хранения любых данных, которые лучше выглядят в двоичном или шестнадцатеричном виде, как, например, идентификатор защиты.

• varbinary. Содержит двоичные данные переменной длины до 8000 разрядов. Этот тип подобен типу данных binary за исключением того, что он использует меньше места на диске при хранении меньшего количества разрядов.

• varbinary(max). Этот тип имеет те же атрибуты, что и тип данных varbinary, но с декларированным размером (max). Может хранить 231-1 (2 147 483 67) разрядов данных. Удобен для хранения двоичных объектов наподобие файлов JPEG с изображениями или документов Word.

• xml. Этот тип данных используется для хранения целых документов или фрагментов XML (фрагмент - это документ без элемента высшего уровня).

• identity. На самом деле это вовсе не тип данных, но он выполняет важную роль. Это свойство, обычно используемое в конъюнкции с типом данных int, применяется для приращения значения столбца каждый раз при вставке новой записи. Например, первая запись таблицы имеет идентификационный номер

1, вторая строка - номер 2 и т.д.

• sql_variant. Подобно типу i d e n t i t y это вовсе не реальный тип данных; он используется для хранения значений с различными типами данных. В нем нельзя хранить лишь следующие значения: varchar (max), nvarchar (max), text, image, sql_variant, varbinary(max), xml, ntext, timestamp и типы данных, определяемые пользователем.

Создание таблиц с помощью мастера.

1. Откройте SQL Server Management Studio. В окне Object Explorer раскройте

папки Databases>BookShopDb.

2. Щелкните правой кнопкой на значке Tables и примените команду New Table, чтобы открыть конструктор таблиц.

3. В первую строку в столбце Column Name введите имя AuthorID.

4. В столбец Data Type введите тип smallint.

5. Убедитесь, что параметр Allow Nulls не включен. Со включенной этой опцией поле может быть вообще без данных.

6. В нижней половине экрана в разделе Table Designer блока Column Properties расширьте Identity Specification и измените значение параметра (Is Identity) в Yes.

7. Введите в столбец Column Name второй строки под AuthorID имя FirstName.

8. В столбец Data Type введите тип varchar(10).

9. В разделе General блока Column Properties определите для настройки Length значение 100.

10. Убедитесь, что параметр Allow Nulls отключен.

11. В столбец Column Name введите для третьей строки имя LastName.

12 В столбец Data Type введите тип varchar(30).

13. Аналогично заполняются остальные поля.

14. Сохраните созданную таблицу с именем Authors.

Использование ограничений проверки

Ограничение на проверку представляет собой инструкцию Transact-SQL связывания

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

полем, даже если они имеют корректный тип.

1. В окне Object Explorer раскройте папки базы данных BookShopDb и найдите в папке Tables таблицу dbo. Authors.

2. Щелкните правой кнопкой на папке Constraints и выберите команду New Constraint.

3. Введите в текстовый блок (Name) диалогового окна New Constraint имя CK_ YearBorn.

4. В текстовый блок Description введите описание Check for YearBorn codes.

5. Чтобы создать ограничение, принимающее четыре числа, которыми могут быть

только цифры от 0 до 9,причем первая цифра может принимать значение 1 или 2 в текстовый блок Expression введите следующий код

(YearBorn like '1 [0-9] [0-9] [0-9]' or YearBorn like '2 [0-9] [0-9] [0-9]')

6. Щелкните на кнопке Close.

7. Щелкните на кнопке Save в левой верхней части панели инструментов.

Добавление записей в таблицу.

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

1. В SQL Server Management Studio щелкните на кнопке New Query и выберите команду New SQL Server Query. В случае чего подключитесь к серверу с использованием аутентификации Windows.

2. Введите в окно запроса следующий код:

USE BookShopDb INSERT Authors

VALUES (1, 'Михаил', 'Лермонтов',1814, 1841)

3. Щелкните на кнопке Execute, чтобы выполнить запрос

Чтобы просмотреть новую запись, выполните команду Query1 New Query With Current Connection (комбинация клавиш ). Введите и выполните следующий код:

SELECT * FROM Authors

Использование первичных ключей

1. Откройте SQL Server Management Studio и выполните подключение с использованием аутентификации Windows.

2. В Object Explorer раскройте папки Database >BookShopDb >Tables.

3. Щелкните правой кнопкой мыши на таблице Authors и выберите команду Modify Table.

4. В окне конструктора таблиц щелкните правой кнопкой на поле AuthorID в столбце

Column Name и выберите команду Set Primary Key.

3 Пример выполнения работы.

Создание базы данных с помощью кода TRANSACT

1. Откройте Management Studio и выполните регистрацию с помощью аутентификации

Windows.

2. Выполните команду File New>New >SQL Server Query.

3. Для создания базы данных размером 10 Мбайт с именем BookShopDB на диске С с файлом журнала размером 2 Мбайт выполните следующий код (отметим, что вы должны заменить путь С : \ путем к диску, на котором у вас установлена программа SQL Server):

CREATE DATABASE BookShopDB

ON PRIMARY

(name = BookShopDB,

filename = 'c:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\data\ BookShopDB.mdf' ,

s i z e = 10MB,

maxsize = 15MB, filegrowth = 1MB)

LOG ON (name = BookShopLog,

filename = ' c:\Program Files\Microsoft SQL

Server\MSSQL.l\MSSQL\data\ BookShopLog.ldf' ,

s i z e = 2MB, maxsize = 3MB,

filegrowth = 10%)

4. На панели результатов окна запроса (внизу) вы должны увидеть сообщение об успешном выполнении команды. Чтобы проверить созданную базу данных, разверните сервер в Object Explorer, а затем раскройте папку Database. В списке доступных баз данных вы должны увидеть базу BookShopDB.

Создание таблиц с помощью TRANSACT-SQL. Использование ограничений PRIMARY KEY, FOREIGN KEY и ограничения проверки CHECK.

Создание таблицы Authors в базе BookShopDB

1. Откройте Management Studio и выполните команду File New>New >SQL Server Query.

2. Введите в окно запроса следующий текст

use[BookShopDB] CREATE TABLE Authors

(AuthorID smallint

IDENTITY(1.1) PRIMARY KEY CLUSTERED,

FirstName varchar(10) NOT NULL,

LastName varchar (30) NOT NULL,

YearBorn smallint NOT NULL

CONSTRAINT ck_YearBorn

CHECK (YearBorn LIKE '1[0-9][0-9][0-9]' or YearBorn LIKE '2[0-9] [0-9] [0-9]'),

YearDied char(4) NOT NULL DEFAULT '-')

3. Щелкните на кнопке Execute чтобы выполнить запрос и посмотрите на полученную таблицу в окне Object Explorer.

При создании таблицы Authors для колонки, содержащей идентификационный номер автора AuthorID имеющего тип данных smallint было использование ограничения первичного ключа. Также было использовано ключевое слово IDENTITY, которое предписывает системе осуществлять заполнение колонки автоматически. При его использовании необходимо указать начальное значение и приращение.

Использование ограничения проверки CHECK заключается в том, что в колонке года рождения можно ввести только четыре цифры от 0 до 9, причем при вводе в качестве первого символа разрешены только значения 1 и 2.

Использование ввода значения по умолчанию DEFAULT заключается в появление значка "-" при отсутствии введенных данных

4. Создание таблицы BookAuthors в базе BookShopDB

use[BookShopDB] CREATE TABLE BookAuthors

(AuthorID smallint

IDENTITY(1.1) PRIMARY KEY CLUSTERED,

TitleID сhar(8) NOT NULL

Title varchar(40) NOT NULL,

Genre char(10) NOT NULL,

BookDate smallint NOT NULL

CONTRAINT ck_ BookDate

CHECK (BookDate LIKE '1[0-9][0-9][0-9]' or BookDate LIKE '200[0-9]')

5. Щелкните на кнопке Execute чтобы выполнить запрос и посмотрите на полученную таблицу в окне Object Explorer.

Добавление ограничения PRIMARY KEY к таблице BookAuthors

При создании в базе данных BookAuthors таблицы BookShopDB мы создали первичный ключ. Но это можно сделать и после создания таблицы. Если вы забыли сразу определить ключи создайте следующий запрос на изменение таблицы:

1. Откройте Management Studio и выполните команду File New>New >SQL Server Query.

2. В раскрывающемся списке баз данных выберите BookShopDB.

3. Введите в окно запроса следующий текст

use[BookShopDB] ALTER TABLE Authors

ADD CONSTRAINT authors_pk PRIMARY KEY (AuthorID)

Добавление к таблице BookAuthors ограничения FOREIGN KEY

1. Откройте Management Studio и выполните команду File New>New >SQL Server Query.

2. В раскрывающемся списке баз данных выберите BookShopDB.

3. Введите в окно запроса следующий текст

use[BookShopDB]

ALTER TABLE BookAuthors

ADD CONSTRAINT authorid_fk FOREIGN KEY (AuthorID)

REFERENCES Authors (AuthorID)

4 Задание по выполнению лабораторной работы №4

1. Создать таблицу "Сотрудники" по следующей структуре:

Имя поля Табельный номер Дата рождения Фамилия Оклад Код отдела Отработано дней Премия Пол Тип Число дата текст число Число Число число текст

(1 символ) Значение по умолчанию - - - 200 - 0 0 м 2. Добавить ограничения на целостность данных.

* табельный номер является первичным ключом данной таблицы и он является уникальным;

* размер оклада должен быть в пределах 200-1000 р.

* премия - в пределах 0-600р.

* отработано дней- положительное число.

* пол может быть только "м" или "ж".

3. Создайте таблицу "Отделы" по следующей структуре:

Имя поля Код отдела Название отдела Количество сотрудников Табельный номер начальника отдела Тип Число текст число Число Значение по умолчанию - "неизвестно" 0 - 4. Добавить в первую таблицу "Сотрудники" ограничение ссылочной целостности FOREIGN KEY (внешний ключ) между столбцами [Код отдела] в таблицах "Сотрудники" и "Отделы".

5. Описать подробно ход выполнения работы.

5 Содержание отчета

Отчет по лабораторной работе должен содержать:

- первая часть - файлы с разработанными заданиями;

- вторая часть - файл в формате WORD ЛАБОРАТОРНАЯ_4.doc с отчетом о выполненной работе. Отчет содержит краткие пояснения к лабораторной работе и выводы.

ЛАБОРАТОРНАЯ РАБОТА №5

Модификация, добавление и удаление данных в базах данных SQL Server

1. Цель работы

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

2. Общие рекомендации по разработке запросов

Добавление данных с помощью оператора INSERT

Запросы на вставку предназначены для вставки в таблицу новых строк данных. Для этих запросов используется инструкция INSERT.

Синтаксис инструкции INSERT

INSERT [INTO] {

имя_таблицы [WITH (подсказка_таблицы [...n])]

I имя_представления

I OPENQUERY | OPENROWSET

} { [(список столбцов)]

{ VALUES ( { DEFAULT | NULL

| выражение }[,...n] )

I временная_таблица

| инструкция_выполнения

I DEFAULT VALUES

• Ключевое слово INSERT и необязательное ключевое слово INTO вводят инструкцию. Ключевое слово INTO используется для удобочитаемости.

• Аргумент имя_ таблицы задает целевую таблицу.

• По желанию вы можете включить подсказки таблицы.

• Вы также можете в качестве цели вставки задать аргумент имя_представления или результаты выполнения функций OPENQUERY и OPENROWSET.

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

• Вставляемые значения можно задать ключевыми словами DEFAULT, NULL или выражениями.

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

• Для создания вставляемых данных вы также можете использовать инструкцию_выполнения вместе с хранимой процедурой или пакетом SQL.

• Предложение DEFAULT VALUES использует значения таблицы по умолчанию для каждого столбца в новой строке.

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

Ограничения инструкции INSERT

Если вы вставляете данные в представление, а не таблицу, то представление должно быть обновляемым. Кроме того, с помощью одной инструкции INSERT вы можете вставить данные только в одну из базовых таблиц, на которую ссылается представление. Если вы не указали в явном виде список столбцов, то инструкция INSERT будет пытаться вставить значения в каждый столбец таблицы в порядке предоставления значений. Со списком столбцов или без него, инструкция INSERT работает только в том случае, если SQL Server может определить, какое значение вставлять в каждый столбец таблицы.

Примеры инструкции INSERT

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

use[Pubs]

CREATE TABLE NewBooks (

BookID INT NOT NULL,

BookTitle VARCHAR(80) NOT NULL,

BookType VARCHAR(10) NOT NULL DEFAULT('Undecided'),

PubCity VARCHAR(50) NULL,

Price INT NOT NULL DEFAULT(1),

CONSTRAINT prk PRIMARY KEY(bookID));

В созданную таблицу решено добавить строку с данными. В следующем операторе INSERT для добавления новой строки в таблицу NewBooks используется конструкция VALUES:

INSERT NewBooks

VALUES (1,'Life Without Fear', 'Chicago');

Добавление данных с помощью подзапроса SELECT

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

В следующем операторе INSERT для добавления строк в таблицу NewBooks используется подзапрос SELECT:

use[Pubs]

INSERT NewBooks

SELECT *FROM Titles

WHERE Type = 'mod_cook';

Синтаксис инструкции SELECT INTO

Инструкция SELECT INTO является вариацией простой инструкции SELECT.

Схематически ее синтаксис выглядит так:

SELECT select_list

INTO имя_новой_таблицы

FROM исходник_таблицы

[WHERE условие]

[GROUP BY выражение]

HAVING условие]

[ORDER BY выражение]

Новым ключевым элементом является предложение INTO. В нем вы можете задать

имя таблицы (с помощью любого корректного идентификатора SQL Server), и инструкция

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

В таблицах, создаваемых с помощью инструкции SELECT INTO, не содержатся индексы, первичные ключи, внешние ключи, значения по умолчания и триггеры. Если что-то из вышеперечисленного требуется, вам следует создать таблицу с помощью инструкции CREATE TABLE, а затем использовать инструкцию INSERT lkzзаполнения таблицы данными. Обычно это сделать проще, чем создавать таблицу с помощью инструкции SELECT INTO, а затем фиксировать другие свойства с помощью инструкции ALTER TABLE. Инструкцию SELECT INTO можно также использовать для создания временной таблицы. При этом первым символом в имени таблицы следует поставить знак решетки (#). SQL Server автоматически удаляет временные таблицы после окончания работы с ними.

Запросы на обновление

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

Синтаксис инструкции UPDATE

UPDATE { имя_таблицы [WITH (подсказка_таблицы [...п])]

I имя_представления

I OPENQUERY | OPENROWSET

} SET ( имя_столбца = {выражение | DEFAULT | NULL}

I ^переменная = выражение

I ^переменная = столбец - выражение

I имя_столбца {WRITE (выражение , ^Смещение, %Длина)

} [ , . . . п ]

{ [FROM {исходная_таблица} [ , . . . п ]]

[WHERE условие_поиска]

} [OPTION (подсказка_запроса [ , . . . п ] )]

Ниже приведен подробный анализ синтаксиса инструкции UPDATE.

• Ключевое слово UPDATE идентифицирует инструкцию.

• В качестве исходного материала для обновляемых строк нужно задать либо имя таблицы или представления, либо результаты выполнения функций OPENQUERY или OPENROWSET.

• Необязательное предложение WITH можно использовать для обеспечения подсказок оптимизатора для таблицы.

• Ключевое слово SET представляет вносимые изменения.

• Вы можете задать значение столбца равным выражению, значению по умолчанию или пустому значению NULL.

• Вы можете присвоить выражение локальной переменной.

• Вы можете объединить присвоение значения локальной переменной и столбцу в одном и том же выражении.

• Вы можете использовать предложение .WRITE для изменения части столбца типа varchar (max), nvarchar(max) или varbinary(max), задав смещение и длину.

• В одной директиве SET вы можете задать множество столбцов.

• Предложение FROM имеет тот же синтаксис и параметры, что и в инструкции SELECT

• Предложение WHERE имеет тот же синтаксис, что и в инструкции SELECT.

* задает подлежащие обновлению строки;

* указывает строки исходной таблицы, из которых извлекаются данные для обновления, если также задана конструкция FROM. Если конструкция WHERE не задана, обновляются все строки таблицы.

• Предложение OPTION используется для описания дополнительных параметров

Например, показанный далее оператор UPDATE включает конструкцию SET, которая увеличивает цену книг в таблице NewBooks на 10%:

use[Pubs]

UPDATE NewBooks SET Price = Price * 1.1;

Запросы на удаление

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

Синтаксис инструкции DELETE

Инструкция DELETE имеет множество опций, но базовый синтаксис ее довольно прост:

DELETE [FROM] (

имя_таблицы [WITH (подсказка_таблицы [ . . . п ] ] )

I имя_представления

I OPENQUERY | 'OPENROWSET | OPENDATASOURCE

} [FROM исходная_таблица]

[WHERE условия_поиска]

[OPTION параметры_запроса]

Проанализируем инструкцию DELETE.

• Ключевое слово DELETE идентифицирует инструкцию.

• Необязательное ключевое слово FROM можно использовать для удобочитаемости инструкции SQL.

• В качестве исходного объекта для удаляемых строк вам следует задать либо имя таблицы или представления, либо результаты выполнения функции OPENQUERY, OPENROWSET или OPENDATASOURCE.

• Необязательное предложение WITH можно использовать для обеспечения подсказок оптимизатора таблицы.

• Предложение FROM имеет такой же синтаксис и параметры, что и предложение FROM в инструкции SELECT.

• Предложение WHERE имеет такой же синтаксис и параметры, как и предложение WHERE в инструкции SELECT.

• Предложение OPTION можно использовать для обеспечения дальнейших подсказок.

use[Pubs]

DELETE FROM NewBooks

WHERE PubCity = 'Chicago'

Синтаксис инструкции TRUNCATE TABLE

Еще одной инструкцией, которую можно использовать для удаления строк, является инструкция TRUNCATE TABLE. Синтаксис инструкции TRUNCATE TABLE довольно прост:

TRUNCATE TABLE имя_таблицы

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

В качестве примера удалим все записи из таблицы NewBook:

TRUNCATE TABLE NewBook

Основы оператора SELECT

Оператор SELECT языка Transact-SQL позволяет получать существующие данные из базы данных SQL Server. Большинство операторов SELECT описывают четыре главных свойства результирующего набора:

* столбцы, которые должны войти в результирующий набор;

* таблицу, из которой извлекаются данные для формирования результирующего набора;

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

В общем виде главные конструкции оператора SELECT можно записать следующим образом:

SELECT список_выбора

[INTO имя_новой_таблицы]

FROM список_таблиц

[WHERE условия_поиска]

[GROUP BY группировка_по_списку]

[HAVING условия_поиска]

[ORDER BY поле_для_сортировки [ASC|DESC] ]

Конструкция INTO

Конструкция INTO позволяет указать, что для результирующего набора будет создана новая таблица, имя которой задано этой конструкцией. С помощью оператора SELECT...INTO удается объединить данные из нескольких таблиц или представлений в одну таблицу. Его можно использовать для создания новой таблицы с данными, выбранными на связанном сервере.

Конструкция FROM

Конструкцию FROM необходимо помещать в каждом операторе SELECT, который извлекает данные из таблиц или представлений. Эта конструкция позволяет задать список таблиц и представлений, столбцы которых ссылаются список выбора и конструкция WHERE. Этим таблицам и представлениям могут быть присвоены псевдонимы в конструкции AS. Конструкция FROM, кроме того, позволяет соединять таблицы, задавая условия соединения в конструкции JOIN.

Конструкция FROM представляет собой список имен представлений и конструкций JOIN, разделенных запятыми. Конструкцию FROM также используют и для определения соединений между двумя таблицами или представлениями. Об этом будет рассказано подробнее дальше.

Конструкции WHERE, GROUP BY и HAVING

В операторе SELECT конструкции WHERE и HAVING определяют строки исходной таблицы, которые необходимы для построения результирующего набора. Конструкции WHERE и HAVING выполняют роль фильтров. Они задают набор условий поиска: для построения результирующего набора выбираются лишь те строки, которые соответствуют условиям поиска.

Конструкция HAVING, как правило (но не обязательно), используется вместе с конструкцией GROUP BY. Конструкция HAVING задает дополнительные фильтры, которые применяются после завершения фильтрации, определяемой конструкцией WHERE.

Конструкция GROUP BY

Конструкция GROUP BY используется для получения итоговых значений в каждой строке результирующего набора. При применении оператора SELECT без конструкции GROUP BY агрегатные функции возвращают лишь одно итоговое значение.

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

Если в операторе SELECT имеется конструкция GROUP BY, SQL Server налагает ограничения на элементы списка выбора. В списке выбора могут быть лишь те группирующие столбцы и выражения, которые возвращают только одно значение для каждого значения группирующих столбцов, например агрегатные функции (векторные агрегаты), одним из параметров которых является имя столбца.

Как правило, конструкция HAVING используется с конструкцией GROUP BY, хотя ее разрешается применять и отдельно. Любое выражение может стать группирующим, если оно не содержит агрегатных функций.

В конструкции GROUP BY необходимо задавать имя столбца таблицы или представления, а не имя столбца результирующего набора,: присвоенное с помощью конструкции AS. В конструкции GROUP BY допустимо указать несколько столбцов в виде вложенных групп, т.е. сгруппировать таблицу посредством любой комбинации столбцов.

Обработка конструкций WHERE, GROUP BY и HAVING

Понимание верной последовательности, в которой применяются конструкции WHERE, GROUP BY и HAVING, помогает программировать эффективные запросы:

• конструкция WHERE фильтрует строки, которые являются результатом операций, заданных в конструкции FROM;

• выходная информация конструкции WHERE группируется с помощью конструкции GROUP BY;

• строки сгруппированного результата фильтруются средствами конструкции HAVING.

Любые условия поиска, которые разрешается применять как до, так и после операции группировки, стоит указывать в конструкции WHERE. Таким образом уменьшают число строк, которые необходимо сгруппировать. В конструкции HAVING следует указывать только те условия поиска, которые надо применять после выполнения операции группировки.

Конструкция ORDER BY

Конструкция ORDER BY сортирует результат запроса по одному или нескольким полям. Сортировка может быть как по возрастанию (ASC), так и по убыванию (DESC). Если не задан ни один из видов сортировки, по умолчанию предполагается ASC

Использование операций объединения

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

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

Операция внешнего объединения outer join

Существует три типа объединения OUTER JOIN. Объединение RIGHT OUTER JOIN (обычно сокращаемое до RIGHT JOIN) используется для просмотра всех записей из таблицы в правой части предложения независимо от того, совпадают ли записи с данными в таблице слева от оператора. Чтобы присмотреть все записи в крайней слева таблице независимо от того, совпадают ли они с записями в самой правой таблице, используется объединение LEFT OUTER JOIN (или LEFT JOIN). Если вам нужно просмотреть все записи в левой и правой таблице независимо от соответствия с записями другой таблицы, используйте полное внешнее объединение FULL OUTER JOIN (или OUTER JOIN

Определение подзапросов внутри операторов SELECT

Подзапросом называется возвращающий единственное значение оператор SELECT, вложенный в другой оператор SELECT, INSERT, UPDATE, DELETE или в другой подзапрос. Подзапрос разрешается применять в любом месте, где разрешено использование выражения. Подзапрос также называется внутренним запросом или внутренней выборкой, а содержащий подзапрос оператор - внешним запросом или внешней выборкой.

В следующем примере подзапрос вложен в конструкцию WHERE внешнего оператора SELECT:

USE Northwind

SELECT ProductName

FROM Products WHERE UnitPrice =

( SELECT UnitPrice

FROM Products

WHERE ProductName = 'Sir Rodney''s Scones'

) Типы подзапросов

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

• WHERE [NOT] IN ()

• WHERE [ANY | ALL] ([

запрос>) • WHERE [NOT] EXISTS ()

Подзапросы с ключевыми словами IN или NOT IN

Результатом подзапроса с ключевым словом IN (или NOT IN) является список, состоящий из нуля или более значений. Результат, который возвращает подзапрос, используется внешним запросом.

В следующем примере подзапрос вложен в конструкцию WHERE и используется ключевое слово IN:

USE Pubs SELECT Pub_name FROM Publishers WHERE Pub_id IN

( SELECT Pub_id

FROM Titles WHERE Type = 'business'

) Сначала внутренний запрос возвращает номера идентификаторов издательств, выпустивших литературу, которая определена типом business (1389 и 0736). Затем эти значения подставляются во внешний запрос, который находит названия, соответствующие номерам идентификаторов из таблицы Publishers.

Подзапросы с ключевыми словами NOT IN также возвращают список, состоящий из нуля или более значений. Эти запросы абсолютно аналогичны подзапросам с ключевым словом IN и отличаются только тем, что ключевое слово IN заменено на NOT IN.

Подзапросы с операторами сравнения

Ключевые слова ALL и ANY сравнивают скалярное значение с набором значений одного столбца. Ключевое слово ALL применяется ко всем значениям, а ключевое слово ANY - как минимум к одному.

В следующем примере ключевое слово ANY используется с оператором сравнения "больше" (>):

USE Pubs SELECT Title

FROM Titles

WHERE Advance > ANY

( SELECT Advance

FROM Publishers INNER JOIN Titles

ON Titles.Pub_id = Publishers.Pub_id

AND Pub_name = 'Algodata Infosystems'

) Этот оператор находит издания, которые авансированы суммой, превышающей минимальную сумму аванса, уплаченного Algodata Infosystems (в этом случае он составляет 5000). Конструкция WHERE во внешнем операторе SELECT содержит подзапрос, с помощью соединения извлекающий суммы аванса для Algodata Infosystems. После этого минимальная сумма аванса используется для определения изданий, которые необходимо извлечь из таблицы Titles.

Предложения top n и тор npercent

Типичный запрос отдела продаж представляет собой отчет, отображающий работу продавцов в компании, в соответствии с которым распределяются премии. Другой типичный отчет включает в себя данные о продаже товаров. Отделу кадров может потребоваться процентное соотношение служащих, которые использовали все свои отпускные или больничные. Все эти отчеты можно генерировать с помощью предложений как GROUP BY, так и HAVING, но при этом вы увидите все записи, в том числе и ненужные. Если, к примеру, вам нужен только определенный процент общего объема результатов или несколько самых больших значений, следует использовать предложение ТОР N.

Значение N в предложении ТОР N представляет число. Если к примеру, ввести вместо него число 5, то вы сможете извлечь пятерку лучших записей по определенному критерию. Кроме того, вы можете подставить значение 5% для извлечения пяти процентов лучших записей. Само по себе предложение ТОР N не обеспечивает никакой организации: оно просто сканирует таблицы и извлекает все данные, которые может найти, поэтому лучше комбинировать его с предложением ORDER BY. При организации извлеченных данных с помощью предложения ORDER BY вы сможете увидеть реальную картину.

Предложение over

Предположим, что в результатах запроса нужно отсортировать имена клиентов, а затем пронумеровать строки. Вы можете сделать это либо вручную, либо применив функцию RANK вместе с предложением OVER. Функция RANK используется для генерирования ранговых номеров в результирующем наборе данных. Для каждой строки она возвращает количество строк, которые находятся выше в результатах запроса. Предложение OVER указывает SQL Server, как разбивать полученные данные для классификации.

4 Пример выполнения лабораторной работы №5

Создание таблицы в базе данных BookShop

1 Откройте Microsoft SQL Server Management Studio и подключитесь к локальному серверу.

2 Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

create table Test1

( RowID int primary key clustered,

Title varchar(80) not null,

Type varchar(10) not null,

City char(50) null,

Cost money not null

) Этот оператор создает таблицу под названием Testl, состоящую из пяти столбцов. 3.

Нажмите Execute.

На вкладке Messages панели Results выводится сообщение об успешном завершении команды.

Исходная таблица

Добавление к таблице Testl данных с помощью оператора INSERT... VALUES

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

INSERT Test1 VALUES ('Test Title', 'business', ' ' , 250.00)

2. Нажмите Execute.

На вкладке Messages панели Results выводится сообщение о том, что исполнение оператора повлияло на одну строку.

3. Используйте оператор SELECT, который позволит просмотреть все данные из таблицы Testl.

use [BookShop]

SELECT * FROM Test1

4. Нажмите Execute.

Результат исполнения запроса Select

Добавление данных в таблицу Test1 с помощью оператора INSERT... SELECT

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

INSERT Test1 INSERT INTO Test1 (Title, Type, Cost) SELECT Title, Type, Price FROM Pubs.dbo.Titles

Этот оператор берет данные из таблицы Titles в базе данных Pubs и вставляет их в таблицу Testl.

2. Исполните оператор Transact-SQL.

На вкладке Messages панели Results выводится сообщение, где указано число строк, на которые повлияло исполнение оператора.

3. Воспользуйтесь оператором SELECT для просмотра данных таблицы Testl.

Обратите внимание, что значения поля RowID сгенерированы автоматически, а в поле City каждой строки содержится пустое значение.

Модификация данных с помощью оператора UPDATE

1. Просмотрите данные таблицы Test 1.

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

2. Запишите названия нескольких книг, значение поля Туре которых равно study, а также их цену. Эти данные пригодятся во время модификации таблицы.

3. . Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

UPDATE Test1 SET Cost = Cost * 2

WHERE Type = 'study'

Этот оператор в два раза увеличивает значение поля Cost по сравнению с исходным значением для книг типа study.

Результат выполнения запроса

4 . Нажмите Execute.

На вкладке Messages панели Results выводится сообщение, где указано число строк, на которые повлияло исполнение оператора.

5. Воспользуйтесь оператором SELECT для просмотра данных таблицы Testl.

Обратите внимание на удвоенное значение поля Cost для каждой книги типа study.

> Удаление данных из таблицы Test1 с помощью оператора DELETE

1. . Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

DELETE FROM Test1

WHERE Title = 'Test Title'

Этот оператор удаляет все строки из таблицы, в столбце Title которой указано значение Test Title.

2. Нажмите Execute.

На вкладке Messages панели Results выводится сообщение, где указано число строк, на которые повлияло исполнение оператора.

3. Воспользуйтесь оператором SELECT для просмотра данных таблицы Test 1.

Обратите внимание, что строка Test Title удалена из таблицы.

Результат выполнения запроса

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop] DELETE FROM Test1

Этот оператор удаляет все строки таблицы Test 1.

5. Нажмите Execute.

На вкладке Messages панели Results выводится сообщение, где указано число строк, на которые повлияло исполнение оператора.

6. Воспользуйтесь оператором SELECT для просмотра данных таблицы Testl. Обратите внимание на отсутствие данных в таблице Testl.

Результат выполнения запроса

Удаление из базы данных таблицы Test1 с помощью оператора DROP TABLE

Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

DROP TABLE Test1

Этот оператор удаляет таблицу Test1 из базы данных BookShop.

Извлечение всех данных из таблицы Titles

1. Откройте Microsoft SQL Server Management Studio и подключитесь к локальному серверу.

2. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

SELECT* FROM Authors

Этот оператор определяет базу данных, где содержится нужная для просмотра таблица. Оператор SELECT извлекает все данные из таблицы Authors в базе данных BookShop. Звездочка ( * ) в списке выбора указывает, что надо выбрать данные из всех столбцов таблицы.

3. Нажмите Execute.

На вкладке Messages панели Results выводится сообщение, где указано число строк, на которые повлияло исполнение оператора.

3. Результирующий набор выводится в нижней половине окна (вкладка Results).

Получение данных из определенных столбцов таблицы Titles

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

SELECT TitleID,Title,Cost

FROM BookAuthors

Здесь оператор SELECT извлекает данные из столбцов TitleID,Title,Cost базы данных BookShop.

2. Нажмите Execute.

3. Результирующий набор выводится в нижней половине окна (вкладка Results).

Задание условия, которому должен соответствовать результирующий набор

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

SELECT TitleID,Title,Cost

FROM BookAuthors

WHERE Cost>21 Теперь оператор SELECT извлечет лишь те строки, значение поля Cost которых превышает $21.

2. Нажмите Execute.

3. Результирующий набор выводится в нижней половине окна (вкладка Results).

Задание порядка, в котором выводится результирующий набор

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

SELECT TitleID,Title,Cost

FROM BookAuthors

WHERE Cost>21 ORDER BY Cost DESC

результирующий набор, который вернет этот оператор SELECT, упорядочивается сначала по цене (по убыванию).

2. Нажмите Execute.

3. Результирующий набор выводится в нижней половине окна (вкладка Results).

Группировка данных в результирующем наборе

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

SELECT Type,AVG(Cost) AS AvgCost

FROM BookAuthors

WHERE Cost>16 GROUP BY Type

ORDER BY AvgCost DESC

В результирующем наборе, который вернет этот оператор SELECT, группируются строки с одними и теми же значениями поля Туре. Строки, не соответствующие условиям конструкции WHERE, исключаются до начала любых операций по группировке. При группировке выполняется усреднение значений столбца Cost, а полученное среднее значение вставляется в результирующий набор в виде столбца AvgCost. Значения столбца AvgCost упорядочиваются по убыванию.

2. Нажмите Execute.

3. Результирующий набор выводится в нижней половине окна (вкладка Results).

Создание таблицы для размещения результирующего набора

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop]

SELECT Type,AVG(Cost) AS AvgCost

INTO TypeAvgCost

FROM BookAuthors

WHERE Cost>16

GROUP BY Type ORDER BY AvgCost DESC

Оператор SELECT создаст новую таблицу под названием TypeAvgCost. В столбцах Туре и AvgCost размещаются значения результирующего набора.

2. Нажмите Execute.

3. Открываем таблицу TypeAvgCost.

Получение данных посредством внутреннего соединения

1. Нажмите New Query и введите в появившемся окне следующий код:

USE BookShop SELECT b.Title, b.Genre, a.YearBorn

FROM Authors a JOIN BookAuthors b

ON a.AuthorID = b.AuthorID WHERE YearPremia>1991 ORDER BY a.YearBorn

Use определяет базу данных, в которой находится нужная таблица. Далее с помощью оператора SELECT извлекаются данные о названии книги (Title), фамилии автора (Genre) и его года рождения (YearBorn ). Поскольку данные о дате рождения авторов находятся в таблице (Authors) необходимо соединить таблицы BookAuthors и Authors, используя в качестве условия соединения идентификатор автора (AuthorID). Обратите внимание, что таблице Authors присвоен псевдоним a, а таблице BookAuthors - b.

2. Нажмите Execute.

3. Результирующий набор выводится в нижней половине окна (вкладка Results).

Результирующий набор выводится в нижней половине окна (вкладка Data output).

Использование в подзапросе ключевого слова IN

1. Нажмите New Query и введите в появившемся окне следующий код:

use [BookShop] SELECT AuthorID,YearBorn,YearDied,LastName AS Author_Vibor

FROM Authors WHERE LastName IN(

SELECT Genre

FROM BookAuthors

WHERE Cost>16) ORDER BY AuthorID DESC

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

2. Нажмите Execute.

3. Результирующий набор выводится в нижней половине окна (вкладка Results).

Применение подзапроса с операторами сравнения и ключевым словом ALL

1. Нажмите New Query и введите в появившемся окне следующий код:

USE BookShop

SELECT Title, Genre

FROM BookAuthors

WHERE BookDate > ALL (

SELECT YearPremia

FROM Authors JOIN BookAuthors

ON Authors.AuthorID = BookAuthors.AuthorID

AND Authors.AuthorID = 2 )

ORDER BY Title

Внутренний подзапрос определяет год вручения премии автору под номером 2. Далее данные поступают в запрос, где они сравниваются с годом издания этим автором книг и выводятся названия книг, написанных автором после получения премии.

2. Нажмите Execute.

3. Результирующий набор выводится в нижней половине окна (вкладка Results).

5. Задание по выполнению лабораторной работы №5

Используется таблица, полученная в результате предыдущей лабораторной работы.

2. С помощью оператора Transact-SQL INSERT добавьте в таблицы "Сотрудники" и "Отделы" по 8-10 кортежей. (оператором можно добавить по дному кортежу, остальные добавить вручную в Query analyser).

3. Обновите данные в таблице "Сотрудники", увеличив оклад всем мужчинам на 30%.

4. Обновите данные в таблице "Сотрудники", увеличив премию всем сотрудникам, родившимся ранее 1 января 1950 года.

5. Удалите записи в таблице "Сотрудники", в которых фамилия начинается на А или Б.

6. Добавьте записи в таблицу "Сотрудники" с учетом того, что в некоторых полях есть значения по умолчанию (т.е. в эти поля вводить ничего не надо).

7. С помощью оператора SELECT выведите все данные из таблиц "Сотрудники" и "Отделы".

8. Выведите столбцы "табельный номер", "дата рождения", "фамилия", "пол" из таблицы "Сотрудники" и отсортируйте их сначала по фамилии, потом по дате рождения в порядке убывания.

9. Создайте запрос с вычисляемым полем "К выдаче" (сумма полей "Оклад" и "Премия");

10. Создайте запрос на выборку - сотрудники с окладом 650 руб.;

11. Создайте запрос на выборку - сотрудники мужчины с окладом больше 500 р.;

12. Создайте запрос на выборку - сотрудники с окладом больше 500 и меньше 800 р.;

13. Найти количество сотрудников в каждом отделе и вывести информацию об этом отделе.

14. Вычислить общую сумму по атрибуту "ПРЕМИЯ" для определенного отдела.

15. Создать новую таблицу на основании запроса из 2-го задания.

16. С помощью внутреннего соединения выведите фамилии сотрудников, их даты рождения и названия отделов, в которых они работают.

6. Содержание отчета

Отчет по лабораторной работе должен содержать:

- первая часть - файлы с разработанными запросами;

- вторая часть - файл в формате WORD ЛАБОРАТОРНАЯ_5.doc с отчетом о выполненной работе. Отчет содержит краткие пояснения к лабораторной работе и выводы.

ЛАБОРАТОРНАЯ РАБОТА №6

Хранимые процедуры в базах данных SQL Server

1. Цель работы

Научиться использовать в своих расчетах процедурный блок состоящий из одной или нескольких инструкций Transact-SQL

2. Общие рекомендации по созданию хранимых процедур

. В процедуре вы можете:

- использовать операторы, которые выполняют любые операции в БД(выборка, вставка, изменение или удаление данных), включая возможность вызова других встроенных процедур;

- возвращать статус выполнения в вызывающую процедуру или модуль для отображения удачного или ошибочного выполнения;

- возвращать несколько значений в вызывающую процедуру или модуль в форуме выходных параметров.

Хранимые процедуры - это именованный набор операторов Transact-SQL, хранящийся на сервере. Они используются для выполнения повторяющихся задач и при этом обладают большими возможностями, чем представления.

Сервер SQL поддерживает 5 типов встроенных процедур:

- системные хранимые процедуры - хранятся в БД master. Система хранит процедуры (определяющиеся по префиксу sp_), представляющие эффективные методы получения информации из системных таблиц. Она позволяет системному администратору выполнять администраторские задачи над БД, которые обновляют необходимые таблицы напрямую. Системные встроенные процедуры - могут быть выполнены из любой БД;

- локальные хранимые процедуры - создаются в определенных пользовательских таблицах;

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

- удаленные хранимые процедуры - устаревшая технология MS SQL Server. На данный момент эту задачу решают распределенные запросы;

- расширенные встроенные процедуры(содержат префикс xp_) - разрабатываются в виде DLL (динамически подключаемая библиотека) и выполняются вне окружения SQL Server.

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

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

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

Для создания процедур используют ключевые слова CREATE PROCEDURE.

Перед исполнением процедуры следует задать значения всех необходимых параметров. Хранимые процедуры можно исполнять вручную или автоматически (при запуске SQL Server). Для исполнения процедуры служит ключевое слово EXECUTE.

Часто приходится модифицировать созданные хранимые процедуры, например, чтобы добавить параметр или изменить ту или иную команду. Модификация процедуры вместо удаления и повторного создания "с нуля" позволяет сэкономить время, поскольку при модификации сохраняются многие свойства хранимых процедур (например, права доступа). Для модификации процедур используются ключевые слова ALTER PROCEDURE.

Для удаления процедур используют ключевое слово DROP. Процедуру можно удалить в Enterprise Manager иди Query Analyzer , выбрав ее и нажав клавишу 'DELETE'. Не следует удалять процедуру до тех пор, пока все зависящие от процедуры объекты не будут удалены или модифицированы (чтобы удалить зависимость).

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

SELECT Фамилия, Дата_Рождения

FROM Сотрудники

WHERE Код_отдела = 5

Это простейшая процедура, которая не будет использовать переменных, поэтому для ее создания необходимо написать:

CREATE PROCEDURE GetSotrudniki

AS SELECT Фамилия, Дата_Рождения

FROM Сотрудники

WHERE Код_отдела = 5

Чтобы выполнить созданную выше процедуру, необходимо:

EXECUTE GetSotrudniki

Или EXEC GetSotrudniki

Результат выполнения процедуры:

Удаление процедуры:

DROP PROCEDURE GetSotrudniki

Использование параметров.

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

CREATE PROCEDURE GetOklad

@LastName varchar (50)

AS SELECT Фамилия, Оклад

FROM Сотрудники

WHERE Фамилия=@LastName

Пример поиска по фамилии:

EXEC GetOklad 'Гришин'

Результат:

В Query Analyzer можно использовать без EXEC/EXECUTE , т.е. можно GetOklad 'Гришин'.

Параметры нужно передавать в том же порядке, как они были указаны в объявлении, но можно сделать и отступление, если указывать их в виде имя=значение. Например в процедуре GetOklad параметр называется @LastName. Это значит, что можно вызвать процедуру следующим образом:

EXECUTE GetOklad @LastName='Гришин'

Преимущества хранимых процедур.

В коде процедуры вы можете использовать практически любые объекты БД MS SQL, а именно: представления, таблицы, функции, другие процедуры, временные таблицы.

Хранимые процедуры предоставляют множество преимуществ, среди которых можно выделить следующее:

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

- разделение логики приложений с другими, таким образом гарантируется совместимый доступ и модификация данных;

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

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

- повышение производительности за счет выполнения нескольких задач в виде набора операторов Transact-SQL и хранения плана выполнения.

Практика создания и использования процедур.

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

CREATE PROCEDURE AddSotr

@Tab int, @Date smalldatetime,

@LastName varchar(20),

@Oklad int, @Kod_otdela int,

@Otrab_dn smallint,

@Premiya int, @Pol varchar(1)

AS IF EXISTS

(SELECT * FROM Сотрудники

WHERE Фамилия=@LastName)

PRINT 'Такая фамилия уже есть'

ELSE

BEGIN INSERT INTO Сотрудники

VALUES(@Tab, @Date, @LastName, @Oklad, @Kod_otdela, @Otrab_dn, @Premiya, @Pol)

PRINT 'Сотрудник добавлен'

END

Если мы введем сотрудника, с фамилией, которая уже имеется у нас в таблице, т.е. запрос:

EXEC AddSotr '45', '11.10.1975','Гришин','200','2','120','250','м'

Результат:

Если с уникальной фамилией:

EXEC AddSotr @Tab=45, @Date='11.10.1975',@LastName='Васильев',@Oklad=600,

@Kod_otdela=2,@Otrab_dn=120,@Premiya=250,@Pol='м'

Благодаря явному указанию имен, порядок может быть любым.

То результат:

Изменение процедур

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

Если вы хотите изменить процедуру, которая была создана с какими-нибудь опциями, например, WITH ENCRYPTION(с шифрованием), вы должны включить их в число опций команды ALTER PROCEDURE для сохранения функциональности, которую предоставляет опция.

Во время выполнения оператора ALTER PROCEDURE изменяется только одна процедура. Если она ссылается на другие, то они не изменяются.

Следующий тривиальный пример изменяет процедуру AddSotr.

ALTER PROCEDURE AddSotr

@Tab int, @Date smalldatetime,

@LastName varchar(20),

@Oklad int = 400,

@Kod_otdela int,

@Otrab_dn smallint,

@Premiya int, @Pol varchar(1)

AS IF EXISTS (SELECT * FROM Сотрудники

WHERE Фамилия=@LastName)

PRINT 'Такая фамилия уже есть'

ELSE BEGIN INSERT INTO Сотрудники

VALUES(@Tab, @Date, @LastName, @Oklad, @Kod_otdela, @Otrab_dn, @Premiya, @Pol)

PRINT 'Сотрудник добавлен'

END Теперь по умолчанию будет оклад = 400.

EXEC AddSotr @Tab=47, @Date='11.10.1975',@LastName='Лавров',

@Kod_otdela=2,@Otrab_dn=120,@Premiya=250,@Pol='м'

Результат:

Опции. Теперь посмотрим, какие дополнительные параметры можно использовать во время создания процедуры. Таких параметров два:

- RECOMPILE - указывает на то, что MS SQL SERVER не должен сохранять план выполнения, компиляция будет происходить при каждом выполнении;

- ENCRYPTION - запись в таблицу syscomments с текстом процедуры должна шифроваться.

Пример:

CREATE PROCEDURE FamSelect

WITH ENCRYPTION

AS SELECT .....

Опция WITH ENCRYPTION указывается после всех параметров процедуры, но до ключевого слова AS.

3. Задания по выполнению лабораторной работы №6:

1) Создайте процедуру, показывающую табельный номер и фамилию сотрудника с окладом больше 450 и женским полом.

2) Создайте процедуру, в котором поле "Разница" будет определяться как разность между полями "Премия" и "Оклад"

3) Модифицируйте процедуру , созданную в задание 1 так, чтобы она показывала "код_отдела" по введенной фамилии сотрудника.

4) Создайте процедуру , которая будет уменьшать оклад всем мужчинам на 100, с фамилиями, начинающуюся на А.

5) Создайте с опцией шифрования процедуру, создающее представление , где выбраны все женщины с премией между 130 и 210.

4. Содержание отчета

Отчет по лабораторной работе должен содержать:

- первая часть - файлы с разработанными процедурами;

- вторая часть - файл в формате WORD ЛАБОРАТОРНАЯ_6.doc с отчетом о выполненной работе. Отчет содержит краткие пояснения к лабораторной работе и выводы.

ЛАБОРАТОРНАЯ РАБОТА №7

Триггеры в базах данных SQL Server

1 Цель работы

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

2. Общие рекомендации по созданию триггеров

Триггер представляет собой набор инструкций SQL Server, которая выглядит и действует подобно хранимой процедуре. Единственное реальное отличие состоит в том, что триггер нельзя вызвать с помощью команды EXEC. Триггеры активизируются при выполнении пользователем определенной инструкции Transact-SQL. Триггеры DML запускаются с помощью инструкций INSERT, UPDATE или DELETE, а триггеры DDL- с помощью инструкций CREATE, ALTER и/или DROP. Предположим, в таблице клиентов вы определили триггер INSERT, указывающий, что пользователи не могут добавлять записи о новых клиентах, находящихся за пределами США. Как только пользователь попытается добавить новую запись, будет запущен триггер INSERT, который определит, соответствует ли она данному критерию. В случае соответствия запись вставляется в таблицу; в противном случае этого не происходит.

SQL Server может блокировать модификацию данных, если запись не соответствует критериям, поскольку триггеры рассматриваются как транзакции. Транзакцией называется блок инструкций Transact-SQL, который интерпретируется SQL Server как единое целое. Код группируется в транзакцию путем помещения инструкции BEGIN TRAN в начало кода и инструкции COMMIT в его конец. Эти инструкции могут размещаться как пользователем (явная транзакция), так и SQL Server (неявная транзакция). Поскольку триггер рассматривается как транзакция, вам нужно добавить лишь команду ROLLBACK в соответствующее место в коде, если вы не хотите, чтобы триггеру передавались записи. Команда ROLLBACK указывает серверу остановить обработку модификации и запретить транзакцию.

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

Существуют и другие способы реализации бизнес-логики. Используя связь внешнего ключа между таблицами клиентов и заказов, вы можете запретить удаление подвешенного заказа пользователями. Вы также можете не позволить пользователю вставлять заказ для клиента, отсутствующего в таблице клиентов.

Процедурная целостность данных

Прежде чем реализовывать триггер, следует выяснить, нельзя ли получить аналогичные результаты с использованием ограничений ил правил. Для уникальной идентификации строк табличных данных используют целостность сущностей (ограничения primary key и unique key). Доменная целостность служит для определения значений по умолчанию (определения default) и ограничения диапазона разрешенных для ввода в данное поле (ограничения check и связанные ограничения). Ссылочная целостность используется для реализации логических связей между таблицами (ограничения foreign key и check). Если значение обязательного поля не задано в операторе INSERT, то оно определяется с помощью определения default. Лучше применять ограничения, чем триггеры и правила. Триггеры применяются в следующих случаях:

* если использование методов декларативной целостности данных не отвечает функциональным потребностям приложения. Например, для изменения числового значения в таблице при удалении записи из этой же таблицы следует создать триггер;

* если необходимо каскадное изменение через связанные таблицы в базе данных. Например, чтобы изменять объем складских запасов при размещении заказа, следует создать триггер, привязанный таблице Orders. Другой триггер, привязанный к таблице Inventory, при изменении количества товара на складе добавляет к таблице Purchasing запрос на закупку товара. Чтобы обновить или удалить данные в столбцах с ограничением foreign key, вместо пользовательского триггера следует применять ограничения каскадной ссылочной целостности;

o если база данных денормализована и требуется способ автоматизированного обновления избыточных данных в нескольких таблицах;

o необходимо сверить значение в одной таблице с неидентичным значением в другой таблице;

o требуется вывод пользовательских сообщений и сложная обработка ошибок.

Исполнение триггеров

Триггер, запущенный в результате добавления или обновления табличных данных, сохраняет новые или модифицированные данные в таблице под названием Inserted, а триггер, сработавший при удалении данных из таблицы, сохраняет удаленные данные в таблице Deleted. С помощью команд Transact-SQL выполняются запросы к этим таблицам, которые существуют в памяти. Эта особенность важна для функционирования большинства триггеров: прежде чем внесенные изменения будут зафиксированы, задача триггера (например, модификация значения в связанной таблице) сравнивает данные таблиц Inserted или Deleted с данными модифицированной таблицы. Используя данные из таблиц Inserted или Deleted, триггер может откатить транзакцию, если это необходимо.

В SQL Server 2000 существуют два класса триггеров: INSTEAD OF и AFTER. Первые выполняются в обход действий, вызывавших их срабатывание, заменяя эти действия. Например, обновление таблицы, в которой есть триггер INSTEAD OF, вызовет срабатывание этого триггера. В результате вместо оператора обновления выполняется код триггера. Это позволяет размещать в триггере сложные операторы обработки, дополняющие действия оператора, модифицирующего таблицу.

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

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

Характеристика INSTEAD OF - триггер AFTER - триггер Объект, к которому можно привязать триггер Таблица или представление. Триггеры, привязанные к представлению, расширяют список типов обновления, которые может поддерживать представление Таблица.

AFTER-триггеры срабатывают при модификации данных в таблице в ответ на модификацию представления допустимое число диггеров В таблице или представлении допускается не больше одного триггера расчете на одно действие. Можно определять представления для других представлений, каждое со своим собственным INSTEAD OF-триггером К таблице можно привязать несколько AFTER-триггеров Порядок исполнения Поскольку в таблице или представлении допускается не больше одного такого триггера в расчете на одно событие, порядок не имеет смысла Можно определять триггеры, срабатывающие первым и последним. Для этого служит системная хранимая процедура sp_settriggerorder. Порядок срабатывания других триггеров, привязанных к таблице, случаен

Создание триггеров с помощью языка Transact-SQL

Триггеры создаются при помощи оператора Transact-SQL CREATE TRIGGER:

При создании триггера в SQL - Server необходимо на папке Triggers нажать правой кнопкой и в появившемся контекстном меню выбратьNew Trigger. Появится шаблон

-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

-- -- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

-- -- See additional Create Trigger templates for more

-- examples of different Trigger statements.

-- -- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO SET QUOTED_IDENTIFIER ON

GO -- =============================================

-- Author: -- Create date:

-- Description:

-- =============================================

CREATE TRIGGER .

ON . AFTER

AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

END GO Нажать Ctrl-Shift-M в появившемся окне ввести данные триггера.

Конструкция AS

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

Управление триггерами

Триггеры - это мощные объекты базы данных, которые автоматически исполняются при модификации таблицы или представления. Для управления триггерами предназначен ряд команд и инструментов баз данных. Триггеры разрешается:

• модифицировать с помощью оператора ALTER TRIGGER-

• переименовать средствами системной хранимой процедуры sp_rename;

• просмотреть путем запроса системных таблиц или с использованием системных хранимых процедур sp_helptrigger или sp_helptext;

• удалить с помощью оператора DROP TRIGGER;

• включить или выключить при помощи конструкций DISABLETRIGGER и ENABLETRIGGER оператора ALTER TABLE.

Триггер Insert

Триггеры INSERT можно использовать для изменения или даже удаления вставленной записи. Хорошим примером использования этого триггера является предотвращение добавления определенных типов записей, таких как данные клиентов с лимитом кредитования больше 10 000 долларов. К качестве примера можно привести добавление или изменение запрещенных данных во вставляемой записи (к примеру, изменение даты создания записи или имени пользователя, вставляющего эту запись). Вы даже можете использовать триггер INSERT для каскадных изменений в других таблицах базы данных. Предположим, что у вас есть две базы данных: деловых контактов и кадров. Многие компании хранят одну и ту же информацию в обеих базах данных, поскольку им нужно, чтобы информация о служащих была перечислена вместе с деловыми контактами. Триггер INSERT (а также триггеры UPDATE и DELETE) может запустить каскадные обновления из одной базы данных в другую для синхронизации текущих данных в обеих базах.

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

Таблицу inserted удобно использовать, когда требуется выполнить каскадные изменения в других таблицах базы данных. Предположим, что существует база данных, содержащая информацию о клиентах, заказах и товарах. Каждый раз при выполнении заказа клиента вам нужно вычитать эти товары в учете складских запасов (т.е. в таблице товаров), чтобы поддерживать правильный баланс. Существует два способа решения этой задачи. Первый заключается в хранении данных о проданных клиенту товарах во временной переменной (или переменной в памяти) и обновлении таблицы товаров с помощью второй инструкции UPDATE. Однако такой метод требует написания дополнительного кода, который может замедлить работу системы, и это нельзя считать идеальным решением. Второй способ состоит в использовании логической таблицы inserted. Требуемое значение хранится в двух местах - таблицах триггеров и inserted, так что вы можете извлечь значение из таблицы inserted и использовать его. Это означает, что вы можете вписать в триггер на вставку код автоматического вычитания данных из таблицы товаров на основе значения в таблице inserted. Он может выглядеть примерно так,

UPDATE р

SET p.instock = (p.instock - i.qty)

FROM Products p JOIN inserted i

ON p.prodid = i.prodid

Триггер Delete

Триггеры DELETE используются для предотвращения удаления данных пользователями из базы данных.

Обычно, когда пользователь выполняет инструкцию DELETE, программа SQL Server удаляет запись из таблицы, и о ней больше никогда никто не слышит. Это поведение изменяется при добавлении в таблицу триггера DELETE. При наличии триггера DELETE SQL Server переносит удаляемую запись в логическую таблицу в памяти с именем deleted. Таким образом, записи не исчезают полностью, и вы можете ссылаться на них в коде. Это удобно применять в бизнес-логике.

Чтобы получить представление о работе этого триггера, мы создадим триггер DELETE, предназначенный для предотвращения удаления пользователями данных о клиентах, которые живут в штате Аризона.

IF (SELECT state FROM deleted) = 'AZ'

BEGIN PRINT 'Cannot remove customers from AZ'

PRINT 'Transaction has cancelled'

ROLLBACK END

Таким образом мы создали триггер DELETE, который использует логическую таблицу deleted, чтобы проверить, не пытаетесь ли вы удалить данные о клиенте из Аризоны. Если вы пытаетесь удалить такие данные, то получите отказ в виде сообщения об ошибке (генерируемого инструкцией PRINT, введенной в код триггера).

Теперь, после изучения внутренней работы триггеров INSERT и DELETE, вам будет легче понять принцип действия триггеров на обновление (UPDATE).

Триггер Update

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

Метод, используемый триггером UPDATE, представляет комбинацию методов, применяемых триггерами INSERT и DELETE. Помните, что триггер INSERT использует таблицу inserted, а триггер DELETE - таблицу deleted.

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

Итак, мы воспользуемся преимуществами триггера UPDATE. Сейчас вы не можете предотвратить продажу пользователями товаров сверх запасов - они смогут продавать товар, даже если столбец instock таблицы Products будет принимать отрицательные значения. Это может навредить отношениям с ценными клиентами - лучше иметь возможность сообщить клиенту о дефиците товара, вместо того, чтобы продавать ему воздух. Мы создадим триггер, который будет проверять столбец InStock таблицы Products на наличие остатков товара на складе на момент продажи.

IF (SELECT InStock from inserted) < 0

BEGIN PRINT 'Cannot oversell Products'

PRINT 'Transaction has been cancelled'

ROLLBACK END

Формирование сообщений об ошибках с помощью функции RAISERROR()

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

Сейчас же мы используем функцию RAISERROR () просто для отправки сообщений конечным пользователям. Синтаксис функции RAISERROR () следующий

RAISERROR('Сообщение', степень_серьезности, состояние)

Параметр ' Сообщение' представляет собой текст, который будут читать пользователи при нарушении правил триггера. Параметр степень_серьезности указывает системе минимальный уровень серьезности ошибки. Скорее всего, вы будете использовать степень серьезности 10, являющуюся информационной Параметр состояние используется в том случае, если отдельная ошибка была вызвана из нескольких точек триггера Например, если эта ошибка возникла в начале, то вы можете задать состояние 1. В случае возникновения ошибки в середине задается состояние 2 и т.д.

IF (SELECT state FROM deleted) = 'AZ '

BEGIN RAISERROR('Cannot modify customers from AZ' , 10, 1)

ROLLBACK

END Рекурсивные триггеры

Как вы уже знаете, триггеры могут существовать в каждой таблице базы данных. Эта триггеры могут также содержать код, обновляющий другие таблицы, как в нашем случае с триггером INSERT. Однако существует дилемма: триггер, обновляющий другие таблицы, может запускать триггеры в других таблицах. Такой триггер называется рекурсивным, триггером.

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

o Совместная работа всех триггеров рассматривается как одна большая транзакция. Команда ROLLBACK, используемая в любом триггере, отменит ввод всех данных. Все данные будут отклонены, а в таблицах ничего не будет изменено.

o SQL Server поддерживает рекурсию триггеров до 16 уровней. Это означает, что если триггер 16 в цепочке запускает 17-й триггер, то эффект будет подобным срабатыванию команды ROLLBACK: все новые данные будут отклонены.

3. Пример выполнения лабораторной работы №7

Создание триггера на добавление_данных

1. Откройте Microsoft SQL Server Management Studio и подключитесь к локальному серверу.

2. Нажмите New Query и введите в появившемся окне следующий код:

CREATE TRIGGER dbo.Добавление_данных

ON dbo.Authors

AFTER INSERT AS

BEGIN print 'Данные добавлены в таблицу'

END

Оператор CREATE TRIGGER создает триггер Добавление_данных и привязывает его к таблице Authors в базе данных BookShop.

Проверка работы триггера:

Добавим в таблицу Authors новые данные. Для этого введем следующий код:

use BookShop

insert Authors values(4,'Александр','Городецкий',1910,1955,1950)

При запуске данного запроса во вкладке Results появится сообщение:

Создание триггера на обновление, добавление и удаление

содержимого связанных таблиц

Триггеры позволяют реализовать любые действия над таблицей или представлением вместо (INSTEAD OF-триггеры) или после (AFTER-триггеры) исполнения операторов INSERT, UPDATE или DELETE. Триггеры позволяют решать различные задачи.

1. Откройте Microsoft SQL Server Management Studio и подключитесь к локальному серверу.

2. Нажмите New Query и введите в появившемся окне следующий код:

CREATE TRIGGER dbo.Отдел_Кадров2

ON dbo.Сотрудник

AFTER INSERT,DELETE,UPDATE AS /*Триггер AFTER после удаления или добавления данных*/

SET NOCOUNT ON /*чтобы строки из оператора SELECT не выводились на экран*/

IF EXISTS (SELECT [Табельный_Номер] FROM Inserted) /*если в таблицу добавили новое значение*/

BEGIN

SELECT Номер_Отдела,count(Табельный_номер) AS Кол_во

INTO Сотрудник1 /*создание времменой таблицы*/

FROM Сотрудник

GROUP BY Номер_Отдела

ORDER BY Кол_во DESC

UPDATE ot SET ot.[Кол-во_Сотрудников] = s1.Кол_во

FROM Отделы ot JOIN Сотрудник1 s1

ON ot.[Номер_отдела] = s1.[Номер_отдела]

WHERE ot.[Номер_отдела]=s1.[Номер_отдела]

drop table Сотрудник1

END IF EXISTS (SELECT [Табельный_Номер] FROM DELETED) /*если из таблицы удалили значение*/

BEGIN

SELECT Номер_Отдела,count(Табельный_номер) AS Кол_во

INTO Сотрудник1

FROM Сотрудник

GROUP BY Номер_Отдела

ORDER BY Кол_во DESC

UPDATE ot SET ot.[Кол-во_Сотрудников] = s1.Кол_во

FROM Отделы ot JOIN Сотрудник1 s1

ON ot.[Номер_отдела] = s1.[Номер_отдела]

WHERE ot.[Номер_отдела]=s1.[Номер_отдела]

drop table Сотрудник1

END IF UPDATE (Табельный_Номер) /*если в таблицу добавили новое значение*/

BEGIN

SELECT Номер_Отдела,count(Табельный_номер) AS Кол_во

INTO Сотрудник1

FROM Сотрудник

GROUP BY Номер_Отдела

ORDER BY Кол_во DESC

UPDATE ot SET ot.[Кол-во_Сотрудников] = s1.Кол_во

FROM Отделы ot JOIN Сотрудник1 s1

ON ot.[Номер_отдела] = s1.[Номер_отдела]

WHERE ot.[Номер_отдела]=s1.[Номер_отдела]

drop table Сотрудник1

END SET NOCOUNT OFF /*разрешаем вывод на экран строк оператора SELECT*/

В триггере применен оператор SET NOCOUNT ON, поэтому, когда триггер срабатывает и обновляет данные в таблице Отделы, результаты оператора SELECT не выводятся. Первая часть триггера срабатывает при добавлении в таблицу "Сотрудник" новой записи. При этом проверяется существование таблицы "Inserted". При ее наличии выполняется подзапрос, результатом работы которого будет создание временной таблицы "Сотрудник1", содержащей данные о количестве сотрудников в каждом из отделов. Далее срабатывает оператор Update, который обновляет данные в таблице "Отделы", получая данные из " Сотрудник1", для чего используется соединение данных таблиц оператором "JOIN".

Аналогично происходит обновление данных в таблице "Отделы" при удалении сотрудника и переходе сотрудников из одного отдела в другой.

4. Задание по выполнению лаборатоной работы №7

1. Создайте триггер, который будет выводить сообщение о добавлении, обновлении или удаление данных в таблице "Отделы".

2. Создайте триггер, который автоматически будет увеличивать или уменьшать [количество сотрудников] в таблице Отделы при добавлении или удалении записей в таблице "Сотрудники".

5. Содержание отчета

Отчет по лабораторной работе должен содержать:

- первая часть - файлы с разработанными триггерами;

- вторая часть - файл в формате WORD ЛАБОРАТОРНАЯ_7.doc с отчетом о выполненной работе. Отчет содержит краткие пояснения к лабораторной работе и выводы.

Литература

1. Проектирование информационных систем: учебное пособие Коваленко В. В. -М.: ФОРУМ, 2012

2. Базы данных: учебное пособие Кузин А.В., Левонисова С.В. - М.: Издательский центр Академия, 2010

3. Управление данными: учебник Кузовкин А.В., Цыганов А.А., Щукин Б.А. - М.: Академия, 2010

4. Технология разработки программных продуктов: практикум: учебное пособие Рудаков А.В., Федорова Г.Н. - М.: Академия, 2010

5. Базы данных: учебное пособие Фуфаев Э.В., Фуфаев Д.Э. -М.: Академия, 2010

6. Технология разработки программных продуктов: практикум: учебное пособие Рудаков А.В., Федорова Г.Н. - М. : Академия, 2010

7. Базы данных: учебное пособие Фуфаев Э.В., Фуфаев Д.Э. -М. : Академия, 2007

8. Администрирование баз данных. СУБД MS SQL Server: учебное пособие Култыгин О. П. - М. : Московская финансово-промышленная академия, 2008

2

Показать полностью…
2 Мб, 5 сентября 2014 в 12:48 - Россия, Москва, НИТУ МИСиС, 2014 г., doc
Рекомендуемые документы в приложении