Представления
Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.
Представления могут содержать все или только некоторые строки таблицы. Представление может быть создано на основе одной или нескольких таблиц (это зависит от запроса для создания представления).
Представления — это виртутальные таблицы, позволяющие делать следующее:
- структурировать данные способом, который пользователи находят наиболее естественным или интуитивно понятным
- ограничивать доступ к данным таким образом, что пользователь может просматривать и (иногда) модифицировать только то, что ему нужно и ничего более
- объединять данные из нескольких таблиц для формирования отчетов
Создание представления
Для создания представления используется инструкция CREATE VIEW. Как было отмечено, представления могут создаваться на основе одной или нескольких таблиц, и даже на основе другого представления.
CREATE VIEW viewName AS
SELECT col1, col2, ...colN
FROM tableName
[WHERE condition];
Создаем представление для имен и возраста пользователей:
CREATE VIEW usersView AS
SELECT userName, age
FROM users;
Получаем данные с помощью представления:
SELECT * FROM usersView;
Результат:
userName | age |
---|
Igor | 30 |
Vika | 26 |
Elena | 27 |
Oleg | 28 |
WITH CHECK OPTION
WITH CHECK OPTION — это настройка инструкции CREATE VIEW. Она позволяет обеспечить соответствие всех UPDATE и INSERT условию, определенном в представлении.
Если условие не удовлетворяется, выбрасывается исключение.
CREATE VIEW usersView AS
SELECT userName, age
FROM users
WHERE age IS NOT NULL
WITH CHECK OPTION;
Обновление представления
Представление может быть обновлено при соблюдении следующих условий:
- SELECT не содержит ключевого слова DISTINCT
- SELECT не содержит агрегирующих функций
- SELECT не содержит функций установки значений
- SELECT не содержит операций установки значений
- SELECT не содержит предложения ORDER BY
- FROM не содержит больше одной таблицы
- WHERE не содержит подзапросы
- запрос не содержит GROUP BY или HAVING
- вычисляемые колонки не обновляются
- все ненулевые колонки из базовой таблицы включены в представление в том же порядке, в каком они указаны в запросе INSERT
Пример обновления возраста пользователя с именем Igor в представлении:
UPDATE usersView
SET age = 31
WHERE userName = 'Igor';
Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.
В представление могут добавляться новые строки с помощью команды INSERT. При выполнении этой команды должны соблюдаться те же правила, что и при выполнении команды UPDATE.
С помощью команды DELETE можно удалять строки из представления.
Удаляем из представления пользователя, возраст которого составляет 26 лет:
DELETE FROM usersView
WHERE age = 26;
Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.
Удаление представления
Для удаления представления используется инструкция DROP VIEW:
DROP VIEW viewName;
Удаляем представление usersView:
DROP VIEW usersView;
HAVING
Предложение HAVING используется для фильтрации результатов группировки. WHERE используется для применения условий к колонкам, а HAVING — к группам, созданным с помощью GROUP BY.
HAVING должно указываться после GROUP BY, но перед ORDER BY (при наличии).
SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE condition]
GROUP BY col1, col2, ...colN
HAVING condition
ORDER BY col1, col2, ...colN;
Транзакции
Транзакция — это единица работы или операции, выполняемой над БД. Это последовательность операций, выполняемых в логическом порядке. Эти операции могут запускаться как пользователем, так и какой-либо программой, функционирующей в БД.
Транзакция — это применение одного или более изменения к БД. Например, при создании/обновлении/удалении записи мы выполняем транзакцию. Важно контролировать выполнение таких операций в целях обеспечения согласованности данных и обработки возможных ошибок.
На практике, запросы, как правило, не отправляются в БД по одному, они группируются и выполняются как часть транзакции.
Свойства транзакции
Транзакции имеют 4 стандартных свойства (ACID):
- атомарность (atomicity) — все операции транзакции должны быть успешно завершены. В противном случае, транзакция прерывается, а все изменения отменяются (происходит откат к предыдущему состоянию)
- согласованность (consistency) — состояние должно изменться в полном соответствии с операциями транзакции
- изоляция или автономность (isolation) — транзакции не зависят друг от друга и не оказывают друг на друга никакого влияния
- долговечность (durability) — результат звершенной транзакции должен сохраняться при поломке системы
Управление транзакцией
Для управления транзакцией используются следующие команды:
- BEGIN|START TRANSACTION — запуск транзакции
- COMMIT — сохранение изменений
- ROLLBACK — отмена изменений
- SAVEPOINT — контрольная точка для отмены изменений
- SET TRANSACTION — установка характеристик текущей транзакции
Команды для управления транзакцией могут использоваться только совместно с такими запросами как INSERT, UPDATE и DELETE. Они не могут использоваться во время создания и удаления таблиц, поскольку эти операции автоматически отправляются в БД.
Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:
BEGIN TRANSACTION
DELETE FROM users
WHERE age = 26;
COMMIT;
Удаляем пользователя с именем Oleg и отменяем эту операцию:
BEGIN
DELETE FROM users
WHERE username = 'Oleg';
ROLLBACK;
Контрольные точки создаются с помощью такого синтаксиса:
SAVEPOINT savepointName;
Возврат к контрольной точке выполняется так:
ROLLBACK TO savepointName;
Выполняем три запроса на удаление данных из users, создавая контрольные точки перед каждый удалением:
START TRANSACTION
SAVEPOINT sp1;
DELETE FROM users
WHERE age = 26;
SAVEPOINT sp2;
DELETE FROM users
WHERE userName = 'Oleg';
SAVEPOINT sp3;
DELETE FROM users
WHERE status = 'inactive';
Отменяем два последних удаления, возвращаясь к контрльной точке sp2, созданной после первого удаления:
ROLLBACK TO sp2;
Делаем выборку пользователей:
SELECT * FROM users;
Результат:
userId | userName | age | city | status |
---|
1 | Igor | 31 | Moscow | active |
3 | Elena | 27 | Ekaterinburg | active |
4 | Oleg | 28 | Moscow | inactive |
Как видим, из таблицы был удален только пользователь с возрастом 26 лет.
Для удаление контрольной точки используется команда RELEASE SAVEPOINT. Естественно, после удаления контрольной точки, к ней нельзя будет вернуться с помощью ROLLBACK TO.
Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):
SET TRANSACTION [READ WRITE | READ ONLY];
Временные таблицы
Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.
Временные таблицы могут быть очень полезными при необходимости хранения временных данных. Одной из главных особенностей таких таблиц является то, что они удаляются по завершении текущей сессии. При запуске скрипта временная таблица удаляется после завершения выполнения этого скрипта. При доступе к БД с помощью клиентской программы, такая таблица будет удалена после закрытия этой программы.
Временная таблица создается с помощью инструкции CREATE TEMPORARY TABLE, в остальном синтаксис создания таких таблиц идентичен синтаксису создания обычных таблиц.
Временная таблица удаляется точно также, как и обычная таблица, с помощью инструкции DROP TABLE.
Клонирование таблицы
Может возникнуть ситуация, когда потребуется получить точную копию существующей таблицы, а CREATE TABLE или SELECT окажется недостаточно в силу того, что мы хотим получить не только идентичную структуру, но также индексы, значения по умолчанию и т.д. копируемой таблицы.
В mysql, например, это можно сделать так:
- вызываем команду SHOW CREATE TABLE для получения инструкции, выполненной при создании таблицы, включая индексы и прочее
- меняем название таблицы и выполняем запрос. Получаем точную копию таблицы
- опционально: если требуется содержимое копируемой таблицы, можно также использовать инструкции INSERT INTO или SELECT
Подзапросы
Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.
Подзапрос используется для получения данных, которые будут использованы основным запросом в качестве условия для фильтрации возвращаемых записей.
Подзапросы могут использоваться в инструкциях SELECT, INSERT, UPDATE и DELETE, а также с операторами =, <, >, >=, <=, IN, BETWEEN и т.д.
Правила использования подзапросов:
- они должны быть обернуты в круглые скобки
- подзапрос должен содержать только одну колонку для выборки, если основной запрос не содержит несколько таких колонок, которые сравниваются в подзапросе
- в подзапросе нельзя использовать команду ORDER BY, это можно сделать в основном запросе. В подзапросе для замены ORDER BY можно использовать GROUP BY
- подзапросы, возвращающие несколько значений, могут использоваться только с операторами, которые работают с наборами значений, такими как IN
- список SELECT не может содержать ссылки на значения, которые оцениваются (вычисляются) как BLOB, ARRAY, CLOB или NCLOB
- подзапрос не может быть сразу передан в функцию для установки значений
- команду BETWEEN нельзя использовать совместно с подзапросом. Тем не менее, в самомподзапросе указанную команду использовать можно
Подзапросы, обычно, используются в инструкции SELECT.
SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
WHERE colName operator
(SELECT col1, col2, ...colN
FROM table1, table2, tableN
[WHERE condition]);
Пример:
SELECT * FROM users
WHERE userId IN (
SELECT userId FROM users
WHERE status = 'active'
);
Результат:
userId | userName | age | city | status |
---|
1 | Igor | 30 | Moscow | active |
3 | Elena | 27 | Ekaterinburg | active |
Подзапросы могут использоваться в инструкции INSERT. Эта инструкция добавляет в таблицу данные, возвращаемые подзапросом. При этом, данные, возвращаемые подзапросом, могут быть модифицированы любыми способами.
INSERT INTO tableName col1, col2, ...colN
SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE operator [value]];
Подзапросы могут использоваться в инструкции UPDATE. При этом, данные из подзапроса могут использоваться для обновления любого количества колонок.
UPDATE tableName
SET col = newVal
[WHERE operator [value]
(
SELECT colName
FROM tableName
[WHERE condition]
)
];
Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.
DELETE FROM tableName
[WHERE operator [value]
(
SELECT colName
FROM tableName
[WHERE condition]
)
];
Последовательности
Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.
Приведенные ниже примеры рассчитаны на mysql.
Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:
CREATE TABLE tableName (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
-- другие строки
);
Для того, чтобы заново пронумеровать строки с помощью автоматически генерируемых значений (например, при удалении большого количества строк), можно удалить колонку, содержащую такие значения и создать ее заново.
Обратите внимание: такая таблица не должна быть частью объединения.
ALTER TABLE tableName DROP id;
ALTER TABLE tableName
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);
По умолчанию значения, генерируемые с помощью AUTO_INCREMENT, начинаются с 1. Для того, чтобы установить другое начальное значение достаточно указать, например, AUTO_INCREMENT = 100 — в этом случае нумерация строк начнется со 100.