Корпоративные информационные системы Создание баз данных в MS

Зарегистрируйтесь, чтобы просмотреть полный документ!

Корпоративные информационные системы. Создание баз данных в MS SQL Server Жуков Артем Владимирович zhukov@sampo. ru

Проектирование БД сбор сведений; выделение объектов; моделирование объектов; определение типов данных для свойств каждого объекта; определение связей между объектами. 2

Основные объекты БД в MS SQL Server Таблицы Индексы Представления Хранимые процедуры Триггеры 3

Таблицы в MS SQL Таблицы в SQL Server 2005 имеют следующие основные составляющие. Столбцы Каждый столбец представляет какой-либо атрибут объекта, моделируемого таблицей, к примеру, таблица сведений о деталях содержит столбцы для их идентификатора, цвета и веса. Строки Каждая строка представляет отдельное вхождение объекта, моделируемого таблицей. Например, в таблице сведений о деталях обязательно имеется одна строка для каждой детали, продаваемой компанией. Специальные виды таблиц: Секционированные таблицы, временные таблицы, системные таблицы 4

Таблицы в MS SQL 5

Типы данных 6 bigint integer smallint tinyint bit numeric (p, s) money float real datetime national character(n) Synonym: nchar(n) national character varying(n) Synonym: nvarchar(n) ntext¹ nchar binary(n) varbinary(n) image¹ uniqueidentifier xml IDENTITY [(s, i)] ROWGUIDCOL

Целостность данных в таблицах Ограничения см. далее Правила — это средства обеспечения обратной совместимости, которые по функциональности напоминают ограничения CHECK. Не используются в последующих версиях. Значения по умолчанию Значение по умолчанию могут быть любым выражением, результат которого — константа, например собственно константой, встроенной функцией или математическим выражением. 7

Ограничения NOT NULL CHECK обеспечивают целостность домена путем ограничения значений, которые могут быть помещены в столбец. UNIQUE PRIMARY KEY FOREIGN KEY 8

Создание и изменение таблиц Transact SQL Create table Alter table Drop table 9 Визуальные средства Management studio (Пример)

Индексы Виды индексов: Кластеризованные индексы Некоторые правила использования индексов: Большое количество индексов в таблице снижает производительность инструкций INSERT, UPDATE и DELETE. Не используйте индексы для маленьких таблиц. Используйте индексы представлений. 10

Представления Виды представлений: Стандартные Сочетание данных из одной или нескольких таблиц с помощью стандартного представления позволяет использовать почти все преимущества представлений. Сюда входит фокусировка на конкретных данных и упрощение управления ими. Индексированные Индексированным называется материализованное представление. Это значит, что оно вычислено и сохранено. Индексировать представление можно, создав для него уникальный кластеризованный индекс. Секционированные Секционированным называется представление, соединяющее горизонтально секционированные данные набора базовых таблиц, находящихся на одном или нескольких серверах. При этом данные выглядят так, как будто находятся в одной таблице. 11

Создание и изменение представлений Transact SQL Create view Alter view Drop view 12 Визуальные средства Management studio (Пример)

Пример представления CREATE VIEW v. Bikes AS SELECT DISTINCT p. [Name] FROM Production. Product p JOIN Production. Product. Inventory i ON p. Product. ID = i. Product. ID JOIN Production. Product. Sub. Category ps ON p. Product. Subcategory. ID = ps. Product. Sub. Category. ID JOIN Production. Product. Category pc ON (ps. Product. Category. ID = pc. Product. Category. ID AND pc. Name = N'Bikes') AND i. Quantity > 0 13

Функции Виды функций: Функции наборов записей. Эти функции возвращают объект, который затем может быть применен в качестве источника Агрегатные функции. Возвращают одно агрегированное значение из набора значений. Скалярные функции. Могут иметь несколько аргументов и возвращают единственное значение. 14

Скалярные функции Конфигурационные. Возвращают информацию 15 о текущей конфигурации Курсорные. Возвращают информацию о курсорах Дата и времени. Выполняют операции над входными значениями даты и времени. Математические. Выполняют математические вычисления. Функции метаданных. Возвращают информацию о БД и ее объектах.

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

@@version возвращает значение версии SQL Server @@trancount возвращает число незавершенных транзакций. @@rowcount возвращает количество строк, которые были обработаны в ходе транзакции. 17

Примеры: Begin declare @i int операторы set @i=0 end while (@i < 10) begin if условие insert into customer действия Else 18 действия txtcustomer values (@i) set @i=@i + 1 break

Хранимые процедуры Типы х. п. : Хранимой процедурой Transact-SQL называется сохраненная коллекция инструкций языка Transact-SQL, которая может принимать и возвращать параметры. Хранимая процедура CLR представляет собой ссылку на метод общеязыковой среды выполнения (CRL) платформы Microsoft. NET Framework, который может принимать и возвращать пользователю параметры. 19

$CREATE { PROC | PROCEDURE } [schema_name. ] procedure_name [ ; number ]$

CREATE { PROC | PROCEDURE } [schema_name. ] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] ] [ , . . . n ] [ WITH <procedure_option> [ , . . . n ] ] [ FOR REPLICATION ] AS { <sql_statement> [; ][. . . n ] | <method_specifier> } [; ] <procedure_option> : : = [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE_AS_Clause ] <sql_statement> : : = { [ BEGIN ] statements [ END ] } 20

schema_name. Имя схемы, которой принадлежит процедура. procedure_name. Имя новой хранимой процедуры. Имена процедур должны соответствовать требованиям, предъявляемым к идентификаторам, и должны быть уникальными в схеме. В имена процедур настоятельно не рекомендуется включать префикс sp_. Этим префиксом в SQL Server обозначаются системные хранимые процедуры. Дополнительные сведения см. в разделе Создание хранимых процедур (компонент Database Engine). Локальную или глобальную процедуру можно создать, указав один символ номера (#) перед procedure_name (#procedure_name) в случае локальных временных процедур и два символа номера в случае глобальных временных процедур (##procedure_name). Присвоить временное имя хранимой процедуре CLR нельзя. Полное имя хранимой процедуры или глобальной временной хранимой процедуры не может включать более 128 символов (с учетом символов ##). Полное имя локальной временной хранимой процедуры с учетом символа # не может включать более 116 символов. 21

@ parameter. Параметр процедуры. В инструкции CREATE PROCEDURE можно объявить один или более параметров. При выполнении процедуры значение каждого из объявленных параметров должно быть указано пользователем, если для параметра не определено значение по умолчанию или значение не задано равным другому параметру. Хранимая процедура может иметь не более 2 100 параметров. Следует указывать имя параметра, используя в качестве первого символа знак @. Имя параметра должно соответствовать правилам дляидентификаторов. Параметры являются локальными в пределах процедуры; в разных процедурах могут быть использованы одинаковые имена параметров. По умолчанию параметры могут использоваться только в качестве константных выражений; они не могут быть использованы вместо имен таблиц, столбцов или других объектов базы данных. Дополнительные сведения см. в разделе EXECUTE (Transact-SQL). 22

[ type_schema_name. ] data_type. Тип данных параметра и схема, к которой он относится. Параметрами хранимых процедур Transact-SQL могут быть любые типы данных, за исключением table. Тип данных cursor может быть использован только в качестве выходного (OUTPUT) параметра. При указании типа данных cursorнужно также указать ключевые слова VARYING и OUTPUT. Выходных параметров типа cursor может быть несколько. VARYINGУказывает результирующий набор, поддерживаемый в качестве выходного параметра. Этот аргумент динамически формируется хранимой процедурой, и его содержимое может различаться. Применяется только к аргументам типа cursor. default. Значение по умолчанию для аргумента. Если значение default определено, процедуру можно выполнить без указания значения соответствующего аргумента. Значение по умолчанию должно быть константой или может равняться NULL. Если в процедуре используется аргумент с ключевым словом LIKE, он может включать символы-шаблоны %, _, [] и [^]. 23

OUTPUTПоказывает, что аргумент процедуры является выходным. Значение этого аргумента можно получить при помощи инструкции EXECUTE. Используйте выходные аргументы для возврата значений коду, вызвавшему процедуру. Аргументы типов text, ntext и image не могут быть выходными, если процедура не является процедурой CLR. Выходным аргументом с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CLR. RECOMPILEПоказывает, что компонент Database Engine не кэширует план выполнения процедуры и что процедура компилируется во время выполнения. Этот аргумент нельзя использовать, если указан аргумент FOR REPLICATION. Задать аргумент RECOMPILE для хранимой процедуры CLR нельзя. Чтобы компонент Database Engine удалил планы выполнения отдельных запросов в хранимой процедуре, следует использовать подсказку в запросе RECOMPILE. Дополнительные сведения см. в разделе Подсказка в запросе (Transact-SQL). Подсказку в запросе RECOMPILE следует использовать в тех случаях, когда необычные или временные значения используются только в части запросов, входящих в состав хранимой процедуры. 24

Триггеры Триггер — это особая разновидность хранимой процедуры, выполняемая автоматически при возникновении события на сервере базы данных. Триггеры языка обработки данных выполняются по событиям, вызванным попыткой пользователя изменить данные с помощью языка обработки данных. Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению. 25

$CREATE TRIGGER [ schema_name. ]trigger_name ON { table | view } [ WITH$

CREATE TRIGGER [ schema_name. ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ , . . . n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement 26

Триггер языка Transact-SQL можно сконструировать для выполнения конкретных действий, основанных на изменении определенных столбцов с помощью инструкций UPDATE или INSERT. Используйте для этих целей в теле триггера конструкции UPDATE() или COLUMNS_UPDATED. Конструкция UPDATE() проверяет действие инструкций UPDATE или INSERT на одном столбце. С помощью конструкции COLUMNS_UPDATED проверяются действия инструкций UPDATE или INSERT, проводимых на нескольких столбцах, и возвращается битовый шаблон, показывающий, какие столбцы были вставлены или 27

schema_name. Имя схемы, которой принадлежит триггер DML. Триггеры DML ограничены областью схемы таблицы или представления, для которых они созданы. Аргумент schema_name не может быть указан для триггеров DDL или входа. trigger_name. Имя триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов — за исключением того, что он не может начинаться с символов # или ##. table | view. Таблица или представление, в которых выполняется триггер DML, иногда указывается как таблица триггера или представление триггера. Указание уточненного имени таблицы или представления не является обязательным. На представление может ссылаться только триггер INSTEAD OF. Триггеры DML не могут быть описаны в локальной или глобальной временных таблицах. 28

DATABASEПрименяет область действия триггера DDL к текущей базе данных. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении в базе данных события типа event_type или event_group. ALL SERVERПрименяет область действия триггера DDL или триггера входа к текущему серверу. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении в любом месте на текущем сервере события типа event_type или event_group. WITH ENCRYPTIONЗатемняет текст инструкции CREATE TRIGGER. Использование аргумента WITH ENCRYPTION не позволяет публиковать триггер как часть репликации SQL Server. Параметр WITH ENCRYPTION не может быть указан для триггеров CLR. EXECUTE ASУказывает контекст безопасности, в котором выполняется триггер. Позволяет управлять учетной записью пользователя, используемой экземпляром SQL Server для проверки разрешений на любые объекты базы данных, ссылаемые триггером. 29

Дополнительные сведения см. в разделе EXECUTE AS, 30 предложение (Transact-SQL). FOR | AFTERТип AFTER указывает, что триггер срабатывает только после успешного выполнения всех операций в инструкции SQL, запускаемой триггером. Все каскадные действия и проверки ограничений, на которые имеется ссылка, должны быть успешно завершены, прежде чем триггер сработает. Если единственным заданным ключевым словом является FOR, аргумент AFTER используется по умолчанию. Триггеры AFTER не могут быть определены на представлениях. INSTEAD OFУказывает, что триггер DML срабатывает вместо инструкции SQL, используемой триггером, переопределяя таким образом действия выполняемой инструкции триггера. Аргумент INSTEAD OF не может быть указан для триггеров DDL или триггеров входа.

На каждую инструкцию INSERT, UPDATE или DELETE в таблице или представлении может быть определено не более одного триггера INSTEAD OF. Однако можно определить представления на представлениях, где у каждого представления есть собственный триггер INSTEAD OF. Триггеры INSTEAD OF не разрешены для обновляемых представлений, использующих параметр WITH CHECK OPTION. SQL Server вызывает ошибку, если триггер INSTEAD OF добавляется к обновляемому представлению с параметром WITH CHECK OPTION. Пользователь должен удалить этот параметр при помощи инструкции ALTER VIEW перед определением триггера INSTEAD OF. { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }Определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке. Для триггеров INSTEAD OF параметр DELETE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON DELETE. Точно так же параметр UPDATE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON UPDATE. 31

Процесс обработки Х. П. 32

Скачать презентацию Корпоративные информационные системы Создание баз данных в MS

Лекция 5. Создание БД в MS SQL Server.ppt

Количество слайдов: 34