Извлечение данных при помощи Transact-SQL

Список выборки

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

au_fname, au_lname

Метасимвол "*". В списках выборки можно применять звездочку (символ "*"), являющуюся метасимволом (подстановочным символом, "джокером"), обозначающим все колонки из всех таблиц и представлений, указанных в предложении FROM запроса. Например, чтобы вернуть все колонки всех строк таблицы sales из базы данных pubs, воспользуйтесь таким запросом:

SELECT   		* 
FROM     		sales 
GO

Далее в этой лекции будет раздел "Перекрестные соединения", в котором описано, что произойдет, если в предложении FROM оператора SELECT, содержащего звездочку, указать более одной таблицы.

Выражения IDENTITYCOL и ROWGUIDCOL. Чтобы извлекать значения из идентифицирующей колонки таблицы (identity column, см. лекцию 10), вы можете применять в списках выборки просто выражение IDENTITYCOL. Ниже дан пример запроса для базы данных Northwind, у которой таблица Employees (Сотрудники) имеет идентифицирующую колонку:

USE      			Northwind 
GO 
SELECT   		IDENTITYCOL 
FROM     		Employees 
GO

Набор результатов этого запроса будет выглядеть примерно так:

EmployeeID  
-------------
3 
4 
8 
. 
. 
. 
9 
(всего 9 строк)

Обратите внимание, что заголовок колонки в наборе результатов – такой же, как имя колонки этой таблицы, обладающей свойством IDENTITY (в нашем случае – EmployeeID).

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

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

Когда в разных таблицах имеются колонки с одинаковыми именами, вы можете захотеть, чтобы в выводе в заголовках выдавались не только имена колонок, но и имена таблиц; это улучшит понимание выводимой информации. Рассмотрим теперь примеры с колонкой lname таблицы employee базы данных pubs. Можно дать такой запрос:

USE      			pubs 
GO 
SELECT   		lname 
FROM     		employee 
GO

Этот запрос выдаст такие результаты:

lname 
----------
Cruz 
Roulet 
Devon 
. 
. 
. 
O’Rourke 
Ashworth 
Latimer
(всего 43 строки)

Если вы хотите, чтобы в наборе результатов вместо имеющегося заголовка "lname" выводился бы заголовок "Фамилия сотрудника", подчеркнув тем самым, что фамилии взяты из таблицы сотрудников, то надо применить ключевое слово AS, вот так:

SELECT   		lname AS "Фамилия сотрудника"
FROM     		employee 
GO

Эта команда выдаст такой вывод:

Фамилия сотрудника 
-------------------------
Cruz 
Roulet 
Devon 
. 
. 
. 
O’Rourke 
Ashworth 
Latimer
(всего 43 строки)

Алиасы колонок можно применять в сочетании с другими типами выражений в списке выборки, а также их можно применять в предложениях ORDER BY для ссылок на колонки. Предположим, что в списке выборки имеется вызов функции. Выводу этой функции можно назначить алиас (т.е. имя), воспользовавшись ключевым словом AS после вызова функции. Если при вызове функции не пользоваться алиасом, то колонка ее вывода не будет вообще иметь никакого заголовка. Ниже дан пример оператора, назначающего заголовок "Maximum Job ID" выводу функции MAX:

SELECT   		MAX(job_id) AS "Maximum Job ID" 
FROM     		employee 
GO

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

Алиас колонки, заданный в предложении SELECT, может применяться в качестве аргумента предложения ORDER BY, он позволяет предложению ORDER BY ссылаться на колонку из предложения SELECT. Это полезно, когда в списке выборки содержится функция, вывод которой должен быть отсортирован. Ниже приведен пример команды, выдающей количество проданных книг в каждом из магазинов, причем вывод отсортирован по этому количеству. В предложении ORDER BY применяется алиас Quantity_of_Books, заданный в списке выборки:

SELECT   		SUM(qty) AS Quantity_of_Books, stor_id  
FROM     		sales 
GROUP BY 	stor_id 
ORDER BY 	Quantity_of_Books 
GO

В этом примере алиас не заключен в кавычки, потому что он не содержит пробелов.

Если бы в этом запросе мы не задали бы алиас для колонки SUM(qty), то мы могли бы поместить в предложение ORDER BY не алиас, а SUM(qty), как показано в приведенном ниже примере. Вывод был бы таким же, за исключением того, что колонка сумм проданных книг выводилась бы без заголовка:

SELECT   		SUM(qty), stor_id FROM sales 
GROUP BY 	stor_id 
ORDER BY 	SUM(qty)
GO

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

Предложение FROM

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

Производные таблицы

Производная таблица (derived table) – набор результатов оператора SELECT, вложенный в предложение FROM. Набор результатов вложенного оператора SELECT используется как таблица, из которой извлекаются данные внешний оператор SELECT. Ниже дан пример запроса, использующего производную таблицу, чтобы найти имена всех магазинов (stores), предоставляющих хотя бы один вид скидок (discounts):

USE      			pubs 
GO 
SELECT   		s.stor_name 
FROM     		stores AS s, (SELECT stor_id, COUNT(DISTINCT discounttype) 
                         						AS d_count 
                         						FROM   	      discounts 
                         						GROUP BY   stor_id) AS d 
WHERE    		s.stor_id = d.stor_id AND 
        			d.d_count >= 1  
GO

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

Заметьте, что в этом запросе применяются сокращения для имен таблиц (s для таблицы stores (магазины) и d для таблицы discounts (скидки)). Эти сокращения называются алиасы таблиц (см. раздел "Алиасы таблиц" далее).

Соединения таблиц

Соединение таблиц (joined table) – это набор результатов от операции соединения (join operation), выполненной над двумя или несколькими таблицами. Над таблицами можно выполнять различные типы соединений: внутренние соединения, полные внешние соединения, левые внешние соединения, правые внешние соединения, перекрестные соединения. Давайте рассмотрим их более подробно.

Внутренние соединения. Внутреннее соединение (inner join) является типом соединений, принятым по умолчанию. Внутреннее соединение задает набор результатов, в который будут включены лишь те строки таблиц, которые соответствуют условию ON, а все несоответствующие строки будут отброшены. Чтобы задать соединение, применяйте ключевое слово JOIN. Для задания условия поиска, на котором основывается соединение, применяется ключевое слово ON. Ниже приведен пример запроса, в котором соединяются таблицы stores и discounts; этот запрос показывает, в каких магазинах предоставляются скидки и типы этих скидок. (Это соединение – внутреннее, потому что по умолчанию применяется внутреннее соединение; поэтому будут возвращены лишь строки, соответствующие условию поиска ON.)

SELECT   		s.stor_id, d.discounttype 
FROM     		stores s JOIN discounts d 
ON       			s.stor_id = d.stor_id 
GO

Набор результатов будет выглядеть примерно так:

stor_id  	discounttype 
------------------------------
8042     	Customer Discount

Как видите, скидки предоставляет лишь один магазин, причем скидки лишь одного типа. Выданная строка является единственной строкой с совпадающими значениями stor_id из таблицы stores и stor_id из таблицы discounts. Возвращаются это значение stor_id и соответствующее ему значение discounttype.

Полные внешние соединения. Полное внешнее соединение (full outer join) задает набор результатов, состоящий как из строк, соответствующих условию ON, так и из строк, не соответствующих условию ON. Для строк, не соответствующих условию ON, значением колонки, несоответствующей условию, станет NULL. В нашем примере NULL будет означать, что либо магазин не предоставляет никаких скидок (если некоторое значение stor_id имеется в таблице stores, но отсутствует в таблице discounts), либо этот тип скидки не предоставляется никаким магазином. Запрос в следующем примере полностью совпадает с предыдущим, за исключением того, что в нем стоят ключевые слова FULL OUTER JOIN:

SELECT   		s.stor_id, d.discounttype 
FROM     		stores s FULL OUTER JOIN discounts d 
ON       			s.stor_id = d.stor_id 
GO

Набор результатов будет выглядеть примерно так:

stor_id  		discounttype 
---------		-----------------
NULL     		Initial Customer 
NULL     		Volume Discount 
6380     		NULL 
7066     		NULL 
7067     		NULL 
7131     		NULL 
7896     		NULL 
8042     		Customer Discount

Условию поиска будет соответствовать лишь одна строка в наборе результатов (последняя). Остальные строки имеют NULL в одной из колонок.

Левые внешние соединения. Левое внешнее соединение (left outer join) возвращает строки, в которых произошло соответствие условию поиска, плюс все строки из таблицы, заданной слева от ключевого слова JOIN. Ниже дан наш старый пример запроса, но теперь в нем используются ключевые слова LEFT OUTER JOIN:

SELECT   		s.stor_id, d.discounttype 
FROM      		stores s LEFT OUTER JOIN discounts d 
ON           			s.stor_id = d.stor_id 
GO

Набор результатов будет выглядеть примерно так:

stor_id  		discounttype 
-------		---------------------
6380     		NULL 
7066     		NULL 
7067     		NULL 
7131     		NULL 
7896     		NULL 
8042     		Customer Discount

В набор результатов попадут и те строки из таблицы stores, для которых не нашлось строк из таблицы discounts с совпадающим значением stor_id (в колонке discounttype набора результатов в этих строках будет стоять значение NULL ). В набор результатов попадет также одна строка, для которой произошло соответствие условию ON.

Правые внешние соединения. Правое внешнее соединение (right outer join) противоположно левому внешнему соединению: в него войдут строки, соответствующие условию поиска, плюс все строки из таблицы, заданной справа от ключевого слова JOIN. Ниже дан наш старый пример запроса, но теперь в нем используются ключевые слова RIGHT OUTER JOIN:

SELECT   		s.stor_id, d.discounttype 
FROM     		stores s RIGHT OUTER JOIN discounts d 
ON       			s.stor_id = d.stor_id

Набор результатов будет выглядеть примерно так:

stor_id  		discounttype 
-------		--------------------
NULL     		Initial Customer 
NULL     		Volume Discount 
8042     		Customer Discount

В набор результатов попали и те строки из таблицы discounts, которые не имеют значений stor_id, совпадающих со значениями, имеющимися в таблице stores (значением stor_id для этих строк в наборе результатов станет NULL ). В набор результатов попадет также строка, соответствующая условию ON.

Перекрестные соединения. Перекрестное соединение (cross join) – это произведение двух таблиц, в котором не задано предложение WHERE. Когда предложение WHERE задано, перекрестное соединение работает как внутреннее соединение. А без предложения WHERE будет возвращаться такой результат: каждая строка из первой таблицы сопоставляется с каждой строкой из второй таблицы, поэтому размер набора результатов будет равен числу строк первой таблицы, умноженному на число строк второй таблицы.

Чтобы понять, что такое перекрестное соединение, давайте рассмотрим еще несколько примеров. Сначала рассмотрим перекрестное соединение без предложения WHERE, а затем рассмотрим три примера перекрестных соединений с предложениями WHERE. Ниже даны три простых примера. Запустите эти запросы и обратите внимание на количество строк в наборах результатов.

SELECT   		* 
FROM     		stores 
GO

SELECT   		* 
FROM     		sales 
GO 
 
SELECT   		* 
FROM     		stores CROSS JOIN sales 
GO

SELECT   		* 
FROM     		stores, sales 
GO

Чтобы не потонуть в массе ненужной информации (если информации выдано больше, чем нужно), мы можем сузить запрос, добавив в него предложение WHERE, вот так:

SELECT   		* 
FROM     		sales CROSS JOIN stores  
WHERE    		sales.stor_id = stores.stor_id 
GO

Этот оператор возвращает только те строки, которые соответствуют условию поиска в предложении WHERE, что уменьшит набор результатов до 21 строки. Из-за предложения WHERE перекрестное соединение работает как внутреннее соединение (т.е. возвращаться будут лишь строки, соответствующие условию поиска). Запрос, показанный в последнем примере, возвращает строки из таблицы sales, соединенные со строками из таблицы stores, имеющими совпадающие значения stor_id. Строки, у которых нет совпадений, не возвращаются.

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

SELECT    		sales.*, stores.city 
FROM      		sales CROSS JOIN stores 
WHERE     		sales.stor_id = stores.stor_id 
GO

Этот запрос возвращает все колонки из таблицы sales (продажи), к которым добавлена колонка city ("город) из таблицы stores, для строк с одинаковыми значениями stor_id. Фактически, в набор результатов будет включены названия городов для магазинов, в которых проводились продажи, присоединенные к строкам из таблицы sales, имеющим значения stor_id, совпадающие со значениями stor_id из таблицы stores.

Ниже приведен еще один пример запроса, не имеющий символа "*" (из таблицы sales извлекается только колонка stor_id):

SELECT    		sales.stor_id, stores.city 
FROM      		sales CROSS JOIN stores 
WHERE     		sales.stor_id = stores.stor_id 
GO

Алиасы таблиц

Вы уже видели несколько примеров, в которых применялись алиасы имен таблиц (слово алиас означает замена имени, псевдоним). Ключевое слово AS является необязательным, т.е. "FROM имя_таблицы AS алиас" будет работать точно так же, как и "FROM имя_таблицы алиас". Давайте вернемся к запросу, приведенному в качестве примера в разделе "Правые внешние соединения", в котором применяются алиасы:

SELECT   		s.stor_id, d.discounttype 
FROM     		stores s RIGHT OUTER JOIN discounts d 
ON       			s.stor_id = d.stor_id 
GO

Каждая из двух таблиц из этого примера имеет колонку stor_id. Чтобы различать, какую именно из этих двух колонок вы применяете в запросе, нужно перед именем колонки задать имя таблицы или алиас (отделив точкой). В нашем примере для таблицы stores применяется алиас s, а для таблицы discounts применяется алиас d. Задавая колонку мы должны ставить перед ее именем "s." или " d.", указывая тем самым, в какой таблице содержится эта колонка. Тот же самый запрос с применением ключевых слов AS будет выглядеть так:

SELECT   		s.stor_id, d.discounttype 
FROM     		stores AS s RIGHT OUTER JOIN discounts AS d 
ON       			s.stor_id = d.stor_id 
GO

Предложение INTO

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

SELECT <список_выборки> INTO<новая таблица>

вы можете извлекать данные из таблицы или из нескольких таблиц и помещать строки результатов в новые таблицы. Новые таблицы создаются автоматически при исполнении оператора SELECT ... INTO и определены в соответствии с колонками из списка выборки. Каждая колонка в новой таблице будет иметь тип данных, такой же, как и у исходных колонок, и будет иметь имя, заданное в списке выборки. Пользователь должен иметь полномочие на создание таблиц ( CREATE TABLE ) в той базе данных, где будет создана новая таблица (в целевой базе данных). (О том, как задавать полномочия см. лекцию 34.)

Оператор SELECT ... INTO можно применять для помещения строк как во временные, так и в постоянные таблицы. Для локальных временных таблиц (видимых только для текущего соединения или пользователя) вы должны поместить перед именем таблицы символ "#". Для глобальных временных таблиц (видимых для всех пользователей) вы должны поместить перед именем таблицы два символа "#" (т.е. "##"). Временная таблица автоматически уничтожается после того, как все пользователи, которые с ней работают, отсоединятся от SQL Server. Чтобы помещать данные в постоянную таблицу, не нужно применять префикс целевых таблиц, но для целевой базы данных должна быть включена настройка Select Into/Bulk Copy. Чтобы включить эту настройку для базы данных pubs, вы можете выполнить такой оператор OSQL:

sp_dboption pubs, "select into/bulkcopy", true 
GO

Для включения этой настройки можно также применить SQL Server Enterprise Manager, вот так:

SELECT   		employee.fname, employee.lname, jobs.job_desc  
INTO     		emp_info 
FROM     		employee, jobs 
WHERE    		employee.job_id = jobs.job_id 
GO

Таблица emp_info будет иметь три колонки – fname, lname и job_desc, типы данных которых будут такими же, как и у колонок, заданных в исходных таблицах (employee и jobs). Если вы хотите, чтобы новая таблица была локальной временной таблицей, то перед ее именем надо поместить символ "#" (вот так: #emp_info), а чтобы новая таблица была глобальной временной таблицей, поместите перед ее именем "##" (вот так: ##emp_info).

Предложение WHERE и условия поиска

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

Давайте сначала договоримся об используемой терминологии. Условие поиска (search condition) может содержать произвольное количество предикатов (predicates), соединенных логическими операциями (logical operators) AND, OR и NOT (И, ИЛИ и НЕ). Предикаты –это выражения, возвращающие значения TRUE, FALSE или UNKNOWN (ИСТИНА, ЛОЖЬ или НЕИЗВЕСТНО). Выражение (expression) может быть именем колонки, константой, скалярной функцией (т.е. функцией, возвращающей одно значение), переменной, скалярным подзапросом (т.е. запросом, возвращающим одну колонку), либо комбинацией этих элементов, соединенных операциями. В этом разделе нашей книги термин "выражение" применяется также и к предикатам.

Операции сравнения

В выражениях можно использовать операции сравнения (comparison operators), перечисленные в табл. 14.1.

В простом предложении WHERE может производиться сравнение двух выражений при помощи операции сравнения на равенство (=). Ниже приведен пример оператора SELECT, проверяющего значения в колонке lname для всех строк (а эти значения имеют тип данных char) и возвращающего TRUE, если это значение равно "Latimer" (в набор результатов будут включены строки, для которых возвращается значение TRUE ):

SELECT   		* 
FROM     		employee 
WHERE    		lname = "Latimer"
GO

Запрос, приведенный в этом примере, вернет одну строку. Имя Latimer должно быть задано в кавычках, потому что оно является текстовой строкой.

В следующем запросе применяется операция неравенства ( <> ), на этот раз по отношению к колонке job_id, имеющей тип данных integer:

SELECT   		job_desc 
FROM     		jobs 
WHERE    		job_id <> 1 
GO

Этот запрос выдает текст с описанием должностных обязанностей из строк таблицы jobs, имеющих значения job_id, не равные 1. Будет выдано 13 строк. Если в строке содержится значение NULL, то оно считается не равным ни 1, ни какому- либо другому значению, поэтому будут выведены и строки null-значениями.

Логические операции

Логические операции (logical operators) AND и OR проверяют два выражения и, в зависимости от их значений, возвращают булево значение TRUE, FALSE или UNKNOWN. Операция NOT выдает булево значение, противоположное значению выражения, следующего за ним. Значения, возвращаемые операциями AND, OR и NOT, показаны в таблицах на рис. 14.3. Таблицами для операций AND и OR надо пользоваться так: найдите результат первого выражения в левой колонке, найдите результат второго выражения в верхней строке, а затем посмотрите результат логической операции в ячейке на пересечении соответствующих строки и колонки. Таблица для операции NOT – совсем понятная. Результат может получить значение UNKNOWN, если среди операндов имеется значение NULL.

В следующем запросе в предложении WHERE имеются два выражения, соединенных логической операцией AND:

SELECT   		job_desc, min_lvl, max_lvl 
FROM     		jobs  
WHERE    		min_lvl 	>= 100 AND 
         			max_lvl 	<= 225 
GO

Как видно из таблицы (рис. 14.3), операция AND возвращает значение TRUE, когда оба условия возвращают TRUE. Этот запрос вернет четыре строки.

В следующем запросе операция OR применяется для поиска издателей из Вашингтона (федеральный округ Колумбия) и из Массачусетса. Будут возвращены строки, для которых значение TRUE явилось результатом хотя бы одного из условий.

SELECT   		p.pub_name, p.state, t.title 
FROM     		publishers p, titles t 
WHERE    		p.state = "DC" OR 
         			p.state = "MA" AND 
         			t.pub_id = p.pub_id 
GO

Этот запрос вернет 23 строки.

Операция NOT возвращает просто отрицание булева значения выражения, следующего за ним. Например, чтобы получить список всех названий книг, у которых авторские отчисления составляют не менее 20%, можно применить операцию NOT:

SELECT   		t.title, r.royalty 
FROM     		titles t, roysched r 
WHERE    		t.title_id = r.title_id AND NOT 
         			r.royalty  < 20 
GO

Этот запрос выдаст 18 названий книг, у которых авторские отчисления составляют 20% или более.

Другие ключевые слова

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

LIKE. Ключевое слово LIKE применяется для поиска по соответствию шаблону. Соответствие шаблону (pattern matching) проверяется для двух операндов условия поиска – сопоставляемого выражения (match expression) и шаблона (pattern), задающего условие поиска. При этом используется такой синтаксис:

<сопоставляемое_выражение> LIKE <шаблон>

Если сопоставляемое выражение соответствует шаблону, то возвращается булево значение TRUE, а если нет, то возвращается FALSE. Сопоставляемое выражение должно иметь тип данных character string, в противном случае SQL Server преобразует его в данные, имеющие тип character string, если это возможно.

Шаблоны являются строковыми выражениями (string expressions), т.е. строками, состоящими из символов (characters) и метасимволов (wildcard characters). Метасимволы – это символы, имеющие особый смысл при использовании внутри строковых выражений. Метасимволы, которые можно применять в шаблонах, перечислены в табл. 14.2.

Чтобы лучше понять применение ключевого слова LIKE и метасимволов, рассмотрим несколько примеров. Если нужно найти в таблице authors все фамилии, начинающиеся с буквы S, то можно воспользоваться таким запросом с метасимволом %:

SELECT  		au_lname 
FROM     		authors 
WHERE    		au_lname LIKE "S%"
GO

Набор результатов может быть, например, таким:

au_lname 
-----------
Smith 
Straight 
Stringer

В этом запросе "S%" означает, что нужно возвращать все строки с фамилиями, первой буквой которых будет S, а за ней может следовать произвольное количества букв.

Чтобы извлечь информацию об авторе, идентификатор которого начинается с числа 724, и, зная, что все идентификаторы авторов имеют формат как у номеров социального страхования (три цифры, тире, затем две цифры, затем еще тире, и затем четыре цифры), вы можете воспользоваться метасимволом "_", вот так:

SELECT   		* 
FROM     		authors 
WHERE    		au_id LIKE "724-__-____"
GO

В набор результатов попадут две строки со следующими значениями au_id: 724-08-9931 и 724-80-9391.

А теперь давайте рассмотрим пример применения метасимвола []. Чтобы получить фамилии авторов, начинающиеся на букву от A до M, можно воспользоваться метасимволом [] в сочетании с метасимволом %, вот так:

SELECT   		au_lname 
FROM     		authors 
WHERE    		au_lname LIKE "[A-M]%"
GO

Набор результатов будет содержать 14 строк с фамилиями, начинающимися на букву от A до M (или 13 строк, если пользоваться порядком сортировки, чувствительным к регистру букв).

Если в этом запросе поменять метасимвол [] на [^], то будут выданы строки, содержащие фамилии, начинающиеся на буквы вне диапазона от A до M:

SELECT   		au_lname 
FROM     		authors 
WHERE    		au_lname LIKE "[^A-M]%"
GO

Такой запрос вернет 9 строк.

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

SELECT   		au_lname 
FROM     		authors 
WHERE    		au_lname LIKE "[A-M]%" OR 
         			au_lname LIKE "[a-m]%"
GO

Имя "del Castillo" попадет в набор результатов, и это будет отличать этот запрос от запроса, чувствительного к регистру, находящего только ПРОПИСНЫЕ буквы от A до M.

Перед ключевым словом LIKE можно помещать операцию NOT. NOT LIKE выдает строки, не соответствующие заданному условию. Например, чтобы найти названия книг, не начинающиеся со слова The, можно воспользоваться таким запросом:

SELECT   		title 
FROM     		titles 
WHERE    		title NOT LIKE "The %"
GO

Этот запрос вернет 15 строк.

Пользуясь ключевым словом LIKE, вы можете дать волю своей фантазии. Однако будьте осторожны и проверяйте, что ваши запросы работают именно так, как задумано. Если не поставить NOT или символ ^ там, где это надо, то набор результатов будет противоположен ожидаемому. Если не поставить в нужном месте метасимвол %, то результат тоже будет неправильным. И не забывайте о том, что первые и завершающие пробелы тоже участвуют в сопоставлении шаблону.

ESCAPE. При помощи ключевого слова ESCAPE можно задать сопоставление шаблону, содержащему сами знаки, служащие для обозначения метасимволов (т.е., сами символы ^, %, [, ] и _). Для этого вы должны задать после ключевого слова ESCAPE так называемый escape-символ, обозначающий, что символ, следующий за ним, следует понимать "как есть". Например, чтобы найти все строки из таблицы, имеющие символ подчеркивания в колонке title, можно применить такой запрос:

SELECT   		title 
FROM     		titles 
WHERE    		title LIKE "%e_%" ESCAPE "e"
GO

Этот запрос не вернет ни одной строки, потому что ни в одном названии книги нет символов подчеркивания.

BETWEEN. Ключевое слово BETWEEN используется всегда в сочетании с ключевым словом AND и задает диапазон вхождения, применяемый как условие поиска. Ниже показан его синтаксис:

<проверяемое_выражение> BETWEEN <начальное_выражение> AND <конечное_выражение>

Если проверяемое_выражение больше или равно, чем начальное_выражение, и в то же время меньше или равно, чем конечное_выражение, то результатом условия поиска является булево значение TRUE, а в противном случае результатом условия поиска является FALSE.

Ниже приведен пример запроса, в котором BETWEEN применяется для поиска названий книг с ценой от 5 до 25 долларов:

SELECT   		price, title 
FROM     		titles 
WHERE    		price BETWEEN 5.00 AND 25.00
GO

Этот запрос вернет 14 строк.

Вместе с BETWEEN тоже можно применять NOT, тогда будут искаться строки, не входящие в заданный диапазон. Например, чтобы найти названия книг с ценами вне диапазона от 20 до 30 долларов (а именно, книг дешевле 20 и дороже 30 долларов), можно было бы применить такой запрос:

SELECT   		price, title 
FROM     		titles 
WHERE    		price NOT BETWEEN 20.00 AND 30.00
GO

В конструкции с ключевым словом BETWEEN проверяемое_выражение должно иметь такой же тип данных, как и начальное_выражение и конечное_выражение.

В последнем примере колонка price (цена) имеет тип данных money, поэтому и начальное_выражение, и конечное_выражение должны быть числами, сравнимыми с типом данных money, или для них должно быть возможным неявное преобразование в этот тип данных. Вы не можете применять в качестве проверяемого выражения значение из колонки price, а для начального и конечных выражений – символьные строки (т.к. они имеют тип данных char). Если вы все-таки сделаете это, то SQL Server выдаст сообщение об ошибке.

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

SELECT   		au_lname 
FROM     		authors 
WHERE    		au_lname BETWEEN "Bennet" AND "McBadden" 
GO

Этот запрос позволяет найти фамилии авторов, которые при сортировке в алфавитном порядке попали бы между фамилиями "Bennet" и "McBadden". Поскольку границы диапазона BETWEEN считаются входящими в проверяемый диапазон, то фамилии "Bennet" и "McBadden" тоже войдут в набор результатов запроса (эти фамилии имеются в таблице).

IS NULL. Ключевое слово IS NULL используется в условиях поиска, ищущих строки, содержащие null -значение в заданной колонке. Например, чтобы найти в таблице titles названия книг, у которых нет данных в колонке notes (примечания), т.е. когда значением колонки notes является NULL, можно воспользоваться таким запросом:

SELECT   		title, notes 
FROM     		titles 
WHERE    		notes IS NULL
GO
Этот запрос выдаст такой набор результатов:
title                                  	notes 
----------------------------------------	------
The Psychology of Computer Cooking     	NULL

Как видите, null -значения в колонке notes в наборе результатов отображаются словом NULL. Это слово не является содержимым колонки, оно служит просто обозначением того, что в данной колонке находится null-значение. (Помните, в лекции 10 объяснялось, что null -значения – это неизвестные значения.)

Чтобы найти названия книг, у которых имеются данные о колонке notes (т.е., у которых значение колонки notes не является null -значением), применяйте конструкцию IS NOT NULL, вот так:

SELECT   		title, notes 
FROM     		titles 
WHERE    		notes IS NOT NULL
GO

Набор результатов будет содержать 17 строк, в каждой из которых в колонке notes будут иметься символы (один или несколько), т.е. не null -значения.

IN. Ключевое слово IN используется как условие поиска, проверяющее, не соответствует ли проверяемое выражение какому-либо из значений в подзапросе или в списке значений. Если соответствие найдено, то возвращается значение TRUE. NOT IN возвращает отрицание того, что получилось бы при применении IN, поэтому NOT IN возвратит значение TRUE, если проверяемое выражение не найдено в подзапросе или в списке значений. Используется такой синтаксис:

<проверяемое_выражение> IN (<подзапрос>)

или

<проверяемое_выражение> IN (<список_значений>)

Подзапрос – это оператор SELECT, который возвращает в наборе результатов только одну колонку. Подзапрос должен быть заключен в скобки. Список значений – это просто список из нескольких значений, разделенных запятыми и заключенный в скобки. Колонка, получающаяся как набор результатов подзапроса или список значений, должна иметь такой же тип данных, как и проверяемое выражение. Если необходимо, SQL Server выполнит неявное преобразование типов данных.

В приведенном ниже примере запроса ключевое слово IN применяется в сочетании со списком значений для поиска идентификаторов должностей для трех описаний должностных обязанностей:

SELECT   		job_id 
FROM     		jobs 
WHERE    		job_desc IN ("Operations Manager",  
                      			      "Marketing Manager", 
                      			      "Designer")
GO

В этом запросе применен такой список значений: (Operations Manager, Marketing Manager, Designer). Запрос будет возвращать идентификаторы должностей из строк, содержащих в колонке job_desc какое-либо значение из списка. Благодаря ключевому слову IN ваш запрос проще и понятней для восприятия, чем запрос, который получился, если бы вы применили две операции OR, вот такой:

SELECT   		job_id 
FROM     		jobs 
WHERE    		job_desc = "Operations Manager" OR  
        	job_desc = "Marketing Manager"  OR   
         	job_desc = "Designer"
GO

В следующем запросе ключевое слово IN используется в одном операторе дважды – первый раз с подзапросом, а второй раз – со списком значений внутри подзапроса:

SELECT   		fname, lname    	-- Внешний запрос 
FROM     		employee 
WHERE    		job_id IN (	SELECT 	job_id   -- Внутренний запрос, подзапрос 
                     				FROM   	jobs 
                   					WHERE  	job_desc IN ("Operations Manager", 
                     					"Marketing Manager", 
                     					"Designer"))
GO

Сначала будет искаться подзапрос (в нашем примере – набор значений job_id ). Значения job_id получаются как результаты подзапроса и не выводятся на экран, они используются внешним запросом как выражения для его собственного условия поиска IN. Окончательный набор результатов будет содержать имена и фамилии всех сотрудников, должности которых называются Operations Manager, Marketing Manager или Designer.

Набор результатов будет таким (запрос вернет 11 строк):

fname                		lname 
------------------------
Pedro                		Afonso 
Lesley               		Brown 
Palle                		Ibsen 
Karin                		Josephs 
Maria                		Larsson 
Elizabeth     	Lincoln 
Patricia             		McKenna 
Roland               		Mendel 
Helvetius            	Nagy 
Miguel               		Paolino 
Daniel               		Tonini

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

SELECT   		pub_name 
FROM     		publishers 
WHERE    		state NOT IN (	"CA", 
                       			"TX", 
                       			"IL")
GO

Этот запрос вернет пять строк, у которых значение в колонке state (штат) не является обозначением ни одного из трех штатов, указанных в списке значений. Если вы установили настройку ANSI nulls вашей базы данных на ON, то набор результатов будет содержать только три строки. Такое малое количество выданных строк произошло потому, что две из пяти строк первоначального набора результатов в качестве значения для state будут иметь NULL, а значения NULL не извлекаются при настройке ANSI nulls заданной как ON.

Чтобы узнать, какова настройка ANSI nulls для базы данных pubs, запустите следующую системную хранимую процедуру:

sp_dboption "pubs", "ANSI nulls" 
GO

Если настройка ANSI nulls задана как OFF, то измените ее на ON при помощи следующего оператора:

sp_dboption "pubs", "ANSI nulls", TRUE 
GO

А чтобы изменить настройку с ON на OFF, примените такой же оператор, но с FALSE вместо TRUE.

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

EXISTS (<подзапрос>)

Если имеется хоть одна строка, удовлетворяющая подзапросу, то возвращается значение TRUE.

Чтобы найти имена авторов, у которых имеются опубликованные книги, можно применить такой запрос:

SELECT   		au_fname, au_lname 
FROM      		authors  
WHERE     		EXISTS (	SELECT au_id 
                  			FROM   titleauthor 
                  			WHERE  titleauthor.au_id = authors.au_id)
GO

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

CONTAINS и FREETEXT. Ключевые слова CONTAINS и FREETEXT используются для полнотекстного поиска в колонках, имеющих символьные типы данных. Это обеспечивает большую гибкость, по сравнению с возможностями ключевого слова LIKE. Например, благодаря ключевому слову CONTAINS, можно искать слова, обладающие не точным соответствием, а некоторой похожестью на заданное слово или фразу (такое сопоставление называется "нечетким"). При помощи FREETEXT можно искать слова, соответствующие или нечетко соответствующие заданной строке поиска или ее части. Найденные слова не обязаны ни полностью совпадать со строкой поиска, ни следовать в порядке расположения слов в строке поиска. Эти два ключевых слова могут применяться различными способами, они обеспечивают разнообразные возможности полнотекстного поиска, рассмотрение которых выходит за рамки нашей книги.

Предложение GROUP BY

Предложение GROUP BY применяется после предложения WHERE и означает, что строки набора результатов должны быть сгруппированы в соответствии с данными в колонке группировки. Если в предложении SELECT используется агрегатная функция, то для каждой группы вычисляется и отображается в выводе итоговое агрегатное значение. Агрегатная функция выполняет вычисления и возвращает значение. (Про агрегатные функции см. раздел "Агрегатные функции" далее.)

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

SELECT   		title_id, SUM(qty) 
FROM     		sales 
GROUP 		BY title_id
GO

Будет выдан набор результатов, содержащий 16 строк:

title_id 
----------------------
BU1032            			15 
BU1111            			25 
BU2075            			35 
BU7832            			15 
MC2222            			10 
MC3021            			40 
PC1035            			30 
PC8888            			50 
PS1372            			20 
PS2091           			108 
PS2106            			25 
PS3333            			15 
PS7777            			25 
TC3218            			40 
TC4203            			20 
TC7777            			20

Этот запрос не содержит предложения WHERE – оно не нужно. Набор результатов состоит из колонки title_id (идентификатор названия книги) и итоговой колонки, не имеющей заголовка. Для каждого отдельного названия книги будет подсчитано общее количество экземпляров этой книги, это число будет показано в итоговой колонке. Например, пусть значение BU1032 колонки title_id встретится в таблице sales (продажи) два раза, первый раз оно будет обозначать продажу 5 экземпляров книги (колонка qty будет иметь значение 5), а во второй раз будет обозначать продажу книг по другому заказу, на этот раз будет продано 10 экземпляров книги. Агрегатная функция SUM произведет суммирование этих двух продаж, отсюда и получится, что общее количество проданных экземпляров равно 15, что и будет показано в итоговой колонке. Если вы хотите, чтобы итоговая колонка имела заголовок, воспользуйтесь ключевым словом AS, вот так:

SELECT      		title_id, SUM(qty) AS "Колич прод"
FROM         		sales 
GROUP BY 	title_id
GO

Теперь набор результатов станет показывать заголовок для итоговой колонки (в наборе результатов содержится 16 строк):

itle_id  		Колич прод
----------------------------
BU1032            				15 
BU1111            				25 
BU2075            				35 
BU7832            				15 
MC2222            				10 
MC3021            				40 
PC1035            				30 
PC8888            				50 
PS1372            				20 
PS2091           				108 
PS2106            				25 
PS3333            				15 
PS7777            				25 
TC3218            				40 
TC4203            				20 
TC7777            				20

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

SELECT   		type, pub_id, AVG(price) AS "Средняя цена"
FROM     		titles 
GROUP BY 	type, pub_id
GO
В набор результатов попадут 8 строк:
type         		pub_id 	                 Средняя цена
--------------------------------------------------
business     		0736                       		2.99 
psychology   		0736                      		11.48 
UNDECIDED    		0877                       		NULL 
mod_cook     		0877                      		11.49 
psychology   		0877                      		21.59 
trad_cook    		0877                      		15.96 
business     		1389                      		17.31 
popular_comp 	    1389                      		21.48

Обратите внимание, что книги, имеющие тип psychology и business, попали в набор результатов более одного раза, потому что они сгруппированы для разных идентификаторов издательств. Значение NULL, показанное в качестве средней цены книг типа UNDECIDED (нераспределенные), отражает тот факт, что для книг этого типа в таблицу не были введены их цены, поэтому невозможно вычислить среднюю цену.

Предложение GROUP BY можно применять с необязательным ключевым словом ALL, означающим, что в набор результатов должны быть включены все группы, даже не соответствующие условию поиска. Группы, не имеющие строк, соответствующих условию поиска, будут содержать в итоговой колонке значение NULL, поэтому их будет сразу видно. Например, чтобы узнать среднюю цену для книг, имеющих авторские отчисления 12%, а также показать в наборе результатов строки для книг, имеющих авторские отчисления не 12% (у них в итоговой колонке будет значение NULL), группируя книги сначала по типам, а затем по идентификатору издательства, выполните такой запрос:

SELECT   		type, pub_id, AVG(price) AS "Средняя цена" 
FROM     		titles 
WHERE    		royalty = 12 
GROUP BY 	ALL type, pub_id
GO

В набор результатов попадут 8 строк:

type         				pub_id 	Средняя цена
-------------------------------------------------
business     		0736                       		NULL 
psychology   		0736                      		10.95 
UNDECIDED    		0877                       		NULL 
mod_cook     		0877                      		19.99 
psychology   		0877                       		NULL 
trad_cook    		0877                       		NULL 
business     		1389                       		NULL 
popular_comp 	    1389                       		NULL

Будут выведены строки для всех типов книг, но для типов книг, у которых не имеется книг с 12-процентными авторскими отчислениями, появится NULL.

Если мы уберем ключевое слово ALL, то набор результатов будет содержать информацию только для тех типов книг, у которых имеются книги с 12-процентными авторскими отчислениями. Набор результатов будет содержать 2 строки и будет таким:

type         				pub_id 	Средняя цена
---------------------------------------------------
psychology   		0736                      		10.95 
mod_cook     		0877                      		19.99

Предложение GROUP BY часто применяется в сочетании с предложением HAVING, про которое мы сейчас вам расскажем.

Предложение HAVING

Предложение HAVING применяется, чтобы задать условия поиска для групп или для агрегатной функции. Предложение HAVING чаще всего используется после предложения GROUP BY в случаях, когда условие поиска должно проверяться уже после группировки результатов. Если условие поиска можно было бы проверить до группировки, то гораздо эффективней было бы поместить его в предложение WHERE, а не пользоваться предложением HAVING (за счет этого уменьшилось бы количество строк, участвующих в группировке). Если предложение GROUP BY отсутствует, то HAVING может применяться только в отношении агрегатной функции в списке выборки. В этом случае предложение HAVING действует точно так же, как предложение WHERE. Если попытаться использовать HAVING как-нибудь по-другому, то SQL Server выдаст сообщение об ошибке.

Предложение HAVING имеет такой синтаксис:

HAVING <условие_поиска>

Здесь условие_поиска имеет такой же смысл, что и условие поиска. (См. раздел "Предложение WHERE и условие поиска" выше в данной лекции). Единственным различием между предложениями HAVING и WHERE является то, что предложение HAVING может содержать агрегатную функцию в условии поиска, а предложение WHERE – нет.

Ниже приведен пример запроса, использующего предложение HAVING для поиска книг, сгруппированных по типам и по издательствам, средняя цена на которые превышает 15 долларов:

SELECT   		type, pub_id, AVG(price) AS "Средняя цена"
FROM     		titles 
GROUP           BY type, pub_id 
HAVING   		AVG(price) > 15.00 
GO

В набор результатов попадут 4 строки:

type         		pub_id 	  Средняя цена
--------------------------------------------------
psychology   		0877           		21.59 
trad_cook    		0877                15.96 
business     		1389                17.31 
popular_comp 	    1389                21.48

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

SELECT   		type, pub_id, AVG(price) AS "Средняя цена" 
FROM     		titles 
GROUP BY 	type, pub_id 
HAVING   		AVG(price) >= 15.00 AND 
         			AVG(price) <= 20.00
GO

В набор результатов попадут 2 строки:

type         	pub_id 	  Средняя цена
--------------------------------------------------
trad_cook    	0877      15.96 
business     	1389      17.31

Такой же результат получится, если вместо AND применить предложение BETWEEN, вот так:

SELECT   	type, pub_id, AVG(price) AS "Средняя цена"
FROM     	titles 
GROUP BY 	type, pub_id 
HAVING   	AVG(price) BETWEEN 15.00 AND 20.00
GO

Чтобы применять HAVING без предложения GROUP BY, нужно иметь агрегатную функцию в списке выборки и в предложении HAVING. Например, чтобы выводить сумму цен на книги типа mod_cook (современная кулинария), только в тех случаях, когда эта сумма будет превышать 20 долларов, можно применять такой запрос:

SELECT   		SUM(price) 
FROM     		titles 
WHERE    		type = "mod_cook"
HAVING   		SUM(price) > 20
GO

Если в этом запросе поместить выражение SUM(price) > 20 в предложение WHERE, то SQL Server выдаст сообщение об ошибке, т.к. в предложениях WHERE агрегатные функции применять нельзя.

Предложение ORDER BY

Предложение ORDER BY применяется, чтобы задать порядок, в котором должны сортироваться строки набора результатов. Пользуясь ключевыми словами ASC и DESC, вы можете задать как возрастающий (ascending, от меньших значений к большим), так и убывающий (descending, от больших значений к меньшим) порядок сортировки. Если порядок сортировки не указать, то по умолчанию будет применяться возрастающий порядок сортировки. В предложении ORDER BY можно задать более одной колонки. Результаты будут сортироваться по первой из заданных колонок, но если в первой колонке встретятся строки с одинаковыми значениями, то они будут сортироваться в порядке возрастания значения из второй колонки, и т.д. Как вы увидите из материала данного раздела, такая сортировка особенно полезна при использовании вместе с предложением GROUP BY. Давайте сначала рассмотрим пример, в котором предложение ORDER BY работает с одной колонкой и сортирует список авторов по фамилии , в возрастающем порядке:

SELECT   	au_lname, au_fname 
FROM     	authors 
ORDER BY 	au_lname ASC
GO

Набор результатов будет отсортирован в алфавитном порядке по фамилиям авторов. Не забудьте, что чувствительность сортировки к регистру букв, заданная вами при инсталляции SQL Server, повлияет на обработку фамилий вроде "del Castillo".

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

SELECT   	job_id, lname, fname 
FROM     	employee 
ORDER BY 	job_id, lname, fname
GO

Набор результатов (43 строки) будет выглядеть так:

job_id lname                          			fname 
-----------------------------------
     2 Cramer                         		Philip 
     3 Devon                          		Ann 
     4 Chang                          		Francisco 
     5 Henriot                        		Paul 
     5 Hernadez                       		Carlos 
     5 Labrune                        		Janine 
     5 Lebihan                        		Laurence 
     5 Muller                         		Rita 
     5 Ottlieb                        		Sven 
     5 Pontes                         		Maria 
     6 Ashworth                       		Victoria 
     6 Karttunen                      		Matti 
     6 Roel                           		Diego 
     6 Roulet                         		Annette 
     7 Brown                          		Lesley 
      7 Ibsen                          		Palle 
     7 Larsson                        		Maria 
     7 Nagy                           		Helvetius 
     . .                              .
     . .                              .
     . .                              .   
    13 Accorti                        		Paolo 
    13 O’Rourke                       		Timothy 
    13 Schmitt                        		Carine 
    14 Afonso                         		Pedro 
    14 Josephs                       		Karin 
    14 Lincoln                        		Elizabeth

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

А теперь давайте рассмотрим предложение ORDER BY, работающее совместно с предложением GROUP BY и агрегатной функцией:

SELECT   	type, pub_id, AVG(price) AS "Средняя цена" 
FROM     	titles 
GROUP BY 	type, pub_id 
ORDER BY 	Средняя цена 
GO

Набор результатов (8 строк) будет выглядеть так:

type         				pub_id 	Средняя цена
-------------------------------------------------
UNDECIDED    		0877                       		NULL 
business     		0736                       		2.99 
business     		1389                      		17.31 
mod_cook     		0877                      		11.49 
popular_comp 	    1389                      		21.48 
psychology   		0736                      		11.48 
psychology   		0877                      		21.59 
trad_cook    		0877                      		15.96

Результаты отсортированы в алфавитном порядке (возрастающем) по типу книг. Также обратите внимание, что в этом запросе в предложении GROUP BY должны присутствовать и type, и pub_id, потому что они не являются частью агрегатной функции. Если вы не зададите колонку pub_id в предложении GROUP BY, то SQL Server выдаст сообщение об ошибке (рис. 14.4).

В предложении ORDER BY нельзя применять агрегатные функции и подзапросы. Однако если вы зададите алиас агрегатной функции в предложении SELECT, то его применить можно в предложении ORDER BY, вот так:

SELECT   	type, pub_id, AVG(price) AS "Средняя цена" 
FROM     	titles 
GROUP 		BY type, pub_id 
ORDER 		BY type
GO

Набор результатов (8 строк) будет выглядеть так:

type         				pub_id 	Средняя цена 
-------------------------------------------------
UNDECIDED    		0877                       		NULL 
business     		0736                       		2.99 
business     		1389                      		17.31 
mod_cook     		0877                      		11.49 
popular_comp 	    1389                      		21.48 
psychology   		0736                      		11.48 
psychology   		0877                      		21.59 
trad_cook    		0877                      		15.96

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

Операция UNION

UNION считается не предложением (clause), а операцией (operator). Она используется для объединения результатов двух или нескольких запросов в один набор результатов. Применяя UNION, вы должны соблюдать два следующих правила:

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

Кроме того, соответственные колонки должны иметь совместимые типы данных. Это значит, что соответственные колонки должны иметь либо одинаковые типы данных, либо SQL Server сможет выполнить неявное преобразование одного типа данных в другой. Ниже дан пример применения операции UNION, соединяющей наборы результатов двух операторов SELECT, выдающих колонки city и state из обеих таблиц publishers и stores:

SELECT   		city, state 
FROM     		publishers 
UNION    		SELECT 	city, state 
         			FROM   	stores
GO

Набор результатов (14 строк) будет таким:

city               state 
-------------------------
Fremont              	CA 
Los Gatos               CA 
Portland             	OR 
Remulade             	WA 
Seattle              	WA 
Tustin               	CA 
Chicago              	IL 
Dallas               	TX 
Munchen              	NULL 
Boston               	MA 
New York             	NY 
Paris                	NULL 
Berkeley             	CA 
Washington           	DC

Две эти колонки, city и state, в обеих таблицах имеют одинаковый тип данных (char), поэтому преобразование типа не потребуется. Заголовки колонок для набора результатов операции UNION берутся из первого оператора SELECT. Если вы хотите создать алиас для заголовка, то поместите его в первый оператор SELECT, вот так:

SELECT   		city AS "Все города", state AS "Все штаты"
FROM     		publishers 
UNION    		SELECT 	city, state 
         			FROM   	stores
GO

Набор результатов (14 строк) будет таким:

Все города           			Все штаты 
------------------------------------
Fremont              			CA 
Los Gatos            			CA 
Portland             			OR 
Remulade             			WA 
Seattle              			WA 
Tustin               			CA 
Chicago              			IL 
Dallas               			TX 
Munchen              			NULL 
Boston               			MA 
New York             			NY 
Paris                			NULL 
Berkeley             			CA
Washington           			DC

При объединении наборов результатов вовсе не обязательно указывать в обоих предложениях SELECT одни и те же колонки. Например, вы можете выбрать колонки city и state из таблицы stores и колонки city и country из таблицы publishers, вот так:

SELECT   		city, country
FROM     		publishers 
UNION    		SELECT 	city, state 
         			FROM   	stores
GO

Набор результатов (14 строк) будет таким:

city                country 
----------------------------
Fremont             CA 
Los Gatos           CA 
Portland            OR 
Remulade            WA 
Seattle             WA 
Tustin              CA 
New York            USA 
Paris               France 
Boston              USA 
Munchen             Germany 
Washington          USA 
Chicago             USA 
Berkeley            USA 
Dallas              USA

В этом наборе результатов верхние шесть строк берутся из набора результатов запроса, применяемого к таблице stores, а последние восемь строк берутся из набора результатов запроса, применяемого к таблице publishers. Колонка state имеет тип данных char, а колонка country имеет тип данных varchar. Так как два этих типа данных являются совместимыми, то SQL Server выполнит неявное преобразование таким образом, чтобы обе колонки стали иметь тип varchar. Колонки итогового набора результатов имеют заголовки city и country, так, как указано в первом операторе SELECT, но здесь было бы лучше, чтобы вторая колонка имела заголовок не country (страна), а State or Country (штат или страна).

Единственное ключевое слово, которое можно применять вместе с UNION – это необязательное ключевое слово ALL. Если вы примените его, то в набор результатов будут включены также все повторяющиеся строки (другими словами, в набор результатов будут включены полностью все строки). Если ключевое слово ALL не применяется, то по умолчанию из набора результатов будут исключены все дублирующиеся строки.

ORDER BY можно применять не в каждом операторе SELECT объединения, а только в самом последнем. Благодаря этому ограничению, итоговый набор результатов будет отсортирован только один раз сразу для всех результатов. С другой стороны, вы можете применять GROUP BY и HAVING в отдельных операторах, так как они влияют только на отдельные наборы результатов, а не на итоговый набор результатов. Ниже приведен пример, иллюстрирующий объединение результатов двух операторов SELECT, каждый из которых имеет предложение GROUP BY:

SELECT   		type, COUNT(title) AS "Number of Titles"  
FROM     		titles 
GROUP BY 	type 
UNION    		SELECT   		pub_name, COUNT(titles.title) 
         			FROM     		publishers, titles 
         			WHERE    		publishers.pub_id = titles.pub_id 
         			GROUP BY 	pub_name 
GO

Набор результатов (9 строк) будет таким:

type                     Number of Titles 
————————————————————     ———————— 
Algodata Infosystems     6 
Binnet  Hardley     7 
New Moon Books           5 
psychology               5 
mod_cook                 2 
trad_cook                3 
popular_comp             3 
UNDECIDED                1 
business                 4

Этот набор результатов показывает, сколько различных названий книг было опубликовано каждым из издателей, публиковавших книги (первые три строки набора результатов) и количество названий книг для каждой из категорий. Каждое из предложений GROUP BY выполняется только на своем подзапросе.

Операцию UNION можно применять и более чем для двух операторов SELECT. Создавая объединение, не забывайте следить, чтобы все колонки и типы данных в запросах были взаимно согласованными.

Функции T-SQL

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

Агрегатные функции

Как уже говорилось, агрегатные функции выполняют вычисления над набором значений и возвращают одно значение. Агрегатные функции могут быть заданы в списке выборки и чаще всего применяются в случаях, когда оператор содержит предложение GROUP BY. В некоторых из приведенных ранее примеров использовались агрегатные функции AVG и COUNT. Список имеющихся агрегатных функций перечислен в табл. 14.3.

Функция COUNT применяется специальным образом: она подсчитывает все строки таблицы. Для этого нужно после COUNT поместить символ-звездочку в скобках, вот так

SELECT   		COUNT(*) 
FROM     		publishers 
GO

Набор результатов будет таким:

----------------------
         				8

Функции AVG, COUNT, MAX, MIN и SUM могут применяться с необязательными ключевыми словами ALL или DISTINCT. Для каждой из этих функций ALL означает, что функция должна применяться ко всем значениям выражения, а DISTINCT означает, что повторяющиеся значения должны участвовать в расчете только по одному разу. По умолчанию применяется опция ALL.

Как правило, применяются понятные названия агрегатных функций. Ниже приведен пример, в котором используются сразу две агрегатные функции MAX и MIN, здесь вычисляется разница цен между самой дорогой и самой дешевой книгами:

SELECT   		MAX(price) - MIN(price) AS "Разница цен" 
FROM     		titles 
GO

Набор результатов будет таким:

Разница цен 
------------------------------
                   					19.96

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

SELECT   		stores.stor_name, SUM(sales.qty) AS "Заказано всего" 
FROM     		sales, stores 
WHERE    		sales.stor_id = stores.stor_id 
GROUP BY 	stor_name
GO

Набор результатов (6 строк) будет таким:

stor_name                                     Заказано всего 
--------------------------------------------------------
Barnum’s                                      125 
Bookbeat                                      80 
Doc-U-Mat: Quality Laundry and Books          130 
Eric the Read Books                           8 
Fricative Bookshop                            60 
News  Brews                                  90

Другие применения оператора SELECT

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

При помощи оператора SELECT вы можете, исполняя транзакцию или хранимую процедуру, присвоить значение локальной переменной, хотя для этого лучше применять оператор SET. Локальные переменные должны быть обозначены символом @ в начале имени. Например, можно присвоить значение 0 локальной переменной @count при помощи оператора SET, вот так:

SET  		@count = 0 
GO

Вы можете применять оператор SELECT для присваивания локальным переменным значений, получаемых как результат запроса. Например, чтобы присвоить локальной переменной @price значение максимальной величины из имеющихся в колонке price, примените такой оператор:

SELECT   		@price = MAX(price) 
FROM     		items 
GO

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

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

SELECT   		GETDATE()
GO

Функция GETDATE не имеет никаких параметров, но скобки нужны все равно.

Заключение

В этой лекции вы изучили оператор SELECT, отдельные предложения, которые могут быть включены в него, научились ими пользоваться, узнали про условия поиска и про функции. Вы изучили материал о логических операциях, о соединениях, об алиасах, о сопоставлении шаблонам, о результатах группировки и сортировки и о многом другом. Мы дали вам большой объем информации, но у оператора SELECT имеется еще много других возможностей. (О дополнительных возможностях см. лекцию 20; там же мы рассмотрим другие операторы манипулирования данными – INSERT, UPDATE и DELETE. А в лекции 15 мы расскажем о том, как можно вносить изменения в таблицы базы данных.)