Запросы с использованием единственной таблицы.

Выборка без использования фразы WHERE

Простая выборка

Запрос выдать название, статус и адрес поставщиков

 

SELECT Название, Статус, Адрес

FROM Поставщики;

дает результат, приведенный на рис. 2.2,а.

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

 

SELECT ПС, Название, Статус, Город, Адрес, Телефон

FROM Поставщики;

или использовать его более короткую нотацию:

 

SELECT *

FROM Поставщики;

Здесь «звездочка» (*) служит кратким обозначением всех имен полей в таблице, указанной во фразе FROM. При этом порядок вывода полей соответствует порядку, в котором эти поля определялись при создании таблицы.

Еще один пример. Выдать основу всех блюд:

 

SELECT Основа

FROM Блюда;

дает результат, показанный на рис. 2.2,б.

 

а)			б)	в)

Название

Статус

Адрес

Основа Основа Овощи Кофе Мясо Крупа СЫТНЫЙ Рынок Сытнинская, 3 Овощи Молоко ПОРТОС Кооператив Садовая, 27 Рыба Мясо ШУШАРЫ Совхоз Новая, 17 Рыба Овощи ТУЛЬСКИЙ Универсам Тульская, 3 Мясо Рыба УРОЖАЙ Коопторг Песчаная, 19 Молоко Фрукты ЛЕТО Агрофирма Пулковское ш.,8 Молоко Яйца ОГУРЕЧИК Ферма Укмерге, 15 …

 

КОРЮШКА Кооператив Нарвское ш., 64 Кофе

Рисунок 2.2

Исключение дубликатов

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

 

SELECT DISTINCT Основа

FROM Блюда;

Результат приведен на рис. 2.2,в.

Выборка вычисляемых значений

Из синтаксиса фразы SELECT видно, что в ней может содержаться не только перечень столбцов таблицы или символ *, но и выражения.

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

 

SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)

FROM Продукты;

результат которого приведен на рис. 2.3,а.

а)		Б)		в)
Продукт			Продукт		Продукт
Говядина	1928.1		Говядина	Калорий =	1928.1	Зелень	118.9
Судак	1523.		Судак	Калорий =	1523.	Помидоры	196.8
Масло	8287.5		Масло	Калорий =	8287.5	Морковь	349.6
Майонез	6464.7		Майонез	Калорий =	6464.7	Лук	459.2
Яйца	1618.9		Яйца	Калорий =	1618.9	Яблоки	479.7
Сметана	3011.4		Сметана	Калорий =	3011.4	Молоко	605.1
Молоко	605.1		Молоко	Калорий =	605.1	Кофе	892.4
Творог	1575.		Творог	Калорий =	1575.	Судак	1523.
Морковь	349.6		Морковь	Калорий =	349.6	Творог	1575.
Лук	459.2		Лук	Калорий =	459.2	Яйца	1618.9
Помидоры	196.8		Помидоры	Калорий =	196.8	Говядина	1928.1
Зелень	118.9		Зелень	Калорий =	118.9	Сметана	3011.4
Рис	3512.1		Рис	Калорий =	3512.1	Рис	3512.1
Мука	3556.7		Мука	Калорий =	3556.7	Мука	3556.7
Яблоки	479.7		Яблоки	Калорий =	479.7	Сахар	4091.8
Сахар	4091.8		Сахар	Калорий =	4091.8	Майонез	6464.7
Кофе	892.4		Кофе	Калорий =	892.4	Масло	8287.5

Рисунок 2.3

Фраза SELECT может включать не только выражения, но и отдельные числовые или текстовые константы. Следует отметить, что текстовые константы должны заключаться в апострофы ('). На рис. 2.3,б приведен результат запроса:

 

SELECT   Продукт, 'Калорий =', ((Белки+Углев)*4.1+Жиры *9.3)

FROM     Продукты;

 

А что произойдет, если какой-либо член выражения не определен, т.е. имеет значение NULL и каким образом появилось такое значение?

Если при загрузке строк таблицы в какой-либо из вводимых строк отсутствует значение для какого-либо столбца, то СУБД введет в такое поле NULL-значение. NULL-значение «придумано» для того, чтобы представить единым образом «неизвестные значения» для любых типов данных. Действительно, так как при вводе данных в столбец или их изменении СУБД запрещает ввод значений не соответствующих описанию данных этого столбца, то, например, нельзя использовать пробел для отсутствующего значения числа. Нельзя для этих целей использовать и ноль: нет месяца или дня недели равного нулю, да и для чисел ноль не может рассматриваться как неизвестное значение в одном месте и как известное – в другом. При выводе же NULL-значения на экран или печатающее устройство его код воспроизводится каким-либо специально заданным символом или набором символов: например, пробелом (если его нельзя перепутать с текстовым значением пробела) или сочетанием –0-.

С помощью специальной команды можно установить в СУБД один из режимов представления NULL-значений при выполнении числовых расчетов: запрет или разрешение замены NULL-значения нулем. В первом случае любое арифметическое выражение, содержащее неопределенный операнд, будет также иметь неопределенное значение. Во втором случае результат вычислений будет иметь численное значение (если это значение попадает в диапазон представления соответствующего типа данных).

Например, при выполнении запроса

 

SELECT ПР, Цена, К_во, (Цена * К_во)

FROM Поставки;

и разных «настройках» СУБД могут быть получены разные результаты:

ПР	Цена	К_во	(Цена*К_во)	ПР	Цена	К_во	(Цена*К_во)
9	-0-	-0-	-0-	9	-0-	-0-	0.
11	1.5	50	75.	11	1.5	50	75.
12	3.	10	30.	12	3.	10	30.
15	2.	170	340.	15	2.	170	340.

 

Использование BETWEEN

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

Например, выдать перечень продуктов, в которых значение содержания белка находится в диапазоне от 10 до 50:

	Результат:
SELECT Продукт, Белки FROM Продукты WHERE Белки BETWEEN 10 AND 50;
	Продукт	Белки
	Майонез	31.
	Сметана	26.
	Молоко	28.
	Морковь	13.
	Лук	17.

Можно задать и NOT BETWEEN (не принадлежит диапазону между), например:

	Результат:
SELECT  Продукт, Белки, Жиры FROM    Продукты     WHERE   Белки NOT BETWEEN 10 AND 50    AND     Жиры 100;
	Продукт	Белки	Жиры
	Говядина	189.	124.
	Масло	60.	825.
Яйца	127.	115.

BETWEEN особенно удобен при работе с данными, задаваемыми интервалами, начало и конец которых расположен в разных столбцах.

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

 

Миноклад	Начало	Конец
2250	01-01-1993	31-03-1993
4275	01-04-1993	30-06-1993
7740	01-07-1993	30-11-1993
14620	01-12-1993	30-06-1994
20500	01-07-1994	09-09-9999

Рисунок 2.4

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

 

SELECT Начало, Миноклад

FROM Миноклады

WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'

и получить результат:

Начало	Миноклад
01-12-1993	14620
01-07-1994	20500

Отметим, что при формировании запросов значения дат следует заключать в апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычитать из 31 значение 8, а затем 1994.

Для выявления всех значений минимальных окладов, которые существовали в 1993/94 учебном году, можно сформировать запрос

 

SELECT *

FROM Миноклады

WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'

OR Конец BETWEEN '1-9-1993' AND '31-8-1994'

Миноклад	Начало	Конец
7740	01/07/1993	30/11/1993
14620	01/12/1993	30/06/1994
20500	01/07/1994	09/09/9999

Наконец, для получения минимального оклада на 15-5-1994:

	Результат:
SELECT  Миноклад FROM    Миноклады WHERE   '15-05-1994' BETWEEN Начало AND Конец	Миноклад
	14620

 

 

Использование IN

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

 

SELECT *

FROM Блюда

WHERE Основа IN (Яйца Крупа Овощи);

Результат:

БЛ	Блюдо	В	Основа	Выход	Труд
1	Салат летний	З	Овощи	200.	3
3	Салат витаминный	З	Овощи	200.	4
16	Драчена	Г	Яйца	180.	4
17	Морковь с рисом	Г	Овощи	260.	3
19	Омлет с луком	Г	Яйца	200.	5
20	Каша рисовая	Г	Крупа	210.	4
21	Пудинг рисовый	Г	Крупа	160.	6
23	Помидоры с луком	Г	Овощи	260.	4

Рассмотренная форма IN является в действительности просто краткой записью последовательности отдельных сравнений, соединенных операторами OR. Предыдущее предложение эквивалентно такому:

 

SELECT *

FROM Блюда

WHERE Основа=Яйца OR Основа=Крупа OR Основа=Овощи;

 

Использование LIKE

Выдать перечень салатов

	Результат:
SELECT  Блюдо FROM    Блюда WHERE   Блюдо LIKE 'Салат%';	Блюдо
	Салат летний
	Салат мясной
	Салат витаминный
	Салат рыбный

Обычная форма «имя_столбца LIKE текстовая_константа» для столбца текстового типа позволяет отыскать все значения указанного столбца, соответствующие образцу, заданному «текстовой_константой». Символы этой константы интерпретируются следующим образом:

· символ _ (подчеркивание) – заменяет любой одиночный символ,

· символ % (процент) – заменяет любую последовательность из N символов (где N может быть нулем),

· все другие символы означают просто сами себя.

Следовательно, в приведенном примере SELECT будет осуществлять выборку записей из таблицы Блюда, для которых значение в столбце Блюдо начинается сочетанием 'Салат' и содержит любую последовательность из нуля или более символов, следующих за сочетанием 'Салат'. Если бы среди блюд были «Луковый салат», «Фруктовый салат» и т.п., то они не были бы найдены. Для их отыскания надо изменить фразу WHERE:

 

WHERE Блюдо LIKE '%салат%'

или при отсутствии различий между малыми и большими буквами (такую настройку допускают некоторые СУБД):

 

WHERE Блюдо LIKE '%Салат%' 

Это позволит отыскать все салаты.

Вовлечение неопределенного значения (NULL-значения)

Если при загрузке данных не введено значение в какое-либо поле таблицы, то СУБД поместит в него NULL-значение. Аналогичное значение можно ввести в поле таблицы, выполняя операцию изменения данных. Так, при отсутствии сведений о наличии у поставщиков судака и моркови в столбцы Цена и К_во соответствующих строк таблицы Поставки вводится NULL и там будет храниться код NULL-значения, а не 0, 0. Или пробел. (Отметим, что в распечатке таблицы Поставки в этих местах расположен пробел, установленный в СУБД для представления NULL-значения при выводе на печать).

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

Результат:		ПР
SELECT  DISTINCT ПР FROM    Наличие      WHERE   К_во IS NULL;		2 9

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

 

SELECT DISTINCT ПР

FROM Наличие

WHERE К_во IS NOT NULL;

Использование условий

 

столбец IS NULL и столбец IS NOT NULL

вместо, например,

 

столбец = NULL и столбец < NULL

связано с тем, что ничто – и даже само NULL-значение – не считается равным другому NULL-значению. (Несмотря на это, два неопределенных значения рассматриваются, однако, как дубликаты друг друга при исключении дубликатов, и предложение SELECT DISTINCT даст в результате не более одного NULL-значения.)

 

Выборка с упорядочением

Простейший вариант этой фразы – упорядочение строк результата по значению одного из столбцов с указанием порядка сортировки или без такого указания. (По умолчанию строки будут сортироваться в порядке возрастания значений в указанном столбце.)

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

SELECT  Продукт, Белки, Жиры, Углев FROM    Продукты ORDER   BY Белки DESC;	Продукт	Белки	Жиры	Углев
	Судак	190.	80.	0.
	Говядина	189.	124.	0.
	Творог	167.	90.	13.
	Яйца	127.	115.	7.
	Кофе	127.	36.	9.
	Мука	106.	13.	732.

 

При включении в список ORDER BY нескольких столбцов СУБД сортирует строки результата по значениям первого столбца списка пока не появится несколько строк с одинаковыми значениями данных в этом столбце. Такие строки сортируются по значениям следующего столбца из списка ORDER BY и т.д.

Например, выдать содержимое таблицы Блюда, отсортировав ее строки по видам блюд и основе:

 

	Результат:
SELECT  * FROM    Блюда ORDER   BY В Основа;	БЛ	Блюдо	В	Основа	Выход	Труд
	21	Пудинг рисовый	Г	Крупа	160.	6
	20	Каша рисовая	Г	Крупа	210.	4
	18	Сырники	Г	Молоко	220.	4
	...
	16	Драчена	Г	Яйца	180.	4
	28	Крем творожный	Д	Молоко	160.	4
	...
	26	Яблоки печеные	Д	Фрукты	160.	3
	7	Сметана	З	Молоко	140.	1
	8	Творог	З	Молоко	140.	2
	2	Салат мясной	З	Мясо	200.	4
	6	Мясо с гарниром	З	Мясо	250.	3
	1	Салат летний	З	Овощи	200.	3
	...

 

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

Например, запрос

 

SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)

FROM Продукты

ORDER BY 2;

позволит получить список продуктов, показанный на рис.2.3,в – переупорядоченный по возрастанию значений калорийности список рис.2.3,а.

 

Агрегирование данных

SQL-функции

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

COUNT

7. число значений в столбце,

SUM

8. сумма значений в столбце,

AVG

9. среднее значение в столбце,

MAX

10. самое большое значение в столбце,

MIN

11. самое малое значение в столбце.

Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения.

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

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

 

Функции без использования фразы GROUP BY

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

Например, выдать данные о массе лука (ПР=10), проданного поставщиками, и указать количество этих поставщиков:

 

	Результат:
SELECT  SUM(К_во),COUNT(К_во)     FROM    Поставки     WHERE   ПР = 10;
	SUM(К_во)	COUNT(К_во)
	220	2

Если бы для вывода в результат еще и номера продукта был сформирован запрос

 

SELECT ПР,SUM(К_во),COUNT(К_во)

FROM Поставки

WHERE ПР = 10;

то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для «свободного» столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где ПР=10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой.

Правда, никто не запрещает дать запрос

 

SELECT 'Кол-во лука =',SUM(К_во),COUNT(К_во)

FROM Поставки

WHERE ПР = 10;

Результат:
'Кол-во лука ='	SUM(К_во)	COUNT(К_во)
Кол-во лука =	220	2

Отметим также, что в столбце-аргументе перед применением любой функции, кроме COUNT(*), исключаются все неопределенные значения. Если оказывается, что аргумент – пустое множество, функция COUNT принимает значение 0, а остальные – NULL.

Например, для получения суммы цен, средней цены, количества поставляемых продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ (ПС=5), а также для получения количества продуктов, которые могут поставляться этим коопторгом, можно дать запрос

 

SELECT SUM(Цена),AVG(Цена),COUNT(Цена),

   COUNT(DISTINCT Цена),COUNT(*)

FROM Поставки

WHERE ПС = 5;

и получить

 

SUM(Цена)	AVG(Цена)	COUNT(Цена)	COUNT(DISTINCT Цена)	COUNT (*)
6.2	1.24	5	4	7

В другом примере, где надо узнать «Сколько поставлено моркови и сколько поставщиков ее поставляют?»:

 

SELECT SUM(К_во),COUNT(К_во)

FROM Поставки

WHER ПР = 2;

будет получен ответ:

SUM(К_во)	COUNT (К_во)
-0-	0

Наконец, попробуем получить сумму массы поставленного лука с его средней ценой («Сапоги с яичницей»):

	Результат:
SELECT  (SUM(К_во) +AVG(Цена))  FROM    Поставки WHERE   ПР = 10;
	SUM(К_во)+AVG(Цена)
	220.6

 

 

Фраза GROUP BY

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

 

SELECT ПР, SUM(К_во)

FROM Поставки

GROUP BY ПР;

Результат показан на рис. 2.5,а.

 

а)							б)						в)			г)
ПР			ПС				ПР				Цена	К_во	ПР			ПР
9		0	1				9				-0-	-0-	1		370	9		0
11		150	3				9				-0-	-0-	2		0	11		150
12		30	5				9				-0-	-0-	3		250	12		30
15		370	1				11				1.50	50	4		100	15		70
1		370	5				11				-0-	-0-	5		170	1		370
3		250	6				11				-0-	-0-	6		220	3		250
5		170	8				11				1.00	100	7		200	5		70
6		220	1				12				3.00	10	8		150	6		140
8		150	3				12				2.50	20	9		0	8		150
7		200	6				12				-0-	-0-	10		220	7		200
2		0	1				15				2.00	170	11		150	2		0
4		100	3				15				1.50	200	12		30	4		100
13		190	2				1				3.60	300	13		190	13		190
14		70	7				1				4.20	70	14		70	14		70
16		250	2				3				-0-	-0-	15		370	16		250
17		50	7				3				4.00	250	16		250	17		50
10		220	...										17		50	10		220

Рисунок 2.5

 

Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы Поставки группируются так, что в одной группе содержатся все строки для продукта с ПР = 1, в другой – для продукта с ПР = 2 и т.д. (см. рис. 2.5,б). Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).

Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по ПР результата рассматриваемого примера (рис. 2.5,в) следует дать запрос

 

SELECT ПР, SUM(К_во)

FROM Поставки

GROUP BY ПР

ORDER BY ПР;

Наконец, отметим, что строки таблицы можно группировать по любой комбинации ее столбцов. Так, по запросу

 

SELECT Т, БЛ, COUNT(БЛ)

FROM Заказ

GROUP BY Т, БЛ;

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

Т	БЛ	COUNT(БЛ)
1	3	18
1	6	14
1	19	17
1	21	15
…

 

 

Использование фразы HAVING

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

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

SELECT       FROM    Поставки GROUP   BY ПС HAVING  COUNT(*) 2;	Результат:	ПР
		9
		11
		12