Web-доступ к базам данных
Введение
Базы данных выполняют функцию систематизации знаний. На основе этой
систематизации могут создаваться новые знания. Так или иначе, любая база данных
служит человеку именно для описания происшедших в прошлом событий и на основе
знания этих событий помогает принять то или иное решение на будущее. База знаний
может быть построена как мультимедийный справочник или как набор текстов и
файлов другого формата, проиндексированных по определенным признакам в базе
данных.
База данных – это, прежде всего, хранилище объектов данных, т.е. набора
возможных понятий или событий, описываемых базой данных, с возможностью поиска
этих объектов по признакам. Неотъемлемой чертой базы данных является возможность
связывания объектов между собой. Базой данных можно считать не только таблицы,
индексирующие файлы со знаниями разных форматов, но и сами эти файлы, потому,
что они являются не типизированными хранилищами знаний в такой базе данных.
Итак, в базах знаний мы накапливаем опыт прошлого. Потом человек может сам
принять решение на основе этого опыта (типичный случай с мультимедийным
справочником) или поставить задачу перед базой данных по поиску решения согласно
сложившейся ситуации (найти закон, поясняющий правило оформления таможенной
декларации и т.п.). Так происходит в программах справочного характера. Как
частный случай баз данных, можно рассматривать различные структурированные
файлы, например, словари для переводчиков, форматы файлов 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;
многоточие (…) – указывает на то, что непосредственно предшествующая ему
синтаксическая единица факультативно может повторяться один или более раз;
прямая черта () – означает наличие выбора из двух или более возможностей.
Например, обозначение ASCDESC указывает, можно выбрать один из терминов 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_функций – одна из следующих конструкций:
{SUMAVGMINMAXCOUNT} ( [[ALL]DISTINCT][таблица.]столбец )
{SUMAVGMINMAXCOUNT} ( [ALL] выражение )
COUNT(*)
Фраза WHERE включает набор условий для отбора строк:
WHERE [NOT] WHERE_условие [[ANDOR][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_условие [[ANDOR][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Кофе
Рисунок 2.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.3
Фраза 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.
111.55075.111.55075.
123.1030.123.1030.
152.170340.152.170340.
Использование 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 года.
МинокладНачалоКонец
225001-01-199331-03-1993
427501-04-199330-06-1993
774001-07-199330-11-1993
1462001-12-199330-06-1994
2050001-07-199409-09-9999
Рисунок 2.4
Если, например, потребовалось узнать, какие изменения минимальных окладов
производились в 1993/94 учебном году, то можно выдать запрос
SELECT Начало, Миноклад
FROM Миноклады
WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'
и получить результат:
НачалоМиноклад
01-12-199314620
01-07-199420500
Отметим, что при формировании запросов значения дат следует заключать в
апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычитать из 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'
МинокладНачалоКонец
774001/07/199330/11/1993
1462001/12/199330/06/1994
2050001/07/199409/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(К_во)
2202
Если бы для вывода в результат еще и номера продукта был сформирован запрос
SELECT ПР,SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция
создает единственное значение из множества значений столбца-аргумента, а для
“свободного” столбца должно быть выдано все множество его значений. Без
специального указания (оно задается фразой GROUP BY) SQL не будет выяснять,
одинаковы значения этого множества (как в данном примере, где ПР=10) или
различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос
отвергается системой.
Правда, никто не запрещает дать запрос
SELECT 'Кол-во лука =',SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
Результат:
'Кол-во лука ='SUM(К_во)COUNT(К_во)
Кол-во лука =2202
Отметим также, что в столбце-аргументе перед применением любой функции, кроме
COUNT(*), исключаются все неопределенные значения. Если оказывается, что
аргумент – пустое множество, функция COUNT принимает значение 0, а остальные –
NULL.
Например, для получения суммы цен, средней цены, количества поставляемых
продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ (ПС=5),
а также для получения количества продуктов, которые могут поставляться этим
коопторгом, можно дать запрос
SELECT SUM(Цена),AVG(Цена),COUNT(Цена),
COUNT(DISTINCT Цена),COUNT(*)
FROM Поставки
WHERE ПС = 5;
и получить
SUM(Цена)AVG(Цена)COUNT(Цена)COUNT(DISTINCT Цена)COUNT (*)
6.21.24547
В другом примере, где надо узнать “Сколько поставлено моркови и сколько
поставщиков ее поставляют?”:
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,а.
а)б)в)г)
ПРПСПРЦенаК_воПРПР
9019-0--0-137090
1115039-0--0-2011150
123059-0--0-32501230
153701111.505041001570
1370511-0--0-51701370
3250611-0--0-62203250
51708111.001007200570
62201123.001081506140
81503122.5020908150
7200612-0--0-102207200
201152.001701115020
41003151.5020012304100
13190213.603001319013190
1470714.207014701470
1625023-0--0-1537016250
1750734.00250162501750
10220. . .175010220
Рисунок 2.5
Фраза 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(БЛ)
1318
1614
11917
12115
…
Использование фразы 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З31ЗавтракЗЗакуска1Салат летнийЗОвощи200.3
1З31ЗавтракЗЗакуска2Салат мяснойЗМясо200.4
1З31ЗавтракЗЗакуска3Салат витаминныйЗОвощи200.4 *
. . .
1З31ЗавтракЗЗакуска12Суп молочныйСМолоко500.3
1З31ЗавтракЗЗакуска13БастурмаГМясо300.5
. . .
1З31ЗавтракЗЗакуска32Кофе черныйНКофе100.1
1З31ЗавтракЗЗакуска33Кофе на молокеНКофе200.2
1З61ЗавтракЗЗакуска1Салат летнийЗОвощи200.3
1З61ЗавтракЗЗакуска2Салат мяснойЗМясо200.4
1З61ЗавтракЗЗакуска3Салат витаминныйЗОвощи200.4
1З61ЗавтракЗЗакуска4Салат рыбныйЗРыба200.4
1З61ЗавтракЗЗакуска5Паштет из рыбыЗРыба120.5
1З61ЗавтракЗЗакуска6Мясо с гарниромЗМясо250.3 *
. . .
Рисунок 2.6
Очевидно, что отбор актуальных строк обеспечивается вводом в запрос WHERE фразы,
в которой устанавливается соответствие между:
кодами трапез (Т) в таблицах Меню и Трапезы (Меню.Т = Трапезы.Т),
кодами видов блюд (В) в таблицах Меню и Вид_блюд (Меню.В = Вид_блюд.В),
номерами блюд (БЛ) в таблицах Меню и Блюда (Меню.БЛ = Блюда.БЛ).
Такой скорректированный запрос
SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;
позволит получить эквисоединение таблиц Меню, Трапезы, Вид_блюд и Блюда:
ТВБЛТТрапезаВВидБЛБлюдоВОсноваВыходТруд
1З31ЗавтракЗЗакуска3Салат витаминныйЗОвощи200.4
1З61ЗавтракЗЗакуска6Мясо с гарниромЗМясо250.3
1Г191ЗавтракГГорячее19Омлет с лукомГЯйца200.5
. . .
3Г163УжинГГорячее16ДраченаГЯйца180.4
3Н303УжинННапиток30КомпотНФрукты200.2
3Н313УжинННапиток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
ACE="Times New Roman">
ЗавтракОмлет с лукомГорячееЯйца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.ПР = Поставки.ПР );
Естественно, что это коррелированный подзапрос: здесь сначала определяется
минимальная цена продукта, входящего в состав Сырников, и только затем
выясняется его поставщик.
На этом примере мы закончим знакомство с вложенными подзапросами, предложив
попробовать свои силы в составлении ряда запросов, с помощью механизма таких
подзапросов:
Выдать названия всех мясных блюд.
Выдать количество всех блюд, в состав которых входят помидоры.
Выдать блюда, продукты для которых поставляются агрофирмой ЛЕТО.