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,б)

а)

б)

БЛ ПР Вес Блюдо 1 11 100 Салат летний 1 15 80 Салат летний 1 12 5 Салат летний 1 4 15 Салат летний 3 11 55 Салат витаминный 3 15 55 Салат витаминный 3 6 50 Салат витаминный 3 12 20 Салат витаминный 3 10 15 Салат витаминный 3 16 5 Салат витаминный 17 9 150 Морковь с рисом 17 7 50 Морковь с рисом 17 13 25 Морковь с рисом 17 3 20 Морковь с рисом 17 12 10 Морковь с рисом 17 14 5 Морковь с рисом 23 11 250 Помидоры с луком 23 10 65 Помидоры с луком 23 3 20 Помидоры с луком

Продукт Вес Помидоры 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 и т.д. до окончания перебора всех продуктов Харчо.

1.2 | Содержание | 2.1