Преобразование типов данных (ядро СУБД)
Преобразование типов данных происходит в следующих случаях:
- При перемещении, сравнении или объединении данных одного объекта с данными другого объекта эти данные могут преобразовываться из одного типа в другой.
- При передаче в переменную программы данных из результирующего столбца Transact-SQL, кода возврата или параметра вывода эти данные должны преобразовываться из системного типа данных SQL Server в тип данных переменной.
При преобразовании между переменной приложения и столбцом результирующих наборов SQL Server, возвращаемым кодом, параметром или маркером параметров поддерживаемые преобразования типов данных определяются API базы данных.
Явное и неявное преобразование
Преобразование типов данных бывает явным и неявным.
Неявное преобразование скрыто от пользователя. SQL Server автоматически преобразует данные из одного типа данных в другой. Например, если smallint сравнивается с int, то перед сравнением smallint неявно преобразуется в int.
GETDATE() выполняет неявное преобразование в стиль даты 0. SYSDATETIME() выполняет неявное преобразование в стиль даты 21.
Явное преобразование выполняется с помощью функций CAST и CONVERT.
Функции CAST и CONVERT преобразуют значение (локальную переменную, столбец или выражение) из одного типа данных в другой. Например, приведенная ниже функция CAST преобразует числовое значение $157.27 в строку символов ‘157.27’ :
CAST ( $157.27 AS VARCHAR(10) ) Если программный код Transact-SQL должен соответствовать требованиям ISO, используйте функцию CAST вместо CONVERT. Использование функции CONVERT вместо CAST дает преимущество в дополнительной функциональности.
На следующем рисунке показаны все явные и неявные преобразования типов данных, которые разрешены для системных типов данных SQL Server. Это могут быть типы xml, bigint и sql_variant. При присваивании неявного преобразования из типа sql_variant не происходит, но неявное преобразование в тип sql_variant производится.
Хотя на приведенной выше диаграмме показаны все явные и неявные преобразования, которые допускаются в SQL Server, в ней не указан результирующий тип данных. Когда SQL Server выполняет явное преобразование, сам оператор определяет результирующий тип данных. Для неявных преобразований операторы назначения, такие как установка значения переменной или вставка значения в столбец, дают в результате тип данных, определенный в объявлении переменной или в определении столбца. Для операторов сравнения или других выражений результирующий тип данных зависит от правил приоритета типов данных.
Например, следующий сценарий определяет переменную типа varchar , присваивает переменной значение типа int , а затем выбирает объединение переменной со строкой.
DECLARE @string VARCHAR(10); SET @string = 1; SELECT @string + ' is a string.' Значение int 1 преобразуется в varchar , поэтому оператор SELECT возвращает значение 1 is a string. .
В следующем примере показан похожий сценарий с переменной int :
DECLARE @notastring INT; SET @notastring = '1'; SELECT @notastring + ' is not a string.' В этом случае оператор SELECT выдает следующую ошибку:
Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value ‘ is not a string.’ to data type int.
Чтобы вычислить выражение @notastring + ‘ is not a string.’ , SQL Server следует правилам приоритета типов данных для выполнения неявного преобразования перед вычислением результата выражения. Поскольку int имеет более высокий приоритет, чем varchar , SQL Server пытается преобразовать строку в целое число, и операция завершается ошибкой, так как эта строка не может быть преобразована в целое число. Если выражение содержит строку, которую можно преобразовать, работа оператора завершается успешно, как показано в следующем примере:
DECLARE @notastring INT; SET @notastring = '1'; SELECT @notastring + '1' В этом случае строка 1 может быть преобразована в целочисленное значение 1 , поэтому оператор SELECT возвращает значение 2 . Обратите внимание, что оператор + выполняет сложение, а не объединение, если предоставленные типы данных являются целыми числами.
Поведение преобразования типов данных
Некоторые неявные и явные преобразования типов данных не поддерживаются при преобразовании типа данных одного объекта SQL Server в другой. Например, значение типа nchar нельзя преобразовать в значение типа image. Тип данных nchar можно преобразовать в тип данных binary только явно. Неявное преобразование в binary не поддерживается. Однако тип данных nchar можно преобразовать в тип nvarchar как явно, так и неявно.
В следующих темах приведено описание процесса преобразования следующих типов данных:
- binary и varbinary (Transact-SQL)
- datetime2 (Transact-SQL)
- money and smallmoney (Transact-SQL)
- bit (Transact-SQL)
- datetimeoffset (Transact-SQL)
- smalldatetime (Transact-SQL)
- char и varchar (Transact-SQL)
- десятичная и числовая (Transact-SQL)
- sql_variant (Transact-SQL)
- date (Transact-SQL)
- float и real (Transact-SQL)
- time (Transact-SQL)
- datetime (Transact-SQL)
- int, bigint, smallint и tinyint (Transact-SQL)
- uniqueidentifier (Transact-SQL)
Преобразование типов данных с помощью хранимых процедур OLE-автоматизации
Поскольку SQL Server использует типы данных Transact-SQL, а служба автоматизации OLE — типы данных Visual Basic, хранимым процедурам службы автоматизации OLE приходится преобразовывать данные, которыми они обмениваются.
В следующей таблице описаны преобразования типов данных SQL Server в Visual Basic.
| Тип данных SQL Server | Тип данных Visual Basic |
|---|---|
| char, varchar, text, nvarchar, ntext | String |
| decimal, numeric | String |
| bit | Boolean |
| binary, varbinary, image | Одномерный массив Byte() |
| int | Long |
| smallint | Целое число |
| tinyint | Byte |
| float | Двойной |
| real | Один |
| money, smallmoney | Валюта |
| datetime, smalldatetime | Дата |
| Все значения NULL | Variant со значением NULL |
Все значения SQL Server преобразуются в одно значение Visual Basic, за исключением двоичных , varbinary и изображений. Эти значения преобразуются в одномерный массив Byte() в Visual Basic. Этот массив имеет диапазон байт ( от 0 до длины 1**)**, где длина — количество байтов в значениях binary, varbinary или image SQL Server.
Это преобразования типов данных Visual Basic в типы данных SQL Server.
| Тип данных Visual Basic | Тип данных SQL Server |
|---|---|
| Long, Integer, Byte, Boolean, Object | int |
| Double, Single | float |
| Валюта | money |
| Дата | datetime |
| String длиной 4000 символов или меньше | varchar/nvarchar |
| String длиной более 4000 символов | text/ntext |
| Одномерный массив Byte() размером 8000 байт или меньше | varbinary |
| Одномерный массив Byte() размером более 8000 байт | Изображение |
Преобразовать тип данных в запросе select
Можно ли не меняя тип данных в таблице, вывести через select столбец с иным типом данных? К примеру, в столбце varchar, а я хочу int (в столбце нет других символов кроме 0-9).
Отслеживать
задан 16 ноя 2022 в 6:24
Андрей Ковров Андрей Ковров
93 6 6 бронзовых знаков
select CAST(СтолбецСтрока AS INT) as НовоеИмяСтолбца from table
16 ноя 2022 в 6:26
Спасибо большое!
16 ноя 2022 в 7:21
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
SELECT CAST(ColumnName AS INT) AS ColumnName FROM TableName Отслеживать
ответ дан 16 ноя 2022 в 6:29
Vitaliy Zlobin Vitaliy Zlobin
1,676 1 1 золотой знак 5 5 серебряных знаков 15 15 бронзовых знаков
TRY_CAST() всё же безопаснее. мало ли что там и кто говорит про значения в поле. Сто пудов у автора нет в таблице констрейнта, который обеспечивает соответствие значения описанному шаблону.
16 ноя 2022 в 6:40
Благодарю, try_cast тоже опробую
16 ноя 2022 в 6:46
@Akina try_cast — это уже спицифика определённой базы двнных, а CAST — это спецификация SQL
16 ноя 2022 в 6:57
@Виктор Это да. Но вроде как вопрос явно помечен тегом SQL Server..
16 ноя 2022 в 7:00
Да, microsoft sql server
16 ноя 2022 в 7:19
- sql
- sql-server
-
Важное на Мете
Похожие
Подписаться на ленту
Лента вопроса
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
Дизайн сайта / логотип © 2023 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2023.11.15.1019
Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.
ALTER TABLE в SQL
Когда вы начнете использовать таблицу после ее создания, вы можете обнаружить, что забыли какой-нибудь столбец или указали неверное имя столбца.
В такой ситуации можно использовать оператор `ALTER TABLE`, чтобы изменить существующую таблицы — с помощью добавления, изменения или удаления столбца в таблице.
Рассмотрим таблицу shippers в нашей базе данных. Ее структура выглядит следующим образом:
+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(60) | NO | | NULL | | +--------------+-------------+------+-----+---------+----------------+
Мы будем использовать таблицу shippers во всех дальнейших примерах с ALTER TABLE .
Как добавить новый столбец
Предположим, что нам нужно расширить существующую таблицу shippers , добавив еще один столбец. Давайте разберемся, как это сделать с помощью SQL-команд.
ALTER TABLE имя_таблицы ADD имя_столбца тип_данных ограничения;
Следующий оператор добавляет новый столбец fax в таблицу shippers .
ALTER TABLE shippers ADD fax VARCHAR(20);
Если вы посмотрите на структуру таблицы с помощью команды DESCRIBE shippers; после выполнения приведенной выше команды, то увидите следующее:
+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(60) | NO | | NULL | | | fax | varchar(20) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+
Примечание. Если вы хотите добавить NOT NULL -столбец в существующую таблицу, то нужно указать явное значение по умолчанию. Это значение используется для заполнения нового столбца для каждой строки, которая уже существует в таблице.
Примечание. При добавлении нового столбца в таблицу, если не указано ни NULL , ни NOT NULL , столбец обрабатывается так, как если бы было указано NULL .
По умолчанию MySQL добавляет новые столбцы в конец. Если вы хотите добавить новый столбец после определенного столбца, используйте условие AFTER , как показано ниже:
mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;
В MySQL существует еще одно условие — FIRST , которое можно использовать для добавления нового столбца на первое место в таблице. Просто замените AFTER на FIRST в предыдущем примере и тогда столбец fax добавится в начало таблицы shippers .
Как изменить расположение столбца
Если вы уже создали таблицу в MySQL, но вас не устраивает существующее положение столбцов в ней, вы можете изменить его в любое время с помощью такого синтаксиса:
ALTER TABLE имя_таблицы
MODIFY имя_столбца определение_столбца AFTER имя_столбца;
Следующий оператор помещает столбец fax после столбца shipper_name в таблице shippers :
mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;
Как изменить расположения столбца
Если вы уже создали таблицу в MySQL, но вас не устраивает существующее положение столбцов, его можно изменить в любое время, используя следующий синтаксис:
ALTER TABLE имя_таблицы
MODIFY имя_столбца определение_столбца AFTER имя_столбца;
Следующий оператор помещает столбец fax после столбца shipper_name в таблице shippers :
mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;
Как добавить ограничения
В текущем виде у таблицы shippers есть одна серьезная проблема. Если вы вставите записи с дублирующимися телефонными номерами, она не помешает вам это сделать, что не очень хорошо, ведь телефонные номера должны быть уникальными.
Это легко исправить, добавив ограничение UNIQUE к столбцу phone . Основной синтаксис для добавления этого ограничения к существующим столбцам таблицы выглядит так:
ALTER TABLE table_name ADD UNIQUE (column_name. );
Следующий оператор добавляет ограничение UNIQUE к столбцу phone .
mysql> ALTER TABLE shippers ADD UNIQUE (phone);
Если вы попытаетесь вставить дубликат телефонного номера после выполнения оператора, то получите ошибку.
Аналогично, если вы создали таблицу без PRIMARY KEY , можно добавить его с помощью следующего выражения:
ALTER TABLE имя_таблицы ADD PRIMARY KEY (имя_столбца. );
А вот этот оператор добавляет ограничение PRIMARY KEY к столбцу shipper_id , если он не определен.
mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id);
Как удалить столбец
Базовый синтаксис для удаления столбца из существующей таблицы выглядит следующим образом:
ALTER TABLE имя_таблицы DROP COLUMN имя_столбца;
Следующий оператор удалит наш недавно добавленный столбец fax из таблицы shippers .
mysql> ALTER TABLE shippers DROP COLUMN fax;
После выполнения оператора, структура таблицы будет выглядеть так:
+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(20) | NO | UNI | NULL | | +--------------+-------------+------+-----+---------+----------------+
Как изменить тип данных столбца
В SQL Server можно изменить тип данных столбца с помощью выражения ALTER , как показано ниже:
ALTER TABLE имя_таблицы ALTER COLUMN имя_таблицы новый_тип_данных;
Однако MySQL не поддерживает синтаксис ALTER COLUMN . Там используется альтернативное выражение MODIFY , которое изменяет столбец:
ALTER TABLE имя_таблицы MODIFY имя_столбца новый_тип_данных;
Следующий оператор изменяет текущий тип данных столбца phone в таблице shippers с VARCHAR на CHAR и длину с 20 на 15.
mysql> ALTER TABLE shippers MODIFY phone CHAR(15);
Аналогично можно использовать выражение MODIFY для переключения допущения нулевых значений в столбце таблицы MySQL. Это реализуется при помощи повторного определения столбца и добавления ограничения NULL или NOT NULL в конце, как показано ниже:
mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL;
Как переименовать таблицу
Основной синтаксис для переименования существующей таблицы в MySQL выглядит следующим образом:
ALTER TABLE текущее_имя_таблицы RENAME новая_имя_таблицы;
Например, следующий оператор переименует таблицу shippers в shipper .
mysql> ALTER TABLE shippers RENAME shipper;
Такого же результата можно добиться с помощью оператора RENAME TABLE :
mysql> RENAME TABLE shippers TO shipper;
СodeСhick.io — простой и эффективный способ изучения программирования.
2023 © ООО «Алгоритмы и практика»
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! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:
