Выбор данных из базы – оператор JOIN

В предыдущем разделе рассматривались варианты запросов с соединением двух и более таблиц. Условие соединения таблиц определялось в логическом выражении ключевого слова WHERE. Поскольку в реальных задачах большинство запросов используют соединения таблиц и виды этих соединений могут быть различными (ранее рассмотрен только один из видов), то в стандарте SQL появилась альтернативная возможность спецификации соединений – оператор JOIN.

Оператор JOIN применяется после ключевого слова FROM и имеет следующий формат (далее указана только форма FROM все формат всех остальных ключевых слов запроса не изменяется):

FROM источник_данных [псевдоним] тип_соединения JOIN

источник_данных [псевдоним] [ON условие_соединения] [тип_соединения JOIN

источник_данных [псевдоним] [ON условие_соединения] …]

 

Условие_соединения – это логическое выражение правило формирование совпадает с правилами формирования условия после ключевого слова WHERE.

Тип_соединения – это один из следующих элементов:

{INNER | {LEFT | RIGHT | FULL} OUTER | CROSS }

 

Определены три типа соединений:

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

 

INNER – внутреннее соединение, в результате данного соединения формируется таблица, состоящая из строк декартового произведения, удовлетворяющих условию, указанному после ON. Данный вид соединения является симметричным.

 

OUTER – внешнее соединение. При внешнем соединении в результирующую таблицу обязательно попадают все строки одной из соединяемых таблиц или обоих. Различают три типа внешних соединений.

LEFT – левое внешнее соединение, в результате данного соединения формируется таблица, объединяющая две таблицы.

Первая таблица – это результат внутреннего соединения (INNER JOIN) левой и правой таблиц. Вторая таблица состоит из записей левой таблицы, которые не вошли в первую таблицу. Для второй таблицы поля, соответствующие правой таблице, заполняются значениями NULL.

RIGHT – правое внешнее соединение, в результате данного соединения формируется таблица, объединяющая две таблицы.

Первая таблица – это результат внутреннего соединения (INNER JOIN) левой и правой таблиц. Вторая таблица состоит из записей правой таблицы, которые не вошли в первую таблицу. Для второй таблицы поля, соответствующие левой таблице, заполняются значениями NULL.

 

FULL – полное внешнее соединение, в результате данного соединения формируется таблица, объединяющая три таблицы.

Первая таблица – это результат внутреннего соединения (INNER JOIN) левой и правой таблиц. Вторая таблица состоит из записей правой таблицы, которые не вошли в первую таблицу. Для второй таблицы поля, соответствующие левой таблице, заполняются значениями NULL. Вторая таблица состоит из записей левой таблицы, которые не вошли в первую таблицу. Для второй таблицы поля, соответствующие правой таблице, заполняются значениями NULL. Данный вид соединения является симметричным.

Выбор данных из базы – источник данных запрос

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

 

 

Управление структурой базы данных

Типы команд управления структурой

В SQL языке определены три типа команд, которые служат для управления всеми объектами структуры базы данных:

· CREATE – команда создания объекта;

· ALTER – изменение объекта;

· DROP – удаление объекта.

 

После команды следует ключевое слово, указывающее на тип обрабатываемого объекта:

· TABLE– таблица;

· TRIGGER – триггер;

· VIEW – представление;

· и т.д.

Далее следует часть, зависимая от команды и типа объекта.

Типы объектов структуры

Основным объектом структуры базы данных является таблица. Следующими по важности объектами являются ключи: первичные, внешние, возможные. Как и все объекты, они могут управляться отдельными командами, но чаще всего эти объекты управляются в рамках команд управления таблицами, что будет показано ниже.

В рамках пособия будут рассмотрены два вспомогательных объекта: представление и триггер.

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

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

Создание таблицы

Для создания таблицы применяется команда CREATE TABLE. Формат команды зависит от конкретной системы управления базой данных. Однако, отличия касаются в первую очередь дополнительных модификаторов полей, базовый синтаксис поддерживается всеми системами управления базами данных.

Формат команды

CREATE TABLE имя_таблицы (
столбец тип_данных [ DEFAULT значение_по_умолчанию ]
[ [ CONSTRAINT имя_ограничения ] ограничение_поля ]
[, столбец тип_данных [ DEFAULT значение_по_умолчанию ]
[ [ CONSTRAINT имя_ограничения ] ограничение_поля ] ]...
[, [CONSTRAINT имя_ограничения] ограничение_таблицы ]...);

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

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

[ NULL | NOT NULL ]

| [ { PRIMARY KEY | UNIQUE }]

| [ [ FOREIGN KEY ] REFERENCES родительская_таблица [(поле_родительской_таблицы) ]]

Первое из трех ограничений определяет возможность задания полю неопределенного значения.

Два других ограничения связаны с понятием ключа. Первое из них – ограничение первичного ключа или задание возможного первичного ключа (UNIQUE). Второе задает ограничение внешнего ключа. Задается имя и поле родительской таблицы (первичный ключ).

Если ограничение поля не задано, то это эквивалентно указанию ограничения NULL, т.е. возможность задания неопределенного значения.

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

{ PRIMARY KEY | UNIQUE }

(поле_таблицы [, поле_таблицы[,..]])

| FOREIGN KEY

(поле_таблицы [, поле_таблицы[,..]])

REFERENCES родительская_таблица [ (поле_родительской_таблицы [поле_родительской_таблицы,...])

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

Удаление таблицы

Удаление таблицы осуществляется командой вида:

DROP TABLE имя_таблицы

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

Создание представления

Далее приведен упрощенный синтаксис команды создания представления:

CREATE VIEW имя_представления [ (поле1 [,...полеn ]) ] AS команда_select

Поле1 [,...полеn ] – имя, которое будет иметь столбец в представлении. Имя столбца требуется только в тех случаях, когда столбец формируется на основе арифметического выражения, функции или константы, если два или более столбцов могут по иной причине получить одинаковые имена (как правило, в результате соединения), или если столбцу представления назначается имя, отличное от имени столбца, от которого он произведен. Назначать столбцам имена можно также в инструкции SELECT.

Если аргумент полеn не указан, столбцам представления назначаются такие же имена, которые имеют столбцы в инструкции SELECT.

Команда_select – команда SELECT, которая определяет представление. Представление не обязательно является простым подмножеством строк и столбцов одной конкретной таблицы. С помощью предложения SELECT можно создавать представление, использующее более одной таблицы, или другие представления любой степени сложности. В команде нельзя использовать ключевое слово ORDER BY, кроме специального случая, не рассматриваемого в рамках данного материала.

Ниже приведен пример создания представления по запросу, выводящему на экран список рейсов (номер рейса) и пунктов прибытия этих рейсов (этот пример рассматривался ранее):

CREATE VIEW Представление_рейсы AS

SELECT Рейсы.№Рейса, Пункты.Пункт_прибытия

FROM Рейсы, Пункты

WHERE Рейсы.№Пункта = Пункты.№Пункта

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

SELECT Представление_рейсы.№Рейса, Представление_рейсы.Пункт_прибытия

FROM Представление_рейсы

или

SELECT *

FROM Представление_рейсы

Удаление представления

Удаление представления осуществляется командой вида:

DROP VIEW имя_представления

 

Изменение представления

Внесение изменений в представление осуществляется командой ALTER VIEW. Формат команды полностью совпадает с форматом команды CREATE VIEW. И является фактически комбинацией удаления и создания представления.

Создание триггера

Ниже приведена упрощенная структура команды создания триггера:

CREATE TRIGGER Имя_тригера ON { таблица }

{ FOR | INSTEAD OF } { [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }

AS { sql-команда }

Имя_тригера – уникальное имя, удовлетворяющее стандарту названий SQL-объектов: комбинация латинских символов и цифр, исключая символы разделители: пробел, запятая, точка запятая и д.р. В дальнейшем, это имя будет использоваться для идентификации триггера в командах изменения и удаления.

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

FOR | INSTEAD OF – признак момента исполнения триггера.

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

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

[ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] - определяет команды изменения данных, по которым срабатывает триггер. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке.

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

Триггеры используют логические (концептуальные) таблицы deleted и inserted. По своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя. При операции INSERT таблица inserted будет содержать строку с вводимой строкой, а таблица deleted будет пустой. При операции DELETE таблица deleted будет содержать удаляемую строку с вводимой строкой, а таблица inserted будет пустой. При операции UPDATE таблица deleted будет содержать строку со значениями до изменения, а таблица inserted содержать строку с новыми значениями.

Transact-SQL – расширенный язык SQL, требующий отдельного рассмотрения. Помимо команд SELECT, Команд управления структурой и манипулирования данными, для выполнения работы, приведенной в приложении, необходимы следующие конструкции:

· внутренние переменные, их объявление и применение;

· оператор сравнения.

 

Объявление внутренних переменных осуществляется в секции DECLARE. При этом указывается имя переменной, обязательно начинающееся с символа @ и тип переменной.

Например:

DECLARE

@id int,

@LAST_NAME varchar(100),

@floatdata float

 

Присвоение значений внутренних переменных осуществляется через команду select.

Присвоение константы:

SELECT @id = 12

Присвоение значения выражения:

SELECT @floatdata = @id+1.3

Присвоение нескольких значений одной командой:

SELECT @floatdata = 1, @id = @id +1

Присвоение значений из базы данных (пример по базе данных для лабораторной работы №1):

SELECT @floatdata = (COMMISSION+ SALARY), @id = JOB_ID FROM employee Where EMPLOYEE_ID = 1

При этом запрос должен возвращать либо одну строку, либо не одной. В случае, если запрос не возвращает строк, то внутренним переменным будет присвоено значение NULL.

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

IF {условие}

{sql-команда}

[Else

{sql-команда}]

 

sql-команда – одна команда Transact-SQL, однако можно применять составную команду, образуемую оборачиванием группы команд в конструкцию BEGIN … END.

Команда, присваивающая значение внутренней переменной в зависимости от значений другой внутренней переменной:

IF @id > 10

SELECT @floatdata = 10

ELSE

SELECT @floatdata = 11

Ниже приведен пример триггера, выполняющегося вместо операции изменения данных. При срабатывании триггера осуществляется проверка нового значения поля intdata. Если его значение меньше 0, то вместо нового значения в таблицу заносится предыдущее значение, увеличенное на 1. В противном случае новое значение поля intdata не изменяется. Новое значение поля id записывается в таблицу безусловно.

Сначала определим команду создающую таблицу:

CREATE TABLE mydata_1 (

id INT PRIMARY KEY,

intdata INT NOT NULL);

Далее создадим триггер.

Create TRIGGER mydata_1_tr

ON mydata_1

INSTEAD OF UPDATE

AS

-- определим действие триггера – вместо операции изменения

DECLARE

@new_id int,

@new_intdata int

 

begin

--запросили новые значения полей

SELECT @new_id = id, @new_intdata = intdata from inserted

 

IF @new_intdata < 0 -- проверили значение поля inddata

-- вернули в переменную старое значение поля intdata

SELECT @new_intdata = intdata + 1 from deleted

--проведем реальное изменение значений записи

Update mydata1 set id = @new_id, intdata=@new_intdata

Where id = @new_id

end

 

Удаление триггера

Удаление триггера осуществляется командой вида:

DROP TRIGGER имя_триггера

 

Изменение триггера

Внесение изменений в представление осуществляется командой ALTER TRIGGER. Формат команды полностью совпадает с форматом команды CREATE TRIGGER. И является фактически комбинацией удаления и создания триггера.

 

Манипулирование данными

Ввод данных

Команда INSERT служит для добавления новых записей в таблицу базы данных. Формат команды:

INSERT INTO имя_таблицы [ (поле1, поле2, поле3) ]
VALUES (знач1, знач2, знач3);

Если после параметра имя_таблицы сразу следует ключевое слово VALUES, то система управления базами данных считает, что значения полей новой записи перечислены в том же порядке, что и поля таблицы при её создании – это неявное указание последовательности полей. Формат команды INSERT позволяет задать последовательность вводимых полей в явном виде перед ключевым полем VALUES.

Далее приведен пример заполнения таблицы «МАРКИ» с явным и неявным указанием последовательности полей. Пусть последовательность полей при создании таблицы совпадает с последовательностью, указанной на рисунке 6.

INSERT INTO Марки (Кол-во_мест, Марка_Cамолета, №Марки) VALUES(150,“ТУ-154”,1);

INSERT INTO Марки VALUES(2,“ТУ-134”,70);

В первой команде поля переставлены намеренно.

Изменение данных

Для изменения значения одного или нескольких полей одной или нескольких строк таблицы используется команда UPDATE. Формат команды:

UPDATE имя_таблицы SET столбец = значение | выражение
[, столбец = значение | выражение ]...
[WHERE фраза];

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

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

Далее приведены примеры изменения значения поля «Кол-во_мест» в таблице «МАРКИ».

UPDATE Марки SET Кол-во_мест = Кол-во_мест+10;

UPDATE Марки SET Кол-во_мест = 130 WHERE №Марки = 2;

Первая команда изменяет все строки таблицы, увеличивая количество мест на 10, т.е. до 160 и 80 соответственно.

Вторая устанавливает значение поля «Кол-во_мест», равное 130 для записи со значением поля «№Марки», равным 2.

 

Удаление данных

Для удаления одной или нескольких строк таблицы используется команда DELETE. Формат команды:

DELETE FROM имя_таблицы [ WHERE фраза ];

Фраза WHERE используется для задания условий отбора удаляемых строк.