Уральский государственный университет им. А. М. Горького математико-механический

Пример создания таблицы 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) – проверяет принадлежность значений поля диапазону

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

В свойствах связи в диаграмме указывается каким образом поступать с записями зависимой таблицы при обновлении данных основной таблицы (например каскадно удалять). Скрипт для создания таблицы T 1 с указанной связью будет выглядеть следующим образом: CREATE TABLE [T 1] ( [T 1_ID] [int] IDENTITY (1, 1) NOT NULL , [T_ID] [int] NOT NULL , CONSTRAINT [PK_T 1] PRIMARY KEY CLUSTERED ( [T 1_ID] ) ON [PRIMARY] , CONSTRAINT [FK_T 1_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 ^3 1 до 2^3 1 -1 smallint Целые от – 2 ^ 15 до 2^ 15 -1 tinyint Целые от 0 до 2 55 bit Целые 0 или 1 decimal and numeric decimal Десятичные числа с фиксированным numeric количеством знаков до и после запятой от – 10 ^ 38+1 до 10 ^ 38 -1 money and smallmoney Числа в денежном формате от – 2 ^63 до 2 ^63 -1 , точность 0, 0001 от денежной единицы smallmoney Числа в денежном формате от – 214748, 3648 до 214748, 3647, точность 0, 0001 от денежной единицы

proximate numeric float Числа с плавающей точкой от – 1. 79 Е+308 до 1. 79 Е+308 real Числа с плавающей точкой от – 3. 40 Е+38 до 3. 40 Е+38 datetime and smalldatetime Значения даты и времени начиная с 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 обращается к БД. Объекты- имена таблиц, атрибутов, индексов и т. п. В описании синтаксиса [ ] указывают на не обязательный параметр, знак … означает повторение ранее указанного выражения, {} объединяют последовательность элементов в логическую группу, один из элементов которой должен быть использован. Вертикальна черта | указывает, что часть, следующая за этим символом, является одним из возможных вариантов. Угловые скобки , заключают элементы объясняемые по ходу.

164 База данных «Колледж» STUDENTS SNUM SFAM SNAME SFATH STIP 3412 Поляков Анатолий Алексеевич 25. 50 3413 Старова Любовь Михайловна 17. 00 3414 Гриценко Владимир Николаевич 0. 00 3415 Котенко Анатолий Николаевич 0. 00 341 6 Нагорный Евгений Васильевич 25.

165 PREDMET PNUM PNAME TNUM HOURS COURS 2001 Физика 4001 34 1 2002 Химия 4002 68 1 2003 Математика 4003 65 1 2004 Философия 4005 17 2 200 5 Экономика

166 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 100 5 341 6 200 4 12. 06.

167 TEACHERS TNUM TFAM TNAME TFATH TDATE 4001 Викулова Валентина Ивановна 01. 04. 94 4002 Костенко Олег Владимирович 01. 09. 97 4003 Казанцев Виталий Владимирович 01. 09. 88 4004 Поздняк Любовь Алексеевна 01. 09. 88 400 5 Загоруйко Илья Дмитриевич 01. 09.

Простейшие SELECT — запросы. Оператор SELECT ( выбрать) языка SQL является самым важным и самым часто используемым оператором. В обобщенной форме его синтаксис : SELECT [DISTINCT] FROM [ WHERE ] [ORDER BY ] [GROUP BY ] [HAVING ] [UNION ];

ПРИМЕРЫ: 1) SELECT * from USP WHERE SNUM = 3412 ; 2)SELECT SNUM, SFAM, SNAME FROM STUDENTS WHERE STIP>0; unum snum pnum udate mark 1001 3412 2001 10. 06. 200 2 5 1004 3412 2003 12. 06. 200 2 4 SNUM SFAM SNAME 3412 Поляков Анатолий 3413 Старова Любовь 3416 Нагорный Евгений

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

Пример 5. 1 Показать номера студенческих билетов, фамилии и имена тех лиц, чьи имена начинаются с буквы «А» . SELECT SNUM, SFAM, SNAME FROM STUDENTS WHERE SNAME)<"Б"; SNUM SFAM SNAME 3412 Поляков Анатолий 3415 Котенко Анатолий

Пример 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 Математика

В записи логических условий могут быть использованы операторы 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 ) I N (4, 5))); SNUM UDATE MARK SFAM 3412 10. 06. 2002 5 Поляков 3413 10. 06. 2002 4 Старова 3412 12. 06. 2002 4 Поляков 3416 12. 06. 2002 5 Нагорный 3412 13. 06. 2002 4 Поляков

Пример 5. 4 Получить сведения о студентах, не получивших оценок 4 и 5. SELECT USP. SNUM, STUDENTS. SFAM, USP. UDATE, USP. MARK FROM USP , STUDENTS WHERE (((USP. SNUM)=[STUDENTS]. [SNUM]) AND ((USP. MARK) N OT I N (4, 5))); SNUM SFAM UDATE MARK 3414 Гриценко 11. 06. 2002 3 3414 Гриценко 12. 06.

Оператор BETWEEN используется для проверки условия вхождения значения поля в заданный интервал, т. е. задаются вместо списка границы. BETWEEN 20 AND 30 . Типы полей как числовые, так и символьные. Пример 5. 5 Показать список тех, кто получает стипендию в указанном диапазоне. SELECT STUDENTS. SNUM, STUDENTS. SFAM, STUDENTS. STIP FROM STUDENTS WHERE (((STUDENTS. STIP) B ETWEEN 20 A ND 30)); SNUM SFAM STIP 3412 Поляков 25, 50 3416 Нагорный 25,

Оператор LIKE применим только символьным полям типа CHAR или VARCHAR. Этот оператор просматривает строковые значения полей и определяет входит ли образец поиска в символьную строку-значение поля. В образце может использоваться шаблон: • Символ подчеркивания «_» определяет наличие 1 любого символа. • Символ % или * допускает наличие любых символов произвольной длины.

Пример 5. 7 Показать списки студентов с отчеством на «Ни*» . SELECT STUDENTS. SNUM, STUDENTS. SFAM, STUDENTS. SNAME, STUDENTS. SFATH FROM STUDENTS WHERE (((STUDENTS. SFATH) L IKE «Ни*»)); SNUM SFAM SNAME SFATH 3414 Гриценко Владимир Николаевич 3415 Котенко Анатолий Николаевич

Если внутри образца содержатся знаки _ | % | * | , то применяют 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 Физика 34 2002 Химия 68 2003 Математика 65 2004 Философия 17 2005 Экономика

Пример. Создать таблицу S TUDENTS. • CREATE TABLE STUDENTS (SNUM INTEGER, SFAM CHAR (20), SNAME CHAR (15), SFATH CHAR (15), STIP DECIMAL) 1. В этой команде порядок полей определяется их местом в списке. 2. После того, как таблица создана, её можно изменить.

Добавление новых полей выполняется командой: ALTER TABLE ADD [()], … [()]); Добавляемые поля автоматически получают значения NULL , если не указываются иные значения по умолчанию [DEFAULT constant_expression ] .

Пример. Предположим мы решили добавить номер курса и специальность. ALTER TABLE STUDENTS ADD COURS INTEGER, SPEC CHAR (20);

• Наиболее частые изменения связаны с изменением размера поля. Необходимо убедиться, что изменения не приведут к потере информации, поэтому необходимо лучше проектировать таблицу и вносить изменения только в крайнем случае. ALTER TABLE MOD I FY [()], … [()]); • Изменения возможны , если столбец пуст; • Для заполненного столбца изменяется размер/точность в сторону увеличения; • Установка ограничения NOT NULL возможна при отсутствии значений NULL ; • Разрешается изменять значения устанавливаемые по умолчанию.

Для удаления таблицы необходимо: 1) быть ее создателем или иметь на это право; 2) перед удалением необходимо ее очистить от данных, это позволяет избежать случайной потери информации. • DROP TABLE ; Пример. Удалить все сведения и таблицу PREDMET. 1. DELETE FROM PREDMET; 2. DROP TABLE PREDMET;

INSERT [ INTO] { table_name WITH (

Заполнение таблицы 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 ];

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

ПРИМЕРЫ: 1) SELECT * from USP WHERE SNUM = 3412 ; 2)SELECT SNUM, SFAM, SNAME FROM STUDENTS WHERE STIP>0; unum snum pnum udate mark 1001 3412 2001 10. 06. 200 2 5 1004 3412 2003 12. 06. 200 2 4 SNUM SFAM SNAME 3412 Поляков Анатолий 3413 Старова Любовь 3416 Нагорный Евгений

Показать номера студенческих билетов, фамилии и имена тех лиц, чьи имена начинаются с буквы «А» . SELECT SNUM, SFAM, SNAME FROM STUDENTS WHERE SNAME)<"Б"; SNUM SFAM SNAME 3412 Поляков Анатолий 3415 Котенко Анатолий

Пример 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 Математика

В записи логических условий могут быть использованы операторы 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 ) I N (4, 5))); SNUM UDATE MARK SFAM 3412 10. 06. 2002 5 Поляков 3413 10. 06. 2002 4 Старова 3412 12. 06. 2002 4 Поляков 3416 12. 06. 2002 5 Нагорный 3412 13. 06. 2002 4 Поляков

Пример 5. 4 Получить сведения о студентах, не получивших оценок 4 и 5. SELECT USP. SNUM, STUDENTS. SFAM, USP. UDATE, USP. MARK FROM USP , STUDENTS WHERE (((USP. SNUM)=[STUDENTS]. [SNUM]) AND ((USP. MARK) N OT I N (4, 5))); SNUM SFAM UDATE MARK 3414 Гриценко 11. 06. 2002 3 3414 Гриценко 12. 06.

Оператор BETWEEN используется для проверки условия вхождения значения поля в заданный интервал, т. е. задаются вместо списка границы. BETWEEN 20 AND 30 . Типы полей как числовые, так и символьные. Пример 5. 5 Показать список тех, кто получает стипендию в указанном диапазоне. SELECT STUDENTS. SNUM, STUDENTS. SFAM, STUDENTS. STIP FROM STUDENTS WHERE (((STUDENTS. STIP) B ETWEEN 20 A ND 30)); SNUM SFAM STIP 3412 Поляков 25, 50 3416 Нагорный 25,

Оператор LIKE применим только символьным полям типа CHAR или VARCHAR. Этот оператор просматривает строковые значения полей и определяет входит ли образец поиска в символьную строку-значение поля. В образце может использоваться шаблон: • Символ подчеркивания «_» определяет наличие 1 любого символа. • Символ % или * допускает наличие любых символов произвольной длины.

Пример 5. 7 Показать списки студентов с отчеством на « Ни*» . SELECT STUDENTS. SNUM, STUDENTS. SFAM, STUDENTS. SNAME, STUDENTS. SFATH FROM STUDENTS WHERE (((STUDENTS. SFATH) L IKE «Ни*»)); SNUM SFAM SNAME SFATH 3414 Гриценко Владимир Николаевич 3415 Котенко Анатолий Николаевич

Если внутри образца содержатся знаки _ | % | * | , то применяют 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 Физика 34 2002 Химия 68 2003 Математика 65 2004 Философия 17 2005 Экономика

Лекция № 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 SFAM STIP 3412 Поляков 31, 875 3413 Старова 21, 25 3416 Нагорный 31,

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,

3) Подготовить объединенный список преподавателей( конкатенация ). SELECT TEACHERS. TNUM AS КОД, TEACHERS. TFAM +» «+ TEACHERS. TNAME +» «+ TEACHERS. TFATH AS СПИСОК, TEACHERS. TDATE AS ПРИЕМ_НА_РАБОТУ FROM TEACHERS; КОД СПИСОК ПРИЕМ_НА_РАБОТУ 4001 Викулова Валентина Ивановна 01. 04. 1994 4002 Костенко Олег Владимирович 01. 09. 1997 4003 Казанцев Виталий Владимирович 01. 09. 1988 4004 Поздняк Любовь Алексеевна 01. 09. 1988 4005 Загоруйко Илья Дмитриевич 10. 05. 1999 4006 Федченко Светлана Геннадьевна 10. 06.

Функции преобразования символов в строке: • 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 ( { [ ALL | DISTINCT ] expression ] | * } ) SELECT COUNT(*) FROM USP; Аргументы DISTINCT и ALL позволяют, соответственно, исключать и включать дубликаты обрабатываемых функцией COUNT значений , ALL работает по умолчанию. SELECT COUNT(DISTINCT SNUM) FROM USP ; expression

• SUM вычисляет арифметическую сумму всех выбранных значений данного поля , игнорируя NULL значения ; • AVG вычисляет среднее значение для всех выбранных значений данного поля , игнорируя NULL значения ; • МАХ вычисляет наибольшее из всех выбранных значений поля; • MIN вычисляет наименьшее из всех выбранных значений поля.

• STDEV возвращает стандартное статистическое отклонение для каждого из выбранного набора; • STDEVP возвращает стандартное статистическое отклонение всей совокупности значений набора; • VAR возвращает статистическую вариацию значений в группе; • VARP возвращает статистическую вариацию значений всего набора.

П ример 4. 2 О предел ить средне е значени е поля MARK (оценки) по всем записям таблицы USP. Для получения нужных результатов можно использовать запрос с функцией AVG следующего вида: SELECT A VG (USP. MARK) AS Avg-MARK FROM USP; Avg-MARK 3,

Предложение GROUP BY (группировать по) позволяет группировать записи в подмножества, определяемые значениями какого-либо поля, и применять агрегирующие функции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе. Пример 2. Предположим, требуется найти максимальное значение оценки, полученной каждым студентом. Выбираемые из таблицы USP записи группируются по значениям поля SNUM , указанного в предложении GROUP BY , и для каждой группы находится максимальное значение поля MARK.

В приведенном запросе рассматриваются группы записей, объединенные по идентификаторам студентов. Запрос будет выглядеть сл едующим образом: SELECT SNUM, MAX(MARK) AS MAX_MARK FROM USP GROUP BY SNUM; SNUM MAX_MARK

Предложение GROUP BY позволяет применять агрегирующие функции к каждой группе, определяемой общим значением поля (или полей), указанных в этом предложении. Пример 4. 3. По итогам аттестации необходимо найти минимальную оценку по каждому предмету. SELECT USP. PNUM, M IN (USP. MARK) AS [Min-MARK] FROM USP GROUP BY USP. PNUM ; PNUM Min-MARK

Применение COUNT в группе SELECT usp. snum, Count([mark]) AS Выражение 1 FROM usp GROUP BY usp. snum Выражение 1 3412 2 3413 1 3414 1 3416 1 SELECT usp. snum, Count([mark]) AS Выражение 1 FROM usp GROUP BY usp. snum HAVING Count([mark])>1 ; snum Выражение

• Применение SUM в группе: SELECT usp. snum, sum([mark]) AS Выражение 1 FROM usp GROUP BY usp. snum ; snum Выражение 1 3412 9 3413 4 3414 3 3416 5 snum Выражение 1 3412 9 3416 5 SELECT usp. snum, sum([mark]) AS Выражение 1 FROM usp GROUP BY usp. snum HAVING sum([mark])> 4;

В конструкции GROUP BY для группирования может быть использовано более одного столбца. П ример 4. 4 : SELECT SNUM , PNUM, MAX(MARK) FROM USP GROUP BY SNUM , PNUM; SNUM PNUM MAX_MARK

В этом случае строки вначале группируются по значениям первого столбца, а внутри этих групп — в подгруппы по значениям второго столбца. Таким образом, GROUP BY не только устанавливает столбцы, по которым осуществляется группирование, но и указывает порядок разбиения столбцов на группы. Следует иметь в виду, что в предложении GROUP BY должны быть указаны все выбираемые столбцы, приведенные после ключевого слова SELECT, кроме столбцов, указанных в качестве аргумента в агрегирующей функции.

При необходимости часть сформированных с помощью GROUP BY групп может быть исключена с помощью предложения HAVING. Предложение HAVING определяет критерий, по которому группы следует включать в выходные данные, по аналогии с предложением WHERE , которое осуществляет это для отдельных строк. В условии, задаваемом предложением HAVIN G , указывают только поля или выражения, которые на выходе имеют единственное значение для каждой выводимой группы.

Пример 4. 5. Показать наименования предметов с количеством учебных часов более либо равным 30. SELECT PNAME, MAX(HOURS) AS _HOURS_ FROM PREDMET GROUP BY PNAME HAVING MAX( HOURS) >= 30; PNAME _HOURS_ Математика 65 Физика 34 Химия

Влияние 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 PNAME TNUM HOURS COURS 2003 Математика 4003 65 1 2001 Физика 4001 34 1 2004 Философия 4005 17 2 2002 Химия 4002 68 1 2005 Экономика

Тот же список, но упорядоченный в обратном порядке, можно получить запросом: 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 Математик а

Можно упорядочить выводимый список предметов обучения по значениям семестров, а внутри семестров — по наименованиям предметов. SELECT * FROM PREDMET ORDER BY COURS, PNAME; PNUM PNAME TNUM HOURS COURS 2003 Математика 4003 65 1 2001 Физика 4001 34 1 2002 Химия 4002 68 1 2004 Философия 4005 17 2 2005 Экономика

Предложение order by может использоваться с group by для упорядочения групп записей. При этом оператор order by в запросе всегда должен быть последним. SELECT PNAME, COUR S FROM PREDMET GROUP BY PNAME, COURS ORDER BY COURS DESC; PNAME COURS Экономика 3 Философия 2 Математика 1 Физика 1 Химия

При упорядочении вместо наименований столбцов можно указывать их номера, имея, однако, в виду, что в данном случае это номера столбцов, указанные при определении выходных данных в запросе, а не номера столбцов в таблице. Полем с номером 1 является первое поле, указанное в предложении order by — независимо от его расположения в таблице. SELECT PNAME, COUR S FROM PREDMET GROUP BY PNAME, COURS ORDER BY 1 DESC;

В этом запросе выводимые записи будут упорядочены полю COURS. Если в поле, которое используется для упорядочения, существуют NULL -значения, то все они размещаются в конце или предшествуют всем остальным значениям этого поля.

Вложенные запросы … Выполнение основного запроса при выполнении подзапроса (необратимость последовательности ).

Основные правила: • Подзапрос выбирает значение одного поля, тип которого совпадает с типом выражения; • Подзапрос может выбрать в качестве результата несколько значений, что может сделать невыполнимым оценку предиката; • Подзапрос может вообще не выбрать значений, то основной запрос не сможет оценить предикат (неизвестное значение рассматривается как ложное).

Пример 4. 6 • Выбрать из таблицы успеваемости те сведения, которые относятся к студенту Полякову. SELECT * from USP WHERE SNUM = (SELECT SNUM FROM STUDENTS WHERE SFAM = ‘ Поляков ’);

Результат: • Из подзапроса найденный номер SNUM подставляется в предикат; 412/06/02200334121004 510/06/02200134121001 MARKUDATEPNUMSNUMUNUM

Пример 4. 7 SELECT * from USP WHERE SNUM = (SELECT SNUM FROM STUDENTS WHERE SNAME = ‘ Анатолий ’); Выбрать сведения об оценках студента по имени Анатолий.

Результат: • Подзапрос получит несколько результатов 3412 и 3415 ; • Это делает невозможным оценку предиката в основном запросе; Выход: Заменить оператор отношения на IN .

SELECT * from USP WHERE SNUM IN (SELECT SNUM FROM STUDENTS WHERE SNAME = ‘ Анатолий ’); 412/06/02200334121004 510/06/02200134121001 MARKUDATEPNUMSNUMUNUM 1 2 Элементы 1 и 2 НЕ перемещаются

Использование агрегатных функций в подзапросах. • Единственное значение – БЕЗ группировки; • Несколько значений с группировкой.

Пример 4. 8 • Выбрать все записи, где оценка превышает среднюю по таблице. • SELECT * FROM USP • WHERE MARK > (SELECT AVG(MARK) • FROM USP);

• Подзапрос вычислит среднюю оценку 4. 2; • Основной запрос проверит условие MARK> 4. 2 Результат: MARKUDATE PNUMSNUMUNUM 512/06/02200434161005 2001 510/06/

• Выбрать все записи, где оценка превосходит либо равна средней по предмету c номером 2003. • SELECT * FROM USP • WHERE MARK > = (SELECT AVG(MARK) • FROM USP • GROUP BY PNUM • HAVING PNUM = 2003); Пример 4.

• Или с « WHERE PNUM=2003 » Результат: 512/06/02200434161005 412/06/02200334121004 410/06/02200334131002 510/06/02200134121001 MARK UDATEPNUMSNUMUNUM

• Для построения запросов с множественными значениями в подзапросе используют ОПЕРАТОР IN . • Операторы BETWEEN, LIKE, IS NULL в подзапросах не используются. Подзапрос строит множество значений для IN .

• Выбрать все записи из STUDENTS о студентах , оценки которых > 0; • SELECT * FROM STUDENTS • WHERE SNUM IN (SELECT DISTINCT SNUM • FROM USP WHERE MARK>0); Пример 4.

Результат: • Подзапрос выдает (3412, 3413, 3414, 3416); • Вынимает SNUM из STUDENTS и ищет в списке (). 25, 50 Васильевич Евгений. Нагорный 3416 0, 00 Николаевич Владимир. Гриценко 3414 17, 00 Михайловна Любовь. Старова 3413 25, 50 Алексеевич Анатолий. Поляков 3412 stipsfatsnamesfamsnum Этот запрос выполняется быстрее, чем соединение.

Аналог – соединение: • 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=‘ философия ’) ; 1654003 Математика 2003 COURSHOURSTNUMPNAMEPNUM

Использование подзапросов в группах: • Пример 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; 42 52 MARKEXPRESSION

Связанные подзапросы: Пример 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.

Использование связанных подзапросов в группах: Пример 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 EXPRESSION 10/06/02 4. 5 12/06/02 4.

Обновление имеющихся записей UPDATE { table_name WITH (

UPDATE authors SET authors. au_fname = ‘Annie’ WHERE au_fname = ‘Anne’ Синтаксис where: WHERE — логические условия, в соединение с 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 *

Удаление DELETE [ FROM ] { table_name WITH (

DELETE authors DELETE FROM authors WHERE au_lname = ‘Mc. Badden’ Для полной очистки таблицы лучше использовать truncate TRUNCATE TABLE authors

Выборка 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 ‘Mc. Badden’ ORDER BY au_lname ASC, au_fname ASC SELECT 1, ‘qq’

SELECT ytd_sales AS Sales, authors. au_fname + ‘ ‘+ authors. au_lname AS Author, To. Author = (ytd_sales * royalty) / 100, To. Publisher = 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 AS

Использование JOIN’ ов • Join’ ы ( объединение таблиц по некоторому условию / условиям ) могут содержаться во FROM или WHERE частях запроса • Join’ ы могут содержаться в SELECT, UPDATE и DELETE запросах • Результатом любого join’ а является некая таблица

Синтаксис SELECT | INSERT | UPDATE select list | update list | delete list FROM Table 1 Name INNER|LEFT OUTER|RIGHT OUTER|FULL|CROSS JOIN Table 2 Name [ON join condition] INNER|LEFT OUTER|RIGHT OUTER|FULL|CROSS JOIN Table 3 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] FOREIGN KEY ( [Parent_ID] ) REFERENCES [Objects] ( [ID] ) ) ON [PRIMARY]

SELECT O 1. [ID], O 1. [Name], O 2. [ID] as Child. ID, O 2. [Name] as Child. Name FROM Objects O 1 INNER JOIN Objects O 2 ON O 1. [ID] = O 2. [Parent_ID] WHERE O 1. Parent_ID IS NULL ORDER BY O 1. [ID], Child. ID SELECT O 1. [ID], O 1. [Name], O 2. [ID] as Child. ID, O 2. [Name] as Child. Name FROM Objects O 1 LEFT OUTER JOIN Objects O 2 ON O 1. [ID] = O 2. [Parent_ID] WHERE O 1. Parent_ID IS NULL ORDER BY O 1. [ID], Child. I

SELECT O 1. [ID], O 1. [Name], O 2. [ID] as Child. ID, O 2. [Name] as Child. Name FROM Objects O 1 FULL JOIN Objects O 2 ON O 1. [ID] = O 2. [Parent_ID] WHERE O 1. Parent_ID IS NULL ORDER BY O 1. [ID], Child. ID SELECT O 1. [ID], O 1. [Name], O 2. [ID] as Child. ID, O 2. [Name] as Child. Name FROM Objects O 1 CROSS JOIN Objects O 2 WHERE O 1. Parent_ID IS NULL ORDER BY O 1. [ID], Child. I

Удалим все объекты второго уровня: DELETE O 2 FROM Objects O 1 INNER JOIN Objects O 2 ON O 1. [ID] = O 2. [Parent_ID] WHERE O 1. Parent_ID IS NULL Добавим всем объектам в имя суффикс, содержащий ID родителя: UPDATE O 2 SET [Name] = O 2. [Name] + ‘_’ + CAST( O 1. [ID] AS VARCHAR ) FROM Objects O 1 INNER JOIN Objects O 2 ON O 1. [ID] = O 2. [Parent_ID] WHERE O 1. 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 – язык определения данных. В SQL существует ряд операторов, позволяющих изменять структуру данных . Операторы DDL позволяют не вникать в детали хранения информации в БД на физическом уровне, используя стандартные понятия поля и таблицы.

Это операции: 1 )создание новой БД; 2 )определение новой структуры и создание таблицы; 3 ) удаление таблицы; 4 ) и зменение структуры существующей таблицы; 5) обеспечение условий безопасности; 6 )создание индексов для доступа к таблице; 7 ) у правление размещением данных на устройствах.

Основу DDL составляют три команды: 1) CREATE — создать; 2) DROP – удалить; 3) ALTER – изменить.

Использование этих команд во время работы позволяет сделать структуру динамичной. Другими словами , в СУБД можно создавать, удалять таблицы, одновременно обеспечивая доступ к данным. Это означает, что БД с течением времени может расти и изменяться и её эксплуатация может продолжаться в то время, когда в нее вносятся новые таблицы и приложения. Операторы можно использовать, как в интерактивном, так и в программном режимах.

Создание базы данных. В системе MS SQL эти действия выполняются оператор о м: CREATE DATABASE ON , , ;

CREATE DATABASE database_name [ ON [ [ , . . . n ] ] [ , [ , . . . n ] ] ] [ LOG ON { [ , . . . n ] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ] : : = [ PRIMARY ] ( [ NAME = logical_file_name , ] FILENAME = ‘ os_file_name ‘ [ , SIZE = size ] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ , . . . n ] : : = FILEGROUP filegroup_name [ , . . . 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 = 5 MB, MAXSIZE = 25 MB, FILEGROWTH = 5 MB ) GO Создание БД со спецификациями данных и журнала.

USE master GO CREATE DATABASE Archive ON PRIMARY ( NAME = Arch 1, FILENAME = ‘c: \program files\microsoft sql server\mssql\data\archdat 1. mdf’, SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch 2, FILENAME = ‘c: \program files\microsoft sql server\mssql\data\archdat 2. ndf’, SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch 3, FILENAME = ‘c: \program files\microsoft sql server\mssql\data\archdat 3. ndf’, SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = Archlog 1, FILENAME = ‘c: \program files\microsoft sql server\mssql\data\archlog 1. ldf’, SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Archlog 2, FILENAME = ‘c: \program files\microsoft sql server\mssql\data\archlog 2. ldf’, SIZE = 100 MB, 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 = 5 MB, MAXSIZE = 25 MB, FILEGROWTH = 5 MB ) GO

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

| [ [ 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 ) }

1) Для разделения элементов команды используются пробелы, поэтому пробел не может быть частью имени ( MY _Table). 2) Значение аргумента размерность [] – зависит от типа данных и может заноситься по умолчанию самой СУБД. Это удобно для числовых полей. Тип 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 ( low int, high int, myavg AS (low + high)/2 ) Уникальность:

Пример. Создать таблицу S TUDENTS. CREATE TABLE STUDENTS (SNUM INTEGER, SFAM CHAR (20), SNAME CHAR (15), SFATH CHAR (15), STIP DECIMAL) 1. В этой команде порядок полей определяется их местом в списке. 2. После того, как таблица создана, её можно изменить.

Добавление новых полей выполняется командой: ALTER TABLE ADD [()], … [()]); Добавляемые поля автоматически получают значения NULL.

Пример. Предположим мы решили добавить номер курса и специальность. ALTER TABLE STUDENTS ADD COURS INTEGER, SPEC CHAR (20);

Наиболее частые изменения связаны с изменением размера поля. Необходимо убедиться, что изменения не приведут к потере информации, поэтому необходимо лучше проектировать таблицу и вносить изменения только в крайнем случае. ALTER TABLE MODYFI [()], … [()]); Изменения возможны , если столбец пуст; Для заполненного столбца изменяется размер/точность в сторону увеличения; Установка ограничения NOT NULL возможна при отсутствии значений NULL ; Разрешается изменять значения устанавливаемые по умолчанию.

Для удаления таблицы необходимо: 1) быть ее создателем или иметь на это право; 2) перед удалением необходимо ее очистить от данных, это позволяет избежать случайной потери информации. DROP TABLE ; Пример. Удалить все сведения и таблицу PREDMET. 1. DELETE FROM PREDMET; 2. DROP TABLE PREDMET;

INSERT [ INTO] { table_name WITH (

Пример10. 3. Создать для пользователя копию таблицы PREDMET , добавить в нее пол я: лабораторные работы, их количество. CREATE TABLE PREDMET_NEW ( PNUM INTEGER, PNAME CHAR (30), COURS INTEGER, HOURS INTEGER, LAB CHAR (30), NUM IN T EGER ) ;

INSERT INTO PREDMET_NEW SELECT * FROM PREDMET; Новые поля заполнятся значениями по умолчанию или значениями NULL. PNUM PNAME TNUM COURS HOURS LAB NUM 2001 Физика 4001 1 34 0 2002 Химия 4002 1 68 0 2003 Математика 4003 1 65 0 2004 Философия 4005 2 17 0 2005 Экономика

Индексы, ограничения, синонимы. Индексом принято называть упорядоченный список полей таблицы или групп полей в таблице. В таблице с большим количеством полей при отсутствии упорядоченности поиск может занимать длительное время. Индексный адрес – это специальный метод объединения всех значений в группы( из 1 или более записей), которые отличаются друг от друга, т. к. уникальность записей часто необходима.

Когда создаётся индекс, в поле БД запоминается порядок всех значений этого поля в области памяти. При наличии индекса система могла бы найти искомый номер прямо в этом упорядоченном массиве и указать, какую искомую строку следует найти. У индексов есть и недостатки : 1)наличие индексов замедляет операции модификации INSERT , DELETE; 2)сам индекс занимает тоже место в памяти.

Индексы могут состоять из нескольких полей, при этом первое поле считается главным, второе поле упорядоченным внутри первого и т. д. Создаются индексы командой: CREATE INDEX on (, [, …]); Разумеется, таблица должна быть создана ранее, и иметь имена полей указанных в команде. Имя индекса является уникальным и не может быть использовано в других целях. SQL сам определяет, когда индекс необходим и использует его автоматически.

Пример 11. 1. В таблице STUDENT наиболее часто употребимо поле SFAM , создать индекс по этому полю. CREATE INDEX SFAMIDX on STUDENTS( SFAM); При создании индекса ему не приписана уникальность. Это делается с помощью специального ключевого слова UNIQUE. CREATE UNIQUE INDEX SNUMIDX ON STUDENTS( SNUM); Однако эта команда не будет выполнена, если среди значений этого поля есть не уникальные значения. Поэтому рекомендуем создавать индексы сразу после создания её структуры, до ввода в неё значений.

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

Для удаления используется команда: DROP INDEX ; Например: DROP INDEX SFAMIDX; Удаление индексов не влияет на данные.

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

Существуют ограничения двух типов: 1) ограничения поля – применимые только к указанному полю; 2) ограничения таблицы – применимые к указанным группам полей. Ограничения поля (атрибута) – помещается в конец фрагмента команды, объявляющего имя после типа данных. Ограничения таблицы (отношения) – помещаются в конец объявления имени таблицы последнего имени поля.

CREATE 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, 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, SNUM INTEGER NOT NULL, PNUM INTEGER NOT NULL, UNIQUE (SNUM, PNUM) );

Ограничение первичного ключа может применяться для группы. CREATE TABLE USP (UNUM INTEGER NOT NULL UNIQUE , MARK INTEGER, UDATE, SNUM 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, 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, 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, SNUM INTEGER NOT NULL, PNUM INTEGER NOT NULL, CHECK ( (MARK 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, SNUM INTEGER NOT NULL, PNUM INTEGER NOT NULL);

Использование значений по умолчанию это альтернатива NULL , т. к. неизвестное значение неверно при любом сравнении (кроме IS NULL ), такие записи исключают большинство предикатов. Иногда нужно вводить пустые поля, не обрабатывая их каким либо образом: 1) числовые – 0; 2) символьные – пробел. В этом случае записи обрабатываются как любые другие.

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

Если пользователю разрешен доступ хотя бы к одному полю таблицы, то он имеет право создать синоним. CREATE SYNONYM FOR ; Например , 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 ; 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 = ’employee. ID_ind’) DROP INDEX emp_pay. employee. ID_ind GO USE pubs GO CREATE TABLE emp_pay ( employee. ID int NOT NULL, base_pay money NOT 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 employee. ID_ind ON emp_pay (employee. ID) 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 Teнhers go Используя проводник, можно построить запрос более детально: 1) Наберите инструкцию Select и в дереве объектов откройте таблицу Students и объект Colomns , в котором выберите нужные поля( или перетащите всю папку после слова Select) ; Select Snum, Sfam, Sname, Stip from Students

Сценарий создания таблицы, заполнения, просмотра: CREATE TABLE AVGRAITING(AUNUM INTEGER NOT NULL UNIQUE, AVGMARK INTEGER, UDATE, SNUM INTEGER, PNUM INTEGER) GO INSERT INTO AVGRAITING (SNUM, AVGMARK) SELECT SNUM, AVG (MARK) FROM USP GROUP BY SNUM GO SELECT SNUM, AVG MARK FROM AVGRAITING GO

Для выполнения сценария на панели инструментов выбирается кнопка Execute Query AVGRAITING SNUM AVG MARK 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 My. Temp. Table (cola INT PRIMARY KEY) INSERT INTO My. Temp. Table VALUES (1) CREATE TABLE #My. Temp. Table (cola INT PRIMARY KEY) INSERT INTO #My. Temp. Table VALUES (1) CREATE TABLE # # My. Temp. Table (cola INT PRIMARY KEY) INSERT INTO # # My. Temp. Table 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 T 2( x int, s varchar(20) ) insert into T 2 (x, s) values(1, ‘test’) select top 1 @s=s, @x=x from T 2 возможен также вариант с использованием set @s=(select top 1 s from T 2)

Использование системной функции @@IDENTITY create table T 3( x int identity(1, 1), s varchar(20) ) insert into T 3 (s) values(‘test’) set @x = @@identity print @x в @x будет записано последнее значение, которое было присвоено полю x автоматически

Использование значения переменных в конструкциях insert и update set @x=4 set @s=’qwerty’ insert into T 2 (x, s) values(@x, @s) insert into T 2 (x, s) select @x, @s update T 2 set s=’123′ where x=@x insert into T 2 (x, s) select x, @s from T 3 переменные могут использоваться в правой части равенств блоков 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 @my. Charvar varchar(50) SET @my. Charvar=‘Hello!’ или SELECT @my. Charvar=‘Hello!’ SELECT @my. Charvar=max(sfam) from STUDENTS INSERT INTO @my. Charvar select tfam from Teacher INSERT INTO @my. Charvar 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 — открывает курсор CLOSE — очищает result set, однако курсор остается доступным для открытия, при каждом открытии result set будет переформирован DEALLOCATE — уничтожает курсор

Передвижение по курсору 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 FOR SELECT , . . . , FROM [WHERE ] ;

DECLARE CURSOR STIPCUR FOR SELECT SNUM, SFAM, STIP FROM STUDENTS WHERE STIP > 0; • Этот запрос не выполняется немедленно, т. к. в данном случае это лишь его определение.

• Курсор похож на представление, его содержательной частью являются данные запроса. Это становится возможным после открытия курсора. Его строки в отличие от базовых таблиц и представлений строго упорядочены, имеется 1 строка, 2 строка и т. д. • Может быть явно задан ORDER BY в запросе.

• Если в программе необходимо выполнить запрос, то открывается курсор: • EXEC SQL OPEN CURSOR STIPCUR; • Значения передаются только тогда, когда выполнена эта команда. • Для извлечения данных по одной строке используется команда • FETCH INTO , …, ;

• Пример. • В нашем случае для курсора, объявленного командой: • 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 ; • После его использования вывод нужно повторить новым открытием курсора.

• 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 (

Вставка строк данных – INSERT [ INTO] [ (список полей) ] VALUES (список значений)| или ; Пример 9. 1 Необходимо добавить новые сведения в таблицу TEACHERS. INSERT INTO TEACHERS (TNUM, TFAM, TNAME, TFATH, TDATE) VALUES (4006, ‘ Федченко ’ , ‘ Светлана ’ , ‘ Геннадьевна ’ , 01. 09. 0 2 );

TNUM TFAM TNAME TFATH TDATE 4001 Викулова Валентина Ивановна 01 / 04 / 1994 4002 Костенко Олег Владимирови ч 01 / 09 / 1997 4003 Казанцев Виталий Владимирови ч 01 — 09 — 198 8 4004 Поздняк Любовь Алексеевна 01. 09. 1988 4005 Загоруйко Илья Дмитриевич 10. 05. 1999 4006 Федченко Светлана Геннадьевна 10. 06. 2001 TEACHERSДля просмотра результатов: SELECT * FROM TEACHERS;

1) Команда не осуществляет никакого вывода информации, поэтому следует контролировать: а) правильность имени таблицы; б) совпадение типов и количества значений указанным полям. 2) Допускается частичное указание полей в произвольном порядке: INSERT INTO TEACHERS (TDATE, TFAM, TNAME, TNUM) VALUES (01 / 09 / 0 2 , ’ Федченко ’ , ‘ Светлана ’ , 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, MARK INTEGER );

Пример 9. 3 CREATE TABLE AVGRAITING (AUNUM INTEGER NOT NULL UNIQUE , AVGMARK INTEGER, UDATE, SNUM INTEGER, PNUM INTEGER); Заполнить таблицу, имеющую такую же структуру как успеваемость, с указанием среднего балла студента в сессию в графе оценка. INSERT INTO AVGRAITING (SNUM, AVGMARK) SELECT SNUM, AVG (MARK) FROM USP GROUP BY SNUM;

AVGRAITING SNUM AVG MARK 3412 4, 5 3413 4 3414 3 3416 5 SELECT SNUM, AVG MARK 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 [FROM] [ ] ; Эта команда осуществляет удаление строк-записей из указанной таблицы. !!! При этом удаляется только целая запись, параметры поля в этой команде не доступны. Пример 9. 6 А) Удалить все сведения из таблицы STUDENTS. DELETE FROM STUDENTS;

Так как SNUM — первичный ключ, то удаляется 1 запись. Если бы в качестве условия выбиралась бы фамилия, то при наличии однофамильцев удалились бы все сведения о них (то есть несколько записей). DELETE FROM STUDENTS WHERE S FAM = ‘ Грицецко ’; Б) Удалить сведения о студенте Котенко. DELETE FROM STUDENTS WHERE SNUM = 3415;

Пример 9. 7 Удалить сведения об оценках за 10. 06. 0 2 (экзамен аннулирован). 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.

Допускается использование вложенных запросов. Чаще всего это необходимо, когда критерий базируется на другой таблице. Пример 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 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 SNUM PNUM UDATE MARK 1001 3412 2001 10. 06. 2002 5 1002 3413 2003 10. 06. 2002 8 1003 3414 2005 11. 06. 2002 3 1004 3412 2003 12. 06. 2002 8 1005 3416 2004 12. 06.

Пример 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 SNUM PNUM UDATE MARK 1001 3412 2001 10. 06. 2002 5 1002 3413 2003 10. 06. 2002 1003 3414 2005 11. 06. 2002 3 1004 3412 2003 12. 06. 2002 1005 3416 2004 12. 06.

Мощным средством является использование подзапросов в команде UPDATE. При этом необходимо соблюдение следующих правил: 1) в предложении FROM любого подзапроса нельзя модифицировать таблицу, на которую ссылается основная команда ; 2) может возникнуть проблема дублирования строк, полученных в результате вложенного подзапроса; (в таблице могут быть заданы ограничения по уникальности, это выясняется с использованием предложения EXISTS )

Пример 9. 14 Увеличить стипендию студентам, сдавшим 2 и более предметов без троек. UPDATE STUDENTS SET STIP=1. 1*STIP WHERE 23 ) ) ; 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. 1 6 Модифицируем стипендию в зависимости от величины минимального балла в тот или иной день. 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 attributes), …] AS ; Пример 12. 1. Создать представление о студентах, получающих стипендию в размере 25. 50. CREATE VIEW STIP 25_50 AS SELECT * FROM STUDENTS WHERE STIP=25. 50;

Теперь в БД существует представление STIP 25_50. Это такая же таблица, как и остальные. С ней можно выполнять запросы, изменения, вставки как с другими таблицами. При выполнении запроса к ней: SELECT * FROM STIP 25_50; будет получена таблица.

SNUM SFAM SNAME SFATH STIP 3412 Поляков Анатолий Алексеевич 25, 5 0 3416 Нагорный Евгений Васильевич 25, 5 0 Если к ней обратиться с запросом с предикатом SELECT * FROM STIP 25_50 WHERE SFAM<’ П ’; То будет получен результат: SNUM SFAM SNAME SFATH STIP 3416 Нагорный Евгений Васильевич 25,

При создании представлений можно часть информации скрыть. Пример 12. 2. Создать представление о студентах без указания стипендии. CREATE VIEW STIPOFF AS SELECT SNUM, SFAM, SNAME, SFATH FROM STUDENTS;

При выполнении запроса к ней, будет получена таблица : SNUM SFAM SNAME SFATH 3412 Поляков Анатолий Алексееви ч 3413 Старова Любовь Михайловн а 3414 Гриценко Владими р Николаеви ч 3415 Котенко Анатолий Николаеви ч 3416 Нагорны й Евгений Васильеви ч

Представление теперь может изменяться также как и таблица, фактически же команда направлена к таблице 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 COUNT(*)>=2; STIP NUM 0, 00 2 25,

В SQL существует понятие групповых представлений, т. е. имеющих предложение GROUP BY или основанных на других групповых представлениях. CREATE VIEW STIPCOUNT (STIP, NUM) AS SELECT STIP, COUNT(*) FROM STUDENTS GROUP BY STIP; STIP NUM 0, 00 2 17, 00 1 25,

Представления могут основываться на двух и более таблицах. Пример 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 Поляков Экономика

Допускается соединение представления с базовыми таблицами: SELECT SFAM, PNAME, MARK, UDATE FROM STUDMARK A, USP B WHERE A. SFAM= ‘ Поляков ’ AND A. UNUM=B. UNUM;

Результат работы запроса: SFAM PNAME MARK UDATE Поляков Физика 5 10. 06. 200 2 Поляков Математик а 4 12. 06. 200 2 Поляков Экономика 4 13. 06.

Представления допускают соотнесенные подзапросы. Пример12. 6. Пусть в таблице USP UNUM SNUM PNUM UDATE MARK 1001 3412 2001 10. 06. 2002 5 1002 3413 2003 10. 06. 2002 4 1003 3414 2005 11. 06. 2002 3 1004 3412 2003 12. 06. 2002 4 1005 3416 2004 12. 06. 2002 5 1006 3414 2001 12. 06. 2002 2 1007 3412 2005 13. 06.

Создадим представление об оценках превышающих среднюю. 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 SNUM PNUM UDATE MARK 1001 3412 2001 10. 06. 2002 5 1007 3412 2005 13. 06. 2002 4 Из этих примеров следует, что представления значительно облегчают работу с данными. Однако они являются чаще всего объектами доступными для чтения.

Существуют ограничения на построения представлений: 1) в них не допускаются объединения UNION запросов; 2) не допустимо упорядочение ORDER BY , так как в базовых таблицах записи не упорядочены.

Для удаления представлений используется команда DROP 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 ONLY 5 AS SELECT SNUM, MARK FROM USP WHERE MARK = 5; 2)INSERT INTO ONLY 5 VALUES (3415, 4 ); Это допустимая команда, в таблицу эти значения будут вставлены, но на экране не появятся.

Таким образом, в таблице могут появляться данные не видимые пользователю. Для исключения таких моментов используется предложение: WITH CHECK OPTION.

Если его добавить к команде: CREATE VIEW ONLY 5 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)однако в NEW 1 она возможна. CREATE VIEW NEW 1 AS SELECT * FROM STIPSTUD;

Вставка выполнится. Это означает, что любое корректное представление модифицируемо. Даже если: CREATE VIEW NEW 1 AS SELECT * FROM STIPSTUD WITH CHECK OPTION ;

Стандарт Input/Output** Default mon dd yyyy hh: mi. AM (or PM) USA mm/dd/yy ANSI yy. mm. dd German/ British /French / Italian dd/mm/yy d d — 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 attributes), …] AS ; Пример 12. 1. Создать представление о студентах, получающих стипендию в размере 25. 50. CREATE VIEW STIP 25_50 AS SELECT * FROM STUDENTS WHERE STIP=25. 50;

Теперь в БД существует представление STIP 25_50. Это такая же таблица, как и остальные. С ней можно выполнять запросы, изменения, вставки как с другими таблицами. При выполнении запроса к ней: SELECT * FROM STIP 25_50; будет получена таблица.

SNUM SFAM SNAME SFATH STIP 3412 Поляков Анатолий Алексеевич 25, 5 0 3416 Нагорный Евгений Васильевич 25, 5 0 Если к ней обратиться с запросом с предикатом SELECT * FROM STIP 25_50 WHERE SFAM<’ П ’; То будет получен результат: SNUM SFAM SNAME SFATH STIP 3416 Нагорный Евгений Васильевич 25,

При создании представлений можно часть информации скрыть. Пример 12. 2. Создать представление о студентах без указания стипендии. CREATE VIEW STIPOFF AS SELECT SNUM, SFAM, SNAME, SFATH FROM STUDENTS;

При выполнении запроса к ней, будет получена таблица : SNUM SFAM SNAME SFATH 3412 Поляков Анатолий Алексееви ч 3413 Старова Любовь Михайловн а 3414 Гриценко Владими р Николаеви ч 3415 Котенко Анатолий Николаеви ч 3416 Нагорны й Евгений Васильеви ч

Представление теперь может изменяться также как и таблица, фактически же команда направлена к таблице 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 COUNT(*)>=2; STIP NUM 0, 00 2 25,

В SQL существует понятие групповых представлений, т. е. имеющих предложение GROUP BY или основанных на других групповых представлениях. CREATE VIEW STIPCOUNT (STIP, NUM) AS SELECT STIP, COUNT(*) FROM STUDENTS GROUP BY STIP; STIP NUM 0, 00 2 17, 00 1 25,

Представления могут основываться на двух и более таблицах. Пример 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 Поляков Экономика

Допускается соединение представления с базовыми таблицами: SELECT SFAM, PNAME, MARK, UDATE FROM STUDMARK A, USP B WHERE A. SFAM= ‘ Поляков ’ AND A. UNUM=B. UNUM;

Результат работы запроса: SFAM PNAME MARK UDATE Поляков Физика 5 10. 06. 200 2 Поляков Математик а 4 12. 06. 200 2 Поляков Экономика 4 13. 06.

Представления допускают соотнесенные подзапросы. Пример12. 6. Пусть в таблице USP UNUM SNUM PNUM UDATE MARK 1001 3412 2001 10. 06. 2002 5 1002 3413 2003 10. 06. 2002 4 1003 3414 2005 11. 06. 2002 3 1004 3412 2003 12. 06. 2002 4 1005 3416 2004 12. 06. 2002 5 1006 3414 2001 12. 06. 2002 2 1007 3412 2005 13. 06.

Создадим представление об оценках превышающих среднюю. 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 SNUM PNUM UDATE MARK 1001 3412 2001 10. 06. 2002 5 1007 3412 2005 13. 06. 2002 4 Из этих примеров следует, что представления значительно облегчают работу с данными. Однако они являются чаще всего объектами доступными для чтения.

Существуют ограничения на построения представлений: 1) в них не допускаются объединения UNION запросов; 2) не допустимо упорядочение ORDER BY , так как в базовых таблицах записи не упорядочены.

Для удаления представлений используется команда DROP 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 ONLY 5 AS SELECT SNUM, MARK FROM USP WHERE MARK = 5; 2)INSERT INTO ONLY 5 VALUES (3415, 4 ); Это допустимая команда, в таблицу эти значения будут вставлены, но на экране не появятся.

Таким образом, в таблице могут появляться данные не видимые пользователю. Для исключения таких моментов используется предложение: WITH CHECK OPTION.

Если его добавить к команде: CREATE VIEW ONLY 5 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)однако в NEW 1 она возможна. CREATE VIEW NEW 1 AS SELECT * FROM STIPSTUD;

Стандарт Input/Output** Default mon dd yyyy hh: mi. AM (or PM) USA mm/dd/yy ANSI yy. mm. dd German/ British /French / Italian dd/mm/yy d d — 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 сервер обращается к журналу транзакций и производит изменения. О соответствии транзакции ACID заботится разработчик.

Запуск транзакции SQL сервер позволяет запустить явную, автоматически совершаем у ю или неявную транзакцию • E xplicit ( явная ) транзакция предваряется выражением 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 Implicit. Tran (Cola int PRIMARY KEY, Colb char(3) NOT NULL) SET IMPLICIT_TRANSACTIONS ON /* Первая implicit транзакция начнется конструкцией INSERT*/ INSERT INTO Implicit. Tran VALUES (1, ‘aaa’) INSERT INTO Implicit. Tran VALUES (2, ‘bbb’) /* Commit first transaction */ COMMIT TRANSACTION /* Вторая implicit транзакция начнется конструкцией SELECT */ SELECT COUNT(*) FROM Implicit. Tran INSERT INTO Implicit. Tran VALUES (3, ‘ccc’) SELECT * FROM Implicit. Tran /* Commit second transaction */ COMMIT TRANSACTION SET IMPLICIT_TRANSACTIONS O

В autocommit режиме в случае возникновения ошибки компиляции SQL сервер делает rollback всему пакету инструкций. При возникновении runtime error откат не производится, не выполняется лишь инструкция, в которой произошел runtime error: —example 1( autocommit ошибка компиляции ) CREATE TABLE Test. Batch (Cola INT PRIMARY KEY, Colb CHAR(3)) INSERT INTO Test. Batch VALUES (1, ‘aaa’) INSERT INTO Test. Batch VALUES (2, ‘bbb’) INSERT INTO Test. Batch VALUSE (3, ‘ccc’) /* Syntax error */ SELECT * FROM Test. Batch /* Returns no rows */ —example 2( autocommit ошибка runtime error ) CREATE TABLE Test. Batch (Cola INT PRIMARY KEY, Colb CHAR(3)) INSERT INTO Test. Batch VALUES (1, ‘aaa’) INSERT INTO Test. Batch VALUES (2, ‘bbb’) INSERT INTO Test. Batch VALUES (1, ‘ccc’) /* Duplicate key error */ SELECT * FROM Test. Batch /* Returns rows 1 and 2 */

В SQL Server контроль имени объекта производится в (во время выполнения) execution time. Поэтому в следующем примере будет runtime error, а не compile error: USE pubs GO CREATE TABLE Test. Batch (Cola INT PRIMARY KEY, Colb CHAR(3)) GO INSERT INTO Test. Batch VALUES (1, ‘aaa’) INSERT INTO Test. Batch VALUES (2, ‘bbb’) INSERT INTO Test. Bch VALUES (3, ‘ccc’) /* Table name error */ GO SELECT * FROM Test. Batch /* 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 до

Схема о бработк и ошибок в 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 instead of trigger выполняется if exists… select… insert… … … no триггер внес измене- ния в исходную таблицуcheck constraints yes log statement заполняются таблицы inserted и deleted вызываются for/after триггеры commit tranno All done!Последовательность работы триггеров

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_trig 1 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_Firms. Insert 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, Deps. Init DI GO

CREATE TRIGGER tri_Firms. Update 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_Firms. Delete ON Firms FOR Delete AS DELETE T FROM Deps T, DELETED D WHERE T. F_ID = D. F_ID GO

Пример 3 CREATE TRIGGER Customer. Has. Orders ON Customers FOR DELETE AS IF EXISTS ( SELECT * FROM DELETED D INNER JOIN ORDERS O ON D. Customer. ID = O. Customer. ID ) BEGIN RAISERROR(‘Customer has order history, delete failed’, 16, 1) ROLLBACK TRAN EN

• Оператор return прекращает выполнение триггера • Триггер может быть рекурсивным (по умолчанию это отключено в настройках) • Глубина рекурсии не более 32, для определения глубины рекурсии ( nested level) можно использовать такую конструкцию: • trigger_nestlevel(object_id( ) ) • М ожно настроить каскадный вызов триггеров – при выполнении каких-то операций над таблицами базы внутри триггера будут вызываться триггеры, соответ ст вующие этим операциям. • Используя INSTEAD OF триггеры можно изменять информацию в таблицах через VIEW

CREATE VIEW dbo. File. Table 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_File. Table. Update ON File. Table 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 Обновление данных триггером через view

Пример: CREATE TABLE Nested. Test ( NT_ID int NOT NULL , NT_Name varchar (50) NOT NULL , NT_Parent int NULL , CONSTRAINT [PK_Nested. Test] PRIMARY KEY CLUSTERED ( [NT_ID] ) ON [PRIMARY] GO

INSERT INTO Nested. Test(NT_ID, NT_Name) VALUES (1, ‘Obj 1’) DECLARE @i int SET @i = 1 WHILE ( @i <= 50 ) BEGIN INSERT INTO Nested. Test(NT_ID, NT_Name, NT_Parent) VALUES (@i+1, 'Obj' + CAST(@i AS varchar), @i ) SET @i = @i + 1 END GO

ALTER TRIGGER tri_Nested. Test. Delete ON Nested. Test FOR DELETE AS IF @@ROWCOUNT = 0 RETURN PRINT trigger_nestlevel( object_id( ‘tri_Nested. Test. Delete’ ) ) IF ( trigger_nestlevel( object_id( ‘tri_Nested. Test. Delete’ ) ) > 31 ) RETURN DELETE T FROM Nested. Test T, DELETED D WHERE D. NT_ID = T. NT_Parent GO DELETE FROM Nested. Test WHERE NT_ID = 1 SELECT * FROM Nested. Test

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 [ [, ]. . . 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 [ [, ]. . . n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ] Синтаксис

Пример 1 CREATE FUNCTION Cubic. Volume — Входные размеры в сантиметрах. (@Cube. Length decimal(4, 1), @Cube. Width decimal(4, 1), @Cube. Height decimal(4, 1) ) RETURNS decimal(12, 3) — Cubic centimeters. AS BEGIN RETURN ( @Cube. Length * @Cube. Width * @Cube. Height ) END SELECT Cubic. Volume(1, 2, 3) В качестве параметров естественно могут выступать названия полей, а не только константы или переменные

Хранимые процедуры • «трехзвенная архитектура» — имеется хранилище данных (1 -е звено), имеется сервер приложений (2 -е звено), который выбирает из этого хранилища данные и определенным образом эти данные обрабатывает и после обработки конечный результат уже посылает на терминал клиента (3 -е звено). • «клиент-сервер» — имеется хранилище данных (сервер) и клиент, который с этого сервера выбирает данные с помощью определенного языка запросов (SQL) (Устаревший взгляд, возвращаюший нас во времена СУБД типа Fox. PRO со встроенной поддержкой 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 Master 1 ( Master 1 ID int IDENTITY (1, 1) NOT NULL, Detail 1 ID int NULL, Detail 2 ID int NULL, Name varchar(200), CONSTRAINT PK_Master 1 PRIMARY KEY CLUSTERED (Master 1 ID) ) GO

CREATE TABLE Detail 1 ( Detail 1 ID int IDENTITY (1, 1) NOT NULL, Name varchar(200), CONSTRAINT PK_Detail 1 PRIMARY KEY CLUSTERED (Detail 1 ID) ) GO CREATE TABLE Detail 2 ( Detail 2 ID int IDENTITY (1, 1) NOT NULL, Name varchar(200), CONSTRAINT PK_Detail 2 PRIMARY KEY CLUSTERED (Detail 2 ID) ) GO

insert Detail 1 (Name) values(‘Рабочий’) insert Detail 1 (Name) values(‘Инженер’) insert Detail 1 (Name) values(‘Дворник’) insert Detail 1 (Name) values(‘Программист’) GO insert Detail 2 (Name) values(‘высшее’) insert Detail 2 (Name) values(‘среднее’) insert Detail 2 (Name) values(‘неполное высшее’) insert Detail 2 (Name) values(‘кандидат наук’) GO insert Master 1 (Detail 1 ID, Detail 2 ID, Name) values(1, 1, ‘Иванов’) insert Master 1 (Detail 1 ID, Detail 2 ID, Name) values(2, 2, ‘Петров’) insert Master 1 (Detail 1 ID, Detail 2 ID, Name) values(1, 2, ‘Сидоров’) insert Master 1 (Detail 1 ID, Detail 2 ID, Name) values(4, 3, ‘Лаврененко’) insert Master 1 (Detail 1 ID, Detail 2 ID, Name) values(null, 1, ‘Кошкин’) insert Master 1 (Detail 1 ID, Detail 2 ID, Name) values(3, null, ‘Самойлов’) GO

Процедура, возвращающая набор данных: CREATE PROCEDURE msp_List 1 @ID int AS select a. Master 1 ID, a. Name, b. Name, c. Name from Master 1 a left join Detail 1 b on b. Detail 1 ID=a. Detail 1 ID left join Detail 2 c on c. Detail 2 ID=a. Detail 2 ID where a. Master 1 ID=@ID GO Выполнение: exec msp_List 1 4 GO Результат: Master 1 ID Name ——————— 4 Лаврененко Программист неполное высшее (1 row(s) affected)

Для использования набор а данных, возвращаем ого хранимой процедурой для обработки на сервере (в других процедурах и т. п. ) можно вставить его во временную таблицу и уже с ней работать: CREATE TABLE #tmp (Master 1 ID int, Name 1 varchar(200), Name 2 varchar(200), Name 3 varchar(200)) INSERT #tmp exec msp_List 1 1 select * from #tmp GO Результат: Master 1 ID Name 1 Name 2 Name 3 ——————— 1 Иванов Рабочий высшее (1 row(s) affected)

Процедура, возвращающая данные : CREATE PROCEDURE msp_List 2 @ID int, @Name varchar(200) OUTPUT AS select @Name=Name from Master 1 where Master 1 ID=@ID GO Выполнение: declare @s varchar(200) exec msp_List 2 5, @s OUTPUT print @s GO Результат: Кошкин

Ничего не возвращающая процедура: С REATE PROCEDURE sp_Vendors. Import @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

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 COMMIT TRAN RETURN error_end: ROLLBACK TRAN —end of procedure GO

Пример 2 CREATE FUNCTION Returned. Table( @ID int ) RETURNS TABLE AS RETURN( SELECT @ID as ‘ID’, ‘Test’ as ‘Name’ ) SELECT * FROM Returned. Table(1)

Лекция № 13 Параллельность и блокировки SQL- сервер позволяет одновременную работу большого числа пользователей, каждый из которых запускает свои запросы в параллельных сессиях. Это приводит к возможности одновременного доступа нескольких сессий к одним и тем же данным (доступ может быть как чтением, так и изменением данных). Одновременная работа таких конкурирующих запросов может привести к следующим проблемам: • «проблема потери последнего изменения» (lost update problem) или проблема «грязной» записи • проблема «грязного чтения» ( dirty read ) • проблема « повторного чтения» ( repeatable read ) • проблема « фантомов » ( phantom )

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

П роблема «грязного чтения» возникает, когда одна транзакция пытается прочитать данные, с которыми работает другая параллельная транзакция. В таком случае временные, неподтвержденные данные могут не удовлетворять ограничениям целостности или правилам. И, хотя к моменту фиксации транзакции они могут быть приведены в «порядок» , другая транзакция уже может воспользоваться этими неверными данными, что приведет к нарушению ее работы.

Проблема повторного чтения состоит в том, что между операциями чтения в одной транзакции другие транзакции могут беспрепятственно вносить любые изменения, так что повторное чтение тех же данные приведет к другому результату. Проблема фантомов возникает, когда выборка данных сделанная в одной транзакции изменяется другой транзакцией. Например мы устанавливаем для поля F 1 значение 5000 для всех записей таблицы, затем накладываем constraint, ограничивающий поле F 1 сверху числом 5001, а в промежуток между этими 2 мя операциями другая транзакция вносит в таблицу запись с F 1 =

• Для разрешения этих проблем необходимо изолировать транзакции друг от друга. • Для реализации различных уровней изоляции в 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 Obj. Id Ind. Id Type Resource Mode 54 8 0 0 DB S GRANT 54 8 1993058136 0 RID 1: 29: 2 U GRANT 54 8 1993058136 0 RID 1: 29: 0 U GRANT 54 8 1993058136 0 PAG 1: 29 IU GRANT 54 8 1993058136 0 TAB IX GRANT 54 8 1993058136 0 RID 1: 29: 1 U GRANT

Как видно, на три строки была наложена блокировка обновления, что означает невозможность обновления этих строк другими транзакциями. Кроме этого, были наложены еще две блокировки, которые относятся к типу блокировок намерения (intent locks) – блокировка на страницу и на таблицу. • Блокировки намерений всегда устанавливаются на таблицу или страницу, но никогда – на строку. • Блокировки намерений относятся к специальным типам блокировок и предназначены для повышения производительности работы менеджера блокировок. • Предположим, некая транзакция пытается изменить какую-либо строку в таблице test. Чтобы определить, что эту транзакцию необходимо заблокировать, менеджеру транзакций (в отсутствие блокировок намерения) пришлось бы сканировать всю таблицу syslockinfo для проверки всех строк таблицы test. Чтобы избежать этой неблагодарной работы, менеджер блокировок сразу устанавливает на страницу и таблицу блокировку намерения обновления (Intent Update) и монопольную блокировку намерения (Intent Exclusive) соответственно, и проверяет уже только их.

Типы блокировок намерений • Разделяемая блокировка намерений (обозначается IS). • Монопольная блокировка намерений (обозначается IX). • Разделяемо-монопольная блокировка намерений (обозначается SIX)

Продолжим пример. Создадим новую сессию и выполним следующий скрипт : begin tran insert into test values(4, ‘other’)

spid dbid Obj. Id Ind. Id Type Resource Mode Status 54 8 0 0 DB S GRANT 54 8 1993058136 0 RID 1: 29: 02 U GRANT 54 8 1993058136 0 RID 1: 29: 00 U GRANT 54 8 1993058136 0 PAG 1: 29 IU GRANT 54 8 1993058136 0 TAB IX GRANT 54 8 1993058136 0 RID 1: 29: 01 U GRANT 55 8 0 0 DB S GRANT 55 8 1993058136 0 PAG 1: 29 IX GRANT 55 8 1993058136 0 TAB IX GRANT 55 8 1993058136 0 RID 1: 29: 03 X GRANT

Как видно, предыдущие блокировки остались ( так как мы не зафиксировали транзакцию), и добавились четыре новых: одна блокировка базы, не имеющая никакого значения, две блокировки намерений (на таблицу и страницу) и монопольная блокировка на новую строку (идентификатор 1: 29: 03).

Блокировки схемы данных • Блокировка стабильности схемы (Schema Stability Lock), обозначается Sch-S. Данный тип блокировки предназначен для гарантии неизменности метаданных, но не самих данных. Т. е. блокировка стабильности схемы – единственная из всех типов блокировок, совместимых с монопольной блокировкой. В основном она устанавливается при компиляции тела запроса или хранимой процедуры, на это время запрещается вносить изменения в схему данных, однако разрешается устанавливать любой тип блокировок на сами данные, с которыми будет работать компилируемый запрос. • Блокировка изменения схемы (Schema Modification Lock), обозначается Sch-M. Данный тип блокировки не совместим ни с каким другим типом, ни с самим собой. Устанавливается после внесения изменений в схему данных и снимается после завершения транзакции.

Блокировки могут преобразовываться друг в друга по следующей схеме

Тип IS S U IX SIX X Sch-S Sch-M BU IS + + + — — 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 {

Уровни 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 =

Взаимоблокировки • В процессе работы параллельных транзакций, обращающихся к одним и тем же ресурсам, возможно возникновение проблемы взаимоблокировок ( deadlock ), которые также называют тупиковыми блокировками. • Если транзакции обращаются к ресурсам последовательно, вероятность возникновения взаимоблокировки очень мала, однако если порядок обращения транзакций к общим ресурсам разный, возможность возникновения мертвой блокировки повышается многократно

• В SQL Server’е предусмотрен механизм автоматического определения взаимоблокировок и разрешения конфликтов. Для этого предназначен специальный серверный процесс LOCK MONITOR, который просыпается каждые 5 секунд. • Он проверяет наличие двух и более ожидающих транзакций и вычисляет зависимости между ними. Если оказывается, что транзакции зависят друг от друга, т. е. для получения блокировки одной из них необходимо снять блокировку другой транзакции, фиксируется факт возникновения взаимоблокировки. • Единственный способ устранения проблемы заключается в отмене одной из транзакций. Для каждой транзакции вводится понятие цены или приоритета. Это значение задается на уровне соединения следующей командой: SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var } 1. MARK = ‘Royalty. Update’

Лекция № 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 2. tools 3. backup database Figure

Указание места бэкапа.

Выбор периодичности и времени бэкапа.

Differential backup С охраняет только данные, которые изменились со времени последнего full backup ’а Благодаря этому занимает гораздо меньше места на диске и выполняется существенно быстрее, что позволяет выполнять его чаще.

Backup можно выполнить также используя конструкцию T — SQL : BACKUP DATABASE { database _ name | @ database _ name _ var } TO [ , . . . 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 My. Nwind backup. USE master EXEC sp_addumpdevice ‘disk’, ‘My. Nwind_1′, DISK =’c: \Program Files\Microsoft SQL Server\MSSQL\BACKUP\My. Nwind_1. dat’ — Back up the full My. Nwind database. BACKUP DATABASE My. Nwind TO My. Nwind_

Transaction — log backup В ключает в себя историю всех транзакций базы. Наличие такого лога позволит вам откатиться на любой момент времени до последнего full backup и привести базу в состояние, в котором она была в этот момент времени

BACKUP LOG { database _ name | @ database _ name _ var } { TO [ , . . . 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’, ‘My. Nwind_2’, ‘c: \Program Files\Microsoft SQL Server\MSSQL\BACKUP\My. Nwind_2. dat’ —Create the log backup device. USE master EXEC sp_addumpdevice ‘disk’, ‘My. Nwind. Log 1’, ‘c: \Program Files\Microsoft SQL Server\MSSQL\BACKUP\My. Nwind. Log 1. dat’ — Back up the full My. Nwind database. BACKUP DATABASE My. Nwind TO My. Nwind_2 — Update activity has occurred since the full database backup. — Back up the log of the My. Nwind database. BACKUP LOG My. Nwind TO My. Nwind. Log

Для восстановления базы из Backup используется команда restore database , для восстановления лога – restore log , кроме того как всегда можно воспользоваться Enterprise Manager

RESTORE DATABASE { database_name | @ database_name_var } [ FROM [ , . . . 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 | STANDBY = undo_file_name } ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] REPLACE ] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ]

RESTORE LOG { database_name | @ database_name_var } [ FROM [ , . . . 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 | 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 My. Nwind FROM My. Nwind_1 2. RESTORE DATABASE My. Nwind FROM My. Nwind_1 WITH NORECOVERY RESTORE DATABASE My. Nwind FROM My. Nwind_1 WITH FILE =

При настройке бэкапа в 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 «Royalty. Update. » 1. BEGIN TRANSACTION Royalty. Update 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 Royalty. Update 8. GO —Time pases. Regular database —and log backups are taken. —An error occurs. 9. USE master 10. GO 11. RESTORE DATABASE pubs FROM Pubs 1 WITH FILE = 3, NORECOVERY 12. GO 13. RESTORE LOG pubs FROM Pubs 1 WITH FILE = 4, STOPATMARK = ‘Royalty. Update’

Лекция № 15 Поиск и индексация Полнотекстовый поиск и индексы. (Full-Text Search)

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ , . . . n ] ) [ WITH [ , . . . n ] ] [ ON filegroup ] : : = { PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB }

unique – значение индексируемой группы должно быть уникально clustered – данные упорядочиваются физически (по умолчанию это используется для всех primary keys ) nonclustered – используется вектор индексов, данные физически не упорядочиваются CREATE TABLE t 1 (a int, b int, c AS a/b) GO CREATE UNIQUE CLUSTERED INDEX Idx 1 ON t 1. c GO INSERT INTO t 1 VALUES (‘1’, ‘0’) GO

Средства SQL для проверки соответствия текста заданному шаблону LIKE – возвращает true, если строковое поле содержит шаблон match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] PATINDEX или CHARINDEX – возвращает позицию заданной подстроки в строке : 1. PATINDEX ( ‘ %pattern% ‘ , expression ) 2. CHARINDEX ( expression 1 , expression 2 [ , start_location ] )

Пример : Like USE pubs GO CREATE PROCEDURE find_books 2 @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_books 2 ‘ring’ Результат работы процедуры: title_ id title ———————————— MC 3021 The Gourmet Microwave PS 2091 Is Anger the Enemy? PS 2106 Life Without Fear (4 row(s) affected)

Пример: PATINDEX USE Northwind GO SELECT Category. ID, 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 = ‘TC 3218’ Результат- позиция подстроки ‘wonderful‘ в поле notes таблицы titles : ——— 46 (1 row(s) affected)

Оба эти метода ограничены образцом Full-Text Search позволяет отслеживать как точное соответствие слову в шаблоне, так и словоформы (Например шаблон drink, а найти нужно не только drink, но и drunk ) Помимо поиска по plain text поддерживается поиск по документам перечисленных ниже типов (содержимое этих документов должно содержаться в image поле)

Поддерживаемые типы документов Word (*. doc) Excel (*. xls) Power. Point (*. ppt) Text (*. txt) HTML (*. htm или *. html) Кроме того многие производители создают собственные фильтры для поддержки полнотекстового поиска по своим документам (например Adobe для Adobe Acrobat )

Full-Text Search взаимодействует с MS Search service, который способен индексировать слова, содержащиеся в обработанных файлах для организации в дальнейшем поиска по этим словам (это используется например в Microsoft Index Server ) Для таблиц MS SQL сервера строятся полнотекстовые индексы, которые хранятся отдельно от базы данных (используется file-based storage архитектура)

MSSearch service Catalog files Stored in OS Physical SQL Server Database. Запрос, содержащий Full-Text поиск SQL Server Full-text часть запроса Номера найденных колонок. Query output

Для того, чтобы Full-Text 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 = ] ‘’, [@action =] ‘{create|drop|start_incremental|start_full|stop|rebuild}’ [, [@path =] ‘’ ] USE Northwind EXEC sp_fulltext_catalog @ftcat = ‘North. Wind. Full. Text’, @action = ‘CREATE’

Включение поддержки Full-Text Search для таблиц EXEC sp_fulltext_table [@tabname =] ‘[. ]

Добавление колонки в Full-Text индекс sp_fulltext_column [@tabname =] ‘[. ]

После всех этих действий необходимо запустить заполнение ( population ) индекса EXEC sp_fulltext_table @tabname = ‘Employees’, @action = ‘start_full’,

Full-Text Query Syntax Существует 2 условных выражения: CONTAINS – строгое соответствие шаблону FREETEXT – нестрогое соответствие (словоформы) и 2 эквивалентных выражения, возвращающие таблицы: CONTAINSTABLE FREETEXTTABL

CONTAINS ({|*} , ‘’) SELECT Employee. ID, Last. Name, First. Name FROM Employees WHERE CONTAINS(*, ‘Course’)

FREETEXT ({|*} , ‘’) SELECT Employee. ID, Last. Name, First. Name FROM Employees WHERE FREETEXT (*, ‘Course’)

CONTAINSTABLE (

SELECT Rank, Employee. ID, Last. Name, First. Name, Notes FROM CONTAINSTABLE(Employees, *, ‘Course’) AS ct JOIN Employees AS e ON ct. [KEY] = e. Employee. I

SELECT Rank, Employee. ID, Last. Name, First. Name, Notes FROM FREETEXTTABLE(Employees, *, ‘Course’) AS ct JOIN Employees AS e ON ct. [KEY] = e. Employee. I

Использование фраз в шаблонах SELECT Employee. ID, Last. Name, First. Name, Notes FROM Employees WHERE CONTAINS(*, ‘ ”University of California” ’) Этот запрос найдет все записи, в поле Notes которых есть слова ‘University’ , ‘of’ и ‘California’ Можно использовать and, or и not

SELECT Employee. ID, Last. Name, First. Name, Notes FROM Employees WHERE FREETEXT(*, ‘ ”University of California” ’) Этот запрос найдет все записи, в поле Notes которых есть слова ‘University’ , ‘of’ ИЛИ ‘California’. ‘Of’ будет проигнорировано как ‘noise word’. Список таких слов содержится в обычном текстовом файле

Использование NEAR – между словами шаблона имеется не более 8 -10 других слов: SELECT Rank, Employee. ID, Last. Name, First. Name, Notes FROM CONTAINSTABLE(Employees, *, ‘completed near sales’) AS ct JOIN Employees AS e ON ct. [KEY] = e. Employee. I

Пример: NEAR USE Northwind GO SELECT FT_TBL. Description, FT_TBL. Category. Name, 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. Category. ID = KEY_TBL. [KEY] WHERE KEY_TBL. RANK > 2 AND FT_TBL. Category. Name ‘Seafood’ ORDER BY KEY_TBL. RANK DES

Использование префиксов SELECT Last. Name, First. Name, Notes FROM Employees WHERE CONTAINS(*, ‘ ”grad*” ’) Будет искать все слова начинающиеся с grad CONTAINS(*, ‘grad*’) будет искать подстроку grad* ( важно ставить кавычки )

Задание весов для частей шаблона SELECT Rank, Employee. ID, Last. Name, First. Name, 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. Employee. ID ORDER BY Rank DESC То есть найти ‘MA’ в 2 раза важнее, чем ‘BS’, а ‘BS’ в 2 раза важнее, чем ‘BA’. Записи, в которых содержится ‘MA’ будут иметь более высокий RANK. Сначала будут выведены, магистры ( MA ) , затем бакалавры ( BS ) , затем бакалавры искусств ( BA ) – производится ранжирование по уровню образования.

Поиск словоформ с использованием CONTAINS По умолчанию CONTAINS ищет точное соответствие шаблону SELECT Last. Name, First. Name, 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. • Active. X 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 — часть универсального механизма доступа к данным Microsoft ( Microsoft Universal Data Access ), позволяющая осуществить доступ как к реляционным, так и к нереляционным источникам данных, таким как файловая система, данные электронной почты, многомерные хранилища данных и др.

Microsoft Active. X 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. Потребители могут обращаться к данным посредством Active. X Data Objects, представляющих собой высокоуровневый интерфейс к OLE DB, или применять OLE DB непосредственно, используя OLE DB-провайдер.

Объекты OLE DB Объектная модель OLE DB содержит четыре ключевых объекта: • Data. Source; • Session; • Command; • Rowset.

Объект Data. Source, применяемый потребителями данных для соединения с провайдером. Объект 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 Data. Source и Session. • объект ADO Command использует объект OLE DB Command и интерфейс ICommand. Например, его метод Execute вызывает непосредственно одноименный метод объекта OLE DB Command, его свойство Command. Text доступно с помощью методов Get. Command. Text и Set. Command. Text интерфейса ICommand. Text. • объект ADO Recordset использует объект OLE DB Rowset. Он применяет интерфейсы IRowset, IRowset. Locate и IRowset. Info для реализации большинства методов, свойств и коллекций. Объект Field использует интерфейс IColumns. Info.

ПРИМЕР ( VB ): Dim cnn ‘ Создать соединение до базы данных Set cnn = Create. Object(«ADODB. Connection») ‘ Открываем при помощи connection string, ‘ определяющей тип провайдера и путь до базы cnn. Open «Provider=Microsoft. Jet. OLEDB. 4. 0; » & _ «Data Source=c: \goodsdata. mdb; » Dim rs Set rs = Create. Object(«ADODB. Record. Set») rs. Open «SELECT * FROM Goods», cnn ‘ rs = cnn. Execute «SELECT * FROM Goods“ – использование ADODB. Connection как фабрики классов ‘ цикл по рекордсету, производящий какие-то действия rs. Close cnn. Close

ADO. NET является преемником Microsoft Active. X Data Objects (ADO). Это W 3 C стандартизированная модель программирования для создания распределенных прикладных программ, нацеленных на совместное использование данных. Подобно 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. NET Резидентное представление данных в памяти Использует объект Record. Set, который напоминает отдельную таблицу. Использует объект Data. Set, который может содержать одну или более таблиц, представленных объектами Data. Table Инспектировани е данных Последовательное сканирование данных Record. Set Использует навигационную парадигму для непоследовательного доступа к строкам в таблице. Следует за отношениями, чтобы передвигаться от строк в одной таблице к соответствующим строкам в другой таблице. Отношения между множеством таблицами Требует, чтобы запрос JOIN транслировал данные из объединяемых таблиц базы данных в отдельную, результирующую таблицу. Поддерживает объект Data. Relation, чтобы сопоставить строки в одном объекте Data. Table со строками в другом объекте Data. Table.

Офлайновый доступ Использует Record. Set, для которого типична поддержка соединения, представленная объектом Connection. Вы подключаетесь к базе данных посредством запросов к OLE DB провайдеру. Подключается к базе данных посредством стандартизированных запросов к объекту Data. Set. Command, который подключается к OLE DB провайдеру (а иногда, непосредственно к API, обеспечивающемуся СУБД). Программируемост ь Использует объект Connection, чтобы передать команды, адресуемые источникам данных, лежащим в основе конструкции данных. Использует строгий формат программирования характеристик XML. Данные включают в себя собственные описатели, поэтому, имена элементов кода отражают реальные проблемы, решаемые кодом. Лежащие в основе конструкций данных типы таблиц, строки, и таблицы, делают код проще для понимания и написания.

Совместное использование офлайновый данных между уровнями или компонентами Использует COM для передачи Record. Set в офлайне. Поддерживаются только те типы данных, которыми располагает стандарт COM. Требуется преобразование типов, которое отнимает системные ресурсы. Передает Data. Set с XML файлом. Формат XML не имеет никаких ограничений на типы данных и не требует никаких преобразований типов. Передача данных через межсетевые экраны (firewalls) Проблематично, потому что межсетевые экраны обычно конфигурируются так, чтобы предотвратить запросы системного уровня, которые используются в COM. Поддерживается, потому что объекты Data. Set, используемые ADO. NET, передают XML, разработанный на основе HTML, который может проходить через межсетевые экраны.

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

public void Read. My. Data(string my. Conn. String) { string my. Select. Query = «SELECT Order. ID, Customer FROM Orders»; Sql. Connection my. Connection = new Sql. Connection(my. Conn. String); Sql. Command my. Command = new Sql. Command(my. Select. Query, my. Connection); my. Connection. Open(); Sql. Data. Reader my. Reader = my. Command. Execute. Reader(); try { while (my. Reader. Read()) { Console. Write. Line(my. Reader. Get. Int 32(0) + «, » + my. Reader. Get. String(1)); } } finally { // always call Close when done reading. my. Reader. Close(); // always call Close when done reading. my. Connection. Close(); } }

Примеры работа с реляционными источниками данных через ADO и ODBC в веб-приложениях. Web-server Db server Access, text file. . MDAC browserrequest response

Web-server Db server Access, text file. . MDAC browserrequest response

Существуют 2 типа организации запроса к серверу ( request ) • Get – данные пересылаются в строке запроса. • Post – данные пересылаются в теле запроса. Для ввода данных используются html- формы, позволяющие работать с полями следующих основных типов • Textarea • Hidden • Select (pulldown) • Checkbox

Формы и поля ввода описываются с помощью основного элемента html – тэгов.

Работать с формами можно через клиентские скрипты Они не предполагают отсылки запроса к веб-серверу и выполняются непосредственно браузером. Серверные скрипты выполняются на веб-сервере и формируют 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 и Java. Script, однако существует возможность расширения этого списка.

5 основных объектов ASP Request – получение информации о запросе Response – управление выводом веб-сервера Session – работа с пользовательской сессией Application – работа с данными, актуальными для всех сессий Server – объект, в частности позволяющий создавать экземпляры com- объектов ( Server. Create. Object(“[object name]”) ) ASP- приложение это потребитель com- объектов, поскольку в саму технологию заложено очень мало функциональности, программист может оперировать лишь с 5 ю перечисленными объектами и писать условия и циклы на Java. Script или VBScript