кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так:
COUNT- число значений в столбце,
SUM- сумма значений в столбце,
AVG- среднее значение в столбце,
MAX- самое большое значение в столбце,
MIN- самое малое значение в столбце.
из SQL-функций можно составлять любые выражения.
Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Специальная же функция COUNT(*) служит для подсчета всех без исключения строк в таблице (включая дубликаты).
Если не используется фраза GROUP BY, то в перечень элементов_SELECT можно включать лишь SQL-функции или выражения, содержащие такие функции. Другими словами, нельзя иметь в списке столбцы, не являющихся аргументами SQL-функций.
Например, для получения суммы цен, средней цены, количества поставляемых продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ (ПС=5), а также для получения количества продуктов, которые могут поставляться этим коопторгом, можно дать запрос
SELECT SUM(Цена),AVG(Цена),COUNT(Цена),
COUNT(DISTINCT Цена),COUNT(*)
FROM Поставки
WHERE ПС = 5;
39. Использование группировки
требуется вычислить общую массу каждого из продуктов, поставляемых в настоящее время поставщиками.
SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР;
Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).
Отметим, что фраза GROUP BY не предполагает ORDER BY.
SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР
ORDER BY ПР;
Фраза HAVINGЭта фраза включается в предложение лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы.
Например, выдать коды продуктов, поставляемых более чем двумя поставщиками:
| SELECT FROM Поставки GROUP BY ПС HAVING COUNT(*) > 2; | |
40. Запросы с исп. нескольких таблиц
SELECT Вид_блюд.*, Трапезы.*
FROM Вид_блюд, Трапезы;
эквисоединение таблиц
SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;
естественное соединение тех же таблиц:
SELECT Т, В, БЛ, Трапеза, Вид, Блюдо, Основа, Выход, Труд
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;
Например, для получения перечня блюд, предлагаемых в меню на завтрак, можно сформировать запрос на основе композиции:
SELECT Вид, Блюдо, Основа, Выход, 'Номер -', БЛ
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ
AND Трапеза = ’Завтрак’;
Соединение таблицы со своей копией
SELECT STUDEBT.LNAME, STUDENT.FNAME, STUDENT.SNUM
FROM STUDENT, STUDENT AS S1
WHERE SRUDENT.LNAME=S1.LNAME
AND STUDENT.FNAME=S1.FNAME
AND STUDENT.DOB=S1.DOB
AND STUDENT.SNUM=S1.SNUM
41.Корреляционно-вложенные запросы
Выдать название и статус поставщиков продукта с номером 11.
SELECT Название, Статус
FROM Поставщики
WHERE 11 IN
(SELECT ПР
FROM Поставки
WHERE ПС = Поставщики.ПС);
Такой подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения Поставщики.ПС а оно изменяется по мере того, как система проверяет различные строки таблицы Поставщики. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом:
1. Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика с номером 1. Тогда значение Поставщики.ПС будет в данный момент имеет значение, равное 1, и система обрабатывает внутренний запрос
(SELECT ПР
FROM Поставки
WHERE ПС = 1);
получая в результате множество (9, 11, 12, 15). Теперь система может завершить обработку для поставщика с номером 1. Выборка значений Название и Статус для ПС=1 (СЫТНЫЙ и рынок) будет проведена тогда и только тогда, когда ПР=11 будет принадлежать этому множеству, что, очевидно, справедливо.
2. Далее система будет повторять обработку такого рода для следующего поставщика и т.д. до тех пор, пока не будут рассмотрены все строки таблицы Поставщики.
42. Вложенные подзапросы
Вложенный подзапрос - это подзапрос, заключенный в круглые скобки и вложенный в WHERE (HAVING) фразу предложения SELECT или других предложений, использующих WHERE фразу. Вложенный подзапрос может содержать в своей WHERE (HAVING) фразе другой вложенный подзапрос и т.д.
система выполняет прежде всего вложенный подзапрос.
SELECT Название, Статус
FROM Поставщики
WHERE ПС IN
(SELECT ПС
FROM Поставки
WHERE ПР = 11);
Получить имена поставщиков, имеющих статус, больший 3 и одновременно поставляющих хотя бы одну деталь номер 2 (пересечение двух подзапросов - ключевое слово INTERSECT):
SELECT P.PNAME
FROM P
WHERE P.STATUS > 3
INTERSECT
SELECT P.PNAME
FROM P, PD
WHERE P.PNUM = PD.PNUM AND
PD.DNUM = 2;
Получить имена поставщиков, имеющих статус, больший 3, за исключением тех, кто поставляет хотя бы одну деталь номер 2 (разность двух подзапросов - ключевое слово EXCEPT):
SELECT P.PNAME
FROM P
WHERE P.STATUS > 3
EXCEPT
SELECT P.PNAME
FROM P, PD
WHERE P.PNUM = PD.PNUM AND
PD.DNUM = 2;
EXIST
EXISTS (SELECT * FROM...).
Такое выражение считается истинным только тогда, когда результат вычисления "SELECT * FROM..." является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос всегда будет коррелированным множеством.)
| SELECT Название FROM Поставщики WHERE EXISTS (SELECT * FROM Поставки WHERE ПС = Поставщики.ПС AND ПР = 11); |
Хотя этот первый пример только показывает иной способ формулировки запроса для задачи, решаемой и другими путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возможностей SQL. Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Однако обратное высказывание несправедливо.
Объединение
Для SQL это означает, что две таблицы можно объединять тогда и только тогда, когда:
a. они имеют одинаковое число столбцов, например, m;
b. для всех i (i = 1, 2,..., m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных.
Например, выдать названия продуктов, в которых нет жиров, либо входящих в состав блюда с кодом БЛ = 1:
| SELECT Продукт FROM Продукты WHERE Жиры = 0 UNION SELECT Продукт FROM Состав WHERE БЛ = 1 |
Из этого простого примера видно, что избыточные дубликаты всегда исключаются из результата UNION.
к оператору SELECT можно добавить условия WHERE, результат можно упорядочить ORDER BY Продукт
45. Предложение модификации данных DELETE.
Предложение DELETE имеет формат
DELETE
FROM SKLAD
[WHERE SKLAD_ID=4;];
и позволяет удалить содержимое всех строк указанной таблицы (при отсутствии WHERE фразы) или тех ее строк, которые выделяются WHERE фразой.
