Расширение курса бд по sql

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

1. группировки и функции агрегации

2. рекурсивные выборки и т.д.

В этой части курса рассмотрим наиболее применяемые в промышленной реализации информационных систем расширения SQL.

ВЫБОРКИ

SELECT [ALL/DISTINCT] {*/EXP1 [as alias1] [,…] }

FROM table1 [as t_alias1][,…]

[WHERE coudition]

[GROUP BY attr1[,…]]

[HAVING coudition]

[{UNION[ALL] | INTERSECT | EXCEPT} SELECT ….]

[ORDER BY attr1 [ASC|DESK] [,…] ]

[LIMIT first, count]

[] – неважно

{} – ВАЖНО

ПОДЗАПРОС

Выборки с агрегацией.

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

Как работает агрегация:

Пусть дан запрос

SELECT attr1,…,attrN, F₁(ag.attr1),…F_N(ag.attrN)

FROM t

WHERE <набор условий 1>

GROUP BY attr1,…,attrN

HAVING <набор условий 2>

В этом запросе F_i – функции агрегации, например, SUM, AVG, MIN, MAX.

Сначала, будет выполнен запрос(первые 3 строки), причем F_i

(ag_attri) -> ag_attri (будут заменены на атрибуты без агрегации). Затем в получившейся выборке будет выделены группы согласно условию секции «group by» (встанут в ряд). После этого к каждой секции будет применен агрегат F_i по соответствующим колонкам. Результирующая выборка будет содержать один кортеж на каждую из ранее выбранных групп.

Замечание: в силу такого алгоритма обработки необходимо, чтобы группировка происходила по всем атрибутам раздела SELECT, не находящимся под функциями агрегации, в противном случае возможно возникновение неоднозначности.

Пример 1:выполнение агрегирующих запросов.

Товары

ID	тип	назв	цена
1
2
3
4

Select Тип, MIN(цена), MAX(цена)

From товары

Where название != «мыло»

Group by Тип

1) Select Тип, цена, цена

From товары

Where название != «мыло»

ПРИМЕР 2:возникновение неоднозначности. Select Тип, название, MIN(цена), MAX(цена)

From товары

Where название != «мясо»

Group by Тип

Значение. Которое будет помещено в ячейку ** , нельзя определить однозначно, что туда будет помещено, будет зависеть от реализации СУБД. Логически такой запрос не имеет смысла.

Замечание: набор условий 1 и 2 находятся в разных планах исполнения. Условия раздела WHERE выполняются на первом шаге исполнения и относятся к реляционной модели. Условия раздела HAVING выполняются на втором этапе, после осуществления группировки. Это дает возможность использовать в этой секции агрегаты.

Пример.

SELECT Наименование Контрагента

FROM Поступления На РС

WHERE ДатаПоступления > = “2012-01-01”

GROUP BY Наименование Контрагента

HAVING SUM (СуммаПоступления) >= 1000000

Объединение.

Операция используется, когда в итоговую выборку должны войти результаты двух и более других выборок, не совмещаемых в одном запросе типа SELECT. При этом необходимо, чтобы схемы объединяемых выборок совпадали по мощности.

Пример: объединить в единую выборку прайс-листы двух фирм, сохранив для каждой строчки описание, из какого прайса она взята.

(Select наименование, Цена, «фирма 1» as фирма

From прайс1)

Union

(Select название, Ц, «фирма 2»

From прайс2)

Замечание: название колонок результата объединения берутся из названий колонок первого запроса в объединения.

Индексирование.

Наиболее важная задача, решаемая БД – задача поиска информации. При этом принципиальным становится эффективность поиска.

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

Пример: индексирование бинарным деревом.

Пусть таблица хранит информацию о студентах. Схема отношений: фамилия, имя, отчество, возраст, курс. Предположим, что база представляет собой набор файлов и тело каждой отдельной таблицы хранится в отдельном файле. Если фамилия, имя и отчество имеют тип varchar (200), то эти поля внутри файла могут занимать от 2 до 202 байтов. Записи внутри файла хранятся в такой последовательности, в какой осуществляется их ввод в базу.

Выполняется запрос:

SELECT ФИО FROM Студенты

WHERE Возраст = 19

СУБД должна просматривать файл, находя начало первой записи, перемещаясь по полям до поля возраст, затем по второй записи и т.д. … сразу на поле Возраст кажд. записи в таком случае невозможно, т.к. ФИО имеют не определенную заранее длину.

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

Кроме значения узел содержит информацию о расположенных внутри файла записей с соответствующим значением поля.

При выполнении поиска вместо просмотра всей совокупности данных будем просматривать индекс и по найденному значению переходить сразу к требуемым записям.

Оценим эффективность такой структуры.

Быстрее и проще, чем простой перебор.

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