Запросы на языке SQL строятся на основе оператора select. В общем виде это запрос вида
select <поля>
from <список таблиц>
where <условие выбора> .
Строится декартово произведение из списка таблиц. Из него выбираются кортежи в соответствии с условиями выбора, а затем делаются проекции на список полей. Если вместо списка стоит *, то проекция не делается.
Чтобы из результатов исключить дубликаты, после select ставим distinct. Если нужно указать, что нужны все результаты, можно поставить all, но этот режим работает по умолчанию.
Условия выбора:
1) могут использоваться операторы сравнения,
2) могут присутствовать логические связки and, or, not,
3) специальные операторы: between, in, like, is null.
In – проверяет принадлежность элемента множеству.
Between – проверяет диапазон. Например, вывести сотрудников, зарплата которых составляет от 100 до 200 у.е.:
Select name from empl where salary between 100 and 200
Like – используется, когда в значении строкового элемента нас интересуют определенные подстроки (соответствие шаблону). Подчеркивание заменяет один любой символ, % заменяет последовательность символов произвольной длины.
Select * from empl where name like ‘Иван%’.
Is null – проверяет, известно ли значение поля. Например, для какого служащего неизвестен номер отдела, в котором он работает:
Select name from empl where depno is null
Функция агрегирования. На входе они получают множество значений, а выдают только одно.
Count – функция, которая определяет количество всех выданных запросом значений. Определить, сколько человек работают в отделе №21:
Select count(*) from empl where depno=21
Sum – вычисляет арифметическую сумму всех выданных значений данного поля. Каков фонд зарплаты в отделе 21:
Select sum (salary) from empl where depno=21
AVG – вычисляет среднее значение.
В функциях агрегирования также можно использовать выражения. Например, вычислить среднюю зарплату по отделу 21. Добавим в таблицу empl поле бонус - премия:
Alter table empl add bonus int
Select avg(salary+bonus) from empl where depno=21
Min и max – вычисляют минимальное и максимальное значение соответственно из выбранных полей.
Select max(salary+bonus) from empl where depno=21
Группирование информации. При использовании функции агрегирования результат запроса можно разбивать на группы по определенному признаку. Например, выдать информацию о средней зарплате по отделам:
Select depno, avg (salary+bonus) from empl group by depno
Можно использовать условие. Язык SQL требует, чтобы условие для отбора групп в результирующее отношение задавалось в разделе having. Например, определить отделы, в которых средняя зарплата больше 1000 у.е.:
Select depno from empl group by depno having avg(salary+bonus)>1000
В разделе having можно использовать только такие аргументы, у которых только одно значение для группы данных. В наших примерах это либо номер отдела, либо результат функции агрегирования.
Вложенные запросы. В этом случае внутренний запрос генерирует значение, которое тестируется в условиях внешнего запроса. Внутренний запрос обычно называется подзапросом. Пример: дать сведения о сотрудниках планового отдела:
Select name, office, salary from empl where depno = (select depno from depart where name=’плановый’)
В рассмотренном примере подзапрос выдает всегда только одно значение. Поэтому результат работы оператора сравнения получается корректным. В противном случае может возникнуть ошибка времени выполнения.
Если все же необходимо использовать подзапрос, выдающий множество значений, то следует использовать оператор in. Пример: пусть задана таблица
Create table project (pnum int,
pname char(30))
create table works( enum int,
pnum int,
start date,
finish date)
Таблица works является ассоциативной таблицей, обеспечивающей связь «многие ко многим» между служащими и проектами. Пример: найти проекты, над которыми работают ведущие специалисты.
Select distinct pnum from works where enum in (select empno from empl where office=’ведущий специалист’)
Подзапросы в разделе having. В SQL можно формулировать запросы, анализирующие данные в группах. Пример: определить, какие есть на предприятии ставки, превышающие среднюю ставку по отделу 21, и сколько человек получают эти ставки. Нужно определить среднюю зарплату по отделу № 21, разбить сотрудников на группы в соответствии с их ставками, выбрать те группы, по которым группы превышают среднюю зарплату по отделу № 21, выдать в результирующем отношении пары (зарплата, число сотрудников).
Select salary, count (empno)
from empl
group by salary
having salary>(select avg(salary)
from empl
where depno=21)
Связанные подзапросы. До сих пор мы имели дело с подзапросами, которые выполнялись один раз и весь результат передавали внешнему запросу. SQL позволяет определять подзапросы, которые запускаются каждый раз, когда внешний запрос пытается выбрать данное.
Подзапрос при каждом запуске получает некоторое значение от внешнего запроса аналогично передаче параметров в процедуре. Для передачи параметров подзапрос ссылается на поля таблиц из раздела from внешнего запроса. Такой подзапрос называется связанным. Пример: найти всех служащих, у которых 31 декабря 2012 заканчивается работа по какому-либо проекту.
Select name
From empl
Where ’31 dec 2012’ in
(select finish
from works
where enum=Empl.empno)
В этом виде запроса содержимое кортежей из таблицы из основного запроса (empl) влияет на подзапрос внутренней таблицы works. В свою очередь, результат выполнения подзапроса булевым значением влияет на выбор в главном запросе.
Подзапрос в связанном запросе можно рассматривать как булевую функцию, в которую можно передавать параметры из главной таблицы. Поле empl.empno является параметром.
Рассмотрим, как работает связанный подзапрос.
1) Для каждого кортежа из основной таблицы запускается подзапрос.
2) Подзапросу в качестве параметра передается значение атрибута текущего кортежа empl.empno.
3) После выполнения подзапроса вычисляется логический оператор in. Результат этого оператора определяет, надо ли выбрать текущий кортеж из основной таблицы или нет.
Тот же результат можно получить и простым соединением:
Select empl.name
From empl, works
Where (works.enum=empl.empno) and
(works.finish=’31-dec-2012’)
Однако такой запрос может работать гораздо дольше предыдущего, так как он будет строить полное декартово произведение. Кроме того, он выдаст в результате дубликаты, если есть сотрудники, у которых несколько работ заканчиваются 31 декабря 2012 года.
Чтобы их не было, нужно использовать distinct.
Пример: перечислить сотрудников, работающих над несколькими проектами одновременно.
