MySQL 数据类型简介 创建数据表及其字段约束

数据类型介绍

MySQL 数据类型分类

• 整型
• 浮点型
• 字符类型(char与varchar)
• 日期类型
• 枚举与集合

具体数据类型见这篇博客

MySQL表操作中的约束

• primary key 主键约束 非空+唯一
• unique key 唯一约束
• not null 非空约束
• foreign key 外键约束

创建表的的语法

CREATE TABLE[IF NOT EXISTS] tbl_name（字段名称字段类型[完整性约束条件]）ENGINE=引擎名称 CHARSET='编码方式”

⚠️注意：

1. 在同一张表中，字段名不能相同
2. 宽度和约束条件可选，字段名和类型是必须的
3. 最后一个字段后不能加逗号！

整型

• 分类：TINYINT SMALLINT MEDIUMINT INT BIGINT
• 作用：存储年龄，等级，id，各种号码等

create table t1(id tinyint); # 创建 tinyint类型 tinyint 范围-128-128
create table t2(x tinyint unsigned); # 创建无符号整数

强调

对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制，所以在创建表时，如果字段采用的是整型类型，完全无需指定显示宽度， 默认的显示宽度，足够显示完整当初存放的数据

浮点型

• 分类:FLOAT DOUBLE decimal
• 应用场景:身高，体重，薪资

字段限制特点(5,3)前一位表示所有的位数，后一位表示小数个数

字符类型

• 分类 char(定长) varchar(变长)
• 作用:姓名，地址，描述类信息

create table t10(name char(4))  # 超出四个字符报错，不够四个字符空格补全
create table t11(name varchar(4))  # 超出四个字符报错，不够四个有几个就存几个

时间类型

• 分类

  • date：2019-05-01

  • time：11:11:11

  • Datetime: 2019-01-02 11:11:11

  • Year:2019

create table student(
	id int,
  name char(16),
  born_year year,
  birth date,
  study_time time,
  reg_time datetime
);

​

枚举与集合类型

• 分类
  • 枚举enum 多选一
  • 集合set 多选多

枚举

create table user(
	id int,
  name char(16),
  gender enum('male','female','others')
);

插入下面数据时第一句就会报错因为XXX不在创建的枚举类型中

insert into user values(1,'小明','xxx')  # 报错
insert into user values(2,'二明','female')  # 正确！

集合

create table teacher(
	id int,
  name char(16),
  gender enum('male','female','others'),
  hobby set('read','sleep','run','singing')
);

插入的时候只要是插入的数据在集合中就可以，值可以没有（MySQL80），可以是一个，也可以全都是。

insert into teacher values(1,'小明','male','read,sleep') 

约束条件

• PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
• FOREIGN KEY (FK) 标识该字段为该表的外键
• NOT NULL 标识该字段不能为空
• UNIQUE KEY (UK) 标识该字段的值是唯一的
• AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
• DEFAULT 为该字段设置默认值
• UNSIGNED 无符号
• ZEROFILL 使用0填充

not null+default

有时候插入数据的时候，要限制不为空，但是这个时候没有插入值，我们可以设置默认值，让默认值去填充没有插入的数据

create table student2(
	id int,
  name char(16) not null,
  gender enum('male','female','others') default 'male'
);

当设置字段的默认值的时候，不写数据也不会出错

insert into student2(id,name) values(1,'小明') 

mysql> select * from student2;
+------+--------+--------+
| id   | name   | gender |
+------+--------+--------+
|    1 | 小明   | male   |
+------+--------+--------+

unique

单列唯一

create table user1(
	id int unique, 
  name char(16)
);

insert into user1 values(1,'小明'),(1,'小蓝')  # 报错
insert into user1 values(1,'小明'),(2,'小蓝')  # 成功

联合唯一

将多个字段设置为唯一，只有当多个字段的值出现重复时才会出现错误。

create table server(
	id int,
  ip char(16),
  port int,
  unique(ip,port)
)
insert into server values(1,'127.0.0.1',8080); # 成功
insert into server values(2,'127.0.0.1',8080);  # 报错
insert into server values(1,'127.0.0.1',8081); # 报错

primary key+auto_increment

单从约束角度来说primary key就等价于not null unique,指定not null 和unique两个字段的时候也会将字段设置为主键。

除了约束之外，它还是innodb引擎组织数据的依据，提升查询效率。

一张表中必须有且只有一个主键，如果你没有设置主键，那么会从上到下搜索直到遇到一个非空且唯一的字段自动将其设置为主键。

如果表里面没有指定任何的可以设置为主键的字段，那么innodb会采用自己默认的一个隐藏字段作为主键，隐藏意味着你在查询的时候无法根据这个主键字段加速查询了
索引：类似于书的目录，没有主键就相当于一页一页翻着查

一张表中通常都应该有一个id字段，并且通常将改id字段作成主键

主键

create table t12(
  id int primary key，
  name char(16),
  age int not null unique,
  addr char(16) not null unique
)engine=innodb;
desc t12;

-- 将ID字段设置为主键
mysql> desc t12;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
| age   | int(11)  | NO   | UNI | NULL    |       |
| addr  | char(16) | NO   | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+

联合主键

多个字段联合起来作为表的一个主键，本质还是一个主键

create table t18(
  ip char(16),
  port int,
  primary key(ip,port)
);

mysql> desc t18;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip    | char(16) | NO   | PRI | NULL    |       |
| port  | int(11)  | NO   | PRI | NULL    |       |
+-------+----------+------+-----+---------+-------+