数据库中如何用语句删除表中的列

各主流数据库用法如下：

sqlserver：

alter table 表名 drop column 列名;oracle：

alter table 表名 drop column 列名;mysql：

alter table 表名 drop column 列名;总结：在主流数据库下，删除列的语法是一致的，并无差别。

microsoft sql server management studio中展开对应表下面的列信息不就可以看到列的详细信息了么？当然这是可以的，但这里我们主要讲的时如何利用sql语句来查询指定表的列信息。

利用sql语句来查询列信息，就是要用到系统视图syscolumns，这个视图记录了数据库中所有表，视图，表值函数等的所有列信息。我们可以利用语句select from syscolumns来查看这个视图返回的信息。

syscolumns返回的列比较多，大部分情况下很多列的信息我们可能用不到，下面我们只解释一下比较常用的列的信息。

1,object_id--这个列是比较重要的，它是返回当前列所属表的ID。利用它，我们就可以查询指定表的所有列信息，比如下面的sql语句是查询表table1的所有列信息。

select from syscolumns where object_id=object_id('table1')

2,name--该列的列名。

3,column_id--该列在数据库中的ID，注意，数据库中任何对象的ID都是唯一的。

4,system_type_id--该列的类型的ID，和下面max_length,precision,scale三列一起可以来举个示例。

5,max_length--该列的最大长度

6,precisionp--如果这列是数值列，那么这是该列的精度，否则就是0

7,scale--如果这列是数值列，那么这就是列的小数位数，否则就是0

system_type_id，max_length,precision,scale四列结合系统视图systypes一起我们来举个示例。

如果我们要查询表table1的所有列，及列的类型，列的精度，列的小数位数，sql语句如下：

select aname,bname,amax_length,aprecision,ascale

from syscolumns a left join systypes b on auser_type_id=buser_type_id

where aobject_id=object_id('table1')

8,is_nullable--该列是否可以为null

9,is_identity--该列是否是标识列

10,is_computed--该列是否是计算列。利用该列，我们也就可以查询某个表的所有计算列了，比如我们要查询表table1中的所有计算列，sql语句如下：

select from syscolumns where object_id=object_id('table1') and is_computed=1

syscolumns视图中常用的列就是这10列了，其它列的信息大家可以到SQLServer 2005联机丛书中查询。

SELECT

表名=CASE WHEN Ccolumn_id=1 THEN Oname ELSE N'' END,

表说明=ISNULL(CASE WHEN Ccolumn_id=1 THEN PTB[value] END,N''),

字段序号=Ccolumn_id,

字段名=Cname,

主键=ISNULL(IDXPrimaryKey,N''),

标识=CASE WHEN Cis_identity=1 THEN N'√'ELSE N'' END,

计算列=CASE WHEN Cis_computed=1 THEN N'√'ELSE N'' END,

类型=Tname,

长度=Cmax_length,

精度=Cprecision,

小数位数=Cscale,

允许空=CASE WHEN Cis_nullable=1 THEN N'√'ELSE N'' END,

默认值=ISNULL(Ddefinition,N''),

字段说明=ISNULL(PFD[value],N''),

索引名=ISNULL(IDXIndexName,N''),

索引排序=ISNULL(IDXSort,N''),

创建时间=OCreate_Date,

修改时间=OModify_date

FROM syscolumns C

INNER JOIN sysobjects O

ON C[object_id]=O[object_id]

AND Otype='U'

AND Ois_ms_shipped=0

INNER JOIN systypes T

ON Cuser_type_id=Tuser_type_id

LEFT JOIN sysdefault_constraints D

ON C[object_id]=Dparent_object_id

AND Ccolumn_id=Dparent_column_id

AND Cdefault_object_id=D[object_id]

LEFT JOIN sysextended_properties PFD

ON PFDclass=1

AND C[object_id]=PFDmajor_id

AND Ccolumn_id=PFDminor_id

-- AND PFDname='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)

LEFT JOIN sysextended_properties PTB

ON PTBclass=1

AND PTBminor_id=0

AND C[object_id]=PTBmajor_id

-- AND PFDname='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)

LEFT JOIN -- 索引及主键信息

(

SELECT

IDXC[object_id],

IDXCcolumn_id,

Sort=CASE INDEXKEY_PROPERTY(IDXC[object_id],IDXCindex_id,IDXCindex_column_id,'IsDescending')

WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,

PrimaryKey=CASE WHEN IDXis_primary_key=1 THEN N'√'ELSE N'' END,

IndexName=IDXName

FROM sysindexes IDX

INNER JOIN sysindex_columns IDXC

ON IDX[object_id]=IDXC[object_id]

AND IDXindex_id=IDXCindex_id

LEFT JOIN syskey_constraints KC

ON IDX[object_id]=KC[parent_object_id]

AND IDXindex_id=KCunique_index_id

INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息

(

SELECT [object_id], Column_id, index_id=MIN(index_id)

FROM sysindex_columns

GROUP BY [object_id], Column_id

) IDXCUQ

ON IDXC[object_id]=IDXCUQ[object_id]

AND IDXCColumn_id=IDXCUQColumn_id

AND IDXCindex_id=IDXCUQindex_id

) IDX

ON C[object_id]=IDX[object_id]

AND Ccolumn_id=IDXcolumn_id

WHERE Oname like '%name%'

-- 如果只查询指定表,加上此条件

ORDER BY Oname,Ccolumn_id

传统情况

我们先回顾一下，在没有 "立刻加列" 功能时，加列 *** 作是怎么完成的。我们也借此来熟悉一下本期的图例：

当进行 加列 *** 作 时，所有的数据行 都必须要 增加一段数据（图中的 列 4 数据）

如上一期图解所讲，当改变数据行的长度，就需要 重建表空间（图中灰蓝的部分为发生变更的部分）

数据字典中的列定义也会被更新

以上 *** 作的问题在于 每次加列 *** 作都需要重建表空间，这就需要大量 IO以及大量的时间

立刻加列

"立刻加列" 的过程如下图：

"立刻加列" 时，只会变更数据字典中的内容，包括：

在列定义中增加 新列的定义

增加 新列的默认值

"立刻加列" 后，当要读取表中的数据时：

由于 "立刻加列" 没有 变更行数据，读取的行数据只有 3 列

MySQL 会将 新增的第 4 列的默认值，追加到 读取的数据后

以上过程描述了 如何读取 在 "立刻加列" 之前写入的数据，其实质是：在读取数据的过程中，"伪造" 了一个新列出来

那么如何读取 在 "立刻加列" 之后 写入的数据呢 过程如下图：

当读取 行 4 时：

通过判断 数据行的头信息中的instant 标志位，可以知道该行的格式是 "新格式"：该行头信息后有一个新字段 "列数"

通过读取 数据行的 "列数" 字段，可以知道 该行数据中多少列有 "真实" 的数据，从而按列数读取数据

通过上图可以看到：读取 在"立刻加列" 前/后写入的数据是不同的流程

通过以上的讨论，我们可以总结 "立刻加列" 之所以高效的原因是：

在执行 "立刻加列" 时，不变更数据行的结构

读取 "旧" 数据时，"伪造" 新增的列，使结果正确

写入 "新" 数据时，使用了新的数据格式（增加了instant标志位 和 "列数" 字段)，以区分新旧数据

读取 "新" 数据时，可以如实读取数据

那么 我们是否能一直 "伪造" 下去  "伪造" 何时会被拆穿

考虑以下场景：

用 "立刻加列" 增加列 A

写入数据行 1

用 "立刻加列" 增加列 B

写入数据行 2

删除列 B

我们推测一下 "删除列 B" 的最小代价：需要修改 数据行中的instant标志位或 "列数" 字段，这至少会影响到 "立刻加列" 之后写入的数据行，成本类似于重建数据

从以上推测可知：当出现 与 "立刻加列"  *** 作不兼容 的 DDL *** 作时，数据表需要进行重建，如下图所示：

扩展思考题：是否能设计其他的数据格式，取代instant标志位和 "列数" 字段，使得 加列/删列 *** 作都能 "立刻完成" （提示：考虑 加列 - 删列 - 再加列 的情况）

使用限制

在了解原理之后，我们来看看 "立刻加列" 的使用限制，就很容易能理解其中的前两项：

"立刻加列" 的加列位置只能在表的最后，而不能加在其他列之间

在元数据中，只记录了 数据行 应有多少列，而没有记录 这些列 应出现的位置。所以无法实现指定列的位置

"立刻加列" 不能添加主键列

加列 不能涉及聚簇索引的变更，否则就变成了 "重建" *** 作，不是 "立刻" 完成了

"立刻加列"不支持压缩的表格式

按照 WL 的说法："COMPRESSED is no need to supported"（没必要支持不怎么用的格式）

总结回顾

我们总结一下上面的讨论：

"立刻加列" 之所以高效的原因是：

在执行 "立刻加列" 时，不变更数据行的结构

读取 "旧" 数据时，"伪造" 新增的列，使结果正确

写入 "新" 数据时，使用了新的数据格式 (增加了 instant 标志位 和 "列数" 字段)，以区分新旧数据

读取 "新" 数据时，可以如实读取数据

"立刻加列" 的 "伪造" 手法，不能一直维持下去。当发生 与 "立刻加列" *** 作不兼容 的 DDL 时，表数据就会发生重建

回到之前遗留的两个问题：

"立刻加列" 是如何工作的

我们已经解答了这个问题

所谓 "立刻加列" 是否完全不影响业务，是否是真正的 "立刻" 完成

可以看到：就算是 "立刻加列"，也需要变更 数据字典，那么 该上的锁还是逃不掉的。也就是说 这里的 "立刻" 指的是 "不变更数据行的结构"，而并非指 "零成本地完成任务"

从表中删除列

从表中删除不再用于存储数据的列。当从数据库关系图的表中删除列时，将从数据库中删除该列及其包含的全部数据。

注意 一旦保存数据库关系图或表，就不能撤消该 *** 作。恢复删除列的唯一方法是不保存更改即关闭表或数据库关系图。

从表中删除列

在表或数据库关系图中，选择要删除的列。

右击该列，并从快捷菜单中选择"删除列"命令。

如果该列参与了关系，将出现一条消息提示确认删除选定列及其关系。选择"确定"按钮。

如果该列没有参与 CHECK 约束，则该列及附加到该列的所有约束、该列所参与的所有关系及其包含的所有数据都将被从数据库和关系图中删除。它们将在保存表或数据库关系图时从数据库中被删除。如果列确实参与了 CHECK 约束，当保存工作时，数据库服务器将拒绝所做的修改。也就是说，提交 *** 作将失败。若要删除参与 CHECK 约束的列，必须在删除该列之前修改或删除 CHECK 约束。

SQL中的列跟Excel中的列一样，在数据库中我们称之为字段；

而一个数据库就比如一个excel文件，数据库中的有很多张表，表跟excel中的工作薄一样，一个excel文也可以有很多工作薄；

一个excel工作薄有行和列，比如以下excel表格:

学号 学生姓名 课程号 课程名称 教师姓名 学生成绩 是否及格

1 唐 1001 数学 夏 91 及格

1 唐 1002 语文 张 52 不及格

1 唐 1003 英语 唐 93 及格

1 唐 1004 化学 郭 54 不及格

2 张 1001 数学 夏 81 及格

2 张 1002 语文 张 51 不及格

2 张 1003 英语 唐 83 及格

2 张 1004 化学 郭 84 及格

3 龙 1001 数学 夏 81 及格

3 龙 1002 语文 张 81 不及格

3 龙 1003 英语 唐 90 不及格

3 龙 1004 化学 郭 90 及格

-----------------

其中第一行为我们的标题，而后面的行为我们每个学生的相关信息，第一行的标题名就是数据库中的字段名，而下面的每一行在数据库中称记录。 第一个学生“唐”的数学成绩为“91”，那么“91”就是字段“学生成绩”对应所有记录中学生姓名为“唐”且课程名称为“数学”的值。

以上就是关于数据库中如何用语句删除表中的列全部的内容，包括:数据库中如何用语句删除表中的列、如何在sqlserver中获取表的所有列信息、怎么将数据库中 获得表的所有列等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！