В теории реляционных баз данных логическая группировка элементов данных называется «отношением», а определенный экземпляр отношения - «кортежем». Так вот первичный ключ — это множество атрибутов отношения, которое уникально определяет кортеж этого отношения.

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

Например, рассмотрим таблицу заказов, состоящую из столбцов:

• order_id (уникальный номер заказа — первичный ключ)
• order_date (дата совершения заказа)
• order_customer (покупатель, сделавший покупку)
• order_sum (сумма заказа).

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

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

Например, в таблице заказов, очевидно, должно быть поле order_customer, которое определяет покупателя сделавшего покупку. Мы могли бы в это поле записывать просто ФИО покупателя. Но как быть, если нам нужно выбрать все заказы, сделанные конкретным покупателем? Велика вероятность, что в таблице могут находится люди, у которых полностью совпадает ФИО.

Во избежание подобных проблем (примеров можно придумать массу) в поле order_customer необходимо хранить внешний ключ, который будет ссылаться на первичный ключ в таблице customers (покупатели).

Например, в таблице заказов будет строка, где в поле order_customer содержится внешний ключ — 3246. Далее в таблице customers необходимо отыскать строку с первичным ключом, равным 3246, в этой строке и будут содержаться все данные покупателя, включая и ФИО.

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

Нормализация базы данных сводит к минимуму количество избыточной информации. Ее целью является сохранять данные только один раз, но в нужном месте.

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

Первоначально доктором Эдгаром Коддом были определены только 3 нормальные формы. Дальнейшая разработка реляционной теории привела к появлению еще нескольких форм и на данный момент их насчитывается 6.

На практике соответствие базы данных правилам 3-ей нормальной формы вполне достаточно.

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

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

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

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

Индексы делятся на 2 вида — кластерный и некластерный.

Обычно СУБД хранит строки в том порядке, в котором они добавляются в таблицу.

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

Некластерный индекс хранит указатели на строки в таблице. Такой способ хранения подразумевает после обращения к индексу обращение к самой таблице для получения строк.

• B-деревья
• Хеши
• R-деревья
• Полнотекстовые

В отличие от некластерного индекса, таблица может иметь только один кластерный индекс.

• INNER JOIN
• FULL OUTER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• CROSS JOIN

Опишем каждый тип соединений на примере запросов. Рассмотрим 2 таблицы A и B:

Посмотрим на результаты следующих запросов:

CROSS JOIN является полным декартовым произведением, т.е. возвращает все возможные сочетания записей таблиц A и B. В стандартном SQL CROSS JOIN выполняется без условия ON, однако, например, в MySQL этого ограничения нет и CROSS JOIN является аналогом INNER JOIN.

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

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

По определению, транзакция — это логическая единица работы.

Транзакция предполагает, что все, входящие в нее SQL-запросы будут обязательно выполнены, причем одновременно.

Начать транзакцию можно при помощи команды BEGIN TRANSACTION. Завершить можно либо сохранив изменения в базе данных при помощи команды COMMIT, либо удалив все сделанные транзакцией изменения командой ROLLBACK

1
2
3
4
5
6

BEGIN TRANSACTION
UPDATE user_account SET balance=balance-100 WHERE account_id=1 /* Снимаем деньги со счета,
                                                                  ID которого равен 1 */
UPDATE user_account SET balance=balance+100 WHERE account_id=2 /* И переводим эти деньги на 
                                                                  счет, ID которого равен 2 */
COMMIT

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

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

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

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

Взаимная блокировка (или deadlock) — это ситуация, при которой возник конфликт между несколькими операциями записи.

В описанном примере обе операции могут снять блокировку с данных только после полного завершения своей работы. Но для завершения работы операции 1 нужно снятие блокировки с данных B, а для операции 2 снятие блокировки с данных A. Обе операции вступают в бесконечное ожидание снятия блокировки с данных.

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

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

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

Триггеры — это части кода, запускаемые автоматически и основанные на каком-либо действии или событии в таблице базы данных.

Триггеры могут применять в таблице во время выполнения операторов INSERT, UPDATE или DELETE, а затем работать или перед, или после этих действий.

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

Конструкцию HAVING можно использовать только при наличии конструкции GROUP BY. Она действует по отношению к результатам конструкции GROUP BY аналогично тому, как конструкция WHERE отсеивает строки, возвращаемые оператором SELECT, в соответствии с указанным условием.

Подзапрос — это запрос, вложенный в другой запрос.

1
2
3
4

SELECT * FROM tableA WHERE id IN (SELECT id FROM tableB)
SELECT * FROM tableA WHERE id > (SELECT AVG(id) FROM tableA)
SELECT tA.* FROM (SELECT * FROM tableA WHERE col IS NOT NULL) tA, tableB tB 
WHERE tA.id=tB.id