Понятие данных, принципы построения информационных систем

              | WHERE inner.snum = outer.snum                 |

              | AND inner.cnum < > outer.cnum);               |

              | ============================================= |

              |   cnum                                        |

              |  -----                                        |

              |   1001                                        |

              |   1002                                        |

                =============================================

Рисунок 12. 2: Использование EXISTS с соотнесенным подзапросом  
 
Для каждой строки-кандидата внешнего запроса ( представляющей заказчика проверяемого в настоящее время ), внутренний запрос находит строки которые совпадают со значением пол snum ( которое имел продавец ), но не со значением пол cnum ( соответствующего другим заказчикам ). Если любые такие строки найдены внутренним запросом, это означает, что имеются два разных заказчика обслуживаемых текущим продавцом ( то есть продавцом заказчика в текущей строке-кандидата из внешнего за- проса ). Предикат EXISTS поэтому верен для текущей строки, и номер продавца пол (snum) таблицы указанной во внешнем запросе будет выведено. Если был DISTINCT не указан, каждый из этих продавцов будет выбран один раз для каждого заказчика к которому он назначен.

КОМБИНАЦИЯ ИЗ EXISTS И ОБЪЕДИНЕНИЯ

Однако для  нас может быть полезнее вывести  больше информации об этих продавцах  а не только их номера. Мы можем сделать  это объединив таблицу Заказчиков с таблицей Продавцов ( вывод для  запроса показывается в Рисунке 12.3 ):

 

             SELECT DISTINCT first.snum, sname, first.city

                FROM Salespeople first, Customers second

                WHERE EXISTS

                   ( SELECT *

                      FROM Customers third

                      WHERE second.snum = third.snum

                            AND second.cnum < > third.cnum )

                   AND first.snum = second.snum;

 

               ===============  SQL Execution Log ============

              |                                               |

              | SELECT DISTINCT first.snum, sname, first.city |

              | FROM  Salespeople first, Customers second     |

              | WHERE EXISTS                                  |

              | (SELECT *                                     |

              | FROM Customers third                          |

              | WHERE second.snum = third.snum                |

              | AND second.cnum < > third.cnum)               |

              | AND first.snum = second.snum;                 |

              | ============================================= |

              |   cnum     cname     city                     |

              |  -----    --------   ----                     |

              |   1001    Peel       London                   |

              |   1002    Serres     San Jose                 |

                =============================================

Рисунок 12.3: Комбинация EXISTS с объединением  
 
Внутренний запрос здесь - как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен. Внешний запрос - это объединение таблицы Продавцов с таблицей Заказчиков, наподобие того что мы видели прежде. Новое предложение основного предиката ( AND first.snum = second.snum ) естественно оценивается на том же самом уровне что и предложение EXISTS. Это - функциональный предикат самого объединения, сравнивающий две таблицы из внешнего запроса в терминах пол snum, которое являются для них общим. Из-за Булева оператора AND, оба условия основного предиката должны быть верны в порядке для верного предиката. Следовательно, результаты подзапроса имеют смысл только в тех случаях когда вторая часть запроса верна, а объединение - выполнимо. Таким образом, комбинация объединения и подзапроса может стать очень мощным способом обработки данных.

ИСПОЛЬЗОВАНИЕ NOT EXISTS

Предыдущий  пример дал понять что EXISTS может  работать в комбинации с операторами  Бул. Конечно, то что является самым  простым способом для использования  и вероятно наиболее часто используется с EXISTS - это оператор NOT. Один из способов которым мы могли бы найти всех продав- цов только с одним заказчиком будет состоять в том, чтобы инвертировать  наш предыдущий пример. ( Вывод для этого запроса показывается в Рисунке 12.4:) SELECT DISTINCT snum FROM Customers outer WHERE NOT EXISTS ( SELECT * FROM Customers inner WHERE inner.snum = outer.snum AND inner.cnum < > outer.cnum );

EXISTS И АГРЕГАТЫ

Одна вещь которую EXISTS не может сделать - взять  функцию агрегата в подзапросе. Это  имеет значение. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, не взирая на то, что  это - значение функции ; если же агрегатная функция не находит никаких строк, EXISTS неправилен.

 

 

               ===============  SQL Execution Log ============

              |                                               |

              | SELECT DISTINCT snum                          |

              | FROM  Salespeople outer                       |

              | WHERE NOT EXISTS                              |

              | (SELECT *                                     |

              | FROM Customers inner                          |

              | WHERE inner.snum = outer.snum                 |

              | AND inner.cnum < > outer.cnum);               |

              | ============================================= |

              |   cnum                                        |

              |  -----                                        |

              |   1003                                        |

              |   1004                                        |

              |   1007                                        |

                =============================================

Рисунок 12.4: Использование EXISTS с NOT  
 
Попытка использовать агрегаты с EXISTS таким способом, вероятно покажет что проблема неверно решалась от начала до конца. Конечно, подзапрос в предикате EXISTS может также использовать один или более из его собственных подзапросов. Они могут иметь любой из различных типов которые мы видели ( или который мы будем видеть ). Такие подзапросы, и любые другие в них, позволяют использовать агре- гаты, если нет другой причины по которой они не могут быть использо- ваны. Следующий раздел приводит этому пример.  
 
В любом случае, вы можете получить тот же самый результат более легко, выбрав поле которое вы использовали в агрегатной функции, вместо использования самой этой функции. Другими словами, предикат - EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) - будет эквивалентен - EXISTS (SELECT sname FROM Salespeople) который был позволен выше. 
Рекурсивные запросы. 
Рекурсивные SQL-запросы являются одним из способов решения проблемы дерева и других проблем, требующих рекурсивную обработку. Они были добавлены в стандарт SQL 99. До этого они уже существовали в Oracle. Несмотря на то, что стандарт вышел так давно, реализации запоздали. Например, в MS SQL они появились только в 2005-ом сервере. 
 
Рекурсивные запросы используют довольно редко, прежде всего, из-за их сложного и непонятного синтаксиса:

with [recursive] <имя_алиаса_запроса> [ (<список столбцов>) ] 
as (<запрос>)  
<основной запрос>

В MS SQL нет ключевого слова recursive, а в остальном все тоже самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL и во всех базах  данных, которые поддерживают стандарт SQL 99.

Проще разобрать на примере. Предположим, есть таблица:

create table tree_sample ( 
  id integer not null primary key,  
  id_parent integer foreign key references tree_sample (id),  
  nm varchar(31) )

id - идентификатор 
id_parent - ссылка на родитель 
nm - название.

Для вывода дерева:

with recursive tree (nm, id, level, pathstr) 
as (select nm, id, 0, cast(`` as text)  
   from tree_sample 
   where id_parent is null  
union all 
   select tree_sample.nm, tree_sample.id, t.level + 1, tree.pathstr + tree_sample.nm 
   from tree_sample  
     inner join tree on tree.id = tree_sample.id_parent)  
select id, space( level ) + nm as nm  
from tree  
order by pathstr

Этот пример выведет дерево по таблице  с отступами. Первый запрос из tree_sample этот запрос выдаст все корни дерева. Второй запрос соединяет между собой  таблицу tree_sample и tree, которая определяется этим же запросом. Этот запрос дополняет  таблицу узлами дерева.

Сначала выполняется первый запрос. Потом к его результатам добавляются  результаты второго запроса, где  данные таблица tree - это результат  первого запроса. Затем снова  выполняется второй запрос, но данные таблицы tree - это уже результат  предыдущего выполнения второго  запроса. И так далее. На самом  деле база данных работает не совсем так, но результат будет таким же, как  результат работы описанного алгоритма.

После этого данные этой таблицы  можно использовать в основном запросе  как обычно.

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

 

 
Структурированные запросы. 
SQL (англ. Structured Query Language — язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных.

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

SQL основывается на реляционной  алгебре. 

 
Представления (view). 
Представления (VIEW) 4.5.1. Понятие представления

Представления (другие варианты перевода - просмотры, виды) - это именованные запросы на выборку, сохранённые в БД, которые при любом обращении к ним по имени создают виртуальную таблицу, наполняя ее актуальными данными из БД.

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

сервер			клиент
\ '		■ • ■ - * ■ -■ ■ ■ ■ •
С   текст запроса    j
\ г					V
оптимизатор запроса
				клиентское приложение
\	f
j*^~*             "*—-v.
/план исполнения^ Ч^^ запроса  ^^J				j	V
\	t
процессор БД
\	f
/^~^ выходная ^^ч Ч.       таблица        J
■-	/

Рис.4.2. Порядок обработки SQL-запроса SELECT ...

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

Оптимизатор запроса передает план исполнения запроса  другому компоненту СУБД, который  называется процессором базы данных (или

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

Запросы на выборку, которые необходимо выполнять регулярно, нет смысла пересылать по сети и компилировать  каждый раз, как только клиенту потребуется соответствующая выборка данных. Разумно постоянно хранить в базе данных тексты таких запросов вместе с планами их исполнения.

Может возникнуть вопрос, зачем хранить  исходные тексты запросов, а не ограничиться только планами их исполнения? Дело в том, что при наличии исходного текста имеется возможность перестройки плана исполнения запроса, если старый план окажется уже не самым оптимальным (во всяком случае, СУБД Oracle такую возможность поддерживает).

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

Сказанное означает, что во всех запросах на выборку  SELECT можно использовать имя представления везде, где можно использовать имя таблицы (т.е. при формулировании запросов на выборку пользователь может даже не знать, чем он пользуется - таблицей или представлением). Более того, некоторые представления (но не все) можно использовать даже в запросах INSERT, DELETE и UPDATE, при этом будут внесены соответствующие изменения в реальные таблицы.