ALTER TABLE — изменение таблицы в SQL

Содержание

1 Команда ALTER TABLE в MySQL
2 Как добавить колонку к таблице MySQL?
3 Как удалить колонку из таблицы MySQL?
4 Как переставить колонки в таблице MySQL?
5 Как изменить имя и тип данных у колонки в таблице MySQL?
6 Изменение таблиц в Microsoft SQL Server на языке T-SQL (ALTER TABLE)
7 Команда ALTER TABLE
8 ALTER TABLE — ADD column
- 8.1 Пример
9 ALTER TABLE — DROP COLUMN
- 9.1 Пример
10 Видео-инструкция по изменению таблиц в Microsoft SQL Server

Команда ALTER TABLE в MySQL

Команда ALTER TABLE используется для осуществления изменений таблицы:

Добавление колонок
Удаление колонок
Модификация колонок
Изменения имени таблицы
Изменения кодировки таблицы
Добавление и удаление ограничений

Для дальнейших примеров будем использовать таблицу books из базы данных Bookstore, которую создали в одном из предыдущих постов.

Чтобы просмотреть изменения колонок в таблице, воспользуйтесь командой:
SHOW COLUMNS FROM table_name;

Перед началом работ выберем базу данных, с которой будем работать.

<текстареа class=»crayon-plain print-no» data-settings=»dblclick» readonly=»» style=»-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;»> USE Bookstore;

1	USEBookstore;

Как добавить колонку к таблице MySQL?

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

1	ALTERTABLEtable_nameADDcolumn_namedata_typeattributes;

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

Можно добавлять, удалять и модифицировать сразу несколько колонок за раз.

1	ALTERTABLEbooksADDdiscountTINYINTUNSIGNED,ADDamountSMALLINTUNSIGNED;

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

Добавим колонку shelf_position сразу после колонки price.

1	ALTERTABLEbooksADDshelf_positionVARCHAR(20)AFTERPrice;

Как удалить колонку из таблицы MySQL?

Синтаксис удаления колонки из таблицы:

1	ALTERTABLEtable_nameDROPcolumn_name;

Если в таблице осталась только одна колонка, то ее удалить нельзя.

Удалим колонку edition с номером издания книги.

1	ALTERTABLEbooksDROPedition;

Как переставить колонки в таблице MySQL?

Чтобы переставить колонку используйте команду AFTER, также понадобится повторно определить тип данных.

1	ALTERTABLEtable_nameMODIFYCOLUMNcolumn_namedata_typeAFTERcolumn_after_name;

Переместим колонку с ценой Price на место после колонки Author:

1	ALTERTABLEbooksMODIFYCOLUMNPriceDECIMAL(15,2)AFTERAuthor;

Как изменить имя и тип данных у колонки в таблице MySQL?

Синтаксис смены имени и типа данных у колонки:

1	ALTERTABLEtable_nameCHANGECOLUMNold_column_namenew_column_namenew_data_type;

Сменим имя у колонки сreation_date на entry_date и тип данных c TIMESTAMP на DATE.

1	ALTERTABLEbooksCHANGECOLUMNcreation_dateentry_dateDATE;

Если вы хотите сменить только тип данных, оставив имя колонки прежним, то воспользуйтесь следующим синтаксисом:

1	ALTERTABLEtable_nameMODIFYCOLUMNcolumn_namenew_data_type;

Изменение таблиц в Microsoft SQL Server на языке T-SQL (ALTER TABLE)

Теперь давайте я покажу, как изменять таблицы в Microsoft SQL Server на T-SQL. Все изменения в таблицы вносятся с помощью инструкции ALTER TABLE. Для начала давайте рассмотрим упрощённый синтаксис инструкции ALTER TABLE, чтобы Вы лучше понимали структуру тех запросов, которые мы будем рассматривать далее в примерах.

Упрощенный синтаксис инструкции ALTER TABLE

ALTER TABLE [Название таблицы] [Тип изменения] [Название столбца] [Тип данных] [Возможность принятия значения NULL]

Добавление нового столбца в таблицу на T-SQL

Чтобы добавить новый столбец в таблицу, мы пишем инструкцию ALTER TABLE с параметром ADD, указываем название нового столбца (в нашем случае ProductDescription, т.е. описание товара), его тип данных и возможность принятия значения NULL (как было уже отмечено ранее, если в таблице есть строки, то сначала столбец должен принимать значения NULL).

ALTER TABLE Goods ADD ProductDescription VARCHAR(300) NULL; GO SELECT * FROM Goods;

Скриншот 8

Удаление столбца из таблицы на T-SQL

Если Вам столбец не нужен, то его легко удалить (если он не участвует ни в каких связях) параметром DROP COLUMN, например, мы передумали добавлять новый столбец с описанием товара, и чтобы его удалить, пишем следующую инструкцию.

ALTER TABLE Goods DROP COLUMN ProductDescription; GO SELECT * FROM Goods;

Скриншот 9

Задаем свойство NOT NULL для столбца на T-SQL

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

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

ALTER TABLE Goods ALTER COLUMN Price MONEY NOT NULL;

Изменяем тип данных столбца на T-SQL

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

Допустим, у нас возникла необходимость увеличить длину строки для хранения наименования товара (например, до 200 символов).

ALTER TABLE Goods ALTER COLUMN ProductName VARCHAR(200) NOT NULL;

Добавляем новый столбец

Для примера добавим новый column Address в таблицу Customers:

ALTERTABLECustomersADDAddressNVARCHAR(50)NULL;

В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:

ALTERTABLECustomersADDAddressNVARCHAR(50)NOTNULLDEFAULT’Неизвестно’;

Тогда, если в таблице существуют данные, для них для column Address добавится значение «Неизвестно».

Удаляем столбец

Теперь можно удалить column Address:

ALTERTABLECustomersDROPCOLUMNAddress;

Меняем тип

Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).

ALTERTABLECustomersALTERCOLUMNFirstNameNVARCHAR(200);

Добавляем ограничения CHECK

Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.

ALTERTABLECustomersADDCHECK(Age>21);

При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:

ALTERTABLECustomersWITHNOCHECKADDCHECK(Age>21);

По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.

Добавляем внешний ключ

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

Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):

ALTERTABLEOrdersADDFOREIGNKEY(CustomerId)REFERENCESCustomers(Id);

Добавляем первичный ключ

Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:

ALTERTABLEOrdersADDPRIMARYKEY(Id);

Добавляем ограничения с именами

Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):

Удаляем ограничения

Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:

Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.

Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название «FK_Orders_To_Customers». Здесь для удаления внешнего подойдёт такое выражение:

ALTERTABLEOrdersDROPFK_Orders_To_Customers;

Команда ALTER TABLE

Команда ALTER TABLE изменяет определение таблицы одним из следующих способов:

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

Условие: Таблица должна быть в схеме пользователя, или пользователь должен иметь системную привилегию ALTER ANY TABLE.

Добавляя столбец с ограничением NOT NULL, разработчик или администратор БД должны учесть ряд обстоятельств. Сначала нужно создать столбец без ограничения, а затем ввести значения во все его строки. После того как все значения столбца станут не NULL-значениями, к нему можно применить ограничение NOT NULL. Если столбец с ограничением NOT NULL пытается добавить пользователь, возвращается сообщение об ошибке, говорящее о том, что либо таблица должна быть пустой, либо в столбце должны содержаться значения для каждой существующей строки (напомним, что после наложения на столбец ограничения NOT NULL в нем не могут присутствовать NULL-значения ни в одной из существующих строк). В СУБД Oracle, начиная с версии 8i, можно удалять столбцы.

Изменяя типы данных существующих столбцов или добавляя столбцы в таблицу базы данных, нужно соблюдать ряд условий. Общепринято, что увеличение – это хорошо, а уменьшение, как правило, — не очень. Допустимые увеличения:

Увеличение размера столбца CHAR или VARCHAR2
Увеличение размера столбца NUMBER
Добавление новых столбцов в таблицу

Уменьшение различных характеристик таблицы, в том числе некоторых типов данных столбцов и реального числа столбцов таблицы, требует особых действий. Часто перед внесением изменения нужно убедиться в том, что в соответствующем столбце или столбцах все значения являются NULL-значениями. Для выполнения подобных операций над столбцами таблицы, содержащими данные, разработчик должен найти или создать какую-то область для временного хранения этих данных. Например, создать таблицу с помощью команды CREATE TABLE AS SELECT, в которой извлекаются данные из первичного ключа и изменяемого столбца или столбцов. Допустимые изменения:

Уменьшение размера столбца NUMBER (только при пустом столбце для всех строк)
Уменьшение размера столбца CHAR или VARCHAR2 (только при пустом столбце для всех строк)
Изменение типа данных столбца (только при пустом столбце для всех строк)

ALTER TABLE Пример 1

Добавление столбца в таблицу:

ALTER TABLE t1(pole1 char(10));

ALTER TABLE Пример 2

Изменение размера столбца таблицы:

ALTER TABLE t1 MODIFY (pole1 char(20));

ALTER TABLE Пример 3

Удаление столбца таблицы:

ALTER TABLE t1 DROP COLUMN pole1;

С помощью команды ALTER TABLE можно изменить имя таблицы без реального переноса физической информации в БД:

ALTER TABLE t1 RENAME TO t2;

Аналогичную операцию можно выполнить с помощью команды RENAME:

RENAME t1 TO t2;

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

ALTER TABLE Пример 4

Модификация структуры таблицы

ALTER TABLE t1 MODIFY (pole1 NOT NULL);

CREATE TABLE t2

(pole1 CHAR(10) PRIMARY KEY);

ALTER TABLE t1 ADD

(CONSTRAINT fk_t1 FOREIGN KEY (pole1)

REFERENCES t2 (pole1));

ALTER TABLE t1 ADD (UNIQUE (p_name));

ALTER TABLE t1 ADD (p_size CHAR(4) CHECK

(p_size IN (‘P’,’S’,’M’,’L’,’XL’,’XXL’,’XXXL’)));

В первой из приведенных выше команд для добавления ограничения NOT NULL для столбца используется конструкция MODIFY, а для добавления всех табличных ограничений целостности других типов – конструкция ADD. Столбец, для которого добавляется ограничение, должен уже существовать в таблице БД; в противном случае создать ограничение не удастся.

ALTER TABLE Пример 5

Для добавления ограничений целостности можно не указывать имя создаваемого ограничения с помощью ключевого слова CONSTRAINT. В этом случае команда будет выглядеть следующим образом:

ALTER TABLE t1 ADD FOREIGN KEY (pole1) REFERENCES t2 (pole1);

Существует ряд условий создания ограничений:

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

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

ALTER TABLE Пример 6

Запрещение ограничений:

ALTER TABLE t1 DISABLE PRIMARY KEY;
ALTER TABLE t1 DISABLE UNIQUE (p_name);

ALTER TABLE Пример 7

В некоторых случаях запрещение первичного ключа, от которого зависят внешние ключи, может вызвать определенные сложности, например:

ALTER TABLE t2 DISABLEPRIMARY KEY;

Error at line 1: Cannot disable constraint …. – dependencies exist (невозможно запретить ограничение – существуют зависимости)

Для удаления первичного ключа при наличии зависящих от него внешних ключей в команде ALTER TABLE DISABLE <ограничения> обязательна конструкция CASCADE:

ALTER TABLE t2 DISABLEPRIMARY KEYCASCADE;

ALTER TABLE Пример 8

Запрещенное ограничение разрешается следующим образом:

ALTER TABLE t1 ENABLE PRIMARY KEY;

ALTER TABLE t1 ENABLE UNIQUE (p_name);

Разрешить можно только те ограничения, которые были установлены ранее, а в данный момент запрещены.

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

ALTER TABLE t1 DROP UNIQUE (p_name);

ALTER TABLE — ADD column

Чтобы добавить столбец в таблицу, используйте следующее Синтаксис:

ALTER TABLE table_name
ADD column_name datatype;

Следующий SQL — добавляет колонку «Email» для таблицы «Customers»:

Пример

ALTER TABLE Customers
ADD Email varchar(255); Попробуйте сами »

ALTER TABLE — DROP COLUMN

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

ALTER TABLE table_name
DROP COLUMN column_name;

Следующий SQL удаляет столбец «Email» из таблицы «Customers»:

Пример

ALTER TABLE Customers
DROP COLUMN Email; Попробуйте сами »

Видео-инструкция по изменению таблиц в Microsoft SQL Server

Источники

https://meliorem.ru/backend/mysql/alter-table-izmenenie-tablic-v-baze-dannyx-mysql/
https://info-comp.ru/obucheniest/723-alter-table-in-ms-sql-server.html
https://otus.ru/nest/post/1684/
https://sql-language.ru/alter-table.html
https://schoolsw3.com/sql/sql_alter.php