Языки SQL и QBE презентация

Языки SQL и QBE Бессарабов Н.В. [email protected] 2019 г.

Цели лекции В этой лекции будут бегло рассмотрены основы наиболее известных языков баз данных реляционного типа -- SQL и QBE. Из-за недостатка времени в рамках курса основ баз данных невозможно сколько-нибудь подробное изучение даже одного языка SQL. Это большой язык. Мы уже обнаружили, что реляционная алгебра и исчисления позволяют построить только языки запросов, причем с весьма ограниченными возможностями. Для практической работы необходимо ещё создавать и перестраивать схемы базы, манипулировать данными, организовывать транзакции. Появляются подъязыки определения дан- ных, манипулирования данными, управления данными, соответственно. Расширения языка запросов выводят его за рамки исходной реляционной модели. Современные версии SQL имеют ядро, основан- ное на исчислении на кортежах, но в них используются встроенные представления, характерные скорее для реляционной алгебры, процедурные фрагменты, многомерные модели, регулярные выражения, позволяющие препарировать значения в столбцах и многое другое. И последнее. SQL определяет требования к результату, но не дает алгоритма его получения. Поэтому СУБД должна генерировать план исполнения, который определяет способы доступа к данным. Настройка плана исполнения это отдельная и большая тема.

Часть I. Язык SQL

История из реальной жизни …

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

Немного истории

Та же история, но чуть подробнее

Подъязыки SQL Выделяются следующие подъязыки: Язык определения данных (ЯОД). Он же Data Definition Language (DDL). Определяет структуру базы,задает пользователей, хранимые объекты и привилегии доступа к ним. Язык манипулирования данными (ЯМД). Он же Data Manipulation Language (DML). Вставляет, обновляет и удаляет данные и выполняет запросы к ним. Язык управления данными (транзакциями) Data Control Language (DCL).

О терминологии SQL Вспомним, что язык SQL оперирует терминами, отличающимися от терминов принятых в реляционной теории:

Базы, схемы, хранимые объекты базы Хранимые объекты базы реляционного типа, образующие схему базы: Таблицы – в них хранятся данные. Представления (view) – обеспечивают фильтрацию данных Индексы (обычно B* и побитовые) – могут ускорить доступ к данным. Триггеры – поддерживают процедурные ограничения целостности. Последовательности (sequence). Пользователи (user). Привилегии и роли пользователей. Современные СУБД имеют мощную процедурную часть (COS в Caché, PL/SQL в Oracle, TSQL в SQLServer). В ней добавляются следующие хранимые объекты: Процедуры (procedure). Функции (function). Триггеры. Курсоры. Процедуры и функции могут группироваться в пакеты. Замечание: Представления (виды, view) будут рассмотрены в этой лекции позднее. Индексы будут изучаться в разделе СУБД”. Процедурный язык Oracle PL/SQL в настоящем курсе изучается в минимальном объёме.

Язык DDL. Операторы определения объектов базы данных Для каждого типа хранимых объектов базы (таблица, представление, последовательность, триггер, пользователь, но не курсор) существует “малый джентльменский” набор инструкций CREATE, ALTER, DROP (СОЗДАТЬ, ИЗМЕНИТЬ, УДАЛИТЬ), например: CREATE TABLE - создать таблицу ALTER TABLE - изменить таблицу DROP TABLE - удалить таблицу или CREATE VIEW - создать представление DROP VIEW - удалить представление ALTER VIEW – изменить представление Замечание: В стандарте предусмотрены еще инструкции для схем и доменов. Здесь они не приведены, так как домены в СУБД обычно не реализуются, а схемы иногда определяют косвенным образом, через пользователей, которые ими владеют.

Виды таблиц в Oracle Heap organized tables - обычные таблицы (heap - куча), данные хранятся неупорядоченно. Index organized tables (IOT) - данные хранятся в упорядоченном виде, отсортированы по ПК. Nested tables (nested - вложенный) - это часть объектно-реляционных расширений для Oracle. Например, в схеме SCOTT таблицу EMP можно сделать вложенной в таблицу DEPT, реализуя связь предок – потомок (1:N). Temporary tables - врéменные таблицы – содержат данные сохраняющиеся во время транзакции или сессии. Object tables – объектные таблицы. Имеют сгенерированный системой REF (идентификатор объекта) для каждой строки. Объектные таблицы - это особый вариант heap-таблиц. Могут содержать вложенные таблицы. Интересны как пример эмуляции объектной модели в табличной. External tables – внешние таблицы. Данные в них хранятся в самой базе данных, а находятся в файлах операционной системы. Можно запрашивать файл, находящийся вне базы данных, как если бы это была обычная таблица в базе данных. Внешние таблицы это инструмент для загрузки данных в базу. Кластерные таблицы. Многоверсионные (временные (исторические)) таблицы и flashback-технология. Таблицы PL/SQL. Секционированные.

Создание таблицы (1/3) Часть синтаксиса инструкции создания таблицы: CREATE TABLE имя_таблицы (столбец [,{столбец|именованное_ограничение_целостности}] .... ) где столбец ::= имя_столбца тип [неимен_огр_целостности] DEFAULT значение_по_умолчанию неимен_огр_целостн ::= NULL | NOT NULL | UNIQUE | PRIMARY KEY Замечание : Неименованные ограничения целостности не имеют имени заданного пользователем, но СУБД называет их своими именами. Именованные ограничения целостности называются ещё ограничениями уровня таблицы. В простейшем варианте их синтаксис: именованное_ограничение_целостности::= CONSTRAINT имя_ограничения определение_ограничения Пример простой инструкции create table: CREATE TABLE qq (c1 NUMBER(3) PRIMARY KEY, c2 CHAR(5)) Другой вариант: CREATE TABLE qq (c1 NUMBER(3), c2 CHAR(5), CONSTRAINT myPK PRIMARY KEY (с1))

Создание таблицы (2/3) Виды ограничений целостности: NOT NULL | NULL — ограничитель NOT NULL запрещает вводить и хранить пустые значения; UNIQUE --- определяет уникальный ключ; формат ограничения уровня таблицы [CONSTRAINT имя_ограничения] UNIQUE (столбец1, столбец2, ....) PRIMARY KEY --- обеспечивает уникальность набора значений перечисленных полей; естественно, пустые значения в отличие от UNIQUE запрещены; формат ограничения для уровня таблицы: [CONSTRAINT имя_ограничения] PRIMARY KEY (столбец1, столбец2, ....) FOREIGN KEY --- указывает, что перечисленные столбцы составляют внешний ключ; с каждым внешним ключом связаны первичный или уникальный ключи (для них заданы ограничения типа UNIQUE или PRIMARY KEY); формат на уровне таблицы CONSTRAINT имя_ограничения FOREIGN KEY (столбец1, столбец2, ....) REFERENCES таблица (столбец1, [столбец2], .....) CHECK --- задает условие, которому должны удовлетворять значения столбцов в каждой строке; формат [CONSRAINT имя_ограничения] CHECK (условие)

Создание таблицы (3/3) Удобно пользоваться синтаксическими диаграммами. Ниже пример для Oracle 11g. Обратите внимание, насколько в реальной жизни всё усложняется.

CREATE TABLE. Heap-таблицы в Oracle Первичный и внешний ключи: CREATE TABLE example5 ( table_id NUMBER(10) PRIMARY KEY, example_table_id NUMBER(10) REFERENCES example4(table_id), first_name VARCHAR2(50), last_name VARCHAR2(200) ); Ограничения целостности и значения по умолчанию: CREATE TABLE projx ( projno NUMBER (4) NOT NULL , pname VARCHAR2 (14) CHECK (SUBSTR(pname,1,1) BETWEEN 'A' AND 'Z') , bdate DATE DEFAULT TRUNC ( SYSDATE ) , budget NUMBER (10,2) ); Версия с запросом AS SELECT: CREATE TABLE dept_copy AS SELECT * FROM dept; CREATE TABLE emps ( name, department ) AS SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno; Запрос AS SELECT передающий структуру таблицы, но не данные: CREATE TABLE dept_new AS (SELECT * FROM dept WHERE 1=0);

Удаление и изменение таблиц Удаление таблицы: DROP TABLE имя_таблицы [CASCADE|RESTRICT] Изменение таблицы (опять неполный синтаксис): ALTER TABLE имя_таблицы {[ADD (столбец|ограничение_уровня_таблицы [, столбец|ограничение_уровня_таблицы] ..... ] [MODIFY (столбец [, столбец] .....)] [DROP столбец|ограничение] ..... [ENABLE|DISABLE ограничение [CASCADE]]} Во фразах ADD и MODIFY “столбец” более подробно выглядит так: cтолбец ::=имя_столбца тип_данных [DEFAULT выражение] ограничение_уровня_столбца Простейший вариант: cтолбец::= имя_столбца тип_данных

Изменение таблиц в Oracle (малая часть диаграммы)

Языки DML и DCL. Манипулирование и управление данными Манипулирование данными (DML): INSERT - добавить строки в таблицу; UPDATE - изменить строки в таблице; DELETE - удалить строки в таблице. Управление данными (DCL): COMMIT - зафиксировать внесенные изменения; ROLLBACK - откатить внесенные изменения. Отсутствие инструкций, определяющих начало транзакции, объясняется тем, что по стандарту ANSI/ISO транзакция начинается автоматически, как только пользователь подключается к базе или после завершения предыдущей транзакции. Инструкции управления данными рассмотрены в лекции о транзакциях. Замечание: Начало транзакции не задается в Oracle, в Caché используют команды %BEGTRANS в SQL и TStart для COS.

Инструкции DML Новая строка вводится в таблицу инструкцией INSERT, имеющей в простейшем случае формат: INSERT INTO имя_таблицы_или_представления [(столбец [,столбец] .... )] VALUES (значение|NULL [, значение|NULL] .....) Перечень столбцов после имени таблицы указывает столбцы, в которые вводят значения (по умолчанию ввод во все столбцы). После слова VALUES перечисляют вводимые значения. Изменение существующих строк выполняет инструкция UPDATE: UPDATE имя_таблицы_или_представления SET столбец=выражение [,столбец=выражение] ...... [WHERE условие]; Удаляются строки из таблицы инструкцией DELETE: DELETE [FROM] имя_таблицы_или_представления [ WHERE условие] Если фраза WHERE отсутствует, будут удалены все строки. Замечание: Для безвозвратного удаления используют инструкцию TRUNCATE.

1.2. Запросы в SQL

Язык SQL. Запрос в рамках TRC Если оставаться строго в рамках исчисления на кортежах, то инструкция SELECT (по-русски “выбрать”) должна состоять минимум из двух фраз SELECT и FROM (по-русски “из”). Синтаксис: SELECT DISTINCT {[*] { столбец|константа[ псевдоним]}, ... } FROM {таблица, ... } Фраза FROM задает список таблиц, из которых производится выборка, а слово DISTINCT позволяет избежать дублирования строк, недопустимого в реляционной модели. Если список содержит более одной таблицы, то образуется декартово произведение. В максимальном варианте запрос в рамках исчисления на кортежах имеет формат: SELECT DISTINCT {[*] |{столбец|константа [псевдоним]}, ..... } FROM {таблица, ....... } WHERE условие(я) Добавленная фраза WHERE (по-русски “где”) определяет условия, которым должны удовлетворять выбираемые кортежи, а также условия соединения таблиц, упомянутых во фразе WHERE. Как вы видели на практике, этого слишком мало! Замечание: Функций от столбцов и констант в TRC нет.

Язык SQL. Простейший запрос А теперь как простой SELECT выглядит на самом деле: SELECT [DISTINCT] {[*]|{столбец|константа|функция[ псевдоним]}, .....} FROM {таблица, ....... } [WHERE условие(я)] [GROUP BY список_столбцов] [ORDER BY {столбец|выражение, .... } [ASC|DESC]] Символ “*” означает выбор всех столбцов. DISTINCT теперь не обязательный символ (в SQL допустимы и повторы). Фраза ORDER BY (“упорядочить по”) всегда стоит последней в SELECT’е и задает упорядочение строк. Как вы помните, в реляционной модели строки не упорядочены. Упорядочение по умолчанию ведётся по возрастанию (ASCENDING), можно задать упорядочение по убыванию (DESCENDING). Функции во фразе SELECT могут быть одно- и многострочными. Последние ещё называют групповыми. Способ группирования определяется списком столбцов во фразе GROUP BY. Функции от значений в столбцах в реляционной теории не предусмотрены. Таким образом, уже простой запрос использующий функции, фразы GROUP BY и ORDER BY выводит нас за пределы реляционной теории.

Выполнение однотабличного запроса Запрос выполняется путём поочерёдного применения фраз, образующих инструкцию: По фразе FROM выбираются (считываются) все строки указанной таблицы. Учитывается псевдоним, если он задан. Если имеется фраза WHERE, то отбираются строки, удовлетворяющие заданному в ней условию. По списку фразы SELECT создаются столбцы таблицы результата, вычисляются все значения во всех отобранных строках (в списке SELECT могут быть функции). Если имеется слово DISTINCT, из полученной таблицы результатов удаляются все повторяющиеся строки. Если имеется фраза ORDER BY, то результаты отсортировывают по значениям записанных в ней выражений. Если бы можно было записывать запрос как последовательность фраз FROM, WHERE, SELECT, ORDER BY, что допускается русским языком, то не пришлось бы вспоминать порядок действий.

Сравнение запросов SQL и запросов в языке TRC (1/3) Расширения языка запросов SQL по сравнению с языком TRC многочисленны и существенны. Как упоминалось выше, язык запросов SQL содержит небольшой слой, соответствующий реляционному исчислению на кортежах. Бóльшая часть языка находится вне рамок исчисления и была добавлена исходя из потребностей пользователей. Заметим, что это обычная судьба долго живущих и широко используемых языков программирования, независимо от их назначения. Перечислим некоторые расширения, частично упомянутые ранее: 1. Многочисленные однострочные функции. Например, функция SUBSTR(имя, начальная_позиция, длина), которая вырезает часть строки, функция DUMP(имя |строка) в СУБД Oracle, возвращающая внутреннее представление данных. Нестандартная функция DECODE(имя, рез1, зн1, рез2, зн2, …значение_по_умолч) анализирует “имя” и если его значение равно “рез1”, то возвращается “зн1”, если равно “рез2” возвращает “зн2”, и т.д., если “резi” не найдено, вернётся значение по умолчанию.

Сравнение запросов SQL и запросов в языке TRC (2/3) Выражение CASE также играет роль встроенного IF-THEN-ELSE. Пример: SELECT ename, job, sal, (CASE job WHEN 'CLERK' THEN 1.10*sal WHEN 'SALESMAN' THEN 1.20*sal ELSE 1.05*sal END) NEW_SAL FROM emp 2. Использование оператора IN (означает “содержится в списке”) во фразе WHERE Пример: Запрос SELECT ename, sal FROM emp WHERE sal IN (1000, 1700, 2000) вернёт сведения о работниках с зарплатой 1000, 1700 или 2000. 3. Использование фразы GROUP BY, обеспечивающей группирование данных и работу многострочных функций. Пример: Запрос SELECT deptno, SUM(sal) FROM emp GROUP BY deptno выдает суммарную заработную плату по отделам.

Сравнение запросов SQL и запросов в языке TRC (3/3) 4. Использование строк с разделителями и списков в качестве значений, хранящихся в таблицах. С ними связаны регулярные выражения, то есть шаблоны, предназначенные для поиска и обработки текста. Языки регулярных выражений встраиваются в другие языки, в том числе в SQL или в JavaScript. В частности, регулярные выражения позволяют разбирать поля номеров счетов. 5. Реализация рекурсивных запросов, эквивалентных заданию переменного (выбираемого) числа соединений. 6. Использование коррелирующих подзапросов, которые срабатывают многократно, причем начинает работу основной запрос, затем подзапрос готовит строку-кандидата для основного запроса, который обрабатывает её и т. д. 7. Эмуляция группирования и многомерной модели за счёт аналитических функций а также конструкций CUBE, ROLLUP, MODEL. 8. Работа с неатомарными значениями (XML и регулярные выражения) Из рассмотренных конструкций мы бегло рассмотрим только рекурсивные и коррелирующие подзапросы и регулярные выражения.

Три вида запросов SQL Простые запросы (запросы без подзапросов) Соединения запросов Запросы с подзапросами Учитывая что самые простые запросы без подзапросов мы уже рассмотрели, начнём с объединений простых запросов. Затем перейдём к простым запросам над одной или несколькими таблицами требующими соединений таблиц (joins).

Соединение результатов запросов Результаты нескольких запросов можно объединить операциями UNION и UNION ALL. Объединение возможно, если результирующие таблицы соединяемых запросов имеют одинаковое число столбцов попарно одинаковых типов. Имена соответствующих столбцов могут различаться. Структура соединения: UNION [ALL] [ORDER BY …..] Замечание: В соединениях запросов могут использоваться сложные выражения, в том числе с другими операциями над множествами (INTERSECT, MINUS, ..). Такие запросы имеют плохие планы исполнения и потому в практике почти не используются. UNION удаляет повторы одинаковых строк, а UNION ALL оставляет повторяющиеся строки. Выполнение запросов с UNION: Выполнить составляющие запросы. Объединить результаты разрешая или удаляя повторы. Если имеется фраза ORDER BY, упорядочить результат.

Соединения таблиц Соединения двух и более таблиц могут выполняться в одном запросе с указанием условий соединения. Пример: Выбрать фамилии сотрудников, номера и названия отделов, в которых они работают. SELECT ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno=dept.deptno Соединяем те строки таблиц emp и dept, которые имеют одинаковые значения столбца deptno. Поскольку deptno имеется в обеих таблицах, в условии соединения следует уточнить название столбца названием его таблицы, например, emp.deptno. В списке фразы SELECT только для одного столбца необходимо указание таблицы emp.deptno или dept.deptno. Если этого не сделать, появится сообщение об ошибке. Остальные столбцы ename и dname имеются только в одной таблице. При желании префиксы можно поставить и перед их именами. Замечание: Различайте связи и соединения таблиц. Связи работают во время манипулирования данными, обеспечивая выполнение ограничений ссылочной целостности. Соединения создаются в запросах. Их смысл целиком на совести программиста создающего запрос.

Внутренние и внешние соединения В рассмотренном на предыдущем слайде примере и операциях соединения реляционной алгебры (по равенству и не по равенству) соединялись существующие строки двух и более таблиц/отношений. (А как иначе?) Такие соединения называются внутренними. Существуют ещё внешние соединения. В них строка одной таблицы может соединяться с пустой строкой из другой таблицы. Несмотря на кажущуюся странность этой операции, она отражает некоторый смысл, имеющийся в моделях бизнеса. Поясним это на примере. Предварительно необходимо в таблицу emp ввести отдел с номером 50, находящийся, например, в Краснодаре и занимающийся маркетингом. Эти детали несущественны. Важно лишь то, что в новом отделе нет сотрудников. Пример: Просмотреть список сотрудников во всех отделах, указав названия отделов. SELECT ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno=dept.deptno Это внутреннее соединение. В ответе отсутствует отдел 50. Поэтому пользователь может считать, что такого отдела нет. Но мы же знаем, что отдел существует, только список его сотрудников пустой. Избежать подобных казусов позволяют внешние соединения. Замечание: Обратите внимание на то, что формулировка задания в примере могла быть переформулирована так, чтобы возможность существования отделов без сотрудников была предусмотрена.

Внешние соединения Для задания внешнего соединения до появления стандарта SQL92 во фразе WHERE использовались специальные обозначения, свои для каждого производителя. Например, Oracle использовал знак (+). Пример: Правильное решение предыдущего примера с использованием левого внешнего соединения в Oracle. SELECT ename, dept.deptno, dname FROM emp, dept WHERE emp.deptno(+) = dept.deptno Теперь в ответе присутствует отдел 40, но сотрудников в нём нет. Существуют: Левое внешнее соединение. Правое внешнее соединение. Полное внешнее соединение. Будем предполагать, что столбцы в условии соединения фразы WHERE записаны в том же порядке, что их таблицы во фразе FROM. Тогда соединение будет левым, если во второй таблице нет строк, соответствующих строкам первой. У полного внешнего соединения приходится дополнять пустыми значениями и строки первой и строки второй таблицы.

Выполнение внешних соединений Порядок действий при выполнении полного внешнего соединения двух таблиц: Построить внутреннее cоединение таблиц. Каждую строку первой таблицы, для которой не найдена соответствующая строка второй таблицы, добавить в результат запроса, приписав строку второй таблицы со значениями NULL. Каждую строку второй таблицы, для которой не найдена соответствующая строка первой таблицы, добавить в результат запроса, приписав строку первой таблицы со значениями NULL. Левое внешнее cоединение получится, если не выполнять п. 3. Правое внешнее cоединение получится, если не выполнять п. 2. Замечание о соединениях: Ещё раз подчеркнём, что семантика данных - результатов соединений, полностью на совести программиста. Соединяя таблицы про человека и морковь можете вычислить средний вес человекоморковки. Остаётся мелочь – додумать, что бы это значило и зачем вам это нужно.

Соединения в стандарте SQL92 (1/2) В стандарте SQL92 внешние соединения определяются во фразе FROM, которая получает сложный синтаксис. Мы рассмотрим основные частные случаи. 1. Внутреннее соединение. Основной вариант. Синтаксис: SELECT список_SELECT FROM имя_таблицы INNER JOIN имя_таблицы ON условие_соединения Пример: 2. Естественное внутреннее соединение. Синтаксис: SELECT список_SELECT FROM имя_таблицы INNER JOIN имя_таблицы USING список_столбцов В рассматриваемом примере используется естественное соеди- нение. Перепишем запрос:

Соединения в стандарте SQL92 (2/2) Внешние соединения – полное, левое, правое. Синтаксис: SELECT список_SELECT FROM имя_таблицы FULL|LEFT|RIGHT OUTER JOIN имя_таблицы ON условие_соединения В естественном внешнем соединении фраза ON условие_соединения, как в пп.1,2 заменяется фразой USING список_столбцов Пример:

Запросы с группированием Фраза GROUP BY, упоминавшаяся ранее, обеспечивает объединение строк с одинаковыми значениями в перечисленных столбцах. Такое преобразование необходимо для получения итоговых данных с помощью многострочных (они же статистические или агрегатные) функций MIN(), MAX(), SUM(), COUNT(), AVG() и др. Пример: Найти суммарную заработную плату по отделам. SELECT deptno, SUM(sal) salary FROM emp GROUP BY deptno При использовании функций во фразе SELECT очень часто применяют псевдонимы, чтобы обеспечить читаемую шапку таблицы результата. Если убрать фразу GROUP BY, то образуется одна группа из всех строк таблицы. Такое умолчание допустимо не всегда. Аргументы функций SUM, AVG и COUNT могут уточняться указанием DISTINCT. Примеры (Не очень умные, но поясняющие суть дела): SELECT COUNT(sal) FROM emp SELECT COUNT(DISTINCT sal) FROM emp SELECT COUNT(comm) FROM emp Первый запрос выдаёт количество сотрудников получающих зарплату, второй -- количество разных зарплат, а третий – количество сотрудников, которые должны получать комиссионные (NULL не учитывается, 0 считается).

Выполнение запросов с группированием Порядок действий при выполнении запросов с фразой GROUP BY: По фразе FROM выбираются все строки. Если имеется фраза WHERE, применить к строкам условие отбора, выбрав только те строки,для которых условие выполняется. Разделить оставшиеся строки на группы строк имеющих одинаковые значения во всех столбцах, по которым производится группирование, описанное фразой GROUP BY. Если в аргументе указан спецификатор DISTINCT, удалить все повторяющиеся строки. Для каждой группы строк вычислить значения групповых функций, создав одну строку результата запроса. Вычисления проводятся для значений столбца у всех строк, входящих в группу. Если имеется фраза ORDER BY, отсортировать результат запроса. Замечание о значениях NULL: Вспомним, что два значения NULL могут считаться не одинаковыми. При группировании это привело бы к тому, что группу образовывала бы каждая строка с NULL в столбце группировки. Поэтому в стандарте ANSI/ISO принято, что при группировке все NULL’ы равны и потому помещаются в одну группу.

Отбор групп строк -- фраза HAVING Фраза HAVING предназначена для организации отбора групп. Формат записываемого в ней условия такой же, как во фразе WHERE. Если условие отбора даёт значение TRUE, группа строк остаётся и в результате для неё создаётся одна строка. Если же проверка даёт FALSE или NULL, группа строк не рассматривается и результирующая строка для неё не формируется. Пример: SELECT job, AVG(sal) FROM emp GROUP BY job HAVING SUM(sal) > 3100 Фраза HAVING почти всегда используется вместе с фразой GROUP BY, однако в реализациях транслятор (но не в Oracle) может допускать применение HAVING в отсутствие GROUP BY. В этом случае образуется одна группа из всех строк таблицы. Правила работы с NULL’ами такие же как в условиях фразы WHERE.

Выполнение запросов с фразой HAVING Ограничения на условия отбора групп: Операндами в условиях отбора могут быть константы, столбцы группирования, групповые функции и выражения, построенные на этих операндах. В условии должна быть хотя бы одна групповая функция. В противном случае HAVING следует удалить перенеся условие во фразу WHERE. Порядок действий при выполнении запросов с фразой HAVING : 1. Создать декартово произведение таблиц, перечисленных во фразе FROM. 2. Применить условие фразы WHERE чтобы оставить только те строки, для которых это условие выполнено. Применить предложение GROUP BY для разделения строк на группы. 4. Отобрать группы строк в соответствии с условием фразы HAVING, оставив только группы удовлетворяющие этому условию и сформировав для каждой отобранной группы одну строку результата (см. предыдущий слайд). 5. Если указан спецификатор DISTINCT, удалить все повторяющиеся строки. 6. Если имеется фраза ORDER BY, отсортировать результат запроса. Замечание: Предполагается, что запрос одиночный, то есть теоретико- множественных операций над соединяемыми таблицами нет.

Подзапросы Подзапрос - это инструкция SELECT, вложенная в другую инструкцию SELECT для получения промежуточных результатов. Подзапросы всегда выполняются от внутренних к внешним (за исключением коррелированных подзапросов, которые изучим позже) Подзапрос может быть вложен: во фразу FROM; подзапрос готовит промежуточную таблицу, данные которой использует основной запрос; во фразы WHERE и HAVING, в условиях сравнения; подзапрос выбирает одну или несколько строк сравниваемых основным запросом (в том числе используя IN и BETWEEN ); во фразу SELECT (имеет смысл только коррелированный подзапрос). Синтаксис простого подзапроса, включённого во фразу WHERE: SELECT ....... FROM имя_табл1 WHERE имя сравнение (SELECT столбец FROM табл2 WHERE условие ) Подзапросы могут использоваться в инструкциях INSERT, UPDATE и DELETE. Напоминаем: Во фразе SELECT имеет смысл только коррелированный подзапрос. После изучения таких подзапросов (сл. 44 и далее) додумайте, почему так?

Однострочные подзапросы Однострочный подзапрос возвращает ровно одну строку. С однострочными подзапросами используются однострочные операторы сравнения: >, =, >=, <, <>, <= Пример однострочного подзапроса: SELECT ename, job, sal FROM emp WHERE mgr = (SELECT empno FROM emp WHERE ename=‘FORD’) AND sal > (SELECT sal FROM emp WHERE empno=7654) Обязательно запишите задание, по которому составлен этот не слишком “умный” запрос.

Многострочные подзапросы Многострочный подзапрос может вернуть несколько строк. Операторы сравнения для многострочных подзапросов: IN (подзапрос) - равенство любому из значений; можно понимать так: “находится в списке, полученном подзапросом”; ANY/SOME - сравнение выполняется хоть для какого-нибудь значения из списка, полученного подзапросом; ALL - сравнение верно для всех значений; EXISTS - значение существует в списке, полученном подзапросом; NOT EXISTS - значение не существует в списке, полученном подзапросом. Пример многострочного подзапроса с оператором сравнения IN: SELECT ename, sal, deptno FROM emp WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno) Обязательно составьте условие задачи, для которой написан запрос.

Примеры многострочных подзапросов Многострочный подзапрос с оператором сравнения ANY: SELECT empno, ename, job, sal FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job=‘SALESMAN') AND job<>‘ANALYST' Многострочный подзапрос с оператором сравнения ALL: SELECT empno, ename, job, sal FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE job=‘SALESMAN') AND job<> ‘CLERK' Для упорядочиваемого списка сравнение “< ALL (меньше всех значений)” эквивалентно сравнению “меньше минимального значения”. Сравнение “> ALL”(больше всех значений) эквивалентно сравнению “больше максимального значения”. Замечание: Для ANY/SOME подобных “свёрнутых” правил нет.

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

Пример коррелированного подзапроса Найдите всех работников, которые получают зарплату выше средней в своем отделе: SELECT ENAME, SAL SALARY, DEPTNO FROM EMP E WHERE SAL> (SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO) ORDER BY DEPTNO; Замечание: Наличие в подзапросе столбца из вмещающего запроса – характерный признак коррелированного подзапроса. Задание для самостоятельной работы: Найдите всех работников, которые получают зарплату равную средней зарплате в своем отделе. Получите ответ. Разберитесь с семантикой запроса. Задание: обязательно разберитесь с методом нисходящего проектирования, описанным в примечаниях к слайду.

Иерархические структуры в таблицах Уже упоминалось, что таблица может хранить дерево (лек. 7, сл.10). В синтаксис запроса Oracle для работы с иерархиями введены две фразы: начальной точки внутри иерархии (фраза START WITH); направления движения – вниз или вверх (фраза CONNECT BY PRIOR). Упрощённый синтаксис иерархического запроса: SELECT [LEVEL], список_столбцов_или_выражений FROM имя_таблицы [WHERE условия] [START WITH условия] [CONNECT BY PRIOR условия] где условие ::= выражение оператор_сравнения выражение; LEVEL – для полученного дерева псевдостолбец LEVEL возвращает значение 1 для корня, 2 для его потомков и т.д. Замечание 1: В Cache такие запросы не реализуются. Замечание 2: В Oracle возможны более сложные иерархические запросы.

Иерархическая структура в таблице emp В таблице emp хранится следующая иерархия:

Примеры запросов к иерархиям В запросе к EMP начинаем с президента King, не имеющего начальника: ... START WITH mgr IS NULL Условие START WITH может содержать подзапрос: ... START WITH empno = (SELECT empno FROM employees WHERE ename = ‘JONES') Если предложение START WITH опущено, обход дерева начинается со всех строк таблицы как с корневых. Направление обхода на примере таблицы employees:

Храним деревья и сети в таблицах В табличной базе для работы с деревьями необходимо вводить в SQL рекурсию, либо использовать процедурные расширения языка. Простейшая разметка, позволяющая хранить дерево в одной таблице, рассмотрена на примере emp. Однако, для полноценной работы с деревьями необходимо ещё реализовать такие действия, как удаление, добавление ветвей, поиск в глубину и ширину и другие. Необходимо работать с лесами деревьев. Поэтому используются другие способы моделирования деревьев, в том числе двухтабличные. Разработаны паттерны для представления деревьев и сетей в табличных базах данных. Для моделирования сетей необходимо представлять дуги и узлы, установив их инцидентности и, может быть, выделив отдельные столбцы для записи меток. Существует подход к СУБД, при котором предлагается не моделировать одни структуры данных в других, а реализовывать каждую модель данных отдельно, добиваясь максимальной эффективности. Вряд ли такие СУБД будут конкурентоспособными.

Что такое представление (VIEW) Представления создаются инструкцией похожей на инструкцию создания таблиц. Фразы ORDER BY и FOR UPDATE в ней не используются. Упрощённый формат инструкции создания представления: CREATE [OR REPLACE] [FORCE] VIEW имя_представления [(столбец [, столбец]) ..... ] AS запрос [WITH CHECK OPTION [CONSTRAINT имя_ограничения] …] “запрос” может строиться над несколькими таблицами. Представление -- хранимый объект. Поскольку данные могут храниться только в таблицах, в базе хранится имя представления, текст образующего запроса и, может быть, описания его свойств. При выполнении инструкции SELECT от представления по текстам SELECT’а и запроса, хранящегося в определении VIEW, строится результирующий запрос. Манипулирование данными через view не всегда возможно, например, если запрос в представлении не выбирает первичный ключ.

Часть диаграммы синтаксиса создания представления

Опция WITH CHECK OPTION Как уже упоминалось, применение операций INSERT, UPDATE и DELETE к представлению не всегда возможно и не всегда осмысленно. Опция WITH CHECK OPTION в определении представления запрещает те изменения данных, которые могут быть выполнены в таблицах, над которыми построено представление, но не будут обнаруживать себя в запросах через это представление. В отсутствие такой опции изменения в БД, ненаблюдаемые через представление, допустимы. При указании опции WITH CHECK OPTION пользователь не может вводить, удалять и обновлять информацию таблицы, из которой он не имеет возможности считать информацию через простое представление (создаваемое из данных одной таблицы). Представление, построенное на нескольких таблицах, нельзя создавать с этой опцией; CONSTRAINT — имя, которое присваивается ограничению определённому фразой CHECK OPTION. Если этот идентификатор опущен, то ORACLE автоматически назначает этому ограничению имя вида: SYS_Cn , где n — целое число уникальное внутри базы данных.

Пример WITH CHECK OPTION 1/2 Запись в emp через emp10 сделана, но через emp10 её не видно!!

Пример WITH CHECK OPTION 2/2 Добавим опцию WITH CHECK OPTION в определение представления. Теперь нельзя добавить данные о сотруднике в отдел 20.

Встроенный SQL Ограничения SQL можно преодолеть двумя способами: встраивая SQL в процедурный язык общего назначения; расширяя язык. С самого начала предполагалось встраивание SQL в другие языки. В любом встроенном SQL его команды помещаются в тело программы вмещающего языка, выделяясь специальными фразами, например, exec sql в языках типа С и Java. В Cache ObjectScript фразы встроенного SQL имеют формат: &sql( фраза_sql ). Одна из основных проблем встроенных языков заключается в том, что ошибки могут быть обнаружены и во вмещающем и во встроенном языке. В стандарте SQL2 для анализа ошибок встроенного SQL используются стандартные переменные: SQLCODE (код ошибки), SQLERROR (сообщение об ошибке). В новых разработках, рекомендуется заменить их переменной SQLSTATE, состоящей из двух частей – двухсимвольного класса ошибки и трехсимвольного подкласса ошибки. В Cache для анализа ошибок встроенного SQL используется только переменная SQLCODE со стандартными значениями (0 – успех, или запись найдена, 100 – больше нет записей, число<0 -- ошибка).

Встроенный SQL. Примеры 1. Для создания таблицы пишем программу &sql(create table QQ ( C1 SMALLINT PRIMARY KEY, C2 VARCHAR2(10), C3 VARCHAR2(30) ) ) write !,"Код ошибки: ", SQLCODE 2. Введем две записи &sql(insert into QQ values (1, ‘QWE’, ‘Z’)) &sql(insert into QQ values (2, ‘АБВГД’, ‘ЕЖЗ’)) 3. Выполним бесполезный запрос &sql(select * from QQ where C1=1) Данные на экране не появились, так как выдача на экран не нужна!! 4. Обмен данными с вмещающим языком &sql(select * into :VC1, :VC2, :VC3 from QQ WHERE C1 = 1) write !,"Код ошибки: ", SQLCODE write !,"-----Результат----" write !,VC1_" "_VC2_" "_VC3

Непервая нормальная форма и регулярные выражения Использование сложных структур в составе значения, которое с точки зрения реляционной модели является атомарным, то есть неделимым, позволяет разбираться с такими структурами как кодированные номера счетов в бухгалтерии и другими объектами, имеющими внутреннюю организацию, например, список. Механизмы, предназначенные для работы со значениями данных, имеющими внутреннюю организацию могут быть различными. В Caché они встроены в язык COS. Универсальный подход, применимый не только в базах данных, это использование стандартизованных регулярных выражений. Для того, чтобы не переходить в Oracle, проиллюстрируем их в JavaScript. Заметим, что в базе, использующей регулярные выражения, следует выделить два слоя – постреляционный, основанный на Н1НФ, и слой внутренних структур значений, хранящихся в базе. Это означает переход к новому классу моделей данных – двуслойных.

Где можно встретить регулярные выражения? Почти везде! Простые варианты регулярных выражений есть в: в DOS (помните шаблоны для поиска файлов типа *.doc?) в СУБД Cache (Cache ObjectScript) в старом SQL (например, в LIKE можно записать шаблон ‘_a%’) Проще всего продемонстрировать регулярные выражения в скрипте JavaScript. Скопируйте контейнер <script….> </script>, приведенный ниже, в текстовый редактор, например, WordPad. Сохраните файл с расширением .html и откройте его любым браузером.

Регулярные выражения. Основные понятия Задача, требующая замены или поиска фрагментов текста, может быть решена с помощью регулярных выражений (regular expression). Регулярные выражения это строки, которые используются для поиска и обработки текста. Языки регулярных выражений встраиваются в другие языки, например в SQL или в JavaScript. В алфавит языка входят: Символы -- любые символы, печатаемые и не печатаемые. Модификаторы — предназначены для "инструктирования" регулярного выражения. Метасимволы — специальные символы, которые служат командами языка регулярных выражений. Регулярное выражение это последовательность символов, модификаторов и метасимволов, определяющая шаблон текста. Примеры шаблонов. В обозначениях JavaScript их помещают в пару знаков //: /р/ -- состоит из одной русской буквы “р”; /р/g -- та же буква “р” и модификатор g, означающий, глобальность, то есть поиск всех вхождений “р”;

Специальные символы (метасимволы) Метасимволы задают : тип символов искомой строки; способ окружения искомой строки в тексте; количество символов отдельного типа. Примеры метасимволов: \d задаёт тип -- цифра от 0 до 9; ^ “находится в начале строки”; q+ означает “один и более”, то есть (q, qq, qqq, ..), а q{2,3} это точно два или три повтора, то есть qq или qqq. Метасимволы разделяются на три группы: Метасимволы поиска совпадений. Количественные метасимволы. Метасимволы позиционирования. Кроме того, определены классы символов (они же – скобочные выражения). Например, [[:alnum:]] обозначает класс алфавитно- цифровых символов.

Символьные классы Классы символов или скобочные выражения -- это сокращенные именования типов строк. Используются в Oracle но не в JavaScript.

Синтаксис функций REGEXP

Синтаксис функции REGEXP_SUBSTR REGEXP_SUBSTR находит соответствие указанной части обрабатываемой строки. Синтаксис: REGEXP_SUBSTR (исходная_строка, -- переменная, либо литерал в кавычках шаблон -- regexp в одинарных кавычках [, начальная_позиция -- начало поиска в строке (по умолчанию 1) [, вхождение -- какое по счету вхождение возвращается [, параметр_соответствия]]]) -- определяет, должен ли учитываться регистр Пример: выделение адресов электронной почты (в Oracle) COLUMN REGEXP_SUBSTR FORMAT A30 SELECT REGEXP_SUBSTR('Comments or questoins – email [email protected]', '[[:alnum:]](([_\.\-\+]?[[:alnum:]]+)*)@([[:alnum:]]+)(([\.-'|| ']?[[:alnum:]]+)*)\.([[:alpha:]]{2,})') "REGEXP_SUBSTR" FROM dual; Ответ: REGEXP_SUBSTR ------------------------- [email protected]

Примеры использования функций REGEXP в Oracle

II. Язык QBE (Query-by-example)

Язык QBE

Изобразительные средства QBE (1/2) Исходное изображение – прямоугольник (рис.1), в котором пользователь вводит имя таблицы. Если таблица с таким именем существует, правее появится полоса с двумя строками (на рис.2 вызвана схема таблицы с именем TYPE и схемой TYPE(ITEM, COLOR, SIZE)). В верхней строке перечень имен столбцов. Нижняя, пока пустая, предназначена для ввода операторов, переменных и операций отношения Рис.1 Рис.2

Изобразительные средства QBE (2/2) Что можно записать в нижней строке? Один из ограниченного (это хорошо) набора операторов, а именно: I. (insert) - включить; D. (delete) - удалить; U. (update) - обновить; P. (print) - печатать; Что ещё? Константы, например, запись “GREEN” в столбце “COLOR” на рис. 2 означает COLOR=“GREEN” Переменные. Обозначаются именами с подчеркиванием, например, SMITH или KING . Условия. Например запись “>1000” в столбце SAL означает условие “SAL>1000”.

Основы QBE (1/5) Часть языка, связанную с запросами, следуя основополагающей работе М.Злуфа, рассмотрим на схеме: EMP(NAME, SAL, MGR, DEPT), SALES(DEPT, ITEM), SUPPLY(ITEM, SUPPLIER), TYPE(ITEM, COLOR, SIZE) Задаём имя таблицы, может быть несуществующей, в специальном поле исходной формы: Если таблицы с таким именем нет, вы должны ввести имена столбцов, строкой ниже указать их типы (домены), а в последующих строках записать другие свойства доменов. Теперь таблица определена.

Основы QBE (2/5) Если же таблица существует, появится ее схема: В нижней строке в столбце ITEM набираем команду P., означающую “печатать столбец ITEM”, а в столбце COLOR помещаем константу GREEN чтобы задать условие выбора COLOR=“GREEN”. Получаем результат запроса: Построенный запрос QBE эквивалентен такому запросу SQL: SELECT item FROM type WHERE color=‘GREEN’ Выведем имена сотрудников, работающих в отделе игр (TOY) и получающих больше $10000. Запрос:

Основы QBE (3/5) Подчеркивание в имени определяет переменную. Использование переменных позволяет связывать таблицы. В частности, реализуем связь таблицы EMP с собой и с другой таблицей SALES в запросе: найти имена и зарплаты служащих, получающих больше, чем Lewis, и работающих в отделе, продающем ручки. Эквивалентный запрос на SQL: select E1.NAME, E1.SAL from EMP E, EMP E1, SALES where E.NAME=‘LEWIS’ AND – условие для E E1.SAL > E.SAL AND -- соединение E1 и E E1.DEPT = SALES.DEPT AND -- соединение E1 и SALES SALES.ITEM=‘PEN’ -- условие в SALES

Основы QBE (4/5) В записи условия выбора можно работать с шаблонами. Для этого вводят частичное подчеркивание в начале, середине или конце слова или предложения. В примере В столбце ITEM IKE означает, что ищутся значения, начинающиеся с I, а KE переменная, включающая остальную часть слова. Шаблон XPAY, означает слово, предложение или параграф, такие, что где-то в них содержатся последовательность букв PA.

Основы QBE (5/5) В QBE можно организовывать запросы в логике второго порядка. Как вы помните, в логике второго порядка кванторы можно навешивать не только на переменные, но ещё и на имена предикатов. А именам преди- катов в реализациях реляционных баз соответствуют имена таблиц. Пример запроса в логике предикатов 2-го порядка: Выберем все имена таблиц схемы таким запросом Ответ: список таблиц EMP, SALES, SUPPLY, TYPE. Легкость перехода к запросам в логике второго порядка можно для себя прояснить тем, что имя таблицы есть всего лишь первый элемент списка <имя_таблицы, имя_столбца+>, так что домен первой колонки как раз содержит имена таблиц и нет принципиальной разницы с последующими столбцами. Замечание 1: В реализациях QBE эта возможность может отсутствовать. Замечание 2: В SQL такие запросы можно организовать через словарь.

Выборка с использованием блока условий В Query-by-Example существует два двухмерных объекта. Один из них - шаблон таблицы – уже описан. Другой - это блок условий, имеющий всегда заголовок CONDITIONS. Пустой блок условий может быть выведен в любое время. Он позволяет задать одно или несколько условий, которые трудно выразить в шаблонах таблиц. Пример: Вывести имена сотрудников, зарплата которых больше суммы зарплат Jones и Nelson. Естественно, это простое условие могло быть выражено заменой S1 на ">(S2+S3)" в первой строке таблицы EMP.

QBE. Команды DML Вставка Удаление Обновление

QBE. Создание таблицы Создается таблица с именем EMP и столбцами NAME, SAL, MGR, DEPT. Начав с пустого шаблона, пользователь заполняет заголовки именами полей. Оператор I. справа от EMP относится ко всей строке заголовков столбцов. Теперь задаем типы данных· TYPE задает тип данных, (CHAR, FLOAT,FIXED и т.д.) LENGTH задает ширину поля. KEY указывает поля первичн. ключа (значение K это Key - ключ, NK это NonKey - не ключ.) DOMAIN – имя домена SYSNULL (System Null) задает необязательный символ, обозначающий null-значение. В примере для обозначения Null использован символ -.

Ограниченность QBE Возможно, вы заметили, что QBE в представленной версии существенно уже чем SQL. Например, отсутствуют иерархические запросы. При более глубоком изучении современных версий SQL увидим, что различия ещё значительней. Почему же языки, начинающиеся с эквивалентных исчислений (на кортежах и доменах) так расходятся в дальнейшем? Дело в отличиях идеологии построения. В QBE пользователю предоставляются схемы данных. На них как на шаблоне выстраиваются фрагменты текста запроса, который “собирается” на основе выбранных схем. Отсюда ограничения. В классической схеме Злуфа можно представить иерархию, но нельзя задать подстроки START WITH и CONNECT BY. Запросы SQL строятся как текст с фрагментами, разделёнными функционально. Как показывает опыт расширение языка в такой структуре выполняется проще.

QBE. Приложение: Содержание использованных таблиц

Заключение Изучены основы двух наиболее распространённых языков баз данных – SQL и QBE. Для практической работы необходимо более полное изучение SQL. Эта цель не могла быть достигнута в нашем общем и очень насыщенном курсе. Так что, если желаете стать профессионалом, переходите к сертифицированным курсам Oracle, DB2 (фирмы IBM) или MS SQLServer. В современных СУБД, как правило, нарушается свойство атомарности. Используется двухслойная архитектура, реализуемая за счёт использования регулярных выражений и типов XML. В табличной модели эмулируется масса других моделей данных. Мы рассмотрели только иерархическую модель. Помните, что современные профессиональные ИС могут включать в себя десятки и сотни продуктов (в том числе разных вендоров), а включение нового продукта может привести к расширению используемых моделей данных.