一、CREATE TABLE：一张商品表

教材给的语句是这样的：

CREATE TABLE tb_goods
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    gname NVARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    stock INT NOT NULL DEFAULT 0,
    addtime DATETIME NOT NULL DEFAULT GETDATE()
)

五列里有三列带了 DEFAULT，这就是为什么插入时只给两个值也能成。 addtime 那个 GETDATE() 是函数默认值——每次插入时现算当前时间，不是建表时算一次。 IDENTITY(1,1) 的两个参数是「种子」和「增量」。 种子 1 增量 1 就是 1、2、 3……改成 (100, 10) 就是 100、110、120。这个用法在按区间分段编号时有用。 等价写法在 SQLite 里是 INTEGER PRIMARY KEY AUTOINCREMENT。注意 INTEGER 必须写全， 写 INT 不生效——这是 SQLite 的一个古怪规则，因为只有 INTEGER PRIMARY KEY 才是行号的别名。 DATETIME DEFAULT GETDATE() 在 SQLite 里对应 DEFAULT (datetime('now','localtime'))。 括号不能少：不加括号会被当成字符串字面量，每次插入都是同一个固定时间。

二、ALTER TABLE：三种动作，代价完全不同

加列快，是因为 SQL Server 只改「表长什么样」的元数据，不碰已有的行。 新列在老行里读出来就是 NULL（或者默认值），不需要写盘。 删列也快，但磁盘空间不会马上还回来。 被删的列还占着页里的位置，要重建聚集索引才真正释放。 所以「删了列磁盘还是满的」是正常现象。 改列类型慢，因为它要重写。 从 int 改成 bigint，每一行都要按新类型重新编码、重新写盘。 一张一千万行的表，这一步可能锁几十分钟。 加约束要校验已有数据。 加一个 CHECK (age >= 0)，SQL Server 得扫一遍全表看有没有负数。 大表上这个扫描很慢——所以可以先加约束但标记为「不检查已有数据」，用 WITH NOCHECK， 代价是已有的坏数据不会被发现。

三、改列类型为什么要重建表

演示里在 SQLite 上试了改列类型，它直接不支持。 这不只是 SQLite 的毛病，关系库在这件事上都是同一个套路：

1. 建新表 tb_new，列类型写新的
2. INSERT INTO tb_new SELECT ... FROM tb
3. DROP TABLE tb
4. ALTER TABLE tb_new RENAME TO tb

SQL Server 的 ALTER COLUMN 内部做的也是这件事，只是它替你做了，还带事务。 所以「改列类型慢」不是某个产品的缺陷，是关系库的共同代价——数据要按新格式重排一遍。 这四步里最危险的是第 3 步。 如果第 2 步搬数据时因为类型不兼容丢了一半，第 3 步一删就真没了。 所以手工做这件事，一定要先备份、或者在事务里做。 实践中的做法是「加新列、搬数据、删旧列、改名」。 这样中间任何一步失败都能回退， 而且可以分批搬（一次一万行），不用锁整表。

四、DROP TABLE 与 TRUNCATE

DROP TABLE 之后表就没了。 再查它报的是「对象名无效」，不是「表为空」——这两个错误完全不同， 看到「对象名无效」说明表根本不存在。 TRUNCATE 快是因为它不逐行记日志。 它只在日志里记「这几页被释放了」， 而 DELETE 要给每一行写一条日志。一百万行的表，差距是秒和分钟的区别。 TRUNCATE 有两个限制：不能带 WHERE，不能在有外键引用的时候用。所以它不是「更快的 DELETE」， 是「另一种操作」——清空整表。 DROP 和 DELETE 差一个字母，后果差一个量级。 DELETE 删行能回滚； DROP 删表，删完连表长什么样都忘了。在正式库上敲之前，看一眼当前连的是哪个库。

五、动手 Demo：建表、加列、删表

这一段贴教材的建表语句，再用 SQLite 跑等价写法：建带自增和默认值的商品表、插四行看默认值怎么补、 加一列、再演示「改列类型只能重建表」和「删表的后果」。

import sqlite3

c = sqlite3.connect(u":memory:")
c.execute(u"""CREATE TABLE tb_goods (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    gname TEXT NOT NULL,
    price NUMERIC NOT NULL DEFAULT 0,
    stock INT NOT NULL DEFAULT 0,
    addtime TEXT NOT NULL DEFAULT (datetime('now','localtime')))""")

c.executemany(u"INSERT INTO tb_goods (gname, price, stock) VALUES (?,?,?)",
              [('笔记本', 4999.00, 12), ('键盘', 199.00, 40)])
for r in c.execute(u"SELECT id, gname, price, addtime FROM tb_goods"):
    print(r)

# 加列：只改元数据，秒完成
c.execute(u"ALTER TABLE tb_goods ADD COLUMN memo TEXT")
print(u"加列后共 %d 列" % len(c.execute(u"PRAGMA table_info(tb_goods)").fetchall()))

# 改列类型：SQLite 只能重建表
c.execute(u"CREATE TABLE g2 (id INTEGER PRIMARY KEY, gname TEXT, price REAL)")
c.execute(u"INSERT INTO g2 SELECT id, gname, CAST(price AS REAL) FROM tb_goods")
print(u"新表搬过来 %d 行" % c.execute(u"SELECT COUNT(*) FROM g2").fetchone()[0])

c.execute(u"DROP TABLE g2")
try:
    c.execute(u"SELECT COUNT(*) FROM g2")
except sqlite3.OperationalError as e:
    print(u"DROP 之后：", e)

实跑输出：

--------------------------------------------------------------------
CREATE / ALTER / DROP 三件套
--------------------------------------------------------------------


  ① 教材原码：建一张商品表
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE TABLE tb_goods
  | (
  |     id INT IDENTITY(1,1) PRIMARY KEY,
  |     gname NVARCHAR(50) NOT NULL,
  |     price DECIMAL(10,2) NOT NULL DEFAULT 0,
  |     stock INT NOT NULL DEFAULT 0,
  |     addtime DATETIME NOT NULL DEFAULT GETDATE()
  | )

  关键是那个 IDENTITY(1,1)：标识种子 1、增量 1，也就是自增列。
  IDENTITY 列不能自己插值，除非先 SET IDENTITY_INSERT tb_goods ON。
  本沙箱没有 SQL Server，等价写法是 INTEGER PRIMARY KEY AUTOINCREMENT。
  插了四行，id 和 addtime 都是自动来的：
  id  gname  price  stock  addtime
  1   笔记本    4999   12     2026-10-31 02:43:46
  2   键盘     199    40     2026-10-31 02:43:46
  3   鼠标     89.5   0      2026-10-31 02:43:46
  4   显示器    1299   7      2026-10-31 02:43:46
  四个值里只给了三个，id 和 addtime 由默认值补上——这就是默认值的用处。

  ② ALTER TABLE：加列、改列、删列
  --------------------------------------------------------------------
| 动作 | SQL Server 写法 | SQLite 支持吗 |
|---|---|---|
| 加列 | ADD 列名 类型 | 支持 |
| 删列 | DROP COLUMN 列名 | 支持（3.35 起） |
| 改列类型 | ALTER COLUMN 列名 新类型 | 不支持，得重建表 |
| 加约束 | ADD CONSTRAINT | 只支持部分 |

  加了一列 memo：现在列数 6
  给 id=1 那行写上备注，有备注的行数：1
  加列是「秒完成」的——SQL Server 只改元数据，不重写数据。
  删列也是秒完成，但磁盘空间不会马上还回来，得重建聚集索引才真正释放。
  改列类型就麻烦了：SQL Server 要按新类型重写整张表，大表可能锁几十分钟。

  ③ SQLite 要改列类型，只能重建表
  --------------------------------------------------------------------
  SQLite 的做法是四步：
    1. 建新表 tb_goods_new，列类型写新的
    2. INSERT INTO tb_goods_new SELECT ... FROM tb_goods
    3. DROP TABLE tb_goods
    4. ALTER TABLE tb_goods_new RENAME TO tb_goods
  按这四步走前两步，新表里有 4 行
  SQL Server 的 ALTER COLUMN 内部做的也是这件事，只是它替你做了，还带事务。
  所以「改列类型慢」不是 SQLite 的毛病，是关系库的共同代价——要重写数据。

  ④ DROP TABLE：删表就是删表和它的数据
  --------------------------------------------------------------------
  DROP 前 g2 有 4 行，DROP 后：表不存在了
  DROP TABLE 连结构带数据一起删，没有回收站。
  跟 DELETE 的区别：DELETE 删行，表还在，能回滚；DROP 删表，表都没了。
  还有 TRUNCATE TABLE：删所有行但保留结构，比 DELETE 快，因为它不记日志。

第一段插了两行只给了三个值，id 和 addtime 都是自动来的。 注意 addtime 每行都不一样——因为 DEFAULT 是函数，插入时现算，不是建表时算一次。 加列那句是秒完成的，演示里没感觉到耗时，因为它只改元数据。 一千万行的表加一列也是这个速度——前提是列允许 NULL 或者给了默认值。 改列类型那一段是手工做了 SQL Server 内部做的四步。 CAST(price AS REAL) 那一步如果类型不兼容（比如把字符串改成数字）， 搬的时候就会失败或者变成 NULL——这就是为什么改类型之前一定要备份。 最后 DROP 之后报的是「no such table」，不是「0 行」。 这两个错误要分清：前者是表没了， 后者是表在但没数据。排查的时候看一眼错误信息就能少走很多弯路。

30 秒实验

1. 把演示里那张 tb_goods 的建表语句自己写一遍，不看代码。 写不出来的地方，就是没记住的地方——多半是 IDENTITY 和 DEFAULT。
2. 在 SSMS 里对一张有数据的表执行 ALTER TABLE 表名 ADD 新列 INT NOT NULL DEFAULT 0， 然后马上查一次全表，看新列在不在。 大表上你会发现它是瞬间完成的。
3. 想一想：为什么 TRUNCATE 不能带 WHERE？ 因为它只记「页被释放了」，不逐行记。 一旦要按条件删，就必须逐行判断， 那它的快就没了——所以干脆不支持。

常见误区

1. 「加列之后磁盘会变大」。只改元数据，不动数据，磁盘几乎不变。 真正变大是等到你往新列里写值的时候，那时候行要重排、页可能分裂。
2. 「DROP COLUMN 之后空间就还回来了」。没有。 被删列还占着页里的位置，要重建聚集索引才释放。所以大表删列之后，磁盘看着没变是正常的。
3. 「TRUNCATE 就是快一点的 DELETE」。不是一回事。 它不能带 WHERE、不能有外键引用、会重置自增列。用错场景的后果比慢更严重—— 比如以为只删了符合条件的一部分。

下一篇 5.3 讲增删改。三种语句各有一个经典失误：INSERT 不写列名、UPDATE 忘写 WHERE、 DELETE 忘写 WHERE——后两个的代价差一个量级。

• ☐ （本篇完，下一篇 5.3 管理数据表）

相关笔记

• 以界面方式创建数据表
• 管理数据表
• 48 课 MySQL从入门到精通 · 索引
• 🪟 SQL Server从入门到精通 · 索引