Язык SQL (на примере диалекта Informix)

Язык SQL
Основные типы данных. Синтаксис и семантика основных операторов. Методика обеспечения многопользовательского доступа к данным. Операторы с расширенными возможностями. Интеграция языка SQL и универсальных систем программирования.
5.1. Типы данных, доступные в SQL
При анализе предметной области нам надо решить вопрос, каким типом данных будет представляться тот или иной атрибут. Выбор типа влияет на суммарный объем базы данных, скорость поиска, допустимые с этим атрибутом операции и т.д. То есть, выбирая тип данных для того или иного атрибута, Вы должны учитывать следующее:-какие значения может принимать тот или иной атрибут;-какие операции будут выполняться с данным атрибутом;-сколько физического места займет одно значение для атрибута.
Прежде всего, следует бороться с "очевидностью". Например, в банковском деле имеется термин "номер счета". Сразу возникает желание использовать для данного атрибута тип "целое". Но может оказаться, что номер счета формируется достаточно специфично - например, он состоит из 9 цифр, средние 3 которых определяют категорию счета. И если в Вашей задаче требуется осуществлять поиск счетов той или иной категории, то реализовать этот поиск для типа "целое" очень непросто, а для типа "символьная строка" - достаточно просто. И, возможно, использовать тип "символьная строка" для данного атрибута предпочительнее, чем тип "целое".
В варианте SQL, реализованным фирмой Informix, имеются следующие типы данных:
INTEGER и SMALLINT - целое и короткое целое. Для их представления используется, соответственно, 4 и 2 байта.
Следовательно, диапазон допустимых значений для INTEGER будет от -2 147 483 647 до 2 147 483 647, а для SMALLINT - от -32 767 до 32 767. Данный тип используется для представления счетчиков, кодов чего-либо и т.д. К недостаткам данного типа данных следует отнести ограниченный набор значений. Впрочем, это не проблема, если Вы уверены, что для Вашего поля допустимый диапазон значений данного типа подходит.
SERIAL - этот тип данных основан на типе INTEGER, но имеет одну очень важную особенность. Этот тип предназначен для создания и хранения уникального ключа для записей в таблице. Только одно поле в таблице может иметь тип SERIAL. Когда Вы вставляете в таблицу, содержащую поле данного типа, новый ряд, то СУБД автоматически выберет для Вас новое уникальное значение данного типа. Вы можете получить это сгенерированное значение и использовать его в дальнейшей работе.
FLOAT и SMALLFLOAT - типы для представления нецелых чисел. Обычно поля данного типа используются для представления научных, экспериметальных, статистических величин. Внутреннее представление значений данного типа состоит из мантиссы и порядка. То есть значения данного типа могут лежать в очень широком диапазоне, но точными будут только несколько цифр.
Объем памяти (число байтов), выделяемых для хранения значений данного типа зависит от компьютера, но обычно составляет 8 байт для типа FLOAT и 4 байта для SMALLFLOAT. При таком представлении точность типа FLOAT составляет 16 десятичных цифр, а SMALLFLOAT - 8 цифр.
Типы FLOAT и SMALLFLOAT имеют одну особенность - из-за внутреннего двоичного представления нельзя гарантировать, что значения данного типа будут представлены точно. Поэтому данный тип нельзя использовать для хранения денежных сумм или других величин, которые нельзя округлять. Для хранения "неокругляемых" значений используются другие типы данных - DECIMAL и MONEY.
DECIMAL(p) - этот тип аналогичен FLOAT, но предназначен для хранения данных с фиксированным числом значащих цифр. Например, если Вы знаете, что некоторое значение всегда измеряется с точностью до пяти знаков, то это как раз тот тип данных, который Вам нужен. Чмсло значащих цифр (параметр 'p') может быть в пределах от 1 до 32. Диапазон допустимых значений для данного типа находится в пределах от 10^-128 до 10^126.
Для представления данных типа DECIMAL(p) используется двоично-десятичное представление. Следовательно, число байтов, необходимое для представления одного значения типа DECIMAL(p) будет равно 1+p/2.
По сравнению с типом FLOAT тип DECIMAL(p) имеет следующие преимущества:
- можно регулировать точность представления;
- размер требуемой памяти зависит от точности;
Но есть у типа DECIMAL(p) и недостатки по сравнению с типом FLOAT:
- операции сортировки и арифметические операции требуют большего, чем для типа FLOAT, времени;
- не все языки программирования поддерживают тип данных с фиксированной точностью, поэтому если SQL встроен, например, в C, то для преобразования типов требуется использовать специальные функции.
DECIMAL (p,n) - этот тип предназначен для хранения данных не только с фиксированным числом значащих цифр, но и с фиксированным числом значащих цифр после запятой. Параметр 'p' задает общее число десятичных цифр в числе, а 'n' - сколько десятичных цифр будет после запятой. Таким образом, данный тип данный позволяет хранить числа, в десятичном представлении которых не более 32 цифр. Размер памяти для хранения одного значения данного типа составляет 1+p/2 байт.
По сравнению с типами INTEGER и FLOAT тип DECIMAL(p,n) имеет те же достоинства и недостатки, что и тип DECIMAL(p).
MONEY (p,n) - этот тип предназначен для хранения денежных величин. Он полностью аналогичен типу DECIMAL(p,n), но отличается от него тем, что в некоторых языках (например, INFORMIX 4GL) существуют специальные способы форматирования для этого типа на основе некоторых внешних по отношению к программе переменных окружения. Это позволяет писать программы, не зависящие от способа написания денжных величин в каждой конкретной стране (например, величина 3000000 будет печататься или как $3,000,000.00 или как 3.000.000,00Рбл).
DATE - этот тип предназначен для хранения дат. По сути, значение этого типа представляет собой число дней, прошедших с 31 декабря 1899 года. Так как это значение может быть отрицательным, то можно хранить и даты до 1899 года. Под значения этого типа выделяется 4 байта, поэтому диапазон допустимых значений очень широк - около 58 000 столетий вперед и назад. Так же как и для типа MONEY, форматирование ввода и вывода для переменных данного типа может быть указано с помощью внешних переменных.
DATETIME - этот тип предназначен для хранения точных моментов времени. Тип DATETIME содержит информацию о годе (YEAR), месяце (MONTH), дне (DAY), часе (HOUR), минуте (MINUTE), секунде (SECOND) и долях секунды (FRACTION). Вы может выбирать нужный диапазон этих значений. Например, если Вам нужен момент времени с точностью до секунды в течении дня, то следует указать тип DATETIME HOUR TO SECOND. Если же Вам нужна информация о событиии с точностью до минуты, но в произвольном году, то требуемый тип должен записываться как DATETIME YEAR TO MINUTE. При указании долей секунды, надо указывать точность представления - от десятых до тысячных. Соответственно, FRACTION(1) указывает время с точностью до десятых, FRACTION(2) - до сотых, а FRACTION(3) - до тысячных.
По сравнению с типом DATE тип DATETIME может хранить данные более точно, однако он требует больше места для хранения и обрабатывается более медленно.
INTERVAL - тип данных для хранения временных интервалов. Значение типа INTERVAL получается, когда, например, из одной даты вычитают другую. Так же как и DATETIME, следует уточнить диапазон возможных значений.
CHAR и CHAR(n) - тип для хранения символьных строк фиксированной длины. Для типа CHAR(n) параметр 'n' задает длину строки. Тип CHAR без параметра является одиночным символом и идентичен типу CHAR(1). Максимальная длина данного типа (значение параметра 'n') равно 32 511. Для хранения данных данного типа всегда отводитя n байт вне зависимости от реальной длины строки.
VARCHAR(m) - короткая (до 255 символов) символьная строка переменной длины. Параметр 'm' задает максимальную длину строки, но не более 255. Для хранения строк переменной длины произвольного размера испльзуеся тип TEXT. При хранении значений типа VARCHAR используется столько байтов, сколько реально занимает строка.
По сравнению с типом CHAR(n), тип VARCHAR имеет то преимущество, что экономится место при хранении строк разной длины. Но такой способ хранения замедляет операции модификации данных.
TEXT - этот тип предназначен для хранения символьных строк произвольной переменной длины. По сравнению с типом VARCHAR тип TEXT имеет повышенные накладные расходы, но не имеет практического ограничения по длине, Например, для сервера Informix DS максимальная длина значения типа TEXT составяет 2 Гигабайта.
BYTE - тип для хранения двоичных объектов произвольного объема. Этот тип данных можно (и нужно) использовать для хранения исполняемых файлов, оцифрованных картинок, звука и т.д. Максимальная длина значения данного типа (для сервера Informix DS ) ограничена 2 Гигабайтами.
У каждого из перечисленных выше типов данных свои операции, свои значения. Но существует одно общее для всех типов значение ­NULL. Это значение "не определено". Не надо путать это значение с нулем. Значение "нуль" - это вполне определенное значение, а NULL означает, что никакого определенного значения в атрибут или в переменную никогда не записывалось или явно было присвоено значение NULL.
5.2. SQL-операторы создания схемы базы данных
Итак, Вы знаете, какие типы данных можно использовать. Вы провели анализ своей задачи, выделили атрибуты, определили ключи и решили, что Ваша база данных будет состоять из таких-то и таких-то таблиц. Каждая таблица будет содержать такие-то и такие-то поля. Другими словами, Вы разработали схему своей базы данных. Рассмотрим операторы языка SQL, которые позволяют создать на SQL-сервере нужную Вам схему базу данных.
Первым делом надо создать саму базу данных. Для этого используется оператор
CREATE DATABASE <имя базы>
В качестве имени базы данных Вы можете использовать любое имя, состоящее из латинских букв, цифр и символа подчеркивания. Имя должно начинаться с буквы или символа подчеркивания. Если используемые SQL-сервер и операционная система обеспечивают NLS (National Language Support - поддержка национальных языков)), или SQL-сервер поддерживает GLS (Global Language Support – глобальная поддержка языков), то Вы можете использовать для именования и национальные символы. Кстати, большие и маленькие буквы не различаются. Максимальная длина имени составляет 14 символов. Вы не можете создать на одном SQL-сервере две базы данных с одинаковым именем.
Пример:
CREATE DATABASE my_base
В SQL существует понятие "текущая база данных". После того, как Вы создали базу данных оператором CREATE DATABASE, она становится текущей. Все указываемые таблицы, все индексы берутся из текущей базы данных. В каждый момент времени для каждой задачи существует единственная текущая база данных (или таковой не существует вовсе). Это не означает, что Вы не можете обратиться к таблице из другой базы данных, просто при обращении к такой таблице надо кроме имени таблицы, еще указывать и имя базы данны, а если база данных расположена на другом сервере, то и имя сервера базы данных.
Сразу опишем и обратный оператор - оператор удаления базы данных
DROP DATABASE <имя базы>
Пример:
DROP DATABASE my_base
Естественно, удаляемая база должна существовать и Вы должны иметь право на ее удаление (но о правах несколько позднее). Кстати, Вы не можете удалить текущую базу.
Если база данных уже существует, то сделать ее текущей (открыть ее) можно оператором
DATABASE <имя базы>
Пример:
DATABASE my_base
Закрытие текущей базы данных производится оператором
CLOSE DATABASE
Итак, предположим, что база данных создана. Для того, чтобы создавать таблицы используется оператор CREATE TABLE. Его синтаксис
CREATE TABLE <имя таблицы> ( <имя колонки> <тип колонки> [ NOT NULL], <имя колонки> <тип колонки> [ NOT NULL] [, ...] )
То есть Вы указываете имя таблицы, а затем в скобках через запятую перечисляете названия полей и их тип. Если Вы хотите сказать серверу, что какое-то поле обязательно должно иметь какое-то определенное значение, то после типа надо добавить слова NOT NULL.
Например, для создания таблицы, содержащей уникальный код, название и адрес призводителей товара, надо выполнить оператор
CREATE TABLE companies ( company_id SERIAL, name CHAR(40) NOT NULL, address CHAR(60))
При создании данной таблицы мы явно указали, что поле name (название) не может быть неопределенным, но поле address (адрес) вполне может быть неопределено.
Теперь создадим таблицу для описания товаров. В ней надо указать ссылку на производителя, название товара, его стоимость:
CREATE TABLE items ( item_id SERIAL, company INTEGER NOT NULL, name CHAR(40) NOT NULL, price MONEY(20,2))
Таблицы 'companies' и 'items' связаны. Логическая связь обеспечивается полем 'company' в таблице 'items' и полем 'company_id' в таблице 'companies'. Поле 'company' данной таблицы содержит идентификатор поставщика. То есть если мы хотим найти поставщика некоторого товара, то надо из этой таблицы для нужной записи выбрать значение поля 'company', а затем, просмотрев таблицу 'companies' найти запись, поле 'company_id' равно найденному значению.
Оператор удаления таблицы очень похож на оператор удаления базы данных:
DROP TABLE <имя таблицы>
Если вдруг Вы ошиблись, либо изменились условия задачи, и Вам требуется поменять структуру таблицу, то вовсе не обязательно удалять старую таблицу и вместо нее создавать новую. Для модификации структуры существующей таблицы существует оператор ALTER TABLE, который позволяет изменять все то, что указывается при выполнении оператора CREATE TABLE.
Добавление новых полей в твблицу производится следующим образом:
ALTER TABLE <имя таблицы> ADD ( <имя колонки> <тип колонки> [ NOT NULL] [, ...] )
Например, если для поставщика товаров (таблица 'companies') надо добавить и контактный телефон, то следует выполнить следующий оператор:
ALTER TABLE companies ADD (phone CHAR(35))
Для удаления ненужных полей надо выполнить другой вариант оператора ALTER TABLE:
ALTER TABLE <имя таблицы> DROP ( <имя колонки> [, ...] )
Существует вариант этого оператора и для модификации поля:
ALTER TABLE <имя таблицы> MODIFY ( <имя колонки> <тип колонки> [ NOT NULL] [, ...] )
При изменении типа поля производится автоматическая преобразование значений к новому типу. В частности, если тип меняется с целого на символьное, то число 2385 будет преобразовано в строку '2385'.
5.3. Основные SQL-операторы для доступа и модификации данных
Итак, структура базы данных создана. Надо как-то начинать работать с базой данных - заносить новые данные, извлекать существующие и т.д. Существует четыре основных оператора манипулирования данными - SELECT, INSERT, UPDATE и DELETE. Примеры, которые быдут сопровождать рассказ об этих операторах будут основываться на предположении, что в нашей текущей базе данных есть таблицы companies и items (см. параграф 5). В таблице companies хранится информация о производителях товаров, а в таблице items - о самих товарах.
Здесь мы приведем несколько упрощенные формы этих операторов, а более полный синтаксис будет рассмотрен в одной из следующих статей. Рассмотрим каждый из этих операторов.
Оператор INSERT вставляет в таблицу новую запись:
INSERT INTO <имя таблицы>(<поле1>, <поле2>, ...) VALUES (<значение1>, <значние2>, ...)
После имени таблицы в скобках надо указать те поля, которым мы хотим присвоить некоторе значение явно. После ключевого слова VALUES в скобках указан список значений для перечисленных полей. Число значений в этом списке должно соответствовать числу указанных полей. Полям, не перечисленным в списке (за исключением поля типа SERIAL) присваивается значение NULL.
Полю типа SERIAL, если его нет в списке или его значением указано 0, присваивается новое уникальное значение. Если для поля типа SERIAL указано отличное от нуля значение, то СУБД использует указанное значение.
Например, последовательность операторов
INSERT INTO companies (name) VALUES ("АО Рога и Копыта")
INSERT INTO companies (name, address) VALUES ("ТОО Добро пожаловать", "Энск, 5-е авеню")
INSERT INTO companies (company_id, name) VALUES (157, "АОЗТ Сделай Сам")
наполнит таблицу companies следующим содержанием
------------------T----------------------T-----------------¬¦ company_id ¦ name ¦ address ¦+-----------------+----------------------+-----------------+ ¦ 1 ¦ АО Рога и Копыта ¦ NULL ¦¦ 2 ¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦¦ 157 ¦ АОЗТ Сделай Сам ¦ NULL ¦L-----------------+----------------------+-----------------+
Кроме констант для задания значений Вы можете использовать и и выражения. Выражения бывают строковые, арифметические, типа DATE и т.д. Иногда очень полезными оказываются встроенные функции. Перечислим некоторые из этих функций:
USER - имя пользователя, который выполняет этот SQL-оператор;
TODAY - дату выполнения этого оператора;
CURRENT - момент времени, когда выполняется этот оператор.
Например, если некоторая таблица под названием 'protocol' содержит описание некоторых действий и, в частности, поля when и who, указывающие когда и кто выполнил это действие, занесение новой записи в эту таблицу будет выглядеть так:
INSERT INTO protocol (who, when, ...) VALUES (USER, CURRENT YEAR TO MINUTE, ...)
Для модификации записей, которые уже есть в таблице, используется оператор UPDATE:
UPDATE <имя таблицы> SET <имя поля>=<значение> [,....] [WHERE <условие>]
или
UPDATE <имя таблицы> SET (<имя поля1>, <имя поля2>, ...) = (<значение1>, <значение2>, ...) [WHERE <условие>]
Эти два варианта оператора UPDATE отличаются только синтаксически: в первом варианте явно указывается колонка и сразу после нее и знака '=' пишется новое значение, а во втором варианте модифицируемые поля и их новые значение сгруппированы по отдельности. Мы, в основном, будем пользоваться первым вариантом, как наиболее защищенным от случайных ошибок.
В обоих случаях может использоваться ключевое слово WHERE, которое определяет записи, подлежащие модификации. Для каждой записи из таблицы это условие будет проверено, и, только если условие будет истинным, к записи будет применен оператор UPDATE. Если слово WHERE вообще не указано, то есть не определено условие обработки записей, то оператор UPDATE будет применен ко всем записям в таблице.
В качестве условия могут использоваться логические выражения над константами и полями. В логических выражениях допускается испльзовать операции сравнения >, <, >=, <=, =, <>, !=. Для проверки поля на значение NULL используются логические операции IS NULL или IS NOT NULL. Отдельные логические операции могут быть соединены связками AND, OR, NOT и сгруппированы с помощью скобок. Примеры правильных условий:
name IS NULL
price > 200 OR name = "кеды"
(name IS NULL AND address IS NULL) OR (name = "АО Рога и Копыта")
Использование в SQL логических связок и операций сравнения аналогично логическим выражениям в обычных языках программирования. Представляет интерес использование в операциях сравнения и в логических связках значения NULL (неопределено). Если NULL появляется в операциях сравнения (<, >, <= и т.д.), то результатом этой операции тоже будет NULL. Если применить к NULL операцию отрицания (NOT), то снова получим NULL. Логическое умножение (AND) значений NULL и "ложь" дает "ложь", а NULL и "истина" - NULL. Логическое сложение (OR) значений NULL и "ложь" дает в результате NULL, а NULL и "истина" - "истину".
Например, если на момент вставки записи с информацией о фирме "АО Рога и Копыта" мы не знали ее адреса, то записать ее адрес после можно оператором:
UPDATE companies SET address = "Одесса, п/я 13" WHERE name = "АО Рога и Копыта"
Далее, если мы хотим поднять минимальную цену на товары до 1000, следует выполнить оператор:
UPDATE items SET price = 1000 WHERE price < 1000
Теперь, немного забегая вперед (мы еще не рассматривали оператор SELECT), напишем оператор UPDATE, который увеличивает в два раза цену на все товары, поставляемые фирмой "АОЗТ Сделай Сам":
UPDATE items SET price = price*2 WHERE company = (SELECT company_id FROM companies WHERE name = "АОЗТ Сделай Сам")
Это пример двойного запроса, то есть запроса в запросе. Вначале ищется идентификатор фирмы по ее имени (оператор SELECT), а затем обновляется поле 'price' для всех товаров, поставляемых данной фирмой.
Для того, что бы удалить ненужные записи в таблице, существует оператор DELETE:
DELETE FROM <имя таблицы> [WHERE <условие>]
Использование ключевого слово WHERE, задающего условие на записи, подлежащие удалению, аналогично его использованию в операторе UPDATE. Точно так же, если условие не задано, то удалены будут все записи из таблицы.
Предположим, фирма "АОЗТ Сделай Сам" разорилась, больше не поставляет нам ни одного товара, и мы хотим удалить ее из списка поставщиков. Это может быть сделано оператором:
DELETE FROM companies WHERE name = "АОЗТ Сделай Сам"
Теперь рассмотрим оператор выборки SELECT. На его долю приходится, наверное, более 2/3 всех SQL-операторов используемых в программах. Оператор SELECT выбирает из одной или нескольких таблиц множество значений, которое и является результатом его работы. К сложным, многотабличным вариантам этого оператора мы вернемся позже.
Синтаксис оператора SELECT (сильно упрощенный вариант):
SELECT <имя поля> [,...] FROM <имя таблицы> [WHERE <условие>]
В операторе SELECT указываются нужные Вам поля, имя таблицы (FROM), из которой производится выборка, и условие (WHERE), которому должны удовлетворять отобранные значения. Оператор SELECT просматривает все записи в таблице и отбирает те, которые удовлетворяют условию. Из отобранных записей берутся только те поля, которые указаны. Таким образом, результатом работы оператора SELECT будет набор значений, фактически таблица, содержащая часть полей и часть записей от исходной. В предельных случаях это может быть пустое множество (ни одна запись не удовлетворила условию) или одно единственное значение, если выбиралось одно поле и только одна запись удовлетворила условию.
Примеры:
SELECT company_id, name, address FROM companies
- результатом этого запроса будет вся таблица 'companies'.
SELECT company_id FROM companies WHERE name = "АО Рога и Копыта"
- этот запрос возвращает одно единственное значение (1), а именно уникальный идентификатор фирмы "АО Рога и Копыта".
SELECT name FROM companies
- возвращает имена всех фирм-поставщиков, имеющихся в нашей базе данных и занесенных в таблицу 'companies'.
SELECT name, price FROM items WHERE company = (SELECT company_id FROM companies WHERE name = "АО Рога и Копыта")
- возвращает все названия товаров и их цены, поставляемые фирмой "АО Рога и Копыта".
SELECT name FROM companies WHERE address IS NULL
- возвращает названия тех фирм, у которых неизвестен адрес.
5.4. Управление транзакциями
Транзакцией называется последовательность действий, которая должна либо быть полностью выполнена, либо ни одно действие из нее не должно быть выполнено (все или ничего). Например, транзакцией следует считать совокупность двух действий: снятие денег с одного счета и занесение их на другой счет. То есть не должно получаться так, чтобы при переводе денег с одного счета на другой с одного счета деньги бы уже были сняты, а на второй - не переведены (питание выключилось как раз между этими событиями).
В SQL имеются следующие операторы управления транзакциями: ·BEGIN WORK - начать транзакцию;·COMMIT WORK - успешно завершить транзакцию;·ROLLBACK WORK - откатить транзакцию, то есть вернуть базу данных в состояние, которое она имела на момент начала текущей транзакции.
Транзакции не могут быть вложеными, то есть в каждый момент времени для данного приложения существует одна (точнее, не более одной) текущая транзакция.
Давайте разберемся, кто и когда может и должен управлять транзакциями. Все современные СУБД выполнены по схеме клиент-сервер. Это означает, что существует программа-сервер, которая умеет выполнять SQL-запросы. Другая программа, программа-клиент, формирует эти запросы на основании интерфейса с пользователем и посылает их программе-серверу, получает от нее результаты и выводит их на экран. Программа-сервер и программа клиент могут находиться как на одном компьютере и обмениваться информацией по некоторому внутреннему мехаинзму (например, через прерывания), так и на разных и обмениваться данными по сетевому протоколу.
Операторы управления транзакциями выполняет программа-клиент. Если она обнаруживает какую-то ошибку, то она может выполнить откат транзакции. В самом SQL нет управляющих структур (циклов, условных операторов, операторов перехода), поэтому приведем примеры на языке Informix 4GL, который содержит SQL как подмножество:
IF error { проверка какого-то события }THEN ROLLBACK WORK { откат транзакции в случае ошибки } ELSE COMMIT WORK { успешное завершене транзакции } END IF
Но если программа-клиент функционирует нормально, то особых проблем и нет - любой откат можно запрограммировать. Поддержка механизма транзакций обеспечивает откат транзакци в случае внезапного нарушения в работе как программы-клиента, так и программы-сервера.
Представим себе, что на компьютере, где исполняется программа-клиент, то есть, где работает пользователь, пропало напряжение. Программа-сервер обнаруживает "гибель" клиента и выполняет оператор ROLLBACK WORK сама, без явного указания от клиента.
Если же питание пропало на сервере, то есть там, где хранятся данные, то программа-клиент получает соответствующие уведомление и, если не принять особых мер, прекращает свою работу. При последующем запуске программы-сервера (при перезагрузке сервера), SQL-сервер определяет, что последняя транзакция не была завершена и опять сам выполняет откат транзакции.
Для того, чтобы SQL-сервер знал, что для данной базы данных надо отслеживать транзакции, то надо сказать ему об этом при создании базы. Для создания базы данных с транзакциями SQL предоставляет следующие варианты оператора CREATE DATABASE:
CREATE DATABASE <имя базы> WITH LOG
- создание базы данных с транзакциями, и
CREATE DATABASE <имя базы> WITH LOG MODE ANSI
- создание базы данных с транзакциями в режиме ANSI.
Если база данных была создана с параметром WITH LOG, то сервер начинает для данной базы данных отслеживать транзакции. Выполнение оператора COMMIT WORK, говорит серверу о начале транзакции, которая закончится по оператору COMMIT WORK или откатится по оператору ROLLBACK WORK в состояние на момент выполнения BEGIN WORK.
BEGIN WORK; { Явно задаем начало транзакции }DELETE .......; { некоторые изменения }INSERT .......; { данных }COMMIT WORK; { Оператор успешного завершения транзакции }
Если база данных была создана в режиме ANSI, то есть в операторе CREATE DATABASE было указано WITH LOG MODE ANSI, то оператор BEGIN WORK не нужен. Транзакция открывается автоматически сразу после удачного или неудачного завершения предыдущей. Открытие базы данных автоматически открывает транзакцию.
{ транзакция для ANSI-базы данных }COMMIT WORK; { завершение предыдущей транзакции }{ Здесь автоматически начинается следующая транзакция}UPDATE .......; { некоторые изменения }INSERT .......; { данных }COMMIT WORK; { успешное программное завершение транзакции } { Здесь автоматически начинается следующая транзакция}
Не все операторы отслеживаются механизмом транзакций. Например, созданная внутри транзакции таблица не будет удалена при откате транзакции. В транзакциях отслеживаются только модификации данных, но не изменение схемы.
5.5. Продвинутые варианты оператора поиска
5.5.1. Поиск по нескольким таблицам
Оператор SELECT может осуществлять поиск сразу по нескольким таблицам. В этом случае после слова FROM в операторе SELECT надо указать таблицы,по которым производится поиск. Если в нескольких полях имеются одноименные поля (например, поле name присутсвует и в таблице companies, и в таблице items), то для устранения неясностей надо перед именем поля указать имя таблицы и символ точка.
Например, если мы хотим получить список всех людей, информация о которых есть в базе, вместе с названием фирмы, в которой они работают, то надо выполнить запрос
SELECT name, address, lname, fname FROM companies, persons WHERE company_id = company
В данном примере не возникает коллизий и запрос будет правильно отработан. Но если мы захотим выдать список товаров и названий фирм, их производящих, то, так как поле name присутствует и в таблице items, и в таблице companies, нам потребуется для поля name указывать таблицу:
SELECT items.name, companies.name FROM persons, companies WHERE company = company_id
Впрочем, указывать при имени поля имя таблицы можно всегда, даже тогда, когда никаких конфликтов не возникает.
Стоит обратить внимание, что при выборке из двух таблиц мы получаем все возможные комбинации значений, при этом из всех таблиц выбираются только те записи, которые удовлетворяют заданному условию. Например, в приведенном выше примере на получение информации о фирмах и людях, в них работающих, мы получим список только тех компаний, для которых в таблице persons есть хотя бы один человек. А если мы хотим в этом запросе получить информацию и о фирмах, для которых мы не знаем ни одного сотрудника? Для этого в разделе FROM оператора SELECT перед таблицей persons надо поставить слово OUTER:
SELECT name, address, lname, fname FROM companies, OUTER persons WHERE company_id = company
Тогда такой оператор будет работать следующим образом. Он последовательно будет перебирать все записи из таблицы companies. Для каждой записи из companies будет просмотрена таблица persons. Как только будет найдена запись из persons, удосвлетворяющая условию WHERE, в результат добавится новый ряд, сформированный из полей name и address записи из companies и полей lname и fname записи из persons. Если же в persons не будет найдено ни одной записи, то при простом операторе SELECT информация о текущей записи из companies в результат не попадет, а в случае SELECT с OUTER - попадет, а поля lname и fname получат значение NULL:
Таблица companies: -------------T----------------------T------------------¬¦ company_id ¦ name ¦ address ¦+------------+----------------------+------------------+¦ 101 ¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦¦ 105 ¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦¦ 107 ¦ АОЗТ Сделай сам ¦ Городская свалка ¦L------------+----------------------+-------------------
Таблица persons:----------T----------T---------¬¦ company ¦ lname ¦ fname ¦+---------+----------+---------+¦ 101 ¦ Антонов ¦ Сергей ¦¦ 105 ¦ Шапокляк ¦ Алексей ¦¦ 102 ¦ Антонов ¦ Антон ¦¦ 101 ¦ Бендер ¦ Остап ¦L---------+----------+----------
Результат простого оператора SELECT
SELECT name, address, lname, fname FROM companies, persons WHERE company_id = company
-----------------------T-----------------T----------T---------¬ ¦ name ¦ address ¦ lname ¦ fname ¦+----------------------+-----------------+----------+---------+ ¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦ Антонов ¦ Сергей ¦¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦ Бендер ¦ Остап ¦¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦ Шапокляк ¦ Алексей ¦ L----------------------+-----------------+----------+----------
Результат оператора SELECT с указателем OUTER
SELECT name, address, lname, fname FROM companies, OUTER persons WHERE company_id = company
-----------------------T-----------------T----------T---------¬ ¦ name ¦ address ¦ lname ¦ fname ¦+----------------------+-----------------+----------+---------+ ¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦ Антонов ¦ Сергей ¦¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦ Бендер ¦ Остап ¦¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦ Шапокляк ¦ Алексей ¦ ¦ АОЗТ Сделай сам ¦ Городская свалка¦ NULL ¦ NULL ¦L----------------------+-----------------+----------+----------
Стоит или не стоит использовать OUTER - зависит от того, что Вы хотите получить в результате оператора SELECT. Относительно приведенного Выше примера, то если бы нас интересовали именно люди, то следовало бы выбрать оператор SELECT в первом варианте, без OUTER. Если же нам было интересно получить информацию о фирмах, а заодно и узнать кто там работает, то следует выбрать второй вариант - c OUTER.
Действие, производимое оператором SELECT при выборке из двух таблиц называют слияние таблиц (join), а если производится слияние с OUTER, то такое слияние таблиц называется внешним (outer join). Внутри одного запроса можно использовать несколько таблиц с OUTER (кстати, такие таблицы называют подчиненными, а таблицы без OUTER - ведущими).
5.5.2. Устранение повторения данных в операторе SELECT
Отношение или таблица- это множество. Следовательно, в них могут содержаться повторяющиеся элементы (одинаковые записи). И если в реальных таблицах это практически никогда не встречается (ведь надо же как-то различать описания разных сущностей!), то в отношении, которое является результатом работы оператора SELECT такие повторения могут возникать достаточно часто. Если в формируемой с помощью оператора SELECT отношении Вам надо избавиться от повторений, то перед списком полей следует поставить ключевое слово DISTINCT или UNIQUE (это синонимы). В этом случае, если в выборке, удовлетворяющей условию WHERE, имеется несколько записей с одинаковыми значениями, то оператор SELECT исключит повторения. Например, если Вам требуется распечатать только номенклатуру поставляемых товаров, без учета того, что один и тот же вид продукта может пставляться разными поставщиками, Вам следует выполнить запрос:
SELECT UNIQUE name FROM items
Если в запросе выбирается два или более поля, то две записи считаются равными, если равны все их составляющие поля.
5.5.3. Вычисления внутри оператора SELECT
Оператор SELECT можно использовать не просто для выборки каких-то значений из одной или нескольких таблиц, но и для осуществления каких-то действий над выбираемыми значениями. Это могут быть арифмеические или строковые операции над полями, сортировка и т.д.
Выражения над полями
Оператор SELECT может производить сразу вычисления. Над отдельными полями можно производить символьные иарифметические операции, а также функции. В вражениях можно использовать как значения полей, так и константы.
К символьным операциям относится конкатенация двух строк и выделение подстроки. Операция конкатенации записывается в виде двух вертикальных черт: '||'. Операция выделения подстроки записывается с помощью квадратных скобок, в которых через запятую указан номер первого и последнего символа в подстроке. Рассмотрим примеры:
SELECT lname[4,9] FROM companies
"Рога и Ко"" Добро по""Т Сделай "
SELECT lname || " " || fname FROM persons
"Антонов Сергей""Шапокляк Алексей""Антонов Антон""Бендер Остап"
Обратите внимание, что в последнем примере результат - это четыре ряда, каждый из которых состоит из одной строки. Если бы мы выполнили запрос
SELECT lname, fname FROM persons
то получили бы четыре ряда по два строковых значения в каждом.
В последнем примере мы также использовали и текстовую константу - строку из одного пробела, заключенную в кавычки. Можно использовать строковые константы любой длины и состоящие из любых символов. Если внутри константы надо использовать символ "кавычки", то следует написать его подряд дважды. В символьных выражениях Вы можете использовать и не символьные выражения ­операция преобразования типов (например, числа 1234 в строку "1234") будет произведена автоматически.
К арифметическим операциям относятся операции сложения ('+'), вычитания ('-'), умножения ('*') и деления ('/'). Пример:
SELECT price*1.2 FROM items
SELECT tax*quantity/1.2 FROM orders
В выражениях внутри оператора SELECT имеется возможность вызывать функции. Некоторые доступные функции приведены в табличке.
Функция определения длины:LENGTH(<имя поля>) - вычисляет длину строки, котораяLENGTH(<текстовая строка>) содержится в указанном поле записи или указанной текстовой констант;
Временные функции:DATE (<выражение не типа DATE>) - преобразует выражение типа CHAR, INTEGER или DATETIMEв значение типа DATE;DAY(<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер дня в месяце;MDY(<месяц>, <день>, <год>) - по заданным трем целым значением, определяющим номер месяца, дня, года строится значение типа DATE;MONTH (<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер месяца в годе;WEEKDAY (<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер дня недели, причем восвкресенью соответсвует значение 0, понедельнику - 1 и т.д.;YEAR (<<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер года, состоящий из 4-х цифр;
Функции преобразованияHEX (<целое выражение>) - преобразует целое, заданное в качестве аргумента, в строку, представляющую шестнадцатеричную запись данного целого;ROUND (<числовое выражение, точность>) - округляет с указанной точностью числовое выражение, заданное в качестве аргумента; точность определяет степень одной десятой, до которой надо округлять.TRUNC (<числовое выражение, точность>) - обрезает незначащие цифры у заданного в качестве аргумента выражения, причем точность определяет смещение влево от запятой последней значащей цифры;
Математические функции:ACOS(<числовое выражение>) - арккосинус от заданного значения; ASIN(<числовое выражение>) - арксинус от заданного значения;ATAN (<числовое выражение>) - арктангенс от заданного значения; COS(<числовое выражение>) - косинус от заданного числа радиан; SIN(<числовое выражение>) - синус от заданного числа радиан;TAN (<числовое выражение>) - тангенс от заданного числа радиан; ABS (<числовое выражение>) - абсолютное значение;EXP (<степень>) - вычисление заданной экспоненты;LOGN (<выражение>) - вычисление натурального логарифма; LOG10 (<выражение>) - вычисление десятичного логарифма; MOD (<делимое>, <делитель>) - остаток от деления;POW (<значение>, <степень>) - возведение в степень; ROOT (<значение>, <степень>) - вычисление корня заданной степени; SQRT (<числовое выражение>) - квадратный корень;
Функции – псевдополяCURRENT <диапазон> - возвращает точный момент времени (тип DATETIME), когда происхoдит обработка данного запроса; параметр <диапазон> задает (как и при описании типа DATETIME) требуемое точность - от года до тысячных секунды (например, CURRENT DAY TO SECOND);TODAY - возвращает дату (тип DATE) выполнения даного запроса;USER - возвращает имя пользователя (как он зарегестрировался в операционной системе), который выполняет данный запрос.
Следует напомнить про специальное значение NULL - "не определено". Если это значение встретится в выражении, то и все выражение будет иметь значение NULL. Даже умножив нуль на NULL, Вы все равно получите NULL. Конкатенация любой строки с неопределенной строкой (то есть строкой, имеющей значение NULL) тоже в результате даст NULL.
В выражениях можно использовать и скобки. С их помощью Вы имеете возможность управлять порядком вычисления внутри выражения.
Агрегатные функции
Рассмотренные в предыдущем пункте функции производили действия над отдельным полем одной записи из выборки. Агрегатные функции позволяют производить вычисления над совокупностью одних и тех же полей сразу в нескольких записях выборки. Например, если мы хотим определить максимальную цену на товар из нашей базы данных, надо выполнить запрос
SELECT MAX(price) FROM items
В этом запросе будут просмотрены все записи из таблицы items и будет определено максимальное значение среди всех полей price.