建表¶

登录数据库管理系统¶

这里用的是MySQL。

mysql -u root -p

成功登录后可通过 show databases; 查看当前所有数据库。

建库¶

create database testDB charset utf8mb4 collate utf8mb4_general_ci;

charset 用于指定字符集，collate 指定排序规则，这里是 utf8mb4_general_ci，表示使用 utf8mb4 字符集，排序规则为 general_ci。

删库¶

drop database testDB;

DROP DATABASE是一个DDL（数据定义语言）操作，它不可回滚，也没有事务保护。一旦执行，数据会立即被永久移除，不进入回收站。如果没有备份，数据将几乎无法恢复。因此，在生产环境中，执行此命令需要极高的权限和极其谨慎的确认，稍有不慎就会导致“删库跑路”的灾难性后果。

建表¶

• 建表 `Student

  create table if not exists `Student` (
    `id` int not null auto_increment comment '学生编号（自增主键）',
    `name` varchar(50) not null comment '学生姓名',
    `student_no` varchar(20) not null comment '学号',
    `sex` enum('男', '女') default null comment '性别',
    primary key (`id`)
  ) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci comment='学生表';
  

  • id 所在行中，auto_increment 表示该列自增，每次插入新行时，该列的值会自动加1；同时，后面的 primary key 表示该列为主键。

  • not_null 表示为某列添加非空约束。

  • comment '...' 表示为该列添加注释。

  • engine=innodb 表示显式指定存储引擎为 InnoDB，这是 MySQL 默认的存储引擎，也是最常用的存储引擎。

• 建表 Course

  create table if not exists `Course` (
    `course_id` int not null auto_increment comment '课程ID，自增主键',
    `course_name` varchar(100) not null comment '课程名称',
    `credit` tinyint unsigned not null comment '学分（整数）',
    primary key (`course_id`)
  ) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci comment='课程表';
  

• 建表 Enrollment

  create table if not exists `Enrollment` (
    `enroll_id` int not null auto_increment comment '选课ID（自增主键）',
    `student_no` varchar(20) not null comment '学号',
    `course_id` int not null comment '课程ID',
    `grade` decimal(5,2) default null comment '成绩',
    primary key (`enroll_id`),
    key `idx_student_no` (`student_no`),
    key `idx_course_id` (`course_id`),
    constraint `fk_enrollment_student` foreign key (`student_no`) references `Student` (`student_no`) on delete restrict on update cascade,
    constraint `fk_enrollment_course` foreign key (`course_id`) references `Course` (`course_id`) on delete restrict on update cascade
  ) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci comment='选课表';
  

  • enroll_id 所在行中，auto_increment 表示该列自增；primary key 表示该列为主键。

  • grade 使用 decimal(5,2) 存储成绩，最多两位小数；default null 表示未录入成绩时可为空。

  • key 为列建立索引，加快按学号或课程 ID 查询。

  • foreign key ... references 建立外键，关联 Student 与 Course 表；on delete restrict 表示有关联记录时不可删除，on update cascade 表示主表 ID 更新时同步更新。

  Warning

  直接运行这个建表代码会出现如下错误：

  ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'fk_enrollment_student' in the referenced table 'student'
  

  这是因为在父表 Student 中，被外键引用的列 student_no 缺少索引。

  MySQL 要求被引用的列（即父表中被 FOREIGN KEY 引用的列）必须拥有一个索引（可以是 PRIMARY KEY、UNIQUE KEY 或普通 KEY），否则无法创建外键约束。

  可先使用以下命令为 student_no 列添加索引再运行建表代码：

  alter table `Student` 
  add unique index `sid_index`(`student_no`) using btree;
  

修改表结构¶

表创建之后，若需调整列、索引或约束，不必删表重建，可使用 ALTER TABLE。ALTER 属于 DDL（数据定义语言），修改的是表的结构，而不是表中的业务数据。

与 CREATE / DROP 的区别¶

添加索引¶

建表 Enrollment时，因 Student.student_no 缺少唯一索引导致外键创建失败，可先执行：

alter table `Student`
add unique index `sid_index`(`student_no`) using btree;

也可为已有表添加普通索引：

alter table `Enrollment` add index `idx_grade` (`grade`);

删除索引：

alter table `Enrollment` drop index `idx_grade`;

更多索引用法见辅助索引。

添加列¶

为 Student 表新增一列「联系电话」：

alter table `Student`
add `phone` varchar(15) null comment '联系电话' after `sex`;

• after 列名：指定新列插入位置（可省略，默认加在最后一列）。

修改列¶

修改已有列的类型、长度、是否可空、注释等，使用 MODIFY：

alter table `Student`
modify `name` varchar(100) not null comment '学生姓名';

示例：login 表的 password_hash 原为 CHAR(60)（适配 bcrypt），若改用 sha2(..., 256) 存储密码，哈希结果为 64 位十六进制字符串，需扩列：

alter table login
modify password_hash char(64) null comment '密码哈希';

若新型号无法容纳已有数据（如把 VARCHAR(100) 改成 VARCHAR(10)），ALTER 会报错。

重命名列¶

CHANGE 可同时改名并修改列定义（须写出完整的旧列名和新定义）：

alter table `Student`
change `sex` `gender` enum('男', '女') default null comment '性别';

若只改名、不改类型，新定义须与旧定义一致。

删除列¶

alter table `Student` drop column `phone`;

删除列会永久移除该列及其数据，操作前请确认无业务依赖。

重命名表¶

alter table `Student` rename to `student_info`;
-- 等价写法
rename table `Student` to `student_info`;

查看当前表结构¶

修改前后可用以下命令确认结构：

desc `Student`;
show create table `Student`;
show index from `Student`;