Тема Архитектура Microsoft sql server



Pdf просмотр
страница1/6
Дата30.12.2016
Размер1.68 Mb.
Просмотров627
Скачиваний0
  1   2   3   4   5   6

Разработка распределенных приложений баз данных
1
Тема 1. Архитектура Microsoft SQL Server.
Физическая архитектура базы данных
Экземпляр Microsoft SQL Server включает в себя системные базы данных
(master.model, msdb, tempdb), содержащие служебную информацию, и пользовательские базы данных. Каждая база данных размещается в отдельных файлах – минимум двух: один для самой базы данных – файл данных (mdf-файл), и один для журнала транзакций (ldf-файл). Первый файл данных (mdf-файл) является основным и кроме самих данных содержит системную информацию, второй и все последующие файлы данных являются вторичными (ndf-файлами) и содержат непосредственно сами данные.
Расположение этих файлов можно указать при создании базы данных.
Основной единицей хранения данных является страница. SQL Server выполняет чтение и запись данных постранично. Вся база данных логически подразделена на страницы, нумеруемые начиная с 0. Размер страницы составляет 8 Кбайт (128 страниц на один мегабайт)
Для более эффективного управления страницами они объединяются в экстенты – по 8 страниц в экстенте. Экстенты могут быть двух типов:
mixed – страницы, входящие в такой экстент могут принадлежать разным объектам;
uniform- экстент содержит станицы, принадлежащие одному объекту.

Разработка распределенных приложений баз данных
2

Системная информация о странице хранится в заголовке, под который отводится первые 96 байт. Эта информация содержит номер страницы, тип страницы, количество свободного пространства, и ID объекта, владеющего страницей. В конце каждой страницы располагается таблица смещения строк.

SQL Server использует в файлах данных следующие типы страниц:
Data - станица содержит строки всехданных за исключением данных типа text, ntext, image, nvarchar(max), varchar(max), varbinary(max) и xml-данных;

Разработка распределенных приложений баз данных
3
Index - станица содержит информацию о индексах;.
Text/Image страница для хранения: o
следующих типов LOB-объектов: text, ntext, image, nvarchar(max), varchar(max), varbinary(max) и xml-данных; o
столбцов переменной длины, чей размер строки превышает 8
KB: varchar, nvarchar, varbinary и sql_variant
Global Allocation Map - станица данного типа содержит информацию об используемости экстентов (на одной странице хранятся данные об используемости 64000 экстентов);
Shared Global Allocation Map
- станица данного типа содержит информацию об используемости экстентов типа Mixed;
Page Free Space - страница содержит информацию о количестве свободного пространства на странице;
Index Allocation Map - страница содержит данные, какие экстенты имеют страницы, принадлежащие одному объекту- владельцу;
Bulk Changed Map - станица содержит информацию об экстентах, измененных посредством набора операций, выполненных после последней операции копирования базы данных (BACKUP LOG);
Differential Changed Map - станица содержит информацию об экстентах, измененных с момента последней операции копирования базы данных (BACKUP DATABASE).
Таблицы и индексы хранятся как наборы страниц. Таблица может быть подразделена на одно или несколько разбиений (partitions), содержащих строки.

Разработка распределенных приложений баз данных
4
Разбиение таблицы определяет пользователь при ее создании.
Сначала в базе данных создается функция, отображающая строки таблицы или индекса на разбиение, основанное на значениях указанного столбца
(CREATE PARTITION FUNCTION).
Далее создается схема, которая отображает разбиения разбиваемых таблиц или индексов на группы файлов (CREATE PARTITION SCHEME).
Например:
CREATE PARTITION FUNCTION myPFunc (int)
AS RANGE LEFT FOR VALUES (1, 1000);
GO
CREATE PARTITION SCHEME myPScheme
AS PARTITION myPFunc
TO ( to1fg, to1fg, to1fg, to2fg ); -- группы файлов
Имя группы файлов определяется командой CREATE DETABASE. По умолчанию файлы данных входят в основную группу файлов.
Таблицы SQL Server 2005 используют для организации их страниц данных в разбиении один из следующих двух методов:

Разработка распределенных приложений баз данных
5
Кластерные таблицы (для которых создан кластерный индекс, требующий физического перестроения данных в соответствии со структурой индекса);
Кучи (Heaps) – таблицы, не имеющие кластерного индекса.
Индексы в SQL Server организованы в виде В-деревьев. Каждая страница в индексном В-дереве называется индексным узлом (index node). В вершине
В-дерева расположен корневой узел (root node). В нижней части дерева располагаются индексные узлы, называемые листья (leaf nodes). Между вершиной дерева и листьями располагаются промежуточные уровни
(intermediate levels). Каждая строка индекса содержит ключевое значение и указатель или на страницу промежуточного уровня в В-дереве, или на данные, расположенные в нижнем уровне дерева (leaf level).
Если таблицы, для которой построен кластерный индекс имеет три разбиения, то для каждого разбиения будет построено свое В-дерево.
Страницы в цепочке данных и строки в них упорядочиваются согласно значению ключа кластерного индекса.
Следующий рисунок иллюстрирует структуру кластерного индекса.

Разработка распределенных приложений баз данных
6

Доступ к таблицам, не имеющим кластерных индексов, выполняется посредством последовательного просмотра IAM-страниц с целью нахождения экстентов, содержащих станицы, относящиеся к данной куче.
Следующий рисунок иллюстрирует процесс извлечения строк данных таблицы машиной баз данных Database Engine.

Разработка распределенных приложений баз данных
7

Некласторные индексы имеют структуру В-дерева подобно кластерным индексам, за исключением следующих различий: строки данных таблицы являются не упорядоченными и хранятся в порядке, основанном на их некластерном ключе; leaf- уровень некластерного индекса состоит из индексных страниц, а не страниц данных.
Каждая строка индекса в некластерном индексе содержит некластерное ключевое значение и локатор строки (row locator). Если таблица не содержит кластерного индекса, то локатор строки является указателем строки, в противном случае – ключом кластерного индекса для данной строки.
Указатель строки (ROWID) содержит ID-файла, номер страницы, номер строки на странице.
На следующем рисунке представлена схема использования некластерного индекса.

Разработка распределенных приложений баз данных
8

Для столбцов типа XML SQL Server позволяет создавать XML-индексы.
Единица размещения (allocation unit) является набором страниц в куче или
B-дереве, используемом для управления данными на основе типа страницы.
SQL Server для управления данными таблиц и индексов применяет следующие типы единиц размещения:
IN_ROW_DATA
LOB_DATA

Разработка распределенных приложений баз данных
9
ROW_OVERFLOW_DATA
Схема
В версии Microsoft SQL Server 2005 схемой называется набор элементов
(entities) базы данных, формирующий единое пространство имен. В предыдущих версиях Microsoft SQL Server понятие схемы было тесно связано с именем пользователя базы данных: для каждого пользователя предназначалась одноименная схема базы данных. В версии Microsoft SQL
Server 2005 пользователь может размещать свои объекты в различных схемах.
Объединение объектов в схемы, не ассоциируемые с конкретным пользователем, не требует внесения изменений в процедуры, использующие объекты схемы, при замене пользователя.
Несколько пользователей могут владеть одной схемой через членство в роли или группе Windows.
Создать новую схему можно, используя графический инструментарий
Micrisoft SQL Server Management Studio, или программным путем, выполнив оператор CREATE SCHEMA, который имеет следующее формальное описание:
CREATE SCHEMA schema_def [ [ , ...n ] ]
::=
{
имя_схемы
| AUTHORIZATION имя_владельца
| имя_схемы AUTHORIZATION имя_владельца
}
::=
{
определение_таблицы | определение_представления |

Разработка распределенных приложений баз данных
10
grant_оператор | revoke_оператор | deny_оператор
}
Этот SQL-оператор одновременно с созданием новой схемы может добавлять в нее новые таблица и представления, а также устанавливать полномочия данным объектам посредством фраз GRANT, DENY и
REVOKE.
Имя владельца схемы является именем пользователя базы данных.
Определение таблицы задается оператором CREATE TABLE (при этом необходимо наличие соответствующих полномочий), а определение представления – оператором CREATE VIEW.
Например:
USE MyDb;
CREATE SCHEMA Schema1 AUTHORIZATION dbo
CREATE TABLE Tbl1 (id1 int, f1 int, f2 varchar(20))
GRANT SELECT TO user1
DENY SELECT TO user2;
GO
Для получения списка всех форм можно использовать представление sys.schemas:

Разработка распределенных приложений баз данных
11

Разработка распределенных приложений баз данных
12
Объекты базы данных
Логически данные в базе данных хранятся в виде объектов базы данных.
Объекты данных хранятся в схеме базы данных.
SQL Server предоставляет следующие объекты данных: таблицы; представления; сининимы; индексы; хранимые процедуры; триггеры; пользовательские типы данных; функции пользователя; ключи, обеспечивающие ссылочную целостность; ограничения целостности; умолчания правила (используются для обратной совместимости)
К объектам базы данных также относятся схемы, пользователи и роли.
В SQL Server введены новые объекты, используемые Service Broker: типы сообщений (структура сообщения, отправляемого от одного сервиса другому), контракты (соглашения между двумя сервисами), очереди (сообщения, направленные сервису), сервисы (наборы задач, где каждая задача представляется контрактом), сервисные программы.
Создание базы данных
Создать базу данных можно как программным путем, выполнив SQL- оператор CREATE DATABASE, так и используя средства Micrisoft SQL
Server Management Studio.

Разработка распределенных приложений баз данных
13
При подключении к SQL-серверу следует выбрать тип сервера: Database
Engine, Analysis Services, Reporting Services, SQL Server Mobile, Integration
Services, и тип аутентификации пользователя: Windows Authentication или
SQL Server Authentication.
Для создания новой базы данных следует в Micrisoft SQL Server Management
Studio в окне Object Explorer выполнить для секции Database команду контекстного меню New Database и в предложенном далее диалоге ввести имя создаваемой пользовательской базы данных.
На странице Options диалога New Database следует задать параметры базы данных.
Режим работы с курсором фиксируется двумя параметрами:
Close Cursor on Commit Enabled – параметр определяет будет ли закрыт курсор при фиксации транзакции, в которой данный курсор был открыт. По умолчанию значение параметра равно false – курсор остается открытым после фиксации транзакции, и при откате транзакции закрываются курсоры, которые не были определены как
STATIC или INSENSITIVE. Отметим, что при значении True фиксация или откат транзакции означает закрытие курсора;
Default Cursor – параметр, определяющий по умолчанию поведение курсора, как LOCAL или GLOBAL (значение по умолчанию).
Для ограничения доступа к базе данных значение параметра Restrict Access можно задать как:
Multiple – к базе данных одновременно разрешен доступ нескольким пользователям (значение по умолчанию);
Single – в каждый момент времени к базе данных может быть подключен только один пользователь;
Restricted - к базе данных могут быть подключены только пользователи, имеющие полномочия, определяемые ролями db_owner, dbcreator или sysadmin.

Разработка распределенных приложений баз данных
14


Разработка распределенных приложений баз данных
15
Тема 2. Реализация доступа к базам данных
средствами языка SQL.
Язык SQL
Язык SQL предназначен для доступа к информации и управления реляционной базой данных.
Язык SQL определяет:

операторы языка, называемые иногда командами языка SQL;

типы данных;

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

язык определения данных DDL (Data Definition Language);

язык манипулирования данными DML (Data Manipulation Language).
Язык определения данных включает операторы, управляющие объектами базы данных. К объектам базы данных относятся таблицы, индексы, представления.
Язык манипулирования данными включает операторы, управляющие содержанием таблиц базы данных и извлекающими информацию из этих таблиц.
Язык DML определяет следующие операторы:

SELECT – извлечение данных из одной или нескольких таблиц;

INSERT – добавление строк в таблицу;

DELETE – удаление строк из таблицы;

UPDATE – изменение значений полей в таблице.
Переключение между базами данных
Один пользователь может работать с несколькими базами данных. Для переключения на конкретную базу данных применяется SQL-оператор USE, который имеет следующее формальное описание:
USE { имя_базы_данных }

Разработка распределенных приложений баз данных
16
Все операторы, выполняющиеся после оператора USE будут использовать указанную базу данных.
Для выполнения перехода на другую базу данных пользователь должен обладать соответствующими полномочиями, например, иметь роль dbo или sysadmin.
Переключение между контекстами выполнения
Контекст выполнения задается именем пользователя при входе (учетной записью пользователя), или при вызове выполняемого модуля.
Система безопасности строится на последовательном подключении к серверу SQL Server, а затем к самой базе данных. Базой банных по умолчанию первоначально устанавливается база данных master.
При аутентификации на уровне сервера проверяется учетная запись (login) и выполняется отображение данной учетной записи в имя пользователя базы данных (user). Все зарегистрированные учетные записи (login) отображаются в секции Security окна Object Explorer среды Micrisoft SQL
Server Management Studio, а пользователи каждой базы данных отображаются в секции Security соответствующей базы данных.
На следующем рисунке представлено соответствие между учетной записью user1 и именем пользователя базы данных MyDB name_user1.

Разработка распределенных приложений баз данных
17

Можно говорить, что при подключение к базе данных происходит первичная
(на уровне сервера) и вторичная (на уровне базы данных) идентификация пользователя.

Разработка распределенных приложений баз данных
18
SQL Server 2005 позволяет явно указать контекст выполнения таких определяемых пользователем модулей как: функции, процедуры, запросы и триггеры. Изменение контекста выполнения инициируется оператором
EXECUTE, который имеет следующее формальное описание:
Функции,хранимые процедуры и DML триггеры:
{EXEC | EXECUTE} AS { CALLER | SELF | OWNER |
'имя_пользователя'}
DDL триггеры уровня базы данных:
{ EXEC | EXECUTE } AS { CALLER | SELF |
'имя_пользователя'}
DDL триггеры уровня сервера:
{ EXEC | EXECUTE } AS { CALLER | SELF |
'учетная_запись' }
Запросы:
{ EXEC | EXECUTE } AS { SELF | OWNER |
'имя_пользователя'}
Ключевое слово CALLER определяет, что модуль выполняется в контексте пользователя, инициирующего выполнение. Для этого у данного пользователя доджны быть соответствующие привелегия не только для запуска модуля, но и на доступ ко всем объектам базы данных, используемым в этом модуле. Значение CALLER используется как значение по умолчанию для всех модулей кроме запросов.
Указать конкретный контекст выполнения можно на стадии создания процедуры.
Например:
USE MyDB;
GO

Разработка распределенных приложений баз данных
19
CREATE PROCEDURE dbo.test1 WITH EXECUTE AS 'user1'
AS
SELECT user_name(); -- Возвращает имя
-- текущего пользователя
GO
Далее применение установленного контекста выполнения указывается ключевым словом CALLER.
Например:
CREATE PROCEDURE dbo.test1 WITH EXECUTE AS 'name_user1'
AS
SELECT user_name(); -- Контекст выполнения установлен
-- на name_user1
EXECUTE AS CALLER;
SELECT user_name();(); -- Контекст выполнения
-- установлен на name_user2,
-- выполнившего вызов модуля
REVERT;
SELECT user_name(); -- Контекст выполнения установлен
-- на name_user1
GO
Оператор REVERT выполняет переключение контекста выполнения на значение указанное в последнем выполненном операторе EXECUTE AS.
Для указания контекста выполнения сеанса используется оператор
EXECUTE AS, который имеет следующее формальное описание:
{ EXEC | EXECUTE ] AS <спецификация_контекста>
[;] где
< спецификация_контекста >::=

Разработка распределенных приложений баз данных
20
{ LOGIN | USER } = 'name'
[ WITH { NO REVERT |
COOKIE INTO @varbinary_variable } ]
| CALLER
Создание таблицы
Для создания таблицы используется оператор CREATE TABLE, имеющий следующее формальное описание:
CREATE TABLE
[ имя_базы_данных . [схема] . |схема .] имя_таблицы
( { <определение_столбца> |
<определение_вычислимого_столбца> }
[ <ограничение_целостности_таблицы> ] [ ,...n ] )
[ ON { partition_scheme_name (
partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]
<определение_столбца> ::=
имя_столбца <тип_данных>
[ COLLATE идентификатор_уппорядочивания ]
[ NULL | NOT NULL ]
[ [ CONSTRAINT идентификатор_ограничения_целостности ]
DEFAULT выражение_ограничения_целостности ]
| [ IDENTITY [ ( нач_значение ,инкремент ) ]
[ NOT FOR REPLICATION ]

Разработка распределенных приложений баз данных
21
]
[ ROWGUIDCOL ] [ <ограничение_целостности_столбца>
[ ...n ] ]
<тип_данных> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ]
xml_schema_collection ) ]
<ограничение_целостности_столбца> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ] REFERENCES [ схема . ]
referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE
| SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL
| SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( лог_выражение )
}

Разработка распределенных приложений баз данных
22
<определение_вычислимиго_столбца> ::=
имя_столбца AS вычисляемое_выражение
[ PERSISTED [ NOT NULL ] ]
[ [ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( [ , ...n ] )
]
| [ FOREIGN KEY ] REFERENCES referenced_table_name
[ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
]
< ограничение_целостности таблицы > ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
(column [ ASC | DESC ] [ ,...n ] )
[ WITH FILLFACTOR = fillfactor
|WITH ( [ , ...n ] )
]
[ ON { partition_scheme_name
(partition_column_name)

Разработка распределенных приложений баз данных
23
| filegroup | "default" } ]
| FOREIGN KEY ( column [ ,...n ] )
REFERENCES referenced_table_name [ (
ref_column [ ,...n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL |
SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL |
SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] (лог_выражение )
}
<опции_индекса> ::=
{ PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF}
| ALLOW_PAGE_LOCKS ={ ON | OFF}
}
При создании таблицы может быть указана база данных и схема, в которую будут добавлена таблица. Описание столбцов указывается в круглых скобках через запятую. Столбцы могут быть двух видов: столбцы заданного типа и вычисляемые столбцы.
При создании таблицы можно определить ее разбиение.
Ограничения целостности для столбца могут указываться следующими фразами:

Разработка распределенных приложений баз данных
24
NOT NULL – в любой добавляемой или изменяемой строке столбец всегда должен иметь значение отличное от NULL;
UNIQUE – все значения столбца должны быть уникальны;
PRIMARY KEY – устанавливает один столбец как первичный ключ и одновременно подразумевает что все значения столбца будут уникальны;
CLUSTERED – определяет создание кластерного индекса;
NONCLUSTERED– определяет создание некластерного индекса;
CHECK (condition) – указываемое в скобках условие используется для сравнения значение столбца и возвращает TRUE, FALSE или
UNKNOWN. Если при попытке выполнения SQL-оператора возвращаемое значение равно FALSE, то оператор выполнен не будет;
REFERENCES table (fields_list) – ограничение требует совпадения значений столбцов данной таблицы с указанными столбцами родительской таблицы.
Ограничения целостности для таблицы могут указываться следующими фразами:
UNIQUE (column) – все значения столбца должны быть уникальны;
PRIMARY KEY(column) – устанавливает один столбец как первичный ключ и одновременно подразумевает что все значения столбца будут уникальны;
CLUSTERED (column) – определяет создание кластерного индекса;
NONCLUSTERED (column) – определяет создание некластерного индекса;
CHECK (condition) – указываемое в скобках условие используется для сравнения значение столбца и возвращает TRUE, FALSE или
UNKNOWN. Если при попытке выполнения SQL-оператора возвращаемое значение равно FALSE, то оператор выполнен не будет;

Разработка распределенных приложений баз данных
25
FOREIN KEY (fields_list) – это ограничение по внешнему ключу для столбцов гарантирует, что все значения, указанные во внешнем ключе будут соответствовать значениям родительского ключа, обеспечивая ссылочную целостность;
REFERENCES table (fields_list) – ограничение требует совпадения значений столбцов данной таблицы с указанными столбцами родительской таблицы.
Таблицы могут быть постоянными и временными. В свою очередь временные таблицы делятся на локальные - доступные только в рамках данного сеанса, и глобальные – доступные во всех сеансах. Имя локальной таблицы начинается с префикса #, а имя гловальной – с префикса ##.
Формирование запросов
Извлечение информации из базы данных выполняется посредством оператора запроса SELECT, который имеет следующее формальное описание:
SELECT
[WITH [,...n]]
<выражение запроса>
[ ORDER BY { выражение_для упорядочивания
| номер_столбца [ ASC | DESC ] }
[ ,...n ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } ( выражение ) }
[ ,...n ]
[ BY выражение [ ,...n ] ]
]
[ FOR { BROWSE | } ]
[ OPTION ( [ ,...n ] ) ]

Разработка распределенных приложений баз данных
26
<выражение_запроса> ::=
{ <спецификация_запроса> | ( <выражение_запроса> )}
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<спецификация_запроса> | ( <выражение_запроса> )
[...n ] ]
<спецификация_запроса> ::=
SELECT [ ALL | DISTINCT ]
[TOP выражение [PERCENT] [ WITH TIES ] ]
< список_выбора >
[ INTO новая_таблица ]
[ FROM { <исходная_таблица> } [ ,...n ] ]
[ WHERE <условие> ]
[ GROUP BY [ ALL ] выражение_используемое_для_группы
[ ,...n ]
[ WITH { CUBE | ROLLUP } ]
]
[ HAVING < условие > ]
<XML>::=
XML
{{ RAW [ ( 'ElementName' ) ] | AUTO }
[
[, BINARY BASE64 ][, TYPE ][, ROOT [('RootName')]]
[, { XMLDATA | XMLSCHEMA [('TargetNameSpaceURI')]}]
[, ELEMENTS [ XSINIL | ABSENT ]
]
| EXPLICIT
[
[, BINARY BASE64 ][, TYPE ][, ROOT [('RootName')]]
[ , XMLDATA ]

Разработка распределенных приложений баз данных
27
]
| PATH [ ( 'ElementName' ) ]
[
[, BINARY BASE64 ][, TYPE ][, ROOT [('RootName')]]
[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}

Если оператор SELECT выполняется из приложения на другом языке программирования, то формируется результирующий набор, размещаемый в памяти приложения или сервера БД, а затем приложение извлекает данные из результирующего набора в свои переменные.
После фразы SELECT указывается список выражений, определяющий значения формируемые запросом. В самом простом случае список выражений является списком полей таблицы. Если требуется извлечение значений всех полей, то вместо списка полей можно указать символ *.
Например: SELECT * FROM tbl1; .
Имя поля может быть квалифицировано именем таблицы, указываемым через точку. Например: SELECT tbl1.f1, tbl2.f1 FROM tbl1, tbl2; .
Фраза FROM определяет одну или несколько таблиц или подзапросов, используемых для извлечения данных.
Фраза WHERE определяет условие, которому должны удовлетворять все строки, используемые для формирования результирующего набора.
Предикат содержит одно или несколько выражений, выполняющих сравнения. В выражениях могут участвовать имена столбцов, функции агрегирования, переменные.
Кроме стандартных операторов сравнения, таких как =, <>, >, <, >=, <= в предикате могут быть использованы операторы такие, как:

Разработка распределенных приложений баз данных
28

BETWEEN – возвращает TRUE, если значение находится в указанном диапазоне. Например: x BETWEEN y AND z эквивалентно выражению (x<=z) AND (x>=y);

IN - совпадает с одним из перечисленных в списке. Например: x IN
(a,b,c);

LIKE - возвращает TRUE для значений, совпадающих с указанной подстрокой символов. Например: x LIKE 'abc';

IS NULL - возвращает TRUE, если значение равно NULL. Этот предикат возвращает только значение TRUE или FALSE Например: x
IS NULL;

EXISTS – это предикат существования, возвращающий значение
TRUE, если указанный в нем подзапрос содержит хотя бы одну строку. Например:
SELECT * FROM tbl1 t_out
WHERE
EXISTS (SELECT * FROM tbl1 t_in

WHERE t_in.f1= t_out.f1);
Фраза GROUP BY оператора SELECT применяется для определения группы строк, над которыми выполняются функции агрегирования. Если в операторе SELECT указана фраза GROUP BY, то все имена столбцов, указываемые в списке для определения создаваемого результирующего набора, должны быть указаны с функциями агрегирования. Так как для каждой группы строк в результирующий набор будет включена только одна строка, содержащая значения полученные функциями агрегирования над данной группой строк.
К функциям агрегирования относятся следующие функции языка Transact-
SQL:

COUNT
( { [ [ ALL | DISTINCT ] выражение ] | * } )
– подсчет количества значений столбцов (ALL- всех для заданного выражения, DIST – учитывая только уникальные не равные NULL, * - подсчет всех строк);

Разработка распределенных приложений баз данных
29

AVG – определение среднего значения;

SUM – подсчет суммы всех значении группы. Если при этом получаемое значение выходит за пределы суммируемого типа данных, то инициируется ошибка выполнения SQL-оператора;

MAX - определение максимального значения из группы;

MIN - определение минимального значения из группы.
Фраза HAVING оператора SELECT определяет предикат аналогично фразе
WHERE, но применяемый к строкам, полученным в результате выполнения функций агрегирования.
Следующий пример иллюстрирует применение групп. Столбец id1 имеет всего три различных значения: 11, 22 и 33. Для каждой из трех групп находится минимальное и максимальное значение столбца f1.
SELECT id1, MIN(f1), MAX(f1)
FROM tbl1
GROUP BY id1;
Результатом выполнения этого SQL-оператора будет формирование следующих строк: id1 MIN(f1)
MAX(f1)
–––––––––– –––––––––– ––––––––––
11 125 600 22 200 2300 33 100 450
При выборе с применением групп и с дополнительным ограничением на значение в столбце MIN(f1).
SELECT dno, MIN(f1), MAX(f1)
FROM tbl1
GROUP BY id1
HAVING MAX(f1) < 1000;
В результате выполнения этого SQL-оператора будут возвращены только две строки: первая и последняя: id1 MIN(f1)
MAX(f1)

Разработка распределенных приложений баз данных
30
–––––––––– –––––––––– ––––––––––
11 125 600 33 100 450
Фраза FOR XML оператора SELECTопределяет, что результат запроса будет возвращен в виде XML-документа.
Соединение таблиц
Для соединения таблиц с одноименными столбцами или соединении таблицы с самой собой используются алиасы, задаваемые во фразе FROM через фразу AS после имени таблицы.
Например: select t1.f1, t1.f2, t2.f1, t2.f2 from tbl1 as t1, tbl1 as t2 where t1.f1= t2.f2;
Соединение таблиц определяется фразой FROM оператора SELECT, имеющей следующее формальное описание:
[ FROM { <исходная_таблица> } [ ,...n ] ]
<исходная_таблица> ::=
{имя_таблицы_или_представления [ [ AS ] алиас_таблицы ]
[ TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT |
ROWS ] )
[REPEATABLE ( repeat_seed )]
]
[ WITH ( < table_hint > [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] алиас_таблицы ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] алиас_таблицы]
| OPENXML
| derived_table [ AS ] алиас_таблицы

Разработка распределенных приложений баз данных
31
[ ( алиас_столбца [ ,...n ] ) ]
| <соединяемые_таблицы>
|
|
}
<соединяемые_таблицы> ::=
{
<исходная_таблица> <тип_соединения> <исходная_таблица>
ON <условие>
|<исходная_таблица> CROSS JOIN <исходная_таблица>
| левая_исходная_таблица { CROSS | OUTER } APPLY
правая_исходная_таблица
| [ ( ] <соединяемые_таблицы> [ ) ]
}
<тип_соединения> ::=
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } }
[ <
join_hint
> ] ]
JOIN

::=

исходная_таблица PIVOT
table_alias

::=

( функция_агрегирования ( value_column )
FOR pivot_column IN ( )
)
::=

Разработка распределенных приложений баз данных
32
исходная_таблица UNPIVOT алиас_таблицы
::=
(value_column FOR pivot_column IN (<список_столбцов>))
<список_столбцов> ::=
имя_столбца [ , ... ]
Если фраза FROM определяет более одной таблицы или подзапроса, то все эти таблицы соединяются. Перекрестное соединение (CROSS JOIN), называемое также декартовым произведением создает результирующий набор со всеми возможными комбинациями строк.
Соединения позволяют выполнять временное объединение данных, не предусмотренное схемой (родительскими и внешними ключами).
Соединяемые таблицы перечисляются через запятую во фразе FROM оператора SELECT.
Во фразе FROM можно использовать следующие типы соединений:
CROSS JOIN - перекрестное соединение;
INNER JOIN – внутреннее соединение. Это соединение используется по умолчанию. При внутреннем соединении в результирующий набор включаются только те строки, значения которых по соединяемым
(одноименным) столбцам совпадают;
LEFT [OUTER] JOIN – левое внешнее соединение. При внешнем левом соединении в результирующий набор будут выбраны все строки из левой таблицы (указываемой первой). При совпадении значений по соединяемым (одноименным) столбцам значения второй таблицы заносятся в результирующий набор в соответствующие строки. При отсутствии совпадений в качестве значений второй таблицы проставляется значение NULL;

Разработка распределенных приложений баз данных
33
RIGHT [OUTER] JOIN – правое внешнее соединение. При внешнем правом соединении в результирующий набор будут выбраны все строки из правой таблицы (указываемой второй). При совпадении значений по соединяемым (одноименным) столбцам значения первой таблицы заносятся в результирующий набор в соответствующие строки (рис. 3.6.). При отсутствии совпадений в качестве значений первой таблицы проставляется значение NULL;
FULL [OUTER] JOIN - полное внешнее соединение. При полном внешнем соединении в результирующий набор будут выбраны все строки, как из правой, так и из левой таблицы. При совпадении значений по соединяемым (одноименным) столбцам строка содержит значения и из левой и из правой таблицы. В противном случае, вместо отсутствующих значений в столбцы таблицы (левой или правой) заносится значение NULL.
Фраза ON позволяет выполнить естественное соединение по указываемому предикату. В результирующий набор выбираются строки, удовлетворяющие заданному условию. Этот способ соединения аналогичен соединению по предикату, указываемому фразой WHERE.
Подзапросы
Спецификация запроса, указываемая в операторах языка Transact-SQL, описывает подзапрос. Подзапрос является очень мощным средством языка
SQL, позволяя строить сложные иерархии запросов, многократно выполняемые в процессе построения результирующего набора или выполнения одного из операторов изменения данных (DELETE, INSERT,
UPDATE).
Условно подзапросы иногда подразделяют на три типа, каждый из которых является сужением предыдущего: табличный подзапрос, возвращающий набор строк и столбцов; подзапрос строки, возвращающий только одну строку, но возможно несколько столбцов;

Разработка распределенных приложений баз данных
34 скалярный подзапрос, возвращающий значение одного столбца в одной строке.
Подзапрос позволяет решать следующие задачи: определять набор строк, добавляемый в таблицу на одно выполнение оператора INSERT; определять данные, включаемые в представление, создаваемое оператором CREATE VIEW. для определения значений, модифицируемых оператором
UPDATE; для указания одного или нескольких значений во фразах
WHERE и HAVING оператора SELECT; для определения во фразе FROM таблицы как результата выполнения подзапроса;
Например:
SELECT * INTO t1
FROM (select * from tbl1 WHERE f2 LIKE 'aa%') для применения коррелированных подзапросов. Подзапрос называется коррелированным, если запрос, содержащийся в предикате, имеет ссылку на значение из таблицы (внешней к данному запросу), которая проверяется посредством данного предиката.
Объединения
Язык Transact-SQL предоставляет два способа объединения таблиц: указывая соединяемые таблицы (включая подзапросы) во фразе
FROM оператора SLECT. В этом случае сначала выполняется соединение таблиц, а уже потом к полученному множеству применяются условия, указанные фразой WHERE, агрегирование, определяемое фразой GROUP BY, упорядочивание данных и т.п.;

Разработка распределенных приложений баз данных
35 определяя объединение результирующих наборов, полученных при обработке оператора SELECT. В этом случае два оператора SELECT соединяются фразой UNION, INTERSECT или EXCEPT.
Для объединения результатов нескольких запросов в один результирующий набор в операторе SELECT используется фраза UNION:
{ <спецификация_запроса> | ( <выражение_запроса> ) }
UNION [ ALL ]
<спецификация_запроса> | ( <выражение_запроса> )
[UNION [ ALL ]
<спецификация_запроса> | ( <выражение_запроса> )
[ ...n ] ]
Фраза UNION объединяет результаты двух запросов по следующим правилам: каждый из объединяемых запросов должен содержать одинаковое число столбцов; тип значений из попарно объединяемых столбцов должен быть одинаковым или приводимым. Так нельзя объединять значения из столбца типа integer и столбца типа varchar; из результирующего набора автоматически исключаются совпадающие строки.
Фраза UNION ALL выполняет объединение двух подзапросов аналогично фразе ALL со следующими исключениями: совпадающие строки не удаляются из формируемого результирующего набора; объединяемые запросы выводятся в результирующем наборе последовательно без упорядочивания.
При объединении более двух запросов для изменения порядка выполнения операции объединения можно использовать скобки

Разработка распределенных приложений баз данных
36
Фраза INTERSECT позволяет выбрать только те строки, которые присутствуют в каждом объединяемом результирующем наборе.
Фраза EXCEPT позволяет выбрать только те строки, которые присутствуют в первом объединяемом результирующем наборе, но отсутствуют во втором результирующем наборе.
При выполнении объединения запросов, указываемых фразами UNION,
EXCEPT и INTERSECT, существуют следующие ограничения: фразу INTO может содержать только первый подзапрос; фраза ORDER BY применяется только ко всему оператору
(указывается в конце), применение этой фразы к любому подзапросу, входящему в объединение не допускается; фразыа GROUP BY и HAVING могут применяться только к подзапросам, применять их к получаемому конечному результирующему набору нельзя; фраза FOR BROWSE не может быть указана в операторах, использующих фразы UNION, EXCEPT и INTERSECT.
В операторе INSERT можно использовать фразы UNION, EXCEPT и
INTERSECT.
Операторы управления данными
К операторам управления данными относятся операторы UPDATE, INSERT и DELETE.
Оператор UPDATE выполняет изменение данных таблицы или представления и имеет следующее формальное описание:
[ WITH [...n] ]
UPDATE
[ TOP ( выражение ) [ PERCENT ] ]
{ <объект> | rowset_function_limited
[ WITH ( [ ...n ] ) ]

Разработка распределенных приложений баз данных
37
}
SET { имя_столбца = { выражение | DEFAULT | NULL }
| {udt_column_name.{{ property_name = expression
| field_name = expression }
| method_name (argument [,...n ])
}
}
|имя_столбца {.WRITE (выражение ,@Offset ,@Length)}
| @variable = expression
| @variable = column = expression [ ,...n ]
} [ ,...n ]
[ <фраза_OUTPUT> ]
[ FROM{ } [ ,...n ] ]
[ WHERE {
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
}
]
[ OPTION ( [ ,...n ] ) ]
[ ; ]
<объект> ::=
{ [ сервер . база_данных . схема .
| база_данных .[ схема ] .
| схема .

Разработка распределенных приложений баз данных
38
] имя_таблицы_или_представления}

Оператор INSERT применяется для добавления строк в таблицу или представление и имеет следующее формальное описание:
[ WITH [ ,...n ] ]
INSERT
[ TOP ( выражение ) [ PERCENT ] ]
[ INTO]
{ <объект> | rowset_function_limited
[ WITH ( [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <фраза_OUTPUT> ]
{ VALUES ( { DEFAULT | NULL | выражение } [ ,...n ] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES
[; ]
Оператор DELETE выполняет удаление строк из таблицы или представления и имеет следующее формальное описание:
[ WITH [ ,...n ] ]
DELETE
[ TOP ( выражение ) [ PERCENT ] ]
[ FROM ]

Разработка распределенных приложений баз данных
39
{ <объект> | rowset_function_limited
[ WITH ( [ ...n ] ) ]
}
[ <фраза_OUTPUT> ]
[ FROM [ ,...n ] ]
[ WHERE {
| { [ CURRENT OF
{ { [ GLOBAL ] имя_курсора }
| имя_переменной_курсора
}
]
}
}
]
[ OPTION ( [ ,...n ] ) ]
[; ]
Фраза WITH определяет временный именуемый результирующий набор, используемый другими операторами модуля.
Например:
USE MYDB;
WITH NewRes(f1, f3) AS
( SELECT f1, COUNT(*)
FROM Tbl1 AS t1
WHERE f1 IS NOT NULL
GROUP BY f1
)
SELECT f1, f3 FROM NewRes ORDER BY f1;

Разработка распределенных приложений баз данных
40
В поле f3 запроса из NewRes отображается количество строк в группе, создаваемой по полю f1.

Разработка распределенных приложений баз данных
41


Поделитесь с Вашими друзьями:
  1   2   3   4   5   6


База данных защищена авторским правом ©nethash.ru 2017
обратиться к администрации

войти | регистрация
    Главная страница


загрузить материал