mysql/mariadb 进阶知识之表设计和查询

进阶知识之表设计和查询

学生信息、成绩、课程数据表，提取码：iqub

评论及回复数据表，提取码：hdr7

---

一、表设计之数据类型选择

1.1、表设计步骤

1. 第一步，确定大类型：数字、字符串、日期等级等；
2. 第二步，确定具体的类型：如整数类型，tinyint/smallint/mediumint/int/bigint

1.2、数据类型

1.2.1、数字类型（整数/小数）

整数类型

整数类型说明：

• 存储值的范围越大，所需要的存储空间也会越大
• 可以添加AUTO_INCREMENT自增约束条件
• UNSIGNED属性(无符号显示)：默认是SIGNED(有符号显示)，正数的上限提升约1倍
• INT(11)：显示宽度，即能显示的最大数字个数

小数类型

浮点数取值范围：

1.2.2、字符类型

utf8mb3字符集：一个字符=3个字节

utf8mb4字符集：一个字符=4个字节

1.2.3、日期时间类型

1.2.4、其他类型

1.2.5、数据类型的选择

选择的参考规则

• 尽可能选择满足业务需求的最小数据类型
  速度更快，更省资源(磁盘、内存、CPU)
  精度不同，消耗的资源不同
• 尽可能选择使用简单数据类型，如：整形比字符操作代价更低
• 尽可能避免NULL，注意：NULL与空值的区别。避免NULL方法 NOT NULL限制，或者DEFAULT指定默认值。

---

二、表设计之字段约束条件

2.1、字段约束

2.2、外键约束管理

同创建表时创建外键约束

CREATE TEABLE `account_user`(
	`id` INT NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(`id`)
);

建立外键的时，可以给外键取一个名字，方便后续进行修改和删除
CREATE TABLE `account_user_address`(
	`user_id` INT NOT NULL,
	FOREIGN KEY [key_name](`user_id`) REFERENCES `account_user`(`id`)
);

修改表时增加外键约束

ALTER TABLE `account_user_address` ADD FROEIGN KEY key_name(`user_id`) REFERENCES `account_user(`id`)`;

删除外键约束

ALTER TABLE `account_user_address` DROP FOREIGN KEY key_name;

外键约束使用要点

• 用来约束多个表中数据的关系
• 在【子表】上定义约束内容
• 约束关联字段的类型要保持一致
• 注意添加外键约束后的数据删除问题，只能先删除外键表相关的数据后，才能正常删除主表中的数据

2.3、实战：设计学生成绩表

-- 学生信息表
CREATE TABLE `school_student_info` (
 `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `stu_no` SMALLINT NOT NULL COMMENT '学号',
 `stu_name` VARCHAR(16) NOT NULL COMMENT '姓名',
 `sex` TINYINT DEFAULT 1 COMMENT '性别, 0: 女，1：男',
 `age` TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
 `class_name` VARCHAR(10) DEFAULT '' COMMENT '班级',
 `address` VARCHAR(255) DEFAULT &#