建表：CREATE TABLE

第 19 课，共 21 课 · 第 5 部分 · 改数据和建表 · 约 20 分钟

网店的每张表都是有人先「建」出来的：表叫什么、有哪几列、每列放什么类型的值、有什么规矩。DESCRIBE 能看到一张表是怎么定义的：

main.sql

1DESCRIBE products;

输出

Field	Type	Null	Key	Default
id	int	NO	PRI	NULL
name	varchar(40)	NO		NULL
category	varchar(20)	YES		NULL
price	decimal(8,2)	YES		NULL
stock	int	YES		NULL

5 行

每一行是一列：Type 是类型，Null 是能不能为空，Key 里的 PRI 表示主键。DESCRIBE 是 MySQL 的写法，别的数据库不一样：PostgreSQL 的命令行里用 \d products，SQLite 用 PRAGMA table_info(products)。这一课我们自己建表。

CREATE TABLE

网店想让客户给商品写评价。新建一张 reviews 表：括号里一列一行，先写列名，再写类型，列和列之间用逗号隔开：

main.sql

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    product_id INT,4    stars INT,5    comment VARCHAR(100)6);7 8INSERT INTO reviews (id, product_id, stars, comment)9VALUES (1, 5, 5, '音质很好'),10    (2, 1, 4, '纸不错，就是有点薄');11 12SELECT * FROM reviews;

输出

第 1 行的语句

Query OK，影响了 0 行

第 8 行的语句

Query OK，影响了 2 行 · Records: 2 Duplicates: 0 Warnings: 0

第 12 行的语句

id	product_id	stars	comment
1	5	5	音质很好
2	1	4	纸不错，就是有点薄

2 行

建好的表一开始是空的，INSERT 进去的行才是它的数据。

常用的类型

每一列都要说清楚放什么类型的值。最常用的几个：

类型	放什么	例子
`INT`	整数	编号、数量、库存
`VARCHAR(n)`	文字，最多 n 个字符	名字、城市、评价
`DECIMAL(m, d)`	精确的小数：一共 m 位，其中 d 位小数	价格、金额
`DATE`	日期，写成 `'2025-10-28'`	注册日期、下单日期

价格用 DECIMAL 不用别的小数类型，是因为它算钱不会有误差。VARCHAR(n) 的长度是规矩，放不下就报错：

main.sql

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    comment VARCHAR(10)4);5 6INSERT INTO reviews (id, comment)7VALUES (1, '音质很好，戴久了耳朵也不疼，推荐');

输出

Query OK，影响了 0 行

ERROR 1406 (22001): Data too long for column 'comment' at row 1

这段文字比「comment」规定的长度还长，放不下。

ERROR 1406：这段文字比 10 个字符长。

PRIMARY KEY：主键

主键是一行的「身份证号」：每一行都要有，而且不能重复（重复就是上一课的 ERROR 1062）。几乎每张表都应该有主键。

主键也可以由两列一起组成。网店的 order_items 就是 PRIMARY KEY (order_id, product_id)：同一个订单里，同一件商品只出现一行。

编号总要自己想很麻烦。MySQL 可以给主键加上 AUTO_INCREMENT，插入时不写 id，它自动用 1、2、3……：

main.sql

1CREATE TABLE reviews (2    id INT AUTO_INCREMENT PRIMARY KEY,3    stars INT4);5 6INSERT INTO reviews (stars) VALUES (5), (3), (4);7 8SELECT * FROM reviews;

输出

第 1 行的语句

Query OK，影响了 0 行

第 6 行的语句

Query OK，影响了 3 行 · Records: 3 Duplicates: 0 Warnings: 0

第 8 行的语句

id	stars
1	5
2	3
3	4

3 行

AUTO_INCREMENT 是 MySQL 的写法，别的数据库可能写成 SERIAL（PostgreSQL）或 GENERATED ALWAYS AS IDENTITY。

NOT NULL 和 DEFAULT

NOT NULL 规定这一列必须有值；DEFAULT 值 规定插入时没写这一列，就用这个值（不写 DEFAULT 的话是 NULL）：

main.sql

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    stars INT NOT NULL,4    comment VARCHAR(100),5    verified INT NOT NULL DEFAULT 06);7 8INSERT INTO reviews (id, stars) VALUES (1, 5);9 10SELECT * FROM reviews;

输出

第 1 行的语句

Query OK，影响了 0 行

第 8 行的语句

Query OK，影响了 1 行

第 10 行的语句

id	stars	comment	verified
1	5	NULL	0

1 行

comment 没写，是 NULL；verified 没写，用了默认值 0。要是 stars 写成 NULL：

main.sql

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    stars INT NOT NULL4);5 6INSERT INTO reviews (id, stars) VALUES (1, NULL);

输出

Query OK，影响了 0 行

ERROR 1048 (23000): Column 'stars' cannot be null

「stars」这一列规定了 NOT NULL，不能是空值。

ERROR 1048：stars 不能是空值。

先猜一猜

查出来的 body 和 pinned 是什么？

1CREATE TABLE notes (2    id INT PRIMARY KEY,3    body VARCHAR(50),4    pinned INT DEFAULT 05);6INSERT INTO notes (id) VALUES (1);7SELECT body, pinned FROM notes;

插入时两列都没写。body 没有默认值，所以是 NULL；pinned 有 DEFAULT 0，所以是 0。

FOREIGN KEY：外键

评价里的 product_id 应该是一件真有的商品。FOREIGN KEY (列) REFERENCES 别的表 (列) 就是这条规矩：这一列的值，必须在那张表里找得到。

main.sql

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    product_id INT NOT NULL,4    stars INT NOT NULL,5    FOREIGN KEY (product_id) REFERENCES products (id)6);7 8INSERT INTO reviews (id, product_id, stars) VALUES (1, 5, 5);9SELECT * FROM reviews;10 11INSERT INTO reviews (id, product_id, stars) VALUES (2, 99, 4);

输出

第 1 行的语句

Query OK，影响了 0 行

第 8 行的语句

Query OK，影响了 1 行

第 9 行的语句

id	product_id	stars
1	5	5

1 行

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`town`.`reviews`, CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

「product_id」的值 99 在表「products」里找不到：外键要指向一行已经有的数据。先往「products」里插入它，或者改成已有的值。

5 号商品有，第一行插进去了；没有 99 号商品，第二行被拒绝：ERROR 1452。网店的 orders.customer_id、order_items.product_id 都是这样的外键，所以上一课删 1 号商品时报了 ERROR 1451：反过来，被指着的行也不能随便删。

ALTER TABLE：加一列

表建好了也能改。ALTER TABLE 表 ADD COLUMN 列类型 加一列，已有的行在这一列都是 NULL（或者默认值）：

main.sql

1ALTER TABLE customers ADD COLUMN phone VARCHAR(20);2 3UPDATE customers SET phone = '138-0000-0001' WHERE id = 1;4 5SELECT id, name, phone FROM customers WHERE id <= 3;

输出

第 1 行的语句

Query OK，影响了 0 行 · Records: 0 Duplicates: 0 Warnings: 0

第 3 行的语句

Query OK，影响了 1 行 · Rows matched: 1 Changed: 1 Warnings: 0

第 5 行的语句

id	name	phone
1	林晓	138-0000-0001
2	王磊	NULL
3	陈静	NULL

3 行

DROP TABLE：删表

DROP TABLE 表 把整张表连同里面的数据一起删掉，删了再查就是「没有这张表」：

main.sql

1CREATE TABLE reviews (id INT PRIMARY KEY, stars INT);2DROP TABLE reviews;3 4SELECT * FROM reviews;

输出

第 1 行的语句

Query OK，影响了 0 行

第 2 行的语句

Query OK，影响了 0 行

ERROR 1146 (42S02): Table 'town.reviews' doesn't exist

没有叫「reviews」的表。先 CREATE TABLE 建表，或者检查一下表名有没有写错。

和 DELETE FROM 表（删光所有行，表还在）不一样，DROP TABLE 连表都没了。还被外键指着的表删不了：

main.sql

1DROP TABLE customers;

输出

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'orders_ibfk_1' on table 'orders'.

表「orders」有外键指向「customers」，要先删掉「orders」（或者它的外键）。

ERROR 3730：orders 的外键还指着 customers。

练一练

写好代码，点检查。提示随便看，实在想不出来再看答案。

练习 1

优惠券

建一张 coupons 表：code（文字，最多 20 个字符，主键）、discount（金额，DECIMAL(5, 2)，不能为空）、expires（日期）。插入两张券：WELCOME 减 5 元、2025-12-31 到期；AUTUMN 减 10 元、没有到期日。最后查出整张表，按 code 排。

要显示

code	discount	expires
AUTUMN	10.00	NULL
WELCOME	5.00	2025-12-31

2 行

exercise1.sql

1SELECT * FROM coupons2ORDER BY code;

输出

点「运行」或按 Ctrl+Enter

练习 2

心愿单

3 号客户想把台灯（6 号商品）加进心愿单，可代码报错了。找到错在哪，改好它。最后的查询不用改。

要显示

name	product	quantity
陈静	台灯	1

1 行

exercise2.sql

1CREATE TABLE wishlist (2    customer_id INT NOT NULL,3    product_id INT NOT NULL,4    quantity INT NOT NULL DEFAULT 1,5    PRIMARY KEY (customer_id, product_id),6    FOREIGN KEY (customer_id) REFERENCES customers (id),7    FOREIGN KEY (product_id) REFERENCES products (id)8);9 10INSERT INTO wishlist (customer_id, product_id) VALUES (3, 60);11 12SELECT c.name, p.name AS product, w.quantity13FROM wishlist AS w14JOIN customers AS c ON c.id = w.customer_id15JOIN products AS p ON p.id = w.product_id;

输出

点「运行」或按 Ctrl+Enter

练习 3

促销标记

给 products 加一列 on_sale（整数，不能为空，默认 0），再把厨房类商品的 on_sale 设成 1。最后查出价格低于 13 元的商品的 name、price、on_sale，按 id 排。

要显示

name	price	on_sale
笔记本	4.50	0
中性笔（10 支）	6.00	0
水杯	12.00	1
数据线	8.50	0
咖啡杯	9.90	1
帆布袋	11.00	0

6 行

exercise3.sql

1SELECT name, price2FROM products3WHERE price < 134ORDER BY id;

输出

点「运行」或按 Ctrl+Enter

这一课学会了

CREATE TABLE 表 (列类型规矩, …); 建表，DESCRIBE 表; 看它的定义（MySQL 的写法）。
常用类型：INT、VARCHAR(n)、DECIMAL(m, d)（钱用它）、DATE。
PRIMARY KEY：每行都有、不能重复；MySQL 的 AUTO_INCREMENT 自动编号。
NOT NULL 不许空值（ERROR 1048）；DEFAULT 是没写这一列时用的值。
FOREIGN KEY … REFERENCES …：值必须在另一张表里找得到（ERROR 1452）。
ALTER TABLE 表 ADD COLUMN … 加列；DROP TABLE 表 连表带数据一起删。