Функции, определяемые пользователем

Некоторые СУБД позволяют использовать функции, определяемые пользователем (UDF – User Defined Functions). Эти функции обычно хранятся во внешних библиотеках и должны быть зарегистрированы в базе данных, после чего их можно использовать в запросах, триггерах и хранимых процедурах.

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

Транзакции

Транзакция (Transaction) – это группа операций над данными в базе, которые либо выполняются все вместе, либо все вместе отменяются.

Транзакция может состоять из нескольких вложенных транзакций.

Если все операции, входящие в состав транзакции, успешно выполнены. То результат их работы сохраняется в базе данных. Говорят, что транзакция успешно завершена (Commit).

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

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

 

Введение в SQL

Стандарты

 

SQL (ˈɛsˈkjuˈɛl; англ. Structured Query Language — «язык структурированных запросов») — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL основывается на исчислении кортежей [http://ru.wikipedia.org/wiki/SQL].

Стандарты:

Год	Название	Иное название	Изменения
	SQL-86	SQL-87	Первый вариант стандарта, принятый институтом ANSI и одобренный ISO в 1987 году.
	SQL-89	FIPS 127-1	Немного доработанный вариант предыдущего стандарта.
	SQL-92	SQL2, FIPS 127-2	Значительные изменения (ISO 9075); уровень Entry Level стандарта SQL-92 был принят как стандарт FIPS 127-2.
	SQL:1999	SQL3	Добавлена поддержка регулярных выражений, рекурсивных запросов, поддержка триггеров, базовые процедурные расширения, нескалярные типы данных и некоторые объектно-ориентированные возможности.
	SQL:2003		Введены расширения для работы с XML-данными, оконные функции (применяемые для работы с OLAP-базами данных), генераторы последовательностей и основанные на них типы данных.
	SQL:2006		Функциональность работы с XML-данными значительно расширена. Появилась возможность совместно использовать в запросах SQL и XQuery.
	SQL:2008		Улучшены возможности оконных функций, устранены некоторые неоднозначности стандарта SQL:2003[5]

Возможности SQL

Каждое предложение SQL — это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в базе. В соответствии с тем, какие изменения происходят в базе данных, различают следующие типы запросов:

• запросы на создание или изменение в базе данных новых или существующих объектов (при этом в запросе описывается тип и структура создаваемого или изменяемого объекта);

· запросы на получение данных;

· запросы на добавление новых данных (записей)

· запросы на удаление данных;

· обращения к СУБД.

Основным объектом хранения реляционной базы данных является таблица, поэтому все SQL-запросы — это операции над таблицами. В соответствии с этим, запросы делятся на

· запросы, оперирующие самими таблицами (создание и изменение таблиц);

· запросы, оперирующие с отдельными записями (или строками таблиц) или наборами записей.

Каждая таблица описывается в виде перечисления своих полей (столбцов таблицы) с указанием

· типа хранимых в каждом поле значений;

· связей между таблицами (задание первичных и вторичных ключей);

· информации, необходимой для построения индексов.

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

· вставка новой строки;

· изменение значений полей строки или набора строк;

· удаление строки или набора строк.

Самый главный вид запроса — это запрос, возвращающий (пользователю) некоторый набор строк, с которым можно осуществить одну из трёх операций:

· просмотреть полученный набор;

· изменить все записи набора;

· удалить все записи набора.

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

Запросы на выборку данных

Для формирования запросов на выборку данных из таблиц используется команда SELECT.

Рассмотрим основные опции данной команды.

SELECT [DISTINCT|ALL] <Список вывода>

[FROM <Список таблиц>]

[WHERE <Условие выборки>]

[GROUP BY <Условие группировки>

[HAVING <Условие выбора группы>]]

[ORDER BY <Условие сортировки>];

 

DISTINCT – при выводе исключить повторяющиеся строки;

ALL – вывести всё, режим по умолчанию;

WHERE – выбор строк таблиц, значения полей (граф) в которых удовлетворяют условию выборки;

GROUP BY – объединение строк, имеющих одинаковое значение условия группировки, в группу, как правило, с целью подведения итогов по графам таблицы; не является сортировкой, т.к. группы формируются в произвольном порядке;

HAVING – аналогично WHERE, но внутри группы строк таблицы;

ORDER BY – сортировка по указанному полю (полям) в порядке возрастания (ASC) или порядке убывания (DESC).

Примеры запросов

Рассмотрим учебную БД, состоящую из трёх таблиц:

КЛИЕНТ (№клиента, Ф, И, О, СуммаКредита, Город);

ТОВАР (КодТовара, НазваниеТовара, Цена);

ПОКУПКА (№клиента, КодТовара, Количество).

Пример 1. Вывести список клиентов, проживающих в г. Ижевске, в порядке убывания их кредита.

Вариант 1:

SELECT №клиента, Ф, И, О, СуммаКредита

FROM КЛИЕНТ

WHERE Город = ‘Ижевск’

ORDER BY СуммаКредита DESC;

 

Вариант 2 (переименование граф результирующей таблицы):

SELECT №клиента AS Номер клиента, Ф AS Фамилия, И AS И., О AS О., СуммаКредита AS Сумма кредита

FROM КЛИЕНТ

WHERE Город = ‘Ижевск’

ORDER BY СуммаКредита DESC;

 

Пример 2. Вывести список товаров, стоимость которых находится в диапазоне от 500 до 1000.

Вариант 1:

SELECT КодТовара, НазваниеТовара, Цена

FROM ТОВАР

WHERE Цена BETWEEN 500 AND 1000;

 

Вариант 2:

SELECT *

FROM ТОВАР

WHERE Цена BETWEEN 500 AND 1000;

 

Вариант 3:

SELECT *

FROM ТОВАР

WHERE Цена >= 500 AND Цена <=1000;

 

Пример 3. Выбрать все товары, наименование которых начинается с буквы «Т».

SELECT *

FROM ТОВАР

WHERE НазваниеТовара LIKE ‘Т%’;

 

В примере использован один из символов шаблона:

% - заменяет любую последовательность символов;

_ - одиночный символ.

 

Пример 4. Выбрать номера клиентов, которым продан товар с названием «Стул 5АС».

SELECT №клиента

FROM ТОВАР, ПОКУПКА

WHERE НазваниеТовара = ‘Стул 5АС’ AND КЛИЕНТ.№клиента = ПОКУПКА.№клиента;

 

Пример 5. Определить номера клиентов, сумма кредита которых больше или равна средней сумме кредита для города, в котором проживает клиент.

 

SELECT №клиента, СуммаКредита, Город

FROM КЛИЕНТ X, КЛИЕНТ Y

WHERE СуммаКредита >= (SELECT AVG(СуммаКредита)

FROM Y

WHERE X.Город = Y.Город);

 

В примере использована одна из стандартных агрегатных функций:

AVG – подсчёт среднего значения по графе всей таблицы или по группе строк таблицы;

COUNT – подсчёт количества строк таблицы в целом или в выделенной группе строк;

SUM – подсчёт суммы по графе (таблицы или группы);

MAX – поиск максимального значения в графе (таблицы или группы);

MIN – поиск минимального значения (таблицы или группы).

 

Данная задача может быть решена двумя запросами.

 

Пример 6. Определить общий объём покупок по каждому товару в количественном выражении.

 

SELECT КодТовара, SUM(Количество)

FROM ПОКУПКА

GROUP BY КодТовара;

Примеры команды SELECT

 

Более подробно вопросы работы с базами данных можно посмотреть в учебнике:

Базы данных. Вводный курс

Сергей Кузнецов

Содержание

Предисловие