Создание таблиц и работа с ними

17. Создание таблиц и работа с ними

17.1.  Создание таблиц

Язык SQL используется не только для манипулирования с данными, но и позволяет создавать таблицы и работать с ними.

Существует два способа создания таблиц:

· большинство СУБД имеют инструментарий администратора, который можно использовать для интерактивного создания таблиц базы данных и управления ими;

· таблицы можно создавать и манипулировать ими посредством операторов языка SQL.

Для создания таблиц программным способом используется оператор CREATE TABLE. При использовании интерактивного инструментария в действительности вся работа выполняется операторами SQL.

Синтаксис оператора CREATE TABLE может различаться для разных реализаций SQL.

Чтобы создать таблицу с помощью оператора CREATE TABLE нужно указать следующие данные:

Рекомендуемые материалы

· имя новой таблицы, которое вводится после слов CREATE TABLE;

· имена и определения столбцов таблицы, разделенные запятыми;

· в некоторых СУБД также требуется, чтобы было указано место размещения таблицы.

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

CREATE TABLE Products1

(

  prod_id CHAR(10) NOT NULL,

  vend_id CHAR(10) NOT NULL,

  prod_name CHAR(254) NOT NULL,

  prod_price CURRENCY NOT NULL,

  prod_desc VARCHAR(255) NULL

);

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

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

17.2.  Типы данных

Типы данных и их название являются одним из основных источников несовместимости в SQL. К сожалению, в разных СУБД используются разные типы данных. Основные типы данных обычно поддерживаются всеми СУБД. Но даже если названия типа данных звучит одинаково, пониматься под одним и тем же типом данных в разных СУБД может не одно и то же.

В Access 2007 предусмотрено 10 типов данных (раньше было 9)

Рассмотрим применяемые типы данных в СУБД и их соответствие в Access.

Строковые данные  

Часто используются данные типа строки, строковые данные. Строки могут быть двух типов – строки фиксированной длины и строки переменной длины.

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

В строках переменной длины можно хранить столько символов, сколько позволяет максимально хранить данная СУБД. В строках сохраняются только указанные данные и никаких дополнительных.

Поля с фиксированной длиной обеспечивают:

· повышение производительности при сортировке и манипулировании данными;

· позволяют индексировать столбцы, многие СУБД не способны индексировать столбцы с данными переменной длины.

Строковые данные

 

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

Числовой тип данных  

Числовые типы данных предназначены для хранения чисел. В большинстве СУБД поддерживаются многие числовые типы данных, каждый из которых предназначен для хранения чисел определенного диапазона.

Числовые типы данных

Денежный тип данных  

Денежный тип данных

Типы данных даты и времени  

Типы данных даты и времени

Не существует стандартного способа указания даты, который подходил бы к любой СУБД. В большинстве реализаций приемлем формат типа 2009-03-20 или MAR 20th 2009.

Поскольку в каждый СУБД используется свой формат представления даты, ODBC (Open DataBase Connectivity - программный интерфейс (API) доступа к базам данных) создал свой собственный формат , который способен работать с любой СУБД при использовании ODBC. Формат ODBC выглядит так: {d ‘2009-03-20’} для значений дат, {t ’21:46:29’} для значений времени и {ts ‘2009-03-20 21:46:29’} для значений даты и времени.

Двоичные типы данных  

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

Двоичные типы данных

17.3.  Работа со значениями NULL

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

Каждый столбец таблицы может быть или пустым (NULL), или не пустым (NOT NULL), и это его состояние оговаривается в определении таблицы во время ее создания.

Рассмотрим пример.

            CREATE TABLE Orders1

            (

              order_num INTEGER NOT NULL,

              order_date DATETIME NOT NULL,

              cust_id CHAR(10) NOT NULL

            );

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

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

            CREATE TABLE Vendors1

            (

              vend_id CHAR(10) NOT NULL,

              vend_name CHAR(50) NOT NULL,

              vend_address CHAR(50),

              vend_city CHAR(50),

              vend_state CHAR(5),

              vend_ZIP CHAR(10),

              vend_country CHAR(50)

            );

В случае допуска в столбце NULL описатель NULL можно не указывать. Значение NULL является значением по умолчанию.

Во многих СУБД отсутствие ключевых слов NOT NULL трактуется как NULL. С другой стороны, например, в СУБД DB2 наличие ключевого слова NULL является обязательным.

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

17.4.  Определение значений по умолчанию

Язык SQL позволяет определять значения по умолчанию, которые будут использованы в том случае, если при добавлении строки какое-то ее значение не указано. Значения по умолчанию определяются с помощью ключевого слова DEFAULT в определениях столбца оператора CREATE TABLE.

Рассмотрим пример.

            CREATE TABLE OrderItems1

            (

              order_num INTEGER NOT NULL,

              order_item INTEGER NOT NULL,

              prod_id CHAR(10) NOT NULL,

              quantity INTEGER NOT NULL DEFAULT 1,

              item_price DECIMAL(8,2) NOT NULL

            );

Столбец quantity содержит количество каждого предмета в заказе. DEFAULT 1 в описании столбца предписывает СУБД указывать количество, равное 1, если не указано иное.

Значение по умолчанию часто используется для хранения в столбцах даты и денежных единиц. Например, системная дата может быть использована как дата по умолчанию путем указания функции или переменной, используемой для ссылки на системную дату. Например, в SQL Server – DEFAULT GETDATE(), в MySQL – DEFAULT CURRENT_DATE().

 В Access DEFAULT не работает.

17.5.  Обновление таблиц

Для обновления определения таблицы, следует воспользоваться оператором ALTER TABLE. Этот оператор позволяет изменить таблицу, при этом необходимо учитывать.

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

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

· Многие СУБД не позволяют удалять или изменять столбцы в таблице.

· Большинство СУБД разрешабт переименовывать столбцы.

· Многие СУБД налагают серьезные ограничения на изменения, которые могут быть сделаны по отношению к заполненным столбцам, и несколько меньшие – по отношению к незаполненным.

Чтобы изменить таблицу посредством оператора ALTER TABLE, нужно ввести следующую информацию:

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

· Список изменений, которые должны быть сделаны.

Рассмотрим пример добавления столбцов в таблицу.

            ALTER TABLE Vendors

            ADD vend_phone CHAR(20);

В таблицу Vendors будет добавлен столбец vend_phone.

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

Например, удаление столбца, будет работать не во всех СУБД. В Access работает.

            ALTER TABLE Vendors

            DROP COLUMN vend_phone;

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

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

· Использование оператора INSERT SELECT для копирования данных из старой таблицы в новую. При необходимости используются функции преобразования и вычисляемые поля.

· Проверка того факта, что новая таблица содержит нужные данные.

· Переименование старой таблицы или удаление ее.

· Присвоение новой таблице имени, которое ранее принадлежало старой таблицы.

· Восстановление триггеров, хранимых процедур, индексов и внешних ключей, если это необходимо.

17.6.  Удаление таблиц

           

Удаление самих таблиц, а не только их содержимого выполняется с помощью оператора DROP TABLE.

Пример, удалим таблицу CustCopy.

Вместе с этой лекцией читают "2. Автоматизация добычных участков".

            DROP TABLE CustCopy;

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

Во многих СУБД применяются правила, препятствующие удалению таблиц, связанных с другими таблицами. Если эти правила действуют, то при применении оператора DROP TABLE по отношению к таблице, которая связана с другой таблицей, СУБД блокирует проведение этой операции до тех пор, пока не будет удалена данная связь

17.7.  Переименование таблиц

В разных СУБД переименование таблиц осуществляется по-разному. Не существует жестких, устоявшихся стандартов на выполнение этой операции. В СУБД MySQL, Oracle применяется оператор RENAME. В СУБД SQL Server можно использовать хранимую процедуру sp_rename.

Основной синтаксис для всех операций переименования требует указания старого и нового имен.