MySQL创建表与字段约束操作详解

一、查看与创建基础表

在开始建表之前，通常需要确认数据库中是否已存在同名表，以防止重复创建引发错误。

第1步：列出所有数据表

使用 SHOW TABLES; 命令即可查看当前数据库下所有的表名。这是进入新环境后的常规检查动作。

第2步：编写建表语句

MySQL建表的标准格式需指定表名、列定义以及存储引擎。通常推荐使用InnoDB引擎以支持事务，并统一字符集为utf8以避免乱码。

CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type constraints,
    ....
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

第3步：实战创建一个users表

下面以一个具体的“用户信息表”为例，定义四个关键字段：

• id: 设为 INT 类型，作为主键 (PRIMARY KEY)，自动增长 (AUTO_INCREMENT)，且不可为空 (NOT NULL)。
• name: 存储姓名，采用 VARCHAR(50) 变长字符串，限定最大50字符，必填。
• birth_date: 记录出生日期，使用 DATE 类型。
• phone: 存储手机号，同样使用 VARCHAR(11)（考虑到可能存在 +86 等扩展情况，有时也设为更长），要求唯一 (UNIQUE) 且不为空。

对应的完整SQL代码如下：

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    birth_date DATE,
    phone VARCHAR(11) NOT NULL UNIQUE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行上述命令后，即成功生成了一张拥有ID、姓名、生日和手机号的表。每个字段的声明都明确了其存储的数据形态。

二、常用数据类型概览

在建表时，选择正确的数据类型不仅能节省存储空间，还能提高查询效率。以下是三类最基础的数据类型：

1. 数值型

数值分为整数和浮点数。浮点数中单精度 (FLOAT) 与双精度 (DOUBLE) 的区别主要体现在内存占用字节数、有效数字位数以及能表示的最大数值范围上。对于金额计算通常建议用DECIMAL而非浮点型以防精度丢失。

2. 字符串类型

常用的有 CHAR 和 VARCHAR。CHAR是定长字符串，填充空格以达到指定长度；VARCHAR则是变长字符串，仅在尾部存储必要的空格，更节省空间。除此之外还有TEXT大文本类型。

3. 日期与时间类型

MySQL支持多种日期格式，如 DATETIME、TIMESTAMP、DATE 等。选择合适的类型有助于处理时区转换和时间自动更新功能。

三、了解常用约束

约束用于限制填入表中的数据，保证数据的完整性和一致性。常见的约束包括：主键 (PRIMARY KEY)（唯一标识）、外键 (FOREIGN KEY)（关联其他表）、唯一 (UNIQUE)（不重复）、非空 (NOT NULL)以及默认值 (DEFAULT)。理解这些概念有助于设计出健壮的结构。

四、存储引擎配置

不同的存储引擎提供不同的锁机制和索引技巧。MyISAM读取速度快但不支持事务，而InnoDB支持事务和外键，是目前最常用的引擎。

可以通过以下命令查看所有支持的引擎及其属性：

SHOW ENGINES;

五、查看与管理表结构

在实际开发中，经常需要查看或修改已存在的表结构。这涉及到CRUD操作中的Schema变更。

第1步：查看表结构详情

使用 DESC 表名; 可以简洁地展示各字段名、类型、是否允许Null、键信息等。

第2步：查看建表原始语句

SHOW CREATE TABLE '表名'; 会返回完整的、可执行的建表SQL代码，方便迁移或备份。

第3步：重命名表

若发现表名命名不规范，可使用 RENAME TABLE 原表名 TO 新表名; 进行修改。

第4步：增删改字段

通过 ALTER TABLE 语句可以进行灵活的表结构变更：

• 添加字段：ALTER TABLE users ADD age INT DEFAULT 1;
• 添加描述字段：ALTER TABLE users ADD user_desc VARCHAR(50) DEFAULT '哈哈';
• 修改字段名/类型：ALTER TABLE users CHANGE user_desc userdesc VARCHAR(50); （这里将user_desc更名为userdesc）
• 删除字段：ALTER TABLE users DROP userdesc;

第5步：彻底删除表

DROP TABLE 表名; 执行后将永久移除该表及其中所有数据，请谨慎操作。

六、实战：在已有表中动态管理约束

很多时候表已经建好但未加全约束，或者业务逻辑变化导致需要调整。下面以 users1 表为例，演示如何在创建后补全和调整约束。

第一步：初始化一个最小化结构的表

仅保留主键，去除其他非必要约束：

CREATE TABLE users1 (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    birth_date DATE,
    phone VARCHAR(11)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

第二步：添加唯一性约束

为了防止不同账号注册相同的手机号，给 phone 字段加上唯一索引：

ALTER TABLE users1 ADD UNIQUE (phone);

第三步：删除该唯一约束

如果不再需要此限制，可通过删除索引来取消：

ALTER TABLE users1 DROP INDEX phone;

第四步：修改为非空约束

确保名字必须填写：

ALTER TABLE users1 MODIFY name VARCHAR(50) NOT NULL;

第五步：还原为非空约束

使用 CHANGE COLUMN 重新定义该列，去掉 NOT NULL：

ALTER TABLE users1 
CHANGE COLUMN `name` `name` VARCHAR(50) NULL ;

第六步：设置默认值

如果未提供生日，系统默认为某个日期：

ALTER TABLE users1 ALTER birth_date SET DEFAULT '1992-05-11';

第七步：删除默认值

ALTER TABLE users1 ALTER birth_date DROP DEFAULT;

以上就是MySQL创建表、管理字段与约束的详细教程。希望这些基础的SQL实操命令能帮助大家在搭建数据库架构时更加得心应手。