Во многих СУБД, которые используют SQL, имеется специальное средство, позволяющее оформить результат запросов. Эти средства позволяют получить пользователю нечто большее, чем значение полей и функций агрегирования.
В предложении SELECT, на ряду с именами полей, могут указываться константы и выражения над полями.
Пример:
| Osnum | ||
| недобрано баллов | ||
| -\\- | ||
| -\\- | ||
| … | … | … |
SELECT osnum, ”Недобрано баллов =”,
5 – ocen From оценка;
По стандарту SQL столбцы с константами и выражениями не имеют имени. Microsoft Access позволяет поименовать столбцы с помощью ключевого слова AS имя_столбца
Для нашего примера:
SELECT osnum, ”Недобрано баллов =”,
5 – ocen AS ”Баллы до 5” From ocenka;
Результаты выполнения запроса могут быть упорядочены по значению одного или нескольких столбцов, путем указания предложения ORDER BY
ORDER BY поле[ASC/DESC],…[ASC/DESC]
Где ASC – сортировка по возрастанию
DESC – сортировка по убыванию.
Пример: упорядочить данные из таблицы «студенты» по возрастанию кода студента и убыванию полученных оценок.
| osnum | odate | ocen |
| 10/01/01 | ||
| 15/01/01 | ||
| 11/01/01 | ||
| 17/01/01 | ||
| 11/01/01 | ||
| 16/01/01 |
SELECT osnum, odate, ocen
FROM оценка
ORDER BY osnum, ocen DESC;
Предложение ORDER BY может совместно использоваться с GROUP BY.
При этом операция сортировки всегда реализуется по концу выполнения запросов над уже построенной таблицей.
| osnum | AVG(ocen) |
Пример:
SELECT osnum, AVG(ocen)
FROM оценка
GROUP BY osnum
ORDER BY osnum
| osnum | AVG(ocen) |
Может потребоваться отсортировать таблицу по значениям вычисляемого столбца не имеющего имени. В таком случае, запрос будет иметь вид:
|
|
SELECT osnum, AVG(ocen)
FROM оценки
GROUP BY osnum
ORDER BY AVG(ocen);
По стандарту SQL такой запрос является ошибочным, но многие системы, в т.ч. и Access его воспринимают и обрабатывают. По стандарту SQL, в предложении ORDER BY, необходимо указывать не имя столбца а его номер.
ORDER BY 2;
Номер столбца можно указывать и для столбца с именем, но нужно помнить, что номер столбца это всегда номер столбца в запросе, а не в таблице, по которой строится запрос. Если поле, которое используется для упорядочения данных существует NULL- значение, т.е. значение не задано, то они располагаются всегда в начале или конце таблицы. Конкретный вариант стандарта не оговаривается и вопрос решается индивидуально для каждой СУБД.
Соединение таблиц
До сих пор мы рассматривали запросы, которые базировались на одной таблице. Теперь перейдем к запросам, которые будут базироваться на множестве таблиц. При использовании в запросе множества таблиц их имена перечисляются в предложении FROM через запятую. В общем случае:
SELECT список_полей
FROM список_таблиц
Строит декартово произведение таблиц.
Установление связей со строками таблиц может быть выполнено с помощью предложения WHERE.
Пример: Построить список дипломников с указанием руководителя работы.
| snmae | Pname |
| С-1 | П-1 |
| С-2 | П-1 |
| С-3 | П-2 |
SELECT Sname, pname
FROM ПРЕПОДАВАТЕЛЬ, СТУДЕНТ
WHERE pnum=spdp;
Таким образом, представленный пример реализует операцию слияния реляционной алгебры средствами SQL.
В предложении WHERE могут быть одновременно заданы и другие операции отбора записи из исходных таблиц.
Например, дополнительное условие для кафедры К-1 упорядочены по убыванию фамилии студентов.
|
|
| Sname | pname |
| С-2 | П-1 |
| С-2 | П-1 |
SELECT sname, pname
FROM ПРЕПОДАВАТЕЛЬ, СТУДЕНТ
WHERE pnum=spdp AND
Pcaf =”K-1”
GROUP BY sname DESC;
В соединении могут участвовать несколько таблиц, например: пусть требуется отобразить результаты экзамена в форме преподаватель, студент, дата, оценка.
| pname | sname | odate | Ocen |
| П-1 | С-1 | 10/01/01 | |
| П-1 | С-2 | 11/01/01 | |
| П-1 | С-3 | 11/01/01 | |
| П-3 | С-1 | 15/01/01 | |
| П-3 | С-3 | 16/01/01 | |
| П-3 | С-3 | 17/01/01 |
SELECT pname, sname, odate, ocen
FROM ПРЕПОДАВАТЕЛЬ, СТУДЕНТ, ОЦЕНКА
WHERE pnum = opnum AND
Snum = osnum;
Как правило, в предложении WHERE в присоединении таблиц используется операция равно. Такие соединения, основанные на равенстве, называются эквисоединениями. Использование равенства не всегда является обязательным. Так же можно использовать и другие операции сравнения, хотя они и используются очень редко.
Использованные в примерах имена полей были различны.
При использовании имен полей, которые совпадают, следует указывать имя таблицы и поля, с целью устранения неоднозначности.
Например, для списка дипломников:
SELECT СТУДЕНТ, sname, ПРЕПОДАВАТЕЛЬ, pname
FROM СТУДЕНТ, ПРЕПОДАВАТЕЛЬ,
WHERE pnum=spdp;
В предложении FROM могут быть определены алиасы(дополнительные имена) в форме
FROM имя_таблицы AS[АЛИАС],…
Для предыдущего примера:
SELECT C.sname, П.pname
FROM ПРЕПОДАВАТЕЛЬ П, СТУДЕНТ С
WHERE pnum = spdp;
Алиасы могут применяться для сокращения записей сложного запроса. Другим примером неоднозначности в опознании полей является соединение таблицы со своей копией (самообъединение).
Это может понадобиться для получения всевозможных пар значений одного атрибута. Это требует обязательного определения алеаса.
|
|
| sname | Sname |
| C-1 | C-1 |
| C-1 | C-2 |
| C-2 | C-2 |
| C-2 | C-1 |
| C-4 | C-4 |
Пример: построить множество пар фамилий студентов, имеющих одного руководителя дипломного проекта.
SELECT C1.sname, C2.sname
FROM студент С1, студент С2
WHERE C1.spdp=C2.spdp
Соединение таблиц может быть определено без использования предложения WHERE путем указания операции JOIN в предложении FROM. Формат этой команды имеет следующий вид:
FROM табл1(irrer/left/right) JOIN табл2 OR условие_объединения;
Где табл1 и табл2 – имена объединяемых таблиц.
Условие объединения обычно имеет вид:
Табл1.поле1=табл2.поле2
- INNER JOIN – объединяет записи двух таблиц
- LEFT JOIN – создаёт левое внешнее объединение, при котором все записи из первой (левой) таблицы включаются в результат, даже если во второй правой таблице нет соответствующих записей по условию;
- RIGHT JOIN – создаёт правое внешнее объединение, при котором все записи из второй правой таблицы включаются в результат;
Пример 1:
Получить список дипломников с указанием руководителя.
| sname | pname |
| C-1 | П-1 |
| C-2 | П-1 |
| C-3 | П-2 |
SELECT sname, pname,
FROM студент INNER JOIN
Преподаватель ON pnum = spdp;
Пример 2: получить список студентов и руководителей при его наличии.
| sname | pname |
| C-1 | П-1 |
| C-2 | П-1 |
| C-3 | П-2 |
| C-4 |
SELECT sname, pname,
FROM студент LEFT JOIN
Преподаватель ON pnum = spdp;
Если соединяется более 2 таблиц, то используются вложенные операции JOIN.
FROM таблица1 INNER JOIN (таблица2 INNER JOIN (таблица3 ON условие_объединения 2_3) ON условие_объединения 1_2);
Вложенные подзапросы
SQL позволяет вкладывать запросы друг в друга. Обычно внутренний запрос генерирует значения, которые тестируются на предмет истинности логического выражения.
| Sname |
| C-1 |
| C-2 |
Пример: Пусть известна фамилия преподавателя и требуется получить список его дипломников.
SELECT sname FROM СТУДЕНТ
WHERE spdp=
(SELECT pnum FROM ПРЕПОДАВАТЕЛЬ
WHERE pname = ”П-1”);
В данном примере, чтобы выполнить основной (внешний) запрос SQL сначала выполняет внутренний запрос (подзапрос) в предложении WHERE основного запроса.
В результате будет получен код преподавателя с его фамилией. Затем выполняется основной запрос с предложением WHERE.
Существенным в этом случае является то, что подзапрос возвращает не более одного значения.
Например: пусть требуется получить фамилии дипломника для определенной кафедры.
SELECT sname FROM СТУДЕНТ
WHERE spdp=
(SELECT pnum FROM ПРЕПОДАВАТЕЛЬ
WHERE Pcaf = “K-1”);
Запрос такого типа приведет к ошибке, потому что будет возвращать два значения. Иногда проблему единственности можно решить с помощью параметра DISTINCT в предложении SELECT.
Пример: получить фамилию преподавателя, принимавшего экзамен 11/01/01.
| Pname |
| П-1 |
SELECT pname FROM ПРЕПОДАВАТЕЛЬ
WHERE pnum=
(SELECT DISTINCT opnum
FROM оценка WHERE odate = “11/01/01”);
Без параметра DISTINCT запрос был бы ошибочным. Потому что возвращал бы 2 значения, хотя и совпадающие. С помощью DISTINCT эта ошибка устраняется. Однако, в данном примере возможна ошибка, если 2 преподавателя принимают экзамен в один день. Следует отметить, что по стандарту SQL логические выражения с использование подзапросов, должны иметь вид:
WHERE скалярное_выражение = (подзапрос)
а не наоборот.
Одним из видов функций, которые всегда выдают единственное значение для любого количества строк, является функции агрегирования. Таким образом, единственность значения подзапросов генерируется при использовании в нем единственной агрегатной функции без предложения GROUP BY.
Пример: получить коды студентов, имеющих у преподавателя П-1 оценки не ниже среднего балла, полученного всеми студентами у этого преподавателя.
SELECT osnum, odate,ocen FROM оценка
WHERE ocen >=
| osnum | Odate | Ocen |
| 10/01/01 | ||
| 11/01/01 |
(SELECT AVG(ocen) FROM оценка
WHERE opnum=
(SELECT pnum FROM преподаватель
WHERE pname = “П-1”))
AND opnum =
(SELECT pnum FROM преподаватель
WHERE pname = “П-1”);
Сгруппированные, т.е. примененные предложением GROUP BY, агрегатные функции могут дать множество значений, поэтому их нельзя применять в подзапросах. Такие команды отвергаются в принципе. Можно применять подзапросы, которые возвращают любое количество строк, при использовании в нем специального оператора IN.
Пример: получить фамилии дипломников для преподавателя определенной кафедры.
| sname | Sgrp |
| C-1 | Г-1 |
| C-2 | Г-2 |
| C-3 | Г-2 |
SELECT sname, sgrp FROM студент
WHERE spdp IN
(SELECT pnum FROM преподаватель
WHERE pcaf = “K-1”);
Когда IN используется в подзапросе, множество значений IN, строится по результатам подзапроса. Это позволяет во многих случаях оптимизировать запросы, отказавшись от построения декартова произведения таблиц. Так этот запрос можно было бы реализовать по-другому:
SELECT sname FROM студент, преподаватель
WHERE spdp = pnum AND
Pcaf = “K-1”;
Или:
SELECT sname FROM студент INNER JOIN
Преподаватель ON spdp=pnum
WHERE pcaf = “K-1”;
Оба эти варианта тоже дадут правильный ответ, только они менее эффективны, чем с использованием подзапросов.
Пусть в таблице студент n-записей, а в таблице преподавателей m-записей. Для последнего примера, условие spd=pnum WHERE pcaf = “K-1” проверяется n*m количество раз.
В результате с подзапросом требуется однократная проверка таблицы преподаватель, по которому строится подмножество значений кодов преподавателей. Следует отметить, что практически во всех коммерческих реализациях SQL есть оптимизатор, который стремится найти самые эффективные способы выполнения запросов. Хороший оптимизатор сам преобразует версию запроса с соединением таблиц в версию с подзапросом. Однако, способа проверки сделано это или нет – не существует.