УМКД 'Автоматизированные базы данных'
Download
Report
Transcript УМКД 'Автоматизированные базы данных'
Уральский государственный университет
им. А.М. Горького
математико-механический факультет
«Автоматизированные базы данных»
лекции-презентации
Стихиной Т. К.,
к.ф.-м.н. , доцент кафедры информатики
и процессов управления
Список литературы:
1. Гектор Гарсиа-Молина, Джеффри Д. Ульман, Дженнифер
Уидом «Системы баз данных. Полный курс».:Пер. с англ.М.:Издательский дом «Вильямс», 2003.
2. Советов Б.Я., Цехановский В.В., Чертовский В.Д. «Базы
данных. Теория и практика». Учебник для вузов. М.: Высшая
школа., 2005.
3. Диго С.М., «Базы данных. Проектирование и использование»:
Учебник.-М.: «Финансы и статистика», 2005.
4. Дейт К.Дж. «Введение в системы баз данных».-8-е изд.:Пер. с
англ.-СПб: Издательский дом «Вильямс», 2006.
5. Хомоненко А.Д. Базы данных: Учебник для вузов, СПб.,
Корона принт, 2006.
6. Астахова И.Ф. и др. «SQL в примерах и задачах».
7. Электронный справочник «Books Online» СУБД Microsoft SQL
Server.
Содержание курса:
Лекция №1 Банки, базы,структуры
Лекция №2 Модели БД
Лекция №3 Нормализация
Лекция №4 Целостность и реляционные
операции
Лекция №5 СУБД, исторические моменты и
факты
Лекция №6 SQL(часть1)
Лекция №7 SQL(часть2)
Лекция №8 DDL
Содержание(продолжение):
Лекция №9 Использование сценариев
Лекция №10 DML особенности применения
Лекция № 11 Views – Представления
Лекция № 12 Транзакции,триггеры
Лекция № 13 Параллельность и блокировки
Лекция № 14 Репликации , дублирование,
восстановление
Лекция № 15 Поиск и индексация
Лекция №16 Механизмы доступа к данным.
P.S. В презентациях использованы некоторые тексты скриптов Кобзева Е.С.
Лекция №1 Банки, базы,структуры
Базы данных и системы управления .
Data bank (банк данных)-совокупность
данных(файлов данных, баз данных) об
одной предметной области.
Data base(база данных)-совокупность
взаимосвязанных данных, используемых
несколькими приложениями под
управлением системы управления базой
данных.
База данных - динамически обновляемая
модель внешнего мира с использованием
единого хранилища.
Докомпьютерные базы данных:
•библиотечные каталоги
•реестры ценностей
1880 г. – перепись населения в США отняла 7 лет жизни
у 1500 сотрудников.
В конкурсе по оптимизации этой работы победил
Герман Холерит, придумавший кодировать информацию
в перфокартах.
Компьютеры были созданы для решения
вычислительных задач, со временем они все чаще
стали использоваться для построения систем
обработки документов, а точнее, содержащейся в них
информации.
Система управления базой данных более
широкое понятие, чем база данных.
Основные черты СУБД (DBMS):
•обеспечение постоянного хранения большого
объема данных
•предоставление программного интерфейса для
доступа и манипуляции данными
•управление транзакциями – обеспечение
одновременной работы нескольких конкурирующих
запросов
Первые коммерческие СУБД на базе ЭВМ возникли в
1960х.
Сферы применения:
•системы бронирование билетов
•банковские системы
•корпоративные приложения (учет труда и
заработной платы)
Такие СУБД базировались на файловых системах и не
могли обеспечить в частности контроль над
транзакциями.
1970 г. Ted Codd “A relational model for large shared
data banks” – работа сотрудника IBM Кодда,
инициировавшая появление реляционных СУБД, –
наиболее распространенных в настоящее время.
Обычно современная СУБД содержит
следующие компоненты:
•ядро, которое отвечает за управление данными во
внешней и оперативной памяти и журнализацию
•процессор языка базы данных, обеспечивающий
оптимизацию запросов на извлечение и
изменение данных и создание, как правило,
машинно-независимого исполняемого внутреннего
кода
•подсистему поддержки времени исполнения,
которая интерпретирует программы манипуляции
данными, создающие пользовательский
интерфейс с СУБД
•а также, сервисные программы (внешние
утилиты), обеспечивающие ряд дополнительных
возможностей
по обслуживанию информационной системы.
Программа
во внутреннем
коде СУБД
Подсистема времени исполнения
Программа
в машинном
коде
Операторы языка общего
пользования
Операторы языка управления
Процессор
запросов
Ядр
о
СУБ
Д
Физ
иче
ска
я
база
дан
ных
Представление данных с помощью модели
"сущность-связь".
Прежде, чем приступать к созданию системы
автоматизированной
обработки
информации,
разработчик должен сформировать понятия о
предметах, фактах и событиях, которыми будет
оперировать данная система.
Для того, чтобы привести эти понятия к той
или иной модели данных, необходимо заменить их
информационными представлениями. Одним из
наиболее удобных инструментов унифицированного
представления
данных,
независимого
от
реализующего его программного обеспечения,
является
модель
"сущность-связь"
(entity
relationship model, ER - model).
Модель "сущность-связь" основывается на некой
важной семантической информации о реальном мире
и предназначена для логического представления
данных. Она определяет значения данных в контексте
их взаимосвязи с другими данными. Важным для нас
является тот факт, что из модели "сущность-связь"
могут быть порождены все существующие модели
данных (иерархическая, сетевая, реляционная,
объектная), поэтому она является наиболее общей.
Модель "сущность-связь" была предложена в 1976 г.
Питером Пин-Шэн Ченом
Модель "сущность-связь" не определяет операций над
данными и ограничивается описанием только их
логической структуры.
Элементы модели
Сущность (entity) - это объект, который может быть
идентифицирован неким способом, отличающим его от
других объектов. Примеры: конкретный человек,
предприятие, событие и т.д.
Набор сущностей (entity set) - множество сущностей
одного типа (обладающих одинаковыми свойствами).
Примеры: все люди, предприятия, праздники и т.д.
Наборы сущностей не обязательно должны быть
непересекающимися. Например, сущность,
принадлежащая к набору МУЖЧИНЫ, также
принадлежит набору ЛЮДИ.
Сущность фактически представляет из себя множество
атрибутов, которые описывают свойства всех членов
данного набора сущностей.
Пример:
рассмотрим множество работников некого предприятия.
Каждого из них можно описать с помощью
характеристик табельный номер, имя, возраст. Поэтому,
сущность СОТРУДНИК имеет атрибуты
ТАБЕЛЬНЫЙ_НОМЕР, ИМЯ, ВОЗРАСТ. Используя
нотацию языка Pascal этот факт можно представить как:
type employee = record number : string[6]; name :
string[50]; age : integer; end;
В дальнейшем для определения сущности и ее атрибутов
будем использовать обозначение вида
СОТРУДНИК (ТАБЕЛЬНЫЙ_НОМЕР, ИМЯ, ВОЗРАСТ).
Множество значений (область определения) атрибута
называется доменом. Например, для атрибута ВОЗРАСТ
домен (назовем его ЧИСЛО_ЛЕТ) задается интервалом
целых чисел больших нуля, поскольку людей с
отрицательным возрастом не бывает.
В статье П.Чена атрибут определяется как функция,
отображающая набор сущностей в набор значений или в
декартово произведение наборов значений. Так атрибут
ВОЗРАСТ производит отображение в набор значений
(домен) ЧИСЛО_ЛЕТ. Атрибут ИМЯ производит
отображение в декартово произведение наборов значений
ИМЯ, ФАМИЛИЯ и ОТЧЕСТВО.
Отсюда определяется ключ сущности - группа
атрибутов, такая, что отображение набора сущностей в
соответствующую группу наборов значений является
взаимнооднозначным отображением.
Другими словами: ключ сущности - это один или
более атрибутов уникально определяющих данную
сущность.
Ключем сущности СОТРУДНИК является
атрибут ТАБЕЛЬНЫЙ_НОМЕР (конечно, только в том
случае, если все табельные номера на предприятии
уникальны).
Связь (relationship) - это ассоциация, установленная между
несколькими сущностями. Примеры:
•поскольку каждый сотрудник работает в каком-либо отделе,
между сущностями СОТРУДНИК и ОТДЕЛ существует связь
"работает в" или ОТДЕЛ-РАБОТНИК;
•так как один из работников отдела является его
руководителем, то между сущностями СОТРУДНИК и ОТДЕЛ
имеется связь "руководит" или ОТДЕЛ-РУКОВОДИТЕЛЬ;
•могут существовать и связи между сущностями одного типа,
например связь РОДИТЕЛЬ - ПОТОМОК между двумя
сущностями ЧЕЛОВЕК;
Связь также может иметь атрибуты.
Например, для связи ОТДЕЛ-РАБОТНИК можно задать
атрибут СТАЖ_РАБОТЫ_В_ОТДЕЛЕ.
Роль сущности в связи - функция, которую
выполняет сущность в данной связи.
Например, в связи РОДИТЕЛЬ-ПОТОМОК сущности
ЧЕЛОВЕК могут иметь роли "родитель" и "потомок".
Указание ролей в модели "сущность-связь" не является
обязательным и служит для уточнения семантики
связи.
Набор связей (relationship set) - это отношение между n
(причем n не меньше 2) сущностями, каждая из которых
относится к некоторому набору сущностей.
Пример:
сущности наборы сущностей
---------- ---------------e1 принадлежит E1
e2 принадлежит E2
...
en принадлежит En
тогда [e1,e2,...,en] - набор связей R
В случае n=2, т.е. когда связь объединяет две
сущности, она называется бинарной.
n-арный набор связей (n>2) всегда можно заменить
множеством бинарных, однако первые лучше
отображают семантику предметной области.
То число сущностей, которое может быть
ассоциировано через набор связей с другой сущностью,
называют степенью связи. Рассмотрение степеней
особенно полезно для бинарных связей. Могут
существовать следующие степени бинарных связей:
•один к одному (обозначается 1 : 1 ). Это означает, что в
такой связи сущности с одной ролью всегда
соответствует не более одной сущности с другой ролью. В
рассмотренном нами примере это связь "руководит",
поскольку в каждом отделе может быть только один
начальник, а сотрудник может руководить только в
одном отделе. Данный факт представлен на следующем
рисунке, где прямоугольники обозначают сущности, а
ромб - связь. Так как степень связи для каждой
сущности равна 1, то они соединяются одной линией.
•Другой важной характеристикой связи помимо ее
степени является класс принадлежности входящих в
нее сущностей или кардинальность связи. Так как в
каждом отделе обязательно должен быть
руководитель, то каждой сущности "ОТДЕЛ"
непременно должна соответствовать сущность
"СОТРУДНИК". Однако, не каждый сотрудник
является руководителем отдела, следовательно в
данной связи не каждая сущность "СОТРУДНИК"
имеет ассоциированную с ней сущность "ОТДЕЛ".
Таким образом, говорят, что сущность
"СОТРУДНИК" имеет обязательный класс
принадлежности (этот факт обозначается также
указанием интервала числа возможных вхождений
сущности в связь, в данном случае это 1,1), а
сущность "ОТДЕЛ" имеет необязательный класс
принадлежности (0,1).
Теперь данную связь мы можем описать как 0,1:1,1.
В дальнейшем кардинальность бинарных связей
степени 1
будем обозначать следующим образом:
•один ко многим ( 1 : n ). В данном случае сущности с
одной ролью может соответствовать любое число сущностей
с другой ролью. Такова связь ОТДЕЛ-СОТРУДНИК. В
каждом отделе может работать произвольное число
сотрудников, но сотрудник может работать только в одном
отделе.
Графически степень связи n отображается "древообразной"
линией, так это сделано на следующем рисунке.
Данный рисунок дополнительно иллюстрирует тот факт, что
между двумя сущностями может быть определено несколько
наборов связей.
Здесь также необходимо учитывать класс
принадлежности сущностей. Каждый сотрудник
должен работать в каком-либо отделе, но не каждый
отдел (например, вновь сформированный) должен
включать хотя бы одного сотрудника. Поэтому
сущность "ОТДЕЛ" имеет обязательный, а сущность
"СОТРУДНИК" необязательный классы
принадлежности. Кардинальность бинарных связей
степени n
будем обозначать так:
•много к одному (n : 1 ). Эта связь аналогична
отображению 1 : n.
Предположим, что рассматриваемое нами
предприятие строит свою деятельность на основании
контрактов, заключаемых с заказчиками. Этот факт
отображается в модели "сущность-связь" с помощью
связи КОНТРАКТ-ЗАКАЗЧИК, объединяющей
сущности
КОНТРАКТ(НОМЕР, СРОК_ИСПОЛНЕНИЯ,
СУММА)
и ЗАКАЗЧИК(НАИМЕНОВАНИЕ, АДРЕС).
Так как с одним заказчиком может быть заключено
более одного контракта, то связь КОНТРАКТЗАКАЗЧИК между этими сущностями будет иметь
степень n : 1.
•многие ко многим ( n : n ). В этом случае каждая из
ассоциированных сущностей может быть представлена
любым количеством экземпляров. Пусть на
рассматриваемом нами предприятии для выполнения
каждого контракта создается рабочая группа, в которую
входят сотрудники разных отделов. Поскольку каждый
сотрудник может входить в несколько (в том числе и ни
в одну) рабочих групп, а каждая группа должна
включать не менее одного сотрудника, то связь между
сущностями СОТРУДНИК и РАБОЧАЯ_ГРУППА имеет
степень n : n.
Если существование сущности x зависит от
существования сущности y, то x называется
зависимой сущностью (иногда сущность x называют
"слабой", а "сущность" y - сильной). В качестве
примера рассмотрим связь между ранее описанными
сущностями РАБОЧАЯ_ГРУППА и КОНТРАКТ.
Рабочая группа создается только после того, как будет
подписан контракт с заказчиком, и прекращает свое
существование по выполнению контракта. Таким
образом, сущность РАБОЧАЯ_ГРУППА является
зависимой от сущности КОНТРАКТ. Зависимую
сущность
будем
обозначать
двойным
прямоугольником, а ее связь с сильной сущностью
линией со стрелкой:
Заметим, что кардинальность связи для сильной
сущности всегда будет (1,1). Класс принадлежности и
степень связи для зависимой сущности могут быть
любыми. Предположим, например, что рассматриваемое
нами предприятие пользуется несколькими
банковскими кредитами, которые редставляются
набором сущностей КРЕДИТ (НОМЕР_ДОГОВОРА,
СУММА, СРОК_ПОГАШЕНИЯ, БАНК). По каждому
кредиту должны осуществляться выплаты процентов и
платежи в счет его погашения. Этот факт
представляется набором сущностей ПЛАТЕЖ(ДАТА,
СУММА) и набором связей "осуществляется по". В том
случае, когда получение запланированного кредита
отменяется, информация о нем должна быть удалена из
базы даных. Соответственно, должны быть удалены и
все сведения о плановых платежах по этому кредиту.
Таким образом, сущность ПЛАТЕЖ зависит от
сущности КРЕДИТ.
Диаграмма "сущность-связь".
Очень важным свойством модели "сущностьсвязь" является то, что она может быть представлена в
виде графической схемы. Это значительно облегчает
анализ предметной области. Существует несколько
вариантов обозначения элементов диаграммы
"сущность-связь", каждый из которых имеет свои
положительные черты.
Список используемых обозначений.
имя сущности
Набор независимых
сущностей
имя сущности
Набор зависимых
сущностей
имя атрибута
Атрибут
имя атрибута
Ключевой атрибут
имя связи
Набор связей
Атрибуты с сущностями и сущности со связями
соединяются прямыми линиями. При этом для
указания кардинальностей связей используются
обозначения, введенные выше.
В процессе построения диаграммы можно выделить
несколько очевидных этапов:
1. Идентификация представляющих интерес сущностей
и связей.
2. Идентификация семантической информации в
наборах связей (например, является ли некоторый
набор связей отображением 1:n).
3. Определение кардинальностей связей.
4. Определение атрибутов и наборов их значений
(доменов).
5. Организация данных в виде отношений "сущностьсвязь".
В качестве примера построим диаграмму,
отображающую связь данных для подсистемы учета
персонала предприятия.
Тренарная связь
Как уже отмечалось выше, каждый n-арный набор
связей можно заменить несколькими бинарными
наборами. Сейчас как раз представляется удобный
случай, чтобы оценить преимущества каждого из этих
способов представления связей.
Тренарная связь, показанная здесь, безусловно несет
более полную информацию о предметной области.
Действительно, она однозначно отображает тот факт,
что оклад сотрудника зависит от его должности,
отдела, где он работает, и ставки. Однако, в этом
случае возникают некоторые проблемы с
определением степени связи.
Хотя, как было сказано, каждый работник может занимать
несколько должностей, а в штате каждого отдела существуют
вакансии с различными должностями, тем не менее класс
принадлежности сущности ДОЛЖНОСТЬ на приведенном рисунке
установлен в (1,1). Это объясняется тем, что ДОЛЖНОСТЬ
ассоциируется фактически не с сущностями СОТРУДНИК и
ОТДЕЛ, а со связью между ними. Обозначать этот факт
предлагается так, как это показано на следующей диаграмме:
•Попытаемcя отобразить ассоциации сотрудников,
отделов и должностей с помощью бинарных связей.
•В этом случае для адекватного описания
семантики предметной области необходимо ввести
еще одну сущность ШТАТНАЯ_ЕДИНИЦА,
которая фактически заменяет собой связь
РАБОТАЕТ_В в абстрактной сущности и поэтому
имеет атрибут ставка.
Рассмотрим теперь более внимательно
информационный объект "заказчик".
Лекция №2 Модели БД
Иерархическая модель данных.
Организация данных в СУБД иерархического типа
определяется в терминах: элемент, агрегат, запись (группа),
групповое отношение, база данных.
•Атрибут (элемент данных) - наименьшая единица структуры
данных. Обычно каждому элементу при описании базы данных
присваивается уникальное имя. По этому имени к нему
обращаются при обработке. Элемент данных также часто
называют полем.
•Запись - именованная совокупность атрибутов. Использование
записей позволяет за одно обращение к базе получить
некоторую логически связанную совокупность данных. Именно
записи изменяются, добавляются и удаляются. Тип записи
определяется составом ее атрибутов. Экземпляр записи конкретная запись с конкретным значением элементов
•Групповое отношение - иерархическое отношение между
записями двух типов. Родительская запись (владелец
группового отношения) называется исходной записью, а
дочерние записи (члены группового отношения) подчиненными. Иерархическая база данных может хранить
Корневая запись каждого дерева обязательно должна содержать
ключ с уникальным значением.
Ключи некорневых записей должны иметь уникальное
значение только в рамках группового отношения.
Каждая запись идентифицируется полным сцепленным
ключом, под которым понимается совокупность ключей всех
записей от корневой по иерархическому пути.
При графическом изображении групповые отношения
изображают дугами ориентированного графа, а типы записей вершинами (диаграмма Бахмана).
Для групповых отношений в иерархической модели
обеспечивается автоматический режим включения и
фиксированное членство. Это означает, что для запоминания
любой некорневой записи в БД должна существовать ее
родительская запись.
При удалении родительской записи автоматически удаляются
все подчиненные.
Пример:
Рассмотрим следующую модель данных предприятия:
предприятие состоит из отделов, в которых работают
сотрудники. В каждом отделе может работать несколько
сотрудников, но сотрудник не может работать более чем в
одном отделе.
Поэтому, для информационной системы управления
персоналом необходимо создать групповое отношение,
состоящее
из
родительской
записи
ОТДЕЛ
(НАИМЕНОВАНИЕ_ОТДЕЛА, ЧИСЛО_РАБОТНИКОВ) и
дочерней записи СОТРУДНИК (ФАМИЛИЯ, ДОЛЖНОСТЬ,
ОКЛАД) (Для простоты полагается, что имеются только две
дочерние записи).
Для автоматизации учета контрактов с заказчиками необходимо
создание еще одной иерархической структуры : заказчик контракты с ним - сотрудники, задействованные в работе над
контрактом.
Это дерево будет включать записи
ЗАКАЗЧИК(НАИМЕНОВАНИЕ_ЗАКАЗЧИКА, АДРЕС),
КОНТРАКТ(НОМЕР, ДАТА,СУММА), ИСПОЛНИТЕЛЬ
(ФАМИЛИЯ, ДОЛЖНОСТЬ, НАИМЕНОВАНИЕ_ОТДЕЛА)
Из этого примера видны недостатки иерархических БД:
•Частично дублируется информация между записями
СОТРУДНИК и ИСПОЛНИТЕЛЬ (такие записи называют
парными), причем в иерархической модели данных не
предусмотрена поддержка соответствия между парными
записями.
•Иерархическая модель реализует отношение между исходной и
дочерней записью по схеме 1:N, то есть одной родительской
записи может соответствовать любое число дочерних. Допустим
теперь, что исполнитель может принимать участие более чем в
одном контракте (т.е. возникает связь типа M:N). В этом случае
в базу данных необходимо ввести еще одно групповое
отношение, в котором ИСПОЛНИТЕЛЬ будет являться
исходной записью, а КОНТРАКТ - дочерней (рис. (c)). Таким
образом, мы опять вынуждены дублировать информацию.
Операции над данными, определенные в иерархической
модели:
•ДОБАВИТЬ в базу данных новую запись. Для корневой записи
обязательно формирование значения ключа.
•ИЗМЕНИТЬ значение данных предварительно извлеченной
записи. Ключевые данные не должны подвергаться
изменениям.
•УДАЛИТЬ некоторую запись и все подчиненные ей записи.
•ИЗВЛЕЧЬ:
•извлечь корневую запись по ключевому значению, допускается
также последовательный просмотр корневых записей
•извлечь следующую запись (следующая запись извлекается в
порядке левостороннего обхода дерева)
•В операции ИЗВЛЕЧЬ допускается задание условий выборки
(например, извлечь сотрудников с окладом более 1 тысячи руб.)
Как видим, все операции изменения применяются только к
одной "текущей" записи (которая предварительно извлечена из
базы данных). Такой подход к манипулированию данных
получил название "навигационного".
Ограничения целостности.
Поддерживается только целостность связей между владельцами
и членами группового отношения (никакой потомок не может
существовать без предка). Как уже отмечалось, не
обеспечивается автоматическое поддержание соответствия
парных записей, входящих в разлные иерархии.
Сетевая модель данных
На разработку этого стандарта большое влияние оказал
американский ученый Ч.Бахман. Основные принципы сетевой
модели данных были разработны в середине 60-х годов,
эталонный вариант сетевой модели данных описан в отчетах
рабочей группы по языкам баз данных (COnference on DAta
SYstem Languages) CODASYL (1971 г.).
Сетевая модель данных определяется в тех же терминах, что и
иерархическая. Она состоит из множества записей, которые
могут быть владельцами или членами групповых отношений.
Связь между между записью-владельцем и записью-членом
также имеет вид 1:N.
Основное различие этих моделей состоит в том, что в сетевой
модели запись может быть членом более чем одного группового
отношения.
Согласно этой модели каждое групповое отношение именуется и
проводится различие между его типом и экземпляром.
Тип группового отношения задается его именем и определяет
свойства общие для всех экземпляров данного типа.
Экземпляр группового отношения представляется записьювладельцем и множеством (возможно пустым) подчиненных
записей.
При этом имеется следующее ограничение: экземпляр записи не
может быть членом двух экземпляров групповых отношений
одного типа (т.е., скажем, сотрудник из предыдущего примера,
не может работать в двух отделах).
Иерархическая структура преобразовывается в сетевую
следующим образом :
•древья (a) и (b) заменяются одной сетевой структурой, в
которой запись СОТРУДНИК входит в два групповых
отношения;
•для отображения типа M:N вводится запись
СОТРУДНИК_КОНТРАКТ, которая не имеет полей и служит
только для связи записей КОНТРАКТ и СОТРУДНИК, см. рис.
(Отметим, что в этой записи может храниться и полезная
информация, например, доля данного сотрудника в общем
вознаграждении по данному контракту.)
Каждый экземпляр группового отношения характеризуется
следующими признаками:
•способ упорядочения подчиненных записей:
•произвольный,
•хронологический /очередь/,
•обратный хронологический /стек/,
•сортированный.
•Если запись объявлена подчиненной в нескольких групповых
отношениях, то в каждом из них может быть назначен свой
способ упорядочивания.
•режим включения подчиненных записей:
•автоматический - невозможно занести в БД запись без того, чтобы
она была сразу же закреплена за неким владельцем;
•ручной - позволяет запомнить в БД подчиненную запись и не
включать ее немедленно в экземпляр группового отношения. Эта
операция позже инициируется пользователем).
•режим исключения Принято выделять три класса членства
подчиненных записей в групповых отношениях:
1.Фиксированное. Подчиненная запись жестко связана с записью
владельцем и ее можно исключить из группового отношения
только удалив. При удалении записи-владельца все
подчиненные записи автоматически тоже удаляются. В
рассмотренном выше примере фиксированное членство
предполагает групповое отношение "ЗАКЛЮЧАЕТ" между
записями "КОНТРАКТ" и "ЗАКАЗЧИК", поскольку контракт
не может существовать без заказчика.
2. Обязательное. Допускается переключение подчиненной
записи на другого владельца, но невозможно ее
существование без владельца. Для удаления записивладельца необходимо, чтобы она не имела подчиненных
записей с обязательным членством. Таким отношением
связаны записи "СОТРУДНИК" и "ОТДЕЛ". Если отдел
расформировывается, все его сорудники должны быть либо
переведены в другие отделы, либо уволены.
3. Необязательное. Можно исключить запись из группового
отношения, но сохранить ее в базе данных не прикрепляя к
другому владельцу. При удалении записи-владельца ее
подчиненные записи - необязательные члены сохраняются в
базе, не участвуя более в групповом отношении такого типа.
Примером такого группового отношения может служить
"ВЫПОЛНЯЕТ" между "СОТРУДНИКИ" и "КОНТРАКТ",
поскольку в организации могут существовать работники,
чья деятельность не связана с выполненинем каких-либо
договорных обязательств перед заказчиками.
Операции над данными.
•ДОБАВИТЬ - внести запись в БД и, в зависимости от режима
включения, либо включить ее в групповое отношение, где она
объявлена подчиненной, либо не включать ни в какое групповое
отношение.
•ВКЛЮЧИТЬ В ГРУППОВОЕ ОТНОШЕНИЕ - связать
существующую подчиненную запись с записью-владельцем.
•ПЕРЕКЛЮЧИТЬ - связать существующую подчиненную запись с
другой записью-владельцем в том же групповом отношении.
•ОБНОВИТЬ - изменить значение элементов предварительно
извлеченной записи.
•ИЗВЛЕЧЬ - извлечь записи последовательно по значению
ключа, а также используя групповые отношения - от владельца
можно перейти к записям - членам, а от подчиненной записи к
владельцу набора.
•УДАЛИТЬ - убрать из БД запись. Если эта запись является
владельцем группового отношения, то анализируется класс
членства подчиненных записей. Обязательные члены должны
быть предварительно исключены из группового отношения,
фиксированные удалены вместе с владельцем, необязательные
останутся в БД.
•ИСКЛЮЧИТЬ ИЗ ГРУППОВОГО ОТНОШЕНИЯ - разорвать
связь между записью-владельцем и записью-членом.
Ограничения целостности.
Как и в иерархической модели, обеспечивается только
поддержание целостности по ссылкам (владелец отношения член отношения).
Реляционная модель данных
Реляционная модель предложена сотрудником компании IBM
Е.Ф.Коддом в 1970 г.
В настоящее время эта модель является фактическим
стандартом, на который ориентируются практически все
современные коммерческие СУБД.
Структура данных.
В реляционной модели достигается гораздо более высокий
уровень абстракции данных, чем в иерархической или сетевой.
В статье Е.Ф.Кодда утверждается, что "реляционная модель
предоставляет средства описания данных на основе только их
естественной структуры, т.е. без потребности введения какойлибо дополнительной структуры для целей машинного
представления".
Другими словами, представление данных не зависит от способа
их физической организации. Это обеспечивается за счет
использования математической теории отношений (само
название "реляционная" происходит от английского relation "отношение").
Определения:
• Декартово произведение: Для заданных конечных множеств
D1,D2,..,Dn (не обязательно различных) декартовым произведением
D1*D2*..*Dn называется множество произведений вида:
d1*d2*..*dn, где
Пример: если даны два множества A (a1,a2,a3) и B (b1,b2), их
декартово произведение будет иметь вид С=A*B (a1*b1, a2*b1,
a3*b1, a1*b2, a2*b2, a3*b2)
•Отношение: Отношением R, определенным на множествах
D1,D2,..,Dn называется подмножество декартова произведения
D1*D2*..*Dn. При этом:
•множества D1,D2,..,Dn называются доменами отношения
•элементы декартова произведения D1*D2*..*Dn называются
кортежами
•число n определяет степень отношения ( n=1 - унарное,
n=2 - бинарное, ..., n-арное)
•количество кортежей называется мощностью отношения
Пример: на множестве С из предыдущего примера могут быть
определены отношения R1 (a1*b1, a3*b2) или R2 (a1*b1, a2*b1,
a1*b2)
Отношения удобно представлять в виде таблиц. На рисунку
ниже представлена таблица (отношение степени 5), содержащая
некоторые сведения о работниках гипотетического
предприятия.
Строки таблицы соответствуют кортежам. Каждая строка
фактически представляет собой описание одного объекта
реального мира (в данном случае работника), характеристики
которого содержатся в столбцах.
Можно провести аналогию между элементами реляционной
модели данных и элементами модели "сущность-связь".
Реляционные отношения соответствуют наборам сущностей, а
кортежи - сущностям.
Поэтому, также как и в модели "сущность-связь" столбцы в
таблице, представляющей реляционное отношение, называют
атрибутами.
Каждый атрибут определен на домене, поэтому домен можно
рассматривать как множество допустимых значений данного
атрибута.
Несколько атрибутов одного отношения и даже атрибуты
разных отношений могут быть определены на одном и том же
домене.
В примере выше, атрибуты "Оклад" и "Премия" определены
на домене "Деньги". Поэтому, понятие домена имеет
семантическую нагрузку: данные можно считать сравнимыми
только тогда, когда они относятся к одному домену. Таким
образом, в рассматриваемом нами примере сравнение атрибутов
"Табельный номер" и "Оклад" является семантически
некорректным, хотя они и содержат данные одного типа.
Именнованное множество пар "имя атрибута - имя домена"
называется схемой отношения. Мощность этого множества называют степенью или "арностью" отношения. Набор
именованных схем отношений представляет из себя схему базы
данных.
Атрибут, значение которого однозначно идентифицирует
кортежи, называется ключевым (или просто ключом). В нашем
случае ключом является атрибут "Табельный номер",
поскольку его значение уникально для каждого работника
предприятия. Если кортежи идентифицируются только
сцеплением значений нескольких атрибутов, то говорят, что
отношение имеет составной ключ.
Отношение может содержать несколько ключей. Всегда один из
ключей объявляется первичным, его значения не могут
обновляться. Все остальные ключи отношения называются
возможными ключами.
В отличие от иерархической и сетевой моделей данных в
реляционной отсутствует понятие группового отношения.
Для отражения ассоциаций между кортежами разных отношений
используется дублирование их ключей.
Рассмотренный в предыдущем разделе пример базы данных,
содержащей сведения о подразделениях предприятия и
работающих в них сотрудниках, применительно к реляционной
модели будет иметь вид:
Например, связь между отношениями ОТДЕЛ и СОТРУДНИК
создается путем копирования первичного ключа "Номер_отдела" из
первого отношения во второе. Таким образом:
•для того, чтобы получить список работников данного
подразделения, необходимо
1.из таблицы ОТДЕЛ установить значение атрибута
"Номер_отдела", соответствующее данному
"Наименованию_отдела"
2.выбрать из таблицы СОТРУДНИК все записи, значение атрибута
"Номер_отдела" которых равно полученному на предыдушем шаге.
•для того, чтобы узнать в каком отделе работает сотрудник, нужно
выполнить обратную операцию:
1.определяем "Номер_отдела" из таблицы СОТРУДНИК
2.по полученному значению находим запись в таблице ОТДЕЛ.
Атрибуты, представляющие собой копии ключей других
отношений, называются внешними ключами.
Свойства отношений.
1. Отсутствие кортежей-дубликатов. Из этого свойства
вытекает наличие у каждого кортежа первичного ключа.
Для каждого отношения, по крайней мере, полный набор его
атрибутов является первичным ключом. Однако, при
определении первичного ключа должно соблюдаться
требование "минимальности", т.е. в него не должны
входить те атрибуты, которые можно отбросить без
ущерба для основного свойства первичного ключа однозначно определять кортеж.
2. Отсутствие упорядоченности кортежей.
3. Отсутствие упордоченности атрибутов. Для ссылки на
значение атрибута всегда используется имя атрибута.
4. Атомарность значений атрибутов, т.е. среди значений домена
не могут содержаться множества значений (отношения).
Лекция №3 Нормализация
Свойства отношений.
1. Отсутствие кортежей-дубликатов. Из этого свойства вытекает
наличие у каждого кортежа первичного ключа. Для каждого
отношения, по крайней мере, полный набор его атрибутов
является первичным ключом. Однако, при определении
первичного ключа должно соблюдаться требование
"минимальности", т.е. в него не должны входить те
атрибуты, которые можно отбросить без ущерба для
основного свойства первичного ключа - однозначно определять
кортеж.
2. Отсутствие упорядоченности кортежей.
3. Отсутствие упордоченности атрибутов. Для ссылки на значение
атрибута всегда используется имя атрибута.
4. Атомарность значений атрибутов, т.е. среди значений домена не
могут содержаться множества значений (отношения).
Функциональные зависимости
Функциональной зависимостью (FD) на отношении R
называется выражение вида: «Если 2 кортежа согласуются по
атрибутам A1,A2,A3,..,An, то они также согласуются по атрибуту
B»
Формальная запись: A1A2…An B. Говорят что «A1,A2,…,An
функционально определяют B»
A1A2…An B1
A1A2…An B2
…
A1A2…An Bn
Краткая запись:
A1A2…An B1B2..Bn
название
год
Продолжительность
тип
студия
актер
Star Wars
1977
124
Color
Fox
Carrie
Fisher
Star Wars
1977
124
Color
Fox
Mark
Hamill
Star Wars
1977
124
Color
Fox
Harrison
Ford
Mighty
Ducks
1991
104
Color
Disney
Emilio
Estevez
Wayne’s
World
1992
95
Color
Paramaunt
Dana
Carvey
Wayne’s
World
1992
95
Color
Paramaunt
Mike
Meyers
Пример: Отношение
Фильмы(название, год, продолжительность, тип, студ
актер)
Можно выделить 3 функциональные зависимости:
название год продложительность
название год тип
название год студия
Краткая запись:
название год продложительность тип студия
название год актер – не FD
Множество атрибутов A={A1,A2,…,An} – ключ отношения R,
если:
1. A функционально определяет все остальные атрибуты
отношения
2. А – наименьшее по включению
Ключ для приведенного примера: {название, год, актер}
{название, год} не ключ, так как название год актер не FD
Суперключ – множество атрибутов, содержащих ключ.
Функциональная зависимость A1A2…An B1B2..Bm называется
• тривиальной, если {B,1B2,..,Bm} – подмножество {A1,A2,…,An}
•нетривиальной, если множества частично не пересекаются
•полностью нетривиальной, если множества не пересекаются
Тривиальные FD всегда выполнимы!
Пусть A={A1,A2,..,An}-множество атрибутов и S – множество
функциональных зависимостей.
Замыканием A над функциональными зависимостями из S
называется множество атрибутов B, такое, что всякое отношение,
удовлетворяющее всем функциональным зависимостям из S,
удовлетворяет также зависимости:
A1A2…An B То есть A1A2…An B следует из S
Обозначение: {A1,A2,..,An}+
Алгоритм вычисления замыкания:
1. Пусть X – вычисляемое замыкание. X={A1,A2,..,An}
2. Последовательно ищем функциональные зависимости
B1B2…BmC в S такие, что: B1,B2…,Bm X, а С X.
Добавляем C в X.
3. Повторяем шаг 2, пока возможно расширение X. Поскольку
X только растет, и множество атрибутов конечно – процесс
прекратится в некоторый момент.
4. Построенное множество X - {A1,A2,..,An}+
Пример:
Атрибуты: A,B,C,D,E,F
FDs: AB C BC AD DE CF B
Построим {A,B}+
1. X={A,B}
2. X={A,B,C}
3. X={A,B,C,D}
4. X={A,B,C,D,E}
Обоснование алгоритма. Докажем что:
1. Алгоритм не рассматривает излишних зависимостей. То есть
покажем, что если FD: A1A2…An B помечена алгоритмом (B
принадлежит замыканию), то A1A2…An B справедлива в
любом отношении, удовлетворяющем всем зависимостям из S
2. Алгоритм находит все зависимости, которые следуют из S, то
есть если зависимость не найдена алгоритмом, то она не следует
из S
1. По индукции по количеству повторения шага 2 алгоритма.
Покажем, что каждое отношение, удовлетворяющее всем
зависимостям из S, удовлетворяет также зависимости
A1A2…An D, где D – атрибут добавленный к замыканию на
очередном шаге
База: A1A2…An D, где D находится среди Ai – тривиальная
зависимость, удовлетворяется в любом отношении.
Шаг: Пусть D был добавлен при использовании правила
B1B2…Bm D. По предположению индукции A1A2…An Bi,
для I=1..m. Таким образом два любых кортежа R,
согласующиеся по атрибутам A1,A2,…,An, согласуются также
по B1,B2…,Bm. Поскольку R удовлетворяет зависимости
B1B2…Bm D, то указанные кортежи согласуются по атрибуту
D. Таким образом R удовлетворяет зависимости A1A2…An
D
2.Пусть A1A2…An B – функциональная зависимость, которая не
следует из S согласно алгоритму. То есть B не принадлежит
замыканию {A1,A2,..,An}+ над зависимостями из S. Покажем, что
A1A2…An B действительно не следует из S, то есть существует
хотя бы одно отношение, удовлетворяющее всем зависимостям из
S и не удовлетворяющее A1A2…An B.
Возьмем отношение I из 2-х кортежей, которые согласуются по
всем атрибутам из {A1,A2,..,An}+ и не согласуются по всем
оставшимся атрибутам:
{A1,A2,..,An}+ остальные атрибуты
t:
11... 11
000 .. . 000
s:
11... 11
111 .. . 111
а)Покажем, что I удовлетворяет всем функциональным
зависимостям из S.
Предположим, что в S существует FD C1C2…CkD, которой не
удовлетворяет I. Кортежи t и s, согласующиеся по атрибутам
{C1,C2,…,Ck} должны не согласовываться по атрибуту D. По
построению I такое возможно, если C1,C2,…,Ck принадлежат
{A1,A2,..,An}+ и D находится среди оставшихся атрибутов. Но это
означает, что замыкание вычислено неверно – поскольку D должен
быть добавлен к {A1,A2,..,An}+ на шаге 2. Предположение неверно.
b) Покажем, что I не удовлетворяет A1A2…An B. t и s согласуются
по всем атрибутам из замыкания, значит согласуются и по
атрибутам A1,A2,…,An. B не принадлежит замыканию
{A1,A2,..,An}+ . Следовательно I не удовлетворяет зависимости
A1A2…An B по определению замыкания
Транзитивность
Если A1A2…An B1B2..Bm и B1B2..Bm С1С2..Сk, то
A1A2…An С1С2..Сk.
Доказательство:
Построим замыкание {A1,A2,..,An}+ над двумя указанными
функциональными зависимостями. С1,С2,..,Сk принадлежат
замыканию по алгоритму построения. Значит A1A2…An
С1С2..Сk по определению замыкания.
Связь замыкания и ключей
{A1,A2,..,An}+ - множество всех атрибутов тогда и только тогда,
когда A1,A2,..,An – суперключ отношения R.
Вернемся к примеру с фильмами и киностудиями. Создадим
отношение ФильмСтудия, хранящее связь фильмов и киностудий,
без информации об актерах.
название
год
продолжи тип
тельность
124
Color
Star Wars
1977
Mighty
Ducks
Wayne’s
World
1991
104
1992
95
студия
адрес студии
Fox
Hollywood
Color
Disney
Buena Vista
Color
Paramount
Hollywood
Выделим две зависимости:
название год студия
студия адрес студии
По правилу транзитивности имеем:
название год адрес студии
Аномалии в схеме реляционной базы
1. Избыточность – информация без необходимости повторяется в
нескольких кортежах
2. Update аномалия – необходимость обновлять информацию в
нескольких кортежах
3. Delete аномалия – удаление части информации может привести к
потере другой информации. Пример – удалим единственного
актера из фильма Диснея и потеряем всю информацию о
фильме.
Для устранения аномалий используют декомпозицию отношений.
Декомпозиция отношения – разбиение атрибутов на
подмножества, определяющие схему новых отношений и
проецирование исходных кортежей в эти новые отношения.
Пример: разобьем отношение Фильмы на два, для избавления от
аномалий
1.
Фильмы1 – в него входят все атрибуты кроме «актер».
Фильмы1(название, год, продолжительность, тип, студия)
2.
Фильмы2(название,год,актер)
название
год
продолжи
тельность
тип
студия
Star Wars
1977
124
Color
Fox
Фильмы1
Mighty
Ducks
1991
104
Color
Disney
Wayne’s
World
1992
95
Color
Paramount
название
год
Актер
Star Wars
1977
Carrie
Fisher
Star Wars
1977
Mark
Hamill
Star Wars
1977
Harrison
Ford
Mighty
Ducks
1991
Emilio
Estevez
Wayne’s
World
1992
Dana
Carvey
Wayne’s
World
1992
Mike
Meyers
Фильмы2
Цель декомпозиции – заменить отношение, на несколько
отношений, не подверженных аномалиям.
Существует формальное условие, при выполнении которого
отношение не подвержено описанным аномалиям – нормальная
форма Бойса-Кодда (BCNF) .
Отношение R находится в BCNF тогда и только тогда, когда не
существует нетривиальной FD A1A2…An B1B2..Bm такой,
что {A1,A2,…,An} не является суперключом.
То есть левая часть всякой нетривиальной зависимости должна быть
суперключом (должна содержать ключ).
Пример: Отношение Фильмы(название, год, продолжительность, тип, студия,
актер) не находится в BCNF.
Выше было показано, что ключ для приведенного примера:
{название, год, актер} и имеет место зависимость:
название год продолжительность тип студия – левая часть
не суперключ.
Отношение Фильмы1(название, год, продолжительность, тип,
студия) находится в BCNF, поскольку имеется единственная
зависимость:
название год продолжительность тип студия и ее левая часть
- суперключ
Стратегия декомпозиции отношения в отношения в BCNF:
1. Выбираем нетривиальную FD A1A2…An B1B2..Bm на
которой нарушается BCNF ({A1,A2,…,An} не суперключ)
2. В первое отношение помещаем все атрибуты из левой и
правой части FD
3. Во второе отношение помещаем все атрибуты из левой части
и оставшиеся атрибуты не вошедшие в правую часть
выбранной FD.
Именно таким образом отношение фильмы было разбито на
два, относительно зависимости, нарушающей BCNF
исходного отношения:
название год продолжительность тип студия
В дальнейшем исходное отношение может быть восстановлено.
Для этого достаточно «склеить» кортежи новых отношений
по атрибутам из левой части функциональной зависимости,
которая инициирует декомпозицию.
Возьмем рассмотренное выше отношение ФильмСтудия. Были
выделены функциональные зависимости:
название год студия
студия адрес студии
(по правилу транзитивности) название год адрес студии
Кроме того очевидна зависимость: название год
продолжительность тип
Ключ: {название, год}. На зависимости: студия адрес студии
BCNF нарушается. Произведем декомпозицию:
ФильмСтудия1(студия, адрес студии)
ФильмСтудия2(название,год,продолжительность,тип,студия)
Не всегда существует возможность провести декомпозицию в
BCNF.
Пример: отношение Билеты(название
(фильма,кинотеатр,город). Предположим, что одинаковые
фильмы не заказываются одновременно одновременно в 2-х
кинотеатрах одного города и существуют кинотеатры,
показывающие несколько фильмов одновременно. Выделим
следующие FD:
кинотеатр город
название город кинотеатр
Ключи: {название, город} и {кинотеатр,название} – можно
проверить, построив замыкание, оно будет содержать все
атрибуты отношения, причем ни один из атрибутов в
одиночестве ключом не является.
FD кинотеатр город нарушает BCNF
Попытаемся провести декомпозицию:
{кинотеатр,город} {кинотеатр,название}
FD название город кинотеатр перестанет выполнятся при
склейке отношений. Например:
кинотеатр | город
колизей | екатеринбург
салют
| екатеринбург
кинотеатр | название
колизей | матрица
салют
| матрица
Склеив получаем:
кинотеатр | город
| название
колизей | екатеринбург | матрица
салют
| екатеринбург | матрица
Отношение находится в третьей нормальной форме, если для
всякой функциональной зависимости либо левая часть является
суперключом, либо все атрибуты правой части входят в ключ.
Отношение билеты находится в 3NF
Отношение находится в первой нормальной форме, если все
атрибуты атомарные.
Отношение находится во второй нормальной форме, если оно
находится в 1NF и каждый не ключевой атрибут
функционально зависит от ключа, но не зависит ни от какого
собственного подмножества ключа.
Отношение находится в третьей нормальной форме, если оно
находится в 2NF и каждый не ключевой атрибут не транзитивн
зависит от первичного ключа.
Отношение находится в BCNF, если оно находится в 3NF и
отсутствуют зависимости атрибутов первичного ключа от не
ключевых атрибутов.
Лекция №4 Целостность и реляционные операции
Ограничения целостности
Целостность данных - это механизм поддержания соответствия
базы данных предметной области. В реляционной модели данных
определены два базовых требования обеспечения целостности:
•целостность ссылок
•целостность сущностей.
Целостность сущностей.
Объект реального мира представляется в реляционной базе
данных как кортеж некоторого отношения. Требование
целостности сущностей заключается в следующем: каждый
кортеж любого отношения должен отличатся от любого другого
кортежа этого отношения (т.е. любое отношение должно
обладать первичным ключом).
Вполне очевидно, что если данное требование не соблюдается (т.е.
кортежи в рамках одного отношения не уникальны), то в базе
данных может хранится противоречивая информация об одном и
том же объекте. Поддержание целостности сущностей
обеспечивается средствами системы управления базой данных
(СУБД). Это осуществляется с помощью двух ограничений:
•при добавлении записей в таблицу проверяется уникальность
их первичных ключей
•не позволяется изменение значений атрибутов, входящих в
первичный ключ.
Целостность ссылок
Сложные объекты реального мира представляются в реляционной
базе данных в виде кортежей нескольких нормализованных
отношений, связанных между собой. При этом:
1. Связи между данными отношениями описываются в терминах
функциональных зависимостей.
2. Для отражения функциональных зависимостей между
кортежами разных отношений используется дублирование
первичного ключа одного отношения (родительского) в другое
(дочернее). Атрибуты, представляющие собой копии ключей
родительских отношений, называются внешними ключами.
Требование целостности по ссылкам состоит в следующем:
Для каждого значения внешнего ключа, появляющегося в
дочернем отношении, в родительском отношении должен
найтись кортеж с таким же значением первичного ключа.
Пусть, например, даны отношения ОТДЕЛ (N_ОТДЕЛА,
ИМЯ_ОТДЕЛА) и СОТРУДНИК (N_СОТРУДНИКА,
N_ОТДЕЛА, ИМЯ_СОТРУДНИКА), в которых хранятся
сведения о работниках предприятия и подразделениях, где они
работают. Отношение ОТДЕЛ в данной паре является
родительским, поэтому его первичный ключ "N_отдела"
присутствует в дочернем отношении СОТРУДНИК.
Требование целостности по ссылкам означает здесь, что в
таблице СОТРУДНИК не может присутствовать кортеж со
значением атрибута "N_отдела", которое не встречается в
таблице ОТДЕЛ. Если такое значение в отношении ОТДЕЛ
отсутствует, значение внешнего ключа в отношении
СОТРУДНИК считается неопределенным.
Как правило, поддержание целостности ссылок также
возлагается на систему управления базой данных. Например,
она может не позволить пользователю добавить запись,
содержащую внешний ключ с несуществующим
(неопределенным) значением.
Часто вместо выражения "целостность по ссылкам"
употребляют его синонимы "ссылочная целостность",
"целостность связей" или "требование внешнего ключа".
Реляционное исчисление.
В реляционной модели определяются два базовых механизма
манипулирования данными:
•основанная на теории множеств реляционная алгебра
•основанное на математической логике реляционное
исчисление.
Операции над данными (реляционная алгебра).
Операции обработки кортежей.
Эти операции связаны с изменением состава кортежей в каком-либо
отношении.
•ДОБАВИТЬ - необходимо задать имя отношения и ключ кортежа.
•УДАЛИТЬ - необходимо указать имя отношения, а также
идентифицировать кортеж или группу кортежей, подлежащих
удалению.
•ИЗМЕНИТЬ - выполняется для названного отношения и может
корректировать как один, так и несколько кортежей.
На входе каждой такой операции используется одно или несколько
отношений, результатом выполения операции всегда является новое
отношение. В рассмотренных ниже примерах (которые заимствованы
из книги Э.Озкарахан "Машины баз данных и управление базами
данных" -М: "Мир", 1989) используются следующие отношения:
P(D1,D2,D3) Q(D4,D5) R(M,P,Q,T) S(A,B)
1 11 x
x 1
x 101 5 a
5 a
2 11 y
x 2
y 105 3 a
10 b
3 11 z
y 1
z 500 9 a
15 c
w 50 1 b
2d
4 12 x
w 10 2 b
6a
w 300 4 b
1b
В реляционной алгебре определены следующие операций обработки отношений:
•ПРОЕКЦИЯ (ВЕРТИКАЛЬНОЕ ПОДМНОЖЕСТВО).
Операция проекции представляет из себя выборку из каждого кортежа отношения
значений атрибутов, входящих в список A, и удаление из полученного отношения
повторяющихся строк.
•ВЫБОРКА (ОГРАНИЧЕНИЕ, ГОРИЗОНТАЛЬНОЕ ПОДМНОЖЕСТВО).
На входе используется одно отношение, результат - новое отношение,
построенное по той же схеме, содержащее подмножество кортежей исходного
отношения, удовлетворяющих условию выборки.
•ОБЪЕДИНЕНИЕ.
Отношения-операнды в этом случае должны быть определены по одной схеме.
Результирующее отношение содержит все строки операндов за исключением
повторяющихся.
•ПЕРЕСЕЧЕНИЕ.
На входе операции два отношения, определенные по одной схеме. На выходе отношение, содержащие кортежи, которые присутствуют в обоих исходных
отношениях.
•РАЗНОСТЬ.
Операция во многом похожая на ПЕРЕСЕЧЕНИЕ, за исключением того, что в
результирующем отношении содержатся кортежи, присутствующие в первом и
отсутствующие во втором исходных отношениях.
•ДЕКАРТОВО ПРОИЗВЕДЕНИЕ
Входные отношения могут быть определены по разным схемам. Схема
результирующего отношения включает все атрибуты исходных. Кроме того:
•степень результирующего отношения равна сумме степеней исходных
отношений
•мощность результирующего отношения равна произведению мощностей
исходных отношений.
•СОЕДИНЕНИЕ
Данная операция имеет сходство с ДЕКАРТОВЫМ ПРОИЗВЕДЕНИЕМ. Однако,
здесь добавлено условие, согласно которому вместо полного произведения всех
строк в результирующее отношение включаются только строки, удовлетворяющие
опредленному соотношению между атрибутами соединения (А1,A2)
соответствующих отношений.
Также как и выражения реляционной алгебры формулы
реляционного исчисления определяются над отношениями
реляционных баз данных, и результатом вычисления также является
отношение.
Эти механизмы манипулирования данными различаются уровнем
процедурности:
•запрос, представленный на языке релационной алгебры, может
быть вычислен на основе вычисления элементарных
алгебраичесских операций с учетом их старшинства и возможных
скобок
•формула реляционного исчисления только устанавливает условия,
которым должны удовлетворять кортежи результирующего
отношения. Поэтому языки реляционного исчисления являются
более непроцедурными или декларативными.
Пример:
Пусть даны два отношения: СОТРУДНИКИ
(СОТР_НОМЕР, СОТР_ИМЯ, СОТР_ЗАРПЛ, ОТД_НОМЕР)
ОТДЕЛЫ(ОТД_НОМЕР, ОТД_КОЛ, ОТД_НАЧ)
Мы хотим узнать имена и номера сотрудников, являющихся
начальниками отделов с количеством работников более 10.
Выполнение этого запроса средствами реляционной алгебры
распадается на четко определенную последовательность шагов:
(1).выполнить соединение отношений СОТРУДНИКИ и ОТДЕЛЫ
по условию СОТР_НОМ = ОТДЕЛ_НАЧ.
С1 = СОТРУДНИКИ [СОТР_НОМ = ОТД_НАЧ] ОТДЕЛЫ
(2).из полученного отношения произвести выборку по условию
ОТД_КОЛ > 10
С2 = С1 [ОТД_КОЛ > 10].
(3).спроецировать результаты предыдущей операции на
атрибуты СОТР_ИМЯ, СОТР_НОМЕР
С3 = С2 [СОТР_ИМЯ, СОТР_НОМЕР]
Заметим, что порядок выполнения шагов может повлиять на
эффективность выполнения запроса. Так, время выполнения
приведенного выше запроса можно сократить, если поменять
местами этапы (1) и (2).
В этом случае сначала из отношения СОТРУДНИКИ будет
сделана выборка всех кортежей со значением атрибута
ОТДЕЛ_КОЛ > 10, а затем выполнено соединение
результирующего отношения с отношением ОТДЕЛЫ.
Машинное время экономится за счет того, что в операции
соединения участвуют меньшие отношения.
На языке реляционного исчисления данный запрос может быть
записан как:
Выдать СОТР_ИМЯ и СОТР_НОМ для СОТРУДНИКИ таких,
что
существует ОТДЕЛ с таким же, что и СОТР_НОМ значением
ОТД_НАЧ
и значением ОТД_КОЛ большим 50.
Здесь мы указываем лишь характеристики результирующего
отношения, но не говорим о способе его формирования. СУБД
сама должна решить какие операции и в каком порядке надо
выполнить над отношениями СОТРУДНИКИ и ОТДЕЛЫ.
Задача оптимизации выполнения запроса в этом случае также
ложится на СУБД.
Лекция №5 СУБД, исторические моменты и факты
•1975 — основана компания Microsoft.
•1977 — основана компания Software Development
Laboratories (ныне — Oracle Corporation).
•1978 — Software Development Laboratories переименована в
Relational Software, Inc.
•1979 — выпущена первая официальная версия СУБД Oracle
— V2.
•1980 — выпущена версия Oracle 2 для операционной
системы VAX/VMS.
•1982 — выпущена версия Oracle 3, которая стала первой
СУБД, поддерживающей обработку транзакций и способной
выполняться на разных платформах, в том числе на
мэйнфреймах и мини-ЭВМ.
•1983 — Relational Software, Inc. переименована в Oracle
Corporation.
•1984 — выпущена и перенесена на несколько различных
платформ версия Oracle 4.
•1986 — выпущена версия Oracle 5, поддерживающая
возможности создания приложений в архитектуре «клиентсервер», распределенную обработку данных, кластеры VAX.
•1987 — заключено соглашение между Microsoft и Sybase
о лицензировании СУБД Sybase (Sybase DataServer).
Oracle становится крупнейшим в мире производителем
СУБД.
•1988 — выпущена версия Oracle 6, а чуть позже
объявлено, что Oracle 6.2 будет поддерживать
симметричные кластеры с применением Oracle Parallel
Server. Oracle выпустила Unix Relational Financial
Applications. В том же году Microsoft и Ashton-Tate
анонсировали первую версию Microsoft SQL Server —
реляционную СУБД для локальных вычислительных
сетей. Новый продукт носил название AshtonTate/Microsoft SQL Server и представлял собой версию
Sybase DataServer для OS/2. Роль Ashton-Tate заключалась
в том, что эта фирма предоставила dBASE IV,
используемую для разработки приложений.
•1989 — Oracle реализовала поддержку OLTP, продукты
фирмы стали доступны в 86 странах мира. В мае этого года
увидела свет первая версия Ashton-Tate/Microsoft SQL Server.
•1990 — выпущен SQL Server v1.1 с поддержкой как OS/2, так
и новой графической оболочки фирмы — Microsoft Windows
3.0.
•1991 — Microsoft получила доступ к исходному коду SQL
Server и начала работу над новой версией продукта. В мае
Microsoft и IBM объявили о завершении совместной работы
над OS/2.
•1992 — выпущена версия Oracle 7 для Unix. Эта СУБД
поддерживала распределенные запросы, удаленное
администрирование, осуществляла поддержку различных
сетевых протоколов. В этом же году был выпущен SQL Server
4.2 — 16-разрядная СУБД, результат совместной работы
Microsoft и Sybase. В этой СУБД были реализованы
клиентские библиотеки для MS-DOS, Windows и OS/2, помимо
этого в нее впервые были включены средства
администрирования с графическим интерфейсом под
управлением Windows. Компания Microsoft приняла решение
сосредоточиться на развитии версий SQL Server только для
Windows NT и остановить развитие версий для Unix. В октябре
была выпущена бета-версия SQL Server для Windows NT.
•1994 — выпущена версия Oracle 7 для IBM PC (до этого
времени компания Oracle не рассматривала данную платформу
как серверную, ограничиваясь лишь созданием для нее
клиентских частей своих СУБД). В этом же году году
закончилось сотрудничество Microsoft и Sybase, и далее эти две
компании стали разрабатывать свои серверные СУБД
независимо друг от друга. В конце года был выпущен сервер
Sybase SQL Server System 10.
•1995 — в начале года выпущен Microsoft SQL Server 6.0. Oracle
объявила о выпуске Discoverer2000 — набора средств для
анализа корпоративных данных.
•1996 — выпущен SQL Server 6.5, обладавший встроенной
поддержкой Web-приложений, средствами распределенного
администрирования, наличием динамических блокировок.
Oracle выпустила Oracle 7.3 Universal Server.
•1997 — выпущена версия Oracle 8, основными особенностями
которой стали более высокая надежность по сравнению с
предыдущей версией, а также поддержка большего числа
пользователей и больших объемов данных. Выпущены версии
Oracle Designer/2000 2.1 и Oracle Developer/2000, которые
существенно облегчили создание приложений, работающих с
базами данных, а также Oracle Applications for theWeb.
•1998 — выпущен Microsoft SQL Server 7.0 с радикально
измененной архитектурой. Это была первая версия SQL
Server, не содержавшая унаследованного кода, оставшегося со
времен сотрудничества с Sybase. Особо стоит отметить
появление в этой версии OLAP-служб в составе продукта (до
этого серверные OLAP-средства, производимые
поставщиками серверных СУБД, включая и Oracle,
продавались исключительно как отдельные продукты и
относились к категории весьма дорогостоящего
программного обеспечения).
•1999 — выпущена версия Oracle8i (Oracle 8.1), которая во
многом была основана на применении Java: виртуальная Javaмашина находится в самой СУБД, на этом же языке написаны
клиентские утилиты, инсталлятор, средства
администрирования. В конце того же года выпущена вторая
версия Oracle 8i (Oracle 8.1.6), поддерживающая XML и
содержащая некоторые новшества, связанные с созданием
хранилищ данных.
•2000 — выпущена третья версия Oracle8i (Oracle 8.1.7),
содержащая Java Virtual Machine Accelerator, а также Internet
File System. В этом же году выпущен Microsoft SQL Server
2000, поддерживающий Web-приложения, XML, а также
содержащий множество нововведений в административных
утилитах.
Сравнение особенностей
MS SQL
Oracle
Поддержка различных
платформ
Настольные и
однопользовательские
версии – Personal
Oracle, Oracle Lite (не
поддерживает
PL/SQL)
Поддержка только
платформы NT
Настольные и
однопользовательские
версии – Microsoft
Data Engine
Сравнение особенностей
MS SQL
Oracle
Полная стоимость
владения обычно
выше
Инструменты и
утилиты
производились
третьими фирмами,
но теперь ситуация
изменилась
Полная стоимость
владения обычно
ниже
Простота
администрирования –
Enterprise Manager
Сравнение особенностей
Oracle
Производительность –
входит в 5ку по данным
Transaction Processing
Performance Council
(TPC;http://www.tpc.org/)
Поддержка Java и XML,
встроенный язык для
написания скриптов
PL/SQL
MS SQL
Производительность –
лидер по данным
Transaction Processing
Performance Council
(TPC;http://www.tpc.org/)
Поддержка XML,
встроенный язык для
написания скриптов –
Transact SQL (T-SQL)
Возможности MS SQL 2000
SQL Server 2000
позволяет использовать
на одном компьютере
несколько одновременно
работающих серверов.
Помимо этого можно
использовать несколько
SQL Server 2000 и один
SQL Server 7.0.
В версии SQL Server
2000 появилась
поддержка
пользовательских
функций, которые
можно создавать
средствами языка
Transact SQL. Помимо
скалярных значений
такие функции могут
возвращать и таблицы.
Возможности MS SQL 2000
В рамках поддержки
ссылочной
целостности, в SQL
Server 2000
реализованы
каскадные удаления
и обновления
(CASCADE
DELETE, CASCADE
UPDATE).
Добавлена
поддержка языка
XML, включая
ключевое слово FOR
XML для извлечения
данных в виде XMLпотоков
Возможности MS SQL 2000
Для повышения
производительности
теперь можно
создавать индексы
для представлений
(Indexed Views).
Возможность
обновлять данные
через представления
(Updatable Views)
Перемещение
протоколов
транзакций с одного
сервера на другой в
целях полной
поддержки
отсоединенных баз
данных (standby
databases).
Возможности MS SQL 2000
Репликация
Служба
трансформации
данных DTS
В Microsoft SQL Server 7
были впервые включены
средства анализа данных
(OLAP) в качестве
компонента сервера. В
SQL Server 2000 эти
средства получили
дальнейшее развитие:
компонент Analysis
Services включает как
средства анализа
данных, так и средства
поиска закономерностей
(Data Mining).
Архитектура сервера
SQL Server использует для хранения баз
данных набор файлов операционной системы,
при этом для каждой из них создается
собственный файл.
Первичный файл данных (Primary data file)
Этот файл — отправная точка базы данных.
Всякая база данных имеет только один
первичный файл данных. Рекомендуемое
расширение — .mdf.
Архитектура сервера
Вторичные файлы данных (Secondary
data files) Эти файлы являются
необязательными и могут хранить все
данные и объекты, не вошедшие в
первичный файл данных. Некоторые
базы данных могут вообще не иметь
вторичных файлов данных, а другие
иметь множество таких файлов.
Рекомендуемое расширение — .ndf.
Архитектура сервера
Файлы журнала (Log files) В этих файлах
фиксируется вся информация о
транзакциях, которая используется для
восстановления базы данных. Каждая
база данных имеет, по крайней мере,
один файл журнала. Рекомендуемое
расширение — .ldf.
Архитектура сервера
При создании базы данных все входящие в ее
состав файлы "обнуляются" (заполняются
нулями), чтобы стереть все данные, которые
остались на диске от ранее удаленных файлов.
Хотя это приводит к увеличению
продолжительности создания БД, это избавляет
Windows NT от необходимости очистки файлов
при записи данных в них (поскольку они уже
"обнулены") во время нормальной работы с
базой данных, что повышает
производительность системы.
Архитектура сервера
Данные таблиц хранятся в наборе страниц
данных. Каждая страница имеет заголовок,
который содержит такую системную
информацию, как идентификатор владеющей
данной страницей таблицы и указатели на
следующую и предыдущую страницы в
связанном списке. В конце страницы
расположена таблица смещений строк;
остальное пространство страницы занято
строками данных.
Типы данных
Bigint
Binary
Bit
Char
Cursor
Datetime
Decimal
Float
Image
•Intmoney
•Nchar
•Ntext
•Nvarchar
•Real
•Smalldatetime
•Smallint
•Smallmoney
•Text
•Timestamp
•Tinyint
•Varbinary
•Varchar
•uniqueidentifier
Авторизация
MS SQL поддерживает windows-авторизацию
(пользователь авторизуется контроллером домена) и
sql-авторизацию (пользователь заводится на sqlсервере) Имеется встроенный пользователь sa – System
Administrator
При любом типе авторизации пользователю могут
быть розданы права как на всю базу так и на отдельные
объекты базы. Пользователь-dbowner базы имеет право
делать с базой все что угодно. Более подробно вопросы
авторизации и безопасности будут рассмотрены
позднее
Клиенты
Query Analyzer – для запуска запросов
Enterprise Manager – набор wizard’ов для
управления базой
Таким образом создание таблиц и заполнение их данными
может производиться как через графический интерфейс, так и
скриптом. Инструкции DDL – data defining language для
создания таблицы не имеют принципиальных отличий от
инструкций Oracle
Синтаксис Create Table
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }
| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
Пример создания таблицы
CREATE TABLE [T] (
[T_ID] [int] IDENTITY (1, 1) NOT NULL ,
[T_Str] [varchar] (50),
[T_X] [int] NOT NULL CONSTRAINT [DF_T_T_X] DEFAULT (0),
[T_Y] [int] NOT NULL CONSTRAINT [DF_T_T_Y] DEFAULT (1),
[T_Z] AS ([T_X] + [T_Y]) ,
CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
[T_ID]
) ON [PRIMARY]
CONSTRAINT [CK_T] CHECK NOT FOR REPLICATION ([T_Y] >= 1 and
[T_Y] <= 10000)
)
Некоторые пояснения
•IDENTITY – auto increment
•COLLATE – кодировка для текстовых полей
•T_Z – поле значение которого вычисляется из значения полей
T_X и T_Y и не может быть установлено в явном виде
•DEFAULT – это значение примет поле, если при добавлении
записи значение поля не было установлено
•CONSTRAINT [CK_T] CHECK NOT FOR REPLICATION
([T_Y] >= 1 and [T_Y] <= 10000) – проверяет принадлежность
значений поля диапазону
чтобы не прописывать при создании таблиц
связи можно использовать диаграммы
В свойствах связи в диаграмме указывается каким образом
поступать с записями зависимой таблицы при обновлении
данных основной таблицы (например каскадно удалять).
Скрипт для создания таблицы T1 с указанной связью будет
выглядеть следующим образом:
CREATE TABLE [T1] (
[T1_ID] [int] IDENTITY (1, 1) NOT NULL ,
[T_ID] [int] NOT NULL ,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[T1_ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_T1_T] FOREIGN KEY
(
[T_ID]
) REFERENCES [T] (
[T_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
Лекция №6 SQL(часть1)
SQL (Structured Query Language).
SQL относится к классу непроцедурных
языков программирования, SQL ориентирован на
множества, так как в качестве исходной информации
используется множество кортежей записей одной или
нескольких таблиц-отношений. Результатом любой
операции SQL также является таблица –отношение.
Существуют и используются две формы языка
SQL: интерактивный SQL и встроенный SQL.
Интерактивный SQL используется для задания
SQL – запросов пользователем и получения
результата в интерактивном режиме.
Встроенный SQL состоит из команд, которые
встраиваются в программы на
других языках
(Паскаль, С, С++, JAVA и др.). Это делает программы
более мощными и эффективными. При этом
используются дополнительные средства интерфейса
SQL с выбранным языком программирования.
SQL подразделяется на DDL (язык
определения данных) и DML (язык
обработки данных).
В языке SQL имеются средства для
указания типов данных , которым
соответствуют отдельные атрибуты.
Определение типов данных является
той частью, в которой коммерческие
реализации языка не согласуются с
требованиями официального стандарта.
numeric
exact numeric
integer
bigint Целые от –2^63 до 2^63-1
int Целые от –2^31 до 2^31-1
smallint Целые от –2^15 до 2^15-1
tinyint Целые от 0 до 255
bit Целые 0 или 1
decimal and numeric
decimal Десятичные числа с фиксированным
numeric количеством знаков до и после запятой от –
10^38+1 до 10^38-1
money and smallmoney
money Числа в денежном формате от –2^63 до 2^63-1,
точность 0,0001 от денежной единицы
smallmoney Числа в денежном формате от
–214748,3648 до 214748,3647, точность
0,0001 от денежной единицы
approximate numeric
float Числа с плавающей точкой от –1.79Е+308 до
1.79Е+308
real Числа с плавающей точкой от –3.40Е+38 до
3.40Е+38
datetime and smalldatetime
datetime Значения даты и времени начиная с
1.01.1753 до31.12.9999
smalldatetime Значения даты и времени начиная с
1.01.1900 до 6.06.2079
character string
char Символьные значения(не Unicode)
фиксированной длины максимум 8000
символов
varchar Символьные значения(не Unicode)
переменной длины максимум 8000 символов
text Данные (не Unicode)
переменной длины
максимум до 2147483647 или
2^31 символов
Неопределенные или пропущенные данные (NULL).
Для обозначения неопределенных, пропущенных,
или неизвестных значений SQL использует слово
NULL. Строго говоря NULL не является значением в
обычном понимании. Поэтому необходимо
учитывать эту особенность при использовании
значений атрибутов, которые могут находиться в
состоянии NULL:
•В агрегирующих функциях, позволяющих получать
сводную информацию по множеству значений
атрибута(суммарное или среднее).
•Условные операторы от булевой двузначной логики
TRUE/FALSE расширяются до трехзначной логики
TRUE/FALSE/UNKNOWN.
•Все операторы (за исключением конкатенации)
возвращают состояние NULL, если один из
операндов NULL.
•Для проверки на наличие NULL используются
специальные операторы IS NULL (IS NOT
NULL). Знак = использовать нельзя!
•Функции преобразования типов при аргументе
NULL возвращают NULL.
Используемые термины и обозначения.
Ключевые слова – зарезервированные в SQL
слова.
Команды или предложения- это инструкции, с
помощью которых SQL обращается к БД.
Объекты- имена таблиц, атрибутов,индексов и
т.п.
В описании синтаксиса [ ] указывают на не
обязательный параметр, знак … означает
повторение ранее указанного выражения, {}
объединяют последовательность элементов в
логическую группу, один из элементов которой
должен быть использован. Вертикальна черта |
указывает, что часть, следующая за этим
символом, является одним из возможных
вариантов. Угловые скобки < > , заключают
элементы объясняемые по ходу.
База данных «Колледж»
STUDENTS
SNUM
SFAM
SNAME
SFATH
STIP
3412
Поляков
Анатолий
Алексеевич
25.50
3413
Старова
Любовь
Михайловна
17.00
3414
Гриценко
Владимир
Николаевич
0.00
3415
Котенко
Анатолий
Николаевич
0.00
3416
Нагорный
Евгений
Васильевич
25.50
164
PREDMET
PNUM
PNAME
TNUM
HOURS
COURS
2001
Физика
4001
34
1
2002
Химия
4002
68
1
2003
Математика
4003
65
1
2004
Философия
4005
17
2
2005
Экономика
4004
17
2
165
USP
UNUM
SNUM
PNUM
UDATE
MARK
1001
3412
2001
10.06.02
5
1002
3413
2003
10.06.02
4
1003
3414
2005
11.06.02
3
1004
3412
2003
12.06.02
4
1005
3416
2004
12.06.02
5
166
TEACHERS
TNUM
TFAM
TNAME
TFATH
TDATE
Ивановна
01.04.94
4001
Викулова Валентина
4002
Костенко
Олег
Владимирович 01.09.97
4003
Казанцев
Виталий
Владимирович 01.09.88
4004
Поздняк
Любовь
Алексеевна
01.09.88
4005
Загоруйко
Илья
Дмитриевич
01.09.99
167
Простейшие SELECT- запросы.
Оператор SELECT ( выбрать) языка SQL
является самым важным и самым часто
используемым оператором. В обобщенной
форме его синтаксис :
SELECT [DISTINCT] <список атрибутов>
FROM <список таблиц>
[ WHERE <условие выборки>]
[ORDER BY <список атрибутов>]
[GROUP BY <список атрибутов>]
[HAVING <условие>]
[UNION
<выражение
с
оператором
SELECT>];
ПРИМЕРЫ:
1) SELECT * from USP
WHERE SNUM = 3412;
unum snum pnum
1001
3412
2001
1004 3412 2003
2)SELECT
SNUM,SFAM,SNAME SNUM
udate
mark
10.06.200
2
12.06.200
5
4
2
SFAM
SNAME
FROM STUDENTS
3412
Поляков
Анатолий
WHERE STIP>0;
3413
Старова
Любовь
3416
Нагорный
Евгений
Построение запросов с условием отбора.
Наибольший интерес представляют
такие запросы, в которых выполняется
выборка данных в соответствии с
поставленными условиями. В записи
условия отбора используются логические
выражения.
Порядок
действий
регламентируется скобками, логическими
операциями и связками.
Пример 5.1
Показать
номера
студенческих
билетов, фамилии и имена тех лиц, чьи
имена начинаются с буквы «А».
SELECT SNUM, SFAM,SNAME
FROM STUDENTS
WHERE SNAME)<"Б";
SNUM
3412
3415
SFAM
Поляков
Котенко
SNAME
Анатолий
Анатолий
Пример 5.2
Показать предметы, которые изучаются на
1 курсе и на них отводится более 30 часов.
SELECT PREDMET.PNUM, PREDMET.PNAME,
PREDMET.HOURS, PREDMET.COURS
FROM PREDMET
WHERE (((PREDMET.HOURS)>30) AND
((PREDMET.COURS)=1));
PNUM
PNAME
HOURS
COURS
2001
Физика
34
1
2002
Химия
68
1
2003
Математика
65
1
В записи логических условий могут
быть использованы операторы IN,
BETWEEN, LIKE, IS NULL.
Операторы IN (равен любому из списка) и
NOT IN (не равен любому из списка)
используются для сравнения проверяемого
значения поля с заданным списком. Список
значений указывается справа от оператора и
заключается в скобки.
IN (3412; 3413; 3414; 3415; 3416)
Пример 5.3
Получить сведения о студентах, получивших
оценки только 4 и 5.
SELECT USP.SNUM, USP.UDATE, USP.MARK,
STUDENTS.SFAM
FROM USP, STUDENTS
WHERE (((USP.SNUM)=[STUDENTS].[SNUM])
AND ((USP.MARK) IN (4,5)));
SNUM
3412
3413
3412
3416
3412
UDATE
10.06.2002
10.06.2002
12.06.2002
12.06.2002
13.06.2002
MARK
5
4
4
5
4
SFAM
Поляков
Старова
Поляков
Нагорный
Поляков
Пример 5.4
Получить сведения о студентах, не получивших
оценок 4 и 5.
SELECT USP.SNUM, STUDENTS.SFAM,
USP.UDATE, USP.MARK
FROM USP, STUDENTS
WHERE (((USP.SNUM)=[STUDENTS].[SNUM])
AND ((USP.MARK) NOT IN (4,5)));
SNUM
SFAM
UDATE
MARK
3414
Гриценко
11.06.2002
3
3414
Гриценко
12.06.2002
2
Оператор BETWEEN используется для
проверки условия вхождения значения поля в
заданный интервал, т.е. задаются вместо списка
границы. BETWEEN 20 AND 30 . Типы полей как
числовые, так и символьные.
Пример 5.5 Показать список тех, кто получает
стипендию в указанном диапазоне.
SELECT STUDENTS.SNUM, STUDENTS.SFAM,
STUDENTS.STIP
FROM STUDENTS
WHERE (((STUDENTS.STIP) BETWEEN 20 AND
30));
SNUM
3412
3416
SFAM
Поляков
Нагорный
STIP
25,50
25,50
Оператор LIKE применим только
символьным полям типа CHAR или
VARCHAR. Этот оператор просматривает
строковые значения полей и определяет
входит ли образец поиска в символьную
строку-значение поля. В образце может
использоваться шаблон:
•Символ подчеркивания «_» определяет
наличие 1 любого символа.
•Символ % или * допускает наличие любых
символов произвольной длины.
Пример 5.7
Показать списки студентов с отчеством на
«Ни*».
SELECT STUDENTS.SNUM,
STUDENTS.SFAM, STUDENTS.SNAME,
STUDENTS.SFATH
FROM STUDENTS
WHERE (((STUDENTS.SFATH) LIKE "Ни*"));
SNUM
3414
3415
SFAM
SNAME
SFATH
Гриценко Владимир Николаевич
Котенко Анатолий Николаевич
Если внутри образца содержатся знаки
_ | % | *|, то применяют escape – символы.
Например, в выражении LIKE “_%_”
ESCAPE “%” знак % будет восприниматься
не как управляющий символ, а как процент.
Все рассмотренные ранее операторы
нельзя рассматривать для работы с NULL .
Для этого используют IS NULL (является
пустым) или IS NOT NULL (не является
пустым).
Преобразование вывода и встроенные
функции.
Несмотря на то, что SQL работает с данными
в понятиях таблиц (столбцов, строк), имеется
возможность применения выражений,
построенных с использованием встроенных
функций, констант, имен столбцов,
определяемых как своего рода виртуальные
столбцы. Они помещаются в списке столбцов
и могут сопровождаться псевдонимами.
Пример 5.8
Составить список изучаемых предметов.
SELECT PREDMET.PNUM as код,
PREDMET.PNAME as название,
PREDMET.HOURS as количество_часов
FROM PREDMET;
код
2001
2002
2003
2004
2005
название
Физика
Химия
Математика
Философия
Экономика
количество_часов
34
68
65
17
17
Пример. Создать таблицу
STUDENTS.
• CREATE TABLE STUDENTS
(SNUM INTEGER,
SFAM CHAR (20),
SNAME CHAR (15), SFATH CHAR (15),
STIP DECIMAL)
1. В этой команде порядок полей
определяется их местом в списке.
2. После того, как таблица создана, её
можно изменить.
Добавление новых полей выполняется командой:
ALTER TABLE <name of table> ADD <Name
of attribution1> <type of attribution1> [(<size
of attribution1>)], …
<Name of attribution n> <type of attribution n>
[(<size of attribution n>)]);
Добавляемые поля автоматически получают
значения NULL, если не указываются иные
значения
по
умолчанию
[DEFAULT
constant_expression ] .
Пример. Предположим мы
решили добавить номер курса
и специальность.
ALTER TABLE STUDENTS
ADD
COURS INTEGER,
SPEC CHAR (20);
• Наиболее частые изменения связаны с
изменением
размера
поля.
Необходимо
убедиться, что изменения не приведут к потере
информации, поэтому необходимо лучше
проектировать таблицу и вносить изменения
только в крайнем случае.
ALTER TABLE <name of table> MODIFY <Name of
attribution1>
<type of attribution1> [(<size of
attribution1>)], …
<Name of attribution n> <type of attribution n> [(<size of
attribution n>)]);
• Изменения возможны ,если столбец пуст;
• Для
заполненного
столбца
изменяется
размер/точность в сторону увеличения;
• Установка ограничения NOT NULL возможна при
отсутствии значений NULL;
• Разрешается изменять значения устанавливаемые
по умолчанию.
Для удаления таблицы необходимо:
1) быть ее создателем или иметь
на это право;
2) перед удалением необходимо ее
очистить от данных, это позволяет
избежать случайной потери информации.
• DROP TABLE <name of table>;
Пример. Удалить все сведения и таблицу
PREDMET.
1. DELETE FROM PREDMET;
2. DROP TABLE PREDMET;
INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [
...n ] )
| view_name
| rowset_function_limited
}
{
}
[ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
Заполнение таблицы STUDENTS:
INSERT INTO STUDENTS (SNUM, SFAM, SNAME,
SFATH, STIP) VALUES
(3412, ‘Поляков’, ‘Анатолий’,’Алексеевич’,25.50);
go
INSERT INTO STUDENTS (SNUM, SFAM, SNAME,
SFATH, STIP) VALUES
(3413,’Старова’,’Любовь’,’Михайловна’, 17.00);
go
Простейшие SELECT- запросы.
Оператор SELECT ( выбрать) языка SQL
является самым важным и самым часто
используемым оператором. В обобщенной
форме его синтаксис :
SELECT [DISTINCT] <список атрибутов>
FROM <список таблиц>
[ WHERE <условие выборки>]
[ORDER BY <список атрибутов>]
[GROUP BY <список атрибутов>]
[HAVING <условие>]
[UNION
<выражение
с
оператором
SELECT>];
Построение запросов с условием отбора.
Наибольший интерес представляют
такие запросы, в которых выполняется
выборка данных в соответствии с
поставленными условиями. В записи
условия отбора используются логические
выражения.
Порядок
действий
регламентируется скобками, логическими
операциями и связками.
ПРИМЕРЫ:
1) SELECT * from USP
WHERE SNUM = 3412;
unum snum pnum
1001
1004
2)SELECT
SNUM,SFAM,SNAME
3412
2001
3412
2003
udate
mark
10.06.200
2
12.06.200
5
2
SNUM
SFAM
SNAME
FROM STUDENTS
3412
Поляков
Анатолий
WHERE STIP>0;
3413
Старова
Любовь
3416
Нагорный
Евгений
4
Показать
номера
студенческих
билетов, фамилии и имена тех лиц, чьи
имена начинаются с буквы «А».
SELECT SNUM, SFAM,SNAME
FROM STUDENTS
WHERE SNAME)<"Б";
SNUM
3412
3415
SFAM
Поляков
Котенко
SNAME
Анатолий
Анатолий
Пример 5.2
Показать предметы, которые изучаются на
1 курсе и на них отводится более 30 часов.
SELECT PREDMET.PNUM, PREDMET.PNAME,
PREDMET.HOURS, PREDMET.COURS
FROM PREDMET
WHERE (((PREDMET.HOURS)>30) AND
((PREDMET.COURS)=1));
PNUM
PNAME
HOURS
COURS
2001
Физика
34
1
2002
Химия
68
1
2003
Математика
65
1
В записи логических условий могут
быть использованы операторы IN,
BETWEEN, LIKE, IS NULL.
Операторы IN (равен любому из списка) и
NOT IN (не равен любому из списка)
используются для сравнения проверяемого
значения поля с заданным списком. Список
значений указывается справа от оператора и
заключается в скобки.
IN (3412, 3413, 3414, 3415, 3416)
Получить сведения о студентах, получивших
оценки только 4 и 5.
SELECT USP.SNUM, USP.UDATE, USP.MARK,
STUDENTS.SFAM
FROM USP, STUDENTS
WHERE (((USP.SNUM)=[STUDENTS].[SNUM])
AND ((USP.MARK) IN (4,5)));
SNUM
3412
3413
3412
3416
3412
UDATE
10.06.2002
10.06.2002
12.06.2002
12.06.2002
13.06.2002
MARK
5
4
4
5
4
SFAM
Поляков
Старова
Поляков
Нагорный
Поляков
Пример 5.4
Получить сведения о студентах, не получивших
оценок 4 и 5.
SELECT USP.SNUM, STUDENTS.SFAM,
USP.UDATE, USP.MARK
FROM USP, STUDENTS
WHERE (((USP.SNUM)=[STUDENTS].[SNUM])
AND ((USP.MARK) NOT IN (4,5)));
SNUM
SFAM
UDATE
MARK
3414
Гриценко
11.06.2002
3
3414
Гриценко
12.06.2002
2
Оператор BETWEEN используется для
проверки условия вхождения значения поля в
заданный интервал, т.е. задаются вместо списка
границы. BETWEEN 20 AND 30 . Типы полей как
числовые, так и символьные.
Пример 5.5 Показать список тех, кто получает
стипендию в указанном диапазоне.
SELECT STUDENTS.SNUM, STUDENTS.SFAM,
STUDENTS.STIP
FROM STUDENTS
WHERE (((STUDENTS.STIP) BETWEEN 20 AND
30));
SNUM
3412
3416
SFAM
Поляков
Нагорный
STIP
25,50
25,50
Оператор LIKE применим только
символьным полям типа CHAR или
VARCHAR. Этот оператор просматривает
строковые значения полей и определяет
входит ли образец поиска в символьную
строку-значение поля. В образце может
использоваться шаблон:
•Символ подчеркивания «_» определяет
наличие 1 любого символа.
•Символ % или * допускает наличие любых
символов произвольной длины.
Пример 5.7
Показать списки студентов с отчеством на
«Ни*».
SELECT STUDENTS.SNUM,
STUDENTS.SFAM, STUDENTS.SNAME,
STUDENTS.SFATH
FROM STUDENTS
WHERE (((STUDENTS.SFATH) LIKE "Ни*"));
SNUM
3414
3415
SFAM
SNAME
SFATH
Гриценко Владимир Николаевич
Котенко Анатолий Николаевич
Если внутри образца содержатся знаки
_ | % | *|, то применяют escape – символы.
Например, в выражении
LIKE “_%_” ESCAPE “%” знак % будет
восприниматься не как управляющий
символ, а как процент.
Все рассмотренные ранее операторы
нельзя рассматривать для работы с NULL .
Для этого используют IS NULL (является
пустым) или IS NOT NULL (не является
пустым).
Преобразование вывода и встроенные
функции.
Несмотря на то, что SQL работает с данными
в понятиях таблиц (столбцов, строк), имеется
возможность применения выражений,
построенных с использованием встроенных
функций, констант, имен столбцов,
определяемых как своего рода виртуальные
столбцы. Они помещаются в списке столбцов
и могут сопровождаться псевдонимами.
Пример 5.8
Составить список изучаемых предметов.
SELECT PREDMET.PNUM as код,
PREDMET.PNAME as название,
PREDMET.HOURS as количество_часов
FROM PREDMET;
код
2001
2002
2003
2004
2005
название
Физика
Химия
Математика
Философия
Экономика
количество_часов
34
68
65
17
17
Лекция №7 SQL(часть2)
Основы SQL
Использование выражений :
•
унарный оператор « - » (знак минус) меняет
знак выражения на противоположный;
•
бинарные операторы « + », « - », « * », « / »
предоставляют возможность выполнения
арифметических действий;
• операция конкатенации строк + (||)
позволяет «склеивать» значения двух и более
строк.
Пример 4.1
1) Увеличить размер стипендии «учащимся без
троек»(оператор *)
SELECT DISTINCT STUDENTS.SNUM, STUDENTS.SFAM,
STUDENTS.STIP*1.25 AS STIP
FROM STUDENTS, USP
WHERE (((STUDENTS.SNUM)=[USP].[SNUM]) AND
((USP.MARK)>3));
SNUM
3412
3413
3416
SFAM
Поляков
Старова
Нагорный
STIP
31,875
21,25
31,875
2) Установить тариф за повторную аттестацию
«учащимся с двойками» (унарный минус, *)
SELECT STUDENTS.SNUM AS НОМЕР,
STUDENTS.SFAM AS ФАМИЛИЯ, -(25.5*0.1) AS
ТАРИФ
FROM STUDENTS, USP
WHERE (((STUDENTS.SNUM)=[USP].[SNUM]) AND
((USP.MARK)<3));
НОМЕР
ФАМИЛИЯ
ТАРИФ
3414
Гриценко
-2,55
3) Подготовить объединенный список
преподавателей(конкатенация).
SELECT TEACHERS.TNUM AS КОД,
TEACHERS.TFAM +" "+ TEACHERS.TNAME
+" "+ TEACHERS.TFATH AS СПИСОК,
TEACHERS.TDATE AS ПРИЕМ_НА_РАБОТУ
FROM TEACHERS;
КОД
4001
4002
4003
4004
4005
4006
СПИСОК
Викулова Валентина Ивановна
Костенко Олег Владимирович
Казанцев Виталий
Владимирович
Поздняк
Любовь Алексеевна
Загоруйко Илья Дмитриевич
Федченко Светлана
Геннадьевна
ПРИЕМ_НА_РАБОТУ
01.04.1994
01.09.1997
01.09.1988
01.09.1988
10.05.1999
10.06.2001
Функции преобразования символов в строке:
•LOWER <строка> – перевод в строчные
символы(нижний регистр)
•UPPER <строка> – перевод в прописные
символы(верхний регистр)
•INITCAP <строка> – перевод первой буквы
каждого слова в прописную(верхний регистр)
•LPAD( <строка>,<длина>[,<подстрока>]) –
дополнение строки слева элементами подстроки,
по умолчанию пробелами; если <длина> меньше
длины <строки>, то исходная строка усекается
слева до заданной длины.
•RPAD ( <строка>, <длина> [,<подстрока>]) –
дополнение строки справа элементами подстроки,
по умолчанию пробелами; если <длина> меньше
длины <строки>, то исходная строка усекается
справа до заданной длины.
•LTRIM ( <строка [,<подстрока>]) удаление
левых граничных символов
•RTRIM ( <строка> [,<подстрока>])
удаление правых граничных символов
•SUBSTR ( <строка>, <начало>
[,<количество>]) выделение подстроки
•INSTR ( <строка>, < подстрока > [,<начало
поиска>]) поиск подстроки
•LENGH ( <строка>) длина строки
ЧИСЛОВЫЕ ФУНКЦИИ:
•ABS – абсолютное значение
•FLOOR –урезанное целое
•CELL-самое малое целое >=заданного
•ROUND - округленное
•TRUNC - усеченное с точностью
•COS, SIN, TAN - тригонометрические
•COSH, SINH, TANH -гиперболические
•EXP, LOG – экспонента, логарифм
•POWER, SQRT – степень, корень
•SIGN- знак.
Агрегирование и групповые функции
Агрегирующие
функции
позволяют
получать
из
таблицы
сводную
(агрегированную) информацию, выполняя
операции над группой строк таблицы.
Для
задания
в
SELECT-запросе
агрегирующих
операций
используются
следующие ключевые слова:
•COUNT определяет количество строк или
значений поля, выбранных посредством
запроса включая NULL-значения;
Для подсчета общего количества строк в таблице
следует использовать функцию COUNT .
COUNT ( { [ ALL | DISTINCT ] expression ] | * } )
SELECT COUNT(*) FROM USP;
Аргументы
DISTINCT
и
ALL позволяют,
соответственно, исключать и включать дубликаты
обрабатываемых функцией COUNT значений, ALL
работает по умолчанию .
SELECT COUNT(DISTINCT SNUM)
FROM USP;
expression1
5
•SUM вычисляет арифметическую
сумму всех выбранных значений
данного поля, игнорируя NULL
значения;
•AVG вычисляет среднее значение для
всех выбранных значений данного
поля, игнорируя NULL значения;
•МАХ вычисляет наибольшее из всех
выбранных значений поля;
•MIN вычисляет наименьшее из всех
выбранных значений поля.
•STDEV
возвращает
стандартное
статистическое отклонение для каждого
из выбранного набора;
•STDEVP
возвращает
стандартное
статистическое
отклонение
всей
совокупности значений набора;
•VAR
возвращает
статистическую
вариацию значений в группе;
•VARP
возвращает
статистическую
вариацию значений всего набора.
Пример 4.2
Определить среднее значение поля MARK
(оценки) по всем записям таблицы USP. Для
получения
нужных
результатов
можно
использовать запрос с функцией AVG
следующего вида:
SELECT AVG (USP.MARK) AS Avg-MARK
FROM USP;
Avg-MARK
3,85714285714286
Предложение GROUP BY (группировать по)
позволяет группировать записи в подмножества,
определяемые значениями какого-либо поля, и
применять агрегирующие функции уже не ко
всем записям таблицы, а раздельно к каждой
сформированной группе.
Пример 2.
Предположим, требуется найти максимальное
значение оценки, полученной каждым
студентом.
Выбираемые из таблицы USP записи
группируются по значениям поля SNUM,
указанного в предложении GROUP BY, и для
каждой группы находится максимальное
значение поля MARK.
В приведенном запросе рассматриваются
группы
записей,
объединенные
по
идентификаторам студентов.
Запрос будет выглядеть следующим образом:
SELECT SNUM, MAX(MARK) AS
MAX_MARK FROM USP GROUP BY SNUM;
SNUM
3412
3413
3414
3416
MAX_MARK
5
4
3
5
Предложение
GROUP
BY
позволяет
применять агрегирующие функции к каждой
группе, определяемой общим значением поля
(или полей), указанных в этом предложении.
Пример 4.3.
По итогам аттестации необходимо найти
минимальную оценку по каждому предмету.
SELECT USP.PNUM, MIN(USP.MARK) AS
[Min-MARK]
FROM USP
GROUP BY USP.PNUM ;
PNUM
2001
2003
2004
2005
Min-MARK
2
4
5
3
Применение COUNT в группе
SELECT usp.snum, Count([mark]) AS Выражение1
FROM usp GROUP BY usp.snum
snum
3412
3413
3414
3416
Выражение
1
2
1
1
1
SELECT usp.snum, Count([mark]) AS Выражение1
FROM usp GROUP BY usp.snum HAVING Count([mark])>1;
Выражение
snum
1
3412
2
•Применение SUM в группе:
SELECT usp.snum,sum([mark]) AS Выражение1
FROM usp
GROUP BY usp.snum;
snum
3412
3413
3414
3416
Выражение1
9
4
3
5
SELECT usp.snum,sum([mark]) AS Выражение1
FROM usp GROUP BY usp.snum HAVING sum([mark])> 4;
snum
Выражение1
3412
9
3416
5
В
конструкции
GROUP
BY
для
группирования может быть использовано
более одного столбца.
Пример 4.4:
SELECT SNUM, PNUM, MAX(MARK)
FROM USP
GROUP BY SNUM, PNUM;
SNUM
3412
3412
3412
3413
3414
3414
3416
PNUM
2001
2003
2005
2003
2001
2005
2004
MAX_MARK
5
4
4
4
2
3
5
В этом случае строки вначале группируются
по значениям первого столбца, а внутри этих
групп — в подгруппы по значениям второго
столбца. Таким образом, GROUP BY не
только устанавливает столбцы, по которым
осуществляется
группирование,
но
и
указывает порядок разбиения столбцов на
группы.
Следует иметь в виду, что в предложении
GROUP BY должны быть указаны все
выбираемые столбцы, приведенные после
ключевого слова SELECT, кроме столбцов,
указанных
в
качестве
аргумента
в
агрегирующей функции.
При необходимости часть сформированных с
помощью GROUP BY групп может быть
исключена с помощью предложения HAVING.
Предложение HAVING определяет критерий, по
которому группы следует включать в выходные
данные, по аналогии с предложением WHERE,
которое осуществляет это для отдельных строк.
В условии, задаваемом предложением HAVING,
указывают только поля или выражения, которые
на выходе имеют единственное значение для
каждой выводимой группы.
Пример 4.5.
Показать наименования предметов с количеством
учебных часов более либо равным 30.
SELECT PNAME, MAX(HOURS) AS _HOURS_
FROM PREDMET
GROUP BY PNAME
HAVING MAX( HOURS) >= 30;
PNAME
Математика
Физика
Химия
_HOURS_
65
34
68
Влияние NULL-значений в функции
AVG.
Среднее значение множества чисел
равно сумме чисел, деленной на число
элементов
множества.
Однако
если
некоторые элементы пусты (то есть их
значения неизвестны или не существуют),
деление на количество всех элементов
множества приведет к неправильному
результату.
Функция AVG вычисляет среднее
значение всех известных значений множества
элементов, то есть эта функция подсчитывает
сумму известных значений и делит ее на
количество этих значений, а не на общее
количество значений, среди которых могут
быть NULL-значения. Если столбец состоит
только из пустых значений, то функция AVG
также возвратит NULL.
Условные операторы при отсутствии
пустых значений возвращают либо true
(истина), либо false (ложь).
Если же в
столбце присутствуют пустые значения, то
может быть возвращено и третье значение:
unknown (неизвестно). В этой схеме,
например, условие where А = 2, где А — имя
столбца, значения которого могут быть
неизвестны, при А = 2 будет соответствовать
true, при А = 4 в результате будет получено
значение false, а при отсутствующем
значении А (NULL-значение) результат будет
unknown. Пустые значения оказывают
влияние на использование логических
операторов NOT, AND и OR..
Оператор NOT.
Обычный унарный оператор NOT обращает
оценку TRUE в FALSE и наоборот. Однако
NOT null по прежнему будет возвращать пустое
значение NULL. При этом следует отличать
случай NOT null от условия is NOT NULL,
которое является противоположностью IS
NULL, отделяя известные значения от
неизвестных.
Оператор AND.
• Если результат двух условий, объединенных
оператором AND, известен, то применяются
правила булевой логики, то есть при обоих
утверждениях true составное утверждение
также будет TRUE. Если же хотя бы одно из
двух утверждений будет false, то составное
утверждение будет false.
• Если результат одного из утверждений
неизвестен NULL, а другой оценивается
как TRUE, то состояние неизвестного
утверждения является определяющим, и,
следовательно, итоговый результат также
неизвестен NULL.
• Если результат одного из утверждений
неизвестен, а другой оценивается как
false, итоговый результат будет false.
• Если результат обоих утверждений
неизвестен, то результат также остается
неизвестным NULL.
Оператор OR.
• Если результат двух условий, объединенных
оператором OR, известен, то применяются
правила булевой логики, а именно:
1. если хотя бы одно из двух утверждений
соответствует
true,
то
и
составное
утверждение будет true,
2. если оба утверждения оцениваются как false,
то,составное утверждение будет false.
• Если результат одного из утверждений
неизвестен, а другой оценивается как true,
итоговый результат будет true.
• Если результат одного из утверждений
неизвестен, а другой оценивается как false,
то состояние неизвестного утверждения
имеет
определяющее
значение.
Следовательно, итоговый результат также
неизвестен NULL.
• Если результат обоих утверждений
неизвестен, то результат также остается
неизвестным NULL.
Отсутствующие
(NULL)
значения
целесообразно использовать в столбцах,
предназначенных для агрегирования,
чтобы извлечь преимущества из способа
обработки пустых значений в функциях
COUNT и AVG.
Практически
во всех остальных
случаях пустых значений следует избегать,
так как при их наличии существенно
усложняется
корректное
построение
условий отбора, приводя иногда к
непредсказуемым результатам выборки.
Для индикации же отсутствующих,
неприменимых или по какой-то причине
неизвестных данных можно использовать
значения по умолчанию, устанавливаемые
заранее (например, с помощью команды
create table .)
.
Упорядочение выходных полей
(ORDER BY).
Как уже отмечалось, записи в таблицах
реляционной базы данных не упорядочены.
Однако данные, выводимые в результате
выполнения запроса, могут быть упорядочены.
Для этого используется оператор ORDER BY,
который позволяет упорядочивать выводимые
записи в соответствии со значениями одного или
нескольких выбранных столбцов. При этом
можно
задать
возрастающую
(asc)
или
убывающую
(desc)
последовательность
сортировки для каждого из столбцов. По
умолчанию
принята
возрастающая
последовательность сортировки.
Запрос, позволяющий выбрать все данные
из таблицы предметов обучения Predmet с
упорядочением
по
наименованиям
предметов, выглядит следующим образом:
SELECT * FROM PREDMET
ORDER BY PNAME;
PNUM
2003
2001
2004
2002
2005
PNAME
Математика
Физика
Философия
Химия
Экономика
TNUM
4003
4001
4005
4002
4004
HOURS
65
34
17
68
17
COURS
1
1
2
1
3
Тот же список, но упорядоченный в
обратном порядке, можно получить
запросом:
SELECT * FROM PREDMET
ORDER BY PNAME DESC;
PNUM
PNAME
TNUM HOURS COURS
2005 Экономика 4004
17
3
2002
Химия
4002
68
1
Философи
2004
4005
17
2
я
2001
Физика
4001
34
1
Математик
2003
4003
65
1
а
Можно упорядочить выводимый список
предметов
обучения
по
значениям
семестров, а внутри семестров — по
наименованиям предметов.
SELECT * FROM PREDMET
ORDER BY COURS, PNAME;
PNUM
2003
2001
2002
2004
2005
PNAME
Математика
Физика
Химия
Философия
Экономика
TNUM
4003
4001
4002
4005
4004
HOURS
65
34
68
17
17
COURS
1
1
1
2
3
Предложение order by может использоваться с group
by для упорядочения групп записей. При этом
оператор order by в запросе всегда должен быть
последним.
SELECT PNAME, COURS
FROM PREDMET
GROUP BY
PNAME,COURS
ORDER BY COURS DESC;
PNAME
Экономика
Философия
Математика
Физика
Химия
COURS
3
2
1
1
1
При упорядочении вместо наименований
столбцов можно указывать их номера, имея,
однако, в виду, что в данном случае это номера
столбцов, указанные при определении выходных
данных в запросе, а не номера столбцов в таблице.
Полем с номером 1 является первое поле,
указанное в предложении order by — независимо
от его расположения в таблице.
SELECT PNAME, COURS FROM PREDMET
GROUP BY
PNAME,COURS
ORDER BY
1 DESC;
В этом запросе выводимые записи будут
упорядочены
по
полю
COURS.
Если в поле, которое используется для
упорядочения,
существуют
NULLзначения, то все они размещаются в конце
или предшествуют всем остальным
значениям
этого
поля.
Вложенные запросы
<запрос 0>
<выражение 1> <оператор 2>
<подзапрос 3> …
Выполнение основного запроса при выполнении
подзапроса
(необратимость последовательности <1><2><3>).
Основные правила:
• Подзапрос выбирает значение одного поля, тип
которого совпадает с типом выражения;
• Подзапрос может выбрать в качестве результата
несколько значений, что может сделать
невыполнимым оценку предиката;
• Подзапрос может вообще не выбрать значений,
то основной запрос не сможет оценить предикат
(неизвестное значение рассматривается как ложное).
Пример 4.6
• Выбрать из таблицы успеваемости те
сведения, которые относятся к студенту
Полякову.
SELECT * from USP
WHERE SNUM = (SELECT SNUM
FROM STUDENTS
WHERE SFAM = ‘Поляков’);
Результат:
• Из подзапроса найденный номер SNUM
подставляется в предикат;
UNUM
SNUM
PNUM
UDATE MARK
1001
3412
2001
10/06/02 5
1004
3412
2003
12/06/02 4
Пример 4.7
Выбрать сведения об оценках студента по имени
Анатолий.
SELECT * from USP
WHERE SNUM = (SELECT SNUM
FROM STUDENTS
WHERE SNAME = ‘Анатолий’);
Результат:
• Подзапрос получит несколько результатов
3412 и 3415 ;
• Это делает невозможным оценку
предиката в основном запросе;
Выход:
Заменить оператор отношения на IN .
1
SELECT * from USP WHERE SNUM IN (SELECT
SNUM FROM STUDENTS WHERE SNAME =
‘Анатолий’);
2
UNUM
SNUM
PNUM
UDATE MARK
1001
3412
2001
10/06/02 5
1004
3412
2003
12/06/02 4
Элементы 1 и 2 НЕ перемещаются
Использование агрегатных функций
в подзапросах.
• Единственное значение – БЕЗ
группировки;
• Несколько значений с
группировкой.
Пример 4.8
• Выбрать все записи, где оценка превышает
среднюю по таблице.
• SELECT * FROM USP
• WHERE MARK > (SELECT AVG(MARK)
• FROM USP);
Результат:
• Подзапрос вычислит среднюю оценку 4.2;
• Основной запрос проверит условие MARK> 4.2
UNUM
SNUM PNUM UDATE MARK
1001
3412
2001
10/06/02 5
1005
3416
2004
12/06/02 5
Пример 4.9
• Выбрать все записи, где оценка превосходит либо
равна средней по предмету c номером 2003.
•
•
•
•
•
SELECT * FROM USP
WHERE MARK >=(SELECT AVG(MARK)
FROM USP
GROUP BY PNUM
HAVING PNUM = 2003);
Результат:
• Или с « WHERE PNUM=2003 »
UNUM SNUM PNUM UDATE MARK
1001
3412
2001
10/06/02 5
1002
3413
2003
10/06/02 4
1004
3412
2003
12/06/02 4
1005
3416
2004
12/06/02 5
• Для построения запросов с множественными
значениями в подзапросе используют
ОПЕРАТОР IN .
• Операторы BETWEEN, LIKE, IS NULL в
подзапросах не используются.
Подзапрос строит множество
значений для IN .
Пример 4.10
• Выбрать все записи из STUDENTS о
студентах, оценки которых > 0;
• SELECT * FROM STUDENTS
• WHERE SNUM IN (SELECT DISTINCT
SNUM
• FROM USP WHERE MARK>0);
Результат:
• Подзапрос выдает (3412, 3413, 3414, 3416);
• Вынимает SNUM из STUDENTS и ищет в
списке ().
Этот запрос выполняется быстрее, чем соединение.
snum
3412
3413
3414
3416
sfam
sname
sfat
Поляков
Старова
Гриценко
Нагорный
Анатолий
Любовь
Владимир
Евгений
Алексееви
ч
Михайловн
а
Николаеви
ч
Васильеви
ч
stip
25,5
0
17,0
0
0,00
25,5
0
Аналог – соединение:
• SELECT STUDENTS.SFAM,
STUDENTS.SNAME, STUDENTS.SFAT,
STUDENTS.STIP(STUDENTS.*)
• FROM STUDENTS, USP
• WHERE STUDENTS.SNUM=USP.SNUM
AND USP.MARK>0(USP.MARK IS NOT
NULL) ;
Проблемы «IN»
• В списке результатов могут оказаться
одинаковые значения, например,
одинаковый номер SNUM. Тогда основной
запрос выдаст то же число записей.
(DISTINCT)
• С WHERE вообще возникнет ошибка, так
как предикат проверяется с одним
значением.
Отказ от префиксов в подзапросах
• Предложение FROM явно определяет
таблицу;
• В подзапросах допускаются выражения
основанные на полях таблицы; например, «
snum – 1 »
Пример 4.11
• Найти сведения о предмете, имеющем
номер на 1 меньше, чем философия.
• SELECT * FROM PREDMET
• WHERE PNUM = (SELECT PNUM –1
FROM PREDMET WHERE
PNAME=‘философия’);
PNUM
PNAME
TNUM HOURS COURS
2003
Математика
4003
65
1
Использование подзапросов в
группах:
• Пример 4.12
• Определить количество оценок, больших ИЛИ
равных средней оценке по математике (2003).
• SELECT COUNT( DISTINCT SNUM) ,MARK
• FROM USP GROUP BY MARK
• HAVING MARK>=( SELECT AVG(MARK) FROM
USP WHERE PNUM=2003);
Результат:
• Средняя оценка по математике 4.
• В группе « 5 » оценок > 4 всего 2;
• В группе « 4 » оценок = 4 всего 2;
EXPRESSION
MARK
2
5
2
4
Связанные подзапросы:
Пример 4.13
• Определить предметы сданные 10.06.02.
• SELECT * FROM PREDMET A
• WHERE 10/06/02 IN (SELECT UDATE
FROM USP B WHERE A.PNUM =
B.PNUM);
Пример 4.14
• Определить студентов, сдававших что-либо
10.06.02.
• SELECT * FROM STUDENTS A
• WHERE 10/06/02 IN (SELECT UDATE
FROM USP B WHERE A.SNUM=B.SNUM);
SNUM
SFAM
SNAME
SFAT
STIP
3412
Поляков
Анатолий Алексеевич 25.50
3413
Старова
Любовь
Михайловн 17.00
Использование связанных подзапросов в
группах:
Пример 4.15
• Установить среднюю оценку на каждый
день, превосходящую min оценку за этот
день + 0.5 балла.
•
•
•
•
•
SELECT UDATE, AVG(MARK)
FROM USP A GROUP BY UDATE
HAVING AVG(MARK)>=( SELECT
MIN (MARK)+0.5 FROM USP B
WHERE A.UDATE =B.UDATE);
Результат:
•
•
•
•
Формируются группы по дате
Определить min(mark)+0.5;
Проверяется внешний запрос;
Формируется строка выходных данных.
UDATE
10/06/02
12/06/02
EXPRESSION
4.5
4.5
Обновление имеющихся записей
UPDATE
UPDATE
{
table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]
{ { [ FROM { < table_source > } [ ,...n ] ]
[ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
]}
[ OPTION ( < query_hint > [ ,...n ] ) ]
UPDATE authors SET authors.au_fname = 'Annie'
WHERE au_fname = 'Anne'
Синтаксис where:
WHERE < search_condition > - логические условия, в
соединение с AND, OR и NOT (=, <>, >, <, >=, <=, like, in)
UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
UPDATE publishers SET city = 'Atlanta', state = 'GA'
UPDATE publishers SET pub_name = NULL
UPDATE titles SET price = price * 2
Удаление
DELETE
DELETE
[ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
[ FROM { < table_source > } [ ,...n ] ]
[ WHERE
{ < search_condition >
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]}
}
]
[ OPTION ( < query_hint > [ ,...n ] ) ]
DELETE authors
DELETE FROM authors
WHERE au_lname = 'McBadden'
Для полной очистки таблицы лучше использовать truncate
TRUNCATE TABLE authors
Выборка
SELECT
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
SELECT * FROM authors
ORDER BY au_lname ASC, au_fname ASC
SELECT authors.* FROM customers ORDER BY au_lname ASC,
au_fname ASC
SELECT au_fname, au_lname, phone AS Telephone FROM
authors WHERE state = 'CA' and au_lname <> 'McBadden'
ORDER BY au_lname ASC, au_fname ASC
SELECT 1, ‘qq’
SELECT ytd_sales AS Sales,
authors.au_fname + ' '+ authors.au_lname AS Author,
ToAuthor = (ytd_sales * royalty) / 100,
ToPublisher = ytd_sales - (ytd_sales * royalty) / 100
FROM titles, titleauthor, authors
WHERE titles.title_id = titleauthor.title_id
AND titleauthor.au_id = authors.au_id
ORDER BY Sales DESC, Author ASC
Использование JOIN’ов
•Join’ы( объединение таблиц по некоторому условию/условиям )
могут содержаться во FROM или WHERE частях запроса
•Join’ы могут содержаться в SELECT, UPDATE и DELETE
запросах
•Результатом любого join’а является некая таблица
Синтаксис
SELECT | INSERT | UPDATE
select list | update list | delete list
FROM Table1 Name
INNER|LEFT OUTER|RIGHT OUTER|FULL|CROSS JOIN Table2 Name
[ON join condition]
INNER|LEFT OUTER|RIGHT OUTER|FULL|CROSS JOIN Table3 Name
[ON join condition]
…
WHERE CLAUSE
…
Типы JOIN’ов
• Inner joins (операция соотвествующая
теоретико-множественному пересечению).
Inner join использует оператор сравнения
для выбора и объединения строк из 2-х
таблиц, основываясь на условии
совпадения значений некоторых ( быть
может одной) колонок из этих таблиц.
Фактически inner join можно не делать в
явном виде, можно просто выбрать из
нужных таблиц и написать условие where
•LEFT JOIN или LEFT OUTER JOIN
В таблицу-результат включаются все строки из
левой ( первой ) таблицы указанной в
выражении LEFT OUTER, даже если в правой
таблице нет строк, содержащих значения
удовлетворяющие условию join’a ( в этом
случае в таблице результате все значения
полей из правой таблицы будут иметь значение
null).
•RIGHT JOIN или RIGHT OUTER JOIN
right outer join это left outer join с точностью до
наоборот
•FULL JOIN или FULL OUTER JOIN.
full outer join возвращает все строки из обеих таблиц. Если
строка не содержит соотвествующую ей строку из другой
таблицы, то в зависимости от того в какой таблице исходная
строка выполняется left или right outer join для этой строки,
иначе выполняется inner join. Данная операция
соответствует теоретико-множественному объединению.
•CROSS JOIN
возвращает декартово произведение строк соединяемых
таблиц
Примеры.
CREATE TABLE [Objects] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL ,
[Parent_ID] [int] NULL ,
CONSTRAINT [PK_Objects] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Objects_Objects] FOREIGN KEY
(
[Parent_ID]
) REFERENCES [Objects] (
[ID]
)
) ON [PRIMARY]
SELECT O1.[ID], O1.[Name], O2.[ID] as ChildID, O2.[Name] as ChildName
FROM Objects O1
INNER JOIN Objects O2
ON O1.[ID] = O2.[Parent_ID]
WHERE O1.Parent_ID IS NULL
ORDER BY O1.[ID], ChildID
SELECT O1.[ID], O1.[Name], O2.[ID] as ChildID, O2.[Name] as ChildName
FROM Objects O1
LEFT OUTER JOIN Objects O2
ON O1.[ID] = O2.[Parent_ID]
WHERE O1.Parent_ID IS NULL
ORDER BY O1.[ID], ChildID
SELECT O1.[ID], O1.[Name], O2.[ID] as ChildID, O2.[Name] as ChildName
FROM Objects O1
FULL JOIN Objects O2
ON O1.[ID] = O2.[Parent_ID]
WHERE O1.Parent_ID IS NULL
ORDER BY O1.[ID], ChildID
SELECT O1.[ID], O1.[Name], O2.[ID] as ChildID, O2.[Name] as ChildName
FROM Objects O1
CROSS JOIN Objects O2
WHERE O1.Parent_ID IS NULL
ORDER BY O1.[ID], ChildID
Удалим все объекты второго уровня:
DELETE O2
FROM Objects O1
INNER JOIN Objects O2
ON O1.[ID] = O2.[Parent_ID]
WHERE O1.Parent_ID IS NULL
Добавим всем объектам в имя суффикс, содержащий ID
родителя:
UPDATE O2
SET [Name] = O2.[Name] + '_' + CAST( O1.[ID] AS VARCHAR )
FROM Objects O1
INNER JOIN Objects O2
ON O1.[ID] = O2.[Parent_ID]
WHERE O1.Parent_ID IS NULL
Некоторые функции и
операторы
• CAST – преобразование типа. CAST (
expression AS data_type )
• CONVERT – аналог CAST. CONVERT (
data_type [ ( length ) ] , expression [ , style ]
)
select cast( '1' as int)
select convert( int, 1)
select convert(datetime, '02/18/2004', 101 )
Работа с датами
• GETDATE() – возвращает текущую дату
select getdate()
• DATEADD ( datepart , number, date )
возвращает дату, получаемую из date добавлением к ней
number единиц. Единицами могут быть дни, месяцы,
годы, секунды и т.д. datepart задает тип единиц
SELECT DATEADD(day, 21, getdate())
• DATEDIFF ( datepart , startdate , enddate ) возвращает в
указанных единицах (datepart) разницу между startdate
и enddate
SELECT DATEDIFF(day,
CONVERT(DATETIME,'02/18/2004',101), getdate())
Лекция №8 DDL
DDL – язык
определения данных.
В SQL существует ряд операторов,
позволяющих изменять структуру
данных .
Операторы
DDL позволяют
не
вникать
в
детали
хранения
информации в БД на физическом
уровне,
используя
стандартные
понятия поля и таблицы.
Это операции:
1)создание новой БД;
2)определение новой структуры
и
создание таблицы;
3) удаление таблицы;
4)изменение структуры существующей
таблицы;
5)обеспечение условий безопасности;
6)создание индексов для доступа к
таблице;
7)управление размещением данных на
устройствах.
Основу DDL составляют
три команды:
1)
2)
3)
CREATE - создать;
DROP – удалить;
ALTER – изменить.
Использование этих команд во время
работы позволяет сделать структуру
динамичной. Другими словами, в СУБД
можно создавать,
удалять таблицы,
одновременно
обеспечивая доступ к
данным. Это означает, что БД с течением
времени может расти и изменяться и её
эксплуатация может продолжаться в то
время, когда в нее вносятся новые таблицы
и приложения.
Операторы можно использовать, как в
интерактивном,
так и в программном
режимах.
Создание базы
данных.
В системе MS SQL
эти действия
выполняются оператором:
CREATE DATABASE
<name of database> ON < name 1>, <…>,
< name n>;
CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]
< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [
,...n ]
< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]
USE master
GO
CREATE DATABASE Sales
ON
Создание БД со
спецификациями данных и
журнала.
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
USE master
GO
CREATE DATABASE Employees
ON
( NAME = Empl_dat,
FILENAME = 'f:',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'g:',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
После создания пустой
базы можно создавать
таблицы.
Эти действия относятся
к структуре, а не к
данным.
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS
computed_column_expression
| < table_constraint > ::= [ CONSTRAINT
constraint_name ] }
| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR
REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::= [ CONSTRAINT
constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column
)]
[ ON DELETE { CASCADE | NO
ACTION } ]
[ ON UPDATE { CASCADE | NO
ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::= [ CONSTRAINT
constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [
,...n ] ) ]
[ ON DELETE { CASCADE | NO
ACTION } ]
[ ON UPDATE { CASCADE | NO
ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
1)Для
разделения
элементов
команды
используются пробелы, поэтому пробел не может
быть частью имени ( MY_Table).
2)Значение аргумента размерность [<size of
attribution>] – зависит от типа данных и может
заноситься по умолчанию самой СУБД. Это
удобно для числовых полей. Тип CHAR требует
обязательного указания размера – количества
символов в поле. По умолчанию размер равен
одному символу.
3)Имена таблиц должны отличаться, но могут
использоваться
одинаковые
имена
полей
(STUDENTS.SNUM USP.SNUM).
4)Пользователи не владельцы таблиц должны к
ним
обращаться
по
имени
владельца.
(SA.STUDENTS).
A. Использование PRIMARY KEY constraints:
job_id smallint
PRIMARY KEY CLUSTERED
emp_id empid (определенный пользователем
CONSTRAINT PK_emp_id PRIMARY KEY
NONCLUSTERED
B. Использование A FOREIGN KEY constraints:
job_id smallint
NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
тип)
CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id,
ord_num, title_id) REFERENCES sales (stor_id, ord_num,
title_id)
Уникальность:
pseudonym varchar(30) NULL
UNIQUE NONCLUSTERED
По умолчанию:
DEFAULT (getdate())
На значения:
CHECK (min_lvl >= 10) and
CHECK (max_lvl <= 250)
Вычисляемые поля:
CREATE TABLE mytable (
myavg AS (low + high)/2 )
low int,
high int,
Пример. Создать таблицу
STUDENTS.
CREATE
TABLE
(SNUM
SFAM
CHAR
SNAME
CHAR
SFATH
CHAR
STIP DECIMAL)
STUDENTS
INTEGER,
(20),
(15),
(15),
1. В этой команде порядок полей
определяется их местом в списке.
2. После того, как таблица создана, её
можно изменить.
Добавление новых полей выполняется
командой:
ALTER TABLE <name of table> ADD
<Name of attribution1>
<type of
attribution1>
[(<size
of
attribution1>)], …
<Name of attribution n> <type of
attribution n> [(<size of attribution
n>)]);
Добавляемые
поля
автоматически
получают значения NULL.
Пример. Предположим
мы решили добавить
номер курса и
специальность.
ALTER TABLE STUDENTS
ADD
COURS INTEGER,
SPEC CHAR (20);
Наиболее частые изменения связаны с
изменением размера поля. Необходимо
убедиться, что изменения не приведут к
потере информации, поэтому необходимо
лучше проектировать таблицу и вносить
изменения только в крайнем случае.
ALTER TABLE <name of table> MODYFI <Name of
attribution1> <type of attribution1> [(<size of
attribution1>)], …
<Name of attribution n> <type of attribution n>
[(<size of attribution n>)]);
Изменения возможны ,если столбец пуст;
Для
заполненного
столбца
изменяется
размер/точность в сторону увеличения;
Установка ограничения NOT NULL возможна
при отсутствии значений NULL;
Разрешается
изменять
значения
устанавливаемые по умолчанию.
необходимо:
1)
быть
ее
создателем
или
иметь на это право;
2)
перед удалением
необходимо ее очистить от
данных,
это
позволяет
избежать
случайной
потери
DROP TABLE
<name of table>;
информации.
Пример. Удалить все сведения
и
таблицу PREDMET.
1. DELETE FROM PREDMET;
2. DROP TABLE PREDMET;
INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
{
}
[ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
Пример10.3.
Создать для пользователя копию
таблицы PREDMET,
добавить в нее поля: лабораторные
работы, их количество.
CREATE TABLE PREDMET_NEW (
PNUM INTEGER,
PNAME CHAR (30),
COURS INTEGER,
HOURS INTEGER,
LAB CHAR (30),
NUM INTEGER);
INSERT INTO PREDMET_NEW
SELECT * FROM PREDMET;
Новые поля заполнятся значениями
по умолчанию или значениями NULL.
PNUM
2001
2002
2003
2004
2005
PNAME
Физика
Химия
Математика
Философия
Экономика
TNUM
4001
4002
4003
4005
4004
COURS
1
1
1
2
3
HOURS
34
68
65
17
17
LAB
NUM
0
0
0
0
0
Индексы, ограничения,
синонимы.
Индексом
принято
называть
упорядоченный список полей таблицы или
групп полей в таблице. В таблице с
большим
количеством
полей
при
отсутствии упорядоченности поиск может
занимать длительное время.
Индексный адрес – это специальный метод
объединения всех значений в группы( из 1
или более записей), которые отличаются
друг от друга, т.к. уникальность записей
часто необходима.
Когда создаётся индекс, в поле БД
запоминается порядок всех значений
этого поля в области памяти.
При наличии индекса система могла
бы найти искомый номер прямо в этом
упорядоченном массиве и указать,
какую искомую строку следует найти. У
индексов есть и недостатки :
1)наличие индексов замедляет операции
модификации INSERT, DELETE;
2)сам индекс занимает тоже место в
памяти.
Индексы могут состоять из нескольких
полей, при этом первое поле считается
главным, второе поле упорядоченным
внутри первого и т. д.
Создаются индексы командой:
CREATE INDEX <Name of index> on <Name
of table>(< Name of attribution1 >,[< Name
of attribution2 >,…]);
Разумеется,
таблица
должна
быть
создана ранее, и иметь
имена полей
указанных в команде. Имя индекса
является уникальным и не может быть
использовано в других целях. SQL сам
определяет, когда индекс необходим и
использует его автоматически.
Пример 11.1.
В таблице STUDENT наиболее часто употребимо
поле SFAM, создать индекс по этому полю.
CREATE INDEX SFAMIDX on STUDENTS( SFAM);
При создании индекса ему не приписана
уникальность. Это делается с помощью
специального ключевого слова UNIQUE.
CREATE
UNIQUE
INDEX
SNUMIDX
ON
STUDENTS( SNUM);
Однако эта команда не будет выполнена, если
среди значений этого поля есть не уникальные
значения. Поэтому рекомендуем создавать
индексы сразу после создания её структуры, до
ввода в неё значений.
!!!
Существует
особенность
уникального индекса, если он состоит
из нескольких полей, то вообще говоря
они могут быть не уникальными
(каждое поле в отдельности). Так как
основной
характеристикой
индекса
является имя, то по имени он может
быть идентифицирован и удален.
SQL
автоматически
определяет
доступна ли информация об индексе
конкретному пользователю. И оценивая
эту информацию, сам определяет
разрешить с ним работу или нет.
Для удаления используется команда:
DROP INDEX <Name of index>;
Например:
DROP INDEX SFAMIDX;
Удаление индексов не влияет на
данные.
Ограничения данных.
Ограничения данных – это часть
определений таблицы, описывающих
условия ввода данных. В качестве
ограничений мы рассмотрим тип,
размер вводимых данных, т.е. их
совместимость с полями, в которые
вводятся данные. Ограничения дают
возможность оговорить их значения
по умолчанию.
Существуют ограничения двух типов:
1) ограничения поля – применимые только к
указанному полю;
2) ограничения таблицы – применимые к
указанным группам полей.
Ограничения поля (атрибута) – помещается в
конец фрагмента команды, объявляющего его
имя после типа данных.
Ограничения таблицы (отношения) – помещаются
в конец объявления имени таблицы после
последнего имени поля.
CREATE TABLE <name of table>
(<Name of attribution1> <type of
attribution1> [(<size of attribution1>)]
<limit1>,
<Name of attribution2> <type of
attribution2> [(<size of attribution2>]
<limit2>, … ,
<Name of attribution n> <type of
attribution n> [(<size of attribution n>)],
<limit n>,
<limit of table>);
Часто
описание
ограничений
используют для ограждения от так
называемых NULL значений, для этих
целей используют предложения NOT
NULL, которое может быть указано
как ограничение поля.
Пример 11.2
Предусмотреть при создании
таблицы STUDENTS отсутствие в
полях SNUM и SFAM значений
типа NULL.
CREATE TABLE STUDENTS
(SNUM INTEGER NOT NULL,
SFAM CHAR (20) NOT NULL,
SNAME CHAR (15),
SFATH CHAR (15),
STIP DECIMAL);
При
этом,
используя
команды
вставки, в поля SNUM, SFAM заносят
конкретное значение. В команде
ALTER TABLE при добавлении полей
тоже можно задавать ограничения.
ALTER TABLE STUDENTS ADD
COURS INTEGER NOT NULL,
SPEC CHAR (20) NOT NULL;
Однако, если новый столбец имеет
такое ограничение, то таблица, к
которой его добавляют должна быть
пустой.
Ограничения по
уникальности.
Уникальные индексы – один из самых
простых и наиболее эффективных
методов. Однако имеется возможность
установить
уникальность
для
отдельных столбцов (полей) таблицы,
если существует уверенность, что все
значения должны отличаться. При
создании таблицы в конкретном поле
указывается слово UNIQUE, при этом
СУБД будет контролировать процесс
ввода и отклонит попытку ввести
имевшееся
ранее
значение.
Это
ограничение может применяться к
полям с ограничением NOT NULL.
Пример 11.3
Устраним повторяющиеся
значения в поле SNUM.
CREATE TABLE STUDENTS
(SNUM INTEGER NOT NULL UNIQUE,
SFAM CHAR (20) NOT NULL,
SNAME CHAR (15),
SFATH CHAR (15),
STIP DECIMAL);
Напоминаем,
что
поля
являющиеся
уникальными
являются
кандидатамиключами, или уникальными ключами.
Подобное
ограничение
в
поле
SFAM
запретило бы иметь однофамильцев в
таблице STUDENTS!
Объявление уникальности возможно и для
группы полей, с помощью ограничения к
таблице.
Между уникальностью поля и таблицы
существуют различия:
1)
уникальные поля - дают единственную
запись-строку;
2)
уникальные группы – уникальная
комбинация значений полей из этой группы,
при этом не требуется уникальность каждого
отдельного поля.
С другой стороны, если хотя бы одно поле в
группе уникальное, то и значение всей
Пример 11.4
В таблице успеваемости USP
объявить поля SNUM, UDATE –
уникальными в группе (пересдачи
фиксируются датой).
CREATE TABLE USP
(UNUM INTEGER NOT NULL UNIQUE,
MARK INTEGER,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM, UDATE) );
Для записи уникальных индексов
(первичных ключей) использовалось
ограничение UNIQUE. Однако SQL
поддерживает
специальное
ограничение PRIMARY KEY.
С помощью такого параметра можно
ограничивать как отдельные поля, так и
таблицу. Но следует помнить, что в
одной
таблице
существует
единственный
первичный
ключ.
Синтаксис этого ограничения такой же
как у UNIQUE, первичные ключи не
допускают NULL значений(естественно
выполнение ограничений NOT NULL.
Пример 11.5
В таблице успеваемости USP
объявить поле UNUM –
первичным ключом.
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
MARK INTEGER,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM,PNUM) );
Ограничение
первичного
ключа
может применяться для группы.
CREATE TABLE USP
(UNUM INTEGER NOT NULL UNIQUE,
MARK INTEGER,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
PRIMARY KEY (SNUM,PNUM,UDATE));
Очевидно, что в этой комбинации
значения полей SNUM,PNUM,UDATE
должны быть уникальными, хотя по
отдельности, или попарно они могут
повторяться.
В качестве первичных ключей лучше
применять числовые целые типы
полей.
Не
исключена
возможность
совпадения
у
разных
людей
фамилий, имен, отчеств, поэтому
такие комбинации не допустимы в
качестве первичных ключей.
Ограничения,
определяемые для
данных.
В таблице может существовать любое
число ограничений для конкретных
данных.
Для этого SQL использует
предложение
CHECK (condition of attribution):
1) оно ставит указанное условие;
2) проверяет данные при вводе до
сохранения в таблице.
Пример11.6
Оценки не должны превышать 5
баллов.
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
MARK INTEGER CHECK(MARK <= 5),
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM,PNUM,UDATE));
CHECK можно использовать для
предопределения значений:
Пример11.7
Оценки выбираются из списка
1,2,3,4,5.
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
MARK INTEGER CHECK (MARK IN
(1,2,3,4,5)),
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM,PNUM,UDATE));
Команда ALTER TABLE позволяет
изменять таблицу, но изменения
или удаление ограничений не
всегда возможны. Более надежный
способ создание новой таблицы и
заполнение ее сведениями из
старой.
CHECK используется в качестве
табличного ограничения, когда в
нем участвуют несколько полей.
Пример 11.8
«5» ставят до 15.06.2002.
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
MARK INTEGER,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
CHECK((MARK<5) AND (UDATE>15.06.02) );
Не
допускается
проверка
ограничений связанных сразу с
несколькими строками таблицы.
Например:
Оценки по дисциплине не ниже, чем
средняя оценка по дисциплинам.
Описания значений по
умолчанию.
При внесении данных система либо
заносит указанные данные, либо берет их
по
умолчанию.
Наиболее
распространенным
значением
по
умолчанию является NULL( для любого
столбца, где отсутствуют ограничения
NOT NULL).
В SQL имеется специальное средство
DEFAULTэто
предложение
не
ограничительного
свойства,
а
определительного.
Пример 11.9
Если практика показывает, что
большая часть оценок «4», то
поставить его по умолчанию.
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
MARK INTEGER CHECK (MARK IN
(1,2,3,4,5)) DEFAULT MARK = 4,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL);
Использование
значений
по
умолчанию это альтернатива NULL,
т.к. неизвестное значение неверно
при любом сравнении (кроме IS
NULL), такие записи исключают
большинство предикатов.
Иногда нужно вводить пустые поля,
не обрабатывая их каким либо
образом:
1)
числовые – 0;
2)
символьные – пробел.
В
этом
случае
записи
обрабатываются как любые другие.
Создание синонимов.
Мы отмечали ранее, что обращение к
чужим таблицам осуществляется с
указанием префиксов-владельцев в
имени таблицы. При выполнении
большого количества команд такие
записи становятся неудобными.
Синоним – это альтернативное имя
таблицы,
а
пользователь
его
создавший становится его владельцем,
т.е.
освобождается
от
бремени
префикса.
Если пользователю разрешен доступ хотя
бы к одному полю таблицы, то он имеет
право создать синоним.
CREATE SYNONYM <Name of synonym>
FOR <Name of table>;
Например,
CREATE
SYNONYM
FIRST
FOR
SA.STUDENTS;
Два
одинаковых
имени
таблицы,
связанные с разными владельцами не
идентичны, а значит - не приводят к
беспорядку. Поэтому пользователь может
создавать синонимы совпадающие с
именем оригинала (либо это будет его
таблица, либо к ней у него не будет
доступа).
Пример 11.10
Пользователь BS: CREATE SYNONYM
STUDENTS FOR SA.STUDENTS;
Теперь оба (BS,SA) могут обращаться к
ней по имени STUDENTS, а система это
проконтролирует.
Существуют стандартные синонимы – PUBLIC
SYNONYM, которые будут иметь единое для всех
пользователей имя.
CREATE PUBLIC SYNONYM STUDENTS FOR
STUDENTS;
Создавать
такие
синонимы
привилегия
собственника,
администратора
или
привилегированного пользователя. Пользователям
должны быть предоставлены также и определенные
привилегии к таблице STUDENTS, т.к. имя является
общим, а сама таблица общей не является.
Любые синонимы удаляются командой :
DROP SYNONYM <Name of synonym>;
DROP SYNONYM FIRST;
Это
право
владельцев,
для
общих
администратора.
Разумеется
сама
таблица
не
А.
В этом примере создается индекс по колонке au_id
БД pubs
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes WHERE
name = 'au_id_ind') DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind ON authors (au_id)
GO
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME = 'emp_pay') DROP TABLE emp_pay GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes WHERE name =
'employeeID_ind') DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay ( employeeID int NOT NULL, base_pay money NO
NULL, commission decimal(2, 2) NOT NULL )
INSERT emp_pay VALUES (1, 500, .10)
INSERT emp_pay VALUES (2, 1000, .05)
INSERT emp_pay VALUES (3, 800, .07)
INSERT emp_pay VALUES (5, 1500, .03)
INSERT emp_pay VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind ON emp_pay
(employeeID)
GO
Лекция №9 Использование сценариев
Использование сценариев SQL.
Сценарий
(script)
представляет
собой
набор
операторов Transact-SQL, хранимых в файле. Сценарии
часто используются для постоянного хранения команд для
выборки, записи, создания и заполнения объектов баз
данных. Так как сценарии хранятся в текстовых файлах, то
они могут быть использованы для воссоздания базы данных
на другом сервере.
В сценарий можно включить любой допустимый
оператор Transact-SQL. Внутри сценария операторы
группируются в пакеты(Batches), которые отделяются друг от
друга командой GO. Эта команда записывается в отдельной
строке. Если сценарий выполняется без GO,
то он
рассматривается как один пакет.
Для создания сценария часто используется
окно запросов в
server query analyzer /
server management studio (SMS)
Для формирования сценария можно использовать
стандартные приемы, такие как выбор объектов из
окна проводника.
1)Наберите инструкцию USE и из дерева объектов
перетащите имя БД;
2) Наберите инструкцию Select * from и перетащите
имя таблицы;
3) Наберите инструкцию Select * from и перетащите
имя ещё одной таблицы;
4)Выполните сценарий, используйте панель
инструментов и кнопку Execute Query (выполнить
запрос);
Результаты отобразятся в двух панелях вкладки Grids
Use study
Select * from Students
Select * from Teachers
go
Используя проводник, можно построить запрос
более детально:
1) Наберите инструкцию Select и в дереве
объектов откройте таблицу Students и объект
Colomns, в котором выберите нужные поля( или
перетащите всю папку после слова Select);
Select Snum, Sfam,Sname, Stip from Students
Сценарий создания таблицы, заполнения,
просмотра:
CREATE TABLE AVGRAITING(AUNUM INTEGER
NOT NULL UNIQUE,
AVGMARK INTEGER, UDATE DATE, SNUM
INTEGER,
PNUM INTEGER)
GO
INSERT INTO AVGRAITING (SNUM, AVGMARK)
SELECT SNUM, AVG (MARK) FROM
USP GROUP BY SNUM
GO
SELECT SNUM, AVGMARK FROM AVGRAITING
GO
Для выполнения сценария на панели инструментов
выбирается кнопка Execute Query
AVGRAITING
SNUM
AVGMARK
3412
4,5
3413
4
3414
3
3416
5
После выполнения запроса сохраните его (Editor Save),
без выбора Editor сохранится результат, а не сам сценарий.
Другой способ создания сценария это скриптование
объектов. Скриптование создает полный оператор
Transact-SQL и может выполняться из контекстного
меню большинства объектов. Не все типы сценариев
применимы ко всем типам объектов, а к таким типам,
как параметры функции или столбцы, операция
скриптования не может применяться вообще.
Create(создать)
Alter(изменить)
Drop(Изъять)
Select(Выбрать)
Insert(Вставить)
Update(Обновить)
Delete(Удалить)
Execute(Выполнить)
Напишем сценарий для оператора SELECT^
1) В дереве объектов нашей базы данных STUDY
укажите на таблицу STUDENTS и вызовите
контекстное меню, в котором выберите Script Object
to new Window As и выберите SELECT;
2) Query Analyzer откроет новое окно, содержащее
запрос Select [sname],[sfam],[sname],[sfath],[stip] from
[study].[dbo].[students]
Создадим сценарий для создания таблицы:
В окне дерева объектов STUDY правой кнопкой вызовем
контекстное меню, ранее созданной, например в Ent.
Manager, таблицы Students, и перейдем к Script Object To
New Window AS и выберем CREATE.
В новом окне получим скрипт:
CREATE TABLE [Students] (
[Snum] [int] IDENTITY(4000, 1) NOT NULL,
[Sfam] [varchar] (50),
[Sname] [varchar] (50),
[Sfath] [varchar] (50),
[Stip] [decimal] ,
CONSTRAINT [PK_SNUM] PRIMARY KEY
CLUSTERED ([Snum] ON [PRIMARY]
)
GO
Поскольку БД не может содержать две одноименных
таблицы изменим ее имя или дополним скрипт:
IF EXISTS( select name from sysobjects
Where name=‘Students’)
DROP TABLE Students
GO
CREATE TABLE [Students] (
[Snum] [int] IDENTITY(4000, 1) NOT NULL,
[Sfam] [varchar] (50),
[Sname] [varchar] (50),
[Sfath] [varchar] (50),
[Stip] [decimal] ,
CONSTRAINT [PK_SNUM] PRIMARY KEY
CLUSTERED ([Snum] ON [PRIMARY]
)
GO
Для хранения
данных могут использоваться не
только постоянные объекты, но и созданные для временного
хранения. Они автоматически удаляются MS SQL SERVER,
как только работа с ними заканчивается. Необходимо
помнить, что это ресурсоемкая процедура, и при
возможности использовать табличные переменные.
Временные таблицы создаются такими же командами
как и постоянные, различие в имени, которое начинается #
или ## (# локальная, ## глобальная):
CREATE TABLE MyTempTable (cola INT PRIMARY KEY)
INSERT INTO MyTempTable VALUES (1)
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
CREATE TABLE ##MyTempTable (cola INT PRIMARY KEY)
INSERT INTO ##MyTempTable VALUES (1)
Переменные.
Переменные обозначаются префиксом @ или @@,
глобальные переменные используются MS SQL
SERVER и не могут определятся самостоятельно.
Объявление переменных
DECLARE <@локальная переменная> < тип
данных>, … , <@локальная переменная> < тип
данных>
Динамически формируемые запросы
Для вызова используется системная хранимая процедура
exec
EXEC[ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )
declare @sql_stmnt varchar(50)
set @sql_stmnt = 'select * from people'
exec ( @sql_stmnt )
Объявление переменных
Имя переменной должно содержать первый символ «@»
локальная или «@@» глобальная.
declare @variable_name variable_type[,
@variable_name variable_type…]
declare @x int,
@y float,
@s varchar(10)
Оператор присваивания
SET @local_variable = expression
set @x = 10
set @y = 1.5
set @s = 'test'
Выборка значения поля в переменную
create table T2(
x int,
s varchar(20)
)
insert into T2 (x,s) values(1,'test')
select top 1 @s=s, @x=x from T2
возможен также вариант с использованием set
set @s=(select top 1 s from T2)
Использование системной функции @@IDENTITY
create table T3(
x int identity(1,1),
s varchar(20)
)
insert into T3 (s) values('test')
set @x = @@identity
print @x
в @x будет записано последнее значение, которое было
присвоено полю x автоматически
Использование значения переменных в конструкциях
insert и update
set @x=4
set @s='qwerty'
insert into T2 (x,s) values(@x,@s)
insert into T2 (x,s) select @x, @s
update T2 set s='123' where x=@x
insert into T2 (x,s) select x, @s from T3
переменные могут использоваться в правой части
равенств блоков where
Условный оператор, циклы
Syntax
•IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
•WHILE Boolean_expression
{ sql_statement | statement_block }
[ BREAK ]
{ sql_statement | statement_block }
[ CONTINUE ]
Для выделения блока используются операторы begin..end
При создании локальная переменная имеет
значение NULL, новые значения присваиваются
командой.
Declare @myCharvar varchar(50)
SET @myCharvar=‘Hello!’ или
SELECT @myCharvar=‘Hello!’
SELECT @myCharvar=max(sfam) from STUDENTS
INSERT INTO @myCharvar select tfam from Teacher
INSERT INTO @myCharvar Values (‘Variables’)
Курсоры
• Одна из сильных сторон системы
SQL – использование при просмотре
любого количества строк. Однако это
преимущество
становится
проблемой при взаимодействии с
другими языками. Трудно сохранить
результаты запроса в переменных,
если заранее не известно их
количество. В SQL для этого
вводится понятие курсора.
Курсоры
Объявление курсора:
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
•STATIC – изменения в исходных таблицах не
отображаются в курсоре
• DYNAMIC - изменения в исходных таблицах
отображаются в курсоре
•SCROLL_LOCKS – изменения данных, сделанные через
курсор, гарантированно будут проделаны
•OPTIMISTIC – изменения не будут проделаны, если
соответствующие строки были изменены другими
запросами после считывания в курсор
OPEN <cursor name> - открывает курсор
CLOSE <cursor name> - очищает result set, однако курсор
остается доступным для открытия, при каждом открытии
result set будет переформирован
DEALLOCATE <cursor name> - уничтожает курсор
Передвижение по курсору
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ]
Функция @@FETCH_STATUS возвращает 0, если FETCH
выполнился успешно
• Курсор – это вид переменной, которая
связана с запросом. Значением
курсора может быть каждая строка,
которая выводится запросом. Он
должен быть заранее объявлен
командой:
• DECLARE
CURSOR
<NAME
OF
CURSOR> FOR
SELECT <NAME OF ATTRIBUTION 1>, ...,
<NAME OF ATTRIBUTION n>
FROM <NAME OF TABLE>
[WHERE < CONDITION > ] ;
DECLARE CURSOR STIPCUR FOR
SELECT SNUM, SFAM, STIP FROM
STUDENTS
WHERE STIP > 0;
• Этот запрос не выполняется
немедленно, т.к. в данном случае это
лишь его определение.
• Курсор похож на представление, его
содержательной частью являются
данные запроса. Это становится
возможным после открытия курсора.
Его строки в отличие от базовых
таблиц и представлений строго
упорядочены, имеется 1 строка, 2
строка и т.д.
• Может быть явно задан
ORDER BY < > в запросе.
• Если в программе необходимо
выполнить запрос, то открывается
курсор:
• EXEC SQL OPEN CURSOR STIPCUR;
• Значения передаются только тогда,
когда выполнена эта команда.
• Для извлечения данных по одной
строке используется команда
• FETCH INTO <:NAME of variable 1>,
…, <:NAME of variable n>;
• Пример.
• В нашем случае для курсора, объявленного
командой:
• EXEC SQL
DECLARE CURSOR STIPCUR FOR
SELECT SNUM, SFAM, STIP FROM STUDENTS
WHERE STIP > 0;
• Для извлечения данных нужна строка:
• EXEC SQL FETСH STIPCUR INTO
:STUDENTSID, :STUDENTSFAM,
:STUDENTSSTIP;
• Эта конструкция поместит данные из
первой выводимой в запросе строки в
переменные памяти. Для получения
данных из всех строк команду FETСH
помещают, как правило, внутрь
цикла. Данные из каждой строки
будут помещаться
в эти же самые переменные.
• Этот оператор освобождает курсор от
данных:
• CLOSE CURSOR < NAME OF
CURSOR >;
• После его использования вывод
нужно повторить новым открытием
курсора.
• 1) SQL не следит на какой строке
вывода
завершили
работу
с
курсором, после повторного открытия
выполнение продолжится с точки
остановки, цикл должен начаться
заново.
• 2)
Если выходные строки
закончились, а цикл не завершен, то
в переменные памяти идет вывод
идентичный последней полученной
строке.
Примеры работы с курсорами
declare Cur cursor for
select P_Name, P_City, P_Age
from People
open Cur
declare @P_Name varchar(50), @P_City varchar(50), @P_Age int
fetch next from Cur
into @P_Name, @P_City, @P_Age
while @@fetch_status = 0
begin
print @P_Name+ '|' + @P_City + '|' + cast(@P_Age as varchar)
fetch next from Cur
into @P_Name, @P_City, @P_Age
end
close Cur
deallocate Cur
Изменение данных с использованием курсора
declare Cur cursor
DYNAMIC SCROLL_LOCKS
for select P_Name, P_City, P_Age from People
for update
open Cur
declare @P_Name varchar(50), @P_City varchar(50), @P_Age int
fetch next from Cur
into @P_Name, @P_City, @P_Age
update People set P_Name = 'Bill Gates'
where current of Cur
close Cur
deallocate Cur
• Пример.
• Встроена часть в Pascal. Все
переменные должны быть объявлены
в SQL.
• EXEC SQL BEGIN
DECLARE SECTION
VAR STUDENTSID : INTEGER;
STUDENTSFAM: PACKED ARRAY [1..20]
OF CHAR;
STUDENTSSTIP: REAL;
• Допустим,
пользователь
просматривает данные по строке:
Лекция №10 DML особенности применения
DML - Управление данными.
При организации работы с SQL важно не
только уметь строить выборки данных, но и
пользоваться
средствами,
управляющими
значениями в базах данных. Рассмотрим
основные инструкции:
INSERT, UPDATE, DELETE.
Все эти инструкции относятся к разделу
DML – управления данными.
INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
{ [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
Вставка строк данных – INSERT.
INSERT [ INTO] <имя базовой или временной таблицы>
[(список полей)]
VALUES (список значений)| или <запрос>;
Пример 9.1
Необходимо добавить новые сведения в таблицу TEACHERS.
INSERT INTO TEACHERS
(TNUM, TFAM, TNAME, TFATH, TDATE)
VALUES
(4006,‘Федченко’,‘Светлана’, ‘Геннадьевна’,01.09.02);
Для просмотра результатов:
SELECT * FROM TEACHERS;
TEACHERS
TNUM
TFAM
TNAME
TFATH
TDATE
4001
Викулова
Валентина
Ивановна
01/04/1994
4002
Костенко
Олег
4003
Казанцев
Виталий
4004
Поздняк
Любовь
4005
Загоруйко
Илья
Дмитриевич
10.05.1999
4006
Федченко
Светлана
Геннадьевна
10.06.2001
Владимирови 01/09/1997
ч
Владимирови
01-09ч
1988
Алексеевна 01.09.1988
1) Команда не осуществляет никакого вывода
информации, поэтому следует контролировать:
а) правильность имени таблицы;
б) совпадение типов и количества значений
указанным полям.
2) Допускается частичное указание полей в произвольном
порядке:
INSERT INTO TEACHERS
(TDATE, TFAM, TNAME, TNUM)
VALUES (01/09/02,’Федченко’, ‘Светлана’, 4006);
В отсутствующие поля значения вводятся по –
умолчанию DEFAULT или NULL.
Если ограничения запрещают такие
значения, то значения должны указываться явно:
INSERT INTO TEACHERS (TNUM, TFAM,
TNAME, TFAT, TDATE)
VALUES (4006, ’Федченко’, ‘Светлана’,
‘Геннадьевна’, 19.10.2005);
Эта же команда может быть использована для
заполнения одной таблицы результатами запроса
к другой таблице.
Пример 9.2
Заполнить таблицу отличники, имеющую такую
же структуру как успеваемость.
INSERT INTO EXCELENT SELECT * FROM USP
WHERE MARK=5;
В качестве результата может быть создана такая таблица:
SELECT * FROM EXCELENT;
EXCELENT
EUNUM
SNUM
PNUM
UDATE
MARK
1001
3412
2001
10.06.2002
5
1005
3416
2004
12.06.2002
5
Естественно, что ее структура была создана ранее.
CREATE TABLE EXCELENT
(EUNUM INTEGER NOT NULL UNIQUE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UDATE DATE,
MARK INTEGER );
Пример 9.3
CREATE TABLE AVGRAITING(AUNUM INTEGER NOT NULL UNIQUE,
AVGMARK INTEGER, UDATE DATE, SNUM INTEGER,
PNUM INTEGER);
Заполнить таблицу, имеющую такую же
структуру как успеваемость, с указанием
среднего балла студента в сессию в графе
оценка.
INSERT INTO AVGRAITING
(SNUM, AVGMARK)
SELECT SNUM, AVG (MARK)
FROM USP GROUP BY SNUM;
AVGRAITING
SNUM
AVGMARK
3412
4,5
3413
4
3414
3
3416
5
SELECT SNUM, AVGMARK FROM AVGRAITING;
Список полей и их типы соответственны с данными запроса.
В командах INSERT можно использовать
подзапросы.
Пример 9.4
Собрать в таблицу ONFIVE данные о студентах,
получивших хотя бы одну пятерку.
INSERT INTO ONFIVE (SNUM, SFAM, SNAME,
SFATH)
SELECT STUDENTS.SNUM, STUDENTS.SFAM,
STUDENTS.SNAME, STUDENTS.SFATH FROM
STUDENTS
WHERE SNUM=ANY (SELECT SNUM FROM USP
WHERE MARK=5);
ONFIVE
SNUM
SFAM
SNAME
SFATH
3412
Поляков
Анатолий
Алексеевич
3416
Нагорный
Евгений
Васильевич
Оба запроса работают как обычно:
Внутренний – находит записи, удовлетворяющие
условию и возвращает из них номер SNUM;
внешний – выбирает строки из STUDENTS, для
которых найдены значения SNUM. (вложенные запросы)
Пример 9.5
Собрать в таблицу MAXMARK сведения о максимальных
оценках на определенную дату.
INSERT INTO MAXMARK
(SNUM, MARK, UDATE)
SELECT SNUM, MARK, UDATE FROM USP as A
WHERE MARK=
(SELECT MAX (MARK) FROM USP as B
WHERE A.UDATE=B.UDATE);
MAXMARK
SNUM
MARK
UDATE
3412
5
10.06.2002
3414
3
11.06.2002
3416
5
12.06.2002
Здесь используются соотнесенные подзапросы.
Удаление данных – DELETE.
DELETE [FROM] <имя таблицы>[< условия>] ;
Эта команда осуществляет удаление строк-записей из
указанной таблицы.
!!!При этом удаляется только целая запись, параметры поля в
этой команде не доступны.
Пример 9.6
А) Удалить все сведения из таблицы STUDENTS.
DELETE FROM STUDENTS;
Б) Удалить сведения о студенте Котенко.
DELETE FROM STUDENTS
WHERE SNUM = 3415;
Так как SNUM - первичный ключ, то удаляется 1
запись. Если бы в качестве условия выбиралась бы
фамилия, то при наличии однофамильцев
удалились бы все сведения о них (то есть
несколько записей).
DELETE FROM STUDENTS
WHERE SFAM = ‘Грицецко’;
Пример 9.7
Удалить сведения об оценках за 10.06.02
(экзамен аннулирован).
DELETE FROM USP
WHERE UDATE=‘10.06.02’;
UNUM
SNUM
PNUM
UDATE
MARK
1003
3414
2005
11.06.2002
3
1004
3412
2003
12.06.2002
4
1005
3416
2004
12.06.2002
5
Допускается использование вложенных запросов. Чаще
всего это необходимо, когда критерий базируется на другой
таблице.
Пример 9.8
Удалить из таблицы сведения о тех лицах, которые имеют
двойки по любой из дисциплин.
DELETE FROM STUDENTS
WHERE SNUM IN (SELECT SNUM FROM USP
WHERE MARK=2);
подзапрос выберет список номеров с двойками:
основная команда удалит данные о тех студентах, чьи
номера оказались в этом списке.
В команде DELETE допускаются соотнесенные подзапросы.
Пример 9.9
Удалить из таблицы сведения о тех лицах, которые имеют двойки
по любой из дисциплин с подтверждением существования
таковых.
DELETE FROM STUDENTS WHERE EXISTS
(SELECT * FROM USP WHERE MARK=2
AND STUDENTS.SNUM = USP.SNUM);
Для каждой строки из STUDENTS осуществляется проверка
подзапроса.
Изменение существующих данных в таблице – UPDATE.
UPDATE <имя таблицы> SET (список элементов:
имя столбца ={выражение| DEFAULT| NULL} )
[<условия>];
В предложении SET могут использоваться выражения в списке
того поля, которое необходимо изменить. ( В INSERT в списке
VALUES выражения не допускаются.!!!)
Пример 9.10
А) Пересчитать результаты сессии по 10 бальной шкале.
UPDATE USP SET MARK=2*MARK;
Б) Увеличить стипендию в 1.5 раза.
UPDATE STUDENTS SET STIP=STIP*1.5;
Чаще всего исправления вносятся при каких-то условиях.
Пример 9.11
Пересчитать результаты сессии по математике с
использованием 10 бальной шкалы.
UPDATE USP SET MARK=2*MARK
WHERE PNUM=2003;
UNUM
1001
1002
1003
1004
1005
SNUM
3412
3413
3414
3412
3416
PNUM
2001
2003
2005
2003
2004
UDATE
10.06.2002
10.06.2002
11.06.2002
12.06.2002
12.06.2002
MARK
5
8
3
8
5
Пример 9.12
Преподаватель Викулова уехала на стажировку, передать все её
предметы преподавателю Федченко.
UPDATE PREDMET SET TNUM=4006
WHERE TNUM=4001;
Или
UPDATE TEACHERS SET TFAM=’Федченко’,
TNAME=’Светлана’, TFATH=’Геннадьевна’, TDATE=10.09.01
WHERE TNUM=4001;
Эти команды укажут нового преподавателя физики.
!!!
1)Нельзя модифицировать несколько таблиц сразу.
2) Нельзя использовать префикс в именах полей.
В качестве значений списка SET могут использоваться
значения NULL.
Пример 9.13 Необходимо изменить все оценки по предмету
2003 на NULL.
UPDATE USP SET MARK=NULL WHERE PNUM=2003;
UNUM
1001
1002
1003
1004
1005
SNUM
3412
3413
3414
3412
3416
PNUM
2001
2003
2005
2003
2004
UDATE
10.06.2002
10.06.2002
11.06.2002
12.06.2002
12.06.2002
MARK
5
3
5
Мощным средством является использование
подзапросов в команде UPDATE. При этом необходимо
соблюдение следующих правил:
1) в предложении FROM любого подзапроса
нельзя модифицировать таблицу, на которую ссылается
основная команда;
2) может возникнуть проблема дублирования
строк, полученных в результате вложенного подзапроса;
(в таблице могут быть заданы ограничения по
уникальности, это выясняется с использованием
предложения EXISTS )
Пример 9.14
Увеличить стипендию студентам, сдавшим 2 и более
предметов без троек.
UPDATE STUDENTS SET STIP=1.1*STIP
WHERE 2<=(SELECT COUNT (*) FROM USP
WHERE (STUDENTS.SNUM = USP.SNUM)
and (USP.MARK>3));
SNUM
SFAM
SNAME
SFATH
STIP
3412
Поляков
Анатолий
Алексеевич
28,05
Здесь внутренний подзапрос определяет количество записей в
USP.
Пример 9.15
Модифицируем оценки студентов в зависимости от
величины среднего балла.
1)
SELECT AVG(MARK) FROM USP;
2)
UPDATE USP SET MARK=MARK – 0.5
WHERE MARK < 4.2;
Нельзя вместо 4.2 подставить подзапрос.
Пример 9.16
Модифицируем стипендию в зависимости от величины
минимального балла в тот или иной день.
UPDATE STUDENTS SET STIP=STIP – 0.05*STIP
WHERE SNUM IN (SELECT SNUM FROM
USP A
WHERE MARK=(SELECT MIN (MARK)
FROM USP B
WHERE A.UDATE=B.UDATE
AND A.PNUM= B.PNUM));
SNUM
SFAM
SNAME
SFATH
STIP
3412
Поляков
Анатолий
Алексеевич
24,23
3413
Старова
Любовь
Михайловна
16,15
3414
Гриценко
Владимир
Николаевич
0,00
3415
Котенко
Анатолий
Николаевич
0,00
3416
Нагорный
Евгений
Васильевич
24,23
X
Представления.
Представление VIEW – это именованная
виртуальная
таблица,
содержание
которой выбирается из других таблиц с
помощью запросов.
При изменении значений в таблицах
автоматически
меняются
значения
представления.
Наличие имени у такой таблицы
позволяет пользователю выполнять с
ней операции аналогичные операциям с
базовыми таблицами.
Рассмотрим таблицы, относящиеся к
базовым, т.е. таким, которые содержат
данные и постоянно находятся на
устройствах
хранения
информации.
Представления по сравнению с ними
являются более гибкими средствами.
Когда СУБД отыскивает в команде
ссылку
на
представление,
она
отыскивает
его
определение,
хранящееся в БД.
После этого происходит преобразование
пользовательской
команды
в
её
эквивалент с учетом запроса. У
пользователя возникает впечатление,
что он работает с настоящей реально
существующей таблицей.
СУБД
имеет
две
возможности
реализации представления:
если определение представления
простое, то система формирует каждую
запись по мере необходимости;
- если представление сложное, СУБД
сначала выполняет материализацию
представления,
т.е.
сохраняет
информацию, из
которой состоит
представление во временной таблице.
Затем
система
выполняет
пользовательские
команды
и
формирует
её
результаты,
после
временная таблица удаляется.
CREATE VIEW <Name of view >
[(name of attributes),…] AS
<SELECT …>;
Пример 12.1. Создать представление о
студентах, получающих стипендию в
размере 25.50.
CREATE VIEW STIP25_50
AS SELECT * FROM STUDENTS
WHERE STIP=25.50;
Теперь в БД существует представление
STIP25_50. Это такая же таблица, как и
остальные. С ней можно выполнять
запросы, изменения, вставки как с
другими таблицами. При выполнении
запроса к ней:
SELECT * FROM STIP25_50;
будет получена таблица.
SNUM
3412
SFAM
Поляков
SNAME
SFATH
STIP
Анатолий Алексеевич 25,5
0
3416 Нагорный Евгений Васильевич 25,5
0
Если к ней обратиться с запросом с предикатом
SELECT * FROM STIP25_50 WHERE SFAM<’П’;
То будет получен результат:
SNUM
SFAM
3416 Нагорный
SNAME
Евгений
SFATH
STIP
Васильевич 25,5
0
При создании
представлений можно
часть информации
скрыть.
Пример 12.2. Создать представление
о студентах без указания стипендии.
CREATE VIEW STIPOFF AS SELECT
SNUM, SFAM, SNAME, SFATH
FROM STUDENTS;
При выполнении запроса к ней,
будет получена таблица :
SNUM
SFAM
SNAME
3412
Поляков Анатолий
3413
Старова
3414
3415
3416
Любовь
Владими
Гриценко
р
Котенко Анатолий
Нагорны
Евгений
й
SFATH
Алексееви
ч
Михайловн
а
Николаеви
ч
Николаеви
ч
Васильеви
ч
Представление теперь может
изменяться также как и
таблица, фактически же
команда направлена к таблице
STUDENTS.
Пример 12.3. Изменить у студента с
номером студенческого билета 3415
имя.
UPDATE STIPOFF SET SNAME =’
Василий’ WHERE SNUM=3415;
UPDATE STUDENTS SET SNAME =’
Василий’ WHERE SNUM=3415;
Но команда
UPDATE STIPOFF SET STIP =100
WHERE SNUM=3415;
будет отвергнута, так как поле STIP в
представлении STIPOFF отсутствует.
Существуют ограничения на
модификацию представлений.
Чаще всего в представлении используются
те же имена столбцов, что и в базовых
таблицах.
При
использовании
объединения может быть конфликт имен.
Допустимо указание других имен в
представлении в скобках после имени.
Пример 12.4.
CREATE VIEW STIPCOUNT(STIP, NUM) AS
SELECT STIP, COUNT(*) FROM STUDENTS
GROUP BY STIP;
Сделаем запрос к представлению: показать
все данные о стипендиях, которые получают 2
и более человек.
SELECT * FROM STIPCOUNT WHERE NUM>=2;
Но не допускается функция в предикате
WHERE:
SELECT
STIP, COUNT(*) FROM STUDENTS
WHERE COUNT(*) >= 2 GROUP BY STIP;
Правильная команда:
SELECT STIP, COUNT(*) as NUM
FROM
STUDENTS GROUP BY STIP
HAVING
STIP
NUM
COUNT(*)>=2;
0,00
2
25,50
2
В
SQL
существует
понятие
групповых
представлений, т.е. имеющих предложение GROUP
BY или основанных на других групповых
представлениях.
CREATE VIEW STIPCOUNT (STIP, NUM)
AS SELECT STIP, COUNT(*)
FROM
STUDENTS GROUP BY STIP;
STIP
0,00
17,00
25,50
NUM
2
1
2
Представления могут
основываться на двух и более
таблицах.
Пример 12.5.Создать представление о
студентах и их оценках.
CREATE VIEW STUDMARK AS SELECT
C.UNUM, A.SFAM, B.PNAME, C.MARK
FROM STUDENTS A, PREDMET B, USP C
WHERE
A.SNUM=C.SNUM
AND
B.PNUM=C.PNUM;
После этого легче ориентироваться в
оценках:
SELECT * FROM STUDMARK;
UNUM
SFAM
PNAME
MARK
1001
Поляков
Физика
5
1002
Старова
Математика
4
1003
Гриценко
Экономика
3
1004
Поляков
Математика
4
1005
Нагорный
Философия
5
1006
Гриценко
Физика
2
1007
Поляков
Экономика
4
Допускается
соединение
представления
с
базовыми
таблицами:
SELECT
SFAM, PNAME, MARK,
UDATE
FROM STUDMARK A, USP B
WHERE A.SFAM= ‘Поляков’
AND A.UNUM=B.UNUM;
Результат работы запроса:
SFAM
PNAME
MARK UDATE
10.06.200
Поляков
Физика
5
2
Математик
12.06.200
Поляков
4
а
2
13.06.200
Поляков Экономика
4
2
Представления допускают
соотнесенные подзапросы.
Пример12.6.Пусть в таблице USP
UNUM
1001
1002
1003
1004
1005
1006
1007
SNUM
3412
3413
3414
3412
3416
3414
3412
PNUM
2001
2003
2005
2003
2004
2001
2005
UDATE
10.06.2002
10.06.2002
11.06.2002
12.06.2002
12.06.2002
12.06.2002
13.06.2002
MARK
5
4
3
4
5
2
4
Создадим представление об оценках
превышающих среднюю.
1)CREATE VIEW AVGMARK AS
SELECT * FROM USP A WHERE
MARK>(SELECT AVG(MARK) FROM
USP B WHERE B.PNUM= A.PNUM);
2)SELECT * FROM AVGMARK;
UNUM
1001
1007
SNUM
3412
3412
PNUM
2001
2005
UDATE
10.06.2002
13.06.2002
MARK
5
4
Из
этих
примеров
следует,
что
представления значительно облегчают
работу с данными. Однако они являются
чаще всего объектами доступными для
чтения.
Существуют
ограничения
на
построения представлений:
1) в них не допускаются объединения
UNION запросов;
2) не допустимо упорядочение ORDER
BY, так как в базовых таблицах
записи не упорядочены.
Для удаления представлений
используется команда
DROP VIEW <Name of view>;
Для удаления представления не требуется
удалять все данные, потому что реально
они в нем не содержатся.
DROP VIEW AVGMARK;
Так как представления состоят из
результатов
запросов, то для их
модификации
должны
быть
модифицированы данные из базовых
таблиц. Но модификация не должна
воздействовать на запрос, она
воздействует на значения в таблице.
Рассмотрим критерии, по которым мы
определяем,
является
ли
представление модифицируемым:
1) представление должно основываться
только на одной таблице;
2) оно должно содержать первичный
ключ этой таблицы;
3)
представление не должно иметь
полей - агрегатных функций;
4) представление не должно использовать
DISTINCT;
5) представление не должно использовать
GROUP BY, HAVING;
6) представление не должно использовать
подзапросы;
7) представление не должно использовать
константы, строки, выражения среди полей
вывода;
8)
для команды INSERT оно может
содержать любые поля базовой таблицы,
для которой имеются ограничения NOT
NULL, если другое значение по умолчанию
не определено.
Модификация представлений
подобна фрагментации базовых
таблиц.
Пример 12.7.
CREATE VIEW PRCOUNT (UDATE,
COL) AS SELECT UDATE, COUNT(*)
FROM USP GROUP BY UDATE;
Это представление не модифицируемо
GROUP BY.
Пример 12.8.
CREATE VIEW MATEMUSP
AS SELECT * FROM USP
WHERE PNUM = 2003;
Это представление – модифицируемо.
Другой результат достигается на
представлении:
1)CREATE VIEW ONLY5
AS SELECT SNUM, MARK
FROM USP
WHERE MARK = 5;
2)INSERT INTO ONLY5
VALUES (3415, 4);
Это допустимая команда, в таблицу эти
значения будут вставлены, но на
экране не появятся.
Таким образом, в таблице могут
появляться данные не видимые
пользователю.
Для исключения таких моментов
используется предложение:
WITH CHECK OPTION.
Если его добавить к команде:
CREATE VIEW ONLY5
AS SELECT SNUM, MARK
FROM USP
WHERE MARK = 5
WITH CHECK OPTION;
То любое значение отличное от
указанных будет отключено.
Различия между модифицируемым
представлением и представлением
только для чтения существуют:
Первое - работает как базовое,
является в основном средством
скрытия
части
информации,
средством защиты;
Второе - позволяет получать целый
набор
всевозможных
запросов,
которые
можно
повторять
и
использовать для других запросов.
CREATE VIEW DATEMARK
AS SELECT SNUM, SFAM FROM
STUDENTS WHERE SNUM IN (
SELECT SNUM FROM USP WHERE
UDATE = 10.06.2002);
Это представление для чтения –
имеется подзапрос.
Что будет если пользователь решит
добавить запись:
INSERT INTO DATEMARK VALUES
(3415,’Котенко’);
Часть данных будет заполнена как NULL.
Проблема не решится если применить
WITH CHECK OPTION, так как
представление станет
модифицируемым и удаляемым, но без
вставки.
Пример 12.9.
1) CREATE VIEW STIPSTUD
AS SELECT SNUM, SFAM, STIP
FROM STUDENTS WHERE STIP>0
WITH CHECK OPTION;
2) То вставка будет не удачна.
INSERT INTO STIPSTUD
VALUES (3417, Решетник, 0.00);
3)однако в NEW1 она возможна.
CREATE VIEW NEW1
AS SELECT * FROM STIPSTUD;
Вставка выполнится.Это означает,
что любое корректное представление
модифицируемо. Даже если:
CREATE VIEW NEW1
AS SELECT * FROM STIPSTUD
WITH CHECK OPTION;
Стандарт
Input/Output**
Default
USA
ANSI
mon dd yyyy
hh:miAM (or PM)
mm/dd/yy
yy.mm.dd
German/British
/French/Italian
dd/mm/yy
dd-mm-yy
Использование CAST:
CAST ( expression AS data_type )
Использование CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
-- Use CAST.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO
-- Use CONVERT.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles WHERE CONVERT(char(20), ytd_sales) LIKE
'3%'
GO
Лекция № 11 Views – Представления
Представления.
Представление VIEW – это именованная
виртуальная
таблица,
содержание
которой выбирается из других таблиц с
помощью запросов.
При изменении значений в таблицах
автоматически
меняются
значения
представления.
Наличие имени у такой таблицы
позволяет пользователю выполнять с
ней операции аналогичные операциям с
базовыми таблицами.
Рассмотрим таблицы, относящиеся к
базовым, т.е. таким, которые содержат
данные и постоянно находятся на
устройствах
хранения
информации.
Представления по сравнению с ними
являются более гибкими средствами.
Когда СУБД отыскивает в команде
ссылку
на
представление,
она
отыскивает
его
определение,
хранящееся в БД.
После этого происходит преобразование
пользовательской
команды
в
её
эквивалент с учетом запроса. У
пользователя возникает впечатление,
что он работает с настоящей реально
существующей таблицей.
СУБД
имеет
две
возможности
реализации представления:
если определение представления
простое, то система формирует каждую
запись по мере необходимости;
- если представление сложное, СУБД
сначала выполняет материализацию
представления,
т.е.
сохраняет
информацию, из
которой состоит
представление во временной таблице.
Затем
система
выполняет
пользовательские
команды
и
формирует
её
результаты,
после
временная таблица удаляется.
CREATE VIEW <Name of view >
[(name of attributes),…] AS
<SELECT …>;
Пример 12.1. Создать представление о
студентах, получающих стипендию в
размере 25.50.
CREATE VIEW STIP25_50
AS SELECT * FROM STUDENTS
WHERE STIP=25.50;
Теперь в БД существует представление
STIP25_50. Это такая же таблица, как и
остальные. С ней можно выполнять
запросы, изменения, вставки как с
другими таблицами. При выполнении
запроса к ней:
SELECT * FROM STIP25_50;
будет получена таблица.
SNUM
3412
SFAM
Поляков
SNAME
SFATH
STIP
Анатолий Алексеевич 25,5
0
3416 Нагорный Евгений Васильевич 25,5
0
Если к ней обратиться с запросом с предикатом
SELECT * FROM STIP25_50 WHERE SFAM<’П’;
То будет получен результат:
SNUM
SFAM
3416 Нагорный
SNAME
Евгений
SFATH
STIP
Васильевич 25,5
0
При создании
представлений можно
часть информации
скрыть.
Пример 12.2. Создать представление
о студентах без указания стипендии.
CREATE VIEW STIPOFF AS SELECT
SNUM, SFAM, SNAME, SFATH
FROM STUDENTS;
При выполнении запроса к ней,
будет получена таблица :
SNUM
SFAM
SNAME
3412
Поляков Анатолий
3413
Старова
3414
3415
3416
Любовь
Владими
Гриценко
р
Котенко Анатолий
Нагорны
Евгений
й
SFATH
Алексееви
ч
Михайловн
а
Николаеви
ч
Николаеви
ч
Васильеви
ч
Представление теперь может
изменяться также как и
таблица, фактически же
команда направлена к таблице
STUDENTS.
Пример 12.3. Изменить у студента с
номером студенческого билета 3415
имя.
UPDATE STIPOFF SET SNAME =’
Василий’ WHERE SNUM=3415;
UPDATE STUDENTS SET SNAME =’
Василий’ WHERE SNUM=3415;
Но команда
UPDATE STIPOFF SET STIP =100
WHERE SNUM=3415;
будет отвергнута, так как поле STIP в
представлении STIPOFF отсутствует.
Существуют ограничения на
модификацию представлений.
Чаще всего в представлении используются
те же имена столбцов, что и в базовых
таблицах.
При
использовании
объединения может быть конфликт имен.
Допустимо указание других имен в
представлении в скобках после имени.
Пример 12.4.
CREATE VIEW STIPCOUNT(STIP, NUM) AS
SELECT STIP, COUNT(*) FROM STUDENTS
GROUP BY STIP;
Сделаем запрос к представлению: показать
все данные о стипендиях, которые получают 2
и более человек.
SELECT * FROM STIPCOUNT WHERE NUM>=2;
Но не допускается функция в предикате
WHERE:
SELECT
STIP, COUNT(*) FROM STUDENTS
WHERE COUNT(*) >= 2 GROUP BY STIP;
Правильная команда:
SELECT STIP, COUNT(*) as NUM
FROM
STUDENTS GROUP BY STIP
HAVING
STIP
NUM
COUNT(*)>=2;
0,00
2
25,50
2
В
SQL
существует
понятие
групповых
представлений, т.е. имеющих предложение GROUP
BY или основанных на других групповых
представлениях.
CREATE VIEW STIPCOUNT (STIP, NUM)
AS SELECT STIP, COUNT(*)
FROM
STUDENTS GROUP BY STIP;
STIP
0,00
17,00
25,50
NUM
2
1
2
Представления могут
основываться на двух и более
таблицах.
Пример 12.5.Создать представление о
студентах и их оценках.
CREATE VIEW STUDMARK AS SELECT
C.UNUM, A.SFAM, B.PNAME, C.MARK
FROM STUDENTS A, PREDMET B, USP C
WHERE
A.SNUM=C.SNUM
AND
B.PNUM=C.PNUM;
После этого легче ориентироваться в
оценках:
SELECT * FROM STUDMARK;
UNUM
SFAM
PNAME
MARK
1001
Поляков
Физика
5
1002
Старова
Математика
4
1003
Гриценко
Экономика
3
1004
Поляков
Математика
4
1005
Нагорный
Философия
5
1006
Гриценко
Физика
2
1007
Поляков
Экономика
4
Допускается
соединение
представления
с
базовыми
таблицами:
SELECT
SFAM, PNAME, MARK,
UDATE
FROM STUDMARK A, USP B
WHERE A.SFAM= ‘Поляков’
AND A.UNUM=B.UNUM;
Результат работы запроса:
SFAM
PNAME
MARK UDATE
10.06.200
Поляков
Физика
5
2
Математик
12.06.200
Поляков
4
а
2
13.06.200
Поляков Экономика
4
2
Представления допускают
соотнесенные подзапросы.
Пример12.6.Пусть в таблице USP
UNUM
1001
1002
1003
1004
1005
1006
1007
SNUM
3412
3413
3414
3412
3416
3414
3412
PNUM
2001
2003
2005
2003
2004
2001
2005
UDATE
10.06.2002
10.06.2002
11.06.2002
12.06.2002
12.06.2002
12.06.2002
13.06.2002
MARK
5
4
3
4
5
2
4
Создадим представление об оценках
превышающих среднюю.
1)CREATE VIEW AVGMARK AS
SELECT * FROM USP A WHERE
MARK>(SELECT AVG(MARK) FROM
USP B WHERE B.PNUM= A.PNUM);
2)SELECT * FROM AVGMARK;
UNUM
1001
1007
SNUM
3412
3412
PNUM
2001
2005
UDATE
10.06.2002
13.06.2002
MARK
5
4
Из
этих
примеров
следует,
что
представления значительно облегчают
работу с данными. Однако они являются
чаще всего объектами доступными для
чтения.
Существуют
ограничения
на
построения представлений:
1) в них не допускаются объединения
UNION запросов;
2) не допустимо упорядочение ORDER
BY, так как в базовых таблицах
записи не упорядочены.
Для удаления представлений
используется команда
DROP VIEW <Name of view>;
Для удаления представления не требуется
удалять все данные, потому что реально
они в нем не содержатся.
DROP VIEW AVGMARK;
Так как представления состоят из
результатов
запросов, то для их
модификации
должны
быть
модифицированы данные из базовых
таблиц. Но модификация не должна
воздействовать на запрос, она
воздействует на значения в таблице.
Рассмотрим критерии, по которым мы
определяем,
является
ли
представление модифицируемым:
1) представление должно основываться
только на одной таблице;
2) оно должно содержать первичный
ключ этой таблицы;
3)
представление не должно иметь
полей - агрегатных функций;
4) представление не должно использовать
DISTINCT;
5) представление не должно использовать
GROUP BY, HAVING;
6) представление не должно использовать
подзапросы;
7) представление не должно использовать
константы, строки, выражения среди полей
вывода;
8)
для команды INSERT оно может
содержать любые поля базовой таблицы,
для которой имеются ограничения NOT
NULL, если другое значение по умолчанию
не определено.
Модификация представлений
подобна фрагментации базовых
таблиц.
Пример 12.7.
CREATE VIEW PRCOUNT (UDATE,
COL) AS SELECT UDATE, COUNT(*)
FROM USP GROUP BY UDATE;
Это представление не модифицируемо
GROUP BY.
Пример 12.8.
CREATE VIEW MATEMUSP
AS SELECT * FROM USP
WHERE PNUM = 2003;
Это представление – модифицируемо.
Другой результат достигается на
представлении:
1)CREATE VIEW ONLY5
AS SELECT SNUM, MARK
FROM USP
WHERE MARK = 5;
2)INSERT INTO ONLY5
VALUES (3415, 4);
Это допустимая команда, в таблицу эти
значения будут вставлены, но на
экране не появятся.
Таким образом, в таблице могут
появляться данные не видимые
пользователю.
Для исключения таких моментов
используется предложение:
WITH CHECK OPTION.
Если его добавить к команде:
CREATE VIEW ONLY5
AS SELECT SNUM, MARK
FROM USP
WHERE MARK = 5
WITH CHECK OPTION;
То любое значение отличное от
указанных будет исключено.
Различия между модифицируемым
представлением и представлением
только для чтения существуют:
Первое - работает как базовое,
является в основном средством
скрытия
части
информации,
средством защиты;
Второе - позволяет получать целый
набор
всевозможных
запросов,
которые
можно
повторять
и
использовать для других запросов.
CREATE VIEW DATEMARK
AS SELECT SNUM, SFAM FROM
STUDENTS WHERE SNUM IN (
SELECT SNUM FROM USP WHERE
UDATE = 10.06.2002);
Это представление для чтения –
имеется подзапрос.
Что будет если пользователь решит
добавить запись:
INSERT INTO DATEMARK VALUES
(3415,’Котенко’);
Часть данных будет заполнена как NULL.
Проблема не решится если применить
WITH CHECK OPTION, так как
представление станет
модифицируемым и удаляемым, но без
вставки.
Пример 12.9.
1) CREATE VIEW STIPSTUD
AS SELECT SNUM, SFAM, STIP
FROM STUDENTS WHERE STIP>0
WITH CHECK OPTION;
2) То вставка будет не удачна.
INSERT INTO STIPSTUD
VALUES (3417, Решетник, 0.00);
3)однако в NEW1 она возможна.
CREATE VIEW NEW1
AS SELECT * FROM STIPSTUD;
Стандарт
Input/Output**
Default
USA
ANSI
mon dd yyyy
hh:miAM (or PM)
mm/dd/yy
yy.mm.dd
German/British
/French/Italian
dd/mm/yy
dd-mm-yy
Использование CAST:
CAST ( expression AS data_type )
Использование CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
-- Use CAST.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO
-- Use CONVERT.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles WHERE CONVERT(char(20), ytd_sales) LIKE
'3%'
GO
Временные таблицы
Временная таблица создается командой create
table,
также
как
и
обычная
таблица,
признаком временности служит символ #
перед именем ( такая таблица доступна только
в
текущей
подпрограмме,
таблица
с
префиксом ## всюду). Временные таблицы
автоматически уничтожаются при завершении
текущей сессии работы с сервером, в
остальном ничем не отличаются от обычных
таблиц.
Лекция № 12 Транзакции,триггеры
Транзакции
•Транзакция это последовательность операций, объединенных в
единый логический рабочий модуль.
•Механизм транзакций позволяет контролировать выполнение
операций в этом логическом модуле и производить откаты
(отмену уже сделанной операции), если этого требует логика
приложения.
•Рабочий
модуль
должен
соответствовать
основным
требованиям к транзакциям, сокращенно называемые ACID
(Atomicity, Consistency, Isolation, Durability)
•Atomicity (атомарность)Логика приложения должна
предполагать, что должны быть проделаны либо
все изменения данных, входящие в транзакцию
либо ни одного;
•Consistensy(постоянство) После завершения транзакции не
должна быть нарушена целостность данных,система не может
оказаться в неком промежуточном состоянии;
•Isolation (изолированность)Изменения,
производимые в рамках одной транзакции,
изолируются от других (конкурирующих)
транзакций;(4-уровня изоляции:0-двум процессам
запрещается изменять одни и те же данные; 1-запрещено
считывание пока идут изменения; 2-в промежутках чтения в
одной TRAN не допускаются изменения в другой; 3запрещаются в это время вставки и удаления)
•Durability (устойчивость)После завершения
транзакции все сделанные изменения будут
сделаны в любом случае, даже если во время
этого процесса произошел сбой системы или
потеря связи – после восстановления
работоспособности SQL сервер обращается к
Запуск транзакции
SQL
сервер
позволяет
запустить
явную,
автоматически совершаемую или неявную
транзакцию
• Explicit
(явная)
транзакция
предваряется
выражением BEGIN TRANSACTION
•
Autocommit
(автоматически
совершаемая)
транзакция – режим, в котором работает SQL
сервер по умолчанию, каждая отдельная
инструкция T-SQL совершается (изменения в
данные
вносятся
физически
)
после
отрабатывания
инструкции.
Не
нужно
указывать никаких ключевых слов, чтобы
начать такую транзакцию
• Implicit (неявная) транзакция. Такой режим
транзакции устанавливается инструкцией SET
IMPLICIT_TRANSACTIONS ON, следующая за
этой
инструкцией
конструкция
T-SQL
автоматически начинает новую транзакцию.
Когда эта транзакция завершается, следующее
выражение начинает новую транзакцию.
Завершение транзакции.
•Для завершения транзакции используется
конструкция COMMIT
•Если все прошло успешно, конструкция COMMIT
гарантирует, что все изменения будут сделаны на
физическом уровне.
•Если же во время выполнения транзакции
произошла ошибка, используется конструкция
ROLLBACK – данные возвращаются к
первоначальному состоянию, или к некоторой точке
сохранения, системные ресурсы освобождаются.
Синтаксис
•SAVE TRAN [ SACTION ] { savepoint_name |
@savepoint_variable } – объявить savepoint
•BEGIN TRAN [ SACTION ] [ transaction_name |
@tran_name_variable
[ WITH MARK [ 'description' ] ] ]
•ROLLBACK [ TRAN [ SACTION ]
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ] ]
•COMMIT [ TRAN [ SACTION ] [ transaction_name |
@tran_name_variable ] ]
Примеры:
CREATE TABLE ImplicitTran (Cola int PRIMARY KEY,
Colb char(3) NOT NULL)
SET IMPLICIT_TRANSACTIONS ON
/* Первая implicit транзакция начнется конструкцией INSERT*/
INSERT INTO ImplicitTran VALUES (1, 'aaa')
INSERT INTO ImplicitTran VALUES (2, 'bbb')
/* Commit first transaction */
COMMIT TRANSACTION
/* Вторая implicit транзакция начнется конструкцией SELECT */
SELECT COUNT(*) FROM ImplicitTran
INSERT INTO ImplicitTran VALUES (3, 'ccc')
SELECT * FROM ImplicitTran
/* Commit second transaction */
COMMIT TRANSACTION
SET IMPLICIT_TRANSACTIONS OFF
В autocommit режиме в случае возникновения
ошибки компиляции SQL сервер делает rollback
всему пакету инструкций. При возникновении
runtime
error
откат
не
производится,
не
выполняется
лишь
инструкция,
в
которой
произошел runtime error:
--example 1(autocommit ошибка компиляции)
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb
CHAR(3))
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUSE (3, 'ccc') /* Syntax error */
SELECT * FROM TestBatch /* Returns no rows */
--example 2(autocommit ошибка runtime error)
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUES (1, 'ccc') /* Duplicate key error */
SELECT * FROM TestBatch /* Returns rows 1 and 2 */
В SQL Server контроль имени объекта производится в
(во время выполнения) execution time. Поэтому в
следующем примере будет runtime error, а не compile
error:
USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY,
Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBch VALUES (3, 'ccc') /* Table name error */
GO
SELECT * FROM TestBatch /* Returns rows 1 and 2 */
GO
Обработка исключений
•@@ERROR возвращает номер ошибки, возникшей при
выполнении предыдущего SQL-выражения или 0, если
ошибок не было. Сообщения, соответствующие каждому
коду ошибки хранятся в таблице sysmessages
•@@ROWCOUNT возвращает количество строк, затронутых
предыдущим запросом
•RAISEERROR – генерирует исключение
RAISERROR ( { msg_id | msg_str } { , severity , state }
[ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
severity - тип проблемы (использовать 11-16)
state – произвольное число от 1 до 127
Схема обработки ошибок в explicit транзакции:
begin tran
Update Authors
set contact=1 where au_id=1000
Save transaction Authors_done
Update AU_titles
set au_num=au_num+3 where au_id=1000
if @@error <> 0 or @@rowcount >1
begin
raiserror('Couldn''t update ',16,1)
print ‘error_update’
rollback tran Authors_done
return
end
commit tran
Print ‘tran’’s ok!’
Триггеры
Триггер - особая разновидность хранимой
процедуры, которая выполняется в тех случаях,
когда пользователь пытается добавить, удалить
или модифицировать данные. Триггеры часто
используются для реализации бизнес-логики и
проверки целостности данных. В триггере
определяется тип запроса (INSERT, DELETE или
UPDATE) и таблица, с которыми он связан.
Во время выполнения триггера создаются две
специальные таблицы - INSERTED и DELETED.
В них находятся записи, соответственно
добавляемые или удаляемые запросами в
таблице, для которой создан триггер.
Синтаксис
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator }
updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
}]
sql_statement [ ...n ]
}
}
•FOR (или AFTER) и INSTEAD OF устанавливают тип
триггера. FOR(AFTER) – все операции в триггере
выполняются после того, как отработал запрос, на который
наложен триггер. INSTEAD OF – триггер выполняется
вместо запроса (таблицы deleted и inserted создаются и
заполняются, однако модификация данных в первичной
таблице не производится).
•WITH APPEND – для совместимости с предыдущими
версиями, доступна только если MS SQL сервер работает в
режиме совместимости с предыдущими версиями (в
предыдущих версиях нельзя было в явном виде создать
несколько однотипных триггеров на одной и той же
таблице)
•IF UPDATE(COLUMN NAME) – true, если колонка была
затронута исходным выражением
insert into…
Последовательность работы триггеров
начинается транзакция
instead of?
yes
заполняются таблицы
inserted и deleted
no
check constraints
yes
instead of
trigger
выполняется
if exists…
select…
insert…
…
…
триггер внес изменения в исходную таблицу
log statement
no
заполняются таблицы
inserted и deleted
вызываются
for/after триггеры
commit tran
All done!
Пример 1
CREATE TABLE my_table (a int NULL, b int NULL)
GO
ALTER TRIGGER my_trig ON my_table
FOR INSERT
AS
PRINT '1'
GO
ALTER TRIGGER my_trig1 ON my_table
FOR INSERT
AS
PRINT '2'
--ROLLBACK TRAN
GO
insert into my_table(a) values(1)
insert into my_table(a,b) values(1,2)
Пример 2
CREATE TRIGGER tri_FirmsInsert ON Firms
FOR INSERT
AS
/* занесем Departments из init-таблицы */
INSERT INTO Deps (F__ID, Dep_Name, Dep_Parent)
SELECT I.F_ID, DI.DI_Name, DI.DI_Parent
FROM INSERTED I, DepsInit DI
GO
CREATE TRIGGER tri_FirmsUpdate ON Firms
FOR Update
AS
UPDATE T
SET T.F_ID = I.F_ID
FROM Deps T, INSERTED I, DELETED D
WHERE T.F_ID = D.F_ID
AND I.F_Name = D.F_Name
AND I.F_ID <> D.F_ID
GO
CREATE TRIGGER tri_FirmsDelete ON Firms
FOR Delete
AS
DELETE T FROM Deps T, DELETED D
WHERE T.F_ID = D.F_ID
GO
CREATE TRIGGER CustomerHasOrders
ON Customers
FOR DELETE
Пример 3
AS
IF EXISTS
(
SELECT * FROM DELETED D
INNER JOIN ORDERS O
ON D.CustomerID = O.CustomerID
)
BEGIN
failed’,16,1)
RAISERROR(‘Customer has order history, delete
ROLLBACK TRAN
END
•Оператор return прекращает выполнение триггера
•Триггер может быть рекурсивным (по умолчанию это
отключено в настройках)
•Глубина рекурсии не более 32, для определения глубины
рекурсии ( nested level) можно использовать такую
конструкцию:
•trigger_nestlevel(object_id( <Название триггера> ) )
•Можно настроить каскадный вызов триггеров – при
выполнении каких-то операций над таблицами базы внутри
триггера будут вызываться триггеры, соответствующие этим
операциям.
•Используя INSTEAD OF триггеры можно изменять
информацию в таблицах через VIEW
Обновление данных триггером через view
CREATE VIEW dbo.FileTable
AS
SELECT
FT_ID = 'up_' + CAST ( U.Up_ID AS varchar ),
Native_ID = U.Up_ID,
FROM Uploads U
UNION
SELECT
FT_ID = 'lib_' + CAST ( Lib_ID AS varchar ),
Native_ID = L.Lib_ID,
FROM Library L
Обновление данных триггером через view
CREATE TRIGGER tri_FileTableUpdate ON FileTable
INSTEAD OF DELETE
AS
BEGIN
IF ( SELECT COUNT(*) FROM DELETED ) > 0
BEGIN
DELETE U FROM Uploads U, DELETED D
WHERE D.Ft_ID = 'up_' + CAST( U.Up_ID AS varchar )
DELETE L FROM Library L, DELETED D
WHERE 'lib_' + CAST( L.Lib_ID AS varchar ) = D.Ft_ID
END
END
Пример:
CREATE TABLE NestedTest (
NT_ID int NOT NULL ,
NT_Name varchar (50) NOT NULL ,
NT_Parent int NULL ,
CONSTRAINT [PK_NestedTest] PRIMARY KEY CLUSTERED
(
[NT_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO NestedTest(NT_ID,NT_Name)
VALUES (1,'Obj1')
DECLARE @i int
SET @i = 1
WHILE ( @i <= 50 )
BEGIN
INSERT INTO NestedTest(NT_ID,NT_Name,NT_Parent)
VALUES (@i+1, 'Obj' + CAST(@i AS varchar), @i )
SET @i = @i + 1
END
GO
ALTER TRIGGER tri_NestedTestDelete ON NestedTest
FOR DELETE
AS
IF @@ROWCOUNT = 0
RETURN
PRINT trigger_nestlevel( object_id( 'tri_NestedTestDelete' ) )
IF ( trigger_nestlevel( object_id( 'tri_NestedTestDelete' ) ) > 31 )
RETURN
DELETE T FROM NestedTest T, DELETED D
WHERE D.NT_ID = T.NT_Parent
GO
DELETE FROM NestedTest WHERE NT_ID = 1
SELECT * FROM NestedTest
User Defined Functions (UDFs)
•Могут возвращать скалярный результат так, как это делает,
например, функция getdate()
•Результатом выполнения функции может быть таблица, в этом
случае мы можем пользоваться этой таблицей как VIEW, при это
мы имеем возможность передавать параметры (view лишены
этого)
•При разработке SQL Server 2005 специалисты Microsoft
вкдючили поддержку для независимых от языка UDF (например,
UDF, записанное в VBScript). В 2000 версии, в силу
существующих программных ограничений, UDFs пока можно
создавать лишь на языке SQL (T-SQL).
Синтаксис
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ]
])
RETURNS scalar_return_data_type
[ WITH < function_option> [ [,] ...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ]
])
RETURNS TABLE
[ WITH < function_option > [ [,] ...n ] ]
[ AS ]
RETURN [ ( ] select-stmt [ ) ]
CREATE FUNCTION CubicVolume
-- Входные размеры в сантиметрах.
Пример 1
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) - Cubic centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
SELECT CubicVolume(1,2,3)
В качестве параметров естественно могут
выступать названия полей, а не только
константы или переменные
Хранимые процедуры
•"трехзвенная архитектура" - имеется хранилище данных (1е звено), имеется сервер приложений (2-е звено), который
выбирает из этого хранилища данные и определенным
образом эти данные обрабатывает и после обработки
конечный результат уже посылает на терминал клиента (3-е
звено).
•"клиент-сервер" - имеется хранилище данных (сервер) и
клиент, который с этого сервера выбирает данные с помощью
определенного языка запросов (SQL) (Устаревший взгляд,
возвращаюший нас во времена СУБД типа FoxPRO со
встроенной поддержкой sql-запросов).
•Более современное описание технологии "клиент-сервер"
выглядит так:
Имеется хранилище данных (1-е звено) и клиент (3-е звено),
который с этого сервера выбирает данные с помощью
определенного языка запросов (SQL), но помимо этого есть сервер
приложений (2-е звено), уже встроенный в базу данных, с
помощью которого можно обрабатывать данные любыми
известными реляционной алгебре способами и уже после этого
передавать конечный результат на клиента.
•Хранимые процедуры как раз и выполняют роль сервера
приложений. С их помощью с данными можно делать все. Для
этого достаточно вызвать заранее написанный код в виде
хранимой процедуры со стороны клиента.
Важная область применения хранимых процедур ограничение доступа к базе данных. Например, можно
запретить для пользователей доступ на добавление
записей в таблицу, и выполнять добавление записей с
помощью специальной хранимой процедуры, доступ к
которой открыт для всех.
Синтаксис
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE ,
ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
;number – необязательный параметр, используется для
создания группы процедур с одинаковым именем,
отличающимися только указанными номерами. В дальнейшем
вся эта группа процедур может быть удалена одной командой
drop procedure procgrname. Например процедуры orderproc;1 и
orderproc;2 удаляются командой drop procedure orderproc
•@parameter - параметр, подаваемый на вход процедуры (их
может быть несколько) Описывается как переменная.
•data_type - тип параметра
•VARYING используется для спецификации изменяемого result
set’а, который может быть помещен в выходной параметр
процедуры (varying применяется только с параметрами типа
CURSOR)
•default – задает значение для параметра по умолчанию, если
значение по умолчанию задано таким образом передавать этот
параметр при вызове процедуры необязательно
•OUTPUT – указывает, что параметр используется для
возвращения значений.
•RECOMPILE – план запросов, вызываемых в процедуре не
кэшируется.
•По умолчанию все параметры nullable
•Число параметров не должно превышать 2100
•Максимальный размер хранимой процедуры – 128Мб
•Можно создать временную хранимую процедуру (имя
начинается с # для локальной и с ## для глобальной)
•Хранимые процедуры могут вызывать друг друга
(nesting), для определения уровня вложенности вызовов
имеется функция @@NESTLEVEL. Если уровень
вложенности превысил максимальный будет сделан
rollback всем вызовам
•Хранимая процедура вызывается конструкцией
exec[ute]
Внутри хранимой процедуры имена объектов,
использующиеся в определенных конструкциях должны
обязательно начинаться с имени object owner’а, если процедура
вызывается несколькими пользователями. Список этих
выражений:
•ALTER TABLE
•CREATE INDEX
•CREATE TABLE
•All DBCC statements (консольные команды базы данных)
•DROP TABLE
•DROP INDEX
•TRUNCATE TABLE (удаляет все содержимое таблицы)
•UPDATE STATISTICS (statistic – гистограмма частот, которая может
быть создана для определенных колонок таблицы или представления.
Используется оптимизатором запросов)
Примеры
Создадим таблицы, которые будем в дальнейшем
использовать в наших процедурах и заполним их:
CREATE TABLE Master1
(
Master1ID int IDENTITY (1,1) NOT NULL,
Detail1ID int NULL,
Detail2ID int NULL,
Name varchar(200),
CONSTRAINT PK_Master1 PRIMARY KEY CLUSTERED
(Master1ID)
)
GO
CREATE TABLE Detail1
(
Detail1ID int IDENTITY (1,1) NOT NULL,
Name varchar(200),
CONSTRAINT PK_Detail1 PRIMARY KEY CLUSTERED
(Detail1ID)
)
GO
CREATE TABLE Detail2
(
Detail2ID int IDENTITY (1,1) NOT NULL,
Name varchar(200),
CONSTRAINT PK_Detail2 PRIMARY KEY CLUSTERED
(Detail2ID)
)
GO
insert Detail1 (Name) values('Рабочий')
insert Detail1 (Name) values('Инженер')
insert Detail1 (Name) values('Дворник')
insert Detail1 (Name) values('Программист')
GO
insert Detail2 (Name) values('высшее')
insert Detail2 (Name) values('среднее')
insert Detail2 (Name) values('неполное высшее')
insert Detail2 (Name) values('кандидат наук')
GO
insert Master1 (Detail1ID,Detail2ID,Name) values(1,1,'Иванов')
insert Master1 (Detail1ID,Detail2ID,Name) values(2,2,'Петров')
insert Master1 (Detail1ID,Detail2ID,Name) values(1,2,'Сидоров')
insert Master1 (Detail1ID,Detail2ID,Name) values(4,3,'Лаврененко')
insert Master1 (Detail1ID,Detail2ID,Name) values(null,1,'Кошкин')
insert Master1 (Detail1ID,Detail2ID,Name) values(3,null,'Самойлов')
GO
Процедура, возвращающая набор данных:
CREATE PROCEDURE msp_List1 @ID int
AS
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a
left join Detail1 b on b.Detail1ID=a.Detail1ID
left join Detail2 c on c.Detail2ID=a.Detail2ID
where a.Master1ID=@ID
GO
Выполнение:
exec msp_List1 4
GO
Результат:
Master1ID Name
Name
Name
----------- -------------------- -------------------- -------------------4
Лаврененко
Программист
неполное высшее
(1 row(s) affected)
Для использования набора данных, возвращаемого хранимой процедурой для
обработки на сервере (в других процедурах и т.п.) можно вставить его во
временную таблицу и уже с ней работать:
CREATE TABLE #tmp (Master1ID int, Name1 varchar(200), Name2
varchar(200), Name3 varchar(200))
INSERT #tmp exec msp_List1 1
select * from #tmp
GO
Результат:
Master1ID Name1
Name2
Name3
----------- -------------------- -------------------- -------------------1
Иванов
Рабочий
высшее
(1 row(s) affected)
Процедура, возвращающая данные:
CREATE PROCEDURE msp_List2 @ID int, @Name varchar(200) OUTPUT
AS
select @Name=Name
from Master1
where Master1ID=@ID
GO
Выполнение:
declare @s varchar(200)
exec msp_List2 5, @s OUTPUT
print @s
GO
Результат:
Кошкин
Ничего не возвращающая процедура:
СREATE PROCEDURE sp_VendorsImport @file varchar(200)
AS
--begin of procedure
BEGIN TRAN
CREATE TABLE #Import (
Company varchar (50) NULL ,
[Name] varchar (50) NULL ,
Email varchar (30) NULL,
)
if @@error <> 0
begin
raiserror('Could not create import table',16,1)
goto error_end
end
DECLARE @bulk_insert nvarchar(2000)
SET @bulk_insert='BULK INSERT #Import FROM ''' + @file +
''' '+
'WITH ('+
'CODEPAGE=''ACP'','+
'FIELDTERMINATOR=''\t'','+
'KEEPNULLS,'+
'ROWTERMINATOR=''\n'','+
'FIRSTROW=2'+
''+
')'
EXEC sp_executesql @stmt=@bulk_insert
if @@error <> 0 or @@rowcount = 0
begin
raiserror('Could not parse vendors file',16,2)
goto error_end
end
COMMIT TRAN
RETURN
error_end:
ROLLBACK TRAN
--end of procedure
GO
Пример 2
CREATE FUNCTION ReturnedTable( @ID int )
RETURNS TABLE
AS
RETURN(
SELECT @ID as 'ID', 'Test' as 'Name'
)
SELECT * FROM ReturnedTable(1)
Лекция № 13 Параллельность и блокировки
Параллельность и блокировки
SQL-сервер позволяет одновременную работу большого числа
пользователей, каждый из которых запускает свои запросы в
параллельных сессиях. Это приводит к возможности
одновременного доступа нескольких сессий к одним и тем же
данным (доступ может быть как чтением, так и изменением
данных). Одновременная работа таких конкурирующих
запросов может привести к следующим проблемам:
•«проблема потери последнего изменения» (lost
update problem) или проблема «грязной» записи
•проблема «грязного чтения» (dirty read)
•проблема «повторного чтения» (repeatable read)
•проблема «фантомов» (phantom)
Проблема «грязной» записи заключается в
том, что при одновременном выполнении
транзакций, в которых производится изменение
данных, невозможно сказать заранее, какое
конечное значение примут данные после
фиксирования обеих транзакций. В случае
«грязной» записи только
одна из всех
параллельно выполняющихся транзакций будет
работать
с
действительными
данными,
остальные – нет. Другими словами, хотя данные
и будут находиться в согласованном состоянии,
логическая их целостность будет нарушена.
Проблема «грязного чтения» возникает, когда
одна транзакция пытается прочитать данные, с
которыми работает другая параллельная транзакция.
В таком случае временные, неподтвержденные
данные могут не удовлетворять ограничениям
целостности или правилам. И, хотя к моменту
фиксации транзакции они могут быть приведены в
«порядок»,
другая
транзакция
уже
может
воспользоваться этими неверными данными, что
приведет к нарушению ее работы.
Проблема повторного чтения состоит в том, что
между операциями чтения в одной транзакции
другие транзакции могут беспрепятственно вносить
любые изменения, так что повторное чтение тех же
данные приведет к другому результату.
Проблема фантомов возникает возникает, когда
выборка данных сделанная в одной транзакции
изменяется другой транзакцией. Например мы
устанавливаем для поля F1 значение 5000 для всех
записей таблицы, затем накладываем constraint,
ограничивающий поле F1 сверху числом 5001, а в
промежуток между этими 2мя операциями другая
транзакция вносит в таблицу запись с F1 = 5500
•Для разрешения этих проблем необходимо изолировать
транзакции друг от друга.
•Для реализации различных уровней изоляции в SQL сервере
используются блокировки (LOCKs)
•Блокировки – чрезвычайно важный и неотъемлемый
механизм функционирования сервера. Они применяются для
каждого запроса на чтение или обновления данных, а также
во многих других случаях (например, при создании новой
сессии). Работой с блокировками занимается специальный
модуль SQL Server’а – менеджер блокировок (Lock Manager).
Уровни
определения
изоляции
транзакций(
Каждый уровень включает в себя предыдущий с
предъявлением более жестких требований к изоляции)
•No trashing of data (запрещение «загрязнения»
данных). Запрещается изменение одних их тех же
данных двумя и более параллельными транзакциями.
Изменять данные может только одна транзакция, если
какая-то другая транзакция попытается сделать это,
она должна быть заблокирована до окончания работы
первой транзакции.
•No dirty read (запрещение «грязного» чтения). Если
данная
транзакция
изменяет
данные,
другим
транзакциям запрещается читать эти данные до тех
пор, пока первая транзакция не завершится.
•No nonrepeatable read (запрещение неповторяемого
чтения). Если данная транзакция читает данные,
запрещается изменять эти данные до тех пор, пока
первая транзакция не завершит работу. При этом
другие транзакции могут получать доступ на чтение
данных.
•No phantom (запрещение фантомов). Если данная
транзакция
производит
выборку
данных,
соответствующих
какому-либо
логическому
условию, другие транзакции не могут ни изменять
эти данные, ни вставлять новые данные, которые
удовлетворяют тому же логическому условию.
•Блокировки в MS SQL Server 2000 (– это механизм
реализации
требования
изолированности
транзакций.
•Существует три основных типа блокировок и
множество специфичных. Сервер устанавливает
блокировки
автоматически
в
зависимости
от
текущего уровня изоляции транзакции, однако при
желании вы можете изменить тип с помощью
специальных подсказок – хинтов.
•При открытии новой сессии по умолчанию
выбирается уровень изоляции READ COMMITTED.
•Вы можете изменить этот уровень для данного
соединения с помощью команды:
SET TRANSACTION ISOLATION LEVEL
Уровни
изоляции
Загрязнен Грязно Неповторяем
ие данных
е
ое чтение
чтение
Фантом
ы
READ UNCOMMITTED
-
+
+
+
READ COMMITTED
-
-
+
+
REPEATABLE READ
-
-
-
+
SERIALIZABLE
-
-
-
-
Блокировки применяются для защиты совместно используемых
ресурсов сервера. В качестве объектов блокировок могут
выступать следующие сущности:
•База данных (обозначается DB). При наложении блокировки
на базу данных блокируются все входящие в нее таблицы.
•Таблица (обозначается TAB). При наложении блокировки на
таблицу блокируются все экстенты данной таблицы, а также все
ее индексы.
•Экстент (обозначается EXT). При наложении блокировки на
экстент блокируются все страницы, входящие в данный экстент.
•Страница (обозначается PAG). При наложении блокировки на
страницу блокируются все строки данной страницы.
•Строка (обозначается RID).
•Диапазон индекса (обозначается KEY). Блокируются данные,
соответствующие диапазону индекса, на обновление, вставку и
удаление.
•SQL Server сам выбирает наиболее оптимальный объект для
блокировки, однако пользователь может изменить это
поведение с помощью тех же хинтов.
•При автоматическом определении объекта блокировки
сервер должен выбрать наиболее подходящий с точки зрения
производительности и параллельной работы пользователей.
• Чем меньше детализация блокировки (строка – самая
высокая степень детализации), тем ниже ее стоимость, но
ниже и возможность параллельной работы пользователей.
• Если выбирать минимальную степень детализации, запросы
на выборку и обновление данных будут исполняться очень
быстро, но другие пользователи при этом должны будут
ожидать завершения транзакции.
•Степень параллелизма можно увеличить путем повышения
уровня детализации, однако блокировка – вполне конкретный
ресурс SQL Server’а, для ее создания, поддержания и
удаления требуется время и память.
•SQL Server может принимать решение об уменьшении
степени детализации, когда количество блокированных
ресурсов
увеличивается.
Этот
процесс
называется
эскалацией блокировок.
•Вообще говоря, существует два метода управления
конкуренцией
для
обеспечения
параллельной
работы
множества
пользователей
–
оптимистический
и
пессимистический. SQL Server использует оптимистическую
конкуренцию только при использовании курсоров (cursors).
Для обычных запросов на выборку и обновление используется
пессимистическая конкуренция.
•Оптимистический метод управления характеризуется тем, что
вместо непосредственного чтения данных берется значение из
буфера. Никаких блокировок при этом не накладывается.
Другие транзакции могут спокойно читать или даже изменять
данные.
В
момент
фиксирования
транзакции
система
сравнивает предыдущее (заранее сохраненное) значение
данных с текущим. Если они совпадают, выполняются операции
блокировки, обновления и разблокировки данных. Если же
значения отличаются, то система генерирует ошибку и
откатывает транзакцию.
•Пессимистический метод. В этом случае сервер всегда
блокирует ресурсы в соответствии с текущим уровнем
изоляции.
Основные задачи менеджера блокировок:
•создание и установка блокировок;
•снятие блокировок;
•эскалация блокировок;
•определение совместимости блокировок;
•устранение взаимоблокировок (deadlocks)
•Когда пользователь делает запрос на обновление или
чтение данных, менеджер транзакций передает
управление менеджеру блокировок для того, чтобы
выяснить были ли блокированы запрашиваемые
ресурсы, и, если да, совместима ли запрашиваемая
блокировка с текущей.
• Если
блокировки
несовместимы,
выполнение
текущей транзакции откладывается до тех пор, пока
данные не будут разблокированы.
•Как только данные становятся доступны, менеджер
блокировок накладывает запрашиваемую блокировку,
и возвращает управление менеджеру транзакций.
Простые блокировки
•Разделяемая блокировка (Shared Lock), обозначается
латинской буквой S. Эта самый распространенный тип
блокировки, который используется при выполнении операции
чтения данных. Гарантируется что данные, на которые она
наложена, не будут изменены другой транзакцией. Однако
чтение данных возможно.
•Монопольная блокировка (Exclusive Lock), обозначается
латинской буквой X. Этот тип применяется при изменении
данных. Если на ресурс установлена монопольная блокировка,
гарантируется, что другие транзакции не могут не только
изменять данные, но даже читать их.
•Блокировка
обновления
(Update
Lock),
обозначается латинской буквой U. Эта блокировка
является промежуточной между разделяемой и
монопольной блокировкой.
•Так как монопольная блокировка не совместима ни с
одним видом других блокировок ее установка
приводит к полному блокированию ресурса.
•Если транзакция хочет обновить данные в какой-то
ближайший момент времени, но не сейчас, и, когда
этот момент придет, не хочет ожидать другой
транзакции,
она
может
запросить
блокировку
обновления.
•В этом случае другим транзакциям разрешается
устанавливать разделяемые блокировки, но не
позволяет устанавливать монопольные.
•Если данная транзакция установила на ресурс блокировку
обновления, никакая другая транзакция не сможет получить
на этот же ресурс монопольную блокировку или блокировку
обновления до тех пор, пока установившая блокировку
транзакция не будет завершена.
•Для просмотра текущих блокировок существует системная
хранимая функция sp_lock.
•Эта процедура возвращает данные о блокировках из
системной таблицы syslockinfo, которая находится в базе
данных master.
Пример:
create table test(i int, n varchar(20))
insert into test values(1,'alex')
insert into test values(2,'rosa')
insert into test values(3,'dima')
убедимся, что при чтении данных с уровнем изоляции ниже
REPEATABLE READ разделяемые блокировки снимаются сразу
же после извлечения данных:
print @@spid
begin tran select * from test
в другой сессии запустим:
sp_lock 63
Мы видим стандартную блокировку, которая создается для
каждого
соединения
с
базой
данных.
Никакой
дополнительной блокировки установлено не было.
В первой сессии зафиксируем транзакцию:
--print @@spid
--begin tran select * from test
сommit
Повторный вызов sp_lock приводит к тем же результатам. Это
подтверждает, что предыдущим запросом никаких блокировок не
устанавливалось. Теперь попробуем наложить блокировку
обновления. Делается это с помощью хинта updlock (хинты
подробно будут рассмотрены далее):
begin tran select * from test with (updlock)
spdi
dbid
ObjId
IndId
54
8
0
0
DB
54
8
1993058136
0
RID
54
8
1993058136
0
54
8
1993058136
54
8
54
8
Type
Resource
Mode
S
GRANT
1:29:2
U
GRANT
RID
1:29:0
U
GRANT
0
PAG
1:29
IU
GRANT
1993058136
0
TAB
IX
GRANT
1993058136
0
RID
U
GRANT
1:29:1
Как видно, на три строки была наложена блокировка обновления,
что означает невозможность обновления этих строк другими
транзакциями. Кроме этого, были наложены еще две блокировки,
которые относятся к типу блокировок намерения (intent locks) –
блокировка на страницу и на таблицу.
•Блокировки намерений всегда устанавливаются на таблицу или
страницу, но никогда – на строку.
•Блокировки намерений относятся к специальным типам
блокировок и предназначены для повышения производительности
работы менеджера блокировок.
•Предположим, некая транзакция пытается изменить какую-либо
строку в таблице test. Чтобы определить, что эту транзакцию
необходимо заблокировать, менеджеру транзакций (в отсутствие
блокировок намерения) пришлось бы сканировать всю таблицу
syslockinfo для проверки всех строк таблицы test. Чтобы избежать
этой неблагодарной работы, менеджер блокировок сразу
устанавливает на страницу и таблицу блокировку намерения
обновления (Intent Update) и монопольную блокировку намерения
(Intent Exclusive) соответственно, и проверяет уже только их.
Типы блокировок намерений
•Разделяемая блокировка намерений (обозначается
IS).
•Монопольная блокировка намерений (обозначается
IX).
•Разделяемо-монопольная
(обозначается SIX)
блокировка
намерений
Продолжим пример. Создадим новую сессию и
выполним следующий скрипт:
begin tran
insert into test values(4,'other')
pid
dbid
ObjId
IndId
54
8
0
0
DB
54
8
1993058136
0
RID
54
8
1993058136
0
54
8
1993058136
54
8
54
Type
Resource
Mode
S
GRANT
1:29:02
U
GRANT
RID
1:29:00
U
GRANT
0
PAG
1:29
IU
GRANT
1993058136
0
TAB
IX
GRANT
8
1993058136
0
RID
U
GRANT
55
8
0
0
DB
S
GRANT
55
8
1993058136
0
PAG
IX
GRANT
55
8
1993058136
0
TAB
IX
GRANT
55
8
1993058136
0
RID
X
GRANT
1:29:01
1:29
1:29:03
Как видно, предыдущие блокировки остались (так
как мы не зафиксировали транзакцию), и
добавились четыре новых: одна блокировка базы,
не имеющая никакого значения, две блокировки
намерений
(на
таблицу
и
страницу)
и
монопольная блокировка
на
новую
строку
(идентификатор 1:29:03).
Блокировки схемы данных
•Блокировка стабильности схемы (Schema Stability Lock),
обозначается Sch-S. Данный тип блокировки предназначен для
гарантии неизменности метаданных, но не самих данных. Т.е.
блокировка стабильности схемы – единственная из всех типов
блокировок, совместимых с монопольной блокировкой. В
основном она устанавливается при компиляции тела запроса
или хранимой процедуры, на это время запрещается вносить
изменения в схему данных, однако разрешается устанавливать
любой тип блокировок на сами данные, с которыми будет
работать компилируемый запрос.
•Блокировка изменения схемы
обозначается Sch-M. Данный тип
каким другим типом, ни с самим
внесения изменений в схему
завершения транзакции.
(Schema Modification Lock),
блокировки не совместим ни с
собой. Устанавливается после
данных и снимается после
Блокировки могут преобразовываться друг в друга
по следующей схеме
Тип
IS
S
U IX SIX X
+ + + + + - +
IS
Sch-S Sch-M BU
-
S
+ + + - + - +
-
-
U
+ + - - -
- +
-
-
IX
+ + - + -
- +
-
-
SIX
+ + - - -
- +
-
-
X
- - - - -
- +
-
-
Sch-S + + + + + + +
-
+
Sch-M - - - - -
- -
-
-
BU
- +
-
+
- - - - -
Хинты и установка уровней изоляции
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED
|
READ UNCOMMITTED
REPEATABLE READ
{
|
|
SERIALIZABLE
}
•READ UNCOMMITTED – устанавливает уровень изоляции
транзакций, на котором решается проблема «грязной» записи;
•READ COMMITTED – устанавливает уровень изоляции, на
котором решается проблема «грязного» чтения;
•REPEATABLE READ – устанавливает уровень изоляции, на
котором решается проблема неповторяемого чтения;
•SERIALIZABLE – устанавливает уровень изоляции, на котором
решается проблема чтения фантомов.
•Установка какого-либо значения остается действительной
до конца сессии, или до тех пор, пока пользователь явно
не изменит уровень изоляции транзакций.
Теперь рассмотрим, каким образом управлять уровнем изоляции
транзакций на уровне отдельных команд. Вот синтаксис раздела
FROM, который относится к нашей теме (все сказанное ниже
относится не только к запросам, но и к командам изменения
данных DELETE и UPDATE):
FROM { < table_source > }
<table_source> ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
XLOCK }
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
|
Уровни
READUNCOMMITTED
и
READCOMMITTED
соответствуют
одноименным уровням изоляции транзакций, только пишутся слитно.
Блокировки при использовании этих уровней снимаются сразу после
выполнения команды. В случае хинтов REPEATABLEREAD и SERIALIZABLE
блокировки остаются до конца транзакции.
•HOLDLOCK – аналогичен хинту SERIALIZABLE, т.е. устанавливает
разделяемую блокировку диапазона индекса (если индекс существует)
или простую разделяемую блокировку на страницу или таблицу.
Оставлен для обратной совместимости.
•NOLOCK – разрешается использовать только с командой SELECT.
Аналогичен хинту READUNCOMMITTED, т.е. не накладывает никаких
блокировок и игнорирует блокировки других транзакций.
•PAGLOCK
–
пожелание
менеджеру
блокировок
устанавливать
блокировки на уровне страниц. Это пожелание выполняется очень редко.
•READPAST – разрешается использовать только с командой SELECT.
Позволяет выбрать только те строки, на которые не установлена
монопольная блокировка. Другими словами, позволяет выбрать все не
измененные строки.
•ROWLOCK – пожелание менеджеру блокировок устанавливать
блокировки на уровне строк. Это пожелание выполняется очень редко.
•TABLOCK – позволяет установить на всю таблицу блокировку, тип
которой зависит от команды, в которой этот хинт используется. Для
команды SELECT будет установлена разделяемая блокировка на всю
таблицу до тех пор, пока команда не выполнится, но не до конца
транзакции. Действие хинта можно продлить с помощью HOLDLOCK.
Например:
select * from test with(tablock,holdlock)
Установит разделяемую блокировку до конца транзакции.
•TABLOCKX – устанавливает монопольную блокировку на всю таблицу
до конца транзакции даже для команды SELECT.
•UPDLOCK – разрешается использовать только с командой SELECT.
Позволяет установить блокировку обновления вместо разделяемой
блокировки. Действие блокировки продолжается до завершения
транзакции.
•XLOCK – Действие аналогично хинту UPDLOCK с той лишь разницей,
что устанавливается монопольная блокировка на ресурс.
Например:
select * from test with(xlock) where i = 2
Взаимоблокировки
•В
процессе
работы
параллельных
транзакций,
обращающихся к одним и тем же ресурсам, возможно
возникновение
проблемы
взаимоблокировок
(deadlock), которые также называют тупиковыми
блокировками.
• Если
транзакции
обращаются
к
ресурсам
последовательно,
вероятность
возникновения
взаимоблокировки очень мала, однако если порядок
обращения транзакций к общим ресурсам разный,
возможность
возникновения
мертвой
блокировки
повышается многократно
•В SQL Server’е предусмотрен механизм автоматического
определения взаимоблокировок и разрешения конфликтов. Для
этого предназначен специальный серверный процесс LOCK
MONITOR, который просыпается каждые 5 секунд.
•Он проверяет наличие двух и более ожидающих транзакций и
вычисляет зависимости между ними. Если оказывается, что
транзакции зависят друг от друга, т.е. для получения блокировки
одной из них необходимо снять блокировку другой транзакции,
фиксируется факт возникновения взаимоблокировки.
•Единственный способ устранения проблемы заключается в
отмене одной из транзакций. Для каждой транзакции вводится
понятие цены или приоритета. Это значение задается на уровне
соединения следующей командой:
SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var
}
1. MARK = 'RoyaltyUpdate'
Лекция № 14 Репликации ,
дублирование, восстановление
Репликация, дублирование и
восстановление.
Репликация - это процесс, посредством
которого данные копируются между базами
данных, находящимися на том же самом
сервере или на других серверах, связанных
через LAN, WAN или Internet
Репликация Microsoft SQL Server использует
метафоры:
publisher
distributor
subscriber.
Publisher - сервер или база данных, которая
посылает данные на другой сервер или в
другую базу данных.
Subscriber - сервер или база данных, которая
получает данные от другого сервера или
другой базы данных.
Distributor - сервер, который управляет
потоком данных через систему репликации.
Этот сервер содержит специализированную
базу данных: Distribution database.
Publisher содержит
публикацию/публикации. Публикация это совокупность одной или более статей,
которые посылаются серверу подписчику
(subscriber) или базе данных.
Статья (Article) - основной модуль
репликации и это может быть таблица
или подмножество таблицы.
Подписка (subscriptions) - это группа
данных, которые сервер или база данных
получает.
Существуют виды подписки:
push и pull subscriptions
Push subscription - это подписка, при
которой сервер издатель периодически
помещает транзакции на подписавшиеся
сервера или базы данных.
Pull subscription - это подписка, при
которой подписавшийся сервер будет
периодически соединяться с
тиражируемой информацией и
перемещать её из Distribution database.
Distribution database - это системная
база данных, которая хранится на
дистрибуторе
(distributor)
и
не
содержит никаких пользовательских
таблиц.
Эта
база
данных
используется для хранения снимков
заданий
и
всех
транзакций,
ожидающих
распределения
подписчикам.
Топология репликации
Microsoft SQL Server поддерживает
следующие топологии репликации
- Центральный publisher
- Центральный subscriber
- Центральный publisher с отдаленным distributor
- Центральный distributor
- Издающий subscriber
Центральный publisher
Это одна из наиболее используемых
топологий репликации. В этом
сценарии, один сервер исполняет
роли publisher и distributor, а другой
сервер/серверы определяется, как
подписчик/подписчики.
Центральный
subscriber
Это обычная топология
складирования данных. Несколько
серверов или баз данных копируют
свои данные на центральный сервер
в одну или более базы данных
Центральный publisher
с отдаленным
distributor
В этой топологии база Distribution
постоянно находится на сервере, отличном
от сервера, где располагается publisher.
Эта топология используется для
повышения эффективности, когда объём
репликации увеличивается, а также, если
сервер или сетевые ресурсы ограничены.
Это уменьшает загрузку publisher, но
увеличивает сетевой трафик. Эта
топология требует отдельных инсталляций
Microsoft SQL Server для publisher и для
distributor.
Центральный
distributor
В этой топологии, несколько издателей
используют только один distributor, который
постоянно находится на отличном от
издателей сервере. Это одна из наиболее
редко используемой топологии
репликации, потому что имеет уязвимую
точку (на сервере с центральным
distributor), и если сервер distributor
потерпит неудачу, сценарий репликации
будет разрушен полностью.
Издающий subscriber
Это топология двойственной роли. В
ней, два сервера издают те же самые
данные. Сервер издатель посылает
данные на subscriber, и затем
subscriber издает данные на любое
число подписчиков. Это полезно
когда publisher должен послать
данные подписчикам по медленной
или дорогой линии связи.
Типы репликации
Microsoft SQL Server 7.0/2000
поддерживает следующие виды
репликации:
- Snapshot
- Transactional
- Merge
Snapshot репликация
(снимок)
Является самой простой. При этом, все
копируемые данные (точная копия) будут
копироваться из базы данных publisher в
базу(ы) данных subscriber/subscribers на
периодической основе. Snapshot
репликация является лучшим методом
копирования данных, которые нечасто
изменяются и когда размер копируемых
данных не очень большой.
Transactional
репликация
SQL Server фиксирует (делает
моментальные снимки) все
изменения, которые были сделаны в
статье, и сохраняет, как: INSERT,
UPDATE и DELETE инструкции в базе
Distribution. Эти изменения
посылаются подписчикам от
Distribution и применяются к
расположенным в них данным.
Transactional репликации лучше
использовать, когда копируемые
данные часто изменяются или когда
размер копируемых данных
достаточно велик и нет
необходимости поддерживать
автономные изменения
реплицируемых данных относительно
publisher и относительно subscriber.
Merge репликация
Является наиболее трудным типом
репликации. Она предоставляет
возможность автономных изменений
реплицируемых данных и на publisher и на
subscriber. При Merge репликации, SQL
Server фиксирует все накопившиеся
изменения не только в источнике данных,
но и целевых базах данных, и урегулирует
конфликты согласно правилам, которые
Вы предварительно конфигурируете, или
посредством определённого Вами блока
принятия решений - resolver-ра.
Merge репликацию лучше использовать,
когда Вы хотите обеспечить поддержку
автономных изменений реплицируемых
данных относительно publisher и
относительно subscriber.
Агенты Репликации
Microsoft SQL Server 7.0/2000
поддерживает следующих агентов
репликации:
- Snapshot Agent
- Log Reader Agent
- Distribution Agent
- Merge Agent
Snapshot Agent
Агент репликации, который создаёт
файлы снимков, хранит снимки на
distributor и производит запись
информации о состоянии синхронизации в
Distribution database. Snapshot Agent
используется во всех типах репликации
(Snapshot, Transactional и Merge) и может
управляться из SQL Server Enterprise
Manager.
Log Reader Agent
Агент репликации, который
перемещает транзакции, отмеченные
для репликации из transaction log,
находящегося на publisher, в
Distribution database. Этот агент
репликации не используется в
Snapshot репликации.
Distribution Agent
Агент репликации, который перемещает
обрабатывающие снимки задания из
Distribution database к подписчикам и
перемещает все транзакции, ожидающие
распределения на подписчиков. Distribution
Agent используется в Snapshot и
Transactional репликациях и может
управляться с помощью SQL Server
Enterprise Manager.
Merge Agent
Агент репликации, который применяет
первоначальные, обрабатывающие снимки
задания по таблицам базы данных
publication на подписчиках, и потом
объединяет возможные последующие
изменения данных, которые произошли
после создания первоначального снимка.
Merge Agent используется только в Merge
репликации
Резервное
копирование
MS SQL поддерживает 3 типа
backup’а данных
Full backup
Differential backup
Transaction-log backup
Full backup
Сохраняет все объекты вашей базы,
включая пользователей и permissions.
Full backup может производиться без
остановки работы сервера, все
транзакции произведенные за время
выполнения backup’а добавляются к
нему по окончании.
Full backup производит backup
пользователей
базы,
но
не
производит бэкап логинов. Для того
чтобы произвести backup логинов
необходимо забэкапить базу данных
master.
В
дальнейшем
при
восстановлении базы на другом
сервере (имеющем свои логины)
необходимо использовать процедуру
sp_change_users_login
для
синхронизации имен логинов.
1. Enterprise
Manager
Figure A
2. tools
3. backup
database
Указание места
бэкапа.
Выбор
периодичности и
времени бэкапа.
Differential backup
Сохраняет только данные, которые
изменились со времени последнего
full
backup’а
Благодаря
этому
занимает гораздо меньше места на
диске и выполняется существенно
быстрее, что позволяет выполнять
его чаще.
Backup можно выполнить также используя
конструкцию T-SQL:
BACKUP
TO
[
DATABASE
{
database_name
|
<
backup_device
>
@database_name_var
}
[
,...n
]
WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[
[
,
]
DIFFERENTIAL
]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[
[
,
]
FORMAT
|
NOFORMAT
]
[
[
,
]
{
INIT
|
NOINIT
}
]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = {
mediapassword
|
@mediapassword_variable
}
]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[
[
,
]
{
NOSKIP
|
SKIP
}
]
[
[
,
]
{
NOREWIND
|
REWIND
}
]
[
[
,
]
{
NOUNLOAD
|
UNLOAD
}
]
[
[
,
]
RESTART
]
[ [ , ] STATS [ =percentage ] ]
Пример - BACKUP
DATABASE
-- Create a logical backup device for the full
MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO
MyNwind_1
Transaction-log backup
Включает в себя историю всех
транзакций базы. Наличие такого
лога позволит вам откатиться на
любой момент времени до
последнего full backup и привести
базу в состояние, в котором она была
в этот момент времени
BACKUP LOG { database_name | @database_name_var }
{
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable }
]
[ [ , ] MEDIANAME = { media_name | @media_name_variable
} ]
[ [ , ] MEDIAPASSWORD = { mediapassword |
@mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var
} ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY =undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ =percentage ] ]
Бэкап базы и журнала.
USE master EXEC sp_addumpdevice 'disk',
'MyNwind_2', 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MyNwind_2.dat'
--Create the log backup device.
USE master EXEC sp_addumpdevice 'disk',
'MyNwindLog1', 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MyNwindLog1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_2 -Update activity has occurred since the full database
backup.
-- Back up the log of the MyNwind database.
BACKUP LOG MyNwind TO MyNwindLog1
Для восстановления базы из Backup
используется команда restore
database, для восстановления лога –
restore log, кроме того как всегда
можно воспользоваться Enterprise
Manager
RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable
}]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
RESTORE LOG { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable
}]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [= percentage ] ]
[ [ , ] STOPAT = { date_time | @date_time_var } .
| [ , ] STOPATMARK = 'mark_name' [ AFTER datetime ]
| [ , ] STOPBEFOREMARK = 'mark_name' [ AFTER datetime ]
]
]
1. RESTORE DATABASE MyNwind
FROM MyNwind_1
2. RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY RESTORE
DATABASE MyNwind FROM
MyNwind_1 WITH FILE = 2
При настройке бэкапа в EM имеется
возможность выбрать одну из 3-х recovery
моделей
Simple recovery – позволяет в дальнейшем
восстановить базу в состояние в котором
она была при бэкапе, то есть дойти до
состояния в котором возник сбой не удастся
Full recovery – можно дойти до состояния
при котором возник сбой (бэкапится
transaction log)
Bulk-Logged Recovery – тоже самое что и Full
Recovery, но оптимизировано бэкапит
операции insert into и bulk insert – занимает в
итоге меньше места, но и гарантия
восстановления ниже
This example restores the transaction log to
the mark named "RoyaltyUpdate."
1. BEGIN TRANSACTION RoyaltyUpdate WITH MARK 'Update royalty
values'
2. GO
3. USE pubs
4. GO
5. UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE
'PC%'
6. GO
7. COMMIT TRANSACTION RoyaltyUpdate
8. GO --Time passes. Regular database --and log backups are taken. -An error occurs.
9. USE master
10. GO
11. RESTORE DATABASE pubs FROM Pubs1 WITH FILE = 3,
NORECOVERY
12. GO
13. RESTORE LOG pubs FROM Pubs1 WITH FILE = 4, STOPATMARK
= 'RoyaltyUpdate'
Лекция № 15 Поиск и индексация
Полнотекстовый
поиск и индексы.
(Full-Text Search)
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX
index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]
< index_option > :: =
{ PAD_INDEX |
FILLFACTOR = fillfactor |
IGNORE_DUP_KEY |
DROP_EXISTING |
STATISTICS_NORECOMPUTE |
SORT_IN_TEMPDB
}
unique – значение индексируемой группы должно быть уникально
clustered – данные упорядочиваются физически (по умолчанию это
используется для всех primary keys)
nonclustered – используется вектор индексов, данные физически не
упорядочиваются
CREATE TABLE t1 (a int, b int, c AS a/b)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
GO
INSERT INTO t1 VALUES ('1', '0')
GO
Средства SQL для проверки
соответствия текста
заданному шаблону
LIKE – возвращает true, если строковое поле
содержит шаблон
match_expression [ NOT ] LIKE pattern [ ESCAPE
escape_character ]
PATINDEX или CHARINDEX – возвращает
позицию заданной подстроки в строке:
1. PATINDEX ( '%pattern%' , expression )
2. CHARINDEX ( expression1 , expression2 [ ,
start_location ] )
Пример: Like
USE pubs
GO
CREATE PROCEDURE find_books2 @au_lname
varchar(20)
AS SELECT t.title_id, t.title
FROM authors a, titleauthor ta, titles t
WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND
a.au_lname LIKE @au_lname + '%'
EXEC find_books2 'ring'
Результат работы процедуры:
title_
id title -------- -------------------------------------------------------------MC3021
The Gourmet Microwave
PS2091
Is Anger the Enemy?
PS2091
Is Anger the Enemy?
PS2106
Life Without Fear (4 row(s) affected)
Пример: PATINDEX
USE Northwind
GO
SELECT CategoryID, PATINDEX('%candies%',
Description)AS POSITION FROM Categories
WHERE PATINDEX('%candies%', Description) <> 0
Результатом будут строки, где в поле Description
будет найдена подстрока 'candies' с
произвольным началом и концом
Пример: CHARINDEX
USE pubs
SELECT CHARINDEX('wonderful', notes)
FROM titles
WHERE title_id = 'TC3218'
Результат- позиция подстроки 'wonderful‘ в
поле notes таблицы titles :
---------------46
(1 row(s) affected)
Оба эти метода ограничены образцом
Full-Text Search позволяет отслеживать
как точное соответствие слову в
шаблоне, так и словоформы (Например
шаблон drink, а найти нужно не только
drink, но и drunk)
Помимо поиска по plain text
поддерживается поиск по документам
перечисленных ниже типов
(содержимое этих документов должно
содержаться в image поле)
Поддерживаемые типы
документов
Word (*.doc)
Excel (*.xls)
PowerPoint (*.ppt)
Text (*.txt)
HTML (*.htm или *.html)
Кроме того многие производители создают
собственные фильтры для поддержки
полнотекстового поиска по своим
документам (например Adobe для Adobe
Acrobat)
Full-Text Search взаимодействует с
MS Search service, который способен
индексировать слова, содержащиеся
в обработанных файлах для
организации в дальнейшем поиска по
этим словам (это используется
например в Microsoft Index Server)
Для таблиц MS SQL сервера строятся
полнотекстовые индексы, которые
хранятся отдельно от базы данных
(используется file-based storage
архитектура)
Запрос, содержащий
Full-Text поиск
SQL Server
Query output
Full-text часть
запроса
MSSearch
service
Номера
найденных
колонок
Catalog files
Stored in OS
Physical SQL
Server Database
Для того, чтобы FullText Search начал
работать необходимо:
Включить поддержку Full-Text для базы
Создать Full-Text Catalog
Создать Full-Text Index
Заполнить Full-Text Index. В процессе
заполнения (population), добавленные в
Full-Text Index текстовые поля таблиц
просматриваются и составляется
«словарь» слов, содержащихся в каждом
поле каждой записи, который в
дальнейшем используется для поиска
Включение поддержки
Full-Text для базы
EXEC sp_fulltext_database [@action =]
‘{enable|disable}’
USE Northwind
EXEC sp_fulltext_database @action = ‘Enable’
Создание Full-Text
Catalog
Для управления Full-Text каталогом используется
процедура sp_fulltext_catalog
EXEC sp_fulltext_catalog [@ftcat = ] ‘<name of catalog>’,
[@action =]
‘{create|drop|start_incremental|start_full|stop|rebuild}’
[, [@path =] ‘<root directory>’ ]
USE Northwind
EXEC sp_fulltext_catalog @ftcat = ‘NorthWindFullText’,
@action = ‘CREATE’
Включение поддержки FullText Search для таблиц
EXEC sp_fulltext_table [@tabname =] ‘[<owner>.]<table>’,
[@action=]
‘{create|drop|activate|deactivate|start_change_tracking
|stop_change_tracking|start_background_update_index
|stop_background_update_index|update_index|start_full
|start_incremental|stop}’
[,[@ftcat = ] ‘<fulltext catalog>’, [@keyname =] ‘index name’]
USE Northwind
EXEC sp_fulltext_table @tabname = ‘Employees’,
@action = ‘create’,
@ftcat = ‘NorthwindFullText’,
@keyname = ‘PK_Employees’
Добавление колонки в
Full-Text индекс
sp_fulltext_column [@tabname =] ‘[<owner>.]<table>’,
[@colname =] ‘<column name>’ ,
[@action =] ‘{add|drop}’
[,[ @language =] ‘<language>’]
[, [@type_colname =] ‘<type column name>’]
USE Northwind
EXEC sp_fulltext_column @tabname = ‘Employees’,
@colname = ‘Notes’
@action = ‘add’
После всех этих действий необходимо
запустить заполнение (population)
индекса
EXEC sp_fulltext_table @tabname = ‘Employees’,
@action = ‘start_full’,
Full-Text Query Syntax
Существует 2 условных выражения:
CONTAINS – строгое соответствие шаблону
FREETEXT – нестрогое соответствие
(словоформы)
и 2 эквивалентных выражения, возвращающие
таблицы:
CONTAINSTABLE
FREETEXTTABLE
CONTAINS
CONTAINS ({<column>|*} , ‘<search condition>’)
SELECT EmployeeID, LastName, FirstName
FROM Employees
WHERE CONTAINS(*, ‘Course’)
FREETEXT
FREETEXT({<column>|*} , ‘<search condition>’)
SELECT EmployeeID, LastName, FirstName
FROM Employees
WHERE FREETEXT(*, ‘Course’)
CONTAINSTABLE
CONTAINSTABLE (<table>, {<column>|*} , ‘<contains search
condition>’ [, <top ‘n’>])
SELECT *
FROM CONTAINSTABLE(Employees, *, ‘Course’)
Возвращает таблицу с 2мя полями – key и rank.
Key – ключевое поле исходной таблицы (Employees),
соответствующее найденным записям
Rank – уровень соответствия найденной записи
заданному шаблону (число от 0 до 1000)
SELECT Rank, EmployeeID, LastName,
FirstName, Notes
FROM CONTAINSTABLE(Employees, *,
‘Course’) AS ct
JOIN Employees AS e
ON ct.[KEY] = e.EmployeeID
SELECT Rank, EmployeeID, LastName,
FirstName, Notes
FROM FREETEXTTABLE(Employees, *,
‘Course’) AS ct
JOIN Employees AS e
ON ct.[KEY] = e.EmployeeID
Использование фраз в
шаблонах
SELECT EmployeeID, LastName, FirstName,
Notes
FROM Employees
WHERE CONTAINS(*, ‘ ”University of
California” ’)
Этот запрос найдет все записи, в поле Notes
которых есть слова ‘University’, ‘of’ и
‘California’
Можно использовать and, or и not
SELECT EmployeeID, LastName,
FirstName, Notes
FROM Employees
WHERE FREETEXT(*, ‘ ”University of
California” ’)
Этот запрос найдет все записи, в поле
Notes которых есть слова ‘University’,
‘of’ ИЛИ ‘California’. ‘Of’ будет
проигнорировано как ‘noise word’.
Список таких слов содержится в
обычном текстовом файле
Использование NEAR
NEAR – между словами шаблона имеется не более 8-10
других слов:
SELECT Rank, EmployeeID, LastName, FirstName, Notes
FROM CONTAINSTABLE(Employees, *, ‘completed near sales’)
AS ct
JOIN Employees AS e
ON ct.[KEY] = e.EmployeeID
Пример: NEAR
USE Northwind
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName, KEY_TBL.RANK FROM
Categories AS FT_TBL
INNER JOIN CONTAINSTABLE (Categories,
Description, '("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)' ) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY] WHERE
KEY_TBL.RANK > 2 AND FT_TBL.CategoryName
<> 'Seafood'
ORDER BY KEY_TBL.RANK DESC
Использование
префиксов
SELECT LastName, FirstName, Notes
FROM Employees
WHERE CONTAINS(*, ‘ ”grad*” ’)
Будет искать все слова начинающиеся
с grad
CONTAINS(*, ‘grad*’) будет искать
подстроку grad* (важно ставить
кавычки)
Задание весов для
частей шаблона
SELECT Rank, EmployeeID, LastName, FirstName, Notes
FROM CONTAINSTABLE(Employees, Notes,
‘ISABOUT (BA WEIGHT (.2), BS WEIGHT (.4), MA WEIGHT (.8))’) AS ct
JOIN Employees AS e
ON ct.[KEY] = e.EmployeeID
ORDER BY Rank DESC
То есть найти ‘MA’ в 2 раза важнее, чем ‘BS’, а ‘BS’ в 2 раза
важнее, чем ‘BA’. Записи, в которых содержится ‘MA’ будут
иметь более высокий RANK. Сначала будут выведены,
магистры (MA), затем бакалавры (BS), затем бакалавры
искусств (BA) – производится ранжирование по уровню
образования.
Поиск словоформ с
использованием
CONTAINS
По умолчанию CONTAINS ищет точное
соответствие шаблону
SELECT LastName, FirstName, Notes
FROM Employees
WHERE CONTAINS(Notes, ‘FORMSOF
(INFLECTIONAL, graduate, degree)’)
Будет искать все слова однокоренные
graduate или degree (FREETEXT работает
так по умолчанию)
Лекция №16 Механизмы доступа к данным.
Механизмы доступа к данным.
Существует несколько способов доступа к данным
из средств разработки и клиентских приложений.
Подавляющее большинство систем управления
базами данных содержит в своем составе
библиотеки, предоставляющие специальный
прикладной программный интерфейс (Application
Programming Interface, API) для доступа к данным
этой СУБД.
Windows-версии
клиентского
программного
обеспечения наиболее популярных серверных СУБД, в
частности Microsoft SQL Server, Oracle, Informix,
содержат также COM-серверы, предоставляющие
объекты для доступа к данным и метаданным.
Использование клиентского API (или клиентских COMобъектов) является наиболее очевидным (и нередко
самым
эффективным
с
точки
зрения
производительности) способом манипуляции данными
в приложении.
Однако в этом случае созданное приложение сможет
использовать
данные
только
СУБД
этого
производителя, и замена ее на другую повлечет за
собой переписывание значительной части кода
клиентского приложения - клиентские API и объектные
модели не подчиняются никаким стандартам и
различны для разных СУБД.
Другой способ манипуляции данными в приложении
базируется на применении универсальных механизмов
доступа к данным.
Универсальный механизм доступа к данным обычно
реализован в виде библиотек и дополнительных
модулей, называемых драйверами или провайдерами.
Библиотеки содержат некий стандартный набор
функций или классов, нередко подчиняющийся той
или иной спецификации.
Дополнительные модули, специфичные для той или
иной СУБД, реализуют непосредственное обращение к
функциям клиентского API конкретных СУБД.
Достоинством универсальных механизмов является
возможность
применения
одного
и
того
же
абстрактного API, а во многих случаях - COM-серверов,
компонентов, классов для доступа к разным типам
СУБД.
Приложения,
использующие
универсальные
механизмы доступа к данным, легко модифицировать,
если необходима смена СУБД.
Универсальные механизмы доступа к данным:
•Open Database Connectivity (ODBC).
•OLE DB.
•ActiveX Data Objects (ADO).
• Borland Database Engine (BDE).
• ADO.NET
Универсальные механизмы ODBC, OLE DB и ADO
фирмы Microsoft представляют собой по существу
промышленные стандарты.
Что касается механизма доступа к данным BDE фирмы
Borland, то он так и не стал промышленным
стандартом, однако до недавнего времени применялся
довольно широко, так как до выхода Delphi 5 был
практически
единственным
универсальным
механизмом доступа к данным, поддерживаемым
средствами разработки Borland на уровне компонентов
и классов.
Возможные механизмы доступа к
данным из приложений и средств
разработки
ODBC
(Open
Database
Connectivity)
широко
распространенный программный интерфейс фирмы
Microsoft, удовлетворяющий стандартам ANSI и ISO
для интерфейсов обращений к базам данных (Call Level
Interface, CLI).
Для доступа к данным конкретной СУБД с помощью
ODBC, кроме собственно клиентской части этой СУБД,
нужен ODBC Administrator (приложение, позволяющее
определить, какие источники данных доступны для
данного компьютера с помощью ODBC, и описать
новые источники данных), и ODBC-драйвер для
доступа к этой СУБД.
ODBC-драйвер
представляет
собой
динамически
загружаемую библиотеку (DLL), которую клиентское
приложение может загрузить в свое адресное
пространство и использовать для доступа к источнику
данных. Для каждой используемой СУБД нужен
собственный ODBC-драйвер, так как ODBC-драйверы
используют функции клиентских API, разные для
различных СУБД.
С помощью ODBC можно манипулировать данными
любой СУБД (и даже данными, не имеющими прямого
отношения к базам данных, например данными в
файлах электронных таблиц или в текстовых файлах),
если для них имеется ODBC-драйвер.
Для манипуляции данными можно использовать как
непосредственные вызовы ODBC API, так и другие
универсальные
механизмы
доступа
к
данным,
например
OLE
DB,
ADO,
BDE,
реализующие
стандартные функции или классы на основе вызовов
ODBC API в драйверах или провайдерах, специально
предназначенных для работы с любыми ODBCисточниками.
OLE DB и ADO
OLE DB и ADO - часть универсального механизма
доступа к данным Microsoft (Microsoft Universal Data
Access), позволяющая осуществить доступ как к
реляционным, так и к нереляционным источникам
данных, таким как файловая система, данные
электронной почты, многомерные хранилища данных и
др.
Microsoft ActiveX Data Objects (ADO) - это набор
библиотек, содержащих COM-объекты, реализующие
прикладной программный интерфейс для доступа к
таким
данным
и
используемые
в
клиентских
приложениях. ADO использует библиотеки OLE DB,
предоставляющие низкоуровневый интерфейс для
доступа к данным. OLE DB предоставляет доступ к
данным с помощью COM-интерфейсов. Можно также
использовать OLE DB непосредственно, минуя ADO.
Для доступа к источнику данных с помощью OLE DB
требуется, чтобы на компьютере, где используется
клиентское приложение, был установлен OLE DBпровайдер для данной СУБД. OLE DB-провайдер
представляет собой DLL, загружаемую в адресное
пространство клиентского приложения и используемую
для доступа к источнику данных. Для каждого типа
СУБД нужен собственный OLE DB-провайдер, так как
эти провайдеры базируются на функциях клиентских
API, разных для различных СУБД.
Среди OLE DB-провайдеров для разных источников
данных имеется специальный провайдер Microsoft OLE
DB Provider for ODBC Drivers. Этот провайдер
использует не API клиентской части какой-либо СУБД,
а интерфейс ODBC API, поэтому он применяется вместе
с ODBC-драйвером для выбранной СУБД.
OLE DB подробнее.
Итак, OLE DB представляет собой программный
интерфейс для доступа к различным источникам
данных. В спецификации OLE DB определен набор
COM-интерфейсов,
инкапсулирующих
различные
сервисы управления данными и предоставляющих
однотипный доступ к перечисленным выше данным.
Эти
интерфейсы
могут
быть
использованы
в
приложениях, предоставляющих доступ к данным.
Компоненты OLE DB
На самом верхнем уровне можно отметить три главных
компонента OLE DB: потребители (consumers),
провайдеры данных (data providers) и сервисные
компоненты (service components).
Любой
компонент
программного
обеспечения,
применяющий
интерфейсы
OLE
DB,
является
потребителем.
Это может быть какое-либо офисное приложение или
иное бизнес-приложение, средство разработки типа
Visual Basic или Delphi либо даже COM-объекты для
доступа к данным, применяющие интерфейсы OLE DB.
Потребители могут обращаться к данным посредством
ActiveX
Data
Objects,
представляющих
собой
высокоуровневый интерфейс к OLE DB, или применять
OLE DB непосредственно, используя OLE DBпровайдер.
Объекты OLE DB
Объектная модель OLE DB содержит четыре ключевых
объекта:
•DataSource;
•Session;
•Command;
•Rowset.
Объект DataSource, применяемый потребителями
данных для соединения с провайдером.
Объект
Session
предоставляет
контекст
для
транзакций, может генерировать наборы данных
(rowsets) из источников данных, а также команды для
запросов к источнику данных.
Объект Session может выполнять роль фабрики
классов для объектов Command и Rowset и объекта
Transaction,
применяемого
для
управления
вложенными транзакциями. Объекты Command и
Rowset могут быть использованы для создания или
модификации таблиц и индексов.
Объект Command используется для выполнения
команд, представляющих собой строки, передаваемые
от потребителя данных объекту Data Source для
выполнения.
В большинстве случаев такая команда представляет
собой SQL-предложение SELECT, однако в общем
случае это может быть любое другое SQL-предложение
(например, DDL-предложение).
Объект Rowset (набор данных) позволяет OLE DBпровайдеру
данных
представлять
данные
из
источников данных в табличном формате, то есть в
виде набора строк, содержащих одну или несколько
колонок.
Этот объект может быть результатом выполнения
команды
или
может
быть
сгенерирован
непосредственно провайдером данных, если провайдер
не поддерживает команд.
Все провайдеры данных <умеют>
данных напрямую. Объект Rowset
использован
для
обновления,
удаления строк - это зависит от
провайдера данных.
создавать наборы
может быть также
добавления
или
функциональности
ADO
представляет
собой
высокоуровневый
программный интерфейс для доступа к OLE DBинтерфейсам.
Он позволяет манипулировать данными с помощью
любых OLE DB-провайдеров, как входящих в состав
Microsoft Data Access Components некоторых других
продуктов Microsoft, так и произведенных сторонними
производителями.
ADO содержит набор объектов, используемых для
соединения с источником данных, для чтения,
добавления, удаления и модификации данных.
Объект ADO Connection применяется для установки
связи с источником данных. Он представляет
единственную сессию.
Этот
объект
позволяет
изменить
параметры
соединения с базой данных, а также начать или
завершить транзакцию. Используя объект Connection,
мы можем выполнять команды (например, SQLзапросы) с помощью метода Execute. Если команда
возвращает набор данных, автоматически создается
объект Recordset, который возвращается в результате
выполнения этого метода.
Объект Error используется для получения сведений об
ошибках, возникающих в процессе выполнения.
Объект Command представляет собой команду,
которую можно выполнить в источнике данных.
Команда может содержать SQL-предложение или вызов
хранимой процедуры. В последнем случае для
определения параметров процедуры может быть
использована
коллекция
Parameters
объекта
Command.
Объект Recordset - это набор записей, полученных из
источника данных, и может быть использован для
добавления, удаления, изменения, просмотра записей.
Данный объект может быть открыт непосредственно
или создан с помощью объектов Connection или
Command.
Объект Field - это колонка в наборе
представленных объектом Recordset.
данных,
Объектная модель ADO
Как объекты ADO используют OLD DB:
•объект ADO Connection использует объекты OLE DB
DataSource и Session.
•объект ADO Command использует объект OLE DB
Command и интерфейс ICommand. Например, его
метод
Execute
вызывает
непосредственно
одноименный метод объекта OLE DB Command, его
свойство CommandText доступно с помощью методов
GetCommandText
и
SetCommandText
интерфейса
ICommandText.
•объект ADO Recordset использует объект OLE DB
Rowset.
Он
применяет
интерфейсы
IRowset,
IRowsetLocate
и
IRowsetInfo
для
реализации
большинства методов, свойств и коллекций. Объект
Field использует интерфейс IColumnsInfo.
ПРИМЕР (VB):
Dim cnn
' Создать соединение до базы данных
Set cnn = CreateObject("ADODB.Connection")
' Открываем при помощи connection string,
' определяющей тип провайдера и путь до базы
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\goodsdata.mdb;"
Dim rs
Set rs = CreateObject("ADODB.RecordSet")
rs.Open "SELECT * FROM Goods", cnn
‘rs = cnn.Execute "SELECT * FROM Goods“ – использование ADODB.Connection как фабрики
классов
' цикл по рекордсету, производящий какие-то действия
rs.Close
cnn.Close
ADO.NET является преемником Microsoft ActiveX Data
Objects (ADO).
Это W3C стандартизированная модель
программирования для создания распределенных
прикладных программ, нацеленных на совместное
использование данных.
Подобно ADO, ADO.NET является программным
интерфейсом (API) для прикладного программного
обеспечения, позволяющим обращаться к данным и
другой информации. ADO.NET поддерживает такие
современные требования, как создание клиентского
интерфейса к базам данных на фронтальном уровне и
на уровне промежуточного слоя объектов клиентских
приложений, инструментальных средств, языков
программирования или Internet браузера.
ADO.NET, подобно ADO, обеспечивает интерфейс
доступа к OLE DB - совместимым источникам данных,
таким, как Microsoft SQL Server 2000.
Прикладные программы, позволяющие пользователям
совместно использовать данные, могут использовать
ADO.NET для подключения к источниками данных, а
также для поиска, управления, и модификации этих
данных. Также, прикладные программы (далее ПП)
могут использовать OLE DB для управления данными,
хранящимися в не относительных форматах, таких, как
Microsoft Excel.
В решениях, требующих офлайнового или удаленного
доступа к данным, ADO.NET использует XML для
обмена данными между программами или с Веб
страницами.
Термин
ADO
ADO.NET
Резидентное
представление
данных в
памяти
Использует объект RecordSet,
который напоминает
отдельную таблицу.
Использует объект DataSet,
который может содержать
одну или более таблиц,
представленных объектами
DataTable
Инспектировани
е данных
Последовательное
сканирование данных
RecordSet
Использует навигационную
парадигму для
непоследовательного доступа
к строкам в таблице. Следует
за отношениями, чтобы
передвигаться от строк в
одной таблице к
соответствующим строкам в
другой таблице.
Отношения
между
множеством
таблицами
Требует, чтобы запрос JOIN
транслировал данные из
объединяемых таблиц базы
данных в отдельную,
результирующую таблицу.
Поддерживает объект
DataRelation, чтобы
сопоставить строки в одном
объекте DataTable со строками
в другом объекте DataTable.
Офлайновый доступ
Использует RecordSet, для
которого типична поддержка
соединения, представленная
объектом Connection. Вы
подключаетесь к базе данных
посредством запросов к OLE
DB провайдеру.
Подключается к базе
данных посредством
стандартизированных
запросов к объекту
DataSetCommand, который
подключается к OLE DB
провайдеру (а иногда,
непосредственно к API,
обеспечивающемуся СУБД).
Программируемость
Использует объект Connection,
чтобы передать команды,
адресуемые источникам
данных, лежащим в основе
конструкции данных.
Использует строгий формат
программирования
характеристик XML. Данные
включают в себя
собственные описатели,
поэтому, имена элементов
кода отражают реальные
проблемы, решаемые кодом.
Лежащие в основе
конструкций данных типы
таблиц, строки, и таблицы,
делают код проще для
понимания и написания.
Совместное
использование
офлайновый данных
между уровнями или
компонентами
Использует COM для
передачи RecordSet в
офлайне.
Поддерживаются только
те типы данных, которыми
располагает стандарт
COM. Требуется
преобразование типов,
которое отнимает
системные ресурсы.
Передает DataSet с XML
файлом. Формат XML не
имеет никаких
ограничений на типы
данных и не требует
никаких преобразований
типов.
Передача данных через
межсетевые экраны
(firewalls)
Проблематично, потому
что межсетевые экраны
обычно конфигурируются
так, чтобы предотвратить
запросы системного
уровня, которые
используются в COM.
Поддерживается, потому
что объекты DataSet,
используемые ADO.NET,
передают XML,
разработанный на основе
HTML, который может
проходить через
межсетевые экраны.
Масштабируемость
Блокировки баз данных и
длительные, активные
подключения к базам
данных могут стать
причиной коллизий и
сильно ограничивать
ресурсы базы данных.
Офлайновый доступ к
базам данных без
накладывания длительных
блокировок или
применения длительных,
активных подключений к
базе данных, существенно
разгружает ресурсы базы
данных.
public void ReadMyData(string myConnString) {
string mySelectQuery = "SELECT OrderID, Customer FROM Orders";
SqlConnection myConnection = new SqlConnection(myConnString);
SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
try {
while (myReader.Read()) {
Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(1));
}
} finally {
// always call Close when done reading.
myReader.Close();
// always call Close when done reading.
myConnection.Close();
}
}
Примеры работа с реляционными источниками
данных через ADO и ODBC в веб-приложениях.
Web-server
MDAC
request
browser
Db server
response
Access, text file ..
Web-server
MDAC
request
browser
Db server
response
Access, text file ..
Существуют 2 типа организации запроса к серверу (request)
•Get – данные пересылаются в строке запроса.
•Post – данные пересылаются в теле запроса.
Для ввода данных используются html-формы, позволяющие
работать с полями следующих основных типов
•Text
•Textarea
•Hidden
•Select (pulldown)
•Checkbox
Формы и поля ввода описываются с помощью основного элемента
html – тэгов.
<form action=“” method=“post”>
…
<input type=text name=t1 value=“hello”>
<input type=submit>
</form>
Работать с формами можно через клиентские скрипты
Они не предполагают отсылки запроса к веб-серверу и
выполняются непосредственно браузером.
Серверные скрипты выполняются на веб-сервере и формируют
response, отдаваемый браузером, фактически любой серверный
скрипт генерирует html динамически.
Исторически первая технология серверных скриптов – CGI
генерировала response полностью в программе, написанной, как
правило на C или на Perl.
В середине 90х годов появились так называемые серверные
страницы – response не генерируется полностью, серверный код
выделяется специальными тэгами и «встраивается» в html.
Наиболее успешные примеры серверных страниц:
•PHP (open source)
•JSP (sun)
•ASP (microsoft)
ASP и JSP не являются новыми языками, это не более чем
технологии, php имеет собственный C-подобный язык.
Для написания JSP используется Java
ASP по умолчанию поддерживает 2 языка – VBScript и JavaScript,
однако существует возможность расширения этого списка.
5 основных объектов ASP
Request – получение информации о запросе
Response – управление выводом веб-сервера
Session – работа с пользовательской сессией
Application – работа с данными, актуальными для всех сессий
Server – объект, в частности позволяющий создавать экземпляры
com-объектов (Server.CreateObject(“[object name]”) )
ASP-приложение это потребитель com-объектов, поскольку в
саму технологию заложено очень мало функциональности,
программист может оперировать лишь с 5ю перечисленными
объектами и писать условия и циклы на JavaScript или VBScript