创建数据表

导学

数据库是容器，数据表才是实际存储数据的地方。本节学习 CREATE TABLE 语句的核心语法，理解主键、数据类型和约束的概念，并通过完整示例动手创建多张数据表，观察操作前后的数据状态变化。

定义

CREATE TABLE：DDL 语句，用于在指定数据库中创建一张新表。创建时需要声明列名、数据类型，以及可选的约束条件（如主键、非空、唯一等）。

核心语法

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
    [表级约束]
) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8mb4];

完整示例一：创建员工信息表

当前数据状态

首先确保有一个测试数据库：

CREATE DATABASE IF NOT EXISTS company
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE company;

-- 查看当前数据库中的表
SHOW TABLES;

执行结果：

Empty set (0.00 sec)

结果解读：新建的 company 数据库中没有任何表。

操作语句

CREATE TABLE IF NOT EXISTS employees (
    emp_id       INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID，主键',
    emp_name     VARCHAR(20) NOT NULL COMMENT '姓名',
    dept         VARCHAR(20) DEFAULT '技术部' COMMENT '部门',
    score        DECIMAL(5,2) COMMENT '绩效分'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='员工信息表';

操作后的数据状态

SHOW TABLES;

Tables_in_company
employees

查看表结构：

DESC employees;

Field	Type	Null	Key	Default	Extra
emp_id	int(11)	NO	PRI	NULL	auto_increment
emp_name	varchar(20)	NO		NULL
dept	varchar(20)	YES		技术部
score	decimal(5,2)	YES		NULL

查看完整建表语句：

SHOW CREATE TABLE employees\G

结果解读：

定义	含义
`INT`	整数类型，范围约 -21亿到 21亿
`AUTO_INCREMENT`	自增，插入时不指定则自动分配下一个值
`NOT NULL`	该列不允许为 NULL
`VARCHAR(20)`	变长字符串，最多 20 个字符
`DEFAULT '技术部'`	不指定值时的默认值
`DECIMAL(5,2)`	定点数，总共5位，小数点后2位
`PRIMARY KEY`	主键约束，唯一标识每行，自动创建索引
`ENGINE=InnoDB`	使用 InnoDB 存储引擎
`COMMENT`	注释，说明字段或表的业务含义

完整示例二：验证约束效果

当前数据状态

SELECT * FROM employees;

Empty set (0.00 sec)

结果解读：表已创建，但暂无数据。

操作语句：测试 NOT NULL 约束

INSERT INTO employees (emp_name, dept, score) VALUES (NULL, '技术部', 100);

操作后的数据状态：

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

结果解读：emp_name 列声明了 NOT NULL，插入 NULL 值时被拒绝。

操作语句：测试 DEFAULT 约束

先插入一条合法记录：

INSERT INTO employees (emp_name, score) VALUES ('大翔', 100);

SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	技术部	100

再插入一条不指定 dept 的记录：

INSERT INTO employees (emp_name, score) VALUES ('白歌', NULL);

SELECT * FROM employees WHERE emp_id = 2;

emp_id	emp_name	dept	score
2	白歌	技术部	NULL

结果解读：未指定 dept 时，自动填充了默认值 技术部。未指定 score 时，填充了 NULL。

操作语句：测试 PRIMARY KEY 唯一性

尝试插入重复的主键：

INSERT INTO employees (emp_id, emp_name, dept, score) VALUES (1, '小崔', '市场部', 80);

操作后的数据状态：

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

结果解读：emp_id 是主键，自动具有唯一性约束，重复值被拒绝。

完整示例三：创建带外键的关联表

当前数据状态

SHOW TABLES;

Tables_in_company
employees

结果解读：目前只有 employees 表。接下来创建绩效记录表，演示表之间的关系。

操作语句

-- 创建绩效记录表（含外键）
CREATE TABLE IF NOT EXISTS scores (
    score_id    INT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
    emp_id      INT NOT NULL COMMENT '员工ID',
    score       DECIMAL(5,2) COMMENT '绩效分',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='绩效记录表';

操作后的数据状态

SHOW TABLES;

Tables_in_company
employees
scores

查看绩效记录表的建表语句：

SHOW CREATE TABLE scores\G

结果解读：

FOREIGN KEY 建立了表与表之间的关系
ON DELETE CASCADE 表示删除员工时，自动删除其绩效记录
ON UPDATE CASCADE 表示员工ID变更时，绩效记录中的 emp_id 同步更新

验证外键约束：

-- employees 表当前有 2 条数据（大翔、白歌）
SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

尝试插入一条不存在的员工绩效记录：

INSERT INTO scores (emp_id, score) VALUES (999, 85.5);

操作后的数据状态：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

结果解读：外键约束保证了数据的引用完整性。emp_id = 999 在 employees 表中不存在，因此插入被拒绝。

插入合法记录：

INSERT INTO scores (emp_id, score) VALUES (1, 90);

SELECT * FROM scores;

score_id	emp_id	score	created_at
1	1	90.00	2024-01-15 10:35:00

结果解读：emp_id = 1（大翔）在 employees 表中存在，插入成功。

主键的设计原则

对于 MySQL 5.7 + InnoDB：

推荐使用自增整数（AUTO_INCREMENT）作为主键，InnoDB 的聚簇索引对此做了深度优化
避免使用业务字段（如手机号、身份证号）作为主键，因为业务规则可能变更
避免使用 UUID 作为主键，虽然全局唯一，但随机写入会导致页分裂，性能下降

场景：不同主键策略对比

当前数据状态：

-- 创建自增主键表
CREATE TABLE auto_pk (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(20)
) ENGINE=InnoDB;

-- 创建 UUID 主键表
CREATE TABLE uuid_pk (
    id CHAR(36) PRIMARY KEY,
    emp_name VARCHAR(20)
) ENGINE=InnoDB;

操作语句：分别插入数据观察差异。

-- 自增主键：无需指定 id
INSERT INTO auto_pk (emp_name) VALUES ('大翔'), ('白歌');

SELECT * FROM auto_pk;

id	emp_name
1	大翔
2	白歌

-- UUID 主键：必须手动生成
INSERT INTO uuid_pk (id, emp_name) VALUES
('550e8400-e29b-41d4-a716-446655440000', '大翔'),
('550e8400-e29b-41d4-a716-446655440001', '白歌');

SELECT * FROM uuid_pk;

id	emp_name
550e8400-e29b-41d4-a716-446655440000	大翔
550e8400-e29b-41d4-a716-446655440001	白歌

结果解读：

自增主键：自动递增，写入磁盘时数据顺序排列，查询效率高
UUID 主键：随机分布，插入时可能导致 InnoDB 页分裂，且占用 36 字节（自增 INT 仅 4 字节）

清理测试表：

DROP TABLE IF EXISTS auto_pk;
DROP TABLE IF EXISTS uuid_pk;

常见误区

误区	正解
"每张表都必须有主键"	MySQL 5.7 允许没有主键的表，但 InnoDB 会隐式创建 6 字节的隐藏主键。强烈建议显式声明主键。
`VARCHAR(255)` 越多越好	VARCHAR 按需定义，过大的声明会浪费内存（排序缓冲区按声明长度分配）。
`TIMESTAMP` 和 `DATETIME` 没区别	`TIMESTAMP` 范围小（1970-2038）、自动转时区、支持自动更新；`DATETIME` 范围大、不转时区。

验证 TIMESTAMP 和 DATETIME 的区别：

CREATE TABLE time_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ts_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    dt_col DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO time_test (id) VALUES (1);

SELECT * FROM time_test;

id	ts_col	dt_col
1	2024-01-15 10:40:00	2024-01-15 10:40:00

结果解读：在 MySQL 5.7 中，两者默认都可以自动填充当前时间。但 TIMESTAMP 会随会话时区转换，而 DATETIME 不会。且 TIMESTAMP 最大只支持到 2038 年。

清理：

DROP TABLE IF EXISTS time_test;

面试考点

Q：为什么 InnoDB 推荐用自增 ID 做主键？

InnoDB 使用聚簇索引，数据按主键顺序存储。自增 ID 是顺序写入，磁盘 I/O 连续，效率高；UUID 或随机 ID 会导致频繁的页分裂和碎片，降低插入和查询性能。

Q：NOT NULL 有什么好处？

减少 SQL 执行时的 NULL 判断开销；2. 避免聚合函数（如 COUNT）出现意外结果；3. 明确语义，减少程序层的空指针风险。

Q：CHAR 和 VARCHAR 怎么选？

CHAR 定长，适合长度固定的场景（如 MD5 值、性别标志），查询略快；VARCHAR 变长，适合长度差异大的场景（如姓名、地址），节省空间。MySQL 5.7 中 VARCHAR 的存储效率已足够高，绝大多数场景优先用 VARCHAR。

小结

CREATE TABLE 需要声明列名、数据类型和约束
主键推荐用 INT/BIGINT UNSIGNED AUTO_INCREMENT
必须显式指定 ENGINE=InnoDB 和 CHARSET=utf8mb4
善用 COMMENT 给字段和表添加注释，这是良好的工程习惯
外键约束保证了数据的引用完整性

清理测试数据库（可选）：

-- DROP DATABASE IF EXISTS company;

下一章引子：表创建好了，但里面是空的。接下来学习如何向表中插入数据。