SQLSERVER改变已有数据表中的列

WBOY

Release： 2016-06-07 15:45:27

Original

1257 people have browsed it

SQLSERVER改变已有数据表中的列包括改变字段的位置，增加列，更改列名称，更改列数据类型，列长度，增加标识列，增加主键，约束上面这些在做数据库升级或者迁移的时候很多时候都要用到的 1.改变字段位置，只需要在表设计器中拖动字段到其他地方直接在表设

SQLSERVER改变已有数据表中的列

包括改变字段的位置，增加列，更改列名称，更改列数据类型，列长度，增加标识列，增加主键，约束

上面这些在做数据库升级或者迁移的时候很多时候都要用到的

1.改变字段位置，只需要在表设计器中拖动字段到其他地方

直接在表设计器中,拖动字段到别的位置，然后点击保存

SQLSERVER改变已有数据表中的列

2.添加新列

例如为adventurworks数据库中employees表添加备注列（Comment）,数据类型为字符型（变长型，长度50字符），该列允许为空

<span>1</span> <span>ALTER</span> <span>TABLE</span><span> table_name
</span><span>2</span> <span>ADD</span> column_name data_type <span>NULL</span>

Copy after login

<span>1</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>ALTER</span> <span>TABLE</span> <span>[</span><span>HumanResources</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>
<span>4</span> <span>ADD</span> Comment <span>CHAR</span>(<span>50</span>) <span>NULL</span>
<span>5</span> <span>GO<br></span>

Copy after login

3.更改列名称

更改列名称不会影响列中的数据，但如果其他数据库对象引用了已修改的列，则用户必须同时做相应的修改

<span>1</span> <span>--</span><span>将表test13中的列名由e重命名为x</span>
<span>2</span> <span>--</span><span>语法：exec sp_rename '表名.原列名','新列名','column';</span>
<span>3</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span>4</span> <span>GO</span>
<span>5</span> <span>exec</span> sp_rename <span>'</span><span>dbo.test13.e</span><span>'</span>,<span>'</span><span>x</span><span>'</span>,<span>'</span><span>column</span><span>'</span>;

Copy after login

4.更改列的数据类型以及长度
当表中有数据的时候，要注意转换的时候的长度和精度，以及转换的规则

<span>1</span> <span>--</span><span>语法</span>
<span>2</span> <span>ALTER</span> <span>TABLE</span><span> table_name
</span><span>3</span> <span>ALTER</span> <span>COLUMN</span> column_name data_type

Copy after login

<span>1</span> <span>--</span><span>例如：将Employees表中的备注列Remark的数据类型更改为字符型（变长型，长度为255字符）</span>
<span>2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span>
<span>3</span> <span>GO</span>
<span>4</span> <span>ALTER</span> <span>TABLE</span> <span>[</span><span>HumanResources</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>
<span>5</span> <span>ALTER</span> <span>COLUMN</span> Remark <span>VARCHAR</span>(<span>255</span>)

Copy after login

5.增加标识列

一个表只能有一个列定义为IDENTITY属性，而且该列必须以tinyint，smallint，int，bigint，numeric，decimal数据类型定义，标识符列

不允许空值。按需指定种子和增量值，二者默认值均为1。

<span>1</span> <span>--</span><span>给Employees表添加一个员工编号（EmployeesCode）列，并将其设置为标识列</span>
<span>2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span>
<span>3</span> <span>GO</span>
<span>4</span> <span>ALTER</span> <span>TABLE</span> <span>[</span><span>HumanResources</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>
<span>5</span> <span>ADD</span> EmployeesCode <span>INT</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span>

Copy after login

6.定义主键

每张表都应该有一个主键，主键可以由一列或者多列组成（复合主键），标识数据的唯一性，提高查询和排序速度

如果是复合主键，那么这些列的里的数据都不能重复，不单只是某一列

<span>1</span> <span>--</span><span>使用TSQL语句为表定义主键约束保证数据完整性</span>
<span>2</span> <span>--</span><span>为Employees表添加主键约束，将EmployeeID,LoginID,ManagerID列设置为复合主键</span>
<span>3</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span>
<span>4</span> <span>GO</span>
<span>5</span> <span>ALTER</span> <span>TABLE</span> <span>[</span><span>HumanResources</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>
<span>6</span> <span>ADD</span> <span>CONSTRAINT</span><span> PK_EMPLOYEES
</span><span>7</span> <span>PRIMARY</span> <span>KEY</span> <span>CLUSTERED</span>(<span>[</span><span>EmployeeID</span><span>]</span>,<span>[</span><span>LoginID</span><span>]</span>,<span>[</span><span>ManagerID</span><span>]</span>)

Copy after login

上个星期在客户那里因为要修改主键，忘记了TSQL怎麽写，要看着SSMS来设置，感觉慢了，所以写TSQL是最快的个人感觉

当然有添加也会有删除啦把ADD 改为DROP

<span>1</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>ALTER</span> <span>TABLE</span> <span>[</span><span>HumanResources</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>
<span>4</span> <span>DROP</span> <span>CONSTRAINT</span><span> PK_EMPLOYEES
</span>

Copy after login

7.删除列

当表中的某些列确定不再需要时，可以删除该列。在删除前，必须首先删除基于该列的索引和约束

<span>1</span> <span>--</span><span>语法</span>
<span>2</span> <span>ALTER</span> <span>TABLE</span><span> table_name
</span><span>3</span> <span>DROP</span> <span>COLUMN</span> <span>[</span><span>COLUMN_NAME</span><span>]</span>

Copy after login

<span>1</span> <span>--</span><span>删除Employees表的Lastname列</span>
<span>2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span>
<span>3</span> <span>GO</span>
<span>4</span> <span>ALTER</span> <span>TABLE</span> <span>[</span><span>HumanResources</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>
<span>5</span> <span>DROP</span> <span>COLUMN</span> <span>[</span><span>LastName</span><span>]</span>

Copy after login

这篇文章非常基础，我也只是做一些笔记 o(∩_∩)o