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

Поможем в ✍️ написании учебной работы

Имя

Поможем с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой

Нажимая кнопку "Продолжить", я принимаю политику конфиденциальности

Выборка без использования фразы 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

Дата: 2019-04-23, просмотров: 208.

⇐ Предыдущая 1 2 3 4 567 8 9 10 Следующая ⇒