SQL-Ex blog
Запросы SQL для изменения типа данных столбца
Добавил Sergey Moiseenko on Суббота, 23 апреля. 2022
- SQL Server 2019
- MySQL Server
- PostgreSQL
SQL-запрос для изменения типа столбца в базе данных SQL Server
Мы можем использовать оператор ALTER TABLE ALTER COLUMN для изменения типа столбца в таблице. Использует следующий синтаксис:
ALTER TABLE [tbl_name] ALTER COLUMN [col_name] [DATA_TYPE]
- tbl_name: задает имя таблицы.
- col_name: задает имя столбца, тип которого мы хотим изменить. col_name должно быть указано после ключевых слов ALTER COLUMN.
- DATA_TYPE: задает новый тип данных и длину столбца.
CREATE TABLE [dbo].[tblstudent]
(
[id] [INT] IDENTITY(1, 1) NOT NULL,
[student_code] [VARCHAR](20) NOT NULL,
[student_firstname] [VARCHAR](250) NOT NULL,
[student_lastname] [VARCHAR](10) NOT NULL,
[address] [VARCHAR](max) NULL,
[city_code] [VARCHAR](20) NOT NULL,
[school_code] [VARCHAR](20) NULL,
[admissiondate] [DATETIME] NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ( [id] ASC )
)
Предположим, что вы хотите изменить тип данных [address] с varchar(max) на nvarchar(1500). Выполните следующий запрос для изменения типа столбца.
Alter table tblstudent alter column address nvarchar(1500)
Проверим изменения с помощью следующего скрипта.
use StudentDB
go
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS
where table_name='tblStudent'
Видно, что тип данных столбца изменился.
- При уменьшении размера столбца SQL Server проверит данные в таблице и, если данные превышают новую длину, вернет предупреждение и прервет выполнение оператора.
- При изменении типа данных nvarchar на varchar, если столбец содержит строку Юникод, то SQL Server возвращает ошибку и прерывает оператор.
- В отличие от MySQL изменение типа данных нескольких столбцов не допускается.
- Вы не можете добавить
а. ограничение NOT NULL, если столбец содержит NULL-значения;
б. ограничение UNIQUE, если в столбце имеются дубликаты.
Запрос SQL для изменения типа столбца в MySQL
Для изменения типа данных столбца мы можем использовать оператор ALTER TABLE MODIFY COLUMN. Синтаксис изменения типа данных столбца имеет следующий вид:
ALTER TABLE [tbl_name] MODIFY COLUMN [col_name_1] [DATA_TYPE],
MODIFY [col_name_2] [data_type],
MODIFY [col_name_3] [data_type]
- Tbl_name: задает имя таблицы, содержащая столбец, который мы хотим изменить.
- Col_name: задает имя столбца, тип которого мы хотим изменить. Col_name должно быть указано после ключевых слов MODIFY COLUMN. Мы можем изменить тип данных нескольких столбцов. При изменении типа данных нескольких столбцов, столбцы разделяются запятой (,).
- Datatype: задает новый тип данных и длину столбца. Тип данных должен указываться после имени столбца.
create table tblactor
(
actor_id int,
first_name varchar(500),
first_name varchar(500),
address varchar(500),
CityID int,
lastupdate datetime
)
Рассмотрим несколько примеров.
Пример 1: Запрос для изменения типа данных одного столбца
Мы хотим изменить тип столбца address с varchar(500) на тип данных TEXT. Выполните следующий запрос для изменения типа данных.
mysql> ALTER TABLE tblActor MODIFY address TEXT
Для проверки изменений выполните следующий запрос:
mysql> describe tblactor
Как можно увидеть, тип данных столбца address был изменен на TEXT.
Пример 2: SQL-запрос для изменения типа данных нескольких столбцов
Мы можем изменить тип данных нескольких столбцов в таблице. В нашем примере мы хотим изменить тип столбцов first_name и last_name. Новым типом данных столбцов становится TINYTEXT.
mysql> ALTER TABLE tblActor MODIFY first_name TINYTEXT, modify last_name TINYTEXT;
Выполните следующий запрос, чтобы проверить изменения:
mysql> describe tblActor
Как видно, тип данных столбцов first_name и last_name изменился на TINYTEXT.
Пример 3: Переименование столбца в MySQL
Чтобы переименовать столбцы, мы должны использовать оператор ALTER TABLE CHANGE COLUMN. Предположим, что вы хотите переименовать столбец CityID в CityCode; вы должны выполнить следующий запрос.
mysql> ALTER TABLE tblActor CHANGE COLUMN CityID CityCode int
Выполните команду describe, чтобы увидеть изменения структуры таблицы.
Видно, что имя столбца изменилось.
Запрос SQL для изменения типа столбца в базе данных PostgreSQL
Мы можем использовать оператор ALTER TABLE ALTER COLUMN для изменения типа данных столбца. Синтаксис изменения типа данных столбца:
ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] TYPE [data_type],
ALTER COLUMN [col_name_2] TYPE [data_type],
ALTER COLUMN [col_name_3] TYPE [data_type]
- Tbl_name: задает имя таблицы, содержащая столбец, который вы хотите изменить.
- Col_name: задает имя столбца, тип которого мы хотим изменить. Col_name должно быть указано после ключевых слов ALTER COLUMN. Мы можем изменить тип данных нескольких столбцов.
- Data_type: задает новый тип данных и длину столбца. Тип данных должен быть указан после ключевого слова TYPE.
create table tblmovies
(
movie_id int,
Movie_Title varchar(500),
Movie_director TEXT,
Movie_Producer TEXT,
duraion int,
Certificate varchar(5),
rent numeric(10,2)
)
Теперь рассмотрим несколько примеров.
Пример 1: Запрос SQL для изменения типа данных одного столбца
Мы хотим изменить тип столбца movie_id с типа данных int4 на int8. Для изменения типа данных выполните следующий запрос.
ALTER TABLE tblmovies ALTER COLUMN movie_id TYPE BIGINT
Для проверки изменений выполните следующий запрос:
SELECT
table_catalog,
table_name,
column_name,
udt_name,
character_maximum_length
FROM
information_schema.columns
WHERE
table_name = 'tblmovies';
Как видно, тип данных столбца movie_id стал int8.
Пример 2: Запрос SQL для изменения типа данных нескольких столбцов
Мы можем изменить тип данных сразу нескольких столбцов таблицы. В нашем примере мы хотим изменить тип столбцов movie_title и movie_producer. Новым типом данных для столбца movie_title становится TEXT, а для movie_producer — varchar(2000).
ALTER TABLE tblmovies ALTER COLUMN movie_title TYPE text, ALTER COLUMN movie_producer TYPE varchar(2000);
Выполните следующий запрос для проверки изменений:
SELECT
table_catalog,
table_name,
column_name,
udt_name,
character_maximum_length
FROM
information_schema.columns
WHERE
table_name = 'tblmovies';
Как видно, типом данных столбца movie_title является TEXT, а movie_producer — varchar(2000).
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Изменить тип данных столбца
На sql server в таблице ‘table’ есть столбец ‘date’, в котором хранится дата в формате numeric: 1519721624578 — пример одной произвольной записи. Это кол-во миллисекунд, прошедших с 01.01.1970 (так оракл хранит дату, данные sql server получает с оракла через openquery). Нужно преобразовать этот столбец, чтобы дата отображалась нормально. Пробую вывести через select:
select CONVERT(datetime, '01.01.1970') + CAST(date/86400000 as datetime) from table Все работает, но хочу изменить сам столбец:
update table set date = (CONVERT(datetime, '01.01.1970') + CAST(date/86400000 as datetime)) Выдает ошибку:
Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query. Что логично, потому что новый и старый типы данных не совпадают. ALter table alter column не работает, потому что столбец уже заполнен.
Sql как изменить тип данных столбца
Модификация типа данных столбца
MS SQL Server
В MS SQL Server для изменения типа данных столбцов используется предложение ALTER COLUMN инструкции ALTER TABLE.
ALTER TABLE ALTER COLUMN [ NULL | NOT NULL ]
В аргументе column_name содержит имя столбца, подлежащего изменению. Аргумент new_col_type содержит описание нового типа данных для изменяемого столбца.
Ниже приведены критерии для аргумента new_col_type изменяемого столбца:
- Предыдущие типы данных должны быть неявно преобразуемыми в новый тип данных.
- Аргумент new_col_type не может принадлежать к типу timestamp.
- Если изменяемый столбец является столбцом идентификаторов, то новый тип данных должен поддерживать свойство идентификатора.
Тип данных столбцов text, ntext и image может быть изменен только следующими способами:
- text на varchar(max), nvarchar(max) или xml
- ntext на varchar(max), nvarchar(max) или xml
- image в varbinary(max)
MySQL Server
В СУБД MySQL описание столбца меняется с помощью предложений CHANGE или MODIFY в операторе ALTER TABLE.
В выражении col_definition для MODIFY и CHANGE используется тот же синтаксис, что и для CREATE TABLE. Следует учитывать, что этот синтаксис включает имя столбца, а не просто его тип.
Операция CHANGE, в отличие от операции MODIFY, не только меняет тип данных, но и переименовывает столбец. Поэтому при ее использовании сначала указывается имя столбца, который будет меняться, а затем дается полностью новое объявление столбца, включая опять же его имя.
Переименовать столбец saledate в таблице tbl_sales в d_sale и изменить его тип с date на datetime. Установить атрибут NOT NULL для данного столбца
MS SQL:
/*переиенование столбца тоблицы*/
sp_rename ‘tbl_sales.saledate’, ‘d_sale’, ‘COLUMN’;
/*измениение описание столбца*/
ALTER TABLE tbl_sales ALTER COLUMN d_sale datetime NOT NULL;
MySQL:
ALTER TABLE tbl_sales CHANGE saledate d_sale datetime NOT NULL
Увеличить размерность столбцов name и lastname в таблице tbl_clients с 45 символов до 60
SQL ALTER TABLE
Инструкция ALTER TABLE используется для добавления, удаления или изменения столбцов в существующей таблице.
Инструкция ALTER TABLE также используется для добавления и удаления различных ограничений на существующую таблицу.
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;
ALTER TABLE — ALTER/MODIFY COLUMN
Чтобы изменить тип данных столбца в таблице, используйте следующие синтаксис:
SQL Сервер / MS Доступ:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
My SQL / Оракул (предыдущая версия 10G):
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Оракул 10G и более поздние версии:
ALTER TABLE table_name
MODIFY column_name datatype;
Пример ALTER TABLE
Посмотрите на таблицу «Persons»:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Теперь мы хотим добавить столбец с именем «DateOfBirth» в таблицу «Persons».
Мы используем следующую инструкцию SQL:
ALTER TABLE Persons
ADD DateOfBirth date;
Обратите внимание, что новый столбец «DateOfBirth» имеет тип date и будет содержать дату. Тип данных указывает, какой тип данных может содержать столбец. Для получения полного описания всех типов данных, доступных в MS Access, MySQL и SQL Server, перейдите к нашему полному Справочнику типов данных.
Таблица «Persons» теперь будет выглядеть так:
| ID | LastName | FirstName | Address | City | DateOfBirth |
|---|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes | |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes | |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Пример изменения типа данных
Теперь мы хотим изменить тип данных столбца с именем «DateOfBirth» в таблице «Persons».
Мы используем следующую инструкцию SQL:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
Обратите внимание, что столбец «DateOfBirth» теперь имеет тип year и будет содержать year в двух или четырехзначном формате.
Пример DROP COLUMN
В первую очередь, мы хотим удалить столбец с именем «DateOfBirth» в таблице «Persons».
Мы используем следующую инструкцию SQL:
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
Таблица «Persons» теперь будет выглядеть так:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Мы только что запустили
SchoolsW3 видео
курс сегодня!
Сообщить об ошибке
Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:
Ваше предложение:
Спасибо Вам за то, что помогаете!
Ваше сообщение было отправлено в SchoolsW3.
ТОП Учебники
ТОП Справочники
ТОП Примеры
SchoolsW3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.