Базовый курс SQL. Работа с таблицами CREATE ALTER DROP TABLE

[к оглавлению]

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

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

• С помощью графического интерфейса, который предусмотрен в большинстве СУБД
• С помощью SQL запросов в консоли

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

Итак, для создания таблицы используется оператор CREATE TABLE. В приложении 1 к данным урокам вы можете увидеть примеры создания таблиц для PostgreSQL. Далее мы рассмотрим основные параметры запроса, а за более полной информацией традиционно можете обратиться к документации вашей СУБД.

Простая таблица

Что содержит в себе запрос на создание таблицы?

• Ключевые слова CREATE TABLE
• Определение и имена столбцов, разделённые запятыми
• Расположение таблицы (в некоторых СУБД)

Вот так, например, создавалась таблица Teachers:

CREATE TABLE Teachers
(
  teacher_id          int       NOT NULL ,
  teacher_name        char(50)  NOT NULL ,
  teacher_surname     char(50)  NOT NULL ,
  teacher_middle_name char(50)  
);

Сразу после ключевых слов указано имя таблицы. Далее, в скобках, - определение таблицы, т.е. описание всех её столбцов. Имя каждого столбца должно быть уникальным в рамках создаваемой таблицы, а тип - соответствовать одному из поддерживаемых данной СУБД (основные типы данных в SQL приведены в приложении 4). Определение каждого столбца отделяется запятой: как мы видим, таблица Teachers содержит четыре столбца.

Данный синтаксис будет работать в PostgreSQL, Oracle, SQLite, SQL Server. Для других СУБД придётся внести незначительные изменения. Например, в MySQL следует заменить тип char на text. Для уточнения обратитесь к документации.

Форматирование

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

Перезаписывание таблиц

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

Значение NULL

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

CREATE TABLE Teachers
(
  teacher_id          int       NOT NULL ,
  teacher_name        char(50)  NOT NULL ,
  teacher_surname     char(50)  NOT NULL ,
  teacher_middle_name char(50)  
);

На примере той же таблицы Teachers мы видим, что первые три столбца обязательны к заполнению, и поэтому напротив них расположена спецификация NOT NULL. Теоретически возможно такое, что у учителя нет фамилии, а значит, столбец teacher_middle_name может быть пустым. Практически во всех СУБД, включая PostgreSQL и MySQL, это никак не нужно обозначать дополнительно: по умолчанию столбец может содержать NULL.

Могут ли первичные ключи содержать значение NULL?

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

NULL или пустая строка?

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

Значение по умолчанию

Если сервер не прислал значение для какого-либо поля, и оно имеет спецификацию NOT NULL, SQL сгенерирует ошибку. Этого можно избежать, установив значение по умолчанию: в этом случае СУБД будет подставлять его вместо отсутствующих данных. Задать значение по умолчанию помогает ключевое слово DEFAULT:

CREATE TABLE Students
(
  student_id      int       NOT NULL ,
  student_name    char(50)  NOT NULL ,
  student_surname char(50)  NOT NULL ,
  student_age     int       NOT NULL ,
  student_country char(50)  NOT NULL ,
  student_email   char(255) ,
  group_id        int       NOT NULL   DEFAULT 30913
);

В данном примере рассматривается создание таблицы Students. Предположим, что распределение студентов по группам происходит вручную, но по умолчанию студенты попадают в группу с group_id равным 30913.

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

• PostgreSQL: CURRENT_DATE
• MySQL: CURRENT_DATE()
• Oracle: SYSDATE
• DB2: CURRENT_DATE
• SQLite: date('now')
• SQL Server: GETDATE()
• Access: NOW()

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

Изменение таблиц

Для переопределения структуры таблиц используются ключевые слова ALTER TABLE. Эта функция в той или иной мере доступна во всех СУБД, однако далеко не везде разрешены все действия над столбцами. Итак, о чём нужно помнить, собираясь запустить ALTER TABLE:

• Лучшие изменения - те, которых не было. В случае, когда речь идёт о структуре таблицы, постарайтесь изначально продумать как можно больше, чем менять что-то впоследствии
• Единственная операция, поддерживаемая всеми СУБД - это добавление столбца в таблицу. Но есть разница в допустимых типах столбцов, а также в правилах использования NULL и DEFAULT
• Во многих СУБД запрещено измениять и удалять столбцы
• В большинстве СУБД разрешается переименовывать столбцы
• Большим ограничениям подлежат столбцы, уже заполненные данными

Обратитесь к документации вашей СУБД для уточнения правил.
Например, в SQLite невозможно применить ALTER TABLE к столбцам, назначенным первичными или внешними ключами.

Давайте рассмотрим пример: запрос на добавление столбца содержит в себе имя существующей таблицы (иначе будет выведена ошибка) и информацию об изменениях:

ALTER TABLE Teachers
ADD teacher_phone CHAR(11);

Данный код добавит в таблицу Teachers столбец под названием teacher_phone. Не забудьте указать его тип данных.

А вот пример удаления столбца. Обратите внимание, что не все СУБД поддерживают эту операцию:

ALTER TABLE Teachers
DROP COLUMN teacher_phone;

Иногда создать таблицу заново проще, чем изменить имеющуюся. Тогда прибегают к методу копирования таблиц (см. урок 16):

1. Создать новую таблицу с требуемой структурой
2. С помощью INSERT SELECT скопировать данные из старой таблицы, по необходимости используя вычисляемые поля
3. Тщательно проверить, все ли данные перенесены и корректно сохранились
4. Удалить старую таблицу
5. Переименовать новую таблицу, дав ей имя старой таблицы
6. Восстановление внешних ключей, индексов, триггеров и хранимых процедур, если требуется

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

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

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

Для примера давайте удалим таблицу NewFaculties, которую мы создавали в уроке №16:

DROP TABLE NewFaculties;

Реляционные правила

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

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

Способ переименования таблиц в различных СУБД существенно различается. Например, в MySQL, PostgreSQL, MariaDB, Oracle, DB2 для этого используется ключевое слово RENAME, в SQLite - ALTER TABLE, в SQL Server - хранимая процедура sp_rename. Для детального описания синтаксиса обратитесь к документации своей СУБД.

Следующая страница. Представления.
Оглавление.