Изменение структуры таблицы (ALTER) — Основы реляционных баз данных
Наиболее сложный запрос при работе с таблицами — обновление ее структуры. Если посмотреть официальную документацию, то общая запись этого запроса занимает больше трех экранов монитора. Но ни один человек не помнит всех возможностей этой команды. Более того, большинство из них редко выполняются. Единственное, что нужно знать — возможности этой команды — общие принципы ее работы. А детали всегда можно будет найти в документации.
В этом уроке разберем такой тип запроса, как ALTER TABLE . Он отвечает за изменение таблицы базы данных. Также узнаем, какие четыре операции со столбцами он включает в себя.
Запрос ALTER TABLE
Запрос ALTER TABLE используют, чтобы изменять структуру столбца таблицы базы данных. Он включает четыре операции:
- Добавление колонки
- Переименование колонки
- Удаление колонки
- Обновление колонки
Разберем каждую операцию подробнее.
Добавление колонки
С помощью ALTER можно добавить колонку в уже имеющуюся таблицу:
В этом примере мы добавили колонку age.
Делается это таким образом:
-- в таблице "users" -- добавить колонку с именем "age" и типом "int" ALTER TABLE users ADD COLUMN age int; Здесь после фразы ADD COLUMN идет строка описания новой колонки, точно такая же, как и при создании таблицы. В простом варианте она выглядит так: . Здесь можно указывать любые ограничения, добавлять ключи, автогенерацию, значение по умолчанию и многое другое. Синтаксис на 100% совпадает с синтаксисом определения колонки, когда таблица создается.
Переименование колонки
Чтобы переименовать колонку, нужно сделать следующий запрос:
-- в таблице "courses" -- изменить колонку "example1": -- поменять имя с "example1" на "example2" ALTER TABLE courses RENAME COLUMN example1 TO example2; Здесь нет никаких сложностей — одно имя меняется на другое.
Удаление колонки
Следующий запрос удаляет колонку:
-- в таблице "courses" -- удалить колонку с именем "example2" ALTER TABLE courses DROP COLUMN example2; Это тоже простая операция, которая убирает ненужную колонку.
Обновление колонки
Команда по изменению параметров колонки наиболее сложная. Практически у каждого элемента, который поддается обновлению, есть собственный синтаксис для этого обновления. Вот несколько базовых примеров:
ALTER TABLE addresses ADD PRIMARY KEY (id); ALTER TABLE addresses ALTER COLUMN created_at SET DATA TYPE timestamp, ALTER COLUMN street DROP NOT NULL; -- Установка ограничения NOT NULL в таблицу addresses для колонки city ALTER TABLE addresses ALTER COLUMN city SET NOT NULL; -- Добавление уникального индекса в таблицу "products" для колонки "product_id" ALTER TABLE products ADD UNIQUE (product_id); Наиболее распространенные команды:
- ADD — добавление ограничения: например, ключа или уникальности
- SET — установка значения: например, типа данных
- DROP — удаление ограничения
В рамках одного обновления можно группировать операции, но существует ряд исключений. Например, группировке не поддается операция RENAME — ее нужно выполнять отдельным запросом, иначе СУБД завершит запрос с ошибкой.
Выводы
В этом уроке мы разобрали тип запроса ALTER , который отвечает за изменение таблицы базы данных. Мы узнали, что с его помощью можно добавлять, переименовывать, удалять и обновлять колонки.
Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях:
Руководство по SQL. Изменение таблицы.
Для добавления, удаления либо изменения колонок существующей таблицы, в языке SQL используется команда ALTER TABLE. Данная команда также используется для добавления, либо удаления различных констрейнтов в/из существующих таблиц.
Запрос с использованием команды ALTER TABLE для добавления новой колонки имеет следующий вид:
ALTER TABLE имя_таблицы ADD имя_колонки тип_данных;
Для удаления колонки:
ALTER TABLE имя_таблицы DROP имя_колонки;
Для изменения типа данных:
ALTER TABLE имя_таблицы MODIFY COLUMN имя_колонки тип_данных;
Для добавления констрейнта (например, NOT NULL):
ALTER TABLE имя_таблицы MODIFY имя_колонки тип_данных NOT NULL;
Для удаления констрейнта (например, NOT NULL):
ALTER TABLE имя_таблицы DROP CONSTRAINT someConstraint;
Предположим, что у нас есть таблица developers, которая содержит следующие записи:
+----+-------------------+------------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+------------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | JavaScript | 3 | 2500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | | 6 | Kolya Nikolaev | Javascript | 5 | 3400 | | 7 | Ivan Ivanov | C# | 1 | 900 | | 8 | Ludmila Geiko | UI/UX | 2 | 1800 | +----+-------------------+------------+------------+--------+
Допустим, что мы хотим добавить новую колонку AGE (возраст).
Для этого нам необходимо использовать следующую команду:
mysql> ALTER TABLE developers ADD AGE INT;
Теперь наша таблица содержит данные такого вида:
+----+-------------------+------------+------------+--------+------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | AGE | +----+-------------------+------------+------------+--------+------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | NULL | | 2 | Peter Romanenko | Java | 3 | 3500 | NULL | | 3 | Andrei Komarov | JavaScript | 3 | 2500 | NULL | | 4 | Konstantin Geiko | C# | 2 | 2000 | NULL | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | NULL | | 6 | Kolya Nikolaev | Javascript | 5 | 3400 | NULL | | 7 | Ivan Ivanov | C# | 1 | 900 | NULL | | 8 | Ludmila Geiko | UI/UX | 2 | 1800 | NULL | +----+-------------------+------------+------------+--------+------+
Теперь попробуем удалить колонку AGE.
Для этого выполним следующий запрос:
mysql> ALTER TABLE developers DROP AGE;
В результате выполнения данного запроса, наша таблица будет иметь записи следующего вида:
+----+-------------------+------------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+------------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | JavaScript | 3 | 2500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | | 6 | Kolya Nikolaev | Javascript | 5 | 3400 | | 7 | Ivan Ivanov | C# | 1 | 900 | | 8 | Ludmila Geiko | UI/UX | 2 | 1800 | +----+-------------------+------------+------------+--------+ На этом мы заканчиваем изучения способа изменения структуры таблицы.
В следующей статье мы рассмотрим способ удаления всех данных из таблицы.
ALTER TABLE — изменение таблицы в SQL
Команда ALTER TABLE применяется в SQL при добавлении, удалении либо модификации колонки в существующей таблице. В этой статье будет рассмотрен синтаксис и примеры использования ALTER TABLE на примере MS SQL Server.
SQL-оператор ALTER TABLE способен менять определение таблицы несколькими способами: • добавлением/переопределением/удалением столбца (column); • модифицированием характеристик памяти; • включением, выключением либо удалением ограничения целостности.
При этом пользователю нужно обладать системной привилегией ALTER ANY TABLE либо таблица должна находиться в схеме пользователя.
Меняя типы данных существующих columns либо добавляя их в БД-таблицу, следует соблюдать некоторые условия. Принято, что увеличение есть хорошо, а уменьшение — не очень. Существует ряд допустимых увеличений: • добавляем новые столбцы в таблицу; • увеличиваем размер столбца CHAR либо VARCHAR2; • увеличиваем размер столбца NUMBER.
Нередко перед внесением изменений следует удостовериться, что в соответствующих columns все значения — это NULL-значения. Если выполняется операция над столбцами, которые содержат данные, следует найти либо создать область временного хранения данных. Можно создать таблицу посредством CREATE TABLE AS SELECT, где извлекаются данные из первичного ключа и изменяемых columns. Существует ряд допустимых изменений: • уменьшаем размер столбца NUMBER (лишь при наличии пустого column для всех строк); • уменьшаем размер столбца CHAR либо VARCHAR2 (лишь при наличии пустого column для всех строк); • меняем тип данных столбца (аналогично, что и в первых двух пунктах).
При добавлении column с ограничением NOT NULL, администратор баз данных либо разработчик обязан учесть некоторые обстоятельства. Вначале следует создать столбец без ограничения, потом ввести значения во все строки. Далее, когда значения column будут уже не NULL, к нему можно будет применить ограничение NOT NULL. Но если column с ограничением NOT NULL хочет добавить юзер, то вернётся сообщение об ошибке, судя по которому таблица должна быть либо пустой, либо содержать в столбце значения для каждой имеющейся строки (после наложения на column NOT NULL-ограничения, в нём не смогут присутствовать значения NULL ни в одной из имеющихся строк).
Синтаксис ALTER TABLE на примере MS SQL Server
Рассмотрим общий формальный синтаксис на примере SQL Server от Microsoft:
ALTER TABLE имя_таблицы [WITH CHECK | WITH NOCHECK]
Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.
Добавляем новый столбец
Для примера добавим новый column Address в таблицу Customers:
ALTER TABLE Customers ADD Address NVARCHAR(50) NULL;В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:
ALTER TABLE Customers ADD Address NVARCHAR(50) NOT NULL DEFAULT 'Неизвестно';Тогда, если в таблице существуют данные, для них для column Address добавится значение "Неизвестно".
Удаляем столбец
Теперь можно удалить column Address:
ALTER TABLE Customers DROP COLUMN Address;Меняем тип
Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).
ALTER TABLE Customers ALTER COLUMN FirstName NVARCHAR(200);Добавляем ограничения CHECK
Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.
ALTER TABLE Customers ADD CHECK (Age > 21);При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:
ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21);По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.
Добавляем внешний ключ
Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:
Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):
ALTER TABLE Orders ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);Добавляем первичный ключ
Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:
ALTER TABLE Orders ADD PRIMARY KEY (Id);Добавляем ограничения с именами
Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):
Удаляем ограничения
Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:
Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.
Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название "FK_Orders_To_Customers". Здесь для удаления внешнего подойдёт такое выражение:
ALTER TABLE Orders DROP FK_Orders_To_Customers;Хотите знать про SQL Server больше? Добро пожаловать на курс "MS SQL Server Developer" в OTUS! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:
ALTER TABLE — изменение таблицы в SQL
Рассказываем о команде ALTER TABLE и учим вносить с ее помощью изменения в таблицы и столбцы.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
ALTER TABLE — один из самых незаменимых инструментов в работе с базами данных SQL. В этой статье мы рассмотрим SQL оператор ALTER TABLE и его применение. Узнаем, как добавить или удалить поля с помощью этого инструмента, и рассмотрим различные примеры его использования. В данной статье мы не будем рассматривать MS SQL и остановимся на синтаксисе наиболее популярной версии — MySQL.
Синтаксис оператора ALTER TABLE в SQL
Синтаксис оператора ALTER TABLE выглядит следующим образом:
ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]Из этой записи мы видим, что сценариев применения данной команды достаточно много. Давайте рассмотрим их. В качестве примера мы будем использовать базу данных slcbookshelf, которую мы создавали в статье о первичных и внешних SQL ключах.
use slcbookshelf;Добавление столбца в таблицу (ADD COLUMN)
Сейчас наша таблица выглядит следующим образом:
mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)Давайте добавим в нашу таблицу новый столбец, в котором будет отображаться автор каждой книги:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL;Данным запросом мы создали в нашей таблице новый столбец authors с типом NVARCHAR и длиной в 50 символов, который не может принимать пустое значение. Если мы не знаем автора произведения, тогда наша команда будет иметь такой вид:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL DEFAULT 'Неизвестен';Теперь для существующих данных, для которых не заполнен столбец author, значение по умолчанию будет «Неизвестен».
Переименование столбца и таблицы
Переименование столбца (RENAME)
С помощью ALTER TABLE можно переименовать существующий столбец. Для этого выполните команду:
ALTER TABLE books RENAME COLUMN author TO authors;Переименование таблицы (RENAME)
При помощи ALTER TABLE можно переименовать таблицу. Выполняем запрос:
ALTER TABLE books RENAME TO books_selectel;Удаление столбца (DROP)
Чтобы удалить столбец из таблицы с помощью ALTER TABLE, требуется выполнить следующий запрос:
ALTER TABLE books DROP COLUMN authors;Изменение столбца (ALTER COLUMN)
Иногда бывают случаи, когда необходимо изменить уже созданный ранее столбец. Это действие можно выполнить с помощью команды ALTER TABLE. Для изменения существующего столбца необходимо выполнить такой запрос:
ALTER TABLE books ALTER COLUMN book_category VARCHAR(200);В данном примере мы изменили максимальное количество символов, которое может использоваться в полях столбца book_category с 255 до 200.
Также с помощью ALTER TABLE можно сделать действие сразу с несколькими столбцами. Чтобы изменить сразу несколько столбцов, вам потребуется использовать эту команду:
ALTER TABLE books MODIFY book_category VARCHAR(200), MODIFY book_name VARCHAR(200), . ;Таким запросом мы изменили сразу два столбца: book_category и book_name.
Изменение типа столбца
При помощи ALTER TABLE можно изменить тип столбца в таблице SQL. Изменение типа существующего столбца осуществляется при помощи команды:
ALTER TABLE books ALTER COLUMN book_category NVARCHAR(200);Выполнив эту команду, мы изменили тип book_category на NVARCHAR(200).
Добавление первичного и внешнего ключей при помощи ALTER TABLE
Вы можете определить существующий столбец в таблице в качестве первичного ключа с помощью команды ALTER TABLE. Запрос, добавляющий в таблицу первичный ключ, будет выглядеть следующим образом:
ALTER TABLE books ADD PRIMARY KEY (book_id);Аналогично при помощи ALTER TABLE можно добавить внешний ключ таблицы. Чтобы создать внешний ключ для таблицы MySQL выполните команду:
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id);В результате выполнения этой команды поле author_id в таблице books будет внешним ключом для аналогичного поля в таблице authors.
Работа с ограничениями
Ограничения — специальные правила, которые применяются к таблице, чтобы ограничить типы данных в таблице. Ограничения очень важны, так как их правильное применение помогает обеспечить целостность данных в таблицах и наладить стабильную работу базы. Давайте рассмотрим одно из таких ограничений — ограничение CHECK. Применяя ограничения CHECK к столбцу таблицы, мы создаем правило, по которому при добавлении данных СУБД будет автоматически проверять их на соответствии заданным правилам.
Создание ограничения
Например, если нам необходимо, чтобы все клиенты в базе данных Customers имели возраст больше 21 года, мы можем установить следующее ограничение:
ALTER TABLE Customers ADD CHECK (Age > 21);При применении такого ограничения стоит учитывать, что если в столбце уже существуют данные, не соответствующие ограничению, то команда будет выполнена с ошибкой. Чтобы избежать подобного поведения, вы можете добавить ограничение со значением WITH NOCHECK. Таким образом, текущие значения столбца не вызовут ошибок при выполнении запроса при несоответствии ограничению:
ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21);Добавление ограничений с именами
Так как ограничений в таблицах может быть много, добавление имен к ограничениям может в значительной мере упростить будущую работу с таблицей. Создать имя для ограничения можно при помощи оператора CONSTRAINT:
ALTER TABLE Customers ADD CONSTRAINT Check_Age_Greater_Than_Twenty_One CHECK (Age > 21);Удаление ограничений
ALTER TABLE можно пользоваться и для удаления ограничений. Для удаления существующих ограничений необходимо выполнить следующую команду:
ALTER TABLE Customers DROP Check_Age_Greater_Than_Twenty_One;После выполнения этой команды ограничение перестанет применяться при добавлении новых данных в столбец.
Заключение
В данной статье мы с вами узнали что такое ALTER TABLE, рассмотрели работу с этой командой и научились вносить с ее помощью изменения в таблицы и столбцы, а также рассмотрели несколько примеров использования данной команды, которые сильно упростят будущую работу с базами данных.
Сброс пароля root в MySQL
