Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Глава 5 Запросы с использованием единственной таблицы 5.1. О предложениях SELECT и SUBQUERY SELECT — предложение языка SQL извлекает строки, столбцы и производные значения (например, вычисляемые выражения) из одной или нескольких таб- лиц базы данных. В большинстве приложений SELECT является наиболее часто используемым оператором DML. При формировании запроса SELECT пользователь описыва- ет ожидаемый набор данных, но не указывает, какие физические операции должны быть произведены для получения этого набора. Определение для за- проса оптимального плана является задачей СУБД, а точнее — оптимизатора. Полный синтаксис стандартного предложения SELECT очень мощный и слож- ный, но его можно разделить на следующие основные фразы. SELECT [{ALL | DISTINCT}] отбираемый_элемент [AS псевдоним] [,...] FROМ [ONLY | OUTER] { имя_таблицы [[AS] псевдоним] | имя_представления [[AS] псевдоним] }[,...] [ [тип_соединения] JOIN условие_соединения ] [WHERE условие_поиска [ {AND | OR | NOT} условие_поиска [...]] [[ START WITH условие ]CONNECT BY [PRIOR] условие ] [GROUP BY группировка_по_выражению { группировка_по_столбцам | ROLLUP группировка_по_столбцам | CUBE группировка_по_столбцам | GROUPING SETS ( список_наборов_группировок ) | ( ) | набор_группировок, список__наборов_ группировок } [HAVING условие_поиска] ] [ORDER BY {выражение_для_сортировки [ASC | DESC]} [,...] ]; Часть II. Язык SQL. Извлечение данных 100 Расшифровка сокращений отбираемый_элемент , псевдоним , имя_таблицы , имя_представления , условие_соединения , условие_поиска и других, входя- щих в этот синтаксис, намного его усложнит. Еще более сложным и громозд- ким будет выглядеть синтаксис SELECT для Oracle Database 10g, в котором существует очень большое количество расширений стандарта SQL:2003 [6]. Поэтому приведем здесь лишь краткое назначение основных фраз предложе- ния SELECT , перенеся их подробное изучение в следующие разделы данной главы и главы 6: FROM — содержит перечень таблиц, из которых должна выводиться запра- шиваемая информация; SELECT — содержит список столбцов из таблиц, перечисленных во фразе FROM , а также выражений, использующих значения из этих таблиц; WHERE — включает набор условий для отбора определенных строк таблиц, из которых должна выводиться запрашиваемая информация; CONNECT BY — определяет условие соотношения между родительскими стро- ками и их строками-потомками в иерархических запросах (см. разд. 5.6); GROUP BY — разбивает выводимые строки на логические группы, приме- няя к ним указанные в SELECT агрегатные функции ( COUNT , SUM , MAX и др.), создавая для каждой из групп итоговую строку с количеством, суммой, максимумом и др. (см. разд. 4.6); HAVING — включает набор условий для отбора определенных итоговых строк из тех, которые получаются при выполнении фразы GROUP BY ; ORDER BY — сортирует полученный набор строк или итоговых строк. Напомним, что SELECT , как и любое другое предложение языка SQL, должно заканчиваться символом точка с запятой. Предложение SUBQUERY (подзапрос) — это предложение SELECT , заключенное в круглые скобки и встроенное в тело другого запроса. Подзапросы (вложен- ные запросы) позволяют получать одно или несколько значений и помещать их в предложения SELECT , INSERN , UPDATE или DELETE или в другой подзапрос. Подробно о них будет рассказано в главе 6. 5.2. Выборка без использования фразы WHERE В этом разделе мы будем рассматривать простейшую форму предложения SELECT : SELECT ALL | DISTINCT}] отбираемый_элемент [[AS] псевдоним] [,...] FROМ имя_таблицы [[AS] псевдоним]; Глава 5. Запросы с использованием единственной таблицы 101 Опишем кратко ключевые слова этого предложения. [{ALL | DISTINCT}] отбираемый_элемент Используется для описания результирующего набора значений, извлекаемых из базы данных при выполнении предложения SELECT Отбираемьй_элемент может представлять собой константу, агрегат или скаляр- ную функцию, математическое выражение, параметры или переменную или же подзапрос, но наиболее часто отбираемый_элемент —это столбец таб- лицы или представления. Несколько таких элементов должны разделяться запятыми. Если данные извлекаются из контекста, отличного от контекста текущего пользователя, перед столбцом нужно указывать префикс в виде схемы или имени владельца. Если таблица принадлежит другому пользователю, то в ссылке на столбец указывать имя этого пользователя. Чтобы извлечь все столбцы таблицы, указанной в предложении FROM , можно использовать крат- кое написание в виде звездочки ( * ). Ключевое слово ALL , заданное по умолчанию, приводит к извлечению всех записей, удовлетворяющих критерию отбора. Ключевое же слово DISTINCT заставляет базу данных отфильтровывать все дублирующиеся записи и воз- вращать экземпляр из нескольких идентичных записей. AS псевдоним заменяет имя столбца более кратким именем. Это предложение особенно полезно для замены непонятных или длинных имен и выражений понятными обозначениями. FROM имя_таблицы [[AS] псевдоним] Это предложение служит для указания таблицы, из которой запрос получает данные. Предложение FROM такжепозволяет назначать псевдонимы длинным именам таблиц. Отметим, что псевдонимы в этой и предыдущей фразе действуют только в пре- делах одного запроса, а также, что слово [AS] можно опустить. Здесь и далее мы будем иллюстрировать результаты выполнения предло- жений SQL на примерах работы с базой данных "COOK", содержимое которой приведено в табл. 3.1—3.10 данной книги. Иногда (для более сложных примеров) мы воспользуемся достаточно содержательной ба- зой данных "UCHEB" (см. главу 19), содержимое которой приведено на компакт-диске, прилагаемом к книге. Последнее будет каждый раз спе- циально оговариваться. Часть II. Язык SQL. Извлечение данных 102 5.2.1. Простая выборка Сформулируем запрос на получения названия, статуса и месторасположения каждого из поставщиков: SELECT название, статус, город FROM поставщики; НАЗВАНИЕ СТАТУС ГОРОД -------- ---------- --------- СЫТНЫЙ рынок Ленинград ПОРТОС кооператив Резекне ШУШАРЫ совхоз Пушкин ТУЛЬСКИЙ универсам Ленинград УРОЖАЙ коопторг Луга ЛЕТО агрофирма Ленинград ОГУРЕЧИК ферма Паневежис КОРЮШКА кооператив Йыхви При необходимости получения полной информации о поставщиках, можно было бы дать запрос SELECT код_поставщика, название, статус, город, адрес, телефон FROM Поставщики; или более краткую нотацию, где вместо перечня всех имен таблицы Постав- щики используется рассмотренный в предыдущем разделе символ ( * ): SELECT * FROM Поставщики; Результат любого из этих запросов имеет вид: КОД_ПОСТАВЩИКА НАЗВАНИЕ СТАТУС ГОРОД АДРЕС ТЕЛЕФОН -------------- -------- ---------- --------- -------------- ------- 1 СЫТНЫЙ рынок Ленинград Сытнинская,3 2329916 2 ПОРТОС кооператив Резекне Садовая,27 317664 3 ШУШАРЫ совхоз Пушкин Новая, 17 4705038 4 ТУЛЬСКИЙ универсам Ленинград Тульский,5 2710837 5 УРОЖАЙ коопторг Луга Песчаная,19 789000 6 ЛЕТО агрофирма Ленинград Пулковская,8 2939729 7 ОГУРЕЧИК ферма Паневежис Укмерге,15 127331 8 КОРЮШКА кооператив Йыхви Нарвское ш.,64 432123 Глава 5. Запросы с использованием единственной таблицы 103 Еще один пример. Выдать основу всех блюд: SELECT Основа FROM Блюда; дает результат, в котором среди десяти первых значений всего лишь четыре уникальных: овощи, мясо, рыба и молоко. ОСНОВА ------ Овощи Мясо Овощи Рыба Рыба Мясо Молоко Молоко Мясо Рыба 5.2.2. Исключение дубликатов В предыдущем примере был выдан правильный, но не совсем удачный пере- чень основных продуктов: из него не были исключены дубликаты. Это мож- но сделать, дополнив запрос ключевым словом DISTINCT (различный, раз- личные), что позволит получить следующий результат: SELECT DISTINCT Основа FROM Блюда; ОСНОВА ------ Кофе Крупа Молоко Мясо Овощи Рыба Фрукты Яйца Здесь всего восемь строк, в которых содержатся все уникальные значения столбца Основа таблицы Блюда Часть II. Язык SQL. Извлечение данных 104 5.2.3. Выборка вычисляемых значений Из описания синтаксиса предложения SELECT известно, что в нем может содер- жаться не только перечень столбцов таблицы или символ * , но и выражения. Например, если нужно получить значение калорийности всех продуктов, то можно учесть, что при окислении 1 г углеводов или белков в организме осво- бождается в среднем 4.1 ккал, а при окислении 1 г жиров — 9.3 ккал, и вы- дать запрос: SELECT Продукт, ((Белки + Углев)*4.1 + Жиры*9.3) FROM Продукты; позволяющий получить следующий результат: ПРОДУКТ (БЕЛКИ+УГЛЕВ)*4.1+ЖИРЫ*9.3 -------- -------------------------- Говядина 1928,1 Судак 1523 Масло 8287,5 Майонез 6464,7 Яйца 1618,9 Сметана 3011,4 Молоко 605,1 Творог 1575 Морковь 349,6 Лук 459,2 Помидоры 196,8 Зелень 118,9 Рис 3512,1 Мука 3556,7 Яблоки 479,7 Сахар 4091,8 Кофе 892,4 Фраза SELECT может включать не только выражения для вычисления число- вых значений, но и текстовые выражения, а также числовые или текстовые константы. В качестве примера дополним предыдущий запрос константой 'Калорий =' , расположенной перед вычисляемым значением, и дадим псевдоним Калор выражению для вычисления калорийности продуктов: SELECT Продукт, 'Калорий =', ((Белки + Углев)*4.1 + Жиры *9.3) Калор FROM Продукты; Глава 5. Запросы с использованием единственной таблицы 105 Результат его выполнения будет иметь вид: ПРОДУКТ 'КАЛОРИЙ=' КАЛОР -------- ---------- ---------- Говядина Калорий = 1928,1 Судак Калорий = 1523 Попробуем теперь создать текстовое выражение, воспользовавшись для этого оператором конкатенации (см. разд. 4.4.4). Создадим набор строк, каждая из которых содержит все данные о поставщике кроме его кода: SELECT статус||' '||название||' - адрес: ' ||город||', '||адрес Поставщики FROM Поставщики; Результат выполнения такого запроса имеет вид: ПОСТАВЩИКИ ------------------------------------------------------ рынок СЫТНЫЙ - адрес: Ленинград, Сытнинская,3 кооператив ПОРТОС - адрес: Резекне, Садовая,27 совхоз ШУШАРЫ - адрес: Пушкин, Новая, 17 универсам ТУЛЬСКИЙ - адрес: Ленинград, Тульский,5 коопторг УРОЖАЙ - адрес: Луга, Песчаная,19 агрофирма ЛЕТО - адрес: Ленинград, Пулковская,8 ферма ОГУРЕЧИК - адрес: Паневежис, Укмерге,15 кооператив КОРЮШКА - адрес: Йыхви, Нарвское ш.,64 Используя разнообразные функции работы со строками (см. разд. 4.6) можно существенно "разукрасить" этот результат. Воспользуемся только одной из них — RPAD , позволяющей дополнить строку справа до указанной длины пробелами (по умолчанию) или любым набором символов: SELECT RPAD(статус||' '||название,20)||' - адрес: ' ||город||', '||адрес Поставщики FROM Поставщики; ПОСТАВЩИКИ ------------------------------------------------------- рынок СЫТНЫЙ - адрес: Ленинград, Сытнинская,3 кооператив ПОРТОС - адрес: Резекне, Садовая,27 совхоз ШУШАРЫ - адрес: Пушкин, Новая, 17 универсам ТУЛЬСКИЙ - адрес: Ленинград, Тульский,5 коопторг УРОЖАЙ - адрес: Луга, Песчаная,19 Часть II. Язык SQL. Извлечение данных 106 агрофирма ЛЕТО - адрес: Ленинград, Пулковская,8 ферма ОГУРЕЧИК - адрес: Паневежис, Укмерге,15 кооператив КОРЮШКА - адрес: Йыхви, Нарвское ш.,64 5.3. Выборка c использованием фразы WHERE Даже в такой микроскопической базе данных, какой является рассматривае- мая нами база данных "COOK", нас часто интересуют только определенные строки таблиц. Для этого в SQL существует фраза WHERE , позволяющая ука- зать критерии для отбора нужных строк. Синтаксис этой фразы имеет вид: WHERE условие_поиска [ {AND | OR | NOT} условие_поиска [...], где условие_поиска одна из следующих конструкций: значение { = | <> | < | <= | > | >= } { значение | (подзапрос) } значение_1 [NOT] BETWEEN значение_2 AND значение_3 значение [NOT] IN { (константа [,константа]...) | (подзапрос) } значение IS [NOT] NULL [таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ'] EXISTS (подзапрос) Кроме традиционных операторов сравнения ( = | <> | < | <= | > | >= ) в WHERE -фразе используются условия BETWEEN (между), LIKE (похоже на), IN (принадлежит), IS NULL (не определено) и EXISTS (существует), которые могут предваряться оператором NOT (не). Критерий отбора строк формируется из одного или нескольких условий, соединенных логическими операторами: AND — когда должны удовлетворяться оба разделяемых с помощью AND условия; OR — когда должно удовлетворяться одно из разделяемых с помощью OR условий; AND NOT — когда должно удовлетворяться первое условие и не должно второе; OR NOT — когда или должно удовлетворяться первое условие или не долж- но удовлетворяться второе. При этом существует приоритет AND над OR (сначала выполняются все опе- рации AND и только после этого операции OR ). Для получения желаемого Глава 5. Запросы с использованием единственной таблицы 107 результата WHERE -условия должны быть введены в правильном порядке, ко- торый можно организовать с помощью скобок. При обработке условия числа сравниваются алгебраически — отрицательные числа считаются меньшими, чем положительные, независимо от их абсолют- ной величины. Строки символов сравниваются в соответствии с их представ- лением в коде, используемом в конкретной СУБД, например, в коде ASCII. Если сравниваются две строки символов, имеющих разные длины, более ко- роткая строка дополняется справа пробелами для того, чтобы они имели оди- наковую длину перед осуществлением сравнения. В заключение отметим, что плохо написанная фраза WHERE может ухудшить производительность полезного во всех прочих отношениях предложения SELECT , поэтому деталями использования фразы WHERE необходимо владеть хорошо. 5.3.1. Использование операторов сравнения В синтаксисе фразы WHERE показано, что для отбора нужных строк таблицы можно использовать операторы сравнения = (равно), <> (не равно), < (мень- ше), <= (меньше или равно), > (больше), >= (больше или равно), которые мо- гут предваряться оператором NOT , создавая, например, отношения "не меньше" и "не больше". Так, для получения перечня продуктов, практически не содержащих углево- дов, можно сформировать запрос SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C FROM Продукты WHERE Углев = 0; и получить: ПРОДУКТ БЕЛКИ ЖИРЫ УГЛЕВ K CA NA B2 PP C -------- ------ ------ ------ ----- ----- ----- ------ ------ ----- Говядина 189,0 124,0 0,0 3150 90 600 1,5 28,0 0 Судак 190,0 80,0 0,0 1870 270 0 1,1 10,0 30 Возможность использования нескольких условий, соединенных логическими операторами AND , OR , AND NOT и OR NOT , позволяет осуществить более де- тальный отбор строк. Так, для получения перечня продуктов, практически не содержащих углеводов и натрия, можно сформировать запрос: SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C FROM Продукты WHERE Углев = 0 AND Na = 0; Часть II. Язык SQL. Извлечение данных 108 Результат запроса имеет вид: ПРОДУКТ БЕЛКИ ЖИРЫ УГЛЕВ K CA NA B2 PP C -------- ------ ------ ------ ----- ----- ----- ------ ------ ----- Судак 190,0 80,0 0,0 1870 270 0 1,1 10,0 30 Добавим к этому запросу еще одно условие: SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C FROM Продукты WHERE Углев = 0 AND Na = 0 AND Продукт <> 'Судак'; и получим на экране сообщение "No rows exist or satisfy the specified clause" или "строки не выбраны" или просто заголовок без строк, в зависимости от вида и настройки того приложения, с помощью которого производится реа- лизация запроса. 5.3.2. Использование BETWEEN С помощью BETWEEN ... AND ... (находится в интервале от ... до ...) можно отобрать строки, в которых значение какого-либо столбца находится в задан- ном диапазоне. Например, выдать перечень продуктов, в которых значение содержания бел- ка находится в диапазоне от 10 до 50: SELECT Продукт, Белки FROM Продукты WHERE Белки BETWEEN 10 AND 50; ПРОДУКТ БЕЛКИ -------- ------ Майонез 31,0 Сметана 26,0 Молоко 28,0 Морковь 13,0 Лук 17,0 Можно задать и NOT BETWEEN (не принадлежит диапазону), например: SELECT Продукт, Белки, Жиры FROM Продукты WHERE Белки NOT BETWEEN 10 AND 50 AND Жиры > 100; Глава 5. Запросы с использованием единственной таблицы 109 ПРОДУКТ БЕЛКИ ЖИРЫ -------- ------ ------ Говядина 189,0 124,0 Масло 60,0 825,0 Яйца 127,0 115,0 BETWEEN особенно удобен при работе с данными, задаваемыми интервалами, начало и конец которых расположены в разных столбцах. Для примера воспользуемся таблицей "минимальных окладов" (табл. 5.1), ве- личина которых непосредственно связана с зарплатами сотрудников универси- тета и другими выплатами. В этой таблице для текущего значения минималь- ного оклада установлена запредельная дата окончания 9 сентября 9999 года. Таблица 5.1. Минимальные оклады Миноклад Начало Конец 90 01.05.1997 31.03.1999 110 01.04.1999 31.12.2000 200 01.01.2001 30.06.2001 300 01.07.2001 30.11.2001 450 01.12.2001 30.09.2003 600 01.10.2003 31.12.2004 720 01.01.2005 31.08.2005 800 01.09.2005 30.04.2006 1100 01.05.2006 30.09.2006 1221 01.10.2006 09.09.9999 Если, например, потребовалось узнать, какие изменения минимальных окла- дов производились в 2000/2001 учебном году, то можно выдать запрос: SELECT Начало, Миноклад FROM Миноклады WHERE Начало BETWEEN '1-9-2000' AND '31-8-2001'; и получить результат: НАЧАЛО МИНОКЛАД ----------- -------- 01.01.2001 200,00 01.07.2001 300,00 Часть II. Язык SQL. Извлечение данных 110 Отметим, что при формировании запросов значения дат следует заключать в апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычи- тать из 31 значение 8, а затем 2001. Для выявления всех значений минимальных окладов, которые существовали в 2000/2001 учебном году, можно сформировать запрос: SELECT * FROM Миноклады WHERE Начало BETWEEN '1-9-2000' AND '31-8-2001' OR Конец BETWEEN '1-9-2000' AND '31-8-2001' получив: МИНОКЛАД НАЧАЛО КОНЕЦ -------- ---------- ---------- 110,00 01.04.1999 31.12.2000 200,00 01.01.2001 30.06.2001 300,00 01.07.2001 30.11.2001 Наконец, для определения минимального оклада на 15-5-2001 можно дать запрос: SELECT Миноклад FROM Миноклады WHERE '15-5-2001' BETWEEN Начало AND Конец; получив: МИНОКЛАД -------- 300,00 5.3.3. Использование IN Выдать сведения о блюдах на основе яиц, крупы и овощей: SELECT * FROM Блюда WHERE Основа IN ('Яйца', 'Крупа', 'Овощи'); Результат: КОД_БЛЮДА БЛЮДО КОД_ВИДА ОСНОВА ВЫХОД ТРУД --------- ---------------- -------- ------ ------ ---- 1 Салат летний 1 Овощи 200,0 3 3 Салат витаминный 1 Овощи 200,0 4 16 Драчена 3 Яйца 180,0 4 Глава 5. Запросы с использованием единственной таблицы 111 17 Морковь с рисом 3 Овощи 260,0 3 19 Омлет с луком 3 Яйца 200,0 5 20 Каша рисовая 3 Крупа 210,0 4 21 Пудинг рисовый 3 Крупа 160,0 6 23 Помидоры с луком 3 Овощи 260,0 4 Рассмотренная форма IN является в действительности просто краткой запи- сью последовательности отдельных сравнений, соединенных операторами OR Предыдущее предложение эквивалентно такому: SELECT * FROM Блюда WHERE Основа = 'Яйца' OR Основа = 'Крупа' OR Основа = 'Овощи'; Можно задать и NOT IN (не принадлежит), также есть возможность использо- вания IN ( NOT IN ) с подзапросом (см. главу 6). 5.3.4. Использование LIKE Оператор LIKE позволяет указывать строковый шаблон для проверки на сов- падение в предложениях SELECT , INSERT , UPDATE и DELETE . Строковый шаблон может включать обобщающие символы. И в стандарте и в Oracle поддержи- ваются два обобщающих символа: символ _ (подчеркивание) заменяет любой одиночный символ; символ % (процент) заменяет любую последовательность из N символов (где N может быть нулем). Существует также ключевое слово ESCAPE (отменяющая_посдедовательность) , позволяющее включать в шаблон символы, которые в обычных условиях ин- терпретировались бы как обобщающие символы. Любой обобщающий сим- вол, перед которым стоит отменяющая_посдедовательность , не считается обобщающим, а считается обычным символом. При организации проверки по строковым шаблонам во фразе LIKE следует помнить: значимыми являются все символы, включая пробелы перед строкой и по- сле нее; с помощью фразы LIKE можно сравнивать разные типы данных, но строки в них хранятся по-разному. Так, надо представлять различия между типа- ми данных CHAR , VARCHAR и DATE (см. разд. 4.5); использование фразы LIKE может заставить пользователя СУБД отказать- ся от употребления индексов или применять альтернативные, менее опти- мальные индексы, чем в простой операции сравнения. Часть II. Язык SQL. Извлечение данных 112 Синтаксис фразы LIKE в Oracle имеет вид: WHERE выражение [NOT] {LIKE | LIKEC | LIKE2 | LIKE4 } строковый_шаблон [ESCAPE отменяющая_посдедовательность ] где LIKEC использует полный набор символов UNICODE, LIKE2 использует набор символов UNICODE USC2, а LIKE4 использует набор символов UNICODE USC4. Поскольку Oracle учитывает регистр, следует включать строковый_шаблон , выражение или и то и другое в функцию UPPER (изменение регистра строки на верхний). В этом случае вы всегда будете сравнивать то, что нужно. Перейдем к примерам. Выдать перечень салатов: SELECT Блюдо FROM Блюда WHERE Блюдо LIKE 'Салат%'; Результат выборки имеет вид: БЛЮДО ---------------- Салат летний Салат мясной Салат витаминный Салат рыбный В этом примере SELECT осуществляет выборку тех строк таблицы Блюда , для которых значение в столбце Блюдо начинается сочетанием "Салат" и содер- жит любую последовательность из нуля или более символов, следующих за этим сочетанием. Если бы среди блюд были "Луковый салат", "Фруктовый салат" и т. п., то они не были бы найдены. Для их отыскания надо изменить фразу WHERE : WHERE Блюдо LIKE '%салат%' или исключить возможные различия между малыми и большими буквами: WHERE UPPER (Блюдо) LIKE UPPER ('%Салат%') Это позволит отыскать все салаты. 5.4. Выборка с упорядочением ( ORDER BY) Синтаксис фразы упорядочения имеет вид: ORDER BY { выражение для сортировки [ASC | DESC ] } [,...] В выражении для сортировки указывается тот элемент запроса, который бу- дет определять порядок данных в результирующем наборе. Обычно это имена Глава 5. Запросы с использованием единственной таблицы 113 или псевдонимы столбцов. Указывается также, что выражение для сортиров- ки должно возвращаться в восходящем ( ASC ) или нисходящем ( DESC ) порядке. По умолчанию используется восходящий порядок: от ранней даты к поздней, от младшего числового значения к старшему, от текстового значения, начи- нающегося на символ с наименьшим кодом ASCII, к символу с наибольшим кодом ASCII (для русских букв от "А" до "Я" и от "а" до "я"). Можно задавать несколько выражений для сортировки. При этом используется порядок сортировки от главного к второстепенному: сначала результирующий набор сортируется по первому из указанных столбцов, затем одинаковые зна- чения первого столбца сортируются по второму столбцу, одинаковые значе- ния второго столбца — по третьему столбцу и т. д. Индивидуальные параметры сортировки столбца ( ASC / DESC ) не зависят от других столбцов фразы ORDER BY , т. е. можно отсортировать результирующий набор по одному столбцу по возрастанию, по следующему — по убыванию и т. п. Пустые значения ( NULL ) при сортировке всегда оказываются рядом (т. е. счи- таются одинаковыми). В Oracle по умолчанию ( ASC ) они собираются внизу. При помощи DESC можно переместить их вверх. Наконец, в Oracle поддерживается выведенная из стандарта сортировка по номеру порядковой позиции столбца (псевдонима, выражения или подзапро- са) в списке SELECT Перейдем к примерам. Выдать перечень продуктов, содержание в них основных веществ в порядке убывания содержания белка и возрастания их калорийности: SELECT продукт, белки, жиры, углев, (Белки + Углев)*4.1 + Жиры*9.3 Калорий FROM продукты ORDER BY Белки DESC, Калорий ASC; Результат выполнения этого запроса имеет вид: ПРОДУКТ БЕЛКИ ЖИРЫ УГЛЕВ КАЛОРИЙ -------- ------ ------ ------ ---------- Судак 190,0 80,0 0,0 1523 Говядина 189,0 124,0 0,0 1928,1 Творог 167,0 90,0 13,0 1575 Кофе 127,0 36,0 9,0 892,4 Яйца 127,0 115,0 7,0 1618,9 Мука 106,0 13,0 732,0 3556,7 Рис 70,0 6,0 773,0 3512,1 Часть II. Язык SQL. Извлечение данных 114 Масло 60,0 825,0 90,0 8287,5 Майонез 31,0 670,0 26,0 6464,7 Молоко 28,0 32,0 47,0 605,1 Сметана 26,0 300,0 28,0 3011,4 Лук 17,0 0,0 95,0 459,2 Морковь 13,0 1,0 70,0 349,6 Зелень 9,0 0,0 20,0 118,9 Помидоры 6,0 0,0 42,0 196,8 Яблоки 4,0 0,0 113,0 479,7 Сахар 0,0 0,0 998,0 4091,8 Для еще одного примера воспользуемся базой данных "UCHEB". Реализуем запрос SELECT ид, фамилия, имя, отчество, дата_рождения FROM н_люди WHERE фамилия = 'Громов' ORDER BY имя, отчество, дата_рождения DESC; в котором отыскиваются все люди с фамилией Громов, результат сортирует- ся по имени и отчеству, а при одинаковых значениях имени и отчества, по дате рождения (от более поздней к более ранней). ИД ФАМИЛИЯ ИМЯ ОТЧЕСТВО ДАТА_РОЖДЕНИЯ ------ -------- --------- ------------- ------------- 125518 Громов Александр Анатольевич 18.05.1960 123040 Громов Александр Вадимович 19.09.1958 102709 Громов Александр Викторович 23.12.1961 142506 Громов Александр Дмитриевич 15.04.1986 129531 Громов Александр Олегович 26.10.1985 117245 Громов Андрей Викторович 24.03.1981 133898 Громов Андрей Владимирович 25.09.1986 122012 Громов Андрей Геннадьевич 18.10.1984 143792 Громов Валерий Александрович 13.06.1988 119218 Громов Валерий Геннадьевич 02.07.1983 100061 Громов Геннадий Юрьевич 29.05.1962 132910 Громов Дмитрий Александрович 17.04.1987 145639 Громов Дмитрий Евгеньевич 24.12.1989 133899 Громов Дмитрий Сергеевич 17.06.1987 126697 Громов Илья Сергеевич 23.03.1984 122240 Громов Илья Сергеевич 01.01.1984 142202 Громов Роман Геннадьевич 02.12.1988 Глава 5. Запросы с использованием единственной таблицы 115 5.5. Агрегирование данных Многие запросы к базе данных не нуждаются в той степени детализации, ко- торую обеспечивают ранее рассмотренные SQL-запросы. Например, во всех запросах, перечисленных далее, требуется узнать всего одно или несколько значений, которые подытоживают информацию, содержащуюся в базе данных. Сколько поставщиков поставляет морковь? Сколько людей заказало "Салат летний" на завтра? Какова средняя трудоемкость приготовления блюд, включенных в меню? Какова средняя стоимость заказанных блюд на каждом месте в столовой? Какова средняя стоимость всех заказанных блюд? Сколько строк в таблице Состав ? Какие фамилии чаще всего встречаются в таблице н_люди ? Сколько человек имеют имя Александр? Сколько неуспевающих студентов в каждой из групп? Сколько неуспевающих студентов на каждом факультете? Сколько неуспевающих студентов в университете? SQL-запросы такого типа можно создавать с помощью агрегатных функций и фраз GROUP BY и HAVING 5.5.1 Агрегатные SQL - функции В SQL существует ряд специальных стандартных функций (агрегатных SQL- функций). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так: COUNT — число значений в столбце; SUM — сумма значений в столбце; AVG — среднее значение в столбце; MIN — самое малое значение в столбце; MAX — самое большое значение в столбце. Для функций SUM и AVG рассматриваемый столбец должен содержать число- вые значения. Часть II. Язык SQL. Извлечение данных 116 Следует отметить, что здесь столбец — это столбец виртуальной таблицы, в которой могут содержаться данные не только из столбца базовой таблицы, но и данные, полученные путем функционального преобразования и (или) связывания символами арифметических операций значений из одного или нескольких столбцов. При этом выражение, определяющее столбец такой таблицы, может быть сколь угодно сложным, но не должно содержать агре- гатных SQL-функций (вложенность таких функций не допускается). Однако из агрегатных SQL-функций можно составлять любые выражения. Аргументу всех функций, кроме COUNT(*) , может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функ- ция. Специальная же функция COUNT(*) служит для подсчета всех без исклю- чения строк в таблице (включая дубликаты). 5.5.2. Функции без использования фразы GROUP BY Если не используется фраза GROUP BY , то в перечень отбираемых_элементов SELECT можно включать лишь агрегатные SQL-функции или выражения, со- держащие такие функции. Другими словами, нельзя иметь в списке столбцы, не являющиеся аргументами агрегатных SQL-функций. Например, выдать данные о массе лука ( Код_продукта=10 ), проданного по- ставщиками, и указать количество этих поставщиков: SELECT SUM(К_во), COUNT(К_во) FROM Поставки WHERE Код_продукта=10; Результат будет выглядеть так: SUM(К_ВО) COUNT(К_ВО) --------- ----------- 220 2 Если бы для вывода в результат еще и номера продукта был сформирован запрос SELECT Код_продукта, SUM(К_во), COUNT(К_во) FROM Поставки WHERE Код_продукта=10; то было бы получено сообщение об ошибке (на Oracle — "ORA-00937: груп- повая функция не является одногруппной"). Это связано с тем, что агрегатная Глава 5. Запросы с использованием единственной таблицы 117 SQL-функция создает единственное значение из множества значений столбца- аргумента, а для "свободного" столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY ) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где Код_продукта=10 ) или различны (как было бы при отсутствии фразы WHERE ). Поэтому подобный запрос отвергается системой. Правда, никто не запрещает дать запрос: SELECT 'Кол-во лука =', SUM(К_во), COUNT(К_во) FROM Поставки WHERE Код_продукта=10; и получить: 'КОЛ-ВО ЛУКА=' SUM(К_ВО) COUNT(К_ВО) -------------- --------- ----------- Кол-во лука = 220 2 Однако если нам все же хочется увидеть в результате (а не только в тексте запроса) значение кода продукта, то можно дать такой "смешной" запрос: SELECT MAX(Код_продукта), SUM(К_во), COUNT(К_во) FROM Поставки WHERE Код_продукта=10; основанный на том, что при формировании списка вывода в него попадут строки с кодом продукта, разным 10, и, следовательно, максимальное (или минимальное) значение этого кода будет равно 10: MAX(КОД_ПРОДУКТА) SUM(К_ВО) COUNT(К_ВО) ----------------- ---------- ----------- 10 220 2 Отметим также, что в столбце-аргументе перед применением любой функ- ции, кроме COUNT(*) , исключаются все неопределенные значения. Если ока- зывается, что аргумент — пустое множество, функция COUNT принимает зна- чение 0, а остальные — NULL Например, для получения суммы цен, средней цены, количества поставляе- мых продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ ( Код_поставщика=5 ), а также для получения количества продуктов, которые могут поставляться этим коопторгом, можно дать запрос: SELECT SUM(Цена), AVG(Цена), COUNT(Цена), COUNT(DISTINCT Цена), COUNT(*) FROM Поставки WHERE Код_поставщика=5; Часть II. Язык SQL. Извлечение данных 118 и получить: SUM(ЦЕНА) AVG(ЦЕНА) COUNT(ЦЕНА) COUNT(DISTINCTЦЕНА) COUNT(*) ---------- ---------- ----------- ------------------- ---------- 6,2 1,24 5 4 5 То в другом примере, где надо узнать "Сколько поставлено судака и сколько поставщиков его поставляют?": SELECT SUM(К_во), COUNT(К_во) FROM Поставки WHERE Код_продукта=2; будет получен ответ: SUM(К_ВО) COUNT(К_ВО) --------- ----------- 0 Так как ни один из поставщиков не поставляет судака, то в этом результате COUNT(К_ВО) оказалось равным нулю, а SUM(К_ВО) — неопределенному зна- чению ( NULL ), которое ни в стандарте, ни в Oracle не имеет видимого изобра- жения. Наконец, попробуем получить сумму массы поставленного лука с его сред- ней ценой: SELECT (SUM(К_во) + AVG(Цена)) FROM Поставки WHERE Код_продукта=10; Результат (SUM(К_ВО)+AVG(ЦЕНА)) --------------------- 220,6 Система с легкостью складывает два запрошенных числовых значения ("са- поги с яичницей"), не задумываясь о несуразности такого действия. Об этом должен заботиться пользователь, формирующий запрос. В завершение данного раздела сформируем запрос на получение статисти- ческих данных о людях, хранящихся в таблице Н_ЛЮДИ базы данных "UCHEBA": SELECT COUNT(*) людей, COUNT(DISTINCT фамилия) фамилий, COUNT(DISTINCT имя) имен, COUNT(DISTINCT отчество) отчеств FROM н_люди; Глава 5. Запросы с использованием единственной таблицы 119 при выполнении которого будет строка ЛЮДЕЙ ФАМИЛИЙ ИМЕН ОТЧЕСТВ ----- ------- ---- ------- 30331 14524 868 1296 содержащая общее число людей, хранящихся в таблице, а также число раз- личных фамилий, имен и отчеств у этих людей. 5.5.3. Фраза GROUP BY Мы показали, как можно вычислить массу определенного продукта, постав- ляемого поставщиками. Предположим, что теперь требуется вычислить об- щую массу каждого из продуктов, поставляемых в настоящее время постав- щиками. Это можно легко сделать с помощью предложения: SELECT Код_продукта, SUM(К_во) FROM Поставки GROUP BY Код_продукта; Его результат имеет вид: КОД_ПРОДУКТА SUM(К_ВО) ------------ ---------- 1 370 3 250 4 100 5 170 6 220 7 200 8 150 10 220 11 150 12 30 13 190 14 70 15 370 16 250 17 50 Фраза GROUP BY (группировать по) инициирует перекомпоновку строк табли- цы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY . В рассматриваемом примере строки таблицы Поставки группируются так, что в одной группе содержатся все строки для продукта Часть II. Язык SQL. Извлечение данных 120 с Код_продукта=1 , в другой для продукта с Код_продукта=3 (строки с Код_ продукта=2 нет, так как судак не поставляется ни одним из поставщиков) и т. д. Затем к каждой группе применяется фраза SELECT . Каждое выражение в этой фразе должно принимать единственное значение для группы, т. е. оно может быть либо: значением столбца, указанного в GROUP BY ; арифметическим выражением, включающим это значение; константой; одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме, как в нашем запросе). Определим теперь среднюю стоимость одного килограмма каждого из про- дуктов, находящихся в таблице поставки, их количество и среднюю цену этих продуктов: SELECT Код_продукта, ROUND(SUM(цена*к_во)/SUM(К_во),2) Средняя, SUM(К_во) Всего, AVG(Цена) FROM Поставки GROUP BY Код_продукта; Результат имеет вид: КОД_ПРОДУКТА СРЕДНЯЯ ВСЕГО AVG(ЦЕНА) ------------ ---------- ---------- ---------- 1 3,71 370 3,9 3 4 250 4 4 2,52 100 2,52 5 1,88 170 1,9 6 2,71 220 2,9 7 0,4 200 0,4 8 1 150 1 10 0,58 220 0,6 11 1,17 150 1,25 12 2,67 30 2,75 13 0,95 190 1,04 14 0,5 70 0,5 15 1,73 370 1,75 16 0,95 250 0,97 17 4,5 50 4,5 Для того чтобы средняя стоимость определялась с точностью до копеек, в запросе использовалась функция ROUND(n,[m]) , в которой n — округляемое Глава 5. Запросы с использованием единственной таблицы 121 числовое значение, а m — число десятичных знаков после запятой (точнее см. в разд. 4.6). Обратите внимание, что ряд значений средней стоимости не совпадает со средней ценой! Отметим также, что фраза GROUP BY не предполагает ORDER BY (хотя в рас- смотренных ранее примерах результаты упорядочены). Чтобы гарантировать упорядочение по Код_продукта результата рассматриваемого примера, следу- ет дать запрос: SELECT код_продукта, SUM(цена*к_во)/SUM(к_во) Средняя, AVG(цена) FROM поставки GROUP BY код_продукта; ORDER BY Код_продукта; Наконец, отметим, что строки таблицы можно группировать по любой ком- бинации ее столбцов. Пример такой группировки будет рассмотрен в сле- дующем разделе. 5.5.4. Использование фразы HAVING Фраза HAVING играет такую же роль для групп, что и фраза WHERE для строк: она используется для исключения групп точно так же, как WHERE использует- ся для исключения строк. Эта фраза включается в предложение лишь при наличии фразы GROUP BY , а выражение в HAVING должно принимать единст- венное значение для группы. Например, выдать количество людей с совпадающими фамилиями, именами и отчествами. В список включить только тех, которые имеют более трех "полных тезок". SELECT фамилия, имя, отчество, COUNT(*) FROM н_люди GROUP BY фамилия, имя, отчество HAVING COUNT(*) > 3 ORDER BY фамилия, имя, отчество; Оказалось, что в таблице Н_ЛЮДИ базы данных "UCHEBA" имеется девять групп лиц, имеющих более трех "полных тезок", а Смирновых Андреев Сер- геевичей — аж восемь штук. ФАМИЛИЯ ИМЯ ОТЧЕСТВО COUNT(*) --------- --------- ------------- -------- Иванов Дмитрий Николаевич 4 Иванов Дмитрий Сергеевич 4 Часть II. Язык SQL. Извлечение данных 122 Иванов Сергей Александрович 5 Кузнецов Сергей Владимирович 4 Кузнецова Мария Александровна 4 Михайлов Андрей Александрович 5 Смирнов Александр Сергеевич 8 Смирнов Сергей Владимирович 4 Федоров Алексей Владимирович 4 В разд. 6.5 можно познакомиться с более содержательным примером исполь- зования этой фразы. 5.6. Иерархические запросы Если информация в таблице представлена, например, в виде генеалогическо- го дерева (родственные связи в виде дерева, в котором у корня расположен родоначальник, а на ветвях дерева — различные линии его потомков), то хо- телось бы иметь возможность получать ее наглядное представление доста- точно простым способом. В синтаксисе предложения SELECT (см. разд. 5.1) существует фраза: [[ START WITH условие ]CONNECT BY [PRIOR] условие ] где START WITH задает строки, которые будут выполнять в результирующем наборе poль родительских (корневых). Условие в этой фразе не должно со- держать подзапросов. Если фраза START WITH не задана, то все строки будут корневыми. CONNECT BY определяет условие соотношения между родительскими строка- ми и их строками-потомками (дочерними строками). Условие в этой фразе не должно содержать подзапросов. PRIOR используется для указания родительских строк вместо строк-потомков. В иерархических запросах псевдостолбец LEVEL используется для указания корневой точки (1), точек-первичных потомков (2), точек-вторичных потом- ков (3) и т. д. В этих запросах нельзя использовать фразы ORDER BY и GROUP BY .Длясорти- ровки одноранговых потомков одной родительской таблицы можно исполь- зовать фразу ORDER SIBLINGS BY Для примера создадим иерархический запрос для таблицы Н_ОТДЕЛЫ , описан- ной в разд. 2.2 (см. табл. 2.1). SELECT RPAD(' ', (LEVEL-1)*4)||TO_CHAR(o.ид)||' '|| o.имя_в_имин_падеже Отделы Глава 5. Запросы с использованием единственной таблицы 123 FROM н_отделы o START WITH o.отд_ид IS NULL CONNECT BY PRIOR o.ид = o.отд_ид ORDER SIBLINGS BY o.ид; Результат этого запроса будет иметь вид: ОТДЕЛЫ ------------------------------------------------------------------------- 777 Санкт-Петербургский государственный университет информационных техно- логий, механики и оптики 701 факультет оптико-информационных систем и технологий 201 кафедра оптико-электронных приборов и систем 215 кафедра экологического приборостроения и мониторинга 304 кафедра прикладной и компьютерной оптики 305 кафедра информ.-измерительн. cистем оптического приборостр-я 307 кафедра оптических технологий 308 кафедра компьютеризации и проектирования оптических приборов 467 межкафедральная лаборатория компьютерной оптики ОФ 512 кафедра системотехники оптических приб. и комплексов 515 ВКЦГОИ 601 деканат факультета оптико-информационных систем и технологий 702 факультет инженерно-физический 202 кафедра электроники 203 кафедра лазерной техники и биомедицинской оптики 205 кафедра компьютерной теплофизики и энергофизич. мониторинга 207 кафедра физики и техники оптической связи 211 кафедра твердотельной оптоэлектроники 216 кафедра мощных технологических лазеров 310 кафедра лазерных технологий и экологического приборостроения 440 межкафедральный вычислительный класс ИФФ 602 деканат инженерно-физического факультета 703 факультет компьютерных технологий и управления 101 кафедра систем управления и информатики 102 кафедра вычислительной техники 108 кафедра проектирования компьютерных систем 109 кафедра информационно-навигационных систем 111 кафедра информатики и прикладной математики 116 кафедра безопасных информационных технологий 208 кафедра электротехники и прецизионных эл/механических систем Часть II. Язык SQL. Извлечение данных 124 511 кафедра МПБЭВА 603 деканат факультета компьютерных технологий и управления 704 факультет естественнонаучный 106 кафедра теоретической и прикладной механики 209 кафедра высшей математики 210 кафедра физики 217 кафедра технологии профессионального обучения 218 кафедра математического моделирования 468 межкафедральный компьютерный класс ЕНФ 604 деканат естественнонаучного факультета 705 факультет точной механики и технологий 103 кафедра измерительных технологий и компьютерной томографии |