Модификация таблиц. Оператор ALTER TABLE
Перед изучением данной темы рекомендуется ознакомиться со следующей темой:
Содержание
- 1. Модификация таблицы. Оператор ALTER TABLE. Особенности применения
- 2. Изменение параметров существующего поля. Комбинация ключевых слов ALTER COLUMN
- 3. Пример изменения типа поля (столбца) таблицы и задавание новых ограничений. ALTER TABLE + ALTER COLUMN
- 4. Добавление новых полей в таблицу. Ключевое слово ADD
- 5. Пример добавления полей к таблице
- 6. Удаление полей. Ключевые слова DROP COLUMN
- 7. Пример создания таблицы, добавление нового и удаление существующего поля из таблицы (ALTER TABLE + ADD, ALTER TABLE + DROP COLUMN)
- 8. Удаление нескольких полей (ALTER TABLE + DROP COLUMN)
- 9. Пример изменения ограничений в заданном поле таблицы. Сочетание ALTER TABLE+ADD+DROP
- 10. Пример изменения названия поля. Комбинация операторов ALTER TABLE + DROP, ALTER TABLE + ADD
- Связанные темы
Поиск на других ресурсах:
1. Модификация таблицы. Оператор ALTER TABLE. Особенности применения
Оператор ALTER TABLE предназначен для модификации ранее созданной таблицы. Оператор позволяет выполнять следующие базовые операции:
- добавлять новые поля в таблицу;
- удалять поля из таблицы;
- изменять тип данных полей;
- добавлять и удалять ограничения на поля;
- другое.
Оператор имеет широкий спектр возможностей. В данной теме рассматриваются только некоторые основные из них, а именно:
- добавление новых полей. Это обеспечивается за счет ключевого слова ADD;
- удаление полей. Здесь используется ключевое слово DROP в сочетании со словом COLUMN;
- редактирование полей. В этом случае используется комбинация слов ALTER COLUMN.
⇑
2. Изменение параметров существующего поля. Комбинация ключевых слов ALTER COLUMN
Для изменения типа поля используется следующий упрощенный синтаксис.
ALTER TABLE [Table_Name] ALTER COLUMN Column_Name New_Type NewRestrictions
здесь
- Table_Name – имя таблицы, которая была создана ранее;
- Column_Name – название поля таблицы Table_Name, которое было создано ранее;
- New_Type – название типа, который назначается полю Column_Name;
- New_Restrictions – ограничения, накладываемые на поле. Здесь можно задавать новые ограничения, которые раньше не были определены.
Рекомендуется, чтобы на момент изменения таблица не содержала записей.
⇑
3. Пример изменения типа поля (столбца) таблицы и задавание новых ограничений. ALTER TABLE + ALTER COLUMN
В нашем примере создается таблица с полями A, B, C, D. Затем с помощью оператора ALTER TABLE тип поля C изменяется с типа VARCHAR(20) на тип DECIMAL. Также задается ограничение NOT NULL.
/* Создать таблицу */ CREATE TABLE [MyTable] ( A INT NOT NULL PRIMARY KEY, B FLOAT, C VARCHAR(20), D DATE ); /* Результат. ------------------------------------- | A | B | C | D | ------------------------------------- | INT | FLOAT | VARCHAR(20) | DATE | ------------------------------------- */ GO /* Изменить тип колонки C на DECIMAL и добавить ограничение NOT NULL */ ALTER TABLE [MyTable] ALTER COLUMN C DECIMAL NOT NULL /* Результат. --------------------------------- | A | B | C | D | --------------------------------- | INT | FLOAT | DECIMAL | DATE | --------------------------------- */
⇑
4. Добавление новых полей в таблицу. Ключевое слово ADD
Добавление нового поля с помощью оператора ALTER TABLE реализуется с использованием ключевого слова ADD. В этом случае упрощенная общая форма оператора имеет вид
ALTER TABLE [Table_Name] ADD Field1 Type1, Field2 Type2, ... FieldN TypeN
здесь
- Table_Name – имя создаваемой таблицы;
- Field1, Field2, FieldN – имена полей, которые нужно добавить. Эти имена не должны повторять существующие имена таблицы. Другими словами, имена полей в таблице должны быть уникальными;
- Type1, Type2, TypeN – типы полей Field1, Field2, FieldN соответственно.
Если необходимо добавить одно поле, то синтаксис оператора следующий
ALTER TABLE [Table_Name] ADD Field Type
здесь
- Field – имя поля, которое нужно добавить;
- Type – тип поля, который нужно добавить.
Этот случай также предусматривает добавление ограничений на поля. В этом случае, после типа Type указывается одно или несколько ограничений (NOT NULL, NULL и другие).
⇑
5. Пример добавления полей к таблице
В примере с помощью команды CREATE TABLE формируются поля со следующими названиями и их типами:
- поле A типа Int. Это поле имеет ограничение NOT NULL и PRIMARY KEY;
- поле B типа Float.
Затем к этим полям добавляются следующие поля с помощью команды ALTER TABLE с ключевым словом ADD:
- поле C типа Varchar(20);
- поле D типа Date.
/* Создать таблицу с именем MyTable. Добавить столбцы A, B */ CREATE TABLE [MyTable] ( A INT NOT NULL PRIMARY KEY, B FLOAT ); GO /* Результат: --------------- | A | B | --------------- | INT | FLOAT | --------------- */ /* Добавить столбцы C, D */ ALTER TABLE [MyTable] ADD C VARCHAR(20), D DATE /* Результат: ------------------------------------ | A | B | C | D | ------------------------------------ | INT | FLOAT | VARCHAR(20) | DATE | ------------------------------------ */
⇑
6. Удаление полей. Ключевые слова DROP COLUMN
Для удаления полей из таблицы используется оператор ALTER TABLE в сочетании с ключевыми словами DROP COLUMN. В этом случае упрощенная общая форма оператора следующая
ALTER TABLE [Table_Name] DROP COLUMN Field1, Field2, ... FieldN
здесь
- Table_Name – имя таблицы;
- Column1, Column2, ColumnN – имена полей (столбцов) таблицы, которые нужно удалить. Если задать несуществующие поля, будет отображаться сообщение об ошибке.
Если из таблицы нужно удалить только одно поле, вид оператора ALTER TABLE будет следующий
ALTER TABLE [Table_Name] DROP COLUMN Field
здесь
- Field – имя удаляемого поля.
⇑
7. Пример создания таблицы, добавление нового и удаление существующего поля из таблицы (ALTER TABLE + ADD, ALTER TABLE + DROP COLUMN)
В примере демонстрируется использование следующих команд:
- CREATE TABLE – создание таблиц из 4-х полей;
- ALTER TABLE с ключом ADD – добавление нового поля к таблице;
- ALTER TABLE с ключом DROP COLUMN – удаление поля из таблицы.
/* Создать таблицу с именем MyTable. Добавить столбцы A, B, C, D */ CREATE TABLE [MyTable] ( A INT, B FLOAT, C VARCHAR(20), D DATE ); GO /* Результат: ------------------------------------ | A | B | C | D | ------------------------------------ | INT | FLOAT | VARCHAR(20) | DATE | ------------------------------------ */ /* Добавить столбец E типа CHAR(10) */ ALTER TABLE [MyTable] ADD E CHAR(10) GO /* Результат: ----------------------------------------------- | A | B | C | D | E | ----------------------------------------------- | INT | FLOAT | VARCHAR(20) | DATE | CHAR(10) | ----------------------------------------------- */ /* Удалить столбец C */ ALTER TABLE [MyTable] DROP COLUMN C /* Результат: --------------------------------- | A | B | D | E | --------------------------------- | INT | FLOAT | DATE | CHAR(10) | --------------------------------- */
⇑
8. Удаление нескольких полей (ALTER TABLE + DROP COLUMN)
В примере сначала создается таблица, содержащая 6 полей с именами A, B, C, D, E, F. Затем с помощью команды ALTER TABLE из таблицы удаляются два поля с именами A, C.
/* Создать таблицу с именем MyTable. Добавить столбцы A, B, C, D, E, F */ CREATE TABLE [MyTable] ( A INT NOT NULL, B FLOAT, C VARCHAR(20), D DATE, E CHAR(10), F REAL ); GO /* Результат: ------------------------------------------------------ | A | B | C | D | E | F | ------------------------------------------------------ | INT | FLOAT | VARCHAR(20) | DATE | CHAR(10) | REAL | ------------------------------------------------------ */ /* Удалить столбцы C, A */ ALTER TABLE [MyTable] DROP COLUMN C, A /* Результат: ---------------------------------- | B | D | E | F | ---------------------------------- | FLOAT | DATE | CHAR(10) | REAL | ---------------------------------- */
⇑
9. Пример изменения ограничений в заданном поле таблицы. Сочетание ALTER TABLE+ADD+DROP
Если к существующему полю нужно добавить ограничение, то можно выполнить следующую последовательность действий:
- удалить поле;
- создать новое поле с таким же именем с заданным ограничением. Если данные в таблице уже существуют, необходимо выполнить операции копирования этих данных в другую таблицу.
Код на языке T-SQL, выполняющий операции по изменению ограничений следующий
/* 1. Создать таблицу с именем MyTable. Задать столбцы A, B, C, D. */ CREATE TABLE [MyTable] ( A INT NOT NULL PRIMARY KEY, B FLOAT, C VARCHAR(20), D DATE ); GO /* Результат: ------------------------------------ | A | B | C | D | ------------------------------------ | INT | FLOAT | VARCHAR(20) | DATE | ------------------------------------ */ /* 2. Добавить к столбцу B ограничение NOT NULL */ /* 2.1. Удалить столбец B */ ALTER TABLE [MyTable] DROP COLUMN B /* 2.2. Добавить столбец B с ограничением NOT NULL */ ALTER TABLE [MyTable] ADD B FLOAT NOT NULL
На рисунке 1 показан вид окна Microsoft SQL Server Management Studio с выполняемым примером.
Рисунок 1. Оператор ALTER TABLE. Добавление ограничения NOT NULL в поле B
⇑
10. Пример изменения названия поля. Комбинация операторов ALTER TABLE + DROP, ALTER TABLE + ADD
В примере показано изменение названия заданного поля путем удаления существующего (ALTER TABLE + DROP) и добавления нового с новым именем (ALTER TABLE + ADD).
Если поле таблицы содержит данные, одним из способов решения задачи является создание временной таблицы, в которую эти данные будут скопированы. После этого, происходят изменения в основной таблице и данные временной таблицы копируются в эту таблицу. Такой способ решения задачи приводится ниже с целью демонстрации использования базовых операций для работы с таблицами.
/* 1. Создать таблицу MyTable */ CREATE TABLE [MyTable] ( A INT, B FLOAT, C VARCHAR(20) ); /* Результат. ----------------------------- | A | B | C | ----------------------------- | INT | FLOAT | VARCHAR(20) | ----------------------------- */ /* 2. Добавить в таблицу MyTable произвольные данные */ INSERT INTO [MyTable] ( A, B, C ) VALUES ( 1, 5.88, 'First' ) INSERT INTO [MyTable] ( A, B, C ) VALUES ( 2, 3.5, 'Second' ) INSERT INTO [MyTable] ( A, B, C ) VALUES ( 4, 8.16, 'Third' ) /* Результат. Таблиця MyTable ----------------------------- | A | B | C | ----------------------------- | INT | FLOAT | VARCHAR(20) | ----------------------------- | 1 | 5.88 | 'First' | | 2 | 3.5 | 'Second' | | 4 | 8.16 | 'Third' | ----------------------------- */ /* 3. Вывести данные таблицы MyTable для контроля */ SELECT * FROM MyTable /* Изменить название поля C на ABC в таблице MyTable */ /* 4.1. Создать новую таблицу-копию, в которой поле C заменено на ABC */ CREATE TABLE [Temp] ( A INT, B FLOAT, ABC VARCHAR(20) ) /* 4.2. Скопировать данные поля C во временную таблицу */ /* 4.2.1. Копирование MyTable.C => Temp.C */ INSERT INTO [Temp] ( A, B, ABC ) SELECT * FROM [MyTable] /* 4.2.2. Вывести данные временной таблицы Temp */ SELECT * FROM [Temp] /* 4.3. Удалить поле C таблицы MyTable */ ALTER TABLE [MyTable] DROP COLUMN C /* 4.4. Добавить поле ABC в таблицу MyTable */ ALTER TABLE [MyTable] ADD ABC VARCHAR(20) /* 4.5. Удалить все предыдущие данные из таблицы MyTable */ DELETE FROM [MyTable] /* 4.6. Скопировать данные из таблицы Temp в таблицу MyTable */ INSERT INTO [MyTable] ( A, B, ABC ) SELECT * FROM [Temp] /* 4.7. Удалить таблицу Temp */ DROP TABLE [Temp] /* 5. Вывести данные таблицы MyTable */ SELECT * FROM [MyTable] /* Результат. Таблица MyTable ----------------------------- | A | B | ABC | ----------------------------- | INT | FLOAT | VARCHAR(20) | ----------------------------- | 1 | 5.88 | 'First' | | 2 | 3.5 | 'Second' | | 4 | 8.16 | 'Third' | ----------------------------- */
Результат выполнения программы в Microsoft SQL Server Management Studio изображен на рисунке 2.
Рисунок 2. Результат выполнения программы. Таблица MyTable с измененным столбцом ABC
⇑
Связанные темы
- Microsoft SQL Server Management Studio 18. Пример создания простейшего запроса
- Создание таблиц. Оператор CREATE TABLE. Примеры
⇑