第 7 章 · 表结构、约束与索引

CREATE TABLE `demo_book` (
  `id`       INT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title`    VARCHAR(150)  NOT NULL                COMMENT '书名',
  `isbn`     VARCHAR(20)   DEFAULT NULL            COMMENT 'ISBN',
  `price`    DECIMAL(10,2) NOT NULL DEFAULT 0.00   COMMENT '定价（元）',
  `stock`    INT UNSIGNED  NOT NULL DEFAULT 0      COMMENT '库存',
  `intro`    TEXT                                  COMMENT '简介',
  `genre`    ENUM('文学','历史','科技') NOT NULL DEFAULT '文学' COMMENT '分类',
  `added_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='演示：图书表';

DESC `demo_book`;

CREATE TABLE `demo_member` (
  `id`       INT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `username` VARCHAR(50)   NOT NULL                COMMENT '登录名，唯一',
  `email`    VARCHAR(100)  DEFAULT NULL            COMMENT '邮箱，唯一',
  `age`      TINYINT UNSIGNED NOT NULL DEFAULT 0   COMMENT '年龄',
  `balance`  DECIMAL(10,2) NOT NULL DEFAULT 0.00   COMMENT '余额',
  `nickname` VARCHAR(50)   NOT NULL DEFAULT '书友' COMMENT '昵称',
  `status`   TINYINT       NOT NULL DEFAULT 1      COMMENT '1正常 0禁用',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_demo_member_username` (`username`),
  UNIQUE KEY `uk_demo_member_email` (`email`),
  CONSTRAINT `chk_demo_member_age`     CHECK (`age` <= 150),
  CONSTRAINT `chk_demo_member_balance` CHECK (`balance` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='演示：约束示例表';

-- 只给必填列，其余自动走默认值
INSERT INTO `demo_member` (`username`, `email`) VALUES ('zhangsan', 'zs@hiwen.local');
SELECT id, username, email, age, balance, nickname, status FROM `demo_member`;

-- ① 违反 UNIQUE：username 重复
INSERT INTO `demo_member` (`username`) VALUES ('zhangsan');

-- ② 违反 CHECK：年龄 200 > 150
INSERT INTO `demo_member` (`username`, `age`) VALUES ('lisi', 200);

-- ③ 违反 CHECK：余额为负
INSERT INTO `demo_member` (`username`, `balance`) VALUES ('wangwu', -10);

-- 删掉「散文」分类（id = 4），看《陋室铭》会怎样
START TRANSACTION;

DELETE FROM `category` WHERE `id` = 4;

SELECT id, title, category_id FROM `article` ORDER BY id;

ROLLBACK; -- 演示完还原

-- 删掉作品 1《静夜思》，它的书评会怎样？
START TRANSACTION;

DELETE FROM `article` WHERE `id` = 1;

-- 种子里作品 1 有 2 条书评（id 1、2）
SELECT COUNT(*) AS 剩余书评 FROM `comment` WHERE `article_id` = 1;

ROLLBACK; -- 演示完还原

CREATE TABLE `demo_user` (
  `id`       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ① 加一列（AFTER 指定插在哪个字段后面）
ALTER TABLE `demo_user` ADD COLUMN `email` VARCHAR(100) DEFAULT NULL AFTER `username`;

-- ② 改列定义：把 username 的长度从 50 放宽到 80
ALTER TABLE `demo_user` MODIFY COLUMN `username` VARCHAR(80) NOT NULL;

-- ③ 删一列
ALTER TABLE `demo_user` DROP COLUMN `email`;

-- 看最终结构
DESC `demo_user`;

-- 连表带数据一起销毁，不可恢复
DROP TABLE `demo_user`;

-- 建表时加 IF NOT EXISTS、删表时加 IF EXISTS，可避免「表已存在 / 不存在」的报错
-- DROP TABLE IF EXISTS `demo_user`;

-- 不写 id，数据库自动分配
INSERT INTO `category` (`name`, `description`, `sort`) VALUES ('戏曲', '传统戏曲唱词', 6);

SELECT id, name, sort FROM `category` ORDER BY id;

-- 演示完删掉，恢复种子的 5 个分类
DELETE FROM `category` WHERE `name` = '戏曲';

SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'hiwen' AND TABLE_NAME = 'article'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 普通索引：给「按朝代筛选」加速
CREATE INDEX `idx_article_dynasty` ON `article` (`dynasty`);

SELECT INDEX_NAME, COLUMN_NAME FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'hiwen' AND TABLE_NAME = 'article'
  AND INDEX_NAME = 'idx_article_dynasty';

-- 删除索引
DROP INDEX `idx_article_dynasty` ON `article`;

CREATE TABLE `demo_note` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `content`    VARCHAR(200) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
               ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入：两个时间列都不用写，自动填当前时间
INSERT INTO `demo_note` (`content`) VALUES ('第一条笔记');

-- 隔一会儿再改一次
UPDATE `demo_note` SET `content` = '改过的笔记' WHERE `id` = 1;

SELECT id, content, created_at, updated_at FROM `demo_note`;