close

Вход

Забыли?

вход по аккаунту

?

Presnyakova

код для вставкиСкачать
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
Федеральное государственное автономное
образовательное учреждение высшего образования
САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ
Г. В. Преснякова, Н. В. Барклаевская,
О. М. Шарапова
РАБОТА С БАЗАМИ ДАННЫХ В СРЕДЕ
MICROSOFT SQL SERVER 2012
Учебное пособие
Санкт-Петербург
2016
УДК004
ББК 32.973-018.2
П73
Рецензенты:
кандидат технических наук Г. С. Евсеев;
руководитель направления «Бизнес-информатика»
СЗИУ РАНХиГС профессор, д.в.н. В. Н. Наумов
Утверждено
редакционно-издательским советом университета
в качестве учебного пособия
Преснякова, Г. В.
П73 Работа с базами данных в среде Microsoft SQL Server 2012: учеб.
пособие / Г. В. Преснякова, Н. В. Барклаевская, О. М. Шарапова. –
СПб.: ГУАП, 2016. – 163 с.
ISBN 978-5-8088-1156-0
Пособие содержит достаточно подробную информацию о создании
и работе с базами данных в среде Microsoft SQL Server 2012. Указаны
возможные способы создания базы данных. Рассмотрены актуальные для студентов простые способы переноса базы данных с одного
компьютера на другой. Даны основы языка запросов Transact-SQL.
Приведено много примеров всех видов запросов, реализованных как
средствами языка Transact-SQL, так и с помощью редактора запросов. Уделено большое внимание способам организации индексов.
Материал учебного пособия проиллюстрирован большим количеством примеров.
Предназначено для использования его в дисциплинах «Базы данных», «Базы данных, базы знаний», «Проектирование интеллектуальных компьютерных систем» и других дисциплинах, связанных
с использованием среды MS SQL Server 2012 и более ранних версий.
Учебное пособие может быть использовано при выполнении квалификационных бакалаврских работ, дипломных работ и проектов и
магистерских диссертаций для студентов, обучающихся по направлению 09.03.01 «Информатика и вычислительная техника», а также
для студентов других специальностей, изучающих современные системы управления базами данных и язык запросов Transact-SQL.
Читателю желательно иметь минимальные представления о создании и работе с базами данных под управлением СУБД MS Access
любых версий.
УДК 004
ББК 32.973-018.2
ISBN 978-5-8088-1156-0
©
©
Преснякова Г. В., Барклаевская Н. В.,
Шарапова О. М., 2016
Санкт-Петербургский государственный
университет аэрокосмического
приборостроения, 2016
ВВЕДЕНИЕ
Microsoft SQL Server версии 2012, как и многие предыдущие
версии, является весьма сложной системой с большими возможностями [2], [6], [14]. Получить желаемый результат можно множеством способов, разными путями, используя различные средства,
предоставляемые системой.
Разработка баз данных и создание программ, использующих
базы данных, включает проектирование баз данных, создание самой базы данных и всех необходимых для ее эффективного использования объектов (таблиц, индексов, хранимых процедур, триггеров, функций), а также поддержание базы данных в работоспособном состоянии и создание пользовательского приложения. MS
SQL Server – это система управления реляционной базой данных.
Вопросы проектирования реляционных баз данных в данном учебном пособии не рассматриваются. Подробно эти вопросы изложены
в работах [11] и [12].
Здесь же рассмотрены основные вопросы пользовательского
уровня, связанные с реализацией уже спроектированной реляционной базой данных в среде MS SQL Server 2012.
Предлагаемое читателю учебное пособие позволяет познакомиться с основными возможностями SQL Server 2012 и его инструментальными средствами, позволяющими пользователю успешно
решать стоящие перед ним задачи.
Учебное пособие содержит достаточно подробную информацию
о создании и работе с базами данных в среде Microsoft SQL Server
2012. Указаны возможные способы создания базы данных: с помощью программной утилиты SQL Server Management Studio, операторами языка запросов Transact-SQL, с использованием ADPпроекта, импортированием таблиц из базы данных MS Access в базу
данных на сервере.
Рассмотрены актуальные для студентов способы переноса базы
данных с одного компьютера на другой, в частности с использованием резервного копирования.
Даны основы языка запросов Transact-SQL, непосредственно используемого в MS SQL Server 2012, а также в более ранних версиях.
Приведено много примеров всех видов запросов, реализованных как
средствами языка Transact-SQL, так и с помощью редактора запросов. Уделено большое внимание способам организации индексов.
В качестве упражнений предложен комплекс заданий для самостоятельного выполнения запросов на языке Transact-SQL.
3
Подробно и с большим количеством примеров рассмотрены вопросы по созданию и работе с объектами базы данных: таблицами,
представлениями, хранимыми процедурами, триггерами, встроенными и пользовательскими функциями, а также рассмотрены вопросы использования проверочных ограничений.
В минимальном объеме даны основные сведения по обеспечению
безопасности данных.
В учебном пособии затронуты также некоторые важные вопросы администрирования баз данных, с которыми приходится сталкиваться студентам.
Материал учебного пособия проиллюстрирован большим количеством примеров, которые были выполнены в операционной системе Windows 7 и русской версии MS SQL Server 2012.
Учебное пособие может быть полезно для получения как теоретических знаний в области использования среды Microsoft SQL
Server, так и для получения практических навыков работы с различным инструментарием, предоставляемым пользователю при
создании баз данных и манипулировании данными.
Читателю желательно иметь минимальные представления о создании и работе с базами данных хотя бы под управлением СУБД MS
Access любых версий.
4
1. НЕКОТОРЫЕ ВАЖНЫЕ СВЕДЕНИЯ О MS SQL SERVER 2012
1.1. Архитектура MS SQL Server
Архитектура SQL Server включает в себя клиентскую часть и
серверы баз данных, взаимодействующие друг с другом с помощью
сетевых протоколов, даже если сервер и клиент находятся на одном
компьютере [2]. Представление данных и интерфейс пользователя
контролируется клиентским программным обеспечением. Сервер
всегда контролирует доступ к данным. В зависимости от особенностей приложения логическая структура программы и организация
проверки целостности вводимых данных могут быть реализованы
как на сервере, так и на клиентском компьютере. Такой подход
имеет следующие преимущества:
• упрощается поддержка приложений, так как структуру базы
данных можно менять независимо от логики представления данных;
• каждое рабочее место можно оптимизировать отдельно с точки
зрения выполняемых задач;
• организация распределенных вычислений позволяет добиться
наилучшей производительности и обеспечивает возможность одновременной работы большого числа пользователей.
К преимуществам Мicrosoft SQL Server также относится [2]:
• Использование «родных» потоков операционной системы для
выполнения низкоуровневых вычислительных задач.
• Использование симметричной мультипроцессорной обработки. Симметричная архитектура сервера Мicrosoft основывается
на службах операционной системы, которые обеспечивают защиту
памяти, а также поддержку симметричной мультипроцессорной
обработки и надежность работы сервера. Пользовательским соединениям динамически предоставляются потоки, которые затем распределяются планировщиком операционной системы для выполнения их на определенных процессорах.
• Сервер динамически оптимизирует выполнение запросов с помощью встроенного оптимизатора, который производит оценку
вариантов выполнения запросов с точки зрения стоимости и затрачиваемых системных ресурсов. Оптимизатор автоматически выбирает лучший метод доступа к данным, который затем используется
для выполнения запроса.
• Даже если в системе имеется только один процессор, SQL
Server может организовывать одновременное выполнение нескольких задач. Сервер динамически распределяет соединения пользо5
вателей по потокам, даже если в настоящий момент количество
одновременных подключений пользователей превышает число потоков, которые можно запустить одновременно.
В Microsoft SQL Server имеются приложения с графическим
интерфейсом для разработки и администрирования баз данных.
Наиболее полезными компонентами являются SQL Server Query
Analyzer и программа-менеджер SQL Server Enterprise Manager
для SQL Server 2000 [1] и SQL Server Management Studio для SQL
Server 2005 и более поздних версий [2], [6]. Первая утилита позволяет работать с серверами баз данных, создавать и выполнять
запросы в стандартном графическом интерфейсе. С помощью утилиты Enterprise Manager осуществляется администрирование
сервера и баз данных. Программа-менеджер (утилита SQL Server
Management Studio) объединяет функции утилит SQL Server
Query Analyzer и SQL Server Enterprise Manager.
В принципе на одном компьютере может одновременно выполняться несколько экземпляров сервера, в том числе и разных версий [2]. Сервер базы данных, с которым выполняется соединение,
называется текущим экземпляром сервера. Только один экземпляр
является экземпляром по умолчанию. Каждый экземпляр сервера
содержит свои версии системных баз данных, имеет набор своих характеристик и содержит свой набор пользовательских баз данных.
При запуске и установке экземпляра сервера используется его имя.
При инсталляции задается имя экземпляра по умолчанию.
Запуск на выполнение экземпляра сервера можно несколькими
способами:
1. из командной строки:
Net start ′SQL Server (имя сервера)′;
2. из программы Configuration Manager:
⇒ Пуск/Все программы/Microsoft SQL Server 2012/
Configuration Tools/SQL Server Configuration Manager;
3. с помощью программы SQL Server Management Studio:
⇒ Пуск/Все программы/Microsoft SQL Server 2012/SQL
Server Management Studio.
Подробнее эти вопросы рассмотрены в работе [2].
В учебном процессе целесообразно использовать один экземпляр
сервера, который устанавливается локально на каждом учебном
компьютере. Запуск на выполнение экземпляра сервера осуществляется по третьему способу.
Среда SQL Server в качестве языка запросов использует расширение языка ANSI SQL, которое называется TRANSACT-SQL или
6
сокращенно T-SQL [2], [6], [15]. Язык SQL стандарта ANSI позволяет только создавать, модифицировать базу данных и ее объекты, а также манипулировать данными базы. Использование языка
TRANSACT-SQL в SQL Server обеспечивает возможность программирования, от управления потоком команд до модульности.
1.2. Замечания по установке и настройке
Microsoft SQL Server 2012
Корпорация Microsoft предлагает следующие версии SQL Server
2012 [6]:
• Express Edition. Это – облегченная версия, предназначенная
для разработчиков приложений. Данный продукт содержит базовую программу Express Manager и поддерживает интеграцию
общеязыковой среды выполнения и собственный язык XML. Упростить управление базой данных можно с помощью компонента
SQL Server Management Express для SQL Server Express, который
можно бесплатно загрузить по адресу http://msdn.microsoft.com/
express.
• Workgroup Edition. Эта версия предназначена для малого бизнеса и для использования ее на уровне отделов предприятия. Версия поддерживает системы с двумя процессорами и двумя Гбайтами оперативной памяти.
• Standard Edition. Данная версия предназначена для малого и среднего бизнеса. Версия поддерживает системы с четырьмя
процессорами и двумя Тбайтами оперативной памяти и содержит
весь диапазон возможностей бизнес-аналитики, включая службы
Reporting Services, Analysis Services и Integration Services. Версия
не содержит некоторых возможностей из версии Express Edition.
• Web Edition. Версия предназначена для поставщиков вебхостинга. Кроме компонента Database Engine она содержит службы отчетности Reporting Services. Поддерживает системы до четырех процессоров и двух Тбайт оперативной памяти.
• Enterprise Edition. Это – специальная версия севера, предназначенная для приложений, критичных по времени и может иметь
большое количество пользователей. В отличие от версии Standard
Edition, данная версия содержит дополнительные возможности,
такие как возможность получения мгновенных снимков состояния
базы данных, а также возможность выполнять секционирование
данных. Версия обеспечивает онлайн-поддержку баз данных.
7
8
Рис. 1. Открытие окна диспетчера конфигурации служб Reporting Services
• Developer Edition. Эта версия позволяет разработчикам создавать и тестировать приложения любого типа для 32-х и 64-х разрядных платформ SQL Server. Версия содержит всю функциональность версии Enterprise Edition.
• Datacenter Edition. Новая версия SQL Server R2, для которой
нет ограничений по памяти.
• Parallel Data Warehouse Edition. Эта версия предназначена
для больших хранилищ данных размером от 10 Тбайт до одного
Пбайт (петабайт, 1 Пбайт = 1024 Тбайт). Для управления такими
громадными базами данных в ней используется архитектура массово-параллельной обработки (МПО), представленная корпорацией Microsoft в операционных системах Windows с возможностями
высокопроизводительных вычислений.
Для учебного процесса рекомендуется использовать версию SQL
Server 2012 Developer Edition.
Установка сервера достаточно трудоемкое занятие. Поэтому
лучше всего обратиться к мастеру установок Microsoft и Альта
Софт по указанным ссылкам:
http://msdn.microsoft.com/ru-ru/library/ms143219.aspx;
http://www.alta.ru/mssqlserver2012.php.
Установленный SQL Server имеет стандартный ТСР-порт: 80, что
может привести к конфликтам с другими программами на вашем
компьютере, имеющими доступ к веб-службам, и в которых также
может использоваться такой же порт. Данное «недоразумение» можно устранить через службы Reporting Services. Это нужно обязательно сделать, если вы установили SQL Server на своем компьютере.
Для этого через меню «Пуск» необходимо открыть папку
«Microsoft SQL Server 2012» и вложенную в нее папку «Средства настройки», содержащую «Диспетчер конфигурации служб
Reporting Services» (рис. 1). Запустить эту программу можно, если
вы обладаете правами Администратора системы. Иначе обратитесь
за помощью к администратору системы вашей компании.
Откроется диалоговое окно URL-адреса веб-службы (рис. 2),
в котором необходимо вручную изменить значение ТСР-порта с 80,
например на 3333, как указано на рисунке, или на любое другое
значение (888, 999 и пр.), которое не используется другими программами, установленными на вашем компьютере. Далее по кнопке «Дополнительно» (рис. 2) нужно открыть окно URL-адреса диспетчера отчетов (рис. 3), в котором также вручную изменить значение ТСР-порта с 80 на то значение, которое вы задали в окне «URLадрес веб-службы» (в нашем случае 3333).
9
Рис. 2. Настройка «URL-адрес веб – службы»
Рис. 3. Настройка «URL – адрес диспетчера отчетов»
10
1.3. Средства управления Microsoft SQL Server 2012
К средствам управления Microsoft SQL Server 2012 относятся
[2], [6]:
• Утилита SQL Server Management Studio, которая представляет собой интегрированную среду для доступа, управления и настройки, а также для администрирования и разработки компонентов сервера. Среда Management Studio позволяет работать с сервером администраторам и разработчикам любого уровня подготовки.
В качестве примера на рис. 4 показана среда SQL Server
Management Studio в режиме выполнения запроса, в которой открыты окна обозревателя объектов, создания запросов, свойств и
окно вывода результатов запроса и сообщений.
Окно обозревателя объектов содержит системные и пользовательские базы данных, а также все необходимые сервисы и службы, обеспечивающие их работоспособность и защиту.
Окно свойств позволяет просматривать и изменять свойства объектов SQL Server Management Studio.
Окно создания запросов позволяет реализовать все виды запросов SQL Server.
Окно вывода используется для вывода результатов выполнения
запросов и других внешних данных, а также сообщений.
Рис. 4. Среда SQL Server Management Studio
в режиме выполнения запроса
11
• Диспетчер конфигурации сервера, который обеспечивает базовые возможности управления конфигурациями для служб, серверных и клиентских протоколов, а также для псевдонимов клиентов
SQL Server.
• SQL Server Profiler, который предоставляет графический
пользовательский интерфейс для наблюдения за экземпляром компонента Database Engine (ядра СУБД) или служб Analysis Services.
• Помощник по настройке ядра СУБД, который помогает создавать оптимальные наборы индексов, индексированных представлений и секций.
• Среда Business Intelligence Development Studio, которая представляет собой интегрированную среду разработки для служб
Analysis Services, Reporting Services и Integration Services.
• Компоненты связи, которые устанавливают связь между серверами, клиентами и сетевыми библиотеками.
1.4. Основные компоненты Microsoft SQL Server 2012
СУБД SQL Server содержит большой набор сервисных служб, основными из которых являются [2]:
• Службы ядра СУБД (Database Engine), которые предназначены для хранения, обработки и обеспечения безопасности данных, а
также для репликации, полнотекстового поиска и средств управления реляционными и XML-данными.
• Службы Analysis Services, которые содержат средства создания и управления приложениями интерактивной аналитической
обработки (OLAP – Online Analytical Processing) и приложениями
анализа данных. Аналитические службы позволяют собирать данные из нескольких источников, например, реляционных баз данных, и обрабатывать их различными способами [14].
• Службы Reporting Services, которые включают в себя серверные и клиентские компоненты для создания, управления и развертывания табличных, матричных и графических отчетов, а также
отчетов в свободной форме. Эти службы можно использовать для
разработки приложений отчетов.
• Службы Integration Services представляют собой набор графических средств и программируемых объектов для преобразования,
копирования и перемещения данных. Эти службы предназначены
для слияния данных из разнородных источников, а также загрузки
данных в хранилища и пр. [14].
12
• Full-Text Search (полнотекстовый поиск), который содержит
функциональность, необходимую для выполнения полнотекстовых запросов к простым символьным данным в таблицах сервера.
• Replication (репликация), которая представляет собой набор
технологий копирования и распространения данных и объектов
между базами данных, а также технологий синхронизации баз данных для поддержания согласованности [2].
• Service Broker, который обеспечивает SQL Server Database
Engine встроенной поддержкой обмена сообщениями и очередями
приложений.
• Notification Services, которые представляют собой платформу
для разработки приложений, формирующих и отправляющих уведомления, и которые являются движком, запускающим эти приложения.
Важной задачей при работе с SQL Server является вопрос сохранения информации о данных в базе. Информация о хранении данных называется метаданными.
Метаданные хранятся в словаре данных, который состоит из набора системных таблиц [3], [6]. Кроме системных таблиц, многие
из которых присутствуют во всех базах, существует несколько системных баз данных, содержащих информацию о конкретной системе. Системными базами данных являются:
• master,
• model,
• msdb,
• tempdb.
Существует еще одна скрытая системная база данных
(resource), которая хранит системные объекты, входящие в состав сервера. Эта база не отображается в списке системных баз
данных в окне обозревателя объектов, открытого в среде SQL
Server Management Studio (рис. 5). Никаких сведений о базе данных resource естественными средствами получить невозможно.
Напрямую к ней нельзя обратиться, однако существуют средства,
Рис. 5. Системные базы данных
13
такие как системные функции и системные представления, позволяющие получить из нее некоторые данные [2].
База данных master является наиболее важной системной базой
данных на сервере. Она содержит данные о сервере такие, как регистрация обращений к серверу, параметры конфигурации сервера базы
данных и их отображение на физические устройства, сведения обо
всех пользовательских базах данных, созданных в экземпляре сервера. Базе данных master соответствуют файлы данных master.mdf
и журнала транзакций mastlog.ldf. Журналы транзакций нужны
для того, чтобы фиксировать все изменения с данными.
База данных model хранит шаблоны для всех вновь создаваемых пользователем баз данных. Базе данных model соответствуют
файлы данных model.mdf и журнала транзакций modellog.ldf.
При создании новой пользовательской базы данных в нее из базы
данных model копируются типы данных.
Вновь создаваемым базам данных присваиваются по умолчанию
значения параметров из базы данных model. Если добавить новые
объекты в базу данных model, то эти объекты также будут копироваться во вновь создаваемые пользовательские базы данных текущего экземпляра сервера [2].
База данных msdb содержит данные для планирования заданий
и оповещений, а также данные об операторах. Эта база также хранит историю создания резервных копий и сведения о репликациях
(файлы msdbdata.mdf и msdblog.ldf).
База данных tempdb содержит временные объекты, создаваемые пользователем, а также внутренние объекты, создаваемые сервером для выполнения отдельных процессов или запросов, а именно: временные таблицы, намеренно созданные разработчиком, и
временные таблицы с промежуточными результатами, используемыми сервером при обработке запросов. Базе данных tempdb соответствуют файлы данных tempdb.mdf и журнала транзакций
templog.ldf. Использование этой базы данных во многих случаях
позволяет повысить производительность системы при выполнении
различных операций с базами данных. Файлы базы данных tempdb
динамически увеличиваются по мере необходимости. При каждом
запуске системы размер tempdb сбрасывается до первоначального
значения, которым является размер базы данных model.
Скрытая системная база данных resource в схеме sys содержит
системные объекты сервера такие, как системные хранимые процедуры, представления, функции, которые доступны из любой пользовательской и системной базы данных.
14
Любая пользовательская база данных также содержит файлы
с данными и файлы журналов транзакций. Файлы данных могут принадлежать одной, основной или первичной группе файлов
(Primary). В этом случае они имеют расширение.mdf. Но могут
быть созданы и файлы вторичной группы. В этом случае они имеют
расширение.ndf. Файлы журналов транзакций всегда имеют расширение.ldf.
В табл.1.1 дана краткая информация о назначении системных
баз данных.
В данной работе в качестве примера рассматривается простая
пользовательская реляционная база данных postavka, спроектированная комбинированным методом [12] и состоящая из четырех
взаимосвязанных таблиц, в которых хранятся данные из предметной области ПОСТАВКА_ДЕТАЛЕЙ [1]:
• таблица Firm содержит данные о фирмах, которые представляют поставщики (NFM – номер фирмы, FIM – название фирмы);
Таблица 1.1
Системные базы данных
Системные
базы
master
model
msdb
tempdb
resource
Назначение
Отвечает за хранение всех данных системного уровня для
экземпляра сервера. Содержит всю системную информацию,
в том числе метаданные, такие как сведения об учетных записях, конечных точках и связанных серверах, параметры
конфигурации системы, сведения об инициализации, а также информацию обо всех других базах данных.
Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре сервера. Изменение размера и параметров (сортировки, модели восстановления и других) базы
данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения
Используется агентом сервера для планирования задач и
предупреждений.
Рабочее пространство для временных объектов или взаимодействия результирующих наборов
Содержит системные объекты, которые входят в состав сервера. Системные объекты физически хранятся в базе данных
resource, но логически отображаются в схеме sys любой
базы данных. Содержимое базы данных resource обычно
используется для системных обновлений
15
Рис. 6. Схема данных базы postavka
• таблица Post содержит данные о поставщиках (PN – номер,
PIM – имя, GOR – город, TEL – телефон поставщика, Comment- комментарий о поставщике, NFM – фирма, которую представляет поставщик);
• таблица Det содержит данные о поставляемых деталях (DN –
номер, DIM – имя, CENA – цена детали, KOL_SKLAD – количество на
складе);
• таблица PD содержит данные о поставках (PN – номер поставщика, DN – номер поставляемой детали, KOL – количество поставленных деталей, DATA – дата поставки). Предполагается, что в один
день допускается только одна поставка.
Схема данных представлена на рис. 6.
Таблицы со стороны связи «1» называются родительскими, а со
стороны связи «много» (∞) – дочерними [11]. Тогда таблицы Post
и Det являются родительскими таблицами для дочерней таблицы
PD. Таблица Post, в свою очередь, является дочерней для родительской таблицы Firm. В родительских таблицах для первичных ключей рекомендуется задавать целочисленные типы данных, и, если
возможно, определять их счетчиками. Счетчик считается длинным
целым (тип данных int). Значение поля счетчика вводить не надо,
оно вводится автоматически с помощью датчика случайных чисел
последовательными числами с шагом приращения, равным единице (по умолчанию). Однако шаг приращения значений можно задавать вручную или задавать случайными значениями, полученными
16
датчиком случайных чисел. В этом случае поля связи в дочерних
таблицах уже нельзя задавать счетчиками, так как все счетчики
работают независимо один от другого. Их нужно определять как
длинное целое int. В приложении 1 приведена таблица основных
типов данных для Microsoft SQL Server 2012.
17
2. СОЗДАНИЕ БАЗЫ ДАННЫХ НА MS SQL SERVER 2012
Создавать пользовательские базы данных на сервере можно несколькими способами:
• используя среду (программу-менеджер) SQL Server Management Studio,
• используя adp-проект, если база данных уже создана в среде
MS Access версии 10 и ниже,
• импортируя таблицы из базы данных MS Access (файл.mdb)
в базу данных на сервере,
• путем переноса базы данных, созданной в среде SQL Server
с одного компьютера на другой,
• операторами T-SQL.
Рассмотрим эти способы подробнее.
2.1. Создание базы данных
с помощью SQL Server Management Studio
Рассмотрим создание базы данных postavka (рис. 6).
Для этого нужно выполнить следующую последовательность
действий:
– запустить программу-менеджер (SQL Server Management
Studio) командой
⇒ Пуск/ Все программы/MS SQL Server 2012/ SQL Server
Management Studio;
– выполнить соединение с сервером с помощью кнопки «Соединить» (рис. 7, слева); при этом выполняется, так называемое, доверительное соединение с сервером (проверка подлинности Windows),
о котором подробнее будет сказано в разделе 10 данного пособия.
– открыть в обозревателе объектов папку «Базы данных», чтобы
убедиться в том, что ваша база еще не создана, и на папке «Базы
данных» из контекстного меню выполнить команду
⇒ Создать базу данных… (рис. 7, справа).
Откроется общая страница диалогового окна «Создание базы
данных» (рис. 8), в котором необходимо обязательно указать имя
создаваемой базы данных (postavka). Остальные параметры задаются по умолчанию. Однако их можно изменить принудительно.
Например, местоположение файлов данных и журналов транзакций, заданное по умолчанию как C:\Program Files\Microsoft SQL
Server\MSSQL…MSSQLSER-VER\MSSQL\DATA, можно изме18
Рис. 7. Соединение с сервером (слева)
и открытие окна для создания базы данных (справа)
нить с помощью кнопок с тремя точками (выделены на рисунке
тонкой линией). Пусть новым местоположением этих файлов будет, например, папка C:\temp.
Для этого нажмите на кнопку с тремя точками, например, для
типа файла «Данные». В открывшемся окне «Поиск папки», выберите папку С:\temp и нажмите на кнопку «ОК». В окне «Создание базы данных» появится новое местоположение файла данных
(С:\temp). Проделайте те же действия для типа файла «Журнал»
(рис. 9). При нажатии на результирующую кнопку «ОК» в окне
«Создание базы данных», в обозревателе объектов появится новая
база данных postavka.
Теперь можно создавать таблицы для базы данных postavka.
Создание таблицы Post
Сначала нужно создать структуру таблицы, а затем уже заполнять ее данными.
Для создания структуры таблицы нужно на папке Таблицы
из контекстного меню выполнить команду ⇒ Создать таблицу…
(рис. 10).
Откроется окно для создания структуры таблицы (рис. 11),
в котором нужно задать параметры структуры (имена и типы полей, первичный ключ и пр.). Имена полей, а также имена любых
объектов базы данных задаются по правилу задания имен (идентификаторов) на языке T-SQL [6], [15]. Идентификатор состоит из
19
20
Рис. 9. Настройка местоположения файлов данных и журналов транзакций
Рис. 8. Задание параметров создаваемой базы данных
Рис. 10. Создание таблицы
строки длиной от одного до 128 символов, которая может содержать буквы, цифры и символы: подчеркивания (_), @, #, и &. Первым символом должна быть буква или символ подчеркивания (_),
а символы @, # и & имеют специальное назначение (см. Создание
структуры таблицы в разделе 3.3.1). Идентификаторы не должны
совпадать с ключевыми словами Transact-SQL. Эти правила не относятся к идентификаторам в скобках, которые могут содержать
или начинаться с любого символа. Так, имя поля может содержать
пробелы. В этом случае такой идентификатор должен заключаться
в квадратные скобки, например [номер поставщика].
Рисунок 11 демонстрирует создание структуры таблицы Post.
На рис. 11 (слева) задается имя первого поля (столбца) PN (ключевое поле) таблицы и его тип int (выбирается из списка). Чтобы это
поле объявить первичным ключом (рис. 11, справа) из контекстного меню на имени поля нужно выполнить команду ⇒ Задать первичный ключ. Для первичного ключа запрещается использовать
значения Null (неопределенные или не введенные значения). Поэтому флажок «Разрешить значения Null» должен оставаться выключенным. В окне «Свойства столбца» (рис. 11, слева) отобразятся общие свойства столбца PN.
В приложении 1 представлены основные типы данных MS SQL
Server 2012.
21
22
Рис. 11. Создание структуры таблицы Post
Для таблицы Post первичный ключ можно объявить счетчиком. Для этого нужно кроме общих свойств, для столбца PN задать
свойство «Спецификация идентификатора», как это показано на
рис. 11 (справа). Тогда при заполнении таблицы Post данными
значения поля PN будут вводиться автоматически от 1 с шагом 1.
После задания параметров остальных столбцов таблицы ее нужно закрыть. Появится окно сохранения структуры таблицы и диалоговое окно «Выбор имени», в котором необходимо ввести имя таблицы, как показано на рис. 12.
Для поля GOR в таблице Post можно задать значение по умолчанию (рис. 13).
Отметим, что иногда сервер может внести некоторые исправления для введенных вами значений. Так, если в качестве значения
по умолчанию для поля GOR вы введете СПб, то сервер заключит это
значение в кавычки (символьное значение) и добавит префикс N,
так как для этого поля задан тип данных nvarchar (рис. 13).
Аналогично можно создать структуру остальных таблиц базы
данных postavka. Структуру всех таблиц базы данных можно увидеть в обозревателе объектов (рис. 14).
Рис. 12. Сохранение структуры таблицы
23
Рис. 13. Задание значения по умолчанию для поля GOR
Создать составной первичный ключ в таблице PD можно, удерживая клавиши Shift или Ctrl.
Создание диаграммы (схемы данных)
Диаграмму нужно строить на пустых таблицах.
Для создания диаграммы базы данных нужно на папке «Диаграммы баз данных» в обозревателе объектов для базы данных postavka
из контекстного меню выполнить команду: ⇒ Создать диаграмму
базы данных (рис. 15, слева). Появится окно сообщения, с которым
нужно согласиться, нажав на кнопку «Да» (рис. 15,справа).
Откроется пустое окно диаграммы базы данных и окно с перечнем таблиц базы данных postavka, которые нужно добавить на
поле диаграммы. Далее таблицы нужно связать между собой с помощью мыши, перемещая курсор от первичного ключа родительской
таблицы к внешнему ключу связи дочерней таблицы. В результате
получим диаграмму, неудобную для использования, в которой неясно по каким ключам связаны таблицы (рис. 16, слева). Выделяя
мышью каждую связь и подтаскивая ее к нужным полям таблицы,
получим удобную для использования диаграмму (рис. 16, справа).
Установленные на диаграмме связи обеспечивают целостность
данных [11]. Можно ее усилить, обеспечив каскадное обновление
связанных полей и каскадное удаление связанных записей. Для
этого нужно воспользоваться окном свойств объектов. Это окно может быть открыто по умолчанию, а если – нет, то открыть его можно из линейки главного горизонтального меню командой ⇒ Вид/
Окно свойств. Итак, нужно открыть диаграмму и окно свойств.
24
25
Рис. 15. Создание диаграммы базы данных postavka
Рис. 14. Структура таблиц базы данных postavka в обозревателе объектов
26
Рис. 17. Задание каскадного обновления и каскадного удаления для выделенной связи
Рис. 16. Диаграммы базы данных postavka
В окне свойств отображаются свойства выделенного объекта. Если
на диаграмме нет выделенного объекта, то отображаются свойства
диаграммы, если выделена таблица, то отображаются свойства
этой таблицы, если выделена связь, то отображаются свойства выделенной связи. Выделяя связи последовательно одну за другой,
для каждой из них устанавливаем нужное значение в свойстве
«Спецификация INSERT и UPDATE». На рис. 17 показано, как это
делается для связи таблиц Post и Firm.
Диаграммы графически отображают структуру баз данных. При
помощи диаграмм можно также не только создавать связи, но и
изменять таблицы, столбцы, связи и ключи, не обращаясь к обозревателю объектов. Диаграммы автоматически отражают любые
внесенные изменения.
На рис. 17 таблицы представлены именами столбцов. Однако
имеется возможность изменять макеты представления таблиц,
оставляя для вывода только имя таблицы или наоборот отображать
более подробную информацию.
Для изменения макета представления таблиц необходимо выделить отдельную таблицу или все таблицы и из контекстного меню,
открытого на имени таблицы, выполнить команду ⇒ Вид таблицы
(рис. 18, слева).
Иногда для отображения межтабличных связей в базах данных
с большим количеством таблиц удобно выводить их имена командой ⇒ Вид таблицы/Только имя (рис. 18, справа).
Можно прямо на диаграмме командой ⇒ Вид таблицы/Стандартная редактировать структуру выделенной таблицы (рис. 19,
слева), а из контекстного меню, открытого на пустом поле диаграммы, можно создать новую таблицу (рис. 19, справа), а затем связать
ее с одной из существующих таблиц.
Для заполнения таблицы данными или для изменения уже существующих данных нужно на папке с именем таблицы в обозревателе объектов из контекстного меню выбрать команду ⇒ Изменить
первые 200 строк (рис. 20).
А далее нужно строка за строкой вводить данные, пополняя содержимое таблицы или исправлять содержимое полей выбранной
строки (записи). По команде
⇒ Выбрать первые 1000 строк
можно только просматривать данные таблицы.
Для изменения структуры таблицы нужно на папке с именем таблицы в обозревателе объектов из контекстного меню выбрать команду ⇒ Проект.
27
28
Рис. 19. Редактирование (слева) и создание структуры таблицы (справа) на диаграмме
Рис. 18. Изменение макета таблиц
Рис. 20. Работа с данными таблицы
2.2. Создание базы данных с использованием ADP-проекта
Данный метод актуален только в том случае, если у вас установлена версия не выше MS Access 2010. В более поздних версиях данная технология не реализуется.
Если уже существует база данных в среде MS Access, то для создания этой базы на сервере можно воспользоваться adp-проектом,
который позволяет связать пользовательский интерфейс MS Access
с источником данных SQL Server.
Используя программу-мастер Upsizing Wizard, можно конвертировать существующую базу данных из MS Access в новый
adp-проект с созданием базы данных на сервере [1]. Рассмотрим,
как это нужно сделать на примере базы данных postavka (рис. 6),
созданной в среде MS Access 2010.
Для этого необходимо выполнить следующую цепочку действий:
• Открыть существующую базу данных postavka в Access 2010.
• Если появится кнопка «Включить содержимое», то нажать ее,
иначе продолжать дальше.
• Открыть вкладку «Работа с базами данных» и на панели «Перемещение данных» нажать на кнопку «SQL Server», по которой
запустится мастер преобразования.
• На первом шаге работы мастера нужно указать, что создается
новая база данных (рис. 21, слева);
• По кнопке «Далее» (рис. 21, слева) перейти ко второму шагу
мастера (рис. 21, справа);
29
30
Рис. 21. Первый (слева) и второй (справа) шаги работы мастера преобразования
• На втором шаге мастера нужно выбрать из списка или ввести
с клавиатуры имя сервера (обычно это имя компьютера), включить флажок «Доверительное соединение», задать имя базы данных на сервере (обычно по умолчанию мастер задаст имя типа
postavkaSQL), которое исправим на postavka.
• На третьем шаге нужно выбрать таблицы, которые необходимо
переместить из базы данных Access в новую базу на сервере. Чтобы
избежать потенциальных проблем в дальнейшем, рекомендуется
перенести все таблицы.
• На четвертом шаге рекомендуется выбрать свойства таблиц,
которые должны быть перенесены в новую базу данных. Для этого нужно включить соответствующие флажки, как показано на
рис. 22 (слева). При этом можно переносить следующие объекты:
– Индексы. Мастер создаст индексы SQL Server, соответствующие имеющимся у таблиц индексам Access. Однако при связывании
двух таблиц Access создаст скрытый индекс, поэтому в базе данных
SQL Server может оказаться больше индексов, чем вы предполагали.
– Правила. Мастер попытается преобразовать существующие
в Access условия на значения (validation rules) в соответствующие ограничения для таблиц SQL Server.
– Значения по умолчанию. Мастер попытается преобразовать
существующие значения по умолчанию в соответствующие ограничения для таблиц SQL Server.
– Межтабличные связи. Мастер создаст в базе данных SQL
Server связи, соответствующие связям в базе данных Access. При
этом если выбрать опцию DRI, то связи между таблицами будут реализованы в виде ограничений Primary Key и Foreign Key. При
выборе опции Триггеры связи будут поддерживаться триггерами
с помощью кода Transact-SQL. В дополнение к перечисленным опциям можно выбрать еще две опции:
 Добавить поля штампа времени в таблицы? Если в таблице
не определен первичный ключ, то мастер добавит в нее поле типа
timestamp. Обычно целесообразнее выбрать из списка ответ: Да,
определяется системой, поскольку мастер, как правило, принимает верные решения.
 Создать только структуру таблицы и не преобразовывать
данные. Эта опция полезна в тех случаях, когда с переносом базы
данных с помощью мастера возникли проблемы, и вы хотите проверить, что он делает. Обычно этот флажок не устанавливают.
• На пятом шаге определяют изменения, которые предстоит внести мастеру в наше приложение (рис. 22, справа).
31
Можно внести следующие изменения:
 Создать новое приложение Access «клиент-сервер» (рекомендуется с целью ознакомления со структурой adp-проекта). Мастер создаст базу данных SQL Server и свяжет ее таблицы с новым
проектом Access. Существующие объекты приложения Access будут перенесены из базы данных в новый проект;
 Имя файла ADP. Мастер по умолчанию задает имя и расположение файла для нового проекта, которые можно принудительно
изменить.
 Связать таблицы SQL Server с существующим приложением (рекомендуется для отслеживания в таблицах adp-проекта всех
изменений, сделанных в таблицах сервера и наоборот). Мастер создаст базу данных SQL Server и свяжет ее таблицы с текущей базой
данных Access;
 Не изменять приложение. Мастер создаст базу данных SQL
Server, но не станет создавать adp-проект;
 Сохранить пароль и код пользователя. Если включить соответствующий флажок, то указанные на втором шаге имя и пароль
пользователя будут сохранены в строке подключения проекта. Это
облегчит открытие проекта, но оставит данные незащищенными.
Поскольку на втором шаге (рис. 21 справа) мы использовали доверительное соединение, то флажок включать не надо.
Рекомендуется оставить установки, как на рис. 22.
После того, как сделаны все установки, нажмите кнопку «Готово». Появится окно шестого шага мастера преобразования
(рис. 23, слева), на котором надо включить переключатель «Открыть файл ADP». После нажатия кнопки «Готово» может появиться окно предупреждения о нарушении безопасности, в котором нужно нажать кнопку «Открыть». Тогда мастер начнет выполнять преобразование (рис. 23, справа), которое может продолжаться несколько минут при большом количестве объектов в базе
данных.
В результате мастер выполнит следующие действия:
• Создаст новую базу данных на сервере.
• Перенесет в эту базу таблицы с установленными вами свойствами.
• Создаст новый adp-проект Access и свяжет его с только что
сформированной базой данных на сервере.
• Создаст в базе данных сервера представления и хранимые процедуры, соответствующие запросам, которые имелись в базе данных Access (с теми же именами), и преобразует операторы Jet-SQL
32
33
Рис. 23. Шестой шаг (слева) и завершение работы мастера преобразования (справа)
Рис. 22. Четвертый (слева) и пятый (справа) шаги работы мастера преобразования
в операторы Transact-SQL. В некоторых случаях для создания эквивалента запроса Access в базе данных сервера может потребоваться и представление, и хранимая процедура.
• Скопирует формы и отчеты из базы данных Access в adp-проект.
В некоторых случаях свойства RecordSource, ControlSource и
RowSource форм, отчетов и элементов управления могут быть модифицированы.
• Скопирует все макросы и модули из базы данных Access в adpпроект, ничего в них не меняя.
• Закончив работу, мастер выведет отчет, в котором будут перечислены все выполненные мастером действия и указаны, какие
объекты ему не удалось перенести в новое приложение. После закрытия отчета вместо текущей базы данных Access откроется созданный adp-проект.
Если мастеру не удается преобразовать базу данных Access
в adp-проект, обратитесь к преподавателю.
Перенос приложения на платформу SQL Server может вызвать
некоторые проблемы. Так, преобразование базы данных Access
в проект требует учета особенностей защиты Access, а именно:
– Для всех объектов, которые вы хотите перенести в новое приложение, у вас должно быть разрешение Read Design, а для таблиц, если вы хотите переносить в базу данных SQL Server их данные, еще и разрешение Read Data.
– Для создания новой базы данных на сервере у вас должно быть
разрешение Create Database, а также разрешение Select для таблиц системной базы данных. Кроме того, необходимы разрешения
Create Table и Create Default для преобразования таблиц.
– Мастер преобразования не сможет конвертировать базу данных MDE в проект.
Некоторые составляющие базы данных Access вообще не могут
быть перенесены в новый проект Access, а именно [1], [8]:
– маски ввода,
– некоторые условия на значения и значения по умолчанию,
– поля гиперссылок будут преобразованы в текстовые, после
чего перестанут функционировать в качестве гиперссылок.
Кроме того, нужно внимательно проверить все перенесенные
в проект модули. Поскольку для проекта Access ядро Jet не загружается, а, следовательно, и не загружается и библиотека DAO.
В новом проекте придется вручную перевести весь имеющийся код
на использование ADO.
34
Мастер не особенно силен в преобразовании запросов. Внимательно просмотрев его отчет, нужно выяснить, все ли запросы он
перенес в новую базу данных. Для тех запросов, перенос которых
не удался, мастер включает в отчет сгенерированные им операторы
SQL, так что можно взять их за основу и самостоятельно довести до
рабочего состояния, создав хранимые процедуры и представления.
На SQL Server не будут перенесены:
– перекрестные запросы,
– запросы на выборку с параметрами,
– запросы на выборку на базе других запросов,
– запросы к серверу и управляющие запросы,
– все запросы, прямо или косвенно базирующиеся на запросе,
который не удалось перенести. А поскольку многие формы, отчеты, списки и поля со списками базируются на запросах, с их переносом в новый проект могут быть проблемы.
Несмотря на перечисленные выше проблемы, все-таки стоит
пользоваться мастером преобразования в формат SQL Server, так
как он прекрасно выполняет перенос в новое приложение как минимум части объектов (например, таблицы, часть запросов, часть
форм и отчетов), так что значительный объем работы он вместо вас
все-таки сделает. Однако без тщательного тестирования получившегося приложения не обойтись.
При переносе таблиц сохраняются важные параметры таблиц:
первичные ключи, связи между таблицами, а также механизм обеспечения целостности данных, включая каскадное обновление связанных полей и каскадное удаление связанных записей. Этот сервис отсутствует при импортировании таблиц из Access на сервер.
2.3. Импорт таблиц из базы данных MS Access
в базу данных на сервере
Если уже существует база данных, например, в среде Access,
то преобразование ее в формат SQL Server можно выполнить, используя процедуру импорта таблиц из базы Access в базу данных
на сервере. Однако мастер импорта на сервере работает только
с mdb-файлами. Поэтому, если база данных создана в версии 2007
и выше, то accdb-файл нужно предварительно преобразовать
в mdb-файл. Эта процедура выполняется примерно одинаково для
любой версии Access 2007 и выше. Рассмотрим такое преобразование на примере.
35
Пример 1. Пусть создан файл postavka.accdb базы данных
в среде Access 2010. Для преобразования его в файл postavka.mdb
нужно выполнить следующую последовательность шагов:
• запустить MS Access 2010;
• ввести имя создаваемой базы данных postavka;
• выбрать папку для размещения файла postavka.mdb новой
базы данных (рис. 24);
• в окне «Файл новой базы данных» из списка выбрать тип файла
(Базы данных Microsoft Access 2002-2003) и нажать кнопку «ОК», а затем – кнопку «Создать». Откроется окно новой базы данных (рис. 25).
• импортировать в новую базу таблицы из базы данных (файла
postavka.accdb); для этого во вкладке «Внешние данные» из раздела «Импорт и связи» нажать кнопку Access (рис. 25).
Откроется окно «Внешние данные – база данных Access» (рис. 26,
слева), в котором нужно с помощью кнопки «Обзор» выбрать источник данных (файл postavka.accdb). После закрытия этого окна
откроется окно со списком импортируемых таблиц (рис. 26, справа).
Нужно выделить все таблицы и кнопкой «ОК» завершить импорт.
Теперь можно выполнять импорт таблиц из базы данных
postavka.mdb в базу данных с тем же именем postavka на сервере. Для этого нужно:
• запустить программу SQL Server Management Studio;
• создать пустую базу данных postavka на сервере, как это было
описано в разделе 2.1;
• в обозревателе объектов сервера из контекстного меню на имени базы данных postavka выполнить команду ⇒ Задачи/Импорт
данных (рис. 27, слева); запустится программа мастера импорта и
экспорта (рис. 27, справа);
• переходя по кнопке «Далее» от одного шага мастера к другому,
нужно вводить требуемые данные для работы мастера (рис. 28, 29, 30);
Работа мастера не должна вызывать затруднений. Однако требует пояснения шаг мастера, изображенного на рис. 29, слева. На этом
шаге нужно включить флажки у всех импортируемых таблиц, а для
таблицы PD нужно еще нажать кнопку «Изменить», и в открывшемся окне «Сопоставления столбцов» выключить выделенные флажки
у полей PN, DN и DATA, так как эти поля образуют составной первичный ключ и для них не допускаются значения Null. Это объясняется тем, что импорт выполняется недостаточно корректно, а именно:
• первичные ключи не импортируются;
• во все импортированные поля разрешается вводить значения
Null, что не всегда верно;
36
37
Рис. 24. Создание новой базы данных (файла postavka.mdb)
38
Рис. 26. Импорт таблиц из файла postavka.accdb в файл postavka.mdb
Рис. 25. Запуск импорта таблиц
39
Рис. 27. Запуск мастера импорта и экспорта SQL Server
40
Рис. 28. Выбор источника данных
41
Рис. 29. Выбор исходных таблиц для импорта
42
Рис. 30. Завершение работы мастера
• не импортируются ограничения целостности данных, в частности каскадные обновление и удаление.
Поэтому после завершения импорта все таблицы требуют доработки. Для каждой перенесенной на сервер таблицы нужно заново
устанавливать первичный ключ и такие параметры целостности
данных, как каскадное обновление и каскадное удаление. В тех таблицах, которые имеют простой первичный ключ (одиночный атрибут), при его установке автоматически запрещается использование
значений Null в поле ключа (таблицы Post, Det и Firm). А для таблиц с составным первичным ключом (таблица PD) запрет значений
Null в полях ключа (поля PN, DN, DATA) должен быть установлен
принудительно до завершения импорта. Иначе это может привести
к отказу сохранения таблиц с установленными составными ключами после завершения импорта.
Указанных проблем не существует для предыдущего метода
с использованием adp-проекта, в котором перечисленные выше некорректные действия импорта выполняются корректно.
2.4. Перенос базы данных с одного компьютера на другой
Выполнить перенос базы данных с одного компьютера на другой
можно разными способами. Рассмотрим два способа:
• отсоединение и присоединение базы данных в режиме администратора;
• использование резервного копирования базы данных с последующим ее восстановлением из резервной копии.
Эти способы можно реализовать как в диалоге, используя среду
SQL Server Management Studio, так и операторами T-SQL. В данном
учебном пособии рассмотрим диалоговый вариант.
2.4.1. Отсоединение и присоединение базы данных
Этот способ доступен только пользователю с правами администратора [2].
Пример 2. Пусть требуется перенести базу данных postavka
c одного компьютера на другой. Для этого нужно выполнить следующие шаги:
• перевести базу данных postavka в автономный режим, выполнив из контекстного меню на имени базы данных команду:
⇒ Задачи/Перевести в автономный режим, как показано на
рис. 31.
43
Рис. 31. Перевод базы данных postavka в автономный режим
Рис. 32. Сообщение (слева) и состояние
обозревателя объектов (справа)
Появится окно сообщения об успешном выполнении этой операции (рис. 32, слева). После закрытия этого окна в обозревателе
объектов база данных будет помечена (рис. 32, справа);
• отсоединить базу данных от экземпляра сервера, выполнив на
имени базы данных из контекстного меню команду ⇒ Задачи/Отсоединить, как показано на рис. 33 (слева); база данных будет удалена из обозревателя объектов;
44
Рис. 33. Отсоединение и присоединение базы данных
• перенести файл данных postavka.mdf и файл журнала транзакций postavka.ldf из папки, в которой они были расположены,
на внешний носитель;
• на другом компьютере запустить программу SQL Server
Management Studio, выполнить соединение с сервером и скопировать файлы postavka.mdf, postavka.ldf с внешнего носителя
в папку по умолчанию (C:\Program Files\Microsoft SQL Server\
MSSQL … MSSQLSERVER\MSSQL\DATA) на этом компьютере;
• выполнить присоединение базы данных командой ⇒ Присоединить из контекстного меню на папке «Базы данных», как показано на рис. 33 (справа).
2.4.2. Использование резервного копирования
Этот способ доступен любому пользователю, не обладающего
правами администратора. Студентам рекомендуется использовать
именно этот способ.
Способ состоит из следующих шагов [2]:
• создание резервной копии базы данных, переносимой на другой компьютер;
45
• сохранение резервной копии на внешнем носителе;
• восстановление резервной копии базы данных на другом компьютере.
Рассмотрим эти шаги подробнее на примере.
Пример 3. Пусть требуется перенести базу данных postavka
c одного компьютера на другой, используя метод резервного копирования.
Создание резервной копии
Перед созданием резервной копии все объекты базы данных
на сервере должны быть закрыты. Для создания резервной копии
нужно выполнить следующую последовательность шагов:
Шаг 1. Из контекстного меню, открытого на папке с именем базы
данных postavka в обозревателе объектов сервера, выполнить команду ⇒ Задачи/Создать резервную копию… (рис. 34);
Шаг 2. В открывшемся окне «Резервное копирование базы данных» обязательно установить выделенные в окне (рис. 35) общие
параметры:
• выбрать из списка базу данных, предназначенную для копирования,
• указать, когда истекает срок действия копии:
– переключатель «Через» указывает количество дней хранения
копии,
– переключатель «По дате» указывает дату окончания хранения
резервной копии;
• проверить соответствие остальных параметров, которые часто
устанавливаются по умолчанию, указанным на рис. 35 значениям.
Если – не соответствуют, то изменить их.
По умолчанию файл резервной копии сохраняется в папке C:\
Program Files\Microsoft SQL Server\MSSQL … MSSQLSERVER\
MSSQL\Backup, как показано на рис. 35. Это не очень удобно, и
папка не всегда доступна. Поэтому необходимо сохранять резервную копию в удобном для вас месте. Пусть это будет папка C:\temp.
Для того чтобы указать эту папку, нужно сначала по кнопке «Удалить» удалить местоположение по умолчанию и нажать кнопку
«Добавить», чтобы добавить новое местоположение резервной копии в папке C:\temp. Далее следует выполнить шаг 3.
Шаг 3. В открывшемся окне «Выбор места расположения резервной копии» необходимо указать путь будущего файла кнопкой
с тремя точками (кнопка выделена на рис. 36, слева). В окне «Расположение файлов базы данных» (рис. 36, справа) задать выбранный путь и имя файла с расширением.BAK. и нажать кнопку «ОК»,
46
Рис. 34. Создание резервной копии
Рис. 35. Задание общих параметров резервного копирования
47
возвращаясь в окно «Выбор местоположения резервной копии»,
в котором нажать кнопку «ОК».
Шаг 4. Возвращаемся в окно «Резервное копирование базы данных» (рис. 37, слева). Прежде, чем выполнить операцию создания
копии нужно сделать настройки способа копирования во вкладке
«Параметры». Открыть ее можно в области «Выбор страницы»
окна «Резервное копирование базы данных» (рис. 37, слева). Откроется окно для настройки способа копирования (рис. 37, справа),
в котором нужно включить переключатель «Перезаписать все существующие резервные наборы данных».
Шаг 5. Завершаем установку нажатием на кнопку «ОК». В результате появится окно с сообщением об успешном (или не успешном) завершении резервного копирования.
После создания резервной копии базу данных при необходимости можно отсоединить командой ⇒ Задачи/Отсоединить. Команда будет доступна из контекстного меню, открытого на имени базы
данных postavka в окне обозревателя объектов сервера.
Чтобы перенести полученную резервную копию базы данных
на другой компьютер, ее нужно скопировать из папки C:\temp на
внешний носитель или на сетевой диск.
Восстановление резервной копии базы данных на другом компьютере
Для восстановления резервной копии базы данных нужно, чтобы на компьютере был установлен MS SQL Server 2012.
Скопируем резервную копию базы данных в любую папку на
компьютере. Пусть это будет также папка C:\temp.
Запускаем MS SQL Server Management Studio и осуществляем
соединение с сервером. Для восстановления базы данных нужно
выполнить следующую последовательность действий:
• В обозревателе объектов из контекстного меню на папке «Базы
данных» выполнить команду ⇒ Восстановить базу данных…
(рис. 38, слева);
• В открывшемся окне «Восстановление базы данных» включить переключатель «С устройства» и выбрать устройство кнопкой
с тремя точкой (выделена на рис. 38, справа).
• В открывшемся окне «Указание резервной копии» (рис. 39, слева)
с помощью кнопки «Добавить» открыть окно «Локальный файл резервной копии» (рис. 39, справа), в котором найти в папке C:\temp необходимый файл резервной копии (postavka.bak) и по кнопке «ОК»
установить нужное местоположение резервной копии (рис. 40, слева).
Вновь откроется окно «Восстановление базы данных» (рис. 40, справа).
48
49
Рис. 36. Выбор места расположения и имени резервной копии
50
Рис. 37. Установленные параметры (слева) и настройка способа копирования (справа)
51
Рис. 38. Восстановление базы данных
52
Рис. 39. Выбор местоположения резервной копии
53
Рис. 40. Местоположение резервной копии (слева) и окно восстановления базы данных (справа)
Рис. 41. Страница установки
дополнительных параметров восстановления
В окне «Восстановление базы данных» (рис. 40, справа) включить флажок (выделен на рисунке). В разделе «Назначение для
восстановления» выбрать базу данных postavka как показано на
рис. 40 (справа). Открыть вкладку «Параметры» для установки дополнительных параметров. В окне вкладки «Параметры» (рис. 41)
в разделе «Параметры восстановления» включить флажок «Перезаписать существующую базу данных». Это необходимо сделать,
если база данных часто обновляется.
• При нажатии на кнопку «ОК» появится сообщение об успешном восстановлении базы данных. После обновления обозревателя
объектов сервера в списке баз данных появится восстановленная
база postavka.
54
2.5. Создание базы данных операторами Transact-SQL
Основы TRANSACT-SQL (T-SQL) будут даны в разделе 3. Здесь
укажем только возможности использования комментариев на языке T-SQL, чтобы облегчить понимание последующих примеров.
T – SQL допускает использование комментариев следующих видов:
– однострочного: два дефиса (--) делают после них текст комментарием;
– многострочного: /* текст комментария */
Для создания пользовательской базы данных используется оператор Create Database со следующим синтаксисом [2] (некоторые
опции опущены):
Create Database <логическое имя базы данных>
[Containment={None|Partial}]
[<предложение On> [<предложение Log On >]]
[Collate <способ_сортировки>]
[With <опция> [,<опция>]…]
<предложение On>::=
[On {[Primary]|
<спецификация файла> [,<спецификация файла>]…
[,<файловая группа> [,<файловая группа>]…]
<предложение Log On>::=
Log On < спецификация файла> [,<спецификация файла>]…
<опция>::=
{FileStream(<опция файлового потока> [,<опция файлового
потока>]…)
| Default_FullText_Language =
{<код языка> | <название языка> |<псевдоним языка>}
| Default_Language =
{<код языка> | <название языка> |<псевдоним языка>}
| Nested_Triggers = {Off | On}
| Db_Chaining {Off | On}
| Trustworthy {Off | On}}
<опция файлового потока>::=
{Non_Transacted_Access = {Off | Read_Only | Full}
| Directory_Name = ′<имя каталога>′}.
Здесь опция Non_Transacted_Access определяет возможность
доступа к данным файлового потока вне контекста транзакций.
Значениями опции являются:
Off – доступ к данным вне транзакции недопустим;
55
Read_Only – к данным файлового потока возможен доступ вне
транзакций только для чтения;
Full – допустимы все операции к данным файлового потока вне
транзакций.
Опция Directory_Name задает имя каталога, который используется для файловых таблиц (FileTable), сведения о которых
можно найти в работе [2].
Замечания по синтаксису
Операторы SQL можно завершать точкой с запятой или разделять операторы словом GO при выполнении нескольких операторов
в одном сценарии (пакете).
Логическое имя базы данных является единственным обязательным параметром в данном операторе. Оно идентифицирует создаваемую базу данных. Имя должно соответствовать правилам задания
идентификаторов и не должно содержать более 128 символов. Оно
должно быть уникальным среди имен всех баз данных текущего
экземпляра сервера. Если имя содержит хотя бы один пробел, то
оно должно заключаться в квадратные скобки. Это же требование
сохраняется для имен любых объектов базы данных (таблиц и их
столбцов (полей), представлений, хранимых процедур и пр.).
Предложение CONTAINMENT
[Containment={None|Partial}]
Это предложение определяет степень независимости базы данных от экземпляра сервера, в котором она создается. Если указано
None (значение по умолчанию), то создается обычная (неавтономная) база данных. Если указано Partial, то создается частично
автономная база данных. Такую базу проще перенести в другой экземпляр сервера или на другой компьютер [2].
Предложение ON
В операторе создания базы данных может задаваться первичный
(основной) файл данных в предложении ON. Файл, который описан
первым в этом предложении, а также перечисленные за ним файлы, если они указаны, помещаются в файловую группу Primary.
О вторичных файловых группах можно найти информацию в примере 5 данного пособия. Вторичные файловые группы описываются всегда после первичной файловой группы.
Предложение LOG ON
Это предложение описывает файл (файлы) журнала транзакций.
Предложение LOG ON можно опустить. В этом случае файлу журнала транзакций присваиваются значения по умолчанию. Задание
56
нескольких файлов журналов транзакций имеет смысл только тогда, когда для одного файла не хватает места на внешнем носителе.
Предложение COLLATE
Это предложение позволяет задать для создаваемой базы данных порядок сортировки, отличный от того, который был установлен при инсталляции системы для экземпляра сервера. Если это
предложение не указано, то порядок сортировки устанавливается
по умолчанию.
Предложение WITH
Это предложение позволяет описать некоторые дополнительные
характеристики создаваемой базы данных, которые подробно описаны в работе [2].
Спецификация файла
Синтаксис спецификации файла одинаков как для файлов данных, так и для файлов журналов транзакций, и имеет вид:
<спецификация файла>::=
(Name = <логическое имя файла>,
FileName = {′<путь к файлу>′|′<путь к файловому потоку>′}
[,Size = <размер файла (число)> [KB|MB|GB|TB]]
[,MaxSize = {<максимальный размер> [KB|MB|GB|TB]|
Unlimited}]
[,FileGrowth = <инкремент_увеличения_файла>
[KB|MB|GB |TB]|%]]),
где
FileName определяет полное имя файла на жестком диске.
Size задает исходный размер файла. Для размера файла можно
указать единицы измерения, например, 700 KB (килобайты) или
10 MB (мегабайты). Если предложение Size не задано, то начальному размеру файла присваивается значение по умолчанию, которое
определено в системной базе данных model (например, 3 Мбайта
для файла данных и 1 Мбайт для файла журнала транзакций [2]).
MaxSize задает максимальный размер файла. Если его не указать, то размер будет автоматически увеличиваться, пока не останется свободного места на жестком диске (опция автоматического
увеличения базы данных должна быть включена).
FileGrowth задает шаг увеличения размера файла (по умолчанию принимается 1 МВ);
Unlimited указывает на тот факт, что размер файла не ограничивается.
Предложение Default_FullText_Language (допустимо только для автономной базы) задает язык базы данных по умолчанию
57
для полнотекстового поиска в индексированных полях (столбцах)
таблиц. Подробнее о методах поиска в индексированных данных
будет сказано в разделе 3.3.1.
Предложение Default_Language (допустимо только для автономной базы) задает язык базы данных по умолчанию для вновь
создаваемых имен пользователей. Может задаваться в виде кода
языка, его названия или псевдонима.
Предложение Nested_Triggers (допустимо только для автономной базы) задает возможность использования вложенных триггеров AFTER. Если указано Off, вложенные триггеры недопустимы.
При задании On может существовать до 32 уровней триггеров. То
есть триггер может инициировать другой триггер, который, в свою
очередь, может инициировать триггер следующего уровня и так далее до 32 уровней. Примеры триггеров будут приведены в разделе 7.
Предложение Db_Chaining определяет, может ли создаваемая
база данных использоваться в цепочках связей между несколькими базами данных. Ключевое слово Off (по умолчанию) запрещает,
а On – разрешает такое использование.
Предложение Trustworthy запрещает (по умолчанию) или разрешает программным компонентам создаваемой базы (хранимым
процедурам, представлениям, пользовательским функциям) обращаться к ресурсам вне базы данных.
Синтаксис оператора создания базы данных кажется достаточно громоздким, в котором не все ясно начинающим пользователям.
Однако по мере накопления знаний и практических навыков многое станет понятным.
Пример 4.
Создать базу данных postavka с основным файлом данных по
имени postavkaData размером 20 МВ с и шагом увеличения 10 МВ
до максимального размера 100 МВ и с соответствующим файлом
журнала транзакций:
Для решения поставленной задачи нужно:
– запустить программу–менеджер SQL Server Management
Studio;
– выполнить соединение с сервером при условии проверки подлинности Windows (доверительное соединение);
– открыть окно запросов (
); по умолчанию запрос
будет создаваться к системной базе данных master;
– набрать в окне запросов оператор:
Create Database postavka
/* Задание первичной файловой группы */
58
On Primary
(Name = postavkaData, -- логическое имя файла данных
/* физические характеристики файла данных */
FileName = ′D:\………..\ postavkaData.mdf′,
Size = 20 MB,
MaxSize = 100 MB,
FileGrowth = 10 MB)
/* Задание файла журнала транзакций */
Log On
(Name = postavkaLog, -- логическое имя файла транзакций
/* физические характеристики файла транзакций */
FileName = ′D:\………..\ postavkaLog.ldf′,
Size = 5 MB,
MaxSize = 15 MB,
FileGrowth = 1 MB);
– выполнить запрос, нажав на кнопку
.
Вообще, для создания базы данных достаточно указать имя базы
и всего один параметр Name (логическое имя файла), хотя так делать не рекомендуется. Рекомендуется использовать следующий
минимальный набор параметров: Name, FileName, Size, а также
FileName и Size для файла журнала транзакций.
Если при создании базы данных физическое имя файла опцией FileName не указано, то по умолчанию файлы данных и файлы
журнала транзакций будут расположены в папку C:\Program Files\
Microsoft SQL Server\MSSQL … MSSQLSERVER\MSSQL\DATA
В учебных базах данных файлы данных и файлы журнала транзакций рекомендуется размещать в папке своей учебной группы.
В рассмотренном примере все файлы с данными объявлены основной (первичной Primary) группой файлов, которые имеют расширение .mdf. Однако базу данных можно создавать с использованием вторичной файловой группы. При этом часть файлов будет
входить в основную группу Primary с расширением.mdf, а другая
часть – с расширением .ndf – во вторичную группу, как показано
в примере 5.
Пример 5.
Создание базы данных с использованием вторичной файловой
группы:
Create Database postavka
/* Задание первичной файловой группы данных */
On Primary
59
-- логическое имя первого файла данных
(Name = postavkaData1,
/* физические характеристики первого файла данных */
FileName = ′D:\………..\ postavkaData1.mdf′,
Size = 5 MB,
MaxSize = 20 MB,
FileGrowth = 1 MB),
/* Задание вторичной файловой группы данных */
-- логическое имя второго файла данных
(Name = postavkaData2,
/* физические характеристики второго файла данных */
FileName = ′D:\………..\ postavkaData2.ndf′,
Size = 5 MB,
MaxSize = 20 MB,
FileGrowth = 1 MB)
/* Задание файлов журналов транзакций */
Log On
-- логическое имя первого файла журнала транзакций
(Name = postavkaLog1,
/* физические характеристики первого файла журнала
транзакций */
FileName = ′D:\………..\ postavkaLog1.ldf′,
Size = 5 MB,
MaxSize = 15 MB,
FileGrowth = 1 MB),
-- логическое имя второго файла журнала транзакций
(Name = postavkaLog2,
-- физические характеристики второго файла журнала
-- транзакций
FileName = ′D:\………..\ postavkaLog2.ldf′,
Size = 5 MB,
MaxSize = 15 MB,
FileGrowth = 1 MB);
Для учебных целей рекомендуется ограничиться основной (первичной) группой файлов.
Удаление базы данных осуществляется оператором:
Drop Database <имя базы данных> [,<имя базы данных>]….
Этот оператор позволяет удалить одну или более указанных
пользовательских баз данных. Нельзя удалить системную базу
данных. Нельзя также удалить базу данных, которая используется в настоящий момент на том же компьютере или в сети. При
60
удалении базы данных она удаляется из списка баз данных экземпляра сервера. При этом автоматически удаляется вся информация, связанная с ней (файлы данных, файлы журналов транзакций и пр.).
Изменение имени и некоторых характеристик базы данных
можно оператором
Alter Database, синтаксис которого такой:
Alter Database {<имя базы данных>|Current}
{Modify Name = <новое имя базы данных>
| Collate <порядок сортировки>
| <характеристики файлов>
| <характеристики файловых групп>
| <характеристики базы данных>}.
Изменения будут выполнены либо для базы данных, указанной по имени, либо для текущей базы данных, если указано слово
Current.
За одно выполнение оператора можно выполнить только одно
действие: либо переименовать базу данных, либо изменить порядок
сортировки (Collate), либо изменить характеристики файлов или
файловых групп или характеристики базы данных. Для выполнения нескольких действий нужно несколько раз выполнить этот
оператор [2].
Характеристики файлов в синтаксисе оператора Alter Database задаются так:
<характеристики файлов>::=
{Add File <спецификация файла> [,<спецификация файла>]
[To FileGroup <имя файловой группы>]
| Add Log File <спецификация файла> [,<спецификация файла>]
| Remove File <логическое имя файла>
| Modify File <изменяемый файл>}.
Предложение Add File позволяет добавить файлы данных, а
опция Add Log File позволяет добавить файлы журналов транзакций. Синтаксис спецификаций файлов такой же, как и в ранее описанном операторе Create Database.
Предложение Remove File позволяет удалить из базы файл данных или файл журнала транзакций, указав его логическое имя.
Нельзя удалить единственный в базе файл данных или единственный файл журнала транзакций.
Нельзя удалить файл, содержащий данные. Надо сначала удалить из файла данные, а затем и сам файл.
61
Для переименования или изменения характеристик существующего файла данных или файла журнала транзакций используется
предложение Modify File. Синтаксис задания изменяемого файла
очень похож на обычную спецификацию файла:
<изменяемый файл>::=
(Name = <логическое имя файла>
[, NewName = <новое логическое имя файла >]
[,FileName ={′<путь к файлу>′|′<путь к файловому потоку>′}]
[,Size = <размер файла (число)> [KB|MB|GB|TB]]
[,MaxSize = {<максимальный размер>
[KB|MB|GB|TB]|Unlimited}]
[,FileGrowth = <инкремент_увеличения_файла>
[KB|MB|GB|TB]|%]]
[,OffLine]).
62
3. ОСНОВЫ TRANSACT-SQL
Структурированный язык запросов Transact-SQL или сокращенно T-SQL (Structured Query Language) является процедурным расширением стандарта ANSI SQL для реляционных баз данных. Язык
обладает большими возможностями, подробное описание которых
выходит за рамки данного учебного пособия. Поэтому в этом разделе
рассмотрим основные из них, которые необходимы для работы с достаточно простыми учебными базами данных. Более полное описание языка запросов Transact-SQL можно найти в работах [2], [6], [15].
3.1. Основные объекты SQL
Основными объектами языка SQL являются:
• лексемы,
• выражения,
• операторы.
Лексемы – неделимые элементы языка. К ним относятся:
• разделители (или ограничители [6]),
• идентификаторы (имена),
• зарезервированные ключевые слова,
• литералы (или константы).
Разделители – это простые или составные символы, имеющие
специальное назначение (+ - * / & ( ); , . ″ ′ [ ] < > ! = @ # _ <= >= и пр.).
Идентификаторы на языке T-SQL – это имена переменных и
объектов баз данных. Идентификатор состоит из строки длиной до
128 символов, которая может содержать буквы, цифры и символы
_, @, # и &. Первым символом идентификатора должна быть буква
или символы _, @ или #. Символ # в начале имени означает временный объект, а символ @ означает переменную. Еще могут быть
идентификаторы с ограничителями, которые могут содержать или
начинаться с любого символа, кроме ограничителя. Чаще всего
в качестве ограничителя используются квадратные скобки. Так,
идентификаторы, содержащие хотя бы один пробел, заключаются
в квадратные скобки, например, [номер поставщика].
Зарезервированные ключевые слова – это набор зарезервированных имен, которые требуется писать и применять в определенном
формате, например, SELECT, INSERT и пр. Написание этих слов не
зависит от регистра. Поэтому можно писать select или Select
или sElect или SELECT и так далее.
63
Литералы – это буквенно-цифровая (строковая или иначе символьная), шестнадцатеричная или числовая константа. Строковая
константа должна заключаться в одинарные или двойные прямые
кавычки, например, ′Москва′ или ″футбол″. Обычно по умолчанию используются одинарные кавычки. Применение двойных кавычек определяется с помощью параметра Quoted_Identifier
оператора Set. Даты на языке ANSI SQL, а, следовательно, и на
языке T-SQL записываются как строковые константы, например,
′12.12.2015′. Примеры недопустимых строковых констант:
′мыло″ – строка должна быть заключена в кавычки одинакового
вида,
′AB′C′ – нечетное число одинарных кавычек.
Примеры числовых констант: 120, -1999, -0.357E5, 22.3E-3.
Пример шестнадцатеричной константы: 0x555C0D.
К литералам специального назначения относятся:
′′ – пустая символьная строка;
Null – неопределенное или не введенное значение, например,
в поле таблицы базы данных;
логические константы: True (истина), False (ложь).
Выражения являются комбинацией более простых элементов
(лексем).
Операторы (или инструкции) конструируются из выражений и
лексем. Оператор начинается всегда с глагола (Select – выбрать,
Insert – добавить, и так далее).
Отдельно выделим так называемые скалярные операторы (обычно называемые операциями), которые используются для работы со
скалярными значениями [6].
Transact-SQL поддерживает числовые и логические скалярные
операторы, а также конкатенацию (слияние строк).
Числовые (арифметические) скалярные операторы бывают унарными (знаки чисел + и -) и бинарными: сложение (+), вычитание (-), умножение (*), деление (/), операция получения остатка от деления (%).
Логические операторы: отрицание (~ или Not), конъюнкция (&
или And), дизъюнкция (| или Or), исключающая дизъюнкция (^ или
Xor – аналог операции по модулю 2 над битовыми строками).
Операторы сравнения (операции отношения): равно (=), не равно (!= или < >), меньше (<), больше (>), меньше или равно, не больше (<= или !>), больше или равно, не меньше (>= или !<).
Операции со значениями Null выполняются по правилу: если
один или оба операнда Null, то результат любой операции над этими операндами будет также Null.
64
Операция конкатенации строк задается символом плюс (+). Например, результатом конкатенации двух строк ′Иван ′ + ′Петров′
будет строка ′Иван Петров′.
Можно еще указать несколько специальных скалярных операторов, примеры которых будут приведены в разделе 3.3.2 при рассмотрении условий отбора записей в запросах на выборку данных.
3.2. Типы данных
Все значения в столбце таблицы должны быть одного типа данных (за исключением значений типа SQL_Variant). Используемые
в T-SQL типы данных можно разбить на следующие категории [6]:
• числовые,
• символьные,
• типы даты и/или времени,
• денежные типы,
• прочие типы, основными из которых являются:
- двоичные типы,
- типы данных больших объектов,
- тип данных SQL_Variant,
- тип данных Cursor,
- тип данных XML,
- типы данных, определяемые пользователем.
Подробнее типы данных представлены в приложении 1.
3.3. Запросы
В T-SQL можно выделить следующие группы операторов:
• операторы языка описания (определения) данных (ЯОД или
DDL – Data Definition Language),
• операторы языка манипулирования данными (ЯМД или DML –
Data Manipulation Language) или обработки данных,
• операторы управления транзакциями,
• операторы управления потоком,
• операторы управления сеансом и системой.
Пробелы в операторах SQL не имеют значения. Это значит, что
одно слово от другого нужно отделять хотя бы одним пробелом.
Можно как угодно переносить оператор на следующую строку.
Однако строковую константу разрывать переносом на следующую
65
строку нельзя. Разрывая строковую константу нужно разорванные
части ее «склеивать» оператором конкатенации.
Примеры запросов, приведенные в этом разделе, будут ориентированы на базу данных postavka, схема данных которой была дана
в разделе 1.4 на рис. 6.
В T-SQL можно выполнять следующие типы запросов:
• для определения (описания) данных:
– создание/ модификация / удаление базы данных (Create/
Alter/ Drop Database),
– создание структуры таблицы (Create Table),
– модификация структуры таблицы и удаление таблицы из
базы данных (Alter/ Drop Table),
– создание/удаление индекса поля таблицы (Create/ Drop
Index);
• для манипулирования данными (обработки данных):
– выборка (отбор) данных (Select … Where),
– создание таблицы (Select … Into),
– изменение данных:
 добавление записей (Insert … Into),
 обновление полей записей (Update),
 удаление записей (Delete);
– вложенные запросы (Select, вложенный в операторы
Select, Select … Into, Delete, Update или в другой вложенный запрос;
– объединение запросов (Union).
3.3.1. Определение данных
В этом разделе рассматриваются операторы (инструкции), связанные с языком определения (описания) данных. Операторы разбиты на три группы. В первую группу входят операторы для создания объектов, вторая группа содержит операторы для модификации (изменения) структуры объектов, а третья содержит операторы для удаления объектов.
Такими объектами являются базы данных, таблицы и индексы.
Вопросы создания, модификации и удаления базы данных были
рассмотрены в разделе 2.5.
Создание структуры таблицы
Таблица – это объект базы данных, являющийся источником
данных. База данных может содержать до двух квадриллионов таблиц, а каждая таблица до 1024 столбцов [2]. Максимальная шири66
на столбца составляет 8192 байта, исключение составляют текстовые и графические типы данных, размер которых может доходить
до двух ГВ (гигабайт).
Как было сказано ранее, для каждого столбца (поля) и для самой
таблицы должно быть задано имя (идентификатор) в рамках определенных ограничений, указанных в разделе 3.1, а именно идентификатор содержит буквы, специальные символы и цифры. Первый
символ – буква, символы @, # или символ подчеркивания (_). Символы @, # имеют специальное назначение:
– с символа @ начинаются имена локальных объектов;
– с символа # – имена временных объектов, используемых одним пользователем базы данных, которые автоматически уничтожаются при завершении сеанса этого пользователя;
– с символов ## – имена глобальных временных объектов, используемых всеми пользователями базы данных, которые автоматически уничтожаются при завершении сеансов всех пользователей.
Для создания структуры таблицы используется оператор Create
Table со следующей базовой синтаксической конструкцией:
Create Table ИмяТаблицы(ИмяПоля1 Тип[(Размер)]
[Not Null | Null] [Default значение]
[Identity [(начало, приращение)]],
[Constraint имя_ограничения
PrimaryKey (имя_столбца,...)][,ИмяПоля2 …],
.. . . . . . . . . . . . . . . . . . . . .
[Constraint имя_ограничения
Foreign Key (ИмяСтолбца в дочерней таблице)
References ИмяРодительскойТаблицы (Первичныйй ключ
в родительской таблице)]
/* каскадное удаление связанных записей */
[On Delete Cascade]
/* каскадное обновление связанных полей */
[On Update Cascade]).
Пример 6. Создание структуры таблиц для базы данных
postavka (рис. 6).
Рекомендуется сначала создавать родительские таблицы, затем – таблицы, которые одновременно являются родительскими и
дочерними, и в последнюю очередь – дочерние таблицы.
/* Создание родительской таблицы Firm */
Create Table Firm
(NFM Int Identity (1,1) Not Null, -- NFM–счетчик
FIM nVarchar(50) Null,
67
-- задание первичного ключа
Constraint Id_NFM Primary Key(NFM));
В этой таблице предложением Constraint объявлен столбец
(поле) NFM первичным ключом. По первичному ключу автоматически создается кластерный индекс, Id_NFM – имя индекса. Подробнее
об индексах будет сказано ниже. Столбец NFM объявлен счетчиком
Identity (1,1) Not Null,который автоматически задает значения
поля от 1 с шагом 1, причем не допускает значений Null.
/* Создание родительской таблицы Det */
Create Table Det
(DN Int Identity (1,1) Not Null,
DIM nVarchar(50)Null,
CENA Money,
Constraint Id_DN Primary Key (DN));
/* Создание таблицы Post и связей с таблицей Firm */
Create Table Post
(PN Int Identity (1,1) Not Null,
PIM Char(15) Null,
ST Int Null,
GOR nVarchar(50)Null Default ′СПб′, -- по умолчанию СПб
TEL nVarchar(15)Null,
COMMENT Varchar(255),
NFM Int,
Constraint Id_PN Primary Key(PN),
Constraint FK_Firm
Foreign Key (NFM)
References Firm (NFM)
/* Обеспечение каскадного удаления связанных записей*/
On Delete Cascade
/* Обеспечение каскадного обновления связанных полей*/
On Update Cascade);
Здесь предложение Foreign Key определяет внешний ключ
(NFM) таблицы Post, которая является дочерней по отношению
к таблице Firm, а предложение References определяет ссылку на
первичный ключ соответствующей родительской таблицы (Firm).
/* Создание дочерней таблицы PD и связей между таблицами */
Create Table PD
(PN int Not Null,
DN int Not Null,
KOL int Null,
DATA Datetime Not Null,
68
Constraint Id_PN_DN_DATA Primary Key (PN,DN,DATA),
Constraint FK_Post Foreign Key (PN)
References Post(PN)
/* Обеспечение каскадного удаления связанных записей
по полю PN */
On Delete Cascade
/* Обеспечение каскадного обновления связанных полей PN */
On Update Cascade,
Constraint FK_Det Foreign Key (DN)
References Det(DN)
/* Обеспечение каскадного удаления связанных записей
по полю DN */
On Delete Cascade
/* Обеспечение каскадного обновления связанных полей DN */
On Update Cascade);
При реализации запросов в среде SQL Server можно несколько запросов объединять в сценарии, а затем выполнять созданный сценарий.
Сценарий – это набор из одного или более запросов, которые хранятся вместе и выполняются в рамках сценария последовательно
один за другим. Запросы, объединенные в сценарий, разделяются
ключевым словом Go, который не является оператором T-SQL. Так,
создав базу данных, можно запросы по созданию таблиц в этой базе
выполнить одним сценарием следующего вида:
Use postavka –- указание имени используемой базы данных
Create Table Firm
...................
Go
Create Table Det
...................
Go
Create Table Post
...................
Go
Create Table PD
...................
Go
Имена ограничений Constraint в операторе создания одной и
той же таблицы должны быть различными.
Указать имя используемой базы данных можно оператором Use,
как это сделано в выше приведенном сценарии, или выделить ис69
пользуемую базу данных в окне обозревателя объектов программы
SQL Server Management Studio, иначе по умолчанию будет выбрана
системная база данных master.
Модификация структуры и удаление таблицы из базы данных
Модификация структуры таблицы может состоять в изменении
имени таблицы, добавлении, удалении и переименовании столбца
таблицы, в задании первичного ключа и изменении ограничений
целостности данных. Осуществляется оператором Alter Table и
с помощью системной хранимой процедуры sp_rename.
Пример 7. Добавить в таблицу Det полe комментария, разрешив
не вводить данные в это поле, можно оператором
Alter Table Det
Add Comment_Det Varchar(20) Null;
Компонент Database Engine заполняет новый столбец значениями Null или с помощью счетчика Identity или указанными
значениями по умолчанию [6].
Поэтому новый столбец должен или поддерживать свойство содержать значения Null (но не Not Null) или для него должно быть
указано значение по умолчанию. Кроме того, если таблица уже
содержит счетчик, то в эту таблицу добавить еще один счетчик не
удается и наоборот, если таблица не содержит счетчик, то счетчик
можно в нее добавить. При этом если таблица уже заполнена данными, то добавленный столбец-счетчик также автоматически будет заполнен значениями.
Примеры 8. Добавить в таблицу PD, не содержащей счетчика,
новый столбец-счетчик ID можно оператором
Alter Table PD
Add ID int Identity (1,1) not Null;
Изменить тип данных столбца Comment_Det на nVarchar(50)
можно оператором
Alter Table Det
Alter column Comment_Det nVarchar(20) Null;
Переименовать столбец Comment_Det на Comment можно с помощью системной хранимой процедуры sp_rename.
SQL Server содержит большое количество системных хранимых процедур, которые не требуют описания и с помощью которых
можно решать разнообразные задачи. Имена системных хранимых
процедур всегда начинаются с префикса sp_. Вызов как системной
так и пользовательской хранимой процедуры, осуществляется оператором Exec с указанием параметров.
70
Поставленную задачу можно решить, вызвав указанную процедуру оператором
Exec sp_rename ′Det.Comment_Det′, Comment;
В процедуре указан список из двух параметров через запятую:
первый параметр – строка символов ′Det.Comment_Det′ указывает на имя таблицы Det и старое имя столбца Comment_Det. Точка
является разделителем. Второй параметр задает новое имя столбца
Comment.
Удалить столбец Comment из таблицы Det можно оператором
Alter Table Det
Drop Column Comment;
Переименовать таблицу Firm в Firma можно с помощью системной хранимой процедуры sp_rename оператором
Exec sp_rename Firm, Firma;
Примеры 9. Предположим, что в базе данных postavka создана
таблица PR, в которой есть поле PR_DN, и пусть эта таблица будет
дочерней по отношению к таблице Det (рис. 42), первичным ключом в которой было объявлено поле DN. Связать таблицы PR и Det
по полям PR_DN и DN соответственно можно оператором
Alter Table PR Add Constraint FK_PR_Det
Foreign Key (PR_DN)References Det(DN);
Устанавливать связь между таблицами можно только путем модификации дочерней, а не родительской таблицы.
Объявить первичным ключом поле ID в таблице PR можно оператором
Alter Table PR
Add Constraint primary_PR Primary Key(ID);
Удалить ограничение целостности данных (связь таблицы PR
с таблицей Det) можно оператором
Alter Table PR
Drop Constraint FK_PR_Det;
Удалить таблицу PR из базы данных можно оператором
Drop Table PR;
Рис. 42. Добавленная таблица PR
71
Создание/Удаление индекса поля таблицы
Индексы позволяют ускорить поиск в таблицах базы данных за
счет использования упорядоченных структур. Индексы представляют собой наборы уникальных для данной таблицы значений и
соответствующий им список указателей (ссылок) на страницы данных, где эти значения находятся в области данных физически.
Существуют следующие типы индексных структур данных:
• индексно-последовательные структуры, в которых и данные, и
индекс упорядочены;
• индексно-произвольные структуры, в которых данные не упорядочены, а индекс может быть, как упорядочен, так и не упорядочен. Упорядоченный индекс предпочтительнее, так как он имеет
более компактную структуру и занимает меньший объем памяти.
Кроме того процедура поиска данных по упорядоченному индексу
осуществляется быстрее.
Структура индексов очень похожа на древовидную структуру,
что и натолкнуло разработчиков на использовании такой структуры при организации индексов в базах данных. При этом очень
удобной оказалась структура В+-дерева. Рассмотрим ее подробнее.
В+-дерево – это дерево с корнем, состоящее из вершин (узлов) и
удовлетворяющее следующим условиям:
• Все пути от корневой вершины к концевым (листьям) имеют
одинаковую длину, которая определяется количеством вершин,
которые надо пройти от корня до листа. Таким образом, такое дерево всегда сбалансировано [2], [6].
• Каждая корневая и промежуточная вершины содержат одно или
несколько упорядоченных значений (в зависимости от порядка дерева), и каждое значение имеет две ссылки. Левая ссылка для чисел
указывает на значение меньше, а правая – на значение больше или
равно. Именно из-за «равно» все значения, содержащиеся в дереве
индексов «спускаются» в листовые вершины. Для символьных значений используется принцип лексикографического упорядочения.
Может быть использован другой порядок обхода вершин. На рис. 43
показана структура корневой и промежуточной вершин В+-дерева.
.
Значение
<
Ссылка на левую вершину
.
>=
Ссылка на правую вершину
Рис. 43. Структура корневой
и промежуточной вершин В+-дерева
72
В вершинах-листьях ссылки указывают чаще всего на область
данных.
Пример 10. Для того, чтобы лучше понять структуру индексов,
сначала рассмотрим пример построения В+-дерева для данных поля
FIM из таблицы Firm (рис. 44) базы данных postavka (рис. 6), а уже
потом рассмотрим типы индексов, которые использует SQL Server, и
построим серверный индекс для данных поля FIM этой же таблицы.
Из рис. 44 видно, что данные поля FIM не упорядочены. Следовательно, построенная нами структура будет индексно-произвольной с многоуровневым упорядоченным индексом в силу его преимуществ перед не упорядоченным индексом. Будем строить В+дерево последовательно, добавляя в него по одному значению поля
FIM, начиная с первого.
Для простоты в каждой вершине разместим не более двух значений (дерево второго порядка [5]), и эти значения будут упорядочены, так как строим упорядоченный индекс. На рис. 45 показаны
последовательные этапы построения В+-дерева.
Построение В+-дерева начинается с листьев. Если при добавлении очередного значения ему не находится места в листьях, то дерево видоизменяется путем добавления в него новых вершин на том же
уровне или, если это невозможно, то путем «роста» дерева вверх. После включения последнего значения «факел» В+-дерево построено.
Поиск по В+-дереву осуществляется следующим образом.
Пусть требуется найти все данные, связанные со значением индексируемого поля «круг». Поиск начинается с корневой вершины
(с индекса 3-го уровня). Это значение отсутствует в корневой вершине, но оно «меньше» чем «рога и копыта». Следовательно, поиск
значения «круг» должен продолжаться по левой ссылке.
Спускаемся по левой ссылке к индексу второго уровня, на котором находим значение «круг». Однако на этом поиск не заканчивается, так как ссылка на данные существует только с индекса 1-го
уровня. Но можно уже сказать, что поиск удачный.
Рис. 44. Содержимое таблицы Firm
73
Поскольку правая ссылка указывает на значение «больше» или
«равно», то спускаемся к индексу 1-го уровня по правой ссылке,
находим значение «круг» и ссылку на данные, содержащие это значение (рис. 45).
В + - дерево
Значение
тур
тур
румба
румба
рога и
копыта
тур
тур
<
>=
Рога и копыта
круг
тур
румба
румба
круг
Рога и копыта
рога и копыта
квадрат
тур
Рога и копыта
румба
Рога и копыта
выбор
тур
круг
квадрат
выбор
Рога и копыта
круг
факел
тур
тур
круг
квадрат
румба
Индекс
3-го уровня
Индекс
2-го уровня
Индекс
1-го уровня
тур
Рога и копыта
тур
круг
выбор
квадрат
Рога и копыта
круг
Данные:
румба
тур
румба
рога и копыта круг квадрат
румба
тур
факел
выбор факел
Рис. 45. Построение В+-дерева для данных поля FIM таблицы Firm
74
Процедура неудачного поиска осуществляется аналогично.
Факт неудачного поиска можно установить только на индексе 1-го
уровня, следовательно, не надо в этом случае обращаться по ссылке
в область данных.
Следует отметить, что при использовании упорядоченного индекса все значения в вершинах индекса 1-го уровня, как видно из
рис. 45, оказываются упорядоченными: выбор, квадрат, круг, рога
и копыта, румба, тур, факел.
SQL Server предполагает использование двух типов индексов:
• кластерные индексы (Clustered Index), которые предполагают физическое упорядочение данных,
• некластерные индексы (NonClustered Index) – без физического упорядочения данных.
Очевидно, кластерные индексы являются индексно-последовательными структурами, а некластерные – индексно-произвольными структурами данных, индекс в которых желательно иметь упорядоченным.
Кластерный, как и некластерный индекс, может быть построен
по любому полю таблицы. Однако кластерный индекс обычно по
умолчанию строится по первичному ключу таблицы. Поскольку
кластерный индекс предполагает физически упорядоченные данные, то он может быть только один в таблице. Некластерных индексов можно построить несколько.
С.34
(Корневая страница )
круг
с.20
рога и копыта с.21
Индекс 3-го уровня
С.20
выбор
круг
С.21
с.15
с.16
С.15
выбор
с.3
квадрат с.3
С.16
круг
С.1
1
2
тур
румба
рога и копыта с.17
Тур
с.18
С.17
с.2
С.18
рога и копыта с.2
румба
с.1
тур
с.1
факел с.4
Индекс
1-го уровня
(страницы-листья)
С.3
С.2
3
4
Индекс 2-го уровня
рога и копыта
круг
5
6
квадрат
выбор
С.4
7
факел
Область данных
Рис. 46. Некластерный индекс для поля FIM
таблицы Firm базы данных postavka
75
Индексы в среде SQL Server имеют структуру, похожую на В+дерево, вершинами которого являются страницы памяти.
Трехуровневый некластерный индекс для поля FIM представлен
на рис. 46 (нумерация страниц на рисунке произвольная).
Процедура поиска данных с помощью некластерного, а также
кластерного индекса несколько отличается от таковой в В+-дереве
и зависит от параметров страницы.
Если для какого-то значения явно указана ссылка на страницу перехода, задача упрощается. Если ссылка явно не указана, то процедуру перехода на следующую страницу проще пояснить на примере.
Пример 11. Рассмотрим сначала удачный поиск.
Пусть требуется найти данные для значения «выбор». Поиск,
как всегда, начинается с корневой страницы (на рис. 46 это – страница С.34). Значение «выбор» отсутствует на корневой странице.
Но «выбор» меньше, чем «круг», поэтому переходим на страницу
20 (как для значения «круг»), на которой такая ссылка есть. По
этой ссылке спускаемся на индекс 1-го уровня (страницу С.15), на
которой имеется ссылка на страницу данных (С.3).
Пусть требуется найти данные для значения поля «факел». На
корневой странице отсутствует это значение. Но «факел» больше,
чем «рога и копыта», поэтому переходим на страницу 21 (как для
значения «рога и копыта»). На странице 21 опять нет значения «факел», но «факел» больше, чем «тур», поэтому переходим на страницу 18 и находим на ней значение «факел» и ссылку на данные (С.4).
Рассмотрим неудачный поиск.
Пусть требуется найти данные для значения «курган». На корневой странице отсутствует это значение. Но «курган» больше,
чем «круг» и меньше, чем «рога и копыта». Поэтому переходим на
страницу 20 (по меньшему значению). На странице 20 также нет
значения «курган». Но «курган» больше, чем «круг». Поэтому
переходим на страницу 16 (по большему значению). На ней также
нет значения «курган». Поскольку это страница находится на 1-м
уровне индекса, то поиск заканчиваем. Поиск неудачен.
Синтаксис оператора создания индекса имеет вид:
Create [Unique] [Clustered | NonClustered]
Index имя_индекса On имя_таблицы
(столбец 1[,…])
[With [Pad_Index] [,Fillfactor = число]
[,Ignore_dup_key]
[,Drop_existing]
[,Statistics_norecompute]
76
[,Sort_in_tempdb] [On группа файлов]],
где
Unique – уникальный индекс. Он не существует как самостоятельный физический тип индекса, а является своеобразной надстройкой над кластерным и некластерным индексами. Уникальность подчеркивает отсутствие повторяющихся значений в индексируемом поле. Уникальным может быть как кластерный, так и
некластерный индекс.
Fillfactor – фактор заполнения, который определяет полноту
заполнения каждой страницы нижнего уровня. Если он не указан, то
нижние страницы индекса заполняются почти полностью, а корневая
и промежуточные страницы имеют свободное место как минимум
для двух строк. Если указано число 100, то страницы нижнего уровня заполняются на 100%, а корневая и промежуточные страницы попрежнему имеют свободное место как минимум для двух строк.
Pad_Index – если присутствует, то действие фактора заполнения распространяется на все страницы индекса.
Ignore_dup_key – игнорирование повторяющихся значений
индексируемого поля.
Drop_existing – индекс должен удаляться и создаваться заново.
Statistics_norecompute указывает на вычисление и хранение статистических данных о составе индексов.
Sort_in_tempdb определяет использование временной базы
данных tempdb для хранения промежуточных структур данных,
образуемых при создании индекса.
Пример 12. Создание уникального кластерного индекса по имени ind_PN для первичного ключа PN таблицы Post осуществляется
оператором:
Create Unique Clustered Index ind_PN On Post(PN);
Создание уникального кластерного составного индекса по имени PN_DN_DATA для полей PN, DN и DATA таблицы PD осуществляется оператором:
Create Unique Clustered Index PN_DN_DATA On PD(PN,DN,
DATA);
Поиск начинается с корневой страницы, указатель на которую
хранится в системной таблице sysindexes в системных представлениях базы данных postavka.
Таблица может иметь до 249 некластерных индексов, рекомендуется не более пяти [6]. Последовательность их создания не имеет
значения. Суммарная длина всех индексируемых значений должна быть не более 900 байт. Концевые страницы-листья некластер77
ного индекса могут содержать указатели на область данных, как на
рис. 46, или на значение кластерного индекса.
Кластерный индекс создается всегда первым.
Некластерный индекс при работе требует больше памяти, чем
кластерный. Однако при создании кластерного индекса требуется больше памяти, чем при создании некластерного индекса. Так,
например, при создании кластерного индекса для временного рабочего пространства требуется как минимум 120% объема памяти
индексируемой таблицы. Это объясняется тем, что при создании
кластерного индекса сервер копирует таблицу, физически упорядочивает ее по значению индексируемого поля, создает структуру
индекса и только после этого удаляет исходную таблицу. В результате созданный кластерный индекс занимает всего около 5% памяти от таблицы.
Всегда надо помнить о том, что частое обновление индексируемого поля замедляет работу приложения пользователя. Особенно
это касается поля, проиндексированного кластерным индексом,
так как обновление кластерного индекса влечет за собой обновление всех некластерных индексов.
Не все типы данных допускают индексирование. Так нельзя
индексировать данные типов: Text, Image, Bit, Ntext и некоторых
других.
Удаление индекса осуществляется оператором Drop index. Например, удалить созданный ранее составной индекс для полей PN,
DN и DATA из таблицы PD можно оператором
Drop index PN_DN_DATA On PD;
3.3.2. Манипулирование данными
Запросы на выборку (отбор) данных могут быть однотабличными, когда для получения ответа на запрос используется одна таблица, и многотабличными, когда для получения ответа на запрос используются две или несколько таблиц.
Запросы на выборку выполняются с использованием оператора
Select.
Однотабличные запросы на выборку данных
Укрупненный синтаксис оператора Select:
Select [предикат] {*| ИмяТаблицы.* | [ИмяТаблицы.]поле1
[As псевдоним1]
[, ИмяТаблицы.]поле2 [As псевдоним2] [, …]}
From выражение [, …]
78
[Where условие отбора данных]
[Group By список группируемых полей]
[Having условие группирования]
[Order By поле1 [Asc | Dsc] [, поле2 [Asc|Dsc]] [, …]].
Предикат используется для ограничения количества возвращаемых запросом записей. SQL Server допускает использование следующих предикатов:
Distinct – предикат, позволяющий исключить из ответа на запрос записи, содержащие повторяющиеся значения в отобранных
полях (пример 14).
Top n [Percent] позволяет получить в ответе на запрос n записей
или n процентов записей (если указано ключевое слово Percent),
находящихся в начале или в конце диапазона, заданного предложением Order By (пример 25).
Результатом выполнения запроса на выборку является так называемый динамический набор данных, который существует только при выполнении запроса и не сохраняется. Чтобы его получить
повторно, нужно еще раз выполнить запрос. Если результат запроса на выборку нужно сохранить, то это надо сделать принудительно, например, реализуя запрос на создание таблицы.
Пример 13. Выдать всю информацию о деталях можно согласно
синтаксису оператора Select следующими запросами:
Us postavka;
Select * From Det;
или
Select Det.* From Det;
или
Select DN,DIM,CENA From Det;
или
Select Det.DN, Det.DIM, Det.CENA From Det;
Здесь символ * означает, что в ответе будут выданы значения
всех полей таблицы Det. Однако так писать не рекомендуется.
В первом запросе сначала написан оператор Use postavka (если
база данных postavka не выбрана из списка в обозревателе объектов), что означает запрос к базе данных postavka. Без этого
оператора по умолчанию запрос может быть адресован системной
базе данных master, что приведет к ошибке, так как в базе данных
master отсутствует таблица Det. Всюду ниже будем опускать оператор Use postavka, но подразумевать его.
Во втором запросе явно указано имя таблицы (Det.*). В третьем запросе явно перечислены имена полей без имени таблицы
79
(DN,DIM,CENA). Такое возможно всегда для однотабличных запросов, а также для многотабличных запросов, если в участвующих
в запросе таблицах нет одинаковых имен полей. Последняя форма
запроса является наиболее полной и является предпочтительной.
Пример 14. Использование предиката Distinct.
Выдать города без повторений, в которых размещаются поставщики.
/* Использование предиката Distinct*/
Select Distinct GOR From Post;
Пример 15. Использование в запросах псевдонимов полей, выражений над полями и добавление в ответ вычисляемых полей.
Все детали подорожали на 10%. Выдать информацию о деталях,
а также о старой и новой цене. Таблицу оставить без изменений.
Select DN As[номер детали],DIM As[имя детали],
CENA As[старая цена],CENA*1.1 As[новая цена]
From Det;
В этом запросе использованы псевдонимы полей таблицы Det и
выражение над полем CENA для вычисления новой цены. Псевдонимы задаются ключевым словом As и в данном запросе они заключены в квадратные скобки, поскольку содержат пробелы. Псевдоним (второе имя) поля задают тогда, когда этим именем хотят назвать поле в ответе на запрос. Результат выполнения запроса представлен на рис. 47.
Здесь поле «новая цена» называется вычисляемым полем, поэтому
подобные запросы называются запросами с вычисляемыми полями.
Если вы не очень уверены в формировании запроса на T-SQL,
можно воспользоваться помощником – редактором запросов.
Реализуем запрос из примера 15 с использованием редактора
запросов. Для этого в обозревателе объектов сервера выделим базу
данных postavka. Затем по кнопке «Создать запрос» откроем пустое окно «SQLQuery…» для создания запроса. В линейке на ленте
главного меню появится пункт меню «Запрос». Открыв его, по команде ⇒ Создать запрос в редакторе… запустим редактор запросов.
Рис. 47. Результат выполнения запроса
80
Откроется пустое окно «Конструктор запросов», поверх которого откроется окно «Добавление таблицы», предназначенное для
выбора таблиц, участвующих в запросе. Выделим таблицу Det, которую с помощью кнопок «Добавить» и «Закрыть» добавим в окно
конструктора запросов.
Редактор запросов можно вызвать иначе, а именно из контекстного меню, открытого в любом месте пустого окна «SQLQuery…»
для создания запросов.
Окно конструктора запросов состоит из трех взаимосвязанных
областей (разделов). Верхняя область предназначена для добавления в нее таблиц, участвующих в запросе (в нашем случае таблица Det). В центральной области, похожей на бланк QBE-запроса
Access, формируем запрос (рис. 48).
В столбце бланка «Столбец» выбираем из списка поля таблиц,
которые мы хотим видеть в ответе на запрос. В рассматриваемом
примере – это поля DN,DIM,CENA. Вместо выбора из списка отобранные поля можно указать включением соответствующих флажков в таблицах верхней области окна конструктора.
Вычисляемое поле (CENA*1,1), которого нет в таблице, вводим
вручную в пустое поле конструктора или выбираем из списка еще
раз поле CENA и дописываем нужное выражение. Обратите внимание на то, что вещественное число в вычисляемом выражении в качестве разделителя использована запятая, а в SQL-коде запятая автоматически заменяется точкой.
На бланке можно также задать псевдонимы полей, указать принадлежность выбранных полей таблицам (как правило, устанавливается автоматически, но можно и выбрать таблицы из списка).
Включением соответствующих флажков в столбце бланка «Выход»
указывается, значения каких полей нужно выводить в ответе на запрос. Например, значения полей, для которых задаются условия
отбора записей, часто не нужны при выводе результата выполнения запроса.
Можно выбрать из списка тип и порядок сортировки для полей
в ответе.
В столбце бланка «Filter» можно написать условие отбора записей.
Столбцы бланка «Or..» используются для построения булевского условия отбора, включающего операции Or. Условие с использованием операции Or можно для некоторых запросов написать
в одной строке. Например, для вывода сведений о поставщиках из
Москвы или из Самары можно в столбце «Filter» в строке GOR написать условие в виде: Москва Or Самара.
81
Булевский запрос с использованием операции And конструируется иначе. Например, для поиска сведений о гайках по цене >100
нужно в столбце «Filter» в строке DIM написать гайка, а в строке
CENA написать условие >100. А для поиска сведений о деталях, цена
которых заключена в интервале «больше 100 и меньше или равно
700» в столбце «Filter» в строке CENA написать условие в виде:
> 100 And <=700.
По мере заполнения средней области в нижней области окна
конструктора запросов автоматически формируется код на языке
T-SQL, который по кнопке «ОК» автоматически переносится в окно
запроса «SQLQuery…». На рис. 48 показано заполненное окно конструктора для запроса из примера 15.
Результат выполнения запроса представлен на рис. 47.
Пример 16. Использование в запросе агрегатных функций.
Выдать информацию о количестве записей в таблице Det, а также о средней и максимальной цене деталей. В этом запросе удобно
воспользоваться агрегатными (или статистическими) функциями
и псевдонимами:
Select Count(*) As[количество записей],
Avg(CENA) As[средняя цена],
Max(CENA) As[максимальная цена]
From Det;
Ответ на запрос будет дан одной строкой, как показано на рис. 49.
Функция Count(*) осуществляет подсчет по вертикали. Символ «*» используется в качестве аргумента, что означает подсчет
Рис. 48. Окно конструктора для запроса из примера 15
82
Рис. 49. Результат выполнения запроса
по вертикали по всем записям. Другие агрегатные функции будут
указаны в предложении Group By.
Пример 17. Использование в запросе стандартных функций.
Выдать всю информацию из таблицы PD, добавив в ответ на запрос
вычисляемое поле, в котором отображать месяц из поля DATA. Для
этого нужно использовать стандартную функцию Month. Список часто используемых стандартных функций приведен в приложении 2.
Эту задачу можно решить, выполнив запрос:
Select PN,DN,DATA, Month(DATA) As ′месяц′ From PD;
Результат выполнения запроса представлен на рис. 50.
На рис. 51 представлена реализация этого запроса в конструкторе. В данном случае запрос в конструкторе не особенно облегчает
работу, так как функцию Month необходимо вводить вручную.
Рис. 50. Результат выполнения запроса
Рис. 51. Реализация запроса в конструкторе
83
Замечания по функциям обработки дат [1]
Функции обработки дат выполняют операции над данными типа
Datetime. Эти функции можно использовать в списке столбцов
оператора Select, в предложении Where или в любом другом месте, где может задаваться выражение. Синтаксис функции обработки дат имеет вид:
Select функция_обработки_дат (параметры).
Значения типа Datetime, передаваемые в качестве параметров,
необходимо заключать в одинарные или двойные кавычки (если
указано разрешение на двойные кавычки). В некоторые функции
добавляется специальный параметр (часть_даты), который указывает, с какой частью переменной типа Datetime следует выполнить требуемые операции.
Имена некоторых функций совпадают с таковыми в JET-SQL
(стандарт для Access), например,
Day(date) – возвращает день из date (одну или две десятичные
цифры);
Month(date) – возвращает месяц из date (одну или две десятичные цифры);
Year(date) – возвращает год из date (четыре десятичные цифры),
а некоторые отличаются от стандарта JET-SQL, например,
Getdate() – возвращает текущую системную дату и время во внутреннем формате.
Примеры выполнения функций обработки дат
Select Day (′15/05/16′); Ответ: 15.
Select Month (′15/05/16′); Ответ: 5.
Select Year (′15/05/16′); Ответ: 2016.
Select Getdate (); Ответ: 2016-11-20 19:48:39.743.
Рассмотрим пример использования еще одной стандартной
функции обработки дат Dateadd, синтаксис которой задается
в виде (см. Приложение 2):
Dateadd (часть_даты, число, date),
согласно которому к date добавляется величина часть_даты,
умноженная на число. Например, Select
Dateadd (mm, 6,
′30/1/00′);
Здесь mm – часть_даты (месяц), 6 – число, ′30/1/00′ – date.
Это означает, что из date выделяется значение месяца (1), к которому добавляется число 1*6. В результате получаем ответ: 2000-0730 00:00:00.000.
В качестве параметра часть_даты могут использоваться: dd
(day в диапазоне 1 – 31), hh (hour в диапазоне 0 – 23), mi (minute
84
в диапазоне 0 – 59), mm (month в диапазоне 1 – 12), ss (second в диапазоне 0 – 59), yy (year в диапазоне 1753 – 9999) и пр.
Полный список допустимых значений этого параметра приведен
в работе [1].
Предложение Where задает условие отбора записей в ответ.
Пример 18. Выдать сведения о поставщиках из Москвы можно
запросом
Select PIM,ST,GOR,TEL From Post Where GOR=′Москва′;
Другие примеры условий отбора:
• Where GOR Like ′М%′;
Оператор Like позволяет по заданному шаблону выбрать города, название которых начинается на букву М, за которой может стоять любое количество любых символов. Символ % часто называется
трафаретным символом [1]. Список основных трафаретных символов приведен ниже в таблице 3.1.
• Where GOR In(′Москва′, ′СПб′, ′Самара′);
Оператор In указывает на принадлежность множеству городов,
заключенных в скобках. Этот запрос оптимизатор запросов преобразует к виду
Where GOR=′Москва′ Or GOR=′СПб′ Or GOR=′Самара′, так как
логические операторы имеют больший приоритет и выполняются
быстрее.
• Where CENA Between 100 And 200;
Оператор Between указывает на диапазон значений цены, включая значения 100 и 200. Если есть возможность использования
In или Between, то Between предпочтительнее, так как оператор
Between имеет больший приоритет. Например, вместо условия
Where PN In(1,2,3,4)лучше использовать условие
Where PN Between (1 And 4);
• Where CENA Is Null;
Is Null указывает на неопределенное значение Null, которое
может быть не введено в поле CENA.
Ключевое слово Is может быть использовано только в конструкциях Is Null или Is Not Null.
Указанные выше скалярные операторы обладают разным быстродействием. Самый быстрый оператор «равно» (=). Далее в порядке уменьшения быстродействия идет группа операторов сравнения с одинаковым быстродействием (>, >=, <, <=,), затем по убыванию быстродействия идут логические операторы: Not, And, Or, Xor,
и операторы: Like, < >, Between, In.
85
Таблица 3.1
Трафаретные символы
Трафаретный символ
Значение
_ (подчеркивание)
Любой одиночный символ
Любая последовательность любых символов
(аналог символа * в JET-SQL)
Любой одиночный символ в границах указанного
диапазона [a-c] или набора символов [abc]
Любой одиночный символ не в границах указанного диапазона или набора символов
%
[]
[∧]
Например,
Like′_ос′ – осуществляется поиск слова, состоящего из трех
букв и заканчивающихся на ос.
Like′М%′ – поиск слов, начинающихся на букву М.
Like′[ус]пех′ – поиск слов, содержащих набор символов
упех, спех.
Like′М[∧о]′ – поиск слов, начинающихся с буквы М и не содержащих букву о во второй позиции (всего слово содержит две
буквы).
В предложении Where запрещается использование агрегатных
функций (Sum, Count, Avg, Max, Min и пр.), а использование стандартных функций разрешается.
Предложение Group By объединяет записи с одинаковыми значениями в указанном списке полей в одну группу.
Группирование данных позволяет получать статистическую,
итоговую и другую информацию по отдельным группам данных.
Группировать данные можно по одному или нескольким полям. При
этом в одну группу включаются данные с одинаковыми значениями
в поле группирования. Например, все записи, сгруппированные по
номеру поставщика в таблице PD, образуют столько групп, сколько
имеется разных значений в поле PN. При группировании данных по
нескольким полям порядок (последовательность) имен полей в списке определяет уровень группирования для каждого поля (вложенные группы). Группирование осуществляется сначала по первому
полю списка, затем для одинаковых значений первого поля данные
группируются по второму полю в списке и так далее.
При группировке данных можно использовать агрегатные функции:
Sum – суммирование всех значений поля в группе данных,
Avg – вычисление среднего значения поля по группе,
86
Min или Max – вычисление минимального или максимального
значения поля в группе,
Count – вычисление количества записей, входящих в группу,
StDev – вычисление среднеквадратического отклонения по
группе,
Var – вычисление дисперсии,
First или Last – первое или последнее значение поля в группе.
В ответе на запрос данные по каждой группе отображаются одной строкой.
Пример 19. Пусть таблица Det базы данных postavka содержит
данные, представленные на рис. 52. Создадим запрос с группировкой по полю DIM. В каждой группе вычислим суммарное количество деталей на складе и укажем количество записей в группе.
Для удобства представления информации в ответе на запрос используем псевдонимы.
Сформулируем запрос в виде:
Select DIM,Count(DIM) As [количество в группе],
Sum(KOL_SKLAD) As [суммарное количество на складе]
From Det Group By DIM;
На рис. 53 представлен ответ на запрос.
Пример 20. Так как в ответе на запрос данные по каждой группе
отображаются одной строкой, то задачу, поставленную в примере
14, можно решить без предиката Distinct, а с использованием
предложения Group By:
Select GOR From Post Group By GOR;
Использование операторов Cube, Rollup
В отличие от JET-SQL (Access) стандарт T-SQL не позволяет создавать перекрестные запросы. Некоторые обобщения данных можно сделать, используя, так называемые сверхагрегатные операторы
Рис. 52. Содержимое
таблицы Det
Рис. 53. Результат
выполнения запроса
87
Rollup и Cube. В запросах с использованием этих операторов обычно используются агрегатные функции. Синтаксис запросов с операторами Rollup и Cube приведен в работе [1].
Оператор Rollup показывает в дополнительно создаваемых
строках значения указанной агрегатной функции в выбранных
столбцах с учетом группируемых полей.
Пример 21. Пусть таблица PD в базе данных postavka содержит
данные, представленные на рис. 54. Ответ на запрос
Select PN,DN,Sum(KOL) As [Суммарное количество]
From PD Group By PN,DN
With Rollup;
представлен на рис. 55.
Оператор Cube показывает значения агрегатных функций для
всех комбинаций выбранных столбцов. Результат выдается как
бы в виде многомерного куба. Синтаксис оператора Cube аналогичен синтаксису оператора Rollup. Нужно просто заменить слово
Rollup на Cube. Попробуйте выполнить запрос с оператором Cube
самостоятельно.
Рис. 54. Содержимое таблицы
PD
Рис. 55. Результат выполнения
запроса
Предложение Having задает условие группирования данных,
определяя, какие записи должны быть отобраны в группу.
Пример 22. Сгруппировать данные из таблицы PD (рис. 54) по
полю DN и выдать информацию о номере детали, количестве деталей в группе, суммарных поставках для каждой группы, если
количество записей в группе больше одной. Информацию выдать
только для деталей с номерами 1, 2, 3. Это можно сделать с помощью запроса
Select DN,Count(DN) As[в группе],
Sum(KOL) As [суммарные поставки]
From PD 
88
Where DN Between 1 And 3 
Group By DN Having Count(DN)> 1; 
Рис. 56. Результат
выполнения запроса
Если предложение Having задает условие без агрегатной функции, то такое условие целесообразно перенести в предложение
Where. В этом случае ответ на запрос будет получен быстрее.
Предложение Order By позволяет отсортировать данные в ответе по одному или нескольким полям.
Ключевое слово Asc (Ascending, устанавливается по умолчанию) позволяет выполнить сортировку по возрастанию значений
поля (для символьных полей – по алфавиту), а ключевое слово Desc
(Descending) – по убыванию значений поля.
Пример 23. Выдать информацию о поставщиках, отсортировав
ее сначала по имени поставщика (по возрастанию значений), а затем
по городу (по убыванию значений). Это можно сделать запросом:
Select Post.* From Post Order By PIM,Gor Desc;
Пример 24. Выдать следующие сведения по каждому типу деталей:
– номер детали (в упорядоченном виде по возрастанию значений),
– количество записей в группе,
– суммарное количество поставок по каждому типу детали,
– дату последней поставки в группе.
Это можно сделать запросом:
Select DN, Count(DN) As [в группе],
Sum(KOL) As [суммарное количество],
Max (DATA) As [последняя поставка]
From PD Group By DN Order By DN;
Используя сортировку данных и предикат Top n или Top n
Percent, можно выводить заданное количество (числом n) или n
процентов (n Percent) первых записей с минимальным или максимальным значением в отсортированных полях.
Пример 25. Запрос с использованием предиката Top.
Выдать сведения о двух максимальных поставках поставщиков.
Поставки отображаются в таблице PD, данные из которой были
представлены на рис. 54. Для того чтобы получить все максимальные поставки, т.е. максимальные значения поля KOL в верхних
89
строках таблицы, нужно упорядочить данные таблицы PD по убыванию значений поля KOL. Это можно сделать запросом: 
Select PD.* From PD Order By KOL Desc;
Ответ на запрос представлен на рис. 57.
Выдать сведения о первых двух максимальных поставках можно запросом (запись Top(2) также верна): 
Select Top 2 PD.* From PD Order By KOL Desc; 
Ответ на запрос представлен на рис. 58.
При этом выдаются данные только о первых двух максимальных поставках. Однако остается проблема, когда несколько ниже
идущих записей таблицы PD будут иметь значение поля KOL = 100.
В нашем случае, как видно из рис. 57, третья запись также имеет
значение поля KOL = 100. Проблема решается при помощи параметра With Ties, который включит в результирующий набор не только n записей (в нашем случае две), но и все ниже идущие записи,
у которых значения поля KOL также равно 100 (в рассматриваемом
примере еще и третья запись). В этом случае запрос будет такой:
Select Top 2 With Ties PD.* From PD Order By KOL Desc;
Ответ на запрос представлен на рис. 59.
Создадим этот запрос с использованием редактора запросов.
Сначала создаем запрос на выборку данных из таблицы PD с сортировкой по убыванию значений поля KOL, как показано на рис. 60
(слева).
Затем из контекстного меню, которое можно открыть в любом месте окна «Конструктор запросов», выполняем команду ⇒ Свойства.
Открывается окно свойств запроса, в котором выполняем настрой-
Рис. 57. Результат выполнения
запроса
Рис. 58. Вывод двух максимальных
поставок
Рис. 59. Максимальные поставки
90
91
Рис. 60. Упорядочение данных по полю KOL (слева)
и открытие окна свойств запроса (справа)
ки, показанные на рис. 61 (слева). На рис. 61 (справа) представлен
ответ на запрос, такой же, как на рис. 59 (с использованием параметра With Ties). Если в окне свойств для параметра With Ties установим значение «Нет», то получим ответ, такой же, как на рис. 58.
Многотабличные запросы на выборку данных
Многотабличный запрос – это запрос, ответ на который может
быть получен из более, чем одной таблицы. Для реализации такого
запроса таблицы должны быть связаны между собой.
Используются следующие типы связей (или типы объединений)
таблиц:
• Симметричное (внутреннее) объединение Inner Join, при котором объединяются записи из первой и из второй таблиц, сопоставимые друг другу;
• Внешние объединения:
- Левое: Left Outer Join, при котором объединяются все записи из левой таблицы (стоящей в запросе слева от Left Outer Join)
и только сопоставимые с ними записи из правой таблицы;
- Правое: Right Outer Join, при котором объединяются все записи из правой таблицы и только сопоставимые с ними записи из
левой таблицы;
- Полное: Full Outer Join, которое включает все сопоставимые и несопоставимые записи обеих таблиц.
Чаще всего используется симметричное объединение Inner Join.
Рассмотрим примеры.
Пример 26. Многотабличный запрос из двух таблиц. Выдать города без повторений, из которых поставляются гайки можно запросом
Select Distinct GOR From Det,Post,PD
Where ((Det.DIM = ′гайка′) And (Det.DN = PD.DN)
And (Post.PN = PD.PN));
Эта форма записи запроса соответствует синтаксису Microsoft и
считается устаревшей.
Возможны другие формы записи запроса:
Select Distinct GOR
From Det Inner Join (Post Inner Join PD
On Post.PN = PD.PN) On Det.DN = PD.DN
Where Det.DIM = ′гайка′;
или
Select Distinct GOR
From Det Inner Join PD On Det.DN = PD.DN
Inner Join Post On Post.PN = PD.PN
Where Det.DIM = ′гайка′;
92
93
Рис. 61. Настройка свойств запроса (слева) и ответ на запрос (справа)
94
Рис. 62. Реализация первой части запроса (слева) и настройка свойств запроса (справа)
Рис. 63. Результирующий запрос (слева) и ответ на запрос (справа)
Эти формы записи запроса соответствует синтаксису ANSI (American National Standards Institute) и используются в настоящее время.
Реализуем последний вид запроса с помощью редактора запросов. Для этого сначала построим первую часть запроса (без предиката Distinct), как показано на рис. 62 (слева). Затем откроем окно
свойств и установим для свойства «Неповторяющиеся значения»
значение «Да», как на рис. 62 (справа), в результате чего в запрос
будет включен предикат Distinct, что обеспечит вывод городов
без повторений (рис. 63).
Пример 27. Многотабличный запрос из трех таблиц. Выдать
имена, фирмы и города поставщиков, а также размер их поставок
можно запросом 
Select PIM, FIM, GOR, DIM, KOL
From Det Inner Join PD On Det.DN = PD.DN
Inner Join Post On PD.PN = Post.PN
Inner Join Firm On Post.NFM = Firm.NFM;
Рассмотрим разницу между типами объединения таблиц на одном и том же примере.
Примеры 28. Построим многотабличный запрос, ответ на который будет получен из таблиц Post и PD для разных типов объединения этих таблиц. На рисунках 64 и 65 представлены данные из
таблиц Post и Det.
Рис. 64. Содержимое таблицы Post
Рис. 65. Содержимое
таблицы PD
95
• Симметричное объединение Inner Join:
Select Post.PN As Post_PN,PIM,PD.DN, KOL, PD.PN As PD_PN
From PD Inner Join Post On PD.PN = Post.PN;
На рис. 66 представлен ответ на запрос, в котором присутствуют
только сопоставимые записи из таблиц Post и PD (одинаковые значения Post_PN и PD_PN).
Редактор запросов объединение Inner Join устанавливает по
умолчанию.
• Левое объединение Left Outer Join:
Select Post.PN As Post_PN,PIM,PD.DN, KOL,
PD.PN As PD_PN
From Post Left Outer Join PD On Post.PN =PD.PN;
Ответ на запрос представлен на рис. 67, в котором из левой таблицы (Post) выданы все записи, а из правой (PD) – сопоставимые
с ними записи.
Рис. 66. Объединение Inner Join
Рис. 67. Объединение Left Outer Join
96
Объединение Left Outer Join с использованием редактора запросов устанавливается в конструкторе запросов принудительно
с помощью флажка «Выбрать все строки из Post» в окне, открытом из контекстного меню на линии связи (рис. 68). Ответ на запрос
представлен на рис. 67.
• Правое объединение Right Outer Join:
Select Post.PN As Post_PN,Post.PIM, PD.DN, PD.KOL,
PD.PN As PD_PN
From Post Right Outer Join PD On Post.PN = PD.PN;
На рис. 69 (слева) сконструирован запрос с использованием объединения Right Outer Join с помощью редактора запросов, а на
рис. 69 (справа) представлен ответ на этот запрос. В результате выполнения запроса выданы все записи из правой таблицы PD, а из левой таблицы Post – сопоставимые с ними записи. Ответ совпадает
с таковым для объединения Inner Join (рис. 66).
• Полное объединение Full Outer Join:
Select Post.PN As Post_PN, Post.PIM, PD.DN, PD.KOL,
PD.PN As PD_PN
From Post Full Outer Join PD On Post.PN = PD.PN;
Ответ на запрос будет таким же, как показано на рис. 67, поскольку он включает все записи из обеих таблиц, как сопоставимые, так и несопоставимые.
Рис. 68. Объединение Left Outer Join
в конструкторе запросов
97
98
Рис. 69. Объединение Right Outer Join в конструкторе запросов (слева) и ответ на запрос (справа)
Объединение Full Outer Join с использованием редактора запросов устанавливается принудительно в конструкторе запросов
включением обоих флажков «Выбрать все строки из Post» и «Выбрать все строки из PD» в окне, открытом из контекстного меню на
линии связи.
Примеры 29. Выдать сведения о поставщиках без поставок можно запросом:
Select Post.PN As Post_PN, PIM,GOR, PD.PN As PD_PN
From Post Left Outer Join PD On PD.PN = Post.PN
Where (PD.PN Is Null);
На рис. 70 представлен ответ на запрос.
Сравните ответы на запросы, представленные на рис. 70 и 67. 
Еще есть возможность построить запросы с использованием
ключевых слов Cross Join (декартово произведение [11]), а также
естественное соединение из двух таблиц с использованием ключевого слова Join, например:
/* Декартово произведение */
Select Firm.NFM As Firm_NFM,FIM,Post.NFM As Post_NFM,PIM
From Firm Cross Join Post;
и
/* Естественное соединение */
Select Distinct Firm.NFM As Firm_NFM,FIM,Post.NFM As
Post_NFM,PIM
From Firm Join Post On Firm.NFM = Post.NFM;
Обратите внимание на использование предиката Distinct
в естественном соединении для исключения из ответа повторяющихся записей.
Если при построении многотабличного запроса с любым типом
объединения таблиц, кроме декартова произведения, в запросе участвуют только поля связи между таблицами, то почти всегда потребуется использоваие предиката Distinct.
Рис. 70. Поставщики без поставок
99
Запросы на создание таблицы
Как было сказано ранее, результатом выполнения запроса на выборку является так называемый динамический набор данных, который существует только при выполнении запроса и не сохраняется. Чтобы его получить повторно, нужно еще раз выполнить запрос.
Если результат запроса на выборку нужно сохранить, то это можно
сделать принудительно, реализуя запрос на создание таблицы.
Синтаксис оператора:
Select Имя_поля_1[,Имя_поля_2 [,…]]Into
Имя_новой_таблицы
From Имя_источника
....
Пример 30. Выдать сведения о поставщиках из Москвы и о фирмах, к которым они относятся. Полученную информацию сохранить в таблице Новая. Это можно сделать запросом на создание таблицы:
Select Firm.FIM, Post.PIM, Post.GOR, Post.TEL Into Новая
From Post Inner Join Firm On Post.NFM = Firm.NFM
Where (Post.GOR = ′Москва′);
Создадим запрос на создание таблицы с помощью редактора запросов. Для этого сначала создадим запрос на выборку данных с заданным критерием отбора (рис. 71, слева), а затем преобразуем его
в запрос на создание таблицы. Такое преобразование можно сделать, выполнив команду ⇒ Изменить тип/Создать таблицу… из
контекстного меню, открытого в любом месте окна конструктора запросов. Откроется окно, в котором нужно ввести имя создаваемой
таблицы.
В результате выполнения созданного запроса и обновления обозревателя объектов сервера в базе данных postavka появится таблица Новая с данными, соответствующими условию отбора, но без
первичного ключа. Первичный ключ можно задать принудительно. Типы данных полей новой таблицы наследуют типы данных таблиц-источников.
Запросы на изменение данных
К запросам на изменение данных относятся запросы на:
• добавление записей в таблицу (Insert … Into),
• обновление полей записей таблицы (Update),
• удаление записей из таблицы (Delete).
Добавление записей в таблицу
В таблицу можно добавить одну запись с заданными значениями
полей или несколько записей, отобранных из других таблиц или из
этой же таблицы.
100
101
Рис. 71. Запрос на создание таблицы с помощью редактора запросов
Добавление в таблицу одной записи осуществляется оператором
со следующим синтаксисом:
Insert Into Имя_таблицы_приемника[(имя_поля_1
[,имя_поля_2[,…]])]
Values (значение_1[,значение_2[,…]).
Примеры 31. Добавить в таблицу Firm (поле NFM – счетчик) новую фирму «корабел» можно оператором 
Insert Into Firm(FIM) Values (′корабел′);
Вводить значения обязательно нужно в те поля, в которых не
допускаются значения Null. В поля, которые допускают значения
Null, данные можно не вводить.
Не допускается вводить данные в поле типа «счетчик».
Создадим такой же запрос на добавление записи в таблицу Firm
с помощью редактора запросов. Для этого сначала создадим в конструкторе запрос на выборку данных из полей таблицы, в которые
будем вводить данные (рис. 72, слева). Затем выполним команду
⇒ Изменить тип/Вставить значения из контекстного меню, открытого в любом месте окна конструктора запросов. В ответ видоизменится окно конструктора запросов, в котором появится только один столбец, в который нужно ввести новое значение, как это
сделано на рис. 72 (справа). Причем, вводить слово корабел можно
без кавычек, так как кавычки будут добавлены редактором запросов автоматически, поскольку в структуре таблицы Firm поле FIM
имеет строковый тип. А поскольку этот тип nvarchar, то значение
будет автоматически написано как N′корабел′.
Рис. 72. Создание в конструкторе запроса
на добавление одной записи в таблицу Firm
102
Добавить в таблицу Новая поставщика Тюрина, номер телефона
которого 2356667 из фирмы факел можно оператором
Insert Into Новая(PIM,FIM,TEL)
Values(′Тюрин′,′факел′,′2356667′);
Добавление данных осуществляется в соответствии с установленной в таблице упорядоченностью данных, иначе – в конец таблицы, причем первое значение Тюрин добавляется в первое поле
PIM, указанное в списке полей, второе значение – во второе поле и
так далее. В данном примере в поле GOR, которое имеется в таблице
Новая, данные не вводятся. Так можно сделать, поскольку структура таблицы Новая допускает значения Null.
Если в операторе не указан список полей, то задаваемые значения вводятся в поля, последовательность которых определена
в структуре таблицы. Кроме того, значения должны вводиться обязательно во все поля, кроме поля типа «счетчик» (если он в таблице
присутствует). Например,
Insert Into Новая
Values(′факел′, ′Тюрин′,′СПб′,′2356667′);
или
Insert Into Post
Values(′Крылова′,80,′Москва′,′5454544′,′приоритет 2′,3);
Можно добавить в таблицу несколько записей, отобранных из
другой или из этой же таблицы, а также из других таблиц. Синтаксис оператора добавления в таблицу нескольких записей имеет вид:
Insert Into имя_приемника [(имя_поля_1[, имя_поля_2
[,...]])]
Select [имя_источника] имя_поля_1[, имя_поля_2[,...]
From выражение.
Пример 32. Добавить в таблицу Новая несколько записей, отобранных из таблиц Post и Firm для поставщиков из города СПб
можно оператором
Insert Into Новая(PIM, GOR, FIM)
Select Post.PIM, Post.GOR, Firm.FIM
From Firm Inner Join Post On Firm.NFM = Post.NFM
Where (Post.GOR = ′СПб′);
Создадим такой же запрос на добавление нескольких записей в таблицу Новая из таблиц Firm и Post с помощью редактора запросов.
Для этого сначала создадим в конструкторе запрос на выборку данных
из полей PIM,GOR,FIM указанных таблиц, в которые будем вводить
данные (рис. 73, слева). Затем выполним команду ⇒ Изменить тип/
Вставить результаты… из контекстного меню, открытого в любом
103
104
Рис. 73. Создание в конструкторе запроса на добавление нескольких записей в таблицу Новая
месте окна конструктора запросов. В открывшемся диалоговом окне
выбираем таблицу Новая, в которую будут внесены отобранные данные (рис. 73, справа). Запрос сконструирован. Далее нужно выполнить запрос и убедиться в правильном решении поставленной задачи.
Обновление полей записей таблицы
Синтаксис оператора обновления поля записи:
Update Имя_таблицы Set Новое_значение
Where Условие_отбора(задает старое значение).
Примеры 33. Обновить значение поля GOR в таблице Новая, заменив город Самару на Караганду, можно оператором
Update Новая Set GOR = ′Караганда′ Where(GOR = ′Самара′);
Можно одним запросом обновить несколько полей записи. Например, в таблице Новая в записях с городом Караганда заменить
имя поставщика на имя – Сидоров, а город Караганду – на город
Тула можно оператором
Update Новая Set GOR = ′Тула′,PIM = ′Сидоров′
Where(GOR = ′Караганда′);
Пусть болты подорожали на 10%, и при этом болты «стали»
шайбами. Внести соответствующие изменения в таблицу Det можно оператором
Update Det Set CENA = CENA*1.1,DIM = ′шайба′
Where DIM = ′болт′;
Для создания такого запроса с помощью редактора запросов
надо сначала создать запрос на выборку болтов из таблицы Det, а
затем в окне конструктора открыть контекстное меню, из которого
выбрать команду ⇒ Изменить тип/Обновить. В результате в окне
конструктора появится столбец «Новое значение», в который надо
вписать новые данные (рис. 74).
Удаление записей из таблицы
Синтаксис оператора удаления записи из таблицы:
Delete[Имя_таблицы] From Имя_таблицы
Where Условие_отбора.
Пример 34. Удалить из таблицы Firm фирму корабел можно
оператором
Delete From Firm Where (FIM =′корабел′);
На рис. 75 показано создание этого запроса с помощью редактора запросов.
Удалить все записи из таблицы Новая можно оператором
Delete From Новая;
или оператором
Truncate Table Новая;
105
106
Рис. 74. Создание запроса на обновление двух полей в таблице Det
107
Рис. 75. Создание запроса на удаление записи из таблицы Firm
При этом структура таблицы сохраняется. Последний оператор
может выполнять только пользователь, наделенный правами администратора, или владелец таблицы.
Вложенные запросы
Вложенный запрос (подзапрос или подчиненный запрос) – это
Select-оператор, который помещается внутрь другого оператора и
возвращает результаты, используемые в предложениях Where или
Having внешнего запроса. Подзапрос также может использоваться
в предложении From. Запрос, содержащий подзапрос, называется
родительским или главным запросом[1].
Различают вложенные и коррелированные подзапросы. Вложенный подзапрос отличается от коррелированного подзапроса тем,
что условие поиска в нем не зависит от данных внешнего запроса,
в то время как в коррелированном подзапросе, который определяет
критерий выбора во внешнем запросе, условие поиска в свою очередь зависит от данных анализируемой строки внешнего запроса.
В данном учебном пособии рассматриваются только вложенные
запросы. Информацию о коррелированных подзапросах можно
найти в работах [5], [15].
Подзапросы всегда ограничиваются круглыми скобками и должны
стоять в правой части оператора сравнения родительского запроса.
В простом случае вложенный подзапрос выглядит следующим
образом:
Select операнд1, операнд2,...
From имя_таблицы
Where операнд = (Select операнд
From имя_таблицы Where условие).
Различают подзапросы, которые могут возвращать одну или более одной строки.
Подзапросы, возвращающие одну строку
В случае, если подзапрос возвращает одну строку, в предложении Where родительского запроса используют простые операции
сравнения (=, < >, >, <, <= и так далее).
Примеры 35. Для поиска деталей, имеющих минимальную
цену, необходимо выполнить запрос:
Select DIM,CENA From Det Where CENA =(Select Min(CENA)
From Det);
В данном запросе и подзапрос, и родительский запрос обращаются к одной и той же таблице Det.
Выдать информацию о детали, которая поставляется в минимальном количестве (должна быть одна строка), можно запросом
108
Select * From Det
Where DN =(Select Top 1 DN From PD Order By KOL);
В данном запросе подзапрос и родительский запрос обращаются
к разным таблицам, подзапрос – к таблице PD, а родительский запрос – к таблице Det.
Подзапросы, возвращающие несколько строк
В таких запросах нужно использовать скалярный оператор In.
Примеры 36. Выдать сведения о деталях, которые стоят столько
же, сколько стоит болт. Здесь подзапрос возвращает больше одной
строки, если в таблице имеются болты с разной ценой.
Select * From Det Where CENA In (Select CENA From Det
Where DIM =′болт′);
Выдать сведения о поставщиках, имеющих более двух поставок
можно запросом
Select * From Post Where Post.PN In (Select PD.PN From PD
Group By PD.PN Having Count(PD.PN)>2);
Типы подзапросов
• запросы, использующие сравнения, синтаксическая конструкция которых может быть такой:
[AnyAll Some] (оператор SQL),
где Any – любой, All – все, Some – некоторые;
• запросы, содержащие предложения, синтаксическая конструкция которых может быть такой:
выражение [Not] In (оператор SQL);
• запросы, содержащие предложения, синтаксическая конструкция которых может быть такой:
[Not] Exists (оператор SQL),
где Exists – существуют.
Операторы Any и Some возвращают значения True, если условие
выполняется хотя бы для одного возвращаемого подзапросом значения, в противном случае – False.
Оператор All возвращает значения True, если условие выполняется для каждого возвращаемого подзапросом значения, в противном случае – False.
Пример 37. Выдать сведения о деталях, цена которых больше,
чем цена любой гайки можно запросом
Select * From Det Where CENA > Any(Select CENA From Det
Where DIM =′гайка′);
Для исключения значений, возвращаемых подзапросом, используется операция Not In. При использовании Not In необходимо учитывать, что если среди возвращаемых подзапросом значений
109
имеется неопределенное значение (Null), то результат сравнения
в операции Not In будет Null.
Пример 38. Вывести сведения о поставщиках, для которых нет
поставок можно запросом
Select * From Post Where Post.PN Not In (Select PD.PN From PD);
Тем не менее, это – не корректно сформулированный запрос, который может не вернуть ни одной строки. Поэтому решить поставленную задачу лучше с помощью корректно сформулированного
запроса:
Select * From Post Where Not Exists (Select PD.PN From PD
Where PD.PN = Post.PN);
Подзапросы можно использовать не только в предложениях
Where, определяющих условия выбора строк, но и в условии выбора групп строк, задаваемых предложением Having.Кроме того,
подзапросы могут быть вложенными один в другой, как показано
в следующем примере.
Пример 39. Выдать номера поставщиков из фирмы тур с максимальными поставками можно запросом
Select PN,Max(KOL) As [максимальные поставки]From PD
Group By PN Having PD.PN In
(Select PN From Post Where NFM =
(Select NFM From Firm Where FIM=′тур′));
Допускается до 255 уровней вложенности подзапросов.
Задание псевдонимов для таблиц
Таблицам, как и полям, можно задавать имена-псевдонимы. Задание псевдонимов для таблиц возможно двумя способами:
1) Как и для полей таблицы, ключевым словом As.
Пример 40. Выдать сведения о деталях, для которых существуют поставки можно запросом
Select D.DN As [номер детали],D.DIM As [имя детали],
D.CENA As [цена детали]
From Det As D -- D – псевдоним таблицы Det
Where Exists(Select *
From PD As A -- A – псевдоним таблицы PD
Where (D.DN = A.DN));
2) В предложении From, например:
Select * From Post P -- P – псевдоним для таблицы Post
Where Not Exists (Select PD.PN From PD Where (PD.PN = P.PN));
Псевдонимы можно использовать в любом месте только того запроса, в котором они определены.
110
Объединение запросов
Можно несколько независимых запросов объединить в один запрос. При этом объединяются и результаты выполнения этих запросов.
Синтаксис объединения запросов:
запрос_1 Union [All] запрос_2
[Union [All]
.........
запрос_n […]],
где запрос_1-запрос_n – операторы Select.
По умолчанию повторяющиеся записи исключаются из объединенного ответа. Однако, добавление предиката All гарантирует
возврат всех записей, включая и повторяющиеся записи. Кроме
того, такие запросы выполняются быстрее.
Все запросы, включающие операцию Union, должны содержать
одинаковое число полей в ответе. При этом типы данных и размеры
полей не обязаны совпадать, однако j-й столбец первой таблицы и j-й
столбец второй таблицы должны иметь одинаковый тип данных [1].
Пример 41. Выдать номера деталей, цена которых больше 100, а
также номера деталей, поставляемых в количестве более 90 штук.
Это можно сделать запросом:
Select Det.DN From Det Where Det.CENA > 100
UNION
Select PD.DN From PD Where PD.KOL > 90;
Такой же результат можно получить запросом
Select Distinct Det.DN From Det Left Outer Join PD
On Det.DN = PD.DN
Where((CENA > 100) Or (KOL >90));
В этом примере используется объединение таблиц Left Outer
Join потому, что некоторые детали из таблицы Det могут не поставляться, а при использовании вместо Left Outer Join объединения Inner Join не поставляемые детали будут отсутствовать
в ответе. Предикат Distinct исключает из ответа повторяющиеся
значения.
Пример 42. Выдать названия фирм, из которых поставляются
гайки, и сведения о поставщиках из Москвы можно запросом
Select Firm.FIM As имя, Null As телефон,
Null As город,Det.DIM As деталь
From Det Inner Join PD On Det.DN = PD.DN
Inner Join Post On PD.PN = Post.PN
Inner Join Firm On Post.NFM = Firm.NFM
111
Where (Det.DIM = ′гайка′)
Union
Select PIM,TEL,GOR,Null From Post Where (GOR = ′Москва′);
Ответ на этот запрос представлен на рис. 76.
Рис. 76. Результат объединения запросов
Количество полей в объединяемых запросах должно быть одинаковым. Если – разное, то надо сделать одинаковым, используя
ключевое слово Null, как это сделано в только что рассмотренном
запросе.
Используйте псевдонимы только в первом запросе, потому что
в последующих запросах псевдонимы игнорируются.
В программах на языке Transact-SQL могут использоваться операторы управления потоком, которые чаще всего применяются
в программах хранимых процедур и триггеров. Поэтому они будут
рассмотрены в разделах 5 и 7.
3.3.3. Задания для самостоятельного выполнения
Задание 1. Вывести без повторения имена поставляемых деталей.
Задание 2. Вывести для всех поставщиков из городов, начинающихся на букву С, следующие сведения: номер поставщика, его
имя, статус и город, в котором поставщик размещается.
Задание 3. Вывести сведения о поставках в заданном диапазоне дат.
Задание 4. Вывести все сведения о деталях, цена которых больше 100.
Задание 5. Вывести сведения о поставках в количестве от 100 до
400 штук.
Задание 6. Все поставки увеличились на 100 штук. Создать запрос, который смог бы отобразить эти изменения в ответе, который
112
должен содержать не только новые значения количества поставляемых деталей, но и указать старые значения.
Задание 7. Вывести сведения о поставщиках, номер которых больше трех и которые проживают в городах Москва, Самара, СПб.
Задание 8. Вывести сведения о поставщиках из городов Москва,
СПб или статус которых больше, чем 40.
Задание 9. Вывести всех поставщиков, имеющих номера телефонов, начинающиеся на (812), а следующая цифра 2 или 3.
Задание 10. Вывести сведения о поставщиках, имеющих номера
телефонов, начинающиеся на (812), а следующая цифра от 5 до 8.
Задание 11. Вывести сведения о поставщиках, имеющих номера
телефонов, начинающиеся на (8), и не содержащих цифру 1 в третьей позиции.
Задание 12. Сгруппировать данные по полю PN в таблице PD.
Вычислить количество записей в каждой группе и среднее количество поставляемых деталей для каждой группы поставщиков.
Задание 13. Используя группировку данных по городу, вывести
без повторений города, в которых размещаются поставщики.
Задание 14. Вычислить минимальные поставки каждого вида
деталей, для которых количество в группе превышает 1.
Задание 15. Вывести все сведения о поставках, упорядоченных
по возрастанию значений номеров деталей и по убыванию значений
номеров поставщиков, их поставляющих.
Задание 16. Вывести все сведения о 20% самых дешевых деталях. Из ответа исключить детали, для которых цена отсутствует,
т.е. имеет значение Null.
Задание 17. Вывести города, из которых поставляются болты.
Задание 18. Выдать сведения о поставщиках, не поставляющих
детали. Сохранить результат запроса в отдельной таблице.
Задание 19. Отобрать города, в которых размещаются поставщики, поставляющие болты, а также имена этих поставщиков, количество и дату поставки. Сохранить отобранные сведения в новой
таблице.
Задание 20. Добавить в таблицу, созданную в задании 19, сведения о поставщиках, поставляющих шайбы.
Задание 21. Добавить в таблицу, созданную в задании 19 нового
поставщика по имени Петров из города Киева, который поставил
ролики 28 декабря 2000 года в количестве 100 штук.
Задание 22. Отобрать названия всех фирм (в том числе и тех,
в которых нет поставщиков), имена и фамилии поставщиков, упорядочив результат по названию фирмы.
113
Задание 23. Удалить все сведения о поставщике с номером 5 и
все его поставки одним запросом.
Задание 24. Найти поставщиков, поставляющих детали в большем количестве, чем поставщики из фирмы выбор. Упорядочить
результат по убыванию значения поставок.
Задание 25. Используя подзапрос, найти все фирмы, в которых
нет поставщиков.
Задание 26. Получить список поставщиков, имеющих три самые высокие поставки. Вывести их имена и поставки.
Задание 27. Найти всех поставщиков, поставляющих большее
количество деталей, чем поставщик с номером 2.
Задание 28. Найти поставщика, для которого суммарное количество поставляемых деталей максимально.
Задание 29. Определить, в каком месяце было поставлено больше всего деталей.
Задание 30. Указать имена и города поставщиков, поставивших
детали в марте месяце.
Задание 31. Создать таблицу Spravka с полями:
KOD_PRED (код предприятия) – числовое или счетчик,
TIP_PRED (тип предприятия) – текстовое поле длины 20,
COMMENT (примечания) – поле примечания.
Объявить поле KOD_PRED первичным ключом.
Добавить в таблицу Post числовое поле KOD_PRED. Связать таблицы Post и Spravka по полю KOD_PRED.
Задание 32. Создать некластерный индекс по полю GOR в таблице Post. Убедиться в том, что индекс создан. Удалить только что
созданный индекс.
Задание 33. Создать запрос, печатающий звездочку напротив
имени детали, поставленной последней по времени.
Задание 34. Напечатать символ * напротив имени поставщика,
поставляющего максимальное количество деталей.
114
4. ПРЕДСТАВЛЕНИЯ
Представления (View) очень похожи на запросы на выборку
данных. Представление интерпретируется как таблица, с которой
можно делать все, что можно делать с таблицей, а именно:
• Строить запросы на добавление, обновление и удаление записей только из однотабличных представлений, т.е. таких, для которых источником представления является одна таблица. При этом
указанные операции автоматически выполняются и с таблицей-источником представления.
• Для всех представлений, как однотабличных, так и многотабличных строить запросы на выборку данных.
• Связывать представления с другими представлениями и таблицами.
Представления могут быть системные и пользовательские. Имя
системного преставления начинается с префикса sys.
Создавать пользовательские представления, как и запросы,
можно с помощью редактора запросов или операторами T-SQL.
Окно редактора запросов можно открыть в обозревателе объектов
сервера из контекстного меню командой ⇒ Представления/Создать представление….
Синтаксис оператора создания пользовательского представления:
Create View имя_представления
As
Оператор Select.
Примеры 43. Создание однотабличного представления, содержащего сведения о поставщиках из Москвы, может быть выполнено оператором:
Create View Post_GOR
As
Select * From Post Where GOR = ′Москва′;
Это представление удобно использовать для решения следующей задачи.
Создадим многотабличное представление с вычисляемым полем,
содержащее сведения о поставщиках из Москвы, поставляющих
гайки, а также информацию о старой и новой цене, увеличенной на
10%. Задачу можно решить с помощью следующего представления:
Create View [многотабличное с представлением Post_GOR]
As
Select Post_GOR.PIM,Post_GOR.GOR,DIM,CENA As[старая
цена],
115
KOL,CENA * 1.1 As[новая цена]
From Det Inner Join PD On Det.DN = PD.DN
Inner Join Post_GOR On PD.PN = Post_GOR.PN
Where DIM = N′гайка′;
Получить информацию из представления можно так же, как и
из таблицы, например, запросом
Select * From [многотабличное с представлением Post _ GOR];
Ответ может быть таким, например, как на рис. 77.
Рис.77. Результат выполнения запроса
Достоинства представлений:
– Представление может содержать только такие данные, к которым разрешен доступ конкретному пользователю.
– С представлением можно работать как с таблицей.
– Можно создавать представления, содержащие результаты вычислений.
– С помощью представлений легче обеспечивать защиту данных
от несанкционированных или ошибочных действий пользователей.
Задать разрешение на доступ к представлению намного проще и
эффективнее, чем задавать разрешения на доступ как к отдельным
столбцам таблицы, так и к отдельным таблицам в случае многотабличных представлений.
– Используя многотабличные представления, можно также
скрыть соединения в нем участвующих таблиц и упростить к этим
представлениям код SQL-запроса.
116
5. ХРАНИМЫЕ ПРОЦЕДУРЫ
5.1. Понятие хранимой процедуры
Хранимая процедура (Stored procedure) – это набор SQLоператоров и операторов управления потоком, которые компилируются и хранятся в базе данных на сервере.
Все, что можно сделать с помощью представления, можно реализовать и с помощью хранимой процедуры. Однако хранимые
процедуры обладают более широкими возможностями.
Преимущества хранимых процедур:
– Хранимые процедуры содержатся на сервере в уже откомпилированном виде, поэтому они выполняются быстрее SQL-операторов,
которые хранятся на стороне клиента. А потому регламентированные, т.е. постоянно выполняемые запросы рекомендуется реализовывать в виде хранимых процедур.
– В хранимые процедуры можно включать сложные операторы
управления потоком. Благодаря этому программный код клиентской части приложения упрощается.
– Организация доступа к данным таблиц посредством хранимых
процедур избавляет разработчика от необходимости предоставлять
пользователям разрешения на доступ к самим таблицам. Тем самым упрощается защита данных.
Хранимые процедуры бывают системные и пользовательские.
Имя системной процедуры всегда начинается с префикса sp, например sp_help…
Синтаксис оператора создания пользовательской хранимой процедуры:
Create Proc[edure] имя_процедуры
/* формальные параметры */
[@параметр1 тип _ данных [=значение по умолчанию] [Output]
[, @параметр2 тип _ данных [=значение по умолчанию] [Output]
[, …]
As
SQL-операторы.
Вызов хранимой процедуры осуществляется оператором:
Exec[ute] имя_процедуры [@список фактических
параметров].
Пример 44. Создать хранимую процедуру, с помощью которой
можно вывести сведения о детали, например, о гайке, причем деталь гайка задается в качестве параметра. Процедура будет такой:
117
Create Proc Det_Im
/* формальный параметр */
@im Char (15)
As
/* SQL-оператор */
Select * From Det Where DIM = @im;
Вызов процедуры осуществляется оператором:
Exec Det_Im ′гайка′;
Вызов процедуры можно написать иначе, указав имя параметра
@im:
Exec Det_Im @im =′гайка′;
При вызове процедуры оператором Exec Det_Im из adp-проекта
будет в диалоге запрошено значение фактического параметра. Возможности диалогового режима при вводе значений параметров вне
adp-проекта отсутствуют.
5.2. Операторы управления потоком
Язык SQL стандарта ANSI позволяет только работать с данными. Использование языка TRANSACT-SQL в SQL Server обеспечивает возможность программирования, от управления потоком команд до модульности.
Хранимые процедуры позволяют использование следующих
операторов управления потоком:
Begin … End – блок операторов (операторные скобки);
While – цикл, пока выполняется заданное условие;
Break – принудительный выход из цикла While;
Continue – переход к началу цикла While; 
Declare – описание локальной переменной;
Go To – переход по метке (пример метки: аdc23:);
If … Else – ветвление;
Print – вывод строки в окно запроса;
Set Nocount On/Off – разрешает/не разрешает вывод информации;
Exec[ute] – вызов хранимой процедуры;
Return – прекращает выполнение хранимой процедуры;
Waitfor – приостанавливает выполнение хранимой процедуры
на определенное время (Waitfor Delay ′00: 00: 05′, что означает на
пять секунд) или до определенного времени (Waitfor Time ′17: 00:
05′, что означает до 17 часов пяти секунд).
118
5.3. Примеры хранимых процедур
Пример 45. Использование в процедуре ветвления.
Создать хранимую процедуру, которая выводит слово успех,
если в таблице Det содержится точно один раз деталь с заданным
именем (например, гайка), и неудача – иначе (т.е. такой детали
в таблице Det нет или она встречается более одного раза).
Create Proc zad_im_1
/* формальный параметр */
@im Char (15)
As
If (Select Count(*) From Det Where DIM = @im)=1
Begin
/* успех*/
Print ′успех′
Return
End
Else
Begin
/* неудача*/
Print ′неудача′
Return
End;
Оператор Select после If должен быть заключен в круглые
скобки.
Вызов хранимой процедуры осуществляется оператором
Exec zad_im_1 @im = ′гайка′;
Видоизменим только что описанную процедуру так, чтобы заданное параметром имя детали встречалось бы в таблице Det не
один раз, а заданное количество. Для этого определим в процедуре
еще один формальный параметр @z, с помощью которого и будем
задавать это количество. Процедура будет такой:
Create Proc Im_zad_kol
/* формальные параметры */
@dim Nvarchar (20), @z Int
As
If (Select Count(*) From Det Where DIM = @dim)= @z
Begin
Print @dim
Print ′встречается точно′
Print @z
119
Print ′раз′
Return
End
Else
Begin
Print @dim
Print ′не встречается ни разу′
Print ′или встречается более или менее′
Print @z
Print ′раз′
Return
End;
Вызов хранимой процедуры осуществляется оператором
Exec Im_zad_kol ′гайка′,3; или так:
Exec Im_zad_kol @dim =′гайка′, @z = 3;
Особенности использования оператора Print.
Оператор Print выводит значения символьного типа данных.
При выводе значений других типов надо преобразовать их в символьный тип. Кроме того, каждый новый оператор Print начинает
вывод с новой строки. Так серия операторов Print в только что описанной процедуре, выведет информацию в виде нескольких строк.
Чтобы вывести информацию в виде одной строки, можно в процедуре параметр @z описать как символьный тип, например, @z
Char(3) и в операторе Print использовать операцию склеивания
(конкатенации) строк, обозначаемую символом «+», например, так:
Print @dim+′ встречается точно ′+@z+′ раз′.
Тогда информация будет выведена в виде одной строки, например:
«гайка встречается точно 5 раз» или так:
Print @dim+′ не встречается ни разу′+
′ или встречается более или′+
′ менее ′ + @z+′раз′.
Если параметр @z описан как Int, то можно использовать функцию Convert(), с помощью которой преобразовать этот параметр
в символьный тип, например, так:
Print @dim+′ встречается точно ′+ Convert(Char(3),@z)+′ раз′
и
Print @dim+′не встречается ни разу или встречается′+
′более или ′+ ′менее ′ + Convert(Char(3),@z)+′ раз′.
Пример 46. Использование в процедуре цикла.
Будем увеличивать цену деталей до тех пор, пока средняя цена
деталей не достигнет 300 денежных единиц.
120
Для решения этой задачи создадим хранимую процедуру без параметров следующего вида:
Create Proc Incr_AvgCena
As
Set Nocount On -- разрешение вывода
/*описание локальной переменной */
Declare @oldAvg Money,@newAvg Money
/*вычисляем и сохраняем старую среднюю цену */
Select @oldAvg = (Select Avg(CENA)From Det)
While (Select Avg(CENA) From Det)< 300
Begin
Update Det
Set CENA = CENA + 10 -- 10 – шаг увеличения цены
End
/* вычисление новой средней цены */
Select @newAvg = (Select Avg(CENA)From Det)
/*возвращаем старую и новую среднюю цену */
Select @oldAvg As старая_ср_цена, @newAvg As
новая_ср_цена;
Вызов процедуры выполняется оператором
Exec Incr_AvgCena;
В результате будет выведена старая и новая средняя цена, например, как показано на рис. 78.
Рис. 78. Результат выполнения процедуры
Incr_AvgCena
Пример 47. Использование в процедуре цикла
Создать хранимую процедуру, с помощью которой можно довести количество болтов и шайб на складе в таблице Det до количества гаек по цене 100 денежных единиц в предположении, что
в таблице содержится только один болт и только одна шайба, а гаек
может быть несколько.
Create Proc Incr_kol
As
Set Nocount On -- разрешение вывода
/*описание локальных переменных */
Declare @kol_gaika_100 Int,
@newkol_bolt Int,
121
@newkol_shaiba Int
/*вычисляем и сохраняем количество гаек по цене 100 */
Select @kol_gaika_100 = (Select KOL_SKLAD From Det
Where ((DIM = ′гайка′) And (CENA = 100)))
/* цикл увеличения количества болтов */
While(Select KOL_SKLAD From Det
Where DIM = ′болт′)< @kol_gaika_100
Begin
Update Det
Set KOL_SKLAD = KOL_SKLAD+10 -- 10 – шаг увеличения
Where DIM = ′болт′
End
/* цикл увеличения количества шайб */
While(Select KOL_SKLAD From Det
Where DIM = ′шайба′)< @kol_gaika_100
Begin
Update Det
Set KOL_SKLAD = KOL_SKLAD+10 -- 10 – шаг увеличения
Where DIM = ′шайба′
End
/* Формирование данных для контроля */
Select @newkol_bolt=(Select KOL_SKLAD From Det
Where DIM = ′болт′)
Select @newkol_shaiba =(Select KOL_SKLAD From Det
Where DIM = ′шайба′)
/* вывод результатов */
Select @kol_gaika_100 As колич_гаек_по_цене_100,
@newkol_bolt As новое_колич_болтов,
@newkol_shaiba As новое_колич_шайб;
Вызов процедуры осуществляется оператором:
Exec Incr_kol;
Пример 48. Создать хранимую процедуру, с помощью которой
будет добавлена в родительскую таблицу Det новая запись. Первичный ключ таблицы Det – счетчик.
Create Proc Ins_Det
/* Формальные параметры */
@Dim nVarchar(50),
@Cena Money
As
/* Описание локальной переменной @newDN */
Declare @newDN Int
122
/* Разрешение на возвращение значения переменной @newDN */
Set Nocount On
/* Добавление записи в таблицу Det */
Insert Into Det(DIM,CENA) Values(@Dim, @Cena)
/* Присвоение переменной @newDN значения поля DN из
только что введенной записи */
Set @newDN = (Select Max(DN) From Det)
/* Вывод значения переменной @newDIM */
Select @newDN As [Новый номер детали]
Select Max(DN),DIM,CENA From Det;
Вызов хранимой процедуры осуществляется оператором:
Exec Ins_Det ′шайба′,250;
Пример 49. Создать хранимую процедуру, с помощью которой
будет добавлено по одной записи в родительские таблицы Post и
Det и две связанные с ними записи в дочернюю таблицу PD.
Create Proc Post_Det_PD_2
/* Формальные параметры, причем первичные ключи PN
и DN в родительских таблицах – счетчики */
@pim nVarchar(20),
@gor nVarchar(20),
@NFM Int,
@dim nVarchar(20),
@cena Money,
@kol Int,
@dat Date
As
/* Объявляем временные локальные переменные */
Declare @newPN Int, @newDN Int, @newDN1 Int
/* Добавляем запись в родительскую таблицу Post */
Insert Into Post(PIM, GOR,NFM)
Values(@pim, @gor, @NFM)
/* Сохраняем значение PN только что добавленной записи
в переменной @newPN */
Select @newPN = PN From Post
/* Добавляем запись в родительскую таблицу Det */
Insert Into Det(DIM, CENA)
Values(@dim, @cena)
/* Сохраняем значение DN только что добавленной записи
в переменной @newDN */
Select @newDN = DN From Det
/* Добавляем первую запись в дочернюю таблицу */
123
Insert Into PD(PN,DN,KOL,DATA)
Values(@newPN, @newDN, @kol,@dat)
/* Задаем новое количество для второй записи, которая будет добавлена ниже в дочернюю таблицу PD. Можно было оставить такое же количество, как и в первой записи */
Set @kol = @kol+10
/* Для второй добавляемой записи в поле PN можно добавить значение из переменной @newPN, но тогда в поле DN уже
нельзя добавлять значение из переменной @newDN, так как
тогда в дочерней таблице появилась бы запись с дубликатным
значением первичного ключа, что недопустимо. Тогда нужно
добавить в поле DN другое значение. Проще всего это значение
взять из первой записи родительской таблицы Det. Найдем это
значение */
Select Top 1 @newDN1=DN From Det Order By DN
/* Добавляем в дочернюю таблицу PD вторую запись */
Insert Into PD(PN,DN,KOL,DATA)
Values(@newPN, @newDN1,@kol,@dat);
Вызов хранимой процедуры осуществляется оператором:
Exec Post_Det_PD_2 ′Смирнов′, ′СПб′, 5,′шайба′,55,500,
′12.05.2016′;
Пример 50. Создать процедуру, которая выясняет, имеются ли
в базе данных сведения о конкретном поставщике. Если – имеются,
то выдать об этом сообщение, если – не имеются, то занести сведения об этом поставщике в базу данных.
Запрашиваемые сведения о поставщике (имя, город, фирма, телефон) содержатся в разных таблицах (Firm и Post), поэтому удобно предварительно создать многотабличное представление, которое
затем использовать в процедуре. Представление будет таким:
Create View для_proc_proverka_ex_post
As
Select PIM, GOR, FIM, TEL
From Firm Inner Join Post On Firm.NFM = Post.NFM;
Поставленную задачу можно решить с помощью процедуры
Create Proc proverka_ex_post
/* формальные параметры */
@pim nVarchar (50),
@gor nVarchar (50),
@fim Nvarchar (50),
@tel nVarchar (15)
As
124
/* Проверка наличия сведений о поставщике */
If Exists(Select * From для_proc_proverka_ex_post
Where (PIM = @pim) And (GOR = @gor) And (FIM = @fim)
And (TEL = @tel))
Begin
Print ′Такой поставщик уже есть›
Return
End
Else
Begin
Print ′Такого поставщика нет′
/* Вызов процедуры dobav_post */
Exec dobav_post @pim,@gor,@fim,@tel
Print ′Сведения о поставщике внесены в базу данных′
Return
End;
Процедура dobav_post будет такой:
Create Proc dobav_post
/* формальные параметры */
@pim nVarchar(50),
@gor nVarchar(50),
@fim nVarchar(50),
@tel nVarchar(15)
As
Declare @nn Int
/* сохранение номера фирмы в переменной @nn */
Set @nn = (Select NFM From Firm Where FIM = @fim)
Insert Into Post(PIM,GOR,TEL,NFM)
values(@pim,@gor,@tel,@nn);
Вызов процедуры Proc proverka_ex_post, например, такой:
Exec proverka_ex_post ′Сидоров′,′Москва′,′факел′,′5555′;
решает поставленную задачу.
5.4. Вывод результатов выполнения
хранимой процедуры
Вывод результатов выполнения хранимой процедуры может
осуществляться двумя способами:
– в теле процедуры,
– при вызове процедуры.
125
Первый способ реализуется проще, однако он не является универсальным, так как при изменении формата выводимых данных
требует модификации процедуры.
Второй способ часто реализуется сложнее, но он является универсальным и при изменении формата выводимых данных не требует модификации процедуры.
Рассмотрим особенности реализации обоих способов.
Пример 51. Пусть содержимое таблиц Post и Firm будет таким,
как показано на рис. 79 и рис. 80.
Создадим хранимую процедуру для вывода фирм, которые представляют поставщики с именем, например, Петров. Вывод результатов выполним:
• в теле процедуры,
• при вызове процедуры.
Вывод результатов в теле процедуры (номер фирмы NFM – счетчик).
Для решения поставленной задачи укажем два способа реализации хранимой процедуры:
1. Без использования переменной табличного типа Table:
Create Proc my_proc_v_tele
/* Формальный параметр */
@pim nVarchar(20)
As
Select NFM As [номер фирмы], FIM As [название фирмы]
From Firm Where Firm.NFM In(Select Post.NFM
From Post Where(Firm.NFM = Post.NFM) And (PIM = @pim));
При вызове процедуры оператором:
Exec my_proc_v_tele ′Петров′;
получим результат, представленный на рис. 81 (слева).
Рис. 79. Содержимое таблицы Post 
126
Рис. 80. Содержимое
таблицы Firm
Рис. 81. Вывод результатов в теле процедуры (слева)
и при вызове процедуры (справа)
2. С использованием переменной @t табличного типа Table:
Create Proc my_proc_pri_vizove_table
/* Формальный параметр */
@pim nVarchar(50)
As
/* Описание переменной табличного типа */
Declare @t Table([номер фирмы] Int,фирма nVarchar(50))
/* Заполнение переменной @t значениями */
Select [номер фирмы] = NFM, фирма = FIM
From Firm Where Firm.NFM In(Select Post.NFM
From Post Where(Firm.NFM = Post.NFM) And
(PIM = @pim));
Вызвав процедуру оператором
Exec my_proc_pri_vizove_table ′Петров′;
Получим такой же результат, как в первом случае.
Вывод результатов при вызове процедуры
Немного изменим эту процедуру, добавив в нее еще два формальных параметра @nfm и @fim, которые опишем как Output (выходные параметры). Это дает возможность воспользоваться последними значениями этих параметров за пределами процедуры. Тогда
процедура будет такой:
Create Proc my_proc_pri_vizove
/* Формальные параметры */
@pim nVarchar(50),
@nfm Int Output,
@fim nVarchar(50) Output
As
Select @nfm = NFM, @fim = FIM
From Firm Where Firm.NFM In(Select Post.NFM
From Post Where(Firm.NFM = Post.NFM) And (PIM = @pim));
В этом случае при вызове процедуры нужно использовать следующую группу операторов, выполняя которые получим ответ, показанный на рис. 81(справа):
127
Declare @nfm Int, @fim nVarchar(50) 
Exec my_proc_pri_vizove ′Петров′,@nfm Output,
@fim Output
Select @nfm As [номер фирмы],@fim As [название фирмы];
Действительно, выведены только последние значения выходных
параметров в виде одной строки, а предыдущие значения потеряны.
Чтобы вывести все строки результата за пределами процедуры,
как на рис. 81 (слева), можно воспользоваться, по крайней мере,
двумя способами:
1) применив механизм курсора,
2) создав вместо процедуры пользовательскую функцию, которая возвращает значение табличного типа.
Ниже рассмотрим первый способ, а второй способ будет описан
в примере 62 раздела 8.
Механизм курсора поддерживает несколько функций. Например, он может предоставлять клиенту не весь набор данных сразу,
а порциями, или позволяет выбрать конкретную запись из набора
данных.
При работе с курсором можно выделить следующие действия:
– создание (объявление) курсора (Declare),
– открытие курсора (Open),
– выборка из курсора и изменение с его помощью строк данных
(Fetch),
– закрытие курсора, т.е. удаление его как объекта (Close).
Процедура с использованием механизма курсора может быть такой:
Create Proc my_proc_cursor
/* Формальные параметры */
@pim nVarchar(50),
@result cursor varying output
As
Set @result = cursor static for
Select NFM,FIM From Firm
Where NFM In(Select NFM From Post
Where(Firm.NFM = Post.NFM) And (PIM = @pim))
/* открыть набор */
Open @result;
В этой процедуре использован статический курсор (static).
Он делает моментальный снимок набора записей, которые можно
только читать и которые сохраняются во временной базе данных
Tempdb. При работе с курсором данные извлекаются из нее.
128
Вызов хранимой процедуры осуществляется с помощью набора
операторов:
Declare @curs cursor,@nfm Int,@fim nVarchar(50)
Exec my_proc_cursor ′Петров′, @curs Output
-- считывается первая строка набора записей
Fetch First From @curs Into @nfm, @fim
Select @nfm As[номер фирмы], @fim As[название фирмы]
While @@Fetch_Status = 0
Begin
/* считываются следующие строки набора записей */
Fetch @curs Into @nfm, @fim
Select @nfm As[номер фирмы],@fim
As[название фирмы]
End;
Имя временной переменной @curs может быть другим.
Функция @@Fetch_Status возвращает результат выполнения
последней команды Fetch. Если она возвращает 0 (ноль), то последняя операция была выполнена успешно. Если функция возвращает
–1, то была предпринята попытка выборки строки, находящейся
за пределами результирующего набора. Если функция возвращает
–2, то была попытка выборки поврежденной строки.
В результате выполнения процедуры будут выведены все записи
для Петрова, как на рис. 81 (слева).
В теле процедуры допускаются вызовы других процедур, а также рекурсивные вызовы процедуры самой себя. Поэтому набор
операторов вызова процедуры my_proc_cursor можно, в свою очередь, оформить в виде процедуры, в теле которой будет вызов процедуры my_proc_cursor:
Create Proc my_proc_visov_my_proc_cursor
/* Формальный параметр */
@pim nVarchar(50)
As
-- описание локальных переменных
Declare @curs cursor,@nfm Int,@fim nVarchar(20)
-- вызов процедуры my_proc_cursor
Exec my_proc_cursor @pim, @curs Output
-- считывается первая строка набора записей
Fetch First From @curs Into @nfm, @fim
Select @nfm As[номер фирмы],
@fim As[название фирмы]
While @@Fetch_Status = 0
129
Begin
/* считываются следующие строки
набора записей */
Fetch @curs Into @nfm, @fim
Select @nfm As[номер фирмы],
@fim As[название фирмы]
End;
Тогда, вызвав процедуру my_proc_visov_my_proc_cursor
оператором
Exec my_proc_visov_my_proc_cursor ′Петров′;
получим ответ такой же, как на рис. 81 (слева), но несколько
в другой форме.
130
6. ТРАНЗАКЦИИ
Транзакция – это процесс, в котором или выполняются все операторы, или не выполняется ни один оператор. Примером классической транзакции является перевод определенной суммы денег
с одного счета на другой. Эта операция состоит из двух частей:
– уменьшение счета, с которого снимаются деньги,
– увеличение счета, на который переводятся деньги.
Транзакции должны обладать следующими свойствами [4], [15]:
– Элементарность (Atomic), т.е. неделимость на отдельные части. Транзакция или выполняется полностью, или не выполняется
ни один ее оператор.
– Целостность (Consistent), т.е. выполнение транзакции не может нарушить целостность базы данных как в случае успешного,
так и аварийного завершения транзакции.
– Изолированность (Isolated), т.е. транзакция не конфликтует
и не взаимодействует с другими транзакциями.
– Надежность (Durable), т.е. выполнение требуемых действий
гарантируется независимо от внешних событий. Если неожиданно
выйдет из строя линия связи, отключится питание, или даже разрушится сервер, все равно после повторной загрузки сервера база
данных будет в целостном состоянии.
Для работы с транзакциями используются следующие ключевые слова:
– Begin Tran[saction] – начало транзакции,
– Commit Tran – успешное завершение транзакции,
– Rollback Tran – аварийное завершение (откат) транзакции,
– Save Tran – создание точки сохранения транзакции.
Транзакции могут быть трех типов:
– явные,
– автоматически фиксируемые,
– неявные.
Явные транзакции определяются в программе напрямую. Например,
Begin Tran -- имя транзакции задавать необязательно
Update …..
Insert ……
Select …..
Commit Tran
Пример 52. Создадим явную транзакцию для удаления из таблицы Post сведений о поставщике с номером 3.
131
Begin Tran
Delete Post Where PN = 3
If @@Error > 0
Rollback Tran -- откат транзакции
Else
Commit Tran -- успешное завершение транзакции;
Здесь использована глобальная переменная @@Error, которая
при возникновении ошибки принимает значение > 0 (больше нуля).
Внутри транзакции можно создать точку сохранения. В этом
случае отменяются (откатываются) только те операторы, которые
должны быть выполнены после нее.
Пример 53. Пример транзакции с точкой сохранения:
Begin Tran
Update table1 Set col1 = 5 Where col2 = 14
Save Tran savepoint1 -- точка сохранения
Insert table2 Values(3,16)
If @@Error >0
/* откат всего после точки сохранения */
Rollback Tran savepoint1
Delete table3 Where col1 >2
If @@Error >0
Rollback Tran -- откат транзакции
Else
Commit Tran -- успешное завершение транзакции;
Внутри одной транзакции может присутствовать несколько точек сохранения с одним и тем же именем, однако фактически при
этом используется только одна, а именно – последняя. Предыдущие точки сохранения игнорируются.
В явной транзакции запрещено совместное использование операторов:
Alter Database,Drop Database,Create Database
и некоторых других аналогичных операторов.
Автоматически фиксируемые транзакции не содержат в явном
виде операторы транзакции, но, тем не менее, выполняются как
транзакции, и информация о них фиксируется в файлах журнала транзакций. По умолчанию любое изменение данных сервером
рассматривается как транзакции. Автоматически фиксируемыми
транзакциями считаются отдельно выполняемые операторы:
........
Insert
Update
132
Delete
Go
.......
Каждый из перечисленных операторов является автоматически
фиксируемой транзакцией, которая сопровождает запись в журнале транзакций. Тогда при выполнении перечисленных трех операторов информация в журнал транзакции запишется три раза. При
этом замедлится работа приложения. Поэтому, если требуется выполнить последовательно несколько операторов, являющихся автоматически фиксируемыми транзакциями, лучше их оформлять
в виде явной транзакции. В этом случае информация о транзакции
запишется в журнал транзакций один раз.
Неявные транзакции включены в язык запросов T-SQL для совместимости со стандартом ANSI-SQL. Если обеспечивать совместимость не требуется, то использовать неявные транзакции не рекомендуется.
Более подробную информацию о транзакциях можно найти в работах [1–5], [15] и других.
Транзакции часто используются в триггерах.
133
7. ТРИГГЕРЫ
Триггер – это особый вид хранимой процедуры, которая выполняется при наступлении некоторого события. Чаще всего такими
событиями являются:
– добавление новых записей в таблицу (Insert),
– удаление ненужных записей из таблицы (Delete),
– обновление полей записей (Update).
Триггер нельзя запустить вручную.
Триггер можно связать с одним или несколькими событиями.
Типы триггеров:
– After – триггер по умолчанию, который выполняется после
внесения изменений в таблицу. При обнаружении ошибки изменения могут быть отменены с помощью оператора отката транзакции
Rollback.
– Instead Of – выполняется перед внесением изменений в таблицу. Триггер «на лету» анализирует корректность вносимых изменений, а затем осуществляет или нет эти изменения.
Триггеры обычно используются для следующих целей [1]:
– если необходимо выполнить каскадное изменение через связанные таблицы в базе данных,
– если база данных денормализована и требуется способ автоматического обновления данных в нескольких таблицах,
– если необходимо сверить значение в одной таблице с неидентичным значением в другой таблице,
– если требуется вывод пользовательских сообщений и сложная
обработка ошибок.
Синтаксис оператора создания триггера:
Create Trigger имя_триггера
On имя_таблицы | имя_представления
For{After | Instead Of}
{Insert|Update|Delete}
[,…]
[With Encryption]
As
[Операторы SQL].
Здесь предложение With Encryption означает шифрование исходного кода триггера.
Триггер воспринимается сервером как неявная транзакция. Поэтому в триггере можно использовать операторы, связанные с транзакциями.
134
При создании триггеров необходимо придерживаться следующих правил:
– Нельзя создавать триггеры для временных таблиц. Но в процессе работы триггеры могут обращаться к представлениям и временным таблицам.
– Триггеры не могут возвращать динамические наборы. Поэтому использовать в триггере оператор Select нужно крайне осторожно. Обычно оператор Select в триггере используется с конструкцией If Exists.
– Триггеры рекомендуется использовать для поддержания целостности данных, ссылочной целостности и проверочных ограничений.
– В триггерах нельзя использовать операторы: Create, Drop,
Alter, Select Into и некоторые другие операторы.
– Каждая таблица может иметь несколько триггеров.
Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их применение часто связано с дополнительными затратами ресурсов. В том случае, когда тех же результатов с гораздо меньшими затратами ресурсов можно добиться
с помощью хранимых процедур или прикладных программ, то применение триггеров нецелесообразно.
Каждый триггер привязывается к конкретной таблице. Все производимые им модификации данных рассматриваются как одна
транзакция. При обнаружении ошибки или нарушении целостности данных происходит откат транзакции. Тем самым внесение
изменений запрещается. Отменяются также все изменения, уже
сделанные триггером.
Триггер создает только владелец базы данных. Это позволяет
избежать случайного изменения структуры таблиц, способов связи
с ними других объектов и пр.
Триггер является полезным и в то же время опасным средством.
Так, при неправильной логике его работы можно легко уничтожить целую базу данных, поэтому триггеры необходимо тщательно
отлаживать.
В отличие от обычной процедуры, триггер выполняется неявно
в каждом случае возникновения триггерного события. К тому же
триггер, в отличие от хранимой процедуры, не может иметь параметров.
Иногда триггер может «мешать» выполнять какие-то действия
с записями таблицы. Тогда его можно отключить оператором
Disable Trigger, а затем снова подключить оператором Enable
Trigger. При этом используется следующий синтаксис:
135
Alter Table имя_таблицы {Disable|Enable}Trigger имя_
триггера.
Например, пусть мы создали триггер по имени Det_Ins, который запускается при добавлении новой записи в таблицу Det (триггер будет дан ниже). Отключить этот триггер от таблицы Det можно оператором
Alter Table Det Disable Trigger Det_Ins;
и снова подключить оператором
Alter Table Det Enable Trigger Det_Ins;
Отключить все триггеры, связанные с таблицей Det можно оператором:
Alter Table Det Disable Trigger All;
Модификация триггера осуществляется оператором
Alter Trigger имя_триггера.
Для удаления триггера используется оператор
Drop Trigger ′имя_триггера′ [,...n].
Пример 54. Создадим триггер для таблицы Det, который при
добавлении записи в таблицу новой детали проверял, есть ли уже
в таблице сведения об этой детали или нет. Если – есть, то добавление не разрешается, а если – нет, то добавление разрешается.
Create Trigger Det_Ins
On Det For Insert
As
Begin Tran
/* описание временных переменных */
Declare @dim nVarchar(20),@nomer Int, @kol Int, @cena Money
/* Выборка вводимого имени и номера поставщика из временно
создаваемой таблицы inserted, которой дается псевдоним i и сохранение этих данных в переменных @dim и @nomer */
Select @dim = i.DIM From inserted i
Select @cena = i.CENA From inserted i
Select @kol = i.KOL_SKLAD From inserted i
Select @nomer = i.DN From inserted i
/* Если вводимое имя,цена детали и количество на складе
уже есть в таблице, и если номер детали в таблице не совпадает с номером вводимой детали, то такая деталь уже есть в таблице */
If Exists(Select DN,DIM,CENA,KOL_SKLAD From Det
Where ((Det.DIM=@dim) And ((CENA= @cena) or (CENA Is Null))
And ((Det.KOL_SKLAD = @kol) or (Det.KOL_SKLAD
Is Null))
136
and (Det.DN <> @nomer))) 
Begin
Rollback Tran --откат транзакции
Print ′такая деталь уже есть в таблице′
End
Else
Begin
/* Успешное завершение транзакции
и добавление новой детали в таблицу Det */
Commit Tran
Print ′вставка детали завершена успешно′
End;
Замечание по триггеру
В условии отбора записей (предложение Where) для полей CENA
и KOL_SKLAD, в которых разрешены значения Null, требуется наряду с условием And добавить еще условие Or, с помощью которого
анализируется наличие значений Null в этих полях. Причем скобки должны быть расставлены в соответствии с приоритетом выполнения логических операций (сначала выполняется операция And,
затем Or).
Созданный триггер сработает при выполнении запроса на добавление, например:
Insert Into Det(DIM,CENA,KOL_SKLAD)
Values(′шайба′,55,Null);
или просто при добавлении с клавиатуры новой записи в таблицу Det.
Если такая деталь в таблице уже есть, то триггер не разрешит
добавить деталь в таблицу, иначе добавление будет успешно выполнено.
Замечания
При выполнении операций Insert,Update,Delete сервер создает в памяти две таблицы:
– inserted при выполнении операции Insert,
– deleted при выполнении операции Delete,
– обе таблицы(inserted и deleted)при выполнении операции
Update.
Эти таблицы имеют такую же структуру с такими же названиями
полей, что и базовые таблицы, над которыми выполняются эти операции. Причем создаваемые сервером таблицы хранят копии строки только что введенной (при Insert) или удаляемой (при Delete).
137
При выполнении операции Update создаются две таблицы, так как
при обновлении полей записи сначала выполняется удаление старого значения, а затем добавление нового значения поля.
Для каждого триггера создается свой комплект таблиц inserted
и deleted, поэтому никакой другой триггер не сможет получить
к ним доступ.
Триггеры можно использовать для поддержания ссылочной целостности данных.
Пример 55. Создать триггер с целью поддержания ссылочной
целостности данных. Для этого в базе данных postavka (рис. 6)
удалим связь между родительской таблицей Post и дочерней таблицей PD. Попытаемся удалить запись из родительской таблицы
Post. Это можно сделать, если в дочерней таблице PD не останется
связанных записей. Если останутся связанные записи («сироты»),
то такое удаление недопустимо. Эту ситуацию и должен проконтролировать триггер, который будет давать или не давать разрешение
на удаление записи из родительской таблицы.
Create Trigger Post_PD_Del
On Post For Delete
As
Begin Tran
If Exists(Select * From deleted, PD
Where PD.PN = deleted.PN)
Begin
-- откат транзакции
Print ′нельзя удалить поставщика′
Rollback Tran
End
Else
Begin
-- успешное завершение транзакции
Print ′поставщик успешно удален′
Commit Tran
End;
При выполнении запроса
Delete From Post Where PN = 2;
сработает триггер. Если при удалении поставщика с номером 2
в таблице PD останутся записи-сироты, то произойдет откат транзакции с выводом соответствующей информации. Если – не останется записей-сирот, то триггер разрешит удаление и транзакция
успешно завершится.
138
Пример 56. Создать триггер, который разрешит добавление записи в дочернюю таблицу PD, если для нее в родительских таблицах Post и Det окажутся связанные с ней записи-родители, и не
разрешит – иначе.
Create Trigger PD_Ins
On PD For Insert
As
If Exists(Select * From inserted i, Post
Where Post.PN = i.PN) And
Exists (Select * From inserted i, Det
Where Det.DN = i.DN)
Print ′вставка успешно завершена′
Else
Begin
If Not Exists(Select * From inserted i, Post
Where Post.PN = i.PN)
Print ′вставка невозможна, нет поставщика′
If Not Exists (Select * From inserted i, Det
Where Det.DN = i.DN)
Print ′вставка невозможна, нет такой детали′
End;
Удалим связи таблиц Post и Det с таблицей PD.
Запрос
Insert Into PD
Values(14,3,55,′12/12/2015′);
выполнит вставку указанной записи в таблицу PD, если в таблице Post есть поставщик с номером 14 и в таблице Det есть деталь
с номером 3. Иначе триггер не разрешит выполнить вставку.
Пример 57. Создать триггер, позволяющий отслеживать изменение цены детали в таблице Det. Для этого добавим в базу данных
postavka таблицу T_History с полями: DN – номер детали, цена
которой изменилась,
Date_Price – дата изменения цены,
Price – старая цена, которая изменилась.
Если цена детали изменилась, триггер разрешает в момент изменения цены детали сохранить в таблице T_History деталь со
старой ценой. Например, по запросу
Update Det Set CENA = 500 Where (DN = 6);
детали с номером 6 назначается новая цена 500 единиц (старая
цена была, например, 295), и деталь с номером 6 со старой ценой
(295) сохраняется в таблице T_History.
139
Если же новая цена детали назначается равной старой цене (например, ошибочно), то выдается сообщение о том, что цена не изменилась, и деталь не сохраняется в таблице T_History. Для решения поставленной задачи нужно создать триггер:
Create Trigger tr_history
On Det
For Update
As
Begin Tran
Declare @PriceO Money, @DN Int,@PriceN Money
/* Здесь в переменной @PriceO сохраняется старая цена,
а в @PriceN – новая цена */
-- Сохраняем старую цену детали в переменной @PriceO
Select @DN=D.DN, @PriceO=D.CENA
From deleted D Inner Join Det T On D.DN=T.DN
-- Сохраняем новую цену детали в переменной @PriceN
Select @DN=I.DN, @PriceN=I.CENA
From inserted I Inner Join Det T On I.DN=T.DN
-- Проверяем, изменилась ли цена детали с заданным номером?
If @PriceO = @PriceN
Begin
Rollback Tran --откат транзакции
Print ′цена не изменилась′
End
Else
Begin
/* Успешное завершение транзакции и добавление детали с новой ценой в таблицу T_History */
Commit Tran
Print ′цена детали изменилась′
Print ′вставка детали в T _ History завершена успешно′
Insert Into T_History (DN, Date_price, Price)
Values (@DN, Getdate(),@PriceO)
End;
Пример 58. Создать триггер, который при добавлении поставки
в таблицу PD обеспечивает увеличение количества соответствующих деталей на складе в таблице Det.
Create Trigger ins_Det_Incr
On PD For Insert
As
140
Declare @DN Int, @kol Int, @DATA Date
-- Выбираем код детали, количество и дату
Select @kol=I.KOL, @DN=I.DN, @DATA=I.DATA
From inserted I
Inner Join Det T On I.DN = T.DN
Update Det
Set KOL_SKLAD=KOL_SKLAD + @kol
Where DN=@DN;
Пусть на складе находится 200 деталей с номером DN =1. Эта
же деталь сегодня бала поставлена поставлена поставщиком PN =1
в количестве 30 штук по запросу
Insert Into PD (PN, DN, KOL, DATA)
Values (1, 1, 30, Getdate());
Сработает триггер ins_Det_Incr, который обеспечит увеличение количества деталей на складе с номером DN =1 до 230 штук и
разрешит добавить эту поставку в таблицу PD.
Как было сказано выше, использование триггеров часто связано с дополнительными затратами ресурсов на операции ввода/
вывода (запись/перезапись в журналы транзакций). В том случае,
когда тех же результатов с гораздо меньшими затратами ресурсов
можно добиться с помощью хранимых процедур или прикладных
программ, применение триггеров нецелесообразно. По-видимому,
в примерах 57 и 58 вместо триггеров можно было использовать хранимые процедуры. Попробуйте это сделать самостоятельно.
141
8. ФУНКЦИИ
Функции могут быть встроенными (стандартными) и созданными пользователями (пользовательскими).
Приведем примеры использования некоторых часто употребляемых встроенных функций.
Функция Getdate() – определяет сегодняшнюю дату;
Функция Convert() – выполняет преобразование используемого типа данных в строковый (символьный) тип. Иногда сервер сам
выполняет такое преобразование.
Синтаксис функции:
Convert (Тип_данных_в_который_преобразуется, Преобразуемый_объект (имя или литерал), стиль);
Стиль указывает на внешний вид отображаемых данных.
Как можно использовать функцию Convert в операторе Print,
было сказано ранее в примере 45. Рассмотрим еще один пример
с использованием этой функции.
Пример 59. Выдать информацию о номере и цене детали. Преобразовать цену детали в строку из 10 символов, к которой в начале
строки добавить символ доллара. Это можно сделать запросом:
Select DN, ′S′ + Convert (Varchar(10), CENA)
As цена
From Det;
Преобразовать целое число 22 в строку символов можно запросом:
Select Convert (Varchar(2), 22);
Вместо функции Convert можно использовать встроенную
функцию Cast, которая имеет меньше параметров (стиль не указывается).
Функция Stuff() – позволяет форматировать строковые значения. Синтаксис функции:
Stuff (Строка, в которой будет произведена замена; начало;
число удаляемых символов; подставляемое в строку значение).
Рассмотрим пример на использование этой функции.
Пример 60. Добавить в начало каждой строки динамического
набора данных из таблицы Post слово номер можно запросом:
Select Stuff (PN, 1, 0, ′номер′) As PN
From Post;
В результате выполнения этого запроса получится динамический набор данных в виде:
PN
номер 1
142
номер 2
........
К встроенным функциям относится и класс агрегатных функций, перечисленных при описании предложения Group By оператора Select в разделе 3.3.2.
Список часто используемых встроенных функций приведен
в приложении 2.
Создание пользовательских функций
Пользовательские функции могут возвращать как скалярное
значение (одно значение), так и таблицу. Возвращаемое значение
определяется ключевым словом Returns.
Существуют следующие типы пользовательских функций [1]:
– Скалярные – функции, возвращающие одно значение.
– Табличные – функции, возвращающие таблицу значений.
Если таблица возвращается одним оператором Select, то функция
называется подставляемой табличной функцией. Создание таблицы, таким образом, предполагает, что все имена столбцов и их типы
определены в операторе Select.
– Многооператорные – функции, при определении которых задаются новые имена столбцов и типы данных.
Ниже перечислены операторы, которые можно использовать
в многооператорной функции:
• операторы присваивания;
• операторы управления потоком;
• локальные операторы объявления переменных;
• операторы Set/Select, которые используются для присвоения значений локальным переменным функции.
Недетерминированные функции сервера использовать внутри
функции нельзя, поскольку они могут возвращать различные значения даже при одинаковых входных данных, например, Getdate,
Rand и другие.
Скалярные функции имеют следующий синтаксис:
Create Function имя_функции
([@Имя_параметра скалярный_тип_параметра [ =
значение_по_умолчанию] [,...,n])
Returns возвращаемый_тип_данных
[As]
[операторы SQL]
Returns [(] оператор Select [)].
Пример 61. Пример скалярной функции. Функция, форматирующая дату в европейский формат: день/месяц/год, может быть такой:
143
Create Function DataForm
(@Date Datetime, @DateSeper Char(1))
Returns Varchar(20)
As
Begin
Declare @ReturnDate Varchar (20)
Set @ReturnDate = Convert (Varchar (2),
Day(@Date)) + @DateSeper + Convert (Varchar (2),
Month(@Date))+ @DateSeper + Convert (Varchar (4),
Year (@Date))
Return (@ReturnDate)
End;
Вызов созданной пользовательской функции можно выполнить
оператором:
Select dbo.DataForm (Getdate (), ′/′) As NateNow;
В результате будет выдана сегодняшняя дата в европейском
формате.
При вызове функции необходимо добавить префикс dbo. Префикс dbo. (владелец) желательно добавлять к таким именам объектов как таблицы, хранимые процедуры, функции, представления
и пр.
Пример 62. Пример табличной функции. Функция, возвращающая фирмы, которые представляют поставщиков с именем, например, Петров, может быть такой:
Create Function [dbo].[ttt](@pim nVarchar(50))
Returns Table
As Return(Select Post.NFM,FIM From Firm,Post
Where (Firm.NFM=Post.NFM) And (PIM=@pim));
Для вывода результата в виде таблицы при описании функции
использован табличный тип данных Table.
Вызов функции осуществляется оператором, в котором использован предикат Distinct, иначе по умолчанию выводятся все записи таблицы, включая повторяющиеся:
Select Distinct * From dbo.ttt(′Петров′);
В результате функция возвращает таблицу, полученную ранее
с помощью хранимой процедуры и представленную на рис. 81(слева) в примере 51 из раздела 5.4.
144
9. ПРОВЕРОЧНЫЕ ОГРАНИЧЕНИЯ
И ЗНАЧЕНИЯ ПО УМОЛЧАНИЮ
Проверочные ограничения используются для контроля вводимых значений, например, в столбец таблицы. В качестве проверочных ограничений в SQL Server 2012 используются правила и ограничения с ключевым словом Check.
Чтобы воспользоваться правилом, нужно сначала его создать, а
затем привязать его к столбцу таблицы.
Пример 63. Создать правило, которое проверяет вводимое значение цены детали в таблицу Det. Цена должна быть больше нуля
или может быть не введена, т.е. иметь значение Null:
Create Rule Znach_CENA_Det
As
@CENA >0 Or @CENA Is Null;
Здесь Znach_CENA_Det – имя правила.
Привязка правила к столбцу таблицы осуществляется с помощью системной хранимой процедуры sp_bindrule согласно синтаксису:
Exec sp_bindrule ′Имя правила′,′Имя столбца′.
Привязать только что созданное правило к столбцу CENA таблицы Det можно оператором
Exec sp_bindrule ′Znach_CENA_Det′,′Det.CENA′;
Отвязать правило от столбца можно с помощью процедуры sp_
unbindrule согласно синтаксису:
Exec sp_unbindrule ′Имя столбца′;
Удалить правило, предварительно отвязав его от столбца, можно оператором
Drop ′Имя правила′;
Здесь задание имени таблицы в качестве префикса Det обязательно.
Вместо правил предпочтительнее использовать проверочное
ограничение с ключевым словом Check.
Проверочное ограничение Check используется, как и правило,
для проверки допустимости данных, вводимых в конкретный столбец таблицы. Это ограничение можно задавать как при создании,
так и при модификации таблицы. Синтаксис ограничения:
[Constraint имя_ограничения] Check
(условие_на_значение).
К одному и тому же столбцу таблицы можно применить несколько ограничений Check, соединенных друг с другом логическими
145
операторами And и Or. Они будут применимы в той последовательности, в которой происходило их создание. Возможно применение
одного и того же ограничения к разным столбцам. Считается, что
значение удовлетворяет ограничению Check, если результатом
проверки условия является значение True.
Пример 64. Задание проверочного ограничения Check при создании таблицы.
Создадим таблицу Stud, в которой будет всего два поля: номер
студенческого билета Nomst и номер группы Gr студента, причем
значение номера группы должно вводиться из заданного множества значений. Оператор создания такой таблицы будет таким:
Create Table Stud (Nomst Int Not Null,Gr Int Not Null,
Constraint gr_ch Check(Gr In(4211,3215,1213)),
Constraint Id_Nomst Primary Key (Nomst));
Пример 65. Добавление проверочного ограничения при модификации таблицы.
Добавим к таблице Det ограничение на цену деталей. Пусть вводимое значение цены не должно выходить за рамки диапазона от 10
до 700 денежных единиц. Это можно сделать оператором:
Alter Table Det
Add Constraint cena_ch Check(CENA Between 10 And 700);
Удалить только что созданное ограничение можно оператором
Alter Table Det Drop cena_ch;
Аналогично правилу можно задавать значение по умолчанию.
Пример 66. Определим значение СПб для поля GOR таблицы Post
как значение по умолчанию:
Create Default GOROD As ′СПб′ –-GOROD–имя значения по
умолчанию.
Привязка значения по умолчанию к столбцу таблицы осуществляется с помощью системной хранимой процедуры
sp_bindefault:
Exec sp_bindefault ′GOROD′,′Post.GOR′;
Отвязать значение по умолчанию от столбца таблицы можно
с помощью системной хранимой процедуры sp_unbindefault.
Удалить значение по умолчанию можно оператором Drop ′Имя
правила′.
146
10. ОБЕСПЕЧЕНИЕ БЕЗОПАСНОСТИ ДАННЫХ
Подключение к MS SQL Server выполняется далеко не так просто, как хотелось бы.
MS SQL Server является системой, удовлетворяющей самым
жестким требованиям безопасности [6]. При запуске SQL Server
проверка прав доступа проводится на трех различных уровнях, как
показано на рис. 82.
Первый уровень представляет служба защиты данных Windows,
второй – SQL Server (проверка учетной записи), а третий – отдельно
взятая база данных (проверка имени пользователя).
Аутентификация в сети Windows (уровень 1) состоит в проверке
подлинности пользователя при работе с операционной системой.
Регистрируясь в домене Windows, пользователь вводит свое имя
(логин) и пароль. Если имя и пароль введены правильно, то пользователь автоматически получает доступ к данным сервера. Такой
метод доступа называется доверительным соединением.
Операционная система работает с учетными записями (logins),
которые содержат все данные о пользователе, такие как его имя, пароль, членство в группах, каталог по умолчанию и пр. Каждая учетная запись имеет уникальный идентификатор (login ID), иначе называемый идентификатором безопасности (SID, Security IDentification),
который хранится в системной базе данных Master. С помощью
идентификатора безопасности пользователь регистрируется в сети.
Идентификатор – это шестнадцатеричное число, которое генерируется операционной системой случайным образом во время созЗапрос на сетевое соединение с SQL Server
1. Аутентификация в сети Windows
Запрос на регистрацию в SQL Server
2. Аутентификация в SQL Server
Запрос на регистрацию в базе данных
3. Аутентификация в базе данных
Рис. 82. Уровни защиты данных в системе Windows – SQL Server
147
дания учетной записи. Такой подход позволяет избежать подделки
учетных записей пользователей.
Если в сети имеется небольшое количество пользователей, то
предоставляется доступ каждому из них. А если – много пользователей, то доступ обычно предоставляется группам пользователей. Тогда на уровне домена администратором создается несколько
групп пользователей, каждая из которых предназначена для решения своих специфических задач.
Одна и та же учетная запись может быть включена в одну или
несколько групп пользователей.
Для повышения безопасности операционная система требует от
пользователя периодической смены пароля. Кроме того, операционная система имеет встроенные системы защиты от подбора паролей.
Если пользователь был удален из домена, то даже повторное создание учетной записи пользователя не дает возможности получить
доступ к объектам, к которым ранее имел доступ тот же пользователь. Таким образом, никто не сможет присвоить права удаленного
пользователя.
Если пользователь не зарегистрирован в домене Windows, то он
должен быть зарегистрирован в SQL Server.
10.1. Регистрация пользователя на уровне сервера
На уровне сервера (уровень 2) система безопасности оперирует
следующими понятиями:
– аутентификация пользователя (authentication),
– учетная запись (login),
– встроенные роли сервера (fixed server roles).
Пользователь, для которого была создана учетная запись, может
соединиться с сервером. Однако он не получает автоматически доступ ко всем базам данных. Доступ к конкретной базе данных пользователь получает, пройдя аутентификацию на третьем уровне. Получив доступ к конкретной базе данных, пользователь не получает
доступ ко всем ее объектам. Ему должны быть предоставлены разрешения на доступ к конкретным объектам базы данных.
Учетные записи можно создавать с помощью:
– программы-менеджера,
– средствами T-SQL.
В данной работе для этих целей будем использовать T-SQL.
Как было отмечено выше, вся информация о пользователе хранится в системной базе данных Master.
148
Создание учетной записи
Для создания учетной записи используется системная хранимая
процедура sp_addlogin, которая имеет следующий синтаксис:
sp_addlogin [@loginame=] ′login′
[,[@passwd=] ′password′]
[,[@defdb=] ′database′]
[,[@deflanguage=] ′language′]
[,[@sid=] sid]
[,[@encryptopt=]
{′encryption_option′|′skip_encryption′},
где переменные, начинающиеся с символа @, – имена параметров процедуры,
login – имя создаваемой учетной записи,
password – пароль (не более 128 символов),
database – имя базы данных, с которой будет работать пользователь (по умолчанию Master),
language – язык (по умолчанию Null),
sid – явно указанный идентификатор безопасности,
encryption_option – указание на то, что осуществляется шифрование пароля,
skip_encryption – указание на то, что шифрование пароля не
выполняется.
Список хранимых на сервере баз данных можно вывести с помощью системной хранимой процедуры sp_helpdb.
Язык можно изменить оператором T-SQL Set Language ′имя
языка′, например, Set Language ′russian′.
Правом выполнения этой процедуры имеют системный администратор (Sysadmin) или пользователь с правами Securityadmin.
Пример 67.
1) Exec sp_addlogin ′Petrov′;
Созданная для работы с базой данных учетная запись по имени
Petrov будет иметь пустой пароль, язык установлен по умолчанию,
идентификатор безопасности будет сгенерирован случайным образом.
2) Exec sp_addlogin ′Petrov′, ′123′,@sid = 000…0FF;
Созданная учетная запись имеет текстовый пароль 123 и конкретный шестнадцатиричный SID, состоящий из 30 символов.
3) Задаем все параметры:
Exec sp_addlogin ′Petrov′, ′123′, ′postavka′,′russian′,
000…0FF, ′skip_encryption′;
где ′skip_encryption′ означает, что шифрование пароля не выполняется.
149
Обратите внимание на последовательность задания параметров
при вызове процедуры. Если задаются только значения параметров, то эти значения указываются в последовательности, определенной синтаксисом. Если при этом какой-то параметр отсутствует, то пропущенное значение обозначается скобками с пустым значением (′′). Если задаются не все параметры или они указываются
в произвольной последовательности, то обязательно надо использовать имена параметров, например,
Exec sp_addlogin @loginame =′Petrov′,@passwd =′123′,
@sid = 00…0FF;
Можно всегда указывать имена параметров, даже если это и не
требуется.
Указания имени базы данных в процедуре sp_addlogin недостаточно для доступа пользователя к указанной базе данных. Администратор должен дать еще пользователю разрешение на доступ
к этой базе данных с помощью системной хранимой процедуры
sp_grantdbaccess:
Exec sp_grantdbaccess ′Petrov′;
Фиксированные роли сервера
Набор фиксированных ролей сервера строго ограничен [4], [6].
Всего их восемь (табл.10.1). Никто, включая администратора, не
может создать новую или удалить существующую роль сервера.
Весь список фиксированных ролей сервера можно получить, вызвав системную хранимую процедуру: Exec sp_helpsrvrole;
Таблица 10.1
Фиксированные роли сервера
Фиксированная роль
Sysadmin
Securityadmin
Serveradmin
Setupadmin
Processadmin
DbCreator
Diskadmin
Bulkadmin
150
Описание
Системный администратор. Имеет абсолютные
права. Выполняет любые действия в системе баз
данных
Имеет право создавать новые учетные записи
Конфигурирует параметры сервера
Устанавливает репликацию и управляет расширенными процедурами
Управляет системными процессами
Создает и модифицирует базы данных
Управляет файлами на диске
Имеет право создавать доменные учетные записи
Для добавления учетной записи в ту или иную фиксированную роль сервера используется системная хранимая процедура
sp_addsrvrolemember с синтаксисом:
sp_addsrvrolemember [@loginame=] ′login′,[@rolename=]
′role′.
Например, Exec sp_addsrvrolemember @loginame = ′Petrov′,
@rolename = ′dbCreator′;
или Exec sp_addsrvrolemember @loginame = ′Petrov′,
@rolename = ′Securityadmin′;
Специальные учетные записи Sa и Builtin\Administrators
сохранены для совместимости с предыдущими версиями сервера.
Список учетных записей, включенных в конкретную фиксированную роль сервера, например в роль sysadmin, можно узнать,
вызвав процедуру:
Exec sp_helpsrvrolemember ′sysadmin′;
Для удаления учетной записи из фиксированной роли сервера
используется процедура sp_dropsrvrolemember с синтаксисом:
sp_dropsrvrolemember [@loginame=] ′login′,
[@rolename=] ′role′.
10.2. Регистрация пользователя на уровне
базы данных
На уровне базы данных (уровень 3) система безопасности оперирует понятиями [4]:
– пользователь базы данных (database user),
– фиксированные роли базы данных (fixed database role),
– пользовательские роли базы данных (users database role),
– роли приложения (application role).
Такой многоступенчатый подход позволяет более гибко управлять доступом к данным.
Фиксированные роли базы данных
Существует всего девять фиксированных ролей базы данных
(табл.10.2). Имеется еще отдельная специальная роль public, которая предназначена для предоставления прав доступа по умолчанию. В эту роль нельзя принудительно включить пользователя.
Любой пользователь базы данных автоматически включается в эту
роль, и его нельзя оттуда исключить. Ни одну из фиксированных
ролей нельзя удалить. Также нельзя никакую фиксированную
роль добавить.
151
Таблица 10.2
Фиксированные роли базы данных
Фиксированная роль
Описание
Владелец базы данных, может работать с любыdb_owner
ми объектами базы
db_accessadmin
Управляет пользователями базы данных.
Управляет правами доступа к объектам базы
db_securityadmin
данных
db_ddladmin
Могут выполнять инструкции DDL в базе данных
Могут читать данные из любых таблиц и предdb_datareader
ставлений базы
Могут добавлять, изменять или удалять данные
db_datawriter
во всех пользовательских таблицах базы данных
Могут выполнять резервное копирование базы
db_backupoperator
данных
Не могут просматривать любые данные в базе неdb_denydatareader
зависимо от выданных разрешений
Не могут изменять никакие данные в базе незавиdb_denydatawriter
симо от выданных разрешений
Для получения информации о фиксированных ролях базы данных используется системная хранимая процедура sp_helprole.
Добавление членов в фиксированную роль выполняется с помощью процедуры sp_addrolemember, имеющей следующий синтаксис:
sp_addrolemember [@rolename=] ′role′,[@membername=]
′login′.
Например, Exec sp_addrolemember @rolename =′db_owner′,
@membername=′Ivanov′;
Удаление члена из роли осуществляется процедурой
sp_droprolemember.
Пользовательские роли базы данных (группы полльзоввателей)
Пользовательские роли служат для группировки пользователей
с целью облегчения управления их правами доступа к объектам
базы данных.
Создание пользовательской роли (группы пользователей) осуществляется процедурой sp_addrole, имеющей следующий синтаксис:
sp_addrole [@namerole=] ′имя группы′.
В предыдущих версиях использовалась процедура sp_addgroup.
Добавление пользователя в роль (группу) осуществляется процедурой
152
sp_addrolemember ′имя роли (группы)′, ′имя пользователя′.
Исключение пользователя из роли (группы) осуществляется
процедурой
sp_droprolemember ′имя роли (группы)′, ′имя пользователя′.
Удаление пользовательской роли (группы) осуществляется процедурой
sp_droprole [@rolename=] ′имя роли (группы)′.
Просмотр списка членов фиксированной роли осуществляется
с помощью системной хранимой процедуры sp_helprolemember:
Exec sp_helprolemember ′имя роли(группы)′;
Просмотр списка членов всех ролей осуществляется с помощью
процедуры sp_helprolemember без указания роли:
Exec sp_helprolemember;
Для управления правами доступа к объектам базы данных используется оператор Grant, укрупненный синтаксис которого такой [4]:
Grant
{All[Privileges]|Permission[,…n]}
{
[(column[,…n])] On {table|View}
|On {table|View}[(column[1,…n])]
|On {stored_procedure|extended_procedure}
|On {user_defined_function}
}
To Security_account [,…n]
[With Grant Option]
[As {group|role}], где
All – все допустимые права, которые может дать или администратор (Sisadmin) или db_owner;
Permission – список категорий доступа. Допускаются
следующие категории доступа: Select, Insert, Delete, Update,
References, Execute (выполнение хранимых процедур или пользовательских функций).
[,…n] – в одной команде Grant может быть выдано множество
прав;
column[,…n] – доступ к одному или списку столбцов таблицы
или представления;
On {table|View} – имя таблицы или представления;
On {stored_procedure|extended_procedure} – имя пользовательской процедуры, которая должна принадлежать текущей
базе данных, или имя расширенной (системной) хранимой процедуры, которая принадлежит базе данных Master.
153
On {user_defined_function} – права доступа к пользовательской функции (только Execute и References).
Примеры 68.
1) /* Разрешается изменять значения в поле DIM таблицы Det
пользователю Serge */
Grant Update (DIM) On Det To Serge;
2) /* Разрешается читать и добавлять записи в таблицу Post
пользователю Ivan */
Grant Select,Insert On Post To Ivan;
3) /* Разрешается вызывать процедуру Ins_Det пользователю
Serge */
Grant Exec On Ins_Det To Serge;
Отмена выданных объектных прав осуществляется оператором
Revoke.
Аннулирование выданных объектных прав осуществляется оператором Deny.
Синтаксис операторов Revoke и Deny почти такой же, как и синтаксис оператора Grant.
Например, Deny Select On Post To Ivan;
Revoke Create Table To Serge, Maxim;
Пример 69. На рис. 83 показаны пользователи, которых необходимо зарегистрировать и дать им следующие права доступа к данным базы postavka:
Группе grupA, включающей пользователей Us1 и Us2, разрешить только читать данных из таблицы Post.
Группе grupВ, включающей пользователей Us3 и Us4, разрешить выполнять все действия над данными таблицы Det: читать,
удалять, обновлять.
Us1
Sysadmin
Securityadmin
USE
grupA
Us2
Us3
grupB
Us4
Рис. 83 Пользователи
154
Кроме того, отдельным пользователям дадим дополнительные
права:
пользователю Us1 – выполнять пользовательскую хранимую
процедуру vivod, а пользователю Us4 – читать данные из столбцов
PIM и GOR таблицы Post.
Ниже приведена последовательность действий, которую необходимо выполнить для решения поставленной задачи.
Выполнив доверительное соединение с сервером, пользователь
Sysadmin регистрирует пользователя USE с паролем «123», выполняя следующие действия:
– Создание учетной записи:
Exec sp_addlogin ′USE′, ′123′, ′postavka′;
– Включение пользователя USE в серверную роль Securityadmin:
Exec sp_addsrvrolemember @loginame = ′USE′, 
@rolename = ′Securityadmin′;
– Разрешение на доступ к базе данных postavka:
Exec sp_grantdbaccess ′USE′;
Выполнив соединение с сервером, пользователь USE с правами
Securityadmin, создает учетные записи для пользователей Us1 –
Us4 с паролями «123»:
Exec sp_addlogin ′Use1′, ′123′, ′postavka′;
Exec sp_addlogin ′Use2′, ′123′, ′postavka′;
Exec sp_addlogin ′Use3′, ′123′, ′postavka′;
Exec sp_addlogin ′Use4′, ′123′, ′postavka′;
Снова выполнив доверительное соединение с сервером, пользователь Sysadmin, выполняет следующие действия:
– Дает разрешение пользователям Us1 – Use4 на доступ к базе
данных postavka (по умолчанию, так как имя базы данных было
указано при создании учетной записи):
Exec sp_grantdbaccess ′Us1′;
Exec sp_grantdbaccess ′Us2′;
Exec sp_grantdbaccess ′Us3′;
Exec sp_grantdbaccess ′Us4′;
– Создает пользовательские роли (группы пользователей):
Exec sp_addrole ′grupA′;
Exec sp_addrole ′grupB′;
– Включает пользователей в пользовательские роли (группы):
Exec sp_addrolemember ′grupA′, ′Us1′;
Exec sp_addrolemember ′grupA′, ′Us2′;
Exec sp_addrolemember ′grupB′, ′Us3′;
Exec sp_addrolemember ′grupB′, ′Us4′;
155
– Дает разрешения для групп пользователей:
Grant Select On Post To grupA;
и
Grant Select,Delete,Update On Det To grupB;
– Дает дополнительные разрешения для отдельных пользователей:
Grant ExecuteGrant Select(PIM, GOR)
On vivod
On Post
To Us1;
и
To Us4;
Если бы потребовалось удалить группы, отобрать права у пользователей и удалить пользователей, то нужно выполнить следующую последовательность действий:
– исключить пользователей Us1 – Us4 из групп,
– отобрать права у пользователей Us1 – Us4,
– удалить группы grupA и grupB,
– удалить пользователей Us1 – Us4.
10.3. Некоторые хранимые процедуры помощи
sp_hehpdb – список хранимых процедур на сервере;
sp_hehpsrvrole – список фиксированных ролей сервера;
sp_hehpsrvrolemember [имя роли] – список учетных записей,
включенных в указанную роль или во все роли, если роль не указана;
sp_hehprole – список фиксированных ролей базы данных;
sp_hehprolemember [имя роли] – список членов в указанной
роли или во всех ролях, если роль не указана;
sp_hehplogins – список всех учетных записей с Sid;
sp_hehprotect – список прав доступа для всех пользователей.
156
ПРИЛОЖЕНИЕ 1
Типы данных
Таблица 1.1
Основные типы данных SQL Server
Тип данных
TINYINT
SMALLINT
INT
BIGINT 
DECIMAL [(p[,s])]
DECIMAL [(p[,s])]
NUMERIC (p,s)
REAL
FLOAT[(n)] 
SMALLMONEY 
MONEY
SMALLDATETIME 
DATETIME
BIT
BINARY (n) 
VARBINARY (n)
Описание
Целочисленные
Целое число в интервале от 0 до 255
2 байта. Целое число в интервале от –32 768 до
32 767
4 байта. Целое число в интервале от –2147 483 648
до 2147 483 647
8 байт. Целое число из диапазона от –263 до +263–1
2–17 байт. По умолчанию p = 18, s = 0 
Вещественные
2–17 байт. Обычно s>0. Аналог предыдущего типа
данных 
Числовые данные с количеством разрядов (р) и
количеством цифр после запятой (s). Интервал: от
–1038–1 до 1038–1. Аналог Decimal [(p[,s])]
4 байта.Число с плавающей запятой, т.е. число
представляется в виде мантиссы и порядка. Диапазон: от 3.40Е+38 до 3.40Е+38. Точность 7 десятичных цифр после запятой
8 байт. Число с плавающей запятой. Интервал: от
–1.79Е+308 до 1.79Е+308. Точность 15 десятичных цифр после запятой
Денежные
4 байта. Диапазон от (–214 748, 3648) до (214 748,
3647) с точностью до 4 знаков после запятой
8 байт. Интервал: от –922337203685477.5808 до
922337203685.5807 с точностью до 4 знаков после
запятой
Дата и Время
4 байта. От 01.01.1900 до 6.06.2079 нашей эры.
Точность 1 минута
8 байт. От 01.01.1753 до 31.12.9999 нашей эры.
Точность 3,33 миллисекунды
Двоичные
1 бит. Целое значение 0 или 1. Аналог логического
типа данных
8000 байт. Двоичная информация
8000 байт. Двоичная информация без дополнительных пробелов
157
Окончание табл. 1.1
Тип данных
IMAGE 
Описание
2 Гбайта. Большие блоки двоичной информации
Строковые (символьные)
CHAR (n)
8000 байт. Строка фиксированной длины (n). Позволяет хранить до 8000 знаков в формате ASCII
NCHAR(n)
8000 байт. Строка переменной длины. Хранение
теста в формате Unicode – 4000 символов
VARCHAR (n)
8000 байт. Строка фиксированной длины (n). Позволяет хранить до 8000 знаков в формате ASCII
без дополнительных пробелов
NVARCHAR(n)
8000 байт. Строка переменной длины. Хранение
теста в формате Unicode – 4000 символов без дополнительных пробелов
Текстовые
TEXT 
2 Гбайта. 2 147 483 647 символов
NTEXT 
2 Гбайта. 1 073 741 823 символа в формате Unicode
TIMESTAMP 
8 байт. Уникальный идентификатор (в пределах
базы) – VARBINARY(8)
UNIQUEIDENTIFIE 16 байт. Глобальный уникальный идентификатор – уникальность в пределах планеты
Специальные
SQL_VARIANT 
8000 байт. Позволяет хранить данные нескольких
типов в одном столбце за исключением text, ntext,
image и timestamp
TABLE 
Применяется подобно временным таблицам для
хранения наборов строк (rowset). Используется
только для переменных и значений, возвращаемых функциями пользователя. Не может использоваться для столбцов таблицы
158
ПРИЛОЖЕНИЕ 2
Стандартные функции
Таблица 2.1
Встроенные функции SQL Server
Математические
ABS (выражение_numeric)
ACOS |ASIN| ATAN (выражение_float)
COS | SIN|TAN| COT (выражение_float)
CEILING (выражение_numeric)
Абсолютное значение
Арккосинус, арксинус, арктангенс
(в радианах)
Косинус, синус, тангенс, котангенс
(в радианах)
Наименьшее целое, большее или равное выражение_numeric
EXP (выражение_float)
Экспонента
PI ( )
Константа
p = 3,141592653589793
LOG (выражение_float)
Натуральный логарифм
LOG10 (выражение_float)
Десятичный логарифм
POWER (выражение_numeric,y) Возведение выражение_numeric в степень y
RAND ([начальное число])
Случайное число типа float расположенное между 0 и 1
ROUND (выражение_numeric,
Округленное выражение_numeric с
длина)
точностью после запятой, определенной целым длина
SIGN (выражение_numeric)
Знак числа
SQUARE (выражение_float)
Квадрат числа
SQRT (выражение_float)
Квадратный корень числа
Строковые
′выражение1′ + ′выражение2′
Конкатенация (слияние) двух символьных строк
ASCII (выражение_char)
Возвращает значение кода ASCII самого левого символа
CHAR (выражение_integer)
Возвращает символ, имеющий указанный код ASCII
DIFFERENCE (выражение1_char, Сравнивает две строки и анализирует
выражение2_char)
их на совпадение; возвращает значение от 0 до 4; при совпадении возвращается 4
LEFT (выражение_char, выраже- Возвращает строку, начиная слева,
ние_integer)
состоящую из символов выражение_
integer
159
Продолжение табл. 2.1
LOWER (выражение_char)
Преобразует символы к нижнему регистру
LTRIM (выражение_char)
Возвращает строку без первых пробелов
PATINDEX(выражение_char)
Возвращает начальную позицию первой встретившейся в выражении подстроки, совпадающей с образцом
REPLACE (′строка1′, ′строка2′, Заменяет в ′строка1′ все элементы
′строка3′)
′строка2′ на ′строка3′
REPLICATE (выражение_char,
Повторяет выражение_char выражевыражение_integer)
ние_in-teger раз
REVERSE (выражение_char)
Возвращает реверсивное значение выражение_char
RIGHT(выражение_char,
Возвращает символьную подстроку
выражение_integer)
из выражение_char, начиная с символа выражение_integer справа
RTRIM (выражение_char)
Возвращает строку без последних
пробелов
SPACE (выражение_integer)
Возвращает строку из выражение_integer пробелов
STR (выражение_float [, длина[, Возвращает символьные данные, предесятичное_число]])
образованные из числовых данных;
длина – общая длина строки; десятичное_число – количество пробелов
справа
STUFF (выражение1_char, нача- Удаляет длина символов, начиная с
ло, длина, выражение2_char)
символа начало, и вставляет в это место строку выражение2_char
SUBSTRING (выражение, наВозвращает часть символьной или бичало, длина)
нарной строки
UPPER (выражение_char)
Преобразует символы в верхний регистр
Обработка дат
DATEADD (часть_даты, число, Добавляет к date величину часть_
date)
даты, умноженную на число раз
DATEDIFF (часть_даты, date1, Возвращает
количество
величин
date2)
часть_даты между двумя датами
DAY (date)
Возвращает целое число дней в date
GETDATE ( )
Возвращает текущие системную дату
и время во внутреннем формате
MONTH (date)
Возвращает целое число месяцев
в date
YEAR (date)
Возвращает целое число лет в date
160
Окончание табл. 2.1
CONVERT(тип_данных [(длина)], выражение [, стиль])
Преобразует значение, заданное выражением, в указанный тип данных,
представляемый указанным стилем
CAST( . . . )
Аналогична функции CONVERT, но
без параметра стиль
Системные функции
COALESCE (выражение1, выраВозвращает первое не равное NULL
жение2,…, выражениеN)
выражение
COL_NAME (id_таблицы,
Возвращает им столбца
id_столбца)
COL_LENGTH (′имя_таблицы′, Возвращает ширину поля столбца
′имя_столбца′)
DATALENGTH (′выражение′)
Возвращает фактическую длину переменной выражение любого типа данных
DB_ID ([′имя_базы_данных′])
Возвращает идентификатор ID базы
данных
DB_NAME ([id_базы_данных])
Возвращает имя базы данных
IDENT_SEED (′имя_таблицы′)
Возвращает начальное значение для
столбца типа identity
IDENT_COL (′имя_таблицы′,
Возвращает имя индексированного
id_индекса, id_ключа)
столбца
ISDATE (переменная/ имя_
Проверяет правильность формата
столбца)
даты; если формат правильный – возвращает 1, иначе – 0
ISNULL (выражение, значение) Заменяет все NULL на значение
ISNUMERIC (переменая/
Проверяет правильность числового
имя_столбца)
формата; возвращает 1, если формат
правильный и 0 в противном случае
NULLIF (выражение1, выражеВозвращает NULL, если выражение2)
ние1 = выражение2
OBJECT_NAME (id_объекта)
Возвращает имя объекта базы данных
USER_ID([′имя_пользоватеВозвращает идентификатор пользоля_сервера′])
вателя сервера
SUSER_NAME ([идентификВозвращает имя пользователя сервера
тор_сервера])
USER_ID ([′имя_пользователя′]) Возвращает идентификатор пользователя
USER_NAME ([id­_пользователя′]) Возвращает имя пользователя базы
данных
161
Библиографический список
1. Барклаевская Н. В., Евсеев Г. С., Преснякова Г. В., Шахомиров А. В. Работа в среде MS SQL Server 2000: метод. указ. к вып. лабораторных и курсовых работ. Санкт-Петербург, 2007.
2. Бондарь А. Г. Microsoft SQL Server 2012. СПб.: БХВ-Петербург,
2013.
3. Веймаер Р., Сотел Р. Освой самостоятельно Microsoft SQL
Server 2000 за 21 день М.: Издательский дом «Вильямс», 2001.
4. Мамаев Е. Ю, Шкарина Л. (для профессионалов). СПб.: Питер, 2001.
5. Михеев Р. Н. MS SQL Server 2005 для администраторов. СПб.:
BHV, 2007.
6. Петкович Д. Microsoft SQL Server 2012. Руководство для начинающих. СПб.: БХВ-Петербург, 2013.
7. Пирогов В. SQL Server 2005. Программирование клиент-серверных приложений. СПб.: БХВ-Петербург, 2006.
8. Пол Литвин и др. Access 2002. Руководство разработчика.
Т. 2. Корпоративные приложения. СПб.: BHV, 2003.
9. Пол Литвин и др. Access 2002. Разработка корпоративных
приложений. СПб.: Питер, Киев, BHV, 2003.
10. Пол Нильсен. SQL Server 2005. Библия пользователя, «Вильямс»,2008.
11. Преснякова Г. В. Проектирование интегрированных реляционных баз данных. М.: КДУ; СПб.: Петроглиф, 2007.
12. Преснякова Г. В., Шахомиров А. В. Проектирование реляционных баз данных: учеб. пособие. Санкт-Петербург, 2015.
13. Роб Хоторн. Разработка баз данных MS SQL Server на примерах, 2001.
14. Станек Уильям Р. Microsoft SQL Server 2012: справочник администратора. М.: Русская редакция; СПб.: БХВ-Петербург, 2013.
15. Фленов М. Е. Transact-SQL. СПб.: БХВ-Петербург, 2006.
162
СОДЕРЖАНИЕ
Введение.................................................................................... 1. Некоторые важные сведения о MS SQL Server 2012....................... 1.1. Архитектура MS SQL Server............................................... 1.2. Замечания по установке и настройке
Microsoft SQL Server 2012........................................................ 1.3. Средства управления Microsoft SQL Server 2012................... 1.4. Основные компоненты Microsoft SQL Server 2012................. 2. Создание базы данных на MS SQL Server 2012.............................. 2.1. Создание базы данных с помощью
SQL Server Management Studio................................................. 2.2. Создание базы данных с использованием ADP-проекта.......... 2.3. Импорт таблиц из базы данных MS Access в базу данных
на сервере.............................................................................. 2.4. Перенос базы данных с одного компьютера на другой............ 2.4.1. Отсоединение и присоединение базы данных ................ 2.4.2. Использование резервного копирования....................... 2.5. Создание базы данных операторами Transact-SQL................. 3. Основы Transact-SQL................................................................ 3.1. Основные объекты SQL ..................................................... 3.2. Типы данных................................................................... 3.3. Запросы.......................................................................... 3.3.1. Определение данных.................................................. 3.3.2. Манипулирование данными........................................ 3.3.3. Задания для самостоятельного выполнения.................. 4. Представления........................................................................ 5. Хранимые процедуры.............................................................. 5.1. Понятие хранимой процедуры............................................ 5.2. Операторы управления потоком......................................... 5.3. Примеры хранимых процедур............................................ 5.4. Вывод результатов выполнения хранимой процедуры........... 6. Транзакции............................................................................ 7. Триггеры................................................................................ 8. Функции................................................................................ 9. Проверочные ограничения и значения по умолчанию................... 10. Обеспечение безопасности данных............................................ 10.1. Регистрация пользователя на уровне сервера...................... 10.2. Регистрация пользователя на уровне базы данных............... 10.3. Некоторые хранимые процедуры помощи.......................... Приложение 1. Типы данных........................................................ Приложение 2. Стандартные функции........................................... Библиографический список.......................................................... 3
5
5
7
11
12
18
18
29
35
43
43
45
55
63
63
65
65
66
78
112
115
117
117
118
119
125
131
134
142
145
147
148
151
156
157
159
162
163
Учебное издание
Преснякова Галина Владимировна
Барклаевская Наталья Владимировна
Шарапова Ольга Михайловна
РАБОТА С БАЗАМИ ДАННЫХ В СРЕДЕ
MICROSOFT SQL SERVER 2012
Учебное пособие
В авторской редакции.
Компьютерная верстка С. Б. Мацапуры
Сдано в набор 21.11.16. Подписано к печати 22.12.16.
Формат 60×84 1/16. Бумага офсетная. Усл. печ. л. 9,5.
Уч.-изд. л. 10,2. Тираж 50 экз. Заказ № 509.
Редакционно-издательский центр ГУАП
190000, Санкт-Петербург, Б. Морская ул., 67
Документ
Категория
Без категории
Просмотров
0
Размер файла
4 632 Кб
Теги
presnyakova
1/--страниц
Пожаловаться на содержимое документа