Эффективная подготовка к государственным междисциплинарным экзаменам для студентов технических специальностей (стр. 10 )

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

Например:

SELECT t1.f1, t1.f2, t2.f1 from tbl1 t1 join tbl2 t2 using f2;

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

Например:

select t1.f1, t1.f2, t2.f1, t2.f2 from tbl1 t1 join tbl2 t2 on t1.f1= t2.f2;

6. Запросы с группировкой и вложенные запросы в SQL.

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

К функциям агрегирования относятся следующие функции языка SQL:

COUNT - подсчет количества всех значений столбцов, за исключением значения NULL и с учетом указания фраз ALL или DISTINCT.

COUNT (*) - подсчет количества всех значений столбцов в группе.

AVG - определение среднего значения.

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

MAX - определение максимального значения из группы.

MIN - определение минимального значения из группы.

Фраза HAVING оператора SELECT определяет предикат аналогично фразе WHERE, но применяемый к строкам, полученным в результате выполнения функций агрегирования.

Пример: SELECT SUM(PLATA) FROM A

WHERE FIRMA = ‘МАХАОН’ AND PLATA>100

SELECT COUNT(*) FROM A

WHERE FIRMA = ‘МАХАОН’ AND PLATA>100

Записи делятся на группы по одному и тому же из указанных полей-групировкой.

Пр.

SELECT FACULT, GROUP, AVG(MARK) FROM A GROUP BY FACULT, GROUP

SELECT FAC, GR, AVG(MARK) FROM A GROUP BY FAC, GR, HAVING MARK<5

для таблицы

Дает:

ФИВТ ПС-11 3.00

ЭК М-11 3.00

Если вместо HAVING – WHERE то

ФИВТ ПС-11 3.00

ЭК М-11 3.00

ЭК К-11 3.00

Вложенный подзапрос – это подзапрос, заключенный в круглые скобки и вложенный в Where (Having) фразу предложения Select, Delete или UpDate. Существуют простые и коррелированные (связанные) подзапросы. Простые запросы обрабатываются системой «снизу вверх». Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т. д.

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

Существует немало задач (особенно на удаление и изменение данных), которые могут быть реализованы только посредством вложенных подзапросов.

Пр. SELECT NAME, ROST FROM A WHERE ROST>(SELECT AVG(ROST) FROM B).

Виды вложенных подзапросов (способы соединения):

1.По условию сравнения (>,<,=)

2.Проверка на принадлежности результатам вложенного запроса ([NOT] IN)

3.Проверка на существование вложенного запроса ([NOT] EXIST).

4. Многократное сравнение (ALL, ANY).

Пр. SELECT NAME, ROST FROM A WHERE NAME IN (SELECT NAME FROM B WHERE CITY = ‘Й-ОЛА’).

И Т. П.

7. Целостность данных. Транзакции.

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

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

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

Под транзакцией понимается неделимая с точки зрения воздействия на БД последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации) такая, что либо результаты всех операторов, входящих в транзакцию, отображаются в БД, либо воздействие всех этих операторов полностью отсутствует. Лозунг транзакции - "Все или ничего": при завершении транзакции оператором COMMIT результаты гарантированно фиксируются во внешней памяти (смысл слова commit - "зафиксировать" результаты транзакции); при завершении транзакции оператором ROLLBACK результаты гарантированно отсутствуют во внешней памяти (смысл слова rollback - ликвидировать результаты транзакции).

Транзакция обладает четырьмя важными свойствами, известными как свойства АСИД:

(А) Атомарность. Транзакция выполняется как атомарная операция - либо выполняется вся транзакция целиком, либо она целиком не выполняется.

(С) Согласованность. Транзакция переводит базу данных из одного согласованного (целостного) состояния в другое согласованное (целостное) состояние. Внутри транзакции согласованность базы данных может нарушаться.

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

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

Различаются два вида ограничений целостности: немедленно проверяемые и откладываемые.

К немедленно проверяемым ограничениям целостности относятся такие ограничения, проверку которых бессмысленно или даже невозможно откладывать. Примером ограничения, проверку которого откладывать бессмысленно, являются ограничения домена (возраст сотрудника не может превышать 150 лет). Более сложным ограничением, проверку которого невозможно отложить, является следующее: зарплата сотрудника не может быть увеличена за одну операцию более чем на 100,000 рублей. Немедленно проверяемые ограничения целостности соответствуют уровню отдельных операторов языкового уровня СУБД. При их нарушениях не производится откат транзакции, а лишь отвергается соответствующий оператор.

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

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

Первый уровень - отсутствие потерянных изменений. Рассмотрим следующий сценарий совместного выполнения двух транзакций. Транзакция 1 изменяет объект базы данных A. До завершения транзакции 1 транзакция 2 также изменяет объект A. Транзакция 2 завершается оператором ROLLBACK (например, по причине нарушения ограничений целостности). Тогда при повторном чтении объекта A транзакция 1 не видит изменений этого объекта, произведенных ранее. Такая ситуация называется ситуацией потерянных изменений. Естественно, она противоречит требованию изолированности пользователей. Чтобы избежать такой ситуации в транзакции 1 требуется, чтобы до завершения транзакции 1 никакая другая транзакция не могла изменять объект A. Отсутствие потерянных изменений является минимальным требованием к СУБД по части синхронизации параллельно выполняемых транзакций.

Второй уровень - отсутствие чтения "грязных данных". Рассмотрим следующий сценарий совместного выполнения транзакций 1 и 2. Транзакция 1 изменяет объект базы данных A. Параллельно с этим транзакция 2 читает объект A. Поскольку операция изменения еще не завершена, транзакция 2 видит несогласованные "грязные" данные (в частности, операция транзакции 1 может быть отвернута при проверке немедленно проверяемого ограничения целостности). Это тоже не соответствует требованию изолированности пользователей (каждый пользователь начинает свою транзакцию при согласованном состоянии базы данных и в праве ожидать видеть согласованные данные). Чтобы избежать ситуации чтения "грязных" данных, до завершения транзакции 1, изменившей объект A, никакая другая транзакция не должна читать объект A (минимальным требованием является блокировка чтения объекта A до завершения операции его изменения в транзакции 1).

Третий уровень - отсутствие неповторяющихся чтений. Рассмотрим следующий сценарий. Транзакция 1 читает объект базы данных A. До завершения транзакции 1 транзакция 2 изменяет объект A и успешно завершается оператором COMMIT. Транзакция 1 повторно читает объект A и видит его измененное состояние. Чтобы избежать неповторяющихся чтений, до завершения транзакции 1 никакая другая транзакция не должна изменять объект A. В большинстве систем это является максимальным требованием к синхронизации транзакций, хотя, как мы увидим немного позже, отсутствие неповторяющихся чтений еще не гарантирует реальной изолированности пользователей.