указать список столбцов для каждой комап.ты INSERT, псио.чьзуя синтаксис наподобие этого:

INSERT INTO Ttems (UemNufiffier, Price)

VALUESdOOl. 123,45)

Также обратите (иш.чкшие, что нет пеобходи.мости указывать столбцы в списке в то.м порядке, в котором они определены в таблице, однако порядок значе-HHii должен соотве1ствовать порядку c-iO..i6uoB в списке. Вот нри.мер:

INSERT INTO i:ems (Price. IlemNu[n.ber) VALUES(123,45, iOOl)

Euie одно за.мечаине: в Transact-SQL юиочевое слово INTO опционально. Это отклоне}1Ие от стандарта ANSI SQL и больиншства других диалектов SQL, Синтаксис ниже эквивалентен п])слыдуще,\1у запросу:

INSERl items (Price, ItemNumber) VALUES(123.45. 1001)

Модификация данных

Многие в конечном счете хотят изметтть дан}иле, которые они загрузили в базу данных. Это .можно сделать с ио.мощыо ко.маиды SQL UPDATE :

UPDATE customers SET Zip B6753-090C-WHERE City=Reo

В зависи.мости от дани11Х предложение WHERE в этом зап1)осе может ограничить UPDATE одной или М}10ги.\и1 заиися.чш. Вы можете .модифтишровать все записи в таблице, опустив пре/июжеиие WHERE:

UPDATE customers

SET State=CA

Также можно модифицировать столбец, испо;Н)Зуя столбцы той же са.мой таблицы, включая са.м столбец, как здесь: UPDATE orders

SET АтоипГ=Атоип1+(Атоип^.07)

Transact-SQL предоставляет за.\1ечате;илюе i)acujHpcinie ко.манды SQL UPDATE, позволяющее за.менять значения в одной таблице на зиачеиия из другой. Вот пример:

иРОАГЕ о

SET Amount-Pnce

FROM orders о JOIN items : ON (o,ItemNumber-i.ItemNumber)

Удаление данных

Ко.манда SQL DELETE смужиг д.чя ущикння данных из таблицы. Чтобы сразу удалить все записи из таблицы, используйте с.чедуюптй стнггаксис: DELETE FROM customers

Так же как и в команде INSERT, ключевое слово FROM является онциональны.м. Как и UPDATE, DELETE может включать предложение WHERE для определения удаляемых записей. Вот пример:

DELETE FROM customers WHERE LastNanieoDoe

SQL Server предоставляет более быстрый способ очистки таб^тицы, наподобие ко.манды ZAP в dBASE, следующим образо.м: TRUNCATE TABLE customers

TRUNCATE TABLE очишает таблицу без журнализации удаления записей в журнале транзакций. Эту команду нельзя использовать с таблица.ми, задействоваными в ограничениях внешних ключей (FOREIGN KEY constraints), и эта команда делает неактуальным журнал транзакций для всей базы данных. После того как журнал транзакций стал неактуальным, к нему нельзя применить операцию резервного копирования до создания следующей полной резервной копии базы данных. TRUNCATE TABLE также ипюрирует триггеры, определенные для таблицы, так что DELETE-триггеры не сработают даже при том, что с технической точки зрения записи удаляются из таблицы (c.vi. главу 4, Внутренняя организация DDL ).

Выборка данных

Команда SELECT служит для получения данных из таблиц и нредставлений. Вы указываете, что вы хотите сделать с по.мошью оператора SELECT, и сервер обслуживает вас - предоставляет результирую1]1ее множество - коллекцию записей, содержащих запрошенные вами данные. SELECT - это универсальный инструмент стандартного SQL. С помощью этой ко.манды мож}ю объединять таблицы, получать необходимые данные, присваивать значения локальным переменным и даже создавать другие таблицы. Можно с уверенностью предположить, что вы будете использовать оператор SELECT чаще любых других команд Transact-SQL.

Давайте начнем исследова}1ие SELECT с получения содержимого только что со,здан}1ых таблиц. Выполните SELECT * FROM tablename

в Query Analyzer, за.менив tablename на имя каждой из трех таблиц. Таблицы customers и items содержат по три записи, в то время как orders - четыре:

SELECT * FROM customers (результаты сокраще}1ы)

CustomerNumber LastName FirstName StreetAddress

1 Doe John 123 Joshua Tree

2 Doe Jane 123 Joshua Tree

3 Citizen John 57 Riverside SELECT * FROM orders

OrderNumber OrderDate CustomerNumber ItemNumber Amount

101 1990-10-18 00:00:00.000 1 lOCl 123.45

102 1992-02-27 00

103 1995-05-20 00

104 1997-11-21 00 SELECT * FROM items

00.000 2 1002 678.90

00.000 3 1003 86753.09

00.000 1 1002 678.90

ItemNumber Description Price

1001 iJIDGET A 123,45

1002 WIDGET В 678,90

1003 WIDGET С 86753,09

Списки столбцов

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

SELECT CustomerNumber. LastName. State FROM customers

CustomerNumber LastName State

1 Doe TX

2 Doe TX

3 Citizen CA

Столбцы в SELECT могут включать ссылки на столбцы таблицы, локальные иере.мен}1ые, абсолютные значения и выражения, состоящие из любых комбинаций этих элементов.

SELECT - переменные и выражения

в отличие от больщинства разновидностей SQL, предложение FROM является опциональным в Transact-SQL, когда SELECT не применяется к объектам базы данных. Вы можете создавать операторы SELECT, которые возвращают переменные (автоматические или локальные), функции, ко}1Станты или осуществляют некоторые вычисления, без использования предложения FROM. Например, SELECT GETOATEO

возвращает системную дату компьютера, на котором установлен SQL Server, и SELECT CASTdO-l AS CHAR(2)) + 7<ASTCP0WER(2,5)-5 AS CHAR(2))+719-i-CAST(30-i-31 AS CHAR(2))

возвращает простую строку. В отличие от Oracle и многих других СУБД, SQL Server не требует наличия предложения FROM, если не считает это необходимым. Вот пример, возвращающий значение автоматической переменной:

SELECT AVERSION

И еще один, возвращающий и.мя текущего пользователя:

SELECT SUSER SNAME()

@@VERSION - это предопределенная в SQL Server автоматическая переменная, доступная только для чтения. SQL Server Books Online теперь называет эти переменные функциями, но на са.мом деле это не функции в истин}юм смысле этого слова, это предопределенные константы или автоматические переменные (например, они могут быть использованы в качестве параметров храни.мых процедур, а настоящие функции не могут). Термин переменные мне нравится больше.

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

Функции

функции могут применяг1)СЯ для промежуточной .\KJДифпкaции значегтй столбцов. Transact-SQL предоставляет большой набор функций, которые можно разделить на шесть основных i-ругнг. строковые, числовые, функции для работы с дата.ми, агрегатные, системн)>1е и функцтн! для работы с метадати1ыми. Вот пример функции Transact-SQL в де11ствни:

SELECT UPPERdastName), Firsthame FROM customers

F i rsiNa.Tie

DOE John

DOt Jane

CITIZEN John

Здесь функция UPPER() используется для преобразования столбца LastName к верхнему perncTjjy, и в таком виде он попадает в 1)езульти1)уюи1ее множество. Функция затрапшает только результирующее мт!ожество - данные, на основании которых оно получается, остаются неизменными.

Преобразование типов данных

Преобразование типов данных осуществляется довольно н])осто. Вы ,vюжeтe ис-иользовагь или функцию CAST(), или функцию CONVERT() для приведения одного типа данных к друго.му, однако 0\ST() - метод, совместимый с SQL-92.

Вот прттмер оператора SELECT, в котором столбец Amount таблицы orders преобразуется в си.мвольную строку:

SELECT CAST{Amount AS varchdr) FROM orders

123.45 678.9C 86753.09 678.90

Вот ири.мер, иллюстрирующий преобра;юваннс значения типа datetime в строку с использованием задашгого (})ормата: SELECT C0NVERT{char(8). GEIDATFC).112)

19690720

В данной ситуации воз.чюжносги CONVERT() значительно И1)евосходят CAST(). CONVERTO поддерживает пара.чет]) ст]1ля (третий а])тумепт в njjHMepe выше), указывающий точньн! формат, которьй! необходн.мо использовать при преобразовании значения тина datetime в сгтмвадьную строку. Вы можете наг1ти таблицу

иочдержнваемых criuieii и Books Online, но стили 102 и 112, пожалу!!, самые распростра}1синые.

CASE

Во лиюгпх примерах в этой книге часто применяется функция UkSE. UKSE имеет две основные формы. В простой фор.мс вы указываете результируюите значения для каждого члена последовательности выражени! !, которые сравниваются с определителем или ключевым вырал<еиие.м, например так:

SELECT CASE sex WHEN О THEN Unknown WHEN 1 THEN Male WHEN 2 THEN Female ELSE Not applicaole END

В более сложной фо[)ме, известной как комплексный (searched) UkSE, вы указываете отдельные результирующие значения для .многих, воз.можио, различных, логических выражений, подобно этому:

SELECT CASE

WHEN BATEDIFF(dd.RentDueOate.GElDATE())>:5 Desposn.

WHEN DATEDIFF(dd,RentDueDate.GETDAlL())>5 IHFN DailyPenalty*

DATEDIFF(dd.RentDueOate.GFTDATF())

ELSE 0

Данная фор.ма CASE подобна вложенным IF,.. ELSE, каждое! WHEN соответствует новое предложение ELSE.

Лично мне никогда не нравился синтакстк; CASE. Мне нравится идея функции CASE, ио я нахожу ее синтаксис иеуклюжи.м. Она ведет себя подобно функции, так как может быть вложена в другие выражения, но синтаксически больше похожа на оператор управления ходом выно.пшиия. В некоторых языках программирования CASE - это конструкция управления ходом выполнения, аналог оператора switch в C/C++. В Transact-SQI. CASE используется аналогично встроенному или немедленному* оператору IF -- она возвращает значение, основанное на логике если-то-ииаче .

Честно говоря, я считаю, что с точки зрения синтаксиса было бы разумнее сделать нечто, подобное это.му:

CASE(sex, О, Unknown, 1, Male, 2, Female, Unkrown)

или

CASE(DATEBIFF(dc,RentDueDate,GETDATE())>i5, DeposU, DATEDIFF(Gd,RentDueDate,GET0ATE())>5, DaiiyPenalty* DATED!FF(dd,RentBueDate,GETDATE()),С)

Так работает функция DECODE() в Oracle, Эта форма более компактна и воспринимается легче, чем громоздкий ANSI-синтаксис CASE,

Агрегатные столбцы

Агрегатные столбцы состоят из специа/ня1ых фуикцп11, которые осуществляют некоторые вычисления на множестве данных, При.меры аг№гат()в - функции COUNT(),

Фильтрация данных

Для фильтрации данных, возвращаемых операторо.м SELECT, служит предложение WHERE. Оно также может быть использовано д./1я ограничинш записей, на которые воздействуют операторы UPDATE или DELETE. Вот несколько запросов, в которых WHERE ири.меняется для фильтрации возвращаемых х^тшх:

SELECT UPpER(LastName). FirstName FROM customers WHERE State=TX

FirstName

DOE John

DOE Jane

Следующий код ограничивает вoзвpaщae.vIыx клиентов теми, у которых в адресе содержится слово Joshua :

SELECT LastName. FirstName. StreetAddress FROM customers WHERE StreetAddress LIKE Woshua

LastName FirstName StreetAddress

Doe John 123 Joshua Tree

Doe Jane 123 Joshua Tree

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

Вот запрос, возвращающий заказы, npCBbmarouH-ie S500:

SELECT OrderNumber. OrderDate. Amount

FROM orders

WHERE Amount > 500

SUMO, AVG(), MIN(), STDDEVO, VAR() и MAX(). Их дучшс всего мол<но понять на примере. Вот команда, возвраща10ща>1 общее количество записей о клиентах; SELECT COUNTC*) FROM customers

Вот еще одна, которая возвращает значение макси.мального заказа в долларах: SELECT MAX(Amount) FROM orders

И вот еще одна, которая возвращает су.м.марное значение всех заказов в долларах:

SELECT SUM(AmounL) FROM orders

Агрегатные функции часто применяются совместно с предложением GROUP BY оператора SELECT (рассмотрим ниже) для получения сгруппированных или разделенных агрегатов. Они также .могут использоваться и для других целей (на-при.мер, чтобы скрыть обычно недопустимый синтаксис), это будет показано в главах, посвященных статистическим вычислениям.

OrderNuiicer OrderDate Amount

102 103

1992-02-27 OC-.00;OO.COO 678.90 1995-05-20 00:00:00.000 66753.09

1C4 1997-11-21 00:00:00.000 678.90

В следующем примере задействован оператор BETWEEN, для то1Ю чтобы получить заказы, полученные между октябрем 1990-го и маем 1995 года, включительно Я включил время во вторую дату, потому что, если его не указать, по умолчанию время будет равно полуночи (в SQL Server столбцы типа datetime всегда хранят и дату и время; если время не указано, оно считается равным полуночи) и данные будут получены не включительно. Если не указать временную часть даты, запрос вернет только заказы, размеще1Н1ые до первых миллисекунд 31 .мая: SELECT OrderNumber. OrderDate. Amount FROM orders WHERE OrderDate BETWEEN 10/01/90 AND 05/31/95 23:59:59.999

OraerNumber OrderDate Amount

10: 1990-10-18 00:00:00.000 123.45

102 1992-02-27 00:00:00.000 678.90

103 1995-05-20 00:00:00.000 86753.09

Соединения (Joins)

Запросы, которые получают все иеобходи.мые им данные из од1юй таблицы, встречаются достаточно редко. Джон Донн сказал: Никто не живет на необитаемом острове . То же самое .можно сказать о таблицах. Обычно запрос должен обра-пггься к двум или больше таблица.м, чтобы найти всю необходимую информащпо. Так обстоят дела в мире реляционных баз данных. Данные обычно намеренно распределены, чтобы хранить их настолько модульно, насколько это возможно.

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

Для работы с такими разделенными данными и предназначены соединения. Соединение собирает данные из двух таб;пщ в одно результируюигее множество. Фактически таблицы не соедипепы; они просто так представляются в записях, возвращаемых запросом. Миожествепные соединения .\югут связывать М1юже-ство таблиц - глубоко вложенные соединения, в которых участвует миолсество таблиц, - это нор.мальное явление.

Соединение двух таблиц осуществляется при 1юмоип1 связывания столбца или столбцов одной таблицы со столбцами другой (CROSS JOIN являются исключением, но подробно о них поговори.м позже). Выражение, используемое для соединения двух таблиц, составляет условие соединения, или критерий соединения. Когда соединение удачно, данные из второй таблргцы объединяются с данньь\1и из первой для формирования составного результирующего множества - мнол<е-ства записей, содержащего данные из обеих таблиц. Короче говоря, у двух таблиц появляется ребенок, хотя и недолговечный.

Существует два основных тина соелинеи1н1, впутраише и внешние. Ключевое различие между ними состоит в том, что внешние соединения вюночают записи в результирующее множество, .даже если условие соединения не выполняется; в случае внутреннего соединения такие записи не попадут в результирующее .множество. Как это происходит? Какие данные попадают в ])езультирующее множество, когда условие соединения нарушается? Когда условие соединения во внешнем соединении не выполтгяется, столбцы первой таблицы возвращаются как обьшно, но столбцы из второй таблицы возвращаются без значений - как - NULL. Это удобно для 1юиска пропущенных значений и нарушенных связей между таблица,\н1.

Сушествует два вида синтаксиса для создания соединений - устаревший и совмести.мьн ! с ANSI/ISO SQL-92. Устаревший синтаксис относится к то.му времени, когда SQL Server сов.местно поддерживался Sybase и Microsoft. Этот синтаксис более краткий, че.м ANSL

SELECT customers.CustomerNumber. orders.Amount FROM customers, orders

WHERE customers.CustomerNumber=orders.CustomerNumber CustomerNumber Amount

1 123.45

2 67B.90

3 86753.09 1 67B.90

Обратите вгш.маиие па использование предложения WHERE для соединетш таблиц customers и orders. Это внутреннее соединение. Если для данного клиента не существует заказа, клиент вообще не окажется в списке. Вот ANSI-версия этого же запроса:

SELECT customers.CustomerNumber. orders Amount

FROM customers JOIN orders ON (customers.CustomerNumber-orders.CustomerNumber)

On немного длиннее, но конечный результат тот л<:е: таблицы customers и orders соединяются с применением соответстпующих столбцов CustomerNumber.

Как я уже упоминает ранее, .многоуровневые соединения в запросах - обычная практика. Вот пример многоуровневого соединения с использованием устаревшего синтаксиса:

SELECT customers.CustomerNumber. orders.Amount, items.Description FROM customers. orders, items

WHERE customers.CustomerNumber=orders.CustomerNumber AND orders.ItemNumber=items.ItemNumber

CustomerNumber Amount Description

1 123.45 WIDGET A

2 678.90 WIDGET В

3 B6753.09 WIDGET С 1 678.90 WIDGET В

Этот запрос соединяет комгюзицию таблиц customers и orders с таблицей items. Обратите вии.мание, что точный порядок в части WHERE не важен. Чтобы позволить серверу полностью оптимизировать запрос, по])ядок предикатов в предло-

1 123.45 WIDGET A

1 678.90 WIDGET Б

2 678.90 WIDGET E

3 86753.03 WIDGET С

женин WHERE не должен влиять па pesvvuiTiipvKjmee множество. Oiui должны быть ассоциативны - запрос должен возвращать чог же са.мый результат, независи.мо от порядка обработки предикатов.

Как и Li случае соединения двух таб.чнц, синтаксис ANSI для пloгoтaбличныx jjHVTpeHHiix соединений аналогичен устаревшему синтаксису. Вот ANSI-синтак-спс для миоготабличных соединен1йи

SELECT cjSiomers.CubtoirierNumber, orders.Air.ojnt, iterrS.Description

FROM customers JOIN orders ON (customers.CjsluiiierNunber=orders.CustomerNumber)

JOIN items ON (orders.ItemNumoeritems.ItemKumter)

И снова on немного длиннее, однако выполняет ту же самую функцию.

Внешние соединения

До настоящего времени не было абсолют11ого различия .между ANSI и устарев-ии!М CHHTaKCHCo.vi. Хотя синтаксически они не идентичны, 4>уикционально они эквивалентны.

Все меняется в случае внешних соединений. ANSl-cnirraKCnc внешних соединений устраняет двусмысленности, возникающие при использовании предложения WHERE (чьи условия ассоциативны по определению) для соединения таб-Л1Н1. Вот пример устаревшего синтаксиса, содержанцп! такие неоднозначности;

-- Плохой SQL - не запускайте

SELECT customers. CustomerNumber. orders. Amount, terns .Description FROM customers, orders, items

WHERE customers .CustomerNumber*=order5 .C-JSioinerNumDer AND orders.ItemNumber*=items.ItemNumber

He пытайтесь выполнить это - SQL Server не позволтгг. Почему? Пото.му что условия в прсдложеипи WHERE должны бьггь ассоциативны, но эти таковы.ми не яв.пяются. Ноли сначала будут соединены таблицы customers и orders, те записи, в которых существуют клиенты, но отсутствуют заказы, не удастся соединить с таблицей items (товары), так как столбец ItemNumber будет равен NULL Между тем, если сначала будут соединены таблицы orders и items, результирующее множество будет включать записи о товарах (ITEM), которые в противном случае были бы пропущены. Таким образом, порядок уечовш ! в предложенриг имеет существенное значение при создании .\нюгоу]ювиевых соединен1Йг с испо;и>зоваии-см устаревшего синтаксиса.

Как раз из-за этой неоднозначности - важности порядка предикатов в предложении WHERE - в стандарте SQL-92 конструкции соединения перемещены в предложение FROM. Вот вышеупомянутьи'! запрос, перегшсанный с исиользо-вание.м правильного ANSI-синтаксиса соединений:

SELECT customers.CustomerNumber, orders.Amount, items,Description

FROM customers LEFT OUTER JOIN orders ON

(customers.CustomerNuinber=orders.CustomerNumber)

LEFT OUTER JOIN items ON (orders, ItemNumber-i tems. ItemNumber-)

CustomerNuinber Amount .Description

Теперь неоднозначность исчезла, и ясно видно, что запрос, как и предполагается, сначала соединяет таблицы customers и orders, а затем результат соединяется с таблицей items. (Ключевое слово OUTER опционально.)

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

SELECT customers.CustomerNumber. orders.Amount FROM customers, orders

WHERE customers.CustomerNumber*=orders.CustomerNumber AND orders.Amount>600

CustomerNumber Amount

1 678.90

2 678.90

3 86753.09

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

SELECT customers.CustomerNumber+2. orders.Amount FROM customers, orders

WHERE customers.CustomerNumber+2* Orders.CustomerNumber AND Orders.Amount>600

Эта версия просто добавляет 2 к CustomerNumber для гарантии что, но крайней мере, несколько соединений не будут выполнены и столбцы таблицы orders будут равны NULL. Вот результирующее множество:

CustomerNumber Amount

3 86753.09

4 NULL

5 NULL

Видите проблему? Двух последних записей быть не должно. Сум.мы заказа (Amount) в них равны NULL (потому что для к/н-1ентов 4 и 5 не существует заказов), и неизвестно, превышают ли они $600,

Предполагается, что запрос должен вернуть только те записи, для которых известно, что значение столбца Amount превышает $600, однако он этого не делает. Вот ANSI-версия этого же запроса:

SELECT customers,CustomerNumber+2. orders,Amount FROM customers LEFT OUTER JOIN orders ON (customers .CustomerNumber<-2=orders. CustomerNumber) WHERE orders.Amount>600

CustomerNumber Amount

3 86753.09

Синтаксис SQL-92 корректно пропускает записи, в которых значение Amount равно NULL, Причина некорректной работы запроса со старым синтаксисом заключается в го,м, что предикаты в предложении WHERE вычисляются вместе.