Выражения со значениями даты-времени и интервальными значениями

Выражения со значениями даты-времени, оказывается, работают (кто бы мог подумать!) со значениями даты и времени. В таких выражениях могут появляться данные типа DATE, TIME, TIMESTAMP и INTERVAL. Результатом выполнения выражений со значениями даты-времени всегда является другое значение даты-времени. К значению этого типа можно прибавить (или отнять от него) какой-либо интервал, а также задать часовой пояс.

Вот пример выражения со значениями даты-времени (название DUE_DATE означает "срок возврата", a INTERVAL 7' DAY – "интервал в 7 дней"):

DUE_DATE + INTERVAL '7' DAY

Такое выражение можно использовать в библиотечной базе данных, чтобы определять, когда отправить напоминание "должникам". А вот новый пример, где, правда, указывается не дата, а время:

TIME '18:55:48' AT LOCAL

Ключевые слова AT LOCAL означают, что указано местное время.

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

Приведем пример использования интервалов. Скажем, кто-то возвращает в библиотеку книгу после истечения крайнего срока. Тогда, используя выражение со значениями интервалов, такое, например, как приведено в следующем примере, можно вычислить, сколько прошло дней задержки после крайнего срока, и соответственно назначить пеню (названия DATE_RETURNED и DATE_DLIE означают соответственно "дата возврата" и "крайний срок"):

(DATE_RETURNED-DATE_DUE) DAY

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

Выражения с логическими значениями

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

(CLASS = SENIOR) IS TRUE

Если это условие выбора строк из таблицы со списком учеников-старшеклассников, то будут выбраны только записи, соответствующие ученикам выпускных классов. А чтобы выбрать записи учеников других классов, можно использовать следующее выражение:

NOT (CLASS = SENIOR) IS TRUE

To же самое условие можно выразить и по-другому:

(CLASS = SENIOR) IS FALSE

Чтобы получить все строки, имеющие в столбце CLASS неопределенное значение, используйте:

(CLASS = SENIOR) IS UNKNOWN

Выражения со значениями, определяемыми пользователем

О типах, определяемых пользователями, см. в главе 2. Благодаря такой возможности пользователи могут определять собственные типы данных, а не довольствоваться теми, которые есть на "складе" SQL. Если есть выражение, имеющее элементы данных какого-либо типа, определяемого пользователем, то значением этого выражения должен быть элемент того же типа.

Выражения со значениями типа записи

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

('Джозеф Тайкосинер', 'заслуженный профессор в отставке', 1918)

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

Выражения со значениями типа коллекции

Значением выражения типа коллекции является массив.

Выражения со значениями типа ссылки

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

Предикаты

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

"Ученик учится в выпускном классе".

В таблице, содержащей информацию об учениках, домен столбца CLASS (класс) может быть набором таких значений: SENIOR (выпускной), JUNIOR (предпоследний), SOPHOMORE (второй старший класс), FRESHMAN (первый старший класс) и NULL (неизвестен). Предикат CLASS = SENIOR можно использовать для отсева тех строк, для которых его значение ложно, оставляя, соответственно, только те строки, для которых значение этого предиката истинно. Иногда в какой – либо строке значение этого предиката не известно (т.е. представляет собой NULL). В таком случае строку можно отбросить или оставить в зависимости от конкретной ситуации.

CLASS = SENIOR – это пример предиката сравнения. В SQL имеется шесть операторов сравнения. В простом предикате сравнения используется только один из этих операторов. Предикаты сравнения и примеры их использования приведены в таблице 3.3.

Таблица 3.3. Операторы и предикаты сравнения.

Оператор	Сравнение	Выражение
=	Равно	CLASS = SENIOR
<>	Не равно	CLASS <> SENIOR
<	Меньше	CLASS < SENIOR
>	Больше	CLASS > SENIOR
<=	Меньше или равно	CLASS <= SENIOR
>=	Больше или равно	CLASS >= SENIOR

Внимание:
В последнем примере только первые два выражения имеют смысл (CLASS = SENIOR и CLASS <> SENIOR). Это объясняется тем, что SOPHOMORE считается больше, чем SENIOR, потому что в последовательности сопоставления, установленной по умолчанию (т.е. когда сортировка выполняется по алфавиту), SO следует после SE. Однако такая интерпретация, по всей вероятности, – не то, что вам нужно.

Логические связки

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

"Ученик учится в выпускном классе"."Ученику еще нет 14 лет".

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

CLASS = SENIOR AND AGE < 14

Если используется связка AND, то чтобы составной предикат был истинным, истинными должны быть оба входящих в него предиката. А если нужно, чтобы составной предикат был истинным тогда когда истинный какой-либо из входящих в него предикатов, то используйте логическую связку OR (или). Третьей логической связкой является NOT (отрицание). Строго говоря, эта связка не соединяет два предиката. Она применяется к единственному предикату и заменяет его логическое значение на противоположное. Возьмем, например, следующее выражение:

NOT (CLASS = SENIOR)

Это значение истинно только тогда, когда значение CLASS на самом деле не равно SENIOR.

Итоговые функции

Иногда информация, которую вы хотите получить из таблицы, не связана с содержимым отдельных строк, но относится к данным таблицы, взятым в целом. Для таких ситуаций стандарт SQL: 2003 предусматривает пять итоговых функций: COUNT, MAX, MIN, SUM и AVG Каждая из этих функций выполняет действие по получению данных, относящихся к множеству строк, а не только к одной.

COUNT

Функция COUNT возвращает число строк указанной таблицы. Чтобы в базе данных сред. ней школы, используемой в качестве примера, подсчитать число самых юных учеников выпускных классов, воспользуйтесь следующим оператором (название GRADE означает "класс"):

SELECT COUNT (*)FROM STUDENTWHERE Grade =12 AND AGE < 14;

MAX

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

SELECT FirstName, LastName, AgeFROM STUDENTWHERE Age = (SELECT MAX(Age) FROM STUDENT);

В результате появляются данные по всем старшим ученикам, т.е. если возраст самого старшего ученика равен 23 годам, этот оператор возвращает данные по всем ученикам с возрастом 23 года.

В этом запросе используется подзапрос. Этот подзапрос, SELECT MAX(Age) FROM STUDENT, находится внутри главного запроса.

MIN

Функция MIN работает точно так же, как и МАХ, за исключением того, что MIN ищет в указанном столбце не максимальное, а минимальное значение. Чтобы найти самых юных учеников школы, можно использовать следующий запрос:

SELECT FirstName, LastName, AgeFROM STUDENTWHERE Age = (SELECT MIN(Age) FROM STUDENT);

В результате появляются данные по самым младшим ученикам вашей школы.

SUM

Функция SUM складывает значения из указанного столбца. Столбец должен иметь один из числовых типов данных, а значение суммы не должно выходить за пределы диапазона, предусмотренного для этого типа. Таким образом, если столбец имеет тип данных SMALLINT, то полученная сумма не должна превышать верхний предел, имеющийся у этого типа данных. В таблице INVOICE (счет-фактура) из базы данных, о которой уже говорилось в этой главе, хранятся данные по всем продажам. Чтобы найти общую сумму в долларах для всех продаж, иные которых хранятся в базе, используйте функцию SUM следующим образом:

SELECT SUM(TotalSale) FROM INVOICE;

AVG

Функция AVG возвращает среднее арифметическое всех значений указанного столбца. Функция SUM, AVG применяется только к столбцам с числовым типом данных. Чтобы найти среднее арифметическое значение продаж, учитывая все финансовые операции, хранящиеся в базе, используйте функцию AVG следующим образом:

SELECT AVG(TotalSale) FROM INVOICE;

Имейте в виду, что неопределенные значения значениями не считаются, так что если в каких-либо строках в столбце TotalSale (всего продано) находятся неопределенные значения, то при подсчете средней продажи эти строки игнорируются.

Подзапросы

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

Язык управления данными

В языке управления данными (Data Control Language, DCL) имеются четыре команды: COMMIT (завершить), ROLLBACK (откат), GRANT (предоставить) и REVOKE (отозвать). Все эти команды связаны с защитой базы от случайного или умышленного повреждения.

Транзакции

Базы данных наиболее уязвимы именно тогда, когда в них вносят изменения. Изменения могут быть опасными даже для однопользовательских баз. Аппаратный или программный сбой, происшедший во время изменения, может застать базу данных в переходном состоянии – между состоянием в момент начала изменений и состоянием, которое было бы в момент завершения этих изменений.

С целью защиты базы данных язык SQL ограничивает операции, которые могут ее изменить, так что они выполняются только в пределах транзакций. Во время транзакции SQL записывает каждую операцию с данными в файл журнала. Если транзакцию, перед тем как она будет завершена оператором COMMIT, что-то прервет, то можно восстановить первоначальное состояние системы с помощью оператора ROLLBACK. Этот оператор обрабатывает журнал транзакций в обратном порядке, отменяя все действия, имевшие место во время транзакции. Выполнив откат базы данных до состояния, в котором она была перед началом транзакции, можно выяснить, что вызвало неполадки, а затем попробовать еще раз выполнить транзакцию.

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

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

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

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

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

Пользователи и полномочия

Кроме повреждения данных, вызванного проблемами с оборудованием и программами или неумышленными совместными действиями двух пользователей, целостности данных угрожает и другая большая опасность. Это сами пользователи. Некоторым людям вообще нельзя иметь доступ к данным. Другим – только ограниченный доступ к некоторым данным и никакого доступа к остальным. А кое-кто должен иметь неограниченный доступ ко всем данным. Поэтому вам нужна система, предназначенная для классификации пользователей по категориям и присвоения этим пользователям в соответствии с их категорией определенных полномочий доступа.

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

SQL дает возможность защищать следующие объекты базы данных.

· Таблицы.

· Столбцы.

· Представления.

· Домены.

· Символьные наборы.

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

· Трансляции.

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

Доступ разрешается с помощью оператора GRANT (разрешить), а аннулируется с помощью тора REVOKE (отозвать). Управляя использованием команды SELECT, DCL позволяет определить тех, кто может видеть объекты базы данных, такие, например, как таблица, столбец или представление. В случае команды INSERT DCL позволяет определить тех, кто может добавлять в таблицу новые строки. То, что команда UPDATE может применяться только авторизованными пользователями, дает возможность назначать пользователей, ответственных за изменение табличных строк, и аналогично в случае команды DELETE – тех, кто может такие строки удалять.

Если в одной таблице базы данных имеется столбец, который для этой таблицы является внешним ключом, а для другой таблицы из этой базы – первичным, то для первой таблицы, если она ссылается на вторую, можно установить ограничение. Дело в том, что когда одна таблица ссылается на другую, то владелец первой из них, вероятно, сможет получать информацию о содержимом второй. Владельцу же второй таблицы, возможно, захочется этот доступ пресечь. Такая возможность дается в виде оператора GRANT REFERENCES (предоставить доступ по ссылке). В следующем разделе рассказывается о проблеме, связанной с "предательской" ссылкой, и о том, как оператор GRANT REFERENCES решает эту проблему. Применяя оператор GRANT USAGE (предоставить использование), можно назначать пользователей, которым позволено использование или просмотр содержимого домена, набора символов, сопоставления или трансляции. (Об этом рассказывается в главе 13.)

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

Таблица 3.4. Виды защиты.

Действие по защите	Оператор
Позволяет просматривать таблицу	GRANT SELECT
Не позволяет просматривать таблицу	REVOKE SELECT
Позволяет вставлять строки в таблицу	GRANT INSERT
Не позволяет вставлять строки в таблицу	REVOKE INSERT
Позволяет менять значения в строках таблицы	GRANT UPDATE
Не позволяет менять значения в строках таблицы	REVOKE UPDATE
Позволяет удалять строки из таблицы	GRANT DELETE
Не позволяет удалять строки из таблицы	REVOKE DELETE
Позволяет ссылаться на таблицу	GRANT REFERENCES
Не позволяет ссылаться на таблицу	REVOKE REFERENCES
Позволяет использовать домен, набор символов, сопоставление или трансляцию	GRANT USAGE ON DOMAIN, REVOKE USAGE ON CHARACTER SET, REVOKE USAGE ON COLLATION, REVOKE USAGE ON TRANSLATION
Не позволяет использовать домен, набор сопоставление или трансляцию	REVOKE USAGE ON DOMAIN, REVOKE USAGE ON CHARACTER SET, REVOKE USAGE ON COLLATION, REVOKE USAGE ON TRANSLATION

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

GRANT SELECTON CUSTOMERTO SALES_MANAGER;

В этом случае один пользователь, менеджер по продажам, получает возможность просматривать таблицу CUSTOMER (клиент).

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

GRANT SELECTON RETAIL_PRICE_LISTTO PUBLIC;

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

GRANT UPDATEON RETAIL_PRICE_LISTTO SALES_MANAGER;

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

GRANT INSERTON RETAIL_PRICE_LISTTO SALES_MANAGER;

А теперь благодаря команде из следующего примера менеджер по продажам может также удалять из таблицы ненужные строки:

GRANT DELETEON RETAIL_PRICE_LISTTO SALES_MANAGER;