авторефераты диссертаций БЕСПЛАТНАЯ БИБЛИОТЕКА РОССИИ

КОНФЕРЕНЦИИ, КНИГИ, ПОСОБИЯ, НАУЧНЫЕ ИЗДАНИЯ

<< ГЛАВНАЯ
АГРОИНЖЕНЕРИЯ
АСТРОНОМИЯ
БЕЗОПАСНОСТЬ
БИОЛОГИЯ
ЗЕМЛЯ
ИНФОРМАТИКА
ИСКУССТВОВЕДЕНИЕ
ИСТОРИЯ
КУЛЬТУРОЛОГИЯ
МАШИНОСТРОЕНИЕ
МЕДИЦИНА
МЕТАЛЛУРГИЯ
МЕХАНИКА
ПЕДАГОГИКА
ПОЛИТИКА
ПРИБОРОСТРОЕНИЕ
ПРОДОВОЛЬСТВИЕ
ПСИХОЛОГИЯ
РАДИОТЕХНИКА
СЕЛЬСКОЕ ХОЗЯЙСТВО
СОЦИОЛОГИЯ
СТРОИТЕЛЬСТВО
ТЕХНИЧЕСКИЕ НАУКИ
ТРАНСПОРТ
ФАРМАЦЕВТИКА
ФИЗИКА
ФИЗИОЛОГИЯ
ФИЛОЛОГИЯ
ФИЛОСОФИЯ
ХИМИЯ
ЭКОНОМИКА
ЭЛЕКТРОТЕХНИКА
ЭНЕРГЕТИКА
ЮРИСПРУДЕНЦИЯ
ЯЗЫКОЗНАНИЕ
РАЗНОЕ
КОНТАКТЫ


Pages:     | 1 |   ...   | 29 | 30 || 32 | 33 |

«Е.Мамаев MS SQL SERVER 2000 Книга посвящена одной из самых мощных и популярных современных систем управления базами данных - Microsoft SQL Server 2000. ...»

-- [ Страница 31 ] --

( Замечание ^ В рассматриваемом примере данные копируются из базы данных pubs, поставляе мой в комплекте с SQL Server 2000. Эта база имеется на каждом только что уста новленном SQL Server 2000. Поэтому результат выполнения представленной ко манды будет аналогичен приведенному.

Файл форматирования Как видно из предыдущего примера, в текстовом файле не содержится никакой информации о названии колонок, их размере, типе данных и т. д. При выполне нии импорта данных из текстового файла в таблицу могут возникнуть проблемы с интерпретацией формата данных. Утилита bcp.exe должна иметь необходимую информацию о каждой колонке текстового файла. Эта информация хранится от дельно от самих данных в специальном файле форматирования (format file).

Утилита bcp.exe позволяет пользователю не вводить информацию о формате данных, предлагая несколько стандартных форматов представления данных. На пример, использование ключа -с приведет к созданию текстового файла в сим вольном формате, а ключа -п — к созданию текстового файла в исконном фор мате SQL Server 2000.

( Замечание ) Полный список ключей, обеспечивающих создание текстового файла стандартного формата, следующий:

-с, -n, -w, -6 и -N. Более подробно каждый из этих ключей был рассмотрен ранее в этой главе.

Глава 28. Вставка, удаление и изменение данных Все же в некоторых ситуациях бывает необходимо получить текстовый файл своего собственного формата. В этом случае пользователь должен явно указать, в каком формате следует сохранить каждую из колонок таблицы. Если при за пуске утилиты bcp.exe не указывается ни один из ключей форматирования (-с, -n, -w, -б или - N ), TO пользователь должен будет ввести информацию о пара метрах форматирования данных вручную. Утилита bcp.exe при этом запускается в интерактивном режиме и предлагает пользователю ряд вопросов. Формат ко нечного текстового файла будет зависеть от того, как пользователь ответит на эти вопросы.

Рассмотрим, как поведет себя утилита bcp.exe, если в предыдущем примере уб рать ключ форматирования -с:

bcp "pubs..discounts" out discounts.txt Приведем всю информацию, выдаваемую утилитой на экран:

Password:

Enter the file storage type of field discounttype [char]:

Enter prefix-length of field discounttype [0]:

Enter length of field discounttype [40]:

Enter field terminator [none]:

Enter the file storage type of field stor_id [char]:

Enter prefix-length of field stor_id [0]:

Enter length of field stor_id [4J. Enter field terminator [none]:

Enter the file storage type of field lowqty [int-null]:

Enter prefix-length of field lowqty [1]:

Enter field terminator [none]:

Enter the file storage type of field highqty [int-null]:

Enter prefix-length of field highqty [1]:

Enter field terminator [none]:

Enter the file storage type of field discount [decimal]:

Enter prefix-length of field discount [1]:

Enter field terminator [none]:

Do you want to save this format information in a file? [Y\n] Host filename [bcp.fmt]:

Starting copy...

3 rows copied.

Network packet size (bytes): Clock Time (ms.): total 1 Avg 0 (3000.00 rows per sec.) Как мы видим, сначала пользователь должен ввести пароль для своей учетной записи SQL Server 2000. Если пароль не указывается, то утилита пытается уста новить с сервером доверительное соединение. То есть утилите предоставляют права доступа, установленные для учетной записи Windows NT пользователя, запустившего утилиту bcp.exe.

1148 Часть V. Программирование ( Замечание ) Если необходимо предоставить утилите bcp.exe права доступа произвольной учет ной записи SQL Server 2000, то нужно воспользоваться ключами -и и - Р.

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

( Замечание } Количество вопросов, на которые должен ответить пользователь, напрямую зависит от количества колонок в таблице. Кроме того, количество вопросов, задаваемых для конкретной колонки, зависит от типа данных, установленного для этой колонки.

Первый вопрос, задаваемый для каждой колонки, следующий:

Enter the file storage type of field columnname [datatype]:

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

Таблица 28.1. Использование различных типов данных Тип данных Ответ на вопрос Опция Prefix.

SQL Server 2000 Host file data type Length Binary X SQLBINARY 1, 1,2, bit b[it] 0,1,0, SQLBIT Char c[har] 2, 2, 2, SQLCHAR Datetime d[ate] 0,1,1, SQLDATETIME Decimal N SQLDECIMAL 1, 1, 1, float ffloat] 0, 1, 1, SQLFLT Image I[mage] 4, 4, 4, SQLBINARY int i[nt] 0,1,1, SQLINT money mfoney] 0,1,1, SQLMONEY Nchar W 2, 2, 2, SQLNCHAR Ntext W 4,4, 1, SQLNCHAR Numeric N 1,1,1, SQLNUMERIC 2, 2, 2, Nvarchar W SQLNCHAR 0, 1, 1, real R SQLFLT Глава 28. Вставка, удаление и изменение данных Таблица 28.1 (окончание) Опция Ответ на вопрос Prefix Тип данных Host file data type SQL Server 2000 Length SQLDATETIM D Smalldatetime 0, 1, 1, s[mallint] SQLSMALLINT sraallint 0, 1, 1, M SQLMONEY smallmoney 0, 1, 1, SQLCHAR T[ext] Text 4, 4, 4, SQLBINARY X timestamp 1, 1,2, SQLTINYINT t[inyint] tinyint 0, 1, 1, U Uniqueidentifier SQLUNIQUEID 1. 1, 1, Varbinary SQLBINARY X 1, 1,2, Varchar c[har] SQLCHAR 2, 2, 2, После того, как информация о типе данных будет указана. Пользователь должен будет ввести информацию о длине префикса:

Enter prefix-length of field columnname [л]:

Значение допустимой длины префикса для каждого типа данных указано в табл. 28. в столбце Prefix Length. Для каждого типа данных указано четыре значения:

• первое значение указывается, если данные хранятся в исконном формате и хранение значений NULL не поддерживается;

• второе значение также используется для исконного формата, но с поддерж кой хранения значений NULL;

П третье значение употребляется при сохранении данных в символьном форма те без возможности хранения NULL;

• последнее значение указывается для символьного формата с поддержкой NULL.

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

Enter length of field columnname [n]:

С Замечание ) Указание количества байт обязательно только для строковых данных. Если для ко лонки используется исконный тип данных, то указание количества байт для некото рых типов данных не поддерживается. Например, для хранения данных типа datetime или u n i q u e i d e n t i f i e r всегда применяется фиксированное количество байт, и изменить это значение нельзя.

Последний вопрос касается символа-разделителя, который будет служить для отделения текущей колонки от следующей. Для каждой колонки может быть 1150 Часть V. Программирование использован индивидуальный разделитель. В качестве разделителей колонок в текстовом файле могут быть следующие символы:

• U. Символ табуляции.

• \п. Символ начала новой строки в файле.

О \г. Символ перевода строки в файле.

П \\. Обратный слэш.

П \о. Нулевой разделитель (Null — невидимый разделитель).

• (*, н, 4, d и т. п.). Любой одиночный печатаемый символ. Использование управляющих символов (исключая символы табуляции, начала новой строки, перевода строки и нулевого разделителя) нежелательно по той причине, что они не будут печататься и колонки окажутся неразделенными.

л • ($$\t**, !@#$% &*(), * \ t \ r \ n *, bakkara2000 и т.п.). Произвольная строка длиной до 10 символов, в которой могут быть использованы любые из вы шеперечисленных символов.

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

( Замечание ^ В рассматриваемом примере выполняется копирование из таблицы, имеющей всего 4 колонки. При этом пользователь должен ответить более чем на 10 вопросов. Если же количество колонок в таблице значительно, то пользователь должен будет отве тить на гораздо большее количество вопросов. Если при ответе на один из вопросов будет допущена ошибка, то утилиту нужно будет запустить заново и отвечать на во просы вновь. Использование файла форматирования в этом случае может сущест венно облегчить процесс переноса данных.

На этом интерактивная часть работы утилиты bcp.exe заканчивается. Все после дующие выводимые на экран данные носят чисто информативный характер. С помощью этих сведений пользователь может получить информацию о произво дительности работы утилиты bcp.exe. В рассматриваемом примере указано, что скопировано три строки. При этом размер сетевого пакета равен 4 Кбайт (значение по умолчанию). На выполнение экспорта данных понадобилось всего 1 мс. При этом была достигнута скорость копирования 3000 строк в секунду.

Как видно, скорость работы утилиты bcp.exe очень высока.

Глава 28. Вставка, удаление и изменение данных с Замечание Если внимательно присмотреться к вопросам, то можно заметить, что в конце каж дого из них в квадратных скобках указывается значение по умолчанию. Если поль зователь в ответ на вопрос введет пустое значение (просто нажмет клавишу Enter), то утилита bcp.exe будет использовать указанное значение. Применение значений по умолчанию для всех вопросов приведет к тому же результату, что и ис пользование ключа - п. То есть данные будут сохранены в исконном формате.

Конечно, создание файла форматирования вовсе необязательно. Но все же, если есть вероятность, что в будущем понадобится выполнить подобную операцию экспорта данных, то рекомендуется создать файл форматирования. Кроме того, если необходимо будет перенести данные из текстового файла обратно в SQL Server 2000, то файл форматирования поможет избежать проблем с неправиль ной интерпретацией данных.

Рассмотрим более подробно структуру файла форматирования и смысл храни мых в нем данных. На рис. 28.1 приведено содержание типичного файла форма тирования.

Рис. 28.1. Структура файла форматирования Рассмотрим назначение каждого из параметров, приведенных на рисунке:

• Версия. Версия SQL Server, к которой принадлежит утилита bcp.exe, участво вавшая в создании файла.

• Количество столбцов. Количество колонок, которое содержится в текстовом файле.

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

П Тип данных. Для каждой колонки в этом столбце указывается тип данных, в котором она сохранена в текстовом файле. Например, если колонка с типом данных decimal записывается в символьном формате, то для нее будет ука зано значение SQLCHAR.

1152 Часть V. Программирование П Длина префикса. В этом столбце указывается количество символов, исполь зуемых для префикса. Существование значения в этом столбце обязательно лишь в том случае, если для колонки разрешено хранение значений NULL.

Допускается использование только значений 0, 1, 2 и 4. По умолчанию ис пользуется значение 1. Если применение префикса нежелательно, то нужно указать значение 0.

• Размер данных. В этом столбце задается количество байт, отводимых в тек стовом файле для хранения данных колонки.

• Разделитель. В этом столбце указывается разделитель полей в текстовом файле.

Обычно для разделения колонок принято использовать символ табуляции (значение \t). Для последней колонки таблицы указывается символ конца стро ки и перевода каретки (значение \г\п). При использовании символьного форма та указанные значения позволят просматривать данные в удобной форме.

• Порядок столбцов на сервере. В этом столбце указывается номер колонки таблицы, в которую будут вставлены данные из текстового файла. Эта ин формация используется только при импорте данных в SQL Server 2000. По умолчанию значения В столбцах Server column order И Host f i l e f i e l d order совпадают.

П Имя столбца на сервере. При выполнении экспорта данных из таблицы ути лита bcp.exe автоматически вставляет в этот столбец имя исходной колонки таблицы, из которой копировались данные в файл. Информация в этом столбце носит чисто информативный характер и нигде не используется. При создании файла форматирования вручную в текстовом редакторе можно ус тановить для столбца server column name произвольное значение. Единст венное требование — это моле не должно быть пустым.

{ Замечание^ ^ Столбец S e r v e r column o r d e r предоставляет пользователю возможность манипули рования данными. Установив для определенной колонки значение 0, можно избежать копирования этой колонки в таблицу. Кроме того, изменяя значения в этом столбце, можно скопировать данные из текстового файла в таблицу, имеющую порядок колонок, отличный от порядка колонок текстового файла. Необходимо правильно указать номер колонки таблицы, в которую должны вставляться данные из колонки текстового файла и следить, чтобы данные из двух колонок файла не копировались в одну колонку таб лицы. Количество колонок в файле и таблицы могут не совпадать.

• Сопоставление столбца. Указывается имя сопоставления, которое имел соот ветствующий столбец на сервере.

Рассмотрим, что же получится после выполнения рассматриваемого примера. В ответ на все вопросы утилиты bcp.exe были приняты значения по умолчанию (просто нажата клавиша Enter). В итоге был получен файл форматирования bcp.fmt, содержащий следующую информацию:

8. 1 SQLCHAR 0 40 "" 1 discounttype Cyrillic_General_CI_AS Глава 28. Вставка, удаление и изменение данных 2 SQLCHAR 0 4 "" 2 stor_id Cyrillic_General_CI_AS 3 SQLSMALLINT 12 3 lowqty 4 SQLSMALLINT 1 2 4 highqty 5 SQLDECIMAL 1 19 "" 5 discount "" Полученный файл может быть с успехом использован в дальнейшем как для экспорта, так и для импорта данных.

Использование команды BULK INSERT Возможности, предоставляемые утилитой bcp.exe, способны удовлетворить са мых требовательных пользователей. Однако существует одно серьезное ограни чение, препятствующее ее массовому применению — программа bcp.exe являет ся утилитой командной строки, и вызов ее из программы или хранимой процедуры связан с некоторыми сложностями. В принципе, использование ути литы bcp.exe возможно и при работе со средствами Transact-SQL. В комплекте с SQL Server 2000 поставляется набор системных хранимых процедур. Среди этих системных процедур имеется процедура x p c m d s h e i i, с помощью которой мож но получить доступ к командной строке и выполнить утилиту bcp.exe со всеми необходимыми ключами. Но все же это не лучший выход из положения.

Разработчики Microsoft предполагали, что при выполнении программы или хра нимой процедуры может появиться необходимость в переносе данных из тек стового файла в таблицу SQL Server 2000. Поэтому они создали специальную команду Transact-SQL, с помощью которой можно производить операции мас сивного копирования. Название этой команды BULK INSERT.

Команда BULK INSERT предоставляет доступ к механизмам массивного копиро вания средствами Transact-SQL и является аналогом утилиты bcp.exe. Но суще ствует серьезное ограничение на область применения команды BULK INSERT.

Проблема в том, что эта команда поддерживает только импорт данных из тек стового файла в таблицу SQL Server 2000. Для экспорта данных в текстовый файл придется воспользоваться либо утилитой bcp.exe, либо службами транс формации данных DTS (Data Transformation Services).

Достоинством команды BULK INSERT является то, что она, как и утилита bcp.exe, поддерживает файлы форматирования. Однако создать файл форматирования с помощью команды BULK INSERT нельзя. Его необходимо подготовить предвари тельно вручную или с помощью утилиты bcp.exe.

Полный синтаксис команды BULK INSERT таков:

BULK INSERT [['database_name'.]['owner']-]{'table_name' FROM data_file} [WITH ([ BATCHSIZE [= batchjsize]] [[,] CHECK_CONSTRAINTS] [[,] CODEPAGE [= 'ACP' I 'OEM' | 'RAW | ' code_page' ] ] [[,] DATAFILETYPE [= • {'char' I 'native'I 'widechar' I 'widenative'}] [[,] FIELDTERMINATOR [= 'field_terminator']] [[,] FIRSTROW [= first row]] Часть V. Программирование FORMATFILE [ = ' f o r m a t _ f i l e j p a t h ' ] ] [[,] [[,] KEEPIDENTITY] [ [, ] KEEPNULLS] [[,] KILOBYTES_PER_BATCH [= k i l o b y t e s _ p e r _ b a t c h ] ] [[,] LASTROW [= l a s t _ r o w ] ] [[,] MAXERRORS [= m a x _ e r r o r s ] ] [[,] ORDER ({column [ASC | DESC]} [,... n ] ) ] [[,] ROWS_PER_BATCH [= r o w s _ p e r _ b a t c h ] ] [[,] ROWTERMINATOR [= ' r o w _ t e r m i n a t o r ' ] ] [[,] TABLOCK]) ] Рассмотрим назначение каждого из используемых параметров.

• database_name Имя базы данных, в которой находится таблица, в которую будут копиро ваться данные. Указание этого параметра необязательно.

О owner Это необязательный параметр, указывающий владельца таблицы, в которую будут вставляться данные. Если имя владельца не указано, то предполагается, что таблица принадлежит пользователю, выполняющему команду BULK INSERT. Если такая таблица не найдена, то ищется таблица, принадлежащая пользователю dbo (data base owner, владелец базы данных).

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

} ( Замечание Если на сервере не существует указанной таблицы, то выдается сообщение об ошибке. Создание новой таблицы не производится. Проверьте правильность указа ния имени базы данных, владельца и собственно таблицы.

• data_file Этот параметр указывает имя текстового файла, из которого будет осуществ ляться импорт данных. Необходимо указать полное имя файла. Допускается использование файлов, расположенных на жестком диске, дисководе, сете вом ресурсе и т. д. при использовании файла, расположенного в сети, пол ный путь к файлу указывается в формате UNC.

• BATCHSIZE = batch_size Этот параметр указывает, какое количество строк должно содержаться в од ном пакете. Вставка в таблицу всех строк, включенных в один пакет, выпол няется в теле одной транзакции. Если аргумент BATCHSIZE не указан, то по умолчанию размер пакета устанавливается максимальным. То есть все копи руемые строки включаются в один пакет и вставляются за одну транзакцию.

П CHECK_CONSTRAINTS По умолчанию проверка вставляемых строк на соответствие определенным в таблице ограничениям целостности (constraints) не выполняется. Это может привести к тому, что в таблицу будут вставлены некорректные данные. Что Глава 28. Вставка, удаление и изменение данных бы избежать этого, необходимо указать параметр CHECKCONSTRAINTS. После завершения вставки данных SQL Server 2000 выполнит проверку данных на соответствие ограничениям целостности. Если конфликтов не обнаружено, то транзакция подтверждается, и вставка данных фиксируется. В противном случае происходит откат транзакции, и вставка данных отменяется.

О CODEPAGE [ =, ' A C P ' 'RAW I 'OEM' | I 'code_page'] Аргумент CODEPAGE определяет, для какой кодовой страницы был создан тек стовый файл. При необходимости сервер выполняет автоматическое преобра зование данных. Значение кодовой страницы используется для типов данных char, varchar и t e x t и влияет только на символы с кодом меньше 32 или больше 127.

• DATAFILETYPE [ = ( ' c h a r ' I 'native'I 'widechar' I 'widenative')] Если при создании текстового файла был использован один из стандартных форматов (с помощью ключа -с, -n, -w или -ы), то, введя аргумент DATAFILETYPE, можно указать нужный формат. Рассмотрим назначение каж дого из параметров:

• char. Файл имеет символьный формат. Это самый простой формат тек стовых файлов, который может быть сгенерирован практически любым приложением или введен вручную. Файл в этом формате может быть по лучен с помощью утилиты bcp.exe при использовании ключа -с.

• native. Это исконный формат текстовых файлов, специфичный для SQL Server. Файлы данного типа могут быть созданы с помощью утилиты bcp.exe при использовании ключа -п или с помощью служб трансформа ции данных DTS.

• widechar. В этом случае предполагается, что данные в файле хранятся в символьном формате с поддержкой Unicode. Текстовый файл этого фор мата может быть получен с помощью утилиты bcp.exe с использованием ключа -w.

• widenative. Формат является комбинацией исконного формата и сим вольного с поддержкой Unicode. Все данные в текстовом файле имеют исконный формат SQL Server 2000. Исключение составляют данные типа char, varchar и t e x t, для которых используется символьный формат с поддержкой Unicode. Текстовые файлы в этом формате могут быть сгене рированы утилитой bcp.exe при использовании ключа -ы.

П1 FIELDTERMINATOR [= 'field_terminator'] С помощью этого аргумента задается символ-разделитель, использованный в текстовом файле для разделения полей. Этот аргумент не применяется для файлов формата n a t i v e и widenative. По умолчанию предполагается, что колонки в файле разделены символом табуляции (\t).

О FIRSTROW [= f i r s t _ r o w ] Если выборку строк из текстового файла нужно начать не с первой строки, то с помощью этого параметра можно указать номер строки, с которой будет начинаться выборка строк.

1156 Часть V. Программирование П FORMATFILE [= ' f o r m a t _ f i l e _ p a t h ' ] Если требуется использовать файл форматирования, то с помощью этого ар гумента необходимо указать полный путь к файлу.

• KEEPIDENTITY Если в таблице, в которую копируются данные, определена колонка с уста новленным свойством IDENTITY, то по умолчанию при вставке новой строки SQL Server 2000 будет автоматически генерировать последовательные значе ния для этой колонки. Значения, указанные в файле, будут игнорироваться.

Чтобы вставить данные в колонку IDENTITY непосредственно из таблицы, необходимо в команде BULK INSERT использовать аргумент KEEPIDENTITY.

П KEEPNULLS Если в текстовом файле строка содержит пустое поле, то при вставке в таб лицу такой строки в соответствующую колонку таблицы будет установлено значение по умолчанию. При использовании аргумента KEEPNULLS вместо вставки значения по умолчанию будет вставляться значение NULL.

• KILOBYTES_PER_BATCH [= kilobytes_per_batch) Этот аргумент определяет приблизительный размер пакета в килобайтах. Для аргумента значение по умолчанию не определено.

• LASTROW [= l a s t _ r o w ] С помощью данного аргумента можно указать, какая строка будет скопиро вана последней. В комбинации с аргументом FIRSTROW МОЖНО операцию вставки данных разбить на несколько отдельных операций. Если аргумент LASTROW опущен, то копирование выполняется до последней строки в файле.

О MAXERRORS [= m a x _ e r r o r s ] Этот аргумент определяет максимальное количество ошибок, которое допус тимо при копировании данных. Каждый раз, когда происходит ошибка, SQL Server 2000 увеличивает на единицу счетчик ошибок. При достижении ука занного числа ошибок процесс массированного копирования прерывается, а пользователь получает сообщение об ошибке.

О ORDER ({column [ASC I DESC]} [,...n]) Этот аргумент указывает, в каком порядке отсортированы строки в текстовом файле. Если колонка, по которой отсортированы данные в файле, входит в кластерный индекс таблицы, то указание этой колонки с помощью аргумента ORDER может увеличить производительность операций массивного копирова ния. По умолчанию считается, что данные в текстовом файле не отсортиро ваны.

П ROWS_PER_BATCH [= rows_per_batch] С помощью этого аргумента можно задать, какое количество строк должно быть включено в один пакет. Операции передачи данных будут оптимизиро ваны для указанного значения. Параметр ROWS_PER_BATCH нельзя использо вать совместно с параметром BATCHSIZE.

Глава 28. Вставка, удаление и изменение данных П ROWTERMINATOR [= ' r o w _ t e r m i n a t o r ' ] По умолчанию для разделения строк в текстовом файле применяются симво лы конца строки (\п) и перевода каретки (\г). Если были использованы другие символы, то их можно указать с помощью аргумента ROWTERMINATOR.

П TABLOCK С помощью этого ключа можно заставить SQL Server 2000 блокировать таб лицу на все время выполнения вставки данных. При выполнении копирова ния большого количества строк это может существенно повысить производи тельность, т. к. не нужно будет тратить ресурсы на блокирование отдельных страниц и экстентов.

На этом рассмотрение механизма массированного копирования можно считать законченным.

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

SQL Server 2000 предоставляет пользователям гибкие средства доступа к дан ным. В одном запросе пользователи могут сразу обращаться к множеству разно образных источников данных, возможно расположенных на разных серверах сети. За счет использования технологии OLE DB пользователи могут получить доступ не только к реляционным источникам данных, как это было бы во время применения ODBC, но и к нереляционным, таким как текстовые файлы и элек тронные таблицы.

Для выборки данных в Transact-SQL существует команда SELECT, которая по зволяет как делать простую выборку всех данных из одной таблицы текущей базы данных, так и выполнять сложные запросы одновременно к множеству таблиц различных баз данных, расположенных на нескольких серверах сети. В самом простейшем случае выборка данных производится с помощью команды:

SELECT * F O table_name RM Эта команда выводит данные из всех столбцов для всех строк таблицы, т. е. в результате выполнения запроса возвращается вся информация, содержащаяся в таблице. Однако в большинстве случаев применяются более сложные конструк ции, использующие группировку, агрегирование, подзапросы, условия и другие дополнительные механизмы управления запросом.

Полный синтаксис команды SELECT следующий:

SELECT s e l e c t _ l i s t [INTO new_table_] F O t a b l e _ s o u r c e RM [WHERE search_condition] [GROUP BY group_by_expression] [HAVING s e a r c h _ c o n d i t i o n ] [UNION] [ORDER BY order_expression [ASC I DESC]] [COMPUTE compure_expression] [FOR] [OPTION query_hint] Глава 29. Выборка данных Как видно, синтаксис команды SELECT может быть очень сложный. Пожалуй, команда SELECT является самой сложной и многофункциональной командой Transact-SQL. Разработчиками SQL Server 2000 были приложены большие уси лия, чтобы сделать команду SELECT максимально наглядной и простой в исполь зовании. В итоге перед пользователями предстала хорошо структурированная команда, разбитая на отдельные разделы. Каждый из разделов выполняет узко специализированную функцию и является практически независимым от других разделов. Пользователи могут указывать только те разделы, которые им действи тельно необходимы, и не вникать в логику работы остальных разделов. Хотя количество разделов довольно велико, на практике обычно применяется "облегченный" вариант команды SELECT:

SELECT select_list [INTO new_table] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [ORDER BY order_expression [ASC I DESC]] Умение создавать правильные и эффективные запросы является важнейшим тре бованием к профессиональному разработчику. Можно не уметь работать с меха низмами DTS, не представлять работы репликации, не знать о возможностях службы SQLServerAgent, но нужно уметь производить выборку данных по опреде ленным критериям. Вряд ли можно перебрать все варианты использования ко манды SELECT. Нередко к одному и тому же результату можно прийти различны ми путями. Кроме того, оптимизатор запросов (query optimizer) при необхо димости может изменить вид запроса для повышения производительности. Эти действия прозрачны для пользователя и приводят к требуемому результату.

В следующих разделах будут подробно рассмотрены структура и использование всех разделов команды SELECT. ПО мере возможности будут приведены приме ры, демонстрирующие те или иные аспекты выборки данных.

Раздел SELECT С помощью этого раздела указывается список столбцов, которые будут включе ны в результат выборки. Кроме того, в этом разделе можно управлять количест вом и качеством строк, входящих в результат выборки. Структура раздела SELECT такова:

SELECT [ALL I DISTINCT] [TOP n [PERCENT] [ WITH TIES]] s e l e c t _ l i s t Рассмотрим использование параметров раздела.

• ALL Это ключевое слово указывает, что в результат выборки должны быть вклю чены все строки, возвращаемые запросом. То есть результат выборки может содержать повторяющиеся строки. Параметр ALL используется по умолчанию и его указание необязательно.

Часть V. Программирование 7/ О DISTINCT Применение этого параметра позволяет исключить из возвращаемого резуль тата повторяющиеся строки. Тем самым можно обеспечить уникальность ка ждой строки, возвращаемой запросом. Значения NULL считаются эквивалент ными и включаются в выборку. Если DISTINCT не указывается, то будет использоваться параметр ALL.

П ТОР П [PERCENT] [WITH TIES] С помощью этой конструкции можно ограничить количество строк, которые будут включены в результат выборки. После ключевого слова ТОР С ПОМОЩЬЮ параметра п задается максимальное количество строк, которое может содер жать результат. Если указывается ключевое слово PERCENT, TO параметр п оз начает количество строк в процентах от общего числа строк, возвращаемых запросом. Например, если таблица содержит 50 строк, а запрос должен воз вратить всего 30 строк, то при указании ТОР 50 PERCENT будет возвращено 15 строк.

• Если в запросе используется раздел ORDER BY, определяющий порядок сор тировки, то возможна ситуация, что вследствие ограничения количества воз вращаемых строк будет разорвана цепочка однотипных строк. Указание па раметра WITH TIES предписывает включить в результат выборки дополни тельные строки, имеющие то же значение в столбцах, указанных в разделе ORDER BY, что и последняя строка. Например, если сортировка выполняется по названию штата и количество строк ограничивается с помощью тор, то возможна ситуация, что для одного из штатов будет выведена только часть строк. Чтобы вывести все строки, относящиеся к штатам, фигурирующим в выборке, достаточно использовать параметр WITH TIES. Применение WITH TIES допускается только совместно с разделом ORDER BY.

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

select_list : : = { * I {table_name | view_name I t a b l e _ a l i a s }. * I {column_name | e x p r e s s i o n I IDENTITYCOL I ROWGUIDCOL} [[AS] column_alias] I column_alias=expression} [,... n ] Рассмотрим подробно назначение каждого из параметров:

• *. Указание этого символа повлечет включение в результат выборки всех столбцов всех таблиц и представлений, участвующих в запросе и указан ных в разделе FROM. Однако следует быть внимательным при выборке од ноименных столбцов из разных таблиц. Обращение к таким столбцам бу дет весьма затруднено при дальнейшем использовании результата выбор ки, т. к. сервер вряд ли сможет определить, к какому конкретно столбцу необходимо обратиться. Порядок перечисления столбцов в результате вы борки соответствует физическому порядку столбцов в таблице. Кроме Глава 29, Выборка данных того, сначала перечисляются все столбцы первой таблицы, указанной в разделе FROM, затем второй таблицы и т. д., пока не будут выведены столбцы всех таблиц. Если не используется конструкция WHERE, TO ДЛЯ каждой строки одной таблицы будет выводиться полный набор комбина ций строк других таблиц. Например, если выборка производится из двух таблиц с количеством строк 23 и 13, то общее количество возвращенных строк будет 299 (23x13).

• {table_name I view_name i t a b l e _ a l i a s }. *. Позволяет ограничить КО личество столбцов включением только всех столбцов одной таблицы или представления. Как видно из синтаксиса, сначала указывается имя объек та, из которого будет производиться выборка, потом точка, и в конце СИМВОЛ *. Параметры table_name И v i e w n a m e говорят О ТОМ, ЧТО МОЖНО ссылаться на конкретную таблицу или представление соответственно. Па раметр t a b i e a i i a s позволяет ссылаться на данные через псевдоним таб лицы. При этом под псевдонимом можно обратиться как к реальной фи зической таблице, так и к результату выборки, возвращаемому подзапро сом (динамической таблице). В любом случае, имя объекта должно быть упомянуто в разделе FROM.

• coiumnname. Подразумевает указание имени столбца, который должен быть включен в результат выборки. Столбец должен принадлежать таблице или представлению, указанному в разделе FROM. ЕСЛИ В таблицах и представле ниях, используемых в запросе, содержится более одного столбца с одинако вым именем и этот столбец должен быть включен в результат выборки, то помимо имени самого столбца следует указать имя таблицы, к которой принадлежит столбец. То есть необходимо задать полное имя столбца в формате tabie_name. column_name. В противном случае нельзя будет опре делить, какой именно столбец должен быть включен в выборку.

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

• IDENTITYCOL. Указание этого параметра включает в результат выборки столбец-счетчик (с установленным свойством IDENTITY). Аналогичного результата можно добиться, явно указав имя столбца с помощью парамет ра coiumn_name. Использование параметра IDENTITYCOL позволяет гаран тированно включить в результат выборки столбец счетчика, даже не зная его имени. Если в запросе участвует более одной таблицы, в которой име ется столбец счетчика, то дополнительно необходимо указать имя табли цы в формате table_name. IDENTITYCOL. В выборке вместо IDENTITYCOL 1162 Часть V. Программирование будет подставлено имя столбца, присвоенное ему при создании. Таким образом можно легко включить в выборку один и тот же столбец дважды.

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

• ROWGUIDCOL. Использование этого параметра похоже на применение пре дыдущего параметра. Наличие параметра ROWGUIDCOL позволяет включить в результат выборки столбец с установленным свойством ROWGUIDCOL.

Принципы работы и использования этого параметра полностью соответ ствуют аналогичным характеристикам параметра IDENTITYCOL.

• [AS] col umna l i a s. С помощью этого параметра можно определять псев донимы (alias) для столбцов. Не нужно путать псевдонимы столбцов с псевдонимами таблиц или представлений. Использование псевдонимов позволяет изменять имена столбцов, под которыми они будут выведены в результат выборки, по сравнению с их первоначальными именами. SQL Server 2000 позволяет включать в результат выборки столбцы с одинако выми именами или вообще без имен. С помощью псевдонимов можно лишить столбцы имен или сделать их все одинаковыми. Тем не менее, обычно псевдонимы служат для формирования в результате выборки на бора столбцов с разными именами или для присваивания столбцам более понятных названий, возможно на национальном языке.

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

• column_alias=expression. Этот параметр является вторым способом за дания псевдонимов для столбцов. В этом варианте сначала указывается имя, которое будет присвоено столбцу в результате выборки, а после зна ка равенства — выражение, на основе которого будет формироваться столбец. В простейшем случае выражение допускает использование имени отдельного столбца, что позволяет просто назначить столбцу новое имя. В более сложных случаях применяются константы, переменные, функции, имена столбцов, над которыми производятся различные действия. Воз можности, предоставляемые параметрами [AS] coiumnaiias и column alias=expression, практически эквивалентны.

С Замечание ^ Ниже будут приведены примеры, иллюстрирующие работу раздела SELECT. Все примеры основаны на применении базы данных pubs, автоматически устанавли ваемой при инсталляции SQL Server 2000 и доступной каждому пользователю.

Глава 29. Выборка данных Использование параметра ALL не вызывает особых затруднений — будет выведен набор всех строк, имеющихся в таблице. Рассмотрим применение параметра DISTINCT на примере выборки всех штатов, в которых проживают авторы.

SELECT DISTINCT state FROM authors state CA IN KS MD MI OR TN UT (8 row(s) affected) Было возвращено всего 8 строк, хотя общее количество строк в таблице authors составляет 23.

Следующий пример демонстрирует параметр WITH T I E S. ДЛЯ начала приведем ва риант, иллюстрирующий использование параметра ТОР без параметра WITH TIES:

SELECT TOP 35 PERCENT au_id, au_lname, city, state FROM authors ORDER BY state Теперь в этот же запрос добавим параметр WITH T I E S :

SELECT TOP 35 PERCENT WITH TIES au_id, au_lname, city, state FROM authors ORDER BY state Часть V. Программирование 427-17-2319 Dull 472-27-2349 Gringlesby 486-29-1786 Locksley 672-71-324 9 Yokomoto 724-08-9931 Stringer 724-80-9391 MacFeather 756-30-7391 Karsen 846-92-7186 Hunter 893-72-1158 McBadden (15 row(s) affected) Как видно, хотя указание параметра ТОР 35 PERCENT возвращает всего 9 строк, при указании параметра WITH TIES В результат выборки включаются все строки, имеющие значение 'СА 1 В столбце s t a t e. Если в разделе ORDER BY указывается более одного столбца, то будут добавлены только те строки, у которых значения в столбцах раздела ORDER BY совпадают со значениями последней возвращенной строки.

Использование псевдонимов не представляет особых проблем. Тем не менее, в качестве примера рассмотрим запрос, который возвращает столбцы под псевдо нимом. Кроме того, для формирования одного из столбцов применяется выра жение:

SELECT TOP 10 [Название книги]=title, type as [Тип], price*ytd_sales as [Доход] FROM titles Следующий пример выполняет обновление идентификаторов для всех авторов, проживающих в Калифорнии, и оставляет старый идентификатор для остальных авторов. Кроме того, приведенный пример иллюстрирует различные способы указания псевдонима для столбцов. Имя псевдонима может быть задано непо средственно, в квадратных скобках, в одинарных или двойных кавычках.

SELECT (CASE W E s t a t e = ' C A ' THEN CAST(NEWID() as CHAR(36);

ELSE HN CAST(au_id as c h a r ( 1 6 ) ) END) as [Идентификатор], 'Имя'=au_fname, au_lname "Фамилия", c t = c o n t r a c t F O authors RM Глава 29. Выборка данных Будет возвращен следующий результат:

Идентификатор Имя Раздел INTO предназначен для помещения результата выборки в отдельную таб лицу, которая создается как часть выполнения запроса. Этот раздел уже был описан в предыдущей главе, и более не будет рассматриваться.

1166 Часть V. Программирование нако если не будет отмечено отдельно, то сказанное о таблицах может быть приме нено и к представлениям.

Раздел FROM имеет следующий синтаксис:

FROM {table_source} [,...n] table_source ::= table_name [[AS] table_alias] [WITH (table_hint [,...n])] I view_name [[AS] table_alias] I rowset_function [[AS] table_alias] I derived_table [AS] table_alias [(column_alias [,...n])] I joined_table Рассмотрим подробно использование каждого из параметров:

• tablename Имя таблицы, из которой будет производиться выборка. Любая таблица, из которой предполагается произвести выборку, должна быть указана в разделе FROM. Кроме того, даже если данные из таблицы непосредственно не выво дятся в результат выборки, но участвуют в подготовке результата, то имя этой таблицы также должно быть приведено. Допускается применение таб лиц, расположенных вне текущей базы данных, а также на других серверах сети. Для ссылки на таблицу базы данных локального сервера необходимо указать имя таблицы в формате database_name.tabie_name. Если же таблица находится на другом сервере, то дополнительно нужно указать и имя сервера.

Чтобы иметь возможность работы с удаленными серверами, необходимо предварительно сконфигурировать связанные (linked) серверы на локальном SQL Server 2000.

• [AS] table_alias С помощью этого параметра можно присвоить таблице псевдоним, под кото рым на нее можно будет ссылаться в запросе. Часто псевдонимы служат для упрощения вида запроса при работе с длинными именами таблиц. Например, если в запросе часто упоминается таблица p u b s. t i t l e a u t h o r, то ей можно присвоить псевдоним, например, ta. Это позволит не писать каждый раз полное имя таблицы, а приводить короткое имя. Кроме того, псевдонимы таблиц активно используются при работе с подзапросами. Если в подзапросе будет выполняться обращение к той же таблице, что и в основном запросе, возможна ситуация, что подзапрос обращается к столбцу, а сервер не может определить, какое значение нужно вернуть — текущее значение основного запроса или значение, обрабатываемое в подзапросе. Применение псевдони ма позволяет однозначно идентифицировать нужные данные.

• WITH (table_hint [,...n].) С помощью этой конструкции можно установить конкретный режим блоки рования по усмотрению пользователя. Если конструкция WITH не указана, сервер сам выбирает наиболее оптимальный режим блокирования. Режим блокирования устанавливается с помощью конструкции tabie_hint, имеющей следующую структуру:

Глава 29. Выборка данных t a b l e _ h i n t : : = {INDEX(indexjval [,... n ] ) I FASTFIRSTROW | HOLDLOCK I NOLOCK I PAGLOCK I READCOMMITTED I READPAST I READUNCQMMITTED I REPEATABLEREAD | ROWLOCK I SERIALIZABLE | TABLOCK I TABLOCKX I UPDLOCK} (~ Замечание } Использование хинтов и описание уровней блокирования было рассмотрено в главе 19.

П view_name [[AS] table_alias] С помощью этого параметра в запросе разрешается использование представ лений, что позволяет производить из них выборку данных и применять в различных разделах. Работа с представлениями практически ничем не отли чается от работы с обычными таблицами. Так же, как и для таблиц, для представлений можно использовать псевдонимы.

О rowset_function [[AS] table_alias] Параметр r o w s e t f unction подразумевает указание команд подготовки набо ра ДанНЫХ — OPENQUERY,OPENROWSET, FREETEXTTABLE ИЛИ CONTAINSTABLE. ЭТИ команды позволяют использовать в запросе динамические таблицы, постро енные на основе данных, полученных в результате выполнения запросов к удаленным источникам данных OLE DB. В качестве таких источников дан ных могут выступать серверы Oracle, SQL Server, текстовые файлы, файлы DBF и XLS, базы данных MS Access и т. д. Подготовленному набору данных может быть присвоен псевдоним, что позволяет работать с данными как с обычной таблицей.

П derived_table [AS] table_alias [(column_alias [,...n])] Эта конструкция позволяет использовать в запросе динамические таблицы.

Динамические таблицы представляют собой набор данных, формируемых в момент выполнения запроса на основе информации, возвращаемой подза просом. Динамические таблицы не существуют физически в базе данных, и поэтому у них нет постоянного имени. Однако чтобы иметь возможность ссылаться на данные динамической таблицы, ей должно быть присвоено ка кое-то имя. Для этого используются псевдонимы, которые указываются с помощью параметра t a b i e a l i a s. В отличие от представлений и стандарт ных таблиц, для динамических таблиц указание псевдонима обязательно. До полнительно с помощью параметра coiumnalias можно задать псевдонимы для столбцов динамической таблицы, т. е. переименовать их.

П joined_table Эта конструкция предназначена для выборки данных из связанных таблиц. В SQL Server 2000 нет механизмов автоматического отслеживания взаимосвязи данных с установленными ограничениями целостности FOREIGN KEY. ДЛЯ об работки таких данных может с успехом выступать конструкция joined_tabie. Помимо этого, конструкция joined_tabie используется и при связывании таблиц, между которыми не установлены ограничения цело стности. Область применения конструкции joined_table станет более по 1168 Часть V. Программирование нятна при рассмотрении типов связывания. Синтаксис конструкции joined_table таков:

joined_table : : = •ctable source join_type table_source ON search_condition I table_source C O S JOIN table_source | joined_table RS Рассмотрим назначение и использование каждого из аргументов:

• table_source. С помощью этого параметра указывается источник, из которого будут браться данные. В качестве источника может выступать стандартная статическая таблица, представление, функция подготовки на бора данных (rowset function) или динамическая таблица. Весь текущий раздел посвящен практически только рассмотрению конструкции table_source. Даже конструкция joined_table является частью кон струкции tabie_source. Таким образом, получается рекурсивная зави симость конструкций table_source И joined_table Друг ОТ друга, что позволяет создавать многоуровневые зависимости между таблицами.

• join_type. Этот параметр определяет метод связывания данных двух таблиц между собой. От выбранного метода связывания зависит, какие конкретно строки каждой из двух связываемых таблиц будут рассматри ваться при выполнении запроса. Структура конструкции join_type следующая:

join_type ::= [INNER I {{LEFT i RIGHT I FULL} [OUTER]}] [join_hint] JOIN Рассмотрим назначение параметров и их использование:

• INNER. При задании этого типа связывания каждая из двух участвующих в связывании таблиц будет включать только те строки, для которых есть соответствие во второй таблице. Данный тип связывания используется по умолчанию. Соответствие определяется условием связывания, которое задается с помощью параметра search_condition. Например, если в качестве условия связывания таблиц t i t l e s и titieauthor рассматри вать столбец title_id, то Оно будет выглядеть как t i t l e s. t i t l e _ i d = t i t l e a u t h o r. t i t l e _ i d. При использовании связыва ния INNER в каждую таблицу будут включены только те строки, для ко торых значения t i t i e i d имеются как в левой (titles), так и в правой (titieauthor) таблицах. То есть в таблице t i t l e s будут доступны только те строки, у которых значение в столбце t i t i e i d соответствует значе нию столбца t i t i e _ i d хоть одной строки таблицы titieauthor. Анало гично, в таблице titieauthor будут доступны только те строки, у кото рых значение в столбце t i t i e i d соответствует значению столбца t i t i e i d хоть одной строки таблицы t i t l e s.

• LEFT [OUTER]. При использовании этого типа связывания в левой таб лице будут оставлены все строки независимо от того, есть ли для них соответствие в правой таблице. Применительно к;

предыдущему приме Глава 29. Выборка данных ру это означает, что если в таблице t i t l e s имеются книги, которые не упомянуты в таблице t i t i e a u t h o r, то строки, соответствующие этим книгам, все равно окажутся доступными. В правой же таблице будут доступны только те строки, для которых имеется соответствие в левой таблице. В столбцах, соответствующих отсутствующим данным правой таблицы, будут выведены значения NULL.

• RIGHT [OUTER]. Действие этого параметра обратно предыдущему. При использовании указанного типа связывания в правой таблице будут ос тавлены все строки независимо от того, есть ли для них соответствие в левой таблице. В результате выборки столбцы, отображающие данные левой таблицы, будут содержать значения NULL ВО всех строках, для ко торых нет соответствия.


• FULL [OUTER]. Тип связывания разрешает использование всех строк связы ваемых таблиц. Можно представить этот тип связывания как одновремен ное использование LEFT И RIGHT (ЧТО не разрешено). При выборке в столбцах с отсутствующими данными будет выведено значение NULL.

Замечание Тип связывания INNER МОЖНО легко заменить использованием оператора = в разде ле WHERE. С помощью специальных операторов *= и =* можно заменить типы свя зывания LEFT И RIGTH..

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

join_hint ' : = {LOOP | HASH I MERGE I REMOTE} :

На этом рассмотрение теоретической части использования раздела FROM МОЖНО считать завершенным. Однако некоторые аспекты требуют приведения практи ческих примеров. Для начала разберем пример применения в запросе двух таб лиц с указанием псевдонимов:

SELECT TOP 10 t.title, au_id, royaltyper as rtyper FROM titieauthor as ta, titles [t] WHERE t.title_id=ta.title_id Будет получен следующий результат:

title au_id rtyper Prolonged Data Deprivation: Four Case Studies 172-32-1176 The Busy Executive's Database Guide 213-46-8915 You Can Combat Computer Stress! 213-46-8915 But Is It User Friendly? 238-95-7766 Cooking with Computers:-Surreptitious Balance Sheets 267-41-2394 38* Глава 29. Выборки данных CASE WHEN LEN(title)30 THEN '...' ELSE " END FROM authors a, titles t, titleauthor ta WHERE ta.au_id=a.au_id AND ta.title_id=t.title_id AND state!='CA' Мы рассмотрели один из примеров использования связывания таблиц с помо щью INNER. Теперь рассмотрим связывание таблиц с указанием LEFT И RIGHT.

Чтобы иметь базу для сравнения, сначала приведем еще один пример примене ния связывания INNER:

S L C T P 10 authors.au_lname, authors.au_fname, contract as c t, EE T O titleauthor.au_ord, t i t l e a u t h o r. r o y a l t y p e r F O authors I N R JOIN t i t l e a u t h o r RM NE ON authors.au id=titleauthor.au id O D R BY contract A C RE S ( Замечание ) Чтобы отследить поведение сервера при использовании различных методов связы вания и не выводить полного содержимого таблиц, применяется сортировка по столбцу c o n t r a c t и ограничение на количество выводимых строк (ТОР 10).

В результате выполнения запроса будет получен результат:

au_lname au_fname ct au_ord royaltyper Как видно, в результате имеются только строки, упомянутые в обеих таблицах.

Запрос связывает строки таблиц authors и titleauthor по столбцу auid. В таб лице titleauthor нет строк для авторов, у которых в столбце contract таблицы authors стоит значение 0. Для просмотра списка строк, существующих в таблице authors и отсутствующих в таблице titleauthor, выполним следующий запрос:

SELECT au_id, au_fname, au_lname, c o n t r a c t F O R M authors W E E au_id N T IN (SELECT au_id F O t i t l e a u t h o r ) HR O RM Будет получен результат:

au_id au_fname au_lname contract 341-22-1782 Meander Smith 527-72-3246 Morningstar Greene 724-08-9931 Dirk Stringer 893-72-1158 Heather McBadden (4 row(s) affected) Часть V. Программирование Теперь заменим INNER на LEFT OUTER И посмотрим, к чему это приведет:

SELECT TOP 10 authors.au_lname, authors.au_fname, contract as ct, titieauthor.au_ord, titieauthor.royaltyper FROM authors LEFT OUTER JOIN titieauthor ON authors.au id=titleauthor.au id ORDER BY contract ASC В столбцах au_ord и royaltyper, принадлежащих таблице t i t i e a u t h o r, выведено значение NULL ДЛЯ тех авторов, информации о которых нет в таблице t i t i e a u t h o r.

Изменение режима связывания LEFT на режим связывания RIGHT ДЛЯ ЭТОГО запро са приведет к тому же результату, что и использование режима связывания INNER.

Дело в том, что для любой строки таблиц t i t i e a u t h o r есть соответствующая строка в таблице authors. Для демонстрации использования связывания RIGHT изменим запрос с двойным связыванием INNER, рассмотренный несколько ранее:

SELECT [Фамилия]=au_lname, [Имя]=au_fname, [Название произведения]=CAST(title as char(30))+ CASE WHEN LEN(title)30 THEN '...' ELSE " END FROM authors as a RIGHT OUTER JOIN (titieauthor as ta RIGHT OUTER JOIN titles as t ON ta.title_id=t.title_id) ON a.au_id=ta.au_id WHERE pub_id='O877' Глава 29. Выборка данных Onions, Leeks, and Garlic : Coo...

Sylvia Panteley Fifty Years in Buckingham Pala...

Reginald Blotchet-Halls Sushi, Anyone?

Michael O'Leary Sushi, Anyone?

Burt Gringlesby Sushi, Anyone?

Akiko Yokomoto (11 row(s) affected) Как видно, в четвертой строке в столбцах Фамилия и Имя выведено значение NULL, т. к. книге не соответствует ни один автор. Теперь приведем пример ис пользования связывания FULL OUTER:

SELECT [Фамилия]=au_lname, [Имя]=au_fname, [Название произведения]=CAST(title as char(30))+ CASE WHEN LEN(title)30 THEN '...' ELSE " END FROM authors as a FULL OUTER JOIN (titleauthor as ta FULL OUTER JOIN titles as t ON ta.title_id=t.title_id) ON a.au id=ta.au_id WHERE pub_id='O877' OR contract= Будет получен следующий результат:

При использовании режима связывания FULL OUTER В таблицах будут доступны все строки, независимо от того, есть ли для них сопоставления в других табли цах. В столбцах, для которых нет данных, будет выведено значение NULL. Как видно, значения NULL находятся в последних четырех строках для столбцов таб лицы t i t l e s и в четвертой строке для столбца таблицы authors.

Раздел WHERE Этот раздел предназначен для ограничения количества строк, включаемых в результат выборки. Будут включены только те строки, которые удовлетворяют указанному логическому условию. Синтаксис раздела WHERE следующий:

W E E search c o n d i t i o n | column name {*= I =*} column_name HR 1174 Часть V. Программирование Рассмотрим назначение и использование параметров:

• column_name {*= I =*} column_name Этой конструкцией можно заменить использование режимов связывания LEFT OUTER и RIGHT OUTER. С помощью параметра *= можно заменить пара метр LEFT OUTER, а параметр =* заменяет параметр RIGHT OUTER. Первый па раметр coiumnname определяет столбец левой таблицы, связываемый со столбцом правой таблицы, который указывается с помощью второго пара метра column_name. В разделе FROM достаточно просто перечислить имена используемых таблиц через запятую.

П search_condition С помощью этой конструкции можно задать любое произвольное условие для выборки данных. Конструкция search_condition является выражени ем, которое должно возвращать булево значение — TRUE ИЛИ FALSE. Указан ное выражение вычисляется для каждой строки таблицы и, только если воз вращается значение TRUE, TO строка включается в результат выборки. В противном случае строка игнорируется. Обычно условие включает имена столбцов таблицы, вместо которых при сканировании для каждой строки подставляется конкретное значение. Однако в условии имена столбцов могут и не участвовать. Структура конструкции search_condition такова:

search_condition : : = ( [NOT] predicate I (search_condition)} [{AND I OR) [NOT] {predicate I ( s e a r c h _ c o n d i t i o n ) 1 ] } [,... n ] Основное назначение самой конструкции search_condition состоит в объединении множества логических условий, каждое из которых возвращает булево значение. Объединение выполняется с помощью операторов AND, OR И NOT. Кроме того, из синтаксиса конструкции видно, что можно создавать сложные вложенные условия. Само же условие определяется с помощью конструкции predicate, имеющей следующий синтаксис:

predicate ::= (expression ( = | о | != I | = | ! | | = | !} expression I string_expression [NOT] LIKE string_expression [ESCAPE 'escape_character'] I expression [NOT] BETWEEN expression AND expression I expression IS [NOT] NULL I CONTAINS ({column | * }, 'contains_search_condition') I FREETEXT ({column | * }, 'freetext_string') I expression [NOT] IN (subquery I expression [,...n]) I expression { =• I I != I I = I ! | | = | ! } {ALL I SOME | ANY) (subquery) | EXISTS (subquery)} Работа с частью операторов не вызывает особых затруднений. К таким опе раторам относятся =,,, != и т. д. Тем не м^нее, часть операторов и аргу ментов все же требует дополнительных комментариев:

• expression. Этот параметр означает использование любого выражения. В большинстве случаев выражение возвращает некоторый результат, кото рый сравнивается с результатом вычисления другого выражения. Если в Глава 29. Выборка данных результате сравнения возвращается TRUE, TO строка включается в результат выборки.

• string_expression. Этот аргумент подразумевает наличие выражения, возвращающего символьное значение. Для строк в Transact-SQL имеются специальные операторы, с помощью которых могут выполняться некото рые операции, специфические только для строк. Примером такой опера ции является оператор LIKE, С ПОМОЩЬЮ которого можно проверить соот ветствие строкового выражения шаблону.

• expression [NOT] BETWEEN e x p r e s s i o n AND e x p r e s s i o n. С ПОМОЩЬЮ ЭТОЙ конструкции можно выполнить проверку на принадлежность значения, воз вращаемого при вычислении выражения, определенному диапазону. Грани цы диапазона также задаются как результат вычисления выражения.

• expression i s [NOT] NOLL. Эта конструкция используется для проверки выражения на равенство значению NULL. ЕСЛИ результатом вычисления выражения является значение NULL, TO выражение expression i s NULL вернет TRUE, результатом чего будет включение соответствующей строки в результат выборки. В качестве параметра expression может выступать как сложное выражение, так и имя отдельного столбца.

В обычных условиях сравнение выражения и значения NULL С ПОМОЩЬЮ оператора = или != невозможно, т. к. любое выражение с участием NULL возвратит NULL, а не TRUE ИЛИ FALSE. Поэтому для проверки выражения на равенство NULL необходимо указывать оператор i s NULL. В качестве де монстрации вышесказанного приведем пример:


DECLARE OVarl int SET @Varl=NULL IF @Varl=NULL SELECT 'aaa' ELSE SELECT 'bbb' IF @Varl!=NULL SELECT 'aaa1 ELSE SELECT 'bbb' Будет возвращен следующий результат:

bbb (1 row(s) affected) bbb (1 row(s) affected) Однако для решения представленной проблемы Transact-SQL позволяет изменять поведение сервера при Сравнении выражений со значением NULL. Для этого предназначена команда SET ANSI_NULLS {ON I OFF}. При использовании параметра ON, устанавливаемого по умолчанию, при срав нении выражения со значением NULL будет возвращено значение NULL.

Если же задан параметр OFF, TO сравнение происходит обычным образом.

Изменим предыдущий пример для демонстрации сказанного:

DECLARE evarl int * SET ANSI_NULLS OFF SET @Varl=NULL Часть V. Программирование IF @Varl=NULL SELECT 'aaa' ELSE SELECT 'bbb' IF @Varl!=NULL SELECT 'aaa' ELSE SELECT 'bbb' Будет возвращен следующий результат:

aaa (1 row(s) affected) bbb (1 row(s) affected) • CONTAINS ( { c o l u m n I * }, ' c o n t a i n s _ s e a r c h _ c o n d i t i o n ' ). С ПОМОЩЬЮ этой конструкции можно выполнять сложный поиск в символьных столб цах таблицы.

• FREETEXT ({column I *}, ' f r e e t e x t _ s t r i n g ' ). С ПОМОЩЬЮ ЭТОЙ конструкции можно производить сложный поиск в текстовых столбцах таблицы.

• expression [NOT] IN (subquery I expression [,... n ] ). С ПОМОЩЬЮ оператора IN МОЖНО проверить вхождение выражения в набор данных, ко торый может задаваться с помощью подзапроса или простого перечисле ния через запятую всех возможных значений.

• e x p r e s s i o n {= I о I != | I = I ! I I = I !} {ALL | SOME | ANY} (subquery). С помощью этой конструкции производится проверка удовлетворения значения, возвращаемого при вычислении выражения, ло гическому условию для всех или хотя бы для одного значения в подзапросе.

• EXISTS (subquery). Оператор EXISTS возвращает значение TRUE, если подзапрос возвращает хоть одну строку. В противном случае возвращается FALSE.

Раздел WHERE уже приводился довольно часто, и простейшие варианты исполь зования условий для отбора строк уже понятны. Примером такого условия мо жет служить следующий запрос:

SELECT * FROM authors WHERE state!='CA' Запрос возвращает полный набор столбцов для всех авторов, проживающих вне штата Калифорния.

В представленном ниже запросе выводится название книги и сумма их продажи для всех книг, написанных авторами не из Калифорнии:

SELECT title, value=price*ytd_sales ' FROM titles WHERE title_ID IN (SELECT title_id FROM titleauthor WHERE au_id IN (SELECT au_id FROM authors WHERE state! = 'CA' )) ORDER BY value DESC Будет получен результат:

title value Fifty Years in Buckingham Palace Kitchens 180397. Глава 29. Выборка данных The Gourmet Microwave 66515. Silicon Valley Gastronomic Treats 40619. Is Anger the Enemy? 22392. Onions, Leeks, and Garlic 7856. Life Without Fear 777. (6 row(s) affected) Следующий запрос демонстрирует использование оператора EXISTS ДЛЯ получе ния того же результата, что и предыдущий запрос:

SELECT title, value=price*ytd_sales FROM titles as t WHERE EXISTS (SELECT * FROM titleauthor as ta WHERE ta.title_id=t.title_id AND au_id IN (SELECT au_id FROM authors WHERE state!='CA')) ORDER BY value DESC С Замечание "^ Хотя запросы внешне и различаются, время их выполнения одинаково. Оптимизатор запросов SQL Server 2000 модифицирует запрос таким образом, что решение по ставленной задачи будет произведено наиболее эффективно. Это снимает с поль зователя необходимость анализа производительности выполнения того или иного запроса и упрощает разработку конечных приложений.

Раздел GROUP BY С помощью этого раздела можно осуществлять группировку данных. Данные группируются по одному или более столбцам таким образом, что для всех строк с одинаковыми значениями в столбце, по которому выполняется группировка, в результате выборки будет возвращена всего одна строка. При этом в результат выборки разрешается включение только столбцов, по которым производится группировка, а также столбцов, использующих функции агрегирования.

Синтаксис раздела GROUP BY таков:

GROUP BY [ALL] g r o u p _ b y _ e x p r e s s i o n [,... n ] [WITH (CUBE | ROLLUP}] Перед тем, как начать рассмотрение параметров раздела GROUP BY, сначала рас скажем об особенностях работы с разделом SELECT при использовании раздела GROUP BY. В раздел SELECT нельзя включать непосредственно имена столбцов, не указанных в разделе GROUP BY. Также нельзя использовать имена таких столбцов в любых выражениях. В непосредственном виде допускается применение только имен столбцов, по которым выполняется агрегирование.

Однако в разделе SELECT ПОМИМО выражения группировки могут существовать специальные функции агрегирования, возвращающие для всех строк группы единственное значение, которое и включается в результат выборки. Непосредст венное использование имен столбцов в разделе SELECT при выполнении группи ровки не допускается, т. к. в каждой группе может содержаться более одной строки и нельзя отдать предпочтение какой-то конкретной строке. Приведем список функций агрегирования с кратким описанием каждой из них:

1178 Часть V. Программирование П COUNT(coiumnname). Возвращает количество строк в группе с не пустым значением (не NULL) В указанном столбце.

• COUNT(*). Возвращает общее количество строк в группе, включая строки с неопределенным значением (NULL).

О MAX(columnname). Возвращает максимальное значение в указанном столбце в пределах группы.

П MiN(columnname). Возвращает минимальное значение в указанном столбце в пределах группы.

О suM(coiumn_name). Возвращает сумму всех значений в пределах группы в указанном столбце. Эта функция может применяться только к столбцам с числовым типом данных.

П AVG(coiumn_name). Возвращает среднее арифметическое для указанного столбца в пределах строк, принадлежащих одной группе. Эта функция может применяться только к столбцам с числовым типом данных.

( Замечание ) При организации группировки следует учитывать, что при агрегировании пропуска ются значения NULL. В противном же случае могла возникнуть ситуация, когда ре зультатом выполнения функции агрегирования было бы значение NULL. Дело в том, что все операции с участием NULL возвращают NULL. Например, если выполнить сложение 4 5 и NULL, TO результатом этой операции будет NULL. Особенности обра ботки значений NULL следует учитывать при использовании функций COUNT(column_name) И AVG(column_name).

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

SELECT price, price*MAX(ytd_sales), MIN(type) FROM titles GROUP BY price Этот запрос выполняет группировку данных по значениям в столбце price. По мимо значений столбца p r i c e в результат включаются два столбца, один из ко торых построен на основе умножения значения столбца p r i c e на значение, воз вращаемое функцией агрегирования МАХ. ВО втором столбце выводится мини мальное значение для столбца type в пределах группы.

Будет возвращен результат:

price NULL 0 2 NULL popular_comp 2.9900 2 2 66515.5400 business 7.0000 1 1 777.0000 psychology 7.3900 1 1 26654.6400 psychology 10.9500 1 • 1 22392.7500 psychology Глава 29. Выборка данных (12 row(s) affected) Очередной запрос демонстрирует разницу между использованием функций COUNT(column_name) И COUNT(*):

SELECT t y p e, c o u n t ( p r i c e ), c o u n t ( * ) FROM t i t l e s GROUP BY t y p e Будет возвращен результат:

type Часть V. Программирование / выражение возвращало ограниченный набор вариантов, каждому из которых соответствовало бы несколько строк. Выражение, по которому производится группировка, может быть включено в раздел SELECT. Дополнительно этому выражению можно присвоить псевдоним.

Приведем пример группировки строк таблицы authors по столбцу s t a t e с вычислением количества авторов, проживающих в каждом из штатов:

SELECT state, count(*) FROM authors GROUP BY state Будет возвращен следующий результат:

state Глава 29. Выборка данных WHEN price*ytd_sales20000 THEN WHEN price*ytd_sales30000 THEN ELSE 4 END) WHEN 1 THEN 'Продано $10000' WHEN 2 THEN 'Продано от $10000 до $20000' WHEN 3 THEN 'Продано от $20000 до $30000' WHEN 4 THEN 'Продано более чем на $30000' END), [Кол-во книг] = С 0 Ш Т ( ), * [На общую сумму]=SUM(price*ytd_sales) FROM titles GROUP BY (CASE WHEN price*ytd_sales10000 THEN WHEN price*ytd_sales20000 THEN WHEN price*ytd_sales30000 THEN ELSE 4 END) Будет возвращен результат:

Диапазон Кол-во книг На общую сумму Продано $10000 3 16729. Продано от $20000 до $30000 2 4 9047. Продано более чем на $30000 13 979731. (3 row(s) affected) П ALL При использовании этого параметра в результат выборки будут включены все группы, независимо от того, соответствуют ли связанные с ними данные су ществующим в разделе WHERE условиям выборки. В строках, которые не со ответствуют условиям выборки, во всех столбцах кроме столбцов, по кото рым выполняется группировка, будут выведены значения NULL.

Для сравнения сначала рассмотрим запрос без использования параметра ALL:

SELECT state, [Кол-во]=COUNT(state), MAX(city) FROM authors WHERE city LIKE '[CS]%' GROUP BY state Этот запрос выводит информацию по количеству авторов, проживающих в каждом штате в городах, начинающихся с буквы с или s. В качестве примера использования функции агрегирования приводится функция МАХ. Приведен ный запрос возвращает следующий результат:

State Кол-во ^ 1182 Часть V. Программирование СА 3 San Jose IN 0 NULL KS 0 NULL MD 0 NULL MI 0 NULL OR 1 Corvallis TN 0 NULL UT 2 Salt Lake City (8 row(s) affected) Как видно, в результат выборки были включены все возможные значения выражений группировки. Однако для строк, не удовлетворяющих условию раздела WHERE, выведены значения NULL, а количество строк равно 0. Тем не менее, пользователь имеет представление обо всех возможных вариантах группировки.

• WITH {CUBE I ROLLUP} С помощью этой конструкции выполняется суперагрегирование данных.

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

Для сравнения приведем пример без использования функций суперагрегирования:

SELECT type, MAX(price), MIN(price) FROM titles GROUP BY type Будет возвращен результат:

type business 19.9900 2. mod_cook 19.9900 2. popular_comp 22.9500 20. psychology 21.5900 7. trad_cook 20.9500 11. UNDECIDED NULL NULL (6 row(s) affected) Теперь добавим функцию суперагрегирования ROLLUP:

SELECT type, max(price), min(price) FROM titles GROUP BY type WITH ROLLUP После этого результат будет выглядеть так:

type business 19.9900 2. mod_cook 19.9900 2. popular_comp 22.9500 20. psychology 21.5900 7. trad_cook 20.9500 11. UNDECIDED NULL NULL NULL 22.9500 2. (7 row(s) affected) Глава 29. Выборка данных Как видно, была добавлена дополнительная строка (самая нижняя), в которой выводится результат выполнения функции агрегирования ко всем группам. Ис пользование функции CUBE даст в этом запросе точно такой же результат. Более интересные сведения отображаются при выполнении группировки по двум и более выражениям. Сначала опять же рассмотрим запрос без использования функций суперагрегирования:

SELECT type, pub_id, [Объем продаж]=SUM(price*ytd_sales), [Сред, цена] =AVG (price), [Макс, тираж] =MAX(ytd_sales) FROM titles GROUP BY type, pub_id Будет возвращен результат:

type pub_id Объем продаж Сред. цена Макс. тираж business 0736 2.9900 55978. psychology 0736 131223.6700 11.4825 mod cook 11.4900 107135. psychology 21. 8096. trad cook 0877 15.9633 249637. UNDECIDED 0877 NULL NULL NULL business 1389 210036.3000 17.3100 popular comp 1389 283401.0000 21.4750 (8 row(s) affected) Теперь добавим функцию суперагрегирования ROLLUP:

SELECT type, pub_id, [Объем продаж]=SUM(price*ytd_sales), [Сред, цена]=AVG(price), [Макс, тираж]=MAX(ytd_sales) FROM titles GROUP BY type, pub_id WITH ROLLUP Будет получен следующий результат:

Как видно, в результате добавилось семь строк (отмечены жирным шрифтом).

Для каждой из групп по столбцу type была добавлена строка с результатом вы Часть V. Программирование полнения функции агрегирования по столбцу p u b i d. Так как агрегирование выполняется по столбцу p u b i d, то в нем находится значение NULL, ПО которому можно легко определить строки, полученные в результате суперагрегирования.

Например, для группы business в строке, являющейся результатом суперагре гирования, для столбца объем продаж выведено значение 266015.0800, что яв ляется результатом выполнения функции агрегирования SUM (сумма) для всех строк исходных данных (не результата), относящихся к группе business в целом. В столбце сред, цена указывается результат функции AVG (среднее) для столбца price исходных данных со значением business в столбце type. На примере средней цены видно, что результат суперагрегирования получается на основе исходных данных, а не результата агрегирования. В столбце макс, тираж выводится максимальное число по столбцу y t d s a i e s, получаемое в результате выполнения функции агрегирования МАХ (максимум).

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

Итак, итогом использования оператора WITH ROLLUP является суперагрегирова ние по выражениям, на основании которых производится агрегирование. Но суперагрегирование не выполняется по первому выражению. Однако в некото рых ситуациях необходимо произвести суперагрегирование по всем выражени ям, участвующим в группировке. Для этого служит оператор WITH CUBE. Пере пишем предьщущии запрос для использования оператора WITH CUBE:

SELECT type, pub_id, [Объем продаж]=SUM(price*ytd sales), [Сред, цена]=AVG(price), [Макс, тираж]=MAX(ytd_sales) FROM titles GROUP BY type, pub_id WITH CUBE Глава 29. Выборка данных / 15. 0877 364868.9700 NULL 18.9760 493437. NULL (18 row(s) affected) По сравнению с предыдущим примером в результат выборки добавилось еще три строки. Эти строки являются результатом агрегирования выборки по столб цу p u b i d, когда подсчитываются значения функций агрегирования для всех строк с одинаковым значением в столбце p u b i d. Новые строки были добавле ны в самый конец выборки.

Часть V. Программирование Для сравнения выполним тот же запрос без разделов WHERE И HAVING:

SELECT s t a t e, count(au_lname), max(au_lname) F O a u t h o r s GROUP BY ALL s t a t e RM Будет возвращен такой результат:

state (8 row(s) affected) Как видно, в первом запросе в разделе WHERE существовало ограничение, запре щающее обработку строк об авторах, проживающих в Калифорнии (СА). Однако в разделе GROUP BY был указан параметр ALL, предписывающий выводить все группы несмотря на наложенные логические условия. В итоге результат выпол нения запроса содержит строку для штата Калифорния, но самих данных нет. В разделе HAVING было указано дополнительное условие для игнорирования авто ров из штата Юта (ит). В итоге результат выборки вообще не содержит никакого упоминания о штате Юта несмотря на использование параметра ALL.

Глава 29. Выборка данных указаны два запроса, возвращающих одинаковое количество столбцов, при чем столбцы должны иметь совместимые типы данных и располагаться в од ном и том же порядке. Запрос может быть как очень простым (SELECT * FROM tabie_name), так и очень сложным с применением всех описанных ранее разделов.

П ALL По умолчанию в объединение не допускается вставка повторяющихся строк.

То есть, если второй запрос возвращает строку, идентичную одной из строк, образованных первым запросом, то такая строка не будет включена в объе динение. Однако с помощью параметра ALL МОЖНО заставить сервер вклю чать в объединение все строки, возвращенные запросами, независимо от то го, повторяются они или нет.

С помощью раздела UNION МОЖНО объединять более двух запросов. Для этого после второго запроса достаточно указать ключевое слово UNION И создать тре тий запрос. Таким же образом можно добавить четвертый запрос, пятый и т. д.

Общее количество объединяемых запросов может составлять несколько десят ков. Однако на практике обычно количество объединяемых запросов не превы шает пяти.

Имена столбцов объединения будут соответствовать именам столбцов, возвра щаемых первым запросом. Имена столбцов остальных запросов игнорируются.

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

Типы данных столбцов запросов, данные из которых будут объединены в один столбец, должны быть совместимыми. В идеале типы данных должны быть пол ностью одинаковы, но все же допускаются некоторые расхождения.

При объединении двух СИМВОЛЬНЫХ столбцов (char, varchar, nchar И nvarchar) разной длины столбец объединения будет иметь тип данных, соответствующий строке большей длины. Также при объединении двоичных данных (binary и varbinary) столбец результата получит тип данных, соответствующий наиболь шему из объединяемых столбцов. При объединении столбцов нецелочисленных ТИПОВ данных (money, smallmoney, float, numeric И decimal) столбец результата будет иметь тип данных, соответствующий исходному типу данных с наиболь шей точностью. Кроме того, аналогичный результат будет и при объединении целочисленных данных ( i n t, smaliint и t i n y i n t ) с нецелочисленными — объе динение станет иметь нецелочисленный тип данных с наибольшей точностью.

Если все соответствующие столбцы исходных запросов запрещают хранение значений NULL, ТО И В соответствующем столбце результата хранение значений NULL будет запрещено. Однако если хоть один из объединяемых столбцов раз решает хранение NULL, ТО И В объединении для соответствующего столбца будет разрешено хранение NULL.

При попытке объединения столбцов с несовместимыми типами данных сервер выдаст сообщение об ошибке.

Часть V. Программирование Рассмотрим использование раздела UNION ДЛЯ СЛИЯНИЯ данных из t i t i e b u s i n e s s и title_psychoiogy. Для этого сначала создадим эти таблицы на основе таблицы t i t l e s, выбрав из нее строки, относящиеся в каждой из серий:

SELECT title_id, title, price, ytd_sales INTO titles_business FROM titles WHERE type='business' SELECT title_id, title, price, ytd_sales INTO titles_psychology FROM titles WHERE type='psychology' Эти два запроса создают необходимые таблицы и копируют в них нужные дан ные. Просмотрим содержимое этих таблиц:

SELECT * F O t i t l e s _ p s y c h o l o g y RM Будет получен следующий результат:

title_id title price ytd_sales PS1372 Computer Phobic AND Non-Phobic Indiuals:

PS2091 Is Anger the Enemy?

PS2106 Life Without Fear PS3333 Prolonged Data Deprivation: Four CaStudie PS7777 Emotional Security: A New Algorithm (5 row(s) affected) Теперь просмотрим содержимое таблицы tities_business:



Pages:     | 1 |   ...   | 29 | 30 || 32 | 33 |
 





 
© 2013 www.libed.ru - «Бесплатная библиотека научно-практических конференций»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.