.RU

Лекция №3: Формирование запросов средствами языка SQL - Лекция №1: Стандарты языка sql


^ Лекция №3: Формирование запросов средствами языка SQL Формирование запросов средствами языка SQL Оператор SELECT
Оператор SELECT позволяет формировать запрос к базе данных. В результате выполнения этого оператора СУБД формирует результирующий набор (иногда также называемый набором данных). Если этот оператор был введен в интерактивном режиме взаимодействия с базой данных, то результат отображается в виде таблицы в текущем диалоговом окне. На рисунке 3.1. приведен пример выполнения оператора SELECT, извлекающего данные всех столбцов из таблицы dept.



Рис. 3.1. Выполнение оператора SELECT

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

Оператор SELECT имеет в стандарте SQL92 следующее формальное описание:

^ SELECT [DISTINCT]
     { function_agregate .,:
     | specification.*
     | *
     [INTO list_variable]
     FROM {{ имя_таблицы [AS] [table_alias] [(field .,:)]}
               | {subquery [AS] subquery_alas [(field .,:)]}
               | union_table
               | constructor_of_table_value
               | {TABLE имя_таблицы [AS] alias [(field .,:)]}
          } .,:
     [^ WHERE condition]
     [GROUP BY { имя_таблицы .field} .,: {COLLATE name]]
      [HAVING condition]
     [ UNION [ALL]
       [CORRESPONDING [BY (field.,:)]]
       SELECT_operator | {TABLE имя_таблицы} | constructor_of_table_value      [ORDER BY] {DESC].,:}
             |{DESC].,:} ;

Для выполнения запроса требуется привилегия SELECT на все таблицы, участвующие в запросе.

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

SELECT * FROM tbl1;

Имя поля может быть квалифицировано именем таблицы, указываемым через точку. Например:

SELECT tbl1.f1, tbl2.f1 FROM tbl1, tbl2;

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

Фраза INTO используется только во встроенном SQL, указывая переменные, в которые записывается результат запроса. При этом формируемый результирующий набор может содержать только одну строку.

Фраза WHERE определяет условие, которому должны удовлетворять все строки, используемые для формирования результирующего набора.

Во всех операциях сравнения языка SQL применяется трехзначная логика (3VL). Предикат, указываемый фразой WHERE, может принимать одно из следующих значений: TRUE, FALSE или UNKNOWN. Значение UNKNOWN получается при сравнении значения NULL с любым другим значением, включая значение NULL.

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

Кроме стандартных операторов сравнения, таких как =, , >, =, <= могут быть использованы следующие операторы:

x BETWEEN y AND z

эквивалентно выражению

(x=y);

x IN (a,b,c);

x LIKE 'abc';

x IS NULL;

SELECT * FROM tbl1 t_out
     WHERE EXISTS (SELECT * FROM tbl1 t_in
          WHERE t_in.f1= t_out.f1);



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

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



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

Приведем пример выбора с применением групп. Столбец dno имеет всего три различных значения: 11, 22 и 33. Для каждой из трех групп находится минимальное и максимальное значение столбца f2:

     SELECT dno, MIN(f2), MAX(f2)
        FROM tbl1
        GROUP BY dno;

Результатом выполнения этого SQL-оператора будет формирование следующих строк:

DNO          

 

MIN(f2)          

 

MAX(f2)          







11

125

200

22

200

2300

33

100

150

При выборе с применением групп и с дополнительным ограничением на значение в столбце MIN(f2):

     SELECT dno, MIN(f2), MAX(f2)
        FROM tbl1
        GROUP BY dno
        HAVING MAX(f2) < 1000;

В результате выполнения этого SQL-оператора будут возвращены только две строки: первая и последняя:

DNO          

 

MIN(f2)          

 

MAX(f2)          







11

125

200

33

100

150
^ Упорядочивание результирующего набора
Фраза ORDER BY применяется для упорядочивания результирующего набора, которое выполняется в соответствии со значениями столбцов, указанных в списке после фразы ORDER BY. Сначала производится упорядочивание по первому указанному столбцу, потом по второму и т.д. При упорядочивании можно указать опцию ASC (по возрастанию) или DESC (по убыванию).

Например:

SELECT f1,f2 FROM tbl1 ORDER BY f2;
^ Создание таблиц по образцу
Стандарт SQL-99 вводит поддержку создания таблиц по образцу, позволяя копировать структуру таблицы, создавая новую таблицу, имеющую количество столбцов и их типы, полностью идентичные исходной таблице. Создаваемая по образцу таблица указывается фразой LIKE.

Например:

CREATE TABLE tbl2 LIKE tbl1;.
^ Соединение таблиц Соединение одинаковых таблиц
Для соединения таблиц с одноименными столбцами или таблицы с самой собой используются алиасы, задаваемые во фразе FROM через пробел после имени таблицы.

Например:

select t1.f1, t1.f2, t2.f1, t2.f2 from tbl1 t1, tbl1 t2 where t1.f1= t2.f2;
^ Перекрестное соединение(CROSS JOIN)
Если фраза FROM определяет более одной таблицы или подзапроса, то все эти таблицы соединяются. По умолчанию объединенная таблица представляет собой перекрестное соединение (CROSS JOIN), называемое также декартовым произведением (Cartesian product).

Следующие два оператора эквивалентны:

SELECT tbl1.f1, tbl2.f1 FROM tbl1, tbl2;
SELECT tbl1.f1, tbl2.f1 FROM tbl1 CROSS JOIN tbl2;

Рассмотрим случай, когда таблицы tbl1 и tbl2 имеют строки, отображенные операторами SELECT на рисунке 3.2.



Рис. 3.2. Строки таблиц tbl1 и tbl2

Перекрестное соединение таблиц tbl1 и tbl2 оператором SELECT сформируют результирующий набор, отображаемый на рисунке 3.3.



Рис. 3.3. Перекрестное соединение (CROSS JOIN)

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

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

Соединяемые таблицы перечисляются через запятую во фразе FROM оператора SELECT.

Во фразе FROM можно использовать следующие операторы соединений:
^ Внутреннее соединение (INNER JOIN)
При внутреннем естественном соединении группируются только те строки, значения которых по соединяемым (одноименным) столбцам совпадают. Результат внутреннего соединения двух таблиц показан на рисунке 3.4.



Рис. 3.4. Внутреннее соединение (INNER JOIN)
^ Внешнее левое соединение LEFT JOIN [OUTER]
При внешнем левом соединении в результирующий набор будут выбраны все строки из левой таблицы (указываемой первой). При совпадении значений по соединяемым (одноименным) столбцам значения второй таблицы заносятся в результирующий набор в соответствующие строки. При отсутствии совпадений в качестве значений второй таблицы проставляется значение NULL.



Рис. 3.5. Внешнее левое соединение LEFT JOIN [OUTER]
^ Внешнее правое соединение RIGHT JOIN [OUTER]
При внешнем правом соединении в результирующий набор будут выбраны все строки из правой таблицы (указываемой второй). При совпадении значений по соединяемым (одноименным) столбцам значения первой таблицы заносятся в результирующий набор в соответствующие строки (рис. 3.6.). При отсутствии совпадений в качестве значений первой таблицы проставляется значение NULL.



Рис. 3.6. Внешнее правое соединение RIGHT JOIN [OUTER]
^ Полное внешнее соединение FULL JOIN [OUTER]
При внешнем правом соединении в результирующий набор будут выбраны все строки, как из правой, так и из левой таблицы. При совпадении значений по соединяемым (одноименным) столбцам строка содержит значения как из левой, так и из правой таблицы (рис. 3.7.). В противном случае, вместо отсутствующих значений в столбцы таблицы (левой или правой) заносится значение NULL.



Рис. 3.7. Полное внешнее соединение FULL JOIN [OUTER]
^ Соединение по указываемым столбцам
Фраза 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;

^ Лекция №4: Выполнение сложных SQL-запросов


Объединение запросов
Язык SQL предоставляет два способа объединения таблиц:
UNION-объединение
Фраза UNION объединяет результаты двух запросов по следующим правилам:


Рис. 4.1. Выполнение UNION-объединения с исключением совпадающих строк


Рис. 4.2. Выполнение UNION-объединения, использующего выражения

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

Для того чтобы явно указать требуемый порядок сортировки, следует использовать фразу ORDER BY. При этом можно использовать как имя столбца, так и его номер (рис. 4.3).


Рис. 4.3. Выполнение UNION-объединения с упорядочиванием результирующего набора

Фраза UNION ALL выполняет объединение двух подзапросов аналогично фразе ALL со следующими исключениями:

При объединении более двух запросов для изменения порядка выполнения операции объединения можно использовать скобки (рис. 4.4).


Рис. 4.4. Выполнение UNION-объединения для трех запросов
INTERSECT-объединение
Фраза INTERSECT позволяет выбрать только те строки, которые присутствуют в каждом объединяемом результирующем наборе. На рисунке 4.5 приведен пример объединения запросов как пересекающихся множеств.


Рис. 4.5. Выполнение INTERSECT-объединения
EXCEPT-объединение
Фраза EXCEPT позволяет выбрать только те строки, которые присутствуют в первом объединяемом результирующем наборе, но отсутствуют во втором результирующем наборе.

Фразы INTERSECT и EXCEPT должны поддерживаться только при полном уровне соответствия стандарту SQL-92. Так, некоторые СУБД вместо фразы EXCEPT поддерживают опцию MINUS (рис. 4.6).


Рис. 4.6. Выполнение MINUS (EXCEPT)-объединения

Как и для других типов объединения запросов, при выполнении EXCEPT-объединения совпадающие строки не входят в формируемый результирующий набор, что хорошо видно на приведенном рисунке.

Если применяется фраза INTERSECT ALL или EXCEPT ALL, то при пересечении множеств или вычитании множеств повторяемая строка удаляется столько раз из формируемого результирующего набора, сколько она повторяется в объединяемых результирующих наборах.

Фраза ^ CORRESPONDING BY позволяет использовать в объединяемых запросах различное число столбцов: в результирующий набор будут включены только столбцы, указанные в списке. Этот список также определяет порядок включения столбцов в результирующий набор.


^ Лекция №5: Использование вложенных SQL-запросов Подзапросы
Язык SQL разрешает использовать в других операторах языка DML подзапросы, которые являются внутренними запросами, определяемыми оператором SELECT.

Подзапрос - очень мощное средство языка SQL. Он позволяет строить сложные иерархии запросов, многократно выполняемые в процессе построения результирующего набора или выполнения одного из операторов изменения данных (DELETE, INSERT, UPDATE).

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



Подзапрос позволяет решать следующие задачи:

Hекоторые СУБД (например, СУБД Oracle) позволяют на основе подзапроса создавать новые таблицы с помощью оператора CREATE TABLE.

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

SELECT * from tbl1 WHERE f2=(SELECT f2 FROM tbl2
                              WHERE f1=1);

В данном операторе подзапрос всегда должен возвращать единственное значение, которое будет проверяться в предикате. Если подзапрос вернет более одного значения, то СУБД выдаст сообщение об ошибке выполнения SQL-оператора.

В случае если подзапрос не выберет ни одной строки, то предикат будет равен UNKNOWN, что большинством СУБД интерпретируется как FALSE.

Стандарт определяет запись предиката в форме "значение оператор подзапрос". Однако некоторые СУБД также позволяют записывать предикат в форме, указывающей подзапрос слева от оператора сравнения.

Например:

SELECT * from tbl1 WHERE
                    (SELECT f2 FROM tbl2 WHERE f1=1) = f2;

Очень часто с подзапросами используются агрегирующие функции, предоставляющие возможность сформулировать условие типа "больше, чем среднее по группе".

Например:

SELECT f1.f2.f3 FROM tbl1 WHERE f2>
                         (SELECT AVG(f2) FROM tbl1);

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

Например:

SELECT * from tbl1 WHERE f2 IN (SELECT f2 FROM tbl2
                              WHERE f1=1);

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

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

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

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

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

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

Например:

SELECT avg_f1, COUNT (f2) from tbl1
          GROUP BY avg_f1
          HAVING avg_f1 >(SELECT f1 FROM tbl1 WHERE f3='a1');
^ Коррелированные подзапросы
В операторе SELECT из внутреннего подзапроса можно ссылаться на столбцы внешнего запроса, указанного во фразе SELECT. Такой подзапрос выполняется для каждой строки таблицы, определяя условие ее вхождения в формируемый результирующий набор.

Например:

SELECT * from tbl1 t1
               WHERE f2 IN (SELECT f2 FROM tbl2 t2
                              WHERE t1.f3=t2.f3);

В данном случае для каждой строки таблицы tbl1 будет проверяться условие, что значение поля f2 совпадает со значением строки таблицы tbl2, где значение поля f3 равно значению поля f3 внешней таблицы (tbl1). Это простейший пример коррелированного подзапроса.

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

Например:

SELECT * from tbl1 t_out
WHERE f2< (SELECT AVG(f2) FROM tbl1 t_in
                    WHERE t_out.f1= t_in.f1);

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

Например:

SELECT f1, COUNT(*), SUM(f2) from tbl1 t1
          GROUP BY f1
          HAVING SUM(f2)> (SELECT MIN(f2)*4 FROM tbl1 t1_in
                              WHERE t1.f1=t1_in.f1);
^ Построение предиката для подзапроса, возвращающего несколько строк
Если в предикате надо сравнить значение с некоторым множеством, то, как было показано выше, можно использовать оператор IN.

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

Например:

SELECT f1,f2,f3 from tbl1
          WHERE EXISTS
               (SELECT * FROM tbl1 WHERE f4='10/11/2003');

Этот запрос будет формировать не пустой результирующий набор только в том случае, если в какое-либо значение столбца f4 таблицы была занесена дата, например: '10/11/2003'.

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

При коррелированном подзапросе оператор EXISTS будет вычисляться каждый раз для каждой строки внешнего запроса.

В стандарте SQL-92 не предусмотрено использование в подзапросах, к которым применяется оператор EXISTS агрегирующих функций. Однако некоторые СУБД позволяют такой вид подзапросов.

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

Приведем пример использования оператора ANY:

SELECT f1,f2,f3 from tbl1
          WHERE f3 = ANY (SELECT f3 FROM tbl2);

Данный оператор определяет, что в результирующий набор будут включены все строки, значение столбца f3 которых присутствует в таблице tbl2.
^ Применение подзапросов в операторах изменения данных
К операторам языка DML, кроме оператора SELECT, относятся операторы, позволяющие изменять данные в таблицах. Это оператор INSERT, выполняющий добавление одной или нескольких строк в таблицу, оператор DELETE, удаляющий из таблицы одну или несколько строк, и оператор UPDATE, изменяющий значения столбцов таблицы.
^ Оператор INSERT
Оператор INSERT в стандарте SQL-92 имеет следующее формальное описание:

INSERT INTO table_name
     [ (field .,…) ]
     { VALUES (value .,…) }
     | subquery
     | {DEFAULT VALUES};

Оператор INSERT может добавлять в таблицу как одну, так и несколько строк. Список полей (field .,…) указывает имена полей и порядок занесения в них значений из списка значений, определяемого фразой VALUES, или как результат выполнения подзапроса.

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

Если список полей (field .,…) опущен, то порядок занесения значений будет соответствовать порядку столбцов, указанному в операторе CREATE TABLE при создании данной таблицы.

Если для столбцов, на которые установлено ограничение NOT NULL, не указано добавляемых данных, то СУБД инициирует ошибку выполнения SQL-оператора.

Следующий оператор INSERT демонстрирует копирование строк таблицы tbl2, выполняемое на основе подзапроса:

INSERT INTO tbl1(f1,f2,f3)
     (SELECT f1,f2,f3 FROM tbl2);

Очевидно, что количество полей, указываемое списком полей, и типы данных этих полей должны совпадать с количеством полей и их типами данных в конструкторе значений таблицы или в результирующем наборе, формируемом подзапросом.
^ Оператор DELETE
Оператор DELETE в стандарте SQL-92 имеет следующее формальное описание:

DELETE FROM table_name
     [ { WHERE condition }
     | { WHERE CURRENT OF cursor_name } ];

Оператор DELETE используется для удаления из таблицы строк, указанных условием во фразе WHERE (поисковое удаление, searched deletion) или WHERE CURRENT OF (позиционное удаление, positioned deletion).

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

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

Никогда нельзя забывать, что если фраза WHERE будет отсутствовать или предикат во фразе WHERE будет всегда принимать значение TRUE, то оператор DELETE удалит из таблицы все строки.
^ Оператор UPDATE
Оператор UPDATE в стандарте SQL-92 имеет следующее формальное описание:

UPDATE table_name
     SET { field = expr } .,…
     [ { WHERE condition }
     | { WHERE CURRENT OF cursor_name } ];

Оператор UPDATE применяется для внесения изменений в данные таблиц.

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

При вычислении значений столбцов можно применять условное выражение CASE и выражение CAST для приведения типов.

Например:

SELECT f1, CAST (f2 AS CHAR), CAST (f3 AS CHAR) from tbl1;
UPDATE tbl2 SET f2 = (SELECT CAST (f2 AS CHAR) from tbl1
                         WHERE f1=1);
UPDATE tbl2 SET f5 = (SELECT CAST (f5 AS DATE) from tbl1
                         WHERE f1=1),
               f6= CAST ('10/12/2003' AS DATE);


^ Условное выражение CASE позволяет выбрать одно из нескольких значений на основании указываемого условия.

Условное выражение CASE имеет следующее формальное описание:

{ CASE
     { expr WHEN expr THEN NULL } …
     | { WHEN expr THEN expr } …
          [ ELSE NULL ]
END}
     | { NULLIF {expr1,expr2) }
     | {COALESCE (expr .,…) }

Условное выражение CASE может быть записано, соответственно, в четырех формах:

SELECT f1, CASE f3 'abc' THEN '1_abc' END FROM tbl1;

SELECT f1, CASE WHEN f3= 'abc' THEN '1_abc' ELSE f3 END FROM tbl1;

UPDATE tbl2 SET f3 = (SELECT ^ NULLIF (f3,'aaa') FROM tbl1 WHERE f1=1);

INSERT INTO tbl1(f1,f2)
     VALUES (1+ COALESCE(SELECT MAX(f1)
               FROM tbl1, 0 ), 100);

Для успешного выполнения оператора UPDATE требуется ряд условий, включая следующие:
^ Лекция №6: Работа с представ-лениями. Типы данных. Создание представлений
Представление (view), иногда называемое также видом, определяет логическую таблицу, получаемую как результат выполнения сохраненного запроса. Представление - это некоторая логическая (виртуальная) таблица, которая формируется заново каждый раз, когда в SQL-операторе встречается ссылка на конкретное представление. Результирующий набор, создаваемый как результат выполнения запроса, определяющего данное представление, формируется из полей других таблиц базы данных. Таблицы, используемые в запросе для создания представления, называются простыми основными таблицами.

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

Для определения представления применяется оператор CREATE VIEW.
^ Оператор CREATE VIEW
Оператор CREATE VIEW имеет в стандарте SQL-92 следующее формальное описание:

CREATE VIEW table_name [(field .,…) ]
          AS (SELECT_operator
               [WITH [CASCADED | LOCAL] CHECK OPTION ] );

Список полей (field), указываемый после имени представления, позволяет переименовать столбцы основных таблиц, используемых в запросе. Это может потребоваться в случае совпадения имен столбцов при запросах, использующих объединение таблиц; для именования вычислимых столбцов; для именования объединенных столбцов, полученных посредством соединения столбцов из двух таблиц, имеющих различные имена полей.

^ Оператор запроса SELECT, использующийся для построения представления, может иметь две формы:

Расширяемая форма оператора SELECT задается как конструкция SELECT *, не ограничивая жестко список полей, извлекаемых в запрос. Это позволяет не менять синтаксис представления при изменении оператором ALTER TABLE структуры таблицы: добавлении новых столбцов или удалении столбцов. Однако это также может являться и недостатком, если SQL-операторы, использующие представление, зависят от числа, типа и имен столбцов.

Постоянная форма оператора SELECT задается как конструкция SELECT список_столбцов, жестко фиксируя имена столбцов, входящих в запрос.

Как будет влиять изменение основных таблиц на представление можно указать в операторе ALTER TABLE:

Оператор ALTER TABLE имеет в стандарте SQL-92 следующее формальное описание:

ALTER TABLE table_name
     { ADD [COLUMN] column_name column_type [(size)]
                    [column_ constraint] }
     | { ALTER [COLUMN] column_name
               { SET DEFAULT value } | DROP DEFAULT }
     | DROP [COLUMN] column_name RESTRICT
     | { ADD table_ constraint }
     | CASCADE ;

Поддержка оператора ALTER TABLE необходима только для полного уровня соответствия стандарту, однако, большинство коммерческих СУБД реализует этот оператор, но с некоторыми изменениями и расширениями.

Следующий оператор иллюстрирует изменение таблицы, приводящее к удалению всех представлений, ссылающихся на столбец f2 изменяемой таблицы:

ALTER TABLE tbl1 DROP COLUMN f2 CASCADE;
^ Изменение данных в представлениях
Если для представления указывается оператор DELETE, INSERT или UPDATE, то все изменения происходят как над представлением, так и над основными таблицами, используемыми для создания представления. Не во все представления можно внести изменения. Так, представления могут быть изменяемыми или постоянными.

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

Стандарт SQL-92 определяет, что представление является изменяемым, если выполнены следующие условия:

  • запрос, используемый для создания представления, извлекает данные только из одной таблицы;

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

  • не разрешается никаких объединений таблиц, даже самой с собой;

  • запрос, используемый для создания представления, не должен содержать вычислимых столбцов, агрегирующих функций и фраз DISTINCT, GROUP BY и HAVING;

  • в запросе, используемом для создания представления, нельзя ссылаться дважды на один и тот же столбец.
^ Опции [WITH [CASCADED | LOCAL] CHECK OPTION
Для изменяемого представления можно указывать фразу WITH CHECK OPTION, позволяющую предотвращать "потерю строк" в представлениях. Так, если эта фразу указана, то при внесении изменений в таблицу будет проверен предикат, указанный в запросе, использованном для создания таблицы. Если предикат не возвращает значение TRUE, то изменения не будут внесены.

Например, если запрос создан следующим оператором

CREATE VIEW v_tbl1 AS (SELECT f1,f2, f3 FROM tbl1 WHERE f2>100) WITH CHECK OPTION;,

то вставка строки не будет произведена:

INSERT INTO v_tbl1 (f1,f2,f3) VALUES (1,50,'abc');.

Фраза WITH CHECK OPTION может быть расширена до:

  • WITH CASCADED CHECK OPTION - предикаты проверяются во всех вложенных запросах;

  • WITH LOCAL CHECK OPTION- предикаты проверяются только в запросе, использованном для создания данного представления;

Так, для представления, созданного операторами

CREATE VIEW v_1 AS (SELECT f1,f2, f3 FROM tbl1 WHERE f2>100);,
CREATE VIEW v_2 AS (SELECT f1,f2, f3 FROM v_1 WHERE f2>50) WITH LOCAL CHECK OPTION;,

добавление строки будет выполнено:

INSERT INTO v_2 (f1,f2,f3) VALUES (1,30,'abc');.

Эта строка будет добавлена в основную таблицу, но не будет видна в представлении, посредством которого она была добавлена.

По умолчанию предполагается, что для WITH CHECK OPTION используется фраза CASCADED.


^ Типы данных
Каждый столбец базы данных имеет свой тип, указываемый при создании столбца.

В стандарте SQL-92 определены следующие типы:



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

Для символьных типов возможно указание фразы CARACTER SET set_name , устанавливающей используемый набор символов.

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

Типы данных, описывающие дату и время, состоят из нескольких полей, в которых хранятся части даты времени. Так, тип DATE содержит поля YEAR, MONTH и DAY. Тип TIME содержит поля HOUR, MINUTE и SECOND.

Тип TIMESTAMP содержит как поля даты, так и поля времени.

Значение типа TIMESTAMP записывается следующим образом: '10-12-2003 08:30:00'. Порядок следования полей при написании даты, как правило, определяется установками компьютера.
^ Функции даты/времени
Для работы с данными, имеющими тип даты/времени в языке SQL предусмотрены следующие функции:

CURRENT_TIME - определяет текущее время;
CURRENT_DATE - определяет текущую дату;
CURRENT_TIMESTAMP - определяет текущие дату и время.

Например:

INSERT INTO tbl1 (f1,f2,f3,f4)
     VALUES (1,100,'abc', CURRENT_DATE);

^ Лекция №7: Транзакции в базах данных Транзакции
Транзакцией называется последовательность действий, которая или полностью фиксируется в базе данных, или полностью отменяется. Иногда под транзакцией также подразумевают не группу SQL-операторов, а интервал времени, выполняемые в течение которого SQL-операторы можно или все зафиксировать или все отменить.

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

^ Фиксация транзакции может производиться принудительно по SQL-оператору или неявно после завершения каждого SQL-оператора. Во втором случае применяется режим автокоммита. Как правило, выполнение SQL-операторов в интерактивном режиме всегда использует автокоммит. Очень часто в интегрированных средах разработки классы, инкапсулирующие работу с базой данных, по умолчанию предполагают режим автокоммита.

Следующая схема демонстрирует принцип использования транзакций.



Новая транзакция начинается с начала каждого сеанса работы с базой данных. Далее все выполняемые SQL-операторы будут входить в одну транзакцию до тех пор, пока не будет выполнен оператор COMMIT WORK или ROLLBACK WORK.

Оператор COMMIT WORK завершает текущую транзакцию, выполняя фиксацию сделанных изменений в базе данных. Иногда говорят, что оператор COMMIT WORK фиксирует транзакцию.

Оператор ROLLBACK WORK выполняет откат транзакции, отменяя действие всех SQL-операторов, выполненных в текущей транзакции.

Логически транзакция должна объединять только выполнение взаимосвязанных операций. Так, если делать транзакции "очень большими", состоящими из последовательности не связанных между собой операторов, то любой сбой, автоматически выполняющий откат транзакции, повлияет на отмену действий, которые могли бы быть успешно завершены при более "коротких" транзакциях.
^ Автоматическая фиксация изменений
Большинство коммерческих СУБД позволяет устанавливать режим автоматической фиксации изменений - автокоммит.

Для установки этого режима используется (но не всеми СУБД) оператор SET AUTOCOMMIT ON;, а для отмены режима - SET AUTOCOMMIT OFF;.
^ Проблемы параллельного доступа с использованием транзакци
При параллельном использовании транзакций могут возникать следующие проблемы:

Рассмотрим ситуации, в которых возможно возникновение данных проблем.
^ Неповторяющееся чтение
Предположим, имеется две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1

Транзакция 2

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=f2+1 WHERE f1=1;

SELECT f2 FROM tbl1 WHERE f1=1;

SELECT f2 FROM tbl1 WHERE f1=1;

В транзакции 2 выбирается значение поля f2, затем в транзакции 1 изменяется значение поля f2. При повторной попытке выбора значения из поля f2 в транзакции 1 будет получен другой результат (рис. 7.1). Эта ситуация особенно неприемлема, когда данные считываются с целью их частичного изменения и обратной записи в базу данных.


Рис. 7.1. Неповторяющееся чтение
^ "Грязное" чтение
Предположим, имеется две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1

Транзакция 2

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=f2+1 WHERE f1=1;

^ ROLLBACK WORK;



SELECT f2 FROM tbl1 WHERE f1=1;

В транзакции 1 изменяется значение поля f1, а затем в транзакции 2 выбирается значение поля f1. После этого происходит откат транзакции 1. В результате значение, полученное второй транзакцией, будет отличаться от значения, хранимого в базе данных.
^ Потерянное обновление
Предположим, имеется две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1

Транзакция 2

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=20 WHERE f1=1;

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=25 WHERE f1=1;

В транзакции 1 изменяется значение поля f1, а затем в транзакции 2 также изменяется значение этого поля. В результате изменение, выполненное первой транзакцией, будет потеряно.
^ Фантомная вставка
Предположим, имеется две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1

Транзакция 2



INSERT INTO tbl1 (f1,f2) VALUES (15,20);

SELECT SUM(f2) FROM tbl1;

SELECT SUM(f2) FROM tbl1;

В транзакции 2 выполняется SQL-оператор, использующий все значения поля f2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется фантомной вставкой и является частным случаем неповторяющегося чтения. При этом, если выполняемый SQL-оператор выбирает не все значений поля f2, а только значение одной строки таблицы (используется предикат WHERE), то выполнение оператора INSERT не приведет к ситуации фантомной вставки.
^ Уровни изоляци
Стандарт SQL-92 определяет уровни изоляции, установка которых предотвращает определенные конфликтные ситуации.

Введены следующие четыре уровня изоляции:

  • ^ SERIALIZABLE - последовательное выполнение (используется по умолчанию). Этот уровень гарантирует предотвращение всех описанных выше конфликтных ситуаций, но, соответственно, при нем наблюдается самая низкая степень параллелизма;

  • REPEATABLE READ - повторяющееся чтение. На этом уровне разрешено выполнение операторов INSERT, приводящих к конфликтной ситуации "фантомная вставка". Этот уровень целесообразно использовать, если на выполняющиеся SQL-операторы не влияет добавление новых строк;

  • ^ READ COMMITED - фиксированное чтение. Этот уровень позволяет получать разные результаты для одинаковых запросов, но только после фиксации транзакции, повлекшей изменение данных;

  • ^ READ UNCOMMITED - нефиксированное чтение. Здесь возможно получение разных результатов для одинаковых запросов без учета фиксации транзакции.



В следующей таблице приводится формальное описание уровней изоляции.





konspekt-uroka-fizicheskoj-kulturi-s-uchashimisya-4-a-klassa-gimnazii-5-im-lesi-ukrainki.html
konspekt-uroka-fiziki-provedennogo-v-7-klasse-po-teme-plavanie-tel.html
konspekt-uroka-formatirovanie-abzacev-microsoft-word.html
konspekt-uroka-geografii-v-8-klasse-po-teme-chelovek-i-voda.html
konspekt-uroka-himii-karbonovie-kisloti-v-zhizni-cheloveka-hrulev-sergej-alekseevich.html
konspekt-uroka-igri-po-fizike-puteshestvie-po-vselennoj.html
  • pisat.bystrickaya.ru/tipovaya-dolzhnostnaya-instrukciya-inzhenera-geodezista-resheniem-obshego-sobraniya.html
  • prepodavatel.bystrickaya.ru/tema-2-beznalichnie-rascheti-tema-kreditnaya-sistema-2.html
  • otsenki.bystrickaya.ru/regioni-kadri-sro-glavnie-prioriteti-v-rabote-rossijskogo-soyuza-stroitelej.html
  • assessments.bystrickaya.ru/doklad-municipalnogo-obsheobrazovatelnogo-uchrezhdeniya-licej-prikladnih-nauk.html
  • institute.bystrickaya.ru/glava-xxxiv-hroniki-peru-rasskaz.html
  • esse.bystrickaya.ru/puti-dostizheniya-programma-razvitiya-territorii-goroda-riddera-vostochno-kazahstanskoj-oblasti-na-2011-2015-godi-2010-god.html
  • shpargalka.bystrickaya.ru/v-okopah-stalingrada-stranica-14.html
  • testyi.bystrickaya.ru/autsorsing-kak-instrument-snizheniya-zatrat-i-optimizacii-biznes-sistemi.html
  • obrazovanie.bystrickaya.ru/pravila-rascheta-potenciala-energosberezheniya-pri-provedenii-energeticheskogo-obsledovaniya-nekommercheskogo-partnerstva-po-sodejstviyu-v-oblasti-energosberezheniya-i-energoeffektivnosti-energoaudit.html
  • zanyatie.bystrickaya.ru/pravovoe-polozhenie-yuridicheskih-lic-v-mezhdunarodnom-chastnom-prave.html
  • uchitel.bystrickaya.ru/razrabotka-dlya-kontrolya-i-opredeleniya-tipa-logicheskih-integralnih-mikroshem-metodom-signaturnogo-analiza.html
  • tasks.bystrickaya.ru/25-problemi-vnedreniya-rezultatov-sistemnogo-analiza-vozniknovenie-nauki-ob-upravlenii.html
  • thesis.bystrickaya.ru/pravosudie-vihodit-v-onlajn-gosduma-rf-monitoring-smi-10-iyunya-2008-g.html
  • urok.bystrickaya.ru/programma-mezhdisciplinarnogo-gosudarstvennogo-ekzamena-po-specialnosti-orenburg.html
  • uchitel.bystrickaya.ru/razdel-1-obespechenie-realnogo-ravenstva-prav-i-vozmozhnostej-zakon-om.html
  • desk.bystrickaya.ru/p-p-gnedich-vsemirnaya-istoriya-iskusstv-stranica-6.html
  • uchebnik.bystrickaya.ru/uslovnie-oboznacheniya-rabochaya-programma-disciplini-disciplina-b-2-matematika-indeks-i-naimenovanie-disciplini.html
  • studies.bystrickaya.ru/israel-palestine-conflict.html
  • tasks.bystrickaya.ru/12-spasi-i-sohrani-taran-vyacheslav-aleksandrovich-igrat-na-birzhe-prosto.html
  • essay.bystrickaya.ru/deputat-lebedev-igornij-biznes-eto-zlo-s-kotorim-nuzhno-borotsya-radio-12-mayak-novosti-13-12-2005-garin-petr-12-00-12.html
  • predmet.bystrickaya.ru/sekretar-fedorisheva-ov-starshij-prepodavatel-kafedri-ekonomiki-i-upravleniya-na-predpriyatii.html
  • holiday.bystrickaya.ru/monitoring-smi-rf-po-pensionnoj-tematike-15-aprelya-2011-goda.html
  • letter.bystrickaya.ru/nu-chto-protivnie-nu-chto-yunie-pakostniki-vse-gotovi-dokazat-svoyu-merzkuyu-sushnost-golosa.html
  • znanie.bystrickaya.ru/annotirovannij-spisok-resursov-internet-po-teme-nestandartnie-zadachi-i-netradicionnie-sposobi-ih-resheniya.html
  • tests.bystrickaya.ru/lekciya-12-gumoralnij-immunitet-immunoglobulini-rol-antitel-v-immunnom-otvete-reakciya-antigen-antitelo-ee-primenenie.html
  • shpargalka.bystrickaya.ru/v-cherepovce-zalozhili-pervij-kamen-ledovogo-dvorca-v-bryanske-otkrilsya-fizkulturno-ozdorovitelnij-kompleks-7.html
  • occupation.bystrickaya.ru/nataliya-dmitrievna-krivolapchuk-stranica-11.html
  • ucheba.bystrickaya.ru/pravovoe-obespechenie-vnedreniya-elektronnih-administrativnih-reglamentov.html
  • occupation.bystrickaya.ru/nesushie-i-ograzhdayushie-konstrukcii-stranica-6.html
  • urok.bystrickaya.ru/postscriptum-novij-zavod-knauf-obespechit-severo-zapad-gipsokartonom.html
  • shpora.bystrickaya.ru/yurevna-formirovanie-sociokulturnoj-kompetentnosti-budushih-specialistov-po-socialnoj-rabote-s-semej-13-00-08-teoriya-i-metodika-professionalnogo-obrazovaniya.html
  • zadachi.bystrickaya.ru/primenenie-tehnicheskogo-analiza-na-fondovom-rinke.html
  • universitet.bystrickaya.ru/strategiya-razvitiya-pishevoj-i-pererabativayushej-promishlennosti-v-kemerovskoj-oblasti-do-2025-goda-glava-obshie-polozheniya.html
  • portfolio.bystrickaya.ru/okno-v-nevedomij-mir-lyudi-osoboj-sudbi.html
  • knigi.bystrickaya.ru/rol-religii-v-duhovno-nravstvennom-vospitanii-molodezhi.html
  • © bystrickaya.ru
    Мобильный рефератник - для мобильных людей.