Наличие специального типа даты даёт возможность выполнять арифметические операции с датами. Например, вы можете добавлять число дней к дате и получать другую дату в программе, самостоятельно следящей за числом дней в месяцах, високосными годами и т.д.
Даты могут также сравниваться; например фраза, дата A < дата B , означает, что дата A предшествует дате B по времени. Помимо даты, большое количество программ определяют специальный тип для времени, который может также быть представлен в ряде форматов, включая следующие:
СтандартФорматПример
| МЕЖДУНАРОДНАЯ ОРГАНИЗАЦИЯ ПО СТАНДАРТИЗАЦИИ (ISO) |
hh-mm-ss | 21.04.37 |
| Японский Индустриальный Стандарт (JIS ) | hh-mm-ss | 21.04.37 |
| IBM Европейский Стандарт | hh-mm-ss | 21.04.37 |
| IBM USA Стандарт (USA) | hh.mm AM/PM | 9.04 PM |
Время может добавляться или сравниваться точно так же, как дата, с автоматической коррекцией числа секунд в минутах или часах. Кроме того, специально встроенные константы, указывающие текущую дату или время (CURDATE или CURTIME), являются общими. Они похожи на константу USER (Пользователь) в которой их значение будет непрерывно меняться.
Можете ли вы включать время и дату в одно поле? Некоторые реализации определяют тип DATE достаточно точно, чтобы включать туда ещё и TIME. В качестве альтернативы третий обобщающий тип, TIMESTAMP, может быть определен как комбинация этих двух.
ТИПЫ ТЕКСТОВОЙ СТРОКИ
ANSI поддерживает только один тип для представления текста. Это тип CHAR. Любое поле такого типа должно иметь определённую длину. Если строка, вставленная в поле, меньше длины поля, она дополняется пробелами; строка не может быть длиннее поля.
Хотя и достаточно удобное, это определение всё же имеет некоторые ограничения для пользователя. Например, символьные поля должны иметь одинаковую длину, чтобы можно было выполнить команду UNION. Большинство реализаций поддерживают строки переменной длины для типов данных VARCHAR и LONG VARCHAR (или просто LONG). В то время как поле типа CHAR всегда может распределить память для максимального числа символов, которое может сохраняться в поле, поле VARCHAR при любом количестве символов может распределить только определённое количество памяти, чтобы сохранить фактическое содержание поля, хотя SQL может установить извне некоторое дополнительное пространство памяти, чтобы следить за текущей длиной поля.
Поле VARCHAR может быть любой длины, включая реализационно определяемый максимум. Этот максимум может меняться от 254 до 2048 символов для VARCHAR и до 16000 символов для LONG. LONG обычно используется для текста пояснительного характера или для данных, которые не могут легко сжиматься в простые значения полей; VARCHAR может использоваться для любой текстовой строки, чья длина может меняться. Между прочим, не всегда хорошо использовать VARCHAR вместо CHAR. Извлечение и модифицирование полей VARCHAR - более сложный и, следовательно, более медленный процесс, чем извлечение и модифицирование полей CHAR. Кроме того, некоторое количество памяти VARCHAR остается всегда неиспользованной (в резерве) для гарантии вмещения всей строки. Вы должны просчитывать, насколько значения полей могут меняться по длине, а также - способны ли они к объединению с другими полями, перед тем как решить, использовать CHAR или VARCHAR. Часто тип LONG используется для сохранения двоичных данных. Естественно, что использование размера такого "неуклюжего" поля будет ограничивать оперативность SQL. Проконсультируйтесь с вашим руководством.
УБЕДИТЕСЬ, ЧТО ЗНАЧЕНИЯ УНИКАЛЬНЫ
В Главе 17 мы обсудили использование уникальных индексов, чтобы заставить поля иметь различные значения для каждой строки. Эта практика осталась с прежних времен, когда SQL поддерживал ограничение UNIQUE. Уникальность это свойство данных в таблице, и поэтому его более логично назвать ограничением этих данных, а не просто свойством логического отличия, связывающим объект данных (индекс). Несомненно, уникальные индексы - один из самых простых и наиболее эффективных методов предписания уникальности. По этой причине некоторые реализации ограничения UNIQUE используют уникальные индексы; то есть они создают индекс, не сообщая вам об этом. Остается фактом, что вероятность беспорядка в базе данных достаточно мала, если вы предписываете уникальность вместе с ограничением.
УДАЛЕНИЕ ИНДЕКСОВ
Главным признаком индекса является его имя, поэтому он может быть удален. Обычно пользователи не знают о существовании индекса. SQL автоматически определяет, разрешено ли пользователю использовать индекс, и, если да, разрешает использовать его. Однако, если вы хотите удалить индекс, вы должны знать его имя.
Синтаксис для удаления индекса:
DROP INDEX index name;
Удаление индекса не влияет на содержимое полей.
УДАЛЕНИЕ ИЗБЫТОЧНЫХ ДАННЫХ
DISTINCT (ОТЛИЧИЕ) - аргумент, который обеспечивает вас способом устранять дублирующие значения из вашего предложения SELECT. Предположим, что вы хотите знать, какие продавцы в настоящее время имеют свои заказы в таблице Заказов. Под заказом (здесь и далее) будет пониматься запись в таблицу Заказов, регистрирующая приобретения, сделанные в определённый день определённым заказчиком у определённого продавца на определённую сумму. Вам не нужно знать, сколько заказов имеет каждый; вам нужен только список номеров продавцов (snum). Поэтому вы можете ввести:
SELECT snum FROM Orders;
для получения вывода показанного в Рисунке 3.4
=============== SQL Execution Log ============ | | | SELECT snum | | FROM Orders; | | | | ============================================= | | snum | | ------- | | 1007 | | 1001 | | 1004 | | 1002 | | 1007 | | 1003 | | 1002 | | 1001 | | 1002 | | 1001 | =============================================
Рисунок 3.4 SELECT с дублированием номеров продавцов
Для получения списка без дубликатов, для удобочитаемости, вы можете ввести следующее:
SELECT DISTINCT snum FROM Orders;
Вывод для этого запроса показан на Рисунке 3.5.
Другими словами, DISTINCT следит за тем, какие значения были ранее, чтобы они не дублировались в списке. Это полезный способ избежать избыточности данных, но важно, чтобы при этом вы понимали, что вы делаете. Если вы не хотите потерять некоторые данные, вы не должны безоглядно использовать DISTINCT, потому что это может скрыть какую-то проблему или какие-то важные данные. Например, вы могли бы предположить, что имена всех ваших заказчиков различны. Если кто-то помещает второго Clemens в таблицу Заказчиков, а вы используете SELECT DISTINCT cname, вы не будете даже знать о существовании двойника. Вы можете получить не того Clemens и даже не знать об этом. Так как вы не ожидаете избыточности, в этом случае вы не должны использовать DISTINCT.
УДАЛЕНИЕ ПРЕДСТАВЛЕНИЙ
Синтаксис удаления представления из базы данных подобен синтаксису удаления базовых таблиц:
DROP VIEW <имя представления>
В этом нет необходимости, потому что содержимое представления не является созданным и сохраняется на период действия определенной команды. Базовая таблица, из которой представление выводится, не задействуется, когда представление удалено. Помните, вы должны являться владельцем представления, чтобы иметь возможность удалить его.
УДАЛЕНИЕ СИНОНИМОВ
Общие и другие синонимы могут удаляться командой DROP SYNONYM. Синонимы удаляются их владельцами, кроме общих синонимов, которые удаляются соответствующими привилегированными пользователями, обычно DBA.
Чтобы удалить, например, синоним Clients, когда вместо него уже появился общий синоним Customers, Adrian может ввести
DROP SYNONYM Clients;
Сама таблица Заказчиков, естественно, становится недействующей.
УДАЛЕНИЕ СТРОК ИЗ ТАБЛИЦ
Вы можете удалять строки из таблицы командой модификации DELETE. Она может удалять только строки, а не индивидуальные значения полей, так что параметр поля является необязательным или недоступным. Чтобы удалить всё содержание таблицы Продавцов, вы можете ввести следующее условие:
DELETE FROM Salespeople;
Теперь, когда таблица пуста, её можно окончательно удалить командой DROP TABLE (это объясняется в Главе 17). Обычно вам нужно удалить только некоторые определённые строки из таблицы. Чтобы определить, какие строки будут удалены, вы используете предикат так же, как вы это делали для запросов. Например, чтобы удалить продавца Axelrod из таблицы, вы можете ввести
DELETE FROM Salespeople WHERE snum = 1003;
Мы использовали поле snum вместо поля sname, потому что лучшая тактика - использование первичных ключей, когда вы хотите, чтобы действию подвергалась одна, и только одна, строка. Для вас это аналогично действию первичного ключа. Конечно, вы можете также использовать DELETE с предикатом, который выбирал бы группу строк, как показано в этом примере:
DELETE FROM Salespeople WHERE city = 'London';
УДАЛЕНИЕ ТАБЛИЦЫ
Вы должны быть владельцем (т.е. создателем) таблицы, чтобы иметь возможность удалить её. Поэтому не беспокойтесь о случайном разрушении ваших данных, SQL сначала потребует, чтобы вы очистили таблицу прежде чем удалить её из БД. Таблица с находящимися в ней строками не может быть удалена. Обратитесь к Главе 15 за подробностями относительно того, как удалять строки из таблицы.
Синтаксис для удаления вашей таблицы, если конечно она является пустой, следующий:
DROP TABLE <table name>;
При подаче этой команды имя таблицы больше не распознаётся, и нет такой команды, которая могла бы быть дана этому объекту. Вы должны убедиться, что эта таблица не ссылается внешним ключом к другой таблице (внешние ключи обсуждаются в Главе 19) и что она не используется в определении представления (Глава 20). Эта команда фактически не является частью стандарта ANSI, но она поддерживается и полезна. К счастью, она более проста и, следовательно, более непротиворечива, чем ALTER TABLE. ANSI просто не имеет способа для определения разрушенных или неправильных таблиц.
УЛУЧШЕННЫЙ ПРИМЕР ПОДЗАПРОСА
В возможных прикладных программах подзапросы могут становиться многократно вкладываемыми. Вы можете вкладывать их два или более в одиночный запрос, и даже один внутрь другого. Так как можно рассмотреть небольшой блок, чтобы получить всю картину работы этой команды, вы можете воспользоваться способом в SQL, который может принимать различные команды из большинства других языков.
Вот запрос, извлекающий строки всех продавцов, которые имеют заказчиков с более чем одним текущим заказом. Это не обязательно самое простое решение этой проблемы, но оно предназначено для того, чтобы показать улучшенную логику SQL. Вывод этой информации связывает все три наши типовые таблицы:
SELECT * FROM Salespeople first WHERE EXISTS (SELECT * FROM Customers second WHERE first.snum = second.snum AND 1 < (SELECT COUNT (*) FROM Orders WHERE Orders.cnum = second.cnum));
Вывод для этого запроса показан на Рисунке 12.5.
=============== SQL Execution Log ============ | | | FROM Salespeople first | | WHERE EXISTS | | (SELECT * | | FROM Customers second | | WHERE first.snum = second.snum | | AND 1 < | | (SELECT CONT (*) | | FROM Orders | | WHERE Orders.cnum = second.cnum)); | | ============================================= | | cnum cname city comm | | ----- -------- ---- -------- | | 1001 Peel London 0.17 | | 1002 Serres San Jose 0.13 | | 1007 Rifkin Barselona 0.15 | =============================================
Рисунок 12.5 Использование EXISTS с комплексным подзапросом
Мы могли бы разобрать вышеупомянутый запрос примерно так:
Берём каждую строку таблицы Продавцов как строку-кандидат (внешний запрос) и выполняем подзапросы. Для каждой строки-кандидата из внешнего запроса ставим в соответствие каждую строку из таблицы Заказчиков (средний запрос). Если текущая строка заказчиков не совпадает с текущей строкой продавца (т.е. если first.snum < > second.snum), предикат среднего запроса неправилен. Всякий раз, когда мы находим заказчика в среднем запросе который совпадает с продавцом во внешнем запросе, мы должны рассматривать сам внутренний запрос чтобы определить, будет ли наш средний предикат запроса верен. Внутренний запрос считает число заказов текущего заказчика (из среднего запроса). Если это число больше 1, предикат среднего запроса верен, и строки выбираются. Это делает EXISTS-предикат внешнего запроса верным для текущей строки продавца и означает, что по крайней мере один из текущих заказчиков продавца имеет более чем один заказ.
Если это не кажется достаточно понятным для вас на данной стадии разбора примера, не волнуйтесь. Сложность этого примера хороша, независимо от того, как часто вы будете использовать её в деловой ситуации. Основная цель примеров такого типа состоит в том, чтобы показать вам некоторые возможности, которые могут оказаться в дальнейшем полезными. После работы со сложными ситуациями, подобными этой, простые запросы, которые являются наиболее часто используемыми в SQL, покажутся вам элементарными.
Кроме того, данный запрос, даже если он кажется удобным, довольно изощрён как способ извлечения информации и делает много работы. Он связывает три разные таблицы, чтобы дать вам эту информацию, а если таблиц больше, чем здесь указано, будет трудно получить её напрямую (хотя это не единственный способ, и не обязательно лучший способ в SQL). Возможно, вам нужно увидеть эту информацию относительно регулярной основы - если, например, вы имеете премию в конце недели для продавца, который получил несколько заказов от одного заказчика. В этом случае нужно было бы вывести команду и сохранить её, чтобы использовать снова и снова, по мере того как данные будут меняться (лучше всего сделать это с помощью представления, которое мы будем проходить в Главе 20).
УНИКАЛЬНОСТЬ ИНДЕКСА
Индексу в предыдущем примере, к счастью, не предписывается уникальность, несмотря на наше замечание, что это является одним из назначений индекса. Данный продавец может иметь любое число заказчиков. Однако этого не случится, если мы используем ключевое слово UNIQUE перед ключевым словом INDEX. Поле сnum, в качестве первичного ключа, станет первым кандидатом для уникального индекса:
CREATE UNIQUE INDEX Custid ON Customers (cnum);
УНИКАЛЬНОСТЬ КАК ОГРАНИЧЕНИЕ СТОЛБЦА
Время от времени вам нужно будет убедиться, что все значения, введённые в столбец, отличаются друг от друга. Например, первичные ключи. Если вы помещаете ограничение столбца UNIQUE в поле при создании таблицы, база данных отклонит любую попытку ввода в это поле для одной из строк значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были объявлены как непустые (NOT NULL), так как не имеет смысла позволить одной строке таблицы иметь значение NULL, а затем исключать другие строки с NULL-значениями как дубликаты.
Вот дальнейшее усовершенствование нашей команды создания таблицы Продавцов:
CREATE TABLE Salespeople (Snum integer NOT NULL UNIQUE, Sname char (10) NOT NULL UNIQUE, city char (10), comm decimal);
Когда вы объявляете поле sname уникальным, убедитесь, что две Mary Smith будут введены различными способами, например, Mary Smith и M. Smith. В то же время это не так уж необходимо с функциональной точки зрения, потому что поле snum в качестве первичного ключа всё равно обеспечит отличие этих двух строк, что проще, нежели помнить, что эти Smith не идентичны. Столбцы (не первичные ключи), чьи значения требуют уникальности, называются ключами-кандидатами или уникальными ключами.
УНИКАЛЬНОСТЬ КАК ОГРАНИЧЕНИЕ ТАБЛИЦЫ
Вы можете также определить группу полей как уникальную с помощью команды UNIQUE ограничения таблицы. Объявление группы полей уникальной отличается от объявления уникальными индивидуальных полей, так как это комбинация значений, а не просто индивидуальное значение, которое обязано быть уникальным.
Уникальность группы это представление порядка так, что бы пары строк со значениями столбцов "a", "b" и "b", "a" рассматривались отдельно одна от другой. Наша БД сделана так, чтобы каждый заказчик был назначен одному, и только одному, продавцу. Это означает, что каждая комбинация номера заказчика (cnum) и номера продавца (snum) в таблице Заказчиков должна быть уникальной. Вы можете убедиться в этом, создав таблицу Заказчиков таким способом:
CREATE TABLE Customers (cnum integer NOT NULL, cname char (10) NOT NULL, city char (10), rating integer, snum integer NOT NULL, UNIQUE (cnum, snum));
Обратите внимание, что оба поля в ограничении таблицы UNIQUE всё ещё используют ограничение столбца NOT NULL. Если бы мы использовали ограничение столбца UNIQUE для поля cnum, такое ограничение таблицы было бы необязательным. Если значения поля cnum различны для каждой строки, то не может быть двух строк с идентичной комбинацией значений полей cnum и snum. То же самое получится, если мы объявим поле snum уникальным, хотя это и не будет соответствовать нашему примеру, так как продавец будет назначен многочисленным заказчикам. Следовательно, ограничение UNIQUE таблицы наиболее полезно, когда вы не хотите, чтобы отдельные поля были уникальными.
Предположим, например, что мы разработали таблицу для отслеживания всех заказов каждый день для каждого продавца. Каждая строка такой таблицы представляет сумму чисел любых заказов, а не просто индивидуальный заказ. В этом случае мы могли бы устранить некоторые возможные ошибки, убедившись, что на каждый день имеется не более чем одна строка для данного продавца или что каждая комбинация полей snum и odate является уникальной. Вот как, например, мы могли бы создать таблицу с именем Salestotal:
CREATE TABLE Salestotal (cnum integer NOT NULL, odate date NULL, totamt decimal, UNIQUE (snum, odate));
Кроме того, имеется команда, которую вы будете использовать, чтобы помещать текущие данные в эту таблицу:
INSERT INTO Salestotal SELECT snum, odate, SUM (amt) FROM Orders GROUP BY snum, odate;
UNION И УСТРАНЕНИЕ ДУБЛИКАТОВ
UNION будет автоматически исключать дубликаты строк из вывода. Это нечто, не свойственное SQL, так как одиночные запросы обычно содержат DISTINCT для устранения дубликатов.
Например, запрос, вывод которого показан на Рисунке 14.2,
SELECT snum, city FROM Customers;
имеет двойную комбинацию значений (snum=1001, city=London), потому что мы не указали, чтобы SQL устранил дубликаты. Однако, если мы используем
=============== SQL Execution Log ============ | | | SELECT snum, city | | FROM Customers; | | ============================================= | | snum city | | ----- -------- | | 1001 London | | 1003 Rome | | 1002 San Jose | | 1002 Berlin | | 1001 London | | 1004 Rome | | 1007 San Jose | | | =============================================
Рисунок 14.2 Одиночный запрос с дублированным выводом
UNION в комбинации этого запроса с ему подобным в таблице Продавцов, то эта избыточная комбинация будет устранена. Рисунок 14.3 показывает вывод следующего запроса.
SELECT snum, city FROM Customers
UNION
SELECT snum, city FROM Salespeople.;
=============== SQL Execution Log ============ | | | FROM Customers | | UNION | | SELECT snum, sity | | FROM Salespeople; | | ============================================= | | | | ----- -------- | | 1001 London | | 1002 Berlin | | 1007 San Jose | | 1007 New York | | 1003 Rome | | 1001 London | | 1003 Rome | | 1002 Barcelona | | 1007 San Jose | | | -----------------------------------------------
Рисунок 14.3 UNION устраняет вывод дубликатов
Вы можете получить нечто похожее (в некоторых программах SQL), используя UNION ALL вместо просто UNION, наподобие этого:
SELECT snum, city FROM Customers
UNION ALL
SELECT snum, city FROM Salespeople;
UNION (ОБЪЕДИНЕНИЕ)
Синтаксис
<query> {UNION [ALL] <query> } . . . ;
Вывод двух или более запросов <query> будет объединён. Каждый запрос <query> должен содержать один и тот же номер <value expression> в предложении SELECT и в таком порядке, что 1.. n каждого совместим по типу данных <data type> и размеру <size> с 1.. n всех других.
UPDATE (МОДИФИКАЦИЯ)
Синтаксис
UPDATE <table name>
SET { <column name> = <value expression> } . , . .
{[ WHERE <predlcate>]; }
| {[WHERE CURRENT OF <cursorname>]
<SQL term>]}
UPDATE изменяет значения в каждом столбце <column name> на соответствующее значение <value expression>. Если предложение WHERE использует предикат <predicate>, то только строки таблиц, чьи текущие значения делают этот предикат <predicate> верным, могут быть изменены. Если WHERE использует предложение CURRENT OF, то значения в строке таблицы <table name>, находящиеся в курсоре <cursor name>, меняются. WHERE CURRENT OF пригодно для использования только во вложенном SQL, и только с модифицируемыми курсорами. При отсутствии предложения WHERE, все строки меняются.
УПОРЯДОЧИВАНИЕ АГРЕГАТНЫХ ГРУПП
ORDER BY может, кроме того, использоваться с GROUP BY для упорядочивания групп. При этом ORDER BY всегда идёт последним.
Вот пример из предыдущей главы с добавлением предложения ORDER BY. Перед группированием вывода порядок групп был произвольным; и мы теперь заставим группы размещаться в последовательности:
SELECT snum, odate, MAX (amt) FROM Orders GROUP BY snum, odate ORDER BY snum;
Вывод показан на Рисунке 7.6.
=============== SQL Execution Log ============== | | | SELECT snum, odate, MAX (amt) | | FROM Orders | | GROUP BY snum, odate | | ORDER BY snum ; | | =============================================== | | snum odate amt | | ----- ---------- -------- | | 1001 10/06/1990 767.19 | | 1001 10/05/1990 4723.00 | | 1001 10/05/1990 9891.88 | | 1002 10/06/1990 5160.45 | | 1002 10/04/1990 75.75 | | 1002 10/03/1990 1309.95 | | 1003 10/04/1990 1713.23 | | 1004 10/03/1990 1900.10 | | 1007 10/03/1990 1098.16 | | | ================================================
Рисунок 7.6 Упорядочивание с помощью группы
Так как мы не указывали на возрастание или убывание порядка, возрастание используется по умолчанию.
УПОРЯДОЧИВАНИЕ С ПОМОЩЬЮ НЕСКОЛЬКИХ СТОЛБЦОВ
Мы можем также упорядочивать таблицу с помощью другого столбца, например, с помощью поля amt, внутри упорядочивания поля cnum. (вывод показан в Рисунке 7.5):
SELECT * FROM Orders ORDER BY cnum DESC, amt DESC;
=============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | ORDER BY cnum DESC, amt DESC; | | =============================================== | | onum amt odate cnum snum | | ------ -------- ---------- ----- ----- | | 3006 1098.16 10/03/1990 2008 1007 | | 3001 18.69 10/03/1990 2008 1007 | | 3002 1900.10 10/03/1990 2007 1004 | | 3011 9891.88 10/06/1990 2006 1001 | | 3008 4723.00 10/05/1990 2006 1001 | | 3010 1309.95 10/06/1990 2004 1002 | | 3007 75.75 10/04/1990 2004 1002 | | 3005 5160.45 10/03/1990 2003 1002 | | 3009 1713.23 10/04/1990 2002 1003 | | 3003 767.19 10/03/1990 2001 1001 | | | ================================================
Рисунок 7.5: Упорядочивание вывода с помощью нескольких полей
Вы можете использовать ORDER BY таким способом одновременно с любым числом столбцов. Обратите внимание, что во всех случаях столбцы, которые упорядочиваются, должны быть указаны в выборе SELECT. Это требование ANSI, которое в большинстве случаев, но не всегда, предписано системе. Следующая команда, например, будет запрещена:
SELECT cname, city FROM Customers GROUP BY cnum;
Так как поле cnum не было выбранным полем, GROUP BY не сможет найти его, чтобы использовать для упорядочивания вывода. Даже если ваша система позволяет это, смысл упорядочивания не будет понятен из вывода, так что включение (в предложение SELECT) всех столбцов, используемых в предложении ORDER BY, в принципе желательно.
УПОРЯДОЧИВАНИЕ С ПОМОЩЬЮ NULL
Если имеются пустые значения (NULL) в поле, которое вы используете для упорядочивания вашего вывода, они могут или следовать за, или предшествовать каждому другому значению в поле. Это возможность, которую ANSI оставил для отдельных программ. Программа использует ту или иную форму.
УПОРЯДОЧИВАНИЕ ВЫВОДА ПО НОМЕРУ СТОЛБЦА
Вместо имён столбцов вы можете использовать их порядковые номера для указания поля, используемого при упорядочивании вывода. Эти номера могут ссылаться не на порядок столбцов в таблице, а на их порядок в выводе. Другими словами, поле, упомянутое в предложении SELECT первым, для ORDER BY - поле 1, независимо от того, каким по порядку оно стоит в таблице. Например, вы можете использовать следующую команду, чтобы увидеть определенные поля таблицы Продавцов упорядоченными по убыванию к наименьшему значению комиссионных (вывод показан на Рисунке 7.7):
SELECT sname, comm FROM Salespeople ORDER BY 2 DESC;
=============== SQL Execution Log ============ | | | (SELECT sname, comm | | FROM Salespeople | | ORDER BY 2 DESC; | | ============================================= | | sname comm | | -------- -------- | | Peel 0.17 | | Serres 0.15 | | Rifkin 0.13 | | | ===============================================
Рисунок 7.7 Упорядочивание, использующее номера
Одна из основных целей этого свойства ORDER BY - дать вам возможность использовать GROUP BY со столбцами вывода, так же как и со столбцами таблицы. Столбцы, производимые агрегатной функцией, константы или выражения в предложении SELECT запроса абсолютно пригодны для использования с GROUP BY, если на них ссылаются с помощью номера.
Например, давайте сосчитаем заказы каждого из наших продавцов и выведем результаты в убывающем порядке, как показано в Рисунке 7.8:
SELECT snum, COUNT (DISTINCT onum) FROM Orders GROUP BY snum ORDER BY 2 DESC;
=============== SQL Execution Log ============== | | | SELECT snum, odate, MAX (amt) | | FROM Orders | | GROUP BY snum | | ORDER BY 2 DESC; | | =============================================== | | snum | | ----- ---------- | | 1001 3 | | 1002 3 | | 1007 2 | | 1003 1 | | 1004 1 | | | ================================================
Рисунок 7.8 Упорядочивание с помощью столбца вывода
В этом случае вы должны использовать номер столбца, так как столбец вывода не имеет имени; и вы не должны использовать саму агрегатную функцию. Строго говоря, по правилам ANSI SQL, следующее не будет работать, хотя некоторые системы и пренебрегают этим требованием:
SELECT snum, COUNT (DISTINCT onum) FROM Orders GROUP BY snum GROUP BY COUNTОМ (DISTINCT onum) DESC;
Это будет отклонено большинством систем!
УПОРЯДОЧИВАНИЕ ВЫВОДА ПОЛЕЙ
Как мы подчеркивали, таблицы это неупорядоченные наборы данных, и данные, которые выводятся из них, не обязательно появляются в какой-то определённой последовательности. SQL использует команду ORDER BY, чтобы дать возможность упорядочить вывод. Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Несколько столбцов упорядочиваются один относительно другого так же, как с GROUP BY, и вы можете определять возрастание (ASC) или убывание (DESC) для каждого столбца. По умолчанию установлено возрастание. Давайте рассмотрим нашу таблицу заказа, приводимую в заказ с помощью номера заказчика (обратите внимание на значения в cnum столбце):
SELECT * FROM Orders ORDER BY cnum DESC;
Вывод показан на Рисунке 7.4.
=============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | ORDER BY cnum DESC; | | =============================================== | | onum amt odate cnum snum | | ------ -------- ---------- ----- ----- | | 3001 18.69 10/03/1990 2008 1007 | | 3006 1098.16 10/03/1990 2008 1007 | | 3002 1900.10 10/03/1990 2007 1004 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | | 3007 75.75 10/04/1990 2004 1002 | | 3010 1309.95 10/06/1990 2004 1002 | | 3005 5160.45 10/03/1990 2003 1002 | | 3009 1713.23 10/04/1990 2002 1003 | | 3003 767.19 10/03/1990 2001 1001 | | | ================================================
Рисунок 7.4 Упорядочивание вывода с помощью убывания поля
УСЛОВНЫЕ ОБОЗНАЧЕНИЯ И ТЕРМИНОЛОГИЯ
Ключевые слова это слова, которые имеют специальное значение в SQL. Они могут быть командами, но не текстом и не именами объектов. Мы будем выделять ключевые слова, печатая их ЗАГЛАВНЫМИ БУКВАМИ. Вы должны быть внимательны, чтобы не путать ключевые слова с терминами.
В SQL есть определённые специальные термины, которые используются для его описания. Среди них такие слова как "запрос", "предложение" и "предикат", которые являются важнейшими в описании и понимании языка, но не означают что-нибудь самостоятельное для SQL.
Команды или предложения являются инструкциями, с помощью которых вы обращаетесь к БД SQL.
Команды состоят из одной или более отдельных логических частей, называемых предложениями.
Предложения начинаются ключевым словом и состоят из ключевых слов и аргументов. Например, предложения, с которыми вы можете сталкиваться, это "FROM Salespeope" и "WHERE city = "London"". Аргументы завершают или изменяют значение предложения. В примерах выше, Salespeople - аргумент, а FROM - ключевое слово предложения FROM. Аналогично "city = "London"" - аргумент предложения WHERE.
Объекты это структуры БД, которым даны имена и которые сохраняются в памяти. Сюда относятся базовые таблицы, представления и индексы.
Чтобы показать вам, как формируются команды, мы будем делать это на примерах. Имеется, однако, более формальный метод описания команд, использующий стандартизированные условные обозначения. Мы будем использовать его в последующих главах для удобства, чтобы понимать эти условные обозначения в случае, если вы столкнетесь с ними в других SQL-документах.
Квадратные скобки ( [ ] ) будут указывать части, которые могут не использоваться, а многоточия ( ... ) указывают, что всё, предшествующее им, может повторяться любое число раз. Слова в угловых скобках (< >) - специальные термины, которые объясняют, что они собой представляют. Мы значительно упростили стандартную терминологию SQL, но без ухудшения его понимания.
УСТАНОВКА ЗНАЧЕНИЙ ПО УМОЛЧАНИЮ
Когда вы вставляете строку в таблицу без указания в ней значений для каждого поля, SQL должен иметь значение по умолчанию для включения его в определённое поле, или же команда будет отклонена. Наиболее общим значением по умолчанию является NULL. Это - значение по умолчанию для любого столбца, которому не было дано ограничение NOT NULL или который имеет другое значение по умолчанию. Значение DEFAULT (ПО УМОЛЧАНИЮ) указывается в команде CREATE TABLE тем же способом, что и ограничение столбца, хотя, с технической точки зрения, значение DEFAULT - не ограничительного свойства: оно не ограничивает значения, которые вы можете вводить, а просто определяет, что может случиться, если вы не введёте любое из них.
Предположим, что вы работаете в офисе Нью-Йорка и подавляющее большинство ваших продавцов живут в Нью-Йорке. Вы можете указать Нью-Йорк в качестве значения поля city по умолчанию для вашей таблицы Продавцов:
CREATE TABLE Salespeople (snum integer NOT NULL UNIQUE, sname char(10) NOT NULL UNIQUE, city char(10) DEFAULT = 'New York', comm decimal CHECK (comm < 1);
Конечно, вводить значение Нью-Йорк в таблицу каждый раз, когда назначается новый продавец, не так уж необходимо, и можно просто пренебречь им (не вводя его), даже если оно должно иметь некоторое значение. Значение по умолчанию такого типа более предпочтительно, чем, например, длинный конторский номер, указывающий на ваш собственный офис в таблице Заказов. Длинные числовые значения более предрасположены к ошибке, поэтому, если подавляющее большинство (или все) ваших заказов должны иметь ваш собственный конторский номер, желательно устанавливать для них значение по умолчанию.
Другой способ использования значения по умолчанию - использовать его как альтернативу NULL. Так как NULL (фактически) является false при любом сравнении, ином, нежели IS NULL, он может быть исключён с помощью большинства предикатов.
Иногда вам нужно видеть пустые значения ваших полей, не обрабатывая их каким-то определённым образом. Вы можете установить значения по умолчанию, типа нуль или пробел, которые функционально меньше по значению, чем просто не установленное значение - пустое значение (NULL). Различие между ними и обычным NULL в том, что SQL будет обрабатывать их так же, как и любое другое значение.
Предположим, что заказчикам не назначены оценки изначально. Каждые шесть месяцев вы повышаете оценку всем вашим заказчикам, имеющим оценку ниже средней, включая и тех, кто предварительно не имел никакого назначения оценки. Если вы хотите выбрать всех этих заказчиков как группу, следующий запрос исключит всех заказчиков с оценкой = NULL:
SELECT * FROM Customers WHERE rating < = 100;
Однако, если вы назначили значение по умолчанию = 000, в поле rating, заказчики без оценок будут выбраны наряду с другими. Приоритет каждого метода зависит от ситуации. Если вы будете делать запрос с помощью поля оценки, то захотите ли вы включить строки без значений, или исключите их?
Другая характеристика значений по умолчанию этого типа позволит объявить поле оценки как NOT NULL. Если вы используете его по умолчанию, чтобы избежать значений = NULL, то это, вероятно, хорошая защита от ошибок. Вы можете также использовать ограничения UNIQUE или PRIMARY KEY в этом поле. Если вы сделаете это, то имеете в виду, что только одна строка одновременно может иметь значение по умолчанию. Любую строку, которая содержит значение по умолчанию нужно будет модифицировать, прежде чем другая строка с установкой по умолчанию будет вставлена. Это не так, как при обычном использовании значений по умолчанию, поэтому ограничения UNIQUE и PRIMARY KEY (особенно последнее) обычно не устанавливаются для строк со значениями по умолчанию.
УСТРАНЕНИЕ ИЗБЫТОЧНОСТИ
Обратите внимание, что наш вывод имеет два значения для каждой комбинации, причем второй раз - в обратном порядке. Это потому что каждое значение показано первый раз в каждом псевдониме, а второй раз (симметрично) - в предикате. Следовательно, значение A в псевдониме сначала выбирается в комбинации со значением B во втором псевдониме, а затем значение A во втором псевдониме выбирается в комбинации со значением B в первом псевдониме. В нашем примере, Hoffman выбран вместе с Clemens, а затем Clemens выбран вместе с Hoffman. Тот же самый случай - с Cisneros и Grass, Liu и Giovanni, и так далее. Кроме того, каждая строка была сравнена сама с собой, чтобы вывести строки, такие как Liu и Liu. Простой способ избежать этого - установить порядок на два значения так, чтобы одно могло быть меньше, чем другое, или предшествовать ему в алфавитном порядке. Это делает предикат асимметричным, поэтому те же самые значения в обратном порядке не будут выбраны снова, например:
SELECT first.cname, second.cname, first.rating FROM Customers first, Customers second WHERE first.rating = second.rating AND first.cname < second.cname;
Вывод этого запроса показан на Рисунке 9.2.
Hoffman предшествует Pereira в алфавитном порядке, поэтому комбинация удовлетворяет обоим условиям предиката и появляется в выводе. Когда та же сама комбинация появляется в обратном порядке - когда Pereira в псевдониме первой таблицы сравнивается с Hoffman во второй таблице псевдонима - второе условие не выполняется. Аналогично Hoffman не выбирается при наличии того же рейтинга, что и у него самого, потому что его имя не предшествует ему самому в алфавитном порядке. Если бы вы захотели включить сравнение строк с ними же самими
=============== SQL Execution Log ============== | | | SELECT first.cname, second.cname, first.rating | | FROM Customers first, Customers second | | WHERE first.rating = second.rating | | AND first.cname < second.cname | | =============================================== | | cname cname rating | | ------- --------- ------- | | Hoffman Pereira 100 | | Giovanni Liu 200 | | Clemens Hoffman 100 | | Pereira Pereira 100 | | Gisneros Grass 300 | =================================================
Рисунок 9.2 Устранение избыточности вывода в объединении с собой
в запросах, подобно этому, вы могли бы просто использовать < = вместо <.
ВКЛЮЧЕНИЕ ДУБЛИКАТОВ В АГРЕГАТНЫЕ ФУНКЦИИ
Агрегатные функции могут также (в большинстве реализаций) использовать аргумент ALL, который помещается перед именем поля, подобно DISTINCT, но означает противоположное: включать дубликаты. ANSI технически не позволяет этого для COUNT, но многие реализации ослабляют это ограничение.
Различия между ALL и * при использовании с COUNT:
ALL использует имя поля как аргумент.ALL не может подсчитать NULL-значения.
Пока * является единственным аргументом который включает NULL-значения, и только он используется с COUNT; функции, кроме COUNT, игнорируют NULL-значения в любом случае.
Следующая команда подсчитает (COUNT) число не-NULL-значений в поле rating в таблице Заказчиков (включая повторения):
SELECT COUNT (ALL rating) FROM Customers;
ВНЕШНЕЕ ОБЪЕДИНЕНИЕ
Операция, которая бывает часто полезна, это объединение двух запросов, в котором второй запрос выбирает строки, исключённые первым. Наиболее часто вы будете делать это, чтобы не исключать строки, которые не удовлетворили предикату при объединении таблиц. Это называется внешним объединением.
Предположим, что некоторые из ваших заказчиков ещё не были назначены продавцам. Вы можете захотеть увидеть имена и города всех ваших заказчиков, с именами продавцов, не учитывая тех, кто ещё не был назначен. Вы можете достичь этого, формируя объединение из двух запросов, один из которых выполняет объединение, а другой выбирает заказчиков с пустыми (NULL) значениями поля snum. Этот последний запрос должен вставлять пробелы в поля, соответствующие полю sname в первом запросе. Как и раньше, вы можете вставлять текстовые строки в ваш вывод чтобы идентифицировать запрос, который вывел данную строку.
Использование этой методики во внешнем объединении дает возможность использовать предикаты для классификации, а не для исключения. Мы и раньше использовали пример нахождения продавцов с заказчиками, размещёнными в их городах. Однако, вместо просто выбора только этих строк, вы, возможно, захотите, чтобы ваш вывод перечислял всех продавцов и указывал тех, кто не имел заказчиков в их городах, и кто имел. Следующий запрос, чей вывод показан на Рисунке 14.6, выполнит это:
SELECT Salespeople.snum, sname, cname, comm FROM Salespeople, Customers WHERE Salespeople.city = Customers.city.
UNION
SELECT snum, sname, ' NO MATCH ', comm FROM Salespeople WHERE NOT city = ANY (SELECT city FROM Customers)
ORDER BY 2 DESC;
=============== SQL Execution Log ============ | | | FROM Salespeople | | WHERE NOT city = ANYate) | | (SELECT city | | FROM Customers) | | ORDER BY 2 DESC; | | ============================================= | | | | ----- ------- --------- ------------ | | 1002 Serres Cisneros 0.1300 | | 1002 Serres Liu 0.1300 | | 1007 Rifkin NO MATCH 0.1500 | | 1001 Peel Clemens 0.1200 | | 1001 Peel Hoffman 0.1200 | | 1004 Motika Clemens 0.1100 | | 1004 Motika Hoffman 0.1100 | | 1003 Axelrod NO MATCH 0.1000 | | | ===============================================
Рисунок 14.6 Внешнее объединение
Строка ' NO MATCH ' была дополнена пробелами, чтобы получить совпадение поля cname по длине (не во всех реализациях SQL). Второй запрос выбирает даже те строки, которые исключил первый. Вы можете также добавить комментарий или выражение в ваш запрос в виде дополнительного поля. Если вы сделаете это, вы должны будете добавить некоторый дополнительный комментарий или выражение в той же самой позиции среди выбранных полей для каждого запроса в операции объединения. Совмещение UNION предотвращает добавление дополнительного поля для первого запроса, но не для второго. Вот запрос, который добавляет строки к выбранным полям и указывает, совпадает ли данный продавец с его заказчиком в его городе:
SELECT a.snum, sname, a.city, ' MATCHED ' FROM Salespeople a, Customers b WHERE a.city = b.city
UNION
SELECT snum, sname, city, 'NO MATCH' FROM Salespeople WHERE NOT city = ANY (SELECT city FROM Customers)
ORDER BY 2 DESC;
Рисунок 14.7 показывает вывод этого запроса.
=============== SQL Execution Log ============ | | | WHERE a.city = b.city | | UNION | | SELECT snum,sname,city, 'NO MATCH' | | FROM Salespeople | | WHERE NOT city = ANYate) | | (SELECT city | | FROM Customers) | | ORDER BY 2 DESC; | | ============================================= | | | | ----- ------- ------------ --------- | | 1002 Serres San Jose MATCHED | | 1007 Rifkin Barselona NO MATCH | | 1001 Peel London MATCHED | | 1004 Motika London MATCHED | | 1003 Axelrod New York NO MATCH | | | ===============================================
Рисунок 14.7 Внешнее объединение с полем комментария
Это неполное внешнее объединение, так как оно включает только несовпадающие поля одной из объединяемых таблиц. Полное внешнее объединение должно включать всех заказчиков, имеющих и не имеющих продавцов в их городах. Такое условие будет более полным, как вы это сможете увидеть (вывод следующего запроса показан на Рисунке 14,8):
SELECT snum, city, 'SALESPERSON - MATCH' FROM Salespeople WHERE NOT city = ANY (SELECT city FROM Customers)
UNION
SELECT snum, city, 'SALESPERSON - NO MATCH' FROM Salespeople WHERE NOT city = ANY (SELECT city FROM Customers))
UNION
(SELECT cnum, city, 'CUSTOMER - MATCHED' FROM Customers WHERE city = ANY (SELECT city FROM Salespeople)
UNION
SELECT cnum, city, 'CUSTOMER - NO MATCH' FROM Customers WHERE NOT city = ANY (SELECT city FROM Salespeople))
ORDER BY 2 DESC;
=============== SQL Execution Log =============== | | | FROM Salespeople) | | ORDER BY 2 DESC; | | | | ================================================ | | | | ---- -------- ------------------------ | | 2003 San Jose CUSTOMER - MATCHED | | 2008 San Jose CUSTOMER - MATCHED | | 2002 Rome CUSTOMER - NO MATCH | | 2007 Rome CUSTOMER - NO MATCH | | 1003 New York SALESPERSON - MATCHED | | 1003 New York SALESPERSON - NO MATCH | | 2001 London CUSTOMER - MATCHED | | 2006 London CUSTOMER - MATCHED | | 2004 Berlin CUSTOMER - NO MATCH | | 1007 Barcelona SALESPERSON - MATCHED | | 1007 Barcelona SALESPERSON - NO MATCH | | | ==================================================
Рисунок 14.8 Полное внешнее объединение
(Понятно, что эта формула, использующая ANY, эквивалентна объединению в предыдущем примере.) Сокращённое внешнее объединение, с которого мы начинали, используется чаще, чем последний пример. Этот пример, однако, имеет другой смысл. Всякий раз, когда вы выполняете объединение более чем двух запросов, вы можете использовать круглые скобки, чтобы определить порядок оценки. Другими словами, вместо
query X UNION query Y UNION query Z;
вы должны указать, или
(query X UNION query Y)UNION query Z;
или
query X UNION (query Y UNION query Z);
Это потому, что UNION и UNION ALL могут быть скомбинированы, чтобы удалять одни дубликаты, не удаляя другие.
Предложение
(query X UNION ALL query Y)UNION query Z;
не обязательно воспроизведёт те же результаты, что и предложение
query X UNION ALL(query Y UNION query Z);
если дублирующие строки в нём, будут удалены.
ВНЕШНИЕ КЛЮЧИ, КОТОРЫЕ ССЫЛАЮТСЯ НА СВОИ ПОДЧИНЁННЫЕ ТАБЛИЦЫ
Как было упомянуто ранее, ограничение FOREIGN KEY может представить имя это в частной таблице, как таблице родительского ключа. Будучи далеко не простой, эта особенность может пригодиться.
Предположим, что мы имеем таблицу Employees с полем manager (администратор). Это поле содержит номера каждого из служащих, некоторые из которых являются ещё и администраторами. Но, так как каждый администратор одновременно является служащим, то он, естественно, будет также представлен и в этой таблице.
Давайте создадим таблицу, где номер служащего (столбец с именем empno), объявляется как первичный ключ, а администратор как внешний ключ будет ссылаться на нее:
CREATE TABLE Employees (empno integer NOT NULL PRIMARY KEY, name char(10) NOT NULL UNIOUE, manager integer REFERENCES Employees);
(Так как внешний ключ это ссылаемый первичный ключ таблицы, список столбцов может быть исключен.)
Имеется содержание этой таблицы:
EMPNO NAME MANAGER 1003 Terrence 2007 2007 Atali NULL 1688 McKenna 1003 2002 Collier 2007
Как вы видите, каждый из них (но не Atali) ссылается на другого служащего в таблице как на своего администратора. Atali, имеющий наивысший номер в таблице, должен иметь значение, установленное в NULL. Это дает другой принцип справочной целостности. Внешний ключ, который ссылается обратно на частную таблицу, должен позволять значения = NULL. Если это не так, то как бы вы могли вставить первую строку? Даже если эта первая строка ссылается на саму себя, значение родительского ключа должно уже быть установлено, когда вводится значение внешнего ключа. Этот принцип будет верен, даже если внешний ключ ссылается обратно к частной таблице не напрямую, а с помощью ссылки к другой таблице, которая затем ссылается обратно к таблице внешнего ключа.
Например, предположим, что наша таблица Продавцов имеет дополнительное поле, которое ссылается на таблицу Заказчиков так, что каждая таблица ссылается на другую, как показано в следующем операторе CREATE TABLE:
CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10), comm declmal, cnum integer REFERENCES Customers);
CREATE TABLE Customers ( cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer REFERENCES Salespeople);
Это называется перекрестной ссылкой. SQL поддерживает это теоретически, но практически это может составить проблему. Любая таблица из этих двух, созданная первой, является ссылочной таблицей, которая ещё не существует для другой. В интересах обеспечения перекрестной ссылки, SQL фактически позволяет это, но никакая таблица не будет пригодна для использования, пока они обе находятся в процессе создания.
С другой стороны, если эти две таблицы создаются различными пользователями, проблема становится ещё более трудной. Перекрестна ссылка может стать полезным инструментом, но она не без неоднозначности и опасностей. Предшествующий пример не совсем пригоден для использования, потому что он ограничивает продавца одиночным заказчиком, и, кроме того, совсем не обязательно использовать перекрёстную ссылку, чтобы достичь этого.
Мы рекомендуем чтобы вы были осторожны в его использовании и анализировали, как ваши программы управляют эффектами модификации и удаления, а также процессами привилегий и диалоговой обработки запросов, перед тем как вы создаёте перекрестную систему справочной целостности. (Привилегии и диалоговая обработка запросов будут обсуждаться, соответственно, в Главах 22 и 23.)
ВНЕШНИЙ КЛЮЧ И РОДИТЕЛЬСКИЙ КЛЮЧ
Когда все значения в поле одной таблицы представлены в поле другой таблицы, мы говорим, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей. Например, каждый из заказчиков в таблице Заказчиков имеет поле snum, которое указывает на продавца, назначенного в таблице Продавцов. Для каждого заказа в таблице Заказов имеется один, и только этот, продавец и один, и только этот, заказчик. Это отображается с помощью полей snum и cnum в таблице Заказов.
Когда одно поле в таблице ссылается на другое, оно называется внешним ключом, а поле, на которое оно ссылается, называется родительским ключом. Так что поле snum таблицы Заказчиков это внешний ключ, а поле snum, на которое оно ссылается в таблице Продавцов, это родительский ключ.
Аналогично, поля cnum и snum таблицы Заказов это внешние ключи, которые ссылаются на их родительские ключи с именами в таблице Заказчиков и в таблице Продавцов. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми, это только соглашение, которому мы следуем, чтобы сделать соединение более понятным.
ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ СТОЛБЦОВ
Вариант ограничения столбца ограничением FOREIGN KEY по-другому называется ссылочное ограничение (REFERENCES), так как оно фактически не содержит в себе слов FOREIGN KEY, а просто использует слово REFERENCES и далее имя родительского ключа, как здесь:
CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10), city char(10), snum integer REFERENCES Salespeople (snum));
Поле Customers.snum определяется как внешний ключ, у которого родительский ключ - Salespeople.snum. Это эквивалентно такому ограничению таблицы:
FOREIGN KEY (snum) REGERENCES Salespeople (snum)
ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ ТАБЛИЦЫ
Синтаксис ограничения таблицы FOREIGN KEY:
FOREIGN KEY <column list> REFERENCES <pktable> [<column list>]
Первый список столбцов это список из одного или более столбцов таблицы, которые разделены запятыми и будут созданы или изменены этой командой.
Pktable это таблица, содержащая родительский ключ. Она может быть таблицей, которая создаётся или изменяется текущей командой.
Второй список столбцов это список столбцов, которые будут составлять родительский ключ.
Списки двух столбцов должны быть совместимы, т.е.:
* Они должны иметь одинаковое число столбцов.
* В данной последовательности первый, второй, третий, и т.д. столбцы списка столбцов внешнего ключа должны иметь те же типы данных и размеры, что и первый, второй, третий, и т.д. столбцы списка столбцов родительского ключа. Столбцы в списках обоих столбцов не должны иметь одинаковых имён, хотя мы и использовали такой способ в наших примерах чтобы сделать связь более понятной.
Создадим таблицу Заказчиков с полем snum, определённым в качестве внешнего ключа, ссылающегося на таблицу Продавцов:
CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY cname char(10), city char(10), snum integer, FOREIGN KEY (snum) REFERENCES Salespeople (snum);
Имейте в виду, что, при использовании ALTER TABLE вместо CREATE TABLE для применения ограничения FOREIGN KEY, значения, которые вы указываете во внешнем ключе и родительском ключе, должны быть в состоянии справочной целостности. Иначе команда будет отклонена. Хотя ALTER TABLE очень полезна из-за её удобства, вы должны будете в вашей системе, по возможности, каждый раз сначала формировать структурные принципы, типа справочной целостности.
ВСТАВКА NULL
Если вам нужно ввести пустое значение (NULL), вы вводите его точно так же, как и обычное значение. Предположим, что ещё нет поля city для мистера Peel. Вы можете вставить в строку значение=NULL в это поле следующим образом:
INSERT INTO Salespeople VALUES (1001, 'Peel', NULL, .12);
Так как значение NULL это специальный маркёр, а не просто символьное значение, оно не заключается в одинарные кавычки.
ВСТАВКА РЕЗУЛЬТАТОВ ЗАПРОСА
Вы можете также использовать команду INSERT, чтобы получать или выбирать значения из одной таблицы и помещать их в другую для использования их вместе с запросом. Чтобы сделать это, вы просто заменяете предложение VALUES (из предыдущего примера) на соответствующий запрос:
INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = 'London';
Здесь выбираются все значения произведённые запросом - то есть все строки из таблицы Продавцов со значениями city = "London" - и помещаются в таблицу Londonstaff. Чтобы это работало, таблица Londonstaff должна отвечать следующим условиям:
она должна уже быть создана командой CREATE TABLE;
она должна иметь четыре столбца, которые совпадают с таблицей Продавцов типами данных; то есть первый, второй и т.д. столбцы каждой таблицы должны иметь одинаковый тип данных (причем они не должны иметь одинаковых имён).
Общее правило таково, что вставляемые столбцы таблицы должны совпадать со столбцами, выводимыми подзапросом, в данном случае - для всей таблицы Продавцов.
Londonstaff это теперь независимая таблица, которая получила некоторые значения из таблицы Продавцов (Salespeople). Если значения в таблице Продавцов будут вдруг изменены, это никак не отразится на таблице Londonstaff (хотя вы могли бы создать такой эффект с помощью Представления (VIEW), описанного в Главе 20).
Так как или запрос, или команда INSERT могут указывать столбцы по имени, вы можете, если захотите, переместить только выбранные столбцы, а также переупорядочить только те столбцы, которые вы выбрали.
Предположим, например, что вы решили сформировать новую таблицу с именем Daytotals, которая просто будет следить за общим количеством долларов сумм приобретений, упорядоченных по каждому дню. Вы можете ввести эти данные независимо от таблицы Заказов, но сначала вы должны заполнить таблицу Daytotals информацией, ранее представленной в таблице Заказов. Учитывая, что таблица Заказов охватывает последний финансовый год, а не только несколько дней, как в нашем примере, вы можете увидеть преимущество использования следующего условия INSERT в подсчёте и вводе значений:
INSERT INTO Daytotals (date, total) SELECT odate, SUM (amt) FROM Orders GROUP BY odate;
Обратите внимание, что, как предложено ранее, имена столбцов таблицы Заказов и таблицы Daytotals не должны быть одинаковыми. Кроме того, если дата приобретения и общее количество это единственные столбцы в таблице и они находятся в данном заказе, их имена могут быть исключены из вывода из-за их очевидной простоты.
Это язык, который дает возможность
SQL (произносится обычно "СЭКВЭЛ" (или, более англообразно - СКЬЮЭЛ)) означает Структурированный Язык Запросов.
Это язык, который дает возможность создавать реляционные базы данных (и работать с ними), которые представляют собой наборы связанной информации, сохраняемой в таблицах.
Мир БД становится всё более единым, что привело к необходимости создания стандартного языка, который мог бы использоваться для функционирования в большом количестве различных видов компьютерных сред. Стандартный язык даст возможность пользователям, знающим один набор команд, использовать их, чтобы создавать, отыскивать, изменять и передавать информацию, независимо от того, где идёт работа: на персональном компьютере, сетевой рабочей станции или на универсальной ЭВМ.
В нашем, всё более и более взаимосвязанном компьютерном мире, пользователь, снабжённый таким языком, имеет огромное преимущество в использовании и обобщении информации из ряда источников с помощью большого количества способов.
Элегантность и независимость от специфики компьютерных технологий, а также его поддержка лидерами промышленности в области технологии РБД, сделали SQL (и, вероятно, в течение обозримого будущего, оставят его) основным стандартным языком БД. По этой причине любой, кто хочет работать с базами данных 90-х годов (прошлого века), должен знать SQL.
Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов) и в данное время также принимается ISO (Международной организацией по стандартизации). Однако большинство коммерческих программ БД расширяют SQL без уведомления ANSI, добавляя разные особенности в этот язык, которые, как они считают, будут весьма полезны.
Иногда это несколько нарушает стандарт языка, хотя хорошие идеи имеют тенденцию развиваться и становиться стандартами рынка в силу полезности своих качеств.
В этой книге мы будем в основном следовать стандарту ANSI, но одновременно иногда будем давать и некоторые наиболее распространённые отклонения от его стандарта.
Вы должны проконсультироваться в документации вашего пакета программ, который будете использовать, чтобы знать, где в нём этот стандарт видоизменен.
ПРЕЖДЕ ЧЕМ ВЫ СМОЖЕТЕ ИСПОЛЬЗОВАТЬ SQL, вы должны понять, что такое реляционные базы данных.
В этой главе мы объясним и покажем, насколько РБД полезны. Мы не будем обсуждать SQL именно здесь, и, если вы уже знаете эти понятия достаточно хорошо, вы можете просто пропустить эту главу.
В любом случае вы должны просмотреть три таблицы, которые предоставляются и объясняются в конце главы; они станут основой наших примеров в этой книге. Вторая копия этих таблиц находится в Приложении E, и мы рекомендуем скопировать их для удобства ссылки к ним.
ВВОД ЗНАЧЕНИЙ
Все значения в SQL вводятся с использованием команды модификации INSERT.
В самой простой форме INSERT использует следующий синтаксис:
INSERT INTO
VALUES (<value>, . . .);
Так, например, чтобы ввести строку в таблицу Продавцов, вы можете использовать следующее условие:
INSERT INTO Salespeople VALUES (1001, 'Peel', 'London', .12);
Команды DML не производят никакого вывода, но ваша программа должна дать вам некоторое подтверждение того, что данные были использованы.
Имя таблицы (в нашем случае - Salespeople (Продавцы)) должно быть предварительно определено в команде CREATE TABLE (см. Главу 17), а каждое значение в предложении значений должно совпадать с типом данных столбца, в который оно вставляется. В ANSI эти значения не могут являться выражениями, что означает, что 3 разрешено, а выражение 2 + 1 - нет. Значения, конечно же, вводятся в таблицу в поимённом порядке, поэтому первое значение с именем автоматически попадает в столбец (поле) 1, второе в столбец 2, и так далее.
ВЫБИРАЙТЕ ВСЕГДА САМЫЙ ПРОСТОЙ СПОСОБ
Если вы хотите видеть все столбцы таблицы, имеется необязательное сокращение, которое вы можете использовать. Звёздочка (*) может применяться для вывода полного списка столбцов следующим образом:
SELECT * FROM Salespeople;
Это приведет к тому же результату, что и наша предыдущая команда.
ВЫБОР СТОЛБЦОВ С ПОМОЩЬЮ EXISTS
В вышеупомянутом примере, EXISTS должен быть установлен так, чтобы легко выбрать один столбец, вместо того чтобы выбирать все столбцы, используя в выборе звёздочку (SELECT *). В этом состоит его отличие от подзапроса, который (как вы видели ранее в Главе 10, мог выбрать только один столбец). Однако в принципе он мало отличается при выборе EXISTS-столбцов или когда выбираются все столбцы, потому что он просто замечает, выполняется или нет вывод из подзапроса, а не использует выведенные значения.
WHENEVER (ВСЯКИЙ РАЗ КАК)
Синтаксис
EXEC SQL WHENEVER <SQLcond> <actlon> <SQL term>
<SQLcond> :: = SQLERROR | NOT FOUND | SQLWARNING
(последнее - нестандартное)
<action> :: = CONTINUE | GOTO <target> | GOTO <target>
<target> :: = зависит от главного языка
ЗАЧЕМ ВКЛАДЫВАТЬ SQL?
Хотя мы и потратили некоторое время на то, чтобы показать возможности SQL, но, если вы - опытный программист, вы, вероятно, отметили, что сам по себе он не очень полезен при написании программ. Самое очевидное ограничение - то, что, в то время как SQL может сразу выполнить пакет команды, интерактивный SQL в основном выполняет по одной команде в каждый момент времени.
Логические конструкции типа if ... then ("если ... то"), for ... do ("для ... выполнить") и while ... repeat ("пока ... повторять"), используемые для структур большинства компьютерных программ, здесь отсутствуют, так что вы не сможете принять решение - выполнять ли, как выполнять или как долго выполнять одно действие в результате другого действия. Кроме того, интерактивный SQL не может делать ничего со значениями, кроме ввода их в таблицу, размещения или распределения их с помощью запросов и, конечно, вывода их на какое-то устройство.
Более традиционные языки, однако, разработаны так, чтобы программист мог начинать обработку данных и, основываясь на её результатах, решать, делать ли то действие или другое, или же повторять действие до тех пор, пока не встретится некоторое условие, создавая логические маршруты и циклы. Значения сохраняются в переменных, которые могут использоваться и изменяться с помощью любого числа команд. Это даёт вам возможность указывать пользователям на ввод или вывод этих команд из файла и возможность форматировать вывод сложными способами (например, преобразовывать числовые данные в диаграммы).
Цель вложенного SQL состоит в том, чтобы объединить эти возможности, позволяющие вам создавать сложные процедурные программы, которые адресуют базу данных (БД) посредством SQL, позволяя устранить сложные действия в таблицах на процедурном языке, который не ориентирован на такую структуру данных, в то же время поддерживая структурную строгость процедурного языка.
ЗНАЧЕНИЯ, КОТОРЫЕ ПОДЗАПРОС МОЖЕТ ВЫВОДИТЬ
Скорее всего, было бы удобнее, чтобы наш подзапрос в предыдущем примере возвращал одно, и только одно, значение.
Имея выбранное поле snum " WHERE city = "London" вместо "WHERE sname = 'Motika", можно получить несколько различных значений. Это может сделать в предикате основного запроса невозможным оценку верности или неверности, и команда выдаст ошибку.
При использовании подзапросов в предикатах, основанных на реляционных операциях (уравнениях или неравенствах, как объяснено в
Главе 4), вы должны убедиться, что использовали подзапрос, который будет выдавать одну, и только одну, строку вывода. Если вы используете подзапрос, который не выводит никаких значений вообще, команда не потерпит неудачи, но основной запрос не выведет никаких значений. Подзапросы, которые не производят никакого вывода (или нулевой вывод), вынуждают рассматривать предикат ни как верный, ни как неверный, а как неизвестный. Однако неизвестный предикат имеет тот же самый эффект, что и неверный: никакие строки не выбираются основным запросом (смотри в Главе 5 подробную информацию о неизвестном предикате).
Вот пример плохой стратегии:
SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE city = Barcelona);
Поскольку мы имеем только одного продавца в Barcelona - Rifkin, то подзапрос будет выбирать одиночное значение snum, и, следовательно, будет принят. Но это только в данном случае. Большинство БД SQL имеют многочисленных пользователей, и, если другой пользователь добавит нового продавца из Barcelona в таблицу, подзапрос выберет два значения, и ваша команда потерпит неудачу.
