3 кнопки, котоpые потpясли DOS.

Unix Dos Linux Windows Os/2 Qnx Beos Gios Hard Etc Link Форум (тестовый режим) Гостевая Юмор Soft Связь
 
 
 

Etc.: Структуризированный язык запросов (SQL)

Содержание

Глава 1. Реляционные базы данных и язык SQL

1.1. Реляционная база данных

Реляционная база данных представляется пользователю как совокупность таблиц и ничего кроме таблиц. На рис.1.1 приведен пример реляционной базы данных ПАНСИОН. Этот простой пример используется для иллюстрации большинства вопросов, рассматриваемых в нашей книге. Поэтому советуем потратить немного времени, чтобы хорошо с ним разобраться*.

Кладовая пансионата периодически пополняется продуктами из списка, часть которого показана в таблице Продукты. Каждый продукт имеет кроме названия (столбец Продукт) уникальный номер этого продукта (столбец ПР). Химический состав продуктов приведен для 1 кг их съедобной части: основные пищевые вещества (белки, жиры и углеводы) даны в граммах, а минеральные вещества (калий, кальций, натрий) и витамины (B2, PP, C) - в миллиграммах.

В таблице Блюда представлены уникальные номера блюд (столбец БЛ), их названия, коды видов (см. таблицу Вид_блюд), основной продукт (столбец Основа), масса порции в граммах (столбец Выход) и приведенная стоимость в копейках приготовления одной порции (столбец Труд).

В таблице Рецепты приведена технология приготовления блюд. Их выделение в отдельную таблицу произведено потому, что одно и то же блюдо может иметь несколько разных рецептов.

Таблица Состав связывает между собой таблицы Блюда и Продукты, оговаривая, какая масса (в граммах) того или иного продукта (столбец Вес) должна входить в состав одной порции блюда. Так, порция блюда с номером 12 (Суп молочный) должна состоять из 350 г продукта с номером 7 (Молоко), 35 г продукта с номером 13 (Рис), 5 г продукта с номером 3 (Масло) и 5 г продукта с номером 16 (Сахар).

Шеф-повар ежедневно получает от завхоза сведения о количестве в килограммах имеющихся продуктов и их текущей стоимости (столбцы К_во и Стоимость таблицы Наличие). Используя эти сведения он определяет по таблице Состав перечень тех блюд, которые можно приготовить из этих продуктов, а также калорийность и стоимость таких блюд. При этом стоимость блюда складывается из стоимости и массы продуктов, необходимых для приготовления одной его порции, а также из трудозатрат на ее приготовление (см. таблицу Блюда). Калорийность же определяется по массе и калорийности каждого из продуктов блюда. (Для получения значения калорийности продукта исходят из того, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров - 9.3 ккал.)

БлюдаРецепты
БЛБлюдоВОсноваВыходТруд
1Салат летнийЗОвощи200.3
2Салат мяснойЗМясо200.4
3Салат витаминныйЗОвощи200.4
4Салат рыбныйЗРыба200.4
5Паштет из рыбыЗРыба120.5
6Мясо с гарниромЗМясо250.3
7СметанаЗМолоко140.1
8ТворогЗМолоко140.2
9Суп харчоСМясо500.5
10Суп-пюре из рыбыСРыба500.6
11Уха из судакаСРыба500.5
12Суп молочныйСМолоко500.3
13БастурмаГМясо300.5
14БефстрогановГМясо210.6
15Судак по-польскиГРыба160.5
16ДраченаГЯйца180.4
17Морковь с рисомГОвощи260.3
18СырникиГМолоко220.4
19Омлет с лукомГЯйца200.5
20Каша рисоваяГКрупа210.4
21Пудинг рисовыйГКрупа160.6
22Вареники ленивыеГМолоко220.4
23Помидоры с лукомГОвощи260.4
24Суфле из творогаГМолоко280.6
25Рулет с яблокамиДФрукты200.5
26Яблоки печеныеДФрукты160.3
27Суфле яблочноеДФрукты220.6
28Крем творожныйДМолоко160.4
29"Утро"НФрукты200.5
30КомпотНФрукты200.2
31Молочный напитокНМолоко200.2
32Кофе черныйНКофе200.1
33Кофе на молокеНКофе200.2
БЛРецепт
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Сварить ч...

Поставщики
ПСНазваниеСтатусГородАдресТелефон
1СЫТНЫЙрынокЛенинградСытнинская, 32329916
2ПОРТОСкооперативРезекнеСадовая, 27317664
3ШУШАРЫсовхозПушкинНовая, 174705038
4ТУЛЬСКИЙуниверсамЛенинградТульская, 32710837
5УРОЖАЙкоопторгЛугаПесчаная, 19789000
6ЛЕТОагрофирмаЛенинградПулковское ш.,82939729
7ОГУРЕЧИКфермаПаневежисУкмерге, 15127331
8КОРЮШКАкооперативЙыхвиНарвское ш., 64432123

Состав Поставки
БЛПРВесБЛПРВесБЛПРВесБЛПРВес
111100911251673524880
115809133516615247100
1125912151614924540
14159315163524630
216510270179150241620
29401072501775024310
2113510320171325241410
21220101415173202515120
252010125171210251635
242011210017145251430
311551192018814025820
315551110201863025320
365011351814152615150
312201112218510261620
310151273501816152632
3165121335195120271550
4250123519745277150
411501216519102027580
444013118019315271635
493513111002013502732
452013104020775288100
412513122020157528520
5280133520161028620
5940141902035281615
53251475021137028310
512514620216302915150
618014101021320299200
611150143521520291615
643014125211615301570
6121014143228140301610
7612515210022630317150
71615159202214203115150
887515520221615311625
865015320225832178
81615151010231125033178
918015125231065331625
910301651202332033775
ПСПРЦенаК_во
19
1111.5050
1123.0010
1152.00170
213.60300
23
251.80100
263.6080
28
370.40200
39
3122.5020
3151.50200
42
442.0450
4130.88150
414
4160.94200
4174.5050
543.0050
59
5100.50130
511
5131.2040
5140.5070
5161.0050
6100.7090
611
612
714.2070
734.00250
762.20140
77
781.00150
82
852.0070
8111.00100

ПродуктыНаличие
ПР ПродуктБелкиЖирыУглевKCaNaB2PPC
1Говядина189.124.0.3150906001.528.0
2Судак190.80.0.187027001.110.30
3Масло60.825.90.2302207400.11.0
4Майонез31.670.26.48028000.0.0
5Яйца127.115.7.15305507104.41.90
6Сметана26.300.28.9508503201.1.2
7Молоко28.32.47.1460121015001.31.10
8Творог167.90.13.1120164014102.74.5
9Морковь13.1.70.20005102100.79.950
10Лук17.0.95.17503101800.22.100
11Помидоры6.0.42.2901404000.45.3250
12Зелень9.0.20.340275751.24.380
13Рис70.6.773.5402402600.416.0
14Мука106.13.732.17602401201.222.0
15Яблоки4.0.113.24801602600.33.130
16Сахар0.0.998.3020100.0.0
17Кофе127.36.9.97101801800.31.80
ПРК_воСтоим
1108429.84
200.00
373274.61
43997.46
561111.83
688206.60
721483.08
89282.80
900.00
107746.30
114651.70
121334.96
135451.14
149143.77
15117189.92
169896.14
1737166.50

Вид_блюдТрапезыМенюВыборВыбрано
ВВид
З Закуска
С Суп
Г Горячее
Д Десерт
Н Напиток
ТТрапеза
1 Завтрак
2 Обед
3 Ужин
ТВБЛТВБЛТВБЛ
1З32З13З6
1З62З63З 8
1Г192С93Г 20
1Г212С 123Г16
1Н31 2Г 143Н30
1Н32 2 Г163Н31
2Г18
2Д26
2 Д 28
СМ ТВ БЛ
21З3
21Г19
21Н31
22З1
22С12
22Г16
22Д26
23З8
23Г21
23Н32
СМТБЛ
113
1121
.
2216
2226
.
316
.
32330

Рис. 1.1. Основные таблицы базы данных ПАНСИОН

Учитывая примерную стоимость и необходимую калорийность дневного рациона отдыхающих, шеф-повар составляет меню на следующий день. В этом меню (таблица Меню) предлагается по несколько альтернативных блюд каждого вида (таблица Вид_блюд) и для каждой трапезы (таблица Трапезы). Перед завтраком каждый отдыхающий вводит в ЭВМ номер закрепленного за ним места в столовой пансионата (столбец СМ в таблице Выбор) и желаемый набор блюд для каждой из трапез следующего дня (в примере таблица заполнялась отдыхающим, сидящим на месте с номером 2). Таблицы Выбор объединяются по мере их создания в общую таблицу Выбрано, по которой определяют, сколько порций того или иного блюда надо приготовить для каждой трапезы.

Завхоз связан с поставщиками продуктов, сведения о которых хранятся в таблице Поставщики. Эта таблица содержит уникальный номер поставщика (столбец ПС), его название, статус, месторасположение и телефон.

Таблица Поставки связывает между собой таблицы Продукты и Поставщики, оговаривая, какое количество продукта (столбец К_во) и по какой цене поставил тот или иной поставщик. Отсутствие в строке цены и количества говорит о том, что поставщик ПС может поставлять продукт ПР, но в данный момент не осуществил такой поставки.

Легко заметить, что все таблицы примера (как и все таблицы любой реляционной базы данных) состоят из строки заголовков столбцов и одной или более строк значений данных под этими заголовками. Эти столбцы и строки должны иметь следующие свойства:

  • всякому столбцу таблицы присвоено имя, которое должно быть уникальным для этой таблицы;
  • столбцы таблицы упорядочиваются слева направо, т.е. столбец 1, столбец 2, ..., столбец n. С математической точки зрения это утверждение некорректно, потому что в реляционной системе столбцы не упорядочены. Однако с точки зрения пользователя, порядок, в котором определены имена столбцов, становится порядком, в котором должны вводиться в них данные, если не предварять при вводе каждое значение именем соответствующего столбца (подробнее это описано в Приложении А литературы [2]);
  • строки таблицы не упорядочены (их последовательность определяется лишь последовательностью ввода в таблицу);
  • в поле на пересечении строки и столбца любой таблицы всегда имеется только одно значение данных и никогда не должно быть множества значений (правда, это "атомарное" значение может быть достаточно объемным, например, таким, как рецепт блюда);
  • всем строкам таблицы соответствует одно и то же множество столбцов, хотя в определенных столбцах любая строка может содержать пустые значения (NULL-значения), т.е. может не иметь значений для этих столбцов;
  • все строки таблицы обязательно отличаются друг от друга хотя бы единственным значением, что позволяет однозначно идентифицировать любую строку такой таблицы;
  • при выполнении операций с таблицей ее строки и столбцы можно обрабатывать в любом порядке безотносительно к их информационному содержанию.

Почему же база данных, составленная из таких таблиц, называется реляционной? А потому, что отношение - relation - просто математический термин для обозначения неупорядоченной совокупности однотипных записей или таблиц определенного специфического вида, описанного выше. Таким образом, можно, например, сказать, что база данных ПАНСИОН состоит из одиннадцати отношений.

Реляционные системы берут свое начало в математической теории множеств. Они были предложены в конце 1968 года доктором Э.Ф.Коддом из фирмы IBM, который первым осознал, что можно использовать математику для придания надежной основы и строгости области управления базами данных.

Нечеткость многих терминов, используемых в сфере обработки данных, заставила Кодда отказаться от них и придумать новые или дать более точные определения существующим. Так, он не мог использовать широко распространенный термин "запись", который в различных ситуациях может означать экземпляр записи, либо тип записей, запись в стиле Кобола (которая допускает повторяющиеся группы) или плоскую запись (которая их не допускает), логическую запись или физическую запись, хранимую запись или виртуальную запись и т.д. Вместо этого он использовал термин "кортеж длины n" или просто "кортеж", которому дал точное определение. В литературе [2,3] можно подробно познакомиться с терминологией реляционных баз данных, а здесь мы будем использовать неформальные их эквиваленты:

таблицадля отношения,
строка или записьдля кортежа,
столбец или поледля атрибута.

Мы также принимаем, по определению, что "запись" означает "экземпляр записи", а "поле" означает "имя и тип поля".

* Так как иллюстративная база данных создавалась для лекционного курса в 1988 году, когда существовали "смешные" цены, а также исчезнувшие названия статусов (коопторг) и городов (Ленинград), то автор пытался несколько раз ее модифицировать. Однако поняв, что изменение цен, статусов и названий идет быстрее, чем подготовка и, тем более, выпуск издания, он решил сохранить в книге старые цены и названия.

^^^

1.2. Почему SQL?

Все языки манипулирования данными (ЯМД), созданные до появления реляционных баз данных и разработанные для многих систем управления базами данных (СУБД) персональных компьютеров, были ориентированы на операции с данными, представленными в виде логических записей файлов. Это требовало от пользователей детального знания организации хранения данных и достаточных усилий для указания не только того, какие данные нужны, но и того, где они размещены и как шаг за шагом получить их.

Рассматриваемый же ниже непроцедурный язык SQL (Structured Query Language - структуризованный язык запросов) ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц. Особенность предложений этого языка состоит в том, что они ориентированы в большей степени на конечный результат обработки данных, чем на процедуру этой обработки. SQL сам определяет, где находятся данные, какие индексы и даже наиболее эффективные последовательности операций следует использовать для их получения: не надо указывать эти детали в запросе к базе данных.

Для иллюстрации различий между ЯМД рассмотрим следующую ситуацию. Пусть, например, вы собираетесь посмотреть кинофильм и хотите воспользоваться для поездки в кинотеатр услугами такси. Одному шоферу такси достаточно сказать название фильма - и он сам найдет вам кинотеатр, в котором показывают нужный фильм. (Подобным же образом, самостоятельно, отыскивает запрошенные данные SQL.)

Для другого шофера такси вам, возможно, потребуется самому узнать, где демонстрируется нужный фильм и назвать кинотеатр. Тогда водитель должен найти адрес этого кинотеатра. Может случиться и так, что вам придется самому узнать адрес кинотеатра и предложить водителю проехать к нему по таким-то и таким-то улицам. В самом худшем случае вам, может быть, даже придется по дороге давать указания: "Повернуть налево... проехать пять кварталов... повернуть направо...". (Аналогично больший или меньший уровень детализации запроса приходится создавать пользователю в разных СУБД, не имеющих языка SQL.)

Появление теории реляционных баз данных и предложенного Коддом языка запросов "alpha", основанного на реляционном исчислении [2, 3], инициировало разработку ряда языков запросов, которые можно отнести к двум классам:

  1. Алгебраические языки, позволяющие выражать запросы средствами специализированных операторов, применяемых к отношениям (JOIN - соединить, INTERSECT - пересечь, SUBTRACT - вычесть и т.д.).
  2. Языки исчисления предикатов представляют собой набор правил для записи выражения, определяющего новое отношение из заданной совокупности существующих отношений. Другими словами исчисление предикатов есть метод определения того отношения, которое нам желательно получить (как ответ на запроc) из отношений, уже имеющихся в базе данных.

Разработка, в основном, шла в отделениях фирмы IBM (языки ISBL, SQL, QBE) и университетах США (PIQUE, QUEL) [3]. Последний создавался для СУБД INGRES (Interactive Graphics and Retrieval System), которая была разработана в начале 70-х годов в Университете шт. Калифорния и сегодня входит в пятерку лучших профессиональных СУБД. Сегодня из всех этих языков полностью сохранились и развиваются QBE (Query-By-Example - запрос по образцу) и SQL, а из остальных взяты в расширение внутренних языков СУБД только наиболее интересные конструкции.

В начале 80-х годов SQL "победил" другие языки запросов и стал фактическим стандартом таких языков для профессиональных реляционных СУБД. В 1987 году он стал международным стандартом языка баз данных и начал внедряться во все распро-страненные СУБД персональных компьютеров. Почему же это произошло?

Непрерывный рост быстродействия, а также снижение энергопотребления, размеров и стоимости компьютеров привели к резкому расширению возможных рынков их сбыта, круга пользователей, разнообразия типов и цен. Естественно, что расширился спрос на разнообразное программное обеспечение.

Борясь за покупателя, фирмы, производящие программное обеспечение, стали выпускать на рынок все более и более интеллектуальные и, следовательно, объемные программные комплексы. Приобретая (желая приобрести) такие комплексы, многие организации и отдельные пользователи часто не могли разместить их на собственных ЭВМ, однако не хотели и отказываться от нового сервиса. Для обмена информацией и ее обобществления были созданы сети ЭВМ, где обобществляемые программы и данные стали размещать на специальных обслуживающих устройствах - файловых серверах.

СУБД, работающие с файловыми серверами, позволяют множеству пользователей разных ЭВМ (иногда расположенных достаточно далеко друг от друга) получать доступ к одним и тем же базам данных. При этом упрощается разработка различных автоматизированных систем управления организациями, учебных комплексов, информационных и других систем, где множество сотрудников (учащихся) должны использовать общие данные и обмениваться создаваемыми в процессе работы (обучения). Однако при такой идеологии вся обработка запросов из программ или с терминалов пользовательских ЭВМ выполняется на этих же ЭВМ. Поэтому для реализации даже простого запроса ЭВМ часто должна считывать из файлового сервера и (или) записывать на сервер целые файлы, что ведет к конфликтным ситуациям и перегрузке сети.

Для исключения указанных и некоторых других недостатков была предложена технология "Клиент-Сервер", по которой запросы пользовательских ЭВМ (Клиент) обрабатываются на специальных серверах баз данных (Сервер), а на ЭВМ возвращаются лишь результаты обработки запроса. При этом, естественно, нужен единый язык общения с Сервером и в качестве такого языка выбран SQL. Поэтому все современные версии профессиональных реляционных СУБД (DB2, Oracle, Ingres, Informix, Sybase, Progress, Rdb) и даже нереляционных СУБД (например, Adabas) используют технологию "Клиент-Сервер" и язык SQL. К тому же приходят разработчики СУБД персональных ЭВМ, многие из которых уже сегодня снабжены языком SQL.

Бытует мнение: Поскольку большая часть запросов формулируется на SQL, практически безразлично, что это за СУБД - был бы SQL.

Реализация в SQL концепции операций, ориентированных на табличное представление данных, позволило создать компактный язык с небольшим (менее 30) набором предложений. SQL может использоваться как интерактивный (для выполнения запросов) и как встроенный (для построения прикладных программ). В нем существуют:

  • предложения определения данных (определение баз данных, а также определение и уничтожение таблиц и индексов);
  • запросы на выбор данных (предложение SELECT);
  • предложения модификации данных (добавление, удаление и изменение данных);
  • предложения управления данными (предоставление и отмена привилегий на доступ к данным, управление транзакциями и другие). Кроме того, он предоставляет возможность выполнять в этих предложениях:
  • арифметические вычисления (включая разнообразные функциональные преобразования), обработку текстовых строк и выполнение операций сравнения значений арифметических выражений и текстов;
  • упорядочение строк и (или) столбцов при выводе содержимого таблиц на печать или экран дисплея;
  • создание представлений (виртуальных таблиц), позволяющих пользователям иметь свой взгляд на данные без увеличения их объема в базе данных;
  • запоминание выводимого по запросу содержимого таблицы, нескольких таблиц или представления в другой таблице (реляционная операция присваивания).
  • агрегатирование данных: группирование данных и применение к этим группам таких операций, как среднее, сумма, максимум, минимум, число элементов и т.п.

В SQL используются следующие основные типы данных, форматы которых могут несколько различаться для разных СУБД:

INTEGER
- целое число (обычно до 10 значащих цифр и знак);
SMALLINT
- "короткое целое" (обычно до 5 значащих цифр и знак);
DECIMAL(p,q)
- десятичное число, имеющее p цифр (0 < p < 16) и знак; с помощью q задается число цифр справа от десятичной точки (q < p, если q = 0, оно может быть опущено);
FLOAT
- вещественное число с 15 значащими цифрами и целочисленным порядком, определяемым типом СУБД;
CHAR(n)
- символьная строка фиксированной длины из n символов (0 < n < 256);
VARCHAR(n)
- символьная строка переменной длины, не превышающей n символов (n > 0 и разное в разных СУБД, но не меньше 4096);
DATE
- дата в формате, определяемом специальной командой (по умолчанию mm/dd/yy); поля даты могут содержать только реальные даты, начинающиеся за несколько тысячелетий до н.э. и ограниченные пятым-десятым тысячелетием н.э.;
TIME
- время в формате, определяемом специальной командой, (по умолчанию hh.mm.ss);
DATETIME
- комбинация даты и времени;
MONEY
- деньги в формате, определяющем символ денежной единицы ($, руб, ...) и его расположение (суффикс или префикс), точность дробной части и условие для показа денежного значения.

В некоторых СУБД еще существует тип данных LOGICAL, DOUBLE и ряд других. СУБД INGRES предоставляет пользователю возможность самостоятельного определения новых типов данных, например, плоскостные или пространственные координаты, единицы различных метрик, пяти- или шестидневные недели (рабочая неделя, где сразу после пятницы или субботы следует понедельник), дроби, графика, большие целые числа (что стало очень актуальным для российских банков) и т.п.

Ориентированный на работу с таблицами SQL не имеет достаточных средств для создания сложных прикладных программ. Поэтому в разных СУБД он либо используется вместе с языками программирования высокого уровня (например, такими как Си или Паскаль), либо включен в состав команд специально разработанного языка СУБД (язык систем dBASE, R:BASE и т.п.). Унификация полных языков современных профессиональных СУБД достигается за счет внедрения объектно-ориентированного языка четвертого поколения 4GL. Последний позволяет организовывать циклы, условные предложения, меню, экранные формы, сложные запросы к базам данных с интерфейсом, ориентированным как на алфавитно-цифровые терминалы, так и на оконный графический интерфейс (X-Windows, MS-Windows).

^^^

1.3. Таблицы SQL

До сих пор понятие "таблица", как правило, связывалось с реальной или базовой таблицей, т.е. c таблицей, для каждой строки которой в действительности имеется некоторый двойник, хранящийся в физической памяти машины (рис.1.2). Однако SQL использует и создает ряд виртуальных (как будто существующих) таблиц: представлений, курсоров и неименованных рабочих таблиц, в которых формируются результаты запросов на получение данных из базовых таблиц и, возможно, представлений. Это таблицы, которые не существуют в базе данных, но как бы существуют с точки зрения пользователя.

Базовые таблицы создаются с помощью предложения CREATE TABLE (создать таблицу), подробное описание которого приведено в главе 5. Здесь же приведем пример предложения для создания описания таблицы Блюда:

Рис. 1.2. База данных в восприятии пользователя

CREATE TABLE Блюда
	(БЛ	SMALLINT,
	Блюдо	CHAR (70),
	В		CHAR (1),
	Основа	CHAR (10),
	Выход	FLOAT,
	Труд	SMALLINT);

Предложение CREAT TABLE специфицирует имя базовой таблицы, которая должна быть создана, имена ее столбцов и типы данных для этих столбцов (а также, возможно, некоторую дополнительную информацию, не иллюстрируемую данным примером). CREAT TABLE - выполняемое предложение. Если его ввести с терминала, система тотчас построит таблицу Блюда, которая сначала будет пустой: она будет содержать только строку заголовков столбцов, но не будет еще содержать никаких строк с данными. Однако можно немедленно приступить к вставке таких строк данных, возможно, с помощью предложения INSERT и создать таблицу, аналогичную таблице Блюда рис.1.1.

Если теперь потребовалось узнать какие овощные блюда может приготовить повар пансионата, то можно набрать на терминале следующий текст запроса:

SELECT	БЛ,Блюдо
FROM 	Блюда
WHERE	Основа = 'Овощи';
и мгновенно получить на экране следующий результат его реализации:

БЛБлюдо
1Салат летний
3Салат витаминный
17Морковь с рисом
23Помидоры с луком

Для выполнения этого предложения SELECT (выбрать), подробное описание которого будет дано в главах 2 и 3, СУБД должна сначала сформировать пустую рабочую таблицу, состоящую из столбцов БЛ и Блюдо, тип данных которых должен совпадать с типом данных аналогичных столбцов базовой таблицы Блюда. Затем она должна выбрать из таблицы Блюда все строки, у которых в столбце Основа хранится слово Овощи, выделить из этих строк столбцы БЛ и Блюдо и загрузить укороченные строки в рабочую таблицу. Наконец, СУБД должна выполнить процедуры по организации вывода содержимого рабочей таблицы на экран терминала (при этом если в рабочей таблице содержится более 20-24 строк, она должна использовать процедуры постраничного вывода и т.п.). После выполнения запроса СУБД должна уничтожить рабочую таблицу.

Если, например, надо получить значение калорийности всех овощей, включенных в таблицу Продукты, то можно набрать на терминале запрос

SELECT	Продукт, Белки, Жиры, Углев,
	((Белки+Углев)*4.1+Жиры*9.3)
FROM 	Продукты
WHERE	Продукт IN ('Морковь','Лук','Помидоры','Зелень');
и получить на экране следующий результат его реализации:
ПродуктБелкиЖирыУглев((Белки+Углев)*4.1+Жиры*9.3)
Морковь13.1.70.349.6
Лук17.0.95.459.2
Помидоры6.0.42.196.8
Зелень9.0.20.118.9

В последнем столбце этой рабочей таблицы приведены данные о калорийности продуктов, отсутствующие в явном виде в базовой таблице Продукты. Эти данные вычислены по хранимым значениям основных питательных веществ продуктов, помещены в рабочую таблицу и будут существовать до момента смены изображения на экране. Однако если необходимо сохранить эти данные в какой-либо базовой таблице, то существует предложение (INSERT), позволяющее переписать содержимое рабочей таблицы в указанные столбцы базовой таблицы (реляционная операция присваивания).

Часто пользователя не устраивает как способ описания нужного набора выводимых строк, так и результат выполнения запроса, сформированного из данных одной таблицы. Ему хотелось бы уточнить выводимые (запрашиваемые) данные сведениями из других таблиц.

Например, в запросе на получение состава овощных блюд

SELECT 	БЛ,ПР,Вес
FROM 	Состав
WHERE 	БЛ IN (1,3,17,23);

пришлось перечислять номера этих блюд, так как в таблице Состав нет данных об основных продуктах блюда (они есть в таблице Блюда). Полученный состав овощных блюд (рис.1.3,а) оказался "слепым": в нем и блюда и продукты представлены номерами, а не именами. Удобнее и нагляднее (рис.1.3,б)

а)б)
БЛПРВесБлюдо
111100Салат летний
11580Салат летний
1125Салат летний
1415Салат летний
31155Салат витаминный
31555Салат витаминный
3650Салат витаминный
31220Салат витаминный
31015Салат витаминный
3165Салат витаминный
179150Морковь с рисом
17750Морковь с рисом
171325Морковь с рисом
17320Морковь с рисом
171210Морковь с рисом
17145Морковь с рисом
2311250Помидоры с луком
231065Помидоры с луком
23320Помидоры с луком
ПродуктВес
Помидоры100
Яблоки80
Зелень5
Майонез15
Помидоры55
Яблоки55
Сметана50
Зелень20
Лук15
Сахар5
Морковь150
Молоко50
Рис25
Масло20
Зелень10
Мука5
Помидоры250
Лук65
Масло20

Рис. 1.3. Состав овощных блюд базы данных ПАНСИОН

запрос сформированный по трем таблицам:
SELECT	Блюдо, Продукт, Вес
FROM	Состав,Б люда, Продукты
WHERE	Состав.БЛ = Блюда.БЛ
AND	Состав.ПР = Продукты.ПР
AND	Основа = 'Овощи';

В нем для получения рабочей таблицы выполняется естественное соединение [2] таблиц Блюда, Продукты и Состав (условие соединения - равенство значений номеров блюд и значений номеров продуктов). Затем выделяются строки, у которых в столбце Основа хранится слово Овощи, и из этих строк - столбцы Блюдо, Продукт и Вес.

Если пользователи достаточно часто интересуются составом различных блюд, то для упрощения формирования запросов целесообразно создать представление.

Представление - это пустая именованная таблица, определяемая перечнем тех столбцов таблиц и признаками тех их строк, которые хотелось бы в ней увидеть. Представление является как бы "окном" в одну или несколько базовых таблиц. Оно создается с помощью предложения CREATE VIEW (создать представление), подробное описание которого приведено в главе 5. Здесь же приведем пример предложения для создания представления Состав_блюд:

CREATE VIEW 	Состав_блюд
AS SELECT 	Блюдо, Продукт, Вес
FROM 	Состав,Блюда,Продукты
WHERE 	Состав.БЛ = Блюда.БЛ
AND 	Состав.ПР = Продукты.ПР;

Оно описывает пустую таблицу, в которую при реализации запроса будут загружаться данные из столбцов Блюдо, Продукт и Вес таблиц Блюда, Продукты и Состав, соответственно. Теперь для получения состава овощных блюд можно дать запрос

SELECT 	Блюдо,Продукт,Вес
FROM 	Состав_блюд
WHERE 	Основа = 'Овощи';

и получить на экране терминала данные, которые представлены на рис. 1.3,б. А для получения состава супа Харчо можно дать запрос

SELECT	Блюдо, Продукт, Вес
FROM 	Состав_блюд
WHERE	Блюдо = 'Суп харчо';

О целесообразности создания представлений будет рассказано ниже, а здесь лишь отметим, что они позволяют повысить уровень логической независимости данных, упростить их восприятие и "скрыть" от некоторых пользователей те или иные данные, например, данные о новых ценах на продукты первой необходимости или из какой рыбы приготавливается "Судак по-польски".

Наконец, еще об одних виртуальных таблицах - курсорах. Курсор - это пустая именованная таблица, определяемая перечнем тех столбцов базовых таблиц и признаками тех их строк, которые хотелось бы в ней увидеть. В чем же различие между курсором и представлением?

Для пользователя представления почти не отличаются от базовых таблиц (есть лишь некоторые ограничения при выполнении различных операций манипулирования данными). Они могут использоваться как в интерактивном режиме, так и в прикладных программах. Курсоры же созданы для процедурной работы с таблицей в прикладных программах. Например, после объявления курсора

DECLARE	Блюд_состав CURSOR FOR
SELECT	Блюдо,Продукт,Вес
FROM	Состав,Блюда,Продукты
WHERE	Состав.БЛ = Блюда.БЛ
AND	Состав.ПР = Продукты.ПР
AND	Блюдо = 'Суп харчо';

и его активизации (OPEN Блюд_состав) будет создана временная таблица с составом блюда "Суп харчо" и специальным указателем, определяющим в качестве текущей первую строку этой таблицы. С помощью предложения FETCH (выбрать), которое обычно исполняется в программном цикле, можно присвоить определенным переменным значения указанных столбцов этой строки. Одновременно курсор будет передвинут к следующей строке таблицы. После обработки в программе полученных значений переменных выполняется следующее предложение FETCH и т.д. до окончания перебора всех продуктов Харчо.

^^^

Далее >>>

 

© Krio, Xbyte, BooM
2004-2012

id-sign