Функции преобразования одних типов данных в другие и форматирование дат
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 записи с данными о Журавлеве Аверьяне Алексеевиче (см. предыдущий пример).
Оператор TRUNCATE позволяет быстро удалить все записи из таблицы.
truncate table [[<имя БД>.] <владелец>.] <имя таблицы>
Пример: truncate table poss
Оператор UPDATE служит для внесения изменений в записи таблиц БД.
update [[<имя БД>.]<владелец>.] {<имя таблицы>|<имя представления>}
set [[[<имя БД>.]<владелец>.]{<имя таблицы>.|<имя представления>.}]
<имя кол.1>={<выр.1> | null | (<предложение select>)}
[,<имя кол.2>={<выр.2> | null | (<предложение select>)}]...
[from [[<имя БД>.] <владелец>.]{<имя таблицы>|<имя представления>}
[,[[<имя БД>.]<владелец>.]{<имя таблицы>|<имя представления>}]]...
[where <условие поиска>]
Пример: Студент Иванов Иван Петрович был переведен из МЭИ в МГУ:
update poss set vuz_k=2066426
where fio='Иванов Иван Петрович' and vuz_k=2066414
где 2066414 - код МЭИ,
2066426 - код МГУ.
Лекция № 14