如何改变列类型的SQL查询

迪鲁宾
692 阅读5分钟

这篇文章涵盖了改变列类型的不同SQL查询。我们将学习如何改变以下数据库的列的数据类型。

  1. SQL Server 2019
  2. MySQL服务器
  3. PostgreSQL

改变SQL Server数据库中的列类型的SQL查询

我们可以使用ALTER TABLE ALTER COLUMN语句来改变表的列类型。改变列类型的语法如下。

ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] [DATA_TYPE]

在这个语法中。

  • Tbl_name：指定表的名称
  • Col_name：指定你想改变其数据类型的列名。col_name必须在ALTER COLUMN关键字之后指定。
  • 数据类型：指定新的数据类型和列的长度。

为了演示，我创建了一个名为tblStudent的表。

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'

image.png

正如你所看到的，列的数据类型已经被改变。

重要说明。

  1. 当我们减少列的大小时，SQL Server将检查表的数据，如果数据的长度高于新的长度，它将返回警告并终止该语句。
  2. 当你把数据类型从nvarchar改为varchar时，如果该列包含Unicode字符串，那么SQL Server将返回错误并终止该语句。
  3. 与MySQL不同，不允许改变多列的数据类型
  4. 你不能添加
    1. 如果该列包含NULL值，则有NOT NULL约束。
    2. 如果列有重复的值，则采用UNIQUE约束。

在MySQL服务器中改变列类型的SQL查询

我们可以使用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关键字之后指定。我们可以改变多个列的数据类型。当我们改变多列的数据类型时，每一列必须用逗号（,）隔开。
  • 数据类型：指定新的数据类型和列的长度。数据类型必须在列名之后指定。

为了演示，我在DemoDatabase中创建了一个名为 tblactor在DemoDatabase中。创建该表的代码如下

create table tblactor
    (
    actor_id int,
    first_name varchar(500),
    first_name varchar(500),
    address varchar(500),
    CityID int,
    lastupdate datetime
    )

现在，让我们通过几个例子来理解这个概念。

例1：改变一个列的数据类型的SQL查询

我们想把地址列的类型从varchar(500)改为TEXT数据类型。运行下面的查询来改变数据类型。

mysql> ALTER TABLE tblActor MODIFY address TEXT

运行下面的查询来验证这些变化。

mysql> describe tblactor

image.png

正如你所看到的，列的数据类型被改为 地址列的数据类型已经被改变为 TEXT.

例2：改变多列数据类型的SQL查询

我们可以改变一个表的多个列的数据类型。在我们的例子中，我们想改变first_name和last_name的列类型。新的数据类型是varchar(200)。

mysql> ALTER TABLE tblActor MODIFY first_name TINYTEXT, modify last_name TINYTEXT;

运行下面的查询来验证这些变化。

mysql> describe tblActor

image.png

正如你所看到的，first_name和last_name列的数据类型已经被改变为TINYTEXT。

例3:重命名MySQL服务器中的列

要重命名列，我们必须使用ALTER TABLE CHANGE COLUMN语句。假设你想把列CityID重命名为CityCode，你必须执行以下查询。

mysql> ALTER TABLE tblActor CHANGE COLUMN CityID CityCode int

运行describe命令，查看表结构的变化。

image.png

正如你所看到的，列名已经被改变。

改变PostgreSQL数据库中列类型的SQL查询

我们可以使用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关键字之后指定。我们可以改变多个列的数据类型
  • 数据类型：指定新的数据类型和列的长度。数据类型必须在TYPE关键字之后指定。

为了演示，我创建了一个名为 tblmovies在DemoDatabase中。创建该表的代码如下。

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';

image.png

正如你所看到的，该列的数据类型 movie_id列的数据类型已经被改变为 int8.

例2：改变多列数据类型的SQL查询

我们可以改变一个表的多个列的数据类型。在我们的例子中，我们想改变movie_title和movie_director的列类型。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';

image.png

你可以看到，movie_title列的数据类型是TEXT，movie_producer的数据类型是varchar(2000)。

小结

在这篇文章中，我们学习了如何改变SQL Server 2019、MySQL Server和PostgreSQL中列的数据类型。

avatar
文章
阅读
粉丝