Для ботаников и лентяев

SQL Server

1. Назначение SQL Server 7.0 (OLTP, OLAP)

2. Масштабируемость, динамическое самоуправление ресурсами SQL Server 7.0

3. Службы и утилиты администрирования SQL Server 7.0

4. Архитектура “клиент-сервер” СУБД SQL Server 7.0 3

5. Аутентификация и права доступа в SQL Server 7.0

6. Система безопасности SQL Server 7.0

7. Системные базы данных SQL Server 7.0

8. Логическая и физическая архитектура базы данных SQL Server 7.0 (объекты базы данных, файловая структура базы данных) 6

9. Журнал транзакций SQL Server 7.0 7

10. Создание базы данных в SQL Server 7.0 (объяснить синтаксис команды, перечислить способы создания базы данных) 8

11. Создание таблицы в SQL Server 7.0 (объяснить синтаксис команды, перечислить способы создания таблиц) 10

12. Ограничения на значения колонок 11

13. PRIMARY KEY как ограничение на столбец 11

14. FOREIGN KEY как ограничение на столбец 12

15. Уникальность данных как ограничение на столбец 12

16. Определение идентификационной колонки IDENTITY. 13

17. Определение проверочных ограничений и значений по умолчанию 13

18. Индексы 14

19. Составные части SQL (встроенный и интерактивный, DML, DDL) 14

20. Отличие языка SQL от процедурных языков программирования 15

21. Оператор SELECT (объяснить синтаксис команды) 15

22. Агрегирующие функции 16

23. Оператор EXISTS 16

24. Операторы ALL,ANY,BETWEEN,IN 17

25. Вложенные подзапросы 17

26. Связанные подзапросы 18

27. Оператор UNION 18

28. Команды INSERT, UPDATE, DELETE. 19

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

30. Управляющие конструкции Transact -SQL. 21

31. Представления 22

32. Хранимые процедуры. 24

33. Триггеры 26

Создание триггера 26

Как работает триггер 26

Применение триггеров 27

Полезные сведения о триггерах 27

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

Аналогичный триггер, только реагирующий на удаление 27

34. Курсоры 28

________________________

1. Назначение SQL Server 7.0 (OLTP, OLAP)

Использование SQL: Server 7.0: 1) Как система поддержи принятия решений(OLAP – online analytical processing). Это ПО позволяет получать статистику на основе обработки больших объемов данных, что необходимо для эффективной деятельности предприятия. Обработка в сжатые сроки огромные V данных, анализировать и выдавать статистические результаты; формировать рекомендации в задачах менеджмента. 2) Как система управления обработкой транзакций(OLTP - online transaction processing systems). Как OLTP решает 2 задачи: • Проблема одновременного доступа. Множество пользователей могут одновременно обращаться к одним и тем же данным, но в конкретный момент право на изменений данных должно быть только у одного пользователя. Для других пользователей накладывается блокировка. Если пользователь хочет изменить данные он должен дождаться снятия блокировки.. • Целостность изменений. Все выполняемые в БД изменения существуют в виде транзакций. Транзакция – последовательность действий, выполняемых как одно целое. Т.е. если хоть одно действие в плане команд транзакции не будет выполнено, то и все остальные действия транзакции будут отменены.

2. Масштабируемость, динамическое самоуправление ресурсами SQL Server 7.0

СУБД MSSQL 7.0 – сетевая реляционная СУБД. Эта СУБД серверного типа, её ядро составляет служба – сервер, которая работает в ОС в фоновом режиме. • SQL Server 7.0 – масшабируемая система, она позволяет усиливать вычислительную мощность системы за счет большого числа процессоров или замены их на более мощные. • Это динамическая самоуправляющееся система, т.е. сервер постоянно отслеживает потребности в тех или иных ресурсах и динамически изменяет параметры системы и отдаёт неиспользованные ресурсы ОС. • SQL сервер поддерживает БД больших объемов, т.е. работает с БД объемом в несколько терабайт.

3. Службы и утилиты администрирования SQL Server 7.0

SQL Server состоит из 4 служб (служба – сервис, который работает в ОС в фоновом режиме): • MS SQL Server – ядро СУБД. Реализует большинство основных функций: выполнений хранимых процедур, запросов и команд Transact – SQL, правление файлами БД и журналами транзакций, обеспечение функционирования системы безопасности, обеспечение целостности данных, служба также призвана распределять ресурсы ОС. • SQL Server Agent – реализует автоматизацию исполнения заданий и извещения операторов об ошибках в работе сервера, для успешной работы этой службы необходима активность службы MS SQL Server. Jobs – задания, который могут выполняться автоматически в соответствии с установленным расписанием. Alerts – описывают события, при наступлении которых операторам будут направлены сообщения о произошедшем. Operators – отвечают за нормальное функционирование системы. Оператор описывается с помощью атрибутов: имя, адрес, пейджер, сетевой адрес в рабочее время. • MS Search – осуществляет поиск символьной информации в полях таблицы баз данных. • MS DTC – позволяет организовать распределенный доступ к различным источникам данных, расположенных в том числе и на удаленных компьютерах. Это осуществляется с помощью механизма распределенной транзакции, при откате транзакции служба восстанавливает данные во всех источниках данных. Утилиты администрирования: • SQL Service Manager – управляет работой служб. • SQL Server Enterprise Manager – утилита графического управления объектами сервера. Представляет информацию в виде иерархического дерева. • SQL Server Query Analyzer – утилита редактирования и выполнения SQL кода. Снабжает всей необходимой информацией о процессе выполнения запросов. • Client NetWork Utility / Server – позволяет настраивать протоколы взаимодействия для клиента и сервера. Для успешного взаимодействия клиента и сервера, необходимо чтобы сетевая библиотека клиента и сервера прослушивали один и тот же сетевой протокол. Мастера SQL Server (Wizard) – позволяют выполнять некоторые задачи пошагово. Books OnLine - справочник. Утилиты командной строки – автоматически инсталлируются с пакетом SQL Server и находятся в каталоге BIN.

4. Архитектура “клиент-сервер” СУБД SQL Server 7.0

Архитектура клиент – сервер предполагает наличие минимум двух звеньев: клиент и сервер. Задача клиента – получение от пользователя запроса и передача этого запроса серверу для выполнения, получение от сервера ответа и демонстрация его пользователю. Сервер должен выполнить полученный запрос и передать результаты клиенту. На уровне аппаратных ресурсов эта архитектура предполагает наличие центрального компьютера – сервера, предоставляющего доступ к своим ресурсам для других компьютеров клиентов. Несомненными достоинствами такой архитектуры является то, что данные хранятся центрально, т.е. информацию необходимо обновлять только на сервере. Архитектура клиент – сервер реализуется и на уровне ПО. В SQL Server 7.0 обращение к БД осуществляется через клиентские приложения. Это могут быть утилиты SQL Server, приложения сторонних разработчиков, написанные для работы с SQL Server и т.д. Процесс обращения к БД реализуется следующим образом: Модуль Функция Клиентское приложение Для обращения к БД клиентское приложение использует специальные интерфейсы доступа к БД – API (Application Programming Interface). Специальный интерфейс. API: DB-Library, ODBC, OLE DB. API – набор операторов языка обращения к БД, функций и методов, с помощью которых можно отправлять операторы к БД и получать результаты. Обращение к API приводит к активации DLL, которые в свою очередь вызывают функции и методы сетевой библиотеки для управления соединением по сети. Клиентская сетевая библиотека: TCP/IP, Named Pipes, Multi protocol Сетевая библиотека вызывает программно интерфейсы IPC для связи с соответствующей сетевой библиотекой сервера. IPC – Inter process communication Сервер: Net-Library, TCP/IP, Named Pipes, Multi protocol Сетевая библиотека сервера через соответствующий API передает данные серверу. API: ODS Через этот API серверная библиотека общается с сервером. ODS – набор методов непосредственного обращения к серверу. SQL Server Обрабатывает запрос и возвращает результат. Данные передаются клиенту в обратном порядке. ODBC – набор методов, функций и переменных для работы с БД в приложении клиент сервер. OLE – DB – содержит набор функций, позволяющий посредством специального драйвера организовать доступ к реляционным и не реляционным источникам данных Драйвер реализует доступ к хранящимся данным из клиентского приложения, запрос передается драйверу, который при необходимости трансформирует его в формат специфичный для формата данного источника.

5. Аутентификация и права доступа в SQL Server 7.0

Существуют два вида аутентификации: 1. NT-аутентификация (доверительная). Пользователь на основе NT-учетной записи получает доступ к ресурсам сервера. 2. Аутентификация SQL-сервера. Т.е. для доступа к ресурсам сервера требуется дополнительная аутентификация. Для каждой БД логин пользователя должен отображаться в пользователя этой БД. При создании БД определяются 2 вида пользователя (dbo и guest). Guest – пользователь с минимальными правами в БД. Обычно он имеет право на чтение, однако эти права могут быть расширены системным администратором, либо владельцем БД. Обычно с этим пользователем связываются те логины, которые не отображаются явно в этой БД в какое то имя пользователя БД. Если удалить этого пользователя, учетные записи, которые явно не отображаются в пользователей БД получат отказ в доступе. DBO – пользователь – владелец БД. Это специальный пользователь, обладающий максимальными правами в созданной им БД. Любой участник роли в системных администраторах автоматически отображается в пользователя dbo. Т.е. dbo может ассоциироваться не только с конкретным пользователем, но и с целой группой пользователей.

6. Система безопасности SQL Server 7.0

Функции системы безопасности: • защита данных от несанкционированного доступа; Фундаментом системы безопасности является аутентификация, учётные записи, пользователи и роли. • NT-аутентификация; • аутентификация SQL-сервера; Подключение к SQL-серверу не даёт автоматического доступа к БД. Логин должен отобразиться в имя пользователя конкретной БД. Пользователь – специальный тип объектов, при помощи которых определяются возможности учётной записи в той или иной БД. Пользователь должен быть включен в определенную роль в БД. Автоматически любому пользователю присваивается роль public. Эта роль включает в себя минимальный набор возможностей в конкретной БД, который автоматически присваивается пользователю БД. Роли – это аналог групп в Windows NT. Можно сказать, что это инструмент объединения пользователей по выполняемым им функциям. Существует 7 ролей уровня сервера и 9 ролей уровня БД, которые являются стандартными для системы безопасности SQL-сервера. Эти роли нельзя ни удалить, ни модифицировать. К тому же, на уровне БД могут создаваться пользовательские роли, которые включают определенный администратором либо dbo-пользователем набор возможностей. Еще существует такой вид роли, как роль приложения. Если пользователь должен обращаться к БД через приложение, а не напрямую, то часто возникает необходимость установления набора прав для обращение через приложение. Роль приложения не имеет участников. Роль активизируется, когда приложение устанавливает связь с БД.

7. Системные базы данных SQL Server 7.0

В СУБД реализовано 4 системных БД: • Master – хранит системную конфигурацию SQL Server. В этой БД храниться информация об учетных записях пользователя, сведения обо всех БД сервера, о расположении и числе файлов БД. Эта системная БД расположена в каталоге Data в файлах master.mdf, журнал транзакта master.ldf. • Model – задает модель любой создаваемой на сервере БД, все содержимое БД и все параметры повторяются во вновь созданной БД. • MSDB – используется службой SQL Server Agent для хранения параметров работы. Эта БД расположена в каталоге DATA и состоит из двух файлов: msdbdata.mdf, msdblock.ldf. • TempDB – предназначена для хранения временных объектов, которые будут удалены сразу после прекращения пользователем работы в системе. БД удаляется вместе с остановкой работы сервера и создается заново при возобновлении работы. Располагается в DATA: tempdb.mdf, Templog.ldf. Любая БД на сервере содержит 18 системных таблиц, содержащих файлы конфигурации. Пользователь не должен на прямую изменять содержимое этих таблиц. Изменения следует делать с помощью системных хранимых процедур.

8. Логическая и физическая архитектура базы данных SQL Server 7.0 (объекты базы данных, файловая структура базы данных)

Логическая структура БД: Существуют 9 объектов, к которым могут обращаться пользователи: 1. Tables – таблицы, которые содержат все данные БД. 2. View – представления, являются виртуальными таблицами, определенные конкретным запросом. Для пользователя выглядит как таблица, но ею не является, она лишь представляет данные. 3. Index – индексы, одна или несколько колонок, которые содержат отсортированные данные и ссылку на строку в исходной таблице, предназначены для более эффективного поиска. 4. Trigger – специальные хранимые процедуры, которые запускаются при определенных событиях изменения данных в таблице (Insert, Delete, Update). 5. User Defined Data type. Пользовательские типы данных. Создаются на основе системных типов данных. Используются когда в нескольких таблицах необходимо хранить однотипные по структуре значения. 6. Default – умолчания. Значения которые будет присвоено колонке таблицы при вставке строки, если не будет указано конкретное значение. Оставлено для обратной совместимости с предыдущими версиями. 7. Rule – Правила предназначенные для ограничения значений, хранимых в колонке таблицы. Оставлены для совместимости с предыдущими версиями. 8. Constraint – ограничение целостности. 9. Stored procedure – хранимые процедуры, хранятся центрально на сервере и могут быть использованы. Кроме того, существуют объекты для администраторских функций: 1. Database User. Учетные записи, которые предоставляют доступ к ресурсам БД. 2. Database Role. Роли БД, стандартный набор прав, которыми наделяется учетная запись к конкретной БД. 3. Diagram. Это не объект БД. Служит для виртуализации отношений между таблицами. Файловая структура БД: Primary-файл. В БД может храниться только 1 такой файл. В нем размещаются системные таблицы, описание объектов БД, данные. Этот файл имеет расширение *.mdf. Transaction Log. Расширение - *.ldf. Журнал транзакций. Представляет собой последовательность записей, содержащих информацию о транзакциях. Неактивные записи завершенных транзакций постепенно заменяются активными данными. Если Primary заполнен, а данные нужно создавать, то создаётся Secondary-файл *.ndf. Используется для хранения данных. БД может не иметь вообще этого файла, а может иметь их несколько. Каждый файл БД может располагаться на отдельном физическом диске (для отказоустойчивости). Каждый файл имеет два имени: логическое имя (используется в Transect SQL) и имя файла ОС. AutoGrow – автоматический рост объема БД.

9. Журнал транзакций SQL Server 7.0

Журнал транзакций Каждый экземпляр SQL Server ведет журнал транзакций, в котором регистрируются все изменения данных в базе. Формат журнала транзакций не подходит для чтения или модификации со стороны пользовательского процесса. Для некоторых операций (например!, CREATE INDEX) регистрируются не изменения данных, а сведения об использовании страниц. В SQL Server 7 журнал транзакций представляет собой файл, отдельный от базы данных. Журналы транзакций обеспечивают прямое и обратное восстановление транзакций. Восстановление автоматически происходит при запуске сервера. В процессе восстановления данные синхронизируются с журналом (синхронизация может быть нарушена в процессе сброса данных из кэша на диск). В журнале транзакций также регистрируются изменения, производимые текущей транзакцией, что позволяет выполнить откат и вернуть данные к состоянию, действовавшему до начала изменений. Последовательность событий при регистрации транзакций Процесс регистрации транзакций существует для того, чтобы обеспечивать транзакционную целостность данных. Сначала команда BEGIN TRAN записывается в журнал, находящийся в кэше; при этом запоминается уникальный номер транзакции, а также ее номер последовательности. Затем в кэше модифицируются страницы журнала (в SQL Server реализован журнал транзакций с опережающей записью). Далее в кэше модифицируются страницы данных. Наконец, команда COMMIT TRAN записывается в журнал, находящийся в кэше, после чего кэш с журналом сбрасывается на диск. Обратите внимание на то, что не стоит торопиться с перенесением на диск страниц данных, поскольку в процессе восстановления сервер может воссоздать страницы данных по страницам журнала. Контрольные точки «Контрольная точка» (checkpoint) — это процесс записи всех грязных страниц данных (dirty pages) из кэша на диск. «Грязными» называются страницы данных в кэше, которые были модифицированы, однако эти модификации еще не были отражены на диске. Сначала на диск записываются страницы журнала, а затем — страницы данных. Затем на диск записывается маркер контрольной точки. Маркер контрольной точки представляет собой механизм, уменьшающий время восстановления, поскольку к моменту завершения контрольной точки база данных на диске полностью синхронизирована (то есть журнал соответствует данным). Контрольные точки устанавливаются в следующих ситуациях: ¦ Превышен интервал восстановления сервера. ¦ На сервере не осталось свободного места в кэше и он выгружает грязные страницы. ¦ Владелец базы данных выполняет команду CHECKPOINT. ¦ Выполняется резервное копирование журнала транзакций. Транзакции устанавливают блокировку записей, страниц или таблиц. Это делается для того, чтобы никто не мог модифицировать или просмотреть данные незавершенной транзакции.

10. Создание базы данных в SQL Server 7.0 (объяснить синтаксис команды, перечислить способы создания базы данных)

Создать БД в SQL-сервере можно 2-мя способами: • При помощи SQL Server Enterprise Manager; • при помощи SQL Query Analizer: Создание баз данных Базы данных создаются с помощью команды CREATE DATABASE. Тот, кто принадлежит к роли Server Administrator, или любой другой пользователь, имеющий соответствующие специальные разрешения, могут создать базу данных. Синтаксис команды CREATE DATABASE: CREATE DATABASE наименование_базы_данных [ ON [PRIMARY] ([ NAME = логическое_имя_файла,] FILENAME = ‘имя_файла_для_операционной_системы’ [, SIZE = размер] [, MAXSIZE = {максимальный_размер | UNLIMITED}] [, FILEGROWTH = размер увеличения [MB|K8|%]) [ {FILEGROUP наименование_файловой группы FILEDEFINITIONS} [,..n] ] [LOG ON { [NAME = логическое_имя_файла, ] FILENAME = ‘имя_файла_для_операционной_системы' [, SIZE = размер] [, MAXSIZE = {максимальныйразмер | UNLIMITED} ] [, FILEGROWTH = размер_увеличения] } [,...n] [FOR LOAD | FOR ATTACH] Когда вы создаете базу данных и опускаете параметры, относящиеся к первичному файлу или файлу журнала (или оба этих параметра), SQL Server создаст их автоматически. Физические файлы будут на¬ходиться в каталоге для хранения данных, используемом по умолча¬нию, и им будут присвоены имена: база_данных.mdf для первичного файла и база_данных_log.ldf для файла журнала. Если вы не определили размер файла, то размер первичного файла будет соответствовать размеру первичного файла базы данных model. Файлы журнала и вторичные файлы будут иметь размер по 1 Мбайт. Размер может быть и больше, если база данных model содержит в себе информацию, которая определяет больший размер новой базы данных. Хотя указывать ключи, относящиеся к файлам и их размерам, не обя¬зательно, на практике лучше это сделать. SQL Server создает базу данных в два этапа. Вначале SQL Server дела¬ет копию базы данных model и переименовывает ее в соответствии с указаниями для новой базы данных. На втором этапе происходит инициализация всех неиспользуемых страниц. Вот краткий список параметров команды CREATE DATABASE: ¦ PRIMARY определяет файл как первичный или как член первич¬ной файловой группы. Когда вы создаете таблицы и индексы, для их хранения используется первичная файловая группа, если вы не дали других указаний. ¦ NAME определяет логическое имя файла. По умолчанию это имя совпадает с физическим именем файла, определяемым параметром FILENAME. ¦ FILENAME указывает полный путь и имя физического файла. ¦ SIZE показывает начальный размер файла. Минимальный размер файла журнала составляет 512 Кбайт. ¦ MAXSIZE указывает максимальные размер, до которого может увеличиваться файл. ¦ UNLIMITED позволяет файлам увеличивать размер без каких-либо ограничений. ¦ FILEGROWTH определяет приращение в мегабайтах (MB), килобайтах (К) или процентах (%). По умолчанию размер приращения — 10%. Если вы не определили, в каких единицах задаете приращение, то по умолчанию ваша цифра будет воспринята в ме¬габайтах. ¦ FOR LOAD обеспечивает обратную совместимость. ¦ FOR ATTACH сообщает, что файл базы данных уже существует. Пользователь, создавший базу данных, становится ее владельцем. Все настройки базы данных копируются из настроек базы данных model, кроме случая, когда база данных создается с параметром FOR ATTACH. Тогда настройки базы данных берутся из уже существующих файлов базы данных. /* создание базы даных с размерами и размещением файлов по умолчанию */ CREATE DATABASE test1 I* размер базы данных - 2 Мбайт, размер файла журнала - по умолчание */ CREATE DATABASE test2 ON (FILENAME = 'c:\d1.mdf', SIZE = 2, NAME = 'd1) /* размер базы данных - 20 Мбайт, размер первичного файла 10 Мбайт, одна файловая группа - g1, файл журнала - 10 Мбайт */ CREATE DATABASE test3 ON PRIMARY (FILENAME ='c:\test3.mdf', SIZE = 10, NAME = ‘d1’) FILEGROUP g1 (FILENAME ='c:\g1.mdf’, SIZE = 10, NAME = 'g1') LOG ON (FILENAME ='c:\test3.ldf’, SIZE = 10, NAME = 'log1')

11. Создание таблицы в SQL Server 7.0 (объяснить синтаксис команды, перечислить способы создания таблиц)

Создать БД в SQL-сервере можно 2-мя способами: • При помощи SQL Server Enterprise Manager; • при помощи SQL Query Analizer: Таблицы создаются командой CREATE TABLE. Создавать таблицы может любой пользователь, получивший это право (от системного администратора или владельца базы данных) или обладающий ролью системного администратора. Синтаксис команды CREATE TABLE: CREATE TABLE имя_таблицы ( <описание столбца>, |имя_столбца AS выражение для вычисления |<ограничения на таблицу> [,..n] описание столбца имя_столбца тип_данных [default значение_по_умолчанию] [IDENTITY (начальное_значение, приращение)] [<ограничения_столбца>] ограничения столбца CONSTRAINT имя_ограничения [NULL | NOT NULL] | [PRIMARY KEY | UNIQUE] | [FOREIGN KEY REFERENCES имя_таблицы(столбец)] | [CHECK (ограничение на значения столбца)] ограничения таблицы CONSTRAINT имя_ограничения [{PRIMARY KEY | UNIQUE} (столбец)] | [FOREIGN KEY (столбец,…) REFERENCES имя_таблицы(столбец)] | [CHECK (ограничение на значения столбца)] Вычисляемый столбец – виртуальная колонка, на ее хранение не выделяется место на диске. Выражение определяющее виртуальную колонку может включать арифметические операторы, функции, имена колонок. В этой колонке нельзя изменять данные, нельзя накладывать ограничения. CREATE TABLE MYTABLE (My_ID smallint IDENTITY (1,2) PRIMARY KEY, [name] varchar(12) NOT NULL, surname varchar (20), city varchar(15) default 'BREST', Age smallint NOT NULL, CONSTRAINT My_Constr CHECK (age between 16 and 65))

12. Ограничения на значения колонок

Ограничения накладывают определенные условия на данные, вводимые в таблицу. Условием может быть уникальность значения, принадлежность некоторому интервалу или наличие первичного ключа, соответствующего внешнему ключу (ограничение ссылочной целостности). SQL Server поддерживает четыре типа ограничений (PRIMARY KEY, FOREIGN KEY, CHECK и UNIQUE). Все четыре типа могут быть объявлены в определении столбца или присоединены в конце определения таблицы. (Некоторые относят к ограничениям значения по умолчанию – DEFAULT и возможность содержать неопределенные значения NULL) Primary Key – определение колонки или группы колонок, которая уникальным образом определяет строку внутри таблицы. Foreign Key – поле таблицы, каждое значение которого в текущем состоянии таблицы всегда совпадает с каким-либо значением поля, являющимся ключом в другой таблице. Unique – альтернативный механизм, обеспечивающий уникальность значений колонки. Check – механизм определяется проверочным ограничением, осуществляется контроль над вводимыми в колонку значениями. Для этого необходимо определить диапазон возможных значений. Default – определение значения по умолчанию. NULL - неопределенное значение. Identity – авто инкремент. Задает начальное значение и величину приращения. GUI – глобальный уникальный идентификатор. Для колонки необходимо определить тип данных UNIQUEIDENTIFIER.

13. PRIMARY KEY как ограничение на столбец

Ограничение PRIMARY KEY (первичный ключ). Поля с таким ограничением не могут принимать NULL-значений, и даже если ограничение NOT NULL не указывается для такого поля, SQL добавляет его по умолчанию. Для этого ограничения по умолчанию также устанавливается CLUSTERED, что означает создание кластерного индекса, если таковой не установлен для какого-либо столбца с ограничением UNIQUE, т.е. с уникальными значениями. Ограничение PRIMARY KEY автоматически требует уникальности вводимых данных. Будучи назначенным для нескольких столбцов (составной первичный ключ) это ограничение задает уникальность комбинаций соответствующих значений, хотя по отдельности значения в каждом столбце составного ключа не обязательно должно быть уникальным. CREATE TABLE authors_short (authorjd int NOT NULL CONSTRAINT author_pk PRIMARY KEY, lastname varchar(40) NOT NULL, firstname varchar(20) NULL) CREATE TABLE authors_short (authorjd int NOT NULL, lastname varchar(40) NOT NULL, firstname varchar(20) NULL, CONSTRAINT author_pk PRIMARY KEY (authorjd))

14. FOREIGN KEY как ограничение на столбец

Ограничение FOREIGN KEY (внешний ключ) обеспечивает принцип ссылочной целостности. Когда столбец является внешним ключом, он определенным образом связан с таблицей, на которую он ссылается. Фактически это означает, что каждое значение в этом столбце (внешнем ключе) непосредственно привязано к значению в другом столбце (родительском ключе). Каждое значение (каждая строка) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это так, то система будет в состоянии справочной целостности. Понятно, что любое число внешних ключей может ссылать к единственному значению родительского ключа. Понятно также, что в качестве родительского ключа могут выступать столбцы с ограничениями PRIMARY KEY или UNIQUE, т.е. столбцы с уникальными значениями и не содержащие NULL-значений. Внешний ключ может содержать только те значения, которые фактически представлены в родительском ключе или NULL-значения, попытка ввода других значений приводит к ошибке. Присутствие NULL-значений во внешнем ключе не нарушит ссылочную целостность, но даст возможность не вводить данные, если они пока не известны. CREATE TABLE book_author (book_name varchar(30) NOT NULL, AuthoMd int NOT NULL CONSTRAINT author_fk REFERENCES authors_short (authoMd)) CREATE TABLE book_author (book_name varchar(30) NOT NULL, authoMd int NOT NULL, CONSTRAINT author_fk FOREIGN KEY (authoMd) REFERENCES authors_short ( authoMd)) 1)создает таблицу, содержащую имена книг и имена авторов. Ограничение REFERENCES требует, чтобы значение authoMd уже присутствовало в таблице authors_short. 2)устанавливается ограничение уровня таблицы, поэтому в нем использована форма с ключевым словом FOREIGN_KEY, за которым указывается проверяемый столбец (столбцы) таблицы book_authors. Ограничение REFERENCES определяет таблицу authors_short и столбец authoMd, в котором должны находиться существующие данные.

15. Уникальность данных как ограничение на столбец

Ограничение UNIQUE (уникальный), как и ограничение PRIMARY KEY, приводит к автоматической генерации индекса, но не кластерного, т.е. по умолчанию устанавливается NONCLUSTERED. Если для поля с ограничением UNIQUE, необходимо создать уникальный индекс, то необходимо установить альтернативный режим для PRIMARY KEY, т.к. для данной таблицы может быть создан только один кластерный индекс. Как и ограничение PRIMARY KEY, ограничение UNIQUE может быть ограничением таблицы, и тогда оно определяет уникальность комбинаций значений соответствующих столбцов.

16. Определение идентификационной колонки IDENTITY.

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

17. Определение проверочных ограничений и значений по умолчанию

Ограничения представляют собой некоторые условия, налагаемые на столбцы, таблицы и т.д. и гарантирующие, что информация будет подчиняться определенным правилам целостности данных. Особенность ограничений проверки состоит в том, что их применение не ограничивается отдельными столбцами. Ограничения проверки можно применять и к единственному столбцу, а также можно их использовать для выполнения проверки значений одного столбца в зависимости от значений другого. Можно проверить на соответствие определенному критерию любую комбинацию полей данной записи. Ограничение задается при помощи правил, аналогичным используемым в параметре WHERE. Примеры: задача SQL-условие ограничить значения месяцев допустимыми значениями BETWEEN 1 AND 12 ограничение списка отдельными записями IN(‘ZNAC1’, ‘ZNACH2’, ‘ZNACH3’) корректная запись (номер телефона) LIKE ‘[2-4] [0-9] [0-9] [0-9] [0-9] [0-9]’ цена должна быть положительным числом Cena>=0 CHECK(TableID IN (1, 3, 5, 7, 11)). Существует два типа значений по умолчанию(default): значения по умолчанию, которые являются объектами, и которые описывают определенный столбец таблицы. Назначение обоих этих типов одинаковое. Если во время добавления записи, значение поля не будет указано и это поле имеет значение по умолчанию, тогда значение будет вставлено автоматически, как определено по умолчанию. /* Объявление на уровне столбца */ CREATE TABLE book_sales (book_id int NOT NULL, Volume int NOT NULL CONSTRAINT vol_def DEFAULT 0) /* Объявление на уровне таблицы */ CREATE TABLE book_sales (book_id int NOT NULL, Volume int NOT NULL, CONSTRAINT vol_def DEFAULT 0 FOR Volume) Обратите внимание: при объявлении на уровне таблицы DEFAULT сопровождается ключевым словом FOR, определяющим конкретный столбец таблицы.

18. Индексы

Индексы – это механизмы, обеспечивающие высокую скорость поиска данных, представляют собой дополнение к таблице, помогающее ускорить поиск данных а счёт их физического или логического представления. Физический индекс – представляет собой набор значений их индексируемой колонки, с указанием на место физического размещения исходных строк в структуре БД. В индексе хранятся ссылки на строки. Использование индексов позволяет избежать полного сканирования таблиц. Индекс – набор ссылок, упорядоченный по определённой колонке, которая называется индексируемой. Индекс это самостоятельный объект, когда обновляются данные, индекс тоже перестраивается, это занимает дополнительное время, поэтому не стоит создавать в таблице более 4-5 индексов. Кластерный индекс. В кластерном индексе физическое расположение данных перестраивается в соответствии со структурой индекса, т.е. информация об индексе и сами данные физически располагаются вместе, и потому в таблице может быть 1 кластерный индекс. По структуре напоминает словарь. При создании первичного ключа для него автоматически создаётся кластерный индекс, если до этого не был определён другой кластерный индекс в таблице. Плюс: кластерный индекс обеспечивает наиболее быстрый поиск. Минус: перестроение физической структуры требует больших временных затрат и затрат ресурсов. Некластерный индекс. Этот индекс не перестраивает структуру таблиц, а лишь организует ссылки на строки с помощью указателей. Если в таблице определён кластернй индекс, то некластерный индекс ссылается на него. При перестроении кластерного индекса, некластеный индекс меняют своё значение. При удалении кластерного индекса у некластерного индекса устанавливаются ссылки на физическое расположение строк в БД. Уникальный индекс. Гарантирует уникальное значение в индексируемой колонке. Можно сказать, что это свойство двух предыдущих индексов. Он определяет уникальное значение в индексируемой колонке.

19. Составные части SQL (встроенный и интерактивный, DML, DDL)

Существует две формы языка – интерактивная и встроенная. Встроенный язык состоит из команд SQL, встроенных внутрь программ, написанных на каком-либо другом языке программирования. Это повышает гибкость и мощность программ. Интерактивный используется для создания SQL запросов и получения результатов в интерактивном режиме. Интерактивный и встроенный подразделяются на 2 части: 1) DDL –Data Definition Language – язык определения объектов – дает возможность создания, изменения, удаления различных объектов БД. 2) DML – Data Manipulation Language – предоставляет возможность выборки информации из БД и ее преобразования.

20. Отличие языка SQL от процедурных языков программирования

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

21. Оператор SELECT (объяснить синтаксис команды)

Оператор SELECT предназначен для выборки информации из таблицы. SELECT [ALL | DISTINCT][TOP n [PERCENT]] < select list >. [INTO new table] FROM table_source [WHERE search_condition] [GROUP BY group_condition] [HAVING search_condition] [ORDER BY order_expression [ASC|DESC] ] ALL – разрешает включение дублирующих строк, по умолчанию используется этот параметр. DISTINCT – запрещает использование дублирующих строк. TOP n – предписывает выбрать только n первых строк. PERCENT – выбрано n % от общего кол-ва строк, удовлетворяющих условию. table_name.col_name – выбрать колонку col_name из таблицы table_name Такая конструкция часто используются, когда в выходном наборе присутствуют одноименные колонки из разных таблиц. Если её не использовать в данном случае, появится сообщение об ошибке. alias – псевдоним, позволяющий изменить имена колонок, которые используются в выходном наборе. expression – в выходном наборе можно использовать функции и выражения. INTO NewTable – при указании этого раздела результат выполнения запроса будет сохранен в новой таблице, которая создается автоматически в соответствии с указанной в запросе строкой. FROM указывает источник извлечения данных. FROM table_name as table_alias [;…n] WHERE позволяет задать выражения, принадлежащее позволяет задать выражение, принимающее значение истина/ложь и в соответствии с которым будут выбираться из источника данных записи в выходной набор. Задаётся предикат (условие отбора) в выборку, являющуюся результатом отбора, включаются только те записи, для которых указанный предикат принимает значение TRUE. GROUP BY позволяет выполнить группировку строк таблицы по определенным критериям. Для каждой группы можно применять специальные функции агрегирования, которые в свою очередь будут применены ко всем строкам каждой сформированной группы отдельно. В условии группировки раздела GROUP BY обычно указывают имя колонки или нескольких колонок, по которым ведется группировка. В этом разделе указываются все столбцы, приведенные в разделе SELECT. В случае, когда используется более одного столбца, строки сначала группируются по значениям 1го столбца, затем по значениям 2го и т.д. (Count, Sum, Avg, Max, Min) HAVING определяет критерий, по которому группу следует включать в выходные данные. Этот раздел аналогичен разделу WHERE, который применяется для отбора отдельных строк. Только HAVING применяется для отдельных групп. ORDER BY используется, когда необходимо отсортировать данные в результирующем наборе. В разделе указывается список колонок, по значениям которых будет осуществляться сортировка. ASC – параметр, указывающий, что данные будут отсортированы по возрастанию, DESC – по убыванию.

22. Агрегирующие функции

Функции агрегирования: AVG () – вычисляет среднее значение для всех значений поля группы или множества. COUNT () – определяет кол-во строк или значений данного поля в группе. NULL – значения не учитываются SUM() – вычисляет арифметическую сумму всех выбранных значений данного поля. MAX(),MIN – вычисляет наибольшее и наименьшее значение из всех выбранных значений данного поля. Эти функции работают с множествами.

23. Оператор EXISTS

EXISTS генерирует значение "истина", либо "ложь", используя запросы в качестве аргумента. Этот оператор оценивает результат выполнения запроса как истинный, если запрос генерирует выходные данные, и ложный, если выходной набор запроса является пустым множеством. Пример: Выдать идентификаторы студентов, получивших хотя бы одну неудовлетворительную оценку("2"). SELECT student_id,surname FROM STUDENT A WHERE EXISTS (SELECT * FROM EXAM_MARKS B WHERE A.Student_id=B.Student_if AND mark=2)

24. Операторы ALL,ANY,BETWEEN,IN

ALL, ANY, BETWEEN, IN - логико-множественные операторы, возвращающие логическое значения сравнения скалярного выражения с элементами множества: между ("BETWEEN"), например, Year BETWEEN 1920 AND 1930 или обратное условие Year NOT BETWEEN 1853 AND 1855 - оператор применим для любых перечислимых типов данных. Все ("ALL"), некоторый ("ANY"), в ("IN") – входит ли значением в множество например, CHECK(TableID IN (1, 3, 5, 7, 11)). Название Пример синтаксиса Описание ANY SELECT c1 FROM t1 WHERE c1> ANY (SELECT c1 FORM t2) возвращает true, если сравнение истинно для любой строки в подзапросе IN SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FORM t2) Эквивалент =ANY SOME SELECT c1 FROM t1 WHERE c1 > SOME (SELECT c1 FORM t2) Другое название ANY ALL SELECT c1 FROM t1 WHERE c1 > ALL (SELECT c1 FORM t2) возвращает true, если сравнение истинно для всех строк в подзапросе

25. Вложенные подзапросы

SQL позволяет использовать одни запросы внутри других запросов, т.е. вкладывать запросы друг в друга. SQL-запрос с вложенным подзапросом работает следующим образом: 1. выбирается очередная строка внешнего запроса. 2. выполняется подзапрос и полученное значение применяется для анализа условия в разделе WHERE запроса; 3. в результате оценки этого условия принимается решение о включении/не включении строки в состав выходных данных; 4. процедура повторяется для следующей строки. Пример: Найти студентов, которые живут в городе, где есть 2 и более университета. SELECT Student_id FROM Student WHERE CITY IN (SELECT City FROM University GROUP BY City HAVING COUNT(City)>1)

26. Связанные подзапросы

При использовании подзапросов во внутреннем запросе можно ссылаться на таблицу, имя которой указано в разделе FROM внешнего запроса. В этом случае внутренний запрос выполняется по одному для каждой строки таблицы основного запроса (внешнего) Пример: Выбрать идентификаторы студентов, которые учатся не в том городе, где живут. SELECT Student_id FROM Student A WHERE City IN (SELECT City FROM University B WHERE (A.University_ud=B.Univ_id) AND (A.City<>B.City)) Связанный подзапрос также можно использовать в HAVING. Пример: по данным из таблицы Exam_marks определить сумму полученных студентом оценок по датам экзаменов, исключая те дни, когда число студентов сдававших в течении дня экзамены было меньше 10. SELECT Exam_Date, Sum(Mark) FORM Exam_marks A GROUP BY Exam_Date HAVING 10<(SELECT Count(Mark)) FROM Exam_marks B Where A.Exam_Date=B.Exam_Date)

27. Оператор UNION

Этот оператор позволяет соединить результаты два и более SQL запросов для получения на основе их выходных данных единого множества строк. Все объединяемые запросы должны иметь одинаковую структуру: одинаковое количество столбцов и совместимые типы данных у столбцов на соответствующих позициях. Если NULL значения запрещены для столбцов хотя бы одного запроса, то они должны быть запрещены и для всех соответствующих столбцов других запросов объединения. SELECT lecturer_id, surname, name, city FROM LECTURER UNION SELECT Student_id, surname, name, city FROM STUDENT Формат оператора: SELECT_выражение UNION [ALL] SELECT_выражение Параметр ALL позволяет в выходном запросе использовать дубликаты строк.

28. Команды INSERT, UPDATE, DELETE.

Команда INSERT осуществляет вставку данных в таблицу. INSERT INTO table_name VALUES (values_list) Чтобы указанная операция могла быть выполнена: 1) должна существовать таблица, в которую вставляются данные; 2) порядок и тип вставляемых данных должен соответствовать порядку и типу полей таблицы. INSERT INTO STUDENT VALUES (101, 'Иванов', 'Александр', 200, 3, 'Брест', 15) Если же структура вставляемой строки не соответствует структуре таблицы, то команда INSERT будет иметь следующий вид: INSERT INTO table_name [(column_list)] VALUES (values_list) INSERT INTO STUDENT (student_id) VALUES (101) В столбцах не указанных в команде должно присваиваться значение NULL. Если для столбца не определена NULL значения то строка не будет вставлена. Также в команде INSERT можно использовать оператор SELECT (подзапросы). Это позволяет вставить сразу несколько строк в одну таблицу, при этом количество и тип возвращенных подзапросом столбцов должен соответствовать количеству и типу столбцов таблицу, в которую будут вставлены значения. INSERT INTO STUDENT1 SELECT * FROM STUDENT WHERE city='Брест' Команда DELETE – осуществляет удаление строк из таблицы. DELETE FROM table_name WHERE search_condition Примеры: 1) DELETE FROM EXAM_MARKS 2) DELETE FROM EXAM_MARKS WHERE student_id = 103 Команда UPDATE – позволяет обновить значения одного или всех полей существующей таблицы. UPDATE table_name SET column_name = expression [,…] WHERE search_condition Пример: UPDATE UNIVERSITY SET rating=rating+200 WHERE rating>100

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

Если в операторе SELECT после ключевого слова FROM указывается не одна, а две таблицы, то в результате выполнения запроса, в котором отсутствует предложение WHERE, каждая строка одной таблицы будет соединена с каждой строкой другой таблицы. Эта операция называется декартовым произведением таблиц. Эта операция не имеет практического значения и при ошибочном использовании может привести к непредсказуемым результатам. Соединение таблиц имеет смысл, если не все строки входят в результирующий набор, а только необходимые. Это может осуществляться с помощь предложения WHERE. Получить фамилии студентов и для каждого студента названия университетов расположенных в городе, где живет студент. SELECT surname, univ_name FROM STUDENT, UNIVERSITY WHERE STUDENT.city = UNIVERSITY.city ORDER BY surname Ту же самую задачу можно реализовать с помощью оператора JOIN SELECT surname, univ_name FROM STUDENT INNER JOIN UNIVERSITY ON STUDENT.city = UNIVERSITY.city ORDER BY surname SELECT surname, univ_name FROM STUDENT LEFT OUTER JOIN UNIVERSITY ON STUDENT.city=UNIVERSITY.city ORDER BY surname При использовании INNER JOIN в выходной набор попадают только строки удовлетворяющие условию, сформулированному после ключевого слова ON. Если же выходной набор необходимо расширить записями, которые не удовлетворяют условию, то используется FULL OUTER JOIN. Существует так называемое внешнее соединение таблиц. Это ситуация, когда записи полученные в результате отбора дополняются записями, отброшенными в результате отбора. Эта операция называется OUTER JOIN. Различают LEFT OUTER JOIN – при таком внешнем соединении записи вошедшие в выходной набор дополняются записями, не вошедшими в выходной набор из таблицы указанной слева; RIGHT OUTER JOIN – при таком внешнем соединении идет аналогичное дополнение за счет данные из таблицы указанной справа.

30. Управляющие конструкции Transact -SQL.

BEGIN … END BEGIN SELECT * FROM Students SELECT * FROM University PRINT 'Data'+CAST(GetDate() as Char(10)) END IF … ELSE IF (MONTH(GetDate()))>5 AND (MONTH(GetDate())<9) PRINT 'Сейчас лето' ELSE PRINT 'Сейчас не лето' CASE … END DECLARE @@var1 char(10) SET @@var1=(SELECT state FROM authors WHERE name=’Mike’) PRINT CASE @@var1 WHEN ‘CA’ THEN ‘Калифорния’ WHEN ‘IN’ THEN ‘Индиана’ ELSE ‘Не понятно’ END WHILE … BREAK … CONTINUE DECLARE @@var1 smallint SET @@var1=1 WHILE 1=1 BEGIN PRINT 'Hello' SET @@var1=@@var1+1 IF @@var1<8 CONTINUE BREAK END

31. Представления

Таблицы, о которых шла речь до сих пор, обычно называются базовыми таблицами. Они содержат реальные данные. Но существует и другой вид таблиц, они не содержат никаких собственных данных. Содержимое их является результатом запроса, заданного при их описании. Такие таблицы называются представлениями. Представления иногда называются именованными запросами. Причем заданный в представлении запрос выполняется всякий раз, когда представление становится объектом SQL-команды. Представления позволяют: 1) ограничить доступ пользователей к конфиденциальным данным. Если в представление не включается колонка исходной таблицы, то на таблицу наложен вертикальный фильтр. Если в представлении присутствуют условия отбора строк, то на представление наложен горизонтальный фильтр. 2) объединять данные из нескольких взаимосвязанных таблиц. Представление является объектом SQL Server, чтобы иметь возможность работать с этим объектом у пользователя должны быть соответствующие права доступа к нему. Однако даже имея права доступа на представление, пользователь может не иметь права на изменения, т.к. представление может быть создано на основе таблиц владельцев, отличных от владельцев представления. Представление создается командой: CREATE VIEW view_name AS select_statement [with check option] with check option – предписывает выполнять проверку изменений производимых через представление на соответствие критериям производимым в параметре select_statement. Эта опция действует только внутри представления, в котором она определена. Если же на основе этого представления создано др. представление, то она уже в созданном представлении не действует. Модификация данных через представление. Данные базовых таблиц могут изменяться через представление с помощью команды DML. Если команды модификации могут осуществляться через представление, то представление называется обновляемым, в противном случае оно предназначено только для чтения при выполнении запросов. Критерии обновляемости представлений: 1) представление основывается только на одной базовой таблице; 2) запрос, на котором основывается представление, не должен содержать агрегирующую функцию, DISTINCT, разделы GROUP BY, HAVING. 3) не должно содержать вычисляемых колонок; 4) нельзя использовать раздел INTO, нельзя обращаться к временным таблицам; 5) не допускается выполнение изменений которые приведут к исчезновению строки в представлении. Если операция модификации предназначена для представления с вертикальным фильтром, то в поля базовой таблицы, не вошедшие в представление, будут вставляться NULL-значения либо значения по умолчанию. Если столбец имеет опцию NOT NULL, то генерируется сообщение об ошибке. CREATE VIEW hi_rating AS SELECT univ_id, rating FROM UNIVERSITY WHERE rating>400 WITH CHECK OPTION При попытке обновить значения поля rating значение <400 будет выдано сообщение об ошибке. CREATE VIEW my_rating AS SELECT * FROM hi_rating В представлении my_rating "with check option" не работает. UPDATE my_rating SET rating=400 WHERE univ_id = 18 При создании представлений могут использоваться подзапросы. CREATE VIEW max_mark AS SELECT b.exam_date, a.student_id, a.surname FROM STUDENT a, EXAM_MARKS b WHERE a.student_id = b.student_id AND b.mark = (SELECT max(mark) FROM EXAM_MARKS c WHERE c.exam_date = b.exam_date) Оператор UNION в представлении не допускается . Модификация представлений осуществляется командой ALTER VIEW, а удаление – DROP VIEW. Синтаксис этих команд аналогичен таким же командам для таблиц.

32. Хранимые процедуры.

Предназначены для повышения эффективности взаимодействия клиента и сервера. Существует две модели взаимодействия клиента и сервера: 1) в рамках первой модели клиент отправляет блок команд серверу на выполнение и ожидает получение данных. Такой способ взаимодействия не совсем оптимален, т.к. на клиента ложиться дополнительная нагрузка, увеличивается время доступа к данным, если необходимо внести изменения в код, то их необходимо произвести на всех клиентских приложениях. 2) состоит в том, что блок команд хранится на сервере в виде специальных хранимых процедур. Сервер БД выполняет блок команд, составляющий тело этой процедуры и возвращает клиенту результат. Этот подход позволяет повысить скорость разработки приложений. При необходимости изменений блока команд меняется только код хранимой процедуры на сервере. Эти изменения никаким образом не отражаются на клиентском приложении, что делает процесс взаимодействия клиента и сервера более гибким. Хранимые процедуры – самостоятельные объекты БД, которые централизовано хранятся на сервере. Хранимые процедуры бывают: 1) системные хранимые процедуры (SP) – эти процедуры охватывают все аспекты управления и конфигурации сервера. Настоятельно рекомендуется делать изменения в системных таблицах с помощью хранимых процедур. Практически любое действие по администрированию SQL сервера реализуется средствами системных процедур. Хранятся в БД master. 2) временные хранимые процедуры (#, ##) – хранятся в БД (Temp.db). Доступны в течении того времени, пока активно соединение, в рамках которого эти процедуры созданы. 3) пользовательские хранимые процедуры - хранятся и выполняются внутри БД, для которой были определены. Хранимая процедура может иметь входные и выходные параметры. Код процедуры может создать последовательность любых команд, в том числе и вызов других хранимых процедур. Синтаксис команды создания хранимой процедуры : CREATE PROC[EDURE] procedure_name [{@parameter data_type} [VARYING] [= default] [OUTPUT]] [,...n] AS sql_statement [...n] procedure_name - используя sp_, #, ## можно определить создаваемую процедуру как системную или временную @parameter - имя параметра (входного либо выходного) data_type – определяет тип данных параметра VARYING – определяет, что в качестве выходного параметра будет представлено результирующее множество. OUTPUT – определяет указанный параметр как выходной Default- определяет для параметра значение по умолчанию, которое будет использоваться, если указанный параметр при вызове процедуры был опущен. AS определяет начало тела процедуры. Здесь можно использовать любые команды, включая вызов других хранимых процедур , за исключением команд, начинающихся с ключевого слова CREATE. Пример: Создать хранимую процедуру выводящую оценку студента по фамилии. CREATE PROCEDURE Select_Marks @stud_surname varchar(20) AS SELECT Mark FROM Exam_marks A, Student B WHERE a.student_id=b.student_id AND Surname=@Surname Exec Select_Marks ‘Иванов’ Удаление: DROP PROCEDURE имя_процедуры

33. Триггеры

Триггер – это хранимая процедура, реагирующая на определенный тип команд, применяемый к данным таблицы. Существует три вида триггеров : 1. INSERT TRIGGER запускается при попытке вставки данных с помощью команды INSERT 2. UPDATE TRIGGER запускается при попытке изменения данных 3. DELETE TRIGGER запускается при попытке удаления данных В SQL Server 7.0 триггер не может быть определен для представления. Создание триггера 1. С помощью команды Transact-SQL и редактора Query Analyzer CREATE TRIGGER trigger_name ON table_name FOR { [DELETE] [,] [INSERT] [,] [UPDATE] } AS sql_statement [...n] trigger_name- имя создаваемого триггера table_name- имя таблицы, для которой создается триггер FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }- параметры определяют, на какой вид команд модификации будет реагировать триггер sql_statement- sql-оператор(ы), определяющий тело триггера 2. С помощью Enterprise Manager В контекстном меню соответствующей таблицы выбрать All Tasks – Manage Triggers… В открывшемся окошке пишется соответствующий код триггера. Как работает триггер Когда пользователь пытается, например, изменить данные в таблице, сервер автоматически запускает триггер, и только если он завершается успешно, разрешается выполнение изменений. Т. е. при запуске триггера исходная таблица находится в состоянии, в которое ее привело бы успешное выполнение триггера (все изменения , добавления и удаления строк выполнены). Если триггер обнаружил, что хоть одна вставляемая строка не отвечает ограничениям целостности, то все строки не будут вставлены. Дело в том, что триггер выполняется как неявно определенная транзакция, а требование транзакции – должны быть выполнены все модификации, либо ни одной. Когда сервер начинает выполнение триггера, он создает две специальные таблицы: inserted и deleted. Структура этих таблиц аналогична структуре таблицы, для которой определен триггер.

• При выполнении команды INSERT: в таблице inserted будут содержаться все строки, которые пользователь пытается вставить в таблицу. Таблица deleted не будет содержать ни одной строки.

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

• При выполнении команды UPDATE : deleted содержит старые значения, а inserted – новые. Применение триггеров

В Microsoft SQL Server 7.0 с помощью ограничений целостности, значений по умолчанию и т. д. не всегда можно достичь нужного уровня функциональности при изменении данных в таблицах. Особенно это касается связанных данных из нескольких таблиц и их каскадных изменений. Для решения этих и многих других проблем используются триггеры. Главная задача триггеров – в том, чтобы отобразить изменения данных основной таблицы на все подчиненные. Полезные сведения о триггерах В связи с выше сказанным,следует отметить, что при обеспечении каскадных изменений в таблицах SQL Server 7.0 с помощью триггеров, невозможно для изменяемых столбцов определить одновременно ограничение целостности PRIMARY KEY -–FOREIGN KEY и триггер, обеспечивающий автоматические изменения данных в столбцах одновременно. ПОЧЕМ? Для этого необходимо воспользоваться иными механизмами ограничения целостности. КАКИМИ? Триггер выполняется как неявно определенная транзакция, поэтому внутри него допускается применение команд управления транзакциями: ROLLBACK TRANSACTION- откат транзакции восстанавливается первоначальное состояние системы без внесения каких либо изменений, COMMIT TRANSACTION- определяет конец транзакции. Если в теле транзакции не было ошибок, все изменения, сделанные в транзакции фиксируются.

1...2

На главную

вверх