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

OLE DB, называются OLE DB провайдерами. Программы-клиенты, использующие для

доступа OLE DB провайдеры, называются потребителями данных.

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

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

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

12. В архитектуре оdвс используется один оdвс Driver Manager и несколько оdвс-

драйверов, обеспечивающих доступ к конкретным СУБД Driver Manager связывает при-

ложение и интерфейсные объекты, которые выполняют обработку SQL-за-Мросов к кон-

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

что ипозволяет использовать ODBС-механизмы для работы практически с любой

системой.

Однако этот способ также не лишеннедостатков:

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

граммного слоя);

-необходимы предварительная инсталляция и настройка ODВС-драйвера (указание драй-

вера СУБД, сетевого пути ксерверу, базы данных и т. д.) на каждом рабочем месте. Па-

раметры этой настройки являются статическими, т. е. приложение их изменить самостоя-

тельно не может.

13. ВсекомпонентыMicrosoftSQLServer 2008запускаютсяизменю«Пуск\ Программы\

MicrosoftSQLServer2008. ВMicrosoftSQLServer2008входятследующиекомпоненты:

1. DeploymentWizard–мастерповыводуинформациихранимойнасервере;

2. SQL ServerInstallationCenter–сентрустановкиSQL Server2008;

3. Reporting Services Configuration Manager– менеджер службы настройки

отчётов;

4. SQL ServerConfigurationManager–менеджернастройкисервера;

5. SQLServerErrorand UsageReporting– службапротоколированияработы

сервераислужбаотчётовобошибках;

6. MicrosoftSamples Overview – ссылка на сайт корпорацииMicrosoft, где

можнопросмотретьпримерыработыссервером;

7. SQLServerBooksOnline- полная справочная система поMicrosoftSQL

Server2008. Онасодержит справки, как попрограммированию,так ипо

администрированиюсервера;

8. SQLServerTutorials–учебникипоработессервером;

9. DataProfileViewer–просмотрпрофилейпоработесданными;

10.ExecutePackageUtility–инструментыпо сжатиюданных;

11.DatabaseEngineTuningAdvisor–мастернастройкиядрабазыданных;

12. SQLServerProfiler–настройкапрофилейпоработесданными;

13. ImportandExportData–импорти экспортданных;

14. SQL Server Business Intelligence Development Studio – интегрированная

средаразработкиBusiness IntelligenceDevelopmentStudio;

15.SQL Server Management Studio – графическая оболочка для управления

серверомиразработкибазданных.

16. Все объекты Transact-SQL имеют имена, или идентификаторы. Для некоторых объектов

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

ограничений)–необязательны.

Стандартныеидентификаторы должны начинаться с символов: _, @, #. Остальные

символы могут быть строчными и прописными буквами латинского алфавита, цифрами,

символами $,_, @, #. В некоторых реализациях допускается использование национального

алфавита. В качестве имени нельзя использовать зарезервированные слова. Длина

идентификатора–от 1 до 128 символов.

Полное имя объектавключает имя сервера (server), базы данных (database), владельца БД

(owner_name), объекта БД(object_name). Обязательно указывается только имя объекта, по

умолчанию используется имя локального сервера, текущей БД, текущего пользователя.

Ограниченные идентификаторыне подчиняются правилам именования объектов и для

них применяются ограничители: квадратные скобки или двойные кавычки. Тело

идентификатора может содержать любую комбинацию символов текущей кодовой

страницы.

Временные объекты предназначены для хранения промежуточных данных. Такими

объектами в Transact-SQL служат переменные и временные таблицы. Переменная

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

DECLARE{@<имя переменной><тип переменной>}[,...n]

Например:DECLARE@sum int, @trand char

Глобальные переменные начинаются с@@.

14. SQL (Structured Query Language) – Структурированный Язык Запросов – стандартный язык запросов по работе с реляцион- ными базами данных. Язык был предложен компанией IBM в нача- ле 1970-х гг. для проверки возможностей реляционной модели. SQL в чистом (базовом) виде является информационно-логи- ческим языком, а не языком программирования. Однако стандарт языка спецификацией SQL/PSM предусматривает возможность его процедурных расширений, с учетом которых язык уже может рас- сматриваться в качестве языка программирования. Достоинства SQL: 1. Наличие международных стандартов. 2. Независимость от конкретной СУБД. Несмотря на наличие диалектов и различий в синтаксисе, в большинстве своем тексты SQL-запросов, содержащие DDL и DML, могут быть достаточно легко перенесены из одной СУБД в другую.5 3. Поддержка архитектуры клиент-сервер. 4. Распространенность. 5. Быстрое обучение. 6. Декларативность. С помощью SQL программист описывает только то, какие данные нужно извлечь или модифицировать. Ка- ким образом это сделать, решает СУБД непосредственно при обра- ботке SQL-запроса. Однако программисту полезно представлять, как СУБД будет разбирать текст его запроса. Чем сложнее сконст- руирован запрос, тем больше он допускает вариантов написания, различных по скорости выполнения, но одинаковых по итоговому набору данных. Недостатки SQL: 1. Неполное соответствие реляционной модели данных (нали- чие дубликатов, необязательность первичного ключа, возможность упорядочения результатов). 2. Недостаточно продуманный механизм неопределенных зна- чений. 3. Сложность формулировок и громоздкость.

15.Объекты Transact-SQL Все объекты Transact-SQL имеют имена, или идентификаторы. Для некоторых объектов (например, таблиц, представлений) идентификаторы обязательны, для других (например, ограничений) – необязательны. Стандартные идентификаторы должны начинаться с символов: _, @, #. Остальные символы могут быть строчными и прописными буквами латинского алфавита, цифрами, символами $,_, @, #. В некоторых реализациях допускается использование национального алфавита. В качестве имени нельзя использовать зарезервированные слова. Длина идентификатора – от 1 до 128 символов. Полное имя объекта включает имя сервера (server), базы данных (database), владельца БД (owner_name), объекта БД(object_name). Обязательно указывается только имя объекта, по умолчанию используется имя локального сервера, текущей БД, текущего пользователя. Ограниченные идентификаторы не подчиняются правилам именования объектов и для них применяются ограничители: квадратные скобки или двойные кавычки. Тело идентификатора может содержать любую комбинацию символов текущей кодовой страницы.Временные объекты предназначены для хранения промежуточных данных. Такими объектами в Transact-SQL служат переменные и временные таблицы. Переменная используется для хранения одиночных значений и создается с помощью команды DECLARE{@<имя переменной><тип переменной>}[,...n] Например: DECLARE @sum int, @trand char Глобальные переменные начинаются с@@. Для временного хранения больших объемов информации используются временные таблицы. Они бывают глобальные и локальные. Доступ к глобальной временной таблице может быть получен из любого соединения, и такие таблицы могут использоваться для обмена данными между различными приложениями. Имя таблицы должно быть уникальным в пределах сервера и должно начинаться с ##. Глобальная временная таблица уничтожается пользователем с помощью команды DROPTABLE или при закрытии соединения, в котором она создавалась. Локальные временные таблицы видны только из того соединения, в котором они были созданы. При закрытии соединения таблица уничтожается. Если локальная таблица создана в хранимой процедуре, то при закрытии процедуры она уничтожается. В разных соединениях могут создаваться локальные таблицы с одинаковыми именами. Имя таблицы должно начинаться с #. ВыражениявTransact-SQL могут включать константы, идентификаторы объектов, логические и арифметические операции, функции и т. д. выражения могут использоваться в качестве параметров хранимых процедур, команд Transact-SQL или запросов. Операндами в выражениях могут быть константы, переменные, функции, имена столбцов, подзапросы. Операторы: 1. унарные +, - 2. присваивания: SET @vartab = 13 3. арифметические:+,-, *, /, % - остаток от деления 4. конкатенация: + 5. сравнения: =, <, >, <=, >=, не равно != или <>, не более !>, не менее !< 6. битовые: AND (&), OR(|), XOR (^) 7. логические: ALL (TRUE, если условие выполняется для всего набора данных), AND, ANY (TRUE, если условие выполняется хотя бы для одного элемента из набора данных), BETWEEN(TRUE, если значение лежит в диапазоне), EXIST(TRUE, если подзапрос возвращает хотя бы одно значение), IN(TRUE, если значение входит в указанный список), LIKE, NOT, OR, SOME (TRUE, если условие выполняется хотя бы для одного элемента из набора данных)

16. Операторы: 1. унарные +, - 2. присваивания: SET @vartab = 13 3. арифметические:+,-, *, /, % - остаток от деления 4. конкатенация: + 5. сравнения: =, <, >, <=, >=, не равно != или <>, не более !>, не менее !< 6. битовые: AND (&), OR(|), XOR (^) 7. логические: ALL (TRUE, если условие выполняется для всего набора данных), AND, ANY (TRUE, если условие выполняется хотя бы для одного элемента из набора данных), BETWEEN(TRUE, если значение лежит в диапазоне), EXIST(TRUE, если подзапрос возвращает хотя бы одно значение), IN(TRUE, если значение входит в указанный список), LIKE, NOT, OR, SOME (TRUE, если условие выполняется хотя бы для одного элемента из набора данных)

17. Синтаксис оператора SELECT.В разделе SELECT могут быть вычисляемые поля, т. е. выражения Transact SQL. Для

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

идентификатор: <выражение>AS<имя поля>.

В разделе FROM могут фигурировать таблицы, представления, объединения. В

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

В разделе GROUPBY могут использоваться ключевые слова ALL, WITHCUBE,

WITHROLLUP.

При использовании условия фильтрации в запросе может возникнуть ситуация, когда в

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

результат. Ключевое слово ALLозначает, что пустые группы будут включаться в

результат.

18. / * Символом * можно выбрать все поля, а вместо имени поля применить выражение из нескольких имен ALL Если SELECT не содержит ни одного предиката, то подразуме- вается предикат ALL. Отбираются все записи, соответствующие условиям, заданным в инструкции SELECT. Приведенные ниже команды Transact SQL эквивалентны; они возвращают все записи из таблицы Students: SELECT ALL FROM Students SELECT * FROM Students DISTINCT Исключает записи, которые содержат повторяющиеся значения в выбранных полях. Чтобы запись была включена в результат выполнения запроса, значения в каждом поле, включенном в команду SELECT, должны быть уникальными. Например, в таблице Students есть однофамильцы. Если две записи содержат значение Иванов в поле FIO, то следующая команда возвратит только одну из них: SELECT DISTINCT FIO FROM Students; Если опустить предикат DISTINCT, этот запрос возвратит обе записи для фамилии Иванов. Результат выполнения команды SELECT, содержащей предикат DISTINCT, является необновляемым и не отражает последую- щие изменения, внесенные другими пользователями58 Окончание табл. 17 1 2 TOP n [PERCENT] Возвращает определенное число записей, находящихся в начале или в конце диапазона, описанного с помощью предложения ORDER BY. Следующая команда позволяет получить список 5 студентов с самой высокой стипендией SELECT TOP 5 FIO, Stipendiya FROM Students ORDER BY Stipendiya DESC; Результат выполнения запроса приведен на рисунке. Если предложение ORDER BY будет опущено, запрос возвратит произвольный набор 5 записей из таблицы Students, удовлетво- ряющих предложению WHERE. Можно использовать зарезервированное слово PERCENT для возврата определенного процента записей, находящихся в начале или в конце диапазона, описанного с помощью предложения ORDER BY. Например, вместо 5 студентов с самой высокой стипендией следует отобрать студентов, попавших в последние 5 процентов: SELECT TOP 5 PERCENT FIO, Stipendiya FROM Students ORDER BY Stipendiya ASC; Предикат ASC обеспечивает возврат последних значений. Значение, следующее после предиката TOP, должно быть числовым значением типа Integer без знака. Предикат TOP не влияет на возможность обновления запроса

19 // Ключевое слово AS служит оператором для присваивания псевдонимов (alias) таблицам или заголовкам столбцов ORDER BY ORDER BY сортирует данные выходного набора в заданной последовательности. Сортировка может выполняться по нескольким полям, в этом случае они перечисляются за ключевым словом ORDER BY через запятую. По умолчанию реализуется сортировка по возрастанию, она задается ключевым словом ASC. Для выполне- ния сортировки в обратной последовательности необходимо указать65 ключевое слово DESC. Фраза ORDER BY позволяет упорядочить выбранные записи в порядке возрастания или убывания значений любого столбца или комбинации столбцов, независимо от того, присутствуют эти столбцы в таблице результата или нет. Фраза ORDER BY всегда должна быть последним элементом в опера- торе SELECT.

23|| Предложение GROUP BY Группирование данных – это размещение данных в столбцах с повторяющимися значениями в определенном логическом порядке. Например, в базе данных содержится информация о студентах. Студенты учатся в разных группах. Вполне вероятно, что может по- надобиться информация по каждой группе и обучающихся там сту- дентах. Для этого следует сгруппировать информацию о студентах по группам. Предположим, что необходимо найти среднюю стипендию студентов по каждой группе. Это можно сделать, применив к столбцу Stipendiya итоговую функцию AVG, а затем использовать GROUP BY для группирования выводимых данных по группам. Запрос, в котором присутствует GROUP BY, называется груп- пирующим запросом. В нем группируются данные, полученные в результате выполнения команды SELECT, после чего для каждой отдельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы команда SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в команде SELECT фразы GROUP BY каждый элемент списка дол-70 жен иметь единственное значение для всей группы. Более того, команда SELECT может включать только следующие типы элементов: мена полей;и тоговые функции;и нстанты;ко выражения, включающие комбинации перечисленных выше элементов. Все имена полей, приведенные в списке предложения SELECT, должны присутствовать и во фразе GROUP BY – за ис- ключением случаев, когда имя столбца используется в итоговой функции. Однако во фразе GROUP BY могут быть имена столбцов, отсутствующие в списке предложения SELECT (но данные из этих столбцов не выводятся!) Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группированию подвер- гаются только те строки, которые удовлетворяют условию поиска. Стандартом SQL определено, что при проведении группиро- вания все отсутствующие значения рассматриваются как равные. Если две строки таблицы в одном и том же группируемом столбце содержат значение NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу. С помощью итоговых (агрегатных) функций в SQL-запросе можно получить ряд обобщающих статистических сведений о мно- жестве отобранных значений выходного набора. Пользователю доступны следующие основные итоговые функции: Count (Выражение) – определяет количество записей в вы- ходном наборе SQL-запроса; Min/Max (Выражение) – определяют наименьшее и наи- большее из множества значений в некотором поле запроса; Avg (Выражение) – эта функция позволяет рассчитать среднее для множества значений, хранящихся в определенном по- ле записей, отобранных запросом. Оно является арифметическим средним значением, т.е. суммой значений, деленной на их коли- чество. Sum (Выражение) – вычисляет сумму множества значений, содержащихся в определенном поле записей, отобранных запросом. Чаще всего в качестве выражения выступают имена столб- цов. Выражение может вычисляться и по значениям нескольких таблиц.67 Все эти функции оперируют со значениями в единственном столбце таблицы или с арифметическим выражением и возвращают единственное значение. Функции COUNT, MIN и MAX применимы как к числовым, так и к нечисловым полям. Функции SUM и AVG могут использоваться только в случае числовых полей. При вычислении результатов любых функций сначала исклю- чаются все пустые значения. После этого требуемая операция при- меняется только к оставшимся конкретным значениям столбца. Вариант COUNT(*) – особый случай использования функции COUNT. Назначение – подсчет всех строк в результирующей таб- лице, независимо от того, содержатся там пустые, дублирующиеся или любые другие значения. Если до применения обобщающей функции необходимо ис- ключить дублирующиеся значения, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT. Оно не имеет смысла для функций MIN и MAX, однако его использова- ние может повлиять на результаты выполнения функций SUM и AVG. Необходимо заранее обдумать, должно ли оно присутствовать в каждом конкретном случае. Ключевое слово DISTINCT может быть указано в любом запросе не более 1 раза. Особые случаи применения итоговых функций: SUM (DISTINCT <поле>) – суммирование различных значе- ний поля; AVG (DISTINCT <поле>) – среднее арифметическое разных значений поля; COUNT (DISTINCT <поле>) – подсчет количества разных значений поля; COUNT (<поле>) – подсчет количества ненулевых значений поля; COUNT (*) – подсчет количества строк в результате. Итоговые функции могут использоваться только в списке предложения SELECT и в составе предложения HAVING. Во всех других случаях это недопустимо.

24// Подзапросы Часто невозможно решить поставленную задачу путем одного запроса. Это особенно актуально, когда при использовании условия поиска в предложении WHERE значение, с которым надо сравни- вать, заранее не определено и должно быть вычислено в момент выполнения команды SELECT. В таком случае используют закон- ченные команды SELECT, внедренные в тело другой команды SELECT. Подзапрос – это запрос, содержащийся в выражении ключево- го слова WHERE другого запроса с целью дополнительных ограни- чений на выводимые данные. Подзапросы называют также вложен- ными запросами. Базовый синтаксис команды с подзапросом: SELECT имя_столбиа FROM таблица WHERE имя_столбца = (SELECT имя столбца FROM таблица WHERE условия); Подзапрос можно использовать в выражении ключевых слов WHERE или HAVING внешних операторов выбора SELECT, встав- ки INSERT, обновления UPDATE или удаления DELETE. Можно использовать логические операции и операции сравнения типа =, >, <, IN, NOT IN, AND, OR и т.п. 10.1. Правила составления подзапросов 1. Во фразах WHERE и HAVING подзапрос записывается как второй операнд условия отбора, т.е. после знака операции сравне- ния (=, <, >, <=, >=, <>). 2. Текст подзапроса заключается в круглые скобки. Подзапрос может ссылаться только на один столбец в выражении своего клю- чевого слова SELECT. Исключение – это случаи, когда в главном запросе используется сравнение с несколькими столбцами из подза- проса. 3. Ключевое слово ORDER BY нельзя использовать в подза- просе, а только во внешнем подзапросе. Вместо ORDER BY в под- запросе можно использовать GROUP BY.76 4. Подзапрос, возвращающий несколько строк данных, можно использовать только в операторах, допускающих множество значе- ний, например в IN. 5. Подзапрос нельзя использовать как аргумент функции, до- пускающей множество значений. 6. Подзапросы нельзя использовать в списках предложений ORDER BY и GROUP BY. 7. Список выбора внутреннего подзапроса, которому предше- ствует операция сравнения, может содержать только одно выраже- ние или название столбца, и подзапрос должен возвращать единст- венный результат. При этом тип данных столбца, указанного в кон- струкции WHERE внешнего оператора, должен быть совместим c типом данных в столбце, указанным в списке выбора подзапроса. 8. В подзапросах не допускаются текстовые (text) и графиче- ские (image) данные. 9. Подзапросы не могут обрабатывать свои результаты внут- ренним образом, т.е. подзапрос не может содержать конструкций ORDER BY или ключевого слова INTO. 10. Количество вложенных уровней для подзапросов не долж- но превышать 16. 11. Операцию BETWEEN нельзя использовать по отношению к подзапросу, но ее можно использовать в самом подзапросе. Пример правильного использования BETWEEN: SELECT имя_столбца FROM таблица WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя_столбца FROM таблица WHERE значение BETWEEN значение); Пример неправильного использования BETWEEN: SELECT имя_столбца FROM таблица WHERE имя_столбца BETWEEN значение AND (SELECT имя_столбца FROM таблица); 12. В предложении SELECT подзапроса нельзя использовать символ “*” и константы (кроме EXISTS-подзапросов).77 13. Имена столбцов в подзапросе относятся по умолчанию к таблице, указанной во фразе FROM подзапроса. Если они относятся к таблице внешнего запроса, необходимо задать полное (уточнен- ное) имя столбца в виде “таблица.столбец”. 14. Список в предложении SELECT состоит из имен отдель- ных столбцов или составленных из них выражений – за исключени- ем случая, когда в подзапросе присутствует ключевое слово EXISTS; 10.2. Типы подзапросов Существуют два основных типа подзапросов: подзапросы-выражения или скалярные подзапросы. Этим подзапросам предшествует немодифицированная операция сравне- ния. Они возвращают единственное значение;

25///Соединение таблиц Для соединения таблиц с одноименными столбцами или таб- лицы с самой собой используются алиасы или псевдонимы. Они за- даются во фразе FROM через пробел после имени таблицы. При этом истинное имя таблицы в базе данных не меняется. Например: SELECT R.a1, R.a2, S.b1, S.b2 FROM R t1, S t2 WHERE R.a1= S.b2; 12.1. Внутреннее соединение (INNER JOIN) При внутреннем естественном соединении группируются только те строки, значения которых по соединяемым (одноимен- ным) столбцам совпадают: SELECT R.a1, R.a2, S.b1, S.b2 FROM R, S WHERE R.a2=S.b1 или SELECT R.a1, R.a2, S.b1, S.b2 FROM R INNER JOIN S ON R.a2=S.b1; Пример 82. Объединить поля из таблиц Teachers и Groups при условии, что преподаватель является куратором группы: SELECT Familia, Imja, Surname, Groups.Kurator FROM Teachers INNER JOIN Groups ON Teachers.ID_Teacher Groups.Kurator; Результат выполнения запроса приведен на рис. 42. Рис. 42. Внутреннее соединение101 12.2. Внешнее соединение При внешнем соединении в результирующую таблицу поме- щаются не только парные строки, но и строки, не нашедшие себе пару. По способу добавления непарных строк различают: левое открытое соединение, когда непарные строки добав- ляются из таблицы, расположенной слева по отношению к опции JOIN ; правое открытое соединение, когда непарные строки добав- ляются из правой по отношению к JOIN таблицы; полное открытое соединение, когда добавляются все непар- ные строки обеих соединяемых таблиц. 12.2.1. Внешнее левое соединение LEFT JOIN При внешнем левом соединении в результирующий набор бу- дут выбраны все строки из левой таблицы (указываемой первой). При совпадении значений по соединяемым (одноименным) столб- цам значения второй таблицы заносятся в результирующий набор в соответствующие строки. При отсутствии совпадений в качестве значений второй таблицы проставляется значение NULL: SELECT R.a1, R.a2, S.b1, S.b2 FROM R LEFT JOIN S ON R.a2=S.b1; Пример 83. Соединить поле Familia из таблицы Teachers с полем Name_Group из таблицы Groups: SELECT Teachers.Familia, Groups.Name_Group FROM Teachers LEFT JOIN Groups ON Teachers.ID_Teacher=Groups.Kurator; 12.2.2. Внешнее правое соединение RIGHT JOIN При внешнем правом соединении в результирующий набор будут выбраны все строки из правой таблицы (указываемой вто- рой). При совпадении значений по соединяемым (одноименным) столбцам значения первой таблицы заносятся в результирующий набор в соответствующие строки. При отсутствии совпадений в ка- честве значений первой таблицы проставляется значение NULL: SELECT R.a1, R.a2, S.b1, S.b2 FROM R RIGHT JOIN S ON R.a2=S.b1;102 Пример 84. Объединить таблицы Lessons и Progress, исполь- зуя правое соединение по предметам: SELECT Nazvanie, Examen FROM Lessons RIGHT JOIN Progress ON Lessons.ID_Lesson=Progress.ID_ Lesson; Результат выполнения запроса приведен на рис. 43. Рис. 43. Внешнее правое соединение 12.2.3. Полное внешнее соединение FULL JOIN При полном внешнем соединении в результирующий набор будут выбраны все строки, как из правой, так и из левой таблицы. При совпадении значений по соединяемым (одноименным) столб- цам строка содержит значения как из левой, так и из правой табли- цы. В противном случае, вместо отсутствующих значений в столб- цы таблицы (левой или правой) заносится значение NULL. Пример 85. Объединить таблицы Teacher и Groups, используя полное соединение по преподавателям: SELECT Teachers.ID_Teacher, Familia, Imja, Surname, Groups.Kurator FROM Teachers FULL JOIN Groups ON Teachers.ID_Teacher=Groups.Kurator;

26|||Оператор INSERT INTO применяется для добавления записей в таблицу. Формат команды: < команда добавления >::= INSERT INTO <имя_таблицы> [ (имя_столбца [,...n] ) ] VALUES (значение[,...n]); где имя_таблицы представляет собой либо имя таблицы базы данных, либо имя обновляемого представления. Эта форма команды INSERT с параметром VALUES предна- значена для вставки единственной строки в указанную таблицу. Список столбцов указывает столбцы, которым будут присвое- ны значения в добавляемых записях. Список может быть опущен. Тогда подразумеваются все столбцы таблицы (кроме объявленных как счетчик), причем в по- рядке, установленном при создании таблицы. Если в команде INSERT указывается конкретный список имен полей, то любые пропущенные в нем столбцы должны быть объяв- лены при создании таблицы как допускающие значение NULL, за исключением тех случаев, когда при описании столбца использо- вался параметр DEFAULT. Список значений должен соответствовать списку столб- цов следующим образом: 1) количество элементов в обоих списках должно быть одина- ковым; 2) должно существовать прямое соответствие между позицией одного и то же элемента в обоих списках. Поэтому I элемент списка значений должен относиться к I столбцу в списке столбцов, II – ко II столбцу и т.д. 3) типы данных элементов в списке значений должны быть совместимы с типами данных соответствующих столбцов таблицы.51 Пример 23. Добавить в таблицу Teachers новую запись: INSERT INTO Teachers (Familia, Imja, Surname, Data_Rozhd, Adres, Stazh, ID_Kaf) VALUES ('Гурьянов', 'Лев', 'Вячеславович ',’1952- 07-07,' ул. Комсомольская, д.24 кв. 26, 30,1); Если столбцы таблицы Teachers указаны в полном составе и в том порядке, в котором они перечислены при создании таблицы Teachers, команду можно упростить. Пример 24. Добавить в таблицу Teachers новую запись: INSERT INTO Teachers VALUES

27|||Команда удаления Команда DELETE FROM удаляет данные из таблицы: DELETE FROM <имя_таблицы> [WHERE <условие_отбора>]; имя_таблицы представляет собой либо имя таблицы базы данных, либо имя обновляемого представления. условие_отбора – здесь задается условие, в соответствии с которым будут удаляться записи (подробнее см. подразд. 9.2). Если предложение WHERE присутствует, удаляются записи из таблицы, удовлетворяющие условию отбора. Если опустить предложение WHERE, из таблицы будут удалены все записи без предупреждения и без запроса на подтверждения, однако сама таб- лица сохранится. Пример 25. Удалить все предметы, на которые отводится 85 часов: DELETE FROM Lessons WHERE Kol_chas=68; После выполнения этого оператора таблица Lessons будет иметь вид, представленный на рис. 7.53 Рис. 7. Вид таблицы Lessons после удаления строк При удалении строк с помощью DELETE эти строки сохраня- ются в системных сегментах отката на случай восстановления. Это может потребовать значительного времени. Поэтому лучше исполь- зовать TRUNCATE для удаления всех данных. 8.3. Команда обновления <оператор_изменения> ::= UPDATE имя_таблицы SET имя_столбца= <выраже- ние>[,...n] [WHERE <условие_отбора>] имя_таблицы – это либо имя таблицы базы данных, либо имя обновляемого представления. В предложении SET указываются имена одного и более столбцов, данные в которых необходимо изменить. Выражение представляет собой новое значение соответст- вующего столбца и должно быть совместимо с ним по типу данных. Оператор UPDATE без предложения WHERE следует ис- пользовать с осторожностью, так как в этом случае будут затронуты все строки таблицы. Пример 26. Увеличить стипендию, равную 1200 рублям, на 25 %: UPDATE Students SET Stipend = Stipend*1.25 WHERE Stipend =1200; После выполнения этого оператора таблица Students будет иметь вид, представленный на рис. 8.54 Рис. 8. Вид таблицы Students после выполнения оператора UPDATE Пример 27. Для студента Иванкова С.В. установить стипен- дию в размере 2000 рублей: UPDATE Students SET Stipend=2000 WHERE FIO LIKE ' Иванкова С.В.;' После выполнения этого оператора таблица Students будет иметь вид, представленный на рис. 9. Рис. 9. Результат выполнения запроса Пример 28. Увеличить максимальную стипендию в 2 раза: UPDATE Students SET Stipend = Stipend*2 WHERE Stipend = (SELECT MAX(Stipend) FROM Students); Краткие итоги Существуют следующие операторы модификации данных: INSERT INTO – оператор добавления; применяется для до- бавления записей в таблицу: INSERT INTO <имя_таблицы> [ (имя_столбца [,...n] ) ] VALUES (значение[,...n]); DELETE FROM – оператор удаления; применяется для уда- ления записей из таблицы.55 DELETE FROM <имя_таблицы> [WHERE <усло- вие_отбора>] При удалении строк с помощью DELETE эти строки сохра- няются в системных сегментах отката на случай восстановления. Это может потребовать значительного времени. Поэтому лучше ис- пользовать TRUNCATE для удаления всех данных. UPDATE – оператор обновления, UPDATE имя_таблицы SET имя_столбца= <выраже- ние>[,...n] [WHERE <условие_отбора>]

29|| Создание базы данных В стандарте ANSI нет команды CREATE DATABASE. Но поч- ти все платформы СУБД поддерживают какой-либо вариант этой команды. Процедура создания базы данных обычно закрепляется только за администратором базы данных. Этапы создания БД 1) создание базы данных (файл с расширением *.mdf для ос- новных файлов и файл с расширением *.ndf для вторичных файлов). В файле базы данных записываются сведения об основных объектах (таблицах, индексах, просмотрах и т.д.); 2) создание журнала транзакций, принадлежащего базе дан- ных (файл с расширением *.ldf). Здесь записываются сведения о процессе работы с транзакциями (контроль целостности данных, со- стояния базы данных до и после выполнения транзакций): <определение_базы_данных> ::= CREATE DATABASE имя_базы_данных [ON [PRIMARY] [ <определение_файла> [,...n] ] [,<определение_группы> [,...n] ] ] [ LOG ON {<определение_файла>[,...n] } ] имя_базы_данных – стандартный идентификатор, допусти- мый в SQL. Если имя базы данных содержит пробелы или любые другие недопустимые символы, оно заключается в ограничители (двойные кавычки или квадратные скобки). Имя базы данных долж- но быть уникальным в пределах сервера и не может превышать 128 символов. Если в процессе использования базы данных планируется ее размещение на нескольких дисках, то можно создать так называе- мые вторичные файлы базы данных с расширением *.ndf. В этом случае основная информация о базе данных располагается в пер- вичном (PRIMARY) файле, а при нехватке для него свободного места добавляемая информация будет размещаться во вторичном31 файле. Подход, используемый в SQL-сервере, позволяет распреде- лять содержимое базы данных по нескольким дисковым томам. ON – определяет список файлов на диске для размещения ин- формации, хранящейся в базе данных. PRIMARY – определяет первичный (основной) файл. В базе данных такой файл может быть только один. Если он опущен, то основным является первый файл в списке. Основной файл содержит логическое начало базы данных. При создании базы данных можно определить набор файлов, из которых она будет состоять. Файл определяется с помощью сле- дующей конструкции: <определение_файла>::= ([ NAME=логическое_имя_файла,] FILENAME='физическое_имя_файла' [,SIZE=размер_файла ] [,MAXSIZE={max_размер_файла |UNLIMITED } ] [, FILEGROWTH=величина_прироста ] )[,...n] NAME=логическое_имя_файла – это имя файла, под которым он будет распознаваться при выполнении различных SQL-команд. FILENAME='физическое_имя_файла' – это имя файла, кото- рый будет создан на жестком диске. Это имя останется за файлом на уровне операционной системы. SIZE=размер_файла определяет первоначальный размер фай- ла; минимальный размер параметра – 512 Кб; если он не указан, то по умолчанию принимается 1 Мб. MAXSIZE={max_размер_файла} определяет максимальный размер файла базы данных. При значении параметра UNLIMITED максимальный размер базы данных ограничивается свободным ме- стом на диске. FILEGROWTH=величина_прироста – величина автоматиче- ского прироста размера базы данных. Приращение – это либо абсо- лютная величина в мегабайтах либо процентное соотношение. Если FILEGROWTH не задан, то файл за одно увеличение будет увели- чиваться на 10 % (но не менее, чем на 64 Кб.) Дополнительные файлы могут быть включены в группу:32 <определение_группы>::=FILEGROUP имя_группы_файлов <определение_файла>[,...n] LOG ON {<определение_файла>[,...n] } – здесь описываются файл или файлы, в которых хранится журнал транзакций. Пример 8. Создать базу данных, причем для данных опреде- лить три файла на дисках D, E, F, для журнала транзакций – два файла на дисках H и M: CREATE DATABASE Institute ON PRIMARY (NAME=Archiv1, FILENAME=”d:\user\data\archdat1.mdf”, SIZE=100MB, MAXSIZE=200, FILEGROWTH=20), (NAME=Archiv2, FILENAME=”е:\

29|||Удаление базы данных Удаление базы данных осуществляется командой DROP DATABASE имя_базы_данных [,...n]; Удаляются все содержащиеся в базе данных объекты, а также файлы, в которых она размещается. Для исполнения операции уда- ления базы данных пользователь должен обладать соответствую- щими правами. Пример 11. Удалить базу данных Institutе DROP DATABASE Institute; Краткие итоги Процедура создания базы данных обычно закрепляется только за администратором базы данных. Этапы создания базы данных: 1) создание базы данных; 2) создание журнала транзакций. Создание базы данных выполняется с помощью команды CREATE DATABASE (с параметрами). Краткая форма оператора создания базы данных – CREATE DATABASE имя_базы_данных; В этом случае все значения параметров задаются по умолчанию. Изменение базы данных выполняется с помощью команды ALTER DATABASE имя_базы_данных; Удаление базы данных осуществляется командой DROP DATABASE имя_базы_данных [,...n];

30||Создание таблицы Таблица – основной объект для хранения информации в реля- ционной базе данных. В SQL Server 2005 в одной базе данных может быть до 2 мил- лиардов таблиц. В таблице – 1024 столбца, в 1 строке – 8060 байтов. После создания общей структуры базы данных можно присту- пить к созданию таблиц, которые представляют собой отношения, входящие в состав проекта базы данных. Таблицы базы данных создаются с помощью команды CREATE TABLE. Эта команда создает пустую таблицу, т.е. табли- цу, не имеющую строк. Значения в эту таблицу вводятся с помощью команды INSERT. Команда CREATE TABLE определяет имя таб- лицы и множество поименованных столбцов в указанном порядке. Для каждого столбца должен быть определен тип и размер. Тип данных, для которого обязательно должен быть указан размер, – это CHAR. Реальное количество символов, которое может находиться в поле, изменяется от нуля (если в поле содержится NULL–значение) до заданного в CREATE TABLE максимального значения. Упрощенный синтаксис этой команды (табл. 16): CREATE TABLE <имя таблицы> ( {<имя поля> <тип данных> [(<размер>)] [<ограничения целостности поля>…]} .,.. [, <ограничения целостности таблицы>.,..] ); Описание команды CREATE TABLE Таблица 16 Элемент Описание <имя таблицы> [database.[owner].]table_name <имя столбца > Имя столбца таблицы, обычный идентификатор <тип данных> Тип данных поля <размер> Размер поля в символах (для текста и чисел)36 Базовый синтаксис команды создания таблицы имеет следую- щий вид: <определение_таблицы> ::= CREATE TABLE имя_таблицы ( { имя_столбца тип_данных [ NOT NULL ] [ [PRIMARY KEY | UNIQUE] [DEFAULT <значение>] [IDENTITY [(стартовое_значение, инкремент)]] [FOREIGN KEY REFERENCES имя_род_таблицы [ (имя_столбца_род_таблицы ) ] [ CHECK (<условие_выбора> ) ] [,...n] [ON UPDATE {CASCADE | NO ACTION } ] [ON DELETE {CASCADE | NO ACTION } ] } ); [IDENTITY [(стартовое_значение, инкремент)] – для ко- лонки с таким свойством сервером автоматически генерируется возрастающая последовательность. Отсчет начинается со стартово- го значения, которое увеличивается на величину инкремента. Если какой-либо параметр опущен, то по умолчанию принимается еди- ница. Сервер не гарантирует непрерывность значений – в реальных данных в таблице могут появляться разрывы. Далее в данном пособии будет рассматриваться база данных Institute, имеющая структуру, приведенную на рис. 5. База данных состоит из 6 таблиц: Таблица Teachers содержит сведения о преподавателях; Таблица Lessons содержит сведения о предметах; Таблица Groups содержит сведения об учебных группах; Таблица Students содержит сведения о преподавателях; Таблица Kafedra содержит сведения о кафедрах; Таблица Progress содержит сведения об успеваемости студентов.37 Рис. 5. Структура базы данных Institute Перед созданием таблиц нужно указать базу данных, в кото- рой будут создаваться требуемые таблицы, с помощью команды USE имя_базы_данных; В нашем случае это будет команда USE Institute;

30||Изменение таблицы Структура существующей таблицы может быть модифициро- вана с помощью команды ALTER TABLE, упрощенный синтаксис которой представлен ниже: ALTER TABLE имя_таблицы { [ALTER COLUMN имя_столбца {новый_тип_данных [ NULL | NOT NULL ]}] | ADD { [имя_столбца тип_данных] | имя_столбца AS вы- ражение } [,...n] | DROP {COLUMN имя_столбца}[,...n] }; Команда позволяет добавлять и удалять столбцы, изменять их определения. Одно из основных правил при добавлении столбцов в сущест- вующую таблицу гласит: если в таблице уже содержатся данные, добавляемый столбец не может быть определен с атрибутом NOT NULL. Этот атрибут означает, что для каждой строки данных соот- ветствующий столбец должен содержать некоторое значение, по- этому добавление столбца с атрибутом NOT NULL приводит к по- явлению противоречия – уже существующие строки данных табли- цы не будут иметь в новом столбце ненулевых значений. Способ добавления обязательных полей в существующую таблицу: добавить в таблицу новый столбец, определив его с атрибу- том NULL (т.е. столбец не обязан содержать каких-либо значений); ввести в новый столбец какие-либо значения для каждой строки данных таблицы; убедившись, что новый столбец содержит ненулевые значе- ния для каждой строки данных, изменить структуру таблицы, заме- нив атрибут этого столбца на NOT NULL. Правила изменения определений столбцов: размер столбца может быть увеличен до максимального значения, допускаемого соответствующим типом данных;47 размер столбца может быть уменьшен только в том случае, если содержащееся в нем наибольшее значение не будет превосхо- дить его нового размера; количество разрядов числового типа данных всегда может быть увеличено; количество разрядов числового типа данных может быть уменьшено только в том случае, если количество разрядов наи- большего значения в соответствующем столбце не будет превосхо- дить нового числа разрядов, определенного для этого столбца; количество десятичных знаков числового типа данных мо- жет быть уменьшено или увеличено; ип данных столбца, как правило, может быть изменен.т Пример 19. Добавить в таблицу Students поле Stipendiya: ALTER TABLE Students ADD Stipendiya INT; Некоторые реализации фактически могут ограничить разра- ботчика в использовании некоторых опций команды ALTER TABLE. Например, может оказаться недопустимым удаление столбцов из существующей таблицы. Чтобы добиться этого, сначала потребуется удалить саму таблицу и только потом заново ее по- строить с нужными столбцами. Причем уже внесенные в таблицу данные будут потеряны. Возможны трудности, связанные с удалением из таблицы столбца, который зависит от некоторого столбца другой таблицы. В таком случае сначала придется удалить ограничение столбца, а затем сам столбец. Пример 20. Удаление ограничения внешнего ключа: ALTER TABLE Students DROP CONSTRAINT to_group ALTER TABLE Students DROP COLUMN ID_Group; 7.4. Удаление таблицы С течением времени структура базы данных меняется: созда- ются новые таблицы, а прежние становятся ненужными и удаляют- ся из базы данных с помощью команды DROP TABLE имя_таблицы [RESTRICT | CASCADE; Пример 21. Удалить таблицу Студенты: DROP TABLE Students;48 Команда DROP TABLE дополнительно позволяет указывать, следует ли операцию удаления выполнять каскадно. Если указано ключевое слово RESTRICT, то при наличии в базе данных хотя бы одного объекта, существование которого зави- сит от удаляемой таблицы, выполнение команды DROP TABLE бу- дет отменено. Если указано ключевое слово CASCADE, автоматически уда- ляются и все прочие объекты базы данных, чье существование зави- сит от удаляемой таблицы, а также другие объекты, зависящие от удаляемых объектов. Общий эффект от выполнения команды DROP TABLE с ключевым словом CASCADE может оказаться весьма

31|||Общие ограничения целостности <ограничения целостности таблицы> – то же, что и для поля. Общие ограничения целостности указываются через запятую после последнего поля.46 7.3. Изменение таблицы Структура существующей таблицы может быть модифициро- вана с помощью команды ALTER TABLE, упрощенный синтаксис которой представлен ниже: ALTER TABLE имя_таблицы { [ALTER COLUMN имя_столбца {новый_тип_данных [ NULL | NOT NULL ]}] | ADD { [имя_столбца тип_данных] | имя_столбца AS вы- ражение } [,...n] | DROP {COLUMN имя_столбца}[,...n] }; Команда позволяет добавлять и удалять столбцы, изменять их определения. Одно из основных правил при добавлении столбцов в сущест- вующую таблицу гласит: если в таблице уже содержатся данные, добавляемый столбец не может быть определен с атрибутом NOT NULL. Этот атрибут означает, что для каждой строки данных соот- ветствующий столбец должен содержать некоторое значение, по- этому добавление столбца с атрибутом NOT NULL приводит к по- явлению противоречия – уже существующие строки данных табли- цы не будут иметь в новом столбце ненулевых значений. Способ добавления обязательных полей в существующую таблицу: добавить в таблицу новый столбец, определив его с атрибу- том NULL (т.е. столбец не обязан содержать каких-либо значений); ввести в новый столбец какие-либо значения для каждой строки данных таблицы; убедившись, что новый столбец содержит ненулевые значе- ния для каждой строки данных, изменить структуру таблицы, заме- нив атрибут этого столбца на NOT NULL. Правила изменения определений столбцов: размер столбца может быть увеличен до максимального значения, допускаемого соответствующим типом данных;47 размер столбца может быть уменьшен только в том случае, если содержащееся в нем наибольшее значение не будет превосхо- дить его нового размера; количество разрядов числового типа данных всегда может быть увеличено; количество разрядов числового типа данных может быть уменьшено только в том случае, если количество разрядов наи- большего значения в соответствующем столбце не будет превосхо- дить нового числа разрядов, определенного для этого столбца; количество десятичных знаков числового типа данных мо- жет быть уменьшено или увеличено; ип данных столбца, как правило, может быть изменен.т Пример 19. Добавить в таблицу Students поле Stipendiya: ALTER TABLE Students ADD Stipendiya INT; Некоторые реализации фактически могут ограничить разра- ботчика в использовании некоторых опций команды ALTER TABLE. Например, может оказаться недопустимым удаление столбцов из существующей таблицы. Чтобы добиться этого, сначала потребуется удалить саму таблицу и только потом заново ее по- строить с нужными столбцами. Причем уже внесенные в таблицу данные будут потеряны. Возможны трудности, связанные с удалением из таблицы столбца, который зависит от некоторого столбца другой таблицы. В таком случае сначала придется удалить ограничение столбца, а затем сам столбец. Пример 20. Удаление ограничения внешнего ключа: ALTER TABLE Students DROP CONSTRAINT to_group ALTER TABLE Students DROP COLUMN ID_Group;

№48 Хранимые процедуры

Хранимая процедура (Stored procedure) – программа, которая

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

действия на основе информации, задаваемой пользователем. По-

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

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

вие, чем при выполнении тех же операций средствами клиента базы

данных.

Хранимая процедура объединяет запросы и процедурную ло-

гику (операторы присваивания, логического ветвления и т.п.) и хра-

нится в базе данных.

Одна процедура может быть использована в любом количестве

клиентских приложений, что позволяет существенно сэкономить

трудозатраты на создание прикладного программного обеспечения

и эффективно применять стратегию повторного использования ко-

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

мирования, хранимые процедуры могут иметь входные и выходные

параметры или не иметь их.

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

вместо отдельных команд Transact SQL:



необходимые команды уже содержатся в базе данных;



все они прошли этап синтаксического анализа и находятся в

исполняемом формате;



хранимые процедуры поддерживают модульное программи-

рование, так как позволяют разбивать большие задачи на самостоя-

тельные, более мелкие и удобные в управлении части;



хранимые процедуры могут вызывать другие хранимые про-

цедуры и функции;



хранимые процедуры могут быть вызваны из прикладных

программ других типов;



как правило, хранимые процедуры выполняются быстрее,

чем последовательность отдельных команд;



хранимые процедуры проще использовать: они могут состо-

ять из десятков и сотен команд, но для их запуска достаточно ука-

зать всего лишь имя нужной хранимой процедуры. Это позволяет

уменьшить размер запроса, посылаемого от клиента на сервер, а

значит, и нагрузку на сеть.

Хранимые процедуры вызываются клиентской программой,

другой хранимой процедурой или триггером. Разработчик может

управлять правами доступа к хранимой процедуре, разрешая или

запрещая ее выполнение. Изменять код хранимой процедуры раз-

решается только ее владельцу или члену фиксированной роли базы

данных. При необходимости можно передать права владения ею от

одного пользователя к другому.

№39 Транзакция представляет собой группу команд, которые обрабатываются как единое

целое. Транзакция завершается либо фиксированием, т. е. выполняются все входящие в

нее команды, и БД переводится в новое устойчивое состояние, либо откатом, т. е. в

случае какого-либо сбоя для незавершенной транзакции отменяются все уже

выполненные действия и БД приводится в состояние, предшествовавшее началу

транзакции.Транзакция должна обладать следующими свойствами:

1. Атомарность. Все изменения данных, выполняемые в транзакции,

рассматриваются как единый минимальный блок.

2. Согласованность. После успешного завершения транзакции данные должны

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

выполнения транзакции целостность может временно нарушаться.

3. Изолированность. Операции изменения данных, производимые одной транзакцией,

не должны зависеть от изменений, вносимых другой транзакцией. Просмотр

данных в промежуточном состоянии не разрешен. Если транзакция выбирает

строки по определенному логическому условию, то другая транзакция не должна

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

4. Устойчивость или долговечность. После фиксирования транзакции система не

может быть возвращена в состояние, в котором была до начала транзакции.

Способы определения транзакций

Автоматическое определение транзакции. В этом режиме транзакций считается каждая

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

устанавливается по умолчанию. Явноеопределение транзакции. В этом режиме начало и конец транзакции явно

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

• BEGINTRANSACTION обозначает начало транзакции. В журнале транзакций

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

• SAVETRANSACTION предназначена для создания точки сохранения, которая

фиксирует внутри транзакции промежуточное состояние данных, к которому

можно вернуться. Точек сохранения может быть несколько.

• ROLLBACK выполняет откат транзакции. Система контролирует целостность

данных при выполнении транзакции, и автоматически выполняет откат при

нарушении целостности. Однако иногда пользователю необходимо отменить

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

• COMMIT выполняет фиксирование транзакции. Если все команды выполнены

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

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

изменения.

Неявное определение транзакции. В этом режиме начало транзакции явно не указывается,

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

COMMIT и ROLLBACK.В итоге получается цепочка команд, составляющих транзакцию.

Новая транзакция начинается с команды, следующей за COMMIT или ROLLBACK. При

работе в таком режиме пользователю надо следить за тем, чтобы не создавать слишком

длинные цепочки.

Вложенные транзакции – это транзакции, которые определены внутри другой транзакции.

Они могут создаваться только в режиме явного определения. При удачном завершении

транзакции нижнего уровня выполняется следующая команда текущей транзакции. При

откате транзакции нижнего уровня выполняется откат всей текущей транзакции.

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

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

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

программа координатор распределенных транзакций(MSDTC).

Наиболее сложный этап работы распределенных транзакций связан с их завершением.

Менеджер транзакций должен гарантировать, что все локальные транзакции закончатся

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

не зафиксируется, и менеджеру транзакций придется отменять остальные, но это будет

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

распределенную транзакцию, MSDTC должен гарантировать, что все локальные

транзакции будут завершены или откачены даже в случае выхода из строя одного из

серверов или повреждения сети. В SQLServer 2000 используется двухфазный протокол

завершения транзакции (2РС). Он состоит из фазы подготовки и фазы завершения.

Фаза подготовки. На этом этапе координатор распределенных транзакций посылает

требование локальным менеджерам транзакций завершить локальные транзакции. По

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

менеджеры уведомляют координатор об успешном или неудачном завершении

транзакции. Сам факт выполнения транзакции не фиксируется.

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

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

зафиксировать транзакцию, MSDTC посылает всем менеджерам требования об откате

транзакций и сообщает пользователю об ошибке выполнения распределенной транзакции.

№40 Журнал транзакций

Журналом транзакций называется системная структура, которая служит для сохранения

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

помечается номером транзакции, к которой относится, и значениями атрибутов, которые

она меняет. Для каждой транзакции фиксируется команда начала и завершения.

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

• Если транзакция содержит команду начала, но не содержит команду фиксации с

подтверждением ее выполнения, то выполняется последовательность действий как

при откате.

• Если сбой произошел после выполнения последней команды изменения БД, но до

выполнения команды фиксации, то команда фиксации выполняется, а БД

сохраняется в измененном состоянии.

№33 Создание, перестройка, переименование, удаление индексов

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

может быть создано несколько индексов. Однако большое число индексов снижает

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

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

Индексы представляют собой набор ссылок на места физического размещения строк в БД,

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

нескольким столбцам, они называются индексированными. Индекс может быть

уникальным или неуникальным. Обычно индекс хранится отдельно от самих данных.

Каждый его элемент хранит значение, соответствующее значению индексированного

столбца конкретной строки, а также ссылку на исходную строку.

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

Имя индекса должно быть уникальным в пределах БД.

Все СУБД имеют средства работы с индексами в интерактивном и программном режиме.

Эти средства зависят от СУБД, но существуют операторы создания, перестройки,

переименования, удаления индексов, определенные в стандарте SQL. Уникальные

индексы для столбцов с ограничением UNIQUE или PRIMARYKEY, создаются

автоматически при определении этого ограничения.

№34В SQLServer 2000 реализовано несколько типов индексов:

• кластерный – физическое расположение данных перестраивается в соответствии со

структурой индекса, поэтому в таблице может быть определен только один

кластерный индекс; такие индексы увеличивают скорость поиска данных по

сравнению с некластерными;

• некластерный– наиболее типичный индекс, не перестраивает физическую

структуру таблицы, а лишь организует ссылки на соответствующие строки; в

таблице можно определить несколько некластерных индексов;

• уникальный гарантирует уникальность значений в индексируемом столбце; такой

индекс является своеобразной надстройкой и может быть реализован как для

кластерного, так и для некластерного индекса.

Команда создания индекса имеет синтаксис:

CREATE [UNIQUE][CLUSTERED|NONCLUSTERED]

INDEX<имяиндекса>ON<имятаблицы> (<имястолбца>[ASC|DESC][,…]); ASC – индекс упорядочен по возрастанию (по умолчанию), DESC – индекс упорядочен

по возрастанию.

Команда удаления индекса имеет синтаксис:

DROP<имя индекса>;

Команды перестроения и переименования индексов зависят от конкретной СУБД.

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

таблице были изменены, а индекс не перестроен.

№35 Представление – это виртуальная таблица, которая является результатом запроса,

заданного при описании представления. Запрос выполняется каждый раз при обращении к

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

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

вводит данные;

• ограничивать число столбцов, из которых пользователь выбирает или в которые

вводит данные;

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

базовой таблицы;

• выводить столбцы из нескольких базовых таблиц.

Представлению присваивается имя, уникальное в пределах БД.

Горизонтальное представление содержит все столбцы и все строки или часть строк

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

все строки и часть столбцов таблицы. Смешанное представление содержит часть столбцов

и часть строк таблицы, которые отвечают некоторому условию. Агрегированное

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

непрерывной обработки и интерпретации извлекаемой информации.

Представление, создаваемое на основе нескольких таблиц, имеют следующие

преимущества:

• представление можно использовать при формировании сложных отчетов как

промежуточный макет;

• предварительно объединенные поисковые и базовые таблицы обеспечивают

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

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

объединяющих процедур;

• предварительно объединенные проверенные представления уменьшают

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

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

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

отобрать только те данные, которые им необходимы, и скрыть остальную информацию.

Представления можно разделить на две категории: «только для чтения» и обновляемые,

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

определенные ограничения:

• представление должно строиться на основе только одной таблицы;

• представление должно содержать первичный ключ базовой таблицы;

• изменять данные можно только в тех столбцах, которые непосредственно выбраны

из таблицы;

• если через представление осуществляется вставка строк, то оно должно содержать

все обязательные столбцы базовой таблицы;

• представление не должно содержать полей с агрегирующими функциями;

• представление не должно содержать параметры DISTINCT, GROUPBY или

HAVING;

• представление не должно содержать подзапросов;

• представление не должно использовать в качестве полей вывода константы или

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

правами доступа к таблицам. Для представлений могут выдаваться следующие права

доступа:

• SELECT - разрешается выборка данных из всего представления или из

определенных столбцов;

• UPDATE – разрешает выполнять изменение данных через представление, но не

позволяет просматривать данные;

• INSERT – разрешается добавлять в представление новые строки, но запрещается

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

пользователем;

• DELETE – разрешается удаление строк представления.

Как и прочие объекты БД, представления могут создаваться в интерактивном или в

программном режиме. Для создания представления применяется команда:

CREATEVIEW<имя представления>

[(<псевдонимы столбцов>)]

[WITH<параметры представления>]

ASSELECT<текст запроса> [WITHCHECKOPTION]

Опция WITHCHECKOPTIONгарантирует, что для строк представления не будет

разрешено выполнение изменений, которые приведут к исчезновению строк из

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

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

исчезнет из представления. Указанная опция запрещает подобные изменения.

№46 Хранимые процедуры

Хранимая процедура – это именованный набор команд, хранящийся на сервере и

представляющий собой самостоятельный объект БД. Хранимые процедуры позволяют

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

выполняет типичные действия.

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

использования оптимизированного и откомпилированного кода. Поскольку для запуска

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

запроса, посылаемого на сервер, что снижает загрузку сети. Использование хранимых

процедур позволяет реализовать модульное проектирование.

№47В SQLServer поддерживается несколько типов хранимых процедур:

• Системные процедуры поставляются в составе SQLServer (они выполняют

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

Системные хранимые процедуры имеют префикс sp_ и размещаются в системной

БД master. Такие процедуры доступны в контексте любой БД.

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

БД. Непосредственное обращение к таким процедурам возможно только в

контексте этой БД .

• Временные процедуры существуют определенное время, после чего автоматически

уничтожаются сервером.

o Локальные временные процедуры могут быть вызваны только из того

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

пользователя, перезапуске или остановке сервера.

o Глобальные временные процедуры могут быть вызваны из любого

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

должно начинаться с ##. Такие процедуры автоматически удаляются при

перезапуске или остановке сервера, а также при закрытии соединения, в

контексте которого они были созданы.

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

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

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

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

№45 Открытие курсора подразумевает наполнение его конкретными данными и

осуществляется командой:

OPEN [GLOBAL] <имя курсора>

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

одинаковыми именами.

Считывать можно только одну строку курсора. При открытии курсора указатель

помещается в позицию, предшествующую первой строке. При чтении очередной строки

указатель перемещается на эту строку. Команда чтения имеет вид:

FETCH

[[NEXT | PRIOR | FIRST | LAST

| ABSOLUTE {<константа> |<переменная> }

| {< константа > |< переменная > }

]

FROM

]

[ GLOBAL] <имя курсора>

[< переменная> [,…]] Назначение параметров:

• NEXT – переход к следующей строке;

• PRIOR – переход к предыдущей строке;

• FIRST – переход к первой строке;

• LAST – переход к последней строке;

• ABSOLUTE – переход к строке с указанным номером;

• RELATIVE – переход к строке, находящейся через заданное число строк;

• INTO – задает список переменных, в которых будут сохраняться отобранные

значения.

Изменение и удаление данных выполняется с помощью команд INSERT и DELETE

соответственно.

Закрытие курсора освобождает выделенные для него ресурсы, снимаются все блокировки.

Закрытый курсор можно снова открыть. Курсор закрывается командой:

CLOSE [GLOBAL] <имякурсора>

Освобождение курсора приводит к его удалению как объекта БД. После этого курсор

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

Курсор освобождается командой:

DEALLOCATE[GLOBAL] <имя курсора>

RELATIVE

№43 типы Курсоры TransactSQL

Статические курсоры (курсоры моментального снимка) предоставляют в распоряжение

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

сервером и сохранения результирующего набора в системной БД tempdb. Изменение

исходных данных на курсоре никак не отражается. Статический курсор всегда

открывается в режиме «только чтение».

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

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

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

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

исходные данные.

Последовательные курсоры не разрешают выполнять выборку данных в обратном

направлении. Такой курсор не хранит весь набор строк. При считывании очередной

строки курсора сервер обращается к исходным данным с помощью запроса SELECT и

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

самое последнее состояние данных.

Ключевые курсоры строятся на основе использования уникальных идентификаторов

(ключей). В качестве ключа выбирается набор столбцов, уникально идентифицирующих

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

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

ключевой курсор представляет собой набор ссылок на исходные строки, сохраненных в

определенной последовательности. Результирующий набор сохраняется в системной БД

tempdb. Сервер блокирует строки исходных таблиц только на время составления таблицы

ключей. После открытия курсора набор ключей не обновляется; если в каких-то строках

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

помечаются как поврежденные. Строки, добавленные в исходные таблицы после открытия

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

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

данных в исходных таблицах.

№41 Команды языка управления транзакциями – TCL (Тгаnsасtiоn Соntrol Language).

 

Эти команды позволяют определить исход транзакции.

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

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

COMMIT – заканчивает («подтверждает») текущую транзакцию и делает постоянными (сохраняет в базе данных) изменения, осуществленные этой транзакцией. Также стирает точки сохранения этой транзакции и освобождает ее блокировки. Можно также использовать эту команду для того, чтобы вручную подтвердить сомнительную распределенную транзакцию.

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

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

В системах с развитыми средствами ограничения и контроля целостности каждая транзакция начинается при целостном состоянии базы данных и должна оставить это состояние целостными после своего завершения. Несоблюдение этого условия приводит к тому, что вместо фиксации результатов транзакции происходит ее откат (т.е. вместо оператора COMMIT выполняется оператор ROLLBACK), и база данных остается в таком состоянии, в котором находилась к моменту начала транзакции, т.е. в целостном состоянии.

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

№42 Последовательности Последовательность — это специальная инструкция, доступная в некоторых реляционных СУБД включая Oracle. Она представляет собой счетчик, используемый для создания уникальных числовых идентификаторов. Текущее значение счетчика можно извлечь с помощью инструкции SELECT. Это происходит в атомарном режиме, что гарантирует уникальность каждого номера, тем самым гарантируется, что никакие два потока не получат два одинаковых идентификатора. В MySQL уникальные идентификаторы строк реализуются лучше всего с помощью первичных ключей-счетчиков. Имитация последовательностей может потребоваться при переносе приложений в MySQL. Работа с последовательностями ведется посредством функции LAST_IN-SERT_ID(). Будучи вызванной без аргументов, она возвращает последнее значение счетчика, установленное путем автоматического увеличения или же самой функцией. Если же вызвать функцию с аргументом, она вернет значение аргумента. Но не стоит пытаться извлечь значение счетчика напрямую из таблицы: другой поток мог изменить это значение.

№36 ТриггерSQLServer 2000 – это специальный тип хранимых процедур, запускаемых

сервером автоматически при выполнении тех или иных действий с данными таблицы.

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

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

успешного завершения разрешается выполнение изменений. Все производимые триггером

модификации данных рассматриваются как одна транзакция, и при обнаружении ошибки

выполняется ее откат.

Триггеры используются во многих ситуациях:

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

выполняемых изменений. Триггеры собирают информацию о производимых

изменениях и сохраняют ее в системных таблицах поддержки репликации.

• Триггеры позволяют создать сложное значение по умолчанию, вычисляя его с

помощью других столбцов и функций TransactSQL.

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

которой стандартными средствами SQLServer невозможно.

• Триггеры используются для выполнения каскадных изменений в нескольких

связанных таблицах.

Помимо этого, пользователь может применять триггеры, исходя из своих требований к

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

злоупотреблять применением триггеров, в том числе и потому, что они удерживают

блокировку до завершения триггера, запрещая обращение к ресурсу других

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

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

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

прибегать к триггерам.

В SQL Server существуеттритипатриггеров: INSERT TRIGGER, DELETE TRIGGER,

UPDATE TRIGGER. Для одной таблицы можно определить несколько однотипных

триггеров.

Триггеры можно классифицировать по типу поведения. В SQLServer есть два параметра,

определяющих поведение триггера: AFTER и INSTEADOF.

При указании параметра AFTER триггер выполняется после успешного выполнения

команды, изменяющей данные в таблице. Если по каким-либо причинам команда не

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

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

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

каждой операции. Для представлений триггеры AFTER определить нельзя. При указании параметра INSTEADOF триггер вызывается вместо команд, назначенных

для запуска триггера. Триггеры INSTEADOF можно определять для таблиц и

представлений, но можно определить только один такой триггер для каждой операции.

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

возникновения «мертвой» блокировки. Поэтому следует снимать блокировки с ресурсов в

теле триггера сразу же после того, как они становятся ненужными. Команда

CREATETRIGGER должна быть первой в пакете и может применяться только к одной

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

к другим БД.