【MySQL】数据类型和表的约束

1. 数据类型

分类	数据类型	解释
数值类型	BIT (M)	位类型。M位数，默认为1范围1-64
	BOOL	01表示真假
	TINYINT [UNSIGNED]	8位整型
	SMALLINT [UNDIGNED]	16位短整型
	INT [UNSIGNED]	32位整型
	BIGINT [UNSIGNED]	64位长整型
小数类型	FLOAT [ (M, D) ] [UNSIGNED]	32位浮点类型，M整体长度，D小数长度
	DOUBLE [ (M, D) ] [UNSIGNED]	64位浮点类型，M整体长度，D小数长度
	DECIMAL (M, D) [UNSIGNED]	M整体长度，D小数位数
文本、二进制类型	CHAR (SIZE)	定长字符串，L字符个数，最大255字符
	VARCHAR (SIZE)	变长字符串，L字符个数，最大65535字节
	BLOB	二进制数据
	TEXT	大文本，不支持全文索引，不支持默认值
时间日期	DATE	yyyy-mm-dd仅日期格式，占3字节
	DATETIME	yyyy-mm-dd hh:mm:ss格式，支持自定义，占8字节
	TIMESTAMP	yyyy-mm-dd hh:mm:ss格式，实际是时间戳，占4字节
String类型	ENUM	单个字符串对象，多选一
	SET	字符串对象集合，多选多

ERROR 1264 (22003): Out of range value for column 'number' at row 1

SQL会直接拒绝插入越界的数据。数据类型本质就是一种数据安全的约束。

varchar最大65535个字节，头3字节存储长度，故utf8编码varchar最大存储21844个字符。当然MySQL限制一行元素大小之和最大65535个字节。
char类型始终占用L个字符的大小，varchar类型的占用和数据长度相关。
数据长度不变就用char，长度变化就用varchar；
定长浪费空间但效率高，变长节省空间但效率低。

enum:

enum ('选项1', '选项2', '选项3', ...)

新增时必须插入规定的选项值中的一个。enum类型，实际存储的是从1开始的数字下标。

set:

set  ('选项1', '选项2', '选项3', ...)

set类型可以选择多个选项，用逗号隔开，合并成一个字符串。

set存储时采用位图结构。下标的二进制位，从低到高每个比特位表示一个选项，为1表示具有该选项，反之则没有。

WHERE:
在这里插入图片描述

where只能筛选出严格匹配的记录，用find_in_set可以筛选出包含该选项的记录。

MySQL为保证数据的完整性一致性，给我们提供了如下各种强约束。

MySQL中的NULL表示不存在、为空。列默认可以为空，not null表示不许为空。

create table stu (
    name varchar(16) not null
);

通过default设置列的默认值，可以避免该字段为空。

create table genders (
    gender char(1) default '男'
);

comment就是注释。

create table genders (
    gender char(1) comment '性别'
);

建表时MySQL自动为int字段添加(10)。设置zerofill的列，如果数据宽度低于指定位数，显示时会在前面补零，不影响存储。

alter table tb modify a(3) int unsigned zerofill;
select * from tbs;
+-----+
| a   |
+-----+
| 001 |
| 011 |
| 111 |
+-----+

数据库要求表中有一个字段作为一行记录的唯一标识，就是主键。

类似于序号，根据主键能够唯一筛选出一条记录。主键字段不可为空不可重复，一张表只能有一个主键。

create table tb (
    id int primary key
);

删除主键

alter table `tb_name` drop primary key; # 只是为字段去掉主键约束，并不是删除整列。

追加主键

alter table `tb_name` add primary key(`field_name`); # 为已存在字段追加主键，如果有重复值，会追加失败。

复合主键可以让多列合起来作为主键。这样只需要保证多列不同时重复就行。

create table `tb_name` (
	`field1` type,
    `field2` type,
    primary key (`field1`, `field2`, ...) # 设置复合主键
);

设置auto_increment的字段，插入时可以不给值，新记录的会自动取值为当前最大值加1。一般自增长的字段都是主键。

create table tb (
    id int primary key auto_increment
) auto_increment=100;

如果存在多个字段需要保证唯一性，但主键只有一个，所以只能用唯一键约束。

create table stu (
	id1 int primary key,
	id2 int unique
);

主键和唯一键并不冲突，只是负责数据在不同层面的唯一性。

外键用来将从表和主表之间建立关系。外键定义在从表上，一般外键就对应着主表的主键或者唯一键。

create table tb (
	foreign key (`field`) references `tb_master`(`field`)
)

通过外键产生关系的表，除了在逻辑上有关联，MySQL也会维护该逻辑对用户操作产生约束。

比如班级表中得先有id为30的班级，才能在学生表中添加class_id为30的学生。

要删除班级表中id为20的班级，首先必须保证学生表中没有对应班级的学生。

对于数据库用户来说，外键是为了方便业务关联，对于MySQL来说，外键是为了产生对应的外键约束，保证数据的安全。