MySQL表结构与约束操作指南

表结构的创建与查看

在进行表操作前，首先需使用 USE 库名; 选定目标数据库。以下是表的基本生命周期管理命令。

第1步：创建表

目的：在数据库中建立新的数据表结构。入口为MySQL命令行或客户端工具。语法如下：

CREATE TABLE table_name(
  属性名 数据类型,
  属性名 数据类型,
  ...
);

注意：表名不能与MySQL关键字冲突（如CREATE, USE），建议采用 t_xxx 或 tab_xxx 命名规范。属性间用逗号分隔，最后一行无需逗号。

示例：创建一个部门表

CREATE TABLE t_dept(
  deptno int,
  dname varchar(20),
  loc varchar(20)
);

第2步：查看表结构

目的：检查已建表的具体定义信息，确保结构符合预期。分为两种方式：

• 简洁查看：使用 DESCRIBE 表名; 或简写 DESC 表名;，用于快速浏览字段名、类型及索引情况。
• 详细查看：使用 SHOW CREATE TABLE 表名 \G;。
  说明：使用 \G 作为结束符可使输出垂直排列，便于在终端中阅读复杂的SQL定义语句。

第3步：删除表

目的：彻底移除不再需要的表及其所有数据。执行后不可恢复，需谨慎操作。

DROP TABLE 表名;

表结构的修改 (ALTER TABLE)

当表中已有大量数据时，直接删除重建会导致数据重载等额外工作。使用 ALTER TABLE 可以在不丢失数据的前提下调整表结构。

第4步：重命名表

目的：修正表名以符合规范或业务变更需求。

ALTER TABLE old_table_name RENAME [TO] new_table_name;

第5步：增加字段

目的：在现有表中补充新列。可根据需要指定插入位置：

• 追加到最后：
  

  ALTER TABLE table_name ADD 属性名 数据类型;

• 插入到第一列：
  

  ALTER TABLE table_name ADD 属性名 数据类型 FIRST;

• 插入到指定列之后：
  

  ALTER TABLE table_name ADD 属性名 数据类型 AFTER 已有属性名;

第6步：删除字段

目的：移除表中不再使用的冗余列。

ALTER TABLE table_name DROP 属性名;

第7步：修改字段属性与顺序

目的：调整字段的数据类型、名称或物理存储顺序。需注意区分 MODIFY 与 CHANGE 的用法：

• 修改数据类型：只改变类型，不更名。使用 MODIFY。
  

  ALTER TABLE table_name MODIFY 属性名 新数据类型;

• 修改字段名：改变字段标识。必须使用 CHANGE 并提供旧名与新名。
  

  ALTER TABLE table_name CHANGE 旧属性名 新属性名 原数据类型;

• 同时改名和改类型：
  

  ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型;

• 调整字段顺序：
  将某字段调至首位：

  ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST;

  
  将某字段调至另一字段之后：

  ALTER TABLE table_name MODIFY 属性名1 数据类型 AFTER 属性名2;

数据完整性约束详解

完整性约束用于保证数据的准确性和一致性。MySQL支持多种标准约束以及特有的自增长约束。

第8步：设置非空约束 (NOT NULL)

作用：强制字段必须有值，不允许为 NULL。通常在创建表时直接指定。

CREATE TABLE table_name (
  属性名 数据类型 NOT NULL
);

第9步：设置默认值 (DEFAULT)

作用：当插入记录未对该字段赋值时，自动填入预设值。

CREATE TABLE table_name (
  属性名 数据类型 DEFAULT '默认值'
);

第10步：设置唯一约束 (UNIQUE)

作用：确保字段内的所有值互不相同，但允许存在多个 NULL 值（不同引擎行为可能略有差异，通常视为distinct）。

基础写法：

CREATE TABLE t_dept(
  deptno INT,
  dname VARCHAR(20) UNIQUE,
  loc VARCHAR(40)
);

命名约束：为方便后续管理，建议使用 CONSTRAINT 子句显式命名，推荐格式为 uk_字段名。

CREATE TABLE t_dept(
  deptno INT,
  dname VARCHAR(20),
  loc VARCHAR(40),
  CONSTRAINT uk_dname UNIQUE(dname)
);

第11步：设置主键约束 (PRIMARY KEY)

作用：唯一标识表中的每一行记录。主键必须满足 非空 且 唯一 两个条件。主键可以是单列，也可以是多列组合。

单字段主键：

-- 直接在列定义后声明
CREATE TABLE table_name(
  deptno INT PRIMARY KEY,
  dname VARCHAR(20)
);

-- 或使用命名约束
CREATE TABLE table_name(
  deptno INT,
  dname VARCHAR(20),
  CONSTRAINT pk_deptno PRIMARY KEY(deptno)
);

多字段主键（复合主键）：当单列无法唯一标识时，需组合多个字段。

CREATE TABLE table_name(
  属性1 数据类型,
  属性2 数据类型,
  ...,
  PRIMARY KEY (属性1, 属性2)
);

第12步：设置自增长 (AUTO_INCREMENT)

作用：常用于主键，实现ID的自动生成。该字段必须是整数类型，且一个表只能有一个自增长字段。默认从1开始，每次递增1。

CREATE TABLE table_name(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

第13步：设置外键约束 (FOREIGN KEY)

作用：建立表与表之间的参照关系，确保“多”的一方关联的“一”的一方记录真实存在。外键字段可以设为 NULL，但引用的被依赖字段必须包含主键或唯一约束。

假设 t_dept 为父表（一方），t_emp 为子表（多方）：

CREATE TABLE t_emp(
  emp_id INT PRIMARY KEY,
  dept_id INT,
  -- 其他字段
  CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES t_dept(deptno)
);

此处 fk_dept_id 是外键约束名，dept_id 是子表字段，t_dept 是父表，deptno 是父表被引用字段。

总结

掌握表的增删改查以及各类约束的使用，是构建健壮数据库的基础。建议在创建表初期就规划好主键、外键及非空/默认值策略，以减少后期因数据结构不合理导致的性能问题或数据不一致。以上就是 MySQL 表结构与约束操作的详细内容，更多关于 MySQL 数据库优化与设计规范的资料请关注本站其它相关文章！