Функции преобразования одних типов данных в другие и форматирование дат

SQL-сервер поддерживает три функции преобразования типов:

1) convert;

2) inttohex;

3) hextoint.

Синтаксис функции convert:

convert (<тип данных>, <выражение> [, <стиль>])

Пример:

select title, convert(char(5), total_sales) from titles

Правила преобразования:

1) преобразование данных типов character в данные типов money, date/time, всех типов numeric и approx_numeric осуществляется только в том случае, если все символы в строке допустимы в новом типе;

2) при преобразовании целых типов данных в тип character необходимо указывать соответствующую длину строки символов;

3) функцию convert можно использовать при преобразовании типов money, date/time, всех типов numeric, integer, binary и image.

Функции inttohex и hextoint служат для преобразования целых чисел в шестнадцатиричные и наоборот.

Операция Join (соединения таблиц).

Для выполнения операции соединения нескольких таблиц в предложении SELECT необходимо соблюдать три условия:

1) В списке выборки имена колонок указываются с именами таблиц, в которые входят эти колонки.

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

3) В предложении where указываются все связи таблиц, где каждая таблица соединяется с другой по полям, определенным на одинаковых доменах, с помощью операций сравнения: =, >, >=, <, <=, !=, !>, !<.

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

1) *= - включаются все записи из первой названной таблицы;

2) =* - включаются все записи из второй названной таблицы.

При этом, если указывается операция “*=”, то в отчет включаются все записи из первой таблицы, а в колонках, принадлежащих второй таблице, в записях, не удовлетворяющих условию соединения, ставятся null значения. Аналогично, если указывается операция “=*”, то в отчет включаются все записи из второй таблицы, а в колонках, принадлежащих первой таблице, в записях, не удовлетворяющих условию соединения, ставятся null значения.

Пример:

select au_fname, au_lname, pub_name from authors, publishers

where authors.city *= publishers.city

Подзапросы

Подзапрос - это select - предложение, вложенное в другое select-, insert-, update- или delete-предложение или в другой подзапрос.

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

1) where <выражение> [not] in (<подзапрос>)

2) where <выражение> <операция сравнения> [any|all] (<подзапрос>)

3) where [not] exists (<подзапрос>)

Подмножества

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

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

1) предоставления пользователю только интересующих его данных;

2) упрощения манипулирования данными (представления определяются сложными операциями выборки, проекции и соединения);

3) предоставления различным пользователям видеть одни и те же данные по-разному;

4) обеспечения механизма секретности данных;

5) поддержки логической независимости данных.

Механизм секретности данных осуществляется командами grant и revoke, примененным к представлениям. При этом пользователи получают доступ к различным подмножествам данных:

1) доступ к подмножеству записей базовой таблицы;

2) доступ к подмножеству колонок базовой таблицы;

3) доступ к подмножеству записей и колонок базовой таблицы;

4) доступ к записям, получаемых путем соединения двух и более базовых таблиц;

5) доступ к статистическим итогам данных в базовой таблице;

6) доступ к подмножеству другого представления или некоторой комбинации представлений и базовых таблиц.

В основе команды создания представления лежит оператор select. Синтаксис команды:

create view [[<имя БД>.] <владелец>.] <имя представления>

[(<имя колонки> [, <имя колонки>]…)]

as <select-предложение>

[with check option]

Пример:

create view pub_view (publisher, city, state)

as select pub_name, city, state from publishers

Существует несколько ограничений на использование select-предложения в создании представления:

1) нельзя использовать структуры order by и compute;

2) нельзя использовать слово into;

3) нельзя ссылаться на временную таблицу.

Структура with check option используется в тех случаях, когда данное представление будет использоваться командами insert и update. При этом будут контролироваться вводимые и обновляемые записи на соответствие select-предложению в представлении.

Существуют ограничения применения операторов изменения данных (update, insert и delete рассматриваются в следующем разделе) к представлениям:

1) нельзя применять эти операторы к колонкам представления, значения которых подсчитываются с помощью агрегатных или встроенных функций;

2) нельзя применять эти операторы к представлениям, использующим агрегатные функции и структуру group by;

3) нельзя применять эти операторы к представлениям, использующим distinct;

4) нельзя использовать insert для представлений, построенных на таблицах с колонками not null, когда в эти колонки попадают null значения;

5) нельзя использовать delete для представлений, построенных на нескольких таблицах;

6) нельзя использовать insert для представлений, построенных на нескольких таблицах с опцией with check option;

7) нельзя применять insert и update в представлении, построенном на нескольких таблицах с опцией distinct;

8) нельзя использовать update для колонки identity.

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

drop view [[<имя БД>.] <владелец>.] <имя представления>

[, [[<имя БД>.] <владелец>.] <имя представления>]…

Пример:

drop view pub_view

Обновление данных

Понятие транзакции

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

Транзакции обеспечивают:

1) целостность данных при работе операторов манипулирования данными (insert, update и delete);

2) восстановление данных в случае сбоев.

По умолчанию, каждая из команд insert, update и delete рассматривается как одна транзакция.

Каждая определяемая пользователем транзакция, состоящая из операторов языка SQL, начинается командой begin transaction (начать транзакцию), а заканчивается либо командой commit transaction (выполнить транзакцию), либо командой rollback transaction (отменить транзакцию).

Понятие пакета

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

Оператор INSERT рассматривался выше при описании загрузки таблиц БД.

Пример:

insert poss values

(534305, 'ЖУРАВЛЕВ АВЕРЬЯН АЛЕКСЕЕВИЧ', '000073', 'M', '0', '001001', '05', 0129000, '08', '90', '03','95')

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

delete [from][[<имя БД>.]<владелец>.]{<имя таблицы>|<имя представления>}

[from][[<имя БД>.]<владелец>.]{<имя таблицы>|<имя представления>}

[,[[<имя БД>.<владелец>.]<имя таблицы>|<имя представления>]]...]

[where <условие поиска>]

Пример:

delete poss where nomer = 534305

удаление из таблицы poss записи с данными о Журавлеве Аверьяне Алексеевиче (см. предыдущий пример).