Microsoft SQL Server 2000 – это система управления реляционными базами данных (RDBMS) в топологии клиент/сервер, разработанная для обработки высокопроизводительных сетевых транзакций (OLTP), хранения данных и использования в приложениях электронной коммерции. Курс "Программирование в Microsoft SQL Server 2000 Шаг за шагом" поможет вам быстро освоить принципы создания и манипулирования базами данных с использованием интерактивных средств, входящих в состав SQL Server 2000 версий Personal, Standard и Enterprise.
Внимание! Этот курс предназначен для использования с SQL Server 2000, однако сама система не поставляется вместе с курсом. Прежде, чем приступить к выполнению уроков, вы должны приобрести и установить SQL Server 2000.
Этот курс предназначен как для читателей, вообще не знакомых с реляционными базами данных, так и для тех, которые имеют опыт работы с другими системами управления базами данных, такими как Microsoft Access, и хотели бы перейти к SQL Server 2000. Воспользуйтесь приведенной ниже таблицей, чтобы выбрать для себя план занятий.
С чего начать изучение
Чтобы установить учебные файлы, вы должны обладать достаточными привилегиями безопасности для SQL Server, который вы используете. Если вы не имеете имени пользователя или пароля для SQL Server, либо столкнулись с какими-либо проблемами в процессе установки, обратитесь к вашему системному администратор
Знакомство с Enterprise Manager
Microsoft Windows включает в себя утилиту Microsoft Management Console (MMC), которая предоставляет стандартную оболочку для управления приложениями сервера. Набор инструментов MMC, используемых для управления определенным сервером, носит название "консоль" (console). Чаще всего консоли серверов представляют собой присоединяемые модули (snap-in), и SQL Server Enterprise Manager, который вы используете для администрирования SQL Server 2000, не является исключением. За дополнительной информацией по MMC вы можете обратиться к справочной системе Windows Help, для этого нажмите кнопку Start (Пуск), выберите Help (Справка) и затем во вкладке Search (Поиск) наберите "Microsoft Management Console".
Создание резервных копий и восстановление базы данных
Вне зависимости от того, насколько надежна основная технология, следует иметь в виду возможности выхода из строя аппаратной части компьютера, сбоев и отказов в программном обеспечении, а также ошибочные действия пользователя. Лучшим способом защиты будет регулярное копирование ваших данных и сохранение их в каком-нибудь безопасном месте. Этот процесс называется "созданием резервной копии". Если что-нибудь случится (а что-нибудь обязательно случится), вы можете использовать резервную копию для восстановления состояния базы данных, которое она имела до возникновения проблемы.
Режимы безопасности
Когда экземпляр SQL Server получает запрос на соединение, он проверяет идентификатор login ID. Login ID представляет собой идентификатор учетной записи, управляющий доступом к SQL Server 2000. SQL Server проверяет предоставленный идентификатор login ID на корректность, а затем определяет, имеет ли данный login ID достаточно привилегий для выполнения запрашиваемой операции. Этот процесс называется аутентификацией.
Создание баз данных
По умолчанию SQL Server подразумевает одинаковое местонахождение для файла базы данных и журнала транзакций, в рабочих системах вам следует всегда хранить журнал транзакций в другом месте, желательно, на другой машине. Это позволит восстановить базу данных в случае отказа аппаратных средств, в результате которого чтение диска станет невозможным.
Создание таблиц
Таблицы представляют собой основные единицы хранения данных в реляционной базе данных. Общепринято, что каждый тип сущности, например, определенная разновидность ароматического масла в базе данных нашего примера, представляется таблицей, в то время как каждый экземпляр сущности, например, Clary Sage или German Chamomile, представляется строкой в таблице.
Понятие об индексах
В реляционной базе данных индекс представляет собой особый объект, который дает возможность базе данных быстро осуществлять доступ к строкам в таблице на основе значений из одного или более столбцов. Здесь имеется аналогия с предметным указателем книги, который обеспечивает быстрый поиск интересующего вас содержимого по определенным ключевым словам.
Понятие об отношениях
Большинство баз данных предназначены для моделирования некой части реального мира, которую называют пространством состояний (problem space). На логическом уровне объекты в пространстве состояний являются сущностями (entities) и связаны между собой через отношения (relationships). На физическом уровне Microsoft SQL Server представляет сущности в виде таблиц, а отношения – в виде ограничений внешнего ключа, которые определяют внешние ключи.
Понятие о проверочных ограничениях
Одним из наиболее важных аспектов в разработке базы данных является обеспечение целостности данных (data integrity). Правила целостности данных гарантируют, что данные, содержащиеся в базе данных, являются если не корректными, то по меньшей мере правдоподобными. Имеется несколько уровней целостности данных. В уроке 7 мы рассмотрели целостность отношений, которая обеспечивает, создание и корректное обслуживание взаимосвязи между таблицами.
Понятие об умолчаниях
Функция умолчаний аналогична свойству умолчания, которое вы задаете при создании столбца в конструкторе таблиц Table Designer, – т.е., они представляют собой значения, которые автоматически присваиваются Microsoft SQL Server, если пользователь не задал значение при создании строки. Однако умолчания являются объектами на уровне базы данных, которые могут быть применены к нескольким столбцам.
Понятие о диаграммах баз данных
Диаграммы баз данных обеспечивают визуальное представление структуры и отношений таблиц в базе данных (схему базы данных). Включение их в состав базы данных является удобным способом документирования схемы, поскольку диаграммы автоматически отражают любые внесенные вами изменения.
Использование конструктора запросов Query Designer
Несмотря на то, что данные, хранящиеся в базе данных Microsoft SQL Server, редко используются в своем полном, необработанном виде, без применения той или иной сортировки или фильтрации, иногда бывает полезно быстро проверить содержимое одной таблицы. Конструктор запросов Query Designer в Enterprise Manager облегчает просмотр и редактирование строк, а также добавление новых
Использование оператора SELECT
Обязательными являются только первая и вторая фразы (разделы) оператора SELECT. Первая фраза, SELECT список_столбцов, задает столбцы, которые будут возвращены запросом. Список столбцов может содержать реальные столбцы из таблицы и представления (виды), на которых основывается запрос, либо содержать вычисляемые столбцы, получаемые из оригинальных столбцов. Вторая фраза, FROM список_источников, задает таблицы и представления, на которых основывается запрос.
Фраза ORDER BY
Фраза ORDER BY является необязательным компонентом оператора SELECT. Она позволяет вам задавать порядок, в котором строки будут возвращены. Можно задать несколько столбцов, а строки возвращать в порядке возрастания или убывания.
Использование фразы FROM
Условие связывания представляет собой выражение, аналогичное условию отбора, используемого в фразе WHERE. Оно задает, как будут соответствовать строки в двух таблицах. Большинство операций связывания выполняются на основе выражений эквивалентности, таких как Column A = Column B. Однако SQL Server поддерживает любые логические операторы, а условие связывания может быть сколь угодно сложным, состоящим из нескольких выражений, соединенных с помощью логических операторов AND или OR. таким же образом, как во фразе WHERE с несколькими условиями отбора.
Оператор SELECT DISTINCT
Хотя одной из целей применения реляционной модели базы данных является устранение повторяющихся данных, большинство баз данных неизбежно будут содержать одинаковые значения в нескольких строках. Например, таблица, содержащая информацию об адресах клиентов, будет, вероятно, включать одни и те же значения страны и штата для многих строк. Это не создает повторы строк и вполне допустимо, поскольку каждое значение штата является атрибутом отдельного клиента. Аналогично, таблица на стороне многих в отношении один-ко-многим может иметь любое заданное значение внешнего ключа, повторяющееся многократно. Это не только не является неправильным, но и необходимо для реляционной целостности базы данных.
Понятие о представлениях
Хотя конструктор запросов Query Designer облегчает обращение к таблицам в вашей базе данных, постоянная корректировка или создание оператора запроса каждый раз заново может оказаться слишком утомительным и трудоемким. SQL Server предоставляет средства для хранения оператора SELECT в виде представления. В большинстве случаев данные, отображаемые представлением, не являются данными, хранящимися в базе данных, – в представлении хранится только оператор SELECT.
Понятие об операторе INSERT
Список столбцов в операторе INSERT не является обязательным. Если он не указан, оператор INSERT должен включать значения для всех столбцов в таблице или представлении, а порядок их должен соответствовать порядку столбцов в таблице или представлении. Вы можете использовать ключевое слово DEFAULT, чтобы задать для строки значения, установленные по умолчанию.
Понятие об операторе UPDATE
За ключевым словом SET следует перечень подлежащих обновлению столбцов, отделяемых запятыми, а также их новые значения. Форма записи при этом следующая: имя_столбца = новое_значение. Новое значение может быть константой или выражением, которое также может ссылаться на сам столбец. Например, выражение SalesPrice = SalesPrice * .90 будет уменьшать значения в столбце SalesPrice на 10 процентов.
Понятие об операторе DELETE
Список столбцов в операторе DELETE не указывается, поскольку при удалении строки удаляются также все столбцы. Необязательная фраза WHERE дает возможность указывать, какие строки следует удалить. Если фраза WHERE опущена, удаляются все строки в указанной таблице или представлении.
Мастера служб преобразования данных
Службы преобразования данных Data Transformation Services (DTS) –мощный набор графических инструментальных средств и программных объектов, который дает возможность импортировать и экспортировать данные, преобразовывать структуры данных и объединять данные из различных источников для их анализа и составления отчетов.
Понятие о Query Analyzer
Query Analyzer предоставляет мощные средства для написания и отладки сложных наборов операторов Transact-SQL различных типов. (Мы рассмотрим один из таких типов – SQL-сценарии). Query Analyzer также предоставляет средства для анализа производительности запросов посредством выполнения планов или с помощью мастера настройки индексов Index Tuning Wizard, который мы рассмотрим в уроке 23.
Понятие о DDL
Язык SQL имеет две составляющие: язык обращения с данными Data Manipulation Language (DML) и язык определения данных Data Definition Language (DDL). DML состоит из операторов, используемых для создания и получения данных. DDL состоит из операторов, используемых для создания объектов в базе данных и для установки свойств и значений атрибутов самой базы данных.
Использование Query Analyzer для оптимизации производительности
В добавлении к панели редактирования Editor Pane, окно Query (Запрос) анализатора запросов SQL Server Query Analyzer предоставляет три дополнительных панели для анализа производительности отдельных запросов. Панель Execution Plan Pane содержит графическое представление задач, которые SQL Server будет обрабатывать для выполнения запроса. Панель Trace Pane показывает детальную информацию о выполнении запроса на стороне сервера, включая время и число операций чтения и записи. Панель Client Statistics Pane отображает информацию о выполнении запроса на стороне клиента, включая количество обращений и ответов от сервера и пропускную способность сети.
Команды Transact-SQL
То, что мы называем командой, в документации SQL Server Books Online обозначается как "зарезервированные ключевые слова" (reserved keywords). Этот термин не очень удачен, поскольку нет большого различия между "зарезервированные ключевые слова" и любым другим зарезервированным словом. По этой причине мы будем использовать термин команда (command), который означает определенный набор зарезервированных ключевых слов, которые представляют действия, выполняемые SQL Server.
Временные таблицы
Временные таблицы походят на обычные таблицы, но существуют только тогда, когда их используют. Они автоматически удаляются Microsoft SQL Server после того, как все пользователи закончили работу с ними.
Условное выполнение
Первая группа операторов управления ходом выполнения, которую мы рассмотрим, определяет порядок выполнения операторов на основе значения булева выражения. (Напомним, что булевы выражения работают с логическими значениями типа "истина" (TRUE) или "ложь" (FALSE)).
Понятие о курсорах
Microsoft SQL Server реально поддерживает два различных типа курсоров: курсоры Transact-SQL и курсоры API (курсоры программного интерфейса приложений). Курсоры API создаются внутри приложения, использующего объекты Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC или DB-Library. Каждое из этих API поддерживает несколько отличающиеся функциональные возможности и использует различный синтаксис. Здесь мы не будем подробно обсуждать курсоры API; если вы планируете использовать их, обратитесь к соответствующей документации на API и языку программирования, который вы собираетесь применить.
Понятие о хранимых процедурах
Хранимые процедуры – не единственное средство выполнения операторов Transact-SQL. Мы уже сталкивались с SQL-сценариями и с возможностью передавать команды непосредственно из приложения
Понятие о триггерах
Преимущество триггерной процедуры состоит в том, что SQL Server автоматически вызывает ее при обращении к команде, для которой она определена. Это повышает устойчивость базы данных к внешним воздействиям (робастность). Вам нет нужды беспокоиться о том, чтобы клиенты базы данных – пользователи или приложения – разбирались и пользовались всеми зависимостями данных и установленными для них правилами.
Понятие о пользовательских функциях
Пользовательские функции в зависимости от типа данных возвращаемых ими значений могут быть скалярными или табличными. Кроме того, они различаются по детерминизму. Детерминизм функции определяется постоянством ее результатов.
Database Programming & Design
Если говорить по-простому, промежуточное ПО обеспечивает простой для использования API (Application Programming Interface - интерфейс прикладного программирования) между приложением и требуемыми для него ресурсами. Например, если производится Java-апплет, для работы которого требуются внешние данные, можно использовать классы пакета JDBC (Java Database Connectivity) для доступа к информации из любого числа баз данных. Классы JDBC
скрывают от разработчика сложности целевой базы данных и позволяют использовать любую базу данных без потребности понимания ее специфических особенностей.
Аналогичные возможности обеспечивает ODBC (Open Database Connectivity) для приложений "клиент-сервер", работающих в среде Windows, и средства, подобные
Borland Database Engine (BDE).
Возможности промежуточного ПО не ограничиваются обеспечением доступа к базам данных. Продукты этого рода также дают возможность прозрачного доступа на уровне API к другим системам и их сервисам без потребности знать, что из себя представляют эти системы. Слой промежуточного ПО может найти систему, используя какой-либо вид сервиса именования, вызвать удаленный процесс и возвратить ответ вызывающему процессу. К соответствующей категории промежуточного ПО относятся Distributed Computing Environment (DCE) компании , продукты, основанные на распределенной объектной технологии CORBA (Common Object Request Broker Architecture - общая архитектура брокера объектных заявок), и большинство продуктов промежуточного ПО, основанных на передаче сообщений (Message-Oriented Middleware - MOM).
Что такое промежуточное ПО?
Одной из тенденций в мире современного промежуточного ПО является движение к стандартам, включая не только те, которые разрабатываются комитетами по стандартизации (например, CORBA), но и стандарты, предлагаемые мощными компаниями-производителями. В прошлом продукты промежуточного ПО основывались на частных предложениях, которые не предполагали возможности интероперабельности. Сегодня компании, производящие промежуточное ПО, учатся использовать стандарты, такие как CORBA или DCOM (Distributed Component Object Model) в качестве базовой модели продуктов. DCOM служит стандартной базой в однородной среде Windows.
Матрица объектно-реляционных свойств
Почему эта статья настолько важна для меня? Во-первых, потому что именно эта статья привлекла меня к работе в области баз данных. Я ненавидела свой аспирантский курс по базам данных, который отставил меня в уверенности, что в этой области нет ничего, кроме скучных вопросов моделирования баз данных. Вместо этого я изучала распределенные алгоритмы и в конце концов сделала диссертацию про распознавание распределенных тупиковых ситуаций. Очевидно, что это имело отношение к базам данных, и благодаря этой связи - которую я почти не признавала, поскольку это означало работу в области баз данных -- мне удалось получить работу в IBM.
Следующее поколение
Во многих своих публикациях и выступлениях автор обращал внимание на то, что технологии складов данных появилась по причине провала технологии распределенных СУБД и необходимости каким-то образом решать "проблему островков данных". Распределенные и неоднородные системы баз данных начинают возвращаться с наличием множества продуктов установившихся и начинающих поставщиков. Эти продукты позволяют получить доступ с содержимому баз данных, располагаемых на различных платформах, в рамках одной информационной или аналитической операции без потребности предварительного копирования всей этой информации в одну базу данных как это делается при классической организации складов данных
Основы проектирования приложений баз данных
Интерфейс ODBC (Open Database Connectivity) был разработан фирмой Microsoft как открытый интерфейс доступа к базам данных. Он предоставляет унифицированные средства взаимодействия прикладной программы, называемой клиентом (или приложением-клиентом), с сервером - базой данных.
В основу интерфейса ODBC были положены спецификация CLI-интерфейса (Call-Level Interface), разработанная X/Open, и ISO/IEC для API баз данных, а также язык SQL (Structured Query Language) как стандарт языка доступа к базам данных.
Интерфейс ODBC проектировался для поддержки максимальной интероперабельности приложений, которая обеспечивает унифицированный доступ любого приложения, использующего ODBC, к различным источникам данных. Так, если приложение, соответствующее стандарту ODBC и SQL, первоначально разрабатывалось для работы с базой данных Microsoft Access, а затем таблицы этой базы были перенесены в базу данных Microsoft SQL Server или базу данных Oracle, то приложение сможет и дальше обрабатывать эти данные без внесения дополнительных изменений.
Основа ODBC
Для взаимодействия с базой данных приложение-клиент вызывает функции интерфейса ODBC, которые реализованы в специальных модулях, называемых ODBC-драйверами. Как правило, ODBC-драйверы - это DLL-библиотеки, при этом одна DLL-библиотека может поддерживать несколько ODBC-драйверов. При установке на компьютер любого SQL-сервера (базы данных, поддерживающей один из стандартов языка SQL, например, SQL-92) автоматически выполняется регистрация в реестре Windows и соответствующего ODBC-драйвера.
Основные функции ODBC
Как уже отмечалось в предыдущей лекции, все функции ODBC API условно можно разделить на четыре группы: основные функции ODBC, обеспечивающие взаимодействие с источником данных; функции установки (setup DLL); функции инсталляции (installer DLL) ODBC и источников данных;функции преобразования данных (translation DLL), вызываемые при передаче данных от драйвера к источнику данных или обратно.
Схема доступа к источнику данных с использованием ODBC API
Первым шагом при реализации доступа к источнику данных посредством ODBC API без применения пула соединений является создание дескриптора окружения. После выделения памяти под дескриптор окружения приложение должно вызвать функцию SQLSetEnvAttr для задания значения атрибуту дескриптора окружения SQL_ATTR_ODBC_VERSION. Если не установить номер версии ODBC API, то при создании дескриптора соединения функция SQLAllocHandle вернет код ответа SQLSTATE равным HY010, что соответствует коду произошедшей ошибки.
Схема извлечения данных с использованием ODBC API
Для извлечения данных с использованием ODBC API сначала следует вызвать функцию, выполняющую SQL-оператор, который определяет формируемый результирующий набор. И только затем можно приступать к выборке данных. ODBC API предоставляет два способа извлечения данных из результирующего набора: с предварительным связыванием полей результирующего набора с переменными основного языка программирования;прямая выборка каждого поля результирующего набора в указываемую переменную основного языка программирования.
Параметры SQL-оператора
Применение параметров позволяет формировать SQL-операторы непосредственно во время выполнения приложения. Так, при использовании многошагового интерфейса, при котором процесс компиляции и выполнения SQL-оператора происходит как последовательные действия, достаточно откомпилировать SQL-оператор с параметрами вместо явного указания значений полей таблицы базы данных, а затем многократно выполнять данный оператор с различными значениями параметров.
Курсоры
Применение курсоров позволяет приложению выполнять выборку одной или нескольких строк за одну операцию извлечения данных. Также курсоры поддерживают возможность применения операторов UPDATE, INSERT и DELETE к текущей позиции курсора.
Для использования курсора следует установить соединение с базой данных и установить нужные значения атрибутам оператора, контролирующим поведение курсора.
Реализация блочной выборки строк
При использовании перемещаемого курсора для изменения текущей позиции курсора и выборки строк используется функция SQLFetchScroll. Эта функция позволяет реализовывать: относительный скроллинг - перемещение по результирующему набору в двух направлениях и на любое число строк; абсолютный скроллинг - перемещение на первую или на последнюю строку, или на строку с указанным номером.
Создание именованного курсора
Курсор, для которого определено имя, называется именованным курсором. Ассоциировать имя курсора с активным дескриптором оператора можно вызовом функции SQLSetCursorName. В том случае, если эта функция не вызывается явным образом, драйвер при необходимости может генерировать имя курсора при выполнении SQL-оператора.
Объектная модель OLE DB
Можно сказать, что OLE DB - это метод доступа к любым данным через стандартные COM-интерфейсы, вне зависимости от типа данных и места их расположения. В качестве данных могут выступать базы данных, простые документы, таблицы Excel и любые другие источники данных. В отличие от доступа, предоставляемого посредством драйверов OBDC, OLE DB позволяет реализовывать доступ к источникам данных, как с применением языка SQL (к SQL-серверам), так и к любым другим произвольным источникам данных.
Библиотека MFC
Среда Visual Studio.NET предоставляет различные подходы для реализации работы с базами данных: Применение библиотеки MFC (Microsoft Foundation Class Library). Применение ATL (Active Template Library). Использование библиотек Framework.
Библиотека MFC реализует поддержку доступа к базам данных, основанную на двух механизмах: через ODBC-драйверы; с применением провайдеров данных OLE DB.
Классы, используемые для доступа к БД
ATL предоставляет OLE DB шаблоны как С++ шаблоны для реализации клиентов и серверов OLE DB. Для реализации клиента OLE DB провайдера можно использовать следующие классы: CDataConnection - класс, управляющий соединением с источником данных и инкапсулирующий поведение объектов OLE DB "источник данных" (DataSource) и "сеанс" (Session). CDataSource - класс, соответствующий объекту OLE DB источник данных, предоставляющий соединение с источником данных через OLE DB провайдера. Для одного соединения можно создать несколько объектов сеансов (CSession).
Механизмы доступа к БД
Основными механизмами доступа к данным, поддерживаемым в Delphi, являются: ODBC - доступ через ODBC-драйверы БД или BDE-драйверы; OLE DB - доступ с использованием провайдеров данных (OLE DB - это метод доступа к любым данным через стандартный COM-интерфейс); средства dbExpress, использующие легковесные драйверы БД; средства доступа к распределенным наборам данных в многозвенной архитектуре.
Работа с базами данных
При реализации доступа через SQLJ SQL-операторы встраиваются непосредственно в код на языке Java, а затем обрабатываются SQLJ-предкомпилятором. Обычный SQLJ-предкомпилятор ограничивает синтаксис встраиваемых SQL-операторов стандартом SQL-92. Однако при применении SQLJ-предкомпиляторов, ориентированных на конкретные СУБД, допускается использование конструкций языка SQL, реализуемых в этих СУБД. Так, SQLJ-предкомпилятор для Oracle 9i позволяет не только статическое, но и динамическое встраивание SQL-операторов.
Создание сервлета, используемого для публикации данных
Создание нового проекта и размещение в нем Web-приложения. Добавление в Web-приложение нового сервлета (используя команду меню File|New и выбирая на вкладке Web пиктограмму Servlet). Для возможности обработки GET и POST HTTP-запросов в мастере Servlet Wizard на панели Implement methods следует установить флажки для методов doGet и doPost. Создание модуля данных и определение подключаемого источника данных. Создание HTML-файла, отображающего форму для публикации данных.
