Реферат: Web-доступ к базам данных - текст реферата. Скачать бесплатно.
Банк рефератов, курсовых и дипломных работ. Много и бесплатно. # | Правила оформления работ | Добавить в избранное
 
 
   
Меню Меню Меню Меню Меню
   
Napishem.com Napishem.com Napishem.com

Реферат

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

Банк рефератов / Компьютерные сети

Рубрики  Рубрики реферат банка

закрыть
Категория: Реферат
Язык реферата: Русский
Дата добавления:   
 
Скачать
Microsoft Word, 1086 kb, скачать бесплатно
Заказать
Узнать стоимость написания уникального реферата

Узнайте стоимость написания уникальной работы

Организация 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 совместимыми со стандартом , однако сделал сам стандарт относительно слабым. Чтобы заполнить эти пробелы , комитет AN SI продолжил свою работу и создал проект нового , более жесткого стандарта SQL2. В отличие от стандарта 1989 года , проект SQL2 предусматривал возможности , выходящие за рамки таковых , уже существующих в реальных коммерческих продуктах . А для следующего за ни м стандарта SQL3 были предложены еще более глубокие изменения . В результате предложенные стандарты SQL2 и SQL3 оказались более противоречивыми , чем исходный стандарт . Стандарт SQL2 прошел процесс утверждения в ANSI и был окончательно принят в октябре 1992 года . В то время , как первый стандарт 1986 года занимает не более ста страниц , официальный стандарт SQL2 содержит около шестисот. Вопреки стандарту SQL2, во всех существующих на сегодняшний день коммерческих продуктах поддерживаются собственные диалекты SQ L. Более того , поставщики СУБД включают в свои продукты новые возможности и расширяют собственные диалекты 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 дает пользователю или приложению возможность читать из базы данных содержащиеся в ней данные и пользоваться ими. Обработка данных. S QL дает пользователю или приложению возможность изменять базу данных , т.е . добавлять в нее новые данные , а также удалять или обновлять уже имеющиеся в ней данные. Управление доступом . С помощью 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 (различный , различные ), как показано в следующем примере : SE LECT 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 42 75 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 B Y Белки 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. Следует подчеркнуть , что в SEL ECT и 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.ПР = Пос тавки.ПР ); Естественно , что это коррелированный подзапрос : здесь сначала определяется минимальная цена продукта , входящего в состав Сырников , и только затем выясняется его поставщик. На этом примере мы закончим знакомство с вложенными подзапросами , предло жив попробовать свои силы в составлении ряда запросов , с помощью механизма таких подзапросов : Выдать названия всех мясных блюд . Выдать количество всех блюд , в состав которых входят помидоры . Выдать блюда , продукты для которых поставляются агрофирмой ЛЕТО .
1Архитектура и строительство
2Астрономия, авиация, космонавтика
 
3Безопасность жизнедеятельности
4Биология
 
5Военная кафедра, гражданская оборона
 
6География, экономическая география
7Геология и геодезия
8Государственное регулирование и налоги
 
9Естествознание
 
10Журналистика
 
11Законодательство и право
12Адвокатура
13Административное право
14Арбитражное процессуальное право
15Банковское право
16Государство и право
17Гражданское право и процесс
18Жилищное право
19Законодательство зарубежных стран
20Земельное право
21Конституционное право
22Конституционное право зарубежных стран
23Международное право
24Муниципальное право
25Налоговое право
26Римское право
27Семейное право
28Таможенное право
29Трудовое право
30Уголовное право и процесс
31Финансовое право
32Хозяйственное право
33Экологическое право
34Юриспруденция
 
35Иностранные языки
36Информатика, информационные технологии
37Базы данных
38Компьютерные сети
39Программирование
40Искусство и культура
41Краеведение
42Культурология
43Музыка
44История
45Биографии
46Историческая личность
47Литература
 
48Маркетинг и реклама
49Математика
50Медицина и здоровье
51Менеджмент
52Антикризисное управление
53Делопроизводство и документооборот
54Логистика
 
55Педагогика
56Политология
57Правоохранительные органы
58Криминалистика и криминология
59Прочее
60Психология
61Юридическая психология
 
62Радиоэлектроника
63Религия
 
64Сельское хозяйство и землепользование
65Социология
66Страхование
 
67Технологии
68Материаловедение
69Машиностроение
70Металлургия
71Транспорт
72Туризм
 
73Физика
74Физкультура и спорт
75Философия
 
76Химия
 
77Экология, охрана природы
78Экономика и финансы
79Анализ хозяйственной деятельности
80Банковское дело и кредитование
81Биржевое дело
82Бухгалтерский учет и аудит
83История экономических учений
84Международные отношения
85Предпринимательство, бизнес, микроэкономика
86Финансы
87Ценные бумаги и фондовый рынок
88Экономика предприятия
89Экономико-математическое моделирование
90Экономическая теория

 Анекдоты - это почти как рефераты, только короткие и смешные Следующий
— На первый-второй по-еврейски рассчитайсь!
— Я болею...
— Я устал...
— Мне нельзя!
— Мне мама не разрешает. Расчет окончен!
Anekdot.ru

Узнайте стоимость курсовой, диплома, реферата на заказ.

Банк рефератов - РефератБанк.ру
© РефератБанк, 2002 - 2016
Рейтинг@Mail.ru