Функции, определяемые пользователем
Некоторые СУБД позволяют использовать функции, определяемые пользователем (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
Более подробно вопросы работы с базами данных можно посмотреть в учебнике:
Базы данных. Вводный курс
Сергей Кузнецов
Содержание
Предисловие