真正约束字段的是数据类型但是数据类型约束很单一需要有一些额外的约束更好的保证数据的合法性从业务逻辑角度保证数据的正确性。比如有一个字段是email要求是唯一的。目录空属性默认值列描述zerofill主键自增长唯一键外键空属性约束含义NULL允许该列的值为空默认行为即插入或更新时可以不指定该列的值NOT NULL不允许该列的值为空插入或更新时必须提供值否则报错不显式指定时列默认允许NULLNOT NULL的列在INSERT时必须显式赋值或通过DEFAULT提供默认值-- 1. 空属性NULL / NOT NULL -- 1.1 创建表name 列不允许为空nickname 列允许为空 CREATE TABLE student_null_demo ( id INT NOT NULL, name VARCHAR(20) NOT NULL, nickname VARCHAR(20) NULL -- NULL 是默认行为可省略 ); -- 1.2 查看表结构确认列的 NULL 属性 DESC student_null_demo; -- 1.3 插入合法数据name 提供了非空值 INSERT INTO student_null_demo (id, name, nickname) VALUES (1, 张三, 阿三); -- 1.4 插入数据时 nickname 为 NULL允许 INSERT INTO student_null_demo (id, name, nickname) VALUES (2, 李四, NULL); -- 1.5 尝试插入 name 为 NULL报错Column name cannot be null -- INSERT INTO student_null_demo (id, name) VALUES (3, NULL); -- 执行会报错 -- 1.6 查看所有数据 SELECT * FROM student_null_demo; -- 1.7 演示结束删除表 DROP TABLE IF EXISTS student_null_demo;结果为默认值DEFAULT用于为列指定默认值当INSERT时未给该列赋值则自动使用默认值默认值的数据类型必须与列的数据类型兼容如果不指定DEFAULTMySQL 会根据列的类型隐式赋予默认值通常是NULL-- 2. 默认值DEFAULT -- 2.1 创建表status 默认为 0created_at 默认为当前时间戳 CREATE TABLE order_default_demo ( id INT NOT NULL, order_no VARCHAR(32) NOT NULL, status TINYINT DEFAULT 0, amount DECIMAL(10,2) DEFAULT 0.00, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 2.2 查看表结构确认 DEFAULT 列 DESC order_default_demo; -- 2.3 插入时不指定 status 和 amount使用默认值 INSERT INTO order_default_demo (id, order_no) VALUES (1, ORD20240101001); -- 2.4 插入时显式指定所有值覆盖默认值 INSERT INTO order_default_demo (id, order_no, status, amount) VALUES (2, ORD20240101002, 1, 99.99); -- 2.5 查看数据观察默认值的效果 SELECT * FROM order_default_demo; -- 2.6 演示结束删除表 DROP TABLE IF EXISTS order_default_demo;列描述COMMENT用于为列或表添加注释说明方便开发者和 DBA 理解字段含义注释信息存储在数据字典中可通过SHOW CREATE TABLE或SHOW FULL COLUMNS查看注释不会影响 SQL 执行纯粹是文档用途建议为每个字段都加上有意义的注释-- 3. 列描述COMMENT -- 3.1 创建表为每一列添加注释 CREATE TABLE product_comment_demo ( id INT COMMENT 商品主键ID, name VARCHAR(100) COMMENT 商品名称, price DECIMAL(10,2) COMMENT 商品单价单位元, category VARCHAR(50) COMMENT 商品分类, description TEXT COMMENT 商品详细描述 ) COMMENT 商品信息表演示列注释用法; -- 3.2 查看完整的列信息包含 Comment 列 SHOW FULL COLUMNS FROM product_comment_demo; -- 3.3 查看建表语句包含表注释和列注释 SHOW CREATE TABLE product_comment_demo\G -- 3.4 通过 information_schema 查询列注释 SELECT COLUMN_NAME AS 列名, COLUMN_TYPE AS 列类型, COLUMN_COMMENT AS 列注释 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA DATABASE() AND TABLE_NAME product_comment_demo; -- 3.5 演示结束删除表 DROP TABLE IF EXISTS product_comment_demo;结果为zerofillZEROFILL是数值类型INT、TINYINT、BIGINT等的显示宽度修饰符当数值的显示宽度不足指定宽度时左侧用0填充使用ZEROFILL时列会自动变为UNSIGNED无符号ZEROFILL只影响显示格式不影响存储的实际值注意MySQL 8.0.17 已弃用整数类型的显示宽度ZEROFILL在未来版本中可能被移除-- 1. 创建表 CREATE TABLE product_zerofill_demo ( id INT(8) COMMENT 普通 INT, order_no INT(8) ZEROFILL COMMENT 订单号不足8位左侧补0, price INT(5) ZEROFILL COMMENT 价格不足5位左侧补0 ); -- 2. 查看表结构 DESC product_zerofill_demo; -- ---------------------------------------------------------------- -- | Field | Type | Null | Key | Default | Extra | -- ---------------------------------------------------------------- -- | id | int | YES | | NULL | | -- | order_no | int(8) unsigned zerofill | YES | | NULL | | ← unsigned zerofill -- | price | int(5) unsigned zerofill | YES | | NULL | | ← 自动变为无符号 -- ---------------------------------------------------------------- -- 3. 插入数据 INSERT INTO product_zerofill_demo VALUES (1, 1, 99); INSERT INTO product_zerofill_demo VALUES (2, 12345, 500); INSERT INTO product_zerofill_demo VALUES (3, 99999999, 8); -- 4. 查看结果注意 ZEROFILL 的显示效果 SELECT * FROM product_zerofill_demo; -- ------------------------- -- | id | order_no | price | -- ------------------------- -- | 1 | 00000001 | 00099 | ← 不足8/5位左侧补0 -- | 2 | 00012345 | 00500 | ← 不足8/5位左侧补0 -- | 3 | 99999999 | 00008 | ← 不足8/5位左侧补0 -- ------------------------- -- ❌ ZEROFILL 列自动为 UNSIGNED插入负数会报错 INSERT INTO product_zerofill_demo VALUES (4, -1, 100); -- ERROR 1264 (22003): Out of range value for column order_no -- 5. 删除表 DROP TABLE product_zerofill_demo;主键主键Primary Key用于唯一标识表中的每一行记录主键列的值必须唯一且非空自动NOT NULL一张表有且仅有一个主键但主键可以由多个列组成复合主键 / 联合主键主键默认创建聚簇索引InnoDB 引擎主键查询效率极高建议每张表都设置主键通常用无业务含义的id列-- 1. 创建表列级主键 -- 方式一在列定义后直接指定 PRIMARY KEY CREATE TABLE student_pk_demo1 ( id INT PRIMARY KEY, -- 列级定义主键 name VARCHAR(20), age INT ); -- 方式二在表定义末尾统一指定推荐便于定义复合主键 CREATE TABLE student_pk_demo2 ( id INT, name VARCHAR(20), age INT, PRIMARY KEY (id) -- 表级定义主键 ); -- 方式三复合主键联合主键 CREATE TABLE score_pk_demo ( student_id INT, course_id INT, score DECIMAL(5, 2), PRIMARY KEY (student_id, course_id) -- 两列共同构成主键一个学生不能重复选同一门课 ); -- 2. 查看表结构 DESC student_pk_demo1; -- ----------------------------------------------- -- | Field | Type | Null | Key | Default | Extra | -- ----------------------------------------------- -- | id | int | NO | PRI | NULL | | ← NullNO, KeyPRI -- | name | varchar(20) | YES | | NULL | | -- | age | int | YES | | NULL | | -- ----------------------------------------------- DESC score_pk_demo; -- ----------------------------------------------------- -- | Field | Type | Null | Key | Default | Extra | -- ----------------------------------------------------- -- | student_id | int | NO | PRI | NULL | | ← 都是 PRI -- | course_id | int | NO | PRI | NULL | | ← 都是 PRI复合主键 -- | score | decimal(5,2) | YES | | NULL | | -- ----------------------------------------------------- -- 3. 插入数据 -- ✅ 主键唯一即可正常插入 INSERT INTO student_pk_demo1 VALUES (1, 张三, 20); INSERT INTO student_pk_demo1 VALUES (2, 李四, 22); -- ❌ 主键重复报错 INSERT INTO student_pk_demo1 VALUES (1, 王五, 21); -- ERROR 1062 (23000): Duplicate entry 1 for key student_pk_demo1.PRIMARY -- ❌ 主键为 NULL报错 INSERT INTO student_pk_demo1 VALUES (NULL, 赵六, 23); -- ERROR 1048 (23000): Column id cannot be null -- ✅ 复合主键只要 (student_id, course_id) 组合不重复即可 INSERT INTO score_pk_demo VALUES (1, 101, 85.5); INSERT INTO score_pk_demo VALUES (1, 102, 90.0); -- 同学生不同课程OK INSERT INTO score_pk_demo VALUES (2, 101, 78.0); -- 同课程不同学生OK -- ❌ 复合主键重复 INSERT INTO score_pk_demo VALUES (1, 101, 92.0); -- ERROR 1062 (23000): Duplicate entry 1-101 for key score_pk_demo.PRIMARY -- 4. 查看结果 SELECT * FROM student_pk_demo1; -- ------------------ -- | id | name | age | -- ------------------ -- | 1 | 张三 | 20 | -- | 2 | 李四 | 22 | -- ------------------ SELECT * FROM score_pk_demo; -- ------------------------------ -- | student_id | course_id | score | -- ------------------------------ -- | 1 | 101 | 85.50 | -- | 1 | 102 | 90.00 | -- | 2 | 101 | 78.00 | -- ------------------------------ -- 5. 删除表 DROP TABLE student_pk_demo1; DROP TABLE student_pk_demo2; DROP TABLE score_pk_demo;自增长AUTO_INCREMENT用于自动生成递增的整数值通常与主键配合使用每张表最多只能有一个AUTO_INCREMENT列AUTO_INCREMENT列必须是索引列通常是PRIMARY KEY或UNIQUE该列的数据类型必须是整数类型TINYINT、INT、BIGINT等AUTO_INCREMENT列插入时可以传NULL或0、或直接省略MySQL 自动分配下一个编号删除记录后自增值不会回退除非手动ALTER TABLE ... AUTO_INCREMENT N-- 1. 创建表 CREATE TABLE user_autoinc_demo ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 用户ID自增主键, username VARCHAR(30) NOT NULL, age INT ); -- 2. 查看表结构 DESC user_autoinc_demo; -- ----------------------------------------------------------- -- | Field | Type | Null | Key | Default | Extra | -- ----------------------------------------------------------- -- | id | int | NO | PRI | NULL | auto_increment | ← Extra 列 -- | username | varchar(30) | NO | | NULL | | -- | age | int | YES | | NULL | | -- ----------------------------------------------------------- -- 3. 插入数据 -- ✅ 方式一省略自增列 INSERT INTO user_autoinc_demo (username, age) VALUES (alice, 25); -- ✅ 方式二传 NULL自动分配下一个编号 INSERT INTO user_autoinc_demo VALUES (NULL, bob, 30); -- ✅ 方式三传 0同样自动分配 INSERT INTO user_autoinc_demo VALUES (0, charlie, 28); -- ✅ 方式四显式指定一个未使用的值可手动覆盖自增序列 INSERT INTO user_autoinc_demo VALUES (100, david, 35); -- 之后再省略自增列从 101 继续 INSERT INTO user_autoinc_demo (username, age) VALUES (eve, 22); -- 4. 查看结果 SELECT * FROM user_autoinc_demo; -- --------------------- -- | id | username | age | -- --------------------- -- | 1 | alice | 25 | -- | 2 | bob | 30 | -- | 3 | charlie | 28 | -- | 100 | david | 35 | -- | 101 | eve | 22 | ← 从 100 之后继续递增 -- --------------------- -- 查看当前自增值 SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA DATABASE() AND TABLE_NAME user_autoinc_demo; -- 5. 删除表 DROP TABLE user_autoinc_demo;唯一键UNIQUE KEY唯一键 / 唯一约束用于保证列或列组合中的值在表中唯一不可重复与主键的区别一张表可以有多个唯一键主键有且仅有一个唯一键列允许NULL值在 MySQL 中多个NULL不算重复唯一键在物理存储上也是索引适合用于邮箱、手机号、身份证号等业务上需保证唯一的字段-- 1. 创建表列级和表级定义唯一键 CREATE TABLE member_unique_demo ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(30) UNIQUE, -- 列级唯一约束用户名不能重复 email VARCHAR(50), -- 表级定义见下方 phone VARCHAR(20), UNIQUE KEY uk_email (email), -- 表级唯一约束指定约束名 uk_email UNIQUE KEY uk_phone (phone) -- 表级唯一约束指定约束名 uk_phone ); -- 2. 查看表结构 DESC member_unique_demo; -- ----------------------------------------------------------- -- | Field | Type | Null | Key | Default | Extra | -- ----------------------------------------------------------- -- | id | int | NO | PRI | NULL | auto_increment | -- | username | varchar(30) | YES | UNI | NULL | | ← KeyUNI -- | email | varchar(50) | YES | UNI | NULL | | ← KeyUNI -- | phone | varchar(20) | YES | UNI | NULL | | ← KeyUNI -- ----------------------------------------------------------- -- 查看建表语句可以看到唯一键名称 SHOW CREATE TABLE member_unique_demo; -- 3. 插入数据 INSERT INTO member_unique_demo (username, email, phone) VALUES (alice, aliceexample.com, 13800001111); INSERT INTO member_unique_demo (username, email, phone) VALUES (bob, bobexample.com, 13800002222); -- ❌ username 重复报错 INSERT INTO member_unique_demo (username, email, phone) VALUES (alice, alice2example.com, 13800003333); -- ERROR 1062 (23000): Duplicate entry alice for key member_unique_demo.username -- ❌ email 重复报错 INSERT INTO member_unique_demo (username, email, phone) VALUES (charlie, aliceexample.com, 13800003333); -- ERROR 1062 (23000): Duplicate entry aliceexample.com for key member_unique_demo.uk_email -- ✅ 唯一键允许 NULL且多个 NULL 不冲突 INSERT INTO member_unique_demo (username, email, phone) VALUES (david, NULL, NULL); INSERT INTO member_unique_demo (username, email, phone) VALUES (eve, NULL, NULL); -- 多个 NULL 不冲突 -- 4. 查看结果 SELECT * FROM member_unique_demo; -- ------------------------------------------------ -- | id | username | email | phone | -- ------------------------------------------------ -- | 1 | alice | aliceexample.com | 13800001111 | -- | 2 | bob | bobexample.com | 13800002222 | -- | 3 | david | NULL | NULL | -- | 4 | eve | NULL | NULL | ← 两个 NULL 不冲突 -- ------------------------------------------------ -- 5. 删除表 DROP TABLE member_unique_demo;外键FOREIGN KEY外键用于建立两张表之间的关联保证引用完整性外键定义在子表从表上引用了父表主表的主键或唯一键列外键的核心约束规则插入子表时外键值必须在父表中存在或为NULL删除/更新父表时受外键约束限制具体行为由ON DELETE/ON UPDATE决定外键参照行为参照动作行为含义CASCADE父表删除/更新 → 子表同步删除/更新SET NULL父表删除/更新 → 子表外键置为 NULLRESTRICT父表有子表引用时禁止删除/更新默认NO ACTION与 RESTRICT 类似标准 SQL 延迟检查MySQL 中等同 RESTRICTSET DEFAULT父表删除/更新 → 子表外键置为默认值InnoDB 不支持外键约束生效的前提父子表都使用 InnoDB 引擎被引用的父表列必须是PRIMARY KEY或UNIQUE KEY外键列和被引用列的数据类型必须一致外键列的命名规范建议父表名_id或关联实体_id-- 1. 创建表 -- 父表被引用表班级表 CREATE TABLE class_fk_demo ( class_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 班级ID, class_name VARCHAR(30) NOT NULL COMMENT 班级名称 ); -- 子表外键所在表学生表 CREATE TABLE student_fk_demo ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生ID, name VARCHAR(20) NOT NULL COMMENT 学生姓名, class_id INT COMMENT 所属班级ID外键, -- 表级定义外键指定约束名和参照行为 CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class_fk_demo (class_id) ON DELETE SET NULL -- 班级被删除时学生班级置为 NULL ON UPDATE CASCADE -- 班级ID更新时学生表中同步更新 ); -- 2. 查看表结构 DESC student_fk_demo; -- ----------------------------------------------------------- -- | Field | Type | Null | Key | Default | Extra | -- ----------------------------------------------------------- -- | id | int | NO | PRI | NULL | auto_increment | -- | name | varchar(20) | NO | | NULL | | -- | class_id | int | YES | MUL | NULL | | ← KeyMUL 表示外键 -- ----------------------------------------------------------- -- 方式二查看完整建表语句能看到外键定义 SHOW CREATE TABLE student_fk_demo; -- 方式三从 information_schema 中查询外键信息 SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL; -- 3. 插入数据 -- 先往父表插入班级 INSERT INTO class_fk_demo (class_name) VALUES (一年级一班); INSERT INTO class_fk_demo (class_name) VALUES (一年级二班); -- ✅ 外键值存在正常插入 INSERT INTO student_fk_demo (name, class_id) VALUES (张三, 1); INSERT INTO student_fk_demo (name, class_id) VALUES (李四, 2); INSERT INTO student_fk_demo (name, class_id) VALUES (王五, 1); -- ✅ 外键允许 NULL未分配班级的学生 INSERT INTO student_fk_demo (name, class_id) VALUES (赵六, NULL); -- ❌ 外键值在父表中不存在报错 INSERT INTO student_fk_demo (name, class_id) VALUES (钱七, 999); -- ERROR 1452 (23000): Cannot add or update a child row: -- a foreign key constraint fails (test.student_fk_demo, -- CONSTRAINT fk_student_class FOREIGN KEY (class_id) -- REFERENCES class_fk_demo (class_id) ON DELETE SET NULL ON UPDATE CASCADE) -- 4. 验证外键参照行为 -- 4a. ON DELETE SET NULL -- 删除父表的班级 1 DELETE FROM class_fk_demo WHERE class_id 1; -- 子表中原来 class_id1 的学生class_id 被自动置为 NULL SELECT * FROM student_fk_demo; -- ---------------------- -- | id | name | class_id | -- ---------------------- -- | 1 | 张三 | NULL | ← 原 class_id1随父表删除被置 NULL -- | 2 | 李四 | 2 | -- | 3 | 王五 | NULL | ← 原 class_id1随父表删除被置 NULL -- | 4 | 赵六 | NULL | -- ---------------------- -- 4b. ON UPDATE CASCADE -- 更新父表的班级 2 为 20 UPDATE class_fk_demo SET class_id 20 WHERE class_id 2; -- 子表中 class_id 跟随同步更新 SELECT * FROM student_fk_demo; -- ---------------------- -- | id | name | class_id | -- ---------------------- -- | 1 | 张三 | NULL | -- | 2 | 李四 | 20 | ← 原 class_id2随父表级联更新为 20 -- | 3 | 王五 | NULL | -- | 4 | 赵六 | NULL | -- ---------------------- -- 4c. 验证默认 RESTRICT 行为有子表引用时禁止删除父表 -- 重建班级表和学生表换用 RESTRICT 参照动作 CREATE TABLE class_restrict ( class_id INT PRIMARY KEY AUTO_INCREMENT, class_name VARCHAR(30) ); CREATE TABLE student_restrict ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), class_id INT, CONSTRAINT fk_stu_cls_restrict FOREIGN KEY (class_id) REFERENCES class_restrict (class_id) ON DELETE RESTRICT -- 有引用则禁止删除 ON UPDATE RESTRICT -- 有引用则禁止更新 ); INSERT INTO class_restrict (class_name) VALUES (A班); INSERT INTO student_restrict (name, class_id) VALUES (小明, 1); -- ❌ 子表存在引用父表无法删除 DELETE FROM class_restrict WHERE class_id 1; -- ERROR 1451 (23000): Cannot delete or update a parent row: -- a foreign key constraint fails (test.student_restrict, -- CONSTRAINT fk_stu_cls_restrict FOREIGN KEY (class_id) -- REFERENCES class_restrict (class_id)) DROP TABLE student_restrict; DROP TABLE class_restrict; -- 5. 删除表先删子表再删父表 -- ⚠️ 有外键关联时必须先删子表再删父表否则报错 DROP TABLE student_fk_demo; DROP TABLE class_fk_demo;本期内容到这里就结束了封面图自取
