Библиотека сайта rus-linux.net
Глава 7
ФОРМИРОВАНИЕ ВЫВОДОВ
ЗАПРОСОВ
ЭТА ГЛАВА РАСШИРИТ ВАШИ ВОЗМОЖНОСТИ
в работе с выводом который
производит запрос. Вы узнаете как вставлять
текст и константы между выбранных полей, как использовать выбранные
поля в математических выражениях, чьи результаты затем становятся
выводом, и как сделать чтобы ваши значения выводились
в определенном порядке. Эта последняя особен- ность включена, чтобы
упорядочивать ваш вывод по любым столбцам, любым полученным значениям
этого столбца, или по обеим.=========== СТРОКИ
И ВЫРАЖЕНИЯ ============Большинство основанных
на SQL баз данных предоставляют специальные средства позволяющие
Вам совершенствовать вывод ваших запросов. Конечно, они претерпевают
значительные изменения от программы к программе, и их обсуждение
здесь не входит в наши задачи, однако, имеются пять особенностей
созданых в стандарте SQL которые позволяют вам делать больше чем
просто вывод значений полей и агрегатных данных.СКАЛЯРНОЕ ВЫРАЖЕНИЕ
С ПОМОЩЬЮ ВЫБРАННЫХ
ПОЛЕЙПредположим что вы хотите
выполнять простые числовые вычисления данных чтобы затем помещать
их в форму больше соответствующую вашим потребностям. SQL позволяет
вам помещать скалярные выражения и константы среди выбраных
полей. Эти выражения могут дополнять или замещать поля в предложениях
SELECT, и могут включать в себя одно или более выбранных полей.
Например, вы можете пожелать, представить комиссионные вашего
продавца в процентном отношении а не как десятич- ные числа. Просто достаточно:
SELECT snum, sname,
city, comm * 100 FROM Salespeople;
Вывод из этого запроса
показывается в Рисунке 7.1.СТОЛБЦЫ ВЫВОДА
Последний столбец предшествующего
примера непомечен( т.е. без наимено- вания), потому что это
- столбец вывода. Столбцы вывода - это столбцы дан-
ных созданные запросом
способом, иным чем просто извлечение их из таб- лицы. Вы создаете их
всякий раз, когда вы используете агрегатные функции=============== SQL
Execution Log ============ | | | SELECT snum, sname,
city, comm * 100 | | FROM Salespeople;
| | ==============================================|
| snum sname city |
| ------ --------- -----------
--------- | | 1001 Peel London 12.000000
| | 1002 Serres San Jose
13.000000 | | 1004 Motika London
11.000000 | | 1007 Rifkin Barcelona
15.000000 | | 1003 Axelrod New York
10.000000 | | | ===============================================
Рисунок 7.1: Помещение
выражения в вашем запросе, константы, или выражения
в предложении SELECT запроса. Так как имя столбца
- один из атрибутов таблицы, столбцы которые приходят не из таблиц не имеют
никаких имен. Другими словами непомеченные, столбцы вывода могут обрабатываться
также как и столбцы извлеченные из таблиц, почти во всех ситуациях.ПОМЕЩЕНИЕ ТЕКСТА
В ВАШЕМ ВЫВОДЕ ЗАПРОСАСимвол 'A', когда ничего
не значит сам по себе, - является константой, такой например как число
1. Вы можете вставлять
константы в предложение SELECT запроса, включая и текст. Однако символьные
константы, в отличие от числовых констант, не могут использоваться
в выражениях. Вы можете иметь выражение 1 + 2 в вашем предложении
SELECT, но вы не можете использовать выражение типа 'A' + 'B'; это
приемлемо только если мы имеем в виду что 'A' и 'B' это просто буквы, а
не переменные и не символы. Тем ни менее, возможность
вставлять текст в вывод ваших запросов очень удобная штука.
Вы можете усовершенствовать
предыдущий пример представив комиссион ные как проценты со
знаком процента (%). Это даст вам возможность помещать в вывод такие единицы
как символы и комментарии, как например в следующем примере ( вывод показывается
в Рисунке 7.2 )SELECT snum, sname,
city, ' % ', comm * 100 FROM Salespeople;=============== SQL
Execution Log ============ | | | SELECT snum, sname,
city, '%' comm * 100 | | FROM Salespeople;
| | ==============================================|
| snum sname city |
| ------ -------- -----------
---- --------- | | 1001 Peel London %
12.000000 | | 1002 Serres San Jose
% 13.000000 | | 1004 Motika London
% 11.000000 | | 1007 Rifkin Barcelona
% 15.000000 | | 1003 Axelrod New York
% 10.000000 | | | ===============================================
Рисунок 7.2: Вставка
символов в ваш выводОбратите внимание что
пробел перед процентом вставляется как часть строки. Эта же самая
особенность может использоваться чтобы маркиро- вать вывод вместе с
вставляемыми комментариями. Вы должны помнить, что этот же самый комментарий
будет напечатан в каждой строке вывода, а не просто один раз
для всей таблицы. Предположим что вы генерируете вывод для отчета который
бы указывал число порядков получаемых в тече- ние каждого дня. Вы
можете промаркировать ваш вывод ( см. Рисунок 7.3 ) сформировав запрос следующим
образом:SELECT ' For ', odate,
', there are ', COUNT ( DISTINCT
onum ), 'orders.' FROM Orders
GROUP BY odate;Грамматической некорректности
вывода, на 5 Октября, невозможно избежать не создав запроса,
еще более сложного чем этот. ( Вы будете должны использовать
два запроса с UNION, который=============== SQL
Execution Log ============== | | | SELECT 'For', odate,
', ' there are ' , | | COUNT (DISTINCT onum),
' orders ' | | FROM Orders |
| GROUP BY odate; |
| ===============================================
| | odate |
| ------ ----------
--------- ------ ------- | | For 10/03/1990 , there
are 5 orders. | | For 10/04/1990 , there
are 2 orders. | | For 10/05/1990 , there
are 1 orders. | | For 10/06/1990 , there
are 2 orders. | | | ================================================
Рисунок 7.3: Комбинация
текста, значений поля, и агрегатовмы будем описывать в
Главе 14. ) Как вы можете видеть, одиночный неизменный комментарий
для каждой строки таблицы может быть очень полезен, но имеет ограничения.
Иногда изящнее и полезнее, произвести один комментарий для
всего вывода в целом, или производить свой собс- твенный комментарии
для каждой строки. Различные программы
использующие SQL часто обеспечивают специаль- ные средства типа генератора
отчетов( например Report Writer), которые
разработаны чтобы форматировать и совершенствовать вывод. Вложенный SQL может также эксплуатировать
возможности того языка в который он вложен. SQL сам по себе
интересен прежде всего при операциях с дан- ными. Вывод, по существу,
это информация, и программа использующая SQL может часто использовать
эту информацию и помещать ее в более привлекательную форму.
Это, однако, вне сферы самой SQL.======= УПОРЯДОЧЕНИЕ
ВЫВОДА ПОЛЕЙ ========Как мы подчеркивали,
таблицы - это неупорядоченные наборы данных, и данные которе выходят
из их, не обязательно появляются в какой-то опре- деленной последовательности.
SQL использует команду ORDER BY чтобы позволять вам упорядочивать
ваш вывод. Эта команда упорядочивает вывод запроса согласно значениям в
том или ином количестве выбранных столбцов. Многочисленые
столбцы упорядочиваются один внутри другого, также как с GROUP BY,
и вы можете определять возрастание ( ASC ) или убывание ( DESC ) для
каждого столбца. По умолчанию установлено - воз- ростание. Давайте рассмотрим
нашу таблицу порядка приводимую в порядок с помощью номера
заказчика ( обратите внимание на значения в cnum столбце):SELECT *
FROM Orders
ORDER BY cnum DESC;Вывод показывается в
Рисунке 7.4.=============== SQL
Execution Log ============== | | | SELECT * |
| FROM Orders |
| ORDER BY cnum DESC;
| | ===============================================
| | onum amt odate cnum
snum | | ------ -------- ----------
----- ----- | | 3001 18.69 10/03/1990
2008 1007 | | 3006 1098.16 10/03/1990
2008 1007 | | 3002 1900.10 10/03/1990
2007 1004 | | 3008 4723.00 10/05/1990
2006 1001 | | 3011 9891.88 10/06/1990
2006 1001 | | 3007 75.75 10/04/1990
2004 1002 | | 3010 1309.95 10/06/1990
2004 1002 | | 3005 5160.45 10/03/1990
2003 1002 | | 3009 1713.23 10/04/1990
2002 1003 | | 3003 767.19 10/03/1990
2001 1001 | | | ================================================
Рисунок 7. 4: Упорядочение
вывода с помощью убывания поляУПОРЯДОЧЕНИЕ С
ПОМОЩЬЮ МНОГОЧИСЛЕНЫХ
СТОЛБЦОВ Мы можем также упорядочивать
таблицу с помощью другого столбца, например с помощью поля
amt, внутри упорядочения поля cnum. ( вывод показан в Рисунке
7.5 ):SELECT *
FROM Orders
ORDER BY cnum DESC,
amt DESC;=============== SQL
Execution Log ============== | | | SELECT * |
| FROM Orders |
| ORDER BY cnum DESC,
amt DESC; | | ===============================================
| | onum amt odate cnum
snum | | ------ -------- ----------
----- ----- | | 3006 1098.16 10/03/1990
2008 1007 | | 3001 18.69 10/03/1990
2008 1007 | | 3002 1900.10 10/03/1990
2007 1004 | | 3011 9891.88 10/06/1990
2006 1001 | | 3008 4723.00 10/05/1990
2006 1001 | | 3010 1309.95 10/06/1990
2004 1002 | | 3007 75.75 10/04/1990
2004 1002 | | 3005 5160.45 10/03/1990
2003 1002 | | 3009 1713.23 10/04/1990
2002 1003 | | 3003 767.19 10/03/1990
2001 1001 | | | ================================================
Рисунок 7.5: Упорядочение
вывода с помощью многочисленых полейВы можете использовать
ORDER BY таким же способом сразу с любым числом столбцов. Обратите
внимание что, во всех случаях, столбцы кото- рые упорядочиваются
должны быть указаны в выборе SELECT. Это - требование ANSI
которые в большинстве, но не всегда, предписано системе. Следующая команда,
например, будет запрещена:SELECT cname, city
FROM Customers
GROUP BY cnum;Так как поле cnum не
было выбранным полем, GROUP BY не cможет найти его чтобы использовать
для упорядочения вывода. Даже если ваша система позволяет это, смысл
упорядочения не будет понятен из вывода, так что включение (в предложение
SELECT) всех столбцов, используемых в предло- жении ORDER BY, в принципе
желательно.УПОРЯДОЧЕНИЕ АГРЕГАТНЫХ
ГРУППORDER BY может кроме
того, использоваться с GROUP BY для упорядоче- ния групп. Если это
так, то ORDER BY всегда приходит последним. Вот - пример из последней
главы с добавлением предложения ORDER BY. Перед сгруппированием
вывода, порядок групп был произвольным; и мы, теперь, заставим
группы размещаться в последовательности:SELECT snum, odate,
MAX (amt) FROM Orders
GROUP BY snum, odate
GROUP BY snum;Вывод показывается в
Рисунке 7.6. =============== SQL
Execution Log ============== | | | SELECT snum, odate,
MAX (amt) | | FROM Orders |
| GROUP BY snum, odate
| | ORDER BY snum ; |
| ===============================================
| | snum odate amt |
| ----- ---------- --------
| | 1001 10/06/1990 767.19
| | 1001 10/05/1990 4723.00
| | 1001 10/05/1990 9891.88
| | 1002 10/06/1990 5160.45
| | 1002 10/04/1990 75.75
| | 1002 10/03/1990 1309.95
| | 1003 10/04/1990 1713.23
| | 1004 10/03/1990 1900.10
| | 1007 10/03/1990 1098.16
| | | ================================================
Рисунок 7. 6: Упорядочение
с помощью группыТак как мы не указывали
на возрастание или убывание порядка, возрастание используется
по умолчанию.УПОРЯДОЧЕНИЕ ВЫВОДА
ПО НОМЕРУ СТОЛБЦАВместо имен столбца,
вы можете использовать их порядковые номера для указания поля используемого
в упорядочении вывода. Эти номера могут ссылаться не на порядок
столбцов в таблице, а на их порядок в выводе. Другими словами, поле
упомянутое в предложении SELECT первым, для ORDER BY - это поле
1, независимо от того каким по порядку оно стоит в таблице. Например, вы
можете использовать следующую команду чтобы увидеть определенные
поля таблицы Продавцов, упорядоченными в порядке убывания к
наименьшему значению комиссионных ( вывод показывается Рисунке
7.7 ):SELECT sname, comm
FROM Salespeople
GROUP BY 2 DESC;=============== SQL
Execution Log ============ | | | (SELECT sname, comm
| | FROM Salespeople |
| ORDER BY 2 DESC; |
| =============================================
| | sname comm |
| -------- --------
| | Peel 0.17 |
| Serres 0.13 |
| Rifkin 0.15 |
| | ===============================================
Рисунок 7. 7: Упорядочение
использующее номераОдна из основных целей
этой возможности ORDER BY - дать вам возмож- ность использовать GROUP
BY со столбцами вывода также как и со стол- бцами таблицы. Столбцы
производимые агрегатной функцией, константы, или выражения в предложении
SELECT запроса, абсолютнопригодны для использования с GROUP
BY, если они ссылаются к ним с помощью номера. Например, давайте сосчитаем
порядки каждого из наших продавцов, и выведем результаты в
убывающем порядке, как показано в Рисунке 7.8:SELECT snum, COUNT
( DISTINCT onum ) FROM Orders
GROUP BY snum
ORDER BY 2 DESC;=============== SQL
Execution Log ============== | | | SELECT snum, odate,
MAX (amt) | | FROM Orders |
| GROUP BY snum |
| ORDER BY 2 DESC; |
| ===============================================
| | snum |
| ----- ---------- |
| 1001 3 |
| 1002 3 |
| 1007 2 |
| 1003 1 |
| 1004 1 |
| | ================================================
Рисунок 7.8: Упорядочение
с помощью столбца выводаВ этом случае, вы должны
использовать номер столбца, так как столбец вывода не имеет имени;
и вы не должны использовать саму агрегатную функцию. Строго говоря
по правилам ANSI SQL, следующее не будет рабо- тать, хотя некоторые
системы и пренебрегают этим требованием:SELECT snum, COUNT
( DISTINCT onum ) FROM Orders
GROUP BY snum
GROUP BY COUNTОМ
( DISTINCT onum ) DESC;Это будет отклонено
большинством систем!УПОРЯДОЧЕНИЕ С
ПОМОЩЬЮ ОПРЕТОРА NULLЕсли имеются пустые
значения (NULL) в поле которое вы используете для упорядочивания вашего
вывода, они могутут или следовать или предшество- вать каждому другому
значению в поле. Это - возможность которую ANSI оставил для индивидуальных
программ. Данная программа использует ту или иную форму.================
РЕЗЮМЕ =================В этой главе, вы изучили
как заставить ваши запросы делать больше, чем просто выводить
значения полей или объединять функциональные данные таблиц. Вы можете
использовать поля в выражениях: например, вы можете умножить числовое
поле на 10 или даже умножить его на другое числовое поле.
Кроме того, вы можете помещать константы, включая и символы, в
ваш вывод, что позволяет вам помещать текст непосредственно в запрос
и получать его в выводе вместе с данными таблицы.
Это дает вам возможность
помечать или объяснять ваш вывод различ- ными способами.
Вы также изучили как
упорядочивать ваш вывод. Даже если таблица сама по себе остается
неупорядоченной, предложение ORDER BY дает вам возможность управлять
порядком вывода строк данного запроса. Вывод запроса может
быть в порядке возрастания или убывания, и столбцы могут быть вложенными
один внутрь другого. Понятие выводимых столбцов
объяснялось в этой главе. Вы теперь зна- ете что выводимые столбцы
можно использовать чтобы упорядочивать вывод запроса, но эти
столбцы - без имени, и следовательно должны определяться их порядковым
номером в предложении ORDER BY. Теперь, когда вы увидели
что можно делать с выводом запроса осно- ванного на одиночной
таблице, настало время чтобы перейти к возмож- ностям улучшенного запроса
и узнать как сделать запрос любого числа таблиц в одной команде,
определив связи между ними как вы это обычно делали. Это будет темой
Главы 8.************** РАБОТА
С SQL **************1. Предположим что каждый
продавец имеет 12% комиссионных. Напишите запрос к таблице
Порядков который мог бы вывести номер порядка, номер продавца,
и сумму комиссионных продавца для этого порядка.2. Напишите запрос к
таблице Заказчиков который мог бы найти высшую оценку в каждом городе.
Вывод должен быть в такой форме:For the city
(city), the highest rating is: (rating). 3. Напишите запрос который
выводил бы список заказчиков в нисходящем порядке. Вывод поля
оценки( rating ) должден сопровождаться именем закзчика и его номером.
4. Напишите запрос который
бы выводил общие порядки на каждый день и помещал результаты в
нисходящем порядке.( См. Приложение A для
ответов. )