sql添加列_SQL添加列操作

sql添加列

This article explains the SQL add column operation into an existing SQL table. We will also explore different examples of SQL add column operations.

本文介绍了对现有SQL表SQL添加列操作。 我们还将探讨SQL添加列操作的不同示例。

Sometimes we want to add columns into an existing table. In existing tables, we might have records in it. We do not want to lose existing data as well. In many circumstances, we can drop the tables and recreate them but this is not recommended generally, especially in a production environment, as it can be destructive as it pertains to data. We can still perform a SQL add column operation using Alter Table command, which avoids have to drop tables, delete data, even if only temporarily.

有时我们想将列添加到现有表中。 在现有表中，我们可能有记录。 我们也不想丢失现有数据。 在许多情况下，我们可以删除表并重新创建它们，但是通常不建议这样做，尤其是在生产环境中，因为它与数据有关可能具有破坏性。 我们仍然可以使用Alter Table命令执行SQL添加列操作，这避免了必须删除表，删除数据（即使只是暂时的）。

句法 (Syntax)

We can perform a SQL add column operation on a table with the following transact SQL command.

我们可以使用以下Transact SQL命令在表上执行SQL添加列操作。

ALTER TABLE table_name
  ADD column_name column_definition;

准备环境 (Prepare the environment)

We need to select a Database table and insert data into it.

我们需要选择一个数据库表并将数据插入其中。

Execute the following query to create an Employee table in SQLShackDemo database.

执行以下查询以在SQLShackDemo数据库中创建Employee表。

USE [SQLShackDemo]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Employee](
  [EmpID] [int] IDENTITY(1,1) NOT NULL,
  [EmpName] [varchar](50) NULL,
  [City] [varchar](30) NULL,
  [Designation] [varchar](30) NULL,
PRIMARY KEY CLUSTERED 
(
  [EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Execute the following query to insert sample data into it.

执行以下查询以将示例数据插入其中。

USE [SQLShackDemo];
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(1, 
 N'Charlotte Robinson', 
 N'Chicago', 
 N'Consultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(2, 
 N'Madison Phillips', 
 N'Dallas', 
 N'Senior Analyst'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(3, 
 N'Emma Hernandez', 
 N'Phoenix', 
 N'Senior Analyst'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(4, 
 N'Samantha Sanchez', 
 N'San Diego', 
 N'Principal Conultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(5, 
 N'Sadie Ward', 
 N'San Antonio', 
 N'Consultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(6, 
 N'Savannah Perez', 
 N'New York', 
 N'Principal Conultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(7, 
 N'Victoria Gray', 
 N'Los Angeles', 
 N'Assistant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(8, 
 N'Alyssa Lewis', 
 N'Houston', 
 N'Consultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(9, 
 N'Anna Lee', 
 N'San Jose', 
 N