Изменение таблиц в Microsoft SQL Server или как добавить, удалить, изменить столбец в таблице? SQL Изменить таблицу

Содержание

Команда 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?

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

<текстареа 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;»> ALTER TABLE table_name ADD column_name data_type attributes;

1 ALTERTABLEtable_nameADDcolumn_namedata_typeattributes;

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

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

<текстареа 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;»> ALTER TABLE books ADD discount TINYINT UNSIGNED, ADD amount SMALLINT UNSIGNED;

1 ALTERTABLEbooksADDdiscountTINYINTUNSIGNED,ADDamountSMALLINTUNSIGNED;

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

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

<текстареа 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;»> ALTER TABLE books ADD shelf_position VARCHAR(20) AFTER Price;

1 ALTERTABLEbooksADDshelf_positionVARCHAR(20)AFTERPrice;

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

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

<текстареа 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;»> ALTER TABLE table_name DROP column_name;

1 ALTERTABLEtable_nameDROPcolumn_name;

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

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

<текстареа 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;»> ALTER TABLE books DROP edition;

1 ALTERTABLEbooksDROPedition;

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

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

<текстареа 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;»> ALTER TABLE table_name MODIFY COLUMN column_name data_type AFTER column_after_name;

1 ALTERTABLEtable_nameMODIFYCOLUMNcolumn_namedata_typeAFTERcolumn_after_name;

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

<текстареа 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;»> ALTER TABLE books MODIFY COLUMN Price DECIMAL(15,2) AFTER Author;

1 ALTERTABLEbooksMODIFYCOLUMNPriceDECIMAL(15,2)AFTERAuthor;

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

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

<текстареа 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;»> ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name new_data_type;

1 ALTERTABLEtable_nameCHANGECOLUMNold_column_namenew_column_namenew_data_type;

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

<текстареа 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;»> ALTER TABLE books CHANGE COLUMN creation_date entry_date DATE;

1 ALTERTABLEbooksCHANGECOLUMNcreation_dateentry_dateDATE;

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

<текстареа 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;»> ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;

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 таблицы, которые между собой не связаны:

1-1801-24e407.png

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

ALTERTABLEOrdersADDFOREIGNKEY(CustomerId)REFERENCESCustomers(Id);

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

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

ALTERTABLEOrdersADDPRIMARYKEY(Id);

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

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

2-1801-9d8180.png

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

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

3-1801-68d176.png

Следует раскрыть в подузле 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

Создание базы данных SQL и работа с таблицами в SQL. SQL — создание таблицы

Понравилась статья? Поделиться с друзьями:
ITandLife.ru
Adblock
detector