Форматирование выходных данных запросов.




Во многих СУБД, которые используют 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 есть оптимизатор, который стремится найти самые эффективные способы выполнения запросов. Хороший оптимизатор сам преобразует версию запроса с соединением таблиц в версию с подзапросом. Однако, способа проверки сделано это или нет – не существует.





©2015-2017 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.

Обратная связь

ТОП 5 активных страниц!