До этой главы мы запрашивали таблицы данных и выполняли команды по извлечению этих данных, считая, что эти таблицы уже были созданы кем-то до нас.
Это действительно наиболее реальная ситуация, когда несколько человек создают таблицы, которые затем используются другими людьми. Наша цель состоит в том, чтобы, охватив информацию сначала более широко, перейти затем к более узким вопросам.
В этой главе мы будем обсуждать создание, изменение и удаление таблиц. Всё это относится к самим таблицам, а не к данным, которые в них содержатся. Будете вы или не будете выполнять эти операции самостоятельно, но их концептуальное понимание увеличит ваше понимание языка SQL и природу таблиц.
Эта глава вводит нас в область SQL, называемую DDL (Язык Определения Данных), где создаются объекты данных SQL.
Эта глава также покажет другой вид объектов данных SQL - Индекс. Индексы используются, чтобы сделать поиск более эффективным и (иногда) заставлять значения отличаться друга от друга. Они обычно работают незаметно для вас, но если вы попробуете поместить значения в таблицу и они будут отклонены из-за их неуникальности, это будет означать, что другая строка имеет то же самое значение для этого поля и что это поле имеет уникальный индекс или ограничение, которое предписывает ему уникальность. Обсуждение этого продолжится в Главе 18.
Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу - таблицу без строк. Значения вводятся с помощью DML-команды INSERT (См. Главу 15). Команда CREATE TABLE определяет имя таблицы и описание набора имён столбцов, указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.
Синтаксис команды CREATE TABLE:
CREATE TABLE table-name
(<column name> <data type>[(size)],
<column name> <data type> [(size)] ...);
Как сказано в Главе 2, типы данных значительно меняются от программы к программе. Для совместимости со стандартом, все они должны по крайней мере поддерживать стандарт типа ANSI. Он описан в Приложении B.
Так как пробелы используются для разделения частей команды SQL, они не могут быть частью имени таблицы (или любого другого объекта, такого как индекс).
Знак подчеркивания ( _ ) обычно используется для
разделения слов в именах таблиц.
Значение аргумента размера зависит от типа
данных. Если вы его не указываете, ваша система сама будет назначать значение
автоматически. Для числовых значений, это - лучший выход, потому что в этом
случае все ваши поля такого типа получат один и тот же размер, что освобождает
вас от проблем их совместимости (см. Главу 14).
Кроме того, использование аргумента размера с некоторыми числовым наборами - не
совсем простой вопрос. Если вам нужно хранить большие числа, вам, несомненно,
понадобятся гарантии, что поля достаточно велики, чтобы вместить их.
Тип данных, для которого вы, в основном, должны назначать размер, это CHAR.
Аргумент размера это целое число, определяющее максимальное число символов, которые может вместить поле. Фактически число символов поля может быть от нуля (если поле - NULL) до этого числа. По умолчанию аргумент размера = 1, что означает, что поле может содержать только одну букву. Это, конечно, не совсем то, что вам нужно.
Таблицы принадлежат пользователю, который их создал, и имена всех таблиц, принадлежащих данному пользователю, должны отличаться друга от друга, как и имена всех столбцов внутри данной таблицы. Отдельные таблицы могут использовать одинаковые имена столбцов, даже если они принадлежат одному и тому же пользователю. Пример этого - столбец city в таблице Заказчиков и в таблице Продавцов. Пользователи, не являющиеся владельцами таблиц, могут обращаться к этим таблицам с помощью имени владельца этих таблиц, сопровождаемого точкой; например, таблица Employees, созданная Smith, будет называться Smith.Employees, когда она упоминается каким-то другим пользователем (мы понимаем, что Smith это идентификатор (ID). ID, сообщаемый пользователем (ваш ID - это ваше имя в SQL. Этот вывод обсуждался в Главе 2 и будет продолжен в Главе 22).
Эта команда создаст таблицу Продавцов:
CREATE TABLE Saleepeople
(snum integer,
sname char (10),
city char (10),
comm declmal);
Порядок столбцов в таблице определяется порядком, в котором они указаны. Имена столбца не должны разделяться при переносе строки (что делается для удобочитаемости) и отделяются запятыми.
Индекс это упорядоченный (буквенный или числовой) список столбцов или групп столбцов в таблице. Таблицы могут иметь большое количество строк, а, так как строки не находятся в каком-нибудь определенном порядке, их поиск по указанному значению может потребовать времени. Индексный адрес это и забота, и в то же время обеспечение способа объединения всех значений в группы из одной или больше строк, которые отличаются одна от другой.
В Главе 18 мы будем описывать более непосредственный способ, который заставит ваши значения быть уникальными. Но этот метод не существует в ранних версиях SQL. Так как уникальность часто необходима, индексы и использовались с этой целью. Индексы это средство SQL, которое породил сам рынок, а не ANSI. Поэтому сам по себе стандарт ANSI в настоящее время не поддерживает индексы, хотя они очень полезны и широко применяются.
Когда вы создаёте индекс в поле, ваша база данных (БД) запоминает соответствующий порядок всех значений этого поля в области памяти. Предположим, что наша таблица Заказчиков имеет тысячи входов, а вы хотите найти заказчика с номером=2999. Так как строки не упорядочены, ваша программа будет просматривать всю таблицу, строку за строкой, проверяя каждый раз значение поля cnum на равенство значению 2999. Однако, если бы имелся индекс в поле cnum, то программа могла бы выйти на номер 2999 прямо по индексу и дать информацию о том, как найти правильную строку таблицы.
Хотя индекс значительно улучшает эффективность запросов, использование индекса несколько замедляет операции модификации DML (такие как INSERT и DELETE), а сам индекс занимает память. Следовательно, каждый раз, когда вы создаёте таблицу, вы должны принять решение, индексировать её или нет.
Индексы могут состоять из нескольких полей. Если больше чем одно поле указывается для одного индекса, второе упорядочивается внутри первого, третье внутри второго, и так далее. Если вы имели первое и последнее имя в двух различных полях таблицы, вы могли бы создать индекс, который упорядочил бы предыдущее поле внутри последующего. Это может быть выполнено независимо от способа упорядочивали столбцов в таблице.
Синтаксис для создания индекса обычно следующий (помните, что это не ANSI-стандарт):
CREATE INDEX ON (column name [,column name]...);
Таблица, конечно, должна уже быть создана и должна содержать имя столбца. Имя индекса не может быть использовано для чего-то другого в БД (любым пользователем). Однажды созданный, индекс будет невидим пользователю. SQL сам решает, когда он необходим, чтобы ссылаться на него, и делает это автоматически. Если, например, таблица Заказчиков будет наиболее часто упоминаться в запросах продавцов к их собственной клиентуре, было бы правильно создать такой индекс в поле snum таблицы Заказчиков:
CREATE INDEX Clientgroup ON Customers (snum);
Теперь тот продавец, который имеет отношение к этой таблице, сможет найти собственную клиентуру очень быстро.
Индексу в предыдущем примере, к счастью, не предписывается уникальность, несмотря на наше замечание, что это является одним из назначений индекса. Данный продавец может иметь любое число заказчиков. Однако этого не случится, если мы используем ключевое слово UNIQUE перед ключевым словом INDEX. Поле сnum, в качестве первичного ключа, станет первым кандидатом для уникального индекса:
CREATE UNIQUE INDEX Custid ON Customers (cnum);
Предыдущий пример - косвенный способ заставить поле cnum работать как первичный ключ таблицы Заказчиков. Базы данных воздействуют на первичные и другие ключи более непосредственно. Мы будем обсуждать это далее в Главах 18 и 19.
Главным признаком индекса является его имя, поэтому он может быть удален. Обычно пользователи не знают о существовании индекса. SQL автоматически определяет, разрешено ли пользователю использовать индекс, и, если да, разрешает использовать его. Однако, если вы хотите удалить индекс, вы должны знать его имя.
Синтаксис для удаления индекса:
DROP INDEX index name;
Удаление индекса не влияет на содержимое полей.
Команда ALTER TABLE не является частью стандарта ANSI, но это широко распространённая и довольно содержательная форма, хотя её возможности несколько ограничены. Она используется для того, чтобы изменить определение существующей таблицы. Обычно она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также, в некоторых программах, добавлять или удалять ограничения (рассмотренные в Главе 18). Типичный синтаксис добавления столбца к таблице:
ALTER TABLE
ADD <column name> <data type> <size>;
Столбец будет добавлен со значениями NULL для всех строк таблицы. Новый столбец станет последним по порядку столбцом таблицы. Вообще-то можно добавить сразу несколько новых столбцов, отделив их запятыми, в одной команде. Имеется возможность удалять или изменять столбцы. Наиболее частым изменением столбца может быть просто увеличение его размера или добавление (удаление) ограничения.
Ваша система должна убедиться, что любые изменения не противоречат существующим данным: например, при попытке добавить ограничение к столбцу, который уже имел значение, при нарушении которого ограничение будет отклонено. Лучше всего дважды проверить это. Как минимум - посмотрите документацию вашей системы чтобы убедиться, гарантирует ли она, что именно это было причиной. Из-за нестандартного характера команды ALTER TABLE вам всё равно необходимо посмотреть тот раздел вашей системной документации, где говорится об особых случаях.
ALTER TABLE не действует, когда таблица должна быть переопределена, но вы должны разрабатывать вашу БД по возможности так, чтобы не слишком ей в этом доверяться. Изменять структуры таблицы, когда она уже в использовании, опасно! Просмотрите внимательно таблицы, которые, являясь вторичными таблицами с извлеченными данными из другой таблицы (см. Главу 20), не долго будут работать правильно, а программы, использующие вложенный SQL (Глава 25), начнут работать неправильно или не всегда правильно. Кроме того, изменение может стереть всех пользователей, имеющих разрешение на обращение к таблице.
По этим причинам вы должны разрабатывать ваши таблицы так, чтобы использовать ALTER TABLE только в крайнем случае. Если ваша система не поддерживает ALTER TABLE, или если вы хотите избежать её использования, вы можете просто создать новую таблицу, с необходимыми изменениями при создании, и использовать команду INSERT с SELECT * запросом чтобы переписать в нее данные из старой таблицы. Пользователям, которым был предоставлен доступ к старой таблице (см. Главу 22), должен быть предоставлен доступ к новой таблице.
Вы должны быть владельцем (т.е. создателем) таблицы, чтобы иметь возможность удалить её. Поэтому не беспокойтесь о случайном разрушении ваших данных, SQL сначала потребует, чтобы вы очистили таблицу прежде чем удалить её из БД. Таблица с находящимися в ней строками не может быть удалена. Обратитесь к Главе 15 за подробностями относительно того, как удалять строки из таблицы.
Синтаксис для удаления вашей таблицы, если конечно она является пустой, следующий:
DROP TABLE <table name>;
При подаче этой команды имя таблицы больше не распознаётся, и нет такой команды, которая могла бы быть дана этому объекту. Вы должны убедиться, что эта таблица не ссылается внешним ключом к другой таблице (внешние ключи обсуждаются в Главе 19) и что она не используется в определении представления (Глава 20). Эта команда фактически не является частью стандарта ANSI, но она поддерживается и полезна. К счастью, она более проста и, следовательно, более непротиворечива, чем ALTER TABLE. ANSI просто не имеет способа для определения разрушенных или неправильных таблиц.
Теперь вы уже бегло ориентируетесь в основах определений данных. Вы можете создавать, изменять и удалять таблицы. В то время как только первая из этих функций - часть официального стандарта SQL, другие будут время от времени меняться, особенно ALTER TABLE.
DROP TABLE позволяет вам избавиться от ненужных таблиц. Она уничтожает только пустые таблицы и, следовательно, не разрушает данные.
Вы теперь знаете об индексах, а также, как их создавать и удалять. SQL не даёт вам большого контроля над ними, так как реализация, которую вы используете, сама определяет, как быстро выполняются различные команды. Индексы это один из инструментов, дающий вам возможность воздействовать непосредственно на эффективность ваших команд в SQL. Мы рассмотрели индексы здесь, чтобы отличать их от ограничений, с которыми их нельзя путать. Ограничения это тема Главы 18 и Главы 19.
1. Напишите предложение CREATE TABLE, которое вывело бы нашу таблицу Заказчиков. 2. Напишите команду, которая давала бы возможность пользователю быстро извлекать заказы, сгруппированные по датам, из таблицы Заказов. 3. Если таблица Заказов уже создана, как вы можете заставить поле onum быть уникальным (если допустить что все текущие значения уникальны)? 4. Создайте индекс, который разрешал бы каждому продавцу быстро отыскивать его заказы, сгруппированные по датам. 5. Предположим, что каждый продавец имеет только одного заказчика с данной оценкой. Введите команду, которая его извлечет. (См. ответы в Приложении A.)