Язык структурированных запросов SQL

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

Для более полного погружения в работу с системой SQL я рекомендую вам ознакомиться с этими руководствми от Metanit MySQl и PostgreSQL. Они удобны в освоении и позволяют быстро приступить к работе с СУБД.

Ну что ж, приступим к изучению языка структурированных запросов SQL.

Что такое SQL?

SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).

Почему именно SQL?

SQL позволяет:

• получать доступ к данным в системах управления РБД
• описывать данные (их структуру)
• определять данные в БД и управлять ими
• взаимодействовать с другими языками через модули SQL, библиотеки и предваритальные компиляторы
• создавать и удалять БД и таблицы
• создавать представления, хранимые процедуры (stored procedures) и функции в БД
• устанавливать разрешения на доступ к таблицам, процедурам и представлениям

Процесс

При выполнении любой команды SQL в любой RDBMS (Relational Datable Management System — система управления РБД, СУБД, например для PostgreSQL,MySQL, MSSQl, SQLite и др.), система определяет лучший способ выполнения запроса,а механизм SQL определяет, как этого достичь, интерпретировать задачу.

В данном процессе участвует несколького компонентов:

• диспетчер запросов (Query Dispatcher)
• движок оптимизации (Optimization Engines)
• классический движок запросов (Classic Query Engine)
• движок запросов SQL (SQL Query Engine) и т.д.

Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.

Команды

Эталонные команды для взаимодействия с базами данных RDB: CREATE, SELECT, INSERT, UPDATE, DELETE и DROP. Эти команды можно классифицировать следующим образом:

• DDL — язык определения данных (Data Definition Language)

• DML — язык изменения данных (Data Manipulation Language)

• DCL — язык управления данными (Data Control Language)

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

Таблица

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

Таблица — это наиболее распространенная и простая форма хранения данных в RDB. Вот пример таблицы с пользователями:

Таблица

Каждую таблицу составляют небольшие части — поля (fields). Пользовательские поля в таблице пользователей: userId, userName, age, city и status. Для хранения информации о каждой записи в таблице используются поля, которые расположены на столбце таблицы.

Вместо userId и username можно было бы использовать id или name, соответственно. Однако, работая в нескольких объектах с id, трудно понять, какому объекту принадлежит идентификационный номер, особенно если вы используете деструктуризацию. А вот со словом name дела обстоят несколько иначе – оно может быть использовано в любой среде, в которой выполняется код, и я стараюсь его не использовать.  

Запись или строка

Запись / строка (record/row) — это отдельная запись, любое единичное вхождение (entry) существующая в таблице. В пользовательской таблице 5 записей. Проще говоря, запись — это горизонтальная запись в таблице.

Столбец

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

Нулевое значение

В этом случае NULL (нулевое значение) – это пустое поле. Нулевое значение –это поле, которое не имеет значения. Нулевое значение отличается от 0 и от полей в которых содержатся «пробел» и «пустоты» (`). Обнуляемое поле – это пустое пространство, которое было оставлено после того, как была создана запись. Обратите внимание на то обстоятельство,что в некоторых СУБД пустая строка('') имеет значение NULL и в некоторых она имеет другие значения.    

Ограничения

В данном случае ограничения (constraints) – это правила, которые применяются к данным. Как правило, они используются для ограничения информации о том, что может быть записано в таблицу. С помощью этого обеспечивается точность, надежность и безопасность информации в базе данных.

Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.

Среди самых распространенных ограничений можно выделить следующие:

• NOT NULL — колонка не может иметь нулевое значение
• DEFAULT — значение колонки по умолчанию
• UNIQUE — все значения колонки должны быть уникальными
• PRIMARY KEY — первичный или основной ключ, уникальный идентификатор записи в текущей таблице
• FOREIGN KEY — внешний ключ, уникальный идентификатор записи в другой таблице (таблице, связанной с текущей)
• CHECK — все значения в колонке должны удовлетворять определенному условию
• INDEX — быстрая запись и извлечение данных

Снять ограничение можно командой ALTER TABLE, а также DROP CONSTRAINT + имя ограничения. Для удаления ограничений и возможности их отключения вместо удаления, некоторые реализации предоставляют ярлыки.

Целостность данных

Содержимое каждой СУБД имеет следующие категории целостности данных :

• целостность объекта (Entity Integrity) — в таблице не должно быть дубликатов (двух и более строк с одинаковыми значениями)
• целостность домена (Domain Integrity) — фильтрация значений по типу, формату или диапазону
• целостность ссылок (Referential integrity) — строки, используемые другими записями (строки, на которые в других записях имеются ссылки), не могут быть удалены
• целостность, определенная пользователем (User-Defined Integrity) — дополнительные правила

Нормализация БД

Нормализация — это процесс эффективной организации данных в БД. Есть две основные причины необходимости нормализации: 

• предотвращение записи в БД лишних данных, например, хранения одинаковых данных в разных таблицах
• обеспечение «оправданной» связи между данными

Нормализация подразумевает соблюдение нескольких форм. Форма — это формат для структурирования базы данных. Существует три основных формы: первая, вторая и, соответственно, третья. Не буду углубляться в детали этих форм, если вам это интересно, то вы легко найдете нужную информацию.

Синтаксис SQL

В синтаксисе есть правила и рекомендации, которые необходимо соблюдать. Все операторы в SQL должны начинаться с такого ключевого слова, как SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW и т. д. И заканчиваться точкой с запятой (;) (точка с запятой не является частью синтаксиса SQL, но ее присутствие обычно требуется клиентам консоли СУБД для обозначения конца команды). SQL не чувствителен к регистру, т.е. SELECT, select и SeLeCt — идентичные инструменты. Исключением из этого правила является MySQL, где имена таблиц чувствительны к регистру.

Примеры синтаксиса

-- выборка
SELECT col1, col2, ...colN
FROM tableName;

SELECT DISTINCT col1, col2, ...colN
FROM tableName;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND|OR condition2;

SELECT col2, col2, ...colN
FROM tableName
WHERE colName IN (val1, val2, ...valN);

SELECT col1, col2, ...colN
FROM tableName
WHERE colName BETWEEN val1 AND val2;

SELECT col1, col2, ...colN
FROM tableName
WHERE colName LIKE pattern;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition
ORDER BY colName [ASC|DESC];

SELECT SUM(colName)
FROM tableName
WHERE condition
GROUP BY colName;

SELECT COUNT(colName)
FROM tableName
WHERE condition;

SELECT SUM(colName)
FROM tableName
WHERE condition
GROUP BY colName
HAVING (function condition);

-- создание таблицы
CREATE TABLE tableName (
  col1 datatype,
  col2 datatype,
  ...
  colN datatype,
  PRIMARY KEY (одна или более колонка)
);

-- удаление таблицы
DROP TABLE tableName;

-- создание индекса
CREATE UNIQUE INDEX indexName
ON tableName (col1, col2, ...colN);

-- удаление индекса
ALTER TABLE tableName
DROP INDEX indexName;

-- получение описания структуры таблицы
DESC tableName;

-- очистка таблицы
TRUNCATE TABLE tableName;

-- добавление/удаление/модификация колонок
ALTER TABLE tableName ADD|DROP|MODIFY colName [datatype];

-- переименование таблицы
ALTER TABLE tableName RENAME TO newTableName;

-- вставка значений
INSERT INTO tableName (col1, col2, ...colN)
VALUES (val1, val2, ...valN)

-- обновление записей
UPDATE tableName
SET col1 = val1, col2 = val2, ...colN = valN
[WHERE condition];

-- удаление записей
DELETE FROM tableName
WHERE condition;

-- создание БД
CREATE DATABASE [IF NOT EXISTS] dbName;

-- удаление БД
DROP DATABASE [IF EXISTS] dbName;

-- выбор БД
USE dbName;

-- завершения транзакции
COMMIT;

-- отмена изменений
ROLLBACK;

Данные

Каждый столбец, переменная и выражение в SQL имеет определенный тип данных. Основные категории типов данных:

Числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

Операторы

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

В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10, а b — 20.

Арифметические

Операторы сравнения

Логические операторы

SELECT col1, col2, ...colN
FROM tableName
WHERE [condition|expression];

SELECT col1, col2, ...colN
FROM tableName
WHERE выражение для поиска совпадения с единичным значением;

SELECT * FROM users WHERE status = active;

SELECT numericalExpression as operationName
[FROM tableName
WHERE condition];

SELECT (10 + 5) AS addition;

SELECT COUNT(*) AS records FROM users;

SELECT CURRENT_TIMESTAMP;

CREATE DATABASE dbName;
-- или
CREATE DATABASE IF NOT EXISTS dbName;

CREATE DATABASE testDB;

SHOW DATABASES;

DROP DATABASE dbName;
-- или
DROP DATABASE IF EXISTS dbName;

SHOW DATABASES;

USE dbName;

USE testDB;

CREATE TABLE tableName (
  col1 datatype,
  col2 datatype,
  ...
  colN datatype,
  PRIMARY KEY (хотя бы одна колонка)
);

CREATE TABLE users (
  userId INT,
  userName VARCHAR(20)  NOT NULL,
  age INT           NOT NULL,
  city VARCHAR(20),
  status VARCHAR(8),
  PRIMARY KEY (id)
);

 DESC users;

DROP TABLE users;

DESC users;
-- ERROR 1146 (42S02): Table 'testDB.users' doesn't exist

INSERT INTO tableName (col1, col2, ...colN)
VALUES (val1, val2, ...valN);

INSERT INTO tableName VALUES (val1, val2, ...valN);

INSERT INTO users (userId, userName, age, city, status)
VALUES (1, 'Igor', 25, 'Moscow', 'active');

INSERT INTO users (userId, userName, age, city, status)
VALUES (2, 'Vika', 26, 'Ekaterinburg', 'inactive');

INSERT INTO users (userId, userName, age, city, status)
VALUES (3, 'Elena', 27, 'Ekaterinburg', 'active');

INSERT INTO users (userId, userName, age, city, status)
VALUES
(1, 'Igor', 25, 'Moscow', 'active'),
(2, 'Vika', 26, 'Ekaterinburg', 'inactive'),
(3, 'Elena', 27, 'Ekaterinburg', 'active');

INSERT INTO users
VALUES (4, 'Oleg', 28, 'Moscow', 'inactive');

INSERT INTO tableName [(col1, col2, ...colN)]
  SELECT col1, col2, ...colN
  FROM anotherTable
  [WHERE condition];

SELECT col1, col2, ...colN
FROM tableName;

SELECT * FROM tableName;

Произведем выборку полей userId, userName и age из таблицы users:

SELECT userId, userName, age FROM users;

SELECT col1, col2, ...col2
FROM tableName
WHERE condition;

SELECT userId, userName, age
FROM users
WHERE status = 'active';

SELECT userId, age, city
FROM users
WHERE userName = 'Vika';

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND condition2 ...AND conditionN;

SELECT userId, userName, age
FROM users
WHERE status = active AND age > 26;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 OR condition2 ...OR conditionN;

SELECT userId, userName, age
FROM users
WHERE status = inactive OR age < 27;

UPDATE tableName
SET col1 = val1, col2 = val2, ...colN = valN
[WHERE condition];

UPDATE users
SET age = 30
WHERE username = 'Igor';

DELETE FROM tableName
[WHERE condition];

DELETE FROM users
WHERE status = 'inactive';

SELECT col1, col2, ...colN FROM tableName
WHERE col LIKE 'xxx%'
-- или
WHERE col LIKE '%xxx%'
-- или
WHERE col LIKE '%xxx'
-- или
WHERE col LIKE 'xxx_'
-- и т.д.

SELECT * FROM users
WHERE status LIKE 'in%';

SELECT * FROM users
WHERE age LIKE '3_';

SELECT col1, col2, ...colN FROM tableName
WHERE colName REGEXP регулярное выражение;

SELECT * FROM users
WHERE userName REGEXP 'Igor|Vika';

SELECT TOP number|percent col1, col2, ...colN
FROM tableName
[WHERE condition];

SELECT TOP 3 * FROM users;

SELECT * FROM users
LIMIT 3, [offset];

SELECT * FROM users
LIMIT 2, 2;

SELECT * FROM users
WHERE ROWNUM <= 3;

SELECT col1, col2, ...colN
FROM tableName
[WHERE condition]
[ORDER BY col1, col2, ...colN] [ASC | DESC];

SELECT * FROM users
ORDER BY city, age;

SELECT * FROM users
ORDER BY city, age DESC;

SELECT * FROM users
ORDER BY (CASE city
  WHEN 'Ekaterinburg' THEN 1
  WHEN 'Moscow' THEN 2
ELSE 100 END) ASC, city DESC;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition
GROUP BY col1, col2, ...colN
ORDER BY col1, col2, ...colN;

SELECT city, COUNT(city) AS amount FROM users
WHERE status = active
GROUP BY city
ORDER BY city;

SELECT DISTINCT col1, col2, ...colN
FROM tableName
[WHERE condition];

SELECT DISTINCT city
FROM users;

SELECT userId, userName, age, amount
FROM users, orders
WHERE users.userId = orders.userId;

SELECT col1, col2, ...colN
FROM table1
[WHERE condition]

UNION
SELECT col1, col2, ...colN
FROM table2
[WHERE condition];

 SELECT userId, userName, amount, date
  FROM users
  LEFT JOIN orders
  ON users.useId = orders.userId
UNION
  SELECT userId, userName, amount, date
  FROM users
  RIGHT JOIN orders
  ON users.userId = orders.userId;

SELECT col1, col2, ...colN
FROM table1
[WHERE condition]

UNION ALL
SELECT col1, col2, ...colN
FROM table2
[WHERE condition];

SELECT col1, col2, ...colN
FROM tableName AS aliasName
[WHERE condition];

SELECT colName AS aliasName
FROM tableName
[WHERE condition];

SELECT U.userId, U.userName, U.age, O.amount
FROM users AS U, orders AS O
WHERE U.userId = O.userId;

SELECT userId AS user_id, userName AS user_name, age AS user_age
FROM users
WHERE status = active;

CREATE INDEX indexName ON tableName;

CREATE INDEX indexName
ON tableName (colName);

CREATE UNIQUE INDEX indexName
ON tableName (colName);

Удаление индексов

Для удаления индексов используется инструкция DROP INDEX:

DROP INDEX indexName;

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

К таким ситуациям относится следующее:

• индексы не должны использоваться в маленьких таблицах
• в таблицах, которые часто и в большом объеме обновляются или перезаписываются
• в колонках, которые содержат большое количество нулевых значений
• в колонках, над которыми часто выполняются операции

Обновление таблицы

Команда ALTER TABLE используется для добавления, удаления и изменения столбцов существующей таблицы. Эта команда также используется для добавления и удаления ограничений.

Синтаксис:

-- добавление новой колонки
ALTER TABLE tableName ADD colName datatype;

-- удаление колонки
ALTER TABLE tableName DROP COLUMN colName;

-- изменение типа данных колонки
ALTER TABLE tableName MODIFY COLUMN colName newDatatype;

-- добавление ограничения `NOT NULL`
ALTER TABLE tableName MODIFY colName datatype NOT NULL;

-- добавление ограничения `UNIQUE`
ALTER TABLE tableName
ADD CONSTRAINT myUniqueConstraint UNIQUE (col1, col2, ...colN);

-- добавление ограничения `CHECK`
ALTER TABLE tableName
ADD CONSTRAINT myUniqueConstraint CHECK (condition);

-- добавление первичного ключа
ALTER TABLE tableName
ADD CONSTRAINT myPrimaryKey PRIMARY KEY (col1, col2, ...colN);

-- удаление ограничения
ALTER TABLE tableName
DROP CONSTRAINT myUniqueContsraint;

-- mysql
ALTER TABLE tableName
DROP INDEX myUniqueContsraint;

-- удаление первичного ключа
ALTER TABLE tableName
DROP CONSTRAINT myPrimaryKey;

-- mysql
ALTER TABLE tableName
DROP PRIMARY KEY;

Добавляем в таблицу users новую колонку — пол пользователя:

ALTER TABLE users ADD sex char(1);

Удаляем эту колонку:

ALTER TABLE users DROP sex;

Очистка таблицы

Команда TRUNCATE TABLE используется для удаления таблицы. Она отличается от DROP TABLE тем, что структура таблицы сохраняется (DROP TABLE полностью удаляет таблицу и все ее данные).

TRUNCATE TABLE tableName;

Очищаем таблицу users:

TRUNCATE TABLE users;

Проверяем, что users пустая:

SELECT * FROM users;
-- Empty set (0.00 sec)

Представления

Представление (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;

Результат:

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;

Результат:

Как видим, из таблицы был удален только пользователь с возрастом 26 лет.

Чтобы удалить контрольную точку, используйте команду RELEASE SAVEPOINT. Естественно, после удаления контрольной точки вернуться к ней с помощью ROLLBACK TO не удастся.

SET TRANSACTION используется для инициализации транзакций, т.е. для начала их выполнения. При таком варианте, вы можете определить некоторые характеристики транзакций. К примеру, вы можете определить уровень доступа транзакаций — только чтение или записи.

SET TRANSACTION [READ WRITE | READ ONLY];

Временные таблицы

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

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

Временная таблица создается с помощью инструкции 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'
);

Результат:

INSERT INTO tableName col1, col2, ...colN
SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE operator [value]];

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.

Заключение

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