Web-доступ к базам данных

Организация Web-доступа к базам данных с использованием SQL-запросов. Введение Базы данных выполняют функцию систематизации знаний. На основе этой систематизации могут создаваться новые знания. Так или иначе, любая база данных служит человеку именно для описания происшедших в прошлом событий и на основе знания этих событий помогает принять то или иное решение на будущее.
База знаний может быть построена как мультимедийный справочник или как набор текстов и файлов другого формата, проиндексированных по определенным признакам в базе данных. База данных – это, прежде всего, хранилище объектов данных, т.е. набора возможных понятий или событий, описываемых базой данных, с возможностью поиска этих объектов по признакам. Неотъемлемой чертой базы данных является возможность связывания объектов между собой.
Базой данных можно считать не только таблицы, индексирующие файлы со знаниями разных форматов, но и сами эти файлы, потому, что они являются не типизированными хранилищами знаний в такой базе данных. Итак, в базах знаний мы накапливаем опыт прошлого. Потом человек может сам принять решение на основе этого опыта типичный случай с мультимедийным справочником или поставить задачу перед базой данных по поиску решения согласно сложившейся ситуации найти закон,
поясняющий правило оформления таможенной декларации и т.п Так происходит в программах справочного характера. Как частный случай баз данных, можно рассматривать различные структурированные файлы, например, словари для переводчиков, форматы файлов RTF, DOC, книги Microsoft Excel, файлы с письмами для почтовых Internet-программ и т.д жизненно важные функции баз данных, в которых
реализуются за счет внутренних функций программ работающих с ними. Базы данных могут применяться как вспомогательное средство, позволяющее реализовать какую-то полезную функцию. Например, хранение настроек программы, Internet-адресов для рассылки рекламы и т.д. Структура информационных систем. Для построения информационных систем применяются базы данных, созданные вокруг ядра базы данных. Работа с базой данных происходит, как правило, в многопользовательском режиме, т.е. программа должна быть сетевой. В связи с этим, необходимо обеспечить разделение прав доступа различным пользователям к данным, правильность завершения транзакций, т.е. ссылочную целостность, ограничения и другие правила, реализуемые через встроенные средства сервера базы данных. К тому же, должна быть обеспечена приемлемая производительность информационной системы. В центре всей информационной системы стоит сервер базы данных.
Он обеспечивает низкоуровневый доступ к таблицам базы данных, в которых и хранится информация об объектах базы данных. Ядром информационной системы в простейшем случае могут выступать несколько функций, реализованных в программе программистом. В современном мире чаще всего применяется сервер приложений для реализации ядра информационной системы. В распределенной вычислительной системе сервер приложений берет на себя функцию распределения нагрузки между серверами, которые в общем случае могут работать под разными операционными
системами, или находится в разных географически местах. Сервер приложений – это мостик между программами-клиентами и одним или несколькими серверами базы данных. За счет сервера приложений можно снизить нагрузку на приложения пользователя и реализовать сложные правила объектной модели базы данных, которые трудно или нерационально реализовывать на стороне сервера базы данных. В результате, сервер приложений снижает трафик между сервером базы данных и компьютером
клиента, повышая общую производительность информационной системы. Исходя из сказанного ранее, на приложение пользователя остается только реализация интерфейса. Такая структура информационной системы называется многозвенной, а приложение пользователя – тонким клиентом. Надо отметить, что в общем случае серверы приложений могут посылать команды друг другу, и взаимодействовать, таким образом, самым рациональным способом с географически удаленными серверами баз данных. Например, для получения отчета с большим количеством вычисляемых полей, нет необходимости делать несколько запросов к удаленной базе данных через Internet, если это может сделать сервер приложений, находящийся в непосредственной близости от сервера базы данных. Он и пошлет в ответ готовый отчет. Таким образом, только информационная система, построенная по принципу многозвенности, может удовлетворять наиболее полным образом условиям наивысшей производительности при
полной коммуникабельности и распределенности вычислений. Система, построенная из нескольких отдельных модулей, выполняющих ряд определенных задач, к тому же, может быть проще модифицируемой. Необходимые функции базы данных. Первой и самой важной функцией базы данных, является функция хранения информации. Информация должна хранится упорядоченно для более быстрого и понятного пользователю доступа к ней.
Упорядоченность информации в базе данных, помимо удобств доступа, может привести к значительному сокращению аппаратных ресурсов, необходимых для ее обслуживания. Упорядоченность достигается путем нормализации. Здесь мы вплотную подошли ко второй функции базы данных – ввод информации. Какую информацию будет вводить пользователь? Хорошая база данных построена из главного документа, справочников, из которых пользователь вводит информацию
и нескольких полей для ручного ввода, например, текстов назначения платежа в платежных поручениях и суммы. База данных должна заполняться средствами, наиболее полно автоматизирующими этот процесс. При этом плохим тоном являются ввод информации об одном объекте разными способами или в разных местах ввод одной и той же информации в нескольких местах ввод информации разрозненно, без поддержания общей структуры объекта. Одной из основных функций базы данных является автоматизация. Под автоматизацией, как правило, понимают автоматическое создание выходных документов и пересчет данных, например печать накладной, счета фактуры и протокола согласования цен в складской программе для исходящей накладной. Далее, нужно вспомнить о системах принятия решений. Информационная система должна позволять создавать статистические отчеты в реальном режиме времени о состоянии описываемого в базе данных процесса. Эта функция удобна для руководителей подразделений, которые
могут прогнозировать поведение описываемой системы на основе статистических данных, полученных из базы данных. Собственно, описанные выше функции информационной системы являются джентльменским набором , которого достаточно в большинстве случаев. Из дополнительных функций необходимо упомянуть возможность поиска по нескольким взаимосвязанным характеристикам. В единой информационной системе необходима возможность идентификации пользователя с целью ограничения
доступа пользователя к определенным частям базы данных и введения информации о создателе документа и лиц, редактировавших его. Это придаст пользователям ощущение ответственности за выполняемые действия. Хорошая информационная система должна легко расширяться при необходимости добавления в нее новых возможностей. Расширяемость подразумевает элементы объектной ориентированности, встроенные в базу данных. Настраивая эти объекты, возможно вносить незначительные изменения в структуру базы данных, что продляет
срок морального устаревания всей информационной системы. Одним из факторов расширяемости является возможность сочленять разнородные базы данных в единый комплекс. Такая возможность сейчас реализуется через дополнительные модули, которые по своей сути являются серверами приложений, или правильное построение базы данных по классическим реляционным законам. Последний случай затрудняется тем, что некоторые серверы базы данных не могут выполнить один SQL запрос к разным базам данных, тем более находящимся в географической удаленности друг от друга. Еще одна удобная функция в базе данных – это сквозное прохождение по документам. Описанные выше функции в разных реализациях информационных систем имеют специфические черты, ориентированные на конкретное прикладное применения. Причины и история создания языка запросов SQL. Реляционные базы данных. Общие понятия. Любую структуру данных можно преобразовать в простую двумерную
таблицу. Такое представление является наиболее удобным и для пользователя, и для машины подавляющее большинство современных информационных систем работает именно с такими таблицами. Базы данных, которые состоят из двумерных таблиц, называются реляционными. Основная идея реляционного подхода состоит в том, чтобы представить произвольную структуру данных в виде простой двумерной таблицы или, как говорят, нормализовать структуру.
Из всех систем баз данных реляционные относятся к самым распространенным в мире. Эти системы способны разрешить многие из тех проблем, которые усложняли работу с нереляционными продуктами прежних поколений. Программисты и администраторы таких баз данных были вынуждены тщательно изучать, как структурирована информация и как она представлена в базе данных, что значительно усложнило разработку этих приложений и модификацию самих программ. Реляционные системы способны работать на более высоком
уровне. Все операции с данными реализуются программой, называемой DBMS система управления базой данных Обращаться к ней можно только с помощью операторов языка высокого уровня. Хотя некоторые продукты по-прежнему поддерживают работу в терминах своих собственных языков, язык SQL Standard Query Language стал тем технологическим стандартом, на базе которого созданы все, более или менее известные, реляционные продукты. Язык для взаимодействия с БД SQL появился в середине 70-х и был разработан в рамках проекта экспериментальной реляционной СУБД System R. Исходное название языка SEQUEL Structered English Query Language только частично отражает суть этого языка. Конечно, язык был ориентирован главным образом на удобную и понятную пользователям формулировку запросов к реляционной БД, но на самом деле уже являлся полным языком
БД, содержащим помимо операторов формулирования запросов и манипулирования БД средства определения и манипулирования схемой БД. В языке отсутствовали средства синхронизации доступа к объектам БД со стороны параллельно выполняемых транзакций с самого начала предполагалось, что необходимую синхронизацию неявно выполняет СУБД. В основе современных реляционных баз данных и стандарта
SQL лежит несколько правил и принципов Все значения данных состоят из простых типов данных. В SQL отсутствуют массивы, указатели, векторы и другие сложные типы данных. Все данные в реляционной базе данных изображаются в форме двумерных таблиц на языке математики – отношений . Каждая таблица содержит некоторое число строк в том числе 0 , называемых картежами и один или несколько столбцов, называемых атрибутами . Все строки в таблице имеют одну и ту же последовательность столбцов,
в которых записаны различные значения, однако наборы значений в столбцах отличаются. На рисунке 1 приведена простейшая таблица такого типа. После того как данные введены в БД, можно сравнивать значения в различных столбцах в том числе и для разных таблиц или объединять строки, в которых найдено совпадение. Это позволяет соотносить между собой строки и производить очень сложные операции обработки над всеми
данными, находящимися в базе. Все операции определяются только логикой, а не положением строки в таблице. Например, можно запросить все строки, со значением 2 и не возможно запросить первую или, третью или пятую строку. Строки в реляционной базе данных расположены в произвольном порядке. Он не обязательно соответствует тому порядку, в котором они были занесены или в котором хранятся на диске. Поскольку невозможно определить строку по ее положению порядку в таблице , необходимо иметь один или несколько столбцов с уникальным значением для идентификации каждой строки. Эти столбцы называются первичными ключами таблицы. ID Имя Телефон Город 2 Иван И. Иванов 555-001 Москва 1 Константин В. Волков 555-330 Екатеринбург 3 Василий В. Грабер 555-607 Санкт-Петербург Одним из преимуществ реляционного подхода к построению
БД – отсутствие необходимости заботится о таких деталях, как способы представления данных или их физическое размещение в самой базе. Старые иерархические и сетевые базы данных, в которых приходилось иметь дело с подобными вопросами реализации, имели громоздкую структуру и были сложными в управлении. 1.2. Взаимодействие SQL и СУБД. Увеличение объема и структурной сложности хранимых данных, расширение круга пользователей информационных систем привели к широкому распространению наиболее удобных и сравнительно
простых для понимания реляционных табличных СУБД. Для обеспечения одновременного доступа к данным множества пользователей, нередко расположенных достаточно далеко друг от друга и от места хранения баз данных, созданы сетевые мультипользовательские версии СУБД. В них тем или иным путем решаются специфические проблемы параллельных процессов, целостности правильности и безопасности данных, а также санкционирования доступа.
SQL стал унифицированным средством общения и стандартным языком манипулирования с базами данных, обладающим средствами для реализации перечисленных выше возможностей. После появления на рынке двух пионерских СУБД – SQL DS 1981 год и DB2 1983 год – он приобрел статус стандарта де-факто для профессиональных реляционных СУБД. В 1987 году SQL стал официальным международным стандартом языка баз данных, а в 1992 году вышла вторая версия этого стандарта. Важной отличительной чертой SQL является его независимость от компьютерной среды операционной системы и архитектуры . Такой язык назвали SQL – это аббревиатура структурированного языка запросов Structured Query Language . SQL является инструментом, предназначенным для обработки и чтения информации, содержащейся в компьютерной базе данных. При создании языка запросов нового поколения разработчики
старались сделать его простым и легким в освоении инструментом для обращения к БД. В итоге SQL стал слабо структурированным языком, особенно по сравнению с такими языками, как С или Pascal, и в то же время достаточно мощным и относительно легким для изучения. 1.3. Стандарты SQL. Сегодняшнее состояние. Одним из наиболее важных шагов на пути к признанию SQL на рынке стало появление стандартов на этот язык.
Обычно при упоминании стандарта SQL имеют в виду официальный стандарт, утвержденный Американским институтом национальных стандартов American National Standards Institute – ANSI и Международной организацией по стандартам International Standards Organization- ISO . Однако существуют и другие важные стандарты SQL, включая SQL, реализованный в системе DB2 компании
IBM, и стандарт X OPEN для SQL в среде UNIX. Работа над официальным стандартом SQL началась в 1982 году, когда ANSI поставил перед своим комитетом ХЗН2 задачу по созданию стандарта языка реляционных баз данных. Вначале в комитете обсуждались достоинства различных предложенных языков. Однако поскольку к тому времени SQL уже стал фактическим стандартом, комитет
ХЗН2 остановил свой выбор на нем и занялся стандартизацией SQL. Разработанный в результате стандарт в большой степени был основан на диалекте SQL системы DB2, хотя и содержал в себе ряд существенных отличий от этого диалекта. После нескольких доработок, в 1986 году стандарт был официально утвержден как стандарт ANSI номер Х3.135, а в 1987 году – в качестве стандарта ISO. Затем стандарт ANSI ISO был принят правительством США как федеральный стандарт США по обработке информации FIPS – Federal Information Processing Standard . Этот стандарт, незначительно пересмотренный в 1989 году, обычно называют стандартом SQL-89 , или SQLI . Когда в данном реферате я упоминаю стандарт ANSI ISO , то подразумеваю
SQLI, который в настоящее время лежит в основе большинства коммерческих продуктов. Многие из членов комитетов по стандартизации ANSI и ISO представляли фирмы-поставщики различных СУБД, в каждой из которых был реализован собственный вариант SQL. Как и диалекты человеческого языка, диалекты SQL были в основном похожи друг на друга, однако несовместимы в деталях. Во многих случаях комитет просто обошел существующие различия и не стандартизировал некоторые
части языка, определив, что они реализуются по усмотрению разработчика. Этот подход позволил объявить большое число реализаций SQL совместимыми со стандартом, однако сделал сам стандарт относительно слабым. Чтобы заполнить эти пробелы, комитет ANSI продолжил свою работу и создал проект нового, более жесткого стандарта SQL2. В отличие от стандарта 1989 года, проект
SQL2 предусматривал возможности, выходящие за рамки таковых, уже существующих в реальных коммерческих продуктах. А для следующего за ним стандарта SQL3 были предложены еще более глубокие изменения. В результате предложенные стандарты SQL2 и SQL3 оказались более противоречивыми, чем исходный стандарт. Стандарт SQL2 прошел процесс утверждения в ANSI и был окончательно принят в октябре 1992 года. В то время, как первый стандарт 1986 года занимает не более ста страниц, официальный стандарт
SQL2 содержит около шестисот. Вопреки стандарту SQL2, во всех существующих на сегодняшний день коммерческих продуктах поддерживаются собственные диалекты SQL. Более того, поставщики СУБД включают в свои продукты новые возможности и расширяют собственные диалекты SQL, чем еще больше отдаляют их от стандарта. Однако ядро SQL стандартизировано довольно жестко. Там, где это можно было сделать, не ущемляя интересы клиентов, поставщики СУБД привели свои продукты в соответствие со стандартом SQL-89, то же самое постепенно произошло и с SQL2. Хотя стандарт ANSI ISO наиболее широко распространен, он не является единственным стандартом SQL. Европейская группа поставщиков X OPEN также приняла SQL в качестве одного из своих стандартов для среды переносимых приложений на основе
UNIX. Стандарты группы X OPEN играют важную роль на европейском компьютерном рынке, где основной проблемой является переносимость приложений между компьютерными системами различных производителей. К несчастью, стандарт X OPEN отличается от стандарта ANSI ISO. Кроме того, компания IBM включила SQL в свою спецификацию Systems Application Architecture архитектура прикладных систем и пообещала, что все ее продукты, очевидно,
будут переведены на этот диалект SQL. Хотя данная спецификация и не оправдала надежд на унификацию линии продуктов компании IBM, движение в сторону унификации SQL в IBM продолжается. Система DB2 остается основной СУБД компании IBM для мэйнфреймов. Однако компания выпустила реализацию DB2 и для OS 2 собственной операционной системы для персональных компьютеров , и для линии серверов
и рабочих станций RS 6000, работающих под управлением UNIX. Таким образом, диалект DB2 языка SQL является мощным стандартом де-факто. В технологии баз данных существует важная область, которую не затрагивают официальные стандарты. Это способность к взаимодействию с другими базами данных – методы, с помощью которых различные БД могут обмениваться информацией как правило, по сети .
В 1989 году несколько поставщиков сформировали консорциум SQL Access Group специально для решения этой проблемы. В 1991 году консорциум опубликовал спецификацию RDA Remote Database Access – удаленный доступ к базам данных . Эта спецификация тесно связана с протоколами OSI, которые не смогли завоевать широкого признания, поэтому она оказывает на рынок незначительное влияние. Прозрачность взаимодействия между различными базами данных остается иллюзорной мечтой. Тем не менее, второй стандарт от SQL Access Group имеет на рынке больший вес. В результате настойчивых требований компании Microsoft, консорциум SQL Access Group включил в стандарт SQL интерфейс вызовов функций. Полученная спецификация
CLI Call Level Interface , основанная на разработках компании Microsoft, увидела свет в 1992 году. В этом же году была опубликована собственная спецификация ODBC Open Database Connectivity – взаимодействие с открытыми базами данных компании Microsoft, основанная на стандарте CLI. Благодаря рыночной силе Microsoft и благословению, полученному открытым стандартом от
SQL Access Group, ODBC оказался стандартом де-факто для интерфейсов доступа к базам данных на персональных компьютерах. Весной 1993 года компании Apple и Microsoft объявили о соглашении относительно поддержки ODBC в MacOS и Windows, что закрепило за этой спецификацией статус стандарта в обеих популярных средах с графическим пользовательским интерфейсом. Появление стандарта SQL вызвало довольно много восторженных заявлений о переносимости
SQL и использующих его приложений. На самом деле пробелы в стандарте SQL-89 и различия между существующими диалектами SQL достаточно значительны, и при переводе приложения под другую СУБД его всегда приходится модифицировать. Эти отличия, большинство из которых устранено в стандарте SQL2, включают в себя Коды ошибок. В стандарте SQL-89 не определены коды, которые возвращают операторы
SQL при возникновении ошибок, и в каждой из коммерческих реализаций используется собственный набор таких кодов. В стандарте SQL2 определены стандартные коды ошибок. Типы данных. В стандарте SQL-89 определен минимальный набор типов данных, однако, в нем отсутствуют некоторые из наиболее распространенных и полезных типов, например символьные строки переменной длины, дата и время, а также денежные единицы. В стандарте SQL2 упомянуты эти типы данных, однако, отсутствуют новые типы данных, такие как графические и мультимедийные объекты. Системные таблицы. В стандарте SQL-89 умалчивается о системных таблицах, в которых содержится информация о структуре самой базы данных. Поэтому каждый поставщик создавал собственные системные таблицы, и их структура отличается даже в четырех реализациях SQL компании IBM. Системные таблицы стандартизированы в
SQL2. Интерактивный SQL. В стандарте определен только программный SQL, используемый прикладной программой, но не интерактивный SQL. Например, оператор select, предназначенный для выполнения запросов к базе данных в интерактивном режиме, в стандарте отсутствует. Программный интерфейс. В первом стандарте определен абстрактный способ использования
SQL в программах, написанных на таких языках программирования, как COBOL, FORTRAN и другие. Этот способ не используется ни в одном коммерческом продукте, а в существующих программных интерфейсах имеются значительные отличия. В стандарте SQL2 определен интерфейс встроенного SQL для популярных языков программирования, но не интерфейс вызова функций. Динамический SQL. В стандарте
SQL-89 не описаны элементы SQL, необходимые для разработки приложений общего назначения, таких как генераторы отчетов и программы создания и выполнения запросов. Однако эти элементы, известные под названием динамический SQL, имеются почти во всех СУБД и в различных системах значительно отличаются. В SQL2 входит стандарт динамического SQL. Семантические отличия.
Поскольку некоторые элементы определены в стандартах как зависящие от реализации, может возникнуть ситуация, когда в результате выполнения одного и того же запроса в двух совместимых СУБД будут получены два различных набора результатов. Отличия результатов обусловлены различиями в обработке значений null, разными агрегатными функциями и несовпадением процедур удаления повторяющихся строк. Последовательность сравнения. В стандарте SQL-89 не упоминаются последовательности сравнения сортировки символов, хранящихся в базе данных. Результаты запроса с сортировкой будут отличаться при выполнении этого запроса на персональном компьютере с кодировкой ASCII и на мэйнфрейме с кодировкой EBCDIC . Стандарт SQL2 позволяет программе или пользователю указывать требуемую последовательность сортировки.
Структура базы данных. В стандарте SQL-89 определен SQL, который используется уже после того, как база данных была открыта и подготовлена к работе. Детали наименования баз данных и первоначального подключения к ним сильно отличаются и несовместимы. Стандарт SQL2 в некоторой степени унифицирует этот процесс, но не может полностью ликвидировать все отличия. Вопреки перечисленным различиям, в начале 90-х годов стали появляться коммерческие программы,
реализующие переносимость приложений между различными СУБД. Однако в таких программах для каждой из поддерживаемых СУБД требуется специальный конвертер, который генерирует код в соответствии с определенным диалектом SQL, выполняет преобразование типов данных, транслирует коды ошибок и т.д. Прозрачная переносимость между различными СУБД, использующими
SQL, является основной целью стандарта SQL2 и протокола ODBC. Однако повсеместный, прозрачный и унифицированный доступ к базам данных SQL остается делом будущего. Технологии, обеспечивающие сетевой доступ к базам данных Всемирная Паутина недаром так быстро завоевала широкую популярность среди пользователей Internet, в мире бизнеса, науки, политики и т. д. Основные достижения
Web – это простота опубликования информации в сети, удобство и сравнительная унифицированность доступа к документам, наличие на сегодняшний день достаточно развитых средств поиска. Однако в целом способы представления, хранения и поиска информации в WWW относятся к категории информационно-поисковых систем ИПС . Хотя хранилища данных в узлах Web иногда называют базами данных, этот термин в данном случае можно использовать только в самом широком смысле. Исторически ИПС применялись для хранения слабоструктурированной и редко изменяемой информации. Базы данных в узком смысле – это хранилища структурированной, изменяемой информации, причем информация в базе данных должна всегда находиться в согласованном состоянии. С равным успехом можно хвалить и ругать Web. Можно хвалить
Всемирную Паутину за то, что, не выходя из дома, вы можете побывать в любой точке земного шара и посмотреть, что же там происходит. Можно ругать Web за то, что трудно найти действительно актуальную информацию обычно она устаревшая , за то, что хранилища информации содержат очень много мусора , опубликованного непонятно из каких соображений. Но в любом случае интерфейс действительно удобен. Ситуация с базами данных кардинально отличается. Именно базы данных содержат основные знания человечества.
В конце двадцатого века с появлением технологии баз данных мы накопили больше информации, чем за всю предыдущую историю. Вся беда в том, что доступ к базам данных даже к тем, которые содержат полностью открытую информацию ограничен. Чтобы получить интересующую его информацию, пользователь должен иметь физический доступ к соответствующей СУБД, быть в курсе модели данных, знать схему базы данных и, наконец, уметь пользоваться соответствующим языком запросов.
Что касается языка запросов, то проблему частично решает протокол ODBC, позволяющий направлять ограниченный набор операторов SQL с промежуточной обработкой соответствующим драйвером ODBC к произвольному серверу баз данных. Но это только частичное решение, поскольку оно никак не помогает пользователю понять схему базы данных даже в терминах SQL и, конечно, не способствует созданию унифицированного интерфейса конечного пользователя нельзя же заставить всех работать в строчном режиме на языке SQL . Итак, мы имеем удобные средства разработки распределенных в Internet гипермедийных документов, простые, удобные, развитые и унифицированные интерфейсы для доступа к информации WWW. Кроме того, мы имеем большое количество ценных баз данных, управляемых разнородными
СУБД, а также желание сделать эти базы доступными всем людям в случае публичных баз данных или членам территориально-распределенной корпорации в случае корпоративных баз данных . Возникает естественное желание скрестить эти две технологии и обеспечить доступ к базам данных в интерфейсе Web. Еще два года назад существовали только идеи такого скрещивания и не очень тщательно разработанные подходы к реализации. На сегодняшний день такие механизмы уже существуют и используются.
Принципы работы SQL-сервера SQL является инструментом, предназначенным для обработки и чтения данных, содержащихся в компьютерной базе данных. SQL структурированный язык запросов как следует из названия, является языком программирования, который применяется для организации взаимодействия пользователя с базой данных. На самом деле SQL работает только с базами данных одного определенного типа, называемых реляционными. На рисунке 2.1 изображена схема работы
SQL. Согласно этой схеме, в вычислительной системе имеется база данных, в которой хранится важная информация. Если БД относится к сфере бизнеса, то в ней может храниться информация о материальных ценностях, выпускаемой продукции, объемах продаж и зарплате. В базе данных на персональном компьютере может храниться информация о выписанных чеках, телефонах и адресах или информация, извлеченная из более крупной вычислительной системы. Компьютерная программа, которая управляет базой данных, называется системой управления базой данных, или СУБД. Если пользователю необходимо прочитать данные из базы данных, он запрашивает их у SQL с помощью СУБД. SQL обрабатывает запрос, находит требуемые данные и посылает их пользователю. Процесс запрашивания данных и получения результата называется запросом к базе данных отсюда и название – структурированный язык запросов. Однако это название не совсем соответствует действительности. Cегодня SQL представляет собой нечто гораздо большее, чем простой инструмент создания запросов, хотя
именно для этого он и был первоначально предназначен. Несмотря на то, что чтение данных по-прежнему остается одной из наиболее важных функций SQL, сейчас этот язык используется для реализации всех функциональных возможностей, которые СУБД предоставляет пользователю, а именно Организация данных. SQL дает пользователю возможность изменять структуру представления данных, а также устанавливать отношения
между элементами базы данных. Чтение данных. SQL дает пользователю или приложению возможность читать из базы данных содержащиеся в ней данные и пользоваться ими. Обработка данных. SQL дает пользователю или приложению возможность изменять базу данных, т.е. добавлять в нее новые данные, а также удалять или обновлять уже имеющиеся в ней данные. Управление доступом. С помощью SQL можно ограничить возможности пользователя по чтению и изменению
данных и защитить их от несанкционированного доступа. Совместное использование данных. SQL координирует совместное использование данных пользователями, работающими параллельно, чтобы они не мешали друг другу. Целостность данных. SQL позволяет обеспечить целостность базы данных, защищая ее от разрушения из-за несогласованных изменений или отказа системы. Таким образом, СУБД является достаточно мощным средством для взаимодействия с SQL. Основными объектами реляционной базы данных являются TABLE Таблица Прямоугольная таблица, состоящая из СТРОК и СТОЛБЦОВ. Задать таблицу – значит указать, из каких столбцов она состоит. ROW Строка Запись, состоящая из полей – столбцов. В каждом поле содержится его значение, либо значение
NULL – пусто . Строк в таблице может быть сколько угодно. Физический порядок их расположения друг относительно друга неопределен. COLUMN Столбец Каждый столбец в таблице имеет собственные имя и тип. Таблицы SQL В реляционной базе данных информация организована в виде таблиц, разделённых на строки и столбцы, на пересечении которых содержатся значения данных.
Используемые в языке SQL для запросов сочетания ключей CREATE TABLE my table – создание таблицы с названием my table получили название предложение . Таблицы создаются в SLQ с помощью предложения CREATE TABLE. Предложение CREAT TABLE специфицирует имя базовой таблицы, которая должна быть создана, имена ее столбцов и типы данных для этих столбцов. CREAT
TABLE – выполняемое предложение. Если SQL-серверу дать запрос CREATE TABLE, система построит таблицу, которая сначала будет пустой она будет содержать только строку заголовков столбцов, но не будет еще содержать никаких строк с данными. Информация в таблицу вставляется при помощи предложения команды INSERT Структура запросов SQL. Все запросы на получение практически любых данных из одной или нескольких
таблиц выполняются с помощью единственного предложения 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.2. Запросы с использованием единственной таблицы. Выборка без использования фразы WHERE Простая выборка
Запрос выдать название, статус и адрес поставщиков SELECT Название, Статус, Адрес FROM Поставщики дает результат, приведенный на рис. 2.2,а. При необходимости получения полной информации о поставщиках, можно было бы дать запрос SELECT ПС, Название, Статус, Город, Адрес, Телефон FROM Поставщики или использовать его более короткую нотацию
SELECT FROM Поставщики Здесь звездочка служит кратким обозначением всех имен полей в таблице, указанной во фразе FROM. При этом порядок вывода полей соответствует порядку, в котором эти поля определялись при создании таблицы. Еще один пример. Выдать основу всех блюд SELECT Основа FROM Блюда дает результат, показанный на рис. 2.2,б. а б в Название Статус Адрес Основа Основа Овощи
Кофе Мясо Крупа СЫТНЫЙ Рынок Сытнинская, 3 Овощи Молоко ПОРТОС Кооператив Садовая, 27 Рыба Мясо ШУШАРЫ Совхоз Новая, 17 Рыба Овощи ТУЛЬСКИЙ Универсам Тульская, 3 Мясо Рыба УРОЖАЙ Коопторг Песчаная, 19 Молоко Фрукты ЛЕТО Агрофирма Пулковское ш 8 Молоко Яйца ОГУРЕЧИК Ферма Укмерге, 15 КОРЮШКА Кооператив Нарвское ш 64 Кофе Рисунок 1.2 Исключение дубликатов В предыдущем примере был выдан правильный, но не совсем удачный перечень основных продуктов из него не были исключены дубликаты. Для исключения дубликатов и одновременного упорядочения перечня необходимо дополнить запрос ключевым словом DISTINCT различный, различные , как показано в следующем примере
SELECT DISTINCT Основа FROM Блюда Результат приведен на рис. 2.2,в. Выборка вычисляемых значений Из синтаксиса фразы SELECT видно, что в ней может содержаться не только перечень столбцов таблицы или символ , но и выражения. Например, если нужно получить значение калорийности всех продуктов, то можно учесть, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров –
9.3 ккал, и выдать запрос SELECT Продукт, Белки Углев 4.1 Жиры 9.3 FROM Продукты результат которого приведен на рис. 2.3,а. а Б в Продукт Продукт Продукт Говядина 1928.1 Говядина Калорий 1928.1 Зелень 118.9 Судак 1523. Судак Калорий 1523. Помидоры 196.8 Масло 8287.5 Масло Калорий 8287.5
Морковь 349.6 Майонез 6464.7 Майонез Калорий 6464.7 Лук 459.2 Яйца 1618.9 Яйца Калорий 1618.9 Яблоки 479.7 Сметана 3011.4 Сметана Калорий 3011.4 Молоко 605.1 Молоко 605.1 Молоко Калорий 605.1 Кофе 892.4 Творог 1575. Творог Калорий 1575. Судак 1523. Морковь 349.6 Морковь
Калорий 349.6 Творог 1575. Лук 459.2 Лук Калорий 459.2 Яйца 1618.9 Помидоры 196.8 Помидоры Калорий 196.8 Говядина 1928.1 Зелень 118.9 Зелень Калорий 118.9 Сметана 3011.4 Рис 3512.1 Рис Калорий 3512.1 Рис 3512.1 Мука 3556.7 Мука Калорий 3556.7 Мука 3556.7 Яблоки 479.7 Яблоки Калорий 479.7 Сахар 4091.8 Сахар 4091.8 Сахар Калорий 4091.8 Майонез 6464.7 Кофе 892.4 Кофе Калорий 892.4 Масло 8287.5 Рисунок 2.2 Фраза SELECT может включать не только выражения, но и отдельные числовые или текстовые константы. Следует отметить, что текстовые константы должны заключаться в апострофы . На рис. 2.3,б приведен результат запроса SELECT Продукт, Калорий , Белки Углев 4.1 Жиры 9.3 FROM Продукты А что произойдет, если какой-либо член выражения не
определен, т.е. имеет значение NULL и каким образом появилось такое значение? Если при загрузке строк таблицы в какой-либо из вводимых строк отсутствует значение для какого-либо столбца, то СУБД введет в такое поле NULL-значение. NULL-значение придумано для того, чтобы представить единым образом неизвестные значения для любых типов данных. Действительно, так как при вводе данных в столбец или их изменении
СУБД запрещает ввод значений не соответствующих описанию данных этого столбца, то, например, нельзя использовать пробел для отсутствующего значения числа. Нельзя для этих целей использовать и ноль нет месяца или дня недели равного нулю, да и для чисел ноль не может рассматриваться как неизвестное значение в одном месте и как известное – в другом. При выводе же NULL-значения на экран или печатающее устройство его код воспроизводится каким-либо специально
заданным символом или набором символов например, пробелом если его нельзя перепутать с текстовым значением пробела или сочетанием -0 С помощью специальной команды можно установить в СУБД один из режимов представления NULL-значений при выполнении числовых расчетов запрет или разрешение замены NULL-значения нулем. В первом случае любое арифметическое выражение, содержащее неопределенный операнд, будет также иметь неопределенное значение. Во втором случае результат вычислений будет иметь численное значение если это значение попадает в диапазон представления соответствующего типа данных . Например, при выполнении запроса SELECT ПР, Цена, К во, Цена К во FROM Поставки и разных настройках СУБД могут быть получены разные результаты ПР Цена К во Цена К во ПР Цена К во Цена К во 9 -0- -0- -0- 9 -0- -0- 0. 11 1.5 50 75. 11 1.5 50 75. 12 3. 10 30. 12 3. 10 30. 15 2. 170 340. 15 2. 170 340.
Использование 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.4 , величина которых непосредственно связана со студенческой стипендией. В этой таблице для текущего значения минимального оклада установлена запредельная дата окончания 9 сентября 9999 года. Миноклад Начало Конец 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
Рисунок 2.3 Если, например, потребовалось узнать, какие изменения минимальных окладов производились в 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 Использование 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 Основа Овощи Использование LIKE Выдать перечень салатов Результат SELECT Блюдо FROM Блюда WHERE Блюдо LIKE
Салат Блюдо Салат летний Салат мясной Салат витаминный Салат рыбный Обычная форма имя столбца LIKE текстовая константа для столбца текстового типа позволяет отыскать все значения указанного столбца, соответствующие образцу, заданному текстовой константой . Символы этой константы интерпретируются следующим образом символ подчеркивание – заменяет любой одиночный символ, символ процент – заменяет любую последовательность из N символов где N может быть нулем , все другие символы означают просто сами себя. Следовательно, в приведенном примере SELECT будет осуществлять выборку записей из таблицы Блюда, для которых значение в столбце Блюдо начинается сочетанием Салат и содержит любую последовательность из нуля или более символов, следующих за сочетанием Салат . Если бы среди блюд были Луковый салат , Фруктовый салат и т.п то они не были бы найдены.
Для их отыскания надо изменить фразу WHERE WHERE Блюдо LIKE салат или при отсутствии различий между малыми и большими буквами такую настройку допускают некоторые СУБД WHERE Блюдо LIKE Салат Это позволит отыскать все салаты. Вовлечение неопределенного значения NULL-значения Если при загрузке данных не введено значение в какое-либо поле таблицы, то
СУБД поместит в него NULL-значение. Аналогичное значение можно ввести в поле таблицы, выполняя операцию изменения данных. Так, при отсутствии сведений о наличии у поставщиков судака и моркови в столбцы Цена и К во соответствующих строк таблицы Поставки вводится NULL и там будет храниться код NULL-значения, а не 0, 0. Или пробел. Отметим, что в распечатке таблицы Поставки в этих местах расположен пробел, установленный
в СУБД для представления 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-значения. Выборка с упорядочением Простейший вариант этой фразы – упорядочение строк результата по значению одного из столбцов с указанием порядка сортировки или без такого указания. По умолчанию строки будут сортироваться в порядке возрастания значений в указанном столбце. Например, выдать перечень продуктов и содержание в них основных веществ в порядке убывания содержания белка 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.3,в – переупорядоченный по возрастанию значений калорийности список рис.2.3,а. Агрегирование данных SQL-функции В SQL существует ряд специальных стандартных функций SQL-функций . Кроме специального случая COUNT каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так COUNT число значений в столбце, SUM сумма значений в столбце, AVG среднее значение в столбце, MAX самое большое значение в столбце,
MIN самое малое значение в столбце. Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения. Следует отметить, что здесь столбец – это столбец виртуальной таблицы, в которой могут содержаться данные не только из столбца базовой таблицы, но и данные, полученные путем функционального преобразования и или связывания символами арифметических операций значений из одного или нескольких столбцов.
При этом выражение, определяющее столбец такой таблицы, может быть сколь угодно сложным, но не должно содержать SQL-функций вложенность SQL-функций не допускается . Однако из SQL-функций можно составлять любые выражения. Аргументу всех функций, кроме COUNT , может предшествовать ключевое слово DISTINCT различный , указывающее, что избыточные дублирующие значения должны быть исключены перед тем,
как будет применяться функция. Специальная же функция COUNT служит для подсчета всех без исключения строк в таблице включая дубликаты . Функции без использования фразы 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 Фраза GROUP BY Мы показали, как можно вычислить массу определенного продукта, поставляемого поставщиками. Предположим, что теперь требуется вычислить общую массу каждого из продуктов, поставляемых в настоящее время поставщиками. Это можно легко сделать с помощью предложения SELECT ПР, SUM К во FROM Поставки GROUP BY ПР Результат показан на рис. 2.5,а. а б в г ПР ПС ПР Цена К во ПР ПР 9 0 1 9 -0- -0- 1 370 9 0 11 150 3 9 -0- -0- 2 0 11 150 12 30 5 9 -0- -0- 3 250 12 30 15 370 1 11 1.50 50 4 100 15 70 1 370 5 11 -0- -0- 5 170 1 370 3 250 6 11 -0- -0- 6 220 3 250 5 170 8 11 1.00 100 7 200 5 70 6 220 1 12 3.00 10 8 150 6 140 8 150 3 12 2.50 20 9 0 8 150 7 200 6 12
-0- -0- 10 220 7 200 2 0 1 15 2.00 170 11 150 2 0 4 100 3 15 1.50 200 12 30 4 100 13 190 2 1 3.60 300 13 190 13 190 14 70 7 1 4.20 70 14 70 14 70 16 250 2 3 -0- -0- 15 370 16 250 17 50 7 3 4.00 250 16 250 17 50 10 220 17 50 10 220 Рисунок 2.4 Фраза GROUP BY группировать по инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы
Поставки группируются так, что в одной группе содержатся все строки для продукта с ПР 1, в другой – для продукта с ПР 2 и т.д. см. рис. 2.5,б . Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение,
либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению например, к сумме . Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по ПР результата рассматриваемого примера рис. 2.5,в следует дать запрос 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 Использование фразы
HAVING Фраза HAVING рис. 2.3 играет такую же роль для групп, что и фраза WHERE для строк она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы. Например, выдать коды продуктов, поставляемых более чем двумя поставщиками
SELECT FROM Поставки GROUP BY ПС HAVING COUNT 2 Результат ПР 9 11 12 2.2.3. Использование запросов с использованием нескольких таблицы. О средствах одновременной работы с множеством таблиц Затрагивая вопросы проектирования баз данных, мы выяснили, что базы данных – это множество взаимосвязанных сущностей или отношений таблиц в терминологии реляционных
СУБД. При проектировании стремятся создавать таблицы, в каждой из которых содержалась бы информация об одном и только об одном типе сущностей. Это облегчает модификацию базы данных и поддержание ее целостности. Но такой подход тяжело усваивается начинающими проектантами, которые пытаются привязать проект к будущим приложениям и так организовать таблицы, чтобы в каждой из них хранилось все необходимое для реализации возможных запросов. Типичен вопрос как же получить сведения о том, где купить продукты для приготовления
того или иного блюда и определить его калорийность и стоимость, если нужные данные рассыпаны по семи различным таблицам? Не лучше ли иметь одну большую таблицу, содержащую все сведения базы данных ПАНСИОН ? Даже при отсутствии средств одновременного доступа ко многим таблицам нежелателен проект, в котором информация о многих типах сущностей перемешана в одной таблице. 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. ОГУРЕЧИК Ферма Здесь с помощью подзапроса, размещенного в трех последних строках
запроса, описывается процесс определения минимальной цены каждого продукта для Сырников и поиск поставщика, предлагающего этот продукт за такую цену. Запросы, использующие соединения Декартово произведение таблиц Так как декартово произведение 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 Меню, Трапезы, Вид блюд, Блюда образуется таблица рис 2.6 , содержащая 21 х 3 х 5 х 33 10395
строк. Эквисоединение таблиц Если из декартова произведения убрать ненужные строки и столбцы, то можно получить актуальные таблицы, соответствующие любому из соединений. Меню Трапезы Вид блюд Блюда Т В БЛ Т Трапеза В Вид БЛ Блюдо В Основа Выход Труд 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 Рисунок 2.5 Очевидно, что отбор актуальных строк обеспечивается вводом в запрос 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 Естественное соединение таблиц Легко заметить, что в эквисоединение таблиц вошли дубликаты столбцов, по которым проводилось соединение Т, В и БЛ . Для исключения этих дубликатов можно создать естественное соединение тех же таблиц 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 Композиция таблиц Для исключения всех столбцов, по которым проводится соединение таблиц, надо создать композицию
SELECT Трапеза, Вид, Блюдо, Основа, Выход, Труд FROM Меню, Трапезы, Вид блюд, Блюда WHERE Меню.Т Трапезы.Т AND Меню.В Вид блюд.В AND Меню.БЛ Блюда.БЛ имеющую вид Трапеза Блюдо Вид Основа Выход Труд Завтрак Салат витаминный Закуска Овощи 200. 4 Завтрак Мясо с гарниром Закуска Мясо 250. 3 Завтрак Омлет с луком Горячее Яйца 200. 5 Ужин Драчена Горячее Яйца 180. 4 Ужин Компот Напиток Фрукты 200. 2 Ужин Молочный напиток Напиток Молоко 200. 2 Тета-соединение таблиц В базе данных ПАНСИОН трудно подобрать несложный пример, иллюстрирующий тета-соединение таблиц. Поэтому сконструируем такой надуманный запрос
SELECT Вид блюд Трапезы. FROM Вид блюд, Трапезы WHERE Вид Трапеза позволяющий выбрать из полученного декартова произведения таблиц Вид блюд и Трапезы лишь те строки, в которых значение трапезы меньше по алфавиту значения вида блюда В Вид Т Трапеза З Закуска 1 Завтрак С Суп 1 Завтрак С Суп 2 Обед Н Напиток 1 Завтрак Соединение таблиц с дополнительным условием
При формировании соединения создается рабочая таблица, к которой применимы все операции отбор нужных строк соединения WHERE фраза , упорядочение получаемого результата ORDER BY фраза и агрегатирование данных SQL-функции и GROUP BY фраза . Например, для получения перечня блюд, предлагаемых в меню на завтрак, можно сформировать запрос на основе композиции SELECT Вид, Блюдо, Основа,
Выход, Номер БЛ FROM Меню, Трапезы, Вид блюд, Блюда WHERE Меню.Т Трапезы.Т AND Меню.В Вид блюд.В AND Меню.БЛ Блюда.БЛ AND Трапеза Завтрак Вид Блюдо Основа Выход Номер – БЛ Закуска Салат витаминный Овощи 200. Номер – 3 Закуска Мясо с гарниром Мясо 250. Номер – 6 Горячее
Омлет с луком Яйца 200. Номер – 19 Горячее Пудинг рисовый Крупа 160. Номер – 21 Напиток Молочный напиток Молоко 200. Номер – 31 Напиток Кофе черный Кофе 100. Номер – 32 Соединение таблицы со своей копией В ряде приложений возникает необходимость одновременной обработки данных какой-либо таблицы и одной или нескольких ее копий, создаваемых на время выполнения запроса. Например, при создании списков студентов таблица Студенты возможен повторный ввод данных о каком-либо студенте с присвоением ему второго номера зачетной книжки. Для выявления таких ошибок можно соединить таблицу Студенты с ее временной копией, установив в WHERE фразе равенство значений всех одноименных столбцов этих таблиц кроме столбцов с номером зачетной книжки для последних надо установить условие неравенства
значений . Временную копию таблицы можно сформировать, указав имя псевдонима за именем таблицы во фразе FROM. Так, с помощью фразы FROM Блюда X, Блюда Y, Блюда Z будут сформированы три копии таблицы Блюда с именами X, Y и Z. В качестве примера соединения таблицы с ней самой сформируем запрос на вывод таких пар блюд таблицы Блюда, в которых совпадает основа, а название первого блюда пары меньше по алфавиту , чем номер
второго блюда пары. Для этого можно создать запрос с одной копией таблицы Блюда Копия SELECT Блюдо, Копия.Блюдо, Основа FROM Блюда, Блюда Копия WHERE Основа Копия.Основа AND Блюдо Копия.Блюдо или двумя ее копиями Первая и Вторая SELECT Первая.Блюдо, Вторая.Блюдо, Основа FROM Блюда Первая,
Блюда Вторая WHERE Первая.Основа Вторая.Основа AND Первая.Блюдо Вторая.Блюдо Получим результат вида Первая.Блюдо Вторая.Блюдо Основа Морковь с рисом Помидоры с луком Овощи Морковь с рисом Салат летний Овощи Морковь с рисом Салат витаминный Овощи Помидоры с луком Салат витаминный
Овощи Помидоры с луком Салат летний Овощи Салат витаминный Салат летний Овощи Бастурма Бефстроганов Мясо Бастурма Мясо с гарниром Мясо Бефстроганов Мясо с гарниром Мясо Вложенные подзапросы Виды вложенных подзапросов Вложенный подзапрос – это подзапрос, заключенный в круглые скобки и вложенный в WHERE HAVING фразу предложения SELECT или других предложений, использующих WHERE фразу. Вложенный подзапрос может содержать в своей WHERE HAVING фразе другой вложенный подзапрос и т.д. Нетрудно догадаться, что вложенный подзапрос создан для того, чтобы при отборе строк таблицы, сформированной основным запросом, можно было использовать данные из других таблиц например, при отборе блюд для меню использовать данные о наличии продуктов в кладовой
пансионата . Существуют простые и коррелированные вложенные подзапросы. Они включаются в WHERE HAVING фразу с помощью условий IN, EXISTS или одного из условий сравнения . Простые вложенные подзапросы обрабатываютя системой снизу вверх . Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса
более высокого уровня и т.д. Запросы с коррелированными вложенными подзапросами обрабатываются системой в обратном порядке. Сначала выбирается первая строка рабочей таблицы, сформированной основным запросом, и из нее выбираются значения тех столбцов, которые используются во вложенном подзапросе вложенных подзапросах . Если эти значения удовлетворяют условиям вложенного подзапроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т.д пока в результат не будут включены все строки, удовлетворяющие
вложенному подзапросу последовательности вложенных подзапросов . Следует отметить, что SQL обладает большой избыточностью в том смысле, что он часто предоставляет несколько различных способов формулировки одного и того же запроса. Поэтому во многих примерах данной главы будут использованы уже знакомые нам по предыдущей главе концептуальные формулировки запросов. И несмотря на то, что часть из них успешнее реализуется с помощью соединений, здесь все же будут приведены их варианты с использованием вложенных подзапросов. Это связано с необходимостью детального знакомства с созданием и принципом выполнения вложенных подзапросов, так как существует немало задач особенно на удаление и изменение данных , которые не могут быть реализованы другим способом. Кроме того, разные формулировки одного и того же запроса требуют для своего выполнения различных ресурсов памяти и могут значительно отличаться по времени реализации в разных
СУБД. Простые вложенные подзапросы Простые вложенные подзапросы используются для представления множества значений, исследование которых должно осуществляться в каком-либо предикате IN, что иллюстрируется в следующем примере выдать название и статус поставщиков продукта с номером 11, т.е. помидоров. Результат SELECT Название, Статус FROM Поставщики WHERE ПС IN SELECT ПС FROM Поставки WHERE
ПР 11 Название Статус СЫТНЫЙ рынок УРОЖАЙ коопторг ЛЕТО агрофирма КОРЮШКА кооператив При обработке полного запроса система выполняет прежде всего вложенный подзапрос. Этот подзапрос выдает множество номеров поставщиков, которые поставляют продукт с кодом ПР 11, а именно множество 1, 5, 6, 8 . Поэтому первоначальный запрос эквивалентен такому простому запросу SELECT Название, Статус FROM Поставщики WHERE ПС IN 1, 5, 6, 8
Подзапрос с несколькими уровнями вложенности можно проиллюстрировать на том же примере. Пусть требуется узнать не поставщиков продукта 11, как это делалось в предыдущих запросах, а поставщиков помидоров, являющихся продуктом с номером 11. Для этого можно дать запрос SELECT Название, Статус FROM Поставщики WHERE ПС IN SELECT ПС FROM Поставки WHERE ПР IN SELECT ПР FROM Продукты WHERE Продукт Помидоры В данном случае результатом самого внутреннего подзапроса является только одно значение 11 . Как уже было показано выше, подзапрос следующего уровня в свою очередь дает в результате множество 1, 5, 6, 8 . Последний, самый внешний SELECT, вычисляет приведенный выше окончательный результат. Вообще допускается любая глубина вложенности подзапросов. Тот же результат можно получить с помощью соединения
SELECT Название, Статус FROM Поставщики, Поставки, Продукты WHERE Поставщики.ПС Поставки.ПС AND Поставки.ПР Продукты.ПР AND Продукт Помидоры При выполнении этого компактного запроса система должна одновременно обрабатывать данные из трех таблиц, тогда как в предыдущем примере эти таблицы обрабатываются поочередно. Естественно, что для их реализации тебуются различные ресурсы памяти и времени, однако этого невозможно
ощутить при работе с ограниченным объемом данных в иллюстративной базе ПАНСИОН. Использование одной и той же таблицы во внешнем и вложенном подзапросе Выдать номера поставщиков, которые поставляют хотя бы один продукт, поставляемый поставщиком 6. Результат SELECT DISTINCT ПС FROM Поставки WHERE ПР IN SELECT ПР FROM Поставки WHERE ПС 6 ПС 1 3 5 6 8
Отметим, что ссылка на Поставки во вложенном подзапросе означает не то же самое, что ссылка на Поставки во внешнем запросе. В действительности, два имени Поставки обозначают различные значения. Чтобы этот факт стал явным, полезно использовать псевдонимы, например, X и Y SELECT DISTINCT X.ПС FROM Поставки X WHERE X.ПР IN SELECT Y.ПР FROM Поставки Y WHERE Y.
ПС 6 Здесь X и Y – произвольные псевдонимы таблицы Поставки, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса. Вложенный подзапрос с оператором сравнения, отличным от IN Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6. Результат SELECT ПС FROM Поставщики WHERE Город SELECT Город FROM Поставщики WHERE ПС 6 ПС 1 4 6 В подобных запросах можно использовать и другие операторы сравнения или , однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка. Коррелированные вложенные подзапросы Выдать название и статус поставщиков продукта с номером 11.
SELECT Название, Статус FROM Поставщики WHERE 11 IN SELECT ПР FROM Поставки WHERE ПС Поставщики.ПС Такой подзапрос отличается от обычного тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения Поставщики.ПС а оно изменяется по мере того, как система проверяет различные строки таблицы
Поставщики. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика с номером 1. Тогда значение Поставщики.ПС будет в данный момент имеет значение, равное 1, и система обрабатывает внутренний запрос SELECT ПР FROM Поставки WHERE ПС 1 получая в результате множество 9, 11, 12, 15 .
Теперь система может завершить обработку для поставщика с номером 1. Выборка значений Название и Статус для ПС 1 СЫТНЫЙ и рынок будет проведена тогда и только тогда, когда ПР 11 будет принадлежать этому множеству, что, очевидно, справедливо. Далее система будет повторять обработку такого рода для следующего поставщика и т.д. до тех пор, пока не будут рассмотрены все строки таблицы Поставщики.
Подобные подзапросы называются коррелированными, так как их результат зависит от значений, определенных во внешнем подзапросе. Обработка коррелированного подзапроса, следовательно, должна повторяться для каждого значения извлекаемого из внешнего подзапроса, а не выполняться раз и навсегда. Рассмотрим пример использования одной и той же таблицы во внешнем подзапросе и коррелированном вложенном подзапросе. Выдать номера всех продуктов, поставляемых только одним поставщиком. Результат SELECT DISTINCT X.ПР FROM Поставки X WHERE X.ПР NOT IN SELECT Y.ПР FROM Поставки Y WHERE Y.ПС X.ПС X.ПР 17 Действие этого запроса можно пояснить следующим образом Поочередно для каждой строки таблицы Поставки, скажем X, выделить значение номера продукта ПР , если и только если это значение не входит в некоторую строку,
скажем, Y, той же таблицы, а значение столбца номер поставщика ПС в строке Y не равно его значению в строке X . Отметим, что в этой формулировке должен быть использован по крайней мере один псевдоним – либо X, либо Y. Запросы, использующие EXISTS Квантор EXISTS существует – понятие, заимствованное из формальной логики. В языке SQL предикат с квантором существования представляется выражением
EXISTS SELECT FROM . Такое выражение считается истинным только тогда, когда результат вычисления SELECT FROM является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса. Практически этот подзапрос всегда будет коррелированным множеством. Рассмотрим примеры. Выдать названия поставщиков, поставляющих продукт с номером 11.
Результат SELECT Название FROM Поставщики WHERE EXISTS SELECT FROM Поставки WHERE ПС Поставщики.ПС AND ПР 11 Название СЫТНЫЙ УРОЖАЙ КОРЮШКА ЛЕТО Система последовательно выбирает строки таблицы Поставщики, выделяет из них значения столбцов Название и ПС, а затем проверяет, является ли истинным условие существования, т.е. су-ществует ли в таблице
Поставки хотя бы одна строка со значением ПР 11 и значением ПС, равным значению ПС, выбранному из таблицы Поставщики. Если условие выполняется, то полученное значение столбца Название включается в результат. Предположим, что первые значения полей Название и ПС равны, соответственно, СЫТНЫЙ и 1. Так как в таблице Поставки есть строка с ПР 11 и ПС 1, то значение СЫТНЫЙ должно быть включено в результат. Хотя этот первый пример только показывает иной способ формулировки запроса для задачи, решаемой и другими путями с помощью оператора IN или соединения , EXISTS представляет собой одну из наиболее важных возможностей SQL. Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью
EXISTS. Однако обратное высказывание несправедливо. Выдать название и статус поставщиков, не поставляющих продукт с номером 11. Результат SELECT Название, Статус FROM Поставщики WHERE NOT EXISTS SELECT FROM Поставки WHERE ПС Поставщики.ПС AND ПР 11 Название Статус ПОРТОС кооператив ШУШАРЫ совхоз
ТУЛЬСКИЙ универсам ОГУРЕЧИК ферма Функции в подзапросе Теперь, после знакомства с различными формулировками вложенных подзапросов и псевдонимами легче понять текст и алгоритм реализации запроса на получение тех поставщиков продуктов для Сырников, которые поставляют эти продукты за минимальную цену SELECT Продукт, Цена, Название, Статус FROM Продукты,
Состав, Блюда, Поставки, Поставщики WHERE Продукты.ПР Состав.ПР AND Состав.БЛ Блюда.БЛ AND Поставки.ПР Состав.ПР AND Поставки.ПС Поставщики.ПС AND Блюдо Сырники AND Цена SELECT MIN Цена FROM Поставки X WHERE X.ПР Поставки.ПР Естественно, что это коррелированный подзапрос здесь сначала определяется минимальная цена
продукта, входящего в состав Сырников, и только затем выясняется его поставщик. На этом примере мы закончим знакомство с вложенными подзапросами, предложив попробовать свои силы в составлении ряда запросов, с помощью механизма таких подзапросов Выдать названия всех мясных блюд. Выдать количество всех блюд, в состав которых входят помидоры. Выдать блюда, продукты для которых поставляются агрофирмой ЛЕТО.