Создание запросов в СУБД Access

Запрос — объект базы данных, используемый для выборки или модификации хранимых данных. В режиме конструктора можно открывать различные запросы: запрос на выборку, перекрестный запрос и запрос на изменение. Запрос на выборку и перекрестный запрос также можно открыть в режиме таблицы для просмотра результатов.
Запросы на выборку и их использование Запрос на выборку является наиболее часто используемым типом запроса. Запросы этого типа выбирает данные из одной или нескольких таблиц и отображают их в виде таблицы, записи в которой можно обновлять (с некоторыми ограничениями). Запросы на выборку можно также использовать для группировки записей и вычисления сумм, средних значений, подсчета записей и нахождения других типов итоговых значений. Для подготовки запросов используются: l QBE (Query By Example) — язык запросов по образцам, l SQL (Structured Query Language) — язык структурированных запросов. SQL Язык структурированных запросов SQL является наиболее распространенным языком управления базами данных клиент/сервер. содержит операторы: - описания данных (DDL — Data Definition Language). Основные операторы DDL — Create Domain (Создание домена), Alter Domain (Изменение домена), Drop Domain (Уничтожение домена), Create Table, Alter Table, Table Drop; - управления данными (DML — Data Manipulation Language). Основные операторы DML — Select (Выбор), Insert (Вставка), Update (Обновление), Delete (Удаление); - формирования запросов. Основой SQL является инструкция SELECT, используемая для создания запросов на выборку. Синтаксис инструкции: SELECT [ ALL │ DISTINCT │DISTINCTROW ] список_выбора FROM имена таблиц [WHERE критерий поиска] [GROUP BY имя столбца, имя столбца,…] [ HAVING условие поиска] [ ORDER BY критерий столбца [ASC│DESC]]; SELECT — выбрать (директива) данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями FROM — из (условие) перечисленных таблиц, в которых расположены эти столбцы WHERE — где (условие) строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк GROUP BY — группируя по (условие) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя во фразе SELECT SQL – функции: SUM (сумма), COUNT (количество), MIN (минимум), MAX (максимум), AVG (среднее значение) HAVING — имея в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп (условие) ORDER BY — спецификация сортировки (условие) определяет порядок сортировки: ASC – сортировка по возрастанию, DESC - сортировка по убыванию. ПРЕДИКАТЫ : 1. Сравнения =, , >=, 2. В интервале - “между” BETWEEN a1 and a2 3. Входит в множество IN (=[Товар] IN (“Мука”,“Крупа”… ) 4. Подобие Like ( ( что ) ( с чем сравнивать) Глава 2. Запросы с использованием единственной таблицы 2.1. О предложении SELECT Все запросы на получение практически любого количества данных из одной или нескольких таблиц выполняются с помощью единственного предложения SELECT. В общем случае результатом реализации предложения SELECT является другая таблица (см. примеры п.1.3). К этой новой (рабочей) таблице может быть снова применена операция SELECT и т.д., т.е. такие операции могут быть вложены друг в друга. Представляет исторический интерес тот факт, что именно возможность включения одного предложения SELECT внутрь другого послужила мотивировкой использования прилагательного "структуризированный" в названии языка SQL. Предложение SELECT может использоваться как: самостоятельная команда на получение и вывод строк таблицы, сформированной из столбцов и строк одной или нескольких таблиц (представлений); элемент WHERE- или HAVING-условия (сокращенный вариант предложения, называемый "вложенный запрос"); фраза выбора в командах CREAT VIEW, DECLARE CURSOR или INSERT; средство присвоения глобальным переменным значений из строк сформированной таблицы (INTO-фраза). В данной и следующей главах будут рассмотрены только две первые функции предложения SELECT, а здесь – его синтаксис, ограниченный конструкциями, используемыми при реализации этих функций. Здесь (так же как и в других главах книги) в синтаксических конструкциях используются следующие обозначения: звездочка (*) для обозначения "все" - употребляется в обычном для программирования смысле, т.е. "все случаи, удовлетворяющие определению"; квадратные скобки ([]) – означают, что конструкции, заключенные в эти скобки, являются необязательными (т.е. могут быть опущены); фигурные скобки ({}) – означают, что конструкции, заключенные в эти скобки, должны рассматриваться как целые синтаксические единицы, т.е. они позволяют уточнить порядок разбора синтаксических конструкций, заменяя обычные скобки, используемые в синтаксисе SQL; многоточие ( .) – указывает на то, что непосредственно предшествующая ему синтаксическая единица факультативно может повторяться один или более раз; прямая черта (|) – означает наличие выбора из двух или более возможностей. Например обозначение ASC|DESC указывает, можно выбрать один из терминов ASC или DESC; когда же один из элементов выбора заключен в квадратные скобки, то это означает, что он выбирается по умолчанию (так, [ASC]|DESC означает, что отсутствие всей этой конструкции будет восприниматься как выбор ASC); точка с запятой (;) – завершающий элемент предложений SQL; запятая (,) – используется для разделения элементов списков; пробелы ( ) – могут вводиться для повышения наглядности между любыми синтаксическими конструкциями предложений SQL; прописные жирные латинские буквы и символы – используются для написания конструкций языка SQL и должны (если это специально не оговорено) записываться в точности так, как показано; строчные буквы – используются для написания конструкций, которые должны заменяться конкретными значениями, выбранными пользователем, причем для определенности отдельные слова этих конструкций связываются между собой символом подчеркивания (_); термины таблица, столбец, . – заменяют (с целью сокращения текста синтаксических конструкций) термины имя_таблицы, имя_столбца, ., соответственно; термин таблица – используется для обобщения таких видов таблиц, как базовая_таблица, представление или псевдоним; здесь псевдоним служит для временного (на момент выполнения запроса) переименования и (или) создания рабочей копии базовой_таблицы (представления). Предложение SELECT (выбрать) имеет следующий формат: подзапрос [UNION [ALL] подзапрос] . [ORDER BY {[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC] [,{[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC]] .; и позволяет объединить (UNION) а затем упорядочить (ORDER BY) результаты выбора данных, полученных с помощью нескольких "подзапросов". При этом упорядочение можно производить в порядке возрастания - ASC (ASCending) или убывания DESC (DESCending), а по умолчанию принимается ASC.
В этом предложении подзапрос позволяет указать условия для выбора нужных данных и (если требуется) их обработки SELECT (выбрать) данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями
FROM (из) перечисленных таблиц, в которых расположены эти столбцы WHERE (где) строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк GROUP BY (группируя по) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя во фразе SELECT SQL-функции SUM (сумма), COUNT (количество), MIN (минимальное значение), MAX (максимальное значение) или AVG (среднее значение) HAVING (имея) в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп и имеет формат SELECT [[ALL] | DISTINCT]{ * | элемент_SELECT [,элемент_SELECT] .} FROM {базовая_таблица | представление} [псевдоним] [,{базовая_таблица | представление} [псевдоним]] . [WHERE фраза] [GROUP BY фраза [HAVING фраза]]; Элемент_SELECT - это одна из следующих конструкций: [таблица.]* | значение | SQL_функция | системная_переменная где значение – это: [таблица.]столбец | (выражение) | константа | переменная Синтаксис выражений имеет вид ( {[ [+] | - ] {значение | функция_СУБД} [ + | - | * | ** ]} . ) а синтаксис SQL_функций – одна из следующих конструкций: {SUM|AVG|MIN|MAX|COUNT} ( [[ALL]|DISTINCT][таблица.]столбец ) {SUM|AVG|MIN|MAX|COUNT} ( [ALL] выражение ) COUNT(*) Фраза WHERE включает набор условий для отбора строк: WHERE [NOT] WHERE_условие [[AND|OR][NOT] WHERE_условие] . где WHERE_условие – одна из следующих конструкций: значение { = | | | >= } { значение | ( подзапрос ) } значение_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). Для получения желаемого результата WHERE условия должны быть введены в правильном порядке, который можно организовать введением скобок. При обработке условия числа сравниваются алгебраически - отрицательные числа считаются меньшими, чем положительные, независимо от их абсолютной величины. Строки символов сравниваются в соответствии с их представлением в коде, используемом в конкретной СУБД, например, в коде ASCII. Если сравниваются две строки символов, имеющих разные длины, более короткая строка дополняется справа пробелами для того, чтобы они имели одинаковую длину перед осуществлением сравнения. Наконец, синтаксис фразы GROUP BY имеет вид GROUP BY [таблица.]столбец [,[таблица.]столбец] . [HAVING фраза] GROUP BY инициирует перекомпоновку формируемой таблицы по группам, каждая из которых имеет одинаковое значение в столб-цах, включенных в перечень GROUP BY. Далее к этим группам применяются агрегирующие функции, указанные во фразе SELECT, что приводит к замене всех значений группы на единственное значение (сумма, количество и т.п.). С помощью фразы HAVING (синтаксис которой почти не отличается от синтаксиса фразы WHERE) HAVING [NOT] HAVING_условие [[AND|OR][NOT] HAVING_условие] . можно исключить из результата группы, не удовлетворяющие заданным условиям: значение { = | | | >= } { значение | ( подзапрос ) | SQL_функция } {значение_1 | SQL_функция_1} [NOT] BETWEEN {значение_2 | SQL_функция_2} AND {значение_3 | SQL_функция_3} {значение | SQL_функция} [NOT] IN { ( константа [,константа] . ) | ( подзапрос ) } {значение | SQL_функция} IS [NOT] NULL [таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ'] EXISTS ( подзапрос ) 2.2. Выборка без использования фразы WHERE 2.2.1. Простая выборка Запрос выдать название, статус и адрес поставщиков SELECT Название, Статус, АдресFROM Поставщики; дает результат, приведенный на рис. 2.1,а. При необходимости получения полной информации о поставщиках, можно было бы дать запрос SELECT ПС, Название, Статус, Город, Адрес, ТелефонFROM Поставщики; или использовать его более короткую нотацию: SELECT *FROM Поставщики; Здесь "звездочка" (*) служит кратким обозначением всех имен полей в таблице, указанной во фразе FROM. При этом порядок вывода полей соответствует порядку, в котором эти поля определялись при создании таблицы. Еще один пример. Выдать основу всех блюд: SELECT ОсноваFROM Блюда; дает результат, показанный на рис. 2.1,б. а) б) в)
Название Статус Адрес СЫТНЫЙ рынок Сытнинская, 3 ПОРТОС кооператив Садовая, 27 ШУШАРЫ совхоз Новая, 17 ТУЛЬСКИЙ универсам Тульская, 3 УРОЖАЙ коопторг Песчаная, 19 ЛЕТО агрофирма Пулковское ш.,8 ОГУРЕЧИК ферма Укмерге, 15 КОРЮШКА кооператив Нарвское ш., 64
Основа Овощи Мясо Овощи Рыба Рыба Мясо Молоко Молоко . Кофе
Основа Кофе Крупа Молоко Мясо Овощи Рыба Фрукты Яйца Рис. 2.1. Примеры простой выборки 2.2.2. Исключение дубликатов В предыдущем примере был выдан правильный, но не совсем удачный перечень основных продуктов: из него не были исключены дубликаты. Для исключения дубликатов и одновременного упорядочения перечня необходимо дополнить запрос ключевым словом DISTINCT (различный, различные), как показано в следующем примере: SELECT DISTINCT ОсноваFROM Блюда;
Результат приведен на рис. 2.1,в. 2.2.3. Выборка вычисляемых значений Из синтаксиса фразы SELECT (п.2.1) видно, что в ней может содержаться не только перечень столбцов таблицы или символ *, но и выражения. Например, если нужно получить значение калорийности всех продуктов, то можно учесть, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров - 9.3 ккал, и выдать запрос: SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)FROM Продукты; результат которого приведен на рис. 2.2,а. а) б) в)
Продукт Говядина 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
Продукт Говядина Калорий = 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
Продукт Зелень 118.9 Помидоры 196.8 Морковь 349.6 Лук 459.2 Яблоки 479.7 Молоко 605.1 Кофе 892.4 Судак 1523. Творог 1575. Яйца 1618.9 Говядина 1928.1 Сметана 3011.4 Рис 3512.1 Мука 3556.7 Сахар 4091.8 Майонез 6464.7 Масло 8287.5 Рис. 2.2. Примеры запросов с вычисляемыми полями Фраза SELECT может включать не только выражения, но и отдельные числовые или текстовые константы. Следует отметить, что текстовые константы должны заключаться в апострофы ('). На рис. 2.2,б приведен результат запроса: SELECT Продукт, 'Калорий =', ((Белки+Углев)*4.1+Жиры *9.3) FROM Продукты; А что произойдет, если какой-либо член выражения не определен, т.е. имеет значение NULL и каким образом появилось такое значение? Если при загрузке строк таблицы в какой-либо из вводимых строк отсутствует значение для какого-либо столбца, то СУБД введет в такое поле NULL-значение. NULL-значение "придумано" для того, чтобы представить единым образом "неизвестные значения" для любых типов данных. Действительно, так как при вводе данных в столбец или их изменении СУБД запрещает ввод значений не соответствующих описанию данных этого столбца, то, например, нельзя использовать пробел для отсутствующего значения числа. Нельзя для этих целей использовать и ноль: нет месяца или дня недели равного нулю, да и для чисел ноль не может рассматриваться как неизвестное значение в одном месте и как известное - в другом. При выводе же NULL-значения на экран или печатающее устройство его код воспроизводится каким-либо специально заданным символом или набором символов: например, пробелом (если его нельзя перепутать с текстовым значением пробела) или сочетанием -0-. С помощью специальной команды можно установить в СУБД один из режимов представления NULL-значений при выполнении числовых расчетов: запрет или разрешение замены NULL-значения нулем. В первом случае любое арифметическое выражение, содержащее неопределенный операнд, будет также иметь неопределенное значение. Во втором случае результат вычислений будет иметь численное значение (если это значение попадает в диапазон представления соответствующего типа данных). Например, при выполнении запроса SELECT ПР, Цена, К_во, (Цена * К_во)FROM Поставки; и разных "настройках" СУБД могут быть получены разные результаты:
ПР Цена К_во (Цена*К_во) 9 -0- -0- -0- 11 1.5 50 75. 12 3. 10 30. 15 2. 170 340.
ПР Цена К_во (Цена*К_во) 9 -0- -0- 0. 11 1.5 50 75. 12 3. 10 30. 15 2. 170 340. 2.3. Выборка c использованием фразы WHERE 2.3.1. Использование операторов сравнения В синтаксисе фразы WHERE (п.2.1) показано, что для отбора нужных строк таблицы можно использовать операторы сравнения = (равно), (не равно), (больше), >= (больше или равно), которые могут предваряться оператором NOT, создавая, например, отношения "не меньше" и "не больше".
Так, для получения перечня продуктов, практически не содержащих углеводов, можно сформировать запрос SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C FROM ПродуктыWHERE Углев = 0; и получить: Продукт Белки Жиры Углев K Ca Na B2 PP C Говядина 189. 124. 0. 3150 90 600 1.5 28. 0 Судак 190. 80. 0. 1870 270 0 1.1 10. 30 Возможность использования нескольких условий, соединенных логическими операторами AND, OR, AND NOT и OR NOT, позволяет осуществить более детальный отбор строк. Так, для получения перечня продуктов, практически не содержащих углеводов и натрия, можно сформировать запрос: SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C FROM ПродуктыWHERE Углев = 0 AND Na = 0; Результат запроса имеет вид Продукт Белки Жиры Углев K Ca Na B2 PP C Судак 190. 80. 0. 1870 270 0 1.1 10. 30 Добавим к этому запросу еще одно условие SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C FROM ПродуктыWHERE Углев = 0 AND Na = 0 AND Продукт 'Судак'; и получим на экране сообщение "No rows exist or satisfy the specified clause" или аналогичное (в зависимости от вкусов разработчиков разных СУБД), информирующее об отсутствии строк, удовлетворяющих заданному(ым) условию(ям). 2.3.2. Использование BETWEEN С помощью BETWEEN . AND . (находится в интервале от . до .) можно отобрать строки, в которых значение какого-либо столбца находятся в заданном диапазоне. Например, выдать перечень продуктов, в которых значение содержания белка находится в диапазоне от 10 до 50: Результат: SELECT Продукт, БелкиFROM ПродуктыWHERE Белки BETWEEN 10 AND 50; Продукт Белки Майонез 31. Сметана 26. Молоко 28. Морковь 13. Лук 17. Можно задать и NOT BETWEEN (не принадлежит диапазону между), например: Результат: SELECT Продукт, Белки, ЖирыFROM Продукты WHERE Белки NOT BETWEEN 10 AND 50 AND Жиры > 100; Продукт Белки Жиры Говядина 189. 124. Масло 60. 825. Яйца 127. 115. BETWEEN особенно удобен при работе с данными, задаваемыми интервалами, начало и конец которых расположен в разных столбцах. Для примера воспользуемся таблицей "минимальных окладов" (табл. 2.1), величина которых непосредственно связана со студенческой стипендией. В этой таблице для текущего значения минимального оклада установлена запредельная дата окончания 9 сентября 9999 года. Таблица 2.1 Минимальные оклады Миноклад Начало Конец 2250 01-01-1993 31-03-1993 4275 01-04-1993 30-06-1993 7740 01-07-1993 30-11-1993 14620 01-12-1993 30-06-1994 20500 01-07-1994 09-09-9999 Если, например, потребовалось узнать, какие изменения минимальных окладов производились в 1993/94 учебном году, то можно выдать запрос SELECT Начало, МинокладFROM МинокладыWHERE Начало BETWEEN '1-9-1993' AND '31-8-1994' и получить результат: Начало Миноклад 01-12-1993 14620 01-07-1994 20500 Отметим, что при формировании запросов значения дат следует заключать в апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычитать из 31 значение 8, а затем 1994. Для выявления всех значений минимальных окладов, которые существовали в 1993/94 учебном году, можно сформировать запрос SELECT *FROM МинокладыWHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'OR Конец BETWEEN '1-9-1993' AND '31-8-1994' Миноклад Начало Конец 7740 01/07/1993 30/11/1993 14620 01/12/1993 30/06/1994 20500 01/07/1994 09/09/9999 Наконец, для получения минимального оклада на 15-5-1994: Результат: SELECT МинокладFROM МинокладыWHERE '15-05-1994' BETWEEN Начало AND Конец
Миноклад 14620 С помощью BETWEEN . AND . (находится в интервале от . до .) можно отобрать строки, в которых значение какого-либо столбца находятся в заданном диапазоне. Например, выдать перечень продуктов, в которых значение содержания белка находится в диапазоне от 10 до 50: Результат: SELECT Продукт, БелкиFROM ПродуктыWHERE Белки BETWEEN 10 AND 50; Продукт Белки Майонез 31. Сметана 26. Молоко 28. Морковь 13. Лук 17. Можно задать и NOT BETWEEN (не принадлежит диапазону между), например: Результат: SELECT Продукт, Белки, ЖирыFROM Продукты WHERE Белки NOT BETWEEN 10 AND 50 AND Жиры > 100; Продукт Белки Жиры Говядина 189. 124. Масло 60. 825. Яйца 127. 115. BETWEEN особенно удобен при работе с данными, задаваемыми интервалами, начало и конец которых расположен в разных столбцах. Для примера воспользуемся таблицей "минимальных окладов" (табл. 2.1), величина которых непосредственно связана со студенческой стипендией. В этой таблице для текущего значения минимального оклада установлена запредельная дата окончания 9 сентября 9999 года. Таблица 2.1 Минимальные оклады Миноклад Начало Конец 2250 01-01-1993 31-03-1993 4275 01-04-1993 30-06-1993 7740 01-07-1993 30-11-1993 14620 01-12-1993 30-06-1994 20500 01-07-1994 09-09-9999 Если, например, потребовалось узнать, какие изменения минимальных окладов производились в 1993/94 учебном году, то можно выдать запрос SELECT Начало, МинокладFROM МинокладыWHERE Начало BETWEEN '1-9-1993' AND '31-8-1994' и получить результат: Начало Миноклад 01-12-1993 14620 01-07-1994 20500 Отметим, что при формировании запросов значения дат следует заключать в апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычитать из 31 значение 8, а затем 1994. Для выявления всех значений минимальных окладов, которые существовали в 1993/94 учебном году, можно сформировать запрос SELECT *FROM МинокладыWHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'OR Конец BETWEEN '1-9-1993' AND '31-8-1994' Миноклад Начало Конец 7740 01/07/1993 30/11/1993 14620 01/12/1993 30/06/1994 20500 01/07/1994 09/09/9999 Наконец, для получения минимального оклада на 15-5-1994: Результат: SELECT МинокладFROM МинокладыWHERE '15-05-1994' BETWEEN Начало AND Конец Миноклад 14620 2.3.3. Использование IN Выдать сведения о блюдах на основе яиц, крупы и овощей SELECT *FROM БлюдаWHERE Основа IN (Яйца Крупа Овощи); Результат: БЛ Блюдо В Основа Выход Труд 1 Салат летний З Овощи 200. 3 3 Салат витаминный З Овощи 200. 4 16 Драчена Г Яйца 180. 4 17 Морковь с рисом Г Овощи 260. 3 19 Омлет с луком Г Яйца 200. 5 20 Каша рисовая Г Крупа 210. 4 21 Пудинг рисовый Г Крупа 160. 6 23 Помидоры с луком Г Овощи 260. 4 Рассмотренная форма IN является в действительности просто краткой записью последовательности отдельных сравнений, соединенных операторами OR. Предыдущее предложение эквивалентно такому: SELECT *FROM БлюдаWHERE Основа=Яйца OR Основа=Крупа OR Основа=Овощи; Можно задать и NOT IN (не принадлежит), а также возможность использования IN (NOT IN) с подзапросом (см. главу 3).
2.3.4. Использование LIKE Выдать перечень салатов Результат: SELECT БлюдоFROM БлюдаWHERE Блюдо LIKE 'Салат%';
Блюдо Салат летний Салат мясной Салат витаминный Салат рыбный Обычная форма "имя_столбца LIKE текстовая_константа" для столбца текстового типа позволяет отыскать все значения указанного столбца, соответствующие образцу, заданному "текстовой_константой". Символы этой константы интерпретируются следующим образом: символ _ (подчеркивание) – заменяет любой одиночный символ, символ % (процент) – заменяет любую последовательность из N символов (где N может быть нулем), все другие символы означают просто сами себя. Следовательно, в приведенном примере SELECT будет осуществлять выборку записей из таблицы Блюда, для которых значение в столбце Блюдо начинается сочетанием 'Салат' и содержит любую последовательность из нуля или более символов, следующих за сочетанием 'Салат'. Если бы среди блюд были "Луковый салат", "Фруктовый салат" и т.п., то они не были бы найдены. Для их отыскания надо изменить фразу WHERE: WHERE Блюдо LIKE '%салат%' или при отсутствии различий между малыми и большими буквами (такую настройку допускают некоторые СУБД): WHERE Блюдо LIKE '%Салат%' Это позволит отыскать все салаты. 2.3.5. Вовлечение неопределенного значения (NULL-значения) Как было рассказано в п.2.2.3, если при загрузке данных не введено значение в какое-либо поле таблицы, то СУБД поместит в него NULL-значение. Аналогичное значение можно ввести в поле таблицы, выполняя операцию изменения данных. Так, при отсутствии сведений о наличии у поставщиков судака и моркови в столбцы Цена и К_во соответствующих строк таблицы Поставки вводится NULL и там будет храниться код NULL-значения, а не 0, 0. или пробел. (Отметим, что в распечатке таблицы Поставки рис.1.1 в этих местах расположен пробел, установленный в СУБД для представления NULL-значения при выводе на печать). В этом случае для выявления названий продуктов, отсутствующих в кладовой, шеф-повар может дать запрос Результат: ПР SELECT DISTINCT ПРFROM Наличие WHERE К_во IS NULL; 2 9 Естественно, что для выявления продуктов, существующих в кладовой, следует дать запрос SELECT DISTINCT ПРFROM НаличиеWHERE К_во IS NOT NULL; Использование условий столбец IS NULL и столбец IS NOT NULL вместо, например, столбец = NULL и столбец NULL связано с тем, что ничто - и даже само NULL-значение - не считается равным другому NULL-значению. (Несмотря на это, два неопределенных значения рассматриваются, однако, как дубликаты друг друга при исключении дубликатов, и предложение SELECT DISTINCT даст в результате не более одного NULL-значения.) 2.4. Выборка с упорядочением Синтаксис фразы упорядочения был дан в п. 2.1. Простейший вариант этой фразы - упорядочение строк результата по значению одного из столбцов с указанием порядка сортировки или без такого указания. (По умолчанию строки будут сортироваться в порядке возрастания значений в указанном столбце.) Например, выдать перечень продуктов и содержание в них основных веществ в порядке убывания содержания белка Результат:
SELECT Продукт, Белки, Жиры, УглевFROM ПродуктыORDER BY Белки DESC;
Продукт Белки Жиры Углев Судак 190. 80. 0. Говядина 189. 124. 0. Творог 167. 90. 13. Яйца 127. 115. 7. Кофе 127. 36. 9. Мука 106. 13. 732. . . . При включении в список ORDER BY нескольких столбцов СУБД сортирует строки результата по значениям первого столбца списка пока не появится несколько строк с одинаковыми значениями данных в этом столбце. Такие строки сортируются по значениям следующего столбца из списка ORDER BY и т.д. Например, выдать содержимое таблицы Блюда, отсортировав ее строки по видам блюд и основе: Результат:
SELECT *FROM БлюдаORDER BY В Основа;
БЛ Блюдо В Основа Выход Труд 21 Пудинг рисовый Г Крупа 160. 6 20 Каша рисовая Г Крупа 210. 4 18 Сырники Г Молоко 220. 4 . . . 16 Драчена Г Яйца 180. 4 28 Крем творожный Д Молоко 160. 4 . . . 26 Яблоки печеные Д Фрукты 160. 3 7 Сметана З Молоко 140. 1 8 Творог З Молоко 140. 2 2 Салат мясной З Мясо 200. 4 6 Мясо с гарниром З Мясо 250. 3 1 Салат летний З Овощи 200. 3 . . . Кроме того, в список ORDER BY можно включать не только имя столбца, а его порядковую позицию в перечне SELECT. Благодаря этому возможно упорядочение результатов на основе вычисляемых столбцов, не имеющих имен. Например, запрос SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)FROM ПродуктыORDER BY 2;
позволит получить список продуктов, показанный на рис.2.2,в – переупорядоченный по возрастанию значений калорийности список рис.2.2,а. 2.5. Агрегирование данных 2.5.1 SQL-функции В SQL существует ряд специальных стандартных функций (SQL-функций). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так: COUNT - число значений в столбце, SUM - сумма значений в столбце, AVG - среднее значение в столбце, MAX - самое большое значение в столбце, MIN - самое малое значение в столбце. Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения. Следует отметить, что здесь столбец - это столбец виртуальной таблицы, в которой могут содержаться данные не только из столбца базовой таблицы, но и данные, полученные путем функционального преобразования и (или) связывания символами арифметических операций значений из одного или нескольких столбцов. При этом выражение, определяющее столбец такой таблицы, может быть сколь угодно сложным, но не должно содержать SQL-функций (вложенность SQL-функций не допускается). Однако из SQL-функций можно составлять любые выражения. Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Специальная же функция COUNT(*) служит для подсчета всех без исключения строк в таблице (включая дубликаты). 2.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; то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для "свободного" столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где ПР=10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой. Правда, никто не запрещает дать запрос SELECT 'Кол-во лука =',SUM(К_во),COUNT(К_во)FROM ПоставкиWHERE ПР = 10; Результат: 'Кол-во лука =' SUM(К_во) COUNT(К_во) Кол-во лука = 220 2 Отметим также, что в столбце-аргументе перед применением любой функции, кроме COUNT(*), исключаются все неопределенные значения. Если оказывается, что аргумент - пустое множество, функция COUNT принимает значение 0, а остальные - NULL. Например, для получения суммы цен, средней цены, количества поставляемых продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ (ПС=5), а также для получения количества продуктов, которые могут поставляться этим коопторгом, можно дать запрос SELECT SUM(Цена),AVG(Цена),COUNT(Цена), COUNT(DISTINCT Цена),COUNT(*) FROM ПоставкиWHERE ПС = 5; и получить SUM(Цена) AVG(Цена) COUNT(Цена) COUNT(DISTINCT Цена) COUNT (*) 6.2 1.24 5 4 7 В другом примере, где надо узнать "Сколько поставлено моркови и сколько поставщиков ее поставляют?": SELECT SUM(К_во),COUNT(К_во)FROM ПоставкиWHER ПР = 2; будет получен ответ: SUM(К_во) COUNT (К_во) -0- 0 Наконец, попробуем получить сумму массы поставленного лука с его средней ценой ("Сапоги с яичницей"): Результат: SELECT (SUM(К_во) +AVG(Цена)) FROM ПоставкиWHERE ПР = 10; SUM(К_во)+AVG(Цена) 220.6 2.5.3. Фраза GROUP BY Мы показали, как можно вычислить массу определенного продукта, поставляемого поставщиками. Предположим, что теперь требуется вычислить общую массу каждого из продуктов, поставляемых в настоящее время поставщиками. Это можно легко сделать с помощью предложения SELECT ПР, SUM(К_во)FROM ПоставкиGROUP BY ПР; Результат показан на рис. 2.3,а. а) б) в) г)
ПР 9 0 11 150 12 30 15 370 1 370 3 250 5 170 6 220 8 150 7 200 2 0 4 100 13 190 14 70 16 250 17 50 10 220
ПС ПР Цена К_во 1 9 -0- -0- 3 9 -0- -0- 5 9 -0- -0- 1 11 1.50 50 5 11 -0- -0- 6 11 -0- -0- 8 11 1.00 100 1 12 3.00 10 3 12 2.50 20 6 12 -0- -0- 1 15 2.00 170 3 15 1.50 200 2 1 3.60 300 7 1 4.20 70 2 3 -0- -0- 7 3 4.00 250 . . .
ПР 1 370 2 0 3 250 4 100 5 170 6 220 7 200 8 150 9 0 10 220 11 150 12 30 13 190 14 70 15 370 16 250 17 50
ПР 9 0 11 150 12 30 15 70 1 370 3 250 5 70 6 140 8 150 7 200 2 0 4 100 13 190 14 70 16 250 17 50 10 220 Рис. 2.3. Иллюстрации к фразе GROUP BY Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы Поставки группируются так, что в одной группе содержатся все строки для продукта с ПР = 1, в другой – для продукта с ПР = 2 и т.д. (см. рис. 2.3.б). Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).
Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по ПР результата рассматриваемого примера (рис. 2.3,в) следует дать запрос SELECT ПР, SUM(К_во)FROM ПоставкиGROUP BY ПРORDER BY ПР; Наконец, отметим, что строки таблицы можно группировать по любой комбинации ее столбцов. Так, по запросу SELECT Т, БЛ, COUNT(БЛ)FROM ЗаказGROUP BY Т, БЛ; можно узнать коды и количество порций блюд, заказанных отдыхающими пансионата (32 человека) на каждую из трапез следующего дня: Т БЛ COUNT(БЛ) 1 3 18 1 6 14 1 19 17 1 21 15 . Если в запросе используются фразы WHERE и GROUP BY, то строки, не удовлетворяющие фразе WHERE, исключаются до выполнения группирования. Например, выдать для каждого продукта его код и общий объем возможных поставок, учитывая временную недееспособность поставщика с ПС=2: SELECT ПР, SUM(К_во)FROM ПоставкиWHERE ПС 2GROUP BY ПР; Результат, приведенный на рис. 2.3,г, отличается от результата (рис. 2.3,а) аналогичного запроса для всех поставщиков объемом поставок продуктов с кодами 15, 5 и 6. 2.5.4. Использование фразы HAVING Фраза HAVING (рис.2.3) играет такую же роль для групп, что и фраза WHERE для строк: она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы. Например, выдать коды продуктов, поставляемых более чем двумя поставщиками: SELECT FROM ПоставкиGROUP BY ПСHAVING COUNT(*) > 2; Результат: ПР 9 11 12 В п.3.6 можно познакомиться с более содержательным примером использования этой фразы. Глава 3. Запросы с использованием нескольких таблиц 3.1. О средствах одновременной работы с множеством таблиц Затрагивая вопросы проектирования баз данных [2], мы выяснили, что базы данных - это множество взаимосвязанных сущностей или отношений (таблиц) в терминологии реляционных СУБД. При проектировании стремятся создавать таблицы, в каждой из которых содержалась бы информация об одном и только об одном типе сущностей. Это облегчает модификацию базы данных и поддержание ее целостности. Но такой подход тяжело усваивается начинающими проектантами, которые пытаются привязать проект к будущим приложениям и так организовать таблицы, чтобы в каждой из них хранилось все необходимое для реализации возможных запросов. Типичен вопрос: как же получить сведения о том, где купить продукты для приготовления того или иного блюда и определить его калорийность и стоимость, если нужные данные "рассыпаны" по семи различным таблицам? Не лучше ли иметь одну большую таблицу, содержащую все сведения базы данных ПАНСИОН ? Даже при отсутствии средств одновременного доступа ко многим таблицам нежелателен проект, в котором информация о многих типах сущностей перемешана в одной таблице. SQL же обладает великолепным механизмом для одновременной или последовательной обработки данных из нескольких взаимосвязанных таблиц. В нем реализованы возможности "соединять" или "объединять" несколько таблиц и так называемые "вложенные подзапросы". Например, чтобы получить перечень поставщиков продуктов, необходимых для приготовления Сырников, возможен запрос SELECT Продукт, Цена, Название, СтатусFROM Продукты, Состав, Блюда, Поставки, ПоставщикиWHERE Продукты.ПР = Состав.ПРAND Состав.БЛ = Блюда.БЛAND Поставки.ПР = Состав.ПРAND Поставки.ПС = Поставщики.ПСAND Блюдо = 'Сырники'AND Цена IS NOT NULL; Продукт Цена Название Статус Яйца 1.8 ПОРТОС кооператив Яйца 2. КОРЮШКА кооператив Сметана 3.6 ПОРТОС кооператив Сметана 2.2 ОГУРЕЧИК ферма Творог 1. ОГУРЕЧИК ферма Мука 0.5 УРОЖАЙ коопторг Сахар 0.94 ТУЛЬСКИЙ универсам Сахар 1. УРОЖАЙ коопторг Он получен следующим образом: СУБД последовательно формирует строки декартова произведения таблиц, перечисленных во фразе FROM, проверяет, удовлетворяют ли данные сформированной строки условиям фразы WHERE, и если удовлетворяют, то включает в ответ на запрос те ее поля, которые перечислены во фразе SELECT. Следует подчеркнуть, что в SELECT и WHERE (во избежание двусмысленности) ссылки на все (*) или отдельные столбцы могут (а иногда и должны) уточняться именем соответствующей таблицы, например, Поставки.ПС, Поставщики.ПС, Меню.*, Состав.БЛ, Блюда.* и т.п. Очевидно, что с помощью соединения несложно сформировать запрос на обработку данных из нескольких таблиц. Кроме того, в такой запрос можно включить любые части предложения SELECT, рассмотренные в главе 2 (выражения с использованием функций, группирование с отбором указанных групп и упорядочением полученного результата). Следовательно, соединения позволяют обрабатывать множество взаимосвязанных таблиц как единую таблицу, в которой перемешана информация о многих типах сущностей. Поэтому начинающий проектант базы данных может спокойно создавать маленькие нормализованные таблицы, так как он всегда может получить из них любую "большую" таблицу.
Кроме механизма соединений в SQL есть механизм вложенных подзапросов, позволяющий объединить несколько простых запросов в едином предложении SELECT. Иными словами, вложенный подзапрос - это уже знакомый нам подзапрос (с небольшими огра-ничениями), который вложен в WHERE фразу другого вложенного подзапроса или WHERE фразу основного запроса.
Для иллюстрации вложенного подзапроса вернемся к предыдущему примеру и попробуем получить перечень тех поставщиков продуктов для Сырников, которые поставляют нужные продукты за минимальную цену. SELECT Продукт, Цена, Название, СтатусFROM Продукты, Состав, Блюда, Поставки, ПоставщикиWHERE Продукты.ПР = Состав.ПРAND Состав.БЛ = Блюда.БЛAND Поставки.ПР = Состав.ПРAND Поставки.ПС = Поставщики.ПСAND Блюдо = 'Сырники'AND Цена = ( SELECT MIN(Цена) FROM Поставки X WHERE X.ПР = Поставки.ПР ); Результат запроса имеет вид Продукт Цена Название Статус Яйца 1.8 ПОРТОС кооператив Сахар 0.94 ТУЛЬСКИЙ универсам Мука 0.5 УРОЖАЙ коопторг Сметана 2.2 ОГУРЕЧИК ферма Творог 1. ОГУРЕЧИК ферма Здесь с помощью подзапроса, размещенного в трех последних строках запроса, описывается процесс определения минимальной цены каждого продукта для Сырников и поиск поставщика, предлагающего этот продукт за такую цену. Механизм реализации подзапросов будет подробно описан в п.3.3. Там же будет рассмотрено, как и для чего вводится псевдоним X для имени таблицы Поставки.3.2. Запросы, использующие соединения 3.2.1. Декартово произведение таблиц В литературе [2] показано, что соединения - это подмножества декартова произведения. Так как декартово произведение n таблиц - это таблица, содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, . и строки из n-й таблицы (а мы уже научились выделять с помощью SELECT любое подмножество реляционной таблицы), то осталось лишь выяснить, можно ли с помощью SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц надо указать во фразе FROM перечень перемножаемых таблиц, а во фразе SELECT – все их столбцы. Так, для получения декартова произведения Вид_блюд и Трапезы надо выдать запрос SELECT Вид_блюд.*, Трапезы.*FROM Вид_блюд, Трапезы; Получим таблицу, содержащую 5 х 3 = 15 строк: В Вид Т Трапеза З Закуска 1 Завтрак З Закуска 2 Обед З Закуска 3 Ужин С Суп 1 Завтрак С Суп 2 Обед С Суп 3 Ужин Г Горячее 1 Завтрак Г Горячее 2 Обед Г Горячее 3 Ужин Д Десерт 1 Завтрак Д Десерт 2 Обед Д Десерт 3 Ужин Н Напиток 1 Завтрак Н Напиток 2 Обед Н Напиток 3 Ужин В другом примере, где перемножаются таблицы Меню, Трапезы, Вид_блюд, Блюда: SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*FROM Меню, Трапезы, Вид_блюд, Блюда; образуется таблица (рис 3.1), содержащая 21 х 3 х 5 х 33 = 10395 строк. Из первых 39 строк этой таблицы лишь две актуальных (отмечены "*"): в них совпадают номера блюд таблиц Меню и Блюда. В остальных – полная чепуха: к закускам отнесены супы и напитки, на завтрак предлагается незапланированный суп и т.д. 3.2.2. Эквисоединение таблиц Если из декартова произведения убрать ненужные строки и столбцы, то можно получить актуальные таблицы, соответствующие любому из соединений. Меню Трапезы Вид_блюд Блюда Т В БЛ Т Трапеза В Вид БЛ Блюдо В Основа Выход Труд 1 З 3 1 Завтрак З Закуска 1 Салат летний З Овощи 200. 3 1 З 3 1 Завтрак З Закуска 2 Салат мясной З Мясо 200. 4 1 З 3 1 Завтрак З Закуска 3 Салат витаминный З Овощи 200. 4 * . . . 1 З 3 1 Завтрак З Закуска 12 Суп молочный С Молоко 500. 3 1 З 3 1 Завтрак З Закуска 13 Бастурма Г Мясо 300. 5 . . . 1 З 3 1 Завтрак З Закуска 32 Кофе черный Н Кофе 100. 1 1 З 3 1 Завтрак З Закуска 33 Кофе на молоке Н Кофе 200. 2 1 З 6 1 Завтрак З Закуска 1 Салат летний З Овощи 200. 3 1 З 6 1 Завтрак З Закуска 2 Салат мясной З Мясо 200. 4 1 З 6 1 Завтрак З Закуска 3 Салат витаминный З Овощи 200. 4 1 З 6 1 Завтрак З Закуска 4 Салат рыбный З Рыба 200. 4 1 З 6 1 Завтрак З Закуска 5 Паштет из рыбы З Рыба 120. 5 1 З 6 1 Завтрак З Закуска 6 Мясо с гарниром З Мясо 250. 3 * . . . Рис. 3.1. Иллюстрация декартова произведения Очевидно, что отбор актуальных строк обеспечивается вводом в запрос WHERE фразы, в которой устанавливается соответствие между: кодами трапез (Т) в таблицах Меню и Трапезы (Меню.Т = Трапезы.Т), кодами видов блюд (В) в таблицах Меню и Вид_блюд (Меню.В = Вид_блюд.В), номерами блюд (БЛ) в таблицах Меню и Блюда (Меню.БЛ = Блюда.БЛ).
Такой скорректированный запрос SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*FROM Меню, Трапезы, Вид_блюд, БлюдаWHERE Меню.Т = Трапезы.ТAND Меню.В = Вид_блюд.ВAND Меню.БЛ = Блюда.БЛ; позволит получить эквисоединение таблиц Меню, Трапезы, Вид_блюд и Блюда: Т В БЛ Т Трапеза В Вид БЛ Блюдо В Основа Выход Труд 1 З 3 1 Завтрак З Закуска 3 Салат витаминный З Овощи 200. 4 1 З 6 1 Завтрак З Закуска 6 Мясо с гарниром З Мясо 250. 3 1 Г 19 1 Завтрак Г Горячее 19 Омлет с луком Г Яйца 200. 5 . . . 3 Г 16 3 Ужин Г Горячее 16 Драчена Г Яйца 180. 4 3 Н 30 3 Ужин Н Напиток 30 Компот Н Фрукты 200. 2 3 Н 31 3 Ужин Н Напиток 31 Молочный напиток Н Молоко 200. 2 3.2.3. Естественное соединение таблиц Легко заметить, что в эквисоединение таблиц вошли дубликаты столбцов, по которым проводилось соединение (Т, В и БЛ). Для исключения этих дубликатов можно создать естественное соединение тех же таблиц: SELECT Т, В, БЛ, Трапеза, Вид, Блюдо, Основа, Выход, ТрудFROM Меню, Трапезы, Вид_блюд, БлюдаWHERE Меню.Т = Трапезы.ТAND Меню.В = Вид_блюд.ВAND Меню.БЛ = Блюда.БЛ; Реализация естественного соединения таблиц имеет вид Т В БЛ Трапеза Вид Блюдо Основа Выход Труд 1 З 3 Завтрак Закуска Салат витаминный Овощи 200. 4 1 З 6 Завтрак Закуска Мясо с гарниром Мясо 250. 3 1 Г 19 Завтрак Горячее Омлет с луком Яйца 200. 5 . 3 Г 16 Ужин Горячее Драчена Яйца 180. 4 3 Н 30 Ужин Напиток Компот Фрукты 200. 2 3 Н 31 Ужин Напиток Молочный напиток Молоко 200. 2 3.2.4. Композиция таблиц Для исключения всех столбцов, по которым проводится соединение таблиц, надо создать композицию SELECT Трапеза, Вид, Блюдо, Основа, Выход, ТрудFROM Меню, Трапезы, Вид_блюд, БлюдаWHERE Меню.Т = Трапезы.ТAND Меню.В = Вид_блюд.ВAND Меню.БЛ = Блюда.БЛ; имеющую вид Трапеза Блюдо Вид Основа Выход Труд Завтрак Салат витаминный Закуска Овощи 200. 4 Завтрак Мясо с гарниром Закуска Мясо 250. 3 Завтрак Омлет с луком Горячее Яйца 200. 5 . . . Ужин Драчена Горячее Яйца 180. 4 Ужин Компот Напиток Фрукты 200. 2 Ужин Молочный напиток Напиток Молоко 200. 2 3.2.5. Тета-соединение таблиц В базе данных ПАНСИОН трудно подобрать несложный пример, иллюстрирующий тета-соединение таблиц. Поэтому сконструируем такой надуманный запрос: SELECT Вид_блюд.*, Трапезы.*FROM Вид_блюд, ТрапезыWHERE Вид > Трапеза; позволяющий выбрать из полученного в п.3.2.1 декартова произведения таблиц Вид_блюд и Трапезы лишь те строки, в которых значение трапезы "меньше" (по алфавиту) значения вида блюда: В Вид Т Трапеза З Закуска 1 Завтрак С Суп 1 Завтрак С Суп 2 Обед Н Напиток 1 Завтрак 3.2.6. Соединение таблиц с дополнительным условием При формировании соединения создается рабочая таблица, к которой применимы все операции, рассмотренные в главе 2: отбор нужных строк соединения (WHERE фраза), упорядочение получаемого результата (ORDER BY фраза) и агрегатирование данных (SQL-функции и GROUP BY фраза).
Например, для получения перечня блюд, предлагаемых в меню на завтрак, можно сформировать запрос на основе композиции (п. 3.2.4): SELECT Вид, Блюдо, Основа, Выход, 'Номер -', БЛFROM Меню, Трапезы, Вид_блюд, БлюдаWHERE Меню.Т = Трапезы.ТAND Меню.В = Вид_блюд.ВAND Меню.БЛ = Блюда.БЛAND Трапеза = ’Завтрак’; Получим Вид Блюдо Основа Выход 'Номер -' БЛ Закуска Салат витаминный Овощи 200. Номер - 3 Закуска Мясо с гарниром Мясо 250. Номер - 6 Горячее Омлет с луком Яйца 200. Номер - 19 Горячее Пудинг рисовый Крупа 160. Номер - 21 Напиток Молочный напиток Молоко 200. Номер - 31 Напиток Кофе черный Кофе 100. Номер - 32 В п.3.6 можно познакомиться с достаточно полным примером соединения таблиц с различными дополнительными фразами. 3.2.7. Соединение таблицы со своей копией В ряде приложений возникает необходимость одновременной обработки данных какой-либо таблицы и одной или нескольких ее копий, создаваемых на время выполнения запроса. Например, при создании списков студентов (таблица Студенты) возможен повторный ввод данных о каком-либо студенте с присвоением ему второго номера зачетной книжки. Для выявления таких ошибок можно соединить таблицу Студенты с ее временной копией, установив в WHERE фразе равенство значений всех одноименных столбцов этих таблиц кроме столбцов с номером зачетной книжки (для последних надо установить условие неравенства значений). Временную копию таблицы можно сформировать, указав имя псевдонима за именем таблицы во фразе FROM. Так, с помощью фразы FROM Блюда X, Блюда Y, Блюда Z будут сформированы три копии таблицы Блюда с именами X, Y и Z. В качестве примера соединения таблицы с ней самой сформируем запрос на вывод таких пар блюд таблицы Блюда, в которых совпадает основа, а название первого блюда пары меньше (по алфавиту), чем номер второго блюда пары. Для этого можно создать запрос с одной копией таблицы Блюда (Копия): SELECT Блюдо, Копия.Блюдо, ОсноваFROM Блюда, Блюда КопияWHERE Основа = Копия.ОсноваAND Блюдо или двумя ее копиями (Первая и Вторая): SELECT Первая.Блюдо, Вторая.Блюдо, ОсноваFROM Блюда Первая, Блюда ВтораяWHERE Первая.Основа = Вторая.ОсноваAND Первая.Блюдо Получим результат вида Первая.Блюдо Вторая.Блюдо Основа Морковь с рисом Помидоры с луком Овощи Морковь с рисом Салат летний Овощи Морковь с рисом Салат витаминный Овощи Помидоры с луком Салат витаминный Овощи Помидоры с луком Салат летний Овощи Салат витаминный Салат летний Овощи Бастурма Бефстроганов Мясо Бастурма Мясо с гарниром Мясо Бефстроганов Мясо с гарниром Мясо