Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Глава 4. Основы SQL Глава 5. Запросы с использованием единственной таблицы Глава 6. Запросы с использованием нескольких таблиц Глава 4 Основы SQL 4.1. Стандарты языка SQL В разд. 3.1 рассматривались революционные предложения Эдгара Кодда по созданию реляционной модели данных и инструмента для удобной работы с отношениями — реляционной алгебры. Он же инициировал разработку для этой модели языка SEQUEL (Structured English Query Language, структуриро- ванный английский язык для запросов), который впоследствии по юридическим соображениям был переименован в SQL (Structured Query Language, структури- рованный язык запросов). Официальным произношением стало [es kju:' el] — эс-кью-эл. Несмотря на это, даже англоязычные специалисты по-прежнему часто называют SQL сиквел, вместо эс-кью-эл (по-русски также часто говорят "эс-ку-эль"). Целью разработки было создание простого непроцедурного языка, которым мог воспользоваться любой пользователь, даже не имеющий навыков программирования. К началу 1980-х годов SQL завоевал популярность как язык реляционных СУБД и привлек внимание Американского национального института по стандартизации (American National Standards Institute, ANSI), который в 1986, 1989, 1992, 1999 и 2003 годах выпустил стандарты языка SQL. В 1989 году SQL был включен в стандарты международной организации по стандарти- зации ISO (SQL:1989), а затем были приняты и опубликованы стандарты SQL:1992, SQL:1999 и SQL:2003. В настоящее время все производители распространенных реляционных СУБД поддерживают с различной степе- нью соответствия стандарт SQL:2003 [6]. Каждый из новых стандартов от- личался от предыдущего улучшением синтаксиса, расширением типов дан- ных, новыми процедурными расширениями, которые сможет применить любое приложение, имеющее доступ к базе данных, средств для работы с Java и XML-документами. Часть II. Язык SQL. Извлечение данных 58 4.2. Почему SQL? Все языки манипулирования данными (ЯМД), созданные до появления реля- ционных баз данных и разработанные для многих СУБД, были ориентирова- ны на операции с данными, представленными в виде логических записей файлов. Это требовало от пользователей детального знания организации хра- нения данных и достаточных усилий для указания не только того, какие дан- ные нужны, но и того, где они размещены, и как шаг за шагом получить их. Рассматриваемый же далее непроцедурный язык SQL ориентирован на опе- рации с данными, представленными в виде логически взаимосвязанных сово- купностей таблиц. Особенность предложений этого языка состоит в том, что они ориентированы в большей степени на конечный результат обработки данных, чем на процедуру этой обработки. SQL сам определяет, где находят- ся данные, какие индексы и даже наиболее эффективные последовательности операций следует использовать для их получения: не надо указывать эти де- тали в запросе к базе данных. Для иллюстрации различий между ЯМД рассмотрим следующую ситуацию. Пусть, например, вы собираетесь посмотреть кинофильм и хотите воспользо- ваться для поездки в кинотеатр услугами такси. Одному шоферу такси доста- точно сказать название фильма — и он сам найдет вам кинотеатр, в котором показывают нужный фильм. (Подобным же образом, самостоятельно, оты- скивает запрошенные данные SQL.) Для другого шофера такси вам, возможно, потребуется самому узнать, где демонстрируется нужный фильм, и назвать кинотеатр. Тогда водитель дол- жен найти адрес этого кинотеатра. Может случиться и так, что вам придется самому узнать адрес кинотеатра и предложить водителю проехать к нему по таким-то и таким-то улицам. В худшем случае вам, может быть, даже при- дется по дороге давать указания: "Повернуть налево... проехать пять кварта- лов... повернуть направо...". (Аналогично больший или меньший уровень де- тализации запроса приходится создавать пользователю в разных СУБД, не имеющих языка SQL.) Появление теории реляционных баз данных и предложенного Коддом языка запросов "alpha", основанного на реляционном исчислении [1], инициировало разработку ряда языков запросов, которые можно отнести к двум классам: 1. Алгебраические языки, позволяющие выражать запросы средствами спе- циализированных операторов, применяемых к отношениям: JOIN — со- единить, INTERSECT — пересечь, SUBTRACT — вычесть и т. д. (см. разд. 3.3). 2. Языки исчисления предикатов, представляющие собой набор правил для записи выражения, определяющего новое отношение из заданной совокуп- Глава 4. Основы SQL 59 ности существующих отношений. Другими словами исчисление предикатов есть метод определения того отношения, которое нам желательно получить (как ответ на запрос) из отношений, уже имеющихся в базе данных. Как указывалось в разд. 4.1, в начале 1980-х годов SQL "победил" другие языки запросов и стал фактическим стандартом таких языков для профессио- нальных реляционных СУБД. В 1989 году он стал международным стандар- том языка баз данных и начал внедряться во все распространенные СУБД персональных компьютеров. Почему же это произошло? Непрерывный рост быстродействия, а также снижение энергопотребления, размеров и стоимости компьютеров привели к резкому расширению возмож- ных рынков их сбыта, круга пользователей, разнообразия типов и цен. Есте- ственно, что расширился спрос на разнообразное программное обеспечение. Борясь за покупателя, фирмы, производящие программное обеспечение, ста- ли выпускать на рынок все более и более интеллектуальные и, следователь- но, объемные программные комплексы. Приобретая (желая приобрести) та- кие комплексы, многие организации и отдельные пользователи часто не могли разместить их на собственных ЭВМ, однако не хотели и отказываться от нового сервиса. Для обмена информацией и ее обобществления были соз- даны сети ЭВМ, где обобществляемые программы и данные стали размещать на специальных обслуживающих устройствах — файловых серверах. СУБД, работающие с файловыми серверами, позволяют множеству пользо- вателей разных ЭВМ (иногда расположенных достаточно далеко друг от дру- га) получать доступ к одним и тем же базам данных. При этом упрощается разработка различных автоматизированных систем управления организация- ми, учебных комплексов, информационных и других систем, где множество сотрудников (учащихся) должны использовать общие данные и обмениваться создаваемыми в процессе работы (обучения). Однако при такой идеологии вся обработка запросов из программ или с терминалов пользовательских ЭВМ выполняется на этих же ЭВМ. Поэтому для реализации даже простого запроса ЭВМ часто должна считывать из файлового сервера и (или) записы- вать на сервер целые файлы, что ведет к конфликтным ситуациям и пере- грузке сети. Для исключения указанных и некоторых других недостатков была предложе- на технология "клиент-сервер", по которой запросы пользовательских ЭВМ (клиент) обрабатываются на специальных серверах баз данных (сервер), а на ЭВМ возвращаются лишь результаты обработки запроса. При этом, естест- венно, нужен единый язык общения с сервером и в качестве такого языка вы- бран SQL. Поэтому все современные версии профессиональных реляционных СУБД (DB2, Oracle, SQL Server, Sybase, PostgreSQL, MySQL) и даже нереля- Часть II. Язык SQL. Извлечение данных 60 ционных СУБД (например, Adabas) используют технологию "клиент-сервер" и язык SQL. К тому же приходят разработчики СУБД персональных ЭВМ, многие из которых уже сегодня снабжены языком SQL. Бытует мнение, поскольку большая часть запросов формулируется на SQL, то практически безразлично, что это за СУБД — был бы SQL. Реализация в SQL концепции операций, ориентированных на табличное представление данных, позволила создать компактный язык с небольшим (менее 30) набором основных предложений. SQL может использоваться как интерактивный (для выполнения запросов) и как встроенный (для построения прикладных программ) язык. В нем существуют: предложения определения данных (определение баз данных, а также оп- ределение и уничтожение таблиц и индексов); запросы на выбор данных (предложение SELECT ); предложения модификации данных (добавление, удаление и изменение данных); предложения управления данными (предоставление и отмена привилегий на доступ к данным, управление транзакциями и другие). Кроме того, он предоставляет возможность выполнять в этих предложениях: арифметические вычисления (включая разнообразные функциональные преобразования), обработку текстовых строк и выполнение операций сравнения значений арифметических выражений и текстов; упорядочение строк и (или) столбцов при выводе содержимого таблиц на печать или экран дисплея; создание представлений (виртуальных таблиц), позволяющих пользо- вателям иметь свой взгляд на данные без увеличения их объема в базе данных (см. разд. 4.3 и 7.3.4); запоминание выводимого по запросу содержимого таблицы, несколь- ких таблиц или представления в другой таблице (реляционная опера- ция присваивания); агрегатирование данных: группирование данных и применение к этим группам таких операций, как среднее, сумма, максимум, минимум, число элементов и т. п. Ориентированный на работу с таблицами SQL не имеет достаточных средств для создания сложных прикладных программ. Поэтому в разных СУБД он либо используется вместе с языками программирования высокого уровня (например, такими как Си, Паскаль, PHP), либо включен в состав команд Глава 4. Основы SQL 61 специально разработанного языка СУБД (диалекта SQL): SQLpl в DB2, PL/SQL в Oracle, PL/pgSQL в PostgreSQL, Trunsact-SQL в SQL Server и т. п. Эти диалекты включают кроме SQL средства условной обработки (например, под контролем IF…THEN ), управляющие операторы (например, циклы WHILE ), переменные и обработку ошибок. 4.3. Таблицы SQL До сих пор понятие "таблица", как правило, связывалось с реальной или базовой таблицей, т. е. c таблицей, для каждой строки которой в действи- тельности имеется некоторый двойник, хранящийся в физической памяти машины (рис. 4.1). Однако SQL использует и создает ряд виртуальных (как будто существующих) таблиц: представлений, курсоров и неименованных рабочих таблиц, в которых формируются результаты запросов на получение данных из базовых таблиц и, возможно, представлений. Это таблицы, кото- рые не существуют в базе данных, но как бы существуют с точки зрения пользователя. Рис. 4.1. База данных в восприятии пользователя Часть II. Язык SQL. Извлечение данных 62 Базовые таблицы создаются с помощью предложения CREATE TABLE (создать таблицу), подробное описание которого приведено в главе 13. Здесь же при- ведем пример предложения для создания описания таблицы Блюда : CREATE TABLE Блюда ( Код_блюда число, Блюдо текст(70), Код_вида число, Основа текст(10), Выход число, Труд число ); Предложение CREATE TABLE специфицирует имя базовой таблицы, которая должна быть создана, имена ее столбцов и типы данных для этих столбцов (а также, возможно, некоторую дополнительную информацию, не иллюстри- руемую данным примером). CREATE TABLE — выполняемое предложение. Ес- ли ввести его с терминала, система тотчас построит таблицу Блюда , которая сначала будет пустой: она будет содержать только строку заголовков столб- цов, но не будет еще содержать никаких строк с данными. Однако можно не- медленно приступить к вставке таких строк данных, возможно, с помощью предложения INSERT , и создать таблицу, аналогичную таблице Блюда (см. табл. 3.1). Если теперь потребовалось узнать, какие овощные блюда может приготовить повар пансионата, то можно набрать на терминале следующий текст запроса: SELECT Код_блюда, Блюдо FROM Блюда WHERE Основа = 'Овощи'; и мгновенно получить на экране следующий результат его реализации: КОД_БЛЮДА БЛЮДО --------- ---------------- 1 Салат летний 3 Салат витаминный 17 Морковь с рисом 23 Помидоры с луком Для выполнения этого предложения SELECT (выбрать), подробное описание которого будет дано в главах 5 и 6, СУБД должна сначала сформировать пус- тую рабочую таблицу, состоящую из столбцов Код_блюда и Блюдо , тип дан- ных которых должен совпадать с типом данных аналогичных столбцов базо- вой таблицы Блюда . Затем она должна выбрать из таблицы Блюда все строки, Глава 4. Основы SQL 63 у которых в столбце Основа хранится слово Овощи , выделить из этих строк столбцы Код_блюда и Блюдо и загрузить укороченные строки в рабочую таб- лицу. Наконец, СУБД должна выполнить процедуры по организации вывода содержимого рабочей таблицы на экран терминала (при этом, если в рабочей таблице содержится более 20—24 строк, она должна использовать процеду- ры постраничного вывода и т. п.). После выполнения запроса СУБД должна уничтожить рабочую таблицу. Если, например, надо получить значение калорийности всех овощей, вклю- ченных в таблицу Продукты , то можно набрать на терминале запрос SELECT Продукт, Белки, Жиры, Углев, ((Белки + Углев)*4.1+Жиры*9.3) FROM Продукты WHERE Продукт IN ('Морковь', 'Лук', 'Помидоры', 'Зелень'); и получить на экране следующий результат его реализации: ПРОДУКТ БЕЛКИ ЖИРЫ УГЛЕВ ((БЕЛКИ+УГЛЕВ)*4.1+ЖИРЫ*9.3) -------- ------ ------ ------ ---------------------------- Морковь 13,0 1,0 70,0 349,6 Лук 17,0 0,0 95,0 459,2 Помидоры 6,0 0,0 42,0 196,8 Зелень 9,0 0,0 20,0 118,9 В последнем столбце этой рабочей таблицы приведены данные о калорийно- сти продуктов, отсутствующие в явном виде в базовой таблице Продукты . Эти данные вычислены по хранимым значениям основных питательных веществ продуктов, помещены в рабочую таблицу и будут существовать до момента смены изображения на экране. Однако если необходимо сохранить эти дан- ные в какой-либо базовой таблице, то существует предложение ( INSERT ), по- зволяющее переписать содержимое рабочей таблицы в указанные столбцы базовой таблицы (реляционная операция присваивания). Отметим, что в рабочей таблице в качестве имени последнего столбца выво- дится выражение для вычисления калорийности, т. е. тот текст, который при- веден в запросе (другого нет). Однако можно присвоить этому тексту псев- доним, и тогда запрос и его результат будут иметь вид: SELECT Продукт, Белки, Жиры, Углев, ((Белки + Углев)*4.1+Жиры*9.3) Кало- рийность FROM Продукты WHERE Продукт IN ('Морковь', 'Лук', 'Помидоры', 'Зелень'); ПРОДУКТ БЕЛКИ ЖИРЫ УГЛЕВ КАЛОРИЙНОСТЬ -------- ------ ------ ------ ------------ Морковь 13,0 1,0 70,0 349,6 Часть II. Язык SQL. Извлечение данных 64 Лук 17,0 0,0 95,0 459,2 Помидоры 6,0 0,0 42,0 196,8 Зелень 9,0 0,0 20,0 118,9 Часто пользователя не устраивает как способ описания нужного набора вы- водимых строк, так и результат выполнения запроса, сформированного из данных одной таблицы. Ему хотелось бы уточнить выводимые (запрашивае- мые) данные сведениями из других таблиц. Например, в запросе на получение состава овощных блюд SELECT Код_блюда, Код_продукта, Вес FROM Состав WHERE Код_блюда IN (1, 3, 17, 23); пришлось перечислять номера этих блюд, так как в таблице Состав нет дан- ных об основных продуктах блюда (они есть в таблице Блюда ). Полученный состав овощных блюд (левая колонка табл. 4.1) оказался "слепым": в нем и блюда и продукты представлены номерами, а не именами. Удобнее и нагляднее выглядит результат запроса (правая колонка табл. 4.1), сформированного по трем таблицам: SELECT Блюда.Блюдо, Продукты.Продукт, Состав.Вес FROM Состав, Блюда, Продукты WHERE Состав.Код_блюда = Блюда.Код_блюда AND Состав.Код_продукта = Продукты.Код_продукта AND Основа = 'Овощи'; В нем для получения рабочей таблицы выполняется естественное соединение (см. разд. 3.3.2) таблиц Блюда , Продукты и Состав (условие соединения — ра- венство значений кодов блюд и значений кодов продуктов). Затем выделяют- ся строки, у которых в столбце Основа хранится слово Овощи , и из получен- ных строк — столбцы Блюдо , Продукт и Вес Таблица 4.1. Результат запроса по составу овощных блюд базы данных "COOK" Запрос по одной таблице Запрос по трем таблицам КОД_БЛЮДА КОД_ПРОДУКТА ВЕС БЛЮДО ПРОДУКТ ВЕС --------- ------------ ---- ---------------- -------- --- 1 4 15 Салат летний Майонез 15 1 11 100 Салат летний Помидоры 100 1 12 5 Салат летний Зелень 5 1 15 80 Салат летний Яблоки 80 3 6 50 Салат витаминный Сметана 50 Глава 4. Основы SQL 65 Таблица 4.1 ( окончание) Запрос по одной таблице Запрос по трем таблицам 3 10 15 Салат витаминный Лук 15 3 12 20 Салат витаминный Помидоры 55 3 11 55 Салат витаминный Зелень 20 3 16 5 Салат витаминный Яблоки 55 3 15 55 Салат витаминный Сахар 5 17 3 20 Морковь с рисом Масло 20 17 7 50 Морковь с рисом Молоко 50 17 12 10 Морковь с рисом Морковь 150 17 9 150 Морковь с рисом Зелень 10 17 13 25 Морковь с рисом Рис 25 17 14 5 Морковь с рисом Мука 5 23 3 20 Помидоры с луком Масло 20 23 10 65 Помидоры с луком Лук 65 23 11 250 Помидоры с луком Помидоры 250 Если пользователи достаточно часто интересуются составом различных блюд, то для упрощения формирования запросов целесообразно создать представление. Представление — это пустая именованная таблица, определяемая перечнем тех столбцов таблиц и признаками тех их строк, которые хотелось бы в ней увидеть. Представление является как бы "окном" в одну или несколько базо- вых таблиц. Оно создается с помощью предложения CREATE VIEW (создать представление), подробное описание которого приведено в разд. 7.3.4. Здесь же приведем пример предложения для создания представления Состав_блюд : CREATE VIEW Состав_блюд AS SELECT Блюдо, Продукт, Вес FROM Состав, Блюда, Продукты WHERE Состав.Код_блюда = Блюда.Код_блюда AND Состав.Код_продукта = Продукты.Код_продукта; Оно описывает пустую таблицу, в которую при реализации запроса будут загружаться данные из столбцов Блюдо , Продукт и Вес таблиц Блюда , Продукты и Состав соответственно. Теперь для получения состава овощных блюд можно дать запрос SELECT Блюдо, Продукт, Вес FROM Состав_блюд WHERE Основа = 'Овощи'; Часть II. Язык SQL. Извлечение данных 66 и получить на экране терминала данные, которые представлены в правой ко- лонке табл. 4.1. А для получения состава супа харчо можно дать запрос: SELECT Блюдо, Продукт, Вес FROM Состав_блюд WHERE Блюдо = 'Суп харчо'; О целесообразности создания представлений будет рассказано далее, а здесь лишь отметим, что они позволяют повысить уровень логической независимо- сти данных, упростить их восприятие и "скрыть" от некоторых пользователей те или иные данные. Например, данные о новых ценах на продукты первой необходимости или из какой рыбы приготавливается "Судак по-польски" (тут мы вспомнили меню одной из столовых, в котором предлагалось блюдо "Судак по-польски из хека"). Наконец, еще об одних виртуальных таблицах — курсорах. Курсор — это пустая именованная таблица, определяемая перечнем тех столбцов базовых таблиц и признаками тех их строк, которые хотелось бы в ней увидеть. В чем же различие между курсором и представлением? Для пользователя представления почти не отличаются от базовых таблиц (есть лишь некоторые ограничения при выполнении различных операций ма- нипулирования данными). Они могут использоваться как в интерактивном режиме, так и в прикладных программах. Курсоры же созданы для процедур- ной работы с таблицей в прикладных программах. Например, после объявле- ния курсора DECLARE Блюд_состав CURSOR FOR SELECT Блюдо, Продукт, Вес FROM Состав, Блюда, Продукты WHERE Состав.Код_блюда = Блюда.Код_блюда AND Состав.Код_продукта = Продукты.Код_продукта AND Блюдо = 'Суп харчо'; и его активизации ( OPEN Блюд_состав ) будет создана временная таблица с составом блюда "Суп харчо" и специальным указателем, определяющим в качестве текущей первую строку этой таблицы. С помощью предложения FETCH (выбрать), которое обычно исполняется в программном цикле, можно присвоить определенным переменным значения указанных столбцов этой строки. Одновременно курсор будет передвинут к следующей строке табли- цы. После обработки в программе полученных значений переменных выпол- няется следующее предложение FETCH и т. д. до окончания перебора всех продуктов харчо. |