Агрегирование и группирование записей.
При формировании запроса можно использовать различные арифметические и логические операции для построения сложных запросов.
!! В SQL приоритет операций сравнения выше приоритета логических операций.
Оператор SELECT позволяет возвращать не только множество значений поля, но и некоторые совокупные характеристики. Эти характеристики подсчитываются по всем или нескольким указанным записям.
Например.
SELECT count(*) FROM Tovar
- подсчет общего количества всех записей в таблице Tovar.
SELECT count(*) FROM Tovar WHERE T_Cena>100
- подсчет количества товаров, цена которых больше 100.
SELECT min(extract(year FROM S_birthday)),
max(extract(year FROM S_birthday)),
avg(extract(year FROM S_birthday))
FROM Sotrudniki
- подсчет минимального, максимального и среднего года рождения
SELECT min(Kol_vo*Cena), max(Kol_vo*Cena)
FROM Tovar
- совокупные характеристики.
После слова SELECT могут записываться или поля (в том числе и вычисляемые), или совокупные характеристики. Для одновременного использования полей и совокупных характеристик нужно использовать группировку записей.
Группировка записей задается ключевым словом
GROUP BY <список имен полей>
где <список имен полей> - перечисляются все поля, входящие в список SELECT.
В случае группировки записей совокупные характеристики проводят вычисления не по всем записям таблицы, а по тем, которые соответствуют одинаковым значениям указанных полей.
Например,
SELECT Prim, count(*) Всего_товаров
FROM Tovar GROUP BY Prim
- будут выведены два столбца с примечанием и количеством записей для каждого значения примечания.
Примеры.
Написать запрос, который получает список студентов, обучающихся на 2-м курсе и выше.
Получить сведения о студентах, имеющих оценки только 4 и 5.
Получить сведения о студентах, не имеющих ни одной оценки 4 или 5.
Объединение таблиц.
В запросе можно объединять данные из одной или нескольких таблиц. Такое объединение таблиц называется соединением (связыванием) таблиц. Различают внутреннее и внешнее соединения.
Внутреннее соединение – это соединение, при котором результирующий набор получается путем перечисления нужных полей из разных таблиц после слова SELECT. При этом таблицы должны находиться в отношении «один-к-одному».
Например,
SELECT R.fam, R.birthday,A.Foto
FROM Rabotniki R, Advanced A
где таблицы Rabotniki и Advanced содержат основные и дополнительные сведения о работниках предприятия. Связь «один-к-одному». Таблице Rabotniki дан псевдоним R, а таблице Advanced дан псевдоним A.
В предыдущем примере перед именем поля записано имя таблицы. Имена поля и таблицы отделяются точкой. Имя таблицы указывать необходимо, если имена полей повторяются для различных таблиц.
Если применить внутреннее соединение к таблицам, связанным по принципу «один-ко-многим», то результирующий набор может содержать избыточную информацию. Для устранения избыточности используют критерии отбора.
Пример 1. Дана БД Sotrudniki, состоящая из двух таблиц:
Запрос внутреннего соединения таблиц, связанных отношением «один-ко-многим» имеет вид
SELECT S_Fio,S_Birthday,D_Nazv FROM Sotrudniki, Doljn
Число записей в результирующем наборе данных равно произведению числа записей в таблице Sotrudniki на число записей в таблице Doljn. Результирующий набор данных имеет вид и содержит избыточную информацию:
Для ограничения числа записей в результирующем наборе данных применяют критерии отбора. Запрос в этом случае будет иметь вид
SELECT S_Fio,S_Birthday,D_Nazv FROM Sotrudniki, Doljn
WHERE S_Doljn=D_Code
Число записей в результирующем наборе данных будет равно числу записей в таблице Sotrudniki.
Пример 2. Требуется для БД Sotrudniki сформировать запрос, который позволит получить список сотрудников, имеющих должность «программист». Получим
SELECT S_fio, S_birthday FROM sotrudniki, doljn
WHERE S_doljn=D_code and D_nazv='программист'
В SQL-запросах допускается самообъединение таблицы. В этом случае одной таблице даются два псевдонима.
Например, для нахождения всех ровесников в таблице Sotrudniki можно написать запрос:
SELECT s1.s_fio, s2.s_fio, s1.s_birthday
FROM Sotrudniki s1, Sotrudniki s2
WHERE (EXTRACT(YEAR
FROM s1.s_birthday)=EXTRACT(YEAR
FROM s2.s_birthday))
AND (s1.s_fio!=s2.s_fio) AND (s1.s_fio<s2.s_fio)
Последнее условие упорядочивает фамилии и исключает дублирование результатов.
При внутреннем соединении все таблицы, поля которых указаны в SQL-запросе, являются равноправными. То есть каждой записи в первой таблице находилась соответствующая ей запись во второй таблице.
При внешнем объединении (outer join) в результирующий набор включаются записи независимо от того, есть ли соответствующее поле во второй таблице. Существует три типа внешнего объединения.
LEFT OUTER JOIN … ON – левое, включает в результат все записи первой таблицы, даже те, для которых не имеется соответствия во второй.
RIGHT OUTER JOIN … ON – правое, включает в результат все записи второй таблицы, даже те, для которых не имеется соответствия в первой.
FULL OUTER JOIN … ON – полное, включает в результат объединение записей обеих таблиц, независимо от их соответствия.
При внешнем соединении можно говорить о том, какая из таблиц является главной. В первом случае – левая, во втором – правая.
Например. Пусть в таблице Sotrudniki БД Sotrudniki есть фамилии, имеющие должность, не указанную в таблице Doljn, и есть должности в таблице Doljn, для которых нет фамилии в таблице Sotrudniki. Тогда
1) SELECT * FROM Sotrudniki LEFT OUTER JOIN Doljn
ON S_doljn=D_code
- результат включат все поля и таблицы Sotrudniki и таблицы Doljn. Число строк соответствует числу записей таблицы Sotrudniki. В строках, относящихся к записям, для которых в Doljn не нашлось соответствие, поля таблицы Doljn остаются пустыми.
2) SELECT * FROM Sotrudniki RIGHT OUTER JOIN Doljn
ON S_doljn=D_code
- число строк соответствует числу записей таблицы Doljn. В строках, относящихся к записям, для которых в Sotrudniki не нашлось соответствие, поля таблицы Sotrudniki остаются пустыми.
3) SELECT * FROM Sotrudniki FULL OUTER JOIN Doljn ON
S_doljn=D_code
- к строкам, относящимся к таблице Sotrudniki добавлены строки, относящиеся к таблице Doljn, для которых нет соответствия в таблице Sotrudniki.