Count – возвращает количество строк, удовлетворяющих условию поиска

COUNT (* | [DISTINCT] имя_столбца)

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

MAX – возвращает наибольшее значение в указанном столбце

MAX (имя_столбца)

Находит наибольшее значение в столбце из выборки. При вычислении учитываются нулевые значения столбца. Если число строк, подходящих под условия поиска, равно нулю, MAX возвращает нулевое значение.

MIN – возвращает наименьшее значение в указанном столбце

MIN (имя_столбца)

Находит наименьшее значение в столбце из выборки. При вычислении учитываются нулевые значения столбца. Если число строк, подходящих под условия поиска, равно нулю, MIN возвращает нулевое значение.

SUM – считает сумму значений в столбце

SUM ([DISTINCT] имя_столбца)

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

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

Предложение FROM указывает имена исходных таблиц для запроса. Эти таблицы могут быть таблицами или представлениями, непосредственно поименованными, или они могут быть получены в результате подзапроса или явно заданного соединения. "Имена корреляции" (этот термин принят для переменных области или псевдонимов), входящие в предложение FROM, обеспечивают использование альтернативных имен для таблиц. Имя корреляции записывается после имени таблицы, и ею определение действует только в течение исполнения оператора, Эти имена не обязательны для базовых таблиц и представлений, но необходимы при работе с таблицами, которые являются результатом подзапроса. Имена корреляции могут применяться для устранения неоднозначностей в используемых в предложении столбцах, в этом случае "имя корреляции" заменяет имя таблицы. Допускается, например, использование соединения таблицы с самой собой, которое будет обрабатываться как соединение двух идентичных таблиц. В лом случае необходимо использовать имя корреляции (псевдоним), чтобы различать две соединяемые копии. Имена корреляции выступают в роли префиксов к именам столбцов и, как обычно, отделяются от имени с помощью точки. Фактически, это переименование столбцов, используемых в предложении SELECT. Однако такие имена используются не для выходных столбцов, а для столбцов, на которые имеется ссылка в оставшейся части оператора, в частности, в предложении WHERE. Имена корреляции не относятся к обязательным элементам и используются только в некоторых случаях для более понятной записи оператора.

Соединения

Когда в предложении FROM поименованы несколько таблиц, то все они неявно считаются соединяемыми. По сути это означает, что можно получить все возможные комбинации строк (по одной из каждой таблицы), и именно с такой конкатенацией будут работать остальные операторы запроса. Эта конкатенированная таблица носит название декартово произведение (Cartesian product) или перекрестное соединение (cross join). Чаше всего пользователю нужно исключить большинство строк и выделить определенные данные, что обычно реализуется посредством установления отношений (или условий) при помощи предложения WHERE. Другой способ установить отношения - использование встроенных операций соединения, чтобы осуществить внутреннее соединение в предложении FROM. Результат этою соединения есть порожденная таблица, которая и должна обрабатываться остальными операторами запроса. Можно использовать оба способа совместно, однако, чаше всего сложности в "запутанной" логике исполнения оператора намного превышают полученные преимущества. В предыдущем стандарте встроенные операции соединения не поддерживались, поэтому те, кто уже постиг искусство соединения таблиц "вручную", возможно, обнаружат, что удобнее использовать новый способ, так как для любых групп стандартных операций встроенные соединения реализовать намного проще.

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

перекрестное соединение::=

таблица A CROSS JOIN таблица В

естественное соединение::=

таблица A [ NATURAL ] [тип соединения] JOIN таблица В

соединение объединения::=

таблица A UNION JOIN таблица В

объединение посредством предиката::=

таблица А [тип соединения] JOIN таблица В ON предикат

объединение посредством имен столбцов::=

таблица А [тип соединения] JOIN таблица В USING (имя столбца.,..)

тип соединения::=

INNER

| { { LEFT | RIGHT | FULL | [OUTER] }

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

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, AVG и т.д.). Если это предложение отсутствует и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT, должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют запросу. В противном случае все столбцы списка SELECT, не вошедшие в агрегатную функцию, должны быть "сгруппированы" с помощью предложения GROUP BY. Все выходные строки запроса, которые сгруппированы по равенству значений столбцов, образуют единую группу (для GROUP BY все значения NULL трактуются, как равные). Агрегатная функция будет применяться к каждой из таких групп. Рассмотрим простой пример:

SELECT snum, AVG (amount), MAX (amount) FROM Salespeople GROUP BY snum; В этом запросе предполагается, что таблица Salespeople имеет, как минимум, два столбца (snum и amount) и, скорее всего, содержит записи с платежными транзакциями. Эта таблица имеет одну строку на каждую транзакцию, содержащую имя продавца и сумму (если это базовая таблица, а не представление, то она должна иметь первичный ключ). Все транзакции с одинаковыми значениями snum (имя продавца) образуют группу, и на выходе SELECT вычисляются максимальные и средние значения для каждой группы. Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти цифры для каждой конкретной даты. Для этого нужно было бы задать дату в качестве группирующего столбца, и тогда агрегатные функции будут вычисляться для каждой комбинации значений (продавец-дата). Такую группировку можно реализовать с помощью соединения, при записи которого необходимо использовать имена таблиц или связанные префиксы вместе с именами столбцов (чтобы избежать неоднозначности).
Если используется предложение COLLATE FROM, то полученная последовательность сортировки будет определена для выходного столбца, полученного с помощью группировки. После COLLATE FROM должен следовать атрибут приведения, заданный в явном виде. Естественно, что предложение COLLATE применимо только к сгруппированному столбцу типа CHARACTER.

 

23. Предложение HAVING, его назначение и использование.

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

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

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

Пример: Получить количество ПК и среднюю цену для каждой модели, средняя цена которой менее $800

1. SELECT model,COUNT(model) AS Qty_model,AVG(price) AS Avg_price

2. FROM PC

3. GROUP BY model

4. HAVING AVG(price) < 800;

Заметим, что в предложении HAVING нельзя использовать псевдоним (Avg_price), используемый для именования значений агрегатной функции в предложении SELECT. Дело в том, что предложение SELECT, формирующее выходной набор запроса, выполняется предпоследним перед предложением ORDER BY. Ниже приведен порядок обработки предложений в операторе SELECT:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

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

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

Фраза HAVING (рис.2.3) играет такую же роль для групп, что и фраза WHERE для строк: она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы.

Например, выдать коды продуктов, поставляемых более чем двумя поставщиками:

SELECT FROM Поставки GROUP BY ПС HAVING COUNT(*) > 2;

 

 

24. Запросы к нескольким таблицам (соединения таблиц).

Затрагивая вопросы проектирования баз данных [2], мы выяснили, что базы данных - это множество взаимосвязанных сущностей или отношений (таблиц) в терминологии реляционных СУБД. При проектировании стремятся создавать таблицы, в каждой из которых содержалась бы информация об одном и только об одном типе сущностей. Это облегчает модификацию базы данных и поддержание ее целостности. Но такой подход тяжело усваивается начинающими проектантами, которые пытаются привязать проект к будущим приложениям и так организовать таблицы, чтобы в каждой из них хранилось все необходимое для реализации возможных запросов. Типичен вопрос: как же получить сведения о том, где купить продукты для приготовления того или иного блюда и определить его калорийность и стоимость, если нужные данные "рассыпаны" по семи различным таблицам? Не лучше ли иметь одну большую таблицу, содержащую все сведения базы данных ПАНСИОН?

Даже при отсутствии средств одновременного доступа ко многим таблицам нежелателен проект, в котором информация о многих типах сущностей перемешана в одной таблице. SQL же обладает великолепным механизмом для одновременной или последовательной обработки данных из нескольких взаимосвязанных таблиц. В нем реализованы возможности "соединять" или "объединять" несколько таблиц и так называемые "вложенные подзапросы". Например, чтобы получить перечень поставщиков продуктов, необходимых для приготовления Сырников, возможен запрос

SELECT Продукт, Цена, Название, СтатусFROM Продукты, Состав, Блюда, Поставки, ПоставщикиWHERE Продукты.ПР = Состав.ПРAND Состав.БЛ = Блюда.БЛAND Поставки.ПР = Состав.ПРAND Поставки.ПС = Поставщики.ПСAND Блюдо = 'Сырники'AND Цена IS NOT NULL;

Продукт	Цена	Название	Статус
Яйца	1.8	ПОРТОС	кооператив
Яйца	2.	КОРЮШКА	кооператив
Сметана	3.6	ПОРТОС	кооператив
Сметана	2.2	ОГУРЕЧИК	ферма
Творог	1.	ОГУРЕЧИК	ферма
Мука	0.5	УРОЖАЙ	коопторг
Сахар	0.94	ТУЛЬСКИЙ	универсам
Сахар	1.	УРОЖАЙ	коопторг

Он получен следующим образом: СУБД последовательно формирует строки декартова произведения таблиц, перечисленных во фразе FROM, проверяет, удовлетворяют ли данные сформированной строки условиям фразы WHERE, и если удовлетворяют, то включает в ответ на запрос те ее поля, которые перечислены во фразе SELECT.

Следует подчеркнуть, что в SELECT и WHERE (во избежание двусмысленности) ссылки на все (*) или отдельные столбцы могут (а иногда и должны) уточняться именем соответствующей таблицы, например, Поставки.ПС, Поставщики.ПС, Меню.*, Состав.БЛ, Блюда.* и т.п.

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

Кроме механизма соединений в SQL есть механизм вложенных подзапросов, позволяющий объединить несколько простых запросов в едином предложении SELECT. Иными словами, вложенный подзапрос - это уже знакомый нам подзапрос (с небольшими огра-ничениями), который вложен в WHERE фразу другого вложенного подзапроса или WHERE фразу основного запроса.

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

SELECT Продукт, Цена, Название, СтатусFROM Продукты, Состав, Блюда, Поставки, ПоставщикиWHERE Продукты.ПР = Состав.ПРAND Состав.БЛ = Блюда.БЛAND Поставки.ПР = Состав.ПРAND Поставки.ПС = Поставщики.ПСAND Блюдо = 'Сырники'AND Цена = (SELECT MIN(Цена) FROM Поставки X WHERE X.ПР = Поставки.ПР);

Результат запроса имеет вид

Продукт	Цена	Название	Статус
Яйца	1.8	ПОРТОС	кооператив
Сахар	0.94	ТУЛЬСКИЙ	универсам
Мука	0.5	УРОЖАЙ	коопторг
Сметана	2.2	ОГУРЕЧИК	ферма
Творог	1.	ОГУРЕЧИК	ферма

Здесь с помощью подзапроса, размещенного в трех последних строках запроса, описывается процесс определения минимальной цены каждого продукта для Сырников и поиск поставщика, предлагающего этот продукт за такую цену. Механизм реализации подзапросов будет подробно описан в п.3.3. Там же будет рассмотрено, как и для чего вводится псевдоним X для имени таблицы Поставки.

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

 

В этой главе рассматривается мультитабличная операция соединения (join). Подзапросы, которые обращаются к нескольким таблицам, будут рассмотрены в главе 5 “Подзапросы: Использование запросов внутри других запросов”. Часто cоединения могут выступать в качестве подзапросов.

 

В этой главе обсуждаются следующие темы:

 

· Общий обзор операций соединения;

· Как соединять таблицы в запросе;

· Как SQL Сервер выполняет соединение;

· Как влияют неопределенные значения на соединение;

· Как указывать столбцы для соединения.

 

ЧТО ТАКОЕ СОЕДИНЕНИЯ?

 

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

Операция соединения имеет свой собственный жаргон. Слово “join” может использоваться и как глагол и как существительное, кроме того оно может означать либо операцию, либо запрос, содержащий эту операцию, либо результаты этого запроса.

Имеется также несколько разновидностей соединений: соединения с равенством (эквисоединения), естественные (natural) соединения, внешние соединения и т.д.

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

 

select au_fname, au_lname, pub_name

From authors, publishers

where authors.city = publishers.city

 

au_fname au_lname pub_name

------------- ------------ -----------------------------

Cheryl Carson Algodata Infosystems

Abraham Bennet Algodata Infosystems

(Выбрано 2 строки)

 

Поскольку требуемая информация находится в двух таблицах publishers и authors, то для ее выбора необходимо соединение этих таблиц.