ПРЕДЛОЖЕНИЕ WHENEVER

Это удобно для выхода при выполненном условии, что все строки выбраны. Но если вы получили ошибку, вы должны предпринять нечто такое, что описано для третьего случая, выше. Для этой цели, SQL предоставляет предложение GOTO. Фактически SQL позволяет вам применять его достаточно широко, так что программа может выполнить команду GOTO автоматически, если будет произведено определенное значение SQLCODE.

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

EXEC SQL WHENEVER SQLERROR GOTO Error_handler; EXEC SQL WHENEVER NOT FOUND CONTINUE;

SQLERROR это другой способ сообщить, что SQLCODE < 0; а NOT FOUND это другой способ сообщить, что SQLCODE = 100. (Некоторые реализации называют последний случай ещё SQLWARNING.)

Error_handler это имя того места в программе, в которое будет передано выполнение программы, если произошла ошибка (GOTO может состоять из одного или двух слов). Такое место определяется любым способом главного языка, например, с помощью метки в Паскале или имени раздела или имени параграфа в КОБОЛе (в дальнейшем мы будем использовать термин "метка"). Метка более удачно идентифицирует стандартную процедуру, распространяемую проектировщиком для включения во все программы.

CONTINUE не делает чего-то специального для значения SQLCODE. Оно также является значением по умолчанию, если вы не используете команду WHENEVER, определяющую значение SQLCODE. Однако эти неактивные определения дают вам возможность переключаться вперёд и назад, выполняя и не выполняя действия в различных точках (метках) вашей программы.

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

EXEC SQL WHENEVER NOT FOUND GOTO No_rows;

No_rows это метка в некотором коде, содержащем определенное действие. С другой стороны, если вам нужно сделать выборку в программе позже, вы можете ввести следующее в этой точке:

EXEC SQL WHENEVER NOT FOUND CONTINUE;

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

ПРЕДОСТАВЛЕНИЕ ПРИВИЛЕГИЙ

Каждый пользователь БД SQL имеет набор привилегий. Это то, что пользователю разрешается делать (возможно это - файл регистрации, который может рассматриваться как минимальная привилегия). Эти привилегии могут изменяться со временем: новые - добавляться, старые - удаляться. Некоторые из этих привилегий определены в ANSI SQL, но имеются и дополнительные привилегии, которые также являются необходимыми.

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

ПРЕДОСТАВЛЕНИЕ ПРИВИЛЕГИЙ С ПОМОЩЬЮ WITH GRANT OPTION

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

создают несколько (или все) базовые таблицы в базе данных, а затем передают ответственность за них тем, кто будет фактически с ними работать. SQL позволяет делать это с помощью предложения WITH GRANT OPTION.

Если бы Diane хотела, чтобы Adrian имел право предоставлять привилегию SELECT в таблице Заказчиков другим пользователям, она дала бы ему привилегию SELECT с использованием предложения WITH GRANT OPTION:

GRANT SELECT ON Customers TO Adrian WITH GRANT OPTION;

После этого Adrian получил право передавать привилегию SELECT третьим лицам; он может выдать команду

GRANT SELECT ON Diane.Customers TO Stephen;

или даже

GRANT SELECT ON Diane.Customers TO Stephen WITH GRANT OPTION;

Пользователь с помощью GRANT OPTION в особой привилегии в данной таблице может, в свою очередь, предоставить эту привилегию в той же таблице, с или без GRANT OPTION, любому другому пользователю. Это не меняет принадлежности самой таблицы; как и прежде таблица принадлежит ее создателю. (Поэтому пользователи, получившие права, должны устанавливать префикс ID доступа владельца, когда обращаются к этим таблицам. Следующая глава покажет вам этот способ.) Пользователь же с помощью GRANT OPTION во всех привилегиях для данной таблицы будет иметь всю полноту власти в той таблице.

ПРЕДСТАВЛЕНИЯ И ОБЪЕДИНЕНИЯ

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

CREATE VIEW Nameorders AS SELECT onum, amt, a.snum, sname, cname FROM Orders a, Customers b, Salespeople c WHERE a.cnum = b.cnum AND a.snum = c.snum;

Теперь вы можете выбрать (SELECT) все заказы заказчика или продавца (*), или можете увидеть эту информацию для любого заказа.

Например, чтобы увидеть все заказы продавца Rifkin, вы должны ввести следующий запрос (вывод показан на Рисунке 20.3):

SELECT * FROM Nameorders WHERE sname = 'Rifkin';

=============== SQL Execution Log ============== | | | SELECT * | | FROM Nameorders | | WHERE sname = 'Rifkin'; | | =============================================== | | onum amt snum sname cname | | ------ -------- ----- ------- ------- | | 3001 18.69 1007 Rifkin Cisneros | | 3006 1098.16 1007 Rifkin Cisneros | | | ================================================

Рисунок 20.3 Заказы Rifkin, показанные в Nameorders

Вы можете также объединять представления с другими таблицами, базовыми таблицами или представлениями, поэтому вы можете увидеть все заказы продавца Axelrod и значения его комиссионных в каждом порядке:

SELECT a.sname, cname, amt comm FROM Nameorders a, Salespeople b WHERE a.sname = 'Axelrod' AND b.snum = a.snum;

Вывод для этого запроса показан на Рисунке 20.4. В предикате мы могли бы написать: "WHERE a.sname = 'Axelrod' AND b.sname = 'Axelrod'", но предикат, который мы использовали здесь, более общеупотребителен. Кроме того, поле snum это первичный ключ таблицы Продавцов, и, следовательно, должно, по определению, быть уникальным.

=============== SQL Execution Log ============== | | | SELECT a.sname, cname, amt * comm | | FROM Nameorders a, Salespeople b | | WHERE a.sname = 'Axelrod' | | AND b.snum = a.snum; | | =============================================== | | onum amt snum sname cname | | ------ -------- ----- ------- ------- | | 3001 18.69 1007 Rifkin Cisneros | | 3006 1098.16 1007 Rifkin Cisneros | | | ================================================

Рисунок 20.4 Объединение основной таблицы с представлением

Если бы там, например, было два Axelrod, вариант с именем будет объединять вместе их данные. Более предпочтительный вариант - использовать поле snum, чтобы хранить его отдельно.

ПРЕДСТАВЛЕНИЯ И ПОДЗАПРОСЫ

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

CREATE VIEW Elitesalesforce AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders c WHERE c.odate = b.odate);

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

CREATE VIEW Bonus AS SELECT DISTINCT snum, sname FROM Elitesalesforce a WHERE 10 < = (SELECT COUNT (*) FROM Elitesalestorce b WHERE a.snum = b.snum);

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

SELECT * FROM Bonus;

Теперь мы видим истинную мощность SQL. Извлечение той же полученной информации программами RPG или COBOL будет более длительной процедурой. В SQL это - только вопрос из двух комплексных команд, сохранённых как представление совместно с простым запросом. При самостоятельном запросе мы должны заботится об этом каждый день, потому что информация которую извлекает запрос, непрерывно меняется, чтобы отражать текущее состояние базы данных.

в принципе, больше соответствует логике

Термин "упорядочить" аналогичен общепринятому "заказать", что, в принципе, больше соответствует логике запроса, потому что, с точки зрения пользователя, он именно "заказывает" информацию в базе данных, которая упорядочивает эту информацию в соответствии с "заказом".
Изменения в БД могут быть прокручены обратно уже после того, как их действия уже были закончены. Например, если вы отменили вашу ошибку уже после того, как Diane получила свой вывод.
Одно действие может влиять частично на результат другого действия. Например, когда Diane получает среднее арифметическое значений, в то время как вы выполняете модификацию этих значений. Хотя это не всегда проблематично, в большинстве случаев действие такое же, как если бы агрегаты должны были отразить состояние БД в пункте относительной стабильности. Например, в ревизионных книгах должна быть возможность вернуться назад и найти это существующее усреднённое значение для Diane в некоторой временн&#x0301;ой точке, и оставить его без изменений, которые можно было бы сделать, начиная уже с этого места. Это будет невозможно сделать, если модификация была выполнена во время вычисления функции.
Тупик. Два пользователя могут попытаться выполнить действия, которые конфликтуют друг с другом. Например, если два пользователя попробуют изменить значение внешнего ключа и значение родительского ключа одновременно.
Имеется много сложнейших сценариев, которые нужно было бы последовательно просматривать, если бы одновременные транзакции были неуправляемыми. К счастью, SQL обеспечивает вас средством управления параллелизмом для точного указания места получения результата.
ANSI указывает, для управления параллелизмом, что все одновременные команды будут выполняться по принципу: ни одна команда не должна быть выдана, пока предыдущая не будет завершена (включая команды COMMIT или ROLLBACK). Точнее, нужно просто не позволить таблице быть доступной более чем для одной транзакции в данный момент времени. Однако в большинстве ситуаций необходимость иметь базу данных, доступную сразу многим пользователям, приводит к некоторому компромиссу в управлении параллелизмом.


Некоторые реализации SQL предлагают пользователям выбор, позволяя им самим находить золотую середину между согласованностью данных и доступом к БД. Этот выбор доступен пользователю, DBA, или тому и другому. На самом деле они осуществляют это управление вне SQL, даже если и воздействуют на процесс работы самого SQL.
Механизм, используемый SQL для управления параллелизмом операций, называется блокировкой. Блокировки задерживают определенные операции в БД, пока другие операции или транзакции не завершены. Задержанные операции выстраиваются в очередь и выполняются только тогда, когда блокировка снята (некоторые инструменты блокировок дают вам возможность указывать NOWAIT, которая будет отклонять команду, вместо того чтобы поставить её в очередь, позволяя вам делать что-нибудь другое).
Блокировки в многопользовательских системах необходимы. Следовательно, должен быть некий тип схемы блокировки по умолчанию, которая могла бы применяться ко всем командам в базе данных. Такая схема по умолчанию может быть определена для всей БД, или в качестве параметра в команде CREATE DBSPACE или команде ALTER DBSPACE и таким образом использовать их по-разному в различных DBS.
Кроме того, системы обычно обеспечиваются неким типом обнаружителя зависания, который может обнаруживать ситуации, где две операции имеют блокировки, блокирующие друг друга. В этом случае одна из команд будет прокручена обратно и получит сброс блокировки. Так как терминология и специфика схем блокировок меняются от программы к программе, мы можем смоделировать наши рассуждения на примере программы базы данных DB2 фирмы IBM. IBM - лидер в этой области (как, впрочем, и во многих других), и поэтому такой подход наиболее оправдан. С другой стороны, некоторые реализации могут иметь значительные различия в синтаксисе и в функциях, но в основном их действия должны быть очень похожими.

эта команда будет

Предыдущий пример - косвенный способ заставить поле cnum работать как первичный ключ таблицы Заказчиков. Базы данных воздействуют на первичные и другие ключи более непосредственно. Мы будем обсуждать это далее в Главах 18 и 19.

эти три представленных

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

ПОЛЕ СОДЕРЖАНИЕ --------- ---------------------------------------------- snum уникальный номер назначенный каждому продавцу ("номер служащего").

sname имя продавца.

city местонахождение продавца (город).

comm комиссионные продавцов в десятичной форме.

Таблица 1.2 содержит следующие столбцы:

ПОЛЕ СОДЕРЖАНИЕ -------- --------------------------------------------------- cnum уникальный номер, назначенный каждому заказчику.

cname имя заказчика.

city местонахождение заказчика (город).

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

И столбцы в Таблице 1.3:

ПОЛЕ СОДЕРЖАНИЕ --------- --------------------------------------------------- onum уникальный номер, данный каждому приобретению.

amt значение суммы приобретений.

odate дата приобретения.

cnum номер заказчика, делающего приобретение (из таблицы Заказчиков). snum номер продавца, продающего приобретение (из таблицы Продавцов).

Последняя строка этого

В более сложной программе вы можете захотеть установить булеву переменную в "true/верно", чтобы указать, что значение city = NULL. Затем вы можете просто проверять эту переменную всякий раз, когда вам это необходимо.

ПРОСМОТР ТОЛЬКО ОПРЕДЕЛЕННЫХ СТОЛБЦОВ ТАБЛИЦЫ

Команда SELECT способна извлечь строго определенную информацию из таблицы. Сначала мы можем предоставить возможность увидеть только опредёленные столбцы таблицы. Это выполняется легко: простым исключением столбцов, которые вы не хотите видеть, из команды SELECT. Например, запрос

SELECT sname, comm FROM Salespeople;

будет производить вывод, показанный на Рисунке 3.2.

=============== SQL Execution Log ============ | | | SELECT snum, comm | | FROM Salespeople; | | | | ==============================================| | sname comm | | ------------- --------- | | Peel 0.12 | | Serres 0.13 | | Motika 0.11 | | Rifkin 0.15 | | Axelrod 0.10 | ===============================================

Рисунок 3.2 Выбор определенных столбцов

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

ПРОВЕРКА ОШИБОК

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

Следующая команда будет определять любые несогласованности в этой области:

SELECT first.onum, tirst.cnum, first.snum, second.onum, second.cnum,second.snum FROM Orders first, Orders second WHERE first.cnum = second.cnum AND first.snum < > second.snum;

Хотя это выглядит сложно, логика достаточно проста. Команда будет брать первую строку таблицы Заказов, запоминать её под первым псевдонимом и проверять её в комбинации с каждой строкой таблицы Заказов под вторым псевдонимом, одну за другой. Если комбинация строк удовлетворяет предикату, она выбирается для вывода. В этом случае предикат будет рассматривать эту строку, найдёт строку, где поле cnum=2008, а поле snum=1007, и затем рассмотрит каждую следующую строку с тем же самым значением поля cnum. Если он находит, что какая-то из них имеет значение, отличное от значения поля snum, предикат будет верен, и выведет выбранные поля из текущей комбинации строк. Если же значение snum с данным значением cnum в нашей таблице совпадает, эта команда не произведет никакого вывода.

ПРОВЕРКА ПРЕДСТАВЛЕНИЙ, КОТОРЫЕ БАЗИРУЮТСЯ НА ДРУГИХ ПРЕДСТАВЛЕНИЯХ

Еще одно надо упомянуть относительно предложения WITH CHECK OPTION в ANSI: оно не делает каскадированного изменения: оно применяется только в представлениях, в которых оно определено, но не в представлениях, основанных на этом представлении. Например, в предыдущем примере

CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION;

попытка вставить или модифицировать значение оценки, не равное 300, потерпит неудачу. Однако мы можем создать второе представление (с идентичным содержанием), основанное на первом:

CREATE VIEW Myratings AS SELECT * FROM Highratings;

Теперь мы можем модифицировать оценки, не равные 300:

UPDATE Myratings SET rating = 200 WHERE cnum = 2004;

Эта команда, выполняемая так, как если бы она выполнялась как первое представление, будет допустима. Предложение WITH CHECK OPTION просто гарантирует, что любая модификация в представлении произведет значения, которые удовлетворяют предикату этого представления.

Модификация других представлений, базирующихся на первом текущем, является всё ещё допустимой, если эти представления не защищены предложениями WITH CHECK OPTION внутри этих представлений. Даже если такие предложения установлены, они проверяют только те предикаты представлений, в которых они содержатся. Так, например, даже если представление Myratings создавалось следующим образом,

CREATE VIEW Myratings AS SELECT * FROM Highratings WITH CHECK OPTION;

проблема не будет решена. Предложение WITH CHECK OPTION будет исследовать только предикат представления Myratings. Пока у Myratings фактически не имеется никакого предиката, WITH CHECK OPTION ничего не будет делать. Если используется предикат, то он будет проверяться всякий раз, когда представление Myratings будет модифицироваться, но предикат Highratings все равно будет проигнорирован. Это - дефект в стандарте ANSI, который у большинства программ исправлен. Вы можете попробовать использовать представление наподобие последнего примера и посмотреть, избавлена ли ваша система от этого дефекта. (Попытка выяснить это самостоятельно может иногда оказаться проще и яснее, чем поиск ответа в документации системы.)

ПРОВЕРКА УСЛОВИЙ, БАЗИРУЮЩИХСЯ НА НЕСКОЛЬКИХ ПОЛЯХ

Вы можете также использовать CHECK в качестве табличного ограничения. Это полезно в тех случаях, когда вы хотите включить более одного поля строки в условие. Предположим, что комиссионные .15 и выше будут разрешены только для продавца из Барселоны. Вы можете указать это с помощью следующего табличного ограничения CHECK:

CREATE TABLE Salespeople (snum integer NOT NULL UNIQUE, sname char (10) NOT NULL UNIQUE, city char(10), comm decimal, CHECK (comm < .15 OR city = 'Barcelona'));

Как видите, два различных поля должны быть проверены, чтобы определить, верен предикат или нет. Имейте в виду, что это - два разных поля одной и той же строки. Хотя вы можете использовать несколько полей, SQL не может проверить более одной строки одновременно. Вы не можете, например, использовать ограничение CHECK, чтобы удостовериться, что все комиссионные в данном городе одинаковы. Чтобы сделать это, SQL должен всякий раз, просматривая другие строки таблицы, когда вы модифицируете или вставляете строку, видеть, что значение комиссионных указано для текущего города. SQL этого делать не умеет.

Фактически вы могли бы использовать сложное ограничение CHECK для вышеупомянутого, если бы знали заранее, каковы должны быть комиссионные в разных городах. Например, вы могли бы установить такое ограничение:

CHECK ((comm = .15 AND city = 'London') OR (comm = .14 AND city = 'Barcelona') OR (comm = 11 AND city = 'San Jose')..)

Мы подали вам идею. Чем налагать такой комплекс ограничений, вы могли бы просто использовать представление с предложением WITH CHECK OPTION, которое имеет все эти условия в своем предикате (смотри в Главах 20 и 21 информацию о представлении и о WITH CHECK OPTION).

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

Главе 21.

ПРОВЕРКА ЗНАЧЕНИЙ ПОЛЕЙ

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

Ограничение CHECK состоит из ключевого слова CHECK, сопровождаемого предложением предиката, который использует указанное поле. Любая попытка модифицировать или вставить значение поля, которое могло бы сделать этот предикат неверным, будет отклонена.

Давайте рассмотрим ещё раз таблицу Продавцов. Столбец комиссионных выражается десятеричным числом и поэтому может быть умножен непосредственно на сумму приобретений, в результате чего будет получена сумма комиссионных (в долларах) продавца с установленным справа значком доллара ($). Кто-то может использовать понятие процента, однако можно ведь об этом и не знать. Если человек введёт по ошибке 14 вместо .14 для указания, в процентах, своих комиссионных, это будет расценено как 14.0 , что является законным десятеричным значением и будет нормально воспринято системой. Чтобы предотвратить эту ошибку, мы можем наложить ограничение CHECK столбца, чтобы убедиться, что вводимое значение меньше 1.

CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL UNIQUE, city char(10), comm decimal CHECK (comm < 1));

ПРОВЕРКА ЗНАЧЕНИЙ, ПОМЕЩАЕМЫХ В ПРЕДСТАВЛЕНИЕ

Другой вывод о модифицируемости представления - вы можете вводить значения, которые "проглатываются" (swallowed) в базовой таблице. Рассмотрим такое представление:

CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300;

Это представление - модифицируемое. Оно просто ограничивает ваш доступ к определенным строкам и столбцам в таблице. Предположим, что вы вставляете (INSERT) следующую строку:

INSERT INTO Highratings VALUES (2018, 200);

Команда INSERT допустима в данном представлении. Строка будет вставлена с помощью представления Highratings в таблицу Заказчиков. Однако, когда она появится там, она исчезнет из представления, поскольку значение оценки не равно 300. Это - обычная проблема.

Значение 200 может быть просто напечатано, но теперь строка находится уже в таблице Заказчиков, где вы не можете даже увидеть её. Пользователь не сможет понять, почему введя строку он не может её увидеть и будет неспособен при этом удалить её. Вы можете быть гарантированы от модификаций такого типа с помощью включения WITH CHECK OPTION (С ОПЦИЕЙ ПРОВЕРКИ) в определение представления. Мы можем использовать WITH CHECK OPTION в определении представления Highratmgs.

CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION;

Вышеупомянутая вставка будет отклонена.

WITH CHECK OPTION производит действие все_или_ничего (all-or-nothing). Вы помещаете его в определение представления, а не в команду DML, так что или все команды модификации в представлении будут проверяться, или ни одна не будет проверена. Обычно вы хотите использовать опцию проверки, применяя её в определении представления, что может быть удобно. В общем, вы должны использовать эту опцию, если у вас нет причины разрешать представлению помещать в таблицу значения, которые оно само не может содержать.

ПСЕВДОНИМЫ

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

Вы можете сделать это с помощью определения временных имён, называемых "переменными диапазона", "переменными корреляции" или просто "псевдонимами".

Вы определяете их в предложении FROM запроса. Это очень просто: вы вводите имя таблицы, оставляете пробел, а затем указываете псевдоним для неё.

Вот пример, который находит все пары заказчиков, имеющих один и тот же рейтинг (вывод показан на Рисунке 9.1):

SELECT first.cname, second.cname, first.rating FROM Customers first, Customers second WHERE first.rating = second.rating;

=============== SQL Execution Log ============== | | | Giovanni Giovanni 200 | | Giovanni Liu 200 | | Liu Giovanni 200 | | Liu Liu 200 | | Grass Grass 300 | | Grass Cisneros 300 | | Clemens Hoffman 100 | | Clemens Clemens 100 | | Clemens Pereira 100 | | Cisneros Grass 300 | | Cisneros Cisneros 300 | | Pereira Hoffman 100 | | Pereira Clemens 100 | | Pereira Pereira 100 | | | ===============================================

Рисунок 9.1 Объединение таблицы с собой

(Обратите внимание, что на Рисунке 9.1, как и в некоторых дальнейших примерах, полный запрос не может уместиться в окне вывода и, следовательно, будет усекаться.)

В вышеприведённой команде, SQL ведёт себя так, как если бы он соединял две таблицы, называемые 'first' и 'second'. Обе они - фактически - таблица Заказчика, но псевдонимы разрешают им быть обработанными независимо. Псевдонимы 'first' и 'second' были установлены в предложении FROM запроса сразу после имени таблицы.

Обратите внимание, что псевдонимы могут использоваться в предложении SELECT, даже если они не определены в предложении FROM. Это очень удобно. SQL будет сначала принимать любые такие псевдонимы на веру, но будет отклонять команду, если они не определены далее в предложении FROM запроса.

Псевдоним существует, только пока команда выполняется!

Когда запрос заканчивается, псевдонимы, используемые в нём, больше не имеют никакого значения.

Теперь, когда имеются две копии таблицы Заказчиков, чтобы работать с ними, SQL может обрабатывать эту операцию точно так же, как и любое другое объединение: берёт каждую строку из одного псевдонима и сравнивает её с каждой строкой из другого псевдонима.

РАБОТА СО SQL

1. Какое поле таблицы Заказчиков является первичным ключом?

2. Что является столбцом 4 из таблицы Заказчиков?

3. Как по другому называется строка? Столбец?

4. Почему вы не можете запрашивать для просмотра первые пять строк таблицы?

(См. ответы в Приложении A.)

Какое наибольшее основное различие между типами данных в SQL?

Распознает ANSI тип данных DATA?

Какой подраздел SQL используется, чтобы помещать значения в таблицы?

Что такое - ключевое слово?

(См. ответы в Приложении A.)

Напишите команду SELECT, которая вывела бы номер заказа, сумму и дату для всех строк из таблицы Заказов.

Напишите запрос, который вывел бы все строки из таблицы Заказчиков, для которых номер продавца = 1001.

Напишите запрос, который вывел бы таблицу со столбцами в следующем порядке: city, sname, snum, comm.

Напишите команду SELECT, которая вывела бы оценку (rating), сопровождаемую именем каждого заказчика в San Jose.

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

(См. ответы в Приложении A.)

Напишите запрос, который может выдать вам все заказы со значениями суммы выше $1,000.

Напишите запрос, который может выдать вам поля sname и city для всех продавцов в Лондоне с комиссионными выше .10.

Напишите запрос к таблице Заказчиков, чей вывод включит всех заказчиков с оценкой =< 100, если они не находятся в Риме.

Что может быть выведено в результате следующего запроса?

SELECT * FROM Orders WHERE (amt < 1000 OR NOT (odate = 10/03/1990 AND cnum > 2003));

Что может быть выведено в результате следующего запроса?

SELECT * FROM Orders WHERE NOT ((odate = 10/03/1990 OR snum > 1006) AND amt > = 1500 );

Как можно проще переписать такой запрос?

SELECT snum, sname, city, comm FROM Salespeople WHERE (comm > + .12 OR comm < .14);

(См. ответы в Приложении A.)

Напишите два запроса, которые могли бы вывести все заказы на 3 или 4 октября 1990.

Напишите запрос, который выберет всех заказчиков, обслуживаемых продавцами Peel или Motika. (Подсказка: из наших типовых таблиц поле snum связывает вторую таблицу с первой.)

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

Напишите запрос, который выберет всех пользователей, чьи имена начинаются с буквы C.

Напишите запрос, который выберет все заказы, имеющие нулевые значения или NULL в поле amt (сумма).

(См. ответы в Приложении A.)

Напишите запрос, который сосчитал бы все суммы продаж на 3 октября.

Напишите запрос, который сосчитал бы число различных не-NULL-значений поля city в таблице Заказчиков.

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

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

Напишите запрос, который выбрал бы высший рейтинг в каждом городе.

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

(См. ответы в Приложении A.)

Предположим, что каждый продавец имеет 12% комиссионных. Напишите запрос к таблице Заказов, который выведет номер заказа, номер продавца и сумму комиссионных продавца по этому заказу.

Напишите запрос к таблице Заказчиков, который мог бы найти высший рейтинг в каждом городе. Вывод должен быть в такой форме:

For the city (city), the highest rating is: (rating).

Напишите запрос, который выводил бы список заказчиков в нисходящем порядке. Вывод поля оценки/рейтинга (rating) должен сопровождаться именем заказчика и его номером.

Напишите запрос, который выводил бы общие заказы на каждый день и помещал результаты в нисходящем порядке.(См. ответы в Приложении A.)

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

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

Напишите запрос, который выводил бы всех заказчиков, обслуживаемых продавцом с комиссионными выше 12%. Выведите имя заказчика, имя продавца и ставку комиссионных продавца.

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

(См. ответы в Приложении A.)

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

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

Напишите запрос, который вывел бы имена (cname) и города (city) всех заказчиков.

(Ответы см. в Приложении А.)

1. Напишите запрос, который использовал бы подзапрос для получения всех заказов для заказчика с именем Cisneros. Предположим, что вы не знаете номера этого заказчика, указываемого в поле cnum.

2. Напишите запрос, который вывел бы имена и оценки всех заказчиков, имеющих усреднённые заказы.

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

(См. ответы в Приложении A.)

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

2. Напишите два запроса, которые выберут всех продавцов (по их имени и номеру), имеющих в своих городах заказчиков, которых они не обслуживают. Один запрос - с использованием объединения, а другой - с соотнесённым подзапросом. Которое из решений будет более изящным?

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

(См. ответы в Приложении A.)

1. Напишите запрос, который использовал бы оператор EXISTS для извлечения всех продавцов, имеющих заказчиков с оценкой 300.

2. Как бы вы решили предыдущую проблему, используя объединение?

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

4. Напишите запрос, который извлекал бы из таблицы Заказчиков каждого заказчика, назначенного продавцу, который в данный момент имеет по крайней мере ещё одного заказчика (кроме заказчика, которого вы выберете) с заказами в таблице Заказов (подсказка: это может быть похоже на структуру в примере с нашим трехуровневым подзапросом).

(См. ответы в Приложении A.)

1. Напишите запрос, который выбирал бы всех заказчиков, чьи оценки равны или больше, чем любая (ANY) оценка заказчика Serres.

2. Что будет выведено вышеупомянутой командой?

3. Напишите запрос, использующий ANY или ALL, который находил бы всех продавцов, которые не имеют никаких заказчиков, живущих в их городе.

4. Напишите запрос, который выбирал бы все заказы с суммой, больше, чем любая (в обычном смысле) для заказчиков в Лондоне.

5. Напишите предыдущий запрос с использованием MAX.

(См. ответы в Приложении A.)

1. Создайте объединение из двух запросов, которое показало бы имена, города и оценки всех заказчиков. Те из них, которые имеют поле rating=200 и более, должны, кроме того, иметь слова - "Высокий Рейтинг", а остальные должны иметь слова "Низкий Рейтинг".

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

3. Сформируйте объединение из трех запросов. Первый выбирает поля snum всех продавцов в San Jose; второй, поля cnum всех заказчиков в San Jose; и третий - поля onum всех заказов на 3 октября. Сохраните дубликаты между последними двумя запросами, но устраните любую избыточность вывода между каждым из них и самым первым.

(Примечание: в данных типовых таблицах не содержится никакой избыточности. Это только пример.)

(См. ответы в Приложении A.)

1. Напишите команду, которая поместила бы следующие значения в указанном заказе в таблицу Продавцов:

city - San Jose, name - Bianco, comm - NULL, cnum - 1100.

2. Напишите команду, которая удалила бы все заказы заказчика Clemens из таблицы Заказов.

3. Напишите команду, которая увеличила бы оценку всех заказчиков в Риме на 100.

4. Продавец Serres оставил компанию. Переназначьте его заказчиков продавцу Motika.

(См. ответы в Приложении A.)

1. Предположите, что имеется таблица Multicust с такими же именами столбцов, что и в таблице Продавцов. Напишите команду, которая вставила бы всех продавцов (из таблицы Продавцов), имеющих более чем одного заказчика, в эту таблицу.

2. Напишите команду, которая удаляла бы всех заказчиков не имеющих текущих заказов.

3. Напишите команду, которая увеличила бы на двадцать процентов комиссионные всех продавцов, имеющих сумму текущих заказов выше $3,000.

(См. ответы в Приложении A.)

1. Напишите предложение CREATE TABLE, которое вывело бы нашу таблицу Заказчиков.

2. Напишите команду, которая давала бы возможность пользователю быстро извлекать заказы, сгруппированные по датам, из таблицы Заказов.

3. Если таблица Заказов уже создана, как вы можете заставить поле onum быть уникальным (если допустить что все текущие значения уникальны)?

4. Создайте индекс, который разрешал бы каждому продавцу быстро отыскивать его заказы, сгруппированные по датам.

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

(См. ответы в Приложении A.)

1. Создайте таблицу Заказов так, чтобы все значения полей onum, а также все комбинации полей cnum и snum, отличались друг от друга и чтобы значения NULL исключались из поля даты.

2. Создайте таблицу Продавцов так, чтобы комиссионные по умолчанию составляли 10%, не разрешались значения NULL, поле snum являлось первичным ключом и чтобы все имена были в алфавитном порядке между A и M включительно (учитывая, что все имена будут напечатаны в верхнем регистре).

3. Создайте таблицу Заказов учётом того, что поле onum больше, чем поле cnum, а cnum больше, чем snum. Запрещены значения NULL в любом из этих трех полей.

(См. ответы в Приложении A.)

1. Создайте таблицу с именем Cityorders. Она должна содержать такие же поля onum, amt и snum, что и таблица Заказов, и такие же поля cnum и city, что и таблица Заказчиков, так что заказ каждого заказчика будет вводиться в эту таблицу вместе с его городом. Поле оnum будет первичным ключом Cityorders. Все поля в Cityorders должны иметь ограничения при сравнении с таблицами Заказчиков и Заказов. Допускается, что родительские ключи в этих таблицах уже имеют соответствующие ограничения.

2. Усложним проблему. Переопределите таблицу Заказов следующим образом: добавьте новый столбец с именем prev, который будет идентифицирован для каждого заказа, поле onum предыдущего заказа для этого текущего заказчика. Выполните это с использованием внешнего ключа, ссылающегося на саму таблицу Заказов. Внешний ключ должен ссылаться также на поле cnum заказчика, обеспечивающее определенную предписанную связь между текущим порядком и ссылаемым.

(См. ответы в Приложении A.)

1. Создайте представление, которое показывало бы всех заказчиков, имеющих самые высокие рейтинги.

2. Создайте представление, которое показывало бы номер продавца в каждом городе.

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

4. Создайте представление, которое показывало бы каждого продавца с несколькими заказчиками.

(См. ответы в Приложении A.)

1. Какое из этих представлений - модифицируемое?

#1 CREATE VIEW Dailyorders AS SELECT DISTINCT cnum, snum, onum, odate FROM Orders;

#2 CREATE VIEW Custotals AS SELECT cname, SUM (amt) FROM Orders, Customers WHERE Orders.cnum = customer.cnum GROUP BY cname;

#3 CREATE VIEW Thirdorders AS SELECT * FROM Dailyorders WHERE odate = 10/03/1990;

#4 CREATE VIEW Nullcities AS SELECT snum, sname, city FROM Salespeople WHERE city IS NULL OR sname BETWEEN 'A' AND 'MZ';

2. Создайте представление таблицы Продавцов с именем Commissions (Комиссионные). Это представление должно включать только поля comm и snum. С помощью этого представления можно будет вводить или изменять комиссионные, но только для значений между .10 и .20.

3. Некоторые SQL-реализации имеют встроенную константу, представляющую текущую дату, иногда называемую " CURDATE ". Слово CURDATE может, следовательно, использоваться в операторе SQL и заменяться текущей датой, когда его значение станет доступным, с помощью таких команд как SELECT или INSERT. Мы будем использовать представление таблицы Заказов с именем Entryorders для вставки строк в таблицу Заказов. Создайте таблицу заказов так, чтобы CURDATE автоматически вставлялась в поле odate, если не указано другое значение. Затем создайте представление Entryorders так, чтобы значения не могли быть указаны.

(См. ответы в Приложении A.)

1. Передайте Janet право на изменение оценки заказчика.

2. Передайте Stephan право передавать другим пользователям право делать запросы в таблице Заказов.

3. Отнимите привилегию INSERT (ВСТАВКА) в таблице Продавцов у Claire и у всех пользователей, которым она была предоставлена.

4. Передайте Jerry право вставлять в или модифицировать таблицу Заказчиков с сохранением его возможности оценивать значения в диапазоне от 100 до 500.

5. Разрешите Janet делать запросы в таблице Заказчиков, но запретите ей уменьшать оценки в той же таблице Заказчиков.

(См. ответы в Приложении A.)

1. Создайте область базы данных с именем Myspace, которая выделяет 15 процентов своей области для индексов и 40 процентов на расширение строк.

2. Вы получили право SELECT в таблице Заказов продавца Diane. Введите команду так, чтобы вы могли ссылаться к этой таблице как к "Orders", не используя имя "Diane" в качестве префикса.

3. Если произойдёт сбой питания, что случится со всеми изменениями, сделанными во время текущей транзакции?

4. Если вы не можете видеть строку из-за её блокировки, какой это тип блокировки?

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

(См. ответы в Приложении A.)

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

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

3. Выясните, сколько таблиц имеют индексы в более чем пятидесяти процентах их столбцов.

(См. ответы в Приложении A.)

Обратите внимание: ответы для этих упражнений написаны в псевдокодах, являющихся английским языком описания логики, которой должна следовать программа. Это сделано для того, чтобы помочь читателям, которые могут быть не знакомы с ПАСКАЛем (или любым другим языком).

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

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

1. Разработайте простую программу, которая выберет все комбинации полей snum и cnum из таблиц Заказов и Заказчиков, и выясните, всегда ли предыдущая комбинация - такая же, как последующая. Если комбинация из таблицы Заказов не найдена в таблице Заказчиков, значение поля snum для этой строки будет изменено на удовлетворяющее условию совпадения. Вы должны помнить, что курсор с подзапросом модифицируем (ANSI-ограничение также применимо к просмотрам) и что базисная целостность базы данных это не тоже самое, что проверка на ошибку (т.е. первичные ключи уникальны, все поля cnums в таблице Заказов правильны, и так далее). Проверьте раздел объявлений и убедитесь, что там объявлены все используемые курсоры.

2. Предположим, что ваша программа предписывает ANSI запрещение курсоров или просмотров, использующих модифицируемые подзапросы. Как вы должны изменить вышеупомянутую программу?

3. Разработайте программу, которая подсказывает пользователям изменить значения поля city продавца, автоматически увеличивает комиссионные на .01 для продавца, переводимого в Барселону, и уменьшает их на .01 для продавца, переводимого в Сан-Хосе. Кроме того, продавец находящийся в Лондоне, должен потерять .02 из своих комиссионных, независимо от того, меняет он город, или нет, в то время как продавец, не находящийся в Лондоне должен иметь увеличение комиссионных на .02. Изменение в комиссионных, основывающееся на нахождении продавца в Лондоне, может применяться независимо от того, куда тот переводится. Выясните, может ли поле city или поле comm содержать NULL-значения, и обработайте их, как это делается в SQL.

Предупреждение! Эта программа имеет некоторые сокращения.

(См. ответы в Приложении A.)

РАБОТА СО ЗНАЧЕНИЯМИ NULL

Часто в таблице будут записи, которые не имеют никаких значений поля, например, потому что информация не завершена, или потому что это поле просто не заполнялось. SQL учитывает такой вариант, позволяя вам вводить значение NULL (ПУСТОЙ) в поле, вместо значения. Когда значение поля равно NULL, это означает, что программа базы данных специально промаркировала это поле как не имеющее никакого значения для этой строки (записи).

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

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

РАВЕНСТВА И НЕРАВЕНСТВА

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

SELECT * FROM Customers WHERE rating = ALL (SELECT rating FROM Customers WHERE city = " San Jose');

Эта команда допустима, но с этими данными мы не получим никакого вывода. Только в единственном случае вывод будет выдан этим запросом - если все значения оценки в San Jose окажутся идентичными. В этом случае можно сказать следующее:

SELECT * FROM Customers WHERE rating = (SELECT DISTINCT rating FROM Customers WHERE city = " San Jose');

Основное различие в том, что эта последняя команда должна потерпеть неудачу, если подзапрос выведет много значений, в то время как вариант с ALL просто не даст никакого вывода. В общем, не самая удачная идея - использовать запросы, которые работают только в определённых ситуациях, подобно этой. Так как ваша БД будет постоянно меняться, это неудачный способ узнать о её содержании. Однако ALL может более эффективно использоваться с неравенствами, то есть с операцией "< >". Но учтите, что сказанное в SQL о значении, которое не равняется всем результатам подзапроса, будет отличаться от того же, но сказанного с учётом грамматики английского языка. Очевидно, если подзапрос возвращает много различных значений, как это обычно бывает, ни одно отдельное значение не может быть равно им всем в обычном смысле. В SQL выражение < > ALL в действительности означает "не равен любому" результату подзапроса. Другими словами, предикат верен, если данное значение не найдено среди результатов подзапроса. Следовательно, наш предыдущий пример противоположен по смыслу этому примеру (с выводом, показанным на Рисунке 13.11):

SELECT * FROM Customers WHERE rating < > ALL (SELECT rating FROM Customers WHERE city = " San Jose');

=============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE rating < > ALL | | (SELECT rating | | FROM Customers | | WHERE city = 'San Jose'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2001 Hoffman London 100 1001 | | 2006 Clemens London 100 1001 | | 2007 Pereira Rome 100 1004 | =============================================

Рисунок 13.11 Использование ALL с < >

Вышеупомянутый подзапрос выбирает все оценки для города San Jose. Он выводит набор из двух значений: 200 (для Liu) и 300 (для Cisneros). Затем основной запрос выбирает все строки с оценкой, не совпадающей ни с одной из них, другими словами - все строки с оценкой 100. Вы можете сформулировать тот же самый запрос с помощью операторов NOT IN:

SELECT* FROM Customers WHERE rating NOT IN (SELECT rating FROM Customers WHERE city = " San Jose');

Вы могли бы также использовать оператор ANY:

SELECT * FROM Customers WHERE NOT rating = ANY (SELECT rating FROM Customers WHERE city = " San Jose');

Вывод будет одинаков для всех трёх условий.

РАЗЛИЧНЫЕ ТИПЫ ДАННЫХ

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

Так как системы с РБД базируются на связях между фрагментами информации, различные типы данных должны отличаться друга от друга так, чтобы соответствующие процессы и сравнения могли быть в них выполнены. В SQL это делается с помощью назначения каждому полю типа данных, указывающего тип значения, которое это поле может содержать. Все значения в данном поле должны иметь одинаковый тип. В таблице Заказчиков, например, cname и city содержат строки текста для оценки, а snum и cnum это числа. По этой причине вы не можете ввести значение Highest (Наивысший) или значение None (Никакой) в поле rating, которое имеет числовой тип данных. Это ограничение удачно, так как оно налагает некоторую структурность на ваши данные. Вы часто будете сравнивать некоторые или все значения в данном поле, поэтому вы можете выполнять действие только на определенных строках, а не на всех. Вы не могли бы сделать этого, если бы значения полей имели смешанный тип данных.

К сожалению, определение этих типов данных является основной областью, в которой большинство коммерческих программ БД и официальный стандарт SQL не всегда совпадают. ANSI SQL-стандарт распознаёт только text и number, в то время как большинство коммерческих программ используют другие специальные типы. Такие как DATA (ДАТА) и TIME (ВРЕМЯ) - фактически, почти стандартные типы (хотя точный формат их меняется). Некоторые пакеты также поддерживают такие типы как, например, MONEY (ДЕНЬГИ) и BINARY (ДВОИЧНЫЙ). (MONEY это специальная "валютная" система исчисления, используемая компьютерами.)

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

ANSI определяет несколько числовых типов, различия между которыми довольно тонки, и иногда их путают. Разрешённые ANSI-типы данных перечислены в Приложении B. Сложность числовых типов ANSI можно, по крайней мере частично, объяснить усилием сделать вложенный SQL совместимым с рядом других языков. Два типа чисел ANSI - INTEGER (ЦЕЛОЕ ЧИСЛО) и DECIMAL (ДЕСЯТЕРИЧНОЕ ЧИСЛО) (которые можно сокращать как INT и DEC, соответственно), будут адекватны для наших целей, так же как и для целей большинства практических деловых прикладных программ. Естественно, что тип ЦЕЛОЕ можно представить как ДЕСЯТЕРИЧНОЕ ЧИСЛО, которое не содержит никаких цифр справа от десятичной точки.

Тип для текста - CHAR (или СИМВОЛ), относящийся к строке текста. Поле типа CHAR имеет длину, определяемую максимальным числом символов, которые могут быть введены в это поле. Большая часть реализаций также имеют нестандартный тип, называемый VARCHAR (ПЕРЕМЕННОЕ ЧИСЛО СИМВОЛОВ), который является текстовой строкой и может иметь любую длину до определённого реализацией максимума (обычно 254 символа). Значения CHARACTER и VARCHAR включаются в одиночные кавычки как 'текст'. Различие между CHAR и VARCHAR в том, что CHAR должен резервировать достаточное количество памяти для максимальной длины строки, а VARCHAR распределяет память по мере необходимости.

Символьные типы состоят из всех печатных символов, включая числа. Однако число 1 это не то же, что символ "1". Символ "1" - только печатный фрагмент текста, не определяемый системой как числовое значение 1. Например 1 + 1 = 2, но "1" + "1" не равно "2". Символьные значения сохраняются в компьютере как двоичные значения, но показываются пользователю как печатный текст.

Преобразование выполняется по формату, определяемому системой, которую вы используете. Этот формат преобразования будет одним из двух стандартных типов (возможно, с расширениями), используемых в компьютерных системах: ASCII-код (используемый во всех персональных и малых компьютерах) и EBCDIC-код (Расширенном Двоично-Десятеричном Код Обмена Информации) (используемый в больших компьютерах). Определенные операции, такие как упорядочивание в алфавитном порядке значений поля, будет изменяться вместе с форматом. Применение этих двух форматов будет обсуждаться в Главе 4.

Мы должны следить за рынком, а не за ANSI, в использовании типа DATE (ДАТА). (В системе, которая не распознает тип ДАТА, вы, конечно, можете объявить дату как символьное или числовое поле, но это сделает большинство операций более трудоёмкими.)

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

РЕГИСТРАЦИЯ

В системах с многочисленными пользователями имеется некоторый вид процедуры входа в систему, которую пользователь должен выполнить, чтобы получить доступ к компьютерной системе. Эта процедура определяет, какой ID доступа будет связан с текущим пользователем. Обычно каждый человек, использующий БД, должен иметь свой собственный ID доступа и при регистрации превращается в действительного пользователям. Однако часто пользователи, имеющие много задач, могут регистрироваться под различными ID доступа, или наоборот - один ID доступа может использоваться несколькими пользователями. С точки зрения SQL нет никакой разницы между этими двум случаями; он воспринимает пользователя просто как его ID доступа.

БД SQL может использовать собственную процедуру входа в систему или она может позволить другой программе, вроде операционной системы (основная программа которая работает на вашем компьютере), обрабатывать файл регистрации и получать ID доступа из этой программы. Тем или другим способом, но SQL будет иметь ID доступа, чтобы связать его с вашими действиями, а для вас будет иметь значение ключевое слово USER.

РЕЛЯЦИОННЫЕ ОПЕРАЦИИ

Реляционная операция - математический символ, который указывает на определённый тип сравнения двух значений. Вы уже видели, как используются равенства, такие как 2 + 3 = 5 или city = "London". Но имеются также и другие реляционные операции. Предположим, что вы хотите видеть всех Продавцов с их комиссионными, выше определенного значения. Вы можете использовать тип сравнения "больше чем" (>). Вот реляционные операции (операции сравнения), которыми располагает SQL:

=Равно>Больше<Меньше

>=Больше или равно<=Меньше или равно<>Не равно

Эти операции имеют стандартное значение для чисел. Для символов их определение зависит от формата преобразования, ASCII или EBCDIC, который вы используете.

SQL сравнивает символьные значения в терминах основных чисел, как определено в формате преобразования. Даже значение символа, такого как "1", который представляет число, не обязательно равняется числу, которое он представляет. Вы можете использовать реляционные операции, чтобы установить алфавитный порядок, например, "a" < "n", где a идёт раньше в алфавитном порядке, но всё это ограничивается с помощью параметра преобразования формата.

И в ASCII, и в EBCDIC символы сортируются по значению: символ имеет значение меньше, чем все другие символы, которым он предшествует в алфавитном порядке и которые имеют с ним один вариант регистра (верхний или нижний). В ASCII все символы верхнего регистра меньше, чем все символы нижнего регистра, поэтому "Z" < "a", а все числа - меньше чем все символы, поэтому "1" < "Z". То же относится и к EBCDIC.

Чтобы сохранить обсуждение более простым, мы допустим, что вы будете использовать текстовый формат ASCII. Проконсультируйтесь в документации вашей системы, если вы не уверены, какой формат вы используете или как он работает.

Значения, сравниваемые здесь, называются скалярными значениями. Скалярные значения производятся скалярными выражениями; 1 + 2 это скалярное выражение, которое производит скалярное значение 3. Скалярное значение может быть символом или числом, хотя очевидно, что только числа используются с арифметическими операциями, такими как + (сложение) или * (умножение).

Предикаты обычно сравнивают значения скалярных величин, используя реляционные операции или специальные операции SQL, чтобы увидеть, верно ли это сравнение. Некоторые операции/операторы SQL описаны в Главе 5.

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

SELECT * FROM Customers WHERE rating > 200;

Вывод для этого запроса показан на Рисунке 4.1.

Конечно, если бы мы захотели увидеть ещё и заказчиков с оценкой, равной 200, мы использовали бы предикат

rating > = 200

REVOKE (*NONSTANDARD*) (ОТМЕНИТЬ ПОЛНОМОЧИЯ) (НЕСТАНДАРТНАЯ)

Синтаксис

REVOKE { ALL [PRIVILEGES]

| <privilege> . , . . } [ON <table name>]

FROM { PUBLIC

| <Authorization ID> . , . . };

Привилегия <privelege> может быть любой из указанных в команде GRANT. Пользователь, дающий REVOKE, должен иметь те же привилегии, что и пользователь, который давал GRANT. Предложение ON может быть использовано, если применяется привилегия специального типа для особого объекта.

Теперь вы знаете, что такое

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


Мы кратко рассмотрели в этой главе основы. Но нашим намерением и было - бегло рассмотреть основы SQL так, чтобы вы смогли охватить весь объём информации. Когда мы возвратимся к основам в следующей главе, некоторые вещи будут конкретизированы.
Теперь вы знаете кое-что относительно SQL: какова его структура, как он используется, как он представляет данные и как они определяются (и некоторые несогласованности, появляющиеся при этом), некоторые условные обозначения и термины, используемые для их описания. Всё это - слишком большой объём информации для одной главы; мы не ожидаем, что вы запомнили все эти подробности, но вы сможете вернуться к ним позже, если понадобится.
Главе 3 мы будем работать, показывая конкретно, как формируются команды и что они делают. Мы представим вам команду SQL, используемую для извлечения информации из таблиц, которая является наиболее часто используемой командой SQL. К концу главы вы будете способны извлекать конкретную информацию из вашей БД с высокой степенью точности.


Теперь вы знаете несколько способов, как заставить таблицу выдавать вам ту информацию, какую вы хотите, а не просто вываливать наружу всё её содержание. Вы можете переупорядочивать столбцы таблицы или отбрасывать любой из них. Вы можете решать, хотите вы видеть дублированные значения, или нет.
Наиболее важно то, что вы можете устанавливать условие, называемое предикатом, которое определяет или не определяет, из тысяч таких же строк, будет ли выбрана для вывода указанная строка.
Предикаты могут становиться очень сложными, предоставляя вам высокую точность в решении того, какие строки вам выбирать с помощью запроса. Именно эта способность решать точно, что вы хотите видеть, делает запросы SQL такими мощными. Следующие несколько глав будут посвящены в большей мере особенностям, которые расширяют мощность предикатов. В Главе 4 вам будут представлены операции, иные, нежели те, которые используются в условиях предиката, а также способы объединения многочисленных условий в единый предикат.


В этой главе вы значительно расширили ваше знакомство с предикатами. Теперь вы можете находить значения, которые связаны с данным значением любым способом, определяемым различными реляционными операциями.
Вы можете также использовать булевы операции AND и OR, чтобы несколько условий, каждое из которых автономно в предикатах, объединять в единый предикат.
Булева операция NOT, как вы уже видели, может изменять значение условия или группы условий на противоположное.
Булевы и Реляционные операции могут эффективно управляться с помощью круглых скобок, которые определяют порядок, в котором операции будут выполнены. Эти операции применимы к любому уровню сложности, и вы поняли, как сложные условия могут создаваться из этих простых частей.
Теперь, когда мы показали, как используются стандартные математические операции, мы можем перейти к операциям которые являются специфичными для SQL. Это мы сделаем в Главе 5.


Теперь вы можете создавать предикаты в терминах связей, специально определённых в SQL. Вы можете искать значения в определённом диапазоне (BETWEEN) или в числовом наборе (IN), или вы можете искать символьные значения, которые соответствуют тексту внутри параметров (LIKE).
Вы также изучили кое-что о том, как SQL поступает при отсутствии данных (а это реально), используя NULL вместо конкретных значений. Вы можете извлекать или исключать значения NULL из вашего вывода, используя оператор IS NULL.
Теперь, когда вы имеете в вашем распоряжении весь набор стандартных математических и специальных операторов/операций, вы можете переходить к специальным функциям SQL, которые работают на всех группах значений, а не просто на одиночном значении, что важно. Это уже тема
Главы 6.


Теперь вы используете запросы несколько по-иному. Способность получать, а не просто размещать значения, очень мощна. Это означает, что вы не обязательно должны следить за определённой информацией, если вы можете сформулировать запрос так, чтобы её получить. Запрос будет давать вам поминутные результаты, в то время как таблица общего или среднего значений будет хороша только некоторое время после её модификации. Это не должно наводить на мысль, что агрегатные функции могут полностью вытеснить потребность в отслеживании информации, такой, например, как эта.
Вы можете применять эти агрегаты для групп значений, определённых предложением GROUP BY. Эти группы имеют значение поля в целом и могут постоянно находиться внутри других групп, которые имеют значение поля в целом. В то же время, предикаты ещё используются, чтобы определять, какие строки агрегатной функции применяются.
Объединенные вместе, эти особенности делают возможным производить агрегаты, основанные на чётко определённых подмножествах значений в поле. Затем вы можете определять другое условие для исключения определенных результатов групп с предложением HAVING.
Теперь, когда вы стали знатоком того, как запрос производит значения, мы покажем вам, в Главе 7, чт&#x0301;о вы можете делать со значениями, которые он производит.


В этой главе вы изучили, как заставить ваши запросы делать больше, чем просто выводить значения полей или объединять функциональные данные таблиц. Вы можете использовать поля в выражениях: например, вы можете умножить числовое поле на 10 или даже умножить его на другое числовое поле. Кроме того, вы можете помещать константы, включая и символы, в ваш вывод, что позволяет помещать текст непосредственно в запрос и получать его в выводе вместе с данными таблицы. Это дает возможность помечать или объяснять ваш вывод различными способами.
Вы также изучили, как упорядочивать ваш вывод. Даже если таблица сама по себе остаётся неупорядоченной, предложение ORDER BY даёт вам возможность управлять порядком вывода строк данного запроса. Вывод запроса может быть в порядке возрастания или убывания, и столбцы могут быть вложенными один внутрь другого.
Понятие выводимых столбцов объяснялось в этой главе. Вы теперь знаете, что выводимые столбцы можно использовать, чтобы упорядочить вывод запроса, но эти столбцы - без имени и, следовательно, должны определяться их порядковым номером в предложении ORDER BY.
Теперь, когда вы увидели, что можно делать с выводом запроса, основанного на одиночной таблице, настало время перейти к возможностям улучшенного запроса и узнать, как сделать запрос любого числа таблиц в одной команде, определив связи между ними. Это будет темой Главы 8.


Теперь вы больше не ограничиваетесь просмотром одной таблицы в каждый момент времени. Кроме того, вы можете делать сложные сравнения между любыми полями любого количества таблиц и использовать полученные результаты, чтобы решать, какую информацию вы хотели бы видеть. Фактически эта методика настолько полезна для построения связей, что она часто используется для создания их внутри одиночной таблицы. Это будет правильным: вы сможете объединить таблицу с собой, а это очень удобна вещь. Это будет темой Главы 9.


Теперь вы понимаете возможности объединения и можете использовать их для ограничения связей с этой же таблицей, между различными таблицами, или в обоих случаях. Вы видели некоторые варианты объединения при использовании его возможностей.
Вы теперь познакомились с терминами "порядковые переменные", "корреляционные переменные" и "предложения" (эта терминология будет меняться от продукта к продукту, так что мы предлагаем вам познакомится со всеми тремя терминами). Кроме того, вы поняли, немного, как в действительности работают запросы.
Следующим шагом, после комбинации нескольких таблиц или нескольких копий одной таблицы в запросе, будет комбинация нескольких запросов, где один запрос будет производить вывод, который будет затем управлять работой другого запроса. Это другое мощное средство SQL, о котором мы расскажем в Главе 10 и более детально - в последующих главах.


Теперь вы используете запросы в иерархической манере. Вы видели, что использование результата одного запроса для управления другим расширяет возможности, позволяя выполнить большее количество функций.
Вы теперь понимаете, как использовать подзапросы с реляционными операциями и со специальным оператором IN, или в предложении WHERE, или в предложении HAVING внешнего запроса.
В следующих главах мы будем рассматривать подзапросы. Сначала, в Главе 11, мы обсудим другой вид подзапроса, который выполняется отдельно для каждой строки таблицы, вызываемой во внешнем запросе. Затем, в Главах 12 и 13, мы представим вам несколько специальных операторов, которые функционируют на всех подзапросах, как это делает IN, за исключением случаев, когда эти операторы могут использоваться только в подзапросах.


Вы можете поздравить себя с овладением большого блока понятий SQL - соотнесённого подзапроса. Вы видели, как соотнесённый подзапрос связан с объединением, а также - как его можно использовать с агрегатными функциями и в предложении HAVING. В общем, вы теперь знаете все типы подзапросов.

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


EXISTS, хотя он и кажется простым, может быть одним из самых непонятных операторов SQL. Однако он обладает гибкостью и мощностью. В этой главе овладели большинством возможностей, которые предоставляет EXISTS. В дальнейшем ваше понимание улучшенной логики подзапроса значительно расширится.
Следующим шагом будет овладение тремя другими специальными операторами, которые принимают подзапросы как аргументы: это ANY, ALL и SOME. Как вы увидите в Главе 13, это альтернативные формулировки некоторых возможностей, которые вы уже использовали, но которые иногда могут оказаться предпочтительными.


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


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


Теперь вы овладели мастерством управления содержанием вашей базы данных с помощью трёх простых команд:
INSERT - используемой, чтобы помещать строки в базу данных; DELETE - чтобы удалять их; REFERENCES - чтобы изменять значения в уже вставленных строках.
Вы обучались использованию предиката с командами UPDATE и DELETE, чтобы определять, на которую из строк будет воздействовать команда. Конечно, предикаты как таковые не значимы для INSERT, потому что обсуждаемая строка не существует в таблице до окончания выполнения команды INSERT. Однако вы можете использовать запросы с INSERT, чтобы сразу помещать все наборы строк в таблицу. Причём вы можете делать это со столбцами в любом порядке.
Вы узнали, что значения по умолчанию могут помещаться в столбцы, если вы не устанавливаете эти значения явно.
Вы также видели использование стандартного значения по умолчанию, каковым является NULL.

Кроме того, вы поняли, что UPDATE может использовать выражение, тогда как INSERT не может.
Следующая глава расширит ваше познания, показав вам, как использовать подзапросы с этими командами. Эти подзапросы напоминают те, с которыми вы уже знакомы, но имеются некоторые специальные выводы и ограничения при использовании подзапросов в командах DML, что мы обсудим в Главе 16.


Теперь вы овладели тремя командами, которые управляют всем содержимым вашей БД. Осталось только несколько общих вопросов относительно ввода и стирания значений таблицы, когда, например, эти команды могут выполниться данным пользователем в данной таблице и когда действия, сделанные ими, становятся постоянными.
Подведём итог: вы используете команду INSERT, чтобы добавлять строки в таблицу. Вы можете или дать имена значениям этих строк в предложении VALUES (когда только одна строка может быть добавлена), или вывести значения с помощью запроса (когда любое число строк можно добавить одной командой).
Если используется запрос, он не может обращаться к таблице, в которую вы делаете вставку, каким бы способом вы её ни делали, ни в предложении FROM, ни с помощью внешней ссылки (как это делается в соотнесённых подзапросах). Всё это относится к любым подзапросам внутри этого запроса.
Запрос, однако, оставляет вам свободу использования соотнесённых подзапросов или подзапросов, которые дают в предложении FROM имя таблице, которое уже было указано в предложении FROM внешнего запроса (это - общий случай для запросов).
DELETE и UPDATE используются, чтобы, соответственно, удалить строки из таблицы и изменить в них значения. Оба они применимы ко всем строкам таблицы, если не используется предикат, определяющий, какие строки должны быть удалены или модифицированы. Этот предикат может содержать подзапросы, которые могут быть связаны с таблицей, удаляемой или модифицируемой с помощью внешней ссылки. Эти подзапросы, однако, не могут ссылаться на таблицу, модифицируемой любым предложением FROM.
Может показаться, что мы прошли материал SQL, который обладает не самой понятной логикой. Сначала мы сделали запрос таблицы, которая уже заполнена данными. Потом мы показали, как можно фактически помещать эти значения изначально. Но, как вы видите, полное ознакомление с запросами здесь неоценимо.
Теперь, когда мы показали вам, как заполнять значениями таблицы, которые уже были созданы (по определению), мы покажем (начиная со
следующей главы), откуда появляются эти таблицы.


Теперь вы уже бегло ориентируетесь в основах определений данных. Вы можете создавать, изменять и удалять таблицы. В то время как только первая из этих функций - часть официального стандарта SQL, другие будут время от времени меняться, особенно ALTER TABLE.
DROP TABLE позволяет вам избавиться от ненужных таблиц. Она уничтожает только пустые таблицы и, следовательно, не разрушает данные.
Вы теперь знаете об индексах, а также, как их создавать и удалять. SQL не даёт вам большого контроля над ними, так как реализация, которую вы используете, сама определяет, как быстро выполняются различные команды. Индексы это один из инструментов, дающий вам возможность воздействовать непосредственно на эффективность ваших команд в SQL. Мы рассмотрели индексы здесь, чтобы отличать их от ограничений, с которыми их нельзя путать. Ограничения это тема Главы 18 и Главы 19.


Вы теперь владеете несколькими способами управления значениями, которые могут быть введены в ваши таблицы. Вы можете использовать ограничение NOT NULL, чтобы исключать NULL; ограничение UNIQUE, чтобы вынуждать все значения в группе из одного или более столбцов отличаться друг от друга; ограничение PRIMARY KEY, для того чтобы делать в основном то же самое что и UNIQUE, но с различным окончанием, и наконец ограничение CHECK - для определения ваших собственных специальных условий, чтобы значения, встреченные перед ними, могли бы быть введены.
Кроме того, вы можете использовать предложение DEFAULT, которое будет автоматически вставлять значение по умолчанию в любое поле с именем, не указанным в INSERT, так же, как вставляется значение NULL, когда предложение DEFAULT не установлено и отсутствует ограничение NOT NULL.

Ограничения FOREIGN KEY или REFERENCES, о которых вы узнаете в Главе 19, очень похожи на них, за исключением того, что они связывают группу из одного или более полей с другой группой, и таким образом, сразу воздействуют на значения, которые могут быть введены в любую из этих групп.


Теперь вы имеете достаточно хорошее управление справочной целостностью. Основная идея в том, что все значения внешнего ключа ссылаются на указанную строку родительского ключа. Это означает, что каждое значение внешнего ключа должно быть представлено один раз, и только один раз, в родительском ключе.
Всякий раз, когда значение помещается во внешний ключ, родительский ключ проверяется, чтобы удостовериться, что его значение представлено; иначе команда будет отклонена.
Родительский ключ должен иметь Первичный Ключ (PRIMARY KEY) или Уникальное (UNIQUE) ограничение, гарантирующее, что значение не будет представлено более чем один раз. Попытка изменить значение родительского ключа, которое в настоящее время представлено во внешнем ключе, будет вообще отклонена. Ваша система может, однако, предложить вам выбор, чтобы получить значение внешнего ключа, установленного в NULL или для получения нового значения родительского ключа и указания, какой из них может быть получен независимо для команд UPDATE и DELETE.
Этим завершается наше обсуждение команды CREATE TABLE. Далее мы представим вам другой тип команды CREATE. В Главе 20 вы обучитесь представлению объектов данных, которые выглядят и действуют подобно таблице, но в действительности являются результатами запросов. Некоторые функции ограничений могут также выполняться представлениями, так что вы сможете лучше оценить вашу потребность в ограничениях, после того как вы прочитаете следующие три главы.


Теперь, когда вы можете использовать представления, ваша способность отслеживать и обрабатывать содержание БД значительно расширилась. Всё, что вы можете создать запросом, вы всегда сможете определить как представление. Запросы этих представлений это, фактически, запрос запроса. Использование представлений и для удобства, и для защиты также удобно, как и многие возможности представлений для форматирования и получения значений из постоянно меняющегося содержания вашей БД.
Имеется один главный вывод относительно представлений: это способность к модификации.
Как показано, вы можете модифицировать представления так же, как и базовую таблицу, с помощью изменений, применяемых к таблице, из которой получается представление, но это не всегда возможно.


Вы теперь полностью овладели знаниями о представлениях. Кроме правил, определяющих, является ли данное представление модифицируемыми в SQL, вы познакомились с основными понятиями, на которых эти правила базируются - т.е., что модификации в представлениях допустимы только тогда, когда SQL может недвусмысленно определить, какие значения базовой таблицы можно изменять. Это означает, что команда модификации при выполнении не должна требовать ни изменений для многих строк сразу, ни сравнений между многочисленными строками либо базовой таблицы, либо вывода запроса.
Поскольку объединения включают в себя сравнение строк, они также запрещены.
Вы также поняли различие между некоторыми способами, которые используют модифицируемые представления и представления только_чтение.
Вы научились воспринимать модифицируемые представления как окна, отображающие данные одиночной таблицы, но не обязательно исключающие или реорганизующие столбцы посредством выбора только определенных строк отвечающих условию предиката.
Представления только_чтение, с другой стороны, могут содержать более допустимые запросы SQL; они могут, следовательно, стать способом хранения запросов, которые вам нужно часто выполнять в неизменной форме. Кроме того, наличие запроса, чей вывод обрабатывается как объект данных, дает вам возможность иметь ясность и удобство при создании запросов в выводе запросов.
Вы можете теперь в представлении предотвращать создание командами модификации строк в базовой таблице, которые не представлены в самом представлении, с помощью предложения WITH CHECK OPTION в определения представления.
Вы можете также использовать WITH CHECK OPTION как один из способов ограничения в базовой таблице. В автономных запросах, вы обычно используете один или более столбцов в предикате, не представленные среди выбранных для вывода, что не вызывает никаких проблем. Но если эти запросы используются в модифицируемых представлениях, появляются проблемы, так как эти запросы производят представления, которые не могут иметь вставляемых в них строк.
Вы видели некоторые подходы к этим проблемам. В Главах 20 и 21, мы говорили, что представления имеют прикладные программы защиты. Вы можете дать пользователям возможность обращаться к представлениям, не разрешая в то же время обращаться к таблицам, в которых эти представления непосредственно находятся. Глава 22 будет исследовать вопросы доступа к объектам данных в SQL.


Привилегии дают вам возможность видеть SQL под новым углом зрения, когда SQL выполняет действия через специальных пользователей в специальной системе базы данных.
Сама команда GRANT достаточно проста: с её помощью вы предоставляете те или иные привилегии объекта одному или более пользователям. Если вы предоставляете привилегию WITH GRANT OPTION пользователю, этот пользователь может, в свою очередь, предоставить эту привилегию другим. Теперь вы понимаете намеки на использование привилегий в представлениях - чтобы усовершенствовать привилегии в базовых таблицах, или как альтернативы ограничениям - и на некоторые преимущества и недостатки такого подхода.
Привилегии системы, которые необходимы, но не входят в область стандарта SQL, обсуждались в их наиболее общей форме, поэтому вы будете знакомиться с ними на практике. Глава 23 продолжит обсуждение таких вопросов о выводе в SQL как сохранение или восстановление изменений, создание ваших собственных имён для таблиц, принадлежащих другим людям и понимание того, что происходит, когда различные пользователи пытаются обращаться к одному и тому же объекту одновременно.


Ключевые определения, с которыми вы познакомились в этой главе:
Синонимы, или как создавать новые имена для объектов данных.
Области базы данных (DBS), или как распределяется доступная память в базе данных.
Транзакция, или как сохранять или восстанавливать изменения в базе данных.
Управление Параллелизмом, или как SQL предохраняет от конфликта одной команды с другой.
Синонимы это объекты в том смысле, что они имеют имена и (иногда) владельцев, но, естественно, они не могут существовать без таблицы, чьё имя они замещают. Они могут быть общими и, следовательно, доступными каждому, кто имеет доступ к объекту, или они могут принадлежать определенному пользователю.
Области DBS, или просто DBS, это подразделы базы данных, которые распределены для пользователей. Связанные таблицы, (например, таблицы, которые будут часто объединяться), лучше хранить в общей для них DBS.
СOMMIT и ROLLBACK это команды, используемые для выполнения изменений в базе данных в то время, когда предыдущая команда COMMIT или команда ROLLBACK начинают сеанс и оставляют изменения или игнорируют их как группу.
Средство Управление Параллелизмом определяет, в какой степени одновременно поданные команды будут мешать друг другу. Оно является адаптируемым средством, находящим компромисс между производительностью базы данных и изоляцией действующих команд.


Итак, система SQL использует набор таблиц, называемый системным каталогом, в структуре базы данных. Эти таблицы могут запрашиваться, но не модифицироваться. Кроме того, вы можете добавлять комментарии столбцов в (и удалять их из) таблицы SYSTEMCATALOG и SYSTEMCOLUMNS.
Создание представлений в этих таблицах - превосходный способ точно определить, какая пользовательская информация может быть доступной.
Теперь, когда вы узнали о каталоге, вы завершили ваше обучение SQL в диалоговом режиме. Следующая глава этой книги расскажет вам, как SQL используется в программах, которые написаны на других языках, но которые способны извлечь пользу из возможностей SQL, взаимодействуя с его таблицами баз данных.


Команды SQL вкладываются в процедурные языки, чтобы объединить возможности двух подходов. Некоторые дополнительные средства SQL необходимы для выполнения этой работу. Вложенные команды SQL, транслируемые программой, называемой прекомпилятором, в форму, пригодную для использования транслятором главного языка, и используемые в этом главном языке как вызовы процедуры к подпрограммам, которые создаёт прекомпилятор, называются модулями доступа. ANSI поддерживает вложение SQL в языки ПАСКАЛЬ, ФОРТРАН, КОБОЛ и PL/I. Другие языки также используются, особенно С.

В попытке кратко описать вложенный SQL, наиболее важное в этой главе:
Все вложенные команды SQL начинаются словами EXEC SQL и заканчиваются способом, который зависит от используемого главного языка;
Все главные переменные, доступные в командах SQL, должны быть объявлены в разделе объявлений SQL, прежде чем они будут использованы;
Всем главным переменным должно предшествовать двоеточие, когда они используются в команде SQL;
Запросы могут сохранять свой вывод непосредственно в главных переменных, используя предложение INTO, если, и только если, они выбирают единственную строку;
Курсоры могут использоваться для сохранения вывода запроса и доступа к одной строке в каждый момент времени. Курсоры бывают объявленными (если определяют запрос, в котором будут содержаться), открытыми (если выполняют запрос) и закрытыми (если удаляют вывод запроса из курсора). Если курсор открыт, команда FETCH используется, чтобы перемещать его по очереди к каждой строке вывода запроса;
Курсоры являются модифицируемыми или только-для-чтения. Чтобы стать модифицируемым, курсор должен удовлетворять всем критериям, которым удовлетворяет просмотр; кроме того, он не должен использовать предложений ORDER BY или UNION, которые в любом случае не могут использоваться просмотрами. Немодифицируемый курсор является курсором только-для-чтения;
Если курсор - модифицируемый, он может использоваться для определения того, какие строки задействованы вложенными командами UPDATE и DELETE, через предложение WHERE CURRENT OF. DELETE или UPDATE должны быть вне той таблицы, к которой курсор обращается в запросе;SQLCODE должен быть объявлен как переменная числового типа для каждой программы, использующей вложенный SQL. Его значение устанавливается автоматически после выполнения каждой команды SQL;

ROLLBACK (WORK) (ОТКАТ) (ТРАНЗАКЦИИ)

Синтаксис

ROLLBACK WORK;

Команда отменяет все изменения в базе данных, сделанные в течение текущей транзакции. Она, кроме того, заканчивается текущую и начинает новую транзакцию.

SELECT (ВЫБОР)

Синтаксис

SELECT { IDISTINCT | ALL] <value expression> . , . . } / *

[INTO <host variable list> (*только внедрённый*)]

FROM <table reference> . , . .

[WHERE <predicate>]

[GROUP BY <grouping column> . , . .]

[HAVING <predicate>]

[ORDER BY <ordering column> [ASC | DESC] . , . . ];

Это предложение организует запрос и выводит значения из базы данных (см. Главы 3 - 14 ).

Применяются следующие правила:

Если ни ALL, ни DISTINCT не указаны, принимается ALL.

Выражение <value expression> состоит из <column spec>, агрегатной функции <aggregate funct>, нестандартной функции <nonstandard function>, постоянной <constant> или любой их комбинации с операторами в допустимых выражениях.

Таблица, на которую ссылаются, <table reference>, состоит из имени таблицы, включая префикс владельца, если текущий пользователь не владелец, или синоним (нестандартно) таблицы. Таблица может быть или базовой таблицей, или просмотром. В принципе псевдоним может указать, какой синонимом используется для таблицы, только на время текущей команды. Имя таблицы или синоним должны отделяться от псевдонима одним или более разделителями <separator>.

Если используется GROUP BY, все столбцы <column spec>, используемые в предложении SELECT, должны будут использоваться как группа столбцов <grouping column>, если они не содержатся в агрегатной функции <aggregate funct>. Вся группа столбцов <grouping column> должна быть представлена среди выражений <value expressions>, указанных в предложении SELECT. Для каждой отдельной комбинации значений группы столбцов <grouping column> будет иметься одна, и только одна, строка вывода.

Если HAVING используется, предикат <predicate> применяется к каждой строке, произведённой предложением GROUP BY, и те строки, которые сделают этот предикат верным, будут выведены.

Если ORDER BY используется, вывод имеет определенную последовательность. Каждый идентификатор столбца <column identifer> ссылается на указанное <value expression> в предложении SELECT. Если это <value expression> является указанным столбцом <column spec>, <column identifier> может быть таким же, как <column spec>. Иначе <column identifier> может быть положительным целым числом, указывающим место, где находится <value expression> в последовательности предложения SELECT. Вывод будет сформирован так, чтобы помещать значения, содержащиеся в <column identifier>, в порядке возрастания, если DESC не указан. Имя идентификатора столбца <column identifier>, стоящее первым в предложении ORDER BY, будет предшествовать позже стоящим именам в определении последовательности вывода.

Предложение SELECT оценивает каждую строку-кандидат таблицы, в которой строки показаны независимо.

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

Если только одна ссылаемая таблица <table reference> включена, каждая строка этой таблица в свою очередь является строкой-кандидатом.

Если более одной ссылаемой таблицы <table reference> включено, каждая строка каждой таблицы должна быть скомбинирована, в свою очередь, с каждой комбинацией строк из всех других таблиц. Каждая такая комбинация будет, в свою очередь, строкой-кандидатом.

Каждая строка-кандидат производит значения, которые делают предикат <predicate> в предложении WHERE верным, неверным, или неизвестным. Если GROUP BY не используется, каждое <value expression> применяется, в свою очередь, для каждой строки-кандидата, чьё значение делает предикат верным, и результатом этой операции является вывод.

Если GROUP BY используется, строки-кандидаты комбинируются, используя агрегатные функции. Если никакого предиката <predicate> не установлено, каждое выражение <value expression> применяется к каждой строке-кандидату или к каждой группе. Если указан DISTINCT, дубликаты (двойники) строк будут удалены из вывода.

СИМВОЛЬНЫЕ ФУНКЦИИ

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

ФУНКЦИЯЗНАЧЕНИЕ

СИНТАКСИС И СПРАВКА ПО КОМАНДАМ

В этом приложении содержатся сокращённые описания различных команд SQL. Цель состоит в том, чтобы дать вам быструю и точную справку и определение SQL.

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

Вот стандартные условные обозначения (они называются BNF-условиями):

Ключевые слова набираются в верхнем регистре.

SQL и другие специальные условия заключаются в угловые скобки и набираются курсивом.(<и>)

Необязательные части команд находятся в квадратных скобках ( [ и ] ).

Многоточие ( .... ) указывает на то, что предшествующая часть команды может повторяться любое число раз.

Вертикальная полоса ( | ) означает: то, что ей предшествует, может быть заменено на то, что следует за ней.

Фигурные Скобки ( { и } ) указывают: всё, что внутри них, должно быть расценено как целое, для оценки других символов (например, вертикальных полос или эллипсов).

Двойное двоеточие и знак равенства (:: = ) означают: то, что следует за ними, является определением того, что им предшествует.

Кроме того, мы будем использовать следующую последовательность ( .,.. ) чтобы указывать, что предшествующее этому может повторяться любое число раз, с индивидуальными событиями, отделяемыми запятыми. Атрибуты, которые не являются частью официального стандарта, будут отмечены в описании как (*нестандартные*).

СКАЛЯРНОЕ ВЫРАЖЕНИЕ С ПОМОЩЬЮ ВЫБРАННЫХ ПОЛЕЙ

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

Например, вы можете представить комиссионные вашего продавца в процентном отношении, а не как десятеричные числа.

Для этого достаточно:

SELECT snum, sname, city, comm * 100 FROM Salespeople;

Вывод для этого запроса показан на Рисунке 7.1.

СМЫСЛ ВНЕШНЕГО И РОДИТЕЛЬСКОГО КЛЮЧЕЙ

Когда поле является внешним ключом, оно определённым образом связано с таблицей, на которую оно ссылается. Вы фактически говорите: "каждое значение в этом поле (внешнем ключе) непосредственно привязано к значению в другом поле (родительском ключе)." Каждое значение (каждая строка) внешнего ключа должно недвусмысленно ссылаться на одно, и только это, значение (строку) родительского ключа. Если это так, то ваша система, как говорится, будет в состоянии справочной целостности.

Вы можете увидеть это на примере. Внешний ключ snum в таблице Заказчиков имеет значение 1001 для строк Hoffman и Clemens.

Предположим, что мы имели две строки в таблице Продавцов со значением в поле snum = 1001. Как мы узнаем, к которому из двух продавцов были назначены заказчики Hoffman и Clemens? Аналогично, если нет никаких таких строк в таблице Продавцов, мы получим Hoffman и Clemens, назначенными к продавцу, которого нет!

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

Фактически данное значение внешнего ключа может ссылаться только к одному значению родительского ключа, не предполагая обратной возможности: т.е. любое число внешних ключей может ссылаться на единственное значение родительского ключа. Вы можете увидеть это в типовых таблицах наших примеров. И Hoffman, и Clemens назначены к Peel, так что оба их значения внешнего ключа совпадают с одним и тем же родительским ключом, что очень хорошо. Значение внешнего ключа должно ссылаться только на одно значение родительского ключа, зато на одно значение родительского ключа может ссылаться с помощью любого количества значений внешнего ключа.

СООТНЕСЁННЫЕ ПОДЗАПРОСЫ И ОБЪЕДИНЕНИЯ

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

Однако в прикладной программе между ними имеется различие, такое как вышеупомянутая потребность в использовании DISTINCT с объединением и его необязательность с подзапросом.

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

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

СООТНЕСЁННЫЕ ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING

Предложение HAVING может принимать подзапросы и соотнесённые подзапросы. Когда вы используете соотнесённый подзапрос в предложении HAVING, вы должны ограничивать внешние ссылки на позиции, которые могли бы непосредственно использоваться в самом предложении HAVING. Вы помните из Главы 6, что предложение HAVING может использовать только агрегатные функции, которые указаны в их предложении SELECT, или поля, используемые в их предложении GROUP BY. Они являются только внешними ссылками, которые вы можете делать. Всё это потому, что предикат предложения HAVING оценивается для каждой группы из внешнего запроса, а не для каждой строки. Следовательно, подзапрос будет выполняться один раз для каждой группы, выведённой из внешнего запроса, а не для каждой строки. Предположим, что вы хотите суммировать значения сумм приобретений покупок из таблицы Заказов, сгруппировав их по датам, удалив все даты, где бы SUM не был по крайней мере на 2000.00 выше максимальной (MAX) суммы:

SELECT odate, SUM (amt) FROM Orders a GROUP BY odate HAVING SUM (amt) > ( SELECT 2000.00 + MAX (amt) FROM Orders b WHERE a.odate = b.odate );

Подзапрос вычисляет значение MAX для всех строк с той же самой датой, что и у текущей агрегатной группы основного запроса. Это должно быть выполнено, как и ранее, с использованием предложения WHERE. Сам подзапрос не должен использовать предложения GROUP BY или HAVING.

СОЗДАНИЕ И УДАЛЕНИЕ ПОЛЬЗОВАТЕЛЕЙ

Естественно, появляется вопрос, откуда возьмётся пользователь с именем Rodriguez? Как определить его ID допуска? В большинстве реализаций DBA создаёт пользователя, автоматически предоставляя ему привилегию CONNECT.

В этом случае обычно добавляется предложение IDENTIFIED BY, указывающее пароль. (Если же нет, операционная система должна определить, можете ли вы зарегистрироваться в БД с данным ID доступа.) DBA может, например, ввести:

GRANT CONNECT TO Thelonius IDENTIFIED BY Redwagon;

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

Раз Thelonious - уже опознанный пользователь, он или DBA могут использовать эту же команду, чтобы изменить пароль Redwagon.

Хотя это и удобно, но всё же имеется ограничение и в этом подходе: невозможность иметь пользователя, который мог бы зарегистрироваться, хотя бы временно. Если вы хотите запретить пользователю регистрироваться, вы должны использовать для REVOKE привилегию CONNECT, которая "удаляет" этого пользователя. Некоторые реализации позволяют вам создавать и удалять пользователей, независимо от их привилегий при регистрации. Когда вы предоставляете привилегию CONNECT пользователю, вы создаете этого пользователя. При этом, чтобы сделать это вам самим, вы должны иметь DBA-привилегию. Если этот пользователь будет создавать базовые таблицы (а не только представления), ему нужно также предоставить привилегию RESOURCE. Но это сразу порождает другую проблему.

Если вы сделаете попытку удалить привилегию CONNECT пользователя, который имеет созданные им таблицы, команда будет отклонена, потому что её действие оставит таблицу без владельца, а это не разрешается. Вы должны сначала удалить все таблицы, созданные этим пользователем, прежде чем удалить его привилегию CONNECT. Если эти таблицы - не пустые, то вы, вероятно, захотите передать их данные в другие таблицы с помощью команды INSERT, которая использует запрос. Вам не нужно удалять отдельно привилегию RESOURSE; достаточно удалить CONNECT, чтобы удалить пользователя.

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

Это несколько выводит нас за пределы стандарта SQL, как он определён в настоящее время, и, в некоторых реализациях, можно полностью выйти за пределы стандарта SQL. Эти вещи, вероятно, не будут слишком вас касаться, если вы не DBA или не пользователь высокого уровня. Обычные пользователи просто должны быть знакомыми с привилегиями системы в принципе, справляясь в своей документации только в случае специальных сообщений.

СОЗДАНИЕ ОБЪЕДИНЕНИЯ

Предположим, что вы хотите поставить в соответствие вашему продавцу ваших заказчиков в городе, в котором они живут, поэтому вы увидите все комбинации продавцов и заказчиков для этого города. Вы должны будете брать каждого продавца и искать в таблице Заказчиков всех заказчиков того же самого города. Вы могли бы сделать это, введя следующую команду (вывод показан на Рисунке 8.1):

SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city;

=============== SQL Execution Log ============ | SELECT Customers.cname, Salespeople.sname, | | Salespeople.city | | FROM Salespeople, Customers | | WHERE Salespeople.city = Customers.city | | ============================================= | | cname cname city | | ------- -------- ---- | | Hoffman Peel London | | Hoffman Peel London | | Liu Serres San Jose | | Cisneros Serres San Jose | | Hoffman Motika London | | Clemens Motika London | =============================================

Рисунок 8.1 Объединение двух таблиц

Так как это city имеется и в таблице Продавцов, и таблице Заказчиков, имена таблиц должны использоваться как префиксы. Хотя это необходимо, только когда два или более полей имеют одно и то же имя, в любом случае это хорошая идея: включать имя таблицы в объединение для лучшего понимания и непротиворечивости. Несмотря на это, мы будем в наших примерах далее использовать имена таблиц только тогда, когда необходимо, так что будет ясно, когда они необходимы, а когда нет.

Что SQL в основном делает в объединении, так это исследует каждую комбинацию строк двух или более возможных таблиц и проверяет эти комбинации по их предикатам. В предыдущем примере требовалась строка продавца Peel из таблицы Продавцов и объединение её с каждой строкой таблицы Пользователей, по одной в каждый момент времени.

Если комбинация производит значение, которое делает предикат верным, и если поле city из строк таблиц Заказчика равно London, то Peel - это то запрашиваемое значение, которое комбинация выберет для вывода. То же самое будет затем выполнено для каждого продавца в таблице Продавцов (у некоторых из которых не было никаких заказчиков в этих городах).

СОЗДАНИЕ ЗАПРОСА

Как мы говорили ранее, SQL это Структурированный Язык Запросов. Запросы, вероятно, наиболее часто используемый аспект SQL. Фактически маловероятно, для категории SQL-пользователей, чтобы этот язык использовался для чего-то другого. По этой причине мы будем начинать наше обсуждение SQL с обсуждения запроса и того, как он выполняется на этом языке.

СПЕЦИАЛЬНЫЕ ОПЕРАТОРЫ ANY ИЛИ SOME

Операторы SOME и ANY взаимозаменяемы везде, и там, где мы используем ANY, SOME будет работать точно так же. Различие в терминологии состоит в том, чтобы позволить людям использовать тот термин, который является однозначным. Это может создать проблему, поскольку, как мы это увидим, наша интуиция может иногда вводить в заблуждение.

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

SELECT * FROM Salespeople WHERE city = ANY (SELECT city FROM Customers);

Оператор ANY берёт все значения, выведенные подзапросом, (для этого случая это все значения city в таблице Заказчиков), и оценивает их как верные, если любое (ANY) из них равняется значению города текущей строки внешнего запроса.

=============== SQL Execution Log ============ | SELECT * | | FROM Salespeople | | WHERE city = ANY | | (SELECT city | | FROM Customers); | | ============================================= | | cnum cname city comm | | ----- -------- ---- -------- | | 1001 Peel London 0.12 | | 1002 Serres San Jose 0.13 | | 1004 Motika London 0.11 | =============================================

Рисунок 13.1 Использование оператора ANY

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

СПЕЦИАЛЬНЫЙ АТРИБУТ COUNT

Функция COUNT несколько отличается от всех остальных. Она считает число значений в данном столбце или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT, чтобы производить счёт чисел различных значений в данном поле. Мы могли бы использовать её, например, чтобы сосчитать количество продавцов, описанных в настоящее время в таблице Заказов (вывод показан на Рисунке 6.3):

SELECT COUNT (DISTINCT snum) FROM Orders;

СПЕЦИАЛЬНЫЙ ОПЕРАТОР ALL

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

SELECT * FROM Customers WHERE rating > ALL (SELECT rating FROM Customers WHERE city = Rome):

=============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE rating > ALL | | (SELECT rating | | FROM Customers | | WHERE city = 'Rome'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | ============================================

Рисунок 13.9 Использование оператора ALL

Этот оператор проверяет значения рейтинга всех заказчиков в Риме. Затем он находит заказчиков с оценкой, большей, чем у любого из заказчиков в Риме. Самая высокая оценка в Риме - у Giovanni (200). Следовательно, выбираются только значения выше 200.

Как и в случае с ANY, мы можем использовать EXISTS для создания альтернативной формулировки такого же запроса (вывод показан на Рисунке 13.10):

SELECT * FROM Customers outer WHERE NOT EXISTS (SELECT * FROM Customers inner WHERE outer.rating < = inner.rating AND inner.city = 'Rome');

=============== SQL Execution Log ============ | | | SELECT * | | FROM Customers outer | | WHERE NOT EXISTS | | (SELECT * | | FROM Customers inner | | WHERE outer rating = inner.rating | | AND inner.city = 'Rome'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | =============================================

Рисунок 13.10 Использование EXISTS в качестве альтернативы ALL

SQL КОМАНДЫ

Этот раздел подробно описывает синтаксис различных команд SQL. Это даст вам возможность быстро отыскивать команду, находить синтаксис и краткое описание её работы.

ИМЕЙТЕ ВВИДУ: команды, которые начинаются словами - EXEC SQL, а также команды или предложения, заканчивающиеся словом, <sql term> могут использоваться только во вложенном SQL.

SQL. НЕСОГЛАСОВАННОСТИ.

Вы можете понять из предшествующего обсуждения, что имеются самостоятельные отдельные несоответствия между продуктами мира SQL. SQL появился из коммерческого мира БД как инструмент и был позже превращён в стандарт ANSI. К сожалению, ANSI не всегда определяет наиболее полезное, поэтому программы пытаются соответствовать стандарту ANSI, не позволяя ему ограничивать их слишком сильно. ANSI - вид минимального стандарта - вы можете делать, больше чем он это позволяет, но вы должны быть способны получить те же самые результаты при выполнении той же самой задачи.

SQLCODE

Хорошо было бы знать, когда данные будут исчерпаны, чтобы можно было сообщить об этом пользователю, и цикл завершился бы автоматически. Это даже более важно, чем, например, знать, что команда SQL выполнена с ошибкой. Переменная SQLCODE (называемая еще SQLCOD в ФОРТРАНе) предназначена для того, чтобы обеспечить эту функцию. Она должна быть определена как переменная главного языка и должна иметь тип данных, который в главном языке соответствует одному из точных числовых типов SQL, как это показано в Приложении B. Значение SQLCODE устанавливается каждый раз, когда выполняется команда SQL.

В основном существуют три возможности:

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

а) Для SELECT, ни одна строка не выбрана запросом.
б) Для FETCH, последняя строка уже была выбрана, или ни одной строки не выбрано запросом в курсоре.
в) Для INSERT, ни одной строки не было вставлено (подразумевается, что запрос использовался, чтобы сгенерировать значения для вставки, и был отвергнут при попытке извлечения любой строки.

г) Для UPDATE и DELETE, ни одна строка не ответила условию предиката и, следовательно, никаких изменений в таблице сделано не будет. В любом случае будет установлен код SQLCODE = 100.

Команда выполнилась нормально, не удовлетворив ни одному из вышеуказанных условий. В этом случае будет установлен код SQLCOD = 0.

Команда сгенерировала ошибку. Если это случилось, изменения, сделанные в БД текущей транзакцией, будут восстановлены (см. Главу 23). В этом случае будет установлен код SQLCODE = некоторому отрицательному числу, определяемому проектировщиком. Задача этого числа - идентифицировать проблему так точно, насколько это возможно. В принципе, ваша система должна быть снабжена подпрограммой, которая в этом случае должна выполниться, чтобы выдать для вас информацию, расшифровывающую значение негативного числа, определенного вашим проектировщиком. В этом случае некоторое сообщение об ошибке будет выведено на экран или записано в файл протокола, а программа в это время выполнит восстановление изменений для текущей транзакции, откл&#x0301;ючится от базы данных и выйдет из нее.

СРАВНЕНИЕ ТАБЛИЦЫ С СОБОЙ

Вы можете также использовать соотнесённый подзапрос, основанный на той же самой таблице, что и основной запрос. Это даст вам возможность извлечь сложные формы произведённой информации. Например, мы можем найти все заказы со значениями сумм приобретений выше среднего для их заказчиков (вывод показан на Рисунке 11.4):

SELECT * FROM Orders outer WHERE amt > (SELECT AVG amt FROM Orders inter WHERE inner.cnum = outer.cnum);

=============== SQL Execution Log ============== | | | SELECT * | | FROM Orders outer | | WHERE amt > | | (SELECT AVG (amt) | | FROM Orders inner | | WHERE inner.cnum = outer.cnum | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3006 1098.19 10/03/1990 2008 1007 | | 3010 1309.00 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================

Рисунок 11.4 Соотнесение таблицы с собой

Конечно, в нашей маленькой (психиатрической больнице) типовой таблице, где большинство заказчиков имеют только один заказ, большинство значений являются одновременно средними и, следовательно, не выбираются. Давайте введём команду другим способом (вывод показан на Рисунке 11.5):

SELECT * FROM Orders outer WHERE amt > = (SELECT AVG (amt) FROM Orders inner WHERE inner.cnum = outer.cnum);

=============== SQL Execution Log ============== | | | SELECT * | | FROM Orders outer | | WHERE amt > = | | (SELECT AVG (amt) | | FROM Orders inner | | WHERE inner.cnum = outer.cnum); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3003 767.19 10/03/1990 2001 1001 | | 3002 1900.10 10/03/1990 2007 1004 | | 3005 5160.45 10/03/1990 2003 1002 | | 3006 1098.19 10/03/1990 2008 1007 | | 3009 1713.23 10/04/1990 2002 1003 | | 3010 1309.95 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================

Рисунок 11.5 Выбираются заказы, которые >= средней сумме приобретений их заказчиков.

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

СТАНДАРТНЫЕ ПРИВИЛЕГИИ

SQL-привилегии, определённые ANSI, это привилегии объекта. Это означает, что пользователь имеет привилегию для выполнения данной команды только на определенном объекте в БД. Очевидно, что привилегии должны различать эти объекты, но система привилегий, основанная исключительно на привилегиях объекта, не может адресовать всё, что нужно SQL, как мы увидим позже в этой главе.

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

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

Вот привилегии, которые можно назначить пользователю:

SELECT Пользователь может выполнять запросы в таблице.

INSERT Пользователь может выполнять команду INSERT в таблице.

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

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

REFERENCES Пользователь определить внешний ключ, который использует один или более столбцов этой таблицы, как родительский ключ. Вы можете ограничить эту привилегию для определённых столбцов. (Смотрите в Главе 19 подробности относительно внешнего ключа и родительского ключа.)

Кроме того, вы столкнётесь с такими нестандартными привилегиями объектов, как, например, INDEX (ИНДЕКС), дающей право создавать индекс в таблице, SYNONYM (СИНОНИМ), дающей право создавать синоним для объекта, который будет объяснён в Главе 23, и ALTER (ИЗМЕНИТЬ), дающей право выполнять команду ALTER TABLE в таблице.

Механизм SQL назначает пользователям эти привилегии с помощью команды GRANT.

СТОЛБЦЫ ИМЕНУЮТСЯ И НУМЕРУЮТСЯ

В отличие от строк, столбцы таблицы (называемые также полями) упорядочиваются и именуются. Таким образом, в нашей таблице адресной книги можно указать на "адрес столбца" или на "столбец 3". Это означает, что каждый столбец данной таблицы должен иметь уникальное имя, чтобы избежать неоднозначности. Лучше всего, если эти имена указывают на содержание поля. В типовых таблицах этой книги мы будем использовать такие сокращения для имени столбца как cname для имени заказчика и odate для даты заказа. Мы также дадим каждой таблице личный числовой номер столбца в качестве первичного ключа. Следующий раздел будет рассматривать эти таблицы и их ключи более подробно.

СТОЛБЦЫ ВЫВОДА

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

=============== SQL Execution Log ============ | | | SELECT snum, sname, city, comm * 100 | | FROM Salespeople; | | ==============================================| | snum sname city | | ------ --------- ----------- --------- | | 1001 Peel London 12.000000 | | 1002 Serres San Jose 13.000000 | | 1004 Motika London 11.000000 | | 1007 Rifkin Barcelona 15.000000 | | 1003 Axelrod New York 10.000000 | | | ===============================================

Рисунок 7.1 Помещение выражения в вашем запросе

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

СТРОКИ И ВЫРАЖЕНИЯ

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

СВЯЗЫВАНИЕ ОДНОЙ ТАБЛИЦЫ С ДРУГОЙ

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

Пациент Доктор Страховка Баланс

Farish Drume B.C./B.S. $272.99

Grillet Halben None $44. 76

Brock Halben Health,Inc. $9077.47

Много мощных функций можно использовать для извлечения информации из этих таблиц, согласно указанным параметрам, особенно когда эти параметры включают в себя фрагменты информации, связанные в различных таблицах друг с другом. Например, возьмём докторов. Предположим доктор Halben захотел получить номера телефонов всех своих пациентов. Чтобы извлечь эту информацию, он мог бы связать таблицу с номерами телефонов пациентов (по адресной книге) с таблицей, которая указывала бы, какой из пациентов - его. Хотя в этом простом примере он мог бы держать это в голове и сразу получать номера телефонов пациентов Grillet и Brock, эти таблицы могут быть достаточно большими и сложными.

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

SYSTEMCOLAUTH - ПРИВИЛЕГИИ ОБЪЕКТА, КОТОРЫЕ ОПРЕДЕЛЯЮТ АВТОРИЗАЦИЮ СТОЛБЦА

СТОЛБЕЦ ОПИСАНИЕ ------------- -------------------------------------------

username Пользователь, который имеет привилегии

grantor Пользователь, который предоставляет привилегии другому пользователю

tname Имя таблицы, в которой существуют привилегии

cname Имя столбца, в котором существуют привилегии

owner Владелец tname

updauth Имеет ли пользователь привилегию UPDATE в этом столбце

refauth Имеет ли пользователь привилегию REFERENCES в этом столбце

Столбцы updauth и refauth могут быть в состоянии Y, N или G, но не могут быть одновременно в состоянии N в одной и той же строке. Это первые пять столбцов таблицы, которые не составляют первичный ключ. Он отличается от первичного ключа SYSTEMTABAUTH, в котором содержится поле cname, указывающее на определенный столбец обсуждаемой таблицы, для которой применяются одна или обе привилегии. Отдельная строка в этой таблице может существовать для каждого столбца в любой данной таблице, в которой одному пользователю передаются привилегии определенного столбца с помощью другого пользователя.

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

ТИПОВОЙ ЗАПРОС Чтобы выяснить, в каких столбцах какой таблицы вы имеете привилегию REFERENCES, вы можете ввести следующий запрос (вывод показан на Рисунке 24.7):

SELECT owner, tname, cname FROM SYSTEMCOLAUTH WHERE refauth IN ('Y', 'G') AND username = USE ORDER BY 1, 2;

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

=============== SQL Execution Log ================ | | | SELECT OWNER, TNAME, CNAME | | FROM SYSTEMCOLAUTH | | WHERE refaulth IN ('Y' , 'G' ) | | AND username = USER | | ORDER BY 1, 2 | | ; | | ================================================= | | owner tname cname | | ----------- ----------- ------- | | Diane Customers cnum | | Diane Salespeople sname | | Diane Salespeople sname | | Gillan Customers cnum | ===================================================

Рисунок 24.7 Столбцы, в которых пользователь имеет привилегию INSERT

SYSTEMINDEXES - ИНДЕКСАЦИЯ В БАЗЕ ДАННЫХ

Имена столбцов в таблице SYSTEMINDEXES и их описания:

СТОЛБЕЦ ОПИСАНИЕ ------------- -----------------------------------------------

iname Имя индекса (используемое для его исключения)

iowner Имя пользователя, создавшего индекс

tname Имя таблицы, которая содержит индекс

cnumber Номер столбца в таблице

tabowner Пользователь, который владеет таблицей, содержащей индекс

numcolumns Число столбцов в индексе

cposition Позиция текущего столбца среди набора индексов

isunique Уникален ли индекс (Y или N)

ТИПОВОЙ ЗАПРОС Индекс считается неуникальным, если он вызывает продавца в snum-столбце таблицы Заказчиков:

SELECT iname, iowner, tname, cnumber, isunique FROM SYSTEMINDEXES WHERE iname = 'salesperson';

Вывод для этого запроса показан на Рисунке 24.4.

=============== SQL Execution Log ================ | | | SELECT iname, iowner, tname, cnumber, isunique | | FROM SYSTEMINDEXES | | WHERE iname = 'salespeople' | | ; | | ================================================= | | iname iowner tname cnumber isunique | | ----------- ------ ---------- ------- -------- | | salesperson Stephan Customers 5 N | | | ===================================================

Рисунок 24.4 Строка из таблицы SYSTEMINDEXES

---

---

LEFT(<string>,X)	Возвращает крайние левые (старшие) символы X из строки.
RICHT(<string>,X)	Возвращает символы X младшего разряда из строки
ASCII(<string>)	Возвращает код ASCII, которым строка представляется в памяти компьютера.
CHR(<asciicode>)	Возвращает принтерные символы кода ASCII.
VALUE(<string>)	Возвращает математическое значение для строки. Считается, что строка имеет тип CHAR или VARCHAR, но состоит из чисел. VALUE('3') произведёт число 3 типа INTEGER.
UPPER(<string>)	Преобразует все символы строки в символы верхнего регистра.
LOWER(<string>)	Преобразует все символы строки в символы нижнего регистра.
INlTCAP(<string>)	Преобразует начальные символы строки в заглавные буквы. В некоторых реализациях может иметь название PROPER.
LENGTH(<string>)	Возвращает число символов в строке.
<string>||
Объединяет две строки в выводе, так чтобы после первой немедленно следовала вторая. (значок || называется оператором сцепления).
LPAD(<string>,X,'*' )
Дополняет строку слева звездочками '*' или любым другим указанным символом в количестве, определяемом X.
RPAD(<string>,X, ")
То же самое что и LPAD, за исключением того, что дополнение делается справа.
SUBSTR(<string>,X,Y)	Извлекает Y символов из строки, начиная с позиции X.